xml2ddl - Generate DDL for a Target DBMS

Contents

Return to main dbmstools documentation page.

Introduction

xml2ddl is used to generate DDL for one or more target DBMS's, based on an XML description of your schema (see documentation of the schema format). By default you execute the tool from the command line, passing it the name of the XML schema file and the name of one or more target DBMS. For each target DBMS, the tool generates a DDL script to create the schema and a second DDL script to drop it - all the scripts are written to the current directory by default.

Usage

You use the tool like this:

main/xml2ddl.py [options] -d <targetDBMS> [-d <targetDBMS> *] <schema.xml>

Options

-b baseName Specifies the base name for SQL files (defaults to the name of the database, as specified in the XML)
-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 files for. Mandatory, may be repeated for multiple DBMS's. Values recognised are: postgres7, postgres8, mssql, oracle, mysql, hsql
-h Shows help screen, and exits
-n length Sets the maximum name length (default is 30, for Oracle)
-o outputDir Write all files to the specified output directory (defaults to current directory)
-s Write all constraints to a separate script
-t Print table headers in database create script
-v Verbose output (to stderr)
-w Wiki markup - break description lines on [[BR]] etc.

Long Options

--help Shows help screen, and exits
--utf8 Encode SQL files as UTF-8, rather than ISO-8859-1
--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

Notes

Example

main/dbmstools/xml2ddl.py -o output/dbscripts -d postgres8 -d mssql -t PSSchema.xml

This command will generate the following files in the output/dbscripts directory (assuming the schema name in PSSchema.xml is 'ps'):

The start of the create-ps-postgres8.sql file looks like this:

/***************************************************************************
 *
 * Database create script
 *
 * Database:         ps
 * DBMS:             postgres8
 *****************************************************************************/

/*===========================================================================
 * Sequence:         SEQ_MsoUser_SystemId
 *===========================================================================*/
CREATE SEQUENCE SEQ_MsoUser_SystemId;

/*===========================================================================
 * Table:            MsoUser
 * Description:      Authorised users of MSO's
 * Size:             small
 * Select frequency: low
 * Update frequency: low
 *===========================================================================*/
CREATE TABLE MsoUser (
    systemId             NUMERIC(18) NOT NULL,
    userName             VARCHAR(18) NOT NULL,
    bigtextcol           VARCHAR(8000),
...

Ant Wrapper

See xml2ddl-ant to run xml2ddl from an ant build script.