db_operate.c

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

				iRet = E_DB_BUSY;
				break;
				
			case SQLITE_ERROR:
			case SQLITE_MISUSE:
			default:
				iRet = E_DB_RECSET; 
				break;
		}
		usleep(DB_SLEEP);
	}
	return iRet;
}

int DbEndSelect (T_DbOper *pDb)
{	
	if (pDb->opened == UNOPEN)
	{
		return E_DB_UNOPEN;
	}
	if (pDb->selected == UNSELECT)	//not select
	{
		return 0;
	}
	else
	{
		pDb->selected = UNSELECT;		
		sqlite3_finalize(pDb->stmt);
		return 0;
	}
}

int DbCell2Txt (T_DbOper *pDb, char *cellName, char *value, int size)
{
	int	i;
	int	colNum;
	
	if (pDb->opened == UNOPEN)
	{
		return E_DB_UNOPEN;
	}
	if (pDb->selected == UNSELECT)
	{
		return E_DB_NOTPREPARE;
	}

	if ((colNum = sqlite3_column_count (pDb->stmt)) <= 0)
	{
		return E_DB_NOCLOUMN;
	}

	for (i=0; i<colNum; i++)
	{
		if (!strcmp (sqlite3_column_name (pDb->stmt, i), cellName))
		{
			switch (sqlite3_column_type (pDb->stmt, i))
			{
				case SQLITE_INTEGER:
					snprintf (value, size, "%d", sqlite3_column_int (pDb->stmt, i));
					return 0;
				case SQLITE_TEXT:
					snprintf (value, size, "%s", sqlite3_column_text (pDb->stmt, i));
					return 0;
				case SQLITE_NULL:
					snprintf (value, size, "%s", "(null)");
					return 0;
				case SQLITE_FLOAT:
					snprintf (gErrMsg, sizeof(gErrMsg), "(%s)", "FLOAT");
					return E_DB_NOTSURPPORT;
				case SQLITE_BLOB:
					snprintf (gErrMsg, sizeof(gErrMsg), "(%s)", "BLOB");
					return E_DB_NOTSURPPORT;
			}
		}
	}
	snprintf (gErrMsg, sizeof(gErrMsg), "(%s)", cellName);
	return E_DB_NOCLOUMNNAME;
}









int DbCell2Int (T_DbOper *pDb, char *cellName, int *retvalue)
{
	int	i;
	int	colNum;
	char *pText;
	
	if (pDb->opened == UNOPEN)
	{
		return E_DB_UNOPEN;
	}
	if (pDb->selected == UNSELECT)
	{
		return E_DB_NOTPREPARE;
	}

	if ((colNum = sqlite3_column_count (pDb->stmt)) <= 0)
	{
		return E_DB_NOCLOUMN;
	}

	for (i=0; i<colNum; i++)
	{
		if (!strcmp (sqlite3_column_name (pDb->stmt, i), cellName))
		{
			switch (sqlite3_column_type (pDb->stmt, i))
			{
				case SQLITE_INTEGER:					
					*retvalue = sqlite3_column_int(pDb->stmt, i);
					return 0;
				case SQLITE_TEXT:
					pText = (char *)sqlite3_column_text(pDb->stmt, i);
					if(strlen(pText) == 0)
					{
						return E_DB_TXTEMPTY;
					}				    
					if(IsNumberString(pText) < 0)
					{   					     
						return E_DB_NOTNUM;
				    }
					*retvalue = atoi(pText);
					return 0;
				case SQLITE_BLOB:	//not implemented yet.
					snprintf (gErrMsg, sizeof(gErrMsg), "(%s)", "BLOB");
					return E_DB_NOTSURPPORT;

				case SQLITE_NULL:
					*retvalue = 0;
					return 0;
				default:
					return E_DB_NOTSURPPORT;
			}
		}
	}
	snprintf (gErrMsg, sizeof(gErrMsg), "(%s)", cellName);
	return E_DB_NOCLOUMNNAME;
}

#if 0
int DbCountDistinct(T_DbOper *pDB, char *tablename, char *item, char *where, ...)
{
	va_list ap;
	char *fullsql = 0, *fullwhere = 0;
	
	int ret = -1;
	int retry = 0;	//retry times
	
	if (pDB->opened == UNOPEN)	//not open
	{
		udplog ("<SQLITE>data base wasn't opened");
		return E_DB_UNOPEN;
	}

	if(where)
	{
		va_start(ap, where);
		fullwhere = sqlite3_vmprintf(where, ap);
		va_end(ap);
		
        /* use %s here, because fullwhere already formated by sqlite3_vmprintf() */
		fullsql = sqlite3_mprintf("select count (*) from (select DISTINCT %q  from %q where %s)",
		                          item, tablename, fullwhere);	
	}
	else
	{
		fullsql = sqlite3_mprintf("select count (*) from (select DISTINCT %q from %q)",
		                          item, tablename);
	}

	ret = DbSelectCount(pDB, fullsql);

	sqlite3_free(fullsql); 
	if(where)
	{   
		sqlite3_free(fullwhere);
	}

    return ret;

	
}
#endif
/*
this call will use "select count(*)" to get the record count in db's table.
tablename: the table name to be counted
where: the sql's where condition, eg: "sex=1 and age>=20 and age<60", this parameter accepts variable parameter

NOTE: this call can also be successful even the during db_bgnselect(), because this function doesn't call db_bgnselect() to 
		implement, it call sqlite3_prepare() - the original sqlite function.
*/
int DbCount(T_DbOper *pDB, char *tablename, char *where, ...)
{
	va_list ap;
	char *fullsql = 0;
	char *fullwhere = 0;
	int   ret = -1;
	
	
	if (pDB->opened == UNOPEN)	//not open
	{
		udplog ("<SQLITE>data base wasn't opened");
		return E_DB_UNOPEN;
	}

	if(where)
	{
		va_start(ap, where);
		fullwhere = sqlite3_vmprintf(where, ap);
		va_end(ap);
		
        /* use %s here, because fullwhere already formated by sqlite3_vmprintf() */
		fullsql = sqlite3_mprintf("select count(*) from %q where %s", 
		                          tablename, fullwhere);
	}
	else
	{
		fullsql=sqlite3_mprintf("select count(*) from %q", tablename);
	}

	#ifdef DEBUG_SQLITE
	udplog ("<SQLITE>%s", fullsql);
	#endif

	ret = DbSelectCount(pDB, fullsql);
    
	sqlite3_free(fullsql); 
	if(where)
	{   
	    sqlite3_free(fullwhere);
    }

    return ret;
}

/*
this function will call "select sum(xxx)" to get the table field's summary, if error occurs during this function, it will return 
	false and errorno will be set.
INPUT:
	pDB: pointer to db
	tablename: the table to be summarized
	sum: the field name to be summarized
	where: the condition string, eg: "sex=1 and age>=20 and age<60", this parameter accepts variable parameter
OUTPUT: 
	*retval: the summary value will be saved into *retval
*/
int DbSum(T_DbOper *pDB, char *tablename, char *sum, char *where, ...)
{
	va_list ap;
	char *fullsql, *fullwhere;
	int ret = -1;
	
	if (pDB->opened == UNOPEN)	//not open
	{
		udplog ("<SQLITE>data base wasn't opened");
		return E_DB_UNOPEN;
	}

	va_start(ap, where);
	fullwhere = sqlite3_vmprintf(where, ap);
	va_end(ap);

    /* use %s here, because fullwhere already formated by sqlite3_vmprintf() */
    if (where)/* bug, when where point is empty, this is error sql */
    {
	    fullsql = sqlite3_mprintf("select sum(%q) from %q where %s", sum, tablename, fullwhere);
    }
    else
    {
        fullsql = sqlite3_mprintf("select sum(%q) from %q ", sum, tablename);
    }

	#ifdef DEBUG_SQLITE
	udplog ("<SQLITE>%s", fullsql);
	#endif

	ret = DbSelectCount(pDB, fullsql);

	sqlite3_free(fullsql);
	sqlite3_free(fullwhere);

	return ret;	
}

/*
this function will call sql "update ..." to update record, the sql must be "update ...."
if something wrong, return <0, otherwise return rows been updated.
*/
int DbUpdate(T_DbOper *pDB, char *sql, ...)
{
	va_list ap;
	char *fullsql;
	int ret;

	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);
	#endif
	
	if(SQLITE_OK == sqlite3_exec(pDB->db, fullsql, 0, 0, 0))
	{
		sqlite3_free(fullsql);
		ret = sqlite3_changes(pDB->db);

		if( 0 == ret) 
		{
		    return E_DB_UPDATE;
	    }
	    
		return ret;
	}
	else 
	{	
	#ifndef DEBUG_SQLITE
		udplog ("<SQLITE>DbUpdate error[%s]", fullsql);
	#endif
		udplog ("<SQLITE>the reason is:%s", sqlite3_errmsg(pDB->db));	
		sqlite3_free(fullsql); 
		return E_DB_EXECUTE;
	}
	
}

/*
this function is a combo call to db_bgnselect(), db_qrynext(), db_endselect(), user entered sql, and if everything is ok, 
he can just call db_cell2int(), db_cell2txt() directly.
*/
int DbOnerecord(T_DbOper *pDB, char *sql, ...)
{
	va_list ap;
	char *fullsql;
	int ret = -1;

	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);

    ret = DbBeginSelect(pDB, "%s", fullsql);
    if( ret != 0)
    {
        return ret;
    }

    ret = DbQureyNext(pDB);
    if( ret < 0)/* error ? */
    {
        DbEndSelect(pDB);
    }
    
    return ret; 
}

/*
return the last insert record's id (primary int key)
*/
int DbLastInsertId(T_DbOper *pDB)
{
	if (pDB->opened == UNOPEN)	//not open
	{
		udplog ("<SQLITE>data base wasn't opened");
		return E_DB_UNOPEN;
	}
	
	return (int)sqlite3_last_insert_rowid(pDB->db);
}