Files

523 lines
25 KiB
Swift

import XCTest
@testable import GRDB
class ForeignKeyInfoTests: GRDBTestCase {
private func assertEqual(_ lhs: ForeignKeyInfo, _ rhs: ForeignKeyInfo, file: StaticString = #file, line: UInt = #line) {
XCTAssertEqual(lhs.destinationTable, rhs.destinationTable, file: file, line: line)
XCTAssertEqual(lhs.mapping.count, rhs.mapping.count, file: file, line: line)
for (larrow, rarrow) in zip(lhs.mapping, rhs.mapping) {
XCTAssertEqual(larrow.origin, rarrow.origin, file: file, line: line)
XCTAssertEqual(larrow.destination, rarrow.destination, file: file, line: line)
}
}
// MARK: Foreign key info
func testForeignKeys() throws {
let dbQueue = try makeDatabaseQueue()
try dbQueue.inDatabase { db in
try db.execute(sql: "CREATE TABLE parents1 (id PRIMARY KEY)")
try db.execute(sql: "CREATE TABLE parents2 (a, b, PRIMARY KEY (a,b))")
try db.execute(sql: "CREATE TABLE children1 (parentId REFERENCES parents1)")
try db.execute(sql: "CREATE TABLE children2 (parentId1 REFERENCES parents1, parentId2 REFERENCES parents1)")
try db.execute(sql: "CREATE TABLE children3 (parentA, parentB, FOREIGN KEY (parentA, parentB) REFERENCES parents2)")
try db.execute(sql: "CREATE TABLE children4 (parentA1, parentB1, parentA2, parentB2, FOREIGN KEY (parentA1, parentB1) REFERENCES parents2, FOREIGN KEY (parentA2, parentB2) REFERENCES parents2(b, a))")
do {
_ = try db.foreignKeys(on: "missing")
XCTFail("Expected DatabaseError")
} catch let error as DatabaseError {
XCTAssertEqual(error.resultCode, .SQLITE_ERROR)
XCTAssertEqual(error.message, "no such table: missing")
}
do {
let foreignKeys = try db.foreignKeys(on: "parents1")
XCTAssert(foreignKeys.isEmpty)
}
do {
let foreignKeys = try db.foreignKeys(on: "parents2")
XCTAssert(foreignKeys.isEmpty)
}
do {
let foreignKeys = try db.foreignKeys(on: "children1")
XCTAssertEqual(foreignKeys.count, 1)
assertEqual(foreignKeys[0], ForeignKeyInfo(id: foreignKeys[0].id, destinationTable: "parents1", mapping: [(origin: "parentId", destination: "id")]))
}
do {
let foreignKeys = try db.foreignKeys(on: "children2")
XCTAssertEqual(foreignKeys.count, 2)
assertEqual(foreignKeys[0], ForeignKeyInfo(id: foreignKeys[0].id, destinationTable: "parents1", mapping: [(origin: "parentId2", destination: "id")]))
assertEqual(foreignKeys[1], ForeignKeyInfo(id: foreignKeys[1].id, destinationTable: "parents1", mapping: [(origin: "parentId1", destination: "id")]))
}
do {
let foreignKeys = try db.foreignKeys(on: "children3")
XCTAssertEqual(foreignKeys.count, 1)
assertEqual(foreignKeys[0], ForeignKeyInfo(id: foreignKeys[0].id, destinationTable: "parents2", mapping: [(origin: "parentA", destination: "a"), (origin: "parentB", destination: "b")]))
}
do {
let foreignKeys = try db.foreignKeys(on: "children4")
XCTAssertEqual(foreignKeys.count, 2)
assertEqual(foreignKeys[0], ForeignKeyInfo(id: foreignKeys[0].id, destinationTable: "parents2", mapping: [(origin: "parentA2", destination: "b"), (origin: "parentB2", destination: "a")]))
assertEqual(foreignKeys[1], ForeignKeyInfo(id: foreignKeys[1].id, destinationTable: "parents2", mapping: [(origin: "parentA1", destination: "a"), (origin: "parentB1", destination: "b")]))
}
}
}
func testUnknownSchema() throws {
let dbQueue = try makeDatabaseQueue()
try dbQueue.writeWithoutTransaction { db in
try db.execute(sql: "CREATE TABLE parents1 (id PRIMARY KEY)")
try db.execute(sql: "CREATE TABLE children1 (parentId REFERENCES parents1)")
do {
_ = try db.foreignKeys(on: "children1", in: "invalid")
XCTFail("Expected Error")
} catch let error as DatabaseError {
XCTAssertEqual(error.resultCode, .SQLITE_ERROR)
XCTAssertEqual(error.message, "no such schema: invalid")
XCTAssertEqual(error.description, "SQLite error 1: no such schema: invalid")
}
}
}
func testSpecifiedMainSchema() throws {
let dbQueue = try makeDatabaseQueue()
try dbQueue.inDatabase { db in
try db.execute(sql: "CREATE TABLE parents1 (id PRIMARY KEY)")
try db.execute(sql: "CREATE TABLE children1 (parentId REFERENCES parents1)")
do {
let foreignKeys = try db.foreignKeys(on: "children1", in: "main")
XCTAssertEqual(foreignKeys.count, 1)
assertEqual(foreignKeys[0], ForeignKeyInfo(id: foreignKeys[0].id, destinationTable: "parents1", mapping: [(origin: "parentId", destination: "id")]))
}
}
}
func testSpecifiedSchemaWithTableNameCollisions() throws {
#if GRDBCIPHER_USE_ENCRYPTION
// Avoid error due to key not being provided:
// file is not a database - while executing `ATTACH DATABASE...`
throw XCTSkip("This test does not support encrypted databases")
#endif
let attached = try makeDatabaseQueue(filename: "attached1")
try attached.inDatabase { db in
try db.execute(sql: "CREATE TABLE parents2 (id PRIMARY KEY)")
try db.execute(sql: "CREATE TABLE children (parentId REFERENCES parents2)")
}
let main = try makeDatabaseQueue(filename: "main")
try main.inDatabase { db in
try db.execute(sql: "CREATE TABLE parents1 (id PRIMARY KEY)")
try db.execute(sql: "CREATE TABLE children (parentId REFERENCES parents1)")
try db.execute(literal: "ATTACH DATABASE \(attached.path) AS attached")
do {
let foreignKeys = try db.foreignKeys(on: "children", in: "attached")
XCTAssertEqual(foreignKeys.count, 1)
assertEqual(foreignKeys[0], ForeignKeyInfo(id: foreignKeys[0].id, destinationTable: "parents2", mapping: [(origin: "parentId", destination: "id")]))
}
}
}
// The `children` table in the attached database should not
// be found unless explicitly specified as it is after
// `main.children` in resolution order.
func testUnspecifiedSchemaWithTableNameCollisions() throws {
#if GRDBCIPHER_USE_ENCRYPTION
// Avoid error due to key not being provided:
// file is not a database - while executing `ATTACH DATABASE...`
throw XCTSkip("This test does not support encrypted databases")
#endif
let attached = try makeDatabaseQueue(filename: "attached1")
try attached.inDatabase { db in
try db.execute(sql: "CREATE TABLE parents2 (id PRIMARY KEY)")
try db.execute(sql: "CREATE TABLE children (parentId REFERENCES parents2)")
}
let main = try makeDatabaseQueue(filename: "main")
try main.inDatabase { db in
try db.execute(sql: "CREATE TABLE parents1 (id PRIMARY KEY)")
try db.execute(sql: "CREATE TABLE children (parentId REFERENCES parents1)")
try db.execute(literal: "ATTACH DATABASE \(attached.path) AS attached")
do {
let foreignKeys = try db.foreignKeys(on: "children")
XCTAssertEqual(foreignKeys.count, 1)
assertEqual(foreignKeys[0], ForeignKeyInfo(id: foreignKeys[0].id, destinationTable: "parents1", mapping: [(origin: "parentId", destination: "id")]))
}
}
}
func testUnspecifiedSchemaFindsAttachedDatabase() throws {
#if GRDBCIPHER_USE_ENCRYPTION
// Avoid error due to key not being provided:
// file is not a database - while executing `ATTACH DATABASE...`
throw XCTSkip("This test does not support encrypted databases")
#endif
let attached = try makeDatabaseQueue(filename: "attached1")
try attached.inDatabase { db in
try db.execute(sql: "CREATE TABLE parents2 (id PRIMARY KEY)")
try db.execute(sql: "CREATE TABLE children2 (parentId REFERENCES parents2)")
}
let main = try makeDatabaseQueue(filename: "main")
try main.inDatabase { db in
try db.execute(sql: "CREATE TABLE parents1 (id PRIMARY KEY)")
try db.execute(sql: "CREATE TABLE children1 (parentId REFERENCES parents1)")
try db.execute(literal: "ATTACH DATABASE \(attached.path) AS attached")
do {
let foreignKeys = try db.foreignKeys(on: "children2")
XCTAssertEqual(foreignKeys.count, 1)
assertEqual(foreignKeys[0], ForeignKeyInfo(id: foreignKeys[0].id, destinationTable: "parents2", mapping: [(origin: "parentId", destination: "id")]))
}
}
}
// MARK: Foreign key violations
func testForeignKeyViolations() throws {
try makeDatabaseQueue().writeWithoutTransaction { db in
try db.execute(sql: """
CREATE TABLE parent(id TEXT NOT NULL PRIMARY KEY);
CREATE TABLE child1(id INTEGER NOT NULL PRIMARY KEY, parentId TEXT REFERENCES parent(id));
CREATE TABLE child2(id INTEGER NOT NULL PRIMARY KEY, parentId TEXT REFERENCES parent(id));
PRAGMA foreign_keys = OFF;
INSERT INTO child1 (id, parentId) VALUES (13, '1');
INSERT INTO child1 (id, parentId) VALUES (42, '2');
INSERT INTO child2 (id, parentId) VALUES (17, '3');
""")
let violations = try Array(db.foreignKeyViolations())
XCTAssertEqual(violations.count, 3)
if let violation = violations.first(where: { $0.originTable == "child1" && $0.originRowID == 13 }) {
XCTAssertEqual(violation.destinationTable, "parent")
} else {
XCTFail("Missing violation")
}
if let violation = violations.first(where: { $0.originTable == "child1" && $0.originRowID == 42 }) {
XCTAssertEqual(violation.destinationTable, "parent")
} else {
XCTFail("Missing violation")
}
if let violation = violations.first(where: { $0.originTable == "child2" && $0.originRowID == 17 }) {
XCTAssertEqual(violation.destinationTable, "parent")
} else {
XCTFail("Missing violation")
}
}
}
func testForeignKeyViolationsInTable() throws {
try makeDatabaseQueue().writeWithoutTransaction { db in
try db.execute(sql: """
CREATE TABLE parent(id TEXT NOT NULL PRIMARY KEY);
CREATE TABLE child1(id INTEGER NOT NULL PRIMARY KEY, parentId TEXT REFERENCES parent(id));
CREATE TABLE child2(id INTEGER NOT NULL PRIMARY KEY, parentId TEXT REFERENCES parent(id));
PRAGMA foreign_keys = OFF;
INSERT INTO child1 (id, parentId) VALUES (13, '1');
INSERT INTO child1 (id, parentId) VALUES (42, '2');
INSERT INTO child2 (id, parentId) VALUES (17, '3');
""")
do {
let violations = try Array(db.foreignKeyViolations(in: "child1"))
XCTAssertEqual(violations.count, 2)
if let violation = violations.first(where: { $0.originRowID == 13 }) {
XCTAssertEqual(violation.originTable, "child1")
XCTAssertEqual(violation.destinationTable, "parent")
} else {
XCTFail("Missing violation")
}
if let violation = violations.first(where: { $0.originRowID == 42 }) {
XCTAssertEqual(violation.originTable, "child1")
XCTAssertEqual(violation.destinationTable, "parent")
} else {
XCTFail("Missing violation")
}
}
do {
let violations = try Array(db.foreignKeyViolations(in: "child2"))
XCTAssertEqual(violations.count, 1)
if let violation = violations.first(where: { $0.originRowID == 17 }) {
XCTAssertEqual(violation.originTable, "child2")
XCTAssertEqual(violation.destinationTable, "parent")
} else {
XCTFail("Missing violation")
}
}
// Case insensitivity
do {
let violations = try Array(db.foreignKeyViolations(in: "cHiLd2"))
XCTAssertEqual(violations.count, 1)
if let violation = violations.first(where: { $0.originRowID == 17 }) {
XCTAssertEqual(violation.originTable, "child2")
XCTAssertEqual(violation.destinationTable, "parent")
} else {
XCTFail("Missing violation")
}
}
// Missing table
do {
_ = try db.foreignKeyViolations(in: "missing")
} catch DatabaseError.SQLITE_ERROR { }
}
}
func testCheckForeignKeys() throws {
try makeDatabaseQueue().writeWithoutTransaction { db in
try db.execute(sql: """
CREATE TABLE parent(id TEXT NOT NULL PRIMARY KEY);
CREATE TABLE child(id INTEGER NOT NULL PRIMARY KEY, parentId TEXT REFERENCES parent(id));
PRAGMA foreign_keys = OFF;
INSERT INTO child (id, parentId) VALUES (13, '1');
""")
do {
try db.checkForeignKeys()
} catch let error as DatabaseError {
XCTAssertEqual(error.resultCode, .SQLITE_CONSTRAINT)
XCTAssertEqual(error.extendedResultCode, .SQLITE_CONSTRAINT_FOREIGNKEY)
XCTAssertEqual(error.message, #"FOREIGN KEY constraint violation - from child(parentId) to parent(id), in [id:13 parentId:"1"]"#)
XCTAssertEqual(error.description, #"SQLite error 19: FOREIGN KEY constraint violation - from child(parentId) to parent(id), in [id:13 parentId:"1"]"#)
}
}
try makeDatabaseQueue().writeWithoutTransaction { db in
try db.execute(sql: """
CREATE TABLE parent(a TEXT NOT NULL, b TEXT NOT NULL, PRIMARY KEY (a, b));
CREATE TABLE child(id INTEGER NOT NULL PRIMARY KEY, parentA, parentB, FOREIGN KEY (parentA, parentB) REFERENCES parent(a, b));
PRAGMA foreign_keys = OFF;
INSERT INTO child (id, parentA, parentB) VALUES (13, 'foo', 'bar');
""")
do {
try db.checkForeignKeys()
} catch let error as DatabaseError {
XCTAssertEqual(error.resultCode, .SQLITE_CONSTRAINT)
XCTAssertEqual(error.extendedResultCode, .SQLITE_CONSTRAINT_FOREIGNKEY)
XCTAssertEqual(error.message, #"FOREIGN KEY constraint violation - from child(parentA, parentB) to parent(a, b), in [id:13 parentA:"foo" parentB:"bar"]"#)
XCTAssertEqual(error.description, #"SQLite error 19: FOREIGN KEY constraint violation - from child(parentA, parentB) to parent(a, b), in [id:13 parentA:"foo" parentB:"bar"]"#)
}
}
try makeDatabaseQueue().writeWithoutTransaction { db in
try db.execute(sql: """
CREATE TABLE parent(a TEXT NOT NULL, b TEXT NOT NULL, PRIMARY KEY (a, b));
CREATE TABLE child(id INTEGER NOT NULL PRIMARY KEY, parentA, parentB, FOREIGN KEY (parentA, parentB) REFERENCES parent(a, b)) WITHOUT ROWID;
PRAGMA foreign_keys = OFF;
INSERT INTO child (id, parentA, parentB) VALUES (13, 'foo', 'bar');
""")
do {
try db.checkForeignKeys()
} catch let error as DatabaseError {
XCTAssertEqual(error.resultCode, .SQLITE_CONSTRAINT)
XCTAssertEqual(error.extendedResultCode, .SQLITE_CONSTRAINT_FOREIGNKEY)
XCTAssertEqual(error.message, "FOREIGN KEY constraint violation - from child(parentA, parentB) to parent(a, b)")
XCTAssertEqual(error.description, "SQLite error 19: FOREIGN KEY constraint violation - from child(parentA, parentB) to parent(a, b)")
}
}
}
func testForeignKeyViolationsUnknownSchema() throws {
let dbQueue = try makeDatabaseQueue()
try dbQueue.writeWithoutTransaction { db in
try db.execute(sql: "CREATE TABLE parent (id PRIMARY KEY)")
try db.execute(sql: "CREATE TABLE child (parentId REFERENCES parent)")
do {
_ = try db.foreignKeyViolations(in: "child", in: "invalid")
XCTFail("Expected Error")
} catch let error as DatabaseError {
XCTAssertEqual(error.resultCode, .SQLITE_ERROR)
XCTAssertEqual(error.message, "no such schema: invalid")
XCTAssertEqual(error.description, "SQLite error 1: no such schema: invalid")
}
do {
_ = try db.checkForeignKeys(in: "child", in: "invalid")
XCTFail("Expected Error")
} catch let error as DatabaseError {
XCTAssertEqual(error.resultCode, .SQLITE_ERROR)
XCTAssertEqual(error.message, "no such schema: invalid")
XCTAssertEqual(error.description, "SQLite error 1: no such schema: invalid")
}
}
}
func testForeignKeyViolationsMainSchema() throws {
let dbQueue = try makeDatabaseQueue()
try dbQueue.writeWithoutTransaction { db in
try db.execute(sql: """
CREATE TABLE parent(id TEXT NOT NULL PRIMARY KEY);
CREATE TABLE child(id INTEGER NOT NULL PRIMARY KEY, parentId TEXT REFERENCES parent(id));
PRAGMA foreign_keys = OFF;
INSERT INTO child (id, parentId) VALUES (13, '1');
""")
do {
let violations = try Array(db.foreignKeyViolations(in: "child", in: "main"))
XCTAssertEqual(violations.count, 1)
}
do {
_ = try db.checkForeignKeys(in: "child", in: "main")
XCTFail("Expected Error")
} catch let error as DatabaseError {
XCTAssertEqual(DatabaseError.SQLITE_CONSTRAINT_FOREIGNKEY, error.extendedResultCode)
}
}
}
func testForeignKeyViolationsInSpecifiedSchemaWithTableNameCollisions() throws {
#if GRDBCIPHER_USE_ENCRYPTION
// Avoid error due to key not being provided:
// file is not a database - while executing `ATTACH DATABASE...`
throw XCTSkip("This test does not support encrypted databases")
#endif
let attached = try makeDatabaseQueue(filename: "attached1")
try attached.inDatabase { db in
try db.execute(sql: """
CREATE TABLE parent(id TEXT NOT NULL PRIMARY KEY);
CREATE TABLE child(id INTEGER NOT NULL PRIMARY KEY, parentId TEXT REFERENCES parent(id));
PRAGMA foreign_keys = OFF;
INSERT INTO child (id, parentId) VALUES (20, '1');
""")
}
let main = try makeDatabaseQueue(filename: "main")
try main.inDatabase { db in
try db.execute(sql: """
CREATE TABLE parent(id TEXT NOT NULL PRIMARY KEY);
CREATE TABLE child(id INTEGER NOT NULL PRIMARY KEY, parentId TEXT REFERENCES parent(id));
PRAGMA foreign_keys = OFF;
INSERT INTO child (id, parentId) VALUES (10, '1');
""")
try db.execute(literal: "ATTACH DATABASE \(attached.path) AS attached")
do {
let violations = try Array(try db.foreignKeyViolations(in: "child", in: "attached"))
XCTAssertEqual(violations.count, 1)
if let violation = violations.first(where: { $0.originRowID == 20 }) {
XCTAssertEqual(violation.originTable, "child")
XCTAssertEqual(violation.destinationTable, "parent")
} else {
XCTFail("Missing violation")
}
}
do {
_ = try db.checkForeignKeys(in: "child", in: "attached")
XCTFail("Expected Error")
} catch let error as DatabaseError {
XCTAssertEqual(DatabaseError.SQLITE_CONSTRAINT_FOREIGNKEY, error.extendedResultCode)
}
}
}
// The `child` table in the attached database should not
// be found unless explicitly specified as it is after
// `main.child` in resolution order.
func testForeignKeyViolationsInUnspecifiedSchemaWithTableNameCollisions() throws {
#if GRDBCIPHER_USE_ENCRYPTION
// Avoid error due to key not being provided:
// file is not a database - while executing `ATTACH DATABASE...`
throw XCTSkip("This test does not support encrypted databases")
#endif
let attached = try makeDatabaseQueue(filename: "attached1")
try attached.inDatabase { db in
try db.execute(sql: """
CREATE TABLE parent(id TEXT NOT NULL PRIMARY KEY);
CREATE TABLE child(id INTEGER NOT NULL PRIMARY KEY, parentId TEXT REFERENCES parent(id));
PRAGMA foreign_keys = OFF;
INSERT INTO child (id, parentId) VALUES (20, '1');
""")
}
let main = try makeDatabaseQueue(filename: "main")
try main.inDatabase { db in
try db.execute(sql: """
CREATE TABLE parent(id TEXT NOT NULL PRIMARY KEY);
CREATE TABLE child(id INTEGER NOT NULL PRIMARY KEY, parentId TEXT REFERENCES parent(id));
PRAGMA foreign_keys = OFF;
INSERT INTO child (id, parentId) VALUES (10, '1');
""")
try db.execute(literal: "ATTACH DATABASE \(attached.path) AS attached")
do {
let violations = try Array(try db.foreignKeyViolations(in: "child"))
XCTAssertEqual(violations.count, 1)
if let violation = violations.first(where: { $0.originRowID == 10 }) {
XCTAssertEqual(violation.originTable, "child")
XCTAssertEqual(violation.destinationTable, "parent")
} else {
XCTFail("Missing violation")
}
}
do {
_ = try db.checkForeignKeys(in: "child")
XCTFail("Expected Error")
} catch let error as DatabaseError {
XCTAssertEqual(DatabaseError.SQLITE_CONSTRAINT_FOREIGNKEY, error.extendedResultCode)
}
}
}
func testForeignKeyViolationsInUnspecifiedSchemaFindsAttachedDatabase() throws {
#if GRDBCIPHER_USE_ENCRYPTION
// Avoid error due to key not being provided:
// file is not a database - while executing `ATTACH DATABASE...`
throw XCTSkip("This test does not support encrypted databases")
#endif
let attached = try makeDatabaseQueue(filename: "attached1")
try attached.inDatabase { db in
try db.execute(sql: """
CREATE TABLE parent(id TEXT NOT NULL PRIMARY KEY);
CREATE TABLE child(id INTEGER NOT NULL PRIMARY KEY, parentId TEXT REFERENCES parent(id));
PRAGMA foreign_keys = OFF;
INSERT INTO child (id, parentId) VALUES (20, '1');
""")
}
let main = try makeDatabaseQueue(filename: "main")
try main.inDatabase { db in
try db.execute(literal: "ATTACH DATABASE \(attached.path) AS attached")
do {
let violations = try Array(try db.foreignKeyViolations(in: "child"))
XCTAssertEqual(violations.count, 1)
if let violation = violations.first(where: { $0.originRowID == 20 }) {
XCTAssertEqual(violation.originTable, "child")
XCTAssertEqual(violation.destinationTable, "parent")
} else {
XCTFail("Missing violation")
}
}
do {
_ = try db.checkForeignKeys(in: "child")
XCTFail("Expected Error")
} catch let error as DatabaseError {
XCTAssertEqual(DatabaseError.SQLITE_CONSTRAINT_FOREIGNKEY, error.extendedResultCode)
}
}
}
}