mirror of
https://github.com/groue/GRDB.swift
synced 2026-11-22 04:00:39 +00:00
750 lines
29 KiB
Swift
750 lines
29 KiB
Swift
/// SQLQueryGenerator is able to generate an SQL SELECT query.
|
|
struct SQLQueryGenerator: Refinable {
|
|
fileprivate private(set) var relation: SQLQualifiedRelation
|
|
private let isDistinct: Bool
|
|
private let groupPromise: DatabasePromise<[SQLExpression]>?
|
|
private let havingExpressions: [SQLExpression]
|
|
private let limit: SQLLimit?
|
|
|
|
init(_ query: SQLQuery) {
|
|
// To generate SQL, we need a "qualified" relation, where all tables,
|
|
// expressions, etc, are identified with table aliases.
|
|
//
|
|
// All those aliases let us disambiguate tables at the SQL level, and
|
|
// prefix columns names. For example, the following request...
|
|
//
|
|
// Book.filter(Column("kind") == Book.Kind.novel)
|
|
// .including(optional: Book.author)
|
|
// .including(optional: Book.translator)
|
|
// .annotated(with: Book.awards.count)
|
|
//
|
|
// ... generates the following SQL, where all identifiers are correctly
|
|
// disambiguated and qualified:
|
|
//
|
|
// SELECT book.*, person1.*, person2.*, COUNT(DISTINCT award.id)
|
|
// FROM book
|
|
// LEFT JOIN person person1 ON person1.id = book.authorId
|
|
// LEFT JOIN person person2 ON person2.id = book.translatorId
|
|
// LEFT JOIN award ON award.bookId = book.id
|
|
// GROUP BY book.id
|
|
// WHERE book.kind = 'novel'
|
|
relation = SQLQualifiedRelation(query.relation)
|
|
|
|
// Qualify group expressions and having clause with the relation alias.
|
|
//
|
|
// This turns `GROUP BY id` INTO `GROUP BY book.id`, and
|
|
// `HAVING MAX(year) < 2000` INTO `HAVING MAX(book.year) < 2000`.
|
|
let alias = relation.sourceAlias
|
|
groupPromise = query.groupPromise?.map { $0.map { $0.qualifiedExpression(with: alias) } }
|
|
havingExpressions = query.havingExpressions.map { $0.qualifiedExpression(with: alias) }
|
|
|
|
// Preserve other flags
|
|
isDistinct = query.isDistinct
|
|
limit = query.limit
|
|
}
|
|
|
|
func sql(_ db: Database, _ context: inout SQLGenerationContext) throws -> String {
|
|
var sql = "SELECT"
|
|
|
|
if isDistinct {
|
|
sql += " DISTINCT"
|
|
}
|
|
|
|
let selection = relation.selection
|
|
GRDBPrecondition(!selection.isEmpty, "Can't generate SQL with empty selection")
|
|
sql += " " + selection.map { $0.resultColumnSQL(&context) }.joined(separator: ", ")
|
|
|
|
sql += " FROM "
|
|
sql += try relation.source.sql(db, &context)
|
|
|
|
for (_, join) in relation.joins {
|
|
sql += " "
|
|
sql += try join.sql(db, &context, leftAlias: relation.sourceAlias)
|
|
}
|
|
|
|
let filters = try relation.filtersPromise.resolve(db)
|
|
if filters.isEmpty == false {
|
|
sql += " WHERE "
|
|
sql += filters.joined(operator: .and).expressionSQL(&context, wrappedInParenthesis: false)
|
|
}
|
|
|
|
if let groupExpressions = try groupPromise?.resolve(db), !groupExpressions.isEmpty {
|
|
sql += " GROUP BY "
|
|
sql += groupExpressions
|
|
.map { $0.expressionSQL(&context, wrappedInParenthesis: false) }
|
|
.joined(separator: ", ")
|
|
}
|
|
|
|
if havingExpressions.isEmpty == false {
|
|
sql += " HAVING "
|
|
sql += havingExpressions.joined(operator: .and).expressionSQL(&context, wrappedInParenthesis: false)
|
|
}
|
|
|
|
let orderings = try relation.ordering.resolve(db)
|
|
if !orderings.isEmpty {
|
|
sql += " ORDER BY "
|
|
sql += orderings
|
|
.map { $0.orderingTermSQL(&context) }
|
|
.joined(separator: ", ")
|
|
}
|
|
|
|
if let limit = limit {
|
|
sql += " LIMIT "
|
|
sql += limit.sql
|
|
}
|
|
|
|
return sql
|
|
}
|
|
|
|
func prepare(_ db: Database) throws -> (SelectStatement, RowAdapter?) {
|
|
try (makeSelectStatement(db), rowAdapter(db))
|
|
}
|
|
|
|
private func optimizedSelectedRegion(_ db: Database, _ selectedRegion: DatabaseRegion) throws -> DatabaseRegion {
|
|
// Can we intersect the region with rowIds?
|
|
//
|
|
// Give up unless request feeds from a single database table
|
|
guard case .table(tableName: let tableName, alias: _) = relation.source else {
|
|
// TODO: try harder
|
|
return selectedRegion
|
|
}
|
|
|
|
// Give up unless primary key is rowId
|
|
let primaryKeyInfo = try db.primaryKey(tableName)
|
|
guard primaryKeyInfo.isRowID else {
|
|
return selectedRegion
|
|
}
|
|
|
|
// The filters knows better
|
|
let filters = try relation.filtersPromise.resolve(db)
|
|
guard let rowIds = filters.joined(operator: .and).matchedRowIds(rowIdName: primaryKeyInfo.rowIDColumn) else {
|
|
return selectedRegion
|
|
}
|
|
|
|
// Database regions are case-sensitive: use the canonical table name
|
|
let canonicalTableName = try db.canonicalTableName(tableName)
|
|
return selectedRegion.tableIntersection(canonicalTableName, rowIds: rowIds)
|
|
}
|
|
|
|
func makeDeleteStatement(_ db: Database) throws -> UpdateStatement {
|
|
switch try grouping(db) {
|
|
case .none:
|
|
guard case .table = relation.source else {
|
|
// Programmer error
|
|
fatalError("Can't delete without any database table")
|
|
}
|
|
|
|
guard relation.joins.isEmpty else {
|
|
return try makeTrivialDeleteStatement(db)
|
|
}
|
|
|
|
var context = SQLGenerationContext.queryContext(aliases: relation.allAliases)
|
|
|
|
var sql = try "DELETE FROM " + relation.source.sql(db, &context)
|
|
|
|
let filters = try relation.filtersPromise.resolve(db)
|
|
if filters.isEmpty == false {
|
|
sql += " WHERE " + filters.joined(operator: .and).expressionSQL(&context, wrappedInParenthesis: false)
|
|
}
|
|
|
|
if let limit = limit {
|
|
let orderings = try relation.ordering.resolve(db)
|
|
if !orderings.isEmpty {
|
|
sql += " ORDER BY " + orderings.map { $0.orderingTermSQL(&context) }.joined(separator: ", ")
|
|
}
|
|
sql += " LIMIT " + limit.sql
|
|
}
|
|
|
|
let statement = try db.makeUpdateStatement(sql: sql)
|
|
statement.arguments = context.arguments
|
|
return statement
|
|
|
|
case .unique:
|
|
return try makeTrivialDeleteStatement(db)
|
|
|
|
case .nonUnique:
|
|
// Programmer error
|
|
fatalError("Can't delete query with GROUP BY clause")
|
|
}
|
|
}
|
|
|
|
/// DELETE FROM table WHERE id IN (SELECT id FROM table ...)
|
|
private func makeTrivialDeleteStatement(_ db: Database) throws -> UpdateStatement {
|
|
guard case let .table(tableName: tableName, alias: _) = relation.source else {
|
|
// Programmer error
|
|
fatalError("Can't delete without any database table")
|
|
}
|
|
|
|
var context = SQLGenerationContext.queryContext(aliases: relation.allAliases)
|
|
let primaryKey = try db.primaryKeyExpression(tableName)
|
|
|
|
var sql = "DELETE FROM \(tableName.quotedDatabaseIdentifier) WHERE "
|
|
sql += primaryKey.expressionSQL(&context, wrappedInParenthesis: false)
|
|
sql += " IN ("
|
|
sql += try map(\.relation, { $0.selectOnly([primaryKey]) }).sql(db, &context)
|
|
sql += ")"
|
|
|
|
let statement = try db.makeUpdateStatement(sql: sql)
|
|
statement.arguments = context.arguments
|
|
return statement
|
|
}
|
|
|
|
/// Returns nil if assignments is empty
|
|
func makeUpdateStatement(
|
|
_ db: Database,
|
|
conflictResolution: Database.ConflictResolution,
|
|
assignments: [ColumnAssignment])
|
|
throws -> UpdateStatement?
|
|
{
|
|
switch try grouping(db) {
|
|
case .none:
|
|
guard case .table = relation.source else {
|
|
// Programmer error
|
|
fatalError("Can't update without any database table")
|
|
}
|
|
|
|
guard relation.joins.isEmpty else {
|
|
return try makeTrivialUpdateStatement(
|
|
db,
|
|
conflictResolution: conflictResolution,
|
|
assignments: assignments)
|
|
}
|
|
|
|
// Check for empty assignments after all programmer errors have
|
|
// been checked.
|
|
if assignments.isEmpty {
|
|
return nil
|
|
}
|
|
|
|
var context = SQLGenerationContext.queryContext(aliases: relation.allAliases)
|
|
|
|
var sql = "UPDATE "
|
|
|
|
if conflictResolution != .abort {
|
|
sql += "OR \(conflictResolution.rawValue) "
|
|
}
|
|
|
|
sql += try relation.source.sql(db, &context)
|
|
|
|
let assignmentsSQL = assignments
|
|
.map { $0.sql(&context) }
|
|
.joined(separator: ", ")
|
|
sql += " SET " + assignmentsSQL
|
|
|
|
let filters = try relation.filtersPromise.resolve(db)
|
|
if filters.isEmpty == false {
|
|
sql += " WHERE " + filters.joined(operator: .and).expressionSQL(&context, wrappedInParenthesis: false)
|
|
}
|
|
|
|
if let limit = limit {
|
|
let orderings = try relation.ordering.resolve(db)
|
|
if !orderings.isEmpty {
|
|
sql += " ORDER BY " + orderings.map { $0.orderingTermSQL(&context) }.joined(separator: ", ")
|
|
}
|
|
sql += " LIMIT " + limit.sql
|
|
}
|
|
|
|
let statement = try db.makeUpdateStatement(sql: sql)
|
|
statement.arguments = context.arguments
|
|
return statement
|
|
|
|
case .unique:
|
|
return try makeTrivialUpdateStatement(db, conflictResolution: conflictResolution, assignments: assignments)
|
|
|
|
case .nonUnique:
|
|
// Programmer error
|
|
fatalError("Can't update query with GROUP BY clause")
|
|
}
|
|
}
|
|
|
|
/// UPDATE table SET ... WHERE id IN (SELECT id FROM table ...)
|
|
/// Returns nil if assignments is empty
|
|
private func makeTrivialUpdateStatement(
|
|
_ db: Database,
|
|
conflictResolution: Database.ConflictResolution,
|
|
assignments: [ColumnAssignment])
|
|
throws -> UpdateStatement?
|
|
{
|
|
guard case let .table(tableName: tableName, alias: _) = relation.source else {
|
|
// Programmer error
|
|
fatalError("Can't delete without any database table")
|
|
}
|
|
|
|
// Check for empty assignments after all programmer errors have
|
|
// been checked.
|
|
if assignments.isEmpty {
|
|
return nil
|
|
}
|
|
|
|
var context = SQLGenerationContext.queryContext(aliases: relation.allAliases)
|
|
let primaryKey = try db.primaryKeyExpression(tableName)
|
|
|
|
// UPDATE table...
|
|
var sql = "UPDATE "
|
|
if conflictResolution != .abort {
|
|
sql += "OR \(conflictResolution.rawValue) "
|
|
}
|
|
sql += tableName.quotedDatabaseIdentifier
|
|
|
|
// SET column = value...
|
|
let assignmentsSQL = assignments
|
|
.map { $0.sql(&context) }
|
|
.joined(separator: ", ")
|
|
sql += " SET " + assignmentsSQL
|
|
|
|
// WHERE id IN (SELECT rowid FROM ...)
|
|
sql += " WHERE "
|
|
sql += primaryKey.expressionSQL(&context, wrappedInParenthesis: false)
|
|
sql += " IN ("
|
|
sql += try map(\.relation, { $0.selectOnly([primaryKey]) }).sql(db, &context)
|
|
sql += ")"
|
|
|
|
let statement = try db.makeUpdateStatement(sql: sql)
|
|
statement.arguments = context.arguments
|
|
return statement
|
|
}
|
|
|
|
/// Returns a select statement
|
|
func makeSelectStatement(_ db: Database) throws -> SelectStatement {
|
|
// Build an SQK generation context with all aliases found in the query,
|
|
// so that we can disambiguate tables that are used several times with
|
|
// SQL aliases.
|
|
var context = SQLGenerationContext.queryContext(aliases: relation.allAliases)
|
|
|
|
// Generate SQL
|
|
let sql = try self.sql(db, &context)
|
|
|
|
// Compile & set arguments
|
|
let statement = try db.makeSelectStatement(sql: sql)
|
|
statement.arguments = context.arguments
|
|
|
|
// Optimize databaseRegion
|
|
statement.selectedRegion = try optimizedSelectedRegion(db, statement.selectedRegion)
|
|
return statement
|
|
}
|
|
|
|
/// Informs about the query grouping
|
|
private enum GroupingInfo {
|
|
/// No grouping at all: SELECT ... FROM player
|
|
case none
|
|
/// Grouped by unique key: SELECT ... FROM player GROUP BY id
|
|
case unique
|
|
/// Grouped by some non-unique columnns: SELECT ... FROM player GROUP BY teamId
|
|
case nonUnique
|
|
}
|
|
|
|
/// Informs about the query grouping
|
|
private func grouping(_ db: Database) throws -> GroupingInfo {
|
|
// Empty group clause: no grouping
|
|
// SELECT * FROM player
|
|
guard let groupExpressions = try groupPromise?.resolve(db), groupExpressions.isEmpty == false else {
|
|
return .none
|
|
}
|
|
|
|
// Grouping by something which is not a column: assume non
|
|
// unique grouping.
|
|
// SELECT * FROM player GROUP BY (score + bonus)
|
|
let qualifiedColumns = groupExpressions.compactMap { $0 as? QualifiedColumn }
|
|
if qualifiedColumns.count != groupExpressions.count {
|
|
return .nonUnique
|
|
}
|
|
|
|
// Grouping something which is not a table: assume non unique grouping.
|
|
guard case let .table(tableName: tableName, alias: alias) = relation.source else {
|
|
return .nonUnique
|
|
}
|
|
|
|
// Grouping by some column(s) which do not come from the main table:
|
|
// assume non unique grouping.
|
|
// SELECT * FROM player JOIN team ... GROUP BY team.id
|
|
guard qualifiedColumns.allSatisfy({ $0.alias == alias }) else {
|
|
return .nonUnique
|
|
}
|
|
|
|
// Grouping by some column(s) which are unique
|
|
// SELECT * FROM player GROUP BY id
|
|
let columnNames = qualifiedColumns.map(\.name)
|
|
if try db.table(tableName, hasUniqueKey: columnNames) {
|
|
return .unique
|
|
}
|
|
|
|
// Grouping by some column(s) which are not unique
|
|
// SELECT * FROM player GROUP BY score
|
|
return .nonUnique
|
|
}
|
|
|
|
/// Returns the row adapter which presents the fetched rows according to the
|
|
/// tree of joined relations.
|
|
///
|
|
/// The adapter is nil for queries without any included relation,
|
|
/// because the fetched rows don't need any processing:
|
|
///
|
|
/// // SELECT * FROM book
|
|
/// let request = Book.all()
|
|
/// for row in try Row.fetchAll(db, request) {
|
|
/// row // [id:1, title:"Moby-Dick"]
|
|
/// let book = Book(row: row)
|
|
/// }
|
|
///
|
|
/// But as soon as the selection includes columns of a included relation,
|
|
/// we need an adapter:
|
|
///
|
|
/// // SELECT book.*, author.* FROM book JOIN author ON author.id = book.authorId
|
|
/// let request = Book.including(required: Book.author)
|
|
/// for row in try Row.fetchAll(db, request) {
|
|
/// row // [id:1, title:"Moby-Dick"]
|
|
/// let book = Book(row: row)
|
|
///
|
|
/// row.scopes["author"] // [id:12, name:"Herman Melville"]
|
|
/// let author: Author = row["author"]
|
|
/// }
|
|
private func rowAdapter(_ db: Database) throws -> RowAdapter? {
|
|
try relation.rowAdapter(db, fromIndex: 0)?.adapter
|
|
}
|
|
}
|
|
|
|
/// A "qualified" relation, where all tables are identified with a table alias.
|
|
///
|
|
/// SELECT ... FROM ... JOIN ... WHERE ... ORDER BY ...
|
|
/// | | | | |
|
|
/// | | | | • ordering
|
|
/// | | | • filterPromise
|
|
/// | | • joins
|
|
/// | • source
|
|
/// • selection
|
|
private struct SQLQualifiedRelation {
|
|
/// The source alias
|
|
var sourceAlias: TableAlias { source.alias }
|
|
|
|
/// All aliases, including aliases of joined relations
|
|
var allAliases: [TableAlias] {
|
|
joins.reduce(into: source.allAliases) {
|
|
$0.append(contentsOf: $1.value.relation.allAliases)
|
|
}
|
|
}
|
|
|
|
/// The source
|
|
///
|
|
/// SELECT ... FROM ... JOIN ... WHERE ... ORDER BY ...
|
|
/// |
|
|
/// • source
|
|
let source: SQLQualifiedSource
|
|
|
|
/// The selection from source, not including selection of joined relations
|
|
private var sourceSelection: [SQLSelectable]
|
|
|
|
/// The full selection, including selection of joined relations
|
|
///
|
|
/// SELECT ... FROM ... JOIN ... WHERE ... ORDER BY ...
|
|
/// |
|
|
/// • selection
|
|
var selection: [SQLSelectable] {
|
|
joins.reduce(into: sourceSelection) {
|
|
$0.append(contentsOf: $1.value.relation.selection)
|
|
}
|
|
}
|
|
|
|
/// The filtering clause
|
|
///
|
|
/// SELECT ... FROM ... JOIN ... WHERE ... ORDER BY ...
|
|
/// |
|
|
/// • filtersPromise
|
|
let filtersPromise: DatabasePromise<[SQLExpression]>
|
|
|
|
/// The ordering of source, not including ordering of joined relations
|
|
private let sourceOrdering: SQLRelation.Ordering
|
|
|
|
/// The full ordering, including orderings of joined relations
|
|
///
|
|
/// SELECT ... FROM ... JOIN ... WHERE ... ORDER BY ...
|
|
/// |
|
|
/// • ordering
|
|
var ordering: SQLRelation.Ordering {
|
|
joins.reduce(sourceOrdering) {
|
|
$0.appending($1.value.relation.ordering)
|
|
}
|
|
}
|
|
|
|
/// The joins
|
|
///
|
|
/// SELECT ... FROM ... JOIN ... WHERE ... ORDER BY ...
|
|
/// |
|
|
/// • joins
|
|
private(set) var joins: OrderedDictionary<String, SQLQualifiedJoin>
|
|
|
|
init(_ relation: SQLRelation) {
|
|
// Qualify the source, so that it be disambiguated with an SQL alias
|
|
// if needed (when a select query uses the same table several times).
|
|
// This disambiguation job will be actually performed by
|
|
// SQLGenerationContext, when the SQLSelectQueryGenerator which owns
|
|
// this SQLQualifiedRelation generates SQL.
|
|
source = SQLQualifiedSource(relation.source)
|
|
let sourceAlias = source.alias
|
|
|
|
// Qualify all joins, selection, filter, and ordering, so that all
|
|
// identifiers can be correctly disambiguated and qualified.
|
|
joins = relation.children.compactMapValues { SQLQualifiedJoin($0) }
|
|
sourceSelection = relation.selection.map { $0.qualifiedSelectable(with: sourceAlias) }
|
|
filtersPromise = relation.filtersPromise.map { $0.map { $0.qualifiedExpression(with: sourceAlias) } }
|
|
sourceOrdering = relation.ordering.qualified(with: sourceAlias)
|
|
}
|
|
|
|
/// See SQLQueryGenerator.rowAdapter(_:)
|
|
///
|
|
/// - parameter db: A database connection.
|
|
/// - parameter startIndex: The index of the leftmost selected column of
|
|
/// this relation in a full SQL query. `startIndex` is 0 for the relation
|
|
/// at the root of a SQLQueryGenerator (as opposed to the
|
|
/// joined relations).
|
|
/// - returns: An optional tuple made of a RowAdapter and the index past the
|
|
/// rightmost selected column of this relation. Nil is returned if this
|
|
/// relations does not need any row adapter.
|
|
func rowAdapter(_ db: Database, fromIndex startIndex: Int) throws -> (adapter: RowAdapter, endIndex: Int)? {
|
|
// Root relation && no join => no need for any adapter
|
|
if startIndex == 0 && joins.isEmpty {
|
|
return nil
|
|
}
|
|
|
|
// The number of columns in source selection. Columns selected by joined
|
|
// relations are appended after.
|
|
let sourceSelectionWidth = try sourceSelection.reduce(0) {
|
|
try $0 + $1.columnCount(db)
|
|
}
|
|
|
|
// Recursively build adapters for each joined relation with a selection.
|
|
// Name them according to the join keys.
|
|
var endIndex = startIndex + sourceSelectionWidth
|
|
var scopes: [String: RowAdapter] = [:]
|
|
for (key, join) in joins {
|
|
if let (joinAdapter, joinEndIndex) = try join.relation.rowAdapter(db, fromIndex: endIndex) {
|
|
scopes[key] = joinAdapter
|
|
endIndex = joinEndIndex
|
|
}
|
|
}
|
|
|
|
// (Root relation || empty selection) && no included relation => no need for any adapter
|
|
if (startIndex == 0 || sourceSelectionWidth == 0) && scopes.isEmpty {
|
|
return nil
|
|
}
|
|
|
|
// Build a RangeRowAdapter extended with the adapters of joined relations.
|
|
//
|
|
// // SELECT book.*, author.* FROM book JOIN author ON author.id = book.authorId
|
|
// let request = Book.including(required: Book.author)
|
|
// for row in try Row.fetchAll(db, request) {
|
|
//
|
|
// The RangeRowAdapter hides the columns appended by joined relations:
|
|
//
|
|
// row // [id:1, title:"Moby-Dick"]
|
|
// let book = Book(row: row)
|
|
//
|
|
// Scopes give access to those joined relations:
|
|
//
|
|
// row.scopes["author"] // [id:12, name:"Herman Melville"]
|
|
// let author: Author = row["author"]
|
|
// }
|
|
let rangeAdapter = RangeRowAdapter(startIndex ..< (startIndex + sourceSelectionWidth))
|
|
let adapter = rangeAdapter.addingScopes(scopes)
|
|
|
|
return (adapter: adapter, endIndex: endIndex)
|
|
}
|
|
|
|
/// Removes all selections from joins
|
|
func selectOnly(_ selection: [SQLSelectable]) -> Self {
|
|
self.with(\.sourceSelection, selection.map { $0.qualifiedSelectable(with: sourceAlias) })
|
|
.map(\.joins, { $0.mapValues { $0.selectOnly([]) } })
|
|
}
|
|
}
|
|
|
|
extension SQLQualifiedRelation: Refinable { }
|
|
|
|
/// A "qualified" source, where all tables are identified with a table alias.
|
|
private enum SQLQualifiedSource {
|
|
case table(tableName: String, alias: TableAlias)
|
|
indirect case query(SQLQueryGenerator)
|
|
|
|
var alias: TableAlias {
|
|
switch self {
|
|
case .table(_, let alias):
|
|
return alias
|
|
case .query(let query):
|
|
return query.relation.sourceAlias
|
|
}
|
|
}
|
|
|
|
var allAliases: [TableAlias] {
|
|
switch self {
|
|
case .table(_, let alias):
|
|
return [alias]
|
|
case .query(let query):
|
|
return query.relation.allAliases
|
|
}
|
|
}
|
|
|
|
init(_ source: SQLSource) {
|
|
switch source {
|
|
case let .table(tableName, alias):
|
|
let alias = alias ?? TableAlias(tableName: tableName)
|
|
self = .table(tableName: tableName, alias: alias)
|
|
case let .query(query):
|
|
self = .query(SQLQueryGenerator(query))
|
|
}
|
|
}
|
|
|
|
func sql(_ db: Database, _ context: inout SQLGenerationContext) throws -> String {
|
|
switch self {
|
|
case let .table(tableName, alias):
|
|
if let aliasName = context.aliasName(for: alias) {
|
|
return "\(tableName.quotedDatabaseIdentifier) \(aliasName.quotedDatabaseIdentifier)"
|
|
} else {
|
|
return "\(tableName.quotedDatabaseIdentifier)"
|
|
}
|
|
case let .query(query):
|
|
return try "(\(query.sql(db, &context)))"
|
|
}
|
|
}
|
|
}
|
|
|
|
/// A "qualified" join, where all tables are identified with a table alias.
|
|
private struct SQLQualifiedJoin: Refinable {
|
|
enum Kind: String {
|
|
case leftJoin = "LEFT JOIN"
|
|
case innerJoin = "JOIN"
|
|
|
|
init?(_ kind: SQLRelation.Child.Kind) {
|
|
switch kind {
|
|
case .oneRequired:
|
|
self = .innerJoin
|
|
case .oneOptional:
|
|
self = .leftJoin
|
|
case .allPrefetched, .allNotPrefetched:
|
|
// Eager loading of to-many associations is not implemented with joins
|
|
return nil
|
|
}
|
|
}
|
|
}
|
|
|
|
enum Target {
|
|
case all
|
|
case first(SQLRelation)
|
|
}
|
|
|
|
var kind: Kind
|
|
var condition: SQLAssociationCondition
|
|
var relation: SQLQualifiedRelation
|
|
var target: Target
|
|
|
|
init?(_ child: SQLRelation.Child) {
|
|
guard let kind = Kind(child.kind) else {
|
|
return nil
|
|
}
|
|
self.kind = kind
|
|
self.condition = child.condition
|
|
|
|
// Remove non-joined grand children
|
|
let relation = child.relation.filteringChildren { Kind($0.kind) != nil }
|
|
|
|
if relation.firstOnly {
|
|
// Split relation into an "outer" relation exposed to the rest of
|
|
// the query, and a "subRelation", private to the join, limited
|
|
// to a single row:
|
|
//
|
|
// v--- outer relation
|
|
// JOIN child ON id = (SELECT id FROM child ... LIMIT 1)
|
|
// ^--- subRelation
|
|
//
|
|
// Outer relation has no filter or ordering: those are handled
|
|
// by the subRelation.
|
|
|
|
let outerRelation = relation
|
|
.with(\.firstOnly, false)
|
|
.unfiltered()
|
|
.unordered()
|
|
|
|
self.relation = SQLQualifiedRelation(outerRelation)
|
|
self.target = .first(relation)
|
|
} else {
|
|
self.relation = SQLQualifiedRelation(relation)
|
|
self.target = .all
|
|
}
|
|
}
|
|
|
|
func sql(_ db: Database, _ context: inout SQLGenerationContext, leftAlias: TableAlias) throws -> String {
|
|
try sql(db, &context, leftAlias: leftAlias, allowingInnerJoin: true)
|
|
}
|
|
|
|
/// Removes all selections from joins
|
|
func selectOnly(_ selection: [SQLSelectable]) -> Self {
|
|
map(\.relation) { $0.selectOnly(selection) }
|
|
}
|
|
|
|
private func sql(
|
|
_ db: Database,
|
|
_ context: inout SQLGenerationContext,
|
|
leftAlias: TableAlias,
|
|
allowingInnerJoin allowsInnerJoin: Bool)
|
|
throws -> String
|
|
{
|
|
var allowsInnerJoin = allowsInnerJoin
|
|
var sql = ""
|
|
|
|
switch self.kind {
|
|
case .innerJoin:
|
|
guard allowsInnerJoin else {
|
|
// TODO: chainOptionalRequired
|
|
//
|
|
// When we eventually implement this, make sure we both support:
|
|
// - joining(optional: assoc.joining(required: ...))
|
|
// - having(assoc.joining(required: ...).isEmpty)
|
|
fatalError("Not implemented: chaining a required association behind an optional association")
|
|
}
|
|
case .leftJoin:
|
|
allowsInnerJoin = false
|
|
}
|
|
|
|
// JOIN table...
|
|
sql += try "\(kind.rawValue) \(relation.source.sql(db, &context))"
|
|
|
|
let rightAlias = relation.sourceAlias
|
|
switch target {
|
|
case .all:
|
|
// ... ON <join conditions> AND <other filters>
|
|
var filters = try condition.expressions(db, leftAlias: leftAlias)
|
|
filters += try relation.filtersPromise.resolve(db)
|
|
if filters.isEmpty == false {
|
|
let filterSQL = filters
|
|
.joined(operator: .and)
|
|
.qualifiedExpression(with: rightAlias)
|
|
.expressionSQL(&context, wrappedInParenthesis: false)
|
|
sql += " ON \(filterSQL)"
|
|
}
|
|
case var .first(subRelation):
|
|
// Subquery: SELECT id ...
|
|
guard let primaryKey = try subRelation.primaryKeyExpression(db) else {
|
|
fatalError("Not implemented: support for WITHOUT ROWID optimization")
|
|
}
|
|
subRelation = subRelation.selectOnly([primaryKey])
|
|
|
|
// Subquery: ... WHERE <condition filters> AND <other filters>
|
|
let filters = try condition.expressions(db, leftAlias: leftAlias)
|
|
subRelation = subRelation.prependingFilters(filters)
|
|
|
|
// Subquery: ... LIMIT 1
|
|
let subQuery = SQLQuery(relation: subRelation, limit: SQLLimit(limit: 1, offset: nil))
|
|
|
|
// ... ON id = (SELECT id FROM table WHERE <condition filters> AND <other filters> LIMIT 1)
|
|
sql += " ON "
|
|
sql += rightAlias[primaryKey].expressionSQL(&context, wrappedInParenthesis: false)
|
|
sql += " = ("
|
|
sql += try SQLQueryGenerator(subQuery).sql(db, &context)
|
|
sql += ")"
|
|
}
|
|
|
|
for (_, join) in relation.joins {
|
|
sql += try " \(join.sql(db, &context, leftAlias: rightAlias, allowingInnerJoin: allowsInnerJoin))"
|
|
}
|
|
|
|
return sql
|
|
}
|
|
}
|