deltaddl - Generate Schema Upgrades


Return to main dbmstools documentation page.


deltaddl is used to generate DDL to upgrade from one version of a schema to another, for one or more target DBMS's, based on the deltas between 2 versions of the XML description of your schemas (see the documentation of the schema format).

By default you execute the tool from the command-line, passing it the name of the 2 XML schema files and the name of 1 or more target DBMS. For each target DBMS, the tool generates up to 3 scripts:

  1. A first DDL script to create any new tables, columns, views etc., to drop any awkward constraints on columns being modified, and a few other things
  2. An optional DML script to perform any DML operations (usually copying to temporary columns, if required)
  3. A second DDL script to add all new or modified constraints and indexes, to drop any old columns and tables, etc.

To upgrade a database you would run the first DDL script, then run whatever DML scripts you needed to copy or modify data in the existing tables (for example, you may need to reformat the contents of a column, or populate a new column which will not allow nulls). Finally, you would run the second DDL script.

The reason for this separation is that deltaddl can't be sure that it's doing the right thing. You may want to restructure data in some columns, and you'll probably need to do it before the constraints are added to the new columns or the old ones are deleted. This way, deltaddl can generate what it can, and you can either modify the second script, or write your own scripts to execute between the first and third scripts.


You use the tool like this:

main/ [options] -d <targetDBMS> <schemaVersion1.xml> <schemaVersion2.xml>


-b baseName Specifies the base name for SQL files (defaults to the name of the database)
-c commandTerminator
 Specifies the command terminator to use for DDL statements (defaults to the standard for the DBMS). The strings 'r' and 'n' will be replaced with carriage returns and linefeeds respectively
-d dbms Name of the DBMS to generate schema files for. Mandatory, may be repeated for multiple DBMS's. Values recognised are: postgres7, postgres8, mssql, oracle, mysql, hsql
-f Output only schema deltas to screen, then exit
-h Shows help screen, and exits
-n length Sets the maximum name length (default taken from schema)
-o outputDir Write all files to the specified output directory (defaults to current directory)
-t Print table headers in schema create script
-u Output only update actions to take (for DBMS), then exit
-v Print verbose messages
-w Wiki markup - break description lines on [[BR]]

Long Options:

--help Shows help screen, and exits
 Omit this object from the scripts (include as many times as required). Names are e.g. aTable, aTable.aColumn, aTable.aConstraint, aTable.anIndex, aView, aSequence
--var name_and_value
 Sets the variable for use in conditions and token replacement, where name_and_value is the variable name and value separated by a colon, e.g. myVar:foo
--version Print version number and exit


main/dbmstools/ -o output/dbscripts -d postgres8 -vt -o output previous/ps- ps.xml



While I'm making damning admissions, heres some information on current limitations in deltaddl: