Standard libraries that lift up your code.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 

608 lines
17 KiB

import {Inject, Injectable} from '../../di'
import {DatabaseService} from '../DatabaseService'
import {
Constraint, ConstraintConnectionOperator,
ConstraintOperator,
OrderDirection,
OrderStatement, QueryResult,
QuerySource,
SpecifiedField,
} from '../types'
import {Connection} from '../connection/Connection'
import {deepCopy, ErrorWithContext, Maybe} from '../../util'
import {EscapeValue, QuerySafeValue, raw} from '../dialect/SQLDialect'
import {ResultCollection} from './result/ResultCollection'
import {AbstractResultIterable} from './result/AbstractResultIterable'
import {AppClass} from '../../lifecycle/AppClass'
/**
* Type alias for a function that applies some constraints to a builder group.
*/
export type ConstraintGroupClosure<T> = (group: AbstractBuilder<T>) => any
/**
* A base class that facilitates building database queries using a fluent interface.
* This can be specialized by child-classes to yield query results of the given type `T`.
*/
@Injectable()
export abstract class AbstractBuilder<T> extends AppClass {
@Inject()
protected readonly databaseService!: DatabaseService
/** Constraints applied to this query. */
protected constraints: Constraint[] = []
/** The source table to query from. */
protected source?: QuerySource
/** The fields to query from the table. */
protected registeredFields: SpecifiedField[] = []
/** The number of records to skip before the result set. */
protected registeredSkip?: number
/** The max number of records to include in the result set. */
protected registeredTake?: number
/** If true, the query should refer to distinct records. */
protected registeredDistinct = false
/** Array of SQL group-by clauses. */
protected registeredGroupings: string[] = []
/** Array of SQL order-by clauses. */
protected registeredOrders: OrderStatement[] = []
/** The connection on which the query should be executed. */
protected registeredConnection?: Connection
/** Raw SQL to use instead. Overrides builder methods. */
protected rawSql?: string
/**
* Create a new, empty, instance of the current builder.
*/
public abstract getNewInstance(): AbstractBuilder<T>
/**
* Get a result iterable for the built query.
*/
public abstract getResultIterable(): AbstractResultIterable<T>
/**
* Clone the current query to a new AbstractBuilder instance with the same properties.
*/
public clone(): AbstractBuilder<T> {
const bldr = this.getNewInstance()
bldr.constraints = deepCopy(this.constraints)
bldr.source = deepCopy(this.source)
bldr.registeredFields = deepCopy(this.registeredFields)
bldr.registeredSkip = deepCopy(this.registeredSkip)
bldr.registeredTake = deepCopy(this.registeredTake)
bldr.registeredDistinct = deepCopy(this.registeredDistinct)
bldr.registeredGroupings = deepCopy(this.registeredGroupings)
bldr.registeredOrders = deepCopy(this.registeredOrders)
bldr.registeredConnection = this.registeredConnection
bldr.rawSql = this.rawSql
return bldr
}
/** Get the constraints applied to this query. */
public get appliedConstraints(): Constraint[] {
return deepCopy(this.constraints)
}
/** Get the fields that should be included in this query. */
public get appliedFields(): SpecifiedField[] {
return deepCopy(this.registeredFields)
}
/** Get the skip/take values of this query. */
public get appliedPagination(): { skip: number | undefined, take: number | undefined} {
return { skip: this.registeredSkip,
take: this.registeredTake }
}
/** True if the query should be DISTINCT */
public get appliedDistinction(): boolean {
return this.registeredDistinct
}
/** Get the SQL group-by clauses applied to this query. */
public get appliedGroupings(): string[] {
return deepCopy(this.registeredGroupings)
}
/** Get the SQL order-by clauses applied to this query. */
public get appliedOrder(): OrderStatement[] {
return deepCopy(this.registeredOrders)
}
/** Get the raw SQL overriding the builder methods, if it exists. */
public get appliedRawSql(): Maybe<string> {
return this.rawSql
}
/** Get the source table for this query. */
public get querySource(): QuerySource | undefined {
if ( this.source ) {
return deepCopy(this.source)
}
}
/**
* Set the source table (and optional alias) for this query.
* @param table
* @param alias
*/
from(table: string, alias?: string): this {
if ( alias ) {
this.source = { table,
alias }
} else {
this.source = table
}
return this
}
/**
* Alias of `from()`.
* @param table
* @param alias
*/
table(table: string, alias?: string): this {
return this.from(table, alias)
}
/**
* Include the given field (and optional alias) in the query.
* @param field
* @param alias
*/
field(field: string | QuerySafeValue, alias?: string): this {
if ( alias ) {
this.registeredFields.push({ field,
alias })
} else {
this.registeredFields.push(field)
}
return this
}
/**
* Include the given fields in the query.
* @param fields
*/
fields(...fields: SpecifiedField[]): this {
this.registeredFields = [...this.registeredFields, ...fields]
return this
}
/**
* Alias of `fields()`.
* @param fields
*/
returning(...fields: SpecifiedField[]): this {
return this.fields(...fields)
}
/**
* Alias of `fields()`.
* @param fields
*/
select(...fields: SpecifiedField[]): this {
return this.fields(...fields)
}
/**
* Remove all selected fields from this query.
*/
clearFields(): this {
this.registeredFields = []
return this
}
/**
* Apply a new WHERE constraint to the query.
* @param field
* @param operator
* @param operand
*/
where(field: string | ConstraintGroupClosure<T>, operator?: ConstraintOperator, operand?: EscapeValue): this {
this.createConstraint('AND', field, operator, operand)
return this
}
/**
* Apply a new WHERE constraint to the query, without escaping `operand`. Prefer `where()`.
* @param field
* @param operator
* @param operand
*/
whereRaw(field: string, operator: ConstraintOperator, operand: string): this {
this.createConstraint('AND', field, operator, raw(operand))
return this
}
/**
* Apply a new WHERE NOT constraint to the query.
* @param field
* @param operator
* @param operand
*/
whereNot(field: string | ConstraintGroupClosure<T>, operator?: ConstraintOperator, operand?: EscapeValue): this {
this.createConstraint('AND NOT', field, operator, operand)
return this
}
/**
* Apply an OR WHERE constraint to the query.
* @param field
* @param operator
* @param operand
*/
orWhere(field: string | ConstraintGroupClosure<T>, operator?: ConstraintOperator, operand?: EscapeValue): this {
this.createConstraint('OR', field, operator, operand)
return this
}
/**
* Apply an OR WHERE NOT constraint to the query.
* @param field
* @param operator
* @param operand
*/
orWhereNot(field: string | ConstraintGroupClosure<T>, operator?: ConstraintOperator, operand?: EscapeValue): this {
this.createConstraint('OR NOT', field, operator, operand)
return this
}
/**
* Apply an OR WHERE constraint to the query, without escaping `operand`. Prefer `orWhere()`.
* @param field
* @param operator
* @param operand
*/
orWhereRaw(field: string, operator: ConstraintOperator, operand: string): this {
this.createConstraint('OR', field, operator, raw(operand))
return this
}
/**
* Apply a WHERE IN constraint to the query, escaping the values in the set.
* @param field
* @param values
*/
whereIn(field: string, values: EscapeValue): this {
this.constraints.push({
field,
operator: 'IN',
operand: values,
preop: 'AND',
})
return this
}
/**
* Apply a WHERE NOT IN constraint to the query, escaping the values in the set.
* @param field
* @param values
*/
whereNotIn(field: string, values: EscapeValue): this {
this.constraints.push({
field,
operator: 'NOT IN',
operand: values,
preop: 'AND',
})
return this
}
/**
* Apply an OR WHERE IN constraint to the query, escaping the values in the set.
* @param field
* @param values
*/
orWhereIn(field: string, values: EscapeValue): this {
this.constraints.push({
field,
operator: 'IN',
operand: values,
preop: 'OR',
})
return this
}
/**
* Apply an OR WHERE NOT IN constraint to the query, escaping the values in the set.
* @param field
* @param values
*/
orWhereNotIn(field: string, values: EscapeValue): this {
this.constraints.push({
field,
operator: 'NOT IN',
operand: values,
preop: 'OR',
})
return this
}
/**
* Limit the query to a maximum number of rows.
* @param rows
*/
limit(rows: number): this {
this.registeredTake = rows
return this
}
/**
* Alias of `limit()`.
* @param rows
*/
take(rows: number): this {
return this.limit(rows)
}
/**
* Skip the first `rows` many rows in the result set.
* @param rows
*/
skip(rows: number): this {
this.registeredSkip = rows
return this
}
/**
* Alias of `skip()`.
* @param rows
*/
offset(rows: number): this {
return this.skip(rows)
}
/**
* Make the query return only distinct rows.
*/
distinct(): this {
this.registeredDistinct = true
return this
}
/**
* Allow the query to return non-distinct rows. (Undoes `distinct()`.)
*/
notDistinct(): this {
this.registeredDistinct = false
return this
}
/**
* Apply `skip()` and `take()` calls to retrieve the records that should appear on
* the `pageNum` page, assuming each page has `pageSize` many records.
* @param pageNum
* @param pageSize
*/
page(pageNum = 1, pageSize = 20): this {
this.skip(pageSize * (pageNum - 1))
this.take(pageSize)
return this
}
/**
* Apply one or more GROUP-BY clauses to the query.
* @param groupings
*/
groupBy(...groupings: string[]): this {
this.registeredGroupings = groupings
return this
}
/**
* Order the query by the given field.
* @param field
* @param direction
*/
orderBy(field: string, direction: OrderDirection = 'ASC'): this {
this.registeredOrders.push({ field,
direction })
return this
}
/**
* Order the query by the given field, ascending.
* @param field
*/
orderByAscending(field: string): this {
return this.orderBy(field, 'ASC')
}
/**
* Order the query by the given field, descending.
* @param field
*/
orderByDescending(field: string): this {
return this.orderBy(field, 'DESC')
}
/**
* Specify the connection name or instance to execute the query on.
* @param nameOrInstance
*/
connection(nameOrInstance: string | Connection): this {
if ( nameOrInstance instanceof Connection ) {
this.registeredConnection = nameOrInstance
} else {
this.registeredConnection = this.databaseService.get(nameOrInstance)
}
return this
}
/**
* Get a result iterable for the rows of this query.
*/
iterator(): AbstractResultIterable<T> {
if ( !this.registeredConnection ) {
throw new ErrorWithContext(`No connection specified to fetch iterator for query.`)
}
return this.getResultIterable()
}
/**
* Get an async collection of the rows resulting from this query.
*/
get(): ResultCollection<T> {
return new ResultCollection<T>(this.iterator())
}
/**
* Get the first record matched by this query, if it exists.
*/
async first(): Promise<T | undefined> {
return this.iterator().at(0)
}
/**
* Run an UPDATE query for all rows matched by this query, setting the given data.
*
* @example
* ```typescript
* query.table('my_table').update({ my_col: 4 })
* ```
*
* This is equivalent to:
* ```sql
* UPDATE TO my_table
* SET
* my_col = 4
* ```
*
* @param data
*/
async update(data: {[key: string]: EscapeValue}): Promise<QueryResult> {
if ( !this.registeredConnection ) {
throw new ErrorWithContext(`No connection specified to execute update query.`)
}
const query = this.registeredConnection.dialect().renderUpdate(this, data)
return this.registeredConnection.query(query)
}
/**
* Execute a DELETE based on this query.
*
* @example
* ```typescript
* query.table('my_table').where('id', <, 44).delete()
* ```
*
* This is equivalent to:
* ```sql
* DELETE
* FROM my_table
* WHERE
* id < 44
* ```
*
*/
async delete(): Promise<QueryResult> {
if ( !this.registeredConnection ) {
throw new ErrorWithContext(`No connection specified to execute update query.`)
}
const query = this.registeredConnection.dialect().renderDelete(this)
return this.registeredConnection.query(query)
}
/**
* Insert the given rows into the table for this query, returning the fields specified in this query.
*
* @example
* ```typescript
* const rows = [
* { name: 'A' },
* { name: 'B' },
* ]
*
* query.table('my_table')
* .returning('id', 'name')
* .insert(rows)
* ```
*
* This is equivalent to:
* ```sql
* INSERT INTO my_table (name)
* VALUES ('A'), ('B')
* RETURNING id, name
* ```
*
* @param rowOrRows
*/
async insert(rowOrRows: {[key: string]: EscapeValue}|{[key: string]: EscapeValue}[]): Promise<QueryResult> {
if ( !this.registeredConnection ) {
throw new ErrorWithContext(`No connection specified to execute update query.`)
}
const query = this.registeredConnection.dialect().renderInsert(this, rowOrRows)
return this.registeredConnection.query(query)
}
/**
* Returns true if at least one row matches the current query.
*/
async exists(): Promise<boolean> {
if ( !this.registeredConnection ) {
throw new ErrorWithContext(`No connection specified to execute update query.`)
}
const query = this.registeredConnection.dialect().renderExistential(this)
const result = await this.registeredConnection.query(query)
return Boolean(result.rows.first())
}
/**
* Set the query manually. Overrides any builder methods.
* @example
* ```ts
* (new Builder())
* .raw('SELECT NOW() AS example_column')
* .get()
* ```
* @param sql
*/
raw(sql: string): this {
this.rawSql = sql
return this
}
/**
* Adds a constraint to this query. This is used internally by the various `where`, `whereIn`, `orWhereNot`, &c.
* @param preop
* @param field
* @param operator
* @param operand
* @private
*/
private createConstraint(preop: ConstraintConnectionOperator, field: string | ConstraintGroupClosure<T>, operator?: ConstraintOperator, operand?: any): void {
if ( typeof field === 'function' ) {
const builder = this.getNewInstance()
field(builder)
this.constraints.push({
preop,
items: builder.appliedConstraints,
})
} else if ( field && operator && typeof operand !== 'undefined' ) {
this.constraints.push({
field,
operator,
operand,
preop, // FIXME escape operand
})
}
}
}