From b35ebc644e143f928ceeca2e04e25085743e82cc Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Fri, 20 Jun 2008 08:11:13 -0500 Subject: [PATCH] bug 2000 - added several circ rules tables (DB rev 090) The first new table is branch_borrower_circ_rules. This table is used to store circulation rule attributes that apply to a combination of patron category and branch across all item types. The one attribute defined is maxissueqty, which sets the maximum number of loans that a patron of a given category can take out at a given branch. Note that branch_borrower_circ_rules is for attributes that apply across all item types. This means that issuingrules.maxissueqty has a different meaning: it is the maximum number of loans per branch, category, and item type; if issuingrules.itemtype is '*', that is a *default* circulation rule used if no more specific rule is found. The new table will allow the implementation of total loan limit across item types without making the wildcard '*' in issuingrules ambiguous. Specifically, if branchcode, categorycode, or itemtype is issuingrules is '*', that will now always mean a loan rule to be applied if a more specific rule cannot be found. Setting issuingrules.itemtype to '*' will no longer mean to set a total limit across item types for maxissueqty. The remaining new tables are used to store default rules for the default branch, the default patron category, or both: default_branch_circ_rules - for a given branch, specify the rule to apply if no more specific rule on branch and patron category is found (i.e. patron category is default) default_borrower_circ_rules - for a given patron category, specify the rule to apply if no more specific rule on branch patron category is found (i.e., branch is default) default_circ_rules - global default if no more specify rule on patron category and branch is available. Note that this table is constructed so that it can have at most one row. Signed-off-by: Joshua Ferraro --- installer/data/mysql/kohastructure.sql | 53 ++++++++++++++++++++++++++ installer/data/mysql/updatedatabase.pl | 44 ++++++++++++++++++++- kohaversion.pl | 2 +- 3 files changed, 97 insertions(+), 2 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 90f59ba278..1d973a32f9 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -633,6 +633,59 @@ CREATE TABLE `categories` ( UNIQUE KEY `categorycode` (`categorycode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `borrower_branch_circ_rules` +-- + +DROP TABLE IF EXISTS `branch_borrower_circ_rules`; +CREATE TABLE `branch_borrower_circ_rules` ( + `branchcode` VARCHAR(10) NOT NULL, + `categorycode` VARCHAR(10) NOT NULL, + `maxissueqty` int(4) default NULL, + 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` ( + `categorycode` VARCHAR(10) NOT NULL, + `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` ( + `branchcode` VARCHAR(10) NOT NULL, + `maxissueqty` int(4) 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_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, + PRIMARY KEY (`singleton`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + -- -- Table structure for table `cities` -- diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index df5b8cb666..8297a757ae 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -1611,7 +1611,6 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } - $DBversion = "3.00.00.088"; if (C4::Context->preference("Version") < TransformToNum($DBversion)) { $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACShelfBrowser','1','','Enable/disable Shelf Browser on item details page','YesNo')"); @@ -1629,6 +1628,49 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +$DBversion = "3.00.00.090"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do(" + CREATE TABLE `branch_borrower_circ_rules` ( + `branchcode` VARCHAR(10) NOT NULL, + `categorycode` VARCHAR(10) NOT NULL, + `maxissueqty` int(4) default NULL, + 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 + "); + $dbh->do(" + CREATE TABLE `default_borrower_circ_rules` ( + `categorycode` VARCHAR(10) NOT NULL, + `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 + "); + $dbh->do(" + CREATE TABLE `default_branch_circ_rules` ( + `branchcode` VARCHAR(10) NOT NULL, + `maxissueqty` int(4) 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 + "); + $dbh->do(" + CREATE TABLE `default_circ_rules` ( + `singleton` enum('singleton') NOT NULL default 'singleton', + `maxissueqty` int(4) default NULL, + PRIMARY KEY (`singleton`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 + "); + print "Upgrade to $DBversion done (added several circ rules tables)\n"; + SetVersion ($DBversion); +} + =item DropAllForeignKeys($table) Drop all foreign keys of the table $table diff --git a/kohaversion.pl b/kohaversion.pl index 5f23daa6de..10835c17dd 100644 --- a/kohaversion.pl +++ b/kohaversion.pl @@ -10,7 +10,7 @@ use strict; sub kohaversion { - our $VERSION = "3.00.00.089"; + our $VERSION = "3.00.00.090"; # version needs to be set this way # so that it can be picked up by Makefile.PL # during install -- 2.39.5