1、嵌入式创新实验 华中农业大学 计算机系 倪福川嵌入式数据库简介-SQLite嵌入式创新实验华中农业大学 计算机系 倪福川纲要SQLite介绍SQLite的发展、优势、缺憾 部结构SQLite数据库的使用SQLite的命令行接口 、SQLite命令行使用SQLite数据库的编程接口C/C+接口简单应用 自定义简单函数自定义聚合函数 自定义排序函数SQLite数据库在ASTRAL中的应用SQLite在多级关联中的应用 SQLite在IPIS中的应用 SQLite在web中的应用嵌入式创新实验华中农业大学 计算机系 倪福川SQLite介绍SQLite的发展 2000年由D.Richard Hipp开
2、始开发2001年发布2.0v2004年发布3.0v(采用了不同的数据文件格式以及编程接口)目标易于管理、操作、维护、自定义以及提供易用的编程接口嵌入式创新实验华中农业大学 计算机系 倪福川SQLite介绍SQLite的优势内存占用量小比MySQL(2倍), PostgreSQL(20倍)快ACID兼容(原子性，一致性，独立性,可持久性)，支持视图，子查询，触发器单个库文件中包含数据库引擎与接口,且其运行不依赖其它库可以将数据放进单个文件为C/C+, Perl,PHP等应用提供了接口免费允许为SQL命令集动态添加自定义函数（简单函数及聚集函数），而无需重编SQLite库嵌入式创新实验华中农业大学
3、 计算机系 倪福川SQLite介绍SQLite的 缺点事务处理并发性 SQLite通过数据库级上的独占性和共享锁来实现独立事务处理，这意味着当多个进程或线程在同一时间可以从数据库读取数据，但是只能有一个可以同时写入，在写入之前，必须获得独占锁，其它的读操作不允许发生。性能 在创建索引( CREATE INDEX)和删除表( DELTE TABLE)时明显比其它数据库慢用户管理/安全 数据库的访问是基于操作系统对文件的控制来控制的，不能通过用户来区分数据库中的不同数据库. 举例，将数据库文件去写权限，然后向其中插入或删除数据条目,将提示写失败。但是不能通过数据库本身的来对权限进行设置。 在网上已
4、经有一些SQLite的安全问题的解决方案，但大多数是商业化的，有些提供在整个数据库上的加密，有些提供在数据级别的加密。比如secure SQLite之类。嵌入式创新实验华中农业大学 计算机系 倪福川SQLite的内部结构在内部，SQLite 由以下几个组件组成：SQL 编译器、内核、后端以及附件。SQLite 通过利用虚拟机和虚拟数据库引擎（VDBE），使调试、修改和扩展 SQLite 的内核变得更加方便。所有 SQL 语句都被编译成易读的、可以在 SQLite 虚拟机中执行的程序集。1、接口将SQL语句传给SQL编译器2、SQL编译器选将SQL分解成为Token3、将Token传递给解析器进
5、行解析、由代码生成器生成虚拟机代码、由虚拟机执行生成的程序6、SQLite库在磁盘上以B树形式组织（每个表和索引都有自己单独的B树，所有的B树都保存的同一个文件里面)7、OS层的操作大多数的SQL内建函数可以在func.c, date.c里面找到。用户自定义函数编写可以参考这里面的实现。无论内内建函数还是用户自定义函数都是用C的回调方式实现(稍后再做介绍)嵌入式创新实验华中农业大学 计算机系 倪福川SQLite数据库的使用SQLite命令行接口SQLite除库本身外，还包含命令行接口，可以在$SQLITE_HOME/bin下发现sqlite/sqlite3，命令行功能介绍运行方式：sqlite
6、 DBFile得到提示符sqlite运行.help（注意sqlite命令行提供的命令都以”.”开头，可以看到sqlite命令行接口提供下面的功能.嵌入式创新实验华中农业大学 计算机系 倪福川SQLite命令行功能简介SQLite命令行功能简介DML/DDL语句的使用和以前一致，不做介绍.databases 列出数据库文件名.tables ?PATTERN? 列出?PATTERN?匹配的表名.import FILE TABLE将文件中的数据导入的文件中.dump ?TABLE? 生成形成数据库表的SQL脚本.output FILENAME 将输出导入到指定的文件中.output stdout 将
7、输出打印到屏幕.mode MODE ?TABLE? 设置数据输出模式(csv,html,tcl.nullvalue STRING 用指定的串代替输出的NULL串.read FILENAME执行指定文件中的SQL语句.schema ?TABLE? 打印创建数据库表的SQL语句 .separator STRING 用指定的字符串代替字段分隔符.show 打印所有SQLite环境变量的设置.quit 退出命令行接口嵌入式创新实验华中农业大学 计算机系 倪福川SQLite命令行功能使用SQLITE命令行的常规使用：SQLite数据导入创建数据文件这个文件可能来自其它的其它程序的输出之类，现只我们手功创
8、建下面的数据文件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.数据库表创建 shell sqlite3 test.dbsqlite create table employee( id integer pri
9、mary key, name text, gender text, age integer );嵌入式创新实验华中农业大学 计算机系 倪福川SQLite命令行功能使用数据导入sqlite.import data.txt employeesqlite提示：data.txt line1:expected 4 coloumns of data but found 1;从经验应该能看出是字符分隔符有问题，先来看看系统用什么样的提示符：.show之后可以看到 separator: “|”，也就是说系统默认的分隔符为”|”面不是”,”，下面修改分隔字符:sqlite.separator “,” sqlit
10、e.import data.txt employeesqlite select * from employee where id 2;sqlite select * from employee where name 9999999999999;上面这句用来说明textinteger（这个和比较字符的内码得到的结果是相同的）数据比较1.NULLINTERGER(REAL)TEXT select id 2, name dong, gender=male from employee;0,1,10,1,01,1,11,0,11,1,1嵌入式创新实验华中农业大学 计算机系 倪福川SQLite命令行功能使
11、用下面举例说明SQLITE命令行的常规使用：SQLite数据导出将数据表导出为数据创建脚本sqlite .dump employee 上面的命令可以得到数据表相关的SQL语句,为了将输出保存的文件中，执行下面的命令。sqlite .output create.sqlsqlite .dump employeesqlite .output stdout（将输出恢复到标准输出）（将输出恢复到标准输出）将特定数据导出成数据文件sqlite .output data_tmp.htmlsqlite select * from employee;sqlite .mode html employeesqlit
12、e select * from employee;sqlite .output stdout上面的几条命令将employee中的数据以html 的格式保存到data_tmp.html中.shell sqlite test.db “.dump” | sed e s/dq/dongqiang/ | sqlite3 test2.db上面的命令行操作将test.db中的dq全部替换为dongqiang然后导入test2.db中n创建索引 create index employee_ID on employee( id );n创建触发器create Trigger insert_trigger AFTE
13、R INSERT ON employeeBEGIN /动作,比如记数，修改时间等END嵌入式创新实验华中农业大学 计算机系 倪福川SQLite的编程接口简介SQLite3 C/C+常见接口int sqlite3_open();/数据库的打开,包括文件和内存数据库两种(:memory).int sqlite3_close(); /数据库的关闭long long int sqlite3_last_insert_rowid(sqlite3*);/最后插入的数据的行号long long int sqlite3_progress_handler(sqlite3*);/查询的进度,参数为空删除进度信息(实
14、验中）SQLite3的不同执行方式执行一行或多行SQL语句并在查询结果的每一行上执行回调函数int sqlite3_exec();动态构造SQL语句char *sqlite3_mprintf(const char*,.);char *sqlite3_vmprintf(const char*, va_list);/构造语句，由sqlite3_exec执行int sqlite3_exec_printf();/构造查询语句，并执行void sqlite3_free(char *z); /释放sqlite3_(v)mprintf分配的内存预编译SQL语句，减少SQL分析的时间int sqlite3_p
15、repare(); /预编译int sqlite3_step(sqlite3_stmt*); /执行一次，或者多次Int sqlite3_reset( sqlite3_stmt*); /重置sqlite3_stmt(来自sqlite3_prepare()int sqlite3_finalize(sqlite3_stmt *pStmt); /释放利用对sqlite3_exec()的封装,返回所有的查询结果int sqlite3_get_table();void sqlite3_free_table(char *result);嵌入式创新实验华中农业大学 计算机系 倪福川SQLite的编程接口简单
16、应用 SQLite的简单使用举例，去除了对异常处理部分。主函数分析：int main(int argc, char* argv) sqlite3 * db = NULL; char* zErrMsg = NULL; int rc; sqlite3_open( “:memory:”, &db ); /打开内存数据库rc = sqlite3_exec(db, “create table employee(id integer primary key, age integer);”, NULL, 0, &zErrMsg);/创建数据库 for( int i= 0; i 10; i+ ) statem
17、ent = sqlite3_mprintf( insert into employee values(%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 * from employee;” , callback, 0, &zErrMsg );/查询，并调用回调函数 sqlite3_close(db);嵌入式创新
18、实验华中农业大学 计算机系 倪福川SQLite的编程接口简单应用回调函数int calltimes = 0; static int callback(void* notused, int argc, char* argv, char* azColName) int i; calltimes+; for(i=0; i select * from employee where id=min( 1, ,2, 3, 4,5);sqlite select * from employee where id=min(1);/min()是aggragate func作为表达式SQL Error: misuse
19、 of aggregate function main()sqlite select min(1) from employee;嵌入式创新实验华中农业大学 计算机系 倪福川SQLite的自定义简单函数接口用户自定义函数接口int sqlite3_create_function(); 该函数用于注册/删除用户自定义函数。自定义函数传递参数有两种方式，第一种是在注册时用pUserData传入，第二种是在调用已经注册函数时传入参数。void (*func)(sqlite3_context*,int,sqlite3_value*); 用户自定义函数的格式，第二个参数是自定函数的参数个数，第三个函数是自
20、定义函数的值。void *sqlite3_user_data(sqlite3_context*); 返回用户注册函数时传入的参数void *pUserData。int sqlite3_value_type(sqlite3_value*); 用来说明sqlite3_value的数据类型,比如用户自已定义的回调函数的第二个参数的数据类型。sqlite3_value_XXXX(sqlite3_value*); 用来返回sqlite3_value所表示的准确值。sqlite3_result_XXXX(sqlite3_context*, .); 用来设置返回值为某一数值的准确拷贝。对上面的内容更详细的介
21、绍将放在利用自定义接口编程的部分进行说明。对聚合函数及排序函数的接口介绍也推后介绍。嵌入式创新实验华中农业大学 计算机系 倪福川SQLite的自定义函数编程接口int sqlite3_create_function( sqlite3 *, /数据库句柄数据库句柄const char *zFunctionName, /自定义函数的名子自定义函数的名子int nArg, /参数个数，参数个数，-1表示任意个数的参数表示任意个数的参数int eTextRep, /函数接受的参数数据类型函数接受的参数数据类型void *pUserData, /在函数实现中用在函数实现中用sqlite_user_dat
22、a()得到得到void (*xFunc)(sqlite3_context*,int,sqlite3_value*), /回调函数，针对简单函数回调函数，针对简单函数,xStep=NULL, xFinalNULLvoid (*xStep)(sqlite3_context*,int,sqlite3_value*), /针对聚集函数针对聚集函数,xFun=NULLvoid (*xFinal)(sqlite3_context*) /针对聚集函数针对聚集函数,xFun=NULL); int sqlite3_create_function16();eTextRep(参数类型)#define SQLITE_
23、UTF8 1 (any func work)#define SQLITE_UTF16 2 #define SQLITE_UTF16BE 3 #define SQLITE_UTF16LE 4 #define SQLITE_ANY 5(no care what text is used)SimpleAggregatedeletexFuncNULLNULLxStepNULLNULLxFinalNULLNULL嵌入式创新实验华中农业大学 计算机系 倪福川自定义函数注册流程上面的图表说明，无论是用户自定义的函数，还是sqlite内建的函数都利用callback的方式进行注册，用户自定义的函数编写的方式完
24、全可以参照func.c中内建函数的实现方式来实现。嵌入式创新实验华中农业大学 计算机系 倪福川SQLite内建简单函数分析功能：返回传入的第一个参数的byte数参数：argc=1,表示只能传入一个参数返回值：回调函数的返回值void,但是调用注册函数会返回整数值static void lengthFunc( sqlite3_context *context, int argc, sqlite3_value *argv) int len; switch( sqlite3_value_type(argv0) )/第一个参数的类型 case SQLITE_BLOB: case SQLITE_INTE
25、GER: case SQLITE_FLOAT: sqlite3_result_int(context, sqlite3_value_bytes(argv0); /统计的bytes数 break; case SQLITE_TEXT: const unsigned char *z = sqlite3_value_text(argv0); for(len=0; *z; z+) if( (0 xc0&*z)!=0 x80 ) len+; /统计字符串的bytes数 sqlite3_result_int(context, len); /设置返回值 break; default: sqlite3_resu
26、lt_null(context);/返回NULL break; 嵌入式创新实验华中农业大学 计算机系 倪福川用户自定义简单函数下面我们实现两个自定义的简单函数:第一个例子在注册函数时，将用户的数据传入，然后调用自定义函数时，返回传入的值。在下面的例子中，将传入的数据插入到数据库中。第二个例子在注册函数后，在查询语句中对自定义函数中进行查询，然后将查询的结果进行修正，从202.12.0.32变成http:/202.12.0.32的形式返回。例一、例一、void get_key_func( sqlite3_context * ctx, int dummy1, sqlite3_value * dum
27、my2 ) sqlite3_result_int( ctx, *(int *)sqlite3_user_data( ctx ) ) ; sqlite3_create_function( db-db, get_key, -1, SQLITE_ANY, &db-key, get_key_func, NULL, NULL ) ;调用自定义函数的查询语句：INSERT INTO tbl VALUES( get_key(), get_value() );上面实现了自定义函数并且将其进行注册，在注册时将db-key做为用户数据传入，每次插入数据前，对db-key的值进行修改，get_key_func()就
28、会返回新的数据，然后将其插入到数据库中。更为详细的例子将在实际应用部分进行讲解。嵌入式创新实验华中农业大学 计算机系 倪福川用户自定义简单函数例二、例二、void host2url_func( sqlite3_context * ctx, int argc, sqlite3_value * argv ) if( argc != 1 ) return; char *httpurl=NULL; switch( sqlite3_value_type( argv 0 ) ) case SQLITE_NULL: sqlite3_result_text( ctx, “NULL”, 4, SQLITE_ST
29、ATIC ); /如果没有传入，返回零break; case SQLITE_TEXT: httpurl = ( char * ) malloc(strlen( sqlite3_value_text( argv 0 ) ) + 8 ); /分配内存 if ( httpurl = 0 ) return; sprintf( httpurl, “http:/%s”, sqlite3_value_text( argv 0 ) ); sqlite3_result_text( ctx, httpurl, strlen( httpurl ) , SQLITE_TRANSIENT ); /设置返回值free(
30、httpurl );/释放内存httpurl = NULL;break; default: sqlite3_result_text( context, “about:blank”, 11, SQLITE_STATIC ); break; sqlite3_create_function( db, “geturl”, 1, SQLITE_ANY, NULL, host2url_func, NULL, NULL ) ; /注册函数$r = sqlite_exec($db, SELECT geturl(url) FROM host_table;, callback, 0, &error ); /查询嵌
31、入式创新实验华中农业大学 计算机系 倪福川用户自定义简单函数嵌入式创新实验华中农业大学 计算机系 倪福川SQLite的自定义聚合函数接口自定义聚合函数接口 在聚合函数的定义过程中， 在SQLite的自定义简单函数接口一节定义的函数都可以使用，除此之外，还有一些函数可以使用：sqlite3_aggregate_context( sqlite3_context *, int nBytes ); 用来分配一个保存聚合过程状态的结构,在下面的调用中，将返回同样的内存块。该内存在聚合函数调用完成后自动释放。void (*xStep)(sqlite3_context*,int,sqlite3_value*
32、); 用户自定义函数的格式，该函数用来统计用户需要的数据，第二个参数是自定函数的参数个数，第三个函数是自定义函数的值。void (*xFinal)(sqlite3_context*); 用户自定义函数的格式，用来设置返回值自定义排序函数接口sqlite3_create_collation()sqlite3_create_collation16()用来创建一个用来排序的函数（不同与简单函数和聚合函数的注册）void (*xCompare)(void *, int, const void *, int , const void *); 用户自定义函数的格式，第二个参数是自定函数的参数个数，第三个函
33、数是自定义函数的值。嵌入式创新实验华中农业大学 计算机系 倪福川SQLite内建聚合函数分析聚合函数与简单函数有一些不同。简单函数只有一个回调函数，但是聚合函数有两个回调函数。struct CountCtx i64 n; /64bit的整数;typedef struct CountCtx CountCtx; /用于记录count()的状态信息的结构下面两个函数是count()的实现，countStep()用于统计，countFinialize()用于设置返回结果。static void countStep(sqlite3_context *context, int argc, sqlite3_
34、value *argv) CountCtx *p; p = sqlite3_aggregate_context(context, sizeof(*p); if( (argc=0 | SQLITE_NULL!=sqlite3_value_type(argv0) & p ) p-n+;/改变统计值 static void countFinalize(sqlite3_context *context) CountCtx *p; p = sqlite3_aggregate_context(context, 0); sqlite3_result_int64(context, p ? p-n : 0);/
35、设置返回结果64bit嵌入式创新实验华中农业大学 计算机系 倪福川用户自定义聚合函数自定义聚合函数的代码主要是两个回调函数的编写以及聚合函数的注册，能过前面的内建聚合函数的分析，我们总结出聚合函数定义的基步骤。基本步骤如下：1.定义自己的用的聚合函数状态结构2.利用sqlite3_aggregate_context( ctx, sizeof( struct custom_agg ) )分配状态结构空间3.xStep()的调用会发生很多次，在查询结果的每一行上都运行xStep()进行数据处理，比如累加，计数。4.在XFinal()中，利用sqlite3_aggregate_context( ct
36、x, 0 )得到状态结构，并且设置返回值。5.利用sqlite3_create_func()注册聚合函数6.在sql语句中调用聚合函数在这里不再给出具体实现，可以参考前面简单函数的实现以及内建聚合函数的实现来编写。嵌入式创新实验华中农业大学 计算机系 倪福川SQLite内部排序函数分析内建排序函数 从前面接口部分的介绍我们可以看到注册一个排序函数是用sqlite3_create_collation()方法，该方法会调用createCollation()来注册排序函数。在main.c里面，我们可以看到在数据库打开完成之后，马上增加注册了下面几个比较函数：createCollation(db, B
37、INARY, SQLITE_UTF8, 0, binCollFunc); createCollation(db, BINARY, SQLITE_UTF16BE, 0, binCollFunc); createCollation(db, BINARY, SQLITE_UTF16LE, 0, binCollFunc) ;createCollation(db, NOCASE, SQLITE_UTF8, 0, nocaseCollatingFunc);嵌入式创新实验华中农业大学 计算机系 倪福川用户自定义排序函数用户自定义排序函数 如果在数据库中存在一列，该列的内容全部都是中文，我们想以中文的拼音对查
38、询结果进行排序，那么上面预定义的几种排序方法是不可行的，因此，我们需要自己定义一个排序函数。int pinyin_cmp(void *NotUsed,int nKey1, const void *pKey1,/比较的字符数，比较的指针 int nKey2, const void *pKey2) int n = nKey1 .schemaCREATE TABLE host_table( host_id integer primary key, desc text );sqlite select * from host_table;1|192.168.1.1982|192.168.1.883|19
39、2.168.1.994|192.168.1.1065|192.168.1.100sqlite select host_id from host_table;12345sqlite select min( host_id ) from host_table;1上面的的过程用来说明聚合函数min(host_id)，是针对列来操作的。即min(host_id)求出的是所有该列的值的最小值。嵌入式创新实验华中农业大学 计算机系 倪福川问题解答问题二、简单函数与聚合函数的区别简单函数的操作对象是行上的单个字段。聚合函数的操作对象是指定的列。问题四、内存数据库的打开 可以同时打开多个内存数据库，并不像打开
40、文件那样是创建一个文件的多个句柄。利子程序在附件中可以看到问题三、数据库大小的问题 支持2T大小的数据库文件 字符串与Blob的大小只与可用的内存大小有关问题四：sqlite3数据导出的模式有下面几种 csv Comma-separated values column Left-aligned columns. (See .width) html HTML code insert SQL insert statements for TABLE line One value per line list Values delimited by .separator string tabs Tab-
41、separated values tcl TCL list elements 嵌入式创新实验华中农业大学 计算机系 倪福川问题解答问题五.rowid相关问题解释sqlite select last_insert_rowid(); /表中虽然有数，但是rowid在没有插入数据时为零0sqlite insert into host_table values( 7, 192.168.1.68 ); /自己指定主键的值sqlite select * from host_table where rowid= last_insert_rowid(); /此时rowid()是我们自己设置的值77|192.1
42、68.1.68sqlite insert into host_table( desc ) values ( 192.168.1.70 ); /如果不指定主键，主键自增长，last_insert_rowid()返回系统设置的值sqlite select * from host_table where rowid= last_insert_rowid();8|192.168.1.70sqlite 在创建数据表时，指定primary key通常仅仅只是在相应于栏位创建一个单键索引，但是如果primary key是应用在一个数据类型为INTEGER的数据栏上，那么这个栏在内部被做为B树的索引键。如果数
43、据表没有一个INTEGER PRIMARY KEY的栏位，那么B树将自动产生B树的键。也就是如果没有id这个键位的话，sqlite会有另一个列，其值表现为rowid。在SQLite中，每一个表中的表一行都一个一rowid,在一个表里面，rowid是唯一的，在sqlite2.8中，rowid是32bit,在3.0后，rowid用 64bit来表示。可以用rowid, _rowid, oid来访问，如果你自己的表里面有一个rowid，那么，就使用你自己指定的rowid，而不是内的rowid.通常新插入的行rowid是最大的 rowid+1,如果最大的rowid达到最大，那么,sqlite会随机取一个没有用的rowid。这也就是说，只要不删、不指定最大的rowid，那么rowid就是自动增加的,删除条目不会导致rowid的减小，所以如果表里面有100条数据，rowid等于100,全删后，rowid不会变化。 AUTOINCREMENT关键字保证rowid的持续增长，如果达到最大值，则返回SQLITE_FULL的错误。last_insert_rowid ()
