Using SQLite

iPhone 上的應用程式可以使用 Plist 或 XML 檔案去記錄資料，如果資料類型比較簡單，資料和資料之間沒有關聯性，資料數量不多，使用 Plist 或 XML 檔案已經足夠應付。如果需要記錄大量並擁複雜關聯性的資料時，還是建議使用資料庫去去儲存資料好。

iPhone 的應用程式可以使用 SQLite 去作為資料庫系統，SQLite 是超輕量版的一款資料庫系統，完全不像常用的資料庫系統，例如: MySQL，PostgreSQL，Oracle 等等。SQLite 不用安裝，整個資料庫就是以一個檔案的型式存在，存取資料靠 iPhone SDK 提供的 Library 就可以了。由於iPhone SDK 已經內置了 SQLite 的 Library 檔案，所以不用擔心兼容性的問題，就算是遲些出到 iPhone 5, 6, 7 也沒問題的。操作方面和常用的資料庫系統差不多，一樣可以使用 SQL 去操作。

在寫程式之前要先製作好資料庫才可以，我建議使用 FireFox 的插件 - SQLite Manager。
下載網址: https://addons.mozilla.org/en-US/firefox/addon/5817/

SQLite Manager 在 Mac OSX， Linux 或 Windows 環境也一樣可以用到，只要裝到 FireFox 就可以用到。

怎麼使用 SQLite Manager 就不多講了，以下是我的資訊庫的 DDL 加上 DML:

DROP TABLE IF EXISTS "customer";
CREATE TABLE "customer" ("pid" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "first_name" VARCHAR, "last_name" VARCHAR);
INSERT INTO "customer" VALUES(1,'Lawrence','Cheung');
INSERT INTO "customer" VALUES(2,'Tom','Chan');
INSERT INTO "customer" VALUES(3,'Ken','Choi');
DROP TABLE IF EXISTS "sqlite_sequence";
CREATE TABLE sqlite_sequence(name,seq);
INSERT INTO "sqlite_sequence" VALUES('customer',3);

你要先在 Project 內增加 SQLIte 的 Library 才使用到 SQLIte 資料庫，將 libsqlite3.0.dylib 加入到 Framework 內。

為了不經常打開資料庫的連接，我使用一個 Singleton 類別去保持資料庫的連接。這個類別的程式碼:
DBHelper.h:

#import <sqlite3.h>

@interface DBHelper : NSObject {
 sqlite3 *database;
}

@property(readonly, nonatomic) sqlite3 *database;

+ (DBHelper *) newInstance;
- (void) openDatabase;
- (void) closeDatabase;
- (NSString *) getDatabaseFullPath;
- (void) copyDatabaseIfNeeded;
- (sqlite3_stmt *) executeQuery:(NSString *) query;

@end

DBHelper.m:

#import "DBHelper.h"

@implementation DBHelper

static DBHelper *instance = nil;

NSString *DB_NAME = @"sample";
NSString *DB_EXT = @".sqlite";

@synthesize database;

+ (DBHelper *) newInstance{
   @synchronized(self) {
      if (instance == nil){
         instance = [[DBHelper alloc]init];
         [instance openDatabase];
      }
   }
   return instance;
}

+ (id)allocWithZone:(NSZone *)zone {
   @synchronized(self) {
      if (instance == nil) {
         instance = [super allocWithZone:zone];
         return instance;        
      }
   }
   return nil;
}

- (id)copyWithZone:(NSZone *)zone
{
    return self;
}

- (id)retain {
    return self;
}

- (unsigned)retainCount {
    return UINT_MAX;
}

- (void)release {
    //do nothing
}

- (id)autorelease {
    return self;
}

- (void) openDatabase{
    if (!database){
      [self copyDatabaseIfNeeded];
      int result = sqlite3_open([[self getDatabaseFullPath] UTF8String], &database);
      if (result != SQLITE_OK){
         NSAssert(0, @"Failed to open database");
      }
   }
}

- (void) closeDatabase{
    if (database){
        sqlite3_close(database);
    }
}

- (void) copyDatabaseIfNeeded{
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSString *dbPath = [self getDatabaseFullPath];
    BOOL success = [fileManager fileExistsAtPath:dbPath]; 
 
    if(!success) {
  
        NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:[NSString stringWithFormat:@"%@%@", DB_NAME, DB_EXT]];
        success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];
        NSLog(@"Database file copied from bundle to %@", dbPath);
  
        if (!success){ 
            NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
        }
        
    } else {
        
        NSLog(@"Database file found at path %@", dbPath);
  
    }
}

- (NSString *) getDatabaseFullPath{
   NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
   NSString *documentsDirectory = [paths objectAtIndex:0];
   NSString *path = [documentsDirectory stringByAppendingPathComponent:[NSString stringWithFormat:@"%@%@", DB_NAME, DB_EXT]];
   return path;
}

- (sqlite3_stmt *) executeQuery:(NSString *) query{
   sqlite3_stmt *statement;
   sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil);
   return statement;
}

@end

我在這個類別初始化同時會打開資料庫的連接，要在 iPhone 開啟 SQLite 資料庫前，必須要將資料庫複製到 Documents 目錄內，而其他類別要用 SQL 向資料庫查詢資料只要執行executeQuery 就可以了。

向資料庫查詢資料:

DBHelper *dbHelper = [DBHelper newInstance];
    
NSString *sql = @"SELECT customer.pid, customer.first_name, customer.last_name FROM customer";
sqlite3_stmt *statement = [dbHelper executeQuery:sql];
    
while(sqlite3_step(statement) == SQLITE_ROW){
    int pid = sqlite3_column_int(statement, 0);
    NSString *firstName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
    NSString *lastName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
    NSLog(@"pid: %i, first name: %@, last name: %@", pid, firstName, lastName);
}

執行時在 Console 內會打印出以下文字:

[Session started at 2010-07-20 11:31:39 +0800.]
2010-07-20 11:31:41.545 sqlite[39544:207] Database file found at path /Users/Lawrence/Library/Application Support/iPhone Simulator/4.0/Applications/999BFA7F-6D9B-490A-B542-CDCA79B1590E/Documents/sample.sqlite
2010-07-20 11:31:41.547 sqlite[39544:207] pid: 1, first name: Lawrence, last name: Cheung
2010-07-20 11:31:41.548 sqlite[39544:207] pid: 2, first name: Tom, last name: Chan
2010-07-20 11:31:41.549 sqlite[39544:207] pid: 3, first name: Ken, last name: Choi

應用程式完結時記得要將資料庫連接關閉:

[[DBHelper newInstance] closeDatabase];

文章來源 ：
http://pro.ctlok.com/p/about.html