Back to « KohaProject Main Page

Running Koha on ```PostgreSQL``` database.

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.

Changes to Postgresql database

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.

Changes to perl code

Perl Patch files

```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;

```modules/C4/Biblio.pm```

--- 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) 

```modules/C4/Bull.pm```

--- 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;

```modules/C4/Context.pm```

--- 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);
 }

```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 {

```cgi-bin/MARCdetail.pl```

--- 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 = ();

```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");
 
postgresql.txt · Last modified: 2006/04/04 09:23 (external edit)
 
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki