Introduction to Schema Management

Most schema management is performed by using the Database class (which is a wrapper for the AbstractDatabase class).

The most complex object is the Table, which has its own section.

One of the primary goals of schema management in SqlUtil is to facilitate automatic schema management from a database-independent schema description. The idea is that a schema can be described in a generic way and then SqlUtil can use the generic schema description to align the database with the description. Aligning in SqlUtil is performed as follows:

if the object does not exist, then it is created
if the object exists, but does not match the description, it is either:
- dropped and recreated, or
- modified in place
if the object exists and matches, then nothing is done

Unfortuntely, it's not currently possible to describe all database objects in a generic way (and it probably never will be); in particular, functions, triggers, and other objects based on procedural code or database-specific functionality have to be defined in their database-specific formats.

The following is a table describing the alignment support for various objects supported by SqlUtil; for database-specific objects, see the database-specific SqlUtil module:

Database Object	Alignment Type
Columns	aligned in place
Constraints	dropped and recreated
Functions and Procedures	dropped and recreated
Indexes	dropped and recreated
Sequences	dropped and recreated
Triggers	dropped and recreated
Tables	aligned in place

Note

For objects that are dropped and recreated, renaming the object is generally supported if the underlying database server supports renaming.
Schemas (and other objects) can also be dropped based on a generic description (or descriptions derived from the database); see SqlUtil::Database::getDropSchemaSql()

Creating New Objects

New objects are generally creating using appropriate methods from the Database class, such as the following:

Tables can also be created by instantiating a Table object (see SqlUtil::Table::constructor()).

Each database driver may provide additional objects (such as types or materialized views, etc); see driver-specific documentation for more information.

Retrieving Objects from the Database

Existing objects can be retrieved from the database using appropriate methods from the Database class, such as the following:

Each database driver may provide additional objects (such as types or materialized views, etc); see driver-specific documentation for more information.

Listing Objects

Listing database objects can be performed using appropriate methods from the Database class, such as the following:

Alternatively, iterators for lists of objects can be acquired with the following methods:

Working With Interdependent Tables

The SqlUtil::Tables class manages a group of tables and the foreign key dependencies between the tables. This class is useful when managing an entire schema, particularly in the context of manual changes to a schema, such as a schema upgrade or downgrade for complex scenarios that are beyond the capabilities of the automatic schema and table alignment code.

Below you can find some examples of what the Tables class can do.

Cache All Tables from a Schema: To load all tables in a particular schema into an object of class Tables:
my Tables $tables($ds);

Drop All Foreign Constraints to a Table: The following is an example of how all foreign constraints to a particular table can be dropped:
my *list $l = $tables.getDropAllForeignConstraintsOnTableSql("table_name", ("sql_callback_executed": True));

{

on_success $ds.commit();

on_error $ds.rollback();

map $ds.execRaw($1), $l;

}

This can be useful when performing a schema change and a table will be dropped, for example.

Note: by setting the sql_callback_executed option to True, the affected constraints and linked unique constraints are also updated in the cached table objects, see SQL CallBack Executed Flag for more information

Drop a Constraint If It Exists: The following code provides an example of how to unconditionally drop a constraint from a table and update the internal links between unique constraints and foreign constraints (if a foreign constraint is dropped):
my *string $sql = $tables.getDropConstraintIfExistsSql("table_name", "fk_table_name_other_table", ("sql_callback_executed": True));

if ($sql) {

on_success $ds.commit();

on_error $ds.rollback();

$ds.execRaw($sql);

}

Note: by setting the sql_callback_executed option to True, the affected constraints and linked unique constraints are also updated in the cached table objects, see SQL CallBack Executed Flag for more information

Rename a Table: The following code gives an example of renaming a table and updating all foreign constraint and unique key links in impacted tables if the source table exists and the target name does not exist:
my *string $sql = $tables.getRenameTableIfExistsSql("old_table_name", "new_table_name", ("sql_callback_executed": True));

if ($sql) {

on_success $ds.commit();

on_error $ds.rollback();

$ds.execRaw($sql);

}

Note: by setting the sql_callback_executed option to True, the affected constraints and linked unique constraints are also updated in the cached table objects, see SQL CallBack Executed Flag for more information

Aligning Schemas With a Template

A schema template can be created any way required (ie by hand, or by retrieving the description from another database), but typically they will be created from a schema description hash.

The following code provides an example of creating a schema template and using it to align the schema in the database with the template using callbacks:

my int $change_count;
my code $info_callback = sub (string $str, int ac, string type, string name, *string table, *string new_name, *string info) {
    if (ac != AbstractDatabase::AC_NotFound && ac != AbstractDatabase::AC_Unchanged)
        ++$change_count;
    if ($verbose)
        printf("*** %s\n", $str);
    else {
        print(AbstractDatabase::ActionLetterMap{ac});
        flush();
    }
};
my code $sql_callback = sub (string $str) {
    if ($verbose > 1)
        printf("%s\n", $str);
    $ds.execRaw($str);
};
my hash $callback_opts = ("info_callback": $info_callback, "sql_callback": $sql_callback, "sql_callback_executed": True);
my Database $db($ds);
my Tables $table_cache();
$db.getAlignSql($schema_template, $callback_opts, $table_cache);

Schema Description Hash

The schema description hash has the following structure:

tables: a hash of table description hashes keyed by table name; each entry describes a table
sequences: a hash of sequence description hashes keyed by sequence name; each entry describes a sequence
functions: a hash of function description hashes keyed by function name; each entry describes a function
procedures: a hash of procedure description hashes keyed by procedure name; each entry describes a procedure
driver: this key can optionally contain a hash keyed by driver name which contains a hash of values that will be added to the schema description hash before processing; this way a schema description hash can contain all the information required for the schema including driver-specific options; any driver-specific options will overwrite values in the top level of the hash if there are duplicate hash keys

Each database driver may provide additional objects (such as types or materialized views, packages, etc); see driver-specific documentation for more information.

The following is an example of a schema description hash:

const Options = (
    "driver": (
        "oracle": (
            "compute_statistics": True,
            "character_semantics": True,
        ),
    ),
    );
const T_Customers = (
    "columns": (
        "id": (
            "qore_type": Type::Number, 
            "size": 14,
            "notnull": True,
            # this column is normally populated from a sequence by a trigger, but mysql
            # enforces not null constraints before "before insert" triggers are fired, so
            # we can't use our emulated sequences on mysql with a not null constraint on this
            # column, also since this column is a part of the primary key for this table,
            # we can't leave it nullable, so we use auto_increment
            "driver": ("mysql": ("native_type": "bigint", "unsigned": True, "auto_increment": True, "size": NOTHING)),
        ),
        "family_name": (
            "qore_type": Type::String,
            "size": 120,
            "notnull": True,
        ),
        "first_names": (
            "qore_type": Type::String,
            "size": 240,
            "notnull": True,
        ),
        "created": (
            "qore_type": Type::Date,
            "notnull": True,
            # this column is populated by a trigger, but mysql enforces not null
            # constraints before "before insert" triggers are fired, so for mysql only
            # this column must be nullable
            "driver": ("mysql": ("notnull": False)),
        ),
        "modified": (
            "qore_type": Type::Date,
        ),
    ),
    "primary_key": ("name": "pk_customers", "columns": "id"),
    "indexes": (
        "sk_customers_name": ("columns": "family_name"),
    ),
    "driver": (
        "pgsql": (
            "functions": (
                "trig_customers()": "returns trigger language plpgsql as $function$ begin if (tg_op = 'INSERT') then if new.created is null then select current_timestamp into new.created;
    end if; end if; if new.modified is null then select current_timestamp into new.modified; end if; return new; end; $function$", #",
            ),
        ),
    ),
    "triggers": (
        "driver": (
            "oracle": (
                "trig_customers": "BEFORE INSERT OR UPDATE ON customers REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW begin if inserting then if :new.id is null then select seq_customers.nextval into :new.id from dual; end if; if :new.created is null then :new.created := sysdate; end if; end if; if :new.modified is null or :new.modified = :old.modified then :new.modified := sysdate; end if; end;",
            ),
            "pgsql": (
                "trig_customers": "before insert or update on customers for each row execute procedure trig_customers()",
            ),
            "mysql": (
                "trig_customers_insert": "before insert on customers for each row begin if new.created is null then set new.created = now(); end if; if new.modified is null then set new.modified = now(); end if; end",
                "trig_customers_update": "before update on customers for each row begin if new.modified is null or new.modified = old.modified then set new.modified = now(); end if; end",
            ),
        ),
    ),
);
const SequenceList = (
    "seq_customers": hash(),
    );
const Sequences = (
    "driver": (
        "oracle": SequenceList,
        "pgsql": SequenceList,
    ),
    );
const Tables = (
    "customers": T_Customers,
    );
const Schema = (
    "sequences": Sequences,
    "tables": Tables,
    );

Table Management

The main class to be used for table management is Table (which is a wrapper for AbstractTable). This class can be used both for building a table from scratch or for retrieving the structure of a table from the database.

Creating New Tables

Tables can be created manually, by creating a Table object and then populating the table with columns, indexes, etc, and then calling SqlUtil::Table::create() for example. The following methods can be used for adding new attributes to a table:

Note that if the table is known to be in the database already (for example, if the original table info was read from the database), then calling one of the above methods will also effect the change in the database immediately.

Alternatively, the following methods can be used to retrieve the SQL that can be executed for the particular database driver that the SqlUtil::Table object is based on:

Otherwise a table can be built from a table description hash with one of the following methods:

Table description hashes are also used when aligning tables to a template, which can be used to automate schema upgrades; see SqlUtil::Table::getAlignSql(), SqlUtil::Database::getAlignSql(), and table description hash for more information.

Retrieving Table Information

The following methods retrieve table information from the database:

Table::cache(): this method reads in the entire table configuration immediately
Table::describe(): this method returns an object of class SqlUtil::Columns describing the table's columns
Table::getPrimaryKey(): this method returns an object of class SqlUtil::AbstractPrimaryKey describing the table's primary key if it has one
Table::getIndexes(): this method returns an object of class SqlUtil::Indexes describing the table's indexes
Table::getForeignConstraints(): this method returns an object of class SqlUtil::ForeignConstraints describing the table's foreign constraints
Table::getConstraints(): this method returns an object of class SqlUtil::Constraints describing the table's non-foreign constraints
Table::getTriggers(): this method returns an object of class SqlUtil::Triggers describing the table's triggers

The SqlUtil::Table::cache() method can be used to retrieve all table information from the database immediately as in the following example:

my Table $table($ds, "table_name");
$table.cache();
my string $sql = $table.getCreateSqlString();

Use the following code to test if a table exists:

my Table $table($ds, "table_name");
if ($table.checkExistence())
    printf("table exists\n");

Or the following:

Database $db($ds);

*AbstractTable $table = $db.getTable("table_name");

Aligning Tables With a Template

Template tables can be created any way required (ie by hand, or by retrieving the description from another database), but typically they will be created from a table description hash.

The following code provides an example of creating a template table and using it to align a table in the database with the template using callbacks:

my int $change_count;
my code $info_callback = sub (string $str, int ac, string type, string name, *string table, *string new_name, *string info) {
    if (ac != AbstractDatabase::AC_NotFound && ac != AbstractDatabase::AC_Unchanged)
        ++$change_count;
    if ($verbose)
        printf("*** %s\n", $str);
    else {
        print(AbstractDatabase::ActionLetterMap{ac});
        flush();
    }
};
my code $sql_callback = sub (string $str) {
    if ($verbose > 1)
        printf("%s\n", $str);
    $ds.execRaw($str);
};
my hash $callback_opts = ("info_callback": $info_callback, "sql_callback": $sql_callback, "sql_callback_executed": True);
my Table $template_table($ds, $table_desc_hash, "table_name");
my Table $db_table($ds, "table_name");
$db_table.getAlignSql($template_table, $callback_opts);

Table Description Hash

Table description hashes have the following structure:

columns: (required) a hash of column information keyed by column name; the values are column description hashes
primary_key: (optional) a primary key description hash describing the primary key for the table
indexes: (optional) a hash of index information keyed by index name; the values are index description hashes
triggers: (optional) a hash of trigger information keyed by trigger name; the values are the trigger source code; since triggers are driver-dependent, a driver-independent table description would include trigger hashes under the drivers key and the driver key name under that; see below for an example
foreign_constraints: (optional) a hash of foreign constraint information keyed by constraint name; the values are foreign constraint hashes
unique_constraints: (optional) a hash of unique constraint information keyed by constraint name; the values are unique constraint hashes
driver: this key can optionally contain a hash keyed by driver name which contains a hash of values that will be added to the table description hash before processing; this way a table description hash can contain all the information required for the table including driver-specific options; any driver-specific options will overwrite values in the top level of the hash if there are duplicate hash keys, see below for an example

Here is an example of a table description hash:

my hash $table_desc = (
    "columns": (
        "domain": (
            "qore_type": Type::String, 
            "size": 240,
            "notnull": True,
        ),
        "keyname": (
            "qore_type": Type::String,
            "size": 240,
            "notnull": True,
        ),
        "value": (
            "qore_type": Type::String,
            "size": 4000,
        ),
        "created": (
            "qore_type": Type::Date,
            "notnull": True,
            "driver": (
                "oracle": (
                    "native_type": "date",
                ),
            ),
        ),
        "modified": (
            "qore_type": Type::Date,
            "driver": (
                "oracle": (
                    "native_type": "date",
                ),
            ),
        ),
    ),
    "primary_key": ("name": "pk_system_properties", "columns": ("domain", "keyname")),
    "indexes": (
        "sk_system_properties_domain": ("columns": "domain"),
        "driver": (
            "oracle": (
                "pk_system_properties": ("columns": ("domain", "keyname"), "unique": True),
            ),
        ),
    ),
    "driver": (
        "pgsql": (
            "functions": (
                "trig_system_properties()": "returns trigger language plpgsql as $function$ begin if (tg_op = 'INSERT') then if new.created is null then select current_timestamp into new.created; end if; end if; if new.modified is null then select current_timestamp into new.modified; end if; return new; end; $function$",
            ),
        ),
    ),
    "triggers": (
        "driver": (
            "oracle": (
                "trig_system_properties": "BEFORE INSERT OR UPDATE ON SYSTEM_PROPERTIES REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW begin if inserting then if :new.created is null then :new.created := sysdate; end if; end if; if :new.modified is null or :new.modified = :old.modified then :new.modified := sysdate; end if; end;",
            ),
            "pgsql": (
                    "trig_system_properties": "before insert or update on system_properties for each row execute procedure trig_system_properties()",
            ),
            "mysql": (
            ),
        ),
    ),
);

Note

some databases will automatically create indexes for you when you create primary keys and unique constraints; some do not (ex: oracle). In the above example, an index is created explicitly for the primary key constraint for oracle only.
the above example includes an additional driver-specific key for the "pgsql" driver: "functions" which is used by the PgsqlSqlUtil module to create trigger functions used by the table's trigger.

Column Description Hash

Column description hashes have the following structure:

qore_type: a qore type string that will be converted to a native DB type with some default conversion
native_type: the native database column type; if both native_type and qore_type are given then native_type is used
size: for data types requiring a size component, the size; for numeric columns this represents the precision for example
scale: for numeric data types, this value gives the scale
default_value: the default value for the column
comment: an optional comment for the column
notnull: if the column should have a "not null" constraint on it; if missing the default value is False
driver: this key can optionally contain a hash keyed by driver name which contains a hash of values that will be added to the column description hash before processing; this way a column description hash can contain all the information required for the column including driver-specific options; any driver-specific options will overwrite values in the top level of the hash if there are duplicate hash keys, see below for an example

Note that the above structure is an extension of the fields in SqlUtil::AbstractTable::ColumnDescOptions, adding notnull and driver keys for additional information to create the column in the table.

Here is an example of a column description hash:

my hash $date_col_desc = (
    "qore_type": Type::Date,
    "notnull": True,
    "driver": (
        "oracle": (
            "native_type": "date",
        ),
    ),
);

The above hash describes a column that will be have TIMESTAMP type on most databases, but DATE on Oracle.

Primary Key Description Hash

The primary key description hash has the following keys:

name: (required) the name of the primary key
columns: (required) a single column name or a list of column names making up the primary key

Here is an example of a primary key description hash:

my hash $pk_desc = (
    "name": "pk_queue",
    "columns": "queueid",
);

Foreign Constraint Description Hash

The foreign constraint description hash has the following keys:

columns: (required) a single column name or a list of column names in the current table making up the foreign constraint
table: (required) a string giving the name of the table the foreign constraint is on
target_columns: (optional) this key is only necessary if the columns in the foreign table have different names than in the current table; if so, it must be assigned to a single column name or a list of column names in the foreign table; if this key is present then the same number of columns must appear in each key

The following is an example of a foreign key description hash:

my hash $fk_desc = (
    "columns": "queueid",
    "tables": "queues",
);

Unique Constraint Description Hash

The unique constraint description hash has the following key:

columns: (required) a single column name or a list of column names in the current table making up the unique constraint

The following is an example of a unique constraint description hash:

my hash $uk_desc = (
    "columns": ("type", "name"),
);

Index Description Hash

Index description hashes have the following structure:

columns: (required) a single column or a list of column names making up the index
unique: a boolean value indicating if the index is unique or not, if missing, then the index is assumed to e not unique

The following is an example of an index description hash:

my hash $ix_desc = (
    "columns": ("type", "name"),
    "unique": True,
);

Callbacks

Most of the SqlUtil methods that return SQL strings also accept an option hash where callbacks an be set. Callbacks can be used to display detailed information about long-running operations, such as schema or data alignment for complex schemas or large data sets, or as a generic framework for executing and logging SQL operations.

SQL Info CallBack Closure or Call Ceference

The "info_callback" closure or call reference is called with information about the current SQL operation and can be specified as in the following example:

my int $change_count;
my code $info_callback = sub (string $str, int ac, string type, string name, *string table, *string new_name, *string info) {
    if (ac != AbstractDatabase::AC_NotFound && ac != AbstractDatabase::AC_Unchanged)
        ++$change_count;
    if ($verbose)
        printf("*** %s\n", $str);
    else {
        print(AbstractDatabase::ActionLetterMap{ac});
        flush();
    }
};
my hash $callback_opts = ("info_callback": $info_callback);
$db.getAlignSql($schema, $callback_opts);

SQL Raw/DDL CallBack Closure or Call Ceference

The "sql_callback" closure or call reference is called with an SQL string suitable for raw execution for each SQL command that is returned or generated for a particular operation and can be specified as in the following example:

my code $sql_callback = sub (string $str) {
    $ds.execRaw($str);
    if ($verbose > 1)
        printf("%s\n", $str);
};
my hash $callback_opts = ("sql_callback": $sql_callback, "sql_callback_executed": True);
$db.getAlignSql($schema, $callback_opts);

Note: this callback is used for DDL execution and does not use bound arguments; for SQL data operations, the SQL Operation Callback Closure or Call Reference is used instead

SQL CallBack Executed Flag

If the "sql_callback_executed" flag is set to True then the called method knows that the changes are made in the database and the internal representation of the affected object(s) is also updated accordingly. Here is an example:

my code $sql_callback = sub (string $str) {
    if ($verbose > 1)
        printf("%s\n", $str);
    $ds.execRaw($str);
};
my hash $callback_opts = ("sql_callback": $sql_callback, "sql_callback_executed": True);
$db.getAlignSql($schema, $callback_opts);

SQL Operation Callback Closure or Call Reference

The "sqlarg_callback" closure or call reference is called with an SQL string and arguments for each SQL command that is executed during SQL data operations and can be specified as in the following example:

my code $sqlarg_callback = sub (string $str, *list $args) {
    if ($verbose > 1)
        printf("SQL: %s\nargs: %y", $str, $args);
};
$table.insertFromIterator($i, $sqlarg_callback);

Note: this callback is used for data operations and is called with bound arguments; for DLL operations, see SQL Raw/DDL CallBack Closure or Call Ceference

Upsert Info Callback

The "info_callback" upsert option key can be assigned to a closure or call reference that is called whenever an row result is available; this callback takes the following arguments:

string table_name: the name of the table
hash row: the row affected
int result: the upsert result code (see Upsert Result Codes) for possible values

Here is an example of an upsert callback:

my code $upsert_callback = sub (string $table_name, hash $row, int $result) {
    # verbosity threshold
    my int $t = 0;
    if ($result != AbstractTable::UR_Unchanged)
        ++$change_count;
    else
        $t = 1;
    if ($opt.verbose > t) {
        if ($dot_count) {
            print("\n");
            $dot_count = 0;
        }
        printf("%s reference data %s: %y: %s\n", t ? "+++" : "***", $table_name, $row, AbstractTable::UpsertResultMap{$result}); 
    }
    else {
        ++$dot_count;
        print(AbstractTable::UpsertResultLetterMap{$result});
        flush();
    }
};
my hash $sh = $table.upsertFromIterator($i, $upsert_strategy, False, ("info_callback": $upsert_callback));

Insert Info Callback

This option is identical to Upsert Info Callback, except the result argument is always SqlUtil::AbstractTable::UR_Inserted.

Table of Contents