Introduction to the SqlUtil Module

The SqlUtil module provides a high level database-independent API for working with database objects and SQL.

To use this module, use "%requires SqlUtil" in your code.

All the public symbols in the module are defined in the SqlUtil namespace

Major sections of this documentation:

SQL Operations: working with database data (finding, updating, inserting, deleting, merging data, etc)
Schema Management: working schema definitions (creating, modifying, aligning tables, functions, types, triggers, etc)
DBA Management: special database administrator tools

The SqlUtil module provides generic functionality and a framework for SQL operations and schema management, and in order to use the SqlUtil module with a particular database, a driver-specific module has to be available as well.

Currently the following driver-specific modules are available:

FreetdsSqlUtil: for working with MS SQL Server and Sybase databases through the freetds driver
MysqlSqlUtil: for working with MySQL databases
OracleSqlUtil: for working with Oracle databases
PgsqlSqlUtil: for working with PostgreSQL databases
Sqlite3SqlUtil: for working with SQLite3 databases
OdbcFirebirdSqlUtil: for working with Firebird databases through the odbc driver

The underlying driver-specific module is automatically loaded and used when required; the classes provided in the SqlUtil module provide a generic API that uses the driver-specific implementations for the underlying driver-specific implementation.

Overview of Functionality

SqlUtil provides API support for the following:

Release Notes for the SqlUtil Module

SqlUtil v1.9

updated the logic used to find implementation-specific SqlUtil modules to use the new AbstractDatasource::getDriverRealName() APIs to better support the jdbc and odbc drivers (issue 4684)

SqlUtil v1.8.1

fixed a bug serializing and deserializing Table objects using the odbc driver (issue 4620)

SqlUtil v1.8

implemented support for generating queries based on generic expressions using the DataProvider module's generic expression support (issue 4538)

SqlUtil v1.7.5

respect "or nothing" Qore types when mapping to column types (issue 4400)
allow a VARCHAR column to be created without a size, assume a default size (issue 4399)
fixed a bug where objects created did not always contain the actual name used to create it (issue 4396)

SqlUtil v1.7.4

fixed upsert usage with unique constraints and indices when there is a non-matching PK (issue 4347)
added support for starting a transaction with Table and AbstractTable objects (issue 4346)

SqlUtil v1.7.3

added APIs to enable duplicate record creation errors to be detected (issue 4308)

SqlUtil v1.7.2

fixed a bug handling foreign constraints backed by indices on DBs that link indices and constraints automatically (issue 4269)

SqlUtil v1.7.1

fixed a bug where pseudocolumn references were used ambiguously in joins (issue 4214)

SqlUtil v1.7

added the SqlResultInfo hash and methods that return this hash instead of taking an optional output reference for SQL for use in languages that do not support lvalue references (issue 3934)

SqlUtil v1.6

added support for the DataProvider module (issue 3545)
implemented the op_in_select() function for Where Clauses (issue 3371)

SqlUtil v1.5.2

implemented the SqlUtil::AbstractDatabase::getPhysicalSize() functionality (issue 3385)

SqlUtil v1.5.1

fixed a bug that could lead to a deadlock when DML methods on the AbstractTable class are used with a datasource pool with connection contention (issue 3352)

SqlUtil v1.5

implemented the AbstractTable::getStatementNoExec() method (issue 2773)
added support for serializing and deserializing AbstractTable objects (issue 2663)
updated the module to use the AbstractSQLStatement class instead of the SQLStatement (issue 2326)
deprecated AbstractTable::getRowIterator() for AbstractTable::getStatement() (issue 2326)

SqlUtil v1.4.4

implemented the AbstractTable::getRowIteratorNoExec() method (issue 2773)

SqlUtil v1.4.3

implemented support for an optional error-handling method in SQL callbacks to allow SqlUtil to recover from error scenarios in schema creation/alignment (issue 2643)

SqlUtil v1.4.2

implemented support for literal values in column operators taking column arguments with cop_value() (issue 2555)

SqlUtil v1.4.1

implemented support for user custom column operators (issue 2314)

SqlUtil v1.4

fixed a bug in update and upsert statement generation when the given data does not have enough columns to use the unique index found, an error message is generated that contains all the columns names instead of just the column names required by the index (issue 1013)
implemented cop_trunc_date() operator (issue 2032)
updated to support complex types (issue 1724)
implemented analytic/window functions: new functions [issue 2202]
implemented analytic/window functions: cop_over full support including ORDER BY [issue 2203]

SqlUtil v1.3.4

fixed a bug in with column aliases that are reserved words (issue 2163)

SqlUtil v1.3.3

fixed a bug in the offset query hash argument in SQL operation methods (issue 1880)
implemented the alias select option to fix a bug that prohibited only columns from the main query to be selected when joins are used (issue 1909)

SqlUtil v1.3.2

fixed schema alignment skips column with name "driver" (issue 1684)
fixed sqlutil schema management: functional indexes are rejected without () in name (issue 1610)

SqlUtil v1.3.1

implemented the force option (i.e. cascade) for dropping code objects (issue 1314)
fixed the ignored character_semantics column option in schema alignmed (issue 1379)
implemented the SqlUtil::cop_length() column function (issue 1395)

SqlUtil v1.3

implemented insert option support (see AbstractTable::InsertOptions) with the "returning" clause in supported drivers
implemented support for late table resolution in joins to support executing SQL from serialized parameters
improved error messages for common errors such as join errors
implemented support for DBA actions (see DBA Management)
implemented support for driver-dependent pseudocolumns
implemented per-column support for the "desc" keyword in orderby expressions
implemented support for "or" logic in Where Clauses
implemented AbstractTable::getBulkUpsertClosure() to better support bulk SQL merge operations
implemented SqlUtil::wop_or() to allow for generating complex SQL strings with "or" and "and" expressions
implemented the SqlUtil::cop_cast() operator for converting [column] value into another datatype
implemented the SqlUtil::cop_sum() aggregate operator for returning sum of column values
implemented update operators SqlUtil::uop_plus(), SqlUtil::uop_minus(), SqlUtil::uop_multiply(), SqlUtil::uop_divide()
implemented support for numeric column arguments for the orderby and groupby options
implemented the SqlUtil::cop_coalesce() column operator functions for the "COALESCE" operator returning the first non-NULL value in a list of columns (issue 671)
column aliases (defined with SqlUtil::cop_as()) can now be used in the where hash argument and in join criteria
removed all APIs that handle implicit transactions; APIs must commit transactions explicitly (issue 533)
column operator functions can be used in where hashes (issue 529)
implemented SqlUtil::AbstractTable::bindEmptyStringsAsNull() (issue 776)
implemented the "omit_update" upsert option for asymmetrical upserts (updates only update a subset of the columns inserted) (issue 791)
implemented the SqlUtil::AbstractTable::UpsertUpdateOnly upsert option (issue 793)
implemented SqlUtil::cop_substr() and SqlUtil::uop_substr() operators (issue 801)
implemented SqlUtil::op_substr() where operator (issue 883)
fixed a bug with queries using a desc argument with the orderby query option with multiple sort columns; the "desc" string was added only to the last column but should have been added to all columns
fixed a bug where foreign key constraints with supporting indexes were not tracked and therefore schema alignment on DBs that automatically create indexes for foreign key constraints would fail
fixed a bug where driver-specific objects were not included when dropping a schema
fixed a bug in subquery handling where bind-by-value arguments from the subquery were lost
fixed a bug in the partition by/over operator where column names as given in the query argument hash were not properly recognized
fixed a bug in schema alignment; when aligning a schema and an index supporting a PK constraint is introduced in the new schema, the alignment would fail when a constraint is attempted to be disabled that doesn't exist
fixed a bug generating select statements for tables accessed through a synonym when used with join clauses; previously inconsistent schema prefixes could be used which could cause errors parsing the SQL statements generated
fixed a bug where the AbstractTable lock was held while executing SQL to determine the upsert strategy to use with UpsertAuto (issue 409)
fixed a bug where complex bind values as hashes (such as used by the pgsql and oracle drivers) were rejected by SqlUtil (issue 494) when updating
fixed a bug where wildcard columns in join tables were not working (issue 499)
fixed a bug in SqlUtil::op_in() where invalid SQL was generated with an argument of 0 (issue 500)
fixed a bug in SqlUtil::cop_value() where an exception was thrown with an argument of 0 (issue 511)
fixed SqlUtil::cop_count() operator to allow other operators as its argument (issue 517)
fixed bugs in SqlUtil::cop_seq() and SqlUtil::cop_seq_currval() (issue 624)
fixed a bug in SqlUtil::join_inner() where the cond argument was ignored (issue 645)
fixed SqlUtil::uop_lower() and SqlUtil::uop_upper() operators to allow nesting (issue 657)
fixed a bug where SqlUtil was generating invalid SQL for some DBs where a wilcard was used with explicit column names (issue 708)
fixed a bug where updating an index without any source constraints caused an invalid exception to be raised (issue 768)
fixed a bug in AbstractTable::update() with sequence operators (issue 942)

SqlUtil v1.2

added insert operator support; for example, for inserting with values from sequences
added new upsert constant maps: UpsertStrategyMap and UpsertStrategyDescriptionMap
added static SqlUtil::AbstractSqlUtilBase::getDatasourceDesc() method
added new AbstractTable::insertFromSelect*() variants taking Table arguments
added SqlUtil::AbstractTable::checkExistence() method
added support for the forupdate select option
fixed a bug in schema management where excessively verbose column aliases were used that caused errors when automatically updating columns with existing rows and new default values and non-null contraints with PostgreSQL databases
fixed a bug where select and row iterator operations could fail with certain select hash arguments without a "columns" entry but where column names were otherwise required

SqlUtil v1.1

implemented new upsert strategy SqlUtil::AbstractTable::UpsertInsertOnly
adding a default value to a column with a not null constraint and existing data populates existing null columns with the new default value automatically in schema alignment
fixed an infinitely recursive call in SqlUtil::AbstractTable::del()
fixed bugs generating "create table" and "align table" SQL with DBs where unique indexes automatically create unique constraints (ex: MySQL)

SqlUtil v1.0

initial release of the SqlUtil modules for schema management and SQL operations

Table of Contents