[sr-dev] git:master: modules/db_postgres: Added implementation of (start|end|abort) _transaction DB APIs

[Peter Dunkley]

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

Author: Peter Dunkley <peter.dunkley at crocodile-rcs.com>
Committer: Peter Dunkley <peter.dunkley at crocodile-rcs.com>
Date:   Tue Apr 24 10:53:24 2012 +0100

modules/db_postgres: Added implementation of (start|end|abort)_transaction DB APIs

---

 modules/db_postgres/km_db_postgres.c |    3 +
 modules/db_postgres/km_dbase.c       |  101 ++++++++++++++++++++++++++++++++++
 modules/db_postgres/km_dbase.h       |   15 +++++
 modules/db_postgres/km_pg_con.h      |    2 +
 4 files changed, 121 insertions(+), 0 deletions(-)

diff --git a/modules/db_postgres/km_db_postgres.c b/modules/db_postgres/km_db_postgres.c
index f56a2ff..34edca7 100644
--- a/modules/db_postgres/km_db_postgres.c
+++ b/modules/db_postgres/km_db_postgres.c
@@ -96,6 +96,9 @@ int db_postgres_bind_api(db_func_t *dbb)
 	dbb->update           = db_postgres_update;
 	dbb->replace          = db_postgres_replace;
 	dbb->affected_rows    = db_postgres_affected_rows;
+	dbb->start_transaction= db_postgres_start_transaction;
+	dbb->end_transaction  = db_postgres_end_transaction;
+	dbb->abort_transaction= db_postgres_abort_transaction;
 
 	return 0;
 }
diff --git a/modules/db_postgres/km_dbase.c b/modules/db_postgres/km_dbase.c
index 5e79d52..101cc8e 100644
--- a/modules/db_postgres/km_dbase.c
+++ b/modules/db_postgres/km_dbase.c
@@ -624,6 +624,107 @@ int db_postgres_affected_rows(const db1_con_t* _h)
 	return CON_AFFECTED(_h);
 }
 
+/**
+ * Starts a single transaction that will consist of one or more queries (SQL BEGIN)
+ * \param _h database handle
+ * \return 0 on success, negative on failure
+ */
+int db_postgres_start_transaction(db1_con_t* _h)
+{
+	db1_res_t *res = NULL;
+	str query_str = str_init("BEGIN");
+	
+	if (!_h) {
+		LM_ERR("invalid parameter value\n");
+		return -1;
+	}
+
+	if (CON_TRANSACTION(_h) == 1) {
+		LM_ERR("transaction already started\n");
+		return -1;
+	}
+
+	if (db_postgres_raw_query(_h, &query_str, &res) < 0)
+	{
+		LM_ERR("executing raw_query\n");
+		return -1;
+	}
+
+	if (res) db_postgres_free_result(_h, res);
+
+	CON_TRANSACTION(_h) = 1;
+	return 0;
+}
+
+/**
+ * Ends a transaction and commits the changes (SQL COMMIT)
+ * \param _h database handle
+ * \return 0 on success, negative on failure
+ */
+int db_postgres_end_transaction(db1_con_t* _h)
+{
+	db1_res_t *res = NULL;
+	str query_str = str_init("COMMIT");
+	
+	if (!_h) {
+		LM_ERR("invalid parameter value\n");
+		return -1;
+	}
+
+	if (CON_TRANSACTION(_h) == 0) {
+		LM_ERR("transaction not in progress\n");
+		return -1;
+	}
+
+	if (db_postgres_raw_query(_h, &query_str, &res) < 0)
+	{
+		LM_ERR("executing raw_query\n");
+		return -1;
+	}
+
+	if (res) db_postgres_free_result(_h, res);
+
+	/* Only _end_ the transaction after the raw_query.  That way, if the
+ 	   raw_query fails, and the calling module does an abort_transaction()
+	   to clean-up, a ROLLBACK will be sent to the DB. */
+	CON_TRANSACTION(_h) = 0;
+	return 0;
+}
+
+/**
+ * Ends a transaction and rollsback the changes (SQL ROLLBACK)
+ * \param _h database handle
+ * \return 1 if there was something to rollback, 0 if not, negative on failure
+ */
+int db_postgres_abort_transaction(db1_con_t* _h)
+{
+	db1_res_t *res = NULL;
+	str query_str = str_init("ROLLBACK");
+	
+	if (!_h) {
+		LM_ERR("invalid parameter value\n");
+		return -1;
+	}
+
+	if (CON_TRANSACTION(_h) == 0) {
+		LM_DBG("nothing to rollback\n");
+		return 0;
+	}
+
+	/* Whether the rollback succeeds or not we need to _end_ the
+ 	   transaction now or all future starts will fail */
+	CON_TRANSACTION(_h) = 0;
+
+	if (db_postgres_raw_query(_h, &query_str, &res) < 0)
+	{
+		LM_ERR("executing raw_query\n");
+		return -1;
+	}
+
+	if (res) db_postgres_free_result(_h, res);
+
+	return 1;
+}
 
 /*!
  * Store name of table that will be used by subsequent database functions
diff --git a/modules/db_postgres/km_dbase.h b/modules/db_postgres/km_dbase.h
index 60c824b..4598139 100644
--- a/modules/db_postgres/km_dbase.h
+++ b/modules/db_postgres/km_dbase.h
@@ -113,6 +113,21 @@ int db_postgres_fetch_result(const db1_con_t* _h, db1_res_t** _r, const int nrow
 int db_postgres_affected_rows(const db1_con_t* _h);
 
 /*
+ * SQL BEGIN
+ */
+int db_postgres_start_transaction(db1_con_t* _h);
+
+/*
+ * SQL COMMIT
+ */
+int db_postgres_end_transaction(db1_con_t* _h);
+
+/*
+ * SQL ROLLBACK
+ */
+int db_postgres_abort_transaction(db1_con_t* _h);
+
+/*
  * Store name of table that will be used by
  * subsequent database functions
  */
diff --git a/modules/db_postgres/km_pg_con.h b/modules/db_postgres/km_pg_con.h
index b0f92c6..bfc2638 100644
--- a/modules/db_postgres/km_pg_con.h
+++ b/modules/db_postgres/km_pg_con.h
@@ -54,6 +54,7 @@ struct pg_con {
 	char**  row;		/*!< Actual row in the result */
 	time_t timestamp;	/*!< Timestamp of last query */
 	int affected_rows;	/*!< Number of rows affected by the last statement */
+	int transaction;	/*!< indicates whether a multi-query transaction is currently open */
 };
 
 #define CON_SQLURL(db_con)     (((struct pg_con*)((db_con)->tail))->sqlurl)
@@ -64,6 +65,7 @@ struct pg_con {
 #define CON_TIMESTAMP(db_con)  (((struct pg_con*)((db_con)->tail))->timestamp)
 #define CON_ID(db_con) 	       (((struct pg_con*)((db_con)->tail))->id)
 #define CON_AFFECTED(db_con)   (((struct pg_con*)((db_con)->tail))->affected_rows)
+#define CON_TRANSACTION(db_con) (((struct pg_con*)((db_con)->tail))->transaction)
 
 /*
  * Create a new connection structure,