Migration is one of the more continual banes of my life and its one of the more interesting problems. Migration from Joomla! 1.0 to 1.5 combined many many changes. Not only did we alter the way the system worked in places in a somewhat drastic way incidentally removing features but we forced everyone for the first time to consider their encoding properly. A lot of sites were using the default latin1 encoding, even if they weren’t actually a Latin 1 language. Some sites used Latin 1 but some how in their database was actually UTF-8 characters every so often.

When I sit here and consider this, I realise that we really did make the right decision to almost force people to create new databases and new tables that were in UTF-8. It means that all of this ‘fun’ with encodings won’t happen unless we move from UTF-8 to UTF-16 or something similar in the future. Whilst its an incredible amount of pain right now, once we’re over this hump it is for the most part plain sailing.

The other interesting aspect of the process is the design of the migrator itself. When David Gal released the first migrator, it was basically a SQL dumping tool that was specifically designed to work with only the core tables that shipped with Joomla1 and to move those aross into the new site via an SQL dump. The design of the migrator that I built took a similar approach of making an SQL file that could be used to install items but it also addressed the concern that third party developers couldn’t extend the original version. My version of the migrator was heavily based around the concept of an ‘ETLPlugin’. An ETL plugin was a simple PHP class that at the bare minimum provided a descriptive display name, the name of the table in question and that was it. Presuming you had no changes in your database, writing a plugin was quite simply four lines of code:

class TableName_ETL extends ETLPlugin {
      function getName() { return "TableName ETL Plugin"; }
      function getAssociatedTable() { return 'tablename'; }
}

Thats it, thats an ETL plugin. The migrator has code that will find the columns in the table and build the appropriate SQL statement to get it into the system. You can add stuff like “getSQLPrologue” to prepend stuff before your rows such a “CREATE TABLE” statement.

As far as I can tell, the only vendor who actually made an ETL Plugin was DocMan. After complaining that they didn’t have a method of extending the tool to support themselves, they didn’t even bother to use it. Documentation on ETL plugins is in my Migrator RC1 OpenOffice.org (ODF ODP) presentation that was dated August 2007, that is around 5 months before we shipped 1.5 – and even now one year later I am yet to see any more plugins. Doing a Google search on “joomla 1.5 etl plugin” reveals links to the official documentation and copies there of, one link to the ODP file mentioned above with “Developing ETL plugins” in Google’s abstract and forum posts for Azrul, CommunityBuilder and a Real Estate component all mention “manually” migrating the users data from the database stating they don’t need an ETL plugin. For some reason they wish to make their users lives harder by forcing them to go outside of the system that I put together to make their lives easier. They could write an ETL plugin really easily and not have to worry about doing anything but shifting files manually (something mostly easily achieved with most file browser/FTP tools).

At the end of the day its sad to spend time building a system that will handle their needs almost automatically but still deliver flexibility if they wanted it and then have it not used and have them say to use a manual migration anyway. Perhaps I’ll write a post on how to make an ETL plugin (again).