3 -- Host: localhost Database: koha30test
4 -- ------------------------------------------------------
5 -- Server version 4.1.22
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
11 /*!40101 SET NAMES utf8 */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19 -- Table structure for table `auth_header`
22 DROP TABLE IF EXISTS `auth_header`;
23 CREATE TABLE `auth_header` (
24 `authid` bigint(20) unsigned NOT NULL auto_increment,
25 `authtypecode` varchar(10) NOT NULL default '',
26 `datecreated` date default NULL,
27 `datemodified` date default NULL,
28 `origincode` varchar(20) default NULL,
29 `authtrees` mediumtext,
31 `linkid` bigint(20) default NULL,
32 `marcxml` longtext NOT NULL,
33 PRIMARY KEY (`authid`),
34 KEY `origincode` (`origincode`)
35 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
38 -- Table structure for table `auth_subfield_structure`
41 DROP TABLE IF EXISTS `auth_subfield_structure`;
42 CREATE TABLE `auth_subfield_structure` (
43 `authtypecode` varchar(10) NOT NULL default '',
44 `tagfield` varchar(3) NOT NULL default '',
45 `tagsubfield` varchar(1) NOT NULL default '',
46 `liblibrarian` varchar(255) NOT NULL default '',
47 `libopac` varchar(255) NOT NULL default '',
48 `repeatable` tinyint(4) NOT NULL default 0,
49 `mandatory` tinyint(4) NOT NULL default 0,
50 `tab` tinyint(1) default NULL,
51 `authorised_value` varchar(10) default NULL,
52 `value_builder` varchar(80) default NULL,
53 `seealso` varchar(255) default NULL,
54 `isurl` tinyint(1) default NULL,
55 `hidden` tinyint(3) NOT NULL default 0,
56 `linkid` tinyint(1) NOT NULL default 0,
57 `kohafield` varchar(45) NULL default '',
58 `frameworkcode` varchar(10) NOT NULL default '',
59 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
60 KEY `tab` (`authtypecode`,`tab`)
61 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
64 -- Table structure for table `auth_tag_structure`
67 DROP TABLE IF EXISTS `auth_tag_structure`;
68 CREATE TABLE `auth_tag_structure` (
69 `authtypecode` varchar(10) NOT NULL default '',
70 `tagfield` varchar(3) NOT NULL default '',
71 `liblibrarian` varchar(255) NOT NULL default '',
72 `libopac` varchar(255) NOT NULL default '',
73 `repeatable` tinyint(4) NOT NULL default 0,
74 `mandatory` tinyint(4) NOT NULL default 0,
75 `authorised_value` varchar(10) default NULL,
76 PRIMARY KEY (`authtypecode`,`tagfield`),
77 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
78 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
81 -- Table structure for table `auth_types`
84 DROP TABLE IF EXISTS `auth_types`;
85 CREATE TABLE `auth_types` (
86 `authtypecode` varchar(10) NOT NULL default '',
87 `authtypetext` varchar(255) NOT NULL default '',
88 `auth_tag_to_report` varchar(3) NOT NULL default '',
89 `summary` mediumtext NOT NULL,
90 PRIMARY KEY (`authtypecode`)
91 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
94 -- Table structure for table `authorised_values`
97 DROP TABLE IF EXISTS `authorised_values`;
98 CREATE TABLE `authorised_values` (
99 `id` int(11) NOT NULL auto_increment,
100 `category` varchar(10) NOT NULL default '',
101 `authorised_value` varchar(80) NOT NULL default '',
102 `lib` varchar(80) default NULL,
103 `lib_opac` VARCHAR(80) default NULL,
104 `imageurl` varchar(200) default NULL,
106 KEY `name` (`category`),
108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
111 -- Table structure for table `biblio`
114 DROP TABLE IF EXISTS `biblio`;
115 CREATE TABLE `biblio` (
116 `biblionumber` int(11) NOT NULL auto_increment,
117 `frameworkcode` varchar(4) NOT NULL default '',
120 `unititle` mediumtext,
122 `serial` tinyint(1) default NULL,
123 `seriestitle` mediumtext,
124 `copyrightdate` smallint(6) default NULL,
125 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
126 `datecreated` DATE NOT NULL,
127 `abstract` mediumtext,
128 PRIMARY KEY (`biblionumber`),
129 KEY `blbnoidx` (`biblionumber`)
130 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
133 -- Table structure for table `biblio_framework`
136 DROP TABLE IF EXISTS `biblio_framework`;
137 CREATE TABLE `biblio_framework` (
138 `frameworkcode` varchar(4) NOT NULL default '',
139 `frameworktext` varchar(255) NOT NULL default '',
140 PRIMARY KEY (`frameworkcode`)
141 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
144 -- Table structure for table `biblioitems`
147 DROP TABLE IF EXISTS `biblioitems`;
148 CREATE TABLE `biblioitems` (
149 `biblioitemnumber` int(11) NOT NULL auto_increment,
150 `biblionumber` int(11) NOT NULL default 0,
153 `itemtype` varchar(10) default NULL,
154 `isbn` varchar(30) default NULL,
155 `issn` varchar(9) default NULL,
156 `publicationyear` text,
157 `publishercode` varchar(255) default NULL,
158 `volumedate` date default NULL,
160 `collectiontitle` mediumtext default NULL,
161 `collectionissn` text default NULL,
162 `collectionvolume` mediumtext default NULL,
163 `editionstatement` text default NULL,
164 `editionresponsibility` text default NULL,
165 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
166 `illus` varchar(255) default NULL,
167 `pages` varchar(255) default NULL,
169 `size` varchar(255) default NULL,
170 `place` varchar(255) default NULL,
171 `lccn` varchar(25) default NULL,
173 `url` varchar(255) default NULL,
174 `cn_source` varchar(10) default NULL,
175 `cn_class` varchar(30) default NULL,
176 `cn_item` varchar(10) default NULL,
177 `cn_suffix` varchar(10) default NULL,
178 `cn_sort` varchar(30) default NULL,
179 `totalissues` int(10),
180 `marcxml` longtext NOT NULL,
181 PRIMARY KEY (`biblioitemnumber`),
182 KEY `bibinoidx` (`biblioitemnumber`),
183 KEY `bibnoidx` (`biblionumber`),
185 KEY `publishercode` (`publishercode`),
186 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
190 -- Table structure for table `borrowers`
193 DROP TABLE IF EXISTS `borrowers`;
194 CREATE TABLE `borrowers` (
195 `borrowernumber` int(11) NOT NULL auto_increment,
196 `cardnumber` varchar(16) default NULL,
197 `surname` mediumtext NOT NULL,
200 `othernames` mediumtext,
202 `streetnumber` varchar(10) default NULL,
203 `streettype` varchar(50) default NULL,
204 `address` mediumtext NOT NULL,
206 `city` mediumtext NOT NULL,
207 `zipcode` varchar(25) default NULL,
211 `mobile` varchar(50) default NULL,
215 `B_streetnumber` varchar(10) default NULL,
216 `B_streettype` varchar(50) default NULL,
217 `B_address` varchar(100) default NULL,
218 `B_address2` text default NULL,
220 `B_zipcode` varchar(25) default NULL,
223 `B_phone` mediumtext,
224 `dateofbirth` date default NULL,
225 `branchcode` varchar(10) NOT NULL default '',
226 `categorycode` varchar(10) NOT NULL default '',
227 `dateenrolled` date default NULL,
228 `dateexpiry` date default NULL,
229 `gonenoaddress` tinyint(1) default NULL,
230 `lost` tinyint(1) default NULL,
231 `debarred` tinyint(1) default NULL,
232 `contactname` mediumtext,
233 `contactfirstname` text,
235 `guarantorid` int(11) default NULL,
236 `borrowernotes` mediumtext,
237 `relationship` varchar(100) default NULL,
238 `ethnicity` varchar(50) default NULL,
239 `ethnotes` varchar(255) default NULL,
240 `sex` varchar(1) default NULL,
241 `password` varchar(30) default NULL,
242 `flags` int(11) default NULL,
243 `userid` varchar(30) default NULL,
244 `opacnote` mediumtext,
245 `contactnote` varchar(255) default NULL,
246 `sort1` varchar(80) default NULL,
247 `sort2` varchar(80) default NULL,
248 `altcontactfirstname` varchar(255) default NULL,
249 `altcontactsurname` varchar(255) default NULL,
250 `altcontactaddress1` varchar(255) default NULL,
251 `altcontactaddress2` varchar(255) default NULL,
252 `altcontactaddress3` varchar(255) default NULL,
253 `altcontactzipcode` varchar(50) default NULL,
254 `altcontactcountry` text default NULL,
255 `altcontactphone` varchar(50) default NULL,
256 `smsalertnumber` varchar(50) default NULL,
257 UNIQUE KEY `cardnumber` (`cardnumber`),
258 PRIMARY KEY `borrowernumber` (`borrowernumber`),
259 KEY `categorycode` (`categorycode`),
260 KEY `branchcode` (`branchcode`),
261 KEY `userid` (`userid`),
262 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
263 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
264 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
267 -- Table structure for table `borrower_attribute_types`
270 DROP TABLE IF EXISTS `borrower_attribute_types`;
271 CREATE TABLE `borrower_attribute_types` (
272 `code` varchar(10) NOT NULL,
273 `description` varchar(255) NOT NULL,
274 `repeatable` tinyint(1) NOT NULL default 0,
275 `unique_id` tinyint(1) NOT NULL default 0,
276 `opac_display` tinyint(1) NOT NULL default 0,
277 `password_allowed` tinyint(1) NOT NULL default 0,
278 `staff_searchable` tinyint(1) NOT NULL default 0,
279 `authorised_value_category` varchar(10) default NULL,
281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
284 -- Table structure for table `borrower_attributes`
287 DROP TABLE IF EXISTS `borrower_attributes`;
288 CREATE TABLE `borrower_attributes` (
289 `borrowernumber` int(11) NOT NULL,
290 `code` varchar(10) NOT NULL,
291 `attribute` varchar(64) default NULL,
292 `password` varchar(64) default NULL,
293 KEY `borrowernumber` (`borrowernumber`),
294 KEY `code_attribute` (`code`, `attribute`),
295 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
296 ON DELETE CASCADE ON UPDATE CASCADE,
297 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
298 ON DELETE CASCADE ON UPDATE CASCADE
299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
301 CREATE TABLE `branch_item_rules` (
302 `branchcode` varchar(10) NOT NULL,
303 `itemtype` varchar(10) NOT NULL,
304 `holdallowed` tinyint(1) default NULL,
305 PRIMARY KEY (`itemtype`,`branchcode`),
306 KEY `branch_item_rules_ibfk_2` (`branchcode`),
307 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
308 ON DELETE CASCADE ON UPDATE CASCADE,
309 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
310 ON DELETE CASCADE ON UPDATE CASCADE
311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
314 -- Table structure for table `branchcategories`
317 DROP TABLE IF EXISTS `branchcategories`;
318 CREATE TABLE `branchcategories` (
319 `categorycode` varchar(10) NOT NULL default '',
320 `categoryname` varchar(32),
321 `codedescription` mediumtext,
322 `categorytype` varchar(16),
323 PRIMARY KEY (`categorycode`)
324 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
327 -- Table structure for table `branches`
330 DROP TABLE IF EXISTS `branches`;
331 CREATE TABLE `branches` (
332 `branchcode` varchar(10) NOT NULL default '',
333 `branchname` mediumtext NOT NULL,
334 `branchaddress1` mediumtext,
335 `branchaddress2` mediumtext,
336 `branchaddress3` mediumtext,
337 `branchzip` varchar(25) default NULL,
338 `branchcity` mediumtext,
339 `branchcountry` text,
340 `branchphone` mediumtext,
341 `branchfax` mediumtext,
342 `branchemail` mediumtext,
343 `branchurl` mediumtext,
344 `issuing` tinyint(4) default NULL,
345 `branchip` varchar(15) default NULL,
346 `branchprinter` varchar(100) default NULL,
347 `branchnotes` mediumtext,
348 UNIQUE KEY `branchcode` (`branchcode`)
349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
352 -- Table structure for table `branchrelations`
355 DROP TABLE IF EXISTS `branchrelations`;
356 CREATE TABLE `branchrelations` (
357 `branchcode` varchar(10) NOT NULL default '',
358 `categorycode` varchar(10) NOT NULL default '',
359 PRIMARY KEY (`branchcode`,`categorycode`),
360 KEY `branchcode` (`branchcode`),
361 KEY `categorycode` (`categorycode`),
362 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
363 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
364 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
367 -- Table structure for table `branchtransfers`
370 DROP TABLE IF EXISTS `branchtransfers`;
371 CREATE TABLE `branchtransfers` (
372 `itemnumber` int(11) NOT NULL default 0,
373 `datesent` datetime default NULL,
374 `frombranch` varchar(10) NOT NULL default '',
375 `datearrived` datetime default NULL,
376 `tobranch` varchar(10) NOT NULL default '',
377 `comments` mediumtext,
378 KEY `frombranch` (`frombranch`),
379 KEY `tobranch` (`tobranch`),
380 KEY `itemnumber` (`itemnumber`),
381 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
382 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
383 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
384 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
388 -- Table structure for table `browser`
390 DROP TABLE IF EXISTS `browser`;
391 CREATE TABLE `browser` (
392 `level` int(11) NOT NULL,
393 `classification` varchar(20) NOT NULL,
394 `description` varchar(255) NOT NULL,
395 `number` bigint(20) NOT NULL,
396 `endnode` tinyint(4) NOT NULL
397 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
400 -- Table structure for table `categories`
403 DROP TABLE IF EXISTS `categories`;
404 CREATE TABLE `categories` (
405 `categorycode` varchar(10) NOT NULL default '',
406 `description` mediumtext,
407 `enrolmentperiod` smallint(6) default NULL,
408 `upperagelimit` smallint(6) default NULL,
409 `dateofbirthrequired` tinyint(1) default NULL,
410 `finetype` varchar(30) default NULL,
411 `bulk` tinyint(1) default NULL,
412 `enrolmentfee` decimal(28,6) default NULL,
413 `overduenoticerequired` tinyint(1) default NULL,
414 `issuelimit` smallint(6) default NULL,
415 `reservefee` decimal(28,6) default NULL,
416 `category_type` varchar(1) NOT NULL default 'A',
417 PRIMARY KEY (`categorycode`),
418 UNIQUE KEY `categorycode` (`categorycode`)
419 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
422 -- Table structure for table `borrower_branch_circ_rules`
425 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
426 CREATE TABLE `branch_borrower_circ_rules` (
427 `branchcode` VARCHAR(10) NOT NULL,
428 `categorycode` VARCHAR(10) NOT NULL,
429 `maxissueqty` int(4) default NULL,
430 PRIMARY KEY (`categorycode`, `branchcode`),
431 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
432 ON DELETE CASCADE ON UPDATE CASCADE,
433 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
434 ON DELETE CASCADE ON UPDATE CASCADE
435 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
438 -- Table structure for table `default_borrower_circ_rules`
441 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
442 CREATE TABLE `default_borrower_circ_rules` (
443 `categorycode` VARCHAR(10) NOT NULL,
444 `maxissueqty` int(4) default NULL,
445 PRIMARY KEY (`categorycode`),
446 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
447 ON DELETE CASCADE ON UPDATE CASCADE
448 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
451 -- Table structure for table `default_branch_circ_rules`
454 DROP TABLE IF EXISTS `default_branch_circ_rules`;
455 CREATE TABLE `default_branch_circ_rules` (
456 `branchcode` VARCHAR(10) NOT NULL,
457 `maxissueqty` int(4) default NULL,
458 `holdallowed` tinyint(1) default NULL,
459 PRIMARY KEY (`branchcode`),
460 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
461 ON DELETE CASCADE ON UPDATE CASCADE
462 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
465 -- Table structure for table `default_branch_item_rules`
468 CREATE TABLE `default_branch_item_rules` (
469 `itemtype` varchar(10) NOT NULL,
470 `holdallowed` tinyint(1) default NULL,
471 PRIMARY KEY (`itemtype`),
472 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
473 ON DELETE CASCADE ON UPDATE CASCADE
474 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
477 -- Table structure for table `default_circ_rules`
480 DROP TABLE IF EXISTS `default_circ_rules`;
481 CREATE TABLE `default_circ_rules` (
482 `singleton` enum('singleton') NOT NULL default 'singleton',
483 `maxissueqty` int(4) default NULL,
484 `holdallowed` int(1) default NULL,
485 PRIMARY KEY (`singleton`)
486 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
489 -- Table structure for table `cities`
492 DROP TABLE IF EXISTS `cities`;
493 CREATE TABLE `cities` (
494 `cityid` int(11) NOT NULL auto_increment,
495 `city_name` varchar(100) NOT NULL default '',
496 `city_zipcode` varchar(20) default NULL,
497 PRIMARY KEY (`cityid`)
498 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
501 -- Table structure for table `class_sort_rules`
504 DROP TABLE IF EXISTS `class_sort_rules`;
505 CREATE TABLE `class_sort_rules` (
506 `class_sort_rule` varchar(10) NOT NULL default '',
507 `description` mediumtext,
508 `sort_routine` varchar(30) NOT NULL default '',
509 PRIMARY KEY (`class_sort_rule`),
510 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
511 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
514 -- Table structure for table `class_sources`
517 DROP TABLE IF EXISTS `class_sources`;
518 CREATE TABLE `class_sources` (
519 `cn_source` varchar(10) NOT NULL default '',
520 `description` mediumtext,
521 `used` tinyint(4) NOT NULL default 0,
522 `class_sort_rule` varchar(10) NOT NULL default '',
523 PRIMARY KEY (`cn_source`),
524 UNIQUE KEY `cn_source_idx` (`cn_source`),
525 KEY `used_idx` (`used`),
526 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
530 -- Table structure for table `currency`
533 DROP TABLE IF EXISTS `currency`;
534 CREATE TABLE `currency` (
535 `currency` varchar(10) NOT NULL default '',
536 `symbol` varchar(5) default NULL,
537 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
538 `rate` float(7,5) default NULL,
539 `active` tinyint(1) default NULL,
540 PRIMARY KEY (`currency`)
541 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
544 -- Table structure for table `deletedbiblio`
547 DROP TABLE IF EXISTS `deletedbiblio`;
548 CREATE TABLE `deletedbiblio` (
549 `biblionumber` int(11) NOT NULL default 0,
550 `frameworkcode` varchar(4) NOT NULL default '',
553 `unititle` mediumtext,
555 `serial` tinyint(1) default NULL,
556 `seriestitle` mediumtext,
557 `copyrightdate` smallint(6) default NULL,
558 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
559 `datecreated` DATE NOT NULL,
560 `abstract` mediumtext,
561 PRIMARY KEY (`biblionumber`),
562 KEY `blbnoidx` (`biblionumber`)
563 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
566 -- Table structure for table `deletedbiblioitems`
569 DROP TABLE IF EXISTS `deletedbiblioitems`;
570 CREATE TABLE `deletedbiblioitems` (
571 `biblioitemnumber` int(11) NOT NULL default 0,
572 `biblionumber` int(11) NOT NULL default 0,
575 `itemtype` varchar(10) default NULL,
576 `isbn` varchar(30) default NULL,
577 `issn` varchar(9) default NULL,
578 `publicationyear` text,
579 `publishercode` varchar(255) default NULL,
580 `volumedate` date default NULL,
582 `collectiontitle` mediumtext default NULL,
583 `collectionissn` text default NULL,
584 `collectionvolume` mediumtext default NULL,
585 `editionstatement` text default NULL,
586 `editionresponsibility` text default NULL,
587 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
588 `illus` varchar(255) default NULL,
589 `pages` varchar(255) default NULL,
591 `size` varchar(255) default NULL,
592 `place` varchar(255) default NULL,
593 `lccn` varchar(25) default NULL,
595 `url` varchar(255) default NULL,
596 `cn_source` varchar(10) default NULL,
597 `cn_class` varchar(30) default NULL,
598 `cn_item` varchar(10) default NULL,
599 `cn_suffix` varchar(10) default NULL,
600 `cn_sort` varchar(30) default NULL,
601 `totalissues` int(10),
602 `marcxml` longtext NOT NULL,
603 PRIMARY KEY (`biblioitemnumber`),
604 KEY `bibinoidx` (`biblioitemnumber`),
605 KEY `bibnoidx` (`biblionumber`),
607 KEY `publishercode` (`publishercode`)
608 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
611 -- Table structure for table `deletedborrowers`
614 DROP TABLE IF EXISTS `deletedborrowers`;
615 CREATE TABLE `deletedborrowers` (
616 `borrowernumber` int(11) NOT NULL default 0,
617 `cardnumber` varchar(9) NOT NULL default '',
618 `surname` mediumtext NOT NULL,
621 `othernames` mediumtext,
623 `streetnumber` varchar(10) default NULL,
624 `streettype` varchar(50) default NULL,
625 `address` mediumtext NOT NULL,
627 `city` mediumtext NOT NULL,
628 `zipcode` varchar(25) default NULL,
632 `mobile` varchar(50) default NULL,
636 `B_streetnumber` varchar(10) default NULL,
637 `B_streettype` varchar(50) default NULL,
638 `B_address` varchar(100) default NULL,
639 `B_address2` text default NULL,
641 `B_zipcode` varchar(25) default NULL,
644 `B_phone` mediumtext,
645 `dateofbirth` date default NULL,
646 `branchcode` varchar(10) NOT NULL default '',
647 `categorycode` varchar(10) default NULL,
648 `dateenrolled` date default NULL,
649 `dateexpiry` date default NULL,
650 `gonenoaddress` tinyint(1) default NULL,
651 `lost` tinyint(1) default NULL,
652 `debarred` tinyint(1) default NULL,
653 `contactname` mediumtext,
654 `contactfirstname` text,
656 `guarantorid` int(11) default NULL,
657 `borrowernotes` mediumtext,
658 `relationship` varchar(100) default NULL,
659 `ethnicity` varchar(50) default NULL,
660 `ethnotes` varchar(255) default NULL,
661 `sex` varchar(1) default NULL,
662 `password` varchar(30) default NULL,
663 `flags` int(11) default NULL,
664 `userid` varchar(30) default NULL,
665 `opacnote` mediumtext,
666 `contactnote` varchar(255) default NULL,
667 `sort1` varchar(80) default NULL,
668 `sort2` varchar(80) default NULL,
669 `altcontactfirstname` varchar(255) default NULL,
670 `altcontactsurname` varchar(255) default NULL,
671 `altcontactaddress1` varchar(255) default NULL,
672 `altcontactaddress2` varchar(255) default NULL,
673 `altcontactaddress3` varchar(255) default NULL,
674 `altcontactzipcode` varchar(50) default NULL,
675 `altcontactcountry` text default NULL,
676 `altcontactphone` varchar(50) default NULL,
677 `smsalertnumber` varchar(50) default NULL,
678 KEY `borrowernumber` (`borrowernumber`),
679 KEY `cardnumber` (`cardnumber`)
680 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
683 -- Table structure for table `deleteditems`
686 DROP TABLE IF EXISTS `deleteditems`;
687 CREATE TABLE `deleteditems` (
688 `itemnumber` int(11) NOT NULL default 0,
689 `biblionumber` int(11) NOT NULL default 0,
690 `biblioitemnumber` int(11) NOT NULL default 0,
691 `barcode` varchar(20) default NULL,
692 `dateaccessioned` date default NULL,
693 `booksellerid` mediumtext default NULL,
694 `homebranch` varchar(10) default NULL,
695 `price` decimal(8,2) default NULL,
696 `replacementprice` decimal(8,2) default NULL,
697 `replacementpricedate` date default NULL,
698 `datelastborrowed` date default NULL,
699 `datelastseen` date default NULL,
700 `stack` tinyint(1) default NULL,
701 `notforloan` tinyint(1) NOT NULL default 0,
702 `damaged` tinyint(1) NOT NULL default 0,
703 `itemlost` tinyint(1) NOT NULL default 0,
704 `wthdrawn` tinyint(1) NOT NULL default 0,
705 `itemcallnumber` varchar(255) default NULL,
706 `issues` smallint(6) default NULL,
707 `renewals` smallint(6) default NULL,
708 `reserves` smallint(6) default NULL,
709 `restricted` tinyint(1) default NULL,
710 `itemnotes` mediumtext,
711 `holdingbranch` varchar(10) default NULL,
712 `paidfor` mediumtext,
713 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
714 `location` varchar(80) default NULL,
715 `permanent_location` varchar(80) default NULL,
716 `onloan` date default NULL,
717 `cn_source` varchar(10) default NULL,
718 `cn_sort` varchar(30) default NULL,
719 `ccode` varchar(10) default NULL,
720 `materials` varchar(10) default NULL,
721 `uri` varchar(255) default NULL,
722 `itype` varchar(10) default NULL,
723 `more_subfields_xml` longtext default NULL,
724 `enumchron` varchar(80) default NULL,
725 `copynumber` varchar(32) default NULL,
726 `stocknumber` varchar(32) default NULL,
728 PRIMARY KEY (`itemnumber`),
729 KEY `delitembarcodeidx` (`barcode`),
730 KEY `delitemstocknumberidx` (`stocknumber`),
731 KEY `delitembinoidx` (`biblioitemnumber`),
732 KEY `delitembibnoidx` (`biblionumber`),
733 KEY `delhomebranch` (`homebranch`),
734 KEY `delholdingbranch` (`holdingbranch`)
735 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
738 -- Table structure for table `ethnicity`
741 DROP TABLE IF EXISTS `ethnicity`;
742 CREATE TABLE `ethnicity` (
743 `code` varchar(10) NOT NULL default '',
744 `name` varchar(255) default NULL,
746 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
749 -- Table structure for table `export_format`
752 DROP TABLE IF EXISTS `export_format`;
753 CREATE TABLE `export_format` (
754 `export_format_id` int(11) NOT NULL auto_increment,
755 `profile` varchar(255) NOT NULL,
756 `description` mediumtext NOT NULL,
757 `marcfields` mediumtext NOT NULL,
758 PRIMARY KEY (`export_format_id`)
759 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
763 -- Table structure for table `hold_fill_targets`
766 DROP TABLE IF EXISTS `hold_fill_targets`;
767 CREATE TABLE hold_fill_targets (
768 `borrowernumber` int(11) NOT NULL,
769 `biblionumber` int(11) NOT NULL,
770 `itemnumber` int(11) NOT NULL,
771 `source_branchcode` varchar(10) default NULL,
772 `item_level_request` tinyint(4) NOT NULL default 0,
773 PRIMARY KEY `itemnumber` (`itemnumber`),
774 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
775 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
776 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
777 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
778 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
779 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
780 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
781 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
782 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
783 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
786 -- Table structure for table `import_batches`
789 DROP TABLE IF EXISTS `import_batches`;
790 CREATE TABLE `import_batches` (
791 `import_batch_id` int(11) NOT NULL auto_increment,
792 `matcher_id` int(11) default NULL,
793 `template_id` int(11) default NULL,
794 `branchcode` varchar(10) default NULL,
795 `num_biblios` int(11) NOT NULL default 0,
796 `num_items` int(11) NOT NULL default 0,
797 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
798 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
799 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
800 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
801 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
802 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
803 `file_name` varchar(100),
804 `comments` mediumtext,
805 PRIMARY KEY (`import_batch_id`),
806 KEY `branchcode` (`branchcode`)
807 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
810 -- Table structure for table `import_records`
813 DROP TABLE IF EXISTS `import_records`;
814 CREATE TABLE `import_records` (
815 `import_record_id` int(11) NOT NULL auto_increment,
816 `import_batch_id` int(11) NOT NULL,
817 `branchcode` varchar(10) default NULL,
818 `record_sequence` int(11) NOT NULL default 0,
819 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
820 `import_date` DATE default NULL,
821 `marc` longblob NOT NULL,
822 `marcxml` longtext NOT NULL,
823 `marcxml_old` longtext NOT NULL,
824 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
825 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
826 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
827 `import_error` mediumtext,
828 `encoding` varchar(40) NOT NULL default '',
829 `z3950random` varchar(40) default NULL,
830 PRIMARY KEY (`import_record_id`),
831 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
832 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
833 KEY `branchcode` (`branchcode`),
834 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
835 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
838 -- Table structure for `import_record_matches`
840 DROP TABLE IF EXISTS `import_record_matches`;
841 CREATE TABLE `import_record_matches` (
842 `import_record_id` int(11) NOT NULL,
843 `candidate_match_id` int(11) NOT NULL,
844 `score` int(11) NOT NULL default 0,
845 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
846 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
847 KEY `record_score` (`import_record_id`, `score`)
848 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
851 -- Table structure for table `import_biblios`
854 DROP TABLE IF EXISTS `import_biblios`;
855 CREATE TABLE `import_biblios` (
856 `import_record_id` int(11) NOT NULL,
857 `matched_biblionumber` int(11) default NULL,
858 `control_number` varchar(25) default NULL,
859 `original_source` varchar(25) default NULL,
860 `title` varchar(128) default NULL,
861 `author` varchar(80) default NULL,
862 `isbn` varchar(30) default NULL,
863 `issn` varchar(9) default NULL,
864 `has_items` tinyint(1) NOT NULL default 0,
865 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
866 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
867 KEY `matched_biblionumber` (`matched_biblionumber`),
868 KEY `title` (`title`),
870 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
873 -- Table structure for table `import_items`
876 DROP TABLE IF EXISTS `import_items`;
877 CREATE TABLE `import_items` (
878 `import_items_id` int(11) NOT NULL auto_increment,
879 `import_record_id` int(11) NOT NULL,
880 `itemnumber` int(11) default NULL,
881 `branchcode` varchar(10) default NULL,
882 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
883 `marcxml` longtext NOT NULL,
884 `import_error` mediumtext,
885 PRIMARY KEY (`import_items_id`),
886 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
887 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
888 KEY `itemnumber` (`itemnumber`),
889 KEY `branchcode` (`branchcode`)
890 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
893 -- Table structure for table `issues`
896 DROP TABLE IF EXISTS `issues`;
897 CREATE TABLE `issues` (
898 `borrowernumber` int(11) default NULL,
899 `itemnumber` int(11) default NULL,
900 `date_due` date default NULL,
901 `branchcode` varchar(10) default NULL,
902 `issuingbranch` varchar(18) default NULL,
903 `returndate` date default NULL,
904 `lastreneweddate` date default NULL,
905 `return` varchar(4) default NULL,
906 `renewals` tinyint(4) default NULL,
907 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
908 `issuedate` date default NULL,
909 KEY `issuesborridx` (`borrowernumber`),
910 KEY `issuesitemidx` (`itemnumber`),
911 KEY `bordate` (`borrowernumber`,`timestamp`),
912 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
913 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
914 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
917 -- Table structure for table `issuingrules`
920 DROP TABLE IF EXISTS `issuingrules`;
921 CREATE TABLE `issuingrules` (
922 `categorycode` varchar(10) NOT NULL default '',
923 `itemtype` varchar(10) NOT NULL default '',
924 `restrictedtype` tinyint(1) default NULL,
925 `rentaldiscount` decimal(28,6) default NULL,
926 `reservecharge` decimal(28,6) default NULL,
927 `fine` decimal(28,6) default NULL,
928 `finedays` int(11) default NULL,
929 `firstremind` int(11) default NULL,
930 `chargeperiod` int(11) default NULL,
931 `accountsent` int(11) default NULL,
932 `chargename` varchar(100) default NULL,
933 `maxissueqty` int(4) default NULL,
934 `issuelength` int(4) default NULL,
935 `renewalsallowed` smallint(6) NOT NULL default "0",
936 `reservesallowed` smallint(6) NOT NULL default "0",
937 `branchcode` varchar(10) NOT NULL default '',
938 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
939 KEY `categorycode` (`categorycode`),
940 KEY `itemtype` (`itemtype`)
941 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
944 -- Table structure for table `items`
947 DROP TABLE IF EXISTS `items`;
948 CREATE TABLE `items` (
949 `itemnumber` int(11) NOT NULL auto_increment,
950 `biblionumber` int(11) NOT NULL default 0,
951 `biblioitemnumber` int(11) NOT NULL default 0,
952 `barcode` varchar(20) default NULL,
953 `dateaccessioned` date default NULL,
954 `booksellerid` mediumtext default NULL,
955 `homebranch` varchar(10) default NULL,
956 `price` decimal(8,2) default NULL,
957 `replacementprice` decimal(8,2) default NULL,
958 `replacementpricedate` date default NULL,
959 `datelastborrowed` date default NULL,
960 `datelastseen` date default NULL,
961 `stack` tinyint(1) default NULL,
962 `notforloan` tinyint(1) NOT NULL default 0,
963 `damaged` tinyint(1) NOT NULL default 0,
964 `itemlost` tinyint(1) NOT NULL default 0,
965 `wthdrawn` tinyint(1) NOT NULL default 0,
966 `itemcallnumber` varchar(255) default NULL,
967 `issues` smallint(6) default NULL,
968 `renewals` smallint(6) default NULL,
969 `reserves` smallint(6) default NULL,
970 `restricted` tinyint(1) default NULL,
971 `itemnotes` mediumtext,
972 `holdingbranch` varchar(10) default NULL,
973 `paidfor` mediumtext,
974 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
975 `location` varchar(80) default NULL,
976 `permanent_location` varchar(80) default NULL,
977 `onloan` date default NULL,
978 `cn_source` varchar(10) default NULL,
979 `cn_sort` varchar(30) default NULL,
980 `ccode` varchar(10) default NULL,
981 `materials` varchar(10) default NULL,
982 `uri` varchar(255) default NULL,
983 `itype` varchar(10) default NULL,
984 `more_subfields_xml` longtext default NULL,
985 `enumchron` varchar(80) default NULL,
986 `copynumber` varchar(32) default NULL,
987 `stocknumber` varchar(32) default NULL,
988 PRIMARY KEY (`itemnumber`),
989 UNIQUE KEY `itembarcodeidx` (`barcode`),
990 UNIQUE KEY `itemstocknumberidx` (`stocknumber`),
991 KEY `itembinoidx` (`biblioitemnumber`),
992 KEY `itembibnoidx` (`biblionumber`),
993 KEY `homebranch` (`homebranch`),
994 KEY `holdingbranch` (`holdingbranch`),
995 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
996 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
997 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
998 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1001 -- Table structure for table `itemtypes`
1004 DROP TABLE IF EXISTS `itemtypes`;
1005 CREATE TABLE `itemtypes` (
1006 `itemtype` varchar(10) NOT NULL default '',
1007 `description` mediumtext,
1008 `rentalcharge` double(16,4) default NULL,
1009 `notforloan` smallint(6) default NULL,
1010 `imageurl` varchar(200) default NULL,
1012 PRIMARY KEY (`itemtype`),
1013 UNIQUE KEY `itemtype` (`itemtype`)
1014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1017 -- Table structure for table `labels_batches`
1020 DROP TABLE IF EXISTS `labels_batches`;
1021 CREATE TABLE `labels_batches` (
1022 `label_id` int(11) NOT NULL auto_increment,
1023 `batch_id` int(10) NOT NULL default '1',
1024 `item_number` int(11) NOT NULL default '0',
1025 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1026 `branch_code` varchar(10) NOT NULL default 'NB',
1027 PRIMARY KEY USING BTREE (`label_id`),
1028 KEY `branch_fk` (`branch_code`),
1029 KEY `item_fk` (`item_number`),
1030 CONSTRAINT `item_fk_constraint` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE,
1031 CONSTRAINT `branch_fk_constraint` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE
1032 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1035 -- Table structure for table `labels_layouts`
1038 DROP TABLE IF EXISTS `labels_layouts`;
1039 CREATE TABLE `labels_layouts` (
1040 `layout_id` int(4) NOT NULL auto_increment,
1041 `barcode_type` char(100) NOT NULL default 'CODE39',
1042 `printing_type` char(32) NOT NULL default 'BAR',
1043 `layout_name` char(20) NOT NULL default 'DEFAULT',
1044 `guidebox` int(1) default '0',
1045 `font` char(10) character set utf8 collate utf8_unicode_ci NOT NULL default 'TR',
1046 `font_size` int(4) NOT NULL default '10',
1047 `callnum_split` int(1) default '0',
1048 `text_justify` char(1) character set utf8 collate utf8_unicode_ci NOT NULL default 'L',
1049 `format_string` varchar(210) NOT NULL default 'barcode',
1050 PRIMARY KEY USING BTREE (`layout_id`)
1051 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1054 -- Table structure for table `labels_templates`
1057 DROP TABLE IF EXISTS `labels_templates`;
1058 CREATE TABLE `labels_templates` (
1059 `template_id` int(4) NOT NULL auto_increment,
1060 `profile_id` int(4) default NULL,
1061 `template_code` char(100) NOT NULL default 'DEFAULT TEMPLATE',
1062 `template_desc` char(100) NOT NULL default 'Default description',
1063 `page_width` float NOT NULL default '0',
1064 `page_height` float NOT NULL default '0',
1065 `label_width` float NOT NULL default '0',
1066 `label_height` float NOT NULL default '0',
1067 `top_text_margin` float NOT NULL default '0',
1068 `left_text_margin` float NOT NULL default '0',
1069 `top_margin` float NOT NULL default '0',
1070 `left_margin` float NOT NULL default '0',
1071 `cols` int(2) NOT NULL default '0',
1072 `rows` int(2) NOT NULL default '0',
1073 `col_gap` float NOT NULL default '0',
1074 `row_gap` float NOT NULL default '0',
1075 `units` char(20) NOT NULL default 'POINT',
1076 PRIMARY KEY (`template_id`),
1077 KEY `template_profile_fk_constraint` (`profile_id`)
1078 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1081 -- Table structure for table `letter`
1084 DROP TABLE IF EXISTS `letter`;
1085 CREATE TABLE `letter` (
1086 `module` varchar(20) NOT NULL default '',
1087 `code` varchar(20) NOT NULL default '',
1088 `name` varchar(100) NOT NULL default '',
1089 `title` varchar(200) NOT NULL default '',
1091 PRIMARY KEY (`module`,`code`)
1092 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1095 -- Table structure for table `marc_subfield_structure`
1098 DROP TABLE IF EXISTS `marc_subfield_structure`;
1099 CREATE TABLE `marc_subfield_structure` (
1100 `tagfield` varchar(3) NOT NULL default '',
1101 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1102 `liblibrarian` varchar(255) NOT NULL default '',
1103 `libopac` varchar(255) NOT NULL default '',
1104 `repeatable` tinyint(4) NOT NULL default 0,
1105 `mandatory` tinyint(4) NOT NULL default 0,
1106 `kohafield` varchar(40) default NULL,
1107 `tab` tinyint(1) default NULL,
1108 `authorised_value` varchar(20) default NULL,
1109 `authtypecode` varchar(20) default NULL,
1110 `value_builder` varchar(80) default NULL,
1111 `isurl` tinyint(1) default NULL,
1112 `hidden` tinyint(1) default NULL,
1113 `frameworkcode` varchar(4) NOT NULL default '',
1114 `seealso` varchar(1100) default NULL,
1115 `link` varchar(80) default NULL,
1116 `defaultvalue` text default NULL,
1117 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1118 KEY `kohafield_2` (`kohafield`),
1119 KEY `tab` (`frameworkcode`,`tab`),
1120 KEY `kohafield` (`frameworkcode`,`kohafield`)
1121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1124 -- Table structure for table `marc_tag_structure`
1127 DROP TABLE IF EXISTS `marc_tag_structure`;
1128 CREATE TABLE `marc_tag_structure` (
1129 `tagfield` varchar(3) NOT NULL default '',
1130 `liblibrarian` varchar(255) NOT NULL default '',
1131 `libopac` varchar(255) NOT NULL default '',
1132 `repeatable` tinyint(4) NOT NULL default 0,
1133 `mandatory` tinyint(4) NOT NULL default 0,
1134 `authorised_value` varchar(10) default NULL,
1135 `frameworkcode` varchar(4) NOT NULL default '',
1136 PRIMARY KEY (`frameworkcode`,`tagfield`)
1137 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1140 -- Table structure for table `marc_matchers`
1143 DROP TABLE IF EXISTS `marc_matchers`;
1144 CREATE TABLE `marc_matchers` (
1145 `matcher_id` int(11) NOT NULL auto_increment,
1146 `code` varchar(10) NOT NULL default '',
1147 `description` varchar(255) NOT NULL default '',
1148 `record_type` varchar(10) NOT NULL default 'biblio',
1149 `threshold` int(11) NOT NULL default 0,
1150 PRIMARY KEY (`matcher_id`),
1151 KEY `code` (`code`),
1152 KEY `record_type` (`record_type`)
1153 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1156 -- Table structure for table `matchpoints`
1158 DROP TABLE IF EXISTS `matchpoints`;
1159 CREATE TABLE `matchpoints` (
1160 `matcher_id` int(11) NOT NULL,
1161 `matchpoint_id` int(11) NOT NULL auto_increment,
1162 `search_index` varchar(30) NOT NULL default '',
1163 `score` int(11) NOT NULL default 0,
1164 PRIMARY KEY (`matchpoint_id`),
1165 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1166 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1171 -- Table structure for table `matchpoint_components`
1173 DROP TABLE IF EXISTS `matchpoint_components`;
1174 CREATE TABLE `matchpoint_components` (
1175 `matchpoint_id` int(11) NOT NULL,
1176 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1177 sequence int(11) NOT NULL default 0,
1178 tag varchar(3) NOT NULL default '',
1179 subfields varchar(40) NOT NULL default '',
1180 offset int(4) NOT NULL default 0,
1181 length int(4) NOT NULL default 0,
1182 PRIMARY KEY (`matchpoint_component_id`),
1183 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1184 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1185 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1186 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1189 -- Table structure for table `matcher_component_norms`
1191 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1192 CREATE TABLE `matchpoint_component_norms` (
1193 `matchpoint_component_id` int(11) NOT NULL,
1194 `sequence` int(11) NOT NULL default 0,
1195 `norm_routine` varchar(50) NOT NULL default '',
1196 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1197 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1198 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1202 -- Table structure for table `matcher_matchpoints`
1204 DROP TABLE IF EXISTS `matcher_matchpoints`;
1205 CREATE TABLE `matcher_matchpoints` (
1206 `matcher_id` int(11) NOT NULL,
1207 `matchpoint_id` int(11) NOT NULL,
1208 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1209 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1210 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1211 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1215 -- Table structure for table `matchchecks`
1217 DROP TABLE IF EXISTS `matchchecks`;
1218 CREATE TABLE `matchchecks` (
1219 `matcher_id` int(11) NOT NULL,
1220 `matchcheck_id` int(11) NOT NULL auto_increment,
1221 `source_matchpoint_id` int(11) NOT NULL,
1222 `target_matchpoint_id` int(11) NOT NULL,
1223 PRIMARY KEY (`matchcheck_id`),
1224 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1225 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1226 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1227 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1228 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1229 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1233 -- Table structure for table `notifys`
1236 DROP TABLE IF EXISTS `notifys`;
1237 CREATE TABLE `notifys` (
1238 `notify_id` int(11) NOT NULL default 0,
1239 `borrowernumber` int(11) NOT NULL default 0,
1240 `itemnumber` int(11) NOT NULL default 0,
1241 `notify_date` date default NULL,
1242 `notify_send_date` date default NULL,
1243 `notify_level` int(1) NOT NULL default 0,
1244 `method` varchar(20) NOT NULL default ''
1245 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1248 -- Table structure for table `nozebra`
1251 DROP TABLE IF EXISTS `nozebra`;
1252 CREATE TABLE `nozebra` (
1253 `server` varchar(20) NOT NULL,
1254 `indexname` varchar(40) NOT NULL,
1255 `value` varchar(250) NOT NULL,
1256 `biblionumbers` longtext NOT NULL,
1257 KEY `indexname` (`server`,`indexname`),
1258 KEY `value` (`server`,`value`))
1259 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1262 -- Table structure for table `old_issues`
1265 DROP TABLE IF EXISTS `old_issues`;
1266 CREATE TABLE `old_issues` (
1267 `borrowernumber` int(11) default NULL,
1268 `itemnumber` int(11) default NULL,
1269 `date_due` date default NULL,
1270 `branchcode` varchar(10) default NULL,
1271 `issuingbranch` varchar(18) default NULL,
1272 `returndate` date default NULL,
1273 `lastreneweddate` date default NULL,
1274 `return` varchar(4) default NULL,
1275 `renewals` tinyint(4) default NULL,
1276 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1277 `issuedate` date default NULL,
1278 KEY `old_issuesborridx` (`borrowernumber`),
1279 KEY `old_issuesitemidx` (`itemnumber`),
1280 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1281 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1282 ON DELETE SET NULL ON UPDATE SET NULL,
1283 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1284 ON DELETE SET NULL ON UPDATE SET NULL
1285 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1288 -- Table structure for table `old_reserves`
1290 DROP TABLE IF EXISTS `old_reserves`;
1291 CREATE TABLE `old_reserves` (
1292 `borrowernumber` int(11) default NULL,
1293 `reservedate` date default NULL,
1294 `biblionumber` int(11) default NULL,
1295 `constrainttype` varchar(1) default NULL,
1296 `branchcode` varchar(10) default NULL,
1297 `notificationdate` date default NULL,
1298 `reminderdate` date default NULL,
1299 `cancellationdate` date default NULL,
1300 `reservenotes` mediumtext,
1301 `priority` smallint(6) default NULL,
1302 `found` varchar(1) default NULL,
1303 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1304 `itemnumber` int(11) default NULL,
1305 `waitingdate` date default NULL,
1306 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1307 KEY `old_reserves_biblionumber` (`biblionumber`),
1308 KEY `old_reserves_itemnumber` (`itemnumber`),
1309 KEY `old_reserves_branchcode` (`branchcode`),
1310 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1311 ON DELETE SET NULL ON UPDATE SET NULL,
1312 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1313 ON DELETE SET NULL ON UPDATE SET NULL,
1314 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1315 ON DELETE SET NULL ON UPDATE SET NULL
1316 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1319 -- Table structure for table `opac_news`
1322 DROP TABLE IF EXISTS `opac_news`;
1323 CREATE TABLE `opac_news` (
1324 `idnew` int(10) unsigned NOT NULL auto_increment,
1325 `title` varchar(250) NOT NULL default '',
1326 `new` text NOT NULL,
1327 `lang` varchar(25) NOT NULL default '',
1328 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1329 `expirationdate` date default NULL,
1330 `number` int(11) default NULL,
1331 PRIMARY KEY (`idnew`)
1332 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1335 -- Table structure for table `overduerules`
1338 DROP TABLE IF EXISTS `overduerules`;
1339 CREATE TABLE `overduerules` (
1340 `branchcode` varchar(10) NOT NULL default '',
1341 `categorycode` varchar(10) NOT NULL default '',
1342 `delay1` int(4) default 0,
1343 `letter1` varchar(20) default NULL,
1344 `debarred1` varchar(1) default 0,
1345 `delay2` int(4) default 0,
1346 `debarred2` varchar(1) default 0,
1347 `letter2` varchar(20) default NULL,
1348 `delay3` int(4) default 0,
1349 `letter3` varchar(20) default NULL,
1350 `debarred3` int(1) default 0,
1351 PRIMARY KEY (`branchcode`,`categorycode`)
1352 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1355 -- Table structure for table `patroncards`
1358 DROP TABLE IF EXISTS `patroncards`;
1359 CREATE TABLE `patroncards` (
1360 `cardid` int(11) NOT NULL auto_increment,
1361 `batch_id` varchar(10) NOT NULL default '1',
1362 `borrowernumber` int(11) NOT NULL,
1363 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1364 PRIMARY KEY (`cardid`),
1365 KEY `patroncards_ibfk_1` (`borrowernumber`),
1366 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1370 -- Table structure for table `patronimage`
1373 DROP TABLE IF EXISTS `patronimage`;
1374 CREATE TABLE `patronimage` (
1375 `cardnumber` varchar(16) NOT NULL,
1376 `mimetype` varchar(15) NOT NULL,
1377 `imagefile` mediumblob NOT NULL,
1378 PRIMARY KEY (`cardnumber`),
1379 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1380 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1383 -- Table structure for table `printers`
1386 DROP TABLE IF EXISTS `printers`;
1387 CREATE TABLE `printers` (
1388 `printername` varchar(40) NOT NULL default '',
1389 `printqueue` varchar(20) default NULL,
1390 `printtype` varchar(20) default NULL,
1391 PRIMARY KEY (`printername`)
1392 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1395 -- Table structure for table `printers_profile`
1398 DROP TABLE IF EXISTS `printers_profile`;
1399 CREATE TABLE `printers_profile` (
1400 `profile_id` int(4) NOT NULL auto_increment,
1401 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1402 `template_id` int(4) NOT NULL default '0',
1403 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1404 `offset_horz` float NOT NULL default '0',
1405 `offset_vert` float NOT NULL default '0',
1406 `creep_horz` float NOT NULL default '0',
1407 `creep_vert` float NOT NULL default '0',
1408 `units` char(20) NOT NULL default 'POINT',
1409 PRIMARY KEY (`profile_id`),
1410 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`)
1411 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1414 -- Table structure for table `repeatable_holidays`
1417 DROP TABLE IF EXISTS `repeatable_holidays`;
1418 CREATE TABLE `repeatable_holidays` (
1419 `id` int(11) NOT NULL auto_increment,
1420 `branchcode` varchar(10) NOT NULL default '',
1421 `weekday` smallint(6) default NULL,
1422 `day` smallint(6) default NULL,
1423 `month` smallint(6) default NULL,
1424 `title` varchar(50) NOT NULL default '',
1425 `description` text NOT NULL,
1427 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1430 -- Table structure for table `reports_dictionary`
1433 DROP TABLE IF EXISTS `reports_dictionary`;
1434 CREATE TABLE reports_dictionary (
1435 `id` int(11) NOT NULL auto_increment,
1436 `name` varchar(255) default NULL,
1438 `date_created` datetime default NULL,
1439 `date_modified` datetime default NULL,
1441 `area` int(11) default NULL,
1443 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1446 -- Table structure for table `reserveconstraints`
1449 DROP TABLE IF EXISTS `reserveconstraints`;
1450 CREATE TABLE `reserveconstraints` (
1451 `borrowernumber` int(11) NOT NULL default 0,
1452 `reservedate` date default NULL,
1453 `biblionumber` int(11) NOT NULL default 0,
1454 `biblioitemnumber` int(11) default NULL,
1455 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1456 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1459 -- Table structure for table `reserves`
1462 DROP TABLE IF EXISTS `reserves`;
1463 CREATE TABLE `reserves` (
1464 `borrowernumber` int(11) NOT NULL default 0,
1465 `reservedate` date default NULL,
1466 `biblionumber` int(11) NOT NULL default 0,
1467 `constrainttype` varchar(1) default NULL,
1468 `branchcode` varchar(10) default NULL,
1469 `notificationdate` date default NULL,
1470 `reminderdate` date default NULL,
1471 `cancellationdate` date default NULL,
1472 `reservenotes` mediumtext,
1473 `priority` smallint(6) default NULL,
1474 `found` varchar(1) default NULL,
1475 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1476 `itemnumber` int(11) default NULL,
1477 `waitingdate` date default NULL,
1478 KEY `borrowernumber` (`borrowernumber`),
1479 KEY `biblionumber` (`biblionumber`),
1480 KEY `itemnumber` (`itemnumber`),
1481 KEY `branchcode` (`branchcode`),
1482 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1483 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1484 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1485 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1486 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1489 -- Table structure for table `reviews`
1492 DROP TABLE IF EXISTS `reviews`;
1493 CREATE TABLE `reviews` (
1494 `reviewid` int(11) NOT NULL auto_increment,
1495 `borrowernumber` int(11) default NULL,
1496 `biblionumber` int(11) default NULL,
1498 `approved` tinyint(4) default NULL,
1499 `datereviewed` datetime default NULL,
1500 PRIMARY KEY (`reviewid`)
1501 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1504 -- Table structure for table `roadtype`
1507 DROP TABLE IF EXISTS `roadtype`;
1508 CREATE TABLE `roadtype` (
1509 `roadtypeid` int(11) NOT NULL auto_increment,
1510 `road_type` varchar(100) NOT NULL default '',
1511 PRIMARY KEY (`roadtypeid`)
1512 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1515 -- Table structure for table `saved_sql`
1518 DROP TABLE IF EXISTS `saved_sql`;
1519 CREATE TABLE saved_sql (
1520 `id` int(11) NOT NULL auto_increment,
1521 `borrowernumber` int(11) default NULL,
1522 `date_created` datetime default NULL,
1523 `last_modified` datetime default NULL,
1525 `last_run` datetime default NULL,
1526 `report_name` varchar(255) default NULL,
1527 `type` varchar(255) default NULL,
1530 KEY boridx (`borrowernumber`)
1531 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1535 -- Table structure for `saved_reports`
1538 DROP TABLE IF EXISTS `saved_reports`;
1539 CREATE TABLE saved_reports (
1540 `id` int(11) NOT NULL auto_increment,
1541 `report_id` int(11) default NULL,
1543 `date_run` datetime default NULL,
1545 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1549 -- Table structure for table `search_history`
1552 DROP TABLE IF EXISTS `search_history`;
1553 CREATE TABLE IF NOT EXISTS `search_history` (
1554 `userid` int(11) NOT NULL,
1555 `sessionid` varchar(32) NOT NULL,
1556 `query_desc` varchar(255) NOT NULL,
1557 `query_cgi` varchar(255) NOT NULL,
1558 `total` int(11) NOT NULL,
1559 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1560 KEY `userid` (`userid`),
1561 KEY `sessionid` (`sessionid`)
1562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1566 -- Table structure for table `serial`
1569 DROP TABLE IF EXISTS `serial`;
1570 CREATE TABLE `serial` (
1571 `serialid` int(11) NOT NULL auto_increment,
1572 `biblionumber` varchar(100) NOT NULL default '',
1573 `subscriptionid` varchar(100) NOT NULL default '',
1574 `serialseq` varchar(100) NOT NULL default '',
1575 `status` tinyint(4) NOT NULL default 0,
1576 `planneddate` date default NULL,
1578 `publisheddate` date default NULL,
1579 `itemnumber` text default NULL,
1580 `claimdate` date default NULL,
1581 `routingnotes` text,
1582 PRIMARY KEY (`serialid`)
1583 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1586 -- Table structure for table `sessions`
1589 DROP TABLE IF EXISTS sessions;
1590 CREATE TABLE sessions (
1591 `id` varchar(32) NOT NULL,
1592 `a_session` text NOT NULL,
1594 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1597 -- Table structure for table `special_holidays`
1600 DROP TABLE IF EXISTS `special_holidays`;
1601 CREATE TABLE `special_holidays` (
1602 `id` int(11) NOT NULL auto_increment,
1603 `branchcode` varchar(10) NOT NULL default '',
1604 `day` smallint(6) NOT NULL default 0,
1605 `month` smallint(6) NOT NULL default 0,
1606 `year` smallint(6) NOT NULL default 0,
1607 `isexception` smallint(1) NOT NULL default 1,
1608 `title` varchar(50) NOT NULL default '',
1609 `description` text NOT NULL,
1611 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1614 -- Table structure for table `statistics`
1617 DROP TABLE IF EXISTS `statistics`;
1618 CREATE TABLE `statistics` (
1619 `datetime` datetime default NULL,
1620 `branch` varchar(10) default NULL,
1621 `proccode` varchar(4) default NULL,
1622 `value` double(16,4) default NULL,
1623 `type` varchar(16) default NULL,
1625 `usercode` varchar(10) default NULL,
1626 `itemnumber` int(11) default NULL,
1627 `itemtype` varchar(10) default NULL,
1628 `borrowernumber` int(11) default NULL,
1629 `associatedborrower` int(11) default NULL,
1630 KEY `timeidx` (`datetime`)
1631 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1634 -- Table structure for table `stopwords`
1637 DROP TABLE IF EXISTS `stopwords`;
1638 CREATE TABLE `stopwords` (
1639 `word` varchar(255) default NULL
1640 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1643 -- Table structure for table `subscription`
1646 DROP TABLE IF EXISTS `subscription`;
1647 CREATE TABLE `subscription` (
1648 `biblionumber` int(11) NOT NULL default 0,
1649 `subscriptionid` int(11) NOT NULL auto_increment,
1650 `librarian` varchar(100) default '',
1651 `startdate` date default NULL,
1652 `aqbooksellerid` int(11) default 0,
1653 `cost` int(11) default 0,
1654 `aqbudgetid` int(11) default 0,
1655 `weeklength` int(11) default 0,
1656 `monthlength` int(11) default 0,
1657 `numberlength` int(11) default 0,
1658 `periodicity` tinyint(4) default 0,
1659 `dow` varchar(100) default '',
1660 `numberingmethod` varchar(100) default '',
1662 `status` varchar(100) NOT NULL default '',
1663 `add1` int(11) default 0,
1664 `every1` int(11) default 0,
1665 `whenmorethan1` int(11) default 0,
1666 `setto1` int(11) default NULL,
1667 `lastvalue1` int(11) default NULL,
1668 `add2` int(11) default 0,
1669 `every2` int(11) default 0,
1670 `whenmorethan2` int(11) default 0,
1671 `setto2` int(11) default NULL,
1672 `lastvalue2` int(11) default NULL,
1673 `add3` int(11) default 0,
1674 `every3` int(11) default 0,
1675 `innerloop1` int(11) default 0,
1676 `innerloop2` int(11) default 0,
1677 `innerloop3` int(11) default 0,
1678 `whenmorethan3` int(11) default 0,
1679 `setto3` int(11) default NULL,
1680 `lastvalue3` int(11) default NULL,
1681 `issuesatonce` tinyint(3) NOT NULL default 1,
1682 `firstacquidate` date default NULL,
1683 `manualhistory` tinyint(1) NOT NULL default 0,
1684 `irregularity` text,
1685 `letter` varchar(20) default NULL,
1686 `numberpattern` tinyint(3) default 0,
1687 `distributedto` text,
1688 `internalnotes` longtext,
1690 `location` varchar(80) NULL default '',
1691 `branchcode` varchar(10) NOT NULL default '',
1692 `hemisphere` tinyint(3) default 0,
1693 `lastbranch` varchar(10),
1694 `serialsadditems` tinyint(1) NOT NULL default '0',
1695 `staffdisplaycount` VARCHAR(10) NULL,
1696 `opacdisplaycount` VARCHAR(10) NULL,
1697 `graceperiod` int(11) NOT NULL default '0',
1698 PRIMARY KEY (`subscriptionid`)
1699 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1702 -- Table structure for table `subscriptionhistory`
1705 DROP TABLE IF EXISTS `subscriptionhistory`;
1706 CREATE TABLE `subscriptionhistory` (
1707 `biblionumber` int(11) NOT NULL default 0,
1708 `subscriptionid` int(11) NOT NULL default 0,
1709 `histstartdate` date default NULL,
1710 `histenddate` date default NULL,
1711 `missinglist` longtext NOT NULL,
1712 `recievedlist` longtext NOT NULL,
1713 `opacnote` varchar(150) NOT NULL default '',
1714 `librariannote` varchar(150) NOT NULL default '',
1715 PRIMARY KEY (`subscriptionid`),
1716 KEY `biblionumber` (`biblionumber`)
1717 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1720 -- Table structure for table `subscriptionroutinglist`
1723 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1724 CREATE TABLE `subscriptionroutinglist` (
1725 `routingid` int(11) NOT NULL auto_increment,
1726 `borrowernumber` int(11) default NULL,
1727 `ranking` int(11) default NULL,
1728 `subscriptionid` int(11) default NULL,
1729 PRIMARY KEY (`routingid`)
1730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1733 -- Table structure for table `suggestions`
1736 DROP TABLE IF EXISTS `suggestions`;
1737 CREATE TABLE `suggestions` (
1738 `suggestionid` int(8) NOT NULL auto_increment,
1739 `suggestedby` int(11) NOT NULL default 0,
1740 `suggesteddate` date NOT NULL default 0,
1741 `managedby` int(11) default NULL,
1742 `manageddate` date default NULL,
1743 acceptedby INT(11) default NULL,
1744 accepteddate date default NULL,
1745 rejectedby INT(11) default NULL,
1746 rejecteddate date default NULL,
1747 `STATUS` varchar(10) NOT NULL default '',
1749 `author` varchar(80) default NULL,
1750 `title` varchar(80) default NULL,
1751 `copyrightdate` smallint(6) default NULL,
1752 `publishercode` varchar(255) default NULL,
1753 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1754 `volumedesc` varchar(255) default NULL,
1755 `publicationyear` smallint(6) default 0,
1756 `place` varchar(255) default NULL,
1757 `isbn` varchar(30) default NULL,
1758 `mailoverseeing` smallint(1) default 0,
1759 `biblionumber` int(11) default NULL,
1762 branchcode VARCHAR(10) default NULL,
1763 collectiontitle text default NULL,
1764 itemtype VARCHAR(30) default NULL,
1765 PRIMARY KEY (`suggestionid`),
1766 KEY `suggestedby` (`suggestedby`),
1767 KEY `managedby` (`managedby`)
1768 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1771 -- Table structure for table `systempreferences`
1774 DROP TABLE IF EXISTS `systempreferences`;
1775 CREATE TABLE `systempreferences` (
1776 `variable` varchar(50) NOT NULL default '',
1778 `options` mediumtext,
1780 `type` varchar(20) default NULL,
1781 PRIMARY KEY (`variable`)
1782 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1785 -- Table structure for table `tags`
1788 DROP TABLE IF EXISTS `tags`;
1789 CREATE TABLE `tags` (
1790 `entry` varchar(255) NOT NULL default '',
1791 `weight` bigint(20) NOT NULL default 0,
1792 PRIMARY KEY (`entry`)
1793 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1796 -- Table structure for table `tags_all`
1799 DROP TABLE IF EXISTS `tags_all`;
1800 CREATE TABLE `tags_all` (
1801 `tag_id` int(11) NOT NULL auto_increment,
1802 `borrowernumber` int(11) NOT NULL,
1803 `biblionumber` int(11) NOT NULL,
1804 `term` varchar(255) NOT NULL,
1805 `language` int(4) default NULL,
1806 `date_created` datetime NOT NULL,
1807 PRIMARY KEY (`tag_id`),
1808 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1809 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1810 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1811 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1812 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1813 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1814 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1817 -- Table structure for table `tags_approval`
1820 DROP TABLE IF EXISTS `tags_approval`;
1821 CREATE TABLE `tags_approval` (
1822 `term` varchar(255) NOT NULL,
1823 `approved` int(1) NOT NULL default '0',
1824 `date_approved` datetime default NULL,
1825 `approved_by` int(11) default NULL,
1826 `weight_total` int(9) NOT NULL default '1',
1827 PRIMARY KEY (`term`),
1828 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1829 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1830 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1831 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1834 -- Table structure for table `tags_index`
1837 DROP TABLE IF EXISTS `tags_index`;
1838 CREATE TABLE `tags_index` (
1839 `term` varchar(255) NOT NULL,
1840 `biblionumber` int(11) NOT NULL,
1841 `weight` int(9) NOT NULL default '1',
1842 PRIMARY KEY (`term`,`biblionumber`),
1843 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1844 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1845 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1846 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1847 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1848 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1851 -- Table structure for table `userflags`
1854 DROP TABLE IF EXISTS `userflags`;
1855 CREATE TABLE `userflags` (
1856 `bit` int(11) NOT NULL default 0,
1857 `flag` varchar(30) default NULL,
1858 `flagdesc` varchar(255) default NULL,
1859 `defaulton` int(11) default NULL,
1861 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1864 -- Table structure for table `virtualshelves`
1867 DROP TABLE IF EXISTS `virtualshelves`;
1868 CREATE TABLE `virtualshelves` (
1869 `shelfnumber` int(11) NOT NULL auto_increment,
1870 `shelfname` varchar(255) default NULL,
1871 `owner` varchar(80) default NULL,
1872 `category` varchar(1) default NULL,
1873 `sortfield` varchar(16) default NULL,
1874 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1875 PRIMARY KEY (`shelfnumber`)
1876 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1879 -- Table structure for table `virtualshelfcontents`
1882 DROP TABLE IF EXISTS `virtualshelfcontents`;
1883 CREATE TABLE `virtualshelfcontents` (
1884 `shelfnumber` int(11) NOT NULL default 0,
1885 `biblionumber` int(11) NOT NULL default 0,
1886 `flags` int(11) default NULL,
1887 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1888 KEY `shelfnumber` (`shelfnumber`),
1889 KEY `biblionumber` (`biblionumber`),
1890 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1891 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1892 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1895 -- Table structure for table `z3950servers`
1898 DROP TABLE IF EXISTS `z3950servers`;
1899 CREATE TABLE `z3950servers` (
1900 `host` varchar(255) default NULL,
1901 `port` int(11) default NULL,
1902 `db` varchar(255) default NULL,
1903 `userid` varchar(255) default NULL,
1904 `password` varchar(255) default NULL,
1906 `id` int(11) NOT NULL auto_increment,
1907 `checked` smallint(6) default NULL,
1908 `rank` int(11) default NULL,
1909 `syntax` varchar(80) default NULL,
1911 `position` enum('primary','secondary','') NOT NULL default 'primary',
1912 `type` enum('zed','opensearch') NOT NULL default 'zed',
1913 `encoding` text default NULL,
1914 `description` text NOT NULL,
1916 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1919 -- Table structure for table `zebraqueue`
1922 DROP TABLE IF EXISTS `zebraqueue`;
1923 CREATE TABLE `zebraqueue` (
1924 `id` int(11) NOT NULL auto_increment,
1925 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1926 `operation` char(20) NOT NULL default '',
1927 `server` char(20) NOT NULL default '',
1928 `done` int(11) NOT NULL default '0',
1929 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1931 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1932 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1934 DROP TABLE IF EXISTS `services_throttle`;
1935 CREATE TABLE `services_throttle` (
1936 `service_type` varchar(10) NOT NULL default '',
1937 `service_count` varchar(45) default NULL,
1938 PRIMARY KEY (`service_type`)
1939 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1941 -- http://www.w3.org/International/articles/language-tags/
1944 DROP TABLE IF EXISTS language_subtag_registry;
1945 CREATE TABLE language_subtag_registry (
1947 type varchar(25), -- language-script-region-variant-extension-privateuse
1948 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1950 id int(11) NOT NULL auto_increment,
1952 KEY `subtag` (`subtag`)
1953 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1955 -- TODO: add suppress_scripts
1956 -- this maps three letter codes defined in iso639.2 back to their
1957 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1958 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1959 CREATE TABLE language_rfc4646_to_iso639 (
1960 rfc4646_subtag varchar(25),
1961 iso639_2_code varchar(25),
1962 id int(11) NOT NULL auto_increment,
1964 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1965 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1967 DROP TABLE IF EXISTS language_descriptions;
1968 CREATE TABLE language_descriptions (
1972 description varchar(255),
1973 id int(11) NOT NULL auto_increment,
1976 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1978 -- bi-directional support, keyed by script subcode
1979 DROP TABLE IF EXISTS language_script_bidi;
1980 CREATE TABLE language_script_bidi (
1981 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1982 bidi varchar(3), -- rtl ltr
1983 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1984 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1986 -- TODO: need to map language subtags to script subtags for detection
1987 -- of bidi when script is not specified (like ar, he)
1988 DROP TABLE IF EXISTS language_script_mapping;
1989 CREATE TABLE language_script_mapping (
1990 language_subtag varchar(25),
1991 script_subtag varchar(25),
1992 KEY `language_subtag` (`language_subtag`)
1993 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1995 DROP TABLE IF EXISTS `permissions`;
1996 CREATE TABLE `permissions` (
1997 `module_bit` int(11) NOT NULL DEFAULT 0,
1998 `code` varchar(64) DEFAULT NULL,
1999 `description` varchar(255) DEFAULT NULL,
2000 PRIMARY KEY (`module_bit`, `code`),
2001 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2002 ON DELETE CASCADE ON UPDATE CASCADE
2003 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2005 DROP TABLE IF EXISTS `serialitems`;
2006 CREATE TABLE `serialitems` (
2007 `itemnumber` int(11) NOT NULL,
2008 `serialid` int(11) NOT NULL,
2009 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2010 KEY `serialitems_sfk_1` (`serialid`),
2011 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2012 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2014 DROP TABLE IF EXISTS `user_permissions`;
2015 CREATE TABLE `user_permissions` (
2016 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2017 `module_bit` int(11) NOT NULL DEFAULT 0,
2018 `code` varchar(64) DEFAULT NULL,
2019 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2020 ON DELETE CASCADE ON UPDATE CASCADE,
2021 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2022 ON DELETE CASCADE ON UPDATE CASCADE
2023 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2026 -- Table structure for table `tmp_holdsqueue`
2029 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2030 CREATE TABLE `tmp_holdsqueue` (
2031 `biblionumber` int(11) default NULL,
2032 `itemnumber` int(11) default NULL,
2033 `barcode` varchar(20) default NULL,
2034 `surname` mediumtext NOT NULL,
2037 `borrowernumber` int(11) NOT NULL,
2038 `cardnumber` varchar(16) default NULL,
2039 `reservedate` date default NULL,
2041 `itemcallnumber` varchar(255) default NULL,
2042 `holdingbranch` varchar(10) default NULL,
2043 `pickbranch` varchar(10) default NULL,
2045 `item_level_request` tinyint(4) NOT NULL default 0
2046 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2049 -- Table structure for table `message_queue`
2052 DROP TABLE IF EXISTS `message_queue`;
2053 CREATE TABLE `message_queue` (
2054 `message_id` int(11) NOT NULL auto_increment,
2055 `borrowernumber` int(11) default NULL,
2058 `metadata` text DEFAULT NULL,
2059 `letter_code` varchar(64) DEFAULT NULL,
2060 `message_transport_type` varchar(20) NOT NULL,
2061 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2062 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2063 `to_address` mediumtext,
2064 `from_address` mediumtext,
2065 `content_type` text,
2066 KEY `message_id` (`message_id`),
2067 KEY `borrowernumber` (`borrowernumber`),
2068 KEY `message_transport_type` (`message_transport_type`),
2069 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2070 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2071 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2074 -- Table structure for table `message_transport_types`
2077 DROP TABLE IF EXISTS `message_transport_types`;
2078 CREATE TABLE `message_transport_types` (
2079 `message_transport_type` varchar(20) NOT NULL,
2080 PRIMARY KEY (`message_transport_type`)
2081 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2084 -- Table structure for table `message_attributes`
2087 DROP TABLE IF EXISTS `message_attributes`;
2088 CREATE TABLE `message_attributes` (
2089 `message_attribute_id` int(11) NOT NULL auto_increment,
2090 `message_name` varchar(20) NOT NULL default '',
2091 `takes_days` tinyint(1) NOT NULL default '0',
2092 PRIMARY KEY (`message_attribute_id`),
2093 UNIQUE KEY `message_name` (`message_name`)
2094 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2097 -- Table structure for table `message_transports`
2100 DROP TABLE IF EXISTS `message_transports`;
2101 CREATE TABLE `message_transports` (
2102 `message_attribute_id` int(11) NOT NULL,
2103 `message_transport_type` varchar(20) NOT NULL,
2104 `is_digest` tinyint(1) NOT NULL default '0',
2105 `letter_module` varchar(20) NOT NULL default '',
2106 `letter_code` varchar(20) NOT NULL default '',
2107 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2108 KEY `message_transport_type` (`message_transport_type`),
2109 KEY `letter_module` (`letter_module`,`letter_code`),
2110 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2111 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2112 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2116 -- Table structure for table `borrower_message_preferences`
2119 DROP TABLE IF EXISTS `borrower_message_preferences`;
2120 CREATE TABLE `borrower_message_preferences` (
2121 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2122 `borrowernumber` int(11) default NULL,
2123 `categorycode` varchar(10) default NULL,
2124 `message_attribute_id` int(11) default '0',
2125 `days_in_advance` int(11) default '0',
2126 `wants_digest` tinyint(1) NOT NULL default '0',
2127 PRIMARY KEY (`borrower_message_preference_id`),
2128 KEY `borrowernumber` (`borrowernumber`),
2129 KEY `categorycode` (`categorycode`),
2130 KEY `message_attribute_id` (`message_attribute_id`),
2131 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2132 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2133 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2134 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2137 -- Table structure for table `borrower_message_transport_preferences`
2140 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2141 CREATE TABLE `borrower_message_transport_preferences` (
2142 `borrower_message_preference_id` int(11) NOT NULL default '0',
2143 `message_transport_type` varchar(20) NOT NULL default '0',
2144 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2145 KEY `message_transport_type` (`message_transport_type`),
2146 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,
2147 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
2148 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2151 -- Table structure for the table branch_transfer_limits
2154 DROP TABLE IF EXISTS `branch_transfer_limits`;
2155 CREATE TABLE branch_transfer_limits (
2156 limitId int(8) NOT NULL auto_increment,
2157 toBranch varchar(10) NOT NULL,
2158 fromBranch varchar(10) NOT NULL,
2159 itemtype varchar(10) NULL,
2160 ccode varchar(10) NULL,
2161 PRIMARY KEY (limitId)
2162 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2165 -- Table structure for table `item_circulation_alert_preferences`
2168 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2169 CREATE TABLE `item_circulation_alert_preferences` (
2170 `id` int(11) NOT NULL auto_increment,
2171 `branchcode` varchar(10) NOT NULL,
2172 `categorycode` varchar(10) NOT NULL,
2173 `item_type` varchar(10) NOT NULL,
2174 `notification` varchar(16) NOT NULL,
2176 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2177 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2180 -- Table structure for table `messages`
2183 CREATE TABLE `messages` (
2184 `message_id` int(11) NOT NULL auto_increment,
2185 `borrowernumber` int(11) NOT NULL,
2186 `branchcode` varchar(4) default NULL,
2187 `message_type` varchar(1) NOT NULL,
2188 `message` text NOT NULL,
2189 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2190 PRIMARY KEY (`message_id`)
2191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2194 -- Table structure for table `accountlines`
2197 DROP TABLE IF EXISTS `accountlines`;
2198 CREATE TABLE `accountlines` (
2199 `borrowernumber` int(11) NOT NULL default 0,
2200 `accountno` smallint(6) NOT NULL default 0,
2201 `itemnumber` int(11) default NULL,
2202 `date` date default NULL,
2203 `amount` decimal(28,6) default NULL,
2204 `description` mediumtext,
2205 `dispute` mediumtext,
2206 `accounttype` varchar(5) default NULL,
2207 `amountoutstanding` decimal(28,6) default NULL,
2208 `lastincrement` decimal(28,6) default NULL,
2209 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2210 `notify_id` int(11) NOT NULL default 0,
2211 `notify_level` int(2) NOT NULL default 0,
2212 KEY `acctsborridx` (`borrowernumber`),
2213 KEY `timeidx` (`timestamp`),
2214 KEY `itemnumber` (`itemnumber`),
2215 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2216 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2217 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2220 -- Table structure for table `accountoffsets`
2223 DROP TABLE IF EXISTS `accountoffsets`;
2224 CREATE TABLE `accountoffsets` (
2225 `borrowernumber` int(11) NOT NULL default 0,
2226 `accountno` smallint(6) NOT NULL default 0,
2227 `offsetaccount` smallint(6) NOT NULL default 0,
2228 `offsetamount` decimal(28,6) default NULL,
2229 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2230 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2231 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2234 -- Table structure for table `action_logs`
2237 DROP TABLE IF EXISTS `action_logs`;
2238 CREATE TABLE `action_logs` (
2239 `action_id` int(11) NOT NULL auto_increment,
2240 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2241 `user` int(11) NOT NULL default 0,
2244 `object` int(11) default NULL,
2246 PRIMARY KEY (`action_id`),
2247 KEY (`timestamp`,`user`)
2248 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2251 -- Table structure for table `alert`
2254 DROP TABLE IF EXISTS `alert`;
2255 CREATE TABLE `alert` (
2256 `alertid` int(11) NOT NULL auto_increment,
2257 `borrowernumber` int(11) NOT NULL default 0,
2258 `type` varchar(10) NOT NULL default '',
2259 `externalid` varchar(20) NOT NULL default '',
2260 PRIMARY KEY (`alertid`),
2261 KEY `borrowernumber` (`borrowernumber`),
2262 KEY `type` (`type`,`externalid`)
2263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2266 -- Table structure for table `aqbasketgroups`
2269 DROP TABLE IF EXISTS `aqbasketgroups`;
2270 CREATE TABLE `aqbasketgroups` (
2271 `id` int(11) NOT NULL auto_increment,
2272 `name` varchar(50) default NULL,
2273 `closed` tinyint(1) default NULL,
2274 `booksellerid` int(11) NOT NULL,
2276 KEY `booksellerid` (`booksellerid`),
2277 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2278 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2281 -- Table structure for table `aqbasket`
2284 DROP TABLE IF EXISTS `aqbasket`;
2285 CREATE TABLE `aqbasket` (
2286 `basketno` int(11) NOT NULL auto_increment,
2287 `basketname` varchar(50) default NULL,
2289 `booksellernote` mediumtext,
2290 `contractnumber` int(11),
2291 `creationdate` date default NULL,
2292 `closedate` date default NULL,
2293 `booksellerid` int(11) NOT NULL default 1,
2294 `authorisedby` varchar(10) default NULL,
2295 `booksellerinvoicenumber` mediumtext,
2296 `basketgroupid` int(11),
2297 PRIMARY KEY (`basketno`),
2298 KEY `booksellerid` (`booksellerid`),
2299 KEY `basketgroupid` (`basketgroupid`),
2300 KEY `contractnumber` (`contractnumber`),
2301 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2302 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2303 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2307 -- Table structure for table `aqbooksellers`
2310 DROP TABLE IF EXISTS `aqbooksellers`;
2311 CREATE TABLE `aqbooksellers` (
2312 `id` int(11) NOT NULL auto_increment,
2313 `name` mediumtext NOT NULL,
2314 `address1` mediumtext,
2315 `address2` mediumtext,
2316 `address3` mediumtext,
2317 `address4` mediumtext,
2318 `phone` varchar(30) default NULL,
2319 `accountnumber` mediumtext,
2320 `othersupplier` mediumtext,
2321 `currency` varchar(3) NOT NULL default '',
2322 `booksellerfax` mediumtext,
2324 `bookselleremail` mediumtext,
2325 `booksellerurl` mediumtext,
2326 `contact` varchar(100) default NULL,
2327 `postal` mediumtext,
2328 `url` varchar(255) default NULL,
2329 `contpos` varchar(100) default NULL,
2330 `contphone` varchar(100) default NULL,
2331 `contfax` varchar(100) default NULL,
2332 `contaltphone` varchar(100) default NULL,
2333 `contemail` varchar(100) default NULL,
2334 `contnotes` mediumtext,
2335 `active` tinyint(4) default NULL,
2336 `listprice` varchar(10) default NULL,
2337 `invoiceprice` varchar(10) default NULL,
2338 `gstreg` tinyint(4) default NULL,
2339 `listincgst` tinyint(4) default NULL,
2340 `invoiceincgst` tinyint(4) default NULL,
2341 `gstrate` decimal(6,4) default NULL,
2342 `discount` float(6,4) default NULL,
2343 `fax` varchar(50) default NULL,
2345 KEY `listprice` (`listprice`),
2346 KEY `invoiceprice` (`invoiceprice`),
2347 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2348 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2352 -- Table structure for table `aqbudgets`
2355 DROP TABLE IF EXISTS `aqbudgets`;
2356 CREATE TABLE `aqbudgets` (
2357 `budget_id` int(11) NOT NULL auto_increment,
2358 `budget_parent_id` int(11) default NULL,
2359 `budget_code` varchar(30) default NULL,
2360 `budget_name` varchar(80) default NULL,
2361 `budget_branchcode` varchar(10) default NULL,
2362 `budget_amount` decimal(28,6) NULL default '0.00',
2363 `budget_encumb` decimal(28,6) NULL default '0.00',
2364 `budget_expend` decimal(28,6) NULL default '0.00',
2365 `budget_notes` mediumtext,
2366 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2367 `budget_period_id` int(11) default NULL,
2368 `sort1_authcat` varchar(80) default NULL,
2369 `sort2_authcat` varchar(80) default NULL,
2370 `budget_owner_id` int(11) default NULL,
2371 `budget_permission` int(1) default '0',
2372 PRIMARY KEY (`budget_id`)
2373 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2377 -- Table structure for table `aqbudgetperiods`
2381 DROP TABLE IF EXISTS `aqbudgetperiods`;
2382 CREATE TABLE `aqbudgetperiods` (
2383 `budget_period_id` int(11) NOT NULL auto_increment,
2384 `budget_period_startdate` date NOT NULL,
2385 `budget_period_enddate` date NOT NULL,
2386 `budget_period_active` tinyint(1) default '0',
2387 `budget_period_description` mediumtext,
2388 `budget_period_total` decimal(28,6),
2389 `budget_period_locked` tinyint(1) default NULL,
2390 `sort1_authcat` varchar(10) default NULL,
2391 `sort2_authcat` varchar(10) default NULL,
2392 PRIMARY KEY (`budget_period_id`)
2393 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2396 -- Table structure for table `aqbudgets_planning`
2399 DROP TABLE IF EXISTS `aqbudgets_planning`;
2400 CREATE TABLE `aqbudgets_planning` (
2401 `plan_id` int(11) NOT NULL auto_increment,
2402 `budget_id` int(11) NOT NULL,
2403 `budget_period_id` int(11) NOT NULL,
2404 `estimated_amount` decimal(28,6) default NULL,
2405 `authcat` varchar(30) NOT NULL,
2406 `authvalue` varchar(30) NOT NULL,
2407 PRIMARY KEY (`plan_id`),
2408 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2409 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2412 -- Table structure for table 'aqcontract'
2415 DROP TABLE IF EXISTS `aqcontract`;
2416 CREATE TABLE `aqcontract` (
2417 `contractnumber` int(11) NOT NULL auto_increment,
2418 `contractstartdate` date default NULL,
2419 `contractenddate` date default NULL,
2420 `contractname` varchar(50) default NULL,
2421 `contractdescription` mediumtext,
2422 `booksellerid` int(11) not NULL,
2423 PRIMARY KEY (`contractnumber`),
2424 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2425 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2429 -- Table structure for table `aqorderdelivery`
2432 DROP TABLE IF EXISTS `aqorderdelivery`;
2433 CREATE TABLE `aqorderdelivery` (
2434 `ordernumber` date default NULL,
2435 `deliverynumber` smallint(6) NOT NULL default 0,
2436 `deliverydate` varchar(18) default NULL,
2437 `qtydelivered` smallint(6) default NULL,
2438 `deliverycomments` mediumtext
2439 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2442 -- Table structure for table `aqorders`
2445 DROP TABLE IF EXISTS `aqorders`;
2446 CREATE TABLE `aqorders` (
2447 `ordernumber` int(11) NOT NULL auto_increment,
2448 `biblionumber` int(11) default NULL,
2449 `entrydate` date default NULL,
2450 `quantity` smallint(6) default NULL,
2451 `currency` varchar(3) default NULL,
2452 `listprice` decimal(28,6) default NULL,
2453 `totalamount` decimal(28,6) default NULL,
2454 `datereceived` date default NULL,
2455 `booksellerinvoicenumber` mediumtext,
2456 `freight` decimal(28,6) default NULL,
2457 `unitprice` decimal(28,6) default NULL,
2458 `quantityreceived` smallint(6) default NULL,
2459 `cancelledby` varchar(10) default NULL,
2460 `datecancellationprinted` date default NULL,
2462 `supplierreference` mediumtext,
2463 `purchaseordernumber` mediumtext,
2464 `subscription` tinyint(1) default NULL,
2465 `serialid` varchar(30) default NULL,
2466 `basketno` int(11) default NULL,
2467 `biblioitemnumber` int(11) default NULL,
2468 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2469 `rrp` decimal(13,2) default NULL,
2470 `ecost` decimal(13,2) default NULL,
2471 `gst` decimal(13,2) default NULL,
2472 `budget_id` int(11) NOT NULL,
2473 `budgetgroup_id` int(11) NOT NULL,
2474 `budgetdate` date default NULL,
2475 `sort1` varchar(80) default NULL,
2476 `sort2` varchar(80) default NULL,
2477 `sort1_authcat` varchar(10) default NULL,
2478 `sort2_authcat` varchar(10) default NULL,
2479 `uncertainprice` tinyint(1),
2480 PRIMARY KEY (`ordernumber`),
2481 KEY `basketno` (`basketno`),
2482 KEY `biblionumber` (`biblionumber`),
2483 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2484 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2485 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2488 -- Table structure for table `aqorders_items`
2491 DROP TABLE IF EXISTS `aqorders_items`;
2492 CREATE TABLE `aqorders_items` (
2493 `ordernumber` int(11) NOT NULL,
2494 `itemnumber` int(11) NOT NULL,
2495 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2496 PRIMARY KEY (`itemnumber`),
2497 KEY `ordernumber` (`ordernumber`)
2498 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2501 -- Table structure for table `fieldmapping`
2504 DROP TABLE IF EXISTS `fieldmapping`;
2505 CREATE TABLE `fieldmapping` (
2506 `id` int(11) NOT NULL auto_increment,
2507 `field` varchar(255) NOT NULL,
2508 `frameworkcode` char(4) NOT NULL default '',
2509 `fieldcode` char(3) NOT NULL,
2510 `subfieldcode` char(1) NOT NULL,
2512 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2515 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2516 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2517 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2518 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2519 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2520 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2521 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2522 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;