轻量级开源嵌入式关系数据库sqlite基本使用及接口初识

preface,先闲来扯下蛋:

嵌入式数据库，NoSQL的是BerkeleyDB和InnoDB,leveDb、TC(个人较不熟悉)，关系型嵌入式是SQLite;

服务器性质的NoSQL服务器，如Redis、MongoDB等;

完全免费开放,开源的数据库,PostgreSQL(个人感觉很可以)

<一,>1,安装 for centos/ReaHat;

yum -y install sqlite sqlite-devel;

2,sqlite C/C++API接口,核心对象

OCI接口(Oracle Call Interface) ODBC,JDBC,ADO.net闻君你知多少?
2_1,核心对象:database_connection和prepared_statement;

database_connection对象是由sqlite3_open()接口函数创建并返回的,在应用程序使用任何其他SQLite接口函数前,必须先调用该函数以便获得database_connection对象,后面的其他APIs调用中,都需要database_connection对象作为输入参数以完成相应的工作,prepared_statement,可将其视为编译好的SQL语句,所有SQL语句执行相关的函数都需要改对象作为输入参数以完成指定的SQL操作.

2_2,核心接口01)

sqlite3_open 操作SQLite数据库的入口函数,该函数返回的database_connection对象是很多其他SQLite APIs的句柄参数,我们可以通过该函数即可以打开已经存在 的数据库文件,也可以创建新的数据库文件,该函数返回的database_connection对象,我们可以在多个线程之间共享该对象的指针,以便完成和数据库相关的任意操作,为访问多个数据库而创建多个数据库连接对象,因为通过SQLite自带的ATTACH命令可以在一个连接中方便的访问多个数据库;

2),sqlite3_prepar

该函数将SQL文本转换为prepared_statement对象,斌在函数执行后返回该对象指针,该函数不会评估参数指定SQL语句,它仅仅是将SQL文本初始化为待执行的状态,sqlite3_prepare_v2等同;

3),sqlite_step

函数用于评估sqlite3_prepare函数返回的prepared_statement对象,在执行完该函数之后,prepared_statement对象的内部指针将指向其返回的结果集的第一行;迭代其后的数据行,则需要不断的调用该函数,直到遍历完数据行,对于insert,update和delete等DML语句,sqlite_step一次即可完成;

4),sqlite3_column _....

函数用于获取当前行指定列的数据,

sqlite3_column_blob,bytes,bytes16,double,int,int64,text,text16,type,value,count;

其中sqlite3_column_count函数用于获取当前结果集中的字段数据

案例伪码,

使用sqlite3_step和sqlite_column函数迭代结果集中每行数据

int fieldCount = sqlite3_column_count();

while (sqlite3_step()<>EOF)

{

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

{

int v = sqlite3_column_int();

}

}

5),sqlite3_finalize

函数用于销毁prepared_statement对象,否则会造成内存泄露

6),sqlite3_close

函数用于关闭之前打开的database_connection对象,其中所有和该对象相关的prepared_statement对象都必须在此之前销毁;

3,参数变量绑定;

SQLite的SQL文本也支持变量绑定,以减少SQL语句被动态解析的次数,有利于提高数据查询和操作效率,要完成该操作,还需要SQLite提供的另外2个接口,sqlite3_reset和sqlite3_bind;

 1 void test_parameter_binding() {
 2 //1. 不带参数绑定的情况下插入多条数据,
 3 char strSQL[128];
 4 for (int i = 0;i < MAX_ROWS;++i)
 5 {
 6 sprintf(strSQL,"insert into testtable values(%d)",i);
 7 sqlite3_prepare_v2(...,strSQL);
 8 sqlite3_step(prepared_statement);
 9 sqlite3_finalize(prepared_statement);
10 }
11 //2,参数绑定情况下插入多条数据
12 string strSQLWithParameter = "insert into testtable values(?)";
13 sqlite3_prepare_v2(...,strSQL);
14 for (int i = 0;i < MAX_ROWS;++i)
15 {
16 sqlite3_bind();
17 sqlite3_step();
18 sqlite3_reset();
19 }
20 sqlite3_finalize();
21 
22 }

<二,>数据库,表CLI语句;

在指定数据库创建表;

用attach database '数据库文件路径,可以存在或是新建' as 数据库名,同前面的数据库文件名一样;

在在其创建表时,仍然需要指定此数据库的名称,不然SQL创建的数据表将会被创建到我们sqlite3 数据库名称的数据库中;

简单数据库备份恢复

临时表不会被持久化到数据库中;

创建带缺省值的数据表

if not exists;

create table tb_Name as select * from tb;

但却无create table tb_Name like tb;

sqlite查看创建表的sql语句.schema tb;

无show create table tb;

desc tb;

主键约束;

唯一性索引,primary key = unique + not null;

(2个列的联合主键,2个列的唯一性索引)

非空约束

检查性约束

基于表多列检查性约束

表的修改;

SQLite对alter命令支持的非常有限,仅仅是修改表名和添加新字段.其它的功能,重命名字段,删除字段和添加约束等均未提供支持;

不能修改attach库中的表,修改表名后索引不受影响,但视图和触发器需重新修复;

在SQLite中alter table命令的执行时间不会受到当前表行数的影响;

在SQLite中如果某个表被删除了,那么与之相关的索引和触发器也会随之删除,在其它关系型数据库中不是这样,如果必须删除相关对象,在语句尾添加with cascade,options;

(表的存在 or 不存在,执行语句时不抛出异常, create table if not exists ruiy(field field_Type,....), drop table if exists ruiy)

数据库视图是什么?在不严格意思上来讲 tables = view,从严格上来讲...;

distinct关键字可以作为函数参数字段的前置属性,以便在进行计算时忽略所有重复的字段值,count(disinct Xx);

 常用SQLite备份方式及在线备份APIs:

下面的方法是比较简单且常用的SQLite数据库备份方式，见如下步骤：
    1). 使用SQLite API或Shell工具在源数据库文件上加共享锁。
    2). 使用Shell工具(cp或copy)拷贝数据库文件到备份目录。
    3). 解除数据库文件上的共享锁。
    以上3个步骤可以应用于大多数场景，而且速度也比较快，然而却存在一定的刚性缺陷，如：
    1). 所有打算在源数据库上执行写操作的连接都不得不被挂起，直到整个拷贝过程结束并释放文件共享锁。
    2). 不能拷贝数据到in-memory数据库。
    3). 在拷贝过程中，一旦备份数据库所在的主机出现任何突发故障，备份数据库可能会被破坏。
    在SQLite中提供了一组用于在线数据库备份的APIs函数(C接口)，可以很好的解决上述方法存在的不足。通过该组函数，可以将源数据库中的内容拷贝 到另一个数据库，同时覆盖目标数据库中的数据。整个拷贝过程可以以增量的方式完成，在此情况下，源数据库也不需要在整个拷贝过程中都被加锁，而只是在真正 读取数据时加共享锁。这样，其它的用户在访问源数据库时就不会被挂起。

在线备份APIs简介:

SQLite提供了以下3个APIs函数用于完成此操作，这里仅仅给出它们的基本用法，至于使用细节可以参考SQLite官方网站"APIs Reference"(http://www.sqlite.org/c3ref/backup_finish.html)。
    1). 函数sqlite3_backup_init()用于创建sqlite3_backup对象，该对象将作为本次拷贝操作的句柄传给其余两个函数。
    2). 函数sqlite3_backup_step()用于数据拷贝，如果该函数的第二个参数为-1，那么整个拷贝过程都将在该函数的一次调用中完成。
    3). 函数sqlite3_backup_finish()用于释放sqlite3_backup_init()函数申请的资源，以避免资源泄露。
    在整个拷贝过程中如果出现任何错误，我们都可以通过调用目的数据库连接的sqlite3_errcode()函数来获取具体的错误码。此外，如果 sqlite3_backup_step()调用失败，由于sqlite3_backup_finish()函数并不会修改当前连接的错误码，因此我们可 以在调用sqlite3_backup_finish()之后再获取错误码，从而在代码中减少了一次错误处理。见如下代码示例(来自SQLite官网)：

/*
pInMemory 指数据连接句柄指针;
装载数据库文件,指在sqlite>.backup '/ruiy.db'中备份的数据库数据到当前连接的sqlite数据库,或是保存当前连接的数据库数据库数据到数据库文件;in-memory database,

zFileName 指保存或恢复的数据库database file文件;

isSave is non-zero(非零),contents of the file zFileName are overwritten with the contents of the database opend by pInMemory(指的是根据isSave,决定是否使用sqlite的数据库备份文件覆盖一个sqlite 类似于ODBC or JDBC URL连接的数据库的数据,当parameter isSave is zero,指将当前sqlite连接的数据库中的数据备份出来替换Replace zFileName指向的数据库备份文件);
operator successfully return code --> SQLITE_OK

ExampleCode,
 int loadOrSaveDb(sqlite3 *pInMemory,const char *zFilename,int isSave){
    //参数再说明,pInMemory sqlite3数据库连接句柄指针
    //*zFileName 磁盘sqlite备份文件
    //isSave决定是从sqlite的数据库连接URL备份数据库数据到磁盘文件，还是恢复磁盘sqlite备份文件到连接的数据库;
  int rc; //
  sqlite3 *pFile; //数据库 备份文件;
  sqlite3_backup *pBackup; //
  sqlite3 *pTo; //
  sqlite3 *pFrom; //
// Open the database file identified by zFilename,
rc = sqlite3_open(zFilename,&pFile); //地址;
if (rc ==SQLITE_OK) {
/*
'load database file to sqlite JDBC' (isSave == 0)
'save ' (isSave == 1) 从连接的sqlite数据库中导出数据到磁盘文件;
*/
pFrom = (isSave ? pInMemory: pFile);
pTo = (isSave ? pFile : pInMemory);
/*

*/
pBackup = sqlite3_backup_init(p_to,"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;
}
*/

SQLite alive backUP Advance APIs

在上面的例子中，我们是通过sqlite3_backup_step()函数的一次调用完成了整个拷贝过程。该实现方式仍然存在之前说过的挂起其它写访问连接的问题，为了解决该问题，这里我们将继续介绍另外一种更高级的实现方式--分片拷贝，其实现步骤如下：
    1). 函数sqlite3_backup_init()用于创建sqlite3_backup对象，该对象将作为本次拷贝操作的句柄传给其余两个函数。
    2). 函数sqlite3_backup_step()被调用用于拷贝数据，和之前方法不同的是，该函数的第二个参数不再是-1，而是一个普通的正整数，表示每次调用将会拷贝的页面数量，如5。
    3). 如果在函数sqlite3_backup_step()调用结束后，仍然有更多的页面需要被拷贝，那么我们将主动休眠250ms，然后再重复步骤2).
    4). 函数sqlite3_backup_finish()用于释放sqlite3_backup_init()函数申请的资源，以避免资源泄露。
    在上述步骤3)中我们主动休眠250ms，此期间，该拷贝操作不会在源数据库上持有任何读锁，这样其它的数据库连接在进行写操作时亦将不会被挂起。然而在 休眠期间，如果另外一个线程或进程对源数据库进行了写操作，SQLite将会检测到该事件的发生，从而在下一次调用 sqlite3_backup_step()函数时重新开始整个拷贝过程。唯一的例外是，如果源数据库不是in-memory数据库，同时写操作是在与拷 贝操作同一个进程内完成，并且在操作时使用的也是同一个数据库连接句柄，那么目的数据库中数据也将被此操作同时自动修改。在下一次调用 sqlite3_backup_step()时，也将不会有任何影响发生。　　
    事实上，在SQLite中仍然提供了另外两个辅助性函数backup_remaining()和backup_pagecount()，其中前者将返回在当前备份操作中还有多少页面需要被拷贝，而后者将返回本次操作总共需要拷贝的页面数量。显而易见的是，通过这两个函数的返回结果，我们可以实时显示本次备份操作的整体进度，计算公式如下：
    Completion = 100% * (pagecount() - remaining()) / pagecount()

/*
online backup of database pDb to database file named by zFilename 5database pages from pDb to zFilename(sqlite磁盘备份文件)
then unlocks pDb and sleeps for 250ms,repeats the process until the entire database is backend up;

*/
int backupDb(
sqlite3 *pDb
)

 sqlite优化之临时文件

简介：

    尽管SQLite的数据库是由单一文件构成，然而事实上在SQLite运行时却存在着一些隐含的临时文件，这些临时文件是出于不同的目的而存在的，对于 开发者而言，它们是透明的，因此在开发的过程中我们并不需要关注它们的存在。尽管如此，如果能对这些临时文件的产生机制和应用场景有着很好的理解，那么对 我们今后应用程序的优化和维护都是极有帮助的。在SQLite中主要产生以下七种临时文件，如：
    1). 回滚日志。
    2). 主数据库日志。
    3). SQL语句日志。
    4). 临时数据库文件。
    5). 视图和子查询的临时持久化文件。
    6). 临时索引文件。
    7). VACUUM命令使用的临时数据库文件。
    
二、具体说明：

    1. 回滚日志：
    SQLite为了保证事物的原子性提交和回滚，在事物开始时创建了该临时文件。此文件始终位于和数据库文件相同的目录下，其文件名格式为: 数据库文件名 + "-journal"。换句话说，如果没有该临时文件的存在，当程序运行的系统出现任何故障时，SQLite将无法保证事物的完整性，以及数据状态的一致性。该文件在事物提交或回滚后将被立刻删除。
    在事物运行期间，如果当前主机因电源故障而宕机，而此时由于回滚日志文件已经保存在磁盘上，那么当下一次程序启动时，SQLite在打开数据库文件的过 程中将会发现该临时文件的存在，我们称这种日志文件为"Hot Journal"。SQLite会在成功打开数据库之前先基于该文件完成数据库的恢复工作，以保证数据库的数据回复到上一个事物开始之前的状态。
    在SQLite中我们可以通过修改journal_mode pragma，而使SQLite对维护该文件采用不同的策略。缺省情况下该值为DELETE， 即在事物结束后删除日志文件。而PERSIST选项值将不会删除日志文件，而是将回滚日志文件的头部清零，从而避免了文件删除所带的磁盘开销。再有就是 OFF选项值，该值将指示SQLite在开始事物时不产生回滚日志文件，这样一旦出现系统故障，SQLite也无法再保障数据库数据的一致性。

    2. 主数据库日志：
    在SQLite中，如果事物的操作作用于多个数据库，即通过ATTACH命令附加到当前连接中的数据库，那么SQLite将生成主数据库日志文件以保证 事物产生的改变在多个数据库之间保持原子性。和回滚日志文件一样，主数据库日志文件也位于当前连接中主数据库文件所处的目录内，其文件名格式为：主数据库 文件名 + 随机的后缀。在该文件中，将包含所有当前事物将会改变的Attached数据库的名字。在事物被提交之后，此文件亦被SQLite随之删除。
    主数据库日志文件只有在某一事物同时操作多个数据库时(主数据库和Attached数据库)才有可能被创建。通过该文件，SQLite可以实现跨多个数 据库的事物原子性，否则，只能简单的保证每个单一的数据库内的状态一致性。换句话说，如果该事物在执行的过程中出现系统崩溃或主机宕机的现象，在进行数据 恢复时，若没有该文件的存在，将会导致部分SQLite数据库处于提交状态，而另外一部分则处于回滚状态，因此该事物的一致性将被打破。

    3. SQL语句日志：
    在一个较大的事物中，SQLite为了保证部分数据在出现错误时可以被正常回滚，所以在事物开始时创建了SQL语句日志文件。比如，update语句修 改了前50条数据，然而在修改第51条数据时发现该操作将会破坏某字段的唯一性约束，最终SQLite将不得不通过该日志文件回滚已经修改的前50条数 据。
    SQL语句日志文件只有在INSERT或UPDATE语句修改多行记录时才有可能被创建，与此同时，这些操作还极有可能会打破某些约束并引发异常。但是 如果INSERT或UPDATE语句没有被包含在BEGIN...COMMIT中，同时也没有任何其它的SQL语句正在当前的连接上运行，在这种情况 下，SQLite将不会创建SQL语句日志文件，而是简单的通过回滚日志来完成部分数据的UNDO操作。
    和上面两种临时文件不同的是，SQL语句日志文件并不一定要存储在和数据库文件相同的目录下，其文件名也是随机生成。该文件所占用的磁盘空间需要视UPDATE或INSERT语句将要修改的记录数量而定。在事物结束后，该文件将被自动删除。

    4. 临时数据库文件：
    当使用"CREATE TEMP TABLE"语法创建临时数据表时，该数据表仅在当前连接内可见，在当前连接被关闭后，临时表也随之消失。然而在生命期内，临时表将连同其相关的索引和视 图均会被存储在一个临时的数据库文件之内。该临时文件是在第一次执行"CREATE TEMP TABLE"时即被创建的，在当前连接被关闭后，该文件亦将被自动删除。最后需要说明的是，临时数据库不能被执行DETACH命令，同时也不能被其它进程 执行ATTACH命令。
   
    5. 视图和子查询的临时持久化文件：
    在很多包含子查询的查询中，SQLite的执行器会将该查询语句拆分为多个独立的SQL语句，同时将子查询的结果持久化到临时文件中，之后在基于该临时 文件中的数据与外部查询进行关联，因此我们可以称其为物化子查询。通常而言，SQLite的优化器会尽力避免子查询的物化行为，但是在有些时候该操作是无 法避免的。该临时文件所占用的磁盘空间需要依赖子查询检索出的数据数量，在查询结束后，该文件将被自动删除。见如下示例：
    SELECT * FROM ex1 WHERE ex1.a IN (SELECT b FROM ex2);
    在上面的查询语句中，子查询SELECT b FROM ex2的结果将会被持久化到临时文件中，外部查询在运行时将会为每一条记录去检查该临时文件，以判断当前记录是否出现在临时文件中，如果是则输出当前记 录。显而易见的是，以上的行为将会产生大量的IO操作，从而显著的降低了查询的执行效率，为了避免临时文件的生成，我们可以将上面的查询语句改为：
    SELECT * FROM ex1 WHERE EXISTS(SELECT 1 FROM ex2 WHERE ex2.b=ex1.a);
    对于如下查询语句，如果SQLite不做任何智能的rewrite操作，该查询中的子查询也将会被持久化到临时文件中，如：
    SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;
    在SQLite自动将其修改为下面的写法后，将不会再生成临时文件了，如：
    SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a;

    6. 临时索引文件：
    当查询语句包含以下SQL从句时，SQLite为存储中间结果而创建了临时索引文件，如：
    1). ORDER BY或GROUP BY从句。
    2). 聚集查询中的DISTINCT关键字。
    3). 由UNION、EXCEPT和INTERSECT连接的多个SELECT查询语句。
    需要说明的是，如果在指定的字段上已经存在了索引，那么SQLite将不会再创建该临时索引文件，而是通过直接遍历索引来访问数据并提取有用信息。如果 没有索引，则需要将排序的结果存储在临时索引文件中以供后用。该临时文件所占用的磁盘空间需要依赖排序数据的数量，在查询结束后，该文件被自动删除。

    7. VACUUM命令使用的临时数据库文件：
    VACUUM命令在工作时将会先创建一个临时文件，然后再将重建的整个数据库写入到该临时文件中。之后再将临时文件中的内容拷贝回原有的数据库文件中，最后删除该临时文件。
    该临时文件所占用的磁盘空间不会超过原有文件的尺寸。

三、相关的编译时参数和指令：

    对于SQLite来说，回滚日志、主数据库日志和SQL语句日志文件在需要的时候SQLite都会将它们写入磁盘文件，但是对于其它类型的临时文 件，SQLite是可以将它们存放在内存中以取代磁盘文件的，这样在执行的过程中就可以减少大量的IO操作了。要完成该优化主要依赖于以下三个因素：
    1. 编译时参数SQLITE_TEMP_STORE：
    该参数是源代码中的宏定义(#define)，其取值范围是0到3(缺省值为1)，见如下说明：
    1). 等于0时，临时文件总是存储在磁盘上，而不会考虑temp_store pragma指令的设置。
    2). 等于1时，临时文件缺省存储在磁盘上，但是该值可以被temp_store pragma指令覆盖。
    3). 等于2时，临时文件缺省存储在内存中，但是该值可以被temp_store pragma指令覆盖。
    4). 等于3时，临时文件总是存储在内存中，而不会考虑temp_store pragma指令的设置。
    
    2. 运行时指令temp_store pragma：
    该指令的取值范围是0到2(缺省值为0)，在程序运行时该指令可以被动态的设置，见如下说明：
    1). 等于0时，临时文件的存储行为完全由SQLITE_TEMP_STORE编译期参数确定。
    2). 等于1时，如果编译期参数SQLITE_TEMP_STORE指定使用内存存储临时文件，那么该指令将覆盖这一行为，使用磁盘存储。
    2). 等于2时，如果编译期参数SQLITE_TEMP_STORE指定使用磁盘存储临时文件，那么该指令将覆盖这一行为，使用内存存储。
    
    3. 临时文件的大小：
    对于以上两个参数，都有参数值表示缺省情况是存储在内存中的，只有当临时文件的大小超过一定的阈值后才会根据一定的算法，将部分数据写入到磁盘中，以免临时文件占用过多的内存而影响其它程序的执行效率。
    
    最后在重新赘述一遍，SQLITE_TEMP_STORE编译期参数和temp_store pragma运行时指令只会影响除回滚日志和主数据库日志之外的其它临时文件的存储策略。换句话说，回滚日志和主数据库日志将总是将数据写入磁盘，而不会 关注以上两个参数的值。

四、其它优化策略：

    在SQLite中由于采用了Page Cache的缓冲优化机制，因此即便临时文件被指定存储在磁盘上，也只有当该文件的大小增长到一定的尺寸后才有可能被SQLite刷新到磁盘文件上，在此 之前它们仍将驻留在内存中。这就意味着对于大多数场景，如果临时表和临时索引的数据量相对较少，那么它们是不会被写到磁盘中的，当然也就不会有IO事件发 生。只有当它们增长到内存不能容纳的时候才会被刷新到磁盘文件中的。其中SQLITE_DEFAULT_TEMP_CACHE_SIZE编译期参数可以用 于指定临时表和索引在占用多少Cache Page时才需要被刷新到磁盘文件，该参数的缺省值为500页。

sqlite内存数据库

一、内存数据库：

    在SQLite中，数据库通常是存储在磁盘文件中的。然而在有些情况下，我们可以让数据库始终驻留在内存中。最常用的一种方式是在调用sqlite3_open()的时候，数据库文件名参数传递":memory:"，如：
    rc = sqlite3_open(":memory:", &db);
    在调用完以上函数后，不会有任何磁盘文件被生成，取而代之的是，一个新的数据库在纯内存中被成功创建了。由于没有持久化，该数据库在当前数据库连接被关 闭后就会立刻消失。需要注意的是，尽管多个数据库连接都可以通过上面的方法创建内存数据库，然而它们却是不同的数据库，相互之间没有任何关系。事实上，我 们也可以通过Attach命令将内存数据库像其他普通数据库一样，附加到当前的连接中，如：
    ATTACH DATABASE ':memory:' AS aux1;
   
二、临时数据库：

    在调用sqlite3_open()函数或执行ATTACH命令时，如果数据库文件参数传的是空字符串，那么一个新的临时文件将被创建作为临时数据库的底层文件，如：
    rc = sqlite3_open("", &db);
    或
    ATTACH DATABASE '' AS aux2;
    和内存数据库非常相似，两个数据库连接创建的临时数据库也是各自独立的，在连接关闭后，临时数据库将自动消失，其底层文件也将被自动删除。
    尽管磁盘文件被创建用于存储临时数据库中的数据信息，但是实际上临时数据库也会和内存数据库一样通常驻留在内存中，唯一不同的是，当临时数据库中数据量过 大时，SQLite为了保证有更多的内存可用于其它操作，因此会将临时数据库中的部分数据写到磁盘文件中，而内存数据库则始终会将数据存放在内存中。