Swift SQLite ORM 框架 - GRDB.swift 使用

简介

在 OC 中我们经常使用 FMDB 以及一些系列衍生的 Model 工具来操作数据库, 但在Swift中显得不那么友好.

1. Swift 与 OC 混编中 ClassName 变成项目名.类名, 这种情况下操作数据库会出现各种各样的问题.
2. 必须要继承 NSObject, 不适合 Struct 作为Model 的情况.

GRDB.swift 是 Swift 用来操作 SQLite 的工具. 通过 Protocol 的方式, 对 Struct Class 进行 CURD 操作, 同样也可以使用 DML 操作数据.

---

创建连接数据库

		var dbPool: DatabasePool?
		var dbQueue: DatabaseQueue?
        do {
            dbPool = try DatabasePool(path: path)
            dbQueue = try DatabaseQueue(path: path)
        }catch {
            
        }

DatabasePool 支持并发数据库操作 和 WAL模式.推荐使用.

---

执行SQL语句操作

创建表

		guard let cursor = dbPool else {
            return
        }
        
        do {
            
            try cursor.write({ (db) in
                try db.execute(sql: """
                        CREATE TABLE IF NOT EXISTS user (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        name TEXT,
                        age INTEGER,
                        address TEXT)
                        """
                )
            })
        }catch {
            debugPrint("create table user error")
        }

创建索引

			try cursor.write({ db in
                let res = try db.indexes(on: "user").filter({$0.name == "name_index"}).isEmpty
                if res {
                    try db.create(index: "name_index", on: "user", columns: ["name"])
                }
            })

删除索引

			try cursor.write({ db in
                try db.drop(index: "name_index")
            })

插入

try cursor.write({ (db) in
    let exists = try db.tableExists("user")
    if exists {
        try db.execute(
            sql: """
                INSERT INTO user(name, age, address)
                VALUES(?,?,?)
            """,
            arguments: ["kevin",12,"address"])
    }
})

statement 方式插入

		try cursor.write({ (db) in
                let exists = try db.tableExists("user")
                if exists {
                    let statement = try db.makeUpdateStatement(sql: """
                        INSERT INTO user(name, age, address)
                        VALUES(?,?,?)
                        """)
                    try statement.execute(arguments: ["kevin",12,"address"])
                    try statement.execute(arguments: ["kevin",12,"address"])
                    try statement.execute(arguments: ["kevin",12,"address"])
                }
            })

更新

			try cursor.write({ (db) in
                let exists = try db.tableExists("user")
                
                if exists {
                    try db.execute(sql: """
                        UPDATE user SET name = ?, age = ?, address = ?
                        WHERE name = ?
                        """, arguments: ["kevin5",12,"address", "kevin"])
                    
                    try db.execute(sql: """
                        UPDATE user SET name = :name, age = :age, address = :address
                        WHERE name = :name2
                        """, arguments: ["name": "kevin5", "age": 12, "address": "address", "name2": "kevin"])
                    
                }
                
            })

statement 方式

		try cursor.write({ (db) in
                let exists = try db.tableExists("user2")
                
                if exists {
                    let statement = try db.makeUpdateStatement(sql: """
                        UPDATE user SET name = ?, age = ?, address = ?
                        WHERE name = ?
                        """)
                    try statement.execute(arguments: ["kevin5",12,"address", "kevin"])
                }
                

查询

查询是需要使用class Row 进行查询, 也可以使用数据库存储数据类型进行查询.(DatabaseValueConvertible)

			try cursor.read({ db in
                let exists = try db.tableExists("user")
                
                if exists {
                    let statement = try db.makeSelectStatement(sql: """
                        select * from user
                        """)
                    let res = try Row.fetchAll(statement)
                    print(res)
                    
                    
                    let res2 = try Row.fetchAll(db, sql: "SELECT * FROM user")
                    if res2.isEmpty == false {
                        let r = res2[0]
                        print(r)
                        print(r["name"] as String)
                    }
                    
                    
                    let res3 = try String.fetchAll(db, sql: "select name from user")
                    print(res3)
                    
                    let res4 = try Row.fetchCursor(db, sql: "SELECT * FROM user")
                    while let r = try res4.next() {
                        debugPrint(r["id"] as Int)
                        debugPrint(r[Column("name")] as String)
                    }
                }
            })

直接过去某个数据

			let name: String? = try cursor.read { (db) -> String? in
                return try String.fetchOne(db, sql: "select name from user where id = 1")
            }

删除

		guard let cursor = dbPool else {
            return
        }
        
        do {
            try cursor.write({ (db) in
                let exists = try db.tableExists("user")
                if exists {
                    try db.execute(
                        sql: """
                        DELETE FROM user
                        WHERE id > 30
                        """)
                }
            })
        } catch let error {
            debugPrint(error)
        }

---

使用 API 操作

创建存储模型

基础部分, 建议使用 Codable 协议

struct UserEntity: Codable  {
    var id: Int?
    var name: String?
    var age: Int?
    var address: String?
}

使用 ColumnExpression 声明列名表示, 在 CURD 中直接使用 Columns.id/.name/.age/.address 进行操作数据

extension UserEntity {    
    enum Columns: String, ColumnExpression {
        case id, name, age, address
    }
}

使用 TableRecord 协议, 告知操作数据表

extension UserEntity: TableRecord {
//  数据表名字 如果不自定义 将使用默认的表名, 默认为小写开头驼峰命名 例如 UserEntity -> userEntity
    static var databaseTableName: String {
        return "user"
    }
    // 查询字段, 默认使用 *  ,  复制后将使用 id, name, age, address 
    // static var databaseSelection: [SQLSelectable] = [Column("id"), Column("name"), Column("age"), Column("address")]
    // 同上 声明列名表示的方式
    static var databaseSelection: [SQLSelectable] = [Columns.id, Columns.name, Columns.age, Columns.address]
}

FetchableRecord 进行查询操作, 使用了Codable可以不实现init(row: Row)

// fetch
extension UserEntity: FetchableRecord {
    // 未使用 Codable = Decodable & Encodable 协议的 需要实现init(row: Row)
    init(row: Row) {
        id = row["id"]
        name = row["name"]
        age = row[Columns.age]
        address = row[Columns.address]
    }

}

使用PersistableRecord / MutablePersistableRecord插入更新保存数据, 存储模型是Class使用PersistableRecord, 存储模型是struct使用MutablePersistableRecord. 两者区别在于 MutablePersistableRecord save() insert() 是 mutating.

// insert
extension UserEntity: MutablePersistableRecord {
    func encode(to container: inout PersistenceContainer) {
        container["id"] = id
        container[Columns.name] = name
        container[Columns.address] = address
        container[Columns.age] = age
    }
    mutating func didInsert(with rowID: Int64, for column: String?) {
        id = Int(rowID)
    }
    
}

创建表

		try db.create(table: databaseTableName, temporary: false, ifNotExists: true, body: { (t) in
            t.autoIncrementedPrimaryKey("id")
            t.column("name", .text)
            t.column("age", .integer)
            t.column("address", .text)
        })

插入

			try cursor.write({ (db) -> Void in
                var user = UserEntity(id: nil, name: "kk", age: 22, address: "kk.ads")
                try user.insert(db)
            })

查询

			guard let user = try cursor.read({ (db) -> UserEntity? in
                return try UserEntity.fetchOne(db, sql: "select * from user where id == 20")
            }) else {
                return
            }

			guard let user2 = try cursor.read({ (db) -> UserEntity? in
                //                return try UserEntity.filter(key: 30).fetchOne(db)
                return try UserEntity.fetchOne(db, key: 30)
                //                return try UserEntity.fetchOne(db)
            }) else {
                return
            }

更新

			guard var kk = try cursor.read({ (db) -> UserEntity? in
                return try UserEntity.filter(sql: "name == 'kk'").fetchOne(db)
            }) else {
                debugPrint("fetch kk error")
                return
            }
            try cursor.write({ (db) -> Void in
                try kk.updateChanges(db, with: { (u) in
                    u.address = "k.kkkkkkk"
                })
//                try kk.updateChanges(db) {
//                    $0.address = "k.kkkkkkk"
//                }
            })

			var newkk = kk
            newkk.address = "k.kk.kk"

            try cursor.write({ (db) -> Void in
                try newkk.updateChanges(db, from: kk)
            })

删除

            guard let kk = try cursor.read({ (db) -> UserEntity? in
                return try UserEntity.filter(sql: "name == 'kk'").fetchOne(db)
            }) else {
                debugPrint("fetch kk error")
                return
            }
            try cursor.write({ (db) -> Void in
                try kk.delete(db)
            })
        }catch {
            debugPrint("delete error")
        }

迁移

GRDB通过注册标识(可写成版本号)进行数据库迁移操作.

创建迁移对象

        var migrator = DatabaseMigrator()

查询应用迁移

		do {
            let res = try migrator.appliedMigrations(in: dbPool!)
            print(res.contains("v1"))
            print(res.contains("v2"))
        } catch let error {
            debugPrint(error)
        }

注册

		migrator.registerMigration("v1") { (db) in
            try db.create(table: "user_entity", body: { (t) in
                t.autoIncrementedPrimaryKey("id")
                t.column("name", .text)
                t.column("address", .text)
                t.column("age", .integer)
            })
        }
        
        migrator.registerMigration("v2") { (db) in
            try db.alter(table: "user_entity", body: { (t) in
                t.add(column: "time", .date).notNull().defaults(to: Date())
            })
        }

执行

		do {
            try migrator.migrate(dbPool!)
//            try migrator.migrate(dbPool!, upTo: "v2")
        }catch {
            print("error migrator")
        }

---

仅为个人学习记录, 详细操作, 请移步官方文档.