database population tutorial

A shop demo shows how to fill database schemas based on a setup file.

The shop database schema is as follows:

You can find it in the benerator distribution . It supports seven major databases:

Oracle (oracle)
DB2 (db2)
MS SQL Server (sql_server)
MySQL (mysql)
PostgreSQL (postgres)
HSQL (sql)
Derby (derby)

If you have a look at the directory demo/shop of you distribution, you will find

the main file shop.ben.xml and sub directories named with the database identifiers above (e.g. postgres for PostgreSQL).
the files shop.stage .properties files, e.g. shop.development.properties and shop.perftest.properties
sub directories like oracle with three files each:
- shop.database .properties with the setup variables specific for this database
- create_tables.database .sql
- drop_tables.database .sql

Running the demo

Time to get going:

Choose a database for your first steps, e.g. oracle
Install benerator, the database and an appropriate jdbc driver
start the database and create or select a schema

edit the file BENERATOR_HOME/demo/shop/oracle/shop.oracle.properties and set the correct database url, driver, schema, user and password:

 db_uri=jdbc:oracle:thin:@localhost:1521:XE
 db_driver=oracle.jdbc.driver.OracleDriver
 db_user=benerator
 db_password=benerator
 db_schema=benerator
 id_strategy=seqhilo
 id_param=seq_id_gen

now go to the root directory of your benerator installation and run (from the command line):
```
Windows:
  set BENERATOR_OPTS=-Dstage=development -Ddatabase=hsql
  bin\benerator demo\shop\shop.ben.xml
*ix:
  export BENERATOR_OPTS=-Dstage=development -Ddatabase=hsql
  bin/benerator demo/shop/shop.ben.xml
```
If you've done everything right, benerator will now populate your schema with some entities.

If you want to test performance, you can use the perftest stage, editing the file shop.perftest.properties and starting benerator with -Dstage=perftest. In the shop.perftest.properties you can adapt the settings:
```
 product_count=5000
 customer_count=10000
 orders_per_customer=3
 items_per_order=3
```

If you have a look at the file shop.ben.xml, you see how the pieces are put together:

First, the -Dstage and -Ddatabase parameters are evcaluates for importing the corresponding properties files:

<include uri="{demo/shop/${database}/shop.${database}.properties}" />
<include uri="{demo/shop/shop.${stage}.properties}" />

The following echo elements print some settings to the console, e.g.

<echo message="{  ${product_count + 6} products}" />

After that, the database is defined with the id 'db'.

A Task class is instantiated by JavaBean mechanisms and executed, running an SQL script on the database 'db'.

<run-task class="org.databene.platform.db.RunSqlScriptTask">
        <property name="uri" value="{demo/shop/${database}/create_tables.${database}.sql}" />
        <property name="db" ref="db" />
</run-task>

A DbUnit file is used for creating a basic predefined setup that may serve for unit tests and regression tests:

<create-entities source="demo/shop/shop.dbunit.xml" consumer="db" />

Let's skip the following examples, you have got the idea from the flat file generation sample .

Just one more create-entities example (the last one of the file and the most sophisticated):

<!-- create order items -->
<create-entities name="db_order_item"
        count="{ftl:${customer_count * orders_per_customer * items_per_order}}" 
        consumer="db">
    <variable name="product" source="db" selector="select ean_code, price from db_product" 
        distribution="cumulated" />
    <id name="id" strategy="{${id_strategy}}" source="db" param="{${id_param}}" />
    <attribute name="number_of_items" min="1" max="27" distribution="cumulated" />
    <attribute name="order_id" source="db" selector="select id from db_order" cyclic="true" />
    <attribute name="product_ean_code" script="{${product[0]}}" />
    <attribute name="total_price" 
        script="{${(product[1] * db_order_item.number_of_items)?c}}" />
</create-entities>

The attribute count ="{ftl:${customer_count * orders_per_customer * items_per_order}}" tells benerator to use a scripting engine (FreeMarker, registered as 'ftl') for calculating the number of entities to create.

The id is generated by one of the predefined strategies by $id_strategy and $id_param ), parameters which are set in the database properties file.

A variable product is used for querying the db_product database table, extracting ean_code and price from all rows, buffering them in memory and providing them with a probability distribution of bell shape: The middle products will be used frequently, the first and last products just rarely

The attribute total_price is determined by evaluating a script '$(product[1] * db_order_item.number_of_items)?c ' with the default script engine. This multiplies column 1 of the 'product' variable (the price column) with the number_of_items attribute of the current entity and renders it for a 'computer audience' because of '?c'. Otherwise, you might get locale.dependent formatting, which would confuse benerator.

databene