`object` int(11) DEFAULT NULL COMMENT 'the object that the action was taken against (could be a borrowernumber, itemnumber, etc)',
`info` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'information about the action (usually includes SQL statement)',
`interface` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'the context this action was taken in',
+ `script` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'the name of the cron script that caused this change',
PRIMARY KEY (`action_id`),
KEY `timestamp_idx` (`timestamp`),
KEY `user_idx` (`user`),
`address4` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'fourth line of vendor physical address',
`phone` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'vendor phone number',
`accountnumber` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'vendor account number',
+ `type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`notes` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'order notes',
`postal` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'vendor postal address (all lines)',
`url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'vendor web address',
`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`),
`type` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`queue` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'default' COMMENT 'Name of the queue the job is sent to',
`data` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `context` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+ `context` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'JSON-serialized context information for the job',
`enqueued_on` datetime DEFAULT NULL,
`started_on` datetime DEFAULT NULL,
`ended_on` datetime DEFAULT NULL,
`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` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'MANUAL' COMMENT 'type of restriction',
+ `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',
`updated` timestamp NULL DEFAULT NULL COMMENT 'date the restriction was updated',
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_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE 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`),
/*!40101 SET character_set_client = @saved_cs_client */;
--
+-- Table structure for table `checkout_renewals`
+--
+
+DROP TABLE IF EXISTS `checkout_renewals`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `checkout_renewals` (
+ `renewal_id` int(11) NOT NULL AUTO_INCREMENT,
+ `checkout_id` int(11) DEFAULT NULL COMMENT 'the id of the checkout this renewal pertains to',
+ `renewer_id` int(11) DEFAULT NULL COMMENT 'the id of the user who processed the renewal',
+ `seen` tinyint(1) DEFAULT 0 COMMENT 'boolean denoting whether the item was present or not',
+ `interface` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'the interface this renewal took place on',
+ `timestamp` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'the date and time the renewal took place',
+ PRIMARY KEY (`renewal_id`),
+ KEY `renewer_id` (`renewer_id`),
+ CONSTRAINT `renewals_renewer_id` FOREIGN KEY (`renewer_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
-- Table structure for table `circulation_rules`
--
/*!40101 SET character_set_client = @saved_cs_client */;
--
+-- Table structure for table `item_groups`
+--
+
+DROP TABLE IF EXISTS `item_groups`;
+CREATE TABLE `item_groups` (
+ `item_group_id` INT(11) NOT NULL auto_increment COMMENT "id for the items group",
+ `biblio_id` INT(11) NOT NULL DEFAULT 0 COMMENT "id for the bibliographic record the group belongs to",
+ `display_order` INT(4) NOT NULL DEFAULT 0 COMMENT "The 'sort order' for item_groups",
+ `description` MEDIUMTEXT default NULL COMMENT "A group description",
+ `created_on` TIMESTAMP NULL COMMENT "Time and date the group was created",
+ `updated_on` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT "Time and date of the latest change on the group",
+ PRIMARY KEY (`item_group_id`),
+ CONSTRAINT `item_groups_ibfk_1` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `item_group_items`
+--
+
+DROP TABLE IF EXISTS `item_group_items`;
+CREATE TABLE `item_group_items` (
+ `item_group_items_id` int(11) NOT NULL auto_increment COMMENT "id for the group/item link",
+ `item_group_id` INT(11) NOT NULL DEFAULT 0 COMMENT "foreign key making this table a 1 to 1 join from items to item groups",
+ `item_id` INT(11) NOT NULL DEFAULT 0 COMMENT "foreign key linking this table to the items table",
+ PRIMARY KEY (`item_group_items_id`),
+ UNIQUE KEY (`item_id`),
+ CONSTRAINT `item_group_items_iifk_1` FOREIGN KEY (`item_id`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `item_group_items_gifk_1` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
-- Table structure for table `club_holds`
--
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
+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;
+
--
-- Table structure for table `columns_settings`
--
/*!40101 SET character_set_client = @saved_cs_client */;
--
+-- Table structure for table `curbside_pickup_policy`
+--
+
+DROP TABLE IF EXISTS `curbside_pickup_policy`;
+CREATE TABLE `curbside_pickup_policy` (
+ `id` int(11) NOT NULL auto_increment,
+ `branchcode` varchar(10) NOT NULL,
+ `enabled` TINYINT(1) NOT NULL DEFAULT 0,
+ `enable_waiting_holds_only` TINYINT(1) NOT NULL DEFAULT 0,
+ `pickup_interval` INT(2) NOT NULL DEFAULT 0,
+ `patrons_per_interval` INT(2) NOT NULL DEFAULT 0,
+ `patron_scheduled_pickup` TINYINT(1) NOT NULL DEFAULT 0,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY (`branchcode`),
+ FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `curbside_pickup_opening_slots`
+--
+
+DROP TABLE IF EXISTS `curbside_pickup_opening_slots`;
+CREATE TABLE `curbside_pickup_opening_slots` (
+ `id` INT(11) NOT NULL AUTO_INCREMENT,
+ `curbside_pickup_policy_id` INT(11) NOT NULL,
+ `day` TINYINT(1) NOT NULL,
+ `start_hour` INT(2) NOT NULL,
+ `start_minute` INT(2) NOT NULL,
+ `end_hour` INT(2) NOT NULL,
+ `end_minute` INT(2) NOT NULL,
+ PRIMARY KEY (`id`),
+ FOREIGN KEY (curbside_pickup_policy_id) REFERENCES curbside_pickup_policy(id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `curbside_pickups`
+--
+
+DROP TABLE IF EXISTS `curbside_pickups`;
+CREATE TABLE `curbside_pickups` (
+ `id` int(11) NOT NULL auto_increment,
+ `borrowernumber` int(11) NOT NULL,
+ `branchcode` varchar(10) NOT NULL,
+ `scheduled_pickup_datetime` datetime NOT NULL,
+ `staged_datetime` datetime NULL DEFAULT NULL,
+ `staged_by` int(11) NULL DEFAULT NULL,
+ `arrival_datetime` datetime NULL DEFAULT NULL,
+ `delivered_datetime` datetime NULL DEFAULT NULL,
+ `delivered_by` int(11) NULL DEFAULT NULL,
+ `notes` text NULL DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE ON UPDATE CASCADE,
+ FOREIGN KEY (borrowernumber) REFERENCES borrowers(borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
+ FOREIGN KEY (staged_by) REFERENCES borrowers(borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `curbside_pickup_issues`
+--
+
+DROP TABLE IF EXISTS `curbside_pickup_issues`;
+CREATE TABLE `curbside_pickup_issues` (
+ `id` int(11) NOT NULL auto_increment,
+ `curbside_pickup_id` int(11) NOT NULL,
+ `issue_id` int(11) NOT NULL,
+ `reserve_id` int(11) NOT NULL,
+ PRIMARY KEY (`id`),
+ FOREIGN KEY (curbside_pickup_id) REFERENCES curbside_pickups(id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
-- Table structure for table `currency`
--
`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',
`itemnotes_nonpublic` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'non-public notes on this item (MARC21 952$x)',
`holdingbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)',
`timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'date and time this item was last altered',
+ `deleted_on` DATETIME DEFAULT NULL COMMENT 'date/time of deletion',
`location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the shelving location for this item (MARC21 952$c)',
`permanent_location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'linked to the CART and PROC temporary locations feature, stores the permanent shelving location',
`onloan` date DEFAULT NULL COMMENT 'defines if item is checked out (NULL for not checked out, and due date for checked out)',
/*!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',
`lastreneweddate` datetime DEFAULT NULL COMMENT 'date the item was last renewed',
- `renewals` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'lists the number of times the item was renewed',
+ `renewals_count` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'lists the number of times the item was renewed',
`unseen_renewals` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'lists the number of consecutive times the item was renewed without being seen',
`auto_renew` tinyint(1) DEFAULT 0 COMMENT 'automatic renewal',
`auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'automatic renewal error',
`itemnotes_nonpublic` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'non-public notes on this item (MARC21 952$x)',
`holdingbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)',
`timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'date and time this item was last altered',
+ `deleted_on` DATETIME DEFAULT NULL COMMENT 'date/time of deletion',
`location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the shelving location for this item (MARC21 952$c)',
`permanent_location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'linked to the CART and PROC temporary locations feature, stores the permanent shelving location',
`onloan` date DEFAULT NULL COMMENT 'defines if item is checked out (NULL for not checked out, and due date for checked out)',
/*!40101 SET character_set_client = @saved_cs_client */;
--
+-- Table structure for table item_bundles
+--
+
+DROP TABLE IF EXISTS `item_bundles`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `item_bundles` (
+ `item` int(11) NOT NULL,
+ `host` int(11) NOT NULL,
+ PRIMARY KEY (`host`, `item`),
+ UNIQUE KEY `item_bundles_uniq_1` (`item`),
+ CONSTRAINT `item_bundles_ibfk_1` FOREIGN KEY (`item`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `item_bundles_ibfk_2` FOREIGN KEY (`host`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
-- Table structure for table `items_last_borrower`
--
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;
`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',
`lastreneweddate` datetime DEFAULT NULL COMMENT 'date the item was last renewed',
- `renewals` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'lists the number of times the item was renewed',
+ `renewals_count` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'lists the number of times the item was renewed',
`unseen_renewals` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'lists the number of consecutive times the item was renewed without being seen',
`auto_renew` tinyint(1) DEFAULT 0 COMMENT 'automatic renewal',
`auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'automatic renewal error',
`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',
- `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',
+ `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',
`reminderdate` 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 `hold_cancellation_requests`
+--
+
+DROP TABLE IF EXISTS `hold_cancellation_requests`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `hold_cancellation_requests` (
+ `hold_cancellation_request_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of the cancellation request',
+ `hold_id` int(11) NOT null COMMENT 'ID of the hold',
+ `creation_date` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Time and date the cancellation request was created',
+ PRIMARY KEY (`hold_cancellation_request_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
-- Table structure for table `return_claims`
--
`resolved_on` timestamp NULL DEFAULT NULL COMMENT 'Time and date the claim was resolved',
`resolved_by` int(11) DEFAULT NULL COMMENT 'ID of the staff member that resolved the claim',
PRIMARY KEY (`id`),
- UNIQUE KEY `issue_id` (`issue_id`),
+ UNIQUE KEY `item_issue` (`itemnumber`, `issue_id`),
KEY `itemnumber` (`itemnumber`),
KEY `rc_borrowers_ibfk` (`borrowernumber`),
KEY `rc_created_by_ibfk` (`created_by`),
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'the name of the field as it will be stored in the search engine',
`label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'the human readable name of the field, for display',
- `type` enum('','string','date','number','boolean','sum','isbn','stdno','year') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine',
+ `type` enum('','string','date','number','boolean','sum','isbn','stdno','year','callnumber') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine',
`weight` decimal(5,2) DEFAULT NULL,
`facet_order` tinyint(4) DEFAULT NULL COMMENT 'the order place of the field in facet list if faceted',
`staff_client` tinyint(1) NOT NULL DEFAULT 1,
/*!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`),
`branchcode` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'default branches (items.homebranch)',
`lastbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`serialsadditems` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'does receiving this serial create an item record',
- `staffdisplaycount` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'how many issues to show to the staff',
- `opacdisplaycount` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'how many issues to show to the public',
+ `staffdisplaycount` INT(11) NULL DEFAULT NULL COMMENT 'how many issues to show to the staff',
+ `opacdisplaycount` INT(11) NULL DEFAULT NULL COMMENT 'how many issues to show to the public',
`graceperiod` int(11) NOT NULL DEFAULT 0 COMMENT 'grace period in days',
`enddate` date DEFAULT NULL COMMENT 'subscription end date',
`closed` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'yes / no if the subscription is closed',
`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