Greatness is a transitory experience. It is never persistent.
– Collected Sayings of Muad’Dib by the Princess Irulan
In the previous article we saw how we could have different version of the code in different editions of the schema. But what about the tables? They are not editionable? This article will show you how to work ‘around’ that.
Most database systems exist mainly because of their ability to store data, but tables are not editionable. Chances are a change in the system will involve changing one or more tables. Will this render Edition Based Redefinition useless when you need to change the structure of tables? No, it will not, but it does ask for you to work with tables a bit differently.
It’s actually always a good practice not to have anyone access the table directly. One way of doing this is revoking all access to the table from everyone and implementing a Table API to perform your create, retrieve, update and delete statements. Another way of doing this is implementing a view for every table and have users access that view. And this is exactly what Edition Based Redefinition needs. But it is not a regular view, it is a special view, namely an Editioning View.
An editioning view is a special kind of view. It’s used to reflect the changes in tables between the different editions. You create an editioning view just like any other view, but by adding the keyword EDITIONING to it. The syntax to create an editioning view is:
CREATE OR REPLACE [[NO] FORCE] EDITIONING VIEW
An editioning view should just project columns from the table directly, it cannot do any calculations on columns or perform formatting. There cannot be joins to other tables to get data from those, it is just used to allow access to the table and its data. In general they are just simple views. They should be used to make the right columns visible in the current edition. Imagine you have a table T_ADDRESS with address data like this:
|Column Name||Data Type|
An editioniong view would be defined like this:
CREATE OR REPLACE EDITIONING VIEW ADDRESS AS SELECT ID AS ID , NAME AS NAME , ADDRESS AS ADDRESS FROM T_ADDRESS;
In the child edition you want to split the address into a street and a number field. To do this, you just add the new columns to the table:
|Column Name||Data Type|
In the child edition you define the editioning view like this:
CREATE OR REPLACE EDITIONING VIEW ADDRESS AS SELECT ID AS ID , NAME AS NAME , STREET AS STREET , NR AS NR FROM T_ADDRESS;
By simply adding the columns to the table you won’t be breaking any code in the parent edition because of the fine grained dependency tracking which will not invalidate any code if the change cannot possibly have an impact on the code, as is the case when adding a column to a table.
In the parent edition you will not see the new columns because they are not projected in the columns in the view. In the child edition you will not see the old column as it is not projected in the view. You essentially hide the column rather than dropping it. Dropping the column would invalidate objects in the base edition and possibly render this edition unusable.
But what happens to the data when entered in either version? In the parent version you add the address in a single column. This data will not be visible in the child edition, and vice versa an address entered in the child edition (in the two columns) will not be visible in the parent edition.
Cross Edition Triggers
This is where the Cross Edition Triggers come into play. In the child edition you can define a special trigger on the table to reflect changes made in the parent edition in the child edition. And subsequently you can also define a trigger to reflect changes made in the current edition back to the parent edition. These are resp. the Forward Crossedition Trigger and the Reverse Cross Edition Trigger:
CREATE OR REPLACE TRIGGER
BEFORE ON FOR EACH ROW ] | REVERSE CROSSEDITION [PRECEDES ]> BEGIN END; /
Forward Cross Edition Trigger
To work with the data entered or modified in the parent edition in the child edition you can define a Forward Cross Edition Trigger. If we have the table as defined earlier and we want the ADDRESS field to be split into the STREET and NR fields we could write a trigger like this:
CREATE OR REPLACE T_ADDRESS_FXD BEFORE INSERT OR UPDATE ON T_ADDRESS FOR EACH ROW FORWARD CROSSEDITION DISABLE BEGIN :NEW.STREET := TRIM(SUBSTR(:NEW.ADDRESS, 1, INSTR(:NEW.ADDRESS, ' ', -1))); :NEW.NR := TRIM(SUBSTR(:NEW.ADDRESS, INSTR(:NEW.ADDRESS, ' ', -1))); END T_ADDRESS_FXD; / ALTER TRIGGER T_ADDRESS_FXD ENABLE /
Reverse Cross Edition Trigger
To work with the data entered or modified in the child edition in the parent edition you can define a Reverse Cross Edition Trigger. In this case we want to combine the STREET and NR fields into the ADDRESS field:
CREATE OR REPLACE T_ADDRESS_RXD BEFORE INSERT OR UPDATE ON T_ADDRESS FOR EACH ROW REVERSE CROSSEDITION DISABLE BEGIN :NEW.ADDRESS := :NEW.STREET || ' ' || :NEW.NR; END T_ADDRESS_RXD; / ALTER TRIGGER T_ADDRESS_RXD ENABLE /
Notice that the triggers are created disabled and then enabled afterwards. This is done to prevent the trigger from causing problems in the parent edition if it might be invalid.
Chances are there is existing data in the tables when you create a new edition. One way to reflect the data in the right columns for the current edition is to run an update script to make the necessary changes for you. But if you have the Forward Cross Edition triggers in place, the easiest way is to run an update statement on the entire table, setting one column to its own value, causing the trigger to fire for every row. In the example we could run either this script:
UPDATE T_ADDRESS SET STREET := TRIM(SUBSTR(ADDRESS, 1, INSTR(ADDRESS, ' ', -1))) , NR := TRIM(SUBSTR(ADDRESS, INSTR(ADDRESS, ' ', -1)));
or this one, relying on the triggers:
UPDATE T_ADDRESS SET ID = ID;
The latter is easier and it uses the code in the trigger, minimizing the chances on typos in the script. Plus, you don’t have to worry (again) about getting the right logic in place.
Edition Based Redefinition takes some getting used to and it might involve some work for existing systems, for example to change table names and putting editioning views in place, but your users will benefit from it because of the ‘near zero’ downtime they will experience. They can even run tests on implemented changes using their own data. When they sign off on the new version, you can start migrating the users to the new edition and when the last user migrated, disable (not delete) the old edition.