Introduction

Although the DML used by most DBMS's is relatively standardised, there are still enough differences that maintaining a common set of data for several of them can range from challenging to downright impossible. Since we frequently have to do exactly this, for reference data or test data, life gets awkward.

filterdml is used to generate DML for one or more target DBMS's, based on a marked-up DML file. The DML file contains the superset of all data and SQL commands, but anything that doesn't apply to all DBMS's is annotated with the DBMS's it does apply to. filterdml will then generate a version of the file for each DBMS you specify, filtering out all the pieces that don't apply. Simple, but quite effective.

By default you execute the tool from the command-line, passing it the name of the base DML file and the name of 1 or more target DBMS. Output can be written as files in a directory, or to stdout (the default).

DML Mark-up Format

The mark-up format for the DML file is similar to that used by Velocity templates, and is based on blocks delimited by #dbms() and #end tags.

The #dbms() tag contains a list of 1 or more DBMS names that the tagged section applies to. The tags may surround text on the same line, or over multiple lines - in the latter case, each tag must appear on its own line (see example below).

-- A single-line example
#dbms(mssql) SET IDENTITY_INSERT Acquirer ON #end

-- Single-line within an SQL statement
INSERT INTO Acquirer (#dbms(postgres8, oracle) systemId, #end columnA, columnB)
    VALUES (#dbms(postgres8, oracle) 1000, #end 'valueA', 'valueB');

-- Multi-line
#dbms(oracle)
WHENEVER SQLERROR EXIT -1 ROLLBACK;
PROMPT Enter password for user X.
CONNECT mxdb
#end

Usage

You use the tool like this:

main/filterdml.py [options] <input.sql> [<outputDir>]

If outputDir is provided, input.sql is used as the base for filenames, with the DBMS name appended, e.g. myscript.sql will lead to myscript-mssql.sql and myscript-postgres8.sql. If outputDir is omitted, output goes to stdout.

Notes

• See the FAQ for details of running this tool under Windows.

Example

main/filterdml.py -o output/dbscripts -d postgres8 -d mssql -t ps-test-data.xml