dbase.c

用来作为linux中SIP SERVER,完成VOIP网络电话中服务器的功能

/*
** begin_transaction	begin transaction
**
**	Arguments :
**		db_con_t *	as previously supplied by db_init()
**		char *		this is just in case an error message
**				is printed, we will know which query
**				was going to be run, giving us a code debug hint
**
**	Returns :
**		0 upon success
**		negative number upon failure
**
**	Notes :
**		This function may be called with a messed up communication
**		channel.  Therefore, alot of this function is dealing with
**		that.  Wen the layering gets corrected later this stuff
**		should continue to work correctly, it will just be
**		way to defensive.
*/

static int begin_transaction(db_con_t * _h, char *_s)
{
	PGresult *mr;
	int rv;

	/*
	** Note:
	**  The upper layers of code may attempt a transaction
	**  before opening or having a valid connection to the
	**  database.  We try to sense this, and open the database
	**  if we have the sqlurl in the _h structure.  Otherwise,
	**  all we can do is return an error.
	*/

	if(_h)
	{
		if(CON_CONNECTED(_h))
		{
			mr = PQexec(CON_CONNECTION(_h), "BEGIN");
			if(!mr || PQresultStatus(mr) != PGRES_COMMAND_OK)
			{
				/*
				** We get here if the connection to the
				** db is corrupt, which can happen a few
				** different ways, but all of them are
				** related to the parent process forking,
				** or being forked.
				*/
				PLOG("begin_transaction","corrupt connection");
				CON_CONNECTED(_h) = 0;
			}
			else
			{
				/*
				** this is the normal way out.
				** the transaction ran fine.
				*/
				PQclear(mr);
				return(0);
			}
		}
		else
		{
			DLOG("begin_transaction", "called before db_init");
		}

		/*
		** if we get here we have a corrupt db connection,
		** but we probably have a valid db_con_t structure.
		** attempt to open the db.
		*/

		if((rv = connect_db(_h, CON_SQLURL(_h))) != 0)
		{
			/*
			** our attempt to fix the connection failed
			*/
			char buf[256];
			sprintf(buf, "no connection, FATAL %d!", rv);
			PLOG("begin_transaction",buf);
			return(rv);
		}
	}
	else
	{
		PLOG("begin_transaction","must call db_init first!");
		return(-1);
	}

	/*
	** we get here if the database connection was corrupt,
	** i didn't want to use recursion ...
	*/

	mr = PQexec(CON_CONNECTION(_h), "BEGIN");
	if(!mr || PQresultStatus(mr) != PGRES_COMMAND_OK)
	{
		char buf[256];
		sprintf("FATAL %s, '%s'!\n",
			PQerrorMessage(CON_CONNECTION(_h)), _s);
		PLOG("begin_transaction", buf);
		return(-1);
	}

	DLOG("begin_transaction", "db channel reset successful");

	PQclear(mr);
	return(0);
}

/*
** commit_transaction	any begin_transaction must be terminated with this
**
**	Arguments :
**		db_con_t *	as previously supplied by db_init()
**
**	Returns :
**		0 upon success
**		negative number upon failure
*/

static int commit_transaction(db_con_t * _h)
{
	PGresult *mr;

	mr = PQexec(CON_CONNECTION(_h), "COMMIT");
	if(!mr || PQresultStatus(mr) != PGRES_COMMAND_OK)
	{
		PLOG("commit_transaction", "error");
		return -1;
	}
	PQclear(mr);
	return(0);
}

/*
 * Print list of columns separated by comma
 */
static int print_columns(char* _b, int _l, db_key_t* _c, int _n)
{
	int i;
	int res = 0;
	for(i = 0; i < _n; i++) {
		if (i == (_n - 1)) {
			res += snprintf(_b + res, _l - res, "%s ", _c[i]);
		} else {
			res += snprintf(_b + res, _l - res, "%s,", _c[i]);
		}
	}
	return res;
}


/*
 * Print list of values separated by comma
 */
static int print_values(char* _b, int _l, db_val_t* _v, int _n)
{
	int i, res = 0, l;

	for(i = 0; i < _n; i++) {
		l = _l - res;
/*		LOG(L_ERR, "%d sizes l = _l - res %d = %d - %d\n", i, l,_l,res);
*/
		if (val2str(_v + i, _b + res, &l) < 0) {
			LOG(L_ERR,
			 "print_values(): Error converting value to string\n");
			return 0;
		}
		res += l;
		if (i != (_n - 1)) {
			*(_b + res) = ',';
			res++;
		}
	}
	return res;
}


/*
 * Print where clause of SQL statement
 */
static int print_where(char* _b, int _l, db_key_t* _k,
	db_op_t* _o, db_val_t* _v, int _n)
{
	int i;
	int res = 0;
	int l;
	for(i = 0; i < _n; i++) {
		if (_o) {
			res += snprintf(_b + res, _l - res, "%s%s",
				_k[i], _o[i]);
		} else {
			res += snprintf(_b + res, _l - res, "%s=", _k[i]);
		}
		l = _l - res;
		val2str(&(_v[i]), _b + res, &l);
		res += l;
		if (i != (_n - 1)) {
			res += snprintf(_b + res, _l - res, " AND ");
		}
	}
	return res;
}


/*
 * Print set clause of update SQL statement
 */
static int print_set(char* _b, int _l, db_key_t* _k,
	db_val_t* _v, int _n)
{
	int i;
	int res = 0;
	int l;
	for(i = 0; i < _n; i++) {
		res += snprintf(_b + res, _l - res, "%s=", _k[i]);
		l = _l - res;
		val2str(&(_v[i]), _b + res, &l);
		res += l;
		if (i != (_n - 1)) {
			if ((_l - res) >= 1) {
				*(_b + res++) = ',';
			}
		}
	}
	return res;
}


/*
 * Query table for specified rows
 * _h: structure representing database connection
 * _k: key names
 * _op: operators
 * _v: values of the keys that must match
 * _c: column names to return
 * _n: nmber of key=values pairs to compare
 * _nc: number of columns to return
 * _o: order by the specified column
 */
int db_query(db_con_t* _h, db_key_t* _k, db_op_t* _op,
	     db_val_t* _v, db_key_t* _c, int _n, int _nc,
	     db_key_t _o, db_res_t** _r)
{
	int off, rv;
	if (!_c) {
		off = snprintf(sql_buf, SQL_BUF_LEN,
			"select * from %s ", CON_TABLE(_h));
	} else {
		off = snprintf(sql_buf, SQL_BUF_LEN, "select ");
		off += print_columns(sql_buf + off, SQL_BUF_LEN - off, _c, _nc);
		off += snprintf(sql_buf + off, SQL_BUF_LEN - off,
			"from %s ", CON_TABLE(_h));
	}
	if (_n) {
		off += snprintf(sql_buf + off, SQL_BUF_LEN - off, "where ");
		off += print_where(sql_buf + off, SQL_BUF_LEN - off,
			_k, _op, _v, _n);
	}
	if (_o) {
		off += snprintf(sql_buf + off, SQL_BUF_LEN - off,
			"order by %s", _o);
	}

	if(begin_transaction(_h, sql_buf)) return(-1);
	if (submit_query(_h, sql_buf) < 0) {
		LOG(L_ERR, "db_query(): Error while submitting query\n");
		return -2;
	}
	rv = get_result(_h, _r);
	free_query(_h);
	commit_transaction(_h);
	return(rv);
}


/*
 * Execute a raw SQL query
 */
int db_raw_query(db_con_t* _h, char* _s, db_res_t** _r)
{
	int rv;

	if(begin_transaction(_h, sql_buf)) return(-1);
	if (submit_query(_h, _s) < 0) {
		LOG(L_ERR, "db_raw_query(): Error while submitting query\n");
		return -2;
	}
	rv = get_result(_h, _r);
	free_query(_h);
	commit_transaction(_h);
	return(rv);
}

/*
 * Retrieve result set
 */
int get_result(db_con_t* _h, db_res_t** _r)
{
	*_r = new_result_pg(CON_SQLURL(_h));

	if (!CON_RESULT(_h)) {
		LOG(L_ERR, "get_result(): error");
		free_result(*_r);
		*_r = 0;
		return -3;
	}

        if (convert_result(_h, *_r) < 0) {
		LOG(L_ERR, "get_result(): Error while converting result\n");
		free_result(*_r);
		*_r = 0;

		return -4;
	}

	return 0;
}

/*
 * Insert a row into specified table
 * _h: structure representing database connection
 * _k: key names
 * _v: values of the keys
 * _n: number of key=value pairs
 */
int db_insert(db_con_t* _h, db_key_t* _k, db_val_t* _v, int _n)
{
	int off;
	off = snprintf(sql_buf, SQL_BUF_LEN, "insert into %s (", CON_TABLE(_h));
	off += print_columns(sql_buf + off, SQL_BUF_LEN - off, _k, _n);
	off += snprintf(sql_buf + off, SQL_BUF_LEN - off, ") values (");
	off += print_values(sql_buf + off, SQL_BUF_LEN - off, _v, _n);
	*(sql_buf + off++) = ')';
	*(sql_buf + off) = '\0';

	if(begin_transaction(_h, sql_buf)) return(-1);
	if (submit_query(_h, sql_buf) < 0) {
		LOG(L_ERR, "db_insert(): Error while inserting\n");
		return -2;
	}
	free_query(_h);
	commit_transaction(_h);
	return(0);
}


/*
 * Delete a row from the specified table
 * _h: structure representing database connection
 * _k: key names
 * _o: operators
 * _v: values of the keys that must match
 * _n: number of key=value pairs
 */
int db_delete(db_con_t* _h, db_key_t* _k, db_op_t* _o, db_val_t* _v, int _n)
{
	int off;
	off = snprintf(sql_buf, SQL_BUF_LEN, "delete from %s", CON_TABLE(_h));
	if (_n) {
		off += snprintf(sql_buf + off, SQL_BUF_LEN - off, " where ");
		off += print_where(sql_buf + off, SQL_BUF_LEN - off, _k,
			_o, _v, _n);
	}
	if(begin_transaction(_h, sql_buf)) return(-1);
	if (submit_query(_h, sql_buf) < 0) {
		LOG(L_ERR, "db_delete(): Error while deleting\n");
		return -2;
	}
	free_query(_h);
	commit_transaction(_h);
	return(0);
}


/*
 * Update some rows in the specified table
 * _h: structure representing database connection
 * _k: key names
 * _o: operators
 * _v: values of the keys that must match
 * _uk: updated columns
 * _uv: updated values of the columns
 * _n: number of key=value pairs
 * _un: number of columns to update
 */
int db_update(db_con_t* _h, db_key_t* _k, db_op_t* _o, db_val_t* _v,
	      db_key_t* _uk, db_val_t* _uv, int _n, int _un)
{
	int off;
	off = snprintf(sql_buf, SQL_BUF_LEN, "update %s set ", CON_TABLE(_h));
	off += print_set(sql_buf + off, SQL_BUF_LEN - off, _uk, _uv, _un);
	if (_n) {
		off += snprintf(sql_buf + off, SQL_BUF_LEN - off, " where ");
		off += print_where(sql_buf + off, SQL_BUF_LEN - off, _k,
			_o, _v, _n);
		*(sql_buf + off) = '\0';
	}

	if(begin_transaction(_h, sql_buf)) return(-1);
	if (submit_query(_h, sql_buf) < 0) {
		LOG(L_ERR, "db_update(): Error while updating\n");
		return -2;
	}
	free_query(_h);
	commit_transaction(_h);
	return(0);
}