/**
* 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;
}
/**
* 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 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 rollback_query_str = str_init("ROLLBACK");
str set_query_str = str_init("SET autocommit=1");
int ret;
if (!_h) {
LM_ERR("invalid parameter value\n");
return -1;
}
if (CON_TRANSACTION(_h) == 0) {
LM_DBG("nothing to rollback\n");
ret = 0;
goto done;
}
/* 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, &rollback_query_str, NULL) < 0)
{
LM_ERR("executing raw_query\n");
ret = -1;
goto done;
}
if (db_mysql_raw_query(_h, &set_query_str, NULL) < 0)
{
LM_ERR("executing raw_query\n");
ret = -1;
goto done;
}
ret = 1;
done:
db_mysql_unlock_tables(_h);
return ret;
}
/*!
* \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);
}
/**
* 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 commit_query_str = str_init("COMMIT");
str set_query_str = str_init("SET autocommit=1");
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, &commit_query_str, NULL) < 0)
{
LM_ERR("executing raw_query\n");
return -1;
}
if (db_mysql_raw_query(_h, &set_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;
if(db_mysql_unlock_tables(_h)<0)
return -1;
return 0;
}
/**
* 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("SET autocommit=0");
str lock_start_str = str_init("LOCK TABLES ");
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);
CON_LOCKEDTABLES(_h) = 1;
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;
}
/*!
* \brief Submit_query, run a query
* \param _con database connection
* \param _s query string
* \return 0 on success, negative on failure
*/
static int db_postgres_submit_query(const db1_con_t* _con, const str* _s)
{
int i, retries;
ExecStatusType pqresult;
if(! _con || !_s || !_s->s)
{
LM_ERR("invalid parameter value\n");
return(-1);
}
/* this bit of nonsense in case our connection get screwed up */
switch(PQstatus(CON_CONNECTION(_con)))
{
case CONNECTION_OK:
break;
case CONNECTION_BAD:
LM_DBG("connection reset\n");
PQreset(CON_CONNECTION(_con));
break;
case CONNECTION_STARTED:
case CONNECTION_MADE:
case CONNECTION_AWAITING_RESPONSE:
case CONNECTION_AUTH_OK:
case CONNECTION_SETENV:
case CONNECTION_SSL_STARTUP:
case CONNECTION_NEEDED:
default:
LM_ERR("%p PQstatus(%s) invalid: %.*s\n", _con,
PQerrorMessage(CON_CONNECTION(_con)), _s->len, _s->s);
return -1;
}
if (CON_TRANSACTION(_con) == 1)
retries = 0;
else
retries = pg_retries;
for(i = 0; i <= retries; i++) {
/* free any previous query that is laying about */
db_postgres_free_query(_con);
/* exec the query */
if (PQsendQuery(CON_CONNECTION(_con), _s->s)) {
pqresult = PQresultStatus(CON_RESULT(_con));
if((pqresult!=PGRES_FATAL_ERROR)
|| (PQstatus(CON_CONNECTION(_con))==CONNECTION_OK))
{
LM_DBG("sending query ok: %p (%d) - [%.*s]\n",
_con, pqresult, _s->len, _s->s);
return 0;
}
LM_WARN("postgres result check failed with code %d (%s)\n",
pqresult, PQresStatus(pqresult));
}
LM_WARN("postgres query command failed, connection status %d,"
" error [%s]\n", PQstatus(CON_CONNECTION(_con)),
PQerrorMessage(CON_CONNECTION(_con)));
if(PQstatus(CON_CONNECTION(_con))!=CONNECTION_OK)
{
LM_DBG("reseting the connection to postgress server\n");
PQreset(CON_CONNECTION(_con));
}
}
LM_ERR("%p PQsendQuery Error: %s Query: %.*s\n", _con,
PQerrorMessage(CON_CONNECTION(_con)), _s->len, _s->s);
return -1;
}
/*!
* \brief Submit_query, run a query
* \param _con database connection
* \param _s query string
* \return 0 on success, negative on failure
*/
static int db_postgres_submit_query(const db1_con_t* _con, const str* _s)
{
char *s=NULL;
int i, retries;
ExecStatusType pqresult;
PGresult *res = NULL;
int sock, ret;
fd_set fds;
time_t max_time;
struct timeval wait_time;
if(! _con || !_s || !_s->s)
{
LM_ERR("invalid parameter value\n");
return(-1);
}
/* this bit of nonsense in case our connection get screwed up */
switch(PQstatus(CON_CONNECTION(_con)))
{
case CONNECTION_OK:
break;
case CONNECTION_BAD:
LM_DBG("connection reset\n");
PQreset(CON_CONNECTION(_con));
break;
case CONNECTION_STARTED:
case CONNECTION_MADE:
case CONNECTION_AWAITING_RESPONSE:
case CONNECTION_AUTH_OK:
case CONNECTION_SETENV:
case CONNECTION_SSL_STARTUP:
case CONNECTION_NEEDED:
default:
LM_ERR("%p PQstatus(%s) invalid: %.*s\n", _con,
PQerrorMessage(CON_CONNECTION(_con)), _s->len, _s->s);
return -1;
}
if (CON_TRANSACTION(_con) == 1)
retries = 0;
else
retries = pg_retries;
s = pkg_malloc((_s->len+1)*sizeof(char));
if (s==NULL)
{
LM_ERR("%p db_postgres_submit_query Out of Memory: Query: %.*s\n", _con, _s->len, _s->s);
return -1;
}
memcpy( s, _s->s, _s->len );
s[_s->len] = '\0';
for(i = 0; i <= retries; i++) {
/* free any previous query that is laying about */
db_postgres_free_query(_con);
/* exec the query */
if (PQsendQuery(CON_CONNECTION(_con), s)) {
if (pg_timeout <= 0)
goto do_read;
max_time = time(NULL) + pg_timeout;
while (1) {
sock = PQsocket(CON_CONNECTION(_con));
FD_ZERO(&fds);
FD_SET(sock, &fds);
wait_time.tv_usec = 0;
wait_time.tv_sec = max_time - time(NULL);
if (wait_time.tv_sec <= 0 || wait_time.tv_sec > 0xffffff)
goto timeout;
ret = select(sock + 1, &fds, NULL, NULL, &wait_time);
if (ret < 0) {
if (errno == EINTR)
continue;
LM_WARN("select() error\n");
goto reset;
}
if (!ret) {
timeout:
LM_WARN("timeout waiting for postgres reply\n");
goto reset;
}
if (!PQconsumeInput(CON_CONNECTION(_con))) {
LM_WARN("error reading data from postgres server: %s\n",
PQerrorMessage(CON_CONNECTION(_con)));
goto reset;
}
if (!PQisBusy(CON_CONNECTION(_con)))
break;
}
do_read:
/* Get the result of the query */
while ((res = PQgetResult(CON_CONNECTION(_con))) != NULL) {
db_postgres_free_query(_con);
CON_RESULT(_con) = res;
}
pqresult = PQresultStatus(CON_RESULT(_con));
if((pqresult!=PGRES_FATAL_ERROR)
&& (PQstatus(CON_CONNECTION(_con))==CONNECTION_OK))
{
LM_DBG("sending query ok: %p (%d) - [%.*s]\n",
_con, pqresult, _s->len, _s->s);
pkg_free(s);
return 0;
}
LM_WARN("postgres result check failed with code %d (%s)\n",
pqresult, PQresStatus(pqresult));
}
LM_WARN("postgres query command failed, connection status %d,"
" error [%s]\n", PQstatus(CON_CONNECTION(_con)),
PQerrorMessage(CON_CONNECTION(_con)));
if(PQstatus(CON_CONNECTION(_con))!=CONNECTION_OK)
{
reset:
LM_DBG("resetting the connection to postgress server\n");
PQreset(CON_CONNECTION(_con));
}
}
LM_ERR("%p PQsendQuery Error: %s Query: %.*s\n", _con,
PQerrorMessage(CON_CONNECTION(_con)), _s->len, _s->s);
pkg_free(s);
return -1;
}
/**
* 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, db_locking_t _l)
{
db1_res_t *res = NULL;
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");
return -1;
}
if (CON_TRANSACTION(_h) == 1) {
LM_ERR("transaction already started\n");
return -1;
}
if (db_postgres_raw_query(_h, &begin_str, &res) < 0)
{
LM_ERR("executing raw_query\n");
return -1;
}
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;
}
