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