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.

See also:

• jurgentje's guide to writing an ETL plugin
• Migrator ETL Plugin section of the JED
• JoomlaTools Migrator Plugin Howto

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.

Getting the SQL CREATE TABLE statement

Before we can continue we need to obtain an SQL CREATE TABLE statement to put in our "getSQLPrologue" call. This SQL statement is unique to each table and so cannot be easily replicated.

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

MySQL Query Browser

Open up MySQL Query Browser and point it towards your Joomla! 1.0 install. First left click to select the table and then right click on table and select "Copy SQL to clipboard".

phpMyAdmin

If you're running on a shared hosting environment its possible you can't use MySQL Query Browser to connect to your database. Typically however you will have access to phpMyAdmin on shared hosting environments.

Using phpMyAdmin navigate to the table you are interested in. Select the "Export" tab and then ensure that "SQL" is the export mode, "Structure" is ticked with the backquotes option enabled and the auto_increment option disabled, untick "Data" and ensure the compression is "none" and "save to file" isn't ticked. Then click "Go" and the screen should reload with a text field. Copy the "CREATE TABLE" aspect down.

Installation package

In your Joomla! 1.0 site navigate to the administrator/components folder and locate the component you are interested in migrating. Within this folder should be an XML file that was used to install the extension. Scrolling through the file you should find an "install" section much like the following:

                <queries>
                        <query>
CREATE TABLE #__migrator_tasks (
`taskid` INT(11) NOT NULL AUTO_INCREMENT,
`tablename` VARCHAR(50) NOT NULL,
`start` INT(11) NOT NULL,
`amount` INT(11) NOT NULL,
`total` INT(11) NOT NULL,
PRIMARY KEY(`taskid`)
)
                        </query>
                </queries>
        </install>

As you can see there is an SQL CREATE TABLE statement here similar to what phpMyAdmin displayed. Each query represents a new table and there is one ETL plugin per table. For each of the "CREATE TABLE" queries you find in the XML file you will need a new ETL plugin. In this case the table is "migrator_tasks" and would be called "migrator_tasks.php" with the class name "class Migrator_Tasks_ETL".

Putting it together

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 (removing the engine, auto increment and default charset options). 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

If you can do so, make sure that your 1.5 version of the extension (legacy or otherwise) uses the"CREATE TABLE IF NOT EXISTS" syntax. This way if you've got a fresh install it will still work but if you've got a migrated instance the tables won't be created.