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)

Returns the sql string with all comments stripped off and ?s replaced with $1, $2 and so on.

? occurring right after ::jsonb are not replaced (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.

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.

genReCreateTable (dbTable)

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

genReCreate (dbObject)

For a given DbObject, returns the CREATE OR UPDATE statement asserting its existence in the database. Actually, is a wrapper around genReCreateView rejecting all other types.

genReCreateView (dbView)

For a given DbView, returns the CREATE OR UPDATE statement for that SQL view.

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.

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.

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.