zeus
1699 lines
60 KiB
Swift
1699 lines
60 KiB
Swift
// QueryInterfaceRequest is the type of requests generated by `Table`
|
|
// and `TableRecord`:
|
|
//
|
|
// struct Player: TableRecord { ... }
|
|
// let playerRequest = Player.all() // QueryInterfaceRequest<Player>
|
|
//
|
|
// It wraps an SQLQuery, and has an attached type.
|
|
//
|
|
// The attached RowDecoder type helps decoding raw database values:
|
|
//
|
|
// try dbQueue.read { db in
|
|
// try playerRequest.fetchAll(db) // [Player]
|
|
// }
|
|
//
|
|
// RowDecoder also helps the compiler validate associated requests:
|
|
//
|
|
// playerRequest.including(required: Player.team) // OK
|
|
// fruitRequest.including(required: Player.team) // Does not compile
|
|
|
|
/// A request that builds SQL queries with Swift.
|
|
///
|
|
/// You build a `QueryInterfaceRequest` from a ``TableRecord`` type, or a
|
|
/// ``Table`` instance. For example:
|
|
///
|
|
/// ```swift
|
|
/// struct Player: TableRecord, FetchableRecord, DecodableRecord { }
|
|
///
|
|
/// try dbQueue.read { db in
|
|
/// // SELECT * FROM player
|
|
/// // WHERE name = 'O''Reilly'
|
|
/// // ORDER BY score DESC
|
|
/// let request = Player
|
|
/// .filter(Column("name") == "O'Reilly")
|
|
/// .order(Column("score").desc)
|
|
/// let players: [Player] = try request.fetchAll(db)
|
|
/// }
|
|
/// ```
|
|
///
|
|
/// Most features of `QueryInterfaceRequest` come from the protocols it
|
|
/// conforms to. In particular:
|
|
///
|
|
/// - **Fetching methods** are defined by ``FetchRequest``.
|
|
/// - **Request building methods** are defined by ``DerivableRequest``.
|
|
///
|
|
/// ## Topics
|
|
///
|
|
/// ### Instance Methods
|
|
///
|
|
/// - ``isEmpty(_:)``
|
|
/// - ``limit(_:offset:)``
|
|
///
|
|
/// ### Changing The Type of Fetched Results
|
|
///
|
|
/// - ``asRequest(of:)``
|
|
/// - ``select(_:as:)-282xc``
|
|
/// - ``select(_:as:)-3o8qw``
|
|
/// - ``select(literal:as:)``
|
|
/// - ``select(sql:arguments:as:)``
|
|
/// - ``selectPrimaryKey(as:)``
|
|
///
|
|
/// ### Batch Delete
|
|
///
|
|
/// - ``deleteAll(_:)``
|
|
/// - ``deleteAndFetchIds(_:)``
|
|
/// - ``deleteAndFetchCursor(_:)``
|
|
/// - ``deleteAndFetchAll(_:)``
|
|
/// - ``deleteAndFetchSet(_:)``
|
|
/// - ``deleteAndFetchStatement(_:selection:)``
|
|
///
|
|
/// ### Batch Update
|
|
///
|
|
/// - ``updateAll(_:onConflict:_:)-9r4v``
|
|
/// - ``updateAll(_:onConflict:_:)-49qg8``
|
|
/// - ``updateAndFetchCursor(_:onConflict:_:)``
|
|
/// - ``updateAndFetchAll(_:onConflict:_:)``
|
|
/// - ``updateAndFetchSet(_:onConflict:_:)``
|
|
/// - ``updateAndFetchStatement(_:onConflict:_:selection:)``
|
|
/// - ``ColumnAssignment``
|
|
public struct QueryInterfaceRequest<RowDecoder> {
|
|
var relation: SQLRelation
|
|
}
|
|
|
|
extension QueryInterfaceRequest: Refinable { }
|
|
|
|
extension QueryInterfaceRequest: FetchRequest {
|
|
public var sqlSubquery: SQLSubquery {
|
|
.relation(relation)
|
|
}
|
|
|
|
public func fetchCount(_ db: Database) throws -> Int {
|
|
try relation.fetchCount(db)
|
|
}
|
|
|
|
public func makePreparedRequest(
|
|
_ db: Database,
|
|
forSingleResult singleResult: Bool = false)
|
|
throws -> PreparedRequest
|
|
{
|
|
let generator = SQLQueryGenerator(relation: relation, forSingleResult: singleResult)
|
|
var preparedRequest = try generator.makePreparedRequest(db)
|
|
let associations = relation.prefetchedAssociations
|
|
if associations.isEmpty == false {
|
|
// Eager loading of prefetched associations
|
|
preparedRequest.supplementaryFetch = { [relation] db, rows, willExecuteSupplementaryRequest in
|
|
try prefetch(
|
|
db,
|
|
associations: associations,
|
|
from: relation,
|
|
into: rows,
|
|
willExecuteSupplementaryRequest: willExecuteSupplementaryRequest)
|
|
}
|
|
}
|
|
return preparedRequest
|
|
}
|
|
}
|
|
|
|
// MARK: - Request Derivation
|
|
|
|
extension QueryInterfaceRequest: SelectionRequest {
|
|
public func selectWhenConnected(
|
|
_ selection: @escaping (Database) throws -> [any SQLSelectable])
|
|
-> Self
|
|
{
|
|
with {
|
|
$0.relation = $0.relation.selectWhenConnected { db in
|
|
try selection(db).map(\.sqlSelection)
|
|
}
|
|
}
|
|
}
|
|
|
|
/// Defines the result columns, and defines the type of decoded rows.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// let minScore = min(Column("score"))
|
|
/// let maxScore = max(Column("score"))
|
|
///
|
|
/// // SELECT MAX(score) FROM player
|
|
/// let request = Player.all()
|
|
/// .select([maxScore], as: Int.self)
|
|
/// let maxScore = try request.fetchOne(db) // Int?
|
|
///
|
|
/// // SELECT MIN(score), MAX(score) FROM player
|
|
/// let request = Player.all()
|
|
/// .select([minScore, maxScore], as: Row.self)
|
|
/// if let row = try request.fetchOne(db) {
|
|
/// let minScore: Int = row[0]
|
|
/// let maxScore: Int = row[1]
|
|
/// }
|
|
/// ```
|
|
///
|
|
/// Any previous selection is discarded.
|
|
public func select<T>(_ selection: [any SQLSelectable], as type: T.Type = T.self)
|
|
-> QueryInterfaceRequest<T>
|
|
{
|
|
select(selection).asRequest(of: T.self)
|
|
}
|
|
|
|
/// Defines the result columns, and defines the type of decoded rows.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// let minScore = min(Column("score"))
|
|
/// let maxScore = max(Column("score"))
|
|
///
|
|
/// // SELECT MAX(score) FROM player
|
|
/// let request = Player.all()
|
|
/// .select(maxScore, as: Int.self)
|
|
/// let maxScore = try request.fetchOne(db) // Int?
|
|
///
|
|
/// // SELECT MIN(score), MAX(score) FROM player
|
|
/// let request = Player.all()
|
|
/// .select(minScore, maxScore, as: Row.self)
|
|
/// if let row = try request.fetchOne(db) {
|
|
/// let minScore: Int = row[0]
|
|
/// let maxScore: Int = row[1]
|
|
/// }
|
|
/// ```
|
|
///
|
|
/// Any previous selection is discarded.
|
|
public func select<T>(_ selection: any SQLSelectable..., as type: T.Type = T.self)
|
|
-> QueryInterfaceRequest<T>
|
|
{
|
|
select(selection, as: type)
|
|
}
|
|
|
|
/// Defines the result columns with an SQL string, and defines the type of
|
|
/// decoded rows.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // SELECT name FROM player
|
|
/// let request = Player.all()
|
|
/// .select(sql: "name", as: String.self)
|
|
/// let names = try request.fetchAll(db) // [String]
|
|
///
|
|
/// // SELECT IFNULL(name, 'Anonymous') FROM player
|
|
/// let defaultName = "Anonymous"
|
|
/// let request = Player.all()
|
|
/// .select(sql: "IFNULL(name, ?)", arguments: [defaultName], as: String.self)
|
|
/// let names = try request.fetchAll(db) // [String]
|
|
/// ```
|
|
///
|
|
/// Any previous selection is discarded.
|
|
public func select<T>(
|
|
sql: String,
|
|
arguments: StatementArguments = StatementArguments(),
|
|
as type: T.Type = T.self)
|
|
-> QueryInterfaceRequest<T>
|
|
{
|
|
select(SQL(sql: sql, arguments: arguments), as: type)
|
|
}
|
|
|
|
/// Defines the result columns with an ``SQL`` literal, and defines the type
|
|
/// of decoded rows.
|
|
///
|
|
/// ``SQL`` literals allow you to safely embed raw values in your SQL,
|
|
/// without any risk of syntax errors or SQL injection:
|
|
///
|
|
/// ```swift
|
|
/// // SELECT IFNULL(name, 'Anonymous') FROM player
|
|
/// let defaultName = "Anonymous"
|
|
/// let request = Player.all()
|
|
/// .select(literal: "IFNULL(name, \(defaultName))", as: String.self)
|
|
/// let names = try request.fetchAll(db) // [String]
|
|
/// ```
|
|
///
|
|
/// Any previous selection is discarded.
|
|
public func select<T>(
|
|
literal sqlLiteral: SQL,
|
|
as type: T.Type = T.self)
|
|
-> QueryInterfaceRequest<T>
|
|
{
|
|
select(sqlLiteral, as: type)
|
|
}
|
|
|
|
/// Selects the primary key.
|
|
///
|
|
/// All primary keys are supported:
|
|
///
|
|
/// ```swift
|
|
/// // SELECT id FROM player WHERE ...
|
|
/// let request = try Player.all().selectPrimaryKey(as: Int64.self)
|
|
/// let ids = try request.fetchAll(db) // [Int64]
|
|
///
|
|
/// // SELECT code FROM country WHERE ...
|
|
/// let request = try Country.all().selectPrimaryKey(as: String.self)
|
|
/// let countryCodes = try request.fetchAll(db) // [String]
|
|
///
|
|
/// // SELECT citizenId, countryCode FROM citizenship WHERE ...
|
|
/// let request = try Citizenship.all().selectPrimaryKey(as: Row.self)
|
|
/// let rows = try request.fetchAll(db) // [Row]
|
|
/// ```
|
|
///
|
|
/// For composite primary keys, you can define a ``FetchableRecord`` type:
|
|
///
|
|
/// ```swift
|
|
/// extension Citizenship {
|
|
/// struct ID: Decodable, FetchableRecord {
|
|
/// var citizenId: Int64
|
|
/// var countryCode: String
|
|
/// }
|
|
/// }
|
|
/// let request = try Citizenship.all().selectPrimaryKey(as: Citizenship.ID.self)
|
|
/// let ids = try request.fetchAll(db) // [Citizenship.ID]
|
|
/// ```
|
|
///
|
|
/// Any previous selection is discarded.
|
|
public func selectPrimaryKey<PrimaryKey>(as type: PrimaryKey.Type = PrimaryKey.self)
|
|
-> QueryInterfaceRequest<PrimaryKey>
|
|
{
|
|
with { request in
|
|
let tableName = request.relation.source.tableName
|
|
request.relation = request.relation.selectWhenConnected { db in
|
|
try db.primaryKey(tableName).columns.map { Column($0).sqlSelection }
|
|
}
|
|
}
|
|
.asRequest(of: PrimaryKey.self)
|
|
}
|
|
|
|
public func annotatedWhenConnected(
|
|
with selection: @escaping (Database) throws -> [any SQLSelectable])
|
|
-> Self
|
|
{
|
|
with {
|
|
$0.relation = $0.relation.annotatedWhenConnected { db in
|
|
try selection(db).map(\.sqlSelection)
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
extension QueryInterfaceRequest: FilteredRequest {
|
|
public func filterWhenConnected(
|
|
_ predicate: @escaping (Database) throws -> any SQLExpressible)
|
|
-> Self
|
|
{
|
|
with {
|
|
$0.relation = $0.relation.filterWhenConnected { db in
|
|
try predicate(db).sqlExpression
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
extension QueryInterfaceRequest: OrderedRequest {
|
|
public func orderWhenConnected(
|
|
_ orderings: @escaping (Database) throws -> [any SQLOrderingTerm])
|
|
-> Self
|
|
{
|
|
with {
|
|
$0.relation = $0.relation.orderWhenConnected { db in
|
|
try orderings(db).map(\.sqlOrdering)
|
|
}
|
|
}
|
|
}
|
|
|
|
/// Creates a request that reverses applied orderings.
|
|
///
|
|
/// // SELECT * FROM player ORDER BY name DESC
|
|
/// var request = Player.all().order(Column("name"))
|
|
/// request = request.reversed()
|
|
///
|
|
/// If no ordering was applied, the returned request is identical.
|
|
///
|
|
/// // SELECT * FROM player
|
|
/// var request = Player.all()
|
|
/// request = request.reversed()
|
|
public func reversed() -> Self {
|
|
with {
|
|
$0.relation = $0.relation.reversed()
|
|
}
|
|
}
|
|
|
|
/// Creates a request without any ordering.
|
|
///
|
|
/// // SELECT * FROM player
|
|
/// var request = Player.all().order(Column("name"))
|
|
/// request = request.unordered()
|
|
public func unordered() -> Self {
|
|
with {
|
|
$0.relation = $0.relation.unordered()
|
|
}
|
|
}
|
|
|
|
public func withStableOrder() -> QueryInterfaceRequest<RowDecoder> {
|
|
with {
|
|
$0.relation = $0.relation.withStableOrder()
|
|
}
|
|
}
|
|
}
|
|
|
|
extension QueryInterfaceRequest: AggregatingRequest {
|
|
public func groupWhenConnected(
|
|
_ expressions: @escaping (Database) throws -> [any SQLExpressible])
|
|
-> Self
|
|
{
|
|
with {
|
|
$0.relation = $0.relation.groupWhenConnected { db in
|
|
try expressions(db).map(\.sqlExpression)
|
|
}
|
|
}
|
|
}
|
|
|
|
public func havingWhenConnected(
|
|
_ predicate: @escaping (Database) throws -> any SQLExpressible)
|
|
-> Self
|
|
{
|
|
with {
|
|
$0.relation = $0.relation.havingWhenConnected { db in
|
|
try predicate(db).sqlExpression
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
extension QueryInterfaceRequest: JoinableRequest {
|
|
public func _including(all association: _SQLAssociation) -> Self {
|
|
with {
|
|
$0.relation = $0.relation._including(all: association)
|
|
}
|
|
}
|
|
|
|
public func _including(optional association: _SQLAssociation) -> Self {
|
|
with {
|
|
$0.relation = $0.relation._including(optional: association)
|
|
}
|
|
}
|
|
|
|
public func _including(required association: _SQLAssociation) -> Self {
|
|
with {
|
|
$0.relation = $0.relation._including(required: association)
|
|
}
|
|
}
|
|
|
|
public func _joining(optional association: _SQLAssociation) -> Self {
|
|
with {
|
|
$0.relation = $0.relation._joining(optional: association)
|
|
}
|
|
}
|
|
|
|
public func _joining(required association: _SQLAssociation) -> Self {
|
|
with {
|
|
$0.relation = $0.relation._joining(required: association)
|
|
}
|
|
}
|
|
}
|
|
|
|
extension QueryInterfaceRequest: TableRequest {
|
|
public var databaseTableName: String {
|
|
relation.source.tableName
|
|
}
|
|
|
|
public func aliased(_ alias: TableAlias) -> Self {
|
|
with {
|
|
$0.relation = $0.relation.aliased(alias)
|
|
}
|
|
}
|
|
}
|
|
|
|
extension QueryInterfaceRequest: DerivableRequest {
|
|
public func distinct() -> Self {
|
|
with {
|
|
$0.relation.isDistinct = true
|
|
}
|
|
}
|
|
|
|
/// Returns a limited request.
|
|
///
|
|
/// The returned request fetches `limit` rows, starting at `offset`. For
|
|
/// example:
|
|
///
|
|
/// ```swift
|
|
/// // SELECT * FROM player LIMIT 10
|
|
/// let request = Player.all().limit(10)
|
|
///
|
|
/// // SELECT * FROM player LIMIT 10 OFFSET 20
|
|
/// let request = Player.all().limit(10, offset: 20)
|
|
/// ```
|
|
///
|
|
/// Any previously applied limit is discarded.
|
|
public func limit(_ limit: Int, offset: Int? = nil) -> Self {
|
|
with {
|
|
$0.relation.limit = SQLLimit(limit: limit, offset: offset)
|
|
}
|
|
}
|
|
|
|
public func with<T>(_ cte: CommonTableExpression<T>) -> Self {
|
|
with {
|
|
$0.relation.ctes[cte.tableName] = cte.cte
|
|
}
|
|
}
|
|
}
|
|
|
|
extension QueryInterfaceRequest {
|
|
/// Returns a request that performs an identical database query, but decodes
|
|
/// database rows with `type`.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.read { db in
|
|
/// let maxScore: Int? = try Player
|
|
/// .select(max(scoreColumn))
|
|
/// .asRequest(of: Int.self) // <--
|
|
/// .fetchOne(db)
|
|
/// }
|
|
/// ```
|
|
public func asRequest<T>(of type: T.Type) -> QueryInterfaceRequest<T> {
|
|
QueryInterfaceRequest<T>(relation: relation)
|
|
}
|
|
}
|
|
|
|
// MARK: - Check Existence
|
|
|
|
extension QueryInterfaceRequest {
|
|
/// Returns whether the requests does not match any row in the database.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.read { db in
|
|
/// let arthurIsMissing = try Player
|
|
/// .filter(Column("name") == "Arthur")
|
|
/// .isEmpty(db)
|
|
/// }
|
|
/// ```
|
|
///
|
|
/// - parameter db: A database connection.
|
|
public func isEmpty(_ db: Database) throws -> Bool {
|
|
try !SQLRequest("SELECT \(exists())").fetchOne(db)!
|
|
}
|
|
}
|
|
|
|
// MARK: - Batch Delete
|
|
|
|
extension QueryInterfaceRequest {
|
|
/// Deletes matching rows, and returns the number of deleted rows.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - returns: The number of deleted rows
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
@discardableResult
|
|
public func deleteAll(_ db: Database) throws -> Int {
|
|
let statement = try SQLQueryGenerator(relation: relation).makeDeleteStatement(db)
|
|
try statement.execute()
|
|
return db.changesCount
|
|
}
|
|
}
|
|
|
|
// MARK: - Batch Delete and Fetch
|
|
|
|
extension QueryInterfaceRequest {
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
/// Returns a `DELETE RETURNING` prepared statement.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Delete all players and return their names
|
|
/// // DELETE FROM player RETURNING name
|
|
/// let request = Player.all()
|
|
/// let statement = try request.deleteAndFetchStatement(db, selection: [Column("name")])
|
|
/// let deletedNames = try String.fetchSet(statement)
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter selection: The returned columns (must not be empty).
|
|
/// - returns: A prepared statement.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
/// - precondition: `selection` is not empty.
|
|
public func deleteAndFetchStatement(
|
|
_ db: Database,
|
|
selection: [any SQLSelectable])
|
|
throws -> Statement
|
|
{
|
|
GRDBPrecondition(!selection.isEmpty, "Invalid empty selection")
|
|
return try SQLQueryGenerator(relation: relation).makeDeleteStatement(db, selection: selection)
|
|
}
|
|
|
|
/// Returns a cursor over the records deleted by a
|
|
/// `DELETE RETURNING` statement.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all deleted players
|
|
/// // DELETE FROM player RETURNING *
|
|
/// let request = Player.all()
|
|
/// let players = try request.deleteAndFetchCursor(db)
|
|
/// while let player = try players.next() {
|
|
/// print("Player \(player) was deleted")
|
|
/// }
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - returns: A ``RecordCursor`` over the deleted records.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
public func deleteAndFetchCursor(_ db: Database)
|
|
throws -> RecordCursor<RowDecoder>
|
|
where RowDecoder: FetchableRecord & TableRecord
|
|
{
|
|
let statement = try deleteAndFetchStatement(db, selection: RowDecoder.databaseSelection)
|
|
return try RowDecoder.fetchCursor(statement)
|
|
}
|
|
|
|
/// Executes a `DELETE RETURNING` statement and returns the array of
|
|
/// deleted records.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all deleted players
|
|
/// // DELETE FROM player RETURNING *
|
|
/// let request = Player.all()
|
|
/// let deletedPlayers = try request.deleteAndFetchAll(db)
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - returns: An array of deleted records.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
public func deleteAndFetchAll(_ db: Database)
|
|
throws -> [RowDecoder]
|
|
where RowDecoder: FetchableRecord & TableRecord
|
|
{
|
|
try Array(deleteAndFetchCursor(db))
|
|
}
|
|
|
|
/// Executes a `DELETE RETURNING` statement and returns the set of
|
|
/// deleted records.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all deleted players
|
|
/// // DELETE FROM player RETURNING *
|
|
/// let request = Player.all()
|
|
/// let deletedPlayers = try request.deleteAndFetchSet(db)
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - returns: A set of deleted records.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
public func deleteAndFetchSet(_ db: Database)
|
|
throws -> Set<RowDecoder>
|
|
where RowDecoder: FetchableRecord & TableRecord & Hashable
|
|
{
|
|
try Set(deleteAndFetchCursor(db))
|
|
}
|
|
|
|
/// Executes a `DELETE RETURNING` statement and returns the set of
|
|
/// deleted ids.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch the ids of deleted players
|
|
/// // DELETE FROM player RETURNING id
|
|
/// let request = Player.all()
|
|
/// let deletedPlayerIds = try request.deleteAndFetchIds(db)
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - returns: A set of deleted ids.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
@available(iOS 13, macOS 10.15, tvOS 13, watchOS 6, *) // Identifiable
|
|
public func deleteAndFetchIds(_ db: Database)
|
|
throws -> Set<RowDecoder.ID>
|
|
where RowDecoder: TableRecord & Identifiable,
|
|
RowDecoder.ID: Hashable & DatabaseValueConvertible & StatementColumnConvertible
|
|
{
|
|
let primaryKey = try db.primaryKey(RowDecoder.databaseTableName)
|
|
GRDBPrecondition(
|
|
primaryKey.columns.count == 1,
|
|
"Fetching id requires a single-column primary key in the table \(databaseTableName)")
|
|
|
|
let statement = try deleteAndFetchStatement(db, selection: [Column(primaryKey.columns[0])])
|
|
|
|
return try RowDecoder.ID.fetchSet(statement)
|
|
}
|
|
#else
|
|
/// Returns a `DELETE RETURNING` prepared statement.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Delete all players and return their names
|
|
/// // DELETE FROM player RETURNING name
|
|
/// let request = Player.all()
|
|
/// let statement = try request.deleteAndFetchStatement(db, selection: [Column("name")])
|
|
/// let deletedNames = try String.fetchSet(statement)
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter selection: The returned columns (must not be empty).
|
|
/// - returns: A prepared statement.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
/// - precondition: `selection` is not empty.
|
|
@available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) // SQLite 3.35.0+
|
|
public func deleteAndFetchStatement(
|
|
_ db: Database,
|
|
selection: [any SQLSelectable])
|
|
throws -> Statement
|
|
{
|
|
GRDBPrecondition(!selection.isEmpty, "Invalid empty selection")
|
|
return try SQLQueryGenerator(relation: relation).makeDeleteStatement(db, selection: selection)
|
|
}
|
|
|
|
/// Returns a cursor over the records deleted by a
|
|
/// `DELETE RETURNING` statement.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all deleted players
|
|
/// // DELETE FROM player RETURNING *
|
|
/// let request = Player.all()
|
|
/// let players = try request.deleteAndFetchCursor(db)
|
|
/// while let player = try players.next() {
|
|
/// print("Player \(player) was deleted")
|
|
/// }
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - returns: A ``RecordCursor`` over the deleted records.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
@available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) // SQLite 3.35.0+
|
|
public func deleteAndFetchCursor(_ db: Database)
|
|
throws -> RecordCursor<RowDecoder>
|
|
where RowDecoder: FetchableRecord & TableRecord
|
|
{
|
|
let statement = try deleteAndFetchStatement(db, selection: RowDecoder.databaseSelection)
|
|
return try RowDecoder.fetchCursor(statement)
|
|
}
|
|
|
|
/// Executes a `DELETE RETURNING` statement and returns the array of
|
|
/// deleted records.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all deleted players
|
|
/// // DELETE FROM player RETURNING *
|
|
/// let request = Player.all()
|
|
/// let deletedPlayers = try request.deleteAndFetchAll(db)
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - returns: An array of deleted records.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
@available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) // SQLite 3.35.0+
|
|
public func deleteAndFetchAll(_ db: Database)
|
|
throws -> [RowDecoder]
|
|
where RowDecoder: FetchableRecord & TableRecord
|
|
{
|
|
try Array(deleteAndFetchCursor(db))
|
|
}
|
|
|
|
/// Executes a `DELETE RETURNING` statement and returns the set of
|
|
/// deleted records.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all deleted players
|
|
/// // DELETE FROM player RETURNING *
|
|
/// let request = Player.all()
|
|
/// let deletedPlayers = try request.deleteAndFetchSet(db)
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - returns: A set of deleted records.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
@available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) // SQLite 3.35.0+
|
|
public func deleteAndFetchSet(_ db: Database)
|
|
throws -> Set<RowDecoder>
|
|
where RowDecoder: FetchableRecord & TableRecord & Hashable
|
|
{
|
|
try Set(deleteAndFetchCursor(db))
|
|
}
|
|
|
|
/// Executes a `DELETE RETURNING` statement and returns the set of
|
|
/// deleted ids.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch the ids of deleted players
|
|
/// // DELETE FROM player RETURNING id
|
|
/// let request = Player.all()
|
|
/// let deletedPlayerIds = try request.deleteAndFetchIds(db)
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - returns: A set of deleted ids.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
@available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) // SQLite 3.35.0+
|
|
public func deleteAndFetchIds(_ db: Database)
|
|
throws -> Set<RowDecoder.ID>
|
|
where RowDecoder: TableRecord & Identifiable,
|
|
RowDecoder.ID: Hashable & DatabaseValueConvertible & StatementColumnConvertible
|
|
{
|
|
let primaryKey = try db.primaryKey(RowDecoder.databaseTableName)
|
|
GRDBPrecondition(
|
|
primaryKey.columns.count == 1,
|
|
"Fetching id requires a single-column primary key in the table \(databaseTableName)")
|
|
|
|
let statement = try deleteAndFetchStatement(db, selection: [Column(primaryKey.columns[0])])
|
|
|
|
return try RowDecoder.ID.fetchSet(statement)
|
|
}
|
|
#endif
|
|
}
|
|
|
|
// MARK: - Batch Update
|
|
|
|
extension QueryInterfaceRequest {
|
|
/// The conflict resolution to use for batch updates
|
|
private var defaultConflictResolutionForUpdate: Database.ConflictResolution {
|
|
// In order to look for the default conflict resolution, we perform a
|
|
// runtime check for MutablePersistableRecord, and look for a
|
|
// user-defined default. Such dynamic dispatch is unusual in GRDB, but
|
|
// static dispatch is likely to create bad surprises in generic contexts.
|
|
if let recordType = RowDecoder.self as? any MutablePersistableRecord.Type {
|
|
return recordType.persistenceConflictPolicy.conflictResolutionForUpdate
|
|
} else {
|
|
return .abort
|
|
}
|
|
}
|
|
|
|
/// Updates matching rows, and returns the number of updated rows.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.write { db in
|
|
/// // UPDATE player SET score = 0
|
|
/// let request = Player.all()
|
|
/// try request.updateAll(db, [Column("score").set(to: 0)])
|
|
/// }
|
|
/// ```
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter conflictResolution: A policy for conflict resolution.
|
|
/// - parameter assignments: An array of column assignments.
|
|
/// - returns: The number of updated rows.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
@discardableResult
|
|
public func updateAll(
|
|
_ db: Database,
|
|
onConflict conflictResolution: Database.ConflictResolution? = nil,
|
|
_ assignments: [ColumnAssignment]) throws -> Int
|
|
{
|
|
let conflictResolution = conflictResolution ?? defaultConflictResolutionForUpdate
|
|
guard let updateStatement = try SQLQueryGenerator(relation: relation).makeUpdateStatement(
|
|
db,
|
|
conflictResolution: conflictResolution,
|
|
assignments: assignments) else
|
|
{
|
|
// database not hit
|
|
return 0
|
|
}
|
|
try updateStatement.execute()
|
|
return db.changesCount
|
|
}
|
|
|
|
/// Updates matching rows, and returns the number of updated rows.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.write { db in
|
|
/// // UPDATE player SET score = 0
|
|
/// let request = Player.all()
|
|
/// try request.updateAll(db, Column("score").set(to: 0))
|
|
/// }
|
|
/// ```
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter conflictResolution: A policy for conflict resolution.
|
|
/// - parameter assignments: Column assignments.
|
|
/// - returns: The number of updated rows.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
@discardableResult
|
|
public func updateAll(
|
|
_ db: Database,
|
|
onConflict conflictResolution: Database.ConflictResolution? = nil,
|
|
_ assignments: ColumnAssignment...)
|
|
throws -> Int
|
|
{
|
|
try updateAll(db, onConflict: conflictResolution, assignments)
|
|
}
|
|
}
|
|
|
|
// MARK: - Batch Update and Fetch
|
|
|
|
extension QueryInterfaceRequest {
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
/// Returns an `UPDATE RETURNING` prepared statement.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all updated scores
|
|
/// // UPDATE player SET score = score + 10 RETURNING score
|
|
/// let request = Player.all()
|
|
/// let statement = try request.updateAndFetchStatement(
|
|
/// db, [Column("score") += 10],
|
|
/// select: [Column("score")])
|
|
/// let updatedScores = try Int.fetchAll(statement)
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter conflictResolution: A policy for conflict resolution.
|
|
/// - parameter assignments: An array of column assignments
|
|
/// (must not be empty).
|
|
/// - parameter selection: The returned columns (must not be empty).
|
|
/// - returns: A prepared statement.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
/// - precondition: `selection` and `assignments` are not empty.
|
|
public func updateAndFetchStatement(
|
|
_ db: Database,
|
|
onConflict conflictResolution: Database.ConflictResolution? = nil,
|
|
_ assignments: [ColumnAssignment],
|
|
selection: [any SQLSelectable])
|
|
throws -> Statement
|
|
{
|
|
GRDBPrecondition(!selection.isEmpty, "Invalid empty selection")
|
|
|
|
let conflictResolution = conflictResolution ?? defaultConflictResolutionForUpdate
|
|
guard let updateStatement = try SQLQueryGenerator(relation: relation).makeUpdateStatement(
|
|
db,
|
|
conflictResolution: conflictResolution,
|
|
assignments: assignments,
|
|
selection: selection)
|
|
else {
|
|
fatalError("Invalid empty assignments")
|
|
}
|
|
|
|
return updateStatement
|
|
}
|
|
|
|
/// Returns a cursor over the records updated by an
|
|
/// `UPDATE RETURNING` statement.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all updated players
|
|
/// // UPDATE player SET score = score + 10 RETURNING *
|
|
/// let request = Player.all()
|
|
/// let players = try request.updateAndFetchCursor(db, [Column("score") += 10])
|
|
/// while let player = try players.next() {
|
|
/// print("Player \(player) was updated")
|
|
/// }
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter conflictResolution: A policy for conflict resolution.
|
|
/// - parameter assignments: An array of column assignments.
|
|
/// - returns: A cursor over the updated records.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
/// - precondition: `assignments` is not empty.
|
|
public func updateAndFetchCursor(
|
|
_ db: Database,
|
|
onConflict conflictResolution: Database.ConflictResolution? = nil,
|
|
_ assignments: [ColumnAssignment])
|
|
throws -> RecordCursor<RowDecoder>
|
|
where RowDecoder: FetchableRecord & TableRecord
|
|
{
|
|
let statement = try updateAndFetchStatement(
|
|
db,
|
|
onConflict: conflictResolution,
|
|
assignments,
|
|
selection: RowDecoder.databaseSelection)
|
|
return try RowDecoder.fetchCursor(statement)
|
|
}
|
|
|
|
/// Execute an `UPDATE RETURNING` statement and returns the array of
|
|
/// updated records.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all updated players
|
|
/// // UPDATE player SET score = score + 10 RETURNING *
|
|
/// let request = Player.all()
|
|
/// let updatedPlayers = try request.updateAndFetchAll(db, [Column("score") += 10])
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter conflictResolution: A policy for conflict resolution.
|
|
/// - parameter assignments: An array of column assignments.
|
|
/// - returns: An array of updated records.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
/// - precondition: `assignments` is not empty.
|
|
public func updateAndFetchAll(
|
|
_ db: Database,
|
|
onConflict conflictResolution: Database.ConflictResolution? = nil,
|
|
_ assignments: [ColumnAssignment])
|
|
throws -> [RowDecoder]
|
|
where RowDecoder: FetchableRecord & TableRecord
|
|
{
|
|
try Array(updateAndFetchCursor(db, onConflict: conflictResolution, assignments))
|
|
}
|
|
|
|
/// Execute an `UPDATE RETURNING` statement and returns the set of
|
|
/// updated records.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all updated players
|
|
/// // UPDATE player SET score = score + 10 RETURNING *
|
|
/// let request = Player.all()
|
|
/// let updatedPlayers = try request.updateAndFetchSet(db, [Column("score") += 10])
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter conflictResolution: A policy for conflict resolution.
|
|
/// - parameter assignments: An array of column assignments.
|
|
/// - returns: A set of updated records.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
/// - precondition: `assignments` is not empty.
|
|
public func updateAndFetchSet(
|
|
_ db: Database,
|
|
onConflict conflictResolution: Database.ConflictResolution? = nil,
|
|
_ assignments: [ColumnAssignment])
|
|
throws -> Set<RowDecoder>
|
|
where RowDecoder: FetchableRecord & TableRecord & Hashable
|
|
{
|
|
try Set(updateAndFetchCursor(db, onConflict: conflictResolution, assignments))
|
|
}
|
|
#else
|
|
/// Returns an `UPDATE RETURNING` prepared statement.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all updated scores
|
|
/// // UPDATE player SET score = score + 10 RETURNING score
|
|
/// let request = Player.all()
|
|
/// let statement = try request.updateAndFetchStatement(
|
|
/// db, [Column("score") += 10],
|
|
/// select: [Column("score")])
|
|
/// let updatedScores = try Int.fetchAll(statement)
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter conflictResolution: A policy for conflict resolution.
|
|
/// - parameter assignments: An array of column assignments
|
|
/// (must not be empty).
|
|
/// - parameter selection: The returned columns (must not be empty).
|
|
/// - returns: A prepared statement.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
/// - precondition: `selection` and `assignments` are not empty.
|
|
@available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) // SQLite 3.35.0+
|
|
public func updateAndFetchStatement(
|
|
_ db: Database,
|
|
onConflict conflictResolution: Database.ConflictResolution? = nil,
|
|
_ assignments: [ColumnAssignment],
|
|
selection: [any SQLSelectable])
|
|
throws -> Statement
|
|
{
|
|
GRDBPrecondition(!selection.isEmpty, "Invalid empty selection")
|
|
|
|
let conflictResolution = conflictResolution ?? defaultConflictResolutionForUpdate
|
|
guard let updateStatement = try SQLQueryGenerator(relation: relation).makeUpdateStatement(
|
|
db,
|
|
conflictResolution: conflictResolution,
|
|
assignments: assignments,
|
|
selection: selection)
|
|
else {
|
|
fatalError("Invalid empty assignments")
|
|
}
|
|
|
|
return updateStatement
|
|
}
|
|
|
|
/// Returns a cursor over the records updated by an
|
|
/// `UPDATE RETURNING` statement.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all updated players
|
|
/// // UPDATE player SET score = score + 10 RETURNING *
|
|
/// let request = Player.all()
|
|
/// let players = try request.updateAndFetchCursor(db, [Column("score") += 10])
|
|
/// while let player = try players.next() {
|
|
/// print("Player \(player) was updated")
|
|
/// }
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter conflictResolution: A policy for conflict resolution.
|
|
/// - parameter assignments: An array of column assignments.
|
|
/// - returns: A cursor over the updated records.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
/// - precondition: `assignments` is not empty.
|
|
@available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) // SQLite 3.35.0+
|
|
public func updateAndFetchCursor(
|
|
_ db: Database,
|
|
onConflict conflictResolution: Database.ConflictResolution? = nil,
|
|
_ assignments: [ColumnAssignment])
|
|
throws -> RecordCursor<RowDecoder>
|
|
where RowDecoder: FetchableRecord & TableRecord
|
|
{
|
|
let statement = try updateAndFetchStatement(
|
|
db,
|
|
onConflict: conflictResolution,
|
|
assignments,
|
|
selection: RowDecoder.databaseSelection)
|
|
return try RowDecoder.fetchCursor(statement)
|
|
}
|
|
|
|
/// Execute an `UPDATE RETURNING` statement and returns the array of
|
|
/// updated records.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all updated players
|
|
/// // UPDATE player SET score = score + 10 RETURNING *
|
|
/// let request = Player.all()
|
|
/// let updatedPlayers = try request.updateAndFetchAll(db, [Column("score") += 10])
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter conflictResolution: A policy for conflict resolution.
|
|
/// - parameter assignments: An array of column assignments.
|
|
/// - returns: An array of updated records.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
/// - precondition: `assignments` is not empty.
|
|
@available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) // SQLite 3.35.0+
|
|
public func updateAndFetchAll(
|
|
_ db: Database,
|
|
onConflict conflictResolution: Database.ConflictResolution? = nil,
|
|
_ assignments: [ColumnAssignment])
|
|
throws -> [RowDecoder]
|
|
where RowDecoder: FetchableRecord & TableRecord
|
|
{
|
|
try Array(updateAndFetchCursor(db, onConflict: conflictResolution, assignments))
|
|
}
|
|
|
|
/// Execute an `UPDATE RETURNING` statement and returns the set of
|
|
/// updated records.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// // Fetch all updated players
|
|
/// // UPDATE player SET score = score + 10 RETURNING *
|
|
/// let request = Player.all()
|
|
/// let updatedPlayers = try request.updateAndFetchSet(db, [Column("score") += 10])
|
|
/// ```
|
|
///
|
|
/// - important: Make sure you check the documentation of the `RETURNING`
|
|
/// clause, which describes important limitations and caveats:
|
|
/// <https://www.sqlite.org/lang_returning.html#limitations_and_caveats>.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter conflictResolution: A policy for conflict resolution.
|
|
/// - parameter assignments: An array of column assignments.
|
|
/// - returns: A set of updated records.
|
|
/// - throws: A ``DatabaseError`` whenever an SQLite error occurs.
|
|
/// - precondition: `assignments` is not empty.
|
|
@available(iOS 15, macOS 12, tvOS 15, watchOS 8, *) // SQLite 3.35.0+
|
|
public func updateAndFetchSet(
|
|
_ db: Database,
|
|
onConflict conflictResolution: Database.ConflictResolution? = nil,
|
|
_ assignments: [ColumnAssignment])
|
|
throws -> Set<RowDecoder>
|
|
where RowDecoder: FetchableRecord & TableRecord & Hashable
|
|
{
|
|
try Set(updateAndFetchCursor(db, onConflict: conflictResolution, assignments))
|
|
}
|
|
#endif
|
|
}
|
|
|
|
// MARK: - ColumnAssignment
|
|
|
|
/// A `ColumnAssignment` assigns a value to a column.
|
|
///
|
|
/// You create an assignment from a column and an assignment method or operator,
|
|
/// such as ``ColumnExpression/set(to:)`` or `+=`:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.write { db in
|
|
/// // UPDATE player SET score = 0
|
|
/// let assignment = Column("score").set(to: 0)
|
|
/// try Player.updateAll(db, assignment)
|
|
/// }
|
|
/// ```
|
|
public struct ColumnAssignment {
|
|
var columnName: String
|
|
|
|
/// If nil, this is a "don't assign" assignment.
|
|
var value: SQLExpression?
|
|
|
|
init(columnName: String, value: SQLExpression? = nil) {
|
|
self.columnName = columnName
|
|
self.value = value
|
|
}
|
|
|
|
/// If nil, there's nothing to assign to.
|
|
func sql(_ context: SQLGenerationContext) throws -> String? {
|
|
guard let value else { return nil }
|
|
return try Column(columnName).sqlExpression.sql(context) + " = " + value.sql(context)
|
|
}
|
|
}
|
|
|
|
extension ColumnExpression {
|
|
/// Returns an assignment of this column to an SQL expression.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// Column("valid").set(to: true)
|
|
/// Column("score").set(to: 0)
|
|
/// Column("score").set(to: nil)
|
|
/// Column("score").set(to: Column("score") + Column("bonus"))
|
|
/// ```
|
|
///
|
|
/// For convenience, the last assignment can also be written as:
|
|
///
|
|
/// ```swift
|
|
/// Column("score") += Column("bonus")
|
|
/// ```
|
|
///
|
|
/// Usage:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.write { db in
|
|
/// // UPDATE player SET score = 0
|
|
/// try Player.updateAll(db, Column("score").set(to: 0))
|
|
/// }
|
|
/// ```
|
|
public func set(to value: (any SQLExpressible)?) -> ColumnAssignment {
|
|
ColumnAssignment(columnName: name, value: value?.sqlExpression ?? .null)
|
|
}
|
|
|
|
/// An assignment that does not modify this column.
|
|
public var noOverwrite: ColumnAssignment {
|
|
ColumnAssignment(columnName: name, value: nil)
|
|
}
|
|
}
|
|
|
|
extension ColumnExpression {
|
|
/// Creates an assignment that adds an SQL expression.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// Column("score") += 1
|
|
/// Column("score") += Column("bonus")
|
|
/// ```
|
|
///
|
|
/// Usage:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.write { db in
|
|
/// // UPDATE player SET score = score + 1
|
|
/// try Player.updateAll(db, Column("score") += 1)
|
|
/// }
|
|
/// ```
|
|
public static func += (column: Self, value: some SQLExpressible) -> ColumnAssignment {
|
|
column.set(to: column + value)
|
|
}
|
|
|
|
/// Creates an assignment that subtracts an SQL expression.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// Column("score") -= 1
|
|
/// Column("score") -= Column("bonus")
|
|
/// ```
|
|
///
|
|
/// Usage:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.write { db in
|
|
/// // UPDATE player SET score = score - 1
|
|
/// try Player.updateAll(db, Column("score") -= 1)
|
|
/// }
|
|
/// ```
|
|
public static func -= (column: Self, value: some SQLExpressible) -> ColumnAssignment {
|
|
column.set(to: column - value)
|
|
}
|
|
|
|
/// Creates an assignment that multiplies by an SQL expression.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// Column("score") *= 2
|
|
/// Column("score") *= Column("factor")
|
|
/// ```
|
|
///
|
|
/// Usage:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.write { db in
|
|
/// // UPDATE player SET score = score * 2
|
|
/// try Player.updateAll(db, Column("score") *= 2)
|
|
/// }
|
|
/// ```
|
|
public static func *= (column: Self, value: some SQLExpressible) -> ColumnAssignment {
|
|
column.set(to: column * value)
|
|
}
|
|
|
|
/// Creates an assignment that divides by an SQL expression.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// Column("score") /= 2
|
|
/// Column("score") /= Column("factor")
|
|
/// ```
|
|
///
|
|
/// Usage:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.write { db in
|
|
/// // UPDATE player SET score = score / 2
|
|
/// try Player.updateAll(db, Column("score") /= 2)
|
|
/// }
|
|
/// ```
|
|
public static func /= (column: Self, value: some SQLExpressible) -> ColumnAssignment {
|
|
column.set(to: column / value)
|
|
}
|
|
|
|
/// Creates an assignment that applies a bitwise and.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// Column("mask") &= 2
|
|
/// Column("mask") &= Column("other")
|
|
/// ```
|
|
///
|
|
/// Usage:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.write { db in
|
|
/// // UPDATE player SET score = score & 2
|
|
/// try Player.updateAll(db, Column("mask") &= 2)
|
|
/// }
|
|
/// ```
|
|
public static func &= (column: Self, value: some SQLExpressible) -> ColumnAssignment {
|
|
column.set(to: column & value)
|
|
}
|
|
|
|
/// Creates an assignment that applies a bitwise or.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// Column("mask") |= 2
|
|
/// Column("mask") |= Column("other")
|
|
/// ```
|
|
///
|
|
/// Usage:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.write { db in
|
|
/// // UPDATE player SET score = score | 2
|
|
/// try Player.updateAll(db, Column("mask") |= 2)
|
|
/// }
|
|
/// ```
|
|
public static func |= (column: Self, value: some SQLExpressible) -> ColumnAssignment {
|
|
column.set(to: column | value)
|
|
}
|
|
|
|
/// Creates an assignment that applies a bitwise left shift.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// Column("mask") <<= 2
|
|
/// Column("mask") <<= Column("other")
|
|
/// ```
|
|
///
|
|
/// Usage:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.write { db in
|
|
/// // UPDATE player SET score = score << 2
|
|
/// try Player.updateAll(db, Column("mask") <<= 2)
|
|
/// }
|
|
/// ```
|
|
public static func <<= (column: Self, value: some SQLExpressible) -> ColumnAssignment {
|
|
column.set(to: column << value)
|
|
}
|
|
|
|
/// Creates an assignment that applies a bitwise right shift.
|
|
///
|
|
/// For example:
|
|
///
|
|
/// ```swift
|
|
/// Column("mask") >>= 2
|
|
/// Column("mask") >>= Column("other")
|
|
/// ```
|
|
///
|
|
/// Usage:
|
|
///
|
|
/// ```swift
|
|
/// try dbQueue.write { db in
|
|
/// // UPDATE player SET score = score >> 2
|
|
/// try Player.updateAll(db, Column("mask") >>= 2)
|
|
/// }
|
|
/// ```
|
|
public static func >>= (column: Self, value: some SQLExpressible) -> ColumnAssignment {
|
|
column.set(to: column >> value)
|
|
}
|
|
}
|
|
|
|
// MARK: - Eager loading of hasMany associations
|
|
|
|
// CAUTION: Keep this code in sync with prefetchedRegion(_:_:)
|
|
/// Append rows from prefetched associations into the `originRows` argument.
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter associations: Prefetched associations.
|
|
/// - parameter originRows: The rows that need to be extended with prefetched rows.
|
|
/// - parameter originQuery: The query that was used to fetch `originRows`.
|
|
/// - parameter willExecuteSupplementaryRequest: A closure executed before a
|
|
/// supplementary fetch is performed.
|
|
private func prefetch(
|
|
_ db: Database,
|
|
associations: [_SQLAssociation],
|
|
from originRelation: SQLRelation,
|
|
into originRows: [Row],
|
|
willExecuteSupplementaryRequest: WillExecuteSupplementaryRequest?) throws
|
|
{
|
|
guard let firstOriginRow = originRows.first else {
|
|
// No rows -> no prefetch
|
|
return
|
|
}
|
|
|
|
for association in associations {
|
|
switch association.pivot.condition {
|
|
case .expression:
|
|
// Likely a GRDB bug: such condition only exist for CTEs, which
|
|
// are not prefetched with including(all:)
|
|
fatalError("Not implemented: prefetch association without any foreign key")
|
|
|
|
case let .foreignKey(pivotForeignKey):
|
|
let originTable = originRelation.source.tableName
|
|
let pivotMapping = try pivotForeignKey.joinMapping(db, from: originTable)
|
|
let pivotColumns = pivotMapping.map(\.right)
|
|
let leftColumns = pivotMapping.map(\.left)
|
|
|
|
// We want to avoid the "Expression tree is too large" SQLite error
|
|
// when the foreign key contains several columns, and there are many
|
|
// base rows that overflow SQLITE_LIMIT_EXPR_DEPTH:
|
|
// https://github.com/groue/GRDB.swift/issues/871
|
|
//
|
|
// -- May be too complex for the SQLite engine
|
|
// SELECT * FROM child
|
|
// WHERE (a = ? AND b = ?)
|
|
// OR (a = ? AND b = ?)
|
|
// OR ...
|
|
//
|
|
// Instead, we do not inject any value from the base rows in
|
|
// the prefetch request. Instead, we directly inject the base
|
|
// request as a common table expression (CTE):
|
|
//
|
|
// WITH grdb_base AS (SELECT a, b FROM parent)
|
|
// SELECT * FROM child
|
|
// WHERE (a, b) IN grdb_base
|
|
let usesCommonTableExpression = pivotMapping.count > 1
|
|
|
|
let prefetchRequest: QueryInterfaceRequest<Row>
|
|
if usesCommonTableExpression {
|
|
// HasMany: Author.including(all: Author.books)
|
|
//
|
|
// WITH grdb_base AS (SELECT a, b FROM author)
|
|
// SELECT book.*, book.authorId AS grdb_authorId
|
|
// FROM book
|
|
// WHERE (book.a, book.b) IN grdb_base
|
|
//
|
|
// HasManyThrough: Citizen.including(all: Citizen.countries)
|
|
//
|
|
// WITH grdb_base AS (SELECT a, b FROM citizen)
|
|
// SELECT country.*, passport.citizenId AS grdb_citizenId
|
|
// FROM country
|
|
// JOIN passport ON passport.countryCode = country.code
|
|
// AND (passport.a, passport.b) IN grdb_base
|
|
//
|
|
// In the CTE, ordering and including(all:) children are
|
|
// useless, and we only need to select pivot columns:
|
|
let originRelation = originRelation
|
|
.unorderedUnlessLimited() // only preserve ordering in the CTE if limited
|
|
.removingPrefetchedAssociations()
|
|
.selectOnly(leftColumns.map { SQLExpression.column($0).sqlSelection })
|
|
let originCTE = CommonTableExpression(
|
|
named: "grdb_base",
|
|
request: SQLSubquery.relation(originRelation))
|
|
let pivotRowValue = SQLExpression.rowValue(pivotColumns.map(SQLExpression.column))!
|
|
let pivotFilter = originCTE.contains(pivotRowValue)
|
|
|
|
prefetchRequest = makePrefetchRequest(
|
|
for: association,
|
|
filteringPivotWith: pivotFilter,
|
|
annotatedWith: pivotColumns)
|
|
.with(originCTE)
|
|
} else {
|
|
// HasMany: Author.including(all: Author.books)
|
|
//
|
|
// SELECT *, authorId AS grdb_authorId
|
|
// FROM book
|
|
// WHERE authorId IN (1, 2, 3)
|
|
//
|
|
// HasManyThrough: Citizen.including(all: Citizen.countries)
|
|
//
|
|
// SELECT country.*, passport.citizenId AS grdb_citizenId
|
|
// FROM country
|
|
// JOIN passport ON passport.countryCode = country.code
|
|
// AND passport.citizenId IN (1, 2, 3)
|
|
let pivotFilter = pivotMapping.joinExpression(leftRows: originRows)
|
|
|
|
prefetchRequest = makePrefetchRequest(
|
|
for: association,
|
|
filteringPivotWith: pivotFilter,
|
|
annotatedWith: pivotColumns)
|
|
}
|
|
|
|
if let willExecuteSupplementaryRequest {
|
|
// Support for `Database.dumpRequest`
|
|
try willExecuteSupplementaryRequest(.init(prefetchRequest), association.keyPath)
|
|
}
|
|
let prefetchedRows = try prefetchRequest.fetchAll(db)
|
|
let prefetchedGroups = prefetchedRows.grouped(byDatabaseValuesOnColumns: pivotColumns.map { "grdb_\($0)" })
|
|
let groupingIndexes = firstOriginRow.indexes(forColumns: leftColumns)
|
|
|
|
for row in originRows {
|
|
let groupingKey = groupingIndexes.map { row.impl.databaseValue(atUncheckedIndex: $0) }
|
|
let prefetchedRows = prefetchedGroups[groupingKey, default: []]
|
|
row.prefetchedRows.setRows(prefetchedRows, forKeyPath: association.keyPath)
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/// Returns a request for prefetched rows.
|
|
///
|
|
/// - parameter association: The prefetched association.
|
|
/// - parameter pivotFilter: The expression that filters the pivot of
|
|
/// the association.
|
|
/// - parameter pivotColumns: The pivot columns that annotate the
|
|
/// returned request.
|
|
func makePrefetchRequest(
|
|
for association: _SQLAssociation,
|
|
filteringPivotWith pivotFilter: SQLExpression,
|
|
annotatedWith pivotColumns: [String])
|
|
-> QueryInterfaceRequest<Row>
|
|
{
|
|
// We annotate prefetched rows with pivot columns, so that we can
|
|
// group them.
|
|
//
|
|
// Those pivot columns are necessary when we prefetch
|
|
// indirect associations:
|
|
//
|
|
// // SELECT country.*, passport.citizenId AS grdb_citizenId
|
|
// // -- ^ the necessary pivot column
|
|
// // FROM country
|
|
// // JOIN passport ON passport.countryCode = country.code
|
|
// // AND passport.citizenId IN (1, 2, 3)
|
|
// Citizen.including(all: Citizen.countries)
|
|
//
|
|
// Those pivot columns are redundant when we prefetch direct
|
|
// associations (maybe we'll remove this redundancy later):
|
|
//
|
|
// // SELECT *, authorId AS grdb_authorId
|
|
// // -- ^ the redundant pivot column
|
|
// // FROM book
|
|
// // WHERE authorId IN (1, 2, 3)
|
|
// Author.including(all: Author.books)
|
|
let pivotAlias = TableAlias()
|
|
|
|
let prefetchRelation = association
|
|
.with {
|
|
$0.pivot.relation = $0.pivot.relation
|
|
.aliased(pivotAlias)
|
|
.filter(pivotFilter)
|
|
}
|
|
.destinationRelation()
|
|
.annotated(with: pivotColumns.map { pivotAlias[$0].forKey("grdb_\($0)") })
|
|
|
|
return QueryInterfaceRequest<Row>(relation: prefetchRelation)
|
|
}
|
|
|
|
// CAUTION: Keep this code in sync with prefetch(_:associations:in:)
|
|
/// Returns the region of prefetched associations
|
|
func prefetchedRegion(
|
|
_ db: Database,
|
|
associations: [_SQLAssociation],
|
|
from originTable: String)
|
|
throws -> DatabaseRegion
|
|
{
|
|
try associations.reduce(into: DatabaseRegion()) { (region, association) in
|
|
switch association.pivot.condition {
|
|
case .expression:
|
|
// Likely a GRDB bug: such condition only exist for CTEs, which
|
|
// are not prefetched with including(all:)
|
|
fatalError("Not implemented: prefetch association without any foreign key")
|
|
|
|
case let .foreignKey(pivotForeignKey):
|
|
let pivotMapping = try pivotForeignKey.joinMapping(db, from: originTable)
|
|
let prefetchRegion = try prefetchedRegion(db, association: association, pivotMapping: pivotMapping)
|
|
region.formUnion(prefetchRegion)
|
|
}
|
|
}
|
|
}
|
|
|
|
// CAUTION: Keep this code in sync with prefetch(_:associations:in:)
|
|
func prefetchedRegion(
|
|
_ db: Database,
|
|
association: _SQLAssociation,
|
|
pivotMapping: JoinMapping)
|
|
throws -> DatabaseRegion
|
|
{
|
|
// Filter the pivot on a `DummyRow` in order to make sure all join
|
|
// condition columns are made visible to SQLite, and present in the
|
|
// selected region:
|
|
// ... JOIN right ON right.leftId = ?
|
|
// ^ content of the DummyRow
|
|
let pivotFilter = pivotMapping.joinExpression(leftRows: [DummyRow()])
|
|
|
|
let prefetchRelation = association
|
|
.with {
|
|
$0.pivot.relation = $0.pivot.relation.filter(pivotFilter)
|
|
}
|
|
.destinationRelation()
|
|
|
|
return try SQLQueryGenerator(relation: prefetchRelation)
|
|
.makeStatement(db)
|
|
.databaseRegion // contains region of nested associations
|
|
}
|
|
|
|
extension [Row] {
|
|
/// - precondition: Columns all exist in all rows. All rows have the same
|
|
/// columnns, in the same order.
|
|
fileprivate func grouped(byDatabaseValuesOnColumns columns: [String]) -> [[DatabaseValue]: [Row]] {
|
|
guard let firstRow = first else {
|
|
return [:]
|
|
}
|
|
let indexes = firstRow.indexes(forColumns: columns)
|
|
return Dictionary(grouping: self, by: { row in
|
|
indexes.map { row.impl.databaseValue(atUncheckedIndex: $0) }
|
|
})
|
|
}
|
|
}
|
|
|
|
extension Row {
|
|
/// - precondition: Columns all exist in the row.
|
|
fileprivate func indexes(forColumns columns: [String]) -> [Int] {
|
|
columns.map { column in
|
|
guard let index = index(forColumn: column) else {
|
|
fatalError("Column \(column) is not selected")
|
|
}
|
|
return index
|
|
}
|
|
}
|
|
}
|