//获取doc路径 let path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first! //如果不存在的话，创建一个名为db.sqlite3的数据库，并且连接数据库 let db = try Connection("\(path)/db.sqlite3")
let id = Expression<Int64>("id") let email = Expression<String>("email") let balance = Expression<Double>("balance") let verified = Expression<Bool>("verified")
以上表达式分别表示表的字段，第一个字段名是id，类型是Int64,第二个字段名是email,类型是String…以此类推，以上创建的是不为空的，要创建可以为空的字段，可以这样创建：let name = Expression<String?>("name")
3.4 创建表
声明一个Table常量
let users = Table("users")
使用上文表达式创建的字段，如果表不存在，就创建表
try db.run(users.create(ifNotExists: true) { t in // CREATE TABLE "users" ( t.column(id, primaryKey: true) // "id" INTEGER PRIMARY KEY NOT NULL, t.column(email, unique: true) // "email" TEXT UNIQUE NOT NULL, t.column(name) // "name" TEXT })
我们也可以对字段进行约束。设置字段为主键/自增/唯一/检查/设置默认值/校对/设置外键等等
t.column(id, primaryKey: true) // "id" INTEGER PRIMARY KEY NOT NULL
for user in try db.prepare(users) { do { print("name: \(try user.get(name))") } catch { // handle } }
我们可以通过对数据库连接上的pluck方法的查询来获取第一行。
if let user = try db.pluck(users) { /* ... */ } // Row // SELECT * FROM "users" LIMIT 1
将查询结果封装成一个数组也很简单，我们可以这样处理：
let all = Array(try db.prepare(users)) // SELECT * FROM "users"
上面的是指一些很简单的查询操作，实际操作中这些肯定满足不了我们的需求的，所以要用到复合查询操作。
let query = users.select(email) // SELECT "email" FROM "users" .filter(name != nil) // WHERE "name" IS NOT NULL .order(email.desc, name) // ORDER BY "email" DESC, "name" .limit(5, offset: 1) // LIMIT 5 OFFSET 1
选择指定的字段
for user in try db.prepare(users.select(id, email)) { print("id: \(user[id]), email: \(user[email])") // id: 1, email: alice@mac.com } // SELECT "id", "email" FROM "users"
数据表内联接，比如将posts表的user_id和users表的id作为连接条件，内联两个表
users.join(posts, on: user_id == users[id]) // SELECT * FROM "users" INNER JOIN "posts" ON ("user_id" = "users"."id")
以上是表之间的连接，有时候涉及到表内自联，这时候就要用到数据表别名alias
let managers = users.alias("managers") let query = users.join(managers, on: managers[id] == users[managerId]) // SELECT * FROM "users" // INNER JOIN ("users") AS "managers" ON ("managers"."id" = "users"."manager_id")
选择的条件：
users.filter(id == 1) // SELECT * FROM "users" WHERE ("id" = 1)
users.filter([1, 2, 3, 4, 5].contains(id)) // SELECT * FROM "users" WHERE ("id" IN (1, 2, 3, 4, 5))
users.filter(email.like("%@mac.com")) // SELECT * FROM "users" WHERE ("email" LIKE '%@mac.com')
users.filter(verified && name.lowercaseString == "alice") // SELECT * FROM "users" WHERE ("verified" AND (lower("name") == 'alice'))
users.filter(verified || balance >= 10_000) // SELECT * FROM "users" WHERE ("verified" OR ("balance" >= 10000.0))
排序操作（ASC：升序，DESC：降序），根据email和name字段排序
users.order(email, name) // SELECT * FROM "users" ORDER BY "email", "name"
email字段降序，name字段升序
users.order(email.desc, name.asc) // SELECT * FROM "users" ORDER BY "email" DESC, "name" ASC
let count = try db.scalar(users.count) // SELECT count(*) FROM "users" let count = try db.scalar(users.filter(name != nil).count) // SELECT count(*) FROM "users" WHERE "name" IS NOT NULL let count = try db.scalar(users.select(name.count)) // -> Int // SELECT count("name") FROM "users" let max = try db.scalar(users.select(id.max)) // -> Int64? // SELECT max("id") FROM "users" let min = try db.scalar(users.select(id.min)) // -> Int64? // SELECT min("id") FROM "users" let average = try db.scalar(users.select(balance.average)) // -> Double? // SELECT avg("balance") FROM "users" let sum = try db.scalar(users.select(balance.sum)) // -> Double? // SELECT sum("balance") FROM "users" let total = try db.scalar(users.select(balance.total)) // -> Double // SELECT total("balance") FROM "users"
3.6 错误处理
你可以匹配上选择性地抓住SQLite的错误错误。例如，专门处理约束错误
do { try db.run(users.insert(email <- "alice@mac.com")) try db.run(users.insert(email <- "alice@mac.com")) } catch let Result.error(message, code, statement) where code == SQLITE_CONSTRAINT { print("constraint failed: \(message), in \(statement)") } catch let error { print("insertion failed: \(error)") }
