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 PRIMARY KEY (`suggestionid`),
1840 KEY `suggestedby` (`suggestedby`),
1841 KEY `managedby` (`managedby`)
1842 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1845 -- Table structure for table `systempreferences`
1848 DROP TABLE IF EXISTS `systempreferences`;
1849 CREATE TABLE `systempreferences` (
1850 `variable` varchar(50) NOT NULL default '',
1852 `options` mediumtext,
1854 `type` varchar(20) default NULL,
1855 PRIMARY KEY (`variable`)
1856 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1859 -- Table structure for table `tags`
1862 DROP TABLE IF EXISTS `tags`;
1863 CREATE TABLE `tags` (
1864 `entry` varchar(255) NOT NULL default '',
1865 `weight` bigint(20) NOT NULL default 0,
1866 PRIMARY KEY (`entry`)
1867 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1870 -- Table structure for table `tags_all`
1873 DROP TABLE IF EXISTS `tags_all`;
1874 CREATE TABLE `tags_all` (
1875 `tag_id` int(11) NOT NULL auto_increment,
1876 `borrowernumber` int(11) NOT NULL,
1877 `biblionumber` int(11) NOT NULL,
1878 `term` varchar(255) NOT NULL,
1879 `language` int(4) default NULL,
1880 `date_created` datetime NOT NULL,
1881 PRIMARY KEY (`tag_id`),
1882 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1883 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1884 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1885 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1886 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1887 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1888 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1891 -- Table structure for table `tags_approval`
1894 DROP TABLE IF EXISTS `tags_approval`;
1895 CREATE TABLE `tags_approval` (
1896 `term` varchar(255) NOT NULL,
1897 `approved` int(1) NOT NULL default '0',
1898 `date_approved` datetime default NULL,
1899 `approved_by` int(11) default NULL,
1900 `weight_total` int(9) NOT NULL default '1',
1901 PRIMARY KEY (`term`),
1902 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1903 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1904 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1905 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1908 -- Table structure for table `tags_index`
1911 DROP TABLE IF EXISTS `tags_index`;
1912 CREATE TABLE `tags_index` (
1913 `term` varchar(255) NOT NULL,
1914 `biblionumber` int(11) NOT NULL,
1915 `weight` int(9) NOT NULL default '1',
1916 PRIMARY KEY (`term`,`biblionumber`),
1917 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1918 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1919 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1920 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1921 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1922 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1925 -- Table structure for table `userflags`
1928 DROP TABLE IF EXISTS `userflags`;
1929 CREATE TABLE `userflags` (
1930 `bit` int(11) NOT NULL default 0,
1931 `flag` varchar(30) default NULL,
1932 `flagdesc` varchar(255) default NULL,
1933 `defaulton` int(11) default NULL,
1935 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1938 -- Table structure for table `virtualshelves`
1941 DROP TABLE IF EXISTS `virtualshelves`;
1942 CREATE TABLE `virtualshelves` (
1943 `shelfnumber` int(11) NOT NULL auto_increment,
1944 `shelfname` varchar(255) default NULL,
1945 `owner` varchar(80) default NULL,
1946 `category` varchar(1) default NULL,
1947 `sortfield` varchar(16) default NULL,
1948 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1949 PRIMARY KEY (`shelfnumber`)
1950 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1953 -- Table structure for table `virtualshelfcontents`
1956 DROP TABLE IF EXISTS `virtualshelfcontents`;
1957 CREATE TABLE `virtualshelfcontents` (
1958 `shelfnumber` int(11) NOT NULL default 0,
1959 `biblionumber` int(11) NOT NULL default 0,
1960 `flags` int(11) default NULL,
1961 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1962 KEY `shelfnumber` (`shelfnumber`),
1963 KEY `biblionumber` (`biblionumber`),
1964 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1965 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1966 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1969 -- Table structure for table `z3950servers`
1972 DROP TABLE IF EXISTS `z3950servers`;
1973 CREATE TABLE `z3950servers` (
1974 `host` varchar(255) default NULL,
1975 `port` int(11) default NULL,
1976 `db` varchar(255) default NULL,
1977 `userid` varchar(255) default NULL,
1978 `password` varchar(255) default NULL,
1980 `id` int(11) NOT NULL auto_increment,
1981 `checked` smallint(6) default NULL,
1982 `rank` int(11) default NULL,
1983 `syntax` varchar(80) default NULL,
1985 `position` enum('primary','secondary','') NOT NULL default 'primary',
1986 `type` enum('zed','opensearch') NOT NULL default 'zed',
1987 `encoding` text default NULL,
1988 `description` text NOT NULL,
1990 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1993 -- Table structure for table `zebraqueue`
1996 DROP TABLE IF EXISTS `zebraqueue`;
1997 CREATE TABLE `zebraqueue` (
1998 `id` int(11) NOT NULL auto_increment,
1999 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2000 `operation` char(20) NOT NULL default '',
2001 `server` char(20) NOT NULL default '',
2002 `done` int(11) NOT NULL default '0',
2003 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2005 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2006 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2008 DROP TABLE IF EXISTS `services_throttle`;
2009 CREATE TABLE `services_throttle` (
2010 `service_type` varchar(10) NOT NULL default '',
2011 `service_count` varchar(45) default NULL,
2012 PRIMARY KEY (`service_type`)
2013 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2015 -- http://www.w3.org/International/articles/language-tags/
2018 DROP TABLE IF EXISTS language_subtag_registry;
2019 CREATE TABLE language_subtag_registry (
2021 type varchar(25), -- language-script-region-variant-extension-privateuse
2022 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2024 id int(11) NOT NULL auto_increment,
2026 KEY `subtag` (`subtag`)
2027 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2029 -- TODO: add suppress_scripts
2030 -- this maps three letter codes defined in iso639.2 back to their
2031 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2032 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2033 CREATE TABLE language_rfc4646_to_iso639 (
2034 rfc4646_subtag varchar(25),
2035 iso639_2_code varchar(25),
2036 id int(11) NOT NULL auto_increment,
2038 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2039 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2041 DROP TABLE IF EXISTS language_descriptions;
2042 CREATE TABLE language_descriptions (
2046 description varchar(255),
2047 id int(11) NOT NULL auto_increment,
2049 KEY `lang` (`lang`),
2050 KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
2051 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2053 -- bi-directional support, keyed by script subcode
2054 DROP TABLE IF EXISTS language_script_bidi;
2055 CREATE TABLE language_script_bidi (
2056 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2057 bidi varchar(3), -- rtl ltr
2058 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2059 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2061 -- TODO: need to map language subtags to script subtags for detection
2062 -- of bidi when script is not specified (like ar, he)
2063 DROP TABLE IF EXISTS language_script_mapping;
2064 CREATE TABLE language_script_mapping (
2065 language_subtag varchar(25),
2066 script_subtag varchar(25),
2067 KEY `language_subtag` (`language_subtag`)
2068 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2070 DROP TABLE IF EXISTS `permissions`;
2071 CREATE TABLE `permissions` (
2072 `module_bit` int(11) NOT NULL DEFAULT 0,
2073 `code` varchar(64) DEFAULT NULL,
2074 `description` varchar(255) DEFAULT NULL,
2075 PRIMARY KEY (`module_bit`, `code`),
2076 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2077 ON DELETE CASCADE ON UPDATE CASCADE
2078 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2080 DROP TABLE IF EXISTS `serialitems`;
2081 CREATE TABLE `serialitems` (
2082 `itemnumber` int(11) NOT NULL,
2083 `serialid` int(11) NOT NULL,
2084 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2085 KEY `serialitems_sfk_1` (`serialid`),
2086 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
2087 CONSTRAINT serialitems_sfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
2088 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2090 DROP TABLE IF EXISTS `user_permissions`;
2091 CREATE TABLE `user_permissions` (
2092 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2093 `module_bit` int(11) NOT NULL DEFAULT 0,
2094 `code` varchar(64) DEFAULT NULL,
2095 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2096 ON DELETE CASCADE ON UPDATE CASCADE,
2097 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2098 ON DELETE CASCADE ON UPDATE CASCADE
2099 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2102 -- Table structure for table `tmp_holdsqueue`
2105 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2106 CREATE TABLE `tmp_holdsqueue` (
2107 `biblionumber` int(11) default NULL,
2108 `itemnumber` int(11) default NULL,
2109 `barcode` varchar(20) default NULL,
2110 `surname` mediumtext NOT NULL,
2113 `borrowernumber` int(11) NOT NULL,
2114 `cardnumber` varchar(16) default NULL,
2115 `reservedate` date default NULL,
2117 `itemcallnumber` varchar(255) default NULL,
2118 `holdingbranch` varchar(10) default NULL,
2119 `pickbranch` varchar(10) default NULL,
2121 `item_level_request` tinyint(4) NOT NULL default 0
2122 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2125 -- Table structure for table `message_queue`
2128 DROP TABLE IF EXISTS `message_queue`;
2129 CREATE TABLE `message_queue` (
2130 `message_id` int(11) NOT NULL auto_increment,
2131 `borrowernumber` int(11) default NULL,
2134 `metadata` text DEFAULT NULL,
2135 `letter_code` varchar(64) DEFAULT NULL,
2136 `message_transport_type` varchar(20) NOT NULL,
2137 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2138 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2139 `to_address` mediumtext,
2140 `from_address` mediumtext,
2141 `content_type` text,
2142 KEY `message_id` (`message_id`),
2143 KEY `borrowernumber` (`borrowernumber`),
2144 KEY `message_transport_type` (`message_transport_type`),
2145 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2146 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2147 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2150 -- Table structure for table `message_transport_types`
2153 DROP TABLE IF EXISTS `message_transport_types`;
2154 CREATE TABLE `message_transport_types` (
2155 `message_transport_type` varchar(20) NOT NULL,
2156 PRIMARY KEY (`message_transport_type`)
2157 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2160 -- Table structure for table `message_attributes`
2163 DROP TABLE IF EXISTS `message_attributes`;
2164 CREATE TABLE `message_attributes` (
2165 `message_attribute_id` int(11) NOT NULL auto_increment,
2166 `message_name` varchar(40) NOT NULL default '',
2167 `takes_days` tinyint(1) NOT NULL default '0',
2168 PRIMARY KEY (`message_attribute_id`),
2169 UNIQUE KEY `message_name` (`message_name`)
2170 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2173 -- Table structure for table `message_transports`
2176 DROP TABLE IF EXISTS `message_transports`;
2177 CREATE TABLE `message_transports` (
2178 `message_attribute_id` int(11) NOT NULL,
2179 `message_transport_type` varchar(20) NOT NULL,
2180 `is_digest` tinyint(1) NOT NULL default '0',
2181 `letter_module` varchar(20) NOT NULL default '',
2182 `letter_code` varchar(20) NOT NULL default '',
2183 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2184 KEY `message_transport_type` (`message_transport_type`),
2185 KEY `letter_module` (`letter_module`,`letter_code`),
2186 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2187 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2188 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2192 -- Table structure for table `borrower_message_preferences`
2195 DROP TABLE IF EXISTS `borrower_message_preferences`;
2196 CREATE TABLE `borrower_message_preferences` (
2197 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2198 `borrowernumber` int(11) default NULL,
2199 `categorycode` varchar(10) default NULL,
2200 `message_attribute_id` int(11) default '0',
2201 `days_in_advance` int(11) default '0',
2202 `wants_digest` tinyint(1) NOT NULL default '0',
2203 PRIMARY KEY (`borrower_message_preference_id`),
2204 KEY `borrowernumber` (`borrowernumber`),
2205 KEY `categorycode` (`categorycode`),
2206 KEY `message_attribute_id` (`message_attribute_id`),
2207 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2208 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2209 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2210 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2213 -- Table structure for table `borrower_message_transport_preferences`
2216 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2217 CREATE TABLE `borrower_message_transport_preferences` (
2218 `borrower_message_preference_id` int(11) NOT NULL default '0',
2219 `message_transport_type` varchar(20) NOT NULL default '0',
2220 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2221 KEY `message_transport_type` (`message_transport_type`),
2222 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,
2223 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
2224 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2227 -- Table structure for the table branch_transfer_limits
2230 DROP TABLE IF EXISTS `branch_transfer_limits`;
2231 CREATE TABLE branch_transfer_limits (
2232 limitId int(8) NOT NULL auto_increment,
2233 toBranch varchar(10) NOT NULL,
2234 fromBranch varchar(10) NOT NULL,
2235 itemtype varchar(10) NULL,
2236 ccode varchar(10) NULL,
2237 PRIMARY KEY (limitId)
2238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2241 -- Table structure for table `item_circulation_alert_preferences`
2244 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2245 CREATE TABLE `item_circulation_alert_preferences` (
2246 `id` int(11) NOT NULL auto_increment,
2247 `branchcode` varchar(10) NOT NULL,
2248 `categorycode` varchar(10) NOT NULL,
2249 `item_type` varchar(10) NOT NULL,
2250 `notification` varchar(16) NOT NULL,
2252 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2253 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2256 -- Table structure for table `messages`
2258 DROP TABLE IF EXISTS `messages`;
2259 CREATE TABLE `messages` (
2260 `message_id` int(11) NOT NULL auto_increment,
2261 `borrowernumber` int(11) NOT NULL,
2262 `branchcode` varchar(10) default NULL,
2263 `message_type` varchar(1) NOT NULL,
2264 `message` text NOT NULL,
2265 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2266 PRIMARY KEY (`message_id`)
2267 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2270 -- Table structure for table `accountlines`
2273 DROP TABLE IF EXISTS `accountlines`;
2274 CREATE TABLE `accountlines` (
2275 `borrowernumber` int(11) NOT NULL default 0,
2276 `accountno` smallint(6) NOT NULL default 0,
2277 `itemnumber` int(11) default NULL,
2278 `date` date default NULL,
2279 `amount` decimal(28,6) default NULL,
2280 `description` mediumtext,
2281 `dispute` mediumtext,
2282 `accounttype` varchar(5) default NULL,
2283 `amountoutstanding` decimal(28,6) default NULL,
2284 `lastincrement` decimal(28,6) default NULL,
2285 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2286 `notify_id` int(11) NOT NULL default 0,
2287 `notify_level` int(2) NOT NULL default 0,
2288 KEY `acctsborridx` (`borrowernumber`),
2289 KEY `timeidx` (`timestamp`),
2290 KEY `itemnumber` (`itemnumber`),
2291 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2292 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2293 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2296 -- Table structure for table `accountoffsets`
2299 DROP TABLE IF EXISTS `accountoffsets`;
2300 CREATE TABLE `accountoffsets` (
2301 `borrowernumber` int(11) NOT NULL default 0,
2302 `accountno` smallint(6) NOT NULL default 0,
2303 `offsetaccount` smallint(6) NOT NULL default 0,
2304 `offsetamount` decimal(28,6) default NULL,
2305 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2306 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2310 -- Table structure for table `action_logs`
2313 DROP TABLE IF EXISTS `action_logs`;
2314 CREATE TABLE `action_logs` (
2315 `action_id` int(11) NOT NULL auto_increment,
2316 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2317 `user` int(11) NOT NULL default 0,
2320 `object` int(11) default NULL,
2322 PRIMARY KEY (`action_id`),
2323 KEY (`timestamp`,`user`)
2324 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2327 -- Table structure for table `alert`
2330 DROP TABLE IF EXISTS `alert`;
2331 CREATE TABLE `alert` (
2332 `alertid` int(11) NOT NULL auto_increment,
2333 `borrowernumber` int(11) NOT NULL default 0,
2334 `type` varchar(10) NOT NULL default '',
2335 `externalid` varchar(20) NOT NULL default '',
2336 PRIMARY KEY (`alertid`),
2337 KEY `borrowernumber` (`borrowernumber`),
2338 KEY `type` (`type`,`externalid`)
2339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2342 -- Table structure for table `aqbasketgroups`
2345 DROP TABLE IF EXISTS `aqbasketgroups`;
2346 CREATE TABLE `aqbasketgroups` (
2347 `id` int(11) NOT NULL auto_increment,
2348 `name` varchar(50) default NULL,
2349 `closed` tinyint(1) default NULL,
2350 `booksellerid` int(11) NOT NULL,
2352 KEY `booksellerid` (`booksellerid`),
2353 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2354 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2357 -- Table structure for table `aqbasket`
2360 DROP TABLE IF EXISTS `aqbasket`;
2361 CREATE TABLE `aqbasket` (
2362 `basketno` int(11) NOT NULL auto_increment,
2363 `basketname` varchar(50) default NULL,
2365 `booksellernote` mediumtext,
2366 `contractnumber` int(11),
2367 `creationdate` date default NULL,
2368 `closedate` date default NULL,
2369 `booksellerid` int(11) NOT NULL default 1,
2370 `authorisedby` varchar(10) default NULL,
2371 `booksellerinvoicenumber` mediumtext,
2372 `basketgroupid` int(11),
2373 PRIMARY KEY (`basketno`),
2374 KEY `booksellerid` (`booksellerid`),
2375 KEY `basketgroupid` (`basketgroupid`),
2376 KEY `contractnumber` (`contractnumber`),
2377 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2378 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2379 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2380 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2383 -- Table structure for table `aqbooksellers`
2386 DROP TABLE IF EXISTS `aqbooksellers`;
2387 CREATE TABLE `aqbooksellers` (
2388 `id` int(11) NOT NULL auto_increment,
2389 `name` mediumtext NOT NULL,
2390 `address1` mediumtext,
2391 `address2` mediumtext,
2392 `address3` mediumtext,
2393 `address4` mediumtext,
2394 `phone` varchar(30) default NULL,
2395 `accountnumber` mediumtext,
2396 `othersupplier` mediumtext,
2397 `currency` varchar(3) NOT NULL default '',
2398 `booksellerfax` mediumtext,
2400 `bookselleremail` mediumtext,
2401 `booksellerurl` mediumtext,
2402 `contact` varchar(100) default NULL,
2403 `postal` mediumtext,
2404 `url` varchar(255) default NULL,
2405 `contpos` varchar(100) default NULL,
2406 `contphone` varchar(100) default NULL,
2407 `contfax` varchar(100) default NULL,
2408 `contaltphone` varchar(100) default NULL,
2409 `contemail` varchar(100) default NULL,
2410 `contnotes` mediumtext,
2411 `active` tinyint(4) default NULL,
2412 `listprice` varchar(10) default NULL,
2413 `invoiceprice` varchar(10) default NULL,
2414 `gstreg` tinyint(4) default NULL,
2415 `listincgst` tinyint(4) default NULL,
2416 `invoiceincgst` tinyint(4) default NULL,
2417 `gstrate` decimal(6,4) default NULL,
2418 `discount` float(6,4) default NULL,
2419 `fax` varchar(50) default NULL,
2421 KEY `listprice` (`listprice`),
2422 KEY `invoiceprice` (`invoiceprice`),
2423 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2424 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2425 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2428 -- Table structure for table `aqbudgets`
2431 DROP TABLE IF EXISTS `aqbudgets`;
2432 CREATE TABLE `aqbudgets` (
2433 `budget_id` int(11) NOT NULL auto_increment,
2434 `budget_parent_id` int(11) default NULL,
2435 `budget_code` varchar(30) default NULL,
2436 `budget_name` varchar(80) default NULL,
2437 `budget_branchcode` varchar(10) default NULL,
2438 `budget_amount` decimal(28,6) NULL default '0.00',
2439 `budget_encumb` decimal(28,6) NULL default '0.00',
2440 `budget_expend` decimal(28,6) NULL default '0.00',
2441 `budget_notes` mediumtext,
2442 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2443 `budget_period_id` int(11) default NULL,
2444 `sort1_authcat` varchar(80) default NULL,
2445 `sort2_authcat` varchar(80) default NULL,
2446 `budget_owner_id` int(11) default NULL,
2447 `budget_permission` int(1) default '0',
2448 PRIMARY KEY (`budget_id`)
2449 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2453 -- Table structure for table `aqbudgetperiods`
2457 DROP TABLE IF EXISTS `aqbudgetperiods`;
2458 CREATE TABLE `aqbudgetperiods` (
2459 `budget_period_id` int(11) NOT NULL auto_increment,
2460 `budget_period_startdate` date NOT NULL,
2461 `budget_period_enddate` date NOT NULL,
2462 `budget_period_active` tinyint(1) default '0',
2463 `budget_period_description` mediumtext,
2464 `budget_period_total` decimal(28,6),
2465 `budget_period_locked` tinyint(1) default NULL,
2466 `sort1_authcat` varchar(10) default NULL,
2467 `sort2_authcat` varchar(10) default NULL,
2468 PRIMARY KEY (`budget_period_id`)
2469 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2472 -- Table structure for table `aqbudgets_planning`
2475 DROP TABLE IF EXISTS `aqbudgets_planning`;
2476 CREATE TABLE `aqbudgets_planning` (
2477 `plan_id` int(11) NOT NULL auto_increment,
2478 `budget_id` int(11) NOT NULL,
2479 `budget_period_id` int(11) NOT NULL,
2480 `estimated_amount` decimal(28,6) default NULL,
2481 `authcat` varchar(30) NOT NULL,
2482 `authvalue` varchar(30) NOT NULL,
2483 `display` tinyint(1) DEFAULT 1,
2484 PRIMARY KEY (`plan_id`),
2485 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2486 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2489 -- Table structure for table 'aqcontract'
2492 DROP TABLE IF EXISTS `aqcontract`;
2493 CREATE TABLE `aqcontract` (
2494 `contractnumber` int(11) NOT NULL auto_increment,
2495 `contractstartdate` date default NULL,
2496 `contractenddate` date default NULL,
2497 `contractname` varchar(50) default NULL,
2498 `contractdescription` mediumtext,
2499 `booksellerid` int(11) not NULL,
2500 PRIMARY KEY (`contractnumber`),
2501 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2502 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2503 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2506 -- Table structure for table `aqorderdelivery`
2509 DROP TABLE IF EXISTS `aqorderdelivery`;
2510 CREATE TABLE `aqorderdelivery` (
2511 `ordernumber` date default NULL,
2512 `deliverynumber` smallint(6) NOT NULL default 0,
2513 `deliverydate` varchar(18) default NULL,
2514 `qtydelivered` smallint(6) default NULL,
2515 `deliverycomments` mediumtext
2516 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2519 -- Table structure for table `aqorders`
2522 DROP TABLE IF EXISTS `aqorders`;
2523 CREATE TABLE `aqorders` (
2524 `ordernumber` int(11) NOT NULL auto_increment,
2525 `biblionumber` int(11) default NULL,
2526 `entrydate` date default NULL,
2527 `quantity` smallint(6) default NULL,
2528 `currency` varchar(3) default NULL,
2529 `listprice` decimal(28,6) default NULL,
2530 `totalamount` decimal(28,6) default NULL,
2531 `datereceived` date default NULL,
2532 `booksellerinvoicenumber` mediumtext,
2533 `freight` decimal(28,6) default NULL,
2534 `unitprice` decimal(28,6) default NULL,
2535 `quantityreceived` smallint(6) NOT NULL default 0,
2536 `cancelledby` varchar(10) default NULL,
2537 `datecancellationprinted` date default NULL,
2539 `supplierreference` mediumtext,
2540 `purchaseordernumber` mediumtext,
2541 `subscription` tinyint(1) default NULL,
2542 `serialid` varchar(30) default NULL,
2543 `basketno` int(11) default NULL,
2544 `biblioitemnumber` int(11) default NULL,
2545 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2546 `rrp` decimal(13,2) default NULL,
2547 `ecost` decimal(13,2) default NULL,
2548 `gst` decimal(13,2) default NULL,
2549 `budget_id` int(11) NOT NULL,
2550 `budgetgroup_id` int(11) NOT NULL,
2551 `budgetdate` date default NULL,
2552 `sort1` varchar(80) default NULL,
2553 `sort2` varchar(80) default NULL,
2554 `sort1_authcat` varchar(10) default NULL,
2555 `sort2_authcat` varchar(10) default NULL,
2556 `uncertainprice` tinyint(1),
2557 PRIMARY KEY (`ordernumber`),
2558 KEY `basketno` (`basketno`),
2559 KEY `biblionumber` (`biblionumber`),
2560 KEY `budget_id` (`budget_id`),
2561 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2562 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2563 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2567 -- Table structure for table `aqorders_items`
2570 DROP TABLE IF EXISTS `aqorders_items`;
2571 CREATE TABLE `aqorders_items` (
2572 `ordernumber` int(11) NOT NULL,
2573 `itemnumber` int(11) NOT NULL,
2574 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2575 PRIMARY KEY (`itemnumber`),
2576 KEY `ordernumber` (`ordernumber`)
2577 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2580 -- Table structure for table `fieldmapping`
2583 DROP TABLE IF EXISTS `fieldmapping`;
2584 CREATE TABLE `fieldmapping` (
2585 `id` int(11) NOT NULL auto_increment,
2586 `field` varchar(255) NOT NULL,
2587 `frameworkcode` char(4) NOT NULL default '',
2588 `fieldcode` char(3) NOT NULL,
2589 `subfieldcode` char(1) NOT NULL,
2591 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2594 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2595 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2596 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2597 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2598 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2599 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2600 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2601 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;