Alter column data type support

Adds a new SQLAlterColumnDefinitionType expression for use in SQLAlterTable.modifyColumns.

This type is supported by a new SQLDialect.alterTableSyntax dialect option.

db.alter(table: "planets")
    .update(column: "type", dataType: .int)
    .run()

The above builder would result in the following queries:

PostgreSQL:

ALTER TABLE "planets" ALTER COLUMN "type" SET DATA TYPE BIGINT

MySQL:

ALTER TABLE `planets` MODIFY COLUMN "type" BIGINT

SQLite:

ALTER TABLE "planets"

Note: SQLite does not support altering existing columns.

Add optional autoincrement function expression to SQLDialect

Some databases doesn’t support the AUTOINCREMENT clause, but have the ability to implement autoincrementing primary keys using a function as a default instead.

Adds support for a drop table behavior clause

In SQL 92, the drop behavior, either RESTRICT or CASCADE is mandatory after the table name. Some databases requires either one of them to be present in a DROP TABLE statement, and there’s no way to specify that in sql-kit.

Intended to resolve issue #64.

Add support for creating and dropping triggers

Adds query structures and builders for creating a dropping triggers from the database (#86).

MySQL example:

try db.create(trigger: "foo", table: "planet", when: .before, event: .insert)
  .body(body)
  .order(precedence: .precedes, otherTriggerName: "other")

SQLite example:

try db.create(trigger: "foo", table: "planet", when: .before, event: .insert)
    .body(body)
    .condition("foo = bar")

PostgreSQL example:

try db.create(trigger: "foo", table: "planet", when: .after, event: .insert)
    .each(.row)
    .isConstraint()
    .timing(.initiallyDeferred)
    .condition("foo = bar")
    .procedure("qwer")
    .referencedTable(SQLIdentifier("galaxies"))

Trigger syntax can vary between SQL databases. SQLDialect.triggerSyntax has been added to check if certain features are supported before attempting to perform them.

Implement remaining binary operators

SQLBinaryOperator.add, .subtract, .multiply, .divide, and .modulo now work.

Unfortunately, .concatenate still does not work, because MySQL (being the odd one out, as usual) does not support it by default. However, we do now provide a much more helpful error message when encountering it.

Add SQLDistinct and isDistinct

Add SQLDistinct and isDistinct (#92)

Support for `SELECT DISTINCT` and `COUNT(DISTINCT(...))` etc.

Add many new helper methods (#84)

• Add protocol extension method which enables generic type inference for SQLRow.decode(column:as:).
• Add .columns(), alias-friendly .from(), and several forms of .join() to SQLSelectBuilder.
• Add an initializer for creating SQLQueryStrings from types conforming to StringProtocol (String and Substring).
• Add missing helpers taking arrays corresponding to variadic arguments on existing helpers.
• Add helper for adding an array of column definitions to a create table builder.

Support the LIKE and NOT LIKE operators.

The .like. and .notLike binary operators now work.

Enum Support

Adds support for using SQL enums natively. There are three different levels of support:

• typeName: Postgres-like, has CREATE TYPE ... AS ENUM syntax
• inline: MySQL-like, has ENUM(...) data type
• unsupported: SQLite-like, no enum support.

Supported enum syntax is available via SQLDialect.enumSyntax. This can be used to conditionally run code depending on database support.

A new test case SQLBenchmarker.testEnum has been added that tests the following on all three databases:

• Create table with enum column
• Add new value to enum
• Drop value from enum

Note: Dropping enum values from Postgres-like DBs is not supported.

Here is the raw SQL output from the benchmark for each database:

SQLite

CREATE TABLE "planets"("id" BIGINT PRIMARY KEY, "name" TEXT NOT NULL, "type" TEXT NOT NULL) []
INSERT INTO "planets" ("name", "type") VALUES (?, 'smallRocky'), (?, 'gasGiant') ["Earth", "Jupiter"]
INSERT INTO "planets" ("name", "type") VALUES (?, 'dwarf') ["Pluto"]
DELETE FROM "planets" WHERE "type" = 'gasGiant' []
DROP TABLE "planets" []

Postgres

CREATE TYPE "planet_type" AS ENUM ('smallRocky', 'gasGiant') []
CREATE TABLE "planets"("id" BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "name" TEXT NOT NULL, "type" "planet_type" NOT NULL) []
INSERT INTO "planets" ("name", "type") VALUES ($1, 'smallRocky'), ($2, 'gasGiant') ["Earth", "Jupiter"]
ALTER TYPE "planet_type" ADD VALUE 'dwarf' []
INSERT INTO "planets" ("name", "type") VALUES ($1, 'dwarf') ["Pluto"]
DELETE FROM "planets" WHERE "type" = 'gasGiant' []
DROP TABLE "planets" []
DROP TYPE "planet_type" []

MySQL

Opening new connection to [IPv6]localhost/::1:3306
CREATE TABLE `planets`(`id` BIGINT PRIMARY KEY AUTO_INCREMENT, `name` TEXT NOT NULL, `type` ENUM('smallRocky', 'gasGiant') NOT NULL) []
INSERT INTO `planets` (`name`, `type`) VALUES (?, 'smallRocky'), (?, 'gasGiant') ["Earth", "Jupiter"]
ALTER TABLE `planets` MODIFY `type` ENUM('smallRocky', 'gasGiant', 'dwarf') []
INSERT INTO `planets` (`name`, `type`) VALUES (?, 'dwarf') ["Pluto"]
DELETE FROM `planets` WHERE `type` = 'gasGiant' []
ALTER TABLE `planets` MODIFY `type` ENUM('smallRocky', 'dwarf') []
DROP TABLE `planets` []

Moved #83 here.

SQLKit 3.0.0 Beta 3

• Added new SQLRow.decode(model:) method (#79)
• SQLRow protocol expanded to support Codable (#79)
• Added first(decoding:) all(decoding:) and run(decoding:) methods to SQLQueryFetcher (#79)
• Added set(model:) method to SQLUpdateBuilder (#79)