documentation

Basic concepts

Alterscript is list of SQL commands and metadata used to configure how are these commands executed, stored in single file. Despite its name, it is not really script; there are no control structures and metadata possibilities are very limited.

Applying alterscript is process of executing commands contained in single alterscript on single specific database. Each alterscript can be applied to many databases, and even multiple times to single database (that might be desirable or not, see details in incremental mode section).

Database configuration describes single database and how to correctly initialize connection used to execute alterscript SQL commands. These data contains, but are not limited to, JDBC configuration items (host, port, database name, user, password).

Note that applyalter provides separation of application-specific (alterscript) and database-instance-specific (configuration) data. Single alterscript can be applied to several databases with different names, in some cases even in different schemas. That is very valuable for local development, experiments and prototyping; it can be even used to create separated staging environment (for example by having databases with suffixes _devel and _test).

Database type is used to identify these special cases: several one are essentially clones of the same database, they have different names but the same type.

Configuration

Database configuration is stored in xml file, referenced by first (mandatory) argument on command line. Root element is always db and contains one or more database elements (one for each database); these elements are named by database engine and their content might vary.

Example of simple database configuration file:

<?xml version="1.0" encoding="UTF-8"?>
<db>
  <pginstance>
    <id>example</id>
    <host>localhost</host>
    <db>example1</db>
    <user>podlesh</user>
    <pass>xxx</pass>
  </pginstance>
</db>

Common database connection elements

• id = unique identifier of this database; used mostly in logging output
• type = (optional) database type, compared with instance element in alterscripts; when not present, id is used instead
• host = hostname to connect to (part of the JDBC URL); required by most engines
• port = (optional) TCP port to connect to (part of the JDBC URL)
• db = database name (part of the JDBC URL); required by all engines
• user = username (part of the JDCB API); required by most engines
• pass = password (part of the JDBC API), stored as plaintext; usually needed, unless database engine uses some other way to authorize connections

Postgresql

Element name pginstance , engine name Postgresql

Postgresql configuration supports all standard features and also two additional: role changing and ~/.pgpass file.

Optional element role can contain name of the postgresql role that is used to execute all SQL commands as, making it automatic owner of all created objects. This feature is implemented by calling SET ROLE command immediately after connecting.

Element pass is optional; when missing or empty, password is loaded from standard $HOME/.pgpass file. See postgresql documentation for description of this file and its content.

DB2 standard (type 4)

Element name dbinstance , engine name DB2

No special features. Password is always required.

DB2 native (type 2)

Element name db2native , engine name DB2

This special type uses native library (aka "type 2") to connect to database. Native CLI driver must be correctly installed and configured for current user session/shell (to diagnose, try executing db2 list database directory). This configuration includes also path to native drivers, so no other special configuration is needed.

Elements host and port are not used by this configuration (any content is simply ignored). Elements user and pass are optional and generally not used - main purpose of native driver is to avoid plaintext password authorization (alterscripts might be uploaded to database server and then applyalter executed via ssh).

Oracle

Element name db2native , engine name Oracle

Warning: Oracle database does not support DDL as part of transaction, making one of the main feature of applyalter void.

Command line invocation

TODO

Alterscript

TODO

Logging table and incremental mode

TODO