Generate DML file from DDL file

[PorcoRosso85]

Hi.

https://docs.benerator.de/latest/introduction_to_benerator.html
This explain us that benerator can understand schema and generate data with connecting live database.

Can benerator generate 'INSERT' query with understanding static DDL?
I want to prepare test/develop data which follow any condition of schema model.

[ake2l]

Hi,

Yes, Benerator can generate 'INSERT' queries by understanding a static DDL (Data Definition Language) schema. Below is a sample configuration that demonstrates how to use Benerator to create and populate tables based on a given schema.

<setup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns="https://www.benerator.de/schema/3.0.0"
       xsi:schemaLocation="https://www.benerator.de/schema/3.0.0 https://www.benerator.de/schema/benerator-3.0.0.xsd"
       defaultDataset="US" defaultLocale="en_US">

    <!-- Define a database that will be referred to by the id 'db' later -->
    <comment>Define a database that will be referred to by the id 'db' subsequently</comment>
    <database id="db" url="jdbc:hsqldb:mem:benerator" driver="org.hsqldb.jdbcDriver" user="sa" schema="PUBLIC"
              tableFilter="db_.*"/>

    <!-- Drop existing tables if they exist -->
    <execute target="db" onError="warn">
        DROP TABLE IF EXISTS "playlist";
        DROP TABLE IF EXISTS TRACK;
        DROP TABLE IF EXISTS PLAYLIST_TRACK;
    </execute>

    <!-- Create tables based on the schema definition -->
    <execute target="db">
        CREATE TABLE "playlist" (
        PLAYLIST_ID int NOT NULL,
        name varchar(16) NOT NULL,
        PRIMARY KEY (PLAYLIST_ID)
        );

        CREATE TABLE TRACK (
        TRACK_ID int NOT NULL,
        name varchar(16) NOT NULL,
        PRIMARY KEY (TRACK_ID)
        );

        CREATE TABLE PLAYLIST_TRACK (
        PLAYLIST_ID int NOT NULL,
        TRACK_ID int NOT NULL,
        name varchar(16) NOT NULL,
        unique (PLAYLIST_ID, TRACK_ID)
        );
    </execute>

    <!-- Generate data for the 'playlist' table -->
    <generate type="playlist" count="20" consumer="db">
        <id name="PLAYLIST_ID" type="int" generator="IncrementGenerator"/>
    </generate>

    <!-- Generate data for the 'TRACK' table -->
    <generate type="TRACK" count="20" consumer="db">
        <id name="TRACK_ID" type="int" generator="IncrementGenerator"/>
    </generate>

    <!-- Generate data for the 'PLAYLIST_TRACK' table with references to 'playlist' and 'TRACK' tables -->
    <generate type="PLAYLIST_TRACK" count="20" consumer="db">
        <reference name="PLAYLIST_ID" type="int" source="db" targetType="playlist" unique="true"/>
        <reference name="TRACK_ID" type="int" source="db" targetType="TRACK" unique="true"/>
    </generate>

    <!-- Output generated data to the console -->
    <echo>Printing generated data</echo>
    <iterate type="playlist" source="db" consumer="ConsoleExporter"/>
    <iterate type="TRACK" source="db" consumer="ConsoleExporter"/>
    <iterate type="PLAYLIST_TRACK" source="db" consumer="ConsoleExporter"/>

    <!-- Verify the generated data by counting the records -->
    <echo>Verifying generated data</echo>
    <evaluate assert="result == 20" target="db">select count(*) from "playlist"</evaluate>
    <evaluate assert="result == 20" target="db">select count(*) from "TRACK"</evaluate>
    <evaluate assert="result == 20" target="db">select count(*) from "PLAYLIST_TRACK"</evaluate>

</setup>

Explanation:

1. Database Setup:

   • Define Database: The <database> tag defines a connection to the database with the ID db, specifying the URL, driver, user, schema, and table filter.
   • Drop Tables: The <execute> tags with DROP TABLE statements ensure that any existing tables are dropped before creating new ones.

2. Create Tables:

   • The second <execute> tag contains CREATE TABLE statements for playlist, TRACK, and PLAYLIST_TRACK tables.

3. Data Generation:

   • Generate Playlist Data: The <generate> tag for playlist specifies generating 20 records with the PLAYLIST_ID field auto-incremented using the IncrementGenerator.
   • Generate Track Data: Similarly, the <generate> tag for TRACK specifies generating 20 records with the TRACK_ID field auto-incremented.
   • Generate Playlist_Track Data: The <generate> tag for PLAYLIST_TRACK generates 20 records, referencing the PLAYLIST_ID and TRACK_ID from the previously generated tables, ensuring uniqueness with the unique attribute.

4. Output and Verification:

   • Print Data: The <iterate> tags with ConsoleExporter output the generated data to the console.
   • Verify Data: The <evaluate> tags run SELECT COUNT(*) queries to verify that 20 records were created in each table.

This configuration will generate and insert data into the database tables based on the schema definition provided. The generated data can then be used for testing or development purposes, ensuring it adheres to the constraints of the schema.

Feel free to reach out if you have any more questions or need further assistance!

[PorcoRosso85]

Thanks for the reply.

In your example, it looks like you are describing the DDL directly in the xml file. For example, if the directory structure was as follows

- ddl.v1.sql
- benerator_setting.xml

I am assuming that the benerator will set up the database with the data inserted using the schema defined in ddv.v1.sql without having to write the CREATE statement directly in the xml file with following...:

• generate dml file such as dml.v1_for_test.sql
• benerator directly insert test data to live database

Am I correct in my understanding?
I am looking for a tool that will set up a test database using ddl and dml files that have already been created.

[ake2l]

Yes, you are correct in your understanding. Benerator supports the creation of SQL insert scripts and can directly insert test data into a live database. By adding consumer="SQLEntityExporter" or consumer="SQLAdvancedEntityExporter" in the Benerator model, you can instruct Benerator to generate SQL scripts. If you specify the database system in the consumer attribute, Benerator uses the rd-lib-jdbacl library to create the SQL scripts internally and utilizes the JDBC driver to write the data directly into the database.

When no pageSize is defined, Benerator will insert the data one by one. However, if pageSize is defined and batching is activated in the database element or environment file, Benerator will write the data in batches, which can significantly improve performance.

[PorcoRosso85]

where is the doc that explains benerator gen SQL file?

[ake2l]

You can find the docs here , or just check in the code ...

https://docs.benerator.de/latest/component_reference.html?h=sql#sqlentityexporter