DOWNLOAD Oracle Database XE Python cx_Oracle

TAGS python, scripting, xe, All

Case Study: From Forms to Python

How one Oracle Forms shop fled client/server for wider Python horizons

Published February 2008

Starting with Oracle 6 and Forms 3.0, we have been an Oracle shop, and had come to critically rely on Oracle Forms for our data entry and query needs. Our environment consists of a group of about 10 clerks and management users using a population of approximately 50 Oracle Forms to query a 2GB OLTP database. Over the years, we kept current with database and Forms upgrades, finally ending up with fairly standard 6i forms, deployed in the classic client-server mode: a full set of forms deployed to each user's desktop PC.

Because of the de-support of client-server mode in post-6i versions of Forms, we had a decision to make in regard to our body of work. But this wasn't entirely unwelcome, for over the years, we had come to recognize several shortcomings with the client/server deployment; we actually welcomed the chance to improve but were concerned about disrupting our now smoothly functioning and well understood operations.

We saw were three specific problems with client/server mode:

• Each client code update meant an IT tech had to physically visit each user's workstation to update their local copy of Forms. We made this "sneaker-net" approach more palatable by locating the master copy of our forms on a shared Windows directory, but getting time from each user to do the upgrade was awkward.
• Incomplete updates meant we had to manage two versions of the client code in use simultaneously for a time, which complicated bug fixes.
• We occasionally encountered different behavior in the same form running on Win95 and WinXP, which complicated development.

Furthermore, as Web-based applications became more common in other aspects of the business, it was increasingly attractive to integrate our Forms applications to ease training and improve productivity. So we were quite certain that some sort of Web conversion was necessary for our forms. Of course, many other organizations faced these same sorts of challenges, so it was time to examine our options.

Possible Solutions

Each of the following options initially seemed overwhelming and we struggled with how to proceed:

Move to a New Version of Forms. We were dissatisfied with this option because to the additional complexity and GUI-heavy end result of this sort of conversion. Yes, it would have been the easiest in terms of letting us keep most of our old code, but there still would have been some rewriting necessary to deal with operations that didn't map from client/server mode to Web deployment. Also, we would have had to set up a separate middle tier application server and address the cost and administrative complexity going forward. We recognized the users of our forms were far more concerned with data-entry speed and basic functionality than UI niceties, so we ended up ruling out Forms 9i and successors.

Use a New 4GL Tool. This was quickly rejected due to cost and learning curve concerns. We would be far better off with Forms 9i or 10g than starting over with a brand new tool (e.g. Zope, Twisted, Zango, TurboGears).

Perform Automatic Conversion. Early on, this was the way we thought we would most like. But as we explored different alternatives, we became disenchanted with the complexity and less-than-perfect conversions that would probably result. The technology behind several of these products was impressive, but over time, we came to appreciate how difficult it would be to cleanly generate an equivalent form without a lot of manual work.

By default, each Oracle Form comes with an impressive amount of default functionality provided by the framework, and even if much of it isn't used in practice, the conversion tools have no way of "knowing" what is important to each customer and thus have to map each construct, used or not. This leads to complex, and probably tough-to-maintain, generated output forms.

Further, some of these conversion products seek to retain the forms architecture in the new language, so training of new staff is complicated - engineers have to be trained on Forms and the output language, even if Forms isn't really in active use in the group any longer. Forms coding becomes a sort of "Latin" one has to know to do one's job!

The Manual Rewrite Option. We started (and ended) with this option. It had the advantage of giving us a lot of flexibility in how to proceed, but had several disadvantages. A very large danger was the project scope: because of an Oracle Forms' declarative (i.e. 4GL) architecture, the interaction across the elements of the code can be tough to reproduce in a 3GL language. An engineer could easily loose sight of the big picture and spend weeks converting a single Form, making the cost of the conversion very high. Previous experience doing software migrations taught us to beware of projects with lots of moving parts or "big-bang" delivery schedules, so we started to look for ways to reduce the overall scope of the project.

Factoring Out Application Tier Code

Thinking about the architecture of typical forms, we recalled some early Oracle Application Server materials that pointed out that programs could be functionally visualized as a combination of code in the database, application, and presentation tiers. Of these three groups, the database interaction and application tier code (e.g. Oracle Forms Program Units) looked to be the major stumbling blocks in a conversion. We also recalled an Oracle Forms advertisement from several years ago that showed the dragging and dropping of PL/SQL code from the Form to the database and the reverse.

After some reflection, we decided to pursue a near term goal of moving our application tier code out of the forms and into a PL/SQL database packages while continuing to evaluate alternatives for the final UI. This meant the creation of packages for each group of forms, and the movement of program units into procedures in one of these new packages. As this was done, we updated each Form Program Unit to call the procedure in its new location. Specifically, we created one-line program units with a call to the package.procedure. This was fast and effective (and transparent to the users) in almost every case. (Note: in some forms there were still a few program units devoted to UI functions, which we properly did not attempt to push into database packages).

So we lost nothing by doing this, and gained several advantages:

• It was self-contained so we were able to work on this in parallel with the evaluation of the different migration alternatives.
• Code re-use and refactoring across forms was achieved with modest effort.
• We ended up with much smaller Forms, both easier to deal with as Forms and much easier to convert in the future. (The irreducible remainder of code that remained in the Form was largely UI-related.)
• We were able to add unit-test procedures to the packages to increase code quality. (Testing the same code as a Program Unit in a form would be much harder.)
• Debugging was easier because of the clean separation of logic.
• We could much sooner detect schema problems via package invalidation, as opposed to latent runtime errors lurking in forms. Because the database can see the code that was formerly "hiding" in program units, any definition problems are brought to the fore immediately when a change is made that breaks them. This heads off bug reports, or at least makes them much quicker to fix.
• We were able to take advantage of some 10g features to re-implement certain functionality (e.g. external tables).

One question that may be in many people's minds is: "Why use PL/SQL in the database instead of Java/Python/etc. in the application server?" We considered this option, but it was too risky for us. Our code was already PL/SQL, and we were confident it would work identically in the server. (Indeed this was the basis for that print advertisement for Oracle Forms several years ago). Because we were greatly interested in reducing the scope of the changes we were making (to reduce project risk), once we had the new packages compiled and Oracle Forms updated, we were able to certify our database tier and much of our application tier before we had even picked our final implementation methodology. After the intermediate update was pushed into production and worked perfectly, we had greatly reduced our workload and risk for the remainder of the migration.

If we had attempted to immediately rewrite the code, we would have faced the challenge of interfacing it to the existing forms, and making it perfect enough to replace the existing functionality. With changes of that magnitude, bugs are unavoidable. We just didn't want to deal with the extra bugs that would come from this strategy, as we needed to stay in our user's good graces for the later migration work.

That said, as we went about designing the PL/SQL packages to contain the migrating code, we were careful to logically separate the database tier from the application tier, and so ended up with separate groups of packages of each type. Of course, physically, all this PL/SQL code was resident in the same database PL/SQL engine, but logically they were distinct and the calling patterns we established between the modules respected this. At some vague later date, those application tier PL/SQL packages would be the first candidates for rewriting in Python for use in the web server, should we want to pursue that strategy.

Product Stack

After months of considering alternatives and doing small pilot projects, we decided on the following components for our project. One very important detail about this stack: its total cost today is $0. Certainly, it's possible to spend more, but without explicitly trying to keep costs down, we were pleasantly surprised to find our preferred selections ended up costing the least.

Linux. Linux is a stable and inexpensive platform. It has been much more trouble-free than Windows machines in the same environment. In preparation for this article, I looked at a Suse Linux machine running an Oracle database: the uptime of the machine was 768 days, or more than two years. We later shifted development to Ubuntu 6.06 (Dapper Drake) and Ubuntu 7.04 (Feisty Fawn) and both have since also worked extremely well for us. Highly recommended. (Note however that Ubuntu is currently supported only by Oracle Database Express Edition.)

Apache Web Server. Certainly the default Web server choice, but one we were happy to make to reduce risk. We found it works well with Linux, had much documentation, and met our needs without complication. Our usage of the Web server to date has been very generic, so some other product could be easily substituted. A full Python solution here would be philosophically very attractive, but so far can't make a strong business case to do the work to swap out Apache.

Firefox. Firefox worked well for us during development. We of course did some QA work from Internet Explorer to make sure we hadn't been bitten by some of its famed incompatibilities. We did encounter a few of these midway through the project and worked around them changing the code slightly. We also later investigated Opera as a lighter weight alternative to Firefox and that has been equally acceptable.

Python. We considered both Java and Python for our implementation language. Early on, it looked as if Java would win because many of the automated Form conversion technologies were were exploring generated output in Java, so the path of least resistance would be to carry on with Java for any related tasks and new development. Well, this was a problem because we just didn't like Java, with its large IDEs, fussy syntax, and windy, hair-splitting library calls.

Casting about for a better solution, we noticed programming pundits like Bruce Eckels saying very positive things about Python. For us, it was love at first sight. Python's interactivity, clear, charming syntax, and extensive library support made us confident we had the right platform to do almost any sort of application coding. In particular, Python's late binding behavior (aka "duck typing") really helped us map some of the Oracle Forms declarative constructs to Python classes. Highly recommended as well.

CGI. There are several Python Web platforms we briefly considered. But given our still-developing proficiency with Python, we were leery of our ability to select the right one, and reluctant to climb the formidable learning curve. While we can probably be accused of a case of "not-invented-here" syndrome, we also strongly wanted to retain full control of our project, so we made the conservative choice to use CGI. This will no doubt be controversial among some readers of this article.

We selected CGI because it was well proven technology and we found plenty of documentation to assist us in getting our prototypes working. We recognized that scalability would be lacking, but for our limited population of users, this was not even remotely a problem. We also recognized that once the system was working well, we could safely explore fast CGI techniques (e.g. mod-python) to speed up the system and reduce system load.

HTMLgen. This is a Python library that performs dynamic generation of HTML. As one observer commented, HTMLgen is the package you would write yourself if you had the time to design one. It's very good, and we've grown to love it. While the documentation could be better, studying the sample code is a workable replacement. Once you see how HTMLgen pages are defined, the code is quite compact and readily sharable between programs. HTMLgen is extremely modular so you will quickly see ways to generate portions of web pages that can be recycled to provide uniform look and feel across your applications. We did several months of low-key experiments to test major constructs we knew we would need to map our forms, and then built Python modules to implement those. Those underlying modules keep improving as we learn more things, but the calling application code doesn't typically change much.

Oracle Database XE. Mid-way though development we changed from Enterprise Edition to the XE platform and were very impressed. XE running with all the components mentioned here easily fit on a development laptop with only 512 MB of memory, with room to spare. Also, aside from the documented differences from Standard and Enterprise Edition, we found no incompatibilities to spoil our experience. Highly recommended.

cx_Oracle. This is the component that provides connectivity from Python to Oracle, by implementing Python's DB API spec. While there are several modules of this sort, cx_Oracle is today overwhelmingly the preferred choice. It uses OCI so performance and feature support are very good. Running within Apache worked well once we sorted out environmental issues, namely how to specify the several environment variables Oracle requires. This was a challenge because Apache is started by root, which doesn't normally have the shell variables ORACLE_HOME, ORACLE_SID, etc. We solved this through explicit settings in the Apache configuration files, but this does create a small maintenance problem if the settings ever change.

Forms Conversion Methodology

Database Access. After a bit of experimentation, we settled on an approach that wrapped each Oracle table with a Python class implementing the basic CRUD (Create, Read, Update and Delete) routines. These all subclassed a SQL class built on cx_Oracle that provided core routines for these operations. Once queried from Oracle, we stored the data in Python dictionaries, with the column name as the hash key. This facilitated processing for display and updates and made for elegant code.

We also provided functionality to handle database log-in. Since we were running in CGI mode, we had to reconnect to the database whenever a form action required. We handled this by storing a single cookie in the user's browser to retain login information after it was validated by the server from our initial login program.

We also had routines to invoke Oracle package procedures and functions. These were critical to the project because they were the bridge from our CGI/Python world back to the stored procedures we had earlier moved from the Oracle Forms into the database. So where we originally had a button or "hot key" in an Oracle Form, we were able to create an action button in a CGI form and end up calling the very same back-end procedure. Our users loved the fact that the functionality was identical between the old and new forms, and our debugging was minimal.

There are of course some Python modules out there that will generate some of this code automatically, but in defense of the do-it-yourself approach we embraced here, the critical core SQL class file ended up being all of 600 lines long, with some of that being comments and unit-test code. The individual table files that subclass the SQL class added perhaps another 250 lines, more or less depending on complexity. Most of these lines were simple column data and unit-test code. The final form-level programs were only 20-300 lines long, depending again on complexity.

Learning Curve. We approached this as an R&D effort, starting with the smallest forms first, and repeatedly modifying them as we figured out better techniques. This had the welcome effect of actually shrinking our forms over time, and making them run faster. While keeping in mind Knuth's maxim that "Premature optimization is the root of all evil", we did keep one eye on efficiency, because we were conscious of all the processing that was happening to prepare each screen, especially compared to the Oracle Forms situation where a dedicated Runform instance is handling a compiled Oracle Form with opportunities for local caching of database data. In contrast, because Python is interpreted, with each new screen, we were loading and compiling several substantial Python modules, in addition to whatever SQL calls were needed to make to gather the latest data to display. Nevertheless, the maturity of all our platform choices showed itself and we detected no notable delays in wait time at the browser level.

Methodology. To actually create each form, we analyzed the flow that was occurring in the original Oracle form. Luckily for us, our forms were either simple query forms, or else they progressed linearly or hierarchically through a series of tabs. We had earlier eschewed relying much on Oracle Forms ability to move randomly between blocks, because this made training and documentation hard. Once the navigation was understood it was simply a matter of using HTMLgen to create matching HTML tables (for simple display) and CGI forms (for input or modification needs). All actions were reduced to discrete HTML "action" buttons tying back to either a procedure in a database package or a Python method in the form or a support module.

To promote maximum code re-use, we wrote a button-handling class to handle the CGI form actions. We worked up a dictionary structure for each form that contained the valid buttons that could be received, and used this both for form generation and for analyzing the reply. We classed each button as one of several types: (i.e. call database PL/SQL, perform a record print, call Python code, etc). The button handler parsed the incoming CGI form and performed the relevant action. In essence, it performed a lot of the subset of Runform work we needed to map our forms, and it took up fewer than 200 lines of code.

Output-only forms were of course much easier. Their interaction was mostly canned and usually amounted to call a stored procedure to write the eventual output on the database server. For input forms, things were more challenging. In particular we had trouble with input forms that needed custom local code. We solved these cases by subclassing the button-handling class to extend it with the custom code needed. The trick then was to get the button handler to know how to call this code. We accomplished this by updating the button dictionary at run time with the address of these new routines.

HTMLgen comes with several "document" styles, which serve as frameworks of varying complexity which you can decorate with your particular content. Development is quick if you can use the standard HTMLgen document types. Larger projects will probably want to develop their own, with appropriate graphical headers, navigation buttons, etc. Once you have nailed down these decisions and built several forms, it takes just minutes to derive new small, single screen form.

Example

Here's a complete example of how this all looks in practice:

Here is the code behind the example:

#!/usr/bin/env python

# This is demo code for a single page form that provides both user input, output and database actions.
# Forms such as this are typically executed multiple times:
#	First, on initial invocation (to display current status)
# 	Then, one more time in reponse to any user actions.
# The user can navigate away from this screen at any time by selecting a new form from the menu
# available in a separate frame.


import HTMLgen

# This is our login class which contains a program to accept user login credentials and verify against
# the Oracle database.  It also has code to manipulate a cookie we use to preserve credentials across
# form executions.
import Login

# We have factored out the repetitive elements of building up these forms into a small, but heavily used module
import ButtonHandler

version_dt_tx = '2008-01-01'

# Here we define a dictionary of actions the form will be able to handle.
# Element 0 is the key to the tuple
# Element 1 is a label for the button
# Element 2 is a keyword telling the form what code handler to use when the button is pressed
# Element 3 is the name of the handler code (e.g. database stored procedure name or custom python code)
button_db = {
	'bound_dates': ('bound_dates', 'Bound Dates', 'update_bound', 'close'),
	'database_action_1': ('database_action_1', 'DB Action 1', 'synch', 'database_action_1'),
	'database_action_2': ('database_action_2', 'DB Action 2', 'asynch', 'database_action_2')}

# Each screen is provided by a single high-level proceedure
def showMainForm(msg_tx):
	# HTMLgen uses template documents.  Here we select a simple one we will build upon.
	doc = HTMLgen.SimpleDocument(title = 'Simple Oracle Database Action Triggered from CGI',
		cgi = 1,
		bgcolor = Login.form_colors['user1_update'])
	title = HTMLgen.Heading(3, 'Section Header' , align='center')
	doc.append(title)
	form = HTMLgen.Form('demo1.py')

	# We typically organize the form into stanzas or sections.  HTMLgen doesn't
	# require this, but we found it easiest to map each Oracle Forms block into an
	# section set of by HTML <HR> statements.

	# This is one section of the screen.  It can contain any valid HTML you like.
	# Here just call our shared code to build up an editable date range
	# and ask it to include the two named buttons.
	part1 = handler.prepareSection('Demo Section',
		'Adjust this date range to enclose the period of interest.',
		'bound_dates', 'database_action_1', 'database_action_2')

	# Typically, multiple parts are built but here we show just one to save space
	lst = HTMLgen.OrderedList([part1])
	form.append(lst)

	# The document needs a formal submit button, which we use to provide the ubiquitous cancel option
	form.submit = HTMLgen.Input(type='submit', name='cancel', value='Cancel')
	doc.append(form)
	doc.append(HTMLgen.HR())
	doc.append(HTMLgen.P())
	doc.append(HTMLgen.Text('Message: %s' % msg_tx))
	print doc

if __name__ == '__main__':
	# Here we ask that our login cookie be retrieved from the browser and the session re-established
	# If the user hasn't authenticated, the this statement will fail and provide an HTML failure message
	login = Login.Login('reconnect')

	# Here we invoke our common code, providing the login credentials,
	# button dictionary and optional Oracle package information
	handler = ButtonHandler.ButtonHandler(login, button_db, 'user1', 'otn_demo')

	# This looks for a CGI form action and attempts to match it against the button dictionary.
	# If it matches, the action is performed
	msg_tx = handler.processForm()

	# We always display our single page form, updating the message line with each CGI round-trip.
	showMainForm(msg_tx)

Problematic Constructs. As we studied our current forms, we recognized several usages that wouldn't readily move to a Web-based model, and some rethinking was required in dealing with unsupported constructs.

Menus. Oracle Forms has the concept of menu modules, where you can define the relationship between Forms and Reports and have these items inserted into the Runform Windows menu. We handled this functionality by employing HTML frames, with the leftmost side of the screen devoted to a menu frame. Within this frame, a yet another short HTMLgen Python program called "menu.py" is run to provide a list of hyperlinks to the rest of the Python CGI Forms. The called programs run in the right-hand frame and come and go without disturbing the menu.

While we haven't gotten around to doing this yet, because we know who the user is via the cookie set during login, it would be a simple matter to tailor the menu list by querying the database for the appropriate programs for a particular user.

Long Running Jobs. One aspect of Forms that we really struggled with was what to do with long-running jobs. While most procedures were essentially instantaneous, we had certain important extract routines that took a variable amount of time, anywhere from several seconds to several minutes on occasion. When using Oracle Forms, our users didn't really mind, and were used to starting these jobs normally via a button press and doing something else for the duration of the run until the Windows hourglass indicated the routine was finished.

In a CGI environment however, this wasn't viable – if the routine wasn't instantaneous in all cases, we were in danger of eventually timing out in the web server. So to handle this, we added a Python function to our SQL class to call the desired procedure via the DBMS_JOBS facility. This let the same routine execute either synchronously or asynchronously, simply by adjusting the class method we called in our SQL class. To keep the user informed, for synchronous calls, we passed back a success/failure message, and for asynchronous calls, we passed back the job number in a message indicating background execution. To let users monitor these operations, we also provided a form to display current user jobs. This was a simple 60 line program with a meta tag specifying AutoRefresh every three seconds.

File Upload/Download. We also had several examples of file upload and download to deal with. Users almost daily obtained new data files, placed them in a local directory and used a button on an Oracle Form to load them into the database. They would reverse this process when they wished to create a data extract, for a mailing, for example.

We were able to re-engineer this process by setting up a Samba share from working directories on the database server that were mapped to their individual Windows Explorers. We defined these working directories to the Oracle server, and then we used the External Table feature to read the data files, all in response to the same button they were used to from earlier training. Output worked similarly, but we used the UTL_FILE to perform the file system writes in this case.

Local Printing. To solve this, first asked users to indicate which screens they needed to print. These were typically data-rich summary screens. So we created a custom Python routine for each of these screens, all formated and optimized for character-mode printing. We added this to the class defined for the relevant database table. When users click on an on-screen button, this Python routine executes within Apache and creates its output on the database server, with a unique name tying it back to the user. The user then is able to load the print file locally from the Samba share (via notepad or equivalent) and print it to any printer Windows is aware of. This approach worked well, and in fact solved a problem we had with GUI mode screen-shots – as screen resolutions creep ever upward, we had complaints about the time, paper and ink consumed with printing screen shots. Oftentimes the desired screen region wasn't even visible in the printed output. So this character mode solution solved all that, since we could optimize the layout for the known size of the paper available to us.

Reporting. Reporting was a challenge in this approach. Previously, we had done a lot of our reporting through SQL*Plus on the client. We looked for some open-source alternatives, but didn't find anything mature enough to be comfortable with. So we ended up creating some Python routines to address common situations in our reports and rewrote them as individual Python scripts. This wasn't the best solution we could have imagined, but it did work well, once we had worked out how to map Sql*Plus's declarative format to procedural Python. A bonus to this approach that we plan to implement in the future is to move the driving SQL statements out of the report and into a separate DB module, where we can write unit-tests to verify functionality.

Costs

Looking back, we learned quite a bit from this project.

Time Spent. As with most software projects, this one took much more time than we thought at the outset.

Performance Change. We retained the same server through this conversion, so we could compare before and after. In one hand we were potentially worsening the situation in moving code from the client to the server, as well as running our web server on the database platform. But we were very satisfied with the overall performance; OLTP operations occurred as fast as the screen could be redrawn. Investigating why this was so, we realized we were adding CPU load to the database machine, which is typically limited by IO -- specifically, the full-table-scan operations that come from various reporting operations. Essentially, we were adding load which consumed the underutilized CPU resource, and removed some network latency, so speed was comparable or even better than the client/server situation.

Maintenance Costs. Maintenance costs dropped, or rather, we were able to accomplish more with the same resources. Since our fixes and testing were centralized, we recovered time formally spent deploying fixes and wrestling with MS Windows. All this time and more was spent in building unittests for the code. This, along with good design, is the key to good Python code – extensive, thorough unittests to anchor code to business reality. We still need to do more in this area, but we love the reassurance that comes from passing unittests after some code changes.

Another aspect that we found valuable with our small staff was the ability to support an old and new version of a form in the menus. Instead of forcing users to cut over to a new or trial webform, we could put the new version in the menu with a slightly different name, and allow users to try them out to give us feedback. This sort of operation took only minutes to do or undo and made us more popular with our users.

Conclusion

Overall, we were very happy with how this project turned out. Certainly, not every business would agree with our selection of functionality over flash, but it was the right call for the small business paying the bills for this project. We were particularly pleased that we were able to marry Python with Oracle Database 10g, and look forward to further refining the system to take advantage of new 11g features. With Python's delightful syntax and extensive library support, combined with 10g's extensive database feature set, it goes a long way toward making programming fun again. Our Forms live on. Now we just have to figure out what to call them.