3 -- Host: localhost Database: koha30test
4 -- ------------------------------------------------------
5 -- Server version 4.1.22
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
11 /*!40101 SET NAMES utf8 */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19 -- Table structure for table `auth_header`
22 DROP TABLE IF EXISTS `auth_header`;
23 CREATE TABLE `auth_header` (
24 `authid` bigint(20) unsigned NOT NULL auto_increment,
25 `authtypecode` varchar(10) NOT NULL default '',
26 `datecreated` date default NULL,
27 `datemodified` date default NULL,
28 `origincode` varchar(20) default NULL,
29 `authtrees` mediumtext,
31 `linkid` bigint(20) default NULL,
32 `marcxml` longtext NOT NULL,
33 PRIMARY KEY (`authid`),
34 KEY `origincode` (`origincode`)
35 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
38 -- Table structure for table `auth_subfield_structure`
41 DROP TABLE IF EXISTS `auth_subfield_structure`;
42 CREATE TABLE `auth_subfield_structure` (
43 `authtypecode` varchar(10) NOT NULL default '',
44 `tagfield` varchar(3) NOT NULL default '',
45 `tagsubfield` varchar(1) NOT NULL default '',
46 `liblibrarian` varchar(255) NOT NULL default '',
47 `libopac` varchar(255) NOT NULL default '',
48 `repeatable` tinyint(4) NOT NULL default 0,
49 `mandatory` tinyint(4) NOT NULL default 0,
50 `tab` tinyint(1) default NULL,
51 `authorised_value` varchar(10) default NULL,
52 `value_builder` varchar(80) default NULL,
53 `seealso` varchar(255) default NULL,
54 `isurl` tinyint(1) default NULL,
55 `hidden` tinyint(3) NOT NULL default 0,
56 `linkid` tinyint(1) NOT NULL default 0,
57 `kohafield` varchar(45) NULL default '',
58 `frameworkcode` varchar(10) NOT NULL default '',
59 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
60 KEY `tab` (`authtypecode`,`tab`)
61 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
64 -- Table structure for table `auth_tag_structure`
67 DROP TABLE IF EXISTS `auth_tag_structure`;
68 CREATE TABLE `auth_tag_structure` (
69 `authtypecode` varchar(10) NOT NULL default '',
70 `tagfield` varchar(3) NOT NULL default '',
71 `liblibrarian` varchar(255) NOT NULL default '',
72 `libopac` varchar(255) NOT NULL default '',
73 `repeatable` tinyint(4) NOT NULL default 0,
74 `mandatory` tinyint(4) NOT NULL default 0,
75 `authorised_value` varchar(10) default NULL,
76 PRIMARY KEY (`authtypecode`,`tagfield`),
77 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
78 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
81 -- Table structure for table `auth_types`
84 DROP TABLE IF EXISTS `auth_types`;
85 CREATE TABLE `auth_types` (
86 `authtypecode` varchar(10) NOT NULL default '',
87 `authtypetext` varchar(255) NOT NULL default '',
88 `auth_tag_to_report` varchar(3) NOT NULL default '',
89 `summary` mediumtext NOT NULL,
90 PRIMARY KEY (`authtypecode`)
91 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
94 -- Table structure for table `authorised_values`
97 DROP TABLE IF EXISTS `authorised_values`;
98 CREATE TABLE `authorised_values` ( -- stores values for authorized values categories and values
99 `id` int(11) NOT NULL auto_increment, -- unique key, used to identify the authorized value
100 `category` varchar(10) NOT NULL default '', -- key used to identify the authorized value category
101 `authorised_value` varchar(80) NOT NULL default '', -- code use to identify the authorized value
102 `lib` varchar(80) default NULL, -- authorized value description as printed in the staff client
103 `lib_opac` VARCHAR(80) default NULL, -- authorized value description as printed in the OPAC
104 `imageurl` varchar(200) default NULL, -- authorized value URL
106 KEY `name` (`category`),
108 KEY `auth_value_idx` (`authorised_value`)
109 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
112 -- Table structure for table `biblio`
115 DROP TABLE IF EXISTS `biblio`;
116 CREATE TABLE `biblio` ( -- table that stores bibliographic information
117 `biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
118 `frameworkcode` varchar(4) NOT NULL default '', -- foriegn key from the biblio_framework table to identify which framework was used in cataloging this record
119 `author` mediumtext, -- statement of responsibility from MARC record (100 in MARC21)
120 `title` mediumtext, -- title (without the subtitle) from the MARC record (245 in MARC21)
121 `unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240 in MARC21)
122 `notes` mediumtext, -- values from the general notes field in the MARC record (500 in MARC21) split by bar (|)
123 `serial` tinyint(1) default NULL, -- foreign key, linking to the subscriptionid in the serial table
124 `seriestitle` mediumtext,
125 `copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
126 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
127 `datecreated` DATE NOT NULL, -- the date this record was added to Koha
128 `abstract` mediumtext, -- summary from the MARC record (520 in MARC21)
129 PRIMARY KEY (`biblionumber`),
130 KEY `blbnoidx` (`biblionumber`)
131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
134 -- Table structure for table `biblio_framework`
137 DROP TABLE IF EXISTS `biblio_framework`;
138 CREATE TABLE `biblio_framework` (
139 `frameworkcode` varchar(4) NOT NULL default '',
140 `frameworktext` varchar(255) NOT NULL default '',
141 PRIMARY KEY (`frameworkcode`)
142 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
145 -- Table structure for table `biblioitems`
148 DROP TABLE IF EXISTS `biblioitems`;
149 CREATE TABLE `biblioitems` (
150 `biblioitemnumber` int(11) NOT NULL auto_increment,
151 `biblionumber` int(11) NOT NULL default 0,
154 `itemtype` varchar(10) default NULL,
155 `isbn` varchar(30) default NULL,
156 `issn` varchar(9) default NULL,
157 `publicationyear` text,
158 `publishercode` varchar(255) default NULL,
159 `volumedate` date default NULL,
161 `collectiontitle` mediumtext default NULL,
162 `collectionissn` text default NULL,
163 `collectionvolume` mediumtext default NULL,
164 `editionstatement` text default NULL,
165 `editionresponsibility` text default NULL,
166 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
167 `illus` varchar(255) default NULL,
168 `pages` varchar(255) default NULL,
170 `size` varchar(255) default NULL,
171 `place` varchar(255) default NULL,
172 `lccn` varchar(25) default NULL,
174 `url` varchar(255) default NULL,
175 `cn_source` varchar(10) default NULL,
176 `cn_class` varchar(30) default NULL,
177 `cn_item` varchar(10) default NULL,
178 `cn_suffix` varchar(10) default NULL,
179 `cn_sort` varchar(30) default NULL,
180 `totalissues` int(10),
181 `marcxml` longtext NOT NULL,
182 PRIMARY KEY (`biblioitemnumber`),
183 KEY `bibinoidx` (`biblioitemnumber`),
184 KEY `bibnoidx` (`biblionumber`),
187 KEY `publishercode` (`publishercode`),
188 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
192 -- Table structure for table `borrowers`
195 DROP TABLE IF EXISTS `borrowers`;
196 CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members
197 `borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers
198 `cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers
199 `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname)
200 `firstname` text, -- patron/borrower's first name
201 `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs.
202 `othernames` mediumtext, -- any other names associated with the patron/borrower
203 `initials` text, -- initials for your patron/borrower
204 `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
205 `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
206 `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address
207 `address2` text, -- the second address line for your patron/borrower's primary address
208 `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address
209 `state` text default NULL, -- the state or province for your patron/borrower's primary address
210 `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
211 `country` text, -- the country for your patron/borrower's primary address
212 `email` mediumtext, -- the primary email address for your patron/borrower's primary address
213 `phone` text, -- the primary phone number for your patron/borrower's primary address
214 `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
215 `fax` mediumtext, -- the fax number for your patron/borrower's primary address
216 `emailpro` text, -- the secondary email addres for your patron/borrower's primary address
217 `phonepro` text, -- the secondary phone number for your patron/borrower's primary address
218 `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
219 `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
220 `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
221 `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address
222 `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address
223 `B_state` text default NULL, -- the state for your patron/borrower's alternate address
224 `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
225 `B_country` text, -- the country for your patron/borrower's alternate address
226 `B_email` text, -- the patron/borrower's alternate email address
227 `B_phone` mediumtext, -- the patron/borrower's alternate phone number
228 `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD)
229 `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch
230 `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category
231 `dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD)
232 `dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD)
233 `gonenoaddress` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having an unconfirmed address
234 `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
235 `debarred` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as being restricted
236 `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name
237 `contactfirstname` text, -- used for children to include first name of guarentor
238 `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor
239 `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations
240 `borrowernotes` mediumtext, -- a note on the patron/borroewr's account that is only visible in the staff client
241 `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor
242 `ethnicity` varchar(50) default NULL, -- unused in Koha
243 `ethnotes` varchar(255) default NULL, -- unused in Koha
244 `sex` varchar(1) default NULL, -- patron/borrower's gender
245 `password` varchar(30) default NULL, -- patron/borrower's encrypted password
246 `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
247 `userid` varchar(30) default NULL, -- patron/borrower's opac and/or staff client log in
248 `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
249 `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
250 `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
251 `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
252 `altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower
253 `altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower
254 `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
255 `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
256 `altcontactaddress3` varchar(255) default NULL, -- the third address line for the alternate contact for the patron/borrower
257 `altcontactstate` text default NULL, -- the city and state for the alternate contact for the patron/borrower
258 `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
259 `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower
260 `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
261 `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
262 `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history
263 UNIQUE KEY `cardnumber` (`cardnumber`),
264 PRIMARY KEY `borrowernumber` (`borrowernumber`),
265 KEY `categorycode` (`categorycode`),
266 KEY `branchcode` (`branchcode`),
267 KEY `userid` (`userid`),
268 KEY `guarantorid` (`guarantorid`),
269 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
270 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
274 -- Table structure for table `borrower_attribute_types`
277 DROP TABLE IF EXISTS `borrower_attribute_types`;
278 CREATE TABLE `borrower_attribute_types` (
279 `code` varchar(10) NOT NULL,
280 `description` varchar(255) NOT NULL,
281 `repeatable` tinyint(1) NOT NULL default 0,
282 `unique_id` tinyint(1) NOT NULL default 0,
283 `opac_display` tinyint(1) NOT NULL default 0,
284 `password_allowed` tinyint(1) NOT NULL default 0,
285 `staff_searchable` tinyint(1) NOT NULL default 0,
286 `authorised_value_category` varchar(10) default NULL,
287 PRIMARY KEY (`code`),
288 KEY `auth_val_cat_idx` (`authorised_value_category`)
289 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
292 -- Table structure for table `borrower_attributes`
295 DROP TABLE IF EXISTS `borrower_attributes`;
296 CREATE TABLE `borrower_attributes` (
297 `borrowernumber` int(11) NOT NULL,
298 `code` varchar(10) NOT NULL,
299 `attribute` varchar(64) default NULL,
300 `password` varchar(64) default NULL,
301 KEY `borrowernumber` (`borrowernumber`),
302 KEY `code_attribute` (`code`, `attribute`),
303 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
304 ON DELETE CASCADE ON UPDATE CASCADE,
305 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
306 ON DELETE CASCADE ON UPDATE CASCADE
307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
309 DROP TABLE IF EXISTS `branch_item_rules`;
310 CREATE TABLE `branch_item_rules` (
311 `branchcode` varchar(10) NOT NULL,
312 `itemtype` varchar(10) NOT NULL,
313 `holdallowed` tinyint(1) default NULL,
314 PRIMARY KEY (`itemtype`,`branchcode`),
315 KEY `branch_item_rules_ibfk_2` (`branchcode`),
316 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
317 ON DELETE CASCADE ON UPDATE CASCADE,
318 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
319 ON DELETE CASCADE ON UPDATE CASCADE
320 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
323 -- Table structure for table `branchcategories`
326 DROP TABLE IF EXISTS `branchcategories`;
327 CREATE TABLE `branchcategories` (
328 `categorycode` varchar(10) NOT NULL default '',
329 `categoryname` varchar(32),
330 `codedescription` mediumtext,
331 `categorytype` varchar(16),
332 PRIMARY KEY (`categorycode`)
333 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
336 -- Table structure for table `branches`
339 DROP TABLE IF EXISTS `branches`;
340 CREATE TABLE `branches` ( -- information about your libraries or branches are stored here
341 `branchcode` varchar(10) NOT NULL default '', -- a unique key assigned to each branch
342 `branchname` mediumtext NOT NULL, -- the name of your library or branch
343 `branchaddress1` mediumtext, -- the first address line of for your library or branch
344 `branchaddress2` mediumtext, -- the second address line of for your library or branch
345 `branchaddress3` mediumtext, -- the third address line of for your library or branch
346 `branchzip` varchar(25) default NULL, -- the zip or postal code for your library or branch
347 `branchcity` mediumtext, -- the city or province for your library or branch
348 `branchstate` mediumtext, -- the state for your library or branch
349 `branchcountry` text, -- the county for your library or branch
350 `branchphone` mediumtext, -- the primary phone for your library or branch
351 `branchfax` mediumtext, -- the fax number for your library or branch
352 `branchemail` mediumtext, -- the primary email address for your library or branch
353 `branchurl` mediumtext, -- the URL for your library or branch's website
354 `issuing` tinyint(4) default NULL, --unused in Koha
355 `branchip` varchar(15) default NULL, -- the IP address for your library or branch
356 `branchprinter` varchar(100) default NULL, -- unused in Koha
357 `branchnotes` mediumtext, -- notes related to your library or branch
358 UNIQUE KEY `branchcode` (`branchcode`)
359 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
362 -- Table structure for table `branchrelations`
365 DROP TABLE IF EXISTS `branchrelations`;
366 CREATE TABLE `branchrelations` (
367 `branchcode` varchar(10) NOT NULL default '',
368 `categorycode` varchar(10) NOT NULL default '',
369 PRIMARY KEY (`branchcode`,`categorycode`),
370 KEY `branchcode` (`branchcode`),
371 KEY `categorycode` (`categorycode`),
372 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
373 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
374 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
377 -- Table structure for table `branchtransfers`
380 DROP TABLE IF EXISTS `branchtransfers`;
381 CREATE TABLE `branchtransfers` (
382 `itemnumber` int(11) NOT NULL default 0,
383 `datesent` datetime default NULL,
384 `frombranch` varchar(10) NOT NULL default '',
385 `datearrived` datetime default NULL,
386 `tobranch` varchar(10) NOT NULL default '',
387 `comments` mediumtext,
388 KEY `frombranch` (`frombranch`),
389 KEY `tobranch` (`tobranch`),
390 KEY `itemnumber` (`itemnumber`),
391 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
392 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
393 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
394 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
398 -- Table structure for table `browser`
400 DROP TABLE IF EXISTS `browser`;
401 CREATE TABLE `browser` (
402 `level` int(11) NOT NULL,
403 `classification` varchar(20) NOT NULL,
404 `description` varchar(255) NOT NULL,
405 `number` bigint(20) NOT NULL,
406 `endnode` tinyint(4) NOT NULL
407 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
410 -- Table structure for table `categories`
413 DROP TABLE IF EXISTS `categories`;
414 CREATE TABLE `categories` (
415 `categorycode` varchar(10) NOT NULL default '',
416 `description` mediumtext,
417 `enrolmentperiod` smallint(6) default NULL,
418 `enrolmentperioddate` DATE NULL DEFAULT NULL,
419 `upperagelimit` smallint(6) default NULL,
420 `dateofbirthrequired` tinyint(1) default NULL,
421 `finetype` varchar(30) default NULL,
422 `bulk` tinyint(1) default NULL,
423 `enrolmentfee` decimal(28,6) default NULL,
424 `overduenoticerequired` tinyint(1) default NULL,
425 `issuelimit` smallint(6) default NULL,
426 `reservefee` decimal(28,6) default NULL,
427 `hidelostitems` tinyint(1) NOT NULL default '0',
428 `category_type` varchar(1) NOT NULL default 'A',
429 PRIMARY KEY (`categorycode`),
430 UNIQUE KEY `categorycode` (`categorycode`)
431 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
434 -- Table: collections
436 DROP TABLE IF EXISTS collections;
437 CREATE TABLE collections (
438 colId integer(11) NOT NULL auto_increment,
439 colTitle varchar(100) NOT NULL DEFAULT '',
440 colDesc text NOT NULL,
441 colBranchcode varchar(4) DEFAULT NULL comment 'branchcode for branch where item should be held.',
443 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
446 -- Table: collections_tracking
448 DROP TABLE IF EXISTS collections_tracking;
449 CREATE TABLE collections_tracking (
450 ctId integer(11) NOT NULL auto_increment,
451 colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId',
452 itemnumber integer(11) NOT NULL DEFAULT 0 comment 'items.itemnumber',
454 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
457 -- Table structure for table `borrower_branch_circ_rules`
460 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
461 CREATE TABLE `branch_borrower_circ_rules` (
462 `branchcode` VARCHAR(10) NOT NULL,
463 `categorycode` VARCHAR(10) NOT NULL,
464 `maxissueqty` int(4) default NULL,
465 PRIMARY KEY (`categorycode`, `branchcode`),
466 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
467 ON DELETE CASCADE ON UPDATE CASCADE,
468 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
469 ON DELETE CASCADE ON UPDATE CASCADE
470 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
473 -- Table structure for table `default_borrower_circ_rules`
476 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
477 CREATE TABLE `default_borrower_circ_rules` (
478 `categorycode` VARCHAR(10) NOT NULL,
479 `maxissueqty` int(4) default NULL,
480 PRIMARY KEY (`categorycode`),
481 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
482 ON DELETE CASCADE ON UPDATE CASCADE
483 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
486 -- Table structure for table `default_branch_circ_rules`
489 DROP TABLE IF EXISTS `default_branch_circ_rules`;
490 CREATE TABLE `default_branch_circ_rules` (
491 `branchcode` VARCHAR(10) NOT NULL,
492 `maxissueqty` int(4) default NULL,
493 `holdallowed` tinyint(1) default NULL,
494 PRIMARY KEY (`branchcode`),
495 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
496 ON DELETE CASCADE ON UPDATE CASCADE
497 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
500 -- Table structure for table `default_branch_item_rules`
502 DROP TABLE IF EXISTS `default_branch_item_rules`;
503 CREATE TABLE `default_branch_item_rules` (
504 `itemtype` varchar(10) NOT NULL,
505 `holdallowed` tinyint(1) default NULL,
506 PRIMARY KEY (`itemtype`),
507 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
508 ON DELETE CASCADE ON UPDATE CASCADE
509 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
512 -- Table structure for table `default_circ_rules`
515 DROP TABLE IF EXISTS `default_circ_rules`;
516 CREATE TABLE `default_circ_rules` (
517 `singleton` enum('singleton') NOT NULL default 'singleton',
518 `maxissueqty` int(4) default NULL,
519 `holdallowed` int(1) default NULL,
520 PRIMARY KEY (`singleton`)
521 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
524 -- Table structure for table `cities`
527 DROP TABLE IF EXISTS `cities`;
528 CREATE TABLE `cities` (
529 `cityid` int(11) NOT NULL auto_increment,
530 `city_name` varchar(100) NOT NULL default '',
531 `city_state` VARCHAR( 100 ) NULL DEFAULT NULL,
532 `city_country` VARCHAR( 100 ) NULL DEFAULT NULL,
533 `city_zipcode` varchar(20) default NULL,
534 PRIMARY KEY (`cityid`)
535 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
538 -- Table structure for table `class_sort_rules`
541 DROP TABLE IF EXISTS `class_sort_rules`;
542 CREATE TABLE `class_sort_rules` (
543 `class_sort_rule` varchar(10) NOT NULL default '',
544 `description` mediumtext,
545 `sort_routine` varchar(30) NOT NULL default '',
546 PRIMARY KEY (`class_sort_rule`),
547 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
548 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
551 -- Table structure for table `class_sources`
554 DROP TABLE IF EXISTS `class_sources`;
555 CREATE TABLE `class_sources` (
556 `cn_source` varchar(10) NOT NULL default '',
557 `description` mediumtext,
558 `used` tinyint(4) NOT NULL default 0,
559 `class_sort_rule` varchar(10) NOT NULL default '',
560 PRIMARY KEY (`cn_source`),
561 UNIQUE KEY `cn_source_idx` (`cn_source`),
562 KEY `used_idx` (`used`),
563 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
564 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
567 -- Table structure for table `currency`
570 DROP TABLE IF EXISTS `currency`;
571 CREATE TABLE `currency` (
572 `currency` varchar(10) NOT NULL default '',
573 `symbol` varchar(5) default NULL,
574 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
575 `rate` float(15,5) default NULL,
576 `active` tinyint(1) default NULL,
577 PRIMARY KEY (`currency`)
578 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
581 -- Table structure for table `deletedbiblio`
584 DROP TABLE IF EXISTS `deletedbiblio`;
585 CREATE TABLE `deletedbiblio` ( -- stores information about bibliographic records that have been deleted
586 `biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
587 `frameworkcode` varchar(4) NOT NULL default '', -- foriegn key from the biblio_framework table to identify which framework was used in cataloging this record
588 `author` mediumtext, -- statement of responsibility from MARC record (100 in MARC21)
589 `title` mediumtext, -- title (without the subtitle) from the MARC record (245 in MARC21)
590 `unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240 in MARC21)
591 `notes` mediumtext, -- values from the general notes field in the MARC record (500 in MARC21) split by bar (|)
592 `serial` tinyint(1) default NULL, -- foreign key, linking to the subscriptionid in the serial table
593 `seriestitle` mediumtext,
594 `copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
595 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
596 `datecreated` DATE NOT NULL, -- the date this record was added to Koha
597 `abstract` mediumtext, -- summary from the MARC record (520 in MARC21)
598 PRIMARY KEY (`biblionumber`),
599 KEY `blbnoidx` (`biblionumber`)
600 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
603 -- Table structure for table `deletedbiblioitems`
606 DROP TABLE IF EXISTS `deletedbiblioitems`;
607 CREATE TABLE `deletedbiblioitems` (
608 `biblioitemnumber` int(11) NOT NULL default 0,
609 `biblionumber` int(11) NOT NULL default 0,
612 `itemtype` varchar(10) default NULL,
613 `isbn` varchar(30) default NULL,
614 `issn` varchar(9) default NULL,
615 `publicationyear` text,
616 `publishercode` varchar(255) default NULL,
617 `volumedate` date default NULL,
619 `collectiontitle` mediumtext default NULL,
620 `collectionissn` text default NULL,
621 `collectionvolume` mediumtext default NULL,
622 `editionstatement` text default NULL,
623 `editionresponsibility` text default NULL,
624 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
625 `illus` varchar(255) default NULL,
626 `pages` varchar(255) default NULL,
628 `size` varchar(255) default NULL,
629 `place` varchar(255) default NULL,
630 `lccn` varchar(25) default NULL,
632 `url` varchar(255) default NULL,
633 `cn_source` varchar(10) default NULL,
634 `cn_class` varchar(30) default NULL,
635 `cn_item` varchar(10) default NULL,
636 `cn_suffix` varchar(10) default NULL,
637 `cn_sort` varchar(30) default NULL,
638 `totalissues` int(10),
639 `marcxml` longtext NOT NULL,
640 PRIMARY KEY (`biblioitemnumber`),
641 KEY `bibinoidx` (`biblioitemnumber`),
642 KEY `bibnoidx` (`biblionumber`),
644 KEY `publishercode` (`publishercode`)
645 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
648 -- Table structure for table `deletedborrowers`
651 DROP TABLE IF EXISTS `deletedborrowers`;
652 CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrowers you have deleted
653 `borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers
654 `cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers
655 `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname)
656 `firstname` text, -- patron/borrower's first name
657 `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs.
658 `othernames` mediumtext, -- any other names associated with the patron/borrower
659 `initials` text, -- initials for your patron/borrower
660 `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
661 `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
662 `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address
663 `address2` text, -- the second address line for your patron/borrower's primary address
664 `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address
665 `state` text default NULL, -- the state or province for your patron/borrower's primary address
666 `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
667 `country` text, -- the country for your patron/borrower's primary address
668 `email` mediumtext, -- the primary email address for your patron/borrower's primary address
669 `phone` text, -- the primary phone number for your patron/borrower's primary address
670 `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
671 `fax` mediumtext, -- the fax number for your patron/borrower's primary address
672 `emailpro` text, -- the secondary email addres for your patron/borrower's primary address
673 `phonepro` text, -- the secondary phone number for your patron/borrower's primary address
674 `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
675 `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
676 `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
677 `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address
678 `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address
679 `B_state` text default NULL, -- the state for your patron/borrower's alternate address
680 `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
681 `B_country` text, -- the country for your patron/borrower's alternate address
682 `B_email` text, -- the patron/borrower's alternate email address
683 `B_phone` mediumtext, -- the patron/borrower's alternate phone number
684 `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD)
685 `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch
686 `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category
687 `dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD)
688 `dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD)
689 `gonenoaddress` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having an unconfirmed address
690 `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
691 `debarred` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as being restricted
692 `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name
693 `contactfirstname` text, -- used for children to include first name of guarentor
694 `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor
695 `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations
696 `borrowernotes` mediumtext, -- a note on the patron/borroewr's account that is only visible in the staff client
697 `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor
698 `ethnicity` varchar(50) default NULL, -- unused in Koha
699 `ethnotes` varchar(255) default NULL, -- unused in Koha
700 `sex` varchar(1) default NULL, -- patron/borrower's gender
701 `password` varchar(30) default NULL, -- patron/borrower's encrypted password
702 `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
703 `userid` varchar(30) default NULL, -- patron/borrower's opac and/or staff client log in
704 `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
705 `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
706 `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
707 `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
708 `altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower
709 `altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower
710 `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
711 `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
712 `altcontactaddress3` varchar(255) default NULL, -- the third address line for the alternate contact for the patron/borrower
713 `altcontactstate` text default NULL, -- the city and state for the alternate contact for the patron/borrower
714 `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
715 `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower
716 `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
717 `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
718 `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history KEY `borrowernumber` (`borrowernumber`),
719 KEY `cardnumber` (`cardnumber`)
720 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
723 -- Table structure for table `deleteditems`
726 DROP TABLE IF EXISTS `deleteditems`;
727 CREATE TABLE `deleteditems` (
728 `itemnumber` int(11) NOT NULL default 0,
729 `biblionumber` int(11) NOT NULL default 0,
730 `biblioitemnumber` int(11) NOT NULL default 0,
731 `barcode` varchar(20) default NULL,
732 `dateaccessioned` date default NULL,
733 `booksellerid` mediumtext default NULL,
734 `homebranch` varchar(10) default NULL,
735 `price` decimal(8,2) default NULL,
736 `replacementprice` decimal(8,2) default NULL,
737 `replacementpricedate` date default NULL,
738 `datelastborrowed` date default NULL,
739 `datelastseen` date default NULL,
740 `stack` tinyint(1) default NULL,
741 `notforloan` tinyint(1) NOT NULL default 0,
742 `damaged` tinyint(1) NOT NULL default 0,
743 `itemlost` tinyint(1) NOT NULL default 0,
744 `wthdrawn` tinyint(1) NOT NULL default 0,
745 `itemcallnumber` varchar(255) default NULL,
746 `issues` smallint(6) default NULL,
747 `renewals` smallint(6) default NULL,
748 `reserves` smallint(6) default NULL,
749 `restricted` tinyint(1) default NULL,
750 `itemnotes` mediumtext,
751 `holdingbranch` varchar(10) default NULL,
752 `paidfor` mediumtext,
753 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
754 `location` varchar(80) default NULL,
755 `permanent_location` varchar(80) default NULL,
756 `onloan` date default NULL,
757 `cn_source` varchar(10) default NULL,
758 `cn_sort` varchar(30) default NULL,
759 `ccode` varchar(10) default NULL,
760 `materials` varchar(10) default NULL,
761 `uri` varchar(255) default NULL,
762 `itype` varchar(10) default NULL,
763 `more_subfields_xml` longtext default NULL,
764 `enumchron` text default NULL,
765 `copynumber` varchar(32) default NULL,
766 `stocknumber` varchar(32) default NULL,
768 PRIMARY KEY (`itemnumber`),
769 KEY `delitembarcodeidx` (`barcode`),
770 KEY `delitemstocknumberidx` (`stocknumber`),
771 KEY `delitembinoidx` (`biblioitemnumber`),
772 KEY `delitembibnoidx` (`biblionumber`),
773 KEY `delhomebranch` (`homebranch`),
774 KEY `delholdingbranch` (`holdingbranch`)
775 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
778 -- Table structure for table `ethnicity`
781 DROP TABLE IF EXISTS `ethnicity`;
782 CREATE TABLE `ethnicity` (
783 `code` varchar(10) NOT NULL default '',
784 `name` varchar(255) default NULL,
786 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
789 -- Table structure for table `export_format`
792 DROP TABLE IF EXISTS `export_format`;
793 CREATE TABLE `export_format` (
794 `export_format_id` int(11) NOT NULL auto_increment,
795 `profile` varchar(255) NOT NULL,
796 `description` mediumtext NOT NULL,
797 `marcfields` mediumtext NOT NULL,
798 `csv_separator` varchar(2) NOT NULL,
799 `field_separator` varchar(2) NOT NULL,
800 `subfield_separator` varchar(2) NOT NULL,
801 `encoding` varchar(255) NOT NULL,
802 PRIMARY KEY (`export_format_id`)
803 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
807 -- Table structure for table `hold_fill_targets`
810 DROP TABLE IF EXISTS `hold_fill_targets`;
811 CREATE TABLE hold_fill_targets (
812 `borrowernumber` int(11) NOT NULL,
813 `biblionumber` int(11) NOT NULL,
814 `itemnumber` int(11) NOT NULL,
815 `source_branchcode` varchar(10) default NULL,
816 `item_level_request` tinyint(4) NOT NULL default 0,
817 PRIMARY KEY `itemnumber` (`itemnumber`),
818 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
819 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
820 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
821 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
822 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
823 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
824 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
825 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
826 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
827 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
830 -- Table structure for table `import_batches`
833 DROP TABLE IF EXISTS `import_batches`;
834 CREATE TABLE `import_batches` (
835 `import_batch_id` int(11) NOT NULL auto_increment,
836 `matcher_id` int(11) default NULL,
837 `template_id` int(11) default NULL,
838 `branchcode` varchar(10) default NULL,
839 `num_biblios` int(11) NOT NULL default 0,
840 `num_items` int(11) NOT NULL default 0,
841 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
842 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
843 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
844 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
845 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
846 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
847 `file_name` varchar(100),
848 `comments` mediumtext,
849 PRIMARY KEY (`import_batch_id`),
850 KEY `branchcode` (`branchcode`)
851 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
854 -- Table structure for table `import_records`
857 DROP TABLE IF EXISTS `import_records`;
858 CREATE TABLE `import_records` (
859 `import_record_id` int(11) NOT NULL auto_increment,
860 `import_batch_id` int(11) NOT NULL,
861 `branchcode` varchar(10) default NULL,
862 `record_sequence` int(11) NOT NULL default 0,
863 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
864 `import_date` DATE default NULL,
865 `marc` longblob NOT NULL,
866 `marcxml` longtext NOT NULL,
867 `marcxml_old` longtext NOT NULL,
868 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
869 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
870 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
871 `import_error` mediumtext,
872 `encoding` varchar(40) NOT NULL default '',
873 `z3950random` varchar(40) default NULL,
874 PRIMARY KEY (`import_record_id`),
875 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
876 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
877 KEY `branchcode` (`branchcode`),
878 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
879 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
882 -- Table structure for `import_record_matches`
884 DROP TABLE IF EXISTS `import_record_matches`;
885 CREATE TABLE `import_record_matches` (
886 `import_record_id` int(11) NOT NULL,
887 `candidate_match_id` int(11) NOT NULL,
888 `score` int(11) NOT NULL default 0,
889 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
890 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
891 KEY `record_score` (`import_record_id`, `score`)
892 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
895 -- Table structure for table `import_biblios`
898 DROP TABLE IF EXISTS `import_biblios`;
899 CREATE TABLE `import_biblios` (
900 `import_record_id` int(11) NOT NULL,
901 `matched_biblionumber` int(11) default NULL,
902 `control_number` varchar(25) default NULL,
903 `original_source` varchar(25) default NULL,
904 `title` varchar(128) default NULL,
905 `author` varchar(80) default NULL,
906 `isbn` varchar(30) default NULL,
907 `issn` varchar(9) default NULL,
908 `has_items` tinyint(1) NOT NULL default 0,
909 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
910 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
911 KEY `matched_biblionumber` (`matched_biblionumber`),
912 KEY `title` (`title`),
914 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
917 -- Table structure for table `import_items`
920 DROP TABLE IF EXISTS `import_items`;
921 CREATE TABLE `import_items` (
922 `import_items_id` int(11) NOT NULL auto_increment,
923 `import_record_id` int(11) NOT NULL,
924 `itemnumber` int(11) default NULL,
925 `branchcode` varchar(10) default NULL,
926 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
927 `marcxml` longtext NOT NULL,
928 `import_error` mediumtext,
929 PRIMARY KEY (`import_items_id`),
930 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
931 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
932 KEY `itemnumber` (`itemnumber`),
933 KEY `branchcode` (`branchcode`)
934 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
937 -- Table structure for table `issues`
940 DROP TABLE IF EXISTS `issues`;
941 CREATE TABLE `issues` (
942 `borrowernumber` int(11),
943 `itemnumber` int(11),
944 `date_due` date default NULL,
945 `branchcode` varchar(10) default NULL,
946 `issuingbranch` varchar(18) default NULL,
947 `returndate` date default NULL,
948 `lastreneweddate` date default NULL,
949 `return` varchar(4) default NULL,
950 `renewals` tinyint(4) default NULL,
951 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
952 `issuedate` date default NULL,
953 PRIMARY KEY (`itemnumber`),
954 KEY `issuesborridx` (`borrowernumber`),
955 KEY `bordate` (`borrowernumber`,`timestamp`),
956 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
957 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
958 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
961 -- Table structure for table `issuingrules`
964 DROP TABLE IF EXISTS `issuingrules`;
965 CREATE TABLE `issuingrules` (
966 `categorycode` varchar(10) NOT NULL default '',
967 `itemtype` varchar(10) NOT NULL default '',
968 `restrictedtype` tinyint(1) default NULL,
969 `rentaldiscount` decimal(28,6) default NULL,
970 `reservecharge` decimal(28,6) default NULL,
971 `fine` decimal(28,6) default NULL,
972 `finedays` int(11) default NULL,
973 `firstremind` int(11) default NULL,
974 `chargeperiod` int(11) default NULL,
975 `accountsent` int(11) default NULL,
976 `chargename` varchar(100) default NULL,
977 `maxissueqty` int(4) default NULL,
978 `issuelength` int(4) default NULL,
979 `hardduedate` date default NULL,
980 `hardduedatecompare` tinyint NOT NULL default "0",
981 `renewalsallowed` smallint(6) NOT NULL default "0",
982 `reservesallowed` smallint(6) NOT NULL default "0",
983 `branchcode` varchar(10) NOT NULL default '',
984 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
985 KEY `categorycode` (`categorycode`),
986 KEY `itemtype` (`itemtype`)
987 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
990 -- Table structure for table `items`
993 DROP TABLE IF EXISTS `items`;
994 CREATE TABLE `items` (
995 `itemnumber` int(11) NOT NULL auto_increment,
996 `biblionumber` int(11) NOT NULL default 0,
997 `biblioitemnumber` int(11) NOT NULL default 0,
998 `barcode` varchar(20) default NULL,
999 `dateaccessioned` date default NULL,
1000 `booksellerid` mediumtext default NULL,
1001 `homebranch` varchar(10) default NULL,
1002 `price` decimal(8,2) default NULL,
1003 `replacementprice` decimal(8,2) default NULL,
1004 `replacementpricedate` date default NULL,
1005 `datelastborrowed` date default NULL,
1006 `datelastseen` date default NULL,
1007 `stack` tinyint(1) default NULL,
1008 `notforloan` tinyint(1) NOT NULL default 0,
1009 `damaged` tinyint(1) NOT NULL default 0,
1010 `itemlost` tinyint(1) NOT NULL default 0,
1011 `wthdrawn` tinyint(1) NOT NULL default 0,
1012 `itemcallnumber` varchar(255) default NULL,
1013 `issues` smallint(6) default NULL,
1014 `renewals` smallint(6) default NULL,
1015 `reserves` smallint(6) default NULL,
1016 `restricted` tinyint(1) default NULL,
1017 `itemnotes` mediumtext,
1018 `holdingbranch` varchar(10) default NULL,
1019 `paidfor` mediumtext,
1020 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1021 `location` varchar(80) default NULL,
1022 `permanent_location` varchar(80) default NULL,
1023 `onloan` date default NULL,
1024 `cn_source` varchar(10) default NULL,
1025 `cn_sort` varchar(30) default NULL,
1026 `ccode` varchar(10) default NULL,
1027 `materials` varchar(10) default NULL,
1028 `uri` varchar(255) default NULL,
1029 `itype` varchar(10) default NULL,
1030 `more_subfields_xml` longtext default NULL,
1031 `enumchron` text default NULL,
1032 `copynumber` varchar(32) default NULL,
1033 `stocknumber` varchar(32) default NULL,
1034 PRIMARY KEY (`itemnumber`),
1035 UNIQUE KEY `itembarcodeidx` (`barcode`),
1036 KEY `itemstocknumberidx` (`stocknumber`),
1037 KEY `itembinoidx` (`biblioitemnumber`),
1038 KEY `itembibnoidx` (`biblionumber`),
1039 KEY `homebranch` (`homebranch`),
1040 KEY `holdingbranch` (`holdingbranch`),
1041 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1042 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1043 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1044 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1047 -- Table structure for table `itemtypes`
1050 DROP TABLE IF EXISTS `itemtypes`;
1051 CREATE TABLE `itemtypes` (
1052 `itemtype` varchar(10) NOT NULL default '',
1053 `description` mediumtext,
1054 `rentalcharge` double(16,4) default NULL,
1055 `notforloan` smallint(6) default NULL,
1056 `imageurl` varchar(200) default NULL,
1058 PRIMARY KEY (`itemtype`),
1059 UNIQUE KEY `itemtype` (`itemtype`)
1060 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1063 -- Table structure for table `creator_batches`
1066 DROP TABLE IF EXISTS `creator_batches`;
1067 SET @saved_cs_client = @@character_set_client;
1068 SET character_set_client = utf8;
1069 CREATE TABLE `creator_batches` (
1070 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1071 `batch_id` int(10) NOT NULL DEFAULT '1',
1072 `item_number` int(11) DEFAULT NULL,
1073 `borrower_number` int(11) DEFAULT NULL,
1074 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1075 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1076 `creator` char(15) NOT NULL DEFAULT 'Labels',
1077 PRIMARY KEY (`label_id`),
1078 KEY `branch_fk_constraint` (`branch_code`),
1079 KEY `item_fk_constraint` (`item_number`),
1080 KEY `borrower_fk_constraint` (`borrower_number`),
1081 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1082 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1083 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1084 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1087 -- Table structure for table `creator_images`
1090 DROP TABLE IF EXISTS `creator_images`;
1091 SET @saved_cs_client = @@character_set_client;
1092 SET character_set_client = utf8;
1093 CREATE TABLE `creator_images` (
1094 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1095 `imagefile` mediumblob,
1096 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1097 PRIMARY KEY (`image_id`),
1098 UNIQUE KEY `image_name_index` (`image_name`)
1099 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1102 -- Table structure for table `creator_layouts`
1105 DROP TABLE IF EXISTS `creator_layouts`;
1106 SET @saved_cs_client = @@character_set_client;
1107 SET character_set_client = utf8;
1108 CREATE TABLE `creator_layouts` (
1109 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1110 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1111 `start_label` int(2) NOT NULL DEFAULT '1',
1112 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1113 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1114 `guidebox` int(1) DEFAULT '0',
1115 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1116 `font_size` int(4) NOT NULL DEFAULT '10',
1117 `units` char(20) NOT NULL DEFAULT 'POINT',
1118 `callnum_split` int(1) DEFAULT '0',
1119 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1120 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1121 `layout_xml` text NOT NULL,
1122 `creator` char(15) NOT NULL DEFAULT 'Labels',
1123 PRIMARY KEY (`layout_id`)
1124 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1127 -- Table structure for table `creator_templates`
1130 DROP TABLE IF EXISTS `creator_templates`;
1131 SET @saved_cs_client = @@character_set_client;
1132 SET character_set_client = utf8;
1133 CREATE TABLE `creator_templates` (
1134 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1135 `profile_id` int(4) DEFAULT NULL,
1136 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1137 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1138 `page_width` float NOT NULL DEFAULT '0',
1139 `page_height` float NOT NULL DEFAULT '0',
1140 `label_width` float NOT NULL DEFAULT '0',
1141 `label_height` float NOT NULL DEFAULT '0',
1142 `top_text_margin` float NOT NULL DEFAULT '0',
1143 `left_text_margin` float NOT NULL DEFAULT '0',
1144 `top_margin` float NOT NULL DEFAULT '0',
1145 `left_margin` float NOT NULL DEFAULT '0',
1146 `cols` int(2) NOT NULL DEFAULT '0',
1147 `rows` int(2) NOT NULL DEFAULT '0',
1148 `col_gap` float NOT NULL DEFAULT '0',
1149 `row_gap` float NOT NULL DEFAULT '0',
1150 `units` char(20) NOT NULL DEFAULT 'POINT',
1151 `creator` char(15) NOT NULL DEFAULT 'Labels',
1152 PRIMARY KEY (`template_id`),
1153 KEY `template_profile_fk_constraint` (`profile_id`)
1154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1157 -- Table structure for table `letter`
1160 DROP TABLE IF EXISTS `letter`;
1161 CREATE TABLE `letter` (
1162 `module` varchar(20) NOT NULL default '',
1163 `code` varchar(20) NOT NULL default '',
1164 `name` varchar(100) NOT NULL default '',
1165 `title` varchar(200) NOT NULL default '',
1167 PRIMARY KEY (`module`,`code`)
1168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1171 -- Table structure for table `marc_subfield_structure`
1174 DROP TABLE IF EXISTS `marc_subfield_structure`;
1175 CREATE TABLE `marc_subfield_structure` (
1176 `tagfield` varchar(3) NOT NULL default '',
1177 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1178 `liblibrarian` varchar(255) NOT NULL default '',
1179 `libopac` varchar(255) NOT NULL default '',
1180 `repeatable` tinyint(4) NOT NULL default 0,
1181 `mandatory` tinyint(4) NOT NULL default 0,
1182 `kohafield` varchar(40) default NULL,
1183 `tab` tinyint(1) default NULL,
1184 `authorised_value` varchar(20) default NULL,
1185 `authtypecode` varchar(20) default NULL,
1186 `value_builder` varchar(80) default NULL,
1187 `isurl` tinyint(1) default NULL,
1188 `hidden` tinyint(1) default NULL,
1189 `frameworkcode` varchar(4) NOT NULL default '',
1190 `seealso` varchar(1100) default NULL,
1191 `link` varchar(80) default NULL,
1192 `defaultvalue` text default NULL,
1193 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1194 KEY `kohafield_2` (`kohafield`),
1195 KEY `tab` (`frameworkcode`,`tab`),
1196 KEY `kohafield` (`frameworkcode`,`kohafield`)
1197 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1200 -- Table structure for table `marc_tag_structure`
1203 DROP TABLE IF EXISTS `marc_tag_structure`;
1204 CREATE TABLE `marc_tag_structure` (
1205 `tagfield` varchar(3) NOT NULL default '',
1206 `liblibrarian` varchar(255) NOT NULL default '',
1207 `libopac` varchar(255) NOT NULL default '',
1208 `repeatable` tinyint(4) NOT NULL default 0,
1209 `mandatory` tinyint(4) NOT NULL default 0,
1210 `authorised_value` varchar(10) default NULL,
1211 `frameworkcode` varchar(4) NOT NULL default '',
1212 PRIMARY KEY (`frameworkcode`,`tagfield`)
1213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1216 -- Table structure for table `marc_matchers`
1219 DROP TABLE IF EXISTS `marc_matchers`;
1220 CREATE TABLE `marc_matchers` (
1221 `matcher_id` int(11) NOT NULL auto_increment,
1222 `code` varchar(10) NOT NULL default '',
1223 `description` varchar(255) NOT NULL default '',
1224 `record_type` varchar(10) NOT NULL default 'biblio',
1225 `threshold` int(11) NOT NULL default 0,
1226 PRIMARY KEY (`matcher_id`),
1227 KEY `code` (`code`),
1228 KEY `record_type` (`record_type`)
1229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1232 -- Table structure for table `matchpoints`
1234 DROP TABLE IF EXISTS `matchpoints`;
1235 CREATE TABLE `matchpoints` (
1236 `matcher_id` int(11) NOT NULL,
1237 `matchpoint_id` int(11) NOT NULL auto_increment,
1238 `search_index` varchar(30) NOT NULL default '',
1239 `score` int(11) NOT NULL default 0,
1240 PRIMARY KEY (`matchpoint_id`),
1241 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1242 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1243 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1247 -- Table structure for table `matchpoint_components`
1249 DROP TABLE IF EXISTS `matchpoint_components`;
1250 CREATE TABLE `matchpoint_components` (
1251 `matchpoint_id` int(11) NOT NULL,
1252 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1253 sequence int(11) NOT NULL default 0,
1254 tag varchar(3) NOT NULL default '',
1255 subfields varchar(40) NOT NULL default '',
1256 offset int(4) NOT NULL default 0,
1257 length int(4) NOT NULL default 0,
1258 PRIMARY KEY (`matchpoint_component_id`),
1259 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1260 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1261 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1262 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1265 -- Table structure for table `matcher_component_norms`
1267 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1268 CREATE TABLE `matchpoint_component_norms` (
1269 `matchpoint_component_id` int(11) NOT NULL,
1270 `sequence` int(11) NOT NULL default 0,
1271 `norm_routine` varchar(50) NOT NULL default '',
1272 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1273 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1274 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1275 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1278 -- Table structure for table `matcher_matchpoints`
1280 DROP TABLE IF EXISTS `matcher_matchpoints`;
1281 CREATE TABLE `matcher_matchpoints` (
1282 `matcher_id` int(11) NOT NULL,
1283 `matchpoint_id` int(11) NOT NULL,
1284 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1285 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1286 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1287 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1288 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1291 -- Table structure for table `matchchecks`
1293 DROP TABLE IF EXISTS `matchchecks`;
1294 CREATE TABLE `matchchecks` (
1295 `matcher_id` int(11) NOT NULL,
1296 `matchcheck_id` int(11) NOT NULL auto_increment,
1297 `source_matchpoint_id` int(11) NOT NULL,
1298 `target_matchpoint_id` int(11) NOT NULL,
1299 PRIMARY KEY (`matchcheck_id`),
1300 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1301 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1302 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1303 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1304 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1305 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1306 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1309 -- Table structure for table `notifys`
1312 DROP TABLE IF EXISTS `notifys`;
1313 CREATE TABLE `notifys` (
1314 `notify_id` int(11) NOT NULL default 0,
1315 `borrowernumber` int(11) NOT NULL default 0,
1316 `itemnumber` int(11) NOT NULL default 0,
1317 `notify_date` date default NULL,
1318 `notify_send_date` date default NULL,
1319 `notify_level` int(1) NOT NULL default 0,
1320 `method` varchar(20) NOT NULL default ''
1321 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1324 -- Table structure for table `nozebra`
1327 DROP TABLE IF EXISTS `nozebra`;
1328 CREATE TABLE `nozebra` (
1329 `server` varchar(20) NOT NULL,
1330 `indexname` varchar(40) NOT NULL,
1331 `value` varchar(250) NOT NULL,
1332 `biblionumbers` longtext NOT NULL,
1333 KEY `indexname` (`server`,`indexname`),
1334 KEY `value` (`server`,`value`))
1335 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1338 -- Table structure for table `old_issues`
1341 DROP TABLE IF EXISTS `old_issues`;
1342 CREATE TABLE `old_issues` (
1343 `borrowernumber` int(11) default NULL,
1344 `itemnumber` int(11) default NULL,
1345 `date_due` date default NULL,
1346 `branchcode` varchar(10) default NULL,
1347 `issuingbranch` varchar(18) default NULL,
1348 `returndate` date default NULL,
1349 `lastreneweddate` date default NULL,
1350 `return` varchar(4) default NULL,
1351 `renewals` tinyint(4) default NULL,
1352 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1353 `issuedate` date default NULL,
1354 KEY `old_issuesborridx` (`borrowernumber`),
1355 KEY `old_issuesitemidx` (`itemnumber`),
1356 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1357 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1358 ON DELETE SET NULL ON UPDATE SET NULL,
1359 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1360 ON DELETE SET NULL ON UPDATE SET NULL
1361 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1364 -- Table structure for table `old_reserves`
1366 DROP TABLE IF EXISTS `old_reserves`;
1367 CREATE TABLE `old_reserves` (
1368 `borrowernumber` int(11) default NULL,
1369 `reservedate` date default NULL,
1370 `biblionumber` int(11) default NULL,
1371 `constrainttype` varchar(1) default NULL,
1372 `branchcode` varchar(10) default NULL,
1373 `notificationdate` date default NULL,
1374 `reminderdate` date default NULL,
1375 `cancellationdate` date default NULL,
1376 `reservenotes` mediumtext,
1377 `priority` smallint(6) default NULL,
1378 `found` varchar(1) default NULL,
1379 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1380 `itemnumber` int(11) default NULL,
1381 `waitingdate` date default NULL,
1382 `expirationdate` DATE DEFAULT NULL,
1383 `lowestPriority` tinyint(1) NOT NULL,
1384 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1385 KEY `old_reserves_biblionumber` (`biblionumber`),
1386 KEY `old_reserves_itemnumber` (`itemnumber`),
1387 KEY `old_reserves_branchcode` (`branchcode`),
1388 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1389 ON DELETE SET NULL ON UPDATE SET NULL,
1390 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1391 ON DELETE SET NULL ON UPDATE SET NULL,
1392 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1393 ON DELETE SET NULL ON UPDATE SET NULL
1394 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1397 -- Table structure for table `opac_news`
1400 DROP TABLE IF EXISTS `opac_news`;
1401 CREATE TABLE `opac_news` (
1402 `idnew` int(10) unsigned NOT NULL auto_increment,
1403 `title` varchar(250) NOT NULL default '',
1404 `new` text NOT NULL,
1405 `lang` varchar(25) NOT NULL default '',
1406 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1407 `expirationdate` date default NULL,
1408 `number` int(11) default NULL,
1409 PRIMARY KEY (`idnew`)
1410 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1413 -- Table structure for table `overduerules`
1416 DROP TABLE IF EXISTS `overduerules`;
1417 CREATE TABLE `overduerules` (
1418 `branchcode` varchar(10) NOT NULL default '',
1419 `categorycode` varchar(10) NOT NULL default '',
1420 `delay1` int(4) default NULL,
1421 `letter1` varchar(20) default NULL,
1422 `debarred1` varchar(1) default 0,
1423 `delay2` int(4) default NULL,
1424 `debarred2` varchar(1) default 0,
1425 `letter2` varchar(20) default NULL,
1426 `delay3` int(4) default NULL,
1427 `letter3` varchar(20) default NULL,
1428 `debarred3` int(1) default 0,
1429 PRIMARY KEY (`branchcode`,`categorycode`)
1430 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1433 -- Table structure for table `patroncards`
1436 DROP TABLE IF EXISTS `patroncards`;
1437 CREATE TABLE `patroncards` (
1438 `cardid` int(11) NOT NULL auto_increment,
1439 `batch_id` varchar(10) NOT NULL default '1',
1440 `borrowernumber` int(11) NOT NULL,
1441 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1442 PRIMARY KEY (`cardid`),
1443 KEY `patroncards_ibfk_1` (`borrowernumber`),
1444 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1445 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1448 -- Table structure for table `patronimage`
1451 DROP TABLE IF EXISTS `patronimage`;
1452 CREATE TABLE `patronimage` (
1453 `cardnumber` varchar(16) NOT NULL,
1454 `mimetype` varchar(15) NOT NULL,
1455 `imagefile` mediumblob NOT NULL,
1456 PRIMARY KEY (`cardnumber`),
1457 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1458 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1461 -- Table structure for table `printers`
1464 DROP TABLE IF EXISTS `printers`;
1465 CREATE TABLE `printers` (
1466 `printername` varchar(40) NOT NULL default '',
1467 `printqueue` varchar(20) default NULL,
1468 `printtype` varchar(20) default NULL,
1469 PRIMARY KEY (`printername`)
1470 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1473 -- Table structure for table `printers_profile`
1476 DROP TABLE IF EXISTS `printers_profile`;
1477 CREATE TABLE `printers_profile` (
1478 `profile_id` int(4) NOT NULL auto_increment,
1479 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1480 `template_id` int(4) NOT NULL default '0',
1481 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1482 `offset_horz` float NOT NULL default '0',
1483 `offset_vert` float NOT NULL default '0',
1484 `creep_horz` float NOT NULL default '0',
1485 `creep_vert` float NOT NULL default '0',
1486 `units` char(20) NOT NULL default 'POINT',
1487 `creator` char(15) NOT NULL DEFAULT 'Labels',
1488 PRIMARY KEY (`profile_id`),
1489 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
1490 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1493 -- Table structure for table `repeatable_holidays`
1496 DROP TABLE IF EXISTS `repeatable_holidays`;
1497 CREATE TABLE `repeatable_holidays` (
1498 `id` int(11) NOT NULL auto_increment,
1499 `branchcode` varchar(10) NOT NULL default '',
1500 `weekday` smallint(6) default NULL,
1501 `day` smallint(6) default NULL,
1502 `month` smallint(6) default NULL,
1503 `title` varchar(50) NOT NULL default '',
1504 `description` text NOT NULL,
1506 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1509 -- Table structure for table `reports_dictionary`
1512 DROP TABLE IF EXISTS `reports_dictionary`;
1513 CREATE TABLE reports_dictionary (
1514 `id` int(11) NOT NULL auto_increment,
1515 `name` varchar(255) default NULL,
1517 `date_created` datetime default NULL,
1518 `date_modified` datetime default NULL,
1520 `area` int(11) default NULL,
1522 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1525 -- Table structure for table `reserveconstraints`
1528 DROP TABLE IF EXISTS `reserveconstraints`;
1529 CREATE TABLE `reserveconstraints` (
1530 `borrowernumber` int(11) NOT NULL default 0,
1531 `reservedate` date default NULL,
1532 `biblionumber` int(11) NOT NULL default 0,
1533 `biblioitemnumber` int(11) default NULL,
1534 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1535 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1538 -- Table structure for table `reserves`
1541 DROP TABLE IF EXISTS `reserves`;
1542 CREATE TABLE `reserves` (
1543 `borrowernumber` int(11) NOT NULL default 0,
1544 `reservedate` date default NULL,
1545 `biblionumber` int(11) NOT NULL default 0,
1546 `constrainttype` varchar(1) default NULL,
1547 `branchcode` varchar(10) default NULL,
1548 `notificationdate` date default NULL,
1549 `reminderdate` date default NULL,
1550 `cancellationdate` date default NULL,
1551 `reservenotes` mediumtext,
1552 `priority` smallint(6) default NULL,
1553 `found` varchar(1) default NULL,
1554 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1555 `itemnumber` int(11) default NULL,
1556 `waitingdate` date default NULL,
1557 `expirationdate` DATE DEFAULT NULL,
1558 `lowestPriority` tinyint(1) NOT NULL,
1559 KEY priorityfoundidx (priority,found),
1560 KEY `borrowernumber` (`borrowernumber`),
1561 KEY `biblionumber` (`biblionumber`),
1562 KEY `itemnumber` (`itemnumber`),
1563 KEY `branchcode` (`branchcode`),
1564 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1565 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1566 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1567 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1568 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1571 -- Table structure for table `reviews`
1574 DROP TABLE IF EXISTS `reviews`;
1575 CREATE TABLE `reviews` (
1576 `reviewid` int(11) NOT NULL auto_increment,
1577 `borrowernumber` int(11) default NULL,
1578 `biblionumber` int(11) default NULL,
1580 `approved` tinyint(4) default NULL,
1581 `datereviewed` datetime default NULL,
1582 PRIMARY KEY (`reviewid`)
1583 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1586 -- Table structure for table `roadtype`
1589 DROP TABLE IF EXISTS `roadtype`;
1590 CREATE TABLE `roadtype` (
1591 `roadtypeid` int(11) NOT NULL auto_increment,
1592 `road_type` varchar(100) NOT NULL default '',
1593 PRIMARY KEY (`roadtypeid`)
1594 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1597 -- Table structure for table `saved_sql`
1600 DROP TABLE IF EXISTS `saved_sql`;
1601 CREATE TABLE saved_sql (
1602 `id` int(11) NOT NULL auto_increment,
1603 `borrowernumber` int(11) default NULL,
1604 `date_created` datetime default NULL,
1605 `last_modified` datetime default NULL,
1607 `last_run` datetime default NULL,
1608 `report_name` varchar(255) default NULL,
1609 `type` varchar(255) default NULL,
1612 KEY boridx (`borrowernumber`)
1613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1617 -- Table structure for `saved_reports`
1620 DROP TABLE IF EXISTS `saved_reports`;
1621 CREATE TABLE saved_reports (
1622 `id` int(11) NOT NULL auto_increment,
1623 `report_id` int(11) default NULL,
1625 `date_run` datetime default NULL,
1627 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1631 -- Table structure for table `search_history`
1634 DROP TABLE IF EXISTS `search_history`;
1635 CREATE TABLE IF NOT EXISTS `search_history` (
1636 `userid` int(11) NOT NULL,
1637 `sessionid` varchar(32) NOT NULL,
1638 `query_desc` varchar(255) NOT NULL,
1639 `query_cgi` varchar(255) NOT NULL,
1640 `total` int(11) NOT NULL,
1641 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1642 KEY `userid` (`userid`),
1643 KEY `sessionid` (`sessionid`)
1644 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1648 -- Table structure for table `serial`
1651 DROP TABLE IF EXISTS `serial`;
1652 CREATE TABLE `serial` (
1653 `serialid` int(11) NOT NULL auto_increment,
1654 `biblionumber` varchar(100) NOT NULL default '',
1655 `subscriptionid` varchar(100) NOT NULL default '',
1656 `serialseq` varchar(100) NOT NULL default '',
1657 `status` tinyint(4) NOT NULL default 0,
1658 `planneddate` date default NULL,
1660 `publisheddate` date default NULL,
1661 `itemnumber` text default NULL,
1662 `claimdate` date default NULL,
1663 `routingnotes` text,
1664 PRIMARY KEY (`serialid`)
1665 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1668 -- Table structure for table `sessions`
1671 DROP TABLE IF EXISTS sessions;
1672 CREATE TABLE sessions (
1673 `id` varchar(32) NOT NULL,
1674 `a_session` text NOT NULL,
1676 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1679 -- Table structure for table `special_holidays`
1682 DROP TABLE IF EXISTS `special_holidays`;
1683 CREATE TABLE `special_holidays` (
1684 `id` int(11) NOT NULL auto_increment,
1685 `branchcode` varchar(10) NOT NULL default '',
1686 `day` smallint(6) NOT NULL default 0,
1687 `month` smallint(6) NOT NULL default 0,
1688 `year` smallint(6) NOT NULL default 0,
1689 `isexception` smallint(1) NOT NULL default 1,
1690 `title` varchar(50) NOT NULL default '',
1691 `description` text NOT NULL,
1693 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1696 -- Table structure for table `statistics`
1699 DROP TABLE IF EXISTS `statistics`;
1700 CREATE TABLE `statistics` (
1701 `datetime` datetime default NULL,
1702 `branch` varchar(10) default NULL,
1703 `proccode` varchar(4) default NULL,
1704 `value` double(16,4) default NULL,
1705 `type` varchar(16) default NULL,
1707 `usercode` varchar(10) default NULL,
1708 `itemnumber` int(11) default NULL,
1709 `itemtype` varchar(10) default NULL,
1710 `borrowernumber` int(11) default NULL,
1711 `associatedborrower` int(11) default NULL,
1712 KEY `timeidx` (`datetime`)
1713 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1716 -- Table structure for table `stopwords`
1719 DROP TABLE IF EXISTS `stopwords`;
1720 CREATE TABLE `stopwords` (
1721 `word` varchar(255) default NULL
1722 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1725 -- Table structure for table `subscription`
1728 DROP TABLE IF EXISTS `subscription`;
1729 CREATE TABLE `subscription` (
1730 `biblionumber` int(11) NOT NULL default 0,
1731 `subscriptionid` int(11) NOT NULL auto_increment,
1732 `librarian` varchar(100) default '',
1733 `startdate` date default NULL,
1734 `aqbooksellerid` int(11) default 0,
1735 `cost` int(11) default 0,
1736 `aqbudgetid` int(11) default 0,
1737 `weeklength` int(11) default 0,
1738 `monthlength` int(11) default 0,
1739 `numberlength` int(11) default 0,
1740 `periodicity` tinyint(4) default 0,
1741 `dow` varchar(100) default '',
1742 `numberingmethod` varchar(100) default '',
1744 `status` varchar(100) NOT NULL default '',
1745 `add1` int(11) default 0,
1746 `every1` int(11) default 0,
1747 `whenmorethan1` int(11) default 0,
1748 `setto1` int(11) default NULL,
1749 `lastvalue1` int(11) default NULL,
1750 `add2` int(11) default 0,
1751 `every2` int(11) default 0,
1752 `whenmorethan2` int(11) default 0,
1753 `setto2` int(11) default NULL,
1754 `lastvalue2` int(11) default NULL,
1755 `add3` int(11) default 0,
1756 `every3` int(11) default 0,
1757 `innerloop1` int(11) default 0,
1758 `innerloop2` int(11) default 0,
1759 `innerloop3` int(11) default 0,
1760 `whenmorethan3` int(11) default 0,
1761 `setto3` int(11) default NULL,
1762 `lastvalue3` int(11) default NULL,
1763 `issuesatonce` tinyint(3) NOT NULL default 1,
1764 `firstacquidate` date default NULL,
1765 `manualhistory` tinyint(1) NOT NULL default 0,
1766 `irregularity` text,
1767 `letter` varchar(20) default NULL,
1768 `numberpattern` tinyint(3) default 0,
1769 `distributedto` text,
1770 `internalnotes` longtext,
1772 `location` varchar(80) NULL default '',
1773 `branchcode` varchar(10) NOT NULL default '',
1774 `hemisphere` tinyint(3) default 0,
1775 `lastbranch` varchar(10),
1776 `serialsadditems` tinyint(1) NOT NULL default '0',
1777 `staffdisplaycount` VARCHAR(10) NULL,
1778 `opacdisplaycount` VARCHAR(10) NULL,
1779 `graceperiod` int(11) NOT NULL default '0',
1780 `enddate` date default NULL,
1781 PRIMARY KEY (`subscriptionid`)
1782 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1785 -- Table structure for table `subscriptionhistory`
1788 DROP TABLE IF EXISTS `subscriptionhistory`;
1789 CREATE TABLE `subscriptionhistory` (
1790 `biblionumber` int(11) NOT NULL default 0,
1791 `subscriptionid` int(11) NOT NULL default 0,
1792 `histstartdate` date default NULL,
1793 `histenddate` date default NULL,
1794 `missinglist` longtext NOT NULL,
1795 `recievedlist` longtext NOT NULL,
1796 `opacnote` varchar(150) NOT NULL default '',
1797 `librariannote` varchar(150) NOT NULL default '',
1798 PRIMARY KEY (`subscriptionid`),
1799 KEY `biblionumber` (`biblionumber`)
1800 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1803 -- Table structure for table `subscriptionroutinglist`
1806 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1807 CREATE TABLE `subscriptionroutinglist` (
1808 `routingid` int(11) NOT NULL auto_increment,
1809 `borrowernumber` int(11) NOT NULL,
1810 `ranking` int(11) default NULL,
1811 `subscriptionid` int(11) NOT NULL,
1812 PRIMARY KEY (`routingid`),
1813 UNIQUE (`subscriptionid`, `borrowernumber`),
1814 CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1815 ON DELETE CASCADE ON UPDATE CASCADE,
1816 CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
1817 ON DELETE CASCADE ON UPDATE CASCADE
1818 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1821 -- Table structure for table `suggestions`
1824 DROP TABLE IF EXISTS `suggestions`;
1825 CREATE TABLE `suggestions` (
1826 `suggestionid` int(8) NOT NULL auto_increment,
1827 `suggestedby` int(11) NOT NULL default 0,
1828 `suggesteddate` date NOT NULL default 0,
1829 `managedby` int(11) default NULL,
1830 `manageddate` date default NULL,
1831 acceptedby INT(11) default NULL,
1832 accepteddate date default NULL,
1833 rejectedby INT(11) default NULL,
1834 rejecteddate date default NULL,
1835 `STATUS` varchar(10) NOT NULL default '',
1837 `author` varchar(80) default NULL,
1838 `title` varchar(80) default NULL,
1839 `copyrightdate` smallint(6) default NULL,
1840 `publishercode` varchar(255) default NULL,
1841 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1842 `volumedesc` varchar(255) default NULL,
1843 `publicationyear` smallint(6) default 0,
1844 `place` varchar(255) default NULL,
1845 `isbn` varchar(30) default NULL,
1846 `mailoverseeing` smallint(1) default 0,
1847 `biblionumber` int(11) default NULL,
1850 branchcode VARCHAR(10) default NULL,
1851 collectiontitle text default NULL,
1852 itemtype VARCHAR(30) default NULL,
1853 quantity SMALLINT(6) default NULL,
1854 currency VARCHAR(3) default NULL,
1855 price DECIMAL(28,6) default NULL,
1856 total DECIMAL(28,6) default NULL,
1857 PRIMARY KEY (`suggestionid`),
1858 KEY `suggestedby` (`suggestedby`),
1859 KEY `managedby` (`managedby`)
1860 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1863 -- Table structure for table `systempreferences`
1866 DROP TABLE IF EXISTS `systempreferences`;
1867 CREATE TABLE `systempreferences` (
1868 `variable` varchar(50) NOT NULL default '',
1870 `options` mediumtext,
1872 `type` varchar(20) default NULL,
1873 PRIMARY KEY (`variable`)
1874 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1877 -- Table structure for table `tags`
1880 DROP TABLE IF EXISTS `tags`;
1881 CREATE TABLE `tags` (
1882 `entry` varchar(255) NOT NULL default '',
1883 `weight` bigint(20) NOT NULL default 0,
1884 PRIMARY KEY (`entry`)
1885 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1888 -- Table structure for table `tags_all`
1891 DROP TABLE IF EXISTS `tags_all`;
1892 CREATE TABLE `tags_all` (
1893 `tag_id` int(11) NOT NULL auto_increment,
1894 `borrowernumber` int(11) NOT NULL,
1895 `biblionumber` int(11) NOT NULL,
1896 `term` varchar(255) NOT NULL,
1897 `language` int(4) default NULL,
1898 `date_created` datetime NOT NULL,
1899 PRIMARY KEY (`tag_id`),
1900 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1901 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1902 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1903 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1904 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1905 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1906 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1909 -- Table structure for table `tags_approval`
1912 DROP TABLE IF EXISTS `tags_approval`;
1913 CREATE TABLE `tags_approval` (
1914 `term` varchar(255) NOT NULL,
1915 `approved` int(1) NOT NULL default '0',
1916 `date_approved` datetime default NULL,
1917 `approved_by` int(11) default NULL,
1918 `weight_total` int(9) NOT NULL default '1',
1919 PRIMARY KEY (`term`),
1920 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1921 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1922 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1923 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1926 -- Table structure for table `tags_index`
1929 DROP TABLE IF EXISTS `tags_index`;
1930 CREATE TABLE `tags_index` (
1931 `term` varchar(255) NOT NULL,
1932 `biblionumber` int(11) NOT NULL,
1933 `weight` int(9) NOT NULL default '1',
1934 PRIMARY KEY (`term`,`biblionumber`),
1935 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1936 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1937 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1938 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1939 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1940 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1943 -- Table structure for table `userflags`
1946 DROP TABLE IF EXISTS `userflags`;
1947 CREATE TABLE `userflags` (
1948 `bit` int(11) NOT NULL default 0,
1949 `flag` varchar(30) default NULL,
1950 `flagdesc` varchar(255) default NULL,
1951 `defaulton` int(11) default NULL,
1953 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1956 -- Table structure for table `virtualshelves`
1959 DROP TABLE IF EXISTS `virtualshelves`;
1960 CREATE TABLE `virtualshelves` (
1961 `shelfnumber` int(11) NOT NULL auto_increment,
1962 `shelfname` varchar(255) default NULL,
1963 `owner` varchar(80) default NULL,
1964 `category` varchar(1) default NULL,
1965 `sortfield` varchar(16) default NULL,
1966 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1967 PRIMARY KEY (`shelfnumber`)
1968 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1971 -- Table structure for table `virtualshelfcontents`
1974 DROP TABLE IF EXISTS `virtualshelfcontents`;
1975 CREATE TABLE `virtualshelfcontents` (
1976 `shelfnumber` int(11) NOT NULL default 0,
1977 `biblionumber` int(11) NOT NULL default 0,
1978 `flags` int(11) default NULL,
1979 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1980 KEY `shelfnumber` (`shelfnumber`),
1981 KEY `biblionumber` (`biblionumber`),
1982 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1983 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1984 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1987 -- Table structure for table `z3950servers`
1990 DROP TABLE IF EXISTS `z3950servers`;
1991 CREATE TABLE `z3950servers` (
1992 `host` varchar(255) default NULL,
1993 `port` int(11) default NULL,
1994 `db` varchar(255) default NULL,
1995 `userid` varchar(255) default NULL,
1996 `password` varchar(255) default NULL,
1998 `id` int(11) NOT NULL auto_increment,
1999 `checked` smallint(6) default NULL,
2000 `rank` int(11) default NULL,
2001 `syntax` varchar(80) default NULL,
2003 `position` enum('primary','secondary','') NOT NULL default 'primary',
2004 `type` enum('zed','opensearch') NOT NULL default 'zed',
2005 `encoding` text default NULL,
2006 `description` text NOT NULL,
2008 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2011 -- Table structure for table `zebraqueue`
2014 DROP TABLE IF EXISTS `zebraqueue`;
2015 CREATE TABLE `zebraqueue` (
2016 `id` int(11) NOT NULL auto_increment,
2017 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2018 `operation` char(20) NOT NULL default '',
2019 `server` char(20) NOT NULL default '',
2020 `done` int(11) NOT NULL default '0',
2021 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2023 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2024 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2026 DROP TABLE IF EXISTS `services_throttle`;
2027 CREATE TABLE `services_throttle` (
2028 `service_type` varchar(10) NOT NULL default '',
2029 `service_count` varchar(45) default NULL,
2030 PRIMARY KEY (`service_type`)
2031 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2033 -- http://www.w3.org/International/articles/language-tags/
2036 DROP TABLE IF EXISTS language_subtag_registry;
2037 CREATE TABLE language_subtag_registry (
2039 type varchar(25), -- language-script-region-variant-extension-privateuse
2040 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2042 id int(11) NOT NULL auto_increment,
2044 KEY `subtag` (`subtag`)
2045 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2047 -- TODO: add suppress_scripts
2048 -- this maps three letter codes defined in iso639.2 back to their
2049 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2050 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2051 CREATE TABLE language_rfc4646_to_iso639 (
2052 rfc4646_subtag varchar(25),
2053 iso639_2_code varchar(25),
2054 id int(11) NOT NULL auto_increment,
2056 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2057 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2059 DROP TABLE IF EXISTS language_descriptions;
2060 CREATE TABLE language_descriptions (
2064 description varchar(255),
2065 id int(11) NOT NULL auto_increment,
2067 KEY `lang` (`lang`),
2068 KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
2069 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2071 -- bi-directional support, keyed by script subcode
2072 DROP TABLE IF EXISTS language_script_bidi;
2073 CREATE TABLE language_script_bidi (
2074 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2075 bidi varchar(3), -- rtl ltr
2076 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2077 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2079 -- TODO: need to map language subtags to script subtags for detection
2080 -- of bidi when script is not specified (like ar, he)
2081 DROP TABLE IF EXISTS language_script_mapping;
2082 CREATE TABLE language_script_mapping (
2083 language_subtag varchar(25),
2084 script_subtag varchar(25),
2085 KEY `language_subtag` (`language_subtag`)
2086 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2088 DROP TABLE IF EXISTS `permissions`;
2089 CREATE TABLE `permissions` (
2090 `module_bit` int(11) NOT NULL DEFAULT 0,
2091 `code` varchar(64) DEFAULT NULL,
2092 `description` varchar(255) DEFAULT NULL,
2093 PRIMARY KEY (`module_bit`, `code`),
2094 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2095 ON DELETE CASCADE ON UPDATE CASCADE
2096 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2098 DROP TABLE IF EXISTS `serialitems`;
2099 CREATE TABLE `serialitems` (
2100 `itemnumber` int(11) NOT NULL,
2101 `serialid` int(11) NOT NULL,
2102 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2103 KEY `serialitems_sfk_1` (`serialid`),
2104 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
2105 CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
2106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2108 DROP TABLE IF EXISTS `user_permissions`;
2109 CREATE TABLE `user_permissions` (
2110 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2111 `module_bit` int(11) NOT NULL DEFAULT 0,
2112 `code` varchar(64) DEFAULT NULL,
2113 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2114 ON DELETE CASCADE ON UPDATE CASCADE,
2115 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2116 ON DELETE CASCADE ON UPDATE CASCADE
2117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2120 -- Table structure for table `tmp_holdsqueue`
2123 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2124 CREATE TABLE `tmp_holdsqueue` (
2125 `biblionumber` int(11) default NULL,
2126 `itemnumber` int(11) default NULL,
2127 `barcode` varchar(20) default NULL,
2128 `surname` mediumtext NOT NULL,
2131 `borrowernumber` int(11) NOT NULL,
2132 `cardnumber` varchar(16) default NULL,
2133 `reservedate` date default NULL,
2135 `itemcallnumber` varchar(255) default NULL,
2136 `holdingbranch` varchar(10) default NULL,
2137 `pickbranch` varchar(10) default NULL,
2139 `item_level_request` tinyint(4) NOT NULL default 0
2140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2143 -- Table structure for table `message_queue`
2146 DROP TABLE IF EXISTS `message_queue`;
2147 CREATE TABLE `message_queue` (
2148 `message_id` int(11) NOT NULL auto_increment,
2149 `borrowernumber` int(11) default NULL,
2152 `metadata` text DEFAULT NULL,
2153 `letter_code` varchar(64) DEFAULT NULL,
2154 `message_transport_type` varchar(20) NOT NULL,
2155 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2156 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2157 `to_address` mediumtext,
2158 `from_address` mediumtext,
2159 `content_type` text,
2160 KEY `message_id` (`message_id`),
2161 KEY `borrowernumber` (`borrowernumber`),
2162 KEY `message_transport_type` (`message_transport_type`),
2163 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2164 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2168 -- Table structure for table `message_transport_types`
2171 DROP TABLE IF EXISTS `message_transport_types`;
2172 CREATE TABLE `message_transport_types` (
2173 `message_transport_type` varchar(20) NOT NULL,
2174 PRIMARY KEY (`message_transport_type`)
2175 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2178 -- Table structure for table `message_attributes`
2181 DROP TABLE IF EXISTS `message_attributes`;
2182 CREATE TABLE `message_attributes` (
2183 `message_attribute_id` int(11) NOT NULL auto_increment,
2184 `message_name` varchar(40) NOT NULL default '',
2185 `takes_days` tinyint(1) NOT NULL default '0',
2186 PRIMARY KEY (`message_attribute_id`),
2187 UNIQUE KEY `message_name` (`message_name`)
2188 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2191 -- Table structure for table `message_transports`
2194 DROP TABLE IF EXISTS `message_transports`;
2195 CREATE TABLE `message_transports` (
2196 `message_attribute_id` int(11) NOT NULL,
2197 `message_transport_type` varchar(20) NOT NULL,
2198 `is_digest` tinyint(1) NOT NULL default '0',
2199 `letter_module` varchar(20) NOT NULL default '',
2200 `letter_code` varchar(20) NOT NULL default '',
2201 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2202 KEY `message_transport_type` (`message_transport_type`),
2203 KEY `letter_module` (`letter_module`,`letter_code`),
2204 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2205 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2206 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2210 -- Table structure for table `borrower_message_preferences`
2213 DROP TABLE IF EXISTS `borrower_message_preferences`;
2214 CREATE TABLE `borrower_message_preferences` (
2215 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2216 `borrowernumber` int(11) default NULL,
2217 `categorycode` varchar(10) default NULL,
2218 `message_attribute_id` int(11) default '0',
2219 `days_in_advance` int(11) default '0',
2220 `wants_digest` tinyint(1) NOT NULL default '0',
2221 PRIMARY KEY (`borrower_message_preference_id`),
2222 KEY `borrowernumber` (`borrowernumber`),
2223 KEY `categorycode` (`categorycode`),
2224 KEY `message_attribute_id` (`message_attribute_id`),
2225 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2226 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2227 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2231 -- Table structure for table `borrower_message_transport_preferences`
2234 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2235 CREATE TABLE `borrower_message_transport_preferences` (
2236 `borrower_message_preference_id` int(11) NOT NULL default '0',
2237 `message_transport_type` varchar(20) NOT NULL default '0',
2238 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2239 KEY `message_transport_type` (`message_transport_type`),
2240 CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_message_preference_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2241 CONSTRAINT `borrower_message_transport_preferences_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
2242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2245 -- Table structure for the table branch_transfer_limits
2248 DROP TABLE IF EXISTS `branch_transfer_limits`;
2249 CREATE TABLE branch_transfer_limits (
2250 limitId int(8) NOT NULL auto_increment,
2251 toBranch varchar(10) NOT NULL,
2252 fromBranch varchar(10) NOT NULL,
2253 itemtype varchar(10) NULL,
2254 ccode varchar(10) NULL,
2255 PRIMARY KEY (limitId)
2256 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2259 -- Table structure for table `item_circulation_alert_preferences`
2262 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2263 CREATE TABLE `item_circulation_alert_preferences` (
2264 `id` int(11) NOT NULL auto_increment,
2265 `branchcode` varchar(10) NOT NULL,
2266 `categorycode` varchar(10) NOT NULL,
2267 `item_type` varchar(10) NOT NULL,
2268 `notification` varchar(16) NOT NULL,
2270 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2274 -- Table structure for table `messages`
2276 DROP TABLE IF EXISTS `messages`;
2277 CREATE TABLE `messages` (
2278 `message_id` int(11) NOT NULL auto_increment,
2279 `borrowernumber` int(11) NOT NULL,
2280 `branchcode` varchar(10) default NULL,
2281 `message_type` varchar(1) NOT NULL,
2282 `message` text NOT NULL,
2283 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2284 PRIMARY KEY (`message_id`)
2285 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2288 -- Table structure for table `accountlines`
2291 DROP TABLE IF EXISTS `accountlines`;
2292 CREATE TABLE `accountlines` (
2293 `borrowernumber` int(11) NOT NULL default 0,
2294 `accountno` smallint(6) NOT NULL default 0,
2295 `itemnumber` int(11) default NULL,
2296 `date` date default NULL,
2297 `amount` decimal(28,6) default NULL,
2298 `description` mediumtext,
2299 `dispute` mediumtext,
2300 `accounttype` varchar(5) default NULL,
2301 `amountoutstanding` decimal(28,6) default NULL,
2302 `lastincrement` decimal(28,6) default NULL,
2303 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2304 `notify_id` int(11) NOT NULL default 0,
2305 `notify_level` int(2) NOT NULL default 0,
2306 `note` text NULL default NULL,
2307 `manager_id` int(11) NULL,
2308 KEY `acctsborridx` (`borrowernumber`),
2309 KEY `timeidx` (`timestamp`),
2310 KEY `itemnumber` (`itemnumber`),
2311 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2312 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2316 -- Table structure for table `accountoffsets`
2319 DROP TABLE IF EXISTS `accountoffsets`;
2320 CREATE TABLE `accountoffsets` (
2321 `borrowernumber` int(11) NOT NULL default 0,
2322 `accountno` smallint(6) NOT NULL default 0,
2323 `offsetaccount` smallint(6) NOT NULL default 0,
2324 `offsetamount` decimal(28,6) default NULL,
2325 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2326 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2327 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2330 -- Table structure for table `action_logs`
2333 DROP TABLE IF EXISTS `action_logs`;
2334 CREATE TABLE `action_logs` (
2335 `action_id` int(11) NOT NULL auto_increment,
2336 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2337 `user` int(11) NOT NULL default 0,
2340 `object` int(11) default NULL,
2342 PRIMARY KEY (`action_id`),
2343 KEY (`timestamp`,`user`)
2344 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2347 -- Table structure for table `alert`
2350 DROP TABLE IF EXISTS `alert`;
2351 CREATE TABLE `alert` (
2352 `alertid` int(11) NOT NULL auto_increment,
2353 `borrowernumber` int(11) NOT NULL default 0,
2354 `type` varchar(10) NOT NULL default '',
2355 `externalid` varchar(20) NOT NULL default '',
2356 PRIMARY KEY (`alertid`),
2357 KEY `borrowernumber` (`borrowernumber`),
2358 KEY `type` (`type`,`externalid`)
2359 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2362 -- Table structure for table `aqbasketgroups`
2365 DROP TABLE IF EXISTS `aqbasketgroups`;
2366 CREATE TABLE `aqbasketgroups` (
2367 `id` int(11) NOT NULL auto_increment,
2368 `name` varchar(50) default NULL,
2369 `closed` tinyint(1) default NULL,
2370 `booksellerid` int(11) NOT NULL,
2371 `deliveryplace` varchar(10) default NULL,
2372 `freedeliveryplace` text default NULL,
2373 `deliverycomment` varchar(255) default NULL,
2374 `billingplace` varchar(10) default NULL,
2376 KEY `booksellerid` (`booksellerid`),
2377 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2381 -- Table structure for table `aqbasket`
2384 DROP TABLE IF EXISTS `aqbasket`;
2385 CREATE TABLE `aqbasket` (
2386 `basketno` int(11) NOT NULL auto_increment,
2387 `basketname` varchar(50) default NULL,
2389 `booksellernote` mediumtext,
2390 `contractnumber` int(11),
2391 `creationdate` date default NULL,
2392 `closedate` date default NULL,
2393 `booksellerid` int(11) NOT NULL default 1,
2394 `authorisedby` varchar(10) default NULL,
2395 `booksellerinvoicenumber` mediumtext,
2396 `basketgroupid` int(11),
2397 PRIMARY KEY (`basketno`),
2398 KEY `booksellerid` (`booksellerid`),
2399 KEY `basketgroupid` (`basketgroupid`),
2400 KEY `contractnumber` (`contractnumber`),
2401 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2402 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2403 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2404 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2407 -- Table structure for table `aqbooksellers`
2410 DROP TABLE IF EXISTS `aqbooksellers`;
2411 CREATE TABLE `aqbooksellers` (
2412 `id` int(11) NOT NULL auto_increment,
2413 `name` mediumtext NOT NULL,
2414 `address1` mediumtext,
2415 `address2` mediumtext,
2416 `address3` mediumtext,
2417 `address4` mediumtext,
2418 `phone` varchar(30) default NULL,
2419 `accountnumber` mediumtext,
2420 `othersupplier` mediumtext,
2421 `currency` varchar(3) NOT NULL default '',
2422 `booksellerfax` mediumtext,
2424 `bookselleremail` mediumtext,
2425 `booksellerurl` mediumtext,
2426 `contact` varchar(100) default NULL,
2427 `postal` mediumtext,
2428 `url` varchar(255) default NULL,
2429 `contpos` varchar(100) default NULL,
2430 `contphone` varchar(100) default NULL,
2431 `contfax` varchar(100) default NULL,
2432 `contaltphone` varchar(100) default NULL,
2433 `contemail` varchar(100) default NULL,
2434 `contnotes` mediumtext,
2435 `active` tinyint(4) default NULL,
2436 `listprice` varchar(10) default NULL,
2437 `invoiceprice` varchar(10) default NULL,
2438 `gstreg` tinyint(4) default NULL,
2439 `listincgst` tinyint(4) default NULL,
2440 `invoiceincgst` tinyint(4) default NULL,
2441 `gstrate` decimal(6,4) default NULL,
2442 `discount` float(6,4) default NULL,
2443 `fax` varchar(50) default NULL,
2445 KEY `listprice` (`listprice`),
2446 KEY `invoiceprice` (`invoiceprice`),
2447 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2448 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2449 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2452 -- Table structure for table `aqbudgets`
2455 DROP TABLE IF EXISTS `aqbudgets`;
2456 CREATE TABLE `aqbudgets` (
2457 `budget_id` int(11) NOT NULL auto_increment,
2458 `budget_parent_id` int(11) default NULL,
2459 `budget_code` varchar(30) default NULL,
2460 `budget_name` varchar(80) default NULL,
2461 `budget_branchcode` varchar(10) default NULL,
2462 `budget_amount` decimal(28,6) NULL default '0.00',
2463 `budget_encumb` decimal(28,6) NULL default '0.00',
2464 `budget_expend` decimal(28,6) NULL default '0.00',
2465 `budget_notes` mediumtext,
2466 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2467 `budget_period_id` int(11) default NULL,
2468 `sort1_authcat` varchar(80) default NULL,
2469 `sort2_authcat` varchar(80) default NULL,
2470 `budget_owner_id` int(11) default NULL,
2471 `budget_permission` int(1) default '0',
2472 PRIMARY KEY (`budget_id`)
2473 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2477 -- Table structure for table `aqbudgetperiods`
2481 DROP TABLE IF EXISTS `aqbudgetperiods`;
2482 CREATE TABLE `aqbudgetperiods` (
2483 `budget_period_id` int(11) NOT NULL auto_increment,
2484 `budget_period_startdate` date NOT NULL,
2485 `budget_period_enddate` date NOT NULL,
2486 `budget_period_active` tinyint(1) default '0',
2487 `budget_period_description` mediumtext,
2488 `budget_period_total` decimal(28,6),
2489 `budget_period_locked` tinyint(1) default NULL,
2490 `sort1_authcat` varchar(10) default NULL,
2491 `sort2_authcat` varchar(10) default NULL,
2492 PRIMARY KEY (`budget_period_id`)
2493 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2496 -- Table structure for table `aqbudgets_planning`
2499 DROP TABLE IF EXISTS `aqbudgets_planning`;
2500 CREATE TABLE `aqbudgets_planning` (
2501 `plan_id` int(11) NOT NULL auto_increment,
2502 `budget_id` int(11) NOT NULL,
2503 `budget_period_id` int(11) NOT NULL,
2504 `estimated_amount` decimal(28,6) default NULL,
2505 `authcat` varchar(30) NOT NULL,
2506 `authvalue` varchar(30) NOT NULL,
2507 `display` tinyint(1) DEFAULT 1,
2508 PRIMARY KEY (`plan_id`),
2509 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2510 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2513 -- Table structure for table 'aqcontract'
2516 DROP TABLE IF EXISTS `aqcontract`;
2517 CREATE TABLE `aqcontract` (
2518 `contractnumber` int(11) NOT NULL auto_increment,
2519 `contractstartdate` date default NULL,
2520 `contractenddate` date default NULL,
2521 `contractname` varchar(50) default NULL,
2522 `contractdescription` mediumtext,
2523 `booksellerid` int(11) not NULL,
2524 PRIMARY KEY (`contractnumber`),
2525 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2526 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2530 -- Table structure for table `aqorderdelivery`
2533 DROP TABLE IF EXISTS `aqorderdelivery`;
2534 CREATE TABLE `aqorderdelivery` (
2535 `ordernumber` date default NULL,
2536 `deliverynumber` smallint(6) NOT NULL default 0,
2537 `deliverydate` varchar(18) default NULL,
2538 `qtydelivered` smallint(6) default NULL,
2539 `deliverycomments` mediumtext
2540 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2543 -- Table structure for table `aqorders`
2546 DROP TABLE IF EXISTS `aqorders`;
2547 CREATE TABLE `aqorders` (
2548 `ordernumber` int(11) NOT NULL auto_increment,
2549 `biblionumber` int(11) default NULL,
2550 `entrydate` date default NULL,
2551 `quantity` smallint(6) default NULL,
2552 `currency` varchar(3) default NULL,
2553 `listprice` decimal(28,6) default NULL,
2554 `totalamount` decimal(28,6) default NULL,
2555 `datereceived` date default NULL,
2556 `booksellerinvoicenumber` mediumtext,
2557 `freight` decimal(28,6) default NULL,
2558 `unitprice` decimal(28,6) default NULL,
2559 `quantityreceived` smallint(6) NOT NULL default 0,
2560 `cancelledby` varchar(10) default NULL,
2561 `datecancellationprinted` date default NULL,
2563 `supplierreference` mediumtext,
2564 `purchaseordernumber` mediumtext,
2565 `subscription` tinyint(1) default NULL,
2566 `serialid` varchar(30) default NULL,
2567 `basketno` int(11) default NULL,
2568 `biblioitemnumber` int(11) default NULL,
2569 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2570 `rrp` decimal(13,2) default NULL,
2571 `ecost` decimal(13,2) default NULL,
2572 `gst` decimal(13,2) default NULL,
2573 `budget_id` int(11) NOT NULL,
2574 `budgetgroup_id` int(11) NOT NULL,
2575 `budgetdate` date default NULL,
2576 `sort1` varchar(80) default NULL,
2577 `sort2` varchar(80) default NULL,
2578 `sort1_authcat` varchar(10) default NULL,
2579 `sort2_authcat` varchar(10) default NULL,
2580 `uncertainprice` tinyint(1),
2581 PRIMARY KEY (`ordernumber`),
2582 KEY `basketno` (`basketno`),
2583 KEY `biblionumber` (`biblionumber`),
2584 KEY `budget_id` (`budget_id`),
2585 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2586 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2587 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2591 -- Table structure for table `aqorders_items`
2594 DROP TABLE IF EXISTS `aqorders_items`;
2595 CREATE TABLE `aqorders_items` (
2596 `ordernumber` int(11) NOT NULL,
2597 `itemnumber` int(11) NOT NULL,
2598 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2599 PRIMARY KEY (`itemnumber`),
2600 KEY `ordernumber` (`ordernumber`)
2601 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2604 -- Table structure for table `fieldmapping`
2607 DROP TABLE IF EXISTS `fieldmapping`;
2608 CREATE TABLE `fieldmapping` (
2609 `id` int(11) NOT NULL auto_increment,
2610 `field` varchar(255) NOT NULL,
2611 `frameworkcode` char(4) NOT NULL default '',
2612 `fieldcode` char(3) NOT NULL,
2613 `subfieldcode` char(1) NOT NULL,
2615 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2618 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2619 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2620 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2621 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2622 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2623 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2624 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2625 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;