DatabaseReader

DatabaseReader is the protocol for all types that can fetch values from an SQLite database.

It is adopted by DatabaseQueue, DatabasePool, and DatabaseSnapshot.

The protocol comes with isolation guarantees that describe the behavior of adopting types in a multithreaded application.

Types that adopt the protocol can provide in practice stronger guarantees. For example, DatabaseQueue provides a stronger isolation level than DatabasePool.

Warning: Isolation guarantees stand as long as there is no external connection to the database. Should you have to cope with external connections, protect yourself with transactions, and be ready to setup a busy handler.

  • The database configuration

  • Closes the database connection with the sqlite3_close() function.

    Note: You DO NOT HAVE to call this method, and you SHOULD NOT call it unless the correct execution of your program depends on precise database closing. Database connections are automatically closed when they are deinitialized, and this is sufficient for most applications.

    If this method does not throw, then the database is properly closed, and every future database access will throw a DatabaseError of code SQLITE_MISUSE.

    Otherwise, there exists concurrent database accesses or living prepared statements that prevent the database from closing, and this method throws a DatabaseError of code SQLITE_BUSY. See https://www.sqlite.org/c3ref/close.html for more information.

    After an error has been thrown, the database may still be opened, and you can keep on accessing it. It may also remain in a “zombie” state, in which case it will throw SQLITE_MISUSE for all future database accesses.

Interrupting Database Operations

  • This method causes any pending database operation to abort and return at its earliest opportunity.

    It can be called from any thread.

    A call to interrupt() that occurs when there are no running SQL statements is a no-op and has no effect on SQL statements that are started after interrupt() returns.

    A database operation that is interrupted will throw a DatabaseError with code SQLITE_INTERRUPT. If the interrupted SQL operation is an INSERT, UPDATE, or DELETE that is inside an explicit transaction, then the entire transaction will be rolled back automatically. If the rolled back transaction was started by a transaction-wrapping method such as DatabaseWriter.write or Database.inTransaction, then all database accesses will throw a DatabaseError with code SQLITE_ABORT until the wrapping method returns.

    For example:

    try dbQueue.write { db in
        // interrupted:
        try Player(...).insert(db)     // throws SQLITE_INTERRUPT
        // not executed:
        try Player(...).insert(db)
    }                                  // throws SQLITE_INTERRUPT
    
    try dbQueue.write { db in
        do {
            // interrupted:
            try Player(...).insert(db) // throws SQLITE_INTERRUPT
        } catch { }
        try Player(...).insert(db)     // throws SQLITE_ABORT
    }                                  // throws SQLITE_ABORT
    
    try dbQueue.write { db in
        do {
            // interrupted:
            try Player(...).insert(db) // throws SQLITE_INTERRUPT
        } catch { }
    }                                  // throws SQLITE_ABORT
    

    When an application creates transaction without a transaction-wrapping method, no SQLITE_ABORT error warns of aborted transactions:

    try dbQueue.inDatabase { db in // or dbPool.writeWithoutTransaction
        try db.beginTransaction()
        do {
            // interrupted:
            try Player(...).insert(db) // throws SQLITE_INTERRUPT
        } catch { }
        try Player(...).insert(db)     // success
        try db.commit()                // throws SQLITE_ERROR "cannot commit - no transaction is active"
    }
    

    Both SQLITE_ABORT and SQLITE_INTERRUPT errors can be checked with the DatabaseError.isInterruptionError property.

Read From Database

  • Synchronously executes a read-only function that accepts a database connection, and returns its result.

    For example:

    let count = try reader.read { db in
        try Player.fetchCount(db)
    }
    

    The value function runs in an isolated fashion: eventual concurrent database updates are not visible from the function:

    try reader.read { db in
        // Those two values are guaranteed to be equal, even if the
        // `player` table is modified, between the two requests, by
        // some other database connection or some other thread.
        let count1 = try Player.fetchCount(db)
        let count2 = try Player.fetchCount(db)
    }
    
    try reader.read { db in
        // Now this value may be different:
        let count = try Player.fetchCount(db)
    }
    

    Attempts to write in the database throw a DatabaseError with resultCode SQLITE_READONLY.

    It is a programmer error to call this method from another database access method:

    try reader.read { db in
        // Raises a fatal error
        try reader.read { ... )
    }
    

    Throws

    The error thrown by value, or any DatabaseError that would happen while establishing the read access to the database.
  • Asynchronously executes a read-only function that accepts a database connection.

    The value function runs in an isolated fashion: eventual concurrent database updates are not visible from the function:

    reader.asyncRead { dbResult in
        do {
            let db = try dbResult.get()
            // Those two values are guaranteed to be equal, even if the
            // `player` table is modified, between the two requests, by
            // some other database connection or some other thread.
            let count1 = try Player.fetchCount(db)
            let count2 = try Player.fetchCount(db)
        } catch {
            // handle error
        }
    }
    

    Attempts to write in the database throw a DatabaseError with resultCode SQLITE_READONLY.

  • Synchronously executes a function that accepts a database connection, and returns its result.

    For example:

    let count = try reader.unsafeRead { db in
        try Player.fetchCount(db)
    }
    

    The guarantees of the read method are lifted:

    the value function is not isolated: eventual concurrent database updates are visible from the function:

    try reader.unsafeRead { db in
        // Those two values can be different, because some other
        // database connection or some other thread may modify the
        // database between the two requests.
        let count1 = try Player.fetchCount(db)
        let count2 = try Player.fetchCount(db)
    }
    

    The value function is not prevented from writing (DatabaseQueue, in particular, will accept database modifications in unsafeRead).

    It is a programmer error to call this method from another database access method:

    try reader.read { db in
        // Raises a fatal error
        try reader.unsafeRead { ... )
    }
    

    Throws

    The error thrown by value, or any DatabaseError that would happen while establishing the read access to the database.
  • Asynchronously executes a function that accepts a database connection.

    The guarantees of the asyncRead method are lifted:

    the value function is not isolated: eventual concurrent database updates are visible from the function:

    reader.asyncUnsafeRead { dbResult in
        do {
            let db = try dbResult.get()
            // Those two values can be different, because some other
            // database connection or some other thread may modify the
            // database between the two requests.
            let count1 = try Player.fetchCount(db)
            let count2 = try Player.fetchCount(db)
        } catch {
            // handle error
        }
    }
    

    The value function is not prevented from writing (DatabaseQueue, in particular, will accept database modifications in asyncUnsafeRead).

  • Synchronously executes a function that accepts a database connection, and returns its result.

    The guarantees of the safe read method are lifted:

    the value function is not isolated: eventual concurrent database updates are visible from the function:

    try reader.unsafeReentrantRead { db in
        // Those two values can be different, because some other
        // database connection or some other thread may modify the
        // database between the two requests.
        let count1 = try Player.fetchCount(db)
        let count2 = try Player.fetchCount(db)
    }
    

    The value function is not prevented from writing (DatabaseQueue, in particular, will accept database modifications in unsafeRead).

    This method is reentrant. It should be avoided because it fosters dangerous concurrency practices.

    Throws

    The error thrown by value, or any DatabaseError that would happen while establishing the read access to the database.

Backup

  • Copies the database contents into another database.

    The backup method blocks the current thread until the destination database contains the same contents as the source database.

    When the source is a DatabasePool, concurrent writes can happen during the backup. Those writes may, or may not, be reflected in the backup, but they won’t trigger any error.

    Usage:

    let source: DatabaseQueue = ...
    let destination: DatabaseQueue = ...
    try source.backup(to: destination)
    

    When you’re after progress reporting during backup, you’ll want to perform the backup in several steps. Each step copies the number of database pages you specify. See https://www.sqlite.org/c3ref/backup_finish.html for more information:

    // Backup with progress reporting
    try source.backup(
        to: destination,
        pagesPerStep: ...)
        { backupProgress in
           print("Database backup progress:", backupProgress)
        }
    

    The progress callback will be called at least once—when backupProgress.isCompleted == true. If the callback throws when backupProgress.isCompleted == false, the backup is aborted and the error is rethrown. If the callback throws when backupProgress.isCompleted == true, backup completion is unaffected and the error is silently ignored.

    See also Database.backup().

    Throws

    The error thrown by progress if the backup is abandoned, or any DatabaseError that would happen while performing the backup.