(bug #3898) Followup : fixing Hold Policies implementation

[koha.git] / installer / data / mysql / kohastructure.sql

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index f652e50..d41b5e6 100644 (file)
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -177,7 +177,7 @@ CREATE TABLE `aqbudget` (
   `startdate` date NOT NULL default 0,
   `enddate` date default NULL,
   `budgetamount` decimal(13,2) default NULL,
-  `aqbudgetid` tinyint(4) NOT NULL auto_increment,
+  `aqbudgetid` int(11) NOT NULL auto_increment,
   `branchcode` varchar(10) default NULL,
   PRIMARY KEY  (`aqbudgetid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -388,7 +388,7 @@ CREATE TABLE `biblioitems` (
   `volume` mediumtext,
   `number` mediumtext,
   `itemtype` varchar(10) default NULL,
-  `isbn` varchar(14) default NULL,
+  `isbn` varchar(30) default NULL,
   `issn` varchar(9) default NULL,
   `publicationyear` text,
   `publishercode` varchar(255) default NULL,
@@ -419,6 +419,7 @@ CREATE TABLE `biblioitems` (
   KEY `bibinoidx` (`biblioitemnumber`),
   KEY `bibnoidx` (`biblionumber`),
   KEY `isbn` (`isbn`),
+  KEY `issn` (`issn`),
   KEY `publishercode` (`publishercode`),
   CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -521,8 +522,8 @@ DROP TABLE IF EXISTS `borrower_attributes`;
 CREATE TABLE `borrower_attributes` (
   `borrowernumber` int(11) NOT NULL,
   `code` varchar(10) NOT NULL,
-  `attribute` varchar(30) default NULL,
-  `password` varchar(30) default NULL,
+  `attribute` varchar(64) default NULL,
+  `password` varchar(64) default NULL,
   KEY `borrowernumber` (`borrowernumber`),
   KEY `code_attribute` (`code`, `attribute`),
   CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
@@ -671,11 +672,38 @@ DROP TABLE IF EXISTS `default_branch_circ_rules`;
 CREATE TABLE `default_branch_circ_rules` (
   `branchcode` VARCHAR(10) NOT NULL,
   `maxissueqty` int(4) default NULL,
+  `holdallowed` int(1) 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`
+--
+
+CREATE TABLE `default_branch_item_rules` (
+  `itemtype` varchar(10) NOT NULL,
+  `holdallowed` tinyint(1) default NULL,
+  PRIMARY KEY  (`itemtype`),
+  CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
+    ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `branch_item_rules`
+--
+
+CREATE TABLE `branch_item_rules` (
+          `branchcode` varchar(10) NOT NULL,
+          `itemtype` varchar(10) NOT NULL,
+          `holdallowed` tinyint(1) default NULL,
+          PRIMARY KEY  (`itemtype`,`branchcode`),
+          KEY `branch_item_rules_ibfk_2` (`branchcode`),
+          CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE,
+          CONSTRAINT `branch_item_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_circ_rules`
 --
@@ -684,6 +712,7 @@ 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,
     PRIMARY KEY (`singleton`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
@@ -774,7 +803,7 @@ CREATE TABLE `deletedbiblioitems` (
   `volume` mediumtext,
   `number` mediumtext,
   `itemtype` varchar(10) default NULL,
-  `isbn` varchar(14) default NULL,
+  `isbn` varchar(30) default NULL,
   `issn` varchar(9) default NULL,
   `publicationyear` text,
   `publishercode` varchar(255) default NULL,
@@ -842,7 +871,7 @@ CREATE TABLE `deletedborrowers` (
   `B_phone` mediumtext,
   `dateofbirth` date default NULL,
   `branchcode` varchar(10) NOT NULL default '',
-  `categorycode` varchar(2) default NULL,
+  `categorycode` varchar(10) default NULL,
   `dateenrolled` date default NULL,
   `dateexpiry` date default NULL,
   `gonenoaddress` tinyint(1) default NULL,
@@ -938,6 +967,29 @@ CREATE TABLE `ethnicity` (
   PRIMARY KEY  (`code`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
+--
+-- Table structure for table `hold_fill_targets`
+--
+
+DROP TABLE IF EXISTS `hold_fill_targets`;
+CREATE TABLE hold_fill_targets (
+  `borrowernumber` int(11) NOT NULL,
+  `biblionumber` int(11) NOT NULL,
+  `itemnumber` int(11) NOT NULL,
+  `source_branchcode`  varchar(10) default NULL,
+  `item_level_request` tinyint(4) NOT NULL default 0,
+  PRIMARY KEY `itemnumber` (`itemnumber`),
+  KEY `bib_branch` (`biblionumber`, `source_branchcode`),
+  CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`) 
+    REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`) 
+    REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`) 
+    REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`) 
+    REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
 --
 -- Table structure for table `import_batches`
 --
@@ -1015,7 +1067,7 @@ CREATE TABLE `import_biblios` (
   `original_source` varchar(25) default NULL,
   `title` varchar(128) default NULL,
   `author` varchar(80) default NULL,
-  `isbn` varchar(14) default NULL,
+  `isbn` varchar(30) default NULL,
   `issn` varchar(9) default NULL,
   `has_items` tinyint(1) NOT NULL default 0,
   CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`) 
@@ -1081,6 +1133,7 @@ CREATE TABLE `issuingrules` (
   `rentaldiscount` decimal(28,6) default NULL,
   `reservecharge` decimal(28,6) default NULL,
   `fine` decimal(28,6) default NULL,
+  `finedays` int(11) default NULL,
   `firstremind` int(11) default NULL,
   `chargeperiod` int(11) default NULL,
   `accountsent` int(11) default NULL,
@@ -1508,7 +1561,7 @@ CREATE TABLE `opac_news` (
 DROP TABLE IF EXISTS `overduerules`;
 CREATE TABLE `overduerules` (
   `branchcode` varchar(10) NOT NULL default '',
-  `categorycode` varchar(2) NOT NULL default '',
+  `categorycode` varchar(10) NOT NULL default '',
   `delay1` int(4) default 0,
   `letter1` varchar(20) default NULL,
   `debarred1` varchar(1) default 0,
@@ -1898,7 +1951,7 @@ CREATE TABLE `suggestions` (
   `volumedesc` varchar(255) default NULL,
   `publicationyear` smallint(6) default 0,
   `place` varchar(255) default NULL,
-  `isbn` varchar(10) default NULL,
+  `isbn` varchar(30) default NULL,
   `mailoverseeing` smallint(1) default 0,
   `biblionumber` int(11) default NULL,
   `reason` text,
@@ -1936,6 +1989,7 @@ CREATE TABLE `tags` (
 -- Table structure for table `tags_all`
 --
 
+DROP TABLE IF EXISTS `tags_all`;
 CREATE TABLE `tags_all` (
   `tag_id`         int(11) NOT NULL auto_increment,
   `borrowernumber` int(11) NOT NULL,
@@ -1956,6 +2010,7 @@ CREATE TABLE `tags_all` (
 -- Table structure for table `tags_approval`
 --
 
+DROP TABLE IF EXISTS `tags_approval`;
 CREATE TABLE `tags_approval` (
   `term`   varchar(255) NOT NULL,
   `approved`     int(1) NOT NULL default '0',
@@ -1972,6 +2027,7 @@ CREATE TABLE `tags_approval` (
 -- Table structure for table `tags_index`
 --
 
+DROP TABLE IF EXISTS `tags_index`;
 CREATE TABLE `tags_index` (
   `term`    varchar(255) NOT NULL,
   `biblionumber` int(11) NOT NULL,
@@ -2008,6 +2064,7 @@ CREATE TABLE `virtualshelves` (
   `owner` varchar(80) default NULL,
   `category` varchar(1) default NULL,
   `sortfield` varchar(16) default NULL,
+  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
   PRIMARY KEY  (`shelfnumber`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
@@ -2058,7 +2115,7 @@ CREATE TABLE `z3950servers` (
 DROP TABLE IF EXISTS `zebraqueue`;
 CREATE TABLE `zebraqueue` (
   `id` int(11) NOT NULL auto_increment,
-  `biblio_auth_number` int(11) NOT NULL default '0',
+  `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
   `operation` char(20) NOT NULL default '',
   `server` char(20) NOT NULL default '',
   `done` int(11) NOT NULL default '0',
@@ -2125,25 +2182,27 @@ CREATE TABLE language_script_mapping (
 DROP TABLE IF EXISTS `permissions`;
 CREATE TABLE `permissions` (
   `module_bit` int(11) NOT NULL DEFAULT 0,
-  `code` varchar(30) DEFAULT NULL,
+  `code` varchar(64) DEFAULT NULL,
   `description` varchar(255) DEFAULT NULL,
   PRIMARY KEY  (`module_bit`, `code`),
   CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
     ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-DROP TABLE IF EXISTS serialitems;
-CREATE TABLE serialitems (
-        serialid int(11) NOT NULL,
-        itemnumber int(11) NOT NULL,
-        UNIQUE KEY `serialididx` (`serialid`)
+DROP TABLE IF EXISTS `serialitems`;
+CREATE TABLE `serialitems` (
+       `itemnumber` int(11) NOT NULL,
+       `serialid` int(11) NOT NULL,
+       UNIQUE KEY `serialitemsidx` (`itemnumber`),
+       KEY `serialitems_sfk_1` (`serialid`),
+       CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
+                 
 DROP TABLE IF EXISTS `user_permissions`;
 CREATE TABLE `user_permissions` (
   `borrowernumber` int(11) NOT NULL DEFAULT 0,
   `module_bit` int(11) NOT NULL DEFAULT 0,
-  `code` varchar(30) DEFAULT NULL,
+  `code` varchar(64) DEFAULT NULL,
   CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
     ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
@@ -2169,22 +2228,26 @@ CREATE TABLE `tmp_holdsqueue` (
   `itemcallnumber` varchar(30) default NULL,
   `holdingbranch` varchar(10) default NULL,
   `pickbranch` varchar(10) default NULL,
-  `notes` text
+  `notes` text,
+  `item_level_request` tinyint(4) NOT NULL default 0
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
 -- Table structure for table `message_queue`
 --
 
-DROP TABLE if EXISTS `message_queue`;
+DROP TABLE IF EXISTS `message_queue`;
 CREATE TABLE `message_queue` (
   `message_id` int(11) NOT NULL auto_increment,
-  `borrowernumber` int(11) NOT NULL,
+  `borrowernumber` int(11) default NULL,
   `subject` text,
   `content` text,
   `message_transport_type` varchar(20) NOT NULL,
   `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
   `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+  `to_address` mediumtext,
+  `from_address` mediumtext,
+  `content_type` text,
   KEY `message_id` (`message_id`),
   KEY `borrowernumber` (`borrowernumber`),
   KEY `message_transport_type` (`message_transport_type`),
@@ -2266,6 +2329,21 @@ CREATE TABLE `borrower_message_transport_preferences` (
   CONSTRAINT `borrower_message_transport_preferences_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
+--
+-- Table structure for table `fieldmapping`
+--
+
+DROP TABLE IF EXISTS `fieldmapping`;
+CREATE TABLE `fieldmapping` (
+  `id` int(11) NOT NULL auto_increment,
+  `field` varchar(255) NOT NULL,
+  `frameworkcode` char(4) NOT NULL default '',
+  `fieldcode` char(3) NOT NULL,
+  `subfieldcode` char(1) NOT NULL,
+  PRIMARY KEY  (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;