From 49c7d69561dcfccf36194a32a25962fa33df8c95 Mon Sep 17 00:00:00 2001
From: Jared Camins-Esakov <jcamins@cpbibliography.com>
Date: Tue, 13 Nov 2012 09:25:42 -0500
Subject: [PATCH] Bug 9066: VirtualShelves db access not Plack+MariaDB
 compatible

The $dbh database handle in C4::VirtualShelves was declared at the
module level, which means under Plack it is initialized only once, when
the server first starts. With the default MySQL configuration this is
not a problem, since the MySQL connection does not time out, but the
MariaDB default configuration does time out, resulting in every page
that calls any sub in C4::VirtualShelves failing after a certain period.
This patch eliminates the module-level $dbh variable and replaces it
with $dbh handles in each subroutine that requires it.

To test:
Confirm that Virtual Shelves functionality still works as expected. The
problem is not reproducible when using Apache or Plack+MySQL, but if
everything works after the patch has been applied, the changes are safe.

Signed-off-by: Mason James <mtj@kohaaloha.com>
Signed-off-by: Paul Poulain <paul.poulain@biblibre.com>
Signed-off-by: Jared Camins-Esakov <jcamins@cpbibliography.com>
---
 C4/VirtualShelves.pm | 14 ++++++++++++--
 1 file changed, 12 insertions(+), 2 deletions(-)

diff --git a/C4/VirtualShelves.pm b/C4/VirtualShelves.pm
index 90a00a580e..5682a1557e 100644
--- a/C4/VirtualShelves.pm
+++ b/C4/VirtualShelves.pm
@@ -50,8 +50,6 @@ BEGIN {
 }
 
 
-my $dbh = C4::Context->dbh;
-
 =head1 NAME
 
 C4::VirtualShelves - Functions for manipulating Koha virtual shelves
@@ -101,6 +99,7 @@ sub GetShelves {
     my ($category, $row_count, $offset, $owner) = @_;
     my @params;
     my $total = _shelf_count($owner, $category);
+    my $dbh = C4::Context->dbh;
     my $query = qq{
         SELECT vs.shelfnumber, vs.shelfname,vs.owner,
         bo.surname,bo.firstname,vs.category,vs.sortfield,
@@ -155,6 +154,7 @@ the submitted parameters.
 sub GetAllShelves {
     my ($category,$owner,$adding_allowed) = @_;
     my @params;
+    my $dbh = C4::Context->dbh;
     my $query = 'SELECT vs.* FROM virtualshelves vs ';
     if($category==1) {
         $query.= qq{
@@ -184,6 +184,7 @@ Returns shelf names and numbers for Add to combo of search results and Lists but
 sub GetSomeShelfNames {
     my ($owner, $purpose, $adding_allowed)= @_;
     my ($bar, $pub, @params);
+    my $dbh = C4::Context->dbh;
 
     my $bquery = 'SELECT vs.shelfnumber, vs.shelfname FROM virtualshelves vs ';
     my $limit= ShelvesMax($purpose);
@@ -223,6 +224,7 @@ Returns the above-mentioned fields for passed virtual shelf number.
 
 sub GetShelf {
     my ($shelfnumber) = @_;
+    my $dbh = C4::Context->dbh;
     my $query = qq(
         SELECT shelfnumber, shelfname, owner, category, sortfield,
             allow_add, allow_delete_own, allow_delete_other
@@ -306,6 +308,7 @@ Returns a code to know what's happen.
 
 sub AddShelf {
     my ($hashref, $owner)= @_;
+    my $dbh = C4::Context->dbh;
 
     #initialize missing hash values to silence warnings
     foreach('shelfname','category', 'sortfield', 'allow_add', 'allow_delete_own', 'allow_delete_other' ) {
@@ -343,6 +346,7 @@ C<$shelfnumber>, unless that bib is already on that shelf.
 sub AddToShelf {
     my ($biblionumber, $shelfnumber, $borrowernumber) = @_;
     return unless $biblionumber;
+    my $dbh = C4::Context->dbh;
     my $query = qq(
         SELECT *
         FROM   virtualshelfcontents
@@ -382,6 +386,7 @@ Returns 1 if the action seemed to be successful.
 
 sub ModShelf {
     my ($shelfnumber,$hashref) = @_;
+    my $dbh = C4::Context->dbh;
 
     my $query= "SELECT * FROM virtualshelves WHERE shelfnumber=?";
     my $sth = $dbh->prepare($query);
@@ -453,6 +458,7 @@ sub ShelfPossibleAction {
 
     return 0 unless defined($shelfnumber);
 
+    my $dbh = C4::Context->dbh;
     my $query = qq/
         SELECT IFNULL(owner,0) AS owner, category, allow_add, allow_delete_own, allow_delete_other, IFNULL(sh.borrowernumber,0) AS borrowernumber
         FROM virtualshelves vs
@@ -500,6 +506,7 @@ Returns 0 if no items have been deleted.
 
 sub DelFromShelf {
     my ($bibref, $shelfnumber, $user) = @_;
+    my $dbh = C4::Context->dbh;
     my $query = qq(SELECT allow_delete_own, allow_delete_other FROM virtualshelves WHERE shelfnumber=?);
     my $sth= $dbh->prepare($query);
     $sth->execute($shelfnumber);
@@ -544,6 +551,7 @@ ShelfPossibleAction with manage parameter.
 sub DelShelf {
     my ($shelfnumber)= @_;
     return unless $shelfnumber && $shelfnumber =~ /^\d+$/;
+    my $dbh = C4::Context->dbh;
     my $sth = $dbh->prepare("DELETE FROM virtualshelves WHERE shelfnumber=?");
     return $sth->execute($shelfnumber);
 }
@@ -629,6 +637,7 @@ sub _shelf_count {
     my @params;
     # Find out how many shelves total meet the submitted criteria...
 
+    my $dbh = C4::Context->dbh;
     my $query = "SELECT count(*) FROM virtualshelves vs ";
     if($category==1) {
         $query.= qq{
@@ -661,6 +670,7 @@ sub _biblionumber_sth { #only used in obsolete sub below
 sub _CheckShelfName {
     my ($name, $cat, $owner, $number)= @_;
 
+    my $dbh = C4::Context->dbh;
     my $query = qq(
         SELECT DISTINCT shelfnumber
         FROM   virtualshelves
-- 
2.39.5