The Util Designer
SwiftUI 學習使用GRDB來進行SQLite的操作
xcode 13.4.1, swift 5.5, iOS 15.4
2022-08-31
App大部份都需要儲存一些資料，比如遊戲的分數等等，雖然Apple提供了Core Data來做類型SQL Database，但不太好用，如果可以使用原先的SQL來進行操作，就不需要再學習另一套Core Data的用法。
1. 在XCode按 File->Add Packages...
2. 在右上角輸入 https://github.com/groue/GRDB.swift.git，根據 https://github.com/groue/GRDB.swift中最高可以使用的版本是v.5.26.0，在Dependency Rule選擇Up to Next Major Version，並填入5.26.0
3. 按 Add Package
4. 選擇GRDB
5. 完成後，在Project Structure中的Package Dependencies看到GRDB已加入：
6. 在Target->General->Frameworks, Libraries, and Embeded Content也看到GRDB：
7. 建立一個playground，並輸入以下代碼：
import Foundation
import GRDB

func connectToDB(_ dbname : String) -> DatabaseQueue? {
    let path = NSSearchPathForDirectoriesInDomains(
        .documentDirectory, .userDomainMask, true
    ).first!
    do {
        let databaseQueue = try DatabaseQueue(path: "\(path)/\(dbname).sqlite")
        return databaseQueue
    } catch {
        print("Error ： \(error)")
        return nil
    }
}


let dbname = "testGRDB"
if let databaseQueue = connectToDB(dbname) {
    print(databaseQueue)
}
8. 運行後應看到在相應的目錄中多了一個testGRDB.sqlite檔案。
9. 增加一個createPlayerTable方法，主要功能是在testGRDB.sqlite的數據庫中建立一個player的table，這個table有三個欄位，分別為id, name和score，並執行一次insert和fetch，運行完應該會看到console有一條記錄被打印出來：
import Foundation
import GRDB

func connectToDB(_ dbname : String) -> DatabaseQueue? {
    let path = NSSearchPathForDirectoriesInDomains(
        .documentDirectory, .userDomainMask, true
    ).first!
    do {
        let databaseQueue = try DatabaseQueue(path: "\(path)/\(dbname).sqlite")
        return databaseQueue
    } catch {
        print("Error ： \(error)")
        return nil
    }
}

func createPlayerTable(_ databseQueue : DatabaseQueue) {
    do {
        try databseQueue.write { db in
            try db.create(table: "player") { t in
                t.autoIncrementedPrimaryKey("id")
                t.column("name", .text).notNull()
                t.column("score", .integer).notNull()
            }
        }
    } catch {
        print("Error ： \(error)")
    }
}

struct Player: Codable, FetchableRecord, PersistableRecord {
    var id: Int64
    var name: String
    var score: Int
}

func insertPlayer( _ databseQueue : DatabaseQueue, _ record : Player) {
    do {
        try databseQueue.write { db in
            try record.insert(db)
        }
    } catch {
        print("Error ： \(error)")
    }
}



let dbname = "testGRDB"
if let databaseQueue = connectToDB(dbname) {
    createPlayerTable(databaseQueue)
    let player = Player(id: 1, name: "JOHN", score: 100)
    insertPlayer(databaseQueue, player)
    
    let players: [Player] = try databaseQueue.read { db in
        try Player.fetchAll(db)
    }
    players.forEach { player in
        print(player)
    }
}
10. 最後再增加一個deletePlayer方法，來刪除記錄，實作看下面：
import Foundation
import GRDB

func connectToDB(_ dbname : String) -> DatabaseQueue? {
    let path = NSSearchPathForDirectoriesInDomains(
        .documentDirectory, .userDomainMask, true
    ).first!
    do {
        let databaseQueue = try DatabaseQueue(path: "\(path)/\(dbname).sqlite")
        return databaseQueue
    } catch {
        print("Error ： \(error)")
        return nil
    }
}

func createPlayerTable(_ databseQueue : DatabaseQueue) {
    do {
        try databseQueue.write { db in
            try db.create(table: "player") { t in
                t.autoIncrementedPrimaryKey("id")
                t.column("name", .text).notNull()
                t.column("score", .integer).notNull()
            }
        }
    } catch {
        print("Error ： \(error)")
    }
}

struct Player: Codable, FetchableRecord, PersistableRecord {
    var id: Int64
    var name: String
    var score: Int
}

func insertPlayer( _ databseQueue : DatabaseQueue, _ record : Player) {
    do {
        try databseQueue.write { db in
            try record.insert(db)
        }
    } catch {
        print("Error ： \(error)")
    }
}

func deletePlayer( _ databseQueue : DatabaseQueue, _ player : Player) {
    do {
        let _  = try databseQueue.write { db in
            try player.delete(db)
        }
    } catch {
        print("Error ： \(error)")
    }
}


let dbname = "testGRDB"
if let databaseQueue = connectToDB(dbname) {
    createPlayerTable(databaseQueue)
    let player = Player(id: 1, name: "JOHN", score: 100)
    insertPlayer(databaseQueue, player)
    
    let players: [Player] = try databaseQueue.read { db in
        try Player.fetchAll(db)
    }
    players.forEach { player in
        print(player)
    }
    
    deletePlayer(databaseQueue, players[0])
    
    print("after delete.")
    let newPlayers: [Player] = try databaseQueue.read { db in
        try Player.fetchAll(db)
    }
    print("There are \(newPlayers.count) players in the db.")
}
11. 以上我們簡單了演示了如何使用GRDB來進行數據庫的Create, Read, Update 和 Delete。