daton/orm/src/builder/types.ts

import {WhereOperator} from '../../../lib/src/collection/Where.ts'
import RawValue from './RawValue.ts'
import {Select} from './type/Select.ts'

/**
 * Represents a field or set of fields.
 */
export type FieldSet = string | string[]

/**
 * Represents a table name, or table name and alias.
 */
export type QuerySource = string | { ref: QuerySource, alias: string }

/**
 * Valid JOIN clause operators.
 */
export type JoinOperator = 'JOIN' | 'LEFT JOIN' | 'LEFT OUTER JOIN' | 'RIGHT JOIN' | 'RIGHT OUTER JOIN' | 'FULL OUTER JOIN' | 'INNER JOIN' | 'CROSS JOIN'

/**
 * Valid operators which can join WHERE clauses.
 */
export type WherePreOperator = 'AND' | 'OR' | 'AND NOT' | 'OR NOT'

/**
 * Abstract representation of a single WHERE clause.
 */
export type WhereClause = { field: string, operator: SQLWhereOperator, operand: string, preop: WherePreOperator }

/**
 * Group of where clauses, and the operator which should join them.
 */
export type WhereGroup = { items: WhereStatement[], preop: WherePreOperator }

/**
 * A single WHERE statement.
 */
export type WhereStatement = WhereClause | WhereGroup

/**
 * Operators which can be used in SQL WHERE clauses.
 */
export type SQLWhereOperator = WhereOperator | 'IN' | 'NOT IN' | 'LIKE' | 'BETWEEN' | 'NOT BETWEEN' | 'IS' | 'IS NOT'

/**
 * Directions for ORDER BY clauses.
 */
export type OrderDirection = 'ASC' | 'DESC'

/**
 * Abstract representation of an ORDER BY clause.
 */
export type OrderStatement = { direction: OrderDirection, field: string }

/**
 * Valid operators which can join HAVING clauses.
 */
export type HavingPreOperator = WherePreOperator

/**
 * Abstract representation of a single HAVING clause.
 */
export type HavingClause = WhereClause

/**
 * Group of having clauses, and the operator which should join them.
 */
export type HavingGroup = WhereGroup

/**
 * A single HAVING statement.
 */
export type HavingStatement = HavingClause | HavingGroup

/**
 * Valid operators which can be used in SQL HAVING clauses.
 */
export type SQLHavingOperator = SQLWhereOperator

/**
 * A value which can be escaped to be interpolated into an SQL query.
 */
export type EscapedValue = string | number | boolean | Date | RawValue | EscapedValue[] | Select<any>

/**
 * Representation of a field and its value.
 */
export type FieldValue = { field: string, value: EscapedValue }

/**
 * Object representation of a number of fields and their values.
 */
export type FieldValueObject = { [field: string]: EscapedValue }

/**
 * Abstract reference to a particular database table, and its alias.
 */
export type TableRef = {
    table: string,
    database?: string,
    alias?: string
}

/**
 * Returns true if the given object is a valid table ref.
 * @param something
 * @return boolean
 */
export function isTableRef(something: any): something is TableRef {
    let is = true
    is = is && typeof something?.table === 'string'

    if ( typeof something?.database !== 'undefined' ) {
         is = typeof something?.database === 'string'
    }

    if ( typeof something?.alias !== 'undefined' ) {
        is = typeof something?.alias === 'string'
    }

    return is
}

/**
 * Returns true if the given item is a valid WHERE pre-operator.
 * @param something
 * @return boolean
 */
export function isWherePreOperator(something: any): something is WherePreOperator {
    return ['AND', 'OR', 'AND NOT', 'OR NOT'].includes(something)
}

/**
 * Returns true if the given item is a valid HAVING clause.
 * @param something
 * @return boolean
 */
export function isHavingClause(something: any): something is HavingClause {
    return isWhereClause(something)
}

/**
 * Returns true if the given item is a valid WHERE clause.
 * @param something
 * @return boolean
 */
export function isWhereClause(something: any): something is WhereClause {
    return typeof something?.field === 'string'
        && typeof something?.operator === 'string' // TODO check this better
        && typeof something?.operand === 'string'
        && isWherePreOperator(something?.preop)
}

/**
 * Returns true if the given item is a valid HAVING group.
 * @param something
 * @return boolean
 */
export function isHavingGroup(something: any): something is HavingGroup {
    return isWhereGroup(something)
}

/**
 * Returns true if the given item is a valid WHERE group.
 * @param something
 * @return boolean
 */
export function isWhereGroup(something: any): something is WhereGroup {
    return Array.isArray(something?.items)
        && something.items.every((item: any) => isWhereStatement(item))
        && isWherePreOperator(something?.preop)
}

/**
 * Returns true if the given value is a valid where statement.
 * @param something
 * @return boolean
 */
export function isWhereStatement(something: any): something is WhereStatement {
    return isWhereClause(something) || isWhereGroup(something)
}

/**
 * Escapes the value so it can be inserted into an SQL query string.
 * @param {EscapedValue} value
 * @return string
 */
export function escape(value: EscapedValue): string {
    if ( value instanceof Select ) {
        return `(${value.sql(5)})`
    } else if ( value instanceof RawValue ) {
        return value.value
    } else if ( Array.isArray(value) ) {
        return `(${value.map(escape).join(',')})`
    } else if ( String(value).toLowerCase() === 'true' ) {
        return 'TRUE'
    } else if ( String(value).toLowerCase() === 'false' ) {
        return 'FALSE'
    } else if ( typeof value === 'number' ) {
        return `${value}`
    } else if ( value === true ) {
        return 'TRUE'
    } else if ( value === false ) {
        return 'FALSE'
    } else if ( value instanceof Date ) { // TODO custom formattig
        const pad = (val: number) => val < 10 ? `0${val}` : `${val}`
        return `'${value.getFullYear()}-${pad(value.getMonth() + 1)}-${pad(value.getDate())} ${pad(value.getHours())}:${pad(value.getMinutes())}:${pad(value.getSeconds())}'`
    } else if ( !isNaN(Number(value)) ) {
        return String(Number(value))
    } else {
        const escaped = value.replace(/"/g, '\\"').replace(/'/g, '\\\'').replace(/`/g, '\\`')
        return `'${escaped}'`
    }
}