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:
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/deltaddl.py [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]] |
| --help | Shows help screen, and exits |
| --omit=objectName | |
| 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/deltaddl.py -o output/dbscripts -d postgres8 -vt -o output previous/ps-3.1.0.2.xml ps.xml
This tool is currently under active development - it is not yet complete. Functionality depends on which DBMS you're using - see the FAQ for more details.
If you have problems, or if your DBMS isn't well enough suppored, it's probably worth giving deltaddl a try anyway, particularly using the '-f' and '-u' options, which just show what's changed or what actions are needed, rather than trying to write the scripts to make the changes.
There's no ant wrapper for deltaddl, since generating updates is rather difficult to automate - it's hard to know what previous version to use, and there will often be some manual tweaking required.
By reversing the order of the XML file paths, deltaddl can generate downgrade scripts. These can be useful if you need a back-out strategy during an upgrade.
See the FAQ for details of running this tool under Windows.
There are sometimes things that deltaddl will get wrong, because it just doesn't have quite enough knowledge to work out what order to do things in. If you run into a problem, you'll probably want to use --omit to leave out the offending object or column, and add it manually to your own custom DDL script
If you pass variables to deltaddl, the same variable values will be used on both versions of the schema.
While I'm making damning admissions, heres some information on current limitations in deltaddl: