OraTransplant

Using IFrame Shim to (partly) cover a Java applet

This week I had a conversation with someone who was interested in integrating Oracle Forms and JSF. They had some issues with the Forms Java applet and a JavaScript/CSS dropdown menu. They wanted to have the dropdown menu to appear in front of the applet. The default behavior of both Internet Explorer and Firefox is to always draw the Java applet on top. This is not something you can fix by setting the z-index or any other CSS property.

I was intrigued by this problem and wanted to find a solution. I can see it being a very common requirement to have a JavaScript/CSS dropdown menu in a web application that embeds Oracle Forms. This can lead the way to replacing the former Oracle Forms menu with a web based menu. Then, over time one can change menu-items that call pages embedding an Oracle Form to menu-items that call "true" web pages. This is clean and smooth path to migrate from Oracle Forms to JSF (or any other web technology) without a big-bang rewrite of all your Forms.

After some googling, I found the solution (aka trick). It's got the nice name "IFrame Shim". Some objects, like applets, select-boxes and iframe's are not actually drawn by your browser but by the operating system. These objects are always on top of the HTML elements rendered by your browser. You cannot put them below HTML elements with a lower z-index. The key to the solution is that both applets and iframe's are managed by the operating system. You can layer these with respect to each other with the z-index. You can create an iframe with the exact same position and dimension as the div (or any other element) you want to float on top of the applet. You can have the iframe sit on top of the applet with a higher z-index. The HTML (e.g. div element) can float on top of the iframe with an even higher z-index.

You're not putting any content or document in the iframe. It's just a trick to have your browser layer the HTML element on top of the iframe and the two of these on top of the applet. See for your self with a simple demo or look at the source code below. I've tested the solution with MS Internet Explorer 5.5, 6.0, 7.0 and Mozilla Firefox 2.0.

Agile databases

Summer 2005 I posted a number of articles on version control of database objects. I had some very interesting discussions in the comments of those entries.

Introducing version control and build automation for our database development suffered from some delay as other projects had higher priorities. We're about to start again with the introduction of version control and build automation.

For that, I was reading through the past entries again and noticed a trackback from another site. That page lists a very usefull bunch of links to other pages discussing the same subject. If you're interested in the subject be sure to check this site.

A very succesfull Agile Oracle project

Last year I wrote a couple of blog entries about agile programming in an Oracle world. I’m setting up the new development process for our development team of 20-25 developers. I want to make it more agile and with that comes version control and build automation.

Back then I found some articles by Robert Baillie who has been very succesfull in setting up an Agile Oracle/PHP project. Last week he presented about it at the UKOUG. He also wrote a very comprehensive article about it on his blog. A must read for anyone who wants to setup an agile development process for an Oracle shop!

Database agility

I wrote a blog entry about version controlling database objects last week. This started some interesting discussions with other Oracle professionals and made Robert Baillie write down some interesting entries at his own blog.

Since then I've continued my research on the internet and found some other interesting resources. I'll just post them to my blog again for the ones that got interested in the subject.

It all started with an article about Evolutionary Database Design by Martin Fowler and Pramod Sadalage. That led my to the Yahoo group agileDatabases. You'll have to register for a free Yahoo account to access the agileDatabases group, but it is really worth while. There are some very interesting discussions on the associated forum and there is a great powerpoint presentation titled "Database Agility" in the Files section. It's 33 slides about how to use version control and project automation for databases.

If you're interested in the subject, you definitely have to go to the Yahoo group and sign up!

Version control of database objects?

In the coming months I will be introducing Subversion to Eurotransplant for version control of all our software. This is part of a bigger plan to learn from 3GL development. Using Subversion is pretty straightforward for things like PLL libraries and FMB files, but what about database objects?

Stored PL/SQL objects (packages, function, procedures and triggers) are also quite straightforward. All these DDL script have CREATE OR REPLACE commands and will replace any previous version of the PL/SQL object.

But what about tables? You initially create these with a CREATE TABLE and during the rest of its live it might have numerous ALTER TABLE statements to change the table. How to keep these in version control?

I came up with a couple of things to consider:

Track end state of the object or modifications?
I'm struggling with this one. On the one hand I would like to store only the table structure as I want it to be in the database. This could be a XML document as created by the DBMS_METADATA package. I would then need something to analyze this XML file and compare it to a current version in the database. It would then need to make any necessary changes to the table in the database to match the structure in the XML file.
The other possibility would be to have the developer record just any ALTER TABLE statements that are necessary to change the table. This gives the developer more freedom in complex data-conversions. What about adding a new column to a table, filling this column with a concatenation of two other columns, drop the two other columns, change the new column to NOT NULL. This could be a real world conversion, but I do not see me expressing these sort of conversions in XML and then deploy them to a database automatically.
So I guess I'll go for the ALTER TABLE history in one file.

Support for automatic builds
To improve our quality and productivity I want to be able to automatically release a version from subversion to a database. We constantly refresh our development/test databases with (made anonymous) data from our production database. I would like to be able to reset a development database to the production version and then run all changes that are made in Subversion against this database. This automatic build might even include a number of automated tests.
If I go for a SQL file with lots of ALTER TABLE history (see first point), then how would I have this automatic build detect the current state of the database. If there are 10 ALTER TABLE statements in the SQL file, how do I detect which ones have already been run against this database and which still have to be run.
One solution would be to convert the original SQL file from version control to a (set of) SQL file(s) with some extra commands. They could log each executed ALTER TABLE in a sort of dictionary-view. The script could then detect which statement have already been run and can be skipped.
One other way would be to store snapshots of the table state in the original SQL file. I could again use the XML documents as created by the DBMS_METADATA package. After each (series of) ALTER TABLE statement(s) a XML document is included with the state of the table at that moment. When deploying the SQL file to a database I could first get the current state of the table, search for it in the SQL file and start from there.

Support for branching and merging
We do not deliver our software to external customers. We have only one production database running and a couple of development/test environments. This makes live a bit easier. Let's look at a scenario. We're approaching the next release. We create a branch of the mainline in Subversion to prepare for release 2.5. Developers can then continue development on the mainline to prepare for version 2.6. Version 2.5 is released to production from its branch. Then a bug report comes in that requires an immediate fix. This fix is developed on the 2.5 branch is labeled as version 2.5.1.
We now have a case where developers have already made changes to a table for version 2.6 and another developer has made changes to the same table on the 2.5(.1) branch. They 2.5.1 developer must be able to merge his changes back in to the mainline.

Special care is needed for merging these changes back to the mainline and still have the automatic build script be able to deploy it to a database. It has to be able to deploy the SQL file to a database which already has the 2.5.1 patch installed as this will be the case on the final deployment to production. It would also be very nice if the automatic build is able to deploy to a database that doesn't have the 2.5.1 patch yet, but does have some of the 2.6 changes. This can be the case on development/test environments that you do not want to refresh completely as there might have been entered a valuable test-case to that database.

I'm slowly getting there, but I guess it will take some more thinking before I have my final solution. I've also been googling for this, but couldn't really find information how others have solved these issues. Tomorrow I will be attending a mini-seminar at Amis about software development for both Oracle and Java, so let's hope I can find some answers there. There must be readers of the OraBlogs community that have experienced these same issues? If so, please leave a comment to explain how you solved it.

Update 5-aug-2005: If you're interested in this subject, you have to checkout the blog of Robert Baillie. Especially his two latest postings on the subject (here and here) are very very very interesting.

Update 5-aug-2005: Googling for the subject resulted in some more perhaps interesting links: here, here and here.

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.