/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
-/*!40101 SET NAMES utf8 */;
+/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
`datecreated` date default NULL,
`modification_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`origincode` varchar(20) default NULL,
- `authtrees` mediumtext,
+ `authtrees` LONGTEXT,
`marc` blob,
`linkid` bigint(20) default NULL,
- `marcxml` longtext NOT NULL,
+ `marcxml` LONGTEXT NOT NULL,
PRIMARY KEY (`authid`),
KEY `origincode` (`origincode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `auth_types`
`authtypecode` varchar(10) NOT NULL default '',
`authtypetext` varchar(255) NOT NULL default '',
`auth_tag_to_report` varchar(3) NOT NULL default '',
- `summary` mediumtext NOT NULL,
+ `summary` LONGTEXT NOT NULL,
PRIMARY KEY (`authtypecode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `auth_subfield_structure`
`linkid` tinyint(1) NOT NULL default 0,
`kohafield` varchar(45) NULL default '',
`frameworkcode` varchar(10) NOT NULL default '',
- `defaultvalue` TEXT,
+ `defaultvalue` MEDIUMTEXT,
PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
KEY `tab` (`authtypecode`,`tab`),
CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `auth_tag_structure`
`authorised_value` varchar(10) default NULL,
PRIMARY KEY (`authtypecode`,`tagfield`),
CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
DROP TABLE IF EXISTS `authorised_value_categories`;
CREATE TABLE `authorised_value_categories` (
- `category_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
+ `category_name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`category_name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `authorised_values`
`imageurl` varchar(200) default NULL, -- authorized value URL
PRIMARY KEY (`id`),
KEY `name` (`category`),
- KEY `lib` (`lib`),
+ KEY `lib` (`lib` (191)),
KEY `auth_value_idx` (`authorised_value`),
CONSTRAINT `authorised_values_authorised_values_category` FOREIGN KEY (`category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `biblio`
CREATE TABLE `biblio` ( -- table that stores bibliographic information
`biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
`frameworkcode` varchar(4) NOT NULL default '', -- foreign key from the biblio_framework table to identify which framework was used in cataloging this record
- `author` mediumtext, -- statement of responsibility from MARC record (100$a in MARC21)
- `title` mediumtext, -- title (without the subtitle) from the MARC record (245$a in MARC21)
- `unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
- `notes` mediumtext, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
+ `author` LONGTEXT, -- statement of responsibility from MARC record (100$a in MARC21)
+ `title` LONGTEXT, -- title (without the subtitle) from the MARC record (245$a in MARC21)
+ `unititle` LONGTEXT, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
+ `notes` LONGTEXT, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
`serial` tinyint(1) default NULL, -- Boolean indicating whether biblio is for a serial
- `seriestitle` mediumtext,
+ `seriestitle` LONGTEXT,
`copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
`datecreated` DATE NOT NULL, -- the date this record was added to Koha
- `abstract` mediumtext, -- summary from the MARC record (520$a in MARC21)
+ `abstract` LONGTEXT, -- summary from the MARC record (520$a in MARC21)
PRIMARY KEY (`biblionumber`),
KEY `blbnoidx` (`biblionumber`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `biblio_framework`
`frameworkcode` varchar(4) NOT NULL default '', -- the unique code assigned to the framework
`frameworktext` varchar(255) NOT NULL default '', -- the description/name given to the framework
PRIMARY KEY (`frameworkcode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `biblioitems`
CREATE TABLE `biblioitems` ( -- information related to bibliographic records in Koha
`biblioitemnumber` int(11) NOT NULL auto_increment, -- primary key, unique identifier assigned by Koha
`biblionumber` int(11) NOT NULL default 0, -- foreign key linking this table to the biblio table
- `volume` mediumtext,
- `number` mediumtext,
+ `volume` LONGTEXT,
+ `number` LONGTEXT,
`itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
- `isbn` mediumtext, -- ISBN (MARC21 020$a)
- `issn` mediumtext, -- ISSN (MARC21 022$a)
- `ean` mediumtext default NULL,
- `publicationyear` text,
+ `isbn` LONGTEXT, -- ISBN (MARC21 020$a)
+ `issn` LONGTEXT, -- ISSN (MARC21 022$a)
+ `ean` LONGTEXT default NULL,
+ `publicationyear` MEDIUMTEXT,
`publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
`volumedate` date default NULL,
- `volumedesc` text, -- volume information (MARC21 362$a)
- `collectiontitle` mediumtext default NULL,
- `collectionissn` text default NULL,
- `collectionvolume` mediumtext default NULL,
- `editionstatement` text default NULL,
- `editionresponsibility` text default NULL,
+ `volumedesc` MEDIUMTEXT, -- volume information (MARC21 362$a)
+ `collectiontitle` LONGTEXT default NULL,
+ `collectionissn` MEDIUMTEXT default NULL,
+ `collectionvolume` LONGTEXT default NULL,
+ `editionstatement` MEDIUMTEXT default NULL,
+ `editionresponsibility` MEDIUMTEXT default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`illus` varchar(255) default NULL, -- illustrations (MARC21 300$b)
`pages` varchar(255) default NULL, -- number of pages (MARC21 300$c)
- `notes` mediumtext,
+ `notes` LONGTEXT,
`size` varchar(255) default NULL, -- material size (MARC21 300$c)
`place` varchar(255) default NULL, -- publication place (MARC21 260$a)
`lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
- `url` text default NULL, -- url (MARC21 856$u)
+ `url` MEDIUMTEXT default NULL, -- url (MARC21 856$u)
`cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
`cn_class` varchar(30) default NULL,
`cn_item` varchar(10) default NULL,
KEY `isbn` (`isbn`(255)),
KEY `issn` (`issn`(255)),
KEY `ean` (`ean`(255)),
- KEY `publishercode` (`publishercode`),
+ KEY `publishercode` (`publishercode` (191)),
KEY `timestamp` (`timestamp`),
CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `borrower_attribute_types`
`class` VARCHAR(255) NOT NULL DEFAULT '',-- defines a class for an attribute_type
PRIMARY KEY (`code`),
KEY `auth_val_cat_idx` (`authorised_value_category`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `borrower_password_recovery`
`valid_until` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- a time limit on the password recovery attempt
PRIMARY KEY (`borrowernumber`),
KEY borrowernumber (borrowernumber)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `branches`
DROP TABLE IF EXISTS `branches`;
CREATE TABLE `branches` ( -- information about your libraries or branches are stored here
`branchcode` varchar(10) NOT NULL default '', -- a unique key assigned to each branch
- `branchname` mediumtext NOT NULL, -- the name of your library or branch
- `branchaddress1` mediumtext, -- the first address line of for your library or branch
- `branchaddress2` mediumtext, -- the second address line of for your library or branch
- `branchaddress3` mediumtext, -- the third address line of for your library or branch
+ `branchname` LONGTEXT NOT NULL, -- the name of your library or branch
+ `branchaddress1` LONGTEXT, -- the first address line of for your library or branch
+ `branchaddress2` LONGTEXT, -- the second address line of for your library or branch
+ `branchaddress3` LONGTEXT, -- the third address line of for your library or branch
`branchzip` varchar(25) default NULL, -- the zip or postal code for your library or branch
- `branchcity` mediumtext, -- the city or province for your library or branch
- `branchstate` mediumtext, -- the state for your library or branch
- `branchcountry` text, -- the county for your library or branch
- `branchphone` mediumtext, -- the primary phone for your library or branch
- `branchfax` mediumtext, -- the fax number for your library or branch
- `branchemail` mediumtext, -- the primary email address for your library or branch
- `branchreplyto` mediumtext, -- the email to be used as a Reply-To
- `branchreturnpath` mediumtext, -- the email to be used as Return-Path
- `branchurl` mediumtext, -- the URL for your library or branch's website
+ `branchcity` LONGTEXT, -- the city or province for your library or branch
+ `branchstate` LONGTEXT, -- the state for your library or branch
+ `branchcountry` MEDIUMTEXT, -- the county for your library or branch
+ `branchphone` LONGTEXT, -- the primary phone for your library or branch
+ `branchfax` LONGTEXT, -- the fax number for your library or branch
+ `branchemail` LONGTEXT, -- the primary email address for your library or branch
+ `branchreplyto` LONGTEXT, -- the email to be used as a Reply-To
+ `branchreturnpath` LONGTEXT, -- the email to be used as Return-Path
+ `branchurl` LONGTEXT, -- the URL for your library or branch's website
`issuing` tinyint(4) default NULL, -- unused in Koha
`branchip` varchar(15) default NULL, -- the IP address for your library or branch
`branchprinter` varchar(100) default NULL, -- unused in Koha
- `branchnotes` mediumtext, -- notes related to your library or branch
- opac_info text, -- HTML that displays in OPAC
+ `branchnotes` LONGTEXT, -- notes related to your library or branch
+ 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=utf8 COLLATE=utf8_unicode_ci;
+) 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`
`description` varchar(255) NOT NULL,
`number` bigint(20) NOT NULL,
`endnode` tinyint(4) NOT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `categories`
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` ( -- this table shows information related to Koha patron categories
`categorycode` varchar(10) NOT NULL default '', -- unique primary key used to idenfity the patron category
- `description` mediumtext, -- description of the patron category
+ `description` LONGTEXT, -- description of the patron category
`enrolmentperiod` smallint(6) default NULL, -- number of months the patron is enrolled for (will be NULL if enrolmentperioddate is set)
`enrolmentperioddate` DATE NULL DEFAULT NULL, -- date the patron is enrolled until (will be NULL if enrolmentperiod is set)
`upperagelimit` smallint(6) default NULL, -- age limit for the patron
`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=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table: collections
CREATE TABLE collections (
colId integer(11) NOT NULL auto_increment,
colTitle varchar(100) NOT NULL DEFAULT '',
- colDesc text NOT NULL,
+ colDesc MEDIUMTEXT NOT NULL,
colBranchcode varchar(10) DEFAULT NULL, -- 'branchcode for branch where item should be held.'
PRIMARY KEY (colId)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Constraints for table `collections`
colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId',
itemnumber integer(11) NOT NULL DEFAULT 0 comment 'items.itemnumber',
PRIMARY KEY (collections_tracking_id)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_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=utf8 COLLATE=utf8_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=utf8 COLLATE=utf8_unicode_ci;
+) 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,
PRIMARY KEY (`branchcode`),
CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `default_circ_rules`
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,
PRIMARY KEY (`singleton`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `cities`
`city_country` VARCHAR( 100 ) NULL DEFAULT NULL, -- name of the country
`city_zipcode` varchar(20) default NULL, -- zip or postal code
PRIMARY KEY (`cityid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `class_sort_rules`
DROP TABLE IF EXISTS `class_sort_rules`;
CREATE TABLE `class_sort_rules` (
`class_sort_rule` varchar(10) NOT NULL default '',
- `description` mediumtext,
+ `description` LONGTEXT,
`sort_routine` varchar(30) NOT NULL default '',
PRIMARY KEY (`class_sort_rule`),
UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) 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`
DROP TABLE IF EXISTS `class_sources`;
CREATE TABLE `class_sources` (
`cn_source` varchar(10) NOT NULL default '',
- `description` mediumtext,
+ `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`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ 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;
--
-- Table structure for table `currency`
`rate` float(15,5) default NULL,
`active` tinyint(1) default NULL,
`archived` tinyint(1) DEFAULT 0,
+ `p_sep_by_space` tinyint(1) DEFAULT 0,
PRIMARY KEY (`currency`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `deletedbiblio`
CREATE TABLE `deletedbiblio` ( -- stores information about bibliographic records that have been deleted
`biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
`frameworkcode` varchar(4) NOT NULL default '', -- foriegn key from the biblio_framework table to identify which framework was used in cataloging this record
- `author` mediumtext, -- statement of responsibility from MARC record (100$a in MARC21)
- `title` mediumtext, -- title (without the subtitle) from the MARC record (245$a in MARC21)
- `unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
- `notes` mediumtext, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
+ `author` LONGTEXT, -- statement of responsibility from MARC record (100$a in MARC21)
+ `title` LONGTEXT, -- title (without the subtitle) from the MARC record (245$a in MARC21)
+ `unititle` LONGTEXT, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
+ `notes` LONGTEXT, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
`serial` tinyint(1) default NULL, -- Boolean indicating whether biblio is for a serial
- `seriestitle` mediumtext,
+ `seriestitle` LONGTEXT,
`copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
`datecreated` DATE NOT NULL, -- the date this record was added to Koha
- `abstract` mediumtext, -- summary from the MARC record (520$a in MARC21)
+ `abstract` LONGTEXT, -- summary from the MARC record (520$a in MARC21)
PRIMARY KEY (`biblionumber`),
KEY `blbnoidx` (`biblionumber`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `deletedbiblioitems`
CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records that have been deleted
`biblioitemnumber` int(11) NOT NULL default 0, -- primary key, unique identifier assigned by Koha
`biblionumber` int(11) NOT NULL default 0, -- foreign key linking this table to the biblio table
- `volume` mediumtext,
- `number` mediumtext,
+ `volume` LONGTEXT,
+ `number` LONGTEXT,
`itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
- `isbn` mediumtext default NULL, -- ISBN (MARC21 020$a)
- `issn` mediumtext default NULL, -- ISSN (MARC21 022$a)
- `ean` mediumtext default NULL,
- `publicationyear` text,
+ `isbn` LONGTEXT default NULL, -- ISBN (MARC21 020$a)
+ `issn` LONGTEXT default NULL, -- ISSN (MARC21 022$a)
+ `ean` LONGTEXT default NULL,
+ `publicationyear` MEDIUMTEXT,
`publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
`volumedate` date default NULL,
- `volumedesc` text, -- volume information (MARC21 362$a)
- `collectiontitle` mediumtext default NULL,
- `collectionissn` text default NULL,
- `collectionvolume` mediumtext default NULL,
- `editionstatement` text default NULL,
- `editionresponsibility` text default NULL,
+ `volumedesc` MEDIUMTEXT, -- volume information (MARC21 362$a)
+ `collectiontitle` LONGTEXT default NULL,
+ `collectionissn` MEDIUMTEXT default NULL,
+ `collectionvolume` LONGTEXT default NULL,
+ `editionstatement` MEDIUMTEXT default NULL,
+ `editionresponsibility` MEDIUMTEXT default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`illus` varchar(255) default NULL, -- illustrations (MARC21 300$b)
`pages` varchar(255) default NULL, -- number of pages (MARC21 300$c)
- `notes` mediumtext,
+ `notes` LONGTEXT,
`size` varchar(255) default NULL, -- material size (MARC21 300$c)
`place` varchar(255) default NULL, -- publication place (MARC21 260$a)
`lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
- `url` text default NULL, -- url (MARC21 856$u)
+ `url` MEDIUMTEXT default NULL, -- url (MARC21 856$u)
`cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
`cn_class` varchar(30) default NULL,
`cn_item` varchar(10) default NULL,
KEY `itemtype_idx` (`itemtype`),
KEY `isbn` (`isbn`(255)),
KEY `ean` (`ean`(255)),
- KEY `publishercode` (`publishercode`),
+ KEY `publishercode` (`publishercode` (191)),
KEY `timestamp` (`timestamp`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `deletedborrowers`
CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrowers you have deleted
`borrowernumber` int(11) NOT NULL default 0, -- primary key, Koha assigned ID number for patrons/borrowers
`cardnumber` varchar(32) default NULL, -- unique key, library assigned ID number for patrons/borrowers
- `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname)
- `firstname` text, -- patron/borrower's first name
- `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs.
- `othernames` mediumtext, -- any other names associated with the patron/borrower
- `initials` text, -- initials for your patron/borrower
+ `surname` LONGTEXT, -- patron/borrower's last name (surname)
+ `firstname` MEDIUMTEXT, -- patron/borrower's first name
+ `title` LONGTEXT, -- patron/borrower's title, for example: Mr. or Mrs.
+ `othernames` LONGTEXT, -- any other names associated with the patron/borrower
+ `initials` MEDIUMTEXT, -- initials for your patron/borrower
`streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
`streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
- `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address
- `address2` text, -- the second address line for your patron/borrower's primary address
- `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address
- `state` text default NULL, -- the state or province for your patron/borrower's primary address
+ `address` LONGTEXT, -- the first address line for your patron/borrower's primary address
+ `address2` MEDIUMTEXT, -- the second address line for your patron/borrower's primary address
+ `city` LONGTEXT, -- the city or town for your patron/borrower's primary address
+ `state` MEDIUMTEXT default NULL, -- the state or province for your patron/borrower's primary address
`zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
- `country` text, -- the country for your patron/borrower's primary address
- `email` mediumtext, -- the primary email address for your patron/borrower's primary address
- `phone` text, -- the primary phone number for your patron/borrower's primary address
+ `country` MEDIUMTEXT, -- the country for your patron/borrower's primary address
+ `email` LONGTEXT, -- the primary email address for your patron/borrower's primary address
+ `phone` MEDIUMTEXT, -- the primary phone number for your patron/borrower's primary address
`mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
- `fax` mediumtext, -- the fax number for your patron/borrower's primary address
- `emailpro` text, -- the secondary email addres for your patron/borrower's primary address
- `phonepro` text, -- the secondary phone number for your patron/borrower's primary address
+ `fax` LONGTEXT, -- the fax number for your patron/borrower's primary address
+ `emailpro` MEDIUMTEXT, -- the secondary email addres for your patron/borrower's primary address
+ `phonepro` MEDIUMTEXT, -- the secondary phone number for your patron/borrower's primary address
`B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
`B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
`B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
- `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address
- `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address
- `B_state` text default NULL, -- the state for your patron/borrower's alternate address
+ `B_address2` MEDIUMTEXT default NULL, -- the second address line for your patron/borrower's alternate address
+ `B_city` LONGTEXT, -- the city or town for your patron/borrower's alternate address
+ `B_state` MEDIUMTEXT default NULL, -- the state for your patron/borrower's alternate address
`B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
- `B_country` text, -- the country for your patron/borrower's alternate address
- `B_email` text, -- the patron/borrower's alternate email address
- `B_phone` mediumtext, -- the patron/borrower's alternate phone number
+ `B_country` MEDIUMTEXT, -- the country for your patron/borrower's alternate address
+ `B_email` MEDIUMTEXT, -- the patron/borrower's alternate email address
+ `B_phone` LONGTEXT, -- the patron/borrower's alternate phone number
`dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD)
`branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch
`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category
`lost` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having lost their card
`debarred` date default NULL, -- until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYYY-MM-DD)
`debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of patron
- `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarantor or organization name
- `contactfirstname` text, -- used for children to include first name of guarantor
- `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarantor
+ `contactname` LONGTEXT, -- used for children and profesionals to include surname or last name of guarantor or organization name
+ `contactfirstname` MEDIUMTEXT, -- used for children to include first name of guarantor
+ `contacttitle` MEDIUMTEXT, -- used for children to include title (Mr., Mrs., etc) of guarantor
`guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarantors or organizations
- `borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client
+ `borrowernotes` LONGTEXT, -- a note on the patron/borrower's account that is only visible in the staff client
`relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarantor
`sex` varchar(1) default NULL, -- patron/borrower's gender
`password` varchar(60) default NULL, -- patron/borrower's encrypted password
`flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
`userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in
- `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
+ `opacnote` LONGTEXT, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
`contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
`sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
`sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
`altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
`altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
`altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower
- `altcontactstate` text default NULL, -- the state for the alternate contact for the patron/borrower
+ `altcontactstate` MEDIUMTEXT default NULL, -- the state for the alternate contact for the patron/borrower
`altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
- `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower
+ `altcontactcountry` MEDIUMTEXT default NULL, -- the country for the alternate contact for the patron/borrower
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SMS turned on)
`sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
`lastseen` datetime default NULL, -- last time a patron has been seen (connected at the OPAC or staff interface)
`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` text default NULL, -- persist OverDrive auth token
+ `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`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `deleteditems`
`biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
`barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
`dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
- `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e)
+ `booksellerid` LONGTEXT default NULL, -- where the item was purchased (MARC21 952$e)
`homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
`price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
`replacementprice` decimal(8,2) default NULL, -- cost the library charges to replace the item if it has been marked lost (MARC21 952$v)
`stack` tinyint(1) default NULL,
`notforloan` tinyint(1) NOT NULL default 0, -- authorized value defining why this item is not for loan (MARC21 952$7)
`damaged` tinyint(1) NOT NULL default 0, -- authorized value defining this item as damaged (MARC21 952$4)
+ `damaged_on` datetime DEFAULT NULL, -- the date and time an item was last marked as damaged, NULL if not damaged
`itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1)
`itemlost_on` datetime DEFAULT NULL, -- the date and time an item was last marked as lost, NULL if not lost
`withdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
`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` mediumtext, -- public notes on this item (MARC21 952$x)
- `itemnotes_nonpublic` mediumtext 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` mediumtext,
+ `paidfor` LONGTEXT,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
`location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c)
`permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
`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)
- `materials` text default NULL, -- materials specified (MARC21 952$3)
+ `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)
- `more_subfields_xml` longtext default NULL, -- additional 952 subfields in XML format
- `enumchron` text default NULL, -- serial enumeration/chronology for the item (MARC21 952$h)
+ `more_subfields_xml` LONGTEXT default NULL, -- additional 952 subfields in XML format
+ `enumchron` MEDIUMTEXT default NULL, -- serial enumeration/chronology for the item (MARC21 952$h)
`copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t)
`stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i)
`new_status` VARCHAR(32) DEFAULT NULL, -- 'new' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.
KEY `delholdingbranch` (`holdingbranch`),
KEY `itype_idx` (`itype`),
KEY `timestamp` (`timestamp`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `export_format`
CREATE TABLE `export_format` (
`export_format_id` int(11) NOT NULL auto_increment,
`profile` varchar(255) NOT NULL,
- `description` mediumtext NOT NULL,
- `content` mediumtext NOT NULL,
- `csv_separator` varchar(2) NOT NULL,
- `field_separator` varchar(2) NOT NULL,
- `subfield_separator` varchar(2) NOT NULL,
- `encoding` varchar(255) NOT NULL,
+ `description` LONGTEXT NOT NULL,
+ `content` LONGTEXT NOT NULL,
+ `csv_separator` varchar(2) NOT NULL DEFAULT ',',
+ `field_separator` varchar(2),
+ `subfield_separator` varchar(2),
+ `encoding` varchar(255) NOT NULL DEFAULT 'utf8',
`type` varchar(255) DEFAULT 'marc',
`used_for` varchar(255) DEFAULT 'export_records',
PRIMARY KEY (`export_format_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Used for CSV export';
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Used for CSV export';
--
-- Table structure for table `import_batches`
`batch_type` enum('batch', 'z3950', 'webservice') NOT NULL default 'batch', -- where this batch has come from
`record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio', -- type of record in the batch
`file_name` varchar(100), -- the name of the file uploaded
- `comments` mediumtext, -- any comments added when the file was uploaded
+ `comments` LONGTEXT, -- any comments added when the file was uploaded
PRIMARY KEY (`import_batch_id`),
KEY `branchcode` (`branchcode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `import_records`
`upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`import_date` DATE default NULL,
`marc` longblob NOT NULL,
- `marcxml` longtext NOT NULL,
- `marcxml_old` longtext NOT NULL,
+ `marcxml` LONGTEXT NOT NULL,
+ `marcxml_old` LONGTEXT NOT NULL,
`record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
`overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
`status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
- `import_error` mediumtext,
+ `import_error` LONGTEXT,
`encoding` varchar(40) NOT NULL default '',
`z3950random` varchar(40) default NULL,
PRIMARY KEY (`import_record_id`),
KEY `branchcode` (`branchcode`),
KEY `batch_sequence` (`import_batch_id`, `record_sequence`),
KEY `batch_id_record_type` (`import_batch_id`,`record_type`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for `import_record_matches`
CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
KEY `record_score` (`import_record_id`, `score`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `import_auths`
CONSTRAINT `import_auths_ibfk_1` FOREIGN KEY (`import_record_id`)
REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
KEY `matched_authid` (`matched_authid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `import_biblios`
KEY `matched_biblionumber` (`matched_biblionumber`),
KEY `title` (`title`),
KEY `isbn` (`isbn`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `import_items`
`itemnumber` int(11) default NULL,
`branchcode` varchar(10) default NULL,
`status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
- `marcxml` longtext NOT NULL,
- `import_error` mediumtext,
+ `marcxml` LONGTEXT NOT NULL,
+ `import_error` LONGTEXT,
PRIMARY KEY (`import_items_id`),
CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
KEY `itemnumber` (`itemnumber`),
KEY `branchcode` (`branchcode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `issuingrules`
`fine` decimal(28,6) default NULL, -- fine amount
`finedays` int(11) default NULL, -- suspension in days
`maxsuspensiondays` int(11) default NULL, -- max suspension days
+ `suspension_chargeperiod` int(11) default '1', -- how often the finedays is charged
`firstremind` int(11) default NULL, -- fine grace period
`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`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `refund_lost_item_fee_rules`
`branchcode` varchar(10) NOT NULL default '', -- the branch this rule is for (branches.branchcode)
`refund` tinyint(1) NOT NULL default 0, -- control wether to refund lost item fees on return
PRIMARY KEY (`branchcode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `items`
`biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
`barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
`dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
- `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e)
+ `booksellerid` LONGTEXT default NULL, -- where the item was purchased (MARC21 952$e)
`homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
`price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
`replacementprice` decimal(8,2) default NULL, -- cost the library charges to replace the item if it has been marked lost (MARC21 952$v)
`stack` tinyint(1) default NULL,
`notforloan` tinyint(1) NOT NULL default 0, -- authorized value defining why this item is not for loan (MARC21 952$7)
`damaged` tinyint(1) NOT NULL default 0, -- authorized value defining this item as damaged (MARC21 952$4)
+ `damaged_on` datetime DEFAULT NULL, -- the date and time an item was last marked as damaged, NULL if not damaged
`itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1)
`itemlost_on` datetime DEFAULT NULL, -- the date and time an item was last marked as lost, NULL if not lost
`withdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
`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` mediumtext, -- public notes on this item (MARC21 952$x)
- `itemnotes_nonpublic` mediumtext 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` mediumtext,
+ `paidfor` LONGTEXT,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
`location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c)
`permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
`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)
- `materials` text default NULL, -- materials specified (MARC21 952$3)
+ `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)
- `more_subfields_xml` longtext default NULL, -- additional 952 subfields in XML format
- `enumchron` text default NULL, -- serial enumeration/chronology for the item (MARC21 952$h)
+ `more_subfields_xml` LONGTEXT default NULL, -- additional 952 subfields in XML format
+ `enumchron` MEDIUMTEXT default NULL, -- serial enumeration/chronology for the item (MARC21 952$h)
`copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t)
`stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i)
`new_status` VARCHAR(32) DEFAULT NULL, -- 'new' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.
KEY `itembibnoidx` (`biblionumber`),
KEY `homebranch` (`homebranch`),
KEY `holdingbranch` (`holdingbranch`),
- KEY `itemcallnumber` (`itemcallnumber`),
+ KEY `itemcallnumber` (`itemcallnumber` (191)),
KEY `items_location` (`location`),
KEY `items_ccode` (`ccode`),
KEY `itype_idx` (`itype`),
CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
CONSTRAINT `items_ibfk_4` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `itemtypes`
DROP TABLE IF EXISTS `itemtypes`;
CREATE TABLE `itemtypes` ( -- defines the item types
itemtype varchar(10) NOT NULL default '', -- unique key, a code associated with the item type
- description mediumtext, -- a plain text explanation of 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
imageurl varchar(200) default NULL, -- URL for the item type icon
- summary text, -- information from the summary field, may include HTML
+ summary MEDIUMTEXT, -- information from the summary field, may include HTML
checkinmsg VARCHAR(255), -- message that is displayed when an item with the given item type is checked in
checkinmsgtype CHAR(16) DEFAULT 'message' NOT NULL, -- type (CSS class) for the checkinmsg, can be "alert" or "message"
sip_media_type VARCHAR(3) DEFAULT NULL, -- SIP2 protocol media type for this itemtype
searchcategory varchar(80) default NULL, -- Group this item type with others with the same value on OPAC search options
PRIMARY KEY (`itemtype`),
UNIQUE KEY `itemtype` (`itemtype`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `default_branch_item_rules`
PRIMARY KEY (`itemtype`),
CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `branchtransfers`
`frombranch` varchar(10) NOT NULL default '', -- the branch the transfer is coming from
`datearrived` datetime default NULL, -- the date the transfer arrived at its destination
`tobranch` varchar(10) NOT NULL default '', -- the branch the transfer was going to
- `comments` mediumtext, -- any comments related to the transfer
+ `comments` LONGTEXT, -- any comments related to the transfer
PRIMARY KEY (`branchtransfer_id`),
KEY `frombranch` (`frombranch`),
KEY `tobranch` (`tobranch`),
CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `branch_item_rules`
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `creator_images`
`image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
PRIMARY KEY (`image_id`),
UNIQUE KEY `image_name_index` (`image_name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `creator_layouts`
`layout_name` char(25) NOT NULL DEFAULT 'DEFAULT',
`guidebox` int(1) DEFAULT '0',
`oblique_title` int(1) DEFAULT '1',
- `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
+ `font` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'TR',
`font_size` int(4) NOT NULL DEFAULT '10',
`units` char(20) NOT NULL DEFAULT 'POINT',
`callnum_split` int(1) DEFAULT '0',
- `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
+ `text_justify` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'L',
`format_string` varchar(210) NOT NULL DEFAULT 'barcode',
- `layout_xml` text NOT NULL,
+ `layout_xml` MEDIUMTEXT NOT NULL,
`creator` char(15) NOT NULL DEFAULT 'Labels',
PRIMARY KEY (`layout_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `creator_templates`
`creator` char(15) NOT NULL DEFAULT 'Labels',
PRIMARY KEY (`template_id`),
KEY `template_profile_fk_constraint` (`profile_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `marc_subfield_structure`
DROP TABLE IF EXISTS `marc_subfield_structure`;
CREATE TABLE `marc_subfield_structure` (
`tagfield` varchar(3) NOT NULL default '',
- `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
+ `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8mb4_bin,
`liblibrarian` varchar(255) NOT NULL default '',
`libopac` varchar(255) NOT NULL default '',
`repeatable` tinyint(4) NOT NULL default 0,
`frameworkcode` varchar(4) NOT NULL default '',
`seealso` varchar(1100) default NULL,
`link` varchar(80) default NULL,
- `defaultvalue` text default NULL,
+ `defaultvalue` MEDIUMTEXT default NULL,
`maxlength` int(4) NOT NULL DEFAULT '9999',
PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
KEY `kohafield_2` (`kohafield`),
KEY `tab` (`frameworkcode`,`tab`),
KEY `kohafield` (`frameworkcode`,`kohafield`),
CONSTRAINT `marc_subfield_structure_ibfk_1` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE SET NULL ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `marc_tag_structure`
`repeatable` tinyint(4) NOT NULL default 0,
`mandatory` tinyint(4) NOT NULL default 0,
`authorised_value` varchar(10) default NULL,
+ `ind1_defaultvalue` varchar(1) NOT NULL default '',
+ `ind2_defaultvalue` varchar(1) NOT NULL default '',
`frameworkcode` varchar(4) NOT NULL default '',
PRIMARY KEY (`frameworkcode`,`tagfield`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `marc_matchers`
PRIMARY KEY (`matcher_id`),
KEY `code` (`code`),
KEY `record_type` (`record_type`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `matchpoints`
PRIMARY KEY (`matchpoint_id`),
CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
KEY `by_sequence` (`matchpoint_id`, `sequence`),
CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `matcher_component_norms`
KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `matcher_matchpoints`
REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `matchchecks`
REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `need_merge_authorities`
`id` int NOT NULL auto_increment PRIMARY KEY, -- unique id
`authid` bigint NOT NULL, -- reference to original authority record
`authid_new` bigint, -- reference to optional new authority record
- `reportxml` text, -- xml showing original reporting tag
+ `reportxml` MEDIUMTEXT, -- xml showing original reporting tag
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date and time last modified
`done` tinyint DEFAULT 0 -- indication whether merge has been executed (0=not done, 1=done, 2=in progress)
-- Note: authid and authid_new should NOT be FOREIGN keys !
-- authid may have been deleted; authid_new may be zero
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `oai_sets`
`spec` varchar(80) NOT NULL UNIQUE,
`name` varchar(80) NOT NULL,
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `oai_sets_descriptions`
`set_id` int(11) NOT NULL,
`description` varchar(255) NOT NULL,
CONSTRAINT `oai_sets_descriptions_ibfk_1` FOREIGN KEY (`set_id`) REFERENCES `oai_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `oai_sets_mappings`
`operator` varchar(8) NOT NULL default 'equal',
`marcvalue` varchar(80) NOT NULL,
CONSTRAINT `oai_sets_mappings_ibfk_1` FOREIGN KEY (`set_id`) REFERENCES `oai_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `oai_sets_biblios`
`set_id` int(11) NOT NULL,
PRIMARY KEY (`biblionumber`, `set_id`),
CONSTRAINT `oai_sets_biblios_ibfk_2` FOREIGN KEY (`set_id`) REFERENCES `oai_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `overduerules`
`debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
PRIMARY KEY (`overduerules_id`),
UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Table structure for table `pending_offline_operations`
--
cardnumber varchar(32) DEFAULT NULL,
amount decimal(28,6) DEFAULT NULL,
PRIMARY KEY (operationid)
-) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
`printername` varchar(40) NOT NULL default '',
`printqueue` varchar(20) default NULL,
`printtype` varchar(20) default NULL,
- PRIMARY KEY (`printername`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ PRIMARY KEY (`printername`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `printers_profile`
`creator` char(15) NOT NULL DEFAULT 'Labels',
PRIMARY KEY (`profile_id`),
UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `repeatable_holidays`
`day` smallint(6) default NULL, -- day of the month this closing is on
`month` smallint(6) default NULL, -- month this closing is in
`title` varchar(50) NOT NULL default '', -- title of this closing
- `description` text NOT NULL, -- description for this closing
+ `description` MEDIUMTEXT NOT NULL, -- description for this closing
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `reports_dictionary`
CREATE TABLE reports_dictionary ( -- definitions (or snippets of SQL) stored for use in reports
`id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
`name` varchar(255) default NULL, -- name for this definition
- `description` text, -- description for this definition
+ `description` MEDIUMTEXT, -- description for this definition
`date_created` datetime default NULL, -- date and time this definition was created
`date_modified` datetime default NULL, -- date and time this definition was last modified
- `saved_sql` text, -- SQL snippet for us in reports
+ `saved_sql` MEDIUMTEXT, -- SQL snippet for us in reports
report_area varchar(6) DEFAULT NULL, -- Koha module this definition is for Circulation, Catalog, Patrons, Acquistions, Accounts)
PRIMARY KEY (id),
KEY dictionary_area_idx (report_area)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `saved_sql`
`borrowernumber` int(11) default NULL, -- the staff member who created this report (borrowers.borrowernumber)
`date_created` datetime default NULL, -- the date this report was created
`last_modified` datetime default NULL, -- the date this report was last edited
- `savedsql` text, -- the SQL for this report
+ `savedsql` MEDIUMTEXT, -- the SQL for this report
`last_run` datetime default NULL,
`report_name` varchar(255) NOT NULL default '', -- the name of this report
`type` varchar(255) default NULL, -- always 1 for tabular
- `notes` text, -- the notes or description given to this report
+ `notes` MEDIUMTEXT, -- the notes or description given to this report
`cache_expiry` int NOT NULL default 300,
`public` boolean NOT NULL default FALSE,
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`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
CREATE TABLE saved_reports (
`id` int(11) NOT NULL auto_increment,
`report_id` int(11) default NULL,
- `report` longtext,
+ `report` LONGTEXT,
`date_run` datetime default NULL,
- PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'search_field'
`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',
+ `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`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ UNIQUE KEY (`name` (191))
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `search_history`
`userid` int(11) NOT NULL, -- the patron who performed the search (borrowers.borrowernumber)
`sessionid` varchar(32) NOT NULL, -- a system generated session id
`query_desc` varchar(255) NOT NULL, -- the search that was performed
- `query_cgi` text NOT NULL, -- the string to append to the search url to rerun the search
+ `query_cgi` MEDIUMTEXT NOT NULL, -- the string to append to the search url to rerun the search
`type` varchar(16) NOT NULL DEFAULT 'biblio', -- search type, must be 'biblio' or 'authority'
`total` int(11) NOT NULL, -- the total of results found
`time` timestamp NOT NULL default CURRENT_TIMESTAMP, -- the date and time the search was run
KEY `userid` (`userid`),
KEY `sessionid` (`sessionid`),
- PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Opac search history results';
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Opac search history results';
--
-- Table structure for table 'search_marc_map'
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, marc_field, marc_type),
+ UNIQUE key `index_name` (`index_name`, `marc_field` (191), `marc_type`),
INDEX (`index_name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'search_marc_to_field'
PRIMARY KEY(search_marc_map_id, search_field_id),
FOREIGN KEY(search_marc_map_id) REFERENCES search_marc_map(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(search_field_id) REFERENCES search_field(id) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `serial`
`serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information
`status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions)
`planneddate` date default NULL, -- date expected
- `notes` text, -- notes
+ `notes` MEDIUMTEXT, -- notes
`publisheddate` date default NULL, -- date published
publisheddatetext varchar(100) default NULL, -- date published (descriptive)
`claimdate` date default NULL, -- date claimed
claims_count int(11) default 0, -- number of claims made related to this issue
- `routingnotes` text, -- notes from the routing list
- PRIMARY KEY (`serialid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ `routingnotes` MEDIUMTEXT, -- notes from the routing list
+ PRIMARY KEY (`serialid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `sessions`
DROP TABLE IF EXISTS sessions;
CREATE TABLE sessions (
`id` varchar(32) NOT NULL,
- `a_session` mediumtext NOT NULL,
+ `a_session` LONGTEXT NOT NULL,
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `sms_providers`
`name` varchar(255) NOT NULL,
`domain` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
- UNIQUE KEY `name` (`name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ UNIQUE KEY `name` (`name` (191))
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `borrowers`
CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members
`borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers
`cardnumber` varchar(32) default NULL, -- unique key, library assigned ID number for patrons/borrowers
- `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname)
- `firstname` text, -- patron/borrower's first name
- `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs.
- `othernames` mediumtext, -- any other names associated with the patron/borrower
- `initials` text, -- initials for your patron/borrower
+ `surname` LONGTEXT, -- patron/borrower's last name (surname)
+ `firstname` MEDIUMTEXT, -- patron/borrower's first name
+ `title` LONGTEXT, -- patron/borrower's title, for example: Mr. or Mrs.
+ `othernames` LONGTEXT, -- any other names associated with the patron/borrower
+ `initials` MEDIUMTEXT, -- initials for your patron/borrower
`streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
`streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
- `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address
- `address2` text, -- the second address line for your patron/borrower's primary address
- `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address
- `state` text default NULL, -- the state or province for your patron/borrower's primary address
+ `address` LONGTEXT, -- the first address line for your patron/borrower's primary address
+ `address2` MEDIUMTEXT, -- the second address line for your patron/borrower's primary address
+ `city` LONGTEXT, -- the city or town for your patron/borrower's primary address
+ `state` MEDIUMTEXT default NULL, -- the state or province for your patron/borrower's primary address
`zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
- `country` text, -- the country for your patron/borrower's primary address
- `email` mediumtext, -- the primary email address for your patron/borrower's primary address
- `phone` text, -- the primary phone number for your patron/borrower's primary address
+ `country` MEDIUMTEXT, -- the country for your patron/borrower's primary address
+ `email` LONGTEXT, -- the primary email address for your patron/borrower's primary address
+ `phone` MEDIUMTEXT, -- the primary phone number for your patron/borrower's primary address
`mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
- `fax` mediumtext, -- the fax number for your patron/borrower's primary address
- `emailpro` text, -- the secondary email addres for your patron/borrower's primary address
- `phonepro` text, -- the secondary phone number for your patron/borrower's primary address
+ `fax` LONGTEXT, -- the fax number for your patron/borrower's primary address
+ `emailpro` MEDIUMTEXT, -- the secondary email addres for your patron/borrower's primary address
+ `phonepro` MEDIUMTEXT, -- the secondary phone number for your patron/borrower's primary address
`B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
`B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
`B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
- `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address
- `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address
- `B_state` text default NULL, -- the state for your patron/borrower's alternate address
+ `B_address2` MEDIUMTEXT default NULL, -- the second address line for your patron/borrower's alternate address
+ `B_city` LONGTEXT, -- the city or town for your patron/borrower's alternate address
+ `B_state` MEDIUMTEXT default NULL, -- the state for your patron/borrower's alternate address
`B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
- `B_country` text, -- the country for your patron/borrower's alternate address
- `B_email` text, -- the patron/borrower's alternate email address
- `B_phone` mediumtext, -- the patron/borrower's alternate phone number
+ `B_country` MEDIUMTEXT, -- the country for your patron/borrower's alternate address
+ `B_email` MEDIUMTEXT, -- the patron/borrower's alternate email address
+ `B_phone` LONGTEXT, -- the patron/borrower's alternate phone number
`dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD)
`branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch
`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category
`lost` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having lost their card
`debarred` date default NULL, -- until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYYY-MM-DD)
`debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron
- `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarantor or organization name
- `contactfirstname` text, -- used for children to include first name of guarantor
- `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarantor
+ `contactname` LONGTEXT, -- used for children and profesionals to include surname or last name of guarantor or organization name
+ `contactfirstname` MEDIUMTEXT, -- used for children to include first name of guarantor
+ `contacttitle` MEDIUMTEXT, -- used for children to include title (Mr., Mrs., etc) of guarantor
`guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarantors or organizations
- `borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client
+ `borrowernotes` LONGTEXT, -- a note on the patron/borrower's account that is only visible in the staff client
`relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarantor
`sex` varchar(1) default NULL, -- patron/borrower's gender
`password` varchar(60) default NULL, -- patron/borrower's Bcrypt encrypted password
`flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
`userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in
- `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
+ `opacnote` LONGTEXT, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
`contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
`sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
`sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
`altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
`altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
`altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower
- `altcontactstate` text default NULL, -- the state for the alternate contact for the patron/borrower
+ `altcontactstate` MEDIUMTEXT default NULL, -- the state for the alternate contact for the patron/borrower
`altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
- `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower
+ `altcontactcountry` MEDIUMTEXT default NULL, -- the country for the alternate contact for the patron/borrower
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SMS turned on)
`sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
`lastseen` datetime default NULL, -- last time a patron has been seen (connected at the OPAC or staff interface)
`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` text default NULL, -- persist OverDrive auth token
+ `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`),
KEY `branchcode` (`branchcode`),
UNIQUE KEY `userid` (`userid`),
KEY `guarantorid` (`guarantorid`),
- KEY `surname_idx` (`surname`(255)),
- KEY `firstname_idx` (`firstname`(255)),
- KEY `othernames_idx` (`othernames`(255)),
+ KEY `surname_idx` (`surname` (191)),
+ KEY `firstname_idx` (`firstname` (191)),
+ KEY `othernames_idx` (`othernames` (191)),
KEY `sms_provider_id` (`sms_provider_id`),
CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`),
CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `borrower_attributes`
`code` varchar(10) NOT NULL, -- foreign key from the borrower_attribute_types table, defines which custom field this value was entered for
`attribute` varchar(255) default NULL, -- custom patron field value
KEY `borrowernumber` (`borrowernumber`),
- KEY `code_attribute` (`code`, `attribute`),
+ KEY `code_attribute` (`code`, `attribute` (191)),
CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `borrower_debarments`
borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted
expiration date DEFAULT NULL, -- expiration date of the restriction
`type` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') NOT NULL DEFAULT 'MANUAL', -- type of restriction
- `comment` text, -- comments about the restriction
+ `comment` MEDIUMTEXT, -- comments about the restriction
manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added
updated timestamp NULL DEFAULT NULL, -- date the restriction was updated
KEY borrowernumber (borrowernumber),
CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_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
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+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;
--
-- Table structure for table `issues`
`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 utf8_unicode_ci DEFAULT NULL, -- automatic renewal error
+ `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
`issuedate` datetime default NULL, -- date the item was checked out or issued
`onsite_checkout` int(1) NOT NULL default 0, -- in house use flag
- `note` mediumtext default NULL, -- issue note text
+ `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`),
KEY `bordate` (`borrowernumber`,`timestamp`),
CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `old_issues`
`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 utf8_unicode_ci DEFAULT NULL, -- automatic renewal error
+ `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
`issuedate` datetime default NULL, -- date the item was checked out or issued
`onsite_checkout` int(1) NOT NULL default 0, -- in house use flag
- `note` mediumtext default NULL, -- issue note text
+ `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`),
ON DELETE SET NULL ON UPDATE SET NULL,
CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
ON DELETE SET NULL ON UPDATE SET NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `items_last_borrower`
KEY `borrowernumber` (`borrowernumber`),
CONSTRAINT `items_last_borrower_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `items_last_borrower_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `creator_batches`
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,
CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `opac_news`
`idnew` int(10) unsigned NOT NULL auto_increment, -- unique identifier for the news article
`branchcode` varchar(10) default NULL, -- branch code users to create branch specific news, NULL is every branch.
`title` varchar(250) NOT NULL default '', -- title of the news article
- `content` text NOT NULL, -- the body of your news article
+ `content` MEDIUMTEXT NOT NULL, -- the body of your news article
`lang` varchar(25) NOT NULL default '', -- location for the article (koha is the staff client, slip is the circulation receipt and language codes are for the opac)
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- pulibcation date and time
`expirationdate` date default NULL, -- date the article is set to expire or no longer be visible
CONSTRAINT `borrowernumber_fk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT opac_news_branchcode_ibfk FOREIGN KEY (branchcode) REFERENCES branches (branchcode)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `patronimage`
`imagefile` mediumblob NOT NULL, -- the image
PRIMARY KEY (`borrowernumber`),
CONSTRAINT `patronimage_fk1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `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
`reminderdate` date default NULL, -- currently unused
`cancellationdate` date default NULL, -- the date this hold was cancelled
- `reservenotes` mediumtext, -- notes related to this hold
+ `reservenotes` LONGTEXT, -- notes related to this hold
`priority` smallint(6) default NULL, -- where in the queue the patron sits
`found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated
`itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with
`waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
`expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date)
- `lowestPriority` tinyint(1) NOT NULL,
+ `lowestPriority` tinyint(1) NOT NULL DEFAULT 0,
`suspend` BOOLEAN NOT NULL DEFAULT 0,
`suspend_until` DATETIME NULL DEFAULT NULL,
`itemtype` VARCHAR(10) NULL DEFAULT NULL, -- If record level hold, the optional itemtype of the item the patron is requesting
CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `reserves_ibfk_5` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `old_reserves`
`notificationdate` date default NULL, -- currently unused
`reminderdate` date default NULL, -- currently unused
`cancellationdate` date default NULL, -- the date this hold was cancelled
- `reservenotes` mediumtext, -- notes related to this hold
+ `reservenotes` LONGTEXT, -- notes related to this hold
`priority` smallint(6) default NULL, -- where in the queue the patron sits
`found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated
`itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with
`waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
`expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date)
- `lowestPriority` tinyint(1) NOT NULL, -- has this hold been pinned to the lowest priority in the holds queue (1 for yes, 0 for no)
+ `lowestPriority` tinyint(1) NOT NULL DEFAULT 0, -- has this hold been pinned to the lowest priority in the holds queue (1 for yes, 0 for no)
`suspend` BOOLEAN NOT NULL DEFAULT 0, -- in this hold suspended (1 for yes, 0 for no)
`suspend_until` DATETIME NULL DEFAULT NULL, -- the date this hold is suspended until (NULL for infinitely)
`itemtype` VARCHAR(10) NULL DEFAULT NULL, -- If record level hold, the optional itemtype of the item the patron is requesting
ON DELETE SET NULL ON UPDATE SET NULL,
CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
ON DELETE SET NULL ON UPDATE SET NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `reviews`
`reviewid` int(11) NOT NULL auto_increment, -- unique identifier for this comment
`borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron left this comment
`biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bibliographic record this comment is for
- `review` text, -- the body of the comment
+ `review` MEDIUMTEXT, -- the body of the comment
`approved` tinyint(4) default 0, -- whether this comment has been approved by a librarian (1 for yes, 0 for no)
`datereviewed` datetime default NULL, -- the date the comment was left
PRIMARY KEY (`reviewid`),
CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `special_holidays`
`year` smallint(6) NOT NULL default 0, -- year this closing is in
`isexception` smallint(1) NOT NULL default 1, -- is this a holiday exception to a repeatable holiday (1 for yes, 0 for no)
`title` varchar(50) NOT NULL default '', -- title for this closing
- `description` text NOT NULL, -- description of this closing
+ `description` MEDIUMTEXT NOT NULL, -- description of this closing
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `statistics`
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*)
- `other` mediumtext, -- used by SIP
+ `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
`itemtype` varchar(10) default NULL, -- foreign key from the itemtypes table, links transaction to a specific item type
`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`),
KEY `borrowernumber_idx` (`borrowernumber`),
KEY `associatedborrower_idx` (`associatedborrower`),
KEY `ccode_idx` (`ccode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table subscription_frequencies
DROP TABLE IF EXISTS subscription_frequencies;
CREATE TABLE subscription_frequencies (
id INTEGER NOT NULL AUTO_INCREMENT,
- description TEXT NOT NULL,
+ description MEDIUMTEXT NOT NULL,
displayorder INT DEFAULT NULL,
unit ENUM('day','week','month','year') DEFAULT NULL,
unitsperissue INTEGER NOT NULL DEFAULT '1',
issuesperunit INTEGER NOT NULL DEFAULT '1',
PRIMARY KEY (id)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table subscription_numberpatterns
id INTEGER NOT NULL AUTO_INCREMENT,
label VARCHAR(255) NOT NULL,
displayorder INTEGER DEFAULT NULL,
- description TEXT NOT NULL,
+ description MEDIUMTEXT NOT NULL,
numberingmethod VARCHAR(255) NOT NULL,
label1 VARCHAR(255) DEFAULT NULL,
add1 INTEGER DEFAULT NULL,
setto3 INTEGER DEFAULT NULL,
numbering3 VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `subscription`
`numberlength` int(11) default 0, -- subscription length in weeks (will not be filled in if monthlength or weeklength is set)
`periodicity` integer default null, -- frequency type links to subscription_frequencies.id
countissuesperunit INTEGER NOT NULL DEFAULT 1,
- `notes` mediumtext, -- notes
+ `notes` LONGTEXT, -- notes
`status` varchar(100) NOT NULL default '', -- status of this subscription
`lastvalue1` int(11) default NULL,
`innerloop1` int(11) default 0,
`innerloop3` int(11) default 0,
`firstacquidate` date default NULL, -- first issue received date
`manualhistory` tinyint(1) NOT NULL default 0, -- yes or no to managing the history manually
- `irregularity` text, -- any irregularities in the subscription
+ `irregularity` MEDIUMTEXT, -- any irregularities in the subscription
skip_serialseq BOOLEAN NOT NULL DEFAULT 0,
`letter` varchar(20) default NULL,
`numberpattern` integer default null, -- the numbering pattern used links to subscription_numberpatterns.id
locale VARCHAR(80) DEFAULT NULL, -- for foreign language subscriptions to display months, seasons, etc correctly
- `distributedto` text,
- `internalnotes` longtext,
- `callnumber` text, -- default call number
+ `distributedto` MEDIUMTEXT,
+ `internalnotes` LONGTEXT,
+ `callnumber` MEDIUMTEXT, -- default call number
`location` varchar(80) NULL default '', -- default shelving location (items.location)
`branchcode` varchar(10) NOT NULL default '', -- default branches (items.homebranch)
`lastbranch` varchar(10),
`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=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `subscriptionhistory`
`subscriptionid` int(11) NOT NULL default 0,
`histstartdate` date default NULL,
`histenddate` date default NULL,
- `missinglist` longtext NOT NULL,
- `recievedlist` longtext NOT NULL,
+ `missinglist` LONGTEXT NOT NULL,
+ `recievedlist` LONGTEXT NOT NULL,
`opacnote` varchar(150) NOT NULL default '',
`librariannote` varchar(150) NOT NULL default '',
PRIMARY KEY (`subscriptionid`),
KEY `biblionumber` (`biblionumber`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `subscriptionroutinglist`
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `systempreferences`
DROP TABLE IF EXISTS `systempreferences`;
CREATE TABLE `systempreferences` ( -- global system preferences
`variable` varchar(50) NOT NULL default '', -- system preference name
- `value` text, -- system preference values
- `options` mediumtext, -- options for multiple choice system preferences
- `explanation` text, -- descriptive text for the system preference
+ `value` MEDIUMTEXT, -- system preference values
+ `options` LONGTEXT, -- options for multiple choice system preferences
+ `explanation` MEDIUMTEXT, -- descriptive text for the system preference
`type` varchar(20) default NULL, -- type of question this preference asks (multiple choice, plain text, yes or no, etc)
PRIMARY KEY (`variable`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `tags`
CREATE TABLE `tags` (
`entry` varchar(255) NOT NULL default '',
`weight` bigint(20) NOT NULL default 0,
- PRIMARY KEY (`entry`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ PRIMARY KEY (`entry` (191))
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `tags_all`
`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`),
REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `tags_approval`
DROP TABLE IF EXISTS `tags_approval`;
CREATE TABLE `tags_approval` ( -- approved tags
- `term` varchar(255) 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)
KEY `tags_approval_borrowers_fk_1` (`approved_by`),
CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `tags_index`
DROP TABLE IF EXISTS `tags_index`;
CREATE TABLE `tags_index` ( -- a weighted list of all tags and where they are used
- `term` varchar(255) 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`),
REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `userflags`
`flagdesc` varchar(255) default NULL,
`defaulton` int(11) default NULL,
PRIMARY KEY (`bit`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `virtualshelves`
`allow_change_from_others` tinyint(1) default 0, -- can others change contents?
PRIMARY KEY (`shelfnumber`),
CONSTRAINT `virtualshelves_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in Members.pm
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `virtualshelfcontents`
CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `shelfcontents_ibfk_3` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in Members.pm
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `virtualshelfshares`
`sharedate` datetime, -- date of invitation or acceptance of invitation
CONSTRAINT `virtualshelfshares_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `virtualshelfshares_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in Members.pm
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `z3950servers`
`db` varchar(255) default NULL, -- target's database name
`userid` varchar(255) default NULL, -- username needed to log in to target
`password` varchar(255) default NULL, -- password needed to log in to target
- `servername` mediumtext NOT NULL, -- name given to the target by the library
+ `servername` LONGTEXT NOT NULL, -- name given to the target by the library
`checked` smallint(6) default NULL, -- whether this target is checked by default (1 for yes, 0 for no)
`rank` int(11) default NULL, -- where this target appears in the list of targets
`syntax` varchar(80) default NULL, -- marc format provided by this target
`timeout` int(11) NOT NULL DEFAULT '0', -- number of seconds before Koha stops trying to access this server
`servertype` enum('zed','sru') NOT NULL default 'zed', -- zed means z39.50 server
- `encoding` text default NULL, -- characters encoding provided by this target
+ `encoding` MEDIUMTEXT default NULL, -- characters encoding provided by this target
`recordtype` enum('authority','biblio') NOT NULL default 'biblio', -- server contains bibliographic or authority records
`sru_options` varchar(255) default NULL, -- options like sru=get, sru_version=1.1; will be passed to the server via ZOOM
- `sru_fields` mediumtext default NULL, -- contains the mapping between the Z3950 search fields and the specific SRU server indexes
- `add_xslt` mediumtext default NULL, -- zero or more paths to XSLT files to be processed on the search results
+ `sru_fields` LONGTEXT default NULL, -- contains the mapping between the Z3950 search fields and the specific SRU server indexes
+ `add_xslt` LONGTEXT default NULL, -- zero or more paths to XSLT files to be processed on the search results
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `zebraqueue`
`time` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_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=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `language_subtag_registry`
id int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
KEY `subtag` (`subtag`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `language_rfc4646_to_iso639`
id int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
KEY `rfc4646_subtag` (`rfc4646_subtag`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `language_descriptions`
PRIMARY KEY (`id`),
KEY `lang` (`lang`),
KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `language_script_bidi`
rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
bidi varchar(3), -- rtl ltr
KEY `rfc4646_subtag` (`rfc4646_subtag`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `language_script_mapping`
language_subtag varchar(25),
script_subtag varchar(25),
KEY `language_subtag` (`language_subtag`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `permissions`
PRIMARY KEY (`module_bit`, `code`),
CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `serialitems`
KEY `serialitems_sfk_1` (`serialid`),
CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `user_permissions`
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `tmp_holdsqueue`
`biblionumber` int(11) default NULL,
`itemnumber` int(11) default NULL,
`barcode` varchar(20) default NULL,
- `surname` mediumtext NOT NULL,
- `firstname` text,
- `phone` text,
+ `surname` LONGTEXT NOT NULL,
+ `firstname` MEDIUMTEXT,
+ `phone` MEDIUMTEXT,
`borrowernumber` int(11) NOT NULL,
`cardnumber` varchar(32) default NULL,
`reservedate` date default NULL,
- `title` mediumtext,
+ `title` LONGTEXT,
`itemcallnumber` varchar(255) default NULL,
`holdingbranch` varchar(10) default NULL,
`pickbranch` varchar(10) default NULL,
- `notes` text,
+ `notes` MEDIUMTEXT,
`item_level_request` tinyint(4) NOT NULL default 0
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `message_transport_types`
CREATE TABLE `message_transport_types` (
`message_transport_type` varchar(20) NOT NULL,
PRIMARY KEY (`message_transport_type`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `message_queue`
CREATE TABLE `message_queue` (
`message_id` int(11) NOT NULL auto_increment,
`borrowernumber` int(11) default NULL,
- `subject` text,
- `content` text,
- `metadata` text DEFAULT NULL,
+ `subject` MEDIUMTEXT,
+ `content` MEDIUMTEXT,
+ `metadata` MEDIUMTEXT DEFAULT NULL,
`letter_code` varchar(64) DEFAULT NULL,
`message_transport_type` varchar(20) NOT NULL,
`status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
`time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
- `to_address` mediumtext,
- `from_address` mediumtext,
- `content_type` text,
+ `to_address` LONGTEXT,
+ `from_address` LONGTEXT,
+ `content_type` MEDIUMTEXT,
PRIMARY KEY `message_id` (`message_id`),
KEY `borrowernumber` (`borrowernumber`),
KEY `message_transport_type` (`message_transport_type`),
CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `letter`
`name` varchar(100) NOT NULL default '', -- plain text name for this notice or slip
`is_html` tinyint(1) default 0, -- does this notice or slip use HTML (1 for yes, 0 for no)
`title` varchar(200) NOT NULL default '', -- subject line of the notice
- `content` text, -- body text for the notice or slip
+ `content` MEDIUMTEXT, -- body text for the notice or slip
`message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice
`lang` varchar(25) NOT NULL DEFAULT 'default', -- lang of the notice
PRIMARY KEY (`module`,`code`, `branchcode`, `message_transport_type`, `lang`),
CONSTRAINT `message_transport_type_fk` FOREIGN KEY (`message_transport_type`)
REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `overduerules_transport_types`
PRIMARY KEY (id),
CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `message_attributes`
`takes_days` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`message_attribute_id`),
UNIQUE KEY `message_name` (`message_name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `message_transports`
KEY `letter_module` (`letter_module`,`letter_code`),
CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `borrower_files`
PRIMARY KEY (`file_id`),
KEY `borrowernumber` (`borrowernumber`),
CONSTRAINT borrower_files_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `borrower_message_preferences`
CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `borrower_message_transport_preferences`
KEY `message_transport_type` (`message_transport_type`),
CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_message_preference_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `borrower_message_transport_preferences_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for the 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=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `item_circulation_alert_preferences`
`notification` varchar(16) NOT NULL,
PRIMARY KEY (`id`),
KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `messages`
`borrowernumber` int(11) NOT NULL, -- foreign key linking this message to the borrowers table
`branchcode` varchar(10) default NULL, -- foreign key linking the message to the branches table
`message_type` varchar(1) NOT NULL, -- whether the message is for the librarians (L) or the patron (B)
- `message` text NOT NULL, -- the text of the message
+ `message` MEDIUMTEXT NOT NULL, -- the text of the message
`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
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ 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;
--
-- Table structure for table `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,
- `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` mediumtext,
- `dispute` mediumtext,
- `accounttype` varchar(5) default NULL,
+ `description` LONGTEXT,
+ `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` text NULL default NULL,
- `manager_id` int(11) NULL,
+ `note` MEDIUMTEXT NULL default 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
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ 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;
--
-- Table structure for table `account_offset_types`
CREATE TABLE `account_offset_types` (
`type` varchar(16) NOT NULL, -- The type of offset this is
PRIMARY KEY (`type`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `account_offsets`
CONSTRAINT `account_offsets_ibfk_p` FOREIGN KEY (`credit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `account_offsets_ibfk_f` FOREIGN KEY (`debit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `account_offsets_ibfk_t` FOREIGN KEY (`type`) REFERENCES `account_offset_types` (`type`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `action_logs`
`action_id` int(11) NOT NULL auto_increment, -- unique identifier for each action
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time the action took place
`user` int(11) NOT NULL default 0, -- the staff member who performed the action (borrowers.borrowernumber)
- `module` text, -- the module this action was taken against
- `action` text, -- the action (includes things like DELETED, ADDED, MODIFY, etc)
+ `module` MEDIUMTEXT, -- the module this action was taken against
+ `action` MEDIUMTEXT, -- the action (includes things like DELETED, ADDED, MODIFY, etc)
`object` int(11) default NULL, -- the object that the action was taken against (could be a borrowernumber, itemnumber, etc)
- `info` text, -- information about the action (usually includes SQL statement)
+ `info` MEDIUMTEXT, -- information about the action (usually includes SQL statement)
`interface` VARCHAR(30) DEFAULT NULL, -- the context this action was taken in
PRIMARY KEY (`action_id`),
KEY `timestamp_idx` (`timestamp`),
KEY `object_idx` (`object`),
KEY `info_idx` (`info`(255)),
KEY `interface` (`interface`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `alert`
PRIMARY KEY (`alertid`),
KEY `borrowernumber` (`borrowernumber`),
KEY `type` (`type`,`externalid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `aqbooksellers`
DROP TABLE IF EXISTS `aqbooksellers`;
CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquisitions
`id` int(11) NOT NULL auto_increment, -- primary key and unique identifier assigned by Koha
- `name` mediumtext NOT NULL, -- vendor name
- `address1` mediumtext, -- first line of vendor physical address
- `address2` mediumtext, -- second line of vendor physical address
- `address3` mediumtext, -- third line of vendor physical address
- `address4` mediumtext, -- fourth line of vendor physical address
+ `name` LONGTEXT NOT NULL, -- vendor name
+ `address1` LONGTEXT, -- first line of vendor physical address
+ `address2` LONGTEXT, -- second line of vendor physical address
+ `address3` LONGTEXT, -- third line of vendor physical address
+ `address4` LONGTEXT, -- fourth line of vendor physical address
`phone` varchar(30) default NULL, -- vendor phone number
- `accountnumber` mediumtext, -- unused in Koha
- `othersupplier` mediumtext, -- unused in Koha
+ `accountnumber` LONGTEXT, -- unused in Koha
+ `othersupplier` LONGTEXT, -- unused in Koha
`currency` varchar(10) NOT NULL default '', -- unused in Koha
- `booksellerfax` mediumtext, -- vendor fax number
- `notes` mediumtext, -- order notes
- `bookselleremail` mediumtext, -- vendor email
- `booksellerurl` mediumtext, -- unused in Koha
- `postal` mediumtext, -- vendor postal address (all lines)
+ `booksellerfax` LONGTEXT, -- vendor fax number
+ `notes` LONGTEXT, -- order notes
+ `bookselleremail` LONGTEXT, -- vendor email
+ `booksellerurl` LONGTEXT, -- unused in Koha
+ `postal` LONGTEXT, -- vendor postal address (all lines)
`url` varchar(255) default NULL, -- vendor web address
`active` tinyint(4) default NULL, -- is this vendor active (1 for yes, 0 for no)
`listprice` varchar(10) default NULL, -- currency code for list prices
KEY `name` (`name`(255)),
CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `aqbasketgroups`
`closed` tinyint(1) default NULL,
`booksellerid` int(11) NOT NULL,
`deliveryplace` varchar(10) default NULL,
- `freedeliveryplace` text default NULL,
+ `freedeliveryplace` MEDIUMTEXT default NULL,
`deliverycomment` varchar(255) default NULL,
`billingplace` varchar(10) default NULL,
PRIMARY KEY (`id`),
KEY `booksellerid` (`booksellerid`),
CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `aqbudgets`
`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_notes` mediumtext, -- notes related to this fund
+ `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)
`sort1_authcat` varchar(80) default NULL, -- statistical category for this fund
KEY `budget_branchcode` (`budget_branchcode`),
KEY `budget_period_id` (`budget_period_id`),
KEY `budget_owner_id` (`budget_owner_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table aqbudgetborrowers
CONSTRAINT aqbudgetborrowers_ibfk_2 FOREIGN KEY (borrowernumber)
REFERENCES borrowers (borrowernumber)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `aqbudgetperiods`
`budget_period_startdate` date NOT NULL, -- date when the budget starts
`budget_period_enddate` date NOT NULL, -- date when the budget ends
`budget_period_active` tinyint(1) default '0', -- whether this budget is active or not (1 for yes, 0 for no)
- `budget_period_description` mediumtext, -- description assigned to this budget
+ `budget_period_description` LONGTEXT, -- description assigned to this budget
`budget_period_total` decimal(28,6), -- total amount available in this budget
`budget_period_locked` tinyint(1) default NULL, -- whether this budget is locked or not (1 for yes, 0 for no)
`sort1_authcat` varchar(10) default NULL, -- statistical category for this budget
`sort2_authcat` varchar(10) default NULL, -- second statistical category for this budget
PRIMARY KEY (`budget_period_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `aqbudgets_planning`
PRIMARY KEY (`plan_id`),
KEY `budget_period_id` (`budget_period_id`),
CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'aqcontacts'
altphone varchar(100) default NULL, -- contact's alternate phone number
fax varchar(100) default NULL, -- contact's fax number
email varchar(100) default NULL, -- contact's email address
- notes mediumtext, -- notes related to the contact
+ notes LONGTEXT, -- notes related to the contact
orderacquisition BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive acquisition orders
claimacquisition BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive acquisitions claims
claimissues BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive serial claims
PRIMARY KEY (id),
CONSTRAINT booksellerid_aqcontacts_fk FOREIGN KEY (booksellerid)
REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=1;
--
-- Table structure for table 'aqcontract'
`contractstartdate` date default NULL,
`contractenddate` date default NULL,
`contractname` varchar(50) default NULL,
- `contractdescription` mediumtext,
+ `contractdescription` LONGTEXT,
`booksellerid` int(11) not NULL,
PRIMARY KEY (`contractnumber`),
CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=1 ;
--
-- Table structure for table `aqbasket`
CREATE TABLE `aqbasket` ( -- stores data about baskets in acquisitions
`basketno` int(11) NOT NULL auto_increment, -- primary key, Koha defined number
`basketname` varchar(50) default NULL, -- name given to the basket at creation
- `note` mediumtext, -- the internal note added at basket creation
- `booksellernote` mediumtext, -- the vendor note added at basket creation
+ `note` LONGTEXT, -- the internal note added at basket creation
+ `booksellernote` LONGTEXT, -- the vendor note added at basket creation
`contractnumber` int(11), -- links this basket to the aqcontract table (aqcontract.contractnumber)
`creationdate` date default NULL, -- the date the basket was created
`closedate` date default NULL, -- the date the basket was closed
`booksellerid` int(11) NOT NULL default 1, -- the Koha assigned ID for the vendor (aqbooksellers.id)
`authorisedby` varchar(10) default NULL, -- the borrowernumber of the person who created the basket
- `booksellerinvoicenumber` mediumtext, -- appears to always be NULL
+ `booksellerinvoicenumber` LONGTEXT, -- appears to always be NULL
`basketgroupid` int(11), -- links this basket to its group (aqbasketgroups.id)
`deliveryplace` varchar(10) default NULL, -- basket delivery place
`billingplace` varchar(10) default NULL, -- basket billing place
CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE,
CONSTRAINT aqbasket_ibfk_4 FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE SET NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table aqbasketusers
PRIMARY KEY (basketno,borrowernumber),
CONSTRAINT aqbasketusers_ibfk_1 FOREIGN KEY (basketno) REFERENCES aqbasket (basketno) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT aqbasketusers_ibfk_2 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON UPDATE CASCADE ON DELETE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `suggestions`
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
rejectedby INT(11) default NULL, -- borrowernumber for the librarian who rejected the suggestion, foreign key linking to the borrowers table
rejecteddate date default NULL, -- date the suggestion was marked as rejected
`STATUS` varchar(10) NOT NULL default '', -- suggestion status (ASKED, CHECKED, ACCEPTED, or REJECTED)
- `note` mediumtext, -- note entered on the suggestion
+ `note` LONGTEXT, -- note entered on the suggestion
`author` varchar(80) default NULL, -- author of the suggested item
`title` varchar(255) default NULL, -- title of the suggested item
`copyrightdate` smallint(6) default NULL, -- copyright date of the suggested item
`place` varchar(255) default NULL, -- publication place of the suggested item
`isbn` varchar(30) default NULL, -- isbn of the suggested item
`biblionumber` int(11) default NULL, -- foreign key linking the suggestion to the biblio table after the suggestion has been ordered
- `reason` text, -- reason for accepting or rejecting the suggestion
- `patronreason` text, -- reason for making the suggestion
+ `reason` MEDIUMTEXT, -- reason for accepting or rejecting the suggestion
+ `patronreason` MEDIUMTEXT, -- reason for making the suggestion
budgetid INT(11), -- foreign key linking the suggested budget to the aqbudgets table
branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table
- collectiontitle text default NULL, -- collection name for the suggested item
+ collectiontitle MEDIUMTEXT default NULL, -- collection name for the suggested item
itemtype VARCHAR(30) default NULL, -- suggested item type
quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased
currency VARCHAR(10) default NULL, -- suggested currency for the suggested price
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
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ 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;
--
-- Table structure for table vendor_edi_accounts
DROP TABLE IF EXISTS vendor_edi_accounts;
CREATE TABLE IF NOT EXISTS vendor_edi_accounts (
id INT(11) NOT NULL auto_increment,
- description TEXT NOT NULL,
+ description MEDIUMTEXT NOT NULL,
host VARCHAR(40),
username VARCHAR(40),
password VARCHAR(40),
last_activity DATE,
vendor_id INT(11) REFERENCES aqbooksellers( id ),
- download_directory TEXT,
- upload_directory TEXT,
+ download_directory MEDIUMTEXT,
+ upload_directory MEDIUMTEXT,
san VARCHAR(20),
id_code_qualifier VARCHAR(3) default '14',
transport VARCHAR(6) default 'FTP',
KEY shipmentbudget (shipment_budget),
CONSTRAINT vfk_vendor_id FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ),
CONSTRAINT vfk_shipment_budget FOREIGN KEY ( shipment_budget ) REFERENCES aqbudgets ( budget_id )
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table edifact_messages
transfer_date DATE,
vendor_id INT(11) REFERENCES aqbooksellers( id ),
edi_acct INTEGER REFERENCES vendor_edi_accounts( id ),
- status TEXT,
+ status MEDIUMTEXT,
basketno INT(11) REFERENCES aqbasket( basketno),
- raw_msg MEDIUMTEXT,
- filename TEXT,
+ raw_msg LONGTEXT,
+ filename MEDIUMTEXT,
deleted BOOLEAN NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY vendorid ( vendor_id),
CONSTRAINT emfk_vendor FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT emfk_edi_acct FOREIGN KEY ( edi_acct ) REFERENCES vendor_edi_accounts ( id ) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT emfk_basketno FOREIGN KEY ( basketno ) REFERENCES aqbasket ( basketno ) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table aqinvoices
DROP TABLE IF EXISTS aqinvoices;
CREATE TABLE aqinvoices (
invoiceid int(11) NOT NULL AUTO_INCREMENT, -- ID of the invoice, primary key
- invoicenumber mediumtext NOT NULL, -- Name of invoice
+ invoicenumber LONGTEXT NOT NULL, -- Name of invoice
booksellerid int(11) NOT NULL, -- foreign key to aqbooksellers
shipmentdate date default NULL, -- date of shipment
billingdate date default NULL, -- date of billing
CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT edifact_msg_fk FOREIGN KEY ( message_id ) REFERENCES edifact_messages ( id ) ON DELETE SET NULL,
CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) 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` text default NULL, -- reason of cancellation
- `order_internalnote` mediumtext, -- notes related to this order line, made for staff
- `order_vendornote` mediumtext, -- notes related to this order line, made for vendor
- `purchaseordernumber` mediumtext, -- not used? always NULL
+ `cancellationreason` MEDIUMTEXT default NULL, -- reason of cancellation
+ `order_internalnote` LONGTEXT, -- notes related to this order line, made for staff
+ `order_vendornote` LONGTEXT, -- notes related to this order line, made for vendor
+ `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
line_item_id varchar(35) default NULL, -- Supplier's article id for Edifact orderline
suppliers_reference_number varchar(35) default NULL, -- Suppliers unique edifact quote ref
suppliers_reference_qualifier varchar(3) default NULL, -- Type of number above usually 'QLI'
- `suppliers_report` text COLLATE utf8_unicode_ci, -- reports received from suppliers
+ `suppliers_report` MEDIUMTEXT COLLATE utf8mb4_unicode_ci, -- reports received from suppliers
PRIMARY KEY (`ordernumber`),
KEY `basketno` (`basketno`),
KEY `biblionumber` (`biblionumber`),
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,
CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `aqorders_subscriptionid` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `aqorders_currency` FOREIGN KEY (`currency`) REFERENCES `currency` (`currency`) ON DELETE SET NULL ON UPDATE SET NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `aqorder_users`
PRIMARY KEY (ordernumber, borrowernumber),
CONSTRAINT aqorder_users_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT aqorder_users_ibfk_2 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `aqorders_items`
PRIMARY KEY (`itemnumber`),
KEY `ordernumber` (`ordernumber`),
CONSTRAINT aqorders_items_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table aqorders_transfers
UNIQUE KEY ordernumber_to (ordernumber_to),
CONSTRAINT aqorders_transfers_ordernumber_from FOREIGN KEY (ordernumber_from) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT aqorders_transfers_ordernumber_to FOREIGN KEY (ordernumber_to) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `fieldmapping`
`fieldcode` char(3) NOT NULL, -- marc field number to map to this keyword
`subfieldcode` char(1) NOT NULL, -- marc subfield associated with the fieldcode to map to this keyword
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `transport_cost`
PRIMARY KEY (frombranch, tobranch),
CONSTRAINT transport_cost_ibfk_1 FOREIGN KEY (frombranch) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT transport_cost_ibfk_2 FOREIGN KEY (tobranch) REFERENCES branches (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `biblioimages`
`thumbnail` mediumblob NOT NULL, -- thumbnail file contents
PRIMARY KEY (`imagenumber`),
CONSTRAINT `bibliocoverimage_fk1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `social_data`
`score_avg` DECIMAL(5,2),
`num_scores` INT,
PRIMARY KEY (`isbn`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- 'Ratings' table. This tracks the star ratings set by borrowers.
PRIMARY KEY (borrowernumber,biblionumber),
CONSTRAINT ratings_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT ratings_ibfk_2 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `quotes`
DROP TABLE IF EXISTS quotes;
CREATE TABLE `quotes` ( -- data for the quote of the day feature
`id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the quote
- `source` text DEFAULT NULL, -- source/credit for the quote
- `text` mediumtext NOT NULL, -- text of the quote
+ `source` MEDIUMTEXT DEFAULT NULL, -- source/credit for the quote
+ `text` LONGTEXT NOT NULL, -- text of the quote
`timestamp` datetime NOT NULL, -- date and time that the quote last appeared in the opac
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table categories_branches
branchcode VARCHAR(10),
FOREIGN KEY (categorycode) REFERENCES categories(categorycode) ON DELETE CASCADE,
FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
-) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table authorised_values_branches
branchcode VARCHAR(10) NOT NULL,
FOREIGN KEY (av_id) REFERENCES authorised_values(id) ON DELETE CASCADE,
FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
-) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
b_branchcode VARCHAR(10),
FOREIGN KEY (bat_code) REFERENCES borrower_attribute_types(code) ON DELETE CASCADE,
FOREIGN KEY (b_branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
-) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `borrower_modifications`
`verification_token` varchar(255) NOT NULL DEFAULT '',
`borrowernumber` int(11) NOT NULL DEFAULT '0',
`cardnumber` varchar(32) DEFAULT NULL,
- `surname` mediumtext,
- `firstname` text,
- `title` mediumtext,
- `othernames` mediumtext,
- `initials` text,
+ `surname` LONGTEXT,
+ `firstname` MEDIUMTEXT,
+ `title` LONGTEXT,
+ `othernames` LONGTEXT,
+ `initials` MEDIUMTEXT,
`streetnumber` varchar(10) DEFAULT NULL,
`streettype` varchar(50) DEFAULT NULL,
- `address` mediumtext,
- `address2` text,
- `city` mediumtext,
- `state` text,
+ `address` LONGTEXT,
+ `address2` MEDIUMTEXT,
+ `city` LONGTEXT,
+ `state` MEDIUMTEXT,
`zipcode` varchar(25) DEFAULT NULL,
- `country` text,
- `email` mediumtext,
- `phone` text,
+ `country` MEDIUMTEXT,
+ `email` LONGTEXT,
+ `phone` MEDIUMTEXT,
`mobile` varchar(50) DEFAULT NULL,
- `fax` mediumtext,
- `emailpro` text,
- `phonepro` text,
+ `fax` LONGTEXT,
+ `emailpro` MEDIUMTEXT,
+ `phonepro` MEDIUMTEXT,
`B_streetnumber` varchar(10) DEFAULT NULL,
`B_streettype` varchar(50) DEFAULT NULL,
`B_address` varchar(100) DEFAULT NULL,
- `B_address2` text,
- `B_city` mediumtext,
- `B_state` text,
+ `B_address2` MEDIUMTEXT,
+ `B_city` LONGTEXT,
+ `B_state` MEDIUMTEXT,
`B_zipcode` varchar(25) DEFAULT NULL,
- `B_country` text,
- `B_email` text,
- `B_phone` mediumtext,
+ `B_country` MEDIUMTEXT,
+ `B_email` MEDIUMTEXT,
+ `B_phone` LONGTEXT,
`dateofbirth` date DEFAULT NULL,
`branchcode` varchar(10) DEFAULT NULL,
`categorycode` varchar(10) DEFAULT NULL,
`lost` tinyint(1) DEFAULT NULL,
`debarred` date DEFAULT NULL,
`debarredcomment` varchar(255) DEFAULT NULL,
- `contactname` mediumtext,
- `contactfirstname` text,
- `contacttitle` text,
+ `contactname` LONGTEXT,
+ `contactfirstname` MEDIUMTEXT,
+ `contacttitle` MEDIUMTEXT,
`guarantorid` int(11) DEFAULT NULL,
- `borrowernotes` mediumtext,
+ `borrowernotes` LONGTEXT,
`relationship` varchar(100) DEFAULT NULL,
`sex` varchar(1) DEFAULT NULL,
`password` varchar(30) DEFAULT NULL,
`flags` int(11) DEFAULT NULL,
`userid` varchar(75) DEFAULT NULL,
- `opacnote` mediumtext,
+ `opacnote` LONGTEXT,
`contactnote` varchar(255) DEFAULT NULL,
`sort1` varchar(80) DEFAULT NULL,
`sort2` varchar(80) DEFAULT NULL,
`altcontactaddress1` varchar(255) DEFAULT NULL,
`altcontactaddress2` varchar(255) DEFAULT NULL,
`altcontactaddress3` varchar(255) DEFAULT NULL,
- `altcontactstate` text,
+ `altcontactstate` MEDIUMTEXT,
`altcontactzipcode` varchar(50) DEFAULT NULL,
- `altcontactcountry` text,
+ `altcontactcountry` MEDIUMTEXT,
`altcontactphone` varchar(50) DEFAULT NULL,
`smsalertnumber` varchar(50) DEFAULT NULL,
`privacy` int(11) DEFAULT NULL,
- `extended_attributes` text DEFAULT NULL,
- PRIMARY KEY (`verification_token`,`borrowernumber`),
- KEY `verification_token` (`verification_token`),
+ `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=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table uploaded_files
CREATE TABLE uploaded_files (
id int(11) NOT NULL AUTO_INCREMENT,
hashvalue CHAR(40) NOT NULL,
- filename TEXT NOT NULL,
- dir TEXT NOT NULL,
+ filename MEDIUMTEXT NOT NULL,
+ dir MEDIUMTEXT NOT NULL,
filesize int(11),
dtcreated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- uploadcategorycode tinytext,
+ uploadcategorycode TEXT,
owner int(11),
public tinyint,
permanent tinyint,
PRIMARY KEY (id)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table linktracker
biblionumber int(11) DEFAULT NULL, -- biblionumber of the record the link is from
itemnumber int(11) DEFAULT NULL, -- itemnumber if applicable that the link was from
borrowernumber int(11) DEFAULT NULL, -- borrowernumber who clicked the link
- url text, -- the link itself
+ url MEDIUMTEXT, -- the link itself
timeclicked datetime DEFAULT NULL, -- the date and time the link was clicked
PRIMARY KEY (id),
KEY bibidx (biblionumber),
KEY itemidx (itemnumber),
KEY borridx (borrowernumber),
KEY dateidx (timeclicked)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'plugin_data'
CREATE TABLE IF NOT EXISTS plugin_data (
plugin_class varchar(255) NOT NULL,
plugin_key varchar(255) NOT NULL,
- plugin_value text,
- PRIMARY KEY (plugin_class,plugin_key)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ plugin_value MEDIUMTEXT,
+ 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`
DROP TABLE IF EXISTS patron_lists;
CREATE TABLE patron_lists (
patron_list_id int(11) NOT NULL AUTO_INCREMENT, -- unique identifier
- name varchar(255) CHARACTER SET utf8 NOT NULL, -- the list's name
+ 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=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Constraints for table `patron_lists`
PRIMARY KEY (patron_list_patron_id),
KEY patron_list_id (patron_list_id),
KEY borrowernumber (borrowernumber)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Constraints for table `patron_list_patrons`
CREATE TABLE IF NOT EXISTS marc_modification_templates (
template_id int(11) NOT NULL AUTO_INCREMENT,
- name text NOT NULL,
+ name MEDIUMTEXT NOT NULL,
PRIMARY KEY (template_id)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table '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,
field_value varchar(100) DEFAULT NULL,
to_field varchar(3) DEFAULT NULL,
to_subfield varchar(1) DEFAULT NULL,
- to_regex_search text,
- to_regex_replace text,
+ to_regex_search MEDIUMTEXT,
+ to_regex_replace MEDIUMTEXT,
to_regex_modifiers varchar(8) DEFAULT '',
conditional enum('if','unless') DEFAULT NULL,
conditional_field varchar(3) DEFAULT NULL,
conditional_subfield varchar(1) DEFAULT NULL,
conditional_comparison enum('exists','not_exists','equals','not_equals') DEFAULT NULL,
- conditional_value text,
+ conditional_value MEDIUMTEXT,
conditional_regex tinyint(1) NOT NULL DEFAULT '0',
- description text,
+ description MEDIUMTEXT,
PRIMARY KEY (mmta_id),
CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `misc_files`
PRIMARY KEY (`file_id`),
KEY `table_tag` (`table_tag`),
KEY `record_id` (`record_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `columns_settings`
columnname varchar(255) NOT NULL,
cannot_be_toggled int(1) NOT NULL DEFAULT 0,
is_hidden int(1) NOT NULL DEFAULT 0,
- PRIMARY KEY(module, page, tablename, columnname)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ PRIMARY KEY(module (191), page (191), tablename (191), columnname (191))
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'items_search_fields'
tagfield CHAR(3) NOT NULL,
tagsubfield CHAR(1) NULL DEFAULT NULL,
authorised_values_category VARCHAR(32) NULL DEFAULT NULL,
- PRIMARY KEY(name),
+ PRIMARY KEY(name (191)),
CONSTRAINT items_search_fields_authorised_values_category
FOREIGN KEY (authorised_values_category) REFERENCES authorised_value_categories (category_name)
ON DELETE SET NULL ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'discharges'
PRIMARY KEY (discharge_id),
KEY borrower_discharges_ibfk1 (borrower),
CONSTRAINT borrower_discharges_ibfk1 FOREIGN KEY (borrower) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table additional_fields
`marcfield` varchar(16) NOT NULL DEFAULT '', -- contains the marc field to copied into the record
`searchable` tinyint(1) NOT NULL DEFAULT '0', -- is the field searchable?
PRIMARY KEY (`id`),
- UNIQUE KEY `fields_uniq` (`tablename`,`name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ UNIQUE KEY `fields_uniq` (`tablename` (191),`name` (191))
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table additional_field_values
PRIMARY KEY (`id`),
UNIQUE KEY `field_record` (`field_id`,`record_id`),
CONSTRAINT `afv_fk` FOREIGN KEY (`field_id`) REFERENCES `additional_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'localization'
DROP TABLE IF EXISTS localization;
CREATE TABLE `localization` (
localization_id int(11) NOT NULL AUTO_INCREMENT,
- entity varchar(16) COLLATE utf8_unicode_ci NOT NULL,
- code varchar(64) COLLATE utf8_unicode_ci NOT NULL,
- lang varchar(25) COLLATE utf8_unicode_ci NOT NULL, -- could be a foreign key
- translation text COLLATE utf8_unicode_ci,
+ entity varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
+ code varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
+ lang varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL, -- could be a foreign key
+ translation MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (localization_id),
UNIQUE KEY `entity_code_lang` (`entity`,`code`,`lang`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'audio_alerts'
sound varchar(255) NOT NULL,
PRIMARY KEY (id),
KEY precedence (precedence)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table '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 )
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `courses`
`section` varchar(255) DEFAULT NULL, -- the 'section' of a course
`course_name` varchar(255) DEFAULT NULL, -- the name of the course
`term` varchar(80) DEFAULT NULL, -- the authorised value for the TERM
- `staff_note` mediumtext, -- the text of the staff only note
- `public_note` mediumtext, -- the text of the public / opac note
+ `staff_note` LONGTEXT, -- the text of the staff only note
+ `public_note` LONGTEXT, -- the text of the public / opac note
`students_count` varchar(20) DEFAULT NULL, -- how many students will be taking this course/section
`enabled` enum('yes','no') NOT NULL DEFAULT 'yes', -- determines whether the course is active
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`course_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `course_instructors`
`borrowernumber` int(11) NOT NULL, -- foreign key to link to borrowers.borrowernumber for instructor information
PRIMARY KEY (`course_id`,`borrowernumber`),
KEY `borrowernumber` (`borrowernumber`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Constraints for table `course_instructors`
`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'
PRIMARY KEY (`ci_id`),
UNIQUE KEY `itemnumber` (`itemnumber`),
KEY `holdingbranch` (`holdingbranch`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Constraints for table `course_items`
`cr_id` int(11) NOT NULL AUTO_INCREMENT,
`course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id
`ci_id` int(11) NOT NULL, -- foreign key to link to courses_items.ci_id
- `staff_note` mediumtext, -- staff only note
- `public_note` mediumtext, -- public, OPAC visible note
+ `staff_note` LONGTEXT, -- staff only note
+ `public_note` LONGTEXT, -- public, OPAC visible note
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`cr_id`),
UNIQUE KEY `pseudo_key` (`course_id`,`ci_id`),
KEY `course_id` (`course_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Constraints for table `course_reserves`
REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `housebound_profile`
DROP TABLE IF EXISTS `housebound_profile`;
CREATE TABLE `housebound_profile` (
`borrowernumber` int(11) NOT NULL, -- Number of the borrower associated with this profile.
- `day` text NOT NULL, -- The preferred day of the week for delivery.
- `frequency` text NOT NULL, -- The Authorised_Value definining the pattern for delivery.
- `fav_itemtypes` text default NULL, -- Free text describing preferred itemtypes.
- `fav_subjects` text default NULL, -- Free text describing preferred subjects.
- `fav_authors` text default NULL, -- Free text describing preferred authors.
- `referral` text default NULL, -- Free text indicating how the borrower was added to the service.
- `notes` text default NULL, -- Free text for additional notes.
+ `day` MEDIUMTEXT NOT NULL, -- The preferred day of the week for delivery.
+ `frequency` MEDIUMTEXT NOT NULL, -- The Authorised_Value definining the pattern for delivery.
+ `fav_itemtypes` MEDIUMTEXT default NULL, -- Free text describing preferred itemtypes.
+ `fav_subjects` MEDIUMTEXT default NULL, -- Free text describing preferred subjects.
+ `fav_authors` MEDIUMTEXT default NULL, -- Free text describing preferred authors.
+ `referral` MEDIUMTEXT default NULL, -- Free text indicating how the borrower was added to the service.
+ `notes` MEDIUMTEXT default NULL, -- Free text for additional notes.
PRIMARY KEY (`borrowernumber`),
CONSTRAINT `housebound_profile_bnfk`
FOREIGN KEY (`borrowernumber`)
REFERENCES `borrowers` (`borrowernumber`)
ON UPDATE CASCADE ON DELETE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `housebound_visit`
FOREIGN KEY (`deliverer_brwnumber`)
REFERENCES `borrowers` (`borrowernumber`)
ON UPDATE CASCADE ON DELETE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `housebound_role`
FOREIGN KEY (`borrowernumber_id`)
REFERENCES `borrowers` (`borrowernumber`)
ON UPDATE CASCADE ON DELETE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'article_requests'
`borrowernumber` int(11) NOT NULL,
`biblionumber` int(11) NOT NULL,
`itemnumber` int(11) DEFAULT NULL,
- `branchcode` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
- `title` text,
- `author` text,
- `volume` text,
- `issue` text,
- `date` text,
- `pages` text,
- `chapters` text,
- `patron_notes` text,
+ `branchcode` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+ `title` MEDIUMTEXT,
+ `author` MEDIUMTEXT,
+ `volume` MEDIUMTEXT,
+ `issue` MEDIUMTEXT,
+ `date` MEDIUMTEXT,
+ `pages` MEDIUMTEXT,
+ `chapters` MEDIUMTEXT,
+ `patron_notes` MEDIUMTEXT,
`status` enum('PENDING','PROCESSING','COMPLETED','CANCELED') NOT NULL DEFAULT 'PENDING',
- `notes` text,
+ `notes` MEDIUMTEXT,
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`updated_on` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `article_requests_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `article_requests_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `article_requests_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `biblio_metadata`
`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=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `deletedbiblio_metadata`
`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=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'club_templates'
CREATE TABLE IF NOT EXISTS club_templates (
id int(11) NOT NULL AUTO_INCREMENT,
- `name` tinytext NOT NULL,
- description text,
+ `name` TEXT NOT NULL,
+ description MEDIUMTEXT,
is_enrollable_from_opac tinyint(1) NOT NULL DEFAULT '0',
is_email_required tinyint(1) NOT NULL DEFAULT '0',
branchcode varchar(10) NULL DEFAULT NULL,
PRIMARY KEY (id),
KEY ct_branchcode (branchcode),
CONSTRAINT `club_templates_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'clubs'
CREATE TABLE IF NOT EXISTS clubs (
id int(11) NOT NULL AUTO_INCREMENT,
club_template_id int(11) NOT NULL,
- `name` tinytext NOT NULL,
- description text,
+ `name` TEXT NOT NULL,
+ description MEDIUMTEXT,
date_start date DEFAULT NULL,
date_end date DEFAULT NULL,
branchcode varchar(10) NULL DEFAULT NULL,
KEY branchcode (branchcode),
CONSTRAINT clubs_ibfk_1 FOREIGN KEY (club_template_id) REFERENCES club_templates (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT clubs_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'club_enrollments'
CONSTRAINT club_enrollments_ibfk_1 FOREIGN KEY (club_id) REFERENCES clubs (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT club_enrollments_ibfk_2 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT club_enrollments_ibfk_3 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE SET NULL ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'club_template_enrollment_fields'
CREATE TABLE IF NOT EXISTS club_template_enrollment_fields (
id int(11) NOT NULL AUTO_INCREMENT,
club_template_id int(11) NOT NULL,
- `name` tinytext NOT NULL,
- description text,
+ `name` TEXT NOT NULL,
+ description MEDIUMTEXT,
authorised_value_category varchar(16) DEFAULT NULL,
PRIMARY KEY (id),
KEY club_template_id (club_template_id),
CONSTRAINT club_template_enrollment_fields_ibfk_1 FOREIGN KEY (club_template_id) REFERENCES club_templates (id) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'club_enrollment_fields'
id int(11) NOT NULL AUTO_INCREMENT,
club_enrollment_id int(11) NOT NULL,
club_template_enrollment_field_id int(11) NOT NULL,
- `value` text NOT NULL,
+ `value` MEDIUMTEXT NOT NULL,
PRIMARY KEY (id),
KEY club_enrollment_id (club_enrollment_id),
KEY club_template_enrollment_field_id (club_template_enrollment_field_id),
CONSTRAINT club_enrollment_fields_ibfk_1 FOREIGN KEY (club_enrollment_id) REFERENCES club_enrollments (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT club_enrollment_fields_ibfk_2 FOREIGN KEY (club_template_enrollment_field_id) REFERENCES club_template_enrollment_fields (id) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'club_template_fields'
CREATE TABLE IF NOT EXISTS club_template_fields (
id int(11) NOT NULL AUTO_INCREMENT,
club_template_id int(11) NOT NULL,
- `name` tinytext NOT NULL,
- description text,
+ `name` TEXT NOT NULL,
+ description MEDIUMTEXT,
authorised_value_category varchar(16) DEFAULT NULL,
PRIMARY KEY (id),
KEY club_template_id (club_template_id),
CONSTRAINT club_template_fields_ibfk_1 FOREIGN KEY (club_template_id) REFERENCES club_templates (id) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'club_fields'
id int(11) NOT NULL AUTO_INCREMENT,
club_template_field_id int(11) NOT NULL,
club_id int(11) NOT NULL,
- `value` text,
+ `value` MEDIUMTEXT,
PRIMARY KEY (id),
KEY club_template_field_id (club_template_field_id),
KEY club_id (club_id),
CONSTRAINT club_fields_ibfk_3 FOREIGN KEY (club_template_field_id) REFERENCES club_template_fields (id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT club_fields_ibfk_4 FOREIGN KEY (club_id) REFERENCES clubs (id) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `illrequests`
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
- notesopac text DEFAULT NULL, -- Patron notes attached to request
- notesstaff text DEFAULT NULL, -- Staff notes attached to 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
backend varchar(20) DEFAULT NULL, -- The backend used to create request
CONSTRAINT `illrequests_bnfk`
CONSTRAINT `illrequests_bcfk_2`
FOREIGN KEY (`branchcode`)
REFERENCES `branches` (`branchcode`)
- ON UPDATE CASCADE ON DELETE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+ 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;
--
-- Table structure for table `illrequestattributes`
CREATE TABLE illrequestattributes (
illrequest_id bigint(20) unsigned NOT NULL, -- ILL request number
type varchar(200) NOT NULL, -- API ILL property name
- value text NOT NULL, -- API ILL property value
- PRIMARY KEY (`illrequest_id`,`type`),
+ 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)
REFERENCES `illrequests` (`illrequest_id`)
ON UPDATE CASCADE ON DELETE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table 'library_groups'
parent_id INT(11) NULL DEFAULT NULL, -- if this is a child group, the id of the parent group
branchcode VARCHAR(10) NULL DEFAULT NULL, -- The branchcode of a branch belonging to the parent group
title VARCHAR(100) NULL DEFAULT NULL, -- Short description of the goup
- description TEXT NULL DEFAULT NULL, -- Longer explanation of the group, if necessary
+ 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 ),
FOREIGN KEY (parent_id) REFERENCES library_groups(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON UPDATE CASCADE ON DELETE CASCADE,
UNIQUE KEY title ( title )
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) 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 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 */;
projects / koha-equinox.git / blobdiff