PRIMARY KEY (collections_tracking_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
---
--- Table structure for table `branch_borrower_circ_rules`
---
-
-DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
-CREATE TABLE `branch_borrower_circ_rules` ( -- includes default circulation rules for patron categories found under "Checkout limit by patron category"
- `branchcode` VARCHAR(10) NOT NULL, -- the branch this rule applies to (branches.branchcode)
- `categorycode` VARCHAR(10) NOT NULL, -- the patron category this rule applies to (categories.categorycode)
- `maxissueqty` int(4) default NULL, -- the maximum number of checkouts this patron category can have at this branch
- `maxonsiteissueqty` int(4) default NULL, -- the maximum number of on-site checkouts this patron category can have at this branch
- PRIMARY KEY (`categorycode`, `branchcode`),
- CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
- ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `branch_borrower_circ_rules_ibfk_2` 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 `default_borrower_circ_rules`
---
-
-DROP TABLE IF EXISTS `default_borrower_circ_rules`;
-CREATE TABLE `default_borrower_circ_rules` ( -- default checkout rules found under "Default checkout, hold and return policy"
- `categorycode` VARCHAR(10) NOT NULL, -- patron category this rul
- `maxissueqty` int(4) default NULL,
- `maxonsiteissueqty` int(4) default NULL,
- PRIMARY KEY (`categorycode`),
- CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
- ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
--
-- Table structure for table `default_branch_circ_rules`
--
DROP TABLE IF EXISTS `default_branch_circ_rules`;
CREATE TABLE `default_branch_circ_rules` (
`branchcode` VARCHAR(10) NOT NULL,
- `maxissueqty` int(4) default NULL,
- `maxonsiteissueqty` int(4) default NULL,
`holdallowed` tinyint(1) default NULL,
hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode
`returnbranch` varchar(15) default NULL,
DROP TABLE IF EXISTS `default_circ_rules`;
CREATE TABLE `default_circ_rules` (
`singleton` enum('singleton') NOT NULL default 'singleton',
- `maxissueqty` int(4) default NULL,
- `maxonsiteissueqty` int(4) default NULL,
`holdallowed` int(1) default NULL,
hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode
`returnbranch` varchar(15) default NULL,
UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+--
+-- Table structure for table `class_split_rules`
+--
+
+DROP TABLE IF EXISTS `class_split_rules`;
+
+CREATE TABLE class_split_rules (
+ class_split_rule varchar(10) NOT NULL default '',
+ description LONGTEXT,
+ split_routine varchar(30) NOT NULL default '',
+ split_regex varchar(255) NOT NULL default '',
+ PRIMARY KEY (class_split_rule),
+ UNIQUE KEY class_split_rule_idx (class_split_rule)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
--
-- Table structure for table `class_sources`
--
`description` LONGTEXT,
`used` tinyint(4) NOT NULL default 0,
`class_sort_rule` varchar(10) NOT NULL default '',
+ `class_split_rule` varchar(10) NOT NULL default '',
PRIMARY KEY (`cn_source`),
UNIQUE KEY `cn_source_idx` (`cn_source`),
KEY `used_idx` (`used`),
- CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
+ CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`),
+ CONSTRAINT `class_source_ibfk_2` FOREIGN KEY (`class_split_rule`) REFERENCES `class_split_rules` (`class_split_rule`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
`renewals` smallint(6) default NULL, -- number of times this item has been renewed
`reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
`restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5)
- `itemnotes` LONGTEXT, -- public notes on this item (MARC21 952$x)
- `itemnotes_nonpublic` LONGTEXT default NULL,
+ `itemnotes` LONGTEXT, -- public notes on this item (MARC21 952$z)
+ `itemnotes_nonpublic` LONGTEXT default NULL, -- non-public notes on this item (MARC21 952$x)
`holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)
`paidfor` LONGTEXT,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
`chargeperiod` int(11) default NULL, -- how often the fine amount is charged
`chargeperiod_charge_at` tinyint(1) NOT NULL DEFAULT '0', -- Should fine be given at the start ( 1 ) or the end ( 0 ) of the period
`accountsent` int(11) default NULL, -- not used? always NULL
- `chargename` varchar(100) default NULL, -- not used? always NULL
- `maxissueqty` int(4) default NULL, -- total number of checkouts allowed
- `maxonsiteissueqty` int(4) default NULL, -- total number of on-site checkouts allowed
`issuelength` int(4) default NULL, -- length of checkout in the unit set in issuingrules.lengthunit
`lengthunit` varchar(10) default 'days', -- unit of checkout length (days, hours)
`hardduedate` date default NULL, -- hard due date
onshelfholds tinyint(1) NOT NULL default 0, -- allow holds for items that are on shelf
opacitemholds char(1) NOT NULL default 'N', -- allow opac users to place specific items on hold
article_requests enum('no','yes','bib_only','item_only') NOT NULL DEFAULT 'no', -- allow article requests to be placed,
+ `note` varchar(100) default NULL, -- description of rule, reason for setting rule
PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
KEY `categorycode` (`categorycode`),
KEY `itemtype` (`itemtype`)
`renewals` smallint(6) default NULL, -- number of times this item has been renewed
`reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
`restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5)
- `itemnotes` LONGTEXT, -- public notes on this item (MARC21 952$x)
- `itemnotes_nonpublic` LONGTEXT default NULL,
+ `itemnotes` LONGTEXT, -- public notes on this item (MARC21 952$z)
+ `itemnotes_nonpublic` LONGTEXT default NULL, -- non-public notes on this item (MARC21 952$x)
`holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)
`paidfor` LONGTEXT,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
report_area varchar(6) default NULL,
report_group varchar(80) default NULL,
report_subgroup varchar(80) default NULL,
+ `mana_id` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY sql_area_group_idx (report_group, report_subgroup),
KEY boridx (`borrowernumber`)
`name` varchar(255) NOT NULL COMMENT 'the name of the field as it will be stored in the search engine',
`label` varchar(255) NOT NULL COMMENT 'the human readable name of the field, for display',
`type` ENUM('', 'string', 'date', 'number', 'boolean', 'sum', 'isbn', 'stdno') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine',
+ `weight` decimal(5,2) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`name` (191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
`reneweddate` date default NULL, -- date of last renewal for the subscription
`itemtype` VARCHAR( 10 ) NULL,
`previousitemtype` VARCHAR( 10 ) NULL,
+ `mana_id` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`subscriptionid`),
KEY `by_biblionumber` (`biblionumber`),
CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id) ON DELETE SET NULL ON UPDATE CASCADE,
`message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- the date and time the message was written
`manager_id` int(11) default NULL, -- creator of message
PRIMARY KEY (`message_id`),
- CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL
+ CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL,
+ CONSTRAINT `messages_borrowernumber` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
CREATE TABLE `accountlines` (
`accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
`issue_id` int(11) NULL DEFAULT NULL,
- `borrowernumber` int(11) NOT NULL default 0,
+ `borrowernumber` int(11) DEFAULT NULL,
`accountno` smallint(6) NOT NULL default 0,
`itemnumber` int(11) default NULL,
`date` date default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`note` MEDIUMTEXT NULL default NULL,
`manager_id` int(11) NULL,
+ `branchcode` VARCHAR( 10 ) NULL DEFAULT NULL, -- the branchcode of the library where a payment was made, a manual invoice created, etc.
PRIMARY KEY (`accountlines_id`),
KEY `acctsborridx` (`borrowernumber`),
KEY `timeidx` (`timestamp`),
KEY `itemnumber` (`itemnumber`),
- CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
+ KEY `branchcode` (`branchcode`),
+ CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,
+ CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
`id` INT(11) NOT NULL AUTO_INCREMENT,
`biblionumber` INT(11) NOT NULL,
`format` VARCHAR(16) NOT NULL,
- `marcflavour` VARCHAR(16) NOT NULL,
+ `schema` VARCHAR(16) NOT NULL,
`metadata` LONGTEXT NOT NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(id),
- UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
+ UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`schema`),
CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
`id` INT(11) NOT NULL AUTO_INCREMENT,
`biblionumber` INT(11) NOT NULL,
`format` VARCHAR(16) NOT NULL,
- `marcflavour` VARCHAR(16) NOT NULL,
+ `schema` VARCHAR(16) NOT NULL,
`metadata` LONGTEXT NOT NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY(id),
- UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
+ UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`schema`),
CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
biblio_id integer DEFAULT NULL, -- Potential bib linked to request
branchcode varchar(50) NOT NULL, -- The branch associated with the request
status varchar(50) DEFAULT NULL, -- Current Koha status of request
+ status_alias varchar(80) DEFAULT NULL, -- Foreign key to relevant authorised_values.authorised_value
placed date DEFAULT NULL, -- Date the request was placed
replied date DEFAULT NULL, -- Last API response
updated timestamp DEFAULT CURRENT_TIMESTAMP -- Last modification to request
CONSTRAINT `illrequests_bcfk_2`
FOREIGN KEY (`branchcode`)
REFERENCES `branches` (`branchcode`)
- ON UPDATE CASCADE ON DELETE CASCADE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT `illrequests_safk`
+ FOREIGN KEY (`status_alias`)
+ REFERENCES `authorised_values` (`authorised_value`)
+ ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
PRIMARY KEY (`access_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+--
+-- Table structure for table illcomments
+--
+
+DROP TABLE IF EXISTS `illcomments`;
+CREATE TABLE illcomments (
+ illcomment_id int(11) NOT NULL AUTO_INCREMENT, -- Unique ID of the comment
+ illrequest_id bigint(20) unsigned NOT NULL, -- ILL request number
+ borrowernumber integer DEFAULT NULL, -- Link to the user who made the comment (could be librarian, patron or ILL partner library)
+ comment text DEFAULT NULL, -- The text of the comment
+ timestamp timestamp DEFAULT CURRENT_TIMESTAMP, -- Date and time when the comment was made
+ PRIMARY KEY ( illcomment_id ),
+ CONSTRAINT illcomments_bnfk
+ FOREIGN KEY ( borrowernumber )
+ REFERENCES borrowers ( borrowernumber )
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT illcomments_ifk
+ FOREIGN KEY (illrequest_id)
+ REFERENCES illrequests ( illrequest_id )
+ ON UPDATE CASCADE ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
--
-- Table structure for table `circulation_rules`
--
--
CREATE TABLE IF NOT EXISTS stockrotationrotas (
- rota_id int(11) auto_increment, -- Stockrotation rota ID
+ rota_id int(11) auto_increment, -- Stockrotation rota ID
title varchar(100) NOT NULL, -- Title for this rota
- description text NOT NULL default '', -- Description for this rota
+ description text NOT NULL, -- Description for this rota
cyclical tinyint(1) NOT NULL default 0, -- Should items on this rota keep cycling?
active tinyint(1) NOT NULL default 0, -- Is this rota currently active?
PRIMARY KEY (`rota_id`)
projects / koha.git / blobdiff