X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=8a8ca4912265e2db9de034956f9e9705ed650cd0;hb=fe147e9df64fab7cbe1fdbc859b72a4c396fae52;hp=20b1a5716e64810c80bf11fbf6bb18430473cae1;hpb=200a468a9c4a75f48ef2fbe77af03c497347c79f;p=koha.git diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 20b1a5716e..8a8ca49122 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -267,9 +267,22 @@ CREATE TABLE `branches` ( -- information about your libraries or branches are st opac_info MEDIUMTEXT, -- HTML that displays in OPAC `geolocation` VARCHAR(255) default NULL, -- geolocation of your library `marcorgcode` VARCHAR(16) default NULL, -- 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, -- the ability to act as a pickup location PRIMARY KEY (`branchcode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- +-- Table structure for table `branches_overdrive` +-- + +DROP TABLE IF EXISTS `branches_overdrive`; +CREATE TABLE IF NOT EXISTS branches_overdrive ( + `branchcode` VARCHAR( 10 ) NOT NULL , + `authname` VARCHAR( 255 ) NOT NULL , + PRIMARY KEY (`branchcode`) , + CONSTRAINT `branches_overdrive_ibfk_1` 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 `browser` -- @@ -429,6 +442,21 @@ CREATE TABLE `class_sort_rules` ( 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` -- @@ -439,10 +467,12 @@ CREATE 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; -- @@ -659,7 +689,7 @@ CREATE TABLE `deleteditems` ( `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and due date for checked out) `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2) `cn_sort` varchar(255) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting - `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8) + `ccode` varchar(80) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8) `materials` MEDIUMTEXT default NULL, -- materials specified (MARC21 952$3) `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u) `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y) @@ -857,12 +887,14 @@ CREATE TABLE `issuingrules` ( -- circulation and fine rules `no_auto_renewal_after_hard_limit` date default NULL, -- no auto renewal allowed after a given date `reservesallowed` smallint(6) NOT NULL default "0", -- how many holds are allowed `holds_per_record` SMALLINT(6) NOT NULL DEFAULT 1, -- How many holds a patron can have on a given bib + `holds_per_day` SMALLINT(6) DEFAULT NULL, -- How many holds a patron can have on a day `branchcode` varchar(10) NOT NULL default '', -- the branch this rule is for (branches.branchcode) overduefinescap decimal(28,6) default NULL, -- the maximum amount of an overdue fine cap_fine_to_replacement_price BOOLEAN NOT NULL DEFAULT '0', -- cap the fine based on item's replacement price 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`) @@ -921,7 +953,7 @@ CREATE TABLE `items` ( -- holdings/item information `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and due date for checked out) `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2) `cn_sort` varchar(255) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting - `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8) + `ccode` varchar(80) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8) `materials` MEDIUMTEXT default NULL, -- materials specified (MARC21 952$3) `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u) `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y) @@ -1460,6 +1492,7 @@ CREATE TABLE `search_field` ( `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; @@ -1695,24 +1728,6 @@ CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; --- --- Table structure for table borrower_sync --- - -DROP TABLE IF EXISTS `borrower_sync`; -CREATE TABLE borrower_sync ( - borrowersyncid int(11) NOT NULL AUTO_INCREMENT, -- Primary key, unique identifier - borrowernumber int(11) NOT NULL, -- Connects data about synchronisations to a borrower - synctype varchar(32) NOT NULL, -- There could potentially be more than one kind of syncing going on, a text string here can be used to tell them apart. E.g.: The Norwegian national patron database uses 'norwegianpatrondb' in this column - sync tinyint(1) NOT NULL DEFAULT '0', -- A boolean (1/0) for turning syncing off and on for individual borrowers - syncstatus varchar(10) DEFAULT NULL, -- The sync status for any given borrower. Could be text strings like 'new', 'edited', 'synced' etc. The values used here will depend on the actual syncing being done. - lastsync varchar(50) DEFAULT NULL, -- Date of the last successfull sync. The date format might be different depending on the service that is being used, so no special date format is being enforced here. - hashed_pin varchar(64) DEFAULT NULL, -- Perhaps specific to The Norwegian national patron database, this column holds a hashed PIN code - PRIMARY KEY (borrowersyncid), - KEY borrowernumber (borrowernumber), - CONSTRAINT borrower_sync_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; - -- -- Table structure for table api_keys -- @@ -1754,6 +1769,7 @@ CREATE TABLE `issues` ( -- information related to check outs or issues `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag `note` LONGTEXT default NULL, -- issue note text `notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss) + `noteseen` int(1) default NULL, -- describes whether checkout note has been seen 1, not been seen 0 or doesn't exist null PRIMARY KEY (`issue_id`), UNIQUE KEY `itemnumber` (`itemnumber`), KEY `issuesborridx` (`borrowernumber`), @@ -1785,6 +1801,7 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag `note` LONGTEXT default NULL, -- issue note text `notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss) + `noteseen` int(1) default NULL, -- describes whether checkout note has been seen 1, not been seen 0 or doesn't exist null PRIMARY KEY (`issue_id`), KEY `old_issuesborridx` (`borrowernumber`), KEY `old_issuesitemidx` (`itemnumber`), @@ -1822,6 +1839,7 @@ SET character_set_client = utf8; CREATE TABLE `creator_batches` ( `label_id` int(11) NOT NULL AUTO_INCREMENT, `batch_id` int(10) NOT NULL DEFAULT '1', + `description` mediumtext DEFAULT NULL, `item_number` int(11) DEFAULT NULL, `borrower_number` int(11) DEFAULT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, @@ -1878,7 +1896,7 @@ DROP TABLE IF EXISTS `reserves`; CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha `reserve_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 this hold is for - `reservedate` date default NULL, -- the date the hold was places + `reservedate` date default NULL, -- the date the hold was placed `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at `notificationdate` date default NULL, -- currently unused @@ -1994,7 +2012,7 @@ CREATE TABLE `statistics` ( -- information related to transactions (circulation `branch` varchar(10) default NULL, -- foreign key, branch where the transaction occurred `proccode` varchar(4) default NULL, -- type of procedure used when making payments (does not appear in the code) `value` double(16,4) default NULL, -- monetary value associated with the transaction - `type` varchar(16) default NULL, -- transaction type (locause, issue, return, renew, writeoff, payment, Credit*) + `type` varchar(16) default NULL, -- transaction type (localuse, issue, return, renew, writeoff, payment) `other` LONGTEXT, -- used by SIP `usercode` varchar(10) default NULL, -- unused in Koha `itemnumber` int(11) default NULL, -- foreign key from the items table, links transaction to a specific item @@ -2002,7 +2020,7 @@ CREATE TABLE `statistics` ( -- information related to transactions (circulation `location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c) `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table, links transaction to a specific borrower `associatedborrower` int(11) default NULL, -- unused in Koha - `ccode` varchar(10) default NULL, -- foreign key from the items table, links transaction to a specific collection code + `ccode` varchar(80) default NULL, -- foreign key from the items table, links transaction to a specific collection code KEY `timeidx` (`datetime`), KEY `branch_idx` (`branch`), KEY `proccode_idx` (`proccode`), @@ -2111,6 +2129,7 @@ CREATE TABLE `subscription` ( -- information related to the subscription `itemtype` VARCHAR( 10 ) NULL, `previousitemtype` VARCHAR( 10 ) 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, CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -2338,17 +2357,6 @@ CREATE TABLE `zebraqueue` ( KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; --- --- Table structure for table `services_throttle` --- - -DROP TABLE IF EXISTS `services_throttle`; -CREATE TABLE `services_throttle` ( - `service_type` varchar(10) NOT NULL default '', - `service_count` varchar(45) default NULL, - PRIMARY KEY (`service_type`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; - -- -- Table structure for table `language_subtag_registry` -- http://www.w3.org/International/articles/language-tags/ @@ -2654,7 +2662,7 @@ CREATE TABLE branch_transfer_limits ( toBranch varchar(10) NOT NULL, fromBranch varchar(10) NOT NULL, itemtype varchar(10) NULL, - ccode varchar(10) NULL, + ccode varchar(80) NULL, PRIMARY KEY (limitId) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -2697,13 +2705,12 @@ DROP TABLE IF EXISTS `accountlines`; 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, `amount` decimal(28,6) default NULL, `description` LONGTEXT, - `dispute` LONGTEXT, `accounttype` varchar(5) default NULL, `payment_type` varchar(80) default NULL, -- optional authorised value PAYMENT_TYPE `amountoutstanding` decimal(28,6) default NULL, @@ -2715,7 +2722,7 @@ CREATE TABLE `accountlines` ( 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_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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -2857,8 +2864,8 @@ CREATE TABLE `aqbudgets` ( -- information related to Funds `budget_name` varchar(80) default NULL, -- name assigned to the fund by the user `budget_branchcode` varchar(10) default NULL, -- branch that this fund belongs to (branches.branchcode) `budget_amount` decimal(28,6) NULL default '0.00', -- total amount for this fund - `budget_encumb` decimal(28,6) NULL default '0.00', -- not used in the code - `budget_expend` decimal(28,6) NULL default '0.00', -- not used in the code + `budget_encumb` decimal(28,6) NULL default '0.00', -- budget warning at percentage + `budget_expend` decimal(28,6) NULL default '0.00', -- budget warning at amount `budget_notes` LONGTEXT, -- notes related to this fund `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this fund was last touched (created or modified) `budget_period_id` int(11) default NULL, -- id of the budget that this fund belongs to (aqbudgetperiods.budget_period_id) @@ -3140,6 +3147,25 @@ CREATE TABLE aqinvoices ( CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- +-- Table structure for table 'aqinvoice_adjustments' +-- + +DROP TABLE IF EXISTS aqinvoice_adjustments; +CREATE TABLE aqinvoice_adjustments ( + adjustment_id int(11) NOT NULL AUTO_INCREMENT, -- primary key for adjustments + invoiceid int(11) NOT NULL, -- foreign key to link an adjustment to an invoice + adjustment decimal(28,6), -- amount of adjustment + reason varchar(80) default NULL, -- reason for adjustment defined by authorised values in ADJ_REASON category + note mediumtext default NULL, -- text to explain adjustment + budget_id int(11) default NULL, -- optional link to budget to apply adjustment to + encumber_open smallint(1) NOT NULL default 1, -- whether or not to encumber the finds when invoice is still open, 1 = yes, 0 = no + timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- timestamp of last adjustment to adjustment + PRIMARY KEY (adjustment_id), + CONSTRAINT aqinvoice_adjustments_fk_invoiceid FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT aqinvoice_adjustments_fk_budget_id FOREIGN KEY (budget_id) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + -- -- Table structure for table `aqorders` -- @@ -3167,7 +3193,8 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `purchaseordernumber` LONGTEXT, -- not used? always NULL `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno) `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified - `rrp` decimal(13,2) DEFAULT NULL, -- the replacement cost for this line item + `rrp` decimal(13,2) DEFAULT NULL, -- the retail cost for this line item + `replacementprice` decimal(28,6) DEFAULT NULL, -- the replacement cost for this line item `rrp_tax_excluded` decimal(28,6) default NULL, -- the replacement cost excluding tax `rrp_tax_included` decimal(28,6) default NULL, -- the replacement cost including tax `ecost` decimal(13,2) DEFAULT NULL, -- the replacement cost for this line item @@ -3454,6 +3481,7 @@ CREATE TABLE IF NOT EXISTS `borrower_modifications` ( `smsalertnumber` varchar(50) DEFAULT NULL, `privacy` int(11) DEFAULT NULL, `extended_attributes` MEDIUMTEXT DEFAULT NULL, + `gdpr_proc_consent` datetime, -- data processing consent PRIMARY KEY (`verification_token` (191),`borrowernumber`), KEY `verification_token` (`verification_token` (191)), KEY `borrowernumber` (`borrowernumber`) @@ -3509,6 +3537,21 @@ CREATE TABLE IF NOT EXISTS plugin_data ( PRIMARY KEY ( `plugin_class` (191), `plugin_key` (191) ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- +-- Table structure for table patron_consent +-- + +DROP TABLE IF EXISTS patron_consent; +CREATE TABLE patron_consent ( + id int AUTO_INCREMENT, + borrowernumber int NOT NULL, + type enum('GDPR_PROCESSING' ), -- allows for future extension + given_on datetime, + refused_on datetime, + PRIMARY KEY (id), + FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + -- -- Table structure for table `patron_lists` -- @@ -3518,6 +3561,7 @@ CREATE TABLE patron_lists ( patron_list_id int(11) NOT NULL AUTO_INCREMENT, -- unique identifier name varchar(255) CHARACTER SET utf8mb4 NOT NULL, -- the list's name owner int(11) NOT NULL, -- borrowernumber of the list creator + shared tinyint(1) default 0, PRIMARY KEY (patron_list_id), KEY owner (owner) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -3567,7 +3611,7 @@ CREATE TABLE IF NOT EXISTS marc_modification_template_actions ( mmta_id int(11) NOT NULL AUTO_INCREMENT, template_id int(11) NOT NULL, ordering int(3) NOT NULL, - action ENUM('delete_field','update_field','move_field','copy_field','copy_and_replace_field') NOT NULL, + action ENUM('delete_field','add_field','update_field','move_field','copy_field','copy_and_replace_field') NOT NULL, field_number smallint(6) NOT NULL DEFAULT '0', from_field varchar(3) NOT NULL, from_subfield varchar(1) DEFAULT NULL, @@ -3722,7 +3766,7 @@ DROP TABLE IF EXISTS edifact_ean; CREATE TABLE IF NOT EXISTS edifact_ean ( ee_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, description VARCHAR(128) NULL DEFAULT NULL, - branchcode VARCHAR(10) NOT NULL REFERENCES branches (branchcode), + branchcode VARCHAR(10) NULL DEFAULT NULL REFERENCES branches (branchcode), ean VARCHAR(15) NOT NULL, id_code_qualifier VARCHAR(3) NOT NULL DEFAULT '14', CONSTRAINT efk_branchcode FOREIGN KEY ( branchcode ) REFERENCES branches ( branchcode ) @@ -3787,7 +3831,7 @@ CREATE TABLE `course_items` ( `ci_id` int(11) NOT NULL AUTO_INCREMENT, -- course item id `itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve `itype` varchar(10) DEFAULT NULL, -- new itemtype for the item to have while on reserve (optional) - `ccode` varchar(10) DEFAULT NULL, -- new category code for the item to have while on reserve (optional) + `ccode` varchar(80) DEFAULT NULL, -- new category code for the item to have while on reserve (optional) `holdingbranch` varchar(10) DEFAULT NULL, -- new holding branch for the item to have while on reserve (optional) `location` varchar(80) DEFAULT NULL, -- new shelving location for the item to have while on reseve (optional) `enabled` enum('yes','no') NOT NULL DEFAULT 'no', -- if at least one enabled course has this item on reseve, this field will be 'yes', otherwise it will be 'no' @@ -4130,7 +4174,8 @@ CREATE TABLE illrequests ( completed date DEFAULT NULL, -- Date the request was completed medium varchar(30) DEFAULT NULL, -- The Koha request type accessurl varchar(500) DEFAULT NULL, -- Potential URL for accessing item - cost varchar(20) DEFAULT NULL, -- Cost of request + cost varchar(20) DEFAULT NULL, -- Quotes cost of request + price_paid varchar(20) DEFAULT NULL, -- Final cost of request notesopac MEDIUMTEXT DEFAULT NULL, -- Patron notes attached to request notesstaff MEDIUMTEXT DEFAULT NULL, -- Staff notes attached to request orderid varchar(50) DEFAULT NULL, -- Backend id attached to request @@ -4153,7 +4198,8 @@ DROP TABLE IF EXISTS `illrequestattributes`; CREATE TABLE illrequestattributes ( illrequest_id bigint(20) unsigned NOT NULL, -- ILL request number type varchar(200) NOT NULL, -- API ILL property name - value MEDIUMTEXT NOT NULL, -- API ILL property value + value MEDIUMTEXT NOT NULL, -- API ILL property value + readonly tinyint(1) NOT NULL DEFAULT 1, -- Is this attribute read only PRIMARY KEY (`illrequest_id`, `type` (191)), CONSTRAINT `illrequestattributes_ifk` FOREIGN KEY (illrequest_id) @@ -4174,7 +4220,7 @@ CREATE TABLE library_groups ( description MEDIUMTEXT NULL DEFAULT NULL, -- Longer explanation of the group, if necessary ft_hide_patron_info tinyint(1) NOT NULL DEFAULT 0, -- Turn on the feature "Hide patron's info" for this group ft_search_groups_opac tinyint(1) NOT NULL DEFAULT 0, -- Use this group for staff side search groups - ft_search_groups_staff tinyint(1) NOT NULL DEFAULT 0, -- Use this group for opac side search groups + ft_search_groups_staff tinyint(1) NOT NULL DEFAULT 0, -- Use this group for opac side search groups created_on TIMESTAMP NULL, -- Date and time of creation updated_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Date and time of last PRIMARY KEY id ( id ), @@ -4195,6 +4241,102 @@ CREATE TABLE `oauth_access_tokens` ( 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` +-- + +DROP TABLE IF EXISTS `circulation_rules`; +CREATE TABLE `circulation_rules` ( + `id` int(11) NOT NULL auto_increment, + `branchcode` varchar(10) NULL default NULL, + `categorycode` varchar(10) NULL default NULL, + `itemtype` varchar(10) NULL default NULL, + `rule_name` varchar(32) NOT NULL, + `rule_value` varchar(32) NOT NULL, + PRIMARY KEY (`id`), + CONSTRAINT `circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `circ_rules_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `circ_rules_ibfk_3` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE, + KEY `rule_name` (`rule_name`), + UNIQUE (`branchcode`,`categorycode`,`itemtype`,`rule_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `stockrotationrotas` +-- + +CREATE TABLE IF NOT EXISTS stockrotationrotas ( + rota_id int(11) auto_increment, -- Stockrotation rota ID + title varchar(100) NOT NULL, -- Title 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `stockrotationstages` +-- + +CREATE TABLE IF NOT EXISTS stockrotationstages ( + stage_id int(11) auto_increment, -- Unique stage ID + position int(11) NOT NULL, -- The position of this stage within its rota + rota_id int(11) NOT NULL, -- The rota this stage belongs to + branchcode_id varchar(10) NOT NULL, -- Branch this stage relates to + duration int(11) NOT NULL default 4, -- The number of days items shoud occupy this stage + PRIMARY KEY (`stage_id`), + CONSTRAINT `stockrotationstages_rifk` + FOREIGN KEY (`rota_id`) + REFERENCES `stockrotationrotas` (`rota_id`) + ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT `stockrotationstages_bifk` + FOREIGN KEY (`branchcode_id`) + REFERENCES `branches` (`branchcode`) + ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `stockrotationitems` +-- + +CREATE TABLE IF NOT EXISTS stockrotationitems ( + itemnumber_id int(11) NOT NULL, -- Itemnumber to link to a stage & rota + stage_id int(11) NOT NULL, -- stage ID to link the item to + indemand tinyint(1) NOT NULL default 0, -- Should this item be skipped for rotation? + fresh tinyint(1) NOT NULL default 0, -- Flag showing item is only just added to rota + PRIMARY KEY (itemnumber_id), + CONSTRAINT `stockrotationitems_iifk` + FOREIGN KEY (`itemnumber_id`) + REFERENCES `items` (`itemnumber`) + ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT `stockrotationitems_sifk` + FOREIGN KEY (`stage_id`) + REFERENCES `stockrotationstages` (`stage_id`) + ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;