December 2005 - Koha-devel - lists.koha-community.org

MySQL 5.0 problem
by Stephen Hedges 08 Dec '05

08 Dec '05

I've been trying to install Koha with MySQL 5.0, but I get an error when the installer tries to create the "issues" table. The problem turns out to be simple to explain, but harder to fix. Starting with version 5.0, MySQL treats "return" as a reserved word, and the "issues" table has a column named "return." That's what causes the error. The easy and immediate fix is to avoid using MySQL 5.0 or higher. Eventually, however, it would be nice to change the name of this column so Koha users can use all the features of MySQL 5+. -- Stephen Hedges Skemotah Solutions, USA www.skemotah.com -- shedges(a)skemotah.com

4 4

Koha2.2.4 0000-00-00 date is not valid in Postgresql
by Simon Mitchell 06 Dec '05

06 Dec '05

I am just playing around at home with Koha. Have catalogued around 800 books 95% of which belong to my flat mate. I have taken a mysql dump and loaded into a postgresql database and started debugging. I have one question, this 0000-00-00 date is not valid in Postgresql, is this being phase out to just a null value? and no more question by some info on my progress with koha on postgresql. Half way through I decided to update the Postgresql entry on http://www.saas.nsw.edu.au/koha_wiki/index.php?page=PostgreSQL I need to add a bit more to the wiki and do some more work. Anyway the things that i have changed in the perl code seem to work fine when I switch back to the mysql database. Hopefully some of this old sql will disappear with Koha 3.0 due to ZOOM. The following examples are of patch files which where required due to - postgresql being case sensitive and Koha mysql setup is not, which amazed me. - in the where clause postgresql treats "items.notforloan" as a column name so single quote was required. - order of the table names is the queries with joins needed to be changed. *Examples * *cgi-bin/loadmodules.pl* --- koha-2.2.4/intranet-cgi/loadmodules.pl +++ postgresql/intranet/cgi-bin/loadmodules.pl @@ -53,7 +53,7 @@ } sub addbiblio { - my $marc_bool = C4::Context->boolean_preference("MARC") || 0; + my $marc_bool = C4::Context->boolean_preference("marc") || 0; if ($marc_bool eq "1") { print $input->redirect("/cgi-bin/koha/acqui.simple/addbooks.pl"); } else { *modules/C4/Search.pm* --- koha-2.2.4/modules/C4/Search.pm +++ postgresql/modules/C4/Search.pm @@ -1354,7 +1354,7 @@ $data->{'datelastseen'}=$date; $data->{'datedue'}=$datedue; # get notforloan complete status if applicable - my $sthnflstatus = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.notforloan"'); + my $sthnflstatus = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield=\' items.notforloan\''); $sthnflstatus->execute; my ($authorised_valuecode) = $sthnflstatus->fetchrow; if ($authorised_valuecode) { @@ -1490,9 +1490,10 @@ my ($bibnum, $type) = @_; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare("Select *, biblioitems.notes AS bnotes, biblio.notes - from biblio, biblioitems + from biblio left join bibliosubtitle on biblio.biblionumber = bibliosubtitle.biblionumber + , biblioitems left join itemtypes on biblioitems.itemtype=itemtypes.itemtype where biblio.biblionumber = ? and biblioitems.biblionumber = biblio.biblionumber");

1 0

Koha2.2.4 0000-00-00 date is not valid in Postgresql
by Simon Mitchell 06 Dec '05

06 Dec '05

I am just playing around at home with Koha. Have catalogued around 800 books 95% of which belong to my flat mate. I have taken a mysql dump and loaded into a postgresql database and started debugging. I have one question, this 0000-00-00 date is not valid in Postgresql, is this being phase out to just a null value? and no more question by some info on my progress with koha on postgresql. Half way through I decided to update the Postgresql entry on http://www.saas.nsw.edu.au/koha_wiki/index.php?page=PostgreSQL I need to add a bit more to the wiki and do some more work. Anyway the things that i have changed in the perl code seem to work fine when I switch back to the mysql database. Hopefully some of this old sql will disappear with Koha 3.0 due to ZOOM. The following examples are of patch files which where required due to - postgresql being case sensitive and Koha mysql setup is not, which amazed me. - in the where clause postgresql treats "items.notforloan" as a column name so single quote was required. - order of the table names is the queries with joins needed to be changed. *Examples * *cgi-bin/loadmodules.pl* --- koha-2.2.4/intranet-cgi/loadmodules.pl +++ postgresql/intranet/cgi-bin/loadmodules.pl @@ -53,7 +53,7 @@ } sub addbiblio { - my $marc_bool = C4::Context->boolean_preference("MARC") || 0; + my $marc_bool = C4::Context->boolean_preference("marc") || 0; if ($marc_bool eq "1") { print $input->redirect("/cgi-bin/koha/acqui.simple/addbooks.pl"); } else { *modules/C4/Search.pm* --- koha-2.2.4/modules/C4/Search.pm +++ postgresql/modules/C4/Search.pm @@ -1354,7 +1354,7 @@ $data->{'datelastseen'}=$date; $data->{'datedue'}=$datedue; # get notforloan complete status if applicable - my $sthnflstatus = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.notforloan"'); + my $sthnflstatus = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield=\'items.notforloan\''); $sthnflstatus->execute; my ($authorised_valuecode) = $sthnflstatus->fetchrow; if ($authorised_valuecode) { @@ -1490,9 +1490,10 @@ my ($bibnum, $type) = @_; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare("Select *, biblioitems.notes AS bnotes, biblio.notes - from biblio, biblioitems + from biblio left join bibliosubtitle on biblio.biblionumber = bibliosubtitle.biblionumber + , biblioitems left join itemtypes on biblioitems.itemtype=itemtypes.itemtype where biblio.biblionumber = ? and biblioitems.biblionumber = biblio.biblionumber");

1 0