[Koha-devel] Thoughts for discussion concerning muliti-dbms approach to koha

[Michael Hafen]

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...?
> 
>