Add support for specifying a query logging level at the SQLKit layer

This patch was authored and released by @gwynne.

Add SQLDatabase.queryLogLevel protocol requirement, defaulting to .debug for databases which don't implement it. Each individual driver must implement support for this logging. This corresponds to the sqlLogLevel configuration in FluentPostgresDriver and is the first step in normalizing support for it at the PostgresKit/MySQLKit/SQLiteKit layer.

Update min Swift version to 5.6 and make platform versions consistent

This patch was authored and released by @gwynne.

Make SQLList's properties mutable

This patch was authored and released by @gwynne.

Improves consistency with other SQLKit types (and is incidentally convenient for Fluent).

Support += operator for SQLQueryString.

This patch was authored by @maciejtrybilo and released by @gwynne.

Very simple change to support the += operator for appending SQLQueryStrings.

Bump minimum Swift version to 5.5

This patch was authored and released by @gwynne.

Also syncs up CI with the latest refinements.

Add support for varying syntax of SELECT locking clauses.

This patch was authored and released by @gwynne.

The FOR SHARE syntax previously provided by adding .for(.share) to a SELECT query is specific to PostgreSQL. This update allows each database driver to specify the correct syntax for locking clauses - or to signal that it doesn't implement that functionality - via its SQLDialect. The default is to assume locking clauses are not supported.

Because new public API is added to SQLStatement and SQLDialect, this update is semver-minor.

PR for MySQL support: vapor/mysql-kit#310
PR for PostgreSQL support: vapor/postgres-kit#230
PR for SQLite support: <not required, SQLite does not support locking clauses>

Adds `LIMIT`, `OFFSET` and `ORDER BY` to Union results

This patch was authored by @NeedleInAJayStack and released by @gwynne.

Previously it was not easy to do pagination on the result of SELECT ... UNION queries. For example:

(SELECT * FROM "Table" WHERE "name" = 'first thing')
UNION ALL
(SELECT * FROM "Zone" WHERE "name" = 'second thing')
LIMIT 5
OFFSET 3
ORDER BY "name"

This pull request adds LIMIT, OFFSET, and ORDER BY functionality to the SQLUnion/SQLUnionBuilder, primarily by copying over the SQLSelect and SQLSubqueryClauseBuilder implementations.

I also have a protocol-based approach that reduces code copying but it comes with it's own smells. I'm happy to elaborate if desired.

UNION handles single entry

This patch was authored by @NeedleInAJayStack and released by @gwynne.

This PR simplifies dynamically building UNION statements.

To be specific, it loosens the restriction that a SQLUnion must contain multiple select statements. While this is common usage, it makes building up UNIONs in client code difficult. For example, building up a UNION in a for loop is awkward right now:

let ids = [1, 2, 3, ...]
guard let firstId = ids.first else { ... }

// Must manually short-circuit as a SQLSelectBuilder
guard ids.count > 1 else {
    return sql.select.column("id").from("t1").where("id", .equals, firstId).all()
}
let unionBuilder = sql.union { select in
    select.column("id").from("t1").where("id", .equals, firstId)
}
for id in ids[1..<ids.count] {
    unionBuilder.union(all: { select in
        select.column("id").from("t1").where("id", .equals, id)
    })
}
return unionBuilder.all()

This PR removes the need for the commented guard in the code above. It also improves code safety by removing a runtime fatal error condition.

Add ability for SQLKit drivers to expose version numbers

This patch was authored and released by @gwynne.

Also fills in some missing documentation.

Add support for MySQL's DROP INDEX syntax

This patch was authored and released by @gwynne.

MySQL's version of DROP INDEX differs from PostgreSQL's in that it requires an ON <table name> clause in order to identify the index to drop, since MySQL treats indexes as table-level objects rather than schema-level. This PR adds support for optionally specifying the table name.

Also deprecates SQLDataType.type(_:), which should never have been public - it is a utility method used in one place by a single test case; its behavior is only valid in PostgreSQL, and even then only for custom-typed enums, but its name and placement give no hint of this to callers. Since it is public API, it can not be summarily removed without a semver-major break, but we can at least remove the single call site and discourage any further use.

Also updates the CI workflows as per the usual in my PRs 🙂.

Note: semver-minor not only due to the new public API on SQLDropIndexBuilder but also because the deprecation of a public API is, while not source-breaking, annoying for anyone who actually used it.