--- MySQL dump 10.19 Distrib 10.3.31-MariaDB, for debian-linux-gnu (x86_64)
+-- MariaDB dump 10.19 Distrib 10.5.15-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: db Database: koha_kohadev
-- ------------------------------------------------------
--- Server version 10.6.5-MariaDB-1:10.6.5+maria~focal
+-- Server version 10.8.3-MariaDB-1:10.8.3+maria~jammy
+
+-- IMPORTANT NOTE:
+-- The lines with /*! are silently IGNORED when the web installer runs this
+-- file (in C4::Installer, using DBIx::RunSQL).
+-- The lines only work when you run this sql script directly with mysql.
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
`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`),
CREATE TABLE `additional_contents` (
`idnew` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content',
`category` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'category for the additional content',
- `code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'code to group content per lang',
+ `code` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'code to group content per lang',
`location` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'location of the additional content',
`branchcode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'branch code users to create branch specific additional content, NULL is every branch.',
`title` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'title of the additional content',
`content` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'the body of your additional content',
- `lang` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'location for the additional content(koha is the staff interface, slip is the circulation receipt and language codes are for the opac)',
+ `lang` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'location for the additional content(koha is the staff interface, slip is the circulation receipt and language codes are for the opac)',
`published_on` date DEFAULT NULL COMMENT 'publication date',
`updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'last modification',
`expirationdate` date DEFAULT NULL COMMENT 'date the additional content is set to expire or no longer be visible',
`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`),
`format` enum('PHOTOCOPY','SCAN') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PHOTOCOPY',
`urls` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`cancellation_reason` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'optional authorised value AR_CANCELLATION',
- `debit_id` int(11) NULL DEFAULT NULL COMMENT 'Debit line with cost for article scan request',
+ `debit_id` int(11) DEFAULT NULL COMMENT 'Debit line with cost for article scan request',
`created_on` timestamp NULL DEFAULT NULL COMMENT 'Be careful with two timestamps in one table not allowing NULL',
`updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`toc_request` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'borrower requested table of contents',
KEY `biblionumber` (`biblionumber`),
KEY `itemnumber` (`itemnumber`),
KEY `branchcode` (`branchcode`),
+ KEY `debit_id` (`debit_id`),
CONSTRAINT `article_requests_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `article_requests_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `article_requests_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
`size` int(11) DEFAULT NULL,
`borrowernumber` int(11) DEFAULT NULL,
`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 COMMENT 'JSON-serialized context information for the job',
`enqueued_on` datetime DEFAULT NULL,
`started_on` datetime DEFAULT NULL,
`ended_on` datetime DEFAULT NULL,
`keep_for_pseudonymization` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'defines if this field is copied to anonymized_borrower_attributes (1 for yes, 0 for no)',
`mandatory` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'defines if the attribute is mandatory or not',
PRIMARY KEY (`code`),
- KEY `auth_val_cat_idx` (`authorised_value_category`)
+ KEY `auth_val_cat_idx` (`authorised_value_category`),
+ KEY `category_code` (`category_code`),
+ CONSTRAINT `borrower_attribute_types_ibfk_1` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
`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 */;
`cardnumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`surname` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`firstname` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+ `middle_name` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s middle name',
`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`),
`cardnumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'unique key, library assigned ID number for patrons/borrowers',
`surname` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s last name (surname)',
`firstname` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s first name',
+ `middle_name` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s middle name',
`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',
`categorycode` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'foreign key from the categories table, includes the code of the patron category',
`dateenrolled` date DEFAULT NULL COMMENT 'date the patron was added to Koha (YYYY-MM-DD)',
`dateexpiry` date DEFAULT NULL COMMENT 'date the patron/borrower''s card is set to expire (YYYY-MM-DD)',
+ `password_expiration_date` date DEFAULT NULL COMMENT 'date the patron/borrower''s password is set to expire (YYYY-MM-DD)',
`date_renewed` date DEFAULT NULL COMMENT 'date the patron/borrower''s card was last renewed',
`gonenoaddress` tinyint(1) DEFAULT NULL COMMENT 'set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having an unconfirmed address',
`lost` tinyint(1) DEFAULT NULL COMMENT 'set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having lost their card',
`relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor',
`sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s gender',
`password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s Bcrypt encrypted password',
+ `secret` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Secret for 2FA',
+ `auth_method` enum('password','two-factor') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'password' COMMENT 'Authentication method',
`flags` int(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions',
`userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in',
`opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note on the patron/borrower''s account that is visible in the OPAC and staff interface',
`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',
`datecancelled` datetime DEFAULT NULL COMMENT 'the date the transfer was cancelled',
`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') 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') COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'what triggered the transfer cancellation',
+ `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','RecallCancellation') COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'what triggered the transfer cancellation',
PRIMARY KEY (`branchtransfer_id`),
KEY `frombranch` (`frombranch`),
KEY `tobranch` (`tobranch`),
`description` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'description of the patron category',
`enrolmentperiod` smallint(6) DEFAULT NULL COMMENT 'number of months the patron is enrolled for (will be NULL if enrolmentperioddate is set)',
`enrolmentperioddate` date DEFAULT NULL COMMENT 'date the patron is enrolled until (will be NULL if enrolmentperiod is set)',
+ `password_expiry_days` smallint(6) DEFAULT NULL COMMENT 'number of days after which the patron must reset their password',
`upperagelimit` smallint(6) DEFAULT NULL COMMENT 'age limit for the patron',
`dateofbirthrequired` tinyint(1) DEFAULT NULL COMMENT 'the minimum age required for the patron category',
`finetype` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'unused in Koha',
`BlockExpiredPatronOpacActions` tinyint(1) NOT NULL DEFAULT -1 COMMENT 'wheither or not a patron of this category can renew books or place holds once their card has expired. 0 means they can, 1 means they cannot, -1 means use syspref BlockExpiredPatronOpacActions',
`default_privacy` enum('default','never','forever') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'default' COMMENT 'Default privacy setting for this patron category',
`checkprevcheckout` varchar(7) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'inherit' COMMENT 'produce a warning for this patron category if this item has previously been checked out to this patron if ''yes'', not if ''no'', defer to syspref setting if ''inherit''.',
+ `can_be_guarantee` tinyint(1) NOT NULL default 0 COMMENT 'if patrons of this category can be guarantees',
`reset_password` tinyint(1) DEFAULT NULL COMMENT 'if patrons of this category can do the password reset flow,',
`change_password` tinyint(1) DEFAULT NULL COMMENT 'if patrons of this category can change their passwords in the OAPC',
`min_password_length` smallint(6) DEFAULT NULL COMMENT 'set minimum password length for patrons in this category',
/*!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`
--
`cardnumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'unique key, library assigned ID number for patrons/borrowers',
`surname` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s last name (surname)',
`firstname` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s first name',
+ `middle_name` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s middle name',
`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',
`categorycode` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'foreign key from the categories table, includes the code of the patron category',
`dateenrolled` date DEFAULT NULL COMMENT 'date the patron was added to Koha (YYYY-MM-DD)',
`dateexpiry` date DEFAULT NULL COMMENT 'date the patron/borrower''s card is set to expire (YYYY-MM-DD)',
+ `password_expiration_date` date DEFAULT NULL COMMENT 'date the patron/borrower''s password is set to expire (YYYY-MM-DD)',
`date_renewed` date DEFAULT NULL COMMENT 'date the patron/borrower''s card was last renewed',
`gonenoaddress` tinyint(1) DEFAULT NULL COMMENT 'set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having an unconfirmed address',
`lost` tinyint(1) DEFAULT NULL COMMENT 'set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having lost their card',
`relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor',
`sex` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s gender',
`password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s encrypted password',
+ `secret` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Secret for 2FA',
+ `auth_method` enum('password','two-factor') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'password' COMMENT 'Authentication method',
`flags` int(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions',
`userid` varchar(75) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in',
`opacnote` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'a note on the patron/borrower''s account that is visible in the OPAC and staff interface',
`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`)
+) 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_package_id` VARCHAR(255) DEFAULT NULL COMMENT 'External key',
+ `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',
+ 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',
+ `vendor_id` INT(11) DEFAULT NULL,
+ `publication_title` 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(255) 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_1` FOREIGN KEY (`vendor_id`) REFERENCES `aqbooksellers` (`id`) 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,
+ `started_on` DATE,
+ `ended_on` DATE,
+ `proxy` VARCHAR(80) DEFAULT NULL,
+ 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,
+ PRIMARY KEY(`resource_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
-- Table structure for table `export_format`
--
`illrequest_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ILL request number',
`borrowernumber` int(11) DEFAULT NULL COMMENT 'Patron associated with request',
`biblio_id` int(11) DEFAULT NULL COMMENT 'Potential bib linked to request',
+ `due_date` datetime DEFAULT NULL COMMENT 'Custom date due specified by backend, leave NULL for default date_due calculation',
`branchcode` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The branch associated with the request',
`status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Current Koha status of request',
`status_alias` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Foreign key to relevant authorised_values.authorised_value',
`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)),
`import_record_id` int(11) NOT NULL COMMENT 'the id given to the imported bib record (import_records.import_record_id)',
`candidate_match_id` int(11) NOT NULL COMMENT 'the biblio the imported record matches (biblio.biblionumber)',
`score` int(11) NOT NULL DEFAULT 0 COMMENT 'the match score',
+ `chosen` tinyint(1) DEFAULT NULL COMMENT 'whether this match has been allowed or denied',
PRIMARY KEY (`import_record_id`,`candidate_match_id`),
KEY `record_score` (`import_record_id`,`score`),
CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`) REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE
/*!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 */;
`description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
- KEY `lang` (`lang`),
- KEY `subtag_type_lang` (`subtag`,`type`,`lang`)
+ UNIQUE KEY `uniq_desc` (`subtag`,`type`,`lang`),
+ KEY `lang` (`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
`iso639_2_code` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
+ UNIQUE KEY `uniq_code` (`rfc4646_subtag`,`iso639_2_code`),
KEY `rfc4646_subtag` (`rfc4646_subtag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `language_script_mapping` (
- `language_subtag` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+ `language_subtag` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
`script_subtag` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- KEY `language_subtag` (`language_subtag`)
+ PRIMARY KEY (`language_subtag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
CREATE TABLE `language_subtag_registry` (
`subtag` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`type` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'language-script-region-variant-extension-privateuse',
- `description` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'only one of the possible descriptions for ease of reference, see language_descriptions for the complete list',
+ `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'only one of the possible descriptions for ease of reference, see language_descriptions for the complete list',
`added` date DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
+ UNIQUE KEY `uniq_lang` (`subtag`,`type`),
KEY `subtag` (`subtag`)
) 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,
/*!40101 SET character_set_client = @saved_cs_client */;
--
+-- Table structure for table `recalls`
+--
+
+DROP TABLE IF EXISTS `recalls`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `recalls` (
+ `recall_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier for this recall',
+ `patron_id` int(11) NOT NULL DEFAULT 0 COMMENT 'Identifier for patron who requested recall',
+ `created_date` datetime DEFAULT NULL COMMENT 'Date the recall was requested',
+ `biblio_id` int(11) NOT NULL DEFAULT 0 COMMENT 'Identifier for bibliographic record that has been recalled',
+ `pickup_library_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Identifier for recall pickup library',
+ `completed_date` datetime DEFAULT NULL COMMENT 'Date the recall is completed (fulfilled, cancelled or expired)',
+ `notes` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Notes related to the recall',
+ `priority` smallint(6) DEFAULT NULL COMMENT 'Where in the queue the patron sits',
+ `status` enum('requested','overdue','waiting','in_transit','cancelled','expired','fulfilled') COLLATE utf8mb4_unicode_ci DEFAULT 'requested' COMMENT 'Status of recall',
+ `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Date and time the recall was last updated',
+ `item_id` int(11) DEFAULT NULL COMMENT 'Identifier for item record that was recalled, if an item-level recall',
+ `waiting_date` datetime DEFAULT NULL COMMENT 'Date an item was marked as waiting for the patron at the library',
+ `expiration_date` datetime DEFAULT NULL COMMENT 'Date recall is no longer required, or date recall will expire after waiting on shelf for pickup',
+ `completed` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Flag if recall is old and no longer active, i.e. expired, cancelled or completed',
+ `item_level` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Flag if recall is for a specific item',
+ PRIMARY KEY (`recall_id`),
+ KEY `recalls_ibfk_1` (`patron_id`),
+ KEY `recalls_ibfk_2` (`biblio_id`),
+ KEY `recalls_ibfk_3` (`item_id`),
+ KEY `recalls_ibfk_4` (`pickup_library_id`),
+ CONSTRAINT `recalls_ibfk_1` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `recalls_ibfk_2` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `recalls_ibfk_3` FOREIGN KEY (`item_id`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `recalls_ibfk_4` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Information related to recalls in Koha';
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
-- Table structure for table `repeatable_holidays`
--
`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') 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`
--
`search_field_id` int(11) NOT NULL,
`facet` tinyint(1) DEFAULT 0 COMMENT 'true if a facet field should be generated for this',
`suggestible` tinyint(1) DEFAULT 0 COMMENT 'true if this field can be used to generate suggestions for browse',
- `sort` tinyint(1) DEFAULT NULL COMMENT 'true/false creates special sort handling, null doesn''t',
+ `sort` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Sort defaults to 1 (Yes) and creates sort fields in the index, 0 (no) will prevent this',
PRIMARY KEY (`search_marc_map_id`,`search_field_id`),
KEY `search_field_id` (`search_field_id`),
CONSTRAINT `search_marc_to_field_ibfk_1` FOREIGN KEY (`search_marc_map_id`) REFERENCES `search_marc_map` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
`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`),
`description` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Description for this rota',
`cyclical` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Should items on this rota keep cycling?',
`active` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is this rota currently active?',
- PRIMARY KEY (`rota_id`)
+ PRIMARY KEY (`rota_id`),
+ UNIQUE KEY `stockrotationrotas_title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
`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',
`itemtype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`previousitemtype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`mana_id` int(11) DEFAULT NULL,
+ `ccode` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'collection code to assign to serial items',
PRIMARY KEY (`subscriptionid`),
KEY `subscription_ibfk_1` (`periodicity`),
KEY `subscription_ibfk_2` (`numberpattern`),
`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',
`module` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`page` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`tablename` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
- `default_display_length` smallint(6) NOT NULL DEFAULT 20,
+ `default_display_length` smallint(6) DEFAULT NULL,
`default_sort_order` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`module`(191),`page`(191),`tablename`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
`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`),
CREATE TABLE `user_permissions` (
`borrowernumber` int(11) NOT NULL DEFAULT 0,
`module_bit` int(11) NOT NULL DEFAULT 0,
- `code` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+ `code` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
+ PRIMARY KEY (`borrowernumber`,`module_bit`,`code`),
KEY `user_permissions_ibfk_1` (`borrowernumber`),
KEY `user_permissions_ibfk_2` (`module_bit`,`code`),
CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
`download_directory` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`upload_directory` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`san` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+ `standard` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT 'EUR',
`id_code_qualifier` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT '14',
`transport` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT 'FTP',
`quotes_enabled` tinyint(1) NOT NULL DEFAULT 0,
`created_on` datetime NOT NULL COMMENT 'creation time',
`allow_change_from_owner` tinyint(1) DEFAULT 1 COMMENT 'can owner change contents?',
`allow_change_from_others` tinyint(1) DEFAULT 0 COMMENT 'can others change contents?',
+ `allow_change_from_staff` tinyint(1) DEFAULT 0 COMMENT 'can staff change contents?',
PRIMARY KEY (`shelfnumber`),
KEY `virtualshelves_ibfk_1` (`owner`),
CONSTRAINT `virtualshelves_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL
/*!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',
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
--- Dump completed on 2021-11-24 9:48:17
+-- Dump completed on 2022-05-26 2:46:01
projects / srvgit / blobdiff