Tuesday, January 27, 2009

Mysql database welcomes its Subversion overlord

This post will describe how I versioned a Mysql database using Subversion(SVN). The database schema is an important part of the application and should be versioned just like your source code.

1. Setup SVN

In SVN create a `Databases` folder with a sub-folder for each database you wish to add to SVN. The Subversion client I use for this project is RapidSVN.


2. Add db_version table to databases

We will need to add a table to each database so we know what version of the database we are currently working with. This table will also serve as a log to track what schema changes have been made to the database.
create table db_version (
`id` int auto_increment,
`majorReleaseNumber` int,
`minorReleaseNumber` int,
`pointReleaseNumber` int,
`scriptName` varchar(50),
`dateApplied` datetime,
PRIMARY KEY(`id`)
);


majorReleaseNumber - Major releases are significant changes to the database.
minorReleaseNumber - Minor releases are enhancements to the database that do not necessitate a major release.
pointReleaseNumber - A point release is usually a simple bug fix.
scriptName - The name of sql script that made the schema changes.
dateApplied - When the script was run on this database.


3. Create baseline scripts

I used mysqldump to generate a create script for existing databases. Be sure to include the --no-data option. SVN is used to keep track of the scripts that make schema changes to the database and is not intended to be used as backup tool for data of a particular instance of the application.
$ mysqldump -h localhost -u root -p db_1 --no-data > db_1.1.0.0.sql

The name of the sql script should contain the name of the database and the version of the database the script applies to.

db_1.1.0.0.sql

At the end of the script be sure to add an insert statement for the db_version table.

4. Changing database schema

When you have a major, minor, or point release change to the database, the change script should be tested and then uploaded to the database folder in SVN. It is a good idea to backup the database before applying a change script. At the end of the change script should be an insert statement for the db_version table.

5. Sync database with SVN version

I am currently creating a small java application to query the SVN repository and compare it to what is in the db_version table. Then prompt the user to upgrade the database to a selected version. Below is an example out of how I see the application working.
$ dbsync check db_1
2 database versions avialiable
1.2.1
1.3.0

$ dbsync upgrade db_1
1.2.1 script applied
1.3.0 script applied
db_1 upgrade to 1.3.0 successful

Or if you only want to upgrade to a certain version
$ dbsync upgrade db_1 version 1.2.1
1.2.1 script applied
db_1 upgrade to 1.2.1 successful

I found a nice Java SVN api SVNKit that I am planning on using to create the dbsync application. I will have a future post about this when I am finished with the application.

Tuesday, January 13, 2009

Posting source code in blog

One of the main reasons I started my blog was for documentation. With documentation comes posting source code, but I had a hard time finding an easy way I could post code with spacing and syntax highlighting. I found syntaxhighlighter written in JavaScript that I could add to the existing blogger template.

public class HelloWorld()
public static void main(String [] args) {
System.out.println("hello world!");
}
}

I added all of the SyntaxHighlighter.css to the head section of the blogger template. Then under that I add links to the JavaScript files I needed.





The last step is to add the following at the end of the template before the end of the body tag.



All the following around the code to post.