Chris Nighswonger wrote:

I have been putting some thought into the best approach for moving koha to a multi-dbms application and wanted to throw out a few of these thoughts hoping to get a discussion started with the aim to form a roadmap as this will most likely require some fairly major refactoring of code. (Not trying to "take charge" here, just trying to get momentum built up. :-)

I've done this on an e-commerce project that supported MySQL for a start, then moved to PostgreSQL for a while and finally settled on Oracle. A single layer of abstraction was used to isolate the application from the SQL. Instead of calling SQL directly, some O-O fu was used to provide a functional interface to database "primitives", such as: new - create a new row in the database via INSERT SQL select - select a row set update - modify an existing row delete - remove a row from the database Then differences like how to get the sequence number (or AUTO_INCREMENT) for a new row were handled by type specialisation. This resulted in a few classes such as the following: Database Database::Table Database::Row Database::Schema We added one more layer of abstraction to provide OO-RDB mapping, so that a database row became a simple data model object. That is another area for discussion methinks. I have worked on projects that just use the above classes with positive results. Putting it all together, we rarely had to write specific SQL for the applications except in cases requiring efficient joins or unusual reporting requirements.

1. This migration should probably begin by bringing all current SQL in koha into compliance as far as possible with ANSI SQL 1999 which it appears most rdbms support. (This may be debatable, I am not widely versed on dbms's) Note: I think that it will not be possible to implement pure ANSI SQL due to different approaches by different dbms's in handling table, constraint, etc. creation. But it should be possible to bring queries, inserts, etc. into some high level of compliance.

A great idea. I would isolate all the SQL into its own module, and possibly add some OO fu to hide the non-uniform SQL (i.e. variants) behind object methods.

2. Once koha is ANSI SQL 1999 compliant, we need to/will have identified irreconcilable differences between dbms's koha would like to support (MySQL, PostgreSQL, and Oracle are currently on the table afaik). I *think* most of these issues will fall into one of two catagories: a. Differences in how the basic db structure is created. (ie. kohastructure.sql) It appears to me that different files will have to be maintained for each dbms structure. (ie. MySQL incorporates secondary keys while in PostgreSQL indexes must be used for the same effect, etc.)

Not really a problem. You can auto-generate the entire table creation SQL for all three databases by using OO, specialisation and a simple schema descriptor methodology that you whip up in perl. Works a treat. e.g. marc_tag_structure_table => { tablename => 'marc_tag_structure', primarykey => [ qw/frameworkcode tagfield/ ], columns => [ frameworkcode => { type => 'char', length => 4 }, tagfield => { type => 'char', length => 3 }, liblibrarian => { type => 'char', length => 255 }, libopac => { type => 'char', length => 255 }, repeatable => { type => 'boolean', default => 0 }, mandatory => { type => 'boolean', default => 0 }, authorised_value => ... etc. ], indexes => { ... } } The point here is that the entire data model is described in perl, and then translated to whatever is required by a specific database driver. The added benefit: the entire data model becomes self documenting, is very easy to maintain and sits in one place. Updates to the data model can be automated. Also, tools can be written to compare the schema in perl against the schema as reported by querying the database. Any differences are flagged as errors and must be rectified.

b. Differences which allow exploitation of a particular dbms strength. It may be that these types of things could be handled by separate modules for each supported dbms with selective includes based on the db_scheme setting koha-conf.xml or perhaps using stored proceedures/functions with a common nomenclature called from the code or <insert other approaches here>....

Some like to move all their data logic into stored procedures. I prefer to keep it in the OO code and specialise as required. If you want screaming performance, use the former. If you want more power and adaptability use the latter.

3. Begin porting current code. Here, I suggest that the port be approached with the goal of ensuring that MySQL support *always* works as the port goes forward. This may be idealistic and/or unrealistic. When I did work on the webinstaller to add PostgreSQL, I set as my priority to not break the MySQL portion. This should permit a high level of compatibility with the current production installs on a forward moving basis. (ie. changes are fairly transparent) If multi-dbms mechanisms can be coded to work with MySQL, then they *should* work for the addition of any number 'x' dmbs as the appropriate ports are made for each. If it is modular enough, once the core code is ported, we should be able to add any dbms with minimal disturbances to the core.

So.... thoughts, critiques, discussion, etc...?

This kind of cross-database support should be thoroughly analysed. We already know the requirements. We already know the variations in DB operations. What needs to be done is write it all up: Requirements, Analysis, Design before even thinking of an implementation. And most importantly, addressing item 3. above, are regression tests that can be run on all three database types, so that you are guaranteed that nothing breaks as code + SQL are being developed. Test early and test often is my motto. It is one area which Koha is lacking. There is no QA and unit testing. A developer's Wiki would be invaluable for capturing design docs. Is there one available for Koha? cheers rickw -- _________________________________ Rick Welykochy || Praxis Services Don't worry about the world coming to an end today. It's already tomorrow in Australia. -- Charles Schultz