J1.5:Writing an ETL Plugin

The "J1.5" namespace is an archived namespace. This page contains information for a Joomla! version which is no longer supported. It exists only as a historical reference, it will not be improved and its content may be incomplete and/or contain broken links.

The Joomla! 1.0 to 1.5 Migrator makes use of a system built from 'ETL Plugins' to handle creating the SQL dump file. ETL Plugins are "Extraction, Transformation and Load" and have the ability to alter rows on the fly as they are transferred, rename or remove fields and even generate content from other sources such as on disk configuration files. Whilst an ETL plugin can do all of this, if the data does not need modification an ETL plugin can still be used.

The advantage of using an ETL plugin is that you may include your tables with the rest of a Joomla! 1.0 site's migration process. This means that the user doesn't have to back up their tables individually, it occurs with the rest of the migration process. All that need happen is that the user installs the extension again and their data remains in tact.

Even if you aren't the developer of the extension, this guide will walk you through how to create a migration plugin for the extension using two different methods.

Step 1: The Basics

The basic ETL plugin hasn't changed much since August 2007. All one normally needs to do is fill in the blanks. For each table you want to transfer you need an ETL plugin file. Files are labelled "tablename.php" and are stored in the "plugins" directory of the migrator. A basic template of the minimum a plugin might need is demonstrated below:

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

So replace "TableName" with the name of your table and you've got the start. However the table won't exist in the new 1.5 site so we're going to have to create it first. The hook to do this is "getSQLPrologue" which gets executed prior to a transformation.

Step 2: Creating the table

As noted before we're going to need to create the table to put the data into as a part of the migration process. Ordinarily the installer creates tables for itself which are used for migration but for third party extensions this can't happen. So we need to work out how to get the SQL create statement and output it in a "getSQLPrologue" call. There are two relatively methods of determining

The first method involves taking an installation package and examining the installation SQL file to build an ETL plugin that just transfers the data. The second method involves using tools like MySQL Query Browser to identify and create an ETL plugin.

Installation package

TODO: Complete this section

MySQL Query Browser

Open up MySQL Query Browser and point it towards your Joomla! 1.5 install. First left click to select the table and then right click on table and select "Copy SQL to clipboard". Navigate to your ETL plugin and add the following code to the class:

function getSQLPrologue() { 
   return ""; 
}

Between the two quotes, paste the copied SQL, it'll end up looking like the following:

function getSQLPrologue() {
   return "CREATE TABLE  `joomla_svn_15`.`jos_banner` (
  `bid` int(11) NOT NULL auto_increment,
  `cid` int(11) NOT NULL default '0',
  `type` varchar(90) NOT NULL default 'banner',
  `name` text NOT NULL,
  `alias` varchar(255) NOT NULL default '',
  `imptotal` int(11) NOT NULL default '0',
  `impmade` int(11) NOT NULL default '0',
  `clicks` int(11) NOT NULL default '0',
  `imageurl` varchar(100) NOT NULL default '',
  `clickurl` varchar(200) NOT NULL default '',
  `date` datetime default NULL,
  `showBanner` tinyint(1) NOT NULL default '0',
  `checked_out` tinyint(1) NOT NULL default '0',
  `checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `editor` varchar(150) default NULL,
  `custombannercode` text,
  `catid` int(10) unsigned NOT NULL default '0',
  `description` text NOT NULL,
  `sticky` tinyint(1) unsigned NOT NULL default '0',
  `ordering` int(11) NOT NULL default '0',
  `publish_up` datetime NOT NULL default '0000-00-00 00:00:00',
  `publish_down` datetime NOT NULL default '0000-00-00 00:00:00',
  `tags` text NOT NULL,
  `params` text NOT NULL,
  PRIMARY KEY  (`bid`),
  KEY `viewbanner` (`showBanner`),
  KEY `idx_banner_catid` (`catid`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8";

}

You're going to need to remove the table name (e.g. the "`joomla_15_svn`." part) and replace the table prefix (e.g. "jos_") with "#__". You should also remove the part at the bottom after the final brace until the double quote. Using the above example, we end up with an ETL plugin much like the following:

class Banner_ETL extends ETLPlugin {
      function getName() { return "Banner ETL Plugin"; }
      function getAssociatedTable() { return 'banner'; }
      function getSQLPrologue() {
         return "CREATE TABLE  `#__banner` (
              `bid` int(11) NOT NULL auto_increment,
              `cid` int(11) NOT NULL default '0',
              `type` varchar(90) NOT NULL default 'banner',
              `name` text NOT NULL,
              `alias` varchar(255) NOT NULL default '',
              `imptotal` int(11) NOT NULL default '0',
              `impmade` int(11) NOT NULL default '0',
              `clicks` int(11) NOT NULL default '0',
              `imageurl` varchar(100) NOT NULL default '',
              `clickurl` varchar(200) NOT NULL default '',
              `date` datetime default NULL,
              `showBanner` tinyint(1) NOT NULL default '0',
              `checked_out` tinyint(1) NOT NULL default '0',
              `checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
              `editor` varchar(150) default NULL,
              `custombannercode` text,
              `catid` int(10) unsigned NOT NULL default '0',
              `description` text NOT NULL,
              `sticky` tinyint(1) unsigned NOT NULL default '0',
              `ordering` int(11) NOT NULL default '0',
              `publish_up` datetime NOT NULL default '0000-00-00 00:00:00',
              `publish_down` datetime NOT NULL default '0000-00-00 00:00:00',
              `tags` text NOT NULL,
              `params` text NOT NULL,
              PRIMARY KEY  (`bid`),
              KEY `viewbanner` (`showBanner`),
              KEY `idx_banner_catid` (`catid`)
            );";
      }

}

I've added some extra indenting here but this is enough to get started. Replace "jos_banner" with the table you're after and you've got yourself a completed ETL plugin. The migrator will take care of the rest of the problems such as generating the SQL insert statements from the table and writing it to the file. To install and test this extension simply name the file 'banner.php' and put it into the "plugins" directory in the migrator (/administrator/components/com_migrator/plugins).

Developer Notes

When installing the plugin into