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

[Peter Dunkley]

Module: sip-router
Branch: master
Commit: f1f39db4f8a73f16497c4cc2658e9b4e68bac0fc
URL:    http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=f1f39db4f8a73f16497c4cc2658e9b4e68bac0fc

Author: Peter Dunkley <peter.dunkley at crocodile-rcs.com>
Committer: Peter Dunkley <peter.dunkley at 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