OraTransplant

Applying best practices from 3GL development

I’ve been looking at the tools used for 3GL development with some envy. There are tons of very productive tools available for Java, C++ and other 3GL developers. Wouldn’t it be great if we can use some of those for our Oracle Forms/Reports/Database development.

I’m planning on implementing some of those for our Oracle development in the rest of 2005 (and probably 2006). Hopefully this also closes the gap between our Oracle and Java developers. This is nice way for the Oracle developers to get to know some best practices from the Java development. Hopefully this makes a transition to Java a bit easier. I’ll keep reporting on this blog on my findings and perhaps publish a number of papers on how we implemented and integrated some tools.

The things I’m hoping to investigate (and perhaps implement) are:

Issue management
We could track bugs, features, tasks and improvements in a more professional way than we are doing now. This would also enable us to implement some workflow. I’m currently thinking about implementing JIRA.

Version control
Currently we do have some version control in place, but it is not as professional as I would like it to be. I will be evaluating both CVS and Subversion and we’ll probably implement one of these two. I tend to like Subversion a bit more but JDeveloper doesn’t support it yet.

Automatic builds
I would like to create automated builds as much as possible but at least daily. Refresh a database and an application server and apply all changes checked in to the version control system. During this automated process lots of checks and other tasks can be performed and monitored (also see other points). We might build this based on Ant and CruiseControl, since we’re already using these for our Java development.

Automatic tests
During the automatic builds I would like to perform as much automated tests as possible. This reduces the load on testers and can warn a developer early on that his change broke some tests. I’m looking at DBUnit and utPLSQL first.

Powerful editors
Currently we develop our PL/SQL code in Designer. The editor in Designer does offer color coding, but is not as powerful as other editors. Perhaps we should make JDeveloper or TOAD our primary editor. This would also enable us to offer all sorts of productivity enhancements to developers by using (custom build) plugins, templates and code snippets.

Formatting
If we would switch to another editor (JDeveloper or TOAD) I would also like to investigate code formatters. Using a uniform formatting of code makes it easier for developers to look at each others code. Also I found during Java development that Jalopy can save you a lot of time when writing code. It’s so nice to not bother about things like indentation and just run your code formatter to take care of it.

Quality Assurance
We have a lot of standards and guidelines for development. Wouldn’t it be nice if most of them could automatically be checked/enforced in your editor and during automatic builds/checks?

Documentation
If you’ve ever programmed Java, you must know JavaDoc. It’s a predefined way of adding documentation to every class and method and can generate quite useful HTML documentation for all your code automatically. Something similar exists for PL/SQL: PLDoc. It’s not much work for a developer to add comments using a fixed format but the generated HTML documentation can be a real treat. The editor should be able to help you here.

Logging
I want to have a look at the Log4PlSql framework. It’s designed after the Apache Log4J framework as being used by Java developers. It offers a way to add logging statements to your code which you can just leave in there for your production code. You can enable it at runtime and write the logging info to different outputs (database table, file, standard output, etc). No more hassling with adding dbms_output all the time and removing it again.

If you have any experience with these tools or methods and have some handy tips, please leave them as comment.

Screenshots of JDeveloper plugins

About two weeks ago I wrote a blog entry about a presentation of Jules de Ruijter of Centraal Boekhuis. They decided to drop Designer as a tool and move all design information from the repository to flat ASCII files managed/edited by JDeveloper and versioned by CVS. They create some plug-ins for JDeveloper to edit/manage these files.

To my surprise the homepage of OTN had a link to this entry for about a week. The number of visitors to my site increased by some 800%!

The presentation was originally in Dutch, but with all the attention Jules was so kind to translate it to English. He also wrote a very informational comment at the previous blog entry explaining in more detail what the plug-ins do. He has now even send me some screenshots to demonstrate their use of JDeveloper. See below for the screenshots and some explanation.

Navigator and custom actions
The first screenshot show how Centraal Boekhuis organized all the ASCII files. They use a normal folder/subdirectory structure to organize the files by business process/unit (Technical, Business planning, Marketing and Sales, Process Development, etc.), Design Area (Business Model, Data Model, System Design, etc) and Application System (ASSBEH - Assortment- and stockmanagement). So far, that’s all default JDeveloper. The only thing they added are some custom actions; Create New Object, Rename Object, Delete Object, Show Versions, Navigate Back and Search Object. Some of these are for shortcomings of CVS (e.g. rename/delete objects), but there is also a very interesting one: Show Versions shows the actual installed version in the different databases (Production, Test, Development).

Custom tabs in the Editor
The second screenshots shows the extra tabs they created in JDeveloper using the SDK. You can see the normal Source tab of JDeveloper. Notice the little tag “--<cbcode>” at line 2. This seperates the documentation from the actual code. I personally found this a simple yet brilliant idea. By combining the documentation and the code in a single ASCII file they are always stored (and versioned!) together. The three new custom tabs Doc, Code and Test all show only that section of the overall text file. The user/developer can just edit these three parts separately in the tabs, yet it is still combined in one overall ASCII file visible on the first tab.

Network diagram in structure pane
The next two screenshots shows the relationships between all objects. Centraal Boekhuis found out that the relationships (Implements/Implemented By, Calls/Called By, Owns, Owned By, etc) between all Designer objects themselves are valuable information. It’s very hard to maintain this information in the ASCII files. That’s why they’ve extracted this information from Designer and stored in some simple database tables. Then they created another plug-in to JDeveloper to show and edit these relationships in the structure pane of JDeveloper. There is a popup menu with common actions; Delete Relationship, Change Relationship and Navigate Back.

Changing a relationship
The final screenshots show the dialog for changing a relationship

Full screenshots
All the pictures above where parts of the original screenshots Jules de Ruijter mailed me. You can click in the thumbnails below to show the original full-size screenshots. Be aware that they might be too large to show on your screen and your webbrowser might shrink them. If you’re using MS Internet Explorer look for an overlayed button at the righthand size of the image to restore it to its full size.

Moving design info from Designer to JDeveloper

Yesterday I attended a seminar at IT-eye. Andrej Koelewijn was presenting about a project he did for us. Two of our own developers and he created a disconnected Swing client to report donor information to our central database. What really caught my attention was a presentation by a client of IT-eye on their choice to drop Designer and move all design information to JDeveloper.

They decided they wanted to drop the tool Designer, but not all the information they gathered in it since the mid 90’s. He had quite a number of arguments why they made this choice and I must say that most of them sounded quite reasonable. You might be in quite a different situation and decide to go with Designer for a couple of more years.

I guess we’re in the same situation; you’ve got tons of information in Oracle Designer. Unfortunately Oracle hasn’t really added new functionality to Designer for quite a while and their statement of direction sais they won’t be adding new features or enhancements. All the new things you’re developing (10g specifics, J2EE apps, etc) cannot be modelled in Designer. The 100% (re)generation goal you used to have becomes more and more difficult or impossible if you want to use these new features.

They decided to drop the generators in Designer and just continue to maintain the Forms and Reports in the respective Builders. But what about all the design information in the Designer repository? They looked at the large number of different objects in Designer. This leads to a huge number of properties you can set. Analysis shows that only a small number of these properties really hold valuable information. A lot of them are never used or are specific to the generators. The actual business information is stored in a relative small number of properties. The other valuable information is stored in all the relationships between the objects (owned by, called by, etc).

They decided to convert all relevant information to plain ASCII files. Each object (business function, table, etc) was stored in a separate text file. The files were placed in a directory tree related to their application system and object type. Each file contains both the design/business information and the actual code. For example the text file for a table not only contains the description from Designer, but also the actual CREATE statement.

The files are versioned using CVS and edited through JDeveloper. They created some small JDeveloper plugins to ease the editing of these files. The plugin added some tabs to the editor of JDeveloper. The normal Source tab shows the entire file. A custom tab Documentation showed only the documentation part of the file and a Code tab showed the SQL statements. A new tab Test was added to store test cases in the future. The tabs are just an easy way to edit separate sections of the text file, but they do make it a lot easier.

They also created a custom plugin to JDeveloper to show the “network diagram”. That’s all the relations (owned by, called by) between the different objects. This shows a tree structure in the structure pane of JDeveloper.

I loved their KISS (Keep It Simple Stupid) approach. Storing it in plain text files and building on top of great functionality already available in tools (CVS + JDeveloper). Perhaps I have to let the idea rest for a while and look at it again in a couple of weeks/months. Who knows where this may lead us?

Update: To my surprise, this entry has been linked from the OTN homepage! I also noticed in Andrej Koelewijn’s post, that the (Dutch) presentation is available for download. I wasn’t really sure if they wanted me to use the client name in this blog entry. Since Andrej did it, I guess it is okay to say it was Jules de Ruijter of Centraal Boekhuis and to thank him for a very interesting presentation. Centraal Boekhuis is a very well known Oracle shop in the Netherlands. Toon Koppelaars of Centraal Boekhuis wrote the award winning paper “A Database-centric Approach to J2EE Application Development” last year.

Update: Since this article has been linked from the OTN homepage it is getting quite a number of hits. Jules de Ruijter was so kind to translate his presentation to English.

Update 10-jul-2005: This blog entry is now linked from the Chinese OTN homepage. See the screenshot to prove it. I’m sorry but my Chinese is a bit rusty and I cannot offer a translation of this page