The markdown parsing is broken/disabled for release notes. Sorry about that, I'm chasing the source of a crash that's been bringing this website down for the last couple of days.
Add support for varying syntax of SELECT locking clauses.
8 weeks ago
###### _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
8 weeks ago
###### _This patch was authored by @NeedleInAJayStack and released by @gwynne._
<!-- 🚀 Thank you for contributing! -->
<!-- Describe your changes clearly and use examples if possible. -->
Previously it was not easy to do pagination on the result of `SELECT ... UNION queries`. For example:
```sql
(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.
<!-- When this PR is merged, the title and body will be -->
<!-- used to generate a release automatically. -->
UNION handles single entry
9 weeks ago
###### _This patch was authored by @NeedleInAJayStack and released by @gwynne._
<!-- 🚀 Thank you for contributing! -->
<!-- Describe your changes clearly and use examples if possible. -->
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 `UNION`s in client code difficult. For example, building up a UNION in a for loop is awkward right now:
```swift
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.
<!-- When this PR is merged, the title and body will be -->
<!-- used to generate a release automatically. -->
Add ability for SQLKit drivers to expose version numbers
9 weeks ago
###### _This patch was authored and released by @gwynne._
Also fills in some missing documentation.
Add support for MySQL's DROP INDEX syntax
21 weeks ago
###### _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._
Adds `ALTER _ RENAME TO _` support
24 weeks ago
###### _This patch was authored by @NeedleInAJayStack and released by @0xTim._
<!-- 🚀 Thank you for contributing! -->
<!-- Describe your changes clearly and use examples if possible. -->
Adds table-renaming support to `SQLAlterTableBuilder`. This produces SQL queries of the form:
```sql
ALTER `table_name` RENAME TO `new_table_name`
```
<!-- When this PR is merged, the title and body will be -->
<!-- used to generate a release automatically. -->
Fix several issues with UNION query support
41 weeks ago
###### _This patch was authored and released by @gwynne._
- Fixes `SQLUnionBuilder` lacking `SQLQueryFetcher` conformance.
- Fixes support for SQLite.
- Adds support for `INTERSECT [DISTINCT|ALL]` and `EXCEPT [DISTINCT|ALL]` unions.
- Adds documentation for `SQLDialect` and its subtypes.
- Improves test infrastructure.
Add support for UNION queries
42 weeks ago
###### _This patch was authored by @finestructure and released by @gwynne._
Adds support for `UNION SELECT` queries:
```swift
try db.select()
.column("id")
.from("t1")
.where("f1", .equal, "foo")
.limit(1)
.union({
$0.column("id")
.from("t2")
.where("f2", .equal, "bar")
.limit(2)
}).union(all: {
$0.column("id")
.from("t3")
.where("f3", .equal, "baz")
.limit(3)
})
.run().wait()
```
Add support for `CREATE TABLE ... SELECT ...`
42 weeks ago
###### _This patch was authored and released by @gwynne._
Adds SQLKit support for creating tables populated by `SELECT` queries:
```swift
try await sqlDatabase.create(table: "populated")
.column("id", type: .bigint, .primaryKey, .notNull)
.column("data", type: .text)
.select { $0
.column(SQLLiteral.default, as: "id")
.column(SQLFunction("UPPER", args:
SQLFunction("LTRIM", args: SQLColumn("data"))
), as: "data")
.from("original_table")
.where("id", .in, Array(1 ... 10))
}
.run()
```
```sql
CREATE TABLE "populated" (
"id" BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY NOT NULL,
"data" TEXT
) AS SELECT
DEFAULT AS "id",
UPPER(LTRIM("data")) AS "data"
FROM
"original_table"
WHERE
"id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
```
Additional changes:
- Significant internal reorganization of SQLKit, specifically in `SQLSelectBuilder`.
- The `.where()`, `.orWhere()`, `.having()`, and `.orHaving()` families of methods on `SQLSelectBuilder` (and other `SQLPredicateBuilders`) have been normalized so that all four sets of methods offer the same series of overloads as all the others.
- More `async` tests.
- Support for MySQL's `DROP TEMPORARY TABLE` syntax, as `sqlDatabase.drop(table: "table").temporary()`.
Note: Despite what may seem appearances to the contrary, the public API of `SQLSelectBuilder` has not lost any methods, though it has gained a small number of overloads. A concerted effort was made to avoid changing any existing API in any source-incompatible way.
Fix Swift compiler choosing wrong overload for SQLInsertBuilder.values(Encodable)
43 weeks ago
###### _This patch was authored and released by @gwynne._
Under certain specific circumstances, the Swift compiler will choose the wrong overload of `SQLInsertBuilder.values(_:)` for the variants that take `Encodable` (specifically when an explicit Array is used in a generic context). Since it's too late to fix the API (it's public now), adding @_disfavoredOverload works around the issue in what should be a backwards-compatible fashion.