Data Definition Language (DDL) Generation

Open Liberty includes a utility for generating Data Definition Language (DDL) scripts. This utility, called ddlGen, simplifies the process of creating or replicating database schemas for applications running on Open Liberty servers.

Background

A databaseStore is a configuration element in Open Liberty that defines a persistence store that server features use, including Jakarta Data repositories that are not configured to use a Persistence Unit reference. It specifies how and where data is stored and accessed, providing a central definition for database usage.

Persistence Unit is a concept defined by the Jakarta Persistence specification. It represents a set of entity classes managed by EntityManager instances in an application. These entity classes correspond to the data that a single data store contains. You define a persistence unit in a persistence.xml file and it includes metadata about the entity classes, database connection details, and provider-specific configurations.

Overview of DDL Generation

Open Liberty includes the ddlGen utility and locates it in the wlp/bin/ directory as ddlGen (for Unix-like systems) and ddlGen.bat (for Windows). It generates DDL files for databaseStore elements you define or the system generates in the server configuration, enabling database administrators to create or replicate schemas required by applications. The ddlGen utility does not generate DDL for Persistence Units that the application defines. If you are using a Persistence Unit, use the mechanisms defined by the Jakarta Persistence specification for DDL generation instead of ddlGen.

The Liberty DDL Generation Guide provides detailed documentation about ddlGen.

Considerations

If you configure a repository interface with a dataStore that points to a dataSource, such as:

The DefaultDataSource
A DataSource JNDI name
A DataSource resource reference
A DataSource configuration id (from server.xml)

Then, the ddlGen utility will be able to generate DDL files from a generated databaseStore. This ensures the database schema that Jakarta Data repositories require is well-defined and easily manageable.

The ddlGen utility does not generate DDL files for repository interfaces you configure with a dataStore that points to a Persistence Unit reference. In these cases, the Jakarta Persistence provider handles DDL generation, which offers its own methods for creating schema definitions. This ensures the separation of responsibilities and allows Jakarta Persistence to maintain full control over the management of its schema generation.

Purpose

The ddlGen utility generates DDL files for each databaseStore element you define or the system generates in the Open Liberty server configuration. This includes all features that utilize the databaseStore element, including Jakarta Data. For Jakarta Data, the generated DDL files are suffixed with _JakartaData.ddl.

The ddlGen utility enables database administrators to create or replicate schemas without granting schema-altering privileges (e.g., CREATE, DROP) to Open Liberty database users. This approach ensures Liberty users can only perform operational tasks (e.g., INSERT, UPDATE, DELETE) while maintaining strict control over schema management.

Prerequisites

To use the ddlGen utility, the localConnector-1.0 feature must be enabled in the server configuration:

<featureManager>
  <feature>localConnector-1.0</feature>
</featureManager>

Running the ddlGen Utility

Start the server
```
./bin/server start <server-name>
```
Generate DDL files
```
./bin/ddlGen generate <server-name>
```
Stop the server
```
./bin/server stop <server-name>
```

The system will store the generated DDL files in the wlp/usr/<server-name>/ddl/ directory. Each file corresponds to a databaseStore you define or the system generates in the server configuration.

Example DDL Files

The Choosing the Database section of the built-in Jakarta Data provider documentation has sample server configurations for each possible dataStore configuration possible on a repository interface. This section has example DDL files for each of these examples to show how the output and file name can change in different situations.

Default DataSource

DDL file name: …databaseStore[java.comp.DefaultDataSource]_JakartaData.ddl

DDL file output:

CREATE TABLE Car (
  VIN VARCHAR(255) NOT NULL,
  MAKE VARCHAR(255),
  MODEL VARCHAR(255),
  MODELYEAR INTEGER,
  ODOMETER INTEGER,
  PRICE FLOAT,
  PRIMARY KEY (VIN)
);

DataSource Id

DDL file name: …databaseStore[ExampleDataSourceID]_JakartaData.ddl

DDL file output:

CREATE TABLE Car (
    VIN NVARCHAR2(255) NOT NULL,
    MAKE NVARCHAR2(255) NULL,
    MODEL NVARCHAR2(255) NULL,
    MODELYEAR NUMBER(10) NULL,
    ODOMETER NUMBER(10) NULL,
    PRICE NUMBER(19,4) NULL,
    PRIMARY KEY (VIN)
)

EXIT;

DataSource JNDI Name

DDL file name: …databaseStore[jdbc.ExampleDataSource]_JakartaData.ddl

DDL file output:

CREATE TABLE Car (
    VIN VARCHAR(255) FOR MIXED DATA NOT NULL,
    MAKE VARCHAR(255) FOR MIXED DATA,
    MODEL VARCHAR(255) FOR MIXED DATA,
    MODELYEAR INTEGER,
    ODOMETER INTEGER,
    PRICE FLOAT,
    PRIMARY KEY (VIN)
);

DataSource Resource Reference

DDL file name: …databaseStore[java.app.env.jdbc.ExampleDataSourceRef]_JakartaData.ddl

DDL file output:

CREATE TABLE Car (
    VIN NVARCHAR(255) NOT NULL,
    MAKE NVARCHAR(255) NULL,
    MODEL NVARCHAR(255) NULL,
    MODELYEAR INTEGER NULL,
    ODOMETER INTEGER NULL,
    PRICE FLOAT(16) NULL,
    PRIMARY KEY (VIN)
);

DataSource Definition

DDL file name: …databaseStore[java.comp.jdbc.ExampleDataSourceDef]_JakartaData.ddl

DDL file output:

CREATE TABLE Car (
    VIN VARCHAR(255) NOT NULL,
    MAKE VARCHAR(255),
    MODEL VARCHAR(255),
    MODELYEAR INTEGER,
    ODOMETER INTEGER,
    PRICE FLOAT,
    PRIMARY KEY (VIN)
)

Persistence Unit Reference

No DDL file created. The Jakarta Persistence provider handles DDL generation, which offers its own methods for creating schema definitions.

DatabaseStore

DDL file name: …databaseStore[ExampleDatabaseStore]_JakartaData.ddl

DDL file output:

CREATE TABLE JAKARTA.DATACar (
    VIN VARCHAR(255) NOT NULL,
    MAKE VARCHAR(255),
    MODEL VARCHAR(255),
    MODELYEAR INTEGER,
    ODOMETER INTEGER,
    PRICE FLOAT,
    PRIMARY KEY (VIN)
);