嵌入式数据库简介---SQLite

前言：

最近项目需求需要使用到SQlite，找了些资料，学习了SQlite数据的使用以及编程接口和在ASTRAL中的应用

一、SQLite介绍

1.SQLite的发展
    2000年由D.Richard Hipp开始开发
2001年发布2.0v
2004年发布3.0vSQLite的发展(采用了不同的数据文件格式以及编程接口)易于管理、操作、维护、自定义以及提供易用的编程接口

2.SQlite的优势

内存占用量小

比MySQL(2倍), PostgreSQL(20倍)快

ACID兼容(原子性，一致性，独立性,可持久性)，支持视图，子查询，触发器

单个库文件中包含数据库引擎与接口,且其运行不依赖其它库

可以将数据放进单个文件

为C/C++, Perl,PHP等应用提供了接口

免费

允许为SQL命令集动态添加自定义函数（简单函数及聚集函数），而无需重编SQLite库

3.SQlite的缺点

事务处理并发性：SQLite通过数据库级上的独占性和共享锁来实现独立事务处理，这意味着当多个进程或线程在同一时间可以从数据库读取数据，但是只能有一个可以同时写入，在写入之前，必须获得独占锁，其它的读操作不允许发生。

性能：在创建索引( CREATE INDEX)和删除表( DELTE TABLE)时明显比其它数据库慢

用户管理/安全：数据库的访问是基于操作系统对文件的控制来控制的，不能通过用户来区分数据库中的不同数据库.

举例，将数据库文件去写权限，然后向其中插入或删除数据条目,将提示写失败。但是不能通过数据库本身的来对权限进行设置。在网上已经有一些SQLite的安全问题的解决方案，但大多数是商业化的，有些提供在整个数据库上的加密，有些提供在数据级别的加密。比如secure SQLite之类。

4.SQLite的内部结构

在内部，SQLite由以下几个组件组成：SQL编译器、内核、后端以及附件。SQLite通过利用虚拟机和虚拟数据库引擎（VDBE），使调试、修改和扩展SQLite 的内核变得更加方便。所有SQL 语句都被编译成易读的、可以在SQLite 虚拟机中执行的程序集。

a.接口将SQL语句传给SQL编译器

b.SQL编译器选将SQL分解成为Token

c.将Token传递给解析器进行解析

e.由代码生成器生成虚拟机代码

f.由虚拟机执行生成的程序

g.SQLite库在磁盘上以B树形式组织（每个表和索引都有自己单独的B树，所有的B树都保存的同一个文件里面)

h.OS层的操作

大多数的SQL内建函数可以在func.c,date.c里面找到。用户自定义函数编写可以参考这里面的实现。无论内内建函数还是用户自定义函数都是用C的回调方式实现

二、SQLite数据库的使用

SQLite命令行接口:

SQLite除库本身外，还包含命令行接口，可以在$SQLITE_HOME/bin下发现sqlite/sqlite3，

n命令行功能介绍

运行方式：sqlite DBFile

得到提示符sqlite>

运行.help（注意sqlite命令行提供的命令都以”.”开头，可以看到sqlite命令行接口提供下面的功能.

SQLite命令行功能简介:

DML/DDL语句的使用和以前一致，不做介绍
.databases 列出数据库文件名
.tables ?PATTERN? 列出?PATTERN?匹配的表名
.import FILE TABLE 将文件中的数据导入的文件中
.dump ?TABLE? 生成形成数据库表的SQL脚本
.output FILENAME 将输出导入到指定的文件中
.output stdout 将输出打印到屏幕
.mode MODE ?TABLE?     设置数据输出模式
.nullvalue STRING 用指定的串代替输出的NULL串
.read FILENAME 执行指定文件中的SQL语句
.schema ?TABLE? 打印创建数据库表的SQL语句 
.separator STRING 用指定的字符串代替字段分隔符
.show 打印所有SQLite环境变量的设置
.quit 退出命令行接口

下面举例说明SQLITE命令行的常规使用：

SQLite数据导入
创建数据文件
这个文件可能来自其它的其它程序的输出之类，现只我们手功创建下面的数据文件data.txt(用逗号分隔）:
id, name,gender, age
1,dq,male,24
2,jz,female,27
3.pp,male,26
4,cj,male,28
5,zc,male,25
创建数据库表

五种数据类型

TEXT,NUMERIC,INTEGER,REAL,NONE

数据类型的转换
向保存的目标类型转换，如将text保存到integer，则试着将文件转为数字(int或float)，如果转换失败，则做为文件保存.
数据库表创建
shell> sqlite3 test.db
sqlite > create table employee( id integer primary key, name text, gender text, age integer );

数据导入

  sqlite>.importdata.txt employee

  sqlite提示：data.txt line1:expected 4 coloumns of data but found 1;

  从经验应该能看出是字符分隔符有问题，先来看看系统用什么样的提示符：

  .show之后可以看到 separator: “|”，也就是说系统默认的分隔符为”|”面不是”,”，下面修改分隔字符:

sqlite>.separator“,”

sqlite>.importdata.txt employee

sqlite>select * from employee where id > 2;

sqlite>select * from employee where name > 9999999999999;

  上面这句用来说明text>integer（这个和比较字符的内码得到的结果是相同的）

数据比较

1.NULL<INTERGER(REAL)<TEXT<BLOB

2.数字之间用数学比较方法比较

3.TEXT/BLOB用memcpy()进行比较

4.比较方法是可以自已定义或者重载的(我们将在后面提及中文字串的比较）

    Ex: sqlite> select id >2, name >‘dong’, gender=‘male’ from employee;

  0,1,1

  0,1,0

  1,1,1

  1,0,1

  1,1,1

下面举例说明SQLITE命令行的常规使用：

SQLite数据导出

将数据表导出为数据创建脚本

  sqlite>.dump employee

  上面的命令可以得到数据表相关的SQL语句,为了将输出保存的文件中，执行下面的命令。

  sqlite> .output create.sql

  sqlite> .dump employee

  sqlite> .output stdout  （将输出恢复到标准输出）

l将特定数据导出成数据文件

  sqlite> .output data_tmp.html

  sqlite> select * from employee;

  sqlite> .mode html employee

  sqlite> select * from employee;

  sqlite> .output stdout

  上面的几条命令将employee中的数据以html 的格式保存到data_tmp.html中.

  shell>sqlitetest.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 employee

BEGIN

     //动作,比如记数，修改时间等

END

三、SQLite的编程接口简介

SQLite3 C/C++常见接口

int sqlite3_open();  //数据库的打开,包括文件和内存数据库两种(:memory).

int sqlite3_close();          //数据库的关闭

long long intsqlite3_last_insert_rowid(sqlite3*);  //最后插入的数据的行号

long long intsqlite3_progress_handler(sqlite3*);  //查询的进度,参数为空删除进度信息(实验中）

SQLite3的不同执行方式

执行一行或多行SQL语句并在查询结果的每一行上执行回调函数

  int sqlite3_exec();　　

动态构造SQL语句

char*sqlite3_mprintf(const char*,...);

char*sqlite3_vmprintf(const char*, va_list);　//构造语句，由sqlite3_exec执行

int sqlite3_exec_printf();　　　 //构造查询语句，并执行

voidsqlite3_free(char *z);         //释放sqlite3_(v)mprintf分配的内存

预编译SQL语句，减少SQL分析的时间

int sqlite3_prepare();    //预编译

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();

voidsqlite3_free_table(char **result);

下面对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 integerprimary key, age integer);”, NULL, 0, &zErrMsg);　//创建数据库

    for( int i= 0; i < 10; i++ )

    {

      statement = sqlite3_mprintf( "insert into employeevalues(%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);

}

回调函数

int calltimes = 0;

staticint callback(void* notused, int argc, char** argv, char** azColName)

{

    int i;

    calltimes++;

    for(i=0; i<argc; i++)

    {

        printf(“%s = %s\n”, azColName[i], argv[i] ? argv[i] : “NULL”);　//打印出查询结果

  }

    printf(“number %d callback\n”, calltimes); //打印出回调的次数

    return 0;

}

四、SQLite自定义函数简介

SQLite最大的特色之一就是可以用户定义函数。用户自定义函数可以像系统内置函数一样可以在注册之后像系统内置函数一样在SQL语句中使用。用户使用自定义函数类似存储过程，方便用户对常见功能的调用，也加快了执行速度。用户自定义函数整体上可以分为两种：简单函数和聚集函数。

简单函数(simple function)

简单函数用在任何表达式中,常见的有max(x,y..),min(x,y..), random(*), last_insert_rowid(),length(x),lower(x),　upper(x),　round(x,y),　round(*),substr(x,y,z),typeof(x)

聚集函数(aggregate function)

聚集函数经常用在select语句中,常见的有avg(x),count(x),count(*), max(x), min(x), sum(x), total(x)

有些函数既是简单函数，又是聚集函数。比如只有一个参数的min()是一个聚集函数，而有多个参数的min()是一个简单函数。

sqlite> select * from employee whereid=min( 1, ,2, 3, 4,5);

sqlite> select * from employee whereid=min(1);　//min()是aggragate func作为表达式

SQL Error: misuse of aggregate function main()

sqlite> select min(1) from employee;

用户自定义函数接口

lint sqlite3_create_function();

该函数用于注册/删除用户自定义函数。自定义函数传递参数有两种方式，第一种是在注册时用pUserData传入，第二种是在调用已经注册函数时传入参数。

lvoid (*func)(sqlite3_context*,int,sqlite3_value**);

用户自定义函数的格式，第二个参数是自定函数的参数个数，第三个函数是自定义函数的值。

lvoid*sqlite3_user_data(sqlite3_context*); 

返回用户注册函数时传入的参数void *pUserData。

lintsqlite3_value_type(sqlite3_value*); 

           用来说明sqlite3_value的数据类型,比如用户自已定义的回调函数的第二个参数的数据类型。

lsqlite3_value_XXXX(sqlite3_value*); 

           用来返回sqlite3_value所表示的准确值。

lsqlite3_result_XXXX(sqlite3_context*,...);

           用来设置返回值为某一数值的准确拷贝。

对上面的内容更详细的介绍将放在利用自定义接口编程的部分进行说明。对聚合函数及排序函数的接口介绍也推后介绍。

注册自定义函数的接口

int sqlite3_create_function(

  sqlite3 *,  //数据库句柄

  const char *zFunctionName, //自定义函数的名子　

  intnArg,   //参数个数，-1表示任意个数的参数

  inteTextRep,  //函数接受的参数数据类型

  void *pUserData,                   //在函数实现中用sqlite_user_data()得到

  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),  //回调函数，针对简单函数,xStep=NULL, xFinal\NULL

  void (*xStep)(sqlite3_context*,int,sqlite3_value**),　　//针对聚集函数,xFun=NULL

  void (*xFinal)(sqlite3_context*) 　　　　　　　　　　 //针对聚集函数,xFun=NULL

  );

  int sqlite3_create_function16(…);

自定义函数注册流程

上面的图表说明，无论是用户自定义的函数，还是sqlite内建的函数都利用callback的方式进行注册，用户自定义的函数编写的方式完全可以参照func.c中内建函数的实现方式来实现。

SQLite内建简单函数分析

n功能：返回传入的第一个参数的byte数

n参数：argc=1,　表示只能传入一个参数

n返回值：回调函数的返回值void,但是调用注册函数会返回整数值

staticvoid lengthFunc( sqlite3_context *context,  int argc, sqlite3_value **argv)

{

  int len;

  switch( sqlite3_value_type(argv[0]) ){  //第一个参数的类型

    case SQLITE_BLOB:

    case SQLITE_INTEGER:

    case SQLITE_FLOAT: {

      sqlite3_result_int(context,sqlite3_value_bytes(argv[0])); //统计的bytes数

      break;

    }

    case SQLITE_TEXT: {

      const unsigned char *z =sqlite3_value_text(argv[0]);

      for(len=0; *z; z++){ if((0xc0&*z)!=0x80 ) len++; }  //统计字符串的bytes数

      sqlite3_result_int(context, len);  //设置返回值

      break;

    }

    default: {

      sqlite3_result_null(context);  //返回NULL

      break;

    }

  }

}