Friday, August 13, 2010

MySQL Java Driven Metadata changes

When changes are made to an existing project very often database metadata needs to be changed as well.

Metadata migration scripts are useful SQL bits that are run to ensure a new version of the program will work as expected.

Data migration scripts are needed to prepopulate new or existing tables with predefined values.

Rollback scripts are SQL bits to be run in case the whole deployment goes wrong. They commonly affect metadata in a reverse way when compared to Migration scripts. If database changes are actually backward compatible (they do not break previous deployed program functionality) then there is no need for sql bits inside the rollback script, but still there is a rollback script which happens to do nothing.

The whole purpose of this post is to document what should be done but it is also a starting point to provide some kind of automation in the future.

At the time of this writting Reverse and forward database engineering in MySQL can be done using MySQL Workbench however you need to purchase the standard edition of the program (Features are disabled or not present at all in the Community edition). Still mysqldump comes to your rescue and as it is a command prompt tool the chances for automation are big.

Here are instructions to follow to provide migration/rollback scripts when you use JPA:

1. Create the tables from Java. You will probably need to uncomment the below code from test persistence.xml and run Unit tests. In a real world scenario you will need to comment and uncomment several files as you might be using for example an in memory database like HSQL for your JUnit tests. After tables are created you should uncoment the code to avoid undesired data wipe out during your tests.

#vi src/test/resources/META-INF/persistence.xml
<!--<property name="" value="create" />-->

2. Create variable DB_BASE and DB

3. Get a local copy of the previously released application. You do that examining the "svn log" output and downloading directly from the tag while getting from there the version number:
svn log$DB
r8509 | deploymentUser | 2010-08-11 14:42:40 -0400 (Wed, 11 Aug 2010) | 1 line

[maven-release-plugin] copy for tag my-lib.1.0.0

4. Create DB_VERSION and RELEASE_VERSION variables

5. Download the latest released metadata. Your local file will be something like r8509.myDB.sql.

6. Get the current metadata
mysqldump --no-data -u root -proot $DB_BASE > $DB

7. compare the files:

8. Use the results to create migration_metadata.sql which will contain the sql bits needed to get from $DB_VERSION.$DB to $DB

9. Create migrations_data.sql containing all new data you want to push into the database. Again even if there is nothing to be included make sure you have the file with empty content. Additionally be sure you create data.sql which is the equivalent of $DB but just to populate data.

10. Locally test that creating the DB by hand using previous released $DB and later applying migration_metadata.sql plus migration_data.sql everything goes fine.

10.1. Recreate local DB
mysql> drop database myDB;
mysql> create database myDB;

10.2. Be sure your variables are set. In our case:
$ DB_VERSION=r8509
$ DB=$DB_BASE.sql

10.3. Get the tagged sql script

10.4. Run the below to test the first three scripts:
$ mysql -uroot -proot myDB < r8509.myDB.sql
$ mysql -u root -proot myDB < myDB_migration_metadata.sql
$ mysql -u root -proot myDB < myDB_data.sql

10.5. Do a change to data that will be affected by the migration_data.sql script
mysql> use myDB
mysql> select * from office;
mysql> delete from office where name = 'Miami';

10.6. Run the forth script to verify it will insert back the deleted record(s)
$ mysql -u root -proot myDB < myDB_migration_data.sql
mysql> select * from office;

11. Include the migration*.sql, data.sql, $DB.sql and rollback.sql in your resources/db folder so they get tagged with the next release. If no need for rollback.sql / migration_data.sql still create / update with blank content.

12. Optional: You can use MySQL Workbench to graphically compare the metadata from previous and current relelase or in fact between any releases. This helps to check tables, fields and relationships are generated as expected.

13. Open MySQL Workbench 5.2.26 or above and select “Model | Create a diagram out from Catalog Objects”. Organize your tables and print to pdf using the naming convention $RELEASE.$DB_VERSION.pdf. Publish them on a WIKI location so the whole team can see the underlying model.

14. Release the application.

15. Deploy the application in Staging: Run migration*.sql scripts. Test.

16. As above for production.


Deryl Spielman said...

I guess the $DB.sql has all previous release's schema changes? Otherwise automation must be in place to run all previous schema changes starting from the base. Another approach is to have one folder with the base file and each schema file with a convention of something like ${id}.${majorReleaseNumber}.${minorReleaseNumber}.${pointReleaseNumber}.sql. Then all of the files can be run in order.

Nestor Urquiza said...

To create the DB from scratch is as easy as running the latest from SVN $DB.sql and data.sql
To get any release from scratch it is as easy as running $DB.sql and data.sql from a tag.
To migrate to a given release number from an existing release is as easy as running all migration_metadata.sql and migration_data.sql from tags starting at current release version + 1
To rollback to any previous release number it is as easy as running rollback.sql starting at the current release tag all the way down to target release – 1. Of course some data will be lost.