ADSQLiteProvider

open class ADSQLiteProvider: ADDataProvider

The ADSQLiteProvider provides both light-weight, low-level access to data stored in a SQLite database and high-level access via a Object Relationship Management (ORM) model. Use provided functions to read and write data stored in a ADRecord format from and to the database using SQL statements directly.

Example:

let provider = ADSQLiteProvider.shared
let record = try provider.query("SELECT * FROM Categories WHERE id = ?", withParameters: [1])
print(record["name"])

Optionally, pass a class instance conforming to the ADDataTable protocol to the ADSQLiteProvider and it will automatically handle reading, writing and deleting data as required.

Example:

let addr1 = Address(addr1: "PO Box 1234", addr2: "", city: "Houston", state: "TX", zip: "77012")
let addr2 = Address(addr1: "25 Nasa Rd 1", addr2: "Apt #123", city: "Seabrook", state: "TX", zip: "77586")

let p1 = Person(firstName: "John", lastName: "Doe", addresses: ["home":addr1, "work":addr2])
let p2 = Person(firstName: "Sue", lastName: "Smith", addresses: ["home":addr1, "work":addr2])

let group = Group(name: "Employees", people: [p1, p2])
try provider.save(group)

Remark

The ADSQLiteProvider will automatically create a SQL Table from a class instance if one does not already exist. In addition, ADSQLiteProvider contains routines to preregister or update the schema classes conforming to the ADDataTable protocol which will build or modify the database tables as required.
  • Provides access to a common, shared instance of the ADSQLiteProvider. For app’s that are working with a single SQLite database, they can use this instance instead of creating their own instance of a ADSQLiteProvider.

    Declaration

    Swift

    public static let shared = ADSQLiteProvider()
  • Reference to the currently open database path or “” if no database is currently open.

    Declaration

    Swift

    public private(set) var path: String = ""
  • The name of the currently open SQLite database or “” if no database is currently open.

    Declaration

    Swift

    public private(set) var databaseName = ""
  • Gets the current user-defined version number for the database. This value can be useful in managing data migrations so that you can add new columns to your tables or massage your existing data to suit a new situation.

    Declaration

    Swift

    public var databaseVersion: Int
  • Returns true if a SQLite database currently open in the data provider, else returns false.

    Declaration

    Swift

    public var isOpen: Bool
  • Returns true if the data provider can write to the currently open SQLite database, else returns false.

    Declaration

    Swift

    public private(set) var isReadOnly: Bool = false
  • An array of all tables that have been read from or written to the given data source.

    Declaration

    Swift

    public var knownTables: [String] = []
  • Initializes a new instance of a ADSQLiteProvider.

    Declaration

    Swift

    public init()
  • Removes the given table from the list of known table names.

    Example:

    ADSQLiteProvider.shared.forgetTable("Category)
    

    Declaration

    Swift

    public func forgetTable(_ name: String)

    Parameters

    name

    The name of the table to forget.

  • Opens the given SQLite database file for the data provider from either the app’s Document or Bundle directories. If opening a database from the Document directory and it does not exist, the database will automatically be created. If opening a database from the Bundle directory for write access, the database will first be copied to the Document directory (if a copy doesn’t already exist there), and the Document directory copy of the database will be opened.

    Example:

    try ADSQLiteProvider.shared.openSource("MyDatabase.db")
    

    Declaration

    Swift

    public func openSource(_ fileName: String, fromBundle: Bool = false, readOnly: Bool = false) throws

    Parameters

    fileName

    The name of the SQLite database file to open.

    fromBundle

    If true, open the file from the app’s Bundle.

    readOnly

    If true the data provider cannot write to the database.

  • Creates the given SQLite database file for the data provider in the app’s Document directory. If the database file already exists, it will be opened instead.

    Example:

    try ADSQLiteProvider.shared.createSource("MyDatabase.db")
    

    Declaration

    Swift

    public func createSource(_ filename: String) throws

    Parameters

    fileName

    The name of the SQLite database file to create.

  • Closes the currently open SQLite database, copies it to a new filename and reopens the database under the new name.

    Declaration

    Swift

    public func saveSource(_ filename: String) throws

    Parameters

    filename

    The name of the new database file.

  • The persist function is used to write in-memory Data Provider content to persistant data storage. This command has no affect on a SQLite database.

    ## Example:

     ADSQLiteProvider.shared.persist()
    

    Declaration

    Swift

    public func persist() throws
  • Close the currently open SQLite database. Before closing the database, the framework automatically takes care of database optimization at frequent intervals by running the following commands:

    1. VACUUM - Repack the database to take advantage of deleted data.
    2. ANALYZE - Gather information about the tables and indices so that the query optimizer can use the information to make queries work better.

    Example:

    ADSQLiteProvider.shared.closeSource()
    

    Declaration

    Swift

    public func closeSource() throws
  • For writable databases stored in the app’s document directory, delete the data source with the specified file name.

    Example:

    ADSQLiteProvider.deleteSource("MyDatabase.db")
    

    Warning

    This command will totally erase the database from the device’s storage and is not undoable!

    Declaration

    Swift

    public func deleteSource(_ fileName: String) throws

    Parameters

    fileName

    The name of the SQLite database to delete.

  • Execute SQL (non-query) command with (optional) parameters and return result code.

    Example:

    let sql = "CREATE TABLE IF NOT EXISTS Person (`ID` INTEGER, `Name` STRING)"
    try ADSQLiteProvider.shared.execute(sql)
    

    Declaration

    Swift

    @discardableResult public func execute(_ sql: String, withParameters parameters: [Any]? = nil) throws -> Int

    Parameters

    sql

    The SQL statement to be executed.

    parameters

    An array of optional parameters incase the SQL statement includes bound parameters (indicated by ? in the SQL Statement).

    Return Value

    If executing an INSERT command of a record with an INTEGER id, the last inserted ID will be returned. For DELETE and UPDATE commands, a count of number of records modified will be returned. All other commands will return 1 on success and -1 on failure.

  • Run an SQL query with (parameters) parameters and returns an array of dictionaries where the keys are the column names.

    Example:

    let sql = "SELECT * FROM Person WHERE ID = ?"
    let records = try ADSQLiteProvider.shared.query(sql, withParameters: [1])
    

    Declaration

    Swift

    public func query(_ sql: String, withParameters parameters: [Any]? = nil) throws -> ADRecordSet

    Parameters

    sql

    The SQL statement to be executed.

    parameters

    An array of optional parameters incase the SQL statement includes bound parameters (indicated by ? in the SQL Statement).

    Return Value

    An empty array if the query resulted in no rows. Otherwise, an array of dictionaries where each dictioanry key is a column name and the value is the column value as a ADRecordSet.

  • Checks to see if the given table exists in the SQLite database.

    Example:

    let exists = try ADSQLiteProvider.shared.tableExists("Person)
    

    Declaration

    Swift

    public func tableExists(_ tableName: String) throws -> Bool

    Parameters

    tableName

    The name the table to check.

    Return Value

    true if the table exists, else false.

  • Counts the number of records in a given SQLite database table, optionally filtered by a given set of contraints.

    Example:

    let count = try ADSQLiteProvider.shared.countRows(inTable: "Person", filteredBy: "ID = ?", withParameters: [1])
    

    Declaration

    Swift

    public func countRows(inTable table: String, filteredBy filter: String = "", withParameters parameters: [Any]? = nil) throws -> Int

    Parameters

    table

    The name of the table to count records for.

    filter

    The optional filter criteria to be used in fetching the data. Specify the filter criteria in the form of a valid SQLite WHERE clause (without the actual WHERE keyword). If this parameter is omitted or a blank string is provided, all rows will be fetched.

    parameters

    An array of optional parameters incase the SQL statement includes bound parameters (indicated by ? in the SQL Statement).

    Return Value

    An integer value indicating the total number of rows, if no filter criteria was provided, or the number of rows matching the provided filter criteria. If the table doesn’t exist, 0 is returned.

  • Gets the largest used number for the given integer primary key of the given table.

    Example:

    let lastID = try ADSQLiteProvider.shared.lastIntID(forTable: "Person", withKey: "ID")
    

    Remark

    This function works with integer primary keys that are not marked AUTOINCREMENT and is useful when the data being stored in a database needs to know the next available ID before a record has been saved.

    Declaration

    Swift

    public func lastIntID(forTable table: String, withKey primaryKey: String) throws -> Int

    Parameters

    table

    The name of the table to get the last ID from.

    primaryKey

    The name of the primary key.

    Return Value

    The largest used number for the given primary key or zero if no record has been written to the table yet.

  • Gets the last auto generated integer ID for the given table.

    Example:

    let lastID = ADSQLiteProvider.shared.lastAutoID(forTable: "Category")
    

    Remark

    This function works with AUTOINCREMENT primary key types and returns the last ID generated after data has been saved to the database.

    Declaration

    Swift

    public func lastAutoID(forTable table: String) throws -> Int

    Parameters

    table

    The name of the table to get the last ID from.

    Return Value

    The last auto generated integer id or zero if no data has been saved to the table or if the table does not have an auto generated integer primary key.

  • Returns all information about a given table in the SQLite database including all of the columns and their types.

    Example:

    let schema = try ADSQLiteProvider.shared.getTableSchema(forTableName: "Category")
    

    Declaration

    Swift

    public func getTableSchema(forTableName name: String) throws -> ADTableSchema

    Parameters

    name

    The name of the table to return the schema for.

    Return Value

    A ADTableSchema instance describing the requested table.

  • Starts an explicit transaction to process a batch of database changes. Once started, the transaction will remain open until it is either committed (via endTransaction) or rolled-back (via `rollbackTransaction).

    Declaration

    Swift

    public func beginTransaction() throws
  • Attempts to commit any chages to the database and close the current transaction that was opened using beginTransaction.

    Declaration

    Swift

    public func endTransaction() throws
  • Ends the current transaction (opened using beginTransaction) and undoes any changes made to the database since the transaction was opened.

    Declaration

    Swift

    public func rollbackTransaction() throws
  • Registers the given ADDataTable class schema with the data provider and creates a table for the class if it doesn’t already exist.

    Example:

    try ADSQLiteProvider.shared.registerTableSchema(Category.self)
    

    Remark

    Classes are usually registered when an app first starts, directly after a database is opened.

    Declaration

    Swift

    public func registerTableSchema<T: ADDataTable>(_ type: T.Type, withDefaultValues instance: T = T.init()) throws

    Parameters

    type

    The type of the class to register.

    instance

    An instance of the type with all properties set to the default values that you want to have added to the data source.

  • Attempts to modify the SQLite database table schema to match the schema of the given ADDataTable class if the schema has changed. If the table does not exist, it will attempt to be registered with the database. If any new columns have been added, the default values will be set from the given defaults.

    Example:

    try ADSQLiteProvider.shared.updateTableSchema(Category.self)
    

    Declaration

    Swift

    public func updateTableSchema<T: ADDataTable>(_ type: T.Type, withDefaultValues instance: T = T.init()) throws

    Parameters

    type

    The type of the class to update the schema of.

    instance

    An instance of the type with all properties set to the default values that you want to have added to the database.

  • Checks to see if a row matching the given primary key exists in the underlying SQLite table.

    Example:

    let found = try ADSQLiteProvider.shared.hasRow(forType: Person.self, matchingPrimaryKey: 1)
    

    Declaration

    Swift

    public func hasRow<T: ADDataTable>(forType type: T.Type, matchingPrimaryKey key: Any) throws -> Bool

    Parameters

    type

    The ADDataTable class to check if the row exists for.

    key

    The primary key value to search for.

    Return Value

    true if a row matching the primary key is found, else false.

  • Return the count of rows in the table, or the count of rows matching a specific filter criteria, if one was provided.

    Example:

    let count = try ADSQLiteProvider.shared.rowCount(forType: Person.self)
    

    Declaration

    Swift

    public func rowCount<T: ADDataTable>(forType type: T.Type, filterBy filter: String = "", withParameters parameters: [Any]? = nil) throws -> Int

    Parameters

    type

    A class conforming to the ADDataTable protocol to count rows for.

    filter

    The optional filter criteria to be used in fetching the data. Specify the filter criteria in the form of a valid SQLite WHERE clause (without the actual WHERE keyword). If this parameter is omitted or a blank string is provided, all rows will be fetched.

    parameters

    An array of optional parameters incase the SQL statement includes bound parameters (indicated by ? in the SQL Statement).

    Return Value

    An integer value indicating the total number of rows or rows matching the optional filter in the given table.

  • Creates an instance of the given ADDataTable class automatically setting the primaryKey field based on the value of the primaryKeyType.

    Example:

    var category = try ADSQLiteProvider.shared.make(Category.self)
    

    Declaration

    Swift

    public func make <T: ADDataTable>(_ type: T.Type) throws -> T

    Parameters

    type

    The class conforming to the ADDataTable protocol to create an instance of.

    Return Value

    A new instance of the given class with the primaryKey automatically set.

  • Returns a value for the primaryKey field based on the value of the primaryKeyType for a class conforming to the ADDataTable protocol.

    Example:

    let id = ADSQLiteProvider.shared.makeID(Category.self) as! Int
    

    Declaration

    Swift

    public func makeID<T: ADDataTable>(_ type: T.Type) -> Any?

    Parameters

    type

    The class conforming to the ADDataTable protocol to create primary key for.

    Return Value

    A new primary key value if it can be generated, else returns nil.

  • Saves the given class conforming to the ADDataTable protocol to the database. If the SQLite database does not contain a table named in the tableName property, one will be created first. If a record is not on file matching the primaryKey value, a new record will be created, else the existing record will be updated.

    Example:

    var category = Category()
    try ADSQLiteProvider.shared.save(category)
    

    Declaration

    Swift

    @discardableResult public func save<T: ADDataTable>(_ value: T) throws -> Any

    Parameters

    value

    The class instance to save to the database.

    Return Value

    If inserting a record with an INTEGER id, the last inserted ID will be returned, else the primary key value will be returned.

  • Saves the given array of class instances conforming to the ADDataTable protocol to the database. If the SQLite database does not contain a table named in the tableName property, one will be created first. If a record is not on file matching the primaryKey value, a new record will be created, else the existing record will be updated.

    Example:

    let c1 = Category()
    let c2 = Category()
    try ADSQLiteProvider.shared.save([c1, c2])
    

    Remark

    Uses a transaction to process all database changes in a single batch. If an error occurs, all changes will be rolled-back and the database will not be modified.

    Declaration

    Swift

    public func save<T: ADDataTable>(_ values: [T]) throws

    Parameters

    values

    The array of class instances to save to the database.

  • Returns rows from the SQLite database of the given class type optionally filtered, sorted and limited to a specific range of results.

    Example:

    let records = try ADSQLiteProvider.shared.getRows(ofType: Person.self)
    

    Declaration

    Swift

    public func getRows<T: ADDataTable>(ofType type: T.Type, fliteredBy filter: String = "", orderedBy order: String = "", startingAt start: Int = 0, limitedTo limit: Int = 0, withParameters parameters: [Any]? = nil) throws -> [T]

    Parameters

    type

    A class conforming to the ADDataTable protocol to store the records in.

    filter

    The optional filter criteria to be used in fetching the data. Specify in the form of a valid SQL WHERE clause (without the actual WHERE keyword). If this parameter is omitted or a blank string is provided, all rows will be fetched.

    order

    The optional sorting criteria to be used in fetching the data. Specify in the form of a valid SQL ORDER BY clause (without the actual ORDER BY keyword). If this parameter is omitted or a blank string is provided, no sorting will be applied.

    start

    The starting index for the returned results. If omitted or zero, the result set starts with the first record.

    limit

    Limits the returned results to a maximum number. If omitted or zero, all matching results are returned.

    parameters

    An array of optional parameters incase the SQL statement includes bound parameters (indicated by ? in the SQL Statement).

    Return Value

    An array of matching records in the given class type or an empty array if no matching records were found.

  • Returns rows from the SQLite database of the given class type matching the given SQL statement.

    Example:

    let sql = "SELECT * FROM Person WHERE ID = ?"
    let records = try ADSQLiteProvider.shared.getRows(ofType: Person.self, matchingSQL: sql, withParameters: [1])
    

    Declaration

    Swift

    public func getRows<T: ADDataTable>(ofType type: T.Type, matchingSQL sql: String, withParameters parameters: [Any]? = nil) throws -> [T]

    Parameters

    type

    A class conforming to the ADDataTable protocol to store the records in.

    sql

    A valid SQL statement used to pull matching records from the database.

    parameters

    An array of optional parameters incase the SQL statement includes bound parameters (indicated by ? in the SQL Statement).

    Return Value

    An array of matching records in the given class type or an empty array if no matching records were found.

  • Returns a row from the SQLite database of the given class type matching the given primary key value.

    Example:

    let person = try ADSQLiteProvider.shared.getRow(ofType: Person.self, forPrimaryKeyValue: 1)
    

    Declaration

    Swift

    public func getRow<T: ADDataTable>(ofType type: T.Type, forPrimaryKeyValue key: Any) throws -> T?

    Parameters

    type

    A class conforming to the ADDataTable protocol to store the records in.

    key

    The primary key value to return a record for.

    Return Value

    A record of the requested type if found or nil if not found.

  • Returns a row from the SQLite database of the given class type optionally filtered and limited to a specific range of results.

    Example:

    let category = try ADSQLiteProvider.shared.getRow(ofType: Category.self, atIndex: 10)
    

    Declaration

    Swift

    public func getRow<T: ADDataTable>(ofType type: T.Type, atIndex index: Int, fliteredBy filter: String = "", orderedBy order: String = "", withParameters parameters: [Any]? = nil) throws -> T?

    Parameters

    type

    A class conforming to the ADDataTable protocol to store the records in.

    index

    The starting index of the record to return.

    filter

    The optional filter criteria to be used in fetching the data. Specify in the form of a valid SQL WHERE clause (without the actual WHERE keyword). If this parameter is omitted or a blank string is provided, all rows will be fetched.

    order

    The optional sorting criteria to be used in fetching the data. Specify in the form of a valid SQL ORDER BY clause (without the actual ORDER BY keyword). If this parameter is omitted or a blank string is provided, no sorting will be applied.

    parameters

    An array of optional parameters incase the SQL statement includes bound parameters (indicated by ? in the SQL Statement).

    Return Value

    A record of the requested type if found or nil if not found.

  • Deletes the row matching the given record from the SQLite database.

    Example:

    let category = try ADSQLiteProvider.shared.getRow(ofType: Category.self, forPrimaryKeyValue: 10)
    try ADSQLiteProvider.shared.delete(category)
    

    Declaration

    Swift

    public func delete<T: ADDataTable>(_ value: T) throws

    Parameters

    value

    An instance of a class conforming to the ADDataTable protocol to delete from the database.

  • Deletes the given set of records from the database.

    Example:

    let c1 = try ADSQLiteProvider.shared.getRow(ofType: Category.self, forPrimaryKeyValue: 10)
    let c2 = try ADSQLiteProvider.shared.getRow(ofType: Category.self, forPrimaryKeyValue: 5)
    try ADSQLiteProvider.shared.delete([c1, c2])
    

    Remark

    Uses a transaction to process all data source changes in a single batch. If an error occurs, all changes will be rolled-back and the data source will not be modified.

    Declaration

    Swift

    public func delete<T: ADDataTable>(_ values: [T]) throws
  • Drops the underlying table from the SQLite database, completely removing all stored data in the table as well as the table itself.

    Example:

    try ADSQLiteProvider.shared.dropTable(Category.self)
    

    Warning: This command is not undable and should be used with caution!

    Declaration

    Swift

    public func dropTable<T: ADDataTable>(_ type: T.Type) throws