`discount` float(6,4) DEFAULT NULL COMMENT 'discount offered on all items ordered from this vendor',
`fax` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'vendor fax number',
`deliverytime` int(11) DEFAULT NULL COMMENT 'vendor delivery time',
+ `external_id` VARCHAR(255) DEFAULT NULL COMMENT 'external id of the vendor',
PRIMARY KEY (`id`),
KEY `listprice` (`listprice`),
KEY `invoiceprice` (`invoiceprice`),
`suppliers_reference_number` varchar(35) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Suppliers unique edifact quote ref',
`suppliers_reference_qualifier` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Type of number above usually ''QLI''',
`suppliers_report` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'reports received from suppliers',
+ `estimated_delivery_date` date DEFAULT NULL COMMENT 'Estimated delivery date',
PRIMARY KEY (`ordernumber`),
KEY `basketno` (`basketno`),
KEY `biblionumber` (`biblionumber`),
`borrower_debarment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key for the restriction',
`borrowernumber` int(11) NOT NULL COMMENT 'foreign key for borrowers.borrowernumber for patron who is restricted',
`expiration` date DEFAULT NULL COMMENT 'expiration date of the restriction',
- `type` varchar(50) NOT NULL COMMENT 'type of restriction, FK to debarment_types.code',
+ `type` varchar(50) NOT NULL COMMENT 'type of restriction, FK to restriction_types.code',
`comment` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'comments about the restriction',
`manager_id` int(11) DEFAULT NULL COMMENT 'foreign key for borrowers.borrowernumber for the librarian managing the restriction',
`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'date the restriction was added',
PRIMARY KEY (`borrower_debarment_id`),
KEY `borrowernumber` (`borrowernumber`),
CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `borrower_debarments_ibfk_2` FOREIGN KEY (`type`) REFERENCES `debarment_types` (`code`) ON DELETE NO ACTION ON UPDATE CASCADE
+ CONSTRAINT `borrower_debarments_ibfk_2` FOREIGN KEY (`type`) REFERENCES `restriction_types` (`code`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
`title` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`othernames` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`initials` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+ `pronouns` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`streetnumber` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`streettype` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `borrower_relationships` (
`id` int(11) NOT NULL AUTO_INCREMENT,
- `guarantor_id` int(11) DEFAULT NULL,
+ `guarantor_id` int(11) NOT NULL,
`guarantee_id` int(11) NOT NULL,
`relationship` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
`title` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s title, for example: Mr. or Mrs.',
`othernames` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'any other names associated with the patron/borrower',
`initials` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'initials for your patron/borrower',
+ `pronouns` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower pronouns',
`streetnumber` tinytext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'the house number for your patron/borrower''s primary address',
`streettype` tinytext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'the street type (Rd., Blvd, etc) for your patron/borrower''s primary address',
`address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'the first address line for your patron/borrower''s primary address',
`issuing` tinyint(4) DEFAULT NULL COMMENT 'unused in Koha',
`branchip` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'the IP address for your library or branch',
`branchnotes` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'notes related to your library or branch',
- `opac_info` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'HTML that displays in OPAC',
`geolocation` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'geolocation of your library',
`marcorgcode` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'MARC Organization Code, see http://www.loc.gov/marc/organizations/orgshome.html, when empty defaults to syspref MARCOrgCode',
`pickup_location` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'the ability to act as a pickup location',
`tobranch` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'the branch the transfer was going to',
`comments` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'any comments related to the transfer',
`reason` ENUM('Manual','StockrotationAdvance','StockrotationRepatriation','ReturnToHome','ReturnToHolding','RotatingCollection','Reserve','LostReserve','CancelReserve','TransferCancellation','Recall','RecallCancellation') COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'what triggered the transfer',
- `cancellation_reason` enum('Manual','StockrotationAdvance','StockrotationRepatriation','ReturnToHome','ReturnToHolding','RotatingCollection','Reserve','LostReserve','CancelReserve','ItemLost','WrongTransfer','CancelRecall') COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'what triggered the transfer cancellation',
+ `cancellation_reason` enum('Manual','StockrotationAdvance','StockrotationRepatriation','ReturnToHome','ReturnToHolding','RotatingCollection','Reserve','LostReserve','CancelReserve','ItemLost','WrongTransfer','RecallCancellation') COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'what triggered the transfer cancellation',
PRIMARY KEY (`branchtransfer_id`),
KEY `frombranch` (`frombranch`),
KEY `tobranch` (`tobranch`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
-DROP TABLE IF EXISTS `debarment_types`;
-CREATE TABLE debarment_types (
- code varchar(50) NOT NULL PRIMARY KEY,
- display_text text NOT NULL,
- ronly tinyint(1) NOT NULL DEFAULT 0,
- dflt tinyint(1) NOT NULL DEFAULT 0
+DROP TABLE IF EXISTS `restriction_types`;
+CREATE TABLE `restriction_types` (
+ `code` varchar(50) NOT NULL,
+ `display_text` text NOT NULL,
+ `is_system` tinyint(1) NOT NULL DEFAULT 0,
+ `is_default` tinyint(1) NOT NULL DEFAULT 0,
+ PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
`title` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s title, for example: Mr. or Mrs.',
`othernames` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'any other names associated with the patron/borrower',
`initials` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'initials for your patron/borrower',
+ `pronouns` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower pronouns',
`streetnumber` tinytext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'the house number for your patron/borrower''s primary address',
`streettype` tinytext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'the street type (Rd., Blvd, etc) for your patron/borrower''s primary address',
`address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'the first address line for your patron/borrower''s primary address',
/*!40101 SET character_set_client = @saved_cs_client */;
--
+-- Table structure for table `erm_agreements`
+--
+
+DROP TABLE IF EXISTS `erm_agreements`;
+CREATE TABLE `erm_agreements` (
+ `agreement_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
+ `vendor_id` INT(11) DEFAULT NULL COMMENT 'foreign key to aqbooksellers',
+ `name` VARCHAR(255) NOT NULL COMMENT 'name of the agreement',
+ `description` LONGTEXT DEFAULT NULL COMMENT 'description of the agreement',
+ `status` VARCHAR(80) NOT NULL COMMENT 'current status of the agreement',
+ `closure_reason` VARCHAR(80) DEFAULT NULL COMMENT 'reason of the closure',
+ `is_perpetual` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'is the agreement perpetual',
+ `renewal_priority` VARCHAR(80) DEFAULT NULL COMMENT 'priority of the renewal',
+ `license_info` VARCHAR(80) DEFAULT NULL COMMENT 'info about the license',
+ CONSTRAINT `erm_agreements_ibfk_1` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
+ PRIMARY KEY(`agreement_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `erm_agreement_periods`
+--
+
+DROP TABLE IF EXISTS `erm_agreement_periods`;
+CREATE TABLE `erm_agreement_periods` (
+ `agreement_period_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
+ `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
+ `started_on` DATE NOT NULL COMMENT 'start of the agreement period',
+ `ended_on` DATE COMMENT 'end of the agreement period',
+ `cancellation_deadline` DATE DEFAULT NULL COMMENT 'Deadline for the cancellation',
+ `notes` mediumtext DEFAULT NULL COMMENT 'notes about this period',
+ CONSTRAINT `erm_agreement_periods_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ PRIMARY KEY(`agreement_period_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `erm_agreement_user_roles`
+--
+
+DROP TABLE IF EXISTS `erm_agreement_user_roles`;
+CREATE TABLE `erm_agreement_user_roles` (
+ `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
+ `user_id` INT(11) NOT NULL COMMENT 'link to the user',
+ `role` VARCHAR(80) NOT NULL COMMENT 'role of the user',
+ CONSTRAINT `erm_agreement_users_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `erm_agreement_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `erm_licenses`
+--
+
+DROP TABLE IF EXISTS `erm_licenses`;
+CREATE TABLE `erm_licenses` (
+ `license_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
+ `name` VARCHAR(255) NOT NULL COMMENT 'name of the license',
+ `description` LONGTEXT DEFAULT NULL COMMENT 'description of the license',
+ `type` VARCHAR(80) NOT NULL COMMENT 'type of the license',
+ `status` VARCHAR(80) NOT NULL COMMENT 'current status of the license',
+ `started_on` DATE COMMENT 'start of the license',
+ `ended_on` DATE COMMENT 'end of the license',
+ PRIMARY KEY(`license_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `erm_agreement_licenses`
+--
+
+DROP TABLE IF EXISTS `erm_agreement_licenses`;
+CREATE TABLE `erm_agreement_licenses` (
+ `agreement_license_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
+ `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
+ `license_id` INT(11) NOT NULL COMMENT 'link to the license',
+ `status` VARCHAR(80) NOT NULL COMMENT 'current status of the license',
+ `physical_location` VARCHAR(80) DEFAULT NULL COMMENT 'physical location of the license',
+ `notes` mediumtext DEFAULT NULL COMMENT 'notes about this license',
+ `uri` varchar(255) DEFAULT NULL COMMENT 'URI of the license',
+ CONSTRAINT `erm_licenses_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `erm_licenses_ibfk_2` FOREIGN KEY (`license_id`) REFERENCES `erm_licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ PRIMARY KEY(`agreement_license_id`),
+ UNIQUE KEY `erm_agreement_licenses_uniq` (`agreement_id`, `license_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `erm_agreement_relationships`
+--
+
+DROP TABLE IF EXISTS `erm_agreement_relationships`;
+CREATE TABLE `erm_agreement_relationships` (
+ `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
+ `related_agreement_id` INT(11) NOT NULL COMMENT 'link to the related agreement',
+ `relationship` ENUM('supersedes', 'is-superseded-by', 'provides_post-cancellation_access_for', 'has-post-cancellation-access-in', 'tracks_demand-driven_acquisitions_for', 'has-demand-driven-acquisitions-in', 'has_backfile_in', 'has_frontfile_in', 'related_to') NOT NULL COMMENT 'relationship between the two agreements',
+ `notes` mediumtext DEFAULT NULL COMMENT 'notes about this relationship',
+ CONSTRAINT `erm_agreement_relationships_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `erm_agreement_relationships_ibfk_2` FOREIGN KEY (`related_agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ PRIMARY KEY(`agreement_id`, `related_agreement_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `erm_agreement_documents`
+--
+
+DROP TABLE IF EXISTS `erm_agreement_documents`;
+CREATE TABLE `erm_agreement_documents` (
+ `document_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
+ `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
+ `file_name` varchar(255) DEFAULT NULL COMMENT 'name of the file',
+ `file_type` varchar(255) DEFAULT NULL COMMENT 'type of the file',
+ `file_description` varchar(255) DEFAULT NULL COMMENT 'description of the file',
+ `file_content` longblob DEFAULT NULL COMMENT 'the content of the file',
+ `uploaded_on` datetime DEFAULT NULL COMMENT 'datetime when the file as attached',
+ `physical_location` VARCHAR(255) DEFAULT NULL COMMENT 'physical location of the document',
+ `uri` varchar(255) DEFAULT NULL COMMENT 'URI of the document',
+ `notes` mediumtext DEFAULT NULL COMMENT 'notes about this relationship',
+ CONSTRAINT `erm_agreement_documents_ibfk_1` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ PRIMARY KEY(`document_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `erm_eholdings_packages`
+--
+
+DROP TABLE IF EXISTS `erm_eholdings_packages`;
+CREATE TABLE `erm_eholdings_packages` (
+ `package_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
+ `vendor_id` INT(11) DEFAULT NULL COMMENT 'foreign key to aqbooksellers',
+ `name` VARCHAR(255) NOT NULL COMMENT 'name of the package',
+ `external_id` VARCHAR(255) DEFAULT NULL COMMENT 'External key',
+ `provider` ENUM('ebsco') DEFAULT NULL COMMENT 'External provider',
+ `package_type` VARCHAR(80) DEFAULT NULL COMMENT 'type of the package',
+ `content_type` VARCHAR(80) DEFAULT NULL COMMENT 'type of the package',
+ `created_on` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date of creation of the package',
+ CONSTRAINT `erm_eholdings_packages_ibfk_1` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
+ PRIMARY KEY(`package_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `erm_eholdings_packages_agreements`
+--
+
+DROP TABLE IF EXISTS `erm_eholdings_packages_agreements`;
+CREATE TABLE `erm_eholdings_packages_agreements` (
+ `package_id` INT(11) NOT NULL COMMENT 'link to the package',
+ `agreement_id` INT(11) NOT NULL COMMENT 'link to the agreement',
+ UNIQUE KEY `erm_eholdings_packages_agreements_uniq` (`package_id`, `agreement_id`),
+ CONSTRAINT `erm_eholdings_packages_agreements_ibfk_1` FOREIGN KEY (`package_id`) REFERENCES `erm_eholdings_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `erm_eholdings_packages_agreements_ibfk_2` FOREIGN KEY (`agreement_id`) REFERENCES `erm_agreements` (`agreement_id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `erm_eholdings_titles`
+--
+
+DROP TABLE IF EXISTS `erm_eholdings_titles`;
+CREATE TABLE `erm_eholdings_titles` (
+ `title_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
+ `biblio_id` INT(11) DEFAULT NULL,
+ `publication_title` VARCHAR(255) DEFAULT NULL,
+ `external_id` VARCHAR(255) DEFAULT NULL,
+ `print_identifier` VARCHAR(255) DEFAULT NULL,
+ `online_identifier` VARCHAR(255) DEFAULT NULL,
+ `date_first_issue_online` VARCHAR(255) DEFAULT NULL,
+ `num_first_vol_online` VARCHAR(255) DEFAULT NULL,
+ `num_first_issue_online` VARCHAR(255) DEFAULT NULL,
+ `date_last_issue_online` VARCHAR(255) DEFAULT NULL,
+ `num_last_vol_online` VARCHAR(255) DEFAULT NULL,
+ `num_last_issue_online` VARCHAR(255) DEFAULT NULL,
+ `title_url` VARCHAR(255) DEFAULT NULL,
+ `first_author` VARCHAR(255) DEFAULT NULL,
+ `embargo_info` VARCHAR(255) DEFAULT NULL,
+ `coverage_depth` VARCHAR(255) DEFAULT NULL,
+ `notes` VARCHAR(255) DEFAULT NULL,
+ `publisher_name` VARCHAR(255) DEFAULT NULL,
+ `publication_type` VARCHAR(80) DEFAULT NULL,
+ `date_monograph_published_print` VARCHAR(255) DEFAULT NULL,
+ `date_monograph_published_online` VARCHAR(255) DEFAULT NULL,
+ `monograph_volume` VARCHAR(255) DEFAULT NULL,
+ `monograph_edition` VARCHAR(255) DEFAULT NULL,
+ `first_editor` VARCHAR(255) DEFAULT NULL,
+ `parent_publication_title_id` VARCHAR(255) DEFAULT NULL,
+ `preceeding_publication_title_id` VARCHAR(255) DEFAULT NULL,
+ `access_type` VARCHAR(255) DEFAULT NULL,
+ CONSTRAINT `erm_eholdings_titles_ibfk_2` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ PRIMARY KEY(`title_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `erm_eholdings_resources`
+--
+
+DROP TABLE IF EXISTS `erm_eholdings_resources`;
+CREATE TABLE `erm_eholdings_resources` (
+ `resource_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
+ `title_id` INT(11) NOT NULL,
+ `package_id` INT(11) NOT NULL,
+ `vendor_id` INT(11) DEFAULT NULL,
+ `started_on` DATE,
+ `ended_on` DATE,
+ `proxy` VARCHAR(80) DEFAULT NULL,
+ UNIQUE KEY `erm_eholdings_resources_uniq` (`title_id`, `package_id`),
+ CONSTRAINT `erm_eholdings_resources_ibfk_1` FOREIGN KEY (`title_id`) REFERENCES `erm_eholdings_titles` (`title_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `erm_eholdings_resources_ibfk_2` FOREIGN KEY (`package_id`) REFERENCES `erm_eholdings_packages` (`package_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `erm_eholdings_resources_ibfk_3` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
+ PRIMARY KEY(`resource_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
-- Table structure for table `export_format`
--
`isbn` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`issn` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`has_items` tinyint(1) NOT NULL DEFAULT 0,
+ PRIMARY KEY (`import_record_id`),
KEY `import_biblios_ibfk_1` (`import_record_id`),
KEY `matched_biblionumber` (`matched_biblionumber`),
KEY `title` (`title`(191)),
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `issues` (
`issue_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key for issues table',
- `borrowernumber` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the borrowers table for the patron this item was checked out to',
+ `borrowernumber` int(11) NOT NULL COMMENT 'foreign key, linking this to the borrowers table for the patron this item was checked out to',
`issuer_id` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the borrowers table for the user who checked out this item',
- `itemnumber` int(11) DEFAULT NULL COMMENT 'foreign key, linking this to the items table for the item that was checked out',
+ `itemnumber` int(11) NOT NULL COMMENT 'foreign key, linking this to the items table for the item that was checked out',
`date_due` datetime DEFAULT NULL COMMENT 'datetime the item is due (yyyy-mm-dd hh:mm::ss)',
`branchcode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key, linking to the branches table for the location the item was checked out',
`returndate` datetime DEFAULT NULL COMMENT 'date the item was returned, will be NULL until moved to old_issues',
PRIMARY KEY (`itemtype`),
UNIQUE KEY `itemtype` (`itemtype`),
KEY `itemtypes_ibfk_1` (`parent_type`),
- CONSTRAINT `itemtypes_ibfk_1` FOREIGN KEY (`parent_type`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
+ CONSTRAINT `itemtypes_ibfk_1` FOREIGN KEY (`parent_type`) REFERENCES `itemtypes` (`itemtype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `message_queue` (
`message_id` int(11) NOT NULL AUTO_INCREMENT,
+ `letter_id` int(11) DEFAULT NULL COMMENT 'Foreign key to the letters table',
`borrowernumber` int(11) DEFAULT NULL,
`subject` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`content` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`message_id`),
KEY `borrowernumber` (`borrowernumber`),
KEY `message_transport_type` (`message_transport_type`),
+ CONSTRAINT `letter_fk` FOREIGN KEY (`letter_id`) REFERENCES `letter` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
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 UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
`borrowernumber` int(11) DEFAULT NULL COMMENT 'foreign key from the borrowers table defining which patron this hold is for',
`reservedate` date DEFAULT NULL COMMENT 'the date the hold was places',
`biblionumber` int(11) DEFAULT NULL COMMENT 'foreign key from the biblio table defining which bib record this hold is on',
+ `item_group_id` int(11) NULL default NULL COMMENT 'foreign key from the item_groups table defining if this is an item group level hold',
`branchcode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table defining which branch the patron wishes to pick this hold up at',
`desk_id` int(11) DEFAULT NULL COMMENT 'foreign key from the desks table defining which desk the patron should pick this hold up at',
`notificationdate` date DEFAULT NULL COMMENT 'currently unused',
KEY `old_reserves_itemnumber` (`itemnumber`),
KEY `old_reserves_branchcode` (`branchcode`),
KEY `old_reserves_itemtype` (`itemtype`),
+ CONSTRAINT `old_reserves_ibfk_ig` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE SET NULL ON UPDATE SET NULL,
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,
`borrowernumber` int(11) NOT NULL DEFAULT 0 COMMENT 'foreign key from the borrowers table defining which patron this hold is for',
`reservedate` date DEFAULT NULL COMMENT 'the date the hold was placed',
`biblionumber` int(11) NOT NULL DEFAULT 0 COMMENT 'foreign key from the biblio table defining which bib record this hold is on',
+ `item_group_id` int(11) NULL default NULL COMMENT 'foreign key from the item_groups table defining if this is an item group level hold',
`branchcode` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'foreign key from the branches table defining which branch the patron wishes to pick this hold up at',
`desk_id` int(11) DEFAULT NULL COMMENT 'foreign key from the desks table defining which desk the patron should pick this hold up at',
`notificationdate` date DEFAULT NULL COMMENT 'currently unused',
KEY `branchcode` (`branchcode`),
KEY `desk_id` (`desk_id`),
KEY `itemtype` (`itemtype`),
+ CONSTRAINT `reserves_ibfk_ig` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
/*!40101 SET character_set_client = @saved_cs_client */;
--
+-- Table structure for table `search_filters`
+--
+
+DROP TABLE IF EXISTS `search_filters`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `search_filters` (
+ `search_filter_id` int(11) NOT NULL AUTO_INCREMENT,
+ `name` varchar(255) NOT NULL COMMENT 'filter name',
+ `query` mediumtext NULL DEFAULT NULL COMMENT 'filter query part',
+ `limits` mediumtext NULL DEFAULT NULL COMMENT 'filter limits part',
+ `opac` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'whether this filter is shown on OPAC',
+ `staff_client` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'whether this filter is shown in staff client',
+ PRIMARY KEY (`search_filter_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
-- Table structure for table `search_history`
--
`location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the shelving location for this item (MARC21 952$c)',
`borrowernumber` int(11) DEFAULT NULL COMMENT 'foreign key from the borrowers table, links transaction to a specific borrower',
`ccode` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the items table, links transaction to a specific collection code',
+ `categorycode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the borrowers table, links transaction to a specific borrower category',
KEY `timeidx` (`datetime`),
KEY `branch_idx` (`branch`),
KEY `type_idx` (`type`),
`STATUS` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'suggestion status (ASKED, CHECKED, ACCEPTED, REJECTED, ORDERED, AVAILABLE or a value from the SUGGEST_STATUS authorised value category)',
`archived` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'is the suggestion archived?',
`note` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'note entered on the suggestion',
+ `staff_note` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'non-public note entered on the suggestion',
`author` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'author of the suggested item',
`title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'title of the suggested item',
`copyrightdate` smallint(6) DEFAULT NULL COMMENT 'copyright date of the suggested item',
`pickbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`notes` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`item_level_request` tinyint(4) NOT NULL DEFAULT 0,
+ `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'date and time this entry as added/last updated',
KEY `tmp_holdsqueue_ibfk_1` (`itemnumber`),
KEY `tmp_holdsqueue_ibfk_2` (`biblionumber`),
KEY `tmp_holdsqueue_ibfk_3` (`borrowernumber`),
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `z3950servers` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique identifier assigned by Koha',
- `host` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'target''s host name',
+ `host` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'target''s host name',
`port` int(11) DEFAULT NULL COMMENT 'port number used to connect to target',
`db` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'target''s database name',
`userid` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'username needed to log in to target',
`servername` longtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'name given to the target by the library',
`checked` smallint(6) DEFAULT NULL COMMENT 'whether this target is checked by default (1 for yes, 0 for no)',
`rank` int(11) DEFAULT NULL COMMENT 'where this target appears in the list of targets',
- `syntax` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'marc format provided by this target',
+ `syntax` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'MARC format provided by this target',
`timeout` int(11) NOT NULL DEFAULT 0 COMMENT 'number of seconds before Koha stops trying to access this server',
`servertype` enum('zed','sru') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'zed' COMMENT 'zed means z39.50 server',
- `encoding` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'characters encoding provided by this target',
+ `encoding` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'characters encoding provided by this target',
`recordtype` enum('authority','biblio') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'biblio' COMMENT 'server contains bibliographic or authority records',
`sru_options` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'options like sru=get, sru_version=1.1; will be passed to the server via ZOOM',
`sru_fields` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'contains the mapping between the Z3950 search fields and the specific SRU server indexes',
projects / koha-ffzg.git / blobdiff