(bug #3370) add keyword to MARC field mapping

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

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index a7e555f..5145bda 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;
@@ -341,7 +341,8 @@ CREATE TABLE `authorised_values` (
   `lib` varchar(80) default NULL,
   `imageurl` varchar(200) default NULL,
   PRIMARY KEY  (`id`),
-  KEY `name` (`category`)
+  KEY `name` (`category`),
+  KEY `lib` (`lib`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
@@ -388,7 +389,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 +420,7 @@ CREATE TABLE `biblioitems` (
   KEY `bibinoidx` (`biblioitemnumber`),
   KEY `bibnoidx` (`biblionumber`),
   KEY `isbn` (`isbn`),
+  KEY `issn` (`issn`),
   KEY `publishercode` (`publishercode`),
   KEY `issn` (`issn`),
   CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
@@ -801,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,
@@ -862,6 +864,7 @@ CREATE TABLE `deletedborrowers` (
   `phonepro` text,
   `B_streetnumber` varchar(10) default NULL,
   `B_streettype` varchar(50) default NULL,
+  `smsalertnumber` varchar(50) default NULL,
   `B_address` varchar(100) default NULL,
   `B_city` mediumtext,
   `B_zipcode` varchar(25) default NULL,
@@ -898,6 +901,7 @@ CREATE TABLE `deletedborrowers` (
   `altcontactaddress3` varchar(255) default NULL,
   `altcontactzipcode` varchar(50) default NULL,
   `altcontactphone` varchar(50) default NULL,
+  `smsalertnumber` varchar(50) default NULL,
   KEY `borrowernumber` (`borrowernumber`),
   KEY `cardnumber` (`cardnumber`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -1065,7 +1069,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`) 
@@ -1131,12 +1135,15 @@ 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,
   `chargename` varchar(100) default NULL,
   `maxissueqty` int(4) default NULL,
   `issuelength` int(4) default NULL,
+  `renewalsallowed` smallint(6) NOT NULL default "0",
+  `reservesallowed` smallint(6) NOT NULL default "0",
   `branchcode` varchar(10) NOT NULL default '',
   PRIMARY KEY  (`branchcode`,`categorycode`,`itemtype`),
   KEY `categorycode` (`categorycode`),
@@ -1205,7 +1212,6 @@ DROP TABLE IF EXISTS `itemtypes`;
 CREATE TABLE `itemtypes` (
   `itemtype` varchar(10) NOT NULL default '',
   `description` mediumtext,
-  `renewalsallowed` smallint(6) default NULL,
   `rentalcharge` double(16,4) default NULL,
   `notforloan` smallint(6) default NULL,
   `imageurl` varchar(200) default NULL,
@@ -1221,7 +1227,7 @@ CREATE TABLE `itemtypes` (
 DROP TABLE IF EXISTS `labels`;
 CREATE TABLE `labels` (
   `labelid` int(11) NOT NULL auto_increment,
-  `batch_id` varchar(10) NOT NULL default 1,
+  `batch_id` int(10) NOT NULL default 1,
   `itemnumber` varchar(100) NOT NULL default '',
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
   PRIMARY KEY  (`labelid`)
@@ -1248,7 +1254,7 @@ CREATE TABLE `labels_conf` (
   `isbn` int(1) default '0',
   `startlabel` int(2) NOT NULL default '1',
   `printingtype` char(32) default 'BAR',
-  `formatstring` varchar(64) default NULL,
+  `formatstring` mediumtext default NULL,
   `layoutname` char(20) NOT NULL default 'TEST',
   `guidebox` int(1) default '0',
   `active` tinyint(1) default '1',
@@ -1891,10 +1897,14 @@ CREATE TABLE `subscription` (
   `distributedto` text,
   `internalnotes` longtext,
   `callnumber` text,
+  `location` varchar(80) NULL default '',
   `branchcode` varchar(10) NOT NULL default '',
   `hemisphere` tinyint(3) default 0,
   `lastbranch` varchar(10),
   `serialsadditems` tinyint(1) NOT NULL default '0',
+  `staffdisplaycount` VARCHAR(10) NULL,
+  `opacdisplaycount` VARCHAR(10) NULL,
+  `graceperiod` int(11) NOT NULL default '0',
   PRIMARY KEY  (`subscriptionid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
@@ -1948,7 +1958,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,
@@ -2112,7 +2122,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',
@@ -2303,15 +2313,18 @@ CREATE TABLE `message_transports` (
 DROP TABLE IF EXISTS `borrower_message_preferences`;
 CREATE TABLE `borrower_message_preferences` (
   `borrower_message_preference_id` int(11) NOT NULL auto_increment,
-  `borrowernumber` int(11) NOT NULL default '0',
+  `borrowernumber` int(11) default NULL,
+  `categorycode` varchar(10) default NULL,
   `message_attribute_id` int(11) default '0',
   `days_in_advance` int(11) default '0',
   `wants_digest` tinyint(1) NOT NULL default '0',
   PRIMARY KEY  (`borrower_message_preference_id`),
   KEY `borrowernumber` (`borrowernumber`),
+  KEY `categorycode` (`categorycode`),
   KEY `message_attribute_id` (`message_attribute_id`),
   CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
-  CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
+  CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
@@ -2335,9 +2348,9 @@ CREATE TABLE `borrower_message_transport_preferences` (
 DROP TABLE IF EXISTS `branch_transfer_limits`;
 CREATE TABLE branch_transfer_limits (
     limitId int(8) NOT NULL auto_increment,
-    toBranch varchar(4) NOT NULL,
-    fromBranch varchar(4) NOT NULL,
-    itemtype varchar(4) NULL,
+    toBranch varchar(10) NOT NULL,
+    fromBranch varchar(10) NOT NULL,
+    itemtype varchar(10) NULL,
     ccode varchar(10) NULL,  
     PRIMARY KEY  (limitId)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -2357,6 +2370,21 @@ CREATE TABLE `item_circulation_alert_preferences` (
   KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
 ) 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 */;