[sr-dev] git:master: db_mysql Add transaction support

Module: sip-router Branch: master Commit: fe977dfcb940b1c48dd26fe5fe61a25aed23b96a URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=fe977df… Author: Olle E. Johansson &lt;oej(a)edvina.net&gt; Committer: Olle E. Johansson &lt;oej(a)edvina.net&gt; Date: Tue Apr 2 14:13:01 2013 +0200 db_mysql Add transaction support Patch contributed via Google+ by Håkon Nassjöen &lt;haakon.nassjoen(a)gmail.com&gt; --- modules/db_mysql/km_db_mysql.c | 3 + modules/db_mysql/km_dbase.c | 136 ++++++++++++++++++++++++++++++++++++++++ modules/db_mysql/km_dbase.h | 16 +++++ modules/db_mysql/km_my_con.h | 10 ++- 4 files changed, 161 insertions(+), 4 deletions(-) diff --git a/modules/db_mysql/km_db_mysql.c b/modules/db_mysql/km_db_mysql.c index 8a51f3d..88cc481 100644 --- a/modules/db_mysql/km_db_mysql.c +++ b/modules/db_mysql/km_db_mysql.c @@ -116,6 +116,9 @@ int db_mysql_bind_api(db_func_t *dbb) dbb->insert_update = db_mysql_insert_update; dbb->insert_delayed = db_mysql_insert_delayed; dbb->affected_rows = db_mysql_affected_rows; + dbb->start_transaction= db_mysql_start_transaction; + dbb->end_transaction = db_mysql_end_transaction; + dbb->abort_transaction= db_mysql_abort_transaction; return 0; } diff --git a/modules/db_mysql/km_dbase.c b/modules/db_mysql/km_dbase.c index 029a674..7288b66 100644 --- a/modules/db_mysql/km_dbase.c +++ b/modules/db_mysql/km_dbase.c @@ -500,6 +500,142 @@ int db_mysql_affected_rows(const db1_con_t* _h) return (int)mysql_affected_rows(CON_CONNECTION(_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_mysql_start_transaction(db1_con_t* _h, db_locking_t _l) +{ + str begin_str = str_init("BEGIN"); + str lock_start_str = str_init("LOCK TABLE "); + str lock_end_str = str_init(" WRITE"); + str lock_str = {0, 0}; + + 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_mysql_raw_query(_h, &begin_str, NULL) < 0) + { + LM_ERR("executing raw_query\n"); + return -1; + } + + CON_TRANSACTION(_h) = 1; + + switch(_l) + { + case DB_LOCKING_NONE: + break; + case DB_LOCKING_FULL: + /* 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_mysql_raw_query(_h, &lock_str, NULL) < 0) + { + LM_ERR("executing raw_query\n"); + goto error; + } + + 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_mysql_abort_transaction(_h); + return -1; +} + +/** + * Ends a transaction and commits the changes (SQL COMMIT) + * \param _h database handle + * \return 0 on success, negative on failure + */ +int db_mysql_end_transaction(db1_con_t* _h) +{ + 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_mysql_raw_query(_h, &query_str, NULL) < 0) + { + LM_ERR("executing raw_query\n"); + return -1; + } + + /* 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_mysql_abort_transaction(db1_con_t* _h) +{ + 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_mysql_raw_query(_h, &query_str, NULL) < 0) + { + LM_ERR("executing raw_query\n"); + return -1; + } + + return 1; +} + /** * Insert a row into a specified table, update on duplicate key. diff --git a/modules/db_mysql/km_dbase.h b/modules/db_mysql/km_dbase.h index 3a567f2..52b2778 100644 --- a/modules/db_mysql/km_dbase.h +++ b/modules/db_mysql/km_dbase.h @@ -40,6 +40,7 @@ #include "../../lib/srdb1/db_key.h" #include "../../lib/srdb1/db_op.h" #include "../../lib/srdb1/db_val.h" +#include "../../lib/srdb1/db_locking.h" #include "../../str.h" /*! \brief @@ -118,6 +119,21 @@ int db_mysql_last_inserted_id(const db1_con_t* _h); */ int db_mysql_affected_rows(const db1_con_t* _h); +/*! \brief + * Starts transaction + */ +int db_mysql_start_transaction(db1_con_t* _h, db_locking_t _l); + +/*! \brief + * Commits transaction + */ +int db_mysql_end_transaction(db1_con_t* _h); + +/*! \brief + * Aborts transaction + */ +int db_mysql_abort_transaction(db1_con_t* _h); + /*! \brief * Insert a row into table, update on duplicate key diff --git a/modules/db_mysql/km_my_con.h b/modules/db_mysql/km_my_con.h index 2624f34..33f2128 100644 --- a/modules/db_mysql/km_my_con.h +++ b/modules/db_mysql/km_my_con.h @@ -48,16 +48,18 @@ struct my_con { MYSQL* con; /*!< Connection representation */ MYSQL_ROW row; /*!< Actual row in the result */ time_t timestamp; /*!< Timestamp of last query */ + int transaction; /*!< indicates whether a multi-query transaction is currently open */ }; /* * Some convenience wrappers */ -#define CON_RESULT(db_con) (((struct my_con*)((db_con)->tail))->res) -#define CON_CONNECTION(db_con) (((struct my_con*)((db_con)->tail))->con) -#define CON_ROW(db_con) (((struct my_con*)((db_con)->tail))->row) -#define CON_TIMESTAMP(db_con) (((struct my_con*)((db_con)->tail))->timestamp) +#define CON_RESULT(db_con) (((struct my_con*)((db_con)->tail))->res) +#define CON_CONNECTION(db_con) (((struct my_con*)((db_con)->tail))->con) +#define CON_ROW(db_con) (((struct my_con*)((db_con)->tail))->row) +#define CON_TIMESTAMP(db_con) (((struct my_con*)((db_con)->tail))->timestamp) +#define CON_TRANSACTION(db_con) (((struct my_con*)((db_con)->tail))->transaction) /*! \brief