databene

 
  • Increase font size
  • Default font size
  • Decrease font size

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 eight major databases:

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

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'.

<execute uri="{${database}/drop_tables.${database}.sql}" target="db" onError="ignore"/>
<execute uri="{${database}/create_tables.${database}.sql}" target="db" optimize="true"/>

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

<iterate 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 <generate> example (the last one of the file and the most sophisticated):

<!-- create order items -->
<generate type="db_order_item"
        count="{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" />
    <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" />

</generate>

The attribute count ="{customer_count * orders_per_customer * items_per_order}" tells benerator to use a script expression (in Benerator Script) for calculating the number of entities to create.

The id is generated by a default id generator that generates the values 1, 2, 3, ...

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 ' 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.