Describes how to use the DDL Generator, and what features and options are currently supported.
Castor DDL Generator creates SQL scripts to drop/create tables, unique keys, sequences and so on based on the input of a Castor JDO mapping file.
java org.castor.ddlgen.Main -m mapping.xml
This will generate the SQL script mapping.sql
in the same directory
mapping.xml is located.
The DDL Generator has a number of different options which may be set. Some of
them are specified at the command line while others need to be configured through
a property file. Most of the options are located in global properties file
org/castor/ddlgen/ddlgen.properties
, but there are also some
options that are specific for one database engine. These DB-specific properties can
be found at org/castor/ddlgen/engine/<database>/<database>.properties
.
Table 7.1. Command line options
Option | Args | Description | Optional? |
---|---|---|---|
m | filename | Castor JDO mapping file to generate DDL for. | Required |
o | filename | Name of file the generated DDL will be written to. If not specified the generated DDL will be written to a file named similar as the Castor JDO mapping source file with xml extension being replaced by sql. | Optional |
e | database engine | Name of database engine to generate DDL for. The engine used as default
can be specified with org.castor.ddlgen.DefaultEngine option of
global properties file. | Optional |
c | filename | Alternative global properties file to be used when generating DDL. | Optional |
d | filename | Alternative database specific properties file to be used when generating DDL. | Optional |
h | Shows help/usage information. | Optional |
The DDL Generator supports generation of SQL scripts for the following database engines:
Table 7.2. Description of the attributes
Name | Database engine | Property file |
---|---|---|
db2 | DB/2 | org/castor/ddlgen/engine/db2/db2.properties |
derby | Apache Derby | org/castor/ddlgen/engine/derby/derby.properties |
hsql | Hypersonic SQL | org/castor/ddlgen/engine/hsql/hsql.properties |
mssql | Microsoft SQL Server | org/castor/ddlgen/engine/mssql/mssql.properties |
mysql | MySQL | org/castor/ddlgen/engine/mysql/mysql.properties |
oracle | Oracle | org/castor/ddlgen/engine/oracle/oracle.properties |
pointbase | Borland Pointbase | org/castor/ddlgen/engine/pointbase/pointbase.properties |
postgresql | PostgreSQL | org/castor/ddlgen/engine/postgresql/postgresql.properties |
sapdb | SAP DB / MaxDB | org/castor/ddlgen/engine/sapdb/sapdb.properties |
sybase | Sybase | org/castor/ddlgen/engine/sybase/sybase.properties |
Describes how to use the Ant task for the Castor DDL Generator and its features.
An alternative to using the command line as shown in the previous section, the Castor DDL Generator Ant Task can be used to call the DDL generator for class generation. The only requirement is that the castor-<version>-anttasks.jar must be made available to your Ant installation.
Please find below the complete list of parameters that can be set on the Castor source generator.
Table 7.3.
Attribute | Description | Required? |
---|---|---|
file | The name of the Castor JDO mapping file to use as input for DDL generation. | Yes |
ddlFileName | The name of the DDL file to be generated. | Yes |
databaseEngine | The name of database engine to generate DDL for. | Yes |
globalProperties | Name of a custom (global) properties file to be used during DDL generation. | No |
databaseEngineProperties | Name of a custom database specific properties file to be used during DDL generation. | No |
Alternatively to specifying the file property, it is possible to work with a nested <FileSet> element or with the dir property.
Below is an example of how to use this task from within an Ant target definition named 'castor:ddl:src':
<target name="castor:ddl:src" depends="init" description="Generate a DDL script from a JDO mapping file."> <taskdef name="castor-ddlgen" classname="org.castor.anttask.CastorDDLGenTask" classpathref="castor.class.path" /> <mkdir dir="generated" /> <castor-ddlgen file="src/main/resources/mapping.xml" ddlFileName="target/generated/ddl/mapping.sql" databaseEngine="mysql"/> </target>
This section describes the mapping between Castor sql type java.sql.Types constant, java data type, sql type of supported database.
Table 7.4.
Castor Type | JDBC Type | Java Object Type | MySQL | PostgreSQL | Oracle | Derby | MSSQL | SapDB | DB2 | Sybase | HSQL | PointBase |
---|---|---|---|---|---|---|---|---|---|---|---|---|
BIT | BIT | java.lang.Boolean | TINYINT(1) | BOOLEAN | BOOLEAN | CHAR FOR BIT DATA | BIT | BOOLEAN | BIT | BIT | BOOLEAN | |
TINYINT | TINYINT | java.lang.Byte | TINYINT | SMALLINT | SMALLINT | SMALLINT | TINYINT | SMALLINT | SMALLINT | TINYINT | TINYINT | SMALLINT |
SMALLINT | SMALLINT | java.lang.Short | SMALLINT | SMALLINT | SMALLINT | SMALLINT | SMALLINT | SMALLINT | SMALLINT | SMALLINT | SMALLINT | SMALLINT |
INTEGER | INTEGER | java.lang.Integer | INTEGER | INTEGER | INTEGER | INTEGER | INTEGER | INTEGER | INTEGER | INTEGER | INTEGER | INTEGER |
BIGINT | BIGINT | java.lang.Long | BIGINT | BIGINT | NUMERIC | BIGINT | BIGINT | INTEGER | BIGINT | INTEGER | BIGINT | NUMERIC |
FLOAT | FLOAT | java.lang.Double | FLOAT | DOUBLE PRECISION | FLOAT | FLOAT | FLOAT | FLOAT | FLOAT | FLOAT | FLOAT | FLOAT |
DOUBLE | DOUBLE | java.lang.Double | DOUBLE | DOUBLE PRECISION | DOUBLE PRECISION | DOUBLE | DOUBLE PRECISION | DOUBLE PRECISION | DOUBLE | DOUBLE PRECISION | DOUBLE PRECISION | DOUBLE PRECISION |
REAL | REAL | java.lang.Float | REAL | REAL | REAL | REAL | REAL | DOUBLE PRECISION | REAL | REAL | REAL | REAL |
NUMERIC | NUMERIC | java.math.BigDecimal | NUMERIC | NUMERIC | NUMERIC | NUMERIC | NUMERIC | NUMERIC | NUMERIC | NUMERIC | NUMERIC | NUMERIC |
DECIMAL | DECIMAL | java.math.BigDecimal | DECIMAL | NUMERIC | DECIMAL | DECIMAL | DECIMAL | DECIMAL | DECIMAL | DECIMAL | DECIMAL | DECIMAL |
CHAR | CHAR | java.lang.String | CHAR | CHAR | CHAR | CHAR | CHAR | CHAR | CHAR | CHAR | CHAR | CHAR |
VARCHAR | VARCHAR | java.lang.String | VARCHAR | VARCHAR | VARCHAR2 | VARCHAR | VARCHAR | VARCHAR | VARCHAR | VARCHAR | VARCHAR | VARCHAR |
DATE | DATE | java.sql.Date | DATE | DATE | DATE | DATE | DATETIME | DATE | DATE | DATETIME | DATE | DATE |
TIME | TIME | java.sql.Time | TIME | TIME | DATE | TIME | DATETIME | TIME | TIME | DATETIME | TIME | TIME |
TIMESTAMP | TIMESTAMP | java.sql.Timestamp | TIMESTAMP | TIMESTAMP | TIMESTAMP | TIMESTAMP | TIMESTAMP | TIMESTAMP | TIMESTAMP | TIMESTAMP | TIMESTAMP | TIMESTAMP |
BINARY | BINARY | byte[] | BINARY | BYTEA | RAW | CHAR [n] FOR BIT DATA | BINARY | BLOB | CHAR [n] FOR BIT DATA | BINARY | BINARY | BLOB |
VARBINARY | VARBINARY | byte[] | VARBINARY | BYTEA | LONG RAW | VARCHAR [] FOR BIT DATA | VARBINARY | BLOB | VARCHAR [] FOR BIT DATA | VARBINARY | VARBINARY | BLOB |
LONGVARBINARY | LONGVARBINARY | byte[] | VARBINARY | BYTEA | LONG RAW | LONG VARCHAR FOR BIT DATA | IMAGE | BLOB | LONG VARCHAR FOR BIT DATA | VARBINARY | LONGVARBINARY | BLOB |
OTHER | OTHER | java.lang.Object | BLOB | BYTEA | BLOB | BLOB | IMAGE | BLOB | BLOB | IMAGE | OTHER | BLOB |
JAVA_OBJECT | JAVA_OBJECT | java.lang.Object | BLOB | BYTEA | BLOB | BLOB | IMAGE | BLOB | BLOB | IMAGE | OBJECT | BLOB |
BLOB | BLOB | java.io.InputStream | BLOB | BYTEA | BLOB | BLOB | IMAGE | BLOB | BLOB | IMAGE | OBJECT | BLOB |
CLOB | CLOB | java.sql.Clob | TEXT | TEXT | CLOB | CLOB | TEXT | CLOB | CLOB | TEXT | OBJECT | CLOB |
Describes the properties available on the Castor DDL Generator.
At startup, the DDL Generator first evaluates the command line options. Next it loads the global properties on the command line if specified, otherwise the default properties included with the DDL Generator. There are two important properties loaded at startup:
tells the DDL Generator about the supported database engines.
The database engine for which to generate a SQL script. Can be overridden on the command line
The last step at startup is to read the specific configuration of the database engine being used. A custom configuration can be provided on the command line to override the default.
Please find below a list of global properties to control various advanced options of the DDL Generator.
Table 7.5. Command line options
Option | Description | Values | Default | Since |
---|---|---|---|---|
org.castor.ddlgen.Generators | Generator classes of supported database engines. | 1.1 | ||
org.castor.ddlgen.DefaultEngine | Name of default database engine. Will be overwritten by engine specified on commandline. | db2, derby, hsql, mssql, mysql, oracle, pointbase, postgresql, sapdb or sybase | mysql | 1.1 |
org.castor.ddlgen.SchemaName | Name of the database schema. | test | 1.1 | |
org.castor.ddlgen.GroupStatements | How to group generated DDL statements? | TABLE or DDLTYPE | TABLE | 1.1 |
org.castor.ddlgen.CharFormat | How to format characters of generated DDL statements? | SENSITIVE, UPPER or LOWER | SENSITIVE | 1.1 |
org.castor.ddlgen.Newline | Character sequence to write for newline. | \n | 1.1 | |
org.castor.ddlgen.Indention | Character sequence to write for indented lines. | \t | 1.1 | |
org.castor.ddlgen.GenerateSchema | Generate SCHEMA statements. | true or false | true | 1.1 |
org.castor.ddlgen.GenerateDrop | Generate DROP statements. | true or false | true | 1.1 |
org.castor.ddlgen.GenerateCreate | Generate CREATE statements. | true or false | true | 1.1 |
org.castor.ddlgen.GeneratePrimaryKey | Generate PRIMARYKEY statement. | true or false | true | 1.1 |
org.castor.ddlgen.GenerateForeignKey | Generate FOREIGNKEY statement. | true or false | true | 1.1 |
org.castor.ddlgen.GenerateIndex | Generate INDEX statements (Not supported yet). | true or false | false | 1.1 |
org.castor.ddlgen.GenerateKeyGenerator | Generate KEYGENERATOR statements. | true or false | true | 1.1 |
default_tinyint_precision | Default precision of tinyint values. | 1.1 | ||
default_smallint_precision | Default precision of smallint values. | 1.1 | ||
default_integer_precision | Default precision of integer values. | 1.1 | ||
default_bigint_precision | Default precision of bigint values. | 19 | 1.1 | |
default_bigint_decimals | Default decimals of bigint values. | 0 | 1.1 | |
default_float_precision | Default precision of float values. | 38 | 1.1 | |
Default precision of tinyint values. | 1.1 | |||
default_float_decimals | Default decimals of float values. | 7 | 1.1 | |
default_double_precision | Default precision of double values. | 53 | 1.1 | |
default_double_decimals | Default decimals of double values. | 15 | 1.1 | |
default_real_precision | Default precision of real values. | 38 | 1.1 | |
default_real_decimals | Default decimals of real values. | 7 | 1.1 | |
default_numeric_precision | Default precision of numeric values. | 65 | 1.1 | |
default_numeric_decimals | Default decimals of numeric values. | 30 | 1.1 | |
default_decimal_precision | Default precision of decimal values. | 65 | 1.1 | |
default_decimal_decimals | Default decimals of decimal values. | 30 | 1.1 | |
default_char_length | Default length of char values. | 256 | 1.1 | |
default_varchar_length | Default length of varchar values. | 256 | 1.1 | |
default_longvarchar_length | Default length of longvarchar values. | 1024 | 1.1 | |
default_date_precision | Default precision of date values. | 1.1 | ||
default_time_precision | Default precision of time values. | 1.1 | ||
default_timestamp_precision | Default precision of timestamp values. | 19 | 1.1 | |
default_binary_length | Default length of binary values. | 256 | 1.1 | |
default_varbinary_length | Default length of varbinary values. | 256 | 1.1 | |
default_longvarbinary_length | Default length of longvarbinary values. | 1024 | 1.1 | |
default_other_length | Default length of other values. | 1024 | 1.1 | |
default_javaobject_length | Default length of javaobject values. | 1024 | 1.1 | |
default_blob_length | Default length of blob values. | 1024 | 1.1 | |
default_clob_length | Default length of clob values. | 1024 | 1.1 |
The supported database engines are defined as follows:
# # generator classes of supported database engines # org.castor.ddlgen.Generators=\ org.castor.ddlgen.engine.db2.Db2Generator,\ org.castor.ddlgen.engine.derby.DerbyGenerator,\ org.castor.ddlgen.engine.hsql.HsqlGenerator,\ org.castor.ddlgen.engine.mssql.MssqlGenerator,\ org.castor.ddlgen.engine.mysql.MysqlGenerator,\ org.castor.ddlgen.engine.oracle.OracleGenerator,\ org.castor.ddlgen.engine.pointbase.PointBaseGenerator,\ org.castor.ddlgen.engine.postgresql.PostgresqlGenerator,\ org.castor.ddlgen.engine.sapdb.SapdbGenerator,\ org.castor.ddlgen.engine.sybase.SybaseGenerator
There are 2 supported modes to group DDL statements. For a simple example, the output of both modes is:
drop A if exist create A(IDA int); alter table A add primary key (IDA) drop B if exist create B(IDB int); alter table B add primary key (IDB)
Figure 7.1. org.castor.ddlgen.GroupStatements=TABLE
drop A if exist drop B if exist create A(IDA int); create B(IDB int); alter table A add primary key (IDA) alter table B add primary key (IDB)
Figure 7.2. org.castor.ddlgen.GroupStatements=DDLTYPE
Below you can find a list of specific properties to control various advanced options of the DDL Generator.
Table 7.6. Common properties for all databases
Option | Description | Values | Default | Since |
---|---|---|---|---|
org.castor.ddlgen.KeyGeneratorFactories | Key generator factory classes supported by DB/2. | 1.1 | ||
org.castor.ddlgen.HeaderComment | Comment to add to head of generated script. | 1.1 |
The key generators supported by a database engine are defined as follows (example taken from Oracle):
# # key generator factory classes of supported database engines # org.castor.ddlgen.KeyGeneratorFactories=\ org.castor.ddlgen.keygenerator.HighLowKeyGeneratorFactory,\ org.castor.ddlgen.keygenerator.MaxKeyGeneratorFactory,\ org.castor.ddlgen.keygenerator.UUIDKeyGeneratorFactory,\ org.castor.ddlgen.engine.oracle.OracleSequenceKeyGeneratorFactory
Table 7.7. Common properties for all databases
Option | Description | Values | Default | Since |
---|---|---|---|---|
org.castor.ddlgen.TriggerTemplate | Template to create TRIGGER statements. | 1.1 |
Below you can take a look at the default trigger template defined for Oracle. The DDL Generator will replace the parameters in brackets with appropriate values (e.g. <table_name>).
# # trigger template # org.castor.ddlgen.TriggerTemplate=\ CREATE TRIGGER <trigger_name> BEFORE INSERT OR UPDATE ON <table_name> FOR EACH ROW DECLARE iCounter <table_name>.<pk_name>%TYPE; cannot_change_counter EXCEPTION; BEGIN IF INSERTING THEN Select <sequence_name>.NEXTVAL INTO iCounter FROM Dual; :new.<pk_name> := iCounter; END IF; IF UPDATING THEN IF NOT (:new.<pk_name> = :old.<pk_name>) THEN RAISE cannot_change_counter; END IF; END IF; EXCEPTION WHEN cannot_change_counter THEN raise_application_error(-20000, 'Cannot Change Counter Value'); END;
Figure 7.3. Default trigegr template for Oracle
Table 7.8. Common properties for all databases
Option | Description | Values | Default | Since |
---|---|---|---|---|
org.castor.ddlgen.engine.mysql.StorageEngine | Storage engine to be used. If left empty the default configured at the database server will be used. | MYISAM, InnoDB, MERGE, MEMORY, BDB or ISAM | 1.1 | |
org.castor.ddlgen.engine.mysql.ForeignKeyOnDeleteStrategy | Delete strategy for foreign keys. If not specified NO ACTION will be used by default. | CASCADE, RESTRICT, SET NULL, NO ACTION | 1.1 | |
org.castor.ddlgen.engine.mysql.ForeignKeyOnUpdateStrategy | Update strategy for foreign keys. If not specified NO ACTION will be used by default. | CASCADE, RESTRICT, SET NULL, NO ACTION | 1.1 |