Database refactoring: Replace table with view

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):

  1. I created the tables CUSTOMER_SHARED, with column BONUS_LEVEL, and CUSTOMER_SITE with columns SITE and LAST_VISIT
  2. I populated the tables with the union of the current site databases.
  3. I dropped the existing CUSTOMER table
  4. 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.
  5. 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
    WHERE
         id = :new.id AND site = ;

    UPDATE customer\_shared SET
         bonus\_level = :new.bonus\_level
    WHERE
         id = :new.id;
END;

CREATE OR REPLACE TRIGGER customer\_DEL\_TR 
    INSTEAD OF DELETE ON customer
FOR EACH ROW BEGIN
    DELETE FROM customer\_site
    WHERE
         id = :new.id AND site = ;

    DELETE FROM customer\_shared
    WHERE not exists (
            SELECT * FROM customer\_site
            WHERE customer\_site.id = customer\_shared.id);
END;

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!

Comments:

[Salman Shaik07] - Sep 29, 2011

I agree with your comments. But, as the release date been closer, I cannot have automated tests in place now.

Apart from having few regression checks via front end screen which access that particular table/view, what else do you think would matter testing in the database directly.

For example, if we have dropped two existing tables and replaced it with one table and then created two views in place of existing two tables. I would mind to check 1) If all the contsraints which were there in earlier tables hold good.

  1. If the DML hits on that views/table go fine without any error. 3) I would check miscellaneous things like permission/grants or user specific roles on that particular table or view.

But, would there be any scenario where replacing two tables with one table and creating two view instead of that two table mights fail ???

I know explaining the scenario is quite difficult without pictorial representation, but would expect some great replies.

Thanks in advance Salman

[Salman Shaik07] - Sep 30, 2011

Ahem. I got the context, let me check all the possibilities until I reach the threshold of exhaustive testing for better satisfaction :) Thanks

[Salman Shaik07] - Sep 29, 2011

Good one. If we think of refactoring of the database by applying different methods like replacing tables with views…etc.. What to you think should be the strategy / execution plan of the QA team to test that the functionality works fine even after re-factoring ?

Though testing the front end is important ? What should be the back end database verifications which needs to eb done ?

Would be very very help ful if you could post some help.

Johannes Brodwall - Sep 29, 2011

Hi, Salman

At the level you’re asking, the question of “how much testing” is a matter of risk assessment. What are the worst things that could happen, what are their consequences, how likely are they, and how much do we want to invest in detecting the problem up front? The answer most to these questions are context specific.

In the case of the view replacing two tables: * Syntax error (likely to happen without testing, easy to test) * The view is wrong (returns the wrong data) (likely to happen without testing, easy to test) * Corner cases are treated wrong (for select) * Insert/Update/Delete triggers contain errors * Corner cases are treated wrong (for Insert/Delete/Update)

The level of testing should correspond to the complexity of the change and the risk for something going wrong.

[Salman Shaik07] - Sep 29, 2011

Please ignore typos, used mobile qwerty to type this :)

Johannes Brodwall - Sep 29, 2011

Hi, Salman

I think it’s critical to have automated tests that exercise the database schema in realistic ways. For a low-risk refactoring like replace table with view, I would trust the automated tests to catch anything serious.

The release as a whole would of course be part of the overall test plan, but I would not take any extra action because of the database refactorings. Provided that we have good tests!

[Karianne Berg] - Oct 13, 2010

After working with the Smidig2010 app without having a proper admin interface, SQL does not scare me at all. :) Never seen “instead-of” triggers before, though. Very cool, will have to try.

Knut Erik Borgen - Oct 18, 2010

I have not read it myself, but a former colleague of mine read the book ‘Database refactorings’ and found it interesting:

http://databaserefactoring.com/

I see that it contains a lot online as well with references that for people with some experience could find useful.

Anders S - Oct 13, 2010

Good stuff. Last resort, but tools everyone should know.