Skip to content

DbLang

Jump to bottom
do- edited this page Apr 18, 2025 · 87 revisions

DbLang is the common ancestor for classes representing different dialects of SQL and, possibly, other database programming languages.

The base implementation mostly follows ANSI SQL.

An instance of DbLang or its descendant must be available as the .lang property of each DbPool and each DbModel.

Static Properties

Name Type Description
TP_SMALLINT DbTypeArithmeticInt 2-byte signed interger
TP_INT DbTypeArithmeticInt 4-byte signed interger
TP_BIGINT DbTypeArithmeticInt 8-byte signed interger
TP_REAL DbTypeArithmeticFloat floating point REAL
TP_NUMERIC DbTypeArithmeticFixed the base fixed point arithmetic type (also used for DECIMAL)
TP_CHAR DbTypeCharacter string type named CHAR
TP_VARCHAR DbTypeCharacter string type named VARCHAR

Instance Properties

Name Type Description
model DbModel The related database model instance

Methods

compareColumns

For given asIs and toBe DbColumns, returns an array of strings meaning the names of their differing properties, specifically:

  • nullable if .nullable values are not strictly equal;
  • default if isEqualColumnDefault () returned false;
  • typeDim if isAdequateColumnTypeDim () returned false. So, a non-empty result means that asIs requires some ALTER statement(s) to be applied to comply with the toBe definition.

createDbObject

For a given bag of options, detects the class to be instantiated with getDbObjectClass, calls its constructor with the same options and injects itself with setLang. Returns the instance created.

getDbObjectClass

Implements the duck typing, like: "if it has columns and an SQL query source, it's a view". For a given plain Object o, returns a DbObject class descendant to be instantiated with o. In the base version:

  • if o has the columns property, returns a DbRelation descendant:
  • if o has the body property, returns a DbRoutine descendant:
  • otherwise, throws an Error.

getDbObjectClassesToDiscover

Returns the list of DbObject subclasses that DbMigrationPlan must discover with its loadStructure method. In the default implementation, returns [DbTable].

getDbObjectName

For a given object DbObject o, returns [quoteName (schemaName) + '.' +] quoteName (localName).

getDbColumnTypeDim

For a given object DbColumn col, returns ${col.type}[(${col.size}[,${col.scale}])].

getRequiredMutation

For a given pair of DbObjects asIs and toBe, returns:

  • 'alter' if asIs can be transformed to toBe with some ALTER... statements;
  • 'migrate' if asIs can be transformed to toBe by creating a copy of asIs / transferring data / dropping asIs / renaming the copy;
  • null if no action is needed for such transformation.

getTypeDefinition

For a given string type representing some name of data type acceptable by the database product, must return a DbType instance representing the type with that name: such one of DbLang.TP_... static properties.

isAdequateColumnType

For given asIs and toBe DbTypes, returns a Boolean showing whether asIs is good to be used in place of toBe without any modification.

The base implementation only allows DbTypeArithmeticInt with more bytes, i. e. BIGINT in place of INT.

isAdequateColumnTypeDim

For given asIs and toBe DbColumns, returns a Boolean showing whether asIs is good to be used in place of toBe without any modification.

The base implementation requires:

  • types to be the same or compatible in sense of isAdequateColumnType;
  • size and scale to be same or greater, where appropriate.

isEqualColumnDefault

For given asIs and toBe strings, returns a Boolean showing whether they mean the same expression used in a column DEFAULT clause.

The base implementation just compares the strings for strict equity.

isUnaryOperator

For a given String, returns a Boolean meaning whether the incoming string is a unary operator, like IS NULL or IS NOT NULL.

genInsertParamsSql

For a given DbRelation name, and the data object representing a single record, returns an array or parameters followed by an INSERT INTO ... (f1, f2, ... fn) VALUES (?, ?,... ?) SQL string.

The field list is constructed based on relation's columns mentioned in data. All data properties not in columns are ignored. Properties with undefined values are ignored (but nulls are kept).

If the field list is empty (no known column is mentioned), DEFAULT VALUES is used instead of (...) VALUES (...) clause.

genUpdateParamsSql

For a given DbRelation name, and the data object representing a single record, returns an array or parameters followed by an UPDATE ... SET f1=?, f2=?,... fn=? WHERE k1=? AND k2=? AND... kn=? SQL string.

The WHERE clause is constructed based on the primary key (the relation's pk option). For each pk element, there must be defined a non-null data property; otherwise, an error is thrown.

The SET clause is constructed based on all relation's columns not in pk. All data properties not in columns are ignored. Properties with undefined values are ignored. For not nullable columns, explicit null values are mapped to the DEFAULT keyword instead of a ? placeholder.

If the SET clause is empty (so the data contains only the primary key and, maybe, some properties unknown to the model), the returned value is null, meaning there is nothing to UPDATE but this is not an error.

genComparisonRightPart

For a given DbQueryTableColumnComparison filter, returns a string to be appended to filter.sql right to the operator: like, '?' for simple binary operators, '? AND ?' for BETWEEN, (?,?...?) for IN etc.

More specifically:

  • by the time of calling genComparisonRightPart, filter.sql is the fully qualified name of the field;
  • if genComparisonRightPart returns a string, is is appended filter.sql along with the op;
  • if it returns null, nothing is appended to filter.sql, but genComparisonRightPart may have mutated it as a side effect.

In the default implementation, for a non-standard ILIKE operator, genComparisonRightPart sets filter.sql to UPPER(${table}.${columns}) LIKE UPPER(?).

getTriggerName

For a given DbTable table and an integer n, returns a string to be used as the nth table's trigger name.

getIndexName

For a given DbRelation relation and a DbIndex index, returns a string to be used as the index localName, unless set explicitly.

genDDL ()

This generator is called internally by DbMigrationPlan to produce the actual SQL (DDL) sequence.

quoteName

Quotes the given name according to ANSI SQL-99 standard: with double quotes, escaping " as "".

quoteStringLiteral

Quotes the given string according to ANSI SQL-99 standard: with single quotes, escaping ' as ''.

quoteLiteral

Returns an ANSI SQL-99 literal representing the given value based on its type:

  • NULL for null and undefined;
  • TRUE or FALSE for a Boolean;
  • .toString () for a Number or BigInt;
  • .quoteStringLiteral () for a String.

Otherwise (i. e. for any non-null Object, including Dates and Arrays), an error is thrown.

genColumnDefinitionParts

For a given DbColumn, this generator yields the sequence of strings to be concatenated by ' ' as the genColumnDefinition result.

genColumnDataType

For a given DbColumn, returns the corresponding data type part of the definition. In the base implementation, returns typeDef.

genColumnConstraints

For a given DbColumn, this generator yields the tail of genColumnDefinitionParts following the type and default value. In the base implementation, yields ' NOT NULL', if appropriate.

genColumnDefinition

For a given DbColumn, returns the corresponding definition.

genCreateTempTable

For a given DbTable and options object, returns the corresponding CREATE TEMPORARY TABLE ... statement. If options.onlyIfMissing is set, IF NOT EXISTS is added.

genPeekSql ()

For a given DbView hosting a DbQueue, returns the SQL fetching complete records in queue.order.

appendFilter

Given a given String sql, an Array of params, and a {sql, params} object called filter, this method pushes all filter.params into params and returns sql + filter.sql.

toParamsSql

For a given DbQuery, returns an Array of parameters with the SQL text appended to the end of it. Does the DbQuery.toParamsSql's job. Not to be invoked directly.

wrapViewSql

For a given DbView, replaces its sql property value with SELECT ${columns} FROM (${sql}) t. Called by the setLang when wrap option is set.

normalizeSQL

For a given DbCall, normalizes its sql property. In the base implementation, only normalizes the whitespace for logging purposes. Depending on the DB product, may also interpolate params (if the driver doesn't support proper binding), strip comments etc.

toQueryColumn

For the given DbQuery, returns a new DbQueryColumn instance bound to it and representing this column.

setLang

With the given DbLang lang, calculates all properties specific to this language/dialect.

In the base implementation:

Column Definition Language

Here, a tiny DSL is implemented to build DbColumn objects from formatted strings.

column    ::= ( physical | reference ) nullable? ("=" default)? range? re? extra? ("//" comment )?

physical  ::= type dimension?
dimension ::= "(" size ("," scale)? ")"

nullable  ::= "?" | "!"

range     ::= "[" min? ".." max? "]"
re        ::= "/" pattern "/"
extra     ::= "{" ...properties "}"

The extra part may contain diverse DBMS vendor specific column options. Its format is loose JSON, or just js object literal.

Where:

  • for reference, see DbReference;
  • size and scale, if present, must be positive integer decimal numbers not starting with 0;
  • when the range is specified, min or max may be omitted, but not both
  • default, min and max literals are not delimited, so they must not contain any special characters used by delimiters by the DSL itself (e. g. max can't contain '}', but no ordinal type allow such constants, so it's not really a restriction). Round braces (and so, function calls, compound expressions etc.) are allowed in default.
  • the nullable availability and meaning depends on default:
nullable deault example The column is...
int NULLABLE
defined int = 0 NOT NULL
! int! NOT NULL
? defined int ?= 0 NULLABLE
? int? n/a
! defined int!=0 n/a

Examples

columns: {

// scalar fields

  dt_birth      : 'date!                                // date of birth', // `!` means NOT NULL
  dt_death      : 'date                                 // date of death', // this one is NULLABLE

  ts_created    : 'timestamp      = now()               // when created',  // NOT NULL implied by DEFAULT
  score         : 'int           ?= 1000                // social credit', // explicitly NULLABLE with `?`

  f_name        : 'varchar (50)       /^[A-Z][a-z\-]*$/ // family name',   // RegExp example
  salary        : 'decimal (7,2) ?= 0 [0..]             // salary',        // may be NULL, but never negative

  ultimate      : 'int=99 [9..999] /^9/ {gz: 9, ttl: 'DAY'} // ...',       // just an illustration

// references

  id_status     : '(tb_status)    = 'W'                 // status',        // `tb_status` has a CHAR(1) PK
  id_parent     : '(-tb_same_entity)                    // parent record', // ON DELETE CASCADE
  id_task       : '(~tb_volatile_tasks)                 // in progress',   // ON DELETE SET NULL

}

parseColumn

Parses the string argument into a DbColumn constructor argument.

parseColumnComment

For a given options object, cuts options.comment off options.src, if found.

parseColumnPattern

For a given options object, cuts options.pattern off options.src, if found.

parseColumnRange

For a given options object, cuts options.min and options.max off options.src, if found.

parseColumnDefault

For a given options object, cuts options.default off options.src, if found.

parseColumnNullable

For a given options object, sets the nullable and type properties based on options.src.

parseColumnDimension

For a given options object, parses options.type into options.type per se, options.size and options.scale.

Clone this wiki locally