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 `creator_batches`
1019 DROP TABLE IF EXISTS `creator_batches`;
1020 SET @saved_cs_client = @@character_set_client;
1021 SET character_set_client = utf8;
1022 CREATE TABLE `creator_batches` (
1023 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1024 `batch_id` int(10) NOT NULL DEFAULT '1',
1025 `item_number` int(11) DEFAULT NULL,
1026 `borrower_number` int(11) DEFAULT NULL,
1027 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1028 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1029 `creator` char(15) NOT NULL DEFAULT 'Labels',
1030 PRIMARY KEY (`label_id`) USING BTREE,
1031 KEY `branch_fk_constraint` (`branch_code`),
1032 KEY `item_fk_constraint` (`item_number`),
1033 KEY `borrower_fk_constraint` (`borrower_number`),
1034 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1035 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1036 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1037 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1040 -- Table structure for table `creator_images`
1043 DROP TABLE IF EXISTS `creator_images`;
1044 SET @saved_cs_client = @@character_set_client;
1045 SET character_set_client = utf8;
1046 CREATE TABLE `creator_images` (
1047 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1048 `imagefile` mediumblob,
1049 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1050 PRIMARY KEY (`image_id`) USING BTREE,
1051 UNIQUE KEY `image_name_index` (`image_name`)
1052 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1055 -- Table structure for table `creator_layouts`
1058 DROP TABLE IF EXISTS `creator_layouts`;
1059 SET @saved_cs_client = @@character_set_client;
1060 SET character_set_client = utf8;
1061 CREATE TABLE `creator_layouts` (
1062 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1063 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1064 `start_label` int(2) NOT NULL DEFAULT '1',
1065 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1066 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1067 `guidebox` int(1) DEFAULT '0',
1068 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1069 `font_size` int(4) NOT NULL DEFAULT '10',
1070 `units` char(20) NOT NULL DEFAULT 'POINT',
1071 `callnum_split` int(1) DEFAULT '0',
1072 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1073 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1074 `layout_xml` text NOT NULL,
1075 `creator` char(15) NOT NULL DEFAULT 'Labels',
1076 PRIMARY KEY (`layout_id`) USING BTREE
1077 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1080 -- Table structure for table `creator_templates`
1083 DROP TABLE IF EXISTS `creator_templates`;
1084 SET @saved_cs_client = @@character_set_client;
1085 SET character_set_client = utf8;
1086 CREATE TABLE `creator_templates` (
1087 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1088 `profile_id` int(4) DEFAULT NULL,
1089 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1090 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1091 `page_width` float NOT NULL DEFAULT '0',
1092 `page_height` float NOT NULL DEFAULT '0',
1093 `label_width` float NOT NULL DEFAULT '0',
1094 `label_height` float NOT NULL DEFAULT '0',
1095 `top_text_margin` float NOT NULL DEFAULT '0',
1096 `left_text_margin` float NOT NULL DEFAULT '0',
1097 `top_margin` float NOT NULL DEFAULT '0',
1098 `left_margin` float NOT NULL DEFAULT '0',
1099 `cols` int(2) NOT NULL DEFAULT '0',
1100 `rows` int(2) NOT NULL DEFAULT '0',
1101 `col_gap` float NOT NULL DEFAULT '0',
1102 `row_gap` float NOT NULL DEFAULT '0',
1103 `units` char(20) NOT NULL DEFAULT 'POINT',
1104 `creator` char(15) NOT NULL DEFAULT 'Labels',
1105 PRIMARY KEY (`template_id`),
1106 KEY `template_profile_fk_constraint` (`profile_id`)
1107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1110 -- Table structure for table `letter`
1113 DROP TABLE IF EXISTS `letter`;
1114 CREATE TABLE `letter` (
1115 `module` varchar(20) NOT NULL default '',
1116 `code` varchar(20) NOT NULL default '',
1117 `name` varchar(100) NOT NULL default '',
1118 `title` varchar(200) NOT NULL default '',
1120 PRIMARY KEY (`module`,`code`)
1121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1124 -- Table structure for table `marc_subfield_structure`
1127 DROP TABLE IF EXISTS `marc_subfield_structure`;
1128 CREATE TABLE `marc_subfield_structure` (
1129 `tagfield` varchar(3) NOT NULL default '',
1130 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1131 `liblibrarian` varchar(255) NOT NULL default '',
1132 `libopac` varchar(255) NOT NULL default '',
1133 `repeatable` tinyint(4) NOT NULL default 0,
1134 `mandatory` tinyint(4) NOT NULL default 0,
1135 `kohafield` varchar(40) default NULL,
1136 `tab` tinyint(1) default NULL,
1137 `authorised_value` varchar(20) default NULL,
1138 `authtypecode` varchar(20) default NULL,
1139 `value_builder` varchar(80) default NULL,
1140 `isurl` tinyint(1) default NULL,
1141 `hidden` tinyint(1) default NULL,
1142 `frameworkcode` varchar(4) NOT NULL default '',
1143 `seealso` varchar(1100) default NULL,
1144 `link` varchar(80) default NULL,
1145 `defaultvalue` text default NULL,
1146 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1147 KEY `kohafield_2` (`kohafield`),
1148 KEY `tab` (`frameworkcode`,`tab`),
1149 KEY `kohafield` (`frameworkcode`,`kohafield`)
1150 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1153 -- Table structure for table `marc_tag_structure`
1156 DROP TABLE IF EXISTS `marc_tag_structure`;
1157 CREATE TABLE `marc_tag_structure` (
1158 `tagfield` varchar(3) NOT NULL default '',
1159 `liblibrarian` varchar(255) NOT NULL default '',
1160 `libopac` varchar(255) NOT NULL default '',
1161 `repeatable` tinyint(4) NOT NULL default 0,
1162 `mandatory` tinyint(4) NOT NULL default 0,
1163 `authorised_value` varchar(10) default NULL,
1164 `frameworkcode` varchar(4) NOT NULL default '',
1165 PRIMARY KEY (`frameworkcode`,`tagfield`)
1166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1169 -- Table structure for table `marc_matchers`
1172 DROP TABLE IF EXISTS `marc_matchers`;
1173 CREATE TABLE `marc_matchers` (
1174 `matcher_id` int(11) NOT NULL auto_increment,
1175 `code` varchar(10) NOT NULL default '',
1176 `description` varchar(255) NOT NULL default '',
1177 `record_type` varchar(10) NOT NULL default 'biblio',
1178 `threshold` int(11) NOT NULL default 0,
1179 PRIMARY KEY (`matcher_id`),
1180 KEY `code` (`code`),
1181 KEY `record_type` (`record_type`)
1182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1185 -- Table structure for table `matchpoints`
1187 DROP TABLE IF EXISTS `matchpoints`;
1188 CREATE TABLE `matchpoints` (
1189 `matcher_id` int(11) NOT NULL,
1190 `matchpoint_id` int(11) NOT NULL auto_increment,
1191 `search_index` varchar(30) NOT NULL default '',
1192 `score` int(11) NOT NULL default 0,
1193 PRIMARY KEY (`matchpoint_id`),
1194 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1195 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1196 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1200 -- Table structure for table `matchpoint_components`
1202 DROP TABLE IF EXISTS `matchpoint_components`;
1203 CREATE TABLE `matchpoint_components` (
1204 `matchpoint_id` int(11) NOT NULL,
1205 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1206 sequence int(11) NOT NULL default 0,
1207 tag varchar(3) NOT NULL default '',
1208 subfields varchar(40) NOT NULL default '',
1209 offset int(4) NOT NULL default 0,
1210 length int(4) NOT NULL default 0,
1211 PRIMARY KEY (`matchpoint_component_id`),
1212 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1213 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1214 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1215 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1218 -- Table structure for table `matcher_component_norms`
1220 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1221 CREATE TABLE `matchpoint_component_norms` (
1222 `matchpoint_component_id` int(11) NOT NULL,
1223 `sequence` int(11) NOT NULL default 0,
1224 `norm_routine` varchar(50) NOT NULL default '',
1225 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1226 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1227 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1231 -- Table structure for table `matcher_matchpoints`
1233 DROP TABLE IF EXISTS `matcher_matchpoints`;
1234 CREATE TABLE `matcher_matchpoints` (
1235 `matcher_id` int(11) NOT NULL,
1236 `matchpoint_id` int(11) NOT NULL,
1237 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1238 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1239 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1240 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1241 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1244 -- Table structure for table `matchchecks`
1246 DROP TABLE IF EXISTS `matchchecks`;
1247 CREATE TABLE `matchchecks` (
1248 `matcher_id` int(11) NOT NULL,
1249 `matchcheck_id` int(11) NOT NULL auto_increment,
1250 `source_matchpoint_id` int(11) NOT NULL,
1251 `target_matchpoint_id` int(11) NOT NULL,
1252 PRIMARY KEY (`matchcheck_id`),
1253 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1254 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1255 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1256 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1257 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1258 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1262 -- Table structure for table `notifys`
1265 DROP TABLE IF EXISTS `notifys`;
1266 CREATE TABLE `notifys` (
1267 `notify_id` int(11) NOT NULL default 0,
1268 `borrowernumber` int(11) NOT NULL default 0,
1269 `itemnumber` int(11) NOT NULL default 0,
1270 `notify_date` date default NULL,
1271 `notify_send_date` date default NULL,
1272 `notify_level` int(1) NOT NULL default 0,
1273 `method` varchar(20) NOT NULL default ''
1274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1277 -- Table structure for table `nozebra`
1280 DROP TABLE IF EXISTS `nozebra`;
1281 CREATE TABLE `nozebra` (
1282 `server` varchar(20) NOT NULL,
1283 `indexname` varchar(40) NOT NULL,
1284 `value` varchar(250) NOT NULL,
1285 `biblionumbers` longtext NOT NULL,
1286 KEY `indexname` (`server`,`indexname`),
1287 KEY `value` (`server`,`value`))
1288 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1291 -- Table structure for table `old_issues`
1294 DROP TABLE IF EXISTS `old_issues`;
1295 CREATE TABLE `old_issues` (
1296 `borrowernumber` int(11) default NULL,
1297 `itemnumber` int(11) default NULL,
1298 `date_due` date default NULL,
1299 `branchcode` varchar(10) default NULL,
1300 `issuingbranch` varchar(18) default NULL,
1301 `returndate` date default NULL,
1302 `lastreneweddate` date default NULL,
1303 `return` varchar(4) default NULL,
1304 `renewals` tinyint(4) default NULL,
1305 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1306 `issuedate` date default NULL,
1307 KEY `old_issuesborridx` (`borrowernumber`),
1308 KEY `old_issuesitemidx` (`itemnumber`),
1309 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1310 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1311 ON DELETE SET NULL ON UPDATE SET NULL,
1312 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1313 ON DELETE SET NULL ON UPDATE SET NULL
1314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1317 -- Table structure for table `old_reserves`
1319 DROP TABLE IF EXISTS `old_reserves`;
1320 CREATE TABLE `old_reserves` (
1321 `borrowernumber` int(11) default NULL,
1322 `reservedate` date default NULL,
1323 `biblionumber` int(11) default NULL,
1324 `constrainttype` varchar(1) default NULL,
1325 `branchcode` varchar(10) default NULL,
1326 `notificationdate` date default NULL,
1327 `reminderdate` date default NULL,
1328 `cancellationdate` date default NULL,
1329 `reservenotes` mediumtext,
1330 `priority` smallint(6) default NULL,
1331 `found` varchar(1) default NULL,
1332 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1333 `itemnumber` int(11) default NULL,
1334 `waitingdate` date default NULL,
1335 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1336 KEY `old_reserves_biblionumber` (`biblionumber`),
1337 KEY `old_reserves_itemnumber` (`itemnumber`),
1338 KEY `old_reserves_branchcode` (`branchcode`),
1339 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1340 ON DELETE SET NULL ON UPDATE SET NULL,
1341 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1342 ON DELETE SET NULL ON UPDATE SET NULL,
1343 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1344 ON DELETE SET NULL ON UPDATE SET NULL
1345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1348 -- Table structure for table `opac_news`
1351 DROP TABLE IF EXISTS `opac_news`;
1352 CREATE TABLE `opac_news` (
1353 `idnew` int(10) unsigned NOT NULL auto_increment,
1354 `title` varchar(250) NOT NULL default '',
1355 `new` text NOT NULL,
1356 `lang` varchar(25) NOT NULL default '',
1357 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1358 `expirationdate` date default NULL,
1359 `number` int(11) default NULL,
1360 PRIMARY KEY (`idnew`)
1361 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1364 -- Table structure for table `overduerules`
1367 DROP TABLE IF EXISTS `overduerules`;
1368 CREATE TABLE `overduerules` (
1369 `branchcode` varchar(10) NOT NULL default '',
1370 `categorycode` varchar(10) NOT NULL default '',
1371 `delay1` int(4) default 0,
1372 `letter1` varchar(20) default NULL,
1373 `debarred1` varchar(1) default 0,
1374 `delay2` int(4) default 0,
1375 `debarred2` varchar(1) default 0,
1376 `letter2` varchar(20) default NULL,
1377 `delay3` int(4) default 0,
1378 `letter3` varchar(20) default NULL,
1379 `debarred3` int(1) default 0,
1380 PRIMARY KEY (`branchcode`,`categorycode`)
1381 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1384 -- Table structure for table `patroncards`
1387 DROP TABLE IF EXISTS `patroncards`;
1388 CREATE TABLE `patroncards` (
1389 `cardid` int(11) NOT NULL auto_increment,
1390 `batch_id` varchar(10) NOT NULL default '1',
1391 `borrowernumber` int(11) NOT NULL,
1392 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1393 PRIMARY KEY (`cardid`),
1394 KEY `patroncards_ibfk_1` (`borrowernumber`),
1395 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1396 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1399 -- Table structure for table `patronimage`
1402 DROP TABLE IF EXISTS `patronimage`;
1403 CREATE TABLE `patronimage` (
1404 `cardnumber` varchar(16) NOT NULL,
1405 `mimetype` varchar(15) NOT NULL,
1406 `imagefile` mediumblob NOT NULL,
1407 PRIMARY KEY (`cardnumber`),
1408 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1409 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1412 -- Table structure for table `printers`
1415 DROP TABLE IF EXISTS `printers`;
1416 CREATE TABLE `printers` (
1417 `printername` varchar(40) NOT NULL default '',
1418 `printqueue` varchar(20) default NULL,
1419 `printtype` varchar(20) default NULL,
1420 PRIMARY KEY (`printername`)
1421 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1424 -- Table structure for table `printers_profile`
1427 DROP TABLE IF EXISTS `printers_profile`;
1428 CREATE TABLE `printers_profile` (
1429 `profile_id` int(4) NOT NULL auto_increment,
1430 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1431 `template_id` int(4) NOT NULL default '0',
1432 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1433 `offset_horz` float NOT NULL default '0',
1434 `offset_vert` float NOT NULL default '0',
1435 `creep_horz` float NOT NULL default '0',
1436 `creep_vert` float NOT NULL default '0',
1437 `units` char(20) NOT NULL default 'POINT',
1438 `creator` char(15) NOT NULL DEFAULT 'Labels',
1439 PRIMARY KEY (`profile_id`),
1440 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`) USING BTREE
1441 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1444 -- Table structure for table `repeatable_holidays`
1447 DROP TABLE IF EXISTS `repeatable_holidays`;
1448 CREATE TABLE `repeatable_holidays` (
1449 `id` int(11) NOT NULL auto_increment,
1450 `branchcode` varchar(10) NOT NULL default '',
1451 `weekday` smallint(6) default NULL,
1452 `day` smallint(6) default NULL,
1453 `month` smallint(6) default NULL,
1454 `title` varchar(50) NOT NULL default '',
1455 `description` text NOT NULL,
1457 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1460 -- Table structure for table `reports_dictionary`
1463 DROP TABLE IF EXISTS `reports_dictionary`;
1464 CREATE TABLE reports_dictionary (
1465 `id` int(11) NOT NULL auto_increment,
1466 `name` varchar(255) default NULL,
1468 `date_created` datetime default NULL,
1469 `date_modified` datetime default NULL,
1471 `area` int(11) default NULL,
1473 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1476 -- Table structure for table `reserveconstraints`
1479 DROP TABLE IF EXISTS `reserveconstraints`;
1480 CREATE TABLE `reserveconstraints` (
1481 `borrowernumber` int(11) NOT NULL default 0,
1482 `reservedate` date default NULL,
1483 `biblionumber` int(11) NOT NULL default 0,
1484 `biblioitemnumber` int(11) default NULL,
1485 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1486 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1489 -- Table structure for table `reserves`
1492 DROP TABLE IF EXISTS `reserves`;
1493 CREATE TABLE `reserves` (
1494 `borrowernumber` int(11) NOT NULL default 0,
1495 `reservedate` date default NULL,
1496 `biblionumber` int(11) NOT NULL default 0,
1497 `constrainttype` varchar(1) default NULL,
1498 `branchcode` varchar(10) default NULL,
1499 `notificationdate` date default NULL,
1500 `reminderdate` date default NULL,
1501 `cancellationdate` date default NULL,
1502 `reservenotes` mediumtext,
1503 `priority` smallint(6) default NULL,
1504 `found` varchar(1) default NULL,
1505 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1506 `itemnumber` int(11) default NULL,
1507 `waitingdate` date default NULL,
1508 KEY `borrowernumber` (`borrowernumber`),
1509 KEY `biblionumber` (`biblionumber`),
1510 KEY `itemnumber` (`itemnumber`),
1511 KEY `branchcode` (`branchcode`),
1512 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1513 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1514 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1515 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1516 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1519 -- Table structure for table `reviews`
1522 DROP TABLE IF EXISTS `reviews`;
1523 CREATE TABLE `reviews` (
1524 `reviewid` int(11) NOT NULL auto_increment,
1525 `borrowernumber` int(11) default NULL,
1526 `biblionumber` int(11) default NULL,
1528 `approved` tinyint(4) default NULL,
1529 `datereviewed` datetime default NULL,
1530 PRIMARY KEY (`reviewid`)
1531 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1534 -- Table structure for table `roadtype`
1537 DROP TABLE IF EXISTS `roadtype`;
1538 CREATE TABLE `roadtype` (
1539 `roadtypeid` int(11) NOT NULL auto_increment,
1540 `road_type` varchar(100) NOT NULL default '',
1541 PRIMARY KEY (`roadtypeid`)
1542 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1545 -- Table structure for table `saved_sql`
1548 DROP TABLE IF EXISTS `saved_sql`;
1549 CREATE TABLE saved_sql (
1550 `id` int(11) NOT NULL auto_increment,
1551 `borrowernumber` int(11) default NULL,
1552 `date_created` datetime default NULL,
1553 `last_modified` datetime default NULL,
1555 `last_run` datetime default NULL,
1556 `report_name` varchar(255) default NULL,
1557 `type` varchar(255) default NULL,
1560 KEY boridx (`borrowernumber`)
1561 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1565 -- Table structure for `saved_reports`
1568 DROP TABLE IF EXISTS `saved_reports`;
1569 CREATE TABLE saved_reports (
1570 `id` int(11) NOT NULL auto_increment,
1571 `report_id` int(11) default NULL,
1573 `date_run` datetime default NULL,
1575 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1579 -- Table structure for table `search_history`
1582 DROP TABLE IF EXISTS `search_history`;
1583 CREATE TABLE IF NOT EXISTS `search_history` (
1584 `userid` int(11) NOT NULL,
1585 `sessionid` varchar(32) NOT NULL,
1586 `query_desc` varchar(255) NOT NULL,
1587 `query_cgi` varchar(255) NOT NULL,
1588 `total` int(11) NOT NULL,
1589 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1590 KEY `userid` (`userid`),
1591 KEY `sessionid` (`sessionid`)
1592 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1596 -- Table structure for table `serial`
1599 DROP TABLE IF EXISTS `serial`;
1600 CREATE TABLE `serial` (
1601 `serialid` int(11) NOT NULL auto_increment,
1602 `biblionumber` varchar(100) NOT NULL default '',
1603 `subscriptionid` varchar(100) NOT NULL default '',
1604 `serialseq` varchar(100) NOT NULL default '',
1605 `status` tinyint(4) NOT NULL default 0,
1606 `planneddate` date default NULL,
1608 `publisheddate` date default NULL,
1609 `itemnumber` text default NULL,
1610 `claimdate` date default NULL,
1611 `routingnotes` text,
1612 PRIMARY KEY (`serialid`)
1613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1616 -- Table structure for table `sessions`
1619 DROP TABLE IF EXISTS sessions;
1620 CREATE TABLE sessions (
1621 `id` varchar(32) NOT NULL,
1622 `a_session` text NOT NULL,
1624 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1627 -- Table structure for table `special_holidays`
1630 DROP TABLE IF EXISTS `special_holidays`;
1631 CREATE TABLE `special_holidays` (
1632 `id` int(11) NOT NULL auto_increment,
1633 `branchcode` varchar(10) NOT NULL default '',
1634 `day` smallint(6) NOT NULL default 0,
1635 `month` smallint(6) NOT NULL default 0,
1636 `year` smallint(6) NOT NULL default 0,
1637 `isexception` smallint(1) NOT NULL default 1,
1638 `title` varchar(50) NOT NULL default '',
1639 `description` text NOT NULL,
1641 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1644 -- Table structure for table `statistics`
1647 DROP TABLE IF EXISTS `statistics`;
1648 CREATE TABLE `statistics` (
1649 `datetime` datetime default NULL,
1650 `branch` varchar(10) default NULL,
1651 `proccode` varchar(4) default NULL,
1652 `value` double(16,4) default NULL,
1653 `type` varchar(16) default NULL,
1655 `usercode` varchar(10) default NULL,
1656 `itemnumber` int(11) default NULL,
1657 `itemtype` varchar(10) default NULL,
1658 `borrowernumber` int(11) default NULL,
1659 `associatedborrower` int(11) default NULL,
1660 KEY `timeidx` (`datetime`)
1661 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1664 -- Table structure for table `stopwords`
1667 DROP TABLE IF EXISTS `stopwords`;
1668 CREATE TABLE `stopwords` (
1669 `word` varchar(255) default NULL
1670 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1673 -- Table structure for table `subscription`
1676 DROP TABLE IF EXISTS `subscription`;
1677 CREATE TABLE `subscription` (
1678 `biblionumber` int(11) NOT NULL default 0,
1679 `subscriptionid` int(11) NOT NULL auto_increment,
1680 `librarian` varchar(100) default '',
1681 `startdate` date default NULL,
1682 `aqbooksellerid` int(11) default 0,
1683 `cost` int(11) default 0,
1684 `aqbudgetid` int(11) default 0,
1685 `weeklength` int(11) default 0,
1686 `monthlength` int(11) default 0,
1687 `numberlength` int(11) default 0,
1688 `periodicity` tinyint(4) default 0,
1689 `dow` varchar(100) default '',
1690 `numberingmethod` varchar(100) default '',
1692 `status` varchar(100) NOT NULL default '',
1693 `add1` int(11) default 0,
1694 `every1` int(11) default 0,
1695 `whenmorethan1` int(11) default 0,
1696 `setto1` int(11) default NULL,
1697 `lastvalue1` int(11) default NULL,
1698 `add2` int(11) default 0,
1699 `every2` int(11) default 0,
1700 `whenmorethan2` int(11) default 0,
1701 `setto2` int(11) default NULL,
1702 `lastvalue2` int(11) default NULL,
1703 `add3` int(11) default 0,
1704 `every3` int(11) default 0,
1705 `innerloop1` int(11) default 0,
1706 `innerloop2` int(11) default 0,
1707 `innerloop3` int(11) default 0,
1708 `whenmorethan3` int(11) default 0,
1709 `setto3` int(11) default NULL,
1710 `lastvalue3` int(11) default NULL,
1711 `issuesatonce` tinyint(3) NOT NULL default 1,
1712 `firstacquidate` date default NULL,
1713 `manualhistory` tinyint(1) NOT NULL default 0,
1714 `irregularity` text,
1715 `letter` varchar(20) default NULL,
1716 `numberpattern` tinyint(3) default 0,
1717 `distributedto` text,
1718 `internalnotes` longtext,
1720 `location` varchar(80) NULL default '',
1721 `branchcode` varchar(10) NOT NULL default '',
1722 `hemisphere` tinyint(3) default 0,
1723 `lastbranch` varchar(10),
1724 `serialsadditems` tinyint(1) NOT NULL default '0',
1725 `staffdisplaycount` VARCHAR(10) NULL,
1726 `opacdisplaycount` VARCHAR(10) NULL,
1727 `graceperiod` int(11) NOT NULL default '0',
1728 PRIMARY KEY (`subscriptionid`)
1729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1732 -- Table structure for table `subscriptionhistory`
1735 DROP TABLE IF EXISTS `subscriptionhistory`;
1736 CREATE TABLE `subscriptionhistory` (
1737 `biblionumber` int(11) NOT NULL default 0,
1738 `subscriptionid` int(11) NOT NULL default 0,
1739 `histstartdate` date default NULL,
1740 `histenddate` date default NULL,
1741 `missinglist` longtext NOT NULL,
1742 `recievedlist` longtext NOT NULL,
1743 `opacnote` varchar(150) NOT NULL default '',
1744 `librariannote` varchar(150) NOT NULL default '',
1745 PRIMARY KEY (`subscriptionid`),
1746 KEY `biblionumber` (`biblionumber`)
1747 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1750 -- Table structure for table `subscriptionroutinglist`
1753 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1754 CREATE TABLE `subscriptionroutinglist` (
1755 `routingid` int(11) NOT NULL auto_increment,
1756 `borrowernumber` int(11) default NULL,
1757 `ranking` int(11) default NULL,
1758 `subscriptionid` int(11) default NULL,
1759 PRIMARY KEY (`routingid`)
1760 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1763 -- Table structure for table `suggestions`
1766 DROP TABLE IF EXISTS `suggestions`;
1767 CREATE TABLE `suggestions` (
1768 `suggestionid` int(8) NOT NULL auto_increment,
1769 `suggestedby` int(11) NOT NULL default 0,
1770 `suggesteddate` date NOT NULL default 0,
1771 `managedby` int(11) default NULL,
1772 `manageddate` date default NULL,
1773 acceptedby INT(11) default NULL,
1774 accepteddate date default NULL,
1775 rejectedby INT(11) default NULL,
1776 rejecteddate date default NULL,
1777 `STATUS` varchar(10) NOT NULL default '',
1779 `author` varchar(80) default NULL,
1780 `title` varchar(80) default NULL,
1781 `copyrightdate` smallint(6) default NULL,
1782 `publishercode` varchar(255) default NULL,
1783 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1784 `volumedesc` varchar(255) default NULL,
1785 `publicationyear` smallint(6) default 0,
1786 `place` varchar(255) default NULL,
1787 `isbn` varchar(30) default NULL,
1788 `mailoverseeing` smallint(1) default 0,
1789 `biblionumber` int(11) default NULL,
1792 branchcode VARCHAR(10) default NULL,
1793 collectiontitle text default NULL,
1794 itemtype VARCHAR(30) default NULL,
1795 PRIMARY KEY (`suggestionid`),
1796 KEY `suggestedby` (`suggestedby`),
1797 KEY `managedby` (`managedby`)
1798 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1801 -- Table structure for table `systempreferences`
1804 DROP TABLE IF EXISTS `systempreferences`;
1805 CREATE TABLE `systempreferences` (
1806 `variable` varchar(50) NOT NULL default '',
1808 `options` mediumtext,
1810 `type` varchar(20) default NULL,
1811 PRIMARY KEY (`variable`)
1812 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1815 -- Table structure for table `tags`
1818 DROP TABLE IF EXISTS `tags`;
1819 CREATE TABLE `tags` (
1820 `entry` varchar(255) NOT NULL default '',
1821 `weight` bigint(20) NOT NULL default 0,
1822 PRIMARY KEY (`entry`)
1823 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1826 -- Table structure for table `tags_all`
1829 DROP TABLE IF EXISTS `tags_all`;
1830 CREATE TABLE `tags_all` (
1831 `tag_id` int(11) NOT NULL auto_increment,
1832 `borrowernumber` int(11) NOT NULL,
1833 `biblionumber` int(11) NOT NULL,
1834 `term` varchar(255) NOT NULL,
1835 `language` int(4) default NULL,
1836 `date_created` datetime NOT NULL,
1837 PRIMARY KEY (`tag_id`),
1838 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1839 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1840 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1841 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1842 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1843 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1844 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1847 -- Table structure for table `tags_approval`
1850 DROP TABLE IF EXISTS `tags_approval`;
1851 CREATE TABLE `tags_approval` (
1852 `term` varchar(255) NOT NULL,
1853 `approved` int(1) NOT NULL default '0',
1854 `date_approved` datetime default NULL,
1855 `approved_by` int(11) default NULL,
1856 `weight_total` int(9) NOT NULL default '1',
1857 PRIMARY KEY (`term`),
1858 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1859 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1860 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1861 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1864 -- Table structure for table `tags_index`
1867 DROP TABLE IF EXISTS `tags_index`;
1868 CREATE TABLE `tags_index` (
1869 `term` varchar(255) NOT NULL,
1870 `biblionumber` int(11) NOT NULL,
1871 `weight` int(9) NOT NULL default '1',
1872 PRIMARY KEY (`term`,`biblionumber`),
1873 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1874 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1875 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1876 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1877 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1878 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1881 -- Table structure for table `userflags`
1884 DROP TABLE IF EXISTS `userflags`;
1885 CREATE TABLE `userflags` (
1886 `bit` int(11) NOT NULL default 0,
1887 `flag` varchar(30) default NULL,
1888 `flagdesc` varchar(255) default NULL,
1889 `defaulton` int(11) default NULL,
1891 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1894 -- Table structure for table `virtualshelves`
1897 DROP TABLE IF EXISTS `virtualshelves`;
1898 CREATE TABLE `virtualshelves` (
1899 `shelfnumber` int(11) NOT NULL auto_increment,
1900 `shelfname` varchar(255) default NULL,
1901 `owner` varchar(80) default NULL,
1902 `category` varchar(1) default NULL,
1903 `sortfield` varchar(16) default NULL,
1904 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1905 PRIMARY KEY (`shelfnumber`)
1906 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1909 -- Table structure for table `virtualshelfcontents`
1912 DROP TABLE IF EXISTS `virtualshelfcontents`;
1913 CREATE TABLE `virtualshelfcontents` (
1914 `shelfnumber` int(11) NOT NULL default 0,
1915 `biblionumber` int(11) NOT NULL default 0,
1916 `flags` int(11) default NULL,
1917 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1918 KEY `shelfnumber` (`shelfnumber`),
1919 KEY `biblionumber` (`biblionumber`),
1920 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1921 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1922 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1925 -- Table structure for table `z3950servers`
1928 DROP TABLE IF EXISTS `z3950servers`;
1929 CREATE TABLE `z3950servers` (
1930 `host` varchar(255) default NULL,
1931 `port` int(11) default NULL,
1932 `db` varchar(255) default NULL,
1933 `userid` varchar(255) default NULL,
1934 `password` varchar(255) default NULL,
1936 `id` int(11) NOT NULL auto_increment,
1937 `checked` smallint(6) default NULL,
1938 `rank` int(11) default NULL,
1939 `syntax` varchar(80) default NULL,
1941 `position` enum('primary','secondary','') NOT NULL default 'primary',
1942 `type` enum('zed','opensearch') NOT NULL default 'zed',
1943 `encoding` text default NULL,
1944 `description` text NOT NULL,
1946 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1949 -- Table structure for table `zebraqueue`
1952 DROP TABLE IF EXISTS `zebraqueue`;
1953 CREATE TABLE `zebraqueue` (
1954 `id` int(11) NOT NULL auto_increment,
1955 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1956 `operation` char(20) NOT NULL default '',
1957 `server` char(20) NOT NULL default '',
1958 `done` int(11) NOT NULL default '0',
1959 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1961 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1962 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1964 DROP TABLE IF EXISTS `services_throttle`;
1965 CREATE TABLE `services_throttle` (
1966 `service_type` varchar(10) NOT NULL default '',
1967 `service_count` varchar(45) default NULL,
1968 PRIMARY KEY (`service_type`)
1969 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1971 -- http://www.w3.org/International/articles/language-tags/
1974 DROP TABLE IF EXISTS language_subtag_registry;
1975 CREATE TABLE language_subtag_registry (
1977 type varchar(25), -- language-script-region-variant-extension-privateuse
1978 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1980 id int(11) NOT NULL auto_increment,
1982 KEY `subtag` (`subtag`)
1983 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1985 -- TODO: add suppress_scripts
1986 -- this maps three letter codes defined in iso639.2 back to their
1987 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1988 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1989 CREATE TABLE language_rfc4646_to_iso639 (
1990 rfc4646_subtag varchar(25),
1991 iso639_2_code varchar(25),
1992 id int(11) NOT NULL auto_increment,
1994 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1995 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1997 DROP TABLE IF EXISTS language_descriptions;
1998 CREATE TABLE language_descriptions (
2002 description varchar(255),
2003 id int(11) NOT NULL auto_increment,
2006 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2008 -- bi-directional support, keyed by script subcode
2009 DROP TABLE IF EXISTS language_script_bidi;
2010 CREATE TABLE language_script_bidi (
2011 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2012 bidi varchar(3), -- rtl ltr
2013 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2016 -- TODO: need to map language subtags to script subtags for detection
2017 -- of bidi when script is not specified (like ar, he)
2018 DROP TABLE IF EXISTS language_script_mapping;
2019 CREATE TABLE language_script_mapping (
2020 language_subtag varchar(25),
2021 script_subtag varchar(25),
2022 KEY `language_subtag` (`language_subtag`)
2023 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2025 DROP TABLE IF EXISTS `permissions`;
2026 CREATE TABLE `permissions` (
2027 `module_bit` int(11) NOT NULL DEFAULT 0,
2028 `code` varchar(64) DEFAULT NULL,
2029 `description` varchar(255) DEFAULT NULL,
2030 PRIMARY KEY (`module_bit`, `code`),
2031 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2032 ON DELETE CASCADE ON UPDATE CASCADE
2033 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2035 DROP TABLE IF EXISTS `serialitems`;
2036 CREATE TABLE `serialitems` (
2037 `itemnumber` int(11) NOT NULL,
2038 `serialid` int(11) NOT NULL,
2039 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2040 KEY `serialitems_sfk_1` (`serialid`),
2041 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2042 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2044 DROP TABLE IF EXISTS `user_permissions`;
2045 CREATE TABLE `user_permissions` (
2046 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2047 `module_bit` int(11) NOT NULL DEFAULT 0,
2048 `code` varchar(64) DEFAULT NULL,
2049 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2050 ON DELETE CASCADE ON UPDATE CASCADE,
2051 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2052 ON DELETE CASCADE ON UPDATE CASCADE
2053 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2056 -- Table structure for table `tmp_holdsqueue`
2059 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2060 CREATE TABLE `tmp_holdsqueue` (
2061 `biblionumber` int(11) default NULL,
2062 `itemnumber` int(11) default NULL,
2063 `barcode` varchar(20) default NULL,
2064 `surname` mediumtext NOT NULL,
2067 `borrowernumber` int(11) NOT NULL,
2068 `cardnumber` varchar(16) default NULL,
2069 `reservedate` date default NULL,
2071 `itemcallnumber` varchar(255) default NULL,
2072 `holdingbranch` varchar(10) default NULL,
2073 `pickbranch` varchar(10) default NULL,
2075 `item_level_request` tinyint(4) NOT NULL default 0
2076 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2079 -- Table structure for table `message_queue`
2082 DROP TABLE IF EXISTS `message_queue`;
2083 CREATE TABLE `message_queue` (
2084 `message_id` int(11) NOT NULL auto_increment,
2085 `borrowernumber` int(11) default NULL,
2088 `metadata` text DEFAULT NULL,
2089 `letter_code` varchar(64) DEFAULT NULL,
2090 `message_transport_type` varchar(20) NOT NULL,
2091 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2092 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2093 `to_address` mediumtext,
2094 `from_address` mediumtext,
2095 `content_type` text,
2096 KEY `message_id` (`message_id`),
2097 KEY `borrowernumber` (`borrowernumber`),
2098 KEY `message_transport_type` (`message_transport_type`),
2099 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2100 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2101 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2104 -- Table structure for table `message_transport_types`
2107 DROP TABLE IF EXISTS `message_transport_types`;
2108 CREATE TABLE `message_transport_types` (
2109 `message_transport_type` varchar(20) NOT NULL,
2110 PRIMARY KEY (`message_transport_type`)
2111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2114 -- Table structure for table `message_attributes`
2117 DROP TABLE IF EXISTS `message_attributes`;
2118 CREATE TABLE `message_attributes` (
2119 `message_attribute_id` int(11) NOT NULL auto_increment,
2120 `message_name` varchar(20) NOT NULL default '',
2121 `takes_days` tinyint(1) NOT NULL default '0',
2122 PRIMARY KEY (`message_attribute_id`),
2123 UNIQUE KEY `message_name` (`message_name`)
2124 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2127 -- Table structure for table `message_transports`
2130 DROP TABLE IF EXISTS `message_transports`;
2131 CREATE TABLE `message_transports` (
2132 `message_attribute_id` int(11) NOT NULL,
2133 `message_transport_type` varchar(20) NOT NULL,
2134 `is_digest` tinyint(1) NOT NULL default '0',
2135 `letter_module` varchar(20) NOT NULL default '',
2136 `letter_code` varchar(20) NOT NULL default '',
2137 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2138 KEY `message_transport_type` (`message_transport_type`),
2139 KEY `letter_module` (`letter_module`,`letter_code`),
2140 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2141 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2142 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2143 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2146 -- Table structure for table `borrower_message_preferences`
2149 DROP TABLE IF EXISTS `borrower_message_preferences`;
2150 CREATE TABLE `borrower_message_preferences` (
2151 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2152 `borrowernumber` int(11) default NULL,
2153 `categorycode` varchar(10) default NULL,
2154 `message_attribute_id` int(11) default '0',
2155 `days_in_advance` int(11) default '0',
2156 `wants_digest` tinyint(1) NOT NULL default '0',
2157 PRIMARY KEY (`borrower_message_preference_id`),
2158 KEY `borrowernumber` (`borrowernumber`),
2159 KEY `categorycode` (`categorycode`),
2160 KEY `message_attribute_id` (`message_attribute_id`),
2161 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2162 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2163 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2164 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2167 -- Table structure for table `borrower_message_transport_preferences`
2170 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2171 CREATE TABLE `borrower_message_transport_preferences` (
2172 `borrower_message_preference_id` int(11) NOT NULL default '0',
2173 `message_transport_type` varchar(20) NOT NULL default '0',
2174 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2175 KEY `message_transport_type` (`message_transport_type`),
2176 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,
2177 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
2178 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2181 -- Table structure for the table branch_transfer_limits
2184 DROP TABLE IF EXISTS `branch_transfer_limits`;
2185 CREATE TABLE branch_transfer_limits (
2186 limitId int(8) NOT NULL auto_increment,
2187 toBranch varchar(10) NOT NULL,
2188 fromBranch varchar(10) NOT NULL,
2189 itemtype varchar(10) NULL,
2190 ccode varchar(10) NULL,
2191 PRIMARY KEY (limitId)
2192 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2195 -- Table structure for table `item_circulation_alert_preferences`
2198 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2199 CREATE TABLE `item_circulation_alert_preferences` (
2200 `id` int(11) NOT NULL auto_increment,
2201 `branchcode` varchar(10) NOT NULL,
2202 `categorycode` varchar(10) NOT NULL,
2203 `item_type` varchar(10) NOT NULL,
2204 `notification` varchar(16) NOT NULL,
2206 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2210 -- Table structure for table `messages`
2213 CREATE TABLE `messages` (
2214 `message_id` int(11) NOT NULL auto_increment,
2215 `borrowernumber` int(11) NOT NULL,
2216 `branchcode` varchar(4) default NULL,
2217 `message_type` varchar(1) NOT NULL,
2218 `message` text NOT NULL,
2219 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2220 PRIMARY KEY (`message_id`)
2221 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2224 -- Table structure for table `accountlines`
2227 DROP TABLE IF EXISTS `accountlines`;
2228 CREATE TABLE `accountlines` (
2229 `borrowernumber` int(11) NOT NULL default 0,
2230 `accountno` smallint(6) NOT NULL default 0,
2231 `itemnumber` int(11) default NULL,
2232 `date` date default NULL,
2233 `amount` decimal(28,6) default NULL,
2234 `description` mediumtext,
2235 `dispute` mediumtext,
2236 `accounttype` varchar(5) default NULL,
2237 `amountoutstanding` decimal(28,6) default NULL,
2238 `lastincrement` decimal(28,6) default NULL,
2239 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2240 `notify_id` int(11) NOT NULL default 0,
2241 `notify_level` int(2) NOT NULL default 0,
2242 KEY `acctsborridx` (`borrowernumber`),
2243 KEY `timeidx` (`timestamp`),
2244 KEY `itemnumber` (`itemnumber`),
2245 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2246 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2247 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2250 -- Table structure for table `accountoffsets`
2253 DROP TABLE IF EXISTS `accountoffsets`;
2254 CREATE TABLE `accountoffsets` (
2255 `borrowernumber` int(11) NOT NULL default 0,
2256 `accountno` smallint(6) NOT NULL default 0,
2257 `offsetaccount` smallint(6) NOT NULL default 0,
2258 `offsetamount` decimal(28,6) default NULL,
2259 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2260 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2261 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2264 -- Table structure for table `action_logs`
2267 DROP TABLE IF EXISTS `action_logs`;
2268 CREATE TABLE `action_logs` (
2269 `action_id` int(11) NOT NULL auto_increment,
2270 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2271 `user` int(11) NOT NULL default 0,
2274 `object` int(11) default NULL,
2276 PRIMARY KEY (`action_id`),
2277 KEY (`timestamp`,`user`)
2278 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2281 -- Table structure for table `alert`
2284 DROP TABLE IF EXISTS `alert`;
2285 CREATE TABLE `alert` (
2286 `alertid` int(11) NOT NULL auto_increment,
2287 `borrowernumber` int(11) NOT NULL default 0,
2288 `type` varchar(10) NOT NULL default '',
2289 `externalid` varchar(20) NOT NULL default '',
2290 PRIMARY KEY (`alertid`),
2291 KEY `borrowernumber` (`borrowernumber`),
2292 KEY `type` (`type`,`externalid`)
2293 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2296 -- Table structure for table `aqbasketgroups`
2299 DROP TABLE IF EXISTS `aqbasketgroups`;
2300 CREATE TABLE `aqbasketgroups` (
2301 `id` int(11) NOT NULL auto_increment,
2302 `name` varchar(50) default NULL,
2303 `closed` tinyint(1) default NULL,
2304 `booksellerid` int(11) NOT NULL,
2306 KEY `booksellerid` (`booksellerid`),
2307 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2308 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2311 -- Table structure for table `aqbasket`
2314 DROP TABLE IF EXISTS `aqbasket`;
2315 CREATE TABLE `aqbasket` (
2316 `basketno` int(11) NOT NULL auto_increment,
2317 `basketname` varchar(50) default NULL,
2319 `booksellernote` mediumtext,
2320 `contractnumber` int(11),
2321 `creationdate` date default NULL,
2322 `closedate` date default NULL,
2323 `booksellerid` int(11) NOT NULL default 1,
2324 `authorisedby` varchar(10) default NULL,
2325 `booksellerinvoicenumber` mediumtext,
2326 `basketgroupid` int(11),
2327 PRIMARY KEY (`basketno`),
2328 KEY `booksellerid` (`booksellerid`),
2329 KEY `basketgroupid` (`basketgroupid`),
2330 KEY `contractnumber` (`contractnumber`),
2331 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2332 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2333 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2334 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2337 -- Table structure for table `aqbooksellers`
2340 DROP TABLE IF EXISTS `aqbooksellers`;
2341 CREATE TABLE `aqbooksellers` (
2342 `id` int(11) NOT NULL auto_increment,
2343 `name` mediumtext NOT NULL,
2344 `address1` mediumtext,
2345 `address2` mediumtext,
2346 `address3` mediumtext,
2347 `address4` mediumtext,
2348 `phone` varchar(30) default NULL,
2349 `accountnumber` mediumtext,
2350 `othersupplier` mediumtext,
2351 `currency` varchar(3) NOT NULL default '',
2352 `booksellerfax` mediumtext,
2354 `bookselleremail` mediumtext,
2355 `booksellerurl` mediumtext,
2356 `contact` varchar(100) default NULL,
2357 `postal` mediumtext,
2358 `url` varchar(255) default NULL,
2359 `contpos` varchar(100) default NULL,
2360 `contphone` varchar(100) default NULL,
2361 `contfax` varchar(100) default NULL,
2362 `contaltphone` varchar(100) default NULL,
2363 `contemail` varchar(100) default NULL,
2364 `contnotes` mediumtext,
2365 `active` tinyint(4) default NULL,
2366 `listprice` varchar(10) default NULL,
2367 `invoiceprice` varchar(10) default NULL,
2368 `gstreg` tinyint(4) default NULL,
2369 `listincgst` tinyint(4) default NULL,
2370 `invoiceincgst` tinyint(4) default NULL,
2371 `gstrate` decimal(6,4) default NULL,
2372 `discount` float(6,4) default NULL,
2373 `fax` varchar(50) default NULL,
2375 KEY `listprice` (`listprice`),
2376 KEY `invoiceprice` (`invoiceprice`),
2377 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2378 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2382 -- Table structure for table `aqbudgets`
2385 DROP TABLE IF EXISTS `aqbudgets`;
2386 CREATE TABLE `aqbudgets` (
2387 `budget_id` int(11) NOT NULL auto_increment,
2388 `budget_parent_id` int(11) default NULL,
2389 `budget_code` varchar(30) default NULL,
2390 `budget_name` varchar(80) default NULL,
2391 `budget_branchcode` varchar(10) default NULL,
2392 `budget_amount` decimal(28,6) NULL default '0.00',
2393 `budget_encumb` decimal(28,6) NULL default '0.00',
2394 `budget_expend` decimal(28,6) NULL default '0.00',
2395 `budget_notes` mediumtext,
2396 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2397 `budget_period_id` int(11) default NULL,
2398 `sort1_authcat` varchar(80) default NULL,
2399 `sort2_authcat` varchar(80) default NULL,
2400 `budget_owner_id` int(11) default NULL,
2401 `budget_permission` int(1) default '0',
2402 PRIMARY KEY (`budget_id`)
2403 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2407 -- Table structure for table `aqbudgetperiods`
2411 DROP TABLE IF EXISTS `aqbudgetperiods`;
2412 CREATE TABLE `aqbudgetperiods` (
2413 `budget_period_id` int(11) NOT NULL auto_increment,
2414 `budget_period_startdate` date NOT NULL,
2415 `budget_period_enddate` date NOT NULL,
2416 `budget_period_active` tinyint(1) default '0',
2417 `budget_period_description` mediumtext,
2418 `budget_period_total` decimal(28,6),
2419 `budget_period_locked` tinyint(1) default NULL,
2420 `sort1_authcat` varchar(10) default NULL,
2421 `sort2_authcat` varchar(10) default NULL,
2422 PRIMARY KEY (`budget_period_id`)
2423 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2426 -- Table structure for table `aqbudgets_planning`
2429 DROP TABLE IF EXISTS `aqbudgets_planning`;
2430 CREATE TABLE `aqbudgets_planning` (
2431 `plan_id` int(11) NOT NULL auto_increment,
2432 `budget_id` int(11) NOT NULL,
2433 `budget_period_id` int(11) NOT NULL,
2434 `estimated_amount` decimal(28,6) default NULL,
2435 `authcat` varchar(30) NOT NULL,
2436 `authvalue` varchar(30) NOT NULL,
2437 PRIMARY KEY (`plan_id`),
2438 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2439 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2442 -- Table structure for table 'aqcontract'
2445 DROP TABLE IF EXISTS `aqcontract`;
2446 CREATE TABLE `aqcontract` (
2447 `contractnumber` int(11) NOT NULL auto_increment,
2448 `contractstartdate` date default NULL,
2449 `contractenddate` date default NULL,
2450 `contractname` varchar(50) default NULL,
2451 `contractdescription` mediumtext,
2452 `booksellerid` int(11) not NULL,
2453 PRIMARY KEY (`contractnumber`),
2454 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2455 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2456 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2459 -- Table structure for table `aqorderdelivery`
2462 DROP TABLE IF EXISTS `aqorderdelivery`;
2463 CREATE TABLE `aqorderdelivery` (
2464 `ordernumber` date default NULL,
2465 `deliverynumber` smallint(6) NOT NULL default 0,
2466 `deliverydate` varchar(18) default NULL,
2467 `qtydelivered` smallint(6) default NULL,
2468 `deliverycomments` mediumtext
2469 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2472 -- Table structure for table `aqorders`
2475 DROP TABLE IF EXISTS `aqorders`;
2476 CREATE TABLE `aqorders` (
2477 `ordernumber` int(11) NOT NULL auto_increment,
2478 `biblionumber` int(11) default NULL,
2479 `entrydate` date default NULL,
2480 `quantity` smallint(6) default NULL,
2481 `currency` varchar(3) default NULL,
2482 `listprice` decimal(28,6) default NULL,
2483 `totalamount` decimal(28,6) default NULL,
2484 `datereceived` date default NULL,
2485 `booksellerinvoicenumber` mediumtext,
2486 `freight` decimal(28,6) default NULL,
2487 `unitprice` decimal(28,6) default NULL,
2488 `quantityreceived` smallint(6) default NULL,
2489 `cancelledby` varchar(10) default NULL,
2490 `datecancellationprinted` date default NULL,
2492 `supplierreference` mediumtext,
2493 `purchaseordernumber` mediumtext,
2494 `subscription` tinyint(1) default NULL,
2495 `serialid` varchar(30) default NULL,
2496 `basketno` int(11) default NULL,
2497 `biblioitemnumber` int(11) default NULL,
2498 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2499 `rrp` decimal(13,2) default NULL,
2500 `ecost` decimal(13,2) default NULL,
2501 `gst` decimal(13,2) default NULL,
2502 `budget_id` int(11) NOT NULL,
2503 `budgetgroup_id` int(11) NOT NULL,
2504 `budgetdate` date default NULL,
2505 `sort1` varchar(80) default NULL,
2506 `sort2` varchar(80) default NULL,
2507 `sort1_authcat` varchar(10) default NULL,
2508 `sort2_authcat` varchar(10) default NULL,
2509 `uncertainprice` tinyint(1),
2510 PRIMARY KEY (`ordernumber`),
2511 KEY `basketno` (`basketno`),
2512 KEY `biblionumber` (`biblionumber`),
2513 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2514 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2515 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2518 -- Table structure for table `aqorders_items`
2521 DROP TABLE IF EXISTS `aqorders_items`;
2522 CREATE TABLE `aqorders_items` (
2523 `ordernumber` int(11) NOT NULL,
2524 `itemnumber` int(11) NOT NULL,
2525 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2526 PRIMARY KEY (`itemnumber`),
2527 KEY `ordernumber` (`ordernumber`)
2528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2531 -- Table structure for table `fieldmapping`
2534 DROP TABLE IF EXISTS `fieldmapping`;
2535 CREATE TABLE `fieldmapping` (
2536 `id` int(11) NOT NULL auto_increment,
2537 `field` varchar(255) NOT NULL,
2538 `frameworkcode` char(4) NOT NULL default '',
2539 `fieldcode` char(3) NOT NULL,
2540 `subfieldcode` char(1) NOT NULL,
2542 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2545 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2546 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2547 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2548 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2549 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2550 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2551 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2552 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;