Skip to content

Perfect-CRUD 3.1.0

Latest
Latest

Choose a tag to compare

View all tags
@taplin taplin released this 15 Oct 13:20
3.1.0
1f6e0c7
This commit was created on GitHub.com and signed with GitHub’s verified signature.
GPG key ID: B5690EEEBB952194
Verified
Learn about vigilant mode.

A minor release. It adds throwing ways to read a query's results, so a failed query or a row that can't be decoded reaches your code instead of only the log. It also fixes several places where results were silently lost or wrong, most notably first() on a join. Nothing is removed or deprecated, and requirements are unchanged (Swift tools 6.2; macOS 12+, iOS 15+, Linux).

The changes are in #88; #89 makes the iOS 15 test build compile.

.package(url: "https://github.com/PerfectlySoft/Perfect-CRUD.git", from: "3.1.0")

The released connectors (Perfect-MySQL 4.0.0, Perfect-MariaDB 4.0.0, Perfect-PostgreSQL 5.0.0, Perfect-SQLite 6.0.0) depend on CRUD from: "3.0.1", so they resolve to 3.1.0 without a new release of their own.

New API

Select gains three throwing ways to read its rows. Each throws if the query can't be run (bad SQL, a server error, a joined table's row that can't be decoded) or a row can't be decoded, for example a zero or NULL date in a non-optional Date.

  • all() throws -> [OverAllForm] returns every row, or throws. It never returns the rows read before a failure.
  • forEachRow(_ body: (OverAllForm) throws -> Void) throws calls body with each row in turn and stops at the first error, including one thrown by body.
  • makeThrowingIterator() throws -> SelectIterator is the throwing counterpart of makeIterator(), named after the standard library's makeAsyncIterator(). Its iterator's existing nextElement() throws reads one row at a time, so you can stop part way through. It reads every joined table's rows before it returns. On Perfect-MySQL and Perfect-SQLite a failure of the master query throws here; on Perfect-PostgreSQL, which only runs the statement when the first row is read, it throws from the first nextElement().
let select = try table.where(\User.active == true).select()
let users = try select.all()
try select.forEachRow { user in print(user.name) }
var rows = try select.makeThrowingIterator()
while let user = try rows.nextElement() { if user.id == 42 { break } }

The throwing loop is named forEachRow, not forEach. A throwing forEach on Select would hide Sequence.forEach, and every existing select.forEach { … } written without try would stop compiling (checked with swiftc). The iterator method was going to be rows(), but the Dynamic API's DynamicResult.rows is an array of rows, so it was renamed before release.

Name clashes in your own code. None of the three names exist anywhere else in this package, the four connectors, Perfect-NIO or Perfect-Session. But if your app declares its own all(), forEachRow(_:) or makeThrowingIterator() on Select, or on Sequence (which Select conforms to), calls to it on a Select become ambiguous, or resolve to CRUD's throwing method and need try. Upgrade: rename your extension.

Fixes

All on the select-errors branch; there are no PRs yet.

  • fetchAll() throws on a bad row or a failed query. It was Array(select()), so it returned the rows read before a row that failed to decode, and [] when the query itself failed. It now runs select().all().

  • first() and firstAsync() throw when the query fails. They read through Select.makeIterator(), which logs an error from running the query and returns an empty iterator. So bad SQL, a server error or an undecodable child row of a join gave nil. A row that failed to decode already threw.

  • first() on a join returns the parent with all of its joined objects, and still reads only one parent. first() added limit(1) to the query. A limit applies to the most recent table or join, so on a Join, JoinPivot, or an order/limit after one, the LIMIT 1 went on the joined table's statement while the parent's statement ran unlimited. The parent came back with at most one child, the first child row overall. That row often belonged to another parent, and then the parent got none. This dates from first()'s addition in 2017. Now:

    • the master statement gets LIMIT 1, keeping the offset of any limit on the master table, and is ordered last by each join's on key;
    • each joined statement joins a derived table that repeats the master statement, with its bindings, instead of the whole master table, so it reads only that parent's joined objects.

    The extra ordering makes both copies of the master statement pick the same parent even when the query's own ordering leaves ties, which PostgreSQL's unordered DISTINCT can break differently each time it runs. With joins and no order, first() therefore returns the parent with the lowest join key. A limit after a join still limits that parent's joined objects. A plain table's first() is the same single LIMIT 1 statement as before, and a where(...) directly on a table (a Where isn't Limitable) now gets LIMIT 1 too.

  • A SelectIterator stays finished. After next() or nextElement() returned nil or threw, the next call went back to the connector. Perfect-MySQL re-ran the statement, restarting at row 1 or throwing "No data supplied for parameters" when it had bindings. Perfect-SQLite threw "no more rows". And on MySQL, PostgreSQL and SQLite, a caller that caught a decode error and kept going silently skipped the bad row. Both methods now return nil from then on.

  • DatabaseMigrator.migrate() doesn't re-run applied migrations when a record can't be read. It read the perfectcrud_migrations table with select().map. A record that failed to decode, or a failed query, ended the read early, and every migration recorded after it ran again. It now reads only the identifier column, so a bad appliedAt no longer matters to it. If an identifier can't be read, it throws a CRUDSQLExeError that names the table and says how to recover, and runs nothing.

Behaviour notes

  • for … in is unchanged. Select is still a Sequence, and for row in select, map, Array(select) and the other Sequence methods still can't throw. On an error they log it through CRUDLogging and end early: a query that can't run yields no rows, and a row that can't be decoded ends the loop there, skipping that row and every row after it. This is documented on Select, SelectIterator, makeIterator() and in the README's new "Reading results" section. Use all(), forEachRow(_:) or makeThrowingIterator() wherever losing rows silently isn't acceptable. This narrows the known limitation in the connectors' release notes ("select() stops at a row that fails to decode … instead of throwing") to Sequence iteration only.
  • Code that relied on nil or a short array now gets an error. first(), firstAsync() and fetchAll() throw where they used to return nil, [] or a partial array, and migrate() throws where it used to re-run migrations. Upgrade: nothing to change if these calls are already in a do/catch or a throwing function. If you were treating nil from first() as "no row" on a query that could fail, handle the error.
  • first() on a join returns all of the parent's joined objects and, without an order, the parent with the lowest join key (NULL keys sort first on MySQL and SQLite, last on PostgreSQL; add an order if that matters). Upgrade: if you added your own .limit(1) after a join to work around the old behaviour, it now limits that parent's joined objects, as the README describes; remove it if you want them all.
  • Recovering from an unreadable migration record. Look at perfectcrud_migrations, then fix the row's identifier, or delete the row if that migration should run again, and call migrate(_:) again. rollbackLast(_:) still reads appliedAt, so a row whose appliedAt can't be read blocks it until the row is fixed.
  • README. The install snippet asks for from: "3.1.0", and the connector snippet uses the released connector versions instead of branch: "main". Error Handling no longer claims every results error is thrown. There's a new "Upgrading to 3.1" section and a migration-recovery paragraph.

Tested

213 tests in 25 suites (183 in 3.0.1) pass with warnings as errors and TZ=America/Denver on:

  • Xcode 27 / Swift 6.4, debug and release;
  • Xcode 26.3 / Swift 6.2.4, release;
  • Linux amd64, Swift 6.2.4 and 6.4, debug and release;
  • the iOS Simulator (iPhone 17), at the iOS 15 deployment target.

Under Guard Malloc the full suite passes. Under the Thread Sanitizer there are no reports. Six exit tests in RecursiveModelTests abort under TSan, as they do on 3.0.1; the other 207 pass.

Tests that fail before the fix. Each new test that uses 3.0.1's API fails against the 3.0.1 sources:

  • three in SelectErrorTests: fetchAll, first/firstAsync on a failing query, and a join with an undecodable child;
  • the three JoinFirstTests;
  • the migrator test.

BoundedFirstTests record the SQL and bindings for the plain, where, join, pivot, double-join, self-join, limit-before-join and ordered cases, with ? and $n placeholders. They fail on the earlier fix that read the unlimited query, except the check that select() is unchanged. SelectIteratorLatchTests, the new migrator test and SelectThrowingReadTests cover behaviour that's new in 3.1.0. Three tests in SelectErrorTests guard behaviour 3.0.1 already had.

Live checks against MySQL 8.4 and PostgreSQL 17, plus SQLite.

  • Correctness. first() returns the expected parent and joined objects for a plain join, parent and child orderings, where on the parent and on the child, limit/skip before the join, pivot, pivot with where, a double join and self-joins. On MySQL and SQLite this always equals the first row of select(). On PostgreSQL four cases differ from select()'s first row only because that unordered DISTINCT returns an arbitrary parent, while first() returns the lowest key with its correct children.
  • Cost. With 100,000 parents and 500,000 children, a joined first() took about 1 ms with no memory growth on both servers. The earlier unlimited fix measured 613 ms and +89 MB on MySQL, and 325 ms and +103 MB on PostgreSQL.
  • Error paths. all(), forEachRow, first(), fetchAll() and firstAsync() throw on a missing table, a mid-stream decode error and an undecodable joined child. makeThrowingIterator() on a missing table throws on MySQL and SQLite, and at the first nextElement() on PostgreSQL. After the end or an error, every iterator returns nil on all three. The connection stays usable.
  • Migrator. With an unreadable appliedAt, migrate() returns [] and runs nothing on all three.

Connector suites, each pointed at a local clone of this branch tagged 3.1.0 and resolved through its own from: "3.0.1" requirement:

  • Perfect-SQLite 6.0.0: 58 tests pass.
  • Perfect-MySQL 4.0.0, live: 75 XCTest pass (3 skipped because they need a non-TLS server, a non-loopback host or MariaDB Connector/C), plus 60 Swift Testing tests and a scratch live check.
  • Perfect-PostgreSQL 5.0.0, live: 64 tests pass.
  • Perfect-MariaDB 4.0.0 (124 tests), Perfect-NIO 0.1.0 (111) and Perfect-Session 4.0.0 (SQLite, MySQL and PostgreSQL suites) also pass against the final branch, alongside Perfect-SQLite 6.0.0 (58), Perfect-MySQL 4.0.0 (live) and Perfect-PostgreSQL 5.0.0 (live, 64).

A scratch consumer resolved CRUD from: "3.1.0" and compiled the README's "Reading results" examples alongside an existing non-throwing select.forEach { … }.

Assets 2
Loading