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`
--
`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)
`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)
`id` int(11) NOT NULL AUTO_INCREMENT,
`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') 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') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine',
PRIMARY KEY (`id`),
UNIQUE KEY (`name` (191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
--- Table structure for table borrower_sync
+-- Table structure for table api_keys
--
-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
+DROP TABLE IF EXISTS `api_keys`;
+CREATE TABLE `api_keys` (
+ `client_id` VARCHAR(191) NOT NULL, -- API client ID
+ `secret` VARCHAR(191) NOT NULL, -- API client secret used for API authentication
+ `description` VARCHAR(255) NOT NULL, -- API client description
+ `patron_id` INT(11) NOT NULL, -- Foreign key to the borrowers table
+ `active` TINYINT(1) DEFAULT 1 NOT NULL, -- 0 means this API key is revoked
+ PRIMARY KEY `client_id` (`client_id`),
+ UNIQUE KEY `secret` (`secret`),
+ KEY `patron_id` (`patron_id`),
+ CONSTRAINT `api_keys_fk_patron_id`
+ FOREIGN KEY (`patron_id`)
+ REFERENCES `borrowers` (`borrowernumber`)
+ ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
`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`),
`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`),
`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`),
`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;
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/
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;
`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,
`lastincrement` decimal(28,6) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`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)
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`
--
`unitprice_tax_excluded` decimal(28,6) default NULL, -- the unit price excluding tax (on receiving)
`unitprice_tax_included` decimal(28,6) default NULL, -- the unit price including tax (on receiving)
`quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far
+ `created_by` int(11) NULL DEFAULT NULL, -- the borrowernumber of order line's creator
`datecancellationprinted` date default NULL, -- the date the line item was deleted
`cancellationreason` MEDIUMTEXT default NULL, -- reason of cancellation
`order_internalnote` LONGTEXT, -- notes related to this order line, made for staff
`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
KEY `budget_id` (`budget_id`),
KEY `parent_ordernumber` (`parent_ordernumber`),
KEY `orderstatus` (`orderstatus`),
+ CONSTRAINT aqorders_created_by FOREIGN KEY (created_by) REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `aqorders_budget_id_fk` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
`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`)
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`
--
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;
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,
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'
UNIQUE KEY title ( title )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+--
+-- Table structure for table 'oauth_access_tokens'
+--
+
+DROP TABLE IF EXISTS `oauth_access_tokens`;
+CREATE TABLE `oauth_access_tokens` (
+ `access_token` VARCHAR(191) NOT NULL, -- generarated access token
+ `client_id` VARCHAR(191) NOT NULL, -- the client id the access token belongs to
+ `expires` INT NOT NULL, -- expiration time in seconds
+ PRIMARY KEY (`access_token`)
+) 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;
+
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;