+-- http://www.w3.org/International/articles/language-tags/
+
+-- RFC4646
+DROP TABLE IF EXISTS language_subtag_registry;
+CREATE TABLE language_subtag_registry (
+ subtag varchar(25),
+ type varchar(25), -- language-script-region-variant-extension-privateuse
+ description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
+ added date,
+ KEY `subtag` (`subtag`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+-- TODO: add suppress_scripts
+-- this maps three letter codes defined in iso639.2 back to their
+-- two letter equivilents in rfc4646 (LOC maintains iso639+)
+DROP TABLE IF EXISTS language_rfc4646_to_iso639;
+CREATE TABLE language_rfc4646_to_iso639 (
+ rfc4646_subtag varchar(25),
+ iso639_2_code varchar(25),
+ KEY `rfc4646_subtag` (`rfc4646_subtag`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+DROP TABLE IF EXISTS language_descriptions;
+CREATE TABLE language_descriptions (
+ subtag varchar(25),
+ type varchar(25),
+ lang varchar(25),
+ description varchar(255),
+ KEY `lang` (`lang`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+-- bi-directional support, keyed by script subcode
+DROP TABLE IF EXISTS language_script_bidi;
+CREATE TABLE language_script_bidi (
+ rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
+ bidi varchar(3), -- rtl ltr
+ KEY `rfc4646_subtag` (`rfc4646_subtag`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+-- TODO: need to map language subtags to script subtags for detection
+-- of bidi when script is not specified (like ar, he)
+DROP TABLE IF EXISTS language_script_mapping;
+CREATE TABLE language_script_mapping (
+ language_subtag varchar(25),
+ script_subtag varchar(25),
+ KEY `language_subtag` (`language_subtag`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+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;
+