Did some work on Sotonians last night. Finally dusted down the old schema and began work on a lower-level API for it. Basically, for each table I need the classic insert, modify, get and delete behaviour modelled. The biggest dilemma, however, is where to perform the error checking.

If I wanted, I could just pass stuff to the DB verbatim, and get it to report errors to me if values don't fit into the attributes they are assigned to. That can be costly, though. I don't really want the overhead of creating a DB connection if the end result is going to be fruitless.

Which leads us into option 2 - which is to provide program logic which will use the Perl regexp checking to make sure values fit before placing them inside. Makes for a much bigger API, but you can be reasonably sure that DB connections are worthwhile.

I did actually knock up a seventy-line Perl script to analyze my DB schema and provide template methods for insertion, modification, deletion and retrieval. This had type checking built-in, so if something was NOT NULL, it'd produce a check to make sure that particular attribute has been supplied as a parameter. It also spawned little regexp and length checks to ensure that INTEGERs were correct, and that VARCHARs had a sensible number of characters in them.

The problem with the generated code stems from the schema. The DB is heavily normalised, which results in the same attribute being present in a lot of other tables. In each table method, the same code was being used to check the same attribute again and again. The generated API weighed in at something like 1200 lines, which is a maintenance bitch. So, whilst my DB API generator was a nice idea ( and a hairy coding experience - nice lot of escape sequences ) I'm now thinking about taking an alternative approach.

I plan to wrap all the attribute checks into a single method, which can be called from the insertion, modify, retrieval and delete functions. I'm also thinking of parameterising those functions to enable me to have just one routine for insertion, one for modification, etc.

Still, a damn useful prototyping session, and something that ought to bear fruit regardless of it's apparent failure.