Andrej Koelewijn

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.

This article (in dutch) describes how you can use UML Statechart diagrams to model pageflow in a web application. I’ve also describes how to integrate struts actions into the diagrams. I’ve kept it very simple, without stereotypes, to focus more on the communication part of the model. Ofcourse when you start to use generators, such as andromda, you can add stereotypes to instruct the generators how to generate the code.

One issue i raised is that, eventhough you can use statecharts to model pageflow, you have to be carefull that you don’t implement a too rigid statemachine when you implement the flow. It is usuallly desireable to have multiple entry points into one flow (usually you’ll have one flow per use case), to enable user to bookmark interesting pages, and to enable easy integration between multiple web applications.

One more point to raise here, activity diagrams are probably better to model pageflow than statecharts. I think statechart are more designed to model the state transitions within one object, whereas activity diagrams are designed to model the flow within a process. JDeveloper 10G will actually use activity diagrams to model pageflow.

OptimalJ currently doesn’t generate code for database sequences which you can use to create unique values for primary keys in your tables.

So I did some research to see how other people solve this problem. One person suggested counting the number of records in a table, increase that number by one, and use that as your primary key. Brilliant! Should perform really well, and no concurrency problems to be expected.

I found one real solution on the optimalj forum, which was to create a Domain class (this translates into a database table) that holds ‘sequence’ records. One record per sequence, with a name and a value column. Then on the Domain Class you can add a DomainOperation to query the next sequence value, e.g., nextVal. The example on the forum described the situation when using EJBs, but I’m trying to generate DAOs, because i don’t have the memory in my laptop to run an ejb container.

After generating the application model and the code model i have to implement the code for the nextVal operation (get current value from table, add one, etc), but there’s a problem. Where is it? I can’t find the nextval stub anywhere, i must have checked 20 java files…

Usually when i want to create a diagram that displays the pages in a web application and the links that allow you to navigate from one page to another I use the UML statechart diagram. I don’t think statecharts where intended for this purpose but it works in communication with clients and other developers. Are you also using this or are you using something else?
I haven’t looked at the upcoming UML 2.0 yet, are there any new diagrams for this purpose?