Refactoring (pl)sql is hard
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.
October 12th, 2003 at 11:48 pm
your title confused me. how do you refactor sql? after all the only thing you can do is to optimize it.
you are really talking about plsql packages etc. maybe that’s what your title should be. because sql and plsql aren’t the same.
October 13th, 2003 at 8:04 am
Actually, i didn’t really mention it, but refactoring sql is the hardest part. Refactoring pl/sql is easier. Sql is more than the select statement. To refactor tables you use sql. The hard part is migrating all the production data. So unit testing all the migration scripts is essential.
October 13th, 2003 at 11:08 am
SQL, PL/SQL are simply languages for representing some semantics and it is absolutely natural to allow for refactoring and other natural services for them. Of course SQL is mostly declarative language but it does not change anything (it is big minus). This list of languages can be continued with JavaScript, JSP, EJB deployment descriptors, HTML, machine codes and whatever other language you like and for all of them we want to apply refactoring and finally unite in one large program. For example, in your case we might want to change a table or field name and then have this change propagated through the whole system including different modules written in different languages. Yet unfortunately it is possible in a very restricted form because currently there is no such a theory or new programming paradigm, which would allow for such a universal treatment. If we had such an approach then the SQL refactoring as well as many other mechanisms could be implemented almost automatically.
October 14th, 2003 at 1:44 pm
In my point of view, you are mixing up the things. I know that refactoring is not intended to be used only with Java (I have read a refactoring book which comented about some refactorings in C/C++), but use it in procedural, or even in SQL language is really out of scope at first sight. You could use other word such as ‘cleaning’ etc, but refactoring is a little more complicated to be used in this situation.
October 14th, 2003 at 2:06 pm
In my understanding refactoring is about improving the design of your application in small steps, as required to be able to add new features to the application, and making use of unit tests to ensure the quality of your changes. So refactoring is cleaning, but doing it in small steps, and unit testing these steps.
I don’t see why you couldn’t apply this to pl/sql or sql.
October 14th, 2003 at 4:17 pm
I wonder about your comments. Having heard something very similar, very recently, from folks who were called in to “fix” a “messed up” project.
The history of the project was: – dos application evolved to windows application, single user pc to multiuser shared local db, to client server. – same developer worked throughout the project, over eight or nine years. – client server version was done in Oracle Designer, full modelling, 98% of code (stored procs for everything possible including data sources) generated by designer.
Now, what happened was that the IT dept at the client site “assumed responsibility” for the application suite, and was therefore quite keen on capturing the skills of the developer. Mind you, the contract with the developer specifically excluded training of any kind. The user base was already trained on the application and the business processes had already been optimized.
The developer, as a bonus for the IT group, delivered and installed Oracle Designer, all Oracle Tools, and the originals and weekly master backups of the designer datasets. So the IT group had all resources, all source code, and the full development environment, both on a local machine (stand alone) and on a networked (shared server) machine.
What happened is that the in-houst IT folks never took 3rd party training on Oracle Tools, Oracle Designer, etc. So they goobered it up quite righteously. Keep in mind that the technical level of this IT group was such that only the DBA was able to do TNS modifications effectively IE in less than three full working days of effort, and he was routinely alienated by the in-house IT group as being too strict, too demanding, and generally an a**hole.
Now, what the IT group did was to hire a different contractor, burn the prior contractor in effigy in their absence while previously stating in writing that the contract was completed satisfactorily and the project was accepted, and then paid enormous sums to the new contractor to “refactor” the project.
Ergo I really have to question the use of refactoring as a work / task item absent the project history and justifications. It is my opinion that refactoring, as an asymmetrical task effort, is totally bogus, whether it is done in SQL, PLSQL, Java, or any other context. It (refactoring) just makes no sense to me unless it is appropriate in the big picture view.
To put it another way, there continues to be, as there has been over the past 20 plus years of my systems engineering / programming experience, a new term every three years or so that is hyped up, and which exists primarily to confuse, obfusticate and conceal the “messed up” code, designs, architectures and applications. This is a bad thing because it defrays accountability, responsibility and marginalizes advancement of professionals who can do the job right the first time, and who have done so. This strategy is a deliberate, premeditated means by which IT staff can do CYA without having to do excellent work.
When someone says “refactoring” to me, I switch into inquisitorial mode, seeking to ferret out whatever they are seeking to hide, because, to me, 99% of the time “refactoring” is needed the reason it is needed is because the job was not done properly to start with.
I will grant you that today’s business environment, which has been prevalent for several years, seems to place a premium on just getting it done, getting the competitive advantage from it, and moving on to do the next thing without proper engineering, architecture, documentation or design efforts. So, yes, refactoring is a huge buzzword today. But I still think it would be more direct, more honest to call it what it is—- a do-over because it was hosed up (money was wasted) the first time around.
Just my opinion.
Thanks!
October 14th, 2003 at 4:21 pm
Scott Ambler has a great article of refactoring databases:
http://www.agiledata.org/essays/databaseRefactoring.html
November 16th, 2003 at 3:51 pm
Process of Database Refactoring
Database Refactoring
February 17th, 2004 at 10:08 am
What I want to say for PL/SQL is thus:
1) Functions or procesure of PL/SQL with many parameters are bullshit.
2) Performance of PL/SQL is said a little better than JDBC natural statement, but its difficulty of debug is much worse. I don’t like blackbox modules.
3) I have seen many of ”?,?,?,?,?,?,?,?,?,?,?,?,.....,?” in some project. Those are extremely ugly and resemples as alien’s home.
4) Without invention of object based parameter and debugging ability by Oracle or third-party, PL/SQL is no use for rapid and secure development.
March 16th, 2004 at 8:25 pm
Ok Ok it’s too much beautiful… but how we refactoring pl/sql…
That’s is the question?
Regards From Spain