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 `export_format`
749 DROP TABLE IF EXISTS `export_format`;
750 CREATE TABLE `export_format` (
751 `export_format_id` int(11) NOT NULL auto_increment,
752 `profile` varchar(255) NOT NULL,
753 `description` mediumtext NOT NULL,
754 `marcfields` mediumtext NOT NULL,
755 PRIMARY KEY (`export_format_id`)
756 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
760 -- Table structure for table `hold_fill_targets`
763 DROP TABLE IF EXISTS `hold_fill_targets`;
764 CREATE TABLE hold_fill_targets (
765 `borrowernumber` int(11) NOT NULL,
766 `biblionumber` int(11) NOT NULL,
767 `itemnumber` int(11) NOT NULL,
768 `source_branchcode` varchar(10) default NULL,
769 `item_level_request` tinyint(4) NOT NULL default 0,
770 PRIMARY KEY `itemnumber` (`itemnumber`),
771 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
772 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
773 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
774 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
775 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
776 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
777 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
778 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
779 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
780 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
783 -- Table structure for table `import_batches`
786 DROP TABLE IF EXISTS `import_batches`;
787 CREATE TABLE `import_batches` (
788 `import_batch_id` int(11) NOT NULL auto_increment,
789 `matcher_id` int(11) default NULL,
790 `template_id` int(11) default NULL,
791 `branchcode` varchar(10) default NULL,
792 `num_biblios` int(11) NOT NULL default 0,
793 `num_items` int(11) NOT NULL default 0,
794 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
795 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
796 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
797 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
798 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
799 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
800 `file_name` varchar(100),
801 `comments` mediumtext,
802 PRIMARY KEY (`import_batch_id`),
803 KEY `branchcode` (`branchcode`)
804 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
807 -- Table structure for table `import_records`
810 DROP TABLE IF EXISTS `import_records`;
811 CREATE TABLE `import_records` (
812 `import_record_id` int(11) NOT NULL auto_increment,
813 `import_batch_id` int(11) NOT NULL,
814 `branchcode` varchar(10) default NULL,
815 `record_sequence` int(11) NOT NULL default 0,
816 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
817 `import_date` DATE default NULL,
818 `marc` longblob NOT NULL,
819 `marcxml` longtext NOT NULL,
820 `marcxml_old` longtext NOT NULL,
821 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
822 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
823 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
824 `import_error` mediumtext,
825 `encoding` varchar(40) NOT NULL default '',
826 `z3950random` varchar(40) default NULL,
827 PRIMARY KEY (`import_record_id`),
828 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
829 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
830 KEY `branchcode` (`branchcode`),
831 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
832 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
835 -- Table structure for `import_record_matches`
837 DROP TABLE IF EXISTS `import_record_matches`;
838 CREATE TABLE `import_record_matches` (
839 `import_record_id` int(11) NOT NULL,
840 `candidate_match_id` int(11) NOT NULL,
841 `score` int(11) NOT NULL default 0,
842 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
843 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
844 KEY `record_score` (`import_record_id`, `score`)
845 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
848 -- Table structure for table `import_biblios`
851 DROP TABLE IF EXISTS `import_biblios`;
852 CREATE TABLE `import_biblios` (
853 `import_record_id` int(11) NOT NULL,
854 `matched_biblionumber` int(11) default NULL,
855 `control_number` varchar(25) default NULL,
856 `original_source` varchar(25) default NULL,
857 `title` varchar(128) default NULL,
858 `author` varchar(80) default NULL,
859 `isbn` varchar(30) default NULL,
860 `issn` varchar(9) default NULL,
861 `has_items` tinyint(1) NOT NULL default 0,
862 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
863 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
864 KEY `matched_biblionumber` (`matched_biblionumber`),
865 KEY `title` (`title`),
867 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
870 -- Table structure for table `import_items`
873 DROP TABLE IF EXISTS `import_items`;
874 CREATE TABLE `import_items` (
875 `import_items_id` int(11) NOT NULL auto_increment,
876 `import_record_id` int(11) NOT NULL,
877 `itemnumber` int(11) default NULL,
878 `branchcode` varchar(10) default NULL,
879 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
880 `marcxml` longtext NOT NULL,
881 `import_error` mediumtext,
882 PRIMARY KEY (`import_items_id`),
883 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
884 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
885 KEY `itemnumber` (`itemnumber`),
886 KEY `branchcode` (`branchcode`)
887 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
890 -- Table structure for table `issues`
893 DROP TABLE IF EXISTS `issues`;
894 CREATE TABLE `issues` (
895 `borrowernumber` int(11) default NULL,
896 `itemnumber` int(11) default NULL,
897 `date_due` date default NULL,
898 `branchcode` varchar(10) default NULL,
899 `issuingbranch` varchar(18) default NULL,
900 `returndate` date default NULL,
901 `lastreneweddate` date default NULL,
902 `return` varchar(4) default NULL,
903 `renewals` tinyint(4) default NULL,
904 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
905 `issuedate` date default NULL,
906 KEY `issuesborridx` (`borrowernumber`),
907 KEY `issuesitemidx` (`itemnumber`),
908 KEY `bordate` (`borrowernumber`,`timestamp`),
909 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
910 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
911 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
914 -- Table structure for table `issuingrules`
917 DROP TABLE IF EXISTS `issuingrules`;
918 CREATE TABLE `issuingrules` (
919 `categorycode` varchar(10) NOT NULL default '',
920 `itemtype` varchar(10) NOT NULL default '',
921 `restrictedtype` tinyint(1) default NULL,
922 `rentaldiscount` decimal(28,6) default NULL,
923 `reservecharge` decimal(28,6) default NULL,
924 `fine` decimal(28,6) default NULL,
925 `firstremind` int(11) default NULL,
926 `chargeperiod` int(11) default NULL,
927 `accountsent` int(11) default NULL,
928 `chargename` varchar(100) default NULL,
929 `maxissueqty` int(4) default NULL,
930 `issuelength` int(4) default NULL,
931 `branchcode` varchar(10) NOT NULL default '',
932 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
933 KEY `categorycode` (`categorycode`),
934 KEY `itemtype` (`itemtype`)
935 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
938 -- Table structure for table `items`
941 DROP TABLE IF EXISTS `items`;
942 CREATE TABLE `items` (
943 `itemnumber` int(11) NOT NULL auto_increment,
944 `biblionumber` int(11) NOT NULL default 0,
945 `biblioitemnumber` int(11) NOT NULL default 0,
946 `barcode` varchar(20) default NULL,
947 `dateaccessioned` date default NULL,
948 `booksellerid` mediumtext default NULL,
949 `homebranch` varchar(10) default NULL,
950 `price` decimal(8,2) default NULL,
951 `replacementprice` decimal(8,2) default NULL,
952 `replacementpricedate` date default NULL,
953 `datelastborrowed` date default NULL,
954 `datelastseen` date default NULL,
955 `stack` tinyint(1) default NULL,
956 `notforloan` tinyint(1) NOT NULL default 0,
957 `damaged` tinyint(1) NOT NULL default 0,
958 `itemlost` tinyint(1) NOT NULL default 0,
959 `wthdrawn` tinyint(1) NOT NULL default 0,
960 `itemcallnumber` varchar(255) default NULL,
961 `issues` smallint(6) default NULL,
962 `renewals` smallint(6) default NULL,
963 `reserves` smallint(6) default NULL,
964 `restricted` tinyint(1) default NULL,
965 `itemnotes` mediumtext,
966 `holdingbranch` varchar(10) default NULL,
967 `paidfor` mediumtext,
968 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
969 `location` varchar(80) default NULL,
970 `permanent_location` varchar(80) default NULL,
971 `onloan` date default NULL,
972 `cn_source` varchar(10) default NULL,
973 `cn_sort` varchar(30) default NULL,
974 `ccode` varchar(10) default NULL,
975 `materials` varchar(10) default NULL,
976 `uri` varchar(255) default NULL,
977 `itype` varchar(10) default NULL,
978 `more_subfields_xml` longtext default NULL,
979 `enumchron` varchar(80) default NULL,
980 `copynumber` varchar(32) default NULL,
981 PRIMARY KEY (`itemnumber`),
982 UNIQUE KEY `itembarcodeidx` (`barcode`),
983 KEY `itembinoidx` (`biblioitemnumber`),
984 KEY `itembibnoidx` (`biblionumber`),
985 KEY `homebranch` (`homebranch`),
986 KEY `holdingbranch` (`holdingbranch`),
987 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
988 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
989 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
990 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
993 -- Table structure for table `itemtypes`
996 DROP TABLE IF EXISTS `itemtypes`;
997 CREATE TABLE `itemtypes` (
998 `itemtype` varchar(10) NOT NULL default '',
999 `description` mediumtext,
1000 `renewalsallowed` smallint(6) default NULL,
1001 `rentalcharge` double(16,4) default NULL,
1002 `notforloan` smallint(6) default NULL,
1003 `imageurl` varchar(200) default NULL,
1005 PRIMARY KEY (`itemtype`),
1006 UNIQUE KEY `itemtype` (`itemtype`)
1007 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1010 -- Table structure for table `labels_batches`
1013 DROP TABLE IF EXISTS `labels_batches`;
1014 CREATE TABLE `labels_batches` (
1015 `label_id` int(11) NOT NULL auto_increment,
1016 `batch_id` int(10) NOT NULL default '1',
1017 `item_number` int(11) NOT NULL default '0',
1018 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1019 `branch_code` varchar(10) NOT NULL default 'NB',
1020 PRIMARY KEY USING BTREE (`label_id`),
1021 KEY `branch_fk` (`branch_code`),
1022 KEY `item_fk` (`item_number`),
1023 CONSTRAINT `item_fk_constraint` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE,
1024 CONSTRAINT `branch_fk_constraint` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE
1025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1028 -- Table structure for table `labels_layouts`
1031 DROP TABLE IF EXISTS `labels_layouts`;
1032 CREATE TABLE `labels_layouts` (
1033 `layout_id` int(4) NOT NULL auto_increment,
1034 `barcode_type` char(100) NOT NULL default 'CODE39',
1035 `printing_type` char(32) NOT NULL default 'BAR',
1036 `layout_name` char(20) NOT NULL default 'DEFAULT',
1037 `guidebox` int(1) default '0',
1038 `font` char(10) character set utf8 collate utf8_unicode_ci NOT NULL default 'TR',
1039 `font_size` int(4) NOT NULL default '10',
1040 `callnum_split` int(1) default '0',
1041 `text_justify` char(1) character set utf8 collate utf8_unicode_ci NOT NULL default 'L',
1042 `format_string` varchar(210) NOT NULL default 'barcode',
1043 PRIMARY KEY USING BTREE (`layout_id`)
1044 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1047 -- Table structure for table `labels_templates`
1050 DROP TABLE IF EXISTS `labels_templates`;
1051 CREATE TABLE `labels_templates` (
1052 `template_id` int(4) NOT NULL auto_increment,
1053 `profile_id` int(4) default NULL,
1054 `template_code` char(100) NOT NULL default 'DEFAULT TEMPLATE',
1055 `template_desc` char(100) NOT NULL default 'Default description',
1056 `page_width` float NOT NULL default '0',
1057 `page_height` float NOT NULL default '0',
1058 `label_width` float NOT NULL default '0',
1059 `label_height` float NOT NULL default '0',
1060 `top_text_margin` float NOT NULL default '0',
1061 `left_text_margin` float NOT NULL default '0',
1062 `top_margin` float NOT NULL default '0',
1063 `left_margin` float NOT NULL default '0',
1064 `cols` int(2) NOT NULL default '0',
1065 `rows` int(2) NOT NULL default '0',
1066 `col_gap` float NOT NULL default '0',
1067 `row_gap` float NOT NULL default '0',
1068 `units` char(20) NOT NULL default 'POINT',
1069 PRIMARY KEY (`template_id`),
1070 KEY `template_profile_fk_constraint` (`profile_id`)
1071 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1074 -- Table structure for table `letter`
1077 DROP TABLE IF EXISTS `letter`;
1078 CREATE TABLE `letter` (
1079 `module` varchar(20) NOT NULL default '',
1080 `code` varchar(20) NOT NULL default '',
1081 `name` varchar(100) NOT NULL default '',
1082 `title` varchar(200) NOT NULL default '',
1084 PRIMARY KEY (`module`,`code`)
1085 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1088 -- Table structure for table `marc_subfield_structure`
1091 DROP TABLE IF EXISTS `marc_subfield_structure`;
1092 CREATE TABLE `marc_subfield_structure` (
1093 `tagfield` varchar(3) NOT NULL default '',
1094 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1095 `liblibrarian` varchar(255) NOT NULL default '',
1096 `libopac` varchar(255) NOT NULL default '',
1097 `repeatable` tinyint(4) NOT NULL default 0,
1098 `mandatory` tinyint(4) NOT NULL default 0,
1099 `kohafield` varchar(40) default NULL,
1100 `tab` tinyint(1) default NULL,
1101 `authorised_value` varchar(20) default NULL,
1102 `authtypecode` varchar(20) default NULL,
1103 `value_builder` varchar(80) default NULL,
1104 `isurl` tinyint(1) default NULL,
1105 `hidden` tinyint(1) default NULL,
1106 `frameworkcode` varchar(4) NOT NULL default '',
1107 `seealso` varchar(1100) default NULL,
1108 `link` varchar(80) default NULL,
1109 `defaultvalue` text default NULL,
1110 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1111 KEY `kohafield_2` (`kohafield`),
1112 KEY `tab` (`frameworkcode`,`tab`),
1113 KEY `kohafield` (`frameworkcode`,`kohafield`)
1114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1117 -- Table structure for table `marc_tag_structure`
1120 DROP TABLE IF EXISTS `marc_tag_structure`;
1121 CREATE TABLE `marc_tag_structure` (
1122 `tagfield` varchar(3) NOT NULL default '',
1123 `liblibrarian` varchar(255) NOT NULL default '',
1124 `libopac` varchar(255) NOT NULL default '',
1125 `repeatable` tinyint(4) NOT NULL default 0,
1126 `mandatory` tinyint(4) NOT NULL default 0,
1127 `authorised_value` varchar(10) default NULL,
1128 `frameworkcode` varchar(4) NOT NULL default '',
1129 PRIMARY KEY (`frameworkcode`,`tagfield`)
1130 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1133 -- Table structure for table `marc_matchers`
1136 DROP TABLE IF EXISTS `marc_matchers`;
1137 CREATE TABLE `marc_matchers` (
1138 `matcher_id` int(11) NOT NULL auto_increment,
1139 `code` varchar(10) NOT NULL default '',
1140 `description` varchar(255) NOT NULL default '',
1141 `record_type` varchar(10) NOT NULL default 'biblio',
1142 `threshold` int(11) NOT NULL default 0,
1143 PRIMARY KEY (`matcher_id`),
1144 KEY `code` (`code`),
1145 KEY `record_type` (`record_type`)
1146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1149 -- Table structure for table `matchpoints`
1151 DROP TABLE IF EXISTS `matchpoints`;
1152 CREATE TABLE `matchpoints` (
1153 `matcher_id` int(11) NOT NULL,
1154 `matchpoint_id` int(11) NOT NULL auto_increment,
1155 `search_index` varchar(30) NOT NULL default '',
1156 `score` int(11) NOT NULL default 0,
1157 PRIMARY KEY (`matchpoint_id`),
1158 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1159 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1160 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1164 -- Table structure for table `matchpoint_components`
1166 DROP TABLE IF EXISTS `matchpoint_components`;
1167 CREATE TABLE `matchpoint_components` (
1168 `matchpoint_id` int(11) NOT NULL,
1169 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1170 sequence int(11) NOT NULL default 0,
1171 tag varchar(3) NOT NULL default '',
1172 subfields varchar(40) NOT NULL default '',
1173 offset int(4) NOT NULL default 0,
1174 length int(4) NOT NULL default 0,
1175 PRIMARY KEY (`matchpoint_component_id`),
1176 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1177 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1178 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1179 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1182 -- Table structure for table `matcher_component_norms`
1184 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1185 CREATE TABLE `matchpoint_component_norms` (
1186 `matchpoint_component_id` int(11) NOT NULL,
1187 `sequence` int(11) NOT NULL default 0,
1188 `norm_routine` varchar(50) NOT NULL default '',
1189 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1190 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1191 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1192 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1195 -- Table structure for table `matcher_matchpoints`
1197 DROP TABLE IF EXISTS `matcher_matchpoints`;
1198 CREATE TABLE `matcher_matchpoints` (
1199 `matcher_id` int(11) NOT NULL,
1200 `matchpoint_id` int(11) NOT NULL,
1201 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1202 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1203 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1204 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1208 -- Table structure for table `matchchecks`
1210 DROP TABLE IF EXISTS `matchchecks`;
1211 CREATE TABLE `matchchecks` (
1212 `matcher_id` int(11) NOT NULL,
1213 `matchcheck_id` int(11) NOT NULL auto_increment,
1214 `source_matchpoint_id` int(11) NOT NULL,
1215 `target_matchpoint_id` int(11) NOT NULL,
1216 PRIMARY KEY (`matchcheck_id`),
1217 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1218 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1219 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1220 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1221 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1222 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1223 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1226 -- Table structure for table `notifys`
1229 DROP TABLE IF EXISTS `notifys`;
1230 CREATE TABLE `notifys` (
1231 `notify_id` int(11) NOT NULL default 0,
1232 `borrowernumber` int(11) NOT NULL default 0,
1233 `itemnumber` int(11) NOT NULL default 0,
1234 `notify_date` date default NULL,
1235 `notify_send_date` date default NULL,
1236 `notify_level` int(1) NOT NULL default 0,
1237 `method` varchar(20) NOT NULL default ''
1238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1241 -- Table structure for table `nozebra`
1244 DROP TABLE IF EXISTS `nozebra`;
1245 CREATE TABLE `nozebra` (
1246 `server` varchar(20) NOT NULL,
1247 `indexname` varchar(40) NOT NULL,
1248 `value` varchar(250) NOT NULL,
1249 `biblionumbers` longtext NOT NULL,
1250 KEY `indexname` (`server`,`indexname`),
1251 KEY `value` (`server`,`value`))
1252 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1255 -- Table structure for table `old_issues`
1258 DROP TABLE IF EXISTS `old_issues`;
1259 CREATE TABLE `old_issues` (
1260 `borrowernumber` int(11) default NULL,
1261 `itemnumber` int(11) default NULL,
1262 `date_due` date default NULL,
1263 `branchcode` varchar(10) default NULL,
1264 `issuingbranch` varchar(18) default NULL,
1265 `returndate` date default NULL,
1266 `lastreneweddate` date default NULL,
1267 `return` varchar(4) default NULL,
1268 `renewals` tinyint(4) default NULL,
1269 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1270 `issuedate` date default NULL,
1271 KEY `old_issuesborridx` (`borrowernumber`),
1272 KEY `old_issuesitemidx` (`itemnumber`),
1273 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1274 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1275 ON DELETE SET NULL ON UPDATE SET NULL,
1276 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1277 ON DELETE SET NULL ON UPDATE SET NULL
1278 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1281 -- Table structure for table `old_reserves`
1283 DROP TABLE IF EXISTS `old_reserves`;
1284 CREATE TABLE `old_reserves` (
1285 `borrowernumber` int(11) default NULL,
1286 `reservedate` date default NULL,
1287 `biblionumber` int(11) default NULL,
1288 `constrainttype` varchar(1) default NULL,
1289 `branchcode` varchar(10) default NULL,
1290 `notificationdate` date default NULL,
1291 `reminderdate` date default NULL,
1292 `cancellationdate` date default NULL,
1293 `reservenotes` mediumtext,
1294 `priority` smallint(6) default NULL,
1295 `found` varchar(1) default NULL,
1296 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1297 `itemnumber` int(11) default NULL,
1298 `waitingdate` date default NULL,
1299 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1300 KEY `old_reserves_biblionumber` (`biblionumber`),
1301 KEY `old_reserves_itemnumber` (`itemnumber`),
1302 KEY `old_reserves_branchcode` (`branchcode`),
1303 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1304 ON DELETE SET NULL ON UPDATE SET NULL,
1305 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1306 ON DELETE SET NULL ON UPDATE SET NULL,
1307 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1308 ON DELETE SET NULL ON UPDATE SET NULL
1309 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1312 -- Table structure for table `opac_news`
1315 DROP TABLE IF EXISTS `opac_news`;
1316 CREATE TABLE `opac_news` (
1317 `idnew` int(10) unsigned NOT NULL auto_increment,
1318 `title` varchar(250) NOT NULL default '',
1319 `new` text NOT NULL,
1320 `lang` varchar(25) NOT NULL default '',
1321 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1322 `expirationdate` date default NULL,
1323 `number` int(11) default NULL,
1324 PRIMARY KEY (`idnew`)
1325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1328 -- Table structure for table `overduerules`
1331 DROP TABLE IF EXISTS `overduerules`;
1332 CREATE TABLE `overduerules` (
1333 `branchcode` varchar(10) NOT NULL default '',
1334 `categorycode` varchar(10) NOT NULL default '',
1335 `delay1` int(4) default 0,
1336 `letter1` varchar(20) default NULL,
1337 `debarred1` varchar(1) default 0,
1338 `delay2` int(4) default 0,
1339 `debarred2` varchar(1) default 0,
1340 `letter2` varchar(20) default NULL,
1341 `delay3` int(4) default 0,
1342 `letter3` varchar(20) default NULL,
1343 `debarred3` int(1) default 0,
1344 PRIMARY KEY (`branchcode`,`categorycode`)
1345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1348 -- Table structure for table `patroncards`
1351 DROP TABLE IF EXISTS `patroncards`;
1352 CREATE TABLE `patroncards` (
1353 `cardid` int(11) NOT NULL auto_increment,
1354 `batch_id` varchar(10) NOT NULL default '1',
1355 `borrowernumber` int(11) NOT NULL,
1356 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1357 PRIMARY KEY (`cardid`),
1358 KEY `patroncards_ibfk_1` (`borrowernumber`),
1359 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1360 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1363 -- Table structure for table `patronimage`
1366 DROP TABLE IF EXISTS `patronimage`;
1367 CREATE TABLE `patronimage` (
1368 `cardnumber` varchar(16) NOT NULL,
1369 `mimetype` varchar(15) NOT NULL,
1370 `imagefile` mediumblob NOT NULL,
1371 PRIMARY KEY (`cardnumber`),
1372 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1373 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1376 -- Table structure for table `printers`
1379 DROP TABLE IF EXISTS `printers`;
1380 CREATE TABLE `printers` (
1381 `printername` varchar(40) NOT NULL default '',
1382 `printqueue` varchar(20) default NULL,
1383 `printtype` varchar(20) default NULL,
1384 PRIMARY KEY (`printername`)
1385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1388 -- Table structure for table `printers_profile`
1391 DROP TABLE IF EXISTS `printers_profile`;
1392 CREATE TABLE `printers_profile` (
1393 `profile_id` int(4) NOT NULL auto_increment,
1394 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1395 `template_id` int(4) NOT NULL default '0',
1396 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1397 `offset_horz` float NOT NULL default '0',
1398 `offset_vert` float NOT NULL default '0',
1399 `creep_horz` float NOT NULL default '0',
1400 `creep_vert` float NOT NULL default '0',
1401 `units` char(20) NOT NULL default 'POINT',
1402 PRIMARY KEY (`profile_id`),
1403 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`)
1404 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1407 -- Table structure for table `repeatable_holidays`
1410 DROP TABLE IF EXISTS `repeatable_holidays`;
1411 CREATE TABLE `repeatable_holidays` (
1412 `id` int(11) NOT NULL auto_increment,
1413 `branchcode` varchar(10) NOT NULL default '',
1414 `weekday` smallint(6) default NULL,
1415 `day` smallint(6) default NULL,
1416 `month` smallint(6) default NULL,
1417 `title` varchar(50) NOT NULL default '',
1418 `description` text NOT NULL,
1420 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1423 -- Table structure for table `reports_dictionary`
1426 DROP TABLE IF EXISTS `reports_dictionary`;
1427 CREATE TABLE reports_dictionary (
1428 `id` int(11) NOT NULL auto_increment,
1429 `name` varchar(255) default NULL,
1431 `date_created` datetime default NULL,
1432 `date_modified` datetime default NULL,
1434 `area` int(11) default NULL,
1436 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1439 -- Table structure for table `reserveconstraints`
1442 DROP TABLE IF EXISTS `reserveconstraints`;
1443 CREATE TABLE `reserveconstraints` (
1444 `borrowernumber` int(11) NOT NULL default 0,
1445 `reservedate` date default NULL,
1446 `biblionumber` int(11) NOT NULL default 0,
1447 `biblioitemnumber` int(11) default NULL,
1448 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1449 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1452 -- Table structure for table `reserves`
1455 DROP TABLE IF EXISTS `reserves`;
1456 CREATE TABLE `reserves` (
1457 `borrowernumber` int(11) NOT NULL default 0,
1458 `reservedate` date default NULL,
1459 `biblionumber` int(11) NOT NULL default 0,
1460 `constrainttype` varchar(1) default NULL,
1461 `branchcode` varchar(10) default NULL,
1462 `notificationdate` date default NULL,
1463 `reminderdate` date default NULL,
1464 `cancellationdate` date default NULL,
1465 `reservenotes` mediumtext,
1466 `priority` smallint(6) default NULL,
1467 `found` varchar(1) default NULL,
1468 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1469 `itemnumber` int(11) default NULL,
1470 `waitingdate` date default NULL,
1471 KEY `borrowernumber` (`borrowernumber`),
1472 KEY `biblionumber` (`biblionumber`),
1473 KEY `itemnumber` (`itemnumber`),
1474 KEY `branchcode` (`branchcode`),
1475 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1476 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1477 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1478 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1479 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1482 -- Table structure for table `reviews`
1485 DROP TABLE IF EXISTS `reviews`;
1486 CREATE TABLE `reviews` (
1487 `reviewid` int(11) NOT NULL auto_increment,
1488 `borrowernumber` int(11) default NULL,
1489 `biblionumber` int(11) default NULL,
1491 `approved` tinyint(4) default NULL,
1492 `datereviewed` datetime default NULL,
1493 PRIMARY KEY (`reviewid`)
1494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1497 -- Table structure for table `roadtype`
1500 DROP TABLE IF EXISTS `roadtype`;
1501 CREATE TABLE `roadtype` (
1502 `roadtypeid` int(11) NOT NULL auto_increment,
1503 `road_type` varchar(100) NOT NULL default '',
1504 PRIMARY KEY (`roadtypeid`)
1505 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1508 -- Table structure for table `saved_sql`
1511 DROP TABLE IF EXISTS `saved_sql`;
1512 CREATE TABLE saved_sql (
1513 `id` int(11) NOT NULL auto_increment,
1514 `borrowernumber` int(11) default NULL,
1515 `date_created` datetime default NULL,
1516 `last_modified` datetime default NULL,
1518 `last_run` datetime default NULL,
1519 `report_name` varchar(255) default NULL,
1520 `type` varchar(255) default NULL,
1523 KEY boridx (`borrowernumber`)
1524 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1528 -- Table structure for `saved_reports`
1531 DROP TABLE IF EXISTS `saved_reports`;
1532 CREATE TABLE saved_reports (
1533 `id` int(11) NOT NULL auto_increment,
1534 `report_id` int(11) default NULL,
1536 `date_run` datetime default NULL,
1538 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1542 -- Table structure for table `serial`
1545 DROP TABLE IF EXISTS `serial`;
1546 CREATE TABLE `serial` (
1547 `serialid` int(11) NOT NULL auto_increment,
1548 `biblionumber` varchar(100) NOT NULL default '',
1549 `subscriptionid` varchar(100) NOT NULL default '',
1550 `serialseq` varchar(100) NOT NULL default '',
1551 `status` tinyint(4) NOT NULL default 0,
1552 `planneddate` date default NULL,
1554 `publisheddate` date default NULL,
1555 `itemnumber` text default NULL,
1556 `claimdate` date default NULL,
1557 `routingnotes` text,
1558 PRIMARY KEY (`serialid`)
1559 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1562 -- Table structure for table `sessions`
1565 DROP TABLE IF EXISTS sessions;
1566 CREATE TABLE sessions (
1567 `id` varchar(32) NOT NULL,
1568 `a_session` text NOT NULL,
1570 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1573 -- Table structure for table `special_holidays`
1576 DROP TABLE IF EXISTS `special_holidays`;
1577 CREATE TABLE `special_holidays` (
1578 `id` int(11) NOT NULL auto_increment,
1579 `branchcode` varchar(10) NOT NULL default '',
1580 `day` smallint(6) NOT NULL default 0,
1581 `month` smallint(6) NOT NULL default 0,
1582 `year` smallint(6) NOT NULL default 0,
1583 `isexception` smallint(1) NOT NULL default 1,
1584 `title` varchar(50) NOT NULL default '',
1585 `description` text NOT NULL,
1587 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1590 -- Table structure for table `statistics`
1593 DROP TABLE IF EXISTS `statistics`;
1594 CREATE TABLE `statistics` (
1595 `datetime` datetime default NULL,
1596 `branch` varchar(10) default NULL,
1597 `proccode` varchar(4) default NULL,
1598 `value` double(16,4) default NULL,
1599 `type` varchar(16) default NULL,
1601 `usercode` varchar(10) default NULL,
1602 `itemnumber` int(11) default NULL,
1603 `itemtype` varchar(10) default NULL,
1604 `borrowernumber` int(11) default NULL,
1605 `associatedborrower` int(11) default NULL,
1606 KEY `timeidx` (`datetime`)
1607 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1610 -- Table structure for table `stopwords`
1613 DROP TABLE IF EXISTS `stopwords`;
1614 CREATE TABLE `stopwords` (
1615 `word` varchar(255) default NULL
1616 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1619 -- Table structure for table `subscription`
1622 DROP TABLE IF EXISTS `subscription`;
1623 CREATE TABLE `subscription` (
1624 `biblionumber` int(11) NOT NULL default 0,
1625 `subscriptionid` int(11) NOT NULL auto_increment,
1626 `librarian` varchar(100) default '',
1627 `startdate` date default NULL,
1628 `aqbooksellerid` int(11) default 0,
1629 `cost` int(11) default 0,
1630 `aqbudgetid` int(11) default 0,
1631 `weeklength` int(11) default 0,
1632 `monthlength` int(11) default 0,
1633 `numberlength` int(11) default 0,
1634 `periodicity` tinyint(4) default 0,
1635 `dow` varchar(100) default '',
1636 `numberingmethod` varchar(100) default '',
1638 `status` varchar(100) NOT NULL default '',
1639 `add1` int(11) default 0,
1640 `every1` int(11) default 0,
1641 `whenmorethan1` int(11) default 0,
1642 `setto1` int(11) default NULL,
1643 `lastvalue1` int(11) default NULL,
1644 `add2` int(11) default 0,
1645 `every2` int(11) default 0,
1646 `whenmorethan2` int(11) default 0,
1647 `setto2` int(11) default NULL,
1648 `lastvalue2` int(11) default NULL,
1649 `add3` int(11) default 0,
1650 `every3` int(11) default 0,
1651 `innerloop1` int(11) default 0,
1652 `innerloop2` int(11) default 0,
1653 `innerloop3` int(11) default 0,
1654 `whenmorethan3` int(11) default 0,
1655 `setto3` int(11) default NULL,
1656 `lastvalue3` int(11) default NULL,
1657 `issuesatonce` tinyint(3) NOT NULL default 1,
1658 `firstacquidate` date default NULL,
1659 `manualhistory` tinyint(1) NOT NULL default 0,
1660 `irregularity` text,
1661 `letter` varchar(20) default NULL,
1662 `numberpattern` tinyint(3) default 0,
1663 `distributedto` text,
1664 `internalnotes` longtext,
1666 `location` varchar(80) NULL default '',
1667 `branchcode` varchar(10) NOT NULL default '',
1668 `hemisphere` tinyint(3) default 0,
1669 `lastbranch` varchar(10),
1670 `serialsadditems` tinyint(1) NOT NULL default '0',
1671 `staffdisplaycount` VARCHAR(10) NULL,
1672 `opacdisplaycount` VARCHAR(10) NULL,
1673 `graceperiod` int(11) NOT NULL default '0',
1674 PRIMARY KEY (`subscriptionid`)
1675 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1678 -- Table structure for table `subscriptionhistory`
1681 DROP TABLE IF EXISTS `subscriptionhistory`;
1682 CREATE TABLE `subscriptionhistory` (
1683 `biblionumber` int(11) NOT NULL default 0,
1684 `subscriptionid` int(11) NOT NULL default 0,
1685 `histstartdate` date default NULL,
1686 `enddate` date default NULL,
1687 `missinglist` longtext NOT NULL,
1688 `recievedlist` longtext NOT NULL,
1689 `opacnote` varchar(150) NOT NULL default '',
1690 `librariannote` varchar(150) NOT NULL default '',
1691 PRIMARY KEY (`subscriptionid`),
1692 KEY `biblionumber` (`biblionumber`)
1693 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1696 -- Table structure for table `subscriptionroutinglist`
1699 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1700 CREATE TABLE `subscriptionroutinglist` (
1701 `routingid` int(11) NOT NULL auto_increment,
1702 `borrowernumber` int(11) default NULL,
1703 `ranking` int(11) default NULL,
1704 `subscriptionid` int(11) default NULL,
1705 PRIMARY KEY (`routingid`)
1706 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1709 -- Table structure for table `suggestions`
1712 DROP TABLE IF EXISTS `suggestions`;
1713 CREATE TABLE `suggestions` (
1714 `suggestionid` int(8) NOT NULL auto_increment,
1715 `suggestedby` int(11) NOT NULL default 0,
1716 `suggesteddate` date NOT NULL default 0,
1717 `managedby` int(11) default NULL,
1718 `manageddate` date default NULL,
1719 `STATUS` varchar(10) NOT NULL default '',
1721 `author` varchar(80) default NULL,
1722 `title` varchar(80) default NULL,
1723 `copyrightdate` smallint(6) default NULL,
1724 `publishercode` varchar(255) default NULL,
1725 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1726 `volumedesc` varchar(255) default NULL,
1727 `publicationyear` smallint(6) default 0,
1728 `place` varchar(255) default NULL,
1729 `isbn` varchar(30) default NULL,
1730 `mailoverseeing` smallint(1) default 0,
1731 `biblionumber` int(11) default NULL,
1733 PRIMARY KEY (`suggestionid`),
1734 KEY `suggestedby` (`suggestedby`),
1735 KEY `managedby` (`managedby`)
1736 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1739 -- Table structure for table `systempreferences`
1742 DROP TABLE IF EXISTS `systempreferences`;
1743 CREATE TABLE `systempreferences` (
1744 `variable` varchar(50) NOT NULL default '',
1746 `options` mediumtext,
1748 `type` varchar(20) default NULL,
1749 PRIMARY KEY (`variable`)
1750 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1753 -- Table structure for table `tags`
1756 DROP TABLE IF EXISTS `tags`;
1757 CREATE TABLE `tags` (
1758 `entry` varchar(255) NOT NULL default '',
1759 `weight` bigint(20) NOT NULL default 0,
1760 PRIMARY KEY (`entry`)
1761 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1764 -- Table structure for table `tags_all`
1767 DROP TABLE IF EXISTS `tags_all`;
1768 CREATE TABLE `tags_all` (
1769 `tag_id` int(11) NOT NULL auto_increment,
1770 `borrowernumber` int(11) NOT NULL,
1771 `biblionumber` int(11) NOT NULL,
1772 `term` varchar(255) NOT NULL,
1773 `language` int(4) default NULL,
1774 `date_created` datetime NOT NULL,
1775 PRIMARY KEY (`tag_id`),
1776 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1777 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1778 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1779 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1780 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1781 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1782 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1785 -- Table structure for table `tags_approval`
1788 DROP TABLE IF EXISTS `tags_approval`;
1789 CREATE TABLE `tags_approval` (
1790 `term` varchar(255) NOT NULL,
1791 `approved` int(1) NOT NULL default '0',
1792 `date_approved` datetime default NULL,
1793 `approved_by` int(11) default NULL,
1794 `weight_total` int(9) NOT NULL default '1',
1795 PRIMARY KEY (`term`),
1796 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1797 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1798 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1802 -- Table structure for table `tags_index`
1805 DROP TABLE IF EXISTS `tags_index`;
1806 CREATE TABLE `tags_index` (
1807 `term` varchar(255) NOT NULL,
1808 `biblionumber` int(11) NOT NULL,
1809 `weight` int(9) NOT NULL default '1',
1810 PRIMARY KEY (`term`,`biblionumber`),
1811 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1812 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1813 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1814 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1815 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1816 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1819 -- Table structure for table `userflags`
1822 DROP TABLE IF EXISTS `userflags`;
1823 CREATE TABLE `userflags` (
1824 `bit` int(11) NOT NULL default 0,
1825 `flag` varchar(30) default NULL,
1826 `flagdesc` varchar(255) default NULL,
1827 `defaulton` int(11) default NULL,
1829 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1832 -- Table structure for table `virtualshelves`
1835 DROP TABLE IF EXISTS `virtualshelves`;
1836 CREATE TABLE `virtualshelves` (
1837 `shelfnumber` int(11) NOT NULL auto_increment,
1838 `shelfname` varchar(255) default NULL,
1839 `owner` varchar(80) default NULL,
1840 `category` varchar(1) default NULL,
1841 `sortfield` varchar(16) default NULL,
1842 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1843 PRIMARY KEY (`shelfnumber`)
1844 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1847 -- Table structure for table `virtualshelfcontents`
1850 DROP TABLE IF EXISTS `virtualshelfcontents`;
1851 CREATE TABLE `virtualshelfcontents` (
1852 `shelfnumber` int(11) NOT NULL default 0,
1853 `biblionumber` int(11) NOT NULL default 0,
1854 `flags` int(11) default NULL,
1855 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1856 KEY `shelfnumber` (`shelfnumber`),
1857 KEY `biblionumber` (`biblionumber`),
1858 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1859 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1860 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1863 -- Table structure for table `z3950servers`
1866 DROP TABLE IF EXISTS `z3950servers`;
1867 CREATE TABLE `z3950servers` (
1868 `host` varchar(255) default NULL,
1869 `port` int(11) default NULL,
1870 `db` varchar(255) default NULL,
1871 `userid` varchar(255) default NULL,
1872 `password` varchar(255) default NULL,
1874 `id` int(11) NOT NULL auto_increment,
1875 `checked` smallint(6) default NULL,
1876 `rank` int(11) default NULL,
1877 `syntax` varchar(80) default NULL,
1879 `position` enum('primary','secondary','') NOT NULL default 'primary',
1880 `type` enum('zed','opensearch') NOT NULL default 'zed',
1881 `encoding` text default NULL,
1882 `description` text NOT NULL,
1884 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1887 -- Table structure for table `zebraqueue`
1890 DROP TABLE IF EXISTS `zebraqueue`;
1891 CREATE TABLE `zebraqueue` (
1892 `id` int(11) NOT NULL auto_increment,
1893 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1894 `operation` char(20) NOT NULL default '',
1895 `server` char(20) NOT NULL default '',
1896 `done` int(11) NOT NULL default '0',
1897 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1899 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1900 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1902 DROP TABLE IF EXISTS `services_throttle`;
1903 CREATE TABLE `services_throttle` (
1904 `service_type` varchar(10) NOT NULL default '',
1905 `service_count` varchar(45) default NULL,
1906 PRIMARY KEY (`service_type`)
1907 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1909 -- http://www.w3.org/International/articles/language-tags/
1912 DROP TABLE IF EXISTS language_subtag_registry;
1913 CREATE TABLE language_subtag_registry (
1915 type varchar(25), -- language-script-region-variant-extension-privateuse
1916 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1918 id int(11) NOT NULL auto_increment,
1920 KEY `subtag` (`subtag`)
1921 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1923 -- TODO: add suppress_scripts
1924 -- this maps three letter codes defined in iso639.2 back to their
1925 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1926 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1927 CREATE TABLE language_rfc4646_to_iso639 (
1928 rfc4646_subtag varchar(25),
1929 iso639_2_code varchar(25),
1930 id int(11) NOT NULL auto_increment,
1932 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1933 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1935 DROP TABLE IF EXISTS language_descriptions;
1936 CREATE TABLE language_descriptions (
1940 description varchar(255),
1941 id int(11) NOT NULL auto_increment,
1944 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1946 -- bi-directional support, keyed by script subcode
1947 DROP TABLE IF EXISTS language_script_bidi;
1948 CREATE TABLE language_script_bidi (
1949 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1950 bidi varchar(3), -- rtl ltr
1951 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1952 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1954 -- TODO: need to map language subtags to script subtags for detection
1955 -- of bidi when script is not specified (like ar, he)
1956 DROP TABLE IF EXISTS language_script_mapping;
1957 CREATE TABLE language_script_mapping (
1958 language_subtag varchar(25),
1959 script_subtag varchar(25),
1960 KEY `language_subtag` (`language_subtag`)
1961 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1963 DROP TABLE IF EXISTS `permissions`;
1964 CREATE TABLE `permissions` (
1965 `module_bit` int(11) NOT NULL DEFAULT 0,
1966 `code` varchar(64) DEFAULT NULL,
1967 `description` varchar(255) DEFAULT NULL,
1968 PRIMARY KEY (`module_bit`, `code`),
1969 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1970 ON DELETE CASCADE ON UPDATE CASCADE
1971 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1973 DROP TABLE IF EXISTS `serialitems`;
1974 CREATE TABLE `serialitems` (
1975 `itemnumber` int(11) NOT NULL,
1976 `serialid` int(11) NOT NULL,
1977 UNIQUE KEY `serialitemsidx` (`itemnumber`),
1978 KEY `serialitems_sfk_1` (`serialid`),
1979 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
1980 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1982 DROP TABLE IF EXISTS `user_permissions`;
1983 CREATE TABLE `user_permissions` (
1984 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1985 `module_bit` int(11) NOT NULL DEFAULT 0,
1986 `code` varchar(64) DEFAULT NULL,
1987 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1988 ON DELETE CASCADE ON UPDATE CASCADE,
1989 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
1990 ON DELETE CASCADE ON UPDATE CASCADE
1991 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1994 -- Table structure for table `tmp_holdsqueue`
1997 DROP TABLE IF EXISTS `tmp_holdsqueue`;
1998 CREATE TABLE `tmp_holdsqueue` (
1999 `biblionumber` int(11) default NULL,
2000 `itemnumber` int(11) default NULL,
2001 `barcode` varchar(20) default NULL,
2002 `surname` mediumtext NOT NULL,
2005 `borrowernumber` int(11) NOT NULL,
2006 `cardnumber` varchar(16) default NULL,
2007 `reservedate` date default NULL,
2009 `itemcallnumber` varchar(255) default NULL,
2010 `holdingbranch` varchar(10) default NULL,
2011 `pickbranch` varchar(10) default NULL,
2013 `item_level_request` tinyint(4) NOT NULL default 0
2014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2017 -- Table structure for table `message_queue`
2020 DROP TABLE IF EXISTS `message_queue`;
2021 CREATE TABLE `message_queue` (
2022 `message_id` int(11) NOT NULL auto_increment,
2023 `borrowernumber` int(11) default NULL,
2026 `metadata` text DEFAULT NULL,
2027 `letter_code` varchar(64) DEFAULT NULL,
2028 `message_transport_type` varchar(20) NOT NULL,
2029 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2030 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2031 `to_address` mediumtext,
2032 `from_address` mediumtext,
2033 `content_type` text,
2034 KEY `message_id` (`message_id`),
2035 KEY `borrowernumber` (`borrowernumber`),
2036 KEY `message_transport_type` (`message_transport_type`),
2037 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2038 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2039 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2042 -- Table structure for table `message_transport_types`
2045 DROP TABLE IF EXISTS `message_transport_types`;
2046 CREATE TABLE `message_transport_types` (
2047 `message_transport_type` varchar(20) NOT NULL,
2048 PRIMARY KEY (`message_transport_type`)
2049 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2052 -- Table structure for table `message_attributes`
2055 DROP TABLE IF EXISTS `message_attributes`;
2056 CREATE TABLE `message_attributes` (
2057 `message_attribute_id` int(11) NOT NULL auto_increment,
2058 `message_name` varchar(20) NOT NULL default '',
2059 `takes_days` tinyint(1) NOT NULL default '0',
2060 PRIMARY KEY (`message_attribute_id`),
2061 UNIQUE KEY `message_name` (`message_name`)
2062 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2065 -- Table structure for table `message_transports`
2068 DROP TABLE IF EXISTS `message_transports`;
2069 CREATE TABLE `message_transports` (
2070 `message_attribute_id` int(11) NOT NULL,
2071 `message_transport_type` varchar(20) NOT NULL,
2072 `is_digest` tinyint(1) NOT NULL default '0',
2073 `letter_module` varchar(20) NOT NULL default '',
2074 `letter_code` varchar(20) NOT NULL default '',
2075 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2076 KEY `message_transport_type` (`message_transport_type`),
2077 KEY `letter_module` (`letter_module`,`letter_code`),
2078 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2079 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2080 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2081 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2084 -- Table structure for table `borrower_message_preferences`
2087 DROP TABLE IF EXISTS `borrower_message_preferences`;
2088 CREATE TABLE `borrower_message_preferences` (
2089 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2090 `borrowernumber` int(11) default NULL,
2091 `categorycode` varchar(10) default NULL,
2092 `message_attribute_id` int(11) default '0',
2093 `days_in_advance` int(11) default '0',
2094 `wants_digest` tinyint(1) NOT NULL default '0',
2095 PRIMARY KEY (`borrower_message_preference_id`),
2096 KEY `borrowernumber` (`borrowernumber`),
2097 KEY `categorycode` (`categorycode`),
2098 KEY `message_attribute_id` (`message_attribute_id`),
2099 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2100 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2101 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2105 -- Table structure for table `borrower_message_transport_preferences`
2108 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2109 CREATE TABLE `borrower_message_transport_preferences` (
2110 `borrower_message_preference_id` int(11) NOT NULL default '0',
2111 `message_transport_type` varchar(20) NOT NULL default '0',
2112 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2113 KEY `message_transport_type` (`message_transport_type`),
2114 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,
2115 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
2116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2119 -- Table structure for the table branch_transfer_limits
2122 DROP TABLE IF EXISTS `branch_transfer_limits`;
2123 CREATE TABLE branch_transfer_limits (
2124 limitId int(8) NOT NULL auto_increment,
2125 toBranch varchar(10) NOT NULL,
2126 fromBranch varchar(10) NOT NULL,
2127 itemtype varchar(10) NULL,
2128 ccode varchar(10) NULL,
2129 PRIMARY KEY (limitId)
2130 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2133 -- Table structure for table `item_circulation_alert_preferences`
2136 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2137 CREATE TABLE `item_circulation_alert_preferences` (
2138 `id` int(11) NOT NULL auto_increment,
2139 `branchcode` varchar(10) NOT NULL,
2140 `categorycode` varchar(10) NOT NULL,
2141 `item_type` varchar(10) NOT NULL,
2142 `notification` varchar(16) NOT NULL,
2144 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2145 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2148 -- Table structure for table `messages`
2151 CREATE TABLE `messages` (
2152 `message_id` int(11) NOT NULL auto_increment,
2153 `borrowernumber` int(11) NOT NULL,
2154 `branchcode` varchar(4) default NULL,
2155 `message_type` varchar(1) NOT NULL,
2156 `message` text NOT NULL,
2157 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2158 PRIMARY KEY (`message_id`)
2159 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2162 -- Table structure for table `accountlines`
2165 DROP TABLE IF EXISTS `accountlines`;
2166 CREATE TABLE `accountlines` (
2167 `borrowernumber` int(11) NOT NULL default 0,
2168 `accountno` smallint(6) NOT NULL default 0,
2169 `itemnumber` int(11) default NULL,
2170 `date` date default NULL,
2171 `amount` decimal(28,6) default NULL,
2172 `description` mediumtext,
2173 `dispute` mediumtext,
2174 `accounttype` varchar(5) default NULL,
2175 `amountoutstanding` decimal(28,6) default NULL,
2176 `lastincrement` decimal(28,6) default NULL,
2177 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2178 `notify_id` int(11) NOT NULL default 0,
2179 `notify_level` int(2) NOT NULL default 0,
2180 KEY `acctsborridx` (`borrowernumber`),
2181 KEY `timeidx` (`timestamp`),
2182 KEY `itemnumber` (`itemnumber`),
2183 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2184 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2188 -- Table structure for table `accountoffsets`
2191 DROP TABLE IF EXISTS `accountoffsets`;
2192 CREATE TABLE `accountoffsets` (
2193 `borrowernumber` int(11) NOT NULL default 0,
2194 `accountno` smallint(6) NOT NULL default 0,
2195 `offsetaccount` smallint(6) NOT NULL default 0,
2196 `offsetamount` decimal(28,6) default NULL,
2197 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2198 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2202 -- Table structure for table `action_logs`
2205 DROP TABLE IF EXISTS `action_logs`;
2206 CREATE TABLE `action_logs` (
2207 `action_id` int(11) NOT NULL auto_increment,
2208 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2209 `user` int(11) NOT NULL default 0,
2212 `object` int(11) default NULL,
2214 PRIMARY KEY (`action_id`),
2215 KEY (`timestamp`,`user`)
2216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2219 -- Table structure for table `alert`
2222 DROP TABLE IF EXISTS `alert`;
2223 CREATE TABLE `alert` (
2224 `alertid` int(11) NOT NULL auto_increment,
2225 `borrowernumber` int(11) NOT NULL default 0,
2226 `type` varchar(10) NOT NULL default '',
2227 `externalid` varchar(20) NOT NULL default '',
2228 PRIMARY KEY (`alertid`),
2229 KEY `borrowernumber` (`borrowernumber`),
2230 KEY `type` (`type`,`externalid`)
2231 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2234 -- Table structure for table `aqbasketgroups`
2237 DROP TABLE IF EXISTS `aqbasketgroups`;
2238 CREATE TABLE `aqbasketgroups` (
2239 `id` int(11) NOT NULL auto_increment,
2240 `name` varchar(50) default NULL,
2241 `closed` tinyint(1) default NULL,
2242 `booksellerid` int(11) NOT NULL,
2244 KEY `booksellerid` (`booksellerid`),
2245 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2246 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2249 -- Table structure for table `aqbasket`
2252 DROP TABLE IF EXISTS `aqbasket`;
2253 CREATE TABLE `aqbasket` (
2254 `basketno` int(11) NOT NULL auto_increment,
2255 `basketname` varchar(50) default NULL,
2257 `booksellernote` mediumtext,
2258 `contractnumber` int(11),
2259 `creationdate` date default NULL,
2260 `closedate` date default NULL,
2261 `booksellerid` int(11) NOT NULL default 1,
2262 `authorisedby` varchar(10) default NULL,
2263 `booksellerinvoicenumber` mediumtext,
2264 `basketgroupid` int(11),
2265 PRIMARY KEY (`basketno`),
2266 KEY `booksellerid` (`booksellerid`),
2267 KEY `basketgroupid` (`basketgroupid`),
2268 KEY `contractnumber` (`contractnumber`),
2269 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2270 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2271 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2275 -- Table structure for table `aqbooksellers`
2278 DROP TABLE IF EXISTS `aqbooksellers`;
2279 CREATE TABLE `aqbooksellers` (
2280 `id` int(11) NOT NULL auto_increment,
2281 `name` mediumtext NOT NULL,
2282 `address1` mediumtext,
2283 `address2` mediumtext,
2284 `address3` mediumtext,
2285 `address4` mediumtext,
2286 `phone` varchar(30) default NULL,
2287 `accountnumber` mediumtext,
2288 `othersupplier` mediumtext,
2289 `currency` varchar(3) NOT NULL default '',
2290 `booksellerfax` mediumtext,
2292 `bookselleremail` mediumtext,
2293 `booksellerurl` mediumtext,
2294 `contact` varchar(100) default NULL,
2295 `postal` mediumtext,
2296 `url` varchar(255) default NULL,
2297 `contpos` varchar(100) default NULL,
2298 `contphone` varchar(100) default NULL,
2299 `contfax` varchar(100) default NULL,
2300 `contaltphone` varchar(100) default NULL,
2301 `contemail` varchar(100) default NULL,
2302 `contnotes` mediumtext,
2303 `active` tinyint(4) default NULL,
2304 `listprice` varchar(10) default NULL,
2305 `invoiceprice` varchar(10) default NULL,
2306 `gstreg` tinyint(4) default NULL,
2307 `listincgst` tinyint(4) default NULL,
2308 `invoiceincgst` tinyint(4) default NULL,
2309 `gstrate` decimal(6,4) default NULL,
2310 `discount` float(6,4) default NULL,
2311 `fax` varchar(50) default NULL,
2313 KEY `listprice` (`listprice`),
2314 KEY `invoiceprice` (`invoiceprice`),
2315 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2316 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2320 -- Table structure for table `aqbudgets`
2323 DROP TABLE IF EXISTS `aqbudgets`;
2324 CREATE TABLE `aqbudgets` (
2325 `budget_id` int(11) NOT NULL auto_increment,
2326 `budget_parent_id` int(11) default NULL,
2327 `budget_code` varchar(30) default NULL,
2328 `budget_name` varchar(80) default NULL,
2329 `budget_branchcode` varchar(10) default NULL,
2330 `budget_amount` decimal(28,6) NULL default '0.00',
2331 `budget_encumb` decimal(28,6) NULL default '0.00',
2332 `budget_expend` decimal(28,6) NULL default '0.00',
2333 `budget_notes` mediumtext,
2334 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2335 `budget_period_id` int(11) default NULL,
2336 `sort1_authcat` varchar(80) default NULL,
2337 `sort2_authcat` varchar(80) default NULL,
2338 `budget_owner_id` int(11) default NULL,
2339 `budget_permission` int(1) default '0',
2340 PRIMARY KEY (`budget_id`)
2341 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2345 -- Table structure for table `aqbudgetperiods`
2349 DROP TABLE IF EXISTS `aqbudgetperiods`;
2350 CREATE TABLE `aqbudgetperiods` (
2351 `budget_period_id` int(11) NOT NULL auto_increment,
2352 `budget_period_startdate` date NOT NULL,
2353 `budget_period_enddate` date NOT NULL,
2354 `budget_period_active` tinyint(1) default '0',
2355 `budget_period_description` mediumtext,
2356 `budget_period_total` decimal(28,6),
2357 `budget_period_locked` tinyint(1) default NULL,
2358 `sort1_authcat` varchar(10) default NULL,
2359 `sort2_authcat` varchar(10) default NULL,
2360 PRIMARY KEY (`budget_period_id`)
2361 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2364 -- Table structure for table `aqbudgets_planning`
2367 DROP TABLE IF EXISTS `aqbudgets_planning`;
2368 CREATE TABLE `aqbudgets_planning` (
2369 `plan_id` int(11) NOT NULL auto_increment,
2370 `budget_id` int(11) NOT NULL,
2371 `budget_period_id` int(11) NOT NULL,
2372 `estimated_amount` decimal(28,6) default NULL,
2373 `authcat` varchar(30) NOT NULL,
2374 `authvalue` varchar(30) NOT NULL,
2375 PRIMARY KEY (`plan_id`),
2376 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2377 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2380 -- Table structure for table 'aqcontract'
2383 DROP TABLE IF EXISTS `aqcontract`;
2384 CREATE TABLE `aqcontract` (
2385 `contractnumber` int(11) NOT NULL auto_increment,
2386 `contractstartdate` date default NULL,
2387 `contractenddate` date default NULL,
2388 `contractname` varchar(50) default NULL,
2389 `contractdescription` mediumtext,
2390 `booksellerid` int(11) not NULL,
2391 PRIMARY KEY (`contractnumber`),
2392 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2393 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2394 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2397 -- Table structure for table `aqorderdelivery`
2400 DROP TABLE IF EXISTS `aqorderdelivery`;
2401 CREATE TABLE `aqorderdelivery` (
2402 `ordernumber` date default NULL,
2403 `deliverynumber` smallint(6) NOT NULL default 0,
2404 `deliverydate` varchar(18) default NULL,
2405 `qtydelivered` smallint(6) default NULL,
2406 `deliverycomments` mediumtext
2407 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2410 -- Table structure for table `aqorders`
2413 DROP TABLE IF EXISTS `aqorders`;
2414 CREATE TABLE `aqorders` (
2415 `ordernumber` int(11) NOT NULL auto_increment,
2416 `biblionumber` int(11) default NULL,
2417 `entrydate` date default NULL,
2418 `quantity` smallint(6) default NULL,
2419 `currency` varchar(3) default NULL,
2420 `listprice` decimal(28,6) default NULL,
2421 `totalamount` decimal(28,6) default NULL,
2422 `datereceived` date default NULL,
2423 `booksellerinvoicenumber` mediumtext,
2424 `freight` decimal(28,6) default NULL,
2425 `unitprice` decimal(28,6) default NULL,
2426 `quantityreceived` smallint(6) default NULL,
2427 `cancelledby` varchar(10) default NULL,
2428 `datecancellationprinted` date default NULL,
2430 `supplierreference` mediumtext,
2431 `purchaseordernumber` mediumtext,
2432 `subscription` tinyint(1) default NULL,
2433 `serialid` varchar(30) default NULL,
2434 `basketno` int(11) default NULL,
2435 `biblioitemnumber` int(11) default NULL,
2436 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2437 `rrp` decimal(13,2) default NULL,
2438 `ecost` decimal(13,2) default NULL,
2439 `gst` decimal(13,2) default NULL,
2440 `budget_id` int(11) NOT NULL,
2441 `budgetgroup_id` int(11) NOT NULL,
2442 `budgetdate` date default NULL,
2443 `sort1` varchar(80) default NULL,
2444 `sort2` varchar(80) default NULL,
2445 `sort1_authcat` varchar(10) default NULL,
2446 `sort2_authcat` varchar(10) default NULL,
2447 `uncertainprice` tinyint(1),
2448 PRIMARY KEY (`ordernumber`),
2449 KEY `basketno` (`basketno`),
2450 KEY `biblionumber` (`biblionumber`),
2451 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2452 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2453 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2456 -- Table structure for table `aqorders_items`
2459 DROP TABLE IF EXISTS `aqorders_items`;
2460 CREATE TABLE `aqorders_items` (
2461 `ordernumber` int(11) NOT NULL,
2462 `itemnumber` int(11) NOT NULL,
2463 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2464 PRIMARY KEY (`itemnumber`),
2465 KEY `ordernumber` (`ordernumber`)
2466 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2468 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2469 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2470 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2471 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2472 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2473 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2474 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2475 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;