SqlUtil Module

Table of Contents

• Introduction to the SqlUtil Module
• Overview of Functionality
• Release Notes
  • SqlUtil v1.2
  • SqlUtil v1.1
  • SqlUtil v1.0

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)

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:

• MysqlSqlUtil: for working with MySQL databases
• OracleSqlUtil: for working with Oracle databases
• PgsqlSqlUtil: for working with PostgreSQL databases

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:

• SQL Operations
  • Retrieving Data from the Database
  • Inserting Data into the Database
  • Updating Data
  • Deleting Data
  • Upserting or Merging Data
• Schema Management
  • Creating New Objects
  • Retrieving Objects from the Database
  • Listing Objects
  • Working With Interdependent Tables
  • Aligning Schemas With a Template
  • Table Management

Release Notes

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 Table::insertFromSelect*() variants taking Table arguments
• added SqlUtil::Table::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
• 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

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::Table::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