Files
GRDB.swift/GRDB/QueryInterface/SQL/SQLRelation.swift
Gwendal Roué a7b8a73dba Cleanup
After failed experiment:

SELECT ...
FROM parent
JOIN (SELECT * FROM child LIMIT 1) child ON child.parentId = child.id

The problem is that the join condition can't go inside the subquery, because SQLite complains about unknown table "parent". And when the join condition is outside the subquery, the LIMIT clause has been applied too early.

So we're still stuck with:

SELECT ...
FROM parent
JOIN child ON child.id = (SELECT id FROM child WHERE child.parentId = child.id LIMIT 1)

For some reason SQLite accepts the reference to the parent table in this case.
2020-04-19 14:14:13 +02:00

821 lines
30 KiB
Swift

/// A "relation", as defined by the [relational
/// terminology](https://en.wikipedia.org/wiki/Relational_database#Terminology),
/// is "a set of tuples sharing the same attributes; a set of columns and rows."
///
/// SQLRelation is defined with a selection, a source table of query, and an
/// eventual filter and ordering.
///
/// SELECT ... FROM ... WHERE ... ORDER BY ...
/// | | | |
/// | | | • ordering
/// | | • filter
/// | • source
/// • selection
///
/// Other SQL clauses such as GROUP BY, LIMIT are defined one level up, in the
/// SQLQuery type.
///
/// ## Promises
///
/// Filter and ordering are actually "promises", which are only resolved
/// when a database connection is available. This is how we can implement
/// requests such as `Record.filter(key: 1)` or `Record.orderByPrimaryKey()`:
/// both need a database connection in order to introspect the primary key.
///
/// // SELECT * FROM player
/// // WHERE continent = 'EU'
/// // ORDER BY code -- primary key infered from the database schema
/// Country
/// .filter(Column("continent") == "EU")
/// .orderByPrimaryKey()
///
/// ## Children
///
/// Relations may also have children. A child is a link to another relation, and
/// provide support for joins and prefetched relations.
///
/// Relations and their children constitute a tree of relations, which the user
/// builds with associations:
///
/// // Builds a relation with two children:
/// Author
/// .including(required: Author.country)
/// .including(all: Author.books)
///
/// There are four kinds of children:
///
/// - `.oneOptional`:
///
/// Such children are left joined. They may, or not, be included in
/// the selection.
///
/// // SELECT book.*
/// // FROM book
/// // LEFT JOIN author ON author.id = book.id
/// Book.joining(optional: Book.author)
///
/// // SELECT book.*, author.*
/// // FROM book
/// // LEFT JOIN author ON author.id = book.id
/// Book.including(optional: Book.author)
///
/// - `.oneRequired`:
///
/// Such children are inner joined. They may, or not, be included in
/// the selection.
///
/// // SELECT book.*
/// // FROM book
/// // JOIN author ON author.id = book.id AND author.country = 'FR'
/// Book.joining(required: Book.author.filter(Column("country") == "FR"))
///
/// // SELECT book.*, author.*
/// // FROM book
/// // JOIN author ON author.id = book.id AND author.country = 'FR'
/// Book.including(required: Book.author.filter(Column("country") == "FR"))
///
/// - `.allPrefetched`:
///
/// Such children are prefetched using several SQL requests:
///
/// // SELECT * FROM countries WHERE continent = 'EU'
/// // SELECT * FROM passport WHERE countryCode IN ('BE', 'DE', 'FR', ...)
/// Country
/// .filter(Column("continent") == "EU")
/// .including(all: Country.passports)
///
/// - `.allNotPrefetched`:
///
/// Such children are not joined, and not prefetched. They are used as
/// intermediate children towards a prefetched child. In the example
/// below, the country relation has a `.allNotPrefetched` child to
/// passports, and the passport relation has a `.allPrefetched` child
/// to citizens.
///
/// // SELECT * FROM countries WHERE continent = 'EU'
/// // SELECT citizens.* FROM citizens
/// // JOIN passport ON passport.citizenId = citizens.id
/// // AND passport.countryCode IN ('BE', 'DE', 'FR', ...)
/// Country
/// .filter(Column("continent") == "EU")
/// .including(all: Country.citizens)
struct SQLRelation {
struct Child: Refinable {
enum Kind {
// Record.including(optional: association)
case oneOptional
// Record.including(required: association)
case oneRequired
// Record.including(all: association)
case allPrefetched
// Record.including(all: associationThroughPivot)
case allNotPrefetched
var isSingular: Bool {
switch self {
case .oneOptional, .oneRequired:
return true
case .allPrefetched, .allNotPrefetched:
return false
}
}
}
var kind: Kind
var condition: SQLAssociationCondition
var relation: SQLRelation
/// Returns true iff this child can change the parent count.
///
/// Record.including(required: association) // true
/// Record.including(all: association) // false
var impactsParentCount: Bool {
switch kind {
case .oneOptional, .oneRequired:
return true
case .allPrefetched, .allNotPrefetched:
return false
}
}
fileprivate func makeAssociationForKey(_ key: String) -> SQLAssociation {
let key = SQLAssociationKey.fixed(key)
let cardinality: SQLAssociationCardinality
switch kind {
case .oneOptional, .oneRequired:
cardinality = .toOne
case .allPrefetched, .allNotPrefetched:
cardinality = .toMany
}
return SQLAssociation(
key: key,
condition: condition,
relation: relation,
cardinality: cardinality)
}
}
var source: SQLSource
var selection: [SQLSelectable] = []
// Filter is an array of expressions that we'll join with the AND operator.
// This gives nicer output in generated SQL: `(a AND b AND c)` instead of
// `((a AND b) AND c)`.
var filtersPromise: DatabasePromise<[SQLExpression]> = DatabasePromise(value: [])
var ordering: SQLRelation.Ordering = SQLRelation.Ordering()
// If true, the relation refers to the first row only, and we'll need to
// perform explicit limit at the SQL level.
var firstOnly = false
var children: OrderedDictionary<String, Child> = [:]
var prefetchedAssociations: [SQLAssociation] {
children.flatMap { key, child -> [SQLAssociation] in
switch child.kind {
case .allPrefetched:
return [child.makeAssociationForKey(key)]
case .oneOptional, .oneRequired, .allNotPrefetched:
return child.relation.prefetchedAssociations.map { association in
// Remove redundant pivot child
let pivotKey = association.pivot.keyName
let child = child.map(\.relation) { relation in
assert(relation.children[pivotKey] != nil)
return relation.removingChild(forKey: pivotKey)
}
return association.through(child.makeAssociationForKey(key))
}
}
}
}
/// Returns nil if there is no way to express the primary key of the
/// relation as an expression.
func primaryKeyExpression(_ db: Database) throws -> SQLExpression? {
switch source {
case .query:
// No primary key for sub query
return nil
case let .table(tableName: tableName, alias: _):
return try db.primaryKeyExpression(tableName)
}
}
}
extension SQLRelation: Refinable {
func select(_ selection: [SQLSelectable]) -> Self {
with(\.selection, selection)
}
/// Removes all selections from chidren
func selectOnly(_ selection: [SQLSelectable]) -> Self {
self.with(\.selection, selection)
.map(\.children, { $0.mapValues { $0.map(\.relation, { $0.selectOnly([]) }) } })
}
func annotated(with selection: [SQLSelectable]) -> Self {
mapInto(\.selection) { $0.append(contentsOf: selection) }
}
func filter(_ predicate: @escaping (Database) throws -> SQLExpressible) -> Self {
map(\.filtersPromise) { filtersPromise in
filtersPromise.flatMap { filters in
DatabasePromise { try filters + [predicate($0).sqlExpression] }
}
}
}
// Prepending filters for aesthetic reasons
func prependingFilters(_ filters: [SQLExpression]) -> Self {
map(\.filtersPromise) { filtersPromise in
filtersPromise.flatMap { existingFilters in
DatabasePromise { _ in filters + existingFilters }
}
}
}
func unfiltered() -> Self {
with(\.filtersPromise, DatabasePromise(value: []))
}
func order(_ orderings: @escaping (Database) throws -> [SQLOrderingTerm]) -> Self {
with(\.ordering, SQLRelation.Ordering(orderings: orderings))
}
func reversed() -> Self {
map(\.ordering, \.reversed)
}
func unordered() -> Self {
self.with(\.ordering, SQLRelation.Ordering())
.map(\.children, { $0.mapValues { $0.map(\.relation, { $0.unordered() }) } })
}
func qualified(with alias: TableAlias) -> Self {
map(\.source) { $0.qualified(with: alias) }
}
}
extension SQLRelation {
/// Returns a relation extended with an association.
///
/// This method provides support for public joining methods such
/// as `including(required:)`:
///
/// struct Destination: TableRecord { }
/// struct Origin: TableRecord {
/// static let destination = belongsTo(Destination.self)
/// }
///
/// // SELECT origin.*, destination.*
/// // FROM origin
/// // JOIN destination ON destination.id = origin.destinationId
/// let request = Origin.including(required: Origin.destination)
///
/// At low-level, this gives:
///
/// let sqlAssociation = Origin.destination.sqlAssociation
/// let origin = Origin.all().query.relation
/// let relation = origin.appending(sqlAssociation, kind: .oneRequired)
/// let query = SQLQuery(relation: relation)
/// let generator = SQLQueryGenerator(query)
/// let statement, _ = try generator.prepare(db)
/// print(statement.sql)
/// // SELECT origin.*, destination.*
/// // FROM origin
/// // JOIN destination ON destination.originId = origin.id
///
/// This method works for simple direct associations such as BelongsTo or
/// HasMany in the above examples, but also for indirect associations such
/// as HasManyThrough, which have any number of pivot relations between the
/// origin and the destination.
func appendingChild(for association: SQLAssociation, kind: SQLRelation.Child.Kind) -> Self {
// Preserve association cardinality in intermediate steps of
// including(all:), and force desired cardinality otherwize
let isSingular = (kind == .allNotPrefetched)
? association.destination.isSingular
: kind.isSingular
let childKey = association.destination.key.name(singular: isSingular)
let child = SQLRelation.Child(
kind: kind,
condition: association.destination.condition,
relation: association.destination.relation)
let initialSteps = association.steps.dropLast()
if initialSteps.isEmpty {
// This is a direct join from origin to destination, without
// intermediate step.
//
// SELECT origin.*, destination.*
// FROM origin
// JOIN destination ON destination.id = origin.destinationId
//
// let association = Origin.belongsTo(Destination.self)
// Origin.including(required: association)
return appendingChild(child, forKey: childKey)
}
// This is an indirect join from origin to destination, through
// some pivot(s):
//
// SELECT origin.*, destination.*
// FROM origin
// JOIN pivot ON pivot.originId = origin.id
// JOIN destination ON destination.id = pivot.destinationId
//
// let association = Origin.hasMany(
// Destination.self,
// through: Origin.hasMany(Pivot.self),
// via: Pivot.belongsTo(Destination.self))
// Origin.including(required: association)
//
// Let's recurse toward a direct join, by making a new association which
// ends on the last pivot, to which we join our destination:
var reducedAssociation = SQLAssociation(steps: Array(initialSteps))
reducedAssociation.destination.relation = reducedAssociation.destination.relation
.select([]) // Intermediate steps are not prefetched
.appendingChild(child, forKey: childKey)
switch kind {
case .oneRequired, .oneOptional, .allNotPrefetched:
return appendingChild(for: reducedAssociation, kind: kind)
case .allPrefetched:
// Intermediate steps of indirect associations are not prefetched.
//
// For example, the request below prefetches citizens, not
// intermediate passports:
//
// extension Country {
// static let passports = hasMany(Passport.self)
// static let citizens = hasMany(Citizens.self, through: passports, using: Passport.citizen)
// }
// let request = Country.including(all: Country.citizens)
return appendingChild(for: reducedAssociation, kind: .allNotPrefetched)
}
}
private func appendingChild(_ child: SQLRelation.Child, forKey key: String) -> Self {
var relation = self
if let existingChild = relation.children.removeValue(forKey: key) {
guard let mergedChild = existingChild.merged(with: child) else {
// can't merge
fatalError("""
The association key \"\(key)\" is ambiguous. \
Use the Association.forKey(_:) method is order to disambiguate.
""")
}
relation.children.appendValue(mergedChild, forKey: key)
} else {
relation.children.appendValue(child, forKey: key)
}
return relation
}
func removingChild(forKey key: String) -> Self {
mapInto(\.children) { $0.removeValue(forKey: key) }
}
func filteringChildren(_ included: (Child) throws -> Bool) rethrows -> Self {
try map(\.children) { try $0.filter { try included($1) } }
}
}
extension SQLRelation: _JoinableRequest {
func _including(all association: SQLAssociation) -> Self {
appendingChild(for: association, kind: .allPrefetched)
}
func _including(optional association: SQLAssociation) -> Self {
appendingChild(for: association, kind: .oneOptional)
}
func _including(required association: SQLAssociation) -> Self {
appendingChild(for: association, kind: .oneRequired)
}
func _joining(optional association: SQLAssociation) -> Self {
appendingChild(for: association.map(\.destination.relation, { $0.select([]) }), kind: .oneOptional)
}
func _joining(required association: SQLAssociation) -> Self {
appendingChild(for: association.map(\.destination.relation, { $0.select([]) }), kind: .oneRequired)
}
}
// MARK: - SQLSource
enum SQLSource {
case table(tableName: String, alias: TableAlias?)
indirect case query(SQLQuery)
func qualified(with alias: TableAlias) -> SQLSource {
switch self {
case let .table(tableName, sourceAlias):
if let sourceAlias = sourceAlias {
alias.becomeProxy(of: sourceAlias)
return self
} else {
alias.setTableName(tableName)
return .table(tableName: tableName, alias: alias)
}
case let .query(query):
return .query(query.qualified(with: alias))
}
}
}
// MARK: - SQLRelation.Ordering
extension SQLRelation {
/// SQLRelation.Ordering provides the order clause to SQLRelation.
struct Ordering {
private enum Element {
case terms(DatabasePromise<[SQLOrderingTerm]>)
case ordering(SQLRelation.Ordering)
var reversed: Element {
switch self {
case .terms(let terms):
return .terms(terms.map { $0.map(\.reversed) })
case .ordering(let ordering):
return .ordering(ordering.reversed)
}
}
func qualified(with alias: TableAlias) -> Element {
switch self {
case .terms(let terms):
return .terms(terms.map { $0.map { $0.qualifiedOrdering(with: alias) } })
case .ordering(let ordering):
return .ordering(ordering.qualified(with: alias))
}
}
func resolve(_ db: Database) throws -> [SQLOrderingTerm] {
switch self {
case .terms(let terms):
return try terms.resolve(db)
case .ordering(let ordering):
return try ordering.resolve(db)
}
}
}
private var elements: [Element] = []
var isReversed: Bool
var isEmpty: Bool { elements.isEmpty }
private init(elements: [Element], isReversed: Bool) {
self.elements = elements
self.isReversed = isReversed
}
init() {
self.init(
elements: [],
isReversed: false)
}
init(orderings: @escaping (Database) throws -> [SQLOrderingTerm]) {
self.init(
elements: [.terms(DatabasePromise(orderings))],
isReversed: false)
}
var reversed: Ordering {
Ordering(
elements: elements,
isReversed: !isReversed)
}
func qualified(with alias: TableAlias) -> Ordering {
Ordering(
elements: elements.map { $0.qualified(with: alias) },
isReversed: isReversed)
}
func appending(_ ordering: Ordering) -> Ordering {
Ordering(
elements: elements + [.ordering(ordering)],
isReversed: isReversed)
}
func resolve(_ db: Database) throws -> [SQLOrderingTerm] {
if isReversed {
return try elements.flatMap { try $0.reversed.resolve(db) }
} else {
return try elements.flatMap { try $0.resolve(db) }
}
}
}
}
// MARK: - SQLAssociationCondition
/// The condition that links two tables.
///
/// Currently, we only support one kind of condition: foreign keys.
///
/// SELECT ... FROM book JOIN author ON author.id = book.authorId
/// <---- the condition ---->
///
/// When we eventually add support for new ways to link tables,
/// SQLAssociationCondition is the type we'll need to update.
///
/// SQLAssociationCondition adopts Equatable so that we can merge associations:
///
/// // request1 and request2 are equivalent
/// let request1 = Book
/// .including(required: Book.author)
/// let request2 = Book
/// .including(required: Book.author)
/// .including(required: Book.author)
///
/// // request3 and request4 are equivalent
/// let request3 = Book
/// .including(required: Book.author.filter(condition1 && condition2))
/// let request4 = Book
/// .joining(required: Book.author.filter(condition1))
/// .including(optional: Book.author.filter(condition2))
struct SQLAssociationCondition: Equatable {
/// Definition of a foreign key
var foreignKeyRequest: SQLForeignKeyRequest
/// True if the table at the origin of the foreign key is on the left of
/// the sql JOIN operator.
///
/// Let's consider the `book.authorId -> author.id` foreign key.
/// Its origin table is `book`.
///
/// The origin table `book` is on the left of the JOIN operator for
/// the BelongsTo association:
///
/// -- Book.including(required: Book.author)
/// SELECT ... FROM book JOIN author ON author.id = book.authorId
///
/// The origin table `book`is on the right of the JOIN operator for
/// the HasMany and HasOne associations:
///
/// -- Author.including(required: Author.books)
/// SELECT ... FROM author JOIN book ON author.id = book.authorId
var originIsLeft: Bool
var reversed: SQLAssociationCondition {
SQLAssociationCondition(
foreignKeyRequest: foreignKeyRequest,
originIsLeft: !originIsLeft)
}
/// Orient foreignKey according to the originIsLeft flag
func columnMappings(_ db: Database) throws -> [(left: String, right: String)] {
let foreignKeyMapping = try foreignKeyRequest.fetchMapping(db)
if originIsLeft {
return foreignKeyMapping.map { (left: $0.origin, right: $0.destination) }
} else {
return foreignKeyMapping.map { (left: $0.destination, right: $0.origin) }
}
}
/// Resolves the condition into SQL expressions which involve both left
/// and right tables.
///
/// SELECT * FROM left JOIN right ON (right.a = left.b)
/// <---------------->
///
/// - parameter db: A database connection.
/// - parameter leftAlias: A TableAlias for the table on the left of the
/// JOIN operator.
/// - Returns: An array of SQL expression that should be joined with
/// the AND operator.
func expressions(_ db: Database, leftAlias: TableAlias) throws -> [SQLExpression] {
try columnMappings(db).map {
Column($0.right) == QualifiedColumn($0.left, alias: leftAlias)
}
}
/// Resolves the condition into an SQL expression which involves only the
/// right table.
///
/// Given `right.a = left.b`, returns `right.a = 1` or
/// `right.a IN (1, 2, 3)`.
func filteringExpression(_ db: Database, leftRows: [Row]) throws -> SQLExpression {
if leftRows.isEmpty {
// Degenerate case: there is no row to attach
return false.sqlExpression
}
let columnMappings = try self.columnMappings(db)
guard let columnMapping = columnMappings.first else {
// Degenerate case: no joining column
return true.sqlExpression
}
if columnMappings.count == 1 {
// Join on a single right column.
let rightColumn = Column(columnMapping.right)
// Unique database values and filter out NULL:
var dbValues = Set(leftRows.map { $0[columnMapping.left] as DatabaseValue })
dbValues.remove(.null)
if dbValues.isEmpty {
// Can't join
return false.sqlExpression
} else {
// table.a IN (1, 2, 3, ...)
// Sort database values for nicer output.
return dbValues.sorted(by: <).contains(rightColumn)
}
} else {
// Join on a multiple columns.
// ((table.a = 1) AND (table.b = 2)) OR ((table.a = 3) AND (table.b = 4)) ...
return leftRows
.map({ leftRow in
// (table.a = 1) AND (table.b = 2)
columnMappings
.map({ columns -> SQLExpression in
let rightColumn = Column(columns.right)
let leftValue = leftRow[columns.left] as DatabaseValue
return rightColumn == leftValue
})
.joined(operator: .and)
})
.joined(operator: .or)
}
}
}
// MARK: - Merging
//
// "Merging" is an operation that takes two relations and, if they are
// compatible, gathers them into a merged relation.
//
// It is an important feature that allows the user to define associated requests
// in several steps. For example, in the sample code below, both requests are
// equivalent and generate the same SQL query, thanks to merging:
//
// let request1 = Book.including(required: Book.author)
//
// let request2 = Book
// .including(required: Book.author)
// .including(required: Book.author)
extension SQLRelation {
/// Returns nil if relations can't be merged (conflict in source, joins...)
func merged(with other: SQLRelation) -> Self? {
guard firstOnly == other.firstOnly else {
// can't merge
return nil
}
guard let mergedSource = source.merged(with: other.source) else {
// can't merge
return nil
}
let mergedFiltersPromise: DatabasePromise<[SQLExpression]> = filtersPromise.flatMap { filters in
DatabasePromise { try filters + other.filtersPromise.resolve($0) }
}
var mergedChildren: OrderedDictionary<String, SQLRelation.Child> = [:]
for (key, child) in children {
if let otherChild = other.children[key] {
guard let mergedChild = child.merged(with: otherChild) else {
// can't merge
return nil
}
mergedChildren.appendValue(mergedChild, forKey: key)
} else {
mergedChildren.appendValue(child, forKey: key)
}
}
for (key, child) in other.children where mergedChildren[key] == nil {
mergedChildren.appendValue(child, forKey: key)
}
// replace selection unless empty
let mergedSelection = other.selection.isEmpty ? selection : other.selection
// replace ordering unless empty
let mergedOrdering = other.ordering.isEmpty ? ordering : other.ordering
return SQLRelation(
source: mergedSource,
selection: mergedSelection,
filtersPromise: mergedFiltersPromise,
ordering: mergedOrdering,
firstOnly: firstOnly,
children: mergedChildren)
}
}
extension SQLSource {
/// Returns nil if sources can't be merged (conflict in tables, aliases...)
func merged(with other: SQLSource) -> SQLSource? {
switch (self, other) {
case let (.table(tableName: tableName, alias: alias), .table(tableName: otherTableName, alias: otherAlias)):
guard tableName == otherTableName else {
// can't merge
return nil
}
switch (alias, otherAlias) {
case (nil, nil):
return .table(tableName: tableName, alias: nil)
case let (alias?, nil), let (nil, alias?):
return .table(tableName: tableName, alias: alias)
case let (alias?, otherAlias?):
guard let mergedAlias = alias.merged(with: otherAlias) else {
// can't merge
return nil
}
return .table(tableName: tableName, alias: mergedAlias)
}
default:
// can't merge
return nil
}
}
}
extension SQLRelation.Child {
/// Returns nil if joins can't be merged (conflict in condition, relation...)
func merged(with other: SQLRelation.Child) -> Self? {
guard condition == other.condition else {
// can't merge
return nil
}
guard let mergedRelation = relation.merged(with: other.relation) else {
// can't merge
return nil
}
guard let mergedKind = kind.merged(with: other.kind) else {
// can't merge
return nil
}
return SQLRelation.Child(
kind: mergedKind,
condition: condition,
relation: mergedRelation)
}
}
extension SQLRelation.Child.Kind {
/// Returns nil if kinds can't be merged
func merged(with other: SQLRelation.Child.Kind) -> Self? {
switch (self, other) {
case (.oneRequired, .oneRequired),
(.oneRequired, .oneOptional),
(.oneOptional, .oneRequired):
// Equivalent to Record.including(required: association):
//
// Record
// .including(required: association)
// .including(optional: association)
return .oneRequired
case (.oneOptional, .oneOptional):
// Equivalent to Record.including(optional: association):
//
// Record
// .including(optional: association)
// .including(optional: association)
return .oneOptional
case (.allPrefetched, .allPrefetched):
// Equivalent to Record.including(all: association):
//
// Record
// .including(all: association)
// .including(all: association)
return .allPrefetched
case (.allPrefetched, .allNotPrefetched),
(.allNotPrefetched, .allPrefetched):
// Record
// .including(all: associationToDestinationThroughPivot)
// .including(all: associationToPivot)
fatalError("Not implemented: merging a direct association and an indirect one with including(all:)")
case (.allNotPrefetched, .allNotPrefetched):
// Equivalent to Record.including(all: association)
//
// Record
// .including(all: association)
// .including(all: association)
return .allNotPrefetched
default:
// Likely a programmer error:
//
// Record
// .including(all: Author.books.forKey("foo"))
// .including(optional: Author.books.forKey("foo"))
return nil
}
}
}