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的全部技术。
1、解压数据库源文件并进入解压后的目录，如下：
#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_testSRC = DB_test.cCC = arm-linux-gccCFLAGS += -Wall -O2#DFLAGS += -g -D_DEBUG#LFLAGS += -lsqlite3 -L /home/rootfs/home/sqlite/lib -I /home/rootfs/home/sqlite/includeLFLAGS += -lsqlite3 -ldl -lpthread -L /root/sqlite-3.6.22/build/target/lib -I /root/sqlite-3.6.22/build/target/includeall:$(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_trylocklibsqlite3.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_createos_unix.o(.text+0x90): undefined reference to `pthread_create'os_unix.o(.text+0x90): relocation truncated to fit: R_ARM_PC24 pthread_createos_unix.o(.text+0x9c): undefined reference to `pthread_join'os_unix.o(.text+0x9c): relocation truncated to fit: R_ARM_PC24 pthread_joinos_unix.o(.text+0xa8): undefined reference to `pthread_join'os_unix.o(.text+0xa8): relocation truncated to fit: R_ARM_PC24 pthread_join总结
以上是内存溢出为你收集整理的【原创】sqlite3移植及结构化数据存储测试全部内容，希望文章能够帮你解决【原创】sqlite3移植及结构化数据存储测试所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）