Attention, Developers!

Have you ever wondered how you can run an upgrade task in a plugin to do something like add an extra column to a table or a new index to boost performance?  You know, those one-time tasks that need to be applied consistently across all your environments?  One of the powerful (yet often overlooked) features in Jive SBS 3.0 is the ability to run upgrade tasks in a plugin.  These tasks are run once and can do anything from modifying a database to creating new files and folders.

The Past...

Prior to SBS, developers relied on a number of techniques to perform upgrade tasks, including:

• Checking for the presence of a system property and, if not present, firing off a background thread to perform the upgrade task from the plugin class' init() method
  (downside: someone deletes the system property and your upgrade task gets run again, clustering introduces risk that your upgrade will be run more than once, more work than necessary)
• Writing a SQL script that could be run by the DBA on each environment prior to the plugin upgrade
  (downside: may require special permission to run scripts, especially in hosted environment; not cross-platform compatible; multiple steps in deployment process may easily be forgotten)
• Overlaying the core application to include upgrade tasks with core application upgrade tasks to take advantage of native process for handling upgrades
  (downside: must be merged with every release, requires multiple deployment artifacts, risky)

...And Now?

I hoped you would ask!  Here's what you need to do:

Step 1

If you don't already have it, add the following two items to your plugin.xml:

<plugin>
     <!--...omitted for clarity...-->
     <databaseKey>pluginName</databaseKey>
     <databaseVersion>1000001</databaseVersion>
     <!--...omitted for clarity...-->
</plugin>

Now when the plugin is installed, it will create a record in the jiveVersion database table with the appropriate values for name and version.

Step 2

Create a new file "upgrade.xml" at the root of your plugin.  This new file will live alongside your spring.xml, struts.xml, etc., and contains the list of upgrade tasks that will be run.  Each upgrade task is given a version.  If the version of your upgrade task is greater than the value in the jiveVersion table for your plugin, your task will be run.  Once the upgrade is complete, the version in the database will be the same as the highest upgrade task version.  Here's an example upgrade.xml taken from our Supportal:

<upgrade-config>
    <upgrades>
        <upgrade order="1">
            <name>supportal</name>
            <tasks>
                 <!-- 3.2.0 -->
                <task version="32000001"
                    className="com.jivesoftware.community.upgrade.tasks.AddEnvironmentsTask"/>
                <task version="32000002"
                    className="com.jivesoftware.community.upgrade.tasks.EnvironmentPermissionsTask"/>

                <!-- 3.2.1 -->
                <task version="32100001"
                    className="com.jivesoftware.community.upgrade.tasks.DeleteDuplicateEnvironmentFieldValuesTask"/>

                <!-- 3.2.2 -->
                <task version="32200001"
                    className="com.jivesoftware.community.upgrade.tasks.RemoveWebServerRequirementFromEnvironmentsTask"/>

                <!-- 3.2.3 -->
                <task version="32300001"
                    className="com.jivesoftware.community.upgrade.tasks.UpdateEnvironmentFieldOptionsTask1"/>
            </tasks>
        </upgrade>
    </upgrades>
</upgrade-config>

Once you have added your task, make sure to update the databaseVersion in your plugin.xml to the same value as your highest upgrade task!

Step 3

Create your upgrade task class.  In the example below we're calling out to an XML file that contains the new schema details.  More on that in a sec.  Here's the source code:

package com.jivesoftware.community.upgrade.tasks;
 
...imports omitted
 
public class RemoveWebServerRequirementFromEnvironmentsTask implements UpgradeTask {
 
    private static final String SQL = "RemoveWebServerRequirementFromEnvironmentsTask";
 
    public String getName() {
        return "Remove requirement for filling out web server from environment template fields";
    }
 
    public String getDescription() {
        return "Task to mark environment template fields for web server as non-required.";
    }
 
    public String getEstimatedRunTime() {
        return "1 second";
    }
 
    public String getInstructions() {
        return "To run manually, copy the SQL from the RemoveWebServerRequirementFromEnvironmentsTask.xml and run directly against your DB.";
    }
 
    public boolean isBackgroundTask() {
        return false;
    }
 
    public void doTask() throws Exception {
       UpgradeUtils.executeSQLGenFile(SQL, getClass());
    }
 
}

Step 4 (almost there!)

The last thing you need to do (assuming you are updating your DB, of course) is to create the XML file that will be read when UpgradeUtils.executeSQLGenFile(SQL, getClass()) is called.  To do this create an XML file in the same path of your class with the same name as your class, only ending with the .xml extension.  Following the example above, this could look like the following:

<schema name="Environment Schema">

    <sql description="Update default data for environments."><![CDATA[
        update supenvtemplfield set isRequired = 0 where envtemplfieldid = 40;
        update supenvtemplfield set isRequired = 0 where envtemplfieldid = 59;
        update supenvtemplfield set isRequired = 0 where envtemplfieldid = 78;
        update supenvtemplfield set isRequired = 0 where envtemplfieldid = 97;
        update supenvtemplfield set isRequired = 0 where envtemplfieldid = 116;
        update supenvtemplfield set isRequired = 0 where envtemplfieldid = 135;
        update supenvtemplfield set isRequired = 0 where envtemplfieldid = 154;
        ]]>
    </sql>

</schema>

The <sql> element lets you execute arbitrary DML or DDL statements against your DB.  You can also use the <alter> tag to change an existing table:

    <alter table="jiveBlog" type="add" description="Add Container Type and ID to JiveBlog">
        <column name="containerType" type="int" nullable="false" default="-2"
                description="The type of the container to which the blog belongs"/>
        <column name="containerID" type="bigint" nullable="false" default="17"
                description="The ID of the container to which the blog belongs."/>
        <index type="normal" name="jiveBlg_ctID_idx" column="containerID, containerType"/>
    </alter>

Additionally, you can define new tables using the same syntax as you would in your schema.xml:

    <table name="environmentTemplate" description="Customer environment template">
        <column name="templateID" type="bigint" nullable="false" description="Environment template ID."/>
        <column name="name" type="varchar" size="255" nullable="false" unicode="true"
                        description="The display name of the template (shows in environment template selection)."/>
        <column name="description" type="text" nullable="true" index_none="true" unicode="true"
                description="Tells admins the purpose of this template"/>
        <column name="status" type="int" nullable="false"
                description="The published status of the environment template."/>

        <index type="primary" name="envTempl_pk" column="templateID"/>
        <index type="normal" name="envTempl_templID_st_idx" column="templateID,status"/>
    </table>

What happens if my upgrade runs into problems?

It is important to note that plugin upgrades are run in the background during plugin initialization, so you won't see the upgrade screen similar to what is shown when you upgrade SBS.  However, any errors that occur during the plugin installation will be reported to you in the admin console under System > Plugins.  Here's what you can expect to see:

Questions?

Leave a comment for me here, or shoot a message to @austrum on Twitter!