Qore SqlUtil Module Reference  1.3.2
SqlUtil Module

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:

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.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

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