Back to « KohaProject Main Page
Please update this page with any info or links about how to Koha on Postgresql.
I am looking at the existing Mysql sql queries to see how they can be modified to work on both mysql and postgresql.
Some examples.
Ran a conversion script change column type from char to varchar, this fixed a problem with extra spaces in query results.
Created concat function, postgresql concat operator is ||, postresql example select tag||subfieldcode from marc_subfield_table limit 10; see this site to create this function http://www.tonymarston.net/php-mysql/postgresql.html
Postgresql has function-based indexes, so this one was created to speedup marc search. create index marc_subfield_table_tag_subfieldcode on marc_subfield_table (concat(tag,subfieldcode));
Run the analyze; command at sql prompt after importing a mysql dump, this updates internal stat and make queries run faster.
The following is the contents of patch files I have used to fix the perl code for postgresql. These changes seem to work with mysql ver 4.0.24
a patch can be applied using the patch command, -b creates a backup file.
patch -b Circ2.pm cir2_pm.patch
```modules/C4/Acquisition.pm```
--- koha-2.2.4/modules/C4/Acquisition.pm
+++ postgresql/modules/C4/Acquisition.pm
@@ -112,8 +112,7 @@
where basketno='$basketno'
and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
=aqorders.biblioitemnumber
- and (datecancellationprinted is NULL or datecancellationprinted =
- '0000-00-00')";
+ and (datecancellationprinted is NULL)";
if ($supplier ne ''){
$query.=" and aqorders.booksellerid='$supplier'";
}
@@ -821,13 +820,13 @@
my $strsth;
if (!($branch eq '')) {
- $strsth="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
+ $strsth="Select distinct * from aqbookfund,aqbudget where aqbookfund.bookfundid
=aqbudget.bookfundid and (aqbookfund.branchcode is null or aqbookfund.branchcode='' or aqbookfund.branchcode= ? )
- group by aqbookfund.bookfundid order by bookfundname";
+ order by bookfundname";
} else {
- $strsth="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
+ $strsth="Select distinct * from aqbookfund,aqbudget where aqbookfund.bookfundid
=aqbudget.bookfundid
- group by aqbookfund.bookfundid order by bookfundname";
+ order by bookfundname";
}
my $sth=$dbh->prepare($strsth);
if (!($branch eq '')){
@@ -856,8 +855,7 @@
my $sth=$dbh->prepare("Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
from aqorders,aqorderbreakdown where bookfundid=? and
aqorders.ordernumber=aqorderbreakdown.ordernumber
- and (datecancellationprinted is NULL or
- datecancellationprinted='0000-00-00')");
+ and datecancellationprinted is NULL");
$sth->execute($id);
my $comtd=0;
my $spent=0;
--- koha-2.2.4/modules/C4/Biblio.pm
+++ postgresql/intranet/modules/C4/Biblio.pm
@@ -2138,7 +2138,7 @@
# When MARC is off, ensures that the MARC biblio table gets updated with new
# subjects, of course, it deletes the biblio in marc, and then recreates.
# This check is to ensure that no MARC data exists to lose.
- if (C4::Context->preference("MARC") eq '0'){
+ if (C4::Context->preference("marc") eq '0'){
my $MARCRecord = &MARCkoha2marcBiblio($dbh,$bibnum);
my $bibid = &MARCfind_MARCbibid_from_oldbiblionumber($dbh,$bibnum);
&MARCmodbiblio($dbh,$bibid, $MARCRecord);
```modules/C4/BookShelves.pm```
--- koha-2.2.4/modules/C4/BookShelves.pm
+++ postgresql/modules/C4/BookShelves.pm
@@ -123,7 +123,7 @@
ON bookshelf.shelfnumber = shelfcontents.shelfnumber
left join borrowers on bookshelf.owner = borrowers.borrowernumber
where owner=? or category>=?
- GROUP BY bookshelf.shelfnumber order by shelfname");
+ GROUP BY bookshelf.shelfnumber, bookshelf.shelfname,owner,surname,firstname,category order by shelfname");
$sth->execute($owner,$mincategory);
my %shelflist;
while (my ($shelfnumber, $shelfname,$owner,$surname,$firstname,$category,$count) = $sth->fetchrow)
--- koha-2.2.4/modules/C4/Bull.pm
+++ postgresql/modules/C4/Bull.pm
@@ -56,8 +56,9 @@
sub getSupplierListWithLateIssues {
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare("SELECT DISTINCT id, name
- FROM subscription, serial
+ FROM subscription
LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
+ , serial
WHERE subscription.subscriptionid = serial.subscriptionid AND
(planneddate < now( ) OR serial.STATUS = 3)
");
@@ -74,8 +75,9 @@
my $sth;
if ($supplierid) {
$sth = $dbh->prepare("SELECT name,title,planneddate,serialseq,serial.subscriptionid
- FROM subscription, serial, biblio
+ FROM subscription
LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
+ , serial, biblio
WHERE subscription.subscriptionid = serial.subscriptionid AND
((planneddate < now() and serial.STATUS =1) OR serial.STATUS = 3) and
subscription.aqbooksellerid=$supplierid and
@@ -83,8 +85,9 @@
");
} else {
$sth = $dbh->prepare("SELECT name,title,planneddate,serialseq,serial.subscriptionid
- FROM subscription, serial, biblio
+ FROM subscription
LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
+ , serial, biblio
WHERE subscription.subscriptionid = serial.subscriptionid AND
((planneddate < now() and serial.STATUS <=3) OR serial.STATUS = 3) and
biblio.biblionumber = subscription.biblionumber
```cgi-bin/admin/checkmarc.pm```
--- koha-2.2.4/intranet-cgi/admin/checkmarc.pl
+++ postgresql/intranet/cgi-bin/admin/checkmarc.pl
@@ -42,7 +42,7 @@
my $dbh = C4::Context->dbh;
my $total;
# checks itemnum field
-my $sth = $dbh->prepare("select tab from marc_subfield_structure where kohafield=\"items.itemnumber\"");
+my $sth = $dbh->prepare("select tab from marc_subfield_structure where kohafield=\'items.itemnumber\'");
$sth->execute;
my ($res) = $sth->fetchrow;
if ($res==-1) {
@@ -53,11 +53,11 @@
}
# checks biblio.biblionumber and biblioitem.biblioitemnumber (same tag and tab=-1)
-$sth = $dbh->prepare("select tagfield,tab from marc_subfield_structure where kohafield=\"biblio.biblionumber\"");
+$sth = $dbh->prepare("select tagfield,tab from marc_subfield_structure where kohafield=\'biblio.biblionumber\'");
$sth->execute;
my $tab;
($res,$tab) = $sth->fetchrow;
-$sth = $dbh->prepare("select tagfield,tab from marc_subfield_structure where kohafield=\"biblioitems.biblioitemnumber\"");
+$sth = $dbh->prepare("select tagfield,tab from marc_subfield_structure where kohafield=\'biblioitems.biblioitemnumber\'");
$sth->execute;
my ($res2,$tab2) = $sth->fetchrow;
if ($res && $res2 && ($res eq $res2) && $tab==-1 && $tab2==-1) {
@@ -69,7 +69,7 @@
# checks all item fields are in the same tag and in tab 10
-$sth = $dbh->prepare("select tagfield,tab,kohafield from marc_subfield_structure where kohafield like \"items.%\"");
+$sth = $dbh->prepare("select tagfield,tab,kohafield from marc_subfield_structure where kohafield like \'items.%\'");
$sth->execute;
my $field;
($res,$res2,$field) = $sth->fetchrow;
@@ -115,7 +115,7 @@
# checks biblioitems.itemtype must be mapped and use authorised_value=itemtype
-$sth = $dbh->prepare("select tagfield,tab,authorised_value from marc_subfield_structure where kohafield = \"biblioitems.itemtype\"");
+$sth = $dbh->prepare("select tagfield,tab,authorised_value from marc_subfield_structure where kohafield = \'biblioitems.itemtype\'");
$sth->execute;
($res,$res2,$field) = $sth->fetchrow;
if ($res && $res2>=0 && $field eq "itemtypes") {
@@ -126,7 +126,7 @@
}
# checks items.homebranch must be mapped and use authorised_value=branches
-$sth = $dbh->prepare("select tagfield,tab,authorised_value from marc_subfield_structure where kohafield = \"items.homebranch\"");
+$sth = $dbh->prepare("select tagfield,tab,authorised_value from marc_subfield_structure where kohafield = \'items.homebranch\'");
$sth->execute;
($res,$res2,$field) = $sth->fetchrow;
if ($res && $res2 eq 10 && $field eq "branches") {
@@ -136,7 +136,7 @@
$total++;
}
# checks items.homebranch must be mapped and use authorised_value=branches
-$sth = $dbh->prepare("select tagfield,tab,authorised_value from marc_subfield_structure where kohafield = \"items.holdingbranch\"");
+$sth = $dbh->prepare("select tagfield,tab,authorised_value from marc_subfield_structure where kohafield = \'items.holdingbranch\'");
$sth->execute;
($res,$res2,$field) = $sth->fetchrow;
if ($res && $res2 eq 10 && $field eq "branches") {
modules/C4/Circulation/Circ2.pm
--- koha-2.2.4/modules/C4/Circulation/Circ2.pm
+++ postgresql/modules/C4/Circulation/Circ2.pm
@@ -341,7 +341,7 @@
my $iteminformation=$sth->fetchrow_hashref;
$sth->finish;
if ($iteminformation) {
- $sth=$dbh->prepare("select date_due from issues where itemnumber=? and isnull(returndate)");
+ $sth=$dbh->prepare("select date_due from issues where itemnumber=? and returndate is null");
$sth->execute($iteminformation->{'itemnumber'});
my ($date_due) = $sth->fetchrow;
$iteminformation->{'date_due'}=$date_due;
--- 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);
}
--- 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 {
--- koha-2.2.4/intranet-cgi/MARCdetail.pl
+++ postgresql/intranet/cgi-bin/MARCdetail.pl
@@ -199,7 +199,7 @@
push(@big_array, \%this_row);
}
}
-my ($holdingbrtagf,$holdingbrtagsubf) = &MARCfind_marc_from_kohafield($dbh,"items.holdingbranch",$itemtype);
+my ($holdingbrtagf,$holdingbrtagsubf) = &MARCfind_marc_from_kohafield($dbh,'items.holdingbranch',$itemtype);
@big_array = sort {$a->{$holdingbrtagsubf} cmp $b->{$holdingbrtagsubf}} @big_array;
#fill big_row with missing datas
```modules/C4/SearchMarc.pm```
--- koha-2.2.4/modules/C4/SearchMarc.pm
+++ postgresql/modules/C4/SearchMarc.pm
@@ -204,7 +204,7 @@
# the item.notforloan contains an integer. Every value <>0 means "book unavailable for loan".
# but each library can have it's own table of meaning for each value. Get them
# 1st search if there is a list of authorised values connected to items.notforloan
- my $sth = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.notforloan"');
+ my $sth = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield=\'items.notforloan\'');
$sth->execute;
my %notforloanstatus;
my ($authorised_valuecode) = $sth->fetchrow;
@@ -283,11 +283,11 @@
# $offset=0 if $orderby eq "biblio.timestamp";
my $sth;
if ($sql_where2) {
- $sth = $dbh->prepare("select distinct m1.bibid from biblio,biblioitems,marc_biblio,$sql_tables where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and $sql_where2 and ($sql_where1) order by $orderby $desc_or_asc");
- warn "Q2 : select distinct m1.bibid from biblio,biblioitems,marc_biblio,$sql_tables where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and $sql_where2 and ($sql_where1) order by $orderby $desc_or_asc term is @$value";
+ $sth = $dbh->prepare("select distinct m1.bibid, $orderby from biblio,biblioitems,marc_biblio,$sql_tables where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and $sql_where2 and ($sql_where1) order by $orderby $desc_or_asc");
+ warn "Q2 : select distinct m1.bibid, $orderby from biblio,biblioitems,marc_biblio,$sql_tables where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and $sql_where2 and ($sql_where1) order by $orderby $desc_or_asc term is @$value";
} else {
- $sth = $dbh->prepare("select distinct m1.bibid from biblio,biblioitems,marc_biblio,$sql_tables where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and $sql_where1 order by $orderby $desc_or_asc");
- warn "Q : select distinct m1.bibid from biblio,biblioitems,marc_biblio,$sql_tables where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and $sql_where1 order by $orderby $desc_or_asc";
+ $sth = $dbh->prepare("select distinct m1.bibid, $orderby from biblio,biblioitems,marc_biblio,$sql_tables where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and $sql_where1 order by $orderby $desc_or_asc");
+ warn "Q : select distinct m1.bibid, $orderby from biblio,biblioitems,marc_biblio,$sql_tables where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and $sql_where1 order by $orderby $desc_or_asc";
}
$sth->execute();
my @result = ();
@@ -343,9 +343,10 @@
# HINT : biblionumber as bn is important. The hash is fills biblionumber with items.biblionumber.
# so if you dont' has an item, you get a not nice empty value.
$sth = $dbh->prepare("SELECT biblio.biblionumber as bn,biblioitems.*,biblio.*, marc_biblio.bibid,itemtypes.notforloan,itemtypes.description
- FROM biblio, marc_biblio
+ FROM biblio
LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
LEFT JOIN itemtypes on itemtypes.itemtype=biblioitems.itemtype
+ , marc_biblio
WHERE biblio.biblionumber = marc_biblio.biblionumber AND bibid = ?");
my $sth_subtitle = $dbh->prepare("SELECT subtitle FROM bibliosubtitle WHERE biblionumber=?"); # Added BY JF for Subtitles
my @finalresult = ();
--- 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");