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 `hidelostitems` tinyint(1) NOT NULL default '0'
424 `category_type` varchar(1) NOT NULL default 'A',
425 PRIMARY KEY (`categorycode`),
426 UNIQUE KEY `categorycode` (`categorycode`)
427 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
430 -- Table: collections
432 CREATE TABLE collections (
433 colId integer(11) NOT NULL auto_increment,
434 colTitle varchar(100) NOT NULL DEFAULT '',
435 colDesc text NOT NULL,
436 colBranchcode varchar(4) DEFAULT NULL comment 'branchcode for branch where item should be held.',
438 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
441 -- Table: collections_tracking
443 CREATE TABLE collections_tracking (
444 ctId integer(11) NOT NULL auto_increment,
445 colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId',
446 itemnumber integer(11) NOT NULL DEFAULT 0 comment 'items.itemnumber',
448 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
451 -- Table structure for table `borrower_branch_circ_rules`
454 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
455 CREATE TABLE `branch_borrower_circ_rules` (
456 `branchcode` VARCHAR(10) NOT NULL,
457 `categorycode` VARCHAR(10) NOT NULL,
458 `maxissueqty` int(4) default NULL,
459 PRIMARY KEY (`categorycode`, `branchcode`),
460 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
461 ON DELETE CASCADE ON UPDATE CASCADE,
462 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
463 ON DELETE CASCADE ON UPDATE CASCADE
464 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
467 -- Table structure for table `default_borrower_circ_rules`
470 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
471 CREATE TABLE `default_borrower_circ_rules` (
472 `categorycode` VARCHAR(10) NOT NULL,
473 `maxissueqty` int(4) default NULL,
474 PRIMARY KEY (`categorycode`),
475 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
476 ON DELETE CASCADE ON UPDATE CASCADE
477 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
480 -- Table structure for table `default_branch_circ_rules`
483 DROP TABLE IF EXISTS `default_branch_circ_rules`;
484 CREATE TABLE `default_branch_circ_rules` (
485 `branchcode` VARCHAR(10) NOT NULL,
486 `maxissueqty` int(4) default NULL,
487 `holdallowed` tinyint(1) default NULL,
488 PRIMARY KEY (`branchcode`),
489 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
490 ON DELETE CASCADE ON UPDATE CASCADE
491 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
494 -- Table structure for table `default_branch_item_rules`
496 DROP TABLE IF EXISTS `default_branch_item_rules`;
497 CREATE TABLE `default_branch_item_rules` (
498 `itemtype` varchar(10) NOT NULL,
499 `holdallowed` tinyint(1) default NULL,
500 PRIMARY KEY (`itemtype`),
501 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
502 ON DELETE CASCADE ON UPDATE CASCADE
503 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
506 -- Table structure for table `default_circ_rules`
509 DROP TABLE IF EXISTS `default_circ_rules`;
510 CREATE TABLE `default_circ_rules` (
511 `singleton` enum('singleton') NOT NULL default 'singleton',
512 `maxissueqty` int(4) default NULL,
513 `holdallowed` int(1) default NULL,
514 PRIMARY KEY (`singleton`)
515 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
518 -- Table structure for table `cities`
521 DROP TABLE IF EXISTS `cities`;
522 CREATE TABLE `cities` (
523 `cityid` int(11) NOT NULL auto_increment,
524 `city_name` varchar(100) NOT NULL default '',
525 `city_zipcode` varchar(20) default NULL,
526 PRIMARY KEY (`cityid`)
527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
530 -- Table structure for table `class_sort_rules`
533 DROP TABLE IF EXISTS `class_sort_rules`;
534 CREATE TABLE `class_sort_rules` (
535 `class_sort_rule` varchar(10) NOT NULL default '',
536 `description` mediumtext,
537 `sort_routine` varchar(30) NOT NULL default '',
538 PRIMARY KEY (`class_sort_rule`),
539 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
540 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
543 -- Table structure for table `class_sources`
546 DROP TABLE IF EXISTS `class_sources`;
547 CREATE TABLE `class_sources` (
548 `cn_source` varchar(10) NOT NULL default '',
549 `description` mediumtext,
550 `used` tinyint(4) NOT NULL default 0,
551 `class_sort_rule` varchar(10) NOT NULL default '',
552 PRIMARY KEY (`cn_source`),
553 UNIQUE KEY `cn_source_idx` (`cn_source`),
554 KEY `used_idx` (`used`),
555 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
556 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
559 -- Table structure for table `currency`
562 DROP TABLE IF EXISTS `currency`;
563 CREATE TABLE `currency` (
564 `currency` varchar(10) NOT NULL default '',
565 `symbol` varchar(5) default NULL,
566 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
567 `rate` float(15,5) default NULL,
568 `active` tinyint(1) default NULL,
569 PRIMARY KEY (`currency`)
570 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
573 -- Table structure for table `deletedbiblio`
576 DROP TABLE IF EXISTS `deletedbiblio`;
577 CREATE TABLE `deletedbiblio` (
578 `biblionumber` int(11) NOT NULL default 0,
579 `frameworkcode` varchar(4) NOT NULL default '',
582 `unititle` mediumtext,
584 `serial` tinyint(1) default NULL,
585 `seriestitle` mediumtext,
586 `copyrightdate` smallint(6) default NULL,
587 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
588 `datecreated` DATE NOT NULL,
589 `abstract` mediumtext,
590 PRIMARY KEY (`biblionumber`),
591 KEY `blbnoidx` (`biblionumber`)
592 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
595 -- Table structure for table `deletedbiblioitems`
598 DROP TABLE IF EXISTS `deletedbiblioitems`;
599 CREATE TABLE `deletedbiblioitems` (
600 `biblioitemnumber` int(11) NOT NULL default 0,
601 `biblionumber` int(11) NOT NULL default 0,
604 `itemtype` varchar(10) default NULL,
605 `isbn` varchar(30) default NULL,
606 `issn` varchar(9) default NULL,
607 `publicationyear` text,
608 `publishercode` varchar(255) default NULL,
609 `volumedate` date default NULL,
611 `collectiontitle` mediumtext default NULL,
612 `collectionissn` text default NULL,
613 `collectionvolume` mediumtext default NULL,
614 `editionstatement` text default NULL,
615 `editionresponsibility` text default NULL,
616 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
617 `illus` varchar(255) default NULL,
618 `pages` varchar(255) default NULL,
620 `size` varchar(255) default NULL,
621 `place` varchar(255) default NULL,
622 `lccn` varchar(25) default NULL,
624 `url` varchar(255) default NULL,
625 `cn_source` varchar(10) default NULL,
626 `cn_class` varchar(30) default NULL,
627 `cn_item` varchar(10) default NULL,
628 `cn_suffix` varchar(10) default NULL,
629 `cn_sort` varchar(30) default NULL,
630 `totalissues` int(10),
631 `marcxml` longtext NOT NULL,
632 PRIMARY KEY (`biblioitemnumber`),
633 KEY `bibinoidx` (`biblioitemnumber`),
634 KEY `bibnoidx` (`biblionumber`),
636 KEY `publishercode` (`publishercode`)
637 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
640 -- Table structure for table `deletedborrowers`
643 DROP TABLE IF EXISTS `deletedborrowers`;
644 CREATE TABLE `deletedborrowers` (
645 `borrowernumber` int(11) NOT NULL default 0,
646 `cardnumber` varchar(16) NOT NULL default '',
647 `surname` mediumtext NOT NULL,
650 `othernames` mediumtext,
652 `streetnumber` varchar(10) default NULL,
653 `streettype` varchar(50) default NULL,
654 `address` mediumtext NOT NULL,
656 `city` mediumtext NOT NULL,
657 `zipcode` varchar(25) default NULL,
661 `mobile` varchar(50) default NULL,
665 `B_streetnumber` varchar(10) default NULL,
666 `B_streettype` varchar(50) default NULL,
667 `B_address` varchar(100) default NULL,
668 `B_address2` text default NULL,
670 `B_zipcode` varchar(25) default NULL,
673 `B_phone` mediumtext,
674 `dateofbirth` date default NULL,
675 `branchcode` varchar(10) NOT NULL default '',
676 `categorycode` varchar(10) default NULL,
677 `dateenrolled` date default NULL,
678 `dateexpiry` date default NULL,
679 `gonenoaddress` tinyint(1) default NULL,
680 `lost` tinyint(1) default NULL,
681 `debarred` tinyint(1) default NULL,
682 `contactname` mediumtext,
683 `contactfirstname` text,
685 `guarantorid` int(11) default NULL,
686 `borrowernotes` mediumtext,
687 `relationship` varchar(100) default NULL,
688 `ethnicity` varchar(50) default NULL,
689 `ethnotes` varchar(255) default NULL,
690 `sex` varchar(1) default NULL,
691 `password` varchar(30) default NULL,
692 `flags` int(11) default NULL,
693 `userid` varchar(30) default NULL,
694 `opacnote` mediumtext,
695 `contactnote` varchar(255) default NULL,
696 `sort1` varchar(80) default NULL,
697 `sort2` varchar(80) default NULL,
698 `altcontactfirstname` varchar(255) default NULL,
699 `altcontactsurname` varchar(255) default NULL,
700 `altcontactaddress1` varchar(255) default NULL,
701 `altcontactaddress2` varchar(255) default NULL,
702 `altcontactaddress3` varchar(255) default NULL,
703 `altcontactzipcode` varchar(50) default NULL,
704 `altcontactcountry` text default NULL,
705 `altcontactphone` varchar(50) default NULL,
706 `smsalertnumber` varchar(50) default NULL,
707 `privacy` integer(11) DEFAULT '1' NOT NULL,
708 KEY `borrowernumber` (`borrowernumber`),
709 KEY `cardnumber` (`cardnumber`)
710 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
713 -- Table structure for table `deleteditems`
716 DROP TABLE IF EXISTS `deleteditems`;
717 CREATE TABLE `deleteditems` (
718 `itemnumber` int(11) NOT NULL default 0,
719 `biblionumber` int(11) NOT NULL default 0,
720 `biblioitemnumber` int(11) NOT NULL default 0,
721 `barcode` varchar(20) default NULL,
722 `dateaccessioned` date default NULL,
723 `booksellerid` mediumtext default NULL,
724 `homebranch` varchar(10) default NULL,
725 `price` decimal(8,2) default NULL,
726 `replacementprice` decimal(8,2) default NULL,
727 `replacementpricedate` date default NULL,
728 `datelastborrowed` date default NULL,
729 `datelastseen` date default NULL,
730 `stack` tinyint(1) default NULL,
731 `notforloan` tinyint(1) NOT NULL default 0,
732 `damaged` tinyint(1) NOT NULL default 0,
733 `itemlost` tinyint(1) NOT NULL default 0,
734 `wthdrawn` tinyint(1) NOT NULL default 0,
735 `itemcallnumber` varchar(255) default NULL,
736 `issues` smallint(6) default NULL,
737 `renewals` smallint(6) default NULL,
738 `reserves` smallint(6) default NULL,
739 `restricted` tinyint(1) default NULL,
740 `itemnotes` mediumtext,
741 `holdingbranch` varchar(10) default NULL,
742 `paidfor` mediumtext,
743 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
744 `location` varchar(80) default NULL,
745 `permanent_location` varchar(80) default NULL,
746 `onloan` date default NULL,
747 `cn_source` varchar(10) default NULL,
748 `cn_sort` varchar(30) default NULL,
749 `ccode` varchar(10) default NULL,
750 `materials` varchar(10) default NULL,
751 `uri` varchar(255) default NULL,
752 `itype` varchar(10) default NULL,
753 `more_subfields_xml` longtext default NULL,
754 `enumchron` text default NULL,
755 `copynumber` varchar(32) default NULL,
756 `stocknumber` varchar(32) default NULL,
758 PRIMARY KEY (`itemnumber`),
759 KEY `delitembarcodeidx` (`barcode`),
760 KEY `delitemstocknumberidx` (`stocknumber`),
761 KEY `delitembinoidx` (`biblioitemnumber`),
762 KEY `delitembibnoidx` (`biblionumber`),
763 KEY `delhomebranch` (`homebranch`),
764 KEY `delholdingbranch` (`holdingbranch`)
765 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
768 -- Table structure for table `ethnicity`
771 DROP TABLE IF EXISTS `ethnicity`;
772 CREATE TABLE `ethnicity` (
773 `code` varchar(10) NOT NULL default '',
774 `name` varchar(255) default NULL,
776 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
779 -- Table structure for table `export_format`
782 DROP TABLE IF EXISTS `export_format`;
783 CREATE TABLE `export_format` (
784 `export_format_id` int(11) NOT NULL auto_increment,
785 `profile` varchar(255) NOT NULL,
786 `description` mediumtext NOT NULL,
787 `marcfields` mediumtext NOT NULL,
788 `csv_separator` varchar(2) NOT NULL,
789 `field_separator` varchar(2) NOT NULL,
790 `subfield_separator` varchar(2) NOT NULL,
791 `encoding` varchar(255) NOT NULL,
792 PRIMARY KEY (`export_format_id`)
793 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
797 -- Table structure for table `hold_fill_targets`
800 DROP TABLE IF EXISTS `hold_fill_targets`;
801 CREATE TABLE hold_fill_targets (
802 `borrowernumber` int(11) NOT NULL,
803 `biblionumber` int(11) NOT NULL,
804 `itemnumber` int(11) NOT NULL,
805 `source_branchcode` varchar(10) default NULL,
806 `item_level_request` tinyint(4) NOT NULL default 0,
807 PRIMARY KEY `itemnumber` (`itemnumber`),
808 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
809 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
810 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
811 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
812 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
813 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
814 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
815 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
816 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
817 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
820 -- Table structure for table `import_batches`
823 DROP TABLE IF EXISTS `import_batches`;
824 CREATE TABLE `import_batches` (
825 `import_batch_id` int(11) NOT NULL auto_increment,
826 `matcher_id` int(11) default NULL,
827 `template_id` int(11) default NULL,
828 `branchcode` varchar(10) default NULL,
829 `num_biblios` int(11) NOT NULL default 0,
830 `num_items` int(11) NOT NULL default 0,
831 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
832 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
833 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
834 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
835 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
836 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
837 `file_name` varchar(100),
838 `comments` mediumtext,
839 PRIMARY KEY (`import_batch_id`),
840 KEY `branchcode` (`branchcode`)
841 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
844 -- Table structure for table `import_records`
847 DROP TABLE IF EXISTS `import_records`;
848 CREATE TABLE `import_records` (
849 `import_record_id` int(11) NOT NULL auto_increment,
850 `import_batch_id` int(11) NOT NULL,
851 `branchcode` varchar(10) default NULL,
852 `record_sequence` int(11) NOT NULL default 0,
853 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
854 `import_date` DATE default NULL,
855 `marc` longblob NOT NULL,
856 `marcxml` longtext NOT NULL,
857 `marcxml_old` longtext NOT NULL,
858 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
859 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
860 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
861 `import_error` mediumtext,
862 `encoding` varchar(40) NOT NULL default '',
863 `z3950random` varchar(40) default NULL,
864 PRIMARY KEY (`import_record_id`),
865 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
866 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
867 KEY `branchcode` (`branchcode`),
868 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
869 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
872 -- Table structure for `import_record_matches`
874 DROP TABLE IF EXISTS `import_record_matches`;
875 CREATE TABLE `import_record_matches` (
876 `import_record_id` int(11) NOT NULL,
877 `candidate_match_id` int(11) NOT NULL,
878 `score` int(11) NOT NULL default 0,
879 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
880 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
881 KEY `record_score` (`import_record_id`, `score`)
882 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
885 -- Table structure for table `import_biblios`
888 DROP TABLE IF EXISTS `import_biblios`;
889 CREATE TABLE `import_biblios` (
890 `import_record_id` int(11) NOT NULL,
891 `matched_biblionumber` int(11) default NULL,
892 `control_number` varchar(25) default NULL,
893 `original_source` varchar(25) default NULL,
894 `title` varchar(128) default NULL,
895 `author` varchar(80) default NULL,
896 `isbn` varchar(30) default NULL,
897 `issn` varchar(9) default NULL,
898 `has_items` tinyint(1) NOT NULL default 0,
899 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
900 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
901 KEY `matched_biblionumber` (`matched_biblionumber`),
902 KEY `title` (`title`),
904 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
907 -- Table structure for table `import_items`
910 DROP TABLE IF EXISTS `import_items`;
911 CREATE TABLE `import_items` (
912 `import_items_id` int(11) NOT NULL auto_increment,
913 `import_record_id` int(11) NOT NULL,
914 `itemnumber` int(11) default NULL,
915 `branchcode` varchar(10) default NULL,
916 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
917 `marcxml` longtext NOT NULL,
918 `import_error` mediumtext,
919 PRIMARY KEY (`import_items_id`),
920 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
921 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
922 KEY `itemnumber` (`itemnumber`),
923 KEY `branchcode` (`branchcode`)
924 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
927 -- Table structure for table `issues`
930 DROP TABLE IF EXISTS `issues`;
931 CREATE TABLE `issues` (
932 `borrowernumber` int(11) default NULL,
933 `itemnumber` int(11) default NULL,
934 `date_due` date default NULL,
935 `branchcode` varchar(10) default NULL,
936 `issuingbranch` varchar(18) default NULL,
937 `returndate` date default NULL,
938 `lastreneweddate` date default NULL,
939 `return` varchar(4) default NULL,
940 `renewals` tinyint(4) default NULL,
941 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
942 `issuedate` date default NULL,
943 KEY `issuesborridx` (`borrowernumber`),
944 KEY `issuesitemidx` (`itemnumber`),
945 KEY `bordate` (`borrowernumber`,`timestamp`),
946 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
947 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
948 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
951 -- Table structure for table `issuingrules`
954 DROP TABLE IF EXISTS `issuingrules`;
955 CREATE TABLE `issuingrules` (
956 `categorycode` varchar(10) NOT NULL default '',
957 `itemtype` varchar(10) NOT NULL default '',
958 `restrictedtype` tinyint(1) default NULL,
959 `rentaldiscount` decimal(28,6) default NULL,
960 `reservecharge` decimal(28,6) default NULL,
961 `fine` decimal(28,6) default NULL,
962 `finedays` int(11) default NULL,
963 `firstremind` int(11) default NULL,
964 `chargeperiod` int(11) default NULL,
965 `accountsent` int(11) default NULL,
966 `chargename` varchar(100) default NULL,
967 `maxissueqty` int(4) default NULL,
968 `issuelength` int(4) default NULL,
969 `renewalsallowed` smallint(6) NOT NULL default "0",
970 `reservesallowed` smallint(6) NOT NULL default "0",
971 `branchcode` varchar(10) NOT NULL default '',
972 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
973 KEY `categorycode` (`categorycode`),
974 KEY `itemtype` (`itemtype`)
975 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
978 -- Table structure for table `items`
981 DROP TABLE IF EXISTS `items`;
982 CREATE TABLE `items` (
983 `itemnumber` int(11) NOT NULL auto_increment,
984 `biblionumber` int(11) NOT NULL default 0,
985 `biblioitemnumber` int(11) NOT NULL default 0,
986 `barcode` varchar(20) default NULL,
987 `dateaccessioned` date default NULL,
988 `booksellerid` mediumtext default NULL,
989 `homebranch` varchar(10) default NULL,
990 `price` decimal(8,2) default NULL,
991 `replacementprice` decimal(8,2) default NULL,
992 `replacementpricedate` date default NULL,
993 `datelastborrowed` date default NULL,
994 `datelastseen` date default NULL,
995 `stack` tinyint(1) default NULL,
996 `notforloan` tinyint(1) NOT NULL default 0,
997 `damaged` tinyint(1) NOT NULL default 0,
998 `itemlost` tinyint(1) NOT NULL default 0,
999 `wthdrawn` tinyint(1) NOT NULL default 0,
1000 `itemcallnumber` varchar(255) default NULL,
1001 `issues` smallint(6) default NULL,
1002 `renewals` smallint(6) default NULL,
1003 `reserves` smallint(6) default NULL,
1004 `restricted` tinyint(1) default NULL,
1005 `itemnotes` mediumtext,
1006 `holdingbranch` varchar(10) default NULL,
1007 `paidfor` mediumtext,
1008 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1009 `location` varchar(80) default NULL,
1010 `permanent_location` varchar(80) default NULL,
1011 `onloan` date default NULL,
1012 `cn_source` varchar(10) default NULL,
1013 `cn_sort` varchar(30) default NULL,
1014 `ccode` varchar(10) default NULL,
1015 `materials` varchar(10) default NULL,
1016 `uri` varchar(255) default NULL,
1017 `itype` varchar(10) default NULL,
1018 `more_subfields_xml` longtext default NULL,
1019 `enumchron` text default NULL,
1020 `copynumber` varchar(32) default NULL,
1021 `stocknumber` varchar(32) default NULL,
1022 PRIMARY KEY (`itemnumber`),
1023 UNIQUE KEY `itembarcodeidx` (`barcode`),
1024 UNIQUE KEY `itemstocknumberidx` (`stocknumber`),
1025 KEY `itembinoidx` (`biblioitemnumber`),
1026 KEY `itembibnoidx` (`biblionumber`),
1027 KEY `homebranch` (`homebranch`),
1028 KEY `holdingbranch` (`holdingbranch`),
1029 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1030 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1031 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1032 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1035 -- Table structure for table `itemtypes`
1038 DROP TABLE IF EXISTS `itemtypes`;
1039 CREATE TABLE `itemtypes` (
1040 `itemtype` varchar(10) NOT NULL default '',
1041 `description` mediumtext,
1042 `rentalcharge` double(16,4) default NULL,
1043 `notforloan` smallint(6) default NULL,
1044 `imageurl` varchar(200) default NULL,
1046 PRIMARY KEY (`itemtype`),
1047 UNIQUE KEY `itemtype` (`itemtype`)
1048 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1051 -- Table structure for table `creator_batches`
1054 DROP TABLE IF EXISTS `creator_batches`;
1055 SET @saved_cs_client = @@character_set_client;
1056 SET character_set_client = utf8;
1057 CREATE TABLE `creator_batches` (
1058 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1059 `batch_id` int(10) NOT NULL DEFAULT '1',
1060 `item_number` int(11) DEFAULT NULL,
1061 `borrower_number` int(11) DEFAULT NULL,
1062 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1063 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1064 `creator` char(15) NOT NULL DEFAULT 'Labels',
1065 PRIMARY KEY (`label_id`),
1066 KEY `branch_fk_constraint` (`branch_code`),
1067 KEY `item_fk_constraint` (`item_number`),
1068 KEY `borrower_fk_constraint` (`borrower_number`),
1069 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1070 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1071 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1072 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1075 -- Table structure for table `creator_images`
1078 DROP TABLE IF EXISTS `creator_images`;
1079 SET @saved_cs_client = @@character_set_client;
1080 SET character_set_client = utf8;
1081 CREATE TABLE `creator_images` (
1082 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1083 `imagefile` mediumblob,
1084 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1085 PRIMARY KEY (`image_id`),
1086 UNIQUE KEY `image_name_index` (`image_name`)
1087 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1090 -- Table structure for table `creator_layouts`
1093 DROP TABLE IF EXISTS `creator_layouts`;
1094 SET @saved_cs_client = @@character_set_client;
1095 SET character_set_client = utf8;
1096 CREATE TABLE `creator_layouts` (
1097 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1098 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1099 `start_label` int(2) NOT NULL DEFAULT '1',
1100 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1101 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1102 `guidebox` int(1) DEFAULT '0',
1103 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1104 `font_size` int(4) NOT NULL DEFAULT '10',
1105 `units` char(20) NOT NULL DEFAULT 'POINT',
1106 `callnum_split` int(1) DEFAULT '0',
1107 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1108 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1109 `layout_xml` text NOT NULL,
1110 `creator` char(15) NOT NULL DEFAULT 'Labels',
1111 PRIMARY KEY (`layout_id`)
1112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1115 -- Table structure for table `creator_templates`
1118 DROP TABLE IF EXISTS `creator_templates`;
1119 SET @saved_cs_client = @@character_set_client;
1120 SET character_set_client = utf8;
1121 CREATE TABLE `creator_templates` (
1122 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1123 `profile_id` int(4) DEFAULT NULL,
1124 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1125 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1126 `page_width` float NOT NULL DEFAULT '0',
1127 `page_height` float NOT NULL DEFAULT '0',
1128 `label_width` float NOT NULL DEFAULT '0',
1129 `label_height` float NOT NULL DEFAULT '0',
1130 `top_text_margin` float NOT NULL DEFAULT '0',
1131 `left_text_margin` float NOT NULL DEFAULT '0',
1132 `top_margin` float NOT NULL DEFAULT '0',
1133 `left_margin` float NOT NULL DEFAULT '0',
1134 `cols` int(2) NOT NULL DEFAULT '0',
1135 `rows` int(2) NOT NULL DEFAULT '0',
1136 `col_gap` float NOT NULL DEFAULT '0',
1137 `row_gap` float NOT NULL DEFAULT '0',
1138 `units` char(20) NOT NULL DEFAULT 'POINT',
1139 `creator` char(15) NOT NULL DEFAULT 'Labels',
1140 PRIMARY KEY (`template_id`),
1141 KEY `template_profile_fk_constraint` (`profile_id`)
1142 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1145 -- Table structure for table `letter`
1148 DROP TABLE IF EXISTS `letter`;
1149 CREATE TABLE `letter` (
1150 `module` varchar(20) NOT NULL default '',
1151 `code` varchar(20) NOT NULL default '',
1152 `name` varchar(100) NOT NULL default '',
1153 `title` varchar(200) NOT NULL default '',
1155 PRIMARY KEY (`module`,`code`)
1156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1159 -- Table structure for table `marc_subfield_structure`
1162 DROP TABLE IF EXISTS `marc_subfield_structure`;
1163 CREATE TABLE `marc_subfield_structure` (
1164 `tagfield` varchar(3) NOT NULL default '',
1165 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1166 `liblibrarian` varchar(255) NOT NULL default '',
1167 `libopac` varchar(255) NOT NULL default '',
1168 `repeatable` tinyint(4) NOT NULL default 0,
1169 `mandatory` tinyint(4) NOT NULL default 0,
1170 `kohafield` varchar(40) default NULL,
1171 `tab` tinyint(1) default NULL,
1172 `authorised_value` varchar(20) default NULL,
1173 `authtypecode` varchar(20) default NULL,
1174 `value_builder` varchar(80) default NULL,
1175 `isurl` tinyint(1) default NULL,
1176 `hidden` tinyint(1) default NULL,
1177 `frameworkcode` varchar(4) NOT NULL default '',
1178 `seealso` varchar(1100) default NULL,
1179 `link` varchar(80) default NULL,
1180 `defaultvalue` text default NULL,
1181 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1182 KEY `kohafield_2` (`kohafield`),
1183 KEY `tab` (`frameworkcode`,`tab`),
1184 KEY `kohafield` (`frameworkcode`,`kohafield`)
1185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1188 -- Table structure for table `marc_tag_structure`
1191 DROP TABLE IF EXISTS `marc_tag_structure`;
1192 CREATE TABLE `marc_tag_structure` (
1193 `tagfield` varchar(3) NOT NULL default '',
1194 `liblibrarian` varchar(255) NOT NULL default '',
1195 `libopac` varchar(255) NOT NULL default '',
1196 `repeatable` tinyint(4) NOT NULL default 0,
1197 `mandatory` tinyint(4) NOT NULL default 0,
1198 `authorised_value` varchar(10) default NULL,
1199 `frameworkcode` varchar(4) NOT NULL default '',
1200 PRIMARY KEY (`frameworkcode`,`tagfield`)
1201 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1204 -- Table structure for table `marc_matchers`
1207 DROP TABLE IF EXISTS `marc_matchers`;
1208 CREATE TABLE `marc_matchers` (
1209 `matcher_id` int(11) NOT NULL auto_increment,
1210 `code` varchar(10) NOT NULL default '',
1211 `description` varchar(255) NOT NULL default '',
1212 `record_type` varchar(10) NOT NULL default 'biblio',
1213 `threshold` int(11) NOT NULL default 0,
1214 PRIMARY KEY (`matcher_id`),
1215 KEY `code` (`code`),
1216 KEY `record_type` (`record_type`)
1217 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1220 -- Table structure for table `matchpoints`
1222 DROP TABLE IF EXISTS `matchpoints`;
1223 CREATE TABLE `matchpoints` (
1224 `matcher_id` int(11) NOT NULL,
1225 `matchpoint_id` int(11) NOT NULL auto_increment,
1226 `search_index` varchar(30) NOT NULL default '',
1227 `score` int(11) NOT NULL default 0,
1228 PRIMARY KEY (`matchpoint_id`),
1229 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1230 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1231 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1235 -- Table structure for table `matchpoint_components`
1237 DROP TABLE IF EXISTS `matchpoint_components`;
1238 CREATE TABLE `matchpoint_components` (
1239 `matchpoint_id` int(11) NOT NULL,
1240 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1241 sequence int(11) NOT NULL default 0,
1242 tag varchar(3) NOT NULL default '',
1243 subfields varchar(40) NOT NULL default '',
1244 offset int(4) NOT NULL default 0,
1245 length int(4) NOT NULL default 0,
1246 PRIMARY KEY (`matchpoint_component_id`),
1247 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1248 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1249 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1250 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1253 -- Table structure for table `matcher_component_norms`
1255 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1256 CREATE TABLE `matchpoint_component_norms` (
1257 `matchpoint_component_id` int(11) NOT NULL,
1258 `sequence` int(11) NOT NULL default 0,
1259 `norm_routine` varchar(50) NOT NULL default '',
1260 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1261 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1262 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1266 -- Table structure for table `matcher_matchpoints`
1268 DROP TABLE IF EXISTS `matcher_matchpoints`;
1269 CREATE TABLE `matcher_matchpoints` (
1270 `matcher_id` int(11) NOT NULL,
1271 `matchpoint_id` int(11) NOT NULL,
1272 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1273 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1274 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1275 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1276 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1279 -- Table structure for table `matchchecks`
1281 DROP TABLE IF EXISTS `matchchecks`;
1282 CREATE TABLE `matchchecks` (
1283 `matcher_id` int(11) NOT NULL,
1284 `matchcheck_id` int(11) NOT NULL auto_increment,
1285 `source_matchpoint_id` int(11) NOT NULL,
1286 `target_matchpoint_id` int(11) NOT NULL,
1287 PRIMARY KEY (`matchcheck_id`),
1288 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1289 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1290 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1291 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1292 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1293 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1294 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1297 -- Table structure for table `notifys`
1300 DROP TABLE IF EXISTS `notifys`;
1301 CREATE TABLE `notifys` (
1302 `notify_id` int(11) NOT NULL default 0,
1303 `borrowernumber` int(11) NOT NULL default 0,
1304 `itemnumber` int(11) NOT NULL default 0,
1305 `notify_date` date default NULL,
1306 `notify_send_date` date default NULL,
1307 `notify_level` int(1) NOT NULL default 0,
1308 `method` varchar(20) NOT NULL default ''
1309 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1312 -- Table structure for table `nozebra`
1315 DROP TABLE IF EXISTS `nozebra`;
1316 CREATE TABLE `nozebra` (
1317 `server` varchar(20) NOT NULL,
1318 `indexname` varchar(40) NOT NULL,
1319 `value` varchar(250) NOT NULL,
1320 `biblionumbers` longtext NOT NULL,
1321 KEY `indexname` (`server`,`indexname`),
1322 KEY `value` (`server`,`value`))
1323 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1326 -- Table structure for table `old_issues`
1329 DROP TABLE IF EXISTS `old_issues`;
1330 CREATE TABLE `old_issues` (
1331 `borrowernumber` int(11) default NULL,
1332 `itemnumber` int(11) default NULL,
1333 `date_due` date default NULL,
1334 `branchcode` varchar(10) default NULL,
1335 `issuingbranch` varchar(18) default NULL,
1336 `returndate` date default NULL,
1337 `lastreneweddate` date default NULL,
1338 `return` varchar(4) default NULL,
1339 `renewals` tinyint(4) default NULL,
1340 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1341 `issuedate` date default NULL,
1342 KEY `old_issuesborridx` (`borrowernumber`),
1343 KEY `old_issuesitemidx` (`itemnumber`),
1344 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1345 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1346 ON DELETE SET NULL ON UPDATE SET NULL,
1347 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1348 ON DELETE SET NULL ON UPDATE SET NULL
1349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1352 -- Table structure for table `old_reserves`
1354 DROP TABLE IF EXISTS `old_reserves`;
1355 CREATE TABLE `old_reserves` (
1356 `borrowernumber` int(11) default NULL,
1357 `reservedate` date default NULL,
1358 `biblionumber` int(11) default NULL,
1359 `constrainttype` varchar(1) default NULL,
1360 `branchcode` varchar(10) default NULL,
1361 `notificationdate` date default NULL,
1362 `reminderdate` date default NULL,
1363 `cancellationdate` date default NULL,
1364 `reservenotes` mediumtext,
1365 `priority` smallint(6) default NULL,
1366 `found` varchar(1) default NULL,
1367 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1368 `itemnumber` int(11) default NULL,
1369 `waitingdate` date default NULL,
1370 `expirationdate` DATE DEFAULT NULL,
1371 `lowestPriority` tinyint(1) NOT NULL,
1372 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1373 KEY `old_reserves_biblionumber` (`biblionumber`),
1374 KEY `old_reserves_itemnumber` (`itemnumber`),
1375 KEY `old_reserves_branchcode` (`branchcode`),
1376 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1377 ON DELETE SET NULL ON UPDATE SET NULL,
1378 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1379 ON DELETE SET NULL ON UPDATE SET NULL,
1380 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1381 ON DELETE SET NULL ON UPDATE SET NULL
1382 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1385 -- Table structure for table `opac_news`
1388 DROP TABLE IF EXISTS `opac_news`;
1389 CREATE TABLE `opac_news` (
1390 `idnew` int(10) unsigned NOT NULL auto_increment,
1391 `title` varchar(250) NOT NULL default '',
1392 `new` text NOT NULL,
1393 `lang` varchar(25) NOT NULL default '',
1394 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1395 `expirationdate` date default NULL,
1396 `number` int(11) default NULL,
1397 PRIMARY KEY (`idnew`)
1398 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1401 -- Table structure for table `overduerules`
1404 DROP TABLE IF EXISTS `overduerules`;
1405 CREATE TABLE `overduerules` (
1406 `branchcode` varchar(10) NOT NULL default '',
1407 `categorycode` varchar(10) NOT NULL default '',
1408 `delay1` int(4) default 0,
1409 `letter1` varchar(20) default NULL,
1410 `debarred1` varchar(1) default 0,
1411 `delay2` int(4) default 0,
1412 `debarred2` varchar(1) default 0,
1413 `letter2` varchar(20) default NULL,
1414 `delay3` int(4) default 0,
1415 `letter3` varchar(20) default NULL,
1416 `debarred3` int(1) default 0,
1417 PRIMARY KEY (`branchcode`,`categorycode`)
1418 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1421 -- Table structure for table `patroncards`
1424 DROP TABLE IF EXISTS `patroncards`;
1425 CREATE TABLE `patroncards` (
1426 `cardid` int(11) NOT NULL auto_increment,
1427 `batch_id` varchar(10) NOT NULL default '1',
1428 `borrowernumber` int(11) NOT NULL,
1429 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1430 PRIMARY KEY (`cardid`),
1431 KEY `patroncards_ibfk_1` (`borrowernumber`),
1432 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1433 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1436 -- Table structure for table `patronimage`
1439 DROP TABLE IF EXISTS `patronimage`;
1440 CREATE TABLE `patronimage` (
1441 `cardnumber` varchar(16) NOT NULL,
1442 `mimetype` varchar(15) NOT NULL,
1443 `imagefile` mediumblob NOT NULL,
1444 PRIMARY KEY (`cardnumber`),
1445 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1446 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1449 -- Table structure for table `printers`
1452 DROP TABLE IF EXISTS `printers`;
1453 CREATE TABLE `printers` (
1454 `printername` varchar(40) NOT NULL default '',
1455 `printqueue` varchar(20) default NULL,
1456 `printtype` varchar(20) default NULL,
1457 PRIMARY KEY (`printername`)
1458 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1461 -- Table structure for table `printers_profile`
1464 DROP TABLE IF EXISTS `printers_profile`;
1465 CREATE TABLE `printers_profile` (
1466 `profile_id` int(4) NOT NULL auto_increment,
1467 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1468 `template_id` int(4) NOT NULL default '0',
1469 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1470 `offset_horz` float NOT NULL default '0',
1471 `offset_vert` float NOT NULL default '0',
1472 `creep_horz` float NOT NULL default '0',
1473 `creep_vert` float NOT NULL default '0',
1474 `units` char(20) NOT NULL default 'POINT',
1475 `creator` char(15) NOT NULL DEFAULT 'Labels',
1476 PRIMARY KEY (`profile_id`),
1477 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
1478 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1481 -- Table structure for table `repeatable_holidays`
1484 DROP TABLE IF EXISTS `repeatable_holidays`;
1485 CREATE TABLE `repeatable_holidays` (
1486 `id` int(11) NOT NULL auto_increment,
1487 `branchcode` varchar(10) NOT NULL default '',
1488 `weekday` smallint(6) default NULL,
1489 `day` smallint(6) default NULL,
1490 `month` smallint(6) default NULL,
1491 `title` varchar(50) NOT NULL default '',
1492 `description` text NOT NULL,
1494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1497 -- Table structure for table `reports_dictionary`
1500 DROP TABLE IF EXISTS `reports_dictionary`;
1501 CREATE TABLE reports_dictionary (
1502 `id` int(11) NOT NULL auto_increment,
1503 `name` varchar(255) default NULL,
1505 `date_created` datetime default NULL,
1506 `date_modified` datetime default NULL,
1508 `area` int(11) default NULL,
1510 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1513 -- Table structure for table `reserveconstraints`
1516 DROP TABLE IF EXISTS `reserveconstraints`;
1517 CREATE TABLE `reserveconstraints` (
1518 `borrowernumber` int(11) NOT NULL default 0,
1519 `reservedate` date default NULL,
1520 `biblionumber` int(11) NOT NULL default 0,
1521 `biblioitemnumber` int(11) default NULL,
1522 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1523 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1526 -- Table structure for table `reserves`
1529 DROP TABLE IF EXISTS `reserves`;
1530 CREATE TABLE `reserves` (
1531 `borrowernumber` int(11) NOT NULL default 0,
1532 `reservedate` date default NULL,
1533 `biblionumber` int(11) NOT NULL default 0,
1534 `constrainttype` varchar(1) default NULL,
1535 `branchcode` varchar(10) default NULL,
1536 `notificationdate` date default NULL,
1537 `reminderdate` date default NULL,
1538 `cancellationdate` date default NULL,
1539 `reservenotes` mediumtext,
1540 `priority` smallint(6) default NULL,
1541 `found` varchar(1) default NULL,
1542 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1543 `itemnumber` int(11) default NULL,
1544 `waitingdate` date default NULL,
1545 `expirationdate` DATE DEFAULT NULL,
1546 `lowestPriority` tinyint(1) NOT NULL,
1547 KEY `borrowernumber` (`borrowernumber`),
1548 KEY `biblionumber` (`biblionumber`),
1549 KEY `itemnumber` (`itemnumber`),
1550 KEY `branchcode` (`branchcode`),
1551 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1552 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1553 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1554 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1555 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1558 -- Table structure for table `reviews`
1561 DROP TABLE IF EXISTS `reviews`;
1562 CREATE TABLE `reviews` (
1563 `reviewid` int(11) NOT NULL auto_increment,
1564 `borrowernumber` int(11) default NULL,
1565 `biblionumber` int(11) default NULL,
1567 `approved` tinyint(4) default NULL,
1568 `datereviewed` datetime default NULL,
1569 PRIMARY KEY (`reviewid`)
1570 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1573 -- Table structure for table `roadtype`
1576 DROP TABLE IF EXISTS `roadtype`;
1577 CREATE TABLE `roadtype` (
1578 `roadtypeid` int(11) NOT NULL auto_increment,
1579 `road_type` varchar(100) NOT NULL default '',
1580 PRIMARY KEY (`roadtypeid`)
1581 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1584 -- Table structure for table `saved_sql`
1587 DROP TABLE IF EXISTS `saved_sql`;
1588 CREATE TABLE saved_sql (
1589 `id` int(11) NOT NULL auto_increment,
1590 `borrowernumber` int(11) default NULL,
1591 `date_created` datetime default NULL,
1592 `last_modified` datetime default NULL,
1594 `last_run` datetime default NULL,
1595 `report_name` varchar(255) default NULL,
1596 `type` varchar(255) default NULL,
1599 KEY boridx (`borrowernumber`)
1600 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1604 -- Table structure for `saved_reports`
1607 DROP TABLE IF EXISTS `saved_reports`;
1608 CREATE TABLE saved_reports (
1609 `id` int(11) NOT NULL auto_increment,
1610 `report_id` int(11) default NULL,
1612 `date_run` datetime default NULL,
1614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1618 -- Table structure for table `search_history`
1621 DROP TABLE IF EXISTS `search_history`;
1622 CREATE TABLE IF NOT EXISTS `search_history` (
1623 `userid` int(11) NOT NULL,
1624 `sessionid` varchar(32) NOT NULL,
1625 `query_desc` varchar(255) NOT NULL,
1626 `query_cgi` varchar(255) NOT NULL,
1627 `total` int(11) NOT NULL,
1628 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1629 KEY `userid` (`userid`),
1630 KEY `sessionid` (`sessionid`)
1631 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1635 -- Table structure for table `serial`
1638 DROP TABLE IF EXISTS `serial`;
1639 CREATE TABLE `serial` (
1640 `serialid` int(11) NOT NULL auto_increment,
1641 `biblionumber` varchar(100) NOT NULL default '',
1642 `subscriptionid` varchar(100) NOT NULL default '',
1643 `serialseq` varchar(100) NOT NULL default '',
1644 `status` tinyint(4) NOT NULL default 0,
1645 `planneddate` date default NULL,
1647 `publisheddate` date default NULL,
1648 `itemnumber` text default NULL,
1649 `claimdate` date default NULL,
1650 `routingnotes` text,
1651 PRIMARY KEY (`serialid`)
1652 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1655 -- Table structure for table `sessions`
1658 DROP TABLE IF EXISTS sessions;
1659 CREATE TABLE sessions (
1660 `id` varchar(32) NOT NULL,
1661 `a_session` text NOT NULL,
1663 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1666 -- Table structure for table `special_holidays`
1669 DROP TABLE IF EXISTS `special_holidays`;
1670 CREATE TABLE `special_holidays` (
1671 `id` int(11) NOT NULL auto_increment,
1672 `branchcode` varchar(10) NOT NULL default '',
1673 `day` smallint(6) NOT NULL default 0,
1674 `month` smallint(6) NOT NULL default 0,
1675 `year` smallint(6) NOT NULL default 0,
1676 `isexception` smallint(1) NOT NULL default 1,
1677 `title` varchar(50) NOT NULL default '',
1678 `description` text NOT NULL,
1680 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1683 -- Table structure for table `statistics`
1686 DROP TABLE IF EXISTS `statistics`;
1687 CREATE TABLE `statistics` (
1688 `datetime` datetime default NULL,
1689 `branch` varchar(10) default NULL,
1690 `proccode` varchar(4) default NULL,
1691 `value` double(16,4) default NULL,
1692 `type` varchar(16) default NULL,
1694 `usercode` varchar(10) default NULL,
1695 `itemnumber` int(11) default NULL,
1696 `itemtype` varchar(10) default NULL,
1697 `borrowernumber` int(11) default NULL,
1698 `associatedborrower` int(11) default NULL,
1699 KEY `timeidx` (`datetime`)
1700 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1703 -- Table structure for table `stopwords`
1706 DROP TABLE IF EXISTS `stopwords`;
1707 CREATE TABLE `stopwords` (
1708 `word` varchar(255) default NULL
1709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1712 -- Table structure for table `subscription`
1715 DROP TABLE IF EXISTS `subscription`;
1716 CREATE TABLE `subscription` (
1717 `biblionumber` int(11) NOT NULL default 0,
1718 `subscriptionid` int(11) NOT NULL auto_increment,
1719 `librarian` varchar(100) default '',
1720 `startdate` date default NULL,
1721 `aqbooksellerid` int(11) default 0,
1722 `cost` int(11) default 0,
1723 `aqbudgetid` int(11) default 0,
1724 `weeklength` int(11) default 0,
1725 `monthlength` int(11) default 0,
1726 `numberlength` int(11) default 0,
1727 `periodicity` tinyint(4) default 0,
1728 `dow` varchar(100) default '',
1729 `numberingmethod` varchar(100) default '',
1731 `status` varchar(100) NOT NULL default '',
1732 `add1` int(11) default 0,
1733 `every1` int(11) default 0,
1734 `whenmorethan1` int(11) default 0,
1735 `setto1` int(11) default NULL,
1736 `lastvalue1` int(11) default NULL,
1737 `add2` int(11) default 0,
1738 `every2` int(11) default 0,
1739 `whenmorethan2` int(11) default 0,
1740 `setto2` int(11) default NULL,
1741 `lastvalue2` int(11) default NULL,
1742 `add3` int(11) default 0,
1743 `every3` int(11) default 0,
1744 `innerloop1` int(11) default 0,
1745 `innerloop2` int(11) default 0,
1746 `innerloop3` int(11) default 0,
1747 `whenmorethan3` int(11) default 0,
1748 `setto3` int(11) default NULL,
1749 `lastvalue3` int(11) default NULL,
1750 `issuesatonce` tinyint(3) NOT NULL default 1,
1751 `firstacquidate` date default NULL,
1752 `manualhistory` tinyint(1) NOT NULL default 0,
1753 `irregularity` text,
1754 `letter` varchar(20) default NULL,
1755 `numberpattern` tinyint(3) default 0,
1756 `distributedto` text,
1757 `internalnotes` longtext,
1759 `location` varchar(80) NULL default '',
1760 `branchcode` varchar(10) NOT NULL default '',
1761 `hemisphere` tinyint(3) default 0,
1762 `lastbranch` varchar(10),
1763 `serialsadditems` tinyint(1) NOT NULL default '0',
1764 `staffdisplaycount` VARCHAR(10) NULL,
1765 `opacdisplaycount` VARCHAR(10) NULL,
1766 `graceperiod` int(11) NOT NULL default '0',
1767 `enddate` date default NULL,
1768 PRIMARY KEY (`subscriptionid`)
1769 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1772 -- Table structure for table `subscriptionhistory`
1775 DROP TABLE IF EXISTS `subscriptionhistory`;
1776 CREATE TABLE `subscriptionhistory` (
1777 `biblionumber` int(11) NOT NULL default 0,
1778 `subscriptionid` int(11) NOT NULL default 0,
1779 `histstartdate` date default NULL,
1780 `histenddate` date default NULL,
1781 `missinglist` longtext NOT NULL,
1782 `recievedlist` longtext NOT NULL,
1783 `opacnote` varchar(150) NOT NULL default '',
1784 `librariannote` varchar(150) NOT NULL default '',
1785 PRIMARY KEY (`subscriptionid`),
1786 KEY `biblionumber` (`biblionumber`)
1787 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1790 -- Table structure for table `subscriptionroutinglist`
1793 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1794 CREATE TABLE `subscriptionroutinglist` (
1795 `routingid` int(11) NOT NULL auto_increment,
1796 `borrowernumber` int(11) NOT NULL,
1797 `ranking` int(11) default NULL,
1798 `subscriptionid` int(11) NOT NULL,
1799 PRIMARY KEY (`routingid`),
1800 UNIQUE (`subscriptionid`, `borrowernumber`),
1801 CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1802 ON DELETE CASCADE ON UPDATE CASCADE,
1803 CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
1804 ON DELETE CASCADE ON UPDATE CASCADE
1805 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1808 -- Table structure for table `suggestions`
1811 DROP TABLE IF EXISTS `suggestions`;
1812 CREATE TABLE `suggestions` (
1813 `suggestionid` int(8) NOT NULL auto_increment,
1814 `suggestedby` int(11) NOT NULL default 0,
1815 `suggesteddate` date NOT NULL default 0,
1816 `managedby` int(11) default NULL,
1817 `manageddate` date default NULL,
1818 acceptedby INT(11) default NULL,
1819 accepteddate date default NULL,
1820 rejectedby INT(11) default NULL,
1821 rejecteddate date default NULL,
1822 `STATUS` varchar(10) NOT NULL default '',
1824 `author` varchar(80) default NULL,
1825 `title` varchar(80) default NULL,
1826 `copyrightdate` smallint(6) default NULL,
1827 `publishercode` varchar(255) default NULL,
1828 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1829 `volumedesc` varchar(255) default NULL,
1830 `publicationyear` smallint(6) default 0,
1831 `place` varchar(255) default NULL,
1832 `isbn` varchar(30) default NULL,
1833 `mailoverseeing` smallint(1) default 0,
1834 `biblionumber` int(11) default NULL,
1837 branchcode VARCHAR(10) default NULL,
1838 collectiontitle text default NULL,
1839 itemtype VARCHAR(30) default NULL,
1840 quantity SMALLINT(6) default NULL,
1841 currency VARCHAR(3) default NULL,
1842 price DECIMAL(28,6) default NULL,
1843 total DECIMAL(28,6) default NULL,
1844 PRIMARY KEY (`suggestionid`),
1845 KEY `suggestedby` (`suggestedby`),
1846 KEY `managedby` (`managedby`)
1847 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1850 -- Table structure for table `systempreferences`
1853 DROP TABLE IF EXISTS `systempreferences`;
1854 CREATE TABLE `systempreferences` (
1855 `variable` varchar(50) NOT NULL default '',
1857 `options` mediumtext,
1859 `type` varchar(20) default NULL,
1860 PRIMARY KEY (`variable`)
1861 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1864 -- Table structure for table `tags`
1867 DROP TABLE IF EXISTS `tags`;
1868 CREATE TABLE `tags` (
1869 `entry` varchar(255) NOT NULL default '',
1870 `weight` bigint(20) NOT NULL default 0,
1871 PRIMARY KEY (`entry`)
1872 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1875 -- Table structure for table `tags_all`
1878 DROP TABLE IF EXISTS `tags_all`;
1879 CREATE TABLE `tags_all` (
1880 `tag_id` int(11) NOT NULL auto_increment,
1881 `borrowernumber` int(11) NOT NULL,
1882 `biblionumber` int(11) NOT NULL,
1883 `term` varchar(255) NOT NULL,
1884 `language` int(4) default NULL,
1885 `date_created` datetime NOT NULL,
1886 PRIMARY KEY (`tag_id`),
1887 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1888 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1889 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1890 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1891 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1892 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1893 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1896 -- Table structure for table `tags_approval`
1899 DROP TABLE IF EXISTS `tags_approval`;
1900 CREATE TABLE `tags_approval` (
1901 `term` varchar(255) NOT NULL,
1902 `approved` int(1) NOT NULL default '0',
1903 `date_approved` datetime default NULL,
1904 `approved_by` int(11) default NULL,
1905 `weight_total` int(9) NOT NULL default '1',
1906 PRIMARY KEY (`term`),
1907 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1908 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1909 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1910 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1913 -- Table structure for table `tags_index`
1916 DROP TABLE IF EXISTS `tags_index`;
1917 CREATE TABLE `tags_index` (
1918 `term` varchar(255) NOT NULL,
1919 `biblionumber` int(11) NOT NULL,
1920 `weight` int(9) NOT NULL default '1',
1921 PRIMARY KEY (`term`,`biblionumber`),
1922 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1923 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1924 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1925 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1926 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1927 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1930 -- Table structure for table `userflags`
1933 DROP TABLE IF EXISTS `userflags`;
1934 CREATE TABLE `userflags` (
1935 `bit` int(11) NOT NULL default 0,
1936 `flag` varchar(30) default NULL,
1937 `flagdesc` varchar(255) default NULL,
1938 `defaulton` int(11) default NULL,
1940 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1943 -- Table structure for table `virtualshelves`
1946 DROP TABLE IF EXISTS `virtualshelves`;
1947 CREATE TABLE `virtualshelves` (
1948 `shelfnumber` int(11) NOT NULL auto_increment,
1949 `shelfname` varchar(255) default NULL,
1950 `owner` varchar(80) default NULL,
1951 `category` varchar(1) default NULL,
1952 `sortfield` varchar(16) default NULL,
1953 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1954 PRIMARY KEY (`shelfnumber`)
1955 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1958 -- Table structure for table `virtualshelfcontents`
1961 DROP TABLE IF EXISTS `virtualshelfcontents`;
1962 CREATE TABLE `virtualshelfcontents` (
1963 `shelfnumber` int(11) NOT NULL default 0,
1964 `biblionumber` int(11) NOT NULL default 0,
1965 `flags` int(11) default NULL,
1966 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1967 KEY `shelfnumber` (`shelfnumber`),
1968 KEY `biblionumber` (`biblionumber`),
1969 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1970 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1971 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1974 -- Table structure for table `z3950servers`
1977 DROP TABLE IF EXISTS `z3950servers`;
1978 CREATE TABLE `z3950servers` (
1979 `host` varchar(255) default NULL,
1980 `port` int(11) default NULL,
1981 `db` varchar(255) default NULL,
1982 `userid` varchar(255) default NULL,
1983 `password` varchar(255) default NULL,
1985 `id` int(11) NOT NULL auto_increment,
1986 `checked` smallint(6) default NULL,
1987 `rank` int(11) default NULL,
1988 `syntax` varchar(80) default NULL,
1990 `position` enum('primary','secondary','') NOT NULL default 'primary',
1991 `type` enum('zed','opensearch') NOT NULL default 'zed',
1992 `encoding` text default NULL,
1993 `description` text NOT NULL,
1995 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1998 -- Table structure for table `zebraqueue`
2001 DROP TABLE IF EXISTS `zebraqueue`;
2002 CREATE TABLE `zebraqueue` (
2003 `id` int(11) NOT NULL auto_increment,
2004 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2005 `operation` char(20) NOT NULL default '',
2006 `server` char(20) NOT NULL default '',
2007 `done` int(11) NOT NULL default '0',
2008 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2010 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2011 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2013 DROP TABLE IF EXISTS `services_throttle`;
2014 CREATE TABLE `services_throttle` (
2015 `service_type` varchar(10) NOT NULL default '',
2016 `service_count` varchar(45) default NULL,
2017 PRIMARY KEY (`service_type`)
2018 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2020 -- http://www.w3.org/International/articles/language-tags/
2023 DROP TABLE IF EXISTS language_subtag_registry;
2024 CREATE TABLE language_subtag_registry (
2026 type varchar(25), -- language-script-region-variant-extension-privateuse
2027 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2029 id int(11) NOT NULL auto_increment,
2031 KEY `subtag` (`subtag`)
2032 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2034 -- TODO: add suppress_scripts
2035 -- this maps three letter codes defined in iso639.2 back to their
2036 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2037 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2038 CREATE TABLE language_rfc4646_to_iso639 (
2039 rfc4646_subtag varchar(25),
2040 iso639_2_code varchar(25),
2041 id int(11) NOT NULL auto_increment,
2043 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2044 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2046 DROP TABLE IF EXISTS language_descriptions;
2047 CREATE TABLE language_descriptions (
2051 description varchar(255),
2052 id int(11) NOT NULL auto_increment,
2054 KEY `lang` (`lang`),
2055 KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
2056 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2058 -- bi-directional support, keyed by script subcode
2059 DROP TABLE IF EXISTS language_script_bidi;
2060 CREATE TABLE language_script_bidi (
2061 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2062 bidi varchar(3), -- rtl ltr
2063 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2064 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2066 -- TODO: need to map language subtags to script subtags for detection
2067 -- of bidi when script is not specified (like ar, he)
2068 DROP TABLE IF EXISTS language_script_mapping;
2069 CREATE TABLE language_script_mapping (
2070 language_subtag varchar(25),
2071 script_subtag varchar(25),
2072 KEY `language_subtag` (`language_subtag`)
2073 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2075 DROP TABLE IF EXISTS `permissions`;
2076 CREATE TABLE `permissions` (
2077 `module_bit` int(11) NOT NULL DEFAULT 0,
2078 `code` varchar(64) DEFAULT NULL,
2079 `description` varchar(255) DEFAULT NULL,
2080 PRIMARY KEY (`module_bit`, `code`),
2081 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2082 ON DELETE CASCADE ON UPDATE CASCADE
2083 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2085 DROP TABLE IF EXISTS `serialitems`;
2086 CREATE TABLE `serialitems` (
2087 `itemnumber` int(11) NOT NULL,
2088 `serialid` int(11) NOT NULL,
2089 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2090 KEY `serialitems_sfk_1` (`serialid`),
2091 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
2092 CONSTRAINT serialitems_sfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
2093 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2095 DROP TABLE IF EXISTS `user_permissions`;
2096 CREATE TABLE `user_permissions` (
2097 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2098 `module_bit` int(11) NOT NULL DEFAULT 0,
2099 `code` varchar(64) DEFAULT NULL,
2100 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2101 ON DELETE CASCADE ON UPDATE CASCADE,
2102 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2103 ON DELETE CASCADE ON UPDATE CASCADE
2104 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2107 -- Table structure for table `tmp_holdsqueue`
2110 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2111 CREATE TABLE `tmp_holdsqueue` (
2112 `biblionumber` int(11) default NULL,
2113 `itemnumber` int(11) default NULL,
2114 `barcode` varchar(20) default NULL,
2115 `surname` mediumtext NOT NULL,
2118 `borrowernumber` int(11) NOT NULL,
2119 `cardnumber` varchar(16) default NULL,
2120 `reservedate` date default NULL,
2122 `itemcallnumber` varchar(255) default NULL,
2123 `holdingbranch` varchar(10) default NULL,
2124 `pickbranch` varchar(10) default NULL,
2126 `item_level_request` tinyint(4) NOT NULL default 0
2127 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2130 -- Table structure for table `message_queue`
2133 DROP TABLE IF EXISTS `message_queue`;
2134 CREATE TABLE `message_queue` (
2135 `message_id` int(11) NOT NULL auto_increment,
2136 `borrowernumber` int(11) default NULL,
2139 `metadata` text DEFAULT NULL,
2140 `letter_code` varchar(64) DEFAULT NULL,
2141 `message_transport_type` varchar(20) NOT NULL,
2142 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2143 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2144 `to_address` mediumtext,
2145 `from_address` mediumtext,
2146 `content_type` text,
2147 KEY `message_id` (`message_id`),
2148 KEY `borrowernumber` (`borrowernumber`),
2149 KEY `message_transport_type` (`message_transport_type`),
2150 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2151 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2152 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2155 -- Table structure for table `message_transport_types`
2158 DROP TABLE IF EXISTS `message_transport_types`;
2159 CREATE TABLE `message_transport_types` (
2160 `message_transport_type` varchar(20) NOT NULL,
2161 PRIMARY KEY (`message_transport_type`)
2162 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2165 -- Table structure for table `message_attributes`
2168 DROP TABLE IF EXISTS `message_attributes`;
2169 CREATE TABLE `message_attributes` (
2170 `message_attribute_id` int(11) NOT NULL auto_increment,
2171 `message_name` varchar(40) NOT NULL default '',
2172 `takes_days` tinyint(1) NOT NULL default '0',
2173 PRIMARY KEY (`message_attribute_id`),
2174 UNIQUE KEY `message_name` (`message_name`)
2175 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2178 -- Table structure for table `message_transports`
2181 DROP TABLE IF EXISTS `message_transports`;
2182 CREATE TABLE `message_transports` (
2183 `message_attribute_id` int(11) NOT NULL,
2184 `message_transport_type` varchar(20) NOT NULL,
2185 `is_digest` tinyint(1) NOT NULL default '0',
2186 `letter_module` varchar(20) NOT NULL default '',
2187 `letter_code` varchar(20) NOT NULL default '',
2188 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2189 KEY `message_transport_type` (`message_transport_type`),
2190 KEY `letter_module` (`letter_module`,`letter_code`),
2191 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2192 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2193 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2197 -- Table structure for table `borrower_message_preferences`
2200 DROP TABLE IF EXISTS `borrower_message_preferences`;
2201 CREATE TABLE `borrower_message_preferences` (
2202 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2203 `borrowernumber` int(11) default NULL,
2204 `categorycode` varchar(10) default NULL,
2205 `message_attribute_id` int(11) default '0',
2206 `days_in_advance` int(11) default '0',
2207 `wants_digest` tinyint(1) NOT NULL default '0',
2208 PRIMARY KEY (`borrower_message_preference_id`),
2209 KEY `borrowernumber` (`borrowernumber`),
2210 KEY `categorycode` (`categorycode`),
2211 KEY `message_attribute_id` (`message_attribute_id`),
2212 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2213 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2214 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2215 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2218 -- Table structure for table `borrower_message_transport_preferences`
2221 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2222 CREATE TABLE `borrower_message_transport_preferences` (
2223 `borrower_message_preference_id` int(11) NOT NULL default '0',
2224 `message_transport_type` varchar(20) NOT NULL default '0',
2225 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2226 KEY `message_transport_type` (`message_transport_type`),
2227 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,
2228 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
2229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2232 -- Table structure for the table branch_transfer_limits
2235 DROP TABLE IF EXISTS `branch_transfer_limits`;
2236 CREATE TABLE branch_transfer_limits (
2237 limitId int(8) NOT NULL auto_increment,
2238 toBranch varchar(10) NOT NULL,
2239 fromBranch varchar(10) NOT NULL,
2240 itemtype varchar(10) NULL,
2241 ccode varchar(10) NULL,
2242 PRIMARY KEY (limitId)
2243 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2246 -- Table structure for table `item_circulation_alert_preferences`
2249 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2250 CREATE TABLE `item_circulation_alert_preferences` (
2251 `id` int(11) NOT NULL auto_increment,
2252 `branchcode` varchar(10) NOT NULL,
2253 `categorycode` varchar(10) NOT NULL,
2254 `item_type` varchar(10) NOT NULL,
2255 `notification` varchar(16) NOT NULL,
2257 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2258 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2261 -- Table structure for table `messages`
2263 DROP TABLE IF EXISTS `messages`;
2264 CREATE TABLE `messages` (
2265 `message_id` int(11) NOT NULL auto_increment,
2266 `borrowernumber` int(11) NOT NULL,
2267 `branchcode` varchar(10) default NULL,
2268 `message_type` varchar(1) NOT NULL,
2269 `message` text NOT NULL,
2270 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2271 PRIMARY KEY (`message_id`)
2272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2275 -- Table structure for table `accountlines`
2278 DROP TABLE IF EXISTS `accountlines`;
2279 CREATE TABLE `accountlines` (
2280 `borrowernumber` int(11) NOT NULL default 0,
2281 `accountno` smallint(6) NOT NULL default 0,
2282 `itemnumber` int(11) default NULL,
2283 `date` date default NULL,
2284 `amount` decimal(28,6) default NULL,
2285 `description` mediumtext,
2286 `dispute` mediumtext,
2287 `accounttype` varchar(5) default NULL,
2288 `amountoutstanding` decimal(28,6) default NULL,
2289 `lastincrement` decimal(28,6) default NULL,
2290 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2291 `notify_id` int(11) NOT NULL default 0,
2292 `notify_level` int(2) NOT NULL default 0,
2293 KEY `acctsborridx` (`borrowernumber`),
2294 KEY `timeidx` (`timestamp`),
2295 KEY `itemnumber` (`itemnumber`),
2296 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2297 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2298 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2301 -- Table structure for table `accountoffsets`
2304 DROP TABLE IF EXISTS `accountoffsets`;
2305 CREATE TABLE `accountoffsets` (
2306 `borrowernumber` int(11) NOT NULL default 0,
2307 `accountno` smallint(6) NOT NULL default 0,
2308 `offsetaccount` smallint(6) NOT NULL default 0,
2309 `offsetamount` decimal(28,6) default NULL,
2310 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2311 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2312 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2315 -- Table structure for table `action_logs`
2318 DROP TABLE IF EXISTS `action_logs`;
2319 CREATE TABLE `action_logs` (
2320 `action_id` int(11) NOT NULL auto_increment,
2321 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2322 `user` int(11) NOT NULL default 0,
2325 `object` int(11) default NULL,
2327 PRIMARY KEY (`action_id`),
2328 KEY (`timestamp`,`user`)
2329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2332 -- Table structure for table `alert`
2335 DROP TABLE IF EXISTS `alert`;
2336 CREATE TABLE `alert` (
2337 `alertid` int(11) NOT NULL auto_increment,
2338 `borrowernumber` int(11) NOT NULL default 0,
2339 `type` varchar(10) NOT NULL default '',
2340 `externalid` varchar(20) NOT NULL default '',
2341 PRIMARY KEY (`alertid`),
2342 KEY `borrowernumber` (`borrowernumber`),
2343 KEY `type` (`type`,`externalid`)
2344 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2347 -- Table structure for table `aqbasketgroups`
2350 DROP TABLE IF EXISTS `aqbasketgroups`;
2351 CREATE TABLE `aqbasketgroups` (
2352 `id` int(11) NOT NULL auto_increment,
2353 `name` varchar(50) default NULL,
2354 `closed` tinyint(1) default NULL,
2355 `booksellerid` int(11) NOT NULL,
2356 `deliveryplace` varchar(10) default NULL,
2357 `deliverycomment` varchar(255) default NULL,
2358 `billingplace` varchar(10) default NULL,
2360 KEY `booksellerid` (`booksellerid`),
2361 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2362 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2365 -- Table structure for table `aqbasket`
2368 DROP TABLE IF EXISTS `aqbasket`;
2369 CREATE TABLE `aqbasket` (
2370 `basketno` int(11) NOT NULL auto_increment,
2371 `basketname` varchar(50) default NULL,
2373 `booksellernote` mediumtext,
2374 `contractnumber` int(11),
2375 `creationdate` date default NULL,
2376 `closedate` date default NULL,
2377 `booksellerid` int(11) NOT NULL default 1,
2378 `authorisedby` varchar(10) default NULL,
2379 `booksellerinvoicenumber` mediumtext,
2380 `basketgroupid` int(11),
2381 PRIMARY KEY (`basketno`),
2382 KEY `booksellerid` (`booksellerid`),
2383 KEY `basketgroupid` (`basketgroupid`),
2384 KEY `contractnumber` (`contractnumber`),
2385 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2386 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2387 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2388 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2391 -- Table structure for table `aqbooksellers`
2394 DROP TABLE IF EXISTS `aqbooksellers`;
2395 CREATE TABLE `aqbooksellers` (
2396 `id` int(11) NOT NULL auto_increment,
2397 `name` mediumtext NOT NULL,
2398 `address1` mediumtext,
2399 `address2` mediumtext,
2400 `address3` mediumtext,
2401 `address4` mediumtext,
2402 `phone` varchar(30) default NULL,
2403 `accountnumber` mediumtext,
2404 `othersupplier` mediumtext,
2405 `currency` varchar(3) NOT NULL default '',
2406 `booksellerfax` mediumtext,
2408 `bookselleremail` mediumtext,
2409 `booksellerurl` mediumtext,
2410 `contact` varchar(100) default NULL,
2411 `postal` mediumtext,
2412 `url` varchar(255) default NULL,
2413 `contpos` varchar(100) default NULL,
2414 `contphone` varchar(100) default NULL,
2415 `contfax` varchar(100) default NULL,
2416 `contaltphone` varchar(100) default NULL,
2417 `contemail` varchar(100) default NULL,
2418 `contnotes` mediumtext,
2419 `active` tinyint(4) default NULL,
2420 `listprice` varchar(10) default NULL,
2421 `invoiceprice` varchar(10) default NULL,
2422 `gstreg` tinyint(4) default NULL,
2423 `listincgst` tinyint(4) default NULL,
2424 `invoiceincgst` tinyint(4) default NULL,
2425 `gstrate` decimal(6,4) default NULL,
2426 `discount` float(6,4) default NULL,
2427 `fax` varchar(50) default NULL,
2429 KEY `listprice` (`listprice`),
2430 KEY `invoiceprice` (`invoiceprice`),
2431 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2432 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2433 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2436 -- Table structure for table `aqbudgets`
2439 DROP TABLE IF EXISTS `aqbudgets`;
2440 CREATE TABLE `aqbudgets` (
2441 `budget_id` int(11) NOT NULL auto_increment,
2442 `budget_parent_id` int(11) default NULL,
2443 `budget_code` varchar(30) default NULL,
2444 `budget_name` varchar(80) default NULL,
2445 `budget_branchcode` varchar(10) default NULL,
2446 `budget_amount` decimal(28,6) NULL default '0.00',
2447 `budget_encumb` decimal(28,6) NULL default '0.00',
2448 `budget_expend` decimal(28,6) NULL default '0.00',
2449 `budget_notes` mediumtext,
2450 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2451 `budget_period_id` int(11) default NULL,
2452 `sort1_authcat` varchar(80) default NULL,
2453 `sort2_authcat` varchar(80) default NULL,
2454 `budget_owner_id` int(11) default NULL,
2455 `budget_permission` int(1) default '0',
2456 PRIMARY KEY (`budget_id`)
2457 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2461 -- Table structure for table `aqbudgetperiods`
2465 DROP TABLE IF EXISTS `aqbudgetperiods`;
2466 CREATE TABLE `aqbudgetperiods` (
2467 `budget_period_id` int(11) NOT NULL auto_increment,
2468 `budget_period_startdate` date NOT NULL,
2469 `budget_period_enddate` date NOT NULL,
2470 `budget_period_active` tinyint(1) default '0',
2471 `budget_period_description` mediumtext,
2472 `budget_period_total` decimal(28,6),
2473 `budget_period_locked` tinyint(1) default NULL,
2474 `sort1_authcat` varchar(10) default NULL,
2475 `sort2_authcat` varchar(10) default NULL,
2476 PRIMARY KEY (`budget_period_id`)
2477 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2480 -- Table structure for table `aqbudgets_planning`
2483 DROP TABLE IF EXISTS `aqbudgets_planning`;
2484 CREATE TABLE `aqbudgets_planning` (
2485 `plan_id` int(11) NOT NULL auto_increment,
2486 `budget_id` int(11) NOT NULL,
2487 `budget_period_id` int(11) NOT NULL,
2488 `estimated_amount` decimal(28,6) default NULL,
2489 `authcat` varchar(30) NOT NULL,
2490 `authvalue` varchar(30) NOT NULL,
2491 `display` tinyint(1) DEFAULT 1,
2492 PRIMARY KEY (`plan_id`),
2493 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2497 -- Table structure for table 'aqcontract'
2500 DROP TABLE IF EXISTS `aqcontract`;
2501 CREATE TABLE `aqcontract` (
2502 `contractnumber` int(11) NOT NULL auto_increment,
2503 `contractstartdate` date default NULL,
2504 `contractenddate` date default NULL,
2505 `contractname` varchar(50) default NULL,
2506 `contractdescription` mediumtext,
2507 `booksellerid` int(11) not NULL,
2508 PRIMARY KEY (`contractnumber`),
2509 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2510 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2511 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2514 -- Table structure for table `aqorderdelivery`
2517 DROP TABLE IF EXISTS `aqorderdelivery`;
2518 CREATE TABLE `aqorderdelivery` (
2519 `ordernumber` date default NULL,
2520 `deliverynumber` smallint(6) NOT NULL default 0,
2521 `deliverydate` varchar(18) default NULL,
2522 `qtydelivered` smallint(6) default NULL,
2523 `deliverycomments` mediumtext
2524 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2527 -- Table structure for table `aqorders`
2530 DROP TABLE IF EXISTS `aqorders`;
2531 CREATE TABLE `aqorders` (
2532 `ordernumber` int(11) NOT NULL auto_increment,
2533 `biblionumber` int(11) default NULL,
2534 `entrydate` date default NULL,
2535 `quantity` smallint(6) default NULL,
2536 `currency` varchar(3) default NULL,
2537 `listprice` decimal(28,6) default NULL,
2538 `totalamount` decimal(28,6) default NULL,
2539 `datereceived` date default NULL,
2540 `booksellerinvoicenumber` mediumtext,
2541 `freight` decimal(28,6) default NULL,
2542 `unitprice` decimal(28,6) default NULL,
2543 `quantityreceived` smallint(6) NOT NULL default 0,
2544 `cancelledby` varchar(10) default NULL,
2545 `datecancellationprinted` date default NULL,
2547 `supplierreference` mediumtext,
2548 `purchaseordernumber` mediumtext,
2549 `subscription` tinyint(1) default NULL,
2550 `serialid` varchar(30) default NULL,
2551 `basketno` int(11) default NULL,
2552 `biblioitemnumber` int(11) default NULL,
2553 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2554 `rrp` decimal(13,2) default NULL,
2555 `ecost` decimal(13,2) default NULL,
2556 `gst` decimal(13,2) default NULL,
2557 `budget_id` int(11) NOT NULL,
2558 `budgetgroup_id` int(11) NOT NULL,
2559 `budgetdate` date default NULL,
2560 `sort1` varchar(80) default NULL,
2561 `sort2` varchar(80) default NULL,
2562 `sort1_authcat` varchar(10) default NULL,
2563 `sort2_authcat` varchar(10) default NULL,
2564 `uncertainprice` tinyint(1),
2565 PRIMARY KEY (`ordernumber`),
2566 KEY `basketno` (`basketno`),
2567 KEY `biblionumber` (`biblionumber`),
2568 KEY `budget_id` (`budget_id`),
2569 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2570 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2571 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2575 -- Table structure for table `aqorders_items`
2578 DROP TABLE IF EXISTS `aqorders_items`;
2579 CREATE TABLE `aqorders_items` (
2580 `ordernumber` int(11) NOT NULL,
2581 `itemnumber` int(11) NOT NULL,
2582 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2583 PRIMARY KEY (`itemnumber`),
2584 KEY `ordernumber` (`ordernumber`)
2585 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2588 -- Table structure for table `fieldmapping`
2591 DROP TABLE IF EXISTS `fieldmapping`;
2592 CREATE TABLE `fieldmapping` (
2593 `id` int(11) NOT NULL auto_increment,
2594 `field` varchar(255) NOT NULL,
2595 `frameworkcode` char(4) NOT NULL default '',
2596 `fieldcode` char(3) NOT NULL,
2597 `subfieldcode` char(1) NOT NULL,
2599 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2602 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2603 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2604 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2605 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2606 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2607 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2608 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2609 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;