View | Details | Raw Unified | Return to bug 18336
Collapse All | Expand All

(-)a/installer/data/mysql/kohastructure.sql (-311 / +310 lines)
Lines 26-35 CREATE TABLE `auth_header` ( Link Here
26
  `datecreated` date default NULL,
26
  `datecreated` date default NULL,
27
  `modification_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
27
  `modification_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
28
  `origincode` varchar(20) default NULL,
28
  `origincode` varchar(20) default NULL,
29
  `authtrees` mediumtext,
29
  `authtrees` LONGTEXT,
30
  `marc` blob,
30
  `marc` blob,
31
  `linkid` bigint(20) default NULL,
31
  `linkid` bigint(20) default NULL,
32
  `marcxml` longtext NOT NULL,
32
  `marcxml` LONGTEXT NOT NULL,
33
  PRIMARY KEY  (`authid`),
33
  PRIMARY KEY  (`authid`),
34
  KEY `origincode` (`origincode`)
34
  KEY `origincode` (`origincode`)
35
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
35
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Lines 43-49 CREATE TABLE `auth_types` ( Link Here
43
  `authtypecode` varchar(10) NOT NULL default '',
43
  `authtypecode` varchar(10) NOT NULL default '',
44
  `authtypetext` varchar(255) NOT NULL default '',
44
  `authtypetext` varchar(255) NOT NULL default '',
45
  `auth_tag_to_report` varchar(3) NOT NULL default '',
45
  `auth_tag_to_report` varchar(3) NOT NULL default '',
46
  `summary` mediumtext NOT NULL,
46
  `summary` LONGTEXT NOT NULL,
47
  PRIMARY KEY  (`authtypecode`)
47
  PRIMARY KEY  (`authtypecode`)
48
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
48
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
49
49
Lines 69-75 CREATE TABLE `auth_subfield_structure` ( Link Here
69
  `linkid` tinyint(1) NOT NULL default 0,
69
  `linkid` tinyint(1) NOT NULL default 0,
70
  `kohafield` varchar(45) NULL default '',
70
  `kohafield` varchar(45) NULL default '',
71
  `frameworkcode` varchar(10) NOT NULL default '',
71
  `frameworkcode` varchar(10) NOT NULL default '',
72
  `defaultvalue` TEXT,
72
  `defaultvalue` MEDIUMTEXT,
73
  PRIMARY KEY  (`authtypecode`,`tagfield`,`tagsubfield`),
73
  PRIMARY KEY  (`authtypecode`,`tagfield`,`tagsubfield`),
74
  KEY `tab` (`authtypecode`,`tab`),
74
  KEY `tab` (`authtypecode`,`tab`),
75
  CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
75
  CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
Lines 130-145 DROP TABLE IF EXISTS `biblio`; Link Here
130
CREATE TABLE `biblio` ( -- table that stores bibliographic information
130
CREATE TABLE `biblio` ( -- table that stores bibliographic information
131
  `biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
131
  `biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
132
  `frameworkcode` varchar(4) NOT NULL default '', -- foreign key from the biblio_framework table to identify which framework was used in cataloging this record
132
  `frameworkcode` varchar(4) NOT NULL default '', -- foreign key from the biblio_framework table to identify which framework was used in cataloging this record
133
  `author` mediumtext, -- statement of responsibility from MARC record (100$a in MARC21)
133
  `author` LONGTEXT, -- statement of responsibility from MARC record (100$a in MARC21)
134
  `title` mediumtext, -- title (without the subtitle) from the MARC record (245$a in MARC21)
134
  `title` LONGTEXT, -- title (without the subtitle) from the MARC record (245$a in MARC21)
135
  `unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
135
  `unititle` LONGTEXT, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
136
  `notes` mediumtext, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
136
  `notes` LONGTEXT, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
137
  `serial` tinyint(1) default NULL, -- Boolean indicating whether biblio is for a serial
137
  `serial` tinyint(1) default NULL, -- Boolean indicating whether biblio is for a serial
138
  `seriestitle` mediumtext,
138
  `seriestitle` LONGTEXT,
139
  `copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
139
  `copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
140
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
140
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
141
  `datecreated` DATE NOT NULL, -- the date this record was added to Koha
141
  `datecreated` DATE NOT NULL, -- the date this record was added to Koha
142
  `abstract` mediumtext, -- summary from the MARC record (520$a in MARC21)
142
  `abstract` LONGTEXT, -- summary from the MARC record (520$a in MARC21)
143
  PRIMARY KEY  (`biblionumber`),
143
  PRIMARY KEY  (`biblionumber`),
144
  KEY `blbnoidx` (`biblionumber`)
144
  KEY `blbnoidx` (`biblionumber`)
145
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
145
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Lines 163-191 DROP TABLE IF EXISTS `biblioitems`; Link Here
163
CREATE TABLE `biblioitems` ( -- information related to bibliographic records in Koha
163
CREATE TABLE `biblioitems` ( -- information related to bibliographic records in Koha
164
  `biblioitemnumber` int(11) NOT NULL auto_increment, -- primary key, unique identifier assigned by Koha
164
  `biblioitemnumber` int(11) NOT NULL auto_increment, -- primary key, unique identifier assigned by Koha
165
  `biblionumber` int(11) NOT NULL default 0, -- foreign key linking this table to the biblio table
165
  `biblionumber` int(11) NOT NULL default 0, -- foreign key linking this table to the biblio table
166
  `volume` mediumtext,
166
  `volume` LONGTEXT,
167
  `number` mediumtext,
167
  `number` LONGTEXT,
168
  `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
168
  `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
169
  `isbn` mediumtext, -- ISBN (MARC21 020$a)
169
  `isbn` LONGTEXT, -- ISBN (MARC21 020$a)
170
  `issn` mediumtext, -- ISSN (MARC21 022$a)
170
  `issn` LONGTEXT, -- ISSN (MARC21 022$a)
171
  `ean` mediumtext default NULL,
171
  `ean` LONGTEXT default NULL,
172
  `publicationyear` text,
172
  `publicationyear` MEDIUMTEXT,
173
  `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
173
  `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
174
  `volumedate` date default NULL,
174
  `volumedate` date default NULL,
175
  `volumedesc` text, -- volume information (MARC21 362$a)
175
  `volumedesc` MEDIUMTEXT, -- volume information (MARC21 362$a)
176
  `collectiontitle` mediumtext default NULL,
176
  `collectiontitle` LONGTEXT default NULL,
177
  `collectionissn` text default NULL,
177
  `collectionissn` MEDIUMTEXT default NULL,
178
  `collectionvolume` mediumtext default NULL,
178
  `collectionvolume` LONGTEXT default NULL,
179
  `editionstatement` text default NULL,
179
  `editionstatement` MEDIUMTEXT default NULL,
180
  `editionresponsibility` text default NULL,
180
  `editionresponsibility` MEDIUMTEXT default NULL,
181
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
181
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
182
  `illus` varchar(255) default NULL, -- illustrations (MARC21 300$b)
182
  `illus` varchar(255) default NULL, -- illustrations (MARC21 300$b)
183
  `pages` varchar(255) default NULL, -- number of pages (MARC21 300$c)
183
  `pages` varchar(255) default NULL, -- number of pages (MARC21 300$c)
184
  `notes` mediumtext,
184
  `notes` LONGTEXT,
185
  `size` varchar(255) default NULL, -- material size (MARC21 300$c)
185
  `size` varchar(255) default NULL, -- material size (MARC21 300$c)
186
  `place` varchar(255) default NULL, -- publication place (MARC21 260$a)
186
  `place` varchar(255) default NULL, -- publication place (MARC21 260$a)
187
  `lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
187
  `lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
188
  `url` text default NULL, -- url (MARC21 856$u)
188
  `url` MEDIUMTEXT default NULL, -- url (MARC21 856$u)
189
  `cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
189
  `cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
190
  `cn_class` varchar(30) default NULL,
190
  `cn_class` varchar(30) default NULL,
191
  `cn_item` varchar(10) default NULL,
191
  `cn_item` varchar(10) default NULL,
Lines 247-253 DROP TABLE IF EXISTS `branchcategories`; Link Here
247
CREATE TABLE `branchcategories` ( -- information related to library/branch groups
247
CREATE TABLE `branchcategories` ( -- information related to library/branch groups
248
  `categorycode` varchar(10) NOT NULL default '', -- unique identifier for the library/branch group
248
  `categorycode` varchar(10) NOT NULL default '', -- unique identifier for the library/branch group
249
  `categoryname` varchar(32), -- name of the library/branch group
249
  `categoryname` varchar(32), -- name of the library/branch group
250
  `codedescription` mediumtext, -- longer description of the library/branch group
250
  `codedescription` LONGTEXT, -- longer description of the library/branch group
251
  `categorytype` varchar(16), -- says whether this is a search group or a properties group
251
  `categorytype` varchar(16), -- says whether this is a search group or a properties group
252
  `show_in_pulldown` tinyint(1) NOT NULL DEFAULT '0', -- says this group should be in the opac libararies pulldown if it is enabled
252
  `show_in_pulldown` tinyint(1) NOT NULL DEFAULT '0', -- says this group should be in the opac libararies pulldown if it is enabled
253
  PRIMARY KEY  (`categorycode`),
253
  PRIMARY KEY  (`categorycode`),
Lines 261-285 CREATE TABLE `branchcategories` ( -- information related to library/branch group Link Here
261
DROP TABLE IF EXISTS `branches`;
261
DROP TABLE IF EXISTS `branches`;
262
CREATE TABLE `branches` ( -- information about your libraries or branches are stored here
262
CREATE TABLE `branches` ( -- information about your libraries or branches are stored here
263
  `branchcode` varchar(10) NOT NULL default '', -- a unique key assigned to each branch
263
  `branchcode` varchar(10) NOT NULL default '', -- a unique key assigned to each branch
264
  `branchname` mediumtext NOT NULL, -- the name of your library or branch
264
  `branchname` LONGTEXT NOT NULL, -- the name of your library or branch
265
  `branchaddress1` mediumtext, -- the first address line of for your library or branch
265
  `branchaddress1` LONGTEXT, -- the first address line of for your library or branch
266
  `branchaddress2` mediumtext, -- the second address line of for your library or branch
266
  `branchaddress2` LONGTEXT, -- the second address line of for your library or branch
267
  `branchaddress3` mediumtext, -- the third address line of for your library or branch
267
  `branchaddress3` LONGTEXT, -- the third address line of for your library or branch
268
  `branchzip` varchar(25) default NULL, -- the zip or postal code for your library or branch
268
  `branchzip` varchar(25) default NULL, -- the zip or postal code for your library or branch
269
  `branchcity` mediumtext, -- the city or province for your library or branch
269
  `branchcity` LONGTEXT, -- the city or province for your library or branch
270
  `branchstate` mediumtext, -- the state for your library or branch
270
  `branchstate` LONGTEXT, -- the state for your library or branch
271
  `branchcountry` text, -- the county for your library or branch
271
  `branchcountry` MEDIUMTEXT, -- the county for your library or branch
272
  `branchphone` mediumtext, -- the primary phone for your library or branch
272
  `branchphone` LONGTEXT, -- the primary phone for your library or branch
273
  `branchfax` mediumtext, -- the fax number for your library or branch
273
  `branchfax` LONGTEXT, -- the fax number for your library or branch
274
  `branchemail` mediumtext, -- the primary email address for your library or branch
274
  `branchemail` LONGTEXT, -- the primary email address for your library or branch
275
  `branchreplyto` mediumtext, -- the email to be used as a Reply-To
275
  `branchreplyto` LONGTEXT, -- the email to be used as a Reply-To
276
  `branchreturnpath` mediumtext, -- the email to be used as Return-Path
276
  `branchreturnpath` LONGTEXT, -- the email to be used as Return-Path
277
  `branchurl` mediumtext, -- the URL for your library or branch's website
277
  `branchurl` LONGTEXT, -- the URL for your library or branch's website
278
  `issuing` tinyint(4) default NULL, -- unused in Koha
278
  `issuing` tinyint(4) default NULL, -- unused in Koha
279
  `branchip` varchar(15) default NULL, -- the IP address for your library or branch
279
  `branchip` varchar(15) default NULL, -- the IP address for your library or branch
280
  `branchprinter` varchar(100) default NULL, -- unused in Koha
280
  `branchprinter` varchar(100) default NULL, -- unused in Koha
281
  `branchnotes` mediumtext, -- notes related to your library or branch
281
  `branchnotes` LONGTEXT, -- notes related to your library or branch
282
  opac_info text, -- HTML that displays in OPAC
282
  opac_info MEDIUMTEXT, -- HTML that displays in OPAC
283
  `geolocation` VARCHAR(255) default NULL, -- geolocation of your library
283
  `geolocation` VARCHAR(255) default NULL, -- geolocation of your library
284
  `marcorgcode` VARCHAR(16) default NULL, -- MARC Organization Code, see http://www.loc.gov/marc/organizations/orgshome.html, when empty defaults to syspref MARCOrgCode
284
  `marcorgcode` VARCHAR(16) default NULL, -- MARC Organization Code, see http://www.loc.gov/marc/organizations/orgshome.html, when empty defaults to syspref MARCOrgCode
285
  PRIMARY KEY (`branchcode`)
285
  PRIMARY KEY (`branchcode`)
Lines 319-325 CREATE TABLE `browser` ( Link Here
319
DROP TABLE IF EXISTS `categories`;
319
DROP TABLE IF EXISTS `categories`;
320
CREATE TABLE `categories` ( -- this table shows information related to Koha patron categories
320
CREATE TABLE `categories` ( -- this table shows information related to Koha patron categories
321
  `categorycode` varchar(10) NOT NULL default '', -- unique primary key used to idenfity the patron category
321
  `categorycode` varchar(10) NOT NULL default '', -- unique primary key used to idenfity the patron category
322
  `description` mediumtext, -- description of the patron category
322
  `description` LONGTEXT, -- description of the patron category
323
  `enrolmentperiod` smallint(6) default NULL, -- number of months the patron is enrolled for (will be NULL if enrolmentperioddate is set)
323
  `enrolmentperiod` smallint(6) default NULL, -- number of months the patron is enrolled for (will be NULL if enrolmentperioddate is set)
324
  `enrolmentperioddate` DATE NULL DEFAULT NULL, -- date the patron is enrolled until (will be NULL if enrolmentperiod is set)
324
  `enrolmentperioddate` DATE NULL DEFAULT NULL, -- date the patron is enrolled until (will be NULL if enrolmentperiod is set)
325
  `upperagelimit` smallint(6) default NULL, -- age limit for the patron
325
  `upperagelimit` smallint(6) default NULL, -- age limit for the patron
Lines 346-352 DROP TABLE IF EXISTS collections; Link Here
346
CREATE TABLE collections (
346
CREATE TABLE collections (
347
  colId integer(11) NOT NULL auto_increment,
347
  colId integer(11) NOT NULL auto_increment,
348
  colTitle varchar(100) NOT NULL DEFAULT '',
348
  colTitle varchar(100) NOT NULL DEFAULT '',
349
  colDesc text NOT NULL,
349
  colDesc MEDIUMTEXT NOT NULL,
350
  colBranchcode varchar(10) DEFAULT NULL, -- 'branchcode for branch where item should be held.'
350
  colBranchcode varchar(10) DEFAULT NULL, -- 'branchcode for branch where item should be held.'
351
  PRIMARY KEY (colId)
351
  PRIMARY KEY (colId)
352
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
352
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Lines 453-459 CREATE TABLE `cities` ( -- authorized values for cities/states/countries to choo Link Here
453
DROP TABLE IF EXISTS `class_sort_rules`;
453
DROP TABLE IF EXISTS `class_sort_rules`;
454
CREATE TABLE `class_sort_rules` (
454
CREATE TABLE `class_sort_rules` (
455
  `class_sort_rule` varchar(10) NOT NULL default '',
455
  `class_sort_rule` varchar(10) NOT NULL default '',
456
  `description` mediumtext,
456
  `description` LONGTEXT,
457
  `sort_routine` varchar(30) NOT NULL default '',
457
  `sort_routine` varchar(30) NOT NULL default '',
458
  PRIMARY KEY (`class_sort_rule`),
458
  PRIMARY KEY (`class_sort_rule`),
459
  UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
459
  UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
Lines 466-472 CREATE TABLE `class_sort_rules` ( Link Here
466
DROP TABLE IF EXISTS `class_sources`;
466
DROP TABLE IF EXISTS `class_sources`;
467
CREATE TABLE `class_sources` (
467
CREATE TABLE `class_sources` (
468
  `cn_source` varchar(10) NOT NULL default '',
468
  `cn_source` varchar(10) NOT NULL default '',
469
  `description` mediumtext,
469
  `description` LONGTEXT,
470
  `used` tinyint(4) NOT NULL default 0,
470
  `used` tinyint(4) NOT NULL default 0,
471
  `class_sort_rule` varchar(10) NOT NULL default '',
471
  `class_sort_rule` varchar(10) NOT NULL default '',
472
  PRIMARY KEY (`cn_source`),
472
  PRIMARY KEY (`cn_source`),
Lines 499-514 DROP TABLE IF EXISTS `deletedbiblio`; Link Here
499
CREATE TABLE `deletedbiblio` ( -- stores information about bibliographic records that have been deleted
499
CREATE TABLE `deletedbiblio` ( -- stores information about bibliographic records that have been deleted
500
  `biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
500
  `biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
501
  `frameworkcode` varchar(4) NOT NULL default '', -- foriegn key from the biblio_framework table to identify which framework was used in cataloging this record
501
  `frameworkcode` varchar(4) NOT NULL default '', -- foriegn key from the biblio_framework table to identify which framework was used in cataloging this record
502
  `author` mediumtext, -- statement of responsibility from MARC record (100$a in MARC21)
502
  `author` LONGTEXT, -- statement of responsibility from MARC record (100$a in MARC21)
503
  `title` mediumtext, -- title (without the subtitle) from the MARC record (245$a in MARC21)
503
  `title` LONGTEXT, -- title (without the subtitle) from the MARC record (245$a in MARC21)
504
  `unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
504
  `unititle` LONGTEXT, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
505
  `notes` mediumtext, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
505
  `notes` LONGTEXT, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
506
  `serial` tinyint(1) default NULL, -- Boolean indicating whether biblio is for a serial
506
  `serial` tinyint(1) default NULL, -- Boolean indicating whether biblio is for a serial
507
  `seriestitle` mediumtext,
507
  `seriestitle` LONGTEXT,
508
  `copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
508
  `copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
509
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
509
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
510
  `datecreated` DATE NOT NULL, -- the date this record was added to Koha
510
  `datecreated` DATE NOT NULL, -- the date this record was added to Koha
511
  `abstract` mediumtext, -- summary from the MARC record (520$a in MARC21)
511
  `abstract` LONGTEXT, -- summary from the MARC record (520$a in MARC21)
512
  PRIMARY KEY  (`biblionumber`),
512
  PRIMARY KEY  (`biblionumber`),
513
  KEY `blbnoidx` (`biblionumber`)
513
  KEY `blbnoidx` (`biblionumber`)
514
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
514
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Lines 521-549 DROP TABLE IF EXISTS `deletedbiblioitems`; Link Here
521
CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records that have been deleted
521
CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records that have been deleted
522
  `biblioitemnumber` int(11) NOT NULL default 0, -- primary key, unique identifier assigned by Koha
522
  `biblioitemnumber` int(11) NOT NULL default 0, -- primary key, unique identifier assigned by Koha
523
  `biblionumber` int(11) NOT NULL default 0, -- foreign key linking this table to the biblio table
523
  `biblionumber` int(11) NOT NULL default 0, -- foreign key linking this table to the biblio table
524
  `volume` mediumtext,
524
  `volume` LONGTEXT,
525
  `number` mediumtext,
525
  `number` LONGTEXT,
526
  `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
526
  `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
527
  `isbn` mediumtext default NULL, -- ISBN (MARC21 020$a)
527
  `isbn` LONGTEXT default NULL, -- ISBN (MARC21 020$a)
528
  `issn` mediumtext default NULL, -- ISSN (MARC21 022$a)
528
  `issn` LONGTEXT default NULL, -- ISSN (MARC21 022$a)
529
  `ean` mediumtext default NULL,
529
  `ean` LONGTEXT default NULL,
530
  `publicationyear` text,
530
  `publicationyear` MEDIUMTEXT,
531
  `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
531
  `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
532
  `volumedate` date default NULL,
532
  `volumedate` date default NULL,
533
  `volumedesc` text, -- volume information (MARC21 362$a)
533
  `volumedesc` MEDIUMTEXT, -- volume information (MARC21 362$a)
534
  `collectiontitle` mediumtext default NULL,
534
  `collectiontitle` LONGTEXT default NULL,
535
  `collectionissn` text default NULL,
535
  `collectionissn` MEDIUMTEXT default NULL,
536
  `collectionvolume` mediumtext default NULL,
536
  `collectionvolume` LONGTEXT default NULL,
537
  `editionstatement` text default NULL,
537
  `editionstatement` MEDIUMTEXT default NULL,
538
  `editionresponsibility` text default NULL,
538
  `editionresponsibility` MEDIUMTEXT default NULL,
539
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
539
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
540
  `illus` varchar(255) default NULL, -- illustrations (MARC21 300$b)
540
  `illus` varchar(255) default NULL, -- illustrations (MARC21 300$b)
541
  `pages` varchar(255) default NULL, -- number of pages (MARC21 300$c)
541
  `pages` varchar(255) default NULL, -- number of pages (MARC21 300$c)
542
  `notes` mediumtext,
542
  `notes` LONGTEXT,
543
  `size` varchar(255) default NULL, -- material size (MARC21 300$c)
543
  `size` varchar(255) default NULL, -- material size (MARC21 300$c)
544
  `place` varchar(255) default NULL, -- publication place (MARC21 260$a)
544
  `place` varchar(255) default NULL, -- publication place (MARC21 260$a)
545
  `lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
545
  `lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
546
  `url` text default NULL, -- url (MARC21 856$u)
546
  `url` MEDIUMTEXT default NULL, -- url (MARC21 856$u)
547
  `cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
547
  `cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
548
  `cn_class` varchar(30) default NULL,
548
  `cn_class` varchar(30) default NULL,
549
  `cn_item` varchar(10) default NULL,
549
  `cn_item` varchar(10) default NULL,
Lines 569-603 DROP TABLE IF EXISTS `deletedborrowers`; Link Here
569
CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrowers you have deleted
569
CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrowers you have deleted
570
  `borrowernumber` int(11) NOT NULL default 0, -- primary key, Koha assigned ID number for patrons/borrowers
570
  `borrowernumber` int(11) NOT NULL default 0, -- primary key, Koha assigned ID number for patrons/borrowers
571
  `cardnumber` varchar(32) default NULL, -- unique key, library assigned ID number for patrons/borrowers
571
  `cardnumber` varchar(32) default NULL, -- unique key, library assigned ID number for patrons/borrowers
572
  `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname)
572
  `surname` LONGTEXT NOT NULL, -- patron/borrower's last name (surname)
573
  `firstname` text, -- patron/borrower's first name
573
  `firstname` MEDIUMTEXT, -- patron/borrower's first name
574
  `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs.
574
  `title` LONGTEXT, -- patron/borrower's title, for example: Mr. or Mrs.
575
  `othernames` mediumtext, -- any other names associated with the patron/borrower
575
  `othernames` LONGTEXT, -- any other names associated with the patron/borrower
576
  `initials` text, -- initials for your patron/borrower
576
  `initials` MEDIUMTEXT, -- initials for your patron/borrower
577
  `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
577
  `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
578
  `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
578
  `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
579
  `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address
579
  `address` LONGTEXT NOT NULL, -- the first address line for your patron/borrower's primary address
580
  `address2` text, -- the second address line for your patron/borrower's primary address
580
  `address2` MEDIUMTEXT, -- the second address line for your patron/borrower's primary address
581
  `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address
581
  `city` LONGTEXT NOT NULL, -- the city or town for your patron/borrower's primary address
582
  `state` text default NULL, -- the state or province for your patron/borrower's primary address
582
  `state` MEDIUMTEXT default NULL, -- the state or province for your patron/borrower's primary address
583
  `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
583
  `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
584
  `country` text, -- the country for your patron/borrower's primary address
584
  `country` MEDIUMTEXT, -- the country for your patron/borrower's primary address
585
  `email` mediumtext, -- the primary email address for your patron/borrower's primary address
585
  `email` LONGTEXT, -- the primary email address for your patron/borrower's primary address
586
  `phone` text, -- the primary phone number for your patron/borrower's primary address
586
  `phone` MEDIUMTEXT, -- the primary phone number for your patron/borrower's primary address
587
  `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
587
  `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
588
  `fax` mediumtext, -- the fax number for your patron/borrower's primary address
588
  `fax` LONGTEXT, -- the fax number for your patron/borrower's primary address
589
  `emailpro` text, -- the secondary email addres for your patron/borrower's primary address
589
  `emailpro` MEDIUMTEXT, -- the secondary email addres for your patron/borrower's primary address
590
  `phonepro` text, -- the secondary phone number for your patron/borrower's primary address
590
  `phonepro` MEDIUMTEXT, -- the secondary phone number for your patron/borrower's primary address
591
  `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
591
  `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
592
  `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
592
  `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
593
  `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
593
  `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
594
  `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address
594
  `B_address2` MEDIUMTEXT default NULL, -- the second address line for your patron/borrower's alternate address
595
  `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address
595
  `B_city` LONGTEXT, -- the city or town for your patron/borrower's alternate address
596
  `B_state` text default NULL, -- the state for your patron/borrower's alternate address
596
  `B_state` MEDIUMTEXT default NULL, -- the state for your patron/borrower's alternate address
597
  `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
597
  `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
598
  `B_country` text, -- the country for your patron/borrower's alternate address
598
  `B_country` MEDIUMTEXT, -- the country for your patron/borrower's alternate address
599
  `B_email` text, -- the patron/borrower's alternate email address
599
  `B_email` MEDIUMTEXT, -- the patron/borrower's alternate email address
600
  `B_phone` mediumtext, -- the patron/borrower's alternate phone number
600
  `B_phone` LONGTEXT, -- the patron/borrower's alternate phone number
601
  `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD)
601
  `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD)
602
  `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch
602
  `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch
603
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category
603
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category
Lines 608-624 CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower Link Here
608
  `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
608
  `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
609
  `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)
609
  `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)
610
  `debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of patron
610
  `debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of patron
611
  `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarantor or organization name
611
  `contactname` LONGTEXT, -- used for children and profesionals to include surname or last name of guarantor or organization name
612
  `contactfirstname` text, -- used for children to include first name of guarantor
612
  `contactfirstname` MEDIUMTEXT, -- used for children to include first name of guarantor
613
  `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarantor
613
  `contacttitle` MEDIUMTEXT, -- used for children to include title (Mr., Mrs., etc) of guarantor
614
  `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarantors or organizations
614
  `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarantors or organizations
615
  `borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client
615
  `borrowernotes` LONGTEXT, -- a note on the patron/borrower's account that is only visible in the staff client
616
  `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarantor
616
  `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarantor
617
  `sex` varchar(1) default NULL, -- patron/borrower's gender
617
  `sex` varchar(1) default NULL, -- patron/borrower's gender
618
  `password` varchar(60) default NULL, -- patron/borrower's encrypted password
618
  `password` varchar(60) default NULL, -- patron/borrower's encrypted password
619
  `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
619
  `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
620
  `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in
620
  `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in
621
  `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
621
  `opacnote` LONGTEXT, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
622
  `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
622
  `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
623
  `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
623
  `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
624
  `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
624
  `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
Lines 627-635 CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower Link Here
627
  `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
627
  `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
628
  `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
628
  `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
629
  `altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower
629
  `altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower
630
  `altcontactstate` text default NULL, -- the state for the alternate contact for the patron/borrower
630
  `altcontactstate` MEDIUMTEXT default NULL, -- the state for the alternate contact for the patron/borrower
631
  `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
631
  `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
632
  `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower
632
  `altcontactcountry` MEDIUMTEXT default NULL, -- the country for the alternate contact for the patron/borrower
633
  `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
633
  `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
634
  `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SMS turned on)
634
  `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SMS turned on)
635
  `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
635
  `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
Lines 640-646 CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower Link Here
640
  `lastseen` datetime default NULL, -- last time a patron has been seen (connected at the OPAC or staff interface)
640
  `lastseen` datetime default NULL, -- last time a patron has been seen (connected at the OPAC or staff interface)
641
  `lang` varchar(25) NOT NULL default 'default', -- lang to use to send notices to this patron
641
  `lang` varchar(25) NOT NULL default 'default', -- lang to use to send notices to this patron
642
  `login_attempts` int(4) default 0, -- number of failed login attemps
642
  `login_attempts` int(4) default 0, -- number of failed login attemps
643
  `overdrive_auth_token` text default NULL, -- persist OverDrive auth token
643
  `overdrive_auth_token` MEDIUMTEXT default NULL, -- persist OverDrive auth token
644
  KEY borrowernumber (borrowernumber),
644
  KEY borrowernumber (borrowernumber),
645
  KEY `cardnumber` (`cardnumber`),
645
  KEY `cardnumber` (`cardnumber`),
646
  KEY `sms_provider_id` (`sms_provider_id`)
646
  KEY `sms_provider_id` (`sms_provider_id`)
Lines 657-663 CREATE TABLE `deleteditems` ( Link Here
657
  `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
657
  `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
658
  `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
658
  `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
659
  `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
659
  `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
660
  `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e)
660
  `booksellerid` LONGTEXT default NULL, -- where the item was purchased (MARC21 952$e)
661
  `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
661
  `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
662
  `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
662
  `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
663
  `replacementprice` decimal(8,2) default NULL, -- cost the library charges to replace the item if it has been marked lost (MARC21 952$v)
663
  `replacementprice` decimal(8,2) default NULL, -- cost the library charges to replace the item if it has been marked lost (MARC21 952$v)
Lines 677-686 CREATE TABLE `deleteditems` ( Link Here
677
  `renewals` smallint(6) default NULL, -- number of times this item has been renewed
677
  `renewals` smallint(6) default NULL, -- number of times this item has been renewed
678
  `reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
678
  `reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
679
  `restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5)
679
  `restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5)
680
  `itemnotes` mediumtext, -- public notes on this item (MARC21 952$x)
680
  `itemnotes` LONGTEXT, -- public notes on this item (MARC21 952$x)
681
  `itemnotes_nonpublic` mediumtext default NULL,
681
  `itemnotes_nonpublic` LONGTEXT default NULL,
682
  `holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)
682
  `holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)
683
  `paidfor` mediumtext,
683
  `paidfor` LONGTEXT,
684
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
684
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
685
  `location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c)
685
  `location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c)
686
  `permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
686
  `permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
Lines 688-698 CREATE TABLE `deleteditems` ( Link Here
688
  `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
688
  `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
689
  `cn_sort` varchar(255) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting
689
  `cn_sort` varchar(255) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting
690
  `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8)
690
  `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8)
691
  `materials` text default NULL, -- materials specified (MARC21 952$3)
691
  `materials` MEDIUMTEXT default NULL, -- materials specified (MARC21 952$3)
692
  `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u)
692
  `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u)
693
  `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y)
693
  `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y)
694
  `more_subfields_xml` longtext default NULL, -- additional 952 subfields in XML format
694
  `more_subfields_xml` LONGTEXT default NULL, -- additional 952 subfields in XML format
695
  `enumchron` text default NULL, -- serial enumeration/chronology for the item (MARC21 952$h)
695
  `enumchron` MEDIUMTEXT default NULL, -- serial enumeration/chronology for the item (MARC21 952$h)
696
  `copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t)
696
  `copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t)
697
  `stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i)
697
  `stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i)
698
  `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.
698
  `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.
Lines 715-722 DROP TABLE IF EXISTS `export_format`; Link Here
715
CREATE TABLE `export_format` (
715
CREATE TABLE `export_format` (
716
  `export_format_id` int(11) NOT NULL auto_increment,
716
  `export_format_id` int(11) NOT NULL auto_increment,
717
  `profile` varchar(255) NOT NULL,
717
  `profile` varchar(255) NOT NULL,
718
  `description` mediumtext NOT NULL,
718
  `description` LONGTEXT NOT NULL,
719
  `content` mediumtext NOT NULL,
719
  `content` LONGTEXT NOT NULL,
720
  `csv_separator` varchar(2) NOT NULL,
720
  `csv_separator` varchar(2) NOT NULL,
721
  `field_separator` varchar(2) NOT NULL,
721
  `field_separator` varchar(2) NOT NULL,
722
  `subfield_separator` varchar(2) NOT NULL,
722
  `subfield_separator` varchar(2) NOT NULL,
Lines 746-752 CREATE TABLE `import_batches` ( -- information about batches of marc records tha Link Here
746
  `batch_type` enum('batch', 'z3950', 'webservice') NOT NULL default 'batch', -- where this batch has come from
746
  `batch_type` enum('batch', 'z3950', 'webservice') NOT NULL default 'batch', -- where this batch has come from
747
  `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio', -- type of record in the batch
747
  `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio', -- type of record in the batch
748
  `file_name` varchar(100), -- the name of the file uploaded
748
  `file_name` varchar(100), -- the name of the file uploaded
749
  `comments` mediumtext, -- any comments added when the file was uploaded
749
  `comments` LONGTEXT, -- any comments added when the file was uploaded
750
  PRIMARY KEY (`import_batch_id`),
750
  PRIMARY KEY (`import_batch_id`),
751
  KEY `branchcode` (`branchcode`)
751
  KEY `branchcode` (`branchcode`)
752
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
752
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Lines 764-775 CREATE TABLE `import_records` ( Link Here
764
  `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
764
  `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
765
  `import_date` DATE default NULL,
765
  `import_date` DATE default NULL,
766
  `marc` longblob NOT NULL,
766
  `marc` longblob NOT NULL,
767
  `marcxml` longtext NOT NULL,
767
  `marcxml` LONGTEXT NOT NULL,
768
  `marcxml_old` longtext NOT NULL,
768
  `marcxml_old` LONGTEXT NOT NULL,
769
  `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
769
  `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
770
  `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
770
  `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
771
  `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
771
  `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
772
  `import_error` mediumtext,
772
  `import_error` LONGTEXT,
773
  `encoding` varchar(40) NOT NULL default '',
773
  `encoding` varchar(40) NOT NULL default '',
774
  `z3950random` varchar(40) default NULL,
774
  `z3950random` varchar(40) default NULL,
775
  PRIMARY KEY (`import_record_id`),
775
  PRIMARY KEY (`import_record_id`),
Lines 842-849 CREATE TABLE `import_items` ( Link Here
842
  `itemnumber` int(11) default NULL,
842
  `itemnumber` int(11) default NULL,
843
  `branchcode` varchar(10) default NULL,
843
  `branchcode` varchar(10) default NULL,
844
  `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
844
  `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
845
  `marcxml` longtext NOT NULL,
845
  `marcxml` LONGTEXT NOT NULL,
846
  `import_error` mediumtext,
846
  `import_error` LONGTEXT,
847
  PRIMARY KEY (`import_items_id`),
847
  PRIMARY KEY (`import_items_id`),
848
  CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
848
  CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
849
             REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
849
             REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
Lines 917-923 CREATE TABLE `items` ( -- holdings/item information Link Here
917
  `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
917
  `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
918
  `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
918
  `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
919
  `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
919
  `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
920
  `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e)
920
  `booksellerid` LONGTEXT default NULL, -- where the item was purchased (MARC21 952$e)
921
  `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
921
  `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
922
  `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
922
  `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
923
  `replacementprice` decimal(8,2) default NULL, -- cost the library charges to replace the item if it has been marked lost (MARC21 952$v)
923
  `replacementprice` decimal(8,2) default NULL, -- cost the library charges to replace the item if it has been marked lost (MARC21 952$v)
Lines 937-946 CREATE TABLE `items` ( -- holdings/item information Link Here
937
  `renewals` smallint(6) default NULL, -- number of times this item has been renewed
937
  `renewals` smallint(6) default NULL, -- number of times this item has been renewed
938
  `reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
938
  `reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
939
  `restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5)
939
  `restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5)
940
  `itemnotes` mediumtext, -- public notes on this item (MARC21 952$x)
940
  `itemnotes` LONGTEXT, -- public notes on this item (MARC21 952$x)
941
  `itemnotes_nonpublic` mediumtext default NULL,
941
  `itemnotes_nonpublic` LONGTEXT default NULL,
942
  `holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)
942
  `holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)
943
  `paidfor` mediumtext,
943
  `paidfor` LONGTEXT,
944
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
944
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
945
  `location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c)
945
  `location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c)
946
  `permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
946
  `permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
Lines 948-958 CREATE TABLE `items` ( -- holdings/item information Link Here
948
  `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
948
  `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
949
  `cn_sort` varchar(255) default NULL,  -- normalized form of the call number (MARC21 952$o) used for sorting
949
  `cn_sort` varchar(255) default NULL,  -- normalized form of the call number (MARC21 952$o) used for sorting
950
  `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8)
950
  `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8)
951
  `materials` text default NULL, -- materials specified (MARC21 952$3)
951
  `materials` MEDIUMTEXT default NULL, -- materials specified (MARC21 952$3)
952
  `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u)
952
  `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u)
953
  `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y)
953
  `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y)
954
  `more_subfields_xml` longtext default NULL, -- additional 952 subfields in XML format
954
  `more_subfields_xml` LONGTEXT default NULL, -- additional 952 subfields in XML format
955
  `enumchron` text default NULL, -- serial enumeration/chronology for the item (MARC21 952$h)
955
  `enumchron` MEDIUMTEXT default NULL, -- serial enumeration/chronology for the item (MARC21 952$h)
956
  `copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t)
956
  `copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t)
957
  `stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i)
957
  `stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i)
958
  `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.
958
  `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.
Lines 981-993 CREATE TABLE `items` ( -- holdings/item information Link Here
981
DROP TABLE IF EXISTS `itemtypes`;
981
DROP TABLE IF EXISTS `itemtypes`;
982
CREATE TABLE `itemtypes` ( -- defines the item types
982
CREATE TABLE `itemtypes` ( -- defines the item types
983
  itemtype varchar(10) NOT NULL default '', -- unique key, a code associated with the item type
983
  itemtype varchar(10) NOT NULL default '', -- unique key, a code associated with the item type
984
  description mediumtext, -- a plain text explanation of the item type
984
  description LONGTEXT, -- a plain text explanation of the item type
985
  rentalcharge decimal(28,6) default NULL, -- the amount charged when this item is checked out/issued
985
  rentalcharge decimal(28,6) default NULL, -- the amount charged when this item is checked out/issued
986
  defaultreplacecost decimal(28,6) default NULL, -- default replacement cost
986
  defaultreplacecost decimal(28,6) default NULL, -- default replacement cost
987
  processfee decimal(28,6) default NULL, -- default text be recorded in the column note when the processing fee is applied
987
  processfee decimal(28,6) default NULL, -- default text be recorded in the column note when the processing fee is applied
988
  notforloan smallint(6) default NULL, -- 1 if the item is not for loan, 0 if the item is available for loan
988
  notforloan smallint(6) default NULL, -- 1 if the item is not for loan, 0 if the item is available for loan
989
  imageurl varchar(200) default NULL, -- URL for the item type icon
989
  imageurl varchar(200) default NULL, -- URL for the item type icon
990
  summary text, -- information from the summary field, may include HTML
990
  summary MEDIUMTEXT, -- information from the summary field, may include HTML
991
  checkinmsg VARCHAR(255), -- message that is displayed when an item with the given item type is checked in
991
  checkinmsg VARCHAR(255), -- message that is displayed when an item with the given item type is checked in
992
  checkinmsgtype CHAR(16) DEFAULT 'message' NOT NULL, -- type (CSS class) for the checkinmsg, can be "alert" or "message"
992
  checkinmsgtype CHAR(16) DEFAULT 'message' NOT NULL, -- type (CSS class) for the checkinmsg, can be "alert" or "message"
993
  sip_media_type VARCHAR(3) DEFAULT NULL, -- SIP2 protocol media type for this itemtype
993
  sip_media_type VARCHAR(3) DEFAULT NULL, -- SIP2 protocol media type for this itemtype
Lines 1024-1030 CREATE TABLE `branchtransfers` ( -- information for items that are in transit be Link Here
1024
  `frombranch` varchar(10) NOT NULL default '', -- the branch the transfer is coming from
1024
  `frombranch` varchar(10) NOT NULL default '', -- the branch the transfer is coming from
1025
  `datearrived` datetime default NULL, -- the date the transfer arrived at its destination
1025
  `datearrived` datetime default NULL, -- the date the transfer arrived at its destination
1026
  `tobranch` varchar(10) NOT NULL default '', -- the branch the transfer was going to
1026
  `tobranch` varchar(10) NOT NULL default '', -- the branch the transfer was going to
1027
  `comments` mediumtext, -- any comments related to the transfer
1027
  `comments` LONGTEXT, -- any comments related to the transfer
1028
  PRIMARY KEY (`branchtransfer_id`),
1028
  PRIMARY KEY (`branchtransfer_id`),
1029
  KEY `frombranch` (`frombranch`),
1029
  KEY `frombranch` (`frombranch`),
1030
  KEY `tobranch` (`tobranch`),
1030
  KEY `tobranch` (`tobranch`),
Lines 1089-1095 CREATE TABLE `creator_layouts` ( Link Here
1089
  `callnum_split` int(1) DEFAULT '0',
1089
  `callnum_split` int(1) DEFAULT '0',
1090
  `text_justify` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'L',
1090
  `text_justify` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'L',
1091
  `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1091
  `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1092
  `layout_xml` text NOT NULL,
1092
  `layout_xml` MEDIUMTEXT NOT NULL,
1093
  `creator` char(15) NOT NULL DEFAULT 'Labels',
1093
  `creator` char(15) NOT NULL DEFAULT 'Labels',
1094
  PRIMARY KEY (`layout_id`)
1094
  PRIMARY KEY (`layout_id`)
1095
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1095
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Lines 1146-1152 CREATE TABLE `marc_subfield_structure` ( Link Here
1146
  `frameworkcode` varchar(4) NOT NULL default '',
1146
  `frameworkcode` varchar(4) NOT NULL default '',
1147
  `seealso` varchar(1100) default NULL,
1147
  `seealso` varchar(1100) default NULL,
1148
  `link` varchar(80) default NULL,
1148
  `link` varchar(80) default NULL,
1149
  `defaultvalue` text default NULL,
1149
  `defaultvalue` MEDIUMTEXT default NULL,
1150
  `maxlength` int(4) NOT NULL DEFAULT '9999',
1150
  `maxlength` int(4) NOT NULL DEFAULT '9999',
1151
  PRIMARY KEY  (`frameworkcode`,`tagfield`,`tagsubfield`),
1151
  PRIMARY KEY  (`frameworkcode`,`tagfield`,`tagsubfield`),
1152
  KEY `kohafield_2` (`kohafield`),
1152
  KEY `kohafield_2` (`kohafield`),
Lines 1273-1279 CREATE TABLE `need_merge_authorities` ( -- keeping track of authority records st Link Here
1273
  `id` int NOT NULL auto_increment PRIMARY KEY, -- unique id
1273
  `id` int NOT NULL auto_increment PRIMARY KEY, -- unique id
1274
  `authid` bigint NOT NULL, -- reference to original authority record
1274
  `authid` bigint NOT NULL, -- reference to original authority record
1275
  `authid_new` bigint, -- reference to optional new authority record
1275
  `authid_new` bigint, -- reference to optional new authority record
1276
  `reportxml` text, -- xml showing original reporting tag
1276
  `reportxml` MEDIUMTEXT, -- xml showing original reporting tag
1277
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date and time last modified
1277
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date and time last modified
1278
  `done` tinyint DEFAULT 0  -- indication whether merge has been executed (0=not done, 1=done, 2=in progress)
1278
  `done` tinyint DEFAULT 0  -- indication whether merge has been executed (0=not done, 1=done, 2=in progress)
1279
-- Note: authid and authid_new should NOT be FOREIGN keys !
1279
-- Note: authid and authid_new should NOT be FOREIGN keys !
Lines 1414-1420 CREATE TABLE `repeatable_holidays` ( -- information for the days the library is Link Here
1414
  `day` smallint(6) default NULL, -- day of the month this closing is on
1414
  `day` smallint(6) default NULL, -- day of the month this closing is on
1415
  `month` smallint(6) default NULL, -- month this closing is in
1415
  `month` smallint(6) default NULL, -- month this closing is in
1416
  `title` varchar(50) NOT NULL default '', -- title of this closing
1416
  `title` varchar(50) NOT NULL default '', -- title of this closing
1417
  `description` text NOT NULL, -- description for this closing
1417
  `description` MEDIUMTEXT NOT NULL, -- description for this closing
1418
  PRIMARY KEY  (`id`)
1418
  PRIMARY KEY  (`id`)
1419
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1419
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1420
1420
Lines 1426-1435 DROP TABLE IF EXISTS `reports_dictionary`; Link Here
1426
CREATE TABLE reports_dictionary ( -- definitions (or snippets of SQL) stored for use in reports
1426
CREATE TABLE reports_dictionary ( -- definitions (or snippets of SQL) stored for use in reports
1427
   `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
1427
   `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
1428
   `name` varchar(255) default NULL, -- name for this definition
1428
   `name` varchar(255) default NULL, -- name for this definition
1429
   `description` text, -- description for this definition
1429
   `description` MEDIUMTEXT, -- description for this definition
1430
   `date_created` datetime default NULL, -- date and time this definition was created
1430
   `date_created` datetime default NULL, -- date and time this definition was created
1431
   `date_modified` datetime default NULL, -- date and time this definition was last modified
1431
   `date_modified` datetime default NULL, -- date and time this definition was last modified
1432
   `saved_sql` text, -- SQL snippet for us in reports
1432
   `saved_sql` MEDIUMTEXT, -- SQL snippet for us in reports
1433
   report_area varchar(6) DEFAULT NULL, -- Koha module this definition is for Circulation, Catalog, Patrons, Acquistions, Accounts)
1433
   report_area varchar(6) DEFAULT NULL, -- Koha module this definition is for Circulation, Catalog, Patrons, Acquistions, Accounts)
1434
   PRIMARY KEY  (id),
1434
   PRIMARY KEY  (id),
1435
   KEY dictionary_area_idx (report_area)
1435
   KEY dictionary_area_idx (report_area)
Lines 1445-1455 CREATE TABLE saved_sql ( -- saved sql reports Link Here
1445
   `borrowernumber` int(11) default NULL, -- the staff member who created this report (borrowers.borrowernumber)
1445
   `borrowernumber` int(11) default NULL, -- the staff member who created this report (borrowers.borrowernumber)
1446
   `date_created` datetime default NULL, -- the date this report was created
1446
   `date_created` datetime default NULL, -- the date this report was created
1447
   `last_modified` datetime default NULL, -- the date this report was last edited
1447
   `last_modified` datetime default NULL, -- the date this report was last edited
1448
   `savedsql` text, -- the SQL for this report
1448
   `savedsql` MEDIUMTEXT, -- the SQL for this report
1449
   `last_run` datetime default NULL,
1449
   `last_run` datetime default NULL,
1450
   `report_name` varchar(255) NOT NULL default '', -- the name of this report
1450
   `report_name` varchar(255) NOT NULL default '', -- the name of this report
1451
   `type` varchar(255) default NULL, -- always 1 for tabular
1451
   `type` varchar(255) default NULL, -- always 1 for tabular
1452
   `notes` text, -- the notes or description given to this report
1452
   `notes` MEDIUMTEXT, -- the notes or description given to this report
1453
   `cache_expiry` int NOT NULL default 300,
1453
   `cache_expiry` int NOT NULL default 300,
1454
   `public` boolean NOT NULL default FALSE,
1454
   `public` boolean NOT NULL default FALSE,
1455
    report_area varchar(6) default NULL,
1455
    report_area varchar(6) default NULL,
Lines 1469-1475 DROP TABLE IF EXISTS `saved_reports`; Link Here
1469
CREATE TABLE saved_reports (
1469
CREATE TABLE saved_reports (
1470
   `id` int(11) NOT NULL auto_increment,
1470
   `id` int(11) NOT NULL auto_increment,
1471
   `report_id` int(11) default NULL,
1471
   `report_id` int(11) default NULL,
1472
   `report` longtext,
1472
   `report` LONGTEXT,
1473
   `date_run` datetime default NULL,
1473
   `date_run` datetime default NULL,
1474
   PRIMARY KEY (`id`)
1474
   PRIMARY KEY (`id`)
1475
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1475
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Lines 1498-1504 CREATE TABLE IF NOT EXISTS `search_history` ( -- patron's opac search history Link Here
1498
  `userid` int(11) NOT NULL, -- the patron who performed the search (borrowers.borrowernumber)
1498
  `userid` int(11) NOT NULL, -- the patron who performed the search (borrowers.borrowernumber)
1499
  `sessionid` varchar(32) NOT NULL, -- a system generated session id
1499
  `sessionid` varchar(32) NOT NULL, -- a system generated session id
1500
  `query_desc` varchar(255) NOT NULL, -- the search that was performed
1500
  `query_desc` varchar(255) NOT NULL, -- the search that was performed
1501
  `query_cgi` text NOT NULL, -- the string to append to the search url to rerun the search
1501
  `query_cgi` MEDIUMTEXT NOT NULL, -- the string to append to the search url to rerun the search
1502
  `type` varchar(16) NOT NULL DEFAULT 'biblio', -- search type, must be 'biblio' or 'authority'
1502
  `type` varchar(16) NOT NULL DEFAULT 'biblio', -- search type, must be 'biblio' or 'authority'
1503
  `total` int(11) NOT NULL, -- the total of results found
1503
  `total` int(11) NOT NULL, -- the total of results found
1504
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP, -- the date and time the search was run
1504
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP, -- the date and time the search was run
Lines 1553-1564 CREATE TABLE `serial` ( -- issues related to subscriptions Link Here
1553
  `serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information
1553
  `serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information
1554
  `status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions)
1554
  `status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions)
1555
  `planneddate` date default NULL, -- date expected
1555
  `planneddate` date default NULL, -- date expected
1556
  `notes` text, -- notes
1556
  `notes` MEDIUMTEXT, -- notes
1557
  `publisheddate` date default NULL, -- date published
1557
  `publisheddate` date default NULL, -- date published
1558
  publisheddatetext varchar(100) default NULL, -- date published (descriptive)
1558
  publisheddatetext varchar(100) default NULL, -- date published (descriptive)
1559
  `claimdate` date default NULL, -- date claimed
1559
  `claimdate` date default NULL, -- date claimed
1560
  claims_count int(11) default 0, -- number of claims made related to this issue
1560
  claims_count int(11) default 0, -- number of claims made related to this issue
1561
  `routingnotes` text, -- notes from the routing list
1561
  `routingnotes` MEDIUMTEXT, -- notes from the routing list
1562
  PRIMARY KEY (`serialid`)
1562
  PRIMARY KEY (`serialid`)
1563
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1563
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1564
1564
Lines 1569-1575 CREATE TABLE `serial` ( -- issues related to subscriptions Link Here
1569
DROP TABLE IF EXISTS sessions;
1569
DROP TABLE IF EXISTS sessions;
1570
CREATE TABLE sessions (
1570
CREATE TABLE sessions (
1571
  `id` varchar(32) NOT NULL,
1571
  `id` varchar(32) NOT NULL,
1572
  `a_session` mediumtext NOT NULL,
1572
  `a_session` LONGTEXT NOT NULL,
1573
  PRIMARY KEY (`id`)
1573
  PRIMARY KEY (`id`)
1574
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1574
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1575
1575
Lines 1594-1628 DROP TABLE IF EXISTS `borrowers`; Link Here
1594
CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members
1594
CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members
1595
  `borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers
1595
  `borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers
1596
  `cardnumber` varchar(32) default NULL, -- unique key, library assigned ID number for patrons/borrowers
1596
  `cardnumber` varchar(32) default NULL, -- unique key, library assigned ID number for patrons/borrowers
1597
  `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname)
1597
  `surname` LONGTEXT NOT NULL, -- patron/borrower's last name (surname)
1598
  `firstname` text, -- patron/borrower's first name
1598
  `firstname` MEDIUMTEXT, -- patron/borrower's first name
1599
  `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs.
1599
  `title` LONGTEXT, -- patron/borrower's title, for example: Mr. or Mrs.
1600
  `othernames` mediumtext, -- any other names associated with the patron/borrower
1600
  `othernames` LONGTEXT, -- any other names associated with the patron/borrower
1601
  `initials` text, -- initials for your patron/borrower
1601
  `initials` MEDIUMTEXT, -- initials for your patron/borrower
1602
  `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
1602
  `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
1603
  `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
1603
  `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
1604
  `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address
1604
  `address` LONGTEXT NOT NULL, -- the first address line for your patron/borrower's primary address
1605
  `address2` text, -- the second address line for your patron/borrower's primary address
1605
  `address2` MEDIUMTEXT, -- the second address line for your patron/borrower's primary address
1606
  `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address
1606
  `city` LONGTEXT NOT NULL, -- the city or town for your patron/borrower's primary address
1607
  `state` text default NULL, -- the state or province for your patron/borrower's primary address
1607
  `state` MEDIUMTEXT default NULL, -- the state or province for your patron/borrower's primary address
1608
  `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
1608
  `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
1609
  `country` text, -- the country for your patron/borrower's primary address
1609
  `country` MEDIUMTEXT, -- the country for your patron/borrower's primary address
1610
  `email` mediumtext, -- the primary email address for your patron/borrower's primary address
1610
  `email` LONGTEXT, -- the primary email address for your patron/borrower's primary address
1611
  `phone` text, -- the primary phone number for your patron/borrower's primary address
1611
  `phone` MEDIUMTEXT, -- the primary phone number for your patron/borrower's primary address
1612
  `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
1612
  `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
1613
  `fax` mediumtext, -- the fax number for your patron/borrower's primary address
1613
  `fax` LONGTEXT, -- the fax number for your patron/borrower's primary address
1614
  `emailpro` text, -- the secondary email addres for your patron/borrower's primary address
1614
  `emailpro` MEDIUMTEXT, -- the secondary email addres for your patron/borrower's primary address
1615
  `phonepro` text, -- the secondary phone number for your patron/borrower's primary address
1615
  `phonepro` MEDIUMTEXT, -- the secondary phone number for your patron/borrower's primary address
1616
  `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
1616
  `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
1617
  `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
1617
  `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
1618
  `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
1618
  `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
1619
  `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address
1619
  `B_address2` MEDIUMTEXT default NULL, -- the second address line for your patron/borrower's alternate address
1620
  `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address
1620
  `B_city` LONGTEXT, -- the city or town for your patron/borrower's alternate address
1621
  `B_state` text default NULL, -- the state for your patron/borrower's alternate address
1621
  `B_state` MEDIUMTEXT default NULL, -- the state for your patron/borrower's alternate address
1622
  `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
1622
  `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
1623
  `B_country` text, -- the country for your patron/borrower's alternate address
1623
  `B_country` MEDIUMTEXT, -- the country for your patron/borrower's alternate address
1624
  `B_email` text, -- the patron/borrower's alternate email address
1624
  `B_email` MEDIUMTEXT, -- the patron/borrower's alternate email address
1625
  `B_phone` mediumtext, -- the patron/borrower's alternate phone number
1625
  `B_phone` LONGTEXT, -- the patron/borrower's alternate phone number
1626
  `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD)
1626
  `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD)
1627
  `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch
1627
  `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch
1628
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category
1628
  `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category
Lines 1633-1649 CREATE TABLE `borrowers` ( -- this table includes information about your patrons Link Here
1633
  `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
1633
  `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
1634
  `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)
1634
  `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)
1635
  `debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron
1635
  `debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron
1636
  `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarantor or organization name
1636
  `contactname` LONGTEXT, -- used for children and profesionals to include surname or last name of guarantor or organization name
1637
  `contactfirstname` text, -- used for children to include first name of guarantor
1637
  `contactfirstname` MEDIUMTEXT, -- used for children to include first name of guarantor
1638
  `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarantor
1638
  `contacttitle` MEDIUMTEXT, -- used for children to include title (Mr., Mrs., etc) of guarantor
1639
  `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarantors or organizations
1639
  `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarantors or organizations
1640
  `borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client
1640
  `borrowernotes` LONGTEXT, -- a note on the patron/borrower's account that is only visible in the staff client
1641
  `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarantor
1641
  `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarantor
1642
  `sex` varchar(1) default NULL, -- patron/borrower's gender
1642
  `sex` varchar(1) default NULL, -- patron/borrower's gender
1643
  `password` varchar(60) default NULL, -- patron/borrower's Bcrypt encrypted password
1643
  `password` varchar(60) default NULL, -- patron/borrower's Bcrypt encrypted password
1644
  `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
1644
  `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
1645
  `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in
1645
  `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in
1646
  `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
1646
  `opacnote` LONGTEXT, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
1647
  `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
1647
  `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
1648
  `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
1648
  `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
1649
  `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
1649
  `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
Lines 1652-1660 CREATE TABLE `borrowers` ( -- this table includes information about your patrons Link Here
1652
  `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
1652
  `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
1653
  `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
1653
  `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
1654
  `altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower
1654
  `altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower
1655
  `altcontactstate` text default NULL, -- the state for the alternate contact for the patron/borrower
1655
  `altcontactstate` MEDIUMTEXT default NULL, -- the state for the alternate contact for the patron/borrower
1656
  `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
1656
  `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
1657
  `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower
1657
  `altcontactcountry` MEDIUMTEXT default NULL, -- the country for the alternate contact for the patron/borrower
1658
  `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
1658
  `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
1659
  `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SMS turned on)
1659
  `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SMS turned on)
1660
  `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
1660
  `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
Lines 1665-1671 CREATE TABLE `borrowers` ( -- this table includes information about your patrons Link Here
1665
  `lastseen` datetime default NULL, -- last time a patron has been seen (connected at the OPAC or staff interface)
1665
  `lastseen` datetime default NULL, -- last time a patron has been seen (connected at the OPAC or staff interface)
1666
  `lang` varchar(25) NOT NULL default 'default', -- lang to use to send notices to this patron
1666
  `lang` varchar(25) NOT NULL default 'default', -- lang to use to send notices to this patron
1667
  `login_attempts` int(4) default 0, -- number of failed login attemps
1667
  `login_attempts` int(4) default 0, -- number of failed login attemps
1668
  `overdrive_auth_token` text default NULL, -- persist OverDrive auth token
1668
  `overdrive_auth_token` MEDIUMTEXT default NULL, -- persist OverDrive auth token
1669
  UNIQUE KEY `cardnumber` (`cardnumber`),
1669
  UNIQUE KEY `cardnumber` (`cardnumber`),
1670
  PRIMARY KEY `borrowernumber` (`borrowernumber`),
1670
  PRIMARY KEY `borrowernumber` (`borrowernumber`),
1671
  KEY `categorycode` (`categorycode`),
1671
  KEY `categorycode` (`categorycode`),
Lines 1709-1715 CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record Link Here
1709
  borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted
1709
  borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted
1710
  expiration date DEFAULT NULL, -- expiration date of the restriction
1710
  expiration date DEFAULT NULL, -- expiration date of the restriction
1711
  `type` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') NOT NULL DEFAULT 'MANUAL', -- type of restriction
1711
  `type` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') NOT NULL DEFAULT 'MANUAL', -- type of restriction
1712
  `comment` text, -- comments about the restriction
1712
  `comment` MEDIUMTEXT, -- comments about the restriction
1713
  manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction
1713
  manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction
1714
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added
1714
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added
1715
  updated timestamp NULL DEFAULT NULL, -- date the restriction was updated
1715
  updated timestamp NULL DEFAULT NULL, -- date the restriction was updated
Lines 1756-1762 CREATE TABLE `issues` ( -- information related to check outs or issues Link Here
1756
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
1756
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
1757
  `issuedate` datetime default NULL, -- date the item was checked out or issued
1757
  `issuedate` datetime default NULL, -- date the item was checked out or issued
1758
  `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag
1758
  `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag
1759
  `note` mediumtext default NULL, -- issue note text
1759
  `note` LONGTEXT default NULL, -- issue note text
1760
  `notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)
1760
  `notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)
1761
  PRIMARY KEY (`issue_id`),
1761
  PRIMARY KEY (`issue_id`),
1762
  UNIQUE KEY `itemnumber` (`itemnumber`),
1762
  UNIQUE KEY `itemnumber` (`itemnumber`),
Lines 1787-1793 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r Link Here
1787
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
1787
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
1788
  `issuedate` datetime default NULL, -- date the item was checked out or issued
1788
  `issuedate` datetime default NULL, -- date the item was checked out or issued
1789
  `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag
1789
  `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag
1790
  `note` mediumtext default NULL, -- issue note text
1790
  `note` LONGTEXT default NULL, -- issue note text
1791
  `notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)
1791
  `notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)
1792
  PRIMARY KEY (`issue_id`),
1792
  PRIMARY KEY (`issue_id`),
1793
  KEY `old_issuesborridx` (`borrowernumber`),
1793
  KEY `old_issuesborridx` (`borrowernumber`),
Lines 1849-1855 CREATE TABLE `opac_news` ( -- data from the news tool Link Here
1849
  `idnew` int(10) unsigned NOT NULL auto_increment, -- unique identifier for the news article
1849
  `idnew` int(10) unsigned NOT NULL auto_increment, -- unique identifier for the news article
1850
  `branchcode` varchar(10) default NULL, -- branch code users to create branch specific news, NULL is every branch.
1850
  `branchcode` varchar(10) default NULL, -- branch code users to create branch specific news, NULL is every branch.
1851
  `title` varchar(250) NOT NULL default '', -- title of the news article
1851
  `title` varchar(250) NOT NULL default '', -- title of the news article
1852
  `content` text NOT NULL, -- the body of your news article
1852
  `content` MEDIUMTEXT NOT NULL, -- the body of your news article
1853
  `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)
1853
  `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)
1854
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- pulibcation date and time
1854
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- pulibcation date and time
1855
  `expirationdate` date default NULL, -- date the article is set to expire or no longer be visible
1855
  `expirationdate` date default NULL, -- date the article is set to expire or no longer be visible
Lines 1888-1894 CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha Link Here
1888
  `notificationdate` date default NULL, -- currently unused
1888
  `notificationdate` date default NULL, -- currently unused
1889
  `reminderdate` date default NULL, -- currently unused
1889
  `reminderdate` date default NULL, -- currently unused
1890
  `cancellationdate` date default NULL, -- the date this hold was cancelled
1890
  `cancellationdate` date default NULL, -- the date this hold was cancelled
1891
  `reservenotes` mediumtext, -- notes related to this hold
1891
  `reservenotes` LONGTEXT, -- notes related to this hold
1892
  `priority` smallint(6) default NULL, -- where in the queue the patron sits
1892
  `priority` smallint(6) default NULL, -- where in the queue the patron sits
1893
  `found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed
1893
  `found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed
1894
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated
1894
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated
Lines 1927-1933 CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b Link Here
1927
  `notificationdate` date default NULL, -- currently unused
1927
  `notificationdate` date default NULL, -- currently unused
1928
  `reminderdate` date default NULL, -- currently unused
1928
  `reminderdate` date default NULL, -- currently unused
1929
  `cancellationdate` date default NULL, -- the date this hold was cancelled
1929
  `cancellationdate` date default NULL, -- the date this hold was cancelled
1930
  `reservenotes` mediumtext, -- notes related to this hold
1930
  `reservenotes` LONGTEXT, -- notes related to this hold
1931
  `priority` smallint(6) default NULL, -- where in the queue the patron sits
1931
  `priority` smallint(6) default NULL, -- where in the queue the patron sits
1932
  `found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed
1932
  `found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed
1933
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated
1933
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated
Lines 1963-1969 CREATE TABLE `reviews` ( -- patron opac comments Link Here
1963
  `reviewid` int(11) NOT NULL auto_increment, -- unique identifier for this comment
1963
  `reviewid` int(11) NOT NULL auto_increment, -- unique identifier for this comment
1964
  `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron left this comment
1964
  `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron left this comment
1965
  `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bibliographic record this comment is for
1965
  `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bibliographic record this comment is for
1966
  `review` text, -- the body of the comment
1966
  `review` MEDIUMTEXT, -- the body of the comment
1967
  `approved` tinyint(4) default 0, -- whether this comment has been approved by a librarian (1 for yes, 0 for no)
1967
  `approved` tinyint(4) default 0, -- whether this comment has been approved by a librarian (1 for yes, 0 for no)
1968
  `datereviewed` datetime default NULL, -- the date the comment was left
1968
  `datereviewed` datetime default NULL, -- the date the comment was left
1969
  PRIMARY KEY  (`reviewid`),
1969
  PRIMARY KEY  (`reviewid`),
Lines 1984-1990 CREATE TABLE `special_holidays` ( -- non repeatable holidays/library closings Link Here
1984
  `year` smallint(6) NOT NULL default 0, -- year this closing is in
1984
  `year` smallint(6) NOT NULL default 0, -- year this closing is in
1985
  `isexception` smallint(1) NOT NULL default 1, -- is this a holiday exception to a repeatable holiday (1 for yes, 0 for no)
1985
  `isexception` smallint(1) NOT NULL default 1, -- is this a holiday exception to a repeatable holiday (1 for yes, 0 for no)
1986
  `title` varchar(50) NOT NULL default '', -- title for this closing
1986
  `title` varchar(50) NOT NULL default '', -- title for this closing
1987
  `description` text NOT NULL, -- description of this closing
1987
  `description` MEDIUMTEXT NOT NULL, -- description of this closing
1988
  PRIMARY KEY  (`id`)
1988
  PRIMARY KEY  (`id`)
1989
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1989
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1990
1990
Lines 1999-2005 CREATE TABLE `statistics` ( -- information related to transactions (circulation Link Here
1999
  `proccode` varchar(4) default NULL, -- type of procedure used when making payments (does not appear in the code)
1999
  `proccode` varchar(4) default NULL, -- type of procedure used when making payments (does not appear in the code)
2000
  `value` double(16,4) default NULL, -- monetary value associated with the transaction
2000
  `value` double(16,4) default NULL, -- monetary value associated with the transaction
2001
  `type` varchar(16) default NULL, -- transaction type (locause, issue, return, renew, writeoff, payment, Credit*)
2001
  `type` varchar(16) default NULL, -- transaction type (locause, issue, return, renew, writeoff, payment, Credit*)
2002
  `other` mediumtext, -- used by SIP
2002
  `other` LONGTEXT, -- used by SIP
2003
  `usercode` varchar(10) default NULL, -- unused in Koha
2003
  `usercode` varchar(10) default NULL, -- unused in Koha
2004
  `itemnumber` int(11) default NULL, -- foreign key from the items table, links transaction to a specific item
2004
  `itemnumber` int(11) default NULL, -- foreign key from the items table, links transaction to a specific item
2005
  `itemtype` varchar(10) default NULL, -- foreign key from the itemtypes table, links transaction to a specific item type
2005
  `itemtype` varchar(10) default NULL, -- foreign key from the itemtypes table, links transaction to a specific item type
Lines 2026-2032 CREATE TABLE `statistics` ( -- information related to transactions (circulation Link Here
2026
DROP TABLE IF EXISTS subscription_frequencies;
2026
DROP TABLE IF EXISTS subscription_frequencies;
2027
CREATE TABLE subscription_frequencies (
2027
CREATE TABLE subscription_frequencies (
2028
    id INTEGER NOT NULL AUTO_INCREMENT,
2028
    id INTEGER NOT NULL AUTO_INCREMENT,
2029
    description TEXT NOT NULL,
2029
    description MEDIUMTEXT NOT NULL,
2030
    displayorder INT DEFAULT NULL,
2030
    displayorder INT DEFAULT NULL,
2031
    unit ENUM('day','week','month','year') DEFAULT NULL,
2031
    unit ENUM('day','week','month','year') DEFAULT NULL,
2032
    unitsperissue INTEGER NOT NULL DEFAULT '1',
2032
    unitsperissue INTEGER NOT NULL DEFAULT '1',
Lines 2043-2049 CREATE TABLE subscription_numberpatterns ( Link Here
2043
    id INTEGER NOT NULL AUTO_INCREMENT,
2043
    id INTEGER NOT NULL AUTO_INCREMENT,
2044
    label VARCHAR(255) NOT NULL,
2044
    label VARCHAR(255) NOT NULL,
2045
    displayorder INTEGER DEFAULT NULL,
2045
    displayorder INTEGER DEFAULT NULL,
2046
    description TEXT NOT NULL,
2046
    description MEDIUMTEXT NOT NULL,
2047
    numberingmethod VARCHAR(255) NOT NULL,
2047
    numberingmethod VARCHAR(255) NOT NULL,
2048
    label1 VARCHAR(255) DEFAULT NULL,
2048
    label1 VARCHAR(255) DEFAULT NULL,
2049
    add1 INTEGER DEFAULT NULL,
2049
    add1 INTEGER DEFAULT NULL,
Lines 2084-2090 CREATE TABLE `subscription` ( -- information related to the subscription Link Here
2084
  `numberlength` int(11) default 0, -- subscription length in weeks (will not be filled in if monthlength or weeklength is set)
2084
  `numberlength` int(11) default 0, -- subscription length in weeks (will not be filled in if monthlength or weeklength is set)
2085
  `periodicity` integer default null, -- frequency type links to subscription_frequencies.id
2085
  `periodicity` integer default null, -- frequency type links to subscription_frequencies.id
2086
  countissuesperunit INTEGER NOT NULL DEFAULT 1,
2086
  countissuesperunit INTEGER NOT NULL DEFAULT 1,
2087
  `notes` mediumtext, -- notes
2087
  `notes` LONGTEXT, -- notes
2088
  `status` varchar(100) NOT NULL default '',  -- status of this subscription
2088
  `status` varchar(100) NOT NULL default '',  -- status of this subscription
2089
  `lastvalue1` int(11) default NULL,
2089
  `lastvalue1` int(11) default NULL,
2090
  `innerloop1` int(11) default 0,
2090
  `innerloop1` int(11) default 0,
Lines 2094-2107 CREATE TABLE `subscription` ( -- information related to the subscription Link Here
2094
  `innerloop3` int(11) default 0,
2094
  `innerloop3` int(11) default 0,
2095
  `firstacquidate` date default NULL, -- first issue received date
2095
  `firstacquidate` date default NULL, -- first issue received date
2096
  `manualhistory` tinyint(1) NOT NULL default 0, -- yes or no to managing the history manually
2096
  `manualhistory` tinyint(1) NOT NULL default 0, -- yes or no to managing the history manually
2097
  `irregularity` text, -- any irregularities in the subscription
2097
  `irregularity` MEDIUMTEXT, -- any irregularities in the subscription
2098
  skip_serialseq BOOLEAN NOT NULL DEFAULT 0,
2098
  skip_serialseq BOOLEAN NOT NULL DEFAULT 0,
2099
  `letter` varchar(20) default NULL,
2099
  `letter` varchar(20) default NULL,
2100
  `numberpattern` integer default null, -- the numbering pattern used links to subscription_numberpatterns.id
2100
  `numberpattern` integer default null, -- the numbering pattern used links to subscription_numberpatterns.id
2101
  locale VARCHAR(80) DEFAULT NULL, -- for foreign language subscriptions to display months, seasons, etc correctly
2101
  locale VARCHAR(80) DEFAULT NULL, -- for foreign language subscriptions to display months, seasons, etc correctly
2102
  `distributedto` text,
2102
  `distributedto` MEDIUMTEXT,
2103
  `internalnotes` longtext,
2103
  `internalnotes` LONGTEXT,
2104
  `callnumber` text, -- default call number
2104
  `callnumber` MEDIUMTEXT, -- default call number
2105
  `location` varchar(80) NULL default '', -- default shelving location (items.location)
2105
  `location` varchar(80) NULL default '', -- default shelving location (items.location)
2106
  `branchcode` varchar(10) NOT NULL default '', -- default branches (items.homebranch)
2106
  `branchcode` varchar(10) NOT NULL default '', -- default branches (items.homebranch)
2107
  `lastbranch` varchar(10),
2107
  `lastbranch` varchar(10),
Lines 2129-2136 CREATE TABLE `subscriptionhistory` ( Link Here
2129
  `subscriptionid` int(11) NOT NULL default 0,
2129
  `subscriptionid` int(11) NOT NULL default 0,
2130
  `histstartdate` date default NULL,
2130
  `histstartdate` date default NULL,
2131
  `histenddate` date default NULL,
2131
  `histenddate` date default NULL,
2132
  `missinglist` longtext NOT NULL,
2132
  `missinglist` LONGTEXT NOT NULL,
2133
  `recievedlist` longtext NOT NULL,
2133
  `recievedlist` LONGTEXT NOT NULL,
2134
  `opacnote` varchar(150) NOT NULL default '',
2134
  `opacnote` varchar(150) NOT NULL default '',
2135
  `librariannote` varchar(150) NOT NULL default '',
2135
  `librariannote` varchar(150) NOT NULL default '',
2136
  PRIMARY KEY  (`subscriptionid`),
2136
  PRIMARY KEY  (`subscriptionid`),
Lines 2162-2170 CREATE TABLE `subscriptionroutinglist` ( -- information related to the routing l Link Here
2162
DROP TABLE IF EXISTS `systempreferences`;
2162
DROP TABLE IF EXISTS `systempreferences`;
2163
CREATE TABLE `systempreferences` ( -- global system preferences
2163
CREATE TABLE `systempreferences` ( -- global system preferences
2164
  `variable` varchar(50) NOT NULL default '', -- system preference name
2164
  `variable` varchar(50) NOT NULL default '', -- system preference name
2165
  `value` text, -- system preference values
2165
  `value` MEDIUMTEXT, -- system preference values
2166
  `options` mediumtext, -- options for multiple choice system preferences
2166
  `options` LONGTEXT, -- options for multiple choice system preferences
2167
  `explanation` text, -- descriptive text for the system preference
2167
  `explanation` MEDIUMTEXT, -- descriptive text for the system preference
2168
  `type` varchar(20) default NULL, -- type of question this preference asks (multiple choice, plain text, yes or no, etc)
2168
  `type` varchar(20) default NULL, -- type of question this preference asks (multiple choice, plain text, yes or no, etc)
2169
  PRIMARY KEY  (`variable`)
2169
  PRIMARY KEY  (`variable`)
2170
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2170
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Lines 2312-2328 CREATE TABLE `z3950servers` ( -- connection information for the Z39.50 targets u Link Here
2312
  `db` varchar(255) default NULL, -- target's database name
2312
  `db` varchar(255) default NULL, -- target's database name
2313
  `userid` varchar(255) default NULL, -- username needed to log in to target
2313
  `userid` varchar(255) default NULL, -- username needed to log in to target
2314
  `password` varchar(255) default NULL, -- password needed to log in to target
2314
  `password` varchar(255) default NULL, -- password needed to log in to target
2315
  `servername` mediumtext NOT NULL, -- name given to the target by the library
2315
  `servername` LONGTEXT NOT NULL, -- name given to the target by the library
2316
  `checked` smallint(6) default NULL, -- whether this target is checked by default  (1 for yes, 0 for no)
2316
  `checked` smallint(6) default NULL, -- whether this target is checked by default  (1 for yes, 0 for no)
2317
  `rank` int(11) default NULL, -- where this target appears in the list of targets
2317
  `rank` int(11) default NULL, -- where this target appears in the list of targets
2318
  `syntax` varchar(80) default NULL, -- marc format provided by this target
2318
  `syntax` varchar(80) default NULL, -- marc format provided by this target
2319
  `timeout` int(11) NOT NULL DEFAULT '0', -- number of seconds before Koha stops trying to access this server
2319
  `timeout` int(11) NOT NULL DEFAULT '0', -- number of seconds before Koha stops trying to access this server
2320
  `servertype` enum('zed','sru') NOT NULL default 'zed', -- zed means z39.50 server
2320
  `servertype` enum('zed','sru') NOT NULL default 'zed', -- zed means z39.50 server
2321
  `encoding` text default NULL, -- characters encoding provided by this target
2321
  `encoding` MEDIUMTEXT default NULL, -- characters encoding provided by this target
2322
  `recordtype` enum('authority','biblio') NOT NULL default 'biblio', -- server contains bibliographic or authority records
2322
  `recordtype` enum('authority','biblio') NOT NULL default 'biblio', -- server contains bibliographic or authority records
2323
  `sru_options` varchar(255) default NULL, -- options like sru=get, sru_version=1.1; will be passed to the server via ZOOM
2323
  `sru_options` varchar(255) default NULL, -- options like sru=get, sru_version=1.1; will be passed to the server via ZOOM
2324
  `sru_fields` mediumtext default NULL, -- contains the mapping between the Z3950 search fields and the specific SRU server indexes
2324
  `sru_fields` LONGTEXT default NULL, -- contains the mapping between the Z3950 search fields and the specific SRU server indexes
2325
  `add_xslt` mediumtext default NULL, -- zero or more paths to XSLT files to be processed on the search results
2325
  `add_xslt` LONGTEXT default NULL, -- zero or more paths to XSLT files to be processed on the search results
2326
  PRIMARY KEY  (`id`)
2326
  PRIMARY KEY  (`id`)
2327
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2327
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2328
2328
Lines 2479-2495 CREATE TABLE `tmp_holdsqueue` ( Link Here
2479
  `biblionumber` int(11) default NULL,
2479
  `biblionumber` int(11) default NULL,
2480
  `itemnumber` int(11) default NULL,
2480
  `itemnumber` int(11) default NULL,
2481
  `barcode` varchar(20) default NULL,
2481
  `barcode` varchar(20) default NULL,
2482
  `surname` mediumtext NOT NULL,
2482
  `surname` LONGTEXT NOT NULL,
2483
  `firstname` text,
2483
  `firstname` MEDIUMTEXT,
2484
  `phone` text,
2484
  `phone` MEDIUMTEXT,
2485
  `borrowernumber` int(11) NOT NULL,
2485
  `borrowernumber` int(11) NOT NULL,
2486
  `cardnumber` varchar(32) default NULL,
2486
  `cardnumber` varchar(32) default NULL,
2487
  `reservedate` date default NULL,
2487
  `reservedate` date default NULL,
2488
  `title` mediumtext,
2488
  `title` LONGTEXT,
2489
  `itemcallnumber` varchar(255) default NULL,
2489
  `itemcallnumber` varchar(255) default NULL,
2490
  `holdingbranch` varchar(10) default NULL,
2490
  `holdingbranch` varchar(10) default NULL,
2491
  `pickbranch` varchar(10) default NULL,
2491
  `pickbranch` varchar(10) default NULL,
2492
  `notes` text,
2492
  `notes` MEDIUMTEXT,
2493
  `item_level_request` tinyint(4) NOT NULL default 0
2493
  `item_level_request` tinyint(4) NOT NULL default 0
2494
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2494
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2495
2495
Lines 2511-2526 DROP TABLE IF EXISTS `message_queue`; Link Here
2511
CREATE TABLE `message_queue` (
2511
CREATE TABLE `message_queue` (
2512
  `message_id` int(11) NOT NULL auto_increment,
2512
  `message_id` int(11) NOT NULL auto_increment,
2513
  `borrowernumber` int(11) default NULL,
2513
  `borrowernumber` int(11) default NULL,
2514
  `subject` text,
2514
  `subject` MEDIUMTEXT,
2515
  `content` text,
2515
  `content` MEDIUMTEXT,
2516
  `metadata` text DEFAULT NULL,
2516
  `metadata` MEDIUMTEXT DEFAULT NULL,
2517
  `letter_code` varchar(64) DEFAULT NULL,
2517
  `letter_code` varchar(64) DEFAULT NULL,
2518
  `message_transport_type` varchar(20) NOT NULL,
2518
  `message_transport_type` varchar(20) NOT NULL,
2519
  `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2519
  `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2520
  `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2520
  `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2521
  `to_address` mediumtext,
2521
  `to_address` LONGTEXT,
2522
  `from_address` mediumtext,
2522
  `from_address` LONGTEXT,
2523
  `content_type` text,
2523
  `content_type` MEDIUMTEXT,
2524
  PRIMARY KEY `message_id` (`message_id`),
2524
  PRIMARY KEY `message_id` (`message_id`),
2525
  KEY `borrowernumber` (`borrowernumber`),
2525
  KEY `borrowernumber` (`borrowernumber`),
2526
  KEY `message_transport_type` (`message_transport_type`),
2526
  KEY `message_transport_type` (`message_transport_type`),
Lines 2540-2546 CREATE TABLE `letter` ( -- table for all notice templates in Koha Link Here
2540
  `name` varchar(100) NOT NULL default '', -- plain text name for this notice or slip
2540
  `name` varchar(100) NOT NULL default '', -- plain text name for this notice or slip
2541
  `is_html` tinyint(1) default 0, -- does this notice or slip use HTML (1 for yes, 0 for no)
2541
  `is_html` tinyint(1) default 0, -- does this notice or slip use HTML (1 for yes, 0 for no)
2542
  `title` varchar(200) NOT NULL default '', -- subject line of the notice
2542
  `title` varchar(200) NOT NULL default '', -- subject line of the notice
2543
  `content` text, -- body text for the notice or slip
2543
  `content` MEDIUMTEXT, -- body text for the notice or slip
2544
  `message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice
2544
  `message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice
2545
  `lang` varchar(25) NOT NULL DEFAULT 'default', -- lang of the notice
2545
  `lang` varchar(25) NOT NULL DEFAULT 'default', -- lang of the notice
2546
  PRIMARY KEY  (`module`,`code`, `branchcode`, `message_transport_type`, `lang`),
2546
  PRIMARY KEY  (`module`,`code`, `branchcode`, `message_transport_type`, `lang`),
Lines 2686-2692 CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou Link Here
2686
  `borrowernumber` int(11) NOT NULL, -- foreign key linking this message to the borrowers table
2686
  `borrowernumber` int(11) NOT NULL, -- foreign key linking this message to the borrowers table
2687
  `branchcode` varchar(10) default NULL, -- foreign key linking the message to the branches table
2687
  `branchcode` varchar(10) default NULL, -- foreign key linking the message to the branches table
2688
  `message_type` varchar(1) NOT NULL, -- whether the message is for the librarians (L) or the patron (B)
2688
  `message_type` varchar(1) NOT NULL, -- whether the message is for the librarians (L) or the patron (B)
2689
  `message` text NOT NULL, -- the text of the message
2689
  `message` MEDIUMTEXT NOT NULL, -- the text of the message
2690
  `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- the date and time the message was written
2690
  `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- the date and time the message was written
2691
  `manager_id` int(11) default NULL, -- creator of message
2691
  `manager_id` int(11) default NULL, -- creator of message
2692
  PRIMARY KEY (`message_id`),
2692
  PRIMARY KEY (`message_id`),
Lines 2706-2718 CREATE TABLE `accountlines` ( Link Here
2706
  `itemnumber` int(11) default NULL,
2706
  `itemnumber` int(11) default NULL,
2707
  `date` date default NULL,
2707
  `date` date default NULL,
2708
  `amount` decimal(28,6) default NULL,
2708
  `amount` decimal(28,6) default NULL,
2709
  `description` mediumtext,
2709
  `description` LONGTEXT,
2710
  `dispute` mediumtext,
2710
  `dispute` LONGTEXT,
2711
  `accounttype` varchar(5) default NULL,
2711
  `accounttype` varchar(5) default NULL,
2712
  `amountoutstanding` decimal(28,6) default NULL,
2712
  `amountoutstanding` decimal(28,6) default NULL,
2713
  `lastincrement` decimal(28,6) default NULL,
2713
  `lastincrement` decimal(28,6) default NULL,
2714
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2714
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2715
  `note` text NULL default NULL,
2715
  `note` MEDIUMTEXT NULL default NULL,
2716
  `manager_id` int(11) NULL,
2716
  `manager_id` int(11) NULL,
2717
  PRIMARY KEY (`accountlines_id`),
2717
  PRIMARY KEY (`accountlines_id`),
2718
  KEY `acctsborridx` (`borrowernumber`),
2718
  KEY `acctsborridx` (`borrowernumber`),
Lines 2759-2768 CREATE TABLE `action_logs` ( -- logs of actions taken in Koha (requires that the Link Here
2759
  `action_id` int(11) NOT NULL auto_increment, -- unique identifier for each action
2759
  `action_id` int(11) NOT NULL auto_increment, -- unique identifier for each action
2760
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time the action took place
2760
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time the action took place
2761
  `user` int(11) NOT NULL default 0, -- the staff member who performed the action (borrowers.borrowernumber)
2761
  `user` int(11) NOT NULL default 0, -- the staff member who performed the action (borrowers.borrowernumber)
2762
  `module` text, -- the module this action was taken against
2762
  `module` MEDIUMTEXT, -- the module this action was taken against
2763
  `action` text, -- the action (includes things like DELETED, ADDED, MODIFY, etc)
2763
  `action` MEDIUMTEXT, -- the action (includes things like DELETED, ADDED, MODIFY, etc)
2764
  `object` int(11) default NULL, -- the object that the action was taken against (could be a borrowernumber, itemnumber, etc)
2764
  `object` int(11) default NULL, -- the object that the action was taken against (could be a borrowernumber, itemnumber, etc)
2765
  `info` text, -- information about the action (usually includes SQL statement)
2765
  `info` MEDIUMTEXT, -- information about the action (usually includes SQL statement)
2766
  `interface` VARCHAR(30) DEFAULT NULL, -- the context this action was taken in
2766
  `interface` VARCHAR(30) DEFAULT NULL, -- the context this action was taken in
2767
  PRIMARY KEY (`action_id`),
2767
  PRIMARY KEY (`action_id`),
2768
  KEY `timestamp_idx` (`timestamp`),
2768
  KEY `timestamp_idx` (`timestamp`),
Lines 2796-2815 CREATE TABLE `alert` ( Link Here
2796
DROP TABLE IF EXISTS `aqbooksellers`;
2796
DROP TABLE IF EXISTS `aqbooksellers`;
2797
CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquisitions
2797
CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquisitions
2798
  `id` int(11) NOT NULL auto_increment, -- primary key and unique identifier assigned by Koha
2798
  `id` int(11) NOT NULL auto_increment, -- primary key and unique identifier assigned by Koha
2799
  `name` mediumtext NOT NULL, -- vendor name
2799
  `name` LONGTEXT NOT NULL, -- vendor name
2800
  `address1` mediumtext, -- first line of vendor physical address
2800
  `address1` LONGTEXT, -- first line of vendor physical address
2801
  `address2` mediumtext, -- second line of vendor physical address
2801
  `address2` LONGTEXT, -- second line of vendor physical address
2802
  `address3` mediumtext, -- third line of vendor physical address
2802
  `address3` LONGTEXT, -- third line of vendor physical address
2803
  `address4` mediumtext, -- fourth line of vendor physical address
2803
  `address4` LONGTEXT, -- fourth line of vendor physical address
2804
  `phone` varchar(30) default NULL, -- vendor phone number
2804
  `phone` varchar(30) default NULL, -- vendor phone number
2805
  `accountnumber` mediumtext, -- unused in Koha
2805
  `accountnumber` LONGTEXT, -- unused in Koha
2806
  `othersupplier` mediumtext,  -- unused in Koha
2806
  `othersupplier` LONGTEXT,  -- unused in Koha
2807
  `currency` varchar(10) NOT NULL default '', -- unused in Koha
2807
  `currency` varchar(10) NOT NULL default '', -- unused in Koha
2808
  `booksellerfax` mediumtext, -- vendor fax number
2808
  `booksellerfax` LONGTEXT, -- vendor fax number
2809
  `notes` mediumtext, -- order notes
2809
  `notes` LONGTEXT, -- order notes
2810
  `bookselleremail` mediumtext, -- vendor email
2810
  `bookselleremail` LONGTEXT, -- vendor email
2811
  `booksellerurl` mediumtext, -- unused in Koha
2811
  `booksellerurl` LONGTEXT, -- unused in Koha
2812
  `postal` mediumtext, -- vendor postal address (all lines)
2812
  `postal` LONGTEXT, -- vendor postal address (all lines)
2813
  `url` varchar(255) default NULL, -- vendor web address
2813
  `url` varchar(255) default NULL, -- vendor web address
2814
  `active` tinyint(4) default NULL, -- is this vendor active (1 for yes, 0 for no)
2814
  `active` tinyint(4) default NULL, -- is this vendor active (1 for yes, 0 for no)
2815
  `listprice` varchar(10) default NULL, -- currency code for list prices
2815
  `listprice` varchar(10) default NULL, -- currency code for list prices
Lines 2840-2846 CREATE TABLE `aqbasketgroups` ( Link Here
2840
  `closed` tinyint(1) default NULL,
2840
  `closed` tinyint(1) default NULL,
2841
  `booksellerid` int(11) NOT NULL,
2841
  `booksellerid` int(11) NOT NULL,
2842
  `deliveryplace` varchar(10) default NULL,
2842
  `deliveryplace` varchar(10) default NULL,
2843
  `freedeliveryplace` text default NULL,
2843
  `freedeliveryplace` MEDIUMTEXT default NULL,
2844
  `deliverycomment` varchar(255) default NULL,
2844
  `deliverycomment` varchar(255) default NULL,
2845
  `billingplace` varchar(10) default NULL,
2845
  `billingplace` varchar(10) default NULL,
2846
  PRIMARY KEY  (`id`),
2846
  PRIMARY KEY  (`id`),
Lines 2862-2868 CREATE TABLE `aqbudgets` ( -- information related to Funds Link Here
2862
  `budget_amount` decimal(28,6) NULL default '0.00', -- total amount for this fund
2862
  `budget_amount` decimal(28,6) NULL default '0.00', -- total amount for this fund
2863
  `budget_encumb` decimal(28,6) NULL default '0.00', -- not used in the code
2863
  `budget_encumb` decimal(28,6) NULL default '0.00', -- not used in the code
2864
  `budget_expend` decimal(28,6) NULL default '0.00', -- not used in the code
2864
  `budget_expend` decimal(28,6) NULL default '0.00', -- not used in the code
2865
  `budget_notes` mediumtext, -- notes related to this fund
2865
  `budget_notes` LONGTEXT, -- notes related to this fund
2866
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this fund was last touched (created or modified)
2866
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this fund was last touched (created or modified)
2867
  `budget_period_id` int(11) default NULL, -- id of the budget that this fund belongs to (aqbudgetperiods.budget_period_id)
2867
  `budget_period_id` int(11) default NULL, -- id of the budget that this fund belongs to (aqbudgetperiods.budget_period_id)
2868
  `sort1_authcat` varchar(80) default NULL, -- statistical category for this fund
2868
  `sort1_authcat` varchar(80) default NULL, -- statistical category for this fund
Lines 2905-2911 CREATE TABLE `aqbudgetperiods` ( -- information related to Budgets Link Here
2905
  `budget_period_startdate` date NOT NULL, -- date when the budget starts
2905
  `budget_period_startdate` date NOT NULL, -- date when the budget starts
2906
  `budget_period_enddate` date NOT NULL, -- date when the budget ends
2906
  `budget_period_enddate` date NOT NULL, -- date when the budget ends
2907
  `budget_period_active` tinyint(1) default '0', -- whether this budget is active or not (1 for yes, 0 for no)
2907
  `budget_period_active` tinyint(1) default '0', -- whether this budget is active or not (1 for yes, 0 for no)
2908
  `budget_period_description` mediumtext, -- description assigned to this budget
2908
  `budget_period_description` LONGTEXT, -- description assigned to this budget
2909
  `budget_period_total` decimal(28,6), -- total amount available in this budget
2909
  `budget_period_total` decimal(28,6), -- total amount available in this budget
2910
  `budget_period_locked` tinyint(1) default NULL, -- whether this budget is locked or not (1 for yes, 0 for no)
2910
  `budget_period_locked` tinyint(1) default NULL, -- whether this budget is locked or not (1 for yes, 0 for no)
2911
  `sort1_authcat` varchar(10) default NULL, -- statistical category for this budget
2911
  `sort1_authcat` varchar(10) default NULL, -- statistical category for this budget
Lines 2944-2950 CREATE TABLE aqcontacts ( Link Here
2944
  altphone varchar(100) default NULL, -- contact's alternate phone number
2944
  altphone varchar(100) default NULL, -- contact's alternate phone number
2945
  fax varchar(100) default NULL,  -- contact's fax number
2945
  fax varchar(100) default NULL,  -- contact's fax number
2946
  email varchar(100) default NULL, -- contact's email address
2946
  email varchar(100) default NULL, -- contact's email address
2947
  notes mediumtext, -- notes related to the contact
2947
  notes LONGTEXT, -- notes related to the contact
2948
  orderacquisition BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive acquisition orders
2948
  orderacquisition BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive acquisition orders
2949
  claimacquisition BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive acquisitions claims
2949
  claimacquisition BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive acquisitions claims
2950
  claimissues BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive serial claims
2950
  claimissues BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive serial claims
Lines 2966-2972 CREATE TABLE `aqcontract` ( Link Here
2966
  `contractstartdate` date default NULL,
2966
  `contractstartdate` date default NULL,
2967
  `contractenddate` date default NULL,
2967
  `contractenddate` date default NULL,
2968
  `contractname` varchar(50) default NULL,
2968
  `contractname` varchar(50) default NULL,
2969
  `contractdescription` mediumtext,
2969
  `contractdescription` LONGTEXT,
2970
  `booksellerid` int(11) not NULL,
2970
  `booksellerid` int(11) not NULL,
2971
  PRIMARY KEY  (`contractnumber`),
2971
  PRIMARY KEY  (`contractnumber`),
2972
  CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2972
  CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
Lines 2981-2994 DROP TABLE IF EXISTS `aqbasket`; Link Here
2981
CREATE TABLE `aqbasket` ( -- stores data about baskets in acquisitions
2981
CREATE TABLE `aqbasket` ( -- stores data about baskets in acquisitions
2982
  `basketno` int(11) NOT NULL auto_increment, -- primary key, Koha defined number
2982
  `basketno` int(11) NOT NULL auto_increment, -- primary key, Koha defined number
2983
  `basketname` varchar(50) default NULL, -- name given to the basket at creation
2983
  `basketname` varchar(50) default NULL, -- name given to the basket at creation
2984
  `note` mediumtext, -- the internal note added at basket creation
2984
  `note` LONGTEXT, -- the internal note added at basket creation
2985
  `booksellernote` mediumtext, -- the vendor note added at basket creation
2985
  `booksellernote` LONGTEXT, -- the vendor note added at basket creation
2986
  `contractnumber` int(11), -- links this basket to the aqcontract table (aqcontract.contractnumber)
2986
  `contractnumber` int(11), -- links this basket to the aqcontract table (aqcontract.contractnumber)
2987
  `creationdate` date default NULL, -- the date the basket was created
2987
  `creationdate` date default NULL, -- the date the basket was created
2988
  `closedate` date default NULL, -- the date the basket was closed
2988
  `closedate` date default NULL, -- the date the basket was closed
2989
  `booksellerid` int(11) NOT NULL default 1, -- the Koha assigned ID for the vendor (aqbooksellers.id)
2989
  `booksellerid` int(11) NOT NULL default 1, -- the Koha assigned ID for the vendor (aqbooksellers.id)
2990
  `authorisedby` varchar(10) default NULL, -- the borrowernumber of the person who created the basket
2990
  `authorisedby` varchar(10) default NULL, -- the borrowernumber of the person who created the basket
2991
  `booksellerinvoicenumber` mediumtext, -- appears to always be NULL
2991
  `booksellerinvoicenumber` LONGTEXT, -- appears to always be NULL
2992
  `basketgroupid` int(11), -- links this basket to its group (aqbasketgroups.id)
2992
  `basketgroupid` int(11), -- links this basket to its group (aqbasketgroups.id)
2993
  `deliveryplace` varchar(10) default NULL, -- basket delivery place
2993
  `deliveryplace` varchar(10) default NULL, -- basket delivery place
2994
  `billingplace` varchar(10) default NULL, -- basket billing place
2994
  `billingplace` varchar(10) default NULL, -- basket billing place
Lines 3035-3041 CREATE TABLE `suggestions` ( -- purchase suggestions Link Here
3035
   rejectedby INT(11) default NULL, -- borrowernumber for the librarian who rejected the suggestion, foreign key linking to the borrowers table
3035
   rejectedby INT(11) default NULL, -- borrowernumber for the librarian who rejected the suggestion, foreign key linking to the borrowers table
3036
   rejecteddate date default NULL, -- date the suggestion was marked as rejected
3036
   rejecteddate date default NULL, -- date the suggestion was marked as rejected
3037
  `STATUS` varchar(10) NOT NULL default '', -- suggestion status (ASKED, CHECKED, ACCEPTED, or REJECTED)
3037
  `STATUS` varchar(10) NOT NULL default '', -- suggestion status (ASKED, CHECKED, ACCEPTED, or REJECTED)
3038
  `note` mediumtext, -- note entered on the suggestion
3038
  `note` LONGTEXT, -- note entered on the suggestion
3039
  `author` varchar(80) default NULL, -- author of the suggested item
3039
  `author` varchar(80) default NULL, -- author of the suggested item
3040
  `title` varchar(255) default NULL, -- title of the suggested item
3040
  `title` varchar(255) default NULL, -- title of the suggested item
3041
  `copyrightdate` smallint(6) default NULL, -- copyright date of the suggested item
3041
  `copyrightdate` smallint(6) default NULL, -- copyright date of the suggested item
Lines 3046-3056 CREATE TABLE `suggestions` ( -- purchase suggestions Link Here
3046
  `place` varchar(255) default NULL, -- publication place of the suggested item
3046
  `place` varchar(255) default NULL, -- publication place of the suggested item
3047
  `isbn` varchar(30) default NULL, -- isbn of the suggested item
3047
  `isbn` varchar(30) default NULL, -- isbn of the suggested item
3048
  `biblionumber` int(11) default NULL, -- foreign key linking the suggestion to the biblio table after the suggestion has been ordered
3048
  `biblionumber` int(11) default NULL, -- foreign key linking the suggestion to the biblio table after the suggestion has been ordered
3049
  `reason` text, -- reason for accepting or rejecting the suggestion
3049
  `reason` MEDIUMTEXT, -- reason for accepting or rejecting the suggestion
3050
  `patronreason` text, -- reason for making the suggestion
3050
  `patronreason` MEDIUMTEXT, -- reason for making the suggestion
3051
   budgetid INT(11), -- foreign key linking the suggested budget to the aqbudgets table
3051
   budgetid INT(11), -- foreign key linking the suggested budget to the aqbudgets table
3052
   branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table
3052
   branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table
3053
   collectiontitle text default NULL, -- collection name for the suggested item
3053
   collectiontitle MEDIUMTEXT default NULL, -- collection name for the suggested item
3054
   itemtype VARCHAR(30) default NULL, -- suggested item type
3054
   itemtype VARCHAR(30) default NULL, -- suggested item type
3055
   quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased
3055
   quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased
3056
   currency VARCHAR(10) default NULL, -- suggested currency for the suggested price
3056
   currency VARCHAR(10) default NULL, -- suggested currency for the suggested price
Lines 3072-3085 CREATE TABLE `suggestions` ( -- purchase suggestions Link Here
3072
DROP TABLE IF EXISTS vendor_edi_accounts;
3072
DROP TABLE IF EXISTS vendor_edi_accounts;
3073
CREATE TABLE IF NOT EXISTS vendor_edi_accounts (
3073
CREATE TABLE IF NOT EXISTS vendor_edi_accounts (
3074
  id INT(11) NOT NULL auto_increment,
3074
  id INT(11) NOT NULL auto_increment,
3075
  description TEXT NOT NULL,
3075
  description MEDIUMTEXT NOT NULL,
3076
  host VARCHAR(40),
3076
  host VARCHAR(40),
3077
  username VARCHAR(40),
3077
  username VARCHAR(40),
3078
  password VARCHAR(40),
3078
  password VARCHAR(40),
3079
  last_activity DATE,
3079
  last_activity DATE,
3080
  vendor_id INT(11) REFERENCES aqbooksellers( id ),
3080
  vendor_id INT(11) REFERENCES aqbooksellers( id ),
3081
  download_directory TEXT,
3081
  download_directory MEDIUMTEXT,
3082
  upload_directory TEXT,
3082
  upload_directory MEDIUMTEXT,
3083
  san VARCHAR(20),
3083
  san VARCHAR(20),
3084
  id_code_qualifier VARCHAR(3) default '14',
3084
  id_code_qualifier VARCHAR(3) default '14',
3085
  transport VARCHAR(6) default 'FTP',
3085
  transport VARCHAR(6) default 'FTP',
Lines 3108-3117 CREATE TABLE IF NOT EXISTS edifact_messages ( Link Here
3108
  transfer_date DATE,
3108
  transfer_date DATE,
3109
  vendor_id INT(11) REFERENCES aqbooksellers( id ),
3109
  vendor_id INT(11) REFERENCES aqbooksellers( id ),
3110
  edi_acct  INTEGER REFERENCES vendor_edi_accounts( id ),
3110
  edi_acct  INTEGER REFERENCES vendor_edi_accounts( id ),
3111
  status TEXT,
3111
  status MEDIUMTEXT,
3112
  basketno INT(11) REFERENCES aqbasket( basketno),
3112
  basketno INT(11) REFERENCES aqbasket( basketno),
3113
  raw_msg MEDIUMTEXT,
3113
  raw_msg LONGTEXT,
3114
  filename TEXT,
3114
  filename MEDIUMTEXT,
3115
  deleted BOOLEAN NOT NULL DEFAULT 0,
3115
  deleted BOOLEAN NOT NULL DEFAULT 0,
3116
  PRIMARY KEY  (id),
3116
  PRIMARY KEY  (id),
3117
  KEY vendorid ( vendor_id),
3117
  KEY vendorid ( vendor_id),
Lines 3129-3135 CREATE TABLE IF NOT EXISTS edifact_messages ( Link Here
3129
DROP TABLE IF EXISTS aqinvoices;
3129
DROP TABLE IF EXISTS aqinvoices;
3130
CREATE TABLE aqinvoices (
3130
CREATE TABLE aqinvoices (
3131
  invoiceid int(11) NOT NULL AUTO_INCREMENT,    -- ID of the invoice, primary key
3131
  invoiceid int(11) NOT NULL AUTO_INCREMENT,    -- ID of the invoice, primary key
3132
  invoicenumber mediumtext NOT NULL,    -- Name of invoice
3132
  invoicenumber LONGTEXT NOT NULL,    -- Name of invoice
3133
  booksellerid int(11) NOT NULL,    -- foreign key to aqbooksellers
3133
  booksellerid int(11) NOT NULL,    -- foreign key to aqbooksellers
3134
  shipmentdate date default NULL,   -- date of shipment
3134
  shipmentdate date default NULL,   -- date of shipment
3135
  billingdate date default NULL,    -- date of billing
3135
  billingdate date default NULL,    -- date of billing
Lines 3163-3172 CREATE TABLE `aqorders` ( -- information related to the basket line items Link Here
3163
  `unitprice_tax_included` decimal(28,6) default NULL, -- the unit price including tax (on receiving)
3163
  `unitprice_tax_included` decimal(28,6) default NULL, -- the unit price including tax (on receiving)
3164
  `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far
3164
  `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far
3165
  `datecancellationprinted` date default NULL, -- the date the line item was deleted
3165
  `datecancellationprinted` date default NULL, -- the date the line item was deleted
3166
  `cancellationreason` text default NULL, -- reason of cancellation
3166
  `cancellationreason` MEDIUMTEXT default NULL, -- reason of cancellation
3167
  `order_internalnote` mediumtext, -- notes related to this order line, made for staff
3167
  `order_internalnote` LONGTEXT, -- notes related to this order line, made for staff
3168
  `order_vendornote` mediumtext, -- notes related to this order line, made for vendor
3168
  `order_vendornote` LONGTEXT, -- notes related to this order line, made for vendor
3169
  `purchaseordernumber` mediumtext, -- not used? always NULL
3169
  `purchaseordernumber` LONGTEXT, -- not used? always NULL
3170
  `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno)
3170
  `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno)
3171
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified
3171
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified
3172
  `rrp` decimal(13,2) DEFAULT NULL, -- the replacement cost for this line item
3172
  `rrp` decimal(13,2) DEFAULT NULL, -- the replacement cost for this line item
Lines 3197-3203 CREATE TABLE `aqorders` ( -- information related to the basket line items Link Here
3197
  line_item_id varchar(35) default NULL, -- Supplier's article id for Edifact orderline
3197
  line_item_id varchar(35) default NULL, -- Supplier's article id for Edifact orderline
3198
  suppliers_reference_number varchar(35) default NULL, -- Suppliers unique edifact quote ref
3198
  suppliers_reference_number varchar(35) default NULL, -- Suppliers unique edifact quote ref
3199
  suppliers_reference_qualifier varchar(3) default NULL, -- Type of number above usually 'QLI'
3199
  suppliers_reference_qualifier varchar(3) default NULL, -- Type of number above usually 'QLI'
3200
  `suppliers_report` text COLLATE utf8mb4_unicode_ci, -- reports received from suppliers
3200
  `suppliers_report` MEDIUMTEXT COLLATE utf8mb4_unicode_ci, -- reports received from suppliers
3201
  PRIMARY KEY  (`ordernumber`),
3201
  PRIMARY KEY  (`ordernumber`),
3202
  KEY `basketno` (`basketno`),
3202
  KEY `basketno` (`basketno`),
3203
  KEY `biblionumber` (`biblionumber`),
3203
  KEY `biblionumber` (`biblionumber`),
Lines 3338-3345 CREATE TABLE ratings ( -- information related to the star ratings in the OPAC Link Here
3338
DROP TABLE IF EXISTS quotes;
3338
DROP TABLE IF EXISTS quotes;
3339
CREATE TABLE `quotes` ( -- data for the quote of the day feature
3339
CREATE TABLE `quotes` ( -- data for the quote of the day feature
3340
  `id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the quote
3340
  `id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the quote
3341
  `source` text DEFAULT NULL, -- source/credit for the quote
3341
  `source` MEDIUMTEXT DEFAULT NULL, -- source/credit for the quote
3342
  `text` mediumtext NOT NULL, -- text of the quote
3342
  `text` LONGTEXT NOT NULL, -- text of the quote
3343
  `timestamp` datetime NOT NULL, -- date and time that the quote last appeared in the opac
3343
  `timestamp` datetime NOT NULL, -- date and time that the quote last appeared in the opac
3344
  PRIMARY KEY (`id`)
3344
  PRIMARY KEY (`id`)
3345
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3345
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Lines 3390-3424 CREATE TABLE IF NOT EXISTS `borrower_modifications` ( Link Here
3390
  `verification_token` varchar(255) NOT NULL DEFAULT '',
3390
  `verification_token` varchar(255) NOT NULL DEFAULT '',
3391
  `borrowernumber` int(11) NOT NULL DEFAULT '0',
3391
  `borrowernumber` int(11) NOT NULL DEFAULT '0',
3392
  `cardnumber` varchar(32) DEFAULT NULL,
3392
  `cardnumber` varchar(32) DEFAULT NULL,
3393
  `surname` mediumtext,
3393
  `surname` LONGTEXT,
3394
  `firstname` text,
3394
  `firstname` MEDIUMTEXT,
3395
  `title` mediumtext,
3395
  `title` LONGTEXT,
3396
  `othernames` mediumtext,
3396
  `othernames` LONGTEXT,
3397
  `initials` text,
3397
  `initials` MEDIUMTEXT,
3398
  `streetnumber` varchar(10) DEFAULT NULL,
3398
  `streetnumber` varchar(10) DEFAULT NULL,
3399
  `streettype` varchar(50) DEFAULT NULL,
3399
  `streettype` varchar(50) DEFAULT NULL,
3400
  `address` mediumtext,
3400
  `address` LONGTEXT,
3401
  `address2` text,
3401
  `address2` MEDIUMTEXT,
3402
  `city` mediumtext,
3402
  `city` LONGTEXT,
3403
  `state` text,
3403
  `state` MEDIUMTEXT,
3404
  `zipcode` varchar(25) DEFAULT NULL,
3404
  `zipcode` varchar(25) DEFAULT NULL,
3405
  `country` text,
3405
  `country` MEDIUMTEXT,
3406
  `email` mediumtext,
3406
  `email` LONGTEXT,
3407
  `phone` text,
3407
  `phone` MEDIUMTEXT,
3408
  `mobile` varchar(50) DEFAULT NULL,
3408
  `mobile` varchar(50) DEFAULT NULL,
3409
  `fax` mediumtext,
3409
  `fax` LONGTEXT,
3410
  `emailpro` text,
3410
  `emailpro` MEDIUMTEXT,
3411
  `phonepro` text,
3411
  `phonepro` MEDIUMTEXT,
3412
  `B_streetnumber` varchar(10) DEFAULT NULL,
3412
  `B_streetnumber` varchar(10) DEFAULT NULL,
3413
  `B_streettype` varchar(50) DEFAULT NULL,
3413
  `B_streettype` varchar(50) DEFAULT NULL,
3414
  `B_address` varchar(100) DEFAULT NULL,
3414
  `B_address` varchar(100) DEFAULT NULL,
3415
  `B_address2` text,
3415
  `B_address2` MEDIUMTEXT,
3416
  `B_city` mediumtext,
3416
  `B_city` LONGTEXT,
3417
  `B_state` text,
3417
  `B_state` MEDIUMTEXT,
3418
  `B_zipcode` varchar(25) DEFAULT NULL,
3418
  `B_zipcode` varchar(25) DEFAULT NULL,
3419
  `B_country` text,
3419
  `B_country` MEDIUMTEXT,
3420
  `B_email` text,
3420
  `B_email` MEDIUMTEXT,
3421
  `B_phone` mediumtext,
3421
  `B_phone` LONGTEXT,
3422
  `dateofbirth` date DEFAULT NULL,
3422
  `dateofbirth` date DEFAULT NULL,
3423
  `branchcode` varchar(10) DEFAULT NULL,
3423
  `branchcode` varchar(10) DEFAULT NULL,
3424
  `categorycode` varchar(10) DEFAULT NULL,
3424
  `categorycode` varchar(10) DEFAULT NULL,
Lines 3429-3445 CREATE TABLE IF NOT EXISTS `borrower_modifications` ( Link Here
3429
  `lost` tinyint(1) DEFAULT NULL,
3429
  `lost` tinyint(1) DEFAULT NULL,
3430
  `debarred` date DEFAULT NULL,
3430
  `debarred` date DEFAULT NULL,
3431
  `debarredcomment` varchar(255) DEFAULT NULL,
3431
  `debarredcomment` varchar(255) DEFAULT NULL,
3432
  `contactname` mediumtext,
3432
  `contactname` LONGTEXT,
3433
  `contactfirstname` text,
3433
  `contactfirstname` MEDIUMTEXT,
3434
  `contacttitle` text,
3434
  `contacttitle` MEDIUMTEXT,
3435
  `guarantorid` int(11) DEFAULT NULL,
3435
  `guarantorid` int(11) DEFAULT NULL,
3436
  `borrowernotes` mediumtext,
3436
  `borrowernotes` LONGTEXT,
3437
  `relationship` varchar(100) DEFAULT NULL,
3437
  `relationship` varchar(100) DEFAULT NULL,
3438
  `sex` varchar(1) DEFAULT NULL,
3438
  `sex` varchar(1) DEFAULT NULL,
3439
  `password` varchar(30) DEFAULT NULL,
3439
  `password` varchar(30) DEFAULT NULL,
3440
  `flags` int(11) DEFAULT NULL,
3440
  `flags` int(11) DEFAULT NULL,
3441
  `userid` varchar(75) DEFAULT NULL,
3441
  `userid` varchar(75) DEFAULT NULL,
3442
  `opacnote` mediumtext,
3442
  `opacnote` LONGTEXT,
3443
  `contactnote` varchar(255) DEFAULT NULL,
3443
  `contactnote` varchar(255) DEFAULT NULL,
3444
  `sort1` varchar(80) DEFAULT NULL,
3444
  `sort1` varchar(80) DEFAULT NULL,
3445
  `sort2` varchar(80) DEFAULT NULL,
3445
  `sort2` varchar(80) DEFAULT NULL,
Lines 3448-3460 CREATE TABLE IF NOT EXISTS `borrower_modifications` ( Link Here
3448
  `altcontactaddress1` varchar(255) DEFAULT NULL,
3448
  `altcontactaddress1` varchar(255) DEFAULT NULL,
3449
  `altcontactaddress2` varchar(255) DEFAULT NULL,
3449
  `altcontactaddress2` varchar(255) DEFAULT NULL,
3450
  `altcontactaddress3` varchar(255) DEFAULT NULL,
3450
  `altcontactaddress3` varchar(255) DEFAULT NULL,
3451
  `altcontactstate` text,
3451
  `altcontactstate` MEDIUMTEXT,
3452
  `altcontactzipcode` varchar(50) DEFAULT NULL,
3452
  `altcontactzipcode` varchar(50) DEFAULT NULL,
3453
  `altcontactcountry` text,
3453
  `altcontactcountry` MEDIUMTEXT,
3454
  `altcontactphone` varchar(50) DEFAULT NULL,
3454
  `altcontactphone` varchar(50) DEFAULT NULL,
3455
  `smsalertnumber` varchar(50) DEFAULT NULL,
3455
  `smsalertnumber` varchar(50) DEFAULT NULL,
3456
  `privacy` int(11) DEFAULT NULL,
3456
  `privacy` int(11) DEFAULT NULL,
3457
  `extended_attributes` text DEFAULT NULL,
3457
  `extended_attributes` MEDIUMTEXT DEFAULT NULL,
3458
  PRIMARY KEY (`verification_token` (191),`borrowernumber`),
3458
  PRIMARY KEY (`verification_token` (191),`borrowernumber`),
3459
  KEY `verification_token` (`verification_token` (191)),
3459
  KEY `verification_token` (`verification_token` (191)),
3460
  KEY `borrowernumber` (`borrowernumber`)
3460
  KEY `borrowernumber` (`borrowernumber`)
Lines 3468-3475 DROP TABLE IF EXISTS uploaded_files; Link Here
3468
CREATE TABLE uploaded_files (
3468
CREATE TABLE uploaded_files (
3469
    id int(11) NOT NULL AUTO_INCREMENT,
3469
    id int(11) NOT NULL AUTO_INCREMENT,
3470
    hashvalue CHAR(40) NOT NULL,
3470
    hashvalue CHAR(40) NOT NULL,
3471
    filename TEXT NOT NULL,
3471
    filename MEDIUMTEXT NOT NULL,
3472
    dir TEXT NOT NULL,
3472
    dir MEDIUMTEXT NOT NULL,
3473
    filesize int(11),
3473
    filesize int(11),
3474
    dtcreated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3474
    dtcreated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3475
    uploadcategorycode tinytext,
3475
    uploadcategorycode tinytext,
Lines 3490-3496 CREATE TABLE linktracker ( Link Here
3490
   biblionumber int(11) DEFAULT NULL, -- biblionumber of the record the link is from
3490
   biblionumber int(11) DEFAULT NULL, -- biblionumber of the record the link is from
3491
   itemnumber int(11) DEFAULT NULL, -- itemnumber if applicable that the link was from
3491
   itemnumber int(11) DEFAULT NULL, -- itemnumber if applicable that the link was from
3492
   borrowernumber int(11) DEFAULT NULL, -- borrowernumber who clicked the link
3492
   borrowernumber int(11) DEFAULT NULL, -- borrowernumber who clicked the link
3493
   url text, -- the link itself
3493
   url MEDIUMTEXT, -- the link itself
3494
   timeclicked datetime DEFAULT NULL, -- the date and time the link was clicked
3494
   timeclicked datetime DEFAULT NULL, -- the date and time the link was clicked
3495
   PRIMARY KEY (id),
3495
   PRIMARY KEY (id),
3496
   KEY bibidx (biblionumber),
3496
   KEY bibidx (biblionumber),
Lines 3506-3512 CREATE TABLE linktracker ( Link Here
3506
CREATE TABLE IF NOT EXISTS plugin_data (
3506
CREATE TABLE IF NOT EXISTS plugin_data (
3507
  plugin_class varchar(255) NOT NULL,
3507
  plugin_class varchar(255) NOT NULL,
3508
  plugin_key varchar(255) NOT NULL,
3508
  plugin_key varchar(255) NOT NULL,
3509
  plugin_value text,
3509
  plugin_value MEDIUMTEXT,
3510
  PRIMARY KEY ( `plugin_class` (191), `plugin_key` (191) )
3510
  PRIMARY KEY ( `plugin_class` (191), `plugin_key` (191) )
3511
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3511
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3512
3512
Lines 3556-3562 ALTER TABLE `patron_list_patrons` Link Here
3556
3556
3557
CREATE TABLE IF NOT EXISTS marc_modification_templates (
3557
CREATE TABLE IF NOT EXISTS marc_modification_templates (
3558
    template_id int(11) NOT NULL AUTO_INCREMENT,
3558
    template_id int(11) NOT NULL AUTO_INCREMENT,
3559
    name text NOT NULL,
3559
    name MEDIUMTEXT NOT NULL,
3560
    PRIMARY KEY (template_id)
3560
    PRIMARY KEY (template_id)
3561
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3561
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3562
3562
Lines 3575-3590 CREATE TABLE IF NOT EXISTS marc_modification_template_actions ( Link Here
3575
  field_value varchar(100) DEFAULT NULL,
3575
  field_value varchar(100) DEFAULT NULL,
3576
  to_field varchar(3) DEFAULT NULL,
3576
  to_field varchar(3) DEFAULT NULL,
3577
  to_subfield varchar(1) DEFAULT NULL,
3577
  to_subfield varchar(1) DEFAULT NULL,
3578
  to_regex_search text,
3578
  to_regex_search MEDIUMTEXT,
3579
  to_regex_replace text,
3579
  to_regex_replace MEDIUMTEXT,
3580
  to_regex_modifiers varchar(8) DEFAULT '',
3580
  to_regex_modifiers varchar(8) DEFAULT '',
3581
  conditional enum('if','unless') DEFAULT NULL,
3581
  conditional enum('if','unless') DEFAULT NULL,
3582
  conditional_field varchar(3) DEFAULT NULL,
3582
  conditional_field varchar(3) DEFAULT NULL,
3583
  conditional_subfield varchar(1) DEFAULT NULL,
3583
  conditional_subfield varchar(1) DEFAULT NULL,
3584
  conditional_comparison enum('exists','not_exists','equals','not_equals') DEFAULT NULL,
3584
  conditional_comparison enum('exists','not_exists','equals','not_equals') DEFAULT NULL,
3585
  conditional_value text,
3585
  conditional_value MEDIUMTEXT,
3586
  conditional_regex tinyint(1) NOT NULL DEFAULT '0',
3586
  conditional_regex tinyint(1) NOT NULL DEFAULT '0',
3587
  description text,
3587
  description MEDIUMTEXT,
3588
  PRIMARY KEY (mmta_id),
3588
  PRIMARY KEY (mmta_id),
3589
  CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
3589
  CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
3590
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3590
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Lines 3696-3702 CREATE TABLE `localization` ( Link Here
3696
      entity varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
3696
      entity varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
3697
      code varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
3697
      code varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
3698
      lang varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL, -- could be a foreign key
3698
      lang varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL, -- could be a foreign key
3699
      translation text COLLATE utf8mb4_unicode_ci,
3699
      translation MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
3700
      PRIMARY KEY (localization_id),
3700
      PRIMARY KEY (localization_id),
3701
      UNIQUE KEY `entity_code_lang` (`entity`,`code`,`lang`)
3701
      UNIQUE KEY `entity_code_lang` (`entity`,`code`,`lang`)
3702
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3702
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Lines 3744-3751 CREATE TABLE `courses` ( Link Here
3744
  `section` varchar(255) DEFAULT NULL, -- the 'section' of a course
3744
  `section` varchar(255) DEFAULT NULL, -- the 'section' of a course
3745
  `course_name` varchar(255) DEFAULT NULL, -- the name of the course
3745
  `course_name` varchar(255) DEFAULT NULL, -- the name of the course
3746
  `term` varchar(80) DEFAULT NULL, -- the authorised value for the TERM
3746
  `term` varchar(80) DEFAULT NULL, -- the authorised value for the TERM
3747
  `staff_note` mediumtext, -- the text of the staff only note
3747
  `staff_note` LONGTEXT, -- the text of the staff only note
3748
  `public_note` mediumtext, -- the text of the public / opac note
3748
  `public_note` LONGTEXT, -- the text of the public / opac note
3749
  `students_count` varchar(20) DEFAULT NULL, -- how many students will be taking this course/section
3749
  `students_count` varchar(20) DEFAULT NULL, -- how many students will be taking this course/section
3750
  `enabled` enum('yes','no') NOT NULL DEFAULT 'yes', -- determines whether the course is active
3750
  `enabled` enum('yes','no') NOT NULL DEFAULT 'yes', -- determines whether the course is active
3751
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3751
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
Lines 3817-3824 CREATE TABLE `course_reserves` ( Link Here
3817
  `cr_id` int(11) NOT NULL AUTO_INCREMENT,
3817
  `cr_id` int(11) NOT NULL AUTO_INCREMENT,
3818
  `course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id
3818
  `course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id
3819
  `ci_id` int(11) NOT NULL, -- foreign key to link to courses_items.ci_id
3819
  `ci_id` int(11) NOT NULL, -- foreign key to link to courses_items.ci_id
3820
  `staff_note` mediumtext, -- staff only note
3820
  `staff_note` LONGTEXT, -- staff only note
3821
  `public_note` mediumtext, -- public, OPAC visible note
3821
  `public_note` LONGTEXT, -- public, OPAC visible note
3822
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3822
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3823
   PRIMARY KEY (`cr_id`),
3823
   PRIMARY KEY (`cr_id`),
3824
   UNIQUE KEY `pseudo_key` (`course_id`,`ci_id`),
3824
   UNIQUE KEY `pseudo_key` (`course_id`,`ci_id`),
Lines 3862-3874 CREATE TABLE `hold_fill_targets` ( Link Here
3862
DROP TABLE IF EXISTS `housebound_profile`;
3862
DROP TABLE IF EXISTS `housebound_profile`;
3863
CREATE TABLE `housebound_profile` (
3863
CREATE TABLE `housebound_profile` (
3864
  `borrowernumber` int(11) NOT NULL, -- Number of the borrower associated with this profile.
3864
  `borrowernumber` int(11) NOT NULL, -- Number of the borrower associated with this profile.
3865
  `day` text NOT NULL,  -- The preferred day of the week for delivery.
3865
  `day` MEDIUMTEXT NOT NULL,  -- The preferred day of the week for delivery.
3866
  `frequency` text NOT NULL, -- The Authorised_Value definining the pattern for delivery.
3866
  `frequency` MEDIUMTEXT NOT NULL, -- The Authorised_Value definining the pattern for delivery.
3867
  `fav_itemtypes` text default NULL, -- Free text describing preferred itemtypes.
3867
  `fav_itemtypes` MEDIUMTEXT default NULL, -- Free text describing preferred itemtypes.
3868
  `fav_subjects` text default NULL, -- Free text describing preferred subjects.
3868
  `fav_subjects` MEDIUMTEXT default NULL, -- Free text describing preferred subjects.
3869
  `fav_authors` text default NULL, -- Free text describing preferred authors.
3869
  `fav_authors` MEDIUMTEXT default NULL, -- Free text describing preferred authors.
3870
  `referral` text default NULL, -- Free text indicating how the borrower was added to the service.
3870
  `referral` MEDIUMTEXT default NULL, -- Free text indicating how the borrower was added to the service.
3871
  `notes` text default NULL, -- Free text for additional notes.
3871
  `notes` MEDIUMTEXT default NULL, -- Free text for additional notes.
3872
  PRIMARY KEY  (`borrowernumber`),
3872
  PRIMARY KEY  (`borrowernumber`),
3873
  CONSTRAINT `housebound_profile_bnfk`
3873
  CONSTRAINT `housebound_profile_bnfk`
3874
    FOREIGN KEY (`borrowernumber`)
3874
    FOREIGN KEY (`borrowernumber`)
Lines 3930-3945 CREATE TABLE `article_requests` ( Link Here
3930
  `biblionumber` int(11) NOT NULL,
3930
  `biblionumber` int(11) NOT NULL,
3931
  `itemnumber` int(11) DEFAULT NULL,
3931
  `itemnumber` int(11) DEFAULT NULL,
3932
  `branchcode` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
3932
  `branchcode` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
3933
  `title` text,
3933
  `title` MEDIUMTEXT,
3934
  `author` text,
3934
  `author` MEDIUMTEXT,
3935
  `volume` text,
3935
  `volume` MEDIUMTEXT,
3936
  `issue` text,
3936
  `issue` MEDIUMTEXT,
3937
  `date` text,
3937
  `date` MEDIUMTEXT,
3938
  `pages` text,
3938
  `pages` MEDIUMTEXT,
3939
  `chapters` text,
3939
  `chapters` MEDIUMTEXT,
3940
  `patron_notes` text,
3940
  `patron_notes` MEDIUMTEXT,
3941
  `status` enum('PENDING','PROCESSING','COMPLETED','CANCELED') NOT NULL DEFAULT 'PENDING',
3941
  `status` enum('PENDING','PROCESSING','COMPLETED','CANCELED') NOT NULL DEFAULT 'PENDING',
3942
  `notes` text,
3942
  `notes` MEDIUMTEXT,
3943
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3943
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
3944
  `updated_on` timestamp NULL DEFAULT NULL,
3944
  `updated_on` timestamp NULL DEFAULT NULL,
3945
  PRIMARY KEY (`id`),
3945
  PRIMARY KEY (`id`),
Lines 3996-4002 CREATE TABLE deletedbiblio_metadata ( Link Here
3996
CREATE TABLE IF NOT EXISTS club_templates (
3996
CREATE TABLE IF NOT EXISTS club_templates (
3997
  id int(11) NOT NULL AUTO_INCREMENT,
3997
  id int(11) NOT NULL AUTO_INCREMENT,
3998
  `name` tinytext NOT NULL,
3998
  `name` tinytext NOT NULL,
3999
  description text,
3999
  description MEDIUMTEXT,
4000
  is_enrollable_from_opac tinyint(1) NOT NULL DEFAULT '0',
4000
  is_enrollable_from_opac tinyint(1) NOT NULL DEFAULT '0',
4001
  is_email_required tinyint(1) NOT NULL DEFAULT '0',
4001
  is_email_required tinyint(1) NOT NULL DEFAULT '0',
4002
  branchcode varchar(10) NULL DEFAULT NULL,
4002
  branchcode varchar(10) NULL DEFAULT NULL,
Lines 4016-4022 CREATE TABLE IF NOT EXISTS clubs ( Link Here
4016
  id int(11) NOT NULL AUTO_INCREMENT,
4016
  id int(11) NOT NULL AUTO_INCREMENT,
4017
  club_template_id int(11) NOT NULL,
4017
  club_template_id int(11) NOT NULL,
4018
  `name` tinytext NOT NULL,
4018
  `name` tinytext NOT NULL,
4019
  description text,
4019
  description MEDIUMTEXT,
4020
  date_start date DEFAULT NULL,
4020
  date_start date DEFAULT NULL,
4021
  date_end date DEFAULT NULL,
4021
  date_end date DEFAULT NULL,
4022
  branchcode varchar(10) NULL DEFAULT NULL,
4022
  branchcode varchar(10) NULL DEFAULT NULL,
Lines 4059-4065 CREATE TABLE IF NOT EXISTS club_template_enrollment_fields ( Link Here
4059
  id int(11) NOT NULL AUTO_INCREMENT,
4059
  id int(11) NOT NULL AUTO_INCREMENT,
4060
  club_template_id int(11) NOT NULL,
4060
  club_template_id int(11) NOT NULL,
4061
  `name` tinytext NOT NULL,
4061
  `name` tinytext NOT NULL,
4062
  description text,
4062
  description MEDIUMTEXT,
4063
  authorised_value_category varchar(16) DEFAULT NULL,
4063
  authorised_value_category varchar(16) DEFAULT NULL,
4064
  PRIMARY KEY (id),
4064
  PRIMARY KEY (id),
4065
  KEY club_template_id (club_template_id),
4065
  KEY club_template_id (club_template_id),
Lines 4074-4080 CREATE TABLE IF NOT EXISTS club_enrollment_fields ( Link Here
4074
  id int(11) NOT NULL AUTO_INCREMENT,
4074
  id int(11) NOT NULL AUTO_INCREMENT,
4075
  club_enrollment_id int(11) NOT NULL,
4075
  club_enrollment_id int(11) NOT NULL,
4076
  club_template_enrollment_field_id int(11) NOT NULL,
4076
  club_template_enrollment_field_id int(11) NOT NULL,
4077
  `value` text NOT NULL,
4077
  `value` MEDIUMTEXT NOT NULL,
4078
  PRIMARY KEY (id),
4078
  PRIMARY KEY (id),
4079
  KEY club_enrollment_id (club_enrollment_id),
4079
  KEY club_enrollment_id (club_enrollment_id),
4080
  KEY club_template_enrollment_field_id (club_template_enrollment_field_id),
4080
  KEY club_template_enrollment_field_id (club_template_enrollment_field_id),
Lines 4090-4096 CREATE TABLE IF NOT EXISTS club_template_fields ( Link Here
4090
  id int(11) NOT NULL AUTO_INCREMENT,
4090
  id int(11) NOT NULL AUTO_INCREMENT,
4091
  club_template_id int(11) NOT NULL,
4091
  club_template_id int(11) NOT NULL,
4092
  `name` tinytext NOT NULL,
4092
  `name` tinytext NOT NULL,
4093
  description text,
4093
  description MEDIUMTEXT,
4094
  authorised_value_category varchar(16) DEFAULT NULL,
4094
  authorised_value_category varchar(16) DEFAULT NULL,
4095
  PRIMARY KEY (id),
4095
  PRIMARY KEY (id),
4096
  KEY club_template_id (club_template_id),
4096
  KEY club_template_id (club_template_id),
Lines 4105-4111 CREATE TABLE IF NOT EXISTS club_fields ( Link Here
4105
  id int(11) NOT NULL AUTO_INCREMENT,
4105
  id int(11) NOT NULL AUTO_INCREMENT,
4106
  club_template_field_id int(11) NOT NULL,
4106
  club_template_field_id int(11) NOT NULL,
4107
  club_id int(11) NOT NULL,
4107
  club_id int(11) NOT NULL,
4108
  `value` text,
4108
  `value` MEDIUMTEXT,
4109
  PRIMARY KEY (id),
4109
  PRIMARY KEY (id),
4110
  KEY club_template_field_id (club_template_field_id),
4110
  KEY club_template_field_id (club_template_field_id),
4111
  KEY club_id (club_id),
4111
  KEY club_id (club_id),
Lines 4132-4139 CREATE TABLE illrequests ( Link Here
4132
    medium varchar(30) DEFAULT NULL,            -- The Koha request type
4132
    medium varchar(30) DEFAULT NULL,            -- The Koha request type
4133
    accessurl varchar(500) DEFAULT NULL,        -- Potential URL for accessing item
4133
    accessurl varchar(500) DEFAULT NULL,        -- Potential URL for accessing item
4134
    cost varchar(20) DEFAULT NULL,              -- Cost of request
4134
    cost varchar(20) DEFAULT NULL,              -- Cost of request
4135
    notesopac text DEFAULT NULL,                -- Patron notes attached to request
4135
    notesopac MEDIUMTEXT DEFAULT NULL,                -- Patron notes attached to request
4136
    notesstaff text DEFAULT NULL,               -- Staff notes attached to request
4136
    notesstaff MEDIUMTEXT DEFAULT NULL,               -- Staff notes attached to request
4137
    orderid varchar(50) DEFAULT NULL,           -- Backend id attached to request
4137
    orderid varchar(50) DEFAULT NULL,           -- Backend id attached to request
4138
    backend varchar(20) DEFAULT NULL,           -- The backend used to create request
4138
    backend varchar(20) DEFAULT NULL,           -- The backend used to create request
4139
    CONSTRAINT `illrequests_bnfk`
4139
    CONSTRAINT `illrequests_bnfk`
Lines 4154-4160 DROP TABLE IF EXISTS `illrequestattributes`; Link Here
4154
CREATE TABLE illrequestattributes (
4154
CREATE TABLE illrequestattributes (
4155
    illrequest_id bigint(20) unsigned NOT NULL, -- ILL request number
4155
    illrequest_id bigint(20) unsigned NOT NULL, -- ILL request number
4156
    type varchar(200) NOT NULL,                 -- API ILL property name
4156
    type varchar(200) NOT NULL,                 -- API ILL property name
4157
    value text NOT NULL,                        -- API ILL property value
4157
    value MEDIUMTEXT NOT NULL,                        -- API ILL property value
4158
    PRIMARY KEY  (`illrequest_id`, `type` (191)),
4158
    PRIMARY KEY  (`illrequest_id`, `type` (191)),
4159
    CONSTRAINT `illrequestattributes_ifk`
4159
    CONSTRAINT `illrequestattributes_ifk`
4160
      FOREIGN KEY (illrequest_id)
4160
      FOREIGN KEY (illrequest_id)
4161
- 

Return to bug 18336