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 `managedby` int(11) default NULL,
1703 `STATUS` varchar(10) NOT NULL default '',
1705 `author` varchar(80) default NULL,
1706 `title` varchar(80) default NULL,
1707 `copyrightdate` smallint(6) default NULL,
1708 `publishercode` varchar(255) default NULL,
1709 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1710 `volumedesc` varchar(255) default NULL,
1711 `publicationyear` smallint(6) default 0,
1712 `place` varchar(255) default NULL,
1713 `isbn` varchar(30) default NULL,
1714 `mailoverseeing` smallint(1) default 0,
1715 `biblionumber` int(11) default NULL,
1717 PRIMARY KEY (`suggestionid`),
1718 KEY `suggestedby` (`suggestedby`),
1719 KEY `managedby` (`managedby`)
1720 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1723 -- Table structure for table `systempreferences`
1726 DROP TABLE IF EXISTS `systempreferences`;
1727 CREATE TABLE `systempreferences` (
1728 `variable` varchar(50) NOT NULL default '',
1730 `options` mediumtext,
1732 `type` varchar(20) default NULL,
1733 PRIMARY KEY (`variable`)
1734 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1737 -- Table structure for table `tags`
1740 DROP TABLE IF EXISTS `tags`;
1741 CREATE TABLE `tags` (
1742 `entry` varchar(255) NOT NULL default '',
1743 `weight` bigint(20) NOT NULL default 0,
1744 PRIMARY KEY (`entry`)
1745 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1748 -- Table structure for table `tags_all`
1751 DROP TABLE IF EXISTS `tags_all`;
1752 CREATE TABLE `tags_all` (
1753 `tag_id` int(11) NOT NULL auto_increment,
1754 `borrowernumber` int(11) NOT NULL,
1755 `biblionumber` int(11) NOT NULL,
1756 `term` varchar(255) NOT NULL,
1757 `language` int(4) default NULL,
1758 `date_created` datetime NOT NULL,
1759 PRIMARY KEY (`tag_id`),
1760 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1761 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1762 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1763 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1764 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1765 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1766 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1769 -- Table structure for table `tags_approval`
1772 DROP TABLE IF EXISTS `tags_approval`;
1773 CREATE TABLE `tags_approval` (
1774 `term` varchar(255) NOT NULL,
1775 `approved` int(1) NOT NULL default '0',
1776 `date_approved` datetime default NULL,
1777 `approved_by` int(11) default NULL,
1778 `weight_total` int(9) NOT NULL default '1',
1779 PRIMARY KEY (`term`),
1780 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1781 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1782 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1783 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1786 -- Table structure for table `tags_index`
1789 DROP TABLE IF EXISTS `tags_index`;
1790 CREATE TABLE `tags_index` (
1791 `term` varchar(255) NOT NULL,
1792 `biblionumber` int(11) NOT NULL,
1793 `weight` int(9) NOT NULL default '1',
1794 PRIMARY KEY (`term`,`biblionumber`),
1795 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1796 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1797 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1798 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1799 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1800 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1803 -- Table structure for table `userflags`
1806 DROP TABLE IF EXISTS `userflags`;
1807 CREATE TABLE `userflags` (
1808 `bit` int(11) NOT NULL default 0,
1809 `flag` varchar(30) default NULL,
1810 `flagdesc` varchar(255) default NULL,
1811 `defaulton` int(11) default NULL,
1813 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1816 -- Table structure for table `virtualshelves`
1819 DROP TABLE IF EXISTS `virtualshelves`;
1820 CREATE TABLE `virtualshelves` (
1821 `shelfnumber` int(11) NOT NULL auto_increment,
1822 `shelfname` varchar(255) default NULL,
1823 `owner` varchar(80) default NULL,
1824 `category` varchar(1) default NULL,
1825 `sortfield` varchar(16) default NULL,
1826 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1827 PRIMARY KEY (`shelfnumber`)
1828 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1831 -- Table structure for table `virtualshelfcontents`
1834 DROP TABLE IF EXISTS `virtualshelfcontents`;
1835 CREATE TABLE `virtualshelfcontents` (
1836 `shelfnumber` int(11) NOT NULL default 0,
1837 `biblionumber` int(11) NOT NULL default 0,
1838 `flags` int(11) default NULL,
1839 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1840 KEY `shelfnumber` (`shelfnumber`),
1841 KEY `biblionumber` (`biblionumber`),
1842 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1843 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1844 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1847 -- Table structure for table `z3950servers`
1850 DROP TABLE IF EXISTS `z3950servers`;
1851 CREATE TABLE `z3950servers` (
1852 `host` varchar(255) default NULL,
1853 `port` int(11) default NULL,
1854 `db` varchar(255) default NULL,
1855 `userid` varchar(255) default NULL,
1856 `password` varchar(255) default NULL,
1858 `id` int(11) NOT NULL auto_increment,
1859 `checked` smallint(6) default NULL,
1860 `rank` int(11) default NULL,
1861 `syntax` varchar(80) default NULL,
1863 `position` enum('primary','secondary','') NOT NULL default 'primary',
1864 `type` enum('zed','opensearch') NOT NULL default 'zed',
1865 `encoding` text default NULL,
1866 `description` text NOT NULL,
1868 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1871 -- Table structure for table `zebraqueue`
1874 DROP TABLE IF EXISTS `zebraqueue`;
1875 CREATE TABLE `zebraqueue` (
1876 `id` int(11) NOT NULL auto_increment,
1877 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1878 `operation` char(20) NOT NULL default '',
1879 `server` char(20) NOT NULL default '',
1880 `done` int(11) NOT NULL default '0',
1881 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1883 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1884 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1886 DROP TABLE IF EXISTS `services_throttle`;
1887 CREATE TABLE `services_throttle` (
1888 `service_type` varchar(10) NOT NULL default '',
1889 `service_count` varchar(45) default NULL,
1890 PRIMARY KEY (`service_type`)
1891 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1893 -- http://www.w3.org/International/articles/language-tags/
1896 DROP TABLE IF EXISTS language_subtag_registry;
1897 CREATE TABLE language_subtag_registry (
1899 type varchar(25), -- language-script-region-variant-extension-privateuse
1900 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1902 id int(11) NOT NULL auto_increment,
1904 KEY `subtag` (`subtag`)
1905 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1907 -- TODO: add suppress_scripts
1908 -- this maps three letter codes defined in iso639.2 back to their
1909 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1910 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1911 CREATE TABLE language_rfc4646_to_iso639 (
1912 rfc4646_subtag varchar(25),
1913 iso639_2_code varchar(25),
1914 id int(11) NOT NULL auto_increment,
1916 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1917 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1919 DROP TABLE IF EXISTS language_descriptions;
1920 CREATE TABLE language_descriptions (
1924 description varchar(255),
1925 id int(11) NOT NULL auto_increment,
1928 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1930 -- bi-directional support, keyed by script subcode
1931 DROP TABLE IF EXISTS language_script_bidi;
1932 CREATE TABLE language_script_bidi (
1933 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1934 bidi varchar(3), -- rtl ltr
1935 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1936 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1938 -- TODO: need to map language subtags to script subtags for detection
1939 -- of bidi when script is not specified (like ar, he)
1940 DROP TABLE IF EXISTS language_script_mapping;
1941 CREATE TABLE language_script_mapping (
1942 language_subtag varchar(25),
1943 script_subtag varchar(25),
1944 KEY `language_subtag` (`language_subtag`)
1945 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1947 DROP TABLE IF EXISTS `permissions`;
1948 CREATE TABLE `permissions` (
1949 `module_bit` int(11) NOT NULL DEFAULT 0,
1950 `code` varchar(64) DEFAULT NULL,
1951 `description` varchar(255) DEFAULT NULL,
1952 PRIMARY KEY (`module_bit`, `code`),
1953 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1954 ON DELETE CASCADE ON UPDATE CASCADE
1955 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1957 DROP TABLE IF EXISTS `serialitems`;
1958 CREATE TABLE `serialitems` (
1959 `itemnumber` int(11) NOT NULL,
1960 `serialid` int(11) NOT NULL,
1961 UNIQUE KEY `serialitemsidx` (`itemnumber`),
1962 KEY `serialitems_sfk_1` (`serialid`),
1963 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
1964 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1966 DROP TABLE IF EXISTS `user_permissions`;
1967 CREATE TABLE `user_permissions` (
1968 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1969 `module_bit` int(11) NOT NULL DEFAULT 0,
1970 `code` varchar(64) DEFAULT NULL,
1971 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1972 ON DELETE CASCADE ON UPDATE CASCADE,
1973 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
1974 ON DELETE CASCADE ON UPDATE CASCADE
1975 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1978 -- Table structure for table `tmp_holdsqueue`
1981 DROP TABLE IF EXISTS `tmp_holdsqueue`;
1982 CREATE TABLE `tmp_holdsqueue` (
1983 `biblionumber` int(11) default NULL,
1984 `itemnumber` int(11) default NULL,
1985 `barcode` varchar(20) default NULL,
1986 `surname` mediumtext NOT NULL,
1989 `borrowernumber` int(11) NOT NULL,
1990 `cardnumber` varchar(16) default NULL,
1991 `reservedate` date default NULL,
1993 `itemcallnumber` varchar(255) default NULL,
1994 `holdingbranch` varchar(10) default NULL,
1995 `pickbranch` varchar(10) default NULL,
1997 `item_level_request` tinyint(4) NOT NULL default 0
1998 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2001 -- Table structure for table `message_queue`
2004 DROP TABLE IF EXISTS `message_queue`;
2005 CREATE TABLE `message_queue` (
2006 `message_id` int(11) NOT NULL auto_increment,
2007 `borrowernumber` int(11) default NULL,
2010 `metadata` text DEFAULT NULL,
2011 `letter_code` varchar(64) DEFAULT NULL,
2012 `message_transport_type` varchar(20) NOT NULL,
2013 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2014 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2015 `to_address` mediumtext,
2016 `from_address` mediumtext,
2017 `content_type` text,
2018 KEY `message_id` (`message_id`),
2019 KEY `borrowernumber` (`borrowernumber`),
2020 KEY `message_transport_type` (`message_transport_type`),
2021 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2022 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2023 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2026 -- Table structure for table `message_transport_types`
2029 DROP TABLE IF EXISTS `message_transport_types`;
2030 CREATE TABLE `message_transport_types` (
2031 `message_transport_type` varchar(20) NOT NULL,
2032 PRIMARY KEY (`message_transport_type`)
2033 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2036 -- Table structure for table `message_attributes`
2039 DROP TABLE IF EXISTS `message_attributes`;
2040 CREATE TABLE `message_attributes` (
2041 `message_attribute_id` int(11) NOT NULL auto_increment,
2042 `message_name` varchar(20) NOT NULL default '',
2043 `takes_days` tinyint(1) NOT NULL default '0',
2044 PRIMARY KEY (`message_attribute_id`),
2045 UNIQUE KEY `message_name` (`message_name`)
2046 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2049 -- Table structure for table `message_transports`
2052 DROP TABLE IF EXISTS `message_transports`;
2053 CREATE TABLE `message_transports` (
2054 `message_attribute_id` int(11) NOT NULL,
2055 `message_transport_type` varchar(20) NOT NULL,
2056 `is_digest` tinyint(1) NOT NULL default '0',
2057 `letter_module` varchar(20) NOT NULL default '',
2058 `letter_code` varchar(20) NOT NULL default '',
2059 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2060 KEY `message_transport_type` (`message_transport_type`),
2061 KEY `letter_module` (`letter_module`,`letter_code`),
2062 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2063 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2064 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2065 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2068 -- Table structure for table `borrower_message_preferences`
2071 DROP TABLE IF EXISTS `borrower_message_preferences`;
2072 CREATE TABLE `borrower_message_preferences` (
2073 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2074 `borrowernumber` int(11) default NULL,
2075 `categorycode` varchar(10) default NULL,
2076 `message_attribute_id` int(11) default '0',
2077 `days_in_advance` int(11) default '0',
2078 `wants_digest` tinyint(1) NOT NULL default '0',
2079 PRIMARY KEY (`borrower_message_preference_id`),
2080 KEY `borrowernumber` (`borrowernumber`),
2081 KEY `categorycode` (`categorycode`),
2082 KEY `message_attribute_id` (`message_attribute_id`),
2083 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2084 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2085 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2086 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2089 -- Table structure for table `borrower_message_transport_preferences`
2092 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2093 CREATE TABLE `borrower_message_transport_preferences` (
2094 `borrower_message_preference_id` int(11) NOT NULL default '0',
2095 `message_transport_type` varchar(20) NOT NULL default '0',
2096 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2097 KEY `message_transport_type` (`message_transport_type`),
2098 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,
2099 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
2100 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2103 -- Table structure for the table branch_transfer_limits
2106 DROP TABLE IF EXISTS `branch_transfer_limits`;
2107 CREATE TABLE branch_transfer_limits (
2108 limitId int(8) NOT NULL auto_increment,
2109 toBranch varchar(10) NOT NULL,
2110 fromBranch varchar(10) NOT NULL,
2111 itemtype varchar(10) NULL,
2112 ccode varchar(10) NULL,
2113 PRIMARY KEY (limitId)
2114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2117 -- Table structure for table `item_circulation_alert_preferences`
2120 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2121 CREATE TABLE `item_circulation_alert_preferences` (
2122 `id` int(11) NOT NULL auto_increment,
2123 `branchcode` varchar(10) NOT NULL,
2124 `categorycode` varchar(10) NOT NULL,
2125 `item_type` varchar(10) NOT NULL,
2126 `notification` varchar(16) NOT NULL,
2128 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2132 -- Table structure for table `messages`
2135 CREATE TABLE `messages` (
2136 `message_id` int(11) NOT NULL auto_increment,
2137 `borrowernumber` int(11) NOT NULL,
2138 `branchcode` varchar(4) default NULL,
2139 `message_type` varchar(1) NOT NULL,
2140 `message` text NOT NULL,
2141 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2142 PRIMARY KEY (`message_id`)
2143 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2146 -- Table structure for table `accountlines`
2149 DROP TABLE IF EXISTS `accountlines`;
2150 CREATE TABLE `accountlines` (
2151 `borrowernumber` int(11) NOT NULL default 0,
2152 `accountno` smallint(6) NOT NULL default 0,
2153 `itemnumber` int(11) default NULL,
2154 `date` date default NULL,
2155 `amount` decimal(28,6) default NULL,
2156 `description` mediumtext,
2157 `dispute` mediumtext,
2158 `accounttype` varchar(5) default NULL,
2159 `amountoutstanding` decimal(28,6) default NULL,
2160 `lastincrement` decimal(28,6) default NULL,
2161 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2162 `notify_id` int(11) NOT NULL default 0,
2163 `notify_level` int(2) NOT NULL default 0,
2164 KEY `acctsborridx` (`borrowernumber`),
2165 KEY `timeidx` (`timestamp`),
2166 KEY `itemnumber` (`itemnumber`),
2167 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2168 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2169 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2172 -- Table structure for table `accountoffsets`
2175 DROP TABLE IF EXISTS `accountoffsets`;
2176 CREATE TABLE `accountoffsets` (
2177 `borrowernumber` int(11) NOT NULL default 0,
2178 `accountno` smallint(6) NOT NULL default 0,
2179 `offsetaccount` smallint(6) NOT NULL default 0,
2180 `offsetamount` decimal(28,6) default NULL,
2181 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2182 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2186 -- Table structure for table `action_logs`
2189 DROP TABLE IF EXISTS `action_logs`;
2190 CREATE TABLE `action_logs` (
2191 `action_id` int(11) NOT NULL auto_increment,
2192 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2193 `user` int(11) NOT NULL default 0,
2196 `object` int(11) default NULL,
2198 PRIMARY KEY (`action_id`),
2199 KEY (`timestamp`,`user`)
2200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2203 -- Table structure for table `alert`
2206 DROP TABLE IF EXISTS `alert`;
2207 CREATE TABLE `alert` (
2208 `alertid` int(11) NOT NULL auto_increment,
2209 `borrowernumber` int(11) NOT NULL default 0,
2210 `type` varchar(10) NOT NULL default '',
2211 `externalid` varchar(20) NOT NULL default '',
2212 PRIMARY KEY (`alertid`),
2213 KEY `borrowernumber` (`borrowernumber`),
2214 KEY `type` (`type`,`externalid`)
2215 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2218 -- Table structure for table `aqbasketgroups`
2221 DROP TABLE IF EXISTS `aqbasketgroups`;
2222 CREATE TABLE `aqbasketgroups` (
2223 `id` int(11) NOT NULL auto_increment,
2224 `name` varchar(50) default NULL,
2225 `closed` tinyint(1) default NULL,
2226 `booksellerid` int(11) NOT NULL,
2228 KEY `booksellerid` (`booksellerid`),
2229 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2233 -- Table structure for table `aqbasket`
2236 DROP TABLE IF EXISTS `aqbasket`;
2237 CREATE TABLE `aqbasket` (
2238 `basketno` int(11) NOT NULL auto_increment,
2239 `basketname` varchar(50) default NULL,
2241 `booksellernote` mediumtext,
2242 `contractnumber` int(11),
2243 `creationdate` date default NULL,
2244 `closedate` date default NULL,
2245 `booksellerid` int(11) NOT NULL default 1,
2246 `authorisedby` varchar(10) default NULL,
2247 `booksellerinvoicenumber` mediumtext,
2248 `basketgroupid` int(11),
2249 PRIMARY KEY (`basketno`),
2250 KEY `booksellerid` (`booksellerid`),
2251 KEY `basketgroupid` (`basketgroupid`),
2252 KEY `contractnumber` (`contractnumber`),
2253 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2254 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2255 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2256 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2259 -- Table structure for table `aqbooksellers`
2262 DROP TABLE IF EXISTS `aqbooksellers`;
2263 CREATE TABLE `aqbooksellers` (
2264 `id` int(11) NOT NULL auto_increment,
2265 `name` mediumtext NOT NULL,
2266 `address1` mediumtext,
2267 `address2` mediumtext,
2268 `address3` mediumtext,
2269 `address4` mediumtext,
2270 `phone` varchar(30) default NULL,
2271 `accountnumber` mediumtext,
2272 `othersupplier` mediumtext,
2273 `currency` varchar(3) NOT NULL default '',
2274 `deliverydays` smallint(6) default NULL,
2275 `followupdays` smallint(6) default NULL,
2276 `followupscancel` smallint(6) default NULL,
2277 `specialty` mediumtext,
2278 `booksellerfax` mediumtext,
2280 `bookselleremail` mediumtext,
2281 `booksellerurl` mediumtext,
2282 `contact` varchar(100) default NULL,
2283 `postal` mediumtext,
2284 `url` varchar(255) default NULL,
2285 `contpos` varchar(100) default NULL,
2286 `contphone` varchar(100) default NULL,
2287 `contfax` varchar(100) default NULL,
2288 `contaltphone` varchar(100) default NULL,
2289 `contemail` varchar(100) default NULL,
2290 `contnotes` mediumtext,
2291 `active` tinyint(4) default NULL,
2292 `listprice` varchar(10) default NULL,
2293 `invoiceprice` varchar(10) default NULL,
2294 `gstreg` tinyint(4) default NULL,
2295 `listincgst` tinyint(4) default NULL,
2296 `invoiceincgst` tinyint(4) default NULL,
2297 `gstrate` decimal(6,4) default NULL,
2298 `discount` float(6,4) default NULL,
2299 `fax` varchar(50) default NULL,
2300 `nocalc` int(11) default NULL,
2301 `invoicedisc` float(6,4) default NULL,
2303 KEY `listprice` (`listprice`),
2304 KEY `invoiceprice` (`invoiceprice`),
2305 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2306 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2310 -- Table structure for table `aqbudgets`
2313 DROP TABLE IF EXISTS `aqbudgets`;
2314 CREATE TABLE `aqbudgets` (
2315 `budget_id` int(11) NOT NULL auto_increment,
2316 `budget_parent_id` int(11) default NULL,
2317 `budget_code` varchar(30) default NULL,
2318 `budget_name` varchar(80) default NULL,
2319 `budget_branchcode` varchar(10) default NULL,
2320 `budget_amount` decimal(28,6) NULL default '0.00',
2321 `budget_amount_sublevel` decimal(28,6) NULL default '0.00',
2322 `budget_encumb` decimal(28,6) NULL default '0.00',
2323 `budget_expend` decimal(28,6) NULL default '0.00',
2324 `budget_notes` mediumtext,
2325 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2326 `budget_period_id` int(11) default NULL,
2327 `sort1_authcat` varchar(80) default NULL,
2328 `sort2_authcat` varchar(80) default NULL,
2329 `budget_owner_id` int(11) default NULL,
2330 `budget_permission` int(1) default '0',
2331 PRIMARY KEY (`budget_id`)
2332 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2336 -- Table structure for table `aqbudgetperiods`
2340 DROP TABLE IF EXISTS `aqbudgetperiods`;
2341 CREATE TABLE `aqbudgetperiods` (
2342 `budget_period_id` int(11) NOT NULL auto_increment,
2343 `budget_period_startdate` date NOT NULL,
2344 `budget_period_enddate` date NOT NULL,
2345 `budget_period_active` tinyint(1) default '0',
2346 `budget_period_description` mediumtext,
2347 `budget_period_total` decimal(28,6),
2348 `budget_period_locked` tinyint(1) default NULL,
2349 `sort1_authcat` varchar(10) default NULL,
2350 `sort2_authcat` varchar(10) default NULL,
2351 PRIMARY KEY (`budget_period_id`)
2352 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2355 -- Table structure for table `aqbudgets_planning`
2358 DROP TABLE IF EXISTS `aqbudgets_planning`;
2359 CREATE TABLE `aqbudgets_planning` (
2360 `plan_id` int(11) NOT NULL auto_increment,
2361 `budget_id` int(11) NOT NULL,
2362 `budget_period_id` int(11) NOT NULL,
2363 `estimated_amount` decimal(28,6) default NULL,
2364 `authcat` varchar(30) NOT NULL,
2365 `authvalue` varchar(30) NOT NULL,
2366 PRIMARY KEY (`plan_id`),
2367 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2368 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2371 -- Table structure for table 'aqcontract'
2374 DROP TABLE IF EXISTS `aqcontract`;
2375 CREATE TABLE `aqcontract` (
2376 `contractnumber` int(11) NOT NULL auto_increment,
2377 `contractstartdate` date default NULL,
2378 `contractenddate` date default NULL,
2379 `contractname` varchar(50) default NULL,
2380 `contractdescription` mediumtext,
2381 `booksellerid` int(11) not NULL,
2382 PRIMARY KEY (`contractnumber`),
2383 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2384 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2388 -- Table structure for table `aqorderdelivery`
2391 DROP TABLE IF EXISTS `aqorderdelivery`;
2392 CREATE TABLE `aqorderdelivery` (
2393 `ordernumber` date default NULL,
2394 `deliverynumber` smallint(6) NOT NULL default 0,
2395 `deliverydate` varchar(18) default NULL,
2396 `qtydelivered` smallint(6) default NULL,
2397 `deliverycomments` mediumtext
2398 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2401 -- Table structure for table `aqorders`
2404 DROP TABLE IF EXISTS `aqorders`;
2405 CREATE TABLE `aqorders` (
2406 `ordernumber` int(11) NOT NULL auto_increment,
2407 `biblionumber` int(11) default NULL,
2408 `entrydate` date default NULL,
2409 `quantity` smallint(6) default NULL,
2410 `currency` varchar(3) default NULL,
2411 `listprice` decimal(28,6) default NULL,
2412 `totalamount` decimal(28,6) default NULL,
2413 `datereceived` date default NULL,
2414 `booksellerinvoicenumber` mediumtext,
2415 `freight` decimal(28,6) default NULL,
2416 `unitprice` decimal(28,6) default NULL,
2417 `quantityreceived` smallint(6) default NULL,
2418 `cancelledby` varchar(10) default NULL,
2419 `datecancellationprinted` date default NULL,
2421 `supplierreference` mediumtext,
2422 `purchaseordernumber` mediumtext,
2423 `subscription` tinyint(1) default NULL,
2424 `serialid` varchar(30) default NULL,
2425 `basketno` int(11) default NULL,
2426 `biblioitemnumber` int(11) default NULL,
2427 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2428 `rrp` decimal(13,2) default NULL,
2429 `ecost` decimal(13,2) default NULL,
2430 `gst` decimal(13,2) default NULL,
2431 `budget_id` int(11) NOT NULL,
2432 `budgetgroup_id` int(11) NOT NULL,
2433 `budgetdate` date default NULL,
2434 `sort1` varchar(80) default NULL,
2435 `sort2` varchar(80) default NULL,
2436 `sort1_authcat` varchar(10) default NULL,
2437 `sort2_authcat` varchar(10) default NULL,
2438 `uncertainprice` tinyint(1),
2439 PRIMARY KEY (`ordernumber`),
2440 KEY `basketno` (`basketno`),
2441 KEY `biblionumber` (`biblionumber`),
2442 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2443 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2444 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2447 -- Table structure for table `aqorders_items`
2450 DROP TABLE IF EXISTS `aqorders_items`;
2451 CREATE TABLE `aqorders_items` (
2452 `ordernumber` int(11) NOT NULL,
2453 `itemnumber` int(11) NOT NULL,
2454 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2455 PRIMARY KEY (`itemnumber`),
2456 KEY `ordernumber` (`ordernumber`)
2457 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2459 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2460 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2461 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2462 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2463 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2464 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2465 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2466 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;