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

[Chris Nighswonger]

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


-- 
Chris Nighswonger
Faculty Member
Network & Systems Director
Foundations Bible College & Seminary
www.foundations.edu
www.fbcradio.org