From cd56d10cb9d815a058660711f62174a08b837680 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@biblibre.com>
Date: Wed, 12 Sep 2012 09:20:26 +0200
Subject: [PATCH] Bug 8369: Merge tables for circulation rules.

This patch adds 3 new tables:
 - circ_rules
 - borrower_circ_rules
 - item_circ_rules
It removes 6 tables:
 - default_borrower_circ_rules
 - default_branch_circ_rules
 - default_branch_item_rules
 - default_circ_rules
 - branch_borrower_circ_rules
 - branch_item_rules

The goal is to merge circulations rules into 3 tables instead of 6 without
add or remove features. Previous behaviours do not change.
The wildcard '*' character is a rule for the default values. Then it is
possible to merge 2 tables into one if we consider that the default value is
the value with a branchcode eq '*'.
I removed the foreign key constraint with branchcode and added 3 queries
in the C4::Branch::DelBranch routine in order to remove records about a
deleted branch.

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
---
 C4/Branch.pm                           |    5 +
 C4/Circulation.pm                      |   33 ++--
 admin/smart-rules.pl                   |  296 +++++++++-----------------------
 installer/data/mysql/kohastructure.sql |   70 ++------
 installer/data/mysql/updatedatabase.pl |   56 ++++++
 5 files changed, 178 insertions(+), 282 deletions(-)

diff --git a/C4/Branch.pm b/C4/Branch.pm
index d765c99..896a832 100644
--- a/C4/Branch.pm
+++ b/C4/Branch.pm
@@ -491,6 +491,11 @@ sub DelBranch {
     my $sth = $dbh->prepare("delete from branches where branchcode = ?");
     $sth->execute($branchcode);
     $sth->finish;
+
+    # We have to delete the corresponding circulation rules
+    $dbh->do("DELETE FROM circ_rules WHERE branchcode = ?", {}, $branchcode);
+    $dbh->do("DELETE FROM borrower_circ_rules WHERE branchcode = ?", {}, $branchcode);
+    $dbh->do("DELETE FROM item_circ_rules WHERE branchcode = ?", {}, $branchcode);
 }
 
 =head2 ModBranchCategoryInfo
diff --git a/C4/Circulation.pm b/C4/Circulation.pm
index afe7a6a..e0ade56 100644
--- a/C4/Circulation.pm
+++ b/C4/Circulation.pm
@@ -1467,13 +1467,12 @@ patron of the given category can have at the given
 branch.  If the value is undef, no limit.
 
 This will first check for a specific branch and
-category match from branch_borrower_circ_rules. 
+category match from borrower_circ_rules.
 
-If no rule is found, it will then check default_branch_circ_rules
+If no rule is found, it will then check circ_rules
 (same branch, default category).  If no rule is found,
-it will then check default_borrower_circ_rules (default 
-branch, same category), then failing that, default_circ_rules
-(default branch, default category).
+it will then check the default branch (*), then failing that, circ_rules
+with the default branch and default category.
 
 If no rule has been found in the database, it will default to
 the buillt in rule:
@@ -1491,7 +1490,7 @@ sub GetBranchBorrowerCircRule {
     my $categorycode = shift;
 
     my $branch_cat_query = "SELECT maxissueqty
-                            FROM branch_borrower_circ_rules
+                            FROM borrower_circ_rules
                             WHERE branchcode = ?
                             AND   categorycode = ?";
     my $dbh = C4::Context->dbh();
@@ -1504,7 +1503,7 @@ sub GetBranchBorrowerCircRule {
 
     # try same branch, default borrower category
     my $branch_query = "SELECT maxissueqty
-                        FROM default_branch_circ_rules
+                        FROM circ_rules
                         WHERE branchcode = ?";
     $sth = $dbh->prepare($branch_query);
     $sth->execute($branchcode);
@@ -1514,8 +1513,10 @@ sub GetBranchBorrowerCircRule {
 
     # try default branch, same borrower category
     my $category_query = "SELECT maxissueqty
-                          FROM default_borrower_circ_rules
-                          WHERE categorycode = ?";
+                          FROM borrower_circ_rules
+                          WHERE categorycode = ?
+                          AND branchcode = '*'
+                          ";
     $sth = $dbh->prepare($category_query);
     $sth->execute($categorycode);
     if ($result = $sth->fetchrow_hashref()) {
@@ -1524,7 +1525,8 @@ sub GetBranchBorrowerCircRule {
   
     # try default branch, default borrower category
     my $default_query = "SELECT maxissueqty
-                          FROM default_circ_rules";
+                          FROM circ_rules
+                          WHERE branchcode = '*'";
     $sth = $dbh->prepare($default_query);
     $sth->execute();
     if ($result = $sth->fetchrow_hashref()) {
@@ -1573,17 +1575,18 @@ sub GetBranchItemRule {
 
     my @attempts = (
         ['SELECT holdallowed, returnbranch
-            FROM branch_item_rules
+            FROM item_circ_rules
             WHERE branchcode = ?
               AND itemtype = ?', $branchcode, $itemtype],
         ['SELECT holdallowed, returnbranch
-            FROM default_branch_circ_rules
+            FROM circ_rules
             WHERE branchcode = ?', $branchcode],
         ['SELECT holdallowed, returnbranch
-            FROM default_branch_item_rules
-            WHERE itemtype = ?', $itemtype],
+            FROM item_circ_rules
+            WHERE branchcode = "*" AND itemtype = ?', $itemtype],
         ['SELECT holdallowed, returnbranch
-            FROM default_circ_rules'],
+            FROM circ_rules
+            WHERE branchcode = "*"'],
     );
 
     foreach my $attempt (@attempts) {
diff --git a/admin/smart-rules.pl b/admin/smart-rules.pl
index c221d71..1f0bee3 100755
--- a/admin/smart-rules.pl
+++ b/admin/smart-rules.pl
@@ -17,8 +17,7 @@
 # with Koha; if not, write to the Free Software Foundation, Inc.,
 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 
-use strict;
-use warnings;
+use Modern::Perl;
 use CGI;
 use C4::Context;
 use C4::Output;
@@ -56,46 +55,26 @@ if ($op eq 'delete') {
 }
 elsif ($op eq 'delete-branch-cat') {
     my $categorycode  = $input->param('categorycode');
-    if ($branch eq "*") {
-        if ($categorycode eq "*") {
-            my $sth_delete = $dbh->prepare("DELETE FROM default_circ_rules");
-            $sth_delete->execute();
-        } else {
-            my $sth_delete = $dbh->prepare("DELETE FROM default_borrower_circ_rules
-                                            WHERE categorycode = ?");
-            $sth_delete->execute($categorycode);
-        }
-    } elsif ($categorycode eq "*") {
-        my $sth_delete = $dbh->prepare("DELETE FROM default_branch_circ_rules
-                                        WHERE branchcode = ?");
-        $sth_delete->execute($branch);
+    if ($categorycode eq "*") {
+        my $sth_delete = $dbh->prepare("DELETE FROM circ_rules WHERE branchcode = ?");
+        $sth_delete->execute( $branch );
     } else {
-        my $sth_delete = $dbh->prepare("DELETE FROM branch_borrower_circ_rules
+        my $sth_delete = $dbh->prepare("DELETE FROM borrower_circ_rules
                                         WHERE branchcode = ?
                                         AND categorycode = ?");
-        $sth_delete->execute($branch, $categorycode);
+        $sth_delete->execute( $branch, $categorycode );
     }
 }
 elsif ($op eq 'delete-branch-item') {
     my $itemtype  = $input->param('itemtype');
-    if ($branch eq "*") {
-        if ($itemtype eq "*") {
-            my $sth_delete = $dbh->prepare("DELETE FROM default_circ_rules");
-            $sth_delete->execute();
-        } else {
-            my $sth_delete = $dbh->prepare("DELETE FROM default_branch_item_rules
-                                            WHERE itemtype = ?");
-            $sth_delete->execute($itemtype);
-        }
-    } elsif ($itemtype eq "*") {
-        my $sth_delete = $dbh->prepare("DELETE FROM default_branch_circ_rules
-                                        WHERE branchcode = ?");
-        $sth_delete->execute($branch);
+    if ($itemtype eq "*") {
+        my $sth_delete = $dbh->prepare("DELETE FROM circ_rules WHERE branchcode = ?");
+        $sth_delete->execute( $branch);
     } else {
-        my $sth_delete = $dbh->prepare("DELETE FROM branch_item_rules
+        my $sth_delete = $dbh->prepare("DELETE FROM item_circ_rules
                                         WHERE branchcode = ?
                                         AND itemtype = ?");
-        $sth_delete->execute($branch, $itemtype);
+        $sth_delete->execute( $branch, $itemtype );
     }
 }
 # save the values entered
@@ -142,40 +121,22 @@ elsif ($op eq "set-branch-defaults") {
     $maxissueqty = undef if $maxissueqty !~ /^\d+/;
     $holdallowed =~ s/\s//g;
     $holdallowed = undef if $holdallowed !~ /^\d+/;
-
-    if ($branch eq "*") {
-        my $sth_search = $dbh->prepare("SELECT count(*) AS total
-                                        FROM default_circ_rules");
-        my $sth_insert = $dbh->prepare("INSERT INTO default_circ_rules
-                                        (maxissueqty, holdallowed, returnbranch)
-                                        VALUES (?, ?, ?)");
-        my $sth_update = $dbh->prepare("UPDATE default_circ_rules
-                                        SET maxissueqty = ?, holdallowed = ?, returnbranch = ?");
-
-        $sth_search->execute();
-        my $res = $sth_search->fetchrow_hashref();
-        if ($res->{total}) {
-            $sth_update->execute($maxissueqty, $holdallowed, $returnbranch);
-        } else {
-            $sth_insert->execute($maxissueqty, $holdallowed, $returnbranch);
-        }
+    my $sth_search = $dbh->prepare("SELECT count(*) AS total
+                                    FROM circ_rules
+                                    WHERE branchcode = ?");
+    my $sth_insert = $dbh->prepare("INSERT INTO circ_rules
+                                    (branchcode, maxissueqty, holdallowed, returnbranch)
+                                    VALUES (?, ?, ?, ?)");
+    my $sth_update = $dbh->prepare("UPDATE circ_rules
+                                    SET maxissueqty = ?, holdallowed = ?, returnbranch = ?
+                                    WHERE branchcode = ?");
+
+    $sth_search->execute( $branch );
+    my $res = $sth_search->fetchrow_hashref();
+    if ($res->{total}) {
+        $sth_update->execute($maxissueqty, $holdallowed, $returnbranch, $branch);
     } else {
-        my $sth_search = $dbh->prepare("SELECT count(*) AS total
-                                        FROM default_branch_circ_rules
-                                        WHERE branchcode = ?");
-        my $sth_insert = $dbh->prepare("INSERT INTO default_branch_circ_rules
-                                        (branchcode, maxissueqty, holdallowed, returnbranch)
-                                        VALUES (?, ?, ?, ?)");
-        my $sth_update = $dbh->prepare("UPDATE default_branch_circ_rules
-                                        SET maxissueqty = ?, holdallowed = ?, returnbranch = ?
-                                        WHERE branchcode = ?");
-        $sth_search->execute($branch);
-        my $res = $sth_search->fetchrow_hashref();
-        if ($res->{total}) {
-            $sth_update->execute($maxissueqty, $holdallowed, $returnbranch, $branch);
-        } else {
-            $sth_insert->execute($branch, $maxissueqty, $holdallowed, $returnbranch);
-        }
+        $sth_insert->execute($branch, $maxissueqty, $holdallowed, $returnbranch);
     }
 }
 elsif ($op eq "add-branch-cat") {
@@ -184,72 +145,37 @@ elsif ($op eq "add-branch-cat") {
     $maxissueqty =~ s/\s//g;
     $maxissueqty = undef if $maxissueqty !~ /^\d+/;
 
-    if ($branch eq "*") {
-        if ($categorycode eq "*") {
-            my $sth_search = $dbh->prepare("SELECT count(*) AS total
-                                            FROM default_circ_rules");
-            my $sth_insert = $dbh->prepare("INSERT INTO default_circ_rules
-                                            (maxissueqty)
-                                            VALUES (?)");
-            my $sth_update = $dbh->prepare("UPDATE default_circ_rules
-                                            SET maxissueqty = ?");
-
-            $sth_search->execute();
-            my $res = $sth_search->fetchrow_hashref();
-            if ($res->{total}) {
-                $sth_update->execute($maxissueqty);
-            } else {
-                $sth_insert->execute($maxissueqty);
-            }
-        } else {
-            my $sth_search = $dbh->prepare("SELECT count(*) AS total
-                                            FROM default_borrower_circ_rules
-                                            WHERE categorycode = ?");
-            my $sth_insert = $dbh->prepare("INSERT INTO default_borrower_circ_rules
-                                            (categorycode, maxissueqty)
-                                            VALUES (?, ?)");
-            my $sth_update = $dbh->prepare("UPDATE default_borrower_circ_rules
-                                            SET maxissueqty = ?
-                                            WHERE categorycode = ?");
-            $sth_search->execute($branch);
-            my $res = $sth_search->fetchrow_hashref();
-            if ($res->{total}) {
-                $sth_update->execute($maxissueqty, $categorycode);
-            } else {
-                $sth_insert->execute($categorycode, $maxissueqty);
-            }
-        }
-    } elsif ($categorycode eq "*") {
+    if ($categorycode eq "*") {
         my $sth_search = $dbh->prepare("SELECT count(*) AS total
-                                        FROM default_branch_circ_rules
+                                        FROM circ_rules
                                         WHERE branchcode = ?");
-        my $sth_insert = $dbh->prepare("INSERT INTO default_branch_circ_rules
+        my $sth_insert = $dbh->prepare("INSERT INTO circ_rules
                                         (branchcode, maxissueqty)
                                         VALUES (?, ?)");
-        my $sth_update = $dbh->prepare("UPDATE default_branch_circ_rules
+        my $sth_update = $dbh->prepare("UPDATE circ_rules
                                         SET maxissueqty = ?
                                         WHERE branchcode = ?");
-        $sth_search->execute($branch);
+
+        $sth_search->execute( $branch );
         my $res = $sth_search->fetchrow_hashref();
         if ($res->{total}) {
-            $sth_update->execute($maxissueqty, $branch);
+            $sth_update->execute( $branch, $maxissueqty );
         } else {
-            $sth_insert->execute($branch, $maxissueqty);
+            $sth_insert->execute( $maxissueqty, $branch );
         }
     } else {
         my $sth_search = $dbh->prepare("SELECT count(*) AS total
-                                        FROM branch_borrower_circ_rules
+                                        FROM borrower_circ_rules
                                         WHERE branchcode = ?
-                                        AND   categorycode = ?");
-        my $sth_insert = $dbh->prepare("INSERT INTO branch_borrower_circ_rules
+                                        AND categorycode = ?");
+        my $sth_insert = $dbh->prepare("INSERT INTO borrower_circ_rules
                                         (branchcode, categorycode, maxissueqty)
                                         VALUES (?, ?, ?)");
-        my $sth_update = $dbh->prepare("UPDATE branch_borrower_circ_rules
+        my $sth_update = $dbh->prepare("UPDATE borrower_circ_rules
                                         SET maxissueqty = ?
                                         WHERE branchcode = ?
                                         AND categorycode = ?");
-
-        $sth_search->execute($branch, $categorycode);
+        $sth_search->execute( $branch, $categorycode );
         my $res = $sth_search->fetchrow_hashref();
         if ($res->{total}) {
             $sth_update->execute($maxissueqty, $branch, $categorycode);
@@ -265,52 +191,18 @@ elsif ($op eq "add-branch-item") {
     $holdallowed =~ s/\s//g;
     $holdallowed = undef if $holdallowed !~ /^\d+/;
 
-    if ($branch eq "*") {
-        if ($itemtype eq "*") {
-            my $sth_search = $dbh->prepare("SELECT count(*) AS total
-                                            FROM default_circ_rules");
-            my $sth_insert = $dbh->prepare("INSERT INTO default_circ_rules
-                                            (holdallowed, returnbranch)
-                                            VALUES (?, ?)");
-            my $sth_update = $dbh->prepare("UPDATE default_circ_rules
-                                            SET holdallowed = ?, returnbranch = ?");
-
-            $sth_search->execute();
-            my $res = $sth_search->fetchrow_hashref();
-            if ($res->{total}) {
-                $sth_update->execute($holdallowed, $returnbranch);
-            } else {
-                $sth_insert->execute($holdallowed, $returnbranch);
-            }
-        } else {
-            my $sth_search = $dbh->prepare("SELECT count(*) AS total
-                                            FROM default_branch_item_rules
-                                            WHERE itemtype = ?");
-            my $sth_insert = $dbh->prepare("INSERT INTO default_branch_item_rules
-                                            (itemtype, holdallowed, returnbranch)
-                                            VALUES (?, ?, ?)");
-            my $sth_update = $dbh->prepare("UPDATE default_branch_item_rules
-                                            SET holdallowed = ?, returnbranch = ?
-                                            WHERE itemtype = ?");
-            $sth_search->execute($itemtype);
-            my $res = $sth_search->fetchrow_hashref();
-            if ($res->{total}) {
-                $sth_update->execute($holdallowed, $returnbranch, $itemtype);
-            } else {
-                $sth_insert->execute($itemtype, $holdallowed, $returnbranch);
-            }
-        }
-    } elsif ($itemtype eq "*") {
+    if ($itemtype eq "*") {
         my $sth_search = $dbh->prepare("SELECT count(*) AS total
-                                        FROM default_branch_circ_rules
+                                        FROM circ_rules
                                         WHERE branchcode = ?");
-        my $sth_insert = $dbh->prepare("INSERT INTO default_branch_circ_rules
+        my $sth_insert = $dbh->prepare("INSERT INTO circ_rules
                                         (branchcode, holdallowed, returnbranch)
                                         VALUES (?, ?, ?)");
-        my $sth_update = $dbh->prepare("UPDATE default_branch_circ_rules
+        my $sth_update = $dbh->prepare("UPDATE circ_rules
                                         SET holdallowed = ?, returnbranch = ?
                                         WHERE branchcode = ?");
-        $sth_search->execute($branch);
+
+        $sth_search->execute( $branch );
         my $res = $sth_search->fetchrow_hashref();
         if ($res->{total}) {
             $sth_update->execute($holdallowed, $returnbranch, $branch);
@@ -319,18 +211,16 @@ elsif ($op eq "add-branch-item") {
         }
     } else {
         my $sth_search = $dbh->prepare("SELECT count(*) AS total
-                                        FROM branch_item_rules
-                                        WHERE branchcode = ?
-                                        AND   itemtype = ?");
-        my $sth_insert = $dbh->prepare("INSERT INTO branch_item_rules
+                                        FROM item_circ_rules
+                                        WHERE itemtype = ?");
+        my $sth_insert = $dbh->prepare("INSERT INTO item_circ_rules
                                         (branchcode, itemtype, holdallowed, returnbranch)
                                         VALUES (?, ?, ?, ?)");
-        my $sth_update = $dbh->prepare("UPDATE branch_item_rules
+        my $sth_update = $dbh->prepare("UPDATE item_circ_rules
                                         SET holdallowed = ?, returnbranch = ?
                                         WHERE branchcode = ?
                                         AND itemtype = ?");
-
-        $sth_search->execute($branch, $itemtype);
+        $sth_search->execute($itemtype);
         my $res = $sth_search->fetchrow_hashref();
         if ($res->{total}) {
             $sth_update->execute($holdallowed, $returnbranch, $branch, $itemtype);
@@ -385,7 +275,7 @@ while (my $row = $sth2->fetchrow_hashref) {
     $row->{'humancategorycode'} ||= $row->{'categorycode'};
     $row->{'default_humancategorycode'} = 1 if $row->{'humancategorycode'} eq '*';
     $row->{'fine'} = sprintf('%.2f', $row->{'fine'});
-    if ($row->{'hardduedate'} ne '0000-00-00') {
+    if ($row->{hardduedate} and $row->{'hardduedate'} ne '0000-00-00') {
        $row->{'hardduedate'} = format_date( $row->{'hardduedate'});
        $row->{'hardduedatebefore'} = 1 if ($row->{'hardduedatecompare'} == -1);
        $row->{'hardduedateexact'} = 1 if ($row->{'hardduedatecompare'} ==  0);
@@ -400,23 +290,13 @@ $sth->finish;
 my @sorted_row_loop = sort by_category_and_itemtype @row_loop;
 
 my $sth_branch_cat;
-if ($branch eq "*") {
-    $sth_branch_cat = $dbh->prepare("
-        SELECT default_borrower_circ_rules.*, categories.description AS humancategorycode
-        FROM default_borrower_circ_rules
-        JOIN categories USING (categorycode)
-        
-    ");
-    $sth_branch_cat->execute();
-} else {
-    $sth_branch_cat = $dbh->prepare("
-        SELECT branch_borrower_circ_rules.*, categories.description AS humancategorycode
-        FROM branch_borrower_circ_rules
-        JOIN categories USING (categorycode)
-        WHERE branch_borrower_circ_rules.branchcode = ?
-    ");
-    $sth_branch_cat->execute($branch);
-}
+$sth_branch_cat = $dbh->prepare("
+    SELECT borrower_circ_rules.*, categories.description AS humancategorycode
+    FROM borrower_circ_rules
+    JOIN categories USING (categorycode)
+    WHERE borrower_circ_rules.branchcode = ?
+");
+$sth_branch_cat->execute($branch);
 
 my @branch_cat_rules = ();
 while (my $row = $sth_branch_cat->fetchrow_hashref) {
@@ -432,22 +312,13 @@ foreach my $entry (@sorted_branch_cat_rules, @sorted_row_loop) {
 @sorted_row_loop = sort by_category_and_itemtype @row_loop;
 
 my $sth_branch_item;
-if ($branch eq "*") {
-    $sth_branch_item = $dbh->prepare("
-        SELECT default_branch_item_rules.*, itemtypes.description AS humanitemtype
-        FROM default_branch_item_rules
-        JOIN itemtypes USING (itemtype)
-    ");
-    $sth_branch_item->execute();
-} else {
-    $sth_branch_item = $dbh->prepare("
-        SELECT branch_item_rules.*, itemtypes.description AS humanitemtype
-        FROM branch_item_rules
-        JOIN itemtypes USING (itemtype)
-        WHERE branch_item_rules.branchcode = ?
-    ");
-    $sth_branch_item->execute($branch);
-}
+$sth_branch_item = $dbh->prepare("
+    SELECT item_circ_rules.*, itemtypes.description AS humanitemtype
+    FROM item_circ_rules
+    JOIN itemtypes USING (itemtype)
+    WHERE item_circ_rules.branchcode = ?
+");
+$sth_branch_item->execute($branch);
 
 my @branch_item_rules = ();
 while (my $row = $sth_branch_item->fetchrow_hashref) {
@@ -466,20 +337,12 @@ $template->param(branch_item_rule_loop => \@sorted_branch_item_rules);
 $template->param(branch_cat_rule_loop => \@sorted_branch_cat_rules);
 
 my $sth_defaults;
-if ($branch eq "*") {
-    $sth_defaults = $dbh->prepare("
-        SELECT *
-        FROM default_circ_rules
-    ");
-    $sth_defaults->execute();
-} else {
-    $sth_defaults = $dbh->prepare("
-        SELECT *
-        FROM default_branch_circ_rules
-        WHERE branchcode = ?
-    ");
-    $sth_defaults->execute($branch);
-}
+$sth_defaults = $dbh->prepare("
+    SELECT *
+    FROM circ_rules
+    WHERE branchcode = ?
+");
+$sth_defaults->execute($branch);
 
 my $defaults = $sth_defaults->fetchrow_hashref;
 
@@ -493,14 +356,15 @@ if ($defaults) {
 
 $template->param(default_rules => ($defaults ? 1 : 0));
 
-$template->param(categoryloop => \@category_loop,
-                        itemtypeloop => \@itemtypes,
-                        rules => \@sorted_row_loop,
-                        branchloop => \@branchloop,
-                        humanbranch => ($branch ne '*' ? $branches->{$branch}->{branchname} : ''),
-                        current_branch => $branch,
-                        definedbranch => scalar(@sorted_row_loop)>0 
-                        );
+$template->param(
+    categoryloop   => \@category_loop,
+    itemtypeloop   => \@itemtypes,
+    rules          => \@sorted_row_loop,
+    branchloop     => \@branchloop,
+    humanbranch    => ($branch ne '*' ? $branches->{$branch}->{branchname} : ''),
+    current_branch => $branch,
+    definedbranch  => scalar(@sorted_row_loop)>0,
+);
 output_html_with_http_headers $input, $cookie, $template->output;
 
 exit 0;
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index f68c9c7..b9abd29 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -466,73 +466,41 @@ CREATE TABLE collections_tracking (
 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
 
 --
--- Table structure for table `borrower_branch_circ_rules`
+-- Table structure for table `circ_rules`
 --
 
-DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
-CREATE TABLE `branch_borrower_circ_rules` ( -- includes default circulation rules for patron categories found under "Checkout limit by patron category"
-  `branchcode` VARCHAR(10) NOT NULL, -- the branch this rule applies to (branches.branchcode)
-  `categorycode` VARCHAR(10) NOT NULL, -- the patron category this rule applies to (categories.categorycode)
-  `maxissueqty` int(4) default NULL, -- the maximum number of checkouts this patron category can have at this branch
-  PRIMARY KEY (`categorycode`, `branchcode`),
-  CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
-    ON DELETE CASCADE ON UPDATE CASCADE,
-  CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
-    ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `default_borrower_circ_rules`
---
-
-DROP TABLE IF EXISTS `default_borrower_circ_rules`;
-CREATE TABLE `default_borrower_circ_rules` ( -- default checkout rules found under "Default checkout, hold and return policy"
-  `categorycode` VARCHAR(10) NOT NULL, -- patron category this rul
-  `maxissueqty` int(4) default NULL,
-  PRIMARY KEY (`categorycode`),
-  CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
-    ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `default_branch_circ_rules`
---
-
-DROP TABLE IF EXISTS `default_branch_circ_rules`;
-CREATE TABLE `default_branch_circ_rules` (
+DROP TABLE IF EXISTS `circ_rules`;
+CREATE TABLE `circ_rules` (
   `branchcode` VARCHAR(10) NOT NULL,
   `maxissueqty` int(4) default NULL,
   `holdallowed` tinyint(1) default NULL,
   `returnbranch` varchar(15) default NULL,
   PRIMARY KEY (`branchcode`),
-  CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
-    ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
--- Table structure for table `default_branch_item_rules`
+-- Table structure for table `borrower_circ_rules`
 --
-DROP TABLE IF EXISTS `default_branch_item_rules`;
-CREATE TABLE `default_branch_item_rules` (
-  `itemtype` varchar(10) NOT NULL,
-  `holdallowed` tinyint(1) default NULL,
-  `returnbranch` varchar(15) default NULL,
-  PRIMARY KEY  (`itemtype`),
-  CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
-    ON DELETE CASCADE ON UPDATE CASCADE
+
+DROP TABLE IF EXISTS `borrower_circ_rules`;
+CREATE TABLE `borrower_circ_rules` ( -- includes default circulation rules for patron categories found under "Checkout limit by patron category"
+  `branchcode` VARCHAR(10) NOT NULL, -- the branch this rule applies to (branches.branchcode)
+  `categorycode` VARCHAR(10) NOT NULL, -- the patron category this rule applies to (categories.categorycode)
+  `maxissueqty` int(4) default NULL, -- the maximum number of checkouts this patron category can have at this branch
+  PRIMARY KEY (`categorycode`, `branchcode`),
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
--- Table structure for table `default_circ_rules`
+-- Table structure for table `item_circ_rules`
 --
 
-DROP TABLE IF EXISTS `default_circ_rules`;
-CREATE TABLE `default_circ_rules` (
-    `singleton` enum('singleton') NOT NULL default 'singleton',
-    `maxissueqty` int(4) default NULL,
-    `holdallowed` int(1) default NULL,
-    `returnbranch` varchar(15) default NULL,
-    PRIMARY KEY (`singleton`)
+DROP TABLE IF EXISTS `item_circ_rules`;
+CREATE TABLE `item_circ_rules` (
+  `branchcode` varchar(10) NOT NULL,
+  `itemtype` varchar(10) NOT NULL,
+  `holdallowed` tinyint(1) default NULL,
+  `returnbranch` varchar(15) default NULL,
+  PRIMARY KEY  (`branchcode`, `itemtype`),
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 676bb3e..f0dc70e 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -6134,6 +6134,62 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     SetVersion ($DBversion);
 }
 
+$DBversion = "3.09.00.XXX";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+    $dbh->do(qq{
+        CREATE TABLE circ_rules (
+            branchcode VARCHAR(10) NOT NULL,
+            maxissueqty INT(4) DEFAULT NULL,
+            holdallowed TINYINT(1) DEFAULT NULL,
+            returnbranch VARCHAR(15) DEFAULT NULL,
+            PRIMARY KEY(branchcode)
+        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+    });
+    $dbh->do(qq{
+        CREATE TABLE borrower_circ_rules (
+            branchcode VARCHAR(10) NOT NULL,
+            categorycode VARCHAR(10) NOT NULL,
+            maxissueqty INT(4) DEFAULT NULL,
+            PRIMARY KEY(branchcode, categorycode)
+        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+    });
+    $dbh->do(qq{
+        CREATE TABLE item_circ_rules (
+            branchcode VARCHAR(10) NOT NULL,
+            itemtype VARCHAR(10) NOT NULL,
+            holdallowed TINYINT(1) DEFAULT NULL,
+            returnbranch VARCHAR(15) DEFAULT NULL,
+            PRIMARY KEY(branchcode, itemtype)
+        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+    });
+    $dbh->do(qq{
+        INSERT INTO circ_rules(branchcode, maxissueqty, holdallowed, returnbranch)
+        SELECT '*', maxissueqty, holdallowed, returnbranch FROM default_circ_rules;
+    });
+    $dbh->do(qq{
+        INSERT INTO circ_rules(branchcode, maxissueqty, holdallowed, returnbranch)
+        SELECT branchcode, maxissueqty, holdallowed, returnbranch FROM default_branch_circ_rules;
+    });
+    $dbh->do(qq{
+        INSERT INTO borrower_circ_rules( branchcode, categorycode, maxissueqty )
+        SELECT '*', categorycode, maxissueqty FROM default_borrower_circ_rules;
+    });
+    $dbh->do(qq{
+        INSERT INTO borrower_circ_rules( branchcode, categorycode, maxissueqty )
+        SELECT branchcode, categorycode, maxissueqty FROM branch_borrower_circ_rules;
+    });
+    $dbh->do(qq{
+        INSERT INTO item_circ_rules( branchcode, itemtype, holdallowed, returnbranch )
+        SELECT '*', itemtype, holdallowed, returnbranch FROM default_branch_item_rules;
+    });
+    $dbh->do(qq{
+        INSERT INTO item_circ_rules( branchcode, itemtype, holdallowed, returnbranch )
+        SELECT branchcode, itemtype, holdallowed, returnbranch FROM branch_item_rules;
+    });
+    print "Upgrade to $DBversion done (Replace circ rules tables. 3 new tables: circ_rules, borrower_circ_rules, item_circ_rules)\n";
+    SetVersion($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
-- 
1.7.2.5