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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
111 -- Table structure for table `biblio`
114 DROP TABLE IF EXISTS `biblio`;
115 CREATE TABLE `biblio` (
116 `biblionumber` int(11) NOT NULL auto_increment,
117 `frameworkcode` varchar(4) NOT NULL default '',
120 `unititle` mediumtext,
122 `serial` tinyint(1) default NULL,
123 `seriestitle` mediumtext,
124 `copyrightdate` smallint(6) default NULL,
125 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
126 `datecreated` DATE NOT NULL,
127 `abstract` mediumtext,
128 PRIMARY KEY (`biblionumber`),
129 KEY `blbnoidx` (`biblionumber`)
130 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
133 -- Table structure for table `biblio_framework`
136 DROP TABLE IF EXISTS `biblio_framework`;
137 CREATE TABLE `biblio_framework` (
138 `frameworkcode` varchar(4) NOT NULL default '',
139 `frameworktext` varchar(255) NOT NULL default '',
140 PRIMARY KEY (`frameworkcode`)
141 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
144 -- Table structure for table `biblioitems`
147 DROP TABLE IF EXISTS `biblioitems`;
148 CREATE TABLE `biblioitems` (
149 `biblioitemnumber` int(11) NOT NULL auto_increment,
150 `biblionumber` int(11) NOT NULL default 0,
153 `itemtype` varchar(10) default NULL,
154 `isbn` varchar(30) default NULL,
155 `issn` varchar(9) default NULL,
156 `publicationyear` text,
157 `publishercode` varchar(255) default NULL,
158 `volumedate` date default NULL,
160 `collectiontitle` mediumtext default NULL,
161 `collectionissn` text default NULL,
162 `collectionvolume` mediumtext default NULL,
163 `editionstatement` text default NULL,
164 `editionresponsibility` text default NULL,
165 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
166 `illus` varchar(255) default NULL,
167 `pages` varchar(255) default NULL,
169 `size` varchar(255) default NULL,
170 `place` varchar(255) default NULL,
171 `lccn` varchar(25) default NULL,
173 `url` varchar(255) default NULL,
174 `cn_source` varchar(10) default NULL,
175 `cn_class` varchar(30) default NULL,
176 `cn_item` varchar(10) default NULL,
177 `cn_suffix` varchar(10) default NULL,
178 `cn_sort` varchar(30) default NULL,
179 `totalissues` int(10),
180 `marcxml` longtext NOT NULL,
181 PRIMARY KEY (`biblioitemnumber`),
182 KEY `bibinoidx` (`biblioitemnumber`),
183 KEY `bibnoidx` (`biblionumber`),
185 KEY `publishercode` (`publishercode`),
186 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
190 -- Table structure for table `borrowers`
193 DROP TABLE IF EXISTS `borrowers`;
194 CREATE TABLE `borrowers` (
195 `borrowernumber` int(11) NOT NULL auto_increment,
196 `cardnumber` varchar(16) default NULL,
197 `surname` mediumtext NOT NULL,
200 `othernames` mediumtext,
202 `streetnumber` varchar(10) default NULL,
203 `streettype` varchar(50) default NULL,
204 `address` mediumtext NOT NULL,
206 `city` mediumtext NOT NULL,
207 `zipcode` varchar(25) default NULL,
211 `mobile` varchar(50) default NULL,
215 `B_streetnumber` varchar(10) default NULL,
216 `B_streettype` varchar(50) default NULL,
217 `B_address` varchar(100) default NULL,
218 `B_address2` text default NULL,
220 `B_zipcode` varchar(25) default NULL,
223 `B_phone` mediumtext,
224 `dateofbirth` date default NULL,
225 `branchcode` varchar(10) NOT NULL default '',
226 `categorycode` varchar(10) NOT NULL default '',
227 `dateenrolled` date default NULL,
228 `dateexpiry` date default NULL,
229 `gonenoaddress` tinyint(1) default NULL,
230 `lost` tinyint(1) default NULL,
231 `debarred` tinyint(1) default NULL,
232 `contactname` mediumtext,
233 `contactfirstname` text,
235 `guarantorid` int(11) default NULL,
236 `borrowernotes` mediumtext,
237 `relationship` varchar(100) default NULL,
238 `ethnicity` varchar(50) default NULL,
239 `ethnotes` varchar(255) default NULL,
240 `sex` varchar(1) default NULL,
241 `password` varchar(30) default NULL,
242 `flags` int(11) default NULL,
243 `userid` varchar(30) default NULL,
244 `opacnote` mediumtext,
245 `contactnote` varchar(255) default NULL,
246 `sort1` varchar(80) default NULL,
247 `sort2` varchar(80) default NULL,
248 `altcontactfirstname` varchar(255) default NULL,
249 `altcontactsurname` varchar(255) default NULL,
250 `altcontactaddress1` varchar(255) default NULL,
251 `altcontactaddress2` varchar(255) default NULL,
252 `altcontactaddress3` varchar(255) default NULL,
253 `altcontactzipcode` varchar(50) default NULL,
254 `altcontactcountry` text default NULL,
255 `altcontactphone` varchar(50) default NULL,
256 `smsalertnumber` varchar(50) default NULL,
257 UNIQUE KEY `cardnumber` (`cardnumber`),
258 PRIMARY KEY `borrowernumber` (`borrowernumber`),
259 KEY `categorycode` (`categorycode`),
260 KEY `branchcode` (`branchcode`),
261 KEY `userid` (`userid`),
262 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
263 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
264 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
267 -- Table structure for table `borrower_attribute_types`
270 DROP TABLE IF EXISTS `borrower_attribute_types`;
271 CREATE TABLE `borrower_attribute_types` (
272 `code` varchar(10) NOT NULL,
273 `description` varchar(255) NOT NULL,
274 `repeatable` tinyint(1) NOT NULL default 0,
275 `unique_id` tinyint(1) NOT NULL default 0,
276 `opac_display` tinyint(1) NOT NULL default 0,
277 `password_allowed` tinyint(1) NOT NULL default 0,
278 `staff_searchable` tinyint(1) NOT NULL default 0,
279 `authorised_value_category` varchar(10) default NULL,
281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
284 -- Table structure for table `borrower_attributes`
287 DROP TABLE IF EXISTS `borrower_attributes`;
288 CREATE TABLE `borrower_attributes` (
289 `borrowernumber` int(11) NOT NULL,
290 `code` varchar(10) NOT NULL,
291 `attribute` varchar(64) default NULL,
292 `password` varchar(64) default NULL,
293 KEY `borrowernumber` (`borrowernumber`),
294 KEY `code_attribute` (`code`, `attribute`),
295 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
296 ON DELETE CASCADE ON UPDATE CASCADE,
297 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
298 ON DELETE CASCADE ON UPDATE CASCADE
299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
301 DROP TABLE IF EXISTS `branch_item_rules`;
302 CREATE TABLE `branch_item_rules` (
303 `branchcode` varchar(10) NOT NULL,
304 `itemtype` varchar(10) NOT NULL,
305 `holdallowed` tinyint(1) default NULL,
306 PRIMARY KEY (`itemtype`,`branchcode`),
307 KEY `branch_item_rules_ibfk_2` (`branchcode`),
308 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
309 ON DELETE CASCADE ON UPDATE CASCADE,
310 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
311 ON DELETE CASCADE ON UPDATE CASCADE
312 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
315 -- Table structure for table `branchcategories`
318 DROP TABLE IF EXISTS `branchcategories`;
319 CREATE TABLE `branchcategories` (
320 `categorycode` varchar(10) NOT NULL default '',
321 `categoryname` varchar(32),
322 `codedescription` mediumtext,
323 `categorytype` varchar(16),
324 PRIMARY KEY (`categorycode`)
325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
328 -- Table structure for table `branches`
331 DROP TABLE IF EXISTS `branches`;
332 CREATE TABLE `branches` (
333 `branchcode` varchar(10) NOT NULL default '',
334 `branchname` mediumtext NOT NULL,
335 `branchaddress1` mediumtext,
336 `branchaddress2` mediumtext,
337 `branchaddress3` mediumtext,
338 `branchzip` varchar(25) default NULL,
339 `branchcity` mediumtext,
340 `branchcountry` text,
341 `branchphone` mediumtext,
342 `branchfax` mediumtext,
343 `branchemail` mediumtext,
344 `branchurl` mediumtext,
345 `issuing` tinyint(4) default NULL,
346 `branchip` varchar(15) default NULL,
347 `branchprinter` varchar(100) default NULL,
348 `branchnotes` mediumtext,
349 UNIQUE KEY `branchcode` (`branchcode`)
350 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
353 -- Table structure for table `branchrelations`
356 DROP TABLE IF EXISTS `branchrelations`;
357 CREATE TABLE `branchrelations` (
358 `branchcode` varchar(10) NOT NULL default '',
359 `categorycode` varchar(10) NOT NULL default '',
360 PRIMARY KEY (`branchcode`,`categorycode`),
361 KEY `branchcode` (`branchcode`),
362 KEY `categorycode` (`categorycode`),
363 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
364 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
368 -- Table structure for table `branchtransfers`
371 DROP TABLE IF EXISTS `branchtransfers`;
372 CREATE TABLE `branchtransfers` (
373 `itemnumber` int(11) NOT NULL default 0,
374 `datesent` datetime default NULL,
375 `frombranch` varchar(10) NOT NULL default '',
376 `datearrived` datetime default NULL,
377 `tobranch` varchar(10) NOT NULL default '',
378 `comments` mediumtext,
379 KEY `frombranch` (`frombranch`),
380 KEY `tobranch` (`tobranch`),
381 KEY `itemnumber` (`itemnumber`),
382 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
383 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
384 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
389 -- Table structure for table `browser`
391 DROP TABLE IF EXISTS `browser`;
392 CREATE TABLE `browser` (
393 `level` int(11) NOT NULL,
394 `classification` varchar(20) NOT NULL,
395 `description` varchar(255) NOT NULL,
396 `number` bigint(20) NOT NULL,
397 `endnode` tinyint(4) NOT NULL
398 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
401 -- Table structure for table `categories`
404 DROP TABLE IF EXISTS `categories`;
405 CREATE TABLE `categories` (
406 `categorycode` varchar(10) NOT NULL default '',
407 `description` mediumtext,
408 `enrolmentperiod` smallint(6) default NULL,
409 `enrolmentperioddate` DATE NULL DEFAULT NULL,
410 `upperagelimit` smallint(6) default NULL,
411 `dateofbirthrequired` tinyint(1) default NULL,
412 `finetype` varchar(30) default NULL,
413 `bulk` tinyint(1) default NULL,
414 `enrolmentfee` decimal(28,6) default NULL,
415 `overduenoticerequired` tinyint(1) default NULL,
416 `issuelimit` smallint(6) default NULL,
417 `reservefee` decimal(28,6) default NULL,
418 `category_type` varchar(1) NOT NULL default 'A',
419 PRIMARY KEY (`categorycode`),
420 UNIQUE KEY `categorycode` (`categorycode`)
421 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
424 -- Table structure for table `borrower_branch_circ_rules`
427 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
428 CREATE TABLE `branch_borrower_circ_rules` (
429 `branchcode` VARCHAR(10) NOT NULL,
430 `categorycode` VARCHAR(10) NOT NULL,
431 `maxissueqty` int(4) default NULL,
432 PRIMARY KEY (`categorycode`, `branchcode`),
433 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
434 ON DELETE CASCADE ON UPDATE CASCADE,
435 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
436 ON DELETE CASCADE ON UPDATE CASCADE
437 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
440 -- Table structure for table `default_borrower_circ_rules`
443 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
444 CREATE TABLE `default_borrower_circ_rules` (
445 `categorycode` VARCHAR(10) NOT NULL,
446 `maxissueqty` int(4) default NULL,
447 PRIMARY KEY (`categorycode`),
448 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
449 ON DELETE CASCADE ON UPDATE CASCADE
450 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
453 -- Table structure for table `default_branch_circ_rules`
456 DROP TABLE IF EXISTS `default_branch_circ_rules`;
457 CREATE TABLE `default_branch_circ_rules` (
458 `branchcode` VARCHAR(10) NOT NULL,
459 `maxissueqty` int(4) default NULL,
460 `holdallowed` tinyint(1) default NULL,
461 PRIMARY KEY (`branchcode`),
462 CONSTRAINT `default_branch_circ_rules_ibfk_1` 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_branch_item_rules`
470 CREATE TABLE `default_branch_item_rules` (
471 `itemtype` varchar(10) NOT NULL,
472 `holdallowed` tinyint(1) default NULL,
473 PRIMARY KEY (`itemtype`),
474 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
475 ON DELETE CASCADE ON UPDATE CASCADE
476 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
479 -- Table structure for table `default_circ_rules`
482 DROP TABLE IF EXISTS `default_circ_rules`;
483 CREATE TABLE `default_circ_rules` (
484 `singleton` enum('singleton') NOT NULL default 'singleton',
485 `maxissueqty` int(4) default NULL,
486 `holdallowed` int(1) default NULL,
487 PRIMARY KEY (`singleton`)
488 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
491 -- Table structure for table `cities`
494 DROP TABLE IF EXISTS `cities`;
495 CREATE TABLE `cities` (
496 `cityid` int(11) NOT NULL auto_increment,
497 `city_name` varchar(100) NOT NULL default '',
498 `city_zipcode` varchar(20) default NULL,
499 PRIMARY KEY (`cityid`)
500 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
503 -- Table structure for table `class_sort_rules`
506 DROP TABLE IF EXISTS `class_sort_rules`;
507 CREATE TABLE `class_sort_rules` (
508 `class_sort_rule` varchar(10) NOT NULL default '',
509 `description` mediumtext,
510 `sort_routine` varchar(30) NOT NULL default '',
511 PRIMARY KEY (`class_sort_rule`),
512 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
513 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
516 -- Table structure for table `class_sources`
519 DROP TABLE IF EXISTS `class_sources`;
520 CREATE TABLE `class_sources` (
521 `cn_source` varchar(10) NOT NULL default '',
522 `description` mediumtext,
523 `used` tinyint(4) NOT NULL default 0,
524 `class_sort_rule` varchar(10) NOT NULL default '',
525 PRIMARY KEY (`cn_source`),
526 UNIQUE KEY `cn_source_idx` (`cn_source`),
527 KEY `used_idx` (`used`),
528 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
529 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
532 -- Table structure for table `currency`
535 DROP TABLE IF EXISTS `currency`;
536 CREATE TABLE `currency` (
537 `currency` varchar(10) NOT NULL default '',
538 `symbol` varchar(5) default NULL,
539 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
540 `rate` float(7,5) default NULL,
541 `active` tinyint(1) default NULL,
542 PRIMARY KEY (`currency`)
543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
546 -- Table structure for table `deletedbiblio`
549 DROP TABLE IF EXISTS `deletedbiblio`;
550 CREATE TABLE `deletedbiblio` (
551 `biblionumber` int(11) NOT NULL default 0,
552 `frameworkcode` varchar(4) NOT NULL default '',
555 `unititle` mediumtext,
557 `serial` tinyint(1) default NULL,
558 `seriestitle` mediumtext,
559 `copyrightdate` smallint(6) default NULL,
560 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
561 `datecreated` DATE NOT NULL,
562 `abstract` mediumtext,
563 PRIMARY KEY (`biblionumber`),
564 KEY `blbnoidx` (`biblionumber`)
565 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
568 -- Table structure for table `deletedbiblioitems`
571 DROP TABLE IF EXISTS `deletedbiblioitems`;
572 CREATE TABLE `deletedbiblioitems` (
573 `biblioitemnumber` int(11) NOT NULL default 0,
574 `biblionumber` int(11) NOT NULL default 0,
577 `itemtype` varchar(10) default NULL,
578 `isbn` varchar(30) default NULL,
579 `issn` varchar(9) default NULL,
580 `publicationyear` text,
581 `publishercode` varchar(255) default NULL,
582 `volumedate` date default NULL,
584 `collectiontitle` mediumtext default NULL,
585 `collectionissn` text default NULL,
586 `collectionvolume` mediumtext default NULL,
587 `editionstatement` text default NULL,
588 `editionresponsibility` text default NULL,
589 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
590 `illus` varchar(255) default NULL,
591 `pages` varchar(255) default NULL,
593 `size` varchar(255) default NULL,
594 `place` varchar(255) default NULL,
595 `lccn` varchar(25) default NULL,
597 `url` varchar(255) default NULL,
598 `cn_source` varchar(10) default NULL,
599 `cn_class` varchar(30) default NULL,
600 `cn_item` varchar(10) default NULL,
601 `cn_suffix` varchar(10) default NULL,
602 `cn_sort` varchar(30) default NULL,
603 `totalissues` int(10),
604 `marcxml` longtext NOT NULL,
605 PRIMARY KEY (`biblioitemnumber`),
606 KEY `bibinoidx` (`biblioitemnumber`),
607 KEY `bibnoidx` (`biblionumber`),
609 KEY `publishercode` (`publishercode`)
610 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
613 -- Table structure for table `deletedborrowers`
616 DROP TABLE IF EXISTS `deletedborrowers`;
617 CREATE TABLE `deletedborrowers` (
618 `borrowernumber` int(11) NOT NULL default 0,
619 `cardnumber` varchar(9) NOT NULL default '',
620 `surname` mediumtext NOT NULL,
623 `othernames` mediumtext,
625 `streetnumber` varchar(10) default NULL,
626 `streettype` varchar(50) default NULL,
627 `address` mediumtext NOT NULL,
629 `city` mediumtext NOT NULL,
630 `zipcode` varchar(25) default NULL,
634 `mobile` varchar(50) default NULL,
638 `B_streetnumber` varchar(10) default NULL,
639 `B_streettype` varchar(50) default NULL,
640 `B_address` varchar(100) default NULL,
641 `B_address2` text default NULL,
643 `B_zipcode` varchar(25) default NULL,
646 `B_phone` mediumtext,
647 `dateofbirth` date default NULL,
648 `branchcode` varchar(10) NOT NULL default '',
649 `categorycode` varchar(10) default NULL,
650 `dateenrolled` date default NULL,
651 `dateexpiry` date default NULL,
652 `gonenoaddress` tinyint(1) default NULL,
653 `lost` tinyint(1) default NULL,
654 `debarred` tinyint(1) default NULL,
655 `contactname` mediumtext,
656 `contactfirstname` text,
658 `guarantorid` int(11) default NULL,
659 `borrowernotes` mediumtext,
660 `relationship` varchar(100) default NULL,
661 `ethnicity` varchar(50) default NULL,
662 `ethnotes` varchar(255) default NULL,
663 `sex` varchar(1) default NULL,
664 `password` varchar(30) default NULL,
665 `flags` int(11) default NULL,
666 `userid` varchar(30) default NULL,
667 `opacnote` mediumtext,
668 `contactnote` varchar(255) default NULL,
669 `sort1` varchar(80) default NULL,
670 `sort2` varchar(80) default NULL,
671 `altcontactfirstname` varchar(255) default NULL,
672 `altcontactsurname` varchar(255) default NULL,
673 `altcontactaddress1` varchar(255) default NULL,
674 `altcontactaddress2` varchar(255) default NULL,
675 `altcontactaddress3` varchar(255) default NULL,
676 `altcontactzipcode` varchar(50) default NULL,
677 `altcontactcountry` text default NULL,
678 `altcontactphone` varchar(50) default NULL,
679 `smsalertnumber` varchar(50) default NULL,
680 KEY `borrowernumber` (`borrowernumber`),
681 KEY `cardnumber` (`cardnumber`)
682 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
685 -- Table structure for table `deleteditems`
688 DROP TABLE IF EXISTS `deleteditems`;
689 CREATE TABLE `deleteditems` (
690 `itemnumber` int(11) NOT NULL default 0,
691 `biblionumber` int(11) NOT NULL default 0,
692 `biblioitemnumber` int(11) NOT NULL default 0,
693 `barcode` varchar(20) default NULL,
694 `dateaccessioned` date default NULL,
695 `booksellerid` mediumtext default NULL,
696 `homebranch` varchar(10) default NULL,
697 `price` decimal(8,2) default NULL,
698 `replacementprice` decimal(8,2) default NULL,
699 `replacementpricedate` date default NULL,
700 `datelastborrowed` date default NULL,
701 `datelastseen` date default NULL,
702 `stack` tinyint(1) default NULL,
703 `notforloan` tinyint(1) NOT NULL default 0,
704 `damaged` tinyint(1) NOT NULL default 0,
705 `itemlost` tinyint(1) NOT NULL default 0,
706 `wthdrawn` tinyint(1) NOT NULL default 0,
707 `itemcallnumber` varchar(255) default NULL,
708 `issues` smallint(6) default NULL,
709 `renewals` smallint(6) default NULL,
710 `reserves` smallint(6) default NULL,
711 `restricted` tinyint(1) default NULL,
712 `itemnotes` mediumtext,
713 `holdingbranch` varchar(10) default NULL,
714 `paidfor` mediumtext,
715 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
716 `location` varchar(80) default NULL,
717 `permanent_location` varchar(80) default NULL,
718 `onloan` date default NULL,
719 `cn_source` varchar(10) default NULL,
720 `cn_sort` varchar(30) default NULL,
721 `ccode` varchar(10) default NULL,
722 `materials` varchar(10) default NULL,
723 `uri` varchar(255) default NULL,
724 `itype` varchar(10) default NULL,
725 `more_subfields_xml` longtext default NULL,
726 `enumchron` varchar(80) default NULL,
727 `copynumber` varchar(32) default NULL,
728 `stocknumber` varchar(32) default NULL,
730 PRIMARY KEY (`itemnumber`),
731 KEY `delitembarcodeidx` (`barcode`),
732 KEY `delitemstocknumberidx` (`stocknumber`),
733 KEY `delitembinoidx` (`biblioitemnumber`),
734 KEY `delitembibnoidx` (`biblionumber`),
735 KEY `delhomebranch` (`homebranch`),
736 KEY `delholdingbranch` (`holdingbranch`)
737 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
740 -- Table structure for table `ethnicity`
743 DROP TABLE IF EXISTS `ethnicity`;
744 CREATE TABLE `ethnicity` (
745 `code` varchar(10) NOT NULL default '',
746 `name` varchar(255) default NULL,
748 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
751 -- Table structure for table `export_format`
754 DROP TABLE IF EXISTS `export_format`;
755 CREATE TABLE `export_format` (
756 `export_format_id` int(11) NOT NULL auto_increment,
757 `profile` varchar(255) NOT NULL,
758 `description` mediumtext NOT NULL,
759 `marcfields` mediumtext NOT NULL,
760 `csv_separator` varchar(2) NOT NULL,
761 `field_separator` varchar(2) NOT NULL,
762 `subfield_separator` varchar(2) NOT NULL,
763 `encoding` varchar(255) NOT NULL,
764 PRIMARY KEY (`export_format_id`)
765 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
769 -- Table structure for table `hold_fill_targets`
772 DROP TABLE IF EXISTS `hold_fill_targets`;
773 CREATE TABLE hold_fill_targets (
774 `borrowernumber` int(11) NOT NULL,
775 `biblionumber` int(11) NOT NULL,
776 `itemnumber` int(11) NOT NULL,
777 `source_branchcode` varchar(10) default NULL,
778 `item_level_request` tinyint(4) NOT NULL default 0,
779 PRIMARY KEY `itemnumber` (`itemnumber`),
780 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
781 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
782 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
783 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
784 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
785 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
786 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
787 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
788 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
789 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
792 -- Table structure for table `import_batches`
795 DROP TABLE IF EXISTS `import_batches`;
796 CREATE TABLE `import_batches` (
797 `import_batch_id` int(11) NOT NULL auto_increment,
798 `matcher_id` int(11) default NULL,
799 `template_id` int(11) default NULL,
800 `branchcode` varchar(10) default NULL,
801 `num_biblios` int(11) NOT NULL default 0,
802 `num_items` int(11) NOT NULL default 0,
803 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
804 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
805 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
806 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
807 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
808 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
809 `file_name` varchar(100),
810 `comments` mediumtext,
811 PRIMARY KEY (`import_batch_id`),
812 KEY `branchcode` (`branchcode`)
813 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
816 -- Table structure for table `import_records`
819 DROP TABLE IF EXISTS `import_records`;
820 CREATE TABLE `import_records` (
821 `import_record_id` int(11) NOT NULL auto_increment,
822 `import_batch_id` int(11) NOT NULL,
823 `branchcode` varchar(10) default NULL,
824 `record_sequence` int(11) NOT NULL default 0,
825 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
826 `import_date` DATE default NULL,
827 `marc` longblob NOT NULL,
828 `marcxml` longtext NOT NULL,
829 `marcxml_old` longtext NOT NULL,
830 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
831 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
832 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
833 `import_error` mediumtext,
834 `encoding` varchar(40) NOT NULL default '',
835 `z3950random` varchar(40) default NULL,
836 PRIMARY KEY (`import_record_id`),
837 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
838 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
839 KEY `branchcode` (`branchcode`),
840 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
841 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
844 -- Table structure for `import_record_matches`
846 DROP TABLE IF EXISTS `import_record_matches`;
847 CREATE TABLE `import_record_matches` (
848 `import_record_id` int(11) NOT NULL,
849 `candidate_match_id` int(11) NOT NULL,
850 `score` int(11) NOT NULL default 0,
851 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
852 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
853 KEY `record_score` (`import_record_id`, `score`)
854 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
857 -- Table structure for table `import_biblios`
860 DROP TABLE IF EXISTS `import_biblios`;
861 CREATE TABLE `import_biblios` (
862 `import_record_id` int(11) NOT NULL,
863 `matched_biblionumber` int(11) default NULL,
864 `control_number` varchar(25) default NULL,
865 `original_source` varchar(25) default NULL,
866 `title` varchar(128) default NULL,
867 `author` varchar(80) default NULL,
868 `isbn` varchar(30) default NULL,
869 `issn` varchar(9) default NULL,
870 `has_items` tinyint(1) NOT NULL default 0,
871 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
872 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
873 KEY `matched_biblionumber` (`matched_biblionumber`),
874 KEY `title` (`title`),
876 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
879 -- Table structure for table `import_items`
882 DROP TABLE IF EXISTS `import_items`;
883 CREATE TABLE `import_items` (
884 `import_items_id` int(11) NOT NULL auto_increment,
885 `import_record_id` int(11) NOT NULL,
886 `itemnumber` int(11) default NULL,
887 `branchcode` varchar(10) default NULL,
888 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
889 `marcxml` longtext NOT NULL,
890 `import_error` mediumtext,
891 PRIMARY KEY (`import_items_id`),
892 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
893 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
894 KEY `itemnumber` (`itemnumber`),
895 KEY `branchcode` (`branchcode`)
896 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
899 -- Table structure for table `issues`
902 DROP TABLE IF EXISTS `issues`;
903 CREATE TABLE `issues` (
904 `borrowernumber` int(11) default NULL,
905 `itemnumber` int(11) default NULL,
906 `date_due` date default NULL,
907 `branchcode` varchar(10) default NULL,
908 `issuingbranch` varchar(18) default NULL,
909 `returndate` date default NULL,
910 `lastreneweddate` date default NULL,
911 `return` varchar(4) default NULL,
912 `renewals` tinyint(4) default NULL,
913 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
914 `issuedate` date default NULL,
915 KEY `issuesborridx` (`borrowernumber`),
916 KEY `issuesitemidx` (`itemnumber`),
917 KEY `bordate` (`borrowernumber`,`timestamp`),
918 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
919 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
920 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
923 -- Table structure for table `issuingrules`
926 DROP TABLE IF EXISTS `issuingrules`;
927 CREATE TABLE `issuingrules` (
928 `categorycode` varchar(10) NOT NULL default '',
929 `itemtype` varchar(10) NOT NULL default '',
930 `restrictedtype` tinyint(1) default NULL,
931 `rentaldiscount` decimal(28,6) default NULL,
932 `reservecharge` decimal(28,6) default NULL,
933 `fine` decimal(28,6) default NULL,
934 `finedays` int(11) default NULL,
935 `firstremind` int(11) default NULL,
936 `chargeperiod` int(11) default NULL,
937 `accountsent` int(11) default NULL,
938 `chargename` varchar(100) default NULL,
939 `maxissueqty` int(4) default NULL,
940 `issuelength` int(4) default NULL,
941 `renewalsallowed` smallint(6) NOT NULL default "0",
942 `reservesallowed` smallint(6) NOT NULL default "0",
943 `branchcode` varchar(10) NOT NULL default '',
944 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
945 KEY `categorycode` (`categorycode`),
946 KEY `itemtype` (`itemtype`)
947 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
950 -- Table structure for table `items`
953 DROP TABLE IF EXISTS `items`;
954 CREATE TABLE `items` (
955 `itemnumber` int(11) NOT NULL auto_increment,
956 `biblionumber` int(11) NOT NULL default 0,
957 `biblioitemnumber` int(11) NOT NULL default 0,
958 `barcode` varchar(20) default NULL,
959 `dateaccessioned` date default NULL,
960 `booksellerid` mediumtext default NULL,
961 `homebranch` varchar(10) default NULL,
962 `price` decimal(8,2) default NULL,
963 `replacementprice` decimal(8,2) default NULL,
964 `replacementpricedate` date default NULL,
965 `datelastborrowed` date default NULL,
966 `datelastseen` date default NULL,
967 `stack` tinyint(1) default NULL,
968 `notforloan` tinyint(1) NOT NULL default 0,
969 `damaged` tinyint(1) NOT NULL default 0,
970 `itemlost` tinyint(1) NOT NULL default 0,
971 `wthdrawn` tinyint(1) NOT NULL default 0,
972 `itemcallnumber` varchar(255) default NULL,
973 `issues` smallint(6) default NULL,
974 `renewals` smallint(6) default NULL,
975 `reserves` smallint(6) default NULL,
976 `restricted` tinyint(1) default NULL,
977 `itemnotes` mediumtext,
978 `holdingbranch` varchar(10) default NULL,
979 `paidfor` mediumtext,
980 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
981 `location` varchar(80) default NULL,
982 `permanent_location` varchar(80) default NULL,
983 `onloan` date default NULL,
984 `cn_source` varchar(10) default NULL,
985 `cn_sort` varchar(30) default NULL,
986 `ccode` varchar(10) default NULL,
987 `materials` varchar(10) default NULL,
988 `uri` varchar(255) default NULL,
989 `itype` varchar(10) default NULL,
990 `more_subfields_xml` longtext default NULL,
991 `enumchron` varchar(80) default NULL,
992 `copynumber` varchar(32) default NULL,
993 `stocknumber` varchar(32) default NULL,
994 PRIMARY KEY (`itemnumber`),
995 UNIQUE KEY `itembarcodeidx` (`barcode`),
996 UNIQUE KEY `itemstocknumberidx` (`stocknumber`),
997 KEY `itembinoidx` (`biblioitemnumber`),
998 KEY `itembibnoidx` (`biblionumber`),
999 KEY `homebranch` (`homebranch`),
1000 KEY `holdingbranch` (`holdingbranch`),
1001 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1002 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1003 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1004 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1007 -- Table structure for table `itemtypes`
1010 DROP TABLE IF EXISTS `itemtypes`;
1011 CREATE TABLE `itemtypes` (
1012 `itemtype` varchar(10) NOT NULL default '',
1013 `description` mediumtext,
1014 `rentalcharge` double(16,4) default NULL,
1015 `notforloan` smallint(6) default NULL,
1016 `imageurl` varchar(200) default NULL,
1018 PRIMARY KEY (`itemtype`),
1019 UNIQUE KEY `itemtype` (`itemtype`)
1020 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1023 -- Table structure for table `creator_batches`
1026 DROP TABLE IF EXISTS `creator_batches`;
1027 SET @saved_cs_client = @@character_set_client;
1028 SET character_set_client = utf8;
1029 CREATE TABLE `creator_batches` (
1030 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1031 `batch_id` int(10) NOT NULL DEFAULT '1',
1032 `item_number` int(11) DEFAULT NULL,
1033 `borrower_number` int(11) DEFAULT NULL,
1034 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1035 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1036 `creator` char(15) NOT NULL DEFAULT 'Labels',
1037 PRIMARY KEY (`label_id`),
1038 KEY `branch_fk_constraint` (`branch_code`),
1039 KEY `item_fk_constraint` (`item_number`),
1040 KEY `borrower_fk_constraint` (`borrower_number`),
1041 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1042 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1043 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1044 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1047 -- Table structure for table `creator_images`
1050 DROP TABLE IF EXISTS `creator_images`;
1051 SET @saved_cs_client = @@character_set_client;
1052 SET character_set_client = utf8;
1053 CREATE TABLE `creator_images` (
1054 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1055 `imagefile` mediumblob,
1056 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1057 PRIMARY KEY (`image_id`),
1058 UNIQUE KEY `image_name_index` (`image_name`)
1059 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1062 -- Table structure for table `creator_layouts`
1065 DROP TABLE IF EXISTS `creator_layouts`;
1066 SET @saved_cs_client = @@character_set_client;
1067 SET character_set_client = utf8;
1068 CREATE TABLE `creator_layouts` (
1069 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1070 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1071 `start_label` int(2) NOT NULL DEFAULT '1',
1072 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1073 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1074 `guidebox` int(1) DEFAULT '0',
1075 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1076 `font_size` int(4) NOT NULL DEFAULT '10',
1077 `units` char(20) NOT NULL DEFAULT 'POINT',
1078 `callnum_split` int(1) DEFAULT '0',
1079 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1080 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1081 `layout_xml` text NOT NULL,
1082 `creator` char(15) NOT NULL DEFAULT 'Labels',
1083 PRIMARY KEY (`layout_id`)
1084 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1087 -- Table structure for table `creator_templates`
1090 DROP TABLE IF EXISTS `creator_templates`;
1091 SET @saved_cs_client = @@character_set_client;
1092 SET character_set_client = utf8;
1093 CREATE TABLE `creator_templates` (
1094 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1095 `profile_id` int(4) DEFAULT NULL,
1096 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1097 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1098 `page_width` float NOT NULL DEFAULT '0',
1099 `page_height` float NOT NULL DEFAULT '0',
1100 `label_width` float NOT NULL DEFAULT '0',
1101 `label_height` float NOT NULL DEFAULT '0',
1102 `top_text_margin` float NOT NULL DEFAULT '0',
1103 `left_text_margin` float NOT NULL DEFAULT '0',
1104 `top_margin` float NOT NULL DEFAULT '0',
1105 `left_margin` float NOT NULL DEFAULT '0',
1106 `cols` int(2) NOT NULL DEFAULT '0',
1107 `rows` int(2) NOT NULL DEFAULT '0',
1108 `col_gap` float NOT NULL DEFAULT '0',
1109 `row_gap` float NOT NULL DEFAULT '0',
1110 `units` char(20) NOT NULL DEFAULT 'POINT',
1111 `creator` char(15) NOT NULL DEFAULT 'Labels',
1112 PRIMARY KEY (`template_id`),
1113 KEY `template_profile_fk_constraint` (`profile_id`)
1114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1117 -- Table structure for table `letter`
1120 DROP TABLE IF EXISTS `letter`;
1121 CREATE TABLE `letter` (
1122 `module` varchar(20) NOT NULL default '',
1123 `code` varchar(20) NOT NULL default '',
1124 `name` varchar(100) NOT NULL default '',
1125 `title` varchar(200) NOT NULL default '',
1127 PRIMARY KEY (`module`,`code`)
1128 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1131 -- Table structure for table `marc_subfield_structure`
1134 DROP TABLE IF EXISTS `marc_subfield_structure`;
1135 CREATE TABLE `marc_subfield_structure` (
1136 `tagfield` varchar(3) NOT NULL default '',
1137 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1138 `liblibrarian` varchar(255) NOT NULL default '',
1139 `libopac` varchar(255) NOT NULL default '',
1140 `repeatable` tinyint(4) NOT NULL default 0,
1141 `mandatory` tinyint(4) NOT NULL default 0,
1142 `kohafield` varchar(40) default NULL,
1143 `tab` tinyint(1) default NULL,
1144 `authorised_value` varchar(20) default NULL,
1145 `authtypecode` varchar(20) default NULL,
1146 `value_builder` varchar(80) default NULL,
1147 `isurl` tinyint(1) default NULL,
1148 `hidden` tinyint(1) default NULL,
1149 `frameworkcode` varchar(4) NOT NULL default '',
1150 `seealso` varchar(1100) default NULL,
1151 `link` varchar(80) default NULL,
1152 `defaultvalue` text default NULL,
1153 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1154 KEY `kohafield_2` (`kohafield`),
1155 KEY `tab` (`frameworkcode`,`tab`),
1156 KEY `kohafield` (`frameworkcode`,`kohafield`)
1157 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1160 -- Table structure for table `marc_tag_structure`
1163 DROP TABLE IF EXISTS `marc_tag_structure`;
1164 CREATE TABLE `marc_tag_structure` (
1165 `tagfield` varchar(3) NOT NULL default '',
1166 `liblibrarian` varchar(255) NOT NULL default '',
1167 `libopac` varchar(255) NOT NULL default '',
1168 `repeatable` tinyint(4) NOT NULL default 0,
1169 `mandatory` tinyint(4) NOT NULL default 0,
1170 `authorised_value` varchar(10) default NULL,
1171 `frameworkcode` varchar(4) NOT NULL default '',
1172 PRIMARY KEY (`frameworkcode`,`tagfield`)
1173 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1176 -- Table structure for table `marc_matchers`
1179 DROP TABLE IF EXISTS `marc_matchers`;
1180 CREATE TABLE `marc_matchers` (
1181 `matcher_id` int(11) NOT NULL auto_increment,
1182 `code` varchar(10) NOT NULL default '',
1183 `description` varchar(255) NOT NULL default '',
1184 `record_type` varchar(10) NOT NULL default 'biblio',
1185 `threshold` int(11) NOT NULL default 0,
1186 PRIMARY KEY (`matcher_id`),
1187 KEY `code` (`code`),
1188 KEY `record_type` (`record_type`)
1189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1192 -- Table structure for table `matchpoints`
1194 DROP TABLE IF EXISTS `matchpoints`;
1195 CREATE TABLE `matchpoints` (
1196 `matcher_id` int(11) NOT NULL,
1197 `matchpoint_id` int(11) NOT NULL auto_increment,
1198 `search_index` varchar(30) NOT NULL default '',
1199 `score` int(11) NOT NULL default 0,
1200 PRIMARY KEY (`matchpoint_id`),
1201 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1202 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1203 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1207 -- Table structure for table `matchpoint_components`
1209 DROP TABLE IF EXISTS `matchpoint_components`;
1210 CREATE TABLE `matchpoint_components` (
1211 `matchpoint_id` int(11) NOT NULL,
1212 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1213 sequence int(11) NOT NULL default 0,
1214 tag varchar(3) NOT NULL default '',
1215 subfields varchar(40) NOT NULL default '',
1216 offset int(4) NOT NULL default 0,
1217 length int(4) NOT NULL default 0,
1218 PRIMARY KEY (`matchpoint_component_id`),
1219 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1220 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1221 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1225 -- Table structure for table `matcher_component_norms`
1227 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1228 CREATE TABLE `matchpoint_component_norms` (
1229 `matchpoint_component_id` int(11) NOT NULL,
1230 `sequence` int(11) NOT NULL default 0,
1231 `norm_routine` varchar(50) NOT NULL default '',
1232 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1233 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1234 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1235 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1238 -- Table structure for table `matcher_matchpoints`
1240 DROP TABLE IF EXISTS `matcher_matchpoints`;
1241 CREATE TABLE `matcher_matchpoints` (
1242 `matcher_id` int(11) NOT NULL,
1243 `matchpoint_id` int(11) NOT NULL,
1244 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1245 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1246 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1247 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1248 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1251 -- Table structure for table `matchchecks`
1253 DROP TABLE IF EXISTS `matchchecks`;
1254 CREATE TABLE `matchchecks` (
1255 `matcher_id` int(11) NOT NULL,
1256 `matchcheck_id` int(11) NOT NULL auto_increment,
1257 `source_matchpoint_id` int(11) NOT NULL,
1258 `target_matchpoint_id` int(11) NOT NULL,
1259 PRIMARY KEY (`matchcheck_id`),
1260 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1261 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1262 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1263 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1264 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1265 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1269 -- Table structure for table `notifys`
1272 DROP TABLE IF EXISTS `notifys`;
1273 CREATE TABLE `notifys` (
1274 `notify_id` int(11) NOT NULL default 0,
1275 `borrowernumber` int(11) NOT NULL default 0,
1276 `itemnumber` int(11) NOT NULL default 0,
1277 `notify_date` date default NULL,
1278 `notify_send_date` date default NULL,
1279 `notify_level` int(1) NOT NULL default 0,
1280 `method` varchar(20) NOT NULL default ''
1281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1284 -- Table structure for table `nozebra`
1287 DROP TABLE IF EXISTS `nozebra`;
1288 CREATE TABLE `nozebra` (
1289 `server` varchar(20) NOT NULL,
1290 `indexname` varchar(40) NOT NULL,
1291 `value` varchar(250) NOT NULL,
1292 `biblionumbers` longtext NOT NULL,
1293 KEY `indexname` (`server`,`indexname`),
1294 KEY `value` (`server`,`value`))
1295 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1298 -- Table structure for table `old_issues`
1301 DROP TABLE IF EXISTS `old_issues`;
1302 CREATE TABLE `old_issues` (
1303 `borrowernumber` int(11) default NULL,
1304 `itemnumber` int(11) default NULL,
1305 `date_due` date default NULL,
1306 `branchcode` varchar(10) default NULL,
1307 `issuingbranch` varchar(18) default NULL,
1308 `returndate` date default NULL,
1309 `lastreneweddate` date default NULL,
1310 `return` varchar(4) default NULL,
1311 `renewals` tinyint(4) default NULL,
1312 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1313 `issuedate` date default NULL,
1314 KEY `old_issuesborridx` (`borrowernumber`),
1315 KEY `old_issuesitemidx` (`itemnumber`),
1316 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1317 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1318 ON DELETE SET NULL ON UPDATE SET NULL,
1319 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1320 ON DELETE SET NULL ON UPDATE SET NULL
1321 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1324 -- Table structure for table `old_reserves`
1326 DROP TABLE IF EXISTS `old_reserves`;
1327 CREATE TABLE `old_reserves` (
1328 `borrowernumber` int(11) default NULL,
1329 `reservedate` date default NULL,
1330 `biblionumber` int(11) default NULL,
1331 `constrainttype` varchar(1) default NULL,
1332 `branchcode` varchar(10) default NULL,
1333 `notificationdate` date default NULL,
1334 `reminderdate` date default NULL,
1335 `cancellationdate` date default NULL,
1336 `reservenotes` mediumtext,
1337 `priority` smallint(6) default NULL,
1338 `found` varchar(1) default NULL,
1339 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1340 `itemnumber` int(11) default NULL,
1341 `waitingdate` date default NULL,
1342 `expirationdate` DATE DEFAULT NULL,
1343 `lowestPriority` tinyint(1) NOT NULL,
1344 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1345 KEY `old_reserves_biblionumber` (`biblionumber`),
1346 KEY `old_reserves_itemnumber` (`itemnumber`),
1347 KEY `old_reserves_branchcode` (`branchcode`),
1348 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1349 ON DELETE SET NULL ON UPDATE SET NULL,
1350 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1351 ON DELETE SET NULL ON UPDATE SET NULL,
1352 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1353 ON DELETE SET NULL ON UPDATE SET NULL
1354 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1357 -- Table structure for table `opac_news`
1360 DROP TABLE IF EXISTS `opac_news`;
1361 CREATE TABLE `opac_news` (
1362 `idnew` int(10) unsigned NOT NULL auto_increment,
1363 `title` varchar(250) NOT NULL default '',
1364 `new` text NOT NULL,
1365 `lang` varchar(25) NOT NULL default '',
1366 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1367 `expirationdate` date default NULL,
1368 `number` int(11) default NULL,
1369 PRIMARY KEY (`idnew`)
1370 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1373 -- Table structure for table `overduerules`
1376 DROP TABLE IF EXISTS `overduerules`;
1377 CREATE TABLE `overduerules` (
1378 `branchcode` varchar(10) NOT NULL default '',
1379 `categorycode` varchar(10) NOT NULL default '',
1380 `delay1` int(4) default 0,
1381 `letter1` varchar(20) default NULL,
1382 `debarred1` varchar(1) default 0,
1383 `delay2` int(4) default 0,
1384 `debarred2` varchar(1) default 0,
1385 `letter2` varchar(20) default NULL,
1386 `delay3` int(4) default 0,
1387 `letter3` varchar(20) default NULL,
1388 `debarred3` int(1) default 0,
1389 PRIMARY KEY (`branchcode`,`categorycode`)
1390 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1393 -- Table structure for table `patroncards`
1396 DROP TABLE IF EXISTS `patroncards`;
1397 CREATE TABLE `patroncards` (
1398 `cardid` int(11) NOT NULL auto_increment,
1399 `batch_id` varchar(10) NOT NULL default '1',
1400 `borrowernumber` int(11) NOT NULL,
1401 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1402 PRIMARY KEY (`cardid`),
1403 KEY `patroncards_ibfk_1` (`borrowernumber`),
1404 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1405 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1408 -- Table structure for table `patronimage`
1411 DROP TABLE IF EXISTS `patronimage`;
1412 CREATE TABLE `patronimage` (
1413 `cardnumber` varchar(16) NOT NULL,
1414 `mimetype` varchar(15) NOT NULL,
1415 `imagefile` mediumblob NOT NULL,
1416 PRIMARY KEY (`cardnumber`),
1417 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1418 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1421 -- Table structure for table `printers`
1424 DROP TABLE IF EXISTS `printers`;
1425 CREATE TABLE `printers` (
1426 `printername` varchar(40) NOT NULL default '',
1427 `printqueue` varchar(20) default NULL,
1428 `printtype` varchar(20) default NULL,
1429 PRIMARY KEY (`printername`)
1430 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1433 -- Table structure for table `printers_profile`
1436 DROP TABLE IF EXISTS `printers_profile`;
1437 CREATE TABLE `printers_profile` (
1438 `profile_id` int(4) NOT NULL auto_increment,
1439 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1440 `template_id` int(4) NOT NULL default '0',
1441 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1442 `offset_horz` float NOT NULL default '0',
1443 `offset_vert` float NOT NULL default '0',
1444 `creep_horz` float NOT NULL default '0',
1445 `creep_vert` float NOT NULL default '0',
1446 `units` char(20) NOT NULL default 'POINT',
1447 `creator` char(15) NOT NULL DEFAULT 'Labels',
1448 PRIMARY KEY (`profile_id`),
1449 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
1450 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1453 -- Table structure for table `repeatable_holidays`
1456 DROP TABLE IF EXISTS `repeatable_holidays`;
1457 CREATE TABLE `repeatable_holidays` (
1458 `id` int(11) NOT NULL auto_increment,
1459 `branchcode` varchar(10) NOT NULL default '',
1460 `weekday` smallint(6) default NULL,
1461 `day` smallint(6) default NULL,
1462 `month` smallint(6) default NULL,
1463 `title` varchar(50) NOT NULL default '',
1464 `description` text NOT NULL,
1466 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1469 -- Table structure for table `reports_dictionary`
1472 DROP TABLE IF EXISTS `reports_dictionary`;
1473 CREATE TABLE reports_dictionary (
1474 `id` int(11) NOT NULL auto_increment,
1475 `name` varchar(255) default NULL,
1477 `date_created` datetime default NULL,
1478 `date_modified` datetime default NULL,
1480 `area` int(11) default NULL,
1482 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1485 -- Table structure for table `reserveconstraints`
1488 DROP TABLE IF EXISTS `reserveconstraints`;
1489 CREATE TABLE `reserveconstraints` (
1490 `borrowernumber` int(11) NOT NULL default 0,
1491 `reservedate` date default NULL,
1492 `biblionumber` int(11) NOT NULL default 0,
1493 `biblioitemnumber` int(11) default NULL,
1494 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1495 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1498 -- Table structure for table `reserves`
1501 DROP TABLE IF EXISTS `reserves`;
1502 CREATE TABLE `reserves` (
1503 `borrowernumber` int(11) NOT NULL default 0,
1504 `reservedate` date default NULL,
1505 `biblionumber` int(11) NOT NULL default 0,
1506 `constrainttype` varchar(1) default NULL,
1507 `branchcode` varchar(10) default NULL,
1508 `notificationdate` date default NULL,
1509 `reminderdate` date default NULL,
1510 `cancellationdate` date default NULL,
1511 `reservenotes` mediumtext,
1512 `priority` smallint(6) default NULL,
1513 `found` varchar(1) default NULL,
1514 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1515 `itemnumber` int(11) default NULL,
1516 `waitingdate` date default NULL,
1517 `expirationdate` DATE DEFAULT NULL,
1518 `lowestPriority` tinyint(1) NOT NULL,
1519 KEY `borrowernumber` (`borrowernumber`),
1520 KEY `biblionumber` (`biblionumber`),
1521 KEY `itemnumber` (`itemnumber`),
1522 KEY `branchcode` (`branchcode`),
1523 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1524 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1525 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1526 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1530 -- Table structure for table `reviews`
1533 DROP TABLE IF EXISTS `reviews`;
1534 CREATE TABLE `reviews` (
1535 `reviewid` int(11) NOT NULL auto_increment,
1536 `borrowernumber` int(11) default NULL,
1537 `biblionumber` int(11) default NULL,
1539 `approved` tinyint(4) default NULL,
1540 `datereviewed` datetime default NULL,
1541 PRIMARY KEY (`reviewid`)
1542 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1545 -- Table structure for table `roadtype`
1548 DROP TABLE IF EXISTS `roadtype`;
1549 CREATE TABLE `roadtype` (
1550 `roadtypeid` int(11) NOT NULL auto_increment,
1551 `road_type` varchar(100) NOT NULL default '',
1552 PRIMARY KEY (`roadtypeid`)
1553 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1556 -- Table structure for table `saved_sql`
1559 DROP TABLE IF EXISTS `saved_sql`;
1560 CREATE TABLE saved_sql (
1561 `id` int(11) NOT NULL auto_increment,
1562 `borrowernumber` int(11) default NULL,
1563 `date_created` datetime default NULL,
1564 `last_modified` datetime default NULL,
1566 `last_run` datetime default NULL,
1567 `report_name` varchar(255) default NULL,
1568 `type` varchar(255) default NULL,
1571 KEY boridx (`borrowernumber`)
1572 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1576 -- Table structure for `saved_reports`
1579 DROP TABLE IF EXISTS `saved_reports`;
1580 CREATE TABLE saved_reports (
1581 `id` int(11) NOT NULL auto_increment,
1582 `report_id` int(11) default NULL,
1584 `date_run` datetime default NULL,
1586 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1590 -- Table structure for table `search_history`
1593 DROP TABLE IF EXISTS `search_history`;
1594 CREATE TABLE IF NOT EXISTS `search_history` (
1595 `userid` int(11) NOT NULL,
1596 `sessionid` varchar(32) NOT NULL,
1597 `query_desc` varchar(255) NOT NULL,
1598 `query_cgi` varchar(255) NOT NULL,
1599 `total` int(11) NOT NULL,
1600 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1601 KEY `userid` (`userid`),
1602 KEY `sessionid` (`sessionid`)
1603 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1607 -- Table structure for table `serial`
1610 DROP TABLE IF EXISTS `serial`;
1611 CREATE TABLE `serial` (
1612 `serialid` int(11) NOT NULL auto_increment,
1613 `biblionumber` varchar(100) NOT NULL default '',
1614 `subscriptionid` varchar(100) NOT NULL default '',
1615 `serialseq` varchar(100) NOT NULL default '',
1616 `status` tinyint(4) NOT NULL default 0,
1617 `planneddate` date default NULL,
1619 `publisheddate` date default NULL,
1620 `itemnumber` text default NULL,
1621 `claimdate` date default NULL,
1622 `routingnotes` text,
1623 PRIMARY KEY (`serialid`)
1624 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1627 -- Table structure for table `sessions`
1630 DROP TABLE IF EXISTS sessions;
1631 CREATE TABLE sessions (
1632 `id` varchar(32) NOT NULL,
1633 `a_session` text NOT NULL,
1635 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1638 -- Table structure for table `special_holidays`
1641 DROP TABLE IF EXISTS `special_holidays`;
1642 CREATE TABLE `special_holidays` (
1643 `id` int(11) NOT NULL auto_increment,
1644 `branchcode` varchar(10) NOT NULL default '',
1645 `day` smallint(6) NOT NULL default 0,
1646 `month` smallint(6) NOT NULL default 0,
1647 `year` smallint(6) NOT NULL default 0,
1648 `isexception` smallint(1) NOT NULL default 1,
1649 `title` varchar(50) NOT NULL default '',
1650 `description` text NOT NULL,
1652 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1655 -- Table structure for table `statistics`
1658 DROP TABLE IF EXISTS `statistics`;
1659 CREATE TABLE `statistics` (
1660 `datetime` datetime default NULL,
1661 `branch` varchar(10) default NULL,
1662 `proccode` varchar(4) default NULL,
1663 `value` double(16,4) default NULL,
1664 `type` varchar(16) default NULL,
1666 `usercode` varchar(10) default NULL,
1667 `itemnumber` int(11) default NULL,
1668 `itemtype` varchar(10) default NULL,
1669 `borrowernumber` int(11) default NULL,
1670 `associatedborrower` int(11) default NULL,
1671 KEY `timeidx` (`datetime`)
1672 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1675 -- Table structure for table `stopwords`
1678 DROP TABLE IF EXISTS `stopwords`;
1679 CREATE TABLE `stopwords` (
1680 `word` varchar(255) default NULL
1681 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1684 -- Table structure for table `subscription`
1687 DROP TABLE IF EXISTS `subscription`;
1688 CREATE TABLE `subscription` (
1689 `biblionumber` int(11) NOT NULL default 0,
1690 `subscriptionid` int(11) NOT NULL auto_increment,
1691 `librarian` varchar(100) default '',
1692 `startdate` date default NULL,
1693 `aqbooksellerid` int(11) default 0,
1694 `cost` int(11) default 0,
1695 `aqbudgetid` int(11) default 0,
1696 `weeklength` int(11) default 0,
1697 `monthlength` int(11) default 0,
1698 `numberlength` int(11) default 0,
1699 `periodicity` tinyint(4) default 0,
1700 `dow` varchar(100) default '',
1701 `numberingmethod` varchar(100) default '',
1703 `status` varchar(100) NOT NULL default '',
1704 `add1` int(11) default 0,
1705 `every1` int(11) default 0,
1706 `whenmorethan1` int(11) default 0,
1707 `setto1` int(11) default NULL,
1708 `lastvalue1` int(11) default NULL,
1709 `add2` int(11) default 0,
1710 `every2` int(11) default 0,
1711 `whenmorethan2` int(11) default 0,
1712 `setto2` int(11) default NULL,
1713 `lastvalue2` int(11) default NULL,
1714 `add3` int(11) default 0,
1715 `every3` int(11) default 0,
1716 `innerloop1` int(11) default 0,
1717 `innerloop2` int(11) default 0,
1718 `innerloop3` int(11) default 0,
1719 `whenmorethan3` int(11) default 0,
1720 `setto3` int(11) default NULL,
1721 `lastvalue3` int(11) default NULL,
1722 `issuesatonce` tinyint(3) NOT NULL default 1,
1723 `firstacquidate` date default NULL,
1724 `manualhistory` tinyint(1) NOT NULL default 0,
1725 `irregularity` text,
1726 `letter` varchar(20) default NULL,
1727 `numberpattern` tinyint(3) default 0,
1728 `distributedto` text,
1729 `internalnotes` longtext,
1731 `location` varchar(80) NULL default '',
1732 `branchcode` varchar(10) NOT NULL default '',
1733 `hemisphere` tinyint(3) default 0,
1734 `lastbranch` varchar(10),
1735 `serialsadditems` tinyint(1) NOT NULL default '0',
1736 `staffdisplaycount` VARCHAR(10) NULL,
1737 `opacdisplaycount` VARCHAR(10) NULL,
1738 `graceperiod` int(11) NOT NULL default '0',
1739 `enddate` date default NULL,
1740 PRIMARY KEY (`subscriptionid`)
1741 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1744 -- Table structure for table `subscriptionhistory`
1747 DROP TABLE IF EXISTS `subscriptionhistory`;
1748 CREATE TABLE `subscriptionhistory` (
1749 `biblionumber` int(11) NOT NULL default 0,
1750 `subscriptionid` int(11) NOT NULL default 0,
1751 `histstartdate` date default NULL,
1752 `histenddate` date default NULL,
1753 `missinglist` longtext NOT NULL,
1754 `recievedlist` longtext NOT NULL,
1755 `opacnote` varchar(150) NOT NULL default '',
1756 `librariannote` varchar(150) NOT NULL default '',
1757 PRIMARY KEY (`subscriptionid`),
1758 KEY `biblionumber` (`biblionumber`)
1759 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1762 -- Table structure for table `subscriptionroutinglist`
1765 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1766 CREATE TABLE `subscriptionroutinglist` (
1767 `routingid` int(11) NOT NULL auto_increment,
1768 `borrowernumber` int(11) default NULL,
1769 `ranking` int(11) default NULL,
1770 `subscriptionid` int(11) default NULL,
1771 PRIMARY KEY (`routingid`)
1772 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1775 -- Table structure for table `suggestions`
1778 DROP TABLE IF EXISTS `suggestions`;
1779 CREATE TABLE `suggestions` (
1780 `suggestionid` int(8) NOT NULL auto_increment,
1781 `suggestedby` int(11) NOT NULL default 0,
1782 `suggesteddate` date NOT NULL default 0,
1783 `managedby` int(11) default NULL,
1784 `manageddate` date default NULL,
1785 acceptedby INT(11) default NULL,
1786 accepteddate date default NULL,
1787 rejectedby INT(11) default NULL,
1788 rejecteddate date default NULL,
1789 `STATUS` varchar(10) NOT NULL default '',
1791 `author` varchar(80) default NULL,
1792 `title` varchar(80) default NULL,
1793 `copyrightdate` smallint(6) default NULL,
1794 `publishercode` varchar(255) default NULL,
1795 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1796 `volumedesc` varchar(255) default NULL,
1797 `publicationyear` smallint(6) default 0,
1798 `place` varchar(255) default NULL,
1799 `isbn` varchar(30) default NULL,
1800 `mailoverseeing` smallint(1) default 0,
1801 `biblionumber` int(11) default NULL,
1804 branchcode VARCHAR(10) default NULL,
1805 collectiontitle text default NULL,
1806 itemtype VARCHAR(30) default NULL,
1807 PRIMARY KEY (`suggestionid`),
1808 KEY `suggestedby` (`suggestedby`),
1809 KEY `managedby` (`managedby`)
1810 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1813 -- Table structure for table `systempreferences`
1816 DROP TABLE IF EXISTS `systempreferences`;
1817 CREATE TABLE `systempreferences` (
1818 `variable` varchar(50) NOT NULL default '',
1820 `options` mediumtext,
1822 `type` varchar(20) default NULL,
1823 PRIMARY KEY (`variable`)
1824 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1827 -- Table structure for table `tags`
1830 DROP TABLE IF EXISTS `tags`;
1831 CREATE TABLE `tags` (
1832 `entry` varchar(255) NOT NULL default '',
1833 `weight` bigint(20) NOT NULL default 0,
1834 PRIMARY KEY (`entry`)
1835 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1838 -- Table structure for table `tags_all`
1841 DROP TABLE IF EXISTS `tags_all`;
1842 CREATE TABLE `tags_all` (
1843 `tag_id` int(11) NOT NULL auto_increment,
1844 `borrowernumber` int(11) NOT NULL,
1845 `biblionumber` int(11) NOT NULL,
1846 `term` varchar(255) NOT NULL,
1847 `language` int(4) default NULL,
1848 `date_created` datetime NOT NULL,
1849 PRIMARY KEY (`tag_id`),
1850 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1851 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1852 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1853 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1854 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1855 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1856 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1859 -- Table structure for table `tags_approval`
1862 DROP TABLE IF EXISTS `tags_approval`;
1863 CREATE TABLE `tags_approval` (
1864 `term` varchar(255) NOT NULL,
1865 `approved` int(1) NOT NULL default '0',
1866 `date_approved` datetime default NULL,
1867 `approved_by` int(11) default NULL,
1868 `weight_total` int(9) NOT NULL default '1',
1869 PRIMARY KEY (`term`),
1870 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1871 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1872 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1873 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1876 -- Table structure for table `tags_index`
1879 DROP TABLE IF EXISTS `tags_index`;
1880 CREATE TABLE `tags_index` (
1881 `term` varchar(255) NOT NULL,
1882 `biblionumber` int(11) NOT NULL,
1883 `weight` int(9) NOT NULL default '1',
1884 PRIMARY KEY (`term`,`biblionumber`),
1885 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1886 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1887 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1888 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1889 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1890 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1893 -- Table structure for table `userflags`
1896 DROP TABLE IF EXISTS `userflags`;
1897 CREATE TABLE `userflags` (
1898 `bit` int(11) NOT NULL default 0,
1899 `flag` varchar(30) default NULL,
1900 `flagdesc` varchar(255) default NULL,
1901 `defaulton` int(11) default NULL,
1903 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1906 -- Table structure for table `virtualshelves`
1909 DROP TABLE IF EXISTS `virtualshelves`;
1910 CREATE TABLE `virtualshelves` (
1911 `shelfnumber` int(11) NOT NULL auto_increment,
1912 `shelfname` varchar(255) default NULL,
1913 `owner` varchar(80) default NULL,
1914 `category` varchar(1) default NULL,
1915 `sortfield` varchar(16) default NULL,
1916 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1917 PRIMARY KEY (`shelfnumber`)
1918 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1921 -- Table structure for table `virtualshelfcontents`
1924 DROP TABLE IF EXISTS `virtualshelfcontents`;
1925 CREATE TABLE `virtualshelfcontents` (
1926 `shelfnumber` int(11) NOT NULL default 0,
1927 `biblionumber` int(11) NOT NULL default 0,
1928 `flags` int(11) default NULL,
1929 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1930 KEY `shelfnumber` (`shelfnumber`),
1931 KEY `biblionumber` (`biblionumber`),
1932 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1933 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1934 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1937 -- Table structure for table `z3950servers`
1940 DROP TABLE IF EXISTS `z3950servers`;
1941 CREATE TABLE `z3950servers` (
1942 `host` varchar(255) default NULL,
1943 `port` int(11) default NULL,
1944 `db` varchar(255) default NULL,
1945 `userid` varchar(255) default NULL,
1946 `password` varchar(255) default NULL,
1948 `id` int(11) NOT NULL auto_increment,
1949 `checked` smallint(6) default NULL,
1950 `rank` int(11) default NULL,
1951 `syntax` varchar(80) default NULL,
1953 `position` enum('primary','secondary','') NOT NULL default 'primary',
1954 `type` enum('zed','opensearch') NOT NULL default 'zed',
1955 `encoding` text default NULL,
1956 `description` text NOT NULL,
1958 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1961 -- Table structure for table `zebraqueue`
1964 DROP TABLE IF EXISTS `zebraqueue`;
1965 CREATE TABLE `zebraqueue` (
1966 `id` int(11) NOT NULL auto_increment,
1967 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1968 `operation` char(20) NOT NULL default '',
1969 `server` char(20) NOT NULL default '',
1970 `done` int(11) NOT NULL default '0',
1971 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1973 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1974 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1976 DROP TABLE IF EXISTS `services_throttle`;
1977 CREATE TABLE `services_throttle` (
1978 `service_type` varchar(10) NOT NULL default '',
1979 `service_count` varchar(45) default NULL,
1980 PRIMARY KEY (`service_type`)
1981 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1983 -- http://www.w3.org/International/articles/language-tags/
1986 DROP TABLE IF EXISTS language_subtag_registry;
1987 CREATE TABLE language_subtag_registry (
1989 type varchar(25), -- language-script-region-variant-extension-privateuse
1990 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1992 id int(11) NOT NULL auto_increment,
1994 KEY `subtag` (`subtag`)
1995 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1997 -- TODO: add suppress_scripts
1998 -- this maps three letter codes defined in iso639.2 back to their
1999 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2000 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2001 CREATE TABLE language_rfc4646_to_iso639 (
2002 rfc4646_subtag varchar(25),
2003 iso639_2_code varchar(25),
2004 id int(11) NOT NULL auto_increment,
2006 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2007 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2009 DROP TABLE IF EXISTS language_descriptions;
2010 CREATE TABLE language_descriptions (
2014 description varchar(255),
2015 id int(11) NOT NULL auto_increment,
2017 KEY `lang` (`lang`),
2018 KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
2019 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2021 -- bi-directional support, keyed by script subcode
2022 DROP TABLE IF EXISTS language_script_bidi;
2023 CREATE TABLE language_script_bidi (
2024 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2025 bidi varchar(3), -- rtl ltr
2026 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2027 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2029 -- TODO: need to map language subtags to script subtags for detection
2030 -- of bidi when script is not specified (like ar, he)
2031 DROP TABLE IF EXISTS language_script_mapping;
2032 CREATE TABLE language_script_mapping (
2033 language_subtag varchar(25),
2034 script_subtag varchar(25),
2035 KEY `language_subtag` (`language_subtag`)
2036 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2038 DROP TABLE IF EXISTS `permissions`;
2039 CREATE TABLE `permissions` (
2040 `module_bit` int(11) NOT NULL DEFAULT 0,
2041 `code` varchar(64) DEFAULT NULL,
2042 `description` varchar(255) DEFAULT NULL,
2043 PRIMARY KEY (`module_bit`, `code`),
2044 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2045 ON DELETE CASCADE ON UPDATE CASCADE
2046 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2048 DROP TABLE IF EXISTS `serialitems`;
2049 CREATE TABLE `serialitems` (
2050 `itemnumber` int(11) NOT NULL,
2051 `serialid` int(11) NOT NULL,
2052 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2053 KEY `serialitems_sfk_1` (`serialid`),
2054 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2055 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2057 DROP TABLE IF EXISTS `user_permissions`;
2058 CREATE TABLE `user_permissions` (
2059 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2060 `module_bit` int(11) NOT NULL DEFAULT 0,
2061 `code` varchar(64) DEFAULT NULL,
2062 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2063 ON DELETE CASCADE ON UPDATE CASCADE,
2064 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2065 ON DELETE CASCADE ON UPDATE CASCADE
2066 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2069 -- Table structure for table `tmp_holdsqueue`
2072 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2073 CREATE TABLE `tmp_holdsqueue` (
2074 `biblionumber` int(11) default NULL,
2075 `itemnumber` int(11) default NULL,
2076 `barcode` varchar(20) default NULL,
2077 `surname` mediumtext NOT NULL,
2080 `borrowernumber` int(11) NOT NULL,
2081 `cardnumber` varchar(16) default NULL,
2082 `reservedate` date default NULL,
2084 `itemcallnumber` varchar(255) default NULL,
2085 `holdingbranch` varchar(10) default NULL,
2086 `pickbranch` varchar(10) default NULL,
2088 `item_level_request` tinyint(4) NOT NULL default 0
2089 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2092 -- Table structure for table `message_queue`
2095 DROP TABLE IF EXISTS `message_queue`;
2096 CREATE TABLE `message_queue` (
2097 `message_id` int(11) NOT NULL auto_increment,
2098 `borrowernumber` int(11) default NULL,
2101 `metadata` text DEFAULT NULL,
2102 `letter_code` varchar(64) DEFAULT NULL,
2103 `message_transport_type` varchar(20) NOT NULL,
2104 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2105 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2106 `to_address` mediumtext,
2107 `from_address` mediumtext,
2108 `content_type` text,
2109 KEY `message_id` (`message_id`),
2110 KEY `borrowernumber` (`borrowernumber`),
2111 KEY `message_transport_type` (`message_transport_type`),
2112 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2113 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2117 -- Table structure for table `message_transport_types`
2120 DROP TABLE IF EXISTS `message_transport_types`;
2121 CREATE TABLE `message_transport_types` (
2122 `message_transport_type` varchar(20) NOT NULL,
2123 PRIMARY KEY (`message_transport_type`)
2124 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2127 -- Table structure for table `message_attributes`
2130 DROP TABLE IF EXISTS `message_attributes`;
2131 CREATE TABLE `message_attributes` (
2132 `message_attribute_id` int(11) NOT NULL auto_increment,
2133 `message_name` varchar(20) NOT NULL default '',
2134 `takes_days` tinyint(1) NOT NULL default '0',
2135 PRIMARY KEY (`message_attribute_id`),
2136 UNIQUE KEY `message_name` (`message_name`)
2137 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2140 -- Table structure for table `message_transports`
2143 DROP TABLE IF EXISTS `message_transports`;
2144 CREATE TABLE `message_transports` (
2145 `message_attribute_id` int(11) NOT NULL,
2146 `message_transport_type` varchar(20) NOT NULL,
2147 `is_digest` tinyint(1) NOT NULL default '0',
2148 `letter_module` varchar(20) NOT NULL default '',
2149 `letter_code` varchar(20) NOT NULL default '',
2150 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2151 KEY `message_transport_type` (`message_transport_type`),
2152 KEY `letter_module` (`letter_module`,`letter_code`),
2153 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2154 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2155 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2159 -- Table structure for table `borrower_message_preferences`
2162 DROP TABLE IF EXISTS `borrower_message_preferences`;
2163 CREATE TABLE `borrower_message_preferences` (
2164 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2165 `borrowernumber` int(11) default NULL,
2166 `categorycode` varchar(10) default NULL,
2167 `message_attribute_id` int(11) default '0',
2168 `days_in_advance` int(11) default '0',
2169 `wants_digest` tinyint(1) NOT NULL default '0',
2170 PRIMARY KEY (`borrower_message_preference_id`),
2171 KEY `borrowernumber` (`borrowernumber`),
2172 KEY `categorycode` (`categorycode`),
2173 KEY `message_attribute_id` (`message_attribute_id`),
2174 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2175 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2176 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2177 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2180 -- Table structure for table `borrower_message_transport_preferences`
2183 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2184 CREATE TABLE `borrower_message_transport_preferences` (
2185 `borrower_message_preference_id` int(11) NOT NULL default '0',
2186 `message_transport_type` varchar(20) NOT NULL default '0',
2187 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2188 KEY `message_transport_type` (`message_transport_type`),
2189 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,
2190 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
2191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2194 -- Table structure for the table branch_transfer_limits
2197 DROP TABLE IF EXISTS `branch_transfer_limits`;
2198 CREATE TABLE branch_transfer_limits (
2199 limitId int(8) NOT NULL auto_increment,
2200 toBranch varchar(10) NOT NULL,
2201 fromBranch varchar(10) NOT NULL,
2202 itemtype varchar(10) NULL,
2203 ccode varchar(10) NULL,
2204 PRIMARY KEY (limitId)
2205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2208 -- Table structure for table `item_circulation_alert_preferences`
2211 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2212 CREATE TABLE `item_circulation_alert_preferences` (
2213 `id` int(11) NOT NULL auto_increment,
2214 `branchcode` varchar(10) NOT NULL,
2215 `categorycode` varchar(10) NOT NULL,
2216 `item_type` varchar(10) NOT NULL,
2217 `notification` varchar(16) NOT NULL,
2219 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2220 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2223 -- Table structure for table `messages`
2226 CREATE TABLE `messages` (
2227 `message_id` int(11) NOT NULL auto_increment,
2228 `borrowernumber` int(11) NOT NULL,
2229 `branchcode` varchar(10) default NULL,
2230 `message_type` varchar(1) NOT NULL,
2231 `message` text NOT NULL,
2232 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2233 PRIMARY KEY (`message_id`)
2234 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2237 -- Table structure for table `accountlines`
2240 DROP TABLE IF EXISTS `accountlines`;
2241 CREATE TABLE `accountlines` (
2242 `borrowernumber` int(11) NOT NULL default 0,
2243 `accountno` smallint(6) NOT NULL default 0,
2244 `itemnumber` int(11) default NULL,
2245 `date` date default NULL,
2246 `amount` decimal(28,6) default NULL,
2247 `description` mediumtext,
2248 `dispute` mediumtext,
2249 `accounttype` varchar(5) default NULL,
2250 `amountoutstanding` decimal(28,6) default NULL,
2251 `lastincrement` decimal(28,6) default NULL,
2252 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2253 `notify_id` int(11) NOT NULL default 0,
2254 `notify_level` int(2) NOT NULL default 0,
2255 KEY `acctsborridx` (`borrowernumber`),
2256 KEY `timeidx` (`timestamp`),
2257 KEY `itemnumber` (`itemnumber`),
2258 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2259 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2263 -- Table structure for table `accountoffsets`
2266 DROP TABLE IF EXISTS `accountoffsets`;
2267 CREATE TABLE `accountoffsets` (
2268 `borrowernumber` int(11) NOT NULL default 0,
2269 `accountno` smallint(6) NOT NULL default 0,
2270 `offsetaccount` smallint(6) NOT NULL default 0,
2271 `offsetamount` decimal(28,6) default NULL,
2272 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2273 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2277 -- Table structure for table `action_logs`
2280 DROP TABLE IF EXISTS `action_logs`;
2281 CREATE TABLE `action_logs` (
2282 `action_id` int(11) NOT NULL auto_increment,
2283 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2284 `user` int(11) NOT NULL default 0,
2287 `object` int(11) default NULL,
2289 PRIMARY KEY (`action_id`),
2290 KEY (`timestamp`,`user`)
2291 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2294 -- Table structure for table `alert`
2297 DROP TABLE IF EXISTS `alert`;
2298 CREATE TABLE `alert` (
2299 `alertid` int(11) NOT NULL auto_increment,
2300 `borrowernumber` int(11) NOT NULL default 0,
2301 `type` varchar(10) NOT NULL default '',
2302 `externalid` varchar(20) NOT NULL default '',
2303 PRIMARY KEY (`alertid`),
2304 KEY `borrowernumber` (`borrowernumber`),
2305 KEY `type` (`type`,`externalid`)
2306 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2309 -- Table structure for table `aqbasketgroups`
2312 DROP TABLE IF EXISTS `aqbasketgroups`;
2313 CREATE TABLE `aqbasketgroups` (
2314 `id` int(11) NOT NULL auto_increment,
2315 `name` varchar(50) default NULL,
2316 `closed` tinyint(1) default NULL,
2317 `booksellerid` int(11) NOT NULL,
2319 KEY `booksellerid` (`booksellerid`),
2320 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2321 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2324 -- Table structure for table `aqbasket`
2327 DROP TABLE IF EXISTS `aqbasket`;
2328 CREATE TABLE `aqbasket` (
2329 `basketno` int(11) NOT NULL auto_increment,
2330 `basketname` varchar(50) default NULL,
2332 `booksellernote` mediumtext,
2333 `contractnumber` int(11),
2334 `creationdate` date default NULL,
2335 `closedate` date default NULL,
2336 `booksellerid` int(11) NOT NULL default 1,
2337 `authorisedby` varchar(10) default NULL,
2338 `booksellerinvoicenumber` mediumtext,
2339 `basketgroupid` int(11),
2340 PRIMARY KEY (`basketno`),
2341 KEY `booksellerid` (`booksellerid`),
2342 KEY `basketgroupid` (`basketgroupid`),
2343 KEY `contractnumber` (`contractnumber`),
2344 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2345 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2346 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2347 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2350 -- Table structure for table `aqbooksellers`
2353 DROP TABLE IF EXISTS `aqbooksellers`;
2354 CREATE TABLE `aqbooksellers` (
2355 `id` int(11) NOT NULL auto_increment,
2356 `name` mediumtext NOT NULL,
2357 `address1` mediumtext,
2358 `address2` mediumtext,
2359 `address3` mediumtext,
2360 `address4` mediumtext,
2361 `phone` varchar(30) default NULL,
2362 `accountnumber` mediumtext,
2363 `othersupplier` mediumtext,
2364 `currency` varchar(3) NOT NULL default '',
2365 `booksellerfax` mediumtext,
2367 `bookselleremail` mediumtext,
2368 `booksellerurl` mediumtext,
2369 `contact` varchar(100) default NULL,
2370 `postal` mediumtext,
2371 `url` varchar(255) default NULL,
2372 `contpos` varchar(100) default NULL,
2373 `contphone` varchar(100) default NULL,
2374 `contfax` varchar(100) default NULL,
2375 `contaltphone` varchar(100) default NULL,
2376 `contemail` varchar(100) default NULL,
2377 `contnotes` mediumtext,
2378 `active` tinyint(4) default NULL,
2379 `listprice` varchar(10) default NULL,
2380 `invoiceprice` varchar(10) default NULL,
2381 `gstreg` tinyint(4) default NULL,
2382 `listincgst` tinyint(4) default NULL,
2383 `invoiceincgst` tinyint(4) default NULL,
2384 `gstrate` decimal(6,4) default NULL,
2385 `discount` float(6,4) default NULL,
2386 `fax` varchar(50) default NULL,
2388 KEY `listprice` (`listprice`),
2389 KEY `invoiceprice` (`invoiceprice`),
2390 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2391 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2392 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2395 -- Table structure for table `aqbudgets`
2398 DROP TABLE IF EXISTS `aqbudgets`;
2399 CREATE TABLE `aqbudgets` (
2400 `budget_id` int(11) NOT NULL auto_increment,
2401 `budget_parent_id` int(11) default NULL,
2402 `budget_code` varchar(30) default NULL,
2403 `budget_name` varchar(80) default NULL,
2404 `budget_branchcode` varchar(10) default NULL,
2405 `budget_amount` decimal(28,6) NULL default '0.00',
2406 `budget_encumb` decimal(28,6) NULL default '0.00',
2407 `budget_expend` decimal(28,6) NULL default '0.00',
2408 `budget_notes` mediumtext,
2409 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2410 `budget_period_id` int(11) default NULL,
2411 `sort1_authcat` varchar(80) default NULL,
2412 `sort2_authcat` varchar(80) default NULL,
2413 `budget_owner_id` int(11) default NULL,
2414 `budget_permission` int(1) default '0',
2415 PRIMARY KEY (`budget_id`)
2416 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2420 -- Table structure for table `aqbudgetperiods`
2424 DROP TABLE IF EXISTS `aqbudgetperiods`;
2425 CREATE TABLE `aqbudgetperiods` (
2426 `budget_period_id` int(11) NOT NULL auto_increment,
2427 `budget_period_startdate` date NOT NULL,
2428 `budget_period_enddate` date NOT NULL,
2429 `budget_period_active` tinyint(1) default '0',
2430 `budget_period_description` mediumtext,
2431 `budget_period_total` decimal(28,6),
2432 `budget_period_locked` tinyint(1) default NULL,
2433 `sort1_authcat` varchar(10) default NULL,
2434 `sort2_authcat` varchar(10) default NULL,
2435 PRIMARY KEY (`budget_period_id`)
2436 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2439 -- Table structure for table `aqbudgets_planning`
2442 DROP TABLE IF EXISTS `aqbudgets_planning`;
2443 CREATE TABLE `aqbudgets_planning` (
2444 `plan_id` int(11) NOT NULL auto_increment,
2445 `budget_id` int(11) NOT NULL,
2446 `budget_period_id` int(11) NOT NULL,
2447 `estimated_amount` decimal(28,6) default NULL,
2448 `authcat` varchar(30) NOT NULL,
2449 `authvalue` varchar(30) NOT NULL,
2450 `display` tinyint(1) DEFAULT 1,
2451 PRIMARY KEY (`plan_id`),
2452 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2453 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2456 -- Table structure for table 'aqcontract'
2459 DROP TABLE IF EXISTS `aqcontract`;
2460 CREATE TABLE `aqcontract` (
2461 `contractnumber` int(11) NOT NULL auto_increment,
2462 `contractstartdate` date default NULL,
2463 `contractenddate` date default NULL,
2464 `contractname` varchar(50) default NULL,
2465 `contractdescription` mediumtext,
2466 `booksellerid` int(11) not NULL,
2467 PRIMARY KEY (`contractnumber`),
2468 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2469 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2470 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2473 -- Table structure for table `aqorderdelivery`
2476 DROP TABLE IF EXISTS `aqorderdelivery`;
2477 CREATE TABLE `aqorderdelivery` (
2478 `ordernumber` date default NULL,
2479 `deliverynumber` smallint(6) NOT NULL default 0,
2480 `deliverydate` varchar(18) default NULL,
2481 `qtydelivered` smallint(6) default NULL,
2482 `deliverycomments` mediumtext
2483 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2486 -- Table structure for table `aqorders`
2489 DROP TABLE IF EXISTS `aqorders`;
2490 CREATE TABLE `aqorders` (
2491 `ordernumber` int(11) NOT NULL auto_increment,
2492 `biblionumber` int(11) default NULL,
2493 `entrydate` date default NULL,
2494 `quantity` smallint(6) default NULL,
2495 `currency` varchar(3) default NULL,
2496 `listprice` decimal(28,6) default NULL,
2497 `totalamount` decimal(28,6) default NULL,
2498 `datereceived` date default NULL,
2499 `booksellerinvoicenumber` mediumtext,
2500 `freight` decimal(28,6) default NULL,
2501 `unitprice` decimal(28,6) default NULL,
2502 `quantityreceived` smallint(6) NOT NULL default 0,
2503 `cancelledby` varchar(10) default NULL,
2504 `datecancellationprinted` date default NULL,
2506 `supplierreference` mediumtext,
2507 `purchaseordernumber` mediumtext,
2508 `subscription` tinyint(1) default NULL,
2509 `serialid` varchar(30) default NULL,
2510 `basketno` int(11) default NULL,
2511 `biblioitemnumber` int(11) default NULL,
2512 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2513 `rrp` decimal(13,2) default NULL,
2514 `ecost` decimal(13,2) default NULL,
2515 `gst` decimal(13,2) default NULL,
2516 `budget_id` int(11) NOT NULL,
2517 `budgetgroup_id` int(11) NOT NULL,
2518 `budgetdate` date default NULL,
2519 `sort1` varchar(80) default NULL,
2520 `sort2` varchar(80) default NULL,
2521 `sort1_authcat` varchar(10) default NULL,
2522 `sort2_authcat` varchar(10) default NULL,
2523 `uncertainprice` tinyint(1),
2524 PRIMARY KEY (`ordernumber`),
2525 KEY `basketno` (`basketno`),
2526 KEY `biblionumber` (`biblionumber`),
2527 KEY `budget_id` (`budget_id`),
2528 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2529 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2530 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2534 -- Table structure for table `aqorders_items`
2537 DROP TABLE IF EXISTS `aqorders_items`;
2538 CREATE TABLE `aqorders_items` (
2539 `ordernumber` int(11) NOT NULL,
2540 `itemnumber` int(11) NOT NULL,
2541 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2542 PRIMARY KEY (`itemnumber`),
2543 KEY `ordernumber` (`ordernumber`)
2544 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2547 -- Table structure for table `fieldmapping`
2550 DROP TABLE IF EXISTS `fieldmapping`;
2551 CREATE TABLE `fieldmapping` (
2552 `id` int(11) NOT NULL auto_increment,
2553 `field` varchar(255) NOT NULL,
2554 `frameworkcode` char(4) NOT NULL default '',
2555 `fieldcode` char(3) NOT NULL,
2556 `subfieldcode` char(1) NOT NULL,
2558 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2561 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2562 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2563 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2564 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2565 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2566 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2567 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2568 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;