While testing the commits for 6716 I discovered a couple of missing

[srvgit] / installer / data / mysql / kohastructure.sql

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index e0bea81..32343a9 100644 (file)
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -206,6 +206,7 @@ CREATE TABLE `borrowers` (
   `address` mediumtext NOT NULL,
   `address2` text,
   `city` mediumtext NOT NULL,
+  `state` text default NULL,
   `zipcode` varchar(25) default NULL,
   `country` text,
   `email` mediumtext,
@@ -219,6 +220,7 @@ CREATE TABLE `borrowers` (
   `B_address` varchar(100) default NULL,
   `B_address2` text default NULL,
   `B_city` mediumtext,
+  `B_state` text default NULL,
   `B_zipcode` varchar(25) default NULL,
   `B_country` text,
   `B_email` text,
@@ -252,6 +254,7 @@ CREATE TABLE `borrowers` (
   `altcontactaddress1` varchar(255) default NULL,
   `altcontactaddress2` varchar(255) default NULL,
   `altcontactaddress3` varchar(255) default NULL,
+  `altcontactstate` text default NULL,
   `altcontactzipcode` varchar(50) default NULL,
   `altcontactcountry` text default NULL,
   `altcontactphone` varchar(50) default NULL,
@@ -342,6 +345,7 @@ CREATE TABLE `branches` (
   `branchaddress3` mediumtext,
   `branchzip` varchar(25) default NULL,
   `branchcity` mediumtext,
+  `branchstate` mediumtext,
   `branchcountry` text,
   `branchphone` mediumtext,
   `branchfax` mediumtext,
@@ -429,6 +433,7 @@ CREATE TABLE `categories` (
 --
 -- Table: collections
 --
+DROP TABLE IF EXISTS collections;
 CREATE TABLE collections (
   colId integer(11) NOT NULL auto_increment,
   colTitle varchar(100) NOT NULL DEFAULT '',
@@ -440,6 +445,7 @@ CREATE TABLE collections (
 --
 -- Table: collections_tracking
 --
+DROP TABLE IF EXISTS collections_tracking;
 CREATE TABLE collections_tracking (
   ctId integer(11) NOT NULL auto_increment,
   colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId',
@@ -522,6 +528,8 @@ DROP TABLE IF EXISTS `cities`;
 CREATE TABLE `cities` (
   `cityid` int(11) NOT NULL auto_increment,
   `city_name` varchar(100) NOT NULL default '',
+  `city_state` VARCHAR( 100 ) NULL DEFAULT NULL,
+  `city_country` VARCHAR( 100 ) NULL DEFAULT NULL,
   `city_zipcode` varchar(20) default NULL,
   PRIMARY KEY  (`cityid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -654,6 +662,7 @@ CREATE TABLE `deletedborrowers` (
   `address` mediumtext NOT NULL,
   `address2` text,
   `city` mediumtext NOT NULL,
+  `state` text default NULL,
   `zipcode` varchar(25) default NULL,
   `country` text,
   `email` mediumtext,
@@ -667,6 +676,7 @@ CREATE TABLE `deletedborrowers` (
   `B_address` varchar(100) default NULL,
   `B_address2` text default NULL,
   `B_city` mediumtext,
+  `B_state` text default NULL,
   `B_zipcode` varchar(25) default NULL,
   `B_country` text,
   `B_email` text,
@@ -700,6 +710,7 @@ CREATE TABLE `deletedborrowers` (
   `altcontactaddress1` varchar(255) default NULL,
   `altcontactaddress2` varchar(255) default NULL,
   `altcontactaddress3` varchar(255) default NULL,
+  `altcontactstate` text default NULL,
   `altcontactzipcode` varchar(50) default NULL,
   `altcontactcountry` text default NULL,
   `altcontactphone` varchar(50) default NULL,
@@ -929,8 +940,8 @@ CREATE TABLE `import_items` (
 
 DROP TABLE IF EXISTS `issues`;
 CREATE TABLE `issues` (
-  `borrowernumber` int(11) default NULL,
-  `itemnumber` int(11) default NULL,
+  `borrowernumber` int(11),
+  `itemnumber` int(11),
   `date_due` date default NULL,
   `branchcode` varchar(10) default NULL,
   `issuingbranch` varchar(18) default NULL,
@@ -940,11 +951,11 @@ CREATE TABLE `issues` (
   `renewals` tinyint(4) default NULL,
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
   `issuedate` date default NULL,
+  PRIMARY KEY (`itemnumber`),
   KEY `issuesborridx` (`borrowernumber`),
-  KEY `issuesitemidx` (`itemnumber`),
   KEY `bordate` (`borrowernumber`,`timestamp`),
-  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
-  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
+  CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
+  CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
@@ -1023,7 +1034,7 @@ CREATE TABLE `items` (
   `stocknumber` varchar(32) default NULL,
   PRIMARY KEY  (`itemnumber`),
   UNIQUE KEY `itembarcodeidx` (`barcode`),
-  UNIQUE KEY `itemstocknumberidx` (`stocknumber`),
+  KEY `itemstocknumberidx` (`stocknumber`),
   KEY `itembinoidx` (`biblioitemnumber`),
   KEY `itembibnoidx` (`biblionumber`),
   KEY `homebranch` (`homebranch`),
@@ -1407,13 +1418,13 @@ DROP TABLE IF EXISTS `overduerules`;
 CREATE TABLE `overduerules` (
   `branchcode` varchar(10) NOT NULL default '',
   `categorycode` varchar(10) NOT NULL default '',
-  `delay1` int(4) default 0,
+  `delay1` int(4) default NULL,
   `letter1` varchar(20) default NULL,
   `debarred1` varchar(1) default 0,
-  `delay2` int(4) default 0,
+  `delay2` int(4) default NULL,
   `debarred2` varchar(1) default 0,
   `letter2` varchar(20) default NULL,
-  `delay3` int(4) default 0,
+  `delay3` int(4) default NULL,
   `letter3` varchar(20) default NULL,
   `debarred3` int(1) default 0,
   PRIMARY KEY  (`branchcode`,`categorycode`)
@@ -1546,6 +1557,7 @@ CREATE TABLE `reserves` (
   `waitingdate` date default NULL,
   `expirationdate` DATE DEFAULT NULL,
   `lowestPriority` tinyint(1) NOT NULL,
+  KEY priorityfoundidx (priority,found),
   KEY `borrowernumber` (`borrowernumber`),
   KEY `biblionumber` (`biblionumber`),
   KEY `itemnumber` (`itemnumber`),
@@ -2091,7 +2103,7 @@ CREATE TABLE `serialitems` (
        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,
-       CONSTRAINT serialitems_sfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
+       CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `user_permissions`;
@@ -2292,6 +2304,8 @@ CREATE TABLE `accountlines` (
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
   `notify_id` int(11) NOT NULL default 0,
   `notify_level` int(2) NOT NULL default 0,
+  `note` text NULL default NULL,
+  `manager_id` int(11) NULL,
   KEY `acctsborridx` (`borrowernumber`),
   KEY `timeidx` (`timestamp`),
   KEY `itemnumber` (`itemnumber`),
@@ -2356,6 +2370,7 @@ CREATE TABLE `aqbasketgroups` (
   `closed` tinyint(1) default NULL,
   `booksellerid` int(11) NOT NULL,
   `deliveryplace` varchar(10) default NULL,
+  `freedeliveryplace` text default NULL,
   `deliverycomment` varchar(255) default NULL,
   `billingplace` varchar(10) default NULL,
   PRIMARY KEY  (`id`),