[Koha] Some question: postgres, italian, ...

[Marco Gaiarin]

Mandi! Chris Cormack
  In chel di` si favelave...

> Koha was originally built and designed with PostgreSQL as its database

Really?! Why leave postgres in flavour of mysql?!


> backend, but for a variety of reasons was ported to mysql. So in theory
> it should just be a matter of changing the DBI connect in C4::Database
> to use DBD::Pg rather than DBD::MySQL.

It is not really true.
I hate MySQL particulary for his loose SQL parser/syntiax, that accept
nearly all. This tipically make programmers lazy. ;-)

Aparte the joke, after a quick conversion of the schema, changing
DBD::MySQL to DBD::Pg lead to some auth problem, i've to rewrote the
connect call to:

	my $dbh=DBI->connect("DBI:Pg:dbname=$database;host=$hostname",$user,$pass);


> Having said that, im sure some mysqlisms have crept into the code over
> the last 2-3 years. So im sure a little bit of code cleanup will be
> needed as well.

As i suppose, i've found a bunch of this. e.g., in 

--- ./cgi-bin/admin/currency.pl~	Mon Jul  1 16:59:55 2002
+++ ./cgi-bin/admin/currency.pl	Mon Jul  1 17:09:37 2002
@@ -31,7 +31,7 @@
 	$searchstring=~ s/\'/\\\'/g;
 	my @data=split(' ',$searchstring);
 	my $count=@data;
-	my $query="Select currency,rate from currency where (currency like \"$data[0]%\") order by currency";
+	my $query="Select currency,rate from currency where (currency like '$data[0]%') order by currency";
 	my $sth=$dbh->prepare($query);
 	$sth->execute;
 	my @results;

Postgres use " for strict escape (inside "", % is a normal character),
and ' for normal character enclosing (so inside '' % is a wildcard
matching char as supposed).
Clearly all file have to be changed, and need to be verified there's no
' character in strings, really common in italian.


And in the same file:

@@ -155,7 +155,7 @@
 # called by add_form, used to insert/modify data in DB
 } elsif ($op eq 'add_validate') {
 	my $dbh=C4Connect;
-	my $query = "replace currency (currency,rate) values (";
+	my $query = "update currency set (currency,rate) values (";
 	$query.= $dbh->quote($input->param('currency')).",";
 	$query.= $dbh->quote($input->param('rate')).")";
 	my $sth=$dbh->prepare($query);

REPLACE?! What sort of SQL instruction are REPLACE?!


Then i've gived up. ;(
dpkg -i mysql-server started installing mysql on my woody server...


I think that is possibile to port koha to other DB engine, but a great
work have to be done. I suppose:

1) porting the schema to more standard SQL format, using referential
  integrity (FOREIGN KEY)
2) do some sort of compatibility function to handle date format,
 seral/auto_increment differencies (if not just handle by DBI/DBD, i've
 never used these).
3) revising all the code checking for syntiax problem like these
  exposed, rewriting them in more standard as possible sql.
4) revise all the code to make them happy with referential integrity
  (for experience changing a schema from a db without RI to one with,
  expose a bounch of little problem/race condition in db handling code)


It is not a patch nor a little work... doable for 1.4.X release?! ;)

-- 
dott. Marco Gaiarin				    GNUPG Key ID: 240A3D66
  Associazione ``La Nostra Famiglia''                http://www.sv.lnf.it/
  Polo FVG  -  Via della Bontà, 7 - 33078  -  San Vito al Tagliamento (PN)
  gaio(at)sv.lnf.it		tel +39-0434-842711    fax +39-0434-842797

            Supporta il disegno di legge sul software libero!
          http://www.softwarelibero.it/news/news020417_01.shtml