Skip to content

Perfect-SQLite 6.0.0

Latest
Latest

Choose a tag to compare

View all tags
@taplin taplin released this 13 Oct 14:44
6.0.0
fd3b8ed
This commit was created on GitHub.com and signed with GitHub’s verified signature.
GPG key ID: B5690EEEBB952194
Verified
Learn about vigilant mode.

The first release since 5.0.0 (January 2020). It is the Swift 6 rewrite of the package, built on Perfect-CRUD 3.0.0. It builds on Linux against the system SQLite again, and it fixes SQL injection through identifiers, data loss and lost foreign keys in .reconcileTable, and a SQLite quirk that would let a misspelled Dynamic API field name delete every row. It is a major version: read "Breaking and behaviour changes" before upgrading, especially if you run raw SQL with double-quoted strings or reconcile tables that have sub-tables.

Requirements

  • Swift tools 6.2 (// swift-tools-version: 6.2); 5.0.0 declared 5.1. The library and tests build in the Swift 6 language mode.
  • Platforms: macOS 12+ (was 10.15+), iOS 15+ (newly declared), Linux.
  • Perfect-CRUD from: "3.0.1" (was from: "2.0.0"); 3.0.1 fixes four crashes in 3.0.0. No other package dependencies.
  • SQLite 3.29 or newer for every feature (the double-quoted string switch needs 3.29, case-only column renames 3.25). The macOS 12+ and iOS 15+ SDKs and Ubuntu 24.04 (3.45) ship newer versions.
  • Linux: the system SQLite development package (libsqlite3-dev on Debian/Ubuntu, sqlite-devel on Fedora/RHEL) and pkg-config, which SwiftPM uses to find it (the official swift Docker images already include pkg-config).
.package(url: "https://github.com/PerfectlySoft/Perfect-SQLite.git", from: "6.0.0")

CI runs on GitHub Actions for every push and pull request (#29, #30): Linux (Swift 6.2 and 6.4) and macOS (Xcode 26.0.1 and 26.6), each in debug and release with warnings as errors, plus the tests on an iOS Simulator at the iOS 15 deployment target.

Highlights

  • Swift 6 (#22). SQLite and SQLiteStmt keep their names and signatures. They and the CRUD delegates are @unchecked Sendable: none of them is thread-safe, so serialize access to each connection yourself.
  • Async API and connection pooling (new through Perfect-CRUD 3). SQLiteDatabaseConfiguration is @unchecked Sendable, so DatabaseConnectionPool<SQLiteDatabaseConfiguration>, transactionAsync, sqlAsync, insertAsync, firstAsync and fetchAll() work with SQLite. They run the blocking SQLite calls on Perfect-CRUD's dispatch queue while the caller waits. Use each connection from one task at a time: moving one between threads is fine (SQLite is built multi-thread on Apple platforms and serialized on Ubuntu), but two tasks using one connection or one Database value at once is a data race on Apple platforms and interleaves statements anywhere. For concurrent work, give each task its own connection, for example from a pool. Pooled connections are separate SQLite connections to one file, so writers still take the database lock in turn.
  • Dynamic API (new through Perfect-CRUD 3; 5.0.0 had none). Database.select(DynamicQuery), mutate(DynamicMutation) and execute(DynamicSQL) work on SQLite for tables and fields named at runtime (#22 implements nextDynamicRow()). Dynamic rows fold column names case-insensitively, first column wins. DynamicResult.affectedRows is always 0 and insertedID always nil on this connector (see below).
  • Linux builds again (#23). The new PerfectCSQLite system-library target links the distribution's libsqlite3 (via pkg-config sqlite3) and replaces the Perfect-sqlite3-support package that 5.0.0 used on Linux. Apple platforms keep using the SDK's SQLite3 module.
  • Identifiers are escaped and always quoted (#25). quote(identifier:) doubles an embedded ", so a Dynamic API table or field name can no longer end the identifier and inject SQL. Column definitions, REFERENCES clauses and the reconcile copy are quoted too, so keyword column names such as order work.
  • Double-quoted string literals are off (#27). See below; doubleQuotedStrings: true opts back in.
  • .reconcileTable keeps foreign keys and reaches sub-tables (#26, #28). See below.

Breaking and behaviour changes

  • Toolchain and platforms. Swift tools 6.2 and macOS 12 / iOS 15 are required, and Perfect-CRUD 3.0.0 comes with them (its own 3.0.0 release notes list its breaking changes, such as Expression → CRUDExpression). Upgrade: use Swift 6.2 or newer, raise your deployment targets, and read the CRUD notes.
  • Linux dependency (#23). Perfect-sqlite3-support is no longer used. Upgrade: install libsqlite3-dev (or sqlite-devel) and pkg-config in your Linux build images.
  • Double-quoted strings are identifiers (#27). Every connection opened by SQLite(...) or SQLiteDatabaseConfiguration(...) turns off SQLite's double-quoted string literal fallback, which the macOS SDK and Ubuntu's libsqlite3 leave on. A "name" that matches no column now throws no such column instead of being read as a string, so a Dynamic delete or update on a misspelled field (WHERE "nmae" = 'nmae') throws instead of hitting every row, or silently none. Raw SQL that writes string literals as "..." now fails, and so do views and triggers already stored with "..." strings when they're used (including an INSERT that fires such a trigger). Table DEFAULT values, CHECK clauses and partial-index WHEREs are still read leniently in normal use, but a "..." string in a CHECK clause or partial-index WHERE anywhere in the database makes every .reconcileTable that removes columns fail, because the rebuild's ALTER TABLE … RENAME re-parses the whole schema (reproduced for both). Upgrade: use single quotes or bindings; for legacy SQL or stored schemas, and to reconcile databases like the above, open with SQLite(path, doubleQuotedStrings: true) or SQLiteDatabaseConfiguration(path, doubleQuotedStrings: true). The generic init(url:name:…) always turns it off. This catches misspelled names; it is not authorization: a caller who picks both a Dynamic field and its value can still match every row (rowid > 0).
  • Reconcile reaches sub-tables on every run (#28). create(Parent.self, policy: .reconcileTable) used to stop at an existing parent and never created or reconciled its sub-tables. It now creates or reconciles each sub-table, referenced tables first. A sub-table whose type has fewer properties than the table has columns loses those columns, and the rebuild drops that table's own indexes and triggers. Upgrade: pass .shallow (for example [.reconcileTable, .shallow]) to leave sub-tables alone, and check sub-tables that are mapped by a projection type.
  • Reconcile is not atomic (#28). The tables are created or reconciled one by one, not in one transaction (the rebuild needs PRAGMA foreign_keys off, which has no effect inside a transaction), so a failing step leaves earlier tables changed. Upgrade: back up before reconciling a schema you can't recreate.
  • Reconcile refuses unsafe rebuilds (#26). With foreign_keys on (the connector's default), a rebuild that removes columns runs PRAGMA foreign_key_check on the table and the tables referencing it, and throws and rolls back if it finds any violation, including ones that existed before the reconcile. Inside a transaction (where PRAGMA foreign_keys can't be turned off) it throws for a table that any table references, itself included. Before, the rebuild deleted referencing rows through ON DELETE CASCADE. Upgrade: reconcile outside a transaction, fix existing FK violations first, and remove a column other tables reference only after changing those tables.
  • .dropTable drops children first (#28), so it no longer fails while a RESTRICT/NO ACTION child has rows.
  • Column names match without regard to ASCII case (#28), as SQLite does. A column whose name changed only in case is renamed to the model's spelling (RENAME COLUMN, SQLite 3.25+) instead of being dropped and re-added with its data lost.
  • One statement per call. Database.sql(...) and Dynamic execute, even with allowsMultipleStatements: true, now throw SQLiteCRUDError when the SQL holds more than one statement. They used to run the first statement and silently drop the rest, because sqlite3_prepare_v2 stops after one. Trailing ;, whitespace and comments are fine. The raw SQLite.execute(statement:) API is unchanged. Upgrade: run each statement in its own call.
  • Unsigned values above Int64.max (Dynamic .uint, or a typed comparison with a large UInt) throw SQLiteCRUDError when bound; they used to trap. Typed UInt/UInt64 properties above Int64.max throw too: Perfect-CRUD 3.0.1 binds them as unsigned, and the connector refuses what doesn't fit.
  • Duplicate result column names: when two result columns share a name (SELECT a.id, b.id), the first one is read; the reader used to take the last.
  • columnText repairs invalid UTF-8 with U+FFFD replacement characters; 5.0.0 returned "" for the whole value. Upgrade: if you stored non-UTF-8 bytes as TEXT, read them with columnIntBlob instead.
  • Dynamic API counts (a known gap). This connector doesn't implement affectedRowCount() or lastInsertedID(), so DynamicResult.affectedRows is always 0 and insertedID always nil. Upgrade: if you need them, read changes() or lastInsertRowID() on the connection (SQLiteDatabaseConfiguration.sqlite) right after the call. lastInsertRowID() and Insert.lastInsertId() return the rowid, which is the key only for an INTEGER PRIMARY KEY column; tables CRUD creates declare integer keys as INT PRIMARY KEY, so there the rowid is a separate value, not your id.

Fixes

  • #22: Swift 6 rewrite on Perfect-CRUD's modernized API; nextDynamicRow() for the Dynamic API.
  • #23: Builds on Linux with the system libsqlite3 (the target is named PerfectCSQLite so it can't clash with another package's CSQLite).
  • #24: README examples, a wrong macOS hint and stale claims corrected.
  • #25: quote(identifier:) escapes " per Unicode scalar (a " followed by a combining mark used to slip through); every identifier in generated DDL is quoted. Closes a Dynamic API injection reproduced on the previous main.
  • #26: A reconcile rebuild keeps the table's FOREIGN KEYs, no longer cascades deletes into referencing tables, rolls back on failure with foreign_keys turned back on, and no longer leaks one table's FOREIGN KEY into the next table's CREATE.
  • #27: Double-quoted string literal fallback off by default (the PerfectSQLiteShim C target wraps the variadic sqlite3_db_config).
  • #28: An added @ForeignKey column gets its REFERENCES constraint; reconcile reaches sub-tables; .dropTable order; case-insensitive (ASCII-only) column matching.
  • #29, #30: iOS 15 declared; CI for Linux, macOS and the iOS Simulator.
  • #31: SQLiteDatabaseConfiguration is Sendable, so CRUD's pool and async methods compile with it; multi-statement SQL throws instead of dropping statements; SQL with no statement (empty, whitespace, ; or a comment) or an embedded NUL throws instead of crashing; unsigned overflow throws instead of trapping; the first of duplicate column names wins (case-insensitively for Dynamic rows, so SELECT 1 AS a, 2 AS A no longer crashes in CRUD 3.0.0's DynamicRow); the stale pre-5.0.0 docs/ API pages were removed.

Upgrade notes

  1. Point your dependency at from: "6.0.0", build with Swift 6.2 or newer, and raise deployment targets to macOS 12 / iOS 15. Apply the Perfect-CRUD 3.0.0 upgrade notes too.
  2. On Linux, install libsqlite3-dev and pkg-config.
  3. Search your raw SQL, views and triggers for "..." string literals. Change them to single quotes, or open those databases with doubleQuotedStrings: true.
  4. If you reconcile parents with sub-tables, decide whether you want sub-tables reconciled every run; if not, add .shallow. Back up before reconciling, since a failure part-way leaves earlier tables changed.
  5. Split any multi-statement strings passed to Database.sql or execute(DynamicSQL).
  6. Don't rely on DynamicResult.affectedRows or insertedID with this connector.
  7. If you adopt the async API, give each concurrent task its own connection (a DatabaseConnectionPool does this); don't share one Database value between tasks running at the same time.

Tested for this release: the 58 tests pass on macOS with Xcode 27 (Swift 6.4) and Xcode 26.3 (Swift 6.2.4) and on Linux with Swift 6.2.4 and 6.4 (Ubuntu 24.04), with warnings as errors, against Perfect-CRUD 3.0.1. GitHub Actions ran Linux 6.2/6.4 and macOS Xcode 26.0.1/26.6 in debug and release, plus the iOS Simulator, on the merged code (#31).

Legacy

The pre-Swift-6 code is preserved on the legacy branch, which points at the same commit as the 5.0.0 tag. Stay on 5.0.0 if you can't move to Swift 6.2 yet.

Assets 2
Loading