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...? > > _______________________________________________ Koha-devel mailing list [email protected] http://lists.nongnu.org/mailman/listinfo/koha-devel
