Re: [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 <address@hidden > 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 <address@hidden> :
> ---------------- 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
address@hidden
http://lists.nongnu.org/mailman/listinfo/koha-devel