DbLangPg

DbLangPg is a class containing a collection of methods for generating/transforming SQL statements to be executed by a PostgreSQL server.

Methods of this class have no access to any database on their own, they deal with command texts only.

An instance of DbLangPg or its descendant is available as .lang property of each DbPoolPg and DbClientPg object.

Constants

DbLangPg.MAP_TYPE_ID_2_NAME is the reverse PostgreSQL type map loaded from pg-types.

Methods

toParamsSql (query)

An overloaded version of DbQuery.toParamsSql that honors query.options.limit and query.options.offset.

addLimitOffset ([...params, sql], limit, offset = 0)

For given [...params, sql] array, limit and offset values:

• pops sql,
• pushes limit and offset into params
• and repushes there appends sql with LIMIT ? OFFSET ? appended.

normalizeSQL ({sql})

Rewrites the sql property stripping off all comments, normalizing whitespace and replacing ?s with $1, $2 and so on.

? occurring right after ::jsonb are left intact (to let use JSONB operators).

genCopyFromSql

For a given DbRelation name, its columns list and a bag of options, returns the COPY t (c1, c2... cn) FROM STDIN WITH (...) statement source.

genRoutineArgShort

For a given DbRoutineParameter, returns its ${mode} ${type} to be interpolated into a DROP FUNCTION statement.

genRoutineArgFull

For a given DbRoutineParameter, returns its ${mode} ${qName} ${type} = ${default} to be interpolated into a CREATE FUNCTION statement.

genCopyFromSqlOptions

For a given bag of options, returns the WITH (...) clause to be used by genCopyFromSql. For an empty object, returns a zero length string.

genCopyFromSqlOption

For a given option name (in upper case) and the corresponding value, returns the string representation of value good to be used by genCopyFromSqlOptions.

genUpsertParamsSql

For a given DbRelation name, the data object representing a single record and the {key: [...]} option, returns an array or parameters followed by an INSERT INTO ... (f1, f2, ... fn) VALUES (?, ?,... ?) ON CONFLICT (${key}) DO UPDATE SET f1=EXCLUDED.f1,f2=EXCLUDED.f2,...fn=EXCLUDED.fn SQL string.

This is the base genInsertParamsSql method with the addition of ON CONFLICT clause to implement the upsert operation.

genAlter (asIs, toBe)

For a given pair of DbObjects, returns the corresponding ALTER statement rendering the database object described by asIs complying to toBe. Actually, is a wrapper around genAlterTable rejecting all other types.

genAlterTable (asIs, toBe)

For a given pair of dbTables describing the actual table structure and the required one, returns the corresponding ALTER TABLE statement.

genCreate (dbObject)

For a given DbObject, returns the corresponding CREATE statement presuming the prior absence of the object in a database. Actually, is a wrapper around genCreateTable rejecting all other types.

genComment (dbObjectOrColumn)

For a given DbObject or a DbColumn, returns the COMMENT ON... statement.

genReCreateTable (dbTable)

For a given dbTable, returns the corresponding CREATE TABLE statement.

genReCreate (dbObject)

For a given DbObject, returns the CREATE OR REPLACE statement or the like, asserting its existence in the database.

genReCreateView (dbView)

For a given DbView, returns the corresponding CREATE OR REPLACE statement.

genReCreateFunction (dbFunction)

For a given DbFunction, returns the corresponding CREATE OR REPLACE statement.

genReCreateProcedure (dbProcedure)

For a given DbProcedure, returns the corresponding CREATE OR REPLACE statement.

genReCreateAsMock (dbObject)

For a given DbObject, returns the CREATE OR UPDATE statement asserting the existence of its mock: the properly structured object that can be created in a totally independent way, i. e. in an empty database (without any source data tables, so, relying on constants only).

Actually, is a wrapper around genReCreateViewAsMock rejecting all other types.

genReCreateViewAsMock (dbView)

For a given DbView, returns the CREATE OR UPDATE statement for a SQL view returning one record of properly named an typed NULL values.

genReCreateFunctionAsMock (dbView)

For a given DbFunction, returns the [DROP FUNCTION, CREATE FUNCTION] statements to guarantee the existence of a function with zero dependencies, the required signature and the proper return type (it returns NULL). The explicit DROP is necessary for the case when the return type is changing.

genReCreateProcedureAsMock (dbView)

For a given DbProcedure, returns the CREATE OR REPLACE PROCEDURE statement to guarantee the existence of a procedure with zero dependencies and the required signature (doing nothing).

genDropTableForeignKeys (dbTable)

For a given table definition fetched from the actual database, returns the ALTER TABLE ... DROP CONSTRAINT for all its foreign keys. It's presumed that dbTable._fk_names must be a non-empty array of constraint names.

genCreateForeignKeys ()

This part of genDDL () generates ALTER TABLE ... ADD FOREIGN KEY ... NOT VALID for each DbReference pointing to a DbTable.

genDropTableColumns ()

This part of genDDL () generates ALTER TABLE ... DROP COLUMN ... CASCADE for each column found in asIs and having null definition in the model.

genDropTableTriggers (dbTable)

For a given table definition fetched from the actual database, returns the DROP FUNCTION IF EXISTS ... CASCADE for all its trigger procedures. It's presumed that dbTable._trg_proc_names must be a non-empty array of trigger procedure names.

genCreateTriggers ()

This part of genDDL () generates CREATE FUNCTION...; CREATE TRIGGER... for each DbTrigger of each DbTable described in model.

genUpsertData ()

This part of genDDL () generates INSERT INTO ... ON CONFLICT DO UPDATE ... for each DbTable described in model with a data section.

genCreateIndexes ()

This part of genDDL () invokes genCreateIndex () for each DbIndex described in model and yields its result. Each time, all indexes are processed this way but, due to the IF NOT EXIST option, only new ones are created. Unlike table columns, existing index definitions are not checked anyhow. So, changing model after first deployment can lead to inconsistencies in index structures.

genCreateIndex ()

For a given DbIndex, this method returns the CREATE INDEX ... IF NOT EXIST... statement text. The only options supported for now are:

• UNIQUE
• WHERE ...

genDDL ()

Implements the DDL script generation for DbMigrationPlan.

genSelectColumnsSql ()

Returns the SQL used by getStreamOfExistingTables. As of now, it is a getter for a constant string.

genPeekSql ()

For a given DbViewQueuePg, returns the SQL fetching complete records in queue.order.

getDbObjectClass ()

Overrides the superclass method for detecting DbViewQueuePg as DbView with the queue property.

isCopyStatement ()

For a given sql string, returns a Boolean indicating whether it starts with COPY / Copy / copy (optionally preceded by some space) or not.