iOS 数据持久化(2):SQLite3

@import url(http://i.cnblogs.com/Load.ashx?type=style&file=SyntaxHighlighter.css);

@import url(/css/cuteeditor.css);

@import url(http://i.cnblogs.com/Load.ashx?type=style&file=SyntaxHighlighter.css);

@import url(/css/cuteeditor.css);

@import url(http://i.cnblogs.com/Load.ashx?type=style&file=SyntaxHighlighter.css);

@import url(/css/cuteeditor.css);

@import url(http://i.cnblogs.com/Load.ashx?type=style&file=SyntaxHighlighter.css);

@import url(/css/cuteeditor.css);

@import url(/css/cuteeditor.css);

1. 功能

第三个持久化选项是IOS的嵌入式SQL数据库,名为SQLite3,但其并非是Apple所提供的,而是第三方的数据库,可以应用于IOS、android、Linux和windows等系统。SQLite3提供多种方式的接口,有命令行的接口、客户端及多种语言的API接口。

本文只针对IOS系统下的C语言编程接口进行讨论,所以先介绍C语言的编程方式。

1.1 第一个程序:封装查询

如下是使用 sqlite3 C API的源文件,该程序主要功能是创建一个数据库文件,接着创建一个表,然后插入一条数据,最后查询表输出表内容。

 1 int main()
 2 {
 3         sqlite3 *db;
 4         char *zErrMsg = 0;
 5         int rc;
 6         char *baseName ="base";
 7         char *createSQL = "create table episodes(id integer primary key, season int, name text);";
 8         char *insertSQL = "insert into episodes(id,name) values(1,\"hello\");";
 9         char *selectSQL = "select * from episodes;";
10 
11         rc = sqlite3_open(baseName, &db);     //创建数据库
12         rc = sqlite3_exec(db, createSQL, NULL, NULL, &zErrMsg);    //创建表
13         rc = sqlite3_exec(db, insertSQL, NULL, NULL, &zErrMsg);    //插入数据
14 
15         char **result;
16         int nrow,  ncolumn;
17 
18         rc = sqlite3_get_table(db, selectSQL,&result,&nrow,&ncolumn,&zErrMsg);     //查询表格
19         int i, j;
20         for(i=0; i<=nrow; i++)
21         {
22                 for(j=0; j<ncolumn; j++)
23                     printf("%s\t",result[i*ncolumn+j]);
24                 printf("\n");
25         }
26 
27        sqlite3_close(db);
28        return 0;
29 }

编译命令:

在Linux平台上,要编译sqlite3源文件,需要将sqlite3.c文件拷贝到源程序文件所在的目录,然后按如下进行编译。

gcc sqlite3.c firstSqlite.c -lpthread –ldl 

1.2 第二个程序:准备查询

准备查询由三个步骤组成:准备、执行和完成

  • 查询例程
     1 void test_prepare_select()
     2 {
     3         int rc, i,ncols;
     4         sqlite3 *db;
     5         sqlite3_stmt *stmt;
     6         char *sql="select * from episodes;";;
     7         const char *tail;
     8 
     9         rc = sqlite3_open("base",&db);   //fprintf(stderr, "can't  open datebase:%s\n",sqlite3_errmsg(db));
    10         rc = sqlite3_prepare_v2(db,sql,-1,&stmt,&tail);  //准备
    11         rc = sqlite3_step(stmt);      //执行
    12         while(rc != SQLITE_DONE)
    13         {
    14                 ncols = sqlite3_column_count(stmt);
    15                 for(i=0; i<ncols; i++)
    16                 {
    17                         printf("'%s'",sqlite3_column_text(stmt,i));
    18                 }
    19                 printf("\n");
    20                 rc = sqlite3_step(stmt);
    21         }
    22 
    23         sqlite3_finalize(stmt);     //完成
    24         sqlite3_close(db);
    25 }
  • 绑定  
 1 void test_prepare_bind()
 2 {
 3         int rc, i,ncols;
 4         sqlite3 *db;
 5         sqlite3_stmt *stmt;
 6         char *sql = "insert into episodes (id,name) values (?, ?);";
 7         const char *tail;
 8         char *createSQL = "create table episodes(id integer primary key, season int, name text);";
 9         char *zErrMsg = 0;
10 
11         sqlite3_open("base",&db);
12         sqlite3_exec(db, createSQL, NULL, NULL, &zErrMsg);
13 
14         sqlite3_prepare_v2(db,sql,-1,&stmt,&tail);
15         sqlite3_bind_int(stmt, 1, 1);
16         sqlite3_bind_text(stmt,2,"hello", strlen("hello"), SQLITE_TRANSIENT);
17         sqlite3_step(stmt);
18     
19         sqlite3_finalize(stmt);
20         sqlite3_close(db);
21 }

2.  SQLite3设计与概念

2.1 API对象模型

SQLite3 C API 的对象模型结构可以由下图表示,即任何一个SQlite3数据库都存储在操作系统文件中——一个数据库对应一个操作系统文件。

iOS 数据持久化(2):SQLite3

图 2

2.1.1 连接与语句

API中与查询处理有关的两个基本数据结构是连接(sqlite3)和语句(sqlite3_stmt),API中所有的主要操作都在使用这两个结构。其中连接负责管理语句,由于在一个时刻,一个连接中只能存在一个语句,所以在每个连接中都设置一个table Locks的锁,从而保证这种唯一性。

      1)连接:sqlite3

             一个连接对象代表到数据库的连接和事务上下文,statement来自连接对象;

      2)语句:sqlite3_Stmt

           一个statement对象代表了一个编译的SQL语句。在内部,它使用VDBE字节码来表示,VDBE字节码是一个会执行SQL命令的程序。statement包括执行一个命令所需要的一切东西。

2.1.2 B-tree与Pager

一个连接对象可以连接到多个数据库对象——一个主数据库和多个附加数据库。每个数据库对象都有一个B-tree和相应的一个pager对象。statement使用它们连接对象的B-tree和pager从数据库中读取数据,向数据库写入数据。并且保证一个数据库只存在一个连接,从而产生一个Database Lock的锁。

     1)B-tree

B-tree负责与语句进行交互,即读取数据库的statement使用游标遍历B-tree。然后b-tree从pager中读取数据。

     2)pager

若一个游标想要访问页面,则pager必须从内存加载到磁盘中,然后返回给b-tree。并且当游标修改页面时,pager必须采取特殊措施保留原始页面,确保在发生事务回滚时可以回到原来的状态。即pager负责读写数据库,维护内存缓存或者页面,管理事务。

2.2 核心API

核心API是用来执行SQL命令的,它由各种用来执行查询的函数和管理数据库的实用函数组成。执行SQL命令有两种方法:准备查询封装查询。在API和SQLite内部,准备查询方法最终执行所有命令的方法,它包含准备执行完成三个阶段,每个阶段都有一个单独的API函数而封装查询方法是将准备查询的三个步骤封装成为一个函数调用,并提供了一次执行SQL命令的简便方法。即

  • 准备查询

         1)准备:sqlite3_prepare();

         2)执行:sqlite3_step();

         3)完成:sqlite3_finalize();

  • 封装查询

    1)sqlite3_exec()

  • 2)sqlite3_get_table()

2.2.1 连接数据库

因为SQLite数据库其实就是一个操作系统文件,所以连接数据库其实就是打开系统文件。C API 中用于连接或打开一个数据库的函数sqlite3_open(),基本上就是一个打开文件的系统调用。若打开的数据库文件不存在,则创建一个新的数据库;若存在则将SQLite文件打开。一旦开打一个数据库文件,那么将使用一个不透明的sqlite3连接句柄来表示它,该句柄表示到数据库的一个连接。sqlite扩展中,连接对象抽象了该句柄,并且有时一些对应的API函数实现方法将该句柄作为参数。

2.2.2 执行准备查询

正如上述所述,准备查询方法中执行一条SQL命令,需要由三个步骤组成:

       1)准备

            在C API中,这个步骤由sqlite3_prepare_ve()函数完成,该函数直接与编译器对话。编译器将命令编译成为VDBE字节码,即创建sqlit3_stmt句柄。

       2)执行

           执行是一个逐步的过程,CAPI中每一步都由sqlite3_step()发起,并使VDBE逐步执行字节码,第一次调用sqlite3_step()通常需要某种类型的锁,锁的类型根据    执行什么样的命令(读或写)而变化。对于select语句,sqlite3_step()的每次调用将使语句句柄的游标位置移动到结果集的下一行。对于结果集中的每一行,游标未到达结果集末尾时,将返回SQLITE_ROW;否则,将返回SQLITE_DONE。对于其他SQL语句(insert、update、delete等),第一次调用sqlite3_step()将促使VDBE执行整个命令。

      3)完成

VDBE关闭语句并释放资源。C API中,由sqlite_finalize()来执行,该函数使得VDBE终止程序、释放资源并关闭statement句柄。

每一步(准备、执行、完成)对应statement句柄的各自状态(准备状态、执行状态、完成状态)。准备状态以为这所有必需的资源以及分配,statement已准备执行,但是没有启动,没有获得锁,也没有申请获得锁,直到第一次调用sqlite3_step()时才开始获取锁。活动状态从第一次调用sqlite3_step()开始。此时,statement处于执行过程中,并且开始使用某种类型的锁。完成状态意味着开始使用关闭,并且所有相关的资源已被释放。如下图所示:

iOS 数据持久化(2):SQLite3

图 3

2.2.3 使用参数化SQL

SQLite3语句可以包含参数,即该参数就是占位符,可能会在编译后为其提供(或"绑定")值。当sqlite3_prepare_v2()编译带有参数的语句时,它在结果语句句柄中为这些参数分配占位符。然后,期望语句执行前为这些参数提供值,如果参数没有绑定值,执行该语句时SQLite默认使用NULL。

其中提供了两种方式的绑定:位置绑定和命名绑定;

insert into episodes(id, name) values (?, ?); 
insert into episodes(id, name) values(:id,:name)

      1)位置绑定

位置绑定是通过问号定义的,第一个问号表示位置1,第二个为2,依次类推。

      2)命名绑定

命名绑定使用实际变量名,以冒号作为前缀。

参数绑定的优点是无需重新编译,就可以多次执行相同的语句。只需重置该语句、绑定新值、并重新执行。这时重置函数(sqlite3_reset)就可以发挥作用了:它可以避免SQL编译的开销;并且参数绑定能够使SQlite会处理绑定参数的转义字符。

3. 核心C API

SQLite v3 API包括许多函数。不过,只有8个函数是实际处理连接、处理查询,并断开与数据库连接所必需的。其余函数可以归入专门用于完成特定任务的小组中。可以查询C API参考手册(网址是www.sqlite.org/c3ref/intro.html)。

3.1 连接与断开连接: sqlite3_open_v2、sqlite3_close

在执行SQL命令前,首先要连接数据库。也许将连接数据库称为打开数据库才是更好的表述,因为SQLite数据库包含在单个操作系统文件(一个文件对应一个数据库)中。同理,断开连接可以成为关闭数据库。

      1)打开数据库

int sqlite3_open_v2(
    const char *filename,    //数据库文件名
    sqlite3 **ppDb,        //OUT:SQLite数据库句柄
    int flags,                //标志
    const char *zVfs        //要使用的VFS模块的名称
);

参数filename是操作系统文件,或文本字符串':memory:',或空字符串,或空指针。如果使用':memory:',sqlite3_open_v2()将在内存中创建数据库,该数据库只存在于连接生成期间。如果文件名是空字符串或null,sqlite3_open_v2()将打开磁盘文件,并在连接关闭时自动删除该文件。

      2)关闭数据库

int sqlite3_close(sqlite3*)

要成功执行sqlite3_clsose(),必须完成与连接关联的所有准备查询。只要有一个查询仍未完成,sqlite3_close()都会返回SQLITE_BUSY并显示错误消息"由于有未完成的语句,所以无法关闭连接"。

3.2 封装查询

3.2.1 执行查询:sqlite3_exec

        函数sqlite3_exec()提供一种快速、方便执行SQL命令的方法,对修改数据库的命令(即不返回任何数据的命令)特别方便。因此,通常也称为便捷函数,一个简单的API调用就能很好地封装很多其他任务。该函数的声明如下:

int sqlite3_exec(
    sqlite3*,             //打开的数据库
    const char *sql,    //要执行的SQL
    sqlite_callback,    //回调函数
    void *dta,            //回调函数的第一个参数
    char **errmsg        //错误信息
);

其中SQlite提供的回调函数声明如下:

typedef  int (*sqlite3_callback)(
    void*,        //sqlite3_exe()函数第四个参数提供的数据
    int,            //行中字段的数目
    char**,        //代表行中字段名称的字符串数组
    char**        //代表字段名称的字符串数组
)

3.2.2 获取查询:sqlite3_get_table

      行数sqlite3_get_table()返回单独函数调用中一个命令的整个结果集,与sqlite3_exec()类似也是封装了准备查收API函数的一些函数,可以一下运行全部的命令。sqlite3_get_table()函数接收SQL语句并返回所有记录,使用堆上声明的内存(使用sqlite3_malloc())将他们存储在参数result中。必须使用sqlite3_free_table()释放内存,该函数将resultp指针作为唯一的参数。resultp中的一个记录实际上不是记录,而是结果集中列的名称。

int sqlite3_get_table(
    sqlite3*,                //打开的数据库
    const char *sql,        //要执行的SQL
    char ***resultp,        //结果写入该指针执行的char *[]
    int *nrow,            //结果集中行的数目
    int *ncolumn,            //结果集中字段的数目
    char **errmsg            //错误信息
)

3.4 准备查询

3.3.1 准备

编译或准备接收SQL语句,并将其编译为虚拟数据库引擎(VDBE)可读的字节码。这是由函数sqlite3_prepare_v2()完成的,其声明如下:

int sqlite3_prepare_v2(
    sqlite3 *db,            //数据库句柄
    const char *zSql,        //SQL文本
    int nBytes,
    ssqlite3_stmt **ppStmt,
    const char **pzTail
)

3.3.2 执行

一旦查询语句准备就绪,下一步就是使用sqlite3_step()执行,sqlite3_step()声明如下:

int sqlite3_step(sqlite3_stmt *pStmt);

对于不返回数据的SQL语句,对sqlite3_step()的第一次调用就执行完SQL语句,并返回一个指示结果的代码。对于返回数据的SQL语句,例如select语句,sqlite3_step()第一次调用将语句定位在第一个记录的B-tree光标上。后续调用的sqlite3_step()将光标定位在结果集内的后续记录。到底末尾值之前,sqlite3_step()为结果集中的每个记录返回SQLITE_ROW。返回SQLITE_DONE,表示游标已到底结果集末尾。

3.3.3 完成与重置

一旦语句执行结束,必须终止。可以使用如下之一完成或重置语句:

int slqite3_finalize(sqlite3_stmt *pStmt);
int sqlite3_reset(sqlite3_stmt *pStmt);
sqlite3_finalize()将关闭语句。释放资源并提交或回滚任何隐式事务。

如果要重复使用语句,可以使用sqlite3_reset()。该函数将保持已编译的SQL语句(和任何绑定的参数),但是会将单签语句相关联的变化提交到数据库。执行sqlite3_finalize()后将释放资源并提交或回滚任何隐藏事物,清除日志文件并释放相关的锁。sqlite3_finalize()和sqlite3_reset()之间的主要差别在于后者保留与语句关联的资源,使它可以重新执行,避免了再次调用sqlite3_prepare()编译SQL命令。

3.4 获取记录

对于返回记录的语句而言,可以使用函数sqlite3_column_count()和sqlite3_dta_count()获得结果集中的字段数,这两个函数声明如下:

int sqlite3_column_count(sqlite3_stmt *pStmt);
int sqlite3_data_count(sqlite3_stmt *pStmt);

3.4.1 获取字段信息

可以是会用sqlite3_column_name()函数获取当前记录中的所有列。同样,可以使用sqlite3_column_type()函数获取每个字段关联的存储类。

3.4.2 获取字段值

可以使用sqlite3_column_xxx()函数获取当前记录中的所有字段值,该函数的通用形式如下:

xxx sqlite3_column_xxx(
    sqltie3_stmt *,    //语句句柄
    int iCol            //字段的次序
)

3.5 查询参数化

       准备语句后,可以使用sqlite3_bind_xxx()函数绑定参数值。这些函数的一般形式如下:

sqlite3_bind_xxx(
    sqlite3_stmt*,        //语句句柄
    int I,                //参数个数
    xxx value            //绑定的值
)

4. IOS使用

4.1 配置

由于SQLite3是第三方的数据库,若想在Xcode中使用需要做一些配置。

4.1.1 链接到SQLite3库

在项目中的Build Phases选项中的Link Binary With Libraries下,点击其"+"号,从而添加所需要的libsqlite3.tbd或者是libsqlite3.dylib库。如图 4所示。

iOS 数据持久化(2):SQLite3

图 4

4.1.2 添加SQLite3头文件

由于SQLite3没有提供swift和Object-C的API接口,所以只能使用其提供的C语言接口。那么在Swift环境中使用SQLite3需要添加桥接头文件;而在Object-C环境中则可以使用C语言接口。如图 5所示。

iOS 数据持久化(2):SQLite3

图 5

4.2 API 调用

SQLite3的API有两种使用方式:封装查询和准备查询。准备查询是由三个步骤组成:准备、执行和完成;而封装查询是对准备查询的三个步骤进行封装,从而只需一步就能完成三个步骤。由于在Object-C中可以直接使用SQLite3的API,所以本文就不对其进行详述,而讨论在swift环境中的SQLite3使用进行讨论。具体SQLite3的设计和概念可以参考另一片博文:SQLite3 API总结。

4.2.1 封装查询:swift 

 1 func testSqlite3()
 2 {
 3         //1:获取Documents的路径,并创建file.txt的路径
 4         let paths = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true);
 5         var documentsDirectory = paths[0] as String;
 6         documentsDirectory = documentsDirectory.stringByAppendingString("/database");
 7         
 8         var db:COpaquePointer = nil;
 9         var rc:Int32
10         var zErrMsg:UnsafeMutablePointer<Int8> = UnsafeMutablePointer<Int8>.alloc(1)
11 
12         let baseName = documentsDirectory;
13         let createSQL = "create table episodes(id integer primary key, season int, name text);";
14         let insertSQL = "insert into episodes(id,name) values(1,\"hello\");";
15         let selectSQL = "select * from episodes;";
16 
17         rc = sqlite3_open(baseName, &db);     //创建数据库
18         rc = sqlite3_exec(db, createSQL, nil, nil, &zErrMsg);    //创建表
19         rc = sqlite3_exec(db, insertSQL, nil, nil, &zErrMsg);    //插入数据
20         var result:UnsafeMutablePointer<UnsafeMutablePointer<Int8>> = UnsafeMutablePointer<UnsafeMutablePointer<Int8>>.alloc(322)
21         var nrow:Int32 = 0
22         var ncolumn:Int32 = 0
23         
24         rc = sqlite3_get_table(db, selectSQL,&result, &nrow, &ncolumn, &zErrMsg);     //查询表格
25     
26         var i:Int32
27         var j:Int32
28         for i=0; i <= nrow; i++
29         {
30             for j=0; j<ncolumn; j++
31             {
32             let ij:Int = Int(i*ncolumn+j)
33                 if nil != result[ij]
34                 {
35                     NSLog(String.fromCString(result[ij])!);
36                 }
37             }
38                 print("\n");
39         }
40         sqlite3_close(db);
41 }

4.2.2 准备查询:swift

 1 func prepareSQLite3()
 2 {
 3         //1:获取Documents的路径,并创建file.txt的路径
 4         let paths = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true);
 5         var documentsDirectory = paths[0] as String;
 6         documentsDirectory = documentsDirectory.stringByAppendingString("/database");
 7 
 8         var db:COpaquePointer = nil;
 9         var statement:COpaquePointer = nil
10         var rc:Int32
11         var zErrMsg:UnsafeMutablePointer<Int8> = UnsafeMutablePointer<Int8>.alloc(1)
12 
13         let baseName = documentsDirectory;
14         let createSQL = "create table episodes(id integer primary key, season integer, name text);";
15         let insertSQL = "insert into episodes(id,season,name) values(2,2,\"hello\");";
16         let selectSQL = "select * from episodes;";
17         
18         rc = sqlite3_open(baseName, &db);     //创建数据库
19         rc = sqlite3_exec(db, createSQL, nil, nil, &zErrMsg);    //创建表
20         rc = sqlite3_exec(db, insertSQL, nil, nil, &zErrMsg);    //插入数据
21 
22         //准备查询
23         sqlite3_prepare_v2(db, selectSQL, -1, &statement, nil)
24         rc = sqlite3_step(statement)
25         while rc != SQLITE_DONE
26         {
27             let ncols = sqlite3_column_count(statement);
28             for var i:Int32 = 0; i < ncols; i++
29             {
30                 if nil != sqlite3_column_text(statement, i)
31                 {
32                     NSLog(String.fromCString(UnsafePointer<CChar>(sqlite3_column_text(statement, i)))!)
33                 }
34             }
35             rc = sqlite3_step(statement)
36         }
37         
38         sqlite3_finalize(statement)
39         sqlite3_close(db)
40 }
上一篇:ios数据存储——数据库:SQlite3以及第三方库FMDB


下一篇:iOS开发之数据存储之SQLite3(包括FMDB)