[Koha-devel] SQL Dtabase Flavour Alternate Routines and Identifier Quoting Proposal

[Thomas D]

SQL DATABASE IMPLEMENTATION COMAPIBILITY

On repeated occasions, I have expressed a concern about the hazards of tying
Koha code too much to a particular database implementation flavour.  No one
has ever seemed to oppose the idea of database neutrality where it can be
made to work.  I am not presuming that there is enough Koha development time
available in the 3.0 schedule to write and test code for multiple SQL
database implementations.  Furthermore, the value in providing code for
multiple SQL implementations in the near term may be minor relative to the
gain from using that same time for other work.

However, given that a significant amount of new code is being written and
that a significant amount of old code is being revised for Koha 3.0, I
propose that a coding guideline be adopted to minimise the work necessary
for wherever SQL code is being changed or newly introduced for 3.X.

The value here is not only for allowing provision for the functional
advantage of using other database implementation types and to promote
thinking without using MySQL blinders.  There is a larger value in marketing
to help obtain wider adoption of Koha.  Many people have well justified
prejudices against MySQL which will persist despite present and future
improvements in MySQL and despite using constraints or other features now in
MySQL.  Software adoption is seldom decided on a proper objective evaluation
of all the merits.  Decisions are informed by prejudices that need to be
addressed.  Where Koha is non-standards compliant or otherwise deficient, at
a minimum, provision for more easily enabling the future correction of those
problems ought to exist in 3.0.

There is already some incomplete code in C4::Context.pm to support the use
of alternate SQL database implementation types.  However, actual coding
practise has made this moot.  I propose that this deficiency be remedied
before the task of escaping a single implementation choice becomes too daunting.


NEUTRAL SYNTAX AND IDENTIFIER QUOTING

Sufficiently neutral syntax ought to also be adopted for SQL code to protect
against present and future incompatibilities and allow reuse between various
versions.  Quotation of identifiers is already needed to avoid
incompatibility of one Koha column name with reserved words in MySQL 5. 
Maximising database neutrality in quoting is best preserved by using ANSI
quotes, however, using them in MySQL poses problems.  One cannot rely on
universal settings in my.conf where Koha may not be the only database in use
on the server.  SQL statements for MySQL would need to be prefaced by the
ANSI quotes setting statement to preserve code reuse but that would add
otherwise unnecessary ANSI quotes setting statements which would be
unrecognised in other database systems.

SET sql_mode='ANSI_QUOTES';

There had been a report of the identifier problem on the koha list and then
again on the koha-devel list.  See the problem reported at
http://lists.gnu.org/archive/html/koha-devel/2005-12/msg00001.html and the
solution that I suggested at
http://lists.gnu.org/archive/html/koha-devel/2005-12/msg00003.html .

A means of preserving SQL implementation neutrality and avoiding the special
problems with ANSI quoting is needed.


ALTERNATE SQL DATABASE IMPLEMENTATION SPECIFIC ROUTINES

Different routines for different SQL database implementations are needed to
provide for all variants without the peculiarities of one interfering with
another.  Even where a single ANSI format might be used, different routines
will avoid the special problem for using ANSI quotes with MySQL.  MySQL
backticks can be used instead of ANSI quotes in a special routine for MySQL.

A parameter is needed for setting the SQL database implementation type to
test against for determining which implementation routine to use.  Given the
legacy for Koha in MySQL, that ought to be the default, despite the logic of
defaulting to the ANSI standard otherwise.

Only one PostgresSQL routine would be needed if the Postgres 8 statements
could be successfully written without referencing the unavoidable schemas. 
I never succeeded at that when I was rushing to have running code while
Postgres 8 was in beta.

C4::Context.pm has some rough code for reading  a db_scheme value if set in
koha.conf, however, values returned by db_scheme2dbi() for use in DBI would
be insufficient for distinguishing between different versions of the same
SQL database type, such as the distinction between Postgres 7 and 8.  I
propose creating a virtual config variable instead before db_scheme2dbi() is
called with its effect of flattening the value of db_scheme.


ONLY A PLACEHOLDER

Of course, as I have indicated, there is insufficient time for implementing
the actual different SQL code for using separate database implementation
types.  This guideline is for a placeholder to reduce the work required for
implementing SQL code apart form merely MySQL.


PROPOSED CODE FOR GUIDELINE

# Modification for Context.pm
        # Quick hack for allowing databases name in full text
        # from authorised value list
        if ( $1 eq "db_scheme" ) {
            $retval{sqlDBType} = $2; # add virtual config variable
            $value = db_scheme2dbi($2);
        } else {
            $value = $2;
        }
        $retval->{$1} = $value;



# At the top of any C4 modules or $KOHA/admin/ 
# scripts requiring SQL database access:
# Obtain a value for the SQL database implementation type (flavour)
$my $sqlDBType = C4::Context->config("sqlDBType");
my $nonMySQLDBType = $sqlDBType;
if ($nonMySQLDBType =~ /mysql/i) {$nonMySQLDBType = undef;}


# Alternate routines or routine placeholders for various SQL database types
if ($nonMySQLDBType) {

    if ($nonMySQLDBType eq 'postgres7') {
        # Postgres 7 specific code
    } elsif ($nonMySQLDBType eq 'postgres8') {
        # Postgres 8 specific code
    } elsif ($nonMySQLDBType eq 'oracle') {
        # Oracle specific code
    } else {
        # ANSI code
    }

} else {
    # MySQL specific code
}


CHANGE TO CODING STANDARDS AND GUIDELINES

If there is general agreement, I propose that the proper quoting of
identifiers and a preferred standard form for alternate SQL database
implementation types such as I have described should be included on
kohadocs.org along with the rational.  Koha Development Team. Koha coding
standards and guidelines for contributors.
http://www.kohadocs.org/codingguidelines.html .


Thomas D

---------------------------------------------
Alinto wishes you a happy new year 2006 http://www.alinto.com