SQLITE3

嵌入式数据库简介[e-mail]纲要SQLite介绍SQLite的发展SQLite的优势SQLite的缺憾SQLite的内部结构SQLite数据库的使用SQLite的命令行接口SQLite命令行使用SQLite数据库的编程接口C/C++接口简单应用自定义简单函数自定义聚合函数自定义排序函数PERL接口SQLite数据库在ASTRAL中的应用SQLite在多级关联中的应用SQLite在IPIS中的应用SQLite在web中的应用SQLite介绍SQLite的发展2000年由D.RichardHipp开始开发2001年发布2.0v2004年发布3.0v(采用了不同的数据文件格式以及编程接口)目标易于管理、操作、维护、自定义以及提供易用的编程接口SQLite介绍SQLite的优势内存占用量小比MySQL(2倍),PostgreSQL(20倍)快ACID兼容(原子性，一致性，独立性,可持久性)，支持视图，子查询，触发器单个库文件中包含数据库引擎与接口,且其运行不依赖其它库可以将数据放进单个文件为C/C++,Perl,PHP等应用提供了接口免费允许为SQL命令集动态添加自定义函数（简单函数及聚集函数），而无需重编SQLite库SQLite介绍SQLite的缺点事务处理并发性SQLite通过数据库级上的独占性和共享锁来实现独立事务处理，这意味着当多个进程或线程在同一时间可以从数据库读取数据，但是只能有一个可以同时写入，在写入之前，必须获得独占锁，其它的读操作不允许发生。性能在创建索引(CREATEINDEX)和删除表(DELTETABLE)时明显比其它数据库慢用户管理/安全数据库的访问是基于操作系统对文件的控制来控制的，不能通过用户来区分数据库中的不同数据库.举例，将数据库文件去写权限，然后向其中插入或删除数据条目,将提示写失败。但是不能通过数据库本身的来对权限进行设置。在网上已经有一些SQLite的安全问题的解决方案，但大多数是商业化的，有些提供在整个数据库上的加密，有些提供在数据级别的加密。比如secureSQLite之类。SQLite的内部结构在内部，SQLite由以下几个组件组成：SQL编译器、内核、后端以及附件。SQLite通过利用虚拟机和虚拟数据库引擎（VDBE），使调试、修改和扩展SQLite的内核变得更加方便。所有SQL语句都被编译成易读的、可以在SQLite虚拟机中执行的程序集。1、接口将SQL语句传给SQL编译器2、SQL编译器选将SQL分解成为Token3、将Token传递给解析器进行解析４、由代码生成器生成虚拟机代码５、由虚拟机执行生成的程序6、SQLite库在磁盘上以B树形式组织（每个表和索引都有自己单独的B树，所有的B树都保存的同一个文件里面)7、OS层的操作大多数的SQL内建函数可以在func.c,date.c里面找到。用户自定义函数编写可以参考这里面的实现。无论内内建函数还是用户自定义函数都是用C的回调方式实现(稍后再做介绍)SQLite数据库的使用SQLite命令行接口SQLite除库本身外，还包含命令行接口，可以在$SQLITE_HOME/bin下发现sqlite/sqlite3，命令行功能介绍运行方式：sqliteDBFile得到提示符sqlite运行.help（注意sqlite命令行提供的命令都以”.”开头，可以看到sqlite命令行接口提供下面的功能.SQLite命令行功能简介SQLite命令行功能简介DML/DDL语句的使用和以前一致，不做介绍.databases列出数据库文件名.tables?PATTERN?列出?PATTERN?匹配的表名.importFILETABLE将文件中的数据导入的文件中.dump?TABLE?生成形成数据库表的SQL脚本.outputFILENAME将输出导入到指定的文件中.outputstdout将输出打印到屏幕.modeMODE?TABLE?设置数据输出模式(csv,html,tcl….nullvalueSTRING用指定的串代替输出的NULL串.readFILENAME执行指定文件中的SQL语句.schema?TABLE?打印创建数据库表的SQL语句.separatorSTRING用指定的字符串代替字段分隔符.show打印所有SQLite环境变量的设置.quit退出命令行接口SQLite命令行功能使用下面举例说明SQLITE命令行的常规使用：SQLite数据导入创建数据文件这个文件可能来自其它的其它程序的输出之类，现只我们手功创建下面的数据文件data.txt(用逗号分隔）:id,name,gender,age1,dq,male,242,jz,female,273.pp,male,264,cj,male,285,zc,male,25创建数据库表1.五种数据类型TEXT,NUMERIC,INTEGER,REAL,NONE2.数据类型的转换向保存的目标类型转换，如将text保存到integer，则试着将文件转为数字(int或float)，如果转换失败，则做为文件保存.3.数据库表创建shellsqlite3test.dbsqlitecreatetableemployee(idintegerprimarykey,nametext,gendertext,ageinteger);SQLite命令行功能使用数据导入sqlite.importdata.txtemployeesqlite提示：data.txtline1:expected4coloumnsofdatabutfound1;从经验应该能看出是字符分隔符有问题，先来看看系统用什么样的提示符：.show之后可以看到separator:“|”，也就是说系统默认的分隔符为”|”面不是”,”，下面修改分隔字符:sqlite.separator“,”sqlite.importdata.txtemployeesqliteselect*fromemployeewhereid2;sqliteselect*fromemployeewherename9999999999999;上面这句用来说明textinteger（这个和比较字符的内码得到的结果是相同的）数据比较1.NULLINTERGER(REAL)TEXTBLOB2.数字之间用数学比较方法比较3.TEXT/BLOB用memcpy()进行比较4.比较方法是可以自已定义或者重载的(我们将在后面提及中文字串的比较）Ex:sqliteselectid2,name‘dong’,gender=‘male’fromemployee;0,1,10,1,01,1,11,0,11,1,1SQLite命令行功能使用下面举例说明SQLITE命令行的常规使用：SQLite数据导出将数据表导出为数据创建脚本sqlite.dumpemployee上面的命令可以得到数据表相关的SQL语句,为了将输出保存的文件中，执行下面的命令。sqlite.outputcreate.sqlsqlite.dumpemployeesqlite.outputstdout（将输出恢复到标准输出）将特定数据导出成数据文件sqlite.outputdata_tmp.htmlsqliteselect*fromemployee;sqlite.modehtmlemployeesqliteselect*fromemployee;sqlite.outputstdout上面的几条命令将employee中的数据以html的格式保存到data_tmp.html中.shellsqlitetest.db“.dump”|sed–es/dq/dongqiang/|sqlite3test2.db上面的命令行操作将test.db中的dq全部替换为dongqiang然后导入test2.db中创建索引createindexemployee_IDonemployee(id);创建触发器createTriggerinsert_triggerAFTERINSERTONemployeeBEGIN//动作,比如记数，修改时间等ENDSQLite的编程接口简介SQLite3C/C++常见接口intsqlite3_open();//数据库的打开,包括文件和内存数据库两种(:memory).intsqlite3_close();//数据库的关闭longlongintsqlite3_last_insert_rowid(sqlite3*);//最后插入的数据的行号longlongintsqlite3_progress_handler(sqlite3*);//查询的进度,参数为空删除进度信息(实验中）SQLite3的不同执行方式执行一行或多行SQL语句并在查询结果的每一行上执行回调函数intsqlite3_exec();动态构造SQL语句char*sqlite3_mprintf(constchar*,...);char*sqlite3_vmprintf(constchar*,va_list);//构造语句，由sqlite3_exec执行intsqlite3_exec_printf();//构造查询语句，并执行voidsqlite3_free(char*z);//释放sqlite3_(v)mprintf分配的内存预编译SQL语句，减少SQL分析的时间intsqlite3_prepare();//预编译intsqlite3_step(sqlite3_stmt*);//执行一次，或者多次Intsqlite3_reset(sqlite3_stmt*);//重置sqlite3_stmt(来自sqlite3_prepare())intsqlite3_finalize(sqlite3_stmt*pStmt);//释放利用对sqlite3_exec()的封装,返回所有的查询结果intsqlite3_get_table();voidsqlite3_free_table(char**result);SQLite的编程接口简单应用下面对SQLite的简单使用举例，下面代码去除了对异常处理部分。主函数分析：intmain(intargc,char**argv){sqlite3*db=NULL;char*zErrMsg=NULL;intrc;sqlite3_open(“:memory:”,&db);//打开内存数据库rc=sqlite3_exec(db,“createtableemployee(idintegerprimarykey,ageinteger);”,NULL,0,&zErrMsg);//创建数据库for(inti=0;i10;i++){statement=sqlite3_mprintf(insertintoemployeevalues(%d,%d);”,NULL,0,&zErrMsg,rand()%65535,rand()%65535);rc=sqlite3_exec(db,statement,NULL,0,0);//插入数据sqlite3_free(statement);}rc=sqlite3_exec(db,“select*fromemployee;”,callback,0,&zErrMsg);//查询，并调用回调函数sqlite3_close(db);}SQLite的编程接口简单应用回调函数intcalltimes=0;staticintcallback(void*notused,intargc,char**argv,char**azColName){inti;calltimes++;for(i=0;iargc;i++){printf(“%s=%s\n”,azColName[i],argv[i]?argv[i]:“NULL”);//打印出查询结果}printf(“number%