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 `enrolmentperioddate` DATE NULL DEFAULT NULL,
409 `upperagelimit` smallint(6) default NULL,
410 `dateofbirthrequired` tinyint(1) default NULL,
411 `finetype` varchar(30) default NULL,
412 `bulk` tinyint(1) default NULL,
413 `enrolmentfee` decimal(28,6) default NULL,
414 `overduenoticerequired` tinyint(1) default NULL,
415 `issuelimit` smallint(6) default NULL,
416 `reservefee` decimal(28,6) default NULL,
417 `category_type` varchar(1) NOT NULL default 'A',
418 PRIMARY KEY (`categorycode`),
419 UNIQUE KEY `categorycode` (`categorycode`)
420 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
423 -- Table structure for table `borrower_branch_circ_rules`
426 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
427 CREATE TABLE `branch_borrower_circ_rules` (
428 `branchcode` VARCHAR(10) NOT NULL,
429 `categorycode` VARCHAR(10) NOT NULL,
430 `maxissueqty` int(4) default NULL,
431 PRIMARY KEY (`categorycode`, `branchcode`),
432 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
433 ON DELETE CASCADE ON UPDATE CASCADE,
434 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
435 ON DELETE CASCADE ON UPDATE CASCADE
436 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
439 -- Table structure for table `default_borrower_circ_rules`
442 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
443 CREATE TABLE `default_borrower_circ_rules` (
444 `categorycode` VARCHAR(10) NOT NULL,
445 `maxissueqty` int(4) default NULL,
446 PRIMARY KEY (`categorycode`),
447 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
448 ON DELETE CASCADE ON UPDATE CASCADE
449 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
452 -- Table structure for table `default_branch_circ_rules`
455 DROP TABLE IF EXISTS `default_branch_circ_rules`;
456 CREATE TABLE `default_branch_circ_rules` (
457 `branchcode` VARCHAR(10) NOT NULL,
458 `maxissueqty` int(4) default NULL,
459 `holdallowed` tinyint(1) default NULL,
460 PRIMARY KEY (`branchcode`),
461 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
462 ON DELETE CASCADE ON UPDATE CASCADE
463 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
466 -- Table structure for table `default_branch_item_rules`
469 CREATE TABLE `default_branch_item_rules` (
470 `itemtype` varchar(10) NOT NULL,
471 `holdallowed` tinyint(1) default NULL,
472 PRIMARY KEY (`itemtype`),
473 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
474 ON DELETE CASCADE ON UPDATE CASCADE
475 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
478 -- Table structure for table `default_circ_rules`
481 DROP TABLE IF EXISTS `default_circ_rules`;
482 CREATE TABLE `default_circ_rules` (
483 `singleton` enum('singleton') NOT NULL default 'singleton',
484 `maxissueqty` int(4) default NULL,
485 `holdallowed` int(1) default NULL,
486 PRIMARY KEY (`singleton`)
487 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
490 -- Table structure for table `cities`
493 DROP TABLE IF EXISTS `cities`;
494 CREATE TABLE `cities` (
495 `cityid` int(11) NOT NULL auto_increment,
496 `city_name` varchar(100) NOT NULL default '',
497 `city_zipcode` varchar(20) default NULL,
498 PRIMARY KEY (`cityid`)
499 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
502 -- Table structure for table `class_sort_rules`
505 DROP TABLE IF EXISTS `class_sort_rules`;
506 CREATE TABLE `class_sort_rules` (
507 `class_sort_rule` varchar(10) NOT NULL default '',
508 `description` mediumtext,
509 `sort_routine` varchar(30) NOT NULL default '',
510 PRIMARY KEY (`class_sort_rule`),
511 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
512 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
515 -- Table structure for table `class_sources`
518 DROP TABLE IF EXISTS `class_sources`;
519 CREATE TABLE `class_sources` (
520 `cn_source` varchar(10) NOT NULL default '',
521 `description` mediumtext,
522 `used` tinyint(4) NOT NULL default 0,
523 `class_sort_rule` varchar(10) NOT NULL default '',
524 PRIMARY KEY (`cn_source`),
525 UNIQUE KEY `cn_source_idx` (`cn_source`),
526 KEY `used_idx` (`used`),
527 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
531 -- Table structure for table `currency`
534 DROP TABLE IF EXISTS `currency`;
535 CREATE TABLE `currency` (
536 `currency` varchar(10) NOT NULL default '',
537 `symbol` varchar(5) default NULL,
538 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
539 `rate` float(7,5) default NULL,
540 `active` tinyint(1) default NULL,
541 PRIMARY KEY (`currency`)
542 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
545 -- Table structure for table `deletedbiblio`
548 DROP TABLE IF EXISTS `deletedbiblio`;
549 CREATE TABLE `deletedbiblio` (
550 `biblionumber` int(11) NOT NULL default 0,
551 `frameworkcode` varchar(4) NOT NULL default '',
554 `unititle` mediumtext,
556 `serial` tinyint(1) default NULL,
557 `seriestitle` mediumtext,
558 `copyrightdate` smallint(6) default NULL,
559 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
560 `datecreated` DATE NOT NULL,
561 `abstract` mediumtext,
562 PRIMARY KEY (`biblionumber`),
563 KEY `blbnoidx` (`biblionumber`)
564 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
567 -- Table structure for table `deletedbiblioitems`
570 DROP TABLE IF EXISTS `deletedbiblioitems`;
571 CREATE TABLE `deletedbiblioitems` (
572 `biblioitemnumber` int(11) NOT NULL default 0,
573 `biblionumber` int(11) NOT NULL default 0,
576 `itemtype` varchar(10) default NULL,
577 `isbn` varchar(30) default NULL,
578 `issn` varchar(9) default NULL,
579 `publicationyear` text,
580 `publishercode` varchar(255) default NULL,
581 `volumedate` date default NULL,
583 `collectiontitle` mediumtext default NULL,
584 `collectionissn` text default NULL,
585 `collectionvolume` mediumtext default NULL,
586 `editionstatement` text default NULL,
587 `editionresponsibility` text default NULL,
588 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
589 `illus` varchar(255) default NULL,
590 `pages` varchar(255) default NULL,
592 `size` varchar(255) default NULL,
593 `place` varchar(255) default NULL,
594 `lccn` varchar(25) default NULL,
596 `url` varchar(255) default NULL,
597 `cn_source` varchar(10) default NULL,
598 `cn_class` varchar(30) default NULL,
599 `cn_item` varchar(10) default NULL,
600 `cn_suffix` varchar(10) default NULL,
601 `cn_sort` varchar(30) default NULL,
602 `totalissues` int(10),
603 `marcxml` longtext NOT NULL,
604 PRIMARY KEY (`biblioitemnumber`),
605 KEY `bibinoidx` (`biblioitemnumber`),
606 KEY `bibnoidx` (`biblionumber`),
608 KEY `publishercode` (`publishercode`)
609 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
612 -- Table structure for table `deletedborrowers`
615 DROP TABLE IF EXISTS `deletedborrowers`;
616 CREATE TABLE `deletedborrowers` (
617 `borrowernumber` int(11) NOT NULL default 0,
618 `cardnumber` varchar(9) NOT NULL default '',
619 `surname` mediumtext NOT NULL,
622 `othernames` mediumtext,
624 `streetnumber` varchar(10) default NULL,
625 `streettype` varchar(50) default NULL,
626 `address` mediumtext NOT NULL,
628 `city` mediumtext NOT NULL,
629 `zipcode` varchar(25) default NULL,
633 `mobile` varchar(50) default NULL,
637 `B_streetnumber` varchar(10) default NULL,
638 `B_streettype` varchar(50) default NULL,
639 `B_address` varchar(100) default NULL,
640 `B_address2` text default NULL,
642 `B_zipcode` varchar(25) default NULL,
645 `B_phone` mediumtext,
646 `dateofbirth` date default NULL,
647 `branchcode` varchar(10) NOT NULL default '',
648 `categorycode` varchar(10) default NULL,
649 `dateenrolled` date default NULL,
650 `dateexpiry` date default NULL,
651 `gonenoaddress` tinyint(1) default NULL,
652 `lost` tinyint(1) default NULL,
653 `debarred` tinyint(1) default NULL,
654 `contactname` mediumtext,
655 `contactfirstname` text,
657 `guarantorid` int(11) default NULL,
658 `borrowernotes` mediumtext,
659 `relationship` varchar(100) default NULL,
660 `ethnicity` varchar(50) default NULL,
661 `ethnotes` varchar(255) default NULL,
662 `sex` varchar(1) default NULL,
663 `password` varchar(30) default NULL,
664 `flags` int(11) default NULL,
665 `userid` varchar(30) default NULL,
666 `opacnote` mediumtext,
667 `contactnote` varchar(255) default NULL,
668 `sort1` varchar(80) default NULL,
669 `sort2` varchar(80) default NULL,
670 `altcontactfirstname` varchar(255) default NULL,
671 `altcontactsurname` varchar(255) default NULL,
672 `altcontactaddress1` varchar(255) default NULL,
673 `altcontactaddress2` varchar(255) default NULL,
674 `altcontactaddress3` varchar(255) default NULL,
675 `altcontactzipcode` varchar(50) default NULL,
676 `altcontactcountry` text default NULL,
677 `altcontactphone` varchar(50) default NULL,
678 `smsalertnumber` varchar(50) default NULL,
679 KEY `borrowernumber` (`borrowernumber`),
680 KEY `cardnumber` (`cardnumber`)
681 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
684 -- Table structure for table `deleteditems`
687 DROP TABLE IF EXISTS `deleteditems`;
688 CREATE TABLE `deleteditems` (
689 `itemnumber` int(11) NOT NULL default 0,
690 `biblionumber` int(11) NOT NULL default 0,
691 `biblioitemnumber` int(11) NOT NULL default 0,
692 `barcode` varchar(20) default NULL,
693 `dateaccessioned` date default NULL,
694 `booksellerid` mediumtext default NULL,
695 `homebranch` varchar(10) default NULL,
696 `price` decimal(8,2) default NULL,
697 `replacementprice` decimal(8,2) default NULL,
698 `replacementpricedate` date default NULL,
699 `datelastborrowed` date default NULL,
700 `datelastseen` date default NULL,
701 `stack` tinyint(1) default NULL,
702 `notforloan` tinyint(1) NOT NULL default 0,
703 `damaged` tinyint(1) NOT NULL default 0,
704 `itemlost` tinyint(1) NOT NULL default 0,
705 `wthdrawn` tinyint(1) NOT NULL default 0,
706 `itemcallnumber` varchar(255) default NULL,
707 `issues` smallint(6) default NULL,
708 `renewals` smallint(6) default NULL,
709 `reserves` smallint(6) default NULL,
710 `restricted` tinyint(1) default NULL,
711 `itemnotes` mediumtext,
712 `holdingbranch` varchar(10) default NULL,
713 `paidfor` mediumtext,
714 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
715 `location` varchar(80) default NULL,
716 `permanent_location` varchar(80) default NULL,
717 `onloan` date default NULL,
718 `cn_source` varchar(10) default NULL,
719 `cn_sort` varchar(30) default NULL,
720 `ccode` varchar(10) default NULL,
721 `materials` varchar(10) default NULL,
722 `uri` varchar(255) default NULL,
723 `itype` varchar(10) default NULL,
724 `more_subfields_xml` longtext default NULL,
725 `enumchron` varchar(80) default NULL,
726 `copynumber` varchar(32) default NULL,
727 `stocknumber` varchar(32) default NULL,
729 PRIMARY KEY (`itemnumber`),
730 KEY `delitembarcodeidx` (`barcode`),
731 KEY `delitemstocknumberidx` (`stocknumber`),
732 KEY `delitembinoidx` (`biblioitemnumber`),
733 KEY `delitembibnoidx` (`biblionumber`),
734 KEY `delhomebranch` (`homebranch`),
735 KEY `delholdingbranch` (`holdingbranch`)
736 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
739 -- Table structure for table `ethnicity`
742 DROP TABLE IF EXISTS `ethnicity`;
743 CREATE TABLE `ethnicity` (
744 `code` varchar(10) NOT NULL default '',
745 `name` varchar(255) default NULL,
747 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
750 -- Table structure for table `export_format`
753 DROP TABLE IF EXISTS `export_format`;
754 CREATE TABLE `export_format` (
755 `export_format_id` int(11) NOT NULL auto_increment,
756 `profile` varchar(255) NOT NULL,
757 `description` mediumtext NOT NULL,
758 `marcfields` mediumtext NOT NULL,
759 `csv_separator` varchar(2) NOT NULL,
760 `field_separator` varchar(2) NOT NULL,
761 `subfield_separator` varchar(2) NOT NULL,
762 `encoding` varchar(255) NOT NULL,
763 PRIMARY KEY (`export_format_id`)
764 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
768 -- Table structure for table `hold_fill_targets`
771 DROP TABLE IF EXISTS `hold_fill_targets`;
772 CREATE TABLE hold_fill_targets (
773 `borrowernumber` int(11) NOT NULL,
774 `biblionumber` int(11) NOT NULL,
775 `itemnumber` int(11) NOT NULL,
776 `source_branchcode` varchar(10) default NULL,
777 `item_level_request` tinyint(4) NOT NULL default 0,
778 PRIMARY KEY `itemnumber` (`itemnumber`),
779 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
780 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
781 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
782 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
783 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
784 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
785 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
786 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
787 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
788 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
791 -- Table structure for table `import_batches`
794 DROP TABLE IF EXISTS `import_batches`;
795 CREATE TABLE `import_batches` (
796 `import_batch_id` int(11) NOT NULL auto_increment,
797 `matcher_id` int(11) default NULL,
798 `template_id` int(11) default NULL,
799 `branchcode` varchar(10) default NULL,
800 `num_biblios` int(11) NOT NULL default 0,
801 `num_items` int(11) NOT NULL default 0,
802 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
803 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
804 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
805 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
806 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
807 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
808 `file_name` varchar(100),
809 `comments` mediumtext,
810 PRIMARY KEY (`import_batch_id`),
811 KEY `branchcode` (`branchcode`)
812 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
815 -- Table structure for table `import_records`
818 DROP TABLE IF EXISTS `import_records`;
819 CREATE TABLE `import_records` (
820 `import_record_id` int(11) NOT NULL auto_increment,
821 `import_batch_id` int(11) NOT NULL,
822 `branchcode` varchar(10) default NULL,
823 `record_sequence` int(11) NOT NULL default 0,
824 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
825 `import_date` DATE default NULL,
826 `marc` longblob NOT NULL,
827 `marcxml` longtext NOT NULL,
828 `marcxml_old` longtext NOT NULL,
829 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
830 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
831 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
832 `import_error` mediumtext,
833 `encoding` varchar(40) NOT NULL default '',
834 `z3950random` varchar(40) default NULL,
835 PRIMARY KEY (`import_record_id`),
836 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
837 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
838 KEY `branchcode` (`branchcode`),
839 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
840 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
843 -- Table structure for `import_record_matches`
845 DROP TABLE IF EXISTS `import_record_matches`;
846 CREATE TABLE `import_record_matches` (
847 `import_record_id` int(11) NOT NULL,
848 `candidate_match_id` int(11) NOT NULL,
849 `score` int(11) NOT NULL default 0,
850 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
851 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
852 KEY `record_score` (`import_record_id`, `score`)
853 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
856 -- Table structure for table `import_biblios`
859 DROP TABLE IF EXISTS `import_biblios`;
860 CREATE TABLE `import_biblios` (
861 `import_record_id` int(11) NOT NULL,
862 `matched_biblionumber` int(11) default NULL,
863 `control_number` varchar(25) default NULL,
864 `original_source` varchar(25) default NULL,
865 `title` varchar(128) default NULL,
866 `author` varchar(80) default NULL,
867 `isbn` varchar(30) default NULL,
868 `issn` varchar(9) default NULL,
869 `has_items` tinyint(1) NOT NULL default 0,
870 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
871 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
872 KEY `matched_biblionumber` (`matched_biblionumber`),
873 KEY `title` (`title`),
875 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
878 -- Table structure for table `import_items`
881 DROP TABLE IF EXISTS `import_items`;
882 CREATE TABLE `import_items` (
883 `import_items_id` int(11) NOT NULL auto_increment,
884 `import_record_id` int(11) NOT NULL,
885 `itemnumber` int(11) default NULL,
886 `branchcode` varchar(10) default NULL,
887 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
888 `marcxml` longtext NOT NULL,
889 `import_error` mediumtext,
890 PRIMARY KEY (`import_items_id`),
891 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
892 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
893 KEY `itemnumber` (`itemnumber`),
894 KEY `branchcode` (`branchcode`)
895 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
898 -- Table structure for table `issues`
901 DROP TABLE IF EXISTS `issues`;
902 CREATE TABLE `issues` (
903 `borrowernumber` int(11) default NULL,
904 `itemnumber` int(11) default NULL,
905 `date_due` date default NULL,
906 `branchcode` varchar(10) default NULL,
907 `issuingbranch` varchar(18) default NULL,
908 `returndate` date default NULL,
909 `lastreneweddate` date default NULL,
910 `return` varchar(4) default NULL,
911 `renewals` tinyint(4) default NULL,
912 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
913 `issuedate` date default NULL,
914 KEY `issuesborridx` (`borrowernumber`),
915 KEY `issuesitemidx` (`itemnumber`),
916 KEY `bordate` (`borrowernumber`,`timestamp`),
917 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
918 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
919 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
922 -- Table structure for table `issuingrules`
925 DROP TABLE IF EXISTS `issuingrules`;
926 CREATE TABLE `issuingrules` (
927 `categorycode` varchar(10) NOT NULL default '',
928 `itemtype` varchar(10) NOT NULL default '',
929 `restrictedtype` tinyint(1) default NULL,
930 `rentaldiscount` decimal(28,6) default NULL,
931 `reservecharge` decimal(28,6) default NULL,
932 `fine` decimal(28,6) default NULL,
933 `finedays` int(11) default NULL,
934 `firstremind` int(11) default NULL,
935 `chargeperiod` int(11) default NULL,
936 `accountsent` int(11) default NULL,
937 `chargename` varchar(100) default NULL,
938 `maxissueqty` int(4) default NULL,
939 `issuelength` int(4) default NULL,
940 `renewalsallowed` smallint(6) NOT NULL default "0",
941 `reservesallowed` smallint(6) NOT NULL default "0",
942 `branchcode` varchar(10) NOT NULL default '',
943 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
944 KEY `categorycode` (`categorycode`),
945 KEY `itemtype` (`itemtype`)
946 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
949 -- Table structure for table `items`
952 DROP TABLE IF EXISTS `items`;
953 CREATE TABLE `items` (
954 `itemnumber` int(11) NOT NULL auto_increment,
955 `biblionumber` int(11) NOT NULL default 0,
956 `biblioitemnumber` int(11) NOT NULL default 0,
957 `barcode` varchar(20) default NULL,
958 `dateaccessioned` date default NULL,
959 `booksellerid` mediumtext default NULL,
960 `homebranch` varchar(10) default NULL,
961 `price` decimal(8,2) default NULL,
962 `replacementprice` decimal(8,2) default NULL,
963 `replacementpricedate` date default NULL,
964 `datelastborrowed` date default NULL,
965 `datelastseen` date default NULL,
966 `stack` tinyint(1) default NULL,
967 `notforloan` tinyint(1) NOT NULL default 0,
968 `damaged` tinyint(1) NOT NULL default 0,
969 `itemlost` tinyint(1) NOT NULL default 0,
970 `wthdrawn` tinyint(1) NOT NULL default 0,
971 `itemcallnumber` varchar(255) default NULL,
972 `issues` smallint(6) default NULL,
973 `renewals` smallint(6) default NULL,
974 `reserves` smallint(6) default NULL,
975 `restricted` tinyint(1) default NULL,
976 `itemnotes` mediumtext,
977 `holdingbranch` varchar(10) default NULL,
978 `paidfor` mediumtext,
979 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
980 `location` varchar(80) default NULL,
981 `permanent_location` varchar(80) default NULL,
982 `onloan` date default NULL,
983 `cn_source` varchar(10) default NULL,
984 `cn_sort` varchar(30) default NULL,
985 `ccode` varchar(10) default NULL,
986 `materials` varchar(10) default NULL,
987 `uri` varchar(255) default NULL,
988 `itype` varchar(10) default NULL,
989 `more_subfields_xml` longtext default NULL,
990 `enumchron` varchar(80) default NULL,
991 `copynumber` varchar(32) default NULL,
992 `stocknumber` varchar(32) default NULL,
993 PRIMARY KEY (`itemnumber`),
994 UNIQUE KEY `itembarcodeidx` (`barcode`),
995 UNIQUE KEY `itemstocknumberidx` (`stocknumber`),
996 KEY `itembinoidx` (`biblioitemnumber`),
997 KEY `itembibnoidx` (`biblionumber`),
998 KEY `homebranch` (`homebranch`),
999 KEY `holdingbranch` (`holdingbranch`),
1000 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1001 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1002 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1003 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1006 -- Table structure for table `itemtypes`
1009 DROP TABLE IF EXISTS `itemtypes`;
1010 CREATE TABLE `itemtypes` (
1011 `itemtype` varchar(10) NOT NULL default '',
1012 `description` mediumtext,
1013 `rentalcharge` double(16,4) default NULL,
1014 `notforloan` smallint(6) default NULL,
1015 `imageurl` varchar(200) default NULL,
1017 PRIMARY KEY (`itemtype`),
1018 UNIQUE KEY `itemtype` (`itemtype`)
1019 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1022 -- Table structure for table `creator_batches`
1025 DROP TABLE IF EXISTS `creator_batches`;
1026 SET @saved_cs_client = @@character_set_client;
1027 SET character_set_client = utf8;
1028 CREATE TABLE `creator_batches` (
1029 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1030 `batch_id` int(10) NOT NULL DEFAULT '1',
1031 `item_number` int(11) DEFAULT NULL,
1032 `borrower_number` int(11) DEFAULT NULL,
1033 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1034 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1035 `creator` char(15) NOT NULL DEFAULT 'Labels',
1036 PRIMARY KEY (`label_id`),
1037 KEY `branch_fk_constraint` (`branch_code`),
1038 KEY `item_fk_constraint` (`item_number`),
1039 KEY `borrower_fk_constraint` (`borrower_number`),
1040 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1041 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1042 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1043 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1046 -- Table structure for table `creator_images`
1049 DROP TABLE IF EXISTS `creator_images`;
1050 SET @saved_cs_client = @@character_set_client;
1051 SET character_set_client = utf8;
1052 CREATE TABLE `creator_images` (
1053 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1054 `imagefile` mediumblob,
1055 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1056 PRIMARY KEY (`image_id`),
1057 UNIQUE KEY `image_name_index` (`image_name`)
1058 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1061 -- Table structure for table `creator_layouts`
1064 DROP TABLE IF EXISTS `creator_layouts`;
1065 SET @saved_cs_client = @@character_set_client;
1066 SET character_set_client = utf8;
1067 CREATE TABLE `creator_layouts` (
1068 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1069 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1070 `start_label` int(2) NOT NULL DEFAULT '1',
1071 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1072 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1073 `guidebox` int(1) DEFAULT '0',
1074 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1075 `font_size` int(4) NOT NULL DEFAULT '10',
1076 `units` char(20) NOT NULL DEFAULT 'POINT',
1077 `callnum_split` int(1) DEFAULT '0',
1078 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1079 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1080 `layout_xml` text NOT NULL,
1081 `creator` char(15) NOT NULL DEFAULT 'Labels',
1082 PRIMARY KEY (`layout_id`)
1083 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1086 -- Table structure for table `creator_templates`
1089 DROP TABLE IF EXISTS `creator_templates`;
1090 SET @saved_cs_client = @@character_set_client;
1091 SET character_set_client = utf8;
1092 CREATE TABLE `creator_templates` (
1093 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1094 `profile_id` int(4) DEFAULT NULL,
1095 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1096 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1097 `page_width` float NOT NULL DEFAULT '0',
1098 `page_height` float NOT NULL DEFAULT '0',
1099 `label_width` float NOT NULL DEFAULT '0',
1100 `label_height` float NOT NULL DEFAULT '0',
1101 `top_text_margin` float NOT NULL DEFAULT '0',
1102 `left_text_margin` float NOT NULL DEFAULT '0',
1103 `top_margin` float NOT NULL DEFAULT '0',
1104 `left_margin` float NOT NULL DEFAULT '0',
1105 `cols` int(2) NOT NULL DEFAULT '0',
1106 `rows` int(2) NOT NULL DEFAULT '0',
1107 `col_gap` float NOT NULL DEFAULT '0',
1108 `row_gap` float NOT NULL DEFAULT '0',
1109 `units` char(20) NOT NULL DEFAULT 'POINT',
1110 `creator` char(15) NOT NULL DEFAULT 'Labels',
1111 PRIMARY KEY (`template_id`),
1112 KEY `template_profile_fk_constraint` (`profile_id`)
1113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1116 -- Table structure for table `letter`
1119 DROP TABLE IF EXISTS `letter`;
1120 CREATE TABLE `letter` (
1121 `module` varchar(20) NOT NULL default '',
1122 `code` varchar(20) NOT NULL default '',
1123 `name` varchar(100) NOT NULL default '',
1124 `title` varchar(200) NOT NULL default '',
1126 PRIMARY KEY (`module`,`code`)
1127 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1130 -- Table structure for table `marc_subfield_structure`
1133 DROP TABLE IF EXISTS `marc_subfield_structure`;
1134 CREATE TABLE `marc_subfield_structure` (
1135 `tagfield` varchar(3) NOT NULL default '',
1136 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1137 `liblibrarian` varchar(255) NOT NULL default '',
1138 `libopac` varchar(255) NOT NULL default '',
1139 `repeatable` tinyint(4) NOT NULL default 0,
1140 `mandatory` tinyint(4) NOT NULL default 0,
1141 `kohafield` varchar(40) default NULL,
1142 `tab` tinyint(1) default NULL,
1143 `authorised_value` varchar(20) default NULL,
1144 `authtypecode` varchar(20) default NULL,
1145 `value_builder` varchar(80) default NULL,
1146 `isurl` tinyint(1) default NULL,
1147 `hidden` tinyint(1) default NULL,
1148 `frameworkcode` varchar(4) NOT NULL default '',
1149 `seealso` varchar(1100) default NULL,
1150 `link` varchar(80) default NULL,
1151 `defaultvalue` text default NULL,
1152 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1153 KEY `kohafield_2` (`kohafield`),
1154 KEY `tab` (`frameworkcode`,`tab`),
1155 KEY `kohafield` (`frameworkcode`,`kohafield`)
1156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1159 -- Table structure for table `marc_tag_structure`
1162 DROP TABLE IF EXISTS `marc_tag_structure`;
1163 CREATE TABLE `marc_tag_structure` (
1164 `tagfield` varchar(3) NOT NULL default '',
1165 `liblibrarian` varchar(255) NOT NULL default '',
1166 `libopac` varchar(255) NOT NULL default '',
1167 `repeatable` tinyint(4) NOT NULL default 0,
1168 `mandatory` tinyint(4) NOT NULL default 0,
1169 `authorised_value` varchar(10) default NULL,
1170 `frameworkcode` varchar(4) NOT NULL default '',
1171 PRIMARY KEY (`frameworkcode`,`tagfield`)
1172 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1175 -- Table structure for table `marc_matchers`
1178 DROP TABLE IF EXISTS `marc_matchers`;
1179 CREATE TABLE `marc_matchers` (
1180 `matcher_id` int(11) NOT NULL auto_increment,
1181 `code` varchar(10) NOT NULL default '',
1182 `description` varchar(255) NOT NULL default '',
1183 `record_type` varchar(10) NOT NULL default 'biblio',
1184 `threshold` int(11) NOT NULL default 0,
1185 PRIMARY KEY (`matcher_id`),
1186 KEY `code` (`code`),
1187 KEY `record_type` (`record_type`)
1188 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1191 -- Table structure for table `matchpoints`
1193 DROP TABLE IF EXISTS `matchpoints`;
1194 CREATE TABLE `matchpoints` (
1195 `matcher_id` int(11) NOT NULL,
1196 `matchpoint_id` int(11) NOT NULL auto_increment,
1197 `search_index` varchar(30) NOT NULL default '',
1198 `score` int(11) NOT NULL default 0,
1199 PRIMARY KEY (`matchpoint_id`),
1200 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1201 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1202 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1206 -- Table structure for table `matchpoint_components`
1208 DROP TABLE IF EXISTS `matchpoint_components`;
1209 CREATE TABLE `matchpoint_components` (
1210 `matchpoint_id` int(11) NOT NULL,
1211 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1212 sequence int(11) NOT NULL default 0,
1213 tag varchar(3) NOT NULL default '',
1214 subfields varchar(40) NOT NULL default '',
1215 offset int(4) NOT NULL default 0,
1216 length int(4) NOT NULL default 0,
1217 PRIMARY KEY (`matchpoint_component_id`),
1218 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1219 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1220 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1221 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1224 -- Table structure for table `matcher_component_norms`
1226 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1227 CREATE TABLE `matchpoint_component_norms` (
1228 `matchpoint_component_id` int(11) NOT NULL,
1229 `sequence` int(11) NOT NULL default 0,
1230 `norm_routine` varchar(50) NOT NULL default '',
1231 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1232 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1233 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1234 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1237 -- Table structure for table `matcher_matchpoints`
1239 DROP TABLE IF EXISTS `matcher_matchpoints`;
1240 CREATE TABLE `matcher_matchpoints` (
1241 `matcher_id` int(11) NOT NULL,
1242 `matchpoint_id` int(11) NOT NULL,
1243 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1244 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1245 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1246 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1247 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1250 -- Table structure for table `matchchecks`
1252 DROP TABLE IF EXISTS `matchchecks`;
1253 CREATE TABLE `matchchecks` (
1254 `matcher_id` int(11) NOT NULL,
1255 `matchcheck_id` int(11) NOT NULL auto_increment,
1256 `source_matchpoint_id` int(11) NOT NULL,
1257 `target_matchpoint_id` int(11) NOT NULL,
1258 PRIMARY KEY (`matchcheck_id`),
1259 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1260 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1261 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1262 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1263 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1264 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1268 -- Table structure for table `notifys`
1271 DROP TABLE IF EXISTS `notifys`;
1272 CREATE TABLE `notifys` (
1273 `notify_id` int(11) NOT NULL default 0,
1274 `borrowernumber` int(11) NOT NULL default 0,
1275 `itemnumber` int(11) NOT NULL default 0,
1276 `notify_date` date default NULL,
1277 `notify_send_date` date default NULL,
1278 `notify_level` int(1) NOT NULL default 0,
1279 `method` varchar(20) NOT NULL default ''
1280 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1283 -- Table structure for table `nozebra`
1286 DROP TABLE IF EXISTS `nozebra`;
1287 CREATE TABLE `nozebra` (
1288 `server` varchar(20) NOT NULL,
1289 `indexname` varchar(40) NOT NULL,
1290 `value` varchar(250) NOT NULL,
1291 `biblionumbers` longtext NOT NULL,
1292 KEY `indexname` (`server`,`indexname`),
1293 KEY `value` (`server`,`value`))
1294 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1297 -- Table structure for table `old_issues`
1300 DROP TABLE IF EXISTS `old_issues`;
1301 CREATE TABLE `old_issues` (
1302 `borrowernumber` int(11) default NULL,
1303 `itemnumber` int(11) default NULL,
1304 `date_due` date default NULL,
1305 `branchcode` varchar(10) default NULL,
1306 `issuingbranch` varchar(18) default NULL,
1307 `returndate` date default NULL,
1308 `lastreneweddate` date default NULL,
1309 `return` varchar(4) default NULL,
1310 `renewals` tinyint(4) default NULL,
1311 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1312 `issuedate` date default NULL,
1313 KEY `old_issuesborridx` (`borrowernumber`),
1314 KEY `old_issuesitemidx` (`itemnumber`),
1315 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1316 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1317 ON DELETE SET NULL ON UPDATE SET NULL,
1318 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1319 ON DELETE SET NULL ON UPDATE SET NULL
1320 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1323 -- Table structure for table `old_reserves`
1325 DROP TABLE IF EXISTS `old_reserves`;
1326 CREATE TABLE `old_reserves` (
1327 `borrowernumber` int(11) default NULL,
1328 `reservedate` date default NULL,
1329 `biblionumber` int(11) default NULL,
1330 `constrainttype` varchar(1) default NULL,
1331 `branchcode` varchar(10) default NULL,
1332 `notificationdate` date default NULL,
1333 `reminderdate` date default NULL,
1334 `cancellationdate` date default NULL,
1335 `reservenotes` mediumtext,
1336 `priority` smallint(6) default NULL,
1337 `found` varchar(1) default NULL,
1338 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1339 `itemnumber` int(11) default NULL,
1340 `waitingdate` date default NULL,
1341 `expirationdate` DATE DEFAULT NULL,
1342 `lowestPriority` tinyint(1) NOT NULL,
1343 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1344 KEY `old_reserves_biblionumber` (`biblionumber`),
1345 KEY `old_reserves_itemnumber` (`itemnumber`),
1346 KEY `old_reserves_branchcode` (`branchcode`),
1347 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1348 ON DELETE SET NULL ON UPDATE SET NULL,
1349 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1350 ON DELETE SET NULL ON UPDATE SET NULL,
1351 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1352 ON DELETE SET NULL ON UPDATE SET NULL
1353 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1356 -- Table structure for table `opac_news`
1359 DROP TABLE IF EXISTS `opac_news`;
1360 CREATE TABLE `opac_news` (
1361 `idnew` int(10) unsigned NOT NULL auto_increment,
1362 `title` varchar(250) NOT NULL default '',
1363 `new` text NOT NULL,
1364 `lang` varchar(25) NOT NULL default '',
1365 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1366 `expirationdate` date default NULL,
1367 `number` int(11) default NULL,
1368 PRIMARY KEY (`idnew`)
1369 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1372 -- Table structure for table `overduerules`
1375 DROP TABLE IF EXISTS `overduerules`;
1376 CREATE TABLE `overduerules` (
1377 `branchcode` varchar(10) NOT NULL default '',
1378 `categorycode` varchar(10) NOT NULL default '',
1379 `delay1` int(4) default 0,
1380 `letter1` varchar(20) default NULL,
1381 `debarred1` varchar(1) default 0,
1382 `delay2` int(4) default 0,
1383 `debarred2` varchar(1) default 0,
1384 `letter2` varchar(20) default NULL,
1385 `delay3` int(4) default 0,
1386 `letter3` varchar(20) default NULL,
1387 `debarred3` int(1) default 0,
1388 PRIMARY KEY (`branchcode`,`categorycode`)
1389 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1392 -- Table structure for table `patroncards`
1395 DROP TABLE IF EXISTS `patroncards`;
1396 CREATE TABLE `patroncards` (
1397 `cardid` int(11) NOT NULL auto_increment,
1398 `batch_id` varchar(10) NOT NULL default '1',
1399 `borrowernumber` int(11) NOT NULL,
1400 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1401 PRIMARY KEY (`cardid`),
1402 KEY `patroncards_ibfk_1` (`borrowernumber`),
1403 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1404 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1407 -- Table structure for table `patronimage`
1410 DROP TABLE IF EXISTS `patronimage`;
1411 CREATE TABLE `patronimage` (
1412 `cardnumber` varchar(16) NOT NULL,
1413 `mimetype` varchar(15) NOT NULL,
1414 `imagefile` mediumblob NOT NULL,
1415 PRIMARY KEY (`cardnumber`),
1416 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1417 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1420 -- Table structure for table `printers`
1423 DROP TABLE IF EXISTS `printers`;
1424 CREATE TABLE `printers` (
1425 `printername` varchar(40) NOT NULL default '',
1426 `printqueue` varchar(20) default NULL,
1427 `printtype` varchar(20) default NULL,
1428 PRIMARY KEY (`printername`)
1429 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1432 -- Table structure for table `printers_profile`
1435 DROP TABLE IF EXISTS `printers_profile`;
1436 CREATE TABLE `printers_profile` (
1437 `profile_id` int(4) NOT NULL auto_increment,
1438 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1439 `template_id` int(4) NOT NULL default '0',
1440 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1441 `offset_horz` float NOT NULL default '0',
1442 `offset_vert` float NOT NULL default '0',
1443 `creep_horz` float NOT NULL default '0',
1444 `creep_vert` float NOT NULL default '0',
1445 `units` char(20) NOT NULL default 'POINT',
1446 `creator` char(15) NOT NULL DEFAULT 'Labels',
1447 PRIMARY KEY (`profile_id`),
1448 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
1449 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1452 -- Table structure for table `repeatable_holidays`
1455 DROP TABLE IF EXISTS `repeatable_holidays`;
1456 CREATE TABLE `repeatable_holidays` (
1457 `id` int(11) NOT NULL auto_increment,
1458 `branchcode` varchar(10) NOT NULL default '',
1459 `weekday` smallint(6) default NULL,
1460 `day` smallint(6) default NULL,
1461 `month` smallint(6) default NULL,
1462 `title` varchar(50) NOT NULL default '',
1463 `description` text NOT NULL,
1465 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1468 -- Table structure for table `reports_dictionary`
1471 DROP TABLE IF EXISTS `reports_dictionary`;
1472 CREATE TABLE reports_dictionary (
1473 `id` int(11) NOT NULL auto_increment,
1474 `name` varchar(255) default NULL,
1476 `date_created` datetime default NULL,
1477 `date_modified` datetime default NULL,
1479 `area` int(11) default NULL,
1481 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1484 -- Table structure for table `reserveconstraints`
1487 DROP TABLE IF EXISTS `reserveconstraints`;
1488 CREATE TABLE `reserveconstraints` (
1489 `borrowernumber` int(11) NOT NULL default 0,
1490 `reservedate` date default NULL,
1491 `biblionumber` int(11) NOT NULL default 0,
1492 `biblioitemnumber` int(11) default NULL,
1493 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1497 -- Table structure for table `reserves`
1500 DROP TABLE IF EXISTS `reserves`;
1501 CREATE TABLE `reserves` (
1502 `borrowernumber` int(11) NOT NULL default 0,
1503 `reservedate` date default NULL,
1504 `biblionumber` int(11) NOT NULL default 0,
1505 `constrainttype` varchar(1) default NULL,
1506 `branchcode` varchar(10) default NULL,
1507 `notificationdate` date default NULL,
1508 `reminderdate` date default NULL,
1509 `cancellationdate` date default NULL,
1510 `reservenotes` mediumtext,
1511 `priority` smallint(6) default NULL,
1512 `found` varchar(1) default NULL,
1513 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1514 `itemnumber` int(11) default NULL,
1515 `waitingdate` date default NULL,
1516 `expirationdate` DATE DEFAULT NULL,
1517 `lowestPriority` tinyint(1) NOT NULL,
1518 KEY `borrowernumber` (`borrowernumber`),
1519 KEY `biblionumber` (`biblionumber`),
1520 KEY `itemnumber` (`itemnumber`),
1521 KEY `branchcode` (`branchcode`),
1522 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1523 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1524 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1525 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1526 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1529 -- Table structure for table `reviews`
1532 DROP TABLE IF EXISTS `reviews`;
1533 CREATE TABLE `reviews` (
1534 `reviewid` int(11) NOT NULL auto_increment,
1535 `borrowernumber` int(11) default NULL,
1536 `biblionumber` int(11) default NULL,
1538 `approved` tinyint(4) default NULL,
1539 `datereviewed` datetime default NULL,
1540 PRIMARY KEY (`reviewid`)
1541 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1544 -- Table structure for table `roadtype`
1547 DROP TABLE IF EXISTS `roadtype`;
1548 CREATE TABLE `roadtype` (
1549 `roadtypeid` int(11) NOT NULL auto_increment,
1550 `road_type` varchar(100) NOT NULL default '',
1551 PRIMARY KEY (`roadtypeid`)
1552 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1555 -- Table structure for table `saved_sql`
1558 DROP TABLE IF EXISTS `saved_sql`;
1559 CREATE TABLE saved_sql (
1560 `id` int(11) NOT NULL auto_increment,
1561 `borrowernumber` int(11) default NULL,
1562 `date_created` datetime default NULL,
1563 `last_modified` datetime default NULL,
1565 `last_run` datetime default NULL,
1566 `report_name` varchar(255) default NULL,
1567 `type` varchar(255) default NULL,
1570 KEY boridx (`borrowernumber`)
1571 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1575 -- Table structure for `saved_reports`
1578 DROP TABLE IF EXISTS `saved_reports`;
1579 CREATE TABLE saved_reports (
1580 `id` int(11) NOT NULL auto_increment,
1581 `report_id` int(11) default NULL,
1583 `date_run` datetime default NULL,
1585 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1589 -- Table structure for table `search_history`
1592 DROP TABLE IF EXISTS `search_history`;
1593 CREATE TABLE IF NOT EXISTS `search_history` (
1594 `userid` int(11) NOT NULL,
1595 `sessionid` varchar(32) NOT NULL,
1596 `query_desc` varchar(255) NOT NULL,
1597 `query_cgi` varchar(255) NOT NULL,
1598 `total` int(11) NOT NULL,
1599 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1600 KEY `userid` (`userid`),
1601 KEY `sessionid` (`sessionid`)
1602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1606 -- Table structure for table `serial`
1609 DROP TABLE IF EXISTS `serial`;
1610 CREATE TABLE `serial` (
1611 `serialid` int(11) NOT NULL auto_increment,
1612 `biblionumber` varchar(100) NOT NULL default '',
1613 `subscriptionid` varchar(100) NOT NULL default '',
1614 `serialseq` varchar(100) NOT NULL default '',
1615 `status` tinyint(4) NOT NULL default 0,
1616 `planneddate` date default NULL,
1618 `publisheddate` date default NULL,
1619 `itemnumber` text default NULL,
1620 `claimdate` date default NULL,
1621 `routingnotes` text,
1622 PRIMARY KEY (`serialid`)
1623 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1626 -- Table structure for table `sessions`
1629 DROP TABLE IF EXISTS sessions;
1630 CREATE TABLE sessions (
1631 `id` varchar(32) NOT NULL,
1632 `a_session` text NOT NULL,
1634 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1637 -- Table structure for table `special_holidays`
1640 DROP TABLE IF EXISTS `special_holidays`;
1641 CREATE TABLE `special_holidays` (
1642 `id` int(11) NOT NULL auto_increment,
1643 `branchcode` varchar(10) NOT NULL default '',
1644 `day` smallint(6) NOT NULL default 0,
1645 `month` smallint(6) NOT NULL default 0,
1646 `year` smallint(6) NOT NULL default 0,
1647 `isexception` smallint(1) NOT NULL default 1,
1648 `title` varchar(50) NOT NULL default '',
1649 `description` text NOT NULL,
1651 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1654 -- Table structure for table `statistics`
1657 DROP TABLE IF EXISTS `statistics`;
1658 CREATE TABLE `statistics` (
1659 `datetime` datetime default NULL,
1660 `branch` varchar(10) default NULL,
1661 `proccode` varchar(4) default NULL,
1662 `value` double(16,4) default NULL,
1663 `type` varchar(16) default NULL,
1665 `usercode` varchar(10) default NULL,
1666 `itemnumber` int(11) default NULL,
1667 `itemtype` varchar(10) default NULL,
1668 `borrowernumber` int(11) default NULL,
1669 `associatedborrower` int(11) default NULL,
1670 KEY `timeidx` (`datetime`)
1671 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1674 -- Table structure for table `stopwords`
1677 DROP TABLE IF EXISTS `stopwords`;
1678 CREATE TABLE `stopwords` (
1679 `word` varchar(255) default NULL
1680 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1683 -- Table structure for table `subscription`
1686 DROP TABLE IF EXISTS `subscription`;
1687 CREATE TABLE `subscription` (
1688 `biblionumber` int(11) NOT NULL default 0,
1689 `subscriptionid` int(11) NOT NULL auto_increment,
1690 `librarian` varchar(100) default '',
1691 `startdate` date default NULL,
1692 `aqbooksellerid` int(11) default 0,
1693 `cost` int(11) default 0,
1694 `aqbudgetid` int(11) default 0,
1695 `weeklength` int(11) default 0,
1696 `monthlength` int(11) default 0,
1697 `numberlength` int(11) default 0,
1698 `periodicity` tinyint(4) default 0,
1699 `dow` varchar(100) default '',
1700 `numberingmethod` varchar(100) default '',
1702 `status` varchar(100) NOT NULL default '',
1703 `add1` int(11) default 0,
1704 `every1` int(11) default 0,
1705 `whenmorethan1` int(11) default 0,
1706 `setto1` int(11) default NULL,
1707 `lastvalue1` int(11) default NULL,
1708 `add2` int(11) default 0,
1709 `every2` int(11) default 0,
1710 `whenmorethan2` int(11) default 0,
1711 `setto2` int(11) default NULL,
1712 `lastvalue2` int(11) default NULL,
1713 `add3` int(11) default 0,
1714 `every3` int(11) default 0,
1715 `innerloop1` int(11) default 0,
1716 `innerloop2` int(11) default 0,
1717 `innerloop3` int(11) default 0,
1718 `whenmorethan3` int(11) default 0,
1719 `setto3` int(11) default NULL,
1720 `lastvalue3` int(11) default NULL,
1721 `issuesatonce` tinyint(3) NOT NULL default 1,
1722 `firstacquidate` date default NULL,
1723 `manualhistory` tinyint(1) NOT NULL default 0,
1724 `irregularity` text,
1725 `letter` varchar(20) default NULL,
1726 `numberpattern` tinyint(3) default 0,
1727 `distributedto` text,
1728 `internalnotes` longtext,
1730 `location` varchar(80) NULL default '',
1731 `branchcode` varchar(10) NOT NULL default '',
1732 `hemisphere` tinyint(3) default 0,
1733 `lastbranch` varchar(10),
1734 `serialsadditems` tinyint(1) NOT NULL default '0',
1735 `staffdisplaycount` VARCHAR(10) NULL,
1736 `opacdisplaycount` VARCHAR(10) NULL,
1737 `graceperiod` int(11) NOT NULL default '0',
1738 `enddate` date default NULL,
1739 PRIMARY KEY (`subscriptionid`)
1740 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1743 -- Table structure for table `subscriptionhistory`
1746 DROP TABLE IF EXISTS `subscriptionhistory`;
1747 CREATE TABLE `subscriptionhistory` (
1748 `biblionumber` int(11) NOT NULL default 0,
1749 `subscriptionid` int(11) NOT NULL default 0,
1750 `histstartdate` date default NULL,
1751 `histenddate` date default NULL,
1752 `missinglist` longtext NOT NULL,
1753 `recievedlist` longtext NOT NULL,
1754 `opacnote` varchar(150) NOT NULL default '',
1755 `librariannote` varchar(150) NOT NULL default '',
1756 PRIMARY KEY (`subscriptionid`),
1757 KEY `biblionumber` (`biblionumber`)
1758 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1761 -- Table structure for table `subscriptionroutinglist`
1764 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1765 CREATE TABLE `subscriptionroutinglist` (
1766 `routingid` int(11) NOT NULL auto_increment,
1767 `borrowernumber` int(11) default NULL,
1768 `ranking` int(11) default NULL,
1769 `subscriptionid` int(11) default NULL,
1770 PRIMARY KEY (`routingid`)
1771 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1774 -- Table structure for table `suggestions`
1777 DROP TABLE IF EXISTS `suggestions`;
1778 CREATE TABLE `suggestions` (
1779 `suggestionid` int(8) NOT NULL auto_increment,
1780 `suggestedby` int(11) NOT NULL default 0,
1781 `suggesteddate` date NOT NULL default 0,
1782 `managedby` int(11) default NULL,
1783 `manageddate` date default NULL,
1784 acceptedby INT(11) default NULL,
1785 accepteddate date default NULL,
1786 rejectedby INT(11) default NULL,
1787 rejecteddate date default NULL,
1788 `STATUS` varchar(10) NOT NULL default '',
1790 `author` varchar(80) default NULL,
1791 `title` varchar(80) default NULL,
1792 `copyrightdate` smallint(6) default NULL,
1793 `publishercode` varchar(255) default NULL,
1794 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1795 `volumedesc` varchar(255) default NULL,
1796 `publicationyear` smallint(6) default 0,
1797 `place` varchar(255) default NULL,
1798 `isbn` varchar(30) default NULL,
1799 `mailoverseeing` smallint(1) default 0,
1800 `biblionumber` int(11) default NULL,
1803 branchcode VARCHAR(10) default NULL,
1804 collectiontitle text default NULL,
1805 itemtype VARCHAR(30) default NULL,
1806 PRIMARY KEY (`suggestionid`),
1807 KEY `suggestedby` (`suggestedby`),
1808 KEY `managedby` (`managedby`)
1809 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1812 -- Table structure for table `systempreferences`
1815 DROP TABLE IF EXISTS `systempreferences`;
1816 CREATE TABLE `systempreferences` (
1817 `variable` varchar(50) NOT NULL default '',
1819 `options` mediumtext,
1821 `type` varchar(20) default NULL,
1822 PRIMARY KEY (`variable`)
1823 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1826 -- Table structure for table `tags`
1829 DROP TABLE IF EXISTS `tags`;
1830 CREATE TABLE `tags` (
1831 `entry` varchar(255) NOT NULL default '',
1832 `weight` bigint(20) NOT NULL default 0,
1833 PRIMARY KEY (`entry`)
1834 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1837 -- Table structure for table `tags_all`
1840 DROP TABLE IF EXISTS `tags_all`;
1841 CREATE TABLE `tags_all` (
1842 `tag_id` int(11) NOT NULL auto_increment,
1843 `borrowernumber` int(11) NOT NULL,
1844 `biblionumber` int(11) NOT NULL,
1845 `term` varchar(255) NOT NULL,
1846 `language` int(4) default NULL,
1847 `date_created` datetime NOT NULL,
1848 PRIMARY KEY (`tag_id`),
1849 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1850 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1851 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1852 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1853 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1854 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1855 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1858 -- Table structure for table `tags_approval`
1861 DROP TABLE IF EXISTS `tags_approval`;
1862 CREATE TABLE `tags_approval` (
1863 `term` varchar(255) NOT NULL,
1864 `approved` int(1) NOT NULL default '0',
1865 `date_approved` datetime default NULL,
1866 `approved_by` int(11) default NULL,
1867 `weight_total` int(9) NOT NULL default '1',
1868 PRIMARY KEY (`term`),
1869 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1870 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1871 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1872 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1875 -- Table structure for table `tags_index`
1878 DROP TABLE IF EXISTS `tags_index`;
1879 CREATE TABLE `tags_index` (
1880 `term` varchar(255) NOT NULL,
1881 `biblionumber` int(11) NOT NULL,
1882 `weight` int(9) NOT NULL default '1',
1883 PRIMARY KEY (`term`,`biblionumber`),
1884 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1885 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1886 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1887 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1888 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1889 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1892 -- Table structure for table `userflags`
1895 DROP TABLE IF EXISTS `userflags`;
1896 CREATE TABLE `userflags` (
1897 `bit` int(11) NOT NULL default 0,
1898 `flag` varchar(30) default NULL,
1899 `flagdesc` varchar(255) default NULL,
1900 `defaulton` int(11) default NULL,
1902 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1905 -- Table structure for table `virtualshelves`
1908 DROP TABLE IF EXISTS `virtualshelves`;
1909 CREATE TABLE `virtualshelves` (
1910 `shelfnumber` int(11) NOT NULL auto_increment,
1911 `shelfname` varchar(255) default NULL,
1912 `owner` varchar(80) default NULL,
1913 `category` varchar(1) default NULL,
1914 `sortfield` varchar(16) default NULL,
1915 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1916 PRIMARY KEY (`shelfnumber`)
1917 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1920 -- Table structure for table `virtualshelfcontents`
1923 DROP TABLE IF EXISTS `virtualshelfcontents`;
1924 CREATE TABLE `virtualshelfcontents` (
1925 `shelfnumber` int(11) NOT NULL default 0,
1926 `biblionumber` int(11) NOT NULL default 0,
1927 `flags` int(11) default NULL,
1928 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1929 KEY `shelfnumber` (`shelfnumber`),
1930 KEY `biblionumber` (`biblionumber`),
1931 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1932 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1933 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1936 -- Table structure for table `z3950servers`
1939 DROP TABLE IF EXISTS `z3950servers`;
1940 CREATE TABLE `z3950servers` (
1941 `host` varchar(255) default NULL,
1942 `port` int(11) default NULL,
1943 `db` varchar(255) default NULL,
1944 `userid` varchar(255) default NULL,
1945 `password` varchar(255) default NULL,
1947 `id` int(11) NOT NULL auto_increment,
1948 `checked` smallint(6) default NULL,
1949 `rank` int(11) default NULL,
1950 `syntax` varchar(80) default NULL,
1952 `position` enum('primary','secondary','') NOT NULL default 'primary',
1953 `type` enum('zed','opensearch') NOT NULL default 'zed',
1954 `encoding` text default NULL,
1955 `description` text NOT NULL,
1957 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1960 -- Table structure for table `zebraqueue`
1963 DROP TABLE IF EXISTS `zebraqueue`;
1964 CREATE TABLE `zebraqueue` (
1965 `id` int(11) NOT NULL auto_increment,
1966 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
1967 `operation` char(20) NOT NULL default '',
1968 `server` char(20) NOT NULL default '',
1969 `done` int(11) NOT NULL default '0',
1970 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1972 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1973 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1975 DROP TABLE IF EXISTS `services_throttle`;
1976 CREATE TABLE `services_throttle` (
1977 `service_type` varchar(10) NOT NULL default '',
1978 `service_count` varchar(45) default NULL,
1979 PRIMARY KEY (`service_type`)
1980 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1982 -- http://www.w3.org/International/articles/language-tags/
1985 DROP TABLE IF EXISTS language_subtag_registry;
1986 CREATE TABLE language_subtag_registry (
1988 type varchar(25), -- language-script-region-variant-extension-privateuse
1989 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1991 id int(11) NOT NULL auto_increment,
1993 KEY `subtag` (`subtag`)
1994 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1996 -- TODO: add suppress_scripts
1997 -- this maps three letter codes defined in iso639.2 back to their
1998 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1999 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2000 CREATE TABLE language_rfc4646_to_iso639 (
2001 rfc4646_subtag varchar(25),
2002 iso639_2_code varchar(25),
2003 id int(11) NOT NULL auto_increment,
2005 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2006 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2008 DROP TABLE IF EXISTS language_descriptions;
2009 CREATE TABLE language_descriptions (
2013 description varchar(255),
2014 id int(11) NOT NULL auto_increment,
2017 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2019 -- bi-directional support, keyed by script subcode
2020 DROP TABLE IF EXISTS language_script_bidi;
2021 CREATE TABLE language_script_bidi (
2022 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2023 bidi varchar(3), -- rtl ltr
2024 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2027 -- TODO: need to map language subtags to script subtags for detection
2028 -- of bidi when script is not specified (like ar, he)
2029 DROP TABLE IF EXISTS language_script_mapping;
2030 CREATE TABLE language_script_mapping (
2031 language_subtag varchar(25),
2032 script_subtag varchar(25),
2033 KEY `language_subtag` (`language_subtag`)
2034 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2036 DROP TABLE IF EXISTS `permissions`;
2037 CREATE TABLE `permissions` (
2038 `module_bit` int(11) NOT NULL DEFAULT 0,
2039 `code` varchar(64) DEFAULT NULL,
2040 `description` varchar(255) DEFAULT NULL,
2041 PRIMARY KEY (`module_bit`, `code`),
2042 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2043 ON DELETE CASCADE ON UPDATE CASCADE
2044 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2046 DROP TABLE IF EXISTS `serialitems`;
2047 CREATE TABLE `serialitems` (
2048 `itemnumber` int(11) NOT NULL,
2049 `serialid` int(11) NOT NULL,
2050 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2051 KEY `serialitems_sfk_1` (`serialid`),
2052 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2053 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2055 DROP TABLE IF EXISTS `user_permissions`;
2056 CREATE TABLE `user_permissions` (
2057 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2058 `module_bit` int(11) NOT NULL DEFAULT 0,
2059 `code` varchar(64) DEFAULT NULL,
2060 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2061 ON DELETE CASCADE ON UPDATE CASCADE,
2062 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2063 ON DELETE CASCADE ON UPDATE CASCADE
2064 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2067 -- Table structure for table `tmp_holdsqueue`
2070 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2071 CREATE TABLE `tmp_holdsqueue` (
2072 `biblionumber` int(11) default NULL,
2073 `itemnumber` int(11) default NULL,
2074 `barcode` varchar(20) default NULL,
2075 `surname` mediumtext NOT NULL,
2078 `borrowernumber` int(11) NOT NULL,
2079 `cardnumber` varchar(16) default NULL,
2080 `reservedate` date default NULL,
2082 `itemcallnumber` varchar(255) default NULL,
2083 `holdingbranch` varchar(10) default NULL,
2084 `pickbranch` varchar(10) default NULL,
2086 `item_level_request` tinyint(4) NOT NULL default 0
2087 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2090 -- Table structure for table `message_queue`
2093 DROP TABLE IF EXISTS `message_queue`;
2094 CREATE TABLE `message_queue` (
2095 `message_id` int(11) NOT NULL auto_increment,
2096 `borrowernumber` int(11) default NULL,
2099 `metadata` text DEFAULT NULL,
2100 `letter_code` varchar(64) DEFAULT NULL,
2101 `message_transport_type` varchar(20) NOT NULL,
2102 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2103 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2104 `to_address` mediumtext,
2105 `from_address` mediumtext,
2106 `content_type` text,
2107 KEY `message_id` (`message_id`),
2108 KEY `borrowernumber` (`borrowernumber`),
2109 KEY `message_transport_type` (`message_transport_type`),
2110 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2111 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2115 -- Table structure for table `message_transport_types`
2118 DROP TABLE IF EXISTS `message_transport_types`;
2119 CREATE TABLE `message_transport_types` (
2120 `message_transport_type` varchar(20) NOT NULL,
2121 PRIMARY KEY (`message_transport_type`)
2122 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2125 -- Table structure for table `message_attributes`
2128 DROP TABLE IF EXISTS `message_attributes`;
2129 CREATE TABLE `message_attributes` (
2130 `message_attribute_id` int(11) NOT NULL auto_increment,
2131 `message_name` varchar(20) NOT NULL default '',
2132 `takes_days` tinyint(1) NOT NULL default '0',
2133 PRIMARY KEY (`message_attribute_id`),
2134 UNIQUE KEY `message_name` (`message_name`)
2135 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2138 -- Table structure for table `message_transports`
2141 DROP TABLE IF EXISTS `message_transports`;
2142 CREATE TABLE `message_transports` (
2143 `message_attribute_id` int(11) NOT NULL,
2144 `message_transport_type` varchar(20) NOT NULL,
2145 `is_digest` tinyint(1) NOT NULL default '0',
2146 `letter_module` varchar(20) NOT NULL default '',
2147 `letter_code` varchar(20) NOT NULL default '',
2148 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2149 KEY `message_transport_type` (`message_transport_type`),
2150 KEY `letter_module` (`letter_module`,`letter_code`),
2151 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2152 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2153 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2157 -- Table structure for table `borrower_message_preferences`
2160 DROP TABLE IF EXISTS `borrower_message_preferences`;
2161 CREATE TABLE `borrower_message_preferences` (
2162 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2163 `borrowernumber` int(11) default NULL,
2164 `categorycode` varchar(10) default NULL,
2165 `message_attribute_id` int(11) default '0',
2166 `days_in_advance` int(11) default '0',
2167 `wants_digest` tinyint(1) NOT NULL default '0',
2168 PRIMARY KEY (`borrower_message_preference_id`),
2169 KEY `borrowernumber` (`borrowernumber`),
2170 KEY `categorycode` (`categorycode`),
2171 KEY `message_attribute_id` (`message_attribute_id`),
2172 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2173 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2174 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2175 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2178 -- Table structure for table `borrower_message_transport_preferences`
2181 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2182 CREATE TABLE `borrower_message_transport_preferences` (
2183 `borrower_message_preference_id` int(11) NOT NULL default '0',
2184 `message_transport_type` varchar(20) NOT NULL default '0',
2185 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2186 KEY `message_transport_type` (`message_transport_type`),
2187 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,
2188 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
2189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2192 -- Table structure for the table branch_transfer_limits
2195 DROP TABLE IF EXISTS `branch_transfer_limits`;
2196 CREATE TABLE branch_transfer_limits (
2197 limitId int(8) NOT NULL auto_increment,
2198 toBranch varchar(10) NOT NULL,
2199 fromBranch varchar(10) NOT NULL,
2200 itemtype varchar(10) NULL,
2201 ccode varchar(10) NULL,
2202 PRIMARY KEY (limitId)
2203 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2206 -- Table structure for table `item_circulation_alert_preferences`
2209 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2210 CREATE TABLE `item_circulation_alert_preferences` (
2211 `id` int(11) NOT NULL auto_increment,
2212 `branchcode` varchar(10) NOT NULL,
2213 `categorycode` varchar(10) NOT NULL,
2214 `item_type` varchar(10) NOT NULL,
2215 `notification` varchar(16) NOT NULL,
2217 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2218 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2221 -- Table structure for table `messages`
2224 CREATE TABLE `messages` (
2225 `message_id` int(11) NOT NULL auto_increment,
2226 `borrowernumber` int(11) NOT NULL,
2227 `branchcode` varchar(4) default NULL,
2228 `message_type` varchar(1) NOT NULL,
2229 `message` text NOT NULL,
2230 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2231 PRIMARY KEY (`message_id`)
2232 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2235 -- Table structure for table `accountlines`
2238 DROP TABLE IF EXISTS `accountlines`;
2239 CREATE TABLE `accountlines` (
2240 `borrowernumber` int(11) NOT NULL default 0,
2241 `accountno` smallint(6) NOT NULL default 0,
2242 `itemnumber` int(11) default NULL,
2243 `date` date default NULL,
2244 `amount` decimal(28,6) default NULL,
2245 `description` mediumtext,
2246 `dispute` mediumtext,
2247 `accounttype` varchar(5) default NULL,
2248 `amountoutstanding` decimal(28,6) default NULL,
2249 `lastincrement` decimal(28,6) default NULL,
2250 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2251 `notify_id` int(11) NOT NULL default 0,
2252 `notify_level` int(2) NOT NULL default 0,
2253 KEY `acctsborridx` (`borrowernumber`),
2254 KEY `timeidx` (`timestamp`),
2255 KEY `itemnumber` (`itemnumber`),
2256 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2257 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2258 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2261 -- Table structure for table `accountoffsets`
2264 DROP TABLE IF EXISTS `accountoffsets`;
2265 CREATE TABLE `accountoffsets` (
2266 `borrowernumber` int(11) NOT NULL default 0,
2267 `accountno` smallint(6) NOT NULL default 0,
2268 `offsetaccount` smallint(6) NOT NULL default 0,
2269 `offsetamount` decimal(28,6) default NULL,
2270 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2271 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2275 -- Table structure for table `action_logs`
2278 DROP TABLE IF EXISTS `action_logs`;
2279 CREATE TABLE `action_logs` (
2280 `action_id` int(11) NOT NULL auto_increment,
2281 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2282 `user` int(11) NOT NULL default 0,
2285 `object` int(11) default NULL,
2287 PRIMARY KEY (`action_id`),
2288 KEY (`timestamp`,`user`)
2289 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2292 -- Table structure for table `alert`
2295 DROP TABLE IF EXISTS `alert`;
2296 CREATE TABLE `alert` (
2297 `alertid` int(11) NOT NULL auto_increment,
2298 `borrowernumber` int(11) NOT NULL default 0,
2299 `type` varchar(10) NOT NULL default '',
2300 `externalid` varchar(20) NOT NULL default '',
2301 PRIMARY KEY (`alertid`),
2302 KEY `borrowernumber` (`borrowernumber`),
2303 KEY `type` (`type`,`externalid`)
2304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2307 -- Table structure for table `aqbasketgroups`
2310 DROP TABLE IF EXISTS `aqbasketgroups`;
2311 CREATE TABLE `aqbasketgroups` (
2312 `id` int(11) NOT NULL auto_increment,
2313 `name` varchar(50) default NULL,
2314 `closed` tinyint(1) default NULL,
2315 `booksellerid` int(11) NOT NULL,
2317 KEY `booksellerid` (`booksellerid`),
2318 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2319 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2322 -- Table structure for table `aqbasket`
2325 DROP TABLE IF EXISTS `aqbasket`;
2326 CREATE TABLE `aqbasket` (
2327 `basketno` int(11) NOT NULL auto_increment,
2328 `basketname` varchar(50) default NULL,
2330 `booksellernote` mediumtext,
2331 `contractnumber` int(11),
2332 `creationdate` date default NULL,
2333 `closedate` date default NULL,
2334 `booksellerid` int(11) NOT NULL default 1,
2335 `authorisedby` varchar(10) default NULL,
2336 `booksellerinvoicenumber` mediumtext,
2337 `basketgroupid` int(11),
2338 PRIMARY KEY (`basketno`),
2339 KEY `booksellerid` (`booksellerid`),
2340 KEY `basketgroupid` (`basketgroupid`),
2341 KEY `contractnumber` (`contractnumber`),
2342 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2343 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2344 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2348 -- Table structure for table `aqbooksellers`
2351 DROP TABLE IF EXISTS `aqbooksellers`;
2352 CREATE TABLE `aqbooksellers` (
2353 `id` int(11) NOT NULL auto_increment,
2354 `name` mediumtext NOT NULL,
2355 `address1` mediumtext,
2356 `address2` mediumtext,
2357 `address3` mediumtext,
2358 `address4` mediumtext,
2359 `phone` varchar(30) default NULL,
2360 `accountnumber` mediumtext,
2361 `othersupplier` mediumtext,
2362 `currency` varchar(3) NOT NULL default '',
2363 `booksellerfax` mediumtext,
2365 `bookselleremail` mediumtext,
2366 `booksellerurl` mediumtext,
2367 `contact` varchar(100) default NULL,
2368 `postal` mediumtext,
2369 `url` varchar(255) default NULL,
2370 `contpos` varchar(100) default NULL,
2371 `contphone` varchar(100) default NULL,
2372 `contfax` varchar(100) default NULL,
2373 `contaltphone` varchar(100) default NULL,
2374 `contemail` varchar(100) default NULL,
2375 `contnotes` mediumtext,
2376 `active` tinyint(4) default NULL,
2377 `listprice` varchar(10) default NULL,
2378 `invoiceprice` varchar(10) default NULL,
2379 `gstreg` tinyint(4) default NULL,
2380 `listincgst` tinyint(4) default NULL,
2381 `invoiceincgst` tinyint(4) default NULL,
2382 `gstrate` decimal(6,4) default NULL,
2383 `discount` float(6,4) default NULL,
2384 `fax` varchar(50) default NULL,
2386 KEY `listprice` (`listprice`),
2387 KEY `invoiceprice` (`invoiceprice`),
2388 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2389 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2390 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2393 -- Table structure for table `aqbudgets`
2396 DROP TABLE IF EXISTS `aqbudgets`;
2397 CREATE TABLE `aqbudgets` (
2398 `budget_id` int(11) NOT NULL auto_increment,
2399 `budget_parent_id` int(11) default NULL,
2400 `budget_code` varchar(30) default NULL,
2401 `budget_name` varchar(80) default NULL,
2402 `budget_branchcode` varchar(10) default NULL,
2403 `budget_amount` decimal(28,6) NULL default '0.00',
2404 `budget_encumb` decimal(28,6) NULL default '0.00',
2405 `budget_expend` decimal(28,6) NULL default '0.00',
2406 `budget_notes` mediumtext,
2407 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2408 `budget_period_id` int(11) default NULL,
2409 `sort1_authcat` varchar(80) default NULL,
2410 `sort2_authcat` varchar(80) default NULL,
2411 `budget_owner_id` int(11) default NULL,
2412 `budget_permission` int(1) default '0',
2413 PRIMARY KEY (`budget_id`)
2414 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2418 -- Table structure for table `aqbudgetperiods`
2422 DROP TABLE IF EXISTS `aqbudgetperiods`;
2423 CREATE TABLE `aqbudgetperiods` (
2424 `budget_period_id` int(11) NOT NULL auto_increment,
2425 `budget_period_startdate` date NOT NULL,
2426 `budget_period_enddate` date NOT NULL,
2427 `budget_period_active` tinyint(1) default '0',
2428 `budget_period_description` mediumtext,
2429 `budget_period_total` decimal(28,6),
2430 `budget_period_locked` tinyint(1) default NULL,
2431 `sort1_authcat` varchar(10) default NULL,
2432 `sort2_authcat` varchar(10) default NULL,
2433 PRIMARY KEY (`budget_period_id`)
2434 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2437 -- Table structure for table `aqbudgets_planning`
2440 DROP TABLE IF EXISTS `aqbudgets_planning`;
2441 CREATE TABLE `aqbudgets_planning` (
2442 `plan_id` int(11) NOT NULL auto_increment,
2443 `budget_id` int(11) NOT NULL,
2444 `budget_period_id` int(11) NOT NULL,
2445 `estimated_amount` decimal(28,6) default NULL,
2446 `authcat` varchar(30) NOT NULL,
2447 `authvalue` varchar(30) NOT NULL,
2448 `display` tinyint(1) DEFAULT 1,
2449 PRIMARY KEY (`plan_id`),
2450 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2451 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2454 -- Table structure for table 'aqcontract'
2457 DROP TABLE IF EXISTS `aqcontract`;
2458 CREATE TABLE `aqcontract` (
2459 `contractnumber` int(11) NOT NULL auto_increment,
2460 `contractstartdate` date default NULL,
2461 `contractenddate` date default NULL,
2462 `contractname` varchar(50) default NULL,
2463 `contractdescription` mediumtext,
2464 `booksellerid` int(11) not NULL,
2465 PRIMARY KEY (`contractnumber`),
2466 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2467 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2468 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2471 -- Table structure for table `aqorderdelivery`
2474 DROP TABLE IF EXISTS `aqorderdelivery`;
2475 CREATE TABLE `aqorderdelivery` (
2476 `ordernumber` date default NULL,
2477 `deliverynumber` smallint(6) NOT NULL default 0,
2478 `deliverydate` varchar(18) default NULL,
2479 `qtydelivered` smallint(6) default NULL,
2480 `deliverycomments` mediumtext
2481 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2484 -- Table structure for table `aqorders`
2487 DROP TABLE IF EXISTS `aqorders`;
2488 CREATE TABLE `aqorders` (
2489 `ordernumber` int(11) NOT NULL auto_increment,
2490 `biblionumber` int(11) default NULL,
2491 `entrydate` date default NULL,
2492 `quantity` smallint(6) default NULL,
2493 `currency` varchar(3) default NULL,
2494 `listprice` decimal(28,6) default NULL,
2495 `totalamount` decimal(28,6) default NULL,
2496 `datereceived` date default NULL,
2497 `booksellerinvoicenumber` mediumtext,
2498 `freight` decimal(28,6) default NULL,
2499 `unitprice` decimal(28,6) default NULL,
2500 `quantityreceived` smallint(6) NOT NULL default 0,
2501 `cancelledby` varchar(10) default NULL,
2502 `datecancellationprinted` date default NULL,
2504 `supplierreference` mediumtext,
2505 `purchaseordernumber` mediumtext,
2506 `subscription` tinyint(1) default NULL,
2507 `serialid` varchar(30) default NULL,
2508 `basketno` int(11) default NULL,
2509 `biblioitemnumber` int(11) default NULL,
2510 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2511 `rrp` decimal(13,2) default NULL,
2512 `ecost` decimal(13,2) default NULL,
2513 `gst` decimal(13,2) default NULL,
2514 `budget_id` int(11) NOT NULL,
2515 `budgetgroup_id` int(11) NOT NULL,
2516 `budgetdate` date default NULL,
2517 `sort1` varchar(80) default NULL,
2518 `sort2` varchar(80) default NULL,
2519 `sort1_authcat` varchar(10) default NULL,
2520 `sort2_authcat` varchar(10) default NULL,
2521 `uncertainprice` tinyint(1),
2522 PRIMARY KEY (`ordernumber`),
2523 KEY `basketno` (`basketno`),
2524 KEY `biblionumber` (`biblionumber`),
2525 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2526 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2530 -- Table structure for table `aqorders_items`
2533 DROP TABLE IF EXISTS `aqorders_items`;
2534 CREATE TABLE `aqorders_items` (
2535 `ordernumber` int(11) NOT NULL,
2536 `itemnumber` int(11) NOT NULL,
2537 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2538 PRIMARY KEY (`itemnumber`),
2539 KEY `ordernumber` (`ordernumber`)
2540 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2543 -- Table structure for table `fieldmapping`
2546 DROP TABLE IF EXISTS `fieldmapping`;
2547 CREATE TABLE `fieldmapping` (
2548 `id` int(11) NOT NULL auto_increment,
2549 `field` varchar(255) NOT NULL,
2550 `frameworkcode` char(4) NOT NULL default '',
2551 `fieldcode` char(3) NOT NULL,
2552 `subfieldcode` char(1) NOT NULL,
2554 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2557 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2558 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2559 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2560 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2561 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2562 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2563 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2564 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;