Thursday 19 January 2012

Simple MySQL reporting database

We have created an XML based repository of clinical trials study data.

To report upon this we create an isomorphic MySQL database using HyperJAXB. Our main configuration of hyperjaxb is to use an information preserving naming convention plugin.

The builds are under Continuous Integration. The database is created during the course of the Maven build, after this views are created and the database dumped.

mysqldump chassisDb  --complete-insert --skip-opt --add-drop-table  > $file

The dump file is then copied to an Amazon Web Services T1 instance, which only has MySQL installed. Two users exist: reader and uploader.

These two users have usernames and passwords configured in .my.cnf in their home directories containing

[client]
user=user
password=password

uploader has a cron job configured to update the database from any uploaded files:

mysql chassisDb < chassisDb_20120119.sql 

The database can now be accessed using the Pentaho reporting tool.

No comments:

Post a Comment