Thursday, September 16. 2004

So I was sitting on the train on the way to work this morning and it I had a brain fart. Everyone has been interested in database abstraction for a long time. There are many great products out there which do this such as PEAR DB or ADODB but all of them seem to fall short of expectations. The problem with all of them is that, unfortunately, it's really hard to write SQL which is universal enough that you can just swap out a database and have it Just Work (tm).

As I was thinking of the situation, there are lots of problems you need to overcome. The first of which is of course incompatibilites in SQL between the packages... Then, assuming you do that, you also have to overcome the differing datatypes between packages as well. What if we applied the same sorts of concepts in many file stream architectures to a database? We'd have something like this:





The idea here basically is that you abstract both SQL and data by abstracting the reading and writing of data into a simple API.. Let's say you have a table foo which has three columns A,B, and C.

A - boolean
B - date
C - String

Using this idea, what you would do is have some sort of facilities for reading and writing from a particular table. To write to the table you would do something like:

$db->foo->write(true, "August 21th, 2004", "This is a string.");

Obviously these values on face value are very unlikely to work just inserting them into a query. But they can be filtered fairly easily into something that would... Let's assume the database we are working with doesn't have a boolean type (we have to use an enum) and only accepts timestamps for dates... basically we would have to do the following type of filtering:

true -> 'T'
"August 21th, 2004" -> strtotime
"This is a string." -> 'This is a string.'

Although these examples are only one level of transformation, I'm sure there are plenty of examples which require multiple transformation operations to convert into an acceptable representation of the data... So why not implement the write functionality as a stream, where you can link individual single-purpose filters together which convert an input type of one format into an input type of another? You could then chain filters together to get from a database-specific type to a generic representation, transmit that representation to the server process and then let the sever process actually write the data to the database by applying a number of filters along the way. This would allow you to create easy to understand table structures which were put in terms of the filtering that needed to be done on each individual column:

$tables['foo'] = array('write' => array('A' => array('booleanToGeneric'),

                                                   'B' => array('stringDateToTimeStamp', 'timeStampToGeneric'),

                                                   'C' => array('stringToGeneric')),

                             'read' => array('A' => array('EnumToBoolean'),

                                                            array('TimeStamptoStringTime'),

                                                            array('StringToString')));

It seems a little complex I know, but I think its an interesting concept none the less, I'd love to hear feedback on it and see what people think. The cool thing in my eyes is that as long as its properly architected from the start implementation is fairly easy on the eyes... You implement the architecture, then you can add individual special-purpose filters to do the "real" work for you quickly and easily. I also think that with a generic representation of the data and the same sort of plugin-filter concept you can create SQL queries which even do database-specific things in the way databases were designed to do it best without the developer ever having to even know how it works.

So what do you think? Is it really a brain-fart or is there something more interesting to the concept?