Swift APIs for SQLite: Type-safe down to the schema. Very, very, fast. Dependency free.

While testing the fix of #8, using develop branch (works) I noticed the following:

I added a database creation script Li_Talents.sql but forgot to exclude it from the project.

This created the following code within the struct LiTalents:

  public static let module : LiTalents! = {
    #if SWIFT_PACKAGE
    let bundle = Bundle.module
    #else
    final class Helper {}
    let bundle = Bundle(for: Helper.self)
    #endif
    if let url = bundle.url(forResource: "Li_Talents", withExtension: "sql") {
      return LiTalents(url: url, readOnly: true)
    }
    else {
      fatalError(#"Missing db resource Li_Talents.sql, not copied?"#)
    }
  }()

Notice how this tries to use the URL to the SQL file when constructing the instance. Using a plain SQL file results in an assertion failure when using the instance:

print(try? await LiTalents.module?.liLiTalents.fetchCount())
// → 2022-08-24 13:33:50.896955+0200 XXX[37374:5177759] [logging] file is not a database in "SELECT COUNT(*) FROM "LiLiTalents""
// → Lighter/SQLDatabaseOperations.swift:56: Fatal error: Failed to prepare SQL SELECT COUNT(*) FROM "LiLiTalents"

The module static is not generated if the SQL file is not included in the project.

I tried to use Lighter plugin for a SQL that uses a UUID column type:

CREATE TABLE Talents (
    id              UUID PRIMARY KEY NOT NULL,
    name         TEXT NOT NULL
    -- …
);

The following code is generated for withOptUUIDBytes:

  public static func withOptUUIDBytes<R>(
    _ uuid: UUID?,
    _ body: ( UnsafeRawBufferPointer ) throws -> R
  ) rethrows -> R
  {
    if let uuid = uuid { return try withUnsafeBytes(of: &uuid, body) }
    else { return try body(UnsafeRawBufferPointer(start: nil, count: 0)) }
  }

It fails to compile with: Cannot pass immutable value as inout argument: 'uuid' is a 'let' constant on the call to withUnsafeBytes.

(Using Xcode “Version 14.0 beta 5 (14A5294e)” if this matters, but I do not think so.)

Possible fix:

    if case .some(var uuid) = uuid { return try withUnsafeBytes(of: &uuid, body) }

Lighter currently considers all int pkeys as database generated, but that is only true if they are spelled explicitly as INTEGER (case doesn't matter). I.e. just id INT PRIMARY KEY doesn't actually work. We should replicate the SQLite behaviour and only consider a property as autogenerated if it matches the requirements.

This probably warrants a new type or marker in SQLite3Schema.

• https://www.sqlite.org/lang_createtable.html#rowid
• https://www.sqlite.org/autoinc.html

When generating code for this SQL:

CREATE TABLE Talents (
    id   UUID PRIMARY KEY NOT NULL,
    name TEXT NOT NULL
);

The DocC comment for the records is this (notice the "has default"):

public struct Talents : Identifiable, SQLKeyedTableRecord, Codable {
  
  /// Static SQL type information for the ``Talents`` record.
  public static let schema = Schema()
  
  /// Primary key `id` (`UUID`), required (has default).
  public var id : UUID
  
  /// Column `name` (`TEXT`), required (has default).
  public var name : String
}

Neither id nor name has a database default though:

sqlite> PRAGMA table_info(Talents);
cid  name  type  notnull  dflt_value  pk
---  ----  ----  -------  ----------  --
0    id    UUID  1                    1 
1    name  TEXT  1                    0 

Though they get default values assigned in Swift:

    public let id = MappedColumn<Talents, UUID>(
      externalName: "id",
      defaultValue: UUID(uuid: ( 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 )),
      keyPath: \Talents.id
    )
    public let name = MappedColumn<Talents, String>(
      externalName: "name",
      defaultValue: "",
      keyPath: \Talents.name
    )

When generating a DB for this:

CREATE TABLE Talents (
    id   UUID PRIMARY KEY NOT NULL,
    name TEXT NOT NULL
);

The result is mostly OK, but ends up with those SQLite API functions:

public func sqlite3_talents0_fetch(...)

Notice the talents0, it is probably generated by the code which makes the identifiers unique. The CUD functions are fine though:

public func sqlite3_talents_delete(...)

Inserts for integer primary keys use SQLite to generate the actual key, e.g.

CREATE TABLE person (
  person_id INTEGER PRIMARY KEY NOT NULL,
  name TEXT
);

This is generated into:

struct Person {
  var id : Int
  var name : String?
  init(id: Int, name: String? = nil) {...}
}

Which is a little inconvenient for inserts:

var person = Person(id: 0, name: "Donald")
person = try db.insert(person)

(the return value of the insert will have copy of the record with the proper db assigned primary key).

Maybe this should be some, ideally unlikely, obscure default value, like:

init(id: Int = MyDB.defaultIntPrimaryKey, name: String? = nil) { ... }
extension MyDB {
  static let MyDB.defaultIntPrimaryKey : Int = -0xDEADBEEF
}

Not quite sure whether that is actually good or not, maybe it is OK as an optional generation option.

Let's say we have:

CREATE TABLE talent (
  talent_id UUID NOT NULL,
  name      TEXT NOT NULL
);

which generates

struct Talent {
  var id   : UUID
  var name : String
  init(id: UUID, name: String) { ... }
}

it would be nice if the init would be init(id: UUID = UUID(), name: String). As that kinda is the sane thing to do for new records.

E.g.:

CREATE VIEW fun_sum
  AS SELECT SUM(intCol) + SUM(doubleCol) AS lol FROM fun;

breaks Swift with:

Cannot create a single-element tuple with an element label on this generated code:

public typealias PropertyIndices = ( idx_lol: Int32 )

For example the CoreData database of the Apple Notes application has a table called ZICCLOUDSYNCINGOBJECT, which has 184 properties.

Generation for this eventually crashes due to the recursion done in:

  func generateBindStatementForProperty(
         _ property : EntityInfo.Property,
         index      : Expression,
         trailer    : () -> [ Statement ] = { [] }
       ) -> ( Statement, didRecurse: Bool )

Probably need to flatten that. Maybe not do recursive binds for tables with that many columns at all.

I've got the issue that the create code generated by Enlighter does not properly indent the multi-line string literal and thus creates a compilation error:

Insufficient indentation of next 4 lines in multi-line string literal

Sample SQL:

CREATE TABLE IF NOT EXISTS "TestTable" (
  "id" integer PRIMARY KEY NOT NULL,
  "someText" text(128),
  "someInt" integer(128),
  "someFloat" real(128)
);

Code generated:

/// The SQL used to create the `TestTable` table.
    public static let create = 
      #"""
      CREATE TABLE "TestTable" (
  "id" INTEGER PRIMARY KEY NOT NULL,
  "someText" text(128),
  "someInt" integer(128),
  "someFloat" real(128)
);
      """#

Manually adding the generated code to the codebase and fixing the indentation compiles just fine.

Lighter: 1.0.16 Xcode 14.1

This can very quickly become non-Light, and maybe it shouldn't be actually done, but it might be useful to have a simple notification center that can broadcast DB change notifications. I had the feeling that it is better to let the user do this in own code as required.

I'd probably do this as a simple closure the user can hook up to anything he wants, like:

MyDatabase.onChange { some enum in  }

It quickly becomes a Pony, users might want to know the exact tables or IDs affected and so on, which sometimes isn't even possible w/ SQL itself. Transactions also complicate the matter.

Maybe rather something for Heavier. So a basic mechanism might be helpful.

As soon as an aggregate function is used, SQLite seems to loose the type affinity of the expression, e.g.:

CREATE TABLE fun ( value INT );
CREATE VIEW fun_sum AS SELECT SUM(value) AS combined FROM fun;

Results in:

/// Column `combined ` (`ANY`), optional (default: `nil`).
public var combined : String?

Which isn't great.

Adding CASTs doesn't seem to help with that, the affinity is lost by the aggregate function. To fix that, we'd probably have to parse the SQL and follow the type-affinity in the expression AST. Possible but work.

Instead of shipping the database, the database itself could be generated as Swift code, like:

struct Person: Identifiable {
  let id : Int
  let name : String
}

let people : [ Person ] = [
  Person(id: 1, name: "Bourne"),
  Person(id: 2, name: "Jason")
]

That might be useful sometimes? 🤔

Instead of heaving to create a view like this:

CREATE VIEW fun_sum
  AS SELECT SUM(intCol) + SUM(doubleCol) AS lol FROM fun;

It would be nice if sqlite2swift could directly generate code for a SELECT expression, like:

-- Swift: fetchSums
SELECT SUM(intCol) + SUM(doubleCol) AS lol FROM fun; 

Similar to SQLDelight.

This has one big advantage, a binding could be specified at compile time, like:

-- Swift: fetchSums
SELECT SUM(intCol) + SUM(doubleCol) AS lol FROM fun WHERE year = $1; 

And added to the fetch function, like:

db.fun.fetchSums(1973)