mirror of
https://github.com/groue/GRDB.swift
synced 2026-11-22 04:00:39 +00:00
1072 lines
40 KiB
Swift
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: " ")
|
|
}
|
|
}
|