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 .NET 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:
using (DatastoreFile datastore = new DatastoreFile( task.DatastoreFileName, task.Log)) { ColumnList v1Columns = new ColumnList( new string[] { "Reference", "LastModified" }); v1Columns.AddUniqueReplaceConstraint( new string[] { "Reference" }); Table docs = datastore.NewTable( "Documents", v1Columns); // Use the table... }
The first time this code runs the table is created. Subsequently, NewTable
opens the table and verifies that it has the structure specified. If the structure is different,
throws an exception.NewTable
With the new version of the connector, the code is modified so that it looks like this:
using (DatastoreFile datastore = new DatastoreFile( task.DatastoreFileName, task.Log)) { ColumnList v1Columns = new ColumnList( new string[] { "Reference", "LastModified" }); v1Columns.AddUniqueReplaceConstraint( new string[] { "Reference" }); Table docs = datastore.NewTable( "Documents", v1Columns, false); ColumnList v2Columns = new ColumnList( new string[] { "Reference", "LastModified", "NewColumn" }); v2Columns.AddUniqueReplaceConstraint( new string[] { "Reference" }); docs.Modify(v2Columns, UpgradeV1ToV2); // Use the table... }
The code starts by defining the first version of the table, in the same way as the old code. When
is called the final argument, NewTable
false
, indicates that this is not the final form of the table.
Next, the structure of the second version of the table is defined and passed to Table.Modify
. This is also passed a function (UpgradeV1ToV2
). The function takes a record in the old format and returns it in the new format, for example:
private static Record UpgradeV1ToV2(Record record) { Record newRecord = new Record(); newRecord["Reference"] = record["Reference"]; newRecord["LastModified"] = record["LastModified"]; newRecord["NewColumn"] = "NewValue"; return newRecord; }
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.
You do not have to construct a new Record
object to return. The original Record
can be modified and returned, for example:
private static Record UpgradeV1ToV2(Record record) { record["NewColumn"] = "NewValue"; return record; }
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:
using (DatastoreFile datastore = new DatastoreFile( task.DatastoreFileName, task.Log)) { ColumnList v1Columns = new ColumnList( new string[] { "Reference", "LastModified" }); v1Columns.AddUniqueReplaceConstraint( new string[] { "Reference" }); Table docs = datastore.NewTable( "Documents", v1Columns, false);
Next, the structure of version 2 of the table is specified. This is the same as before, except that Modify
is passed false
as its last argument, to indicate that this is not the final structure of the table:
ColumnList v2Columns = new ColumnList( new string[] { "Reference", "LastModified", "NewColumn" }); v2Columns.AddUniqueReplaceConstraint( new string[] { "Reference" }); docs.Modify(v2Columns, UpgradeV1ToV2, false);
Finally, version 3 of the structure is specified:
ColumnList v3Columns = new ColumnList( new string[] { "Reference", "Date", "Time", "NewColumn" }); v3Columns.AddUniqueReplaceConstraint( new string[] { "Reference" }); docs.Modify(v3Columns, UpgradeV2ToV3); // Use the table... }
In this instance the upgrade function could look like this:
private static Record UpgradeV2ToV3(Record record) { Record newRecord = new Record(); newRecord["Reference"] = record["Reference"]; DateTime lastModified = record.GetAsDateTime("LastModified"); newRecord["Date"] = lastModified.Date.ToShortDateString(); newRecord["Time"] = lastModified.TimeOfDay.ToString(); newRecord["NewColumn"] = record["NewColumn"]; return newRecord; }
Alternatively, you could modify the original record passed to the upgrade function. Remember to remove the "LastModified" column, as this is replaced by separate "Date" and "Time" columns:
private static Record UpgradeV2ToV3(Record record) { DateTime lastModified = record.GetAsDateTime("LastModified"); record["Date"] = lastModified.Date.ToShortDateString(); record["Time"] = lastModified.TimeOfDay.ToString(); record.Remove("LastModified"); return record; }
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.