From d6449c50f48e2bb99649357d92bdf38653452a5d Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Demians?= <f.demians@tamil.fr>
Date: Sat, 25 Oct 2014 20:05:29 +0200
Subject: [PATCH] Bug 13146 Improve GetRecordValue function by caching field
 mapping

GetRecordValue function is called each time a specific field mapping is
required. For example, bug 12692 display biblio record subtitle for each hold
awaiting pickup or overdue. As it is implemented now, GetRecordValue queries
directly the fieldmapping table to determine in which tag/letter a field has to
be searched. It means, that the same mapping are requested over and over.

This patch uses Koha cache system to store a complete data structure
representing the field mapping.

TO TEST:

(1) Look at the code! and check that Koha caching system is properly used.

(2) Find out a Koha function where subtitle field mapping is used. Make you
    choice: sending a basket, tag list, overdues with fines, renew item, etc.

(3) Note that the display of subtitle field hasn't changed.

(4) If you really want to see how less queries are sent to MySQL with this
    patch, you have to monitor your MySQL log files!

    Suggestion to do it on a Debian box:

    - In MySQL client: SHOW VARIABLES LIKE "general_log%";
      It gives you MySQL log file path. Usually, /var/run/mysqld/mysqld.log
    - Enable log, with: SET GLOBAL general_log = 'ON';
    - Display a page displaying the subtitle field
    - Compare request send with/withouth this patch. Without the patch, you
      will see plenty of query like that:

      SELECT fieldcode, subfieldcode FROM fieldmapping
---
 C4/Biblio.pm | 71 +++++++++++++++++++++++++++++++++++++++++++++++++-----------
 1 file changed, 58 insertions(+), 13 deletions(-)

diff --git a/C4/Biblio.pm b/C4/Biblio.pm
index 2e8827a..6373416 100644
--- a/C4/Biblio.pm
+++ b/C4/Biblio.pm
@@ -688,26 +688,69 @@ sub GetRecordValue {
         carp 'GetRecordValue called with undefined record';
         return;
     }
-    my $dbh = C4::Context->dbh;
 
-    my $sth = $dbh->prepare('SELECT fieldcode, subfieldcode FROM fieldmapping WHERE frameworkcode = ? AND field = ?');
-    $sth->execute( $frameworkcode, $field );
+    # All field mappings are retrieved from cache, or from DB if not already
+    # cached.
+    # The mapping is a data structure that looks like that:
+    # '' => {
+    #     subtitle => [
+    #         [ '200', 'e' ],
+    #         [ '400', 'e' ],
+    #     },
+    #     maintitle => [
+    #         [ '200', 'a' ],
+    #         [ '400', 'a' ],
+    #     },
+    #  },
+    #  MAIN = {
+    #     subtitle => [
+    #         [ '200', 'e' ],
+    #     },
+    #     maintitle => [
+    #         [ '200', 'a' ],
+    #     },
+    #  },
+
+    my $key = 'FieldMapping';
+    my $cache = Koha::Cache->get_instance();
+    my $mapping = $cache->get_from_cache($key);
+    unless ( $mapping ) {
+        my $sth = C4::Context->dbh->prepare('SELECT * FROM fieldmapping');
+        $sth->execute;
+        $mapping = {};
+        for my $map ( @{ $sth->fetchall_arrayref({}) } ) {
+            my ($field, $framework, $tag, $letter) = (
+                $map->{field},
+                $map->{frameworkcode},
+                $map->{fieldcode},
+                $map->{subfieldcode} );
+            $mapping->{$framework} ||= {};
+            $mapping->{$framework}->{$field} ||= [];
+            push @{ $mapping->{$framework}->{$field} }, [ $tag, $letter ];
+        }
+        $cache->set_in_cache($key, $mapping);
+    }
+
+    # Does the requested field mapping exists from the framework?
+    my $maps = $mapping->{$frameworkcode};
+    return unless $maps;
+    $maps = $maps->{$field};
+    return unless $maps;
 
     my @result = ();
-
-    while ( my $row = $sth->fetchrow_hashref ) {
-        foreach my $field ( $record->field( $row->{fieldcode} ) ) {
-            if ( ( $row->{subfieldcode} ne "" && $field->subfield( $row->{subfieldcode} ) ) ) {
-                foreach my $subfield ( $field->subfield( $row->{subfieldcode} ) ) {
-                    push @result, { 'subfield' => $subfield };
+    for my $map (@$maps) {
+        my ($tag, $letter) = @$map;
+        foreach my $field ( $record->field($tag) ) {
+            if ( $letter ) {
+                if ( my @values = $field->subfield($letter) ) {
+                    push @result, { subfield => $_ } for @values;
                 }
-
-            } elsif ( $row->{subfieldcode} eq "" ) {
-                push @result, { 'subfield' => $field->as_string() };
+            }
+            else {
+                push @result, { subfield => $field->as_string() };
             }
         }
     }
-
     return \@result;
 }
 
@@ -731,6 +774,7 @@ sub SetFieldMapping {
 
         $sth->execute( $fieldcode, $subfieldcode, $framework, $field );
     }
+    my $cache = Koha::Cache->get_instance()->clear_from_cache('FieldMapping');
 }
 
 =head2 DeleteFieldMapping
@@ -747,6 +791,7 @@ sub DeleteFieldMapping {
 
     my $sth = $dbh->prepare('DELETE FROM fieldmapping WHERE id = ?');
     $sth->execute($id);
+    my $cache = Koha::Cache->get_instance()->clear_from_cache('FieldMapping');
 }
 
 =head2 GetFieldMapping
-- 
2.6.2