db_operate.c

SQILTE3的一些基本函数的封装,包括文件的创建,打开,添加记录,查询记录,删除记录,计算记录条数和总数

#include <include.h>
#include <sqlite3.h>
#include <file_operate.h>
#include <db_operate.h>
#include <error.h>
#include <udplog.h>
#include <common.h>
#include <common_utils.h>

extern char gErrMsg[256];

#define	DB_RETRY		5
#define	DB_SLEEP		(100*1000)




/* used int  internal */
static int DbSelectCount(T_DbOper *pDB,  char *fullsql)
{
	char            *sTail = 0;
	sqlite3_stmt    *stmt = 0;
	int             i = 0;	
	int             ret = -1;
	int				finish;

	if(sqlite3_prepare(pDB->db, fullsql, -1, &stmt, &sTail) != SQLITE_OK)
	{
		udplog ("<SQLITE>%s", fullsql);
		udplog("<SQLITE>Error in DbSelectCount A:%s", sqlite3_errmsg(pDB->db));
		return E_DB_PREPARE;
	}	

	for (i=0, finish=0; i<DB_RETRY; i++)
	{
		switch(sqlite3_step(stmt))
		{
			case SQLITE_ROW:
				if(sqlite3_column_count(stmt) <= 0) 
				{
					ret = E_DB_NOCLOUMN; 
					finish = 1;
					break;
				}
				ret = sqlite3_column_int(stmt, 0);
				sqlite3_finalize(stmt);
				return ret;
		
			case SQLITE_BUSY:
				ret = E_DB_BUSY; 
				break;
				
			case SQLITE_DONE:
			    ret = E_DB_SELECTDONE;
			    finish = 1;
			    break;				
			case SQLITE_ERROR:
			case SQLITE_MISUSE:
			default:
				ret = E_DB_RECSET;
				break;
		}
		if (finish == 1)
		{
			break;
		}
	}

	sqlite3_finalize(stmt);
	udplog ("<SQLITE>%s", fullsql);
	udplog("<SQLITE>Error in DbSelectCount B:%s", sqlite3_errmsg(pDB->db));
	return ret;
    
}

#if 0
int DbCreat (T_DbOper *pDb, char *file)
{
	char	lock[200];
	char *p;
	
	snprintf (lock, sizeof(lock), "%s", file);
	
	if (( p = strstr (lock, DB_FILE_EXTEND)) == NULL)
	{
		udplog ("<SQLITE>%s", "the file name is error");
		return E_DB_WRONG_FILE;
	}
	snprintf (p, sizeof(lock)-(p-lock), "%s", LOCK_FILE_EXTEND);

	if (CheckExist (file) == 0)		//not exist
	{
		udplog ("<SQLITE>%s not exist, will creat it", file);
		if(sqlite3_open(file, &(pDb->db)) != SQLITE_OK)
		{
			udplog ("<SQLITE>%s[%s]", sqlite3_errmsg(pDb->db), file);
			return E_DB_OPEN;
		}
		
		if ( (pDb->fd = open (lock, O_RDWR|O_CREAT)) < 0)
		{
			udplog ("<SQLITE>%s[%s]", "Can't open the file to lock", strerror(errno));
			sqlite3_close (pDb->db);
			return E_DB_OPEN;
		}

		#ifndef CORETREX
		if (chmod (lock, S_IXOTH|S_IWOTH|S_IROTH|S_IXGRP|S_IWGRP|S_IRGRP|S_IXUSR|S_IWUSR|S_IRUSR) != 0)
		{
			udplog ("chmod failed");
		}
		if (chmod (file, S_IXOTH|S_IWOTH|S_IROTH|S_IXGRP|S_IWGRP|S_IRGRP|S_IXUSR|S_IWUSR|S_IRUSR) != 0)
		{
			udplog ("chmod failed");
		}
		#endif
		
		if ( lockf (pDb->fd, F_TLOCK, 0) == 0 )		//lock ok
		{
			pDb->opened = OPENNED;
			pDb->selected = UNSELECT;
			return 0;
		}
		else
		{
			udplog ("<SQLITE>%s[%s]", "the file is locked, please wait", strerror(errno));
			close (pDb->fd);
			sqlite3_close (pDb->db);
			return E_DB_LOCK;
		}
	}
	else			//it's exist
	{
		if ( (pDb->fd = open (lock, O_RDWR)) < 0)
		{
			udplog ("<SQLITE>%s", "Can't open the file to lock");
			return E_DB_OPEN;
		}
		if (lockf (pDb->fd, F_TLOCK, 0) != 0)			//lock failed
		{
			udplog ("<SQLITE>%s", "the file is locked, please wait");
			close (pDb->fd);
			return E_DB_LOCK;
		}		
		if(sqlite3_open(file, &(pDb->db)) != SQLITE_OK)
		{
			udplog ("<SQLITE>%s", sqlite3_errmsg(pDb->db));
			close (pDb->fd);
			return E_DB_OPEN;
		}
		else
		{
			pDb->opened = OPENNED;
			pDb->selected = UNSELECT;
			return 0;
		}
	}
}
#endif
/*
open an database file and lock it, if the file is not exist, will creat it.
return:	0	open ok
		E_DB_OPEN	open faile
		E_DB_LOCK	lock by others
*/
int DbOpen (T_DbOper *pDb, char *file)
{
	char	lock[200];
	char *p;
	
	snprintf (lock, sizeof(lock), "%s", file);
	
	if (( p = strstr (lock, DB_FILE_EXTEND)) == NULL)
	{
		udplog ("<SQLITE>%s", "the file name is error");
		return E_DB_WRONG_FILE;
	}
	snprintf (p, sizeof(lock)-(p-lock), "%s", LOCK_FILE_EXTEND);

	if (CheckExist (file) == 0)		//not exist
	{
		udplog ("<SQLITE>%s not exist, will creat it", file);
		if(sqlite3_open(file, &(pDb->db)) != SQLITE_OK)
		{
			udplog ("<SQLITE>%s[%s]", sqlite3_errmsg(pDb->db), file);
			return E_DB_OPEN;
		}
		
		if ( (pDb->fd = open (lock, O_RDWR|O_CREAT)) < 0)
		{
			udplog ("<SQLITE>%s[%s]", "Can't open the file to lock", strerror(errno));
			sqlite3_close (pDb->db);
			return E_DB_OPEN;
		}

		#ifndef CORETREX
		if (chmod (lock, S_IXOTH|S_IWOTH|S_IROTH|S_IXGRP|S_IWGRP|S_IRGRP|S_IXUSR|S_IWUSR|S_IRUSR) != 0)
		{
			udplog ("chmod failed");
		}
		if (chmod (file, S_IXOTH|S_IWOTH|S_IROTH|S_IXGRP|S_IWGRP|S_IRGRP|S_IXUSR|S_IWUSR|S_IRUSR) != 0)
		{
			udplog ("chmod failed");
		}
		#endif
		
		if ( lockf (pDb->fd, F_TLOCK, 0) == 0 )		//lock ok
		{
			pDb->opened = OPENNED;
			pDb->selected = UNSELECT;
			return 0;
		}
		else
		{
			udplog ("<SQLITE>%s[%s]", "the file is locked, please wait", strerror(errno));
			close (pDb->fd);
			sqlite3_close (pDb->db);
			return E_DB_LOCK;
		}
	}
	else			//it's exist
	{
		if ( (pDb->fd = open (lock, O_RDWR)) < 0)
		{
			udplog ("<SQLITE>%s", "Can't open the file to lock");
			return E_DB_OPEN;
		}
		if (lockf (pDb->fd, F_TLOCK, 0) != 0)			//lock failed
		{
			udplog ("<SQLITE>%s", "the file is locked, please wait");
			close (pDb->fd);
			return E_DB_LOCK;
		}
		if(sqlite3_open(file, &(pDb->db)) != SQLITE_OK)
		{
			udplog ("<SQLITE>%s", sqlite3_errmsg(pDb->db));
			close (pDb->fd);
			return E_DB_OPEN;
		}
		else
		{
			pDb->opened = OPENNED;
			pDb->selected = UNSELECT;
			return 0;
		}
	}
}


/*
close database
return:	0 ok
		E_BD_UNLOCK unlock the file failed
*/
int DbClose (T_DbOper *pDb)
{
	if (pDb->selected == SELECTED)		//it's selected, must end select
	{
		sqlite3_finalize(pDb->stmt);
	}
	sqlite3_close (pDb->db);
	if (lockf (pDb->fd, F_ULOCK, 0) != 0)
	{
		udplog ("<SQLITE>%s", "unlock the file failed");
		return E_BD_UNLOCK;
	}
	close (pDb->fd);
	pDb->opened = UNOPEN;
	return 0;
}

int DbExecute (T_DbOper *pDb, char *sql, ...)
{
	va_list ap;
	char *fullSql;
	char *errMsg;

	if (pDb->opened == UNOPEN)	//not open
	{
		udplog ("<SQLITE>data base wasn't opened");
		return E_DB_UNOPEN;
	}
	va_start (ap, sql);
	fullSql = sqlite3_vmprintf (sql, ap);
	va_end (ap);

	#ifdef DEBUG_SQLITE
	udplog ("<SQLITE>%s", fullSql);
	printf ("<SQLITE>%s", fullSql);
	#endif
	
	if (sqlite3_exec (pDb->db, fullSql, 0, 0, &errMsg) == SQLITE_OK)	//execute ok
	{
		sqlite3_free (fullSql);
		return 0;
	}
	else
	{
	#ifndef DEBUG_SQLITE
		udplog ("<SQLITE>DbExecute error[%s]", fullSql);
		printf ("<SQLITE>DbExecute error[%s]", fullSql);
	#endif
		udplog ("<SQLITE>the reason is:%s", errMsg);
		printf ("<SQLITE>the reason is:%s", errMsg);
		sqlite3_free(fullSql);
		sqlite3_free(errMsg);
		return E_DB_EXECUTE;
	}
}

/*
begin call select
NOTE:
	1. don't call db_bgselect() again if the pDB already called db_bgnselect() unless u call db_endselect(), otherwise it will
		return ERR_DB_INSELECT, which means the dabase is in select.
	2. don't excute a transaction during db_bgnselect(), because the database will be locked after db_bgnselect(), 
		transactions can't be commited. call db_endselect() before commiting a transaction!!!
	3. don't call this function to a closed database, otherwise it will return ERR_DB_TRYCLOSEDDB
*/

int DbBeginSelect (T_DbOper *pDb, char *sql, ...)
{
	char *sTail;
	va_list ap;
	char *fullSql;

	if (pDb->opened == UNOPEN)
	{
		return E_DB_UNOPEN;
	}
	if (pDb->selected == SELECTED)	//already selected by others
	{
		return E_DB_SELECTED;
	}
	
	va_start(ap, sql);
	fullSql=sqlite3_vmprintf(sql, ap);
	va_end(ap);
	
	#ifdef DEBUG_SQLITE
	udplog ("<SQLITE>%s", fullSql);
	#endif

	if(SQLITE_OK == sqlite3_prepare(pDb->db, fullSql, -1, &(pDb->stmt), &sTail)) 
	{
		pDb->selected = SELECTED;
		sqlite3_free(fullSql);
		return 0;
	}
	else 
	{
	#ifndef DEBUG_SQLITE
		udplog ("<SQLITE>DbBeginSelect error[%s]", fullSql);
	#endif
		udplog ("<SQLITE>the reason is:%s", sqlite3_errmsg(pDb->db));
		pDb->selected = UNSELECT;
		sqlite3_free(fullSql);
		return E_DB_PREPARE;
	}
}

/* this return value > 0 */
int DbQureyNext (T_DbOper *pDb)
{
	int	i;
	int	iRet = 0;

	if (pDb->opened == UNOPEN)
	{
		return E_DB_UNOPEN;
	}
	if (pDb->selected == UNSELECT)
	{
		return E_DB_NOTPREPARE;
	}
	
	for (i=0; i<DB_RETRY; i++)
	{
		switch(sqlite3_step(pDb->stmt))
		{
			case SQLITE_ROW:
				return 1;
		
			case SQLITE_DONE:
				return 0;
				
			case SQLITE_BUSY: