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` mediumtext 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` mediumtext 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` mediumtext 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 `branchcountry` text,
349 `branchphone` mediumtext,
350 `branchfax` mediumtext,
351 `branchemail` mediumtext,
352 `branchurl` mediumtext,
353 `issuing` tinyint(4) default NULL,
354 `branchip` varchar(15) default NULL,
355 `branchprinter` varchar(100) default NULL,
356 `branchnotes` mediumtext,
357 UNIQUE KEY `branchcode` (`branchcode`)
358 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
361 -- Table structure for table `branchrelations`
364 DROP TABLE IF EXISTS `branchrelations`;
365 CREATE TABLE `branchrelations` (
366 `branchcode` varchar(10) NOT NULL default '',
367 `categorycode` varchar(10) NOT NULL default '',
368 PRIMARY KEY (`branchcode`,`categorycode`),
369 KEY `branchcode` (`branchcode`),
370 KEY `categorycode` (`categorycode`),
371 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
372 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
373 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
376 -- Table structure for table `branchtransfers`
379 DROP TABLE IF EXISTS `branchtransfers`;
380 CREATE TABLE `branchtransfers` (
381 `itemnumber` int(11) NOT NULL default 0,
382 `datesent` datetime default NULL,
383 `frombranch` varchar(10) NOT NULL default '',
384 `datearrived` datetime default NULL,
385 `tobranch` varchar(10) NOT NULL default '',
386 `comments` mediumtext,
387 KEY `frombranch` (`frombranch`),
388 KEY `tobranch` (`tobranch`),
389 KEY `itemnumber` (`itemnumber`),
390 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
391 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
392 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
393 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
397 -- Table structure for table `browser`
399 DROP TABLE IF EXISTS `browser`;
400 CREATE TABLE `browser` (
401 `level` int(11) NOT NULL,
402 `classification` varchar(20) NOT NULL,
403 `description` varchar(255) NOT NULL,
404 `number` bigint(20) NOT NULL,
405 `endnode` tinyint(4) NOT NULL
406 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
409 -- Table structure for table `categories`
412 DROP TABLE IF EXISTS `categories`;
413 CREATE TABLE `categories` (
414 `categorycode` varchar(10) NOT NULL default '',
415 `description` mediumtext,
416 `enrolmentperiod` smallint(6) default NULL,
417 `enrolmentperioddate` DATE NULL DEFAULT NULL,
418 `upperagelimit` smallint(6) default NULL,
419 `dateofbirthrequired` tinyint(1) default NULL,
420 `finetype` varchar(30) default NULL,
421 `bulk` tinyint(1) default NULL,
422 `enrolmentfee` decimal(28,6) default NULL,
423 `overduenoticerequired` tinyint(1) default NULL,
424 `issuelimit` smallint(6) default NULL,
425 `reservefee` decimal(28,6) default NULL,
426 `hidelostitems` tinyint(1) NOT NULL default '0',
427 `category_type` varchar(1) NOT NULL default 'A',
428 PRIMARY KEY (`categorycode`),
429 UNIQUE KEY `categorycode` (`categorycode`)
430 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
433 -- Table: collections
435 CREATE TABLE collections (
436 colId integer(11) NOT NULL auto_increment,
437 colTitle varchar(100) NOT NULL DEFAULT '',
438 colDesc text NOT NULL,
439 colBranchcode varchar(4) DEFAULT NULL comment 'branchcode for branch where item should be held.',
441 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
444 -- Table: collections_tracking
446 CREATE TABLE collections_tracking (
447 ctId integer(11) NOT NULL auto_increment,
448 colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId',
449 itemnumber integer(11) NOT NULL DEFAULT 0 comment 'items.itemnumber',
451 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
454 -- Table structure for table `borrower_branch_circ_rules`
457 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
458 CREATE TABLE `branch_borrower_circ_rules` (
459 `branchcode` VARCHAR(10) NOT NULL,
460 `categorycode` VARCHAR(10) NOT NULL,
461 `maxissueqty` int(4) default NULL,
462 PRIMARY KEY (`categorycode`, `branchcode`),
463 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
464 ON DELETE CASCADE ON UPDATE CASCADE,
465 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
466 ON DELETE CASCADE ON UPDATE CASCADE
467 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
470 -- Table structure for table `default_borrower_circ_rules`
473 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
474 CREATE TABLE `default_borrower_circ_rules` (
475 `categorycode` VARCHAR(10) NOT NULL,
476 `maxissueqty` int(4) default NULL,
477 PRIMARY KEY (`categorycode`),
478 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
479 ON DELETE CASCADE ON UPDATE CASCADE
480 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
483 -- Table structure for table `default_branch_circ_rules`
486 DROP TABLE IF EXISTS `default_branch_circ_rules`;
487 CREATE TABLE `default_branch_circ_rules` (
488 `branchcode` VARCHAR(10) NOT NULL,
489 `maxissueqty` int(4) default NULL,
490 `holdallowed` tinyint(1) default NULL,
491 PRIMARY KEY (`branchcode`),
492 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
493 ON DELETE CASCADE ON UPDATE CASCADE
494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
497 -- Table structure for table `default_branch_item_rules`
499 DROP TABLE IF EXISTS `default_branch_item_rules`;
500 CREATE TABLE `default_branch_item_rules` (
501 `itemtype` varchar(10) NOT NULL,
502 `holdallowed` tinyint(1) default NULL,
503 PRIMARY KEY (`itemtype`),
504 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
505 ON DELETE CASCADE ON UPDATE CASCADE
506 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
509 -- Table structure for table `default_circ_rules`
512 DROP TABLE IF EXISTS `default_circ_rules`;
513 CREATE TABLE `default_circ_rules` (
514 `singleton` enum('singleton') NOT NULL default 'singleton',
515 `maxissueqty` int(4) default NULL,
516 `holdallowed` int(1) default NULL,
517 PRIMARY KEY (`singleton`)
518 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
521 -- Table structure for table `cities`
524 DROP TABLE IF EXISTS `cities`;
525 CREATE TABLE `cities` (
526 `cityid` int(11) NOT NULL auto_increment,
527 `city_name` varchar(100) NOT NULL default '',
528 `city_zipcode` varchar(20) default NULL,
529 PRIMARY KEY (`cityid`)
530 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
533 -- Table structure for table `class_sort_rules`
536 DROP TABLE IF EXISTS `class_sort_rules`;
537 CREATE TABLE `class_sort_rules` (
538 `class_sort_rule` varchar(10) NOT NULL default '',
539 `description` mediumtext,
540 `sort_routine` varchar(30) NOT NULL default '',
541 PRIMARY KEY (`class_sort_rule`),
542 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
546 -- Table structure for table `class_sources`
549 DROP TABLE IF EXISTS `class_sources`;
550 CREATE TABLE `class_sources` (
551 `cn_source` varchar(10) NOT NULL default '',
552 `description` mediumtext,
553 `used` tinyint(4) NOT NULL default 0,
554 `class_sort_rule` varchar(10) NOT NULL default '',
555 PRIMARY KEY (`cn_source`),
556 UNIQUE KEY `cn_source_idx` (`cn_source`),
557 KEY `used_idx` (`used`),
558 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
559 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
562 -- Table structure for table `currency`
565 DROP TABLE IF EXISTS `currency`;
566 CREATE TABLE `currency` (
567 `currency` varchar(10) NOT NULL default '',
568 `symbol` varchar(5) default NULL,
569 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
570 `rate` float(15,5) default NULL,
571 `active` tinyint(1) default NULL,
572 PRIMARY KEY (`currency`)
573 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
576 -- Table structure for table `deletedbiblio`
579 DROP TABLE IF EXISTS `deletedbiblio`;
580 CREATE TABLE `deletedbiblio` (
581 `biblionumber` int(11) NOT NULL default 0,
582 `frameworkcode` varchar(4) NOT NULL default '',
585 `unititle` mediumtext,
587 `serial` tinyint(1) default NULL,
588 `seriestitle` mediumtext,
589 `copyrightdate` smallint(6) default NULL,
590 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
591 `datecreated` DATE NOT NULL,
592 `abstract` mediumtext,
593 PRIMARY KEY (`biblionumber`),
594 KEY `blbnoidx` (`biblionumber`)
595 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
598 -- Table structure for table `deletedbiblioitems`
601 DROP TABLE IF EXISTS `deletedbiblioitems`;
602 CREATE TABLE `deletedbiblioitems` (
603 `biblioitemnumber` int(11) NOT NULL default 0,
604 `biblionumber` int(11) NOT NULL default 0,
607 `itemtype` varchar(10) default NULL,
608 `isbn` varchar(30) default NULL,
609 `issn` varchar(9) default NULL,
610 `publicationyear` text,
611 `publishercode` varchar(255) default NULL,
612 `volumedate` date default NULL,
614 `collectiontitle` mediumtext default NULL,
615 `collectionissn` text default NULL,
616 `collectionvolume` mediumtext default NULL,
617 `editionstatement` text default NULL,
618 `editionresponsibility` text default NULL,
619 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
620 `illus` varchar(255) default NULL,
621 `pages` varchar(255) default NULL,
623 `size` varchar(255) default NULL,
624 `place` varchar(255) default NULL,
625 `lccn` varchar(25) default NULL,
627 `url` varchar(255) default NULL,
628 `cn_source` varchar(10) default NULL,
629 `cn_class` varchar(30) default NULL,
630 `cn_item` varchar(10) default NULL,
631 `cn_suffix` varchar(10) default NULL,
632 `cn_sort` varchar(30) default NULL,
633 `totalissues` int(10),
634 `marcxml` longtext NOT NULL,
635 PRIMARY KEY (`biblioitemnumber`),
636 KEY `bibinoidx` (`biblioitemnumber`),
637 KEY `bibnoidx` (`biblionumber`),
639 KEY `publishercode` (`publishercode`)
640 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
643 -- Table structure for table `deletedborrowers`
646 DROP TABLE IF EXISTS `deletedborrowers`;
647 CREATE TABLE `deletedborrowers` (
648 `borrowernumber` int(11) NOT NULL default 0,
649 `cardnumber` varchar(16) NOT NULL default '',
650 `surname` mediumtext NOT NULL,
653 `othernames` mediumtext,
655 `streetnumber` varchar(10) default NULL,
656 `streettype` varchar(50) default NULL,
657 `address` mediumtext NOT NULL,
659 `city` mediumtext NOT NULL,
660 `zipcode` varchar(25) default NULL,
664 `mobile` varchar(50) default NULL,
668 `B_streetnumber` varchar(10) default NULL,
669 `B_streettype` varchar(50) default NULL,
670 `B_address` varchar(100) default NULL,
671 `B_address2` text default NULL,
673 `B_zipcode` varchar(25) default NULL,
676 `B_phone` mediumtext,
677 `dateofbirth` date default NULL,
678 `branchcode` varchar(10) NOT NULL default '',
679 `categorycode` varchar(10) default NULL,
680 `dateenrolled` date default NULL,
681 `dateexpiry` date default NULL,
682 `gonenoaddress` tinyint(1) default NULL,
683 `lost` tinyint(1) default NULL,
684 `debarred` tinyint(1) default NULL,
685 `contactname` mediumtext,
686 `contactfirstname` text,
688 `guarantorid` int(11) default NULL,
689 `borrowernotes` mediumtext,
690 `relationship` varchar(100) default NULL,
691 `ethnicity` varchar(50) default NULL,
692 `ethnotes` varchar(255) default NULL,
693 `sex` varchar(1) default NULL,
694 `password` varchar(30) default NULL,
695 `flags` int(11) default NULL,
696 `userid` varchar(30) default NULL,
697 `opacnote` mediumtext,
698 `contactnote` varchar(255) default NULL,
699 `sort1` varchar(80) default NULL,
700 `sort2` varchar(80) default NULL,
701 `altcontactfirstname` varchar(255) default NULL,
702 `altcontactsurname` varchar(255) default NULL,
703 `altcontactaddress1` varchar(255) default NULL,
704 `altcontactaddress2` varchar(255) default NULL,
705 `altcontactaddress3` varchar(255) default NULL,
706 `altcontactzipcode` varchar(50) default NULL,
707 `altcontactcountry` text default NULL,
708 `altcontactphone` varchar(50) default NULL,
709 `smsalertnumber` varchar(50) default NULL,
710 `privacy` integer(11) DEFAULT '1' NOT NULL,
711 KEY `borrowernumber` (`borrowernumber`),
712 KEY `cardnumber` (`cardnumber`)
713 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
716 -- Table structure for table `deleteditems`
719 DROP TABLE IF EXISTS `deleteditems`;
720 CREATE TABLE `deleteditems` (
721 `itemnumber` int(11) NOT NULL default 0,
722 `biblionumber` int(11) NOT NULL default 0,
723 `biblioitemnumber` int(11) NOT NULL default 0,
724 `barcode` varchar(20) default NULL,
725 `dateaccessioned` date default NULL,
726 `booksellerid` mediumtext default NULL,
727 `homebranch` varchar(10) default NULL,
728 `price` decimal(8,2) default NULL,
729 `replacementprice` decimal(8,2) default NULL,
730 `replacementpricedate` date default NULL,
731 `datelastborrowed` date default NULL,
732 `datelastseen` date default NULL,
733 `stack` tinyint(1) default NULL,
734 `notforloan` tinyint(1) NOT NULL default 0,
735 `damaged` tinyint(1) NOT NULL default 0,
736 `itemlost` tinyint(1) NOT NULL default 0,
737 `wthdrawn` tinyint(1) NOT NULL default 0,
738 `itemcallnumber` varchar(255) default NULL,
739 `issues` smallint(6) default NULL,
740 `renewals` smallint(6) default NULL,
741 `reserves` smallint(6) default NULL,
742 `restricted` tinyint(1) default NULL,
743 `itemnotes` mediumtext,
744 `holdingbranch` varchar(10) default NULL,
745 `paidfor` mediumtext,
746 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
747 `location` varchar(80) default NULL,
748 `permanent_location` varchar(80) default NULL,
749 `onloan` date default NULL,
750 `cn_source` varchar(10) default NULL,
751 `cn_sort` varchar(30) default NULL,
752 `ccode` varchar(10) default NULL,
753 `materials` varchar(10) default NULL,
754 `uri` varchar(255) default NULL,
755 `itype` varchar(10) default NULL,
756 `more_subfields_xml` longtext default NULL,
757 `enumchron` text default NULL,
758 `copynumber` varchar(32) default NULL,
759 `stocknumber` varchar(32) default NULL,
761 PRIMARY KEY (`itemnumber`),
762 KEY `delitembarcodeidx` (`barcode`),
763 KEY `delitemstocknumberidx` (`stocknumber`),
764 KEY `delitembinoidx` (`biblioitemnumber`),
765 KEY `delitembibnoidx` (`biblionumber`),
766 KEY `delhomebranch` (`homebranch`),
767 KEY `delholdingbranch` (`holdingbranch`)
768 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
771 -- Table structure for table `ethnicity`
774 DROP TABLE IF EXISTS `ethnicity`;
775 CREATE TABLE `ethnicity` (
776 `code` varchar(10) NOT NULL default '',
777 `name` varchar(255) default NULL,
779 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
782 -- Table structure for table `export_format`
785 DROP TABLE IF EXISTS `export_format`;
786 CREATE TABLE `export_format` (
787 `export_format_id` int(11) NOT NULL auto_increment,
788 `profile` varchar(255) NOT NULL,
789 `description` mediumtext NOT NULL,
790 `marcfields` mediumtext NOT NULL,
791 `csv_separator` varchar(2) NOT NULL,
792 `field_separator` varchar(2) NOT NULL,
793 `subfield_separator` varchar(2) NOT NULL,
794 `encoding` varchar(255) NOT NULL,
795 PRIMARY KEY (`export_format_id`)
796 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
800 -- Table structure for table `hold_fill_targets`
803 DROP TABLE IF EXISTS `hold_fill_targets`;
804 CREATE TABLE hold_fill_targets (
805 `borrowernumber` int(11) NOT NULL,
806 `biblionumber` int(11) NOT NULL,
807 `itemnumber` int(11) NOT NULL,
808 `source_branchcode` varchar(10) default NULL,
809 `item_level_request` tinyint(4) NOT NULL default 0,
810 PRIMARY KEY `itemnumber` (`itemnumber`),
811 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
812 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
813 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
814 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
815 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
816 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
817 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
818 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
819 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
820 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
823 -- Table structure for table `import_batches`
826 DROP TABLE IF EXISTS `import_batches`;
827 CREATE TABLE `import_batches` (
828 `import_batch_id` int(11) NOT NULL auto_increment,
829 `matcher_id` int(11) default NULL,
830 `template_id` int(11) default NULL,
831 `branchcode` varchar(10) default NULL,
832 `num_biblios` int(11) NOT NULL default 0,
833 `num_items` int(11) NOT NULL default 0,
834 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
835 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
836 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
837 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
838 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
839 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
840 `file_name` varchar(100),
841 `comments` mediumtext,
842 PRIMARY KEY (`import_batch_id`),
843 KEY `branchcode` (`branchcode`)
844 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
847 -- Table structure for table `import_records`
850 DROP TABLE IF EXISTS `import_records`;
851 CREATE TABLE `import_records` (
852 `import_record_id` int(11) NOT NULL auto_increment,
853 `import_batch_id` int(11) NOT NULL,
854 `branchcode` varchar(10) default NULL,
855 `record_sequence` int(11) NOT NULL default 0,
856 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
857 `import_date` DATE default NULL,
858 `marc` longblob NOT NULL,
859 `marcxml` longtext NOT NULL,
860 `marcxml_old` longtext NOT NULL,
861 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
862 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
863 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
864 `import_error` mediumtext,
865 `encoding` varchar(40) NOT NULL default '',
866 `z3950random` varchar(40) default NULL,
867 PRIMARY KEY (`import_record_id`),
868 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
869 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
870 KEY `branchcode` (`branchcode`),
871 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
872 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
875 -- Table structure for `import_record_matches`
877 DROP TABLE IF EXISTS `import_record_matches`;
878 CREATE TABLE `import_record_matches` (
879 `import_record_id` int(11) NOT NULL,
880 `candidate_match_id` int(11) NOT NULL,
881 `score` int(11) NOT NULL default 0,
882 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
883 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
884 KEY `record_score` (`import_record_id`, `score`)
885 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
888 -- Table structure for table `import_biblios`
891 DROP TABLE IF EXISTS `import_biblios`;
892 CREATE TABLE `import_biblios` (
893 `import_record_id` int(11) NOT NULL,
894 `matched_biblionumber` int(11) default NULL,
895 `control_number` varchar(25) default NULL,
896 `original_source` varchar(25) default NULL,
897 `title` varchar(128) default NULL,
898 `author` varchar(80) default NULL,
899 `isbn` varchar(30) default NULL,
900 `issn` varchar(9) default NULL,
901 `has_items` tinyint(1) NOT NULL default 0,
902 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
903 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
904 KEY `matched_biblionumber` (`matched_biblionumber`),
905 KEY `title` (`title`),
907 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
910 -- Table structure for table `import_items`
913 DROP TABLE IF EXISTS `import_items`;
914 CREATE TABLE `import_items` (
915 `import_items_id` int(11) NOT NULL auto_increment,
916 `import_record_id` int(11) NOT NULL,
917 `itemnumber` int(11) default NULL,
918 `branchcode` varchar(10) default NULL,
919 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
920 `marcxml` longtext NOT NULL,
921 `import_error` mediumtext,
922 PRIMARY KEY (`import_items_id`),
923 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
924 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
925 KEY `itemnumber` (`itemnumber`),
926 KEY `branchcode` (`branchcode`)
927 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
930 -- Table structure for table `issues`
933 DROP TABLE IF EXISTS `issues`;
934 CREATE TABLE `issues` (
935 `borrowernumber` int(11) default NULL,
936 `itemnumber` int(11) default NULL,
937 `date_due` date default NULL,
938 `branchcode` varchar(10) default NULL,
939 `issuingbranch` varchar(18) default NULL,
940 `returndate` date default NULL,
941 `lastreneweddate` date default NULL,
942 `return` varchar(4) default NULL,
943 `renewals` tinyint(4) default NULL,
944 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
945 `issuedate` date default NULL,
946 KEY `issuesborridx` (`borrowernumber`),
947 KEY `issuesitemidx` (`itemnumber`),
948 KEY `bordate` (`borrowernumber`,`timestamp`),
949 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
950 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
951 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
954 -- Table structure for table `issuingrules`
957 DROP TABLE IF EXISTS `issuingrules`;
958 CREATE TABLE `issuingrules` (
959 `categorycode` varchar(10) NOT NULL default '',
960 `itemtype` varchar(10) NOT NULL default '',
961 `restrictedtype` tinyint(1) default NULL,
962 `rentaldiscount` decimal(28,6) default NULL,
963 `reservecharge` decimal(28,6) default NULL,
964 `fine` decimal(28,6) default NULL,
965 `finedays` int(11) default NULL,
966 `firstremind` int(11) default NULL,
967 `chargeperiod` int(11) default NULL,
968 `accountsent` int(11) default NULL,
969 `chargename` varchar(100) default NULL,
970 `maxissueqty` int(4) default NULL,
971 `issuelength` int(4) default NULL,
972 `hardduedate` date default NULL,
973 `hardduedatecompare` tinyint NOT NULL default "0",
974 `renewalsallowed` smallint(6) NOT NULL default "0",
975 `reservesallowed` smallint(6) NOT NULL default "0",
976 `branchcode` varchar(10) NOT NULL default '',
977 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
978 KEY `categorycode` (`categorycode`),
979 KEY `itemtype` (`itemtype`)
980 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
983 -- Table structure for table `items`
986 DROP TABLE IF EXISTS `items`;
987 CREATE TABLE `items` (
988 `itemnumber` int(11) NOT NULL auto_increment,
989 `biblionumber` int(11) NOT NULL default 0,
990 `biblioitemnumber` int(11) NOT NULL default 0,
991 `barcode` varchar(20) default NULL,
992 `dateaccessioned` date default NULL,
993 `booksellerid` mediumtext default NULL,
994 `homebranch` varchar(10) default NULL,
995 `price` decimal(8,2) default NULL,
996 `replacementprice` decimal(8,2) default NULL,
997 `replacementpricedate` date default NULL,
998 `datelastborrowed` date default NULL,
999 `datelastseen` date default NULL,
1000 `stack` tinyint(1) default NULL,
1001 `notforloan` tinyint(1) NOT NULL default 0,
1002 `damaged` tinyint(1) NOT NULL default 0,
1003 `itemlost` tinyint(1) NOT NULL default 0,
1004 `wthdrawn` tinyint(1) NOT NULL default 0,
1005 `itemcallnumber` varchar(255) default NULL,
1006 `issues` smallint(6) default NULL,
1007 `renewals` smallint(6) default NULL,
1008 `reserves` smallint(6) default NULL,
1009 `restricted` tinyint(1) default NULL,
1010 `itemnotes` mediumtext,
1011 `holdingbranch` varchar(10) default NULL,
1012 `paidfor` mediumtext,
1013 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1014 `location` varchar(80) default NULL,
1015 `permanent_location` varchar(80) default NULL,
1016 `onloan` date default NULL,
1017 `cn_source` varchar(10) default NULL,
1018 `cn_sort` varchar(30) default NULL,
1019 `ccode` varchar(10) default NULL,
1020 `materials` varchar(10) default NULL,
1021 `uri` varchar(255) default NULL,
1022 `itype` varchar(10) default NULL,
1023 `more_subfields_xml` longtext default NULL,
1024 `enumchron` text default NULL,
1025 `copynumber` varchar(32) default NULL,
1026 `stocknumber` varchar(32) default NULL,
1027 PRIMARY KEY (`itemnumber`),
1028 UNIQUE KEY `itembarcodeidx` (`barcode`),
1029 KEY `itemstocknumberidx` (`stocknumber`),
1030 KEY `itembinoidx` (`biblioitemnumber`),
1031 KEY `itembibnoidx` (`biblionumber`),
1032 KEY `homebranch` (`homebranch`),
1033 KEY `holdingbranch` (`holdingbranch`),
1034 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1035 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1036 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1037 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1040 -- Table structure for table `itemtypes`
1043 DROP TABLE IF EXISTS `itemtypes`;
1044 CREATE TABLE `itemtypes` (
1045 `itemtype` varchar(10) NOT NULL default '',
1046 `description` mediumtext,
1047 `rentalcharge` double(16,4) default NULL,
1048 `notforloan` smallint(6) default NULL,
1049 `imageurl` varchar(200) default NULL,
1051 PRIMARY KEY (`itemtype`),
1052 UNIQUE KEY `itemtype` (`itemtype`)
1053 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1056 -- Table structure for table `creator_batches`
1059 DROP TABLE IF EXISTS `creator_batches`;
1060 SET @saved_cs_client = @@character_set_client;
1061 SET character_set_client = utf8;
1062 CREATE TABLE `creator_batches` (
1063 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1064 `batch_id` int(10) NOT NULL DEFAULT '1',
1065 `item_number` int(11) DEFAULT NULL,
1066 `borrower_number` int(11) DEFAULT NULL,
1067 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1068 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1069 `creator` char(15) NOT NULL DEFAULT 'Labels',
1070 PRIMARY KEY (`label_id`),
1071 KEY `branch_fk_constraint` (`branch_code`),
1072 KEY `item_fk_constraint` (`item_number`),
1073 KEY `borrower_fk_constraint` (`borrower_number`),
1074 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1075 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1076 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1077 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1080 -- Table structure for table `creator_images`
1083 DROP TABLE IF EXISTS `creator_images`;
1084 SET @saved_cs_client = @@character_set_client;
1085 SET character_set_client = utf8;
1086 CREATE TABLE `creator_images` (
1087 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1088 `imagefile` mediumblob,
1089 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1090 PRIMARY KEY (`image_id`),
1091 UNIQUE KEY `image_name_index` (`image_name`)
1092 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1095 -- Table structure for table `creator_layouts`
1098 DROP TABLE IF EXISTS `creator_layouts`;
1099 SET @saved_cs_client = @@character_set_client;
1100 SET character_set_client = utf8;
1101 CREATE TABLE `creator_layouts` (
1102 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1103 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1104 `start_label` int(2) NOT NULL DEFAULT '1',
1105 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1106 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1107 `guidebox` int(1) DEFAULT '0',
1108 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1109 `font_size` int(4) NOT NULL DEFAULT '10',
1110 `units` char(20) NOT NULL DEFAULT 'POINT',
1111 `callnum_split` int(1) DEFAULT '0',
1112 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1113 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1114 `layout_xml` text NOT NULL,
1115 `creator` char(15) NOT NULL DEFAULT 'Labels',
1116 PRIMARY KEY (`layout_id`)
1117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1120 -- Table structure for table `creator_templates`
1123 DROP TABLE IF EXISTS `creator_templates`;
1124 SET @saved_cs_client = @@character_set_client;
1125 SET character_set_client = utf8;
1126 CREATE TABLE `creator_templates` (
1127 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1128 `profile_id` int(4) DEFAULT NULL,
1129 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1130 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1131 `page_width` float NOT NULL DEFAULT '0',
1132 `page_height` float NOT NULL DEFAULT '0',
1133 `label_width` float NOT NULL DEFAULT '0',
1134 `label_height` float NOT NULL DEFAULT '0',
1135 `top_text_margin` float NOT NULL DEFAULT '0',
1136 `left_text_margin` float NOT NULL DEFAULT '0',
1137 `top_margin` float NOT NULL DEFAULT '0',
1138 `left_margin` float NOT NULL DEFAULT '0',
1139 `cols` int(2) NOT NULL DEFAULT '0',
1140 `rows` int(2) NOT NULL DEFAULT '0',
1141 `col_gap` float NOT NULL DEFAULT '0',
1142 `row_gap` float NOT NULL DEFAULT '0',
1143 `units` char(20) NOT NULL DEFAULT 'POINT',
1144 `creator` char(15) NOT NULL DEFAULT 'Labels',
1145 PRIMARY KEY (`template_id`),
1146 KEY `template_profile_fk_constraint` (`profile_id`)
1147 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1150 -- Table structure for table `letter`
1153 DROP TABLE IF EXISTS `letter`;
1154 CREATE TABLE `letter` (
1155 `module` varchar(20) NOT NULL default '',
1156 `code` varchar(20) NOT NULL default '',
1157 `name` varchar(100) NOT NULL default '',
1158 `title` varchar(200) NOT NULL default '',
1160 PRIMARY KEY (`module`,`code`)
1161 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1164 -- Table structure for table `marc_subfield_structure`
1167 DROP TABLE IF EXISTS `marc_subfield_structure`;
1168 CREATE TABLE `marc_subfield_structure` (
1169 `tagfield` varchar(3) NOT NULL default '',
1170 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1171 `liblibrarian` varchar(255) NOT NULL default '',
1172 `libopac` varchar(255) NOT NULL default '',
1173 `repeatable` tinyint(4) NOT NULL default 0,
1174 `mandatory` tinyint(4) NOT NULL default 0,
1175 `kohafield` varchar(40) default NULL,
1176 `tab` tinyint(1) default NULL,
1177 `authorised_value` varchar(20) default NULL,
1178 `authtypecode` varchar(20) default NULL,
1179 `value_builder` varchar(80) default NULL,
1180 `isurl` tinyint(1) default NULL,
1181 `hidden` tinyint(1) default NULL,
1182 `frameworkcode` varchar(4) NOT NULL default '',
1183 `seealso` varchar(1100) default NULL,
1184 `link` varchar(80) default NULL,
1185 `defaultvalue` text default NULL,
1186 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1187 KEY `kohafield_2` (`kohafield`),
1188 KEY `tab` (`frameworkcode`,`tab`),
1189 KEY `kohafield` (`frameworkcode`,`kohafield`)
1190 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1193 -- Table structure for table `marc_tag_structure`
1196 DROP TABLE IF EXISTS `marc_tag_structure`;
1197 CREATE TABLE `marc_tag_structure` (
1198 `tagfield` varchar(3) NOT NULL default '',
1199 `liblibrarian` varchar(255) NOT NULL default '',
1200 `libopac` varchar(255) NOT NULL default '',
1201 `repeatable` tinyint(4) NOT NULL default 0,
1202 `mandatory` tinyint(4) NOT NULL default 0,
1203 `authorised_value` varchar(10) default NULL,
1204 `frameworkcode` varchar(4) NOT NULL default '',
1205 PRIMARY KEY (`frameworkcode`,`tagfield`)
1206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1209 -- Table structure for table `marc_matchers`
1212 DROP TABLE IF EXISTS `marc_matchers`;
1213 CREATE TABLE `marc_matchers` (
1214 `matcher_id` int(11) NOT NULL auto_increment,
1215 `code` varchar(10) NOT NULL default '',
1216 `description` varchar(255) NOT NULL default '',
1217 `record_type` varchar(10) NOT NULL default 'biblio',
1218 `threshold` int(11) NOT NULL default 0,
1219 PRIMARY KEY (`matcher_id`),
1220 KEY `code` (`code`),
1221 KEY `record_type` (`record_type`)
1222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1225 -- Table structure for table `matchpoints`
1227 DROP TABLE IF EXISTS `matchpoints`;
1228 CREATE TABLE `matchpoints` (
1229 `matcher_id` int(11) NOT NULL,
1230 `matchpoint_id` int(11) NOT NULL auto_increment,
1231 `search_index` varchar(30) NOT NULL default '',
1232 `score` int(11) NOT NULL default 0,
1233 PRIMARY KEY (`matchpoint_id`),
1234 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1235 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1236 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1240 -- Table structure for table `matchpoint_components`
1242 DROP TABLE IF EXISTS `matchpoint_components`;
1243 CREATE TABLE `matchpoint_components` (
1244 `matchpoint_id` int(11) NOT NULL,
1245 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1246 sequence int(11) NOT NULL default 0,
1247 tag varchar(3) NOT NULL default '',
1248 subfields varchar(40) NOT NULL default '',
1249 offset int(4) NOT NULL default 0,
1250 length int(4) NOT NULL default 0,
1251 PRIMARY KEY (`matchpoint_component_id`),
1252 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1253 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1254 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1255 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1258 -- Table structure for table `matcher_component_norms`
1260 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1261 CREATE TABLE `matchpoint_component_norms` (
1262 `matchpoint_component_id` int(11) NOT NULL,
1263 `sequence` int(11) NOT NULL default 0,
1264 `norm_routine` varchar(50) NOT NULL default '',
1265 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1266 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1267 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1268 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1271 -- Table structure for table `matcher_matchpoints`
1273 DROP TABLE IF EXISTS `matcher_matchpoints`;
1274 CREATE TABLE `matcher_matchpoints` (
1275 `matcher_id` int(11) NOT NULL,
1276 `matchpoint_id` int(11) NOT NULL,
1277 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1278 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1279 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1280 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1284 -- Table structure for table `matchchecks`
1286 DROP TABLE IF EXISTS `matchchecks`;
1287 CREATE TABLE `matchchecks` (
1288 `matcher_id` int(11) NOT NULL,
1289 `matchcheck_id` int(11) NOT NULL auto_increment,
1290 `source_matchpoint_id` int(11) NOT NULL,
1291 `target_matchpoint_id` int(11) NOT NULL,
1292 PRIMARY KEY (`matchcheck_id`),
1293 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1294 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1295 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1296 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1297 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1298 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1302 -- Table structure for table `notifys`
1305 DROP TABLE IF EXISTS `notifys`;
1306 CREATE TABLE `notifys` (
1307 `notify_id` int(11) NOT NULL default 0,
1308 `borrowernumber` int(11) NOT NULL default 0,
1309 `itemnumber` int(11) NOT NULL default 0,
1310 `notify_date` date default NULL,
1311 `notify_send_date` date default NULL,
1312 `notify_level` int(1) NOT NULL default 0,
1313 `method` varchar(20) NOT NULL default ''
1314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1317 -- Table structure for table `nozebra`
1320 DROP TABLE IF EXISTS `nozebra`;
1321 CREATE TABLE `nozebra` (
1322 `server` varchar(20) NOT NULL,
1323 `indexname` varchar(40) NOT NULL,
1324 `value` varchar(250) NOT NULL,
1325 `biblionumbers` longtext NOT NULL,
1326 KEY `indexname` (`server`,`indexname`),
1327 KEY `value` (`server`,`value`))
1328 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1331 -- Table structure for table `old_issues`
1334 DROP TABLE IF EXISTS `old_issues`;
1335 CREATE TABLE `old_issues` (
1336 `borrowernumber` int(11) default NULL,
1337 `itemnumber` int(11) default NULL,
1338 `date_due` date default NULL,
1339 `branchcode` varchar(10) default NULL,
1340 `issuingbranch` varchar(18) default NULL,
1341 `returndate` date default NULL,
1342 `lastreneweddate` date default NULL,
1343 `return` varchar(4) default NULL,
1344 `renewals` tinyint(4) default NULL,
1345 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1346 `issuedate` date default NULL,
1347 KEY `old_issuesborridx` (`borrowernumber`),
1348 KEY `old_issuesitemidx` (`itemnumber`),
1349 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1350 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1351 ON DELETE SET NULL ON UPDATE SET NULL,
1352 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1353 ON DELETE SET NULL ON UPDATE SET NULL
1354 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1357 -- Table structure for table `old_reserves`
1359 DROP TABLE IF EXISTS `old_reserves`;
1360 CREATE TABLE `old_reserves` (
1361 `borrowernumber` int(11) default NULL,
1362 `reservedate` date default NULL,
1363 `biblionumber` int(11) default NULL,
1364 `constrainttype` varchar(1) default NULL,
1365 `branchcode` varchar(10) default NULL,
1366 `notificationdate` date default NULL,
1367 `reminderdate` date default NULL,
1368 `cancellationdate` date default NULL,
1369 `reservenotes` mediumtext,
1370 `priority` smallint(6) default NULL,
1371 `found` varchar(1) default NULL,
1372 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1373 `itemnumber` int(11) default NULL,
1374 `waitingdate` date default NULL,
1375 `expirationdate` DATE DEFAULT NULL,
1376 `lowestPriority` tinyint(1) NOT NULL,
1377 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1378 KEY `old_reserves_biblionumber` (`biblionumber`),
1379 KEY `old_reserves_itemnumber` (`itemnumber`),
1380 KEY `old_reserves_branchcode` (`branchcode`),
1381 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1382 ON DELETE SET NULL ON UPDATE SET NULL,
1383 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1384 ON DELETE SET NULL ON UPDATE SET NULL,
1385 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1386 ON DELETE SET NULL ON UPDATE SET NULL
1387 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1390 -- Table structure for table `opac_news`
1393 DROP TABLE IF EXISTS `opac_news`;
1394 CREATE TABLE `opac_news` (
1395 `idnew` int(10) unsigned NOT NULL auto_increment,
1396 `title` varchar(250) NOT NULL default '',
1397 `new` text NOT NULL,
1398 `lang` varchar(25) NOT NULL default '',
1399 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1400 `expirationdate` date default NULL,
1401 `number` int(11) default NULL,
1402 PRIMARY KEY (`idnew`)
1403 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1406 -- Table structure for table `overduerules`
1409 DROP TABLE IF EXISTS `overduerules`;
1410 CREATE TABLE `overduerules` (
1411 `branchcode` varchar(10) NOT NULL default '',
1412 `categorycode` varchar(10) NOT NULL default '',
1413 `delay1` int(4) default NULL,
1414 `letter1` varchar(20) default NULL,
1415 `debarred1` varchar(1) default 0,
1416 `delay2` int(4) default NULL,
1417 `debarred2` varchar(1) default 0,
1418 `letter2` varchar(20) default NULL,
1419 `delay3` int(4) default NULL,
1420 `letter3` varchar(20) default NULL,
1421 `debarred3` int(1) default 0,
1422 PRIMARY KEY (`branchcode`,`categorycode`)
1423 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1426 -- Table structure for table `patroncards`
1429 DROP TABLE IF EXISTS `patroncards`;
1430 CREATE TABLE `patroncards` (
1431 `cardid` int(11) NOT NULL auto_increment,
1432 `batch_id` varchar(10) NOT NULL default '1',
1433 `borrowernumber` int(11) NOT NULL,
1434 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1435 PRIMARY KEY (`cardid`),
1436 KEY `patroncards_ibfk_1` (`borrowernumber`),
1437 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1438 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1441 -- Table structure for table `patronimage`
1444 DROP TABLE IF EXISTS `patronimage`;
1445 CREATE TABLE `patronimage` (
1446 `cardnumber` varchar(16) NOT NULL,
1447 `mimetype` varchar(15) NOT NULL,
1448 `imagefile` mediumblob NOT NULL,
1449 PRIMARY KEY (`cardnumber`),
1450 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1451 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1454 -- Table structure for table `printers`
1457 DROP TABLE IF EXISTS `printers`;
1458 CREATE TABLE `printers` (
1459 `printername` varchar(40) NOT NULL default '',
1460 `printqueue` varchar(20) default NULL,
1461 `printtype` varchar(20) default NULL,
1462 PRIMARY KEY (`printername`)
1463 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1466 -- Table structure for table `printers_profile`
1469 DROP TABLE IF EXISTS `printers_profile`;
1470 CREATE TABLE `printers_profile` (
1471 `profile_id` int(4) NOT NULL auto_increment,
1472 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1473 `template_id` int(4) NOT NULL default '0',
1474 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1475 `offset_horz` float NOT NULL default '0',
1476 `offset_vert` float NOT NULL default '0',
1477 `creep_horz` float NOT NULL default '0',
1478 `creep_vert` float NOT NULL default '0',
1479 `units` char(20) NOT NULL default 'POINT',
1480 `creator` char(15) NOT NULL DEFAULT 'Labels',
1481 PRIMARY KEY (`profile_id`),
1482 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
1483 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1486 -- Table structure for table `repeatable_holidays`
1489 DROP TABLE IF EXISTS `repeatable_holidays`;
1490 CREATE TABLE `repeatable_holidays` (
1491 `id` int(11) NOT NULL auto_increment,
1492 `branchcode` varchar(10) NOT NULL default '',
1493 `weekday` smallint(6) default NULL,
1494 `day` smallint(6) default NULL,
1495 `month` smallint(6) default NULL,
1496 `title` varchar(50) NOT NULL default '',
1497 `description` text NOT NULL,
1499 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1502 -- Table structure for table `reports_dictionary`
1505 DROP TABLE IF EXISTS `reports_dictionary`;
1506 CREATE TABLE reports_dictionary (
1507 `id` int(11) NOT NULL auto_increment,
1508 `name` varchar(255) default NULL,
1510 `date_created` datetime default NULL,
1511 `date_modified` datetime default NULL,
1513 `area` int(11) default NULL,
1515 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1518 -- Table structure for table `reserveconstraints`
1521 DROP TABLE IF EXISTS `reserveconstraints`;
1522 CREATE TABLE `reserveconstraints` (
1523 `borrowernumber` int(11) NOT NULL default 0,
1524 `reservedate` date default NULL,
1525 `biblionumber` int(11) NOT NULL default 0,
1526 `biblioitemnumber` int(11) default NULL,
1527 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1531 -- Table structure for table `reserves`
1534 DROP TABLE IF EXISTS `reserves`;
1535 CREATE TABLE `reserves` (
1536 `borrowernumber` int(11) NOT NULL default 0,
1537 `reservedate` date default NULL,
1538 `biblionumber` int(11) NOT NULL default 0,
1539 `constrainttype` varchar(1) default NULL,
1540 `branchcode` varchar(10) default NULL,
1541 `notificationdate` date default NULL,
1542 `reminderdate` date default NULL,
1543 `cancellationdate` date default NULL,
1544 `reservenotes` mediumtext,
1545 `priority` smallint(6) default NULL,
1546 `found` varchar(1) default NULL,
1547 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1548 `itemnumber` int(11) default NULL,
1549 `waitingdate` date default NULL,
1550 `expirationdate` DATE DEFAULT NULL,
1551 `lowestPriority` tinyint(1) NOT NULL,
1552 KEY `borrowernumber` (`borrowernumber`),
1553 KEY `biblionumber` (`biblionumber`),
1554 KEY `itemnumber` (`itemnumber`),
1555 KEY `branchcode` (`branchcode`),
1556 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1557 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1558 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1559 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1560 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1563 -- Table structure for table `reviews`
1566 DROP TABLE IF EXISTS `reviews`;
1567 CREATE TABLE `reviews` (
1568 `reviewid` int(11) NOT NULL auto_increment,
1569 `borrowernumber` int(11) default NULL,
1570 `biblionumber` int(11) default NULL,
1572 `approved` tinyint(4) default NULL,
1573 `datereviewed` datetime default NULL,
1574 PRIMARY KEY (`reviewid`)
1575 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1578 -- Table structure for table `roadtype`
1581 DROP TABLE IF EXISTS `roadtype`;
1582 CREATE TABLE `roadtype` (
1583 `roadtypeid` int(11) NOT NULL auto_increment,
1584 `road_type` varchar(100) NOT NULL default '',
1585 PRIMARY KEY (`roadtypeid`)
1586 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1589 -- Table structure for table `saved_sql`
1592 DROP TABLE IF EXISTS `saved_sql`;
1593 CREATE TABLE saved_sql (
1594 `id` int(11) NOT NULL auto_increment,
1595 `borrowernumber` int(11) default NULL,
1596 `date_created` datetime default NULL,
1597 `last_modified` datetime default NULL,
1599 `last_run` datetime default NULL,
1600 `report_name` varchar(255) default NULL,
1601 `type` varchar(255) default NULL,
1604 KEY boridx (`borrowernumber`)
1605 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1609 -- Table structure for `saved_reports`
1612 DROP TABLE IF EXISTS `saved_reports`;
1613 CREATE TABLE saved_reports (
1614 `id` int(11) NOT NULL auto_increment,
1615 `report_id` int(11) default NULL,
1617 `date_run` datetime default NULL,
1619 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1623 -- Table structure for table `search_history`
1626 DROP TABLE IF EXISTS `search_history`;
1627 CREATE TABLE IF NOT EXISTS `search_history` (
1628 `userid` int(11) NOT NULL,
1629 `sessionid` varchar(32) NOT NULL,
1630 `query_desc` varchar(255) NOT NULL,
1631 `query_cgi` varchar(255) NOT NULL,
1632 `total` int(11) NOT NULL,
1633 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1634 KEY `userid` (`userid`),
1635 KEY `sessionid` (`sessionid`)
1636 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1640 -- Table structure for table `serial`
1643 DROP TABLE IF EXISTS `serial`;
1644 CREATE TABLE `serial` (
1645 `serialid` int(11) NOT NULL auto_increment,
1646 `biblionumber` varchar(100) NOT NULL default '',
1647 `subscriptionid` varchar(100) NOT NULL default '',
1648 `serialseq` varchar(100) NOT NULL default '',
1649 `status` tinyint(4) NOT NULL default 0,
1650 `planneddate` date default NULL,
1652 `publisheddate` date default NULL,
1653 `itemnumber` text default NULL,
1654 `claimdate` date default NULL,
1655 `routingnotes` text,
1656 PRIMARY KEY (`serialid`)
1657 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1660 -- Table structure for table `sessions`
1663 DROP TABLE IF EXISTS sessions;
1664 CREATE TABLE sessions (
1665 `id` varchar(32) NOT NULL,
1666 `a_session` text NOT NULL,
1668 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1671 -- Table structure for table `special_holidays`
1674 DROP TABLE IF EXISTS `special_holidays`;
1675 CREATE TABLE `special_holidays` (
1676 `id` int(11) NOT NULL auto_increment,
1677 `branchcode` varchar(10) NOT NULL default '',
1678 `day` smallint(6) NOT NULL default 0,
1679 `month` smallint(6) NOT NULL default 0,
1680 `year` smallint(6) NOT NULL default 0,
1681 `isexception` smallint(1) NOT NULL default 1,
1682 `title` varchar(50) NOT NULL default '',
1683 `description` text NOT NULL,
1685 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1688 -- Table structure for table `statistics`
1691 DROP TABLE IF EXISTS `statistics`;
1692 CREATE TABLE `statistics` (
1693 `datetime` datetime default NULL,
1694 `branch` varchar(10) default NULL,
1695 `proccode` varchar(4) default NULL,
1696 `value` double(16,4) default NULL,
1697 `type` varchar(16) default NULL,
1699 `usercode` varchar(10) default NULL,
1700 `itemnumber` int(11) default NULL,
1701 `itemtype` varchar(10) default NULL,
1702 `borrowernumber` int(11) default NULL,
1703 `associatedborrower` int(11) default NULL,
1704 KEY `timeidx` (`datetime`)
1705 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1708 -- Table structure for table `stopwords`
1711 DROP TABLE IF EXISTS `stopwords`;
1712 CREATE TABLE `stopwords` (
1713 `word` varchar(255) default NULL
1714 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1717 -- Table structure for table `subscription`
1720 DROP TABLE IF EXISTS `subscription`;
1721 CREATE TABLE `subscription` (
1722 `biblionumber` int(11) NOT NULL default 0,
1723 `subscriptionid` int(11) NOT NULL auto_increment,
1724 `librarian` varchar(100) default '',
1725 `startdate` date default NULL,
1726 `aqbooksellerid` int(11) default 0,
1727 `cost` int(11) default 0,
1728 `aqbudgetid` int(11) default 0,
1729 `weeklength` int(11) default 0,
1730 `monthlength` int(11) default 0,
1731 `numberlength` int(11) default 0,
1732 `periodicity` tinyint(4) default 0,
1733 `dow` varchar(100) default '',
1734 `numberingmethod` varchar(100) default '',
1736 `status` varchar(100) NOT NULL default '',
1737 `add1` int(11) default 0,
1738 `every1` int(11) default 0,
1739 `whenmorethan1` int(11) default 0,
1740 `setto1` int(11) default NULL,
1741 `lastvalue1` int(11) default NULL,
1742 `add2` int(11) default 0,
1743 `every2` int(11) default 0,
1744 `whenmorethan2` int(11) default 0,
1745 `setto2` int(11) default NULL,
1746 `lastvalue2` int(11) default NULL,
1747 `add3` int(11) default 0,
1748 `every3` int(11) default 0,
1749 `innerloop1` int(11) default 0,
1750 `innerloop2` int(11) default 0,
1751 `innerloop3` int(11) default 0,
1752 `whenmorethan3` int(11) default 0,
1753 `setto3` int(11) default NULL,
1754 `lastvalue3` int(11) default NULL,
1755 `issuesatonce` tinyint(3) NOT NULL default 1,
1756 `firstacquidate` date default NULL,
1757 `manualhistory` tinyint(1) NOT NULL default 0,
1758 `irregularity` text,
1759 `letter` varchar(20) default NULL,
1760 `numberpattern` tinyint(3) default 0,
1761 `distributedto` text,
1762 `internalnotes` longtext,
1764 `location` varchar(80) NULL default '',
1765 `branchcode` varchar(10) NOT NULL default '',
1766 `hemisphere` tinyint(3) default 0,
1767 `lastbranch` varchar(10),
1768 `serialsadditems` tinyint(1) NOT NULL default '0',
1769 `staffdisplaycount` VARCHAR(10) NULL,
1770 `opacdisplaycount` VARCHAR(10) NULL,
1771 `graceperiod` int(11) NOT NULL default '0',
1772 `enddate` date default NULL,
1773 PRIMARY KEY (`subscriptionid`)
1774 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1777 -- Table structure for table `subscriptionhistory`
1780 DROP TABLE IF EXISTS `subscriptionhistory`;
1781 CREATE TABLE `subscriptionhistory` (
1782 `biblionumber` int(11) NOT NULL default 0,
1783 `subscriptionid` int(11) NOT NULL default 0,
1784 `histstartdate` date default NULL,
1785 `histenddate` date default NULL,
1786 `missinglist` longtext NOT NULL,
1787 `recievedlist` longtext NOT NULL,
1788 `opacnote` varchar(150) NOT NULL default '',
1789 `librariannote` varchar(150) NOT NULL default '',
1790 PRIMARY KEY (`subscriptionid`),
1791 KEY `biblionumber` (`biblionumber`)
1792 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1795 -- Table structure for table `subscriptionroutinglist`
1798 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1799 CREATE TABLE `subscriptionroutinglist` (
1800 `routingid` int(11) NOT NULL auto_increment,
1801 `borrowernumber` int(11) NOT NULL,
1802 `ranking` int(11) default NULL,
1803 `subscriptionid` int(11) NOT NULL,
1804 PRIMARY KEY (`routingid`),
1805 UNIQUE (`subscriptionid`, `borrowernumber`),
1806 CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1807 ON DELETE CASCADE ON UPDATE CASCADE,
1808 CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
1809 ON DELETE CASCADE ON UPDATE CASCADE
1810 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1813 -- Table structure for table `suggestions`
1816 DROP TABLE IF EXISTS `suggestions`;
1817 CREATE TABLE `suggestions` (
1818 `suggestionid` int(8) NOT NULL auto_increment,
1819 `suggestedby` int(11) NOT NULL default 0,
1820 `suggesteddate` date NOT NULL default 0,
1821 `managedby` int(11) default NULL,
1822 `manageddate` date default NULL,
1823 acceptedby INT(11) default NULL,
1824 accepteddate date default NULL,
1825 rejectedby INT(11) default NULL,
1826 rejecteddate date default NULL,
1827 `STATUS` varchar(10) NOT NULL default '',
1829 `author` varchar(80) default NULL,
1830 `title` varchar(80) default NULL,
1831 `copyrightdate` smallint(6) default NULL,
1832 `publishercode` varchar(255) default NULL,
1833 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1834 `volumedesc` varchar(255) default NULL,
1835 `publicationyear` smallint(6) default 0,
1836 `place` varchar(255) default NULL,
1837 `isbn` varchar(30) default NULL,
1838 `mailoverseeing` smallint(1) default 0,
1839 `biblionumber` int(11) default NULL,
1842 branchcode VARCHAR(10) default NULL,
1843 collectiontitle text default NULL,
1844 itemtype VARCHAR(30) default NULL,
1845 quantity SMALLINT(6) default NULL,
1846 currency VARCHAR(3) default NULL,
1847 price DECIMAL(28,6) default NULL,
1848 total DECIMAL(28,6) default NULL,
1849 PRIMARY KEY (`suggestionid`),
1850 KEY `suggestedby` (`suggestedby`),
1851 KEY `managedby` (`managedby`)
1852 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1855 -- Table structure for table `systempreferences`
1858 DROP TABLE IF EXISTS `systempreferences`;
1859 CREATE TABLE `systempreferences` (
1860 `variable` varchar(50) NOT NULL default '',
1862 `options` mediumtext,
1864 `type` varchar(20) default NULL,
1865 PRIMARY KEY (`variable`)
1866 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1869 -- Table structure for table `tags`
1872 DROP TABLE IF EXISTS `tags`;
1873 CREATE TABLE `tags` (
1874 `entry` varchar(255) NOT NULL default '',
1875 `weight` bigint(20) NOT NULL default 0,
1876 PRIMARY KEY (`entry`)
1877 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1880 -- Table structure for table `tags_all`
1883 DROP TABLE IF EXISTS `tags_all`;
1884 CREATE TABLE `tags_all` (
1885 `tag_id` int(11) NOT NULL auto_increment,
1886 `borrowernumber` int(11) NOT NULL,
1887 `biblionumber` int(11) NOT NULL,
1888 `term` varchar(255) NOT NULL,
1889 `language` int(4) default NULL,
1890 `date_created` datetime NOT NULL,
1891 PRIMARY KEY (`tag_id`),
1892 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1893 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1894 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1895 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1896 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1897 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1898 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1901 -- Table structure for table `tags_approval`
1904 DROP TABLE IF EXISTS `tags_approval`;
1905 CREATE TABLE `tags_approval` (
1906 `term` varchar(255) NOT NULL,
1907 `approved` int(1) NOT NULL default '0',
1908 `date_approved` datetime default NULL,
1909 `approved_by` int(11) default NULL,
1910 `weight_total` int(9) NOT NULL default '1',
1911 PRIMARY KEY (`term`),
1912 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1913 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1914 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1915 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1918 -- Table structure for table `tags_index`
1921 DROP TABLE IF EXISTS `tags_index`;
1922 CREATE TABLE `tags_index` (
1923 `term` varchar(255) NOT NULL,
1924 `biblionumber` int(11) NOT NULL,
1925 `weight` int(9) NOT NULL default '1',
1926 PRIMARY KEY (`term`,`biblionumber`),
1927 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1928 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1929 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1930 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1931 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1932 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1935 -- Table structure for table `userflags`
1938 DROP TABLE IF EXISTS `userflags`;
1939 CREATE TABLE `userflags` (
1940 `bit` int(11) NOT NULL default 0,
1941 `flag` varchar(30) default NULL,
1942 `flagdesc` varchar(255) default NULL,
1943 `defaulton` int(11) default NULL,
1945 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1948 -- Table structure for table `virtualshelves`
1951 DROP TABLE IF EXISTS `virtualshelves`;
1952 CREATE TABLE `virtualshelves` (
1953 `shelfnumber` int(11) NOT NULL auto_increment,
1954 `shelfname` varchar(255) default NULL,
1955 `owner` varchar(80) default NULL,
1956 `category` varchar(1) default NULL,
1957 `sortfield` varchar(16) default NULL,
1958 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1959 PRIMARY KEY (`shelfnumber`)
1960 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1963 -- Table structure for table `virtualshelfcontents`
1966 DROP TABLE IF EXISTS `virtualshelfcontents`;
1967 CREATE TABLE `virtualshelfcontents` (
1968 `shelfnumber` int(11) NOT NULL default 0,
1969 `biblionumber` int(11) NOT NULL default 0,
1970 `flags` int(11) default NULL,
1971 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1972 KEY `shelfnumber` (`shelfnumber`),
1973 KEY `biblionumber` (`biblionumber`),
1974 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1975 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1976 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1979 -- Table structure for table `z3950servers`
1982 DROP TABLE IF EXISTS `z3950servers`;
1983 CREATE TABLE `z3950servers` (
1984 `host` varchar(255) default NULL,
1985 `port` int(11) default NULL,
1986 `db` varchar(255) default NULL,
1987 `userid` varchar(255) default NULL,
1988 `password` varchar(255) default NULL,
1990 `id` int(11) NOT NULL auto_increment,
1991 `checked` smallint(6) default NULL,
1992 `rank` int(11) default NULL,
1993 `syntax` varchar(80) default NULL,
1995 `position` enum('primary','secondary','') NOT NULL default 'primary',
1996 `type` enum('zed','opensearch') NOT NULL default 'zed',
1997 `encoding` text default NULL,
1998 `description` text NOT NULL,
2000 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2003 -- Table structure for table `zebraqueue`
2006 DROP TABLE IF EXISTS `zebraqueue`;
2007 CREATE TABLE `zebraqueue` (
2008 `id` int(11) NOT NULL auto_increment,
2009 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2010 `operation` char(20) NOT NULL default '',
2011 `server` char(20) NOT NULL default '',
2012 `done` int(11) NOT NULL default '0',
2013 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2015 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2016 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2018 DROP TABLE IF EXISTS `services_throttle`;
2019 CREATE TABLE `services_throttle` (
2020 `service_type` varchar(10) NOT NULL default '',
2021 `service_count` varchar(45) default NULL,
2022 PRIMARY KEY (`service_type`)
2023 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2025 -- http://www.w3.org/International/articles/language-tags/
2028 DROP TABLE IF EXISTS language_subtag_registry;
2029 CREATE TABLE language_subtag_registry (
2031 type varchar(25), -- language-script-region-variant-extension-privateuse
2032 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2034 id int(11) NOT NULL auto_increment,
2036 KEY `subtag` (`subtag`)
2037 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2039 -- TODO: add suppress_scripts
2040 -- this maps three letter codes defined in iso639.2 back to their
2041 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2042 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2043 CREATE TABLE language_rfc4646_to_iso639 (
2044 rfc4646_subtag varchar(25),
2045 iso639_2_code varchar(25),
2046 id int(11) NOT NULL auto_increment,
2048 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2049 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2051 DROP TABLE IF EXISTS language_descriptions;
2052 CREATE TABLE language_descriptions (
2056 description varchar(255),
2057 id int(11) NOT NULL auto_increment,
2059 KEY `lang` (`lang`),
2060 KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
2061 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2063 -- bi-directional support, keyed by script subcode
2064 DROP TABLE IF EXISTS language_script_bidi;
2065 CREATE TABLE language_script_bidi (
2066 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2067 bidi varchar(3), -- rtl ltr
2068 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2069 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2071 -- TODO: need to map language subtags to script subtags for detection
2072 -- of bidi when script is not specified (like ar, he)
2073 DROP TABLE IF EXISTS language_script_mapping;
2074 CREATE TABLE language_script_mapping (
2075 language_subtag varchar(25),
2076 script_subtag varchar(25),
2077 KEY `language_subtag` (`language_subtag`)
2078 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2080 DROP TABLE IF EXISTS `permissions`;
2081 CREATE TABLE `permissions` (
2082 `module_bit` int(11) NOT NULL DEFAULT 0,
2083 `code` varchar(64) DEFAULT NULL,
2084 `description` varchar(255) DEFAULT NULL,
2085 PRIMARY KEY (`module_bit`, `code`),
2086 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2087 ON DELETE CASCADE ON UPDATE CASCADE
2088 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2090 DROP TABLE IF EXISTS `serialitems`;
2091 CREATE TABLE `serialitems` (
2092 `itemnumber` int(11) NOT NULL,
2093 `serialid` int(11) NOT NULL,
2094 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2095 KEY `serialitems_sfk_1` (`serialid`),
2096 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
2097 CONSTRAINT serialitems_sfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
2098 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2100 DROP TABLE IF EXISTS `user_permissions`;
2101 CREATE TABLE `user_permissions` (
2102 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2103 `module_bit` int(11) NOT NULL DEFAULT 0,
2104 `code` varchar(64) DEFAULT NULL,
2105 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2106 ON DELETE CASCADE ON UPDATE CASCADE,
2107 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2108 ON DELETE CASCADE ON UPDATE CASCADE
2109 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2112 -- Table structure for table `tmp_holdsqueue`
2115 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2116 CREATE TABLE `tmp_holdsqueue` (
2117 `biblionumber` int(11) default NULL,
2118 `itemnumber` int(11) default NULL,
2119 `barcode` varchar(20) default NULL,
2120 `surname` mediumtext NOT NULL,
2123 `borrowernumber` int(11) NOT NULL,
2124 `cardnumber` varchar(16) default NULL,
2125 `reservedate` date default NULL,
2127 `itemcallnumber` varchar(255) default NULL,
2128 `holdingbranch` varchar(10) default NULL,
2129 `pickbranch` varchar(10) default NULL,
2131 `item_level_request` tinyint(4) NOT NULL default 0
2132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2135 -- Table structure for table `message_queue`
2138 DROP TABLE IF EXISTS `message_queue`;
2139 CREATE TABLE `message_queue` (
2140 `message_id` int(11) NOT NULL auto_increment,
2141 `borrowernumber` int(11) default NULL,
2144 `metadata` text DEFAULT NULL,
2145 `letter_code` varchar(64) DEFAULT NULL,
2146 `message_transport_type` varchar(20) NOT NULL,
2147 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2148 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2149 `to_address` mediumtext,
2150 `from_address` mediumtext,
2151 `content_type` text,
2152 KEY `message_id` (`message_id`),
2153 KEY `borrowernumber` (`borrowernumber`),
2154 KEY `message_transport_type` (`message_transport_type`),
2155 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2156 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2157 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2160 -- Table structure for table `message_transport_types`
2163 DROP TABLE IF EXISTS `message_transport_types`;
2164 CREATE TABLE `message_transport_types` (
2165 `message_transport_type` varchar(20) NOT NULL,
2166 PRIMARY KEY (`message_transport_type`)
2167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2170 -- Table structure for table `message_attributes`
2173 DROP TABLE IF EXISTS `message_attributes`;
2174 CREATE TABLE `message_attributes` (
2175 `message_attribute_id` int(11) NOT NULL auto_increment,
2176 `message_name` varchar(40) NOT NULL default '',
2177 `takes_days` tinyint(1) NOT NULL default '0',
2178 PRIMARY KEY (`message_attribute_id`),
2179 UNIQUE KEY `message_name` (`message_name`)
2180 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2183 -- Table structure for table `message_transports`
2186 DROP TABLE IF EXISTS `message_transports`;
2187 CREATE TABLE `message_transports` (
2188 `message_attribute_id` int(11) NOT NULL,
2189 `message_transport_type` varchar(20) NOT NULL,
2190 `is_digest` tinyint(1) NOT NULL default '0',
2191 `letter_module` varchar(20) NOT NULL default '',
2192 `letter_code` varchar(20) NOT NULL default '',
2193 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2194 KEY `message_transport_type` (`message_transport_type`),
2195 KEY `letter_module` (`letter_module`,`letter_code`),
2196 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2197 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2198 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2202 -- Table structure for table `borrower_message_preferences`
2205 DROP TABLE IF EXISTS `borrower_message_preferences`;
2206 CREATE TABLE `borrower_message_preferences` (
2207 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2208 `borrowernumber` int(11) default NULL,
2209 `categorycode` varchar(10) default NULL,
2210 `message_attribute_id` int(11) default '0',
2211 `days_in_advance` int(11) default '0',
2212 `wants_digest` tinyint(1) NOT NULL default '0',
2213 PRIMARY KEY (`borrower_message_preference_id`),
2214 KEY `borrowernumber` (`borrowernumber`),
2215 KEY `categorycode` (`categorycode`),
2216 KEY `message_attribute_id` (`message_attribute_id`),
2217 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2218 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2219 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2220 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2223 -- Table structure for table `borrower_message_transport_preferences`
2226 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2227 CREATE TABLE `borrower_message_transport_preferences` (
2228 `borrower_message_preference_id` int(11) NOT NULL default '0',
2229 `message_transport_type` varchar(20) NOT NULL default '0',
2230 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2231 KEY `message_transport_type` (`message_transport_type`),
2232 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,
2233 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
2234 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2237 -- Table structure for the table branch_transfer_limits
2240 DROP TABLE IF EXISTS `branch_transfer_limits`;
2241 CREATE TABLE branch_transfer_limits (
2242 limitId int(8) NOT NULL auto_increment,
2243 toBranch varchar(10) NOT NULL,
2244 fromBranch varchar(10) NOT NULL,
2245 itemtype varchar(10) NULL,
2246 ccode varchar(10) NULL,
2247 PRIMARY KEY (limitId)
2248 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2251 -- Table structure for table `item_circulation_alert_preferences`
2254 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2255 CREATE TABLE `item_circulation_alert_preferences` (
2256 `id` int(11) NOT NULL auto_increment,
2257 `branchcode` varchar(10) NOT NULL,
2258 `categorycode` varchar(10) NOT NULL,
2259 `item_type` varchar(10) NOT NULL,
2260 `notification` varchar(16) NOT NULL,
2262 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2266 -- Table structure for table `messages`
2268 DROP TABLE IF EXISTS `messages`;
2269 CREATE TABLE `messages` (
2270 `message_id` int(11) NOT NULL auto_increment,
2271 `borrowernumber` int(11) NOT NULL,
2272 `branchcode` varchar(10) default NULL,
2273 `message_type` varchar(1) NOT NULL,
2274 `message` text NOT NULL,
2275 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2276 PRIMARY KEY (`message_id`)
2277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2280 -- Table structure for table `accountlines`
2283 DROP TABLE IF EXISTS `accountlines`;
2284 CREATE TABLE `accountlines` (
2285 `borrowernumber` int(11) NOT NULL default 0,
2286 `accountno` smallint(6) NOT NULL default 0,
2287 `itemnumber` int(11) default NULL,
2288 `date` date default NULL,
2289 `amount` decimal(28,6) default NULL,
2290 `description` mediumtext,
2291 `dispute` mediumtext,
2292 `accounttype` varchar(5) default NULL,
2293 `amountoutstanding` decimal(28,6) default NULL,
2294 `lastincrement` decimal(28,6) default NULL,
2295 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2296 `notify_id` int(11) NOT NULL default 0,
2297 `notify_level` int(2) NOT NULL default 0,
2298 KEY `acctsborridx` (`borrowernumber`),
2299 KEY `timeidx` (`timestamp`),
2300 KEY `itemnumber` (`itemnumber`),
2301 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2302 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2303 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2306 -- Table structure for table `accountoffsets`
2309 DROP TABLE IF EXISTS `accountoffsets`;
2310 CREATE TABLE `accountoffsets` (
2311 `borrowernumber` int(11) NOT NULL default 0,
2312 `accountno` smallint(6) NOT NULL default 0,
2313 `offsetaccount` smallint(6) NOT NULL default 0,
2314 `offsetamount` decimal(28,6) default NULL,
2315 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2316 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2320 -- Table structure for table `action_logs`
2323 DROP TABLE IF EXISTS `action_logs`;
2324 CREATE TABLE `action_logs` (
2325 `action_id` int(11) NOT NULL auto_increment,
2326 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2327 `user` int(11) NOT NULL default 0,
2330 `object` int(11) default NULL,
2332 PRIMARY KEY (`action_id`),
2333 KEY (`timestamp`,`user`)
2334 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2337 -- Table structure for table `alert`
2340 DROP TABLE IF EXISTS `alert`;
2341 CREATE TABLE `alert` (
2342 `alertid` int(11) NOT NULL auto_increment,
2343 `borrowernumber` int(11) NOT NULL default 0,
2344 `type` varchar(10) NOT NULL default '',
2345 `externalid` varchar(20) NOT NULL default '',
2346 PRIMARY KEY (`alertid`),
2347 KEY `borrowernumber` (`borrowernumber`),
2348 KEY `type` (`type`,`externalid`)
2349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2352 -- Table structure for table `aqbasketgroups`
2355 DROP TABLE IF EXISTS `aqbasketgroups`;
2356 CREATE TABLE `aqbasketgroups` (
2357 `id` int(11) NOT NULL auto_increment,
2358 `name` varchar(50) default NULL,
2359 `closed` tinyint(1) default NULL,
2360 `booksellerid` int(11) NOT NULL,
2361 `deliveryplace` varchar(10) default NULL,
2362 `freedeliveryplace` text default NULL,
2363 `deliverycomment` varchar(255) default NULL,
2364 `billingplace` varchar(10) default NULL,
2366 KEY `booksellerid` (`booksellerid`),
2367 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2368 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2371 -- Table structure for table `aqbasket`
2374 DROP TABLE IF EXISTS `aqbasket`;
2375 CREATE TABLE `aqbasket` (
2376 `basketno` int(11) NOT NULL auto_increment,
2377 `basketname` varchar(50) default NULL,
2379 `booksellernote` mediumtext,
2380 `contractnumber` int(11),
2381 `creationdate` date default NULL,
2382 `closedate` date default NULL,
2383 `booksellerid` int(11) NOT NULL default 1,
2384 `authorisedby` varchar(10) default NULL,
2385 `booksellerinvoicenumber` mediumtext,
2386 `basketgroupid` int(11),
2387 PRIMARY KEY (`basketno`),
2388 KEY `booksellerid` (`booksellerid`),
2389 KEY `basketgroupid` (`basketgroupid`),
2390 KEY `contractnumber` (`contractnumber`),
2391 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2392 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2393 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2394 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2397 -- Table structure for table `aqbooksellers`
2400 DROP TABLE IF EXISTS `aqbooksellers`;
2401 CREATE TABLE `aqbooksellers` (
2402 `id` int(11) NOT NULL auto_increment,
2403 `name` mediumtext NOT NULL,
2404 `address1` mediumtext,
2405 `address2` mediumtext,
2406 `address3` mediumtext,
2407 `address4` mediumtext,
2408 `phone` varchar(30) default NULL,
2409 `accountnumber` mediumtext,
2410 `othersupplier` mediumtext,
2411 `currency` varchar(3) NOT NULL default '',
2412 `booksellerfax` mediumtext,
2414 `bookselleremail` mediumtext,
2415 `booksellerurl` mediumtext,
2416 `contact` varchar(100) default NULL,
2417 `postal` mediumtext,
2418 `url` varchar(255) default NULL,
2419 `contpos` varchar(100) default NULL,
2420 `contphone` varchar(100) default NULL,
2421 `contfax` varchar(100) default NULL,
2422 `contaltphone` varchar(100) default NULL,
2423 `contemail` varchar(100) default NULL,
2424 `contnotes` mediumtext,
2425 `active` tinyint(4) default NULL,
2426 `listprice` varchar(10) default NULL,
2427 `invoiceprice` varchar(10) default NULL,
2428 `gstreg` tinyint(4) default NULL,
2429 `listincgst` tinyint(4) default NULL,
2430 `invoiceincgst` tinyint(4) default NULL,
2431 `gstrate` decimal(6,4) default NULL,
2432 `discount` float(6,4) default NULL,
2433 `fax` varchar(50) default NULL,
2435 KEY `listprice` (`listprice`),
2436 KEY `invoiceprice` (`invoiceprice`),
2437 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2438 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2439 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2442 -- Table structure for table `aqbudgets`
2445 DROP TABLE IF EXISTS `aqbudgets`;
2446 CREATE TABLE `aqbudgets` (
2447 `budget_id` int(11) NOT NULL auto_increment,
2448 `budget_parent_id` int(11) default NULL,
2449 `budget_code` varchar(30) default NULL,
2450 `budget_name` varchar(80) default NULL,
2451 `budget_branchcode` varchar(10) default NULL,
2452 `budget_amount` decimal(28,6) NULL default '0.00',
2453 `budget_encumb` decimal(28,6) NULL default '0.00',
2454 `budget_expend` decimal(28,6) NULL default '0.00',
2455 `budget_notes` mediumtext,
2456 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2457 `budget_period_id` int(11) default NULL,
2458 `sort1_authcat` varchar(80) default NULL,
2459 `sort2_authcat` varchar(80) default NULL,
2460 `budget_owner_id` int(11) default NULL,
2461 `budget_permission` int(1) default '0',
2462 PRIMARY KEY (`budget_id`)
2463 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2467 -- Table structure for table `aqbudgetperiods`
2471 DROP TABLE IF EXISTS `aqbudgetperiods`;
2472 CREATE TABLE `aqbudgetperiods` (
2473 `budget_period_id` int(11) NOT NULL auto_increment,
2474 `budget_period_startdate` date NOT NULL,
2475 `budget_period_enddate` date NOT NULL,
2476 `budget_period_active` tinyint(1) default '0',
2477 `budget_period_description` mediumtext,
2478 `budget_period_total` decimal(28,6),
2479 `budget_period_locked` tinyint(1) default NULL,
2480 `sort1_authcat` varchar(10) default NULL,
2481 `sort2_authcat` varchar(10) default NULL,
2482 PRIMARY KEY (`budget_period_id`)
2483 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2486 -- Table structure for table `aqbudgets_planning`
2489 DROP TABLE IF EXISTS `aqbudgets_planning`;
2490 CREATE TABLE `aqbudgets_planning` (
2491 `plan_id` int(11) NOT NULL auto_increment,
2492 `budget_id` int(11) NOT NULL,
2493 `budget_period_id` int(11) NOT NULL,
2494 `estimated_amount` decimal(28,6) default NULL,
2495 `authcat` varchar(30) NOT NULL,
2496 `authvalue` varchar(30) NOT NULL,
2497 `display` tinyint(1) DEFAULT 1,
2498 PRIMARY KEY (`plan_id`),
2499 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2500 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2503 -- Table structure for table 'aqcontract'
2506 DROP TABLE IF EXISTS `aqcontract`;
2507 CREATE TABLE `aqcontract` (
2508 `contractnumber` int(11) NOT NULL auto_increment,
2509 `contractstartdate` date default NULL,
2510 `contractenddate` date default NULL,
2511 `contractname` varchar(50) default NULL,
2512 `contractdescription` mediumtext,
2513 `booksellerid` int(11) not NULL,
2514 PRIMARY KEY (`contractnumber`),
2515 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2516 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2517 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2520 -- Table structure for table `aqorderdelivery`
2523 DROP TABLE IF EXISTS `aqorderdelivery`;
2524 CREATE TABLE `aqorderdelivery` (
2525 `ordernumber` date default NULL,
2526 `deliverynumber` smallint(6) NOT NULL default 0,
2527 `deliverydate` varchar(18) default NULL,
2528 `qtydelivered` smallint(6) default NULL,
2529 `deliverycomments` mediumtext
2530 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2533 -- Table structure for table `aqorders`
2536 DROP TABLE IF EXISTS `aqorders`;
2537 CREATE TABLE `aqorders` (
2538 `ordernumber` int(11) NOT NULL auto_increment,
2539 `biblionumber` int(11) default NULL,
2540 `entrydate` date default NULL,
2541 `quantity` smallint(6) default NULL,
2542 `currency` varchar(3) default NULL,
2543 `listprice` decimal(28,6) default NULL,
2544 `totalamount` decimal(28,6) default NULL,
2545 `datereceived` date default NULL,
2546 `booksellerinvoicenumber` mediumtext,
2547 `freight` decimal(28,6) default NULL,
2548 `unitprice` decimal(28,6) default NULL,
2549 `quantityreceived` smallint(6) NOT NULL default 0,
2550 `cancelledby` varchar(10) default NULL,
2551 `datecancellationprinted` date default NULL,
2553 `supplierreference` mediumtext,
2554 `purchaseordernumber` mediumtext,
2555 `subscription` tinyint(1) default NULL,
2556 `serialid` varchar(30) default NULL,
2557 `basketno` int(11) default NULL,
2558 `biblioitemnumber` int(11) default NULL,
2559 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2560 `rrp` decimal(13,2) default NULL,
2561 `ecost` decimal(13,2) default NULL,
2562 `gst` decimal(13,2) default NULL,
2563 `budget_id` int(11) NOT NULL,
2564 `budgetgroup_id` int(11) NOT NULL,
2565 `budgetdate` date default NULL,
2566 `sort1` varchar(80) default NULL,
2567 `sort2` varchar(80) default NULL,
2568 `sort1_authcat` varchar(10) default NULL,
2569 `sort2_authcat` varchar(10) default NULL,
2570 `uncertainprice` tinyint(1),
2571 PRIMARY KEY (`ordernumber`),
2572 KEY `basketno` (`basketno`),
2573 KEY `biblionumber` (`biblionumber`),
2574 KEY `budget_id` (`budget_id`),
2575 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2576 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2577 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2581 -- Table structure for table `aqorders_items`
2584 DROP TABLE IF EXISTS `aqorders_items`;
2585 CREATE TABLE `aqorders_items` (
2586 `ordernumber` int(11) NOT NULL,
2587 `itemnumber` int(11) NOT NULL,
2588 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2589 PRIMARY KEY (`itemnumber`),
2590 KEY `ordernumber` (`ordernumber`)
2591 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2594 -- Table structure for table `fieldmapping`
2597 DROP TABLE IF EXISTS `fieldmapping`;
2598 CREATE TABLE `fieldmapping` (
2599 `id` int(11) NOT NULL auto_increment,
2600 `field` varchar(255) NOT NULL,
2601 `frameworkcode` char(4) NOT NULL default '',
2602 `fieldcode` char(3) NOT NULL,
2603 `subfieldcode` char(1) NOT NULL,
2605 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2608 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2609 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2610 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2611 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2612 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2613 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2614 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2615 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;