Sqlite3详细解读

#import "ViewController.h"
// 首先添加libsqlite3.0.dylib库，再导入头文件
#import <sqlite3.h>
// 数据库名称
#define DatabaseName @"database.sqlite3"
@interface ViewController () {
    sqlite3* _pDB; // 指向SQLite数据库的指针，非OC对象
}
- (const char* )databasePath;
- (IBAction)clickButton:(id)sender;
@end

- (void)viewDidLoad
{
    [super viewDidLoad];
    /* 在执行任何SQL语句之前，必须首先连接到一个数据库，也就是打开或者新建一个SQlite3数据库文件。连接数据库由sqlite3_open函数完成，它一共有上面3个版本。其中 sqlite3_open函数假定SQlite3数据库文件名为UTF-8编码，sqlite3_open_v2是它的加强版。sqlite3_open16函数假定SQlite3数据库文件名为UTF-16（Unicode宽字符）编码。
      */
                                                  
    _pDB = NULL; // 定义指向代表SQLite数据库结构体的指针
    // SQlite3数据库文件的扩展名没有一个标准定义，比较流行的选择是.sqlite3、.db、.db3。不过在Windows系统平台上，不推荐使用.sdb作为 SQlite3数据库文件的扩展名，据说这会导致IO速度显著减慢，因为.sdb扩展名有其特殊用义。
    // 调用SQlite API时，如果成功则会返回SQLITE_OK，如果调用失败将返回一个错误码（Error code），指明发生了什么错误。对API调用的返回值进行适当检查，可以提高程序的健壮性。
    NSInteger status = sqlite3_open_v2([self databasePath], &_pDB, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
    /* sqlite3_open_v2(const char *filename, sqlite3 **ppDb, int flags, const char *zVfs)
     参数说明：
     filename：需要被打开的数据库文件的文件名，在sqlite3_open和sqlite3_open_v2中这个参数采用UTF-8编码，而在sqlite3_open16中则采用UTF-16编码。注意该参数是c字符串而不是OC.
                                                   
     ppDb：参数ppDb看起来有点复杂，它是一个指向指针的指针。当调用sqlite3_open_xxx函数时，该函数将分配一个新的SQlite3数据结构，然后初始化，然后将指针ppDb指向它。所以客户应用程序可以通过sqlite3_open_xxx函数连接到名为filename的数据库，并通过参数ppDb返回指向该数据库数据结构的指针。如果sqlite不能分配内存来存放sqlite对象，ppDb将会被返回一个NULL值。
                                                   
     flags：作为数据库连接的额外控制的参数，可以是SQLITE_OPEN_READONLY，SQLITE_OPEN_READWRITE和SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE中的一个，用于控制数据库的打开方式。
     SQLITE_OPEN_READONLY，则SQlite3数据库文件以只读的方式打开，如果该数据库文件不存在，则sqlite3_open_v2函数执行失败，返回一个error。
     SQLITE_OPEN_READWRITE，则SQlite3数据库文件以可读可写的方式打开，如果该数据库文件本身被操作系统设置为写保护状态，则以只读的方式打开。如果该数据库文件不存在，则sqlite3_open_v2函数执行失败，返回一个error。
     SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE，则SQlite3数据库文件以可读可写的方式打开，如果该数据库文件不存在则新建一个。这也是sqlite3_open和sqlite3_open16函数的默认行为。除此之外，flags还可以设置为其他标志，具体可以查看SQlite官方文档。
                                                   
     zVfs：允许客户应用程序命名一个虚拟文件系统（Virtual File System）模块，用来与数据库连接。VFS作为SQlite library和底层存储系统（如某个文件系统）之间的一个抽象层，通常客户应用程序可以简单的给该参数传递一个NULL指针，以使用默认的VFS模块。
    */
    if (SQLITE_OK != status) {
        NSLog(@"数据库打开失败，%@", NSStringFromSelector(_cmd));
    }
                                                  
    sqlite3_close(_pDB); // 关闭数据库
    _pDB = NULL;
    //在使用完SQlite数据库之后，需要调用sqlite3_close函数关闭数据库连接，释放数据结构所关联的内存，删除所有的临时数据项。如果在调用sqlite3_close函数关闭数据库之前，还有某些没有完成的（nonfinalized）SQL语句，那么sqlite3_close函数将会返回SQLITE_BUSY错误。客户程序员需要finalize所有的预处理语句（prepared statement）之后再次调用sqlite3_close。
}

// 用于返回沙盒下Document的完整路径（C字符串）
- (const char* )databasePath
{
    NSString* documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString* fullPath = [documentPath stringByAppendingPathComponent:DatabaseName];
    return [fullPath cStringUsingEncoding:NSUTF8StringEncoding];
}

- (IBAction)clickButton:(id)sender {
    UIButton* button = (UIButton* )sender;
                                         
    // 建议：执行任何操作前，打开数据库，操作完成后关闭
    NSInteger status = 0;
    if (_pDB == NULL) {
        status = sqlite3_open_v2([self databasePath], &_pDB, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
        if (SQLITE_OK != status) {
            NSLog(@"数据库打开失败，%@", NSStringFromSelector(_cmd));
            sqlite3_close(_pDB);
            _pDB = NULL;
        }
    }
                                         
    switch (button.tag) {
        case 100: { // 创建表
            [self createTableWithDatabase:_pDB];
        } break;
                                                 
        case 200: { // 添加一行数据
            [self addDataWithDatabase:_pDB];
        } break;
                                                 
        case 300: {
            [self deleteFromDatabase:_pDB];
        } break;
                                                 
        case 400: {
            [self selectWithDatabase:_pDB];
        } break;
                                                 
        default:
            break;
    }
                                         
    // 操作完成后关闭数据库
    sqlite3_close(_pDB);
    _pDB = NULL;   
}

// 这里创建一个表
- (void)createTableWithDatabase:(sqlite3* )db {
                              
    if (!db) {
        return;
    }
    // 定义一个sqlite3_stmt结构体的指针，用于保存编译成字节码的SQL语句
    // 在sqlite中并没有定义sqlite3_stmt这个结构的具体内容，它只是一个抽象类型，在使用过程中一般以它的指针进行操作
    sqlite3_stmt* stmt = NULL;
                              
    // 需要执行的SQL语句，其中的保留字大小写无关，如“create”和“CREATE”一样
    char* szSql = "CREATE TABLE UserInfo(id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, money REAL)";
                              
    NSInteger status = sqlite3_prepare_v2(db, szSql, -1, &stmt, NULL);
    /*
           sqlite3_prepare_v2(sqlite3 *db,
                       const char *szSql,
                       int nByte,
                       sqlite3_stmt **ppStmt,
                       const char **pzTail)
            这个函数将sql文本转换成一个准备语句（prepared statement）对象，同时返回这个对象的指针。这个接口需要一个数据库连接指针以及一个要准备的包含SQL语句的文本。它实际上并不执行（evaluate）这个SQL语句，它仅仅为执行准备这个sql语句（编译SQL语句为字节码留给后面的执行函数执行）。
             参数：
             db：数据指针
                               
             szSql：sql语句，使用UTF-8编码
                               
             nByte：szSql的字节长度。如果nByte为负值，则prepare函数会自动计算出szSql的字节长度，不过要确保szSql传入的是以NULL结尾的字符串。如果nByte不是负的，那么它就是这个函数能从szSql中读取的字节数的最大值。如果nBytes为负，szSql在第一次遇见’/000/或’u000’的时候终止。如果SQL命令字符串中只包含一条SQL语句，那么它没有必要以“;”结尾。
                               
             ppStmt：一个指向指针的指针，用来传回一个指向新建的sqlite3_stmt结构体的指针，sqlite3_stmt结构体里面保存有转换好的SQL语句。如果SQL命令字符串包含多条SQL语句，同时参数pzTail不为NULL，那么它将指向SQL命令字符串中的下一条SQL语句。如果错误发生，它被置为NULL。调用过程必须负责在编译好的sql语句完成使用后使用sqlite3_finalize()删除它。
                               
             pzTail：上面提到szSql在遇见终止符或者是达到设定的nByte之后结束，假如szSql还有剩余的内容，那么这些剩余的内容被存放到pZTail中，不包括终止符
                                       
             说明
             如果执行成功，则返回SQLITE_OK，否则返回一个错误码。推荐在现在任何的程序中都使用sqlite3_prepare_v2这个函数，sqlite3_prepare只是用于前向兼容。
             prepared语句可以被重置（调用sqlite3_reset函数），然后可以重新绑定参数之后重新执行。sqlite3_prepare_v2函数代价昂贵，所以通常尽可能的重用prepared语句。最后，这条prepared语句确实不在使用时，调用sqlite3_finalize函数释放所有的内部资源和sqlite3_stmt数据结构，有效删除prepared语句。
        */
    if (status == SQLITE_OK) { // 如果该表存在，会返回 SQLITE_ERROR
                                  
        // CREATE TABLE语句没有返回值，调用sqlite3_step函数执行这条语句
        // 通过调用sqlite3_step一次或多次来执行前面sqlite3_prepare创建的准备语句。这个语句执行到结果的第一行可用的位置，如需继续前进到结果的第二行的话，只需再次调用sqlite3_setp()
        // 对于不返回结果的语句（如：INSERT，UPDATE，或DELETE），sqlite3_step()只执行一次就返回
        NSInteger result = sqlite3_step(stmt);
        /*
                说明：sqlite3_exec 这个方法可以执行那些没有返回结果的操作，例如创建、插入、删除等。
                它属于早期遗留下来的便捷函数，这些函数存在很多缺点。当然他们依然存在就有理由——使用方便。它们的优点也仅仅是使用方便，而不是具有很好的性能。对于这些便捷函数，它们并没有什么特别之处，只是在这些函数内部调用sqlite3_prepare_xxx、sqlite3_step、sqlite3_finalize等API函数来完成一站式功能。在这样的函数内部往往存在很多额外的类型转换，所以这些函数很可能会比我们自己去调用sqlite3_prepare_xxx、sqlite3_step、sqlite3_finalize等API执行的更慢一些。
               */
        if (result != SQLITE_DONE) {
            NSLog(@"创建表失败，%@", NSStringFromSelector(_cmd));
        }
    } else {
        NSLog(@"该表已经存在或创建失败");
    }
                              
    sqlite3_finalize(stmt); // 摧毁stmt结构体，释放资源
}

// 插入数据
- (void)addDataWithDatabase:(sqlite3* )db {
    if (!db) {
        return;
    }
                     
    // 准备工作，解释见 createTableWithDatabase
    sqlite3_stmt* stmt = NULL;
    char* szSql = "INSERT INTO UserInfo(date, money) VALUES(?, ?)";
    /* SQL插入语句
        插入语句有几种形式，标准的为："insert into 数据表 (字段1,字段2,字段3, ...) valuess (值1,值2,值3, ...)"
        其中valuess (值1,值2,值3, ...) 中的值可以使用具体的数值，也可以使用如下的占位的通配符：
        ?
        ?NNN，NNN代表数字
        :VVV，VVV代表字符
        @VVV
        $VVV
        这里只介绍 ？，其它的自己查SQL文档。相同的通配符在同一个SQL声明中出现多次, 在这种情况下所有相同的通配符都会被替换成相应的值. 没有被绑定的通配符将自动取NULL值。使用sqlite3_bind_*()来给这些参数绑定值，用sqlite3_clear_bindings重设这些绑定。
        SQL语句字符串可以带?号，它是SQL语句中的不确定部分，需要对它另外赋值。事实上，SQLite的官方文档中已经明确指出，在很多时候sqlite3_prepare_v2函数的执行时间要多于sqlite3_step函数的执行时间，因此建议使用者要尽量避免重复调用sqlite3_prepare_v2函数。在我们的实现中，如果想避免此类开销，只需将待插入的数据以变量的形式绑定到SQL语句中，这样该SQL语句仅需调用sqlite3_prepare_v2函数编译一次即可，其后的操作只是替换不同的变量数值。
        sqlite3_reset() 函数可以用来重置一个SQL声明的状态（sqlite3_stmt），使得它回到未执行前的状态，并不改变绑定值。
       */
                     
    NSInteger status = sqlite3_prepare_v2(db, szSql, -1, &stmt, NULL);
    if (SQLITE_OK != status) {
        NSLog(@"插入数据失败，%@", NSStringFromSelector(_cmd));
        sqlite3_finalize(stmt);
        return;
    }
                     
    /* 绑定参数
         int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
         int sqlite3_bind_double(sqlite3_stmt*, int, double);
         int sqlite3_bind_int(sqlite3_stmt*, int, int);
         int sqlite3_bind_int64(sqlite3_stmt*, int, long long int);
         int sqlite3_bind_null(sqlite3_stmt*, int);
         int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
         int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
         int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
         以上是 sqlite3_bind 所包含的全部接口，它们是用来给SQL声明中的通配符赋值的. 没有绑定的通配符则被认为是空值。在准备SQL语句过程中，绑定是可选的。其中的第二个参数表示该绑定参数对应在SQL语句中？的索引值。第三个参数为替换的具体值。
        */
    NSDate* now = [NSDate date]; // 获得当前GMT时间
//    NSLog(@"date = %@", now);
    // 第一个绑定参数
    sqlite3_bind_text(stmt, 1, [[now description] UTF8String], -1, SQLITE_STATIC);
    /* 上面第四个参数为字符串的长度，该值为负将返回第一遇到'\0'的位置。
           第五个参数为一个函数指针，SQLITE3执行完操作后回调此函数，通常用于释放字符串占用的内存。此参数有两个常数，SQLITE_STATIC告诉sqlite3_bind_text函数字符串为常量，可以放心使用；而SQLITE_TRANSIENT会使得sqlite3_bind_text函数对字符串做一份拷贝。
       */
    // 第二个绑定参数
    double money = arc4random() % 1000 * 0.01; // 随机数值
    sqlite3_bind_double(stmt, 2, money);
                     
    // 如是，stmt完全准备好了，下面就是执行工作，我们依然使用sqlite3_step
    // 对于不返回结果的语句（如：INSERT，UPDATE，或DELETE），sqlite3_step()只执行一次就返回
    // 返回SQLITE_BUSY表示暂时无法执行操作，SQLITE_DONE表示操作执行完毕，SQLITE_ROW表示执行完毕并且有返回（执行select语句时）。当返回值为SQLITE_ROW时，我们需要对查询结果进行处理，SQLITE3提供sqlite3_column_*系列函数。
    NSInteger result = sqlite3_step(stmt);
    if (SQLITE_DONE == result) {
        NSLog(@"插入一条数据");
    } else {
        NSLog(@"插入数据失败，%@", NSStringFromSelector(_cmd));
    }
    sqlite3_finalize(stmt); // 摧毁stmt结构体，释放资源
}

// 删除最后一条数据
- (void)deleteFromDatabase:(sqlite3* )db {
    // 删除一般伴随查找，建议先看查找
    // 首先找到最后一条数据的主键(id)
    // SQLite中语法的不同，不能使用top 1，应使用LIMIT 0,1表示从第0条记录开始，往后读取1条记录
    char* selectSql = "SELECT * FROM UserInfo ORDER BY id DESC LIMIT 0,1";
    sqlite3_stmt* stmt = NULL;
    NSInteger Status = sqlite3_prepare_v2(db, selectSql, -1, &stmt, NULL);
               
    if (SQLITE_OK != Status) {
        sqlite3_finalize(stmt);
        return;
    }
               
    NSInteger nIndex = -1;
    while (SQLITE_ROW == sqlite3_step(stmt)) {
        nIndex = sqlite3_column_int(stmt, 0);
        NSLog(@"最后一行的id = %i", nIndex);
    }
               
    sqlite3_finalize(stmt); // 摧毁stmt结构体，释放资源
    if (nIndex > -1) {
        sqlite3_stmt* st = NULL;
        // 删除该条记录
        char* deleteSql = "DELETE FROM UserInfo WHERE id = ?";
        NSInteger result = sqlite3_prepare_v2(db, deleteSql, -1, &st, NULL);
                   
        if (SQLITE_OK == result) {
            // 多此一举，使用绑定
            sqlite3_bind_int(st, 1, nIndex);
                       
            while (SQLITE_ROW == sqlite3_step(st)) {
            }
        }
        sqlite3_finalize(st);
    }  
}

// 查询数值
- (void)selectWithDatabase:(sqlite3* )db {
    // SQL的筛选语句有非常多的形式，这里只介绍常见的两种：
    // "select * from 数据表 where 字段名=字段值 order by 字段名 [desc]"
    // "where"后面接筛选条件；"order by"表示排序方式（可选）；ACS表示按正序排序(从小到大排序，默认)，DESC 表示按倒序排序(从大到小排序)
    // "select * from 数据表 where 字段名 in ('值1','值2','值3')"
        
    // 查找所有"money"字段值大于2的所有数据
    char* szSql = "SELECT * FROM UserInfo WHERE money>2";
    sqlite3_stmt* stmt = NULL;
    sqlite3_prepare_v2(db, szSql, -1, &stmt, NULL);
    // 如果SQL语句中包含？号，需要使用sqlite3_bind_*()来给这些参数绑定值，这里没有。
        
    // 返回SQLITE_BUSY表示暂时无法执行操作，SQLITE_DONE表示操作执行完毕，SQLITE_ROW表示执行完毕并且有返回（执行select语句时）。当返回值为SQLITE_ROW时，我们需要对查询结果进行处理，SQLITE3提供sqlite3_column_*系列函数。
    /*
         sqlite3_column()
         这个过程从执行sqlite3_step()执行一个准备语句得到的结果集的当前行中返回一个列。每次sqlite3_step得到一个结果集的列停下后，这个过程就可以被多次调用去查询这个行的各列的值。对列操作是有多个函数，均以sqlite3_column为前缀
         const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
         int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
         int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
         double sqlite3_column_double(sqlite3_stmt*, int iCol);
         int sqlite3_column_int(sqlite3_stmt*, int iCol);
         sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
         const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
         const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
         int sqlite3_column_type(sqlite3_stmt*, int iCol);
         sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
         说明
         第一个参数为从sqlite3_prepare返回来的prepared statement对象的指针，第二参数指定这一行中的想要被返回的列的索引。最左边的一列的索引号是0，行的列数可以使用sqlite3_colum_count()获得。这些函数会根据情况去转换数值的类型。
         单步执行一次将返回一个结果（一条数据），一条数据可能包含多列（每列对应一个字段）。
        */
    while (SQLITE_ROW == sqlite3_step(stmt)) {
        // 第0列对应字段“id”
        NSInteger nId = sqlite3_column_int(stmt, 0);
            
        // 第1列对应字段“date”
        char* szDate = (char* )sqlite3_column_text(stmt, 1);
        NSString* strDate = [NSString stringWithUTF8String:szDate];
            
        // 第2列对应字段“money”
        double dMoney = sqlite3_column_double(stmt, 2);
        NSLog(@"id = %i, date = %@, money = %g", nId, strDate, dMoney);
    }
        
    sqlite3_finalize(stmt); // 摧毁stmt结构体，释放资源
}