Keeping Firebird Generators Up to Date in your DR Database

SymmetricDS can be used to replicate Firebird databases to the cloud for Disaster Recovery. Setup is fairly straightforward. Select the tables you want to sync and the direction you want to sync them and, voilà, you have a backup database. While this is all well and good, if the database relies on id generators for primary key generation, then when failing over to the slave database you will probably end up with generators that no longer reflect the next value to insert.

You can solve this problem by adding load transforms to your tables that use generators. The following example is a Java Extension Point that can be used as a column transform that keeps a named generator up to date.

First, you want to add a new extension point. You do this from the Configure > Extensions screen. Make sure you select the ISingleNewAndOldValueColumnTransform interface.

Adding a Custom Extension Point

Next, you need to add the Java code that represents the implementation of the interface. You do this by selecting the extension and pressing the Edit Script button.

Adding Java Code for the Extension

Following is the implementation of the column transform. It simply checks to see that table and column it is associated with does NOT have a bigger value in the target table. If it doesn’t, then it assumes that it is safe to update the generator to the current id value. Note that it gets the name of the generator from the transform expression. This will need to be configured when the transform is setup on the target table(s).

Now we have a registered column transform extension that we can use. It is time to setup the transform on a table. For this example, the table transform is going to be on the NOTE table. We will assume that the NOTE table has already been configured to synchronize. Use the Auto Create button to select the NOTE table. We are using an IMPLIED table transform which means all of the columns that are not explicitly named will be passed through.

Adding the Transform

Next we need to edit the table transform to make the transform a LOAD transform. LOAD transforms execute on the target node. EXTRACT transforms execute on the source node.

Make it a LOAD Transform

Finally, we need to add our column transform on our ID column. Select the table transform and press the Edit Columns button. Add a column and select ID for the source and target columns. Drop down the transform type and select our custom transform. The custom transform expects the transform expression to be the name of the generator.

Add the Custom Column Transform