playing with GRDB cursor

GRDB-Cursor-Playground.swift

// To run this playground, select and build the GRDBOSX scheme.

import GRDB
import PlaygroundSupport

public struct User: Codable, TableRecord, FetchableRecord, MutablePersistableRecord {
    public static let databaseTableName: String = "user"
    
    public var id: Int64?
    public var username: String
    public var isFlagged: Bool
    
    init(id: Int64? = nil, username: String, isFlagged: Bool = false) {
        self.id = id
        self.username = username
        self.isFlagged = isFlagged
    }
    
    public mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

public struct FlagUser: Codable, TableRecord, FetchableRecord, MutablePersistableRecord {
    public static let databaseTableName: String = "flagUser"
    
    public var username: String
}

let url = playgroundSharedDataDirectory.appendingPathComponent("GRDB")
try! FileManager.default.createDirectory(at: url, withIntermediateDirectories: true)
try? FileManager.default.removeItem(at: url.appendingPathComponent("database.sqlite-wal"))
try? FileManager.default.removeItem(at: url.appendingPathComponent("database.sqlite-shm"))
try? FileManager.default.removeItem(at: url.appendingPathComponent("database.sqlite"))
let databasePath = url.appendingPathComponent("database.sqlite").path

var configuration = Configuration()
configuration.trace = { print($0) }

var migrator = DatabaseMigrator()

migrator.registerMigration("initial") { database in
    try database.create(table: User.databaseTableName) { definition in
        definition.column("id", .integer).primaryKey(autoincrement: true)
        definition.column("username", .text).notNull()
        definition.column("isFlagged", .boolean).notNull().defaults(to: false)
    }
    
    try database.create(table: FlagUser.databaseTableName) { definition in
        definition.column("username", .text).notNull()
    }
}

migrator.registerMigration("data") { database in
    [Int](0...50).forEach {
        var user = User(username: "User\($0)")
        try! user.insert(database)
    }
    
    [Int](40...60).forEach {
        var flag = FlagUser(username: "User\($0)")
        try! flag.insert(database)
    }
}

let pool = try! DatabasePool(path: databasePath, configuration: configuration)
try migrator.migrate(pool)


func cursorIterationTest1() {
    let query = "SELECT * FROM user WHERE isFlagged = false"
    let snapshot = try! pool.makeSnapshot()
    
    try! snapshot.read { snapshotDatabase in
        
        let flaggedInSnapshotBefore = try User.fetchAll(snapshotDatabase, sql: query).count
        let flaggedInPoolBefore = try pool.read {try User.fetchAll($0, sql: query).count }
        let totalInSnapshotBefore = try User.fetchCount(snapshotDatabase)
        let totalInPoolBefore = try pool.read {try User.fetchCount($0) }
        
        let cursor = try User.fetchCursor(snapshotDatabase, sql: query)
        var change = 5
        
        try pool.write { database in
            while var user = try cursor.next() {
                user.isFlagged = true
                try user.save(database)
                
                if change >= 0 {
                    var newUser = User(username: "user\(100 + change)")
                    try newUser.save(database)
                    change -= 1
                }
                
            }
        }
        
        let flaggedInSnapshot = try User.fetchAll(snapshotDatabase, sql: query).count
        let flaggedInPool = try pool.read {try User.fetchAll($0, sql: query).count }
        let totalInSnapshot = try User.fetchCount(snapshotDatabase)
        let totalInPool = try pool.read {try User.fetchCount($0) }
        
        print("\nflaggedInSnapshot - before: \(flaggedInSnapshotBefore) after: \(flaggedInSnapshot)")
        print("flaggedInPool - before: \(flaggedInPoolBefore) after:\(flaggedInPool)")
        print("totalInSnapshot - before: \(totalInSnapshotBefore) after:\(totalInSnapshot)")
        print("totalInPool - before: \(totalInPoolBefore) after:\(totalInPool)\n")
    }
}

func cursorIterationTest2() {
    let query = "SELECT * FROM flagUser WHERE (SELECT COUNT(id) FROM user WHERE username = flagUser.username AND isFlagged = true) = 0"
    let snapshot = try! pool.makeSnapshot()
    
    try! snapshot.read { snapshotDatabase in
        let inSnapshotBefore = try FlagUser.fetchAll(snapshotDatabase, sql: query).count
        let inPoolBefore = try pool.read {try FlagUser.fetchAll($0, sql: query).count }

        let cursor = try FlagUser.fetchCursor(snapshotDatabase, sql: query)
        
        try pool.write { database in
            while let flagged = try cursor.next() {
                //fails on next `cursor.next()` if `fetchOne(snapshotDatabase`. works if `fetchOne(database`.
                var user = try User.fetchOne(snapshotDatabase, sql: "SELECT * FROM user WHERE username = '\(flagged.username)' LIMIT 1") ??
                    User(username: flagged.username)
                user.isFlagged = true
                try user.save(database)
            }
        }
        
        let inSnapshotAfter = try FlagUser.fetchAll(snapshotDatabase, sql: query).count
        let inPoolAfter = try pool.read {try FlagUser.fetchAll($0, sql: query).count }
        
        print("\ninSnapshot - before: \(inSnapshotBefore) after: \(inSnapshotAfter)")
        print("inPool - before: \(inPoolBefore) after:\(inPoolAfter)\n")
        
    }
}

func cursorIterationTest3() {
    let query = "SELECT * FROM flagUser"
    let query2 = "SELECT * FROM user WHERE isFlagged = true"
    let snapshot = try! pool.makeSnapshot()
    
    try! snapshot.read { snapshotDatabase in
        let inSnapshotBefore = try FlagUser.fetchAll(snapshotDatabase, sql: query2).count
        let inPoolBefore = try pool.read {try FlagUser.fetchAll($0, sql: query2).count }
        
        let cursor = try FlagUser.fetchCursor(snapshotDatabase, sql: query)
        
        try pool.write { database in
            while let flagged = try cursor.next() {
                var user = try User.fetchOne(snapshotDatabase, sql: "SELECT * FROM user WHERE username = '\(flagged.username)' LIMIT 1") ??
                    User(username: flagged.username)
                user.isFlagged = true
                try user.save(database)
            }
        }
        
        let inSnapshotAfter = try FlagUser.fetchAll(snapshotDatabase, sql: query2).count
        let inPoolAfter = try pool.read {try FlagUser.fetchAll($0, sql: query2).count }
        
        print("\ninSnapshot - before: \(inSnapshotBefore) after: \(inSnapshotAfter)")
        print("inPool - before: \(inPoolBefore) after:\(inPoolAfter)\n")
        
    }
}

//user one
//cursorIterationTest1()
cursorIterationTest2()
//cursorIterationTest3()

sample2.swift

// To run this playground, select and build the GRDBOSX scheme.

import GRDB

public struct User: Codable, TableRecord, FetchableRecord, MutablePersistableRecord {
    public static let databaseTableName: String = "user"
    
    public var id: Int64?
    public var username: String
    public var isFlagged: Bool
    
    init(id: Int64? = nil, username: String, isFlagged: Bool = false) {
        self.id = id
        self.username = username
        self.isFlagged = isFlagged
    }
    
    public mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

public struct FlagUser: Codable, TableRecord, FetchableRecord, MutablePersistableRecord {
    public static let databaseTableName: String = "flagUser"
    
    public var username: String
}

var configuration = Configuration()
configuration.trace = { print($0) }

var migrator = DatabaseMigrator()

migrator.registerMigration("initial") { database in
    try database.create(table: User.databaseTableName) { definition in
        definition.column("id", .integer).primaryKey(autoincrement: true)
        definition.column("username", .text).notNull()
        definition.column("isFlagged", .boolean).notNull().defaults(to: false)
    }
    
    try database.create(table: FlagUser.databaseTableName) { definition in
        definition.column("username", .text).notNull()
    }
}

migrator.registerMigration("data") { database in
    [Int](0...50).forEach {
        var user = User(username: "User\($0)")
        try! user.insert(database)
    }
    
    [Int](40...60).forEach {
        var flag = FlagUser(username: "User\($0)")
        try! flag.insert(database)
    }
}

let queue = try! DatabaseQueue(configuration: configuration)
try migrator.migrate(queue)

func cursorIterationTest2() {
    let query = "SELECT * FROM flagUser WHERE (SELECT COUNT(id) FROM user WHERE username = flagUser.username AND isFlagged = true) = 0"
    try! queue.read { database in
        let cursor = try FlagUser.fetchCursor(database, sql: query)
        while let flagged = try cursor.next() {
            var user = try User.fetchOne(database, sql: "SELECT * FROM user WHERE username = '\(flagged.username)' LIMIT 1") ??
                User(username: flagged.username)
        }
    }
}

cursorIterationTest2()