X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=09aaf9773fb217424d09d4a7dde3452135de1ccf;hb=f91e8fb509639c7515545470470172f07ace4615;hp=42f42a30b665a983856fcd8a5f895fb641be19b1;hpb=bc895d86267d0084cdd7df35617bb572a36d22e3;p=koha.git diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 42f42a30b6..09aaf9773f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -30,6 +30,7 @@ CREATE TABLE `accountlines` ( `dispute` mediumtext, `accounttype` varchar(5) default NULL, `amountoutstanding` decimal(28,6) default NULL, + `lastincrement` decimal(28,6) default NULL, `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, @@ -123,7 +124,7 @@ CREATE TABLE `aqbookfund` ( DROP TABLE IF EXISTS `aqbooksellers`; CREATE TABLE `aqbooksellers` ( `id` int(11) NOT NULL auto_increment, - `name` mediumtext, + `name` mediumtext NOT NULL, `address1` mediumtext, `address2` mediumtext, `address3` mediumtext, @@ -338,6 +339,7 @@ CREATE TABLE `authorised_values` ( `category` varchar(10) NOT NULL default '', `authorised_value` varchar(80) NOT NULL default '', `lib` varchar(80) default NULL, + `imageurl` varchar(200) default NULL, PRIMARY KEY (`id`), KEY `name` (`category`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -484,6 +486,7 @@ CREATE TABLE `borrowers` ( `altcontactaddress3` varchar(255) default NULL, `altcontactzipcode` varchar(50) default NULL, `altcontactphone` varchar(50) default NULL, + `smsalertnumber` varchar(50) default NULL, UNIQUE KEY `cardnumber` (`cardnumber`), PRIMARY KEY `borrowernumber` (`borrowernumber`), KEY `categorycode` (`categorycode`), @@ -493,6 +496,41 @@ CREATE TABLE `borrowers` ( CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `borrower_attribute_types` +-- + +DROP TABLE IF EXISTS `borrower_attribute_types`; +CREATE TABLE `borrower_attribute_types` ( + `code` varchar(10) NOT NULL, + `description` varchar(255) NOT NULL, + `repeatable` tinyint(1) NOT NULL default 0, + `unique_id` tinyint(1) NOT NULL default 0, + `opac_display` tinyint(1) NOT NULL default 0, + `password_allowed` tinyint(1) NOT NULL default 0, + `staff_searchable` tinyint(1) NOT NULL default 0, + `authorised_value_category` varchar(10) default NULL, + PRIMARY KEY (`code`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `borrower_attributes` +-- + +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, + KEY `borrowernumber` (`borrowernumber`), + KEY `code_attribute` (`code`, `attribute`), + CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + -- -- Table structure for table `branchcategories` -- @@ -597,15 +635,56 @@ CREATE TABLE `categories` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table structure for table `categorytable` +-- Table structure for table `borrower_branch_circ_rules` -- -DROP TABLE IF EXISTS `categorytable`; -CREATE TABLE `categorytable` ( - `categorycode` varchar(5) NOT NULL default '', - `description` text, - `itemtypecodes` text, - PRIMARY KEY (`categorycode`) +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; -- @@ -763,7 +842,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, @@ -807,7 +886,7 @@ CREATE TABLE `deleteditems` ( `biblioitemnumber` int(11) NOT NULL default 0, `barcode` varchar(20) default NULL, `dateaccessioned` date default NULL, - `booksellerid` varchar(10) default NULL, + `booksellerid` mediumtext default NULL, `homebranch` varchar(10) default NULL, `price` decimal(8,2) default NULL, `replacementprice` decimal(8,2) default NULL, @@ -837,6 +916,8 @@ CREATE TABLE `deleteditems` ( `uri` varchar(255) default NULL, `itype` varchar(10) default NULL, `more_subfields_xml` longtext default NULL, + `enumchron` varchar(80) default NULL, + `copynumber` varchar(32) default NULL, `marc` longblob, PRIMARY KEY (`itemnumber`), KEY `delitembarcodeidx` (`barcode`), @@ -857,6 +938,28 @@ CREATE TABLE `ethnicity` ( PRIMARY KEY (`code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `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` -- @@ -870,7 +973,9 @@ CREATE TABLE `import_batches` ( `num_biblios` int(11) NOT NULL default 0, `num_items` int(11) NOT NULL default 0, `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, - `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new', + `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new', + `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new', + `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add', `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging', `batch_type` enum('batch', 'z3950') NOT NULL default 'batch', `file_name` varchar(100), @@ -896,7 +1001,7 @@ CREATE TABLE `import_records` ( `marcxml_old` longtext NOT NULL, `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio', `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match', - `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged', + `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged', `import_error` mediumtext, `encoding` varchar(40) NOT NULL default '', `z3950random` varchar(40) default NULL, @@ -952,7 +1057,7 @@ CREATE TABLE `import_items` ( `import_record_id` int(11) NOT NULL, `itemnumber` int(11) default NULL, `branchcode` varchar(10) default NULL, - `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged', + `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged', `marcxml` longtext NOT NULL, `import_error` mediumtext, PRIMARY KEY (`import_items_id`), @@ -1021,7 +1126,7 @@ CREATE TABLE `items` ( `biblioitemnumber` int(11) NOT NULL default 0, `barcode` varchar(20) default NULL, `dateaccessioned` date default NULL, - `booksellerid` varchar(10) default NULL, + `booksellerid` mediumtext default NULL, `homebranch` varchar(10) default NULL, `price` decimal(8,2) default NULL, `replacementprice` decimal(8,2) default NULL, @@ -1051,6 +1156,8 @@ CREATE TABLE `items` ( `uri` varchar(255) default NULL, `itype` varchar(10) default NULL, `more_subfields_xml` longtext default NULL, + `enumchron` varchar(80) default NULL, + `copynumber` varchar(32) default NULL, PRIMARY KEY (`itemnumber`), UNIQUE KEY `itembarcodeidx` (`barcode`), KEY `itembinoidx` (`biblioitemnumber`), @@ -1105,7 +1212,7 @@ CREATE TABLE `labels_conf` ( `itemtype` int(1) default '0', `barcode` int(1) default '0', `dewey` int(1) default '0', - `class` int(1) default NULL, + `classification` int(1) default NULL, `subclass` int(1) default '0', `itemcallnumber` int(1) default '0', `author` int(1) default '0', @@ -1113,6 +1220,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, `layoutname` char(20) NOT NULL default 'TEST', `guidebox` int(1) default '0', `active` tinyint(1) default '1', @@ -1157,6 +1265,7 @@ CREATE TABLE `labels_templates` ( `active` int(1) default NULL, `units` char(20) default 'PX', `fontsize` int(4) NOT NULL default '3', + `font` char(10) NOT NULL default 'TR', PRIMARY KEY (`tmpl_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1181,7 +1290,7 @@ CREATE TABLE `letter` ( DROP TABLE IF EXISTS `marc_subfield_structure`; CREATE TABLE `marc_subfield_structure` ( `tagfield` varchar(3) NOT NULL default '', - `tagsubfield` varchar(1) NOT NULL default '', + `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin, `liblibrarian` varchar(255) NOT NULL default '', `libopac` varchar(255) NOT NULL default '', `repeatable` tinyint(4) NOT NULL default 0, @@ -1312,18 +1421,6 @@ CREATE TABLE `matchchecks` ( REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --- --- Table structure for table `mediatypetable` --- - -DROP TABLE IF EXISTS `mediatypetable`; -CREATE TABLE `mediatypetable` ( - `mediatypecode` varchar(5) NOT NULL default '', - `description` text, - `itemtypecodes` text, - PRIMARY KEY (`mediatypecode`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -- -- Table structure for table `notifys` -- @@ -1342,6 +1439,7 @@ CREATE TABLE `notifys` ( -- -- Table structure for table `nozebra` -- + DROP TABLE IF EXISTS `nozebra`; CREATE TABLE `nozebra` ( `server` varchar(20) NOT NULL, @@ -1352,6 +1450,63 @@ CREATE TABLE `nozebra` ( KEY `value` (`server`,`value`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `old_issues` +-- + +DROP TABLE IF EXISTS `old_issues`; +CREATE TABLE `old_issues` ( + `borrowernumber` int(11) default NULL, + `itemnumber` int(11) default NULL, + `date_due` date default NULL, + `branchcode` varchar(10) default NULL, + `issuingbranch` varchar(18) default NULL, + `returndate` date default NULL, + `lastreneweddate` date default NULL, + `return` varchar(4) default NULL, + `renewals` tinyint(4) default NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `issuedate` date default NULL, + KEY `old_issuesborridx` (`borrowernumber`), + KEY `old_issuesitemidx` (`itemnumber`), + KEY `old_bordate` (`borrowernumber`,`timestamp`), + CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) + ON DELETE SET NULL ON UPDATE SET NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `old_reserves` +-- +DROP TABLE IF EXISTS `old_reserves`; +CREATE TABLE `old_reserves` ( + `borrowernumber` int(11) default NULL, + `reservedate` date default NULL, + `biblionumber` int(11) default NULL, + `constrainttype` varchar(1) default NULL, + `branchcode` varchar(10) default NULL, + `notificationdate` date default NULL, + `reminderdate` date default NULL, + `cancellationdate` date default NULL, + `reservenotes` mediumtext, + `priority` smallint(6) default NULL, + `found` varchar(1) default NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `itemnumber` int(11) default NULL, + `waitingdate` date default NULL, + KEY `old_reserves_borrowernumber` (`borrowernumber`), + KEY `old_reserves_biblionumber` (`biblionumber`), + KEY `old_reserves_itemnumber` (`itemnumber`), + KEY `old_reserves_branchcode` (`branchcode`), + CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) + ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) + ON DELETE SET NULL ON UPDATE SET NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + -- -- Table structure for table `opac_news` -- @@ -1361,7 +1516,7 @@ CREATE TABLE `opac_news` ( `idnew` int(10) unsigned NOT NULL auto_increment, `title` varchar(250) NOT NULL default '', `new` text NOT NULL, - `lang` varchar(4) NOT NULL default '', + `lang` varchar(25) NOT NULL default '', `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, `expirationdate` date default NULL, `number` int(11) default NULL, @@ -1375,7 +1530,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, @@ -1388,6 +1543,34 @@ CREATE TABLE `overduerules` ( PRIMARY KEY (`branchcode`,`categorycode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `patroncards` +-- + +DROP TABLE IF EXISTS `patroncards`; +CREATE TABLE `patroncards` ( + `cardid` int(11) NOT NULL auto_increment, + `batch_id` varchar(10) NOT NULL default '1', + `borrowernumber` int(11) NOT NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY (`cardid`), + KEY `patroncards_ibfk_1` (`borrowernumber`), + CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `patronimage` +-- + +DROP TABLE IF EXISTS `patronimage`; +CREATE TABLE `patronimage` ( + `cardnumber` varchar(16) NOT NULL, + `mimetype` varchar(15) NOT NULL, + `imagefile` mediumblob NOT NULL, + PRIMARY KEY (`cardnumber`), + CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + -- -- Table structure for table `printers` -- @@ -1632,18 +1815,6 @@ DROP TABLE IF EXISTS `stopwords`; `word` varchar(255) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --- --- Table structure for table `subcategorytable` --- - -DROP TABLE IF EXISTS `subcategorytable`; -CREATE TABLE `subcategorytable` ( - `subcategorycode` varchar(5) NOT NULL default '', - `description` text, - `itemtypecodes` text, - PRIMARY KEY (`subcategorycode`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -- -- Table structure for table `subscription` -- @@ -1695,6 +1866,7 @@ CREATE TABLE `subscription` ( `branchcode` varchar(10) NOT NULL default '', `hemisphere` tinyint(3) default 0, `lastbranch` varchar(10), + `serialsadditems` tinyint(1) NOT NULL default '0', PRIMARY KEY (`subscriptionid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1782,6 +1954,58 @@ CREATE TABLE `tags` ( PRIMARY KEY (`entry`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `tags_all` +-- + +CREATE TABLE `tags_all` ( + `tag_id` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) NOT NULL, + `biblionumber` int(11) NOT NULL, + `term` varchar(255) NOT NULL, + `language` int(4) default NULL, + `date_created` datetime NOT NULL, + PRIMARY KEY (`tag_id`), + KEY `tags_borrowers_fk_1` (`borrowernumber`), + KEY `tags_biblionumber_fk_1` (`biblionumber`), + CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`) + REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`) + REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `tags_approval` +-- + +CREATE TABLE `tags_approval` ( + `term` varchar(255) NOT NULL, + `approved` int(1) NOT NULL default '0', + `date_approved` datetime default NULL, + `approved_by` int(11) default NULL, + `weight_total` int(9) NOT NULL default '1', + PRIMARY KEY (`term`), + KEY `tags_approval_borrowers_fk_1` (`approved_by`), + CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`) + REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `tags_index` +-- + +CREATE TABLE `tags_index` ( + `term` varchar(255) NOT NULL, + `biblionumber` int(11) NOT NULL, + `weight` int(9) NOT NULL default '1', + PRIMARY KEY (`term`,`biblionumber`), + KEY `tags_index_biblionumber_fk_1` (`biblionumber`), + CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`) + REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`) + REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + -- -- Table structure for table `userflags` -- @@ -1806,6 +2030,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; @@ -1818,7 +2043,7 @@ CREATE TABLE `virtualshelfcontents` ( `shelfnumber` int(11) NOT NULL default 0, `biblionumber` int(11) NOT NULL default 0, `flags` int(11) default NULL, - `dateadded` timestamp NULL default NULL, + `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY `shelfnumber` (`shelfnumber`), KEY `biblionumber` (`biblionumber`), CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE, @@ -1861,7 +2086,8 @@ CREATE TABLE `zebraqueue` ( `server` char(20) NOT NULL default '', `done` int(11) NOT NULL default '0', `time` timestamp NOT NULL default CURRENT_TIMESTAMP, - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `services_throttle`; @@ -1919,11 +2145,153 @@ CREATE TABLE language_script_mapping ( KEY `language_subtag` (`language_subtag`) ) 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 `permissions`; +CREATE TABLE `permissions` ( + `module_bit` int(11) NOT NULL DEFAULT 0, + `code` varchar(30) 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` ( + `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, + 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`) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `tmp_holdsqueue` +-- + +DROP TABLE IF EXISTS `tmp_holdsqueue`; +CREATE TABLE `tmp_holdsqueue` ( + `biblionumber` int(11) default NULL, + `itemnumber` int(11) default NULL, + `barcode` varchar(20) default NULL, + `surname` mediumtext NOT NULL, + `firstname` text, + `phone` text, + `borrowernumber` int(11) NOT NULL, + `cardnumber` varchar(16) default NULL, + `reservedate` date default NULL, + `title` mediumtext, + `itemcallnumber` varchar(30) default NULL, + `holdingbranch` varchar(10) default NULL, + `pickbranch` varchar(10) default NULL, + `notes` text, + `item_level_request` tinyint(4) NOT NULL default 0 +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `message_queue` +-- + +CREATE TABLE `message_queue` ( + `message_id` int(11) NOT NULL auto_increment, + `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`), + CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `message_transport_types` +-- + +DROP TABLE IF EXISTS `message_transport_types`; +CREATE TABLE `message_transport_types` ( + `message_transport_type` varchar(20) NOT NULL, + PRIMARY KEY (`message_transport_type`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `message_attributes` +-- + +DROP TABLE IF EXISTS `message_attributes`; +CREATE TABLE `message_attributes` ( + `message_attribute_id` int(11) NOT NULL auto_increment, + `message_name` varchar(20) NOT NULL default '', + `takes_days` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`message_attribute_id`), + UNIQUE KEY `message_name` (`message_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `message_transports` +-- + +DROP TABLE IF EXISTS `message_transports`; +CREATE TABLE `message_transports` ( + `message_attribute_id` int(11) NOT NULL, + `message_transport_type` varchar(20) NOT NULL, + `is_digest` tinyint(1) NOT NULL default '0', + `letter_module` varchar(20) NOT NULL default '', + `letter_code` varchar(20) NOT NULL default '', + PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`), + KEY `message_transport_type` (`message_transport_type`), + KEY `letter_module` (`letter_module`,`letter_code`), + CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `borrower_message_preferences` +-- + +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', + `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 `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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `borrower_message_transport_preferences` +-- + +DROP TABLE IF EXISTS `borrower_message_transport_preferences`; +CREATE TABLE `borrower_message_transport_preferences` ( + `borrower_message_preference_id` int(11) NOT NULL default '0', + `message_transport_type` varchar(20) NOT NULL default '0', + PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`), + KEY `message_transport_type` (`message_transport_type`), + CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_message_preference_id`) ON DELETE CASCADE ON UPDATE CASCADE, + 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; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;