3-sqlite嵌入式数据库简介ppt课件

1、嵌入式创新实验 华中农业大学 计算机系 倪福川嵌入式数据库简介-SQLite嵌入式创新实验华中农业大学 计算机系 倪福川纲要SQLite介绍SQLite的发展、优势、缺憾 部结构SQLite数据库的使用SQLite的命令行接口 、SQLite命令行使用SQLite数据库的编程接口C/C+接口简单应用 自定义简单函数自定义聚合函数 自定义排序函数SQLite数据库在ASTRAL中的应用SQLite在多级关联中的应用 SQLite在IPIS中的应用 SQLite在web中的应用嵌入式创新实验华中农业大学 计算机系 倪福川SQLite介绍SQLite的发展 2000年由D.Richard Hipp开

2、始开发2019年发布2.0v2019年发布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 DB

6、File得到提示符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创建数据库表五种数据类型 TEXT,NUMERIC,INTEGER,REAL,NONE数据类型的转换 向保存的目标类型转换，如将text保存到integer，则试着将文件转为数字(int或float)，如果转换失败，则做为文件保存.数据库表创建 shell sqlite3 test.dbsqlite create table employee( id integer primary key,

9、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 “,” sqlite.import d

10、ata.txt employeesqlite select * from employee where id 2;sqlite select * from employee where name 9999999999999;上面这句用来说明textinteger这个和比较字符的内码得到的结果是相同的）数据比较NULLINTERGER(REAL)TEXT select id 2, name dong, gender=male from employee;0,1,10,1,01,1,11,0,11,1,1嵌入式创新实验华中农业大学 计算机系 倪福川SQLite命令行功能使用下面举例说明SQLITE

11、命令行的常规使用：SQLite数据导出将数据表导出为数据创建脚本sqlite .dump employee 上面的命令可以得到数据表相关的SQL语句,为了将输出保存的文件中，执行下面的命令。sqlite .output create.sqlsqlite .dump employeesqlite .output stdout（将输出恢复到标准输出）将特定数据导出成数据文件sqlite .output data_tmp.htmlsqlite select * from employee;sqlite .mode html employeesqlite select * from employee;

12、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中创建索引 create index employee_ID on employee( id );创建触发器create Trigger insert_trigger AFTER INSERT ON employeeBEGIN /

13、动作,比如记数，修改时间等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*);/查询的进度,参数为空删除进度信息(实验中）SQLite3的不同执行方式执行一行或多行SQL

14、语句并在查询结果的每一行上执行回调函数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_prepare(); /预编译int sqlite3_s

15、tep(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的编程接口简单应用 SQLite的简单使用举例，去除了对异常处理部分

16、。主函数分析：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+ ) statement = sqlite3_mprin

17、tf( 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 of aggrega

19、te function main()sqlite select min(1) from employee;嵌入式创新实验华中农业大学 计算机系 倪福川SQLite的自定义简单函数接口用户自定义函数接口int sqlite3_create_function(); 该函数用于注册/删除用户自定义函数。自定义函数传递参数有两种方式，第一种是在注册时用pUserData传入，第二种是在调用已经注册函数时传入参数。void (*func)(sqlite3_context*,int,sqlite3_value*); 用户自定义函数的格式，第二个参数是自定函数的参数个数，第三个函数是自定义函数的值。void

20、 *sqlite3_user_data(sqlite3_context*); 返回用户注册函数时传入的参数void *pUserD sqlite3_value_type(sqlite3_value*); 用来说明sqlite3_value的数据类型,比如用户自已定义的回调函数的第二个参数的数据类型。sqlite3_value_XXXX(sqlite3_value*); 用来返回sqlite3_value所表示的准确值。sqlite3_result_XXXX(sqlite3_context*, .); 用来设置返回值为某一数值的准确拷贝。对上面的内容更详细的介绍将放在利用自定义接口

21、编程的部分进行说明。对聚合函数及排序函数的接口介绍也推后介绍。嵌入式创新实验华中农业大学 计算机系 倪福川SQLite的自定义函数编程接口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_fun

22、ction16();eTextRep(参数类型)#define SQLITE_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嵌入式创新实验华中农业大学 计算机系 倪福川自定义函数注册流程上面的图表说明，无论是用户自定义的函数，还是sqlit

23、e内建的函数都利用callback的方式进行注册，用户自定义的函数编写的方式完全可以参照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) )/第一个参

24、数的类型 case SQLITE_BLOB: case SQLITE_INTEGER: 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

25、, len);/设置返回值 break; default: sqlite3_result_null(context);/返回NULL break; 嵌入式创新实验华中农业大学 计算机系 倪福川用户自定义简单函数下面我们实现两个自定义的简单函数:第一个例子在注册函数时，将用户的数据传入，然后调用自定义函数时，返回传入的值。在下面的例子中，将传入的数据插入到数据库中。第二个例子在注册函数后，在查询语句中对自定义函数中进行查询，然后将查询的结果进行修正，从2变成2的形式返回。例一、void get_key_func( sqlite3_context * ctx

26、, int dummy1, sqlite3_value * dummy2 ) 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做为用户数据传入，

27、每次插入数据前，对db-key的值进行修改，get_key_func()就会返回新的数据，然后将其插入到数据库中。更为详细的例子将在实际应用部分进行讲解。嵌入式创新实验华中农业大学 计算机系 倪福川用户自定义简单函数例二、例二、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_r

28、esult_text( ctx, “NULL”, 4, SQLITE_STATIC ); /如果没有如果没有传入，返回零传入，返回零break; case SQLITE_TEXT: httpurl = ( char * ) malloc(strlen( sqlite3_value_text( argv 0 ) ) + 8 ); /分配内存分配内存 if ( httpurl = 0 ) return; sprintf( httpurl, “%s”, sqlite3_value_text( argv 0 ) ); sqlite3_result_text( ctx, httpurl, strlen(

29、 httpurl ) , SQLITE_TRANSIENT ); /设置返回值设置返回值free( 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

30、 geturl(url) FROM host_table;, callback, 0, &error ); /查询查询嵌入式创新实验华中农业大学 计算机系 倪福川用户自定义简单函数嵌入式创新实验华中农业大学 计算机系 倪福川SQLite的自定义聚合函数接口自定义聚合函数接口 在聚合函数的定义过程中， 在SQLite的自定义简单函数接口一节定义的函数都可以使用，除此之外，还有一些函数可以使用：sqlite3_aggregate_context( sqlite3_context *, int nBytes ); 用来分配一个保存聚合过程状态的结构,在下面的调用中，将返回同样的内存块。该内存

31、在聚合函数调用完成后自动释放。void (*xStep)(sqlite3_context*,int,sqlite3_value*); 用户自定义函数的格式，该函数用来统计用户需要的数据，第二个参数是自定函数的参数个数，第三个函数是自定义函数的值。void (*xFinal)(sqlite3_context*); 用户自定义函数的格式，用来设置返回值自定义排序函数接口sqlite3_create_collation()sqlite3_create_collation16()用来创建一个用来排序的函数不同与简单函数和聚合函数的注册）void (*xCompare)(void *, int, con

32、st void *, int , const void *); 用户自定义函数的格式，第二个参数是自定函数的参数个数，第三个函数是自定义函数的值。嵌入式创新实验华中农业大学 计算机系 倪福川SQLite内建聚合函数分析聚合函数与简单函数有一些不同。简单函数只有一个回调函数，但是聚合函数有两个回调函数。struct CountCtx i64 n;/64bit的整数;typedef struct CountCtx CountCtx; /用于记录count()的状态信息的结构下面两个函数是count()的实现，countStep()用于统计，countFinialize()用于设置返回结果。stat

33、ic void countStep(sqlite3_context *context, int argc, sqlite3_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

34、_context(context, 0); sqlite3_result_int64(context, p ? p-n : 0);/设置返回结果64bit嵌入式创新实验华中农业大学 计算机系 倪福川用户自定义聚合函数自定义聚合函数的代码主要是两个回调函数的编写以及聚合函数的注册，能过前面的内建聚合函数的分析，我们总结出聚合函数定义的基步骤。基本步骤如下：定义自己的用的聚合函数状态结构利用sqlite3_aggregate_context( ctx, sizeof( struct custom_agg ) )分配状态结构空间xStep()的调用会发生很多次，在查询结果的每一行上都运行xStep(

35、)进行数据处理，比如累加，计数。在XFinal()中，利用sqlite3_aggregate_context( ctx, 0 )得到状态结构，并且设置返回值。利用sqlite3_create_func()注册聚合函数在sql语句中调用聚合函数在这里不再给出具体实现，可以参考前面简单函数的实现以及内建聚合函数的实现来编写。嵌入式创新实验华中农业大学 计算机系 倪福川SQLite内部排序函数分析内建排序函数 从前面接口部分的介绍我们可以看到注册一个排序函数是用sqlite3_create_collation()方法，该方法会调用createCollation()来注册排序函数。在main.c里面，

36、我们可以看到在数据库打开完成之后，马上增加注册了下面几个比较函数：createCollation(db, BINARY, 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);嵌入式创新实验华中农业大学 计算机系 倪福川

37、用户自定义排序函数用户自定义排序函数 如果在数据库中存在一列，该列的内容全部都是中文，我们想以中文的拼音对查询结果进行排序，那么上面预定义的几种排序方法是不可行的，因此，我们需要自己定义一个排序函数。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 sele

38、ct * from host_table;1|982|83|94|065|00sqlite select host_id from host_table;12345sqlite select min( host_id ) from host_table;1上面的的过程用来说明聚合函数min(host_id)，是针对列来操作的。即min(host_id)求出的是所有该列的值的最小值。嵌入式创新实验华中农业大学 计算机系 倪福川问题解答问题二、简单函数与聚合函数的区别简单函数的操作对象是行上的单个字段。聚合函数的操作对象是指定的列。问题四、内存数据库的打开 可以同时打开多个内存数据库，并不像打开文件那样是创建一个文件的多个句柄。利子程序在附件中可以看到问题三、数据库大小的问题 支持2T大小的数据库文件 字符串与Blob的大小只与可用的内存大小有关问题四：sqlite3数据导出的模式有下面几