lighter-swift / lighter Goto Github PK

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) }

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.

I get a build error when I define a column as Boolean in the SQLite database. Something about Bool not conforming to MappedColumn.

I ran sqlite2swift against the chinook db with Lighter 1.0.26 and Swift 5.9 (Xcode 15.0) and it produced the following Swift code:

    /// The SQL used to create the `albums` table.
    public static let create = #"CREATE TABLE "albums"
(
    [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Title] NVARCHAR(160)  NOT NULL,
    [ArtistId] INTEGER  NOT NULL,
    FOREIGN KEY ([ArtistId]) REFERENCES "artists" ([ArtistId]) 
		ON DELETE NO ACTION ON UPDATE NO ACTION
);"#

Changing it to

    /// The SQL used to create the `albums` table.
    public static let create = #"""
        CREATE TABLE "albums"
        (
            [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            [Title] NVARCHAR(160)  NOT NULL,
            [ArtistId] INTEGER  NOT NULL,
            FOREIGN KEY ([ArtistId]) REFERENCES "artists" ([ArtistId])
                ON DELETE NO ACTION ON UPDATE NO ACTION
        );
        """#

for all the tables gets it to compile.

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 )

Not quite sure how that would look like. Presumably we'd detect the column type JSON and then allow SQLite JSON operations on it?
How would it look in the associated structure? Expose it as Data, but at least in the Lighter API provide a wrapper that allows direct access?

struct Person {
  var name: String
  var privateAddress: [ UInt8 ] // in raw API?
  var businessAddress: [ UInt8 ] // in raw API?
}

struct Address: Codable {
  var street: String
  var city: String
}

We can't use Codable directly here (and Codable is really slow anyways, so not that desirable).

Your package looks outstanding. Thank you. I would like to use it to replace GRDB in my current app development. I followed the getting started steps but instead of using an existing database, I added stations.sql to my project.

I get this Xcode error.
Multiple commands produce '/Users/iansmith/Library/Developer/Xcode/DerivedData/TryLighterStations-euddgsmncyclyietjarityspmrwp/Build/Products/Debug-iphonesimulator/TryLighterStations.app/station.sql'

The error also provides this information.

• Target 'TryLighterStations' (project 'TryLighterStations') has copy command from '/Users/iansmith/Documents/Developer/Tutorials, Examples and Experiments/Experiments/TryLighterStations/TryLighterStations/TryLighterStations/station.sql' to '/Users/iansmith/Library/Developer/Xcode/DerivedData/TryLighterStations-euddgsmncyclyietjarityspmrwp/Build/Products/Debug-iphonesimulator/TryLighterStations.app/station.sql'
• Target 'TryLighterStations' (project 'TryLighterStations') has copy command from '/Users/iansmith/Library/Developer/Xcode/DerivedData/TryLighterStations-euddgsmncyclyietjarityspmrwp/SourcePackages/plugins/TryLighterStations.output/TryLighterStations/Enlighter/station.sql' to '/Users/iansmith/Library/Developer/Xcode/DerivedData/TryLighterStations-euddgsmncyclyietjarityspmrwp/Build/Products/Debug-iphonesimulator/TryLighterStations.app/station.sql'

This is my station.sql:
CREATE TABLE station (
country_code TEXT,
home_page TEXT,
icon_url TEXT,
latitude REAL,
longitude REAL,
name TEXT NOT NULL,
ordered_by_name TEXT NOT NULL,
location TEXT NOT NULL,
id TEXT NOT NULL PRIMARY KEY,
url TEXT NOT NULL,
genre_list TEXT
);

CREATE INDEX country_index ON station (country_code);
CREATE INDEX name_index ON station (name);
CREATE INDEX ordered_by_name_index ON station (ordered_by_name);

CREATE TABLE station_genre (
genre TEXT NOT NULL,
station_id TEXT NOT NULL REFERENCES station (id) ON DELETE CASCADE
)

I am using Xcode Version 15.0.1 (15A507) and running on an ios17 simulator.

Perhaps I am not adding the .sql file to my project correctly. I am at a loss about where I went wrong. I would be most grateful for any guidance you could offer.

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

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

Instead of basing the thing of a SQLite schema, with the new Macro feature in Swift 5.9 we could also generate the metadata required from the Swift structure itself.
It probably wouldn't have much affect on the surrounding API, though maybe a Macro can't see related entities?
Also the "Database" main structure wouldn't be generated? But there could be API for that.

Wouldn't be hard to auto-generate copy-on-write structs, such may be more efficient for dealing with larger records naively.

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)

The main project I want to use this project with has two .sqlite3 files added as resources to the target.

When I build after configuring the Enlighter plug-in, I get the following error:

Build service could not create build operation: unable to load transferred PIF: PIFLoader: GUID '~/Library/Developer/Xcode/DerivedData/LighterTesting-euoeynunaavusyczdlhoedwycgua/SourcePackages/plugins/LighterTesting.output/LighterTesting/Enlighter/cities3.0.sqlite3' has already been registered

I can easily reproduce this in a sample project.

1. Created a new project - Multiplatform app, no Core Data or Tests
2. Add the Lighter package
3. Configure Enlighter as a build tool plug-in
4. Add two different .sqlite3 files, one .sqlite3 file & one .db file, or two .db files
5. Build

You will then see the above error.

It would be nice to detect FTS tables and do special things with them. I.e. also omit FTS backing tables from Lighter schemas (they need to be included in schemas if DB recreation is on, but no regular Swift funcs etc should be emitted for them?).

Table Detection

The tables can be detected by looking at the SQL in the metadata:

CREATE VIRTUAL TABLE papers USING fts3(...)

i.e. "VIRTUAL TABLE" and "USING fts*".

FTS tables, even contentful, have no associated column types (though they should be all TEXT?).

FTS creates a set of supporting, regular, tables that should be omitted, FTS 3:

papers_content 
papers_segments
papers_segdir  

FTS 4:

papers4_content 
papers4_segments
papers4_segdir  
papers4_docsize 
papers4_stat    

FTS 5:

email_data     
email_idx      
email_content  
email_docsize  
email_config   

So first the FTS table needs to be detected, than the associated tables need to be tagged/excluded.

External content FTS tables can refer to a backing table ("documents" in this case):

USING fts5(title, value, content='documents')

So that can be detected too (i.e. whether a regular table has one (or more! e.g. for multiple languages) FTS backing tables).

Operations

It is unclear what kind of operations would need to be supported? Everything in FTS centers around MATCH queries, like:

SELECT * FROM mail WHERE body MATCH 'sqlite';

Note that FTS table columns are completely untyped (but except for rowid always TEXT?)
The rowid is also more important to match up w/ the content table and needs to be exposed? (e.g. it can be aliased to an integer primary key and is also stable between VACUUMs).

Then FTS also has a set of special functions, like highlight and snippet:

SELECT highlight(email, 2, '<b>', '</b>') FROM email WHERE email MATCH 'fts5';

2 is the 0 based column index here ("email" is the FTS table). Also note that the table can be used on the left side of the match.

If an FTS table has a backing table (content='mytable'), maybe the ops should be directly attached to the content table, like sqlite_mytable_fts(...).

FTS 5 query language:

<phrase>    := string [*]
<phrase>    := <phrase> + <phrase>
<neargroup> := NEAR ( <phrase> <phrase> ... [, N] )
<query>     := [ [-] <colspec> :] [^] <phrase>
<query>     := [ [-] <colspec> :] <neargroup>
<query>     := [ [-] <colspec> :] ( <query> )
<query>     := <query> AND <query>
<query>     := <query> OR <query>
<query>     := <query> NOT <query>
<colspec>   := colname
<colspec>   := { colname1 colname2 ... }

Ordering has a special rank column (ORDER BY rank), that would need to be made available.

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? 🤔

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.

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.

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.

I'd be very interested to see support for including open source extensions to sqlite, such as SQLCipher.

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.

Not sure if this is an xcode bug or not, but I am unable to add the build tool after updating xcode.

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.

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
    )