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 `zipcode` varchar(25) default NULL,
213 `mobile` varchar(50) default NULL,
217 `B_streetnumber` varchar(10) default NULL,
218 `B_streettype` varchar(50) default NULL,
219 `B_address` varchar(100) default NULL,
220 `B_address2` text default NULL,
222 `B_zipcode` varchar(25) default NULL,
225 `B_phone` mediumtext,
226 `dateofbirth` date default NULL,
227 `branchcode` varchar(10) NOT NULL default '',
228 `categorycode` varchar(10) NOT NULL default '',
229 `dateenrolled` date default NULL,
230 `dateexpiry` date default NULL,
231 `gonenoaddress` tinyint(1) default NULL,
232 `lost` tinyint(1) default NULL,
233 `debarred` tinyint(1) default NULL,
234 `contactname` mediumtext,
235 `contactfirstname` text,
237 `guarantorid` int(11) default NULL,
238 `borrowernotes` mediumtext,
239 `relationship` varchar(100) default NULL,
240 `ethnicity` varchar(50) default NULL,
241 `ethnotes` varchar(255) default NULL,
242 `sex` varchar(1) default NULL,
243 `password` varchar(30) default NULL,
244 `flags` int(11) default NULL,
245 `userid` varchar(30) default NULL,
246 `opacnote` mediumtext,
247 `contactnote` varchar(255) default NULL,
248 `sort1` varchar(80) default NULL,
249 `sort2` varchar(80) default NULL,
250 `altcontactfirstname` varchar(255) default NULL,
251 `altcontactsurname` varchar(255) default NULL,
252 `altcontactaddress1` varchar(255) default NULL,
253 `altcontactaddress2` varchar(255) default NULL,
254 `altcontactaddress3` varchar(255) default NULL,
255 `altcontactzipcode` varchar(50) default NULL,
256 `altcontactcountry` text default NULL,
257 `altcontactphone` varchar(50) default NULL,
258 `smsalertnumber` varchar(50) default NULL,
259 `privacy` integer(11) DEFAULT '1' NOT NULL,
260 UNIQUE KEY `cardnumber` (`cardnumber`),
261 PRIMARY KEY `borrowernumber` (`borrowernumber`),
262 KEY `categorycode` (`categorycode`),
263 KEY `branchcode` (`branchcode`),
264 KEY `userid` (`userid`),
265 KEY `guarantorid` (`guarantorid`),
266 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
267 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
268 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
271 -- Table structure for table `borrower_attribute_types`
274 DROP TABLE IF EXISTS `borrower_attribute_types`;
275 CREATE TABLE `borrower_attribute_types` (
276 `code` varchar(10) NOT NULL,
277 `description` varchar(255) NOT NULL,
278 `repeatable` tinyint(1) NOT NULL default 0,
279 `unique_id` tinyint(1) NOT NULL default 0,
280 `opac_display` tinyint(1) NOT NULL default 0,
281 `password_allowed` tinyint(1) NOT NULL default 0,
282 `staff_searchable` tinyint(1) NOT NULL default 0,
283 `authorised_value_category` varchar(10) default NULL,
284 PRIMARY KEY (`code`),
285 KEY `auth_val_cat_idx` (`authorised_value_category`)
286 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
289 -- Table structure for table `borrower_attributes`
292 DROP TABLE IF EXISTS `borrower_attributes`;
293 CREATE TABLE `borrower_attributes` (
294 `borrowernumber` int(11) NOT NULL,
295 `code` varchar(10) NOT NULL,
296 `attribute` varchar(64) default NULL,
297 `password` varchar(64) default NULL,
298 KEY `borrowernumber` (`borrowernumber`),
299 KEY `code_attribute` (`code`, `attribute`),
300 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
301 ON DELETE CASCADE ON UPDATE CASCADE,
302 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
303 ON DELETE CASCADE ON UPDATE CASCADE
304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
306 DROP TABLE IF EXISTS `branch_item_rules`;
307 CREATE TABLE `branch_item_rules` (
308 `branchcode` varchar(10) NOT NULL,
309 `itemtype` varchar(10) NOT NULL,
310 `holdallowed` tinyint(1) default NULL,
311 PRIMARY KEY (`itemtype`,`branchcode`),
312 KEY `branch_item_rules_ibfk_2` (`branchcode`),
313 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
314 ON DELETE CASCADE ON UPDATE CASCADE,
315 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
316 ON DELETE CASCADE ON UPDATE CASCADE
317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
320 -- Table structure for table `branchcategories`
323 DROP TABLE IF EXISTS `branchcategories`;
324 CREATE TABLE `branchcategories` (
325 `categorycode` varchar(10) NOT NULL default '',
326 `categoryname` varchar(32),
327 `codedescription` mediumtext,
328 `categorytype` varchar(16),
329 PRIMARY KEY (`categorycode`)
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
333 -- Table structure for table `branches`
336 DROP TABLE IF EXISTS `branches`;
337 CREATE TABLE `branches` (
338 `branchcode` varchar(10) NOT NULL default '',
339 `branchname` mediumtext NOT NULL,
340 `branchaddress1` mediumtext,
341 `branchaddress2` mediumtext,
342 `branchaddress3` mediumtext,
343 `branchzip` varchar(25) default NULL,
344 `branchcity` mediumtext,
345 `branchcountry` text,
346 `branchphone` mediumtext,
347 `branchfax` mediumtext,
348 `branchemail` mediumtext,
349 `branchurl` mediumtext,
350 `issuing` tinyint(4) default NULL,
351 `branchip` varchar(15) default NULL,
352 `branchprinter` varchar(100) default NULL,
353 `branchnotes` mediumtext,
354 UNIQUE KEY `branchcode` (`branchcode`)
355 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
358 -- Table structure for table `branchrelations`
361 DROP TABLE IF EXISTS `branchrelations`;
362 CREATE TABLE `branchrelations` (
363 `branchcode` varchar(10) NOT NULL default '',
364 `categorycode` varchar(10) NOT NULL default '',
365 PRIMARY KEY (`branchcode`,`categorycode`),
366 KEY `branchcode` (`branchcode`),
367 KEY `categorycode` (`categorycode`),
368 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
369 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
370 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
373 -- Table structure for table `branchtransfers`
376 DROP TABLE IF EXISTS `branchtransfers`;
377 CREATE TABLE `branchtransfers` (
378 `itemnumber` int(11) NOT NULL default 0,
379 `datesent` datetime default NULL,
380 `frombranch` varchar(10) NOT NULL default '',
381 `datearrived` datetime default NULL,
382 `tobranch` varchar(10) NOT NULL default '',
383 `comments` mediumtext,
384 KEY `frombranch` (`frombranch`),
385 KEY `tobranch` (`tobranch`),
386 KEY `itemnumber` (`itemnumber`),
387 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
388 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
389 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
390 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
394 -- Table structure for table `browser`
396 DROP TABLE IF EXISTS `browser`;
397 CREATE TABLE `browser` (
398 `level` int(11) NOT NULL,
399 `classification` varchar(20) NOT NULL,
400 `description` varchar(255) NOT NULL,
401 `number` bigint(20) NOT NULL,
402 `endnode` tinyint(4) NOT NULL
403 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
406 -- Table structure for table `categories`
409 DROP TABLE IF EXISTS `categories`;
410 CREATE TABLE `categories` (
411 `categorycode` varchar(10) NOT NULL default '',
412 `description` mediumtext,
413 `enrolmentperiod` smallint(6) default NULL,
414 `enrolmentperioddate` DATE NULL DEFAULT NULL,
415 `upperagelimit` smallint(6) default NULL,
416 `dateofbirthrequired` tinyint(1) default NULL,
417 `finetype` varchar(30) default NULL,
418 `bulk` tinyint(1) default NULL,
419 `enrolmentfee` decimal(28,6) default NULL,
420 `overduenoticerequired` tinyint(1) default NULL,
421 `issuelimit` smallint(6) default NULL,
422 `reservefee` decimal(28,6) default NULL,
423 `category_type` varchar(1) NOT NULL default 'A',
424 PRIMARY KEY (`categorycode`),
425 UNIQUE KEY `categorycode` (`categorycode`)
426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
429 -- Table: collections
431 CREATE TABLE collections (
432 colId integer(11) NOT NULL auto_increment,
433 colTitle varchar(100) NOT NULL DEFAULT '',
434 colDesc text NOT NULL,
435 colBranchcode varchar(4) DEFAULT NULL comment 'branchcode for branch where item should be held.',
437 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
440 -- Table: collections_tracking
442 CREATE TABLE collections_tracking (
443 ctId integer(11) NOT NULL auto_increment,
444 colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId',
445 itemnumber integer(11) NOT NULL DEFAULT 0 comment 'items.itemnumber',
447 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
450 -- Table structure for table `borrower_branch_circ_rules`
453 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
454 CREATE TABLE `branch_borrower_circ_rules` (
455 `branchcode` VARCHAR(10) NOT NULL,
456 `categorycode` VARCHAR(10) NOT NULL,
457 `maxissueqty` int(4) default NULL,
458 PRIMARY KEY (`categorycode`, `branchcode`),
459 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
460 ON DELETE CASCADE ON UPDATE CASCADE,
461 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
462 ON DELETE CASCADE ON UPDATE CASCADE
463 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
466 -- Table structure for table `default_borrower_circ_rules`
469 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
470 CREATE TABLE `default_borrower_circ_rules` (
471 `categorycode` VARCHAR(10) NOT NULL,
472 `maxissueqty` int(4) default NULL,
473 PRIMARY KEY (`categorycode`),
474 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
475 ON DELETE CASCADE ON UPDATE CASCADE
476 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
479 -- Table structure for table `default_branch_circ_rules`
482 DROP TABLE IF EXISTS `default_branch_circ_rules`;
483 CREATE TABLE `default_branch_circ_rules` (
484 `branchcode` VARCHAR(10) NOT NULL,
485 `maxissueqty` int(4) default NULL,
486 `holdallowed` tinyint(1) default NULL,
487 PRIMARY KEY (`branchcode`),
488 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
489 ON DELETE CASCADE ON UPDATE CASCADE
490 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
493 -- Table structure for table `default_branch_item_rules`
495 DROP TABLE IF EXISTS `default_branch_item_rules`;
496 CREATE TABLE `default_branch_item_rules` (
497 `itemtype` varchar(10) NOT NULL,
498 `holdallowed` tinyint(1) default NULL,
499 PRIMARY KEY (`itemtype`),
500 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
501 ON DELETE CASCADE ON UPDATE CASCADE
502 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
505 -- Table structure for table `default_circ_rules`
508 DROP TABLE IF EXISTS `default_circ_rules`;
509 CREATE TABLE `default_circ_rules` (
510 `singleton` enum('singleton') NOT NULL default 'singleton',
511 `maxissueqty` int(4) default NULL,
512 `holdallowed` int(1) default NULL,
513 PRIMARY KEY (`singleton`)
514 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
517 -- Table structure for table `cities`
520 DROP TABLE IF EXISTS `cities`;
521 CREATE TABLE `cities` (
522 `cityid` int(11) NOT NULL auto_increment,
523 `city_name` varchar(100) NOT NULL default '',
524 `city_zipcode` varchar(20) default NULL,
525 PRIMARY KEY (`cityid`)
526 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
529 -- Table structure for table `class_sort_rules`
532 DROP TABLE IF EXISTS `class_sort_rules`;
533 CREATE TABLE `class_sort_rules` (
534 `class_sort_rule` varchar(10) NOT NULL default '',
535 `description` mediumtext,
536 `sort_routine` varchar(30) NOT NULL default '',
537 PRIMARY KEY (`class_sort_rule`),
538 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
539 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
542 -- Table structure for table `class_sources`
545 DROP TABLE IF EXISTS `class_sources`;
546 CREATE TABLE `class_sources` (
547 `cn_source` varchar(10) NOT NULL default '',
548 `description` mediumtext,
549 `used` tinyint(4) NOT NULL default 0,
550 `class_sort_rule` varchar(10) NOT NULL default '',
551 PRIMARY KEY (`cn_source`),
552 UNIQUE KEY `cn_source_idx` (`cn_source`),
553 KEY `used_idx` (`used`),
554 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
555 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
558 -- Table structure for table `currency`
561 DROP TABLE IF EXISTS `currency`;
562 CREATE TABLE `currency` (
563 `currency` varchar(10) NOT NULL default '',
564 `symbol` varchar(5) default NULL,
565 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
566 `rate` float(7,5) default NULL,
567 `active` tinyint(1) default NULL,
568 PRIMARY KEY (`currency`)
569 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
572 -- Table structure for table `deletedbiblio`
575 DROP TABLE IF EXISTS `deletedbiblio`;
576 CREATE TABLE `deletedbiblio` (
577 `biblionumber` int(11) NOT NULL default 0,
578 `frameworkcode` varchar(4) NOT NULL default '',
581 `unititle` mediumtext,
583 `serial` tinyint(1) default NULL,
584 `seriestitle` mediumtext,
585 `copyrightdate` smallint(6) default NULL,
586 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
587 `datecreated` DATE NOT NULL,
588 `abstract` mediumtext,
589 PRIMARY KEY (`biblionumber`),
590 KEY `blbnoidx` (`biblionumber`)
591 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
594 -- Table structure for table `deletedbiblioitems`
597 DROP TABLE IF EXISTS `deletedbiblioitems`;
598 CREATE TABLE `deletedbiblioitems` (
599 `biblioitemnumber` int(11) NOT NULL default 0,
600 `biblionumber` int(11) NOT NULL default 0,
603 `itemtype` varchar(10) default NULL,
604 `isbn` varchar(30) default NULL,
605 `issn` varchar(9) default NULL,
606 `publicationyear` text,
607 `publishercode` varchar(255) default NULL,
608 `volumedate` date default NULL,
610 `collectiontitle` mediumtext default NULL,
611 `collectionissn` text default NULL,
612 `collectionvolume` mediumtext default NULL,
613 `editionstatement` text default NULL,
614 `editionresponsibility` text default NULL,
615 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
616 `illus` varchar(255) default NULL,
617 `pages` varchar(255) default NULL,
619 `size` varchar(255) default NULL,
620 `place` varchar(255) default NULL,
621 `lccn` varchar(25) default NULL,
623 `url` varchar(255) default NULL,
624 `cn_source` varchar(10) default NULL,
625 `cn_class` varchar(30) default NULL,
626 `cn_item` varchar(10) default NULL,
627 `cn_suffix` varchar(10) default NULL,
628 `cn_sort` varchar(30) default NULL,
629 `totalissues` int(10),
630 `marcxml` longtext NOT NULL,
631 PRIMARY KEY (`biblioitemnumber`),
632 KEY `bibinoidx` (`biblioitemnumber`),
633 KEY `bibnoidx` (`biblionumber`),
635 KEY `publishercode` (`publishercode`)
636 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
639 -- Table structure for table `deletedborrowers`
642 DROP TABLE IF EXISTS `deletedborrowers`;
643 CREATE TABLE `deletedborrowers` (
644 `borrowernumber` int(11) NOT NULL default 0,
645 `cardnumber` varchar(16) NOT NULL default '',
646 `surname` mediumtext NOT NULL,
649 `othernames` mediumtext,
651 `streetnumber` varchar(10) default NULL,
652 `streettype` varchar(50) default NULL,
653 `address` mediumtext NOT NULL,
655 `city` mediumtext NOT NULL,
656 `zipcode` varchar(25) default NULL,
660 `mobile` varchar(50) default NULL,
664 `B_streetnumber` varchar(10) default NULL,
665 `B_streettype` varchar(50) default NULL,
666 `B_address` varchar(100) default NULL,
667 `B_address2` text default NULL,
669 `B_zipcode` varchar(25) default NULL,
672 `B_phone` mediumtext,
673 `dateofbirth` date default NULL,
674 `branchcode` varchar(10) NOT NULL default '',
675 `categorycode` varchar(10) default NULL,
676 `dateenrolled` date default NULL,
677 `dateexpiry` date default NULL,
678 `gonenoaddress` tinyint(1) default NULL,
679 `lost` tinyint(1) default NULL,
680 `debarred` tinyint(1) default NULL,
681 `contactname` mediumtext,
682 `contactfirstname` text,
684 `guarantorid` int(11) default NULL,
685 `borrowernotes` mediumtext,
686 `relationship` varchar(100) default NULL,
687 `ethnicity` varchar(50) default NULL,
688 `ethnotes` varchar(255) default NULL,
689 `sex` varchar(1) default NULL,
690 `password` varchar(30) default NULL,
691 `flags` int(11) default NULL,
692 `userid` varchar(30) default NULL,
693 `opacnote` mediumtext,
694 `contactnote` varchar(255) default NULL,
695 `sort1` varchar(80) default NULL,
696 `sort2` varchar(80) default NULL,
697 `altcontactfirstname` varchar(255) default NULL,
698 `altcontactsurname` varchar(255) default NULL,
699 `altcontactaddress1` varchar(255) default NULL,
700 `altcontactaddress2` varchar(255) default NULL,
701 `altcontactaddress3` varchar(255) default NULL,
702 `altcontactzipcode` varchar(50) default NULL,
703 `altcontactcountry` text default NULL,
704 `altcontactphone` varchar(50) default NULL,
705 `smsalertnumber` varchar(50) default NULL,
706 `privacy` integer(11) DEFAULT '1' NOT NULL,
707 KEY `borrowernumber` (`borrowernumber`),
708 KEY `cardnumber` (`cardnumber`)
709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
712 -- Table structure for table `deleteditems`
715 DROP TABLE IF EXISTS `deleteditems`;
716 CREATE TABLE `deleteditems` (
717 `itemnumber` int(11) NOT NULL default 0,
718 `biblionumber` int(11) NOT NULL default 0,
719 `biblioitemnumber` int(11) NOT NULL default 0,
720 `barcode` varchar(20) default NULL,
721 `dateaccessioned` date default NULL,
722 `booksellerid` mediumtext default NULL,
723 `homebranch` varchar(10) default NULL,
724 `price` decimal(8,2) default NULL,
725 `replacementprice` decimal(8,2) default NULL,
726 `replacementpricedate` date default NULL,
727 `datelastborrowed` date default NULL,
728 `datelastseen` date default NULL,
729 `stack` tinyint(1) default NULL,
730 `notforloan` tinyint(1) NOT NULL default 0,
731 `damaged` tinyint(1) NOT NULL default 0,
732 `itemlost` tinyint(1) NOT NULL default 0,
733 `wthdrawn` tinyint(1) NOT NULL default 0,
734 `itemcallnumber` varchar(255) default NULL,
735 `issues` smallint(6) default NULL,
736 `renewals` smallint(6) default NULL,
737 `reserves` smallint(6) default NULL,
738 `restricted` tinyint(1) default NULL,
739 `itemnotes` mediumtext,
740 `holdingbranch` varchar(10) default NULL,
741 `paidfor` mediumtext,
742 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
743 `location` varchar(80) default NULL,
744 `permanent_location` varchar(80) default NULL,
745 `onloan` date default NULL,
746 `cn_source` varchar(10) default NULL,
747 `cn_sort` varchar(30) default NULL,
748 `ccode` varchar(10) default NULL,
749 `materials` varchar(10) default NULL,
750 `uri` varchar(255) default NULL,
751 `itype` varchar(10) default NULL,
752 `more_subfields_xml` longtext default NULL,
753 `enumchron` varchar(80) default NULL,
754 `copynumber` varchar(32) default NULL,
755 `stocknumber` varchar(32) default NULL,
757 PRIMARY KEY (`itemnumber`),
758 KEY `delitembarcodeidx` (`barcode`),
759 KEY `delitemstocknumberidx` (`stocknumber`),
760 KEY `delitembinoidx` (`biblioitemnumber`),
761 KEY `delitembibnoidx` (`biblionumber`),
762 KEY `delhomebranch` (`homebranch`),
763 KEY `delholdingbranch` (`holdingbranch`)
764 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
767 -- Table structure for table `ethnicity`
770 DROP TABLE IF EXISTS `ethnicity`;
771 CREATE TABLE `ethnicity` (
772 `code` varchar(10) NOT NULL default '',
773 `name` varchar(255) default NULL,
775 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
778 -- Table structure for table `export_format`
781 DROP TABLE IF EXISTS `export_format`;
782 CREATE TABLE `export_format` (
783 `export_format_id` int(11) NOT NULL auto_increment,
784 `profile` varchar(255) NOT NULL,
785 `description` mediumtext NOT NULL,
786 `marcfields` mediumtext NOT NULL,
787 `csv_separator` varchar(2) NOT NULL,
788 `field_separator` varchar(2) NOT NULL,
789 `subfield_separator` varchar(2) NOT NULL,
790 `encoding` varchar(255) NOT NULL,
791 PRIMARY KEY (`export_format_id`)
792 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
796 -- Table structure for table `hold_fill_targets`
799 DROP TABLE IF EXISTS `hold_fill_targets`;
800 CREATE TABLE hold_fill_targets (
801 `borrowernumber` int(11) NOT NULL,
802 `biblionumber` int(11) NOT NULL,
803 `itemnumber` int(11) NOT NULL,
804 `source_branchcode` varchar(10) default NULL,
805 `item_level_request` tinyint(4) NOT NULL default 0,
806 PRIMARY KEY `itemnumber` (`itemnumber`),
807 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
808 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
809 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
810 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
811 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
812 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
813 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
814 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
815 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
816 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
819 -- Table structure for table `import_batches`
822 DROP TABLE IF EXISTS `import_batches`;
823 CREATE TABLE `import_batches` (
824 `import_batch_id` int(11) NOT NULL auto_increment,
825 `matcher_id` int(11) default NULL,
826 `template_id` int(11) default NULL,
827 `branchcode` varchar(10) default NULL,
828 `num_biblios` int(11) NOT NULL default 0,
829 `num_items` int(11) NOT NULL default 0,
830 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
831 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
832 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
833 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
834 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
835 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
836 `file_name` varchar(100),
837 `comments` mediumtext,
838 PRIMARY KEY (`import_batch_id`),
839 KEY `branchcode` (`branchcode`)
840 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
843 -- Table structure for table `import_records`
846 DROP TABLE IF EXISTS `import_records`;
847 CREATE TABLE `import_records` (
848 `import_record_id` int(11) NOT NULL auto_increment,
849 `import_batch_id` int(11) NOT NULL,
850 `branchcode` varchar(10) default NULL,
851 `record_sequence` int(11) NOT NULL default 0,
852 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
853 `import_date` DATE default NULL,
854 `marc` longblob NOT NULL,
855 `marcxml` longtext NOT NULL,
856 `marcxml_old` longtext NOT NULL,
857 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
858 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
859 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
860 `import_error` mediumtext,
861 `encoding` varchar(40) NOT NULL default '',
862 `z3950random` varchar(40) default NULL,
863 PRIMARY KEY (`import_record_id`),
864 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
865 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
866 KEY `branchcode` (`branchcode`),
867 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
868 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
871 -- Table structure for `import_record_matches`
873 DROP TABLE IF EXISTS `import_record_matches`;
874 CREATE TABLE `import_record_matches` (
875 `import_record_id` int(11) NOT NULL,
876 `candidate_match_id` int(11) NOT NULL,
877 `score` int(11) NOT NULL default 0,
878 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
879 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
880 KEY `record_score` (`import_record_id`, `score`)
881 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
884 -- Table structure for table `import_biblios`
887 DROP TABLE IF EXISTS `import_biblios`;
888 CREATE TABLE `import_biblios` (
889 `import_record_id` int(11) NOT NULL,
890 `matched_biblionumber` int(11) default NULL,
891 `control_number` varchar(25) default NULL,
892 `original_source` varchar(25) default NULL,
893 `title` varchar(128) default NULL,
894 `author` varchar(80) default NULL,
895 `isbn` varchar(30) default NULL,
896 `issn` varchar(9) default NULL,
897 `has_items` tinyint(1) NOT NULL default 0,
898 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
899 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
900 KEY `matched_biblionumber` (`matched_biblionumber`),
901 KEY `title` (`title`),
903 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
906 -- Table structure for table `import_items`
909 DROP TABLE IF EXISTS `import_items`;
910 CREATE TABLE `import_items` (
911 `import_items_id` int(11) NOT NULL auto_increment,
912 `import_record_id` int(11) NOT NULL,
913 `itemnumber` int(11) default NULL,
914 `branchcode` varchar(10) default NULL,
915 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
916 `marcxml` longtext NOT NULL,
917 `import_error` mediumtext,
918 PRIMARY KEY (`import_items_id`),
919 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
920 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
921 KEY `itemnumber` (`itemnumber`),
922 KEY `branchcode` (`branchcode`)
923 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
926 -- Table structure for table `issues`
929 DROP TABLE IF EXISTS `issues`;
930 CREATE TABLE `issues` (
931 `borrowernumber` int(11) default NULL,
932 `itemnumber` int(11) default NULL,
933 `date_due` date default NULL,
934 `branchcode` varchar(10) default NULL,
935 `issuingbranch` varchar(18) default NULL,
936 `returndate` date default NULL,
937 `lastreneweddate` date default NULL,
938 `return` varchar(4) default NULL,
939 `renewals` tinyint(4) default NULL,
940 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
941 `issuedate` date default NULL,
942 KEY `issuesborridx` (`borrowernumber`),
943 KEY `issuesitemidx` (`itemnumber`),
944 KEY `bordate` (`borrowernumber`,`timestamp`),
945 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
946 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
947 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
950 -- Table structure for table `issuingrules`
953 DROP TABLE IF EXISTS `issuingrules`;
954 CREATE TABLE `issuingrules` (
955 `categorycode` varchar(10) NOT NULL default '',
956 `itemtype` varchar(10) NOT NULL default '',
957 `restrictedtype` tinyint(1) default NULL,
958 `rentaldiscount` decimal(28,6) default NULL,
959 `reservecharge` decimal(28,6) default NULL,
960 `fine` decimal(28,6) default NULL,
961 `finedays` int(11) default NULL,
962 `firstremind` int(11) default NULL,
963 `chargeperiod` int(11) default NULL,
964 `accountsent` int(11) default NULL,
965 `chargename` varchar(100) default NULL,
966 `maxissueqty` int(4) default NULL,
967 `issuelength` int(4) default NULL,
968 `renewalsallowed` smallint(6) NOT NULL default "0",
969 `reservesallowed` smallint(6) NOT NULL default "0",
970 `branchcode` varchar(10) NOT NULL default '',
971 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
972 KEY `categorycode` (`categorycode`),
973 KEY `itemtype` (`itemtype`)
974 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
977 -- Table structure for table `items`
980 DROP TABLE IF EXISTS `items`;
981 CREATE TABLE `items` (
982 `itemnumber` int(11) NOT NULL auto_increment,
983 `biblionumber` int(11) NOT NULL default 0,
984 `biblioitemnumber` int(11) NOT NULL default 0,
985 `barcode` varchar(20) default NULL,
986 `dateaccessioned` date default NULL,
987 `booksellerid` mediumtext default NULL,
988 `homebranch` varchar(10) default NULL,
989 `price` decimal(8,2) default NULL,
990 `replacementprice` decimal(8,2) default NULL,
991 `replacementpricedate` date default NULL,
992 `datelastborrowed` date default NULL,
993 `datelastseen` date default NULL,
994 `stack` tinyint(1) default NULL,
995 `notforloan` tinyint(1) NOT NULL default 0,
996 `damaged` tinyint(1) NOT NULL default 0,
997 `itemlost` tinyint(1) NOT NULL default 0,
998 `wthdrawn` tinyint(1) NOT NULL default 0,
999 `itemcallnumber` varchar(255) default NULL,
1000 `issues` smallint(6) default NULL,
1001 `renewals` smallint(6) default NULL,
1002 `reserves` smallint(6) default NULL,
1003 `restricted` tinyint(1) default NULL,
1004 `itemnotes` mediumtext,
1005 `holdingbranch` varchar(10) default NULL,
1006 `paidfor` mediumtext,
1007 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1008 `location` varchar(80) default NULL,
1009 `permanent_location` varchar(80) default NULL,
1010 `onloan` date default NULL,
1011 `cn_source` varchar(10) default NULL,
1012 `cn_sort` varchar(30) default NULL,
1013 `ccode` varchar(10) default NULL,
1014 `materials` varchar(10) default NULL,
1015 `uri` varchar(255) default NULL,
1016 `itype` varchar(10) default NULL,
1017 `more_subfields_xml` longtext default NULL,
1018 `enumchron` varchar(80) default NULL,
1019 `copynumber` varchar(32) default NULL,
1020 `stocknumber` varchar(32) default NULL,
1021 PRIMARY KEY (`itemnumber`),
1022 UNIQUE KEY `itembarcodeidx` (`barcode`),
1023 UNIQUE KEY `itemstocknumberidx` (`stocknumber`),
1024 KEY `itembinoidx` (`biblioitemnumber`),
1025 KEY `itembibnoidx` (`biblionumber`),
1026 KEY `homebranch` (`homebranch`),
1027 KEY `holdingbranch` (`holdingbranch`),
1028 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1029 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1030 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1031 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1034 -- Table structure for table `itemtypes`
1037 DROP TABLE IF EXISTS `itemtypes`;
1038 CREATE TABLE `itemtypes` (
1039 `itemtype` varchar(10) NOT NULL default '',
1040 `description` mediumtext,
1041 `rentalcharge` double(16,4) default NULL,
1042 `notforloan` smallint(6) default NULL,
1043 `imageurl` varchar(200) default NULL,
1045 PRIMARY KEY (`itemtype`),
1046 UNIQUE KEY `itemtype` (`itemtype`)
1047 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1050 -- Table structure for table `creator_batches`
1053 DROP TABLE IF EXISTS `creator_batches`;
1054 SET @saved_cs_client = @@character_set_client;
1055 SET character_set_client = utf8;
1056 CREATE TABLE `creator_batches` (
1057 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1058 `batch_id` int(10) NOT NULL DEFAULT '1',
1059 `item_number` int(11) DEFAULT NULL,
1060 `borrower_number` int(11) DEFAULT NULL,
1061 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1062 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1063 `creator` char(15) NOT NULL DEFAULT 'Labels',
1064 PRIMARY KEY (`label_id`),
1065 KEY `branch_fk_constraint` (`branch_code`),
1066 KEY `item_fk_constraint` (`item_number`),
1067 KEY `borrower_fk_constraint` (`borrower_number`),
1068 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1069 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1070 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1071 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1074 -- Table structure for table `creator_images`
1077 DROP TABLE IF EXISTS `creator_images`;
1078 SET @saved_cs_client = @@character_set_client;
1079 SET character_set_client = utf8;
1080 CREATE TABLE `creator_images` (
1081 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1082 `imagefile` mediumblob,
1083 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1084 PRIMARY KEY (`image_id`),
1085 UNIQUE KEY `image_name_index` (`image_name`)
1086 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1089 -- Table structure for table `creator_layouts`
1092 DROP TABLE IF EXISTS `creator_layouts`;
1093 SET @saved_cs_client = @@character_set_client;
1094 SET character_set_client = utf8;
1095 CREATE TABLE `creator_layouts` (
1096 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1097 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1098 `start_label` int(2) NOT NULL DEFAULT '1',
1099 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1100 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1101 `guidebox` int(1) DEFAULT '0',
1102 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1103 `font_size` int(4) NOT NULL DEFAULT '10',
1104 `units` char(20) NOT NULL DEFAULT 'POINT',
1105 `callnum_split` int(1) DEFAULT '0',
1106 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1107 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1108 `layout_xml` text NOT NULL,
1109 `creator` char(15) NOT NULL DEFAULT 'Labels',
1110 PRIMARY KEY (`layout_id`)
1111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1114 -- Table structure for table `creator_templates`
1117 DROP TABLE IF EXISTS `creator_templates`;
1118 SET @saved_cs_client = @@character_set_client;
1119 SET character_set_client = utf8;
1120 CREATE TABLE `creator_templates` (
1121 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1122 `profile_id` int(4) DEFAULT NULL,
1123 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1124 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1125 `page_width` float NOT NULL DEFAULT '0',
1126 `page_height` float NOT NULL DEFAULT '0',
1127 `label_width` float NOT NULL DEFAULT '0',
1128 `label_height` float NOT NULL DEFAULT '0',
1129 `top_text_margin` float NOT NULL DEFAULT '0',
1130 `left_text_margin` float NOT NULL DEFAULT '0',
1131 `top_margin` float NOT NULL DEFAULT '0',
1132 `left_margin` float NOT NULL DEFAULT '0',
1133 `cols` int(2) NOT NULL DEFAULT '0',
1134 `rows` int(2) NOT NULL DEFAULT '0',
1135 `col_gap` float NOT NULL DEFAULT '0',
1136 `row_gap` float NOT NULL DEFAULT '0',
1137 `units` char(20) NOT NULL DEFAULT 'POINT',
1138 `creator` char(15) NOT NULL DEFAULT 'Labels',
1139 PRIMARY KEY (`template_id`),
1140 KEY `template_profile_fk_constraint` (`profile_id`)
1141 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1144 -- Table structure for table `letter`
1147 DROP TABLE IF EXISTS `letter`;
1148 CREATE TABLE `letter` (
1149 `module` varchar(20) NOT NULL default '',
1150 `code` varchar(20) NOT NULL default '',
1151 `name` varchar(100) NOT NULL default '',
1152 `title` varchar(200) NOT NULL default '',
1154 PRIMARY KEY (`module`,`code`)
1155 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1158 -- Table structure for table `marc_subfield_structure`
1161 DROP TABLE IF EXISTS `marc_subfield_structure`;
1162 CREATE TABLE `marc_subfield_structure` (
1163 `tagfield` varchar(3) NOT NULL default '',
1164 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1165 `liblibrarian` varchar(255) NOT NULL default '',
1166 `libopac` varchar(255) NOT NULL default '',
1167 `repeatable` tinyint(4) NOT NULL default 0,
1168 `mandatory` tinyint(4) NOT NULL default 0,
1169 `kohafield` varchar(40) default NULL,
1170 `tab` tinyint(1) default NULL,
1171 `authorised_value` varchar(20) default NULL,
1172 `authtypecode` varchar(20) default NULL,
1173 `value_builder` varchar(80) default NULL,
1174 `isurl` tinyint(1) default NULL,
1175 `hidden` tinyint(1) default NULL,
1176 `frameworkcode` varchar(4) NOT NULL default '',
1177 `seealso` varchar(1100) default NULL,
1178 `link` varchar(80) default NULL,
1179 `defaultvalue` text default NULL,
1180 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1181 KEY `kohafield_2` (`kohafield`),
1182 KEY `tab` (`frameworkcode`,`tab`),
1183 KEY `kohafield` (`frameworkcode`,`kohafield`)
1184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1187 -- Table structure for table `marc_tag_structure`
1190 DROP TABLE IF EXISTS `marc_tag_structure`;
1191 CREATE TABLE `marc_tag_structure` (
1192 `tagfield` varchar(3) NOT NULL default '',
1193 `liblibrarian` varchar(255) NOT NULL default '',
1194 `libopac` varchar(255) NOT NULL default '',
1195 `repeatable` tinyint(4) NOT NULL default 0,
1196 `mandatory` tinyint(4) NOT NULL default 0,
1197 `authorised_value` varchar(10) default NULL,
1198 `frameworkcode` varchar(4) NOT NULL default '',
1199 PRIMARY KEY (`frameworkcode`,`tagfield`)
1200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1203 -- Table structure for table `marc_matchers`
1206 DROP TABLE IF EXISTS `marc_matchers`;
1207 CREATE TABLE `marc_matchers` (
1208 `matcher_id` int(11) NOT NULL auto_increment,
1209 `code` varchar(10) NOT NULL default '',
1210 `description` varchar(255) NOT NULL default '',
1211 `record_type` varchar(10) NOT NULL default 'biblio',
1212 `threshold` int(11) NOT NULL default 0,
1213 PRIMARY KEY (`matcher_id`),
1214 KEY `code` (`code`),
1215 KEY `record_type` (`record_type`)
1216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1219 -- Table structure for table `matchpoints`
1221 DROP TABLE IF EXISTS `matchpoints`;
1222 CREATE TABLE `matchpoints` (
1223 `matcher_id` int(11) NOT NULL,
1224 `matchpoint_id` int(11) NOT NULL auto_increment,
1225 `search_index` varchar(30) NOT NULL default '',
1226 `score` int(11) NOT NULL default 0,
1227 PRIMARY KEY (`matchpoint_id`),
1228 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1229 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1234 -- Table structure for table `matchpoint_components`
1236 DROP TABLE IF EXISTS `matchpoint_components`;
1237 CREATE TABLE `matchpoint_components` (
1238 `matchpoint_id` int(11) NOT NULL,
1239 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1240 sequence int(11) NOT NULL default 0,
1241 tag varchar(3) NOT NULL default '',
1242 subfields varchar(40) NOT NULL default '',
1243 offset int(4) NOT NULL default 0,
1244 length int(4) NOT NULL default 0,
1245 PRIMARY KEY (`matchpoint_component_id`),
1246 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1247 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1248 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1249 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1252 -- Table structure for table `matcher_component_norms`
1254 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1255 CREATE TABLE `matchpoint_component_norms` (
1256 `matchpoint_component_id` int(11) NOT NULL,
1257 `sequence` int(11) NOT NULL default 0,
1258 `norm_routine` varchar(50) NOT NULL default '',
1259 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1260 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1261 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1262 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1265 -- Table structure for table `matcher_matchpoints`
1267 DROP TABLE IF EXISTS `matcher_matchpoints`;
1268 CREATE TABLE `matcher_matchpoints` (
1269 `matcher_id` int(11) NOT NULL,
1270 `matchpoint_id` int(11) NOT NULL,
1271 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1272 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1273 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1274 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1275 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1278 -- Table structure for table `matchchecks`
1280 DROP TABLE IF EXISTS `matchchecks`;
1281 CREATE TABLE `matchchecks` (
1282 `matcher_id` int(11) NOT NULL,
1283 `matchcheck_id` int(11) NOT NULL auto_increment,
1284 `source_matchpoint_id` int(11) NOT NULL,
1285 `target_matchpoint_id` int(11) NOT NULL,
1286 PRIMARY KEY (`matchcheck_id`),
1287 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1288 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1289 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1290 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1291 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1292 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1293 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1296 -- Table structure for table `notifys`
1299 DROP TABLE IF EXISTS `notifys`;
1300 CREATE TABLE `notifys` (
1301 `notify_id` int(11) NOT NULL default 0,
1302 `borrowernumber` int(11) NOT NULL default 0,
1303 `itemnumber` int(11) NOT NULL default 0,
1304 `notify_date` date default NULL,
1305 `notify_send_date` date default NULL,
1306 `notify_level` int(1) NOT NULL default 0,
1307 `method` varchar(20) NOT NULL default ''
1308 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1311 -- Table structure for table `nozebra`
1314 DROP TABLE IF EXISTS `nozebra`;
1315 CREATE TABLE `nozebra` (
1316 `server` varchar(20) NOT NULL,
1317 `indexname` varchar(40) NOT NULL,
1318 `value` varchar(250) NOT NULL,
1319 `biblionumbers` longtext NOT NULL,
1320 KEY `indexname` (`server`,`indexname`),
1321 KEY `value` (`server`,`value`))
1322 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1325 -- Table structure for table `old_issues`
1328 DROP TABLE IF EXISTS `old_issues`;
1329 CREATE TABLE `old_issues` (
1330 `borrowernumber` int(11) default NULL,
1331 `itemnumber` int(11) default NULL,
1332 `date_due` date default NULL,
1333 `branchcode` varchar(10) default NULL,
1334 `issuingbranch` varchar(18) default NULL,
1335 `returndate` date default NULL,
1336 `lastreneweddate` date default NULL,
1337 `return` varchar(4) default NULL,
1338 `renewals` tinyint(4) default NULL,
1339 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1340 `issuedate` date default NULL,
1341 KEY `old_issuesborridx` (`borrowernumber`),
1342 KEY `old_issuesitemidx` (`itemnumber`),
1343 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1344 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1345 ON DELETE SET NULL ON UPDATE SET NULL,
1346 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1347 ON DELETE SET NULL ON UPDATE SET NULL
1348 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1351 -- Table structure for table `old_reserves`
1353 DROP TABLE IF EXISTS `old_reserves`;
1354 CREATE TABLE `old_reserves` (
1355 `borrowernumber` int(11) default NULL,
1356 `reservedate` date default NULL,
1357 `biblionumber` int(11) default NULL,
1358 `constrainttype` varchar(1) default NULL,
1359 `branchcode` varchar(10) default NULL,
1360 `notificationdate` date default NULL,
1361 `reminderdate` date default NULL,
1362 `cancellationdate` date default NULL,
1363 `reservenotes` mediumtext,
1364 `priority` smallint(6) default NULL,
1365 `found` varchar(1) default NULL,
1366 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1367 `itemnumber` int(11) default NULL,
1368 `waitingdate` date default NULL,
1369 `expirationdate` DATE DEFAULT NULL,
1370 `lowestPriority` tinyint(1) NOT NULL,
1371 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1372 KEY `old_reserves_biblionumber` (`biblionumber`),
1373 KEY `old_reserves_itemnumber` (`itemnumber`),
1374 KEY `old_reserves_branchcode` (`branchcode`),
1375 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1376 ON DELETE SET NULL ON UPDATE SET NULL,
1377 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1378 ON DELETE SET NULL ON UPDATE SET NULL,
1379 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1380 ON DELETE SET NULL ON UPDATE SET NULL
1381 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1384 -- Table structure for table `opac_news`
1387 DROP TABLE IF EXISTS `opac_news`;
1388 CREATE TABLE `opac_news` (
1389 `idnew` int(10) unsigned NOT NULL auto_increment,
1390 `title` varchar(250) NOT NULL default '',
1391 `new` text NOT NULL,
1392 `lang` varchar(25) NOT NULL default '',
1393 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1394 `expirationdate` date default NULL,
1395 `number` int(11) default NULL,
1396 PRIMARY KEY (`idnew`)
1397 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1400 -- Table structure for table `overduerules`
1403 DROP TABLE IF EXISTS `overduerules`;
1404 CREATE TABLE `overduerules` (
1405 `branchcode` varchar(10) NOT NULL default '',
1406 `categorycode` varchar(10) NOT NULL default '',
1407 `delay1` int(4) default 0,
1408 `letter1` varchar(20) default NULL,
1409 `debarred1` varchar(1) default 0,
1410 `delay2` int(4) default 0,
1411 `debarred2` varchar(1) default 0,
1412 `letter2` varchar(20) default NULL,
1413 `delay3` int(4) default 0,
1414 `letter3` varchar(20) default NULL,
1415 `debarred3` int(1) default 0,
1416 PRIMARY KEY (`branchcode`,`categorycode`)
1417 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1420 -- Table structure for table `patroncards`
1423 DROP TABLE IF EXISTS `patroncards`;
1424 CREATE TABLE `patroncards` (
1425 `cardid` int(11) NOT NULL auto_increment,
1426 `batch_id` varchar(10) NOT NULL default '1',
1427 `borrowernumber` int(11) NOT NULL,
1428 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1429 PRIMARY KEY (`cardid`),
1430 KEY `patroncards_ibfk_1` (`borrowernumber`),
1431 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1432 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1435 -- Table structure for table `patronimage`
1438 DROP TABLE IF EXISTS `patronimage`;
1439 CREATE TABLE `patronimage` (
1440 `cardnumber` varchar(16) NOT NULL,
1441 `mimetype` varchar(15) NOT NULL,
1442 `imagefile` mediumblob NOT NULL,
1443 PRIMARY KEY (`cardnumber`),
1444 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1445 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1448 -- Table structure for table `printers`
1451 DROP TABLE IF EXISTS `printers`;
1452 CREATE TABLE `printers` (
1453 `printername` varchar(40) NOT NULL default '',
1454 `printqueue` varchar(20) default NULL,
1455 `printtype` varchar(20) default NULL,
1456 PRIMARY KEY (`printername`)
1457 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1460 -- Table structure for table `printers_profile`
1463 DROP TABLE IF EXISTS `printers_profile`;
1464 CREATE TABLE `printers_profile` (
1465 `profile_id` int(4) NOT NULL auto_increment,
1466 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1467 `template_id` int(4) NOT NULL default '0',
1468 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1469 `offset_horz` float NOT NULL default '0',
1470 `offset_vert` float NOT NULL default '0',
1471 `creep_horz` float NOT NULL default '0',
1472 `creep_vert` float NOT NULL default '0',
1473 `units` char(20) NOT NULL default 'POINT',
1474 `creator` char(15) NOT NULL DEFAULT 'Labels',
1475 PRIMARY KEY (`profile_id`),
1476 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
1477 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1480 -- Table structure for table `repeatable_holidays`
1483 DROP TABLE IF EXISTS `repeatable_holidays`;
1484 CREATE TABLE `repeatable_holidays` (
1485 `id` int(11) NOT NULL auto_increment,
1486 `branchcode` varchar(10) NOT NULL default '',
1487 `weekday` smallint(6) default NULL,
1488 `day` smallint(6) default NULL,
1489 `month` smallint(6) default NULL,
1490 `title` varchar(50) NOT NULL default '',
1491 `description` text NOT NULL,
1493 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1496 -- Table structure for table `reports_dictionary`
1499 DROP TABLE IF EXISTS `reports_dictionary`;
1500 CREATE TABLE reports_dictionary (
1501 `id` int(11) NOT NULL auto_increment,
1502 `name` varchar(255) default NULL,
1504 `date_created` datetime default NULL,
1505 `date_modified` datetime default NULL,
1507 `area` int(11) default NULL,
1509 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1512 -- Table structure for table `reserveconstraints`
1515 DROP TABLE IF EXISTS `reserveconstraints`;
1516 CREATE TABLE `reserveconstraints` (
1517 `borrowernumber` int(11) NOT NULL default 0,
1518 `reservedate` date default NULL,
1519 `biblionumber` int(11) NOT NULL default 0,
1520 `biblioitemnumber` int(11) default NULL,
1521 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1522 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1525 -- Table structure for table `reserves`
1528 DROP TABLE IF EXISTS `reserves`;
1529 CREATE TABLE `reserves` (
1530 `borrowernumber` int(11) NOT NULL default 0,
1531 `reservedate` date default NULL,
1532 `biblionumber` int(11) NOT NULL default 0,
1533 `constrainttype` varchar(1) default NULL,
1534 `branchcode` varchar(10) default NULL,
1535 `notificationdate` date default NULL,
1536 `reminderdate` date default NULL,
1537 `cancellationdate` date default NULL,
1538 `reservenotes` mediumtext,
1539 `priority` smallint(6) default NULL,
1540 `found` varchar(1) default NULL,
1541 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1542 `itemnumber` int(11) default NULL,
1543 `waitingdate` date default NULL,
1544 `expirationdate` DATE DEFAULT NULL,
1545 `lowestPriority` tinyint(1) NOT NULL,
1546 KEY `borrowernumber` (`borrowernumber`),
1547 KEY `biblionumber` (`biblionumber`),
1548 KEY `itemnumber` (`itemnumber`),
1549 KEY `branchcode` (`branchcode`),
1550 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1551 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1552 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1553 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1554 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1557 -- Table structure for table `reviews`
1560 DROP TABLE IF EXISTS `reviews`;
1561 CREATE TABLE `reviews` (
1562 `reviewid` int(11) NOT NULL auto_increment,
1563 `borrowernumber` int(11) default NULL,
1564 `biblionumber` int(11) default NULL,
1566 `approved` tinyint(4) default NULL,
1567 `datereviewed` datetime default NULL,
1568 PRIMARY KEY (`reviewid`)
1569 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1572 -- Table structure for table `roadtype`
1575 DROP TABLE IF EXISTS `roadtype`;
1576 CREATE TABLE `roadtype` (
1577 `roadtypeid` int(11) NOT NULL auto_increment,
1578 `road_type` varchar(100) NOT NULL default '',
1579 PRIMARY KEY (`roadtypeid`)
1580 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1583 -- Table structure for table `saved_sql`
1586 DROP TABLE IF EXISTS `saved_sql`;
1587 CREATE TABLE saved_sql (
1588 `id` int(11) NOT NULL auto_increment,
1589 `borrowernumber` int(11) default NULL,
1590 `date_created` datetime default NULL,
1591 `last_modified` datetime default NULL,
1593 `last_run` datetime default NULL,
1594 `report_name` varchar(255) default NULL,
1595 `type` varchar(255) default NULL,
1598 KEY boridx (`borrowernumber`)
1599 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1603 -- Table structure for `saved_reports`
1606 DROP TABLE IF EXISTS `saved_reports`;
1607 CREATE TABLE saved_reports (
1608 `id` int(11) NOT NULL auto_increment,
1609 `report_id` int(11) default NULL,
1611 `date_run` datetime default NULL,
1613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1617 -- Table structure for table `search_history`
1620 DROP TABLE IF EXISTS `search_history`;
1621 CREATE TABLE IF NOT EXISTS `search_history` (
1622 `userid` int(11) NOT NULL,
1623 `sessionid` varchar(32) NOT NULL,
1624 `query_desc` varchar(255) NOT NULL,
1625 `query_cgi` varchar(255) NOT NULL,
1626 `total` int(11) NOT NULL,
1627 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1628 KEY `userid` (`userid`),
1629 KEY `sessionid` (`sessionid`)
1630 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1634 -- Table structure for table `serial`
1637 DROP TABLE IF EXISTS `serial`;
1638 CREATE TABLE `serial` (
1639 `serialid` int(11) NOT NULL auto_increment,
1640 `biblionumber` varchar(100) NOT NULL default '',
1641 `subscriptionid` varchar(100) NOT NULL default '',
1642 `serialseq` varchar(100) NOT NULL default '',
1643 `status` tinyint(4) NOT NULL default 0,
1644 `planneddate` date default NULL,
1646 `publisheddate` date default NULL,
1647 `itemnumber` text default NULL,
1648 `claimdate` date default NULL,
1649 `routingnotes` text,
1650 PRIMARY KEY (`serialid`)
1651 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1654 -- Table structure for table `sessions`
1657 DROP TABLE IF EXISTS sessions;
1658 CREATE TABLE sessions (
1659 `id` varchar(32) NOT NULL,
1660 `a_session` text NOT NULL,
1662 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1665 -- Table structure for table `special_holidays`
1668 DROP TABLE IF EXISTS `special_holidays`;
1669 CREATE TABLE `special_holidays` (
1670 `id` int(11) NOT NULL auto_increment,
1671 `branchcode` varchar(10) NOT NULL default '',
1672 `day` smallint(6) NOT NULL default 0,
1673 `month` smallint(6) NOT NULL default 0,
1674 `year` smallint(6) NOT NULL default 0,
1675 `isexception` smallint(1) NOT NULL default 1,
1676 `title` varchar(50) NOT NULL default '',
1677 `description` text NOT NULL,
1679 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1682 -- Table structure for table `statistics`
1685 DROP TABLE IF EXISTS `statistics`;
1686 CREATE TABLE `statistics` (
1687 `datetime` datetime default NULL,
1688 `branch` varchar(10) default NULL,
1689 `proccode` varchar(4) default NULL,
1690 `value` double(16,4) default NULL,
1691 `type` varchar(16) default NULL,
1693 `usercode` varchar(10) default NULL,
1694 `itemnumber` int(11) default NULL,
1695 `itemtype` varchar(10) default NULL,
1696 `borrowernumber` int(11) default NULL,
1697 `associatedborrower` int(11) default NULL,
1698 KEY `timeidx` (`datetime`)
1699 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1702 -- Table structure for table `stopwords`
1705 DROP TABLE IF EXISTS `stopwords`;
1706 CREATE TABLE `stopwords` (
1707 `word` varchar(255) default NULL
1708 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1711 -- Table structure for table `subscription`
1714 DROP TABLE IF EXISTS `subscription`;
1715 CREATE TABLE `subscription` (
1716 `biblionumber` int(11) NOT NULL default 0,
1717 `subscriptionid` int(11) NOT NULL auto_increment,
1718 `librarian` varchar(100) default '',
1719 `startdate` date default NULL,
1720 `aqbooksellerid` int(11) default 0,
1721 `cost` int(11) default 0,
1722 `aqbudgetid` int(11) default 0,
1723 `weeklength` int(11) default 0,
1724 `monthlength` int(11) default 0,
1725 `numberlength` int(11) default 0,
1726 `periodicity` tinyint(4) default 0,
1727 `dow` varchar(100) default '',
1728 `numberingmethod` varchar(100) default '',
1730 `status` varchar(100) NOT NULL default '',
1731 `add1` int(11) default 0,
1732 `every1` int(11) default 0,
1733 `whenmorethan1` int(11) default 0,
1734 `setto1` int(11) default NULL,
1735 `lastvalue1` int(11) default NULL,
1736 `add2` int(11) default 0,
1737 `every2` int(11) default 0,
1738 `whenmorethan2` int(11) default 0,
1739 `setto2` int(11) default NULL,
1740 `lastvalue2` int(11) default NULL,
1741 `add3` int(11) default 0,
1742 `every3` int(11) default 0,
1743 `innerloop1` int(11) default 0,
1744 `innerloop2` int(11) default 0,
1745 `innerloop3` int(11) default 0,
1746 `whenmorethan3` int(11) default 0,
1747 `setto3` int(11) default NULL,
1748 `lastvalue3` int(11) default NULL,
1749 `issuesatonce` tinyint(3) NOT NULL default 1,
1750 `firstacquidate` date default NULL,
1751 `manualhistory` tinyint(1) NOT NULL default 0,
1752 `irregularity` text,
1753 `letter` varchar(20) default NULL,
1754 `numberpattern` tinyint(3) default 0,
1755 `distributedto` text,
1756 `internalnotes` longtext,
1758 `location` varchar(80) NULL default '',
1759 `branchcode` varchar(10) NOT NULL default '',
1760 `hemisphere` tinyint(3) default 0,
1761 `lastbranch` varchar(10),
1762 `serialsadditems` tinyint(1) NOT NULL default '0',
1763 `staffdisplaycount` VARCHAR(10) NULL,
1764 `opacdisplaycount` VARCHAR(10) NULL,
1765 `graceperiod` int(11) NOT NULL default '0',
1766 `enddate` date default NULL,
1767 PRIMARY KEY (`subscriptionid`)
1768 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1771 -- Table structure for table `subscriptionhistory`
1774 DROP TABLE IF EXISTS `subscriptionhistory`;
1775 CREATE TABLE `subscriptionhistory` (
1776 `biblionumber` int(11) NOT NULL default 0,
1777 `subscriptionid` int(11) NOT NULL default 0,
1778 `histstartdate` date default NULL,
1779 `histenddate` date default NULL,
1780 `missinglist` longtext NOT NULL,
1781 `recievedlist` longtext NOT NULL,
1782 `opacnote` varchar(150) NOT NULL default '',
1783 `librariannote` varchar(150) NOT NULL default '',
1784 PRIMARY KEY (`subscriptionid`),
1785 KEY `biblionumber` (`biblionumber`)
1786 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1789 -- Table structure for table `subscriptionroutinglist`
1792 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1793 CREATE TABLE `subscriptionroutinglist` (
1794 `routingid` int(11) NOT NULL auto_increment,
1795 `borrowernumber` int(11) NOT NULL,
1796 `ranking` int(11) default NULL,
1797 `subscriptionid` int(11) NOT NULL,
1798 PRIMARY KEY (`routingid`),
1799 UNIQUE (`subscriptionid`, `borrowernumber`),
1800 CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1801 ON DELETE CASCADE ON UPDATE CASCADE,
1802 CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
1803 ON DELETE CASCADE ON UPDATE CASCADE
1804 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1807 -- Table structure for table `suggestions`
1810 DROP TABLE IF EXISTS `suggestions`;
1811 CREATE TABLE `suggestions` (
1812 `suggestionid` int(8) NOT NULL auto_increment,
1813 `suggestedby` int(11) NOT NULL default 0,
1814 `suggesteddate` date NOT NULL default 0,
1815 `managedby` int(11) default NULL,
1816 `manageddate` date default NULL,
1817 acceptedby INT(11) default NULL,
1818 accepteddate date default NULL,
1819 rejectedby INT(11) default NULL,
1820 rejecteddate date default NULL,
1821 `STATUS` varchar(10) NOT NULL default '',
1823 `author` varchar(80) default NULL,
1824 `title` varchar(80) default NULL,
1825 `copyrightdate` smallint(6) default NULL,
1826 `publishercode` varchar(255) default NULL,
1827 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1828 `volumedesc` varchar(255) default NULL,
1829 `publicationyear` smallint(6) default 0,
1830 `place` varchar(255) default NULL,
1831 `isbn` varchar(30) default NULL,
1832 `mailoverseeing` smallint(1) default 0,
1833 `biblionumber` int(11) default NULL,
1836 branchcode VARCHAR(10) default NULL,
1837 collectiontitle text default NULL,
1838 itemtype VARCHAR(30) default NULL,
1839 quantity SMALLINT(6) default NULL,
1840 currency VARCHAR(3) default NULL,
1841 price DECIMAL(28,6) default NULL,
1842 total DECIMAL(28,6) default NULL,
1843 PRIMARY KEY (`suggestionid`),
1844 KEY `suggestedby` (`suggestedby`),
1845 KEY `managedby` (`managedby`)
1846 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1849 -- Table structure for table `systempreferences`
1852 DROP TABLE IF EXISTS `systempreferences`;
1853 CREATE TABLE `systempreferences` (
1854 `variable` varchar(50) NOT NULL default '',
1856 `options` mediumtext,
1858 `type` varchar(20) default NULL,
1859 PRIMARY KEY (`variable`)
1860 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1863 -- Table structure for table `tags`
1866 DROP TABLE IF EXISTS `tags`;
1867 CREATE TABLE `tags` (
1868 `entry` varchar(255) NOT NULL default '',
1869 `weight` bigint(20) NOT NULL default 0,
1870 PRIMARY KEY (`entry`)
1871 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1874 -- Table structure for table `tags_all`
1877 DROP TABLE IF EXISTS `tags_all`;
1878 CREATE TABLE `tags_all` (
1879 `tag_id` int(11) NOT NULL auto_increment,
1880 `borrowernumber` int(11) NOT NULL,
1881 `biblionumber` int(11) NOT NULL,
1882 `term` varchar(255) NOT NULL,
1883 `language` int(4) default NULL,
1884 `date_created` datetime NOT NULL,
1885 PRIMARY KEY (`tag_id`),
1886 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1887 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1888 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1889 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1890 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1891 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1892 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1895 -- Table structure for table `tags_approval`
1898 DROP TABLE IF EXISTS `tags_approval`;
1899 CREATE TABLE `tags_approval` (
1900 `term` varchar(255) NOT NULL,
1901 `approved` int(1) NOT NULL default '0',
1902 `date_approved` datetime default NULL,
1903 `approved_by` int(11) default NULL,
1904 `weight_total` int(9) NOT NULL default '1',
1905 PRIMARY KEY (`term`),
1906 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1907 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1908 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1909 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1912 -- Table structure for table `tags_index`
1915 DROP TABLE IF EXISTS `tags_index`;
1916 CREATE TABLE `tags_index` (
1917 `term` varchar(255) NOT NULL,
1918 `biblionumber` int(11) NOT NULL,
1919 `weight` int(9) NOT NULL default '1',
1920 PRIMARY KEY (`term`,`biblionumber`),
1921 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1922 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1923 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1924 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1925 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1926 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1929 -- Table structure for table `userflags`
1932 DROP TABLE IF EXISTS `userflags`;
1933 CREATE TABLE `userflags` (
1934 `bit` int(11) NOT NULL default 0,
1935 `flag` varchar(30) default NULL,
1936 `flagdesc` varchar(255) default NULL,
1937 `defaulton` int(11) default NULL,
1939 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1942 -- Table structure for table `virtualshelves`
1945 DROP TABLE IF EXISTS `virtualshelves`;
1946 CREATE TABLE `virtualshelves` (
1947 `shelfnumber` int(11) NOT NULL auto_increment,
1948 `shelfname` varchar(255) default NULL,
1949 `owner` varchar(80) default NULL,
1950 `category` varchar(1) default NULL,
1951 `sortfield` varchar(16) default NULL,
1952 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1953 PRIMARY KEY (`shelfnumber`)
1954 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1957 -- Table structure for table `virtualshelfcontents`
1960 DROP TABLE IF EXISTS `virtualshelfcontents`;
1961 CREATE TABLE `virtualshelfcontents` (
1962 `shelfnumber` int(11) NOT NULL default 0,
1963 `biblionumber` int(11) NOT NULL default 0,
1964 `flags` int(11) default NULL,
1965 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1966 KEY `shelfnumber` (`shelfnumber`),
1967 KEY `biblionumber` (`biblionumber`),
1968 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1969 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1970 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1973 -- Table structure for table `z3950servers`
1976 DROP TABLE IF EXISTS `z3950servers`;
1977 CREATE TABLE `z3950servers` (
1978 `host` varchar(255) default NULL,
1979 `port` int(11) default NULL,
1980 `db` varchar(255) default NULL,
1981 `userid` varchar(255) default NULL,
1982 `password` varchar(255) default NULL,
1984 `id` int(11) NOT NULL auto_increment,
1985 `checked` smallint(6) default NULL,
1986 `rank` int(11) default NULL,
1987 `syntax` varchar(80) default NULL,
1989 `position` enum('primary','secondary','') NOT NULL default 'primary',
1990 `type` enum('zed','opensearch') NOT NULL default 'zed',
1991 `encoding` text default NULL,
1992 `description` text NOT NULL,
1994 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1997 -- Table structure for table `zebraqueue`
2000 DROP TABLE IF EXISTS `zebraqueue`;
2001 CREATE TABLE `zebraqueue` (
2002 `id` int(11) NOT NULL auto_increment,
2003 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2004 `operation` char(20) NOT NULL default '',
2005 `server` char(20) NOT NULL default '',
2006 `done` int(11) NOT NULL default '0',
2007 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2009 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2010 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2012 DROP TABLE IF EXISTS `services_throttle`;
2013 CREATE TABLE `services_throttle` (
2014 `service_type` varchar(10) NOT NULL default '',
2015 `service_count` varchar(45) default NULL,
2016 PRIMARY KEY (`service_type`)
2017 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2019 -- http://www.w3.org/International/articles/language-tags/
2022 DROP TABLE IF EXISTS language_subtag_registry;
2023 CREATE TABLE language_subtag_registry (
2025 type varchar(25), -- language-script-region-variant-extension-privateuse
2026 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2028 id int(11) NOT NULL auto_increment,
2030 KEY `subtag` (`subtag`)
2031 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2033 -- TODO: add suppress_scripts
2034 -- this maps three letter codes defined in iso639.2 back to their
2035 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2036 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2037 CREATE TABLE language_rfc4646_to_iso639 (
2038 rfc4646_subtag varchar(25),
2039 iso639_2_code varchar(25),
2040 id int(11) NOT NULL auto_increment,
2042 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2043 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2045 DROP TABLE IF EXISTS language_descriptions;
2046 CREATE TABLE language_descriptions (
2050 description varchar(255),
2051 id int(11) NOT NULL auto_increment,
2053 KEY `lang` (`lang`),
2054 KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
2055 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2057 -- bi-directional support, keyed by script subcode
2058 DROP TABLE IF EXISTS language_script_bidi;
2059 CREATE TABLE language_script_bidi (
2060 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2061 bidi varchar(3), -- rtl ltr
2062 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2063 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2065 -- TODO: need to map language subtags to script subtags for detection
2066 -- of bidi when script is not specified (like ar, he)
2067 DROP TABLE IF EXISTS language_script_mapping;
2068 CREATE TABLE language_script_mapping (
2069 language_subtag varchar(25),
2070 script_subtag varchar(25),
2071 KEY `language_subtag` (`language_subtag`)
2072 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2074 DROP TABLE IF EXISTS `permissions`;
2075 CREATE TABLE `permissions` (
2076 `module_bit` int(11) NOT NULL DEFAULT 0,
2077 `code` varchar(64) DEFAULT NULL,
2078 `description` varchar(255) DEFAULT NULL,
2079 PRIMARY KEY (`module_bit`, `code`),
2080 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2081 ON DELETE CASCADE ON UPDATE CASCADE
2082 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2084 DROP TABLE IF EXISTS `serialitems`;
2085 CREATE TABLE `serialitems` (
2086 `itemnumber` int(11) NOT NULL,
2087 `serialid` int(11) NOT NULL,
2088 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2089 KEY `serialitems_sfk_1` (`serialid`),
2090 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
2091 CONSTRAINT serialitems_sfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
2092 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2094 DROP TABLE IF EXISTS `user_permissions`;
2095 CREATE TABLE `user_permissions` (
2096 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2097 `module_bit` int(11) NOT NULL DEFAULT 0,
2098 `code` varchar(64) DEFAULT NULL,
2099 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2100 ON DELETE CASCADE ON UPDATE CASCADE,
2101 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2102 ON DELETE CASCADE ON UPDATE CASCADE
2103 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2106 -- Table structure for table `tmp_holdsqueue`
2109 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2110 CREATE TABLE `tmp_holdsqueue` (
2111 `biblionumber` int(11) default NULL,
2112 `itemnumber` int(11) default NULL,
2113 `barcode` varchar(20) default NULL,
2114 `surname` mediumtext NOT NULL,
2117 `borrowernumber` int(11) NOT NULL,
2118 `cardnumber` varchar(16) default NULL,
2119 `reservedate` date default NULL,
2121 `itemcallnumber` varchar(255) default NULL,
2122 `holdingbranch` varchar(10) default NULL,
2123 `pickbranch` varchar(10) default NULL,
2125 `item_level_request` tinyint(4) NOT NULL default 0
2126 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2129 -- Table structure for table `message_queue`
2132 DROP TABLE IF EXISTS `message_queue`;
2133 CREATE TABLE `message_queue` (
2134 `message_id` int(11) NOT NULL auto_increment,
2135 `borrowernumber` int(11) default NULL,
2138 `metadata` text DEFAULT NULL,
2139 `letter_code` varchar(64) DEFAULT NULL,
2140 `message_transport_type` varchar(20) NOT NULL,
2141 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2142 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2143 `to_address` mediumtext,
2144 `from_address` mediumtext,
2145 `content_type` text,
2146 KEY `message_id` (`message_id`),
2147 KEY `borrowernumber` (`borrowernumber`),
2148 KEY `message_transport_type` (`message_transport_type`),
2149 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2150 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2154 -- Table structure for table `message_transport_types`
2157 DROP TABLE IF EXISTS `message_transport_types`;
2158 CREATE TABLE `message_transport_types` (
2159 `message_transport_type` varchar(20) NOT NULL,
2160 PRIMARY KEY (`message_transport_type`)
2161 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2164 -- Table structure for table `message_attributes`
2167 DROP TABLE IF EXISTS `message_attributes`;
2168 CREATE TABLE `message_attributes` (
2169 `message_attribute_id` int(11) NOT NULL auto_increment,
2170 `message_name` varchar(40) NOT NULL default '',
2171 `takes_days` tinyint(1) NOT NULL default '0',
2172 PRIMARY KEY (`message_attribute_id`),
2173 UNIQUE KEY `message_name` (`message_name`)
2174 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2177 -- Table structure for table `message_transports`
2180 DROP TABLE IF EXISTS `message_transports`;
2181 CREATE TABLE `message_transports` (
2182 `message_attribute_id` int(11) NOT NULL,
2183 `message_transport_type` varchar(20) NOT NULL,
2184 `is_digest` tinyint(1) NOT NULL default '0',
2185 `letter_module` varchar(20) NOT NULL default '',
2186 `letter_code` varchar(20) NOT NULL default '',
2187 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2188 KEY `message_transport_type` (`message_transport_type`),
2189 KEY `letter_module` (`letter_module`,`letter_code`),
2190 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2191 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2192 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2193 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2196 -- Table structure for table `borrower_message_preferences`
2199 DROP TABLE IF EXISTS `borrower_message_preferences`;
2200 CREATE TABLE `borrower_message_preferences` (
2201 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2202 `borrowernumber` int(11) default NULL,
2203 `categorycode` varchar(10) default NULL,
2204 `message_attribute_id` int(11) default '0',
2205 `days_in_advance` int(11) default '0',
2206 `wants_digest` tinyint(1) NOT NULL default '0',
2207 PRIMARY KEY (`borrower_message_preference_id`),
2208 KEY `borrowernumber` (`borrowernumber`),
2209 KEY `categorycode` (`categorycode`),
2210 KEY `message_attribute_id` (`message_attribute_id`),
2211 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2212 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2213 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2214 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2217 -- Table structure for table `borrower_message_transport_preferences`
2220 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2221 CREATE TABLE `borrower_message_transport_preferences` (
2222 `borrower_message_preference_id` int(11) NOT NULL default '0',
2223 `message_transport_type` varchar(20) NOT NULL default '0',
2224 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2225 KEY `message_transport_type` (`message_transport_type`),
2226 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,
2227 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
2228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2231 -- Table structure for the table branch_transfer_limits
2234 DROP TABLE IF EXISTS `branch_transfer_limits`;
2235 CREATE TABLE branch_transfer_limits (
2236 limitId int(8) NOT NULL auto_increment,
2237 toBranch varchar(10) NOT NULL,
2238 fromBranch varchar(10) NOT NULL,
2239 itemtype varchar(10) NULL,
2240 ccode varchar(10) NULL,
2241 PRIMARY KEY (limitId)
2242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2245 -- Table structure for table `item_circulation_alert_preferences`
2248 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2249 CREATE TABLE `item_circulation_alert_preferences` (
2250 `id` int(11) NOT NULL auto_increment,
2251 `branchcode` varchar(10) NOT NULL,
2252 `categorycode` varchar(10) NOT NULL,
2253 `item_type` varchar(10) NOT NULL,
2254 `notification` varchar(16) NOT NULL,
2256 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2257 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2260 -- Table structure for table `messages`
2262 DROP TABLE IF EXISTS `messages`;
2263 CREATE TABLE `messages` (
2264 `message_id` int(11) NOT NULL auto_increment,
2265 `borrowernumber` int(11) NOT NULL,
2266 `branchcode` varchar(10) default NULL,
2267 `message_type` varchar(1) NOT NULL,
2268 `message` text NOT NULL,
2269 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2270 PRIMARY KEY (`message_id`)
2271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2274 -- Table structure for table `accountlines`
2277 DROP TABLE IF EXISTS `accountlines`;
2278 CREATE TABLE `accountlines` (
2279 `borrowernumber` int(11) NOT NULL default 0,
2280 `accountno` smallint(6) NOT NULL default 0,
2281 `itemnumber` int(11) default NULL,
2282 `date` date default NULL,
2283 `amount` decimal(28,6) default NULL,
2284 `description` mediumtext,
2285 `dispute` mediumtext,
2286 `accounttype` varchar(5) default NULL,
2287 `amountoutstanding` decimal(28,6) default NULL,
2288 `lastincrement` decimal(28,6) default NULL,
2289 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2290 `notify_id` int(11) NOT NULL default 0,
2291 `notify_level` int(2) NOT NULL default 0,
2292 KEY `acctsborridx` (`borrowernumber`),
2293 KEY `timeidx` (`timestamp`),
2294 KEY `itemnumber` (`itemnumber`),
2295 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2296 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2297 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2300 -- Table structure for table `accountoffsets`
2303 DROP TABLE IF EXISTS `accountoffsets`;
2304 CREATE TABLE `accountoffsets` (
2305 `borrowernumber` int(11) NOT NULL default 0,
2306 `accountno` smallint(6) NOT NULL default 0,
2307 `offsetaccount` smallint(6) NOT NULL default 0,
2308 `offsetamount` decimal(28,6) default NULL,
2309 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2310 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2314 -- Table structure for table `action_logs`
2317 DROP TABLE IF EXISTS `action_logs`;
2318 CREATE TABLE `action_logs` (
2319 `action_id` int(11) NOT NULL auto_increment,
2320 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2321 `user` int(11) NOT NULL default 0,
2324 `object` int(11) default NULL,
2326 PRIMARY KEY (`action_id`),
2327 KEY (`timestamp`,`user`)
2328 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2331 -- Table structure for table `alert`
2334 DROP TABLE IF EXISTS `alert`;
2335 CREATE TABLE `alert` (
2336 `alertid` int(11) NOT NULL auto_increment,
2337 `borrowernumber` int(11) NOT NULL default 0,
2338 `type` varchar(10) NOT NULL default '',
2339 `externalid` varchar(20) NOT NULL default '',
2340 PRIMARY KEY (`alertid`),
2341 KEY `borrowernumber` (`borrowernumber`),
2342 KEY `type` (`type`,`externalid`)
2343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2346 -- Table structure for table `aqbasketgroups`
2349 DROP TABLE IF EXISTS `aqbasketgroups`;
2350 CREATE TABLE `aqbasketgroups` (
2351 `id` int(11) NOT NULL auto_increment,
2352 `name` varchar(50) default NULL,
2353 `closed` tinyint(1) default NULL,
2354 `booksellerid` int(11) NOT NULL,
2356 KEY `booksellerid` (`booksellerid`),
2357 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2358 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2361 -- Table structure for table `aqbasket`
2364 DROP TABLE IF EXISTS `aqbasket`;
2365 CREATE TABLE `aqbasket` (
2366 `basketno` int(11) NOT NULL auto_increment,
2367 `basketname` varchar(50) default NULL,
2369 `booksellernote` mediumtext,
2370 `contractnumber` int(11),
2371 `creationdate` date default NULL,
2372 `closedate` date default NULL,
2373 `booksellerid` int(11) NOT NULL default 1,
2374 `authorisedby` varchar(10) default NULL,
2375 `booksellerinvoicenumber` mediumtext,
2376 `basketgroupid` int(11),
2377 PRIMARY KEY (`basketno`),
2378 KEY `booksellerid` (`booksellerid`),
2379 KEY `basketgroupid` (`basketgroupid`),
2380 KEY `contractnumber` (`contractnumber`),
2381 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2382 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2383 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2384 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2387 -- Table structure for table `aqbooksellers`
2390 DROP TABLE IF EXISTS `aqbooksellers`;
2391 CREATE TABLE `aqbooksellers` (
2392 `id` int(11) NOT NULL auto_increment,
2393 `name` mediumtext NOT NULL,
2394 `address1` mediumtext,
2395 `address2` mediumtext,
2396 `address3` mediumtext,
2397 `address4` mediumtext,
2398 `phone` varchar(30) default NULL,
2399 `accountnumber` mediumtext,
2400 `othersupplier` mediumtext,
2401 `currency` varchar(3) NOT NULL default '',
2402 `booksellerfax` mediumtext,
2404 `bookselleremail` mediumtext,
2405 `booksellerurl` mediumtext,
2406 `contact` varchar(100) default NULL,
2407 `postal` mediumtext,
2408 `url` varchar(255) default NULL,
2409 `contpos` varchar(100) default NULL,
2410 `contphone` varchar(100) default NULL,
2411 `contfax` varchar(100) default NULL,
2412 `contaltphone` varchar(100) default NULL,
2413 `contemail` varchar(100) default NULL,
2414 `contnotes` mediumtext,
2415 `active` tinyint(4) default NULL,
2416 `listprice` varchar(10) default NULL,
2417 `invoiceprice` varchar(10) default NULL,
2418 `gstreg` tinyint(4) default NULL,
2419 `listincgst` tinyint(4) default NULL,
2420 `invoiceincgst` tinyint(4) default NULL,
2421 `gstrate` decimal(6,4) default NULL,
2422 `discount` float(6,4) default NULL,
2423 `fax` varchar(50) default NULL,
2425 KEY `listprice` (`listprice`),
2426 KEY `invoiceprice` (`invoiceprice`),
2427 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2428 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2429 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2432 -- Table structure for table `aqbudgets`
2435 DROP TABLE IF EXISTS `aqbudgets`;
2436 CREATE TABLE `aqbudgets` (
2437 `budget_id` int(11) NOT NULL auto_increment,
2438 `budget_parent_id` int(11) default NULL,
2439 `budget_code` varchar(30) default NULL,
2440 `budget_name` varchar(80) default NULL,
2441 `budget_branchcode` varchar(10) default NULL,
2442 `budget_amount` decimal(28,6) NULL default '0.00',
2443 `budget_encumb` decimal(28,6) NULL default '0.00',
2444 `budget_expend` decimal(28,6) NULL default '0.00',
2445 `budget_notes` mediumtext,
2446 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2447 `budget_period_id` int(11) default NULL,
2448 `sort1_authcat` varchar(80) default NULL,
2449 `sort2_authcat` varchar(80) default NULL,
2450 `budget_owner_id` int(11) default NULL,
2451 `budget_permission` int(1) default '0',
2452 PRIMARY KEY (`budget_id`)
2453 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2457 -- Table structure for table `aqbudgetperiods`
2461 DROP TABLE IF EXISTS `aqbudgetperiods`;
2462 CREATE TABLE `aqbudgetperiods` (
2463 `budget_period_id` int(11) NOT NULL auto_increment,
2464 `budget_period_startdate` date NOT NULL,
2465 `budget_period_enddate` date NOT NULL,
2466 `budget_period_active` tinyint(1) default '0',
2467 `budget_period_description` mediumtext,
2468 `budget_period_total` decimal(28,6),
2469 `budget_period_locked` tinyint(1) default NULL,
2470 `sort1_authcat` varchar(10) default NULL,
2471 `sort2_authcat` varchar(10) default NULL,
2472 PRIMARY KEY (`budget_period_id`)
2473 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2476 -- Table structure for table `aqbudgets_planning`
2479 DROP TABLE IF EXISTS `aqbudgets_planning`;
2480 CREATE TABLE `aqbudgets_planning` (
2481 `plan_id` int(11) NOT NULL auto_increment,
2482 `budget_id` int(11) NOT NULL,
2483 `budget_period_id` int(11) NOT NULL,
2484 `estimated_amount` decimal(28,6) default NULL,
2485 `authcat` varchar(30) NOT NULL,
2486 `authvalue` varchar(30) NOT NULL,
2487 `display` tinyint(1) DEFAULT 1,
2488 PRIMARY KEY (`plan_id`),
2489 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2490 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2493 -- Table structure for table 'aqcontract'
2496 DROP TABLE IF EXISTS `aqcontract`;
2497 CREATE TABLE `aqcontract` (
2498 `contractnumber` int(11) NOT NULL auto_increment,
2499 `contractstartdate` date default NULL,
2500 `contractenddate` date default NULL,
2501 `contractname` varchar(50) default NULL,
2502 `contractdescription` mediumtext,
2503 `booksellerid` int(11) not NULL,
2504 PRIMARY KEY (`contractnumber`),
2505 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2506 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2510 -- Table structure for table `aqorderdelivery`
2513 DROP TABLE IF EXISTS `aqorderdelivery`;
2514 CREATE TABLE `aqorderdelivery` (
2515 `ordernumber` date default NULL,
2516 `deliverynumber` smallint(6) NOT NULL default 0,
2517 `deliverydate` varchar(18) default NULL,
2518 `qtydelivered` smallint(6) default NULL,
2519 `deliverycomments` mediumtext
2520 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2523 -- Table structure for table `aqorders`
2526 DROP TABLE IF EXISTS `aqorders`;
2527 CREATE TABLE `aqorders` (
2528 `ordernumber` int(11) NOT NULL auto_increment,
2529 `biblionumber` int(11) default NULL,
2530 `entrydate` date default NULL,
2531 `quantity` smallint(6) default NULL,
2532 `currency` varchar(3) default NULL,
2533 `listprice` decimal(28,6) default NULL,
2534 `totalamount` decimal(28,6) default NULL,
2535 `datereceived` date default NULL,
2536 `booksellerinvoicenumber` mediumtext,
2537 `freight` decimal(28,6) default NULL,
2538 `unitprice` decimal(28,6) default NULL,
2539 `quantityreceived` smallint(6) NOT NULL default 0,
2540 `cancelledby` varchar(10) default NULL,
2541 `datecancellationprinted` date default NULL,
2543 `supplierreference` mediumtext,
2544 `purchaseordernumber` mediumtext,
2545 `subscription` tinyint(1) default NULL,
2546 `serialid` varchar(30) default NULL,
2547 `basketno` int(11) default NULL,
2548 `biblioitemnumber` int(11) default NULL,
2549 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2550 `rrp` decimal(13,2) default NULL,
2551 `ecost` decimal(13,2) default NULL,
2552 `gst` decimal(13,2) default NULL,
2553 `budget_id` int(11) NOT NULL,
2554 `budgetgroup_id` int(11) NOT NULL,
2555 `budgetdate` date default NULL,
2556 `sort1` varchar(80) default NULL,
2557 `sort2` varchar(80) default NULL,
2558 `sort1_authcat` varchar(10) default NULL,
2559 `sort2_authcat` varchar(10) default NULL,
2560 `uncertainprice` tinyint(1),
2561 PRIMARY KEY (`ordernumber`),
2562 KEY `basketno` (`basketno`),
2563 KEY `biblionumber` (`biblionumber`),
2564 KEY `budget_id` (`budget_id`),
2565 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2566 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2567 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2571 -- Table structure for table `aqorders_items`
2574 DROP TABLE IF EXISTS `aqorders_items`;
2575 CREATE TABLE `aqorders_items` (
2576 `ordernumber` int(11) NOT NULL,
2577 `itemnumber` int(11) NOT NULL,
2578 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2579 PRIMARY KEY (`itemnumber`),
2580 KEY `ordernumber` (`ordernumber`)
2581 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2584 -- Table structure for table `fieldmapping`
2587 DROP TABLE IF EXISTS `fieldmapping`;
2588 CREATE TABLE `fieldmapping` (
2589 `id` int(11) NOT NULL auto_increment,
2590 `field` varchar(255) NOT NULL,
2591 `frameworkcode` char(4) NOT NULL default '',
2592 `fieldcode` char(3) NOT NULL,
2593 `subfieldcode` char(1) NOT NULL,
2595 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2598 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2599 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2600 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2601 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2602 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2603 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2604 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2605 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;