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`
--
DROP TABLE IF EXISTS `browser`;
`BlockExpiredPatronOpacActions` tinyint(1) NOT NULL default '-1', -- 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' ) NOT NULL DEFAULT 'default', -- Default privacy setting for this patron category
`checkprevcheckout` varchar(7) NOT NULL default 'inherit', -- 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'.
+ `reset_password` TINYINT(1) NULL DEFAULT NULL, -- if patrons of this category can do the password reset flow,
+ `change_password` TINYINT(1) NULL DEFAULT NULL, -- if patrons of this category can change their passwords in the OAPC
PRIMARY KEY (`categorycode`),
UNIQUE KEY `categorycode` (`categorycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
) 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,
) 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;
--
`lang` varchar(25) NOT NULL default 'default', -- lang to use to send notices to this patron
`login_attempts` int(4) default 0, -- number of failed login attemps
`overdrive_auth_token` MEDIUMTEXT default NULL, -- persist OverDrive auth token
+ `flgAnonymized` tinyint DEFAULT 0, -- flag for data anonymization
KEY borrowernumber (borrowernumber),
KEY `cardnumber` (`cardnumber`),
KEY `sms_provider_id` (`sms_provider_id`)
`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
`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)
`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
`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`)
`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
`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)
itemtype varchar(10) NOT NULL default '', -- unique key, a code associated with the item type
description LONGTEXT, -- a plain text explanation of the item type
rentalcharge decimal(28,6) default NULL, -- the amount charged when this item is checked out/issued
+ rentalcharge_daily decimal(28,6) default NULL, -- the amount charged for each day between checkout date and due date
+ rentalcharge_hourly decimal(28,6) default NULL, -- the amount charged for each hour between checkout date and due date
defaultreplacecost decimal(28,6) default NULL, -- default replacement cost
processfee decimal(28,6) default NULL, -- default text be recorded in the column note when the processing fee is applied
notforloan smallint(6) default NULL, -- 1 if the item is not for loan, 0 if the item is available for loan
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,
+ `facet_order` TINYINT(4) DEFAULT NULL COMMENT 'the order place of the field in facet list if faceted',
PRIMARY KEY (`id`),
UNIQUE KEY (`name` (191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
id int(11) NOT NULL AUTO_INCREMENT,
index_name ENUM('biblios','authorities') NOT NULL COMMENT 'what storage index this map is for',
marc_type ENUM('marc21', 'unimarc', 'normarc') NOT NULL COMMENT 'what MARC type this map is for',
- marc_field VARCHAR(255) NOT NULL COMMENT 'the MARC specifier for this field',
+ marc_field VARCHAR(255) NOT NULL COLLATE utf8mb4_bin COMMENT 'the MARC specifier for this field',
PRIMARY KEY(`id`),
UNIQUE key `index_name` (`index_name`, `marc_field` (191), `marc_type`),
INDEX (`index_name`)
`lang` varchar(25) NOT NULL default 'default', -- lang to use to send notices to this patron
`login_attempts` int(4) default 0, -- number of failed login attemps
`overdrive_auth_token` MEDIUMTEXT default NULL, -- persist OverDrive auth token
+ `flgAnonymized` tinyint DEFAULT 0, -- flag for data anonymization
UNIQUE KEY `cardnumber` (`cardnumber`),
PRIMARY KEY `borrowernumber` (`borrowernumber`),
KEY `categorycode` (`categorycode`),
) 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
--
`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
`returndate` datetime default NULL, -- date the item was returned, will be NULL until moved to old_issues
`lastreneweddate` datetime default NULL, -- date the item was last renewed
- `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
+ `renewals` tinyint(4) NOT NULL default 0, -- lists the number of times the item was renewed
`auto_renew` BOOLEAN default FALSE, -- automatic renewal
`auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- automatic renewal error
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
`returndate` datetime default NULL, -- date the item was returned
`lastreneweddate` datetime default NULL, -- date the item was last renewed
- `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
+ `renewals` tinyint(4) NOT NULL default 0, -- lists the number of times the item was renewed
`auto_renew` BOOLEAN default FALSE, -- automatic renewal
`auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- automatic renewal error
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
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,
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
CREATE TABLE `statistics` ( -- information related to transactions (circulation and fines) in Koha
`datetime` datetime default NULL, -- date and time of the transaction
`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
`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`),
KEY `type_idx` (`type`),
KEY `usercode_idx` (`usercode`),
KEY `itemnumber_idx` (`itemnumber`),
`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,
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;
`tag_id` int(11) NOT NULL auto_increment, -- unique id and primary key
`borrowernumber` int(11) DEFAULT NULL, -- the patron who added the tag (borrowers.borrowernumber)
`biblionumber` int(11) NOT NULL, -- the bib record this tag was left on (biblio.biblionumber)
- `term` varchar(255) NOT NULL, -- the tag
+ `term` varchar(191) NOT NULL COLLATE utf8mb4_bin, -- the tag
`language` int(4) default NULL, -- the language the tag was left in
`date_created` datetime NOT NULL, -- the date the tag was added
PRIMARY KEY (`tag_id`),
DROP TABLE IF EXISTS `tags_approval`;
CREATE TABLE `tags_approval` ( -- approved tags
- `term` varchar(191) NOT NULL, -- the tag
+ `term` varchar(191) NOT NULL COLLATE utf8mb4_bin, -- the tag
`approved` int(1) NOT NULL default '0', -- whether the tag is approved or not (1=yes, 0=pending, -1=rejected)
`date_approved` datetime default NULL, -- the date this tag was approved
`approved_by` int(11) default NULL, -- the librarian who approved the tag (borrowers.borrowernumber)
DROP TABLE IF EXISTS `tags_index`;
CREATE TABLE `tags_index` ( -- a weighted list of all tags and where they are used
- `term` varchar(191) NOT NULL, -- the tag
+ `term` varchar(191) NOT NULL COLLATE utf8mb4_bin, -- the tag
`biblionumber` int(11) NOT NULL, -- the bib record this tag was used on (biblio.biblionumber)
`weight` int(9) NOT NULL default '1', -- the number of times this term was used on this bib record
PRIMARY KEY (`term`,`biblionumber`),
) 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/
-- RFC4646
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;
`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,
- `accountno` smallint(6) NOT NULL default 0,
+ `borrowernumber` int(11) DEFAULT NULL,
`itemnumber` int(11) default NULL,
`date` date default NULL,
`amount` decimal(28,6) default NULL,
`description` LONGTEXT,
- `dispute` LONGTEXT,
- `accounttype` varchar(5) default NULL,
+ `accounttype` varchar(16) default NULL,
+ `status` varchar(16) default NULL,
`payment_type` varchar(80) default NULL, -- optional authorised value PAYMENT_TYPE
`amountoutstanding` decimal(28,6) default NULL,
- `lastincrement` decimal(28,6) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`note` MEDIUMTEXT NULL default NULL,
- `manager_id` int(11) NULL,
+ `manager_id` int(11) NULL DEFAULT NULL,
+ `interface` VARCHAR(16) NOT 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`),
+ KEY `manager_id` (`manager_id`),
+ CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ 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;
--
`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)
DROP TABLE IF EXISTS `suggestions`;
CREATE TABLE `suggestions` ( -- purchase suggestions
`suggestionid` int(8) NOT NULL auto_increment, -- unique identifier assigned automatically by Koha
- `suggestedby` int(11) NOT NULL default 0, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table
+ `suggestedby` int(11) DEFAULT NULL, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table
`suggesteddate` date NOT NULL, -- date the suggestion was submitted
`managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table
`manageddate` date default NULL, -- date the suggestion was updated
PRIMARY KEY (`suggestionid`),
KEY `suggestedby` (`suggestedby`),
KEY `managedby` (`managedby`),
- KEY `status` (`STATUS`),
+ KEY `acceptedby` (`acceptedby`),
+ KEY `rejectedby` (`rejectedby`),
KEY `biblionumber` (`biblionumber`),
+ KEY `budgetid` (`budgetid`),
KEY `branchcode` (`branchcode`),
- CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE
+ KEY `status` (`STATUS`),
+ CONSTRAINT `suggestions_ibfk_suggestedby` FOREIGN KEY (`suggestedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `suggestions_ibfk_managedby` FOREIGN KEY (`managedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `suggestions_ibfk_acceptedby` FOREIGN KEY (`acceptedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `suggestions_ibfk_rejectedby` FOREIGN KEY (`rejectedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `suggestions_ibfk_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `suggestions_ibfk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
--
DROP TABLE IF EXISTS aqinvoice_adjustments;
-CREATE TABLE IF NOT 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
`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
`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`)
) 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`
--
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 )
`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'
`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
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
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;
--
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)
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 ),
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`
+--
+
+DROP TABLE IF EXISTS stockrotationrotas;
+CREATE TABLE 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`
+--
+
+DROP TABLE IF EXISTS stockrotationstages;
+CREATE TABLE 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`
+--
+
+DROP TABLE IF EXISTS stockrotationitems;
+CREATE TABLE 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 */;
projects / koha-equinox.git / blobdiff