13. Generating DDL from a Schema

Contents

13.1. Defining Template Files

13.2. Generating a DDL script

13.1. Defining Template Files

Once a mapping schema has been defined, you can generate a DDL script for the persistent classes. A DDL script is a group of SQL CREATE TABLE, CREATE INDEX, and other DDL (Data Definition Language) statements used to create all tables, indexes, and any other required database objects used by the persistent classes. A DDL script can be executed using a database vendor provided tool or directly from within a client program as named SQL statements.

The mapping tool provides the flexibility to generate a DDL script in any required format by generating the script based on a template you define. Multiple templates can be defined for multiple target databases. A template is defined by means of a template file that defines the format to use when generating a DDL script for persistent classes. A DDL template file is a text file made up of DDL SQL statements and special tags. A template file can be created or edited using any text editor.

The next section jumps directly into a simple example that shows the DDL script that is generated from a template. A definition of all possible template tags is then given in the section following the example.

13.1.1. A Simple Example

Below are the contents of a very simple template file.

%[%TABLE%CREATE TABLE %TABLE_NAME%(
%[%COLUMN%%COLUMN_NAME% %COL_DATATYPE% %NULLABILITY%,]%
);
]%

And below is an example of a DDL script generated from the above template file using MS Access data types:

CREATE TABLE CONTACT(
ContactID COUNTER NOT NULL,
LastName TEXT(20) NOT NULL,
SecondName TEXT(10) NOT NULL,
FirstName TEXT(20) NOT NULL,
Title TEXT(5) NOT NULL,
CompanyID LONG NOT NULL,
EntryDate DATETIME NOT NULL,
SupervisorID NOT NULL,
CommissionPctg SINGLE NOT NULL,
AmtOwed CURRENCY NOT NULL,
CategoryID INTEGER NOT NULL,
OwesMeFavor BIT NOT NULL
);

CREATE TABLE ADDRESS(
AddressID COUNTER NOT NULL,
ContactID LONG NOT NULL,
Type SHORT NOT NULL,
AdrName TEXT(30) NOT NULL,
Adr1 TEXT(30) NOT NULL,
Adr2 TEXT(30) NOT NULL,
City TEXT(20) NOT NULL,
State TEXT(15) NOT NULL,
Zip TEXT(10) NOT NULL,
Country TEXT(20) NOT NULL
);

....

13.1.2. Template Tags

The DDL script generator processes a template file by interpreting and processing all template tags, and outputting all remaining text unchanged into the generated DDL script file. Template tags are always capitalized words separated by underscores and surrounded with the percentage (%) sign (e.g., %COLUMN_NAME% is a tag). There are five types of tags: Place Holders, Conditional, Conditional Types, Loops, and Loop Types.

Place Holder tags are substituted by an actual object name or type during DDL script generation. Place Holder tags can also be enclosed by the ampersand sign (&) in addition to the percentage (%) sign. (More on that in the next paragraph.)

Conditional tags allow you to specify custom DDL text depending on the type of an object. The Conditional Type tag specifies the type of object to be processed in a Conditional tag.  The Conditional tag must always be followed by a Conditional Type tag that specifies the type of object to to which the customized DDL text applies. Inside a Conditional tag you may place DDL text and Place Holder tags. All Place Holder tags enclosed in a Conditional tag must be surrounded by the ampersand sign (&). All Place Holder tags NOT enclosed in a Conditional tag must be surrounded by the the percentage sign (%).

Loop tags are used to specify that one or more statements are to be repeated for the object represented by the Loop Type tag that follows the Loop tag. Loop tags delimit all text and tags that should be repeated for all the applicable objects. The Loop Start tag is a percentage sign followed by an open bracket (%[). The Loop End tag is a close bracket followed by percentage sign (]%). The Loop Start tag must always be followed by a Loop Type tag that specifies the type of object to be processed in the loop.

Below is a table that shows all Place Holder, Conditional, Conditional Type, and Loop Type tags with a brief description of each:

Table Notes:

(1)  The NULLABILITY tag is replaced by the string NULL if the corresponding attribute is nullable, or by the string NOT NULL   if the corresponding attribute is not nullable

(2)  The NULLABLE_MSG tag is replaced by a different custom string you specify, depending on whether the corresponding attribute is nullable. For example, in the template code below:

%[%COLUMN%%COLUMN_NAME% %COL_DATATYPE%%NULLABLE_MSG IS NULL: IS NOT NULL%,]%

the %NULLABLE_MSG IS NULL: IS NOT NULL% tag is replaced by the string IS NULL (including the leading space) if the attribute is nullable, and by the string IS NOT NULL (including the leading space) if the attribute is not nullable. The colon symbol (:) must be used as a separator between the null and not null strings. This tag is useful in the rare event that the database does not support the standard SQL syntax of NULL/NOT NULL to specify column nullability.

(3)  The UNIQUE and UNIQUE_COL tags apply only when the attribute is defined as unique and not indexed. The INDEXED and INDEXED_COL tags apply only when the
attribute is defined as indexed and not unique. The UNIQUE_INDEXED and UNIQUE_INDEXED_COL tags apply only when the attribute is defined as both unique and indexed. The createtablesddl.tmpl template demonstrate the use of these tags.

13.1.3. A Complex Example

An important deficiency of the simple template presented in section 13.1.1. is that it does not account for primary key and foreign key constraints. Below are the contents of a more complex template file named access_ddl.tmpl that addresses these issues and also creates indexes. This template file is customized for MS Access, and can be found in the schema repository directory.

%[%TABLE%CREATE TABLE %TABLE_NAME%(
%[%COLUMN%%COLUMN_NAME% %COL_DATATYPE%%IF_FIRST_OID CONSTRAINT PK&COLUMN_NAME& PRIMARY KEY ELSE_IF_REFERENCE CONSTRAINT FK&COLUMN_NAME& REFERENCES &REF_TAB_NAME&(&REF_COL_NAME&) ELSE_ &NULLABILITY&%,]%
);

%[%INDEXED_COL%CREATE UNIQUE INDEX %INDEXED_COL_NAME%_idx ON %TABLE_NAME%(%INDEXED_COL_NAME%);]%

]%

Below is an example of the DDL script generated from the contactdemo2 sample application schema using the above template file:

CREATE TABLE CONTACT(
ContactID COUNTER CONSTRAINT PKContactID PRIMARY KEY,
LastName TEXT(20) NOT NULL,
SecondName TEXT(10) NOT NULL,
FirstName TEXT(20) NOT NULL,
Title TEXT(5) NOT NULL,
CompanyID LONG CONSTRAINT FKCompanyID REFERENCES COMPANY(CompanyID),
EntryDate DATETIME NOT NULL,
SupervisorID LONG CONSTRAINT FKSupervisorID REFERENCES CONTACT(ContactID),
CommissionPctg SINGLE NOT NULL,
AmtOwed CURRENCY NOT NULL,
CategoryID INTEGER NOT NULL,
OwesMeFavor BIT NOT NULL
);

CREATE TABLE COMPANY(
CompanyID COUNTER CONSTRAINT PKCompanyID PRIMARY KEY,
Name TEXT(25) NOT NULL,
WebAddress TEXT(20) NOT NULL
);

CREATE TABLE ADDRESS(
AddressID COUNTER CONSTRAINT PKAddressID PRIMARY KEY,
ContactID LONG CONSTRAINT FKContactID REFERENCES CONTACT(ContactID),
Type SHORT NOT NULL,
AdrName TEXT(30) NOT NULL,
Adr1 TEXT(30) NOT NULL,
Adr2 TEXT(30) NOT NULL,
City TEXT(20) NOT NULL,
State TEXT(15) NOT NULL,
Zip TEXT(10) NOT NULL,
Country TEXT(20) NOT NULL
);

CREATE TABLE LOCATOR(
LocatorID COUNTER CONSTRAINT PKLocatorID PRIMARY KEY,
ContactID LONG CONSTRAINT FKContactID REFERENCES CONTACT(ContactID),
Type SHORT NOT NULL,
LocatorNo TEXT(15) NOT NULL,
Description TEXT(20) NOT NULL
);

CREATE TABLE CATEGORY(
Description TEXT(25) NOT NULL,
CategoryID COUNTER CONSTRAINT PKCategoryID PRIMARY KEY
);

The repository directory contains additional sample DDL template files for other target databases. A listing of all included sample DDL template files, along with a description, can be found in a README.txt file located in the schema repository directory.

13.2. Generating a DDL script

To generate a DDL script in the mapping tool select the Generate->DDL script from schema menu option to display the dialog box shown below.

The Available list shows all classes defined in the current application schema. Use the Add and Add All buttons to move classes from the Available list to the Selected list. You can also use the Remove and Remove All buttons to move classes from the Selected list back to the Available list.

Click the Options button to bring up the Generation Options panel of the configuration dialog box if necessary to verify or update the template file and output file to be used when generating the DDL script.

Once the Selected list displays the classes for which you want to generate a DDL script click the Generate button to start the generation process. The generation procedure produces DDL statements for all classes selected, and places the generated text in the output file specified in the Generation Options of the configuration dialog box. If the output file exists, it will be overwritten without warning. The status bar at the bottom edge of the mapping tool will inform you when the generation process has completed.

Below are some additional notes about the generated DDL:

• If your application schema involves SUPERSET classes, classes with filters, or referenced collections mapped to join tables, it is possible that some of the columns in the CREATE TABLE statements will not be listed in the order you desire. The columns for which this may happen are primary key columns not mapped to object IDs of SUPERSET classes, filter columns, and join table columns. If that is the case, you can simply cut and paste the output file to place the columns in the desired order before executing the DDL script against the target database.  Please note that we are talking about aesthetics. The actual ability of VBSF to persist your classes using the tables created by executing the generated DDL script is not affected. The only order that is significant to maintain portability across databases is the order of columns mapped to persistent class attributes, and those columns are listed in proper order in the DDL script.

Next Section

Return to Table of Contents