Extreme database programming - now possible with Refactoring.

Extreme programming is a quite interesting technique. It really is useful in projects, for which customer demands have not yet been clearly formulated or where customer’s business changes so rapidly that there is a constant call for changing the information system of the enterprise itself. Refactoring is one of the basic techniques of the extreme programming. Thanks to it we do not mind fuzzy customer demands, because we are not afraid to change and improve our code.

Clearly the projects for which extreme programming techniques suit most are those that involve database development. This is due to the fact that development for business automation is usually about collecting and analyzing data. In case there is a need to modify the code, most of the modern development environments offer tools for automatic refactoring. And yet there are no tools aiding database refactoring, in fact there is nothing for refactoring in database development! Yet to keep the efficiency of the system not only the code should be changed but also the data structure. Moreover, handmade refactoring is hard, because the code of the procedures in the database is strongly depended to the structure of other objects. And consequentially even the simplest routine change requires time consuming manual work. Developers under InterBase or Firebird should rejoice: now they have a system with automatic refactoring.

Below I convincingly demonstrate that even a little change is hard to make without refactoring tools built into the environment. And then I show how easy it is with such tools at hand.

Sometimes, in process of the intensive development and support of databases it is suddenly found out that the name of a table does not correspond to entity it stores, or in the name of a column there was a typo or an inexperienced developer has given an odd name to a procedure. If the table or procedure is not used by other objects in the database then renaming procedure is simple enough:

Assume that we want to rename a table which is used in procedures, views and other table constraints. Now we should change not only the name of the table, but also all objects, using the table. Things become even more complicated for following reasons: InterBase/Firebird servers support consistency of metadata - in a database there can not be procedures referring to nonexistent tables and fields, procedures and view. If there is a foreign key for a table it cannot be dropped, etc.

This means that we have to:

• find all dependent objects
• change them so they do not use old table
• rename table using previous algorithm
• change dependent objects to use new table

To temporarily "disconnect" a procedure, a trigger or a table from the old table is relatively easy: the body of procedures and triggers can be set empty, and constraints can be removed in the table. Views are much harder to change. Views cannot be changed (ALTER), it can only be dropped and then recreated again. This means, it is necessary to take care of dependent procedures, triggers and other views recursively! Assume it is necessary to remove a table which is used in three procedures - the server of a DB will give out the message "Cannot delete. COLUMN NNN. There are three dependencies.". The Server does not tell in which procedures the table is used, and that also complicates a problem of searching dependences. Accordingly there are two choices:

• make a full script of a DB and manually look for dependent objects in a file.
• use the table RDB$DEPENDENCIES for searching dependent objects. Modern development tools are able to show the list of dependent objects on the basis of RDB$DEPENDENCIES in a more or less acceptable way, but one needs to look into object text to find out precisely how a table or a column is used.

It takes 10 minutes of laborious, nonintellectual, from the developer point of view, work to rename table CUSTOMER in the sample database EMPLOYEE which is referred to by two procedures, one trigger and one table. Average database contains (for example) 50-70 tables, two hundreds procedures and triggers and about 30 views - it will take not less than 20-30 minutes to rename one object. In a big data base with a lot of objects and big size of stored procedures it will even more.

By now it should be clear why in a more or less used system the structure of the database remains unchanged. Because this is a very laborious job. Consequentially the difference between the ideology underlying the structure of the database and the ideology of the applications grows which leads to the “patchy” programming style. Obviously this hampers speed and reliability.

Now I will demonstrate how a lot simpler all this would be using the IB/FB Development Studio. Let us recall the task: it is required to rename the table for which not an optimal name was chosen, e.g. the name of the table does not match the data stored in it. IB/FB Development Studio supports the Rename References operation. It renames references to the specified object. (Note: the object to which the references are made should be created in the database before the Rename References operation is called.) To do the renaming the following four simple steps must be made:

1. Create the copy of the object with a new name.
2. For the table, migrate data from the old table to the new one (you may run the following statement: INSERT INTO newname SELECT … FROM oldname ).
3. Rename references (in the database editor this command creates a runable SQL script).
4. Make sure there are no references to the old object and then delete it.

In fact there is even a shorter way. After the Rename command the system generates a script which does the renaming, and everything will be taken care of: automatic creation of a new object with a new name, as well as renaming the references. In case of a table, the data will be migrated from the old table to the new one.

By Pavel Kutakov, November 2006