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 `csv_separator` varchar(2) NOT NULL,
759 `field_separator` varchar(2) NOT NULL,
760 `subfield_separator` varchar(2) NOT NULL,
761 `encoding` varchar(255) NOT NULL,
762 PRIMARY KEY (`export_format_id`)
763 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
767 -- Table structure for table `hold_fill_targets`
770 DROP TABLE IF EXISTS `hold_fill_targets`;
771 CREATE TABLE hold_fill_targets (
772 `borrowernumber` int(11) NOT NULL,
773 `biblionumber` int(11) NOT NULL,
774 `itemnumber` int(11) NOT NULL,
775 `source_branchcode` varchar(10) default NULL,
776 `item_level_request` tinyint(4) NOT NULL default 0,
777 PRIMARY KEY `itemnumber` (`itemnumber`),
778 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
779 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
780 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
781 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
782 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
783 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
784 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
785 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
786 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
787 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
790 -- Table structure for table `import_batches`
793 DROP TABLE IF EXISTS `import_batches`;
794 CREATE TABLE `import_batches` (
795 `import_batch_id` int(11) NOT NULL auto_increment,
796 `matcher_id` int(11) default NULL,
797 `template_id` int(11) default NULL,
798 `branchcode` varchar(10) default NULL,
799 `num_biblios` int(11) NOT NULL default 0,
800 `num_items` int(11) NOT NULL default 0,
801 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
802 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
803 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
804 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
805 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
806 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
807 `file_name` varchar(100),
808 `comments` mediumtext,
809 PRIMARY KEY (`import_batch_id`),
810 KEY `branchcode` (`branchcode`)
811 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
814 -- Table structure for table `import_records`
817 DROP TABLE IF EXISTS `import_records`;
818 CREATE TABLE `import_records` (
819 `import_record_id` int(11) NOT NULL auto_increment,
820 `import_batch_id` int(11) NOT NULL,
821 `branchcode` varchar(10) default NULL,
822 `record_sequence` int(11) NOT NULL default 0,
823 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
824 `import_date` DATE default NULL,
825 `marc` longblob NOT NULL,
826 `marcxml` longtext NOT NULL,
827 `marcxml_old` longtext NOT NULL,
828 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
829 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
830 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
831 `import_error` mediumtext,
832 `encoding` varchar(40) NOT NULL default '',
833 `z3950random` varchar(40) default NULL,
834 PRIMARY KEY (`import_record_id`),
835 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
836 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
837 KEY `branchcode` (`branchcode`),
838 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
839 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
842 -- Table structure for `import_record_matches`
844 DROP TABLE IF EXISTS `import_record_matches`;
845 CREATE TABLE `import_record_matches` (
846 `import_record_id` int(11) NOT NULL,
847 `candidate_match_id` int(11) NOT NULL,
848 `score` int(11) NOT NULL default 0,
849 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
850 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
851 KEY `record_score` (`import_record_id`, `score`)
852 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
855 -- Table structure for table `import_biblios`
858 DROP TABLE IF EXISTS `import_biblios`;
859 CREATE TABLE `import_biblios` (
860 `import_record_id` int(11) NOT NULL,
861 `matched_biblionumber` int(11) default NULL,
862 `control_number` varchar(25) default NULL,
863 `original_source` varchar(25) default NULL,
864 `title` varchar(128) default NULL,
865 `author` varchar(80) default NULL,
866 `isbn` varchar(30) default NULL,
867 `issn` varchar(9) default NULL,
868 `has_items` tinyint(1) NOT NULL default 0,
869 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
870 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
871 KEY `matched_biblionumber` (`matched_biblionumber`),
872 KEY `title` (`title`),
874 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
877 -- Table structure for table `import_items`
880 DROP TABLE IF EXISTS `import_items`;
881 CREATE TABLE `import_items` (
882 `import_items_id` int(11) NOT NULL auto_increment,
883 `import_record_id` int(11) NOT NULL,
884 `itemnumber` int(11) default NULL,
885 `branchcode` varchar(10) default NULL,
886 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
887 `marcxml` longtext NOT NULL,
888 `import_error` mediumtext,
889 PRIMARY KEY (`import_items_id`),
890 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
891 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
892 KEY `itemnumber` (`itemnumber`),
893 KEY `branchcode` (`branchcode`)
894 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
897 -- Table structure for table `issues`
900 DROP TABLE IF EXISTS `issues`;
901 CREATE TABLE `issues` (
902 `borrowernumber` int(11) default NULL,
903 `itemnumber` int(11) default NULL,
904 `date_due` date default NULL,
905 `branchcode` varchar(10) default NULL,
906 `issuingbranch` varchar(18) default NULL,
907 `returndate` date default NULL,
908 `lastreneweddate` date default NULL,
909 `return` varchar(4) default NULL,
910 `renewals` tinyint(4) default NULL,
911 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
912 `issuedate` date default NULL,
913 KEY `issuesborridx` (`borrowernumber`),
914 KEY `issuesitemidx` (`itemnumber`),
915 KEY `bordate` (`borrowernumber`,`timestamp`),
916 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
917 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
918 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
921 -- Table structure for table `issuingrules`
924 DROP TABLE IF EXISTS `issuingrules`;
925 CREATE TABLE `issuingrules` (
926 `categorycode` varchar(10) NOT NULL default '',
927 `itemtype` varchar(10) NOT NULL default '',
928 `restrictedtype` tinyint(1) default NULL,
929 `rentaldiscount` decimal(28,6) default NULL,
930 `reservecharge` decimal(28,6) default NULL,
931 `fine` decimal(28,6) default NULL,
932 `finedays` int(11) default NULL,
933 `firstremind` int(11) default NULL,
934 `chargeperiod` int(11) default NULL,
935 `accountsent` int(11) default NULL,
936 `chargename` varchar(100) default NULL,
937 `maxissueqty` int(4) default NULL,
938 `issuelength` int(4) default NULL,
939 `renewalsallowed` smallint(6) NOT NULL default "0",
940 `reservesallowed` smallint(6) NOT NULL default "0",
941 `branchcode` varchar(10) NOT NULL default '',
942 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
943 KEY `categorycode` (`categorycode`),
944 KEY `itemtype` (`itemtype`)
945 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
948 -- Table structure for table `items`
951 DROP TABLE IF EXISTS `items`;
952 CREATE TABLE `items` (
953 `itemnumber` int(11) NOT NULL auto_increment,
954 `biblionumber` int(11) NOT NULL default 0,
955 `biblioitemnumber` int(11) NOT NULL default 0,
956 `barcode` varchar(20) default NULL,
957 `dateaccessioned` date default NULL,
958 `booksellerid` mediumtext default NULL,
959 `homebranch` varchar(10) default NULL,
960 `price` decimal(8,2) default NULL,
961 `replacementprice` decimal(8,2) default NULL,
962 `replacementpricedate` date default NULL,
963 `datelastborrowed` date default NULL,
964 `datelastseen` date default NULL,
965 `stack` tinyint(1) default NULL,
966 `notforloan` tinyint(1) NOT NULL default 0,
967 `damaged` tinyint(1) NOT NULL default 0,
968 `itemlost` tinyint(1) NOT NULL default 0,
969 `wthdrawn` tinyint(1) NOT NULL default 0,
970 `itemcallnumber` varchar(255) default NULL,
971 `issues` smallint(6) default NULL,
972 `renewals` smallint(6) default NULL,
973 `reserves` smallint(6) default NULL,
974 `restricted` tinyint(1) default NULL,
975 `itemnotes` mediumtext,
976 `holdingbranch` varchar(10) default NULL,
977 `paidfor` mediumtext,
978 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
979 `location` varchar(80) default NULL,
980 `permanent_location` varchar(80) default NULL,
981 `onloan` date default NULL,
982 `cn_source` varchar(10) default NULL,
983 `cn_sort` varchar(30) default NULL,
984 `ccode` varchar(10) default NULL,
985 `materials` varchar(10) default NULL,
986 `uri` varchar(255) default NULL,
987 `itype` varchar(10) default NULL,
988 `more_subfields_xml` longtext default NULL,
989 `enumchron` varchar(80) default NULL,
990 `copynumber` varchar(32) default NULL,
991 `stocknumber` varchar(32) default NULL,
992 PRIMARY KEY (`itemnumber`),
993 UNIQUE KEY `itembarcodeidx` (`barcode`),
994 UNIQUE KEY `itemstocknumberidx` (`stocknumber`),
995 KEY `itembinoidx` (`biblioitemnumber`),
996 KEY `itembibnoidx` (`biblionumber`),
997 KEY `homebranch` (`homebranch`),
998 KEY `holdingbranch` (`holdingbranch`),
999 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1000 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1001 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1002 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1005 -- Table structure for table `itemtypes`
1008 DROP TABLE IF EXISTS `itemtypes`;
1009 CREATE TABLE `itemtypes` (
1010 `itemtype` varchar(10) NOT NULL default '',
1011 `description` mediumtext,
1012 `rentalcharge` double(16,4) default NULL,
1013 `notforloan` smallint(6) default NULL,
1014 `imageurl` varchar(200) default NULL,
1016 PRIMARY KEY (`itemtype`),
1017 UNIQUE KEY `itemtype` (`itemtype`)
1018 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1021 -- Table structure for table `creator_batches`
1024 DROP TABLE IF EXISTS `creator_batches`;
1025 SET @saved_cs_client = @@character_set_client;
1026 SET character_set_client = utf8;
1027 CREATE TABLE `creator_batches` (
1028 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1029 `batch_id` int(10) NOT NULL DEFAULT '1',
1030 `item_number` int(11) DEFAULT NULL,
1031 `borrower_number` int(11) DEFAULT NULL,
1032 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1033 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1034 `creator` char(15) NOT NULL DEFAULT 'Labels',
1035 PRIMARY KEY (`label_id`),
1036 KEY `branch_fk_constraint` (`branch_code`),
1037 KEY `item_fk_constraint` (`item_number`),
1038 KEY `borrower_fk_constraint` (`borrower_number`),
1039 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1040 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1041 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1042 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1045 -- Table structure for table `creator_images`
1048 DROP TABLE IF EXISTS `creator_images`;
1049 SET @saved_cs_client = @@character_set_client;
1050 SET character_set_client = utf8;
1051 CREATE TABLE `creator_images` (
1052 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1053 `imagefile` mediumblob,
1054 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1055 PRIMARY KEY (`image_id`),
1056 UNIQUE KEY `image_name_index` (`image_name`)
1057 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1060 -- Table structure for table `creator_layouts`
1063 DROP TABLE IF EXISTS `creator_layouts`;
1064 SET @saved_cs_client = @@character_set_client;
1065 SET character_set_client = utf8;
1066 CREATE TABLE `creator_layouts` (
1067 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1068 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1069 `start_label` int(2) NOT NULL DEFAULT '1',
1070 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1071 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1072 `guidebox` int(1) DEFAULT '0',
1073 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1074 `font_size` int(4) NOT NULL DEFAULT '10',
1075 `units` char(20) NOT NULL DEFAULT 'POINT',
1076 `callnum_split` int(1) DEFAULT '0',
1077 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1078 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1079 `layout_xml` text NOT NULL,
1080 `creator` char(15) NOT NULL DEFAULT 'Labels',
1081 PRIMARY KEY (`layout_id`)
1082 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1085 -- Table structure for table `creator_templates`
1088 DROP TABLE IF EXISTS `creator_templates`;
1089 SET @saved_cs_client = @@character_set_client;
1090 SET character_set_client = utf8;
1091 CREATE TABLE `creator_templates` (
1092 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1093 `profile_id` int(4) DEFAULT NULL,
1094 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1095 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1096 `page_width` float NOT NULL DEFAULT '0',
1097 `page_height` float NOT NULL DEFAULT '0',
1098 `label_width` float NOT NULL DEFAULT '0',
1099 `label_height` float NOT NULL DEFAULT '0',
1100 `top_text_margin` float NOT NULL DEFAULT '0',
1101 `left_text_margin` float NOT NULL DEFAULT '0',
1102 `top_margin` float NOT NULL DEFAULT '0',
1103 `left_margin` float NOT NULL DEFAULT '0',
1104 `cols` int(2) NOT NULL DEFAULT '0',
1105 `rows` int(2) NOT NULL DEFAULT '0',
1106 `col_gap` float NOT NULL DEFAULT '0',
1107 `row_gap` float NOT NULL DEFAULT '0',
1108 `units` char(20) NOT NULL DEFAULT 'POINT',
1109 `creator` char(15) NOT NULL DEFAULT 'Labels',
1110 PRIMARY KEY (`template_id`),
1111 KEY `template_profile_fk_constraint` (`profile_id`)
1112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1115 -- Table structure for table `letter`
1118 DROP TABLE IF EXISTS `letter`;
1119 CREATE TABLE `letter` (
1120 `module` varchar(20) NOT NULL default '',
1121 `code` varchar(20) NOT NULL default '',
1122 `name` varchar(100) NOT NULL default '',
1123 `title` varchar(200) NOT NULL default '',
1125 PRIMARY KEY (`module`,`code`)
1126 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1129 -- Table structure for table `marc_subfield_structure`
1132 DROP TABLE IF EXISTS `marc_subfield_structure`;
1133 CREATE TABLE `marc_subfield_structure` (
1134 `tagfield` varchar(3) NOT NULL default '',
1135 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1136 `liblibrarian` varchar(255) NOT NULL default '',
1137 `libopac` varchar(255) NOT NULL default '',
1138 `repeatable` tinyint(4) NOT NULL default 0,
1139 `mandatory` tinyint(4) NOT NULL default 0,
1140 `kohafield` varchar(40) default NULL,
1141 `tab` tinyint(1) default NULL,
1142 `authorised_value` varchar(20) default NULL,
1143 `authtypecode` varchar(20) default NULL,
1144 `value_builder` varchar(80) default NULL,
1145 `isurl` tinyint(1) default NULL,
1146 `hidden` tinyint(1) default NULL,
1147 `frameworkcode` varchar(4) NOT NULL default '',
1148 `seealso` varchar(1100) default NULL,
1149 `link` varchar(80) default NULL,
1150 `defaultvalue` text default NULL,
1151 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1152 KEY `kohafield_2` (`kohafield`),
1153 KEY `tab` (`frameworkcode`,`tab`),
1154 KEY `kohafield` (`frameworkcode`,`kohafield`)
1155 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1158 -- Table structure for table `marc_tag_structure`
1161 DROP TABLE IF EXISTS `marc_tag_structure`;
1162 CREATE TABLE `marc_tag_structure` (
1163 `tagfield` varchar(3) NOT NULL default '',
1164 `liblibrarian` varchar(255) NOT NULL default '',
1165 `libopac` varchar(255) NOT NULL default '',
1166 `repeatable` tinyint(4) NOT NULL default 0,
1167 `mandatory` tinyint(4) NOT NULL default 0,
1168 `authorised_value` varchar(10) default NULL,
1169 `frameworkcode` varchar(4) NOT NULL default '',
1170 PRIMARY KEY (`frameworkcode`,`tagfield`)
1171 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1174 -- Table structure for table `marc_matchers`
1177 DROP TABLE IF EXISTS `marc_matchers`;
1178 CREATE TABLE `marc_matchers` (
1179 `matcher_id` int(11) NOT NULL auto_increment,
1180 `code` varchar(10) NOT NULL default '',
1181 `description` varchar(255) NOT NULL default '',
1182 `record_type` varchar(10) NOT NULL default 'biblio',
1183 `threshold` int(11) NOT NULL default 0,
1184 PRIMARY KEY (`matcher_id`),
1185 KEY `code` (`code`),
1186 KEY `record_type` (`record_type`)
1187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1190 -- Table structure for table `matchpoints`
1192 DROP TABLE IF EXISTS `matchpoints`;
1193 CREATE TABLE `matchpoints` (
1194 `matcher_id` int(11) NOT NULL,
1195 `matchpoint_id` int(11) NOT NULL auto_increment,
1196 `search_index` varchar(30) NOT NULL default '',
1197 `score` int(11) NOT NULL default 0,
1198 PRIMARY KEY (`matchpoint_id`),
1199 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1200 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1201 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1205 -- Table structure for table `matchpoint_components`
1207 DROP TABLE IF EXISTS `matchpoint_components`;
1208 CREATE TABLE `matchpoint_components` (
1209 `matchpoint_id` int(11) NOT NULL,
1210 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1211 sequence int(11) NOT NULL default 0,
1212 tag varchar(3) NOT NULL default '',
1213 subfields varchar(40) NOT NULL default '',
1214 offset int(4) NOT NULL default 0,
1215 length int(4) NOT NULL default 0,
1216 PRIMARY KEY (`matchpoint_component_id`),
1217 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1218 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1219 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1220 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1223 -- Table structure for table `matcher_component_norms`
1225 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1226 CREATE TABLE `matchpoint_component_norms` (
1227 `matchpoint_component_id` int(11) NOT NULL,
1228 `sequence` int(11) NOT NULL default 0,
1229 `norm_routine` varchar(50) NOT NULL default '',
1230 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1231 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1232 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1233 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1236 -- Table structure for table `matcher_matchpoints`
1238 DROP TABLE IF EXISTS `matcher_matchpoints`;
1239 CREATE TABLE `matcher_matchpoints` (
1240 `matcher_id` int(11) NOT NULL,
1241 `matchpoint_id` int(11) NOT NULL,
1242 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1243 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1244 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1245 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1246 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1249 -- Table structure for table `matchchecks`
1251 DROP TABLE IF EXISTS `matchchecks`;
1252 CREATE TABLE `matchchecks` (
1253 `matcher_id` int(11) NOT NULL,
1254 `matchcheck_id` int(11) NOT NULL auto_increment,
1255 `source_matchpoint_id` int(11) NOT NULL,
1256 `target_matchpoint_id` int(11) NOT NULL,
1257 PRIMARY KEY (`matchcheck_id`),
1258 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1259 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1260 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1261 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1262 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1263 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1264 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1267 -- Table structure for table `notifys`
1270 DROP TABLE IF EXISTS `notifys`;
1271 CREATE TABLE `notifys` (
1272 `notify_id` int(11) NOT NULL default 0,
1273 `borrowernumber` int(11) NOT NULL default 0,
1274 `itemnumber` int(11) NOT NULL default 0,
1275 `notify_date` date default NULL,
1276 `notify_send_date` date default NULL,
1277 `notify_level` int(1) NOT NULL default 0,
1278 `method` varchar(20) NOT NULL default ''
1279 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1282 -- Table structure for table `nozebra`
1285 DROP TABLE IF EXISTS `nozebra`;
1286 CREATE TABLE `nozebra` (
1287 `server` varchar(20) NOT NULL,
1288 `indexname` varchar(40) NOT NULL,
1289 `value` varchar(250) NOT NULL,
1290 `biblionumbers` longtext NOT NULL,
1291 KEY `indexname` (`server`,`indexname`),
1292 KEY `value` (`server`,`value`))
1293 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1296 -- Table structure for table `old_issues`
1299 DROP TABLE IF EXISTS `old_issues`;
1300 CREATE TABLE `old_issues` (
1301 `borrowernumber` int(11) default NULL,
1302 `itemnumber` int(11) default NULL,
1303 `date_due` date default NULL,
1304 `branchcode` varchar(10) default NULL,
1305 `issuingbranch` varchar(18) default NULL,
1306 `returndate` date default NULL,
1307 `lastreneweddate` date default NULL,
1308 `return` varchar(4) default NULL,
1309 `renewals` tinyint(4) default NULL,
1310 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1311 `issuedate` date default NULL,
1312 KEY `old_issuesborridx` (`borrowernumber`),
1313 KEY `old_issuesitemidx` (`itemnumber`),
1314 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1315 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1316 ON DELETE SET NULL ON UPDATE SET NULL,
1317 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1318 ON DELETE SET NULL ON UPDATE SET NULL
1319 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1322 -- Table structure for table `old_reserves`
1324 DROP TABLE IF EXISTS `old_reserves`;
1325 CREATE TABLE `old_reserves` (
1326 `borrowernumber` int(11) default NULL,
1327 `reservedate` date default NULL,
1328 `biblionumber` int(11) default NULL,
1329 `constrainttype` varchar(1) default NULL,
1330 `branchcode` varchar(10) default NULL,
1331 `notificationdate` date default NULL,
1332 `reminderdate` date default NULL,
1333 `cancellationdate` date default NULL,
1334 `reservenotes` mediumtext,
1335 `priority` smallint(6) default NULL,
1336 `found` varchar(1) default NULL,
1337 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1338 `itemnumber` int(11) default NULL,
1339 `waitingdate` date default NULL,
1340 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1341 KEY `old_reserves_biblionumber` (`biblionumber`),
1342 KEY `old_reserves_itemnumber` (`itemnumber`),
1343 KEY `old_reserves_branchcode` (`branchcode`),
1344 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1345 ON DELETE SET NULL ON UPDATE SET NULL,
1346 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1347 ON DELETE SET NULL ON UPDATE SET NULL,
1348 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1349 ON DELETE SET NULL ON UPDATE SET NULL
1350 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1353 -- Table structure for table `opac_news`
1356 DROP TABLE IF EXISTS `opac_news`;
1357 CREATE TABLE `opac_news` (
1358 `idnew` int(10) unsigned NOT NULL auto_increment,
1359 `title` varchar(250) NOT NULL default '',
1360 `new` text NOT NULL,
1361 `lang` varchar(25) NOT NULL default '',
1362 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1363 `expirationdate` date default NULL,
1364 `number` int(11) default NULL,
1365 PRIMARY KEY (`idnew`)
1366 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1369 -- Table structure for table `overduerules`
1372 DROP TABLE IF EXISTS `overduerules`;
1373 CREATE TABLE `overduerules` (
1374 `branchcode` varchar(10) NOT NULL default '',
1375 `categorycode` varchar(10) NOT NULL default '',
1376 `delay1` int(4) default 0,
1377 `letter1` varchar(20) default NULL,
1378 `debarred1` varchar(1) default 0,
1379 `delay2` int(4) default 0,
1380 `debarred2` varchar(1) default 0,
1381 `letter2` varchar(20) default NULL,
1382 `delay3` int(4) default 0,
1383 `letter3` varchar(20) default NULL,
1384 `debarred3` int(1) default 0,
1385 PRIMARY KEY (`branchcode`,`categorycode`)
1386 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1389 -- Table structure for table `patroncards`
1392 DROP TABLE IF EXISTS `patroncards`;
1393 CREATE TABLE `patroncards` (
1394 `cardid` int(11) NOT NULL auto_increment,
1395 `batch_id` varchar(10) NOT NULL default '1',
1396 `borrowernumber` int(11) NOT NULL,
1397 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1398 PRIMARY KEY (`cardid`),
1399 KEY `patroncards_ibfk_1` (`borrowernumber`),
1400 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1401 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1404 -- Table structure for table `patronimage`
1407 DROP TABLE IF EXISTS `patronimage`;
1408 CREATE TABLE `patronimage` (
1409 `cardnumber` varchar(16) NOT NULL,
1410 `mimetype` varchar(15) NOT NULL,
1411 `imagefile` mediumblob NOT NULL,
1412 PRIMARY KEY (`cardnumber`),
1413 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1414 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1417 -- Table structure for table `printers`
1420 DROP TABLE IF EXISTS `printers`;
1421 CREATE TABLE `printers` (
1422 `printername` varchar(40) NOT NULL default '',
1423 `printqueue` varchar(20) default NULL,
1424 `printtype` varchar(20) default NULL,
1425 PRIMARY KEY (`printername`)
1426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1429 -- Table structure for table `printers_profile`
1432 DROP TABLE IF EXISTS `printers_profile`;
1433 CREATE TABLE `printers_profile` (
1434 `profile_id` int(4) NOT NULL auto_increment,
1435 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1436 `template_id` int(4) NOT NULL default '0',
1437 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1438 `offset_horz` float NOT NULL default '0',
1439 `offset_vert` float NOT NULL default '0',
1440 `creep_horz` float NOT NULL default '0',
1441 `creep_vert` float NOT NULL default '0',
1442 `units` char(20) NOT NULL default 'POINT',
1443 `creator` char(15) NOT NULL DEFAULT 'Labels',
1444 PRIMARY KEY (`profile_id`),
1445 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
1446 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1449 -- Table structure for table `repeatable_holidays`
1452 DROP TABLE IF EXISTS `repeatable_holidays`;
1453 CREATE TABLE `repeatable_holidays` (
1454 `id` int(11) NOT NULL auto_increment,
1455 `branchcode` varchar(10) NOT NULL default '',
1456 `weekday` smallint(6) default NULL,
1457 `day` smallint(6) default NULL,
1458 `month` smallint(6) default NULL,
1459 `title` varchar(50) NOT NULL default '',
1460 `description` text NOT NULL,
1462 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1465 -- Table structure for table `reports_dictionary`
1468 DROP TABLE IF EXISTS `reports_dictionary`;
1469 CREATE TABLE reports_dictionary (
1470 `id` int(11) NOT NULL auto_increment,
1471 `name` varchar(255) default NULL,
1473 `date_created` datetime default NULL,
1474 `date_modified` datetime default NULL,
1476 `area` int(11) default NULL,
1478 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1481 -- Table structure for table `reserveconstraints`
1484 DROP TABLE IF EXISTS `reserveconstraints`;
1485 CREATE TABLE `reserveconstraints` (
1486 `borrowernumber` int(11) NOT NULL default 0,
1487 `reservedate` date default NULL,
1488 `biblionumber` int(11) NOT NULL default 0,
1489 `biblioitemnumber` int(11) default NULL,
1490 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1491 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1494 -- Table structure for table `reserves`
1497 DROP TABLE IF EXISTS `reserves`;
1498 CREATE TABLE `reserves` (
1499 `borrowernumber` int(11) NOT NULL default 0,
1500 `reservedate` date default NULL,
1501 `biblionumber` int(11) NOT NULL default 0,
1502 `constrainttype` varchar(1) default NULL,
1503 `branchcode` varchar(10) default NULL,
1504 `notificationdate` date default NULL,
1505 `reminderdate` date default NULL,
1506 `cancellationdate` date default NULL,
1507 `reservenotes` mediumtext,
1508 `priority` smallint(6) default NULL,
1509 `found` varchar(1) default NULL,
1510 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1511 `itemnumber` int(11) default NULL,
1512 `waitingdate` date default NULL,
1513 KEY `borrowernumber` (`borrowernumber`),
1514 KEY `biblionumber` (`biblionumber`),
1515 KEY `itemnumber` (`itemnumber`),
1516 KEY `branchcode` (`branchcode`),
1517 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1518 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1519 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1520 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1521 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1524 -- Table structure for table `reviews`
1527 DROP TABLE IF EXISTS `reviews`;
1528 CREATE TABLE `reviews` (
1529 `reviewid` int(11) NOT NULL auto_increment,
1530 `borrowernumber` int(11) default NULL,
1531 `biblionumber` int(11) default NULL,
1533 `approved` tinyint(4) default NULL,
1534 `datereviewed` datetime default NULL,
1535 PRIMARY KEY (`reviewid`)
1536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1539 -- Table structure for table `roadtype`
1542 DROP TABLE IF EXISTS `roadtype`;
1543 CREATE TABLE `roadtype` (
1544 `roadtypeid` int(11) NOT NULL auto_increment,
1545 `road_type` varchar(100) NOT NULL default '',
1546 PRIMARY KEY (`roadtypeid`)
1547 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1550 -- Table structure for table `saved_sql`
1553 DROP TABLE IF EXISTS `saved_sql`;
1554 CREATE TABLE saved_sql (
1555 `id` int(11) NOT NULL auto_increment,
1556 `borrowernumber` int(11) default NULL,
1557 `date_created` datetime default NULL,
1558 `last_modified` datetime default NULL,
1560 `last_run` datetime default NULL,
1561 `report_name` varchar(255) default NULL,
1562 `type` varchar(255) default NULL,
1565 KEY boridx (`borrowernumber`)
1566 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1570 -- Table structure for `saved_reports`
1573 DROP TABLE IF EXISTS `saved_reports`;
1574 CREATE TABLE saved_reports (
1575 `id` int(11) NOT NULL auto_increment,
1576 `report_id` int(11) default NULL,
1578 `date_run` datetime default NULL,
1580 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1584 -- Table structure for table `search_history`
1587 DROP TABLE IF EXISTS `search_history`;
1588 CREATE TABLE IF NOT EXISTS `search_history` (
1589 `userid` int(11) NOT NULL,
1590 `sessionid` varchar(32) NOT NULL,
1591 `query_desc` varchar(255) NOT NULL,
1592 `query_cgi` varchar(255) NOT NULL,
1593 `total` int(11) NOT NULL,
1594 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1595 KEY `userid` (`userid`),
1596 KEY `sessionid` (`sessionid`)
1597 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1601 -- Table structure for table `serial`
1604 DROP TABLE IF EXISTS `serial`;
1605 CREATE TABLE `serial` (
1606 `serialid` int(11) NOT NULL auto_increment,
1607 `biblionumber` varchar(100) NOT NULL default '',
1608 `subscriptionid` varchar(100) NOT NULL default '',
1609 `serialseq` varchar(100) NOT NULL default '',
1610 `status` tinyint(4) NOT NULL default 0,
1611 `planneddate` date default NULL,
1613 `publisheddate` date default NULL,
1614 `itemnumber` text default NULL,
1615 `claimdate` date default NULL,
1616 `routingnotes` text,
1617 PRIMARY KEY (`serialid`)
1618 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1621 -- Table structure for table `sessions`
1624 DROP TABLE IF EXISTS sessions;
1625 CREATE TABLE sessions (
1626 `id` varchar(32) NOT NULL,
1627 `a_session` text NOT NULL,
1629 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1632 -- Table structure for table `special_holidays`
1635 DROP TABLE IF EXISTS `special_holidays`;
1636 CREATE TABLE `special_holidays` (
1637 `id` int(11) NOT NULL auto_increment,
1638 `branchcode` varchar(10) NOT NULL default '',
1639 `day` smallint(6) NOT NULL default 0,
1640 `month` smallint(6) NOT NULL default 0,
1641 `year` smallint(6) NOT NULL default 0,
1642 `isexception` smallint(1) NOT NULL default 1,
1643 `title` varchar(50) NOT NULL default '',
1644 `description` text NOT NULL,
1646 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1649 -- Table structure for table `statistics`
1652 DROP TABLE IF EXISTS `statistics`;
1653 CREATE TABLE `statistics` (
1654 `datetime` datetime default NULL,
1655 `branch` varchar(10) default NULL,
1656 `proccode` varchar(4) default NULL,
1657 `value` double(16,4) default NULL,
1658 `type` varchar(16) default NULL,
1660 `usercode` varchar(10) default NULL,
1661 `itemnumber` int(11) default NULL,
1662 `itemtype` varchar(10) default NULL,
1663 `borrowernumber` int(11) default NULL,
1664 `associatedborrower` int(11) default NULL,
1665 KEY `timeidx` (`datetime`)
1666 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1669 -- Table structure for table `stopwords`
1672 DROP TABLE IF EXISTS `stopwords`;
1673 CREATE TABLE `stopwords` (
1674 `word` varchar(255) default NULL
1675 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1678 -- Table structure for table `subscription`
1681 DROP TABLE IF EXISTS `subscription`;
1682 CREATE TABLE `subscription` (
1683 `biblionumber` int(11) NOT NULL default 0,
1684 `subscriptionid` int(11) NOT NULL auto_increment,
1685 `librarian` varchar(100) default '',
1686 `startdate` date default NULL,
1687 `aqbooksellerid` int(11) default 0,
1688 `cost` int(11) default 0,
1689 `aqbudgetid` int(11) default 0,
1690 `weeklength` int(11) default 0,
1691 `monthlength` int(11) default 0,
1692 `numberlength` int(11) default 0,
1693 `periodicity` tinyint(4) default 0,
1694 `dow` varchar(100) default '',
1695 `numberingmethod` varchar(100) default '',
1697 `status` varchar(100) NOT NULL default '',
1698 `add1` int(11) default 0,
1699 `every1` int(11) default 0,
1700 `whenmorethan1` int(11) default 0,
1701 `setto1` int(11) default NULL,
1702 `lastvalue1` int(11) default NULL,
1703 `add2` int(11) default 0,
1704 `every2` int(11) default 0,
1705 `whenmorethan2` int(11) default 0,
1706 `setto2` int(11) default NULL,
1707 `lastvalue2` int(11) default NULL,
1708 `add3` int(11) default 0,
1709 `every3` int(11) default 0,
1710 `innerloop1` int(11) default 0,
1711 `innerloop2` int(11) default 0,
1712 `innerloop3` int(11) default 0,
1713 `whenmorethan3` int(11) default 0,
1714 `setto3` int(11) default NULL,
1715 `lastvalue3` int(11) default NULL,
1716 `issuesatonce` tinyint(3) NOT NULL default 1,
1717 `firstacquidate` date default NULL,
1718 `manualhistory` tinyint(1) NOT NULL default 0,
1719 `irregularity` text,
1720 `letter` varchar(20) default NULL,
1721 `numberpattern` tinyint(3) default 0,
1722 `distributedto` text,
1723 `internalnotes` longtext,
1725 `location` varchar(80) NULL default '',
1726 `branchcode` varchar(10) NOT NULL default '',
1727 `hemisphere` tinyint(3) default 0,
1728 `lastbranch` varchar(10),
1729 `serialsadditems` tinyint(1) NOT NULL default '0',
1730 `staffdisplaycount` VARCHAR(10) NULL,
1731 `opacdisplaycount` VARCHAR(10) NULL,
1732 `graceperiod` int(11) NOT NULL default '0',
1733 PRIMARY KEY (`subscriptionid`)
1734 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1737 -- Table structure for table `subscriptionhistory`
1740 DROP TABLE IF EXISTS `subscriptionhistory`;
1741 CREATE TABLE `subscriptionhistory` (
1742 `biblionumber` int(11) NOT NULL default 0,
1743 `subscriptionid` int(11) NOT NULL default 0,
1744 `histstartdate` date default NULL,
1745 `histenddate` date default NULL,
1746 `missinglist` longtext NOT NULL,
1747 `recievedlist` longtext NOT NULL,
1748 `opacnote` varchar(150) NOT NULL default '',
1749 `librariannote` varchar(150) NOT NULL default '',
1750 PRIMARY KEY (`subscriptionid`),
1751 KEY `biblionumber` (`biblionumber`)
1752 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1755 -- Table structure for table `subscriptionroutinglist`
1758 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1759 CREATE TABLE `subscriptionroutinglist` (
1760 `routingid` int(11) NOT NULL auto_increment,
1761 `borrowernumber` int(11) default NULL,
1762 `ranking` int(11) default NULL,
1763 `subscriptionid` int(11) default NULL,
1764 PRIMARY KEY (`routingid`)
1765 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1768 -- Table structure for table `suggestions`
1771 DROP TABLE IF EXISTS `suggestions`;
1772 CREATE TABLE `suggestions` (
1773 `suggestionid` int(8) NOT NULL auto_increment,
1774 `suggestedby` int(11) NOT NULL default 0,
1775 `suggesteddate` date NOT NULL default 0,
1776 `managedby` int(11) default NULL,
1777 `manageddate` date default NULL,
1778 acceptedby INT(11) default NULL,
1779 accepteddate date default NULL,
1780 rejectedby INT(11) default NULL,
1781 rejecteddate date default NULL,
1782 `STATUS` varchar(10) NOT NULL default '',
1784 `author` varchar(80) default NULL,
1785 `title` varchar(80) default NULL,
1786 `copyrightdate` smallint(6) default NULL,
1787 `publishercode` varchar(255) default NULL,
1788 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1789 `volumedesc` varchar(255) default NULL,
1790 `publicationyear` smallint(6) default 0,
1791 `place` varchar(255) default NULL,
1792 `isbn` varchar(30) default NULL,
1793 `mailoverseeing` smallint(1) default 0,
1794 `biblionumber` int(11) default NULL,
1797 branchcode VARCHAR(10) default NULL,
1798 collectiontitle text default NULL,
1799 itemtype VARCHAR(30) default NULL,
1800 PRIMARY KEY (`suggestionid`),
1801 KEY `suggestedby` (`suggestedby`),
1802 KEY `managedby` (`managedby`)
1803 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1806 -- Table structure for table `systempreferences`
1809 DROP TABLE IF EXISTS `systempreferences`;
1810 CREATE TABLE `systempreferences` (
1811 `variable` varchar(50) NOT NULL default '',
1813 `options` mediumtext,
1815 `type` varchar(20) default NULL,
1816 PRIMARY KEY (`variable`)
1817 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1820 -- Table structure for table `tags`
1823 DROP TABLE IF EXISTS `tags`;
1824 CREATE TABLE `tags` (
1825 `entry` varchar(255) NOT NULL default '',
1826 `weight` bigint(20) NOT NULL default 0,
1827 PRIMARY KEY (`entry`)
1828 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1831 -- Table structure for table `tags_all`
1834 DROP TABLE IF EXISTS `tags_all`;
1835 CREATE TABLE `tags_all` (
1836 `tag_id` int(11) NOT NULL auto_increment,
1837 `borrowernumber` int(11) NOT NULL,
1838 `biblionumber` int(11) NOT NULL,
1839 `term` varchar(255) NOT NULL,
1840 `language` int(4) default NULL,
1841 `date_created` datetime NOT NULL,
1842 PRIMARY KEY (`tag_id`),
1843 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1844 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1845 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1846 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1847 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1848 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1849 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1852 -- Table structure for table `tags_approval`
1855 DROP TABLE IF EXISTS `tags_approval`;
1856 CREATE TABLE `tags_approval` (
1857 `term` varchar(255) NOT NULL,
1858 `approved` int(1) NOT NULL default '0',
1859 `date_approved` datetime default NULL,
1860 `approved_by` int(11) default NULL,
1861 `weight_total` int(9) NOT NULL default '1',
1862 PRIMARY KEY (`term`),
1863 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1864 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1865 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1866 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1869 -- Table structure for table `tags_index`
1872 DROP TABLE IF EXISTS `tags_index`;
1873 CREATE TABLE `tags_index` (
1874 `term` varchar(255) NOT NULL,
1875 `biblionumber` int(11) NOT NULL,
1876 `weight` int(9) NOT NULL default '1',
1877 PRIMARY KEY (`term`,`biblionumber`),
1878 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1879 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1880 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1881 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1882 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1883 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1886 -- Table structure for table `userflags`
1889 DROP TABLE IF EXISTS `userflags`;
1890 CREATE TABLE `userflags` (
1891 `bit` int(11) NOT NULL default 0,
1892 `flag` varchar(30) default NULL,
1893 `flagdesc` varchar(255) default NULL,
1894 `defaulton` int(11) default NULL,
1896 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1899 -- Table structure for table `virtualshelves`
1902 DROP TABLE IF EXISTS `virtualshelves`;
1903 CREATE TABLE `virtualshelves` (
1904 `shelfnumber` int(11) NOT NULL auto_increment,
1905 `shelfname` varchar(255) default NULL,
1906 `owner` varchar(80) default NULL,
1907 `category` varchar(1) default NULL,
1908 `sortfield` varchar(16) default NULL,
1909 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1910 PRIMARY KEY (`shelfnumber`)
1911 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1914 -- Table structure for table `virtualshelfcontents`
1917 DROP TABLE IF EXISTS `virtualshelfcontents`;
1918 CREATE TABLE `virtualshelfcontents` (
1919 `shelfnumber` int(11) NOT NULL default 0,
1920 `biblionumber` int(11) NOT NULL default 0,
1921 `flags` int(11) default NULL,
1922 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1923 KEY `shelfnumber` (`shelfnumber`),
1924 KEY `biblionumber` (`biblionumber`),
1925 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1926 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1927 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1930 -- Table structure for table `z3950servers`
1933 DROP TABLE IF EXISTS `z3950servers`;
1934 CREATE TABLE `z3950servers` (
1935 `host` varchar(255) default NULL,
1936 `port` int(11) default NULL,
1937 `db` varchar(255) default NULL,
1938 `userid` varchar(255) default NULL,
1939 `password` varchar(255) default NULL,
1941 `id` int(11) NOT NULL auto_increment,
1942 `checked` smallint(6) default NULL,
1943 `rank` int(11) default NULL,
1944 `syntax` varchar(80) default NULL,
1946 `position` enum('primary','secondary','') NOT NULL default 'primary',
1947 `type` enum('zed','opensearch') NOT NULL default 'zed',
1948 `encoding` text default NULL,
1949 `description` text NOT NULL,
1951 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1954 -- Table structure for table `zebraqueue`
1957 DROP TABLE IF EXISTS `zebraqueue`;
1958 CREATE TABLE `zebraqueue` (
1959 `id` int(11) NOT NULL auto_increment,
1960 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1961 `operation` char(20) NOT NULL default '',
1962 `server` char(20) NOT NULL default '',
1963 `done` int(11) NOT NULL default '0',
1964 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1966 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1967 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1969 DROP TABLE IF EXISTS `services_throttle`;
1970 CREATE TABLE `services_throttle` (
1971 `service_type` varchar(10) NOT NULL default '',
1972 `service_count` varchar(45) default NULL,
1973 PRIMARY KEY (`service_type`)
1974 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1976 -- http://www.w3.org/International/articles/language-tags/
1979 DROP TABLE IF EXISTS language_subtag_registry;
1980 CREATE TABLE language_subtag_registry (
1982 type varchar(25), -- language-script-region-variant-extension-privateuse
1983 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1985 id int(11) NOT NULL auto_increment,
1987 KEY `subtag` (`subtag`)
1988 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1990 -- TODO: add suppress_scripts
1991 -- this maps three letter codes defined in iso639.2 back to their
1992 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1993 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1994 CREATE TABLE language_rfc4646_to_iso639 (
1995 rfc4646_subtag varchar(25),
1996 iso639_2_code varchar(25),
1997 id int(11) NOT NULL auto_increment,
1999 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2000 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2002 DROP TABLE IF EXISTS language_descriptions;
2003 CREATE TABLE language_descriptions (
2007 description varchar(255),
2008 id int(11) NOT NULL auto_increment,
2011 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2013 -- bi-directional support, keyed by script subcode
2014 DROP TABLE IF EXISTS language_script_bidi;
2015 CREATE TABLE language_script_bidi (
2016 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2017 bidi varchar(3), -- rtl ltr
2018 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2019 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2021 -- TODO: need to map language subtags to script subtags for detection
2022 -- of bidi when script is not specified (like ar, he)
2023 DROP TABLE IF EXISTS language_script_mapping;
2024 CREATE TABLE language_script_mapping (
2025 language_subtag varchar(25),
2026 script_subtag varchar(25),
2027 KEY `language_subtag` (`language_subtag`)
2028 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2030 DROP TABLE IF EXISTS `permissions`;
2031 CREATE TABLE `permissions` (
2032 `module_bit` int(11) NOT NULL DEFAULT 0,
2033 `code` varchar(64) DEFAULT NULL,
2034 `description` varchar(255) DEFAULT NULL,
2035 PRIMARY KEY (`module_bit`, `code`),
2036 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2037 ON DELETE CASCADE ON UPDATE CASCADE
2038 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2040 DROP TABLE IF EXISTS `serialitems`;
2041 CREATE TABLE `serialitems` (
2042 `itemnumber` int(11) NOT NULL,
2043 `serialid` int(11) NOT NULL,
2044 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2045 KEY `serialitems_sfk_1` (`serialid`),
2046 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2047 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2049 DROP TABLE IF EXISTS `user_permissions`;
2050 CREATE TABLE `user_permissions` (
2051 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2052 `module_bit` int(11) NOT NULL DEFAULT 0,
2053 `code` varchar(64) DEFAULT NULL,
2054 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2055 ON DELETE CASCADE ON UPDATE CASCADE,
2056 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2057 ON DELETE CASCADE ON UPDATE CASCADE
2058 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2061 -- Table structure for table `tmp_holdsqueue`
2064 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2065 CREATE TABLE `tmp_holdsqueue` (
2066 `biblionumber` int(11) default NULL,
2067 `itemnumber` int(11) default NULL,
2068 `barcode` varchar(20) default NULL,
2069 `surname` mediumtext NOT NULL,
2072 `borrowernumber` int(11) NOT NULL,
2073 `cardnumber` varchar(16) default NULL,
2074 `reservedate` date default NULL,
2076 `itemcallnumber` varchar(255) default NULL,
2077 `holdingbranch` varchar(10) default NULL,
2078 `pickbranch` varchar(10) default NULL,
2080 `item_level_request` tinyint(4) NOT NULL default 0
2081 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2084 -- Table structure for table `message_queue`
2087 DROP TABLE IF EXISTS `message_queue`;
2088 CREATE TABLE `message_queue` (
2089 `message_id` int(11) NOT NULL auto_increment,
2090 `borrowernumber` int(11) default NULL,
2093 `metadata` text DEFAULT NULL,
2094 `letter_code` varchar(64) DEFAULT NULL,
2095 `message_transport_type` varchar(20) NOT NULL,
2096 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2097 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2098 `to_address` mediumtext,
2099 `from_address` mediumtext,
2100 `content_type` text,
2101 KEY `message_id` (`message_id`),
2102 KEY `borrowernumber` (`borrowernumber`),
2103 KEY `message_transport_type` (`message_transport_type`),
2104 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2105 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2109 -- Table structure for table `message_transport_types`
2112 DROP TABLE IF EXISTS `message_transport_types`;
2113 CREATE TABLE `message_transport_types` (
2114 `message_transport_type` varchar(20) NOT NULL,
2115 PRIMARY KEY (`message_transport_type`)
2116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2119 -- Table structure for table `message_attributes`
2122 DROP TABLE IF EXISTS `message_attributes`;
2123 CREATE TABLE `message_attributes` (
2124 `message_attribute_id` int(11) NOT NULL auto_increment,
2125 `message_name` varchar(20) NOT NULL default '',
2126 `takes_days` tinyint(1) NOT NULL default '0',
2127 PRIMARY KEY (`message_attribute_id`),
2128 UNIQUE KEY `message_name` (`message_name`)
2129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2132 -- Table structure for table `message_transports`
2135 DROP TABLE IF EXISTS `message_transports`;
2136 CREATE TABLE `message_transports` (
2137 `message_attribute_id` int(11) NOT NULL,
2138 `message_transport_type` varchar(20) NOT NULL,
2139 `is_digest` tinyint(1) NOT NULL default '0',
2140 `letter_module` varchar(20) NOT NULL default '',
2141 `letter_code` varchar(20) NOT NULL default '',
2142 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2143 KEY `message_transport_type` (`message_transport_type`),
2144 KEY `letter_module` (`letter_module`,`letter_code`),
2145 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2146 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2147 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2148 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2151 -- Table structure for table `borrower_message_preferences`
2154 DROP TABLE IF EXISTS `borrower_message_preferences`;
2155 CREATE TABLE `borrower_message_preferences` (
2156 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2157 `borrowernumber` int(11) default NULL,
2158 `categorycode` varchar(10) default NULL,
2159 `message_attribute_id` int(11) default '0',
2160 `days_in_advance` int(11) default '0',
2161 `wants_digest` tinyint(1) NOT NULL default '0',
2162 PRIMARY KEY (`borrower_message_preference_id`),
2163 KEY `borrowernumber` (`borrowernumber`),
2164 KEY `categorycode` (`categorycode`),
2165 KEY `message_attribute_id` (`message_attribute_id`),
2166 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2167 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2168 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2169 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2172 -- Table structure for table `borrower_message_transport_preferences`
2175 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2176 CREATE TABLE `borrower_message_transport_preferences` (
2177 `borrower_message_preference_id` int(11) NOT NULL default '0',
2178 `message_transport_type` varchar(20) NOT NULL default '0',
2179 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2180 KEY `message_transport_type` (`message_transport_type`),
2181 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,
2182 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
2183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2186 -- Table structure for the table branch_transfer_limits
2189 DROP TABLE IF EXISTS `branch_transfer_limits`;
2190 CREATE TABLE branch_transfer_limits (
2191 limitId int(8) NOT NULL auto_increment,
2192 toBranch varchar(10) NOT NULL,
2193 fromBranch varchar(10) NOT NULL,
2194 itemtype varchar(10) NULL,
2195 ccode varchar(10) NULL,
2196 PRIMARY KEY (limitId)
2197 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2200 -- Table structure for table `item_circulation_alert_preferences`
2203 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2204 CREATE TABLE `item_circulation_alert_preferences` (
2205 `id` int(11) NOT NULL auto_increment,
2206 `branchcode` varchar(10) NOT NULL,
2207 `categorycode` varchar(10) NOT NULL,
2208 `item_type` varchar(10) NOT NULL,
2209 `notification` varchar(16) NOT NULL,
2211 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2215 -- Table structure for table `messages`
2218 CREATE TABLE `messages` (
2219 `message_id` int(11) NOT NULL auto_increment,
2220 `borrowernumber` int(11) NOT NULL,
2221 `branchcode` varchar(4) default NULL,
2222 `message_type` varchar(1) NOT NULL,
2223 `message` text NOT NULL,
2224 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2225 PRIMARY KEY (`message_id`)
2226 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2229 -- Table structure for table `accountlines`
2232 DROP TABLE IF EXISTS `accountlines`;
2233 CREATE TABLE `accountlines` (
2234 `borrowernumber` int(11) NOT NULL default 0,
2235 `accountno` smallint(6) NOT NULL default 0,
2236 `itemnumber` int(11) default NULL,
2237 `date` date default NULL,
2238 `amount` decimal(28,6) default NULL,
2239 `description` mediumtext,
2240 `dispute` mediumtext,
2241 `accounttype` varchar(5) default NULL,
2242 `amountoutstanding` decimal(28,6) default NULL,
2243 `lastincrement` decimal(28,6) default NULL,
2244 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2245 `notify_id` int(11) NOT NULL default 0,
2246 `notify_level` int(2) NOT NULL default 0,
2247 KEY `acctsborridx` (`borrowernumber`),
2248 KEY `timeidx` (`timestamp`),
2249 KEY `itemnumber` (`itemnumber`),
2250 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2251 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2252 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2255 -- Table structure for table `accountoffsets`
2258 DROP TABLE IF EXISTS `accountoffsets`;
2259 CREATE TABLE `accountoffsets` (
2260 `borrowernumber` int(11) NOT NULL default 0,
2261 `accountno` smallint(6) NOT NULL default 0,
2262 `offsetaccount` smallint(6) NOT NULL default 0,
2263 `offsetamount` decimal(28,6) default NULL,
2264 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2265 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2269 -- Table structure for table `action_logs`
2272 DROP TABLE IF EXISTS `action_logs`;
2273 CREATE TABLE `action_logs` (
2274 `action_id` int(11) NOT NULL auto_increment,
2275 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2276 `user` int(11) NOT NULL default 0,
2279 `object` int(11) default NULL,
2281 PRIMARY KEY (`action_id`),
2282 KEY (`timestamp`,`user`)
2283 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2286 -- Table structure for table `alert`
2289 DROP TABLE IF EXISTS `alert`;
2290 CREATE TABLE `alert` (
2291 `alertid` int(11) NOT NULL auto_increment,
2292 `borrowernumber` int(11) NOT NULL default 0,
2293 `type` varchar(10) NOT NULL default '',
2294 `externalid` varchar(20) NOT NULL default '',
2295 PRIMARY KEY (`alertid`),
2296 KEY `borrowernumber` (`borrowernumber`),
2297 KEY `type` (`type`,`externalid`)
2298 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2301 -- Table structure for table `aqbasketgroups`
2304 DROP TABLE IF EXISTS `aqbasketgroups`;
2305 CREATE TABLE `aqbasketgroups` (
2306 `id` int(11) NOT NULL auto_increment,
2307 `name` varchar(50) default NULL,
2308 `closed` tinyint(1) default NULL,
2309 `booksellerid` int(11) NOT NULL,
2311 KEY `booksellerid` (`booksellerid`),
2312 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2316 -- Table structure for table `aqbasket`
2319 DROP TABLE IF EXISTS `aqbasket`;
2320 CREATE TABLE `aqbasket` (
2321 `basketno` int(11) NOT NULL auto_increment,
2322 `basketname` varchar(50) default NULL,
2324 `booksellernote` mediumtext,
2325 `contractnumber` int(11),
2326 `creationdate` date default NULL,
2327 `closedate` date default NULL,
2328 `booksellerid` int(11) NOT NULL default 1,
2329 `authorisedby` varchar(10) default NULL,
2330 `booksellerinvoicenumber` mediumtext,
2331 `basketgroupid` int(11),
2332 PRIMARY KEY (`basketno`),
2333 KEY `booksellerid` (`booksellerid`),
2334 KEY `basketgroupid` (`basketgroupid`),
2335 KEY `contractnumber` (`contractnumber`),
2336 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2337 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2338 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2342 -- Table structure for table `aqbooksellers`
2345 DROP TABLE IF EXISTS `aqbooksellers`;
2346 CREATE TABLE `aqbooksellers` (
2347 `id` int(11) NOT NULL auto_increment,
2348 `name` mediumtext NOT NULL,
2349 `address1` mediumtext,
2350 `address2` mediumtext,
2351 `address3` mediumtext,
2352 `address4` mediumtext,
2353 `phone` varchar(30) default NULL,
2354 `accountnumber` mediumtext,
2355 `othersupplier` mediumtext,
2356 `currency` varchar(3) NOT NULL default '',
2357 `booksellerfax` mediumtext,
2359 `bookselleremail` mediumtext,
2360 `booksellerurl` mediumtext,
2361 `contact` varchar(100) default NULL,
2362 `postal` mediumtext,
2363 `url` varchar(255) default NULL,
2364 `contpos` varchar(100) default NULL,
2365 `contphone` varchar(100) default NULL,
2366 `contfax` varchar(100) default NULL,
2367 `contaltphone` varchar(100) default NULL,
2368 `contemail` varchar(100) default NULL,
2369 `contnotes` mediumtext,
2370 `active` tinyint(4) default NULL,
2371 `listprice` varchar(10) default NULL,
2372 `invoiceprice` varchar(10) default NULL,
2373 `gstreg` tinyint(4) default NULL,
2374 `listincgst` tinyint(4) default NULL,
2375 `invoiceincgst` tinyint(4) default NULL,
2376 `gstrate` decimal(6,4) default NULL,
2377 `discount` float(6,4) default NULL,
2378 `fax` varchar(50) default NULL,
2380 KEY `listprice` (`listprice`),
2381 KEY `invoiceprice` (`invoiceprice`),
2382 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2383 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2384 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2387 -- Table structure for table `aqbudgets`
2390 DROP TABLE IF EXISTS `aqbudgets`;
2391 CREATE TABLE `aqbudgets` (
2392 `budget_id` int(11) NOT NULL auto_increment,
2393 `budget_parent_id` int(11) default NULL,
2394 `budget_code` varchar(30) default NULL,
2395 `budget_name` varchar(80) default NULL,
2396 `budget_branchcode` varchar(10) default NULL,
2397 `budget_amount` decimal(28,6) NULL default '0.00',
2398 `budget_encumb` decimal(28,6) NULL default '0.00',
2399 `budget_expend` decimal(28,6) NULL default '0.00',
2400 `budget_notes` mediumtext,
2401 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2402 `budget_period_id` int(11) default NULL,
2403 `sort1_authcat` varchar(80) default NULL,
2404 `sort2_authcat` varchar(80) default NULL,
2405 `budget_owner_id` int(11) default NULL,
2406 `budget_permission` int(1) default '0',
2407 PRIMARY KEY (`budget_id`)
2408 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2412 -- Table structure for table `aqbudgetperiods`
2416 DROP TABLE IF EXISTS `aqbudgetperiods`;
2417 CREATE TABLE `aqbudgetperiods` (
2418 `budget_period_id` int(11) NOT NULL auto_increment,
2419 `budget_period_startdate` date NOT NULL,
2420 `budget_period_enddate` date NOT NULL,
2421 `budget_period_active` tinyint(1) default '0',
2422 `budget_period_description` mediumtext,
2423 `budget_period_total` decimal(28,6),
2424 `budget_period_locked` tinyint(1) default NULL,
2425 `sort1_authcat` varchar(10) default NULL,
2426 `sort2_authcat` varchar(10) default NULL,
2427 PRIMARY KEY (`budget_period_id`)
2428 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2431 -- Table structure for table `aqbudgets_planning`
2434 DROP TABLE IF EXISTS `aqbudgets_planning`;
2435 CREATE TABLE `aqbudgets_planning` (
2436 `plan_id` int(11) NOT NULL auto_increment,
2437 `budget_id` int(11) NOT NULL,
2438 `budget_period_id` int(11) NOT NULL,
2439 `estimated_amount` decimal(28,6) default NULL,
2440 `authcat` varchar(30) NOT NULL,
2441 `authvalue` varchar(30) NOT NULL,
2442 PRIMARY KEY (`plan_id`),
2443 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2444 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2447 -- Table structure for table 'aqcontract'
2450 DROP TABLE IF EXISTS `aqcontract`;
2451 CREATE TABLE `aqcontract` (
2452 `contractnumber` int(11) NOT NULL auto_increment,
2453 `contractstartdate` date default NULL,
2454 `contractenddate` date default NULL,
2455 `contractname` varchar(50) default NULL,
2456 `contractdescription` mediumtext,
2457 `booksellerid` int(11) not NULL,
2458 PRIMARY KEY (`contractnumber`),
2459 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2460 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2461 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2464 -- Table structure for table `aqorderdelivery`
2467 DROP TABLE IF EXISTS `aqorderdelivery`;
2468 CREATE TABLE `aqorderdelivery` (
2469 `ordernumber` date default NULL,
2470 `deliverynumber` smallint(6) NOT NULL default 0,
2471 `deliverydate` varchar(18) default NULL,
2472 `qtydelivered` smallint(6) default NULL,
2473 `deliverycomments` mediumtext
2474 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2477 -- Table structure for table `aqorders`
2480 DROP TABLE IF EXISTS `aqorders`;
2481 CREATE TABLE `aqorders` (
2482 `ordernumber` int(11) NOT NULL auto_increment,
2483 `biblionumber` int(11) default NULL,
2484 `entrydate` date default NULL,
2485 `quantity` smallint(6) default NULL,
2486 `currency` varchar(3) default NULL,
2487 `listprice` decimal(28,6) default NULL,
2488 `totalamount` decimal(28,6) default NULL,
2489 `datereceived` date default NULL,
2490 `booksellerinvoicenumber` mediumtext,
2491 `freight` decimal(28,6) default NULL,
2492 `unitprice` decimal(28,6) default NULL,
2493 `quantityreceived` smallint(6) default NULL,
2494 `cancelledby` varchar(10) default NULL,
2495 `datecancellationprinted` date default NULL,
2497 `supplierreference` mediumtext,
2498 `purchaseordernumber` mediumtext,
2499 `subscription` tinyint(1) default NULL,
2500 `serialid` varchar(30) default NULL,
2501 `basketno` int(11) default NULL,
2502 `biblioitemnumber` int(11) default NULL,
2503 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2504 `rrp` decimal(13,2) default NULL,
2505 `ecost` decimal(13,2) default NULL,
2506 `gst` decimal(13,2) default NULL,
2507 `budget_id` int(11) NOT NULL,
2508 `budgetgroup_id` int(11) NOT NULL,
2509 `budgetdate` date default NULL,
2510 `sort1` varchar(80) default NULL,
2511 `sort2` varchar(80) default NULL,
2512 `sort1_authcat` varchar(10) default NULL,
2513 `sort2_authcat` varchar(10) default NULL,
2514 `uncertainprice` tinyint(1),
2515 PRIMARY KEY (`ordernumber`),
2516 KEY `basketno` (`basketno`),
2517 KEY `biblionumber` (`biblionumber`),
2518 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2519 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2520 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2523 -- Table structure for table `aqorders_items`
2526 DROP TABLE IF EXISTS `aqorders_items`;
2527 CREATE TABLE `aqorders_items` (
2528 `ordernumber` int(11) NOT NULL,
2529 `itemnumber` int(11) NOT NULL,
2530 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2531 PRIMARY KEY (`itemnumber`),
2532 KEY `ordernumber` (`ordernumber`)
2533 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2536 -- Table structure for table `fieldmapping`
2539 DROP TABLE IF EXISTS `fieldmapping`;
2540 CREATE TABLE `fieldmapping` (
2541 `id` int(11) NOT NULL auto_increment,
2542 `field` varchar(255) NOT NULL,
2543 `frameworkcode` char(4) NOT NULL default '',
2544 `fieldcode` char(3) NOT NULL,
2545 `subfieldcode` char(1) NOT NULL,
2547 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2550 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2551 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2552 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2553 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2554 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2555 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2556 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2557 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;