Files
GRDB.swift/GRDB/QueryInterface/Schema/TableDefinition.swift
2020-12-23 12:13:05 +01:00

1072 lines
40 KiB
Swift

extension Database {
// MARK: - Database Schema
/// Creates a database table.
///
/// try db.create(table: "place") { t in
/// t.autoIncrementedPrimaryKey("id")
/// t.column("title", .text)
/// t.column("favorite", .boolean).notNull().default(false)
/// t.column("longitude", .double).notNull()
/// t.column("latitude", .double).notNull()
/// }
///
/// See https://www.sqlite.org/lang_createtable.html and
/// https://www.sqlite.org/withoutrowid.html
///
/// - parameters:
/// - name: The table name.
/// - temporary: If true, creates a temporary table.
/// - ifNotExists: If false (the default), an error is thrown if the
/// table already exists. Otherwise, the table is created unless it
/// already exists.
/// - withoutRowID: If true, uses WITHOUT ROWID optimization.
/// - body: A closure that defines table columns and constraints.
/// - throws: A DatabaseError whenever an SQLite error occurs.
public func create(
table name: String,
temporary: Bool = false,
ifNotExists: Bool = false,
withoutRowID: Bool = false,
body: (TableDefinition) -> Void)
throws
{
let definition = TableDefinition(
name: name,
temporary: temporary,
ifNotExists: ifNotExists,
withoutRowID: withoutRowID)
body(definition)
let sql = try definition.sql(self)
try execute(sql: sql)
}
/// Renames a database table.
///
/// See https://www.sqlite.org/lang_altertable.html
///
/// - throws: A DatabaseError whenever an SQLite error occurs.
public func rename(table name: String, to newName: String) throws {
try execute(sql: "ALTER TABLE \(name.quotedDatabaseIdentifier) RENAME TO \(newName.quotedDatabaseIdentifier)")
}
/// Modifies a database table.
///
/// try db.alter(table: "player") { t in
/// t.add(column: "url", .text)
/// }
///
/// See https://www.sqlite.org/lang_altertable.html
///
/// - parameters:
/// - name: The table name.
/// - body: A closure that defines table alterations.
/// - throws: A DatabaseError whenever an SQLite error occurs.
public func alter(table name: String, body: (TableAlteration) -> Void) throws {
let alteration = TableAlteration(name: name)
body(alteration)
let sql = try alteration.sql(self)
try execute(sql: sql)
}
/// Deletes a database table.
///
/// See https://www.sqlite.org/lang_droptable.html
///
/// - throws: A DatabaseError whenever an SQLite error occurs.
public func drop(table name: String) throws {
try execute(sql: "DROP TABLE \(name.quotedDatabaseIdentifier)")
}
/// Creates an index.
///
/// try db.create(index: "playerByEmail", on: "player", columns: ["email"])
///
/// SQLite can also index expressions (https://www.sqlite.org/expridx.html)
/// and use specific collations. To create such an index, use a raw SQL
/// query.
///
/// try db.execute(sql: "CREATE INDEX ...")
///
/// See https://www.sqlite.org/lang_createindex.html
///
/// - parameters:
/// - name: The index name.
/// - table: The name of the indexed table.
/// - columns: The indexed columns.
/// - unique: If true, creates a unique index.
/// - ifNotExists: If false, no error is thrown if index already exists.
/// - condition: If not nil, creates a partial index
/// (see https://www.sqlite.org/partialindex.html).
public func create(
index name: String,
on table: String,
columns: [String],
unique: Bool = false,
ifNotExists: Bool = false,
condition: SQLExpressible? = nil)
throws
{
let definition = IndexDefinition(
name: name,
table: table,
columns: columns,
unique: unique,
ifNotExists: ifNotExists,
condition: condition?.sqlExpression)
let sql = try definition.sql(self)
try execute(sql: sql)
}
/// Deletes a database index.
///
/// See https://www.sqlite.org/lang_dropindex.html
///
/// - throws: A DatabaseError whenever an SQLite error occurs.
public func drop(index name: String) throws {
try execute(sql: "DROP INDEX \(name.quotedDatabaseIdentifier)")
}
/// Delete and recreate from scratch all indices that use this collation.
///
/// This method is useful when the definition of a collation sequence
/// has changed.
///
/// See https://www.sqlite.org/lang_reindex.html
///
/// - throws: A DatabaseError whenever an SQLite error occurs.
public func reindex(collation: Database.CollationName) throws {
try execute(sql: "REINDEX \(collation.rawValue)")
}
/// Delete and recreate from scratch all indices that use this collation.
///
/// This method is useful when the definition of a collation sequence
/// has changed.
///
/// See https://www.sqlite.org/lang_reindex.html
///
/// - throws: A DatabaseError whenever an SQLite error occurs.
public func reindex(collation: DatabaseCollation) throws {
try reindex(collation: Database.CollationName(rawValue: collation.name))
}
}
/// The TableDefinition class lets you define table columns and constraints.
///
/// You don't create instances of this class. Instead, you use the Database
/// `create(table:)` method:
///
/// try db.create(table: "player") { t in // t is TableDefinition
/// t.column(...)
/// }
///
/// See https://www.sqlite.org/lang_createtable.html
public final class TableDefinition {
private typealias KeyConstraint = (
columns: [String],
conflictResolution: Database.ConflictResolution?)
private struct ForeignKeyConstraint {
var columns: [String]
var table: String
var destinationColumns: [String]?
var deleteAction: Database.ForeignKeyAction?
var updateAction: Database.ForeignKeyAction?
var deferred: Bool
}
private let name: String
private let temporary: Bool
private let ifNotExists: Bool
private let withoutRowID: Bool
private var columns: [ColumnDefinition] = []
private var primaryKeyConstraint: KeyConstraint?
private var uniqueKeyConstraints: [KeyConstraint] = []
private var foreignKeyConstraints: [ForeignKeyConstraint] = []
private var checkConstraints: [SQLExpression] = []
init(name: String, temporary: Bool, ifNotExists: Bool, withoutRowID: Bool) {
self.name = name
self.temporary = temporary
self.ifNotExists = ifNotExists
self.withoutRowID = withoutRowID
}
/// Defines the auto-incremented primary key.
///
/// try db.create(table: "player") { t in
/// t.autoIncrementedPrimaryKey("id")
/// }
///
/// The auto-incremented primary key is an integer primary key that
/// automatically generates unused values when you do not explicitly
/// provide one, and prevents the reuse of ids over the lifetime of
/// the database.
///
/// **It is the preferred way to define a numeric primary key**.
///
/// The fact that an auto-incremented primary key prevents the reuse of
/// ids is an excellent guard against data races that could happen when your
/// application processes ids in an asynchronous way. The auto-incremented
/// primary key provides the guarantee that a given id can't reference a row
/// that is different from the one it used to be at the beginning of the
/// asynchronous process, even if this row gets deleted and a new one is
/// inserted in between.
///
/// See https://www.sqlite.org/lang_createtable.html#primkeyconst and
/// https://www.sqlite.org/lang_createtable.html#rowid
///
/// - parameter conflictResolution: An optional conflict resolution
/// (see https://www.sqlite.org/lang_conflict.html).
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func autoIncrementedPrimaryKey(
_ name: String,
onConflict conflictResolution: Database.ConflictResolution? = nil)
-> ColumnDefinition
{
column(name, .integer).primaryKey(onConflict: conflictResolution, autoincrement: true)
}
/// Appends a table column.
///
/// try db.create(table: "player") { t in
/// t.column("name", .text)
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#tablecoldef
///
/// - parameter name: the column name.
/// - parameter type: the eventual column type.
/// - returns: An ColumnDefinition that allows you to refine the
/// column definition.
@discardableResult
public func column(_ name: String, _ type: Database.ColumnType? = nil) -> ColumnDefinition {
let column = ColumnDefinition(name: name, type: type)
columns.append(column)
return column
}
/// Defines the table primary key.
///
/// try db.create(table: "citizenship") { t in
/// t.column("citizenID", .integer)
/// t.column("countryCode", .text)
/// t.primaryKey(["citizenID", "countryCode"])
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#primkeyconst and
/// https://www.sqlite.org/lang_createtable.html#rowid
///
/// - parameter columns: The primary key columns.
/// - parameter conflictResolution: An optional conflict resolution
/// (see https://www.sqlite.org/lang_conflict.html).
public func primaryKey(_ columns: [String], onConflict conflictResolution: Database.ConflictResolution? = nil) {
guard primaryKeyConstraint == nil else {
// Programmer error
fatalError("can't define several primary keys")
}
primaryKeyConstraint = (columns: columns, conflictResolution: conflictResolution)
}
/// Adds a unique key.
///
/// try db.create(table: "place") { t in
/// t.column("latitude", .double)
/// t.column("longitude", .double)
/// t.uniqueKey(["latitude", "longitude"])
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#uniqueconst
///
/// - parameter columns: The unique key columns.
/// - parameter conflictResolution: An optional conflict resolution
/// (see https://www.sqlite.org/lang_conflict.html).
public func uniqueKey(_ columns: [String], onConflict conflictResolution: Database.ConflictResolution? = nil) {
uniqueKeyConstraints.append((columns: columns, conflictResolution: conflictResolution))
}
/// Adds a foreign key.
///
/// try db.create(table: "passport") { t in
/// t.column("issueDate", .date)
/// t.column("citizenID", .integer)
/// t.column("countryCode", .text)
/// t.foreignKey(["citizenID", "countryCode"], references: "citizenship", onDelete: .cascade)
/// }
///
/// See https://www.sqlite.org/foreignkeys.html
///
/// - parameters:
/// - columns: The foreign key columns.
/// - table: The referenced table.
/// - destinationColumns: The columns in the referenced table. If not
/// specified, the columns of the primary key of the referenced table
/// are used.
/// - deleteAction: Optional action when the referenced row is deleted.
/// - updateAction: Optional action when the referenced row is updated.
/// - deferred: If true, defines a deferred foreign key constraint.
/// See https://www.sqlite.org/foreignkeys.html#fk_deferred.
public func foreignKey(
_ columns: [String],
references table: String,
columns destinationColumns: [String]? = nil,
onDelete deleteAction: Database.ForeignKeyAction? = nil,
onUpdate updateAction: Database.ForeignKeyAction? = nil,
deferred: Bool = false)
{
foreignKeyConstraints.append(ForeignKeyConstraint(
columns: columns,
table: table,
destinationColumns: destinationColumns,
deleteAction: deleteAction,
updateAction: updateAction,
deferred: deferred))
}
/// Adds a CHECK constraint.
///
/// try db.create(table: "player") { t in
/// t.column("personalPhone", .text)
/// t.column("workPhone", .text)
/// let personalPhone = Column("personalPhone")
/// let workPhone = Column("workPhone")
/// t.check(personalPhone != nil || workPhone != nil)
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#ckconst
///
/// - parameter condition: The checked condition
public func check(_ condition: SQLExpressible) {
checkConstraints.append(condition.sqlExpression)
}
/// Adds a CHECK constraint.
///
/// try db.create(table: "player") { t in
/// t.column("personalPhone", .text)
/// t.column("workPhone", .text)
/// t.check(sql: "personalPhone IS NOT NULL OR workPhone IS NOT NULL")
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#ckconst
///
/// - parameter sql: An SQL snippet
public func check(sql: String) {
checkConstraints.append(SQLLiteral(sql: sql).sqlExpression)
}
fileprivate func sql(_ db: Database) throws -> String {
var statements: [String] = []
do {
var chunks: [String] = []
chunks.append("CREATE")
if temporary {
chunks.append("TEMPORARY")
}
chunks.append("TABLE")
if ifNotExists {
chunks.append("IF NOT EXISTS")
}
chunks.append(name.quotedDatabaseIdentifier)
let primaryKeyColumns: [String]
if let (columns, _) = primaryKeyConstraint {
primaryKeyColumns = columns
} else if let index = columns.firstIndex(where: { $0.primaryKey != nil }) {
primaryKeyColumns = [columns[index].name]
} else {
// WITHOUT ROWID optimization requires a primary key. If the
// user sets withoutRowID, but does not define a primary key,
// this is undefined behavior.
//
// We thus can use the rowId column even when the withoutRowID
// flag is set ;-)
primaryKeyColumns = [Column.rowID.name]
}
do {
var items: [String] = []
try items.append(contentsOf: columns.map {
try $0.sql(db, tableName: name, primaryKeyColumns: primaryKeyColumns)
})
if let (columns, conflictResolution) = primaryKeyConstraint {
var chunks: [String] = []
chunks.append("PRIMARY KEY")
chunks.append("(\(columns.map(\.quotedDatabaseIdentifier).joined(separator: ", ")))")
if let conflictResolution = conflictResolution {
chunks.append("ON CONFLICT")
chunks.append(conflictResolution.rawValue)
}
items.append(chunks.joined(separator: " "))
}
for (columns, conflictResolution) in uniqueKeyConstraints {
var chunks: [String] = []
chunks.append("UNIQUE")
chunks.append("(\(columns.map(\.quotedDatabaseIdentifier).joined(separator: ", ")))")
if let conflictResolution = conflictResolution {
chunks.append("ON CONFLICT")
chunks.append(conflictResolution.rawValue)
}
items.append(chunks.joined(separator: " "))
}
for constraint in foreignKeyConstraints {
var chunks: [String] = []
chunks.append("FOREIGN KEY")
chunks.append("(\(constraint.columns.map(\.quotedDatabaseIdentifier).joined(separator: ", ")))")
chunks.append("REFERENCES")
if let destinationColumns = constraint.destinationColumns {
chunks.append("""
\(constraint.table.quotedDatabaseIdentifier)(\
\(destinationColumns.map(\.quotedDatabaseIdentifier).joined(separator: ", "))\
)
""")
} else if constraint.table == name {
chunks.append("""
\(constraint.table.quotedDatabaseIdentifier)(\
\(primaryKeyColumns.map(\.quotedDatabaseIdentifier).joined(separator: ", "))\
)
""")
} else {
let primaryKey = try db.primaryKey(constraint.table)
chunks.append("""
\(constraint.table.quotedDatabaseIdentifier)(\
\(primaryKey.columns.map(\.quotedDatabaseIdentifier).joined(separator: ", "))\
)
""")
}
if let deleteAction = constraint.deleteAction {
chunks.append("ON DELETE")
chunks.append(deleteAction.rawValue)
}
if let updateAction = constraint.updateAction {
chunks.append("ON UPDATE")
chunks.append(updateAction.rawValue)
}
if constraint.deferred {
chunks.append("DEFERRABLE INITIALLY DEFERRED")
}
items.append(chunks.joined(separator: " "))
}
for checkExpression in checkConstraints {
var chunks: [String] = []
try chunks.append("CHECK (\(checkExpression.quotedSQL(db)))")
items.append(chunks.joined(separator: " "))
}
chunks.append("(\(items.joined(separator: ", ")))")
}
if withoutRowID {
chunks.append("WITHOUT ROWID")
}
statements.append(chunks.joined(separator: " "))
}
let indexStatements = try columns
.compactMap { $0.indexDefinition(in: name, ifNotExists: ifNotExists) }
.map { try $0.sql(db) }
statements.append(contentsOf: indexStatements)
return statements.joined(separator: "; ")
}
}
/// The TableAlteration class lets you alter database tables.
///
/// You don't create instances of this class. Instead, you use the Database
/// `alter(table:)` method:
///
/// try db.alter(table: "player") { t in // t is TableAlteration
/// t.add(column: ...)
/// }
///
/// See https://www.sqlite.org/lang_altertable.html
public final class TableAlteration {
private let name: String
private enum TableAlterationKind {
case add(ColumnDefinition)
case rename(old: String, new: String)
}
private var alterations: [TableAlterationKind] = []
init(name: String) {
self.name = name
}
/// Appends a column to the table.
///
/// try db.alter(table: "player") { t in
/// t.add(column: "url", .text)
/// }
///
/// See https://www.sqlite.org/lang_altertable.html
///
/// - parameter name: the column name.
/// - parameter type: the column type.
/// - returns: An ColumnDefinition that allows you to refine the
/// column definition.
@discardableResult
public func add(column name: String, _ type: Database.ColumnType? = nil) -> ColumnDefinition {
let column = ColumnDefinition(name: name, type: type)
alterations.append(.add(column))
return column
}
#if GRDBCUSTOMSQLITE || GRDBCIPHER
/// Renames a column in a table.
///
/// try db.alter(table: "player") { t in
/// t.rename(column: "url", to: "homeURL")
/// }
///
/// See https://www.sqlite.org/lang_altertable.html
///
/// - parameter name: the column name to rename.
/// - parameter newName: the new name of the column.
public func rename(column name: String, to newName: String) {
_rename(column: name, to: newName)
}
#else
/// Renames a column in a table.
///
/// try db.alter(table: "player") { t in
/// t.rename(column: "url", to: "homeURL")
/// }
///
/// See https://www.sqlite.org/lang_altertable.html
///
/// - parameter name: the column name to rename.
/// - parameter newName: the new name of the column.
@available(iOS 13.0, tvOS 13.0, watchOS 6.0, *)
public func rename(column name: String, to newName: String) {
_rename(column: name, to: newName)
}
#endif
private func _rename(column name: String, to newName: String) {
alterations.append(.rename(old: name, new: newName))
}
fileprivate func sql(_ db: Database) throws -> String {
var statements: [String] = []
for alteration in alterations {
switch alteration {
case let .add(column):
var chunks: [String] = []
chunks.append("ALTER TABLE")
chunks.append(name.quotedDatabaseIdentifier)
chunks.append("ADD COLUMN")
try chunks.append(column.sql(db, tableName: name, primaryKeyColumns: nil))
let statement = chunks.joined(separator: " ")
statements.append(statement)
if let indexDefinition = column.indexDefinition(in: name, ifNotExists: false) {
try statements.append(indexDefinition.sql(db))
}
case let .rename(oldName, newName):
var chunks: [String] = []
chunks.append("ALTER TABLE")
chunks.append(name.quotedDatabaseIdentifier)
chunks.append("RENAME COLUMN")
chunks.append(oldName.quotedDatabaseIdentifier)
chunks.append("TO")
chunks.append(newName.quotedDatabaseIdentifier)
let statement = chunks.joined(separator: " ")
statements.append(statement)
}
}
return statements.joined(separator: "; ")
}
}
/// The ColumnDefinition class lets you refine a table column.
///
/// You get instances of this class when you create or alter a database table:
///
/// try db.create(table: "player") { t in
/// t.column(...) // ColumnDefinition
/// }
///
/// try db.alter(table: "player") { t in
/// t.add(column: ...) // ColumnDefinition
/// }
///
/// See https://www.sqlite.org/lang_createtable.html and
/// https://www.sqlite.org/lang_altertable.html
public final class ColumnDefinition {
enum Index {
case none
case index
case unique(Database.ConflictResolution)
}
private struct ForeignKeyConstraint {
var table: String
var column: String?
var deleteAction: Database.ForeignKeyAction?
var updateAction: Database.ForeignKeyAction?
var deferred: Bool
}
/// The `GeneratedColumnQualification` enum defines whether a generated
/// column sis virtual or stored.
///
/// See https://sqlite.org/gencol.html#virtual_versus_stored_columns
public enum GeneratedColumnQualification {
case virtual
case stored
}
private struct GeneratedColumnConstraint {
var expression: SQLExpression
var qualification: GeneratedColumnQualification
}
fileprivate let name: String
private let type: Database.ColumnType?
fileprivate var primaryKey: (conflictResolution: Database.ConflictResolution?, autoincrement: Bool)?
private var index: Index = .none
private var notNullConflictResolution: Database.ConflictResolution?
private var checkConstraints: [SQLExpression] = []
private var foreignKeyConstraints: [ForeignKeyConstraint] = []
private var defaultExpression: SQLExpression?
private var collationName: String?
private var generatedColumnConstraint: GeneratedColumnConstraint?
init(name: String, type: Database.ColumnType?) {
self.name = name
self.type = type
}
/// Adds a primary key constraint on the column.
///
/// try db.create(table: "player") { t in
/// t.column("id", .integer).primaryKey()
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#primkeyconst and
/// https://www.sqlite.org/lang_createtable.html#rowid
///
/// - parameters:
/// - conflictResolution: An optional conflict resolution
/// (see https://www.sqlite.org/lang_conflict.html).
/// - autoincrement: If true, the primary key is autoincremented.
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func primaryKey(
onConflict conflictResolution: Database.ConflictResolution? = nil,
autoincrement: Bool = false)
-> Self
{
primaryKey = (conflictResolution: conflictResolution, autoincrement: autoincrement)
return self
}
/// Adds a NOT NULL constraint on the column.
///
/// try db.create(table: "player") { t in
/// t.column("name", .text).notNull()
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#notnullconst
///
/// - parameter conflictResolution: An optional conflict resolution
/// (see https://www.sqlite.org/lang_conflict.html).
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func notNull(onConflict conflictResolution: Database.ConflictResolution? = nil) -> Self {
notNullConflictResolution = conflictResolution ?? .abort
return self
}
/// Adds a UNIQUE constraint on the column.
///
/// try db.create(table: "player") { t in
/// t.column("email", .text).unique()
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#uniqueconst
///
/// - parameter conflictResolution: An optional conflict resolution
/// (see https://www.sqlite.org/lang_conflict.html).
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func unique(onConflict conflictResolution: Database.ConflictResolution? = nil) -> Self {
index = .unique(conflictResolution ?? .abort)
return self
}
/// Adds an index of the column.
///
/// try db.create(table: "player") { t in
/// t.column("email", .text).indexed()
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#uniqueconst
///
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func indexed() -> Self {
if case .none = index {
self.index = .index
}
return self
}
/// Adds a CHECK constraint on the column.
///
/// try db.create(table: "player") { t in
/// t.column("name", .text).check { length($0) > 0 }
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#ckconst
///
/// - parameter condition: A closure whose argument is an Column that
/// represents the defined column, and returns the expression to check.
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func check(_ condition: (Column) -> SQLExpressible) -> Self {
checkConstraints.append(condition(Column(name)).sqlExpression)
return self
}
/// Adds a CHECK constraint on the column.
///
/// try db.create(table: "player") { t in
/// t.column("name", .text).check(sql: "LENGTH(name) > 0")
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#ckconst
///
/// - parameter sql: An SQL snippet.
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func check(sql: String) -> Self {
checkConstraints.append(SQLLiteral(sql: sql).sqlExpression)
return self
}
/// Defines the default column value.
///
/// try db.create(table: "player") { t in
/// t.column("name", .text).defaults(to: "Anonymous")
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#dfltval
///
/// - parameter value: A DatabaseValueConvertible value.
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func defaults(to value: DatabaseValueConvertible) -> Self {
defaultExpression = value.sqlExpression
return self
}
/// Defines the default column value.
///
/// try db.create(table: "player") { t in
/// t.column("creationDate", .DateTime).defaults(sql: "CURRENT_TIMESTAMP")
/// }
///
/// See https://www.sqlite.org/lang_createtable.html#dfltval
///
/// - parameter sql: An SQL snippet.
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func defaults(sql: String) -> Self {
defaultExpression = SQLLiteral(sql: sql).sqlExpression
return self
}
/// Defines the default column collation.
///
/// try db.create(table: "player") { t in
/// t.column("email", .text).collate(.nocase)
/// }
///
/// See https://www.sqlite.org/datatype3.html#collation
///
/// - parameter collation: An Database.CollationName.
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func collate(_ collation: Database.CollationName) -> Self {
collationName = collation.rawValue
return self
}
/// Defines the default column collation.
///
/// try db.create(table: "player") { t in
/// t.column("name", .text).collate(.localizedCaseInsensitiveCompare)
/// }
///
/// See https://www.sqlite.org/datatype3.html#collation
///
/// - parameter collation: A custom DatabaseCollation.
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func collate(_ collation: DatabaseCollation) -> Self {
collationName = collation.name
return self
}
#if GRDBCUSTOMSQLITE
/// Defines the column as a generated column.
///
/// try db.create(table: "player") { t in
/// t.column("id", .integer).primaryKey()
/// t.column("score", .integer).notNull()
/// t.column("bonus", .integer).notNull()
/// t.column("totalScore", .integer).generatedAs(sql: "score + bonus", .stored)
/// }
///
/// See https://sqlite.org/gencol.html. Note particularly the limitations of
/// generated columns, e.g. they may not have a default value.
///
/// - parameters:
/// - sql: An SQL expression.
/// - qualification: The generated column's qualification, which
/// defaults to `.virtual`.
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func generatedAs(
sql: String,
_ qualification: GeneratedColumnQualification = .virtual)
-> Self
{
let expression = SQLLiteral(sql: sql).sqlExpression
generatedColumnConstraint = GeneratedColumnConstraint(
expression: expression,
qualification: qualification)
return self
}
/// Defines the column as a generated column.
///
/// try db.create(table: "player") { t in
/// t.column("id", .integer).primaryKey()
/// t.column("score", .integer).notNull()
/// t.column("bonus", .integer).notNull()
/// t.column("totalScore", .integer).generatedAs(Column("score") + Column("bonus"), .stored)
/// }
///
/// See https://sqlite.org/gencol.html. Note particularly the limitations of
/// generated columns, e.g. they may not have a default value.
///
/// - parameters:
/// - expression: The generated expression.
/// - qualification: The generated column's qualification, which
/// defaults to `.virtual`.
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func generatedAs(
_ expression: SQLExpressible,
_ qualification: GeneratedColumnQualification = .virtual)
-> Self
{
generatedColumnConstraint = GeneratedColumnConstraint(
expression: expression.sqlExpression,
qualification: qualification)
return self
}
#endif
/// Defines a foreign key.
///
/// try db.create(table: "book") { t in
/// t.column("authorId", .integer).references("author", onDelete: .cascade)
/// }
///
/// See https://www.sqlite.org/foreignkeys.html
///
/// - parameters
/// - table: The referenced table.
/// - column: The column in the referenced table. If not specified, the
/// column of the primary key of the referenced table is used.
/// - deleteAction: Optional action when the referenced row is deleted.
/// - updateAction: Optional action when the referenced row is updated.
/// - deferred: If true, defines a deferred foreign key constraint.
/// See https://www.sqlite.org/foreignkeys.html#fk_deferred.
/// - returns: Self so that you can further refine the column definition.
@discardableResult
public func references(
_ table: String,
column: String? = nil,
onDelete deleteAction: Database.ForeignKeyAction? = nil,
onUpdate updateAction: Database.ForeignKeyAction? = nil,
deferred: Bool = false) -> Self
{
foreignKeyConstraints.append(ForeignKeyConstraint(
table: table,
column: column,
deleteAction: deleteAction,
updateAction: updateAction,
deferred: deferred))
return self
}
fileprivate func sql(_ db: Database, tableName: String, primaryKeyColumns: [String]?) throws -> String {
var chunks: [String] = []
chunks.append(name.quotedDatabaseIdentifier)
if let type = type {
chunks.append(type.rawValue)
}
if let (conflictResolution, autoincrement) = primaryKey {
chunks.append("PRIMARY KEY")
if let conflictResolution = conflictResolution {
chunks.append("ON CONFLICT")
chunks.append(conflictResolution.rawValue)
}
if autoincrement {
chunks.append("AUTOINCREMENT")
}
}
switch notNullConflictResolution {
case .none:
break
case .abort?:
chunks.append("NOT NULL")
case let conflictResolution?:
chunks.append("NOT NULL ON CONFLICT")
chunks.append(conflictResolution.rawValue)
}
switch index {
case .none:
break
case .unique(let conflictResolution):
switch conflictResolution {
case .abort:
chunks.append("UNIQUE")
default:
chunks.append("UNIQUE ON CONFLICT")
chunks.append(conflictResolution.rawValue)
}
case .index:
break
}
for checkConstraint in checkConstraints {
try chunks.append("CHECK (\(checkConstraint.quotedSQL(db)))")
}
if let defaultExpression = defaultExpression {
try chunks.append("DEFAULT \(defaultExpression.quotedSQL(db))")
}
if let collationName = collationName {
chunks.append("COLLATE")
chunks.append(collationName)
}
for constraint in foreignKeyConstraints {
chunks.append("REFERENCES")
if let column = constraint.column {
// explicit reference
chunks.append("\(constraint.table.quotedDatabaseIdentifier)(\(column.quotedDatabaseIdentifier))")
} else if constraint.table.lowercased() == tableName.lowercased() {
// implicit autoreference
let primaryKeyColumns = try primaryKeyColumns ?? db.primaryKey(constraint.table).columns
chunks.append("""
\(constraint.table.quotedDatabaseIdentifier)(\
\(primaryKeyColumns.map(\.quotedDatabaseIdentifier).joined(separator: ", "))\
)
""")
} else {
// implicit external reference
let primaryKeyColumns = try db.primaryKey(constraint.table).columns
chunks.append("""
\(constraint.table.quotedDatabaseIdentifier)(\
\(primaryKeyColumns.map(\.quotedDatabaseIdentifier).joined(separator: ", "))\
)
""")
}
if let deleteAction = constraint.deleteAction {
chunks.append("ON DELETE")
chunks.append(deleteAction.rawValue)
}
if let updateAction = constraint.updateAction {
chunks.append("ON UPDATE")
chunks.append(updateAction.rawValue)
}
if constraint.deferred {
chunks.append("DEFERRABLE INITIALLY DEFERRED")
}
}
if let constraint = generatedColumnConstraint {
try chunks.append("GENERATED ALWAYS AS (\(constraint.expression.quotedSQL(db)))")
let qualificationLiteral: String
switch constraint.qualification {
case .stored:
qualificationLiteral = "STORED"
case .virtual:
qualificationLiteral = "VIRTUAL"
}
chunks.append(qualificationLiteral)
}
return chunks.joined(separator: " ")
}
fileprivate func indexDefinition(in table: String, ifNotExists: Bool) -> IndexDefinition? {
switch index {
case .none: return nil
case .unique: return nil
case .index:
return IndexDefinition(
name: "\(table)_on_\(name)",
table: table,
columns: [name],
unique: false,
ifNotExists: ifNotExists,
condition: nil)
}
}
}
private struct IndexDefinition {
let name: String
let table: String
let columns: [String]
let unique: Bool
let ifNotExists: Bool
let condition: SQLExpression?
func sql(_ db: Database) throws -> String {
var chunks: [String] = []
chunks.append("CREATE")
if unique {
chunks.append("UNIQUE")
}
chunks.append("INDEX")
if ifNotExists {
chunks.append("IF NOT EXISTS")
}
chunks.append(name.quotedDatabaseIdentifier)
chunks.append("ON")
chunks.append("""
\(table.quotedDatabaseIdentifier)(\
\(columns.map(\.quotedDatabaseIdentifier).joined(separator: ", "))\
)
""")
if let condition = condition {
try chunks.append("WHERE \(condition.quotedSQL(db))")
}
return chunks.joined(separator: " ")
}
}