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,
725 `stocknumber` varchar(32) default NULL,
727 PRIMARY KEY (`itemnumber`),
728 KEY `delitembarcodeidx` (`barcode`),
729 KEY `delitemstocknumberidx` (`stocknumber`),
730 KEY `delitembinoidx` (`biblioitemnumber`),
731 KEY `delitembibnoidx` (`biblionumber`),
732 KEY `delhomebranch` (`homebranch`),
733 KEY `delholdingbranch` (`holdingbranch`)
734 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
737 -- Table structure for table `ethnicity`
740 DROP TABLE IF EXISTS `ethnicity`;
741 CREATE TABLE `ethnicity` (
742 `code` varchar(10) NOT NULL default '',
743 `name` varchar(255) default NULL,
745 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
748 -- Table structure for table `export_format`
751 DROP TABLE IF EXISTS `export_format`;
752 CREATE TABLE `export_format` (
753 `export_format_id` int(11) NOT NULL auto_increment,
754 `profile` varchar(255) NOT NULL,
755 `description` mediumtext NOT NULL,
756 `marcfields` mediumtext NOT NULL,
757 PRIMARY KEY (`export_format_id`)
758 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
762 -- Table structure for table `hold_fill_targets`
765 DROP TABLE IF EXISTS `hold_fill_targets`;
766 CREATE TABLE hold_fill_targets (
767 `borrowernumber` int(11) NOT NULL,
768 `biblionumber` int(11) NOT NULL,
769 `itemnumber` int(11) NOT NULL,
770 `source_branchcode` varchar(10) default NULL,
771 `item_level_request` tinyint(4) NOT NULL default 0,
772 PRIMARY KEY `itemnumber` (`itemnumber`),
773 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
774 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
775 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
776 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
777 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
778 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
779 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
780 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
781 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
782 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
785 -- Table structure for table `import_batches`
788 DROP TABLE IF EXISTS `import_batches`;
789 CREATE TABLE `import_batches` (
790 `import_batch_id` int(11) NOT NULL auto_increment,
791 `matcher_id` int(11) default NULL,
792 `template_id` int(11) default NULL,
793 `branchcode` varchar(10) default NULL,
794 `num_biblios` int(11) NOT NULL default 0,
795 `num_items` int(11) NOT NULL default 0,
796 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
797 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
798 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
799 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
800 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
801 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
802 `file_name` varchar(100),
803 `comments` mediumtext,
804 PRIMARY KEY (`import_batch_id`),
805 KEY `branchcode` (`branchcode`)
806 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
809 -- Table structure for table `import_records`
812 DROP TABLE IF EXISTS `import_records`;
813 CREATE TABLE `import_records` (
814 `import_record_id` int(11) NOT NULL auto_increment,
815 `import_batch_id` int(11) NOT NULL,
816 `branchcode` varchar(10) default NULL,
817 `record_sequence` int(11) NOT NULL default 0,
818 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
819 `import_date` DATE default NULL,
820 `marc` longblob NOT NULL,
821 `marcxml` longtext NOT NULL,
822 `marcxml_old` longtext NOT NULL,
823 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
824 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
825 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
826 `import_error` mediumtext,
827 `encoding` varchar(40) NOT NULL default '',
828 `z3950random` varchar(40) default NULL,
829 PRIMARY KEY (`import_record_id`),
830 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
831 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
832 KEY `branchcode` (`branchcode`),
833 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
834 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
837 -- Table structure for `import_record_matches`
839 DROP TABLE IF EXISTS `import_record_matches`;
840 CREATE TABLE `import_record_matches` (
841 `import_record_id` int(11) NOT NULL,
842 `candidate_match_id` int(11) NOT NULL,
843 `score` int(11) NOT NULL default 0,
844 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
845 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
846 KEY `record_score` (`import_record_id`, `score`)
847 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
850 -- Table structure for table `import_biblios`
853 DROP TABLE IF EXISTS `import_biblios`;
854 CREATE TABLE `import_biblios` (
855 `import_record_id` int(11) NOT NULL,
856 `matched_biblionumber` int(11) default NULL,
857 `control_number` varchar(25) default NULL,
858 `original_source` varchar(25) default NULL,
859 `title` varchar(128) default NULL,
860 `author` varchar(80) default NULL,
861 `isbn` varchar(30) default NULL,
862 `issn` varchar(9) default NULL,
863 `has_items` tinyint(1) NOT NULL default 0,
864 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
865 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
866 KEY `matched_biblionumber` (`matched_biblionumber`),
867 KEY `title` (`title`),
869 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
872 -- Table structure for table `import_items`
875 DROP TABLE IF EXISTS `import_items`;
876 CREATE TABLE `import_items` (
877 `import_items_id` int(11) NOT NULL auto_increment,
878 `import_record_id` int(11) NOT NULL,
879 `itemnumber` int(11) default NULL,
880 `branchcode` varchar(10) default NULL,
881 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
882 `marcxml` longtext NOT NULL,
883 `import_error` mediumtext,
884 PRIMARY KEY (`import_items_id`),
885 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
886 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
887 KEY `itemnumber` (`itemnumber`),
888 KEY `branchcode` (`branchcode`)
889 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
892 -- Table structure for table `issues`
895 DROP TABLE IF EXISTS `issues`;
896 CREATE TABLE `issues` (
897 `borrowernumber` int(11) default NULL,
898 `itemnumber` int(11) default NULL,
899 `date_due` date default NULL,
900 `branchcode` varchar(10) default NULL,
901 `issuingbranch` varchar(18) default NULL,
902 `returndate` date default NULL,
903 `lastreneweddate` date default NULL,
904 `return` varchar(4) default NULL,
905 `renewals` tinyint(4) default NULL,
906 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
907 `issuedate` date default NULL,
908 KEY `issuesborridx` (`borrowernumber`),
909 KEY `issuesitemidx` (`itemnumber`),
910 KEY `bordate` (`borrowernumber`,`timestamp`),
911 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
912 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
913 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
916 -- Table structure for table `issuingrules`
919 DROP TABLE IF EXISTS `issuingrules`;
920 CREATE TABLE `issuingrules` (
921 `categorycode` varchar(10) NOT NULL default '',
922 `itemtype` varchar(10) NOT NULL default '',
923 `restrictedtype` tinyint(1) default NULL,
924 `rentaldiscount` decimal(28,6) default NULL,
925 `reservecharge` decimal(28,6) default NULL,
926 `fine` decimal(28,6) default NULL,
927 `finedays` int(11) default NULL,
928 `firstremind` int(11) default NULL,
929 `chargeperiod` int(11) default NULL,
930 `accountsent` int(11) default NULL,
931 `chargename` varchar(100) default NULL,
932 `maxissueqty` int(4) default NULL,
933 `issuelength` int(4) default NULL,
934 `renewalsallowed` smallint(6) NOT NULL default "0",
935 `reservesallowed` smallint(6) NOT NULL default "0",
936 `branchcode` varchar(10) NOT NULL default '',
937 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
938 KEY `categorycode` (`categorycode`),
939 KEY `itemtype` (`itemtype`)
940 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
943 -- Table structure for table `items`
946 DROP TABLE IF EXISTS `items`;
947 CREATE TABLE `items` (
948 `itemnumber` int(11) NOT NULL auto_increment,
949 `biblionumber` int(11) NOT NULL default 0,
950 `biblioitemnumber` int(11) NOT NULL default 0,
951 `barcode` varchar(20) default NULL,
952 `dateaccessioned` date default NULL,
953 `booksellerid` mediumtext default NULL,
954 `homebranch` varchar(10) default NULL,
955 `price` decimal(8,2) default NULL,
956 `replacementprice` decimal(8,2) default NULL,
957 `replacementpricedate` date default NULL,
958 `datelastborrowed` date default NULL,
959 `datelastseen` date default NULL,
960 `stack` tinyint(1) default NULL,
961 `notforloan` tinyint(1) NOT NULL default 0,
962 `damaged` tinyint(1) NOT NULL default 0,
963 `itemlost` tinyint(1) NOT NULL default 0,
964 `wthdrawn` tinyint(1) NOT NULL default 0,
965 `itemcallnumber` varchar(255) default NULL,
966 `issues` smallint(6) default NULL,
967 `renewals` smallint(6) default NULL,
968 `reserves` smallint(6) default NULL,
969 `restricted` tinyint(1) default NULL,
970 `itemnotes` mediumtext,
971 `holdingbranch` varchar(10) default NULL,
972 `paidfor` mediumtext,
973 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
974 `location` varchar(80) default NULL,
975 `permanent_location` varchar(80) default NULL,
976 `onloan` date default NULL,
977 `cn_source` varchar(10) default NULL,
978 `cn_sort` varchar(30) default NULL,
979 `ccode` varchar(10) default NULL,
980 `materials` varchar(10) default NULL,
981 `uri` varchar(255) default NULL,
982 `itype` varchar(10) default NULL,
983 `more_subfields_xml` longtext default NULL,
984 `enumchron` varchar(80) default NULL,
985 `copynumber` varchar(32) default NULL,
986 `stocknumber` varchar(32) default NULL,
987 PRIMARY KEY (`itemnumber`),
988 UNIQUE KEY `itembarcodeidx` (`barcode`),
989 UNIQUE KEY `itemstocknumberidx` (`stocknumber`),
990 KEY `itembinoidx` (`biblioitemnumber`),
991 KEY `itembibnoidx` (`biblionumber`),
992 KEY `homebranch` (`homebranch`),
993 KEY `holdingbranch` (`holdingbranch`),
994 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
995 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
996 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
997 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1000 -- Table structure for table `itemtypes`
1003 DROP TABLE IF EXISTS `itemtypes`;
1004 CREATE TABLE `itemtypes` (
1005 `itemtype` varchar(10) NOT NULL default '',
1006 `description` mediumtext,
1007 `rentalcharge` double(16,4) default NULL,
1008 `notforloan` smallint(6) default NULL,
1009 `imageurl` varchar(200) default NULL,
1011 PRIMARY KEY (`itemtype`),
1012 UNIQUE KEY `itemtype` (`itemtype`)
1013 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1016 -- Table structure for table `labels_batches`
1019 DROP TABLE IF EXISTS `labels_batches`;
1020 CREATE TABLE `labels_batches` (
1021 `label_id` int(11) NOT NULL auto_increment,
1022 `batch_id` int(10) NOT NULL default '1',
1023 `item_number` int(11) NOT NULL default '0',
1024 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1025 `branch_code` varchar(10) NOT NULL default 'NB',
1026 PRIMARY KEY USING BTREE (`label_id`),
1027 KEY `branch_fk` (`branch_code`),
1028 KEY `item_fk` (`item_number`),
1029 CONSTRAINT `item_fk_constraint` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE,
1030 CONSTRAINT `branch_fk_constraint` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE
1031 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1034 -- Table structure for table `labels_layouts`
1037 DROP TABLE IF EXISTS `labels_layouts`;
1038 CREATE TABLE `labels_layouts` (
1039 `layout_id` int(4) NOT NULL auto_increment,
1040 `barcode_type` char(100) NOT NULL default 'CODE39',
1041 `printing_type` char(32) NOT NULL default 'BAR',
1042 `layout_name` char(20) NOT NULL default 'DEFAULT',
1043 `guidebox` int(1) default '0',
1044 `font` char(10) character set utf8 collate utf8_unicode_ci NOT NULL default 'TR',
1045 `font_size` int(4) NOT NULL default '10',
1046 `callnum_split` int(1) default '0',
1047 `text_justify` char(1) character set utf8 collate utf8_unicode_ci NOT NULL default 'L',
1048 `format_string` varchar(210) NOT NULL default 'barcode',
1049 PRIMARY KEY USING BTREE (`layout_id`)
1050 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1053 -- Table structure for table `labels_templates`
1056 DROP TABLE IF EXISTS `labels_templates`;
1057 CREATE TABLE `labels_templates` (
1058 `template_id` int(4) NOT NULL auto_increment,
1059 `profile_id` int(4) default NULL,
1060 `template_code` char(100) NOT NULL default 'DEFAULT TEMPLATE',
1061 `template_desc` char(100) NOT NULL default 'Default description',
1062 `page_width` float NOT NULL default '0',
1063 `page_height` float NOT NULL default '0',
1064 `label_width` float NOT NULL default '0',
1065 `label_height` float NOT NULL default '0',
1066 `top_text_margin` float NOT NULL default '0',
1067 `left_text_margin` float NOT NULL default '0',
1068 `top_margin` float NOT NULL default '0',
1069 `left_margin` float NOT NULL default '0',
1070 `cols` int(2) NOT NULL default '0',
1071 `rows` int(2) NOT NULL default '0',
1072 `col_gap` float NOT NULL default '0',
1073 `row_gap` float NOT NULL default '0',
1074 `units` char(20) NOT NULL default 'POINT',
1075 PRIMARY KEY (`template_id`),
1076 KEY `template_profile_fk_constraint` (`profile_id`)
1077 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1080 -- Table structure for table `letter`
1083 DROP TABLE IF EXISTS `letter`;
1084 CREATE TABLE `letter` (
1085 `module` varchar(20) NOT NULL default '',
1086 `code` varchar(20) NOT NULL default '',
1087 `name` varchar(100) NOT NULL default '',
1088 `title` varchar(200) NOT NULL default '',
1090 PRIMARY KEY (`module`,`code`)
1091 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1094 -- Table structure for table `marc_subfield_structure`
1097 DROP TABLE IF EXISTS `marc_subfield_structure`;
1098 CREATE TABLE `marc_subfield_structure` (
1099 `tagfield` varchar(3) NOT NULL default '',
1100 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1101 `liblibrarian` varchar(255) NOT NULL default '',
1102 `libopac` varchar(255) NOT NULL default '',
1103 `repeatable` tinyint(4) NOT NULL default 0,
1104 `mandatory` tinyint(4) NOT NULL default 0,
1105 `kohafield` varchar(40) default NULL,
1106 `tab` tinyint(1) default NULL,
1107 `authorised_value` varchar(20) default NULL,
1108 `authtypecode` varchar(20) default NULL,
1109 `value_builder` varchar(80) default NULL,
1110 `isurl` tinyint(1) default NULL,
1111 `hidden` tinyint(1) default NULL,
1112 `frameworkcode` varchar(4) NOT NULL default '',
1113 `seealso` varchar(1100) default NULL,
1114 `link` varchar(80) default NULL,
1115 `defaultvalue` text default NULL,
1116 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1117 KEY `kohafield_2` (`kohafield`),
1118 KEY `tab` (`frameworkcode`,`tab`),
1119 KEY `kohafield` (`frameworkcode`,`kohafield`)
1120 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1123 -- Table structure for table `marc_tag_structure`
1126 DROP TABLE IF EXISTS `marc_tag_structure`;
1127 CREATE TABLE `marc_tag_structure` (
1128 `tagfield` varchar(3) NOT NULL default '',
1129 `liblibrarian` varchar(255) NOT NULL default '',
1130 `libopac` varchar(255) NOT NULL default '',
1131 `repeatable` tinyint(4) NOT NULL default 0,
1132 `mandatory` tinyint(4) NOT NULL default 0,
1133 `authorised_value` varchar(10) default NULL,
1134 `frameworkcode` varchar(4) NOT NULL default '',
1135 PRIMARY KEY (`frameworkcode`,`tagfield`)
1136 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1139 -- Table structure for table `marc_matchers`
1142 DROP TABLE IF EXISTS `marc_matchers`;
1143 CREATE TABLE `marc_matchers` (
1144 `matcher_id` int(11) NOT NULL auto_increment,
1145 `code` varchar(10) NOT NULL default '',
1146 `description` varchar(255) NOT NULL default '',
1147 `record_type` varchar(10) NOT NULL default 'biblio',
1148 `threshold` int(11) NOT NULL default 0,
1149 PRIMARY KEY (`matcher_id`),
1150 KEY `code` (`code`),
1151 KEY `record_type` (`record_type`)
1152 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1155 -- Table structure for table `matchpoints`
1157 DROP TABLE IF EXISTS `matchpoints`;
1158 CREATE TABLE `matchpoints` (
1159 `matcher_id` int(11) NOT NULL,
1160 `matchpoint_id` int(11) NOT NULL auto_increment,
1161 `search_index` varchar(30) NOT NULL default '',
1162 `score` int(11) NOT NULL default 0,
1163 PRIMARY KEY (`matchpoint_id`),
1164 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1165 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1170 -- Table structure for table `matchpoint_components`
1172 DROP TABLE IF EXISTS `matchpoint_components`;
1173 CREATE TABLE `matchpoint_components` (
1174 `matchpoint_id` int(11) NOT NULL,
1175 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1176 sequence int(11) NOT NULL default 0,
1177 tag varchar(3) NOT NULL default '',
1178 subfields varchar(40) NOT NULL default '',
1179 offset int(4) NOT NULL default 0,
1180 length int(4) NOT NULL default 0,
1181 PRIMARY KEY (`matchpoint_component_id`),
1182 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1183 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1184 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1188 -- Table structure for table `matcher_component_norms`
1190 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1191 CREATE TABLE `matchpoint_component_norms` (
1192 `matchpoint_component_id` int(11) NOT NULL,
1193 `sequence` int(11) NOT NULL default 0,
1194 `norm_routine` varchar(50) NOT NULL default '',
1195 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1196 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1197 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1201 -- Table structure for table `matcher_matchpoints`
1203 DROP TABLE IF EXISTS `matcher_matchpoints`;
1204 CREATE TABLE `matcher_matchpoints` (
1205 `matcher_id` int(11) NOT NULL,
1206 `matchpoint_id` int(11) NOT NULL,
1207 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1208 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1209 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1210 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1214 -- Table structure for table `matchchecks`
1216 DROP TABLE IF EXISTS `matchchecks`;
1217 CREATE TABLE `matchchecks` (
1218 `matcher_id` int(11) NOT NULL,
1219 `matchcheck_id` int(11) NOT NULL auto_increment,
1220 `source_matchpoint_id` int(11) NOT NULL,
1221 `target_matchpoint_id` int(11) NOT NULL,
1222 PRIMARY KEY (`matchcheck_id`),
1223 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1224 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1225 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1226 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1227 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1228 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1232 -- Table structure for table `notifys`
1235 DROP TABLE IF EXISTS `notifys`;
1236 CREATE TABLE `notifys` (
1237 `notify_id` int(11) NOT NULL default 0,
1238 `borrowernumber` int(11) NOT NULL default 0,
1239 `itemnumber` int(11) NOT NULL default 0,
1240 `notify_date` date default NULL,
1241 `notify_send_date` date default NULL,
1242 `notify_level` int(1) NOT NULL default 0,
1243 `method` varchar(20) NOT NULL default ''
1244 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1247 -- Table structure for table `nozebra`
1250 DROP TABLE IF EXISTS `nozebra`;
1251 CREATE TABLE `nozebra` (
1252 `server` varchar(20) NOT NULL,
1253 `indexname` varchar(40) NOT NULL,
1254 `value` varchar(250) NOT NULL,
1255 `biblionumbers` longtext NOT NULL,
1256 KEY `indexname` (`server`,`indexname`),
1257 KEY `value` (`server`,`value`))
1258 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1261 -- Table structure for table `old_issues`
1264 DROP TABLE IF EXISTS `old_issues`;
1265 CREATE TABLE `old_issues` (
1266 `borrowernumber` int(11) default NULL,
1267 `itemnumber` int(11) default NULL,
1268 `date_due` date default NULL,
1269 `branchcode` varchar(10) default NULL,
1270 `issuingbranch` varchar(18) default NULL,
1271 `returndate` date default NULL,
1272 `lastreneweddate` date default NULL,
1273 `return` varchar(4) default NULL,
1274 `renewals` tinyint(4) default NULL,
1275 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1276 `issuedate` date default NULL,
1277 KEY `old_issuesborridx` (`borrowernumber`),
1278 KEY `old_issuesitemidx` (`itemnumber`),
1279 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1280 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1281 ON DELETE SET NULL ON UPDATE SET NULL,
1282 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1283 ON DELETE SET NULL ON UPDATE SET NULL
1284 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1287 -- Table structure for table `old_reserves`
1289 DROP TABLE IF EXISTS `old_reserves`;
1290 CREATE TABLE `old_reserves` (
1291 `borrowernumber` int(11) default NULL,
1292 `reservedate` date default NULL,
1293 `biblionumber` int(11) default NULL,
1294 `constrainttype` varchar(1) default NULL,
1295 `branchcode` varchar(10) default NULL,
1296 `notificationdate` date default NULL,
1297 `reminderdate` date default NULL,
1298 `cancellationdate` date default NULL,
1299 `reservenotes` mediumtext,
1300 `priority` smallint(6) default NULL,
1301 `found` varchar(1) default NULL,
1302 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1303 `itemnumber` int(11) default NULL,
1304 `waitingdate` date default NULL,
1305 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1306 KEY `old_reserves_biblionumber` (`biblionumber`),
1307 KEY `old_reserves_itemnumber` (`itemnumber`),
1308 KEY `old_reserves_branchcode` (`branchcode`),
1309 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1310 ON DELETE SET NULL ON UPDATE SET NULL,
1311 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1312 ON DELETE SET NULL ON UPDATE SET NULL,
1313 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1314 ON DELETE SET NULL ON UPDATE SET NULL
1315 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1318 -- Table structure for table `opac_news`
1321 DROP TABLE IF EXISTS `opac_news`;
1322 CREATE TABLE `opac_news` (
1323 `idnew` int(10) unsigned NOT NULL auto_increment,
1324 `title` varchar(250) NOT NULL default '',
1325 `new` text NOT NULL,
1326 `lang` varchar(25) NOT NULL default '',
1327 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1328 `expirationdate` date default NULL,
1329 `number` int(11) default NULL,
1330 PRIMARY KEY (`idnew`)
1331 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1334 -- Table structure for table `overduerules`
1337 DROP TABLE IF EXISTS `overduerules`;
1338 CREATE TABLE `overduerules` (
1339 `branchcode` varchar(10) NOT NULL default '',
1340 `categorycode` varchar(10) NOT NULL default '',
1341 `delay1` int(4) default 0,
1342 `letter1` varchar(20) default NULL,
1343 `debarred1` varchar(1) default 0,
1344 `delay2` int(4) default 0,
1345 `debarred2` varchar(1) default 0,
1346 `letter2` varchar(20) default NULL,
1347 `delay3` int(4) default 0,
1348 `letter3` varchar(20) default NULL,
1349 `debarred3` int(1) default 0,
1350 PRIMARY KEY (`branchcode`,`categorycode`)
1351 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1354 -- Table structure for table `patroncards`
1357 DROP TABLE IF EXISTS `patroncards`;
1358 CREATE TABLE `patroncards` (
1359 `cardid` int(11) NOT NULL auto_increment,
1360 `batch_id` varchar(10) NOT NULL default '1',
1361 `borrowernumber` int(11) NOT NULL,
1362 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1363 PRIMARY KEY (`cardid`),
1364 KEY `patroncards_ibfk_1` (`borrowernumber`),
1365 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1366 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1369 -- Table structure for table `patronimage`
1372 DROP TABLE IF EXISTS `patronimage`;
1373 CREATE TABLE `patronimage` (
1374 `cardnumber` varchar(16) NOT NULL,
1375 `mimetype` varchar(15) NOT NULL,
1376 `imagefile` mediumblob NOT NULL,
1377 PRIMARY KEY (`cardnumber`),
1378 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1382 -- Table structure for table `printers`
1385 DROP TABLE IF EXISTS `printers`;
1386 CREATE TABLE `printers` (
1387 `printername` varchar(40) NOT NULL default '',
1388 `printqueue` varchar(20) default NULL,
1389 `printtype` varchar(20) default NULL,
1390 PRIMARY KEY (`printername`)
1391 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1394 -- Table structure for table `printers_profile`
1397 DROP TABLE IF EXISTS `printers_profile`;
1398 CREATE TABLE `printers_profile` (
1399 `profile_id` int(4) NOT NULL auto_increment,
1400 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1401 `template_id` int(4) NOT NULL default '0',
1402 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1403 `offset_horz` float NOT NULL default '0',
1404 `offset_vert` float NOT NULL default '0',
1405 `creep_horz` float NOT NULL default '0',
1406 `creep_vert` float NOT NULL default '0',
1407 `units` char(20) NOT NULL default 'POINT',
1408 PRIMARY KEY (`profile_id`),
1409 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`)
1410 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1413 -- Table structure for table `repeatable_holidays`
1416 DROP TABLE IF EXISTS `repeatable_holidays`;
1417 CREATE TABLE `repeatable_holidays` (
1418 `id` int(11) NOT NULL auto_increment,
1419 `branchcode` varchar(10) NOT NULL default '',
1420 `weekday` smallint(6) default NULL,
1421 `day` smallint(6) default NULL,
1422 `month` smallint(6) default NULL,
1423 `title` varchar(50) NOT NULL default '',
1424 `description` text NOT NULL,
1426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1429 -- Table structure for table `reports_dictionary`
1432 DROP TABLE IF EXISTS `reports_dictionary`;
1433 CREATE TABLE reports_dictionary (
1434 `id` int(11) NOT NULL auto_increment,
1435 `name` varchar(255) default NULL,
1437 `date_created` datetime default NULL,
1438 `date_modified` datetime default NULL,
1440 `area` int(11) default NULL,
1442 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1445 -- Table structure for table `reserveconstraints`
1448 DROP TABLE IF EXISTS `reserveconstraints`;
1449 CREATE TABLE `reserveconstraints` (
1450 `borrowernumber` int(11) NOT NULL default 0,
1451 `reservedate` date default NULL,
1452 `biblionumber` int(11) NOT NULL default 0,
1453 `biblioitemnumber` int(11) default NULL,
1454 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1455 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1458 -- Table structure for table `reserves`
1461 DROP TABLE IF EXISTS `reserves`;
1462 CREATE TABLE `reserves` (
1463 `borrowernumber` int(11) NOT NULL default 0,
1464 `reservedate` date default NULL,
1465 `biblionumber` int(11) NOT NULL default 0,
1466 `constrainttype` varchar(1) default NULL,
1467 `branchcode` varchar(10) default NULL,
1468 `notificationdate` date default NULL,
1469 `reminderdate` date default NULL,
1470 `cancellationdate` date default NULL,
1471 `reservenotes` mediumtext,
1472 `priority` smallint(6) default NULL,
1473 `found` varchar(1) default NULL,
1474 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1475 `itemnumber` int(11) default NULL,
1476 `waitingdate` date default NULL,
1477 KEY `borrowernumber` (`borrowernumber`),
1478 KEY `biblionumber` (`biblionumber`),
1479 KEY `itemnumber` (`itemnumber`),
1480 KEY `branchcode` (`branchcode`),
1481 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1482 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1483 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1484 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1485 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1488 -- Table structure for table `reviews`
1491 DROP TABLE IF EXISTS `reviews`;
1492 CREATE TABLE `reviews` (
1493 `reviewid` int(11) NOT NULL auto_increment,
1494 `borrowernumber` int(11) default NULL,
1495 `biblionumber` int(11) default NULL,
1497 `approved` tinyint(4) default NULL,
1498 `datereviewed` datetime default NULL,
1499 PRIMARY KEY (`reviewid`)
1500 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1503 -- Table structure for table `roadtype`
1506 DROP TABLE IF EXISTS `roadtype`;
1507 CREATE TABLE `roadtype` (
1508 `roadtypeid` int(11) NOT NULL auto_increment,
1509 `road_type` varchar(100) NOT NULL default '',
1510 PRIMARY KEY (`roadtypeid`)
1511 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1514 -- Table structure for table `saved_sql`
1517 DROP TABLE IF EXISTS `saved_sql`;
1518 CREATE TABLE saved_sql (
1519 `id` int(11) NOT NULL auto_increment,
1520 `borrowernumber` int(11) default NULL,
1521 `date_created` datetime default NULL,
1522 `last_modified` datetime default NULL,
1524 `last_run` datetime default NULL,
1525 `report_name` varchar(255) default NULL,
1526 `type` varchar(255) default NULL,
1529 KEY boridx (`borrowernumber`)
1530 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1534 -- Table structure for `saved_reports`
1537 DROP TABLE IF EXISTS `saved_reports`;
1538 CREATE TABLE saved_reports (
1539 `id` int(11) NOT NULL auto_increment,
1540 `report_id` int(11) default NULL,
1542 `date_run` datetime default NULL,
1544 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1548 -- Table structure for table `search_history`
1551 DROP TABLE IF EXISTS `search_history`;
1552 CREATE TABLE IF NOT EXISTS `search_history` (
1553 `userid` int(11) NOT NULL,
1554 `sessionid` varchar(32) NOT NULL,
1555 `query_desc` varchar(255) NOT NULL,
1556 `query_cgi` varchar(255) NOT NULL,
1557 `total` int(11) NOT NULL,
1558 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1559 KEY `userid` (`userid`),
1560 KEY `sessionid` (`sessionid`)
1561 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1565 -- Table structure for table `serial`
1568 DROP TABLE IF EXISTS `serial`;
1569 CREATE TABLE `serial` (
1570 `serialid` int(11) NOT NULL auto_increment,
1571 `biblionumber` varchar(100) NOT NULL default '',
1572 `subscriptionid` varchar(100) NOT NULL default '',
1573 `serialseq` varchar(100) NOT NULL default '',
1574 `status` tinyint(4) NOT NULL default 0,
1575 `planneddate` date default NULL,
1577 `publisheddate` date default NULL,
1578 `itemnumber` text default NULL,
1579 `claimdate` date default NULL,
1580 `routingnotes` text,
1581 PRIMARY KEY (`serialid`)
1582 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1585 -- Table structure for table `sessions`
1588 DROP TABLE IF EXISTS sessions;
1589 CREATE TABLE sessions (
1590 `id` varchar(32) NOT NULL,
1591 `a_session` text NOT NULL,
1593 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1596 -- Table structure for table `special_holidays`
1599 DROP TABLE IF EXISTS `special_holidays`;
1600 CREATE TABLE `special_holidays` (
1601 `id` int(11) NOT NULL auto_increment,
1602 `branchcode` varchar(10) NOT NULL default '',
1603 `day` smallint(6) NOT NULL default 0,
1604 `month` smallint(6) NOT NULL default 0,
1605 `year` smallint(6) NOT NULL default 0,
1606 `isexception` smallint(1) NOT NULL default 1,
1607 `title` varchar(50) NOT NULL default '',
1608 `description` text NOT NULL,
1610 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1613 -- Table structure for table `statistics`
1616 DROP TABLE IF EXISTS `statistics`;
1617 CREATE TABLE `statistics` (
1618 `datetime` datetime default NULL,
1619 `branch` varchar(10) default NULL,
1620 `proccode` varchar(4) default NULL,
1621 `value` double(16,4) default NULL,
1622 `type` varchar(16) default NULL,
1624 `usercode` varchar(10) default NULL,
1625 `itemnumber` int(11) default NULL,
1626 `itemtype` varchar(10) default NULL,
1627 `borrowernumber` int(11) default NULL,
1628 `associatedborrower` int(11) default NULL,
1629 KEY `timeidx` (`datetime`)
1630 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1633 -- Table structure for table `stopwords`
1636 DROP TABLE IF EXISTS `stopwords`;
1637 CREATE TABLE `stopwords` (
1638 `word` varchar(255) default NULL
1639 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1642 -- Table structure for table `subscription`
1645 DROP TABLE IF EXISTS `subscription`;
1646 CREATE TABLE `subscription` (
1647 `biblionumber` int(11) NOT NULL default 0,
1648 `subscriptionid` int(11) NOT NULL auto_increment,
1649 `librarian` varchar(100) default '',
1650 `startdate` date default NULL,
1651 `aqbooksellerid` int(11) default 0,
1652 `cost` int(11) default 0,
1653 `aqbudgetid` int(11) default 0,
1654 `weeklength` int(11) default 0,
1655 `monthlength` int(11) default 0,
1656 `numberlength` int(11) default 0,
1657 `periodicity` tinyint(4) default 0,
1658 `dow` varchar(100) default '',
1659 `numberingmethod` varchar(100) default '',
1661 `status` varchar(100) NOT NULL default '',
1662 `add1` int(11) default 0,
1663 `every1` int(11) default 0,
1664 `whenmorethan1` int(11) default 0,
1665 `setto1` int(11) default NULL,
1666 `lastvalue1` int(11) default NULL,
1667 `add2` int(11) default 0,
1668 `every2` int(11) default 0,
1669 `whenmorethan2` int(11) default 0,
1670 `setto2` int(11) default NULL,
1671 `lastvalue2` int(11) default NULL,
1672 `add3` int(11) default 0,
1673 `every3` int(11) default 0,
1674 `innerloop1` int(11) default 0,
1675 `innerloop2` int(11) default 0,
1676 `innerloop3` int(11) default 0,
1677 `whenmorethan3` int(11) default 0,
1678 `setto3` int(11) default NULL,
1679 `lastvalue3` int(11) default NULL,
1680 `issuesatonce` tinyint(3) NOT NULL default 1,
1681 `firstacquidate` date default NULL,
1682 `manualhistory` tinyint(1) NOT NULL default 0,
1683 `irregularity` text,
1684 `letter` varchar(20) default NULL,
1685 `numberpattern` tinyint(3) default 0,
1686 `distributedto` text,
1687 `internalnotes` longtext,
1689 `location` varchar(80) NULL default '',
1690 `branchcode` varchar(10) NOT NULL default '',
1691 `hemisphere` tinyint(3) default 0,
1692 `lastbranch` varchar(10),
1693 `serialsadditems` tinyint(1) NOT NULL default '0',
1694 `staffdisplaycount` VARCHAR(10) NULL,
1695 `opacdisplaycount` VARCHAR(10) NULL,
1696 `graceperiod` int(11) NOT NULL default '0',
1697 PRIMARY KEY (`subscriptionid`)
1698 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1701 -- Table structure for table `subscriptionhistory`
1704 DROP TABLE IF EXISTS `subscriptionhistory`;
1705 CREATE TABLE `subscriptionhistory` (
1706 `biblionumber` int(11) NOT NULL default 0,
1707 `subscriptionid` int(11) NOT NULL default 0,
1708 `histstartdate` date default NULL,
1709 `histenddate` date default NULL,
1710 `missinglist` longtext NOT NULL,
1711 `recievedlist` longtext NOT NULL,
1712 `opacnote` varchar(150) NOT NULL default '',
1713 `librariannote` varchar(150) NOT NULL default '',
1714 PRIMARY KEY (`subscriptionid`),
1715 KEY `biblionumber` (`biblionumber`)
1716 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1719 -- Table structure for table `subscriptionroutinglist`
1722 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1723 CREATE TABLE `subscriptionroutinglist` (
1724 `routingid` int(11) NOT NULL auto_increment,
1725 `borrowernumber` int(11) default NULL,
1726 `ranking` int(11) default NULL,
1727 `subscriptionid` int(11) default NULL,
1728 PRIMARY KEY (`routingid`)
1729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1732 -- Table structure for table `suggestions`
1735 DROP TABLE IF EXISTS `suggestions`;
1736 CREATE TABLE `suggestions` (
1737 `suggestionid` int(8) NOT NULL auto_increment,
1738 `suggestedby` int(11) NOT NULL default 0,
1739 `suggesteddate` date NOT NULL default 0,
1740 `managedby` int(11) default NULL,
1741 `manageddate` date default NULL,
1742 `STATUS` varchar(10) NOT NULL default '',
1744 `author` varchar(80) default NULL,
1745 `title` varchar(80) default NULL,
1746 `copyrightdate` smallint(6) default NULL,
1747 `publishercode` varchar(255) default NULL,
1748 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1749 `volumedesc` varchar(255) default NULL,
1750 `publicationyear` smallint(6) default 0,
1751 `place` varchar(255) default NULL,
1752 `isbn` varchar(30) default NULL,
1753 `mailoverseeing` smallint(1) default 0,
1754 `biblionumber` int(11) default NULL,
1756 PRIMARY KEY (`suggestionid`),
1757 KEY `suggestedby` (`suggestedby`),
1758 KEY `managedby` (`managedby`)
1759 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1762 -- Table structure for table `systempreferences`
1765 DROP TABLE IF EXISTS `systempreferences`;
1766 CREATE TABLE `systempreferences` (
1767 `variable` varchar(50) NOT NULL default '',
1769 `options` mediumtext,
1771 `type` varchar(20) default NULL,
1772 PRIMARY KEY (`variable`)
1773 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1776 -- Table structure for table `tags`
1779 DROP TABLE IF EXISTS `tags`;
1780 CREATE TABLE `tags` (
1781 `entry` varchar(255) NOT NULL default '',
1782 `weight` bigint(20) NOT NULL default 0,
1783 PRIMARY KEY (`entry`)
1784 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1787 -- Table structure for table `tags_all`
1790 DROP TABLE IF EXISTS `tags_all`;
1791 CREATE TABLE `tags_all` (
1792 `tag_id` int(11) NOT NULL auto_increment,
1793 `borrowernumber` int(11) NOT NULL,
1794 `biblionumber` int(11) NOT NULL,
1795 `term` varchar(255) NOT NULL,
1796 `language` int(4) default NULL,
1797 `date_created` datetime NOT NULL,
1798 PRIMARY KEY (`tag_id`),
1799 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1800 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1801 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1802 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1803 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1804 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1805 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1808 -- Table structure for table `tags_approval`
1811 DROP TABLE IF EXISTS `tags_approval`;
1812 CREATE TABLE `tags_approval` (
1813 `term` varchar(255) NOT NULL,
1814 `approved` int(1) NOT NULL default '0',
1815 `date_approved` datetime default NULL,
1816 `approved_by` int(11) default NULL,
1817 `weight_total` int(9) NOT NULL default '1',
1818 PRIMARY KEY (`term`),
1819 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1820 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1821 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1822 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1825 -- Table structure for table `tags_index`
1828 DROP TABLE IF EXISTS `tags_index`;
1829 CREATE TABLE `tags_index` (
1830 `term` varchar(255) NOT NULL,
1831 `biblionumber` int(11) NOT NULL,
1832 `weight` int(9) NOT NULL default '1',
1833 PRIMARY KEY (`term`,`biblionumber`),
1834 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1835 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1836 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1837 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1838 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1839 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1842 -- Table structure for table `userflags`
1845 DROP TABLE IF EXISTS `userflags`;
1846 CREATE TABLE `userflags` (
1847 `bit` int(11) NOT NULL default 0,
1848 `flag` varchar(30) default NULL,
1849 `flagdesc` varchar(255) default NULL,
1850 `defaulton` int(11) default NULL,
1852 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1855 -- Table structure for table `virtualshelves`
1858 DROP TABLE IF EXISTS `virtualshelves`;
1859 CREATE TABLE `virtualshelves` (
1860 `shelfnumber` int(11) NOT NULL auto_increment,
1861 `shelfname` varchar(255) default NULL,
1862 `owner` varchar(80) default NULL,
1863 `category` varchar(1) default NULL,
1864 `sortfield` varchar(16) default NULL,
1865 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1866 PRIMARY KEY (`shelfnumber`)
1867 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1870 -- Table structure for table `virtualshelfcontents`
1873 DROP TABLE IF EXISTS `virtualshelfcontents`;
1874 CREATE TABLE `virtualshelfcontents` (
1875 `shelfnumber` int(11) NOT NULL default 0,
1876 `biblionumber` int(11) NOT NULL default 0,
1877 `flags` int(11) default NULL,
1878 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1879 KEY `shelfnumber` (`shelfnumber`),
1880 KEY `biblionumber` (`biblionumber`),
1881 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1882 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1883 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1886 -- Table structure for table `z3950servers`
1889 DROP TABLE IF EXISTS `z3950servers`;
1890 CREATE TABLE `z3950servers` (
1891 `host` varchar(255) default NULL,
1892 `port` int(11) default NULL,
1893 `db` varchar(255) default NULL,
1894 `userid` varchar(255) default NULL,
1895 `password` varchar(255) default NULL,
1897 `id` int(11) NOT NULL auto_increment,
1898 `checked` smallint(6) default NULL,
1899 `rank` int(11) default NULL,
1900 `syntax` varchar(80) default NULL,
1902 `position` enum('primary','secondary','') NOT NULL default 'primary',
1903 `type` enum('zed','opensearch') NOT NULL default 'zed',
1904 `encoding` text default NULL,
1905 `description` text NOT NULL,
1907 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1910 -- Table structure for table `zebraqueue`
1913 DROP TABLE IF EXISTS `zebraqueue`;
1914 CREATE TABLE `zebraqueue` (
1915 `id` int(11) NOT NULL auto_increment,
1916 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1917 `operation` char(20) NOT NULL default '',
1918 `server` char(20) NOT NULL default '',
1919 `done` int(11) NOT NULL default '0',
1920 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1922 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1923 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1925 DROP TABLE IF EXISTS `services_throttle`;
1926 CREATE TABLE `services_throttle` (
1927 `service_type` varchar(10) NOT NULL default '',
1928 `service_count` varchar(45) default NULL,
1929 PRIMARY KEY (`service_type`)
1930 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1932 -- http://www.w3.org/International/articles/language-tags/
1935 DROP TABLE IF EXISTS language_subtag_registry;
1936 CREATE TABLE language_subtag_registry (
1938 type varchar(25), -- language-script-region-variant-extension-privateuse
1939 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1941 id int(11) NOT NULL auto_increment,
1943 KEY `subtag` (`subtag`)
1944 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1946 -- TODO: add suppress_scripts
1947 -- this maps three letter codes defined in iso639.2 back to their
1948 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1949 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1950 CREATE TABLE language_rfc4646_to_iso639 (
1951 rfc4646_subtag varchar(25),
1952 iso639_2_code varchar(25),
1953 id int(11) NOT NULL auto_increment,
1955 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1956 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1958 DROP TABLE IF EXISTS language_descriptions;
1959 CREATE TABLE language_descriptions (
1963 description varchar(255),
1964 id int(11) NOT NULL auto_increment,
1967 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1969 -- bi-directional support, keyed by script subcode
1970 DROP TABLE IF EXISTS language_script_bidi;
1971 CREATE TABLE language_script_bidi (
1972 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1973 bidi varchar(3), -- rtl ltr
1974 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1975 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1977 -- TODO: need to map language subtags to script subtags for detection
1978 -- of bidi when script is not specified (like ar, he)
1979 DROP TABLE IF EXISTS language_script_mapping;
1980 CREATE TABLE language_script_mapping (
1981 language_subtag varchar(25),
1982 script_subtag varchar(25),
1983 KEY `language_subtag` (`language_subtag`)
1984 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1986 DROP TABLE IF EXISTS `permissions`;
1987 CREATE TABLE `permissions` (
1988 `module_bit` int(11) NOT NULL DEFAULT 0,
1989 `code` varchar(64) DEFAULT NULL,
1990 `description` varchar(255) DEFAULT NULL,
1991 PRIMARY KEY (`module_bit`, `code`),
1992 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1993 ON DELETE CASCADE ON UPDATE CASCADE
1994 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1996 DROP TABLE IF EXISTS `serialitems`;
1997 CREATE TABLE `serialitems` (
1998 `itemnumber` int(11) NOT NULL,
1999 `serialid` int(11) NOT NULL,
2000 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2001 KEY `serialitems_sfk_1` (`serialid`),
2002 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2003 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2005 DROP TABLE IF EXISTS `user_permissions`;
2006 CREATE TABLE `user_permissions` (
2007 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2008 `module_bit` int(11) NOT NULL DEFAULT 0,
2009 `code` varchar(64) DEFAULT NULL,
2010 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2011 ON DELETE CASCADE ON UPDATE CASCADE,
2012 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2013 ON DELETE CASCADE ON UPDATE CASCADE
2014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2017 -- Table structure for table `tmp_holdsqueue`
2020 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2021 CREATE TABLE `tmp_holdsqueue` (
2022 `biblionumber` int(11) default NULL,
2023 `itemnumber` int(11) default NULL,
2024 `barcode` varchar(20) default NULL,
2025 `surname` mediumtext NOT NULL,
2028 `borrowernumber` int(11) NOT NULL,
2029 `cardnumber` varchar(16) default NULL,
2030 `reservedate` date default NULL,
2032 `itemcallnumber` varchar(255) default NULL,
2033 `holdingbranch` varchar(10) default NULL,
2034 `pickbranch` varchar(10) default NULL,
2036 `item_level_request` tinyint(4) NOT NULL default 0
2037 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2040 -- Table structure for table `message_queue`
2043 DROP TABLE IF EXISTS `message_queue`;
2044 CREATE TABLE `message_queue` (
2045 `message_id` int(11) NOT NULL auto_increment,
2046 `borrowernumber` int(11) default NULL,
2049 `metadata` text DEFAULT NULL,
2050 `letter_code` varchar(64) DEFAULT NULL,
2051 `message_transport_type` varchar(20) NOT NULL,
2052 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2053 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2054 `to_address` mediumtext,
2055 `from_address` mediumtext,
2056 `content_type` text,
2057 KEY `message_id` (`message_id`),
2058 KEY `borrowernumber` (`borrowernumber`),
2059 KEY `message_transport_type` (`message_transport_type`),
2060 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2061 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2062 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2065 -- Table structure for table `message_transport_types`
2068 DROP TABLE IF EXISTS `message_transport_types`;
2069 CREATE TABLE `message_transport_types` (
2070 `message_transport_type` varchar(20) NOT NULL,
2071 PRIMARY KEY (`message_transport_type`)
2072 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2075 -- Table structure for table `message_attributes`
2078 DROP TABLE IF EXISTS `message_attributes`;
2079 CREATE TABLE `message_attributes` (
2080 `message_attribute_id` int(11) NOT NULL auto_increment,
2081 `message_name` varchar(20) NOT NULL default '',
2082 `takes_days` tinyint(1) NOT NULL default '0',
2083 PRIMARY KEY (`message_attribute_id`),
2084 UNIQUE KEY `message_name` (`message_name`)
2085 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2088 -- Table structure for table `message_transports`
2091 DROP TABLE IF EXISTS `message_transports`;
2092 CREATE TABLE `message_transports` (
2093 `message_attribute_id` int(11) NOT NULL,
2094 `message_transport_type` varchar(20) NOT NULL,
2095 `is_digest` tinyint(1) NOT NULL default '0',
2096 `letter_module` varchar(20) NOT NULL default '',
2097 `letter_code` varchar(20) NOT NULL default '',
2098 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2099 KEY `message_transport_type` (`message_transport_type`),
2100 KEY `letter_module` (`letter_module`,`letter_code`),
2101 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2102 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2103 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2104 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2107 -- Table structure for table `borrower_message_preferences`
2110 DROP TABLE IF EXISTS `borrower_message_preferences`;
2111 CREATE TABLE `borrower_message_preferences` (
2112 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2113 `borrowernumber` int(11) default NULL,
2114 `categorycode` varchar(10) default NULL,
2115 `message_attribute_id` int(11) default '0',
2116 `days_in_advance` int(11) default '0',
2117 `wants_digest` tinyint(1) NOT NULL default '0',
2118 PRIMARY KEY (`borrower_message_preference_id`),
2119 KEY `borrowernumber` (`borrowernumber`),
2120 KEY `categorycode` (`categorycode`),
2121 KEY `message_attribute_id` (`message_attribute_id`),
2122 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2123 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2124 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2125 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2128 -- Table structure for table `borrower_message_transport_preferences`
2131 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2132 CREATE TABLE `borrower_message_transport_preferences` (
2133 `borrower_message_preference_id` int(11) NOT NULL default '0',
2134 `message_transport_type` varchar(20) NOT NULL default '0',
2135 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2136 KEY `message_transport_type` (`message_transport_type`),
2137 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,
2138 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
2139 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2142 -- Table structure for the table branch_transfer_limits
2145 DROP TABLE IF EXISTS `branch_transfer_limits`;
2146 CREATE TABLE branch_transfer_limits (
2147 limitId int(8) NOT NULL auto_increment,
2148 toBranch varchar(10) NOT NULL,
2149 fromBranch varchar(10) NOT NULL,
2150 itemtype varchar(10) NULL,
2151 ccode varchar(10) NULL,
2152 PRIMARY KEY (limitId)
2153 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2156 -- Table structure for table `item_circulation_alert_preferences`
2159 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2160 CREATE TABLE `item_circulation_alert_preferences` (
2161 `id` int(11) NOT NULL auto_increment,
2162 `branchcode` varchar(10) NOT NULL,
2163 `categorycode` varchar(10) NOT NULL,
2164 `item_type` varchar(10) NOT NULL,
2165 `notification` varchar(16) NOT NULL,
2167 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2171 -- Table structure for table `messages`
2174 CREATE TABLE `messages` (
2175 `message_id` int(11) NOT NULL auto_increment,
2176 `borrowernumber` int(11) NOT NULL,
2177 `branchcode` varchar(4) default NULL,
2178 `message_type` varchar(1) NOT NULL,
2179 `message` text NOT NULL,
2180 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2181 PRIMARY KEY (`message_id`)
2182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2185 -- Table structure for table `accountlines`
2188 DROP TABLE IF EXISTS `accountlines`;
2189 CREATE TABLE `accountlines` (
2190 `borrowernumber` int(11) NOT NULL default 0,
2191 `accountno` smallint(6) NOT NULL default 0,
2192 `itemnumber` int(11) default NULL,
2193 `date` date default NULL,
2194 `amount` decimal(28,6) default NULL,
2195 `description` mediumtext,
2196 `dispute` mediumtext,
2197 `accounttype` varchar(5) default NULL,
2198 `amountoutstanding` decimal(28,6) default NULL,
2199 `lastincrement` decimal(28,6) default NULL,
2200 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2201 `notify_id` int(11) NOT NULL default 0,
2202 `notify_level` int(2) NOT NULL default 0,
2203 KEY `acctsborridx` (`borrowernumber`),
2204 KEY `timeidx` (`timestamp`),
2205 KEY `itemnumber` (`itemnumber`),
2206 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2207 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2211 -- Table structure for table `accountoffsets`
2214 DROP TABLE IF EXISTS `accountoffsets`;
2215 CREATE TABLE `accountoffsets` (
2216 `borrowernumber` int(11) NOT NULL default 0,
2217 `accountno` smallint(6) NOT NULL default 0,
2218 `offsetaccount` smallint(6) NOT NULL default 0,
2219 `offsetamount` decimal(28,6) default NULL,
2220 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2221 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2225 -- Table structure for table `action_logs`
2228 DROP TABLE IF EXISTS `action_logs`;
2229 CREATE TABLE `action_logs` (
2230 `action_id` int(11) NOT NULL auto_increment,
2231 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2232 `user` int(11) NOT NULL default 0,
2235 `object` int(11) default NULL,
2237 PRIMARY KEY (`action_id`),
2238 KEY (`timestamp`,`user`)
2239 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2242 -- Table structure for table `alert`
2245 DROP TABLE IF EXISTS `alert`;
2246 CREATE TABLE `alert` (
2247 `alertid` int(11) NOT NULL auto_increment,
2248 `borrowernumber` int(11) NOT NULL default 0,
2249 `type` varchar(10) NOT NULL default '',
2250 `externalid` varchar(20) NOT NULL default '',
2251 PRIMARY KEY (`alertid`),
2252 KEY `borrowernumber` (`borrowernumber`),
2253 KEY `type` (`type`,`externalid`)
2254 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2257 -- Table structure for table `aqbasketgroups`
2260 DROP TABLE IF EXISTS `aqbasketgroups`;
2261 CREATE TABLE `aqbasketgroups` (
2262 `id` int(11) NOT NULL auto_increment,
2263 `name` varchar(50) default NULL,
2264 `closed` tinyint(1) default NULL,
2265 `booksellerid` int(11) NOT NULL,
2267 KEY `booksellerid` (`booksellerid`),
2268 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2272 -- Table structure for table `aqbasket`
2275 DROP TABLE IF EXISTS `aqbasket`;
2276 CREATE TABLE `aqbasket` (
2277 `basketno` int(11) NOT NULL auto_increment,
2278 `basketname` varchar(50) default NULL,
2280 `booksellernote` mediumtext,
2281 `contractnumber` int(11),
2282 `creationdate` date default NULL,
2283 `closedate` date default NULL,
2284 `booksellerid` int(11) NOT NULL default 1,
2285 `authorisedby` varchar(10) default NULL,
2286 `booksellerinvoicenumber` mediumtext,
2287 `basketgroupid` int(11),
2288 PRIMARY KEY (`basketno`),
2289 KEY `booksellerid` (`booksellerid`),
2290 KEY `basketgroupid` (`basketgroupid`),
2291 KEY `contractnumber` (`contractnumber`),
2292 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2293 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2294 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2295 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2298 -- Table structure for table `aqbooksellers`
2301 DROP TABLE IF EXISTS `aqbooksellers`;
2302 CREATE TABLE `aqbooksellers` (
2303 `id` int(11) NOT NULL auto_increment,
2304 `name` mediumtext NOT NULL,
2305 `address1` mediumtext,
2306 `address2` mediumtext,
2307 `address3` mediumtext,
2308 `address4` mediumtext,
2309 `phone` varchar(30) default NULL,
2310 `accountnumber` mediumtext,
2311 `othersupplier` mediumtext,
2312 `currency` varchar(3) NOT NULL default '',
2313 `booksellerfax` mediumtext,
2315 `bookselleremail` mediumtext,
2316 `booksellerurl` mediumtext,
2317 `contact` varchar(100) default NULL,
2318 `postal` mediumtext,
2319 `url` varchar(255) default NULL,
2320 `contpos` varchar(100) default NULL,
2321 `contphone` varchar(100) default NULL,
2322 `contfax` varchar(100) default NULL,
2323 `contaltphone` varchar(100) default NULL,
2324 `contemail` varchar(100) default NULL,
2325 `contnotes` mediumtext,
2326 `active` tinyint(4) default NULL,
2327 `listprice` varchar(10) default NULL,
2328 `invoiceprice` varchar(10) default NULL,
2329 `gstreg` tinyint(4) default NULL,
2330 `listincgst` tinyint(4) default NULL,
2331 `invoiceincgst` tinyint(4) default NULL,
2332 `gstrate` decimal(6,4) default NULL,
2333 `discount` float(6,4) default NULL,
2334 `fax` varchar(50) default NULL,
2336 KEY `listprice` (`listprice`),
2337 KEY `invoiceprice` (`invoiceprice`),
2338 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2339 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2340 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2343 -- Table structure for table `aqbudgets`
2346 DROP TABLE IF EXISTS `aqbudgets`;
2347 CREATE TABLE `aqbudgets` (
2348 `budget_id` int(11) NOT NULL auto_increment,
2349 `budget_parent_id` int(11) default NULL,
2350 `budget_code` varchar(30) default NULL,
2351 `budget_name` varchar(80) default NULL,
2352 `budget_branchcode` varchar(10) default NULL,
2353 `budget_amount` decimal(28,6) NULL default '0.00',
2354 `budget_encumb` decimal(28,6) NULL default '0.00',
2355 `budget_expend` decimal(28,6) NULL default '0.00',
2356 `budget_notes` mediumtext,
2357 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2358 `budget_period_id` int(11) default NULL,
2359 `sort1_authcat` varchar(80) default NULL,
2360 `sort2_authcat` varchar(80) default NULL,
2361 `budget_owner_id` int(11) default NULL,
2362 `budget_permission` int(1) default '0',
2363 PRIMARY KEY (`budget_id`)
2364 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2368 -- Table structure for table `aqbudgetperiods`
2372 DROP TABLE IF EXISTS `aqbudgetperiods`;
2373 CREATE TABLE `aqbudgetperiods` (
2374 `budget_period_id` int(11) NOT NULL auto_increment,
2375 `budget_period_startdate` date NOT NULL,
2376 `budget_period_enddate` date NOT NULL,
2377 `budget_period_active` tinyint(1) default '0',
2378 `budget_period_description` mediumtext,
2379 `budget_period_total` decimal(28,6),
2380 `budget_period_locked` tinyint(1) default NULL,
2381 `sort1_authcat` varchar(10) default NULL,
2382 `sort2_authcat` varchar(10) default NULL,
2383 PRIMARY KEY (`budget_period_id`)
2384 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2387 -- Table structure for table `aqbudgets_planning`
2390 DROP TABLE IF EXISTS `aqbudgets_planning`;
2391 CREATE TABLE `aqbudgets_planning` (
2392 `plan_id` int(11) NOT NULL auto_increment,
2393 `budget_id` int(11) NOT NULL,
2394 `budget_period_id` int(11) NOT NULL,
2395 `estimated_amount` decimal(28,6) default NULL,
2396 `authcat` varchar(30) NOT NULL,
2397 `authvalue` varchar(30) NOT NULL,
2398 PRIMARY KEY (`plan_id`),
2399 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2400 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2403 -- Table structure for table 'aqcontract'
2406 DROP TABLE IF EXISTS `aqcontract`;
2407 CREATE TABLE `aqcontract` (
2408 `contractnumber` int(11) NOT NULL auto_increment,
2409 `contractstartdate` date default NULL,
2410 `contractenddate` date default NULL,
2411 `contractname` varchar(50) default NULL,
2412 `contractdescription` mediumtext,
2413 `booksellerid` int(11) not NULL,
2414 PRIMARY KEY (`contractnumber`),
2415 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2416 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2417 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2420 -- Table structure for table `aqorderdelivery`
2423 DROP TABLE IF EXISTS `aqorderdelivery`;
2424 CREATE TABLE `aqorderdelivery` (
2425 `ordernumber` date default NULL,
2426 `deliverynumber` smallint(6) NOT NULL default 0,
2427 `deliverydate` varchar(18) default NULL,
2428 `qtydelivered` smallint(6) default NULL,
2429 `deliverycomments` mediumtext
2430 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2433 -- Table structure for table `aqorders`
2436 DROP TABLE IF EXISTS `aqorders`;
2437 CREATE TABLE `aqorders` (
2438 `ordernumber` int(11) NOT NULL auto_increment,
2439 `biblionumber` int(11) default NULL,
2440 `entrydate` date default NULL,
2441 `quantity` smallint(6) default NULL,
2442 `currency` varchar(3) default NULL,
2443 `listprice` decimal(28,6) default NULL,
2444 `totalamount` decimal(28,6) default NULL,
2445 `datereceived` date default NULL,
2446 `booksellerinvoicenumber` mediumtext,
2447 `freight` decimal(28,6) default NULL,
2448 `unitprice` decimal(28,6) default NULL,
2449 `quantityreceived` smallint(6) default NULL,
2450 `cancelledby` varchar(10) default NULL,
2451 `datecancellationprinted` date default NULL,
2453 `supplierreference` mediumtext,
2454 `purchaseordernumber` mediumtext,
2455 `subscription` tinyint(1) default NULL,
2456 `serialid` varchar(30) default NULL,
2457 `basketno` int(11) default NULL,
2458 `biblioitemnumber` int(11) default NULL,
2459 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2460 `rrp` decimal(13,2) default NULL,
2461 `ecost` decimal(13,2) default NULL,
2462 `gst` decimal(13,2) default NULL,
2463 `budget_id` int(11) NOT NULL,
2464 `budgetgroup_id` int(11) NOT NULL,
2465 `budgetdate` date default NULL,
2466 `sort1` varchar(80) default NULL,
2467 `sort2` varchar(80) default NULL,
2468 `sort1_authcat` varchar(10) default NULL,
2469 `sort2_authcat` varchar(10) default NULL,
2470 `uncertainprice` tinyint(1),
2471 PRIMARY KEY (`ordernumber`),
2472 KEY `basketno` (`basketno`),
2473 KEY `biblionumber` (`biblionumber`),
2474 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2475 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2476 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2479 -- Table structure for table `aqorders_items`
2482 DROP TABLE IF EXISTS `aqorders_items`;
2483 CREATE TABLE `aqorders_items` (
2484 `ordernumber` int(11) NOT NULL,
2485 `itemnumber` int(11) NOT NULL,
2486 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2487 PRIMARY KEY (`itemnumber`),
2488 KEY `ordernumber` (`ordernumber`)
2489 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2492 -- Table structure for table `fieldmapping`
2495 DROP TABLE IF EXISTS `fieldmapping`;
2496 CREATE TABLE `fieldmapping` (
2497 `id` int(11) NOT NULL auto_increment,
2498 `field` varchar(255) NOT NULL,
2499 `frameworkcode` char(4) NOT NULL default '',
2500 `fieldcode` char(3) NOT NULL,
2501 `subfieldcode` char(1) NOT NULL,
2503 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2506 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2507 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2508 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2509 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2510 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2511 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2512 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2513 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;