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(9) 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 KEY `borrowernumber` (`borrowernumber`),
707 KEY `cardnumber` (`cardnumber`)
708 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
711 -- Table structure for table `deleteditems`
714 DROP TABLE IF EXISTS `deleteditems`;
715 CREATE TABLE `deleteditems` (
716 `itemnumber` int(11) NOT NULL default 0,
717 `biblionumber` int(11) NOT NULL default 0,
718 `biblioitemnumber` int(11) NOT NULL default 0,
719 `barcode` varchar(20) default NULL,
720 `dateaccessioned` date default NULL,
721 `booksellerid` mediumtext default NULL,
722 `homebranch` varchar(10) default NULL,
723 `price` decimal(8,2) default NULL,
724 `replacementprice` decimal(8,2) default NULL,
725 `replacementpricedate` date default NULL,
726 `datelastborrowed` date default NULL,
727 `datelastseen` date default NULL,
728 `stack` tinyint(1) default NULL,
729 `notforloan` tinyint(1) NOT NULL default 0,
730 `damaged` tinyint(1) NOT NULL default 0,
731 `itemlost` tinyint(1) NOT NULL default 0,
732 `wthdrawn` tinyint(1) NOT NULL default 0,
733 `itemcallnumber` varchar(255) default NULL,
734 `issues` smallint(6) default NULL,
735 `renewals` smallint(6) default NULL,
736 `reserves` smallint(6) default NULL,
737 `restricted` tinyint(1) default NULL,
738 `itemnotes` mediumtext,
739 `holdingbranch` varchar(10) default NULL,
740 `paidfor` mediumtext,
741 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
742 `location` varchar(80) default NULL,
743 `permanent_location` varchar(80) default NULL,
744 `onloan` date default NULL,
745 `cn_source` varchar(10) default NULL,
746 `cn_sort` varchar(30) default NULL,
747 `ccode` varchar(10) default NULL,
748 `materials` varchar(10) default NULL,
749 `uri` varchar(255) default NULL,
750 `itype` varchar(10) default NULL,
751 `more_subfields_xml` longtext default NULL,
752 `enumchron` varchar(80) default NULL,
753 `copynumber` varchar(32) default NULL,
754 `stocknumber` varchar(32) default NULL,
756 PRIMARY KEY (`itemnumber`),
757 KEY `delitembarcodeidx` (`barcode`),
758 KEY `delitemstocknumberidx` (`stocknumber`),
759 KEY `delitembinoidx` (`biblioitemnumber`),
760 KEY `delitembibnoidx` (`biblionumber`),
761 KEY `delhomebranch` (`homebranch`),
762 KEY `delholdingbranch` (`holdingbranch`)
763 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
766 -- Table structure for table `ethnicity`
769 DROP TABLE IF EXISTS `ethnicity`;
770 CREATE TABLE `ethnicity` (
771 `code` varchar(10) NOT NULL default '',
772 `name` varchar(255) default NULL,
774 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
777 -- Table structure for table `export_format`
780 DROP TABLE IF EXISTS `export_format`;
781 CREATE TABLE `export_format` (
782 `export_format_id` int(11) NOT NULL auto_increment,
783 `profile` varchar(255) NOT NULL,
784 `description` mediumtext NOT NULL,
785 `marcfields` mediumtext NOT NULL,
786 `csv_separator` varchar(2) NOT NULL,
787 `field_separator` varchar(2) NOT NULL,
788 `subfield_separator` varchar(2) NOT NULL,
789 `encoding` varchar(255) NOT NULL,
790 PRIMARY KEY (`export_format_id`)
791 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
795 -- Table structure for table `hold_fill_targets`
798 DROP TABLE IF EXISTS `hold_fill_targets`;
799 CREATE TABLE hold_fill_targets (
800 `borrowernumber` int(11) NOT NULL,
801 `biblionumber` int(11) NOT NULL,
802 `itemnumber` int(11) NOT NULL,
803 `source_branchcode` varchar(10) default NULL,
804 `item_level_request` tinyint(4) NOT NULL default 0,
805 PRIMARY KEY `itemnumber` (`itemnumber`),
806 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
807 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
808 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
809 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
810 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
811 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
812 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
813 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
814 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
815 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
818 -- Table structure for table `import_batches`
821 DROP TABLE IF EXISTS `import_batches`;
822 CREATE TABLE `import_batches` (
823 `import_batch_id` int(11) NOT NULL auto_increment,
824 `matcher_id` int(11) default NULL,
825 `template_id` int(11) default NULL,
826 `branchcode` varchar(10) default NULL,
827 `num_biblios` int(11) NOT NULL default 0,
828 `num_items` int(11) NOT NULL default 0,
829 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
830 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
831 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
832 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
833 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
834 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
835 `file_name` varchar(100),
836 `comments` mediumtext,
837 PRIMARY KEY (`import_batch_id`),
838 KEY `branchcode` (`branchcode`)
839 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
842 -- Table structure for table `import_records`
845 DROP TABLE IF EXISTS `import_records`;
846 CREATE TABLE `import_records` (
847 `import_record_id` int(11) NOT NULL auto_increment,
848 `import_batch_id` int(11) NOT NULL,
849 `branchcode` varchar(10) default NULL,
850 `record_sequence` int(11) NOT NULL default 0,
851 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
852 `import_date` DATE default NULL,
853 `marc` longblob NOT NULL,
854 `marcxml` longtext NOT NULL,
855 `marcxml_old` longtext NOT NULL,
856 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
857 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
858 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
859 `import_error` mediumtext,
860 `encoding` varchar(40) NOT NULL default '',
861 `z3950random` varchar(40) default NULL,
862 PRIMARY KEY (`import_record_id`),
863 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
864 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
865 KEY `branchcode` (`branchcode`),
866 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
867 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
870 -- Table structure for `import_record_matches`
872 DROP TABLE IF EXISTS `import_record_matches`;
873 CREATE TABLE `import_record_matches` (
874 `import_record_id` int(11) NOT NULL,
875 `candidate_match_id` int(11) NOT NULL,
876 `score` int(11) NOT NULL default 0,
877 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
878 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
879 KEY `record_score` (`import_record_id`, `score`)
880 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
883 -- Table structure for table `import_biblios`
886 DROP TABLE IF EXISTS `import_biblios`;
887 CREATE TABLE `import_biblios` (
888 `import_record_id` int(11) NOT NULL,
889 `matched_biblionumber` int(11) default NULL,
890 `control_number` varchar(25) default NULL,
891 `original_source` varchar(25) default NULL,
892 `title` varchar(128) default NULL,
893 `author` varchar(80) default NULL,
894 `isbn` varchar(30) default NULL,
895 `issn` varchar(9) default NULL,
896 `has_items` tinyint(1) NOT NULL default 0,
897 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
898 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
899 KEY `matched_biblionumber` (`matched_biblionumber`),
900 KEY `title` (`title`),
902 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
905 -- Table structure for table `import_items`
908 DROP TABLE IF EXISTS `import_items`;
909 CREATE TABLE `import_items` (
910 `import_items_id` int(11) NOT NULL auto_increment,
911 `import_record_id` int(11) NOT NULL,
912 `itemnumber` int(11) default NULL,
913 `branchcode` varchar(10) default NULL,
914 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
915 `marcxml` longtext NOT NULL,
916 `import_error` mediumtext,
917 PRIMARY KEY (`import_items_id`),
918 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
919 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
920 KEY `itemnumber` (`itemnumber`),
921 KEY `branchcode` (`branchcode`)
922 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
925 -- Table structure for table `issues`
928 DROP TABLE IF EXISTS `issues`;
929 CREATE TABLE `issues` (
930 `borrowernumber` int(11) default NULL,
931 `itemnumber` int(11) default NULL,
932 `date_due` date default NULL,
933 `branchcode` varchar(10) default NULL,
934 `issuingbranch` varchar(18) default NULL,
935 `returndate` date default NULL,
936 `lastreneweddate` date default NULL,
937 `return` varchar(4) default NULL,
938 `renewals` tinyint(4) default NULL,
939 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
940 `issuedate` date default NULL,
941 KEY `issuesborridx` (`borrowernumber`),
942 KEY `issuesitemidx` (`itemnumber`),
943 KEY `bordate` (`borrowernumber`,`timestamp`),
944 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
945 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
946 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
949 -- Table structure for table `issuingrules`
952 DROP TABLE IF EXISTS `issuingrules`;
953 CREATE TABLE `issuingrules` (
954 `categorycode` varchar(10) NOT NULL default '',
955 `itemtype` varchar(10) NOT NULL default '',
956 `restrictedtype` tinyint(1) default NULL,
957 `rentaldiscount` decimal(28,6) default NULL,
958 `reservecharge` decimal(28,6) default NULL,
959 `fine` decimal(28,6) default NULL,
960 `finedays` int(11) default NULL,
961 `firstremind` int(11) default NULL,
962 `chargeperiod` int(11) default NULL,
963 `accountsent` int(11) default NULL,
964 `chargename` varchar(100) default NULL,
965 `maxissueqty` int(4) default NULL,
966 `issuelength` int(4) default NULL,
967 `renewalsallowed` smallint(6) NOT NULL default "0",
968 `reservesallowed` smallint(6) NOT NULL default "0",
969 `branchcode` varchar(10) NOT NULL default '',
970 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
971 KEY `categorycode` (`categorycode`),
972 KEY `itemtype` (`itemtype`)
973 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
976 -- Table structure for table `items`
979 DROP TABLE IF EXISTS `items`;
980 CREATE TABLE `items` (
981 `itemnumber` int(11) NOT NULL auto_increment,
982 `biblionumber` int(11) NOT NULL default 0,
983 `biblioitemnumber` int(11) NOT NULL default 0,
984 `barcode` varchar(20) default NULL,
985 `dateaccessioned` date default NULL,
986 `booksellerid` mediumtext default NULL,
987 `homebranch` varchar(10) default NULL,
988 `price` decimal(8,2) default NULL,
989 `replacementprice` decimal(8,2) default NULL,
990 `replacementpricedate` date default NULL,
991 `datelastborrowed` date default NULL,
992 `datelastseen` date default NULL,
993 `stack` tinyint(1) default NULL,
994 `notforloan` tinyint(1) NOT NULL default 0,
995 `damaged` tinyint(1) NOT NULL default 0,
996 `itemlost` tinyint(1) NOT NULL default 0,
997 `wthdrawn` tinyint(1) NOT NULL default 0,
998 `itemcallnumber` varchar(255) default NULL,
999 `issues` smallint(6) default NULL,
1000 `renewals` smallint(6) default NULL,
1001 `reserves` smallint(6) default NULL,
1002 `restricted` tinyint(1) default NULL,
1003 `itemnotes` mediumtext,
1004 `holdingbranch` varchar(10) default NULL,
1005 `paidfor` mediumtext,
1006 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1007 `location` varchar(80) default NULL,
1008 `permanent_location` varchar(80) default NULL,
1009 `onloan` date default NULL,
1010 `cn_source` varchar(10) default NULL,
1011 `cn_sort` varchar(30) default NULL,
1012 `ccode` varchar(10) default NULL,
1013 `materials` varchar(10) default NULL,
1014 `uri` varchar(255) default NULL,
1015 `itype` varchar(10) default NULL,
1016 `more_subfields_xml` longtext default NULL,
1017 `enumchron` varchar(80) default NULL,
1018 `copynumber` varchar(32) default NULL,
1019 `stocknumber` varchar(32) default NULL,
1020 PRIMARY KEY (`itemnumber`),
1021 UNIQUE KEY `itembarcodeidx` (`barcode`),
1022 UNIQUE KEY `itemstocknumberidx` (`stocknumber`),
1023 KEY `itembinoidx` (`biblioitemnumber`),
1024 KEY `itembibnoidx` (`biblionumber`),
1025 KEY `homebranch` (`homebranch`),
1026 KEY `holdingbranch` (`holdingbranch`),
1027 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1028 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1029 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1030 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1033 -- Table structure for table `itemtypes`
1036 DROP TABLE IF EXISTS `itemtypes`;
1037 CREATE TABLE `itemtypes` (
1038 `itemtype` varchar(10) NOT NULL default '',
1039 `description` mediumtext,
1040 `rentalcharge` double(16,4) default NULL,
1041 `notforloan` smallint(6) default NULL,
1042 `imageurl` varchar(200) default NULL,
1044 PRIMARY KEY (`itemtype`),
1045 UNIQUE KEY `itemtype` (`itemtype`)
1046 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1049 -- Table structure for table `creator_batches`
1052 DROP TABLE IF EXISTS `creator_batches`;
1053 SET @saved_cs_client = @@character_set_client;
1054 SET character_set_client = utf8;
1055 CREATE TABLE `creator_batches` (
1056 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1057 `batch_id` int(10) NOT NULL DEFAULT '1',
1058 `item_number` int(11) DEFAULT NULL,
1059 `borrower_number` int(11) DEFAULT NULL,
1060 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1061 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1062 `creator` char(15) NOT NULL DEFAULT 'Labels',
1063 PRIMARY KEY (`label_id`),
1064 KEY `branch_fk_constraint` (`branch_code`),
1065 KEY `item_fk_constraint` (`item_number`),
1066 KEY `borrower_fk_constraint` (`borrower_number`),
1067 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1068 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1069 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1070 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1073 -- Table structure for table `creator_images`
1076 DROP TABLE IF EXISTS `creator_images`;
1077 SET @saved_cs_client = @@character_set_client;
1078 SET character_set_client = utf8;
1079 CREATE TABLE `creator_images` (
1080 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1081 `imagefile` mediumblob,
1082 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1083 PRIMARY KEY (`image_id`),
1084 UNIQUE KEY `image_name_index` (`image_name`)
1085 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1088 -- Table structure for table `creator_layouts`
1091 DROP TABLE IF EXISTS `creator_layouts`;
1092 SET @saved_cs_client = @@character_set_client;
1093 SET character_set_client = utf8;
1094 CREATE TABLE `creator_layouts` (
1095 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1096 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1097 `start_label` int(2) NOT NULL DEFAULT '1',
1098 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1099 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1100 `guidebox` int(1) DEFAULT '0',
1101 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1102 `font_size` int(4) NOT NULL DEFAULT '10',
1103 `units` char(20) NOT NULL DEFAULT 'POINT',
1104 `callnum_split` int(1) DEFAULT '0',
1105 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1106 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1107 `layout_xml` text NOT NULL,
1108 `creator` char(15) NOT NULL DEFAULT 'Labels',
1109 PRIMARY KEY (`layout_id`)
1110 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1113 -- Table structure for table `creator_templates`
1116 DROP TABLE IF EXISTS `creator_templates`;
1117 SET @saved_cs_client = @@character_set_client;
1118 SET character_set_client = utf8;
1119 CREATE TABLE `creator_templates` (
1120 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1121 `profile_id` int(4) DEFAULT NULL,
1122 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1123 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1124 `page_width` float NOT NULL DEFAULT '0',
1125 `page_height` float NOT NULL DEFAULT '0',
1126 `label_width` float NOT NULL DEFAULT '0',
1127 `label_height` float NOT NULL DEFAULT '0',
1128 `top_text_margin` float NOT NULL DEFAULT '0',
1129 `left_text_margin` float NOT NULL DEFAULT '0',
1130 `top_margin` float NOT NULL DEFAULT '0',
1131 `left_margin` float NOT NULL DEFAULT '0',
1132 `cols` int(2) NOT NULL DEFAULT '0',
1133 `rows` int(2) NOT NULL DEFAULT '0',
1134 `col_gap` float NOT NULL DEFAULT '0',
1135 `row_gap` float NOT NULL DEFAULT '0',
1136 `units` char(20) NOT NULL DEFAULT 'POINT',
1137 `creator` char(15) NOT NULL DEFAULT 'Labels',
1138 PRIMARY KEY (`template_id`),
1139 KEY `template_profile_fk_constraint` (`profile_id`)
1140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1143 -- Table structure for table `letter`
1146 DROP TABLE IF EXISTS `letter`;
1147 CREATE TABLE `letter` (
1148 `module` varchar(20) NOT NULL default '',
1149 `code` varchar(20) NOT NULL default '',
1150 `name` varchar(100) NOT NULL default '',
1151 `title` varchar(200) NOT NULL default '',
1153 PRIMARY KEY (`module`,`code`)
1154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1157 -- Table structure for table `marc_subfield_structure`
1160 DROP TABLE IF EXISTS `marc_subfield_structure`;
1161 CREATE TABLE `marc_subfield_structure` (
1162 `tagfield` varchar(3) NOT NULL default '',
1163 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1164 `liblibrarian` varchar(255) NOT NULL default '',
1165 `libopac` varchar(255) NOT NULL default '',
1166 `repeatable` tinyint(4) NOT NULL default 0,
1167 `mandatory` tinyint(4) NOT NULL default 0,
1168 `kohafield` varchar(40) default NULL,
1169 `tab` tinyint(1) default NULL,
1170 `authorised_value` varchar(20) default NULL,
1171 `authtypecode` varchar(20) default NULL,
1172 `value_builder` varchar(80) default NULL,
1173 `isurl` tinyint(1) default NULL,
1174 `hidden` tinyint(1) default NULL,
1175 `frameworkcode` varchar(4) NOT NULL default '',
1176 `seealso` varchar(1100) default NULL,
1177 `link` varchar(80) default NULL,
1178 `defaultvalue` text default NULL,
1179 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1180 KEY `kohafield_2` (`kohafield`),
1181 KEY `tab` (`frameworkcode`,`tab`),
1182 KEY `kohafield` (`frameworkcode`,`kohafield`)
1183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1186 -- Table structure for table `marc_tag_structure`
1189 DROP TABLE IF EXISTS `marc_tag_structure`;
1190 CREATE TABLE `marc_tag_structure` (
1191 `tagfield` varchar(3) NOT NULL default '',
1192 `liblibrarian` varchar(255) NOT NULL default '',
1193 `libopac` varchar(255) NOT NULL default '',
1194 `repeatable` tinyint(4) NOT NULL default 0,
1195 `mandatory` tinyint(4) NOT NULL default 0,
1196 `authorised_value` varchar(10) default NULL,
1197 `frameworkcode` varchar(4) NOT NULL default '',
1198 PRIMARY KEY (`frameworkcode`,`tagfield`)
1199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1202 -- Table structure for table `marc_matchers`
1205 DROP TABLE IF EXISTS `marc_matchers`;
1206 CREATE TABLE `marc_matchers` (
1207 `matcher_id` int(11) NOT NULL auto_increment,
1208 `code` varchar(10) NOT NULL default '',
1209 `description` varchar(255) NOT NULL default '',
1210 `record_type` varchar(10) NOT NULL default 'biblio',
1211 `threshold` int(11) NOT NULL default 0,
1212 PRIMARY KEY (`matcher_id`),
1213 KEY `code` (`code`),
1214 KEY `record_type` (`record_type`)
1215 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1218 -- Table structure for table `matchpoints`
1220 DROP TABLE IF EXISTS `matchpoints`;
1221 CREATE TABLE `matchpoints` (
1222 `matcher_id` int(11) NOT NULL,
1223 `matchpoint_id` int(11) NOT NULL auto_increment,
1224 `search_index` varchar(30) NOT NULL default '',
1225 `score` int(11) NOT NULL default 0,
1226 PRIMARY KEY (`matchpoint_id`),
1227 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1228 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1233 -- Table structure for table `matchpoint_components`
1235 DROP TABLE IF EXISTS `matchpoint_components`;
1236 CREATE TABLE `matchpoint_components` (
1237 `matchpoint_id` int(11) NOT NULL,
1238 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1239 sequence int(11) NOT NULL default 0,
1240 tag varchar(3) NOT NULL default '',
1241 subfields varchar(40) NOT NULL default '',
1242 offset int(4) NOT NULL default 0,
1243 length int(4) NOT NULL default 0,
1244 PRIMARY KEY (`matchpoint_component_id`),
1245 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1246 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1247 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1248 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1251 -- Table structure for table `matcher_component_norms`
1253 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1254 CREATE TABLE `matchpoint_component_norms` (
1255 `matchpoint_component_id` int(11) NOT NULL,
1256 `sequence` int(11) NOT NULL default 0,
1257 `norm_routine` varchar(50) NOT NULL default '',
1258 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1259 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1260 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1261 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1264 -- Table structure for table `matcher_matchpoints`
1266 DROP TABLE IF EXISTS `matcher_matchpoints`;
1267 CREATE TABLE `matcher_matchpoints` (
1268 `matcher_id` int(11) NOT NULL,
1269 `matchpoint_id` int(11) NOT NULL,
1270 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1271 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1272 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1273 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1277 -- Table structure for table `matchchecks`
1279 DROP TABLE IF EXISTS `matchchecks`;
1280 CREATE TABLE `matchchecks` (
1281 `matcher_id` int(11) NOT NULL,
1282 `matchcheck_id` int(11) NOT NULL auto_increment,
1283 `source_matchpoint_id` int(11) NOT NULL,
1284 `target_matchpoint_id` int(11) NOT NULL,
1285 PRIMARY KEY (`matchcheck_id`),
1286 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1287 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1288 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1289 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1290 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1291 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1292 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1295 -- Table structure for table `notifys`
1298 DROP TABLE IF EXISTS `notifys`;
1299 CREATE TABLE `notifys` (
1300 `notify_id` int(11) NOT NULL default 0,
1301 `borrowernumber` int(11) NOT NULL default 0,
1302 `itemnumber` int(11) NOT NULL default 0,
1303 `notify_date` date default NULL,
1304 `notify_send_date` date default NULL,
1305 `notify_level` int(1) NOT NULL default 0,
1306 `method` varchar(20) NOT NULL default ''
1307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1310 -- Table structure for table `nozebra`
1313 DROP TABLE IF EXISTS `nozebra`;
1314 CREATE TABLE `nozebra` (
1315 `server` varchar(20) NOT NULL,
1316 `indexname` varchar(40) NOT NULL,
1317 `value` varchar(250) NOT NULL,
1318 `biblionumbers` longtext NOT NULL,
1319 KEY `indexname` (`server`,`indexname`),
1320 KEY `value` (`server`,`value`))
1321 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1324 -- Table structure for table `old_issues`
1327 DROP TABLE IF EXISTS `old_issues`;
1328 CREATE TABLE `old_issues` (
1329 `borrowernumber` int(11) default NULL,
1330 `itemnumber` int(11) default NULL,
1331 `date_due` date default NULL,
1332 `branchcode` varchar(10) default NULL,
1333 `issuingbranch` varchar(18) default NULL,
1334 `returndate` date default NULL,
1335 `lastreneweddate` date default NULL,
1336 `return` varchar(4) default NULL,
1337 `renewals` tinyint(4) default NULL,
1338 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1339 `issuedate` date default NULL,
1340 KEY `old_issuesborridx` (`borrowernumber`),
1341 KEY `old_issuesitemidx` (`itemnumber`),
1342 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1343 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1344 ON DELETE SET NULL ON UPDATE SET NULL,
1345 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1346 ON DELETE SET NULL ON UPDATE SET NULL
1347 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1350 -- Table structure for table `old_reserves`
1352 DROP TABLE IF EXISTS `old_reserves`;
1353 CREATE TABLE `old_reserves` (
1354 `borrowernumber` int(11) default NULL,
1355 `reservedate` date default NULL,
1356 `biblionumber` int(11) default NULL,
1357 `constrainttype` varchar(1) default NULL,
1358 `branchcode` varchar(10) default NULL,
1359 `notificationdate` date default NULL,
1360 `reminderdate` date default NULL,
1361 `cancellationdate` date default NULL,
1362 `reservenotes` mediumtext,
1363 `priority` smallint(6) default NULL,
1364 `found` varchar(1) default NULL,
1365 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1366 `itemnumber` int(11) default NULL,
1367 `waitingdate` date default NULL,
1368 `expirationdate` DATE DEFAULT NULL,
1369 `lowestPriority` tinyint(1) NOT NULL,
1370 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1371 KEY `old_reserves_biblionumber` (`biblionumber`),
1372 KEY `old_reserves_itemnumber` (`itemnumber`),
1373 KEY `old_reserves_branchcode` (`branchcode`),
1374 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1375 ON DELETE SET NULL ON UPDATE SET NULL,
1376 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1377 ON DELETE SET NULL ON UPDATE SET NULL,
1378 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1379 ON DELETE SET NULL ON UPDATE SET NULL
1380 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1383 -- Table structure for table `opac_news`
1386 DROP TABLE IF EXISTS `opac_news`;
1387 CREATE TABLE `opac_news` (
1388 `idnew` int(10) unsigned NOT NULL auto_increment,
1389 `title` varchar(250) NOT NULL default '',
1390 `new` text NOT NULL,
1391 `lang` varchar(25) NOT NULL default '',
1392 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1393 `expirationdate` date default NULL,
1394 `number` int(11) default NULL,
1395 PRIMARY KEY (`idnew`)
1396 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1399 -- Table structure for table `overduerules`
1402 DROP TABLE IF EXISTS `overduerules`;
1403 CREATE TABLE `overduerules` (
1404 `branchcode` varchar(10) NOT NULL default '',
1405 `categorycode` varchar(10) NOT NULL default '',
1406 `delay1` int(4) default 0,
1407 `letter1` varchar(20) default NULL,
1408 `debarred1` varchar(1) default 0,
1409 `delay2` int(4) default 0,
1410 `debarred2` varchar(1) default 0,
1411 `letter2` varchar(20) default NULL,
1412 `delay3` int(4) default 0,
1413 `letter3` varchar(20) default NULL,
1414 `debarred3` int(1) default 0,
1415 PRIMARY KEY (`branchcode`,`categorycode`)
1416 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1419 -- Table structure for table `patroncards`
1422 DROP TABLE IF EXISTS `patroncards`;
1423 CREATE TABLE `patroncards` (
1424 `cardid` int(11) NOT NULL auto_increment,
1425 `batch_id` varchar(10) NOT NULL default '1',
1426 `borrowernumber` int(11) NOT NULL,
1427 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1428 PRIMARY KEY (`cardid`),
1429 KEY `patroncards_ibfk_1` (`borrowernumber`),
1430 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1431 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1434 -- Table structure for table `patronimage`
1437 DROP TABLE IF EXISTS `patronimage`;
1438 CREATE TABLE `patronimage` (
1439 `cardnumber` varchar(16) NOT NULL,
1440 `mimetype` varchar(15) NOT NULL,
1441 `imagefile` mediumblob NOT NULL,
1442 PRIMARY KEY (`cardnumber`),
1443 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1444 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1447 -- Table structure for table `printers`
1450 DROP TABLE IF EXISTS `printers`;
1451 CREATE TABLE `printers` (
1452 `printername` varchar(40) NOT NULL default '',
1453 `printqueue` varchar(20) default NULL,
1454 `printtype` varchar(20) default NULL,
1455 PRIMARY KEY (`printername`)
1456 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1459 -- Table structure for table `printers_profile`
1462 DROP TABLE IF EXISTS `printers_profile`;
1463 CREATE TABLE `printers_profile` (
1464 `profile_id` int(4) NOT NULL auto_increment,
1465 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1466 `template_id` int(4) NOT NULL default '0',
1467 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1468 `offset_horz` float NOT NULL default '0',
1469 `offset_vert` float NOT NULL default '0',
1470 `creep_horz` float NOT NULL default '0',
1471 `creep_vert` float NOT NULL default '0',
1472 `units` char(20) NOT NULL default 'POINT',
1473 `creator` char(15) NOT NULL DEFAULT 'Labels',
1474 PRIMARY KEY (`profile_id`),
1475 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
1476 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1479 -- Table structure for table `repeatable_holidays`
1482 DROP TABLE IF EXISTS `repeatable_holidays`;
1483 CREATE TABLE `repeatable_holidays` (
1484 `id` int(11) NOT NULL auto_increment,
1485 `branchcode` varchar(10) NOT NULL default '',
1486 `weekday` smallint(6) default NULL,
1487 `day` smallint(6) default NULL,
1488 `month` smallint(6) default NULL,
1489 `title` varchar(50) NOT NULL default '',
1490 `description` text NOT NULL,
1492 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1495 -- Table structure for table `reports_dictionary`
1498 DROP TABLE IF EXISTS `reports_dictionary`;
1499 CREATE TABLE reports_dictionary (
1500 `id` int(11) NOT NULL auto_increment,
1501 `name` varchar(255) default NULL,
1503 `date_created` datetime default NULL,
1504 `date_modified` datetime default NULL,
1506 `area` int(11) default NULL,
1508 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1511 -- Table structure for table `reserveconstraints`
1514 DROP TABLE IF EXISTS `reserveconstraints`;
1515 CREATE TABLE `reserveconstraints` (
1516 `borrowernumber` int(11) NOT NULL default 0,
1517 `reservedate` date default NULL,
1518 `biblionumber` int(11) NOT NULL default 0,
1519 `biblioitemnumber` int(11) default NULL,
1520 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1521 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1524 -- Table structure for table `reserves`
1527 DROP TABLE IF EXISTS `reserves`;
1528 CREATE TABLE `reserves` (
1529 `borrowernumber` int(11) NOT NULL default 0,
1530 `reservedate` date default NULL,
1531 `biblionumber` int(11) NOT NULL default 0,
1532 `constrainttype` varchar(1) default NULL,
1533 `branchcode` varchar(10) default NULL,
1534 `notificationdate` date default NULL,
1535 `reminderdate` date default NULL,
1536 `cancellationdate` date default NULL,
1537 `reservenotes` mediumtext,
1538 `priority` smallint(6) default NULL,
1539 `found` varchar(1) default NULL,
1540 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1541 `itemnumber` int(11) default NULL,
1542 `waitingdate` date default NULL,
1543 `expirationdate` DATE DEFAULT NULL,
1544 `lowestPriority` tinyint(1) NOT NULL,
1545 KEY `borrowernumber` (`borrowernumber`),
1546 KEY `biblionumber` (`biblionumber`),
1547 KEY `itemnumber` (`itemnumber`),
1548 KEY `branchcode` (`branchcode`),
1549 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1550 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1551 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1552 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1553 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1556 -- Table structure for table `reviews`
1559 DROP TABLE IF EXISTS `reviews`;
1560 CREATE TABLE `reviews` (
1561 `reviewid` int(11) NOT NULL auto_increment,
1562 `borrowernumber` int(11) default NULL,
1563 `biblionumber` int(11) default NULL,
1565 `approved` tinyint(4) default NULL,
1566 `datereviewed` datetime default NULL,
1567 PRIMARY KEY (`reviewid`)
1568 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1571 -- Table structure for table `roadtype`
1574 DROP TABLE IF EXISTS `roadtype`;
1575 CREATE TABLE `roadtype` (
1576 `roadtypeid` int(11) NOT NULL auto_increment,
1577 `road_type` varchar(100) NOT NULL default '',
1578 PRIMARY KEY (`roadtypeid`)
1579 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1582 -- Table structure for table `saved_sql`
1585 DROP TABLE IF EXISTS `saved_sql`;
1586 CREATE TABLE saved_sql (
1587 `id` int(11) NOT NULL auto_increment,
1588 `borrowernumber` int(11) default NULL,
1589 `date_created` datetime default NULL,
1590 `last_modified` datetime default NULL,
1592 `last_run` datetime default NULL,
1593 `report_name` varchar(255) default NULL,
1594 `type` varchar(255) default NULL,
1597 KEY boridx (`borrowernumber`)
1598 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1602 -- Table structure for `saved_reports`
1605 DROP TABLE IF EXISTS `saved_reports`;
1606 CREATE TABLE saved_reports (
1607 `id` int(11) NOT NULL auto_increment,
1608 `report_id` int(11) default NULL,
1610 `date_run` datetime default NULL,
1612 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1616 -- Table structure for table `search_history`
1619 DROP TABLE IF EXISTS `search_history`;
1620 CREATE TABLE IF NOT EXISTS `search_history` (
1621 `userid` int(11) NOT NULL,
1622 `sessionid` varchar(32) NOT NULL,
1623 `query_desc` varchar(255) NOT NULL,
1624 `query_cgi` varchar(255) NOT NULL,
1625 `total` int(11) NOT NULL,
1626 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1627 KEY `userid` (`userid`),
1628 KEY `sessionid` (`sessionid`)
1629 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1633 -- Table structure for table `serial`
1636 DROP TABLE IF EXISTS `serial`;
1637 CREATE TABLE `serial` (
1638 `serialid` int(11) NOT NULL auto_increment,
1639 `biblionumber` varchar(100) NOT NULL default '',
1640 `subscriptionid` varchar(100) NOT NULL default '',
1641 `serialseq` varchar(100) NOT NULL default '',
1642 `status` tinyint(4) NOT NULL default 0,
1643 `planneddate` date default NULL,
1645 `publisheddate` date default NULL,
1646 `itemnumber` text default NULL,
1647 `claimdate` date default NULL,
1648 `routingnotes` text,
1649 PRIMARY KEY (`serialid`)
1650 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1653 -- Table structure for table `sessions`
1656 DROP TABLE IF EXISTS sessions;
1657 CREATE TABLE sessions (
1658 `id` varchar(32) NOT NULL,
1659 `a_session` text NOT NULL,
1661 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1664 -- Table structure for table `special_holidays`
1667 DROP TABLE IF EXISTS `special_holidays`;
1668 CREATE TABLE `special_holidays` (
1669 `id` int(11) NOT NULL auto_increment,
1670 `branchcode` varchar(10) NOT NULL default '',
1671 `day` smallint(6) NOT NULL default 0,
1672 `month` smallint(6) NOT NULL default 0,
1673 `year` smallint(6) NOT NULL default 0,
1674 `isexception` smallint(1) NOT NULL default 1,
1675 `title` varchar(50) NOT NULL default '',
1676 `description` text NOT NULL,
1678 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1681 -- Table structure for table `statistics`
1684 DROP TABLE IF EXISTS `statistics`;
1685 CREATE TABLE `statistics` (
1686 `datetime` datetime default NULL,
1687 `branch` varchar(10) default NULL,
1688 `proccode` varchar(4) default NULL,
1689 `value` double(16,4) default NULL,
1690 `type` varchar(16) default NULL,
1692 `usercode` varchar(10) default NULL,
1693 `itemnumber` int(11) default NULL,
1694 `itemtype` varchar(10) default NULL,
1695 `borrowernumber` int(11) default NULL,
1696 `associatedborrower` int(11) default NULL,
1697 KEY `timeidx` (`datetime`)
1698 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1701 -- Table structure for table `stopwords`
1704 DROP TABLE IF EXISTS `stopwords`;
1705 CREATE TABLE `stopwords` (
1706 `word` varchar(255) default NULL
1707 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1710 -- Table structure for table `subscription`
1713 DROP TABLE IF EXISTS `subscription`;
1714 CREATE TABLE `subscription` (
1715 `biblionumber` int(11) NOT NULL default 0,
1716 `subscriptionid` int(11) NOT NULL auto_increment,
1717 `librarian` varchar(100) default '',
1718 `startdate` date default NULL,
1719 `aqbooksellerid` int(11) default 0,
1720 `cost` int(11) default 0,
1721 `aqbudgetid` int(11) default 0,
1722 `weeklength` int(11) default 0,
1723 `monthlength` int(11) default 0,
1724 `numberlength` int(11) default 0,
1725 `periodicity` tinyint(4) default 0,
1726 `dow` varchar(100) default '',
1727 `numberingmethod` varchar(100) default '',
1729 `status` varchar(100) NOT NULL default '',
1730 `add1` int(11) default 0,
1731 `every1` int(11) default 0,
1732 `whenmorethan1` int(11) default 0,
1733 `setto1` int(11) default NULL,
1734 `lastvalue1` int(11) default NULL,
1735 `add2` int(11) default 0,
1736 `every2` int(11) default 0,
1737 `whenmorethan2` int(11) default 0,
1738 `setto2` int(11) default NULL,
1739 `lastvalue2` int(11) default NULL,
1740 `add3` int(11) default 0,
1741 `every3` int(11) default 0,
1742 `innerloop1` int(11) default 0,
1743 `innerloop2` int(11) default 0,
1744 `innerloop3` int(11) default 0,
1745 `whenmorethan3` int(11) default 0,
1746 `setto3` int(11) default NULL,
1747 `lastvalue3` int(11) default NULL,
1748 `issuesatonce` tinyint(3) NOT NULL default 1,
1749 `firstacquidate` date default NULL,
1750 `manualhistory` tinyint(1) NOT NULL default 0,
1751 `irregularity` text,
1752 `letter` varchar(20) default NULL,
1753 `numberpattern` tinyint(3) default 0,
1754 `distributedto` text,
1755 `internalnotes` longtext,
1757 `location` varchar(80) NULL default '',
1758 `branchcode` varchar(10) NOT NULL default '',
1759 `hemisphere` tinyint(3) default 0,
1760 `lastbranch` varchar(10),
1761 `serialsadditems` tinyint(1) NOT NULL default '0',
1762 `staffdisplaycount` VARCHAR(10) NULL,
1763 `opacdisplaycount` VARCHAR(10) NULL,
1764 `graceperiod` int(11) NOT NULL default '0',
1765 `enddate` date default NULL,
1766 PRIMARY KEY (`subscriptionid`)
1767 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1770 -- Table structure for table `subscriptionhistory`
1773 DROP TABLE IF EXISTS `subscriptionhistory`;
1774 CREATE TABLE `subscriptionhistory` (
1775 `biblionumber` int(11) NOT NULL default 0,
1776 `subscriptionid` int(11) NOT NULL default 0,
1777 `histstartdate` date default NULL,
1778 `histenddate` date default NULL,
1779 `missinglist` longtext NOT NULL,
1780 `recievedlist` longtext NOT NULL,
1781 `opacnote` varchar(150) NOT NULL default '',
1782 `librariannote` varchar(150) NOT NULL default '',
1783 PRIMARY KEY (`subscriptionid`),
1784 KEY `biblionumber` (`biblionumber`)
1785 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1788 -- Table structure for table `subscriptionroutinglist`
1791 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1792 CREATE TABLE `subscriptionroutinglist` (
1793 `routingid` int(11) NOT NULL auto_increment,
1794 `borrowernumber` int(11) NOT NULL,
1795 `ranking` int(11) default NULL,
1796 `subscriptionid` int(11) NOT NULL,
1797 PRIMARY KEY (`routingid`),
1798 UNIQUE (`subscriptionid`, `borrowernumber`),
1799 CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1800 ON DELETE CASCADE ON UPDATE CASCADE,
1801 CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
1802 ON DELETE CASCADE ON UPDATE CASCADE
1803 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1806 -- Table structure for table `suggestions`
1809 DROP TABLE IF EXISTS `suggestions`;
1810 CREATE TABLE `suggestions` (
1811 `suggestionid` int(8) NOT NULL auto_increment,
1812 `suggestedby` int(11) NOT NULL default 0,
1813 `suggesteddate` date NOT NULL default 0,
1814 `managedby` int(11) default NULL,
1815 `manageddate` date default NULL,
1816 acceptedby INT(11) default NULL,
1817 accepteddate date default NULL,
1818 rejectedby INT(11) default NULL,
1819 rejecteddate date default NULL,
1820 `STATUS` varchar(10) NOT NULL default '',
1822 `author` varchar(80) default NULL,
1823 `title` varchar(80) default NULL,
1824 `copyrightdate` smallint(6) default NULL,
1825 `publishercode` varchar(255) default NULL,
1826 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1827 `volumedesc` varchar(255) default NULL,
1828 `publicationyear` smallint(6) default 0,
1829 `place` varchar(255) default NULL,
1830 `isbn` varchar(30) default NULL,
1831 `mailoverseeing` smallint(1) default 0,
1832 `biblionumber` int(11) default NULL,
1835 branchcode VARCHAR(10) default NULL,
1836 collectiontitle text default NULL,
1837 itemtype VARCHAR(30) default NULL,
1838 PRIMARY KEY (`suggestionid`),
1839 KEY `suggestedby` (`suggestedby`),
1840 KEY `managedby` (`managedby`)
1841 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1844 -- Table structure for table `systempreferences`
1847 DROP TABLE IF EXISTS `systempreferences`;
1848 CREATE TABLE `systempreferences` (
1849 `variable` varchar(50) NOT NULL default '',
1851 `options` mediumtext,
1853 `type` varchar(20) default NULL,
1854 PRIMARY KEY (`variable`)
1855 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1858 -- Table structure for table `tags`
1861 DROP TABLE IF EXISTS `tags`;
1862 CREATE TABLE `tags` (
1863 `entry` varchar(255) NOT NULL default '',
1864 `weight` bigint(20) NOT NULL default 0,
1865 PRIMARY KEY (`entry`)
1866 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1869 -- Table structure for table `tags_all`
1872 DROP TABLE IF EXISTS `tags_all`;
1873 CREATE TABLE `tags_all` (
1874 `tag_id` int(11) NOT NULL auto_increment,
1875 `borrowernumber` int(11) NOT NULL,
1876 `biblionumber` int(11) NOT NULL,
1877 `term` varchar(255) NOT NULL,
1878 `language` int(4) default NULL,
1879 `date_created` datetime NOT NULL,
1880 PRIMARY KEY (`tag_id`),
1881 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1882 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1883 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1884 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1885 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1886 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1887 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1890 -- Table structure for table `tags_approval`
1893 DROP TABLE IF EXISTS `tags_approval`;
1894 CREATE TABLE `tags_approval` (
1895 `term` varchar(255) NOT NULL,
1896 `approved` int(1) NOT NULL default '0',
1897 `date_approved` datetime default NULL,
1898 `approved_by` int(11) default NULL,
1899 `weight_total` int(9) NOT NULL default '1',
1900 PRIMARY KEY (`term`),
1901 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1902 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1903 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1904 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1907 -- Table structure for table `tags_index`
1910 DROP TABLE IF EXISTS `tags_index`;
1911 CREATE TABLE `tags_index` (
1912 `term` varchar(255) NOT NULL,
1913 `biblionumber` int(11) NOT NULL,
1914 `weight` int(9) NOT NULL default '1',
1915 PRIMARY KEY (`term`,`biblionumber`),
1916 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1917 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1918 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1919 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1920 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1921 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1924 -- Table structure for table `userflags`
1927 DROP TABLE IF EXISTS `userflags`;
1928 CREATE TABLE `userflags` (
1929 `bit` int(11) NOT NULL default 0,
1930 `flag` varchar(30) default NULL,
1931 `flagdesc` varchar(255) default NULL,
1932 `defaulton` int(11) default NULL,
1934 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1937 -- Table structure for table `virtualshelves`
1940 DROP TABLE IF EXISTS `virtualshelves`;
1941 CREATE TABLE `virtualshelves` (
1942 `shelfnumber` int(11) NOT NULL auto_increment,
1943 `shelfname` varchar(255) default NULL,
1944 `owner` varchar(80) default NULL,
1945 `category` varchar(1) default NULL,
1946 `sortfield` varchar(16) default NULL,
1947 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1948 PRIMARY KEY (`shelfnumber`)
1949 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1952 -- Table structure for table `virtualshelfcontents`
1955 DROP TABLE IF EXISTS `virtualshelfcontents`;
1956 CREATE TABLE `virtualshelfcontents` (
1957 `shelfnumber` int(11) NOT NULL default 0,
1958 `biblionumber` int(11) NOT NULL default 0,
1959 `flags` int(11) default NULL,
1960 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1961 KEY `shelfnumber` (`shelfnumber`),
1962 KEY `biblionumber` (`biblionumber`),
1963 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1964 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1965 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1968 -- Table structure for table `z3950servers`
1971 DROP TABLE IF EXISTS `z3950servers`;
1972 CREATE TABLE `z3950servers` (
1973 `host` varchar(255) default NULL,
1974 `port` int(11) default NULL,
1975 `db` varchar(255) default NULL,
1976 `userid` varchar(255) default NULL,
1977 `password` varchar(255) default NULL,
1979 `id` int(11) NOT NULL auto_increment,
1980 `checked` smallint(6) default NULL,
1981 `rank` int(11) default NULL,
1982 `syntax` varchar(80) default NULL,
1984 `position` enum('primary','secondary','') NOT NULL default 'primary',
1985 `type` enum('zed','opensearch') NOT NULL default 'zed',
1986 `encoding` text default NULL,
1987 `description` text NOT NULL,
1989 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1992 -- Table structure for table `zebraqueue`
1995 DROP TABLE IF EXISTS `zebraqueue`;
1996 CREATE TABLE `zebraqueue` (
1997 `id` int(11) NOT NULL auto_increment,
1998 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1999 `operation` char(20) NOT NULL default '',
2000 `server` char(20) NOT NULL default '',
2001 `done` int(11) NOT NULL default '0',
2002 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2004 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2005 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2007 DROP TABLE IF EXISTS `services_throttle`;
2008 CREATE TABLE `services_throttle` (
2009 `service_type` varchar(10) NOT NULL default '',
2010 `service_count` varchar(45) default NULL,
2011 PRIMARY KEY (`service_type`)
2012 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2014 -- http://www.w3.org/International/articles/language-tags/
2017 DROP TABLE IF EXISTS language_subtag_registry;
2018 CREATE TABLE language_subtag_registry (
2020 type varchar(25), -- language-script-region-variant-extension-privateuse
2021 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2023 id int(11) NOT NULL auto_increment,
2025 KEY `subtag` (`subtag`)
2026 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2028 -- TODO: add suppress_scripts
2029 -- this maps three letter codes defined in iso639.2 back to their
2030 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2031 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2032 CREATE TABLE language_rfc4646_to_iso639 (
2033 rfc4646_subtag varchar(25),
2034 iso639_2_code varchar(25),
2035 id int(11) NOT NULL auto_increment,
2037 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2038 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2040 DROP TABLE IF EXISTS language_descriptions;
2041 CREATE TABLE language_descriptions (
2045 description varchar(255),
2046 id int(11) NOT NULL auto_increment,
2048 KEY `lang` (`lang`),
2049 KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
2050 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2052 -- bi-directional support, keyed by script subcode
2053 DROP TABLE IF EXISTS language_script_bidi;
2054 CREATE TABLE language_script_bidi (
2055 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2056 bidi varchar(3), -- rtl ltr
2057 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2058 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2060 -- TODO: need to map language subtags to script subtags for detection
2061 -- of bidi when script is not specified (like ar, he)
2062 DROP TABLE IF EXISTS language_script_mapping;
2063 CREATE TABLE language_script_mapping (
2064 language_subtag varchar(25),
2065 script_subtag varchar(25),
2066 KEY `language_subtag` (`language_subtag`)
2067 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2069 DROP TABLE IF EXISTS `permissions`;
2070 CREATE TABLE `permissions` (
2071 `module_bit` int(11) NOT NULL DEFAULT 0,
2072 `code` varchar(64) DEFAULT NULL,
2073 `description` varchar(255) DEFAULT NULL,
2074 PRIMARY KEY (`module_bit`, `code`),
2075 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2076 ON DELETE CASCADE ON UPDATE CASCADE
2077 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2079 DROP TABLE IF EXISTS `serialitems`;
2080 CREATE TABLE `serialitems` (
2081 `itemnumber` int(11) NOT NULL,
2082 `serialid` int(11) NOT NULL,
2083 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2084 KEY `serialitems_sfk_1` (`serialid`),
2085 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
2086 CONSTRAINT serialitems_sfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
2087 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2089 DROP TABLE IF EXISTS `user_permissions`;
2090 CREATE TABLE `user_permissions` (
2091 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2092 `module_bit` int(11) NOT NULL DEFAULT 0,
2093 `code` varchar(64) DEFAULT NULL,
2094 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2095 ON DELETE CASCADE ON UPDATE CASCADE,
2096 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2097 ON DELETE CASCADE ON UPDATE CASCADE
2098 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2101 -- Table structure for table `tmp_holdsqueue`
2104 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2105 CREATE TABLE `tmp_holdsqueue` (
2106 `biblionumber` int(11) default NULL,
2107 `itemnumber` int(11) default NULL,
2108 `barcode` varchar(20) default NULL,
2109 `surname` mediumtext NOT NULL,
2112 `borrowernumber` int(11) NOT NULL,
2113 `cardnumber` varchar(16) default NULL,
2114 `reservedate` date default NULL,
2116 `itemcallnumber` varchar(255) default NULL,
2117 `holdingbranch` varchar(10) default NULL,
2118 `pickbranch` varchar(10) default NULL,
2120 `item_level_request` tinyint(4) NOT NULL default 0
2121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2124 -- Table structure for table `message_queue`
2127 DROP TABLE IF EXISTS `message_queue`;
2128 CREATE TABLE `message_queue` (
2129 `message_id` int(11) NOT NULL auto_increment,
2130 `borrowernumber` int(11) default NULL,
2133 `metadata` text DEFAULT NULL,
2134 `letter_code` varchar(64) DEFAULT NULL,
2135 `message_transport_type` varchar(20) NOT NULL,
2136 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2137 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2138 `to_address` mediumtext,
2139 `from_address` mediumtext,
2140 `content_type` text,
2141 KEY `message_id` (`message_id`),
2142 KEY `borrowernumber` (`borrowernumber`),
2143 KEY `message_transport_type` (`message_transport_type`),
2144 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2145 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2149 -- Table structure for table `message_transport_types`
2152 DROP TABLE IF EXISTS `message_transport_types`;
2153 CREATE TABLE `message_transport_types` (
2154 `message_transport_type` varchar(20) NOT NULL,
2155 PRIMARY KEY (`message_transport_type`)
2156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2159 -- Table structure for table `message_attributes`
2162 DROP TABLE IF EXISTS `message_attributes`;
2163 CREATE TABLE `message_attributes` (
2164 `message_attribute_id` int(11) NOT NULL auto_increment,
2165 `message_name` varchar(40) NOT NULL default '',
2166 `takes_days` tinyint(1) NOT NULL default '0',
2167 PRIMARY KEY (`message_attribute_id`),
2168 UNIQUE KEY `message_name` (`message_name`)
2169 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2172 -- Table structure for table `message_transports`
2175 DROP TABLE IF EXISTS `message_transports`;
2176 CREATE TABLE `message_transports` (
2177 `message_attribute_id` int(11) NOT NULL,
2178 `message_transport_type` varchar(20) NOT NULL,
2179 `is_digest` tinyint(1) NOT NULL default '0',
2180 `letter_module` varchar(20) NOT NULL default '',
2181 `letter_code` varchar(20) NOT NULL default '',
2182 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2183 KEY `message_transport_type` (`message_transport_type`),
2184 KEY `letter_module` (`letter_module`,`letter_code`),
2185 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2186 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2187 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2188 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2191 -- Table structure for table `borrower_message_preferences`
2194 DROP TABLE IF EXISTS `borrower_message_preferences`;
2195 CREATE TABLE `borrower_message_preferences` (
2196 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2197 `borrowernumber` int(11) default NULL,
2198 `categorycode` varchar(10) default NULL,
2199 `message_attribute_id` int(11) default '0',
2200 `days_in_advance` int(11) default '0',
2201 `wants_digest` tinyint(1) NOT NULL default '0',
2202 PRIMARY KEY (`borrower_message_preference_id`),
2203 KEY `borrowernumber` (`borrowernumber`),
2204 KEY `categorycode` (`categorycode`),
2205 KEY `message_attribute_id` (`message_attribute_id`),
2206 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2207 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2208 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2212 -- Table structure for table `borrower_message_transport_preferences`
2215 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2216 CREATE TABLE `borrower_message_transport_preferences` (
2217 `borrower_message_preference_id` int(11) NOT NULL default '0',
2218 `message_transport_type` varchar(20) NOT NULL default '0',
2219 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2220 KEY `message_transport_type` (`message_transport_type`),
2221 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,
2222 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
2223 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2226 -- Table structure for the table branch_transfer_limits
2229 DROP TABLE IF EXISTS `branch_transfer_limits`;
2230 CREATE TABLE branch_transfer_limits (
2231 limitId int(8) NOT NULL auto_increment,
2232 toBranch varchar(10) NOT NULL,
2233 fromBranch varchar(10) NOT NULL,
2234 itemtype varchar(10) NULL,
2235 ccode varchar(10) NULL,
2236 PRIMARY KEY (limitId)
2237 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2240 -- Table structure for table `item_circulation_alert_preferences`
2243 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2244 CREATE TABLE `item_circulation_alert_preferences` (
2245 `id` int(11) NOT NULL auto_increment,
2246 `branchcode` varchar(10) NOT NULL,
2247 `categorycode` varchar(10) NOT NULL,
2248 `item_type` varchar(10) NOT NULL,
2249 `notification` varchar(16) NOT NULL,
2251 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2252 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2255 -- Table structure for table `messages`
2257 DROP TABLE IF EXISTS `messages`;
2258 CREATE TABLE `messages` (
2259 `message_id` int(11) NOT NULL auto_increment,
2260 `borrowernumber` int(11) NOT NULL,
2261 `branchcode` varchar(10) default NULL,
2262 `message_type` varchar(1) NOT NULL,
2263 `message` text NOT NULL,
2264 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2265 PRIMARY KEY (`message_id`)
2266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2269 -- Table structure for table `accountlines`
2272 DROP TABLE IF EXISTS `accountlines`;
2273 CREATE TABLE `accountlines` (
2274 `borrowernumber` int(11) NOT NULL default 0,
2275 `accountno` smallint(6) NOT NULL default 0,
2276 `itemnumber` int(11) default NULL,
2277 `date` date default NULL,
2278 `amount` decimal(28,6) default NULL,
2279 `description` mediumtext,
2280 `dispute` mediumtext,
2281 `accounttype` varchar(5) default NULL,
2282 `amountoutstanding` decimal(28,6) default NULL,
2283 `lastincrement` decimal(28,6) default NULL,
2284 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2285 `notify_id` int(11) NOT NULL default 0,
2286 `notify_level` int(2) NOT NULL default 0,
2287 KEY `acctsborridx` (`borrowernumber`),
2288 KEY `timeidx` (`timestamp`),
2289 KEY `itemnumber` (`itemnumber`),
2290 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2291 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2292 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2295 -- Table structure for table `accountoffsets`
2298 DROP TABLE IF EXISTS `accountoffsets`;
2299 CREATE TABLE `accountoffsets` (
2300 `borrowernumber` int(11) NOT NULL default 0,
2301 `accountno` smallint(6) NOT NULL default 0,
2302 `offsetaccount` smallint(6) NOT NULL default 0,
2303 `offsetamount` decimal(28,6) default NULL,
2304 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2305 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2306 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2309 -- Table structure for table `action_logs`
2312 DROP TABLE IF EXISTS `action_logs`;
2313 CREATE TABLE `action_logs` (
2314 `action_id` int(11) NOT NULL auto_increment,
2315 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2316 `user` int(11) NOT NULL default 0,
2319 `object` int(11) default NULL,
2321 PRIMARY KEY (`action_id`),
2322 KEY (`timestamp`,`user`)
2323 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2326 -- Table structure for table `alert`
2329 DROP TABLE IF EXISTS `alert`;
2330 CREATE TABLE `alert` (
2331 `alertid` int(11) NOT NULL auto_increment,
2332 `borrowernumber` int(11) NOT NULL default 0,
2333 `type` varchar(10) NOT NULL default '',
2334 `externalid` varchar(20) NOT NULL default '',
2335 PRIMARY KEY (`alertid`),
2336 KEY `borrowernumber` (`borrowernumber`),
2337 KEY `type` (`type`,`externalid`)
2338 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2341 -- Table structure for table `aqbasketgroups`
2344 DROP TABLE IF EXISTS `aqbasketgroups`;
2345 CREATE TABLE `aqbasketgroups` (
2346 `id` int(11) NOT NULL auto_increment,
2347 `name` varchar(50) default NULL,
2348 `closed` tinyint(1) default NULL,
2349 `booksellerid` int(11) NOT NULL,
2351 KEY `booksellerid` (`booksellerid`),
2352 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2353 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2356 -- Table structure for table `aqbasket`
2359 DROP TABLE IF EXISTS `aqbasket`;
2360 CREATE TABLE `aqbasket` (
2361 `basketno` int(11) NOT NULL auto_increment,
2362 `basketname` varchar(50) default NULL,
2364 `booksellernote` mediumtext,
2365 `contractnumber` int(11),
2366 `creationdate` date default NULL,
2367 `closedate` date default NULL,
2368 `booksellerid` int(11) NOT NULL default 1,
2369 `authorisedby` varchar(10) default NULL,
2370 `booksellerinvoicenumber` mediumtext,
2371 `basketgroupid` int(11),
2372 PRIMARY KEY (`basketno`),
2373 KEY `booksellerid` (`booksellerid`),
2374 KEY `basketgroupid` (`basketgroupid`),
2375 KEY `contractnumber` (`contractnumber`),
2376 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2377 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2378 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2382 -- Table structure for table `aqbooksellers`
2385 DROP TABLE IF EXISTS `aqbooksellers`;
2386 CREATE TABLE `aqbooksellers` (
2387 `id` int(11) NOT NULL auto_increment,
2388 `name` mediumtext NOT NULL,
2389 `address1` mediumtext,
2390 `address2` mediumtext,
2391 `address3` mediumtext,
2392 `address4` mediumtext,
2393 `phone` varchar(30) default NULL,
2394 `accountnumber` mediumtext,
2395 `othersupplier` mediumtext,
2396 `currency` varchar(3) NOT NULL default '',
2397 `booksellerfax` mediumtext,
2399 `bookselleremail` mediumtext,
2400 `booksellerurl` mediumtext,
2401 `contact` varchar(100) default NULL,
2402 `postal` mediumtext,
2403 `url` varchar(255) default NULL,
2404 `contpos` varchar(100) default NULL,
2405 `contphone` varchar(100) default NULL,
2406 `contfax` varchar(100) default NULL,
2407 `contaltphone` varchar(100) default NULL,
2408 `contemail` varchar(100) default NULL,
2409 `contnotes` mediumtext,
2410 `active` tinyint(4) default NULL,
2411 `listprice` varchar(10) default NULL,
2412 `invoiceprice` varchar(10) default NULL,
2413 `gstreg` tinyint(4) default NULL,
2414 `listincgst` tinyint(4) default NULL,
2415 `invoiceincgst` tinyint(4) default NULL,
2416 `gstrate` decimal(6,4) default NULL,
2417 `discount` float(6,4) default NULL,
2418 `fax` varchar(50) default NULL,
2420 KEY `listprice` (`listprice`),
2421 KEY `invoiceprice` (`invoiceprice`),
2422 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2423 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2424 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2427 -- Table structure for table `aqbudgets`
2430 DROP TABLE IF EXISTS `aqbudgets`;
2431 CREATE TABLE `aqbudgets` (
2432 `budget_id` int(11) NOT NULL auto_increment,
2433 `budget_parent_id` int(11) default NULL,
2434 `budget_code` varchar(30) default NULL,
2435 `budget_name` varchar(80) default NULL,
2436 `budget_branchcode` varchar(10) default NULL,
2437 `budget_amount` decimal(28,6) NULL default '0.00',
2438 `budget_encumb` decimal(28,6) NULL default '0.00',
2439 `budget_expend` decimal(28,6) NULL default '0.00',
2440 `budget_notes` mediumtext,
2441 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2442 `budget_period_id` int(11) default NULL,
2443 `sort1_authcat` varchar(80) default NULL,
2444 `sort2_authcat` varchar(80) default NULL,
2445 `budget_owner_id` int(11) default NULL,
2446 `budget_permission` int(1) default '0',
2447 PRIMARY KEY (`budget_id`)
2448 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2452 -- Table structure for table `aqbudgetperiods`
2456 DROP TABLE IF EXISTS `aqbudgetperiods`;
2457 CREATE TABLE `aqbudgetperiods` (
2458 `budget_period_id` int(11) NOT NULL auto_increment,
2459 `budget_period_startdate` date NOT NULL,
2460 `budget_period_enddate` date NOT NULL,
2461 `budget_period_active` tinyint(1) default '0',
2462 `budget_period_description` mediumtext,
2463 `budget_period_total` decimal(28,6),
2464 `budget_period_locked` tinyint(1) default NULL,
2465 `sort1_authcat` varchar(10) default NULL,
2466 `sort2_authcat` varchar(10) default NULL,
2467 PRIMARY KEY (`budget_period_id`)
2468 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2471 -- Table structure for table `aqbudgets_planning`
2474 DROP TABLE IF EXISTS `aqbudgets_planning`;
2475 CREATE TABLE `aqbudgets_planning` (
2476 `plan_id` int(11) NOT NULL auto_increment,
2477 `budget_id` int(11) NOT NULL,
2478 `budget_period_id` int(11) NOT NULL,
2479 `estimated_amount` decimal(28,6) default NULL,
2480 `authcat` varchar(30) NOT NULL,
2481 `authvalue` varchar(30) NOT NULL,
2482 `display` tinyint(1) DEFAULT 1,
2483 PRIMARY KEY (`plan_id`),
2484 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2485 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2488 -- Table structure for table 'aqcontract'
2491 DROP TABLE IF EXISTS `aqcontract`;
2492 CREATE TABLE `aqcontract` (
2493 `contractnumber` int(11) NOT NULL auto_increment,
2494 `contractstartdate` date default NULL,
2495 `contractenddate` date default NULL,
2496 `contractname` varchar(50) default NULL,
2497 `contractdescription` mediumtext,
2498 `booksellerid` int(11) not NULL,
2499 PRIMARY KEY (`contractnumber`),
2500 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2501 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2502 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2505 -- Table structure for table `aqorderdelivery`
2508 DROP TABLE IF EXISTS `aqorderdelivery`;
2509 CREATE TABLE `aqorderdelivery` (
2510 `ordernumber` date default NULL,
2511 `deliverynumber` smallint(6) NOT NULL default 0,
2512 `deliverydate` varchar(18) default NULL,
2513 `qtydelivered` smallint(6) default NULL,
2514 `deliverycomments` mediumtext
2515 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2518 -- Table structure for table `aqorders`
2521 DROP TABLE IF EXISTS `aqorders`;
2522 CREATE TABLE `aqorders` (
2523 `ordernumber` int(11) NOT NULL auto_increment,
2524 `biblionumber` int(11) default NULL,
2525 `entrydate` date default NULL,
2526 `quantity` smallint(6) default NULL,
2527 `currency` varchar(3) default NULL,
2528 `listprice` decimal(28,6) default NULL,
2529 `totalamount` decimal(28,6) default NULL,
2530 `datereceived` date default NULL,
2531 `booksellerinvoicenumber` mediumtext,
2532 `freight` decimal(28,6) default NULL,
2533 `unitprice` decimal(28,6) default NULL,
2534 `quantityreceived` smallint(6) NOT NULL default 0,
2535 `cancelledby` varchar(10) default NULL,
2536 `datecancellationprinted` date default NULL,
2538 `supplierreference` mediumtext,
2539 `purchaseordernumber` mediumtext,
2540 `subscription` tinyint(1) default NULL,
2541 `serialid` varchar(30) default NULL,
2542 `basketno` int(11) default NULL,
2543 `biblioitemnumber` int(11) default NULL,
2544 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2545 `rrp` decimal(13,2) default NULL,
2546 `ecost` decimal(13,2) default NULL,
2547 `gst` decimal(13,2) default NULL,
2548 `budget_id` int(11) NOT NULL,
2549 `budgetgroup_id` int(11) NOT NULL,
2550 `budgetdate` date default NULL,
2551 `sort1` varchar(80) default NULL,
2552 `sort2` varchar(80) default NULL,
2553 `sort1_authcat` varchar(10) default NULL,
2554 `sort2_authcat` varchar(10) default NULL,
2555 `uncertainprice` tinyint(1),
2556 PRIMARY KEY (`ordernumber`),
2557 KEY `basketno` (`basketno`),
2558 KEY `biblionumber` (`biblionumber`),
2559 KEY `budget_id` (`budget_id`),
2560 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2561 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2566 -- Table structure for table `aqorders_items`
2569 DROP TABLE IF EXISTS `aqorders_items`;
2570 CREATE TABLE `aqorders_items` (
2571 `ordernumber` int(11) NOT NULL,
2572 `itemnumber` int(11) NOT NULL,
2573 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2574 PRIMARY KEY (`itemnumber`),
2575 KEY `ordernumber` (`ordernumber`)
2576 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2579 -- Table structure for table `fieldmapping`
2582 DROP TABLE IF EXISTS `fieldmapping`;
2583 CREATE TABLE `fieldmapping` (
2584 `id` int(11) NOT NULL auto_increment,
2585 `field` varchar(255) NOT NULL,
2586 `frameworkcode` char(4) NOT NULL default '',
2587 `fieldcode` char(3) NOT NULL,
2588 `subfieldcode` char(1) NOT NULL,
2590 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2593 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2594 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2595 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2596 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2597 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2598 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2599 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2600 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;