I'd like to add to your list of potential problems something I've run across trying to get applications to support multiple dbms' especially MySQL and PostgreSQL. That is auto_increment vs. sequences. That could be a very difficult problem to work around in deed. On Tue, 2007-11-20 at 12:02 -0500, Chris Nighswonger wrote:
Hi all, 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. :-)
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.
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.) 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>....
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...?