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 `hardduedate` date default NULL,
970 `hardduedatecompare` tinyint NOT NULL default "0",
971 `renewalsallowed` smallint(6) NOT NULL default "0",
972 `reservesallowed` smallint(6) NOT NULL default "0",
973 `branchcode` varchar(10) NOT NULL default '',
974 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
975 KEY `categorycode` (`categorycode`),
976 KEY `itemtype` (`itemtype`)
977 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
980 -- Table structure for table `items`
983 DROP TABLE IF EXISTS `items`;
984 CREATE TABLE `items` (
985 `itemnumber` int(11) NOT NULL auto_increment,
986 `biblionumber` int(11) NOT NULL default 0,
987 `biblioitemnumber` int(11) NOT NULL default 0,
988 `barcode` varchar(20) default NULL,
989 `dateaccessioned` date default NULL,
990 `booksellerid` mediumtext default NULL,
991 `homebranch` varchar(10) default NULL,
992 `price` decimal(8,2) default NULL,
993 `replacementprice` decimal(8,2) default NULL,
994 `replacementpricedate` date default NULL,
995 `datelastborrowed` date default NULL,
996 `datelastseen` date default NULL,
997 `stack` tinyint(1) default NULL,
998 `notforloan` tinyint(1) NOT NULL default 0,
999 `damaged` tinyint(1) NOT NULL default 0,
1000 `itemlost` tinyint(1) NOT NULL default 0,
1001 `wthdrawn` tinyint(1) NOT NULL default 0,
1002 `itemcallnumber` varchar(255) default NULL,
1003 `issues` smallint(6) default NULL,
1004 `renewals` smallint(6) default NULL,
1005 `reserves` smallint(6) default NULL,
1006 `restricted` tinyint(1) default NULL,
1007 `itemnotes` mediumtext,
1008 `holdingbranch` varchar(10) default NULL,
1009 `paidfor` mediumtext,
1010 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1011 `location` varchar(80) default NULL,
1012 `permanent_location` varchar(80) default NULL,
1013 `onloan` date default NULL,
1014 `cn_source` varchar(10) default NULL,
1015 `cn_sort` varchar(30) default NULL,
1016 `ccode` varchar(10) default NULL,
1017 `materials` varchar(10) default NULL,
1018 `uri` varchar(255) default NULL,
1019 `itype` varchar(10) default NULL,
1020 `more_subfields_xml` longtext default NULL,
1021 `enumchron` text default NULL,
1022 `copynumber` varchar(32) default NULL,
1023 `stocknumber` varchar(32) default NULL,
1024 PRIMARY KEY (`itemnumber`),
1025 UNIQUE KEY `itembarcodeidx` (`barcode`),
1026 KEY `itemstocknumberidx` (`stocknumber`),
1027 KEY `itembinoidx` (`biblioitemnumber`),
1028 KEY `itembibnoidx` (`biblionumber`),
1029 KEY `homebranch` (`homebranch`),
1030 KEY `holdingbranch` (`holdingbranch`),
1031 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1032 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1033 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1034 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1037 -- Table structure for table `itemtypes`
1040 DROP TABLE IF EXISTS `itemtypes`;
1041 CREATE TABLE `itemtypes` (
1042 `itemtype` varchar(10) NOT NULL default '',
1043 `description` mediumtext,
1044 `rentalcharge` double(16,4) default NULL,
1045 `notforloan` smallint(6) default NULL,
1046 `imageurl` varchar(200) default NULL,
1048 PRIMARY KEY (`itemtype`),
1049 UNIQUE KEY `itemtype` (`itemtype`)
1050 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1053 -- Table structure for table `creator_batches`
1056 DROP TABLE IF EXISTS `creator_batches`;
1057 SET @saved_cs_client = @@character_set_client;
1058 SET character_set_client = utf8;
1059 CREATE TABLE `creator_batches` (
1060 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1061 `batch_id` int(10) NOT NULL DEFAULT '1',
1062 `item_number` int(11) DEFAULT NULL,
1063 `borrower_number` int(11) DEFAULT NULL,
1064 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1065 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1066 `creator` char(15) NOT NULL DEFAULT 'Labels',
1067 PRIMARY KEY (`label_id`),
1068 KEY `branch_fk_constraint` (`branch_code`),
1069 KEY `item_fk_constraint` (`item_number`),
1070 KEY `borrower_fk_constraint` (`borrower_number`),
1071 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1072 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1073 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1074 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1077 -- Table structure for table `creator_images`
1080 DROP TABLE IF EXISTS `creator_images`;
1081 SET @saved_cs_client = @@character_set_client;
1082 SET character_set_client = utf8;
1083 CREATE TABLE `creator_images` (
1084 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1085 `imagefile` mediumblob,
1086 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1087 PRIMARY KEY (`image_id`),
1088 UNIQUE KEY `image_name_index` (`image_name`)
1089 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1092 -- Table structure for table `creator_layouts`
1095 DROP TABLE IF EXISTS `creator_layouts`;
1096 SET @saved_cs_client = @@character_set_client;
1097 SET character_set_client = utf8;
1098 CREATE TABLE `creator_layouts` (
1099 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1100 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1101 `start_label` int(2) NOT NULL DEFAULT '1',
1102 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1103 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1104 `guidebox` int(1) DEFAULT '0',
1105 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1106 `font_size` int(4) NOT NULL DEFAULT '10',
1107 `units` char(20) NOT NULL DEFAULT 'POINT',
1108 `callnum_split` int(1) DEFAULT '0',
1109 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1110 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1111 `layout_xml` text NOT NULL,
1112 `creator` char(15) NOT NULL DEFAULT 'Labels',
1113 PRIMARY KEY (`layout_id`)
1114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1117 -- Table structure for table `creator_templates`
1120 DROP TABLE IF EXISTS `creator_templates`;
1121 SET @saved_cs_client = @@character_set_client;
1122 SET character_set_client = utf8;
1123 CREATE TABLE `creator_templates` (
1124 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1125 `profile_id` int(4) DEFAULT NULL,
1126 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1127 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1128 `page_width` float NOT NULL DEFAULT '0',
1129 `page_height` float NOT NULL DEFAULT '0',
1130 `label_width` float NOT NULL DEFAULT '0',
1131 `label_height` float NOT NULL DEFAULT '0',
1132 `top_text_margin` float NOT NULL DEFAULT '0',
1133 `left_text_margin` float NOT NULL DEFAULT '0',
1134 `top_margin` float NOT NULL DEFAULT '0',
1135 `left_margin` float NOT NULL DEFAULT '0',
1136 `cols` int(2) NOT NULL DEFAULT '0',
1137 `rows` int(2) NOT NULL DEFAULT '0',
1138 `col_gap` float NOT NULL DEFAULT '0',
1139 `row_gap` float NOT NULL DEFAULT '0',
1140 `units` char(20) NOT NULL DEFAULT 'POINT',
1141 `creator` char(15) NOT NULL DEFAULT 'Labels',
1142 PRIMARY KEY (`template_id`),
1143 KEY `template_profile_fk_constraint` (`profile_id`)
1144 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1147 -- Table structure for table `letter`
1150 DROP TABLE IF EXISTS `letter`;
1151 CREATE TABLE `letter` (
1152 `module` varchar(20) NOT NULL default '',
1153 `code` varchar(20) NOT NULL default '',
1154 `name` varchar(100) NOT NULL default '',
1155 `title` varchar(200) NOT NULL default '',
1157 PRIMARY KEY (`module`,`code`)
1158 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1161 -- Table structure for table `marc_subfield_structure`
1164 DROP TABLE IF EXISTS `marc_subfield_structure`;
1165 CREATE TABLE `marc_subfield_structure` (
1166 `tagfield` varchar(3) NOT NULL default '',
1167 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1168 `liblibrarian` varchar(255) NOT NULL default '',
1169 `libopac` varchar(255) NOT NULL default '',
1170 `repeatable` tinyint(4) NOT NULL default 0,
1171 `mandatory` tinyint(4) NOT NULL default 0,
1172 `kohafield` varchar(40) default NULL,
1173 `tab` tinyint(1) default NULL,
1174 `authorised_value` varchar(20) default NULL,
1175 `authtypecode` varchar(20) default NULL,
1176 `value_builder` varchar(80) default NULL,
1177 `isurl` tinyint(1) default NULL,
1178 `hidden` tinyint(1) default NULL,
1179 `frameworkcode` varchar(4) NOT NULL default '',
1180 `seealso` varchar(1100) default NULL,
1181 `link` varchar(80) default NULL,
1182 `defaultvalue` text default NULL,
1183 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1184 KEY `kohafield_2` (`kohafield`),
1185 KEY `tab` (`frameworkcode`,`tab`),
1186 KEY `kohafield` (`frameworkcode`,`kohafield`)
1187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1190 -- Table structure for table `marc_tag_structure`
1193 DROP TABLE IF EXISTS `marc_tag_structure`;
1194 CREATE TABLE `marc_tag_structure` (
1195 `tagfield` varchar(3) NOT NULL default '',
1196 `liblibrarian` varchar(255) NOT NULL default '',
1197 `libopac` varchar(255) NOT NULL default '',
1198 `repeatable` tinyint(4) NOT NULL default 0,
1199 `mandatory` tinyint(4) NOT NULL default 0,
1200 `authorised_value` varchar(10) default NULL,
1201 `frameworkcode` varchar(4) NOT NULL default '',
1202 PRIMARY KEY (`frameworkcode`,`tagfield`)
1203 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1206 -- Table structure for table `marc_matchers`
1209 DROP TABLE IF EXISTS `marc_matchers`;
1210 CREATE TABLE `marc_matchers` (
1211 `matcher_id` int(11) NOT NULL auto_increment,
1212 `code` varchar(10) NOT NULL default '',
1213 `description` varchar(255) NOT NULL default '',
1214 `record_type` varchar(10) NOT NULL default 'biblio',
1215 `threshold` int(11) NOT NULL default 0,
1216 PRIMARY KEY (`matcher_id`),
1217 KEY `code` (`code`),
1218 KEY `record_type` (`record_type`)
1219 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1222 -- Table structure for table `matchpoints`
1224 DROP TABLE IF EXISTS `matchpoints`;
1225 CREATE TABLE `matchpoints` (
1226 `matcher_id` int(11) NOT NULL,
1227 `matchpoint_id` int(11) NOT NULL auto_increment,
1228 `search_index` varchar(30) NOT NULL default '',
1229 `score` int(11) NOT NULL default 0,
1230 PRIMARY KEY (`matchpoint_id`),
1231 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1232 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1233 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1237 -- Table structure for table `matchpoint_components`
1239 DROP TABLE IF EXISTS `matchpoint_components`;
1240 CREATE TABLE `matchpoint_components` (
1241 `matchpoint_id` int(11) NOT NULL,
1242 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1243 sequence int(11) NOT NULL default 0,
1244 tag varchar(3) NOT NULL default '',
1245 subfields varchar(40) NOT NULL default '',
1246 offset int(4) NOT NULL default 0,
1247 length int(4) NOT NULL default 0,
1248 PRIMARY KEY (`matchpoint_component_id`),
1249 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1250 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1251 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1252 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1255 -- Table structure for table `matcher_component_norms`
1257 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1258 CREATE TABLE `matchpoint_component_norms` (
1259 `matchpoint_component_id` int(11) NOT NULL,
1260 `sequence` int(11) NOT NULL default 0,
1261 `norm_routine` varchar(50) NOT NULL default '',
1262 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1263 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1264 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1268 -- Table structure for table `matcher_matchpoints`
1270 DROP TABLE IF EXISTS `matcher_matchpoints`;
1271 CREATE TABLE `matcher_matchpoints` (
1272 `matcher_id` int(11) NOT NULL,
1273 `matchpoint_id` int(11) NOT NULL,
1274 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1275 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1276 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1277 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1278 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1281 -- Table structure for table `matchchecks`
1283 DROP TABLE IF EXISTS `matchchecks`;
1284 CREATE TABLE `matchchecks` (
1285 `matcher_id` int(11) NOT NULL,
1286 `matchcheck_id` int(11) NOT NULL auto_increment,
1287 `source_matchpoint_id` int(11) NOT NULL,
1288 `target_matchpoint_id` int(11) NOT NULL,
1289 PRIMARY KEY (`matchcheck_id`),
1290 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1291 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1292 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1293 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1294 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1295 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1296 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1299 -- Table structure for table `notifys`
1302 DROP TABLE IF EXISTS `notifys`;
1303 CREATE TABLE `notifys` (
1304 `notify_id` int(11) NOT NULL default 0,
1305 `borrowernumber` int(11) NOT NULL default 0,
1306 `itemnumber` int(11) NOT NULL default 0,
1307 `notify_date` date default NULL,
1308 `notify_send_date` date default NULL,
1309 `notify_level` int(1) NOT NULL default 0,
1310 `method` varchar(20) NOT NULL default ''
1311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1314 -- Table structure for table `nozebra`
1317 DROP TABLE IF EXISTS `nozebra`;
1318 CREATE TABLE `nozebra` (
1319 `server` varchar(20) NOT NULL,
1320 `indexname` varchar(40) NOT NULL,
1321 `value` varchar(250) NOT NULL,
1322 `biblionumbers` longtext NOT NULL,
1323 KEY `indexname` (`server`,`indexname`),
1324 KEY `value` (`server`,`value`))
1325 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1328 -- Table structure for table `old_issues`
1331 DROP TABLE IF EXISTS `old_issues`;
1332 CREATE TABLE `old_issues` (
1333 `borrowernumber` int(11) default NULL,
1334 `itemnumber` int(11) default NULL,
1335 `date_due` date default NULL,
1336 `branchcode` varchar(10) default NULL,
1337 `issuingbranch` varchar(18) default NULL,
1338 `returndate` date default NULL,
1339 `lastreneweddate` date default NULL,
1340 `return` varchar(4) default NULL,
1341 `renewals` tinyint(4) default NULL,
1342 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1343 `issuedate` date default NULL,
1344 KEY `old_issuesborridx` (`borrowernumber`),
1345 KEY `old_issuesitemidx` (`itemnumber`),
1346 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1347 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1348 ON DELETE SET NULL ON UPDATE SET NULL,
1349 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1350 ON DELETE SET NULL ON UPDATE SET NULL
1351 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1354 -- Table structure for table `old_reserves`
1356 DROP TABLE IF EXISTS `old_reserves`;
1357 CREATE TABLE `old_reserves` (
1358 `borrowernumber` int(11) default NULL,
1359 `reservedate` date default NULL,
1360 `biblionumber` int(11) default NULL,
1361 `constrainttype` varchar(1) default NULL,
1362 `branchcode` varchar(10) default NULL,
1363 `notificationdate` date default NULL,
1364 `reminderdate` date default NULL,
1365 `cancellationdate` date default NULL,
1366 `reservenotes` mediumtext,
1367 `priority` smallint(6) default NULL,
1368 `found` varchar(1) default NULL,
1369 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1370 `itemnumber` int(11) default NULL,
1371 `waitingdate` date default NULL,
1372 `expirationdate` DATE DEFAULT NULL,
1373 `lowestPriority` tinyint(1) NOT NULL,
1374 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1375 KEY `old_reserves_biblionumber` (`biblionumber`),
1376 KEY `old_reserves_itemnumber` (`itemnumber`),
1377 KEY `old_reserves_branchcode` (`branchcode`),
1378 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1379 ON DELETE SET NULL ON UPDATE SET NULL,
1380 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1381 ON DELETE SET NULL ON UPDATE SET NULL,
1382 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1383 ON DELETE SET NULL ON UPDATE SET NULL
1384 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1387 -- Table structure for table `opac_news`
1390 DROP TABLE IF EXISTS `opac_news`;
1391 CREATE TABLE `opac_news` (
1392 `idnew` int(10) unsigned NOT NULL auto_increment,
1393 `title` varchar(250) NOT NULL default '',
1394 `new` text NOT NULL,
1395 `lang` varchar(25) NOT NULL default '',
1396 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1397 `expirationdate` date default NULL,
1398 `number` int(11) default NULL,
1399 PRIMARY KEY (`idnew`)
1400 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1403 -- Table structure for table `overduerules`
1406 DROP TABLE IF EXISTS `overduerules`;
1407 CREATE TABLE `overduerules` (
1408 `branchcode` varchar(10) NOT NULL default '',
1409 `categorycode` varchar(10) NOT NULL default '',
1410 `delay1` int(4) default NULL,
1411 `letter1` varchar(20) default NULL,
1412 `debarred1` varchar(1) default 0,
1413 `delay2` int(4) default NULL,
1414 `debarred2` varchar(1) default 0,
1415 `letter2` varchar(20) default NULL,
1416 `delay3` int(4) default NULL,
1417 `letter3` varchar(20) default NULL,
1418 `debarred3` int(1) default 0,
1419 PRIMARY KEY (`branchcode`,`categorycode`)
1420 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1423 -- Table structure for table `patroncards`
1426 DROP TABLE IF EXISTS `patroncards`;
1427 CREATE TABLE `patroncards` (
1428 `cardid` int(11) NOT NULL auto_increment,
1429 `batch_id` varchar(10) NOT NULL default '1',
1430 `borrowernumber` int(11) NOT NULL,
1431 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1432 PRIMARY KEY (`cardid`),
1433 KEY `patroncards_ibfk_1` (`borrowernumber`),
1434 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1435 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1438 -- Table structure for table `patronimage`
1441 DROP TABLE IF EXISTS `patronimage`;
1442 CREATE TABLE `patronimage` (
1443 `cardnumber` varchar(16) NOT NULL,
1444 `mimetype` varchar(15) NOT NULL,
1445 `imagefile` mediumblob NOT NULL,
1446 PRIMARY KEY (`cardnumber`),
1447 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1448 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1451 -- Table structure for table `printers`
1454 DROP TABLE IF EXISTS `printers`;
1455 CREATE TABLE `printers` (
1456 `printername` varchar(40) NOT NULL default '',
1457 `printqueue` varchar(20) default NULL,
1458 `printtype` varchar(20) default NULL,
1459 PRIMARY KEY (`printername`)
1460 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1463 -- Table structure for table `printers_profile`
1466 DROP TABLE IF EXISTS `printers_profile`;
1467 CREATE TABLE `printers_profile` (
1468 `profile_id` int(4) NOT NULL auto_increment,
1469 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1470 `template_id` int(4) NOT NULL default '0',
1471 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1472 `offset_horz` float NOT NULL default '0',
1473 `offset_vert` float NOT NULL default '0',
1474 `creep_horz` float NOT NULL default '0',
1475 `creep_vert` float NOT NULL default '0',
1476 `units` char(20) NOT NULL default 'POINT',
1477 `creator` char(15) NOT NULL DEFAULT 'Labels',
1478 PRIMARY KEY (`profile_id`),
1479 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
1480 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1483 -- Table structure for table `repeatable_holidays`
1486 DROP TABLE IF EXISTS `repeatable_holidays`;
1487 CREATE TABLE `repeatable_holidays` (
1488 `id` int(11) NOT NULL auto_increment,
1489 `branchcode` varchar(10) NOT NULL default '',
1490 `weekday` smallint(6) default NULL,
1491 `day` smallint(6) default NULL,
1492 `month` smallint(6) default NULL,
1493 `title` varchar(50) NOT NULL default '',
1494 `description` text NOT NULL,
1496 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1499 -- Table structure for table `reports_dictionary`
1502 DROP TABLE IF EXISTS `reports_dictionary`;
1503 CREATE TABLE reports_dictionary (
1504 `id` int(11) NOT NULL auto_increment,
1505 `name` varchar(255) default NULL,
1507 `date_created` datetime default NULL,
1508 `date_modified` datetime default NULL,
1510 `area` int(11) default NULL,
1512 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1515 -- Table structure for table `reserveconstraints`
1518 DROP TABLE IF EXISTS `reserveconstraints`;
1519 CREATE TABLE `reserveconstraints` (
1520 `borrowernumber` int(11) NOT NULL default 0,
1521 `reservedate` date default NULL,
1522 `biblionumber` int(11) NOT NULL default 0,
1523 `biblioitemnumber` int(11) default NULL,
1524 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1525 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1528 -- Table structure for table `reserves`
1531 DROP TABLE IF EXISTS `reserves`;
1532 CREATE TABLE `reserves` (
1533 `borrowernumber` int(11) NOT NULL default 0,
1534 `reservedate` date default NULL,
1535 `biblionumber` int(11) NOT NULL default 0,
1536 `constrainttype` varchar(1) default NULL,
1537 `branchcode` varchar(10) default NULL,
1538 `notificationdate` date default NULL,
1539 `reminderdate` date default NULL,
1540 `cancellationdate` date default NULL,
1541 `reservenotes` mediumtext,
1542 `priority` smallint(6) default NULL,
1543 `found` varchar(1) default NULL,
1544 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1545 `itemnumber` int(11) default NULL,
1546 `waitingdate` date default NULL,
1547 `expirationdate` DATE DEFAULT NULL,
1548 `lowestPriority` tinyint(1) NOT NULL,
1549 KEY `borrowernumber` (`borrowernumber`),
1550 KEY `biblionumber` (`biblionumber`),
1551 KEY `itemnumber` (`itemnumber`),
1552 KEY `branchcode` (`branchcode`),
1553 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1554 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1555 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1556 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1557 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1560 -- Table structure for table `reviews`
1563 DROP TABLE IF EXISTS `reviews`;
1564 CREATE TABLE `reviews` (
1565 `reviewid` int(11) NOT NULL auto_increment,
1566 `borrowernumber` int(11) default NULL,
1567 `biblionumber` int(11) default NULL,
1569 `approved` tinyint(4) default NULL,
1570 `datereviewed` datetime default NULL,
1571 PRIMARY KEY (`reviewid`)
1572 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1575 -- Table structure for table `roadtype`
1578 DROP TABLE IF EXISTS `roadtype`;
1579 CREATE TABLE `roadtype` (
1580 `roadtypeid` int(11) NOT NULL auto_increment,
1581 `road_type` varchar(100) NOT NULL default '',
1582 PRIMARY KEY (`roadtypeid`)
1583 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1586 -- Table structure for table `saved_sql`
1589 DROP TABLE IF EXISTS `saved_sql`;
1590 CREATE TABLE saved_sql (
1591 `id` int(11) NOT NULL auto_increment,
1592 `borrowernumber` int(11) default NULL,
1593 `date_created` datetime default NULL,
1594 `last_modified` datetime default NULL,
1596 `last_run` datetime default NULL,
1597 `report_name` varchar(255) default NULL,
1598 `type` varchar(255) default NULL,
1601 KEY boridx (`borrowernumber`)
1602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1606 -- Table structure for `saved_reports`
1609 DROP TABLE IF EXISTS `saved_reports`;
1610 CREATE TABLE saved_reports (
1611 `id` int(11) NOT NULL auto_increment,
1612 `report_id` int(11) default NULL,
1614 `date_run` datetime default NULL,
1616 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1620 -- Table structure for table `search_history`
1623 DROP TABLE IF EXISTS `search_history`;
1624 CREATE TABLE IF NOT EXISTS `search_history` (
1625 `userid` int(11) NOT NULL,
1626 `sessionid` varchar(32) NOT NULL,
1627 `query_desc` varchar(255) NOT NULL,
1628 `query_cgi` varchar(255) NOT NULL,
1629 `total` int(11) NOT NULL,
1630 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1631 KEY `userid` (`userid`),
1632 KEY `sessionid` (`sessionid`)
1633 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1637 -- Table structure for table `serial`
1640 DROP TABLE IF EXISTS `serial`;
1641 CREATE TABLE `serial` (
1642 `serialid` int(11) NOT NULL auto_increment,
1643 `biblionumber` varchar(100) NOT NULL default '',
1644 `subscriptionid` varchar(100) NOT NULL default '',
1645 `serialseq` varchar(100) NOT NULL default '',
1646 `status` tinyint(4) NOT NULL default 0,
1647 `planneddate` date default NULL,
1649 `publisheddate` date default NULL,
1650 `itemnumber` text default NULL,
1651 `claimdate` date default NULL,
1652 `routingnotes` text,
1653 PRIMARY KEY (`serialid`)
1654 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1657 -- Table structure for table `sessions`
1660 DROP TABLE IF EXISTS sessions;
1661 CREATE TABLE sessions (
1662 `id` varchar(32) NOT NULL,
1663 `a_session` text NOT NULL,
1665 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1668 -- Table structure for table `special_holidays`
1671 DROP TABLE IF EXISTS `special_holidays`;
1672 CREATE TABLE `special_holidays` (
1673 `id` int(11) NOT NULL auto_increment,
1674 `branchcode` varchar(10) NOT NULL default '',
1675 `day` smallint(6) NOT NULL default 0,
1676 `month` smallint(6) NOT NULL default 0,
1677 `year` smallint(6) NOT NULL default 0,
1678 `isexception` smallint(1) NOT NULL default 1,
1679 `title` varchar(50) NOT NULL default '',
1680 `description` text NOT NULL,
1682 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1685 -- Table structure for table `statistics`
1688 DROP TABLE IF EXISTS `statistics`;
1689 CREATE TABLE `statistics` (
1690 `datetime` datetime default NULL,
1691 `branch` varchar(10) default NULL,
1692 `proccode` varchar(4) default NULL,
1693 `value` double(16,4) default NULL,
1694 `type` varchar(16) default NULL,
1696 `usercode` varchar(10) default NULL,
1697 `itemnumber` int(11) default NULL,
1698 `itemtype` varchar(10) default NULL,
1699 `borrowernumber` int(11) default NULL,
1700 `associatedborrower` int(11) default NULL,
1701 KEY `timeidx` (`datetime`)
1702 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1705 -- Table structure for table `stopwords`
1708 DROP TABLE IF EXISTS `stopwords`;
1709 CREATE TABLE `stopwords` (
1710 `word` varchar(255) default NULL
1711 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1714 -- Table structure for table `subscription`
1717 DROP TABLE IF EXISTS `subscription`;
1718 CREATE TABLE `subscription` (
1719 `biblionumber` int(11) NOT NULL default 0,
1720 `subscriptionid` int(11) NOT NULL auto_increment,
1721 `librarian` varchar(100) default '',
1722 `startdate` date default NULL,
1723 `aqbooksellerid` int(11) default 0,
1724 `cost` int(11) default 0,
1725 `aqbudgetid` int(11) default 0,
1726 `weeklength` int(11) default 0,
1727 `monthlength` int(11) default 0,
1728 `numberlength` int(11) default 0,
1729 `periodicity` tinyint(4) default 0,
1730 `dow` varchar(100) default '',
1731 `numberingmethod` varchar(100) default '',
1733 `status` varchar(100) NOT NULL default '',
1734 `add1` int(11) default 0,
1735 `every1` int(11) default 0,
1736 `whenmorethan1` int(11) default 0,
1737 `setto1` int(11) default NULL,
1738 `lastvalue1` int(11) default NULL,
1739 `add2` int(11) default 0,
1740 `every2` int(11) default 0,
1741 `whenmorethan2` int(11) default 0,
1742 `setto2` int(11) default NULL,
1743 `lastvalue2` int(11) default NULL,
1744 `add3` int(11) default 0,
1745 `every3` int(11) default 0,
1746 `innerloop1` int(11) default 0,
1747 `innerloop2` int(11) default 0,
1748 `innerloop3` int(11) default 0,
1749 `whenmorethan3` int(11) default 0,
1750 `setto3` int(11) default NULL,
1751 `lastvalue3` int(11) default NULL,
1752 `issuesatonce` tinyint(3) NOT NULL default 1,
1753 `firstacquidate` date default NULL,
1754 `manualhistory` tinyint(1) NOT NULL default 0,
1755 `irregularity` text,
1756 `letter` varchar(20) default NULL,
1757 `numberpattern` tinyint(3) default 0,
1758 `distributedto` text,
1759 `internalnotes` longtext,
1761 `location` varchar(80) NULL default '',
1762 `branchcode` varchar(10) NOT NULL default '',
1763 `hemisphere` tinyint(3) default 0,
1764 `lastbranch` varchar(10),
1765 `serialsadditems` tinyint(1) NOT NULL default '0',
1766 `staffdisplaycount` VARCHAR(10) NULL,
1767 `opacdisplaycount` VARCHAR(10) NULL,
1768 `graceperiod` int(11) NOT NULL default '0',
1769 `enddate` date default NULL,
1770 PRIMARY KEY (`subscriptionid`)
1771 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1774 -- Table structure for table `subscriptionhistory`
1777 DROP TABLE IF EXISTS `subscriptionhistory`;
1778 CREATE TABLE `subscriptionhistory` (
1779 `biblionumber` int(11) NOT NULL default 0,
1780 `subscriptionid` int(11) NOT NULL default 0,
1781 `histstartdate` date default NULL,
1782 `histenddate` date default NULL,
1783 `missinglist` longtext NOT NULL,
1784 `recievedlist` longtext NOT NULL,
1785 `opacnote` varchar(150) NOT NULL default '',
1786 `librariannote` varchar(150) NOT NULL default '',
1787 PRIMARY KEY (`subscriptionid`),
1788 KEY `biblionumber` (`biblionumber`)
1789 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1792 -- Table structure for table `subscriptionroutinglist`
1795 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1796 CREATE TABLE `subscriptionroutinglist` (
1797 `routingid` int(11) NOT NULL auto_increment,
1798 `borrowernumber` int(11) NOT NULL,
1799 `ranking` int(11) default NULL,
1800 `subscriptionid` int(11) NOT NULL,
1801 PRIMARY KEY (`routingid`),
1802 UNIQUE (`subscriptionid`, `borrowernumber`),
1803 CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1804 ON DELETE CASCADE ON UPDATE CASCADE,
1805 CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
1806 ON DELETE CASCADE ON UPDATE CASCADE
1807 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1810 -- Table structure for table `suggestions`
1813 DROP TABLE IF EXISTS `suggestions`;
1814 CREATE TABLE `suggestions` (
1815 `suggestionid` int(8) NOT NULL auto_increment,
1816 `suggestedby` int(11) NOT NULL default 0,
1817 `suggesteddate` date NOT NULL default 0,
1818 `managedby` int(11) default NULL,
1819 `manageddate` date default NULL,
1820 acceptedby INT(11) default NULL,
1821 accepteddate date default NULL,
1822 rejectedby INT(11) default NULL,
1823 rejecteddate date default NULL,
1824 `STATUS` varchar(10) NOT NULL default '',
1826 `author` varchar(80) default NULL,
1827 `title` varchar(80) default NULL,
1828 `copyrightdate` smallint(6) default NULL,
1829 `publishercode` varchar(255) default NULL,
1830 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1831 `volumedesc` varchar(255) default NULL,
1832 `publicationyear` smallint(6) default 0,
1833 `place` varchar(255) default NULL,
1834 `isbn` varchar(30) default NULL,
1835 `mailoverseeing` smallint(1) default 0,
1836 `biblionumber` int(11) default NULL,
1839 branchcode VARCHAR(10) default NULL,
1840 collectiontitle text default NULL,
1841 itemtype VARCHAR(30) default NULL,
1842 quantity SMALLINT(6) default NULL,
1843 currency VARCHAR(3) default NULL,
1844 price DECIMAL(28,6) default NULL,
1845 total DECIMAL(28,6) default NULL,
1846 PRIMARY KEY (`suggestionid`),
1847 KEY `suggestedby` (`suggestedby`),
1848 KEY `managedby` (`managedby`)
1849 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1852 -- Table structure for table `systempreferences`
1855 DROP TABLE IF EXISTS `systempreferences`;
1856 CREATE TABLE `systempreferences` (
1857 `variable` varchar(50) NOT NULL default '',
1859 `options` mediumtext,
1861 `type` varchar(20) default NULL,
1862 PRIMARY KEY (`variable`)
1863 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1866 -- Table structure for table `tags`
1869 DROP TABLE IF EXISTS `tags`;
1870 CREATE TABLE `tags` (
1871 `entry` varchar(255) NOT NULL default '',
1872 `weight` bigint(20) NOT NULL default 0,
1873 PRIMARY KEY (`entry`)
1874 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1877 -- Table structure for table `tags_all`
1880 DROP TABLE IF EXISTS `tags_all`;
1881 CREATE TABLE `tags_all` (
1882 `tag_id` int(11) NOT NULL auto_increment,
1883 `borrowernumber` int(11) NOT NULL,
1884 `biblionumber` int(11) NOT NULL,
1885 `term` varchar(255) NOT NULL,
1886 `language` int(4) default NULL,
1887 `date_created` datetime NOT NULL,
1888 PRIMARY KEY (`tag_id`),
1889 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1890 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1891 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1892 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1893 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1894 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1895 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1898 -- Table structure for table `tags_approval`
1901 DROP TABLE IF EXISTS `tags_approval`;
1902 CREATE TABLE `tags_approval` (
1903 `term` varchar(255) NOT NULL,
1904 `approved` int(1) NOT NULL default '0',
1905 `date_approved` datetime default NULL,
1906 `approved_by` int(11) default NULL,
1907 `weight_total` int(9) NOT NULL default '1',
1908 PRIMARY KEY (`term`),
1909 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1910 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1911 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1912 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1915 -- Table structure for table `tags_index`
1918 DROP TABLE IF EXISTS `tags_index`;
1919 CREATE TABLE `tags_index` (
1920 `term` varchar(255) NOT NULL,
1921 `biblionumber` int(11) NOT NULL,
1922 `weight` int(9) NOT NULL default '1',
1923 PRIMARY KEY (`term`,`biblionumber`),
1924 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1925 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1926 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1927 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1928 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1929 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1932 -- Table structure for table `userflags`
1935 DROP TABLE IF EXISTS `userflags`;
1936 CREATE TABLE `userflags` (
1937 `bit` int(11) NOT NULL default 0,
1938 `flag` varchar(30) default NULL,
1939 `flagdesc` varchar(255) default NULL,
1940 `defaulton` int(11) default NULL,
1942 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1945 -- Table structure for table `virtualshelves`
1948 DROP TABLE IF EXISTS `virtualshelves`;
1949 CREATE TABLE `virtualshelves` (
1950 `shelfnumber` int(11) NOT NULL auto_increment,
1951 `shelfname` varchar(255) default NULL,
1952 `owner` varchar(80) default NULL,
1953 `category` varchar(1) default NULL,
1954 `sortfield` varchar(16) default NULL,
1955 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1956 PRIMARY KEY (`shelfnumber`)
1957 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1960 -- Table structure for table `virtualshelfcontents`
1963 DROP TABLE IF EXISTS `virtualshelfcontents`;
1964 CREATE TABLE `virtualshelfcontents` (
1965 `shelfnumber` int(11) NOT NULL default 0,
1966 `biblionumber` int(11) NOT NULL default 0,
1967 `flags` int(11) default NULL,
1968 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1969 KEY `shelfnumber` (`shelfnumber`),
1970 KEY `biblionumber` (`biblionumber`),
1971 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1972 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1973 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1976 -- Table structure for table `z3950servers`
1979 DROP TABLE IF EXISTS `z3950servers`;
1980 CREATE TABLE `z3950servers` (
1981 `host` varchar(255) default NULL,
1982 `port` int(11) default NULL,
1983 `db` varchar(255) default NULL,
1984 `userid` varchar(255) default NULL,
1985 `password` varchar(255) default NULL,
1987 `id` int(11) NOT NULL auto_increment,
1988 `checked` smallint(6) default NULL,
1989 `rank` int(11) default NULL,
1990 `syntax` varchar(80) default NULL,
1992 `position` enum('primary','secondary','') NOT NULL default 'primary',
1993 `type` enum('zed','opensearch') NOT NULL default 'zed',
1994 `encoding` text default NULL,
1995 `description` text NOT NULL,
1997 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2000 -- Table structure for table `zebraqueue`
2003 DROP TABLE IF EXISTS `zebraqueue`;
2004 CREATE TABLE `zebraqueue` (
2005 `id` int(11) NOT NULL auto_increment,
2006 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2007 `operation` char(20) NOT NULL default '',
2008 `server` char(20) NOT NULL default '',
2009 `done` int(11) NOT NULL default '0',
2010 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2012 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2013 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2015 DROP TABLE IF EXISTS `services_throttle`;
2016 CREATE TABLE `services_throttle` (
2017 `service_type` varchar(10) NOT NULL default '',
2018 `service_count` varchar(45) default NULL,
2019 PRIMARY KEY (`service_type`)
2020 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2022 -- http://www.w3.org/International/articles/language-tags/
2025 DROP TABLE IF EXISTS language_subtag_registry;
2026 CREATE TABLE language_subtag_registry (
2028 type varchar(25), -- language-script-region-variant-extension-privateuse
2029 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2031 id int(11) NOT NULL auto_increment,
2033 KEY `subtag` (`subtag`)
2034 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2036 -- TODO: add suppress_scripts
2037 -- this maps three letter codes defined in iso639.2 back to their
2038 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2039 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2040 CREATE TABLE language_rfc4646_to_iso639 (
2041 rfc4646_subtag varchar(25),
2042 iso639_2_code varchar(25),
2043 id int(11) NOT NULL auto_increment,
2045 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2046 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2048 DROP TABLE IF EXISTS language_descriptions;
2049 CREATE TABLE language_descriptions (
2053 description varchar(255),
2054 id int(11) NOT NULL auto_increment,
2056 KEY `lang` (`lang`),
2057 KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
2058 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2060 -- bi-directional support, keyed by script subcode
2061 DROP TABLE IF EXISTS language_script_bidi;
2062 CREATE TABLE language_script_bidi (
2063 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2064 bidi varchar(3), -- rtl ltr
2065 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2066 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2068 -- TODO: need to map language subtags to script subtags for detection
2069 -- of bidi when script is not specified (like ar, he)
2070 DROP TABLE IF EXISTS language_script_mapping;
2071 CREATE TABLE language_script_mapping (
2072 language_subtag varchar(25),
2073 script_subtag varchar(25),
2074 KEY `language_subtag` (`language_subtag`)
2075 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2077 DROP TABLE IF EXISTS `permissions`;
2078 CREATE TABLE `permissions` (
2079 `module_bit` int(11) NOT NULL DEFAULT 0,
2080 `code` varchar(64) DEFAULT NULL,
2081 `description` varchar(255) DEFAULT NULL,
2082 PRIMARY KEY (`module_bit`, `code`),
2083 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2084 ON DELETE CASCADE ON UPDATE CASCADE
2085 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2087 DROP TABLE IF EXISTS `serialitems`;
2088 CREATE TABLE `serialitems` (
2089 `itemnumber` int(11) NOT NULL,
2090 `serialid` int(11) NOT NULL,
2091 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2092 KEY `serialitems_sfk_1` (`serialid`),
2093 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
2094 CONSTRAINT serialitems_sfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE
2095 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2097 DROP TABLE IF EXISTS `user_permissions`;
2098 CREATE TABLE `user_permissions` (
2099 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2100 `module_bit` int(11) NOT NULL DEFAULT 0,
2101 `code` varchar(64) DEFAULT NULL,
2102 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2103 ON DELETE CASCADE ON UPDATE CASCADE,
2104 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2105 ON DELETE CASCADE ON UPDATE CASCADE
2106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2109 -- Table structure for table `tmp_holdsqueue`
2112 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2113 CREATE TABLE `tmp_holdsqueue` (
2114 `biblionumber` int(11) default NULL,
2115 `itemnumber` int(11) default NULL,
2116 `barcode` varchar(20) default NULL,
2117 `surname` mediumtext NOT NULL,
2120 `borrowernumber` int(11) NOT NULL,
2121 `cardnumber` varchar(16) default NULL,
2122 `reservedate` date default NULL,
2124 `itemcallnumber` varchar(255) default NULL,
2125 `holdingbranch` varchar(10) default NULL,
2126 `pickbranch` varchar(10) default NULL,
2128 `item_level_request` tinyint(4) NOT NULL default 0
2129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2132 -- Table structure for table `message_queue`
2135 DROP TABLE IF EXISTS `message_queue`;
2136 CREATE TABLE `message_queue` (
2137 `message_id` int(11) NOT NULL auto_increment,
2138 `borrowernumber` int(11) default NULL,
2141 `metadata` text DEFAULT NULL,
2142 `letter_code` varchar(64) DEFAULT NULL,
2143 `message_transport_type` varchar(20) NOT NULL,
2144 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2145 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2146 `to_address` mediumtext,
2147 `from_address` mediumtext,
2148 `content_type` text,
2149 KEY `message_id` (`message_id`),
2150 KEY `borrowernumber` (`borrowernumber`),
2151 KEY `message_transport_type` (`message_transport_type`),
2152 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2153 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2157 -- Table structure for table `message_transport_types`
2160 DROP TABLE IF EXISTS `message_transport_types`;
2161 CREATE TABLE `message_transport_types` (
2162 `message_transport_type` varchar(20) NOT NULL,
2163 PRIMARY KEY (`message_transport_type`)
2164 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2167 -- Table structure for table `message_attributes`
2170 DROP TABLE IF EXISTS `message_attributes`;
2171 CREATE TABLE `message_attributes` (
2172 `message_attribute_id` int(11) NOT NULL auto_increment,
2173 `message_name` varchar(40) NOT NULL default '',
2174 `takes_days` tinyint(1) NOT NULL default '0',
2175 PRIMARY KEY (`message_attribute_id`),
2176 UNIQUE KEY `message_name` (`message_name`)
2177 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2180 -- Table structure for table `message_transports`
2183 DROP TABLE IF EXISTS `message_transports`;
2184 CREATE TABLE `message_transports` (
2185 `message_attribute_id` int(11) NOT NULL,
2186 `message_transport_type` varchar(20) NOT NULL,
2187 `is_digest` tinyint(1) NOT NULL default '0',
2188 `letter_module` varchar(20) NOT NULL default '',
2189 `letter_code` varchar(20) NOT NULL default '',
2190 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2191 KEY `message_transport_type` (`message_transport_type`),
2192 KEY `letter_module` (`letter_module`,`letter_code`),
2193 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2194 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2195 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2196 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2199 -- Table structure for table `borrower_message_preferences`
2202 DROP TABLE IF EXISTS `borrower_message_preferences`;
2203 CREATE TABLE `borrower_message_preferences` (
2204 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2205 `borrowernumber` int(11) default NULL,
2206 `categorycode` varchar(10) default NULL,
2207 `message_attribute_id` int(11) default '0',
2208 `days_in_advance` int(11) default '0',
2209 `wants_digest` tinyint(1) NOT NULL default '0',
2210 PRIMARY KEY (`borrower_message_preference_id`),
2211 KEY `borrowernumber` (`borrowernumber`),
2212 KEY `categorycode` (`categorycode`),
2213 KEY `message_attribute_id` (`message_attribute_id`),
2214 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2215 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2216 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2217 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2220 -- Table structure for table `borrower_message_transport_preferences`
2223 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2224 CREATE TABLE `borrower_message_transport_preferences` (
2225 `borrower_message_preference_id` int(11) NOT NULL default '0',
2226 `message_transport_type` varchar(20) NOT NULL default '0',
2227 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2228 KEY `message_transport_type` (`message_transport_type`),
2229 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,
2230 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
2231 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2234 -- Table structure for the table branch_transfer_limits
2237 DROP TABLE IF EXISTS `branch_transfer_limits`;
2238 CREATE TABLE branch_transfer_limits (
2239 limitId int(8) NOT NULL auto_increment,
2240 toBranch varchar(10) NOT NULL,
2241 fromBranch varchar(10) NOT NULL,
2242 itemtype varchar(10) NULL,
2243 ccode varchar(10) NULL,
2244 PRIMARY KEY (limitId)
2245 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2248 -- Table structure for table `item_circulation_alert_preferences`
2251 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2252 CREATE TABLE `item_circulation_alert_preferences` (
2253 `id` int(11) NOT NULL auto_increment,
2254 `branchcode` varchar(10) NOT NULL,
2255 `categorycode` varchar(10) NOT NULL,
2256 `item_type` varchar(10) NOT NULL,
2257 `notification` varchar(16) NOT NULL,
2259 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2263 -- Table structure for table `messages`
2265 DROP TABLE IF EXISTS `messages`;
2266 CREATE TABLE `messages` (
2267 `message_id` int(11) NOT NULL auto_increment,
2268 `borrowernumber` int(11) NOT NULL,
2269 `branchcode` varchar(10) default NULL,
2270 `message_type` varchar(1) NOT NULL,
2271 `message` text NOT NULL,
2272 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2273 PRIMARY KEY (`message_id`)
2274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2277 -- Table structure for table `accountlines`
2280 DROP TABLE IF EXISTS `accountlines`;
2281 CREATE TABLE `accountlines` (
2282 `borrowernumber` int(11) NOT NULL default 0,
2283 `accountno` smallint(6) NOT NULL default 0,
2284 `itemnumber` int(11) default NULL,
2285 `date` date default NULL,
2286 `amount` decimal(28,6) default NULL,
2287 `description` mediumtext,
2288 `dispute` mediumtext,
2289 `accounttype` varchar(5) default NULL,
2290 `amountoutstanding` decimal(28,6) default NULL,
2291 `lastincrement` decimal(28,6) default NULL,
2292 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2293 `notify_id` int(11) NOT NULL default 0,
2294 `notify_level` int(2) NOT NULL default 0,
2295 KEY `acctsborridx` (`borrowernumber`),
2296 KEY `timeidx` (`timestamp`),
2297 KEY `itemnumber` (`itemnumber`),
2298 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2299 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2303 -- Table structure for table `accountoffsets`
2306 DROP TABLE IF EXISTS `accountoffsets`;
2307 CREATE TABLE `accountoffsets` (
2308 `borrowernumber` int(11) NOT NULL default 0,
2309 `accountno` smallint(6) NOT NULL default 0,
2310 `offsetaccount` smallint(6) NOT NULL default 0,
2311 `offsetamount` decimal(28,6) default NULL,
2312 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2313 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2317 -- Table structure for table `action_logs`
2320 DROP TABLE IF EXISTS `action_logs`;
2321 CREATE TABLE `action_logs` (
2322 `action_id` int(11) NOT NULL auto_increment,
2323 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2324 `user` int(11) NOT NULL default 0,
2327 `object` int(11) default NULL,
2329 PRIMARY KEY (`action_id`),
2330 KEY (`timestamp`,`user`)
2331 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2334 -- Table structure for table `alert`
2337 DROP TABLE IF EXISTS `alert`;
2338 CREATE TABLE `alert` (
2339 `alertid` int(11) NOT NULL auto_increment,
2340 `borrowernumber` int(11) NOT NULL default 0,
2341 `type` varchar(10) NOT NULL default '',
2342 `externalid` varchar(20) NOT NULL default '',
2343 PRIMARY KEY (`alertid`),
2344 KEY `borrowernumber` (`borrowernumber`),
2345 KEY `type` (`type`,`externalid`)
2346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2349 -- Table structure for table `aqbasketgroups`
2352 DROP TABLE IF EXISTS `aqbasketgroups`;
2353 CREATE TABLE `aqbasketgroups` (
2354 `id` int(11) NOT NULL auto_increment,
2355 `name` varchar(50) default NULL,
2356 `closed` tinyint(1) default NULL,
2357 `booksellerid` int(11) NOT NULL,
2358 `deliveryplace` varchar(10) default NULL,
2359 `freedeliveryplace` text default NULL,
2360 `deliverycomment` varchar(255) default NULL,
2361 `billingplace` varchar(10) default NULL,
2363 KEY `booksellerid` (`booksellerid`),
2364 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2368 -- Table structure for table `aqbasket`
2371 DROP TABLE IF EXISTS `aqbasket`;
2372 CREATE TABLE `aqbasket` (
2373 `basketno` int(11) NOT NULL auto_increment,
2374 `basketname` varchar(50) default NULL,
2376 `booksellernote` mediumtext,
2377 `contractnumber` int(11),
2378 `creationdate` date default NULL,
2379 `closedate` date default NULL,
2380 `booksellerid` int(11) NOT NULL default 1,
2381 `authorisedby` varchar(10) default NULL,
2382 `booksellerinvoicenumber` mediumtext,
2383 `basketgroupid` int(11),
2384 PRIMARY KEY (`basketno`),
2385 KEY `booksellerid` (`booksellerid`),
2386 KEY `basketgroupid` (`basketgroupid`),
2387 KEY `contractnumber` (`contractnumber`),
2388 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2389 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2390 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2391 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2394 -- Table structure for table `aqbooksellers`
2397 DROP TABLE IF EXISTS `aqbooksellers`;
2398 CREATE TABLE `aqbooksellers` (
2399 `id` int(11) NOT NULL auto_increment,
2400 `name` mediumtext NOT NULL,
2401 `address1` mediumtext,
2402 `address2` mediumtext,
2403 `address3` mediumtext,
2404 `address4` mediumtext,
2405 `phone` varchar(30) default NULL,
2406 `accountnumber` mediumtext,
2407 `othersupplier` mediumtext,
2408 `currency` varchar(3) NOT NULL default '',
2409 `booksellerfax` mediumtext,
2411 `bookselleremail` mediumtext,
2412 `booksellerurl` mediumtext,
2413 `contact` varchar(100) default NULL,
2414 `postal` mediumtext,
2415 `url` varchar(255) default NULL,
2416 `contpos` varchar(100) default NULL,
2417 `contphone` varchar(100) default NULL,
2418 `contfax` varchar(100) default NULL,
2419 `contaltphone` varchar(100) default NULL,
2420 `contemail` varchar(100) default NULL,
2421 `contnotes` mediumtext,
2422 `active` tinyint(4) default NULL,
2423 `listprice` varchar(10) default NULL,
2424 `invoiceprice` varchar(10) default NULL,
2425 `gstreg` tinyint(4) default NULL,
2426 `listincgst` tinyint(4) default NULL,
2427 `invoiceincgst` tinyint(4) default NULL,
2428 `gstrate` decimal(6,4) default NULL,
2429 `discount` float(6,4) default NULL,
2430 `fax` varchar(50) default NULL,
2432 KEY `listprice` (`listprice`),
2433 KEY `invoiceprice` (`invoiceprice`),
2434 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2435 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2436 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2439 -- Table structure for table `aqbudgets`
2442 DROP TABLE IF EXISTS `aqbudgets`;
2443 CREATE TABLE `aqbudgets` (
2444 `budget_id` int(11) NOT NULL auto_increment,
2445 `budget_parent_id` int(11) default NULL,
2446 `budget_code` varchar(30) default NULL,
2447 `budget_name` varchar(80) default NULL,
2448 `budget_branchcode` varchar(10) default NULL,
2449 `budget_amount` decimal(28,6) NULL default '0.00',
2450 `budget_encumb` decimal(28,6) NULL default '0.00',
2451 `budget_expend` decimal(28,6) NULL default '0.00',
2452 `budget_notes` mediumtext,
2453 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2454 `budget_period_id` int(11) default NULL,
2455 `sort1_authcat` varchar(80) default NULL,
2456 `sort2_authcat` varchar(80) default NULL,
2457 `budget_owner_id` int(11) default NULL,
2458 `budget_permission` int(1) default '0',
2459 PRIMARY KEY (`budget_id`)
2460 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2464 -- Table structure for table `aqbudgetperiods`
2468 DROP TABLE IF EXISTS `aqbudgetperiods`;
2469 CREATE TABLE `aqbudgetperiods` (
2470 `budget_period_id` int(11) NOT NULL auto_increment,
2471 `budget_period_startdate` date NOT NULL,
2472 `budget_period_enddate` date NOT NULL,
2473 `budget_period_active` tinyint(1) default '0',
2474 `budget_period_description` mediumtext,
2475 `budget_period_total` decimal(28,6),
2476 `budget_period_locked` tinyint(1) default NULL,
2477 `sort1_authcat` varchar(10) default NULL,
2478 `sort2_authcat` varchar(10) default NULL,
2479 PRIMARY KEY (`budget_period_id`)
2480 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2483 -- Table structure for table `aqbudgets_planning`
2486 DROP TABLE IF EXISTS `aqbudgets_planning`;
2487 CREATE TABLE `aqbudgets_planning` (
2488 `plan_id` int(11) NOT NULL auto_increment,
2489 `budget_id` int(11) NOT NULL,
2490 `budget_period_id` int(11) NOT NULL,
2491 `estimated_amount` decimal(28,6) default NULL,
2492 `authcat` varchar(30) NOT NULL,
2493 `authvalue` varchar(30) NOT NULL,
2494 `display` tinyint(1) DEFAULT 1,
2495 PRIMARY KEY (`plan_id`),
2496 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2497 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2500 -- Table structure for table 'aqcontract'
2503 DROP TABLE IF EXISTS `aqcontract`;
2504 CREATE TABLE `aqcontract` (
2505 `contractnumber` int(11) NOT NULL auto_increment,
2506 `contractstartdate` date default NULL,
2507 `contractenddate` date default NULL,
2508 `contractname` varchar(50) default NULL,
2509 `contractdescription` mediumtext,
2510 `booksellerid` int(11) not NULL,
2511 PRIMARY KEY (`contractnumber`),
2512 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2513 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2514 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2517 -- Table structure for table `aqorderdelivery`
2520 DROP TABLE IF EXISTS `aqorderdelivery`;
2521 CREATE TABLE `aqorderdelivery` (
2522 `ordernumber` date default NULL,
2523 `deliverynumber` smallint(6) NOT NULL default 0,
2524 `deliverydate` varchar(18) default NULL,
2525 `qtydelivered` smallint(6) default NULL,
2526 `deliverycomments` mediumtext
2527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2530 -- Table structure for table `aqorders`
2533 DROP TABLE IF EXISTS `aqorders`;
2534 CREATE TABLE `aqorders` (
2535 `ordernumber` int(11) NOT NULL auto_increment,
2536 `biblionumber` int(11) default NULL,
2537 `entrydate` date default NULL,
2538 `quantity` smallint(6) default NULL,
2539 `currency` varchar(3) default NULL,
2540 `listprice` decimal(28,6) default NULL,
2541 `totalamount` decimal(28,6) default NULL,
2542 `datereceived` date default NULL,
2543 `booksellerinvoicenumber` mediumtext,
2544 `freight` decimal(28,6) default NULL,
2545 `unitprice` decimal(28,6) default NULL,
2546 `quantityreceived` smallint(6) NOT NULL default 0,
2547 `cancelledby` varchar(10) default NULL,
2548 `datecancellationprinted` date default NULL,
2550 `supplierreference` mediumtext,
2551 `purchaseordernumber` mediumtext,
2552 `subscription` tinyint(1) default NULL,
2553 `serialid` varchar(30) default NULL,
2554 `basketno` int(11) default NULL,
2555 `biblioitemnumber` int(11) default NULL,
2556 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2557 `rrp` decimal(13,2) default NULL,
2558 `ecost` decimal(13,2) default NULL,
2559 `gst` decimal(13,2) default NULL,
2560 `budget_id` int(11) NOT NULL,
2561 `budgetgroup_id` int(11) NOT NULL,
2562 `budgetdate` date default NULL,
2563 `sort1` varchar(80) default NULL,
2564 `sort2` varchar(80) default NULL,
2565 `sort1_authcat` varchar(10) default NULL,
2566 `sort2_authcat` varchar(10) default NULL,
2567 `uncertainprice` tinyint(1),
2568 PRIMARY KEY (`ordernumber`),
2569 KEY `basketno` (`basketno`),
2570 KEY `biblionumber` (`biblionumber`),
2571 KEY `budget_id` (`budget_id`),
2572 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2573 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2574 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2578 -- Table structure for table `aqorders_items`
2581 DROP TABLE IF EXISTS `aqorders_items`;
2582 CREATE TABLE `aqorders_items` (
2583 `ordernumber` int(11) NOT NULL,
2584 `itemnumber` int(11) NOT NULL,
2585 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2586 PRIMARY KEY (`itemnumber`),
2587 KEY `ordernumber` (`ordernumber`)
2588 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2591 -- Table structure for table `fieldmapping`
2594 DROP TABLE IF EXISTS `fieldmapping`;
2595 CREATE TABLE `fieldmapping` (
2596 `id` int(11) NOT NULL auto_increment,
2597 `field` varchar(255) NOT NULL,
2598 `frameworkcode` char(4) NOT NULL default '',
2599 `fieldcode` char(3) NOT NULL,
2600 `subfieldcode` char(1) NOT NULL,
2602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2605 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2606 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2607 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2608 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2609 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2610 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2611 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2612 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;