GRDB JSONSynchronization

JSONSynchronization.swift

// To run this playground, select and build the GRDBOSX scheme.
//
// This sample code shows how to use GRDB to synchronize a database table
// with a JSON payload. We use as few SQL queries as possible:
//
// - Only one SELECT query.
// - One query per insert, delete, and update.
// - Useless UPDATE statements are avoided.

import Foundation
import GRDB


// Open an in-memory database that logs all its SQL statements

var configuration = Configuration()
configuration.trace = { print($0) }
let dbQueue = DatabaseQueue(configuration: configuration)


// Create a database table

try dbQueue.inDatabase { db in
    try db.create(table: "players") { t in
        t.column("id", .integer).primaryKey()
        t.column("name", .text)
        t.column("score", .integer)
    }
}


// Define a Player struct
struct Player {
    let id: Int64
    let name: String
    let score: Int
    
    init(json: [String : Any]) {
        self.id = json["id"] as! Int64
        self.name = json["name"] as! String
        self.score = json["score"] as! Int
    }
}

// Turn Player into a GRDB record:
extension Player : RowConvertible, Persistable {
    static var databaseTableName = "players"
    
    init(row: Row) {
        id = row["id"]
        name = row["name"]
        score = row["score"]
    }
    
    func encode(to container: inout PersistenceContainer) {
        container["id"] = id
        container["name"] = name
        container["score"] = score
    }
}

// Record is a GRDB built-in class that provides change tracking.
//
// SyncRecord is a subclass that can wrap any other record. That means that
// SyncRecord<Player> provides change tracking on players!
private class SyncRecord<Base: RowConvertible & MutablePersistable> : Record {
    var base: Base
    
    init(base: Base) {
        self.base = base
        super.init()
    }
    
    override class var databaseTableName: String {
        return Base.databaseTableName
    }
    
    required init(row: Row) {
        self.base = Base(row: row)
        super.init(row: row)
    }
    
    override func encode(to container: inout PersistenceContainer) {
        base.encode(to: &container)
    }
}

// Synchronizes the players table with a JSON payload
func synchronizePlayers(with jsonString: String, in db: Database) throws {
    let jsonData = jsonString.data(using: .utf8)!
    let json = try JSONSerialization.jsonObject(with: jsonData, options: []) as! [String: Any]
    
    // A support function that extracts an ID from a JSON player.
    func jsonPlayerId(_ jsonPlayer: [String: Any]) -> Int64 {
        return jsonPlayer["id"] as! Int64
    }
    
    // Sort JSON players by id:
    let jsonPlayers = (json["players"] as! [[String: Any]]).sorted {
        return jsonPlayerId($0) < jsonPlayerId($1)
    }
    
    // Sort database players by id:
    let players = try SyncRecord<Player>.order(Column("id")).fetchAll(db)
    
    // Now that both lists are sorted by id, we can compare them with
    // the sortedMerge() function (see https://gist.github.com/groue/7e8510849ded36f7d770).
    //
    // We'll delete, insert or update players, depending on their presence
    // in either lists.
    for mergeStep in sortedMerge(
        left: players,           // Database players
        right: jsonPlayers,      // JSON players
        leftKey: { $0.base.id }, // The id of a database player
        rightKey: jsonPlayerId)  // The id of a JSON player
    {
        switch mergeStep {
        case .left(let player):
            // Delete database player without matching JSON player:
            try player.delete(db)
        case .right(let jsonPlayer):
            // Insert JSON player without matching database player:
            let player = Player(json: jsonPlayer)
            try player.insert(db)
        case .common(let player, let jsonPlayer):
            // Update database player with its JSON counterpart:
            player.base = Player(json: jsonPlayer)
            try player.updateChanges(db)
        }
    }
}

do {
    let jsonString = """
    {
        "players": [
            { "id": 1, "name": "Arthur", "score": 1000},
            { "id": 2, "name": "Barbara", "score": 2000},
            { "id": 3, "name": "Craig", "score": 500},
        ]
    }
    """
    print("---\nImport \(jsonString)")
    try dbQueue.inDatabase { db in
        // SELECT * FROM players ORDER BY id
        // INSERT INTO "players" ("id", "name", "score") VALUES (1,'Arthur',1000)
        // INSERT INTO "players" ("id", "name", "score") VALUES (2,'Barbara',2000)
        // INSERT INTO "players" ("id", "name", "score") VALUES (3,'Craig',500)
        try synchronizePlayers(with: jsonString, in: db)
    }
}

do {
    let jsonString = """
    {
        "players": [
            { "id": 2, "name": "Barbara", "score": 3000},
            { "id": 3, "name": "Craig", "score": 500},
            { "id": 4, "name": "Daniel", "score": 1500},
        ]
    }
    """
    print("---\nImport \(jsonString)")
    try dbQueue.inDatabase { db in
        // SELECT * FROM players ORDER BY id
        // DELETE FROM "players" WHERE "id"=1
        // UPDATE "players" SET "score"=3000 WHERE "id"=2
        // INSERT INTO "players" ("id", "name", "score") VALUES (4,'Daniel',1500)
        try synchronizePlayers(with: jsonString, in: db)
    }
}