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 `creator_batches`
1020 DROP TABLE IF EXISTS `creator_batches`;
1021 SET @saved_cs_client = @@character_set_client;
1022 SET character_set_client = utf8;
1023 CREATE TABLE `creator_batches` (
1024 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1025 `batch_id` int(10) NOT NULL DEFAULT '1',
1026 `item_number` int(11) DEFAULT NULL,
1027 `borrower_number` int(11) DEFAULT NULL,
1028 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1029 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1030 `creator` char(15) NOT NULL DEFAULT 'Labels',
1031 PRIMARY KEY (`label_id`) USING BTREE,
1032 KEY `branch_fk_constraint` (`branch_code`),
1033 KEY `item_fk_constraint` (`item_number`),
1034 KEY `borrower_fk_constraint` (`borrower_number`),
1035 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1036 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1037 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1038 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1041 -- Table structure for table `creator_images`
1044 DROP TABLE IF EXISTS `creator_images`;
1045 SET @saved_cs_client = @@character_set_client;
1046 SET character_set_client = utf8;
1047 CREATE TABLE `creator_images` (
1048 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1049 `imagefile` mediumblob,
1050 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1051 PRIMARY KEY (`image_id`) USING BTREE,
1052 UNIQUE KEY `image_name_index` (`image_name`)
1053 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1056 -- Table structure for table `creator_layouts`
1059 DROP TABLE IF EXISTS `creator_layouts`;
1060 SET @saved_cs_client = @@character_set_client;
1061 SET character_set_client = utf8;
1062 CREATE TABLE `creator_layouts` (
1063 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1064 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1065 `start_label` int(2) NOT NULL DEFAULT '1',
1066 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1067 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1068 `guidebox` int(1) DEFAULT '0',
1069 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1070 `font_size` int(4) NOT NULL DEFAULT '10',
1071 `units` char(20) NOT NULL DEFAULT 'POINT',
1072 `callnum_split` int(1) DEFAULT '0',
1073 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1074 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1075 `layout_xml` text NOT NULL,
1076 `creator` char(15) NOT NULL DEFAULT 'Labels',
1077 PRIMARY KEY (`layout_id`) USING BTREE
1078 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1081 -- Table structure for table `creator_templates`
1084 DROP TABLE IF EXISTS `creator_templates`;
1085 SET @saved_cs_client = @@character_set_client;
1086 SET character_set_client = utf8;
1087 CREATE TABLE `creator_templates` (
1088 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1089 `profile_id` int(4) DEFAULT NULL,
1090 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1091 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1092 `page_width` float NOT NULL DEFAULT '0',
1093 `page_height` float NOT NULL DEFAULT '0',
1094 `label_width` float NOT NULL DEFAULT '0',
1095 `label_height` float NOT NULL DEFAULT '0',
1096 `top_text_margin` float NOT NULL DEFAULT '0',
1097 `left_text_margin` float NOT NULL DEFAULT '0',
1098 `top_margin` float NOT NULL DEFAULT '0',
1099 `left_margin` float NOT NULL DEFAULT '0',
1100 `cols` int(2) NOT NULL DEFAULT '0',
1101 `rows` int(2) NOT NULL DEFAULT '0',
1102 `col_gap` float NOT NULL DEFAULT '0',
1103 `row_gap` float NOT NULL DEFAULT '0',
1104 `units` char(20) NOT NULL DEFAULT 'POINT',
1105 `creator` char(15) NOT NULL DEFAULT 'Labels',
1106 PRIMARY KEY (`template_id`),
1107 KEY `template_profile_fk_constraint` (`profile_id`)
1108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1111 -- Table structure for table `letter`
1114 DROP TABLE IF EXISTS `letter`;
1115 CREATE TABLE `letter` (
1116 `module` varchar(20) NOT NULL default '',
1117 `code` varchar(20) NOT NULL default '',
1118 `name` varchar(100) NOT NULL default '',
1119 `title` varchar(200) NOT NULL default '',
1121 PRIMARY KEY (`module`,`code`)
1122 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1125 -- Table structure for table `marc_subfield_structure`
1128 DROP TABLE IF EXISTS `marc_subfield_structure`;
1129 CREATE TABLE `marc_subfield_structure` (
1130 `tagfield` varchar(3) NOT NULL default '',
1131 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1132 `liblibrarian` varchar(255) NOT NULL default '',
1133 `libopac` varchar(255) NOT NULL default '',
1134 `repeatable` tinyint(4) NOT NULL default 0,
1135 `mandatory` tinyint(4) NOT NULL default 0,
1136 `kohafield` varchar(40) default NULL,
1137 `tab` tinyint(1) default NULL,
1138 `authorised_value` varchar(20) default NULL,
1139 `authtypecode` varchar(20) default NULL,
1140 `value_builder` varchar(80) default NULL,
1141 `isurl` tinyint(1) default NULL,
1142 `hidden` tinyint(1) default NULL,
1143 `frameworkcode` varchar(4) NOT NULL default '',
1144 `seealso` varchar(1100) default NULL,
1145 `link` varchar(80) default NULL,
1146 `defaultvalue` text default NULL,
1147 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1148 KEY `kohafield_2` (`kohafield`),
1149 KEY `tab` (`frameworkcode`,`tab`),
1150 KEY `kohafield` (`frameworkcode`,`kohafield`)
1151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1154 -- Table structure for table `marc_tag_structure`
1157 DROP TABLE IF EXISTS `marc_tag_structure`;
1158 CREATE TABLE `marc_tag_structure` (
1159 `tagfield` varchar(3) NOT NULL default '',
1160 `liblibrarian` varchar(255) NOT NULL default '',
1161 `libopac` varchar(255) NOT NULL default '',
1162 `repeatable` tinyint(4) NOT NULL default 0,
1163 `mandatory` tinyint(4) NOT NULL default 0,
1164 `authorised_value` varchar(10) default NULL,
1165 `frameworkcode` varchar(4) NOT NULL default '',
1166 PRIMARY KEY (`frameworkcode`,`tagfield`)
1167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1170 -- Table structure for table `marc_matchers`
1173 DROP TABLE IF EXISTS `marc_matchers`;
1174 CREATE TABLE `marc_matchers` (
1175 `matcher_id` int(11) NOT NULL auto_increment,
1176 `code` varchar(10) NOT NULL default '',
1177 `description` varchar(255) NOT NULL default '',
1178 `record_type` varchar(10) NOT NULL default 'biblio',
1179 `threshold` int(11) NOT NULL default 0,
1180 PRIMARY KEY (`matcher_id`),
1181 KEY `code` (`code`),
1182 KEY `record_type` (`record_type`)
1183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1186 -- Table structure for table `matchpoints`
1188 DROP TABLE IF EXISTS `matchpoints`;
1189 CREATE TABLE `matchpoints` (
1190 `matcher_id` int(11) NOT NULL,
1191 `matchpoint_id` int(11) NOT NULL auto_increment,
1192 `search_index` varchar(30) NOT NULL default '',
1193 `score` int(11) NOT NULL default 0,
1194 PRIMARY KEY (`matchpoint_id`),
1195 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1196 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1197 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1201 -- Table structure for table `matchpoint_components`
1203 DROP TABLE IF EXISTS `matchpoint_components`;
1204 CREATE TABLE `matchpoint_components` (
1205 `matchpoint_id` int(11) NOT NULL,
1206 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1207 sequence int(11) NOT NULL default 0,
1208 tag varchar(3) NOT NULL default '',
1209 subfields varchar(40) NOT NULL default '',
1210 offset int(4) NOT NULL default 0,
1211 length int(4) NOT NULL default 0,
1212 PRIMARY KEY (`matchpoint_component_id`),
1213 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1214 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1215 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1219 -- Table structure for table `matcher_component_norms`
1221 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1222 CREATE TABLE `matchpoint_component_norms` (
1223 `matchpoint_component_id` int(11) NOT NULL,
1224 `sequence` int(11) NOT NULL default 0,
1225 `norm_routine` varchar(50) NOT NULL default '',
1226 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1227 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1228 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1232 -- Table structure for table `matcher_matchpoints`
1234 DROP TABLE IF EXISTS `matcher_matchpoints`;
1235 CREATE TABLE `matcher_matchpoints` (
1236 `matcher_id` int(11) NOT NULL,
1237 `matchpoint_id` int(11) NOT NULL,
1238 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1239 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1240 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1241 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1245 -- Table structure for table `matchchecks`
1247 DROP TABLE IF EXISTS `matchchecks`;
1248 CREATE TABLE `matchchecks` (
1249 `matcher_id` int(11) NOT NULL,
1250 `matchcheck_id` int(11) NOT NULL auto_increment,
1251 `source_matchpoint_id` int(11) NOT NULL,
1252 `target_matchpoint_id` int(11) NOT NULL,
1253 PRIMARY KEY (`matchcheck_id`),
1254 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1255 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1256 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1257 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1258 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1259 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1263 -- Table structure for table `notifys`
1266 DROP TABLE IF EXISTS `notifys`;
1267 CREATE TABLE `notifys` (
1268 `notify_id` int(11) NOT NULL default 0,
1269 `borrowernumber` int(11) NOT NULL default 0,
1270 `itemnumber` int(11) NOT NULL default 0,
1271 `notify_date` date default NULL,
1272 `notify_send_date` date default NULL,
1273 `notify_level` int(1) NOT NULL default 0,
1274 `method` varchar(20) NOT NULL default ''
1275 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1278 -- Table structure for table `nozebra`
1281 DROP TABLE IF EXISTS `nozebra`;
1282 CREATE TABLE `nozebra` (
1283 `server` varchar(20) NOT NULL,
1284 `indexname` varchar(40) NOT NULL,
1285 `value` varchar(250) NOT NULL,
1286 `biblionumbers` longtext NOT NULL,
1287 KEY `indexname` (`server`,`indexname`),
1288 KEY `value` (`server`,`value`))
1289 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1292 -- Table structure for table `old_issues`
1295 DROP TABLE IF EXISTS `old_issues`;
1296 CREATE TABLE `old_issues` (
1297 `borrowernumber` int(11) default NULL,
1298 `itemnumber` int(11) default NULL,
1299 `date_due` date default NULL,
1300 `branchcode` varchar(10) default NULL,
1301 `issuingbranch` varchar(18) default NULL,
1302 `returndate` date default NULL,
1303 `lastreneweddate` date default NULL,
1304 `return` varchar(4) default NULL,
1305 `renewals` tinyint(4) default NULL,
1306 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1307 `issuedate` date default NULL,
1308 KEY `old_issuesborridx` (`borrowernumber`),
1309 KEY `old_issuesitemidx` (`itemnumber`),
1310 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1311 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1312 ON DELETE SET NULL ON UPDATE SET NULL,
1313 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1314 ON DELETE SET NULL ON UPDATE SET NULL
1315 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1318 -- Table structure for table `old_reserves`
1320 DROP TABLE IF EXISTS `old_reserves`;
1321 CREATE TABLE `old_reserves` (
1322 `borrowernumber` int(11) default NULL,
1323 `reservedate` date default NULL,
1324 `biblionumber` int(11) default NULL,
1325 `constrainttype` varchar(1) default NULL,
1326 `branchcode` varchar(10) default NULL,
1327 `notificationdate` date default NULL,
1328 `reminderdate` date default NULL,
1329 `cancellationdate` date default NULL,
1330 `reservenotes` mediumtext,
1331 `priority` smallint(6) default NULL,
1332 `found` varchar(1) default NULL,
1333 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1334 `itemnumber` int(11) default NULL,
1335 `waitingdate` date default NULL,
1336 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1337 KEY `old_reserves_biblionumber` (`biblionumber`),
1338 KEY `old_reserves_itemnumber` (`itemnumber`),
1339 KEY `old_reserves_branchcode` (`branchcode`),
1340 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1341 ON DELETE SET NULL ON UPDATE SET NULL,
1342 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1343 ON DELETE SET NULL ON UPDATE SET NULL,
1344 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1345 ON DELETE SET NULL ON UPDATE SET NULL
1346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1349 -- Table structure for table `opac_news`
1352 DROP TABLE IF EXISTS `opac_news`;
1353 CREATE TABLE `opac_news` (
1354 `idnew` int(10) unsigned NOT NULL auto_increment,
1355 `title` varchar(250) NOT NULL default '',
1356 `new` text NOT NULL,
1357 `lang` varchar(25) NOT NULL default '',
1358 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1359 `expirationdate` date default NULL,
1360 `number` int(11) default NULL,
1361 PRIMARY KEY (`idnew`)
1362 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1365 -- Table structure for table `overduerules`
1368 DROP TABLE IF EXISTS `overduerules`;
1369 CREATE TABLE `overduerules` (
1370 `branchcode` varchar(10) NOT NULL default '',
1371 `categorycode` varchar(10) NOT NULL default '',
1372 `delay1` int(4) default 0,
1373 `letter1` varchar(20) default NULL,
1374 `debarred1` varchar(1) default 0,
1375 `delay2` int(4) default 0,
1376 `debarred2` varchar(1) default 0,
1377 `letter2` varchar(20) default NULL,
1378 `delay3` int(4) default 0,
1379 `letter3` varchar(20) default NULL,
1380 `debarred3` int(1) default 0,
1381 PRIMARY KEY (`branchcode`,`categorycode`)
1382 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1385 -- Table structure for table `patroncards`
1388 DROP TABLE IF EXISTS `patroncards`;
1389 CREATE TABLE `patroncards` (
1390 `cardid` int(11) NOT NULL auto_increment,
1391 `batch_id` varchar(10) NOT NULL default '1',
1392 `borrowernumber` int(11) NOT NULL,
1393 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1394 PRIMARY KEY (`cardid`),
1395 KEY `patroncards_ibfk_1` (`borrowernumber`),
1396 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1397 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1400 -- Table structure for table `patronimage`
1403 DROP TABLE IF EXISTS `patronimage`;
1404 CREATE TABLE `patronimage` (
1405 `cardnumber` varchar(16) NOT NULL,
1406 `mimetype` varchar(15) NOT NULL,
1407 `imagefile` mediumblob NOT NULL,
1408 PRIMARY KEY (`cardnumber`),
1409 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1410 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1413 -- Table structure for table `printers`
1416 DROP TABLE IF EXISTS `printers`;
1417 CREATE TABLE `printers` (
1418 `printername` varchar(40) NOT NULL default '',
1419 `printqueue` varchar(20) default NULL,
1420 `printtype` varchar(20) default NULL,
1421 PRIMARY KEY (`printername`)
1422 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1425 -- Table structure for table `printers_profile`
1428 DROP TABLE IF EXISTS `printers_profile`;
1429 CREATE TABLE `printers_profile` (
1430 `profile_id` int(4) NOT NULL auto_increment,
1431 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1432 `template_id` int(4) NOT NULL default '0',
1433 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1434 `offset_horz` float NOT NULL default '0',
1435 `offset_vert` float NOT NULL default '0',
1436 `creep_horz` float NOT NULL default '0',
1437 `creep_vert` float NOT NULL default '0',
1438 `units` char(20) NOT NULL default 'POINT',
1439 `creator` char(15) NOT NULL DEFAULT 'Labels',
1440 PRIMARY KEY (`profile_id`),
1441 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`) USING BTREE
1442 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1445 -- Table structure for table `repeatable_holidays`
1448 DROP TABLE IF EXISTS `repeatable_holidays`;
1449 CREATE TABLE `repeatable_holidays` (
1450 `id` int(11) NOT NULL auto_increment,
1451 `branchcode` varchar(10) NOT NULL default '',
1452 `weekday` smallint(6) default NULL,
1453 `day` smallint(6) default NULL,
1454 `month` smallint(6) default NULL,
1455 `title` varchar(50) NOT NULL default '',
1456 `description` text NOT NULL,
1458 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1461 -- Table structure for table `reports_dictionary`
1464 DROP TABLE IF EXISTS `reports_dictionary`;
1465 CREATE TABLE reports_dictionary (
1466 `id` int(11) NOT NULL auto_increment,
1467 `name` varchar(255) default NULL,
1469 `date_created` datetime default NULL,
1470 `date_modified` datetime default NULL,
1472 `area` int(11) default NULL,
1474 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1477 -- Table structure for table `reserveconstraints`
1480 DROP TABLE IF EXISTS `reserveconstraints`;
1481 CREATE TABLE `reserveconstraints` (
1482 `borrowernumber` int(11) NOT NULL default 0,
1483 `reservedate` date default NULL,
1484 `biblionumber` int(11) NOT NULL default 0,
1485 `biblioitemnumber` int(11) default NULL,
1486 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1487 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1490 -- Table structure for table `reserves`
1493 DROP TABLE IF EXISTS `reserves`;
1494 CREATE TABLE `reserves` (
1495 `borrowernumber` int(11) NOT NULL default 0,
1496 `reservedate` date default NULL,
1497 `biblionumber` int(11) NOT NULL default 0,
1498 `constrainttype` varchar(1) default NULL,
1499 `branchcode` varchar(10) default NULL,
1500 `notificationdate` date default NULL,
1501 `reminderdate` date default NULL,
1502 `cancellationdate` date default NULL,
1503 `reservenotes` mediumtext,
1504 `priority` smallint(6) default NULL,
1505 `found` varchar(1) default NULL,
1506 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1507 `itemnumber` int(11) default NULL,
1508 `waitingdate` date default NULL,
1509 KEY `borrowernumber` (`borrowernumber`),
1510 KEY `biblionumber` (`biblionumber`),
1511 KEY `itemnumber` (`itemnumber`),
1512 KEY `branchcode` (`branchcode`),
1513 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1514 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1515 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1516 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1517 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1520 -- Table structure for table `reviews`
1523 DROP TABLE IF EXISTS `reviews`;
1524 CREATE TABLE `reviews` (
1525 `reviewid` int(11) NOT NULL auto_increment,
1526 `borrowernumber` int(11) default NULL,
1527 `biblionumber` int(11) default NULL,
1529 `approved` tinyint(4) default NULL,
1530 `datereviewed` datetime default NULL,
1531 PRIMARY KEY (`reviewid`)
1532 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1535 -- Table structure for table `roadtype`
1538 DROP TABLE IF EXISTS `roadtype`;
1539 CREATE TABLE `roadtype` (
1540 `roadtypeid` int(11) NOT NULL auto_increment,
1541 `road_type` varchar(100) NOT NULL default '',
1542 PRIMARY KEY (`roadtypeid`)
1543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1546 -- Table structure for table `saved_sql`
1549 DROP TABLE IF EXISTS `saved_sql`;
1550 CREATE TABLE saved_sql (
1551 `id` int(11) NOT NULL auto_increment,
1552 `borrowernumber` int(11) default NULL,
1553 `date_created` datetime default NULL,
1554 `last_modified` datetime default NULL,
1556 `last_run` datetime default NULL,
1557 `report_name` varchar(255) default NULL,
1558 `type` varchar(255) default NULL,
1561 KEY boridx (`borrowernumber`)
1562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1566 -- Table structure for `saved_reports`
1569 DROP TABLE IF EXISTS `saved_reports`;
1570 CREATE TABLE saved_reports (
1571 `id` int(11) NOT NULL auto_increment,
1572 `report_id` int(11) default NULL,
1574 `date_run` datetime default NULL,
1576 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1580 -- Table structure for table `search_history`
1583 DROP TABLE IF EXISTS `search_history`;
1584 CREATE TABLE IF NOT EXISTS `search_history` (
1585 `userid` int(11) NOT NULL,
1586 `sessionid` varchar(32) NOT NULL,
1587 `query_desc` varchar(255) NOT NULL,
1588 `query_cgi` varchar(255) NOT NULL,
1589 `total` int(11) NOT NULL,
1590 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1591 KEY `userid` (`userid`),
1592 KEY `sessionid` (`sessionid`)
1593 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1597 -- Table structure for table `serial`
1600 DROP TABLE IF EXISTS `serial`;
1601 CREATE TABLE `serial` (
1602 `serialid` int(11) NOT NULL auto_increment,
1603 `biblionumber` varchar(100) NOT NULL default '',
1604 `subscriptionid` varchar(100) NOT NULL default '',
1605 `serialseq` varchar(100) NOT NULL default '',
1606 `status` tinyint(4) NOT NULL default 0,
1607 `planneddate` date default NULL,
1609 `publisheddate` date default NULL,
1610 `itemnumber` text default NULL,
1611 `claimdate` date default NULL,
1612 `routingnotes` text,
1613 PRIMARY KEY (`serialid`)
1614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1617 -- Table structure for table `sessions`
1620 DROP TABLE IF EXISTS sessions;
1621 CREATE TABLE sessions (
1622 `id` varchar(32) NOT NULL,
1623 `a_session` text NOT NULL,
1625 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1628 -- Table structure for table `special_holidays`
1631 DROP TABLE IF EXISTS `special_holidays`;
1632 CREATE TABLE `special_holidays` (
1633 `id` int(11) NOT NULL auto_increment,
1634 `branchcode` varchar(10) NOT NULL default '',
1635 `day` smallint(6) NOT NULL default 0,
1636 `month` smallint(6) NOT NULL default 0,
1637 `year` smallint(6) NOT NULL default 0,
1638 `isexception` smallint(1) NOT NULL default 1,
1639 `title` varchar(50) NOT NULL default '',
1640 `description` text NOT NULL,
1642 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1645 -- Table structure for table `statistics`
1648 DROP TABLE IF EXISTS `statistics`;
1649 CREATE TABLE `statistics` (
1650 `datetime` datetime default NULL,
1651 `branch` varchar(10) default NULL,
1652 `proccode` varchar(4) default NULL,
1653 `value` double(16,4) default NULL,
1654 `type` varchar(16) default NULL,
1656 `usercode` varchar(10) default NULL,
1657 `itemnumber` int(11) default NULL,
1658 `itemtype` varchar(10) default NULL,
1659 `borrowernumber` int(11) default NULL,
1660 `associatedborrower` int(11) default NULL,
1661 KEY `timeidx` (`datetime`)
1662 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1665 -- Table structure for table `stopwords`
1668 DROP TABLE IF EXISTS `stopwords`;
1669 CREATE TABLE `stopwords` (
1670 `word` varchar(255) default NULL
1671 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1674 -- Table structure for table `subscription`
1677 DROP TABLE IF EXISTS `subscription`;
1678 CREATE TABLE `subscription` (
1679 `biblionumber` int(11) NOT NULL default 0,
1680 `subscriptionid` int(11) NOT NULL auto_increment,
1681 `librarian` varchar(100) default '',
1682 `startdate` date default NULL,
1683 `aqbooksellerid` int(11) default 0,
1684 `cost` int(11) default 0,
1685 `aqbudgetid` int(11) default 0,
1686 `weeklength` int(11) default 0,
1687 `monthlength` int(11) default 0,
1688 `numberlength` int(11) default 0,
1689 `periodicity` tinyint(4) default 0,
1690 `dow` varchar(100) default '',
1691 `numberingmethod` varchar(100) default '',
1693 `status` varchar(100) NOT NULL default '',
1694 `add1` int(11) default 0,
1695 `every1` int(11) default 0,
1696 `whenmorethan1` int(11) default 0,
1697 `setto1` int(11) default NULL,
1698 `lastvalue1` int(11) default NULL,
1699 `add2` int(11) default 0,
1700 `every2` int(11) default 0,
1701 `whenmorethan2` int(11) default 0,
1702 `setto2` int(11) default NULL,
1703 `lastvalue2` int(11) default NULL,
1704 `add3` int(11) default 0,
1705 `every3` int(11) default 0,
1706 `innerloop1` int(11) default 0,
1707 `innerloop2` int(11) default 0,
1708 `innerloop3` int(11) default 0,
1709 `whenmorethan3` int(11) default 0,
1710 `setto3` int(11) default NULL,
1711 `lastvalue3` int(11) default NULL,
1712 `issuesatonce` tinyint(3) NOT NULL default 1,
1713 `firstacquidate` date default NULL,
1714 `manualhistory` tinyint(1) NOT NULL default 0,
1715 `irregularity` text,
1716 `letter` varchar(20) default NULL,
1717 `numberpattern` tinyint(3) default 0,
1718 `distributedto` text,
1719 `internalnotes` longtext,
1721 `location` varchar(80) NULL default '',
1722 `branchcode` varchar(10) NOT NULL default '',
1723 `hemisphere` tinyint(3) default 0,
1724 `lastbranch` varchar(10),
1725 `serialsadditems` tinyint(1) NOT NULL default '0',
1726 `staffdisplaycount` VARCHAR(10) NULL,
1727 `opacdisplaycount` VARCHAR(10) NULL,
1728 `graceperiod` int(11) NOT NULL default '0',
1729 PRIMARY KEY (`subscriptionid`)
1730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1733 -- Table structure for table `subscriptionhistory`
1736 DROP TABLE IF EXISTS `subscriptionhistory`;
1737 CREATE TABLE `subscriptionhistory` (
1738 `biblionumber` int(11) NOT NULL default 0,
1739 `subscriptionid` int(11) NOT NULL default 0,
1740 `histstartdate` date default NULL,
1741 `histenddate` date default NULL,
1742 `missinglist` longtext NOT NULL,
1743 `recievedlist` longtext NOT NULL,
1744 `opacnote` varchar(150) NOT NULL default '',
1745 `librariannote` varchar(150) NOT NULL default '',
1746 PRIMARY KEY (`subscriptionid`),
1747 KEY `biblionumber` (`biblionumber`)
1748 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1751 -- Table structure for table `subscriptionroutinglist`
1754 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1755 CREATE TABLE `subscriptionroutinglist` (
1756 `routingid` int(11) NOT NULL auto_increment,
1757 `borrowernumber` int(11) default NULL,
1758 `ranking` int(11) default NULL,
1759 `subscriptionid` int(11) default NULL,
1760 PRIMARY KEY (`routingid`)
1761 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1764 -- Table structure for table `suggestions`
1767 DROP TABLE IF EXISTS `suggestions`;
1768 CREATE TABLE `suggestions` (
1769 `suggestionid` int(8) NOT NULL auto_increment,
1770 `suggestedby` int(11) NOT NULL default 0,
1771 `suggesteddate` date NOT NULL default 0,
1772 `managedby` int(11) default NULL,
1773 `manageddate` date default NULL,
1774 acceptedby INT(11) default NULL,
1775 accepteddate date default NULL,
1776 rejectedby INT(11) default NULL,
1777 rejecteddate date default NULL,
1778 `STATUS` varchar(10) NOT NULL default '',
1780 `author` varchar(80) default NULL,
1781 `title` varchar(80) default NULL,
1782 `copyrightdate` smallint(6) default NULL,
1783 `publishercode` varchar(255) default NULL,
1784 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1785 `volumedesc` varchar(255) default NULL,
1786 `publicationyear` smallint(6) default 0,
1787 `place` varchar(255) default NULL,
1788 `isbn` varchar(30) default NULL,
1789 `mailoverseeing` smallint(1) default 0,
1790 `biblionumber` int(11) default NULL,
1793 branchcode VARCHAR(10) default NULL,
1794 collectiontitle text default NULL,
1795 itemtype VARCHAR(30) default NULL,
1796 PRIMARY KEY (`suggestionid`),
1797 KEY `suggestedby` (`suggestedby`),
1798 KEY `managedby` (`managedby`)
1799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1802 -- Table structure for table `systempreferences`
1805 DROP TABLE IF EXISTS `systempreferences`;
1806 CREATE TABLE `systempreferences` (
1807 `variable` varchar(50) NOT NULL default '',
1809 `options` mediumtext,
1811 `type` varchar(20) default NULL,
1812 PRIMARY KEY (`variable`)
1813 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1816 -- Table structure for table `tags`
1819 DROP TABLE IF EXISTS `tags`;
1820 CREATE TABLE `tags` (
1821 `entry` varchar(255) NOT NULL default '',
1822 `weight` bigint(20) NOT NULL default 0,
1823 PRIMARY KEY (`entry`)
1824 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1827 -- Table structure for table `tags_all`
1830 DROP TABLE IF EXISTS `tags_all`;
1831 CREATE TABLE `tags_all` (
1832 `tag_id` int(11) NOT NULL auto_increment,
1833 `borrowernumber` int(11) NOT NULL,
1834 `biblionumber` int(11) NOT NULL,
1835 `term` varchar(255) NOT NULL,
1836 `language` int(4) default NULL,
1837 `date_created` datetime NOT NULL,
1838 PRIMARY KEY (`tag_id`),
1839 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1840 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1841 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1842 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1843 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1844 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1845 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1848 -- Table structure for table `tags_approval`
1851 DROP TABLE IF EXISTS `tags_approval`;
1852 CREATE TABLE `tags_approval` (
1853 `term` varchar(255) NOT NULL,
1854 `approved` int(1) NOT NULL default '0',
1855 `date_approved` datetime default NULL,
1856 `approved_by` int(11) default NULL,
1857 `weight_total` int(9) NOT NULL default '1',
1858 PRIMARY KEY (`term`),
1859 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1860 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1861 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1862 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1865 -- Table structure for table `tags_index`
1868 DROP TABLE IF EXISTS `tags_index`;
1869 CREATE TABLE `tags_index` (
1870 `term` varchar(255) NOT NULL,
1871 `biblionumber` int(11) NOT NULL,
1872 `weight` int(9) NOT NULL default '1',
1873 PRIMARY KEY (`term`,`biblionumber`),
1874 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1875 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1876 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1877 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1878 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1879 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1882 -- Table structure for table `userflags`
1885 DROP TABLE IF EXISTS `userflags`;
1886 CREATE TABLE `userflags` (
1887 `bit` int(11) NOT NULL default 0,
1888 `flag` varchar(30) default NULL,
1889 `flagdesc` varchar(255) default NULL,
1890 `defaulton` int(11) default NULL,
1892 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1895 -- Table structure for table `virtualshelves`
1898 DROP TABLE IF EXISTS `virtualshelves`;
1899 CREATE TABLE `virtualshelves` (
1900 `shelfnumber` int(11) NOT NULL auto_increment,
1901 `shelfname` varchar(255) default NULL,
1902 `owner` varchar(80) default NULL,
1903 `category` varchar(1) default NULL,
1904 `sortfield` varchar(16) default NULL,
1905 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1906 PRIMARY KEY (`shelfnumber`)
1907 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1910 -- Table structure for table `virtualshelfcontents`
1913 DROP TABLE IF EXISTS `virtualshelfcontents`;
1914 CREATE TABLE `virtualshelfcontents` (
1915 `shelfnumber` int(11) NOT NULL default 0,
1916 `biblionumber` int(11) NOT NULL default 0,
1917 `flags` int(11) default NULL,
1918 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1919 KEY `shelfnumber` (`shelfnumber`),
1920 KEY `biblionumber` (`biblionumber`),
1921 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1922 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1923 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1926 -- Table structure for table `z3950servers`
1929 DROP TABLE IF EXISTS `z3950servers`;
1930 CREATE TABLE `z3950servers` (
1931 `host` varchar(255) default NULL,
1932 `port` int(11) default NULL,
1933 `db` varchar(255) default NULL,
1934 `userid` varchar(255) default NULL,
1935 `password` varchar(255) default NULL,
1937 `id` int(11) NOT NULL auto_increment,
1938 `checked` smallint(6) default NULL,
1939 `rank` int(11) default NULL,
1940 `syntax` varchar(80) default NULL,
1942 `position` enum('primary','secondary','') NOT NULL default 'primary',
1943 `type` enum('zed','opensearch') NOT NULL default 'zed',
1944 `encoding` text default NULL,
1945 `description` text NOT NULL,
1947 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1950 -- Table structure for table `zebraqueue`
1953 DROP TABLE IF EXISTS `zebraqueue`;
1954 CREATE TABLE `zebraqueue` (
1955 `id` int(11) NOT NULL auto_increment,
1956 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1957 `operation` char(20) NOT NULL default '',
1958 `server` char(20) NOT NULL default '',
1959 `done` int(11) NOT NULL default '0',
1960 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1962 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1963 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1965 DROP TABLE IF EXISTS `services_throttle`;
1966 CREATE TABLE `services_throttle` (
1967 `service_type` varchar(10) NOT NULL default '',
1968 `service_count` varchar(45) default NULL,
1969 PRIMARY KEY (`service_type`)
1970 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1972 -- http://www.w3.org/International/articles/language-tags/
1975 DROP TABLE IF EXISTS language_subtag_registry;
1976 CREATE TABLE language_subtag_registry (
1978 type varchar(25), -- language-script-region-variant-extension-privateuse
1979 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1981 id int(11) NOT NULL auto_increment,
1983 KEY `subtag` (`subtag`)
1984 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1986 -- TODO: add suppress_scripts
1987 -- this maps three letter codes defined in iso639.2 back to their
1988 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1989 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1990 CREATE TABLE language_rfc4646_to_iso639 (
1991 rfc4646_subtag varchar(25),
1992 iso639_2_code varchar(25),
1993 id int(11) NOT NULL auto_increment,
1995 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1996 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1998 DROP TABLE IF EXISTS language_descriptions;
1999 CREATE TABLE language_descriptions (
2003 description varchar(255),
2004 id int(11) NOT NULL auto_increment,
2007 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2009 -- bi-directional support, keyed by script subcode
2010 DROP TABLE IF EXISTS language_script_bidi;
2011 CREATE TABLE language_script_bidi (
2012 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2013 bidi varchar(3), -- rtl ltr
2014 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2015 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2017 -- TODO: need to map language subtags to script subtags for detection
2018 -- of bidi when script is not specified (like ar, he)
2019 DROP TABLE IF EXISTS language_script_mapping;
2020 CREATE TABLE language_script_mapping (
2021 language_subtag varchar(25),
2022 script_subtag varchar(25),
2023 KEY `language_subtag` (`language_subtag`)
2024 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2026 DROP TABLE IF EXISTS `permissions`;
2027 CREATE TABLE `permissions` (
2028 `module_bit` int(11) NOT NULL DEFAULT 0,
2029 `code` varchar(64) DEFAULT NULL,
2030 `description` varchar(255) DEFAULT NULL,
2031 PRIMARY KEY (`module_bit`, `code`),
2032 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2033 ON DELETE CASCADE ON UPDATE CASCADE
2034 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2036 DROP TABLE IF EXISTS `serialitems`;
2037 CREATE TABLE `serialitems` (
2038 `itemnumber` int(11) NOT NULL,
2039 `serialid` int(11) NOT NULL,
2040 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2041 KEY `serialitems_sfk_1` (`serialid`),
2042 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2043 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2045 DROP TABLE IF EXISTS `user_permissions`;
2046 CREATE TABLE `user_permissions` (
2047 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2048 `module_bit` int(11) NOT NULL DEFAULT 0,
2049 `code` varchar(64) DEFAULT NULL,
2050 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2051 ON DELETE CASCADE ON UPDATE CASCADE,
2052 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2053 ON DELETE CASCADE ON UPDATE CASCADE
2054 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2057 -- Table structure for table `tmp_holdsqueue`
2060 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2061 CREATE TABLE `tmp_holdsqueue` (
2062 `biblionumber` int(11) default NULL,
2063 `itemnumber` int(11) default NULL,
2064 `barcode` varchar(20) default NULL,
2065 `surname` mediumtext NOT NULL,
2068 `borrowernumber` int(11) NOT NULL,
2069 `cardnumber` varchar(16) default NULL,
2070 `reservedate` date default NULL,
2072 `itemcallnumber` varchar(255) default NULL,
2073 `holdingbranch` varchar(10) default NULL,
2074 `pickbranch` varchar(10) default NULL,
2076 `item_level_request` tinyint(4) NOT NULL default 0
2077 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2080 -- Table structure for table `message_queue`
2083 DROP TABLE IF EXISTS `message_queue`;
2084 CREATE TABLE `message_queue` (
2085 `message_id` int(11) NOT NULL auto_increment,
2086 `borrowernumber` int(11) default NULL,
2089 `metadata` text DEFAULT NULL,
2090 `letter_code` varchar(64) DEFAULT NULL,
2091 `message_transport_type` varchar(20) NOT NULL,
2092 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2093 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2094 `to_address` mediumtext,
2095 `from_address` mediumtext,
2096 `content_type` text,
2097 KEY `message_id` (`message_id`),
2098 KEY `borrowernumber` (`borrowernumber`),
2099 KEY `message_transport_type` (`message_transport_type`),
2100 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2101 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2105 -- Table structure for table `message_transport_types`
2108 DROP TABLE IF EXISTS `message_transport_types`;
2109 CREATE TABLE `message_transport_types` (
2110 `message_transport_type` varchar(20) NOT NULL,
2111 PRIMARY KEY (`message_transport_type`)
2112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2115 -- Table structure for table `message_attributes`
2118 DROP TABLE IF EXISTS `message_attributes`;
2119 CREATE TABLE `message_attributes` (
2120 `message_attribute_id` int(11) NOT NULL auto_increment,
2121 `message_name` varchar(20) NOT NULL default '',
2122 `takes_days` tinyint(1) NOT NULL default '0',
2123 PRIMARY KEY (`message_attribute_id`),
2124 UNIQUE KEY `message_name` (`message_name`)
2125 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2128 -- Table structure for table `message_transports`
2131 DROP TABLE IF EXISTS `message_transports`;
2132 CREATE TABLE `message_transports` (
2133 `message_attribute_id` int(11) NOT NULL,
2134 `message_transport_type` varchar(20) NOT NULL,
2135 `is_digest` tinyint(1) NOT NULL default '0',
2136 `letter_module` varchar(20) NOT NULL default '',
2137 `letter_code` varchar(20) NOT NULL default '',
2138 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2139 KEY `message_transport_type` (`message_transport_type`),
2140 KEY `letter_module` (`letter_module`,`letter_code`),
2141 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2142 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2143 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2144 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2147 -- Table structure for table `borrower_message_preferences`
2150 DROP TABLE IF EXISTS `borrower_message_preferences`;
2151 CREATE TABLE `borrower_message_preferences` (
2152 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2153 `borrowernumber` int(11) default NULL,
2154 `categorycode` varchar(10) default NULL,
2155 `message_attribute_id` int(11) default '0',
2156 `days_in_advance` int(11) default '0',
2157 `wants_digest` tinyint(1) NOT NULL default '0',
2158 PRIMARY KEY (`borrower_message_preference_id`),
2159 KEY `borrowernumber` (`borrowernumber`),
2160 KEY `categorycode` (`categorycode`),
2161 KEY `message_attribute_id` (`message_attribute_id`),
2162 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2163 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2164 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2168 -- Table structure for table `borrower_message_transport_preferences`
2171 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2172 CREATE TABLE `borrower_message_transport_preferences` (
2173 `borrower_message_preference_id` int(11) NOT NULL default '0',
2174 `message_transport_type` varchar(20) NOT NULL default '0',
2175 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2176 KEY `message_transport_type` (`message_transport_type`),
2177 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,
2178 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
2179 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2182 -- Table structure for the table branch_transfer_limits
2185 DROP TABLE IF EXISTS `branch_transfer_limits`;
2186 CREATE TABLE branch_transfer_limits (
2187 limitId int(8) NOT NULL auto_increment,
2188 toBranch varchar(10) NOT NULL,
2189 fromBranch varchar(10) NOT NULL,
2190 itemtype varchar(10) NULL,
2191 ccode varchar(10) NULL,
2192 PRIMARY KEY (limitId)
2193 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2196 -- Table structure for table `item_circulation_alert_preferences`
2199 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2200 CREATE TABLE `item_circulation_alert_preferences` (
2201 `id` int(11) NOT NULL auto_increment,
2202 `branchcode` varchar(10) NOT NULL,
2203 `categorycode` varchar(10) NOT NULL,
2204 `item_type` varchar(10) NOT NULL,
2205 `notification` varchar(16) NOT NULL,
2207 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2211 -- Table structure for table `messages`
2214 CREATE TABLE `messages` (
2215 `message_id` int(11) NOT NULL auto_increment,
2216 `borrowernumber` int(11) NOT NULL,
2217 `branchcode` varchar(4) default NULL,
2218 `message_type` varchar(1) NOT NULL,
2219 `message` text NOT NULL,
2220 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2221 PRIMARY KEY (`message_id`)
2222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2225 -- Table structure for table `accountlines`
2228 DROP TABLE IF EXISTS `accountlines`;
2229 CREATE TABLE `accountlines` (
2230 `borrowernumber` int(11) NOT NULL default 0,
2231 `accountno` smallint(6) NOT NULL default 0,
2232 `itemnumber` int(11) default NULL,
2233 `date` date default NULL,
2234 `amount` decimal(28,6) default NULL,
2235 `description` mediumtext,
2236 `dispute` mediumtext,
2237 `accounttype` varchar(5) default NULL,
2238 `amountoutstanding` decimal(28,6) default NULL,
2239 `lastincrement` decimal(28,6) default NULL,
2240 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2241 `notify_id` int(11) NOT NULL default 0,
2242 `notify_level` int(2) NOT NULL default 0,
2243 KEY `acctsborridx` (`borrowernumber`),
2244 KEY `timeidx` (`timestamp`),
2245 KEY `itemnumber` (`itemnumber`),
2246 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2247 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2248 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2251 -- Table structure for table `accountoffsets`
2254 DROP TABLE IF EXISTS `accountoffsets`;
2255 CREATE TABLE `accountoffsets` (
2256 `borrowernumber` int(11) NOT NULL default 0,
2257 `accountno` smallint(6) NOT NULL default 0,
2258 `offsetaccount` smallint(6) NOT NULL default 0,
2259 `offsetamount` decimal(28,6) default NULL,
2260 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2261 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2262 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2265 -- Table structure for table `action_logs`
2268 DROP TABLE IF EXISTS `action_logs`;
2269 CREATE TABLE `action_logs` (
2270 `action_id` int(11) NOT NULL auto_increment,
2271 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2272 `user` int(11) NOT NULL default 0,
2275 `object` int(11) default NULL,
2277 PRIMARY KEY (`action_id`),
2278 KEY (`timestamp`,`user`)
2279 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2282 -- Table structure for table `alert`
2285 DROP TABLE IF EXISTS `alert`;
2286 CREATE TABLE `alert` (
2287 `alertid` int(11) NOT NULL auto_increment,
2288 `borrowernumber` int(11) NOT NULL default 0,
2289 `type` varchar(10) NOT NULL default '',
2290 `externalid` varchar(20) NOT NULL default '',
2291 PRIMARY KEY (`alertid`),
2292 KEY `borrowernumber` (`borrowernumber`),
2293 KEY `type` (`type`,`externalid`)
2294 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2297 -- Table structure for table `aqbasketgroups`
2300 DROP TABLE IF EXISTS `aqbasketgroups`;
2301 CREATE TABLE `aqbasketgroups` (
2302 `id` int(11) NOT NULL auto_increment,
2303 `name` varchar(50) default NULL,
2304 `closed` tinyint(1) default NULL,
2305 `booksellerid` int(11) NOT NULL,
2307 KEY `booksellerid` (`booksellerid`),
2308 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2309 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2312 -- Table structure for table `aqbasket`
2315 DROP TABLE IF EXISTS `aqbasket`;
2316 CREATE TABLE `aqbasket` (
2317 `basketno` int(11) NOT NULL auto_increment,
2318 `basketname` varchar(50) default NULL,
2320 `booksellernote` mediumtext,
2321 `contractnumber` int(11),
2322 `creationdate` date default NULL,
2323 `closedate` date default NULL,
2324 `booksellerid` int(11) NOT NULL default 1,
2325 `authorisedby` varchar(10) default NULL,
2326 `booksellerinvoicenumber` mediumtext,
2327 `basketgroupid` int(11),
2328 PRIMARY KEY (`basketno`),
2329 KEY `booksellerid` (`booksellerid`),
2330 KEY `basketgroupid` (`basketgroupid`),
2331 KEY `contractnumber` (`contractnumber`),
2332 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2333 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2334 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2335 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2338 -- Table structure for table `aqbooksellers`
2341 DROP TABLE IF EXISTS `aqbooksellers`;
2342 CREATE TABLE `aqbooksellers` (
2343 `id` int(11) NOT NULL auto_increment,
2344 `name` mediumtext NOT NULL,
2345 `address1` mediumtext,
2346 `address2` mediumtext,
2347 `address3` mediumtext,
2348 `address4` mediumtext,
2349 `phone` varchar(30) default NULL,
2350 `accountnumber` mediumtext,
2351 `othersupplier` mediumtext,
2352 `currency` varchar(3) NOT NULL default '',
2353 `booksellerfax` mediumtext,
2355 `bookselleremail` mediumtext,
2356 `booksellerurl` mediumtext,
2357 `contact` varchar(100) default NULL,
2358 `postal` mediumtext,
2359 `url` varchar(255) default NULL,
2360 `contpos` varchar(100) default NULL,
2361 `contphone` varchar(100) default NULL,
2362 `contfax` varchar(100) default NULL,
2363 `contaltphone` varchar(100) default NULL,
2364 `contemail` varchar(100) default NULL,
2365 `contnotes` mediumtext,
2366 `active` tinyint(4) default NULL,
2367 `listprice` varchar(10) default NULL,
2368 `invoiceprice` varchar(10) default NULL,
2369 `gstreg` tinyint(4) default NULL,
2370 `listincgst` tinyint(4) default NULL,
2371 `invoiceincgst` tinyint(4) default NULL,
2372 `gstrate` decimal(6,4) default NULL,
2373 `discount` float(6,4) default NULL,
2374 `fax` varchar(50) default NULL,
2376 KEY `listprice` (`listprice`),
2377 KEY `invoiceprice` (`invoiceprice`),
2378 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2379 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2380 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2383 -- Table structure for table `aqbudgets`
2386 DROP TABLE IF EXISTS `aqbudgets`;
2387 CREATE TABLE `aqbudgets` (
2388 `budget_id` int(11) NOT NULL auto_increment,
2389 `budget_parent_id` int(11) default NULL,
2390 `budget_code` varchar(30) default NULL,
2391 `budget_name` varchar(80) default NULL,
2392 `budget_branchcode` varchar(10) default NULL,
2393 `budget_amount` decimal(28,6) NULL default '0.00',
2394 `budget_encumb` decimal(28,6) NULL default '0.00',
2395 `budget_expend` decimal(28,6) NULL default '0.00',
2396 `budget_notes` mediumtext,
2397 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2398 `budget_period_id` int(11) default NULL,
2399 `sort1_authcat` varchar(80) default NULL,
2400 `sort2_authcat` varchar(80) default NULL,
2401 `budget_owner_id` int(11) default NULL,
2402 `budget_permission` int(1) default '0',
2403 PRIMARY KEY (`budget_id`)
2404 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2408 -- Table structure for table `aqbudgetperiods`
2412 DROP TABLE IF EXISTS `aqbudgetperiods`;
2413 CREATE TABLE `aqbudgetperiods` (
2414 `budget_period_id` int(11) NOT NULL auto_increment,
2415 `budget_period_startdate` date NOT NULL,
2416 `budget_period_enddate` date NOT NULL,
2417 `budget_period_active` tinyint(1) default '0',
2418 `budget_period_description` mediumtext,
2419 `budget_period_total` decimal(28,6),
2420 `budget_period_locked` tinyint(1) default NULL,
2421 `sort1_authcat` varchar(10) default NULL,
2422 `sort2_authcat` varchar(10) default NULL,
2423 PRIMARY KEY (`budget_period_id`)
2424 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2427 -- Table structure for table `aqbudgets_planning`
2430 DROP TABLE IF EXISTS `aqbudgets_planning`;
2431 CREATE TABLE `aqbudgets_planning` (
2432 `plan_id` int(11) NOT NULL auto_increment,
2433 `budget_id` int(11) NOT NULL,
2434 `budget_period_id` int(11) NOT NULL,
2435 `estimated_amount` decimal(28,6) default NULL,
2436 `authcat` varchar(30) NOT NULL,
2437 `authvalue` varchar(30) NOT NULL,
2438 PRIMARY KEY (`plan_id`),
2439 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2440 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2443 -- Table structure for table 'aqcontract'
2446 DROP TABLE IF EXISTS `aqcontract`;
2447 CREATE TABLE `aqcontract` (
2448 `contractnumber` int(11) NOT NULL auto_increment,
2449 `contractstartdate` date default NULL,
2450 `contractenddate` date default NULL,
2451 `contractname` varchar(50) default NULL,
2452 `contractdescription` mediumtext,
2453 `booksellerid` int(11) not NULL,
2454 PRIMARY KEY (`contractnumber`),
2455 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2456 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2457 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2460 -- Table structure for table `aqorderdelivery`
2463 DROP TABLE IF EXISTS `aqorderdelivery`;
2464 CREATE TABLE `aqorderdelivery` (
2465 `ordernumber` date default NULL,
2466 `deliverynumber` smallint(6) NOT NULL default 0,
2467 `deliverydate` varchar(18) default NULL,
2468 `qtydelivered` smallint(6) default NULL,
2469 `deliverycomments` mediumtext
2470 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2473 -- Table structure for table `aqorders`
2476 DROP TABLE IF EXISTS `aqorders`;
2477 CREATE TABLE `aqorders` (
2478 `ordernumber` int(11) NOT NULL auto_increment,
2479 `biblionumber` int(11) default NULL,
2480 `entrydate` date default NULL,
2481 `quantity` smallint(6) default NULL,
2482 `currency` varchar(3) default NULL,
2483 `listprice` decimal(28,6) default NULL,
2484 `totalamount` decimal(28,6) default NULL,
2485 `datereceived` date default NULL,
2486 `booksellerinvoicenumber` mediumtext,
2487 `freight` decimal(28,6) default NULL,
2488 `unitprice` decimal(28,6) default NULL,
2489 `quantityreceived` smallint(6) default NULL,
2490 `cancelledby` varchar(10) default NULL,
2491 `datecancellationprinted` date default NULL,
2493 `supplierreference` mediumtext,
2494 `purchaseordernumber` mediumtext,
2495 `subscription` tinyint(1) default NULL,
2496 `serialid` varchar(30) default NULL,
2497 `basketno` int(11) default NULL,
2498 `biblioitemnumber` int(11) default NULL,
2499 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2500 `rrp` decimal(13,2) default NULL,
2501 `ecost` decimal(13,2) default NULL,
2502 `gst` decimal(13,2) default NULL,
2503 `budget_id` int(11) NOT NULL,
2504 `budgetgroup_id` int(11) NOT NULL,
2505 `budgetdate` date default NULL,
2506 `sort1` varchar(80) default NULL,
2507 `sort2` varchar(80) default NULL,
2508 `sort1_authcat` varchar(10) default NULL,
2509 `sort2_authcat` varchar(10) default NULL,
2510 `uncertainprice` tinyint(1),
2511 PRIMARY KEY (`ordernumber`),
2512 KEY `basketno` (`basketno`),
2513 KEY `biblionumber` (`biblionumber`),
2514 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2515 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2516 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2519 -- Table structure for table `aqorders_items`
2522 DROP TABLE IF EXISTS `aqorders_items`;
2523 CREATE TABLE `aqorders_items` (
2524 `ordernumber` int(11) NOT NULL,
2525 `itemnumber` int(11) NOT NULL,
2526 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2527 PRIMARY KEY (`itemnumber`),
2528 KEY `ordernumber` (`ordernumber`)
2529 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2532 -- Table structure for table `fieldmapping`
2535 DROP TABLE IF EXISTS `fieldmapping`;
2536 CREATE TABLE `fieldmapping` (
2537 `id` int(11) NOT NULL auto_increment,
2538 `field` varchar(255) NOT NULL,
2539 `frameworkcode` char(4) NOT NULL default '',
2540 `fieldcode` char(3) NOT NULL,
2541 `subfieldcode` char(1) NOT NULL,
2543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2546 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2547 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2548 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2549 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2550 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2551 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2552 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2553 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;