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,
725 `stocknumber` varchar(32) default NULL,
727 PRIMARY KEY (`itemnumber`),
728 KEY `delitembarcodeidx` (`barcode`),
729 KEY `delitemstocknumberidx` (`stocknumber`),
730 KEY `delitembinoidx` (`biblioitemnumber`),
731 KEY `delitembibnoidx` (`biblionumber`),
732 KEY `delhomebranch` (`homebranch`),
733 KEY `delholdingbranch` (`holdingbranch`)
734 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
737 -- Table structure for table `ethnicity`
740 DROP TABLE IF EXISTS `ethnicity`;
741 CREATE TABLE `ethnicity` (
742 `code` varchar(10) NOT NULL default '',
743 `name` varchar(255) default NULL,
745 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
748 -- Table structure for table `export_format`
751 DROP TABLE IF EXISTS `export_format`;
752 CREATE TABLE `export_format` (
753 `export_format_id` int(11) NOT NULL auto_increment,
754 `profile` varchar(255) NOT NULL,
755 `description` mediumtext NOT NULL,
756 `marcfields` mediumtext NOT NULL,
757 PRIMARY KEY (`export_format_id`)
758 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
762 -- Table structure for table `hold_fill_targets`
765 DROP TABLE IF EXISTS `hold_fill_targets`;
766 CREATE TABLE hold_fill_targets (
767 `borrowernumber` int(11) NOT NULL,
768 `biblionumber` int(11) NOT NULL,
769 `itemnumber` int(11) NOT NULL,
770 `source_branchcode` varchar(10) default NULL,
771 `item_level_request` tinyint(4) NOT NULL default 0,
772 PRIMARY KEY `itemnumber` (`itemnumber`),
773 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
774 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
775 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
776 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
777 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
778 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
779 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
780 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
781 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
782 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
785 -- Table structure for table `import_batches`
788 DROP TABLE IF EXISTS `import_batches`;
789 CREATE TABLE `import_batches` (
790 `import_batch_id` int(11) NOT NULL auto_increment,
791 `matcher_id` int(11) default NULL,
792 `template_id` int(11) default NULL,
793 `branchcode` varchar(10) default NULL,
794 `num_biblios` int(11) NOT NULL default 0,
795 `num_items` int(11) NOT NULL default 0,
796 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
797 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
798 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
799 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
800 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
801 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
802 `file_name` varchar(100),
803 `comments` mediumtext,
804 PRIMARY KEY (`import_batch_id`),
805 KEY `branchcode` (`branchcode`)
806 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
809 -- Table structure for table `import_records`
812 DROP TABLE IF EXISTS `import_records`;
813 CREATE TABLE `import_records` (
814 `import_record_id` int(11) NOT NULL auto_increment,
815 `import_batch_id` int(11) NOT NULL,
816 `branchcode` varchar(10) default NULL,
817 `record_sequence` int(11) NOT NULL default 0,
818 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
819 `import_date` DATE default NULL,
820 `marc` longblob NOT NULL,
821 `marcxml` longtext NOT NULL,
822 `marcxml_old` longtext NOT NULL,
823 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
824 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
825 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
826 `import_error` mediumtext,
827 `encoding` varchar(40) NOT NULL default '',
828 `z3950random` varchar(40) default NULL,
829 PRIMARY KEY (`import_record_id`),
830 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
831 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
832 KEY `branchcode` (`branchcode`),
833 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
834 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
837 -- Table structure for `import_record_matches`
839 DROP TABLE IF EXISTS `import_record_matches`;
840 CREATE TABLE `import_record_matches` (
841 `import_record_id` int(11) NOT NULL,
842 `candidate_match_id` int(11) NOT NULL,
843 `score` int(11) NOT NULL default 0,
844 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
845 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
846 KEY `record_score` (`import_record_id`, `score`)
847 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
850 -- Table structure for table `import_biblios`
853 DROP TABLE IF EXISTS `import_biblios`;
854 CREATE TABLE `import_biblios` (
855 `import_record_id` int(11) NOT NULL,
856 `matched_biblionumber` int(11) default NULL,
857 `control_number` varchar(25) default NULL,
858 `original_source` varchar(25) default NULL,
859 `title` varchar(128) default NULL,
860 `author` varchar(80) default NULL,
861 `isbn` varchar(30) default NULL,
862 `issn` varchar(9) default NULL,
863 `has_items` tinyint(1) NOT NULL default 0,
864 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
865 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
866 KEY `matched_biblionumber` (`matched_biblionumber`),
867 KEY `title` (`title`),
869 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
872 -- Table structure for table `import_items`
875 DROP TABLE IF EXISTS `import_items`;
876 CREATE TABLE `import_items` (
877 `import_items_id` int(11) NOT NULL auto_increment,
878 `import_record_id` int(11) NOT NULL,
879 `itemnumber` int(11) default NULL,
880 `branchcode` varchar(10) default NULL,
881 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
882 `marcxml` longtext NOT NULL,
883 `import_error` mediumtext,
884 PRIMARY KEY (`import_items_id`),
885 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
886 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
887 KEY `itemnumber` (`itemnumber`),
888 KEY `branchcode` (`branchcode`)
889 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
892 -- Table structure for table `issues`
895 DROP TABLE IF EXISTS `issues`;
896 CREATE TABLE `issues` (
897 `borrowernumber` int(11) default NULL,
898 `itemnumber` int(11) default NULL,
899 `date_due` date default NULL,
900 `branchcode` varchar(10) default NULL,
901 `issuingbranch` varchar(18) default NULL,
902 `returndate` date default NULL,
903 `lastreneweddate` date default NULL,
904 `return` varchar(4) default NULL,
905 `renewals` tinyint(4) default NULL,
906 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
907 `issuedate` date default NULL,
908 KEY `issuesborridx` (`borrowernumber`),
909 KEY `issuesitemidx` (`itemnumber`),
910 KEY `bordate` (`borrowernumber`,`timestamp`),
911 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
912 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
913 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
916 -- Table structure for table `issuingrules`
919 DROP TABLE IF EXISTS `issuingrules`;
920 CREATE TABLE `issuingrules` (
921 `categorycode` varchar(10) NOT NULL default '',
922 `itemtype` varchar(10) NOT NULL default '',
923 `restrictedtype` tinyint(1) default NULL,
924 `rentaldiscount` decimal(28,6) default NULL,
925 `reservecharge` decimal(28,6) default NULL,
926 `fine` decimal(28,6) default NULL,
927 `firstremind` int(11) default NULL,
928 `chargeperiod` int(11) default NULL,
929 `accountsent` int(11) default NULL,
930 `chargename` varchar(100) default NULL,
931 `maxissueqty` int(4) default NULL,
932 `issuelength` int(4) default NULL,
933 `branchcode` varchar(10) NOT NULL default '',
934 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
935 KEY `categorycode` (`categorycode`),
936 KEY `itemtype` (`itemtype`)
937 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
940 -- Table structure for table `items`
943 DROP TABLE IF EXISTS `items`;
944 CREATE TABLE `items` (
945 `itemnumber` int(11) NOT NULL auto_increment,
946 `biblionumber` int(11) NOT NULL default 0,
947 `biblioitemnumber` int(11) NOT NULL default 0,
948 `barcode` varchar(20) default NULL,
949 `dateaccessioned` date default NULL,
950 `booksellerid` mediumtext default NULL,
951 `homebranch` varchar(10) default NULL,
952 `price` decimal(8,2) default NULL,
953 `replacementprice` decimal(8,2) default NULL,
954 `replacementpricedate` date default NULL,
955 `datelastborrowed` date default NULL,
956 `datelastseen` date default NULL,
957 `stack` tinyint(1) default NULL,
958 `notforloan` tinyint(1) NOT NULL default 0,
959 `damaged` tinyint(1) NOT NULL default 0,
960 `itemlost` tinyint(1) NOT NULL default 0,
961 `wthdrawn` tinyint(1) NOT NULL default 0,
962 `itemcallnumber` varchar(255) default NULL,
963 `issues` smallint(6) default NULL,
964 `renewals` smallint(6) default NULL,
965 `reserves` smallint(6) default NULL,
966 `restricted` tinyint(1) default NULL,
967 `itemnotes` mediumtext,
968 `holdingbranch` varchar(10) default NULL,
969 `paidfor` mediumtext,
970 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
971 `location` varchar(80) default NULL,
972 `permanent_location` varchar(80) default NULL,
973 `onloan` date default NULL,
974 `cn_source` varchar(10) default NULL,
975 `cn_sort` varchar(30) default NULL,
976 `ccode` varchar(10) default NULL,
977 `materials` varchar(10) default NULL,
978 `uri` varchar(255) default NULL,
979 `itype` varchar(10) default NULL,
980 `more_subfields_xml` longtext default NULL,
981 `enumchron` varchar(80) default NULL,
982 `copynumber` varchar(32) default NULL,
983 `stocknumber` varchar(32) default NULL,
984 PRIMARY KEY (`itemnumber`),
985 UNIQUE KEY `itembarcodeidx` (`barcode`),
986 UNIQUE KEY `itemstocknumberidx` (`stocknumber`),
987 KEY `itembinoidx` (`biblioitemnumber`),
988 KEY `itembibnoidx` (`biblionumber`),
989 KEY `homebranch` (`homebranch`),
990 KEY `holdingbranch` (`holdingbranch`),
991 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
992 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
993 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
994 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
997 -- Table structure for table `itemtypes`
1000 DROP TABLE IF EXISTS `itemtypes`;
1001 CREATE TABLE `itemtypes` (
1002 `itemtype` varchar(10) NOT NULL default '',
1003 `description` mediumtext,
1004 `renewalsallowed` smallint(6) default NULL,
1005 `rentalcharge` double(16,4) default NULL,
1006 `notforloan` smallint(6) default NULL,
1007 `imageurl` varchar(200) default NULL,
1009 PRIMARY KEY (`itemtype`),
1010 UNIQUE KEY `itemtype` (`itemtype`)
1011 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1014 -- Table structure for table `labels_batches`
1017 DROP TABLE IF EXISTS `labels_batches`;
1018 CREATE TABLE `labels_batches` (
1019 `label_id` int(11) NOT NULL auto_increment,
1020 `batch_id` int(10) NOT NULL default '1',
1021 `item_number` int(11) NOT NULL default '0',
1022 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1023 `branch_code` varchar(10) NOT NULL default 'NB',
1024 PRIMARY KEY USING BTREE (`label_id`),
1025 KEY `branch_fk` (`branch_code`),
1026 KEY `item_fk` (`item_number`),
1027 CONSTRAINT `item_fk_constraint` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE,
1028 CONSTRAINT `branch_fk_constraint` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE
1029 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1032 -- Table structure for table `labels_layouts`
1035 DROP TABLE IF EXISTS `labels_layouts`;
1036 CREATE TABLE `labels_layouts` (
1037 `layout_id` int(4) NOT NULL auto_increment,
1038 `barcode_type` char(100) NOT NULL default 'CODE39',
1039 `printing_type` char(32) NOT NULL default 'BAR',
1040 `layout_name` char(20) NOT NULL default 'DEFAULT',
1041 `guidebox` int(1) default '0',
1042 `font` char(10) character set utf8 collate utf8_unicode_ci NOT NULL default 'TR',
1043 `font_size` int(4) NOT NULL default '10',
1044 `callnum_split` int(1) default '0',
1045 `text_justify` char(1) character set utf8 collate utf8_unicode_ci NOT NULL default 'L',
1046 `format_string` varchar(210) NOT NULL default 'barcode',
1047 PRIMARY KEY USING BTREE (`layout_id`)
1048 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1051 -- Table structure for table `labels_templates`
1054 DROP TABLE IF EXISTS `labels_templates`;
1055 CREATE TABLE `labels_templates` (
1056 `template_id` int(4) NOT NULL auto_increment,
1057 `profile_id` int(4) default NULL,
1058 `template_code` char(100) NOT NULL default 'DEFAULT TEMPLATE',
1059 `template_desc` char(100) NOT NULL default 'Default description',
1060 `page_width` float NOT NULL default '0',
1061 `page_height` float NOT NULL default '0',
1062 `label_width` float NOT NULL default '0',
1063 `label_height` float NOT NULL default '0',
1064 `top_text_margin` float NOT NULL default '0',
1065 `left_text_margin` float NOT NULL default '0',
1066 `top_margin` float NOT NULL default '0',
1067 `left_margin` float NOT NULL default '0',
1068 `cols` int(2) NOT NULL default '0',
1069 `rows` int(2) NOT NULL default '0',
1070 `col_gap` float NOT NULL default '0',
1071 `row_gap` float NOT NULL default '0',
1072 `units` char(20) NOT NULL default 'POINT',
1073 PRIMARY KEY (`template_id`),
1074 KEY `template_profile_fk_constraint` (`profile_id`)
1075 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1078 -- Table structure for table `letter`
1081 DROP TABLE IF EXISTS `letter`;
1082 CREATE TABLE `letter` (
1083 `module` varchar(20) NOT NULL default '',
1084 `code` varchar(20) NOT NULL default '',
1085 `name` varchar(100) NOT NULL default '',
1086 `title` varchar(200) NOT NULL default '',
1088 PRIMARY KEY (`module`,`code`)
1089 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1092 -- Table structure for table `marc_subfield_structure`
1095 DROP TABLE IF EXISTS `marc_subfield_structure`;
1096 CREATE TABLE `marc_subfield_structure` (
1097 `tagfield` varchar(3) NOT NULL default '',
1098 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1099 `liblibrarian` varchar(255) NOT NULL default '',
1100 `libopac` varchar(255) NOT NULL default '',
1101 `repeatable` tinyint(4) NOT NULL default 0,
1102 `mandatory` tinyint(4) NOT NULL default 0,
1103 `kohafield` varchar(40) default NULL,
1104 `tab` tinyint(1) default NULL,
1105 `authorised_value` varchar(20) default NULL,
1106 `authtypecode` varchar(20) default NULL,
1107 `value_builder` varchar(80) default NULL,
1108 `isurl` tinyint(1) default NULL,
1109 `hidden` tinyint(1) default NULL,
1110 `frameworkcode` varchar(4) NOT NULL default '',
1111 `seealso` varchar(1100) default NULL,
1112 `link` varchar(80) default NULL,
1113 `defaultvalue` text default NULL,
1114 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1115 KEY `kohafield_2` (`kohafield`),
1116 KEY `tab` (`frameworkcode`,`tab`),
1117 KEY `kohafield` (`frameworkcode`,`kohafield`)
1118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1121 -- Table structure for table `marc_tag_structure`
1124 DROP TABLE IF EXISTS `marc_tag_structure`;
1125 CREATE TABLE `marc_tag_structure` (
1126 `tagfield` varchar(3) NOT NULL default '',
1127 `liblibrarian` varchar(255) NOT NULL default '',
1128 `libopac` varchar(255) NOT NULL default '',
1129 `repeatable` tinyint(4) NOT NULL default 0,
1130 `mandatory` tinyint(4) NOT NULL default 0,
1131 `authorised_value` varchar(10) default NULL,
1132 `frameworkcode` varchar(4) NOT NULL default '',
1133 PRIMARY KEY (`frameworkcode`,`tagfield`)
1134 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1137 -- Table structure for table `marc_matchers`
1140 DROP TABLE IF EXISTS `marc_matchers`;
1141 CREATE TABLE `marc_matchers` (
1142 `matcher_id` int(11) NOT NULL auto_increment,
1143 `code` varchar(10) NOT NULL default '',
1144 `description` varchar(255) NOT NULL default '',
1145 `record_type` varchar(10) NOT NULL default 'biblio',
1146 `threshold` int(11) NOT NULL default 0,
1147 PRIMARY KEY (`matcher_id`),
1148 KEY `code` (`code`),
1149 KEY `record_type` (`record_type`)
1150 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1153 -- Table structure for table `matchpoints`
1155 DROP TABLE IF EXISTS `matchpoints`;
1156 CREATE TABLE `matchpoints` (
1157 `matcher_id` int(11) NOT NULL,
1158 `matchpoint_id` int(11) NOT NULL auto_increment,
1159 `search_index` varchar(30) NOT NULL default '',
1160 `score` int(11) NOT NULL default 0,
1161 PRIMARY KEY (`matchpoint_id`),
1162 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1163 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1164 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1168 -- Table structure for table `matchpoint_components`
1170 DROP TABLE IF EXISTS `matchpoint_components`;
1171 CREATE TABLE `matchpoint_components` (
1172 `matchpoint_id` int(11) NOT NULL,
1173 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1174 sequence int(11) NOT NULL default 0,
1175 tag varchar(3) NOT NULL default '',
1176 subfields varchar(40) NOT NULL default '',
1177 offset int(4) NOT NULL default 0,
1178 length int(4) NOT NULL default 0,
1179 PRIMARY KEY (`matchpoint_component_id`),
1180 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1181 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1182 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1186 -- Table structure for table `matcher_component_norms`
1188 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1189 CREATE TABLE `matchpoint_component_norms` (
1190 `matchpoint_component_id` int(11) NOT NULL,
1191 `sequence` int(11) NOT NULL default 0,
1192 `norm_routine` varchar(50) NOT NULL default '',
1193 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1194 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1195 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1196 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1199 -- Table structure for table `matcher_matchpoints`
1201 DROP TABLE IF EXISTS `matcher_matchpoints`;
1202 CREATE TABLE `matcher_matchpoints` (
1203 `matcher_id` int(11) NOT NULL,
1204 `matchpoint_id` int(11) NOT NULL,
1205 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1206 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1207 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1208 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1212 -- Table structure for table `matchchecks`
1214 DROP TABLE IF EXISTS `matchchecks`;
1215 CREATE TABLE `matchchecks` (
1216 `matcher_id` int(11) NOT NULL,
1217 `matchcheck_id` int(11) NOT NULL auto_increment,
1218 `source_matchpoint_id` int(11) NOT NULL,
1219 `target_matchpoint_id` int(11) NOT NULL,
1220 PRIMARY KEY (`matchcheck_id`),
1221 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1222 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1223 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1224 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1225 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1226 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1227 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1230 -- Table structure for table `notifys`
1233 DROP TABLE IF EXISTS `notifys`;
1234 CREATE TABLE `notifys` (
1235 `notify_id` int(11) NOT NULL default 0,
1236 `borrowernumber` int(11) NOT NULL default 0,
1237 `itemnumber` int(11) NOT NULL default 0,
1238 `notify_date` date default NULL,
1239 `notify_send_date` date default NULL,
1240 `notify_level` int(1) NOT NULL default 0,
1241 `method` varchar(20) NOT NULL default ''
1242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1245 -- Table structure for table `nozebra`
1248 DROP TABLE IF EXISTS `nozebra`;
1249 CREATE TABLE `nozebra` (
1250 `server` varchar(20) NOT NULL,
1251 `indexname` varchar(40) NOT NULL,
1252 `value` varchar(250) NOT NULL,
1253 `biblionumbers` longtext NOT NULL,
1254 KEY `indexname` (`server`,`indexname`),
1255 KEY `value` (`server`,`value`))
1256 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1259 -- Table structure for table `old_issues`
1262 DROP TABLE IF EXISTS `old_issues`;
1263 CREATE TABLE `old_issues` (
1264 `borrowernumber` int(11) default NULL,
1265 `itemnumber` int(11) default NULL,
1266 `date_due` date default NULL,
1267 `branchcode` varchar(10) default NULL,
1268 `issuingbranch` varchar(18) default NULL,
1269 `returndate` date default NULL,
1270 `lastreneweddate` date default NULL,
1271 `return` varchar(4) default NULL,
1272 `renewals` tinyint(4) default NULL,
1273 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1274 `issuedate` date default NULL,
1275 KEY `old_issuesborridx` (`borrowernumber`),
1276 KEY `old_issuesitemidx` (`itemnumber`),
1277 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1278 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1279 ON DELETE SET NULL ON UPDATE SET NULL,
1280 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1281 ON DELETE SET NULL ON UPDATE SET NULL
1282 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1285 -- Table structure for table `old_reserves`
1287 DROP TABLE IF EXISTS `old_reserves`;
1288 CREATE TABLE `old_reserves` (
1289 `borrowernumber` int(11) default NULL,
1290 `reservedate` date default NULL,
1291 `biblionumber` int(11) default NULL,
1292 `constrainttype` varchar(1) default NULL,
1293 `branchcode` varchar(10) default NULL,
1294 `notificationdate` date default NULL,
1295 `reminderdate` date default NULL,
1296 `cancellationdate` date default NULL,
1297 `reservenotes` mediumtext,
1298 `priority` smallint(6) default NULL,
1299 `found` varchar(1) default NULL,
1300 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1301 `itemnumber` int(11) default NULL,
1302 `waitingdate` date default NULL,
1303 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1304 KEY `old_reserves_biblionumber` (`biblionumber`),
1305 KEY `old_reserves_itemnumber` (`itemnumber`),
1306 KEY `old_reserves_branchcode` (`branchcode`),
1307 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1308 ON DELETE SET NULL ON UPDATE SET NULL,
1309 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1310 ON DELETE SET NULL ON UPDATE SET NULL,
1311 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1312 ON DELETE SET NULL ON UPDATE SET NULL
1313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1316 -- Table structure for table `opac_news`
1319 DROP TABLE IF EXISTS `opac_news`;
1320 CREATE TABLE `opac_news` (
1321 `idnew` int(10) unsigned NOT NULL auto_increment,
1322 `title` varchar(250) NOT NULL default '',
1323 `new` text NOT NULL,
1324 `lang` varchar(25) NOT NULL default '',
1325 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1326 `expirationdate` date default NULL,
1327 `number` int(11) default NULL,
1328 PRIMARY KEY (`idnew`)
1329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1332 -- Table structure for table `overduerules`
1335 DROP TABLE IF EXISTS `overduerules`;
1336 CREATE TABLE `overduerules` (
1337 `branchcode` varchar(10) NOT NULL default '',
1338 `categorycode` varchar(10) NOT NULL default '',
1339 `delay1` int(4) default 0,
1340 `letter1` varchar(20) default NULL,
1341 `debarred1` varchar(1) default 0,
1342 `delay2` int(4) default 0,
1343 `debarred2` varchar(1) default 0,
1344 `letter2` varchar(20) default NULL,
1345 `delay3` int(4) default 0,
1346 `letter3` varchar(20) default NULL,
1347 `debarred3` int(1) default 0,
1348 PRIMARY KEY (`branchcode`,`categorycode`)
1349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1352 -- Table structure for table `patroncards`
1355 DROP TABLE IF EXISTS `patroncards`;
1356 CREATE TABLE `patroncards` (
1357 `cardid` int(11) NOT NULL auto_increment,
1358 `batch_id` varchar(10) NOT NULL default '1',
1359 `borrowernumber` int(11) NOT NULL,
1360 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1361 PRIMARY KEY (`cardid`),
1362 KEY `patroncards_ibfk_1` (`borrowernumber`),
1363 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1364 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1367 -- Table structure for table `patronimage`
1370 DROP TABLE IF EXISTS `patronimage`;
1371 CREATE TABLE `patronimage` (
1372 `cardnumber` varchar(16) NOT NULL,
1373 `mimetype` varchar(15) NOT NULL,
1374 `imagefile` mediumblob NOT NULL,
1375 PRIMARY KEY (`cardnumber`),
1376 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1377 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1380 -- Table structure for table `printers`
1383 DROP TABLE IF EXISTS `printers`;
1384 CREATE TABLE `printers` (
1385 `printername` varchar(40) NOT NULL default '',
1386 `printqueue` varchar(20) default NULL,
1387 `printtype` varchar(20) default NULL,
1388 PRIMARY KEY (`printername`)
1389 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1392 -- Table structure for table `printers_profile`
1395 DROP TABLE IF EXISTS `printers_profile`;
1396 CREATE TABLE `printers_profile` (
1397 `profile_id` int(4) NOT NULL auto_increment,
1398 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1399 `template_id` int(4) NOT NULL default '0',
1400 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1401 `offset_horz` float NOT NULL default '0',
1402 `offset_vert` float NOT NULL default '0',
1403 `creep_horz` float NOT NULL default '0',
1404 `creep_vert` float NOT NULL default '0',
1405 `units` char(20) NOT NULL default 'POINT',
1406 PRIMARY KEY (`profile_id`),
1407 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`)
1408 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1411 -- Table structure for table `repeatable_holidays`
1414 DROP TABLE IF EXISTS `repeatable_holidays`;
1415 CREATE TABLE `repeatable_holidays` (
1416 `id` int(11) NOT NULL auto_increment,
1417 `branchcode` varchar(10) NOT NULL default '',
1418 `weekday` smallint(6) default NULL,
1419 `day` smallint(6) default NULL,
1420 `month` smallint(6) default NULL,
1421 `title` varchar(50) NOT NULL default '',
1422 `description` text NOT NULL,
1424 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1427 -- Table structure for table `reports_dictionary`
1430 DROP TABLE IF EXISTS `reports_dictionary`;
1431 CREATE TABLE reports_dictionary (
1432 `id` int(11) NOT NULL auto_increment,
1433 `name` varchar(255) default NULL,
1435 `date_created` datetime default NULL,
1436 `date_modified` datetime default NULL,
1438 `area` int(11) default NULL,
1440 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1443 -- Table structure for table `reserveconstraints`
1446 DROP TABLE IF EXISTS `reserveconstraints`;
1447 CREATE TABLE `reserveconstraints` (
1448 `borrowernumber` int(11) NOT NULL default 0,
1449 `reservedate` date default NULL,
1450 `biblionumber` int(11) NOT NULL default 0,
1451 `biblioitemnumber` int(11) default NULL,
1452 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1453 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1456 -- Table structure for table `reserves`
1459 DROP TABLE IF EXISTS `reserves`;
1460 CREATE TABLE `reserves` (
1461 `borrowernumber` int(11) NOT NULL default 0,
1462 `reservedate` date default NULL,
1463 `biblionumber` int(11) NOT NULL default 0,
1464 `constrainttype` varchar(1) default NULL,
1465 `branchcode` varchar(10) default NULL,
1466 `notificationdate` date default NULL,
1467 `reminderdate` date default NULL,
1468 `cancellationdate` date default NULL,
1469 `reservenotes` mediumtext,
1470 `priority` smallint(6) default NULL,
1471 `found` varchar(1) default NULL,
1472 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1473 `itemnumber` int(11) default NULL,
1474 `waitingdate` date default NULL,
1475 KEY `borrowernumber` (`borrowernumber`),
1476 KEY `biblionumber` (`biblionumber`),
1477 KEY `itemnumber` (`itemnumber`),
1478 KEY `branchcode` (`branchcode`),
1479 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1480 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1481 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1482 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1483 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1486 -- Table structure for table `reviews`
1489 DROP TABLE IF EXISTS `reviews`;
1490 CREATE TABLE `reviews` (
1491 `reviewid` int(11) NOT NULL auto_increment,
1492 `borrowernumber` int(11) default NULL,
1493 `biblionumber` int(11) default NULL,
1495 `approved` tinyint(4) default NULL,
1496 `datereviewed` datetime default NULL,
1497 PRIMARY KEY (`reviewid`)
1498 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1501 -- Table structure for table `roadtype`
1504 DROP TABLE IF EXISTS `roadtype`;
1505 CREATE TABLE `roadtype` (
1506 `roadtypeid` int(11) NOT NULL auto_increment,
1507 `road_type` varchar(100) NOT NULL default '',
1508 PRIMARY KEY (`roadtypeid`)
1509 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1512 -- Table structure for table `saved_sql`
1515 DROP TABLE IF EXISTS `saved_sql`;
1516 CREATE TABLE saved_sql (
1517 `id` int(11) NOT NULL auto_increment,
1518 `borrowernumber` int(11) default NULL,
1519 `date_created` datetime default NULL,
1520 `last_modified` datetime default NULL,
1522 `last_run` datetime default NULL,
1523 `report_name` varchar(255) default NULL,
1524 `type` varchar(255) default NULL,
1527 KEY boridx (`borrowernumber`)
1528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1532 -- Table structure for `saved_reports`
1535 DROP TABLE IF EXISTS `saved_reports`;
1536 CREATE TABLE saved_reports (
1537 `id` int(11) NOT NULL auto_increment,
1538 `report_id` int(11) default NULL,
1540 `date_run` datetime default NULL,
1542 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1546 -- Table structure for table `search_history`
1549 DROP TABLE IF EXISTS `search_history`;
1550 CREATE TABLE IF NOT EXISTS `search_history` (
1551 `userid` int(11) NOT NULL,
1552 `sessionid` varchar(32) NOT NULL,
1553 `query_desc` varchar(255) NOT NULL,
1554 `query_cgi` varchar(255) NOT NULL,
1555 `total` int(11) NOT NULL,
1556 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1557 KEY `userid` (`userid`),
1558 KEY `sessionid` (`sessionid`)
1559 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1563 -- Table structure for table `serial`
1566 DROP TABLE IF EXISTS `serial`;
1567 CREATE TABLE `serial` (
1568 `serialid` int(11) NOT NULL auto_increment,
1569 `biblionumber` varchar(100) NOT NULL default '',
1570 `subscriptionid` varchar(100) NOT NULL default '',
1571 `serialseq` varchar(100) NOT NULL default '',
1572 `status` tinyint(4) NOT NULL default 0,
1573 `planneddate` date default NULL,
1575 `publisheddate` date default NULL,
1576 `itemnumber` text default NULL,
1577 `claimdate` date default NULL,
1578 `routingnotes` text,
1579 PRIMARY KEY (`serialid`)
1580 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1583 -- Table structure for table `sessions`
1586 DROP TABLE IF EXISTS sessions;
1587 CREATE TABLE sessions (
1588 `id` varchar(32) NOT NULL,
1589 `a_session` text NOT NULL,
1591 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1594 -- Table structure for table `special_holidays`
1597 DROP TABLE IF EXISTS `special_holidays`;
1598 CREATE TABLE `special_holidays` (
1599 `id` int(11) NOT NULL auto_increment,
1600 `branchcode` varchar(10) NOT NULL default '',
1601 `day` smallint(6) NOT NULL default 0,
1602 `month` smallint(6) NOT NULL default 0,
1603 `year` smallint(6) NOT NULL default 0,
1604 `isexception` smallint(1) NOT NULL default 1,
1605 `title` varchar(50) NOT NULL default '',
1606 `description` text NOT NULL,
1608 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1611 -- Table structure for table `statistics`
1614 DROP TABLE IF EXISTS `statistics`;
1615 CREATE TABLE `statistics` (
1616 `datetime` datetime default NULL,
1617 `branch` varchar(10) default NULL,
1618 `proccode` varchar(4) default NULL,
1619 `value` double(16,4) default NULL,
1620 `type` varchar(16) default NULL,
1622 `usercode` varchar(10) default NULL,
1623 `itemnumber` int(11) default NULL,
1624 `itemtype` varchar(10) default NULL,
1625 `borrowernumber` int(11) default NULL,
1626 `associatedborrower` int(11) default NULL,
1627 KEY `timeidx` (`datetime`)
1628 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1631 -- Table structure for table `stopwords`
1634 DROP TABLE IF EXISTS `stopwords`;
1635 CREATE TABLE `stopwords` (
1636 `word` varchar(255) default NULL
1637 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1640 -- Table structure for table `subscription`
1643 DROP TABLE IF EXISTS `subscription`;
1644 CREATE TABLE `subscription` (
1645 `biblionumber` int(11) NOT NULL default 0,
1646 `subscriptionid` int(11) NOT NULL auto_increment,
1647 `librarian` varchar(100) default '',
1648 `startdate` date default NULL,
1649 `aqbooksellerid` int(11) default 0,
1650 `cost` int(11) default 0,
1651 `aqbudgetid` int(11) default 0,
1652 `weeklength` int(11) default 0,
1653 `monthlength` int(11) default 0,
1654 `numberlength` int(11) default 0,
1655 `periodicity` tinyint(4) default 0,
1656 `dow` varchar(100) default '',
1657 `numberingmethod` varchar(100) default '',
1659 `status` varchar(100) NOT NULL default '',
1660 `add1` int(11) default 0,
1661 `every1` int(11) default 0,
1662 `whenmorethan1` int(11) default 0,
1663 `setto1` int(11) default NULL,
1664 `lastvalue1` int(11) default NULL,
1665 `add2` int(11) default 0,
1666 `every2` int(11) default 0,
1667 `whenmorethan2` int(11) default 0,
1668 `setto2` int(11) default NULL,
1669 `lastvalue2` int(11) default NULL,
1670 `add3` int(11) default 0,
1671 `every3` int(11) default 0,
1672 `innerloop1` int(11) default 0,
1673 `innerloop2` int(11) default 0,
1674 `innerloop3` int(11) default 0,
1675 `whenmorethan3` int(11) default 0,
1676 `setto3` int(11) default NULL,
1677 `lastvalue3` int(11) default NULL,
1678 `issuesatonce` tinyint(3) NOT NULL default 1,
1679 `firstacquidate` date default NULL,
1680 `manualhistory` tinyint(1) NOT NULL default 0,
1681 `irregularity` text,
1682 `letter` varchar(20) default NULL,
1683 `numberpattern` tinyint(3) default 0,
1684 `distributedto` text,
1685 `internalnotes` longtext,
1687 `location` varchar(80) NULL default '',
1688 `branchcode` varchar(10) NOT NULL default '',
1689 `hemisphere` tinyint(3) default 0,
1690 `lastbranch` varchar(10),
1691 `serialsadditems` tinyint(1) NOT NULL default '0',
1692 `staffdisplaycount` VARCHAR(10) NULL,
1693 `opacdisplaycount` VARCHAR(10) NULL,
1694 `graceperiod` int(11) NOT NULL default '0',
1695 PRIMARY KEY (`subscriptionid`)
1696 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1699 -- Table structure for table `subscriptionhistory`
1702 DROP TABLE IF EXISTS `subscriptionhistory`;
1703 CREATE TABLE `subscriptionhistory` (
1704 `biblionumber` int(11) NOT NULL default 0,
1705 `subscriptionid` int(11) NOT NULL default 0,
1706 `histstartdate` date default NULL,
1707 `histenddate` date default NULL,
1708 `missinglist` longtext NOT NULL,
1709 `recievedlist` longtext NOT NULL,
1710 `opacnote` varchar(150) NOT NULL default '',
1711 `librariannote` varchar(150) NOT NULL default '',
1712 PRIMARY KEY (`subscriptionid`),
1713 KEY `biblionumber` (`biblionumber`)
1714 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1717 -- Table structure for table `subscriptionroutinglist`
1720 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1721 CREATE TABLE `subscriptionroutinglist` (
1722 `routingid` int(11) NOT NULL auto_increment,
1723 `borrowernumber` int(11) default NULL,
1724 `ranking` int(11) default NULL,
1725 `subscriptionid` int(11) default NULL,
1726 PRIMARY KEY (`routingid`)
1727 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1730 -- Table structure for table `suggestions`
1733 DROP TABLE IF EXISTS `suggestions`;
1734 CREATE TABLE `suggestions` (
1735 `suggestionid` int(8) NOT NULL auto_increment,
1736 `suggestedby` int(11) NOT NULL default 0,
1737 `suggesteddate` date NOT NULL default 0,
1738 `managedby` int(11) default NULL,
1739 `manageddate` date default NULL,
1740 `STATUS` varchar(10) NOT NULL default '',
1742 `author` varchar(80) default NULL,
1743 `title` varchar(80) default NULL,
1744 `copyrightdate` smallint(6) default NULL,
1745 `publishercode` varchar(255) default NULL,
1746 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1747 `volumedesc` varchar(255) default NULL,
1748 `publicationyear` smallint(6) default 0,
1749 `place` varchar(255) default NULL,
1750 `isbn` varchar(30) default NULL,
1751 `mailoverseeing` smallint(1) default 0,
1752 `biblionumber` int(11) default NULL,
1754 PRIMARY KEY (`suggestionid`),
1755 KEY `suggestedby` (`suggestedby`),
1756 KEY `managedby` (`managedby`)
1757 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1760 -- Table structure for table `systempreferences`
1763 DROP TABLE IF EXISTS `systempreferences`;
1764 CREATE TABLE `systempreferences` (
1765 `variable` varchar(50) NOT NULL default '',
1767 `options` mediumtext,
1769 `type` varchar(20) default NULL,
1770 PRIMARY KEY (`variable`)
1771 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1774 -- Table structure for table `tags`
1777 DROP TABLE IF EXISTS `tags`;
1778 CREATE TABLE `tags` (
1779 `entry` varchar(255) NOT NULL default '',
1780 `weight` bigint(20) NOT NULL default 0,
1781 PRIMARY KEY (`entry`)
1782 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1785 -- Table structure for table `tags_all`
1788 DROP TABLE IF EXISTS `tags_all`;
1789 CREATE TABLE `tags_all` (
1790 `tag_id` int(11) NOT NULL auto_increment,
1791 `borrowernumber` int(11) NOT NULL,
1792 `biblionumber` int(11) NOT NULL,
1793 `term` varchar(255) NOT NULL,
1794 `language` int(4) default NULL,
1795 `date_created` datetime NOT NULL,
1796 PRIMARY KEY (`tag_id`),
1797 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1798 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1799 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1800 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1801 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1802 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1803 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1806 -- Table structure for table `tags_approval`
1809 DROP TABLE IF EXISTS `tags_approval`;
1810 CREATE TABLE `tags_approval` (
1811 `term` varchar(255) NOT NULL,
1812 `approved` int(1) NOT NULL default '0',
1813 `date_approved` datetime default NULL,
1814 `approved_by` int(11) default NULL,
1815 `weight_total` int(9) NOT NULL default '1',
1816 PRIMARY KEY (`term`),
1817 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1818 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1819 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1820 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1823 -- Table structure for table `tags_index`
1826 DROP TABLE IF EXISTS `tags_index`;
1827 CREATE TABLE `tags_index` (
1828 `term` varchar(255) NOT NULL,
1829 `biblionumber` int(11) NOT NULL,
1830 `weight` int(9) NOT NULL default '1',
1831 PRIMARY KEY (`term`,`biblionumber`),
1832 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1833 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1834 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1835 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1836 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1837 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1840 -- Table structure for table `userflags`
1843 DROP TABLE IF EXISTS `userflags`;
1844 CREATE TABLE `userflags` (
1845 `bit` int(11) NOT NULL default 0,
1846 `flag` varchar(30) default NULL,
1847 `flagdesc` varchar(255) default NULL,
1848 `defaulton` int(11) default NULL,
1850 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1853 -- Table structure for table `virtualshelves`
1856 DROP TABLE IF EXISTS `virtualshelves`;
1857 CREATE TABLE `virtualshelves` (
1858 `shelfnumber` int(11) NOT NULL auto_increment,
1859 `shelfname` varchar(255) default NULL,
1860 `owner` varchar(80) default NULL,
1861 `category` varchar(1) default NULL,
1862 `sortfield` varchar(16) default NULL,
1863 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1864 PRIMARY KEY (`shelfnumber`)
1865 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1868 -- Table structure for table `virtualshelfcontents`
1871 DROP TABLE IF EXISTS `virtualshelfcontents`;
1872 CREATE TABLE `virtualshelfcontents` (
1873 `shelfnumber` int(11) NOT NULL default 0,
1874 `biblionumber` int(11) NOT NULL default 0,
1875 `flags` int(11) default NULL,
1876 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1877 KEY `shelfnumber` (`shelfnumber`),
1878 KEY `biblionumber` (`biblionumber`),
1879 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1880 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1881 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1884 -- Table structure for table `z3950servers`
1887 DROP TABLE IF EXISTS `z3950servers`;
1888 CREATE TABLE `z3950servers` (
1889 `host` varchar(255) default NULL,
1890 `port` int(11) default NULL,
1891 `db` varchar(255) default NULL,
1892 `userid` varchar(255) default NULL,
1893 `password` varchar(255) default NULL,
1895 `id` int(11) NOT NULL auto_increment,
1896 `checked` smallint(6) default NULL,
1897 `rank` int(11) default NULL,
1898 `syntax` varchar(80) default NULL,
1900 `position` enum('primary','secondary','') NOT NULL default 'primary',
1901 `type` enum('zed','opensearch') NOT NULL default 'zed',
1902 `encoding` text default NULL,
1903 `description` text NOT NULL,
1905 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1908 -- Table structure for table `zebraqueue`
1911 DROP TABLE IF EXISTS `zebraqueue`;
1912 CREATE TABLE `zebraqueue` (
1913 `id` int(11) NOT NULL auto_increment,
1914 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1915 `operation` char(20) NOT NULL default '',
1916 `server` char(20) NOT NULL default '',
1917 `done` int(11) NOT NULL default '0',
1918 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1920 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1921 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1923 DROP TABLE IF EXISTS `services_throttle`;
1924 CREATE TABLE `services_throttle` (
1925 `service_type` varchar(10) NOT NULL default '',
1926 `service_count` varchar(45) default NULL,
1927 PRIMARY KEY (`service_type`)
1928 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1930 -- http://www.w3.org/International/articles/language-tags/
1933 DROP TABLE IF EXISTS language_subtag_registry;
1934 CREATE TABLE language_subtag_registry (
1936 type varchar(25), -- language-script-region-variant-extension-privateuse
1937 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1939 id int(11) NOT NULL auto_increment,
1941 KEY `subtag` (`subtag`)
1942 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1944 -- TODO: add suppress_scripts
1945 -- this maps three letter codes defined in iso639.2 back to their
1946 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1947 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1948 CREATE TABLE language_rfc4646_to_iso639 (
1949 rfc4646_subtag varchar(25),
1950 iso639_2_code varchar(25),
1951 id int(11) NOT NULL auto_increment,
1953 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1954 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1956 DROP TABLE IF EXISTS language_descriptions;
1957 CREATE TABLE language_descriptions (
1961 description varchar(255),
1962 id int(11) NOT NULL auto_increment,
1965 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1967 -- bi-directional support, keyed by script subcode
1968 DROP TABLE IF EXISTS language_script_bidi;
1969 CREATE TABLE language_script_bidi (
1970 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1971 bidi varchar(3), -- rtl ltr
1972 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1973 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1975 -- TODO: need to map language subtags to script subtags for detection
1976 -- of bidi when script is not specified (like ar, he)
1977 DROP TABLE IF EXISTS language_script_mapping;
1978 CREATE TABLE language_script_mapping (
1979 language_subtag varchar(25),
1980 script_subtag varchar(25),
1981 KEY `language_subtag` (`language_subtag`)
1982 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1984 DROP TABLE IF EXISTS `permissions`;
1985 CREATE TABLE `permissions` (
1986 `module_bit` int(11) NOT NULL DEFAULT 0,
1987 `code` varchar(64) DEFAULT NULL,
1988 `description` varchar(255) DEFAULT NULL,
1989 PRIMARY KEY (`module_bit`, `code`),
1990 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1991 ON DELETE CASCADE ON UPDATE CASCADE
1992 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1994 DROP TABLE IF EXISTS `serialitems`;
1995 CREATE TABLE `serialitems` (
1996 `itemnumber` int(11) NOT NULL,
1997 `serialid` int(11) NOT NULL,
1998 UNIQUE KEY `serialitemsidx` (`itemnumber`),
1999 KEY `serialitems_sfk_1` (`serialid`),
2000 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2003 DROP TABLE IF EXISTS `user_permissions`;
2004 CREATE TABLE `user_permissions` (
2005 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2006 `module_bit` int(11) NOT NULL DEFAULT 0,
2007 `code` varchar(64) DEFAULT NULL,
2008 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2009 ON DELETE CASCADE ON UPDATE CASCADE,
2010 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2011 ON DELETE CASCADE ON UPDATE CASCADE
2012 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2015 -- Table structure for table `tmp_holdsqueue`
2018 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2019 CREATE TABLE `tmp_holdsqueue` (
2020 `biblionumber` int(11) default NULL,
2021 `itemnumber` int(11) default NULL,
2022 `barcode` varchar(20) default NULL,
2023 `surname` mediumtext NOT NULL,
2026 `borrowernumber` int(11) NOT NULL,
2027 `cardnumber` varchar(16) default NULL,
2028 `reservedate` date default NULL,
2030 `itemcallnumber` varchar(255) default NULL,
2031 `holdingbranch` varchar(10) default NULL,
2032 `pickbranch` varchar(10) default NULL,
2034 `item_level_request` tinyint(4) NOT NULL default 0
2035 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2038 -- Table structure for table `message_queue`
2041 DROP TABLE IF EXISTS `message_queue`;
2042 CREATE TABLE `message_queue` (
2043 `message_id` int(11) NOT NULL auto_increment,
2044 `borrowernumber` int(11) default NULL,
2047 `metadata` text DEFAULT NULL,
2048 `letter_code` varchar(64) DEFAULT NULL,
2049 `message_transport_type` varchar(20) NOT NULL,
2050 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2051 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2052 `to_address` mediumtext,
2053 `from_address` mediumtext,
2054 `content_type` text,
2055 KEY `message_id` (`message_id`),
2056 KEY `borrowernumber` (`borrowernumber`),
2057 KEY `message_transport_type` (`message_transport_type`),
2058 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2059 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2060 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2063 -- Table structure for table `message_transport_types`
2066 DROP TABLE IF EXISTS `message_transport_types`;
2067 CREATE TABLE `message_transport_types` (
2068 `message_transport_type` varchar(20) NOT NULL,
2069 PRIMARY KEY (`message_transport_type`)
2070 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2073 -- Table structure for table `message_attributes`
2076 DROP TABLE IF EXISTS `message_attributes`;
2077 CREATE TABLE `message_attributes` (
2078 `message_attribute_id` int(11) NOT NULL auto_increment,
2079 `message_name` varchar(20) NOT NULL default '',
2080 `takes_days` tinyint(1) NOT NULL default '0',
2081 PRIMARY KEY (`message_attribute_id`),
2082 UNIQUE KEY `message_name` (`message_name`)
2083 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2086 -- Table structure for table `message_transports`
2089 DROP TABLE IF EXISTS `message_transports`;
2090 CREATE TABLE `message_transports` (
2091 `message_attribute_id` int(11) NOT NULL,
2092 `message_transport_type` varchar(20) NOT NULL,
2093 `is_digest` tinyint(1) NOT NULL default '0',
2094 `letter_module` varchar(20) NOT NULL default '',
2095 `letter_code` varchar(20) NOT NULL default '',
2096 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2097 KEY `message_transport_type` (`message_transport_type`),
2098 KEY `letter_module` (`letter_module`,`letter_code`),
2099 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2100 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2101 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2105 -- Table structure for table `borrower_message_preferences`
2108 DROP TABLE IF EXISTS `borrower_message_preferences`;
2109 CREATE TABLE `borrower_message_preferences` (
2110 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2111 `borrowernumber` int(11) default NULL,
2112 `categorycode` varchar(10) default NULL,
2113 `message_attribute_id` int(11) default '0',
2114 `days_in_advance` int(11) default '0',
2115 `wants_digest` tinyint(1) NOT NULL default '0',
2116 PRIMARY KEY (`borrower_message_preference_id`),
2117 KEY `borrowernumber` (`borrowernumber`),
2118 KEY `categorycode` (`categorycode`),
2119 KEY `message_attribute_id` (`message_attribute_id`),
2120 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2121 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2122 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2126 -- Table structure for table `borrower_message_transport_preferences`
2129 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2130 CREATE TABLE `borrower_message_transport_preferences` (
2131 `borrower_message_preference_id` int(11) NOT NULL default '0',
2132 `message_transport_type` varchar(20) NOT NULL default '0',
2133 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2134 KEY `message_transport_type` (`message_transport_type`),
2135 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,
2136 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
2137 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2140 -- Table structure for the table branch_transfer_limits
2143 DROP TABLE IF EXISTS `branch_transfer_limits`;
2144 CREATE TABLE branch_transfer_limits (
2145 limitId int(8) NOT NULL auto_increment,
2146 toBranch varchar(10) NOT NULL,
2147 fromBranch varchar(10) NOT NULL,
2148 itemtype varchar(10) NULL,
2149 ccode varchar(10) NULL,
2150 PRIMARY KEY (limitId)
2151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2154 -- Table structure for table `item_circulation_alert_preferences`
2157 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2158 CREATE TABLE `item_circulation_alert_preferences` (
2159 `id` int(11) NOT NULL auto_increment,
2160 `branchcode` varchar(10) NOT NULL,
2161 `categorycode` varchar(10) NOT NULL,
2162 `item_type` varchar(10) NOT NULL,
2163 `notification` varchar(16) NOT NULL,
2165 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2169 -- Table structure for table `messages`
2172 CREATE TABLE `messages` (
2173 `message_id` int(11) NOT NULL auto_increment,
2174 `borrowernumber` int(11) NOT NULL,
2175 `branchcode` varchar(4) default NULL,
2176 `message_type` varchar(1) NOT NULL,
2177 `message` text NOT NULL,
2178 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2179 PRIMARY KEY (`message_id`)
2180 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2183 -- Table structure for table `accountlines`
2186 DROP TABLE IF EXISTS `accountlines`;
2187 CREATE TABLE `accountlines` (
2188 `borrowernumber` int(11) NOT NULL default 0,
2189 `accountno` smallint(6) NOT NULL default 0,
2190 `itemnumber` int(11) default NULL,
2191 `date` date default NULL,
2192 `amount` decimal(28,6) default NULL,
2193 `description` mediumtext,
2194 `dispute` mediumtext,
2195 `accounttype` varchar(5) default NULL,
2196 `amountoutstanding` decimal(28,6) default NULL,
2197 `lastincrement` decimal(28,6) default NULL,
2198 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2199 `notify_id` int(11) NOT NULL default 0,
2200 `notify_level` int(2) NOT NULL default 0,
2201 KEY `acctsborridx` (`borrowernumber`),
2202 KEY `timeidx` (`timestamp`),
2203 KEY `itemnumber` (`itemnumber`),
2204 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2205 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2209 -- Table structure for table `accountoffsets`
2212 DROP TABLE IF EXISTS `accountoffsets`;
2213 CREATE TABLE `accountoffsets` (
2214 `borrowernumber` int(11) NOT NULL default 0,
2215 `accountno` smallint(6) NOT NULL default 0,
2216 `offsetaccount` smallint(6) NOT NULL default 0,
2217 `offsetamount` decimal(28,6) default NULL,
2218 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2219 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2220 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2223 -- Table structure for table `action_logs`
2226 DROP TABLE IF EXISTS `action_logs`;
2227 CREATE TABLE `action_logs` (
2228 `action_id` int(11) NOT NULL auto_increment,
2229 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2230 `user` int(11) NOT NULL default 0,
2233 `object` int(11) default NULL,
2235 PRIMARY KEY (`action_id`),
2236 KEY (`timestamp`,`user`)
2237 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2240 -- Table structure for table `alert`
2243 DROP TABLE IF EXISTS `alert`;
2244 CREATE TABLE `alert` (
2245 `alertid` int(11) NOT NULL auto_increment,
2246 `borrowernumber` int(11) NOT NULL default 0,
2247 `type` varchar(10) NOT NULL default '',
2248 `externalid` varchar(20) NOT NULL default '',
2249 PRIMARY KEY (`alertid`),
2250 KEY `borrowernumber` (`borrowernumber`),
2251 KEY `type` (`type`,`externalid`)
2252 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2255 -- Table structure for table `aqbasketgroups`
2258 DROP TABLE IF EXISTS `aqbasketgroups`;
2259 CREATE TABLE `aqbasketgroups` (
2260 `id` int(11) NOT NULL auto_increment,
2261 `name` varchar(50) default NULL,
2262 `closed` tinyint(1) default NULL,
2263 `booksellerid` int(11) NOT NULL,
2265 KEY `booksellerid` (`booksellerid`),
2266 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2267 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2270 -- Table structure for table `aqbasket`
2273 DROP TABLE IF EXISTS `aqbasket`;
2274 CREATE TABLE `aqbasket` (
2275 `basketno` int(11) NOT NULL auto_increment,
2276 `basketname` varchar(50) default NULL,
2278 `booksellernote` mediumtext,
2279 `contractnumber` int(11),
2280 `creationdate` date default NULL,
2281 `closedate` date default NULL,
2282 `booksellerid` int(11) NOT NULL default 1,
2283 `authorisedby` varchar(10) default NULL,
2284 `booksellerinvoicenumber` mediumtext,
2285 `basketgroupid` int(11),
2286 PRIMARY KEY (`basketno`),
2287 KEY `booksellerid` (`booksellerid`),
2288 KEY `basketgroupid` (`basketgroupid`),
2289 KEY `contractnumber` (`contractnumber`),
2290 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2291 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2292 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2293 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2296 -- Table structure for table `aqbooksellers`
2299 DROP TABLE IF EXISTS `aqbooksellers`;
2300 CREATE TABLE `aqbooksellers` (
2301 `id` int(11) NOT NULL auto_increment,
2302 `name` mediumtext NOT NULL,
2303 `address1` mediumtext,
2304 `address2` mediumtext,
2305 `address3` mediumtext,
2306 `address4` mediumtext,
2307 `phone` varchar(30) default NULL,
2308 `accountnumber` mediumtext,
2309 `othersupplier` mediumtext,
2310 `currency` varchar(3) NOT NULL default '',
2311 `booksellerfax` mediumtext,
2313 `bookselleremail` mediumtext,
2314 `booksellerurl` mediumtext,
2315 `contact` varchar(100) default NULL,
2316 `postal` mediumtext,
2317 `url` varchar(255) default NULL,
2318 `contpos` varchar(100) default NULL,
2319 `contphone` varchar(100) default NULL,
2320 `contfax` varchar(100) default NULL,
2321 `contaltphone` varchar(100) default NULL,
2322 `contemail` varchar(100) default NULL,
2323 `contnotes` mediumtext,
2324 `active` tinyint(4) default NULL,
2325 `listprice` varchar(10) default NULL,
2326 `invoiceprice` varchar(10) default NULL,
2327 `gstreg` tinyint(4) default NULL,
2328 `listincgst` tinyint(4) default NULL,
2329 `invoiceincgst` tinyint(4) default NULL,
2330 `gstrate` decimal(6,4) default NULL,
2331 `discount` float(6,4) default NULL,
2332 `fax` varchar(50) default NULL,
2334 KEY `listprice` (`listprice`),
2335 KEY `invoiceprice` (`invoiceprice`),
2336 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2337 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2338 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2341 -- Table structure for table `aqbudgets`
2344 DROP TABLE IF EXISTS `aqbudgets`;
2345 CREATE TABLE `aqbudgets` (
2346 `budget_id` int(11) NOT NULL auto_increment,
2347 `budget_parent_id` int(11) default NULL,
2348 `budget_code` varchar(30) default NULL,
2349 `budget_name` varchar(80) default NULL,
2350 `budget_branchcode` varchar(10) default NULL,
2351 `budget_amount` decimal(28,6) NULL default '0.00',
2352 `budget_encumb` decimal(28,6) NULL default '0.00',
2353 `budget_expend` decimal(28,6) NULL default '0.00',
2354 `budget_notes` mediumtext,
2355 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2356 `budget_period_id` int(11) default NULL,
2357 `sort1_authcat` varchar(80) default NULL,
2358 `sort2_authcat` varchar(80) default NULL,
2359 `budget_owner_id` int(11) default NULL,
2360 `budget_permission` int(1) default '0',
2361 PRIMARY KEY (`budget_id`)
2362 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2366 -- Table structure for table `aqbudgetperiods`
2370 DROP TABLE IF EXISTS `aqbudgetperiods`;
2371 CREATE TABLE `aqbudgetperiods` (
2372 `budget_period_id` int(11) NOT NULL auto_increment,
2373 `budget_period_startdate` date NOT NULL,
2374 `budget_period_enddate` date NOT NULL,
2375 `budget_period_active` tinyint(1) default '0',
2376 `budget_period_description` mediumtext,
2377 `budget_period_total` decimal(28,6),
2378 `budget_period_locked` tinyint(1) default NULL,
2379 `sort1_authcat` varchar(10) default NULL,
2380 `sort2_authcat` varchar(10) default NULL,
2381 PRIMARY KEY (`budget_period_id`)
2382 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2385 -- Table structure for table `aqbudgets_planning`
2388 DROP TABLE IF EXISTS `aqbudgets_planning`;
2389 CREATE TABLE `aqbudgets_planning` (
2390 `plan_id` int(11) NOT NULL auto_increment,
2391 `budget_id` int(11) NOT NULL,
2392 `budget_period_id` int(11) NOT NULL,
2393 `estimated_amount` decimal(28,6) default NULL,
2394 `authcat` varchar(30) NOT NULL,
2395 `authvalue` varchar(30) NOT NULL,
2396 PRIMARY KEY (`plan_id`),
2397 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2398 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2401 -- Table structure for table 'aqcontract'
2404 DROP TABLE IF EXISTS `aqcontract`;
2405 CREATE TABLE `aqcontract` (
2406 `contractnumber` int(11) NOT NULL auto_increment,
2407 `contractstartdate` date default NULL,
2408 `contractenddate` date default NULL,
2409 `contractname` varchar(50) default NULL,
2410 `contractdescription` mediumtext,
2411 `booksellerid` int(11) not NULL,
2412 PRIMARY KEY (`contractnumber`),
2413 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2414 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2415 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2418 -- Table structure for table `aqorderdelivery`
2421 DROP TABLE IF EXISTS `aqorderdelivery`;
2422 CREATE TABLE `aqorderdelivery` (
2423 `ordernumber` date default NULL,
2424 `deliverynumber` smallint(6) NOT NULL default 0,
2425 `deliverydate` varchar(18) default NULL,
2426 `qtydelivered` smallint(6) default NULL,
2427 `deliverycomments` mediumtext
2428 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2431 -- Table structure for table `aqorders`
2434 DROP TABLE IF EXISTS `aqorders`;
2435 CREATE TABLE `aqorders` (
2436 `ordernumber` int(11) NOT NULL auto_increment,
2437 `biblionumber` int(11) default NULL,
2438 `entrydate` date default NULL,
2439 `quantity` smallint(6) default NULL,
2440 `currency` varchar(3) default NULL,
2441 `listprice` decimal(28,6) default NULL,
2442 `totalamount` decimal(28,6) default NULL,
2443 `datereceived` date default NULL,
2444 `booksellerinvoicenumber` mediumtext,
2445 `freight` decimal(28,6) default NULL,
2446 `unitprice` decimal(28,6) default NULL,
2447 `quantityreceived` smallint(6) default NULL,
2448 `cancelledby` varchar(10) default NULL,
2449 `datecancellationprinted` date default NULL,
2451 `supplierreference` mediumtext,
2452 `purchaseordernumber` mediumtext,
2453 `subscription` tinyint(1) default NULL,
2454 `serialid` varchar(30) default NULL,
2455 `basketno` int(11) default NULL,
2456 `biblioitemnumber` int(11) default NULL,
2457 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2458 `rrp` decimal(13,2) default NULL,
2459 `ecost` decimal(13,2) default NULL,
2460 `gst` decimal(13,2) default NULL,
2461 `budget_id` int(11) NOT NULL,
2462 `budgetgroup_id` int(11) NOT NULL,
2463 `budgetdate` date default NULL,
2464 `sort1` varchar(80) default NULL,
2465 `sort2` varchar(80) default NULL,
2466 `sort1_authcat` varchar(10) default NULL,
2467 `sort2_authcat` varchar(10) default NULL,
2468 `uncertainprice` tinyint(1),
2469 PRIMARY KEY (`ordernumber`),
2470 KEY `basketno` (`basketno`),
2471 KEY `biblionumber` (`biblionumber`),
2472 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2473 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2474 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2477 -- Table structure for table `aqorders_items`
2480 DROP TABLE IF EXISTS `aqorders_items`;
2481 CREATE TABLE `aqorders_items` (
2482 `ordernumber` int(11) NOT NULL,
2483 `itemnumber` int(11) NOT NULL,
2484 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2485 PRIMARY KEY (`itemnumber`),
2486 KEY `ordernumber` (`ordernumber`)
2487 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2490 -- Table structure for table `fieldmapping`
2493 DROP TABLE IF EXISTS `fieldmapping`;
2494 CREATE TABLE `fieldmapping` (
2495 `id` int(11) NOT NULL auto_increment,
2496 `field` varchar(255) NOT NULL,
2497 `frameworkcode` char(4) NOT NULL default '',
2498 `fieldcode` char(3) NOT NULL,
2499 `subfieldcode` char(1) NOT NULL,
2501 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2504 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2505 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2506 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2507 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2508 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2509 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2510 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2511 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;