From 514bfb95517ce190e1d6861cb2f43d751113a3bf Mon Sep 17 00:00:00 2001 From: Jonathan Druart 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. --- 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 5cbc211..a46873a 100644 --- a/C4/Circulation.pm +++ b/C4/Circulation.pm @@ -1465,13 +1465,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: @@ -1489,7 +1488,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(); @@ -1502,7 +1501,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); @@ -1512,8 +1511,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()) { @@ -1522,7 +1523,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()) { @@ -1571,17 +1573,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 29ee782..cad5c2c 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 7ab48ee..a74a492 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -6020,6 +6020,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.10.4