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_zipcode` varchar(20) default NULL,
530 PRIMARY KEY (`cityid`)
531 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
534 -- Table structure for table `class_sort_rules`
537 DROP TABLE IF EXISTS `class_sort_rules`;
538 CREATE TABLE `class_sort_rules` (
539 `class_sort_rule` varchar(10) NOT NULL default '',
540 `description` mediumtext,
541 `sort_routine` varchar(30) NOT NULL default '',
542 PRIMARY KEY (`class_sort_rule`),
543 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
544 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
547 -- Table structure for table `class_sources`
550 DROP TABLE IF EXISTS `class_sources`;
551 CREATE TABLE `class_sources` (
552 `cn_source` varchar(10) NOT NULL default '',
553 `description` mediumtext,
554 `used` tinyint(4) NOT NULL default 0,
555 `class_sort_rule` varchar(10) NOT NULL default '',
556 PRIMARY KEY (`cn_source`),
557 UNIQUE KEY `cn_source_idx` (`cn_source`),
558 KEY `used_idx` (`used`),
559 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
560 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
563 -- Table structure for table `currency`
566 DROP TABLE IF EXISTS `currency`;
567 CREATE TABLE `currency` (
568 `currency` varchar(10) NOT NULL default '',
569 `symbol` varchar(5) default NULL,
570 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
571 `rate` float(15,5) default NULL,
572 `active` tinyint(1) default NULL,
573 PRIMARY KEY (`currency`)
574 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
577 -- Table structure for table `deletedbiblio`
580 DROP TABLE IF EXISTS `deletedbiblio`;
581 CREATE TABLE `deletedbiblio` (
582 `biblionumber` int(11) NOT NULL default 0,
583 `frameworkcode` varchar(4) NOT NULL default '',
586 `unititle` mediumtext,
588 `serial` tinyint(1) default NULL,
589 `seriestitle` mediumtext,
590 `copyrightdate` smallint(6) default NULL,
591 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
592 `datecreated` DATE NOT NULL,
593 `abstract` mediumtext,
594 PRIMARY KEY (`biblionumber`),
595 KEY `blbnoidx` (`biblionumber`)
596 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
599 -- Table structure for table `deletedbiblioitems`
602 DROP TABLE IF EXISTS `deletedbiblioitems`;
603 CREATE TABLE `deletedbiblioitems` (
604 `biblioitemnumber` int(11) NOT NULL default 0,
605 `biblionumber` int(11) NOT NULL default 0,
608 `itemtype` varchar(10) default NULL,
609 `isbn` varchar(30) default NULL,
610 `issn` varchar(9) default NULL,
611 `publicationyear` text,
612 `publishercode` varchar(255) default NULL,
613 `volumedate` date default NULL,
615 `collectiontitle` mediumtext default NULL,
616 `collectionissn` text default NULL,
617 `collectionvolume` mediumtext default NULL,
618 `editionstatement` text default NULL,
619 `editionresponsibility` text default NULL,
620 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
621 `illus` varchar(255) default NULL,
622 `pages` varchar(255) default NULL,
624 `size` varchar(255) default NULL,
625 `place` varchar(255) default NULL,
626 `lccn` varchar(25) default NULL,
628 `url` varchar(255) default NULL,
629 `cn_source` varchar(10) default NULL,
630 `cn_class` varchar(30) default NULL,
631 `cn_item` varchar(10) default NULL,
632 `cn_suffix` varchar(10) default NULL,
633 `cn_sort` varchar(30) default NULL,
634 `totalissues` int(10),
635 `marcxml` longtext NOT NULL,
636 PRIMARY KEY (`biblioitemnumber`),
637 KEY `bibinoidx` (`biblioitemnumber`),
638 KEY `bibnoidx` (`biblionumber`),
640 KEY `publishercode` (`publishercode`)
641 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
644 -- Table structure for table `deletedborrowers`
647 DROP TABLE IF EXISTS `deletedborrowers`;
648 CREATE TABLE `deletedborrowers` (
649 `borrowernumber` int(11) NOT NULL default 0,
650 `cardnumber` varchar(16) NOT NULL default '',
651 `surname` mediumtext NOT NULL,
654 `othernames` mediumtext,
656 `streetnumber` varchar(10) default NULL,
657 `streettype` varchar(50) default NULL,
658 `address` mediumtext NOT NULL,
660 `city` mediumtext NOT NULL,
661 `state` text default NULL,
662 `zipcode` varchar(25) default NULL,
666 `mobile` varchar(50) default NULL,
670 `B_streetnumber` varchar(10) default NULL,
671 `B_streettype` varchar(50) default NULL,
672 `B_address` varchar(100) default NULL,
673 `B_address2` text default NULL,
675 `B_state` text default NULL,
676 `B_zipcode` varchar(25) default NULL,
679 `B_phone` mediumtext,
680 `dateofbirth` date default NULL,
681 `branchcode` varchar(10) NOT NULL default '',
682 `categorycode` varchar(10) default NULL,
683 `dateenrolled` date default NULL,
684 `dateexpiry` date default NULL,
685 `gonenoaddress` tinyint(1) default NULL,
686 `lost` tinyint(1) default NULL,
687 `debarred` tinyint(1) default NULL,
688 `contactname` mediumtext,
689 `contactfirstname` text,
691 `guarantorid` int(11) default NULL,
692 `borrowernotes` mediumtext,
693 `relationship` varchar(100) default NULL,
694 `ethnicity` varchar(50) default NULL,
695 `ethnotes` varchar(255) default NULL,
696 `sex` varchar(1) default NULL,
697 `password` varchar(30) default NULL,
698 `flags` int(11) default NULL,
699 `userid` varchar(30) default NULL,
700 `opacnote` mediumtext,
701 `contactnote` varchar(255) default NULL,
702 `sort1` varchar(80) default NULL,
703 `sort2` varchar(80) default NULL,
704 `altcontactfirstname` varchar(255) default NULL,
705 `altcontactsurname` varchar(255) default NULL,
706 `altcontactaddress1` varchar(255) default NULL,
707 `altcontactaddress2` varchar(255) default NULL,
708 `altcontactaddress3` varchar(255) default NULL,
709 `altcontactstate` text default NULL,
710 `altcontactzipcode` varchar(50) default NULL,
711 `altcontactcountry` text default NULL,
712 `altcontactphone` varchar(50) default NULL,
713 `smsalertnumber` varchar(50) default NULL,
714 `privacy` integer(11) DEFAULT '1' NOT NULL,
715 KEY `borrowernumber` (`borrowernumber`),
716 KEY `cardnumber` (`cardnumber`)
717 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
720 -- Table structure for table `deleteditems`
723 DROP TABLE IF EXISTS `deleteditems`;
724 CREATE TABLE `deleteditems` (
725 `itemnumber` int(11) NOT NULL default 0,
726 `biblionumber` int(11) NOT NULL default 0,
727 `biblioitemnumber` int(11) NOT NULL default 0,
728 `barcode` varchar(20) default NULL,
729 `dateaccessioned` date default NULL,
730 `booksellerid` mediumtext default NULL,
731 `homebranch` varchar(10) default NULL,
732 `price` decimal(8,2) default NULL,
733 `replacementprice` decimal(8,2) default NULL,
734 `replacementpricedate` date default NULL,
735 `datelastborrowed` date default NULL,
736 `datelastseen` date default NULL,
737 `stack` tinyint(1) default NULL,
738 `notforloan` tinyint(1) NOT NULL default 0,
739 `damaged` tinyint(1) NOT NULL default 0,
740 `itemlost` tinyint(1) NOT NULL default 0,
741 `wthdrawn` tinyint(1) NOT NULL default 0,
742 `itemcallnumber` varchar(255) default NULL,
743 `issues` smallint(6) default NULL,
744 `renewals` smallint(6) default NULL,
745 `reserves` smallint(6) default NULL,
746 `restricted` tinyint(1) default NULL,
747 `itemnotes` mediumtext,
748 `holdingbranch` varchar(10) default NULL,
749 `paidfor` mediumtext,
750 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
751 `location` varchar(80) default NULL,
752 `permanent_location` varchar(80) default NULL,
753 `onloan` date default NULL,
754 `cn_source` varchar(10) default NULL,
755 `cn_sort` varchar(30) default NULL,
756 `ccode` varchar(10) default NULL,
757 `materials` varchar(10) default NULL,
758 `uri` varchar(255) default NULL,
759 `itype` varchar(10) default NULL,
760 `more_subfields_xml` longtext default NULL,
761 `enumchron` text default NULL,
762 `copynumber` varchar(32) default NULL,
763 `stocknumber` varchar(32) default NULL,
765 PRIMARY KEY (`itemnumber`),
766 KEY `delitembarcodeidx` (`barcode`),
767 KEY `delitemstocknumberidx` (`stocknumber`),
768 KEY `delitembinoidx` (`biblioitemnumber`),
769 KEY `delitembibnoidx` (`biblionumber`),
770 KEY `delhomebranch` (`homebranch`),
771 KEY `delholdingbranch` (`holdingbranch`)
772 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
775 -- Table structure for table `ethnicity`
778 DROP TABLE IF EXISTS `ethnicity`;
779 CREATE TABLE `ethnicity` (
780 `code` varchar(10) NOT NULL default '',
781 `name` varchar(255) default NULL,
783 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
786 -- Table structure for table `export_format`
789 DROP TABLE IF EXISTS `export_format`;
790 CREATE TABLE `export_format` (
791 `export_format_id` int(11) NOT NULL auto_increment,
792 `profile` varchar(255) NOT NULL,
793 `description` mediumtext NOT NULL,
794 `marcfields` mediumtext NOT NULL,
795 `csv_separator` varchar(2) NOT NULL,
796 `field_separator` varchar(2) NOT NULL,
797 `subfield_separator` varchar(2) NOT NULL,
798 `encoding` varchar(255) NOT NULL,
799 PRIMARY KEY (`export_format_id`)
800 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
804 -- Table structure for table `hold_fill_targets`
807 DROP TABLE IF EXISTS `hold_fill_targets`;
808 CREATE TABLE hold_fill_targets (
809 `borrowernumber` int(11) NOT NULL,
810 `biblionumber` int(11) NOT NULL,
811 `itemnumber` int(11) NOT NULL,
812 `source_branchcode` varchar(10) default NULL,
813 `item_level_request` tinyint(4) NOT NULL default 0,
814 PRIMARY KEY `itemnumber` (`itemnumber`),
815 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
816 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
817 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
818 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
819 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
820 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
821 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
822 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
823 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
824 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
827 -- Table structure for table `import_batches`
830 DROP TABLE IF EXISTS `import_batches`;
831 CREATE TABLE `import_batches` (
832 `import_batch_id` int(11) NOT NULL auto_increment,
833 `matcher_id` int(11) default NULL,
834 `template_id` int(11) default NULL,
835 `branchcode` varchar(10) default NULL,
836 `num_biblios` int(11) NOT NULL default 0,
837 `num_items` int(11) NOT NULL default 0,
838 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
839 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
840 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
841 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
842 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
843 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
844 `file_name` varchar(100),
845 `comments` mediumtext,
846 PRIMARY KEY (`import_batch_id`),
847 KEY `branchcode` (`branchcode`)
848 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
851 -- Table structure for table `import_records`
854 DROP TABLE IF EXISTS `import_records`;
855 CREATE TABLE `import_records` (
856 `import_record_id` int(11) NOT NULL auto_increment,
857 `import_batch_id` int(11) NOT NULL,
858 `branchcode` varchar(10) default NULL,
859 `record_sequence` int(11) NOT NULL default 0,
860 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
861 `import_date` DATE default NULL,
862 `marc` longblob NOT NULL,
863 `marcxml` longtext NOT NULL,
864 `marcxml_old` longtext NOT NULL,
865 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
866 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
867 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
868 `import_error` mediumtext,
869 `encoding` varchar(40) NOT NULL default '',
870 `z3950random` varchar(40) default NULL,
871 PRIMARY KEY (`import_record_id`),
872 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
873 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
874 KEY `branchcode` (`branchcode`),
875 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
876 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
879 -- Table structure for `import_record_matches`
881 DROP TABLE IF EXISTS `import_record_matches`;
882 CREATE TABLE `import_record_matches` (
883 `import_record_id` int(11) NOT NULL,
884 `candidate_match_id` int(11) NOT NULL,
885 `score` int(11) NOT NULL default 0,
886 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
887 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
888 KEY `record_score` (`import_record_id`, `score`)
889 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
892 -- Table structure for table `import_biblios`
895 DROP TABLE IF EXISTS `import_biblios`;
896 CREATE TABLE `import_biblios` (
897 `import_record_id` int(11) NOT NULL,
898 `matched_biblionumber` int(11) default NULL,
899 `control_number` varchar(25) default NULL,
900 `original_source` varchar(25) default NULL,
901 `title` varchar(128) default NULL,
902 `author` varchar(80) default NULL,
903 `isbn` varchar(30) default NULL,
904 `issn` varchar(9) default NULL,
905 `has_items` tinyint(1) NOT NULL default 0,
906 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
907 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
908 KEY `matched_biblionumber` (`matched_biblionumber`),
909 KEY `title` (`title`),
911 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
914 -- Table structure for table `import_items`
917 DROP TABLE IF EXISTS `import_items`;
918 CREATE TABLE `import_items` (
919 `import_items_id` int(11) NOT NULL auto_increment,
920 `import_record_id` int(11) NOT NULL,
921 `itemnumber` int(11) default NULL,
922 `branchcode` varchar(10) default NULL,
923 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
924 `marcxml` longtext NOT NULL,
925 `import_error` mediumtext,
926 PRIMARY KEY (`import_items_id`),
927 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
928 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
929 KEY `itemnumber` (`itemnumber`),
930 KEY `branchcode` (`branchcode`)
931 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
934 -- Table structure for table `issues`
937 DROP TABLE IF EXISTS `issues`;
938 CREATE TABLE `issues` (
939 `borrowernumber` int(11) default NULL,
940 `itemnumber` int(11) default NULL,
941 `date_due` date default NULL,
942 `branchcode` varchar(10) default NULL,
943 `issuingbranch` varchar(18) default NULL,
944 `returndate` date default NULL,
945 `lastreneweddate` date default NULL,
946 `return` varchar(4) default NULL,
947 `renewals` tinyint(4) default NULL,
948 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
949 `issuedate` date default NULL,
950 KEY `issuesborridx` (`borrowernumber`),
951 KEY `issuesitemidx` (`itemnumber`),
952 KEY `bordate` (`borrowernumber`,`timestamp`),
953 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
954 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
955 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
958 -- Table structure for table `issuingrules`
961 DROP TABLE IF EXISTS `issuingrules`;
962 CREATE TABLE `issuingrules` (
963 `categorycode` varchar(10) NOT NULL default '',
964 `itemtype` varchar(10) NOT NULL default '',
965 `restrictedtype` tinyint(1) default NULL,
966 `rentaldiscount` decimal(28,6) default NULL,
967 `reservecharge` decimal(28,6) default NULL,
968 `fine` decimal(28,6) default NULL,
969 `finedays` int(11) default NULL,
970 `firstremind` int(11) default NULL,
971 `chargeperiod` int(11) default NULL,
972 `accountsent` int(11) default NULL,
973 `chargename` varchar(100) default NULL,
974 `maxissueqty` int(4) default NULL,
975 `issuelength` int(4) default NULL,
976 `hardduedate` date default NULL,
977 `hardduedatecompare` tinyint NOT NULL default "0",
978 `renewalsallowed` smallint(6) NOT NULL default "0",
979 `reservesallowed` smallint(6) NOT NULL default "0",
980 `branchcode` varchar(10) NOT NULL default '',
981 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
982 KEY `categorycode` (`categorycode`),
983 KEY `itemtype` (`itemtype`)
984 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
987 -- Table structure for table `items`
990 DROP TABLE IF EXISTS `items`;
991 CREATE TABLE `items` (
992 `itemnumber` int(11) NOT NULL auto_increment,
993 `biblionumber` int(11) NOT NULL default 0,
994 `biblioitemnumber` int(11) NOT NULL default 0,
995 `barcode` varchar(20) default NULL,
996 `dateaccessioned` date default NULL,
997 `booksellerid` mediumtext default NULL,
998 `homebranch` varchar(10) default NULL,
999 `price` decimal(8,2) default NULL,
1000 `replacementprice` decimal(8,2) default NULL,
1001 `replacementpricedate` date default NULL,
1002 `datelastborrowed` date default NULL,
1003 `datelastseen` date default NULL,
1004 `stack` tinyint(1) default NULL,
1005 `notforloan` tinyint(1) NOT NULL default 0,
1006 `damaged` tinyint(1) NOT NULL default 0,
1007 `itemlost` tinyint(1) NOT NULL default 0,
1008 `wthdrawn` tinyint(1) NOT NULL default 0,
1009 `itemcallnumber` varchar(255) default NULL,
1010 `issues` smallint(6) default NULL,
1011 `renewals` smallint(6) default NULL,
1012 `reserves` smallint(6) default NULL,
1013 `restricted` tinyint(1) default NULL,
1014 `itemnotes` mediumtext,
1015 `holdingbranch` varchar(10) default NULL,
1016 `paidfor` mediumtext,
1017 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1018 `location` varchar(80) default NULL,
1019 `permanent_location` varchar(80) default NULL,
1020 `onloan` date default NULL,
1021 `cn_source` varchar(10) default NULL,
1022 `cn_sort` varchar(30) default NULL,
1023 `ccode` varchar(10) default NULL,
1024 `materials` varchar(10) default NULL,
1025 `uri` varchar(255) default NULL,
1026 `itype` varchar(10) default NULL,
1027 `more_subfields_xml` longtext default NULL,
1028 `enumchron` text default NULL,
1029 `copynumber` varchar(32) default NULL,
1030 `stocknumber` varchar(32) default NULL,
1031 PRIMARY KEY (`itemnumber`),
1032 UNIQUE KEY `itembarcodeidx` (`barcode`),
1033 KEY `itemstocknumberidx` (`stocknumber`),
1034 KEY `itembinoidx` (`biblioitemnumber`),
1035 KEY `itembibnoidx` (`biblionumber`),
1036 KEY `homebranch` (`homebranch`),
1037 KEY `holdingbranch` (`holdingbranch`),
1038 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1039 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1040 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1041 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1044 -- Table structure for table `itemtypes`
1047 DROP TABLE IF EXISTS `itemtypes`;
1048 CREATE TABLE `itemtypes` (
1049 `itemtype` varchar(10) NOT NULL default '',
1050 `description` mediumtext,
1051 `rentalcharge` double(16,4) default NULL,
1052 `notforloan` smallint(6) default NULL,
1053 `imageurl` varchar(200) default NULL,
1055 PRIMARY KEY (`itemtype`),
1056 UNIQUE KEY `itemtype` (`itemtype`)
1057 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1060 -- Table structure for table `creator_batches`
1063 DROP TABLE IF EXISTS `creator_batches`;
1064 SET @saved_cs_client = @@character_set_client;
1065 SET character_set_client = utf8;
1066 CREATE TABLE `creator_batches` (
1067 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1068 `batch_id` int(10) NOT NULL DEFAULT '1',
1069 `item_number` int(11) DEFAULT NULL,
1070 `borrower_number` int(11) DEFAULT NULL,
1071 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1072 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1073 `creator` char(15) NOT NULL DEFAULT 'Labels',
1074 PRIMARY KEY (`label_id`),
1075 KEY `branch_fk_constraint` (`branch_code`),
1076 KEY `item_fk_constraint` (`item_number`),
1077 KEY `borrower_fk_constraint` (`borrower_number`),
1078 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1079 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1080 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1081 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1084 -- Table structure for table `creator_images`
1087 DROP TABLE IF EXISTS `creator_images`;
1088 SET @saved_cs_client = @@character_set_client;
1089 SET character_set_client = utf8;
1090 CREATE TABLE `creator_images` (
1091 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1092 `imagefile` mediumblob,
1093 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1094 PRIMARY KEY (`image_id`),
1095 UNIQUE KEY `image_name_index` (`image_name`)
1096 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1099 -- Table structure for table `creator_layouts`
1102 DROP TABLE IF EXISTS `creator_layouts`;
1103 SET @saved_cs_client = @@character_set_client;
1104 SET character_set_client = utf8;
1105 CREATE TABLE `creator_layouts` (
1106 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1107 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1108 `start_label` int(2) NOT NULL DEFAULT '1',
1109 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1110 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1111 `guidebox` int(1) DEFAULT '0',
1112 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1113 `font_size` int(4) NOT NULL DEFAULT '10',
1114 `units` char(20) NOT NULL DEFAULT 'POINT',
1115 `callnum_split` int(1) DEFAULT '0',
1116 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1117 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1118 `layout_xml` text NOT NULL,
1119 `creator` char(15) NOT NULL DEFAULT 'Labels',
1120 PRIMARY KEY (`layout_id`)
1121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1124 -- Table structure for table `creator_templates`
1127 DROP TABLE IF EXISTS `creator_templates`;
1128 SET @saved_cs_client = @@character_set_client;
1129 SET character_set_client = utf8;
1130 CREATE TABLE `creator_templates` (
1131 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1132 `profile_id` int(4) DEFAULT NULL,
1133 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1134 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1135 `page_width` float NOT NULL DEFAULT '0',
1136 `page_height` float NOT NULL DEFAULT '0',
1137 `label_width` float NOT NULL DEFAULT '0',
1138 `label_height` float NOT NULL DEFAULT '0',
1139 `top_text_margin` float NOT NULL DEFAULT '0',
1140 `left_text_margin` float NOT NULL DEFAULT '0',
1141 `top_margin` float NOT NULL DEFAULT '0',
1142 `left_margin` float NOT NULL DEFAULT '0',
1143 `cols` int(2) NOT NULL DEFAULT '0',
1144 `rows` int(2) NOT NULL DEFAULT '0',
1145 `col_gap` float NOT NULL DEFAULT '0',
1146 `row_gap` float NOT NULL DEFAULT '0',
1147 `units` char(20) NOT NULL DEFAULT 'POINT',
1148 `creator` char(15) NOT NULL DEFAULT 'Labels',
1149 PRIMARY KEY (`template_id`),
1150 KEY `template_profile_fk_constraint` (`profile_id`)
1151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1154 -- Table structure for table `letter`
1157 DROP TABLE IF EXISTS `letter`;
1158 CREATE TABLE `letter` (
1159 `module` varchar(20) NOT NULL default '',
1160 `code` varchar(20) NOT NULL default '',
1161 `name` varchar(100) NOT NULL default '',
1162 `title` varchar(200) NOT NULL default '',
1164 PRIMARY KEY (`module`,`code`)
1165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1168 -- Table structure for table `marc_subfield_structure`
1171 DROP TABLE IF EXISTS `marc_subfield_structure`;
1172 CREATE TABLE `marc_subfield_structure` (
1173 `tagfield` varchar(3) NOT NULL default '',
1174 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1175 `liblibrarian` varchar(255) NOT NULL default '',
1176 `libopac` varchar(255) NOT NULL default '',
1177 `repeatable` tinyint(4) NOT NULL default 0,
1178 `mandatory` tinyint(4) NOT NULL default 0,
1179 `kohafield` varchar(40) default NULL,
1180 `tab` tinyint(1) default NULL,
1181 `authorised_value` varchar(20) default NULL,
1182 `authtypecode` varchar(20) default NULL,
1183 `value_builder` varchar(80) default NULL,
1184 `isurl` tinyint(1) default NULL,
1185 `hidden` tinyint(1) default NULL,
1186 `frameworkcode` varchar(4) NOT NULL default '',
1187 `seealso` varchar(1100) default NULL,
1188 `link` varchar(80) default NULL,
1189 `defaultvalue` text default NULL,
1190 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1191 KEY `kohafield_2` (`kohafield`),
1192 KEY `tab` (`frameworkcode`,`tab`),
1193 KEY `kohafield` (`frameworkcode`,`kohafield`)
1194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1197 -- Table structure for table `marc_tag_structure`
1200 DROP TABLE IF EXISTS `marc_tag_structure`;
1201 CREATE TABLE `marc_tag_structure` (
1202 `tagfield` varchar(3) NOT NULL default '',
1203 `liblibrarian` varchar(255) NOT NULL default '',
1204 `libopac` varchar(255) NOT NULL default '',
1205 `repeatable` tinyint(4) NOT NULL default 0,
1206 `mandatory` tinyint(4) NOT NULL default 0,
1207 `authorised_value` varchar(10) default NULL,
1208 `frameworkcode` varchar(4) NOT NULL default '',
1209 PRIMARY KEY (`frameworkcode`,`tagfield`)
1210 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1213 -- Table structure for table `marc_matchers`
1216 DROP TABLE IF EXISTS `marc_matchers`;
1217 CREATE TABLE `marc_matchers` (
1218 `matcher_id` int(11) NOT NULL auto_increment,
1219 `code` varchar(10) NOT NULL default '',
1220 `description` varchar(255) NOT NULL default '',
1221 `record_type` varchar(10) NOT NULL default 'biblio',
1222 `threshold` int(11) NOT NULL default 0,
1223 PRIMARY KEY (`matcher_id`),
1224 KEY `code` (`code`),
1225 KEY `record_type` (`record_type`)
1226 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1229 -- Table structure for table `matchpoints`
1231 DROP TABLE IF EXISTS `matchpoints`;
1232 CREATE TABLE `matchpoints` (
1233 `matcher_id` int(11) NOT NULL,
1234 `matchpoint_id` int(11) NOT NULL auto_increment,
1235 `search_index` varchar(30) NOT NULL default '',
1236 `score` int(11) NOT NULL default 0,
1237 PRIMARY KEY (`matchpoint_id`),
1238 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1239 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1240 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1244 -- Table structure for table `matchpoint_components`
1246 DROP TABLE IF EXISTS `matchpoint_components`;
1247 CREATE TABLE `matchpoint_components` (
1248 `matchpoint_id` int(11) NOT NULL,
1249 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1250 sequence int(11) NOT NULL default 0,
1251 tag varchar(3) NOT NULL default '',
1252 subfields varchar(40) NOT NULL default '',
1253 offset int(4) NOT NULL default 0,
1254 length int(4) NOT NULL default 0,
1255 PRIMARY KEY (`matchpoint_component_id`),
1256 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1257 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1258 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1262 -- Table structure for table `matcher_component_norms`
1264 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1265 CREATE TABLE `matchpoint_component_norms` (
1266 `matchpoint_component_id` int(11) NOT NULL,
1267 `sequence` int(11) NOT NULL default 0,
1268 `norm_routine` varchar(50) NOT NULL default '',
1269 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1270 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1271 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1275 -- Table structure for table `matcher_matchpoints`
1277 DROP TABLE IF EXISTS `matcher_matchpoints`;
1278 CREATE TABLE `matcher_matchpoints` (
1279 `matcher_id` int(11) NOT NULL,
1280 `matchpoint_id` int(11) NOT NULL,
1281 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1282 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1283 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1284 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1285 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1288 -- Table structure for table `matchchecks`
1290 DROP TABLE IF EXISTS `matchchecks`;
1291 CREATE TABLE `matchchecks` (
1292 `matcher_id` int(11) NOT NULL,
1293 `matchcheck_id` int(11) NOT NULL auto_increment,
1294 `source_matchpoint_id` int(11) NOT NULL,
1295 `target_matchpoint_id` int(11) NOT NULL,
1296 PRIMARY KEY (`matchcheck_id`),
1297 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1298 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1299 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1300 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1301 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1302 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1303 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1306 -- Table structure for table `notifys`
1309 DROP TABLE IF EXISTS `notifys`;
1310 CREATE TABLE `notifys` (
1311 `notify_id` int(11) NOT NULL default 0,
1312 `borrowernumber` int(11) NOT NULL default 0,
1313 `itemnumber` int(11) NOT NULL default 0,
1314 `notify_date` date default NULL,
1315 `notify_send_date` date default NULL,
1316 `notify_level` int(1) NOT NULL default 0,
1317 `method` varchar(20) NOT NULL default ''
1318 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1321 -- Table structure for table `nozebra`
1324 DROP TABLE IF EXISTS `nozebra`;
1325 CREATE TABLE `nozebra` (
1326 `server` varchar(20) NOT NULL,
1327 `indexname` varchar(40) NOT NULL,
1328 `value` varchar(250) NOT NULL,
1329 `biblionumbers` longtext NOT NULL,
1330 KEY `indexname` (`server`,`indexname`),
1331 KEY `value` (`server`,`value`))
1332 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1335 -- Table structure for table `old_issues`
1338 DROP TABLE IF EXISTS `old_issues`;
1339 CREATE TABLE `old_issues` (
1340 `borrowernumber` int(11) default NULL,
1341 `itemnumber` int(11) default NULL,
1342 `date_due` date default NULL,
1343 `branchcode` varchar(10) default NULL,
1344 `issuingbranch` varchar(18) default NULL,
1345 `returndate` date default NULL,
1346 `lastreneweddate` date default NULL,
1347 `return` varchar(4) default NULL,
1348 `renewals` tinyint(4) default NULL,
1349 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1350 `issuedate` date default NULL,
1351 KEY `old_issuesborridx` (`borrowernumber`),
1352 KEY `old_issuesitemidx` (`itemnumber`),
1353 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1354 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1355 ON DELETE SET NULL ON UPDATE SET NULL,
1356 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1357 ON DELETE SET NULL ON UPDATE SET NULL
1358 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1361 -- Table structure for table `old_reserves`
1363 DROP TABLE IF EXISTS `old_reserves`;
1364 CREATE TABLE `old_reserves` (
1365 `borrowernumber` int(11) default NULL,
1366 `reservedate` date default NULL,
1367 `biblionumber` int(11) default NULL,
1368 `constrainttype` varchar(1) default NULL,
1369 `branchcode` varchar(10) default NULL,
1370 `notificationdate` date default NULL,
1371 `reminderdate` date default NULL,
1372 `cancellationdate` date default NULL,
1373 `reservenotes` mediumtext,
1374 `priority` smallint(6) default NULL,
1375 `found` varchar(1) default NULL,
1376 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1377 `itemnumber` int(11) default NULL,
1378 `waitingdate` date default NULL,
1379 `expirationdate` DATE DEFAULT NULL,
1380 `lowestPriority` tinyint(1) NOT NULL,
1381 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1382 KEY `old_reserves_biblionumber` (`biblionumber`),
1383 KEY `old_reserves_itemnumber` (`itemnumber`),
1384 KEY `old_reserves_branchcode` (`branchcode`),
1385 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1386 ON DELETE SET NULL ON UPDATE SET NULL,
1387 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1388 ON DELETE SET NULL ON UPDATE SET NULL,
1389 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1390 ON DELETE SET NULL ON UPDATE SET NULL
1391 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1394 -- Table structure for table `opac_news`
1397 DROP TABLE IF EXISTS `opac_news`;
1398 CREATE TABLE `opac_news` (
1399 `idnew` int(10) unsigned NOT NULL auto_increment,
1400 `title` varchar(250) NOT NULL default '',
1401 `new` text NOT NULL,
1402 `lang` varchar(25) NOT NULL default '',
1403 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1404 `expirationdate` date default NULL,
1405 `number` int(11) default NULL,
1406 PRIMARY KEY (`idnew`)
1407 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1410 -- Table structure for table `overduerules`
1413 DROP TABLE IF EXISTS `overduerules`;
1414 CREATE TABLE `overduerules` (
1415 `branchcode` varchar(10) NOT NULL default '',
1416 `categorycode` varchar(10) NOT NULL default '',
1417 `delay1` int(4) default NULL,
1418 `letter1` varchar(20) default NULL,
1419 `debarred1` varchar(1) default 0,
1420 `delay2` int(4) default NULL,
1421 `debarred2` varchar(1) default 0,
1422 `letter2` varchar(20) default NULL,
1423 `delay3` int(4) default NULL,
1424 `letter3` varchar(20) default NULL,
1425 `debarred3` int(1) default 0,
1426 PRIMARY KEY (`branchcode`,`categorycode`)
1427 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1430 -- Table structure for table `patroncards`
1433 DROP TABLE IF EXISTS `patroncards`;
1434 CREATE TABLE `patroncards` (
1435 `cardid` int(11) NOT NULL auto_increment,
1436 `batch_id` varchar(10) NOT NULL default '1',
1437 `borrowernumber` int(11) NOT NULL,
1438 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1439 PRIMARY KEY (`cardid`),
1440 KEY `patroncards_ibfk_1` (`borrowernumber`),
1441 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1442 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1445 -- Table structure for table `patronimage`
1448 DROP TABLE IF EXISTS `patronimage`;
1449 CREATE TABLE `patronimage` (
1450 `cardnumber` varchar(16) NOT NULL,
1451 `mimetype` varchar(15) NOT NULL,
1452 `imagefile` mediumblob NOT NULL,
1453 PRIMARY KEY (`cardnumber`),
1454 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1455 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1458 -- Table structure for table `printers`
1461 DROP TABLE IF EXISTS `printers`;
1462 CREATE TABLE `printers` (
1463 `printername` varchar(40) NOT NULL default '',
1464 `printqueue` varchar(20) default NULL,
1465 `printtype` varchar(20) default NULL,
1466 PRIMARY KEY (`printername`)
1467 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1470 -- Table structure for table `printers_profile`
1473 DROP TABLE IF EXISTS `printers_profile`;
1474 CREATE TABLE `printers_profile` (
1475 `profile_id` int(4) NOT NULL auto_increment,
1476 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1477 `template_id` int(4) NOT NULL default '0',
1478 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1479 `offset_horz` float NOT NULL default '0',
1480 `offset_vert` float NOT NULL default '0',
1481 `creep_horz` float NOT NULL default '0',
1482 `creep_vert` float NOT NULL default '0',
1483 `units` char(20) NOT NULL default 'POINT',
1484 `creator` char(15) NOT NULL DEFAULT 'Labels',
1485 PRIMARY KEY (`profile_id`),
1486 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
1487 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1490 -- Table structure for table `repeatable_holidays`
1493 DROP TABLE IF EXISTS `repeatable_holidays`;
1494 CREATE TABLE `repeatable_holidays` (
1495 `id` int(11) NOT NULL auto_increment,
1496 `branchcode` varchar(10) NOT NULL default '',
1497 `weekday` smallint(6) default NULL,
1498 `day` smallint(6) default NULL,
1499 `month` smallint(6) default NULL,
1500 `title` varchar(50) NOT NULL default '',
1501 `description` text NOT NULL,
1503 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1506 -- Table structure for table `reports_dictionary`
1509 DROP TABLE IF EXISTS `reports_dictionary`;
1510 CREATE TABLE reports_dictionary (
1511 `id` int(11) NOT NULL auto_increment,
1512 `name` varchar(255) default NULL,
1514 `date_created` datetime default NULL,
1515 `date_modified` datetime default NULL,
1517 `area` int(11) default NULL,
1519 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1522 -- Table structure for table `reserveconstraints`
1525 DROP TABLE IF EXISTS `reserveconstraints`;
1526 CREATE TABLE `reserveconstraints` (
1527 `borrowernumber` int(11) NOT NULL default 0,
1528 `reservedate` date default NULL,
1529 `biblionumber` int(11) NOT NULL default 0,
1530 `biblioitemnumber` int(11) default NULL,
1531 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1532 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1535 -- Table structure for table `reserves`
1538 DROP TABLE IF EXISTS `reserves`;
1539 CREATE TABLE `reserves` (
1540 `borrowernumber` int(11) NOT NULL default 0,
1541 `reservedate` date default NULL,
1542 `biblionumber` int(11) NOT NULL default 0,
1543 `constrainttype` varchar(1) default NULL,
1544 `branchcode` varchar(10) default NULL,
1545 `notificationdate` date default NULL,
1546 `reminderdate` date default NULL,
1547 `cancellationdate` date default NULL,
1548 `reservenotes` mediumtext,
1549 `priority` smallint(6) default NULL,
1550 `found` varchar(1) default NULL,
1551 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1552 `itemnumber` int(11) default NULL,
1553 `waitingdate` date default NULL,
1554 `expirationdate` DATE DEFAULT NULL,
1555 `lowestPriority` tinyint(1) NOT NULL,
1556 KEY `borrowernumber` (`borrowernumber`),
1557 KEY `biblionumber` (`biblionumber`),
1558 KEY `itemnumber` (`itemnumber`),
1559 KEY `branchcode` (`branchcode`),
1560 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1561 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1562 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1563 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1564 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1567 -- Table structure for table `reviews`
1570 DROP TABLE IF EXISTS `reviews`;
1571 CREATE TABLE `reviews` (
1572 `reviewid` int(11) NOT NULL auto_increment,
1573 `borrowernumber` int(11) default NULL,
1574 `biblionumber` int(11) default NULL,
1576 `approved` tinyint(4) default NULL,
1577 `datereviewed` datetime default NULL,
1578 PRIMARY KEY (`reviewid`)
1579 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1582 -- Table structure for table `roadtype`
1585 DROP TABLE IF EXISTS `roadtype`;
1586 CREATE TABLE `roadtype` (
1587 `roadtypeid` int(11) NOT NULL auto_increment,
1588 `road_type` varchar(100) NOT NULL default '',
1589 PRIMARY KEY (`roadtypeid`)
1590 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1593 -- Table structure for table `saved_sql`
1596 DROP TABLE IF EXISTS `saved_sql`;
1597 CREATE TABLE saved_sql (
1598 `id` int(11) NOT NULL auto_increment,
1599 `borrowernumber` int(11) default NULL,
1600 `date_created` datetime default NULL,
1601 `last_modified` datetime default NULL,
1603 `last_run` datetime default NULL,
1604 `report_name` varchar(255) default NULL,
1605 `type` varchar(255) default NULL,
1608 KEY boridx (`borrowernumber`)
1609 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1613 -- Table structure for `saved_reports`
1616 DROP TABLE IF EXISTS `saved_reports`;
1617 CREATE TABLE saved_reports (
1618 `id` int(11) NOT NULL auto_increment,
1619 `report_id` int(11) default NULL,
1621 `date_run` datetime default NULL,
1623 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1627 -- Table structure for table `search_history`
1630 DROP TABLE IF EXISTS `search_history`;
1631 CREATE TABLE IF NOT EXISTS `search_history` (
1632 `userid` int(11) NOT NULL,
1633 `sessionid` varchar(32) NOT NULL,
1634 `query_desc` varchar(255) NOT NULL,
1635 `query_cgi` varchar(255) NOT NULL,
1636 `total` int(11) NOT NULL,
1637 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1638 KEY `userid` (`userid`),
1639 KEY `sessionid` (`sessionid`)
1640 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1644 -- Table structure for table `serial`
1647 DROP TABLE IF EXISTS `serial`;
1648 CREATE TABLE `serial` (
1649 `serialid` int(11) NOT NULL auto_increment,
1650 `biblionumber` varchar(100) NOT NULL default '',
1651 `subscriptionid` varchar(100) NOT NULL default '',
1652 `serialseq` varchar(100) NOT NULL default '',
1653 `status` tinyint(4) NOT NULL default 0,
1654 `planneddate` date default NULL,
1656 `publisheddate` date default NULL,
1657 `itemnumber` text default NULL,
1658 `claimdate` date default NULL,
1659 `routingnotes` text,
1660 PRIMARY KEY (`serialid`)
1661 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1664 -- Table structure for table `sessions`
1667 DROP TABLE IF EXISTS sessions;
1668 CREATE TABLE sessions (
1669 `id` varchar(32) NOT NULL,
1670 `a_session` text NOT NULL,
1672 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1675 -- Table structure for table `special_holidays`
1678 DROP TABLE IF EXISTS `special_holidays`;
1679 CREATE TABLE `special_holidays` (
1680 `id` int(11) NOT NULL auto_increment,
1681 `branchcode` varchar(10) NOT NULL default '',
1682 `day` smallint(6) NOT NULL default 0,
1683 `month` smallint(6) NOT NULL default 0,
1684 `year` smallint(6) NOT NULL default 0,
1685 `isexception` smallint(1) NOT NULL default 1,
1686 `title` varchar(50) NOT NULL default '',
1687 `description` text NOT NULL,
1689 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1692 -- Table structure for table `statistics`
1695 DROP TABLE IF EXISTS `statistics`;
1696 CREATE TABLE `statistics` (
1697 `datetime` datetime default NULL,
1698 `branch` varchar(10) default NULL,
1699 `proccode` varchar(4) default NULL,
1700 `value` double(16,4) default NULL,
1701 `type` varchar(16) default NULL,
1703 `usercode` varchar(10) default NULL,
1704 `itemnumber` int(11) default NULL,
1705 `itemtype` varchar(10) default NULL,
1706 `borrowernumber` int(11) default NULL,
1707 `associatedborrower` int(11) default NULL,
1708 KEY `timeidx` (`datetime`)
1709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1712 -- Table structure for table `stopwords`
1715 DROP TABLE IF EXISTS `stopwords`;
1716 CREATE TABLE `stopwords` (
1717 `word` varchar(255) default NULL
1718 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1721 -- Table structure for table `subscription`
1724 DROP TABLE IF EXISTS `subscription`;
1725 CREATE TABLE `subscription` (
1726 `biblionumber` int(11) NOT NULL default 0,
1727 `subscriptionid` int(11) NOT NULL auto_increment,
1728 `librarian` varchar(100) default '',
1729 `startdate` date default NULL,
1730 `aqbooksellerid` int(11) default 0,
1731 `cost` int(11) default 0,
1732 `aqbudgetid` int(11) default 0,
1733 `weeklength` int(11) default 0,
1734 `monthlength` int(11) default 0,
1735 `numberlength` int(11) default 0,
1736 `periodicity` tinyint(4) default 0,
1737 `dow` varchar(100) default '',
1738 `numberingmethod` varchar(100) default '',
1740 `status` varchar(100) NOT NULL default '',
1741 `add1` int(11) default 0,
1742 `every1` int(11) default 0,
1743 `whenmorethan1` int(11) default 0,
1744 `setto1` int(11) default NULL,
1745 `lastvalue1` int(11) default NULL,
1746 `add2` int(11) default 0,
1747 `every2` int(11) default 0,
1748 `whenmorethan2` int(11) default 0,
1749 `setto2` int(11) default NULL,
1750 `lastvalue2` int(11) default NULL,
1751 `add3` int(11) default 0,
1752 `every3` int(11) default 0,
1753 `innerloop1` int(11) default 0,
1754 `innerloop2` int(11) default 0,
1755 `innerloop3` int(11) default 0,
1756 `whenmorethan3` int(11) default 0,
1757 `setto3` int(11) default NULL,
1758 `lastvalue3` int(11) default NULL,
1759 `issuesatonce` tinyint(3) NOT NULL default 1,
1760 `firstacquidate` date default NULL,
1761 `manualhistory` tinyint(1) NOT NULL default 0,
1762 `irregularity` text,
1763 `letter` varchar(20) default NULL,
1764 `numberpattern` tinyint(3) default 0,
1765 `distributedto` text,
1766 `internalnotes` longtext,
1768 `location` varchar(80) NULL default '',
1769 `branchcode` varchar(10) NOT NULL default '',
1770 `hemisphere` tinyint(3) default 0,
1771 `lastbranch` varchar(10),
1772 `serialsadditems` tinyint(1) NOT NULL default '0',
1773 `staffdisplaycount` VARCHAR(10) NULL,
1774 `opacdisplaycount` VARCHAR(10) NULL,
1775 `graceperiod` int(11) NOT NULL default '0',
1776 `enddate` date default NULL,
1777 PRIMARY KEY (`subscriptionid`)
1778 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1781 -- Table structure for table `subscriptionhistory`
1784 DROP TABLE IF EXISTS `subscriptionhistory`;
1785 CREATE TABLE `subscriptionhistory` (
1786 `biblionumber` int(11) NOT NULL default 0,
1787 `subscriptionid` int(11) NOT NULL default 0,
1788 `histstartdate` date default NULL,
1789 `histenddate` date default NULL,
1790 `missinglist` longtext NOT NULL,
1791 `recievedlist` longtext NOT NULL,
1792 `opacnote` varchar(150) NOT NULL default '',
1793 `librariannote` varchar(150) NOT NULL default '',
1794 PRIMARY KEY (`subscriptionid`),
1795 KEY `biblionumber` (`biblionumber`)
1796 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1799 -- Table structure for table `subscriptionroutinglist`
1802 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1803 CREATE TABLE `subscriptionroutinglist` (
1804 `routingid` int(11) NOT NULL auto_increment,
1805 `borrowernumber` int(11) NOT NULL,
1806 `ranking` int(11) default NULL,
1807 `subscriptionid` int(11) NOT NULL,
1808 PRIMARY KEY (`routingid`),
1809 UNIQUE (`subscriptionid`, `borrowernumber`),
1810 CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1811 ON DELETE CASCADE ON UPDATE CASCADE,
1812 CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
1813 ON DELETE CASCADE ON UPDATE CASCADE
1814 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1817 -- Table structure for table `suggestions`
1820 DROP TABLE IF EXISTS `suggestions`;
1821 CREATE TABLE `suggestions` (
1822 `suggestionid` int(8) NOT NULL auto_increment,
1823 `suggestedby` int(11) NOT NULL default 0,
1824 `suggesteddate` date NOT NULL default 0,
1825 `managedby` int(11) default NULL,
1826 `manageddate` date default NULL,
1827 acceptedby INT(11) default NULL,
1828 accepteddate date default NULL,
1829 rejectedby INT(11) default NULL,
1830 rejecteddate date default NULL,
1831 `STATUS` varchar(10) NOT NULL default '',
1833 `author` varchar(80) default NULL,
1834 `title` varchar(80) default NULL,
1835 `copyrightdate` smallint(6) default NULL,
1836 `publishercode` varchar(255) default NULL,
1837 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1838 `volumedesc` varchar(255) default NULL,
1839 `publicationyear` smallint(6) default 0,
1840 `place` varchar(255) default NULL,
1841 `isbn` varchar(30) default NULL,
1842 `mailoverseeing` smallint(1) default 0,
1843 `biblionumber` int(11) default NULL,
1846 branchcode VARCHAR(10) default NULL,
1847 collectiontitle text default NULL,
1848 itemtype VARCHAR(30) default NULL,
1849 quantity SMALLINT(6) default NULL,
1850 currency VARCHAR(3) default NULL,
1851 price DECIMAL(28,6) default NULL,
1852 total DECIMAL(28,6) default NULL,
1853 PRIMARY KEY (`suggestionid`),
1854 KEY `suggestedby` (`suggestedby`),
1855 KEY `managedby` (`managedby`)
1856 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1859 -- Table structure for table `systempreferences`
1862 DROP TABLE IF EXISTS `systempreferences`;
1863 CREATE TABLE `systempreferences` (
1864 `variable` varchar(50) NOT NULL default '',
1866 `options` mediumtext,
1868 `type` varchar(20) default NULL,
1869 PRIMARY KEY (`variable`)
1870 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1873 -- Table structure for table `tags`
1876 DROP TABLE IF EXISTS `tags`;
1877 CREATE TABLE `tags` (
1878 `entry` varchar(255) NOT NULL default '',
1879 `weight` bigint(20) NOT NULL default 0,
1880 PRIMARY KEY (`entry`)
1881 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1884 -- Table structure for table `tags_all`
1887 DROP TABLE IF EXISTS `tags_all`;
1888 CREATE TABLE `tags_all` (
1889 `tag_id` int(11) NOT NULL auto_increment,
1890 `borrowernumber` int(11) NOT NULL,
1891 `biblionumber` int(11) NOT NULL,
1892 `term` varchar(255) NOT NULL,
1893 `language` int(4) default NULL,
1894 `date_created` datetime NOT NULL,
1895 PRIMARY KEY (`tag_id`),
1896 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1897 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1898 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1899 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1900 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1901 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1902 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1905 -- Table structure for table `tags_approval`
1908 DROP TABLE IF EXISTS `tags_approval`;
1909 CREATE TABLE `tags_approval` (
1910 `term` varchar(255) NOT NULL,
1911 `approved` int(1) NOT NULL default '0',
1912 `date_approved` datetime default NULL,
1913 `approved_by` int(11) default NULL,
1914 `weight_total` int(9) NOT NULL default '1',
1915 PRIMARY KEY (`term`),
1916 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1917 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1918 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1919 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1922 -- Table structure for table `tags_index`
1925 DROP TABLE IF EXISTS `tags_index`;
1926 CREATE TABLE `tags_index` (
1927 `term` varchar(255) NOT NULL,
1928 `biblionumber` int(11) NOT NULL,
1929 `weight` int(9) NOT NULL default '1',
1930 PRIMARY KEY (`term`,`biblionumber`),
1931 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1932 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1933 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1934 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1935 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1936 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1939 -- Table structure for table `userflags`
1942 DROP TABLE IF EXISTS `userflags`;
1943 CREATE TABLE `userflags` (
1944 `bit` int(11) NOT NULL default 0,
1945 `flag` varchar(30) default NULL,
1946 `flagdesc` varchar(255) default NULL,
1947 `defaulton` int(11) default NULL,
1949 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1952 -- Table structure for table `virtualshelves`
1955 DROP TABLE IF EXISTS `virtualshelves`;
1956 CREATE TABLE `virtualshelves` (
1957 `shelfnumber` int(11) NOT NULL auto_increment,
1958 `shelfname` varchar(255) default NULL,
1959 `owner` varchar(80) default NULL,
1960 `category` varchar(1) default NULL,
1961 `sortfield` varchar(16) default NULL,
1962 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1963 PRIMARY KEY (`shelfnumber`)
1964 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1967 -- Table structure for table `virtualshelfcontents`
1970 DROP TABLE IF EXISTS `virtualshelfcontents`;
1971 CREATE TABLE `virtualshelfcontents` (
1972 `shelfnumber` int(11) NOT NULL default 0,
1973 `biblionumber` int(11) NOT NULL default 0,
1974 `flags` int(11) default NULL,
1975 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1976 KEY `shelfnumber` (`shelfnumber`),
1977 KEY `biblionumber` (`biblionumber`),
1978 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1979 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1980 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1983 -- Table structure for table `z3950servers`
1986 DROP TABLE IF EXISTS `z3950servers`;
1987 CREATE TABLE `z3950servers` (
1988 `host` varchar(255) default NULL,
1989 `port` int(11) default NULL,
1990 `db` varchar(255) default NULL,
1991 `userid` varchar(255) default NULL,
1992 `password` varchar(255) default NULL,
1994 `id` int(11) NOT NULL auto_increment,
1995 `checked` smallint(6) default NULL,
1996 `rank` int(11) default NULL,
1997 `syntax` varchar(80) default NULL,
1999 `position` enum('primary','secondary','') NOT NULL default 'primary',
2000 `type` enum('zed','opensearch') NOT NULL default 'zed',
2001 `encoding` text default NULL,
2002 `description` text NOT NULL,
2004 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2007 -- Table structure for table `zebraqueue`
2010 DROP TABLE IF EXISTS `zebraqueue`;
2011 CREATE TABLE `zebraqueue` (
2012 `id` int(11) NOT NULL auto_increment,
2013 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2014 `operation` char(20) NOT NULL default '',
2015 `server` char(20) NOT NULL default '',
2016 `done` int(11) NOT NULL default '0',
2017 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2019 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2020 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2022 DROP TABLE IF EXISTS `services_throttle`;
2023 CREATE TABLE `services_throttle` (
2024 `service_type` varchar(10) NOT NULL default '',
2025 `service_count` varchar(45) default NULL,
2026 PRIMARY KEY (`service_type`)
2027 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2029 -- http://www.w3.org/International/articles/language-tags/
2032 DROP TABLE IF EXISTS language_subtag_registry;
2033 CREATE TABLE language_subtag_registry (
2035 type varchar(25), -- language-script-region-variant-extension-privateuse
2036 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2038 id int(11) NOT NULL auto_increment,
2040 KEY `subtag` (`subtag`)
2041 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2043 -- TODO: add suppress_scripts
2044 -- this maps three letter codes defined in iso639.2 back to their
2045 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2046 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2047 CREATE TABLE language_rfc4646_to_iso639 (
2048 rfc4646_subtag varchar(25),
2049 iso639_2_code varchar(25),
2050 id int(11) NOT NULL auto_increment,
2052 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2053 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2055 DROP TABLE IF EXISTS language_descriptions;
2056 CREATE TABLE language_descriptions (
2060 description varchar(255),
2061 id int(11) NOT NULL auto_increment,
2063 KEY `lang` (`lang`),
2064 KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
2065 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2067 -- bi-directional support, keyed by script subcode
2068 DROP TABLE IF EXISTS language_script_bidi;
2069 CREATE TABLE language_script_bidi (
2070 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2071 bidi varchar(3), -- rtl ltr
2072 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2073 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2075 -- TODO: need to map language subtags to script subtags for detection
2076 -- of bidi when script is not specified (like ar, he)
2077 DROP TABLE IF EXISTS language_script_mapping;
2078 CREATE TABLE language_script_mapping (
2079 language_subtag varchar(25),
2080 script_subtag varchar(25),
2081 KEY `language_subtag` (`language_subtag`)
2082 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2084 DROP TABLE IF EXISTS `permissions`;
2085 CREATE TABLE `permissions` (
2086 `module_bit` int(11) NOT NULL DEFAULT 0,
2087 `code` varchar(64) DEFAULT NULL,
2088 `description` varchar(255) DEFAULT NULL,
2089 PRIMARY KEY (`module_bit`, `code`),
2090 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2091 ON DELETE CASCADE ON UPDATE CASCADE
2092 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2094 DROP TABLE IF EXISTS `serialitems`;
2095 CREATE TABLE `serialitems` (
2096 `itemnumber` int(11) NOT NULL,
2097 `serialid` int(11) NOT NULL,
2098 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2099 KEY `serialitems_sfk_1` (`serialid`),
2100 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
2101 CONSTRAINT serialitems_sfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
2102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2104 DROP TABLE IF EXISTS `user_permissions`;
2105 CREATE TABLE `user_permissions` (
2106 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2107 `module_bit` int(11) NOT NULL DEFAULT 0,
2108 `code` varchar(64) DEFAULT NULL,
2109 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2110 ON DELETE CASCADE ON UPDATE CASCADE,
2111 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2112 ON DELETE CASCADE ON UPDATE CASCADE
2113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2116 -- Table structure for table `tmp_holdsqueue`
2119 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2120 CREATE TABLE `tmp_holdsqueue` (
2121 `biblionumber` int(11) default NULL,
2122 `itemnumber` int(11) default NULL,
2123 `barcode` varchar(20) default NULL,
2124 `surname` mediumtext NOT NULL,
2127 `borrowernumber` int(11) NOT NULL,
2128 `cardnumber` varchar(16) default NULL,
2129 `reservedate` date default NULL,
2131 `itemcallnumber` varchar(255) default NULL,
2132 `holdingbranch` varchar(10) default NULL,
2133 `pickbranch` varchar(10) default NULL,
2135 `item_level_request` tinyint(4) NOT NULL default 0
2136 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2139 -- Table structure for table `message_queue`
2142 DROP TABLE IF EXISTS `message_queue`;
2143 CREATE TABLE `message_queue` (
2144 `message_id` int(11) NOT NULL auto_increment,
2145 `borrowernumber` int(11) default NULL,
2148 `metadata` text DEFAULT NULL,
2149 `letter_code` varchar(64) DEFAULT NULL,
2150 `message_transport_type` varchar(20) NOT NULL,
2151 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2152 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2153 `to_address` mediumtext,
2154 `from_address` mediumtext,
2155 `content_type` text,
2156 KEY `message_id` (`message_id`),
2157 KEY `borrowernumber` (`borrowernumber`),
2158 KEY `message_transport_type` (`message_transport_type`),
2159 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2160 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2161 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2164 -- Table structure for table `message_transport_types`
2167 DROP TABLE IF EXISTS `message_transport_types`;
2168 CREATE TABLE `message_transport_types` (
2169 `message_transport_type` varchar(20) NOT NULL,
2170 PRIMARY KEY (`message_transport_type`)
2171 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2174 -- Table structure for table `message_attributes`
2177 DROP TABLE IF EXISTS `message_attributes`;
2178 CREATE TABLE `message_attributes` (
2179 `message_attribute_id` int(11) NOT NULL auto_increment,
2180 `message_name` varchar(40) NOT NULL default '',
2181 `takes_days` tinyint(1) NOT NULL default '0',
2182 PRIMARY KEY (`message_attribute_id`),
2183 UNIQUE KEY `message_name` (`message_name`)
2184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2187 -- Table structure for table `message_transports`
2190 DROP TABLE IF EXISTS `message_transports`;
2191 CREATE TABLE `message_transports` (
2192 `message_attribute_id` int(11) NOT NULL,
2193 `message_transport_type` varchar(20) NOT NULL,
2194 `is_digest` tinyint(1) NOT NULL default '0',
2195 `letter_module` varchar(20) NOT NULL default '',
2196 `letter_code` varchar(20) NOT NULL default '',
2197 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2198 KEY `message_transport_type` (`message_transport_type`),
2199 KEY `letter_module` (`letter_module`,`letter_code`),
2200 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2201 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2202 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2203 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2206 -- Table structure for table `borrower_message_preferences`
2209 DROP TABLE IF EXISTS `borrower_message_preferences`;
2210 CREATE TABLE `borrower_message_preferences` (
2211 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2212 `borrowernumber` int(11) default NULL,
2213 `categorycode` varchar(10) default NULL,
2214 `message_attribute_id` int(11) default '0',
2215 `days_in_advance` int(11) default '0',
2216 `wants_digest` tinyint(1) NOT NULL default '0',
2217 PRIMARY KEY (`borrower_message_preference_id`),
2218 KEY `borrowernumber` (`borrowernumber`),
2219 KEY `categorycode` (`categorycode`),
2220 KEY `message_attribute_id` (`message_attribute_id`),
2221 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2222 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2223 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2224 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2227 -- Table structure for table `borrower_message_transport_preferences`
2230 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2231 CREATE TABLE `borrower_message_transport_preferences` (
2232 `borrower_message_preference_id` int(11) NOT NULL default '0',
2233 `message_transport_type` varchar(20) NOT NULL default '0',
2234 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2235 KEY `message_transport_type` (`message_transport_type`),
2236 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,
2237 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
2238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2241 -- Table structure for the table branch_transfer_limits
2244 DROP TABLE IF EXISTS `branch_transfer_limits`;
2245 CREATE TABLE branch_transfer_limits (
2246 limitId int(8) NOT NULL auto_increment,
2247 toBranch varchar(10) NOT NULL,
2248 fromBranch varchar(10) NOT NULL,
2249 itemtype varchar(10) NULL,
2250 ccode varchar(10) NULL,
2251 PRIMARY KEY (limitId)
2252 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2255 -- Table structure for table `item_circulation_alert_preferences`
2258 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2259 CREATE TABLE `item_circulation_alert_preferences` (
2260 `id` int(11) NOT NULL auto_increment,
2261 `branchcode` varchar(10) NOT NULL,
2262 `categorycode` varchar(10) NOT NULL,
2263 `item_type` varchar(10) NOT NULL,
2264 `notification` varchar(16) NOT NULL,
2266 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2267 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2270 -- Table structure for table `messages`
2272 DROP TABLE IF EXISTS `messages`;
2273 CREATE TABLE `messages` (
2274 `message_id` int(11) NOT NULL auto_increment,
2275 `borrowernumber` int(11) NOT NULL,
2276 `branchcode` varchar(10) default NULL,
2277 `message_type` varchar(1) NOT NULL,
2278 `message` text NOT NULL,
2279 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2280 PRIMARY KEY (`message_id`)
2281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2284 -- Table structure for table `accountlines`
2287 DROP TABLE IF EXISTS `accountlines`;
2288 CREATE TABLE `accountlines` (
2289 `borrowernumber` int(11) NOT NULL default 0,
2290 `accountno` smallint(6) NOT NULL default 0,
2291 `itemnumber` int(11) default NULL,
2292 `date` date default NULL,
2293 `amount` decimal(28,6) default NULL,
2294 `description` mediumtext,
2295 `dispute` mediumtext,
2296 `accounttype` varchar(5) default NULL,
2297 `amountoutstanding` decimal(28,6) default NULL,
2298 `lastincrement` decimal(28,6) default NULL,
2299 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2300 `notify_id` int(11) NOT NULL default 0,
2301 `notify_level` int(2) NOT NULL default 0,
2302 KEY `acctsborridx` (`borrowernumber`),
2303 KEY `timeidx` (`timestamp`),
2304 KEY `itemnumber` (`itemnumber`),
2305 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2306 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2310 -- Table structure for table `accountoffsets`
2313 DROP TABLE IF EXISTS `accountoffsets`;
2314 CREATE TABLE `accountoffsets` (
2315 `borrowernumber` int(11) NOT NULL default 0,
2316 `accountno` smallint(6) NOT NULL default 0,
2317 `offsetaccount` smallint(6) NOT NULL default 0,
2318 `offsetamount` decimal(28,6) default NULL,
2319 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2320 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2321 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2324 -- Table structure for table `action_logs`
2327 DROP TABLE IF EXISTS `action_logs`;
2328 CREATE TABLE `action_logs` (
2329 `action_id` int(11) NOT NULL auto_increment,
2330 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2331 `user` int(11) NOT NULL default 0,
2334 `object` int(11) default NULL,
2336 PRIMARY KEY (`action_id`),
2337 KEY (`timestamp`,`user`)
2338 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2341 -- Table structure for table `alert`
2344 DROP TABLE IF EXISTS `alert`;
2345 CREATE TABLE `alert` (
2346 `alertid` int(11) NOT NULL auto_increment,
2347 `borrowernumber` int(11) NOT NULL default 0,
2348 `type` varchar(10) NOT NULL default '',
2349 `externalid` varchar(20) NOT NULL default '',
2350 PRIMARY KEY (`alertid`),
2351 KEY `borrowernumber` (`borrowernumber`),
2352 KEY `type` (`type`,`externalid`)
2353 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2356 -- Table structure for table `aqbasketgroups`
2359 DROP TABLE IF EXISTS `aqbasketgroups`;
2360 CREATE TABLE `aqbasketgroups` (
2361 `id` int(11) NOT NULL auto_increment,
2362 `name` varchar(50) default NULL,
2363 `closed` tinyint(1) default NULL,
2364 `booksellerid` int(11) NOT NULL,
2365 `deliveryplace` varchar(10) default NULL,
2366 `freedeliveryplace` text default NULL,
2367 `deliverycomment` varchar(255) default NULL,
2368 `billingplace` varchar(10) default NULL,
2370 KEY `booksellerid` (`booksellerid`),
2371 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2372 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2375 -- Table structure for table `aqbasket`
2378 DROP TABLE IF EXISTS `aqbasket`;
2379 CREATE TABLE `aqbasket` (
2380 `basketno` int(11) NOT NULL auto_increment,
2381 `basketname` varchar(50) default NULL,
2383 `booksellernote` mediumtext,
2384 `contractnumber` int(11),
2385 `creationdate` date default NULL,
2386 `closedate` date default NULL,
2387 `booksellerid` int(11) NOT NULL default 1,
2388 `authorisedby` varchar(10) default NULL,
2389 `booksellerinvoicenumber` mediumtext,
2390 `basketgroupid` int(11),
2391 PRIMARY KEY (`basketno`),
2392 KEY `booksellerid` (`booksellerid`),
2393 KEY `basketgroupid` (`basketgroupid`),
2394 KEY `contractnumber` (`contractnumber`),
2395 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2396 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2397 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2398 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2401 -- Table structure for table `aqbooksellers`
2404 DROP TABLE IF EXISTS `aqbooksellers`;
2405 CREATE TABLE `aqbooksellers` (
2406 `id` int(11) NOT NULL auto_increment,
2407 `name` mediumtext NOT NULL,
2408 `address1` mediumtext,
2409 `address2` mediumtext,
2410 `address3` mediumtext,
2411 `address4` mediumtext,
2412 `phone` varchar(30) default NULL,
2413 `accountnumber` mediumtext,
2414 `othersupplier` mediumtext,
2415 `currency` varchar(3) NOT NULL default '',
2416 `booksellerfax` mediumtext,
2418 `bookselleremail` mediumtext,
2419 `booksellerurl` mediumtext,
2420 `contact` varchar(100) default NULL,
2421 `postal` mediumtext,
2422 `url` varchar(255) default NULL,
2423 `contpos` varchar(100) default NULL,
2424 `contphone` varchar(100) default NULL,
2425 `contfax` varchar(100) default NULL,
2426 `contaltphone` varchar(100) default NULL,
2427 `contemail` varchar(100) default NULL,
2428 `contnotes` mediumtext,
2429 `active` tinyint(4) default NULL,
2430 `listprice` varchar(10) default NULL,
2431 `invoiceprice` varchar(10) default NULL,
2432 `gstreg` tinyint(4) default NULL,
2433 `listincgst` tinyint(4) default NULL,
2434 `invoiceincgst` tinyint(4) default NULL,
2435 `gstrate` decimal(6,4) default NULL,
2436 `discount` float(6,4) default NULL,
2437 `fax` varchar(50) default NULL,
2439 KEY `listprice` (`listprice`),
2440 KEY `invoiceprice` (`invoiceprice`),
2441 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2442 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2443 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2446 -- Table structure for table `aqbudgets`
2449 DROP TABLE IF EXISTS `aqbudgets`;
2450 CREATE TABLE `aqbudgets` (
2451 `budget_id` int(11) NOT NULL auto_increment,
2452 `budget_parent_id` int(11) default NULL,
2453 `budget_code` varchar(30) default NULL,
2454 `budget_name` varchar(80) default NULL,
2455 `budget_branchcode` varchar(10) default NULL,
2456 `budget_amount` decimal(28,6) NULL default '0.00',
2457 `budget_encumb` decimal(28,6) NULL default '0.00',
2458 `budget_expend` decimal(28,6) NULL default '0.00',
2459 `budget_notes` mediumtext,
2460 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2461 `budget_period_id` int(11) default NULL,
2462 `sort1_authcat` varchar(80) default NULL,
2463 `sort2_authcat` varchar(80) default NULL,
2464 `budget_owner_id` int(11) default NULL,
2465 `budget_permission` int(1) default '0',
2466 PRIMARY KEY (`budget_id`)
2467 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2471 -- Table structure for table `aqbudgetperiods`
2475 DROP TABLE IF EXISTS `aqbudgetperiods`;
2476 CREATE TABLE `aqbudgetperiods` (
2477 `budget_period_id` int(11) NOT NULL auto_increment,
2478 `budget_period_startdate` date NOT NULL,
2479 `budget_period_enddate` date NOT NULL,
2480 `budget_period_active` tinyint(1) default '0',
2481 `budget_period_description` mediumtext,
2482 `budget_period_total` decimal(28,6),
2483 `budget_period_locked` tinyint(1) default NULL,
2484 `sort1_authcat` varchar(10) default NULL,
2485 `sort2_authcat` varchar(10) default NULL,
2486 PRIMARY KEY (`budget_period_id`)
2487 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2490 -- Table structure for table `aqbudgets_planning`
2493 DROP TABLE IF EXISTS `aqbudgets_planning`;
2494 CREATE TABLE `aqbudgets_planning` (
2495 `plan_id` int(11) NOT NULL auto_increment,
2496 `budget_id` int(11) NOT NULL,
2497 `budget_period_id` int(11) NOT NULL,
2498 `estimated_amount` decimal(28,6) default NULL,
2499 `authcat` varchar(30) NOT NULL,
2500 `authvalue` varchar(30) NOT NULL,
2501 `display` tinyint(1) DEFAULT 1,
2502 PRIMARY KEY (`plan_id`),
2503 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2504 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2507 -- Table structure for table 'aqcontract'
2510 DROP TABLE IF EXISTS `aqcontract`;
2511 CREATE TABLE `aqcontract` (
2512 `contractnumber` int(11) NOT NULL auto_increment,
2513 `contractstartdate` date default NULL,
2514 `contractenddate` date default NULL,
2515 `contractname` varchar(50) default NULL,
2516 `contractdescription` mediumtext,
2517 `booksellerid` int(11) not NULL,
2518 PRIMARY KEY (`contractnumber`),
2519 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2520 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2521 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2524 -- Table structure for table `aqorderdelivery`
2527 DROP TABLE IF EXISTS `aqorderdelivery`;
2528 CREATE TABLE `aqorderdelivery` (
2529 `ordernumber` date default NULL,
2530 `deliverynumber` smallint(6) NOT NULL default 0,
2531 `deliverydate` varchar(18) default NULL,
2532 `qtydelivered` smallint(6) default NULL,
2533 `deliverycomments` mediumtext
2534 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2537 -- Table structure for table `aqorders`
2540 DROP TABLE IF EXISTS `aqorders`;
2541 CREATE TABLE `aqorders` (
2542 `ordernumber` int(11) NOT NULL auto_increment,
2543 `biblionumber` int(11) default NULL,
2544 `entrydate` date default NULL,
2545 `quantity` smallint(6) default NULL,
2546 `currency` varchar(3) default NULL,
2547 `listprice` decimal(28,6) default NULL,
2548 `totalamount` decimal(28,6) default NULL,
2549 `datereceived` date default NULL,
2550 `booksellerinvoicenumber` mediumtext,
2551 `freight` decimal(28,6) default NULL,
2552 `unitprice` decimal(28,6) default NULL,
2553 `quantityreceived` smallint(6) NOT NULL default 0,
2554 `cancelledby` varchar(10) default NULL,
2555 `datecancellationprinted` date default NULL,
2557 `supplierreference` mediumtext,
2558 `purchaseordernumber` mediumtext,
2559 `subscription` tinyint(1) default NULL,
2560 `serialid` varchar(30) default NULL,
2561 `basketno` int(11) default NULL,
2562 `biblioitemnumber` int(11) default NULL,
2563 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2564 `rrp` decimal(13,2) default NULL,
2565 `ecost` decimal(13,2) default NULL,
2566 `gst` decimal(13,2) default NULL,
2567 `budget_id` int(11) NOT NULL,
2568 `budgetgroup_id` int(11) NOT NULL,
2569 `budgetdate` date default NULL,
2570 `sort1` varchar(80) default NULL,
2571 `sort2` varchar(80) default NULL,
2572 `sort1_authcat` varchar(10) default NULL,
2573 `sort2_authcat` varchar(10) default NULL,
2574 `uncertainprice` tinyint(1),
2575 PRIMARY KEY (`ordernumber`),
2576 KEY `basketno` (`basketno`),
2577 KEY `biblionumber` (`biblionumber`),
2578 KEY `budget_id` (`budget_id`),
2579 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2580 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2581 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2585 -- Table structure for table `aqorders_items`
2588 DROP TABLE IF EXISTS `aqorders_items`;
2589 CREATE TABLE `aqorders_items` (
2590 `ordernumber` int(11) NOT NULL,
2591 `itemnumber` int(11) NOT NULL,
2592 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2593 PRIMARY KEY (`itemnumber`),
2594 KEY `ordernumber` (`ordernumber`)
2595 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2598 -- Table structure for table `fieldmapping`
2601 DROP TABLE IF EXISTS `fieldmapping`;
2602 CREATE TABLE `fieldmapping` (
2603 `id` int(11) NOT NULL auto_increment,
2604 `field` varchar(255) NOT NULL,
2605 `frameworkcode` char(4) NOT NULL default '',
2606 `fieldcode` char(3) NOT NULL,
2607 `subfieldcode` char(1) NOT NULL,
2609 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2612 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2613 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2614 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2615 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2616 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2617 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2618 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2619 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;