import { JoinType , QueryDescription , QueryType , WhereBinding , WhereType , } from "./querybuilder.ts" ; import type { DatabaseDialect } from "./connect.ts" ; import { formatDate } from "./utils/date.ts" ; import { quote } from "./utils/dialect.ts" ; import { uniqueColumnNames } from "./utils/array.ts" ; import type { DatabaseValues } from "./adapters/adapter.ts" ; import { QueryOperator } from "./q.ts" ; /**
 * Transform QueryDescription to an executable SQL query string
 */ export class QueryCompiler { private values : DatabaseValues [ ] = [ ] ; constructor ( private description , private dialect ) { } /**
     * Generate executable SQL statement
     */ public compile ( ) { let query ; switch ( this . description . type ) { case QueryType . Insert : query = this . toInsertSQL ( ) ; break ; case QueryType . Replace : query = this . toInsertSQL ( true ) ; break ; case QueryType . Update : query = this . toUpdateSQL ( ) ; break ; case QueryType . Delete : query = this . toDeleteSQL ( ) ; break ; case QueryType . Select : default : query = this . toSelectSQL ( ) ; break ; } return { text : query , values : this . values } ; } /**
     * Generate `UPDATE` query string
     */ private toUpdateSQL ( ) { let query = [ `UPDATE ${ quote ( this . description . tableName , this . dialect ) } SET` , ] ; if ( ! this . description . values ) { throw new Error ( "Cannot perform update query without values!" ) ; } const values = Object . entries ( this . description . values ) . map ( ( [ key , value ] ) => `${ quote ( key , this . dialect ) } = ${ this . bindValue ( value ) }` ) ; if ( ! ( values . length >= 1 ) ) { throw new Error ( "Cannot perform update query without values!" ) ; } query . push ( values . join ( ", " ) ) ; query = query . concat ( this . collectConstraints ( ) ) ; if ( this . description . returning . length > 0 ) { const returnings = uniqueColumnNames ( this . description . returning ) ; query . push ( "RETURNING" , returnings . map ( ( item ) => quote ( item , this . dialect ) ) . join ( ", " ) ) ; } return query . join ( " " ) + ";" ; } /**
     * Generate `INSERT` query string
     */ private toInsertSQL ( replace = false ) { const tableName = quote ( this . description . tableName , this . dialect ) ; let query = replace ? [ `REPLACE INTO ${ tableName }` ] : [ `INSERT INTO ${ tableName }` ] ; if ( ! this . description . values ) { throw new Error ( `Cannot perform ${ replace ? "replace" : "insert" } query without values!` ) ; } if ( Array . isArray ( this . description . values ) ) { if ( ! ( this . description . values . length >= 1 ) ) { throw new Error ( `Cannot perform ${ replace ? "replace" : "insert" } query without values!` ) ; } const fields = this . description . values . map ( ( v ) => Object . keys ( v ) ) . reduce ( ( accumulator , currentValue ) => { for ( const field of currentValue ) { if ( ! accumulator . includes ( field ) ) { accumulator . push ( field ) ; } } return accumulator ; } ) ; if ( ! ( fields . length >= 1 ) ) { throw new Error ( `Cannot perform ${ replace ? "replace" : "insert" } query without values!` ) ; } const values = this . description . values . map ( ( item ) => { const itemValues = fields . map ( ( field ) => this . bindValue ( ( item [ field ] ) ) ) ; return `(${ itemValues . join ( ", " ) })` ; } ) ; const columns = fields . map ( ( field ) => quote ( field , this . dialect ) ) . join ( ", " ) ; query . push ( `(${ columns })` , "VALUES" , values . join ( ", " ) ) ; } else { const fields = Object . keys ( this . description . values ) ; if ( ! ( fields . length >= 1 ) ) { throw new Error ( `Cannot perform ${ replace ? "replace" : "insert" } query without values!` ) ; } const values = Object . values ( this . description . values ) . map ( ( i ) => this . bindValue ( i ) ) . join ( ", " ) ; const columns = fields . map ( ( field ) => quote ( field , this . dialect ) ) . join ( ", " ) ; query . push ( `(${ columns }) VALUES (${ values })` ) ; } if ( this . description . returning . length > 0 ) { query . push ( "RETURNING" , this . description . returning . map ( ( item ) => quote ( item , this . dialect ) ) . join ( ", " ) ) ; } return query . join ( " " ) + ";" ; } /**
     * Generate `SELECT` query string
     */ private toSelectSQL ( ) { let query = [ `SELECT` ] ; const tableName = quote ( this . description . tableName , this . dialect ) ; if ( this . description . counts . length >= 1 ) { const counts = this . description . counts . map ( ( column ) => { const names = column . columns . map ( ( item ) => this . getColumnName ( item ) ) . join ( ", " ) ; const count = column . distinct ? `COUNT(DISTINCT(${ names }))` : `COUNT(${ names })` ; return column . as ? count + " AS " + quote ( column . as , this . dialect ) : count ; } ) . join ( ", " ) ; query . push ( counts ) ; } else { const columns = uniqueColumnNames ( this . description . columns ) ; if ( columns . length >= 1 ) { query . push ( columns . map ( ( column ) => this . getColumnName ( column ) ) . join ( ", " ) ) ; } else { query . push ( `${ tableName }.*` ) ; } } query . push ( `FROM ${ tableName }` ) ; query = query . concat ( this . collectConstraints ( ) ) ; return query . join ( " " ) + ";" ; } /**
     * Generate `DELETE` query string
     */ private toDeleteSQL ( ) { let query = [ `DELETE FROM ${ quote ( this . description . tableName , this . dialect ) }` , ] ; const constraints = this . collectConstraints ( ) ; if ( ! ( constraints . length >= 1 ) ) { throw new Error ( "Cannot perform delete without any constraints!" ) ; } query = query . concat ( constraints ) ; return query . join ( " " ) + ";" ; } /**
     * Collect query constraints in the current query
     *
     * Example result:
     *
     * ```
     * ["WHERE `users`.`email` = ?", "AND `users`.`age` > 16", "LIMIT 1"]
     * ```
     */ private collectConstraints ( ) { let query = [ ] ; if ( this . description . joins && this . description . joins . length >= 1 ) { for ( const join of this . description . joins ) { const joinTableName = quote ( join . table , this . dialect ) ; const columnA = this . getColumnName ( join . columnA ) ; const columnB = this . getColumnName ( join . columnB ) ; switch ( join . type ) { case JoinType . Right : query . push ( `RIGHT OUTER JOIN ${ joinTableName } ON ${ columnA } = ${ columnB }` ) ; break ; case JoinType . Left : query . push ( `LEFT OUTER JOIN ${ joinTableName } ON ${ columnA } = ${ columnB }` ) ; break ; case JoinType . Full : query . push ( `FULL OUTER JOIN ${ joinTableName } ON ${ columnA } = ${ columnB }` ) ; break ; case JoinType . Inner : default : query . push ( `INNER JOIN ${ joinTableName } ON ${ columnA } = ${ columnB }` ) ; break ; } } } if ( this . description . wheres . length > 0 ) { query . push ( this . getWhereBindings ( "WHERE" , this . description . wheres ) ) ; } if ( this . description . groupBy . length > 0 ) { const columns = uniqueColumnNames ( this . description . groupBy ) . map ( ( item ) => this . getColumnName ( item ) ) . join ( ", " ) ; query . push ( `GROUP BY ${ columns }` ) ; } if ( this . description . havings . length > 0 ) { query . push ( this . getWhereBindings ( "HAVING" , this . description . havings ) ) ; } if ( this . description . orders . length > 0 ) { const orders = this . description . orders . map ( ( order ) => `${ order . column } ${ order . order }` ) . join ( ", " ) ; query . push ( `ORDER BY ${ orders }` ) ; } if ( this . description . limit && this . description . limit > 0 ) { query . push ( `LIMIT ${ this . description . limit }` ) ; } if ( this . description . offset && this . description . offset > 0 ) { query . push ( `OFFSET ${ this . description . offset }` ) ; } return query ; } param value private bindValue ( value ) { if ( Array . isArray ( value ) ) { const values = value . map ( ( item ) => this . bindValue ( item ) ) . join ( ", " ) ; return `(${ values })` ; } if ( typeof value === "undefined" || value === null ) { return "NULL" ; } if ( typeof value === "boolean" ) { if ( this . dialect === "mysql" || this . dialect === "sqlite" ) { return value ? "1" : "0" ; } else { return value ? "TRUE" : "FALSE" ; } } if ( value instanceof Date ) { this . values . push ( formatDate ( value ) ) ; } else { this . values . push ( value ) ; } return this . getPlaceholder ( ) ; } private getPlaceholder ( ) { switch ( this . dialect ) { case "mysql" : case "sqlite" : return "?" ; case "postgres" : return "$" + this . values . length ; default : throw new Error ( `Dialect '${ this . dialect }' is not supported yet!` ) ; } } private getColumnName ( column ) { if ( Array . isArray ( column ) ) { if ( column . length !== 2 ) { throw new Error ( "Alias must have two values!" ) ; } return this . normalizeColumnName ( column [ 0 ] ) + " AS " + column [ 1 ] ; } else { return this . normalizeColumnName ( column ) ; } } private normalizeColumnName ( column ) { const data = column . split ( "." ) ; if ( data . length === 1 ) { return quote ( this . description . tableName , this . dialect ) + "." + quote ( column , this . dialect ) ; } else if ( data . length === 2 ) { return quote ( data [ 0 ] , this . dialect ) + "." + quote ( data [ 1 ] , this . dialect ) ; } else { throw new Error ( `'${ column }' is an invalid column name!` ) ; } } param keyword param bindings private getWhereBindings ( keyword , bindings ) { const query = [ ] ; for ( let index = 0 ; index < bindings . length ; index ++ ) { const { type , expression : { operator , value } , column } = bindings [ index ] ; let expression = `${ this . getColumnName ( column ) } ${ operator }` ; if ( operator === QueryOperator . Between ) { if ( ! Array . isArray ( value ) || value . length !== 2 ) { throw new Error ( "BETWEEN must have two values!" ) ; } const a = this . bindValue ( value [ 0 ] ) ; const b = this . bindValue ( value [ 1 ] ) ; expression += ` ${ a } AND ${ b }` ; } else if ( operator !== QueryOperator . Null && operator !== QueryOperator . NotNull ) { expression += ` ${ this . bindValue ( value ) }` ; } if ( index === 0 ) { if ( type === WhereType . Not ) { query . push ( `${ keyword } NOT ${ expression }` ) ; } else { query . push ( `${ keyword } ${ expression }` ) ; } } else { switch ( type ) { case WhereType . Not : query . push ( `AND NOT ${ expression }` ) ; break ; case WhereType . Or : query . push ( `OR ${ expression }` ) ; break ; case WhereType . Default : default : query . push ( `AND ${ expression }` ) ; break ; } } } return query . join ( " " ) ; } }