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 |
- |
|
|