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` (
99 `id` int(11) NOT NULL auto_increment,
100 `category` varchar(10) NOT NULL default '',
101 `authorised_value` varchar(80) NOT NULL default '',
102 `lib` varchar(80) default NULL,
103 `lib_opac` VARCHAR(80) default NULL,
104 `imageurl` varchar(200) default NULL,
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` (
117 `biblionumber` int(11) NOT NULL auto_increment,
118 `frameworkcode` varchar(4) NOT NULL default '',
121 `unititle` mediumtext,
123 `serial` tinyint(1) default NULL,
124 `seriestitle` mediumtext,
125 `copyrightdate` smallint(6) default NULL,
126 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
127 `datecreated` DATE NOT NULL,
128 `abstract` mediumtext,
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` (
197 `borrowernumber` int(11) NOT NULL auto_increment,
198 `cardnumber` varchar(16) default NULL,
199 `surname` mediumtext NOT NULL,
202 `othernames` mediumtext,
204 `streetnumber` varchar(10) default NULL,
205 `streettype` varchar(50) default NULL,
206 `address` mediumtext NOT NULL,
208 `city` mediumtext NOT NULL,
209 `state` text default NULL,
210 `zipcode` varchar(25) default NULL,
214 `mobile` varchar(50) default NULL,
218 `B_streetnumber` varchar(10) default NULL,
219 `B_streettype` varchar(50) default NULL,
220 `B_address` varchar(100) default NULL,
221 `B_address2` text default NULL,
223 `B_state` text default NULL,
224 `B_zipcode` varchar(25) default NULL,
227 `B_phone` mediumtext,
228 `dateofbirth` date default NULL,
229 `branchcode` varchar(10) NOT NULL default '',
230 `categorycode` varchar(10) NOT NULL default '',
231 `dateenrolled` date default NULL,
232 `dateexpiry` date default NULL,
233 `gonenoaddress` tinyint(1) default NULL,
234 `lost` tinyint(1) default NULL,
235 `debarred` tinyint(1) default NULL,
236 `contactname` mediumtext,
237 `contactfirstname` text,
239 `guarantorid` int(11) default NULL,
240 `borrowernotes` mediumtext,
241 `relationship` varchar(100) default NULL,
242 `ethnicity` varchar(50) default NULL,
243 `ethnotes` varchar(255) default NULL,
244 `sex` varchar(1) default NULL,
245 `password` varchar(30) default NULL,
246 `flags` int(11) default NULL,
247 `userid` varchar(30) default NULL,
248 `opacnote` mediumtext,
249 `contactnote` varchar(255) default NULL,
250 `sort1` varchar(80) default NULL,
251 `sort2` varchar(80) default NULL,
252 `altcontactfirstname` varchar(255) default NULL,
253 `altcontactsurname` varchar(255) default NULL,
254 `altcontactaddress1` varchar(255) default NULL,
255 `altcontactaddress2` varchar(255) default NULL,
256 `altcontactaddress3` varchar(255) default NULL,
257 `altcontactstate` text default NULL,
258 `altcontactzipcode` varchar(50) default NULL,
259 `altcontactcountry` text default NULL,
260 `altcontactphone` varchar(50) default NULL,
261 `smsalertnumber` varchar(50) default NULL,
262 `privacy` integer(11) DEFAULT '1' NOT NULL,
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` (
341 `branchcode` varchar(10) NOT NULL default '',
342 `branchname` mediumtext NOT NULL,
343 `branchaddress1` mediumtext,
344 `branchaddress2` mediumtext,
345 `branchaddress3` mediumtext,
346 `branchzip` varchar(25) default NULL,
347 `branchcity` mediumtext,
348 `branchstate` mediumtext,
349 `branchcountry` text,
350 `branchphone` mediumtext,
351 `branchfax` mediumtext,
352 `branchemail` mediumtext,
353 `branchurl` mediumtext,
354 `issuing` tinyint(4) default NULL,
355 `branchip` varchar(15) default NULL,
356 `branchprinter` varchar(100) default NULL,
357 `branchnotes` mediumtext,
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 CREATE TABLE collections (
437 colId integer(11) NOT NULL auto_increment,
438 colTitle varchar(100) NOT NULL DEFAULT '',
439 colDesc text NOT NULL,
440 colBranchcode varchar(4) DEFAULT NULL comment 'branchcode for branch where item should be held.',
442 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
445 -- Table: collections_tracking
447 CREATE TABLE collections_tracking (
448 ctId integer(11) NOT NULL auto_increment,
449 colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId',
450 itemnumber integer(11) NOT NULL DEFAULT 0 comment 'items.itemnumber',
452 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
455 -- Table structure for table `borrower_branch_circ_rules`
458 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
459 CREATE TABLE `branch_borrower_circ_rules` (
460 `branchcode` VARCHAR(10) NOT NULL,
461 `categorycode` VARCHAR(10) NOT NULL,
462 `maxissueqty` int(4) default NULL,
463 PRIMARY KEY (`categorycode`, `branchcode`),
464 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
465 ON DELETE CASCADE ON UPDATE CASCADE,
466 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
467 ON DELETE CASCADE ON UPDATE CASCADE
468 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
471 -- Table structure for table `default_borrower_circ_rules`
474 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
475 CREATE TABLE `default_borrower_circ_rules` (
476 `categorycode` VARCHAR(10) NOT NULL,
477 `maxissueqty` int(4) default NULL,
478 PRIMARY KEY (`categorycode`),
479 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
480 ON DELETE CASCADE ON UPDATE CASCADE
481 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
484 -- Table structure for table `default_branch_circ_rules`
487 DROP TABLE IF EXISTS `default_branch_circ_rules`;
488 CREATE TABLE `default_branch_circ_rules` (
489 `branchcode` VARCHAR(10) NOT NULL,
490 `maxissueqty` int(4) default NULL,
491 `holdallowed` tinyint(1) default NULL,
492 PRIMARY KEY (`branchcode`),
493 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
494 ON DELETE CASCADE ON UPDATE CASCADE
495 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
498 -- Table structure for table `default_branch_item_rules`
500 DROP TABLE IF EXISTS `default_branch_item_rules`;
501 CREATE TABLE `default_branch_item_rules` (
502 `itemtype` varchar(10) NOT NULL,
503 `holdallowed` tinyint(1) default NULL,
504 PRIMARY KEY (`itemtype`),
505 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
506 ON DELETE CASCADE ON UPDATE CASCADE
507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
510 -- Table structure for table `default_circ_rules`
513 DROP TABLE IF EXISTS `default_circ_rules`;
514 CREATE TABLE `default_circ_rules` (
515 `singleton` enum('singleton') NOT NULL default 'singleton',
516 `maxissueqty` int(4) default NULL,
517 `holdallowed` int(1) default NULL,
518 PRIMARY KEY (`singleton`)
519 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
522 -- Table structure for table `cities`
525 DROP TABLE IF EXISTS `cities`;
526 CREATE TABLE `cities` (
527 `cityid` int(11) NOT NULL auto_increment,
528 `city_name` varchar(100) NOT NULL default '',
529 `city_state` VARCHAR( 100 ) NULL DEFAULT NULL,
530 `city_country` VARCHAR( 100 ) NULL DEFAULT NULL,
531 `city_zipcode` varchar(20) default NULL,
532 PRIMARY KEY (`cityid`)
533 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
536 -- Table structure for table `class_sort_rules`
539 DROP TABLE IF EXISTS `class_sort_rules`;
540 CREATE TABLE `class_sort_rules` (
541 `class_sort_rule` varchar(10) NOT NULL default '',
542 `description` mediumtext,
543 `sort_routine` varchar(30) NOT NULL default '',
544 PRIMARY KEY (`class_sort_rule`),
545 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
546 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
549 -- Table structure for table `class_sources`
552 DROP TABLE IF EXISTS `class_sources`;
553 CREATE TABLE `class_sources` (
554 `cn_source` varchar(10) NOT NULL default '',
555 `description` mediumtext,
556 `used` tinyint(4) NOT NULL default 0,
557 `class_sort_rule` varchar(10) NOT NULL default '',
558 PRIMARY KEY (`cn_source`),
559 UNIQUE KEY `cn_source_idx` (`cn_source`),
560 KEY `used_idx` (`used`),
561 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
565 -- Table structure for table `currency`
568 DROP TABLE IF EXISTS `currency`;
569 CREATE TABLE `currency` (
570 `currency` varchar(10) NOT NULL default '',
571 `symbol` varchar(5) default NULL,
572 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
573 `rate` float(15,5) default NULL,
574 `active` tinyint(1) default NULL,
575 PRIMARY KEY (`currency`)
576 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
579 -- Table structure for table `deletedbiblio`
582 DROP TABLE IF EXISTS `deletedbiblio`;
583 CREATE TABLE `deletedbiblio` (
584 `biblionumber` int(11) NOT NULL default 0,
585 `frameworkcode` varchar(4) NOT NULL default '',
588 `unititle` mediumtext,
590 `serial` tinyint(1) default NULL,
591 `seriestitle` mediumtext,
592 `copyrightdate` smallint(6) default NULL,
593 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
594 `datecreated` DATE NOT NULL,
595 `abstract` mediumtext,
596 PRIMARY KEY (`biblionumber`),
597 KEY `blbnoidx` (`biblionumber`)
598 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
601 -- Table structure for table `deletedbiblioitems`
604 DROP TABLE IF EXISTS `deletedbiblioitems`;
605 CREATE TABLE `deletedbiblioitems` (
606 `biblioitemnumber` int(11) NOT NULL default 0,
607 `biblionumber` int(11) NOT NULL default 0,
610 `itemtype` varchar(10) default NULL,
611 `isbn` varchar(30) default NULL,
612 `issn` varchar(9) default NULL,
613 `publicationyear` text,
614 `publishercode` varchar(255) default NULL,
615 `volumedate` date default NULL,
617 `collectiontitle` mediumtext default NULL,
618 `collectionissn` text default NULL,
619 `collectionvolume` mediumtext default NULL,
620 `editionstatement` text default NULL,
621 `editionresponsibility` text default NULL,
622 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
623 `illus` varchar(255) default NULL,
624 `pages` varchar(255) default NULL,
626 `size` varchar(255) default NULL,
627 `place` varchar(255) default NULL,
628 `lccn` varchar(25) default NULL,
630 `url` varchar(255) default NULL,
631 `cn_source` varchar(10) default NULL,
632 `cn_class` varchar(30) default NULL,
633 `cn_item` varchar(10) default NULL,
634 `cn_suffix` varchar(10) default NULL,
635 `cn_sort` varchar(30) default NULL,
636 `totalissues` int(10),
637 `marcxml` longtext NOT NULL,
638 PRIMARY KEY (`biblioitemnumber`),
639 KEY `bibinoidx` (`biblioitemnumber`),
640 KEY `bibnoidx` (`biblionumber`),
642 KEY `publishercode` (`publishercode`)
643 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
646 -- Table structure for table `deletedborrowers`
649 DROP TABLE IF EXISTS `deletedborrowers`;
650 CREATE TABLE `deletedborrowers` (
651 `borrowernumber` int(11) NOT NULL default 0,
652 `cardnumber` varchar(16) NOT NULL default '',
653 `surname` mediumtext NOT NULL,
656 `othernames` mediumtext,
658 `streetnumber` varchar(10) default NULL,
659 `streettype` varchar(50) default NULL,
660 `address` mediumtext NOT NULL,
662 `city` mediumtext NOT NULL,
663 `state` text default NULL,
664 `zipcode` varchar(25) default NULL,
668 `mobile` varchar(50) default NULL,
672 `B_streetnumber` varchar(10) default NULL,
673 `B_streettype` varchar(50) default NULL,
674 `B_address` varchar(100) default NULL,
675 `B_address2` text default NULL,
677 `B_state` text default NULL,
678 `B_zipcode` varchar(25) default NULL,
681 `B_phone` mediumtext,
682 `dateofbirth` date default NULL,
683 `branchcode` varchar(10) NOT NULL default '',
684 `categorycode` varchar(10) default NULL,
685 `dateenrolled` date default NULL,
686 `dateexpiry` date default NULL,
687 `gonenoaddress` tinyint(1) default NULL,
688 `lost` tinyint(1) default NULL,
689 `debarred` tinyint(1) default NULL,
690 `contactname` mediumtext,
691 `contactfirstname` text,
693 `guarantorid` int(11) default NULL,
694 `borrowernotes` mediumtext,
695 `relationship` varchar(100) default NULL,
696 `ethnicity` varchar(50) default NULL,
697 `ethnotes` varchar(255) default NULL,
698 `sex` varchar(1) default NULL,
699 `password` varchar(30) default NULL,
700 `flags` int(11) default NULL,
701 `userid` varchar(30) default NULL,
702 `opacnote` mediumtext,
703 `contactnote` varchar(255) default NULL,
704 `sort1` varchar(80) default NULL,
705 `sort2` varchar(80) default NULL,
706 `altcontactfirstname` varchar(255) default NULL,
707 `altcontactsurname` varchar(255) default NULL,
708 `altcontactaddress1` varchar(255) default NULL,
709 `altcontactaddress2` varchar(255) default NULL,
710 `altcontactaddress3` varchar(255) default NULL,
711 `altcontactstate` text default NULL,
712 `altcontactzipcode` varchar(50) default NULL,
713 `altcontactcountry` text default NULL,
714 `altcontactphone` varchar(50) default NULL,
715 `smsalertnumber` varchar(50) default NULL,
716 `privacy` integer(11) DEFAULT '1' NOT NULL,
717 KEY `borrowernumber` (`borrowernumber`),
718 KEY `cardnumber` (`cardnumber`)
719 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
722 -- Table structure for table `deleteditems`
725 DROP TABLE IF EXISTS `deleteditems`;
726 CREATE TABLE `deleteditems` (
727 `itemnumber` int(11) NOT NULL default 0,
728 `biblionumber` int(11) NOT NULL default 0,
729 `biblioitemnumber` int(11) NOT NULL default 0,
730 `barcode` varchar(20) default NULL,
731 `dateaccessioned` date default NULL,
732 `booksellerid` mediumtext default NULL,
733 `homebranch` varchar(10) default NULL,
734 `price` decimal(8,2) default NULL,
735 `replacementprice` decimal(8,2) default NULL,
736 `replacementpricedate` date default NULL,
737 `datelastborrowed` date default NULL,
738 `datelastseen` date default NULL,
739 `stack` tinyint(1) default NULL,
740 `notforloan` tinyint(1) NOT NULL default 0,
741 `damaged` tinyint(1) NOT NULL default 0,
742 `itemlost` tinyint(1) NOT NULL default 0,
743 `wthdrawn` tinyint(1) NOT NULL default 0,
744 `itemcallnumber` varchar(255) default NULL,
745 `issues` smallint(6) default NULL,
746 `renewals` smallint(6) default NULL,
747 `reserves` smallint(6) default NULL,
748 `restricted` tinyint(1) default NULL,
749 `itemnotes` mediumtext,
750 `holdingbranch` varchar(10) default NULL,
751 `paidfor` mediumtext,
752 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
753 `location` varchar(80) default NULL,
754 `permanent_location` varchar(80) default NULL,
755 `onloan` date default NULL,
756 `cn_source` varchar(10) default NULL,
757 `cn_sort` varchar(30) default NULL,
758 `ccode` varchar(10) default NULL,
759 `materials` varchar(10) default NULL,
760 `uri` varchar(255) default NULL,
761 `itype` varchar(10) default NULL,
762 `more_subfields_xml` longtext default NULL,
763 `enumchron` text default NULL,
764 `copynumber` varchar(32) default NULL,
765 `stocknumber` varchar(32) default NULL,
767 PRIMARY KEY (`itemnumber`),
768 KEY `delitembarcodeidx` (`barcode`),
769 KEY `delitemstocknumberidx` (`stocknumber`),
770 KEY `delitembinoidx` (`biblioitemnumber`),
771 KEY `delitembibnoidx` (`biblionumber`),
772 KEY `delhomebranch` (`homebranch`),
773 KEY `delholdingbranch` (`holdingbranch`)
774 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
777 -- Table structure for table `ethnicity`
780 DROP TABLE IF EXISTS `ethnicity`;
781 CREATE TABLE `ethnicity` (
782 `code` varchar(10) NOT NULL default '',
783 `name` varchar(255) default NULL,
785 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
788 -- Table structure for table `export_format`
791 DROP TABLE IF EXISTS `export_format`;
792 CREATE TABLE `export_format` (
793 `export_format_id` int(11) NOT NULL auto_increment,
794 `profile` varchar(255) NOT NULL,
795 `description` mediumtext NOT NULL,
796 `marcfields` mediumtext NOT NULL,
797 `csv_separator` varchar(2) NOT NULL,
798 `field_separator` varchar(2) NOT NULL,
799 `subfield_separator` varchar(2) NOT NULL,
800 `encoding` varchar(255) NOT NULL,
801 PRIMARY KEY (`export_format_id`)
802 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
806 -- Table structure for table `hold_fill_targets`
809 DROP TABLE IF EXISTS `hold_fill_targets`;
810 CREATE TABLE hold_fill_targets (
811 `borrowernumber` int(11) NOT NULL,
812 `biblionumber` int(11) NOT NULL,
813 `itemnumber` int(11) NOT NULL,
814 `source_branchcode` varchar(10) default NULL,
815 `item_level_request` tinyint(4) NOT NULL default 0,
816 PRIMARY KEY `itemnumber` (`itemnumber`),
817 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
818 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
819 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
820 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
821 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
822 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
823 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
824 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
825 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
826 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
829 -- Table structure for table `import_batches`
832 DROP TABLE IF EXISTS `import_batches`;
833 CREATE TABLE `import_batches` (
834 `import_batch_id` int(11) NOT NULL auto_increment,
835 `matcher_id` int(11) default NULL,
836 `template_id` int(11) default NULL,
837 `branchcode` varchar(10) default NULL,
838 `num_biblios` int(11) NOT NULL default 0,
839 `num_items` int(11) NOT NULL default 0,
840 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
841 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
842 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
843 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
844 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
845 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
846 `file_name` varchar(100),
847 `comments` mediumtext,
848 PRIMARY KEY (`import_batch_id`),
849 KEY `branchcode` (`branchcode`)
850 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
853 -- Table structure for table `import_records`
856 DROP TABLE IF EXISTS `import_records`;
857 CREATE TABLE `import_records` (
858 `import_record_id` int(11) NOT NULL auto_increment,
859 `import_batch_id` int(11) NOT NULL,
860 `branchcode` varchar(10) default NULL,
861 `record_sequence` int(11) NOT NULL default 0,
862 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
863 `import_date` DATE default NULL,
864 `marc` longblob NOT NULL,
865 `marcxml` longtext NOT NULL,
866 `marcxml_old` longtext NOT NULL,
867 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
868 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
869 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
870 `import_error` mediumtext,
871 `encoding` varchar(40) NOT NULL default '',
872 `z3950random` varchar(40) default NULL,
873 PRIMARY KEY (`import_record_id`),
874 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
875 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
876 KEY `branchcode` (`branchcode`),
877 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
878 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
881 -- Table structure for `import_record_matches`
883 DROP TABLE IF EXISTS `import_record_matches`;
884 CREATE TABLE `import_record_matches` (
885 `import_record_id` int(11) NOT NULL,
886 `candidate_match_id` int(11) NOT NULL,
887 `score` int(11) NOT NULL default 0,
888 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
889 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
890 KEY `record_score` (`import_record_id`, `score`)
891 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
894 -- Table structure for table `import_biblios`
897 DROP TABLE IF EXISTS `import_biblios`;
898 CREATE TABLE `import_biblios` (
899 `import_record_id` int(11) NOT NULL,
900 `matched_biblionumber` int(11) default NULL,
901 `control_number` varchar(25) default NULL,
902 `original_source` varchar(25) default NULL,
903 `title` varchar(128) default NULL,
904 `author` varchar(80) default NULL,
905 `isbn` varchar(30) default NULL,
906 `issn` varchar(9) default NULL,
907 `has_items` tinyint(1) NOT NULL default 0,
908 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
909 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
910 KEY `matched_biblionumber` (`matched_biblionumber`),
911 KEY `title` (`title`),
913 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
916 -- Table structure for table `import_items`
919 DROP TABLE IF EXISTS `import_items`;
920 CREATE TABLE `import_items` (
921 `import_items_id` int(11) NOT NULL auto_increment,
922 `import_record_id` int(11) NOT NULL,
923 `itemnumber` int(11) default NULL,
924 `branchcode` varchar(10) default NULL,
925 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
926 `marcxml` longtext NOT NULL,
927 `import_error` mediumtext,
928 PRIMARY KEY (`import_items_id`),
929 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
930 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
931 KEY `itemnumber` (`itemnumber`),
932 KEY `branchcode` (`branchcode`)
933 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
936 -- Table structure for table `issues`
939 DROP TABLE IF EXISTS `issues`;
940 CREATE TABLE `issues` (
941 `borrowernumber` int(11),
942 `itemnumber` int(11),
943 `date_due` date default NULL,
944 `branchcode` varchar(10) default NULL,
945 `issuingbranch` varchar(18) default NULL,
946 `returndate` date default NULL,
947 `lastreneweddate` date default NULL,
948 `return` varchar(4) default NULL,
949 `renewals` tinyint(4) default NULL,
950 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
951 `issuedate` date default NULL,
952 PRIMARY KEY (`itemnumber`),
953 KEY `issuesborridx` (`borrowernumber`),
954 KEY `bordate` (`borrowernumber`,`timestamp`),
955 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
956 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
957 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
960 -- Table structure for table `issuingrules`
963 DROP TABLE IF EXISTS `issuingrules`;
964 CREATE TABLE `issuingrules` (
965 `categorycode` varchar(10) NOT NULL default '',
966 `itemtype` varchar(10) NOT NULL default '',
967 `restrictedtype` tinyint(1) default NULL,
968 `rentaldiscount` decimal(28,6) default NULL,
969 `reservecharge` decimal(28,6) default NULL,
970 `fine` decimal(28,6) default NULL,
971 `finedays` int(11) default NULL,
972 `firstremind` int(11) default NULL,
973 `chargeperiod` int(11) default NULL,
974 `accountsent` int(11) default NULL,
975 `chargename` varchar(100) default NULL,
976 `maxissueqty` int(4) default NULL,
977 `issuelength` int(4) default NULL,
978 `hardduedate` date default NULL,
979 `hardduedatecompare` tinyint NOT NULL default "0",
980 `renewalsallowed` smallint(6) NOT NULL default "0",
981 `reservesallowed` smallint(6) NOT NULL default "0",
982 `branchcode` varchar(10) NOT NULL default '',
983 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
984 KEY `categorycode` (`categorycode`),
985 KEY `itemtype` (`itemtype`)
986 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
989 -- Table structure for table `items`
992 DROP TABLE IF EXISTS `items`;
993 CREATE TABLE `items` (
994 `itemnumber` int(11) NOT NULL auto_increment,
995 `biblionumber` int(11) NOT NULL default 0,
996 `biblioitemnumber` int(11) NOT NULL default 0,
997 `barcode` varchar(20) default NULL,
998 `dateaccessioned` date default NULL,
999 `booksellerid` mediumtext default NULL,
1000 `homebranch` varchar(10) default NULL,
1001 `price` decimal(8,2) default NULL,
1002 `replacementprice` decimal(8,2) default NULL,
1003 `replacementpricedate` date default NULL,
1004 `datelastborrowed` date default NULL,
1005 `datelastseen` date default NULL,
1006 `stack` tinyint(1) default NULL,
1007 `notforloan` tinyint(1) NOT NULL default 0,
1008 `damaged` tinyint(1) NOT NULL default 0,
1009 `itemlost` tinyint(1) NOT NULL default 0,
1010 `wthdrawn` tinyint(1) NOT NULL default 0,
1011 `itemcallnumber` varchar(255) default NULL,
1012 `issues` smallint(6) default NULL,
1013 `renewals` smallint(6) default NULL,
1014 `reserves` smallint(6) default NULL,
1015 `restricted` tinyint(1) default NULL,
1016 `itemnotes` mediumtext,
1017 `holdingbranch` varchar(10) default NULL,
1018 `paidfor` mediumtext,
1019 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1020 `location` varchar(80) default NULL,
1021 `permanent_location` varchar(80) default NULL,
1022 `onloan` date default NULL,
1023 `cn_source` varchar(10) default NULL,
1024 `cn_sort` varchar(30) default NULL,
1025 `ccode` varchar(10) default NULL,
1026 `materials` varchar(10) default NULL,
1027 `uri` varchar(255) default NULL,
1028 `itype` varchar(10) default NULL,
1029 `more_subfields_xml` longtext default NULL,
1030 `enumchron` text default NULL,
1031 `copynumber` varchar(32) default NULL,
1032 `stocknumber` varchar(32) default NULL,
1033 PRIMARY KEY (`itemnumber`),
1034 UNIQUE KEY `itembarcodeidx` (`barcode`),
1035 KEY `itemstocknumberidx` (`stocknumber`),
1036 KEY `itembinoidx` (`biblioitemnumber`),
1037 KEY `itembibnoidx` (`biblionumber`),
1038 KEY `homebranch` (`homebranch`),
1039 KEY `holdingbranch` (`holdingbranch`),
1040 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1041 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1042 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1043 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1046 -- Table structure for table `itemtypes`
1049 DROP TABLE IF EXISTS `itemtypes`;
1050 CREATE TABLE `itemtypes` (
1051 `itemtype` varchar(10) NOT NULL default '',
1052 `description` mediumtext,
1053 `rentalcharge` double(16,4) default NULL,
1054 `notforloan` smallint(6) default NULL,
1055 `imageurl` varchar(200) default NULL,
1057 PRIMARY KEY (`itemtype`),
1058 UNIQUE KEY `itemtype` (`itemtype`)
1059 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1062 -- Table structure for table `creator_batches`
1065 DROP TABLE IF EXISTS `creator_batches`;
1066 SET @saved_cs_client = @@character_set_client;
1067 SET character_set_client = utf8;
1068 CREATE TABLE `creator_batches` (
1069 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1070 `batch_id` int(10) NOT NULL DEFAULT '1',
1071 `item_number` int(11) DEFAULT NULL,
1072 `borrower_number` int(11) DEFAULT NULL,
1073 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1074 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1075 `creator` char(15) NOT NULL DEFAULT 'Labels',
1076 PRIMARY KEY (`label_id`),
1077 KEY `branch_fk_constraint` (`branch_code`),
1078 KEY `item_fk_constraint` (`item_number`),
1079 KEY `borrower_fk_constraint` (`borrower_number`),
1080 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1081 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1082 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1083 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1086 -- Table structure for table `creator_images`
1089 DROP TABLE IF EXISTS `creator_images`;
1090 SET @saved_cs_client = @@character_set_client;
1091 SET character_set_client = utf8;
1092 CREATE TABLE `creator_images` (
1093 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1094 `imagefile` mediumblob,
1095 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1096 PRIMARY KEY (`image_id`),
1097 UNIQUE KEY `image_name_index` (`image_name`)
1098 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1101 -- Table structure for table `creator_layouts`
1104 DROP TABLE IF EXISTS `creator_layouts`;
1105 SET @saved_cs_client = @@character_set_client;
1106 SET character_set_client = utf8;
1107 CREATE TABLE `creator_layouts` (
1108 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1109 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1110 `start_label` int(2) NOT NULL DEFAULT '1',
1111 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1112 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1113 `guidebox` int(1) DEFAULT '0',
1114 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1115 `font_size` int(4) NOT NULL DEFAULT '10',
1116 `units` char(20) NOT NULL DEFAULT 'POINT',
1117 `callnum_split` int(1) DEFAULT '0',
1118 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1119 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1120 `layout_xml` text NOT NULL,
1121 `creator` char(15) NOT NULL DEFAULT 'Labels',
1122 PRIMARY KEY (`layout_id`)
1123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1126 -- Table structure for table `creator_templates`
1129 DROP TABLE IF EXISTS `creator_templates`;
1130 SET @saved_cs_client = @@character_set_client;
1131 SET character_set_client = utf8;
1132 CREATE TABLE `creator_templates` (
1133 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1134 `profile_id` int(4) DEFAULT NULL,
1135 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1136 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1137 `page_width` float NOT NULL DEFAULT '0',
1138 `page_height` float NOT NULL DEFAULT '0',
1139 `label_width` float NOT NULL DEFAULT '0',
1140 `label_height` float NOT NULL DEFAULT '0',
1141 `top_text_margin` float NOT NULL DEFAULT '0',
1142 `left_text_margin` float NOT NULL DEFAULT '0',
1143 `top_margin` float NOT NULL DEFAULT '0',
1144 `left_margin` float NOT NULL DEFAULT '0',
1145 `cols` int(2) NOT NULL DEFAULT '0',
1146 `rows` int(2) NOT NULL DEFAULT '0',
1147 `col_gap` float NOT NULL DEFAULT '0',
1148 `row_gap` float NOT NULL DEFAULT '0',
1149 `units` char(20) NOT NULL DEFAULT 'POINT',
1150 `creator` char(15) NOT NULL DEFAULT 'Labels',
1151 PRIMARY KEY (`template_id`),
1152 KEY `template_profile_fk_constraint` (`profile_id`)
1153 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1156 -- Table structure for table `letter`
1159 DROP TABLE IF EXISTS `letter`;
1160 CREATE TABLE `letter` (
1161 `module` varchar(20) NOT NULL default '',
1162 `code` varchar(20) NOT NULL default '',
1163 `name` varchar(100) NOT NULL default '',
1164 `title` varchar(200) NOT NULL default '',
1166 PRIMARY KEY (`module`,`code`)
1167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1170 -- Table structure for table `marc_subfield_structure`
1173 DROP TABLE IF EXISTS `marc_subfield_structure`;
1174 CREATE TABLE `marc_subfield_structure` (
1175 `tagfield` varchar(3) NOT NULL default '',
1176 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1177 `liblibrarian` varchar(255) NOT NULL default '',
1178 `libopac` varchar(255) NOT NULL default '',
1179 `repeatable` tinyint(4) NOT NULL default 0,
1180 `mandatory` tinyint(4) NOT NULL default 0,
1181 `kohafield` varchar(40) default NULL,
1182 `tab` tinyint(1) default NULL,
1183 `authorised_value` varchar(20) default NULL,
1184 `authtypecode` varchar(20) default NULL,
1185 `value_builder` varchar(80) default NULL,
1186 `isurl` tinyint(1) default NULL,
1187 `hidden` tinyint(1) default NULL,
1188 `frameworkcode` varchar(4) NOT NULL default '',
1189 `seealso` varchar(1100) default NULL,
1190 `link` varchar(80) default NULL,
1191 `defaultvalue` text default NULL,
1192 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1193 KEY `kohafield_2` (`kohafield`),
1194 KEY `tab` (`frameworkcode`,`tab`),
1195 KEY `kohafield` (`frameworkcode`,`kohafield`)
1196 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1199 -- Table structure for table `marc_tag_structure`
1202 DROP TABLE IF EXISTS `marc_tag_structure`;
1203 CREATE TABLE `marc_tag_structure` (
1204 `tagfield` varchar(3) NOT NULL default '',
1205 `liblibrarian` varchar(255) NOT NULL default '',
1206 `libopac` varchar(255) NOT NULL default '',
1207 `repeatable` tinyint(4) NOT NULL default 0,
1208 `mandatory` tinyint(4) NOT NULL default 0,
1209 `authorised_value` varchar(10) default NULL,
1210 `frameworkcode` varchar(4) NOT NULL default '',
1211 PRIMARY KEY (`frameworkcode`,`tagfield`)
1212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1215 -- Table structure for table `marc_matchers`
1218 DROP TABLE IF EXISTS `marc_matchers`;
1219 CREATE TABLE `marc_matchers` (
1220 `matcher_id` int(11) NOT NULL auto_increment,
1221 `code` varchar(10) NOT NULL default '',
1222 `description` varchar(255) NOT NULL default '',
1223 `record_type` varchar(10) NOT NULL default 'biblio',
1224 `threshold` int(11) NOT NULL default 0,
1225 PRIMARY KEY (`matcher_id`),
1226 KEY `code` (`code`),
1227 KEY `record_type` (`record_type`)
1228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1231 -- Table structure for table `matchpoints`
1233 DROP TABLE IF EXISTS `matchpoints`;
1234 CREATE TABLE `matchpoints` (
1235 `matcher_id` int(11) NOT NULL,
1236 `matchpoint_id` int(11) NOT NULL auto_increment,
1237 `search_index` varchar(30) NOT NULL default '',
1238 `score` int(11) NOT NULL default 0,
1239 PRIMARY KEY (`matchpoint_id`),
1240 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1241 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1246 -- Table structure for table `matchpoint_components`
1248 DROP TABLE IF EXISTS `matchpoint_components`;
1249 CREATE TABLE `matchpoint_components` (
1250 `matchpoint_id` int(11) NOT NULL,
1251 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1252 sequence int(11) NOT NULL default 0,
1253 tag varchar(3) NOT NULL default '',
1254 subfields varchar(40) NOT NULL default '',
1255 offset int(4) NOT NULL default 0,
1256 length int(4) NOT NULL default 0,
1257 PRIMARY KEY (`matchpoint_component_id`),
1258 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1259 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1260 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1261 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1264 -- Table structure for table `matcher_component_norms`
1266 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1267 CREATE TABLE `matchpoint_component_norms` (
1268 `matchpoint_component_id` int(11) NOT NULL,
1269 `sequence` int(11) NOT NULL default 0,
1270 `norm_routine` varchar(50) NOT NULL default '',
1271 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1272 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1273 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1277 -- Table structure for table `matcher_matchpoints`
1279 DROP TABLE IF EXISTS `matcher_matchpoints`;
1280 CREATE TABLE `matcher_matchpoints` (
1281 `matcher_id` int(11) NOT NULL,
1282 `matchpoint_id` int(11) NOT NULL,
1283 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1284 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1285 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1286 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1287 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1290 -- Table structure for table `matchchecks`
1292 DROP TABLE IF EXISTS `matchchecks`;
1293 CREATE TABLE `matchchecks` (
1294 `matcher_id` int(11) NOT NULL,
1295 `matchcheck_id` int(11) NOT NULL auto_increment,
1296 `source_matchpoint_id` int(11) NOT NULL,
1297 `target_matchpoint_id` int(11) NOT NULL,
1298 PRIMARY KEY (`matchcheck_id`),
1299 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1300 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1301 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1302 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1303 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1304 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1305 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1308 -- Table structure for table `notifys`
1311 DROP TABLE IF EXISTS `notifys`;
1312 CREATE TABLE `notifys` (
1313 `notify_id` int(11) NOT NULL default 0,
1314 `borrowernumber` int(11) NOT NULL default 0,
1315 `itemnumber` int(11) NOT NULL default 0,
1316 `notify_date` date default NULL,
1317 `notify_send_date` date default NULL,
1318 `notify_level` int(1) NOT NULL default 0,
1319 `method` varchar(20) NOT NULL default ''
1320 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1323 -- Table structure for table `nozebra`
1326 DROP TABLE IF EXISTS `nozebra`;
1327 CREATE TABLE `nozebra` (
1328 `server` varchar(20) NOT NULL,
1329 `indexname` varchar(40) NOT NULL,
1330 `value` varchar(250) NOT NULL,
1331 `biblionumbers` longtext NOT NULL,
1332 KEY `indexname` (`server`,`indexname`),
1333 KEY `value` (`server`,`value`))
1334 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1337 -- Table structure for table `old_issues`
1340 DROP TABLE IF EXISTS `old_issues`;
1341 CREATE TABLE `old_issues` (
1342 `borrowernumber` int(11) default NULL,
1343 `itemnumber` int(11) default NULL,
1344 `date_due` date default NULL,
1345 `branchcode` varchar(10) default NULL,
1346 `issuingbranch` varchar(18) default NULL,
1347 `returndate` date default NULL,
1348 `lastreneweddate` date default NULL,
1349 `return` varchar(4) default NULL,
1350 `renewals` tinyint(4) default NULL,
1351 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1352 `issuedate` date default NULL,
1353 KEY `old_issuesborridx` (`borrowernumber`),
1354 KEY `old_issuesitemidx` (`itemnumber`),
1355 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1356 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1357 ON DELETE SET NULL ON UPDATE SET NULL,
1358 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1359 ON DELETE SET NULL ON UPDATE SET NULL
1360 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1363 -- Table structure for table `old_reserves`
1365 DROP TABLE IF EXISTS `old_reserves`;
1366 CREATE TABLE `old_reserves` (
1367 `borrowernumber` int(11) default NULL,
1368 `reservedate` date default NULL,
1369 `biblionumber` int(11) default NULL,
1370 `constrainttype` varchar(1) default NULL,
1371 `branchcode` varchar(10) default NULL,
1372 `notificationdate` date default NULL,
1373 `reminderdate` date default NULL,
1374 `cancellationdate` date default NULL,
1375 `reservenotes` mediumtext,
1376 `priority` smallint(6) default NULL,
1377 `found` varchar(1) default NULL,
1378 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1379 `itemnumber` int(11) default NULL,
1380 `waitingdate` date default NULL,
1381 `expirationdate` DATE DEFAULT NULL,
1382 `lowestPriority` tinyint(1) NOT NULL,
1383 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1384 KEY `old_reserves_biblionumber` (`biblionumber`),
1385 KEY `old_reserves_itemnumber` (`itemnumber`),
1386 KEY `old_reserves_branchcode` (`branchcode`),
1387 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1388 ON DELETE SET NULL ON UPDATE SET NULL,
1389 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1390 ON DELETE SET NULL ON UPDATE SET NULL,
1391 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1392 ON DELETE SET NULL ON UPDATE SET NULL
1393 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1396 -- Table structure for table `opac_news`
1399 DROP TABLE IF EXISTS `opac_news`;
1400 CREATE TABLE `opac_news` (
1401 `idnew` int(10) unsigned NOT NULL auto_increment,
1402 `title` varchar(250) NOT NULL default '',
1403 `new` text NOT NULL,
1404 `lang` varchar(25) NOT NULL default '',
1405 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1406 `expirationdate` date default NULL,
1407 `number` int(11) default NULL,
1408 PRIMARY KEY (`idnew`)
1409 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1412 -- Table structure for table `overduerules`
1415 DROP TABLE IF EXISTS `overduerules`;
1416 CREATE TABLE `overduerules` (
1417 `branchcode` varchar(10) NOT NULL default '',
1418 `categorycode` varchar(10) NOT NULL default '',
1419 `delay1` int(4) default NULL,
1420 `letter1` varchar(20) default NULL,
1421 `debarred1` varchar(1) default 0,
1422 `delay2` int(4) default NULL,
1423 `debarred2` varchar(1) default 0,
1424 `letter2` varchar(20) default NULL,
1425 `delay3` int(4) default NULL,
1426 `letter3` varchar(20) default NULL,
1427 `debarred3` int(1) default 0,
1428 PRIMARY KEY (`branchcode`,`categorycode`)
1429 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1432 -- Table structure for table `patroncards`
1435 DROP TABLE IF EXISTS `patroncards`;
1436 CREATE TABLE `patroncards` (
1437 `cardid` int(11) NOT NULL auto_increment,
1438 `batch_id` varchar(10) NOT NULL default '1',
1439 `borrowernumber` int(11) NOT NULL,
1440 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1441 PRIMARY KEY (`cardid`),
1442 KEY `patroncards_ibfk_1` (`borrowernumber`),
1443 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1444 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1447 -- Table structure for table `patronimage`
1450 DROP TABLE IF EXISTS `patronimage`;
1451 CREATE TABLE `patronimage` (
1452 `cardnumber` varchar(16) NOT NULL,
1453 `mimetype` varchar(15) NOT NULL,
1454 `imagefile` mediumblob NOT NULL,
1455 PRIMARY KEY (`cardnumber`),
1456 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1457 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1460 -- Table structure for table `printers`
1463 DROP TABLE IF EXISTS `printers`;
1464 CREATE TABLE `printers` (
1465 `printername` varchar(40) NOT NULL default '',
1466 `printqueue` varchar(20) default NULL,
1467 `printtype` varchar(20) default NULL,
1468 PRIMARY KEY (`printername`)
1469 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1472 -- Table structure for table `printers_profile`
1475 DROP TABLE IF EXISTS `printers_profile`;
1476 CREATE TABLE `printers_profile` (
1477 `profile_id` int(4) NOT NULL auto_increment,
1478 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1479 `template_id` int(4) NOT NULL default '0',
1480 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1481 `offset_horz` float NOT NULL default '0',
1482 `offset_vert` float NOT NULL default '0',
1483 `creep_horz` float NOT NULL default '0',
1484 `creep_vert` float NOT NULL default '0',
1485 `units` char(20) NOT NULL default 'POINT',
1486 `creator` char(15) NOT NULL DEFAULT 'Labels',
1487 PRIMARY KEY (`profile_id`),
1488 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
1489 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1492 -- Table structure for table `repeatable_holidays`
1495 DROP TABLE IF EXISTS `repeatable_holidays`;
1496 CREATE TABLE `repeatable_holidays` (
1497 `id` int(11) NOT NULL auto_increment,
1498 `branchcode` varchar(10) NOT NULL default '',
1499 `weekday` smallint(6) default NULL,
1500 `day` smallint(6) default NULL,
1501 `month` smallint(6) default NULL,
1502 `title` varchar(50) NOT NULL default '',
1503 `description` text NOT NULL,
1505 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1508 -- Table structure for table `reports_dictionary`
1511 DROP TABLE IF EXISTS `reports_dictionary`;
1512 CREATE TABLE reports_dictionary (
1513 `id` int(11) NOT NULL auto_increment,
1514 `name` varchar(255) default NULL,
1516 `date_created` datetime default NULL,
1517 `date_modified` datetime default NULL,
1519 `area` int(11) default NULL,
1521 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1524 -- Table structure for table `reserveconstraints`
1527 DROP TABLE IF EXISTS `reserveconstraints`;
1528 CREATE TABLE `reserveconstraints` (
1529 `borrowernumber` int(11) NOT NULL default 0,
1530 `reservedate` date default NULL,
1531 `biblionumber` int(11) NOT NULL default 0,
1532 `biblioitemnumber` int(11) default NULL,
1533 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1534 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1537 -- Table structure for table `reserves`
1540 DROP TABLE IF EXISTS `reserves`;
1541 CREATE TABLE `reserves` (
1542 `borrowernumber` int(11) NOT NULL default 0,
1543 `reservedate` date default NULL,
1544 `biblionumber` int(11) NOT NULL default 0,
1545 `constrainttype` varchar(1) default NULL,
1546 `branchcode` varchar(10) default NULL,
1547 `notificationdate` date default NULL,
1548 `reminderdate` date default NULL,
1549 `cancellationdate` date default NULL,
1550 `reservenotes` mediumtext,
1551 `priority` smallint(6) default NULL,
1552 `found` varchar(1) default NULL,
1553 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1554 `itemnumber` int(11) default NULL,
1555 `waitingdate` date default NULL,
1556 `expirationdate` DATE DEFAULT NULL,
1557 `lowestPriority` tinyint(1) NOT NULL,
1558 KEY priorityfoundidx (priority,found),
1559 KEY `borrowernumber` (`borrowernumber`),
1560 KEY `biblionumber` (`biblionumber`),
1561 KEY `itemnumber` (`itemnumber`),
1562 KEY `branchcode` (`branchcode`),
1563 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1564 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1565 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1566 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1567 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1570 -- Table structure for table `reviews`
1573 DROP TABLE IF EXISTS `reviews`;
1574 CREATE TABLE `reviews` (
1575 `reviewid` int(11) NOT NULL auto_increment,
1576 `borrowernumber` int(11) default NULL,
1577 `biblionumber` int(11) default NULL,
1579 `approved` tinyint(4) default NULL,
1580 `datereviewed` datetime default NULL,
1581 PRIMARY KEY (`reviewid`)
1582 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1585 -- Table structure for table `roadtype`
1588 DROP TABLE IF EXISTS `roadtype`;
1589 CREATE TABLE `roadtype` (
1590 `roadtypeid` int(11) NOT NULL auto_increment,
1591 `road_type` varchar(100) NOT NULL default '',
1592 PRIMARY KEY (`roadtypeid`)
1593 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1596 -- Table structure for table `saved_sql`
1599 DROP TABLE IF EXISTS `saved_sql`;
1600 CREATE TABLE saved_sql (
1601 `id` int(11) NOT NULL auto_increment,
1602 `borrowernumber` int(11) default NULL,
1603 `date_created` datetime default NULL,
1604 `last_modified` datetime default NULL,
1606 `last_run` datetime default NULL,
1607 `report_name` varchar(255) default NULL,
1608 `type` varchar(255) default NULL,
1611 KEY boridx (`borrowernumber`)
1612 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1616 -- Table structure for `saved_reports`
1619 DROP TABLE IF EXISTS `saved_reports`;
1620 CREATE TABLE saved_reports (
1621 `id` int(11) NOT NULL auto_increment,
1622 `report_id` int(11) default NULL,
1624 `date_run` datetime default NULL,
1626 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1630 -- Table structure for table `search_history`
1633 DROP TABLE IF EXISTS `search_history`;
1634 CREATE TABLE IF NOT EXISTS `search_history` (
1635 `userid` int(11) NOT NULL,
1636 `sessionid` varchar(32) NOT NULL,
1637 `query_desc` varchar(255) NOT NULL,
1638 `query_cgi` varchar(255) NOT NULL,
1639 `total` int(11) NOT NULL,
1640 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1641 KEY `userid` (`userid`),
1642 KEY `sessionid` (`sessionid`)
1643 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1647 -- Table structure for table `serial`
1650 DROP TABLE IF EXISTS `serial`;
1651 CREATE TABLE `serial` (
1652 `serialid` int(11) NOT NULL auto_increment,
1653 `biblionumber` varchar(100) NOT NULL default '',
1654 `subscriptionid` varchar(100) NOT NULL default '',
1655 `serialseq` varchar(100) NOT NULL default '',
1656 `status` tinyint(4) NOT NULL default 0,
1657 `planneddate` date default NULL,
1659 `publisheddate` date default NULL,
1660 `itemnumber` text default NULL,
1661 `claimdate` date default NULL,
1662 `routingnotes` text,
1663 PRIMARY KEY (`serialid`)
1664 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1667 -- Table structure for table `sessions`
1670 DROP TABLE IF EXISTS sessions;
1671 CREATE TABLE sessions (
1672 `id` varchar(32) NOT NULL,
1673 `a_session` text NOT NULL,
1675 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1678 -- Table structure for table `special_holidays`
1681 DROP TABLE IF EXISTS `special_holidays`;
1682 CREATE TABLE `special_holidays` (
1683 `id` int(11) NOT NULL auto_increment,
1684 `branchcode` varchar(10) NOT NULL default '',
1685 `day` smallint(6) NOT NULL default 0,
1686 `month` smallint(6) NOT NULL default 0,
1687 `year` smallint(6) NOT NULL default 0,
1688 `isexception` smallint(1) NOT NULL default 1,
1689 `title` varchar(50) NOT NULL default '',
1690 `description` text NOT NULL,
1692 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1695 -- Table structure for table `statistics`
1698 DROP TABLE IF EXISTS `statistics`;
1699 CREATE TABLE `statistics` (
1700 `datetime` datetime default NULL,
1701 `branch` varchar(10) default NULL,
1702 `proccode` varchar(4) default NULL,
1703 `value` double(16,4) default NULL,
1704 `type` varchar(16) default NULL,
1706 `usercode` varchar(10) default NULL,
1707 `itemnumber` int(11) default NULL,
1708 `itemtype` varchar(10) default NULL,
1709 `borrowernumber` int(11) default NULL,
1710 `associatedborrower` int(11) default NULL,
1711 KEY `timeidx` (`datetime`)
1712 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1715 -- Table structure for table `stopwords`
1718 DROP TABLE IF EXISTS `stopwords`;
1719 CREATE TABLE `stopwords` (
1720 `word` varchar(255) default NULL
1721 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1724 -- Table structure for table `subscription`
1727 DROP TABLE IF EXISTS `subscription`;
1728 CREATE TABLE `subscription` (
1729 `biblionumber` int(11) NOT NULL default 0,
1730 `subscriptionid` int(11) NOT NULL auto_increment,
1731 `librarian` varchar(100) default '',
1732 `startdate` date default NULL,
1733 `aqbooksellerid` int(11) default 0,
1734 `cost` int(11) default 0,
1735 `aqbudgetid` int(11) default 0,
1736 `weeklength` int(11) default 0,
1737 `monthlength` int(11) default 0,
1738 `numberlength` int(11) default 0,
1739 `periodicity` tinyint(4) default 0,
1740 `dow` varchar(100) default '',
1741 `numberingmethod` varchar(100) default '',
1743 `status` varchar(100) NOT NULL default '',
1744 `add1` int(11) default 0,
1745 `every1` int(11) default 0,
1746 `whenmorethan1` int(11) default 0,
1747 `setto1` int(11) default NULL,
1748 `lastvalue1` int(11) default NULL,
1749 `add2` int(11) default 0,
1750 `every2` int(11) default 0,
1751 `whenmorethan2` int(11) default 0,
1752 `setto2` int(11) default NULL,
1753 `lastvalue2` int(11) default NULL,
1754 `add3` int(11) default 0,
1755 `every3` int(11) default 0,
1756 `innerloop1` int(11) default 0,
1757 `innerloop2` int(11) default 0,
1758 `innerloop3` int(11) default 0,
1759 `whenmorethan3` int(11) default 0,
1760 `setto3` int(11) default NULL,
1761 `lastvalue3` int(11) default NULL,
1762 `issuesatonce` tinyint(3) NOT NULL default 1,
1763 `firstacquidate` date default NULL,
1764 `manualhistory` tinyint(1) NOT NULL default 0,
1765 `irregularity` text,
1766 `letter` varchar(20) default NULL,
1767 `numberpattern` tinyint(3) default 0,
1768 `distributedto` text,
1769 `internalnotes` longtext,
1771 `location` varchar(80) NULL default '',
1772 `branchcode` varchar(10) NOT NULL default '',
1773 `hemisphere` tinyint(3) default 0,
1774 `lastbranch` varchar(10),
1775 `serialsadditems` tinyint(1) NOT NULL default '0',
1776 `staffdisplaycount` VARCHAR(10) NULL,
1777 `opacdisplaycount` VARCHAR(10) NULL,
1778 `graceperiod` int(11) NOT NULL default '0',
1779 `enddate` date default NULL,
1780 PRIMARY KEY (`subscriptionid`)
1781 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1784 -- Table structure for table `subscriptionhistory`
1787 DROP TABLE IF EXISTS `subscriptionhistory`;
1788 CREATE TABLE `subscriptionhistory` (
1789 `biblionumber` int(11) NOT NULL default 0,
1790 `subscriptionid` int(11) NOT NULL default 0,
1791 `histstartdate` date default NULL,
1792 `histenddate` date default NULL,
1793 `missinglist` longtext NOT NULL,
1794 `recievedlist` longtext NOT NULL,
1795 `opacnote` varchar(150) NOT NULL default '',
1796 `librariannote` varchar(150) NOT NULL default '',
1797 PRIMARY KEY (`subscriptionid`),
1798 KEY `biblionumber` (`biblionumber`)
1799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1802 -- Table structure for table `subscriptionroutinglist`
1805 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1806 CREATE TABLE `subscriptionroutinglist` (
1807 `routingid` int(11) NOT NULL auto_increment,
1808 `borrowernumber` int(11) NOT NULL,
1809 `ranking` int(11) default NULL,
1810 `subscriptionid` int(11) NOT NULL,
1811 PRIMARY KEY (`routingid`),
1812 UNIQUE (`subscriptionid`, `borrowernumber`),
1813 CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1814 ON DELETE CASCADE ON UPDATE CASCADE,
1815 CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
1816 ON DELETE CASCADE ON UPDATE CASCADE
1817 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1820 -- Table structure for table `suggestions`
1823 DROP TABLE IF EXISTS `suggestions`;
1824 CREATE TABLE `suggestions` (
1825 `suggestionid` int(8) NOT NULL auto_increment,
1826 `suggestedby` int(11) NOT NULL default 0,
1827 `suggesteddate` date NOT NULL default 0,
1828 `managedby` int(11) default NULL,
1829 `manageddate` date default NULL,
1830 acceptedby INT(11) default NULL,
1831 accepteddate date default NULL,
1832 rejectedby INT(11) default NULL,
1833 rejecteddate date default NULL,
1834 `STATUS` varchar(10) NOT NULL default '',
1836 `author` varchar(80) default NULL,
1837 `title` varchar(80) default NULL,
1838 `copyrightdate` smallint(6) default NULL,
1839 `publishercode` varchar(255) default NULL,
1840 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1841 `volumedesc` varchar(255) default NULL,
1842 `publicationyear` smallint(6) default 0,
1843 `place` varchar(255) default NULL,
1844 `isbn` varchar(30) default NULL,
1845 `mailoverseeing` smallint(1) default 0,
1846 `biblionumber` int(11) default NULL,
1849 branchcode VARCHAR(10) default NULL,
1850 collectiontitle text default NULL,
1851 itemtype VARCHAR(30) default NULL,
1852 quantity SMALLINT(6) default NULL,
1853 currency VARCHAR(3) default NULL,
1854 price DECIMAL(28,6) default NULL,
1855 total DECIMAL(28,6) default NULL,
1856 PRIMARY KEY (`suggestionid`),
1857 KEY `suggestedby` (`suggestedby`),
1858 KEY `managedby` (`managedby`)
1859 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1862 -- Table structure for table `systempreferences`
1865 DROP TABLE IF EXISTS `systempreferences`;
1866 CREATE TABLE `systempreferences` (
1867 `variable` varchar(50) NOT NULL default '',
1869 `options` mediumtext,
1871 `type` varchar(20) default NULL,
1872 PRIMARY KEY (`variable`)
1873 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1876 -- Table structure for table `tags`
1879 DROP TABLE IF EXISTS `tags`;
1880 CREATE TABLE `tags` (
1881 `entry` varchar(255) NOT NULL default '',
1882 `weight` bigint(20) NOT NULL default 0,
1883 PRIMARY KEY (`entry`)
1884 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1887 -- Table structure for table `tags_all`
1890 DROP TABLE IF EXISTS `tags_all`;
1891 CREATE TABLE `tags_all` (
1892 `tag_id` int(11) NOT NULL auto_increment,
1893 `borrowernumber` int(11) NOT NULL,
1894 `biblionumber` int(11) NOT NULL,
1895 `term` varchar(255) NOT NULL,
1896 `language` int(4) default NULL,
1897 `date_created` datetime NOT NULL,
1898 PRIMARY KEY (`tag_id`),
1899 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1900 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1901 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1902 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1903 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1904 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1905 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1908 -- Table structure for table `tags_approval`
1911 DROP TABLE IF EXISTS `tags_approval`;
1912 CREATE TABLE `tags_approval` (
1913 `term` varchar(255) NOT NULL,
1914 `approved` int(1) NOT NULL default '0',
1915 `date_approved` datetime default NULL,
1916 `approved_by` int(11) default NULL,
1917 `weight_total` int(9) NOT NULL default '1',
1918 PRIMARY KEY (`term`),
1919 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1920 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1921 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1922 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1925 -- Table structure for table `tags_index`
1928 DROP TABLE IF EXISTS `tags_index`;
1929 CREATE TABLE `tags_index` (
1930 `term` varchar(255) NOT NULL,
1931 `biblionumber` int(11) NOT NULL,
1932 `weight` int(9) NOT NULL default '1',
1933 PRIMARY KEY (`term`,`biblionumber`),
1934 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1935 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1936 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1937 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1938 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1939 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1942 -- Table structure for table `userflags`
1945 DROP TABLE IF EXISTS `userflags`;
1946 CREATE TABLE `userflags` (
1947 `bit` int(11) NOT NULL default 0,
1948 `flag` varchar(30) default NULL,
1949 `flagdesc` varchar(255) default NULL,
1950 `defaulton` int(11) default NULL,
1952 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1955 -- Table structure for table `virtualshelves`
1958 DROP TABLE IF EXISTS `virtualshelves`;
1959 CREATE TABLE `virtualshelves` (
1960 `shelfnumber` int(11) NOT NULL auto_increment,
1961 `shelfname` varchar(255) default NULL,
1962 `owner` varchar(80) default NULL,
1963 `category` varchar(1) default NULL,
1964 `sortfield` varchar(16) default NULL,
1965 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1966 PRIMARY KEY (`shelfnumber`)
1967 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1970 -- Table structure for table `virtualshelfcontents`
1973 DROP TABLE IF EXISTS `virtualshelfcontents`;
1974 CREATE TABLE `virtualshelfcontents` (
1975 `shelfnumber` int(11) NOT NULL default 0,
1976 `biblionumber` int(11) NOT NULL default 0,
1977 `flags` int(11) default NULL,
1978 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1979 KEY `shelfnumber` (`shelfnumber`),
1980 KEY `biblionumber` (`biblionumber`),
1981 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1982 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1983 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1986 -- Table structure for table `z3950servers`
1989 DROP TABLE IF EXISTS `z3950servers`;
1990 CREATE TABLE `z3950servers` (
1991 `host` varchar(255) default NULL,
1992 `port` int(11) default NULL,
1993 `db` varchar(255) default NULL,
1994 `userid` varchar(255) default NULL,
1995 `password` varchar(255) default NULL,
1997 `id` int(11) NOT NULL auto_increment,
1998 `checked` smallint(6) default NULL,
1999 `rank` int(11) default NULL,
2000 `syntax` varchar(80) default NULL,
2002 `position` enum('primary','secondary','') NOT NULL default 'primary',
2003 `type` enum('zed','opensearch') NOT NULL default 'zed',
2004 `encoding` text default NULL,
2005 `description` text NOT NULL,
2007 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2010 -- Table structure for table `zebraqueue`
2013 DROP TABLE IF EXISTS `zebraqueue`;
2014 CREATE TABLE `zebraqueue` (
2015 `id` int(11) NOT NULL auto_increment,
2016 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2017 `operation` char(20) NOT NULL default '',
2018 `server` char(20) NOT NULL default '',
2019 `done` int(11) NOT NULL default '0',
2020 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2022 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2023 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2025 DROP TABLE IF EXISTS `services_throttle`;
2026 CREATE TABLE `services_throttle` (
2027 `service_type` varchar(10) NOT NULL default '',
2028 `service_count` varchar(45) default NULL,
2029 PRIMARY KEY (`service_type`)
2030 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2032 -- http://www.w3.org/International/articles/language-tags/
2035 DROP TABLE IF EXISTS language_subtag_registry;
2036 CREATE TABLE language_subtag_registry (
2038 type varchar(25), -- language-script-region-variant-extension-privateuse
2039 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2041 id int(11) NOT NULL auto_increment,
2043 KEY `subtag` (`subtag`)
2044 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2046 -- TODO: add suppress_scripts
2047 -- this maps three letter codes defined in iso639.2 back to their
2048 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2049 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2050 CREATE TABLE language_rfc4646_to_iso639 (
2051 rfc4646_subtag varchar(25),
2052 iso639_2_code varchar(25),
2053 id int(11) NOT NULL auto_increment,
2055 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2056 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2058 DROP TABLE IF EXISTS language_descriptions;
2059 CREATE TABLE language_descriptions (
2063 description varchar(255),
2064 id int(11) NOT NULL auto_increment,
2066 KEY `lang` (`lang`),
2067 KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
2068 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2070 -- bi-directional support, keyed by script subcode
2071 DROP TABLE IF EXISTS language_script_bidi;
2072 CREATE TABLE language_script_bidi (
2073 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2074 bidi varchar(3), -- rtl ltr
2075 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2076 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2078 -- TODO: need to map language subtags to script subtags for detection
2079 -- of bidi when script is not specified (like ar, he)
2080 DROP TABLE IF EXISTS language_script_mapping;
2081 CREATE TABLE language_script_mapping (
2082 language_subtag varchar(25),
2083 script_subtag varchar(25),
2084 KEY `language_subtag` (`language_subtag`)
2085 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2087 DROP TABLE IF EXISTS `permissions`;
2088 CREATE TABLE `permissions` (
2089 `module_bit` int(11) NOT NULL DEFAULT 0,
2090 `code` varchar(64) DEFAULT NULL,
2091 `description` varchar(255) DEFAULT NULL,
2092 PRIMARY KEY (`module_bit`, `code`),
2093 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2094 ON DELETE CASCADE ON UPDATE CASCADE
2095 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2097 DROP TABLE IF EXISTS `serialitems`;
2098 CREATE TABLE `serialitems` (
2099 `itemnumber` int(11) NOT NULL,
2100 `serialid` int(11) NOT NULL,
2101 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2102 KEY `serialitems_sfk_1` (`serialid`),
2103 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
2104 CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
2105 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2107 DROP TABLE IF EXISTS `user_permissions`;
2108 CREATE TABLE `user_permissions` (
2109 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2110 `module_bit` int(11) NOT NULL DEFAULT 0,
2111 `code` varchar(64) DEFAULT NULL,
2112 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2113 ON DELETE CASCADE ON UPDATE CASCADE,
2114 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2115 ON DELETE CASCADE ON UPDATE CASCADE
2116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2119 -- Table structure for table `tmp_holdsqueue`
2122 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2123 CREATE TABLE `tmp_holdsqueue` (
2124 `biblionumber` int(11) default NULL,
2125 `itemnumber` int(11) default NULL,
2126 `barcode` varchar(20) default NULL,
2127 `surname` mediumtext NOT NULL,
2130 `borrowernumber` int(11) NOT NULL,
2131 `cardnumber` varchar(16) default NULL,
2132 `reservedate` date default NULL,
2134 `itemcallnumber` varchar(255) default NULL,
2135 `holdingbranch` varchar(10) default NULL,
2136 `pickbranch` varchar(10) default NULL,
2138 `item_level_request` tinyint(4) NOT NULL default 0
2139 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2142 -- Table structure for table `message_queue`
2145 DROP TABLE IF EXISTS `message_queue`;
2146 CREATE TABLE `message_queue` (
2147 `message_id` int(11) NOT NULL auto_increment,
2148 `borrowernumber` int(11) default NULL,
2151 `metadata` text DEFAULT NULL,
2152 `letter_code` varchar(64) DEFAULT NULL,
2153 `message_transport_type` varchar(20) NOT NULL,
2154 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2155 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2156 `to_address` mediumtext,
2157 `from_address` mediumtext,
2158 `content_type` text,
2159 KEY `message_id` (`message_id`),
2160 KEY `borrowernumber` (`borrowernumber`),
2161 KEY `message_transport_type` (`message_transport_type`),
2162 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2163 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2164 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2167 -- Table structure for table `message_transport_types`
2170 DROP TABLE IF EXISTS `message_transport_types`;
2171 CREATE TABLE `message_transport_types` (
2172 `message_transport_type` varchar(20) NOT NULL,
2173 PRIMARY KEY (`message_transport_type`)
2174 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2177 -- Table structure for table `message_attributes`
2180 DROP TABLE IF EXISTS `message_attributes`;
2181 CREATE TABLE `message_attributes` (
2182 `message_attribute_id` int(11) NOT NULL auto_increment,
2183 `message_name` varchar(40) NOT NULL default '',
2184 `takes_days` tinyint(1) NOT NULL default '0',
2185 PRIMARY KEY (`message_attribute_id`),
2186 UNIQUE KEY `message_name` (`message_name`)
2187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2190 -- Table structure for table `message_transports`
2193 DROP TABLE IF EXISTS `message_transports`;
2194 CREATE TABLE `message_transports` (
2195 `message_attribute_id` int(11) NOT NULL,
2196 `message_transport_type` varchar(20) NOT NULL,
2197 `is_digest` tinyint(1) NOT NULL default '0',
2198 `letter_module` varchar(20) NOT NULL default '',
2199 `letter_code` varchar(20) NOT NULL default '',
2200 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2201 KEY `message_transport_type` (`message_transport_type`),
2202 KEY `letter_module` (`letter_module`,`letter_code`),
2203 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2204 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2205 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2209 -- Table structure for table `borrower_message_preferences`
2212 DROP TABLE IF EXISTS `borrower_message_preferences`;
2213 CREATE TABLE `borrower_message_preferences` (
2214 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2215 `borrowernumber` int(11) default NULL,
2216 `categorycode` varchar(10) default NULL,
2217 `message_attribute_id` int(11) default '0',
2218 `days_in_advance` int(11) default '0',
2219 `wants_digest` tinyint(1) NOT NULL default '0',
2220 PRIMARY KEY (`borrower_message_preference_id`),
2221 KEY `borrowernumber` (`borrowernumber`),
2222 KEY `categorycode` (`categorycode`),
2223 KEY `message_attribute_id` (`message_attribute_id`),
2224 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2225 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2226 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2227 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2230 -- Table structure for table `borrower_message_transport_preferences`
2233 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2234 CREATE TABLE `borrower_message_transport_preferences` (
2235 `borrower_message_preference_id` int(11) NOT NULL default '0',
2236 `message_transport_type` varchar(20) NOT NULL default '0',
2237 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2238 KEY `message_transport_type` (`message_transport_type`),
2239 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,
2240 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
2241 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2244 -- Table structure for the table branch_transfer_limits
2247 DROP TABLE IF EXISTS `branch_transfer_limits`;
2248 CREATE TABLE branch_transfer_limits (
2249 limitId int(8) NOT NULL auto_increment,
2250 toBranch varchar(10) NOT NULL,
2251 fromBranch varchar(10) NOT NULL,
2252 itemtype varchar(10) NULL,
2253 ccode varchar(10) NULL,
2254 PRIMARY KEY (limitId)
2255 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2258 -- Table structure for table `item_circulation_alert_preferences`
2261 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2262 CREATE TABLE `item_circulation_alert_preferences` (
2263 `id` int(11) NOT NULL auto_increment,
2264 `branchcode` varchar(10) NOT NULL,
2265 `categorycode` varchar(10) NOT NULL,
2266 `item_type` varchar(10) NOT NULL,
2267 `notification` varchar(16) NOT NULL,
2269 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2270 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2273 -- Table structure for table `messages`
2275 DROP TABLE IF EXISTS `messages`;
2276 CREATE TABLE `messages` (
2277 `message_id` int(11) NOT NULL auto_increment,
2278 `borrowernumber` int(11) NOT NULL,
2279 `branchcode` varchar(10) default NULL,
2280 `message_type` varchar(1) NOT NULL,
2281 `message` text NOT NULL,
2282 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2283 PRIMARY KEY (`message_id`)
2284 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2287 -- Table structure for table `accountlines`
2290 DROP TABLE IF EXISTS `accountlines`;
2291 CREATE TABLE `accountlines` (
2292 `borrowernumber` int(11) NOT NULL default 0,
2293 `accountno` smallint(6) NOT NULL default 0,
2294 `itemnumber` int(11) default NULL,
2295 `date` date default NULL,
2296 `amount` decimal(28,6) default NULL,
2297 `description` mediumtext,
2298 `dispute` mediumtext,
2299 `accounttype` varchar(5) default NULL,
2300 `amountoutstanding` decimal(28,6) default NULL,
2301 `lastincrement` decimal(28,6) default NULL,
2302 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2303 `notify_id` int(11) NOT NULL default 0,
2304 `notify_level` int(2) NOT NULL default 0,
2305 `note` text NULL default NULL,
2306 `manager_id` int(11) NULL,
2307 KEY `acctsborridx` (`borrowernumber`),
2308 KEY `timeidx` (`timestamp`),
2309 KEY `itemnumber` (`itemnumber`),
2310 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2311 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2312 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2315 -- Table structure for table `accountoffsets`
2318 DROP TABLE IF EXISTS `accountoffsets`;
2319 CREATE TABLE `accountoffsets` (
2320 `borrowernumber` int(11) NOT NULL default 0,
2321 `accountno` smallint(6) NOT NULL default 0,
2322 `offsetaccount` smallint(6) NOT NULL default 0,
2323 `offsetamount` decimal(28,6) default NULL,
2324 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2325 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2326 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2329 -- Table structure for table `action_logs`
2332 DROP TABLE IF EXISTS `action_logs`;
2333 CREATE TABLE `action_logs` (
2334 `action_id` int(11) NOT NULL auto_increment,
2335 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2336 `user` int(11) NOT NULL default 0,
2339 `object` int(11) default NULL,
2341 PRIMARY KEY (`action_id`),
2342 KEY (`timestamp`,`user`)
2343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2346 -- Table structure for table `alert`
2349 DROP TABLE IF EXISTS `alert`;
2350 CREATE TABLE `alert` (
2351 `alertid` int(11) NOT NULL auto_increment,
2352 `borrowernumber` int(11) NOT NULL default 0,
2353 `type` varchar(10) NOT NULL default '',
2354 `externalid` varchar(20) NOT NULL default '',
2355 PRIMARY KEY (`alertid`),
2356 KEY `borrowernumber` (`borrowernumber`),
2357 KEY `type` (`type`,`externalid`)
2358 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2361 -- Table structure for table `aqbasketgroups`
2364 DROP TABLE IF EXISTS `aqbasketgroups`;
2365 CREATE TABLE `aqbasketgroups` (
2366 `id` int(11) NOT NULL auto_increment,
2367 `name` varchar(50) default NULL,
2368 `closed` tinyint(1) default NULL,
2369 `booksellerid` int(11) NOT NULL,
2370 `deliveryplace` varchar(10) default NULL,
2371 `freedeliveryplace` text default NULL,
2372 `deliverycomment` varchar(255) default NULL,
2373 `billingplace` varchar(10) default NULL,
2375 KEY `booksellerid` (`booksellerid`),
2376 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2377 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2380 -- Table structure for table `aqbasket`
2383 DROP TABLE IF EXISTS `aqbasket`;
2384 CREATE TABLE `aqbasket` (
2385 `basketno` int(11) NOT NULL auto_increment,
2386 `basketname` varchar(50) default NULL,
2388 `booksellernote` mediumtext,
2389 `contractnumber` int(11),
2390 `creationdate` date default NULL,
2391 `closedate` date default NULL,
2392 `booksellerid` int(11) NOT NULL default 1,
2393 `authorisedby` varchar(10) default NULL,
2394 `booksellerinvoicenumber` mediumtext,
2395 `basketgroupid` int(11),
2396 PRIMARY KEY (`basketno`),
2397 KEY `booksellerid` (`booksellerid`),
2398 KEY `basketgroupid` (`basketgroupid`),
2399 KEY `contractnumber` (`contractnumber`),
2400 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2401 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2402 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2403 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2406 -- Table structure for table `aqbooksellers`
2409 DROP TABLE IF EXISTS `aqbooksellers`;
2410 CREATE TABLE `aqbooksellers` (
2411 `id` int(11) NOT NULL auto_increment,
2412 `name` mediumtext NOT NULL,
2413 `address1` mediumtext,
2414 `address2` mediumtext,
2415 `address3` mediumtext,
2416 `address4` mediumtext,
2417 `phone` varchar(30) default NULL,
2418 `accountnumber` mediumtext,
2419 `othersupplier` mediumtext,
2420 `currency` varchar(3) NOT NULL default '',
2421 `booksellerfax` mediumtext,
2423 `bookselleremail` mediumtext,
2424 `booksellerurl` mediumtext,
2425 `contact` varchar(100) default NULL,
2426 `postal` mediumtext,
2427 `url` varchar(255) default NULL,
2428 `contpos` varchar(100) default NULL,
2429 `contphone` varchar(100) default NULL,
2430 `contfax` varchar(100) default NULL,
2431 `contaltphone` varchar(100) default NULL,
2432 `contemail` varchar(100) default NULL,
2433 `contnotes` mediumtext,
2434 `active` tinyint(4) default NULL,
2435 `listprice` varchar(10) default NULL,
2436 `invoiceprice` varchar(10) default NULL,
2437 `gstreg` tinyint(4) default NULL,
2438 `listincgst` tinyint(4) default NULL,
2439 `invoiceincgst` tinyint(4) default NULL,
2440 `gstrate` decimal(6,4) default NULL,
2441 `discount` float(6,4) default NULL,
2442 `fax` varchar(50) default NULL,
2444 KEY `listprice` (`listprice`),
2445 KEY `invoiceprice` (`invoiceprice`),
2446 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2447 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2448 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2451 -- Table structure for table `aqbudgets`
2454 DROP TABLE IF EXISTS `aqbudgets`;
2455 CREATE TABLE `aqbudgets` (
2456 `budget_id` int(11) NOT NULL auto_increment,
2457 `budget_parent_id` int(11) default NULL,
2458 `budget_code` varchar(30) default NULL,
2459 `budget_name` varchar(80) default NULL,
2460 `budget_branchcode` varchar(10) default NULL,
2461 `budget_amount` decimal(28,6) NULL default '0.00',
2462 `budget_encumb` decimal(28,6) NULL default '0.00',
2463 `budget_expend` decimal(28,6) NULL default '0.00',
2464 `budget_notes` mediumtext,
2465 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2466 `budget_period_id` int(11) default NULL,
2467 `sort1_authcat` varchar(80) default NULL,
2468 `sort2_authcat` varchar(80) default NULL,
2469 `budget_owner_id` int(11) default NULL,
2470 `budget_permission` int(1) default '0',
2471 PRIMARY KEY (`budget_id`)
2472 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2476 -- Table structure for table `aqbudgetperiods`
2480 DROP TABLE IF EXISTS `aqbudgetperiods`;
2481 CREATE TABLE `aqbudgetperiods` (
2482 `budget_period_id` int(11) NOT NULL auto_increment,
2483 `budget_period_startdate` date NOT NULL,
2484 `budget_period_enddate` date NOT NULL,
2485 `budget_period_active` tinyint(1) default '0',
2486 `budget_period_description` mediumtext,
2487 `budget_period_total` decimal(28,6),
2488 `budget_period_locked` tinyint(1) default NULL,
2489 `sort1_authcat` varchar(10) default NULL,
2490 `sort2_authcat` varchar(10) default NULL,
2491 PRIMARY KEY (`budget_period_id`)
2492 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2495 -- Table structure for table `aqbudgets_planning`
2498 DROP TABLE IF EXISTS `aqbudgets_planning`;
2499 CREATE TABLE `aqbudgets_planning` (
2500 `plan_id` int(11) NOT NULL auto_increment,
2501 `budget_id` int(11) NOT NULL,
2502 `budget_period_id` int(11) NOT NULL,
2503 `estimated_amount` decimal(28,6) default NULL,
2504 `authcat` varchar(30) NOT NULL,
2505 `authvalue` varchar(30) NOT NULL,
2506 `display` tinyint(1) DEFAULT 1,
2507 PRIMARY KEY (`plan_id`),
2508 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2509 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2512 -- Table structure for table 'aqcontract'
2515 DROP TABLE IF EXISTS `aqcontract`;
2516 CREATE TABLE `aqcontract` (
2517 `contractnumber` int(11) NOT NULL auto_increment,
2518 `contractstartdate` date default NULL,
2519 `contractenddate` date default NULL,
2520 `contractname` varchar(50) default NULL,
2521 `contractdescription` mediumtext,
2522 `booksellerid` int(11) not NULL,
2523 PRIMARY KEY (`contractnumber`),
2524 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2525 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2526 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2529 -- Table structure for table `aqorderdelivery`
2532 DROP TABLE IF EXISTS `aqorderdelivery`;
2533 CREATE TABLE `aqorderdelivery` (
2534 `ordernumber` date default NULL,
2535 `deliverynumber` smallint(6) NOT NULL default 0,
2536 `deliverydate` varchar(18) default NULL,
2537 `qtydelivered` smallint(6) default NULL,
2538 `deliverycomments` mediumtext
2539 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2542 -- Table structure for table `aqorders`
2545 DROP TABLE IF EXISTS `aqorders`;
2546 CREATE TABLE `aqorders` (
2547 `ordernumber` int(11) NOT NULL auto_increment,
2548 `biblionumber` int(11) default NULL,
2549 `entrydate` date default NULL,
2550 `quantity` smallint(6) default NULL,
2551 `currency` varchar(3) default NULL,
2552 `listprice` decimal(28,6) default NULL,
2553 `totalamount` decimal(28,6) default NULL,
2554 `datereceived` date default NULL,
2555 `booksellerinvoicenumber` mediumtext,
2556 `freight` decimal(28,6) default NULL,
2557 `unitprice` decimal(28,6) default NULL,
2558 `quantityreceived` smallint(6) NOT NULL default 0,
2559 `cancelledby` varchar(10) default NULL,
2560 `datecancellationprinted` date default NULL,
2562 `supplierreference` mediumtext,
2563 `purchaseordernumber` mediumtext,
2564 `subscription` tinyint(1) default NULL,
2565 `serialid` varchar(30) default NULL,
2566 `basketno` int(11) default NULL,
2567 `biblioitemnumber` int(11) default NULL,
2568 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2569 `rrp` decimal(13,2) default NULL,
2570 `ecost` decimal(13,2) default NULL,
2571 `gst` decimal(13,2) default NULL,
2572 `budget_id` int(11) NOT NULL,
2573 `budgetgroup_id` int(11) NOT NULL,
2574 `budgetdate` date default NULL,
2575 `sort1` varchar(80) default NULL,
2576 `sort2` varchar(80) default NULL,
2577 `sort1_authcat` varchar(10) default NULL,
2578 `sort2_authcat` varchar(10) default NULL,
2579 `uncertainprice` tinyint(1),
2580 PRIMARY KEY (`ordernumber`),
2581 KEY `basketno` (`basketno`),
2582 KEY `biblionumber` (`biblionumber`),
2583 KEY `budget_id` (`budget_id`),
2584 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2585 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2586 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2590 -- Table structure for table `aqorders_items`
2593 DROP TABLE IF EXISTS `aqorders_items`;
2594 CREATE TABLE `aqorders_items` (
2595 `ordernumber` int(11) NOT NULL,
2596 `itemnumber` int(11) NOT NULL,
2597 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2598 PRIMARY KEY (`itemnumber`),
2599 KEY `ordernumber` (`ordernumber`)
2600 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2603 -- Table structure for table `fieldmapping`
2606 DROP TABLE IF EXISTS `fieldmapping`;
2607 CREATE TABLE `fieldmapping` (
2608 `id` int(11) NOT NULL auto_increment,
2609 `field` varchar(255) NOT NULL,
2610 `frameworkcode` char(4) NOT NULL default '',
2611 `fieldcode` char(3) NOT NULL,
2612 `subfieldcode` char(1) NOT NULL,
2614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2617 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2618 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2619 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2620 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2621 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2622 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2623 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2624 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;