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(8) 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 `imageurl` varchar(200) default NULL,
105 KEY `name` (`category`),
107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
110 -- Table structure for table `biblio`
113 DROP TABLE IF EXISTS `biblio`;
114 CREATE TABLE `biblio` (
115 `biblionumber` int(11) NOT NULL auto_increment,
116 `frameworkcode` varchar(4) NOT NULL default '',
119 `unititle` mediumtext,
121 `serial` tinyint(1) default NULL,
122 `seriestitle` mediumtext,
123 `copyrightdate` smallint(6) default NULL,
124 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
125 `datecreated` DATE NOT NULL,
126 `abstract` mediumtext,
127 PRIMARY KEY (`biblionumber`),
128 KEY `blbnoidx` (`biblionumber`)
129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
132 -- Table structure for table `biblio_framework`
135 DROP TABLE IF EXISTS `biblio_framework`;
136 CREATE TABLE `biblio_framework` (
137 `frameworkcode` varchar(4) NOT NULL default '',
138 `frameworktext` varchar(255) NOT NULL default '',
139 PRIMARY KEY (`frameworkcode`)
140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
143 -- Table structure for table `biblioitems`
146 DROP TABLE IF EXISTS `biblioitems`;
147 CREATE TABLE `biblioitems` (
148 `biblioitemnumber` int(11) NOT NULL auto_increment,
149 `biblionumber` int(11) NOT NULL default 0,
152 `itemtype` varchar(10) default NULL,
153 `isbn` varchar(30) default NULL,
154 `issn` varchar(9) default NULL,
155 `publicationyear` text,
156 `publishercode` varchar(255) default NULL,
157 `volumedate` date default NULL,
159 `collectiontitle` mediumtext default NULL,
160 `collectionissn` text default NULL,
161 `collectionvolume` mediumtext default NULL,
162 `editionstatement` text default NULL,
163 `editionresponsibility` text default NULL,
164 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
165 `illus` varchar(255) default NULL,
166 `pages` varchar(255) default NULL,
168 `size` varchar(255) default NULL,
169 `place` varchar(255) default NULL,
170 `lccn` varchar(25) default NULL,
172 `url` varchar(255) default NULL,
173 `cn_source` varchar(10) default NULL,
174 `cn_class` varchar(30) default NULL,
175 `cn_item` varchar(10) default NULL,
176 `cn_suffix` varchar(10) default NULL,
177 `cn_sort` varchar(30) default NULL,
178 `totalissues` int(10),
179 `marcxml` longtext NOT NULL,
180 PRIMARY KEY (`biblioitemnumber`),
181 KEY `bibinoidx` (`biblioitemnumber`),
182 KEY `bibnoidx` (`biblionumber`),
184 KEY `publishercode` (`publishercode`),
185 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
186 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
189 -- Table structure for table `borrowers`
192 DROP TABLE IF EXISTS `borrowers`;
193 CREATE TABLE `borrowers` (
194 `borrowernumber` int(11) NOT NULL auto_increment,
195 `cardnumber` varchar(16) default NULL,
196 `surname` mediumtext NOT NULL,
199 `othernames` mediumtext,
201 `streetnumber` varchar(10) default NULL,
202 `streettype` varchar(50) default NULL,
203 `address` mediumtext NOT NULL,
205 `city` mediumtext NOT NULL,
206 `zipcode` varchar(25) default NULL,
210 `mobile` varchar(50) default NULL,
214 `B_streetnumber` varchar(10) default NULL,
215 `B_streettype` varchar(50) default NULL,
216 `B_address` varchar(100) default NULL,
217 `B_address2` text default NULL,
219 `B_zipcode` varchar(25) default NULL,
222 `B_phone` mediumtext,
223 `dateofbirth` date default NULL,
224 `branchcode` varchar(10) NOT NULL default '',
225 `categorycode` varchar(10) NOT NULL default '',
226 `dateenrolled` date default NULL,
227 `dateexpiry` date default NULL,
228 `gonenoaddress` tinyint(1) default NULL,
229 `lost` tinyint(1) default NULL,
230 `debarred` tinyint(1) default NULL,
231 `contactname` mediumtext,
232 `contactfirstname` text,
234 `guarantorid` int(11) default NULL,
235 `borrowernotes` mediumtext,
236 `relationship` varchar(100) default NULL,
237 `ethnicity` varchar(50) default NULL,
238 `ethnotes` varchar(255) default NULL,
239 `sex` varchar(1) default NULL,
240 `password` varchar(30) default NULL,
241 `flags` int(11) default NULL,
242 `userid` varchar(30) default NULL,
243 `opacnote` mediumtext,
244 `contactnote` varchar(255) default NULL,
245 `sort1` varchar(80) default NULL,
246 `sort2` varchar(80) default NULL,
247 `altcontactfirstname` varchar(255) default NULL,
248 `altcontactsurname` varchar(255) default NULL,
249 `altcontactaddress1` varchar(255) default NULL,
250 `altcontactaddress2` varchar(255) default NULL,
251 `altcontactaddress3` varchar(255) default NULL,
252 `altcontactzipcode` varchar(50) default NULL,
253 `altcontactcountry` text default NULL,
254 `altcontactphone` varchar(50) default NULL,
255 `smsalertnumber` varchar(50) default NULL,
256 UNIQUE KEY `cardnumber` (`cardnumber`),
257 PRIMARY KEY `borrowernumber` (`borrowernumber`),
258 KEY `categorycode` (`categorycode`),
259 KEY `branchcode` (`branchcode`),
260 KEY `userid` (`userid`),
261 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
262 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
266 -- Table structure for table `borrower_attribute_types`
269 DROP TABLE IF EXISTS `borrower_attribute_types`;
270 CREATE TABLE `borrower_attribute_types` (
271 `code` varchar(10) NOT NULL,
272 `description` varchar(255) NOT NULL,
273 `repeatable` tinyint(1) NOT NULL default 0,
274 `unique_id` tinyint(1) NOT NULL default 0,
275 `opac_display` tinyint(1) NOT NULL default 0,
276 `password_allowed` tinyint(1) NOT NULL default 0,
277 `staff_searchable` tinyint(1) NOT NULL default 0,
278 `authorised_value_category` varchar(10) default NULL,
280 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
283 -- Table structure for table `borrower_attributes`
286 DROP TABLE IF EXISTS `borrower_attributes`;
287 CREATE TABLE `borrower_attributes` (
288 `borrowernumber` int(11) NOT NULL,
289 `code` varchar(10) NOT NULL,
290 `attribute` varchar(64) default NULL,
291 `password` varchar(64) default NULL,
292 KEY `borrowernumber` (`borrowernumber`),
293 KEY `code_attribute` (`code`, `attribute`),
294 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
295 ON DELETE CASCADE ON UPDATE CASCADE,
296 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
297 ON DELETE CASCADE ON UPDATE CASCADE
298 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
300 CREATE TABLE `branch_item_rules` (
301 `branchcode` varchar(10) NOT NULL,
302 `itemtype` varchar(10) NOT NULL,
303 `holdallowed` tinyint(1) default NULL,
304 PRIMARY KEY (`itemtype`,`branchcode`),
305 KEY `branch_item_rules_ibfk_2` (`branchcode`),
306 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
307 ON DELETE CASCADE ON UPDATE CASCADE,
308 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
309 ON DELETE CASCADE ON UPDATE CASCADE
310 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
313 -- Table structure for table `branchcategories`
316 DROP TABLE IF EXISTS `branchcategories`;
317 CREATE TABLE `branchcategories` (
318 `categorycode` varchar(10) NOT NULL default '',
319 `categoryname` varchar(32),
320 `codedescription` mediumtext,
321 `categorytype` varchar(16),
322 PRIMARY KEY (`categorycode`)
323 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
326 -- Table structure for table `branches`
329 DROP TABLE IF EXISTS `branches`;
330 CREATE TABLE `branches` (
331 `branchcode` varchar(10) NOT NULL default '',
332 `branchname` mediumtext NOT NULL,
333 `branchaddress1` mediumtext,
334 `branchaddress2` mediumtext,
335 `branchaddress3` mediumtext,
336 `branchzip` varchar(25) default NULL,
337 `branchcity` mediumtext,
338 `branchcountry` text,
339 `branchphone` mediumtext,
340 `branchfax` mediumtext,
341 `branchemail` mediumtext,
342 `branchurl` mediumtext,
343 `issuing` tinyint(4) default NULL,
344 `branchip` varchar(15) default NULL,
345 `branchprinter` varchar(100) default NULL,
346 `branchnotes` mediumtext,
347 UNIQUE KEY `branchcode` (`branchcode`)
348 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
351 -- Table structure for table `branchrelations`
354 DROP TABLE IF EXISTS `branchrelations`;
355 CREATE TABLE `branchrelations` (
356 `branchcode` varchar(10) NOT NULL default '',
357 `categorycode` varchar(10) NOT NULL default '',
358 PRIMARY KEY (`branchcode`,`categorycode`),
359 KEY `branchcode` (`branchcode`),
360 KEY `categorycode` (`categorycode`),
361 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
362 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
363 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
366 -- Table structure for table `branchtransfers`
369 DROP TABLE IF EXISTS `branchtransfers`;
370 CREATE TABLE `branchtransfers` (
371 `itemnumber` int(11) NOT NULL default 0,
372 `datesent` datetime default NULL,
373 `frombranch` varchar(10) NOT NULL default '',
374 `datearrived` datetime default NULL,
375 `tobranch` varchar(10) NOT NULL default '',
376 `comments` mediumtext,
377 KEY `frombranch` (`frombranch`),
378 KEY `tobranch` (`tobranch`),
379 KEY `itemnumber` (`itemnumber`),
380 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
381 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
382 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
383 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
387 -- Table structure for table `browser`
389 DROP TABLE IF EXISTS `browser`;
390 CREATE TABLE `browser` (
391 `level` int(11) NOT NULL,
392 `classification` varchar(20) NOT NULL,
393 `description` varchar(255) NOT NULL,
394 `number` bigint(20) NOT NULL,
395 `endnode` tinyint(4) NOT NULL
396 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
399 -- Table structure for table `categories`
402 DROP TABLE IF EXISTS `categories`;
403 CREATE TABLE `categories` (
404 `categorycode` varchar(10) NOT NULL default '',
405 `description` mediumtext,
406 `enrolmentperiod` smallint(6) default NULL,
407 `upperagelimit` smallint(6) default NULL,
408 `dateofbirthrequired` tinyint(1) default NULL,
409 `finetype` varchar(30) default NULL,
410 `bulk` tinyint(1) default NULL,
411 `enrolmentfee` decimal(28,6) default NULL,
412 `overduenoticerequired` tinyint(1) default NULL,
413 `issuelimit` smallint(6) default NULL,
414 `reservefee` decimal(28,6) default NULL,
415 `category_type` varchar(1) NOT NULL default 'A',
416 PRIMARY KEY (`categorycode`),
417 UNIQUE KEY `categorycode` (`categorycode`)
418 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
421 -- Table structure for table `borrower_branch_circ_rules`
424 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
425 CREATE TABLE `branch_borrower_circ_rules` (
426 `branchcode` VARCHAR(10) NOT NULL,
427 `categorycode` VARCHAR(10) NOT NULL,
428 `maxissueqty` int(4) default NULL,
429 PRIMARY KEY (`categorycode`, `branchcode`),
430 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
431 ON DELETE CASCADE ON UPDATE CASCADE,
432 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
433 ON DELETE CASCADE ON UPDATE CASCADE
434 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
437 -- Table structure for table `default_borrower_circ_rules`
440 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
441 CREATE TABLE `default_borrower_circ_rules` (
442 `categorycode` VARCHAR(10) NOT NULL,
443 `maxissueqty` int(4) default NULL,
444 PRIMARY KEY (`categorycode`),
445 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
446 ON DELETE CASCADE ON UPDATE CASCADE
447 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
450 -- Table structure for table `default_branch_circ_rules`
453 DROP TABLE IF EXISTS `default_branch_circ_rules`;
454 CREATE TABLE `default_branch_circ_rules` (
455 `branchcode` VARCHAR(10) NOT NULL,
456 `maxissueqty` int(4) default NULL,
457 `holdallowed` tinyint(1) default NULL,
458 PRIMARY KEY (`branchcode`),
459 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
460 ON DELETE CASCADE ON UPDATE CASCADE
461 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
464 -- Table structure for table `default_branch_item_rules`
467 CREATE TABLE `default_branch_item_rules` (
468 `itemtype` varchar(10) NOT NULL,
469 `holdallowed` tinyint(1) default NULL,
470 PRIMARY KEY (`itemtype`),
471 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
472 ON DELETE CASCADE ON UPDATE CASCADE
473 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
476 -- Table structure for table `default_circ_rules`
479 DROP TABLE IF EXISTS `default_circ_rules`;
480 CREATE TABLE `default_circ_rules` (
481 `singleton` enum('singleton') NOT NULL default 'singleton',
482 `maxissueqty` int(4) default NULL,
483 `holdallowed` int(1) default NULL,
484 PRIMARY KEY (`singleton`)
485 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
488 -- Table structure for table `cities`
491 DROP TABLE IF EXISTS `cities`;
492 CREATE TABLE `cities` (
493 `cityid` int(11) NOT NULL auto_increment,
494 `city_name` varchar(100) NOT NULL default '',
495 `city_zipcode` varchar(20) default NULL,
496 PRIMARY KEY (`cityid`)
497 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
500 -- Table structure for table `class_sort_rules`
503 DROP TABLE IF EXISTS `class_sort_rules`;
504 CREATE TABLE `class_sort_rules` (
505 `class_sort_rule` varchar(10) NOT NULL default '',
506 `description` mediumtext,
507 `sort_routine` varchar(30) NOT NULL default '',
508 PRIMARY KEY (`class_sort_rule`),
509 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
510 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
513 -- Table structure for table `class_sources`
516 DROP TABLE IF EXISTS `class_sources`;
517 CREATE TABLE `class_sources` (
518 `cn_source` varchar(10) NOT NULL default '',
519 `description` mediumtext,
520 `used` tinyint(4) NOT NULL default 0,
521 `class_sort_rule` varchar(10) NOT NULL default '',
522 PRIMARY KEY (`cn_source`),
523 UNIQUE KEY `cn_source_idx` (`cn_source`),
524 KEY `used_idx` (`used`),
525 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
526 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
529 -- Table structure for table `currency`
532 DROP TABLE IF EXISTS `currency`;
533 CREATE TABLE `currency` (
534 `currency` varchar(10) NOT NULL default '',
535 `symbol` varchar(5) default NULL,
536 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
537 `rate` float(7,5) default NULL,
538 `active` tinyint(1) default NULL,
539 PRIMARY KEY (`currency`)
540 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
543 -- Table structure for table `deletedbiblio`
546 DROP TABLE IF EXISTS `deletedbiblio`;
547 CREATE TABLE `deletedbiblio` (
548 `biblionumber` int(11) NOT NULL default 0,
549 `frameworkcode` varchar(4) NOT NULL default '',
552 `unititle` mediumtext,
554 `serial` tinyint(1) default NULL,
555 `seriestitle` mediumtext,
556 `copyrightdate` smallint(6) default NULL,
557 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
558 `datecreated` DATE NOT NULL,
559 `abstract` mediumtext,
560 PRIMARY KEY (`biblionumber`),
561 KEY `blbnoidx` (`biblionumber`)
562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
565 -- Table structure for table `deletedbiblioitems`
568 DROP TABLE IF EXISTS `deletedbiblioitems`;
569 CREATE TABLE `deletedbiblioitems` (
570 `biblioitemnumber` int(11) NOT NULL default 0,
571 `biblionumber` int(11) NOT NULL default 0,
574 `itemtype` varchar(10) default NULL,
575 `isbn` varchar(30) default NULL,
576 `issn` varchar(9) default NULL,
577 `publicationyear` text,
578 `publishercode` varchar(255) default NULL,
579 `volumedate` date default NULL,
581 `collectiontitle` mediumtext default NULL,
582 `collectionissn` text default NULL,
583 `collectionvolume` mediumtext default NULL,
584 `editionstatement` text default NULL,
585 `editionresponsibility` text default NULL,
586 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
587 `illus` varchar(255) default NULL,
588 `pages` varchar(255) default NULL,
590 `size` varchar(255) default NULL,
591 `place` varchar(255) default NULL,
592 `lccn` varchar(25) default NULL,
594 `url` varchar(255) default NULL,
595 `cn_source` varchar(10) default NULL,
596 `cn_class` varchar(30) default NULL,
597 `cn_item` varchar(10) default NULL,
598 `cn_suffix` varchar(10) default NULL,
599 `cn_sort` varchar(30) default NULL,
600 `totalissues` int(10),
601 `marcxml` longtext NOT NULL,
602 PRIMARY KEY (`biblioitemnumber`),
603 KEY `bibinoidx` (`biblioitemnumber`),
604 KEY `bibnoidx` (`biblionumber`),
606 KEY `publishercode` (`publishercode`)
607 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
610 -- Table structure for table `deletedborrowers`
613 DROP TABLE IF EXISTS `deletedborrowers`;
614 CREATE TABLE `deletedborrowers` (
615 `borrowernumber` int(11) NOT NULL default 0,
616 `cardnumber` varchar(9) NOT NULL default '',
617 `surname` mediumtext NOT NULL,
620 `othernames` mediumtext,
622 `streetnumber` varchar(10) default NULL,
623 `streettype` varchar(50) default NULL,
624 `address` mediumtext NOT NULL,
626 `city` mediumtext NOT NULL,
627 `zipcode` varchar(25) default NULL,
631 `mobile` varchar(50) default NULL,
635 `B_streetnumber` varchar(10) default NULL,
636 `B_streettype` varchar(50) default NULL,
637 `B_address` varchar(100) default NULL,
638 `B_address2` text default NULL,
640 `B_zipcode` varchar(25) default NULL,
643 `B_phone` mediumtext,
644 `dateofbirth` date default NULL,
645 `branchcode` varchar(10) NOT NULL default '',
646 `categorycode` varchar(10) default NULL,
647 `dateenrolled` date default NULL,
648 `dateexpiry` date default NULL,
649 `gonenoaddress` tinyint(1) default NULL,
650 `lost` tinyint(1) default NULL,
651 `debarred` tinyint(1) default NULL,
652 `contactname` mediumtext,
653 `contactfirstname` text,
655 `guarantorid` int(11) default NULL,
656 `borrowernotes` mediumtext,
657 `relationship` varchar(100) default NULL,
658 `ethnicity` varchar(50) default NULL,
659 `ethnotes` varchar(255) default NULL,
660 `sex` varchar(1) default NULL,
661 `password` varchar(30) default NULL,
662 `flags` int(11) default NULL,
663 `userid` varchar(30) default NULL,
664 `opacnote` mediumtext,
665 `contactnote` varchar(255) default NULL,
666 `sort1` varchar(80) default NULL,
667 `sort2` varchar(80) default NULL,
668 `altcontactfirstname` varchar(255) default NULL,
669 `altcontactsurname` varchar(255) default NULL,
670 `altcontactaddress1` varchar(255) default NULL,
671 `altcontactaddress2` varchar(255) default NULL,
672 `altcontactaddress3` varchar(255) default NULL,
673 `altcontactzipcode` varchar(50) default NULL,
674 `altcontactcountry` text default NULL,
675 `altcontactphone` varchar(50) default NULL,
676 `smsalertnumber` varchar(50) default NULL,
677 KEY `borrowernumber` (`borrowernumber`),
678 KEY `cardnumber` (`cardnumber`)
679 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
682 -- Table structure for table `deleteditems`
685 DROP TABLE IF EXISTS `deleteditems`;
686 CREATE TABLE `deleteditems` (
687 `itemnumber` int(11) NOT NULL default 0,
688 `biblionumber` int(11) NOT NULL default 0,
689 `biblioitemnumber` int(11) NOT NULL default 0,
690 `barcode` varchar(20) default NULL,
691 `dateaccessioned` date default NULL,
692 `booksellerid` mediumtext default NULL,
693 `homebranch` varchar(10) default NULL,
694 `price` decimal(8,2) default NULL,
695 `replacementprice` decimal(8,2) default NULL,
696 `replacementpricedate` date default NULL,
697 `datelastborrowed` date default NULL,
698 `datelastseen` date default NULL,
699 `stack` tinyint(1) default NULL,
700 `notforloan` tinyint(1) NOT NULL default 0,
701 `damaged` tinyint(1) NOT NULL default 0,
702 `itemlost` tinyint(1) NOT NULL default 0,
703 `wthdrawn` tinyint(1) NOT NULL default 0,
704 `itemcallnumber` varchar(255) default NULL,
705 `issues` smallint(6) default NULL,
706 `renewals` smallint(6) default NULL,
707 `reserves` smallint(6) default NULL,
708 `restricted` tinyint(1) default NULL,
709 `itemnotes` mediumtext,
710 `holdingbranch` varchar(10) default NULL,
711 `paidfor` mediumtext,
712 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
713 `location` varchar(80) default NULL,
714 `permanent_location` varchar(80) default NULL,
715 `onloan` date default NULL,
716 `cn_source` varchar(10) default NULL,
717 `cn_sort` varchar(30) default NULL,
718 `ccode` varchar(10) default NULL,
719 `materials` varchar(10) default NULL,
720 `uri` varchar(255) default NULL,
721 `itype` varchar(10) default NULL,
722 `more_subfields_xml` longtext default NULL,
723 `enumchron` varchar(80) default NULL,
724 `copynumber` varchar(32) default NULL,
726 PRIMARY KEY (`itemnumber`),
727 KEY `delitembarcodeidx` (`barcode`),
728 KEY `delitembinoidx` (`biblioitemnumber`),
729 KEY `delitembibnoidx` (`biblionumber`),
730 KEY `delhomebranch` (`homebranch`),
731 KEY `delholdingbranch` (`holdingbranch`)
732 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
735 -- Table structure for table `ethnicity`
738 DROP TABLE IF EXISTS `ethnicity`;
739 CREATE TABLE `ethnicity` (
740 `code` varchar(10) NOT NULL default '',
741 `name` varchar(255) default NULL,
743 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
746 -- Table structure for table `export_format`
749 DROP TABLE IF EXISTS `export_format`;
750 CREATE TABLE `export_format` (
751 `export_format_id` int(11) NOT NULL auto_increment,
752 `profile` varchar(255) NOT NULL,
753 `description` mediumtext NOT NULL,
754 `marcfields` mediumtext NOT NULL,
755 PRIMARY KEY (`export_format_id`)
756 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
760 -- Table structure for table `hold_fill_targets`
763 DROP TABLE IF EXISTS `hold_fill_targets`;
764 CREATE TABLE hold_fill_targets (
765 `borrowernumber` int(11) NOT NULL,
766 `biblionumber` int(11) NOT NULL,
767 `itemnumber` int(11) NOT NULL,
768 `source_branchcode` varchar(10) default NULL,
769 `item_level_request` tinyint(4) NOT NULL default 0,
770 PRIMARY KEY `itemnumber` (`itemnumber`),
771 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
772 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
773 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
774 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
775 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
776 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
777 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
778 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
779 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
780 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
783 -- Table structure for table `import_batches`
786 DROP TABLE IF EXISTS `import_batches`;
787 CREATE TABLE `import_batches` (
788 `import_batch_id` int(11) NOT NULL auto_increment,
789 `matcher_id` int(11) default NULL,
790 `template_id` int(11) default NULL,
791 `branchcode` varchar(10) default NULL,
792 `num_biblios` int(11) NOT NULL default 0,
793 `num_items` int(11) NOT NULL default 0,
794 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
795 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
796 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
797 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
798 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
799 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
800 `file_name` varchar(100),
801 `comments` mediumtext,
802 PRIMARY KEY (`import_batch_id`),
803 KEY `branchcode` (`branchcode`)
804 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
807 -- Table structure for table `import_records`
810 DROP TABLE IF EXISTS `import_records`;
811 CREATE TABLE `import_records` (
812 `import_record_id` int(11) NOT NULL auto_increment,
813 `import_batch_id` int(11) NOT NULL,
814 `branchcode` varchar(10) default NULL,
815 `record_sequence` int(11) NOT NULL default 0,
816 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
817 `import_date` DATE default NULL,
818 `marc` longblob NOT NULL,
819 `marcxml` longtext NOT NULL,
820 `marcxml_old` longtext NOT NULL,
821 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
822 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
823 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
824 `import_error` mediumtext,
825 `encoding` varchar(40) NOT NULL default '',
826 `z3950random` varchar(40) default NULL,
827 PRIMARY KEY (`import_record_id`),
828 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
829 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
830 KEY `branchcode` (`branchcode`),
831 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
832 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
835 -- Table structure for `import_record_matches`
837 DROP TABLE IF EXISTS `import_record_matches`;
838 CREATE TABLE `import_record_matches` (
839 `import_record_id` int(11) NOT NULL,
840 `candidate_match_id` int(11) NOT NULL,
841 `score` int(11) NOT NULL default 0,
842 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
843 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
844 KEY `record_score` (`import_record_id`, `score`)
845 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
848 -- Table structure for table `import_biblios`
851 DROP TABLE IF EXISTS `import_biblios`;
852 CREATE TABLE `import_biblios` (
853 `import_record_id` int(11) NOT NULL,
854 `matched_biblionumber` int(11) default NULL,
855 `control_number` varchar(25) default NULL,
856 `original_source` varchar(25) default NULL,
857 `title` varchar(128) default NULL,
858 `author` varchar(80) default NULL,
859 `isbn` varchar(30) default NULL,
860 `issn` varchar(9) default NULL,
861 `has_items` tinyint(1) NOT NULL default 0,
862 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
863 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
864 KEY `matched_biblionumber` (`matched_biblionumber`),
865 KEY `title` (`title`),
867 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
870 -- Table structure for table `import_items`
873 DROP TABLE IF EXISTS `import_items`;
874 CREATE TABLE `import_items` (
875 `import_items_id` int(11) NOT NULL auto_increment,
876 `import_record_id` int(11) NOT NULL,
877 `itemnumber` int(11) default NULL,
878 `branchcode` varchar(10) default NULL,
879 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
880 `marcxml` longtext NOT NULL,
881 `import_error` mediumtext,
882 PRIMARY KEY (`import_items_id`),
883 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
884 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
885 KEY `itemnumber` (`itemnumber`),
886 KEY `branchcode` (`branchcode`)
887 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
890 -- Table structure for table `issues`
893 DROP TABLE IF EXISTS `issues`;
894 CREATE TABLE `issues` (
895 `borrowernumber` int(11) default NULL,
896 `itemnumber` int(11) default NULL,
897 `date_due` date default NULL,
898 `branchcode` varchar(10) default NULL,
899 `issuingbranch` varchar(18) default NULL,
900 `returndate` date default NULL,
901 `lastreneweddate` date default NULL,
902 `return` varchar(4) default NULL,
903 `renewals` tinyint(4) default NULL,
904 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
905 `issuedate` date default NULL,
906 KEY `issuesborridx` (`borrowernumber`),
907 KEY `issuesitemidx` (`itemnumber`),
908 KEY `bordate` (`borrowernumber`,`timestamp`),
909 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
910 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
911 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
914 -- Table structure for table `issuingrules`
917 DROP TABLE IF EXISTS `issuingrules`;
918 CREATE TABLE `issuingrules` (
919 `categorycode` varchar(10) NOT NULL default '',
920 `itemtype` varchar(10) NOT NULL default '',
921 `restrictedtype` tinyint(1) default NULL,
922 `rentaldiscount` decimal(28,6) default NULL,
923 `reservecharge` decimal(28,6) default NULL,
924 `fine` decimal(28,6) default NULL,
925 `firstremind` int(11) default NULL,
926 `chargeperiod` int(11) default NULL,
927 `accountsent` int(11) default NULL,
928 `chargename` varchar(100) default NULL,
929 `maxissueqty` int(4) default NULL,
930 `issuelength` int(4) default NULL,
931 `branchcode` varchar(10) NOT NULL default '',
932 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
933 KEY `categorycode` (`categorycode`),
934 KEY `itemtype` (`itemtype`)
935 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
938 -- Table structure for table `items`
941 DROP TABLE IF EXISTS `items`;
942 CREATE TABLE `items` (
943 `itemnumber` int(11) NOT NULL auto_increment,
944 `biblionumber` int(11) NOT NULL default 0,
945 `biblioitemnumber` int(11) NOT NULL default 0,
946 `barcode` varchar(20) default NULL,
947 `dateaccessioned` date default NULL,
948 `booksellerid` mediumtext default NULL,
949 `homebranch` varchar(10) default NULL,
950 `price` decimal(8,2) default NULL,
951 `replacementprice` decimal(8,2) default NULL,
952 `replacementpricedate` date default NULL,
953 `datelastborrowed` date default NULL,
954 `datelastseen` date default NULL,
955 `stack` tinyint(1) default NULL,
956 `notforloan` tinyint(1) NOT NULL default 0,
957 `damaged` tinyint(1) NOT NULL default 0,
958 `itemlost` tinyint(1) NOT NULL default 0,
959 `wthdrawn` tinyint(1) NOT NULL default 0,
960 `itemcallnumber` varchar(255) default NULL,
961 `issues` smallint(6) default NULL,
962 `renewals` smallint(6) default NULL,
963 `reserves` smallint(6) default NULL,
964 `restricted` tinyint(1) default NULL,
965 `itemnotes` mediumtext,
966 `holdingbranch` varchar(10) default NULL,
967 `paidfor` mediumtext,
968 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
969 `location` varchar(80) default NULL,
970 `permanent_location` varchar(80) default NULL,
971 `onloan` date default NULL,
972 `cn_source` varchar(10) default NULL,
973 `cn_sort` varchar(30) default NULL,
974 `ccode` varchar(10) default NULL,
975 `materials` varchar(10) default NULL,
976 `uri` varchar(255) default NULL,
977 `itype` varchar(10) default NULL,
978 `more_subfields_xml` longtext default NULL,
979 `enumchron` varchar(80) default NULL,
980 `copynumber` varchar(32) default NULL,
981 PRIMARY KEY (`itemnumber`),
982 UNIQUE KEY `itembarcodeidx` (`barcode`),
983 KEY `itembinoidx` (`biblioitemnumber`),
984 KEY `itembibnoidx` (`biblionumber`),
985 KEY `homebranch` (`homebranch`),
986 KEY `holdingbranch` (`holdingbranch`),
987 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
988 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
989 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
990 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
993 -- Table structure for table `itemtypes`
996 DROP TABLE IF EXISTS `itemtypes`;
997 CREATE TABLE `itemtypes` (
998 `itemtype` varchar(10) NOT NULL default '',
999 `description` mediumtext,
1000 `renewalsallowed` smallint(6) default NULL,
1001 `rentalcharge` double(16,4) default NULL,
1002 `notforloan` smallint(6) default NULL,
1003 `imageurl` varchar(200) default NULL,
1005 PRIMARY KEY (`itemtype`),
1006 UNIQUE KEY `itemtype` (`itemtype`)
1007 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1010 -- Table structure for table `labels_batches`
1013 DROP TABLE IF EXISTS `labels_batches`;
1014 CREATE TABLE `labels_batches` (
1015 `label_id` int(11) NOT NULL auto_increment,
1016 `batch_id` int(10) NOT NULL default '1',
1017 `item_number` int(11) NOT NULL default '0',
1018 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1019 `branch_code` varchar(10) NOT NULL default 'NB',
1020 PRIMARY KEY USING BTREE (`label_id`),
1021 KEY `branch_fk` (`branch_code`),
1022 KEY `item_fk` (`item_number`),
1023 CONSTRAINT `item_fk_constraint` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE,
1024 CONSTRAINT `branch_fk_constraint` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE
1025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1028 -- Table structure for table `labels_layouts`
1031 DROP TABLE IF EXISTS `labels_layouts`;
1032 CREATE TABLE `labels_layouts` (
1033 `layout_id` int(4) NOT NULL auto_increment,
1034 `barcode_type` char(100) NOT NULL default 'CODE39',
1035 `printing_type` char(32) NOT NULL default 'BAR',
1036 `layout_name` char(20) NOT NULL default 'DEFAULT',
1037 `guidebox` int(1) default '0',
1038 `font` char(10) character set utf8 collate utf8_unicode_ci NOT NULL default 'TR',
1039 `font_size` int(4) NOT NULL default '10',
1040 `callnum_split` int(1) default '0',
1041 `text_justify` char(1) character set utf8 collate utf8_unicode_ci NOT NULL default 'L',
1042 `format_string` varchar(210) NOT NULL default 'barcode',
1043 PRIMARY KEY USING BTREE (`layout_id`)
1044 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1047 -- Table structure for table `labels_templates`
1050 DROP TABLE IF EXISTS `labels_templates`;
1051 CREATE TABLE `labels_templates` (
1052 `template_id` int(4) NOT NULL auto_increment,
1053 `profile_id` int(4) default NULL,
1054 `template_code` char(100) NOT NULL default 'DEFAULT TEMPLATE',
1055 `template_desc` char(100) NOT NULL default 'Default description',
1056 `page_width` float NOT NULL default '0',
1057 `page_height` float NOT NULL default '0',
1058 `label_width` float NOT NULL default '0',
1059 `label_height` float NOT NULL default '0',
1060 `top_text_margin` float NOT NULL default '0',
1061 `left_text_margin` float NOT NULL default '0',
1062 `top_margin` float NOT NULL default '0',
1063 `left_margin` float NOT NULL default '0',
1064 `cols` int(2) NOT NULL default '0',
1065 `rows` int(2) NOT NULL default '0',
1066 `col_gap` float NOT NULL default '0',
1067 `row_gap` float NOT NULL default '0',
1068 `units` char(20) NOT NULL default 'POINT',
1069 PRIMARY KEY (`template_id`),
1070 KEY `template_profile_fk_constraint` (`profile_id`)
1071 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1074 -- Table structure for table `letter`
1077 DROP TABLE IF EXISTS `letter`;
1078 CREATE TABLE `letter` (
1079 `module` varchar(20) NOT NULL default '',
1080 `code` varchar(20) NOT NULL default '',
1081 `name` varchar(100) NOT NULL default '',
1082 `title` varchar(200) NOT NULL default '',
1084 PRIMARY KEY (`module`,`code`)
1085 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1088 -- Table structure for table `marc_subfield_structure`
1091 DROP TABLE IF EXISTS `marc_subfield_structure`;
1092 CREATE TABLE `marc_subfield_structure` (
1093 `tagfield` varchar(3) NOT NULL default '',
1094 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1095 `liblibrarian` varchar(255) NOT NULL default '',
1096 `libopac` varchar(255) NOT NULL default '',
1097 `repeatable` tinyint(4) NOT NULL default 0,
1098 `mandatory` tinyint(4) NOT NULL default 0,
1099 `kohafield` varchar(40) default NULL,
1100 `tab` tinyint(1) default NULL,
1101 `authorised_value` varchar(20) default NULL,
1102 `authtypecode` varchar(20) default NULL,
1103 `value_builder` varchar(80) default NULL,
1104 `isurl` tinyint(1) default NULL,
1105 `hidden` tinyint(1) default NULL,
1106 `frameworkcode` varchar(4) NOT NULL default '',
1107 `seealso` varchar(1100) default NULL,
1108 `link` varchar(80) default NULL,
1109 `defaultvalue` text default NULL,
1110 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1111 KEY `kohafield_2` (`kohafield`),
1112 KEY `tab` (`frameworkcode`,`tab`),
1113 KEY `kohafield` (`frameworkcode`,`kohafield`)
1114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1117 -- Table structure for table `marc_tag_structure`
1120 DROP TABLE IF EXISTS `marc_tag_structure`;
1121 CREATE TABLE `marc_tag_structure` (
1122 `tagfield` varchar(3) NOT NULL default '',
1123 `liblibrarian` varchar(255) NOT NULL default '',
1124 `libopac` varchar(255) NOT NULL default '',
1125 `repeatable` tinyint(4) NOT NULL default 0,
1126 `mandatory` tinyint(4) NOT NULL default 0,
1127 `authorised_value` varchar(10) default NULL,
1128 `frameworkcode` varchar(4) NOT NULL default '',
1129 PRIMARY KEY (`frameworkcode`,`tagfield`)
1130 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1133 -- Table structure for table `marc_matchers`
1136 DROP TABLE IF EXISTS `marc_matchers`;
1137 CREATE TABLE `marc_matchers` (
1138 `matcher_id` int(11) NOT NULL auto_increment,
1139 `code` varchar(10) NOT NULL default '',
1140 `description` varchar(255) NOT NULL default '',
1141 `record_type` varchar(10) NOT NULL default 'biblio',
1142 `threshold` int(11) NOT NULL default 0,
1143 PRIMARY KEY (`matcher_id`),
1144 KEY `code` (`code`),
1145 KEY `record_type` (`record_type`)
1146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1149 -- Table structure for table `matchpoints`
1151 DROP TABLE IF EXISTS `matchpoints`;
1152 CREATE TABLE `matchpoints` (
1153 `matcher_id` int(11) NOT NULL,
1154 `matchpoint_id` int(11) NOT NULL auto_increment,
1155 `search_index` varchar(30) NOT NULL default '',
1156 `score` int(11) NOT NULL default 0,
1157 PRIMARY KEY (`matchpoint_id`),
1158 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1159 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1160 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1164 -- Table structure for table `matchpoint_components`
1166 DROP TABLE IF EXISTS `matchpoint_components`;
1167 CREATE TABLE `matchpoint_components` (
1168 `matchpoint_id` int(11) NOT NULL,
1169 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1170 sequence int(11) NOT NULL default 0,
1171 tag varchar(3) NOT NULL default '',
1172 subfields varchar(40) NOT NULL default '',
1173 offset int(4) NOT NULL default 0,
1174 length int(4) NOT NULL default 0,
1175 PRIMARY KEY (`matchpoint_component_id`),
1176 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1177 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1178 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1179 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1182 -- Table structure for table `matcher_component_norms`
1184 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1185 CREATE TABLE `matchpoint_component_norms` (
1186 `matchpoint_component_id` int(11) NOT NULL,
1187 `sequence` int(11) NOT NULL default 0,
1188 `norm_routine` varchar(50) NOT NULL default '',
1189 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1190 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1191 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1192 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1195 -- Table structure for table `matcher_matchpoints`
1197 DROP TABLE IF EXISTS `matcher_matchpoints`;
1198 CREATE TABLE `matcher_matchpoints` (
1199 `matcher_id` int(11) NOT NULL,
1200 `matchpoint_id` int(11) NOT NULL,
1201 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1202 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1203 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1204 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1208 -- Table structure for table `matchchecks`
1210 DROP TABLE IF EXISTS `matchchecks`;
1211 CREATE TABLE `matchchecks` (
1212 `matcher_id` int(11) NOT NULL,
1213 `matchcheck_id` int(11) NOT NULL auto_increment,
1214 `source_matchpoint_id` int(11) NOT NULL,
1215 `target_matchpoint_id` int(11) NOT NULL,
1216 PRIMARY KEY (`matchcheck_id`),
1217 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1218 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1219 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1220 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1221 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1222 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1223 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1226 -- Table structure for table `notifys`
1229 DROP TABLE IF EXISTS `notifys`;
1230 CREATE TABLE `notifys` (
1231 `notify_id` int(11) NOT NULL default 0,
1232 `borrowernumber` int(11) NOT NULL default 0,
1233 `itemnumber` int(11) NOT NULL default 0,
1234 `notify_date` date default NULL,
1235 `notify_send_date` date default NULL,
1236 `notify_level` int(1) NOT NULL default 0,
1237 `method` varchar(20) NOT NULL default ''
1238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1241 -- Table structure for table `nozebra`
1244 DROP TABLE IF EXISTS `nozebra`;
1245 CREATE TABLE `nozebra` (
1246 `server` varchar(20) NOT NULL,
1247 `indexname` varchar(40) NOT NULL,
1248 `value` varchar(250) NOT NULL,
1249 `biblionumbers` longtext NOT NULL,
1250 KEY `indexname` (`server`,`indexname`),
1251 KEY `value` (`server`,`value`))
1252 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1255 -- Table structure for table `old_issues`
1258 DROP TABLE IF EXISTS `old_issues`;
1259 CREATE TABLE `old_issues` (
1260 `borrowernumber` int(11) default NULL,
1261 `itemnumber` int(11) default NULL,
1262 `date_due` date default NULL,
1263 `branchcode` varchar(10) default NULL,
1264 `issuingbranch` varchar(18) default NULL,
1265 `returndate` date default NULL,
1266 `lastreneweddate` date default NULL,
1267 `return` varchar(4) default NULL,
1268 `renewals` tinyint(4) default NULL,
1269 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1270 `issuedate` date default NULL,
1271 KEY `old_issuesborridx` (`borrowernumber`),
1272 KEY `old_issuesitemidx` (`itemnumber`),
1273 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1274 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1275 ON DELETE SET NULL ON UPDATE SET NULL,
1276 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1277 ON DELETE SET NULL ON UPDATE SET NULL
1278 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1281 -- Table structure for table `old_reserves`
1283 DROP TABLE IF EXISTS `old_reserves`;
1284 CREATE TABLE `old_reserves` (
1285 `borrowernumber` int(11) default NULL,
1286 `reservedate` date default NULL,
1287 `biblionumber` int(11) default NULL,
1288 `constrainttype` varchar(1) default NULL,
1289 `branchcode` varchar(10) default NULL,
1290 `notificationdate` date default NULL,
1291 `reminderdate` date default NULL,
1292 `cancellationdate` date default NULL,
1293 `reservenotes` mediumtext,
1294 `priority` smallint(6) default NULL,
1295 `found` varchar(1) default NULL,
1296 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1297 `itemnumber` int(11) default NULL,
1298 `waitingdate` date default NULL,
1299 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1300 KEY `old_reserves_biblionumber` (`biblionumber`),
1301 KEY `old_reserves_itemnumber` (`itemnumber`),
1302 KEY `old_reserves_branchcode` (`branchcode`),
1303 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1304 ON DELETE SET NULL ON UPDATE SET NULL,
1305 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1306 ON DELETE SET NULL ON UPDATE SET NULL,
1307 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1308 ON DELETE SET NULL ON UPDATE SET NULL
1309 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1312 -- Table structure for table `opac_news`
1315 DROP TABLE IF EXISTS `opac_news`;
1316 CREATE TABLE `opac_news` (
1317 `idnew` int(10) unsigned NOT NULL auto_increment,
1318 `title` varchar(250) NOT NULL default '',
1319 `new` text NOT NULL,
1320 `lang` varchar(25) NOT NULL default '',
1321 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1322 `expirationdate` date default NULL,
1323 `number` int(11) default NULL,
1324 PRIMARY KEY (`idnew`)
1325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1328 -- Table structure for table `overduerules`
1331 DROP TABLE IF EXISTS `overduerules`;
1332 CREATE TABLE `overduerules` (
1333 `branchcode` varchar(10) NOT NULL default '',
1334 `categorycode` varchar(10) NOT NULL default '',
1335 `delay1` int(4) default 0,
1336 `letter1` varchar(20) default NULL,
1337 `debarred1` varchar(1) default 0,
1338 `delay2` int(4) default 0,
1339 `debarred2` varchar(1) default 0,
1340 `letter2` varchar(20) default NULL,
1341 `delay3` int(4) default 0,
1342 `letter3` varchar(20) default NULL,
1343 `debarred3` int(1) default 0,
1344 PRIMARY KEY (`branchcode`,`categorycode`)
1345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1348 -- Table structure for table `patroncards`
1351 DROP TABLE IF EXISTS `patroncards`;
1352 CREATE TABLE `patroncards` (
1353 `cardid` int(11) NOT NULL auto_increment,
1354 `batch_id` varchar(10) NOT NULL default '1',
1355 `borrowernumber` int(11) NOT NULL,
1356 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1357 PRIMARY KEY (`cardid`),
1358 KEY `patroncards_ibfk_1` (`borrowernumber`),
1359 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1360 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1363 -- Table structure for table `patronimage`
1366 DROP TABLE IF EXISTS `patronimage`;
1367 CREATE TABLE `patronimage` (
1368 `cardnumber` varchar(16) NOT NULL,
1369 `mimetype` varchar(15) NOT NULL,
1370 `imagefile` mediumblob NOT NULL,
1371 PRIMARY KEY (`cardnumber`),
1372 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1373 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1376 -- Table structure for table `printers`
1379 DROP TABLE IF EXISTS `printers`;
1380 CREATE TABLE `printers` (
1381 `printername` varchar(40) NOT NULL default '',
1382 `printqueue` varchar(20) default NULL,
1383 `printtype` varchar(20) default NULL,
1384 PRIMARY KEY (`printername`)
1385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1388 -- Table structure for table `printers_profile`
1391 DROP TABLE IF EXISTS `printers_profile`;
1392 CREATE TABLE `printers_profile` (
1393 `profile_id` int(4) NOT NULL auto_increment,
1394 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1395 `template_id` int(4) NOT NULL default '0',
1396 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1397 `offset_horz` float NOT NULL default '0',
1398 `offset_vert` float NOT NULL default '0',
1399 `creep_horz` float NOT NULL default '0',
1400 `creep_vert` float NOT NULL default '0',
1401 `units` char(20) NOT NULL default 'POINT',
1402 PRIMARY KEY (`profile_id`),
1403 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`)
1404 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1407 -- Table structure for table `repeatable_holidays`
1410 DROP TABLE IF EXISTS `repeatable_holidays`;
1411 CREATE TABLE `repeatable_holidays` (
1412 `id` int(11) NOT NULL auto_increment,
1413 `branchcode` varchar(10) NOT NULL default '',
1414 `weekday` smallint(6) default NULL,
1415 `day` smallint(6) default NULL,
1416 `month` smallint(6) default NULL,
1417 `title` varchar(50) NOT NULL default '',
1418 `description` text NOT NULL,
1420 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1423 -- Table structure for table `reports_dictionary`
1426 DROP TABLE IF EXISTS `reports_dictionary`;
1427 CREATE TABLE reports_dictionary (
1428 `id` int(11) NOT NULL auto_increment,
1429 `name` varchar(255) default NULL,
1431 `date_created` datetime default NULL,
1432 `date_modified` datetime default NULL,
1434 `area` int(11) default NULL,
1436 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1439 -- Table structure for table `reserveconstraints`
1442 DROP TABLE IF EXISTS `reserveconstraints`;
1443 CREATE TABLE `reserveconstraints` (
1444 `borrowernumber` int(11) NOT NULL default 0,
1445 `reservedate` date default NULL,
1446 `biblionumber` int(11) NOT NULL default 0,
1447 `biblioitemnumber` int(11) default NULL,
1448 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1449 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1452 -- Table structure for table `reserves`
1455 DROP TABLE IF EXISTS `reserves`;
1456 CREATE TABLE `reserves` (
1457 `borrowernumber` int(11) NOT NULL default 0,
1458 `reservedate` date default NULL,
1459 `biblionumber` int(11) NOT NULL default 0,
1460 `constrainttype` varchar(1) default NULL,
1461 `branchcode` varchar(10) default NULL,
1462 `notificationdate` date default NULL,
1463 `reminderdate` date default NULL,
1464 `cancellationdate` date default NULL,
1465 `reservenotes` mediumtext,
1466 `priority` smallint(6) default NULL,
1467 `found` varchar(1) default NULL,
1468 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1469 `itemnumber` int(11) default NULL,
1470 `waitingdate` date default NULL,
1471 KEY `borrowernumber` (`borrowernumber`),
1472 KEY `biblionumber` (`biblionumber`),
1473 KEY `itemnumber` (`itemnumber`),
1474 KEY `branchcode` (`branchcode`),
1475 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1476 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1477 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1478 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1479 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1482 -- Table structure for table `reviews`
1485 DROP TABLE IF EXISTS `reviews`;
1486 CREATE TABLE `reviews` (
1487 `reviewid` int(11) NOT NULL auto_increment,
1488 `borrowernumber` int(11) default NULL,
1489 `biblionumber` int(11) default NULL,
1491 `approved` tinyint(4) default NULL,
1492 `datereviewed` datetime default NULL,
1493 PRIMARY KEY (`reviewid`)
1494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1497 -- Table structure for table `roadtype`
1500 DROP TABLE IF EXISTS `roadtype`;
1501 CREATE TABLE `roadtype` (
1502 `roadtypeid` int(11) NOT NULL auto_increment,
1503 `road_type` varchar(100) NOT NULL default '',
1504 PRIMARY KEY (`roadtypeid`)
1505 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1508 -- Table structure for table `saved_sql`
1511 DROP TABLE IF EXISTS `saved_sql`;
1512 CREATE TABLE saved_sql (
1513 `id` int(11) NOT NULL auto_increment,
1514 `borrowernumber` int(11) default NULL,
1515 `date_created` datetime default NULL,
1516 `last_modified` datetime default NULL,
1518 `last_run` datetime default NULL,
1519 `report_name` varchar(255) default NULL,
1520 `type` varchar(255) default NULL,
1523 KEY boridx (`borrowernumber`)
1524 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1528 -- Table structure for `saved_reports`
1531 DROP TABLE IF EXISTS `saved_reports`;
1532 CREATE TABLE saved_reports (
1533 `id` int(11) NOT NULL auto_increment,
1534 `report_id` int(11) default NULL,
1536 `date_run` datetime default NULL,
1538 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1542 -- Table structure for table `search_history`
1545 DROP TABLE IF EXISTS `search_history`;
1546 CREATE TABLE IF NOT EXISTS `search_history` (
1547 `userid` int(11) NOT NULL,
1548 `sessionid` varchar(32) NOT NULL,
1549 `query_desc` varchar(255) NOT NULL,
1550 `query_cgi` varchar(255) NOT NULL,
1551 `total` int(11) NOT NULL,
1552 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1553 KEY `userid` (`userid`),
1554 KEY `sessionid` (`sessionid`)
1555 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1559 -- Table structure for table `serial`
1562 DROP TABLE IF EXISTS `serial`;
1563 CREATE TABLE `serial` (
1564 `serialid` int(11) NOT NULL auto_increment,
1565 `biblionumber` varchar(100) NOT NULL default '',
1566 `subscriptionid` varchar(100) NOT NULL default '',
1567 `serialseq` varchar(100) NOT NULL default '',
1568 `status` tinyint(4) NOT NULL default 0,
1569 `planneddate` date default NULL,
1571 `publisheddate` date default NULL,
1572 `itemnumber` text default NULL,
1573 `claimdate` date default NULL,
1574 `routingnotes` text,
1575 PRIMARY KEY (`serialid`)
1576 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1579 -- Table structure for table `sessions`
1582 DROP TABLE IF EXISTS sessions;
1583 CREATE TABLE sessions (
1584 `id` varchar(32) NOT NULL,
1585 `a_session` text NOT NULL,
1587 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1590 -- Table structure for table `special_holidays`
1593 DROP TABLE IF EXISTS `special_holidays`;
1594 CREATE TABLE `special_holidays` (
1595 `id` int(11) NOT NULL auto_increment,
1596 `branchcode` varchar(10) NOT NULL default '',
1597 `day` smallint(6) NOT NULL default 0,
1598 `month` smallint(6) NOT NULL default 0,
1599 `year` smallint(6) NOT NULL default 0,
1600 `isexception` smallint(1) NOT NULL default 1,
1601 `title` varchar(50) NOT NULL default '',
1602 `description` text NOT NULL,
1604 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1607 -- Table structure for table `statistics`
1610 DROP TABLE IF EXISTS `statistics`;
1611 CREATE TABLE `statistics` (
1612 `datetime` datetime default NULL,
1613 `branch` varchar(10) default NULL,
1614 `proccode` varchar(4) default NULL,
1615 `value` double(16,4) default NULL,
1616 `type` varchar(16) default NULL,
1618 `usercode` varchar(10) default NULL,
1619 `itemnumber` int(11) default NULL,
1620 `itemtype` varchar(10) default NULL,
1621 `borrowernumber` int(11) default NULL,
1622 `associatedborrower` int(11) default NULL,
1623 KEY `timeidx` (`datetime`)
1624 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1627 -- Table structure for table `stopwords`
1630 DROP TABLE IF EXISTS `stopwords`;
1631 CREATE TABLE `stopwords` (
1632 `word` varchar(255) default NULL
1633 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1636 -- Table structure for table `subscription`
1639 DROP TABLE IF EXISTS `subscription`;
1640 CREATE TABLE `subscription` (
1641 `biblionumber` int(11) NOT NULL default 0,
1642 `subscriptionid` int(11) NOT NULL auto_increment,
1643 `librarian` varchar(100) default '',
1644 `startdate` date default NULL,
1645 `aqbooksellerid` int(11) default 0,
1646 `cost` int(11) default 0,
1647 `aqbudgetid` int(11) default 0,
1648 `weeklength` int(11) default 0,
1649 `monthlength` int(11) default 0,
1650 `numberlength` int(11) default 0,
1651 `periodicity` tinyint(4) default 0,
1652 `dow` varchar(100) default '',
1653 `numberingmethod` varchar(100) default '',
1655 `status` varchar(100) NOT NULL default '',
1656 `add1` int(11) default 0,
1657 `every1` int(11) default 0,
1658 `whenmorethan1` int(11) default 0,
1659 `setto1` int(11) default NULL,
1660 `lastvalue1` int(11) default NULL,
1661 `add2` int(11) default 0,
1662 `every2` int(11) default 0,
1663 `whenmorethan2` int(11) default 0,
1664 `setto2` int(11) default NULL,
1665 `lastvalue2` int(11) default NULL,
1666 `add3` int(11) default 0,
1667 `every3` int(11) default 0,
1668 `innerloop1` int(11) default 0,
1669 `innerloop2` int(11) default 0,
1670 `innerloop3` int(11) default 0,
1671 `whenmorethan3` int(11) default 0,
1672 `setto3` int(11) default NULL,
1673 `lastvalue3` int(11) default NULL,
1674 `issuesatonce` tinyint(3) NOT NULL default 1,
1675 `firstacquidate` date default NULL,
1676 `manualhistory` tinyint(1) NOT NULL default 0,
1677 `irregularity` text,
1678 `letter` varchar(20) default NULL,
1679 `numberpattern` tinyint(3) default 0,
1680 `distributedto` text,
1681 `internalnotes` longtext,
1683 `location` varchar(80) NULL default '',
1684 `branchcode` varchar(10) NOT NULL default '',
1685 `hemisphere` tinyint(3) default 0,
1686 `lastbranch` varchar(10),
1687 `serialsadditems` tinyint(1) NOT NULL default '0',
1688 `staffdisplaycount` VARCHAR(10) NULL,
1689 `opacdisplaycount` VARCHAR(10) NULL,
1690 `graceperiod` int(11) NOT NULL default '0',
1691 PRIMARY KEY (`subscriptionid`)
1692 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1695 -- Table structure for table `subscriptionhistory`
1698 DROP TABLE IF EXISTS `subscriptionhistory`;
1699 CREATE TABLE `subscriptionhistory` (
1700 `biblionumber` int(11) NOT NULL default 0,
1701 `subscriptionid` int(11) NOT NULL default 0,
1702 `histstartdate` date default NULL,
1703 `enddate` date default NULL,
1704 `missinglist` longtext NOT NULL,
1705 `recievedlist` longtext NOT NULL,
1706 `opacnote` varchar(150) NOT NULL default '',
1707 `librariannote` varchar(150) NOT NULL default '',
1708 PRIMARY KEY (`subscriptionid`),
1709 KEY `biblionumber` (`biblionumber`)
1710 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1713 -- Table structure for table `subscriptionroutinglist`
1716 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1717 CREATE TABLE `subscriptionroutinglist` (
1718 `routingid` int(11) NOT NULL auto_increment,
1719 `borrowernumber` int(11) default NULL,
1720 `ranking` int(11) default NULL,
1721 `subscriptionid` int(11) default NULL,
1722 PRIMARY KEY (`routingid`)
1723 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1726 -- Table structure for table `suggestions`
1729 DROP TABLE IF EXISTS `suggestions`;
1730 CREATE TABLE `suggestions` (
1731 `suggestionid` int(8) NOT NULL auto_increment,
1732 `suggestedby` int(11) NOT NULL default 0,
1733 `suggesteddate` date NOT NULL default 0,
1734 `managedby` int(11) default NULL,
1735 `manageddate` date default NULL,
1736 `STATUS` varchar(10) NOT NULL default '',
1738 `author` varchar(80) default NULL,
1739 `title` varchar(80) default NULL,
1740 `copyrightdate` smallint(6) default NULL,
1741 `publishercode` varchar(255) default NULL,
1742 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1743 `volumedesc` varchar(255) default NULL,
1744 `publicationyear` smallint(6) default 0,
1745 `place` varchar(255) default NULL,
1746 `isbn` varchar(30) default NULL,
1747 `mailoverseeing` smallint(1) default 0,
1748 `biblionumber` int(11) default NULL,
1750 PRIMARY KEY (`suggestionid`),
1751 KEY `suggestedby` (`suggestedby`),
1752 KEY `managedby` (`managedby`)
1753 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1756 -- Table structure for table `systempreferences`
1759 DROP TABLE IF EXISTS `systempreferences`;
1760 CREATE TABLE `systempreferences` (
1761 `variable` varchar(50) NOT NULL default '',
1763 `options` mediumtext,
1765 `type` varchar(20) default NULL,
1766 PRIMARY KEY (`variable`)
1767 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1770 -- Table structure for table `tags`
1773 DROP TABLE IF EXISTS `tags`;
1774 CREATE TABLE `tags` (
1775 `entry` varchar(255) NOT NULL default '',
1776 `weight` bigint(20) NOT NULL default 0,
1777 PRIMARY KEY (`entry`)
1778 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1781 -- Table structure for table `tags_all`
1784 DROP TABLE IF EXISTS `tags_all`;
1785 CREATE TABLE `tags_all` (
1786 `tag_id` int(11) NOT NULL auto_increment,
1787 `borrowernumber` int(11) NOT NULL,
1788 `biblionumber` int(11) NOT NULL,
1789 `term` varchar(255) NOT NULL,
1790 `language` int(4) default NULL,
1791 `date_created` datetime NOT NULL,
1792 PRIMARY KEY (`tag_id`),
1793 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1794 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1795 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1796 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1797 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1798 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1802 -- Table structure for table `tags_approval`
1805 DROP TABLE IF EXISTS `tags_approval`;
1806 CREATE TABLE `tags_approval` (
1807 `term` varchar(255) NOT NULL,
1808 `approved` int(1) NOT NULL default '0',
1809 `date_approved` datetime default NULL,
1810 `approved_by` int(11) default NULL,
1811 `weight_total` int(9) NOT NULL default '1',
1812 PRIMARY KEY (`term`),
1813 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1814 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1815 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1816 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1819 -- Table structure for table `tags_index`
1822 DROP TABLE IF EXISTS `tags_index`;
1823 CREATE TABLE `tags_index` (
1824 `term` varchar(255) NOT NULL,
1825 `biblionumber` int(11) NOT NULL,
1826 `weight` int(9) NOT NULL default '1',
1827 PRIMARY KEY (`term`,`biblionumber`),
1828 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1829 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1830 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1831 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1832 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1833 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1836 -- Table structure for table `userflags`
1839 DROP TABLE IF EXISTS `userflags`;
1840 CREATE TABLE `userflags` (
1841 `bit` int(11) NOT NULL default 0,
1842 `flag` varchar(30) default NULL,
1843 `flagdesc` varchar(255) default NULL,
1844 `defaulton` int(11) default NULL,
1846 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1849 -- Table structure for table `virtualshelves`
1852 DROP TABLE IF EXISTS `virtualshelves`;
1853 CREATE TABLE `virtualshelves` (
1854 `shelfnumber` int(11) NOT NULL auto_increment,
1855 `shelfname` varchar(255) default NULL,
1856 `owner` varchar(80) default NULL,
1857 `category` varchar(1) default NULL,
1858 `sortfield` varchar(16) default NULL,
1859 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1860 PRIMARY KEY (`shelfnumber`)
1861 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1864 -- Table structure for table `virtualshelfcontents`
1867 DROP TABLE IF EXISTS `virtualshelfcontents`;
1868 CREATE TABLE `virtualshelfcontents` (
1869 `shelfnumber` int(11) NOT NULL default 0,
1870 `biblionumber` int(11) NOT NULL default 0,
1871 `flags` int(11) default NULL,
1872 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1873 KEY `shelfnumber` (`shelfnumber`),
1874 KEY `biblionumber` (`biblionumber`),
1875 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1876 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1877 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1880 -- Table structure for table `z3950servers`
1883 DROP TABLE IF EXISTS `z3950servers`;
1884 CREATE TABLE `z3950servers` (
1885 `host` varchar(255) default NULL,
1886 `port` int(11) default NULL,
1887 `db` varchar(255) default NULL,
1888 `userid` varchar(255) default NULL,
1889 `password` varchar(255) default NULL,
1891 `id` int(11) NOT NULL auto_increment,
1892 `checked` smallint(6) default NULL,
1893 `rank` int(11) default NULL,
1894 `syntax` varchar(80) default NULL,
1896 `position` enum('primary','secondary','') NOT NULL default 'primary',
1897 `type` enum('zed','opensearch') NOT NULL default 'zed',
1898 `encoding` text default NULL,
1899 `description` text NOT NULL,
1901 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1904 -- Table structure for table `zebraqueue`
1907 DROP TABLE IF EXISTS `zebraqueue`;
1908 CREATE TABLE `zebraqueue` (
1909 `id` int(11) NOT NULL auto_increment,
1910 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1911 `operation` char(20) NOT NULL default '',
1912 `server` char(20) NOT NULL default '',
1913 `done` int(11) NOT NULL default '0',
1914 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1916 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1917 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1919 DROP TABLE IF EXISTS `services_throttle`;
1920 CREATE TABLE `services_throttle` (
1921 `service_type` varchar(10) NOT NULL default '',
1922 `service_count` varchar(45) default NULL,
1923 PRIMARY KEY (`service_type`)
1924 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1926 -- http://www.w3.org/International/articles/language-tags/
1929 DROP TABLE IF EXISTS language_subtag_registry;
1930 CREATE TABLE language_subtag_registry (
1932 type varchar(25), -- language-script-region-variant-extension-privateuse
1933 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1935 id int(11) NOT NULL auto_increment,
1937 KEY `subtag` (`subtag`)
1938 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1940 -- TODO: add suppress_scripts
1941 -- this maps three letter codes defined in iso639.2 back to their
1942 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1943 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1944 CREATE TABLE language_rfc4646_to_iso639 (
1945 rfc4646_subtag varchar(25),
1946 iso639_2_code varchar(25),
1947 id int(11) NOT NULL auto_increment,
1949 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1950 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1952 DROP TABLE IF EXISTS language_descriptions;
1953 CREATE TABLE language_descriptions (
1957 description varchar(255),
1958 id int(11) NOT NULL auto_increment,
1961 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1963 -- bi-directional support, keyed by script subcode
1964 DROP TABLE IF EXISTS language_script_bidi;
1965 CREATE TABLE language_script_bidi (
1966 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1967 bidi varchar(3), -- rtl ltr
1968 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1969 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1971 -- TODO: need to map language subtags to script subtags for detection
1972 -- of bidi when script is not specified (like ar, he)
1973 DROP TABLE IF EXISTS language_script_mapping;
1974 CREATE TABLE language_script_mapping (
1975 language_subtag varchar(25),
1976 script_subtag varchar(25),
1977 KEY `language_subtag` (`language_subtag`)
1978 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1980 DROP TABLE IF EXISTS `permissions`;
1981 CREATE TABLE `permissions` (
1982 `module_bit` int(11) NOT NULL DEFAULT 0,
1983 `code` varchar(64) DEFAULT NULL,
1984 `description` varchar(255) DEFAULT NULL,
1985 PRIMARY KEY (`module_bit`, `code`),
1986 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1987 ON DELETE CASCADE ON UPDATE CASCADE
1988 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1990 DROP TABLE IF EXISTS `serialitems`;
1991 CREATE TABLE `serialitems` (
1992 `itemnumber` int(11) NOT NULL,
1993 `serialid` int(11) NOT NULL,
1994 UNIQUE KEY `serialitemsidx` (`itemnumber`),
1995 KEY `serialitems_sfk_1` (`serialid`),
1996 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
1997 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1999 DROP TABLE IF EXISTS `user_permissions`;
2000 CREATE TABLE `user_permissions` (
2001 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2002 `module_bit` int(11) NOT NULL DEFAULT 0,
2003 `code` varchar(64) DEFAULT NULL,
2004 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2005 ON DELETE CASCADE ON UPDATE CASCADE,
2006 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2007 ON DELETE CASCADE ON UPDATE CASCADE
2008 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2011 -- Table structure for table `tmp_holdsqueue`
2014 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2015 CREATE TABLE `tmp_holdsqueue` (
2016 `biblionumber` int(11) default NULL,
2017 `itemnumber` int(11) default NULL,
2018 `barcode` varchar(20) default NULL,
2019 `surname` mediumtext NOT NULL,
2022 `borrowernumber` int(11) NOT NULL,
2023 `cardnumber` varchar(16) default NULL,
2024 `reservedate` date default NULL,
2026 `itemcallnumber` varchar(255) default NULL,
2027 `holdingbranch` varchar(10) default NULL,
2028 `pickbranch` varchar(10) default NULL,
2030 `item_level_request` tinyint(4) NOT NULL default 0
2031 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2034 -- Table structure for table `message_queue`
2037 DROP TABLE IF EXISTS `message_queue`;
2038 CREATE TABLE `message_queue` (
2039 `message_id` int(11) NOT NULL auto_increment,
2040 `borrowernumber` int(11) default NULL,
2043 `metadata` text DEFAULT NULL,
2044 `letter_code` varchar(64) DEFAULT NULL,
2045 `message_transport_type` varchar(20) NOT NULL,
2046 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2047 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2048 `to_address` mediumtext,
2049 `from_address` mediumtext,
2050 `content_type` text,
2051 KEY `message_id` (`message_id`),
2052 KEY `borrowernumber` (`borrowernumber`),
2053 KEY `message_transport_type` (`message_transport_type`),
2054 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2055 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2056 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2059 -- Table structure for table `message_transport_types`
2062 DROP TABLE IF EXISTS `message_transport_types`;
2063 CREATE TABLE `message_transport_types` (
2064 `message_transport_type` varchar(20) NOT NULL,
2065 PRIMARY KEY (`message_transport_type`)
2066 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2069 -- Table structure for table `message_attributes`
2072 DROP TABLE IF EXISTS `message_attributes`;
2073 CREATE TABLE `message_attributes` (
2074 `message_attribute_id` int(11) NOT NULL auto_increment,
2075 `message_name` varchar(20) NOT NULL default '',
2076 `takes_days` tinyint(1) NOT NULL default '0',
2077 PRIMARY KEY (`message_attribute_id`),
2078 UNIQUE KEY `message_name` (`message_name`)
2079 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2082 -- Table structure for table `message_transports`
2085 DROP TABLE IF EXISTS `message_transports`;
2086 CREATE TABLE `message_transports` (
2087 `message_attribute_id` int(11) NOT NULL,
2088 `message_transport_type` varchar(20) NOT NULL,
2089 `is_digest` tinyint(1) NOT NULL default '0',
2090 `letter_module` varchar(20) NOT NULL default '',
2091 `letter_code` varchar(20) NOT NULL default '',
2092 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2093 KEY `message_transport_type` (`message_transport_type`),
2094 KEY `letter_module` (`letter_module`,`letter_code`),
2095 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2096 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2097 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2098 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2101 -- Table structure for table `borrower_message_preferences`
2104 DROP TABLE IF EXISTS `borrower_message_preferences`;
2105 CREATE TABLE `borrower_message_preferences` (
2106 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2107 `borrowernumber` int(11) default NULL,
2108 `categorycode` varchar(10) default NULL,
2109 `message_attribute_id` int(11) default '0',
2110 `days_in_advance` int(11) default '0',
2111 `wants_digest` tinyint(1) NOT NULL default '0',
2112 PRIMARY KEY (`borrower_message_preference_id`),
2113 KEY `borrowernumber` (`borrowernumber`),
2114 KEY `categorycode` (`categorycode`),
2115 KEY `message_attribute_id` (`message_attribute_id`),
2116 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2117 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2118 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2119 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2122 -- Table structure for table `borrower_message_transport_preferences`
2125 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2126 CREATE TABLE `borrower_message_transport_preferences` (
2127 `borrower_message_preference_id` int(11) NOT NULL default '0',
2128 `message_transport_type` varchar(20) NOT NULL default '0',
2129 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2130 KEY `message_transport_type` (`message_transport_type`),
2131 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,
2132 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
2133 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2136 -- Table structure for the table branch_transfer_limits
2139 DROP TABLE IF EXISTS `branch_transfer_limits`;
2140 CREATE TABLE branch_transfer_limits (
2141 limitId int(8) NOT NULL auto_increment,
2142 toBranch varchar(10) NOT NULL,
2143 fromBranch varchar(10) NOT NULL,
2144 itemtype varchar(10) NULL,
2145 ccode varchar(10) NULL,
2146 PRIMARY KEY (limitId)
2147 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2150 -- Table structure for table `item_circulation_alert_preferences`
2153 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2154 CREATE TABLE `item_circulation_alert_preferences` (
2155 `id` int(11) NOT NULL auto_increment,
2156 `branchcode` varchar(10) NOT NULL,
2157 `categorycode` varchar(10) NOT NULL,
2158 `item_type` varchar(10) NOT NULL,
2159 `notification` varchar(16) NOT NULL,
2161 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2162 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2165 -- Table structure for table `messages`
2168 CREATE TABLE `messages` (
2169 `message_id` int(11) NOT NULL auto_increment,
2170 `borrowernumber` int(11) NOT NULL,
2171 `branchcode` varchar(4) default NULL,
2172 `message_type` varchar(1) NOT NULL,
2173 `message` text NOT NULL,
2174 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2175 PRIMARY KEY (`message_id`)
2176 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2179 -- Table structure for table `accountlines`
2182 DROP TABLE IF EXISTS `accountlines`;
2183 CREATE TABLE `accountlines` (
2184 `borrowernumber` int(11) NOT NULL default 0,
2185 `accountno` smallint(6) NOT NULL default 0,
2186 `itemnumber` int(11) default NULL,
2187 `date` date default NULL,
2188 `amount` decimal(28,6) default NULL,
2189 `description` mediumtext,
2190 `dispute` mediumtext,
2191 `accounttype` varchar(5) default NULL,
2192 `amountoutstanding` decimal(28,6) default NULL,
2193 `lastincrement` decimal(28,6) default NULL,
2194 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2195 `notify_id` int(11) NOT NULL default 0,
2196 `notify_level` int(2) NOT NULL default 0,
2197 KEY `acctsborridx` (`borrowernumber`),
2198 KEY `timeidx` (`timestamp`),
2199 KEY `itemnumber` (`itemnumber`),
2200 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2201 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2202 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2205 -- Table structure for table `accountoffsets`
2208 DROP TABLE IF EXISTS `accountoffsets`;
2209 CREATE TABLE `accountoffsets` (
2210 `borrowernumber` int(11) NOT NULL default 0,
2211 `accountno` smallint(6) NOT NULL default 0,
2212 `offsetaccount` smallint(6) NOT NULL default 0,
2213 `offsetamount` decimal(28,6) default NULL,
2214 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2215 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2219 -- Table structure for table `action_logs`
2222 DROP TABLE IF EXISTS `action_logs`;
2223 CREATE TABLE `action_logs` (
2224 `action_id` int(11) NOT NULL auto_increment,
2225 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2226 `user` int(11) NOT NULL default 0,
2229 `object` int(11) default NULL,
2231 PRIMARY KEY (`action_id`),
2232 KEY (`timestamp`,`user`)
2233 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2236 -- Table structure for table `alert`
2239 DROP TABLE IF EXISTS `alert`;
2240 CREATE TABLE `alert` (
2241 `alertid` int(11) NOT NULL auto_increment,
2242 `borrowernumber` int(11) NOT NULL default 0,
2243 `type` varchar(10) NOT NULL default '',
2244 `externalid` varchar(20) NOT NULL default '',
2245 PRIMARY KEY (`alertid`),
2246 KEY `borrowernumber` (`borrowernumber`),
2247 KEY `type` (`type`,`externalid`)
2248 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2251 -- Table structure for table `aqbasketgroups`
2254 DROP TABLE IF EXISTS `aqbasketgroups`;
2255 CREATE TABLE `aqbasketgroups` (
2256 `id` int(11) NOT NULL auto_increment,
2257 `name` varchar(50) default NULL,
2258 `closed` tinyint(1) default NULL,
2259 `booksellerid` int(11) NOT NULL,
2261 KEY `booksellerid` (`booksellerid`),
2262 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2266 -- Table structure for table `aqbasket`
2269 DROP TABLE IF EXISTS `aqbasket`;
2270 CREATE TABLE `aqbasket` (
2271 `basketno` int(11) NOT NULL auto_increment,
2272 `basketname` varchar(50) default NULL,
2274 `booksellernote` mediumtext,
2275 `contractnumber` int(11),
2276 `creationdate` date default NULL,
2277 `closedate` date default NULL,
2278 `booksellerid` int(11) NOT NULL default 1,
2279 `authorisedby` varchar(10) default NULL,
2280 `booksellerinvoicenumber` mediumtext,
2281 `basketgroupid` int(11),
2282 PRIMARY KEY (`basketno`),
2283 KEY `booksellerid` (`booksellerid`),
2284 KEY `basketgroupid` (`basketgroupid`),
2285 KEY `contractnumber` (`contractnumber`),
2286 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2287 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2288 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2289 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2292 -- Table structure for table `aqbooksellers`
2295 DROP TABLE IF EXISTS `aqbooksellers`;
2296 CREATE TABLE `aqbooksellers` (
2297 `id` int(11) NOT NULL auto_increment,
2298 `name` mediumtext NOT NULL,
2299 `address1` mediumtext,
2300 `address2` mediumtext,
2301 `address3` mediumtext,
2302 `address4` mediumtext,
2303 `phone` varchar(30) default NULL,
2304 `accountnumber` mediumtext,
2305 `othersupplier` mediumtext,
2306 `currency` varchar(3) NOT NULL default '',
2307 `booksellerfax` mediumtext,
2309 `bookselleremail` mediumtext,
2310 `booksellerurl` mediumtext,
2311 `contact` varchar(100) default NULL,
2312 `postal` mediumtext,
2313 `url` varchar(255) default NULL,
2314 `contpos` varchar(100) default NULL,
2315 `contphone` varchar(100) default NULL,
2316 `contfax` varchar(100) default NULL,
2317 `contaltphone` varchar(100) default NULL,
2318 `contemail` varchar(100) default NULL,
2319 `contnotes` mediumtext,
2320 `active` tinyint(4) default NULL,
2321 `listprice` varchar(10) default NULL,
2322 `invoiceprice` varchar(10) default NULL,
2323 `gstreg` tinyint(4) default NULL,
2324 `listincgst` tinyint(4) default NULL,
2325 `invoiceincgst` tinyint(4) default NULL,
2326 `gstrate` decimal(6,4) default NULL,
2327 `discount` float(6,4) default NULL,
2328 `fax` varchar(50) default NULL,
2330 KEY `listprice` (`listprice`),
2331 KEY `invoiceprice` (`invoiceprice`),
2332 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2333 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2334 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2337 -- Table structure for table `aqbudgets`
2340 DROP TABLE IF EXISTS `aqbudgets`;
2341 CREATE TABLE `aqbudgets` (
2342 `budget_id` int(11) NOT NULL auto_increment,
2343 `budget_parent_id` int(11) default NULL,
2344 `budget_code` varchar(30) default NULL,
2345 `budget_name` varchar(80) default NULL,
2346 `budget_branchcode` varchar(10) default NULL,
2347 `budget_amount` decimal(28,6) NULL default '0.00',
2348 `budget_encumb` decimal(28,6) NULL default '0.00',
2349 `budget_expend` decimal(28,6) NULL default '0.00',
2350 `budget_notes` mediumtext,
2351 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2352 `budget_period_id` int(11) default NULL,
2353 `sort1_authcat` varchar(80) default NULL,
2354 `sort2_authcat` varchar(80) default NULL,
2355 `budget_owner_id` int(11) default NULL,
2356 `budget_permission` int(1) default '0',
2357 PRIMARY KEY (`budget_id`)
2358 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2362 -- Table structure for table `aqbudgetperiods`
2366 DROP TABLE IF EXISTS `aqbudgetperiods`;
2367 CREATE TABLE `aqbudgetperiods` (
2368 `budget_period_id` int(11) NOT NULL auto_increment,
2369 `budget_period_startdate` date NOT NULL,
2370 `budget_period_enddate` date NOT NULL,
2371 `budget_period_active` tinyint(1) default '0',
2372 `budget_period_description` mediumtext,
2373 `budget_period_total` decimal(28,6),
2374 `budget_period_locked` tinyint(1) default NULL,
2375 `sort1_authcat` varchar(10) default NULL,
2376 `sort2_authcat` varchar(10) default NULL,
2377 PRIMARY KEY (`budget_period_id`)
2378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2381 -- Table structure for table `aqbudgets_planning`
2384 DROP TABLE IF EXISTS `aqbudgets_planning`;
2385 CREATE TABLE `aqbudgets_planning` (
2386 `plan_id` int(11) NOT NULL auto_increment,
2387 `budget_id` int(11) NOT NULL,
2388 `budget_period_id` int(11) NOT NULL,
2389 `estimated_amount` decimal(28,6) default NULL,
2390 `authcat` varchar(30) NOT NULL,
2391 `authvalue` varchar(30) NOT NULL,
2392 PRIMARY KEY (`plan_id`),
2393 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2394 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2397 -- Table structure for table 'aqcontract'
2400 DROP TABLE IF EXISTS `aqcontract`;
2401 CREATE TABLE `aqcontract` (
2402 `contractnumber` int(11) NOT NULL auto_increment,
2403 `contractstartdate` date default NULL,
2404 `contractenddate` date default NULL,
2405 `contractname` varchar(50) default NULL,
2406 `contractdescription` mediumtext,
2407 `booksellerid` int(11) not NULL,
2408 PRIMARY KEY (`contractnumber`),
2409 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2410 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2411 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2414 -- Table structure for table `aqorderdelivery`
2417 DROP TABLE IF EXISTS `aqorderdelivery`;
2418 CREATE TABLE `aqorderdelivery` (
2419 `ordernumber` date default NULL,
2420 `deliverynumber` smallint(6) NOT NULL default 0,
2421 `deliverydate` varchar(18) default NULL,
2422 `qtydelivered` smallint(6) default NULL,
2423 `deliverycomments` mediumtext
2424 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2427 -- Table structure for table `aqorders`
2430 DROP TABLE IF EXISTS `aqorders`;
2431 CREATE TABLE `aqorders` (
2432 `ordernumber` int(11) NOT NULL auto_increment,
2433 `biblionumber` int(11) default NULL,
2434 `entrydate` date default NULL,
2435 `quantity` smallint(6) default NULL,
2436 `currency` varchar(3) default NULL,
2437 `listprice` decimal(28,6) default NULL,
2438 `totalamount` decimal(28,6) default NULL,
2439 `datereceived` date default NULL,
2440 `booksellerinvoicenumber` mediumtext,
2441 `freight` decimal(28,6) default NULL,
2442 `unitprice` decimal(28,6) default NULL,
2443 `quantityreceived` smallint(6) default NULL,
2444 `cancelledby` varchar(10) default NULL,
2445 `datecancellationprinted` date default NULL,
2447 `supplierreference` mediumtext,
2448 `purchaseordernumber` mediumtext,
2449 `subscription` tinyint(1) default NULL,
2450 `serialid` varchar(30) default NULL,
2451 `basketno` int(11) default NULL,
2452 `biblioitemnumber` int(11) default NULL,
2453 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2454 `rrp` decimal(13,2) default NULL,
2455 `ecost` decimal(13,2) default NULL,
2456 `gst` decimal(13,2) default NULL,
2457 `budget_id` int(11) NOT NULL,
2458 `budgetgroup_id` int(11) NOT NULL,
2459 `budgetdate` date default NULL,
2460 `sort1` varchar(80) default NULL,
2461 `sort2` varchar(80) default NULL,
2462 `sort1_authcat` varchar(10) default NULL,
2463 `sort2_authcat` varchar(10) default NULL,
2464 `uncertainprice` tinyint(1),
2465 PRIMARY KEY (`ordernumber`),
2466 KEY `basketno` (`basketno`),
2467 KEY `biblionumber` (`biblionumber`),
2468 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2469 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2470 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2473 -- Table structure for table `aqorders_items`
2476 DROP TABLE IF EXISTS `aqorders_items`;
2477 CREATE TABLE `aqorders_items` (
2478 `ordernumber` int(11) NOT NULL,
2479 `itemnumber` int(11) NOT NULL,
2480 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2481 PRIMARY KEY (`itemnumber`),
2482 KEY `ordernumber` (`ordernumber`)
2483 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2486 -- Table structure for table `fieldmapping`
2489 DROP TABLE IF EXISTS `fieldmapping`;
2490 CREATE TABLE `fieldmapping` (
2491 `id` int(11) NOT NULL auto_increment,
2492 `field` varchar(255) NOT NULL,
2493 `frameworkcode` char(4) NOT NULL default '',
2494 `fieldcode` char(3) NOT NULL,
2495 `subfieldcode` char(1) NOT NULL,
2497 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2500 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2501 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2502 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2503 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2504 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2505 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2506 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2507 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;