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.

Diagram illustrating the parts of a dining table

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] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!