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 `accountlines`
22 DROP TABLE IF EXISTS `accountlines`;
23 CREATE TABLE `accountlines` (
24 `borrowernumber` int(11) NOT NULL default 0,
25 `accountno` smallint(6) NOT NULL default 0,
26 `itemnumber` int(11) default NULL,
27 `date` date default NULL,
28 `amount` decimal(28,6) default NULL,
29 `description` mediumtext,
31 `accounttype` varchar(5) default NULL,
32 `amountoutstanding` decimal(28,6) default NULL,
33 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
34 `notify_id` int(11) NOT NULL default 0,
35 `notify_level` int(2) NOT NULL default 0,
36 KEY `acctsborridx` (`borrowernumber`),
37 KEY `timeidx` (`timestamp`),
38 KEY `itemnumber` (`itemnumber`),
39 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
40 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
41 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
44 -- Table structure for table `accountoffsets`
47 DROP TABLE IF EXISTS `accountoffsets`;
48 CREATE TABLE `accountoffsets` (
49 `borrowernumber` int(11) NOT NULL default 0,
50 `accountno` smallint(6) NOT NULL default 0,
51 `offsetaccount` smallint(6) NOT NULL default 0,
52 `offsetamount` decimal(28,6) default NULL,
53 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
54 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
55 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
58 -- Table structure for table `action_logs`
61 DROP TABLE IF EXISTS `action_logs`;
62 CREATE TABLE `action_logs` (
63 `action_id` int(11) NOT NULL auto_increment,
64 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
65 `user` int(11) NOT NULL default 0,
68 `object` int(11) default NULL,
70 PRIMARY KEY (`action_id`),
71 KEY (`timestamp`,`user`)
72 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
75 -- Table structure for table `alert`
78 DROP TABLE IF EXISTS `alert`;
79 CREATE TABLE `alert` (
80 `alertid` int(11) NOT NULL auto_increment,
81 `borrowernumber` int(11) NOT NULL default 0,
82 `type` varchar(10) NOT NULL default '',
83 `externalid` varchar(20) NOT NULL default '',
84 PRIMARY KEY (`alertid`),
85 KEY `borrowernumber` (`borrowernumber`),
86 KEY `type` (`type`,`externalid`)
87 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
90 -- Table structure for table `aqbasket`
93 DROP TABLE IF EXISTS `aqbasket`;
94 CREATE TABLE `aqbasket` (
95 `basketno` int(11) NOT NULL auto_increment,
96 `creationdate` date default NULL,
97 `closedate` date default NULL,
98 `booksellerid` int(11) NOT NULL default 1,
99 `authorisedby` varchar(10) default NULL,
100 `booksellerinvoicenumber` mediumtext,
101 PRIMARY KEY (`basketno`),
102 KEY `booksellerid` (`booksellerid`),
103 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
104 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
107 -- Table structure for table `aqbookfund`
110 DROP TABLE IF EXISTS `aqbookfund`;
111 CREATE TABLE `aqbookfund` (
112 `bookfundid` varchar(10) NOT NULL default '',
113 `bookfundname` mediumtext,
114 `bookfundgroup` varchar(5) default NULL,
115 `branchcode` varchar(10) NOT NULL default '',
116 PRIMARY KEY (`bookfundid`,`branchcode`)
117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
120 -- Table structure for table `aqbooksellers`
123 DROP TABLE IF EXISTS `aqbooksellers`;
124 CREATE TABLE `aqbooksellers` (
125 `id` int(11) NOT NULL auto_increment,
126 `name` mediumtext NOT NULL,
127 `address1` mediumtext,
128 `address2` mediumtext,
129 `address3` mediumtext,
130 `address4` mediumtext,
131 `phone` varchar(30) default NULL,
132 `accountnumber` mediumtext,
133 `othersupplier` mediumtext,
134 `currency` varchar(3) NOT NULL default '',
135 `deliverydays` smallint(6) default NULL,
136 `followupdays` smallint(6) default NULL,
137 `followupscancel` smallint(6) default NULL,
138 `specialty` mediumtext,
139 `booksellerfax` mediumtext,
141 `bookselleremail` mediumtext,
142 `booksellerurl` mediumtext,
143 `contact` varchar(100) default NULL,
145 `url` varchar(255) default NULL,
146 `contpos` varchar(100) default NULL,
147 `contphone` varchar(100) default NULL,
148 `contfax` varchar(100) default NULL,
149 `contaltphone` varchar(100) default NULL,
150 `contemail` varchar(100) default NULL,
151 `contnotes` mediumtext,
152 `active` tinyint(4) default NULL,
153 `listprice` varchar(10) default NULL,
154 `invoiceprice` varchar(10) default NULL,
155 `gstreg` tinyint(4) default NULL,
156 `listincgst` tinyint(4) default NULL,
157 `invoiceincgst` tinyint(4) default NULL,
158 `discount` float(6,4) default NULL,
159 `fax` varchar(50) default NULL,
160 `nocalc` int(11) default NULL,
161 `invoicedisc` float(6,4) default NULL,
163 KEY `listprice` (`listprice`),
164 KEY `invoiceprice` (`invoiceprice`),
165 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
166 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
170 -- Table structure for table `aqbudget`
173 DROP TABLE IF EXISTS `aqbudget`;
174 CREATE TABLE `aqbudget` (
175 `bookfundid` varchar(10) NOT NULL default '',
176 `startdate` date NOT NULL default 0,
177 `enddate` date default NULL,
178 `budgetamount` decimal(13,2) default NULL,
179 `aqbudgetid` tinyint(4) NOT NULL auto_increment,
180 `branchcode` varchar(10) default NULL,
181 PRIMARY KEY (`aqbudgetid`)
182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
185 -- Table structure for table `aqorderbreakdown`
188 DROP TABLE IF EXISTS `aqorderbreakdown`;
189 CREATE TABLE `aqorderbreakdown` (
190 `ordernumber` int(11) default NULL,
191 `linenumber` int(11) default NULL,
192 `branchcode` varchar(10) default NULL,
193 `bookfundid` varchar(10) NOT NULL default '',
194 `allocation` smallint(6) default NULL,
195 KEY `ordernumber` (`ordernumber`),
196 KEY `bookfundid` (`bookfundid`),
197 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
198 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
202 -- Table structure for table `aqorderdelivery`
205 DROP TABLE IF EXISTS `aqorderdelivery`;
206 CREATE TABLE `aqorderdelivery` (
207 `ordernumber` date default NULL,
208 `deliverynumber` smallint(6) NOT NULL default 0,
209 `deliverydate` varchar(18) default NULL,
210 `qtydelivered` smallint(6) default NULL,
211 `deliverycomments` mediumtext
212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
215 -- Table structure for table `aqorders`
218 DROP TABLE IF EXISTS `aqorders`;
219 CREATE TABLE `aqorders` (
220 `ordernumber` int(11) NOT NULL auto_increment,
221 `biblionumber` int(11) default NULL,
223 `entrydate` date default NULL,
224 `quantity` smallint(6) default NULL,
225 `currency` varchar(3) default NULL,
226 `listprice` decimal(28,6) default NULL,
227 `totalamount` decimal(28,6) default NULL,
228 `datereceived` date default NULL,
229 `booksellerinvoicenumber` mediumtext,
230 `freight` decimal(28,6) default NULL,
231 `unitprice` decimal(28,6) default NULL,
232 `quantityreceived` smallint(6) default NULL,
233 `cancelledby` varchar(10) default NULL,
234 `datecancellationprinted` date default NULL,
236 `supplierreference` mediumtext,
237 `purchaseordernumber` mediumtext,
238 `subscription` tinyint(1) default NULL,
239 `serialid` varchar(30) default NULL,
240 `basketno` int(11) default NULL,
241 `biblioitemnumber` int(11) default NULL,
242 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
243 `rrp` decimal(13,2) default NULL,
244 `ecost` decimal(13,2) default NULL,
245 `gst` decimal(13,2) default NULL,
246 `budgetdate` date default NULL,
247 `sort1` varchar(80) default NULL,
248 `sort2` varchar(80) default NULL,
249 PRIMARY KEY (`ordernumber`),
250 KEY `basketno` (`basketno`),
251 KEY `biblionumber` (`biblionumber`),
252 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
253 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
254 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
257 -- Table structure for table `auth_header`
260 DROP TABLE IF EXISTS `auth_header`;
261 CREATE TABLE `auth_header` (
262 `authid` bigint(20) unsigned NOT NULL auto_increment,
263 `authtypecode` varchar(10) NOT NULL default '',
264 `datecreated` date default NULL,
265 `datemodified` date default NULL,
266 `origincode` varchar(20) default NULL,
267 `authtrees` mediumtext,
269 `linkid` bigint(20) default NULL,
270 `marcxml` longtext NOT NULL,
271 PRIMARY KEY (`authid`),
272 KEY `origincode` (`origincode`)
273 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
276 -- Table structure for table `auth_subfield_structure`
279 DROP TABLE IF EXISTS `auth_subfield_structure`;
280 CREATE TABLE `auth_subfield_structure` (
281 `authtypecode` varchar(10) NOT NULL default '',
282 `tagfield` varchar(3) NOT NULL default '',
283 `tagsubfield` varchar(1) NOT NULL default '',
284 `liblibrarian` varchar(255) NOT NULL default '',
285 `libopac` varchar(255) NOT NULL default '',
286 `repeatable` tinyint(4) NOT NULL default 0,
287 `mandatory` tinyint(4) NOT NULL default 0,
288 `tab` tinyint(1) default NULL,
289 `authorised_value` varchar(10) default NULL,
290 `value_builder` varchar(80) default NULL,
291 `seealso` varchar(255) default NULL,
292 `isurl` tinyint(1) default NULL,
293 `hidden` tinyint(3) NOT NULL default 0,
294 `linkid` tinyint(1) NOT NULL default 0,
295 `kohafield` varchar(45) NULL default '',
296 `frameworkcode` varchar(8) NOT NULL default '',
297 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
298 KEY `tab` (`authtypecode`,`tab`)
299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
302 -- Table structure for table `auth_tag_structure`
305 DROP TABLE IF EXISTS `auth_tag_structure`;
306 CREATE TABLE `auth_tag_structure` (
307 `authtypecode` varchar(10) NOT NULL default '',
308 `tagfield` varchar(3) NOT NULL default '',
309 `liblibrarian` varchar(255) NOT NULL default '',
310 `libopac` varchar(255) NOT NULL default '',
311 `repeatable` tinyint(4) NOT NULL default 0,
312 `mandatory` tinyint(4) NOT NULL default 0,
313 `authorised_value` varchar(10) default NULL,
314 PRIMARY KEY (`authtypecode`,`tagfield`),
315 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
316 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
319 -- Table structure for table `auth_types`
322 DROP TABLE IF EXISTS `auth_types`;
323 CREATE TABLE `auth_types` (
324 `authtypecode` varchar(10) NOT NULL default '',
325 `authtypetext` varchar(255) NOT NULL default '',
326 `auth_tag_to_report` varchar(3) NOT NULL default '',
327 `summary` mediumtext NOT NULL,
328 PRIMARY KEY (`authtypecode`)
329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
332 -- Table structure for table `authorised_values`
335 DROP TABLE IF EXISTS `authorised_values`;
336 CREATE TABLE `authorised_values` (
337 `id` int(11) NOT NULL auto_increment,
338 `category` varchar(10) NOT NULL default '',
339 `authorised_value` varchar(80) NOT NULL default '',
340 `lib` varchar(80) default NULL,
341 `imageurl` varchar(200) default NULL,
343 KEY `name` (`category`)
344 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
347 -- Table structure for table `biblio`
350 DROP TABLE IF EXISTS `biblio`;
351 CREATE TABLE `biblio` (
352 `biblionumber` int(11) NOT NULL auto_increment,
353 `frameworkcode` varchar(4) NOT NULL default '',
356 `unititle` mediumtext,
358 `serial` tinyint(1) default NULL,
359 `seriestitle` mediumtext,
360 `copyrightdate` smallint(6) default NULL,
361 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
362 `datecreated` DATE NOT NULL,
363 `abstract` mediumtext,
364 PRIMARY KEY (`biblionumber`),
365 KEY `blbnoidx` (`biblionumber`)
366 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
369 -- Table structure for table `biblio_framework`
372 DROP TABLE IF EXISTS `biblio_framework`;
373 CREATE TABLE `biblio_framework` (
374 `frameworkcode` varchar(4) NOT NULL default '',
375 `frameworktext` varchar(255) NOT NULL default '',
376 PRIMARY KEY (`frameworkcode`)
377 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
380 -- Table structure for table `biblioitems`
383 DROP TABLE IF EXISTS `biblioitems`;
384 CREATE TABLE `biblioitems` (
385 `biblioitemnumber` int(11) NOT NULL auto_increment,
386 `biblionumber` int(11) NOT NULL default 0,
389 `itemtype` varchar(10) default NULL,
390 `isbn` varchar(14) default NULL,
391 `issn` varchar(9) default NULL,
392 `publicationyear` text,
393 `publishercode` varchar(255) default NULL,
394 `volumedate` date default NULL,
396 `collectiontitle` mediumtext default NULL,
397 `collectionissn` text default NULL,
398 `collectionvolume` mediumtext default NULL,
399 `editionstatement` text default NULL,
400 `editionresponsibility` text default NULL,
401 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
402 `illus` varchar(255) default NULL,
403 `pages` varchar(255) default NULL,
405 `size` varchar(255) default NULL,
406 `place` varchar(255) default NULL,
407 `lccn` varchar(25) default NULL,
409 `url` varchar(255) default NULL,
410 `cn_source` varchar(10) default NULL,
411 `cn_class` varchar(30) default NULL,
412 `cn_item` varchar(10) default NULL,
413 `cn_suffix` varchar(10) default NULL,
414 `cn_sort` varchar(30) default NULL,
415 `totalissues` int(10),
416 `marcxml` longtext NOT NULL,
417 PRIMARY KEY (`biblioitemnumber`),
418 KEY `bibinoidx` (`biblioitemnumber`),
419 KEY `bibnoidx` (`biblionumber`),
421 KEY `publishercode` (`publishercode`),
422 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
423 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
426 -- Table structure for table `borrowers`
429 DROP TABLE IF EXISTS `borrowers`;
430 CREATE TABLE `borrowers` (
431 `borrowernumber` int(11) NOT NULL auto_increment,
432 `cardnumber` varchar(16) default NULL,
433 `surname` mediumtext NOT NULL,
436 `othernames` mediumtext,
438 `streetnumber` varchar(10) default NULL,
439 `streettype` varchar(50) default NULL,
440 `address` mediumtext NOT NULL,
442 `city` mediumtext NOT NULL,
443 `zipcode` varchar(25) default NULL,
446 `mobile` varchar(50) default NULL,
450 `B_streetnumber` varchar(10) default NULL,
451 `B_streettype` varchar(50) default NULL,
452 `B_address` varchar(100) default NULL,
454 `B_zipcode` varchar(25) default NULL,
456 `B_phone` mediumtext,
457 `dateofbirth` date default NULL,
458 `branchcode` varchar(10) NOT NULL default '',
459 `categorycode` varchar(10) NOT NULL default '',
460 `dateenrolled` date default NULL,
461 `dateexpiry` date default NULL,
462 `gonenoaddress` tinyint(1) default NULL,
463 `lost` tinyint(1) default NULL,
464 `debarred` tinyint(1) default NULL,
465 `contactname` mediumtext,
466 `contactfirstname` text,
468 `guarantorid` int(11) default NULL,
469 `borrowernotes` mediumtext,
470 `relationship` varchar(100) default NULL,
471 `ethnicity` varchar(50) default NULL,
472 `ethnotes` varchar(255) default NULL,
473 `sex` varchar(1) default NULL,
474 `password` varchar(30) default NULL,
475 `flags` int(11) default NULL,
476 `userid` varchar(30) default NULL,
477 `opacnote` mediumtext,
478 `contactnote` varchar(255) default NULL,
479 `sort1` varchar(80) default NULL,
480 `sort2` varchar(80) default NULL,
481 `altcontactfirstname` varchar(255) default NULL,
482 `altcontactsurname` varchar(255) default NULL,
483 `altcontactaddress1` varchar(255) default NULL,
484 `altcontactaddress2` varchar(255) default NULL,
485 `altcontactaddress3` varchar(255) default NULL,
486 `altcontactzipcode` varchar(50) default NULL,
487 `altcontactphone` varchar(50) default NULL,
488 UNIQUE KEY `cardnumber` (`cardnumber`),
489 PRIMARY KEY `borrowernumber` (`borrowernumber`),
490 KEY `categorycode` (`categorycode`),
491 KEY `branchcode` (`branchcode`),
492 KEY `userid` (`userid`),
493 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
494 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
495 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
498 -- Table structure for table `borrower_attribute_types`
501 DROP TABLE IF EXISTS `borrower_attribute_types`;
502 CREATE TABLE `borrower_attribute_types` (
503 `code` varchar(10) NOT NULL,
504 `description` varchar(255) NOT NULL,
505 `repeatable` tinyint(1) NOT NULL default 0,
506 `unique_id` tinyint(1) NOT NULL default 0,
507 `opac_display` tinyint(1) NOT NULL default 0,
508 `password_allowed` tinyint(1) NOT NULL default 0,
509 `staff_searchable` tinyint(1) NOT NULL default 0,
510 `authorised_value_category` varchar(10) default NULL,
512 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
515 -- Table structure for table `borrower_attributes`
518 DROP TABLE IF EXISTS `borrower_attributes`;
519 CREATE TABLE `borrower_attributes` (
520 `borrowernumber` int(11) NOT NULL,
521 `code` varchar(10) NOT NULL,
522 `attribute` varchar(30) default NULL,
523 `password` varchar(30) default NULL,
524 KEY `borrowernumber` (`borrowernumber`),
525 KEY `code_attribute` (`code`, `attribute`),
526 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
527 ON DELETE CASCADE ON UPDATE CASCADE,
528 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
529 ON DELETE CASCADE ON UPDATE CASCADE
530 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
533 -- Table structure for table `branchcategories`
536 DROP TABLE IF EXISTS `branchcategories`;
537 CREATE TABLE `branchcategories` (
538 `categorycode` varchar(10) NOT NULL default '',
539 `categoryname` varchar(32),
540 `codedescription` mediumtext,
541 `categorytype` varchar(16),
542 PRIMARY KEY (`categorycode`)
543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
546 -- Table structure for table `branches`
549 DROP TABLE IF EXISTS `branches`;
550 CREATE TABLE `branches` (
551 `branchcode` varchar(10) NOT NULL default '',
552 `branchname` mediumtext NOT NULL,
553 `branchaddress1` mediumtext,
554 `branchaddress2` mediumtext,
555 `branchaddress3` mediumtext,
556 `branchphone` mediumtext,
557 `branchfax` mediumtext,
558 `branchemail` mediumtext,
559 `issuing` tinyint(4) default NULL,
560 `branchip` varchar(15) default NULL,
561 `branchprinter` varchar(100) default NULL,
562 UNIQUE KEY `branchcode` (`branchcode`)
563 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
566 -- Table structure for table `branchrelations`
569 DROP TABLE IF EXISTS `branchrelations`;
570 CREATE TABLE `branchrelations` (
571 `branchcode` varchar(10) NOT NULL default '',
572 `categorycode` varchar(10) NOT NULL default '',
573 PRIMARY KEY (`branchcode`,`categorycode`),
574 KEY `branchcode` (`branchcode`),
575 KEY `categorycode` (`categorycode`),
576 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
577 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
578 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
581 -- Table structure for table `branchtransfers`
584 DROP TABLE IF EXISTS `branchtransfers`;
585 CREATE TABLE `branchtransfers` (
586 `itemnumber` int(11) NOT NULL default 0,
587 `datesent` datetime default NULL,
588 `frombranch` varchar(10) NOT NULL default '',
589 `datearrived` datetime default NULL,
590 `tobranch` varchar(10) NOT NULL default '',
591 `comments` mediumtext,
592 KEY `frombranch` (`frombranch`),
593 KEY `tobranch` (`tobranch`),
594 KEY `itemnumber` (`itemnumber`),
595 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
596 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
597 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
598 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
602 -- Table structure for table `browser`
604 DROP TABLE IF EXISTS `browser`;
605 CREATE TABLE `browser` (
606 `level` int(11) NOT NULL,
607 `classification` varchar(20) NOT NULL,
608 `description` varchar(255) NOT NULL,
609 `number` bigint(20) NOT NULL,
610 `endnode` tinyint(4) NOT NULL
611 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
614 -- Table structure for table `categories`
617 DROP TABLE IF EXISTS `categories`;
618 CREATE TABLE `categories` (
619 `categorycode` varchar(10) NOT NULL default '',
620 `description` mediumtext,
621 `enrolmentperiod` smallint(6) default NULL,
622 `upperagelimit` smallint(6) default NULL,
623 `dateofbirthrequired` tinyint(1) default NULL,
624 `finetype` varchar(30) default NULL,
625 `bulk` tinyint(1) default NULL,
626 `enrolmentfee` decimal(28,6) default NULL,
627 `overduenoticerequired` tinyint(1) default NULL,
628 `issuelimit` smallint(6) default NULL,
629 `reservefee` decimal(28,6) default NULL,
630 `category_type` varchar(1) NOT NULL default 'A',
631 PRIMARY KEY (`categorycode`),
632 UNIQUE KEY `categorycode` (`categorycode`)
633 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
636 -- Table structure for table `cities`
639 DROP TABLE IF EXISTS `cities`;
640 CREATE TABLE `cities` (
641 `cityid` int(11) NOT NULL auto_increment,
642 `city_name` varchar(100) NOT NULL default '',
643 `city_zipcode` varchar(20) default NULL,
644 PRIMARY KEY (`cityid`)
645 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
648 -- Table structure for table `class_sort_rules`
651 DROP TABLE IF EXISTS `class_sort_rules`;
652 CREATE TABLE `class_sort_rules` (
653 `class_sort_rule` varchar(10) NOT NULL default '',
654 `description` mediumtext,
655 `sort_routine` varchar(30) NOT NULL default '',
656 PRIMARY KEY (`class_sort_rule`),
657 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
658 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
661 -- Table structure for table `class_sources`
664 DROP TABLE IF EXISTS `class_sources`;
665 CREATE TABLE `class_sources` (
666 `cn_source` varchar(10) NOT NULL default '',
667 `description` mediumtext,
668 `used` tinyint(4) NOT NULL default 0,
669 `class_sort_rule` varchar(10) NOT NULL default '',
670 PRIMARY KEY (`cn_source`),
671 UNIQUE KEY `cn_source_idx` (`cn_source`),
672 KEY `used_idx` (`used`),
673 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
674 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
677 -- Table structure for table `currency`
680 DROP TABLE IF EXISTS `currency`;
681 CREATE TABLE `currency` (
682 `currency` varchar(10) NOT NULL default '',
683 `symbol` varchar(5) default NULL,
684 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
685 `rate` float(7,5) default NULL,
686 PRIMARY KEY (`currency`)
687 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
690 -- Table structure for table `deletedbiblio`
693 DROP TABLE IF EXISTS `deletedbiblio`;
694 CREATE TABLE `deletedbiblio` (
695 `biblionumber` int(11) NOT NULL default 0,
696 `frameworkcode` varchar(4) NOT NULL default '',
699 `unititle` mediumtext,
701 `serial` tinyint(1) default NULL,
702 `seriestitle` mediumtext,
703 `copyrightdate` smallint(6) default NULL,
704 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
705 `datecreated` DATE NOT NULL,
706 `abstract` mediumtext,
707 PRIMARY KEY (`biblionumber`),
708 KEY `blbnoidx` (`biblionumber`)
709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
712 -- Table structure for table `deletedbiblioitems`
715 DROP TABLE IF EXISTS `deletedbiblioitems`;
716 CREATE TABLE `deletedbiblioitems` (
717 `biblioitemnumber` int(11) NOT NULL default 0,
718 `biblionumber` int(11) NOT NULL default 0,
721 `itemtype` varchar(10) default NULL,
722 `isbn` varchar(14) default NULL,
723 `issn` varchar(9) default NULL,
724 `publicationyear` text,
725 `publishercode` varchar(255) default NULL,
726 `volumedate` date default NULL,
728 `collectiontitle` mediumtext default NULL,
729 `collectionissn` text default NULL,
730 `collectionvolume` mediumtext default NULL,
731 `editionstatement` text default NULL,
732 `editionresponsibility` text default NULL,
733 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
734 `illus` varchar(255) default NULL,
735 `pages` varchar(255) default NULL,
737 `size` varchar(255) default NULL,
738 `place` varchar(255) default NULL,
739 `lccn` varchar(25) default NULL,
741 `url` varchar(255) default NULL,
742 `cn_source` varchar(10) default NULL,
743 `cn_class` varchar(30) default NULL,
744 `cn_item` varchar(10) default NULL,
745 `cn_suffix` varchar(10) default NULL,
746 `cn_sort` varchar(30) default NULL,
747 `totalissues` int(10),
748 `marcxml` longtext NOT NULL,
749 PRIMARY KEY (`biblioitemnumber`),
750 KEY `bibinoidx` (`biblioitemnumber`),
751 KEY `bibnoidx` (`biblionumber`),
753 KEY `publishercode` (`publishercode`)
754 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
757 -- Table structure for table `deletedborrowers`
760 DROP TABLE IF EXISTS `deletedborrowers`;
761 CREATE TABLE `deletedborrowers` (
762 `borrowernumber` int(11) NOT NULL default 0,
763 `cardnumber` varchar(9) NOT NULL default '',
764 `surname` mediumtext NOT NULL,
767 `othernames` mediumtext,
769 `streetnumber` varchar(10) default NULL,
770 `streettype` varchar(50) default NULL,
771 `address` mediumtext NOT NULL,
773 `city` mediumtext NOT NULL,
774 `zipcode` varchar(25) default NULL,
777 `mobile` varchar(50) default NULL,
781 `B_streetnumber` varchar(10) default NULL,
782 `B_streettype` varchar(50) default NULL,
783 `B_address` varchar(100) default NULL,
785 `B_zipcode` varchar(25) default NULL,
787 `B_phone` mediumtext,
788 `dateofbirth` date default NULL,
789 `branchcode` varchar(10) NOT NULL default '',
790 `categorycode` varchar(2) default NULL,
791 `dateenrolled` date default NULL,
792 `dateexpiry` date default NULL,
793 `gonenoaddress` tinyint(1) default NULL,
794 `lost` tinyint(1) default NULL,
795 `debarred` tinyint(1) default NULL,
796 `contactname` mediumtext,
797 `contactfirstname` text,
799 `guarantorid` int(11) default NULL,
800 `borrowernotes` mediumtext,
801 `relationship` varchar(100) default NULL,
802 `ethnicity` varchar(50) default NULL,
803 `ethnotes` varchar(255) default NULL,
804 `sex` varchar(1) default NULL,
805 `password` varchar(30) default NULL,
806 `flags` int(11) default NULL,
807 `userid` varchar(30) default NULL,
808 `opacnote` mediumtext,
809 `contactnote` varchar(255) default NULL,
810 `sort1` varchar(80) default NULL,
811 `sort2` varchar(80) default NULL,
812 `altcontactfirstname` varchar(255) default NULL,
813 `altcontactsurname` varchar(255) default NULL,
814 `altcontactaddress1` varchar(255) default NULL,
815 `altcontactaddress2` varchar(255) default NULL,
816 `altcontactaddress3` varchar(255) default NULL,
817 `altcontactzipcode` varchar(50) default NULL,
818 `altcontactphone` varchar(50) default NULL,
819 KEY `borrowernumber` (`borrowernumber`),
820 KEY `cardnumber` (`cardnumber`)
821 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
824 -- Table structure for table `deleteditems`
827 DROP TABLE IF EXISTS `deleteditems`;
828 CREATE TABLE `deleteditems` (
829 `itemnumber` int(11) NOT NULL default 0,
830 `biblionumber` int(11) NOT NULL default 0,
831 `biblioitemnumber` int(11) NOT NULL default 0,
832 `barcode` varchar(20) default NULL,
833 `dateaccessioned` date default NULL,
834 `booksellerid` mediumtext default NULL,
835 `homebranch` varchar(10) default NULL,
836 `price` decimal(8,2) default NULL,
837 `replacementprice` decimal(8,2) default NULL,
838 `replacementpricedate` date default NULL,
839 `datelastborrowed` date default NULL,
840 `datelastseen` date default NULL,
841 `stack` tinyint(1) default NULL,
842 `notforloan` tinyint(1) NOT NULL default 0,
843 `damaged` tinyint(1) NOT NULL default 0,
844 `itemlost` tinyint(1) NOT NULL default 0,
845 `wthdrawn` tinyint(1) NOT NULL default 0,
846 `itemcallnumber` varchar(30) default NULL,
847 `issues` smallint(6) default NULL,
848 `renewals` smallint(6) default NULL,
849 `reserves` smallint(6) default NULL,
850 `restricted` tinyint(1) default NULL,
851 `itemnotes` mediumtext,
852 `holdingbranch` varchar(10) default NULL,
853 `paidfor` mediumtext,
854 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
855 `location` varchar(80) default NULL,
856 `onloan` date default NULL,
857 `cn_source` varchar(10) default NULL,
858 `cn_sort` varchar(30) default NULL,
859 `ccode` varchar(10) default NULL,
860 `materials` varchar(10) default NULL,
861 `uri` varchar(255) default NULL,
862 `itype` varchar(10) default NULL,
863 `more_subfields_xml` longtext default NULL,
864 `enumchron` varchar(80) default NULL,
865 `copynumber` smallint(6) default NULL,
867 PRIMARY KEY (`itemnumber`),
868 KEY `delitembarcodeidx` (`barcode`),
869 KEY `delitembinoidx` (`biblioitemnumber`),
870 KEY `delitembibnoidx` (`biblionumber`),
871 KEY `delhomebranch` (`homebranch`),
872 KEY `delholdingbranch` (`holdingbranch`)
873 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
876 -- Table structure for table `ethnicity`
879 DROP TABLE IF EXISTS `ethnicity`;
880 CREATE TABLE `ethnicity` (
881 `code` varchar(10) NOT NULL default '',
882 `name` varchar(255) default NULL,
884 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
887 -- Table structure for table `import_batches`
890 DROP TABLE IF EXISTS `import_batches`;
891 CREATE TABLE `import_batches` (
892 `import_batch_id` int(11) NOT NULL auto_increment,
893 `matcher_id` int(11) default NULL,
894 `template_id` int(11) default NULL,
895 `branchcode` varchar(10) default NULL,
896 `num_biblios` int(11) NOT NULL default 0,
897 `num_items` int(11) NOT NULL default 0,
898 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
899 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
900 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
901 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
902 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
903 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
904 `file_name` varchar(100),
905 `comments` mediumtext,
906 PRIMARY KEY (`import_batch_id`),
907 KEY `branchcode` (`branchcode`)
908 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
911 -- Table structure for table `import_records`
914 DROP TABLE IF EXISTS `import_records`;
915 CREATE TABLE `import_records` (
916 `import_record_id` int(11) NOT NULL auto_increment,
917 `import_batch_id` int(11) NOT NULL,
918 `branchcode` varchar(10) default NULL,
919 `record_sequence` int(11) NOT NULL default 0,
920 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
921 `import_date` DATE default NULL,
922 `marc` longblob NOT NULL,
923 `marcxml` longtext NOT NULL,
924 `marcxml_old` longtext NOT NULL,
925 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
926 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
927 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
928 `import_error` mediumtext,
929 `encoding` varchar(40) NOT NULL default '',
930 `z3950random` varchar(40) default NULL,
931 PRIMARY KEY (`import_record_id`),
932 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
933 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
934 KEY `branchcode` (`branchcode`),
935 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
936 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
939 -- Table structure for `import_record_matches`
941 DROP TABLE IF EXISTS `import_record_matches`;
942 CREATE TABLE `import_record_matches` (
943 `import_record_id` int(11) NOT NULL,
944 `candidate_match_id` int(11) NOT NULL,
945 `score` int(11) NOT NULL default 0,
946 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
947 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
948 KEY `record_score` (`import_record_id`, `score`)
949 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
952 -- Table structure for table `import_biblios`
955 DROP TABLE IF EXISTS `import_biblios`;
956 CREATE TABLE `import_biblios` (
957 `import_record_id` int(11) NOT NULL,
958 `matched_biblionumber` int(11) default NULL,
959 `control_number` varchar(25) default NULL,
960 `original_source` varchar(25) default NULL,
961 `title` varchar(128) default NULL,
962 `author` varchar(80) default NULL,
963 `isbn` varchar(14) default NULL,
964 `issn` varchar(9) default NULL,
965 `has_items` tinyint(1) NOT NULL default 0,
966 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
967 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
968 KEY `matched_biblionumber` (`matched_biblionumber`),
969 KEY `title` (`title`),
971 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
974 -- Table structure for table `import_items`
977 DROP TABLE IF EXISTS `import_items`;
978 CREATE TABLE `import_items` (
979 `import_items_id` int(11) NOT NULL auto_increment,
980 `import_record_id` int(11) NOT NULL,
981 `itemnumber` int(11) default NULL,
982 `branchcode` varchar(10) default NULL,
983 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
984 `marcxml` longtext NOT NULL,
985 `import_error` mediumtext,
986 PRIMARY KEY (`import_items_id`),
987 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
988 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
989 KEY `itemnumber` (`itemnumber`),
990 KEY `branchcode` (`branchcode`)
991 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
994 -- Table structure for table `issues`
997 DROP TABLE IF EXISTS `issues`;
998 CREATE TABLE `issues` (
999 `borrowernumber` int(11) default NULL,
1000 `itemnumber` int(11) default NULL,
1001 `date_due` date default NULL,
1002 `branchcode` varchar(10) default NULL,
1003 `issuingbranch` varchar(18) default NULL,
1004 `returndate` date default NULL,
1005 `lastreneweddate` date default NULL,
1006 `return` varchar(4) default NULL,
1007 `renewals` tinyint(4) default NULL,
1008 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1009 `issuedate` date default NULL,
1010 KEY `issuesborridx` (`borrowernumber`),
1011 KEY `issuesitemidx` (`itemnumber`),
1012 KEY `bordate` (`borrowernumber`,`timestamp`),
1013 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1014 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1015 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1018 -- Table structure for table `issuingrules`
1021 DROP TABLE IF EXISTS `issuingrules`;
1022 CREATE TABLE `issuingrules` (
1023 `categorycode` varchar(10) NOT NULL default '',
1024 `itemtype` varchar(10) NOT NULL default '',
1025 `restrictedtype` tinyint(1) default NULL,
1026 `rentaldiscount` decimal(28,6) default NULL,
1027 `reservecharge` decimal(28,6) default NULL,
1028 `fine` decimal(28,6) default NULL,
1029 `firstremind` int(11) default NULL,
1030 `chargeperiod` int(11) default NULL,
1031 `accountsent` int(11) default NULL,
1032 `chargename` varchar(100) default NULL,
1033 `maxissueqty` int(4) default NULL,
1034 `issuelength` int(4) default NULL,
1035 `branchcode` varchar(10) NOT NULL default '',
1036 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1037 KEY `categorycode` (`categorycode`),
1038 KEY `itemtype` (`itemtype`)
1039 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1042 -- Table structure for table `items`
1045 DROP TABLE IF EXISTS `items`;
1046 CREATE TABLE `items` (
1047 `itemnumber` int(11) NOT NULL auto_increment,
1048 `biblionumber` int(11) NOT NULL default 0,
1049 `biblioitemnumber` int(11) NOT NULL default 0,
1050 `barcode` varchar(20) default NULL,
1051 `dateaccessioned` date default NULL,
1052 `booksellerid` mediumtext default NULL,
1053 `homebranch` varchar(10) default NULL,
1054 `price` decimal(8,2) default NULL,
1055 `replacementprice` decimal(8,2) default NULL,
1056 `replacementpricedate` date default NULL,
1057 `datelastborrowed` date default NULL,
1058 `datelastseen` date default NULL,
1059 `stack` tinyint(1) default NULL,
1060 `notforloan` tinyint(1) NOT NULL default 0,
1061 `damaged` tinyint(1) NOT NULL default 0,
1062 `itemlost` tinyint(1) NOT NULL default 0,
1063 `wthdrawn` tinyint(1) NOT NULL default 0,
1064 `itemcallnumber` varchar(30) default NULL,
1065 `issues` smallint(6) default NULL,
1066 `renewals` smallint(6) default NULL,
1067 `reserves` smallint(6) default NULL,
1068 `restricted` tinyint(1) default NULL,
1069 `itemnotes` mediumtext,
1070 `holdingbranch` varchar(10) default NULL,
1071 `paidfor` mediumtext,
1072 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1073 `location` varchar(80) default NULL,
1074 `onloan` date default NULL,
1075 `cn_source` varchar(10) default NULL,
1076 `cn_sort` varchar(30) default NULL,
1077 `ccode` varchar(10) default NULL,
1078 `materials` varchar(10) default NULL,
1079 `uri` varchar(255) default NULL,
1080 `itype` varchar(10) default NULL,
1081 `more_subfields_xml` longtext default NULL,
1082 `enumchron` varchar(80) default NULL,
1083 `copynumber` smallint(6) default NULL,
1084 PRIMARY KEY (`itemnumber`),
1085 UNIQUE KEY `itembarcodeidx` (`barcode`),
1086 KEY `itembinoidx` (`biblioitemnumber`),
1087 KEY `itembibnoidx` (`biblionumber`),
1088 KEY `homebranch` (`homebranch`),
1089 KEY `holdingbranch` (`holdingbranch`),
1090 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1091 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1092 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1093 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1096 -- Table structure for table `itemtypes`
1099 DROP TABLE IF EXISTS `itemtypes`;
1100 CREATE TABLE `itemtypes` (
1101 `itemtype` varchar(10) NOT NULL default '',
1102 `description` mediumtext,
1103 `renewalsallowed` smallint(6) default NULL,
1104 `rentalcharge` double(16,4) default NULL,
1105 `notforloan` smallint(6) default NULL,
1106 `imageurl` varchar(200) default NULL,
1108 PRIMARY KEY (`itemtype`),
1109 UNIQUE KEY `itemtype` (`itemtype`)
1110 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1113 -- Table structure for table `labels`
1116 DROP TABLE IF EXISTS `labels`;
1117 CREATE TABLE `labels` (
1118 `labelid` int(11) NOT NULL auto_increment,
1119 `batch_id` varchar(10) NOT NULL default 1,
1120 `itemnumber` varchar(100) NOT NULL default '',
1121 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1122 PRIMARY KEY (`labelid`)
1123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1126 -- Table structure for table `labels_conf`
1129 DROP TABLE IF EXISTS `labels_conf`;
1130 CREATE TABLE `labels_conf` (
1131 `id` int(4) NOT NULL auto_increment,
1132 `barcodetype` char(100) default '',
1133 `title` int(1) default '0',
1134 `subtitle` int(1) default '0',
1135 `itemtype` int(1) default '0',
1136 `barcode` int(1) default '0',
1137 `dewey` int(1) default '0',
1138 `classification` int(1) default NULL,
1139 `subclass` int(1) default '0',
1140 `itemcallnumber` int(1) default '0',
1141 `author` int(1) default '0',
1142 `issn` int(1) default '0',
1143 `isbn` int(1) default '0',
1144 `startlabel` int(2) NOT NULL default '1',
1145 `printingtype` char(32) default 'BAR',
1146 `formatstring` varchar(64) default NULL,
1147 `layoutname` char(20) NOT NULL default 'TEST',
1148 `guidebox` int(1) default '0',
1149 `active` tinyint(1) default '1',
1150 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1151 `ccode` char(4) collate utf8_unicode_ci default NULL,
1152 `callnum_split` int(1) default NULL,
1153 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1155 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1158 -- Table structure for table `labels_profile`
1161 DROP TABLE IF EXISTS `labels_profile`;
1162 CREATE TABLE `labels_profile` (
1163 `tmpl_id` int(4) NOT NULL,
1164 `prof_id` int(4) NOT NULL,
1165 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1166 UNIQUE KEY `prof_id` (`prof_id`)
1167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1170 -- Table structure for table `labels_templates`
1173 DROP TABLE IF EXISTS `labels_templates`;
1174 CREATE TABLE `labels_templates` (
1175 `tmpl_id` int(4) NOT NULL auto_increment,
1176 `tmpl_code` char(100) default '',
1177 `tmpl_desc` char(100) default '',
1178 `page_width` float default '0',
1179 `page_height` float default '0',
1180 `label_width` float default '0',
1181 `label_height` float default '0',
1182 `topmargin` float default '0',
1183 `leftmargin` float default '0',
1184 `cols` int(2) default '0',
1185 `rows` int(2) default '0',
1186 `colgap` float default '0',
1187 `rowgap` float default '0',
1188 `active` int(1) default NULL,
1189 `units` char(20) default 'PX',
1190 `fontsize` int(4) NOT NULL default '3',
1191 `font` char(10) NOT NULL default 'TR',
1192 PRIMARY KEY (`tmpl_id`)
1193 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1196 -- Table structure for table `letter`
1199 DROP TABLE IF EXISTS `letter`;
1200 CREATE TABLE `letter` (
1201 `module` varchar(20) NOT NULL default '',
1202 `code` varchar(20) NOT NULL default '',
1203 `name` varchar(100) NOT NULL default '',
1204 `title` varchar(200) NOT NULL default '',
1206 PRIMARY KEY (`module`,`code`)
1207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1210 -- Table structure for table `marc_subfield_structure`
1213 DROP TABLE IF EXISTS `marc_subfield_structure`;
1214 CREATE TABLE `marc_subfield_structure` (
1215 `tagfield` varchar(3) NOT NULL default '',
1216 `tagsubfield` varchar(1) NOT NULL default '',
1217 `liblibrarian` varchar(255) NOT NULL default '',
1218 `libopac` varchar(255) NOT NULL default '',
1219 `repeatable` tinyint(4) NOT NULL default 0,
1220 `mandatory` tinyint(4) NOT NULL default 0,
1221 `kohafield` varchar(40) default NULL,
1222 `tab` tinyint(1) default NULL,
1223 `authorised_value` varchar(20) default NULL,
1224 `authtypecode` varchar(20) default NULL,
1225 `value_builder` varchar(80) default NULL,
1226 `isurl` tinyint(1) default NULL,
1227 `hidden` tinyint(1) default NULL,
1228 `frameworkcode` varchar(4) NOT NULL default '',
1229 `seealso` varchar(1100) default NULL,
1230 `link` varchar(80) default NULL,
1231 `defaultvalue` text default NULL,
1232 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1233 KEY `kohafield_2` (`kohafield`),
1234 KEY `tab` (`frameworkcode`,`tab`),
1235 KEY `kohafield` (`frameworkcode`,`kohafield`)
1236 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1239 -- Table structure for table `marc_tag_structure`
1242 DROP TABLE IF EXISTS `marc_tag_structure`;
1243 CREATE TABLE `marc_tag_structure` (
1244 `tagfield` varchar(3) NOT NULL default '',
1245 `liblibrarian` varchar(255) NOT NULL default '',
1246 `libopac` varchar(255) NOT NULL default '',
1247 `repeatable` tinyint(4) NOT NULL default 0,
1248 `mandatory` tinyint(4) NOT NULL default 0,
1249 `authorised_value` varchar(10) default NULL,
1250 `frameworkcode` varchar(4) NOT NULL default '',
1251 PRIMARY KEY (`frameworkcode`,`tagfield`)
1252 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1255 -- Table structure for table `marc_matchers`
1258 DROP TABLE IF EXISTS `marc_matchers`;
1259 CREATE TABLE `marc_matchers` (
1260 `matcher_id` int(11) NOT NULL auto_increment,
1261 `code` varchar(10) NOT NULL default '',
1262 `description` varchar(255) NOT NULL default '',
1263 `record_type` varchar(10) NOT NULL default 'biblio',
1264 `threshold` int(11) NOT NULL default 0,
1265 PRIMARY KEY (`matcher_id`),
1266 KEY `code` (`code`),
1267 KEY `record_type` (`record_type`)
1268 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1271 -- Table structure for table `matchpoints`
1273 DROP TABLE IF EXISTS `matchpoints`;
1274 CREATE TABLE `matchpoints` (
1275 `matcher_id` int(11) NOT NULL,
1276 `matchpoint_id` int(11) NOT NULL auto_increment,
1277 `search_index` varchar(30) NOT NULL default '',
1278 `score` int(11) NOT NULL default 0,
1279 PRIMARY KEY (`matchpoint_id`),
1280 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1281 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1282 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1286 -- Table structure for table `matchpoint_components`
1288 DROP TABLE IF EXISTS `matchpoint_components`;
1289 CREATE TABLE `matchpoint_components` (
1290 `matchpoint_id` int(11) NOT NULL,
1291 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1292 sequence int(11) NOT NULL default 0,
1293 tag varchar(3) NOT NULL default '',
1294 subfields varchar(40) NOT NULL default '',
1295 offset int(4) NOT NULL default 0,
1296 length int(4) NOT NULL default 0,
1297 PRIMARY KEY (`matchpoint_component_id`),
1298 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1299 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1300 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1301 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1304 -- Table structure for table `matcher_component_norms`
1306 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1307 CREATE TABLE `matchpoint_component_norms` (
1308 `matchpoint_component_id` int(11) NOT NULL,
1309 `sequence` int(11) NOT NULL default 0,
1310 `norm_routine` varchar(50) NOT NULL default '',
1311 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1312 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1313 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1317 -- Table structure for table `matcher_matchpoints`
1319 DROP TABLE IF EXISTS `matcher_matchpoints`;
1320 CREATE TABLE `matcher_matchpoints` (
1321 `matcher_id` int(11) NOT NULL,
1322 `matchpoint_id` int(11) NOT NULL,
1323 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1324 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1325 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1326 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1327 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1330 -- Table structure for table `matchchecks`
1332 DROP TABLE IF EXISTS `matchchecks`;
1333 CREATE TABLE `matchchecks` (
1334 `matcher_id` int(11) NOT NULL,
1335 `matchcheck_id` int(11) NOT NULL auto_increment,
1336 `source_matchpoint_id` int(11) NOT NULL,
1337 `target_matchpoint_id` int(11) NOT NULL,
1338 PRIMARY KEY (`matchcheck_id`),
1339 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1340 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1341 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1342 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1343 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1344 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1348 -- Table structure for table `notifys`
1351 DROP TABLE IF EXISTS `notifys`;
1352 CREATE TABLE `notifys` (
1353 `notify_id` int(11) NOT NULL default 0,
1354 `borrowernumber` int(11) NOT NULL default 0,
1355 `itemnumber` int(11) NOT NULL default 0,
1356 `notify_date` date default NULL,
1357 `notify_send_date` date default NULL,
1358 `notify_level` int(1) NOT NULL default 0,
1359 `method` varchar(20) NOT NULL default ''
1360 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1363 -- Table structure for table `nozebra`
1366 DROP TABLE IF EXISTS `nozebra`;
1367 CREATE TABLE `nozebra` (
1368 `server` varchar(20) NOT NULL,
1369 `indexname` varchar(40) NOT NULL,
1370 `value` varchar(250) NOT NULL,
1371 `biblionumbers` longtext NOT NULL,
1372 KEY `indexname` (`server`,`indexname`),
1373 KEY `value` (`server`,`value`))
1374 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1377 -- Table structure for table `old_issues`
1380 DROP TABLE IF EXISTS `old_issues`;
1381 CREATE TABLE `old_issues` (
1382 `borrowernumber` int(11) default NULL,
1383 `itemnumber` int(11) default NULL,
1384 `date_due` date default NULL,
1385 `branchcode` varchar(10) default NULL,
1386 `issuingbranch` varchar(18) default NULL,
1387 `returndate` date default NULL,
1388 `lastreneweddate` date default NULL,
1389 `return` varchar(4) default NULL,
1390 `renewals` tinyint(4) default NULL,
1391 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1392 `issuedate` date default NULL,
1393 KEY `old_issuesborridx` (`borrowernumber`),
1394 KEY `old_issuesitemidx` (`itemnumber`),
1395 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1396 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1397 ON DELETE SET NULL ON UPDATE SET NULL,
1398 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1399 ON DELETE SET NULL ON UPDATE SET NULL
1400 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1403 -- Table structure for table `old_reserves`
1405 DROP TABLE IF EXISTS `old_reserves`;
1406 CREATE TABLE `old_reserves` (
1407 `borrowernumber` int(11) default NULL,
1408 `reservedate` date default NULL,
1409 `biblionumber` int(11) default NULL,
1410 `constrainttype` varchar(1) default NULL,
1411 `branchcode` varchar(10) default NULL,
1412 `notificationdate` date default NULL,
1413 `reminderdate` date default NULL,
1414 `cancellationdate` date default NULL,
1415 `reservenotes` mediumtext,
1416 `priority` smallint(6) default NULL,
1417 `found` varchar(1) default NULL,
1418 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1419 `itemnumber` int(11) default NULL,
1420 `waitingdate` date default NULL,
1421 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1422 KEY `old_reserves_biblionumber` (`biblionumber`),
1423 KEY `old_reserves_itemnumber` (`itemnumber`),
1424 KEY `old_reserves_branchcode` (`branchcode`),
1425 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1426 ON DELETE SET NULL ON UPDATE SET NULL,
1427 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1428 ON DELETE SET NULL ON UPDATE SET NULL,
1429 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1430 ON DELETE SET NULL ON UPDATE SET NULL
1431 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1434 -- Table structure for table `opac_news`
1437 DROP TABLE IF EXISTS `opac_news`;
1438 CREATE TABLE `opac_news` (
1439 `idnew` int(10) unsigned NOT NULL auto_increment,
1440 `title` varchar(250) NOT NULL default '',
1441 `new` text NOT NULL,
1442 `lang` varchar(25) NOT NULL default '',
1443 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1444 `expirationdate` date default NULL,
1445 `number` int(11) default NULL,
1446 PRIMARY KEY (`idnew`)
1447 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1450 -- Table structure for table `overduerules`
1453 DROP TABLE IF EXISTS `overduerules`;
1454 CREATE TABLE `overduerules` (
1455 `branchcode` varchar(10) NOT NULL default '',
1456 `categorycode` varchar(2) NOT NULL default '',
1457 `delay1` int(4) default 0,
1458 `letter1` varchar(20) default NULL,
1459 `debarred1` varchar(1) default 0,
1460 `delay2` int(4) default 0,
1461 `debarred2` varchar(1) default 0,
1462 `letter2` varchar(20) default NULL,
1463 `delay3` int(4) default 0,
1464 `letter3` varchar(20) default NULL,
1465 `debarred3` int(1) default 0,
1466 PRIMARY KEY (`branchcode`,`categorycode`)
1467 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1470 -- Table structure for table `patroncards`
1473 DROP TABLE IF EXISTS `patroncards`;
1474 CREATE TABLE `patroncards` (
1475 `cardid` int(11) NOT NULL auto_increment,
1476 `batch_id` varchar(10) NOT NULL default '1',
1477 `borrowernumber` int(11) NOT NULL,
1478 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1479 PRIMARY KEY (`cardid`),
1480 KEY `patroncards_ibfk_1` (`borrowernumber`),
1481 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1482 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1485 -- Table structure for table `patronimage`
1488 DROP TABLE IF EXISTS `patronimage`;
1489 CREATE TABLE `patronimage` (
1490 `cardnumber` varchar(16) NOT NULL,
1491 `mimetype` varchar(15) NOT NULL,
1492 `imagefile` mediumblob NOT NULL,
1493 PRIMARY KEY (`cardnumber`),
1494 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1495 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1498 -- Table structure for table `printers`
1501 DROP TABLE IF EXISTS `printers`;
1502 CREATE TABLE `printers` (
1503 `printername` varchar(40) NOT NULL default '',
1504 `printqueue` varchar(20) default NULL,
1505 `printtype` varchar(20) default NULL,
1506 PRIMARY KEY (`printername`)
1507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1510 -- Table structure for table `printers_profile`
1513 DROP TABLE IF EXISTS `printers_profile`;
1514 CREATE TABLE `printers_profile` (
1515 `prof_id` int(4) NOT NULL auto_increment,
1516 `printername` varchar(40) NOT NULL,
1517 `tmpl_id` int(4) NOT NULL,
1518 `paper_bin` varchar(20) NOT NULL,
1519 `offset_horz` float default NULL,
1520 `offset_vert` float default NULL,
1521 `creep_horz` float default NULL,
1522 `creep_vert` float default NULL,
1523 `unit` char(20) NOT NULL default 'POINT',
1524 PRIMARY KEY (`prof_id`),
1525 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1526 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1530 -- Table structure for table `repeatable_holidays`
1533 DROP TABLE IF EXISTS `repeatable_holidays`;
1534 CREATE TABLE `repeatable_holidays` (
1535 `id` int(11) NOT NULL auto_increment,
1536 `branchcode` varchar(10) NOT NULL default '',
1537 `weekday` smallint(6) default NULL,
1538 `day` smallint(6) default NULL,
1539 `month` smallint(6) default NULL,
1540 `title` varchar(50) NOT NULL default '',
1541 `description` text NOT NULL,
1543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1546 -- Table structure for table `reports_dictionary`
1549 DROP TABLE IF EXISTS `reports_dictionary`;
1550 CREATE TABLE reports_dictionary (
1551 `id` int(11) NOT NULL auto_increment,
1552 `name` varchar(255) default NULL,
1554 `date_created` datetime default NULL,
1555 `date_modified` datetime default NULL,
1557 `area` int(11) default NULL,
1559 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1562 -- Table structure for table `reserveconstraints`
1565 DROP TABLE IF EXISTS `reserveconstraints`;
1566 CREATE TABLE `reserveconstraints` (
1567 `borrowernumber` int(11) NOT NULL default 0,
1568 `reservedate` date default NULL,
1569 `biblionumber` int(11) NOT NULL default 0,
1570 `biblioitemnumber` int(11) default NULL,
1571 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1572 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1575 -- Table structure for table `reserves`
1578 DROP TABLE IF EXISTS `reserves`;
1579 CREATE TABLE `reserves` (
1580 `borrowernumber` int(11) NOT NULL default 0,
1581 `reservedate` date default NULL,
1582 `biblionumber` int(11) NOT NULL default 0,
1583 `constrainttype` varchar(1) default NULL,
1584 `branchcode` varchar(10) default NULL,
1585 `notificationdate` date default NULL,
1586 `reminderdate` date default NULL,
1587 `cancellationdate` date default NULL,
1588 `reservenotes` mediumtext,
1589 `priority` smallint(6) default NULL,
1590 `found` varchar(1) default NULL,
1591 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1592 `itemnumber` int(11) default NULL,
1593 `waitingdate` date default NULL,
1594 KEY `borrowernumber` (`borrowernumber`),
1595 KEY `biblionumber` (`biblionumber`),
1596 KEY `itemnumber` (`itemnumber`),
1597 KEY `branchcode` (`branchcode`),
1598 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1599 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1600 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1601 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1605 -- Table structure for table `reviews`
1608 DROP TABLE IF EXISTS `reviews`;
1609 CREATE TABLE `reviews` (
1610 `reviewid` int(11) NOT NULL auto_increment,
1611 `borrowernumber` int(11) default NULL,
1612 `biblionumber` int(11) default NULL,
1614 `approved` tinyint(4) default NULL,
1615 `datereviewed` datetime default NULL,
1616 PRIMARY KEY (`reviewid`)
1617 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1620 -- Table structure for table `roadtype`
1623 DROP TABLE IF EXISTS `roadtype`;
1624 CREATE TABLE `roadtype` (
1625 `roadtypeid` int(11) NOT NULL auto_increment,
1626 `road_type` varchar(100) NOT NULL default '',
1627 PRIMARY KEY (`roadtypeid`)
1628 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1631 -- Table structure for table `saved_sql`
1634 DROP TABLE IF EXISTS `saved_sql`;
1635 CREATE TABLE saved_sql (
1636 `id` int(11) NOT NULL auto_increment,
1637 `borrowernumber` int(11) default NULL,
1638 `date_created` datetime default NULL,
1639 `last_modified` datetime default NULL,
1641 `last_run` datetime default NULL,
1642 `report_name` varchar(255) default NULL,
1643 `type` varchar(255) default NULL,
1646 KEY boridx (`borrowernumber`)
1647 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1651 -- Table structure for `saved_reports`
1654 DROP TABLE IF EXISTS `saved_reports`;
1655 CREATE TABLE saved_reports (
1656 `id` int(11) NOT NULL auto_increment,
1657 `report_id` int(11) default NULL,
1659 `date_run` datetime default NULL,
1661 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1665 -- Table structure for table `serial`
1668 DROP TABLE IF EXISTS `serial`;
1669 CREATE TABLE `serial` (
1670 `serialid` int(11) NOT NULL auto_increment,
1671 `biblionumber` varchar(100) NOT NULL default '',
1672 `subscriptionid` varchar(100) NOT NULL default '',
1673 `serialseq` varchar(100) NOT NULL default '',
1674 `status` tinyint(4) NOT NULL default 0,
1675 `planneddate` date default NULL,
1677 `publisheddate` date default NULL,
1678 `itemnumber` text default NULL,
1679 `claimdate` date default NULL,
1680 `routingnotes` text,
1681 PRIMARY KEY (`serialid`)
1682 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1685 -- Table structure for table `sessions`
1688 DROP TABLE IF EXISTS sessions;
1689 CREATE TABLE sessions (
1690 `id` varchar(32) NOT NULL,
1691 `a_session` text NOT NULL,
1693 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1696 -- Table structure for table `special_holidays`
1699 DROP TABLE IF EXISTS `special_holidays`;
1700 CREATE TABLE `special_holidays` (
1701 `id` int(11) NOT NULL auto_increment,
1702 `branchcode` varchar(10) NOT NULL default '',
1703 `day` smallint(6) NOT NULL default 0,
1704 `month` smallint(6) NOT NULL default 0,
1705 `year` smallint(6) NOT NULL default 0,
1706 `isexception` smallint(1) NOT NULL default 1,
1707 `title` varchar(50) NOT NULL default '',
1708 `description` text NOT NULL,
1710 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1713 -- Table structure for table `statistics`
1716 DROP TABLE IF EXISTS `statistics`;
1717 CREATE TABLE `statistics` (
1718 `datetime` datetime default NULL,
1719 `branch` varchar(10) default NULL,
1720 `proccode` varchar(4) default NULL,
1721 `value` double(16,4) default NULL,
1722 `type` varchar(16) default NULL,
1724 `usercode` varchar(10) default NULL,
1725 `itemnumber` int(11) default NULL,
1726 `itemtype` varchar(10) default NULL,
1727 `borrowernumber` int(11) default NULL,
1728 `associatedborrower` int(11) default NULL,
1729 KEY `timeidx` (`datetime`)
1730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1733 -- Table structure for table `stopwords`
1736 DROP TABLE IF EXISTS `stopwords`;
1737 CREATE TABLE `stopwords` (
1738 `word` varchar(255) default NULL
1739 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1742 -- Table structure for table `subscription`
1745 DROP TABLE IF EXISTS `subscription`;
1746 CREATE TABLE `subscription` (
1747 `biblionumber` int(11) NOT NULL default 0,
1748 `subscriptionid` int(11) NOT NULL auto_increment,
1749 `librarian` varchar(100) default '',
1750 `startdate` date default NULL,
1751 `aqbooksellerid` int(11) default 0,
1752 `cost` int(11) default 0,
1753 `aqbudgetid` int(11) default 0,
1754 `weeklength` int(11) default 0,
1755 `monthlength` int(11) default 0,
1756 `numberlength` int(11) default 0,
1757 `periodicity` tinyint(4) default 0,
1758 `dow` varchar(100) default '',
1759 `numberingmethod` varchar(100) default '',
1761 `status` varchar(100) NOT NULL default '',
1762 `add1` int(11) default 0,
1763 `every1` int(11) default 0,
1764 `whenmorethan1` int(11) default 0,
1765 `setto1` int(11) default NULL,
1766 `lastvalue1` int(11) default NULL,
1767 `add2` int(11) default 0,
1768 `every2` int(11) default 0,
1769 `whenmorethan2` int(11) default 0,
1770 `setto2` int(11) default NULL,
1771 `lastvalue2` int(11) default NULL,
1772 `add3` int(11) default 0,
1773 `every3` int(11) default 0,
1774 `innerloop1` int(11) default 0,
1775 `innerloop2` int(11) default 0,
1776 `innerloop3` int(11) default 0,
1777 `whenmorethan3` int(11) default 0,
1778 `setto3` int(11) default NULL,
1779 `lastvalue3` int(11) default NULL,
1780 `issuesatonce` tinyint(3) NOT NULL default 1,
1781 `firstacquidate` date default NULL,
1782 `manualhistory` tinyint(1) NOT NULL default 0,
1783 `irregularity` text,
1784 `letter` varchar(20) default NULL,
1785 `numberpattern` tinyint(3) default 0,
1786 `distributedto` text,
1787 `internalnotes` longtext,
1789 `branchcode` varchar(10) NOT NULL default '',
1790 `hemisphere` tinyint(3) default 0,
1791 `lastbranch` varchar(10),
1792 `serialsadditems` tinyint(1) NOT NULL default '0',
1793 PRIMARY KEY (`subscriptionid`)
1794 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1797 -- Table structure for table `subscriptionhistory`
1800 DROP TABLE IF EXISTS `subscriptionhistory`;
1801 CREATE TABLE `subscriptionhistory` (
1802 `biblionumber` int(11) NOT NULL default 0,
1803 `subscriptionid` int(11) NOT NULL default 0,
1804 `histstartdate` date default NULL,
1805 `enddate` date default NULL,
1806 `missinglist` longtext NOT NULL,
1807 `recievedlist` longtext NOT NULL,
1808 `opacnote` varchar(150) NOT NULL default '',
1809 `librariannote` varchar(150) NOT NULL default '',
1810 PRIMARY KEY (`subscriptionid`),
1811 KEY `biblionumber` (`biblionumber`)
1812 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1815 -- Table structure for table `subscriptionroutinglist`
1818 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1819 CREATE TABLE `subscriptionroutinglist` (
1820 `routingid` int(11) NOT NULL auto_increment,
1821 `borrowernumber` int(11) default NULL,
1822 `ranking` int(11) default NULL,
1823 `subscriptionid` int(11) default NULL,
1824 PRIMARY KEY (`routingid`)
1825 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1828 -- Table structure for table `suggestions`
1831 DROP TABLE IF EXISTS `suggestions`;
1832 CREATE TABLE `suggestions` (
1833 `suggestionid` int(8) NOT NULL auto_increment,
1834 `suggestedby` int(11) NOT NULL default 0,
1835 `managedby` int(11) default NULL,
1836 `STATUS` varchar(10) NOT NULL default '',
1838 `author` varchar(80) default NULL,
1839 `title` varchar(80) default NULL,
1840 `copyrightdate` smallint(6) default NULL,
1841 `publishercode` varchar(255) default NULL,
1842 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1843 `volumedesc` varchar(255) default NULL,
1844 `publicationyear` smallint(6) default 0,
1845 `place` varchar(255) default NULL,
1846 `isbn` varchar(10) default NULL,
1847 `mailoverseeing` smallint(1) default 0,
1848 `biblionumber` int(11) default NULL,
1850 PRIMARY KEY (`suggestionid`),
1851 KEY `suggestedby` (`suggestedby`),
1852 KEY `managedby` (`managedby`)
1853 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1856 -- Table structure for table `systempreferences`
1859 DROP TABLE IF EXISTS `systempreferences`;
1860 CREATE TABLE `systempreferences` (
1861 `variable` varchar(50) NOT NULL default '',
1863 `options` mediumtext,
1865 `type` varchar(20) default NULL,
1866 PRIMARY KEY (`variable`)
1867 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1870 -- Table structure for table `tags`
1873 DROP TABLE IF EXISTS `tags`;
1874 CREATE TABLE `tags` (
1875 `entry` varchar(255) NOT NULL default '',
1876 `weight` bigint(20) NOT NULL default 0,
1877 PRIMARY KEY (`entry`)
1878 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1881 -- Table structure for table `tags_all`
1884 CREATE TABLE `tags_all` (
1885 `tag_id` int(11) NOT NULL auto_increment,
1886 `borrowernumber` int(11) NOT NULL,
1887 `biblionumber` int(11) NOT NULL,
1888 `term` varchar(255) NOT NULL,
1889 `language` int(4) default NULL,
1890 `date_created` datetime NOT NULL,
1891 PRIMARY KEY (`tag_id`),
1892 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1893 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1894 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1895 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1896 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1897 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1898 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1901 -- Table structure for table `tags_approval`
1904 CREATE TABLE `tags_approval` (
1905 `term` varchar(255) NOT NULL,
1906 `approved` int(1) NOT NULL default '0',
1907 `date_approved` datetime default NULL,
1908 `approved_by` int(11) default NULL,
1909 `weight_total` int(9) NOT NULL default '1',
1910 PRIMARY KEY (`term`),
1911 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1912 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1913 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1914 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1917 -- Table structure for table `tags_index`
1920 CREATE TABLE `tags_index` (
1921 `term` varchar(255) NOT NULL,
1922 `biblionumber` int(11) NOT NULL,
1923 `weight` int(9) NOT NULL default '1',
1924 PRIMARY KEY (`term`,`biblionumber`),
1925 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1926 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1927 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1928 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1929 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1930 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1933 -- Table structure for table `userflags`
1936 DROP TABLE IF EXISTS `userflags`;
1937 CREATE TABLE `userflags` (
1938 `bit` int(11) NOT NULL default 0,
1939 `flag` varchar(30) default NULL,
1940 `flagdesc` varchar(255) default NULL,
1941 `defaulton` int(11) default NULL,
1943 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1946 -- Table structure for table `virtualshelves`
1949 DROP TABLE IF EXISTS `virtualshelves`;
1950 CREATE TABLE `virtualshelves` (
1951 `shelfnumber` int(11) NOT NULL auto_increment,
1952 `shelfname` varchar(255) default NULL,
1953 `owner` varchar(80) default NULL,
1954 `category` varchar(1) default NULL,
1955 `sortfield` varchar(16) default NULL,
1956 PRIMARY KEY (`shelfnumber`)
1957 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1960 -- Table structure for table `virtualshelfcontents`
1963 DROP TABLE IF EXISTS `virtualshelfcontents`;
1964 CREATE TABLE `virtualshelfcontents` (
1965 `shelfnumber` int(11) NOT NULL default 0,
1966 `biblionumber` int(11) NOT NULL default 0,
1967 `flags` int(11) default NULL,
1968 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1969 KEY `shelfnumber` (`shelfnumber`),
1970 KEY `biblionumber` (`biblionumber`),
1971 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1972 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1973 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1976 -- Table structure for table `z3950servers`
1979 DROP TABLE IF EXISTS `z3950servers`;
1980 CREATE TABLE `z3950servers` (
1981 `host` varchar(255) default NULL,
1982 `port` int(11) default NULL,
1983 `db` varchar(255) default NULL,
1984 `userid` varchar(255) default NULL,
1985 `password` varchar(255) default NULL,
1987 `id` int(11) NOT NULL auto_increment,
1988 `checked` smallint(6) default NULL,
1989 `rank` int(11) default NULL,
1990 `syntax` varchar(80) default NULL,
1992 `position` enum('primary','secondary','') NOT NULL default 'primary',
1993 `type` enum('zed','opensearch') NOT NULL default 'zed',
1994 `encoding` text default NULL,
1995 `description` text NOT NULL,
1997 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2000 -- Table structure for table `zebraqueue`
2003 DROP TABLE IF EXISTS `zebraqueue`;
2004 CREATE TABLE `zebraqueue` (
2005 `id` int(11) NOT NULL auto_increment,
2006 `biblio_auth_number` int(11) NOT NULL default '0',
2007 `operation` char(20) NOT NULL default '',
2008 `server` char(20) NOT NULL default '',
2009 `done` int(11) NOT NULL default '0',
2010 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2012 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2013 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2015 DROP TABLE IF EXISTS `services_throttle`;
2016 CREATE TABLE `services_throttle` (
2017 `service_type` varchar(10) NOT NULL default '',
2018 `service_count` varchar(45) default NULL,
2019 PRIMARY KEY (`service_type`)
2020 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2022 -- http://www.w3.org/International/articles/language-tags/
2025 DROP TABLE IF EXISTS language_subtag_registry;
2026 CREATE TABLE language_subtag_registry (
2028 type varchar(25), -- language-script-region-variant-extension-privateuse
2029 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2031 KEY `subtag` (`subtag`)
2032 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2034 -- TODO: add suppress_scripts
2035 -- this maps three letter codes defined in iso639.2 back to their
2036 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2037 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2038 CREATE TABLE language_rfc4646_to_iso639 (
2039 rfc4646_subtag varchar(25),
2040 iso639_2_code varchar(25),
2041 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2042 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2044 DROP TABLE IF EXISTS language_descriptions;
2045 CREATE TABLE language_descriptions (
2049 description varchar(255),
2051 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2053 -- bi-directional support, keyed by script subcode
2054 DROP TABLE IF EXISTS language_script_bidi;
2055 CREATE TABLE language_script_bidi (
2056 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2057 bidi varchar(3), -- rtl ltr
2058 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2059 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2061 -- TODO: need to map language subtags to script subtags for detection
2062 -- of bidi when script is not specified (like ar, he)
2063 DROP TABLE IF EXISTS language_script_mapping;
2064 CREATE TABLE language_script_mapping (
2065 language_subtag varchar(25),
2066 script_subtag varchar(25),
2067 KEY `language_subtag` (`language_subtag`)
2068 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2070 DROP TABLE IF EXISTS `permissions`;
2071 CREATE TABLE `permissions` (
2072 `module_bit` int(11) NOT NULL DEFAULT 0,
2073 `code` varchar(30) DEFAULT NULL,
2074 `description` varchar(255) DEFAULT NULL,
2075 PRIMARY KEY (`module_bit`, `code`),
2076 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2077 ON DELETE CASCADE ON UPDATE CASCADE
2078 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2080 DROP TABLE IF EXISTS serialitems;
2081 CREATE TABLE serialitems (
2082 serialid int(11) NOT NULL,
2083 itemnumber int(11) NOT NULL,
2084 UNIQUE KEY `serialididx` (`serialid`)
2085 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2087 DROP TABLE IF EXISTS `user_permissions`;
2088 CREATE TABLE `user_permissions` (
2089 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2090 `module_bit` int(11) NOT NULL DEFAULT 0,
2091 `code` varchar(30) DEFAULT NULL,
2092 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2093 ON DELETE CASCADE ON UPDATE CASCADE,
2094 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2095 ON DELETE CASCADE ON UPDATE CASCADE
2096 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2098 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2099 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2100 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2101 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2102 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2103 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2104 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2105 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;