From fa491c3e33cefdb52d4d0b82a01af5ed91454af7 Mon Sep 17 00:00:00 2001
From: Joe Atzberger <joe.atzberger@liblime.com>
Date: Fri, 27 Mar 2009 10:34:34 -0500
Subject: [PATCH] Introduce sth caching to Letters.pm

The re-creation of statement handles for the same queries, repeatedly,
seriously degrades the performance of notices.  This introduces new
class-level caching to avoid that problem.

Note: initial provisions
to do the same with caching of COLUMNS information about tables is
also included, but this would probably be most usefully implemented
in C4::Context (or a separate more central module).  The benefits of
caching that info would probably be even greater.  Note that this
proposed implementation might be obsoleted by ORM integration, where
DB structure info would already be cached.

Signed-off-by: Galen Charlton <galen.charlton@liblime.com>
Signed-off-by: Henri-Damien LAURENT <henridamien.laurent@biblibre.com>
---
 C4/Letters.pm | 69 +++++++++++++++++++++++++++++++++++----------------
 1 file changed, 47 insertions(+), 22 deletions(-)

diff --git a/C4/Letters.pm b/C4/Letters.pm
index 040e2ff96f..d15a78784a 100644
--- a/C4/Letters.pm
+++ b/C4/Letters.pm
@@ -24,6 +24,7 @@ use C4::Members;
 use C4::Log;
 use C4::SMS;
 use Encode;
+use Carp;
 
 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
 
@@ -466,40 +467,64 @@ sub SendAlerts {
 
 =cut
 
-sub parseletter {
-    my ( $letter, $table, $pk ) = @_;
+our %handles = ();
+our %columns = ();
 
-    # 	warn "Parseletter : ($letter,$table,$pk)";
-    my $dbh = C4::Context->dbh;
-    my $sth;
-    if ( $table eq 'biblio' ) {
-        $sth = $dbh->prepare("select * from biblio where biblionumber=?");
+sub parseletter_sth {
+    my $table = shift;
+    unless ($table) {
+        carp "ERROR: parseletter_sth() called without argument (table)";
+        return;
+    }
+    # check cache first
+    (defined $handles{$table}) and return $handles{$table};
+    my $query = 
+    ($table eq 'biblio'       ) ? "SELECT * FROM $table WHERE   biblionumber = ?"                      :
+    ($table eq 'biblioitems'  ) ? "SELECT * FROM $table WHERE   biblionumber = ?"                      :
+    ($table eq 'items'        ) ? "SELECT * FROM $table WHERE     itemnumber = ?"                      :
+    ($table eq 'reserves'     ) ? "SELECT * FROM $table WHERE borrowernumber = ? and biblionumber = ?" :
+    ($table eq 'borrowers'    ) ? "SELECT * FROM $table WHERE borrowernumber = ?"                      :
+    ($table eq 'branches'     ) ? "SELECT * FROM $table WHERE     branchcode = ?"                      :
+    ($table eq 'aqbooksellers') ? "SELECT * FROM $table WHERE             id = ?"                      : undef ;
+    unless ($query) {
+        warn "ERROR: No parseletter_sth query for table '$table'";
+        return;     # nothing to get
     }
-    elsif ( $table eq 'biblioitems' ) {
-        $sth = $dbh->prepare("select * from biblioitems where biblionumber=?");
+    unless ($handles{$table} = C4::Context->dbh->prepare($query)) {
+        warn "ERROR: Failed to prepare query: '$query'";
+        return;
     }
-    elsif ( $table eq 'borrowers' ) {
-        $sth = $dbh->prepare("select * from borrowers where borrowernumber=?");
+    return $handles{$table};    # now cache is populated for that $table
+}
+
+sub parseletter {
+    my ( $letter, $table, $pk, $pk2 ) = @_;
+    unless ($letter) {
+        carp "ERROR: parseletter() 1st argument 'letter' empty";
+        return;
     }
-    elsif ( $table eq 'branches' ) {
-        $sth = $dbh->prepare("select * from branches where branchcode=?");
+    # 	warn "Parseletter : ($letter, $table, $pk ...)";
+    my $sth = parseletter_sth($table);
+    unless ($sth) {
+        warn "parseletter_sth('$table') failed to return a valid sth.  No substitution will be done for that table.";
+        return;
     }
-    elsif ( $table eq 'aqbooksellers' ) {
-        $sth = $dbh->prepare("select * from aqbooksellers where id=?");
+    if ( $pk2 ) {
+        $sth->execute($pk, $pk2);
+    } else {
+        $sth->execute($pk);
     }
-    $sth->execute($pk);
 
-    # store the result in an hash
     my $values = $sth->fetchrow_hashref;
 
     # and get all fields from the table
-    $sth = $dbh->prepare("show columns from $table");
-    $sth->execute;
-    while ( ( my $field ) = $sth->fetchrow_array ) {
+    my $columns = C4::Context->dbh->prepare("SHOW COLUMNS FROM $table");
+    $columns->execute;
+    while ( ( my $field ) = $columns->fetchrow_array ) {
         my $replacefield = "<<$table.$field>>";
         my $replacedby   = $values->{$field} || '';
-        $letter->{title}   =~ s/$replacefield/$replacedby/g;
-        $letter->{content} =~ s/$replacefield/$replacedby/g;
+        ($letter->{title}  ) and $letter->{title}   =~ s/$replacefield/$replacedby/g;
+        ($letter->{content}) and $letter->{content} =~ s/$replacefield/$replacedby/g;
     }
 }
 
-- 
2.20.1