1186 lines
46 KiB
Swift
1186 lines
46 KiB
Swift
import XCTest
|
|
import GRDB
|
|
|
|
class TableDefinitionTests: GRDBTestCase {
|
|
|
|
func testCreateTable() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
// Simple table creation
|
|
try db.create(table: "test") { t in
|
|
t.primaryKey("id", .integer)
|
|
t.column("name", .text)
|
|
}
|
|
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"id" INTEGER PRIMARY KEY, \
|
|
"name" TEXT\
|
|
)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testTableCreationOptions() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test", temporary: true, ifNotExists: true, withoutRowID: true) { t in
|
|
t.primaryKey("id", .integer)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS "test" (\
|
|
"id" INTEGER PRIMARY KEY\
|
|
) WITHOUT ROWID
|
|
""")
|
|
}
|
|
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test2", options: [.temporary, .ifNotExists, .withoutRowID]) { t in
|
|
t.primaryKey("id", .integer)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS "test2" (\
|
|
"id" INTEGER PRIMARY KEY\
|
|
) WITHOUT ROWID
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testStrictTableCreationOption() throws {
|
|
guard sqlite3_libversion_number() >= 3037000 else {
|
|
throw XCTSkip("STRICT tables are not available")
|
|
}
|
|
#if !GRDBCUSTOMSQLITE && !GRDBCIPHER
|
|
guard #available(iOS 15.4, macOS 12.4, tvOS 15.4, watchOS 8.5, *) else {
|
|
throw XCTSkip("STRICT tables are not available")
|
|
}
|
|
#endif
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test3", options: [.strict]) { t in
|
|
t.primaryKey("id", .integer)
|
|
t.column("a", .integer)
|
|
t.column("b", .real)
|
|
t.column("c", .text)
|
|
t.column("d", .blob)
|
|
t.column("e", .any)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test3" (\
|
|
"id" INTEGER PRIMARY KEY, \
|
|
"a" INTEGER, \
|
|
"b" REAL, \
|
|
"c" TEXT, \
|
|
"d" BLOB, \
|
|
"e" ANY\
|
|
) STRICT
|
|
""")
|
|
|
|
do {
|
|
try db.execute(sql: "INSERT INTO test3 (id, a) VALUES (1, 'foo')")
|
|
XCTFail("Expected DatabaseError.SQLITE_CONSTRAINT_DATATYPE")
|
|
} catch DatabaseError.SQLITE_CONSTRAINT_DATATYPE {
|
|
}
|
|
}
|
|
}
|
|
|
|
func testColumnLiteral() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
// Simple table creation
|
|
try db.create(table: "test") { t in
|
|
t.column(sql: "a TEXT")
|
|
t.column(literal: "b TEXT DEFAULT \("O'Brien")")
|
|
}
|
|
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (a TEXT, b TEXT DEFAULT 'O''Brien')
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testUntypedColumn() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
// Simple table creation
|
|
try db.create(table: "test") { t in
|
|
t.column("a")
|
|
t.column("b")
|
|
}
|
|
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" ("a", "b")
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testAutoIncrementedPrimaryKey() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inTransaction { db in
|
|
try db.create(table: "test") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"id" INTEGER PRIMARY KEY AUTOINCREMENT\
|
|
)
|
|
""")
|
|
return .rollback
|
|
}
|
|
try dbQueue.inTransaction { db in
|
|
try db.create(table: "test") { t in
|
|
t.autoIncrementedPrimaryKey("id", onConflict: .fail)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"id" INTEGER PRIMARY KEY ON CONFLICT FAIL AUTOINCREMENT\
|
|
)
|
|
""")
|
|
return .rollback
|
|
}
|
|
}
|
|
|
|
func testColumnPrimaryKeyOptions() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inTransaction { db in
|
|
try db.create(table: "test") { t in
|
|
t.primaryKey("id", .integer, onConflict: .fail)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"id" INTEGER PRIMARY KEY ON CONFLICT FAIL\
|
|
)
|
|
""")
|
|
return .rollback
|
|
}
|
|
try dbQueue.inTransaction { db in
|
|
try db.create(table: "test") { t in
|
|
// legacy api
|
|
t.column("id", .integer).primaryKey(autoincrement: true)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"id" INTEGER PRIMARY KEY AUTOINCREMENT\
|
|
)
|
|
""")
|
|
return .rollback
|
|
}
|
|
}
|
|
|
|
func testColumnNotNull() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .integer).notNull()
|
|
t.column("b", .integer).notNull(onConflict: .abort)
|
|
t.column("c", .integer).notNull(onConflict: .rollback)
|
|
t.column("d", .integer).notNull(onConflict: .fail)
|
|
t.column("e", .integer).notNull(onConflict: .ignore)
|
|
t.column("f", .integer).notNull(onConflict: .replace)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"a" INTEGER NOT NULL, \
|
|
"b" INTEGER NOT NULL, \
|
|
"c" INTEGER NOT NULL ON CONFLICT ROLLBACK, \
|
|
"d" INTEGER NOT NULL ON CONFLICT FAIL, \
|
|
"e" INTEGER NOT NULL ON CONFLICT IGNORE, \
|
|
"f" INTEGER NOT NULL ON CONFLICT REPLACE\
|
|
)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testColumnIndexed() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
sqlQueries.removeAll()
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .integer).indexed()
|
|
t.column("b", .integer).indexed()
|
|
}
|
|
assertEqualSQL(sqlQueries[0], "CREATE TABLE \"test\" (\"a\" INTEGER, \"b\" INTEGER)")
|
|
assertEqualSQL(sqlQueries[1], "CREATE INDEX \"test_on_a\" ON \"test\"(\"a\")")
|
|
assertEqualSQL(sqlQueries[2], "CREATE INDEX \"test_on_b\" ON \"test\"(\"b\")")
|
|
}
|
|
}
|
|
|
|
// Regression test for https://github.com/groue/GRDB.swift/issues/838
|
|
func testColumnIndexedInheritsIfNotExistsFlag() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
sqlQueries.removeAll()
|
|
try db.create(table: "test", options: [.ifNotExists]) { t in
|
|
t.column("a", .integer).indexed()
|
|
t.column("b", .integer).indexed()
|
|
}
|
|
assertEqualSQL(sqlQueries[0], "CREATE TABLE IF NOT EXISTS \"test\" (\"a\" INTEGER, \"b\" INTEGER)")
|
|
assertEqualSQL(sqlQueries[1], "CREATE INDEX IF NOT EXISTS \"test_on_a\" ON \"test\"(\"a\")")
|
|
assertEqualSQL(sqlQueries[2], "CREATE INDEX IF NOT EXISTS \"test_on_b\" ON \"test\"(\"b\")")
|
|
}
|
|
}
|
|
|
|
func testColumnUnique() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .integer).unique()
|
|
t.column("b", .integer).unique(onConflict: .abort)
|
|
t.column("c", .integer).unique(onConflict: .rollback)
|
|
t.column("d", .integer).unique(onConflict: .fail)
|
|
t.column("e", .integer).unique(onConflict: .ignore)
|
|
t.column("f", .integer).unique(onConflict: .replace)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"a" INTEGER UNIQUE, \
|
|
"b" INTEGER UNIQUE, \
|
|
"c" INTEGER UNIQUE ON CONFLICT ROLLBACK, \
|
|
"d" INTEGER UNIQUE ON CONFLICT FAIL, \
|
|
"e" INTEGER UNIQUE ON CONFLICT IGNORE, \
|
|
"f" INTEGER UNIQUE ON CONFLICT REPLACE\
|
|
)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testColumnCheck() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .integer).check { $0 > 0 }
|
|
t.column("b", .integer).check(sql: "b <> 2")
|
|
t.column("c", .integer).check { $0 > 0 }.check { $0 < 10 }
|
|
t.column("d", .integer).check { $0 != Column("c") }
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"a" INTEGER CHECK ("a" > 0), \
|
|
"b" INTEGER CHECK (b <> 2), \
|
|
"c" INTEGER CHECK ("c" > 0) CHECK ("c" < 10), \
|
|
"d" INTEGER CHECK ("d" <> "c")\
|
|
)
|
|
""")
|
|
|
|
// Sanity check
|
|
try db.execute(sql: "INSERT INTO test (a, b, c) VALUES (1, 0, 1)")
|
|
do {
|
|
try db.execute(sql: "INSERT INTO test (a, b, c) VALUES (0, 0, 1)")
|
|
XCTFail()
|
|
} catch {
|
|
}
|
|
}
|
|
}
|
|
|
|
func testColumnDefault() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .integer).defaults(to: 1)
|
|
t.column("b", .integer).defaults(to: 1.0)
|
|
t.column("c", .integer).defaults(to: "'fooéı👨👨🏿🇫🇷🇨🇮'")
|
|
t.column("d", .integer).defaults(to: "foo".data(using: .utf8)!)
|
|
t.column("e", .integer).defaults(sql: "NULL")
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"a" INTEGER DEFAULT 1, \
|
|
"b" INTEGER DEFAULT 1.0, \
|
|
"c" INTEGER DEFAULT '''fooéı👨👨🏿🇫🇷🇨🇮''', \
|
|
"d" INTEGER DEFAULT X'666F6F', \
|
|
"e" INTEGER DEFAULT NULL\
|
|
)
|
|
""")
|
|
|
|
// Sanity check
|
|
try db.execute(sql: "INSERT INTO test DEFAULT VALUES")
|
|
XCTAssertEqual(try Int.fetchOne(db, sql: "SELECT a FROM test")!, 1)
|
|
XCTAssertEqual(try String.fetchOne(db, sql: "SELECT c FROM test")!, "'fooéı👨👨🏿🇫🇷🇨🇮'")
|
|
}
|
|
}
|
|
|
|
func testColumnCollation() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inTransaction { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("name", .text).collate(.nocase)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"name" TEXT COLLATE NOCASE\
|
|
)
|
|
""")
|
|
return .rollback
|
|
}
|
|
|
|
try dbQueue.inTransaction { db in
|
|
let collation = DatabaseCollation("foo") { (lhs, rhs) in .orderedSame }
|
|
db.add(collation: collation)
|
|
try db.create(table: "test") { t in
|
|
t.column("name", .text).collate(collation)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"name" TEXT COLLATE foo\
|
|
)
|
|
""")
|
|
return .rollback
|
|
}
|
|
}
|
|
|
|
func testColumnReference() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "parent") { t in
|
|
t.primaryKey("name", .text)
|
|
t.column("email", .text).unique()
|
|
}
|
|
try db.create(table: "pkless") { t in
|
|
t.column("email", .text)
|
|
}
|
|
try db.create(table: "child") { t in
|
|
t.column("parentName", .text).references("parent", onDelete: .cascade, onUpdate: .cascade)
|
|
t.column("parentEmail", .text).references("parent", column: "email", onDelete: .restrict, deferred: true)
|
|
t.column("weird", .text).references("parent", column: "name").references("parent", column: "email")
|
|
t.column("pklessRowId", .text).references("pkless")
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "child" (\
|
|
"parentName" TEXT REFERENCES "parent"("name") ON DELETE CASCADE ON UPDATE CASCADE, \
|
|
"parentEmail" TEXT REFERENCES "parent"("email") ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED, \
|
|
"weird" TEXT REFERENCES "parent"("name") REFERENCES "parent"("email"), \
|
|
"pklessRowId" TEXT REFERENCES "pkless"("rowid")\
|
|
)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testColumnGeneratedAs() throws {
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
guard sqlite3_libversion_number() >= 3031000 else {
|
|
throw XCTSkip("Generated columns are not available")
|
|
}
|
|
#else
|
|
guard #available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) else {
|
|
throw XCTSkip("Generated columns are not available")
|
|
}
|
|
#endif
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inTransaction { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .integer)
|
|
t.column("b", .integer)
|
|
t.column("c", .text)
|
|
t.column("d", .integer).generatedAs(sql: "a*abs(b)", .virtual)
|
|
t.column("e", .text).generatedAs(sql: "substr(c,b,b+1)", .stored)
|
|
t.column("f").generatedAs(sql: "e")
|
|
t.column("g").generatedAs(Column("a") * 2)
|
|
t.column("h").generatedAs("O'Brien")
|
|
}
|
|
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"a" INTEGER, \
|
|
"b" INTEGER, \
|
|
"c" TEXT, \
|
|
"d" INTEGER GENERATED ALWAYS AS (a*abs(b)) VIRTUAL, \
|
|
"e" TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED, \
|
|
"f" GENERATED ALWAYS AS (e) VIRTUAL, \
|
|
"g" GENERATED ALWAYS AS ("a" * 2) VIRTUAL, \
|
|
"h" GENERATED ALWAYS AS ('O''Brien') VIRTUAL\
|
|
)
|
|
""")
|
|
return .rollback
|
|
}
|
|
}
|
|
|
|
func testTablePrimaryKey() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inTransaction { db in
|
|
try db.create(table: "test") { t in
|
|
// Legacy api
|
|
t.primaryKey(["a", "b"])
|
|
t.column("a", .text)
|
|
t.column("b", .text)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"a" TEXT, \
|
|
"b" TEXT, \
|
|
PRIMARY KEY ("a", "b")\
|
|
)
|
|
""")
|
|
return .rollback
|
|
}
|
|
try dbQueue.inTransaction { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("regular1")
|
|
t.primaryKey {
|
|
t.column("a", .text)
|
|
t.column("b", .text)
|
|
}
|
|
t.column("regular2")
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"regular1", \
|
|
"a" TEXT NOT NULL, \
|
|
"b" TEXT NOT NULL, \
|
|
"regular2", \
|
|
PRIMARY KEY ("a", "b")\
|
|
)
|
|
""")
|
|
return .rollback
|
|
}
|
|
try dbQueue.inTransaction { db in
|
|
try db.create(table: "test") { t in
|
|
// Legacy api
|
|
t.primaryKey(["a", "b"], onConflict: .fail)
|
|
t.column("a", .text)
|
|
t.column("b", .text)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"a" TEXT, \
|
|
"b" TEXT, \
|
|
PRIMARY KEY ("a", "b") ON CONFLICT FAIL\
|
|
)
|
|
""")
|
|
return .rollback
|
|
}
|
|
try dbQueue.inTransaction { db in
|
|
try db.create(table: "test") { t in
|
|
t.primaryKey(onConflict: .fail) {
|
|
t.column("a", .text).defaults(to: "O'Reilly")
|
|
t.column("b", .text)
|
|
}
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"a" TEXT NOT NULL DEFAULT 'O''Reilly', \
|
|
"b" TEXT NOT NULL, \
|
|
PRIMARY KEY ("a", "b") ON CONFLICT FAIL\
|
|
)
|
|
""")
|
|
return .rollback
|
|
}
|
|
}
|
|
|
|
func testTableUniqueKey() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.uniqueKey(["a"])
|
|
t.uniqueKey(["b", "c"], onConflict: .fail)
|
|
t.column("a", .text)
|
|
t.column("b", .text)
|
|
t.column("c", .text)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"a" TEXT, \
|
|
"b" TEXT, \
|
|
"c" TEXT, \
|
|
UNIQUE ("a"), \
|
|
UNIQUE ("b", "c") ON CONFLICT FAIL\
|
|
)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testTableForeignKey() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "parent") { t in
|
|
t.primaryKey {
|
|
t.column("a", .text)
|
|
t.column("b", .text)
|
|
}
|
|
}
|
|
try db.create(table: "child") { t in
|
|
t.foreignKey(["c", "d"], references: "parent", onDelete: .cascade, onUpdate: .cascade)
|
|
t.foreignKey(["d", "e"], references: "parent", columns: ["b", "a"], onDelete: .restrict, deferred: true)
|
|
t.column("c", .text)
|
|
t.column("d", .text)
|
|
t.column("e", .text)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "child" (\
|
|
"c" TEXT, \
|
|
"d" TEXT, \
|
|
"e" TEXT, \
|
|
FOREIGN KEY ("c", "d") REFERENCES "parent"("a", "b") ON DELETE CASCADE ON UPDATE CASCADE, \
|
|
FOREIGN KEY ("d", "e") REFERENCES "parent"("b", "a") ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED\
|
|
)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testTableCheck() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.check(Column("a") + Column("b") < 10)
|
|
t.check(sql: "a + b < 10")
|
|
t.column("a", .integer)
|
|
t.column("b", .integer)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"a" INTEGER, \
|
|
"b" INTEGER, \
|
|
CHECK (("a" + "b") < 10), \
|
|
CHECK (a + b < 10)\
|
|
)
|
|
""")
|
|
|
|
// Sanity check
|
|
try db.execute(sql: "INSERT INTO test (a, b) VALUES (1, 0)")
|
|
do {
|
|
try db.execute(sql: "INSERT INTO test (a, b) VALUES (5, 5)")
|
|
XCTFail()
|
|
} catch {
|
|
}
|
|
}
|
|
}
|
|
|
|
@available(*, deprecated)
|
|
func testTableCheck_deprecated() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
// Deprecated because this does not what the user means!
|
|
t.check("a < b")
|
|
t.column("a", .integer)
|
|
t.column("b", .integer)
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"a" INTEGER, \
|
|
"b" INTEGER, \
|
|
CHECK ('a < b')\
|
|
)
|
|
""")
|
|
|
|
// Sanity check: insert should fail because the 'a < b' string is false for SQLite
|
|
do {
|
|
try db.execute(sql: "INSERT INTO test (a, b) VALUES (0, 1)")
|
|
XCTFail()
|
|
} catch {
|
|
}
|
|
}
|
|
}
|
|
|
|
func testConstraintLiteral() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
// Simple table creation
|
|
try db.create(table: "test") { t in
|
|
t.constraint(sql: "CHECK (a + b < 10)")
|
|
t.constraint(sql: "CHECK (a + b < \(20))")
|
|
t.column("a", .integer)
|
|
t.column("b", .integer)
|
|
}
|
|
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test" (\
|
|
"a" INTEGER, \
|
|
"b" INTEGER, \
|
|
CHECK (a + b < 10), \
|
|
CHECK (a + b < 20)\
|
|
)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testAutoReferences() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test1") { t in
|
|
t.primaryKey("id", .integer)
|
|
t.column("id2", .integer).references("test1")
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test1" (\
|
|
"id" INTEGER PRIMARY KEY, \
|
|
"id2" INTEGER REFERENCES "test1"("id")\
|
|
)
|
|
""")
|
|
|
|
try db.create(table: "test2_legacy") { t in
|
|
// Legacy api
|
|
t.column("id", .integer)
|
|
t.column("id2", .integer).references("test2_legacy")
|
|
t.primaryKey(["id"])
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test2_legacy" (\
|
|
"id" INTEGER, \
|
|
"id2" INTEGER REFERENCES "test2_legacy"("id"), \
|
|
PRIMARY KEY ("id")\
|
|
)
|
|
""")
|
|
|
|
try db.create(table: "test2") { t in
|
|
t.column("id2", .integer).references("test2")
|
|
t.primaryKey {
|
|
t.column("id", .integer)
|
|
}
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test2" (\
|
|
"id2" INTEGER REFERENCES "test2"("id"), \
|
|
"id" INTEGER NOT NULL, \
|
|
PRIMARY KEY ("id")\
|
|
)
|
|
""")
|
|
|
|
try db.create(table: "test3Legacy") { t in
|
|
t.column("a", .integer)
|
|
t.column("b", .integer)
|
|
t.column("c", .integer)
|
|
t.column("d", .integer)
|
|
t.foreignKey(["c", "d"], references: "test3Legacy")
|
|
t.primaryKey(["a", "b"])
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test3Legacy" (\
|
|
"a" INTEGER, \
|
|
"b" INTEGER, \
|
|
"c" INTEGER, \
|
|
"d" INTEGER, \
|
|
PRIMARY KEY ("a", "b"), \
|
|
FOREIGN KEY ("c", "d") REFERENCES "test3Legacy"("a", "b")\
|
|
)
|
|
""")
|
|
|
|
try db.create(table: "test3") { t in
|
|
t.column("c", .integer)
|
|
t.column("d", .integer)
|
|
t.foreignKey(["c", "d"], references: "test3")
|
|
t.primaryKey {
|
|
t.column("a", .integer)
|
|
t.column("b", .integer)
|
|
}
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test3" (\
|
|
"c" INTEGER, \
|
|
"d" INTEGER, \
|
|
"a" INTEGER NOT NULL, \
|
|
"b" INTEGER NOT NULL, \
|
|
PRIMARY KEY ("a", "b"), \
|
|
FOREIGN KEY ("c", "d") REFERENCES "test3"("a", "b")\
|
|
)
|
|
""")
|
|
|
|
try db.create(table: "test4") { t in
|
|
t.column("parent", .integer).references("test4")
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test4" (\
|
|
"parent" INTEGER REFERENCES "test4"("rowid")\
|
|
)
|
|
""")
|
|
|
|
try db.create(table: "test5") { t in
|
|
t.column("parent", .integer)
|
|
t.foreignKey(["parent"], references: "test5")
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TABLE "test5" (\
|
|
"parent" INTEGER, \
|
|
FOREIGN KEY ("parent") REFERENCES "test5"("rowid")\
|
|
)
|
|
""")
|
|
}
|
|
}
|
|
|
|
func testRenameTable() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .text)
|
|
}
|
|
XCTAssertTrue(try db.tableExists("test"))
|
|
XCTAssertEqual(try db.columns(in: "test").count, 1)
|
|
|
|
try db.rename(table: "test", to: "foo")
|
|
assertEqualSQL(lastSQLQuery!, "ALTER TABLE \"test\" RENAME TO \"foo\"")
|
|
XCTAssertFalse(try db.tableExists("test"))
|
|
XCTAssertTrue(try db.tableExists("foo"))
|
|
XCTAssertEqual(try db.columns(in: "foo").count, 1)
|
|
}
|
|
}
|
|
|
|
func testAlterTable() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .text)
|
|
}
|
|
try db.create(table: "alt") { t in
|
|
t.column("a", .text)
|
|
}
|
|
|
|
sqlQueries.removeAll()
|
|
try db.alter(table: "test") { t in
|
|
t.add(column: "b", .text)
|
|
t.add(column: "c", .integer).notNull().defaults(to: 1)
|
|
t.add(column: "d", .text).references("alt")
|
|
t.add(column: "e")
|
|
t.addColumn(sql: "f TEXT")
|
|
t.addColumn(literal: "g TEXT DEFAULT \("O'Brien")")
|
|
}
|
|
|
|
assertEqualSQL(sqlQueries[sqlQueries.count - 6], "ALTER TABLE \"test\" ADD COLUMN \"b\" TEXT")
|
|
assertEqualSQL(sqlQueries[sqlQueries.count - 5], "ALTER TABLE \"test\" ADD COLUMN \"c\" INTEGER NOT NULL DEFAULT 1")
|
|
assertEqualSQL(sqlQueries[sqlQueries.count - 4], "ALTER TABLE \"test\" ADD COLUMN \"d\" TEXT REFERENCES \"alt\"(\"rowid\")")
|
|
assertEqualSQL(sqlQueries[sqlQueries.count - 3], "ALTER TABLE \"test\" ADD COLUMN \"e\"")
|
|
assertEqualSQL(sqlQueries[sqlQueries.count - 2], "ALTER TABLE \"test\" ADD COLUMN f TEXT")
|
|
assertEqualSQL(sqlQueries[sqlQueries.count - 1], "ALTER TABLE \"test\" ADD COLUMN g TEXT DEFAULT 'O''Brien'")
|
|
}
|
|
}
|
|
|
|
func testAlterTableAddAutoReferencingForeignKey() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
do {
|
|
try db.create(table: "hiddenRowIdTable") { t in
|
|
t.column("a", .text)
|
|
}
|
|
|
|
sqlQueries.removeAll()
|
|
try db.alter(table: "hiddenRowIdTable") { t in
|
|
t.add(column: "ref").references("hiddenRowIdTable")
|
|
}
|
|
XCTAssertEqual(lastSQLQuery, """
|
|
ALTER TABLE "hiddenRowIdTable" ADD COLUMN "ref" REFERENCES "hiddenRowIdTable"("rowid")
|
|
""")
|
|
}
|
|
|
|
do {
|
|
try db.create(table: "explicitPrimaryKey") { t in
|
|
t.primaryKey("code", .text)
|
|
t.column("a", .text)
|
|
}
|
|
|
|
sqlQueries.removeAll()
|
|
try db.alter(table: "explicitPrimaryKey") { t in
|
|
t.add(column: "ref").references("explicitPrimaryKey")
|
|
}
|
|
XCTAssertEqual(lastSQLQuery, """
|
|
ALTER TABLE "explicitPrimaryKey" ADD COLUMN "ref" REFERENCES "explicitPrimaryKey"("code")
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
func testAlterTableAddColumnInvalidatesSchemaCache() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .text)
|
|
}
|
|
try XCTAssertEqual(db.columns(in: "test").map(\.name), ["a"])
|
|
|
|
try db.alter(table: "test") { t in
|
|
t.add(column: "b", .text)
|
|
}
|
|
try XCTAssertEqual(db.columns(in: "test").map(\.name), ["a", "b"])
|
|
}
|
|
}
|
|
|
|
func testAlterTableRenameColumn() throws {
|
|
guard sqlite3_libversion_number() >= 3025000 else {
|
|
throw XCTSkip("ALTER TABLE RENAME COLUMN is not available")
|
|
}
|
|
#if !GRDBCUSTOMSQLITE && !GRDBCIPHER
|
|
guard #available(iOS 13, tvOS 13, watchOS 6, *) else {
|
|
throw XCTSkip("ALTER TABLE RENAME COLUMN is not available")
|
|
}
|
|
#endif
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .text)
|
|
}
|
|
|
|
sqlQueries.removeAll()
|
|
try db.alter(table: "test") { t in
|
|
t.rename(column: "a", to: "b")
|
|
t.add(column: "c")
|
|
t.rename(column: "c", to: "d")
|
|
}
|
|
|
|
assertEqualSQL(sqlQueries[sqlQueries.count - 3], "ALTER TABLE \"test\" RENAME COLUMN \"a\" TO \"b\"")
|
|
assertEqualSQL(sqlQueries[sqlQueries.count - 2], "ALTER TABLE \"test\" ADD COLUMN \"c\"")
|
|
assertEqualSQL(sqlQueries[sqlQueries.count - 1], "ALTER TABLE \"test\" RENAME COLUMN \"c\" TO \"d\"")
|
|
}
|
|
}
|
|
|
|
func testAlterTableRenameColumnInvalidatesSchemaCache() throws {
|
|
guard sqlite3_libversion_number() >= 3025000 else {
|
|
throw XCTSkip("ALTER TABLE RENAME COLUMN is not available")
|
|
}
|
|
#if !GRDBCUSTOMSQLITE && !GRDBCIPHER
|
|
guard #available(iOS 13, tvOS 13, watchOS 6, *) else {
|
|
throw XCTSkip("ALTER TABLE RENAME COLUMN is not available")
|
|
}
|
|
#endif
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .text)
|
|
}
|
|
try XCTAssertEqual(db.columns(in: "test").map(\.name), ["a"])
|
|
|
|
try db.alter(table: "test") { t in
|
|
t.rename(column: "a", to: "b")
|
|
}
|
|
try XCTAssertEqual(db.columns(in: "test").map(\.name), ["b"])
|
|
}
|
|
}
|
|
|
|
func testAlterTableAddGeneratedVirtualColumn() throws {
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
guard sqlite3_libversion_number() >= 3031000 else {
|
|
throw XCTSkip("Generated columns are not available")
|
|
}
|
|
#else
|
|
guard #available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) else {
|
|
throw XCTSkip("Generated columns are not available")
|
|
}
|
|
#endif
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .integer)
|
|
t.column("b", .integer)
|
|
t.column("c", .text)
|
|
}
|
|
|
|
sqlQueries.removeAll()
|
|
try db.alter(table: "test") { t in
|
|
t.add(column: "d", .integer).generatedAs(sql: "a*abs(b)", .virtual)
|
|
t.add(column: "e", .text).generatedAs(sql: "substr(c,b,b+1)", .virtual)
|
|
t.add(column: "f").generatedAs(sql: "e", .virtual)
|
|
t.add(column: "g").generatedAs(Column("a") * 2)
|
|
t.add(column: "h").generatedAs("O'Brien")
|
|
}
|
|
|
|
let latestQueries = Array(sqlQueries.suffix(5))
|
|
assertEqualSQL(latestQueries[0], "ALTER TABLE \"test\" ADD COLUMN \"d\" INTEGER GENERATED ALWAYS AS (a*abs(b)) VIRTUAL")
|
|
assertEqualSQL(latestQueries[1], "ALTER TABLE \"test\" ADD COLUMN \"e\" TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) VIRTUAL")
|
|
assertEqualSQL(latestQueries[2], "ALTER TABLE \"test\" ADD COLUMN \"f\" GENERATED ALWAYS AS (e) VIRTUAL")
|
|
assertEqualSQL(latestQueries[3], "ALTER TABLE \"test\" ADD COLUMN \"g\" GENERATED ALWAYS AS (\"a\" * 2) VIRTUAL")
|
|
assertEqualSQL(latestQueries[4], "ALTER TABLE \"test\" ADD COLUMN \"h\" GENERATED ALWAYS AS ('O''Brien') VIRTUAL")
|
|
}
|
|
}
|
|
|
|
func testAlterTableDropColumn() throws {
|
|
guard sqlite3_libversion_number() >= 3035000 else {
|
|
throw XCTSkip("ALTER TABLE DROP COLUMN is not available")
|
|
}
|
|
#if !GRDBCUSTOMSQLITE && !GRDBCIPHER
|
|
guard #available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) else {
|
|
throw XCTSkip("ALTER TABLE DROP COLUMN is not available")
|
|
}
|
|
#endif
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .text)
|
|
t.column("b", .text)
|
|
}
|
|
|
|
sqlQueries.removeAll()
|
|
try db.alter(table: "test") { t in
|
|
t.drop(column: "b")
|
|
}
|
|
assertEqualSQL(lastSQLQuery!, "ALTER TABLE \"test\" DROP COLUMN \"b\"")
|
|
}
|
|
}
|
|
|
|
func testAlterTableDropColumnInvalidatesSchemaCache() throws {
|
|
guard sqlite3_libversion_number() >= 3035000 else {
|
|
throw XCTSkip("ALTER TABLE DROP COLUMN is not available")
|
|
}
|
|
#if !GRDBCUSTOMSQLITE && !GRDBCIPHER
|
|
guard #available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) else {
|
|
throw XCTSkip("ALTER TABLE DROP COLUMN is not available")
|
|
}
|
|
#endif
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.column("a", .text)
|
|
t.column("b", .text)
|
|
}
|
|
try XCTAssertEqual(db.columns(in: "test").map(\.name), ["a", "b"])
|
|
|
|
try db.alter(table: "test") { t in
|
|
t.drop(column: "b")
|
|
}
|
|
try XCTAssertEqual(db.columns(in: "test").map(\.name), ["a"])
|
|
}
|
|
}
|
|
|
|
func testDropTable() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.primaryKey("id", .integer)
|
|
t.column("name", .text)
|
|
}
|
|
XCTAssertTrue(try db.tableExists("test"))
|
|
XCTAssertEqual(try db.columns(in: "test").count, 2)
|
|
|
|
try db.drop(table: "test")
|
|
assertEqualSQL(lastSQLQuery!, "DROP TABLE \"test\"")
|
|
XCTAssertFalse(try db.tableExists("test"))
|
|
}
|
|
}
|
|
|
|
func testCreateIndex() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.primaryKey("id", .integer)
|
|
t.column("a", .text)
|
|
t.column("b", .text)
|
|
}
|
|
|
|
try db.create(index: "test_on_a", on: "test", columns: ["a"])
|
|
assertEqualSQL(lastSQLQuery!, "CREATE INDEX \"test_on_a\" ON \"test\"(\"a\")")
|
|
|
|
try db.create(index: "test_on_a_b", on: "test", columns: ["a", "b"], unique: true, ifNotExists: true)
|
|
assertEqualSQL(lastSQLQuery!, "CREATE UNIQUE INDEX IF NOT EXISTS \"test_on_a_b\" ON \"test\"(\"a\", \"b\")")
|
|
|
|
try db.create(index: "test_on_a_b_2", on: "test", columns: ["a", "b"], options: [.unique, .ifNotExists])
|
|
assertEqualSQL(lastSQLQuery!, "CREATE UNIQUE INDEX IF NOT EXISTS \"test_on_a_b_2\" ON \"test\"(\"a\", \"b\")")
|
|
|
|
try db.create(index: "test_on_a_plus_b", on: "test", expressions: [Column("a") + Column("b")], options: [.unique, .ifNotExists])
|
|
assertEqualSQL(lastSQLQuery!, "CREATE UNIQUE INDEX IF NOT EXISTS \"test_on_a_plus_b\" ON \"test\"(\"a\" + \"b\")")
|
|
|
|
try db.create(index: "test_on_a_nocase", on: "test", expressions: [Column("a").collating(.nocase)], options: [.unique, .ifNotExists])
|
|
assertEqualSQL(lastSQLQuery!, "CREATE UNIQUE INDEX IF NOT EXISTS \"test_on_a_nocase\" ON \"test\"(\"a\" COLLATE NOCASE)")
|
|
|
|
// Sanity check
|
|
XCTAssertEqual(try Set(db.indexes(on: "test").map(\.name)), ["test_on_a", "test_on_a_b", "test_on_a_b_2", "test_on_a_nocase"])
|
|
}
|
|
}
|
|
|
|
func testCreateIndexOn() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.primaryKey("id", .integer)
|
|
t.column("a", .text)
|
|
t.column("b", .text)
|
|
}
|
|
|
|
try db.create(indexOn: "test", columns: ["a"])
|
|
assertEqualSQL(lastSQLQuery!, "CREATE INDEX \"index_test_on_a\" ON \"test\"(\"a\")")
|
|
|
|
try db.create(indexOn: "test", columns: ["a", "b"], options: [.unique])
|
|
assertEqualSQL(lastSQLQuery!, "CREATE UNIQUE INDEX \"index_test_on_a_b\" ON \"test\"(\"a\", \"b\")")
|
|
|
|
try db.create(indexOn: "test", columns: ["b"], options: [.unique, .ifNotExists])
|
|
assertEqualSQL(lastSQLQuery!, "CREATE UNIQUE INDEX IF NOT EXISTS \"index_test_on_b\" ON \"test\"(\"b\")")
|
|
|
|
// Sanity check
|
|
XCTAssertEqual(try Set(db.indexes(on: "test").map(\.name)), ["index_test_on_a", "index_test_on_a_b", "index_test_on_b"])
|
|
}
|
|
}
|
|
|
|
func testCreatePartialIndex() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.primaryKey("id", .integer)
|
|
t.column("a", .text)
|
|
t.column("b", .text)
|
|
}
|
|
|
|
try db.create(index: "test_on_a_b", on: "test", columns: ["a", "b"], options: [.unique, .ifNotExists], condition: Column("a") == 1)
|
|
assertEqualSQL(lastSQLQuery!, "CREATE UNIQUE INDEX IF NOT EXISTS \"test_on_a_b\" ON \"test\"(\"a\", \"b\") WHERE \"a\" = 1")
|
|
|
|
try db.create(index: "test_on_a_plus_b", on: "test", expressions: [Column("a") + Column("b")], options: [.unique, .ifNotExists], condition: Column("a") == 1)
|
|
assertEqualSQL(lastSQLQuery!, "CREATE UNIQUE INDEX IF NOT EXISTS \"test_on_a_plus_b\" ON \"test\"(\"a\" + \"b\") WHERE \"a\" = 1")
|
|
|
|
// Sanity check
|
|
XCTAssertEqual(try Set(db.indexes(on: "test").map(\.name)), ["test_on_a_b"])
|
|
}
|
|
}
|
|
|
|
func testDropIndex() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.primaryKey("id", .integer)
|
|
t.column("name", .text)
|
|
}
|
|
try db.create(index: "test_on_name", on: "test", columns: ["name"])
|
|
|
|
try db.drop(index: "test_on_name")
|
|
assertEqualSQL(lastSQLQuery!, "DROP INDEX \"test_on_name\"")
|
|
|
|
// Sanity check
|
|
XCTAssertTrue(try db.indexes(on: "test").isEmpty)
|
|
}
|
|
}
|
|
|
|
func testDropIndexOn() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "test") { t in
|
|
t.primaryKey("id", .integer)
|
|
t.column("a", .text)
|
|
t.column("b", .text)
|
|
t.column("c", .text)
|
|
t.column("d", .text)
|
|
}
|
|
try db.create(index: "custom_name_a", on: "test", columns: ["a"])
|
|
try db.create(index: "custom_name_b", on: "test", columns: ["a", "b"])
|
|
try db.create(indexOn: "test", columns: ["c"])
|
|
try db.create(indexOn: "test", columns: ["c", "d"])
|
|
|
|
// Custom name
|
|
try db.drop(indexOn: "test", columns: ["a"])
|
|
assertEqualSQL(lastSQLQuery!, "DROP INDEX \"custom_name_a\"")
|
|
|
|
// Custom name, case insensitivity
|
|
try db.drop(indexOn: "TEST", columns: ["A", "B"])
|
|
assertEqualSQL(lastSQLQuery!, "DROP INDEX \"custom_name_b\"")
|
|
|
|
// Default name
|
|
try db.drop(indexOn: "test", columns: ["c"])
|
|
assertEqualSQL(lastSQLQuery!, "DROP INDEX \"index_test_on_c\"")
|
|
|
|
// Default name, case insensitivity
|
|
try db.drop(indexOn: "TEST", columns: ["C", "D"])
|
|
assertEqualSQL(lastSQLQuery!, "DROP INDEX \"index_test_on_c_d\"")
|
|
|
|
// Non existing index: no error
|
|
try db.drop(indexOn: "test", columns: ["a", "b", "c", "d"])
|
|
|
|
// Non existing table: error
|
|
do {
|
|
try db.drop(indexOn: "missing", columns: ["a"])
|
|
XCTFail("Expected error")
|
|
} catch { }
|
|
|
|
// Sanity check
|
|
XCTAssertTrue(try db.indexes(on: "test").isEmpty)
|
|
}
|
|
}
|
|
|
|
func testReindex() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.reindex(collation: .binary)
|
|
assertEqualSQL(lastSQLQuery!, "REINDEX BINARY")
|
|
|
|
try db.reindex(collation: .localizedCompare)
|
|
assertEqualSQL(lastSQLQuery!, "REINDEX swiftLocalizedCompare")
|
|
}
|
|
}
|
|
|
|
func testCreateView() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "player") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.column("name")
|
|
t.column("score")
|
|
}
|
|
|
|
do {
|
|
let request = SQLRequest(literal: """
|
|
SELECT * FROM player WHERE name = \("O'Brien")
|
|
""")
|
|
try db.create(view: "view1", as: request)
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE VIEW "view1" AS SELECT * FROM player WHERE name = 'O''Brien'
|
|
""")
|
|
}
|
|
|
|
do {
|
|
let request = Table("player").filter(Column("name") == "O'Brien")
|
|
try db.create(view: "view2", as: request)
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE VIEW "view2" AS SELECT * FROM "player" WHERE "name" = 'O''Brien'
|
|
""")
|
|
}
|
|
|
|
do {
|
|
let sql: SQL = """
|
|
SELECT * FROM player WHERE name = \("O'Brien")
|
|
"""
|
|
try db.create(view: "view3", asLiteral: sql)
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE VIEW "view3" AS SELECT * FROM player WHERE name = 'O''Brien'
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
func testCreateViewOptions() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(table: "player") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.column("name")
|
|
t.column("score")
|
|
}
|
|
|
|
let request = SQLRequest(literal: """
|
|
SELECT * FROM player WHERE name = \("O'Brien")
|
|
""")
|
|
|
|
do {
|
|
try db.create(view: "view1", options: .ifNotExists, as: request)
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE VIEW IF NOT EXISTS "view1" AS SELECT * FROM player WHERE name = 'O''Brien'
|
|
""")
|
|
}
|
|
|
|
do {
|
|
try db.create(view: "view2", options: .temporary, as: request)
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TEMPORARY VIEW "view2" AS SELECT * FROM player WHERE name = 'O''Brien'
|
|
""")
|
|
}
|
|
|
|
do {
|
|
try db.create(view: "view3", options: [.temporary, .ifNotExists], as: request)
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE TEMPORARY VIEW IF NOT EXISTS "view3" AS SELECT * FROM player WHERE name = 'O''Brien'
|
|
""")
|
|
}
|
|
|
|
do {
|
|
try db.create(view: "view4", columns: ["a", "b", "c"], as: request)
|
|
assertEqualSQL(lastSQLQuery!, """
|
|
CREATE VIEW "view4" ("a", "b", "c") AS SELECT * FROM player WHERE name = 'O''Brien'
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
func testDropView() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try db.create(view: "test", as: SQLRequest(literal: "SELECT 'test', 42"))
|
|
XCTAssertTrue(try db.viewExists("test"))
|
|
XCTAssertEqual(try db.columns(in: "test").count, 2)
|
|
|
|
try db.drop(view: "test")
|
|
assertEqualSQL(lastSQLQuery!, "DROP VIEW \"test\"")
|
|
XCTAssertFalse(try db.viewExists("test"))
|
|
}
|
|
}
|
|
}
|