【原创】sqlite3移植及结构化数据存储测试
Author: chad
Mail: linczone@163.com
本文可以自由转载，但转载请务必注明出处以及本声明信息。
一、开发环境：
at91sam9260 + Linux_2.6.32 + Ubuntu 11.04
sqlite 3.7.15版源码包 下载
sqlite3 c测试程序 下载，该测试程序主要测试sqlite3数据库的数据表创建，二进制结构化数据插入/更新/查询以及执行速度测试等功能。
[★★★★★]全面解析sqlite.pdf 下载： sqlite3 学习必看,全面系统的介绍了sqlite的全部技术。
二、移植步骤
#tar -zxvf sqlite-3.7.15.tar.gz
#cd sqlite-3.7.15
2、创建build目录，在build目录中运行configure脚本生成Makefile文件如下：
#mkdir build
#cd build
#../configure --host=arm-linux --prefix=/root/sqlite-3.7.15/build/target
选项host指定的是用arm交叉编译器进行编译选项prefix后面的路径是到时候编译安装后目标存放的目录，你可以任意设置
3、执行make和make install命令，如下：
#make
#make install
编译和安装完后，在/root/sqlite-3.7.15/build/target目录中会生成三个目标文件夹，分别是：
bin include lib
移植到开发板：
bin下的文件下载到开发板的/usr/bin目录中；
lib下的所有文件下载到开发板的/usr/lib目录中即可；
include 目录下是sqlite的C语言API的头文件，编程时会用到。
4、在开发板上测试移植的sqlite数据库。登录开发板的Linux控制台，执行：
sqlite3 test.db
sqlite3是一个sqlite的命令行访问程序，test.db是新建的数据库的名字，打印出sqlite3版本信息说明移植成功。
三、sqlite3数据库C API 测试
直接上测试代码，具体测试程序见上文下载连接：
makefile文件：
#**********************************************************************
# chad Makefile
# linczone@163.com
#
#***********************************************************************
EXEC = db_test
SRC = DB_test.c
CC = arm-linux-gcc
CFLAGS += -Wall -O2
#DFLAGS += -g -D_DEBUG
#LFLAGS += -lsqlite3 -L /home/rootfs/home/sqlite/lib -I /home/rootfs/home/sqlite/include
LFLAGS += -lsqlite3 -ldl -lpthread -L /root/sqlite-3.6.22/build/target/lib -I /root/sqlite-3.6.22/build/target/include
all:$(EXEC)
$(EXEC):$(SRC)
$(CC) $(SRC) -o $@ $(CFLAGS) $(DFLAGS) $(LFLAGS)
arm-linux-strip $(EXEC)
clean:
rm -vf $(EXEC) *.o *~
sqlite3 数据库C API 测试程序，数据表创建，二进制结构化数据插入/更新/查询以及执行速度测试等功能：
/*************************************************************************
> File Name: sqlite3_api_test.c
> Author: chad
> Mail: linczone@163.com
************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
#include <time.h>
#include "sqlite3.h"
sqlite3 *pdb;
typedef struct STRUCT_TEST2
{
int A;
int B;
char C;
int D;
}STRUCT_TEST2;
typedef struct STRUCT_TEST1
{
int num;
int point;
char port;
int type;
char addr[6];
char pw[6];
short rate;
char user[20];
}__attribute__( (packed,aligned(1)) )STRUCT_TEST1;
int mycallback(void* para,int n_column,char** colume_value,char** column_name )
{
printf("in mycallback\n");
int *temp = (int *)para;
printf("n_column = %d\n",n_column);//返回数据的列数，至少=1
printf("colume_value[0] = %s\n",colume_value[0]);//返回的各个数据列值
printf("column_name[0] = %s\n",column_name[0]);//返回的数据列名，本实验返回count(*)
*temp = atoi(colume_value[0]);
printf("out mycallback\n");
return 0;//必须返回0
}
//创建数据表
int Create_db( char* db_file_name )
{
int ret;
sqlite3_stmt *stmt = NULL;
char *errMsg = NULL;
char sqlbuf[2048];
int i;
ret = sqlite3_open( db_file_name,&pdb ); //打开数据库，跟打开文本文件一样
if( ret != sqlITE_OK ) {
printf("the sqlite3_open error!\n");
return -1;
}
int count;
//新建数据包之前先查询数据库中是否有指定的表，有的话count>0
ret = sqlite3_exec( pdb,\
"SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='MonthFreezeTable';",\
mycallback,&count,&errMsg );
if( ret != sqlITE_OK ) {
printf("error code :%d,reason:%s\n",ret,errMsg);
sqlite3_free( errMsg );//释放内存
return -1;
}
if(count > 0) {
printf("该表已经存在!!!!!!\n");
return 0;
} else {
char *columname[]={"id INTEGER PRIMARY KEY","TimeScale date","ForwardPowerInd blob","ReactivePowerInd_1 blob","ReversePowerInd blob","ReactivePowerInd_2 blob","OneQuadrantReactivePower blob","TwoQuadrantReactivePower blob","ThreeQuadrantReactivePower blob","FourQuadrantReactivePower blob","ForwardPower blob","ReactivePower_1 blob","ReversePower blob","ReactivePower_2 blob","ForwardActiveDemand blob","ReverseActiveDemand blob","ForwardReactiveDemand blob","ReverseReactiveDemand blob","PhasePowerTime blob","PhaseDemandTime blob"
};
//创建数据表
sprintf(sqlbuf,"CREATE TABLE MonthFreezeTable( %s,%s,%s );",\
columname[0],columname[1],columname[2],columname[3],columname[4],columname[5],columname[6],\
columname[7],columname[8],columname[9],columname[10],columname[11],columname[12],columname[13],\
columname[14],columname[15],columname[16],columname[17],columname[18],columname[19]);
printf("%s\n",sqlbuf);
ret = sqlite3_exec(pdb,sqlbuf,NULL,&errMsg );
if( ret != sqlITE_OK ) {
printf("creat --->error code :%d,errMsg);
sqlite3_free( errMsg );//释放内存
return -1;
}
}
clock_t starttime,endtime;
double totaltime;
starttime = clock();//计时开始
//使用事务方式批量提交可以大幅度提高数据库执行效率
ret = sqlite3_exec(pdb,"BEGIN TRANSACTION;",&errMsg);//启动事务
if(ret != sqlITE_OK) {
printf("error code :%d,errMsg);
sqlite3_free( errMsg );
return -1;
}
//插入12*2041行数据
for(i=1;i<12*2041;i++)
{
sprintf(sqlbuf,"INSERT INTO MonthFreezeTable(id,TimeScale) VALUES(%d,%s);",i,"2010-11-10" );
ret = sqlite3_prepare(pdb,strlen(sqlbuf),&stmt,errMsg);//编译
if( ret != sqlITE_OK ) {
printf("error code :%d,errMsg);
sqlite3_free( errMsg );
return -1;
}
ret = sqlite3_step(stmt);//执行
if( ret != sqlITE_DONE ) {
printf("the sqlite3_step ret = %d!\n",ret);
return -1;
}
sqlite3_finalize( stmt ); //把刚才分配的内容析构掉
}
/*
ret = sqlite3_exec(pdb,&errMsg);
if(ret != sqlITE_OK) {
printf("1:error code :%d,errMsg);
sqlite3_free( errMsg );
return -1;
}
for ( i=0;i<12*2041;i++)
{
sprintf(sqlbuf,"INSERT INTO MonthFreezeTable(id) VALUES(%d);",i );
ret = sqlite3_exec(pdb,&errMsg);
if(ret != sqlITE_OK)
{
printf("2:error code :%d,errMsg);
sqlite3_free( errMsg );
return -1;
}
}*/
//提交事务
ret = sqlite3_exec(pdb,"COMMIT TRANSACTION;",&errMsg);
if(ret != sqlITE_OK) {
printf("3:error code :%d,errMsg);
sqlite3_free( errMsg );
return -1;
}
endtime = clock();//计时结束
totaltime = (double)( (endtime - starttime)/(double)CLOCKS_PER_SEC );
printf("the total time = %f s\n",totaltime);
printf("the step time = %f ms\n",(totaltime*1000)/(12*2041));
return 0;
}
//查询数据表
int Query_db( char* db_file_name )
{
printf("in %s\n",__FUNCTION__);
int ret;
sqlite3_stmt *pstmt = NULL;
char *errMsg = NULL;
char *sql = "SELECT count(*) FROM MonthFreezeTable WHERE id>0;";//查询表中有多少列
int id;
STRUCT_TEST1 test_struct1;
STRUCT_TEST2 test_struct2;
memset(&test_struct1,0,sizeof(test_struct1));
memset(&test_struct2,sizeof(test_struct2));
ret = sqlite3_prepare(pdb,sql,strlen(sql),&pstmt,&errMsg);
if( ret != sqlITE_OK ) {
printf("error code :%d,errMsg);
sqlite3_free( errMsg );
return -1;
}/*这一部分程序有问题*/
while( 1 )
{
printf("start query data\n");
ret = sqlite3_step(pstmt);
if( ret != sqlITE_ROW )
{ break; }
id = sqlite3_column_int(pstmt,0);
printf("id = %d\n",id);
}
sqlite3_finalize( pstmt ); //把刚才分配的内容析构掉
sqlite3_reset( pstmt );
return 0;
}
char *Now( void )
{
static char tm[40];
struct tm tm_t;
time_t t_time;
if( time(&t_time) == -1 ) {
perror("time");
return -1;
}
tm_t = *localtime(&t_time);
sprintf(tm,"%d-%02d-%02d %02d:%02d:%02d",tm_t.tm_year + 1900 - 2000,\
tm_t.tm_mon + 1,tm_t.tm_mday,tm_t.tm_hour,tm_t.tm_min,tm_t.tm_sec);
return tm;
}
//更新二进制数据
int UpdateBlobData(char *db_file_name)
{
printf("in %s\n",__FUNCTION__);
int ret;
sqlite3_stmt *pstmt = NULL;
char *errMsg = NULL;
//char sql[1023] = "UPDATE MonthFreezeTable SET TimeScale=:time,ForwardPowerInd=:forw WHERE id>0;";
char sql[1023] = "UPDATE MonthFreezeTable SET TimeScale=datetime(),ForwardPowerInd=:forw WHERE id>0;";
int index1,index2;
char *timebuf="2010-11-11";
STRUCT_TEST2 test_struct2;
//-------------------------------------------------------
test_struct2.A = 'A';
test_struct2.B = 'B';
test_struct2.C = 'C';
test_struct2.D = 'D';
//-------------------------------------------------------
clock_t starttime,endtime;
double totaltime;
starttime = clock();
printf("sqlite3_exec!\n");
/* ret = sqlite3_exec(pdb,"BEGIN TRANSACTION;",&errMsg);//开始事务 if(ret != sqlITE_OK) { printf("1:error code :%d,reason:%s\n",errMsg); sqlite3_free( errMsg ); return -1; }*/
//struct time_t tm_t;
printf("start update the db \n");
int i ;
for(i=0;i<12*2041;i++) {
printf("%10d",i);
//timebuf = Now();
//printf("Now:%s",timebuf);
sprintf(sql,"UPDATE MonthFreezeTable SET TimeScale=datetime(),ForwardPowerInd=:forw WHERE id=%d;",i);
ret = sqlite3_prepare(pdb,&errMsg);//预编译
if( ret != sqlITE_OK ) {
printf("error code :%d,errMsg);
sqlite3_free( errMsg );
return -1;
}
//index1 = sqlite3_bind_parameter_index( pstmt,":time" );
index2 = sqlite3_bind_parameter_index( pstmt,":forw" );//生成索引
//ret = sqlite3_bind_blob(pstmt,index1,timebuf,sizeof(timebuf),sqlITE_STATIC);//绑定数据流
ret = sqlite3_bind_blob(pstmt,index2,&test_struct2,sizeof(test_struct2),sqlITE_STATIC);
if( ret != sqlITE_OK ) {
printf("the sqlite3_bind_blob error!\n");
return -1;
}
ret = sqlite3_step(pstmt);//执行
if( ret != sqlITE_DONE ) {
printf("the sqlite3_step error!\n");
return -1;
}
}
printf("end update the db \n");
/* ret = sqlite3_exec(pdb,"COMMIT TRANSACTION;",&errMsg);//提交事务 if(ret != sqlITE_OK) { printf("3:error code :%d,errMsg); sqlite3_free( errMsg ); return -1; }*/
endtime = clock();
totaltime = (double)( (endtime - starttime)/(double)CLOCKS_PER_SEC );
printf("the total time = %f s\n",(totaltime*1000)/(12*2041));
sqlite3_finalize( pstmt ); //把刚才分配的内容析构掉
sqlite3_reset( pstmt );
return 0;
}
//备份数据库
int loadOrSaveDb(sqlite3 *pInMemory,const char *zFilename,int isSave)
{
int rc; /* Function return code */
sqlite3 *pFile; /* Database connection opened on zFilename */
sqlite3_backup *pBackup; /* Backup object used to copy data */
sqlite3 *pTo; /* Database to copy to (pFile or pInMemory) */
sqlite3 *pFrom; /* Database to copy from (pFile or pInMemory) */
rc = sqlite3_open(zFilename,&pFile);
if( rc==sqlITE_OK ) {
pFrom = (isSave ? pInMemory : pFile);
pTo = (isSave ? pFile : pInMemory);
pBackup = sqlite3_backup_init(pTo,"main",pFrom,"main");
if( pBackup ) {
(void)sqlite3_backup_step(pBackup,-1);
(void)sqlite3_backup_finish(pBackup);
}
rc = sqlite3_errcode(pTo);
}
(void)sqlite3_close(pFile);
return rc;
}
int main(void)
{
int ret;
ret = Create_db( "/home/terminal.db" );
if(ret < 0) {
return -1;
}
//ret = Query_db( "/home/terminal.db" );
//if(ret < 0)
//{
// return -1;
//}
ret = UpdateBlobData( "/home/terminal.db" );
if(ret < 0) {
return -1;
}
/*ret = Query_db( "/home/test.db" );
if(ret < 0) {
return -1;
}
*/
loadOrSaveDb(pdb,"/home/bak.db",1);
sqlite3_close(pdb);
return 0;
}
其他注意事项
请注意：若在编译时候出现下面这种问题，请在编译选项中增加“-lpthread”，在编译sqlite的时候可以把“THREADLIB = -lpthread“
前面”#“去掉。
libsqlite3.a(mutex_unix.o): In function `sqlite3_mutex_alloc': mutex_unix.o(.text+0x38): undefined reference to `pthread_mutexattr_init'
mutex_unix.o(.text+0x38): relocation truncated to fit: R_ARM_PC24 pthread_mutexattr_init mutex_unix.o(.text+0x44): undefined reference to `pthread_mutexattr_settype'
mutex_unix.o(.text+0x44): relocation truncated to fit: R_ARM_PC24 pthread_mutexattr_settype mutex_unix.o(.text+0x58): undefined reference to `pthread_mutexattr_destroy'
mutex_unix.o(.text+0x58): relocation truncated to fit: R_ARM_PC24 pthread_mutexattr_destroy libsqlite3.a(mutex_unix.o): In function `sqlite3_mutex_try':
mutex_unix.o(.text+0x100): undefined reference to `pthread_mutex_trylock' mutex_unix.o(.text+0x100): relocation truncated to fit: R_ARM_PC24 pthread_mutex_trylock
libsqlite3.a(os_unix.o): In function `testThreadLockingBehavior': os_unix.o(.text+0x7c): undefined reference to `pthread_create'
os_unix.o(.text+0x7c): relocation truncated to fit: R_ARM_PC24 pthread_create
os_unix.o(.text+0x90): undefined reference to `pthread_create'
os_unix.o(.text+0x90): relocation truncated to fit: R_ARM_PC24 pthread_create
os_unix.o(.text+0x9c): undefined reference to `pthread_join'
os_unix.o(.text+0x9c): relocation truncated to fit: R_ARM_PC24 pthread_join
os_unix.o(.text+0xa8): undefined reference to `pthread_join'
os_unix.o(.text+0xa8): relocation truncated to fit: R_ARM_PC24 pthread_join
@H_404_1182@