When working on replacement projects, I often find I need to make minor changes to an existing database that is still in use by one of several other applications. Initially, it may seem like situation will force you to conform to the current database schema. But there are other options, even though they may not be for those who are faint of heart.
The general pattern when you want to evolve a database that is in use by legacy system, is to make sure that the legacy system sees the same data structure when it reads from or writes to the database. You can achieve this by using database views and “instead-of” triggers that will execute custom SQL code when insert, update or delete statements for the view are executed.
Here is an example: I was once given the challenge of centralizing a database that the customer currently installed one per site. The rub: I could not change the current application. And the new solution should treat some information as shared across the sites and some information as exclusive per site.
Here is how I solved the problem for an example table (let’s say CUSTOMER, with shared column BONUS_LEVEL and site-exclusive column LAST_VISIT):
- I created the tables CUSTOMER_SHARED, with column BONUS_LEVEL, and CUSTOMER_SITE with columns SITE and LAST_VISIT
- I populated the tables with the union of the current site databases.
- I dropped the existing CUSTOMER table
- I created a new CUSTOMER view as “SELECT … FROM customer_site INNER JOIN customer_shared WHERE customer_site.site =
“. Now, the existing applications can see, but not update data.
- I created “instead-of triggers” for insert, update and delete on customer. The existing applications can now update data as if nothing had changed.
Instead-of triggers is a very handy mechanism for views. They are executed when insert, update or delete statements are executed on the table. Some examples of such a triggers are:
CREATE OR REPLACE TRIGGER customer_UPD_TR
INSTEAD OF UPDATE ON customer
FOR EACH ROW BEGIN
UPDATE customer_site SET
last_visit = :new.last_visit
id = :new.id AND site = <current user name>;
UPDATE customer_shared SET
bonus_level = :new.bonus_level
id = :new.id;
CREATE OR REPLACE TRIGGER customer_DEL_TR
INSTEAD OF DELETE ON customer
FOR EACH ROW BEGIN
DELETE FROM customer_site
id = :new.id AND site = </current><current user name>;
DELETE FROM customer_shared
WHERE not exists (
SELECT * FROM customer_site
WHERE customer_site.id = customer_shared.id);
This sort of transformation is not without risk, and should be carefully tested and scripted to make sure it behaves the same when you test and when you deploy it.
A final word of warning: Both the function and the performance of instead-of triggers can depend a lot on your database vendor. Make sure you test a solution that use these features with a realistic amount of data.
I’d like to hear from readers who either found the SQL code intimidating or enlightening. I’d really like to know whether my readers are as frightened by SQL-code as non-programmers are by Java. ;-)
So remember: When you’re faced with a seemingly impossible task: Think inside a bigger box!
Copyright © 2010 Johannes Brodwall. All Rights Reserved.