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 <peter.dunkley(a)crocodile-rcs.com>
Committer: Peter Dunkley <peter.dunkley(a)crocodile-rcs.com>
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