Sqlite

Contents

簡介
核心物件
簡單範例
查詢 Database 資訊
使用在 Objective-C 上
- Core Data
FMDB (輕包裝的sqlite objc framework)
- 專案位置
- 簡易教學

簡介

sqlite3 C/C++ 使用筆記。

核心物件

Database Connection

類似 file handle 的物件，當 sqlite database file 開啟後透過這個指標來進行操作。可透過 sqlite3_open() 相關 functions 建立 database connection，其中filename 需要使用 utf-8 encoding string 請自行使用 libiconv (或任何具備轉換utf8的library) 提供的 function 轉換。

/*
int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
int sqlite3_open16(
  const void *filename,   /* Database filename (UTF-16) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
int sqlite3_open_v2(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb,         /* OUT: SQLite db handle */
  int flags,              /* Flags */
  const char *zVfs        /* Name of VFS module to use */
);
*/
sqlite3 *db;
int result = sqlite3_open (filename,&db);
if (result != SQLITE_OK)
{
  // 開啟資料庫檔案錯誤
}

Prepared statement

編譯過的 SQL 命令。用 sqlite3_prepare() 相關的 functions 建立，由於不是所有

/*
int sqlite3_prepare(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
*/

const char *sqlQuery = "CREATE TABLE maintable (name,text);";
sqlite3_stmt *prepare_statement;
/*nByte=-1 代表 zSql 為 null terminator string*/
int result = sqlite3_prepare_v2(db, sqlQuery, -1, &statement, NULL);

if (result != SQLITE_OK)
{
  // 建立 prepared statement 發生錯誤
}

簡單範例

// gcc testsql.c -o test -lsqlite3
int main (int argc, char const *argv[])
{
    sqlite3 *db;
    int result = sqlite3_open("test.db",&db);
    if (result != SQLITE_OK) {
        printf("Error open database \n");
        return 0;
    }
    char query[] = "SELECT * FROM things;";
    sqlite3_stmt *prepare_statement;
    result = sqlite3_prepare_v2(db,query,-1,&prepare_statement,NULL);
    while(sqlite3_step(prepare_statement) == SQLITE_ROW) {
        int num_column = sqlite3_column_count(prepare_statement);
        int i;
        for(i = 0; i < num_column; ++i)
        {
            switch(sqlite3_column_type(prepare_statement,i)) {
                case SQLITE_TEXT:
                printf(sqlite3_column_name(prepare_statement,i));
                printf(" = ");
                printf(sqlite3_column_text(prepare_statement,i));
                printf("\n");
                break;
                case SQLITE_INTEGER:
                printf(sqlite3_column_text(prepare_statement,i));
                printf("\n");
                break;
                case SQLITE_NULL:
                break;
                default:
                break;
            }
        }
    }
    sqlite3_finalize(prepare_statement);
    sqlite3_close(db);
    return 0;
}

查詢 Database 資訊

在sqlite命令模式下可以使用 .schema 或是 .tables 來查詢資料庫的資訊，在C/C++ 中自然無法直接執行這些指令，如果想得到類似的資訊就必須查詢 SQLITE_MASTER 來取得資訊。

SQLITE_MASTER 的 schema 如下：

CREATE TABLE sqlite_master (
  type TEXT,
  name TEXT,
  tbl_name TEXT,
  rootpage INTEGER,
  sql TEXT
);

sqlite> select * from sqlite_master;
type        name         tbl_name     rootpage    sql
----------  -----------  -----------  ----------  -----------------------------------------------------------
table       Events_Tags  Events_Tags  5           CREATE TABLE Events_Tags (event_id NUMERIC, tag_id NUMERIC)
table       Tags         Tags         4           CREATE TABLE Tags (tag_id INTEGER PRIMARY KEY, text TEXT)
table       Times        Times        2           CREATE TABLE Times (time_id INTEGER PRIMARY KEY, date NUMER
table       Events_Time  Events_Time  6           CREATE TABLE Events_Times (event_id NUMERIC, time_id NUMERI
table       Events       Events       3           CREATE TABLE Events (note TEXT, event_id INTEGER PRIMARY KE

除了 sqlite_master 外還有一個 sqlite_temp_master 來維護 temporary tables.

使用在 Objective-C 上

其實看過前面介紹 C/C++ sqlite interface 的說明後，應該就可以在 mac 和 iPhone 上面開發應用程式了， Objective-C 不過是 Apple 增加的 C 語言擴充，百分百完全相容於 C，因此可以直接使用。

sqlite 基本上 mac 或是 iPhone/iPad/iPod Touch 都有內建，寫程式時不太需要考慮直接在把libqlite3.0.dylib 或是 libsqlite3.dylib 加入專案中一併連結即可，這兩個函式庫也可以直接在 Existing Framework 中找到。

說到這其實 Apple 還滿喜歡用一些良好的 Open source ，對於習慣 linux 上面開發的人應該倍感親切才是。

Core Data

Apple 有發佈 Core Data Framework 來包裝使用 sqlite,xml或是自定data，如果沒有特別需求，乖乖用 Core Data 其實是一個十分不錯的選擇。

如果嫌 Core Data 太肥太慢，學習又要花時間，又不想直接呼叫 sqlite api，其實有不少熱心人士包裝的 sqlite objective-c classes 可以使用 (不稱呼為 framework 因為這些輕度包裝稱其為 framework 厚重感太過，也有點名不符實)，下面介紹幾個比較知名的。

FMDB (輕包裝的sqlite objc framework)

十分優小巧好用的 sqlite 包裝， classes 才少少的三個，如果你已經大致了解 sqlite 那使用這個絕對是得心應手。

專案位置

http://code.google.com/p/flycode/source/browse/#svn/trunk/fmdb

安裝方式:

svn checkout http://flycode.googlecode.com/svn/trunk/fmdb fmdb

使用方法:: 直接把3個classes丟進專案即可。

簡易教學

這個 Project 真的是簡單不囉說，和sqlite一樣輕巧好用簡單，簡單到沒有說明文件沒有 API 檔案，作者也叫你自己看原始碼，當然真的很簡單才敢這樣（或是很懶..）

在解釋細節前要先介紹兩個 Class 一個是 FMDatabase 包裝所有 sqlite 相關的操作，一個是 FMResultSet 包裝查詢回來的結果。

接下來直接看範例就很容易理解運作方式了。

讀取/建立/關閉database以及執行沒有傳回值的SQL命令

FMDatabase databaseWithPath: 傳回來的物件和 stringWithString: arrayWithObjects: 這些便利工廠函式 (factroy method) 一樣，傳回來的物件已經有對他送出 autorelease message，所以不需要明確的呼叫 release 釋放物件。

setShouldCacheStatements: 會把編譯過的 sql 敘述存起來供以後使用，對於效率有所幫助。

executeUpdate: 用來執行沒有返回資料的sql命令，如 create table， insert into， alter table 等等， executeUpdate: 支援了類似 stringWithFormate: 的語法，相當方便。

beginTransaction 和 commit 是一對的，不要單獨使用。

最後我們不會忘記在 sqlite C/C++ API 中開啟資料庫用完後是要關閉釋放出資源的， ( sqlite_close(db) )，在 FMDB 中直接傳送 close message 給 FMDatabase 物件。

FMDatabase* db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];
// open 和 close 是成對的 method
if (![db open]) {
  //Error handling -- can't open database
}

// 設定儲存 prepared statements
[db setShouldCacheStatements:YES];

// executeUpdate 用來執行
[db executeUpdate:@"create table test (a text, b text, c integer, d double, e double)"];

// 會執行 BEGIN EXCLUSIVE TRANSACTION 命令鎖住資料庫。
[db beginTransaction];
int i = 0;
while (i++ < 20) {
  // 類似 stringWithFormat: 的語法！
  [db executeUpdate:@"insert into test (a, b, c, d, e) values (?, ?, ?, ?, ?)" ,
         @"hi'", // 不需要把'寫成\'或是''
         [NSString stringWithFormat:@"number %d", i],
         [NSNumber numberWithInt:i],
         [NSDate date],
         [NSNumber numberWithFloat:2.2f]];
 }
 [db commit];
 [db close];

處理查詢結果

接者介紹如果有查詢結果的處理方式： executeQuery: 會傳回 FMResultSet 物件， FMResultSet 可以非常方便的取得傳回值，使用 next 取得每個完整的row並透過一組 xxxxForColumn: 的 method 存取 column 資料。

columnNameForIndex: 可以快速查詢 column 的名稱。

熟悉 sqlite C/C++ API 的話會知道，執行 sql 命令的 function:

int sqlite3_step(sqlite3_stmt*);

是依據一個 prepared statement 來進行處理，在 FMDB 包裝好的 FMResultSet 物件也有保存對應的 statement 物件，大家都知道物件沒用了就要釋放， FMResultSet 在執行 dealloc 會釋放保存的 statement 如果想明確釋放(比如想重複使用FMResultSet物件於不同的查詢) 需執行:

[ResultSet close];

下面是較為完整的範例程式片段，繼續承接上面的範例。

FMResultSet *rs = [db executeQuery:@"select rowid,* from test where a = ?", @"hi'"];
while ([rs next]) {

    NSLog(@"%d %@ %@ %@ %@ %f %f",
          [rs intForColumn:@"c"],
          [rs stringForColumn:@"b"],
          [rs stringForColumn:@"a"],
          [rs stringForColumn:@"rowid"],
          [rs dateForColumn:@"d"],
          [rs doubleForColumn:@"d"],
          [rs doubleForColumn:@"e"]);


    if (!([[rs columnNameForIndex:0] isEqualToString:@"rowid"] &&
          [[rs columnNameForIndex:1] isEqualToString:@"a"])
          ) {
        NSLog(@"WHOA THERE BUDDY, columnNameForIndex ISN'T WORKING!");
        return 7;
    }
}
[rs close];

Blob 處理範例

// blob support.
// blob 處理的範例，這邊使用NSData包裝資料。
[db executeUpdate:@"create table blobTable (a text, b blob)"];

// let's read in an image from safari's app bundle.
NSData *safariCompass = [NSData dataWithContentsOfFile:@"/Applications/Safari.app/Contents/Resources/compass.icns"];
if (safariCompass) {
    [db executeUpdate:@"insert into blobTable (a, b) values (?,?)", @"safari's compass", safariCompass];

    rs = [db executeQuery:@"select b from blobTable where a = ?", @"safari's compass"];
    if ([rs next]) {
        safariCompass = [rs dataForColumn:@"b"];
        [safariCompass writeToFile:@"/tmp/compass.icns" atomically:NO];

        // let's look at our fancy image that we just wrote out..
        system("/usr/bin/open /tmp/compass.icns");

        // ye shall read the header for this function, or suffer the consequences.
        safariCompass = [rs dataNoCopyForColumn:@"b"];
        [safariCompass writeToFile:@"/tmp/compass_data_no_copy.icns" atomically:NO];
        system("/usr/bin/open /tmp/compass_data_no_copy.icns");
    }
    else {
        NSLog(@"Could not select image.");
    }

    [rs close];

}
else {
    NSLog(@"Can't find compass image..");
}

錯誤處理

FMDB 的錯誤處理機制也相當容易， FMDatabase 提供3個 methods 供我們存取錯誤狀態:

- (BOOL)hadError; - (int)lastErrorCode; - (NSString*)lastErrorMessage;

概念類似 Win32 API 的 GetLastError() ，系統幫你保存最後一個錯誤碼，當要檢查有無發生錯的時候直接透過 hadError 判斷，在由 lastErrorCode 和 lastErrorMessage 取得相關資訊。

if ([db hadError]) {
  NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
}

快速查詢第一個

FMDB 依照不同的資料型態還提供一組便利 methods xxxForQuery: ，其中xxx表示資料型態如 intForQuery: 就會傳回查詢後第一個結果並轉成 int type (row at index:0)

[db executeUpdate:@"create table t1 (a integer)"];
[db executeUpdate:@"insert into t1 values (?)", [NSNumber numberWithInt:5]];
int a = [db intForQuery:@"select a from t1 where a = ?", [NSNumber numberWithInt:5]];
if (a != 5) {
  NSLog(@"intForQuery didn't work (a != 5)");
}

Austin's study note

版權聲明

Sqlite in iPhone

Sqlite

簡介

核心物件

簡單範例

查詢 Database 資訊

使用在 Objective-C 上

Core Data

FMDB (輕包裝的sqlite objc framework)

專案位置

簡易教學

讀取/建立/關閉database以及執行沒有傳回值的SQL命令

處理查詢結果

Blob 處理範例

錯誤處理

快速查詢第一個

沒有留言:

*最新文章

標籤

網誌存檔

關於我自己

blog文章原始檔案

Analyzer

版權聲明

Sqlite

沒有留言:

*最新文章

標籤

訂閱

網誌存檔

關於我自己

blog文章原始檔案

Analyzer