General

Creating an upgradable Mambo Component

Mambo and Joomla don’t currently seem to have any standards for creating a component that can upgrade.
Generally, when a component is installed, it creates the tables it requires and when it is uninstalled it removes them.
However at some point the developers introduced the com_install() and com_uninstall() functions which can be used instead of putting sql in the XML component file.
The component I am writting is a component for uploading themes, basically stores an image and a file. so where “theme” occurs in classes and table names, it is referring to my component, adapt these to suit your own.
The installation / upgrade of tables is quite easy, I created a ‘theme_version’ table which simply has one field and one record that stores the version number.  This is used to track the version of the tables installed.
If this table does not exist, then it is assumed to be a new install of the component and the rest of th etables are created and populated.  Note the first thing you should do is create the theme_version table and add a single record with a value of 1 to denote the first version of the tables.
If the table does exist, we get the value of the version field and then simply add a switch statement to perform the alter / update sql dependant on the version value.  If you ensure the case statements are in numeric order and don’t bother with break; statements, this system can even upgrade version 1 to version 5 for example.
The ony problem now is what happens on uninstall.  Generally in the uninstall script you would put all the drop table sql to remove the component’s tables.  However, if a user is uninstalling to upgrade, the last thing we want is to remove the tables.
To get round this, I added a boolean field to my component’s configuration table which when set to Yes, will remove the tables.  As a default this field is set to No, so to actually uninstall the component fully, including deleting the tables, the user must first goto the configuration screen, save the configuration with “uninstall tables” set and then uninstall the component.
Personally, I don’t think this is a problem, as if the user forgets, they can just re-install the component, change the config and then uninstall to ensure the whole component is removed.
This assumes you have a configuration section on your component.
The code below mentions themes, this is the component  I was developing. Change the theme to the relevant name for you
component.
Step 1) Create the uninstallTables option.
Add a new field to the configuration table called uninstallTables, this should be set with a default value of either 0 or 1,
depending on whether or not you want to uninstall the tables as default.  Personally, I default it to 0.
e.g.
`uninstallTables` TINYINT(1) NOT NULL
Add the new field to the extended mosDBTable class for the configuration table if used.
Modify the edit configuation html to add a Yes/ No option for the new field.
e.g.

            <tr>
                <td>Uninstall Tables ?</td>
                <td>
                <? echo mosHTML::yesnoSelectList( "uninstallTables", "", $row->uninstallTables ); ?>
                </td>
                <td>By default, the Themes component does not remove tables it has created so they can be upgraded.  If you

want to completely remove this component set this option to 'yes' and save the configuration before uninstalling</td>
            </tr>

You should now be able to modify this field, set it to yes or no and have it persisted in the table.
Step 2) com_install() function
Move you table creations from the XML to the com_install function and add the theme_version table and the install / updgrad
condition e.g.

<?php
global $mainframe;
require_once( $mainframe->getPath( 'class' ) );

function com_install() {
    global $database;

    $table_prefix = $database->_table_prefix;  // get the mos_ table prefix

    $tableList = $database->getTableList(); // get a list of the tables
    if (in_array("{$table_prefix}themes_version",$tableList))  // if the version table exists
    {
        // get the current version
        $SQL = "SELECT  * FROM #__themes_version LIMIT 1";
        $database->setQuery($SQL);
        $rows = $database -> loadObjectList();
        if ($database -> getErrorNum()) {
            echo $database -> stderr();
            return false;
        }
        $row = $rows[0];

        $tableVersion = intval($row->version);  // get the current table version

        switch($tableVersion)
        {
            case 1 :
                // run alter statements to upgrade next version
                ...

                // set the new version
                $SQL = "UPDATE #__themes_version SET `version`=2";
                $database->setQuery($SQL);
                $database->query() or die( $database->stderr() );

        // note we don't have a break here so the V2 - V3 upgrade below will get run as well
            case 2 :
                // run alter statements to upgrade next version
                ...

                // set the new version
                $SQL = "UPDATE #__themes_version SET `version`=3";
                $database->setQuery($SQL);
                $database->query() or die( $database->stderr() );
            break;
        }

    }
    else
    {
        // Themes_Version does not exist so create the tables

        // Themes_version table
        $SQL = "DROP TABLE IF EXISTS #__themes_version";
        $database->setQuery($SQL);
        $database->query() or die( $database->stderr() );

        $SQL = "CREATE TABLE #__themes_version (";
        $SQL = $SQL." version int ";
        $SQL = $SQL." ) TYPE=MyISAM;";
        $database->setQuery($SQL);

   $database->query() or die( $database->stderr() );

        $SQL = "INSERT INTO #__themes_version (version) values(3);";  // change the value here to the table version each time

you release
        $database->setQuery($SQL);
        $database->query() or die( $database->stderr() );

        // themes configuration table
        $SQL = "DROP TABLE IF EXISTS #__themes_config";
        $database->setQuery($SQL);
        $database->query() or die( $database->stderr() );

        $SQL = "CREATE TABLE #__themes_config (";
        $SQL = $SQL." `configid` TINYINT(4) NOT NULL,";
        $SQL = $SQL." `themePath` char(240) NOT NULL,";
        $SQL = $SQL." `uninstallTables` TINYINT(1) NOT NULL";  // this is the uninstall Tables field
        $SQL = $SQL." ) TYPE=MyISAM;";
        $database->setQuery($SQL);
        $database->query() or die( $database->stderr() );

        $SQL = "INSERT INTO `#__themes_config`";
        $SQL = $SQL."(configid, themePath, uninstallTables) values(1, '/themes',0);";  // unistallTables is initialised to 0
        $database->setQuery($SQL);
        $database->query() or die( $database->stderr() );

    // create other tables
    ...

    }

}
?>

Step 3) com_uninstall() function
The uninstall is nice and simple, check the uninstallTables field in the configuration then uninstall if set

<?
global $mainframe;
global $mosConfig_absolute_path;
require_once( $mainframe->getPath( 'class' ) );

function getConfig()
{
    // this function gets the row from the config table
    global $database;

    $database->setQuery("SELECT * FROM #__themes_config LIMIT 1");
    $rows = $database -> loadObjectList();
    if ($database -> getErrorNum()) {
        echo $database -> stderr();
        return false;
    }

    return $rows[0];
}

function com_uninstall() {
    global $database;

    $config = getConfig();

    if ($config->uninstallTables == 1)
    {

        $SQL = "DROP TABLE IF EXISTS #__themes_version";
        $database->setQuery($SQL);
        $database->query() or die( $database->stderr() );

        $SQL = "DROP TABLE IF EXISTS #__themes_config";
        $database->setQuery($SQL);
        $database->query() or die( $database->stderr() );

    // drop other tables
    ...
    }
}
?>

If there are any mambo / joomla developers reading this then it would be nice for something similar to this to be integrated into the CMS, as I the inability to upgrade components as standard as quite a flaw in the component architecture.
Maybe, you can add a standard table which holds a version number and uninstall tables option for each component.  In addition the install can then either call the com_install() or a new com_upgrade($version) script.