Playing with a DBMonster

I recently had the privilege of using DBMonster when I needed to generate some random test data for a MySQL database. What I am about to demonstrate is a simple use of the DBMonster tool.

The SQL statement for the simple table used in this writeup is as follows (of course you will have to create a database first):

CREATE TABLE person (
id int not null auto_increment,
last_name varchar(45) not null,
first_name varchar(45) not null,
gender varchar(6) not null,
date_of_birth date not null,
PRIMARY KEY (id)
);

Note that, the SQL statement is for MySQL only although if you remove the auto_increment statement it should work for your database.

Now that we are all set we can start to use DBMonster. The simplest way to start is by using DBMonster to reverse engineer the database table and generate a schema for itself. Then you can tweak the schema to your satisfaction.

To start (you will need access to the JDBC Driver for your database):
a) Download DBMonster and unzip. I will call this directory "<DBMonster_Home>".
b) In the <DBMonster_Home> directory, create a file call "dbmonster.properties". In this file insert the following (each on a separate line):
dbmonster.jdbc.driver=com.mysql.jdbc.Driver
dbmonster.jdbc.url=jdbc:mysql://localhost:3306/<database_name>
dbmonster.jdbc.username=<username>
dbmonster.jdbc.password=<password>
c) When this is done, launch the command prompt and navigate to the <DBMonster_Home> directory, at the command prompt type the following (without the inverted commas):
"java -classpath ../mysql-connector-java-5.1.6-bin.jar;dbmonster-core-1.0.3.jar pl.kernelpanic.dbmonster.Launcher -c dbmonster.properties --grab -o dbmonster-schema.xml"
d) A Schema file called "dbmonster-schema.xml" should have been generated for you. We will be making some changes to this file.

Note: Do not close the command prompt we will be reusing it later

The changes we will be making in the dbmonster-schema.xml file are:
a) We will reduce the number of rows to 50 unless you have the time to wait for 1000 entries
b) For the last_name and first_name fields, spaces should not be allowed. We will change that.
c) It makes no sense for gender to be anything other than Male or Female. Fortunately, DBMonster has a DictionaryGenerator we can use for this.

To proceed,
a) Open the dbmonster-schema.xml file in your favorite XML editor.
b) Look for <table name="person" rows="1000"> and change to <table name="person" rows="50">
c) Also look for <column name="last_name" databaseDefault="false">. Within this tag, there should be a <property name="allowSpaces" value="true"/> change that to <property name="allowSpaces" value="false"/>. Repeat this for the <column name="first_name" databaseDefault="false"> tag.
d) Create a file called "gender_dictionary.txt" in the <DBMonster_Home> directory and put the following in it (each on a separate line)
Male
Female
e) In the dbmonster-schema.xml file, replace the entire <column name="gender" databaseDefault="false"> tag withe the following:
<column name="gender" databaseDefault="false">
<generator type="pl.kernelpanic.dbmonster.generator.DictionaryGenerator">
<property name="unique" value="false"/>
<property name="dictFile" value="gender_dictionary.txt"/>
</generator>
</column>

We have finished with the dbmonster-schema.xml file. To generate the test data run the following at the command prompt (without the inverted commas):
"java -classpath ../mysql-connector-java-5.1.6-bin.jar;dbmonster-core-1.0.3.jar pl.kernelpanic.dbmonster.Launcher -s dbmonster-schema.xml -c dbmonster.properties"

Do not hesitate to visit the DBMonster Homepage to learn more.

Enjoy.