X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=e430f1521a257fabd24acd8deb6b4f7432d52c3b;hb=812b3d66b573beabefdb3f66d74d3df3abb3eccd;hp=36481ad3f68fd104dca323774fdd0a87a1ec076f;hpb=9fd0555ae828972a7268c56a4f5e56a4a681aa2b;p=koha.git diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 36481ad3f6..e430f1521a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -8,7 +8,7 @@ /*!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 */; @@ -26,13 +26,13 @@ CREATE TABLE `auth_header` ( `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` @@ -43,9 +43,9 @@ CREATE 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` @@ -69,11 +69,11 @@ CREATE 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` @@ -90,7 +90,7 @@ CREATE 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; -- @@ -99,9 +99,9 @@ CREATE TABLE `auth_tag_structure` ( 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` @@ -117,10 +117,10 @@ CREATE TABLE `authorised_values` ( -- stores values for authorized values catego `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` @@ -130,19 +130,19 @@ DROP TABLE IF EXISTS `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` @@ -153,7 +153,7 @@ CREATE TABLE `biblio_framework` ( -- information about MARC frameworks `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` @@ -163,29 +163,29 @@ DROP TABLE IF EXISTS `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, @@ -200,10 +200,10 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in 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` @@ -224,7 +224,7 @@ CREATE TABLE `borrower_attribute_types` ( -- definitions for custom patron field `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` @@ -237,22 +237,7 @@ CREATE TABLE IF NOT EXISTS `borrower_password_recovery` ( -- holds information a `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; - --- --- Table structure for table `branchcategories` --- - -DROP TABLE IF EXISTS `branchcategories`; -CREATE TABLE `branchcategories` ( -- information related to library/branch groups - `categorycode` varchar(10) NOT NULL default '', -- unique identifier for the library/branch group - `categoryname` varchar(32), -- name of the library/branch group - `codedescription` mediumtext, -- longer description of the library/branch group - `categorytype` varchar(16), -- says whether this is a search group or a properties group - `show_in_pulldown` tinyint(1) NOT NULL DEFAULT '0', -- says this group should be in the opac libararies pulldown if it is enabled - PRIMARY KEY (`categorycode`), - KEY `show_in_pulldown` (`show_in_pulldown`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table `branches` @@ -261,44 +246,42 @@ CREATE TABLE `branchcategories` ( -- information related to library/branch group 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 `branchrelations` +-- Table structure for table `branches_overdrive` -- -DROP TABLE IF EXISTS `branchrelations`; -CREATE TABLE `branchrelations` ( -- this table links libraries/branches to groups - `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table to identify the branch - `categorycode` varchar(10) NOT NULL default '', -- foreign key from the branchcategories table to identify the group - PRIMARY KEY (`branchcode`,`categorycode`), - KEY `branchcode` (`branchcode`), - KEY `categorycode` (`categorycode`), - CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +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` @@ -310,7 +293,7 @@ CREATE 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` @@ -319,7 +302,7 @@ CREATE TABLE `browser` ( 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 @@ -337,7 +320,7 @@ CREATE TABLE `categories` ( -- this table shows information related to Koha patr `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'. 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 @@ -346,10 +329,10 @@ DROP TABLE IF EXISTS 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` @@ -367,7 +350,7 @@ CREATE TABLE collections_tracking ( 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; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table `branch_borrower_circ_rules` @@ -384,7 +367,7 @@ CREATE TABLE `branch_borrower_circ_rules` ( -- includes default circulation rule 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; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table `default_borrower_circ_rules` @@ -398,7 +381,7 @@ CREATE TABLE `default_borrower_circ_rules` ( -- default checkout rules found und 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` @@ -415,7 +398,7 @@ CREATE TABLE `default_branch_circ_rules` ( 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` @@ -430,7 +413,7 @@ CREATE TABLE `default_circ_rules` ( 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` @@ -444,7 +427,7 @@ CREATE TABLE `cities` ( -- authorized values for cities/states/countries to choo `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` @@ -453,11 +436,11 @@ CREATE TABLE `cities` ( -- authorized values for cities/states/countries to choo 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_sources` @@ -466,14 +449,14 @@ CREATE TABLE `class_sort_rules` ( 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 '', 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; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table `currency` @@ -488,8 +471,9 @@ CREATE 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` @@ -499,19 +483,19 @@ DROP TABLE IF EXISTS `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` @@ -521,29 +505,29 @@ DROP TABLE IF EXISTS `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, @@ -557,9 +541,9 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t 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` @@ -569,35 +553,35 @@ DROP TABLE IF EXISTS `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 @@ -608,17 +592,17 @@ CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower `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 @@ -627,9 +611,9 @@ CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower `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 @@ -640,11 +624,11 @@ CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower `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 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` @@ -657,7 +641,7 @@ CREATE 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) @@ -667,6 +651,7 @@ CREATE TABLE `deleteditems` ( `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) @@ -677,22 +662,22 @@ CREATE TABLE `deleteditems` ( `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$x) + `itemnotes_nonpublic` LONGTEXT default NULL, `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. @@ -705,7 +690,7 @@ CREATE TABLE `deleteditems` ( 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` @@ -715,16 +700,16 @@ DROP TABLE IF EXISTS `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` @@ -746,10 +731,10 @@ CREATE TABLE `import_batches` ( -- information about batches of marc records tha `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` @@ -764,12 +749,12 @@ CREATE 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`), @@ -778,7 +763,7 @@ CREATE TABLE `import_records` ( 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` @@ -791,7 +776,7 @@ CREATE TABLE `import_record_matches` ( -- matches found when importing a batch o 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` @@ -807,7 +792,7 @@ CREATE 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` @@ -829,7 +814,7 @@ CREATE 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` @@ -842,14 +827,14 @@ CREATE 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` @@ -865,6 +850,7 @@ CREATE TABLE `issuingrules` ( -- circulation and fine rules `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 @@ -884,16 +870,18 @@ CREATE TABLE `issuingrules` ( -- circulation and fine rules `no_auto_renewal_after_hard_limit` date default NULL, -- no auto renewal allowed after a given date `reservesallowed` smallint(6) NOT NULL default "0", -- how many holds are allowed `holds_per_record` SMALLINT(6) NOT NULL DEFAULT 1, -- How many holds a patron can have on a given bib + `holds_per_day` SMALLINT(6) DEFAULT NULL, -- How many holds a patron can have on a day `branchcode` varchar(10) NOT NULL default '', -- the branch this rule is for (branches.branchcode) overduefinescap decimal(28,6) default NULL, -- the maximum amount of an overdue fine cap_fine_to_replacement_price BOOLEAN NOT NULL DEFAULT '0', -- cap the fine based on item's replacement price onshelfholds tinyint(1) NOT NULL default 0, -- allow holds for items that are on shelf opacitemholds char(1) NOT NULL default 'N', -- allow opac users to place specific items on hold article_requests enum('no','yes','bib_only','item_only') NOT NULL DEFAULT 'no', -- allow article requests to be placed, + `note` varchar(100) default NULL, -- description of rule, reason for setting rule PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`), KEY `categorycode` (`categorycode`), KEY `itemtype` (`itemtype`) -) 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` @@ -904,7 +892,7 @@ CREATE TABLE `refund_lost_item_fee_rules` ( -- refund lost item fee rules tbale `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` @@ -917,7 +905,7 @@ CREATE TABLE `items` ( -- holdings/item information `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) @@ -927,6 +915,7 @@ CREATE TABLE `items` ( -- holdings/item information `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) @@ -937,22 +926,22 @@ CREATE TABLE `items` ( -- holdings/item information `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$x) + `itemnotes_nonpublic` LONGTEXT default NULL, `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. @@ -963,7 +952,7 @@ CREATE TABLE `items` ( -- holdings/item information 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`), @@ -972,7 +961,7 @@ CREATE TABLE `items` ( -- holdings/item information 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` @@ -981,13 +970,13 @@ CREATE TABLE `items` ( -- holdings/item information 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 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 @@ -995,7 +984,7 @@ CREATE TABLE `itemtypes` ( -- defines the item types 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` @@ -1010,7 +999,7 @@ CREATE 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` @@ -1024,7 +1013,7 @@ CREATE TABLE `branchtransfers` ( -- information for items that are in transit be `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`), @@ -1032,7 +1021,7 @@ CREATE TABLE `branchtransfers` ( -- information for items that are in transit be 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` @@ -1051,7 +1040,7 @@ CREATE TABLE `branch_item_rules` ( -- information entered in the circulation and 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` @@ -1066,7 +1055,7 @@ CREATE 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` @@ -1083,16 +1072,16 @@ CREATE 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` @@ -1122,7 +1111,7 @@ CREATE 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` @@ -1131,7 +1120,7 @@ CREATE TABLE `creator_templates` ( 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, @@ -1146,14 +1135,14 @@ CREATE TABLE `marc_subfield_structure` ( `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` @@ -1167,9 +1156,11 @@ CREATE 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` @@ -1185,7 +1176,7 @@ CREATE 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` @@ -1199,7 +1190,7 @@ CREATE 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; -- @@ -1218,7 +1209,7 @@ CREATE TABLE `matchpoint_components` ( 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` @@ -1231,7 +1222,7 @@ CREATE TABLE `matchpoint_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` @@ -1244,7 +1235,7 @@ CREATE 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` @@ -1262,7 +1253,7 @@ CREATE 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` @@ -1273,27 +1264,12 @@ CREATE TABLE `need_merge_authorities` ( -- keeping track of authority records st `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; - --- --- Table structure for table `notifys` --- - -DROP TABLE IF EXISTS `notifys`; -CREATE TABLE `notifys` ( - `notify_id` int(11) NOT NULL default 0, - `borrowernumber` int(11) NOT NULL default 0, - `itemnumber` int(11) NOT NULL default 0, - `notify_date` date default NULL, - `notify_send_date` date default NULL, - `notify_level` int(1) NOT NULL default 0, - `method` varchar(20) NOT NULL default '' -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table `oai_sets` @@ -1305,7 +1281,7 @@ CREATE 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` @@ -1316,7 +1292,7 @@ CREATE 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` @@ -1330,7 +1306,7 @@ CREATE 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` @@ -1342,7 +1318,7 @@ CREATE 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` @@ -1364,7 +1340,7 @@ CREATE TABLE `overduerules` ( -- overdue notice status and triggers `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` -- @@ -1382,7 +1358,7 @@ CREATE 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; -- @@ -1394,8 +1370,8 @@ CREATE TABLE `printers` ( `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` @@ -1415,7 +1391,7 @@ CREATE 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` @@ -1429,9 +1405,9 @@ CREATE TABLE `repeatable_holidays` ( -- information for the days the library is `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` @@ -1441,14 +1417,14 @@ DROP TABLE IF EXISTS `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` @@ -1460,11 +1436,11 @@ CREATE TABLE saved_sql ( -- saved sql reports `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, @@ -1473,7 +1449,7 @@ CREATE TABLE saved_sql ( -- saved sql reports 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; -- @@ -1484,10 +1460,10 @@ DROP TABLE IF EXISTS `saved_reports`; 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' @@ -1498,10 +1474,11 @@ CREATE 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, 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` @@ -1513,14 +1490,14 @@ CREATE TABLE IF NOT EXISTS `search_history` ( -- patron's opac 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' @@ -1533,9 +1510,9 @@ CREATE TABLE `search_marc_map` ( 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', 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' @@ -1551,7 +1528,7 @@ CREATE 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` @@ -1568,14 +1545,14 @@ CREATE TABLE `serial` ( -- issues related to subscriptions `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` @@ -1584,9 +1561,9 @@ CREATE TABLE `serial` ( -- issues related to subscriptions 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` @@ -1598,8 +1575,8 @@ CREATE 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` @@ -1609,35 +1586,35 @@ DROP TABLE IF EXISTS `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 @@ -1648,17 +1625,17 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons `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 @@ -1667,9 +1644,9 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons `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 @@ -1680,21 +1657,21 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons `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 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` @@ -1707,12 +1684,12 @@ CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as `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` @@ -1724,7 +1701,7 @@ CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record 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 @@ -1732,25 +1709,27 @@ CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record 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` @@ -1767,12 +1746,13 @@ CREATE TABLE `issues` ( -- information related to check outs or 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 `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`), @@ -1781,7 +1761,7 @@ CREATE TABLE `issues` ( -- information related to check outs or issues 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` @@ -1798,12 +1778,13 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r `lastreneweddate` datetime default NULL, -- date the item was last renewed `renewals` tinyint(4) default NULL, -- 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`), @@ -1813,7 +1794,7 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r 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` @@ -1829,7 +1810,7 @@ CREATE TABLE IF NOT EXISTS `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` @@ -1841,6 +1822,7 @@ SET character_set_client = utf8; CREATE TABLE `creator_batches` ( `label_id` int(11) NOT NULL AUTO_INCREMENT, `batch_id` int(10) NOT NULL DEFAULT '1', + `description` mediumtext DEFAULT NULL, `item_number` int(11) DEFAULT NULL, `borrower_number` int(11) DEFAULT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, @@ -1853,7 +1835,7 @@ CREATE TABLE `creator_batches` ( 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` @@ -1864,7 +1846,7 @@ CREATE TABLE `opac_news` ( -- data from the news tool `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 @@ -1874,7 +1856,7 @@ CREATE TABLE `opac_news` ( -- data from the news tool 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` @@ -1887,7 +1869,7 @@ CREATE TABLE `patronimage` ( -- information related to patron images `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` @@ -1897,20 +1879,20 @@ DROP TABLE IF EXISTS `reserves`; CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha `reserve_id` int(11) NOT NULL auto_increment, -- primary key `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for - `reservedate` date default NULL, -- the date the hold was places + `reservedate` date default NULL, -- the date the hold was placed `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at `notificationdate` date default NULL, -- currently unused `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 @@ -1926,7 +1908,7 @@ CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha 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` @@ -1942,14 +1924,14 @@ CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b `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 @@ -1967,7 +1949,7 @@ CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b 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` @@ -1978,13 +1960,13 @@ CREATE TABLE `reviews` ( -- patron opac comments `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` @@ -1999,9 +1981,9 @@ CREATE TABLE `special_holidays` ( -- non repeatable holidays/library closings `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` @@ -2013,15 +1995,15 @@ CREATE TABLE `statistics` ( -- information related to transactions (circulation `branch` varchar(10) default NULL, -- foreign key, branch where the transaction occurred `proccode` varchar(4) default NULL, -- type of procedure used when making payments (does not appear in the code) `value` double(16,4) default NULL, -- monetary value associated with the transaction - `type` varchar(16) default NULL, -- transaction type (locause, issue, return, renew, writeoff, payment, Credit*) - `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`), @@ -2032,7 +2014,7 @@ CREATE TABLE `statistics` ( -- information related to transactions (circulation 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 @@ -2041,13 +2023,13 @@ CREATE TABLE `statistics` ( -- information related to transactions (circulation 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 @@ -2058,7 +2040,7 @@ CREATE 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, @@ -2079,7 +2061,7 @@ CREATE TABLE subscription_numberpatterns ( 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` @@ -2099,7 +2081,7 @@ CREATE TABLE `subscription` ( -- information related to the 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, @@ -2109,14 +2091,14 @@ CREATE TABLE `subscription` ( -- information related to the subscription `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), @@ -2130,9 +2112,10 @@ CREATE TABLE `subscription` ( -- information related to the subscription `itemtype` VARCHAR( 10 ) NULL, `previousitemtype` VARCHAR( 10 ) NULL, PRIMARY KEY (`subscriptionid`), + KEY `by_biblionumber` (`biblionumber`), CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ON DELETE SET NULL ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table `subscriptionhistory` @@ -2144,13 +2127,13 @@ CREATE 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` @@ -2168,7 +2151,7 @@ CREATE TABLE `subscriptionroutinglist` ( -- information related to the routing l 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` @@ -2177,12 +2160,12 @@ CREATE TABLE `subscriptionroutinglist` ( -- information related to the routing l 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` @@ -2192,8 +2175,8 @@ DROP TABLE IF EXISTS `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` @@ -2214,7 +2197,7 @@ CREATE TABLE `tags_all` ( -- all of the tags 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` @@ -2222,7 +2205,7 @@ CREATE TABLE `tags_all` ( -- all of the tags DROP TABLE IF EXISTS `tags_approval`; CREATE TABLE `tags_approval` ( -- approved tags - `term` varchar(255) NOT NULL, -- the tag + `term` varchar(191) NOT NULL, -- 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) @@ -2231,7 +2214,7 @@ CREATE TABLE `tags_approval` ( -- approved tags 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` @@ -2239,7 +2222,7 @@ CREATE TABLE `tags_approval` ( -- approved tags 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, -- 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`), @@ -2248,7 +2231,7 @@ CREATE TABLE `tags_index` ( -- a weighted list of all tags and where they are us 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` @@ -2261,7 +2244,7 @@ CREATE 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` @@ -2280,7 +2263,7 @@ CREATE TABLE `virtualshelves` ( -- information about lists (or virtual shelves) `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` @@ -2298,7 +2281,7 @@ CREATE TABLE `virtualshelfcontents` ( -- information about the titles in a list 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` @@ -2313,7 +2296,7 @@ CREATE TABLE `virtualshelfshares` ( -- shared private lists `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` @@ -2327,19 +2310,19 @@ CREATE TABLE `z3950servers` ( -- connection information for the Z39.50 targets u `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` @@ -2355,18 +2338,7 @@ CREATE 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` @@ -2383,7 +2355,7 @@ CREATE 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` @@ -2399,7 +2371,7 @@ CREATE 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` @@ -2415,7 +2387,7 @@ CREATE 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` @@ -2427,7 +2399,7 @@ CREATE 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` @@ -2440,7 +2412,7 @@ CREATE 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` @@ -2454,7 +2426,7 @@ CREATE 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` @@ -2468,7 +2440,7 @@ CREATE 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` @@ -2483,7 +2455,7 @@ CREATE 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` @@ -2494,19 +2466,19 @@ CREATE 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` @@ -2516,7 +2488,7 @@ DROP TABLE IF EXISTS `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` @@ -2526,22 +2498,22 @@ DROP TABLE IF EXISTS `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` @@ -2555,13 +2527,13 @@ CREATE TABLE `letter` ( -- table for all notice templates in Koha `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` @@ -2576,7 +2548,7 @@ CREATE 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` @@ -2589,7 +2561,7 @@ CREATE 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` @@ -2608,7 +2580,7 @@ CREATE 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` @@ -2626,7 +2598,7 @@ CREATE TABLE IF NOT EXISTS `borrower_files` ( -- files attached to the patron/bo 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` @@ -2647,7 +2619,7 @@ CREATE 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` @@ -2661,7 +2633,7 @@ CREATE 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 @@ -2673,9 +2645,9 @@ CREATE TABLE branch_transfer_limits ( toBranch varchar(10) NOT NULL, fromBranch varchar(10) NOT NULL, itemtype varchar(10) NULL, - ccode varchar(10) NULL, + ccode varchar(80) NULL, PRIMARY KEY (limitId) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table `item_circulation_alert_preferences` @@ -2690,7 +2662,7 @@ CREATE 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` @@ -2701,12 +2673,12 @@ CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou `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; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table `accountlines` @@ -2721,15 +2693,13 @@ CREATE TABLE `accountlines` ( `itemnumber` int(11) default NULL, `date` date default NULL, `amount` decimal(28,6) default NULL, - `description` mediumtext, - `dispute` mediumtext, + `description` LONGTEXT, `accounttype` varchar(5) default NULL, + `payment_type` varchar(80) default NULL, -- optional authorised value PAYMENT_TYPE `amountoutstanding` decimal(28,6) default NULL, `lastincrement` decimal(28,6) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `notify_id` int(11) NOT NULL default 0, - `notify_level` int(2) NOT NULL default 0, - `note` text NULL default NULL, + `note` MEDIUMTEXT NULL default NULL, `manager_id` int(11) NULL, PRIMARY KEY (`accountlines_id`), KEY `acctsborridx` (`borrowernumber`), @@ -2737,7 +2707,7 @@ CREATE TABLE `accountlines` ( 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; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table `account_offset_types` @@ -2747,7 +2717,7 @@ DROP TABLE IF EXISTS `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` @@ -2765,7 +2735,7 @@ CREATE 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` @@ -2776,10 +2746,10 @@ CREATE TABLE `action_logs` ( -- logs of actions taken in Koha (requires that the `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`), @@ -2789,7 +2759,7 @@ CREATE TABLE `action_logs` ( -- logs of actions taken in Koha (requires that the 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` @@ -2804,7 +2774,7 @@ CREATE 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` @@ -2813,20 +2783,20 @@ CREATE TABLE `alert` ( 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 @@ -2844,7 +2814,7 @@ CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquis 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` @@ -2857,13 +2827,13 @@ CREATE 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` @@ -2877,9 +2847,9 @@ CREATE TABLE `aqbudgets` ( -- information related to Funds `budget_name` varchar(80) default NULL, -- name assigned to the fund by the user `budget_branchcode` varchar(10) default NULL, -- branch that this fund belongs to (branches.branchcode) `budget_amount` decimal(28,6) NULL default '0.00', -- total amount for this fund - `budget_encumb` decimal(28,6) NULL default '0.00', -- not used in the code - `budget_expend` decimal(28,6) NULL default '0.00', -- not used in the code - `budget_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 @@ -2892,7 +2862,7 @@ CREATE TABLE `aqbudgets` ( -- information related to Funds 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 @@ -2909,7 +2879,7 @@ CREATE 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` @@ -2922,13 +2892,13 @@ CREATE TABLE `aqbudgetperiods` ( -- information related to Budgets `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` @@ -2946,7 +2916,7 @@ CREATE 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' @@ -2961,7 +2931,7 @@ CREATE 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 @@ -2971,7 +2941,7 @@ CREATE TABLE aqcontacts ( 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' @@ -2983,12 +2953,12 @@ CREATE 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` @@ -2998,14 +2968,14 @@ DROP TABLE IF EXISTS `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 @@ -3021,7 +2991,7 @@ CREATE TABLE `aqbasket` ( -- stores data about baskets in acquisitions 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 @@ -3034,7 +3004,7 @@ CREATE 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` @@ -3052,7 +3022,7 @@ CREATE TABLE `suggestions` ( -- purchase suggestions 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 @@ -3063,11 +3033,11 @@ CREATE TABLE `suggestions` ( -- purchase suggestions `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 @@ -3080,7 +3050,7 @@ CREATE TABLE `suggestions` ( -- purchase suggestions KEY `biblionumber` (`biblionumber`), 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; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table vendor_edi_accounts @@ -3089,14 +3059,14 @@ CREATE TABLE `suggestions` ( -- purchase suggestions 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', @@ -3112,7 +3082,7 @@ CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( 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 @@ -3125,10 +3095,10 @@ CREATE TABLE IF NOT EXISTS 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), @@ -3137,7 +3107,7 @@ CREATE TABLE IF NOT EXISTS edifact_messages ( 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 @@ -3146,7 +3116,7 @@ CREATE TABLE IF NOT EXISTS edifact_messages ( 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 @@ -3158,7 +3128,26 @@ CREATE TABLE aqinvoices ( 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` @@ -3179,14 +3168,16 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `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 @@ -3214,20 +3205,21 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items 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` @@ -3240,7 +3232,7 @@ CREATE TABLE aqorder_users ( -- Mapping orders to patrons for notification sendi 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` @@ -3254,7 +3246,7 @@ CREATE TABLE `aqorders_items` ( -- information on items entered in the acquisiti 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 @@ -3269,7 +3261,7 @@ CREATE 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` @@ -3283,7 +3275,7 @@ CREATE TABLE `fieldmapping` ( -- koha to keyword mapping `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` @@ -3299,7 +3291,7 @@ CREATE 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` @@ -3315,7 +3307,7 @@ CREATE TABLE `biblioimages` ( -- local cover images `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` @@ -3331,7 +3323,7 @@ CREATE TABLE IF NOT EXISTS `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. @@ -3346,7 +3338,7 @@ CREATE TABLE ratings ( -- information related to the star ratings in the OPAC 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` @@ -3355,11 +3347,11 @@ CREATE TABLE ratings ( -- information related to the star ratings in the OPAC 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 @@ -3371,7 +3363,7 @@ CREATE TABLE categories_branches( -- association table between categories and br 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 @@ -3383,7 +3375,7 @@ CREATE TABLE authorised_values_branches( -- association table between authorised 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; -- @@ -3396,7 +3388,7 @@ CREATE TABLE borrower_attribute_types_branches( -- association table between bor 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` @@ -3407,35 +3399,35 @@ CREATE TABLE IF NOT EXISTS `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, @@ -3446,17 +3438,17 @@ CREATE TABLE IF NOT EXISTS `borrower_modifications` ( `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, @@ -3465,17 +3457,18 @@ CREATE TABLE IF NOT EXISTS `borrower_modifications` ( `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 @@ -3485,16 +3478,16 @@ DROP TABLE IF EXISTS 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 @@ -3507,14 +3500,14 @@ CREATE 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' @@ -3523,9 +3516,24 @@ CREATE TABLE linktracker ( 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` @@ -3534,11 +3542,12 @@ CREATE TABLE IF NOT EXISTS plugin_data ( 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` @@ -3558,7 +3567,7 @@ CREATE TABLE patron_list_patrons ( 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` @@ -3573,9 +3582,9 @@ ALTER 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' @@ -3585,26 +3594,26 @@ CREATE TABLE IF NOT EXISTS marc_modification_template_actions ( mmta_id int(11) NOT NULL AUTO_INCREMENT, template_id int(11) NOT NULL, ordering int(3) NOT NULL, - action ENUM('delete_field','update_field','move_field','copy_field','copy_and_replace_field') NOT NULL, + action ENUM('delete_field','add_field','update_field','move_field','copy_field','copy_and_replace_field') NOT NULL, field_number smallint(6) NOT NULL DEFAULT '0', from_field varchar(3) NOT NULL, from_subfield varchar(1) DEFAULT NULL, 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` @@ -3622,7 +3631,7 @@ CREATE TABLE IF NOT EXISTS `misc_files` ( -- miscellaneous files attached to rec 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` @@ -3635,8 +3644,8 @@ CREATE TABLE IF NOT EXISTS 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' @@ -3649,11 +3658,11 @@ CREATE 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' @@ -3668,7 +3677,7 @@ CREATE 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 @@ -3684,8 +3693,8 @@ CREATE 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 @@ -3701,7 +3710,7 @@ CREATE 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' @@ -3710,13 +3719,13 @@ CREATE TABLE `additional_field_values` ( 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' @@ -3730,7 +3739,7 @@ CREATE 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' @@ -3740,11 +3749,11 @@ DROP TABLE IF EXISTS edifact_ean; CREATE TABLE IF NOT EXISTS edifact_ean ( ee_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, description VARCHAR(128) NULL DEFAULT NULL, - branchcode VARCHAR(10) NOT NULL REFERENCES branches (branchcode), + branchcode VARCHAR(10) NULL DEFAULT NULL REFERENCES branches (branchcode), ean VARCHAR(15) NOT NULL, id_code_qualifier VARCHAR(3) NOT NULL DEFAULT '14', CONSTRAINT efk_branchcode FOREIGN KEY ( branchcode ) REFERENCES branches ( branchcode ) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table `courses` @@ -3761,13 +3770,13 @@ CREATE 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` @@ -3783,7 +3792,7 @@ CREATE 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` @@ -3805,7 +3814,7 @@ CREATE TABLE `course_items` ( `ci_id` int(11) NOT NULL AUTO_INCREMENT, -- course item id `itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve `itype` varchar(10) DEFAULT NULL, -- new itemtype for the item to have while on reserve (optional) - `ccode` varchar(10) DEFAULT NULL, -- new category code for the item to have while on reserve (optional) + `ccode` varchar(80) DEFAULT NULL, -- new category code for the item to have while on reserve (optional) `holdingbranch` varchar(10) DEFAULT NULL, -- new holding branch for the item to have while on reserve (optional) `location` varchar(80) DEFAULT NULL, -- new shelving location for the item to have while on reseve (optional) `enabled` enum('yes','no') NOT NULL DEFAULT 'no', -- if at least one enabled course has this item on reseve, this field will be 'yes', otherwise it will be 'no' @@ -3813,7 +3822,7 @@ CREATE TABLE `course_items` ( 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` @@ -3834,13 +3843,13 @@ CREATE TABLE `course_reserves` ( `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` @@ -3870,7 +3879,7 @@ CREATE TABLE `hold_fill_targets` ( 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` @@ -3879,19 +3888,19 @@ CREATE TABLE `hold_fill_targets` ( 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` @@ -3918,7 +3927,7 @@ CREATE 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` @@ -3934,7 +3943,7 @@ CREATE TABLE IF NOT EXISTS `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' @@ -3946,17 +3955,17 @@ CREATE 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`), @@ -3968,7 +3977,7 @@ CREATE TABLE `article_requests` ( 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` @@ -3981,10 +3990,12 @@ CREATE TABLE biblio_metadata ( `format` VARCHAR(16) NOT NULL, `marcflavour` 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`), - CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, + KEY `timestamp` (`timestamp`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table `deletedbiblio_metadata` @@ -3997,10 +4008,12 @@ CREATE TABLE deletedbiblio_metadata ( `format` VARCHAR(16) NOT NULL, `marcflavour` 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`), - CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, + KEY `timestamp` (`timestamp`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table 'club_templates' @@ -4008,8 +4021,8 @@ CREATE TABLE deletedbiblio_metadata ( 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, @@ -4019,7 +4032,7 @@ CREATE TABLE IF NOT EXISTS club_templates ( 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' @@ -4028,8 +4041,8 @@ CREATE TABLE IF NOT EXISTS club_templates ( 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, @@ -4040,7 +4053,7 @@ CREATE TABLE IF NOT EXISTS clubs ( 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' @@ -4052,7 +4065,7 @@ CREATE TABLE IF NOT EXISTS club_enrollments ( borrowernumber int(11) NOT NULL, date_enrolled timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, date_canceled timestamp NULL DEFAULT NULL, - date_created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + date_created timestamp NULL DEFAULT NULL, date_updated timestamp NULL DEFAULT NULL, branchcode varchar(10) NULL DEFAULT NULL, PRIMARY KEY (id), @@ -4062,7 +4075,7 @@ CREATE TABLE IF NOT EXISTS 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' @@ -4071,13 +4084,13 @@ CREATE TABLE IF NOT EXISTS club_enrollments ( 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' @@ -4087,13 +4100,13 @@ CREATE TABLE IF NOT EXISTS 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' @@ -4102,13 +4115,13 @@ CREATE TABLE IF NOT EXISTS club_enrollment_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' @@ -4118,13 +4131,194 @@ CREATE TABLE IF NOT EXISTS 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` +-- + +DROP TABLE IF EXISTS `illrequests`; +CREATE TABLE illrequests ( + illrequest_id serial PRIMARY KEY, -- ILL request number + borrowernumber integer DEFAULT NULL, -- Patron associated with request + 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 + 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 + ON UPDATE CURRENT_TIMESTAMP, + 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, -- 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` + FOREIGN KEY (`borrowernumber`) + REFERENCES `borrowers` (`borrowernumber`) + ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT `illrequests_bcfk_2` + FOREIGN KEY (`branchcode`) + REFERENCES `branches` (`branchcode`) + ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `illrequestattributes` +-- + +DROP TABLE IF EXISTS `illrequestattributes`; +CREATE TABLE illrequestattributes ( + illrequest_id bigint(20) unsigned NOT NULL, -- ILL request number + type varchar(200) NOT NULL, -- API ILL property name + value MEDIUMTEXT NOT NULL, -- API ILL property value + 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=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table 'library_groups' +-- + +DROP TABLE IF EXISTS `library_groups`; +CREATE TABLE library_groups ( + id INT(11) NOT NULL auto_increment, -- unique id for each group + 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 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 + 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=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` +-- + +CREATE TABLE IF NOT EXISTS stockrotationrotas ( + rota_id int(11) auto_increment, -- Stockrotation rota ID + title varchar(100) NOT NULL, -- Title for this rota + description text NOT NULL, -- Description for this rota + cyclical tinyint(1) NOT NULL default 0, -- Should items on this rota keep cycling? + active tinyint(1) NOT NULL default 0, -- Is this rota currently active? + PRIMARY KEY (`rota_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `stockrotationstages` +-- + +CREATE TABLE IF NOT EXISTS stockrotationstages ( + stage_id int(11) auto_increment, -- Unique stage ID + position int(11) NOT NULL, -- The position of this stage within its rota + rota_id int(11) NOT NULL, -- The rota this stage belongs to + branchcode_id varchar(10) NOT NULL, -- Branch this stage relates to + duration int(11) NOT NULL default 4, -- The number of days items shoud occupy this stage + PRIMARY KEY (`stage_id`), + CONSTRAINT `stockrotationstages_rifk` + FOREIGN KEY (`rota_id`) + REFERENCES `stockrotationrotas` (`rota_id`) + ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT `stockrotationstages_bifk` + FOREIGN KEY (`branchcode_id`) + REFERENCES `branches` (`branchcode`) + ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `stockrotationitems` +-- + +CREATE TABLE IF NOT EXISTS stockrotationitems ( + itemnumber_id int(11) NOT NULL, -- Itemnumber to link to a stage & rota + stage_id int(11) NOT NULL, -- stage ID to link the item to + indemand tinyint(1) NOT NULL default 0, -- Should this item be skipped for rotation? + fresh tinyint(1) NOT NULL default 0, -- Flag showing item is only just added to rota + PRIMARY KEY (itemnumber_id), + CONSTRAINT `stockrotationitems_iifk` + FOREIGN KEY (`itemnumber_id`) + REFERENCES `items` (`itemnumber`) + ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT `stockrotationitems_sifk` + FOREIGN KEY (`stage_id`) + REFERENCES `stockrotationstages` (`stage_id`) + ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;