Overview:
DesknowSqlConvertor is a utility that I used to migrate the content of my MySQL DeskNow database to a PostgreSQL database, while maintaining all emails, users, and other preferences.
Background:
This tool was developed to solve a rather ugly situation, after I tried a number of open source and commercial database migration utilities without success.
Originally, this database was using MySQL's MyISAM backend only. Of course after a few revisions of DeskNow software, it was finally realized that foreign key integrity and transactions are important, and converted the database to use the InnoDB backend. Of course it doesn't appear that the DeskNow foreign keys ever 'stuck', so after a few years of use there were quite a few entries in the various tables that were "orphaned" so to speak - they should have been caught by 'ON DELETE CASCADE' clauses... but never were. There were also more tables in the database than in a "clean" patched DeskNow installation - the result of many patches to various beta versions, most likely.
The task at hand was simple: to convert the database to PostgreSQL, and clean up the "cruft" in the process.
Technical Summary:
DesknowSqlConvertor is a command-line application written in Java. It uses standard JDBC Drivers to connect to both the source and destination databases, and then proceeds to copy the contents of each table that it knows about from the source to the target database.
Technical Details:
This tool was written with the primary goal of accomplishing one task - copying a specific DeskNow database from MySQL to PostgreSQL. In theory, this tool can be used to copy data from _any_ JDBC source to _any_ JDBC destination, regardless of the RDBMS being used (Microsoft SQL Server, Oracle, etc.) however in practice it will need to be modified before it will successfully convert any version of DeskNow other than 3.1.11, and/or in any direction other than MySQL 5.x to PostgreSQL 8.x.
Currently, the basic architecture is as follows:
- The ISettingsProvider interface, of which one implementation was written - "HardcodedSettingsProvider", a simple class that merely has all of the source/destination database configuration information hard-coded.
- IDesknowJdbcProvider interface, of which there are two implementations written: PostgresJdbcProvider and MysqlJdbcProvider. They both derive from a base class AbstractJdbcProvider which provides a few helper methods.
- SqlConvertor, the entrypoint to the program.
- TableNames class, which is my hard-coded version of the tables to copy, _in the order they need to be in where applicable_ for the foreign key references to work.
Basic Usage:
Some knowledge of Java as well as SQL will make your life much easier. Remember, this tool was tested only for converting from MySQL 5 to PostgreSQL 8 with a DeskNow 3.1.11 database.
First, download the tarball of the java source:
desknowsqlconvertor-0.1.tar.gz
Next, go ahead and take a look around. Make sure to pay particular attention to the values in HardcodedSettingsProvider.java, as they will almost undoubtedly have to be modified. When you're all done, go ahead and compile the program.
You will need to obtain the JDBC driver jar files for both the source and destination database, which you should be able to get from the database's website. Make sure they are accessible in your java classpath. so the program can find them.
Now use the DeskNow-supplied create script from the "sql" subfolder of the "desknow_server.zip" on your target database to create the basic schema. Now you need to patch up the database - you can either modify your existing server's $DESKNOWDATA/cfg/Database.cfg, or use another copy of the DeskNow webapp to do this if you don't want to take your current system down. All you need to do is fire the application server up once pointed at the new database, and the startup code should handle applying all the new patches.
Once your target database is fully patched up, stop the application server again. At this point, you also need to make sure that your existing installation's database wont' be modified while you are copying the data, so you will need to stop your old "real" application server as well.
Now, just run the application, and let it begin copying all of your data. If it encounters any problems, it will stop and dump the SQL error messages to the console, so you can hopefully have some idea of what you have to fix. In my case, I had to remove a bunch of rows that were orphaned due to earlier lack of foreign key constraints on the old database (the new database would die out whenever I tried to insert any of the bad data.) In most cases, this step will not be necessary.
Todo / Future:
Depending on requests / feedback, the following things seem like they may be worth doing sometime in the future, in order from less estimated work to more estimated work:
- Write other ISettingsProvider implementations to take command like argument configuration and configuration information from a file.
- Finish writing the MysqlJdbcProvider so that it could act as a target, as well as implementing other JDBC provider classes for other databases.
- Dynamically determine the tables that need to be copied. This is actually quite easy. The harder part is figuring out the order that the tables need to be copied in to ensure that foreign key constraints are not violated.
- Handle restarting of the program with "continue where left off" mode. Currently the program starts from the beginning in every case, and in fact will encounter more errors if the target database isn't cleaned out because it will insert duplicate rows (and most likely cause primary key duplication errors to be thrown).
License:
Released under the BSD License.
Version History:
9/29/06: Initial public release of 0.1
Contact:
Any experiences, suggestions, etc.: E-mail marcus at gangus dot com.