700 lines
32 KiB
Swift
700 lines
32 KiB
Swift
import XCTest
|
|
import GRDB
|
|
|
|
private struct Hacker : TableRecord {
|
|
static let databaseTableName = "hackers"
|
|
var id: Int64? // Optional
|
|
}
|
|
|
|
@available(iOS 13, macOS 10.15, tvOS 13, watchOS 6, *)
|
|
extension Hacker: Identifiable { }
|
|
|
|
private struct Person : Codable, PersistableRecord, FetchableRecord, Hashable {
|
|
static let databaseTableName = "persons"
|
|
var id: Int64 // Non-optional
|
|
var name: String
|
|
var email: String
|
|
}
|
|
|
|
@available(iOS 13, macOS 10.15, tvOS 13, watchOS 6, *)
|
|
extension Person: Identifiable { }
|
|
|
|
private struct Citizenship : TableRecord {
|
|
static let databaseTableName = "citizenships"
|
|
}
|
|
|
|
|
|
class TableRecordDeleteTests: GRDBTestCase {
|
|
|
|
override func setup(_ dbWriter: some DatabaseWriter) throws {
|
|
try dbWriter.write { db in
|
|
try db.execute(sql: "CREATE TABLE hackers (name TEXT)")
|
|
try db.execute(sql: "CREATE TABLE persons (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE)")
|
|
try db.execute(sql: "CREATE TABLE citizenships (personId INTEGER NOT NULL, countryIsoCode TEXT NOT NULL, PRIMARY KEY (personId, countryIsoCode))")
|
|
}
|
|
}
|
|
|
|
func testImplicitRowIDPrimaryKey() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
var deleted = try Hacker.deleteOne(db, key: 1)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"hackers\" WHERE \"rowid\" = 1")
|
|
XCTAssertFalse(deleted)
|
|
|
|
try db.execute(sql: "INSERT INTO hackers (rowid, name) VALUES (?, ?)", arguments: [1, "Arthur"])
|
|
deleted = try Hacker.deleteOne(db, key: 1)
|
|
XCTAssertTrue(deleted)
|
|
XCTAssertEqual(try Hacker.fetchCount(db), 0)
|
|
|
|
if #available(iOS 13, macOS 10.15, tvOS 13, watchOS 6, *) {
|
|
try db.execute(sql: "INSERT INTO hackers (rowid, name) VALUES (?, ?)", arguments: [1, "Arthur"])
|
|
try XCTAssertFalse(Hacker.deleteOne(db, id: nil))
|
|
deleted = try Hacker.deleteOne(db, id: 1)
|
|
XCTAssertTrue(deleted)
|
|
XCTAssertEqual(try Hacker.fetchCount(db), 0)
|
|
}
|
|
|
|
try db.execute(sql: "INSERT INTO hackers (rowid, name) VALUES (?, ?)", arguments: [1, "Arthur"])
|
|
try db.execute(sql: "INSERT INTO hackers (rowid, name) VALUES (?, ?)", arguments: [2, "Barbara"])
|
|
try db.execute(sql: "INSERT INTO hackers (rowid, name) VALUES (?, ?)", arguments: [3, "Craig"])
|
|
let deletedCount = try Hacker.deleteAll(db, keys: [2, 3, 4])
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"hackers\" WHERE \"rowid\" IN (2, 3, 4)")
|
|
XCTAssertEqual(deletedCount, 2)
|
|
XCTAssertEqual(try Hacker.fetchCount(db), 1)
|
|
|
|
if #available(iOS 13, macOS 10.15, tvOS 13, watchOS 6, *) {
|
|
try db.execute(sql: "INSERT INTO hackers (rowid, name) VALUES (?, ?)", arguments: [2, "Barbara"])
|
|
try db.execute(sql: "INSERT INTO hackers (rowid, name) VALUES (?, ?)", arguments: [3, "Craig"])
|
|
let deletedCount = try Hacker.deleteAll(db, ids: [2, 3, 4])
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"hackers\" WHERE \"rowid\" IN (2, 3, 4)")
|
|
XCTAssertEqual(deletedCount, 2)
|
|
XCTAssertEqual(try Hacker.fetchCount(db), 1)
|
|
}
|
|
}
|
|
}
|
|
|
|
func testSingleColumnPrimaryKey() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
var deleted = try Person.deleteOne(db, key: 1)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" = 1")
|
|
XCTAssertFalse(deleted)
|
|
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [1, "Arthur", "arthur@example.com"])
|
|
deleted = try Person.deleteOne(db, key: 1)
|
|
XCTAssertTrue(deleted)
|
|
XCTAssertEqual(try Person.fetchCount(db), 0)
|
|
|
|
if #available(iOS 13, macOS 10.15, tvOS 13, watchOS 6, *) {
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [1, "Arthur", "arthur@example.com"])
|
|
deleted = try Person.deleteOne(db, id: 1)
|
|
XCTAssertTrue(deleted)
|
|
XCTAssertEqual(try Person.fetchCount(db), 0)
|
|
}
|
|
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [1, "Arthur", "arthur@example.com"])
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [2, "Barbara", "barbara@example.com"])
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [3, "Craig", "craig@example.com"])
|
|
let deletedCount = try Person.deleteAll(db, keys: [2, 3, 4])
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" IN (2, 3, 4)")
|
|
XCTAssertEqual(deletedCount, 2)
|
|
XCTAssertEqual(try Person.fetchCount(db), 1)
|
|
|
|
if #available(iOS 13, macOS 10.15, tvOS 13, watchOS 6, *) {
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [2, "Barbara", "barbara@example.com"])
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [3, "Craig", "craig@example.com"])
|
|
let deletedCount = try Person.deleteAll(db, ids: [2, 3, 4])
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" IN (2, 3, 4)")
|
|
XCTAssertEqual(deletedCount, 2)
|
|
XCTAssertEqual(try Person.fetchCount(db), 1)
|
|
}
|
|
}
|
|
}
|
|
|
|
func testMultipleColumnPrimaryKey() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
var deleted = try Citizenship.deleteOne(db, key: ["personId": 1, "countryIsoCode": "FR"])
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"citizenships\" WHERE (\"personId\" = 1) AND (\"countryIsoCode\" = 'FR')")
|
|
XCTAssertFalse(deleted)
|
|
|
|
try db.execute(sql: "INSERT INTO citizenships (personId, countryIsoCode) VALUES (?, ?)", arguments: [1, "FR"])
|
|
deleted = try Citizenship.deleteOne(db, key: ["personId": 1, "countryIsoCode": "FR"])
|
|
XCTAssertTrue(deleted)
|
|
XCTAssertEqual(try Citizenship.fetchCount(db), 0)
|
|
|
|
try db.execute(sql: "INSERT INTO citizenships (personId, countryIsoCode) VALUES (?, ?)", arguments: [1, "FR"])
|
|
try db.execute(sql: "INSERT INTO citizenships (personId, countryIsoCode) VALUES (?, ?)", arguments: [1, "US"])
|
|
try db.execute(sql: "INSERT INTO citizenships (personId, countryIsoCode) VALUES (?, ?)", arguments: [2, "US"])
|
|
let deletedCount = try Citizenship.deleteAll(db, keys: [["personId": 1, "countryIsoCode": "FR"], ["personId": 1, "countryIsoCode": "US"], ["personId": 1, "countryIsoCode": "DE"]])
|
|
XCTAssertEqual(deletedCount, 2)
|
|
XCTAssertEqual(try Citizenship.fetchCount(db), 1)
|
|
}
|
|
}
|
|
|
|
func testUniqueIndex() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
var deleted = try Person.deleteOne(db, key: ["email": "arthur@example.com"])
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"email\" = 'arthur@example.com'")
|
|
XCTAssertFalse(deleted)
|
|
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [1, "Arthur", "arthur@example.com"])
|
|
deleted = try Person.deleteOne(db, key: ["email": "arthur@example.com"])
|
|
XCTAssertTrue(deleted)
|
|
XCTAssertEqual(try Person.fetchCount(db), 0)
|
|
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [1, "Arthur", "arthur@example.com"])
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [2, "Barbara", "barbara@example.com"])
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [3, "Craig", "craig@example.com"])
|
|
let deletedCount = try Person.deleteAll(db, keys: [["email": "arthur@example.com"], ["email": "barbara@example.com"], ["email": "david@example.com"]])
|
|
XCTAssertEqual(deletedCount, 2)
|
|
XCTAssertEqual(try Person.fetchCount(db), 1)
|
|
}
|
|
}
|
|
|
|
func testImplicitUniqueIndexOnSingleColumnPrimaryKey() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
var deleted = try Person.deleteOne(db, key: ["id": 1])
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" = 1")
|
|
XCTAssertFalse(deleted)
|
|
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [1, "Arthur", "arthur@example.com"])
|
|
deleted = try Person.deleteOne(db, key: ["id": 1])
|
|
XCTAssertTrue(deleted)
|
|
XCTAssertEqual(try Person.fetchCount(db), 0)
|
|
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [1, "Arthur", "arthur@example.com"])
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [2, "Barbara", "barbara@example.com"])
|
|
try db.execute(sql: "INSERT INTO persons (id, name, email) VALUES (?, ?, ?)", arguments: [3, "Craig", "craig@example.com"])
|
|
let deletedCount = try Person.deleteAll(db, keys: [["id": 2], ["id": 3], ["id": 4]])
|
|
XCTAssertEqual(deletedCount, 2)
|
|
XCTAssertEqual(try Person.fetchCount(db), 1)
|
|
}
|
|
}
|
|
|
|
func testRequestDeleteAll() throws {
|
|
let dbQueue = try makeDatabaseQueue()
|
|
|
|
try dbQueue.inDatabase { db in
|
|
try Person.deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\"")
|
|
|
|
try Person.filter(Column("name") == "Arthur").deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"name\" = 'Arthur'")
|
|
|
|
try Person.filter(key: 1).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" = 1")
|
|
|
|
try Person.filter(keys: [1, 2]).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" IN (1, 2)")
|
|
|
|
if #available(iOS 13, macOS 10.15, tvOS 13, watchOS 6, *) {
|
|
try Person.filter(id: 1).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" = 1")
|
|
|
|
try Person.filter(ids: [1, 2]).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" IN (1, 2)")
|
|
}
|
|
|
|
try Person.filter(sql: "id = 1").deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE id = 1")
|
|
|
|
try Person.filter(sql: "id = 1").filter(Column("name") == "Arthur").deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE (id = 1) AND (\"name\" = 'Arthur')")
|
|
|
|
try Person.select(Column("name")).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\"")
|
|
|
|
try Person.order(Column("name")).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\"")
|
|
|
|
if try String.fetchCursor(db, sql: "PRAGMA COMPILE_OPTIONS").contains("ENABLE_UPDATE_DELETE_LIMIT") {
|
|
try Person.limit(1).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" LIMIT 1")
|
|
|
|
try Person.order(Column("name")).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\"")
|
|
|
|
try Person.order(Column("name")).limit(1).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" ORDER BY \"name\" LIMIT 1")
|
|
|
|
try Person.order(Column("name")).limit(1, offset: 2).reversed().deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" ORDER BY \"name\" DESC LIMIT 1 OFFSET 2")
|
|
|
|
try Person.limit(1, offset: 2).reversed().deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" LIMIT 1 OFFSET 2")
|
|
}
|
|
}
|
|
}
|
|
|
|
func testRequestDeleteAndFetchStatement() throws {
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
guard sqlite3_libversion_number() >= 3035000 else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#else
|
|
guard #available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#endif
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
let request = Person.all()
|
|
let statement = try request.deleteAndFetchStatement(db, selection: [AllColumns()])
|
|
XCTAssertEqual(statement.sql, "DELETE FROM \"persons\" RETURNING *")
|
|
XCTAssertEqual(statement.columnNames, ["id", "name", "email"])
|
|
}
|
|
}
|
|
|
|
func testRequestDeleteAndFetchCursor() throws {
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
guard sqlite3_libversion_number() >= 3035000 else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#else
|
|
guard #available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#endif
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
|
|
try dbQueue.inDatabase { db in
|
|
_ = try Person.all().deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" RETURNING *")
|
|
|
|
_ = try Person.all().deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" RETURNING *")
|
|
|
|
_ = try Person.filter(Column("name") == "Arthur").deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"name\" = 'Arthur' RETURNING *")
|
|
|
|
_ = try Person.filter(key: 1).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" = 1 RETURNING *")
|
|
|
|
_ = try Person.filter(keys: [1, 2]).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" IN (1, 2) RETURNING *")
|
|
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
if #available(iOS 13, macOS 10.15, tvOS 13, watchOS 6, *) {
|
|
_ = try Person.filter(id: 1).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" = 1 RETURNING *")
|
|
|
|
_ = try Person.filter(ids: [1, 2]).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" IN (1, 2) RETURNING *")
|
|
}
|
|
#else
|
|
_ = try Person.filter(id: 1).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" = 1 RETURNING *")
|
|
|
|
_ = try Person.filter(ids: [1, 2]).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE \"id\" IN (1, 2) RETURNING *")
|
|
#endif
|
|
|
|
_ = try Person.filter(sql: "id = 1").deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE id = 1 RETURNING *")
|
|
|
|
_ = try Person.filter(sql: "id = 1").filter(Column("name") == "Arthur").deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" WHERE (id = 1) AND (\"name\" = 'Arthur') RETURNING *")
|
|
|
|
_ = try Person.select(Column("name")).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" RETURNING *")
|
|
|
|
_ = try Person.order(Column("name")).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, "DELETE FROM \"persons\" RETURNING *")
|
|
|
|
// No test for LIMIT ... RETURNING ... since this is not supported by SQLite
|
|
}
|
|
}
|
|
|
|
func testRequestDeleteAndFetchArray() throws {
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
guard sqlite3_libversion_number() >= 3035000 else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#else
|
|
guard #available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#endif
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try Person(id: 1, name: "Arthur", email: "arthur@example.com").insert(db)
|
|
try Person(id: 2, name: "Barbara", email: "barbara@example.com").insert(db)
|
|
try Person(id: 3, name: "Craig", email: "craig@example.com").insert(db)
|
|
|
|
let request = Person.filter(Column("id") != 2)
|
|
let deletePersons = try request
|
|
.deleteAndFetchAll(db)
|
|
.sorted(by: { $0.id < $1.id })
|
|
XCTAssertEqual(deletePersons, [
|
|
Person(id: 1, name: "Arthur", email: "arthur@example.com"),
|
|
Person(id: 3, name: "Craig", email: "craig@example.com"),
|
|
])
|
|
}
|
|
}
|
|
|
|
func testRequestDeleteAndFetchSet() throws {
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
guard sqlite3_libversion_number() >= 3035000 else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#else
|
|
guard #available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#endif
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try Person(id: 1, name: "Arthur", email: "arthur@example.com").insert(db)
|
|
try Person(id: 2, name: "Barbara", email: "barbara@example.com").insert(db)
|
|
try Person(id: 3, name: "Craig", email: "craig@example.com").insert(db)
|
|
|
|
let request = Person.filter(Column("id") != 2)
|
|
let deletePersons = try request.deleteAndFetchSet(db)
|
|
XCTAssertEqual(deletePersons, [
|
|
Person(id: 1, name: "Arthur", email: "arthur@example.com"),
|
|
Person(id: 3, name: "Craig", email: "craig@example.com"),
|
|
])
|
|
}
|
|
}
|
|
|
|
@available(iOS 13, macOS 10.15, tvOS 13, watchOS 6, *) // Identifiable
|
|
func testRequestDeleteAndFetchIds() throws {
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
guard sqlite3_libversion_number() >= 3035000 else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#else
|
|
guard #available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#endif
|
|
|
|
let dbQueue = try makeDatabaseQueue()
|
|
try dbQueue.inDatabase { db in
|
|
try Person(id: 1, name: "Arthur", email: "arthur@example.com").insert(db)
|
|
try Person(id: 2, name: "Barbara", email: "barbara@example.com").insert(db)
|
|
try Person(id: 3, name: "Craig", email: "craig@example.com").insert(db)
|
|
|
|
let request = Person.filter(Column("id") != 2)
|
|
let deletedIds = try request.deleteAndFetchIds(db)
|
|
XCTAssertEqual(deletedIds, [1, 3])
|
|
}
|
|
}
|
|
|
|
func testJoinedRequestDeleteAll() throws {
|
|
try makeDatabaseQueue().inDatabase { db in
|
|
struct Player: MutablePersistableRecord {
|
|
static let team = belongsTo(Team.self)
|
|
func encode(to container: inout PersistenceContainer) { preconditionFailure("should not be called") }
|
|
}
|
|
|
|
struct Team: MutablePersistableRecord {
|
|
static let players = hasMany(Player.self)
|
|
func encode(to container: inout PersistenceContainer) { preconditionFailure("should not be called") }
|
|
}
|
|
|
|
try db.create(table: "team") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
}
|
|
|
|
try db.create(table: "player") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("team")
|
|
}
|
|
|
|
do {
|
|
try Player.including(required: Player.team).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "player" WHERE "id" IN (\
|
|
SELECT "player"."id" \
|
|
FROM "player" \
|
|
JOIN "team" ON "team"."id" = "player"."teamId")
|
|
""")
|
|
}
|
|
do {
|
|
let alias = TableAlias(name: "p")
|
|
try Player.aliased(alias).including(required: Player.team).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "player" WHERE "id" IN (\
|
|
SELECT "p"."id" \
|
|
FROM "player" "p" \
|
|
JOIN "team" ON "team"."id" = "p"."teamId")
|
|
""")
|
|
}
|
|
do {
|
|
try Team.having(Team.players.isEmpty).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "team" WHERE "id" IN (\
|
|
SELECT "team"."id" \
|
|
FROM "team" \
|
|
LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
|
|
GROUP BY "team"."id" \
|
|
HAVING COUNT(DISTINCT "player"."id") = 0)
|
|
""")
|
|
}
|
|
do {
|
|
try Team.including(all: Team.players).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "team"
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
func testJoinedRequestDeleteAndFetch() throws {
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
guard sqlite3_libversion_number() >= 3035000 else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#else
|
|
guard #available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#endif
|
|
|
|
try makeDatabaseQueue().inDatabase { db in
|
|
struct Player: MutablePersistableRecord, FetchableRecord {
|
|
static let team = belongsTo(Team.self)
|
|
func encode(to container: inout PersistenceContainer) { preconditionFailure("should not be called") }
|
|
init(row: Row) { preconditionFailure("should not be called") }
|
|
}
|
|
|
|
struct Team: MutablePersistableRecord, FetchableRecord {
|
|
// Test RETURNING
|
|
static let databaseSelection: [any SQLSelectable] = [Column("id"), Column("name")]
|
|
static let players = hasMany(Player.self)
|
|
func encode(to container: inout PersistenceContainer) { preconditionFailure("should not be called") }
|
|
init(row: Row) { preconditionFailure("should not be called") }
|
|
}
|
|
|
|
try db.create(table: "team") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.column("name", .text)
|
|
}
|
|
|
|
try db.create(table: "player") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.belongsTo("team")
|
|
}
|
|
|
|
do {
|
|
let request = Player.including(required: Player.team)
|
|
let statement = try request.deleteAndFetchStatement(db, selection: [AllColumns()])
|
|
XCTAssertEqual(statement.sql, """
|
|
DELETE FROM "player" WHERE "id" IN (\
|
|
SELECT "player"."id" \
|
|
FROM "player" \
|
|
JOIN "team" ON "team"."id" = "player"."teamId") \
|
|
RETURNING *
|
|
""")
|
|
XCTAssertEqual(statement.columnNames, ["id", "teamId"])
|
|
|
|
_ = try request.deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "player" WHERE "id" IN (\
|
|
SELECT "player"."id" \
|
|
FROM "player" \
|
|
JOIN "team" ON "team"."id" = "player"."teamId") \
|
|
RETURNING *
|
|
""")
|
|
}
|
|
do {
|
|
let alias = TableAlias(name: "p")
|
|
_ = try Player.aliased(alias).including(required: Player.team).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "player" WHERE "id" IN (\
|
|
SELECT "p"."id" \
|
|
FROM "player" "p" \
|
|
JOIN "team" ON "team"."id" = "p"."teamId") \
|
|
RETURNING *
|
|
""")
|
|
}
|
|
do {
|
|
let request = Team.having(Team.players.isEmpty)
|
|
let statement = try request.deleteAndFetchStatement(db, selection: [AllColumns()])
|
|
XCTAssertEqual(statement.sql, """
|
|
DELETE FROM "team" WHERE "id" IN (\
|
|
SELECT "team"."id" \
|
|
FROM "team" \
|
|
LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
|
|
GROUP BY "team"."id" \
|
|
HAVING COUNT(DISTINCT "player"."id") = 0) \
|
|
RETURNING *
|
|
""")
|
|
XCTAssertEqual(statement.columnNames, ["id", "name"])
|
|
|
|
_ = try request.deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "team" WHERE "id" IN (\
|
|
SELECT "team"."id" \
|
|
FROM "team" \
|
|
LEFT JOIN "player" ON "player"."teamId" = "team"."id" \
|
|
GROUP BY "team"."id" \
|
|
HAVING COUNT(DISTINCT "player"."id") = 0) \
|
|
RETURNING "id", "name"
|
|
""")
|
|
}
|
|
do {
|
|
_ = try Team.including(all: Team.players).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "team" RETURNING "id", "name"
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
func testGroupedRequestDeleteAll() throws {
|
|
try makeDatabaseQueue().inDatabase { db in
|
|
struct Player: MutablePersistableRecord {
|
|
func encode(to container: inout PersistenceContainer) { preconditionFailure("should not be called") }
|
|
}
|
|
struct Passport: MutablePersistableRecord {
|
|
func encode(to container: inout PersistenceContainer) { preconditionFailure("should not be called") }
|
|
}
|
|
try db.create(table: "player") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.column("score", .integer)
|
|
}
|
|
try db.create(table: "passport") { t in
|
|
t.column("countryCode", .text).notNull()
|
|
t.column("citizenId", .integer).notNull()
|
|
t.primaryKey(["countryCode", "citizenId"])
|
|
}
|
|
do {
|
|
try Player.all().groupByPrimaryKey().deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "player" WHERE "id" IN (\
|
|
SELECT "id" \
|
|
FROM "player" \
|
|
GROUP BY "id")
|
|
""")
|
|
}
|
|
do {
|
|
try Player.all().group(-Column("id")).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "player" WHERE "id" IN (\
|
|
SELECT "id" \
|
|
FROM "player" \
|
|
GROUP BY -"id")
|
|
""")
|
|
}
|
|
do {
|
|
try Player.all().group(Column.rowID).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "player" WHERE "id" IN (\
|
|
SELECT "id" \
|
|
FROM "player" \
|
|
GROUP BY "rowid")
|
|
""")
|
|
}
|
|
do {
|
|
try Passport.all().groupByPrimaryKey().deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "passport" WHERE "rowid" IN (\
|
|
SELECT "rowid" \
|
|
FROM "passport" \
|
|
GROUP BY "rowid")
|
|
""")
|
|
}
|
|
do {
|
|
try Passport.all().group(Column.rowID).deleteAll(db)
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "passport" WHERE "rowid" IN (\
|
|
SELECT "rowid" \
|
|
FROM "passport" \
|
|
GROUP BY "rowid")
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
|
|
func testGroupedRequestDeleteAndFetchCursor() throws {
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
guard sqlite3_libversion_number() >= 3035000 else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#else
|
|
guard #available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) else {
|
|
throw XCTSkip("RETURNING clause is not available")
|
|
}
|
|
#endif
|
|
|
|
try makeDatabaseQueue().inDatabase { db in
|
|
struct Player: MutablePersistableRecord, FetchableRecord {
|
|
func encode(to container: inout PersistenceContainer) { preconditionFailure("should not be called") }
|
|
init(row: Row) { preconditionFailure("should not be called") }
|
|
}
|
|
struct Passport: MutablePersistableRecord, FetchableRecord {
|
|
func encode(to container: inout PersistenceContainer) { preconditionFailure("should not be called") }
|
|
init(row: Row) { preconditionFailure("should not be called") }
|
|
}
|
|
try db.create(table: "player") { t in
|
|
t.autoIncrementedPrimaryKey("id")
|
|
t.column("score", .integer)
|
|
}
|
|
try db.create(table: "passport") { t in
|
|
t.column("countryCode", .text).notNull()
|
|
t.column("citizenId", .integer).notNull()
|
|
t.primaryKey(["countryCode", "citizenId"])
|
|
}
|
|
do {
|
|
_ = try Player.all().groupByPrimaryKey().deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "player" WHERE "id" IN (\
|
|
SELECT "id" \
|
|
FROM "player" \
|
|
GROUP BY "id") \
|
|
RETURNING *
|
|
""")
|
|
}
|
|
do {
|
|
_ = try Player.all().group(-Column("id")).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "player" WHERE "id" IN (\
|
|
SELECT "id" \
|
|
FROM "player" \
|
|
GROUP BY -"id") \
|
|
RETURNING *
|
|
""")
|
|
}
|
|
do {
|
|
_ = try Player.all().group(Column.rowID).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "player" WHERE "id" IN (\
|
|
SELECT "id" \
|
|
FROM "player" \
|
|
GROUP BY "rowid") \
|
|
RETURNING *
|
|
""")
|
|
}
|
|
do {
|
|
_ = try Passport.all().groupByPrimaryKey().deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "passport" WHERE "rowid" IN (\
|
|
SELECT "rowid" \
|
|
FROM "passport" \
|
|
GROUP BY "rowid") \
|
|
RETURNING *
|
|
""")
|
|
}
|
|
do {
|
|
_ = try Passport.all().group(Column.rowID).deleteAndFetchCursor(db).next()
|
|
XCTAssertEqual(self.lastSQLQuery, """
|
|
DELETE FROM "passport" WHERE "rowid" IN (\
|
|
SELECT "rowid" \
|
|
FROM "passport" \
|
|
GROUP BY "rowid") \
|
|
RETURNING *
|
|
""")
|
|
}
|
|
}
|
|
}
|
|
}
|