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

[Simon Mitchell]

FYI

Hello, last year I was  playing  around with postgresql and have put some
patches up on
http://www.saas.nsw.edu.au/koha_wiki/index.php?page=PostgreSQL
, but had not done any real testing.

Am looking forward to the release 3 , with all that sql moved to zebra, and
might have another play.

I have found

1. MySQL searches and db names are not case sensitive.

2. that some  sql can be changed or rearranged to suite postgresql and
still work with mysql. So just changing to a more of  ANSI sql would be a
good start.

3. I needed to change Context db connect string and added
"db_scheme=Postgres" to /etc/koha.conf

--- koha-2.2.4/modules/C4/Context.pm
+++ postgresql/modules/C4/Context.pm
@@ -408,7 +408,7 @@
  	my $db_host   = $context->{"config"}{"hostname"};
  	my $db_user   = $context->{"config"}{"user"};
  	my $db_passwd = $context->{"config"}{"pass"};
-	return DBI->connect("DBI:$db_driver:$db_name:$db_host",
+	return DBI->connect("DBI:$db_driver:dbname=$db_name;host=$db_host",
 			    $db_user, $db_passwd);
 }


I recommend using postgresql 8.1
1.  new postgresql 8.0 and above is available for windows
2. 8.0 has a some sort problem with character set.

Regards,
Simon


On 04/02/06, Thomas D <koha at alinto.com> wrote:
>
> Oops, I left out an enclosing if clause for one part of the code at the
> end
> of this section.  This is one thing need to prevent failure in the present
> usual case where db_scheme may not be included in the koha.conf.
>
> # 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) {
>     if ($nonMySQLDBType =~ /mysql/i) {$nonMySQLDBType = undef;}
> }
>
>
> Thomas D
>
>
> Quoting Thomas D <koha at alinto.com> :
> > ---------------- Beginning of the original message ------------------
> >
>
> [snip]
>
> > 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
> > }
> >
> >
>
> [snip]
>
> ---------------------------------------------
> Alinto wishes you a happy new year 2006 http://www.alinto.com
>
>
> _______________________________________________
> Koha-devel mailing list
> Koha-devel at nongnu.org
> http://lists.nongnu.org/mailman/listinfo/koha-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </pipermail/koha-devel/attachments/20060205/6dbb8a55/attachment-0002.htm>