telegram - sqliteValueBox

private let lock = NSRecursiveLock()

fileprivate let basePath: String
private let isTemporary: Bool
private let isReadOnly: Bool
private let useCaches: Bool
private let inMemory: Bool
private let encryptionParameters: ValueBoxEncryptionParameters?
private let databasePath: String
private let removeDatabaseOnError: Bool
private var database: Database!
private var tables: [Int32: SqliteValueBoxTable] = [:]
private var fullTextTables: [Int32: ValueBoxFullTextTable] = [:]

private var getStatements: [Int32 : SqlitePreparedStatement] = [:]
private var getRowIdStatements: [Int32 : SqlitePreparedStatement] = [:]
private var rangeKeyAscStatementsLimit: [Int32 : SqlitePreparedStatement] = [:]
private var rangeKeyAscStatementsNoLimit: [Int32 : SqlitePreparedStatement] = [:]
private var rangeKeyDescStatementsLimit: [Int32 : SqlitePreparedStatement] = [:]
private var rangeKeyDescStatementsNoLimit: [Int32 : SqlitePreparedStatement] = [:]
private var deleteRangeStatements: [Int32 : SqlitePreparedStatement] = [:]
private var rangeValueAscStatementsLimit: [Int32 : SqlitePreparedStatement] = [:]
private var rangeValueAscStatementsNoLimit: [Int32 : SqlitePreparedStatement] = [:]
private var rangeValueDescStatementsLimit: [Int32 : SqlitePreparedStatement] = [:]
private var rangeValueDescStatementsNoLimit: [Int32 : SqlitePreparedStatement] = [:]
private var scanStatements: [Int32 : SqlitePreparedStatement] = [:]
private var scanKeysStatements: [Int32 : SqlitePreparedStatement] = [:]
private var existsStatements: [Int32 : SqlitePreparedStatement] = [:]
private var updateStatements: [Int32 : SqlitePreparedStatement] = [:]
private var insertOrReplacePrimaryKeyStatements: [Int32 : SqlitePreparedStatement] = [:]
private var insertOrReplaceIndexKeyStatements: [Int32 : SqlitePreparedStatement] = [:]
private var deleteStatements: [Int32 : SqlitePreparedStatement] = [:]
private var moveStatements: [Int32 : SqlitePreparedStatement] = [:]
private var copyStatements: [TablePairKey : SqlitePreparedStatement] = [:]
private var fullTextInsertStatements: [Int32 : SqlitePreparedStatement] = [:]
private var fullTextDeleteStatements: [Int32 : SqlitePreparedStatement] = [:]
private var fullTextMatchGlobalStatements: [Int32 : SqlitePreparedStatement] = [:]
private var fullTextMatchCollectionStatements: [Int32 : SqlitePreparedStatement] = [:]
private var fullTextMatchCollectionTagsStatements: [Int32 : SqlitePreparedStatement] = [:]

private var secureDeleteEnabled: Bool = false
private let checkpoints = MetaDisposable()
private let queue: Queue


public init?(basePath: String, queue: Queue, isTemporary: Bool, isReadOnly: Bool, useCaches: Bool, removeDatabaseOnError: Bool,
encryptionParameters: ValueBoxEncryptionParameters?, upgradeProgress: (Float) -> Void, inMemory: Bool = false) {
    self.basePath = basePath
    self.isTemporary = isTemporary
    self.isReadOnly = isReadOnly
    self.useCaches = useCaches
    self.removeDatabaseOnError = removeDatabaseOnError
    self.inMemory = inMemory
    self.encryptionParameters = encryptionParameters
    self.databasePath = basePath + "/db_sqlite"
    self.queue = queue
    if let database = self.openDatabase(encryptionParameters: encryptionParameters, isTemporary: isTemporary, isReadOnly: isReadOnly,
upgradeProgress: upgradeProgress) {
        self.database = database
    } else {
        return nil
    }
}

deinit {
    precondition(self.queue.isCurrent())
    self.clearStatements()
    checkpoints.dispose()
}

func internalClose() {
    self.database = nil
}

private func openDatabase(encryptionParameters: ValueBoxEncryptionParameters?, isTemporary: Bool, isReadOnly: Bool, upgradeProgress: (Float) -> Void) -> Database? {
    precondition(self.queue.isCurrent())

    checkpoints.set(nil)
    lock.lock()

    let _ = try? FileManager.default.createDirectory(atPath: basePath, withIntermediateDirectories: true, attributes: nil)
    let path = basePath + "/db_sqlite"

    postboxLog("Instance \(self) opening sqlite at \(path)")

  // dump existing db data in DEBUG mode
    #if DEBUG
    let exists = FileManager.default.fileExists(atPath: path)
    postboxLog("Opening \(path), exists: \(exists)")
    if exists {
        do {
            let data = try Data(contentsOf: URL(fileURLWithPath: path), options: .mappedIfSafe)
            postboxLog("\(path) size: \(data.count)")
        } catch let e {
            postboxLog("Couldn't open database: \(e)")
        }
    }
    let walExists = FileManager.default.fileExists(atPath: path + "-wal")
    postboxLog("Opening \(path)-wal, exists: \(walExists)")
    if walExists {
        do {
            let data = try Data(contentsOf: URL(fileURLWithPath: path + "-wal"), options: .mappedIfSafe)
            postboxLog("\(path)-wal size: \(data.count)")
        } catch let e {
            postboxLog("Couldn't open database: \(e)")
        }
    }
    #endif

  // open db in path or memory
    var database: Database
    if let result = Database(self.inMemory ? ":memory:" : path, readOnly: isReadOnly) {
        database = result
    } else {
        postboxLog("Couldn't open DB")

        if isReadOnly {
            postboxLog("Readonly, exiting")
            return nil
        }

        let tempPath = basePath + "_test\(arc4random())"
        enum TempError: Error {
            case generic
        }
        do {
            try FileManager.default.createDirectory(atPath: tempPath, withIntermediateDirectories: true, attributes: nil)
          
          // create a test.db
            let testDatabase = Database(tempPath + "/test_db", readOnly: false)!
          // set journal_mode to WAL
            var resultCode = testDatabase.execute("PRAGMA journal_mode=WAL")
            if !resultCode {
                throw TempError.generic
            }
          // set the user_version header field to 123
            resultCode = testDatabase.execute("PRAGMA user_version=123")
            if !resultCode {
                throw TempError.generic
            }
        } catch {
          // throw has no write access to database folder
            let _ = try? FileManager.default.removeItem(atPath: tempPath)
            postboxLog("Don't have write access to database folder")
            preconditionFailure("Don't have write access to database folder")
        }

      // remove on error
        if self.removeDatabaseOnError {
            let _ = try? FileManager.default.removeItem(atPath: path)
        }
        preconditionFailure("Couldn't open database")
    }

    postboxLog("Did open DB at \(path)")

    sqlite3_busy_timeout(database.handle, 5 * 1000)

    var resultCode: Bool = true

    resultCode = database.execute("PRAGMA cipher_plaintext_header_size=32")
    assert(resultCode)
    resultCode = database.execute("PRAGMA cipher_default_plaintext_header_size=32")
    assert(resultCode)

    postboxLog("Did set up cipher")

    if self.isEncrypted(database) {
        postboxLog("Database is encrypted")

        if let encryptionParameters = encryptionParameters {// has stored encryption key 
            precondition(encryptionParameters.salt.data.count == 16)
            precondition(encryptionParameters.key.data.count == 32)

            let hexKey = hexString(encryptionParameters.key.data + encryptionParameters.salt.data)

          // provide key and salt
            resultCode = database.execute("PRAGMA key=\"x'\(hexKey)'\"")
            assert(resultCode)

            postboxLog("Setting encryption key")

          // still encrypted (key is invalid)
            if self.isEncrypted(database) {
                postboxLog("Encryption key is invalid")

                if isTemporary || isReadOnly || !self.removeDatabaseOnError {
                    return nil
                }

                for fileName in dabaseFileNames {
                    let _ = try? FileManager.default.removeItem(atPath: basePath + "/\(fileName)")
                }
              // retyr create database
                database = Database(path, readOnly: false)!

                resultCode = database.execute("PRAGMA cipher_plaintext_header_size=32")
                assert(resultCode)
                resultCode = database.execute("PRAGMA cipher_default_plaintext_header_size=32")
                assert(resultCode)

              // provide key and salt
                resultCode = database.execute("PRAGMA key=\"x'\(hexKey)'\"")
                assert(resultCode)
            }
        } else {// encryption key is required
            postboxLog("Encryption key is required")
            if isReadOnly || !self.removeDatabaseOnError {
                return nil
            }

            assert(false)
            for fileName in dabaseFileNames {
                let _ = try? FileManager.default.removeItem(atPath: basePath + "/\(fileName)")
            }
          // create the database
            database = Database(path, readOnly: false)!

            resultCode = database.execute("PRAGMA cipher_plaintext_header_size=32")
            assert(resultCode)
            resultCode = database.execute("PRAGMA cipher_default_plaintext_header_size=32")
            assert(resultCode)
        }
    } else if let encryptionParameters = encryptionParameters, encryptionParameters.forceEncryptionIfNoSet {// not encrypted
        postboxLog("Not encrypted")

        let hexKey = hexString(encryptionParameters.key.data + encryptionParameters.salt.data)

        if FileManager.default.fileExists(atPath: path) {// db file is exsiting
            if isReadOnly {
                return nil
            }

            postboxLog("Reencrypting database")
          // reencrypting database
            database = self.reencryptInPlace(database: database, encryptionParameters: encryptionParameters)

          // encrypted successfully
            if self.isEncrypted(database) {
                postboxLog("Reencryption failed")

                for fileName in dabaseFileNames {
                    let _ = try? FileManager.default.removeItem(atPath: basePath + "/\(fileName)")
                }
                database = Database(path, readOnly: false)!

                resultCode = database.execute("PRAGMA cipher_plaintext_header_size=32")
                assert(resultCode)
                resultCode = database.execute("PRAGMA cipher_default_plaintext_header_size=32")
                assert(resultCode)

              // test the encryption key
                resultCode = database.execute("PRAGMA key=\"x'\(hexKey)'\"")
                assert(resultCode)
            }
        } else {//db file not exsiting
            precondition(encryptionParameters.salt.data.count == 16)
            precondition(encryptionParameters.key.data.count == 32)
          // test the encryption key
            resultCode = database.execute("PRAGMA key=\"x'\(hexKey)'\"")
            assert(resultCode)

            if self.isEncrypted(database) {
                postboxLog("Encryption setup failed")
                //assert(false)

                if isReadOnly {
                    return nil
                }

                for fileName in dabaseFileNames {
                    let _ = try? FileManager.default.removeItem(atPath: basePath + "/\(fileName)")
                }
              // recreate the db file
                database = Database(path, readOnly: false)!

                resultCode = database.execute("PRAGMA cipher_plaintext_header_size=32")
                assert(resultCode)
                resultCode = database.execute("PRAGMA cipher_default_plaintext_header_size=32")
                assert(resultCode)

              // test the encryption key
                resultCode = database.execute("PRAGMA key=\"x'\(hexKey)'\"")
                assert(resultCode)
            }
        }
    }

    postboxLog("Did set up encryption")

    if !self.useCaches {
      // set cache size
        resultCode = database.execute("PRAGMA cache_size=32")
        assert(resultCode)
    }
  // diable memory-map
    resultCode = database.execute("PRAGMA mmap_size=0")
    assert(resultCode)
  // set synchronous mode to `Normal`
    resultCode = database.execute("PRAGMA synchronous=NORMAL")
    assert(resultCode)
  // set temp_store type as `MEMORY`
    resultCode = database.execute("PRAGMA temp_store=MEMORY")
    assert(resultCode)
  // set journal_mode to `WAL`
    resultCode = database.execute("PRAGMA journal_mode=WAL")
    assert(resultCode)
  // disable memory wiping
    resultCode = database.execute("PRAGMA cipher_memory_security = OFF")
    assert(resultCode)

    postboxLog("Did set up pragmas")

    //resultCode = database.execute("PRAGMA wal_autocheckpoint=500")
    //database.execute("PRAGMA journal_size_limit=1536")

    /*#if DEBUG
    var statement: OpaquePointer? = nil
    sqlite3_prepare_v2(database.handle, "PRAGMA integrity_check", -1, &statement, nil)
    let preparedStatement = SqlitePreparedStatement(statement: statement)
    while preparedStatement.step(handle: database.handle, path: self.databasePath) {
        let value = preparedStatement.valueAt(0)
        let text = String(data: Data(bytes: value.memory.assumingMemoryBound(to: UInt8.self), count: value.length), encoding: .utf8)
        print("integrity_check: \(text ?? "")")
        assert(text == "ok")
        //let value = preparedStatement.stringAt(0)
        //print("integrity_check: \(value)")
    }
    preparedStatement.destroy()
    #endif*/

    let _ = self.runPragma(database, "checkpoint_fullfsync = 1")
    assert(self.runPragma(database, "checkpoint_fullfsync") == "1")

    postboxLog("Did set up checkpoint_fullfsync")

    self.beginInternal(database: database)

    postboxLog("Did begin transaction")

    let result = self.getUserVersion(database)

    if result < 3 {
        resultCode = database.execute("CREATE TABLE __meta_fulltext_tables (name INTEGER)")
        assert(resultCode)
    }

    if result < 4 {
        resultCode = database.execute("PRAGMA user_version=4")
        assert(resultCode)
    }

    for table in self.listTables(database) {
        self.tables[table.table.id] = table
    }
    for table in self.listFullTextTables(database) {
        self.fullTextTables[table.id] = table
    }

    postboxLog("Did load tables")

    self.commitInternal(database: database)

    postboxLog("Did commit final")

    lock.unlock()

    return database
}

private func listTables(_ database: Database) -> [SqliteValueBoxTable] {
    precondition(self.queue.isCurrent())
    var statement: OpaquePointer? = nil
    let status = sqlite3_prepare_v2(database.handle, "SELECT name, type, sql FROM sqlite_master", -1, &statement, nil)
    precondition(status == SQLITE_OK)
    let preparedStatement = SqlitePreparedStatement(statement: statement)
    var tables: [SqliteValueBoxTable] = []

    while preparedStatement.step(handle: database.handle, true, pathToRemoveOnError: self.removeDatabaseOnError ? self.databasePath : nil) {
        guard let name = preparedStatement.stringAt(0) else {
            assertionFailure()
            continue
        }
        guard let type = preparedStatement.stringAt(1), type == "table" else {
            continue
        }
        guard let sql = preparedStatement.stringAt(2) else {
            assertionFailure()
            continue
        }

        if name.hasPrefix("t") {
            if let intName = Int(String(name[name.index(after: name.startIndex)...])) {
                let keyType: ValueBoxKeyType
                var hasPrimaryKey = false
                if sql.range(of: "(key INTEGER") != nil {
                    keyType = .int64
                    hasPrimaryKey = true
                } else if sql.range(of: "(key BLOB") != nil {
                    keyType = .binary
                    if sql.range(of: "(key BLOB PRIMARY KEY") != nil {
                        hasPrimaryKey = true
                    }
                } else {
                    assertionFailure()
                    continue
                }
                let isCompact = sql.range(of: "WITHOUT ROWID") != nil
                tables.append(SqliteValueBoxTable(table: ValueBoxTable(id: Int32(intName), keyType: keyType, compactValuesOnCreation: isCompact), hasPrimaryKey: hasPrimaryKey))
            }
        }
    }
    preparedStatement.destroy()

    return tables
}

private func listFullTextTables(_ database: Database) -> [ValueBoxFullTextTable] {
    precondition(self.queue.isCurrent())
    var statement: OpaquePointer? = nil
    let status = sqlite3_prepare_v2(database.handle, "SELECT name FROM __meta_fulltext_tables", -1, &statement, nil)
    assert(status == SQLITE_OK)
    let preparedStatement = SqlitePreparedStatement(statement: statement)
    var tables: [ValueBoxFullTextTable] = []

    while preparedStatement.step(handle: database.handle, true, pathToRemoveOnError: self.removeDatabaseOnError ? self.databasePath : nil) {
        let value = preparedStatement.int64At(0)
        tables.append(ValueBoxFullTextTable(id: Int32(value)))
    }
    preparedStatement.destroy()
    return tables
}

private func checkTable(_ table: ValueBoxTable) -> SqliteValueBoxTable {
    precondition(self.queue.isCurrent())
    if let currentTable = self.tables[table.id] {
        precondition(currentTable.table.keyType == table.keyType)
        return currentTable
    } else {
        self.createTable(database: self.database, table: table)
        let resultTable = SqliteValueBoxTable(table: table, hasPrimaryKey: true)
        self.tables[table.id] = resultTable
        return resultTable
    }
}

private func createTable(database: Database, table: ValueBoxTable) {
    switch table.keyType {
        case .binary:
            var resultCode: Bool
            var createStatement = "CREATE TABLE IF NOT EXISTS t\(table.id) (key BLOB PRIMARY KEY, value BLOB)"
            if table.compactValuesOnCreation {
                createStatement += " WITHOUT ROWID"
            }
            resultCode = database.execute(createStatement)
            assert(resultCode)
        case .int64:
            let resultCode = database.execute("CREATE TABLE IF NOT EXISTS t\(table.id) (key INTEGER PRIMARY KEY, value BLOB)")
            assert(resultCode)
    }
}

private func checkFullTextTable(_ table: ValueBoxFullTextTable) {
    precondition(self.queue.isCurrent())
    if let _ = self.fullTextTables[table.id] {
    } else {
        var resultCode = self.database.execute("CREATE VIRTUAL TABLE IF NOT EXISTS ft\(table.id) USING fts5(collectionId, itemId, contents,
tags)")
        precondition(resultCode)
        self.fullTextTables[table.id] = table
        resultCode = self.database.execute("INSERT OR IGNORE INTO __meta_fulltext_tables(name) VALUES (\(table.id))")
        precondition(resultCode)
    }
}

private func getStatement(_ table: ValueBoxTable, key: ValueBoxKey) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    checkTableKey(table, key)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.getStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT value FROM t\(table.id) WHERE key=?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.getStatements[table.id] = preparedStatement
        resultStatement =  preparedStatement
    }
    
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: key.memory, length: key.length)
        case .int64:
            resultStatement.bind(1, number: key.getInt64(0))
    }
    
    return resultStatement
}

private func getRowIdStatement(_ table: ValueBoxTable, key: ValueBoxKey) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    checkTableKey(table, key)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.getRowIdStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT rowid FROM t\(table.id) WHERE key=?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.getRowIdStatements[table.id] = preparedStatement
        resultStatement =  preparedStatement
    }
    
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: key.memory, length: key.length)
        case .int64:
            resultStatement.bind(1, number: key.getInt64(0))
    }
    
    return resultStatement
}

private func rangeKeyAscStatementLimit(_ table: ValueBoxTable, start: ValueBoxKey, end: ValueBoxKey, limit: Int) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    checkTableKey(table, start)
    checkTableKey(table, end)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.rangeKeyAscStatementsLimit[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT key FROM t\(table.id) WHERE key > ? AND key < ? ORDER BY key ASC LIMIT
?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.rangeKeyAscStatementsLimit[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: start.memory, length: start.length)
            resultStatement.bind(2, data: end.memory, length: end.length)
        case .int64:
            resultStatement.bind(1, number: start.getInt64(0))
            resultStatement.bind(2, number: end.getInt64(0))
    }
    resultStatement.bind(3, number: Int32(limit))
    
    return resultStatement
}

private func rangeKeyAscStatementNoLimit(_ table: ValueBoxTable, start: ValueBoxKey, end: ValueBoxKey) ->
    SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    checkTableKey(table, start)
    checkTableKey(table, end)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.rangeKeyAscStatementsNoLimit[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT key FROM t\(table.id) WHERE key > ? AND key < ? ORDER BY key ASC", -1,
&statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.rangeKeyAscStatementsNoLimit[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: start.memory, length: start.length)
            resultStatement.bind(2, data: end.memory, length: end.length)
        case .int64:
            resultStatement.bind(1, number: start.getInt64(0))
            resultStatement.bind(2, number: end.getInt64(0))
    }
    
    return resultStatement
}

private func rangeKeyDescStatementLimit(_ table: ValueBoxTable, start: ValueBoxKey, end: ValueBoxKey, limit: Int) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    let resultStatement: SqlitePreparedStatement
    checkTableKey(table, start)
    checkTableKey(table, end)
    
    if let statement = self.rangeKeyDescStatementsLimit[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT key FROM t\(table.id) WHERE key > ? AND key < ? ORDER BY key DESC LIMIT
?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.rangeKeyDescStatementsLimit[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: start.memory, length: start.length)
            resultStatement.bind(2, data: end.memory, length: end.length)
        case .int64:
            resultStatement.bind(1, number: start.getInt64(0))
            resultStatement.bind(2, number: end.getInt64(0))
    }
    resultStatement.bind(3, number: Int32(limit))
    
    return resultStatement
}

private func rangeKeyDescStatementNoLimit(_ table: ValueBoxTable, start: ValueBoxKey, end: ValueBoxKey) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    let resultStatement: SqlitePreparedStatement
    checkTableKey(table, start)
    checkTableKey(table, end)
    
    if let statement = self.rangeKeyDescStatementsNoLimit[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT key FROM t\(table.id) WHERE key > ? AND key < ? ORDER BY key DESC", -1,
&statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.rangeKeyDescStatementsNoLimit[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: start.memory, length: start.length)
            resultStatement.bind(2, data: end.memory, length: end.length)
        case .int64:
            resultStatement.bind(1, number: start.getInt64(0))
            resultStatement.bind(2, number: end.getInt64(0))
    }
    
    return resultStatement
}

private func rangeDeleteStatement(_ table: ValueBoxTable, start: ValueBoxKey, end: ValueBoxKey) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    let resultStatement: SqlitePreparedStatement
    checkTableKey(table, start)
    checkTableKey(table, end)
    precondition(start <= end)
    
    if let statement = self.deleteRangeStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "DELETE FROM t\(table.id) WHERE key >= ? AND key <= ?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.deleteRangeStatements[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: start.memory, length: start.length)
            resultStatement.bind(2, data: end.memory, length: end.length)
        case .int64:
            resultStatement.bind(1, number: start.getInt64(0))
            resultStatement.bind(2, number: end.getInt64(0))
    }
    
    return resultStatement
}

private func rangeValueAscStatementLimit(_ table: ValueBoxTable, start: ValueBoxKey, end: ValueBoxKey, limit: Int) -> SqlitePreparedStatement
{
    precondition(self.queue.isCurrent())
    checkTableKey(table, start)
    checkTableKey(table, end)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.rangeValueAscStatementsLimit[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
            // with some doubt for the where clause here
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT key, value FROM t\(table.id) WHERE key > ? AND key < ? ORDER BY key ASC
LIMIT ?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.rangeValueAscStatementsLimit[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: start.memory, length: start.length)
            resultStatement.bind(2, data: end.memory, length: end.length)
        case .int64:
            resultStatement.bind(1, number: start.getInt64(0))
            resultStatement.bind(2, number: end.getInt64(0))
    }
    resultStatement.bind(3, number: Int32(limit))
    
    return resultStatement
}

private func rangeValueAscStatementNoLimit(_ table: ValueBoxTable, start: ValueBoxKey, end: ValueBoxKey) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    checkTableKey(table, start)
    checkTableKey(table, end)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.rangeValueAscStatementsNoLimit[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
            // with some doubt for the where clause here
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT key, value FROM t\(table.id) WHERE key > ? AND key < ? ORDER BY key
ASC", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.rangeValueAscStatementsNoLimit[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: start.memory, length: start.length)
            resultStatement.bind(2, data: end.memory, length: end.length)
        case .int64:
            resultStatement.bind(1, number: start.getInt64(0))
            resultStatement.bind(2, number: end.getInt64(0))
    }
    
    return resultStatement
}

private func rangeValueDescStatementLimit(_ table: ValueBoxTable, start: ValueBoxKey, end: ValueBoxKey, limit: Int) ->
SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    checkTableKey(table, start)
    checkTableKey(table, end)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.rangeValueDescStatementsLimit[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
            // with some doubt for the where clause here
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT key, value FROM t\(table.id) WHERE key > ? AND key < ? ORDER BY key
DESC LIMIT ?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.rangeValueDescStatementsLimit[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: start.memory, length: start.length)
            resultStatement.bind(2, data: end.memory, length: end.length)
        case .int64:
            resultStatement.bind(1, number: start.getInt64(0))
            resultStatement.bind(2, number: end.getInt64(0))
    }
    resultStatement.bind(3, number: Int32(limit))
    
    return resultStatement
}

private func rangeValueDescStatementNoLimit(_ table: ValueBoxTable, start: ValueBoxKey, end: ValueBoxKey) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    checkTableKey(table, start)
    checkTableKey(table, end)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.rangeValueDescStatementsNoLimit[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
      // with some doubt for the where clause here
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT key, value FROM t\(table.id) WHERE key > ? AND key < ? ORDER BY key
DESC", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.rangeValueDescStatementsNoLimit[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: start.memory, length: start.length)
            resultStatement.bind(2, data: end.memory, length: end.length)
        case .int64:
            resultStatement.bind(1, number: start.getInt64(0))
            resultStatement.bind(2, number: end.getInt64(0))
    }
    
    return resultStatement
}

private func scanStatement(_ table: ValueBoxTable) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.scanStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT key, value FROM t\(table.id) ORDER BY key ASC", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.scanStatements[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    return resultStatement
}

private func scanKeysStatement(_ table: ValueBoxTable) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.scanKeysStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT key FROM t\(table.id) ORDER BY key ASC", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.scanKeysStatements[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    return resultStatement
}

private func existsStatement(_ table: ValueBoxTable, key: ValueBoxKey) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    checkTableKey(table, key)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.existsStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT rowid FROM t\(table.id) WHERE key=?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.existsStatements[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: key.memory, length: key.length)
        case .int64:
            resultStatement.bind(1, number: key.getInt64(0))
    }
    
    return resultStatement
}

private func updateStatement(_ table: ValueBoxTable, key: ValueBoxKey, value: MemoryBuffer) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    checkTableKey(table, key)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.updateStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "UPDATE t\(table.id) SET value=? WHERE key=?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.updateStatements[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    resultStatement.bind(1, data: value.memory, length: value.length)
    switch table.keyType {
        case .binary:
            resultStatement.bind(2, data: key.memory, length: key.length)
        case .int64:
            resultStatement.bind(2, number: key.getInt64(0))
    }
    
    return resultStatement
}

private func insertOrReplaceStatement(_ table: SqliteValueBoxTable, key: ValueBoxKey, value: MemoryBuffer) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    checkTableKey(table.table, key)

    let resultStatement: SqlitePreparedStatement

    if table.table.keyType == .int64 || table.hasPrimaryKey {
        if let statement = self.insertOrReplacePrimaryKeyStatements[table.table.id] {
            resultStatement = statement
        } else {
            var statement: OpaquePointer? = nil
          // insert or update
            let status = sqlite3_prepare_v2(self.database.handle, "INSERT INTO t\(table.table.id) (key, value) VALUES(?, ?) ON CONFLICT(key) DO UPDATE SET value=excluded.value", -1, &statement, nil)
            if status != SQLITE_OK {
                let errorText = self.database.currentError() ?? "Unknown error"
                preconditionFailure(errorText)
            }
            let preparedStatement = SqlitePreparedStatement(statement: statement)
            self.insertOrReplacePrimaryKeyStatements[table.table.id] = preparedStatement
            resultStatement = preparedStatement
        }
    } else {
        if let statement = self.insertOrReplaceIndexKeyStatements[table.table.id] {
            resultStatement = statement
        } else {
            var statement: OpaquePointer? = nil
            let status = sqlite3_prepare_v2(self.database.handle, "INSERT INTO t\(table.table.id) (key, value) VALUES(?, ?)", -1, &statement, nil)
            if status != SQLITE_OK {
                let errorText = self.database.currentError() ?? "Unknown error"
                preconditionFailure(errorText)
            }
            let preparedStatement = SqlitePreparedStatement(statement: statement)
            self.insertOrReplacePrimaryKeyStatements[table.table.id] = preparedStatement
            resultStatement = preparedStatement
        }
    }

    resultStatement.reset()

    switch table.table.keyType {
        case .binary:
            resultStatement.bind(1, data: key.memory, length: key.length)
        case .int64:
            resultStatement.bind(1, number: key.getInt64(0))
    }
    if value.length == 0 {
        resultStatement.bindNull(2)
    } else {
        resultStatement.bind(2, data: value.memory, length: value.length)
    }

    return resultStatement
}

private func deleteStatement(_ table: ValueBoxTable, key: ValueBoxKey) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    checkTableKey(table, key)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.deleteStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "DELETE FROM t\(table.id) WHERE key=?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.deleteStatements[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: key.memory, length: key.length)
        case .int64:
            resultStatement.bind(1, number: key.getInt64(0))
    }
    
    return resultStatement
}

private func moveStatement(_ table: ValueBoxTable, from previousKey: ValueBoxKey, to updatedKey: ValueBoxKey) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    checkTableKey(table, previousKey)
    checkTableKey(table, updatedKey)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.moveStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "UPDATE t\(table.id) SET key=? WHERE key=?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.moveStatements[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
  // with some doubt in binding here
    switch table.keyType {
        case .binary:
            resultStatement.bind(1, data: previousKey.memory, length: previousKey.length)
            resultStatement.bind(2, data: updatedKey.memory, length: updatedKey.length)
        case .int64:
            resultStatement.bind(1, number: previousKey.getInt64(0))
            resultStatement.bind(2, number: updatedKey.getInt64(0))
    }
    
    return resultStatement
}

private func copyStatement(fromTable: ValueBoxTable, fromKey: ValueBoxKey, toTable: ValueBoxTable, toKey: ValueBoxKey) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    let _ = checkTable(fromTable)
    let _ = checkTable(toTable)
    checkTableKey(fromTable, fromKey)
    checkTableKey(toTable, toKey)
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.copyStatements[TablePairKey(table1: fromTable.id, table2: toTable.id)] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "INSERT INTO t\(toTable.id) (key, value) SELECT ?, t\(fromTable.id).value FROM t\(fromTable.id) WHERE
t\(fromTable.id).key=?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.copyStatements[TablePairKey(table1: fromTable.id, table2: toTable.id)] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    switch toTable.keyType {
        case .binary:
            resultStatement.bind(1, data: toKey.memory, length: toKey.length)
        case .int64:
            resultStatement.bind(1, number: toKey.getInt64(0))
    }
    
    switch fromTable.keyType {
        case .binary:
            resultStatement.bind(2, data: fromKey.memory, length: fromKey.length)
        case .int64:
            resultStatement.bind(2, number: fromKey.getInt64(0))
    }
    
    return resultStatement
}

private func fullTextInsertStatement(_ table: ValueBoxFullTextTable, collectionId: Data, itemId: Data, contents: Data, tags: Data) -> SqlitePreparedStatement {
    precondition(self.queue.isCurrent())
    
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.fullTextInsertStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "INSERT INTO ft\(table.id) (collectionId, itemId, contents, tags) VALUES(?, ?, ?, ?)", -1, &statement,
nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.fullTextInsertStatements[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    collectionId.withUnsafeBytes { rawBytes -> Void in
        let bytes = rawBytes.baseAddress!.assumingMemoryBound(to: UInt8.self)
        resultStatement.bindText(1, data: bytes, length: collectionId.count)
    }
    
    itemId.withUnsafeBytes { rawBytes -> Void in
        let bytes = rawBytes.baseAddress!.assumingMemoryBound(to: UInt8.self)
        resultStatement.bindText(2, data: bytes, length: itemId.count)
    }
    
    contents.withUnsafeBytes { rawBytes -> Void in
        let bytes = rawBytes.baseAddress!.assumingMemoryBound(to: UInt8.self)
        resultStatement.bindText(3, data: bytes, length: contents.count)
    }
    
    tags.withUnsafeBytes { rawBytes -> Void in
        let bytes = rawBytes.baseAddress!.assumingMemoryBound(to: UInt8.self)
        resultStatement.bindText(4, data: bytes, length: tags.count)
    }
    return resultStatement
}

private func fullTextDeleteStatement(_ table: ValueBoxFullTextTable, itemId: Data) -> SqlitePreparedStatement {
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.fullTextDeleteStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "DELETE FROM ft\(table.id) WHERE itemId=?", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.fullTextDeleteStatements[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    itemId.withUnsafeBytes { rawBytes -> Void in
        let bytes = rawBytes.baseAddress!.assumingMemoryBound(to: UInt8.self)
        resultStatement.bindText(1, data: bytes, length: itemId.count)
    }
    
    return resultStatement
}

private func fullTextMatchGlobalStatement(_ table: ValueBoxFullTextTable, contents: Data) -> SqlitePreparedStatement {
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.fullTextMatchGlobalStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
      // the `MATCH` clause
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT collectionId, itemId FROM ft\(table.id) WHERE ft\(table.id) MATCH 'contents:\"' || ? || '\"'",
-1, &statement, nil)
        if status != SQLITE_OK {
            self.printError()
            assertionFailure()
        }
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.fullTextMatchGlobalStatements[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    contents.withUnsafeBytes { rawBytes -> Void in
        let bytes = rawBytes.baseAddress!.assumingMemoryBound(to: UInt8.self)
        resultStatement.bindText(1, data: bytes, length: contents.count)
    }
    
    return resultStatement
}

private func fullTextMatchCollectionStatement(_ table: ValueBoxFullTextTable, collectionId: Data, contents: Data) -> SqlitePreparedStatement {
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.fullTextMatchCollectionStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT collectionId, itemId FROM ft\(table.id) WHERE ft\(table.id) MATCH 'contents:\"' || ? || '\" AND
collectionId:\"' || ? || '\"'", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.fullTextMatchCollectionStatements[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    contents.withUnsafeBytes { rawBytes -> Void in
        let bytes = rawBytes.baseAddress!.assumingMemoryBound(to: UInt8.self)
        resultStatement.bindText(1, data: bytes, length: contents.count)
    }
    
    collectionId.withUnsafeBytes { rawBytes -> Void in
        let bytes = rawBytes.baseAddress!.assumingMemoryBound(to: UInt8.self)
        resultStatement.bindText(2, data: bytes, length: collectionId.count)
    }
    
    return resultStatement
}

private func fullTextMatchCollectionTagsStatement(_ table: ValueBoxFullTextTable, collectionId: Data, contents: Data, tags: Data) -> SqlitePreparedStatement {
    let resultStatement: SqlitePreparedStatement
    
    if let statement = self.fullTextMatchCollectionTagsStatements[table.id] {
        resultStatement = statement
    } else {
        var statement: OpaquePointer? = nil
        let status = sqlite3_prepare_v2(self.database.handle, "SELECT collectionId, itemId FROM ft\(table.id) WHERE ft\(table.id) MATCH 'contents:\"' || ? || '\" AND
collectionId:\"' || ? || '\" AND tags:\"' || ? || '\"'", -1, &statement, nil)
        precondition(status == SQLITE_OK)
        let preparedStatement = SqlitePreparedStatement(statement: statement)
        self.fullTextMatchCollectionTagsStatements[table.id] = preparedStatement
        resultStatement = preparedStatement
    }
    
    resultStatement.reset()
    
    contents.withUnsafeBytes { rawBytes -> Void in
        let bytes = rawBytes.baseAddress!.assumingMemoryBound(to: UInt8.self)
        resultStatement.bindText(1, data: bytes, length: contents.count)
    }
    
    collectionId.withUnsafeBytes { rawBytes -> Void in
        let bytes = rawBytes.baseAddress!.assumingMemoryBound(to: UInt8.self)
        resultStatement.bindText(2, data: bytes, length: collectionId.count)
    }
    
    tags.withUnsafeBytes { rawBytes -> Void in
        let bytes = rawBytes.baseAddress!.assumingMemoryBound(to: UInt8.self)
        resultStatement.bindText(3, data: bytes, length: tags.count)
    }
    
    return resultStatement
}

public func get(_ table: ValueBoxTable, key: ValueBoxKey) -> ReadBuffer? {
    precondition(self.queue.isCurrent())
    if let _ = self.tables[table.id] {
        let statement = self.getStatement(table, key: key)
        
        var buffer: ReadBuffer?
        
        while statement.step(handle: self.database.handle, pathToRemoveOnError: self.removeDatabaseOnError ? self.databasePath : nil) {
            buffer = statement.valueAt(0)
            break
        }
        
        statement.reset()
        
        return buffer
    }
    
    withExtendedLifetime(key, {})
    
    return nil
}