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

[Rick Welykochy]

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