Skip to end of metadata
Go to start of metadata

Introduction

The DDL generator is executed by commandline. The program takes the mapping files as input. Normally, it uses the default configuration file. Users can customize configuration files. The run.sh (run.bat for Windows) script helps you easy to run it from your console. It also provides an API for gererating DDL script from Castor mapping file. The log file is also created by configurating the log4j.xml and commons-logging.properties.

Configuration

Configuration files are used to define parameters, options for creating DDL scritp. There are 2 configuration files located in ./conf directory:

  • ddl.properties: contains the global configuration for all DB system
  • [database].properties: the specific database configuration file  contains the parameters/options for indivudual.

The program loads the configuration files each time when the DDL generator is instanced. If both define the same entry, the specific database value will overwrite the global configuration

ddl.properties

# Date: Jun 03, 2006
# Author: Le Duc Bao
# email: leducbao AT gmail DOT com
#
#configuration properties file for DDL generator
#

#database engine, except keywords:
# mysql, oracle, postgresql, db2, derby, hsql, mssql,
# pointbase, postgresql, sapdb, sybase
database_engine=mysql

#chema name
schema_name=test

#this parameter defines how to group ddl
#accept keywords:table (default), ddltype
#by table:
# 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)
#by ddltype:
# 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)
group_ddl_by=ddltype

# ddl format case =[casesensitive | upper | lower]

ddl_format_case=casesensitive
#sql bloc separator, this separator will be inserted after each group
sql_bloc_separator=\n

#line indent for ddl
line_indent=\t

#line separator, this will overwrite system line.separator
line.separator=\n

# option for generate DDL for SCHEMA statement
generate_ddl_for_schema=true

# option for generate DDL for DROP statement
generate_ddl_for_drop=true

# option for generate DDL for CREATE statement
generate_ddl_for_create=true

# option for generate DDL for ALTER statement
generate_ddl_for_alter=true

# option for generate DDL for PRIMARY KEY statement
generate_ddl_for_primarykey=true

# option for generate DDL for FOREIGN KEY statement
generate_ddl_for_foreignkey=true

# option for generate DDL for INDEX statement
generate_ddl_for_index=true

# option for generate DDL for Key generator statement
generate_ddl_for_keygenerator=true

# define the property's key for default precision of tinyint,
# default null, it will use default of DBMS
default_tinyint_precision=

# define the property's key for default precision of smallint
# if null, it will use default of DBMS
default_smallint_precision=

# define the property's key for default precision of integer
# if null, it will use default of DBMS
default_integer_precision=

# define the property's key for default precision of bitint
default_bigint_precision=19

# define the property's key for default decimals of bitint
default_bigint_decimals=0

# define the property's key for default precision of float
default_float_precision=38

# define the property's key for default decimals of float
default_float_decimals=7
   
# define the property's key for default precision of double
default_double_precision=53

# define the property's key for default decimals of double
default_double_decimals=15

# define the property's key for default precision of real
default_real_precision=38

# define the property's key for default decimals of real
default_real_decimals=7

# define the property's key for default precision of numeric
default_numeric_precision=65

# define the property's key for default decimals of numeric
default_numeric_decimals=30

# define the property's key for default precision of decimal
default_decimal_precision=65

# define the property's key for default decimals of decimal
default_decimal_decimals=30

# define the property's key for default length of char
default_char_length=256

# define the property's key for default length of varchar
default_varchar_length=256

# define the property's key for default length of longvarchar
default_longvarchar_length=1024

# define the property's key for default length of timestamp
default_date_precision=

# define the property's key for default length of timestamp
default_time_precision=

# define the property's key for default length of timestamp
default_timestamp_precision=19

#define the property's key for default length of binary
default_binary_length=256

#define the property's key for default length of varbinary
default_varbinary_length=256

#define the property's key for default length of longvarbinary
default_longvarbinary_length=1024

# define the property's key for default length of other
default_other_length=1024

# define the property's key for default of other type's sufixe
default_other_sufixe=K

# define the property's key for default length of javaobject
#use for DB2, Derby, HSQL
default_javaobject_length=1024

# define the property's key for default of java object type's sufixe
default_javaobject_sufixe=K

# define the property's key for default length of blob
default_blob_length=1024

# define the property's key for default of blob type's sufixe
default_blob_sufixe=K

# define the property's key for default length of clob
default_clob_length=1024

# define the property's key for default of clob type's sufixe
default_clob_sufixe=K

[database].properties 

In general, you must define the header_comment_text property. For example:

#header comment text
header_comment_text=/cds database/

#trigger template
# You can use fine pre-define keywords in trigger template. There are

  • <sequence_name>: sequence's name, refer to  Castor documentfor more information
  • <trigger_name>: trigger's name, auto-generated by using <sequence_name>
  • <table_name>: table's name of the trigger
  • <pk_name>: primary key
  • <pk_type>: primary key SQL's type

The example for Oracle trigger is listed bellow:

trigger_template=CREATE TRIGGER <trigger_name>\n\t\
 BEFORE INSERT OR UPDATE ON <table_name>\n\t\
 FOR EACH ROW\n\t\
 DECLARE\n\t\t\
    iCounter <table_name>.<pk_name>%TYPE;\n\t\t\
    cannot_change_counter EXCEPTION;\n\t\
 BEGIN\n\t\t\
     IF INSERTING THEN\n\t\t\t\
         Select <sequence_name>.NEXTVAL INTO iCounter FROM Dual;\n\t\t\t\
         :new.<pk_name> := iCounter;\n\t\t\
     END IF;\n\t\
\n\t\t\
     IF UPDATING THEN\n\t\t\t\
          IF NOT (:new.<pk_name> = :old.<pk_name>) THEN\n\t\t\t\t\
              RAISE cannot_change_counter;\n\t\t\t\
          END IF;\n\t\t\
     END IF;\n\t\
\n\t\
EXCEPTION\n\t\t\
      WHEN cannot_change_counter THEN\n\t\t\t\
          raise_application_error(-20000, 'Cannot Change Counter Value');\n\t\
END;

MySQL 

#engine is one of MYISAM, InnoDB, MERGE, MEMORY, BDB, ISAM or null for default
engine=InnoDB

#on delete for create the foreign key, one of the CASCADE, RESTRICT, SET NULL,
# NO ACTION, default is NO ACTION
foreign_key_on_delete=

#on update for create the foreign key, one of the CASCADE, RESTRICT, SET NULL,
# NO ACTION, default is NO ACTION
foreign_key_on_update= 

Commandline Run 

The program requires the castor-1.0.jar and additional library need for Castor. The command to run the DDL generator is:

 On Window

cd [DDL generator directory] 

set JAVA="%JAVA_HOME%\bin\java"

The LIB_DIR indicates to the directory which contains the library required for Castor.

set LIB_DIR=D:\projects\SoC\castor\castor-1.0-src\castor-1.0\lib

set cp=.\classes
for %%i in (%LIB_DIR%*.jar) do  set cp=%cp%;%%i

set cp=%cp%;.\lib\castor-1.0.jar

Next,  

%JAVA% -classpath %cp% org.castor.ddl.Main  -m <mapping file> [-c config.properties] [-d database.properties] [-e engine] [-o output.sql]

Where:

  • mapping file: input mapping file
  • config.properties: global configuration file
  • database.properties: specific database configuration file
  • engine: database engine supported by DDL generator: mysql, oracle, postgresql, db2, derby, hsql, mssql, pointbase, postgresql, sapdb, sybase
  • output.sql: output DDL file, if this parameter is ignored, the [mapping file].sql is generated.

A run.bat script includes all above code. You can use

run.bat  -m <mapping file> [-c config.properties] [-d database.properties] [-e engine] [-o output.sql]

where parameters like above

On Linux [not completed]

cd [DDL generator directory] 
export JAVA="%JAVA_HOME%\bin\java"

The LIB_DIR indicates to the directory which contains the library required for Castor.

export LIB_DIR=~ldbao/castor/ddlgenerator

export cp=./classes

export list=`ls $LIB_DIR`

for i in $list 

    export cp=$cp:$i

exoport cp=$cp;./lib/castor-1.0.jar

Next, 

%JAVA% -classpath %cp% org.castor.ddl.Main  -m <mapping file> [-c config.properties] [-d database.properties] [-e engine] [-o output.sql]

Where:

  • mapping file: input mapping file
  • config.properties: global configuration file
  • database.properties: specific database configuration file
  • engine: database engine supported by DDL generator: mysql, oracle, postgresql, db2, derby, hsql, mssql, pointbase, postgresql, sapdb, sybase
  • output.sql: output DDL file, if this parameter is ignored, the [mapping file].sql is generated.

How to Build

 You can check out the lastest source code from SVN server. The build requires Ant version 1.6.1 or later.

SVN Server:  http://svn.codehaus.org/castor/castor/trunk

Command to build:

cd [DDL generator home] 

ant

To create jar file:

ant jar 

Test

 To run UTF test of Castor's generator:

cd [DDL generator home]

ant run-test