Wednesday, July 4, 2012

JEE webapp RetroEngineering part 1 : database surfing

A few days ago I was faced one more time to an existing JEE application I didn't know much about before. Of course most of the team involved in design and development has left, leaving few documents behind...

Good points to start, project modules have been developed using ECLIPSE and MAVEN and managed with CVS.

I start with a quick look at the source code. Nothing very special : based on Spring, Struts2, ExtJS for a large part of the web front-end, OJB for the database mapping.

Then I look at the database, currently hosted on an ORACLE 10 instance.
Schema scripts exist in CVS and most tables are connected through numerous FK.
Okay, the schema seems to be consistent and strong (no hazardous implicit dependencies between tables, not much denormalization around at first sight).

Basically I think of two major options to learn more about the schema :
  • read one by one the SQL scripts stored in CVS
  • browse the living schema with my old friends SQuirreL, SqlDeveloper or the ECLIPSE plugins (my two favorites being SQLExplorer which is currently based on SQuirreL and ToadExtensions which seems to be a packaging of the old dead jOra that used to be at, as it is still referenced in the eclipse marketplace)
At this point, my mind starts to dream about a magical tool that would allow me to easily navigate from one table, call it TableA, to those "below" (referenced by FK from TableA) and "above" (referencing TableA by FK). In my dream, this tool is free, easy to install and use, non-intrusive and if possible open-source.

The company I work for uses Enterprise Architect, that can inspect a living schema and build up an UML schema from it, but I will finally get a gigantic sheet of paper with all the tables in random order (putting apart that it is not free nor open-source).

First I Google "database navigator". Mmmh first results does not appeal much to me.
Second chance, searched over not much either.
Third chance, searched over bingo ! the oracle-genie entry catch my eye : high activity, web based... smells good...

The project page provides an impressive video of its capabilities :
Let's have a try.
I download the package (a single war), copy it in the webapps folder of my tomcat local install (a very old 6.0), start it with "catalina.bat start" (yes, I'm working on Windows Seven 64 bits...).
I open my browser on localhost:8080/oracle-genie/ and the oracle-genie login page appears.
I enter the JDBC url, the schema username and password and click on connect.

The whole thing took me about 5 minutes, and that's it.

I begin the navigation from one of the tables on the left pane, and its details appears. Two sections address my "need of navigation" :
  • Foreign key section : the "below" tables
  • Related table section : the "above" tables
Both provides links that make the navigation a breeze.

The tool also provides a cool semi-graphical view of current table, its "above" and "below" tables around (actually what I called "below" tables are shown above, and "above" tables are shown below, but arrows between tables make it very clear to read and understand).

That's exactly what I needed.


  1. I will test it soon. Hope you share some others tools like this one! See ya Mike!