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.5.2
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
SqlUtil v1.4.4
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
SqlUtil v1.3
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