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) default NULL,
942 `itemnumber` int(11) default NULL,
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 KEY `issuesborridx` (`borrowernumber`),
953 KEY `issuesitemidx` (`itemnumber`),
954 KEY `bordate` (`borrowernumber`,`timestamp`),
955 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
956 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
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 `borrowernumber` (`borrowernumber`),
1559 KEY `biblionumber` (`biblionumber`),
1560 KEY `itemnumber` (`itemnumber`),
1561 KEY `branchcode` (`branchcode`),
1562 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1563 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1564 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1565 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1566 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1569 -- Table structure for table `reviews`
1572 DROP TABLE IF EXISTS `reviews`;
1573 CREATE TABLE `reviews` (
1574 `reviewid` int(11) NOT NULL auto_increment,
1575 `borrowernumber` int(11) default NULL,
1576 `biblionumber` int(11) default NULL,
1578 `approved` tinyint(4) default NULL,
1579 `datereviewed` datetime default NULL,
1580 PRIMARY KEY (`reviewid`)
1581 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1584 -- Table structure for table `roadtype`
1587 DROP TABLE IF EXISTS `roadtype`;
1588 CREATE TABLE `roadtype` (
1589 `roadtypeid` int(11) NOT NULL auto_increment,
1590 `road_type` varchar(100) NOT NULL default '',
1591 PRIMARY KEY (`roadtypeid`)
1592 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1595 -- Table structure for table `saved_sql`
1598 DROP TABLE IF EXISTS `saved_sql`;
1599 CREATE TABLE saved_sql (
1600 `id` int(11) NOT NULL auto_increment,
1601 `borrowernumber` int(11) default NULL,
1602 `date_created` datetime default NULL,
1603 `last_modified` datetime default NULL,
1605 `last_run` datetime default NULL,
1606 `report_name` varchar(255) default NULL,
1607 `type` varchar(255) default NULL,
1610 KEY boridx (`borrowernumber`)
1611 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1615 -- Table structure for `saved_reports`
1618 DROP TABLE IF EXISTS `saved_reports`;
1619 CREATE TABLE saved_reports (
1620 `id` int(11) NOT NULL auto_increment,
1621 `report_id` int(11) default NULL,
1623 `date_run` datetime default NULL,
1625 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1629 -- Table structure for table `search_history`
1632 DROP TABLE IF EXISTS `search_history`;
1633 CREATE TABLE IF NOT EXISTS `search_history` (
1634 `userid` int(11) NOT NULL,
1635 `sessionid` varchar(32) NOT NULL,
1636 `query_desc` varchar(255) NOT NULL,
1637 `query_cgi` varchar(255) NOT NULL,
1638 `total` int(11) NOT NULL,
1639 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1640 KEY `userid` (`userid`),
1641 KEY `sessionid` (`sessionid`)
1642 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1646 -- Table structure for table `serial`
1649 DROP TABLE IF EXISTS `serial`;
1650 CREATE TABLE `serial` (
1651 `serialid` int(11) NOT NULL auto_increment,
1652 `biblionumber` varchar(100) NOT NULL default '',
1653 `subscriptionid` varchar(100) NOT NULL default '',
1654 `serialseq` varchar(100) NOT NULL default '',
1655 `status` tinyint(4) NOT NULL default 0,
1656 `planneddate` date default NULL,
1658 `publisheddate` date default NULL,
1659 `itemnumber` text default NULL,
1660 `claimdate` date default NULL,
1661 `routingnotes` text,
1662 PRIMARY KEY (`serialid`)
1663 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1666 -- Table structure for table `sessions`
1669 DROP TABLE IF EXISTS sessions;
1670 CREATE TABLE sessions (
1671 `id` varchar(32) NOT NULL,
1672 `a_session` text NOT NULL,
1674 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1677 -- Table structure for table `special_holidays`
1680 DROP TABLE IF EXISTS `special_holidays`;
1681 CREATE TABLE `special_holidays` (
1682 `id` int(11) NOT NULL auto_increment,
1683 `branchcode` varchar(10) NOT NULL default '',
1684 `day` smallint(6) NOT NULL default 0,
1685 `month` smallint(6) NOT NULL default 0,
1686 `year` smallint(6) NOT NULL default 0,
1687 `isexception` smallint(1) NOT NULL default 1,
1688 `title` varchar(50) NOT NULL default '',
1689 `description` text NOT NULL,
1691 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1694 -- Table structure for table `statistics`
1697 DROP TABLE IF EXISTS `statistics`;
1698 CREATE TABLE `statistics` (
1699 `datetime` datetime default NULL,
1700 `branch` varchar(10) default NULL,
1701 `proccode` varchar(4) default NULL,
1702 `value` double(16,4) default NULL,
1703 `type` varchar(16) default NULL,
1705 `usercode` varchar(10) default NULL,
1706 `itemnumber` int(11) default NULL,
1707 `itemtype` varchar(10) default NULL,
1708 `borrowernumber` int(11) default NULL,
1709 `associatedborrower` int(11) default NULL,
1710 KEY `timeidx` (`datetime`)
1711 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1714 -- Table structure for table `stopwords`
1717 DROP TABLE IF EXISTS `stopwords`;
1718 CREATE TABLE `stopwords` (
1719 `word` varchar(255) default NULL
1720 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1723 -- Table structure for table `subscription`
1726 DROP TABLE IF EXISTS `subscription`;
1727 CREATE TABLE `subscription` (
1728 `biblionumber` int(11) NOT NULL default 0,
1729 `subscriptionid` int(11) NOT NULL auto_increment,
1730 `librarian` varchar(100) default '',
1731 `startdate` date default NULL,
1732 `aqbooksellerid` int(11) default 0,
1733 `cost` int(11) default 0,
1734 `aqbudgetid` int(11) default 0,
1735 `weeklength` int(11) default 0,
1736 `monthlength` int(11) default 0,
1737 `numberlength` int(11) default 0,
1738 `periodicity` tinyint(4) default 0,
1739 `dow` varchar(100) default '',
1740 `numberingmethod` varchar(100) default '',
1742 `status` varchar(100) NOT NULL default '',
1743 `add1` int(11) default 0,
1744 `every1` int(11) default 0,
1745 `whenmorethan1` int(11) default 0,
1746 `setto1` int(11) default NULL,
1747 `lastvalue1` int(11) default NULL,
1748 `add2` int(11) default 0,
1749 `every2` int(11) default 0,
1750 `whenmorethan2` int(11) default 0,
1751 `setto2` int(11) default NULL,
1752 `lastvalue2` int(11) default NULL,
1753 `add3` int(11) default 0,
1754 `every3` int(11) default 0,
1755 `innerloop1` int(11) default 0,
1756 `innerloop2` int(11) default 0,
1757 `innerloop3` int(11) default 0,
1758 `whenmorethan3` int(11) default 0,
1759 `setto3` int(11) default NULL,
1760 `lastvalue3` int(11) default NULL,
1761 `issuesatonce` tinyint(3) NOT NULL default 1,
1762 `firstacquidate` date default NULL,
1763 `manualhistory` tinyint(1) NOT NULL default 0,
1764 `irregularity` text,
1765 `letter` varchar(20) default NULL,
1766 `numberpattern` tinyint(3) default 0,
1767 `distributedto` text,
1768 `internalnotes` longtext,
1770 `location` varchar(80) NULL default '',
1771 `branchcode` varchar(10) NOT NULL default '',
1772 `hemisphere` tinyint(3) default 0,
1773 `lastbranch` varchar(10),
1774 `serialsadditems` tinyint(1) NOT NULL default '0',
1775 `staffdisplaycount` VARCHAR(10) NULL,
1776 `opacdisplaycount` VARCHAR(10) NULL,
1777 `graceperiod` int(11) NOT NULL default '0',
1778 `enddate` date default NULL,
1779 PRIMARY KEY (`subscriptionid`)
1780 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1783 -- Table structure for table `subscriptionhistory`
1786 DROP TABLE IF EXISTS `subscriptionhistory`;
1787 CREATE TABLE `subscriptionhistory` (
1788 `biblionumber` int(11) NOT NULL default 0,
1789 `subscriptionid` int(11) NOT NULL default 0,
1790 `histstartdate` date default NULL,
1791 `histenddate` date default NULL,
1792 `missinglist` longtext NOT NULL,
1793 `recievedlist` longtext NOT NULL,
1794 `opacnote` varchar(150) NOT NULL default '',
1795 `librariannote` varchar(150) NOT NULL default '',
1796 PRIMARY KEY (`subscriptionid`),
1797 KEY `biblionumber` (`biblionumber`)
1798 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1801 -- Table structure for table `subscriptionroutinglist`
1804 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1805 CREATE TABLE `subscriptionroutinglist` (
1806 `routingid` int(11) NOT NULL auto_increment,
1807 `borrowernumber` int(11) NOT NULL,
1808 `ranking` int(11) default NULL,
1809 `subscriptionid` int(11) NOT NULL,
1810 PRIMARY KEY (`routingid`),
1811 UNIQUE (`subscriptionid`, `borrowernumber`),
1812 CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1813 ON DELETE CASCADE ON UPDATE CASCADE,
1814 CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
1815 ON DELETE CASCADE ON UPDATE CASCADE
1816 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1819 -- Table structure for table `suggestions`
1822 DROP TABLE IF EXISTS `suggestions`;
1823 CREATE TABLE `suggestions` (
1824 `suggestionid` int(8) NOT NULL auto_increment,
1825 `suggestedby` int(11) NOT NULL default 0,
1826 `suggesteddate` date NOT NULL default 0,
1827 `managedby` int(11) default NULL,
1828 `manageddate` date default NULL,
1829 acceptedby INT(11) default NULL,
1830 accepteddate date default NULL,
1831 rejectedby INT(11) default NULL,
1832 rejecteddate date default NULL,
1833 `STATUS` varchar(10) NOT NULL default '',
1835 `author` varchar(80) default NULL,
1836 `title` varchar(80) default NULL,
1837 `copyrightdate` smallint(6) default NULL,
1838 `publishercode` varchar(255) default NULL,
1839 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1840 `volumedesc` varchar(255) default NULL,
1841 `publicationyear` smallint(6) default 0,
1842 `place` varchar(255) default NULL,
1843 `isbn` varchar(30) default NULL,
1844 `mailoverseeing` smallint(1) default 0,
1845 `biblionumber` int(11) default NULL,
1848 branchcode VARCHAR(10) default NULL,
1849 collectiontitle text default NULL,
1850 itemtype VARCHAR(30) default NULL,
1851 quantity SMALLINT(6) default NULL,
1852 currency VARCHAR(3) default NULL,
1853 price DECIMAL(28,6) default NULL,
1854 total DECIMAL(28,6) default NULL,
1855 PRIMARY KEY (`suggestionid`),
1856 KEY `suggestedby` (`suggestedby`),
1857 KEY `managedby` (`managedby`)
1858 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1861 -- Table structure for table `systempreferences`
1864 DROP TABLE IF EXISTS `systempreferences`;
1865 CREATE TABLE `systempreferences` (
1866 `variable` varchar(50) NOT NULL default '',
1868 `options` mediumtext,
1870 `type` varchar(20) default NULL,
1871 PRIMARY KEY (`variable`)
1872 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1875 -- Table structure for table `tags`
1878 DROP TABLE IF EXISTS `tags`;
1879 CREATE TABLE `tags` (
1880 `entry` varchar(255) NOT NULL default '',
1881 `weight` bigint(20) NOT NULL default 0,
1882 PRIMARY KEY (`entry`)
1883 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1886 -- Table structure for table `tags_all`
1889 DROP TABLE IF EXISTS `tags_all`;
1890 CREATE TABLE `tags_all` (
1891 `tag_id` int(11) NOT NULL auto_increment,
1892 `borrowernumber` int(11) NOT NULL,
1893 `biblionumber` int(11) NOT NULL,
1894 `term` varchar(255) NOT NULL,
1895 `language` int(4) default NULL,
1896 `date_created` datetime NOT NULL,
1897 PRIMARY KEY (`tag_id`),
1898 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1899 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1900 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1901 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1902 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1903 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1904 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1907 -- Table structure for table `tags_approval`
1910 DROP TABLE IF EXISTS `tags_approval`;
1911 CREATE TABLE `tags_approval` (
1912 `term` varchar(255) NOT NULL,
1913 `approved` int(1) NOT NULL default '0',
1914 `date_approved` datetime default NULL,
1915 `approved_by` int(11) default NULL,
1916 `weight_total` int(9) NOT NULL default '1',
1917 PRIMARY KEY (`term`),
1918 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1919 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1920 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1921 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1924 -- Table structure for table `tags_index`
1927 DROP TABLE IF EXISTS `tags_index`;
1928 CREATE TABLE `tags_index` (
1929 `term` varchar(255) NOT NULL,
1930 `biblionumber` int(11) NOT NULL,
1931 `weight` int(9) NOT NULL default '1',
1932 PRIMARY KEY (`term`,`biblionumber`),
1933 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1934 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1935 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1936 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1937 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1938 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1941 -- Table structure for table `userflags`
1944 DROP TABLE IF EXISTS `userflags`;
1945 CREATE TABLE `userflags` (
1946 `bit` int(11) NOT NULL default 0,
1947 `flag` varchar(30) default NULL,
1948 `flagdesc` varchar(255) default NULL,
1949 `defaulton` int(11) default NULL,
1951 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1954 -- Table structure for table `virtualshelves`
1957 DROP TABLE IF EXISTS `virtualshelves`;
1958 CREATE TABLE `virtualshelves` (
1959 `shelfnumber` int(11) NOT NULL auto_increment,
1960 `shelfname` varchar(255) default NULL,
1961 `owner` varchar(80) default NULL,
1962 `category` varchar(1) default NULL,
1963 `sortfield` varchar(16) default NULL,
1964 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1965 PRIMARY KEY (`shelfnumber`)
1966 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1969 -- Table structure for table `virtualshelfcontents`
1972 DROP TABLE IF EXISTS `virtualshelfcontents`;
1973 CREATE TABLE `virtualshelfcontents` (
1974 `shelfnumber` int(11) NOT NULL default 0,
1975 `biblionumber` int(11) NOT NULL default 0,
1976 `flags` int(11) default NULL,
1977 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1978 KEY `shelfnumber` (`shelfnumber`),
1979 KEY `biblionumber` (`biblionumber`),
1980 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1981 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1982 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1985 -- Table structure for table `z3950servers`
1988 DROP TABLE IF EXISTS `z3950servers`;
1989 CREATE TABLE `z3950servers` (
1990 `host` varchar(255) default NULL,
1991 `port` int(11) default NULL,
1992 `db` varchar(255) default NULL,
1993 `userid` varchar(255) default NULL,
1994 `password` varchar(255) default NULL,
1996 `id` int(11) NOT NULL auto_increment,
1997 `checked` smallint(6) default NULL,
1998 `rank` int(11) default NULL,
1999 `syntax` varchar(80) default NULL,
2001 `position` enum('primary','secondary','') NOT NULL default 'primary',
2002 `type` enum('zed','opensearch') NOT NULL default 'zed',
2003 `encoding` text default NULL,
2004 `description` text NOT NULL,
2006 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2009 -- Table structure for table `zebraqueue`
2012 DROP TABLE IF EXISTS `zebraqueue`;
2013 CREATE TABLE `zebraqueue` (
2014 `id` int(11) NOT NULL auto_increment,
2015 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2016 `operation` char(20) NOT NULL default '',
2017 `server` char(20) NOT NULL default '',
2018 `done` int(11) NOT NULL default '0',
2019 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2021 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2022 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2024 DROP TABLE IF EXISTS `services_throttle`;
2025 CREATE TABLE `services_throttle` (
2026 `service_type` varchar(10) NOT NULL default '',
2027 `service_count` varchar(45) default NULL,
2028 PRIMARY KEY (`service_type`)
2029 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2031 -- http://www.w3.org/International/articles/language-tags/
2034 DROP TABLE IF EXISTS language_subtag_registry;
2035 CREATE TABLE language_subtag_registry (
2037 type varchar(25), -- language-script-region-variant-extension-privateuse
2038 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2040 id int(11) NOT NULL auto_increment,
2042 KEY `subtag` (`subtag`)
2043 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2045 -- TODO: add suppress_scripts
2046 -- this maps three letter codes defined in iso639.2 back to their
2047 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2048 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2049 CREATE TABLE language_rfc4646_to_iso639 (
2050 rfc4646_subtag varchar(25),
2051 iso639_2_code varchar(25),
2052 id int(11) NOT NULL auto_increment,
2054 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2055 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2057 DROP TABLE IF EXISTS language_descriptions;
2058 CREATE TABLE language_descriptions (
2062 description varchar(255),
2063 id int(11) NOT NULL auto_increment,
2065 KEY `lang` (`lang`),
2066 KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
2067 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2069 -- bi-directional support, keyed by script subcode
2070 DROP TABLE IF EXISTS language_script_bidi;
2071 CREATE TABLE language_script_bidi (
2072 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2073 bidi varchar(3), -- rtl ltr
2074 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2075 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2077 -- TODO: need to map language subtags to script subtags for detection
2078 -- of bidi when script is not specified (like ar, he)
2079 DROP TABLE IF EXISTS language_script_mapping;
2080 CREATE TABLE language_script_mapping (
2081 language_subtag varchar(25),
2082 script_subtag varchar(25),
2083 KEY `language_subtag` (`language_subtag`)
2084 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2086 DROP TABLE IF EXISTS `permissions`;
2087 CREATE TABLE `permissions` (
2088 `module_bit` int(11) NOT NULL DEFAULT 0,
2089 `code` varchar(64) DEFAULT NULL,
2090 `description` varchar(255) DEFAULT NULL,
2091 PRIMARY KEY (`module_bit`, `code`),
2092 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2093 ON DELETE CASCADE ON UPDATE CASCADE
2094 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2096 DROP TABLE IF EXISTS `serialitems`;
2097 CREATE TABLE `serialitems` (
2098 `itemnumber` int(11) NOT NULL,
2099 `serialid` int(11) NOT NULL,
2100 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2101 KEY `serialitems_sfk_1` (`serialid`),
2102 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
2103 CONSTRAINT serialitems_sfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
2104 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2106 DROP TABLE IF EXISTS `user_permissions`;
2107 CREATE TABLE `user_permissions` (
2108 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2109 `module_bit` int(11) NOT NULL DEFAULT 0,
2110 `code` varchar(64) DEFAULT NULL,
2111 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2112 ON DELETE CASCADE ON UPDATE CASCADE,
2113 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2114 ON DELETE CASCADE ON UPDATE CASCADE
2115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2118 -- Table structure for table `tmp_holdsqueue`
2121 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2122 CREATE TABLE `tmp_holdsqueue` (
2123 `biblionumber` int(11) default NULL,
2124 `itemnumber` int(11) default NULL,
2125 `barcode` varchar(20) default NULL,
2126 `surname` mediumtext NOT NULL,
2129 `borrowernumber` int(11) NOT NULL,
2130 `cardnumber` varchar(16) default NULL,
2131 `reservedate` date default NULL,
2133 `itemcallnumber` varchar(255) default NULL,
2134 `holdingbranch` varchar(10) default NULL,
2135 `pickbranch` varchar(10) default NULL,
2137 `item_level_request` tinyint(4) NOT NULL default 0
2138 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2141 -- Table structure for table `message_queue`
2144 DROP TABLE IF EXISTS `message_queue`;
2145 CREATE TABLE `message_queue` (
2146 `message_id` int(11) NOT NULL auto_increment,
2147 `borrowernumber` int(11) default NULL,
2150 `metadata` text DEFAULT NULL,
2151 `letter_code` varchar(64) DEFAULT NULL,
2152 `message_transport_type` varchar(20) NOT NULL,
2153 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2154 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2155 `to_address` mediumtext,
2156 `from_address` mediumtext,
2157 `content_type` text,
2158 KEY `message_id` (`message_id`),
2159 KEY `borrowernumber` (`borrowernumber`),
2160 KEY `message_transport_type` (`message_transport_type`),
2161 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2162 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2163 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2166 -- Table structure for table `message_transport_types`
2169 DROP TABLE IF EXISTS `message_transport_types`;
2170 CREATE TABLE `message_transport_types` (
2171 `message_transport_type` varchar(20) NOT NULL,
2172 PRIMARY KEY (`message_transport_type`)
2173 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2176 -- Table structure for table `message_attributes`
2179 DROP TABLE IF EXISTS `message_attributes`;
2180 CREATE TABLE `message_attributes` (
2181 `message_attribute_id` int(11) NOT NULL auto_increment,
2182 `message_name` varchar(40) NOT NULL default '',
2183 `takes_days` tinyint(1) NOT NULL default '0',
2184 PRIMARY KEY (`message_attribute_id`),
2185 UNIQUE KEY `message_name` (`message_name`)
2186 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2189 -- Table structure for table `message_transports`
2192 DROP TABLE IF EXISTS `message_transports`;
2193 CREATE TABLE `message_transports` (
2194 `message_attribute_id` int(11) NOT NULL,
2195 `message_transport_type` varchar(20) NOT NULL,
2196 `is_digest` tinyint(1) NOT NULL default '0',
2197 `letter_module` varchar(20) NOT NULL default '',
2198 `letter_code` varchar(20) NOT NULL default '',
2199 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2200 KEY `message_transport_type` (`message_transport_type`),
2201 KEY `letter_module` (`letter_module`,`letter_code`),
2202 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2203 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2204 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2208 -- Table structure for table `borrower_message_preferences`
2211 DROP TABLE IF EXISTS `borrower_message_preferences`;
2212 CREATE TABLE `borrower_message_preferences` (
2213 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2214 `borrowernumber` int(11) default NULL,
2215 `categorycode` varchar(10) default NULL,
2216 `message_attribute_id` int(11) default '0',
2217 `days_in_advance` int(11) default '0',
2218 `wants_digest` tinyint(1) NOT NULL default '0',
2219 PRIMARY KEY (`borrower_message_preference_id`),
2220 KEY `borrowernumber` (`borrowernumber`),
2221 KEY `categorycode` (`categorycode`),
2222 KEY `message_attribute_id` (`message_attribute_id`),
2223 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2224 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2225 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2226 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2229 -- Table structure for table `borrower_message_transport_preferences`
2232 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2233 CREATE TABLE `borrower_message_transport_preferences` (
2234 `borrower_message_preference_id` int(11) NOT NULL default '0',
2235 `message_transport_type` varchar(20) NOT NULL default '0',
2236 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2237 KEY `message_transport_type` (`message_transport_type`),
2238 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,
2239 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
2240 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2243 -- Table structure for the table branch_transfer_limits
2246 DROP TABLE IF EXISTS `branch_transfer_limits`;
2247 CREATE TABLE branch_transfer_limits (
2248 limitId int(8) NOT NULL auto_increment,
2249 toBranch varchar(10) NOT NULL,
2250 fromBranch varchar(10) NOT NULL,
2251 itemtype varchar(10) NULL,
2252 ccode varchar(10) NULL,
2253 PRIMARY KEY (limitId)
2254 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2257 -- Table structure for table `item_circulation_alert_preferences`
2260 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2261 CREATE TABLE `item_circulation_alert_preferences` (
2262 `id` int(11) NOT NULL auto_increment,
2263 `branchcode` varchar(10) NOT NULL,
2264 `categorycode` varchar(10) NOT NULL,
2265 `item_type` varchar(10) NOT NULL,
2266 `notification` varchar(16) NOT NULL,
2268 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2272 -- Table structure for table `messages`
2274 DROP TABLE IF EXISTS `messages`;
2275 CREATE TABLE `messages` (
2276 `message_id` int(11) NOT NULL auto_increment,
2277 `borrowernumber` int(11) NOT NULL,
2278 `branchcode` varchar(10) default NULL,
2279 `message_type` varchar(1) NOT NULL,
2280 `message` text NOT NULL,
2281 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2282 PRIMARY KEY (`message_id`)
2283 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2286 -- Table structure for table `accountlines`
2289 DROP TABLE IF EXISTS `accountlines`;
2290 CREATE TABLE `accountlines` (
2291 `borrowernumber` int(11) NOT NULL default 0,
2292 `accountno` smallint(6) NOT NULL default 0,
2293 `itemnumber` int(11) default NULL,
2294 `date` date default NULL,
2295 `amount` decimal(28,6) default NULL,
2296 `description` mediumtext,
2297 `dispute` mediumtext,
2298 `accounttype` varchar(5) default NULL,
2299 `amountoutstanding` decimal(28,6) default NULL,
2300 `lastincrement` decimal(28,6) default NULL,
2301 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2302 `notify_id` int(11) NOT NULL default 0,
2303 `notify_level` int(2) NOT NULL default 0,
2304 `note` text NULL default NULL,
2305 `manager_id` int(11) NULL,
2306 KEY `acctsborridx` (`borrowernumber`),
2307 KEY `timeidx` (`timestamp`),
2308 KEY `itemnumber` (`itemnumber`),
2309 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2310 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2314 -- Table structure for table `accountoffsets`
2317 DROP TABLE IF EXISTS `accountoffsets`;
2318 CREATE TABLE `accountoffsets` (
2319 `borrowernumber` int(11) NOT NULL default 0,
2320 `accountno` smallint(6) NOT NULL default 0,
2321 `offsetaccount` smallint(6) NOT NULL default 0,
2322 `offsetamount` decimal(28,6) default NULL,
2323 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2324 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2328 -- Table structure for table `action_logs`
2331 DROP TABLE IF EXISTS `action_logs`;
2332 CREATE TABLE `action_logs` (
2333 `action_id` int(11) NOT NULL auto_increment,
2334 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2335 `user` int(11) NOT NULL default 0,
2338 `object` int(11) default NULL,
2340 PRIMARY KEY (`action_id`),
2341 KEY (`timestamp`,`user`)
2342 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2345 -- Table structure for table `alert`
2348 DROP TABLE IF EXISTS `alert`;
2349 CREATE TABLE `alert` (
2350 `alertid` int(11) NOT NULL auto_increment,
2351 `borrowernumber` int(11) NOT NULL default 0,
2352 `type` varchar(10) NOT NULL default '',
2353 `externalid` varchar(20) NOT NULL default '',
2354 PRIMARY KEY (`alertid`),
2355 KEY `borrowernumber` (`borrowernumber`),
2356 KEY `type` (`type`,`externalid`)
2357 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2360 -- Table structure for table `aqbasketgroups`
2363 DROP TABLE IF EXISTS `aqbasketgroups`;
2364 CREATE TABLE `aqbasketgroups` (
2365 `id` int(11) NOT NULL auto_increment,
2366 `name` varchar(50) default NULL,
2367 `closed` tinyint(1) default NULL,
2368 `booksellerid` int(11) NOT NULL,
2369 `deliveryplace` varchar(10) default NULL,
2370 `freedeliveryplace` text default NULL,
2371 `deliverycomment` varchar(255) default NULL,
2372 `billingplace` varchar(10) default NULL,
2374 KEY `booksellerid` (`booksellerid`),
2375 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2376 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2379 -- Table structure for table `aqbasket`
2382 DROP TABLE IF EXISTS `aqbasket`;
2383 CREATE TABLE `aqbasket` (
2384 `basketno` int(11) NOT NULL auto_increment,
2385 `basketname` varchar(50) default NULL,
2387 `booksellernote` mediumtext,
2388 `contractnumber` int(11),
2389 `creationdate` date default NULL,
2390 `closedate` date default NULL,
2391 `booksellerid` int(11) NOT NULL default 1,
2392 `authorisedby` varchar(10) default NULL,
2393 `booksellerinvoicenumber` mediumtext,
2394 `basketgroupid` int(11),
2395 PRIMARY KEY (`basketno`),
2396 KEY `booksellerid` (`booksellerid`),
2397 KEY `basketgroupid` (`basketgroupid`),
2398 KEY `contractnumber` (`contractnumber`),
2399 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2400 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2401 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2402 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2405 -- Table structure for table `aqbooksellers`
2408 DROP TABLE IF EXISTS `aqbooksellers`;
2409 CREATE TABLE `aqbooksellers` (
2410 `id` int(11) NOT NULL auto_increment,
2411 `name` mediumtext NOT NULL,
2412 `address1` mediumtext,
2413 `address2` mediumtext,
2414 `address3` mediumtext,
2415 `address4` mediumtext,
2416 `phone` varchar(30) default NULL,
2417 `accountnumber` mediumtext,
2418 `othersupplier` mediumtext,
2419 `currency` varchar(3) NOT NULL default '',
2420 `booksellerfax` mediumtext,
2422 `bookselleremail` mediumtext,
2423 `booksellerurl` mediumtext,
2424 `contact` varchar(100) default NULL,
2425 `postal` mediumtext,
2426 `url` varchar(255) default NULL,
2427 `contpos` varchar(100) default NULL,
2428 `contphone` varchar(100) default NULL,
2429 `contfax` varchar(100) default NULL,
2430 `contaltphone` varchar(100) default NULL,
2431 `contemail` varchar(100) default NULL,
2432 `contnotes` mediumtext,
2433 `active` tinyint(4) default NULL,
2434 `listprice` varchar(10) default NULL,
2435 `invoiceprice` varchar(10) default NULL,
2436 `gstreg` tinyint(4) default NULL,
2437 `listincgst` tinyint(4) default NULL,
2438 `invoiceincgst` tinyint(4) default NULL,
2439 `gstrate` decimal(6,4) default NULL,
2440 `discount` float(6,4) default NULL,
2441 `fax` varchar(50) default NULL,
2443 KEY `listprice` (`listprice`),
2444 KEY `invoiceprice` (`invoiceprice`),
2445 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2446 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2447 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2450 -- Table structure for table `aqbudgets`
2453 DROP TABLE IF EXISTS `aqbudgets`;
2454 CREATE TABLE `aqbudgets` (
2455 `budget_id` int(11) NOT NULL auto_increment,
2456 `budget_parent_id` int(11) default NULL,
2457 `budget_code` varchar(30) default NULL,
2458 `budget_name` varchar(80) default NULL,
2459 `budget_branchcode` varchar(10) default NULL,
2460 `budget_amount` decimal(28,6) NULL default '0.00',
2461 `budget_encumb` decimal(28,6) NULL default '0.00',
2462 `budget_expend` decimal(28,6) NULL default '0.00',
2463 `budget_notes` mediumtext,
2464 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2465 `budget_period_id` int(11) default NULL,
2466 `sort1_authcat` varchar(80) default NULL,
2467 `sort2_authcat` varchar(80) default NULL,
2468 `budget_owner_id` int(11) default NULL,
2469 `budget_permission` int(1) default '0',
2470 PRIMARY KEY (`budget_id`)
2471 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2475 -- Table structure for table `aqbudgetperiods`
2479 DROP TABLE IF EXISTS `aqbudgetperiods`;
2480 CREATE TABLE `aqbudgetperiods` (
2481 `budget_period_id` int(11) NOT NULL auto_increment,
2482 `budget_period_startdate` date NOT NULL,
2483 `budget_period_enddate` date NOT NULL,
2484 `budget_period_active` tinyint(1) default '0',
2485 `budget_period_description` mediumtext,
2486 `budget_period_total` decimal(28,6),
2487 `budget_period_locked` tinyint(1) default NULL,
2488 `sort1_authcat` varchar(10) default NULL,
2489 `sort2_authcat` varchar(10) default NULL,
2490 PRIMARY KEY (`budget_period_id`)
2491 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2494 -- Table structure for table `aqbudgets_planning`
2497 DROP TABLE IF EXISTS `aqbudgets_planning`;
2498 CREATE TABLE `aqbudgets_planning` (
2499 `plan_id` int(11) NOT NULL auto_increment,
2500 `budget_id` int(11) NOT NULL,
2501 `budget_period_id` int(11) NOT NULL,
2502 `estimated_amount` decimal(28,6) default NULL,
2503 `authcat` varchar(30) NOT NULL,
2504 `authvalue` varchar(30) NOT NULL,
2505 `display` tinyint(1) DEFAULT 1,
2506 PRIMARY KEY (`plan_id`),
2507 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2508 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2511 -- Table structure for table 'aqcontract'
2514 DROP TABLE IF EXISTS `aqcontract`;
2515 CREATE TABLE `aqcontract` (
2516 `contractnumber` int(11) NOT NULL auto_increment,
2517 `contractstartdate` date default NULL,
2518 `contractenddate` date default NULL,
2519 `contractname` varchar(50) default NULL,
2520 `contractdescription` mediumtext,
2521 `booksellerid` int(11) not NULL,
2522 PRIMARY KEY (`contractnumber`),
2523 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2524 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2525 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2528 -- Table structure for table `aqorderdelivery`
2531 DROP TABLE IF EXISTS `aqorderdelivery`;
2532 CREATE TABLE `aqorderdelivery` (
2533 `ordernumber` date default NULL,
2534 `deliverynumber` smallint(6) NOT NULL default 0,
2535 `deliverydate` varchar(18) default NULL,
2536 `qtydelivered` smallint(6) default NULL,
2537 `deliverycomments` mediumtext
2538 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2541 -- Table structure for table `aqorders`
2544 DROP TABLE IF EXISTS `aqorders`;
2545 CREATE TABLE `aqorders` (
2546 `ordernumber` int(11) NOT NULL auto_increment,
2547 `biblionumber` int(11) default NULL,
2548 `entrydate` date default NULL,
2549 `quantity` smallint(6) default NULL,
2550 `currency` varchar(3) default NULL,
2551 `listprice` decimal(28,6) default NULL,
2552 `totalamount` decimal(28,6) default NULL,
2553 `datereceived` date default NULL,
2554 `booksellerinvoicenumber` mediumtext,
2555 `freight` decimal(28,6) default NULL,
2556 `unitprice` decimal(28,6) default NULL,
2557 `quantityreceived` smallint(6) NOT NULL default 0,
2558 `cancelledby` varchar(10) default NULL,
2559 `datecancellationprinted` date default NULL,
2561 `supplierreference` mediumtext,
2562 `purchaseordernumber` mediumtext,
2563 `subscription` tinyint(1) default NULL,
2564 `serialid` varchar(30) default NULL,
2565 `basketno` int(11) default NULL,
2566 `biblioitemnumber` int(11) default NULL,
2567 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2568 `rrp` decimal(13,2) default NULL,
2569 `ecost` decimal(13,2) default NULL,
2570 `gst` decimal(13,2) default NULL,
2571 `budget_id` int(11) NOT NULL,
2572 `budgetgroup_id` int(11) NOT NULL,
2573 `budgetdate` date default NULL,
2574 `sort1` varchar(80) default NULL,
2575 `sort2` varchar(80) default NULL,
2576 `sort1_authcat` varchar(10) default NULL,
2577 `sort2_authcat` varchar(10) default NULL,
2578 `uncertainprice` tinyint(1),
2579 PRIMARY KEY (`ordernumber`),
2580 KEY `basketno` (`basketno`),
2581 KEY `biblionumber` (`biblionumber`),
2582 KEY `budget_id` (`budget_id`),
2583 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2584 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2585 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2589 -- Table structure for table `aqorders_items`
2592 DROP TABLE IF EXISTS `aqorders_items`;
2593 CREATE TABLE `aqorders_items` (
2594 `ordernumber` int(11) NOT NULL,
2595 `itemnumber` int(11) NOT NULL,
2596 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2597 PRIMARY KEY (`itemnumber`),
2598 KEY `ordernumber` (`ordernumber`)
2599 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2602 -- Table structure for table `fieldmapping`
2605 DROP TABLE IF EXISTS `fieldmapping`;
2606 CREATE TABLE `fieldmapping` (
2607 `id` int(11) NOT NULL auto_increment,
2608 `field` varchar(255) NOT NULL,
2609 `frameworkcode` char(4) NOT NULL default '',
2610 `fieldcode` char(3) NOT NULL,
2611 `subfieldcode` char(1) NOT NULL,
2613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2616 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2617 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2618 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2619 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2620 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2621 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2622 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2623 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;