3 -- Host: localhost Database: koha30test
4 -- ------------------------------------------------------
5 -- Server version 4.1.22
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
11 /*!40101 SET NAMES utf8 */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19 -- Table structure for table `auth_header`
22 DROP TABLE IF EXISTS `auth_header`;
23 CREATE TABLE `auth_header` (
24 `authid` bigint(20) unsigned NOT NULL auto_increment,
25 `authtypecode` varchar(10) NOT NULL default '',
26 `datecreated` date default NULL,
27 `datemodified` date default NULL,
28 `origincode` varchar(20) default NULL,
29 `authtrees` mediumtext,
31 `linkid` bigint(20) default NULL,
32 `marcxml` longtext NOT NULL,
33 PRIMARY KEY (`authid`),
34 KEY `origincode` (`origincode`)
35 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
38 -- Table structure for table `auth_subfield_structure`
41 DROP TABLE IF EXISTS `auth_subfield_structure`;
42 CREATE TABLE `auth_subfield_structure` (
43 `authtypecode` varchar(10) NOT NULL default '',
44 `tagfield` varchar(3) NOT NULL default '',
45 `tagsubfield` varchar(1) NOT NULL default '',
46 `liblibrarian` varchar(255) NOT NULL default '',
47 `libopac` varchar(255) NOT NULL default '',
48 `repeatable` tinyint(4) NOT NULL default 0,
49 `mandatory` tinyint(4) NOT NULL default 0,
50 `tab` tinyint(1) default NULL,
51 `authorised_value` varchar(10) default NULL,
52 `value_builder` varchar(80) default NULL,
53 `seealso` varchar(255) default NULL,
54 `isurl` tinyint(1) default NULL,
55 `hidden` tinyint(3) NOT NULL default 0,
56 `linkid` tinyint(1) NOT NULL default 0,
57 `kohafield` varchar(45) NULL default '',
58 `frameworkcode` varchar(8) NOT NULL default '',
59 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
60 KEY `tab` (`authtypecode`,`tab`)
61 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
64 -- Table structure for table `auth_tag_structure`
67 DROP TABLE IF EXISTS `auth_tag_structure`;
68 CREATE TABLE `auth_tag_structure` (
69 `authtypecode` varchar(10) NOT NULL default '',
70 `tagfield` varchar(3) NOT NULL default '',
71 `liblibrarian` varchar(255) NOT NULL default '',
72 `libopac` varchar(255) NOT NULL default '',
73 `repeatable` tinyint(4) NOT NULL default 0,
74 `mandatory` tinyint(4) NOT NULL default 0,
75 `authorised_value` varchar(10) default NULL,
76 PRIMARY KEY (`authtypecode`,`tagfield`),
77 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
78 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
81 -- Table structure for table `auth_types`
84 DROP TABLE IF EXISTS `auth_types`;
85 CREATE TABLE `auth_types` (
86 `authtypecode` varchar(10) NOT NULL default '',
87 `authtypetext` varchar(255) NOT NULL default '',
88 `auth_tag_to_report` varchar(3) NOT NULL default '',
89 `summary` mediumtext NOT NULL,
90 PRIMARY KEY (`authtypecode`)
91 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
94 -- Table structure for table `authorised_values`
97 DROP TABLE IF EXISTS `authorised_values`;
98 CREATE TABLE `authorised_values` (
99 `id` int(11) NOT NULL auto_increment,
100 `category` varchar(10) NOT NULL default '',
101 `authorised_value` varchar(80) NOT NULL default '',
102 `lib` varchar(80) default NULL,
103 `imageurl` varchar(200) default NULL,
105 KEY `name` (`category`),
107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
110 -- Table structure for table `biblio`
113 DROP TABLE IF EXISTS `biblio`;
114 CREATE TABLE `biblio` (
115 `biblionumber` int(11) NOT NULL auto_increment,
116 `frameworkcode` varchar(4) NOT NULL default '',
119 `unititle` mediumtext,
121 `serial` tinyint(1) default NULL,
122 `seriestitle` mediumtext,
123 `copyrightdate` smallint(6) default NULL,
124 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
125 `datecreated` DATE NOT NULL,
126 `abstract` mediumtext,
127 PRIMARY KEY (`biblionumber`),
128 KEY `blbnoidx` (`biblionumber`)
129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
132 -- Table structure for table `biblio_framework`
135 DROP TABLE IF EXISTS `biblio_framework`;
136 CREATE TABLE `biblio_framework` (
137 `frameworkcode` varchar(4) NOT NULL default '',
138 `frameworktext` varchar(255) NOT NULL default '',
139 PRIMARY KEY (`frameworkcode`)
140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
143 -- Table structure for table `biblioitems`
146 DROP TABLE IF EXISTS `biblioitems`;
147 CREATE TABLE `biblioitems` (
148 `biblioitemnumber` int(11) NOT NULL auto_increment,
149 `biblionumber` int(11) NOT NULL default 0,
152 `itemtype` varchar(10) default NULL,
153 `isbn` varchar(30) default NULL,
154 `issn` varchar(9) default NULL,
155 `publicationyear` text,
156 `publishercode` varchar(255) default NULL,
157 `volumedate` date default NULL,
159 `collectiontitle` mediumtext default NULL,
160 `collectionissn` text default NULL,
161 `collectionvolume` mediumtext default NULL,
162 `editionstatement` text default NULL,
163 `editionresponsibility` text default NULL,
164 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
165 `illus` varchar(255) default NULL,
166 `pages` varchar(255) default NULL,
168 `size` varchar(255) default NULL,
169 `place` varchar(255) default NULL,
170 `lccn` varchar(25) default NULL,
172 `url` varchar(255) default NULL,
173 `cn_source` varchar(10) default NULL,
174 `cn_class` varchar(30) default NULL,
175 `cn_item` varchar(10) default NULL,
176 `cn_suffix` varchar(10) default NULL,
177 `cn_sort` varchar(30) default NULL,
178 `totalissues` int(10),
179 `marcxml` longtext NOT NULL,
180 PRIMARY KEY (`biblioitemnumber`),
181 KEY `bibinoidx` (`biblioitemnumber`),
182 KEY `bibnoidx` (`biblionumber`),
184 KEY `publishercode` (`publishercode`),
185 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
186 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
189 -- Table structure for table `borrowers`
192 DROP TABLE IF EXISTS `borrowers`;
193 CREATE TABLE `borrowers` (
194 `borrowernumber` int(11) NOT NULL auto_increment,
195 `cardnumber` varchar(16) default NULL,
196 `surname` mediumtext NOT NULL,
199 `othernames` mediumtext,
201 `streetnumber` varchar(10) default NULL,
202 `streettype` varchar(50) default NULL,
203 `address` mediumtext NOT NULL,
205 `city` mediumtext NOT NULL,
206 `zipcode` varchar(25) default NULL,
210 `mobile` varchar(50) default NULL,
214 `B_streetnumber` varchar(10) default NULL,
215 `B_streettype` varchar(50) default NULL,
216 `B_address` varchar(100) default NULL,
217 `B_address2` text default NULL,
219 `B_zipcode` varchar(25) default NULL,
222 `B_phone` mediumtext,
223 `dateofbirth` date default NULL,
224 `branchcode` varchar(10) NOT NULL default '',
225 `categorycode` varchar(10) NOT NULL default '',
226 `dateenrolled` date default NULL,
227 `dateexpiry` date default NULL,
228 `gonenoaddress` tinyint(1) default NULL,
229 `lost` tinyint(1) default NULL,
230 `debarred` tinyint(1) default NULL,
231 `contactname` mediumtext,
232 `contactfirstname` text,
234 `guarantorid` int(11) default NULL,
235 `borrowernotes` mediumtext,
236 `relationship` varchar(100) default NULL,
237 `ethnicity` varchar(50) default NULL,
238 `ethnotes` varchar(255) default NULL,
239 `sex` varchar(1) default NULL,
240 `password` varchar(30) default NULL,
241 `flags` int(11) default NULL,
242 `userid` varchar(30) default NULL,
243 `opacnote` mediumtext,
244 `contactnote` varchar(255) default NULL,
245 `sort1` varchar(80) default NULL,
246 `sort2` varchar(80) default NULL,
247 `altcontactfirstname` varchar(255) default NULL,
248 `altcontactsurname` varchar(255) default NULL,
249 `altcontactaddress1` varchar(255) default NULL,
250 `altcontactaddress2` varchar(255) default NULL,
251 `altcontactaddress3` varchar(255) default NULL,
252 `altcontactzipcode` varchar(50) default NULL,
253 `altcontactcountry` text default NULL,
254 `altcontactphone` varchar(50) default NULL,
255 `smsalertnumber` varchar(50) default NULL,
256 UNIQUE KEY `cardnumber` (`cardnumber`),
257 PRIMARY KEY `borrowernumber` (`borrowernumber`),
258 KEY `categorycode` (`categorycode`),
259 KEY `branchcode` (`branchcode`),
260 KEY `userid` (`userid`),
261 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
262 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
266 -- Table structure for table `borrower_attribute_types`
269 DROP TABLE IF EXISTS `borrower_attribute_types`;
270 CREATE TABLE `borrower_attribute_types` (
271 `code` varchar(10) NOT NULL,
272 `description` varchar(255) NOT NULL,
273 `repeatable` tinyint(1) NOT NULL default 0,
274 `unique_id` tinyint(1) NOT NULL default 0,
275 `opac_display` tinyint(1) NOT NULL default 0,
276 `password_allowed` tinyint(1) NOT NULL default 0,
277 `staff_searchable` tinyint(1) NOT NULL default 0,
278 `authorised_value_category` varchar(10) default NULL,
280 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
283 -- Table structure for table `borrower_attributes`
286 DROP TABLE IF EXISTS `borrower_attributes`;
287 CREATE TABLE `borrower_attributes` (
288 `borrowernumber` int(11) NOT NULL,
289 `code` varchar(10) NOT NULL,
290 `attribute` varchar(64) default NULL,
291 `password` varchar(64) default NULL,
292 KEY `borrowernumber` (`borrowernumber`),
293 KEY `code_attribute` (`code`, `attribute`),
294 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
295 ON DELETE CASCADE ON UPDATE CASCADE,
296 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
297 ON DELETE CASCADE ON UPDATE CASCADE
298 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
300 CREATE TABLE `branch_item_rules` (
301 `branchcode` varchar(10) NOT NULL,
302 `itemtype` varchar(10) NOT NULL,
303 `holdallowed` tinyint(1) default NULL,
304 PRIMARY KEY (`itemtype`,`branchcode`),
305 KEY `branch_item_rules_ibfk_2` (`branchcode`),
306 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
307 ON DELETE CASCADE ON UPDATE CASCADE,
308 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
309 ON DELETE CASCADE ON UPDATE CASCADE
310 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
313 -- Table structure for table `branchcategories`
316 DROP TABLE IF EXISTS `branchcategories`;
317 CREATE TABLE `branchcategories` (
318 `categorycode` varchar(10) NOT NULL default '',
319 `categoryname` varchar(32),
320 `codedescription` mediumtext,
321 `categorytype` varchar(16),
322 PRIMARY KEY (`categorycode`)
323 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
326 -- Table structure for table `branches`
329 DROP TABLE IF EXISTS `branches`;
330 CREATE TABLE `branches` (
331 `branchcode` varchar(10) NOT NULL default '',
332 `branchname` mediumtext NOT NULL,
333 `branchaddress1` mediumtext,
334 `branchaddress2` mediumtext,
335 `branchaddress3` mediumtext,
336 `branchzip` varchar(25) default NULL,
337 `branchcity` mediumtext,
338 `branchcountry` text,
339 `branchphone` mediumtext,
340 `branchfax` mediumtext,
341 `branchemail` mediumtext,
342 `branchurl` mediumtext,
343 `issuing` tinyint(4) default NULL,
344 `branchip` varchar(15) default NULL,
345 `branchprinter` varchar(100) default NULL,
346 `branchnotes` mediumtext,
347 UNIQUE KEY `branchcode` (`branchcode`)
348 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
351 -- Table structure for table `branchrelations`
354 DROP TABLE IF EXISTS `branchrelations`;
355 CREATE TABLE `branchrelations` (
356 `branchcode` varchar(10) NOT NULL default '',
357 `categorycode` varchar(10) NOT NULL default '',
358 PRIMARY KEY (`branchcode`,`categorycode`),
359 KEY `branchcode` (`branchcode`),
360 KEY `categorycode` (`categorycode`),
361 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
362 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
363 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
366 -- Table structure for table `branchtransfers`
369 DROP TABLE IF EXISTS `branchtransfers`;
370 CREATE TABLE `branchtransfers` (
371 `itemnumber` int(11) NOT NULL default 0,
372 `datesent` datetime default NULL,
373 `frombranch` varchar(10) NOT NULL default '',
374 `datearrived` datetime default NULL,
375 `tobranch` varchar(10) NOT NULL default '',
376 `comments` mediumtext,
377 KEY `frombranch` (`frombranch`),
378 KEY `tobranch` (`tobranch`),
379 KEY `itemnumber` (`itemnumber`),
380 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
381 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
382 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
383 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
387 -- Table structure for table `browser`
389 DROP TABLE IF EXISTS `browser`;
390 CREATE TABLE `browser` (
391 `level` int(11) NOT NULL,
392 `classification` varchar(20) NOT NULL,
393 `description` varchar(255) NOT NULL,
394 `number` bigint(20) NOT NULL,
395 `endnode` tinyint(4) NOT NULL
396 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
399 -- Table structure for table `categories`
402 DROP TABLE IF EXISTS `categories`;
403 CREATE TABLE `categories` (
404 `categorycode` varchar(10) NOT NULL default '',
405 `description` mediumtext,
406 `enrolmentperiod` smallint(6) default NULL,
407 `upperagelimit` smallint(6) default NULL,
408 `dateofbirthrequired` tinyint(1) default NULL,
409 `finetype` varchar(30) default NULL,
410 `bulk` tinyint(1) default NULL,
411 `enrolmentfee` decimal(28,6) default NULL,
412 `overduenoticerequired` tinyint(1) default NULL,
413 `issuelimit` smallint(6) default NULL,
414 `reservefee` decimal(28,6) default NULL,
415 `category_type` varchar(1) NOT NULL default 'A',
416 PRIMARY KEY (`categorycode`),
417 UNIQUE KEY `categorycode` (`categorycode`)
418 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
421 -- Table structure for table `borrower_branch_circ_rules`
424 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
425 CREATE TABLE `branch_borrower_circ_rules` (
426 `branchcode` VARCHAR(10) NOT NULL,
427 `categorycode` VARCHAR(10) NOT NULL,
428 `maxissueqty` int(4) default NULL,
429 PRIMARY KEY (`categorycode`, `branchcode`),
430 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
431 ON DELETE CASCADE ON UPDATE CASCADE,
432 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
433 ON DELETE CASCADE ON UPDATE CASCADE
434 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
437 -- Table structure for table `default_borrower_circ_rules`
440 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
441 CREATE TABLE `default_borrower_circ_rules` (
442 `categorycode` VARCHAR(10) NOT NULL,
443 `maxissueqty` int(4) default NULL,
444 PRIMARY KEY (`categorycode`),
445 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
446 ON DELETE CASCADE ON UPDATE CASCADE
447 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
450 -- Table structure for table `default_branch_circ_rules`
453 DROP TABLE IF EXISTS `default_branch_circ_rules`;
454 CREATE TABLE `default_branch_circ_rules` (
455 `branchcode` VARCHAR(10) NOT NULL,
456 `maxissueqty` int(4) default NULL,
457 `holdallowed` tinyint(1) default NULL,
458 PRIMARY KEY (`branchcode`),
459 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
460 ON DELETE CASCADE ON UPDATE CASCADE
461 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
464 -- Table structure for table `default_branch_item_rules`
467 CREATE TABLE `default_branch_item_rules` (
468 `itemtype` varchar(10) NOT NULL,
469 `holdallowed` tinyint(1) default NULL,
470 PRIMARY KEY (`itemtype`),
471 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
472 ON DELETE CASCADE ON UPDATE CASCADE
473 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
476 -- Table structure for table `default_circ_rules`
479 DROP TABLE IF EXISTS `default_circ_rules`;
480 CREATE TABLE `default_circ_rules` (
481 `singleton` enum('singleton') NOT NULL default 'singleton',
482 `maxissueqty` int(4) default NULL,
483 `holdallowed` int(1) default NULL,
484 PRIMARY KEY (`singleton`)
485 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
488 -- Table structure for table `cities`
491 DROP TABLE IF EXISTS `cities`;
492 CREATE TABLE `cities` (
493 `cityid` int(11) NOT NULL auto_increment,
494 `city_name` varchar(100) NOT NULL default '',
495 `city_zipcode` varchar(20) default NULL,
496 PRIMARY KEY (`cityid`)
497 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
500 -- Table structure for table `class_sort_rules`
503 DROP TABLE IF EXISTS `class_sort_rules`;
504 CREATE TABLE `class_sort_rules` (
505 `class_sort_rule` varchar(10) NOT NULL default '',
506 `description` mediumtext,
507 `sort_routine` varchar(30) NOT NULL default '',
508 PRIMARY KEY (`class_sort_rule`),
509 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
510 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
513 -- Table structure for table `class_sources`
516 DROP TABLE IF EXISTS `class_sources`;
517 CREATE TABLE `class_sources` (
518 `cn_source` varchar(10) NOT NULL default '',
519 `description` mediumtext,
520 `used` tinyint(4) NOT NULL default 0,
521 `class_sort_rule` varchar(10) NOT NULL default '',
522 PRIMARY KEY (`cn_source`),
523 UNIQUE KEY `cn_source_idx` (`cn_source`),
524 KEY `used_idx` (`used`),
525 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
526 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
529 -- Table structure for table `currency`
532 DROP TABLE IF EXISTS `currency`;
533 CREATE TABLE `currency` (
534 `currency` varchar(10) NOT NULL default '',
535 `symbol` varchar(5) default NULL,
536 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
537 `rate` float(7,5) default NULL,
538 `active` tinyint(1) default NULL,
539 PRIMARY KEY (`currency`)
540 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
543 -- Table structure for table `deletedbiblio`
546 DROP TABLE IF EXISTS `deletedbiblio`;
547 CREATE TABLE `deletedbiblio` (
548 `biblionumber` int(11) NOT NULL default 0,
549 `frameworkcode` varchar(4) NOT NULL default '',
552 `unititle` mediumtext,
554 `serial` tinyint(1) default NULL,
555 `seriestitle` mediumtext,
556 `copyrightdate` smallint(6) default NULL,
557 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
558 `datecreated` DATE NOT NULL,
559 `abstract` mediumtext,
560 PRIMARY KEY (`biblionumber`),
561 KEY `blbnoidx` (`biblionumber`)
562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
565 -- Table structure for table `deletedbiblioitems`
568 DROP TABLE IF EXISTS `deletedbiblioitems`;
569 CREATE TABLE `deletedbiblioitems` (
570 `biblioitemnumber` int(11) NOT NULL default 0,
571 `biblionumber` int(11) NOT NULL default 0,
574 `itemtype` varchar(10) default NULL,
575 `isbn` varchar(30) default NULL,
576 `issn` varchar(9) default NULL,
577 `publicationyear` text,
578 `publishercode` varchar(255) default NULL,
579 `volumedate` date default NULL,
581 `collectiontitle` mediumtext default NULL,
582 `collectionissn` text default NULL,
583 `collectionvolume` mediumtext default NULL,
584 `editionstatement` text default NULL,
585 `editionresponsibility` text default NULL,
586 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
587 `illus` varchar(255) default NULL,
588 `pages` varchar(255) default NULL,
590 `size` varchar(255) default NULL,
591 `place` varchar(255) default NULL,
592 `lccn` varchar(25) default NULL,
594 `url` varchar(255) default NULL,
595 `cn_source` varchar(10) default NULL,
596 `cn_class` varchar(30) default NULL,
597 `cn_item` varchar(10) default NULL,
598 `cn_suffix` varchar(10) default NULL,
599 `cn_sort` varchar(30) default NULL,
600 `totalissues` int(10),
601 `marcxml` longtext NOT NULL,
602 PRIMARY KEY (`biblioitemnumber`),
603 KEY `bibinoidx` (`biblioitemnumber`),
604 KEY `bibnoidx` (`biblionumber`),
606 KEY `publishercode` (`publishercode`)
607 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
610 -- Table structure for table `deletedborrowers`
613 DROP TABLE IF EXISTS `deletedborrowers`;
614 CREATE TABLE `deletedborrowers` (
615 `borrowernumber` int(11) NOT NULL default 0,
616 `cardnumber` varchar(9) NOT NULL default '',
617 `surname` mediumtext NOT NULL,
620 `othernames` mediumtext,
622 `streetnumber` varchar(10) default NULL,
623 `streettype` varchar(50) default NULL,
624 `address` mediumtext NOT NULL,
626 `city` mediumtext NOT NULL,
627 `zipcode` varchar(25) default NULL,
631 `mobile` varchar(50) default NULL,
635 `B_streetnumber` varchar(10) default NULL,
636 `B_streettype` varchar(50) default NULL,
637 `B_address` varchar(100) default NULL,
638 `B_address2` text default NULL,
640 `B_zipcode` varchar(25) default NULL,
643 `B_phone` mediumtext,
644 `dateofbirth` date default NULL,
645 `branchcode` varchar(10) NOT NULL default '',
646 `categorycode` varchar(10) default NULL,
647 `dateenrolled` date default NULL,
648 `dateexpiry` date default NULL,
649 `gonenoaddress` tinyint(1) default NULL,
650 `lost` tinyint(1) default NULL,
651 `debarred` tinyint(1) default NULL,
652 `contactname` mediumtext,
653 `contactfirstname` text,
655 `guarantorid` int(11) default NULL,
656 `borrowernotes` mediumtext,
657 `relationship` varchar(100) default NULL,
658 `ethnicity` varchar(50) default NULL,
659 `ethnotes` varchar(255) default NULL,
660 `sex` varchar(1) default NULL,
661 `password` varchar(30) default NULL,
662 `flags` int(11) default NULL,
663 `userid` varchar(30) default NULL,
664 `opacnote` mediumtext,
665 `contactnote` varchar(255) default NULL,
666 `sort1` varchar(80) default NULL,
667 `sort2` varchar(80) default NULL,
668 `altcontactfirstname` varchar(255) default NULL,
669 `altcontactsurname` varchar(255) default NULL,
670 `altcontactaddress1` varchar(255) default NULL,
671 `altcontactaddress2` varchar(255) default NULL,
672 `altcontactaddress3` varchar(255) default NULL,
673 `altcontactzipcode` varchar(50) default NULL,
674 `altcontactcountry` text default NULL,
675 `altcontactphone` varchar(50) default NULL,
676 `smsalertnumber` varchar(50) default NULL,
677 KEY `borrowernumber` (`borrowernumber`),
678 KEY `cardnumber` (`cardnumber`)
679 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
682 -- Table structure for table `deleteditems`
685 DROP TABLE IF EXISTS `deleteditems`;
686 CREATE TABLE `deleteditems` (
687 `itemnumber` int(11) NOT NULL default 0,
688 `biblionumber` int(11) NOT NULL default 0,
689 `biblioitemnumber` int(11) NOT NULL default 0,
690 `barcode` varchar(20) default NULL,
691 `dateaccessioned` date default NULL,
692 `booksellerid` mediumtext default NULL,
693 `homebranch` varchar(10) default NULL,
694 `price` decimal(8,2) default NULL,
695 `replacementprice` decimal(8,2) default NULL,
696 `replacementpricedate` date default NULL,
697 `datelastborrowed` date default NULL,
698 `datelastseen` date default NULL,
699 `stack` tinyint(1) default NULL,
700 `notforloan` tinyint(1) NOT NULL default 0,
701 `damaged` tinyint(1) NOT NULL default 0,
702 `itemlost` tinyint(1) NOT NULL default 0,
703 `wthdrawn` tinyint(1) NOT NULL default 0,
704 `itemcallnumber` varchar(255) default NULL,
705 `issues` smallint(6) default NULL,
706 `renewals` smallint(6) default NULL,
707 `reserves` smallint(6) default NULL,
708 `restricted` tinyint(1) default NULL,
709 `itemnotes` mediumtext,
710 `holdingbranch` varchar(10) default NULL,
711 `paidfor` mediumtext,
712 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
713 `location` varchar(80) default NULL,
714 `permanent_location` varchar(80) default NULL,
715 `onloan` date default NULL,
716 `cn_source` varchar(10) default NULL,
717 `cn_sort` varchar(30) default NULL,
718 `ccode` varchar(10) default NULL,
719 `materials` varchar(10) default NULL,
720 `uri` varchar(255) default NULL,
721 `itype` varchar(10) default NULL,
722 `more_subfields_xml` longtext default NULL,
723 `enumchron` varchar(80) default NULL,
724 `copynumber` varchar(32) default NULL,
726 PRIMARY KEY (`itemnumber`),
727 KEY `delitembarcodeidx` (`barcode`),
728 KEY `delitembinoidx` (`biblioitemnumber`),
729 KEY `delitembibnoidx` (`biblionumber`),
730 KEY `delhomebranch` (`homebranch`),
731 KEY `delholdingbranch` (`holdingbranch`)
732 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
735 -- Table structure for table `ethnicity`
738 DROP TABLE IF EXISTS `ethnicity`;
739 CREATE TABLE `ethnicity` (
740 `code` varchar(10) NOT NULL default '',
741 `name` varchar(255) default NULL,
743 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
746 -- Table structure for table `hold_fill_targets`
749 DROP TABLE IF EXISTS `hold_fill_targets`;
750 CREATE TABLE hold_fill_targets (
751 `borrowernumber` int(11) NOT NULL,
752 `biblionumber` int(11) NOT NULL,
753 `itemnumber` int(11) NOT NULL,
754 `source_branchcode` varchar(10) default NULL,
755 `item_level_request` tinyint(4) NOT NULL default 0,
756 PRIMARY KEY `itemnumber` (`itemnumber`),
757 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
758 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
759 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
760 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
761 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
762 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
763 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
764 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
765 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
766 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
769 -- Table structure for table `import_batches`
772 DROP TABLE IF EXISTS `import_batches`;
773 CREATE TABLE `import_batches` (
774 `import_batch_id` int(11) NOT NULL auto_increment,
775 `matcher_id` int(11) default NULL,
776 `template_id` int(11) default NULL,
777 `branchcode` varchar(10) default NULL,
778 `num_biblios` int(11) NOT NULL default 0,
779 `num_items` int(11) NOT NULL default 0,
780 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
781 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
782 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
783 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
784 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
785 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
786 `file_name` varchar(100),
787 `comments` mediumtext,
788 PRIMARY KEY (`import_batch_id`),
789 KEY `branchcode` (`branchcode`)
790 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
793 -- Table structure for table `import_records`
796 DROP TABLE IF EXISTS `import_records`;
797 CREATE TABLE `import_records` (
798 `import_record_id` int(11) NOT NULL auto_increment,
799 `import_batch_id` int(11) NOT NULL,
800 `branchcode` varchar(10) default NULL,
801 `record_sequence` int(11) NOT NULL default 0,
802 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
803 `import_date` DATE default NULL,
804 `marc` longblob NOT NULL,
805 `marcxml` longtext NOT NULL,
806 `marcxml_old` longtext NOT NULL,
807 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
808 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
809 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
810 `import_error` mediumtext,
811 `encoding` varchar(40) NOT NULL default '',
812 `z3950random` varchar(40) default NULL,
813 PRIMARY KEY (`import_record_id`),
814 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
815 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
816 KEY `branchcode` (`branchcode`),
817 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
818 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
821 -- Table structure for `import_record_matches`
823 DROP TABLE IF EXISTS `import_record_matches`;
824 CREATE TABLE `import_record_matches` (
825 `import_record_id` int(11) NOT NULL,
826 `candidate_match_id` int(11) NOT NULL,
827 `score` int(11) NOT NULL default 0,
828 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
829 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
830 KEY `record_score` (`import_record_id`, `score`)
831 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
834 -- Table structure for table `import_biblios`
837 DROP TABLE IF EXISTS `import_biblios`;
838 CREATE TABLE `import_biblios` (
839 `import_record_id` int(11) NOT NULL,
840 `matched_biblionumber` int(11) default NULL,
841 `control_number` varchar(25) default NULL,
842 `original_source` varchar(25) default NULL,
843 `title` varchar(128) default NULL,
844 `author` varchar(80) default NULL,
845 `isbn` varchar(30) default NULL,
846 `issn` varchar(9) default NULL,
847 `has_items` tinyint(1) NOT NULL default 0,
848 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
849 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
850 KEY `matched_biblionumber` (`matched_biblionumber`),
851 KEY `title` (`title`),
853 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
856 -- Table structure for table `import_items`
859 DROP TABLE IF EXISTS `import_items`;
860 CREATE TABLE `import_items` (
861 `import_items_id` int(11) NOT NULL auto_increment,
862 `import_record_id` int(11) NOT NULL,
863 `itemnumber` int(11) default NULL,
864 `branchcode` varchar(10) default NULL,
865 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
866 `marcxml` longtext NOT NULL,
867 `import_error` mediumtext,
868 PRIMARY KEY (`import_items_id`),
869 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
870 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
871 KEY `itemnumber` (`itemnumber`),
872 KEY `branchcode` (`branchcode`)
873 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
876 -- Table structure for table `issues`
879 DROP TABLE IF EXISTS `issues`;
880 CREATE TABLE `issues` (
881 `borrowernumber` int(11) default NULL,
882 `itemnumber` int(11) default NULL,
883 `date_due` date default NULL,
884 `branchcode` varchar(10) default NULL,
885 `issuingbranch` varchar(18) default NULL,
886 `returndate` date default NULL,
887 `lastreneweddate` date default NULL,
888 `return` varchar(4) default NULL,
889 `renewals` tinyint(4) default NULL,
890 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
891 `issuedate` date default NULL,
892 KEY `issuesborridx` (`borrowernumber`),
893 KEY `issuesitemidx` (`itemnumber`),
894 KEY `bordate` (`borrowernumber`,`timestamp`),
895 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
896 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
897 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
900 -- Table structure for table `issuingrules`
903 DROP TABLE IF EXISTS `issuingrules`;
904 CREATE TABLE `issuingrules` (
905 `categorycode` varchar(10) NOT NULL default '',
906 `itemtype` varchar(10) NOT NULL default '',
907 `restrictedtype` tinyint(1) default NULL,
908 `rentaldiscount` decimal(28,6) default NULL,
909 `reservecharge` decimal(28,6) default NULL,
910 `fine` decimal(28,6) default NULL,
911 `firstremind` int(11) default NULL,
912 `chargeperiod` int(11) default NULL,
913 `accountsent` int(11) default NULL,
914 `chargename` varchar(100) default NULL,
915 `maxissueqty` int(4) default NULL,
916 `issuelength` int(4) default NULL,
917 `branchcode` varchar(10) NOT NULL default '',
918 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
919 KEY `categorycode` (`categorycode`),
920 KEY `itemtype` (`itemtype`)
921 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
924 -- Table structure for table `items`
927 DROP TABLE IF EXISTS `items`;
928 CREATE TABLE `items` (
929 `itemnumber` int(11) NOT NULL auto_increment,
930 `biblionumber` int(11) NOT NULL default 0,
931 `biblioitemnumber` int(11) NOT NULL default 0,
932 `barcode` varchar(20) default NULL,
933 `dateaccessioned` date default NULL,
934 `booksellerid` mediumtext default NULL,
935 `homebranch` varchar(10) default NULL,
936 `price` decimal(8,2) default NULL,
937 `replacementprice` decimal(8,2) default NULL,
938 `replacementpricedate` date default NULL,
939 `datelastborrowed` date default NULL,
940 `datelastseen` date default NULL,
941 `stack` tinyint(1) default NULL,
942 `notforloan` tinyint(1) NOT NULL default 0,
943 `damaged` tinyint(1) NOT NULL default 0,
944 `itemlost` tinyint(1) NOT NULL default 0,
945 `wthdrawn` tinyint(1) NOT NULL default 0,
946 `itemcallnumber` varchar(255) default NULL,
947 `issues` smallint(6) default NULL,
948 `renewals` smallint(6) default NULL,
949 `reserves` smallint(6) default NULL,
950 `restricted` tinyint(1) default NULL,
951 `itemnotes` mediumtext,
952 `holdingbranch` varchar(10) default NULL,
953 `paidfor` mediumtext,
954 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
955 `location` varchar(80) default NULL,
956 `permanent_location` varchar(80) default NULL,
957 `onloan` date default NULL,
958 `cn_source` varchar(10) default NULL,
959 `cn_sort` varchar(30) default NULL,
960 `ccode` varchar(10) default NULL,
961 `materials` varchar(10) default NULL,
962 `uri` varchar(255) default NULL,
963 `itype` varchar(10) default NULL,
964 `more_subfields_xml` longtext default NULL,
965 `enumchron` varchar(80) default NULL,
966 `copynumber` varchar(32) default NULL,
967 PRIMARY KEY (`itemnumber`),
968 UNIQUE KEY `itembarcodeidx` (`barcode`),
969 KEY `itembinoidx` (`biblioitemnumber`),
970 KEY `itembibnoidx` (`biblionumber`),
971 KEY `homebranch` (`homebranch`),
972 KEY `holdingbranch` (`holdingbranch`),
973 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
974 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
975 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
976 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
979 -- Table structure for table `itemtypes`
982 DROP TABLE IF EXISTS `itemtypes`;
983 CREATE TABLE `itemtypes` (
984 `itemtype` varchar(10) NOT NULL default '',
985 `description` mediumtext,
986 `renewalsallowed` smallint(6) default NULL,
987 `rentalcharge` double(16,4) default NULL,
988 `notforloan` smallint(6) default NULL,
989 `imageurl` varchar(200) default NULL,
991 PRIMARY KEY (`itemtype`),
992 UNIQUE KEY `itemtype` (`itemtype`)
993 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
996 -- Table structure for table `labels_batches`
999 DROP TABLE IF EXISTS `labels_batches`;
1000 CREATE TABLE `labels_batches` (
1001 `label_id` int(11) NOT NULL auto_increment,
1002 `batch_id` int(10) NOT NULL default '1',
1003 `item_number` int(11) NOT NULL default '0',
1004 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1005 `branch_code` varchar(10) NOT NULL default 'NB',
1006 PRIMARY KEY USING BTREE (`label_id`),
1007 KEY `branch_fk` (`branch_code`),
1008 KEY `item_fk` (`item_number`),
1009 CONSTRAINT `item_fk_constraint` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE,
1010 CONSTRAINT `branch_fk_constraint` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE
1011 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1014 -- Table structure for table `labels_layouts`
1017 DROP TABLE IF EXISTS `labels_layouts`;
1018 CREATE TABLE `labels_layouts` (
1019 `layout_id` int(4) NOT NULL auto_increment,
1020 `barcode_type` char(100) NOT NULL default 'CODE39',
1021 `printing_type` char(32) NOT NULL default 'BAR',
1022 `layout_name` char(20) NOT NULL default 'DEFAULT',
1023 `guidebox` int(1) default '0',
1024 `font` char(10) character set utf8 collate utf8_unicode_ci NOT NULL default 'TR',
1025 `font_size` int(4) NOT NULL default '10',
1026 `callnum_split` int(1) default '0',
1027 `text_justify` char(1) character set utf8 collate utf8_unicode_ci NOT NULL default 'L',
1028 `format_string` varchar(210) NOT NULL default 'barcode',
1029 PRIMARY KEY USING BTREE (`layout_id`)
1030 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1033 -- Table structure for table `labels_templates`
1036 DROP TABLE IF EXISTS `labels_templates`;
1037 CREATE TABLE `labels_templates` (
1038 `template_id` int(4) NOT NULL auto_increment,
1039 `profile_id` int(4) default NULL,
1040 `template_code` char(100) NOT NULL default 'DEFAULT TEMPLATE',
1041 `template_desc` char(100) NOT NULL default 'Default description',
1042 `page_width` float NOT NULL default '0',
1043 `page_height` float NOT NULL default '0',
1044 `label_width` float NOT NULL default '0',
1045 `label_height` float NOT NULL default '0',
1046 `top_text_margin` float NOT NULL default '0',
1047 `left_text_margin` float NOT NULL default '0',
1048 `top_margin` float NOT NULL default '0',
1049 `left_margin` float NOT NULL default '0',
1050 `cols` int(2) NOT NULL default '0',
1051 `rows` int(2) NOT NULL default '0',
1052 `col_gap` float NOT NULL default '0',
1053 `row_gap` float NOT NULL default '0',
1054 `units` char(20) NOT NULL default 'POINT',
1055 PRIMARY KEY (`template_id`),
1056 KEY `template_profile_fk_constraint` (`profile_id`)
1057 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1060 -- Table structure for table `letter`
1063 DROP TABLE IF EXISTS `letter`;
1064 CREATE TABLE `letter` (
1065 `module` varchar(20) NOT NULL default '',
1066 `code` varchar(20) NOT NULL default '',
1067 `name` varchar(100) NOT NULL default '',
1068 `title` varchar(200) NOT NULL default '',
1070 PRIMARY KEY (`module`,`code`)
1071 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1074 -- Table structure for table `marc_subfield_structure`
1077 DROP TABLE IF EXISTS `marc_subfield_structure`;
1078 CREATE TABLE `marc_subfield_structure` (
1079 `tagfield` varchar(3) NOT NULL default '',
1080 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1081 `liblibrarian` varchar(255) NOT NULL default '',
1082 `libopac` varchar(255) NOT NULL default '',
1083 `repeatable` tinyint(4) NOT NULL default 0,
1084 `mandatory` tinyint(4) NOT NULL default 0,
1085 `kohafield` varchar(40) default NULL,
1086 `tab` tinyint(1) default NULL,
1087 `authorised_value` varchar(20) default NULL,
1088 `authtypecode` varchar(20) default NULL,
1089 `value_builder` varchar(80) default NULL,
1090 `isurl` tinyint(1) default NULL,
1091 `hidden` tinyint(1) default NULL,
1092 `frameworkcode` varchar(4) NOT NULL default '',
1093 `seealso` varchar(1100) default NULL,
1094 `link` varchar(80) default NULL,
1095 `defaultvalue` text default NULL,
1096 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1097 KEY `kohafield_2` (`kohafield`),
1098 KEY `tab` (`frameworkcode`,`tab`),
1099 KEY `kohafield` (`frameworkcode`,`kohafield`)
1100 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1103 -- Table structure for table `marc_tag_structure`
1106 DROP TABLE IF EXISTS `marc_tag_structure`;
1107 CREATE TABLE `marc_tag_structure` (
1108 `tagfield` varchar(3) NOT NULL default '',
1109 `liblibrarian` varchar(255) NOT NULL default '',
1110 `libopac` varchar(255) NOT NULL default '',
1111 `repeatable` tinyint(4) NOT NULL default 0,
1112 `mandatory` tinyint(4) NOT NULL default 0,
1113 `authorised_value` varchar(10) default NULL,
1114 `frameworkcode` varchar(4) NOT NULL default '',
1115 PRIMARY KEY (`frameworkcode`,`tagfield`)
1116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1119 -- Table structure for table `marc_matchers`
1122 DROP TABLE IF EXISTS `marc_matchers`;
1123 CREATE TABLE `marc_matchers` (
1124 `matcher_id` int(11) NOT NULL auto_increment,
1125 `code` varchar(10) NOT NULL default '',
1126 `description` varchar(255) NOT NULL default '',
1127 `record_type` varchar(10) NOT NULL default 'biblio',
1128 `threshold` int(11) NOT NULL default 0,
1129 PRIMARY KEY (`matcher_id`),
1130 KEY `code` (`code`),
1131 KEY `record_type` (`record_type`)
1132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1135 -- Table structure for table `matchpoints`
1137 DROP TABLE IF EXISTS `matchpoints`;
1138 CREATE TABLE `matchpoints` (
1139 `matcher_id` int(11) NOT NULL,
1140 `matchpoint_id` int(11) NOT NULL auto_increment,
1141 `search_index` varchar(30) NOT NULL default '',
1142 `score` int(11) NOT NULL default 0,
1143 PRIMARY KEY (`matchpoint_id`),
1144 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1145 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1150 -- Table structure for table `matchpoint_components`
1152 DROP TABLE IF EXISTS `matchpoint_components`;
1153 CREATE TABLE `matchpoint_components` (
1154 `matchpoint_id` int(11) NOT NULL,
1155 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1156 sequence int(11) NOT NULL default 0,
1157 tag varchar(3) NOT NULL default '',
1158 subfields varchar(40) NOT NULL default '',
1159 offset int(4) NOT NULL default 0,
1160 length int(4) NOT NULL default 0,
1161 PRIMARY KEY (`matchpoint_component_id`),
1162 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1163 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1164 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1168 -- Table structure for table `matcher_component_norms`
1170 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1171 CREATE TABLE `matchpoint_component_norms` (
1172 `matchpoint_component_id` int(11) NOT NULL,
1173 `sequence` int(11) NOT NULL default 0,
1174 `norm_routine` varchar(50) NOT NULL default '',
1175 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1176 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1177 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1178 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1181 -- Table structure for table `matcher_matchpoints`
1183 DROP TABLE IF EXISTS `matcher_matchpoints`;
1184 CREATE TABLE `matcher_matchpoints` (
1185 `matcher_id` int(11) NOT NULL,
1186 `matchpoint_id` int(11) NOT NULL,
1187 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1188 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1189 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1190 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1194 -- Table structure for table `matchchecks`
1196 DROP TABLE IF EXISTS `matchchecks`;
1197 CREATE TABLE `matchchecks` (
1198 `matcher_id` int(11) NOT NULL,
1199 `matchcheck_id` int(11) NOT NULL auto_increment,
1200 `source_matchpoint_id` int(11) NOT NULL,
1201 `target_matchpoint_id` int(11) NOT NULL,
1202 PRIMARY KEY (`matchcheck_id`),
1203 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1204 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1205 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1206 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1207 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1208 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1212 -- Table structure for table `notifys`
1215 DROP TABLE IF EXISTS `notifys`;
1216 CREATE TABLE `notifys` (
1217 `notify_id` int(11) NOT NULL default 0,
1218 `borrowernumber` int(11) NOT NULL default 0,
1219 `itemnumber` int(11) NOT NULL default 0,
1220 `notify_date` date default NULL,
1221 `notify_send_date` date default NULL,
1222 `notify_level` int(1) NOT NULL default 0,
1223 `method` varchar(20) NOT NULL default ''
1224 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1227 -- Table structure for table `nozebra`
1230 DROP TABLE IF EXISTS `nozebra`;
1231 CREATE TABLE `nozebra` (
1232 `server` varchar(20) NOT NULL,
1233 `indexname` varchar(40) NOT NULL,
1234 `value` varchar(250) NOT NULL,
1235 `biblionumbers` longtext NOT NULL,
1236 KEY `indexname` (`server`,`indexname`),
1237 KEY `value` (`server`,`value`))
1238 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1241 -- Table structure for table `old_issues`
1244 DROP TABLE IF EXISTS `old_issues`;
1245 CREATE TABLE `old_issues` (
1246 `borrowernumber` int(11) default NULL,
1247 `itemnumber` int(11) default NULL,
1248 `date_due` date default NULL,
1249 `branchcode` varchar(10) default NULL,
1250 `issuingbranch` varchar(18) default NULL,
1251 `returndate` date default NULL,
1252 `lastreneweddate` date default NULL,
1253 `return` varchar(4) default NULL,
1254 `renewals` tinyint(4) default NULL,
1255 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1256 `issuedate` date default NULL,
1257 KEY `old_issuesborridx` (`borrowernumber`),
1258 KEY `old_issuesitemidx` (`itemnumber`),
1259 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1260 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1261 ON DELETE SET NULL ON UPDATE SET NULL,
1262 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1263 ON DELETE SET NULL ON UPDATE SET NULL
1264 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1267 -- Table structure for table `old_reserves`
1269 DROP TABLE IF EXISTS `old_reserves`;
1270 CREATE TABLE `old_reserves` (
1271 `borrowernumber` int(11) default NULL,
1272 `reservedate` date default NULL,
1273 `biblionumber` int(11) default NULL,
1274 `constrainttype` varchar(1) default NULL,
1275 `branchcode` varchar(10) default NULL,
1276 `notificationdate` date default NULL,
1277 `reminderdate` date default NULL,
1278 `cancellationdate` date default NULL,
1279 `reservenotes` mediumtext,
1280 `priority` smallint(6) default NULL,
1281 `found` varchar(1) default NULL,
1282 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1283 `itemnumber` int(11) default NULL,
1284 `waitingdate` date default NULL,
1285 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1286 KEY `old_reserves_biblionumber` (`biblionumber`),
1287 KEY `old_reserves_itemnumber` (`itemnumber`),
1288 KEY `old_reserves_branchcode` (`branchcode`),
1289 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1290 ON DELETE SET NULL ON UPDATE SET NULL,
1291 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1292 ON DELETE SET NULL ON UPDATE SET NULL,
1293 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1294 ON DELETE SET NULL ON UPDATE SET NULL
1295 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1298 -- Table structure for table `opac_news`
1301 DROP TABLE IF EXISTS `opac_news`;
1302 CREATE TABLE `opac_news` (
1303 `idnew` int(10) unsigned NOT NULL auto_increment,
1304 `title` varchar(250) NOT NULL default '',
1305 `new` text NOT NULL,
1306 `lang` varchar(25) NOT NULL default '',
1307 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1308 `expirationdate` date default NULL,
1309 `number` int(11) default NULL,
1310 PRIMARY KEY (`idnew`)
1311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1314 -- Table structure for table `overduerules`
1317 DROP TABLE IF EXISTS `overduerules`;
1318 CREATE TABLE `overduerules` (
1319 `branchcode` varchar(10) NOT NULL default '',
1320 `categorycode` varchar(10) NOT NULL default '',
1321 `delay1` int(4) default 0,
1322 `letter1` varchar(20) default NULL,
1323 `debarred1` varchar(1) default 0,
1324 `delay2` int(4) default 0,
1325 `debarred2` varchar(1) default 0,
1326 `letter2` varchar(20) default NULL,
1327 `delay3` int(4) default 0,
1328 `letter3` varchar(20) default NULL,
1329 `debarred3` int(1) default 0,
1330 PRIMARY KEY (`branchcode`,`categorycode`)
1331 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1334 -- Table structure for table `patroncards`
1337 DROP TABLE IF EXISTS `patroncards`;
1338 CREATE TABLE `patroncards` (
1339 `cardid` int(11) NOT NULL auto_increment,
1340 `batch_id` varchar(10) NOT NULL default '1',
1341 `borrowernumber` int(11) NOT NULL,
1342 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1343 PRIMARY KEY (`cardid`),
1344 KEY `patroncards_ibfk_1` (`borrowernumber`),
1345 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1349 -- Table structure for table `patronimage`
1352 DROP TABLE IF EXISTS `patronimage`;
1353 CREATE TABLE `patronimage` (
1354 `cardnumber` varchar(16) NOT NULL,
1355 `mimetype` varchar(15) NOT NULL,
1356 `imagefile` mediumblob NOT NULL,
1357 PRIMARY KEY (`cardnumber`),
1358 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1359 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1362 -- Table structure for table `printers`
1365 DROP TABLE IF EXISTS `printers`;
1366 CREATE TABLE `printers` (
1367 `printername` varchar(40) NOT NULL default '',
1368 `printqueue` varchar(20) default NULL,
1369 `printtype` varchar(20) default NULL,
1370 PRIMARY KEY (`printername`)
1371 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1374 -- Table structure for table `printers_profile`
1377 DROP TABLE IF EXISTS `printers_profile`;
1378 CREATE TABLE `printers_profile` (
1379 `profile_id` int(4) NOT NULL auto_increment,
1380 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1381 `template_id` int(4) NOT NULL default '0',
1382 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1383 `offset_horz` float NOT NULL default '0',
1384 `offset_vert` float NOT NULL default '0',
1385 `creep_horz` float NOT NULL default '0',
1386 `creep_vert` float NOT NULL default '0',
1387 `units` char(20) NOT NULL default 'POINT',
1388 PRIMARY KEY (`profile_id`),
1389 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`)
1390 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1393 -- Table structure for table `repeatable_holidays`
1396 DROP TABLE IF EXISTS `repeatable_holidays`;
1397 CREATE TABLE `repeatable_holidays` (
1398 `id` int(11) NOT NULL auto_increment,
1399 `branchcode` varchar(10) NOT NULL default '',
1400 `weekday` smallint(6) default NULL,
1401 `day` smallint(6) default NULL,
1402 `month` smallint(6) default NULL,
1403 `title` varchar(50) NOT NULL default '',
1404 `description` text NOT NULL,
1406 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1409 -- Table structure for table `reports_dictionary`
1412 DROP TABLE IF EXISTS `reports_dictionary`;
1413 CREATE TABLE reports_dictionary (
1414 `id` int(11) NOT NULL auto_increment,
1415 `name` varchar(255) default NULL,
1417 `date_created` datetime default NULL,
1418 `date_modified` datetime default NULL,
1420 `area` int(11) default NULL,
1422 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1425 -- Table structure for table `reserveconstraints`
1428 DROP TABLE IF EXISTS `reserveconstraints`;
1429 CREATE TABLE `reserveconstraints` (
1430 `borrowernumber` int(11) NOT NULL default 0,
1431 `reservedate` date default NULL,
1432 `biblionumber` int(11) NOT NULL default 0,
1433 `biblioitemnumber` int(11) default NULL,
1434 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1435 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1438 -- Table structure for table `reserves`
1441 DROP TABLE IF EXISTS `reserves`;
1442 CREATE TABLE `reserves` (
1443 `borrowernumber` int(11) NOT NULL default 0,
1444 `reservedate` date default NULL,
1445 `biblionumber` int(11) NOT NULL default 0,
1446 `constrainttype` varchar(1) default NULL,
1447 `branchcode` varchar(10) default NULL,
1448 `notificationdate` date default NULL,
1449 `reminderdate` date default NULL,
1450 `cancellationdate` date default NULL,
1451 `reservenotes` mediumtext,
1452 `priority` smallint(6) default NULL,
1453 `found` varchar(1) default NULL,
1454 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1455 `itemnumber` int(11) default NULL,
1456 `waitingdate` date default NULL,
1457 KEY `borrowernumber` (`borrowernumber`),
1458 KEY `biblionumber` (`biblionumber`),
1459 KEY `itemnumber` (`itemnumber`),
1460 KEY `branchcode` (`branchcode`),
1461 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1462 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1463 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1464 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1465 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1468 -- Table structure for table `reviews`
1471 DROP TABLE IF EXISTS `reviews`;
1472 CREATE TABLE `reviews` (
1473 `reviewid` int(11) NOT NULL auto_increment,
1474 `borrowernumber` int(11) default NULL,
1475 `biblionumber` int(11) default NULL,
1477 `approved` tinyint(4) default NULL,
1478 `datereviewed` datetime default NULL,
1479 PRIMARY KEY (`reviewid`)
1480 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1483 -- Table structure for table `roadtype`
1486 DROP TABLE IF EXISTS `roadtype`;
1487 CREATE TABLE `roadtype` (
1488 `roadtypeid` int(11) NOT NULL auto_increment,
1489 `road_type` varchar(100) NOT NULL default '',
1490 PRIMARY KEY (`roadtypeid`)
1491 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1494 -- Table structure for table `saved_sql`
1497 DROP TABLE IF EXISTS `saved_sql`;
1498 CREATE TABLE saved_sql (
1499 `id` int(11) NOT NULL auto_increment,
1500 `borrowernumber` int(11) default NULL,
1501 `date_created` datetime default NULL,
1502 `last_modified` datetime default NULL,
1504 `last_run` datetime default NULL,
1505 `report_name` varchar(255) default NULL,
1506 `type` varchar(255) default NULL,
1509 KEY boridx (`borrowernumber`)
1510 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1514 -- Table structure for `saved_reports`
1517 DROP TABLE IF EXISTS `saved_reports`;
1518 CREATE TABLE saved_reports (
1519 `id` int(11) NOT NULL auto_increment,
1520 `report_id` int(11) default NULL,
1522 `date_run` datetime default NULL,
1524 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1528 -- Table structure for table `serial`
1531 DROP TABLE IF EXISTS `serial`;
1532 CREATE TABLE `serial` (
1533 `serialid` int(11) NOT NULL auto_increment,
1534 `biblionumber` varchar(100) NOT NULL default '',
1535 `subscriptionid` varchar(100) NOT NULL default '',
1536 `serialseq` varchar(100) NOT NULL default '',
1537 `status` tinyint(4) NOT NULL default 0,
1538 `planneddate` date default NULL,
1540 `publisheddate` date default NULL,
1541 `itemnumber` text default NULL,
1542 `claimdate` date default NULL,
1543 `routingnotes` text,
1544 PRIMARY KEY (`serialid`)
1545 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1548 -- Table structure for table `sessions`
1551 DROP TABLE IF EXISTS sessions;
1552 CREATE TABLE sessions (
1553 `id` varchar(32) NOT NULL,
1554 `a_session` text NOT NULL,
1556 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1559 -- Table structure for table `special_holidays`
1562 DROP TABLE IF EXISTS `special_holidays`;
1563 CREATE TABLE `special_holidays` (
1564 `id` int(11) NOT NULL auto_increment,
1565 `branchcode` varchar(10) NOT NULL default '',
1566 `day` smallint(6) NOT NULL default 0,
1567 `month` smallint(6) NOT NULL default 0,
1568 `year` smallint(6) NOT NULL default 0,
1569 `isexception` smallint(1) NOT NULL default 1,
1570 `title` varchar(50) NOT NULL default '',
1571 `description` text NOT NULL,
1573 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1576 -- Table structure for table `statistics`
1579 DROP TABLE IF EXISTS `statistics`;
1580 CREATE TABLE `statistics` (
1581 `datetime` datetime default NULL,
1582 `branch` varchar(10) default NULL,
1583 `proccode` varchar(4) default NULL,
1584 `value` double(16,4) default NULL,
1585 `type` varchar(16) default NULL,
1587 `usercode` varchar(10) default NULL,
1588 `itemnumber` int(11) default NULL,
1589 `itemtype` varchar(10) default NULL,
1590 `borrowernumber` int(11) default NULL,
1591 `associatedborrower` int(11) default NULL,
1592 KEY `timeidx` (`datetime`)
1593 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1596 -- Table structure for table `stopwords`
1599 DROP TABLE IF EXISTS `stopwords`;
1600 CREATE TABLE `stopwords` (
1601 `word` varchar(255) default NULL
1602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1605 -- Table structure for table `subscription`
1608 DROP TABLE IF EXISTS `subscription`;
1609 CREATE TABLE `subscription` (
1610 `biblionumber` int(11) NOT NULL default 0,
1611 `subscriptionid` int(11) NOT NULL auto_increment,
1612 `librarian` varchar(100) default '',
1613 `startdate` date default NULL,
1614 `aqbooksellerid` int(11) default 0,
1615 `cost` int(11) default 0,
1616 `aqbudgetid` int(11) default 0,
1617 `weeklength` int(11) default 0,
1618 `monthlength` int(11) default 0,
1619 `numberlength` int(11) default 0,
1620 `periodicity` tinyint(4) default 0,
1621 `dow` varchar(100) default '',
1622 `numberingmethod` varchar(100) default '',
1624 `status` varchar(100) NOT NULL default '',
1625 `add1` int(11) default 0,
1626 `every1` int(11) default 0,
1627 `whenmorethan1` int(11) default 0,
1628 `setto1` int(11) default NULL,
1629 `lastvalue1` int(11) default NULL,
1630 `add2` int(11) default 0,
1631 `every2` int(11) default 0,
1632 `whenmorethan2` int(11) default 0,
1633 `setto2` int(11) default NULL,
1634 `lastvalue2` int(11) default NULL,
1635 `add3` int(11) default 0,
1636 `every3` int(11) default 0,
1637 `innerloop1` int(11) default 0,
1638 `innerloop2` int(11) default 0,
1639 `innerloop3` int(11) default 0,
1640 `whenmorethan3` int(11) default 0,
1641 `setto3` int(11) default NULL,
1642 `lastvalue3` int(11) default NULL,
1643 `issuesatonce` tinyint(3) NOT NULL default 1,
1644 `firstacquidate` date default NULL,
1645 `manualhistory` tinyint(1) NOT NULL default 0,
1646 `irregularity` text,
1647 `letter` varchar(20) default NULL,
1648 `numberpattern` tinyint(3) default 0,
1649 `distributedto` text,
1650 `internalnotes` longtext,
1652 `location` varchar(80) NULL default '',
1653 `branchcode` varchar(10) NOT NULL default '',
1654 `hemisphere` tinyint(3) default 0,
1655 `lastbranch` varchar(10),
1656 `serialsadditems` tinyint(1) NOT NULL default '0',
1657 `staffdisplaycount` VARCHAR(10) NULL,
1658 `opacdisplaycount` VARCHAR(10) NULL,
1659 `graceperiod` int(11) NOT NULL default '0',
1660 PRIMARY KEY (`subscriptionid`)
1661 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1664 -- Table structure for table `subscriptionhistory`
1667 DROP TABLE IF EXISTS `subscriptionhistory`;
1668 CREATE TABLE `subscriptionhistory` (
1669 `biblionumber` int(11) NOT NULL default 0,
1670 `subscriptionid` int(11) NOT NULL default 0,
1671 `histstartdate` date default NULL,
1672 `enddate` date default NULL,
1673 `missinglist` longtext NOT NULL,
1674 `recievedlist` longtext NOT NULL,
1675 `opacnote` varchar(150) NOT NULL default '',
1676 `librariannote` varchar(150) NOT NULL default '',
1677 PRIMARY KEY (`subscriptionid`),
1678 KEY `biblionumber` (`biblionumber`)
1679 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1682 -- Table structure for table `subscriptionroutinglist`
1685 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1686 CREATE TABLE `subscriptionroutinglist` (
1687 `routingid` int(11) NOT NULL auto_increment,
1688 `borrowernumber` int(11) default NULL,
1689 `ranking` int(11) default NULL,
1690 `subscriptionid` int(11) default NULL,
1691 PRIMARY KEY (`routingid`)
1692 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1695 -- Table structure for table `suggestions`
1698 DROP TABLE IF EXISTS `suggestions`;
1699 CREATE TABLE `suggestions` (
1700 `suggestionid` int(8) NOT NULL auto_increment,
1701 `suggestedby` int(11) NOT NULL default 0,
1702 `suggesteddate` date NOT NULL default 0,
1703 `managedby` int(11) default NULL,
1704 `manageddate` date default NULL,
1705 `STATUS` varchar(10) NOT NULL default '',
1707 `author` varchar(80) default NULL,
1708 `title` varchar(80) default NULL,
1709 `copyrightdate` smallint(6) default NULL,
1710 `publishercode` varchar(255) default NULL,
1711 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1712 `volumedesc` varchar(255) default NULL,
1713 `publicationyear` smallint(6) default 0,
1714 `place` varchar(255) default NULL,
1715 `isbn` varchar(30) default NULL,
1716 `mailoverseeing` smallint(1) default 0,
1717 `biblionumber` int(11) default NULL,
1719 PRIMARY KEY (`suggestionid`),
1720 KEY `suggestedby` (`suggestedby`),
1721 KEY `managedby` (`managedby`)
1722 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1725 -- Table structure for table `systempreferences`
1728 DROP TABLE IF EXISTS `systempreferences`;
1729 CREATE TABLE `systempreferences` (
1730 `variable` varchar(50) NOT NULL default '',
1732 `options` mediumtext,
1734 `type` varchar(20) default NULL,
1735 PRIMARY KEY (`variable`)
1736 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1739 -- Table structure for table `tags`
1742 DROP TABLE IF EXISTS `tags`;
1743 CREATE TABLE `tags` (
1744 `entry` varchar(255) NOT NULL default '',
1745 `weight` bigint(20) NOT NULL default 0,
1746 PRIMARY KEY (`entry`)
1747 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1750 -- Table structure for table `tags_all`
1753 DROP TABLE IF EXISTS `tags_all`;
1754 CREATE TABLE `tags_all` (
1755 `tag_id` int(11) NOT NULL auto_increment,
1756 `borrowernumber` int(11) NOT NULL,
1757 `biblionumber` int(11) NOT NULL,
1758 `term` varchar(255) NOT NULL,
1759 `language` int(4) default NULL,
1760 `date_created` datetime NOT NULL,
1761 PRIMARY KEY (`tag_id`),
1762 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1763 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1764 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1765 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1766 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1767 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1768 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1771 -- Table structure for table `tags_approval`
1774 DROP TABLE IF EXISTS `tags_approval`;
1775 CREATE TABLE `tags_approval` (
1776 `term` varchar(255) NOT NULL,
1777 `approved` int(1) NOT NULL default '0',
1778 `date_approved` datetime default NULL,
1779 `approved_by` int(11) default NULL,
1780 `weight_total` int(9) NOT NULL default '1',
1781 PRIMARY KEY (`term`),
1782 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1783 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1784 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1785 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1788 -- Table structure for table `tags_index`
1791 DROP TABLE IF EXISTS `tags_index`;
1792 CREATE TABLE `tags_index` (
1793 `term` varchar(255) NOT NULL,
1794 `biblionumber` int(11) NOT NULL,
1795 `weight` int(9) NOT NULL default '1',
1796 PRIMARY KEY (`term`,`biblionumber`),
1797 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1798 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1799 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1800 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1801 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1802 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1805 -- Table structure for table `userflags`
1808 DROP TABLE IF EXISTS `userflags`;
1809 CREATE TABLE `userflags` (
1810 `bit` int(11) NOT NULL default 0,
1811 `flag` varchar(30) default NULL,
1812 `flagdesc` varchar(255) default NULL,
1813 `defaulton` int(11) default NULL,
1815 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1818 -- Table structure for table `virtualshelves`
1821 DROP TABLE IF EXISTS `virtualshelves`;
1822 CREATE TABLE `virtualshelves` (
1823 `shelfnumber` int(11) NOT NULL auto_increment,
1824 `shelfname` varchar(255) default NULL,
1825 `owner` varchar(80) default NULL,
1826 `category` varchar(1) default NULL,
1827 `sortfield` varchar(16) default NULL,
1828 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1829 PRIMARY KEY (`shelfnumber`)
1830 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1833 -- Table structure for table `virtualshelfcontents`
1836 DROP TABLE IF EXISTS `virtualshelfcontents`;
1837 CREATE TABLE `virtualshelfcontents` (
1838 `shelfnumber` int(11) NOT NULL default 0,
1839 `biblionumber` int(11) NOT NULL default 0,
1840 `flags` int(11) default NULL,
1841 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1842 KEY `shelfnumber` (`shelfnumber`),
1843 KEY `biblionumber` (`biblionumber`),
1844 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1845 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1846 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1849 -- Table structure for table `z3950servers`
1852 DROP TABLE IF EXISTS `z3950servers`;
1853 CREATE TABLE `z3950servers` (
1854 `host` varchar(255) default NULL,
1855 `port` int(11) default NULL,
1856 `db` varchar(255) default NULL,
1857 `userid` varchar(255) default NULL,
1858 `password` varchar(255) default NULL,
1860 `id` int(11) NOT NULL auto_increment,
1861 `checked` smallint(6) default NULL,
1862 `rank` int(11) default NULL,
1863 `syntax` varchar(80) default NULL,
1865 `position` enum('primary','secondary','') NOT NULL default 'primary',
1866 `type` enum('zed','opensearch') NOT NULL default 'zed',
1867 `encoding` text default NULL,
1868 `description` text NOT NULL,
1870 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1873 -- Table structure for table `zebraqueue`
1876 DROP TABLE IF EXISTS `zebraqueue`;
1877 CREATE TABLE `zebraqueue` (
1878 `id` int(11) NOT NULL auto_increment,
1879 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1880 `operation` char(20) NOT NULL default '',
1881 `server` char(20) NOT NULL default '',
1882 `done` int(11) NOT NULL default '0',
1883 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1885 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1886 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1888 DROP TABLE IF EXISTS `services_throttle`;
1889 CREATE TABLE `services_throttle` (
1890 `service_type` varchar(10) NOT NULL default '',
1891 `service_count` varchar(45) default NULL,
1892 PRIMARY KEY (`service_type`)
1893 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1895 -- http://www.w3.org/International/articles/language-tags/
1898 DROP TABLE IF EXISTS language_subtag_registry;
1899 CREATE TABLE language_subtag_registry (
1901 type varchar(25), -- language-script-region-variant-extension-privateuse
1902 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1904 id int(11) NOT NULL auto_increment,
1906 KEY `subtag` (`subtag`)
1907 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1909 -- TODO: add suppress_scripts
1910 -- this maps three letter codes defined in iso639.2 back to their
1911 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1912 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1913 CREATE TABLE language_rfc4646_to_iso639 (
1914 rfc4646_subtag varchar(25),
1915 iso639_2_code varchar(25),
1916 id int(11) NOT NULL auto_increment,
1918 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1919 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1921 DROP TABLE IF EXISTS language_descriptions;
1922 CREATE TABLE language_descriptions (
1926 description varchar(255),
1927 id int(11) NOT NULL auto_increment,
1930 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1932 -- bi-directional support, keyed by script subcode
1933 DROP TABLE IF EXISTS language_script_bidi;
1934 CREATE TABLE language_script_bidi (
1935 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1936 bidi varchar(3), -- rtl ltr
1937 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1938 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1940 -- TODO: need to map language subtags to script subtags for detection
1941 -- of bidi when script is not specified (like ar, he)
1942 DROP TABLE IF EXISTS language_script_mapping;
1943 CREATE TABLE language_script_mapping (
1944 language_subtag varchar(25),
1945 script_subtag varchar(25),
1946 KEY `language_subtag` (`language_subtag`)
1947 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1949 DROP TABLE IF EXISTS `permissions`;
1950 CREATE TABLE `permissions` (
1951 `module_bit` int(11) NOT NULL DEFAULT 0,
1952 `code` varchar(64) DEFAULT NULL,
1953 `description` varchar(255) DEFAULT NULL,
1954 PRIMARY KEY (`module_bit`, `code`),
1955 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1956 ON DELETE CASCADE ON UPDATE CASCADE
1957 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1959 DROP TABLE IF EXISTS `serialitems`;
1960 CREATE TABLE `serialitems` (
1961 `itemnumber` int(11) NOT NULL,
1962 `serialid` int(11) NOT NULL,
1963 UNIQUE KEY `serialitemsidx` (`itemnumber`),
1964 KEY `serialitems_sfk_1` (`serialid`),
1965 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
1966 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1968 DROP TABLE IF EXISTS `user_permissions`;
1969 CREATE TABLE `user_permissions` (
1970 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1971 `module_bit` int(11) NOT NULL DEFAULT 0,
1972 `code` varchar(64) DEFAULT NULL,
1973 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1974 ON DELETE CASCADE ON UPDATE CASCADE,
1975 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
1976 ON DELETE CASCADE ON UPDATE CASCADE
1977 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1980 -- Table structure for table `tmp_holdsqueue`
1983 DROP TABLE IF EXISTS `tmp_holdsqueue`;
1984 CREATE TABLE `tmp_holdsqueue` (
1985 `biblionumber` int(11) default NULL,
1986 `itemnumber` int(11) default NULL,
1987 `barcode` varchar(20) default NULL,
1988 `surname` mediumtext NOT NULL,
1991 `borrowernumber` int(11) NOT NULL,
1992 `cardnumber` varchar(16) default NULL,
1993 `reservedate` date default NULL,
1995 `itemcallnumber` varchar(255) default NULL,
1996 `holdingbranch` varchar(10) default NULL,
1997 `pickbranch` varchar(10) default NULL,
1999 `item_level_request` tinyint(4) NOT NULL default 0
2000 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2003 -- Table structure for table `message_queue`
2006 DROP TABLE IF EXISTS `message_queue`;
2007 CREATE TABLE `message_queue` (
2008 `message_id` int(11) NOT NULL auto_increment,
2009 `borrowernumber` int(11) default NULL,
2012 `metadata` text DEFAULT NULL,
2013 `letter_code` varchar(64) DEFAULT NULL,
2014 `message_transport_type` varchar(20) NOT NULL,
2015 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2016 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2017 `to_address` mediumtext,
2018 `from_address` mediumtext,
2019 `content_type` text,
2020 KEY `message_id` (`message_id`),
2021 KEY `borrowernumber` (`borrowernumber`),
2022 KEY `message_transport_type` (`message_transport_type`),
2023 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2024 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2028 -- Table structure for table `message_transport_types`
2031 DROP TABLE IF EXISTS `message_transport_types`;
2032 CREATE TABLE `message_transport_types` (
2033 `message_transport_type` varchar(20) NOT NULL,
2034 PRIMARY KEY (`message_transport_type`)
2035 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2038 -- Table structure for table `message_attributes`
2041 DROP TABLE IF EXISTS `message_attributes`;
2042 CREATE TABLE `message_attributes` (
2043 `message_attribute_id` int(11) NOT NULL auto_increment,
2044 `message_name` varchar(20) NOT NULL default '',
2045 `takes_days` tinyint(1) NOT NULL default '0',
2046 PRIMARY KEY (`message_attribute_id`),
2047 UNIQUE KEY `message_name` (`message_name`)
2048 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2051 -- Table structure for table `message_transports`
2054 DROP TABLE IF EXISTS `message_transports`;
2055 CREATE TABLE `message_transports` (
2056 `message_attribute_id` int(11) NOT NULL,
2057 `message_transport_type` varchar(20) NOT NULL,
2058 `is_digest` tinyint(1) NOT NULL default '0',
2059 `letter_module` varchar(20) NOT NULL default '',
2060 `letter_code` varchar(20) NOT NULL default '',
2061 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2062 KEY `message_transport_type` (`message_transport_type`),
2063 KEY `letter_module` (`letter_module`,`letter_code`),
2064 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2065 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2066 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2067 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2070 -- Table structure for table `borrower_message_preferences`
2073 DROP TABLE IF EXISTS `borrower_message_preferences`;
2074 CREATE TABLE `borrower_message_preferences` (
2075 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2076 `borrowernumber` int(11) default NULL,
2077 `categorycode` varchar(10) default NULL,
2078 `message_attribute_id` int(11) default '0',
2079 `days_in_advance` int(11) default '0',
2080 `wants_digest` tinyint(1) NOT NULL default '0',
2081 PRIMARY KEY (`borrower_message_preference_id`),
2082 KEY `borrowernumber` (`borrowernumber`),
2083 KEY `categorycode` (`categorycode`),
2084 KEY `message_attribute_id` (`message_attribute_id`),
2085 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2086 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2087 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2088 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2091 -- Table structure for table `borrower_message_transport_preferences`
2094 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2095 CREATE TABLE `borrower_message_transport_preferences` (
2096 `borrower_message_preference_id` int(11) NOT NULL default '0',
2097 `message_transport_type` varchar(20) NOT NULL default '0',
2098 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2099 KEY `message_transport_type` (`message_transport_type`),
2100 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,
2101 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
2102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2105 -- Table structure for the table branch_transfer_limits
2108 DROP TABLE IF EXISTS `branch_transfer_limits`;
2109 CREATE TABLE branch_transfer_limits (
2110 limitId int(8) NOT NULL auto_increment,
2111 toBranch varchar(10) NOT NULL,
2112 fromBranch varchar(10) NOT NULL,
2113 itemtype varchar(10) NULL,
2114 ccode varchar(10) NULL,
2115 PRIMARY KEY (limitId)
2116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2119 -- Table structure for table `item_circulation_alert_preferences`
2122 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2123 CREATE TABLE `item_circulation_alert_preferences` (
2124 `id` int(11) NOT NULL auto_increment,
2125 `branchcode` varchar(10) NOT NULL,
2126 `categorycode` varchar(10) NOT NULL,
2127 `item_type` varchar(10) NOT NULL,
2128 `notification` varchar(16) NOT NULL,
2130 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2134 -- Table structure for table `messages`
2137 CREATE TABLE `messages` (
2138 `message_id` int(11) NOT NULL auto_increment,
2139 `borrowernumber` int(11) NOT NULL,
2140 `branchcode` varchar(4) default NULL,
2141 `message_type` varchar(1) NOT NULL,
2142 `message` text NOT NULL,
2143 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2144 PRIMARY KEY (`message_id`)
2145 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2148 -- Table structure for table `accountlines`
2151 DROP TABLE IF EXISTS `accountlines`;
2152 CREATE TABLE `accountlines` (
2153 `borrowernumber` int(11) NOT NULL default 0,
2154 `accountno` smallint(6) NOT NULL default 0,
2155 `itemnumber` int(11) default NULL,
2156 `date` date default NULL,
2157 `amount` decimal(28,6) default NULL,
2158 `description` mediumtext,
2159 `dispute` mediumtext,
2160 `accounttype` varchar(5) default NULL,
2161 `amountoutstanding` decimal(28,6) default NULL,
2162 `lastincrement` decimal(28,6) default NULL,
2163 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2164 `notify_id` int(11) NOT NULL default 0,
2165 `notify_level` int(2) NOT NULL default 0,
2166 KEY `acctsborridx` (`borrowernumber`),
2167 KEY `timeidx` (`timestamp`),
2168 KEY `itemnumber` (`itemnumber`),
2169 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2170 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2171 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2174 -- Table structure for table `accountoffsets`
2177 DROP TABLE IF EXISTS `accountoffsets`;
2178 CREATE TABLE `accountoffsets` (
2179 `borrowernumber` int(11) NOT NULL default 0,
2180 `accountno` smallint(6) NOT NULL default 0,
2181 `offsetaccount` smallint(6) NOT NULL default 0,
2182 `offsetamount` decimal(28,6) default NULL,
2183 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2184 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2188 -- Table structure for table `action_logs`
2191 DROP TABLE IF EXISTS `action_logs`;
2192 CREATE TABLE `action_logs` (
2193 `action_id` int(11) NOT NULL auto_increment,
2194 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2195 `user` int(11) NOT NULL default 0,
2198 `object` int(11) default NULL,
2200 PRIMARY KEY (`action_id`),
2201 KEY (`timestamp`,`user`)
2202 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2205 -- Table structure for table `alert`
2208 DROP TABLE IF EXISTS `alert`;
2209 CREATE TABLE `alert` (
2210 `alertid` int(11) NOT NULL auto_increment,
2211 `borrowernumber` int(11) NOT NULL default 0,
2212 `type` varchar(10) NOT NULL default '',
2213 `externalid` varchar(20) NOT NULL default '',
2214 PRIMARY KEY (`alertid`),
2215 KEY `borrowernumber` (`borrowernumber`),
2216 KEY `type` (`type`,`externalid`)
2217 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2220 -- Table structure for table `aqbasketgroups`
2223 DROP TABLE IF EXISTS `aqbasketgroups`;
2224 CREATE TABLE `aqbasketgroups` (
2225 `id` int(11) NOT NULL auto_increment,
2226 `name` varchar(50) default NULL,
2227 `closed` tinyint(1) default NULL,
2228 `booksellerid` int(11) NOT NULL,
2230 KEY `booksellerid` (`booksellerid`),
2231 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2232 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2235 -- Table structure for table `aqbasket`
2238 DROP TABLE IF EXISTS `aqbasket`;
2239 CREATE TABLE `aqbasket` (
2240 `basketno` int(11) NOT NULL auto_increment,
2241 `basketname` varchar(50) default NULL,
2243 `booksellernote` mediumtext,
2244 `contractnumber` int(11),
2245 `creationdate` date default NULL,
2246 `closedate` date default NULL,
2247 `booksellerid` int(11) NOT NULL default 1,
2248 `authorisedby` varchar(10) default NULL,
2249 `booksellerinvoicenumber` mediumtext,
2250 `basketgroupid` int(11),
2251 PRIMARY KEY (`basketno`),
2252 KEY `booksellerid` (`booksellerid`),
2253 KEY `basketgroupid` (`basketgroupid`),
2254 KEY `contractnumber` (`contractnumber`),
2255 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2256 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2257 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2258 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2261 -- Table structure for table `aqbooksellers`
2264 DROP TABLE IF EXISTS `aqbooksellers`;
2265 CREATE TABLE `aqbooksellers` (
2266 `id` int(11) NOT NULL auto_increment,
2267 `name` mediumtext NOT NULL,
2268 `address1` mediumtext,
2269 `address2` mediumtext,
2270 `address3` mediumtext,
2271 `address4` mediumtext,
2272 `phone` varchar(30) default NULL,
2273 `accountnumber` mediumtext,
2274 `othersupplier` mediumtext,
2275 `currency` varchar(3) NOT NULL default '',
2276 `deliverydays` smallint(6) default NULL,
2277 `followupdays` smallint(6) default NULL,
2278 `followupscancel` smallint(6) default NULL,
2279 `specialty` mediumtext,
2280 `booksellerfax` mediumtext,
2282 `bookselleremail` mediumtext,
2283 `booksellerurl` mediumtext,
2284 `contact` varchar(100) default NULL,
2285 `postal` mediumtext,
2286 `url` varchar(255) default NULL,
2287 `contpos` varchar(100) default NULL,
2288 `contphone` varchar(100) default NULL,
2289 `contfax` varchar(100) default NULL,
2290 `contaltphone` varchar(100) default NULL,
2291 `contemail` varchar(100) default NULL,
2292 `contnotes` mediumtext,
2293 `active` tinyint(4) default NULL,
2294 `listprice` varchar(10) default NULL,
2295 `invoiceprice` varchar(10) default NULL,
2296 `gstreg` tinyint(4) default NULL,
2297 `listincgst` tinyint(4) default NULL,
2298 `invoiceincgst` tinyint(4) default NULL,
2299 `gstrate` decimal(6,4) default NULL,
2300 `discount` float(6,4) default NULL,
2301 `fax` varchar(50) default NULL,
2302 `nocalc` int(11) default NULL,
2303 `invoicedisc` float(6,4) default NULL,
2305 KEY `listprice` (`listprice`),
2306 KEY `invoiceprice` (`invoiceprice`),
2307 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2308 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2309 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2312 -- Table structure for table `aqbudgets`
2315 DROP TABLE IF EXISTS `aqbudgets`;
2316 CREATE TABLE `aqbudgets` (
2317 `budget_id` int(11) NOT NULL auto_increment,
2318 `budget_parent_id` int(11) default NULL,
2319 `budget_code` varchar(30) default NULL,
2320 `budget_name` varchar(80) default NULL,
2321 `budget_branchcode` varchar(10) default NULL,
2322 `budget_amount` decimal(28,6) NULL default '0.00',
2323 `budget_encumb` decimal(28,6) NULL default '0.00',
2324 `budget_expend` decimal(28,6) NULL default '0.00',
2325 `budget_notes` mediumtext,
2326 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2327 `budget_period_id` int(11) default NULL,
2328 `sort1_authcat` varchar(80) default NULL,
2329 `sort2_authcat` varchar(80) default NULL,
2330 `budget_owner_id` int(11) default NULL,
2331 `budget_permission` int(1) default '0',
2332 PRIMARY KEY (`budget_id`)
2333 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2337 -- Table structure for table `aqbudgetperiods`
2341 DROP TABLE IF EXISTS `aqbudgetperiods`;
2342 CREATE TABLE `aqbudgetperiods` (
2343 `budget_period_id` int(11) NOT NULL auto_increment,
2344 `budget_period_startdate` date NOT NULL,
2345 `budget_period_enddate` date NOT NULL,
2346 `budget_period_active` tinyint(1) default '0',
2347 `budget_period_description` mediumtext,
2348 `budget_period_total` decimal(28,6),
2349 `budget_period_locked` tinyint(1) default NULL,
2350 `sort1_authcat` varchar(10) default NULL,
2351 `sort2_authcat` varchar(10) default NULL,
2352 PRIMARY KEY (`budget_period_id`)
2353 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2356 -- Table structure for table `aqbudgets_planning`
2359 DROP TABLE IF EXISTS `aqbudgets_planning`;
2360 CREATE TABLE `aqbudgets_planning` (
2361 `plan_id` int(11) NOT NULL auto_increment,
2362 `budget_id` int(11) NOT NULL,
2363 `budget_period_id` int(11) NOT NULL,
2364 `estimated_amount` decimal(28,6) default NULL,
2365 `authcat` varchar(30) NOT NULL,
2366 `authvalue` varchar(30) NOT NULL,
2367 PRIMARY KEY (`plan_id`),
2368 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2369 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2372 -- Table structure for table 'aqcontract'
2375 DROP TABLE IF EXISTS `aqcontract`;
2376 CREATE TABLE `aqcontract` (
2377 `contractnumber` int(11) NOT NULL auto_increment,
2378 `contractstartdate` date default NULL,
2379 `contractenddate` date default NULL,
2380 `contractname` varchar(50) default NULL,
2381 `contractdescription` mediumtext,
2382 `booksellerid` int(11) not NULL,
2383 PRIMARY KEY (`contractnumber`),
2384 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2385 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2386 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2389 -- Table structure for table `aqorderdelivery`
2392 DROP TABLE IF EXISTS `aqorderdelivery`;
2393 CREATE TABLE `aqorderdelivery` (
2394 `ordernumber` date default NULL,
2395 `deliverynumber` smallint(6) NOT NULL default 0,
2396 `deliverydate` varchar(18) default NULL,
2397 `qtydelivered` smallint(6) default NULL,
2398 `deliverycomments` mediumtext
2399 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2402 -- Table structure for table `aqorders`
2405 DROP TABLE IF EXISTS `aqorders`;
2406 CREATE TABLE `aqorders` (
2407 `ordernumber` int(11) NOT NULL auto_increment,
2408 `biblionumber` int(11) default NULL,
2409 `entrydate` date default NULL,
2410 `quantity` smallint(6) default NULL,
2411 `currency` varchar(3) default NULL,
2412 `listprice` decimal(28,6) default NULL,
2413 `totalamount` decimal(28,6) default NULL,
2414 `datereceived` date default NULL,
2415 `booksellerinvoicenumber` mediumtext,
2416 `freight` decimal(28,6) default NULL,
2417 `unitprice` decimal(28,6) default NULL,
2418 `quantityreceived` smallint(6) default NULL,
2419 `cancelledby` varchar(10) default NULL,
2420 `datecancellationprinted` date default NULL,
2422 `supplierreference` mediumtext,
2423 `purchaseordernumber` mediumtext,
2424 `subscription` tinyint(1) default NULL,
2425 `serialid` varchar(30) default NULL,
2426 `basketno` int(11) default NULL,
2427 `biblioitemnumber` int(11) default NULL,
2428 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2429 `rrp` decimal(13,2) default NULL,
2430 `ecost` decimal(13,2) default NULL,
2431 `gst` decimal(13,2) default NULL,
2432 `budget_id` int(11) NOT NULL,
2433 `budgetgroup_id` int(11) NOT NULL,
2434 `budgetdate` date default NULL,
2435 `sort1` varchar(80) default NULL,
2436 `sort2` varchar(80) default NULL,
2437 `sort1_authcat` varchar(10) default NULL,
2438 `sort2_authcat` varchar(10) default NULL,
2439 `uncertainprice` tinyint(1),
2440 PRIMARY KEY (`ordernumber`),
2441 KEY `basketno` (`basketno`),
2442 KEY `biblionumber` (`biblionumber`),
2443 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2444 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2445 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2448 -- Table structure for table `aqorders_items`
2451 DROP TABLE IF EXISTS `aqorders_items`;
2452 CREATE TABLE `aqorders_items` (
2453 `ordernumber` int(11) NOT NULL,
2454 `itemnumber` int(11) NOT NULL,
2455 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2456 PRIMARY KEY (`itemnumber`),
2457 KEY `ordernumber` (`ordernumber`)
2458 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2460 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2461 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2462 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2463 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2464 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2465 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2466 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2467 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;