[Dbixx-devel] The plan for the future of DBI++

[Anders B. <an...@we...>]

I've been using the ruby port of the dbi++ library for a project for some time 
now. And i've come to see some flaws in the way, that the library works. Some 
of them are implementation flaws (which means trivial to easy to fix) others 
are design flaws (which means throw away a lot of code and try again).

To start with a small and easy thing to fix (unless I overlooked something): 
Having a class that extends another class will often mean that the extended 
class will appear in one table, whereas the extending class will appear in two 
tables. This is of cause aside from the fact that there may be many other 
associations which make some of the class attributes appear in separate 
tables. For example a User with a list of AccessRights will most likely have a 
user table and a accessrights table in the underlying database.

This means that we will have to have two sets of load methods. I've already 
begun implementing this and the general idea should be visible from what is 
already in cvs. The ruby port has this implemented in 0.0.2 and the java port 
has a general idea of the implementation.

The idea is described in a now closed (maybe it should be reopened) feature 
request (no. 554960). You will still be able to find it in the tracker on the 
project page.

This is a small issue. There are larger things. Design flaws.

Firstly, the SQL Abstraction api should be used. This is not a big thing. 
Actually this is mostly about throwing almost everything in the 
SybaseInterface away and replacing it with a few calls to 
sql.SQLDatabaseInterface and sql.Select.

The basic idea is that it is the responsibility of dbixx.DatabaseInterface to 
load, save and remove persistents from a database in a transparent way, with 
the help of the PersistentFactory, which knows the structure of the db. It is 
the repsonsibility of dbixx.sql package to provide access to sql statements 
while still making sure that the individualities of different sql databases 
are kept hidden from the user of the api.

This I forgot as I was coding at the DatabaseInterface. Therefore everything 
was coded twice... This should be fixed. All the code concerning loading could 
very well cut down to very few lines:

<pseudocode> <!-- well, actually almost real code -->
SybaseInterface.SybaseInterface (...)
   sqldbi = dbixx.sql.SQLDatabaseInterface (...) ;
}

Persistent[] SybaseInterface.load (table, search, p) {
   select = sqldbi.getSelect () ;
   select.column (table, "*") ;
   select.where (search) ;
   sql = select.toString () ;
   return doLoad (sql, p) ;
}
</pseudocode>

 From this point on the next goal is to make sure that the damned thing 
actually works. The dbixx.sql.* was tested rather thoroughly before it was in 
the dbixx package (it was removed from an internal (proprietary) software 
package into the dbixx package). Nothing but the package name was changed, but 
this probably wasn't done in all places and therefore broke something. We need 
to restest everything and make sure it still works.

Next up: sequences, booleans, stored procedures, triggers, views, and ddl in 
general.

Nikolaj (pan...@us...) has talked to me about the existence 
of boolean handling in the code, I never saw it (and didn't look very hard). 
If it's not there then it needs to be implemented.

Next, more databases: some things are not available in all databases. Like 
MySQL is nearly not a relational db. For those we need to make an attempt to 
implement suff ourselves. I haven't thought hard about this but maybe a 
meta-table (or a set of meta-tables) could describe the stuff missing from the 
database, like stored procedures and views.

Also, Sybase has no sequences or autoincrements, this could be emulated with 
triggers, but the purpose of the SQL Abstraction layer is to make such things 
transparent. So i propose a Sequence class that will act in the same way as 
Oracle sequences. This, we should be able to implement via meta-tables in 
Sybase, MySQL and just use the build-in sequences or Oracle.

The DatabaseInterface has no save method implemented yet. I have implemented 
the save method in C++ and we should be able to use what I have learned there 
to make a nice Java implementation of the save method.

<pseudocode>
SybaseInterface.save (table, persistent)
   persistent.getAttributes (table, updateCriteria) ; /* fetch attributes and 
update criteria for this table */
   save (table, attributes, updateCriteria) ;
}

SybaseInterface.save (table, attributes, updateCriteria) ;
   update = sqldbi.getUpdate () ;
   update.set (attributes) ;
   update.where (updateCriteria) ;
   sql = update.toString () ;
   execute (sql) ;
   /* if this fails then the entry doen not exist yet and we must insert */
   insert = sqldbi.getInsert () ;
   insert.set (attributes) ;
   sql = insert.toString () ;
   execute (sql) ;
}
</pseudocode>

We may consider having a SQLDatabaseInterface, which SybaseInterface, 
MySQLInterface and others in the dbixx package (not the dbixx.sql package) 
could extend and thereby save ourselves a lot of code. Most of the code will 
me in dbixx.sql anyways...

Some more futuristic ideas: xmldbmsi (extended modelling language database 
management systems interface)

Along with the sql abstraction layer an xml abstraction layer would be nice 
too... Before even attempting to lay the description of this out we need to do 
some research on xml databases (whether any of them are in a state where 
supporting them is possible). Unless of cause some of you know something about 
xml databases and would like to share it with the rest of us.

I need not speak highly of xml to you (I don't think I need to - anyways). It 
would be very nice indeed to have xml database support in this library. But, 
what interfaces are there to xml databases. Is it at all possible to store 
classes via the dbi++ api, or do we need some form of extension to the api 
before being able to store data in an xml database? How about xml queries? I 
know very little of these things and need to study it before being able to 
tell anything about it. If you have any knwoledge of xml databases please come 
forward and tell about them.

Finally, if you have any ideas or suggestions or questions... or anything, go 
ahead and write to the list! This is what the mailinglist is there for!

-- 
Anders Borch                   Web Forge
Software Engineer              Jenagade 22 G, 2300 Copenhagen S, Denmark
an...@we...            www.web-forge.dk