--- 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`),
`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 */;
`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,
`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',
`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',
`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','Recall','CancelRecall') 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','Recall','CancelRecall') 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','CancelRecall') 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`
--
/*!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',
`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)',
`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',
`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
`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`
--
`description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
- KEY `lang` (`lang`),
- UNIQUE KEY `uniq_desc` (`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`),
- KEY `rfc4646_subtag` (`rfc4646_subtag`),
- UNIQUE KEY `uniq_code` (`rfc4646_subtag`, `iso639_2_code`)
+ 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 */;
CREATE TABLE `language_script_mapping` (
`language_subtag` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
`script_subtag` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- PRIMARY 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 */;
`added` date DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
- KEY `subtag` (`subtag`),
- UNIQUE KEY `uniq_lang` (`subtag`, `type`)
+ 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 */;
`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',
-- Table structure for table `recalls`
--
-DROP TABLE IF EXISTS recalls;
-CREATE TABLE recalls ( -- information related to recalls in Koha
- recall_id int(11) NOT NULL auto_increment, -- primary key
- borrowernumber int(11) NOT NULL DEFAULT 0, -- foreign key from the borrowers table defining which patron requested a recall
- recalldate datetime DEFAULT NULL, -- the date the recall request was placed
- biblionumber int(11) NOT NULL DEFAULT 0, -- foreign key from the biblio table defining which bib record this request is for
- branchcode varchar(10) DEFAULT NULL, -- foreign key from the branches table defining which branch the patron wishes to pick up their recall from
- cancellationdate datetime DEFAULT NULL, -- the date this recall was cancelled
- recallnotes mediumtext, -- notes related to this recall
- priority smallint(6) DEFAULT NULL, -- where in the queue the patron sits
- status ENUM('requested','overdue','waiting','in_transit','cancelled','expired','fulfilled') DEFAULT 'requested', -- request status
- timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- the date and time this recall was last updated
- itemnumber int(11) DEFAULT NULL, -- foreign key from the items table defining the specific item the recall request was placed on
- waitingdate datetime DEFAULT NULL, -- the date the item was marked as waiting for the patron at the library
- expirationdate datetime DEFAULT NULL, -- the date the recall expires
- old TINYINT(1) NOT NULL DEFAULT 0, -- flag if the recall is old and no longer active, i.e. expired, cancelled or completed
- item_level_recall TINYINT(1) NOT NULL DEFAULT 0, -- flag if item-level recall
- PRIMARY KEY (recall_id),
- KEY borrowernumber (borrowernumber),
- KEY biblionumber (biblionumber),
- KEY itemnumber (itemnumber),
- KEY branchcode (branchcode),
- CONSTRAINT recalls_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT recalls_ibfk_2 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT recalls_ibfk_3 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT recalls_ibfk_4 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+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',
+ `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',
/*!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,
`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,
`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`),
`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;
`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 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 / koha-ffzg.git / blobdiff