forked from groue/GRDB.swift
/
Contents.swift
469 lines (413 loc) · 16.6 KB
/
Contents.swift
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
//: # Customized Decoding of Database Rows
//:
//: To run this playground:
//:
//: - Open GRDB.xcworkspace
//: - Select the GRDBOSX scheme: menu Product > Scheme > GRDBOSX
//: - Build: menu Product > Build
//: - Select the playground in the Playgrounds Group
//: - Run the playground
import GRDB
//: This playground demonstrates how one can implement **customized decoding of
//: database rows**.
//:
//: Customized row decoding allows you to go beyong the built-in support for
//: requests of raw rows, values, or types that adopt the
//: FetchableRecord protocol.
//:
//: For example:
//:
//: - Your application needs polymorphic row decoding: it decodes some class or
//: another, depending on the values contained in a database row.
//:
//: - Your application needs to decode rows with a context: each decoded value
//: should be initialized with some extra value that does not come from
//: the database.
//:
//: - Your application needs a record type that supports untrusted databases,
//: and may fail at decoding database rows (throw an error when a row contains
//: invalid values).
//:
//: None of those use cases can be handled by the built-in `FetchableRecord`
//: protocol and its `init(row:)` initializer. They need *customized
//: row decoding*.
//:
//: ## An Example: Polymorphic Decoding
//:
//: In this playground, we will provide some sample code that performs
//: polymorphic row decoding. Given a base class, `Base`, and two subclasses
//: `Foo` and `Bar`, we'd like to fetch Foo or Bar instances, depending on the
//: value found in the `type` column of the `base` database table.
//:
//: From this sample code, we hope that you will be able to derive your own
//: customized decoding.
//:
//: Everything starts from our class hierarchy:
class Base {
var description: String { "Base" }
init() { }
}
class Foo: Base {
var name: String
override var description: String { "Foo: \(name)" }
init(name: String) {
self.name = name
super.init()
}
}
class Bar: Base {
var score: Int
override var description: String { "Bar: \(score)" }
init(score: Int) {
self.score = score
super.init()
}
}
//: We need a database, a table, and a few values so that we can do a real demo:
// An in-memory database is good enough
let dbQueue = DatabaseQueue()
try dbQueue.write { db in
// Database table
try db.create(table: "base") { t in
t.column("type", .text).notNull() // Contains "Foo" or "Bar"
t.column("fooName", .text) // Contains a Foo's name
t.column("barScore", .integer) // Contains a Bar's score
}
// Demo values: two Foo and one Bar
try db.execute(
sql: """
INSERT INTO base (type, fooName, barScore) VALUES (?, ?, ?);
INSERT INTO base (type, fooName, barScore) VALUES (?, ?, ?);
INSERT INTO base (type, fooName, barScore) VALUES (?, ?, ?);
""",
arguments: [
"Foo", "Arthur", nil,
"Bar", nil, 100,
"Foo", "Barbara", nil,
])
}
//: We also need a method that decodes database rows into `Foo` or `Bar`
//: instances: let's call it `Base.decode(row:)`:
extension Base {
static func decode(row: Row) -> Base {
switch row["type"] as String {
case "Foo":
return Foo(name: row["fooName"])
case "Bar":
return Bar(score: row["barScore"])
case let type:
fatalError("Unknown Base type: \(type)")
}
}
}
//: Now we are able to express the kind of request we'd like to write. For
//: example, we could fetch all Base values from the database:
//:
//: try dbQueue.read { db in
//: // An array [Base] that constains Foo and Bar instances
//: let bases = try Base.fetchAll(db)
//: }
//:
//: But so far, we have a compiler error:
// Compiler error: Type 'Base' has no member 'fetchAll'
//try dbQueue.read { db in
// let bases = try Base.fetchAll(db)
//}
//: We'll see that this goal can be achieved by declaring a protocol, writing a
//: few extensions that guarantee the most efficient use of GRDB, and have the
//: Base class adopt this protocol.
//:
//: ### Keep It Simple, Stupid!
//:
//: But first, let's see how polymorphic decoding can be done as simply as
//: possible. After all, it is useless to design a full-fledged protocol unless
//: we need to use this protocol several times, or write code that is as
//: streamlined as possible.
//:
//: Can we fetch Foo and Bar values from an SQL request? Sure:
try dbQueue.read { db in
print("> KISS: Fetch from SQL")
let rows = try Row.fetchAll(db, sql: "SELECT * FROM base") // Fetch database rows
let bases = rows.map { row in // Decode database rows
Base.decode(row: row)
}
for base in bases { // Use fetched values
print(base.description)
}
}
//: Can we have GRDB generate SQL for us? Sure, we just need to adopt the
//: `TableRecord` protocol:
extension Base: TableRecord {
static let databaseTableName = "base"
}
try dbQueue.read { db in
print("> KISS: Fetch from query interface request")
let request = Base.filter(Column("type") == "Foo") // Define a request
let rows = try Row.fetchAll(db, request) // Fetch database rows
let bases = rows.map { row in // Decode database rows
Base.decode(row: row)
}
for base in bases { // Use fetched values
print(base.description)
}
}
//: As seen above, we can perform polymorphic requests with the standard GRDB,
//: by fetching raw rows and mapping them through a decoding method.
//:
//: The resulting code is not as streamlined as usual GRDB code, but we could
//: already do what we needed without much effort.
//:
//: ### Custom Protocol: MyDatabaseDecoder
//:
//: In the rest of this playground, we will define a **new realm of requests**.
//:
//: GRDB ships with three built-in realms of requests: requests of raw rows,
//: requests of values, and requests of record types that adopt the
//: `FetchableRecord` protocol.
//:
//: We'll define requests of record types that adopt the custom
//: `MyDatabaseDecoder` protocol:
protocol MyDatabaseDecoder {
associatedtype DecodedType
static func decode(row: Row) -> DecodedType
}
//: Types that adopt the MyDatabaseDecoder protocol are able, among other
//: things, to perform polymorphic decoding. Our Base class, for example:
extension Base: MyDatabaseDecoder {
// Already declared above:
// static func decode(row: Row) -> Base { ... }
}
//: Now let's see how we can define a new realm of requests based on the
//: `MyDatabaseDecoder` protocol. Our goal is to make them just as powerful as
//: ready-made requests of rows, values and FetchableRecord.
//:
//: All we need is a set of *extensions* that define the classic GRDB fetching
//: methods: `fetchOne`, `fetchAll`, and `fetchCursor`. The most fundamental one
//: is `fetchCursor`, because from it we can derive the two others.
//:
//: The first extension is the most low-level one: fetching from SQLite
//: **prepared statement**:
//:
//: try dbQueue.read { db in
//: let statement = try db.makeSelectStatement(sql: "SELECT ...")
//: try Base.fetchCursor(statement) // Cursor of Base
//: try Base.fetchAll(statement) // [Base]
//: try Base.fetchOne(statement) // Base?
//: }
extension MyDatabaseDecoder {
// MARK: - Fetch from SelectStatement
// SelectStatement, StatementArguments, and RowAdapter are the fundamental
// fetching parameters of GRDB. Make sure to accept them all:
static func fetchCursor(_ statement: SelectStatement, arguments: StatementArguments? = nil, adapter: RowAdapter? = nil) throws -> MapCursor<RowCursor, DecodedType> {
// Turn the cursor of raw rows into a cursor of decoded rows
return try Row.fetchCursor(statement, arguments: arguments, adapter: adapter).map {
self.decode(row: $0)
}
}
static func fetchAll(_ statement: SelectStatement, arguments: StatementArguments? = nil, adapter: RowAdapter? = nil) throws -> [DecodedType] {
// Turn the cursor into an Array
return try Array(fetchCursor(statement, arguments: arguments, adapter: adapter))
}
static func fetchOne(_ statement: SelectStatement, arguments: StatementArguments? = nil, adapter: RowAdapter? = nil) throws -> DecodedType? {
// Consume the first value of the cursor
return try fetchCursor(statement, arguments: arguments, adapter: adapter).next()
}
}
try dbQueue.read { db in
print("> Fetch from prepared statement")
let statement = try db.makeSelectStatement(sql: "SELECT * FROM base")
let bases = try Base.fetchAll(statement)
for base in bases {
print(base.description)
}
}
//: Now that we can fetch from prepared statements, we can fetch when given a
//: **request**, as defined by the `FetchRequest` protocol. This is the
//: protocol for query interface requests such as `Base.all()`, or
//: `SQLRequest`, etc.
//:
//: try dbQueue.read { db in
//: let request = Base.all()
//: try Base.fetchCursor(db, request) // Cursor of Base
//: try Base.fetchAll(db, request) // [Base]
//: try Base.fetchOne(db, request) // Base?
//: }
extension MyDatabaseDecoder {
// MARK: - Fetch from FetchRequest
static func fetchCursor<R: FetchRequest>(_ db: Database, _ request: R) throws -> MapCursor<RowCursor, DecodedType> {
let preparedRequest = try request.makePreparedRequest(db, forSingleResult: false)
return try fetchCursor(preparedRequest.statement, adapter: preparedRequest.adapter)
}
static func fetchAll<R: FetchRequest>(_ db: Database, _ request: R) throws -> [DecodedType] {
let preparedRequest = try request.makePreparedRequest(db, forSingleResult: false)
return try fetchAll(preparedRequest.statement, adapter: preparedRequest.adapter)
}
static func fetchOne<R: FetchRequest>(_ db: Database, _ request: R) throws -> DecodedType? {
// The `forSingleResult: true` argument hints the request that a single
// row will be consumed. Some requests will add a LIMIT SQL clause.
let preparedRequest = try request.makePreparedRequest(db, forSingleResult: true)
return try fetchOne(preparedRequest.statement, adapter: preparedRequest.adapter)
}
}
try dbQueue.read { db in
print("> Fetch given a request")
let request = Base.all()
let bases = try Base.fetchAll(db, request)
for base in bases {
print(base.description)
}
}
//: When it's fine to fetch given a request, it's even better when requests can
//: fetch, too:
//:
//: try dbQueue.read { db in
//: let request = Base.all()
//: try request.fetchCursor(db) // Cursor of Base
//: try request.fetchAll(db) // [Base]
//: try request.fetchOne(db) // Base?
//: }
extension FetchRequest where RowDecoder: MyDatabaseDecoder {
// MARK: - FetchRequest fetching methods
func fetchCursor(_ db: Database) throws -> MapCursor<RowCursor, RowDecoder.DecodedType> {
try RowDecoder.fetchCursor(db, self)
}
func fetchAll(_ db: Database) throws -> [RowDecoder.DecodedType] {
try RowDecoder.fetchAll(db, self)
}
func fetchOne(_ db: Database) throws -> RowDecoder.DecodedType? {
try RowDecoder.fetchOne(db, self)
}
}
try dbQueue.read { db in
print("> Fetch from request")
let request = Base.all()
let bases = try request.fetchAll(db)
for base in bases {
print(base.description)
}
}
//: Types that adopt both FetchableRecord and TableRecord are able to fetch
//: right from the base type. Let's allow this as well for MyDatabaseDecoder:
//:
//: try dbQueue.read { db in
//: try Base.fetchCursor(db) // Cursor of Base
//: try Base.fetchAll(db) // [Base]
//: try Base.fetchOne(db) // Base?
//: }
extension MyDatabaseDecoder where Self: TableRecord {
// MARK: - Static fetching methods
static func fetchCursor(_ db: Database) throws -> MapCursor<RowCursor, DecodedType> {
try all().fetchCursor(db)
}
static func fetchAll(_ db: Database) throws -> [DecodedType] {
try all().fetchAll(db)
}
static func fetchOne(_ db: Database) throws -> DecodedType? {
try all().fetchOne(db)
}
}
try dbQueue.read { db in
print("> Fetch from base type")
let bases = try Base.fetchAll(db)
for base in bases {
print(base.description)
}
}
//: Finally, you can support raw SQL as well:
//:
//: try dbQueue.read { db in
//: try Base.fetchAll(db,
//: sql: "SELECT ... WHERE name = ?",
//: arguments: ["O'Brien"]) // [Base]
//: }
extension MyDatabaseDecoder {
// MARK: - Fetch from SQL
static func fetchCursor(_ db: Database, sql: String, arguments: StatementArguments = StatementArguments(), adapter: RowAdapter? = nil) throws -> MapCursor<RowCursor, DecodedType> {
try fetchCursor(db, SQLRequest<Void>(sql: sql, arguments: arguments, adapter: adapter))
}
static func fetchAll(_ db: Database, sql: String, arguments: StatementArguments = StatementArguments(), adapter: RowAdapter? = nil) throws -> [DecodedType] {
try fetchAll(db, SQLRequest<Void>(sql: sql, arguments: arguments, adapter: adapter))
}
static func fetchOne(_ db: Database, sql: String, arguments: StatementArguments = StatementArguments(), adapter: RowAdapter? = nil) throws -> DecodedType? {
try fetchOne(db, SQLRequest<Void>(sql: sql, arguments: arguments, adapter: adapter))
}
}
try dbQueue.read { db in
print("> Fetch from SQL")
let bases = try Base.fetchAll(db, sql: "SELECT * FROM base")
for base in bases {
print(base.description)
}
}
//: Voilà! Our `MyDatabaseDecoder` protocol is now as able as the built-in
//: `FetchableRecord` protocol.
//:
//: To sum up, you have learned:
//:
//: - how to keep things simple: as long as you can fetch rows, you can decode
//: them the way you want.
//: - how to define a whole new realm of requests based on a custom protocol.
//: This involves writing a few extensions that give your protocol the same
//: fluent interface that is ready-made for the built-in FetchableRecord
//: protocol. This is more work, but you are granted with the full
//: customization freedom.
//:
//: To end this tour, let's quickly look at two other possible customized
//: row decoding strategies.
//:
//: ## An Example: Contextualized Records
//:
//: Your application needs to decode rows with a context: each decoded value
//: should be initialized with some extra value that does not come from
//: the database.
//:
//: In this case, you may define a `ContextFetchableRecord` protocol, and
//: derive all other fetching methods from the most fundamental one, which
//: fetches a cursor from a prepared statement (as we did for the
//: MyDatabaseDecoder protocol, above):
protocol ContextFetchableRecord {
associatedtype Context
init(row: Row, context: Context)
}
extension ContextFetchableRecord {
static func fetchCursor(
_ statement: SelectStatement,
arguments: StatementArguments? = nil,
adapter: RowAdapter? = nil,
context: Context)
throws -> MapCursor<RowCursor, Self>
{
// Turn the cursor of raw rows into a cursor of decoded rows
return try Row.fetchCursor(statement, arguments: arguments, adapter: adapter).map {
self.init(row: $0, context: context)
}
}
// Define fetchAll, fetchOne, and other extensions...
}
//: ## An Example: Failable Records
//:
//: Your application needs a record type that supports untrusted databases,
//: and may fail at decoding database rows (throw an error when a row contains
//: invalid values).
//:
//: In this case, you may define a `FailableFetchableRecord` protocol, and
//: derive all other fetching methods from the most fundamental one, which
//: fetches a cursor from a prepared statement (as we did for the
//: MyDatabaseDecoder protocol, above):
protocol FailableFetchableRecord {
init(row: Row) throws
}
extension FailableFetchableRecord {
static func fetchCursor(
_ statement: SelectStatement,
arguments: StatementArguments? = nil,
adapter: RowAdapter? = nil)
throws -> MapCursor<RowCursor, Self>
{
// Turn the cursor of raw rows into a cursor of decoded rows
return try Row.fetchCursor(statement, arguments: arguments, adapter: adapter).map {
try self.init(row: $0)
}
}
// Define fetchAll, fetchOne, and other extensions...
}