[sr-dev] git:master: modules/db_postgres: Added support for database row and table locking to PostgreSQL database module

Module: sip-router Branch: master Commit: f1f39db4f8a73f16497c4cc2658e9b4e68bac0fc URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=f1f39db… Author: Peter Dunkley &lt;peter.dunkley(a)crocodile-rcs.com&gt; Committer: Peter Dunkley &lt;peter.dunkley(a)crocodile-rcs.com&gt; Date: Tue Aug 21 15:27:29 2012 +0100 modules/db_postgres: Added support for database row and table locking to PostgreSQL database module - start_transaction() now takes an argument allowing the type of locking (none, read, or full) to be specified. - new query_lock() API will use a SELECT ... FOR UPDATE query instead of just a SELECT ... --- modules/db_postgres/km_db_postgres.c | 1 + modules/db_postgres/km_dbase.c | 80 ++++++++++++++++++++++++++++++++- modules/db_postgres/km_dbase.h | 11 ++++- 3 files changed, 88 insertions(+), 4 deletions(-) diff --git a/modules/db_postgres/km_db_postgres.c b/modules/db_postgres/km_db_postgres.c index 34edca7..a8f0130 100644 --- a/modules/db_postgres/km_db_postgres.c +++ b/modules/db_postgres/km_db_postgres.c @@ -99,6 +99,7 @@ int db_postgres_bind_api(db_func_t *dbb) dbb->start_transaction= db_postgres_start_transaction; dbb->end_transaction = db_postgres_end_transaction; dbb->abort_transaction= db_postgres_abort_transaction; + dbb->query_lock = db_postgres_query_lock; return 0; } diff --git a/modules/db_postgres/km_dbase.c b/modules/db_postgres/km_dbase.c index 6c21b05..bf393ed 100644 --- a/modules/db_postgres/km_dbase.c +++ b/modules/db_postgres/km_dbase.c @@ -441,6 +441,33 @@ int db_postgres_query(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _o /*! + * \brief Query table for specified rows and lock them + * \param _h structure representing database connection + * \param _k key names + * \param _op operators + * \param _v values of the keys that must match + * \param _c column names to return + * \param _n nmber of key=values pairs to compare + * \param _nc number of columns to return + * \param _o order by the specified column + * \param _r result set + * \return 0 on success, negative on failure + */ +int db_postgres_query_lock(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _op, + const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc, + const db_key_t _o, db1_res_t** _r) +{ + if (CON_TRANSACTION(_h) == 0) + { + LM_ERR("transaction not in progress\n"); + return -1; + } + return db_do_query_lock(_h, _k, _op, _v, _c, _n, _nc, _o, _r, db_postgres_val2str, + db_postgres_submit_query, db_postgres_store_result); +} + + +/*! * Execute a raw SQL query * \param _h database connection * \param _s raw query string @@ -658,10 +685,15 @@ int db_postgres_affected_rows(const db1_con_t* _h) * \param _h database handle * \return 0 on success, negative on failure */ -int db_postgres_start_transaction(db1_con_t* _h) +int db_postgres_start_transaction(db1_con_t* _h, db_locking_t _l) { db1_res_t *res = NULL; - str query_str = str_init("BEGIN"); + str begin_str = str_init("BEGIN"); + str lock_start_str = str_init("LOCK TABLE "); + str lock_write_end_str = str_init(" IN EXCLUSIVE MODE"); + str lock_full_end_str = str_init(" IN ACCESS EXCLUSIVE MODE"); + str *lock_end_str = &lock_write_end_str; + str lock_str = {0, 0}; if (!_h) { LM_ERR("invalid parameter value\n"); @@ -673,7 +705,7 @@ int db_postgres_start_transaction(db1_con_t* _h) return -1; } - if (db_postgres_raw_query(_h, &query_str, &res) < 0) + if (db_postgres_raw_query(_h, &begin_str, &res) < 0) { LM_ERR("executing raw_query\n"); return -1; @@ -682,7 +714,49 @@ int db_postgres_start_transaction(db1_con_t* _h) if (res) db_postgres_free_result(_h, res); CON_TRANSACTION(_h) = 1; + + switch(_l) + { + case DB_LOCKING_NONE: + break; + case DB_LOCKING_FULL: + lock_end_str = &lock_full_end_str; + /* Fall-thru */ + case DB_LOCKING_WRITE: + if ((lock_str.s = pkg_malloc((lock_start_str.len + CON_TABLE(_h)->len + lock_end_str->len) * sizeof(char))) == NULL) + { + LM_ERR("allocating pkg memory\n"); + goto error; + } + + memcpy(lock_str.s, lock_start_str.s, lock_start_str.len); + lock_str.len += lock_start_str.len; + memcpy(lock_str.s + lock_str.len, CON_TABLE(_h)->s, CON_TABLE(_h)->len); + lock_str.len += CON_TABLE(_h)->len; + memcpy(lock_str.s + lock_str.len, lock_end_str->s, lock_end_str->len); + lock_str.len += lock_end_str->len; + + if (db_postgres_raw_query(_h, &lock_str, &res) < 0) + { + LM_ERR("executing raw_query\n"); + goto error; + } + + if (res) db_postgres_free_result(_h, res); + if (lock_str.s) pkg_free(lock_str.s); + break; + + default: + LM_WARN("unrecognised lock type\n"); + goto error; + } + return 0; + +error: + if (lock_str.s) pkg_free(lock_str.s); + db_postgres_abort_transaction(_h); + return -1; } /** diff --git a/modules/db_postgres/km_dbase.h b/modules/db_postgres/km_dbase.h index 4598139..24ae484 100644 --- a/modules/db_postgres/km_dbase.h +++ b/modules/db_postgres/km_dbase.h @@ -75,6 +75,15 @@ int db_postgres_query(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _o const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc, const db_key_t _o, db1_res_t** _r); + +/* + * Do a query and lock rows for update + */ +int db_postgres_query_lock(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _op, + const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc, + const db_key_t _o, db1_res_t** _r); + + /* * Raw SQL query */ @@ -115,7 +124,7 @@ int db_postgres_affected_rows(const db1_con_t* _h); /* * SQL BEGIN */ -int db_postgres_start_transaction(db1_con_t* _h); +int db_postgres_start_transaction(db1_con_t* _h, db_locking_t _l); /* * SQL COMMIT