Table Driven Software

We've all built table driven software. In your engine, you put a bunch of potential callbacks into some data structure, perhaps a map, and call the relevant one based upon some key value. Then the calling logic that uses the engine has some structure that holds the key(s) of the method(s) to be called for some context. If you change the key(s) for a given context, then the corresponding method(s) that get called change accordingly. It's neat, clean, efficient and fairly simple to implement.

At least you'd think so.

Unless you run into one of those folks who believes that everything, and I mean everything belongs in the database.

About 15 years ago, a mid level developer was tasked with creating a table-driven mechanism to call methods based upon values returned from a query to a remote system in real time. He took the phrase "table driven" literally. After I was hired, I was tasked with diagnosing and fixing the performance problems that were weighing down the application. This developer spent a little time explaining his table driven software to me (minus the fact that it was actual DB tables) and that this was highly efficient and couldn't be the source of the performance issues.

There was a single stored procedure call named Engine which took as a hard wired argument the name of the method to call, and an arbitrary list of up to 100 pairs of parameter type and value strings. It would then look up the specified method name to see if it existed, and grab the user id from the environment and look up whether the user had permission to call said method. If so, it would parse the arguments until it hit a null, and based upon the specified types, verify that those types matched the ones configured for the specified method, build up a string representing the method call, exec it, grab the results, and spit them back as a delimited string in a single output parameter.

It looked something like this:

 Procedure Engine(methodName IN varchar2, resultData OUT clob, exceptions OUT clob, param1Type IN number Default null, param1Val IN varchar2 Default null, ... param100Type IN number Default null, param100Val IN varchar2 Default null) IS Begin ... End;

To accomplish this magic, all of the callback-method logic had to be configured inside the database as Java stored procs so that it was accessible to be called from the engine stored proc.

For added fun, exceptions were converted to text fields (to hold the stack dumps) and passed back as an additional output parameter.

Your code would then be responsible for manually checking to see if an exception had been thrown. If so, it could then throw a generic exception with the text of the actual exception that was thrown inside the code in the DB as the message field of the new exception.

Thus, you would build up a stored procedure call to the Engine in your Java code, call it, and in the database, the engine would pick apart the parameters, construct an SQL string representing the targeted method call, call it as a Java stored proc inside the DB, grab any returned results or exceptions and stringify them, grab the results in the usual way in your Java application, then manually check for and throw an exception if one occurred, and if not, pick apart the results-string.

The table(s) that controlled this table-driven engine were as follows:

 DB Table AppUserPermissions -- permissions, by user, to call specific methods id number(38,0) -- sequence/PK userName varchar2(32) not null -- name of user executing the program methodId number(38,0) not null -- FK to AppMethods.Id that UserName may execute DB Table AppMethods -- list of methods callable via the table driven engine id number(38,0) -- sequence/PK methodName varchar2(255) not null -- name of method that may be invoked DB Table AppParamTypes -- declare each parameter-type once id number(38,0) -- sequence/PK paramType varchar2(32) not null -- parameter types ('int', 'String', 'Date', ...) DB Table AppParams -- define each parameter for each method id number(38,0) -- sequence/PK methodId number(38,0) not null -- FK to AppMethods.Id (method to which param is associated) paramNum number(38,0) not null -- 1..n paramType number(38,0) not null -- FK to AppParamTypes.id (type of parameter)

And they were wondering why there was a performance problem?

To clean it up, I yanked all the code out of the database into a dedicated package. Then I converted all the "exception handling" to actual exception handling. Finally I changed all the return values from result sets munged into a single string to relevant POJOs, that could then be processed by the calling code.

It reduced the load on the database by more than 95% and sped up the relevant sections of the application by more than 99%.

The logic was still table driven, but there was no longer a dependency on database tables.

Most importantly, after explaining what I had found and what had to be done to fix it, it didn't take much effort to convince our boss that all code needed to be code reviewed by someone with more experience - no exceptions!

[Advertisement] Atalasoft's imaging SDKs come with APIs & pre-built controls for web viewing, browser scanning, annotating, & OCR/barcode capture. Try it for 30 days with included support.