When reading through all the articles on oo refactoring you might think that refactoring is fun. Just use some of the functions of your IDE, recompile and retest using junit or something similar.
In my current project, we are doing some serious refactoring. The problem is that we are not using java but sql and oracle pl/sql. I can tell you, refactoring pl/sql & sql is not much fun. It’s a lot of manual work.
The reason we are refactoring is that the application was not flexible enough and the code was a bit of a mess. So the first step was to clean it up. First we redesigned all the functionality, assigned all the code to separate modules based on their responsibility, and made sure we had a layered architecture. I did the design using Poseidon for Uml. Poseidon does have some bugs, but i found it really usefull. It doesn’t do ER diagrams, and it doesn’t generate sql code, but it does allow you to model the design.
I used a class diagram, in which i put packages for all the different modules we had. Per module we have one pl/sql package, and a number of tables and views. I used classes in the module for these, using stereotypes to specify the type (table, view or package). The most important part of this was to layer the modules, avoiding circular dependencies, and define very clearly what the responsibility was for every module. We started coding bottom up, moving the procedures from the old packagse to the correct place (old way: cut and past).
Unit testing is important. We created a test script for every module, testing every function in the module. We didn’t use a unit test tool, a simple (pl/)sql script is good enough. Btw, writing the test script before you start coding the module, also helps you focus on the responsibility the module has.
All of this, refactoring, using layered architectures, unit testing, are normal, everyday activities in the oo world. But they are also very useful for database projects. I guess it’s time toad gets some serious support for UML models, pl/sql unit testing and most important of all, refactoring.