3 -- Host: localhost Database: koha30test
4 -- ------------------------------------------------------
5 -- Server version 4.1.22
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
11 /*!40101 SET NAMES utf8 */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19 -- Table structure for table `auth_header`
22 DROP TABLE IF EXISTS `auth_header`;
23 CREATE TABLE `auth_header` (
24 `authid` bigint(20) unsigned NOT NULL auto_increment,
25 `authtypecode` varchar(10) NOT NULL default '',
26 `datecreated` date default NULL,
27 `datemodified` date default NULL,
28 `origincode` varchar(20) default NULL,
29 `authtrees` mediumtext,
31 `linkid` bigint(20) default NULL,
32 `marcxml` longtext NOT NULL,
33 PRIMARY KEY (`authid`),
34 KEY `origincode` (`origincode`)
35 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
38 -- Table structure for table `auth_subfield_structure`
41 DROP TABLE IF EXISTS `auth_subfield_structure`;
42 CREATE TABLE `auth_subfield_structure` (
43 `authtypecode` varchar(10) NOT NULL default '',
44 `tagfield` varchar(3) NOT NULL default '',
45 `tagsubfield` varchar(1) NOT NULL default '',
46 `liblibrarian` varchar(255) NOT NULL default '',
47 `libopac` varchar(255) NOT NULL default '',
48 `repeatable` tinyint(4) NOT NULL default 0,
49 `mandatory` tinyint(4) NOT NULL default 0,
50 `tab` tinyint(1) default NULL,
51 `authorised_value` varchar(10) default NULL,
52 `value_builder` varchar(80) default NULL,
53 `seealso` varchar(255) default NULL,
54 `isurl` tinyint(1) default NULL,
55 `hidden` tinyint(3) NOT NULL default 0,
56 `linkid` tinyint(1) NOT NULL default 0,
57 `kohafield` varchar(45) NULL default '',
58 `frameworkcode` varchar(10) NOT NULL default '',
59 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
60 KEY `tab` (`authtypecode`,`tab`)
61 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
64 -- Table structure for table `auth_tag_structure`
67 DROP TABLE IF EXISTS `auth_tag_structure`;
68 CREATE TABLE `auth_tag_structure` (
69 `authtypecode` varchar(10) NOT NULL default '',
70 `tagfield` varchar(3) NOT NULL default '',
71 `liblibrarian` varchar(255) NOT NULL default '',
72 `libopac` varchar(255) NOT NULL default '',
73 `repeatable` tinyint(4) NOT NULL default 0,
74 `mandatory` tinyint(4) NOT NULL default 0,
75 `authorised_value` varchar(10) default NULL,
76 PRIMARY KEY (`authtypecode`,`tagfield`),
77 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
78 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
81 -- Table structure for table `auth_types`
84 DROP TABLE IF EXISTS `auth_types`;
85 CREATE TABLE `auth_types` (
86 `authtypecode` varchar(10) NOT NULL default '',
87 `authtypetext` varchar(255) NOT NULL default '',
88 `auth_tag_to_report` varchar(3) NOT NULL default '',
89 `summary` mediumtext NOT NULL,
90 PRIMARY KEY (`authtypecode`)
91 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
94 -- Table structure for table `authorised_values`
97 DROP TABLE IF EXISTS `authorised_values`;
98 CREATE TABLE `authorised_values` (
99 `id` int(11) NOT NULL auto_increment,
100 `category` varchar(10) NOT NULL default '',
101 `authorised_value` varchar(80) NOT NULL default '',
102 `lib` varchar(80) default NULL,
103 `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 `finedays` int(11) default NULL,
928 `firstremind` int(11) default NULL,
929 `chargeperiod` int(11) default NULL,
930 `accountsent` int(11) default NULL,
931 `chargename` varchar(100) default NULL,
932 `maxissueqty` int(4) default NULL,
933 `issuelength` int(4) default NULL,
934 `renewalsallowed` smallint(6) NOT NULL default "0",
935 `reservesallowed` smallint(6) NOT NULL default "0",
936 `branchcode` varchar(10) NOT NULL default '',
937 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
938 KEY `categorycode` (`categorycode`),
939 KEY `itemtype` (`itemtype`)
940 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
943 -- Table structure for table `items`
946 DROP TABLE IF EXISTS `items`;
947 CREATE TABLE `items` (
948 `itemnumber` int(11) NOT NULL auto_increment,
949 `biblionumber` int(11) NOT NULL default 0,
950 `biblioitemnumber` int(11) NOT NULL default 0,
951 `barcode` varchar(20) default NULL,
952 `dateaccessioned` date default NULL,
953 `booksellerid` mediumtext default NULL,
954 `homebranch` varchar(10) default NULL,
955 `price` decimal(8,2) default NULL,
956 `replacementprice` decimal(8,2) default NULL,
957 `replacementpricedate` date default NULL,
958 `datelastborrowed` date default NULL,
959 `datelastseen` date default NULL,
960 `stack` tinyint(1) default NULL,
961 `notforloan` tinyint(1) NOT NULL default 0,
962 `damaged` tinyint(1) NOT NULL default 0,
963 `itemlost` tinyint(1) NOT NULL default 0,
964 `wthdrawn` tinyint(1) NOT NULL default 0,
965 `itemcallnumber` varchar(255) default NULL,
966 `issues` smallint(6) default NULL,
967 `renewals` smallint(6) default NULL,
968 `reserves` smallint(6) default NULL,
969 `restricted` tinyint(1) default NULL,
970 `itemnotes` mediumtext,
971 `holdingbranch` varchar(10) default NULL,
972 `paidfor` mediumtext,
973 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
974 `location` varchar(80) default NULL,
975 `permanent_location` varchar(80) default NULL,
976 `onloan` date default NULL,
977 `cn_source` varchar(10) default NULL,
978 `cn_sort` varchar(30) default NULL,
979 `ccode` varchar(10) default NULL,
980 `materials` varchar(10) default NULL,
981 `uri` varchar(255) default NULL,
982 `itype` varchar(10) default NULL,
983 `more_subfields_xml` longtext default NULL,
984 `enumchron` varchar(80) default NULL,
985 `copynumber` varchar(32) default NULL,
986 `stocknumber` varchar(32) default NULL,
987 PRIMARY KEY (`itemnumber`),
988 UNIQUE KEY `itembarcodeidx` (`barcode`),
989 UNIQUE KEY `itemstocknumberidx` (`stocknumber`),
990 KEY `itembinoidx` (`biblioitemnumber`),
991 KEY `itembibnoidx` (`biblionumber`),
992 KEY `homebranch` (`homebranch`),
993 KEY `holdingbranch` (`holdingbranch`),
994 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
995 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
996 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
997 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1000 -- Table structure for table `itemtypes`
1003 DROP TABLE IF EXISTS `itemtypes`;
1004 CREATE TABLE `itemtypes` (
1005 `itemtype` varchar(10) NOT NULL default '',
1006 `description` mediumtext,
1007 `rentalcharge` double(16,4) default NULL,
1008 `notforloan` smallint(6) default NULL,
1009 `imageurl` varchar(200) default NULL,
1011 PRIMARY KEY (`itemtype`),
1012 UNIQUE KEY `itemtype` (`itemtype`)
1013 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1016 -- Table structure for table `labels_batches`
1019 DROP TABLE IF EXISTS `labels_batches`;
1020 CREATE TABLE `labels_batches` (
1021 `label_id` int(11) NOT NULL auto_increment,
1022 `batch_id` int(10) NOT NULL default '1',
1023 `item_number` int(11) NOT NULL default '0',
1024 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1025 `branch_code` varchar(10) NOT NULL default 'NB',
1026 PRIMARY KEY USING BTREE (`label_id`),
1027 KEY `branch_fk` (`branch_code`),
1028 KEY `item_fk` (`item_number`),
1029 CONSTRAINT `item_fk_constraint` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE,
1030 CONSTRAINT `branch_fk_constraint` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE
1031 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1034 -- Table structure for table `labels_layouts`
1037 DROP TABLE IF EXISTS `labels_layouts`;
1038 CREATE TABLE `labels_layouts` (
1039 `layout_id` int(4) NOT NULL auto_increment,
1040 `barcode_type` char(100) NOT NULL default 'CODE39',
1041 `printing_type` char(32) NOT NULL default 'BAR',
1042 `layout_name` char(20) NOT NULL default 'DEFAULT',
1043 `guidebox` int(1) default '0',
1044 `font` char(10) character set utf8 collate utf8_unicode_ci NOT NULL default 'TR',
1045 `font_size` int(4) NOT NULL default '10',
1046 `callnum_split` int(1) default '0',
1047 `text_justify` char(1) character set utf8 collate utf8_unicode_ci NOT NULL default 'L',
1048 `format_string` varchar(210) NOT NULL default 'barcode',
1049 PRIMARY KEY USING BTREE (`layout_id`)
1050 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1053 -- Table structure for table `labels_templates`
1056 DROP TABLE IF EXISTS `labels_templates`;
1057 CREATE TABLE `labels_templates` (
1058 `template_id` int(4) NOT NULL auto_increment,
1059 `profile_id` int(4) default NULL,
1060 `template_code` char(100) NOT NULL default 'DEFAULT TEMPLATE',
1061 `template_desc` char(100) NOT NULL default 'Default description',
1062 `page_width` float NOT NULL default '0',
1063 `page_height` float NOT NULL default '0',
1064 `label_width` float NOT NULL default '0',
1065 `label_height` float NOT NULL default '0',
1066 `top_text_margin` float NOT NULL default '0',
1067 `left_text_margin` float NOT NULL default '0',
1068 `top_margin` float NOT NULL default '0',
1069 `left_margin` float NOT NULL default '0',
1070 `cols` int(2) NOT NULL default '0',
1071 `rows` int(2) NOT NULL default '0',
1072 `col_gap` float NOT NULL default '0',
1073 `row_gap` float NOT NULL default '0',
1074 `units` char(20) NOT NULL default 'POINT',
1075 PRIMARY KEY (`template_id`),
1076 KEY `template_profile_fk_constraint` (`profile_id`)
1077 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1080 -- Table structure for table `letter`
1083 DROP TABLE IF EXISTS `letter`;
1084 CREATE TABLE `letter` (
1085 `module` varchar(20) NOT NULL default '',
1086 `code` varchar(20) NOT NULL default '',
1087 `name` varchar(100) NOT NULL default '',
1088 `title` varchar(200) NOT NULL default '',
1090 PRIMARY KEY (`module`,`code`)
1091 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1094 -- Table structure for table `marc_subfield_structure`
1097 DROP TABLE IF EXISTS `marc_subfield_structure`;
1098 CREATE TABLE `marc_subfield_structure` (
1099 `tagfield` varchar(3) NOT NULL default '',
1100 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1101 `liblibrarian` varchar(255) NOT NULL default '',
1102 `libopac` varchar(255) NOT NULL default '',
1103 `repeatable` tinyint(4) NOT NULL default 0,
1104 `mandatory` tinyint(4) NOT NULL default 0,
1105 `kohafield` varchar(40) default NULL,
1106 `tab` tinyint(1) default NULL,
1107 `authorised_value` varchar(20) default NULL,
1108 `authtypecode` varchar(20) default NULL,
1109 `value_builder` varchar(80) default NULL,
1110 `isurl` tinyint(1) default NULL,
1111 `hidden` tinyint(1) default NULL,
1112 `frameworkcode` varchar(4) NOT NULL default '',
1113 `seealso` varchar(1100) default NULL,
1114 `link` varchar(80) default NULL,
1115 `defaultvalue` text default NULL,
1116 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1117 KEY `kohafield_2` (`kohafield`),
1118 KEY `tab` (`frameworkcode`,`tab`),
1119 KEY `kohafield` (`frameworkcode`,`kohafield`)
1120 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1123 -- Table structure for table `marc_tag_structure`
1126 DROP TABLE IF EXISTS `marc_tag_structure`;
1127 CREATE TABLE `marc_tag_structure` (
1128 `tagfield` varchar(3) NOT NULL default '',
1129 `liblibrarian` varchar(255) NOT NULL default '',
1130 `libopac` varchar(255) NOT NULL default '',
1131 `repeatable` tinyint(4) NOT NULL default 0,
1132 `mandatory` tinyint(4) NOT NULL default 0,
1133 `authorised_value` varchar(10) default NULL,
1134 `frameworkcode` varchar(4) NOT NULL default '',
1135 PRIMARY KEY (`frameworkcode`,`tagfield`)
1136 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1139 -- Table structure for table `marc_matchers`
1142 DROP TABLE IF EXISTS `marc_matchers`;
1143 CREATE TABLE `marc_matchers` (
1144 `matcher_id` int(11) NOT NULL auto_increment,
1145 `code` varchar(10) NOT NULL default '',
1146 `description` varchar(255) NOT NULL default '',
1147 `record_type` varchar(10) NOT NULL default 'biblio',
1148 `threshold` int(11) NOT NULL default 0,
1149 PRIMARY KEY (`matcher_id`),
1150 KEY `code` (`code`),
1151 KEY `record_type` (`record_type`)
1152 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1155 -- Table structure for table `matchpoints`
1157 DROP TABLE IF EXISTS `matchpoints`;
1158 CREATE TABLE `matchpoints` (
1159 `matcher_id` int(11) NOT NULL,
1160 `matchpoint_id` int(11) NOT NULL auto_increment,
1161 `search_index` varchar(30) NOT NULL default '',
1162 `score` int(11) NOT NULL default 0,
1163 PRIMARY KEY (`matchpoint_id`),
1164 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1165 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1170 -- Table structure for table `matchpoint_components`
1172 DROP TABLE IF EXISTS `matchpoint_components`;
1173 CREATE TABLE `matchpoint_components` (
1174 `matchpoint_id` int(11) NOT NULL,
1175 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1176 sequence int(11) NOT NULL default 0,
1177 tag varchar(3) NOT NULL default '',
1178 subfields varchar(40) NOT NULL default '',
1179 offset int(4) NOT NULL default 0,
1180 length int(4) NOT NULL default 0,
1181 PRIMARY KEY (`matchpoint_component_id`),
1182 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1183 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1184 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1188 -- Table structure for table `matcher_component_norms`
1190 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1191 CREATE TABLE `matchpoint_component_norms` (
1192 `matchpoint_component_id` int(11) NOT NULL,
1193 `sequence` int(11) NOT NULL default 0,
1194 `norm_routine` varchar(50) NOT NULL default '',
1195 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1196 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1197 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1201 -- Table structure for table `matcher_matchpoints`
1203 DROP TABLE IF EXISTS `matcher_matchpoints`;
1204 CREATE TABLE `matcher_matchpoints` (
1205 `matcher_id` int(11) NOT NULL,
1206 `matchpoint_id` int(11) NOT NULL,
1207 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1208 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1209 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1210 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1214 -- Table structure for table `matchchecks`
1216 DROP TABLE IF EXISTS `matchchecks`;
1217 CREATE TABLE `matchchecks` (
1218 `matcher_id` int(11) NOT NULL,
1219 `matchcheck_id` int(11) NOT NULL auto_increment,
1220 `source_matchpoint_id` int(11) NOT NULL,
1221 `target_matchpoint_id` int(11) NOT NULL,
1222 PRIMARY KEY (`matchcheck_id`),
1223 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1224 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1225 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1226 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1227 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1228 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1232 -- Table structure for table `notifys`
1235 DROP TABLE IF EXISTS `notifys`;
1236 CREATE TABLE `notifys` (
1237 `notify_id` int(11) NOT NULL default 0,
1238 `borrowernumber` int(11) NOT NULL default 0,
1239 `itemnumber` int(11) NOT NULL default 0,
1240 `notify_date` date default NULL,
1241 `notify_send_date` date default NULL,
1242 `notify_level` int(1) NOT NULL default 0,
1243 `method` varchar(20) NOT NULL default ''
1244 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1247 -- Table structure for table `nozebra`
1250 DROP TABLE IF EXISTS `nozebra`;
1251 CREATE TABLE `nozebra` (
1252 `server` varchar(20) NOT NULL,
1253 `indexname` varchar(40) NOT NULL,
1254 `value` varchar(250) NOT NULL,
1255 `biblionumbers` longtext NOT NULL,
1256 KEY `indexname` (`server`,`indexname`),
1257 KEY `value` (`server`,`value`))
1258 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1261 -- Table structure for table `old_issues`
1264 DROP TABLE IF EXISTS `old_issues`;
1265 CREATE TABLE `old_issues` (
1266 `borrowernumber` int(11) default NULL,
1267 `itemnumber` int(11) default NULL,
1268 `date_due` date default NULL,
1269 `branchcode` varchar(10) default NULL,
1270 `issuingbranch` varchar(18) default NULL,
1271 `returndate` date default NULL,
1272 `lastreneweddate` date default NULL,
1273 `return` varchar(4) default NULL,
1274 `renewals` tinyint(4) default NULL,
1275 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1276 `issuedate` date default NULL,
1277 KEY `old_issuesborridx` (`borrowernumber`),
1278 KEY `old_issuesitemidx` (`itemnumber`),
1279 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1280 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1281 ON DELETE SET NULL ON UPDATE SET NULL,
1282 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1283 ON DELETE SET NULL ON UPDATE SET NULL
1284 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1287 -- Table structure for table `old_reserves`
1289 DROP TABLE IF EXISTS `old_reserves`;
1290 CREATE TABLE `old_reserves` (
1291 `borrowernumber` int(11) default NULL,
1292 `reservedate` date default NULL,
1293 `biblionumber` int(11) default NULL,
1294 `constrainttype` varchar(1) default NULL,
1295 `branchcode` varchar(10) default NULL,
1296 `notificationdate` date default NULL,
1297 `reminderdate` date default NULL,
1298 `cancellationdate` date default NULL,
1299 `reservenotes` mediumtext,
1300 `priority` smallint(6) default NULL,
1301 `found` varchar(1) default NULL,
1302 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1303 `itemnumber` int(11) default NULL,
1304 `waitingdate` date default NULL,
1305 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1306 KEY `old_reserves_biblionumber` (`biblionumber`),
1307 KEY `old_reserves_itemnumber` (`itemnumber`),
1308 KEY `old_reserves_branchcode` (`branchcode`),
1309 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1310 ON DELETE SET NULL ON UPDATE SET NULL,
1311 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1312 ON DELETE SET NULL ON UPDATE SET NULL,
1313 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1314 ON DELETE SET NULL ON UPDATE SET NULL
1315 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1318 -- Table structure for table `opac_news`
1321 DROP TABLE IF EXISTS `opac_news`;
1322 CREATE TABLE `opac_news` (
1323 `idnew` int(10) unsigned NOT NULL auto_increment,
1324 `title` varchar(250) NOT NULL default '',
1325 `new` text NOT NULL,
1326 `lang` varchar(25) NOT NULL default '',
1327 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1328 `expirationdate` date default NULL,
1329 `number` int(11) default NULL,
1330 PRIMARY KEY (`idnew`)
1331 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1334 -- Table structure for table `overduerules`
1337 DROP TABLE IF EXISTS `overduerules`;
1338 CREATE TABLE `overduerules` (
1339 `branchcode` varchar(10) NOT NULL default '',
1340 `categorycode` varchar(10) NOT NULL default '',
1341 `delay1` int(4) default 0,
1342 `letter1` varchar(20) default NULL,
1343 `debarred1` varchar(1) default 0,
1344 `delay2` int(4) default 0,
1345 `debarred2` varchar(1) default 0,
1346 `letter2` varchar(20) default NULL,
1347 `delay3` int(4) default 0,
1348 `letter3` varchar(20) default NULL,
1349 `debarred3` int(1) default 0,
1350 PRIMARY KEY (`branchcode`,`categorycode`)
1351 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1354 -- Table structure for table `patroncards`
1357 DROP TABLE IF EXISTS `patroncards`;
1358 CREATE TABLE `patroncards` (
1359 `cardid` int(11) NOT NULL auto_increment,
1360 `batch_id` varchar(10) NOT NULL default '1',
1361 `borrowernumber` int(11) NOT NULL,
1362 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1363 PRIMARY KEY (`cardid`),
1364 KEY `patroncards_ibfk_1` (`borrowernumber`),
1365 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1366 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1369 -- Table structure for table `patronimage`
1372 DROP TABLE IF EXISTS `patronimage`;
1373 CREATE TABLE `patronimage` (
1374 `cardnumber` varchar(16) NOT NULL,
1375 `mimetype` varchar(15) NOT NULL,
1376 `imagefile` mediumblob NOT NULL,
1377 PRIMARY KEY (`cardnumber`),
1378 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1382 -- Table structure for table `printers`
1385 DROP TABLE IF EXISTS `printers`;
1386 CREATE TABLE `printers` (
1387 `printername` varchar(40) NOT NULL default '',
1388 `printqueue` varchar(20) default NULL,
1389 `printtype` varchar(20) default NULL,
1390 PRIMARY KEY (`printername`)
1391 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1394 -- Table structure for table `printers_profile`
1397 DROP TABLE IF EXISTS `printers_profile`;
1398 CREATE TABLE `printers_profile` (
1399 `profile_id` int(4) NOT NULL auto_increment,
1400 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1401 `template_id` int(4) NOT NULL default '0',
1402 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1403 `offset_horz` float NOT NULL default '0',
1404 `offset_vert` float NOT NULL default '0',
1405 `creep_horz` float NOT NULL default '0',
1406 `creep_vert` float NOT NULL default '0',
1407 `units` char(20) NOT NULL default 'POINT',
1408 PRIMARY KEY (`profile_id`),
1409 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`)
1410 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1413 -- Table structure for table `repeatable_holidays`
1416 DROP TABLE IF EXISTS `repeatable_holidays`;
1417 CREATE TABLE `repeatable_holidays` (
1418 `id` int(11) NOT NULL auto_increment,
1419 `branchcode` varchar(10) NOT NULL default '',
1420 `weekday` smallint(6) default NULL,
1421 `day` smallint(6) default NULL,
1422 `month` smallint(6) default NULL,
1423 `title` varchar(50) NOT NULL default '',
1424 `description` text NOT NULL,
1426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1429 -- Table structure for table `reports_dictionary`
1432 DROP TABLE IF EXISTS `reports_dictionary`;
1433 CREATE TABLE reports_dictionary (
1434 `id` int(11) NOT NULL auto_increment,
1435 `name` varchar(255) default NULL,
1437 `date_created` datetime default NULL,
1438 `date_modified` datetime default NULL,
1440 `area` int(11) default NULL,
1442 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1445 -- Table structure for table `reserveconstraints`
1448 DROP TABLE IF EXISTS `reserveconstraints`;
1449 CREATE TABLE `reserveconstraints` (
1450 `borrowernumber` int(11) NOT NULL default 0,
1451 `reservedate` date default NULL,
1452 `biblionumber` int(11) NOT NULL default 0,
1453 `biblioitemnumber` int(11) default NULL,
1454 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1455 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1458 -- Table structure for table `reserves`
1461 DROP TABLE IF EXISTS `reserves`;
1462 CREATE TABLE `reserves` (
1463 `borrowernumber` int(11) NOT NULL default 0,
1464 `reservedate` date default NULL,
1465 `biblionumber` int(11) NOT NULL default 0,
1466 `constrainttype` varchar(1) default NULL,
1467 `branchcode` varchar(10) default NULL,
1468 `notificationdate` date default NULL,
1469 `reminderdate` date default NULL,
1470 `cancellationdate` date default NULL,
1471 `reservenotes` mediumtext,
1472 `priority` smallint(6) default NULL,
1473 `found` varchar(1) default NULL,
1474 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1475 `itemnumber` int(11) default NULL,
1476 `waitingdate` date default NULL,
1477 KEY `borrowernumber` (`borrowernumber`),
1478 KEY `biblionumber` (`biblionumber`),
1479 KEY `itemnumber` (`itemnumber`),
1480 KEY `branchcode` (`branchcode`),
1481 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1482 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1483 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1484 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1485 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1488 -- Table structure for table `reviews`
1491 DROP TABLE IF EXISTS `reviews`;
1492 CREATE TABLE `reviews` (
1493 `reviewid` int(11) NOT NULL auto_increment,
1494 `borrowernumber` int(11) default NULL,
1495 `biblionumber` int(11) default NULL,
1497 `approved` tinyint(4) default NULL,
1498 `datereviewed` datetime default NULL,
1499 PRIMARY KEY (`reviewid`)
1500 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1503 -- Table structure for table `roadtype`
1506 DROP TABLE IF EXISTS `roadtype`;
1507 CREATE TABLE `roadtype` (
1508 `roadtypeid` int(11) NOT NULL auto_increment,
1509 `road_type` varchar(100) NOT NULL default '',
1510 PRIMARY KEY (`roadtypeid`)
1511 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1514 -- Table structure for table `saved_sql`
1517 DROP TABLE IF EXISTS `saved_sql`;
1518 CREATE TABLE saved_sql (
1519 `id` int(11) NOT NULL auto_increment,
1520 `borrowernumber` int(11) default NULL,
1521 `date_created` datetime default NULL,
1522 `last_modified` datetime default NULL,
1524 `last_run` datetime default NULL,
1525 `report_name` varchar(255) default NULL,
1526 `type` varchar(255) default NULL,
1529 KEY boridx (`borrowernumber`)
1530 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1534 -- Table structure for `saved_reports`
1537 DROP TABLE IF EXISTS `saved_reports`;
1538 CREATE TABLE saved_reports (
1539 `id` int(11) NOT NULL auto_increment,
1540 `report_id` int(11) default NULL,
1542 `date_run` datetime default NULL,
1544 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1548 -- Table structure for table `search_history`
1551 DROP TABLE IF EXISTS `search_history`;
1552 CREATE TABLE IF NOT EXISTS `search_history` (
1553 `userid` int(11) NOT NULL,
1554 `sessionid` varchar(32) NOT NULL,
1555 `query_desc` varchar(255) NOT NULL,
1556 `query_cgi` varchar(255) NOT NULL,
1557 `total` int(11) NOT NULL,
1558 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1559 KEY `userid` (`userid`),
1560 KEY `sessionid` (`sessionid`)
1561 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1565 -- Table structure for table `serial`
1568 DROP TABLE IF EXISTS `serial`;
1569 CREATE TABLE `serial` (
1570 `serialid` int(11) NOT NULL auto_increment,
1571 `biblionumber` varchar(100) NOT NULL default '',
1572 `subscriptionid` varchar(100) NOT NULL default '',
1573 `serialseq` varchar(100) NOT NULL default '',
1574 `status` tinyint(4) NOT NULL default 0,
1575 `planneddate` date default NULL,
1577 `publisheddate` date default NULL,
1578 `itemnumber` text default NULL,
1579 `claimdate` date default NULL,
1580 `routingnotes` text,
1581 PRIMARY KEY (`serialid`)
1582 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1585 -- Table structure for table `sessions`
1588 DROP TABLE IF EXISTS sessions;
1589 CREATE TABLE sessions (
1590 `id` varchar(32) NOT NULL,
1591 `a_session` text NOT NULL,
1593 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1596 -- Table structure for table `special_holidays`
1599 DROP TABLE IF EXISTS `special_holidays`;
1600 CREATE TABLE `special_holidays` (
1601 `id` int(11) NOT NULL auto_increment,
1602 `branchcode` varchar(10) NOT NULL default '',
1603 `day` smallint(6) NOT NULL default 0,
1604 `month` smallint(6) NOT NULL default 0,
1605 `year` smallint(6) NOT NULL default 0,
1606 `isexception` smallint(1) NOT NULL default 1,
1607 `title` varchar(50) NOT NULL default '',
1608 `description` text NOT NULL,
1610 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1613 -- Table structure for table `statistics`
1616 DROP TABLE IF EXISTS `statistics`;
1617 CREATE TABLE `statistics` (
1618 `datetime` datetime default NULL,
1619 `branch` varchar(10) default NULL,
1620 `proccode` varchar(4) default NULL,
1621 `value` double(16,4) default NULL,
1622 `type` varchar(16) default NULL,
1624 `usercode` varchar(10) default NULL,
1625 `itemnumber` int(11) default NULL,
1626 `itemtype` varchar(10) default NULL,
1627 `borrowernumber` int(11) default NULL,
1628 `associatedborrower` int(11) default NULL,
1629 KEY `timeidx` (`datetime`)
1630 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1633 -- Table structure for table `stopwords`
1636 DROP TABLE IF EXISTS `stopwords`;
1637 CREATE TABLE `stopwords` (
1638 `word` varchar(255) default NULL
1639 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1642 -- Table structure for table `subscription`
1645 DROP TABLE IF EXISTS `subscription`;
1646 CREATE TABLE `subscription` (
1647 `biblionumber` int(11) NOT NULL default 0,
1648 `subscriptionid` int(11) NOT NULL auto_increment,
1649 `librarian` varchar(100) default '',
1650 `startdate` date default NULL,
1651 `aqbooksellerid` int(11) default 0,
1652 `cost` int(11) default 0,
1653 `aqbudgetid` int(11) default 0,
1654 `weeklength` int(11) default 0,
1655 `monthlength` int(11) default 0,
1656 `numberlength` int(11) default 0,
1657 `periodicity` tinyint(4) default 0,
1658 `dow` varchar(100) default '',
1659 `numberingmethod` varchar(100) default '',
1661 `status` varchar(100) NOT NULL default '',
1662 `add1` int(11) default 0,
1663 `every1` int(11) default 0,
1664 `whenmorethan1` int(11) default 0,
1665 `setto1` int(11) default NULL,
1666 `lastvalue1` int(11) default NULL,
1667 `add2` int(11) default 0,
1668 `every2` int(11) default 0,
1669 `whenmorethan2` int(11) default 0,
1670 `setto2` int(11) default NULL,
1671 `lastvalue2` int(11) default NULL,
1672 `add3` int(11) default 0,
1673 `every3` int(11) default 0,
1674 `innerloop1` int(11) default 0,
1675 `innerloop2` int(11) default 0,
1676 `innerloop3` int(11) default 0,
1677 `whenmorethan3` int(11) default 0,
1678 `setto3` int(11) default NULL,
1679 `lastvalue3` int(11) default NULL,
1680 `issuesatonce` tinyint(3) NOT NULL default 1,
1681 `firstacquidate` date default NULL,
1682 `manualhistory` tinyint(1) NOT NULL default 0,
1683 `irregularity` text,
1684 `letter` varchar(20) default NULL,
1685 `numberpattern` tinyint(3) default 0,
1686 `distributedto` text,
1687 `internalnotes` longtext,
1689 `location` varchar(80) NULL default '',
1690 `branchcode` varchar(10) NOT NULL default '',
1691 `hemisphere` tinyint(3) default 0,
1692 `lastbranch` varchar(10),
1693 `serialsadditems` tinyint(1) NOT NULL default '0',
1694 `staffdisplaycount` VARCHAR(10) NULL,
1695 `opacdisplaycount` VARCHAR(10) NULL,
1696 `graceperiod` int(11) NOT NULL default '0',
1697 PRIMARY KEY (`subscriptionid`)
1698 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1701 -- Table structure for table `subscriptionhistory`
1704 DROP TABLE IF EXISTS `subscriptionhistory`;
1705 CREATE TABLE `subscriptionhistory` (
1706 `biblionumber` int(11) NOT NULL default 0,
1707 `subscriptionid` int(11) NOT NULL default 0,
1708 `histstartdate` date default NULL,
1709 `histenddate` date default NULL,
1710 `missinglist` longtext NOT NULL,
1711 `recievedlist` longtext NOT NULL,
1712 `opacnote` varchar(150) NOT NULL default '',
1713 `librariannote` varchar(150) NOT NULL default '',
1714 PRIMARY KEY (`subscriptionid`),
1715 KEY `biblionumber` (`biblionumber`)
1716 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1719 -- Table structure for table `subscriptionroutinglist`
1722 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1723 CREATE TABLE `subscriptionroutinglist` (
1724 `routingid` int(11) NOT NULL auto_increment,
1725 `borrowernumber` int(11) default NULL,
1726 `ranking` int(11) default NULL,
1727 `subscriptionid` int(11) default NULL,
1728 PRIMARY KEY (`routingid`)
1729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1732 -- Table structure for table `suggestions`
1735 DROP TABLE IF EXISTS `suggestions`;
1736 CREATE TABLE `suggestions` (
1737 `suggestionid` int(8) NOT NULL auto_increment,
1738 `suggestedby` int(11) NOT NULL default 0,
1739 `suggesteddate` date NOT NULL default 0,
1740 `managedby` int(11) default NULL,
1741 `manageddate` date default NULL,
1742 acceptedby INT(11) default NULL,
1743 accepteddate date default NULL,
1744 rejectedby INT(11) default NULL,
1745 rejecteddate date default NULL,
1746 `STATUS` varchar(10) NOT NULL default '',
1748 `author` varchar(80) default NULL,
1749 `title` varchar(80) default NULL,
1750 `copyrightdate` smallint(6) default NULL,
1751 `publishercode` varchar(255) default NULL,
1752 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1753 `volumedesc` varchar(255) default NULL,
1754 `publicationyear` smallint(6) default 0,
1755 `place` varchar(255) default NULL,
1756 `isbn` varchar(30) default NULL,
1757 `mailoverseeing` smallint(1) default 0,
1758 `biblionumber` int(11) default NULL,
1761 branchcode VARCHAR(10) default NULL,
1762 collectiontitle text default NULL,
1763 itemtype VARCHAR(30) default NULL,
1764 PRIMARY KEY (`suggestionid`),
1765 KEY `suggestedby` (`suggestedby`),
1766 KEY `managedby` (`managedby`)
1767 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1770 -- Table structure for table `systempreferences`
1773 DROP TABLE IF EXISTS `systempreferences`;
1774 CREATE TABLE `systempreferences` (
1775 `variable` varchar(50) NOT NULL default '',
1777 `options` mediumtext,
1779 `type` varchar(20) default NULL,
1780 PRIMARY KEY (`variable`)
1781 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1784 -- Table structure for table `tags`
1787 DROP TABLE IF EXISTS `tags`;
1788 CREATE TABLE `tags` (
1789 `entry` varchar(255) NOT NULL default '',
1790 `weight` bigint(20) NOT NULL default 0,
1791 PRIMARY KEY (`entry`)
1792 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1795 -- Table structure for table `tags_all`
1798 DROP TABLE IF EXISTS `tags_all`;
1799 CREATE TABLE `tags_all` (
1800 `tag_id` int(11) NOT NULL auto_increment,
1801 `borrowernumber` int(11) NOT NULL,
1802 `biblionumber` int(11) NOT NULL,
1803 `term` varchar(255) NOT NULL,
1804 `language` int(4) default NULL,
1805 `date_created` datetime NOT NULL,
1806 PRIMARY KEY (`tag_id`),
1807 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1808 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1809 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1810 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1811 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1812 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1813 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1816 -- Table structure for table `tags_approval`
1819 DROP TABLE IF EXISTS `tags_approval`;
1820 CREATE TABLE `tags_approval` (
1821 `term` varchar(255) NOT NULL,
1822 `approved` int(1) NOT NULL default '0',
1823 `date_approved` datetime default NULL,
1824 `approved_by` int(11) default NULL,
1825 `weight_total` int(9) NOT NULL default '1',
1826 PRIMARY KEY (`term`),
1827 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1828 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1829 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1830 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1833 -- Table structure for table `tags_index`
1836 DROP TABLE IF EXISTS `tags_index`;
1837 CREATE TABLE `tags_index` (
1838 `term` varchar(255) NOT NULL,
1839 `biblionumber` int(11) NOT NULL,
1840 `weight` int(9) NOT NULL default '1',
1841 PRIMARY KEY (`term`,`biblionumber`),
1842 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1843 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1844 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1845 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1846 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1847 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1850 -- Table structure for table `userflags`
1853 DROP TABLE IF EXISTS `userflags`;
1854 CREATE TABLE `userflags` (
1855 `bit` int(11) NOT NULL default 0,
1856 `flag` varchar(30) default NULL,
1857 `flagdesc` varchar(255) default NULL,
1858 `defaulton` int(11) default NULL,
1860 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1863 -- Table structure for table `virtualshelves`
1866 DROP TABLE IF EXISTS `virtualshelves`;
1867 CREATE TABLE `virtualshelves` (
1868 `shelfnumber` int(11) NOT NULL auto_increment,
1869 `shelfname` varchar(255) default NULL,
1870 `owner` varchar(80) default NULL,
1871 `category` varchar(1) default NULL,
1872 `sortfield` varchar(16) default NULL,
1873 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1874 PRIMARY KEY (`shelfnumber`)
1875 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1878 -- Table structure for table `virtualshelfcontents`
1881 DROP TABLE IF EXISTS `virtualshelfcontents`;
1882 CREATE TABLE `virtualshelfcontents` (
1883 `shelfnumber` int(11) NOT NULL default 0,
1884 `biblionumber` int(11) NOT NULL default 0,
1885 `flags` int(11) default NULL,
1886 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1887 KEY `shelfnumber` (`shelfnumber`),
1888 KEY `biblionumber` (`biblionumber`),
1889 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1890 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1891 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1894 -- Table structure for table `z3950servers`
1897 DROP TABLE IF EXISTS `z3950servers`;
1898 CREATE TABLE `z3950servers` (
1899 `host` varchar(255) default NULL,
1900 `port` int(11) default NULL,
1901 `db` varchar(255) default NULL,
1902 `userid` varchar(255) default NULL,
1903 `password` varchar(255) default NULL,
1905 `id` int(11) NOT NULL auto_increment,
1906 `checked` smallint(6) default NULL,
1907 `rank` int(11) default NULL,
1908 `syntax` varchar(80) default NULL,
1910 `position` enum('primary','secondary','') NOT NULL default 'primary',
1911 `type` enum('zed','opensearch') NOT NULL default 'zed',
1912 `encoding` text default NULL,
1913 `description` text NOT NULL,
1915 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1918 -- Table structure for table `zebraqueue`
1921 DROP TABLE IF EXISTS `zebraqueue`;
1922 CREATE TABLE `zebraqueue` (
1923 `id` int(11) NOT NULL auto_increment,
1924 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1925 `operation` char(20) NOT NULL default '',
1926 `server` char(20) NOT NULL default '',
1927 `done` int(11) NOT NULL default '0',
1928 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1930 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1931 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1933 DROP TABLE IF EXISTS `services_throttle`;
1934 CREATE TABLE `services_throttle` (
1935 `service_type` varchar(10) NOT NULL default '',
1936 `service_count` varchar(45) default NULL,
1937 PRIMARY KEY (`service_type`)
1938 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1940 -- http://www.w3.org/International/articles/language-tags/
1943 DROP TABLE IF EXISTS language_subtag_registry;
1944 CREATE TABLE language_subtag_registry (
1946 type varchar(25), -- language-script-region-variant-extension-privateuse
1947 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1949 id int(11) NOT NULL auto_increment,
1951 KEY `subtag` (`subtag`)
1952 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1954 -- TODO: add suppress_scripts
1955 -- this maps three letter codes defined in iso639.2 back to their
1956 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1957 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1958 CREATE TABLE language_rfc4646_to_iso639 (
1959 rfc4646_subtag varchar(25),
1960 iso639_2_code varchar(25),
1961 id int(11) NOT NULL auto_increment,
1963 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1964 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1966 DROP TABLE IF EXISTS language_descriptions;
1967 CREATE TABLE language_descriptions (
1971 description varchar(255),
1972 id int(11) NOT NULL auto_increment,
1975 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1977 -- bi-directional support, keyed by script subcode
1978 DROP TABLE IF EXISTS language_script_bidi;
1979 CREATE TABLE language_script_bidi (
1980 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1981 bidi varchar(3), -- rtl ltr
1982 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1983 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1985 -- TODO: need to map language subtags to script subtags for detection
1986 -- of bidi when script is not specified (like ar, he)
1987 DROP TABLE IF EXISTS language_script_mapping;
1988 CREATE TABLE language_script_mapping (
1989 language_subtag varchar(25),
1990 script_subtag varchar(25),
1991 KEY `language_subtag` (`language_subtag`)
1992 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1994 DROP TABLE IF EXISTS `permissions`;
1995 CREATE TABLE `permissions` (
1996 `module_bit` int(11) NOT NULL DEFAULT 0,
1997 `code` varchar(64) DEFAULT NULL,
1998 `description` varchar(255) DEFAULT NULL,
1999 PRIMARY KEY (`module_bit`, `code`),
2000 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2001 ON DELETE CASCADE ON UPDATE CASCADE
2002 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2004 DROP TABLE IF EXISTS `serialitems`;
2005 CREATE TABLE `serialitems` (
2006 `itemnumber` int(11) NOT NULL,
2007 `serialid` int(11) NOT NULL,
2008 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2009 KEY `serialitems_sfk_1` (`serialid`),
2010 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2011 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2013 DROP TABLE IF EXISTS `user_permissions`;
2014 CREATE TABLE `user_permissions` (
2015 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2016 `module_bit` int(11) NOT NULL DEFAULT 0,
2017 `code` varchar(64) DEFAULT NULL,
2018 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2019 ON DELETE CASCADE ON UPDATE CASCADE,
2020 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2021 ON DELETE CASCADE ON UPDATE CASCADE
2022 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2025 -- Table structure for table `tmp_holdsqueue`
2028 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2029 CREATE TABLE `tmp_holdsqueue` (
2030 `biblionumber` int(11) default NULL,
2031 `itemnumber` int(11) default NULL,
2032 `barcode` varchar(20) default NULL,
2033 `surname` mediumtext NOT NULL,
2036 `borrowernumber` int(11) NOT NULL,
2037 `cardnumber` varchar(16) default NULL,
2038 `reservedate` date default NULL,
2040 `itemcallnumber` varchar(255) default NULL,
2041 `holdingbranch` varchar(10) default NULL,
2042 `pickbranch` varchar(10) default NULL,
2044 `item_level_request` tinyint(4) NOT NULL default 0
2045 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2048 -- Table structure for table `message_queue`
2051 DROP TABLE IF EXISTS `message_queue`;
2052 CREATE TABLE `message_queue` (
2053 `message_id` int(11) NOT NULL auto_increment,
2054 `borrowernumber` int(11) default NULL,
2057 `metadata` text DEFAULT NULL,
2058 `letter_code` varchar(64) DEFAULT NULL,
2059 `message_transport_type` varchar(20) NOT NULL,
2060 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2061 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2062 `to_address` mediumtext,
2063 `from_address` mediumtext,
2064 `content_type` text,
2065 KEY `message_id` (`message_id`),
2066 KEY `borrowernumber` (`borrowernumber`),
2067 KEY `message_transport_type` (`message_transport_type`),
2068 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2069 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2070 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2073 -- Table structure for table `message_transport_types`
2076 DROP TABLE IF EXISTS `message_transport_types`;
2077 CREATE TABLE `message_transport_types` (
2078 `message_transport_type` varchar(20) NOT NULL,
2079 PRIMARY KEY (`message_transport_type`)
2080 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2083 -- Table structure for table `message_attributes`
2086 DROP TABLE IF EXISTS `message_attributes`;
2087 CREATE TABLE `message_attributes` (
2088 `message_attribute_id` int(11) NOT NULL auto_increment,
2089 `message_name` varchar(20) NOT NULL default '',
2090 `takes_days` tinyint(1) NOT NULL default '0',
2091 PRIMARY KEY (`message_attribute_id`),
2092 UNIQUE KEY `message_name` (`message_name`)
2093 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2096 -- Table structure for table `message_transports`
2099 DROP TABLE IF EXISTS `message_transports`;
2100 CREATE TABLE `message_transports` (
2101 `message_attribute_id` int(11) NOT NULL,
2102 `message_transport_type` varchar(20) NOT NULL,
2103 `is_digest` tinyint(1) NOT NULL default '0',
2104 `letter_module` varchar(20) NOT NULL default '',
2105 `letter_code` varchar(20) NOT NULL default '',
2106 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2107 KEY `message_transport_type` (`message_transport_type`),
2108 KEY `letter_module` (`letter_module`,`letter_code`),
2109 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2110 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2111 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2115 -- Table structure for table `borrower_message_preferences`
2118 DROP TABLE IF EXISTS `borrower_message_preferences`;
2119 CREATE TABLE `borrower_message_preferences` (
2120 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2121 `borrowernumber` int(11) default NULL,
2122 `categorycode` varchar(10) default NULL,
2123 `message_attribute_id` int(11) default '0',
2124 `days_in_advance` int(11) default '0',
2125 `wants_digest` tinyint(1) NOT NULL default '0',
2126 PRIMARY KEY (`borrower_message_preference_id`),
2127 KEY `borrowernumber` (`borrowernumber`),
2128 KEY `categorycode` (`categorycode`),
2129 KEY `message_attribute_id` (`message_attribute_id`),
2130 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2131 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2132 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2133 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2136 -- Table structure for table `borrower_message_transport_preferences`
2139 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2140 CREATE TABLE `borrower_message_transport_preferences` (
2141 `borrower_message_preference_id` int(11) NOT NULL default '0',
2142 `message_transport_type` varchar(20) NOT NULL default '0',
2143 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2144 KEY `message_transport_type` (`message_transport_type`),
2145 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,
2146 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
2147 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2150 -- Table structure for the table branch_transfer_limits
2153 DROP TABLE IF EXISTS `branch_transfer_limits`;
2154 CREATE TABLE branch_transfer_limits (
2155 limitId int(8) NOT NULL auto_increment,
2156 toBranch varchar(10) NOT NULL,
2157 fromBranch varchar(10) NOT NULL,
2158 itemtype varchar(10) NULL,
2159 ccode varchar(10) NULL,
2160 PRIMARY KEY (limitId)
2161 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2164 -- Table structure for table `item_circulation_alert_preferences`
2167 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2168 CREATE TABLE `item_circulation_alert_preferences` (
2169 `id` int(11) NOT NULL auto_increment,
2170 `branchcode` varchar(10) NOT NULL,
2171 `categorycode` varchar(10) NOT NULL,
2172 `item_type` varchar(10) NOT NULL,
2173 `notification` varchar(16) NOT NULL,
2175 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2176 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2179 -- Table structure for table `messages`
2182 CREATE TABLE `messages` (
2183 `message_id` int(11) NOT NULL auto_increment,
2184 `borrowernumber` int(11) NOT NULL,
2185 `branchcode` varchar(4) default NULL,
2186 `message_type` varchar(1) NOT NULL,
2187 `message` text NOT NULL,
2188 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2189 PRIMARY KEY (`message_id`)
2190 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2193 -- Table structure for table `accountlines`
2196 DROP TABLE IF EXISTS `accountlines`;
2197 CREATE TABLE `accountlines` (
2198 `borrowernumber` int(11) NOT NULL default 0,
2199 `accountno` smallint(6) NOT NULL default 0,
2200 `itemnumber` int(11) default NULL,
2201 `date` date default NULL,
2202 `amount` decimal(28,6) default NULL,
2203 `description` mediumtext,
2204 `dispute` mediumtext,
2205 `accounttype` varchar(5) default NULL,
2206 `amountoutstanding` decimal(28,6) default NULL,
2207 `lastincrement` decimal(28,6) default NULL,
2208 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2209 `notify_id` int(11) NOT NULL default 0,
2210 `notify_level` int(2) NOT NULL default 0,
2211 KEY `acctsborridx` (`borrowernumber`),
2212 KEY `timeidx` (`timestamp`),
2213 KEY `itemnumber` (`itemnumber`),
2214 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2215 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2219 -- Table structure for table `accountoffsets`
2222 DROP TABLE IF EXISTS `accountoffsets`;
2223 CREATE TABLE `accountoffsets` (
2224 `borrowernumber` int(11) NOT NULL default 0,
2225 `accountno` smallint(6) NOT NULL default 0,
2226 `offsetaccount` smallint(6) NOT NULL default 0,
2227 `offsetamount` decimal(28,6) default NULL,
2228 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2229 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2233 -- Table structure for table `action_logs`
2236 DROP TABLE IF EXISTS `action_logs`;
2237 CREATE TABLE `action_logs` (
2238 `action_id` int(11) NOT NULL auto_increment,
2239 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2240 `user` int(11) NOT NULL default 0,
2243 `object` int(11) default NULL,
2245 PRIMARY KEY (`action_id`),
2246 KEY (`timestamp`,`user`)
2247 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2250 -- Table structure for table `alert`
2253 DROP TABLE IF EXISTS `alert`;
2254 CREATE TABLE `alert` (
2255 `alertid` int(11) NOT NULL auto_increment,
2256 `borrowernumber` int(11) NOT NULL default 0,
2257 `type` varchar(10) NOT NULL default '',
2258 `externalid` varchar(20) NOT NULL default '',
2259 PRIMARY KEY (`alertid`),
2260 KEY `borrowernumber` (`borrowernumber`),
2261 KEY `type` (`type`,`externalid`)
2262 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2265 -- Table structure for table `aqbasketgroups`
2268 DROP TABLE IF EXISTS `aqbasketgroups`;
2269 CREATE TABLE `aqbasketgroups` (
2270 `id` int(11) NOT NULL auto_increment,
2271 `name` varchar(50) default NULL,
2272 `closed` tinyint(1) default NULL,
2273 `booksellerid` int(11) NOT NULL,
2275 KEY `booksellerid` (`booksellerid`),
2276 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2280 -- Table structure for table `aqbasket`
2283 DROP TABLE IF EXISTS `aqbasket`;
2284 CREATE TABLE `aqbasket` (
2285 `basketno` int(11) NOT NULL auto_increment,
2286 `basketname` varchar(50) default NULL,
2288 `booksellernote` mediumtext,
2289 `contractnumber` int(11),
2290 `creationdate` date default NULL,
2291 `closedate` date default NULL,
2292 `booksellerid` int(11) NOT NULL default 1,
2293 `authorisedby` varchar(10) default NULL,
2294 `booksellerinvoicenumber` mediumtext,
2295 `basketgroupid` int(11),
2296 PRIMARY KEY (`basketno`),
2297 KEY `booksellerid` (`booksellerid`),
2298 KEY `basketgroupid` (`basketgroupid`),
2299 KEY `contractnumber` (`contractnumber`),
2300 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2301 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2302 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2303 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2306 -- Table structure for table `aqbooksellers`
2309 DROP TABLE IF EXISTS `aqbooksellers`;
2310 CREATE TABLE `aqbooksellers` (
2311 `id` int(11) NOT NULL auto_increment,
2312 `name` mediumtext NOT NULL,
2313 `address1` mediumtext,
2314 `address2` mediumtext,
2315 `address3` mediumtext,
2316 `address4` mediumtext,
2317 `phone` varchar(30) default NULL,
2318 `accountnumber` mediumtext,
2319 `othersupplier` mediumtext,
2320 `currency` varchar(3) NOT NULL default '',
2321 `booksellerfax` mediumtext,
2323 `bookselleremail` mediumtext,
2324 `booksellerurl` mediumtext,
2325 `contact` varchar(100) default NULL,
2326 `postal` mediumtext,
2327 `url` varchar(255) default NULL,
2328 `contpos` varchar(100) default NULL,
2329 `contphone` varchar(100) default NULL,
2330 `contfax` varchar(100) default NULL,
2331 `contaltphone` varchar(100) default NULL,
2332 `contemail` varchar(100) default NULL,
2333 `contnotes` mediumtext,
2334 `active` tinyint(4) default NULL,
2335 `listprice` varchar(10) default NULL,
2336 `invoiceprice` varchar(10) default NULL,
2337 `gstreg` tinyint(4) default NULL,
2338 `listincgst` tinyint(4) default NULL,
2339 `invoiceincgst` tinyint(4) default NULL,
2340 `gstrate` decimal(6,4) default NULL,
2341 `discount` float(6,4) default NULL,
2342 `fax` varchar(50) default NULL,
2344 KEY `listprice` (`listprice`),
2345 KEY `invoiceprice` (`invoiceprice`),
2346 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2347 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2348 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2351 -- Table structure for table `aqbudgets`
2354 DROP TABLE IF EXISTS `aqbudgets`;
2355 CREATE TABLE `aqbudgets` (
2356 `budget_id` int(11) NOT NULL auto_increment,
2357 `budget_parent_id` int(11) default NULL,
2358 `budget_code` varchar(30) default NULL,
2359 `budget_name` varchar(80) default NULL,
2360 `budget_branchcode` varchar(10) default NULL,
2361 `budget_amount` decimal(28,6) NULL default '0.00',
2362 `budget_encumb` decimal(28,6) NULL default '0.00',
2363 `budget_expend` decimal(28,6) NULL default '0.00',
2364 `budget_notes` mediumtext,
2365 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2366 `budget_period_id` int(11) default NULL,
2367 `sort1_authcat` varchar(80) default NULL,
2368 `sort2_authcat` varchar(80) default NULL,
2369 `budget_owner_id` int(11) default NULL,
2370 `budget_permission` int(1) default '0',
2371 PRIMARY KEY (`budget_id`)
2372 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2376 -- Table structure for table `aqbudgetperiods`
2380 DROP TABLE IF EXISTS `aqbudgetperiods`;
2381 CREATE TABLE `aqbudgetperiods` (
2382 `budget_period_id` int(11) NOT NULL auto_increment,
2383 `budget_period_startdate` date NOT NULL,
2384 `budget_period_enddate` date NOT NULL,
2385 `budget_period_active` tinyint(1) default '0',
2386 `budget_period_description` mediumtext,
2387 `budget_period_total` decimal(28,6),
2388 `budget_period_locked` tinyint(1) default NULL,
2389 `sort1_authcat` varchar(10) default NULL,
2390 `sort2_authcat` varchar(10) default NULL,
2391 PRIMARY KEY (`budget_period_id`)
2392 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2395 -- Table structure for table `aqbudgets_planning`
2398 DROP TABLE IF EXISTS `aqbudgets_planning`;
2399 CREATE TABLE `aqbudgets_planning` (
2400 `plan_id` int(11) NOT NULL auto_increment,
2401 `budget_id` int(11) NOT NULL,
2402 `budget_period_id` int(11) NOT NULL,
2403 `estimated_amount` decimal(28,6) default NULL,
2404 `authcat` varchar(30) NOT NULL,
2405 `authvalue` varchar(30) NOT NULL,
2406 PRIMARY KEY (`plan_id`),
2407 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2408 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2411 -- Table structure for table 'aqcontract'
2414 DROP TABLE IF EXISTS `aqcontract`;
2415 CREATE TABLE `aqcontract` (
2416 `contractnumber` int(11) NOT NULL auto_increment,
2417 `contractstartdate` date default NULL,
2418 `contractenddate` date default NULL,
2419 `contractname` varchar(50) default NULL,
2420 `contractdescription` mediumtext,
2421 `booksellerid` int(11) not NULL,
2422 PRIMARY KEY (`contractnumber`),
2423 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2424 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2425 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2428 -- Table structure for table `aqorderdelivery`
2431 DROP TABLE IF EXISTS `aqorderdelivery`;
2432 CREATE TABLE `aqorderdelivery` (
2433 `ordernumber` date default NULL,
2434 `deliverynumber` smallint(6) NOT NULL default 0,
2435 `deliverydate` varchar(18) default NULL,
2436 `qtydelivered` smallint(6) default NULL,
2437 `deliverycomments` mediumtext
2438 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2441 -- Table structure for table `aqorders`
2444 DROP TABLE IF EXISTS `aqorders`;
2445 CREATE TABLE `aqorders` (
2446 `ordernumber` int(11) NOT NULL auto_increment,
2447 `biblionumber` int(11) default NULL,
2448 `entrydate` date default NULL,
2449 `quantity` smallint(6) default NULL,
2450 `currency` varchar(3) default NULL,
2451 `listprice` decimal(28,6) default NULL,
2452 `totalamount` decimal(28,6) default NULL,
2453 `datereceived` date default NULL,
2454 `booksellerinvoicenumber` mediumtext,
2455 `freight` decimal(28,6) default NULL,
2456 `unitprice` decimal(28,6) default NULL,
2457 `quantityreceived` smallint(6) default NULL,
2458 `cancelledby` varchar(10) default NULL,
2459 `datecancellationprinted` date default NULL,
2461 `supplierreference` mediumtext,
2462 `purchaseordernumber` mediumtext,
2463 `subscription` tinyint(1) default NULL,
2464 `serialid` varchar(30) default NULL,
2465 `basketno` int(11) default NULL,
2466 `biblioitemnumber` int(11) default NULL,
2467 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2468 `rrp` decimal(13,2) default NULL,
2469 `ecost` decimal(13,2) default NULL,
2470 `gst` decimal(13,2) default NULL,
2471 `budget_id` int(11) NOT NULL,
2472 `budgetgroup_id` int(11) NOT NULL,
2473 `budgetdate` date default NULL,
2474 `sort1` varchar(80) default NULL,
2475 `sort2` varchar(80) default NULL,
2476 `sort1_authcat` varchar(10) default NULL,
2477 `sort2_authcat` varchar(10) default NULL,
2478 `uncertainprice` tinyint(1),
2479 PRIMARY KEY (`ordernumber`),
2480 KEY `basketno` (`basketno`),
2481 KEY `biblionumber` (`biblionumber`),
2482 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2483 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2484 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2487 -- Table structure for table `aqorders_items`
2490 DROP TABLE IF EXISTS `aqorders_items`;
2491 CREATE TABLE `aqorders_items` (
2492 `ordernumber` int(11) NOT NULL,
2493 `itemnumber` int(11) NOT NULL,
2494 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2495 PRIMARY KEY (`itemnumber`),
2496 KEY `ordernumber` (`ordernumber`)
2497 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2500 -- Table structure for table `fieldmapping`
2503 DROP TABLE IF EXISTS `fieldmapping`;
2504 CREATE TABLE `fieldmapping` (
2505 `id` int(11) NOT NULL auto_increment,
2506 `field` varchar(255) NOT NULL,
2507 `frameworkcode` char(4) NOT NULL default '',
2508 `fieldcode` char(3) NOT NULL,
2509 `subfieldcode` char(1) NOT NULL,
2511 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2514 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2515 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2516 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2517 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2518 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2519 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2520 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2521 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;