Upgrade Tables

You might add a new feature to your connector that requires a change in the structure of its datastore tables. If the connector has already been used in a production environment there might be datastore files with the old structure that have been populated with information. It might be desirable to upgrade these to the new format, instead of discarding the information. The Datastore provides a way to perform these upgrades.

A connector needs no custom programming to determine whether a datastore table needs to be upgraded. Instead, the code defines the structure of each version of the table, and provides a function to upgrade between the versions. ConnectorLib Java automatically determines which version of the table is present and performs upgrades if necessary.

Example

Consider the following table:

Reference LastModified
C:\data\file1.txt 2013-08-05 18:05
C:\data\file2.txt 2013-07-30 14:36
C:\data\file3.txt 2012-12-24 09:00

This might be modified to include an extra column:

Reference LastModified NewColumn
C:\data\file1.txt 2013-08-05 18:05 New Value
C:\data\file2.txt 2013-07-30 14:36 New Value
C:\data\file3.txt 2012-12-24 09:00 New Value

The original connector code would create or open the table like this:

       String[] columnsV1 = new String[]{"Reference", "LastModified"};

       datastore.createTable(
            "Documents",
            columnsV1);

       //Use the table

The first time this code runs the table is created. Subsequently, createTable opens the table and verifies that it has the structure specified. If the structure is different, createTable throws an exception.

With the new version of the connector, the code is modified so that it looks like this:

       String[] columnsV1 = new String[]{"Reference", "LastModified"};

       datastore.createTable(
            "Documents",
            columnsV1,
            new String[]{},
            new String[]{},
            false);

       String[] columnsV2 = new String[]{"Reference", "LastModified", "NewColumn"};

       datastore.modifyTableByRow(
            "Documents",
            columnsV2,
            new String[]{},
            new String[]{},
            new DatastoreUpgrader(),
            "upgradeV1ToV2");

       datastore.commitTable("Documents");

       // Use the table

The code starts by defining the first version of the table, in the same way as the old code. When createTable is called the final argument, false, indicates that this is not the final form of the table and the table should not be committed yet.

Next, the structure of the second version of the table is defined and passed to Datastore.modifyTableByRow. This is also passed a method (upgradeV1ToV2). The method takes a record in the old format, a record in the new format and returns a boolean indicating whether the new record should be added to the upgraded table. For example:

       public class DatastoreUpgrader
       {
           public boolean upgradeV1ToV2(DatastoreRecord oldRecord,
                                          DatastoreRecord newRecord)
           {
               newRecord.setString("Reference", oldRecord.getString("Reference"));
               newRecord.setString(
                           "LastModified",
                           oldRecord.getString("LastModified"));
               newRecord.setString("NewColumn", "New Value");
               return true;
           }
       };

In the previous example, the connector creates a new record and repopulates the values. The auto-generated ID column does not need to be assigned explicitly as the existing ID is transferred to the new record.

Finally, the connector calls Datastore.commitTable to indicate that this is the final form of the table.

When the connector is run its behavior depends on the datastore file that is present:

  • If there is no datastore file, or the datastore does not contain a “Documents” table, the new “Documents” table is created. This table has three columns ("Reference", "LastModified", and "NewColumn") and its version number is 2.
  • If the datastore file exists and contains a V1 Documents table, it is upgraded to V2. For each record in the V1 table, upgradeV1ToV2 is called to upgrade the V1 record to a V2 record.
  • If the datastore file exists and contains a V2 Documents table, the datastore is not changed.

In all three cases, when the code reaches the “// Use the table…” comment, the table contains the columns “Reference”, “LastModified” and “NewColumn”, and is ready to use.

A Second Upgrade

Some time after performing the previous upgrade, you might want to update the connector again. For example you might split the "LastModified" column into separate "Date" and "Time" columns:

Reference Date Time NewColumn
C:\data\file1.txt 2013-08-05 18:05 New Value
C:\data\file2.txt 2013-07-30 14:36 New Value
C:\data\file3.txt 2012-12-24 09:00 New Value

The code for opening the table would start as before, specifying version 1 of the table structure:

       String[] columnsV1 = new String[]{"Reference", "LastModified"};

       datastore.createTable(
           "Documents",
           columnsV1,
           new String[]{},
           new String[]{},
           false);

Next, the structure of version 2 of the table is specified. This is the same as before, except that commitTable is not called yet, to indicate that this is not the final structure of the table:

       String[] columnsV2 = new String[]{"Reference", "LastModified", "NewColumn"};

       datastore.modifyTableByRow(
           "Documents",
           columnsV2,
           new String[]{},
           new String[]{},
           new DatastoreUpgrader(),
           "upgradeDocumentsV1ToV2");

Finally, version 3 of the structure is specified:

       datastore.modifyTableByRow(
           "Documents",
           columnsV3,
           new String[]{},
           new String[]{},
           new DatastoreUpgrader(),
           "upgradeV2ToV3");

       datastore.commitTable("Documents");

       //Use the table...

When the “Documents” table is opened the connector automatically determines its version and performs any upgrades necessary to bring it up to date. This might involve multiple upgrade steps, as in this example when upgrading from version 1 to version 3.