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 `lastincrement` decimal(28,6) default NULL,
34 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
35 `notify_id` int(11) NOT NULL default 0,
36 `notify_level` int(2) NOT NULL default 0,
37 KEY `acctsborridx` (`borrowernumber`),
38 KEY `timeidx` (`timestamp`),
39 KEY `itemnumber` (`itemnumber`),
40 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
41 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
45 -- Table structure for table `accountoffsets`
48 DROP TABLE IF EXISTS `accountoffsets`;
49 CREATE TABLE `accountoffsets` (
50 `borrowernumber` int(11) NOT NULL default 0,
51 `accountno` smallint(6) NOT NULL default 0,
52 `offsetaccount` smallint(6) NOT NULL default 0,
53 `offsetamount` decimal(28,6) default NULL,
54 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
55 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
56 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
59 -- Table structure for table `action_logs`
62 DROP TABLE IF EXISTS `action_logs`;
63 CREATE TABLE `action_logs` (
64 `action_id` int(11) NOT NULL auto_increment,
65 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
66 `user` int(11) NOT NULL default 0,
69 `object` int(11) default NULL,
71 PRIMARY KEY (`action_id`),
72 KEY (`timestamp`,`user`)
73 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
76 -- Table structure for table `alert`
79 DROP TABLE IF EXISTS `alert`;
80 CREATE TABLE `alert` (
81 `alertid` int(11) NOT NULL auto_increment,
82 `borrowernumber` int(11) NOT NULL default 0,
83 `type` varchar(10) NOT NULL default '',
84 `externalid` varchar(20) NOT NULL default '',
85 PRIMARY KEY (`alertid`),
86 KEY `borrowernumber` (`borrowernumber`),
87 KEY `type` (`type`,`externalid`)
88 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
91 -- Table structure for table `aqbasket`
94 DROP TABLE IF EXISTS `aqbasket`;
95 CREATE TABLE `aqbasket` (
96 `basketno` int(11) NOT NULL auto_increment,
97 `creationdate` date default NULL,
98 `closedate` date default NULL,
99 `booksellerid` int(11) NOT NULL default 1,
100 `authorisedby` varchar(10) default NULL,
101 `booksellerinvoicenumber` mediumtext,
102 PRIMARY KEY (`basketno`),
103 KEY `booksellerid` (`booksellerid`),
104 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
105 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
108 -- Table structure for table `aqbookfund`
111 DROP TABLE IF EXISTS `aqbookfund`;
112 CREATE TABLE `aqbookfund` (
113 `bookfundid` varchar(10) NOT NULL default '',
114 `bookfundname` mediumtext,
115 `bookfundgroup` varchar(5) default NULL,
116 `branchcode` varchar(10) NOT NULL default '',
117 PRIMARY KEY (`bookfundid`,`branchcode`)
118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
121 -- Table structure for table `aqbooksellers`
124 DROP TABLE IF EXISTS `aqbooksellers`;
125 CREATE TABLE `aqbooksellers` (
126 `id` int(11) NOT NULL auto_increment,
127 `name` mediumtext NOT NULL,
128 `address1` mediumtext,
129 `address2` mediumtext,
130 `address3` mediumtext,
131 `address4` mediumtext,
132 `phone` varchar(30) default NULL,
133 `accountnumber` mediumtext,
134 `othersupplier` mediumtext,
135 `currency` varchar(3) NOT NULL default '',
136 `deliverydays` smallint(6) default NULL,
137 `followupdays` smallint(6) default NULL,
138 `followupscancel` smallint(6) default NULL,
139 `specialty` mediumtext,
140 `booksellerfax` mediumtext,
142 `bookselleremail` mediumtext,
143 `booksellerurl` mediumtext,
144 `contact` varchar(100) default NULL,
146 `url` varchar(255) default NULL,
147 `contpos` varchar(100) default NULL,
148 `contphone` varchar(100) default NULL,
149 `contfax` varchar(100) default NULL,
150 `contaltphone` varchar(100) default NULL,
151 `contemail` varchar(100) default NULL,
152 `contnotes` mediumtext,
153 `active` tinyint(4) default NULL,
154 `listprice` varchar(10) default NULL,
155 `invoiceprice` varchar(10) default NULL,
156 `gstreg` tinyint(4) default NULL,
157 `listincgst` tinyint(4) default NULL,
158 `invoiceincgst` tinyint(4) default NULL,
159 `discount` float(6,4) default NULL,
160 `fax` varchar(50) default NULL,
161 `nocalc` int(11) default NULL,
162 `invoicedisc` float(6,4) default NULL,
164 KEY `listprice` (`listprice`),
165 KEY `invoiceprice` (`invoiceprice`),
166 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
167 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
171 -- Table structure for table `aqbudget`
174 DROP TABLE IF EXISTS `aqbudget`;
175 CREATE TABLE `aqbudget` (
176 `bookfundid` varchar(10) NOT NULL default '',
177 `startdate` date NOT NULL default 0,
178 `enddate` date default NULL,
179 `budgetamount` decimal(13,2) default NULL,
180 `aqbudgetid` tinyint(4) NOT NULL auto_increment,
181 `branchcode` varchar(10) default NULL,
182 PRIMARY KEY (`aqbudgetid`)
183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
186 -- Table structure for table `aqorderbreakdown`
189 DROP TABLE IF EXISTS `aqorderbreakdown`;
190 CREATE TABLE `aqorderbreakdown` (
191 `ordernumber` int(11) default NULL,
192 `linenumber` int(11) default NULL,
193 `branchcode` varchar(10) default NULL,
194 `bookfundid` varchar(10) NOT NULL default '',
195 `allocation` smallint(6) default NULL,
196 KEY `ordernumber` (`ordernumber`),
197 KEY `bookfundid` (`bookfundid`),
198 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
199 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
203 -- Table structure for table `aqorderdelivery`
206 DROP TABLE IF EXISTS `aqorderdelivery`;
207 CREATE TABLE `aqorderdelivery` (
208 `ordernumber` date default NULL,
209 `deliverynumber` smallint(6) NOT NULL default 0,
210 `deliverydate` varchar(18) default NULL,
211 `qtydelivered` smallint(6) default NULL,
212 `deliverycomments` mediumtext
213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
216 -- Table structure for table `aqorders`
219 DROP TABLE IF EXISTS `aqorders`;
220 CREATE TABLE `aqorders` (
221 `ordernumber` int(11) NOT NULL auto_increment,
222 `biblionumber` int(11) default NULL,
224 `entrydate` date default NULL,
225 `quantity` smallint(6) default NULL,
226 `currency` varchar(3) default NULL,
227 `listprice` decimal(28,6) default NULL,
228 `totalamount` decimal(28,6) default NULL,
229 `datereceived` date default NULL,
230 `booksellerinvoicenumber` mediumtext,
231 `freight` decimal(28,6) default NULL,
232 `unitprice` decimal(28,6) default NULL,
233 `quantityreceived` smallint(6) default NULL,
234 `cancelledby` varchar(10) default NULL,
235 `datecancellationprinted` date default NULL,
237 `supplierreference` mediumtext,
238 `purchaseordernumber` mediumtext,
239 `subscription` tinyint(1) default NULL,
240 `serialid` varchar(30) default NULL,
241 `basketno` int(11) default NULL,
242 `biblioitemnumber` int(11) default NULL,
243 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
244 `rrp` decimal(13,2) default NULL,
245 `ecost` decimal(13,2) default NULL,
246 `gst` decimal(13,2) default NULL,
247 `budgetdate` date default NULL,
248 `sort1` varchar(80) default NULL,
249 `sort2` varchar(80) default NULL,
250 PRIMARY KEY (`ordernumber`),
251 KEY `basketno` (`basketno`),
252 KEY `biblionumber` (`biblionumber`),
253 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
254 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
255 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
258 -- Table structure for table `auth_header`
261 DROP TABLE IF EXISTS `auth_header`;
262 CREATE TABLE `auth_header` (
263 `authid` bigint(20) unsigned NOT NULL auto_increment,
264 `authtypecode` varchar(10) NOT NULL default '',
265 `datecreated` date default NULL,
266 `datemodified` date default NULL,
267 `origincode` varchar(20) default NULL,
268 `authtrees` mediumtext,
270 `linkid` bigint(20) default NULL,
271 `marcxml` longtext NOT NULL,
272 PRIMARY KEY (`authid`),
273 KEY `origincode` (`origincode`)
274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
277 -- Table structure for table `auth_subfield_structure`
280 DROP TABLE IF EXISTS `auth_subfield_structure`;
281 CREATE TABLE `auth_subfield_structure` (
282 `authtypecode` varchar(10) NOT NULL default '',
283 `tagfield` varchar(3) NOT NULL default '',
284 `tagsubfield` varchar(1) NOT NULL default '',
285 `liblibrarian` varchar(255) NOT NULL default '',
286 `libopac` varchar(255) NOT NULL default '',
287 `repeatable` tinyint(4) NOT NULL default 0,
288 `mandatory` tinyint(4) NOT NULL default 0,
289 `tab` tinyint(1) default NULL,
290 `authorised_value` varchar(10) default NULL,
291 `value_builder` varchar(80) default NULL,
292 `seealso` varchar(255) default NULL,
293 `isurl` tinyint(1) default NULL,
294 `hidden` tinyint(3) NOT NULL default 0,
295 `linkid` tinyint(1) NOT NULL default 0,
296 `kohafield` varchar(45) NULL default '',
297 `frameworkcode` varchar(8) NOT NULL default '',
298 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
299 KEY `tab` (`authtypecode`,`tab`)
300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
303 -- Table structure for table `auth_tag_structure`
306 DROP TABLE IF EXISTS `auth_tag_structure`;
307 CREATE TABLE `auth_tag_structure` (
308 `authtypecode` varchar(10) NOT NULL default '',
309 `tagfield` varchar(3) NOT NULL default '',
310 `liblibrarian` varchar(255) NOT NULL default '',
311 `libopac` varchar(255) NOT NULL default '',
312 `repeatable` tinyint(4) NOT NULL default 0,
313 `mandatory` tinyint(4) NOT NULL default 0,
314 `authorised_value` varchar(10) default NULL,
315 PRIMARY KEY (`authtypecode`,`tagfield`),
316 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
320 -- Table structure for table `auth_types`
323 DROP TABLE IF EXISTS `auth_types`;
324 CREATE TABLE `auth_types` (
325 `authtypecode` varchar(10) NOT NULL default '',
326 `authtypetext` varchar(255) NOT NULL default '',
327 `auth_tag_to_report` varchar(3) NOT NULL default '',
328 `summary` mediumtext NOT NULL,
329 PRIMARY KEY (`authtypecode`)
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
333 -- Table structure for table `authorised_values`
336 DROP TABLE IF EXISTS `authorised_values`;
337 CREATE TABLE `authorised_values` (
338 `id` int(11) NOT NULL auto_increment,
339 `category` varchar(10) NOT NULL default '',
340 `authorised_value` varchar(80) NOT NULL default '',
341 `lib` varchar(80) default NULL,
342 `imageurl` varchar(200) default NULL,
344 KEY `name` (`category`)
345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
348 -- Table structure for table `biblio`
351 DROP TABLE IF EXISTS `biblio`;
352 CREATE TABLE `biblio` (
353 `biblionumber` int(11) NOT NULL auto_increment,
354 `frameworkcode` varchar(4) NOT NULL default '',
357 `unititle` mediumtext,
359 `serial` tinyint(1) default NULL,
360 `seriestitle` mediumtext,
361 `copyrightdate` smallint(6) default NULL,
362 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
363 `datecreated` DATE NOT NULL,
364 `abstract` mediumtext,
365 PRIMARY KEY (`biblionumber`),
366 KEY `blbnoidx` (`biblionumber`)
367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
370 -- Table structure for table `biblio_framework`
373 DROP TABLE IF EXISTS `biblio_framework`;
374 CREATE TABLE `biblio_framework` (
375 `frameworkcode` varchar(4) NOT NULL default '',
376 `frameworktext` varchar(255) NOT NULL default '',
377 PRIMARY KEY (`frameworkcode`)
378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
381 -- Table structure for table `biblioitems`
384 DROP TABLE IF EXISTS `biblioitems`;
385 CREATE TABLE `biblioitems` (
386 `biblioitemnumber` int(11) NOT NULL auto_increment,
387 `biblionumber` int(11) NOT NULL default 0,
390 `itemtype` varchar(10) default NULL,
391 `isbn` varchar(14) default NULL,
392 `issn` varchar(9) default NULL,
393 `publicationyear` text,
394 `publishercode` varchar(255) default NULL,
395 `volumedate` date default NULL,
397 `collectiontitle` mediumtext default NULL,
398 `collectionissn` text default NULL,
399 `collectionvolume` mediumtext default NULL,
400 `editionstatement` text default NULL,
401 `editionresponsibility` text default NULL,
402 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
403 `illus` varchar(255) default NULL,
404 `pages` varchar(255) default NULL,
406 `size` varchar(255) default NULL,
407 `place` varchar(255) default NULL,
408 `lccn` varchar(25) default NULL,
410 `url` varchar(255) default NULL,
411 `cn_source` varchar(10) default NULL,
412 `cn_class` varchar(30) default NULL,
413 `cn_item` varchar(10) default NULL,
414 `cn_suffix` varchar(10) default NULL,
415 `cn_sort` varchar(30) default NULL,
416 `totalissues` int(10),
417 `marcxml` longtext NOT NULL,
418 PRIMARY KEY (`biblioitemnumber`),
419 KEY `bibinoidx` (`biblioitemnumber`),
420 KEY `bibnoidx` (`biblionumber`),
422 KEY `publishercode` (`publishercode`),
423 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
424 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
427 -- Table structure for table `borrowers`
430 DROP TABLE IF EXISTS `borrowers`;
431 CREATE TABLE `borrowers` (
432 `borrowernumber` int(11) NOT NULL auto_increment,
433 `cardnumber` varchar(16) default NULL,
434 `surname` mediumtext NOT NULL,
437 `othernames` mediumtext,
439 `streetnumber` varchar(10) default NULL,
440 `streettype` varchar(50) default NULL,
441 `address` mediumtext NOT NULL,
443 `city` mediumtext NOT NULL,
444 `zipcode` varchar(25) default NULL,
447 `mobile` varchar(50) default NULL,
451 `B_streetnumber` varchar(10) default NULL,
452 `B_streettype` varchar(50) default NULL,
453 `B_address` varchar(100) default NULL,
455 `B_zipcode` varchar(25) default NULL,
457 `B_phone` mediumtext,
458 `dateofbirth` date default NULL,
459 `branchcode` varchar(10) NOT NULL default '',
460 `categorycode` varchar(10) NOT NULL default '',
461 `dateenrolled` date default NULL,
462 `dateexpiry` date default NULL,
463 `gonenoaddress` tinyint(1) default NULL,
464 `lost` tinyint(1) default NULL,
465 `debarred` tinyint(1) default NULL,
466 `contactname` mediumtext,
467 `contactfirstname` text,
469 `guarantorid` int(11) default NULL,
470 `borrowernotes` mediumtext,
471 `relationship` varchar(100) default NULL,
472 `ethnicity` varchar(50) default NULL,
473 `ethnotes` varchar(255) default NULL,
474 `sex` varchar(1) default NULL,
475 `password` varchar(30) default NULL,
476 `flags` int(11) default NULL,
477 `userid` varchar(30) default NULL,
478 `opacnote` mediumtext,
479 `contactnote` varchar(255) default NULL,
480 `sort1` varchar(80) default NULL,
481 `sort2` varchar(80) default NULL,
482 `altcontactfirstname` varchar(255) default NULL,
483 `altcontactsurname` varchar(255) default NULL,
484 `altcontactaddress1` varchar(255) default NULL,
485 `altcontactaddress2` varchar(255) default NULL,
486 `altcontactaddress3` varchar(255) default NULL,
487 `altcontactzipcode` varchar(50) default NULL,
488 `altcontactphone` varchar(50) default NULL,
489 UNIQUE KEY `cardnumber` (`cardnumber`),
490 PRIMARY KEY `borrowernumber` (`borrowernumber`),
491 KEY `categorycode` (`categorycode`),
492 KEY `branchcode` (`branchcode`),
493 KEY `userid` (`userid`),
494 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
495 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
496 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
499 -- Table structure for table `borrower_attribute_types`
502 DROP TABLE IF EXISTS `borrower_attribute_types`;
503 CREATE TABLE `borrower_attribute_types` (
504 `code` varchar(10) NOT NULL,
505 `description` varchar(255) NOT NULL,
506 `repeatable` tinyint(1) NOT NULL default 0,
507 `unique_id` tinyint(1) NOT NULL default 0,
508 `opac_display` tinyint(1) NOT NULL default 0,
509 `password_allowed` tinyint(1) NOT NULL default 0,
510 `staff_searchable` tinyint(1) NOT NULL default 0,
511 `authorised_value_category` varchar(10) default NULL,
513 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
516 -- Table structure for table `borrower_attributes`
519 DROP TABLE IF EXISTS `borrower_attributes`;
520 CREATE TABLE `borrower_attributes` (
521 `borrowernumber` int(11) NOT NULL,
522 `code` varchar(10) NOT NULL,
523 `attribute` varchar(30) default NULL,
524 `password` varchar(30) default NULL,
525 KEY `borrowernumber` (`borrowernumber`),
526 KEY `code_attribute` (`code`, `attribute`),
527 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
528 ON DELETE CASCADE ON UPDATE CASCADE,
529 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
530 ON DELETE CASCADE ON UPDATE CASCADE
531 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
534 -- Table structure for table `branchcategories`
537 DROP TABLE IF EXISTS `branchcategories`;
538 CREATE TABLE `branchcategories` (
539 `categorycode` varchar(10) NOT NULL default '',
540 `categoryname` varchar(32),
541 `codedescription` mediumtext,
542 `categorytype` varchar(16),
543 PRIMARY KEY (`categorycode`)
544 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
547 -- Table structure for table `branches`
550 DROP TABLE IF EXISTS `branches`;
551 CREATE TABLE `branches` (
552 `branchcode` varchar(10) NOT NULL default '',
553 `branchname` mediumtext NOT NULL,
554 `branchaddress1` mediumtext,
555 `branchaddress2` mediumtext,
556 `branchaddress3` mediumtext,
557 `branchphone` mediumtext,
558 `branchfax` mediumtext,
559 `branchemail` mediumtext,
560 `issuing` tinyint(4) default NULL,
561 `branchip` varchar(15) default NULL,
562 `branchprinter` varchar(100) default NULL,
563 UNIQUE KEY `branchcode` (`branchcode`)
564 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
567 -- Table structure for table `branchrelations`
570 DROP TABLE IF EXISTS `branchrelations`;
571 CREATE TABLE `branchrelations` (
572 `branchcode` varchar(10) NOT NULL default '',
573 `categorycode` varchar(10) NOT NULL default '',
574 PRIMARY KEY (`branchcode`,`categorycode`),
575 KEY `branchcode` (`branchcode`),
576 KEY `categorycode` (`categorycode`),
577 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
578 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
579 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
582 -- Table structure for table `branchtransfers`
585 DROP TABLE IF EXISTS `branchtransfers`;
586 CREATE TABLE `branchtransfers` (
587 `itemnumber` int(11) NOT NULL default 0,
588 `datesent` datetime default NULL,
589 `frombranch` varchar(10) NOT NULL default '',
590 `datearrived` datetime default NULL,
591 `tobranch` varchar(10) NOT NULL default '',
592 `comments` mediumtext,
593 KEY `frombranch` (`frombranch`),
594 KEY `tobranch` (`tobranch`),
595 KEY `itemnumber` (`itemnumber`),
596 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
597 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
598 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
599 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
603 -- Table structure for table `browser`
605 DROP TABLE IF EXISTS `browser`;
606 CREATE TABLE `browser` (
607 `level` int(11) NOT NULL,
608 `classification` varchar(20) NOT NULL,
609 `description` varchar(255) NOT NULL,
610 `number` bigint(20) NOT NULL,
611 `endnode` tinyint(4) NOT NULL
612 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
615 -- Table structure for table `categories`
618 DROP TABLE IF EXISTS `categories`;
619 CREATE TABLE `categories` (
620 `categorycode` varchar(10) NOT NULL default '',
621 `description` mediumtext,
622 `enrolmentperiod` smallint(6) default NULL,
623 `upperagelimit` smallint(6) default NULL,
624 `dateofbirthrequired` tinyint(1) default NULL,
625 `finetype` varchar(30) default NULL,
626 `bulk` tinyint(1) default NULL,
627 `enrolmentfee` decimal(28,6) default NULL,
628 `overduenoticerequired` tinyint(1) default NULL,
629 `issuelimit` smallint(6) default NULL,
630 `reservefee` decimal(28,6) default NULL,
631 `category_type` varchar(1) NOT NULL default 'A',
632 PRIMARY KEY (`categorycode`),
633 UNIQUE KEY `categorycode` (`categorycode`)
634 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
637 -- Table structure for table `cities`
640 DROP TABLE IF EXISTS `cities`;
641 CREATE TABLE `cities` (
642 `cityid` int(11) NOT NULL auto_increment,
643 `city_name` varchar(100) NOT NULL default '',
644 `city_zipcode` varchar(20) default NULL,
645 PRIMARY KEY (`cityid`)
646 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
649 -- Table structure for table `class_sort_rules`
652 DROP TABLE IF EXISTS `class_sort_rules`;
653 CREATE TABLE `class_sort_rules` (
654 `class_sort_rule` varchar(10) NOT NULL default '',
655 `description` mediumtext,
656 `sort_routine` varchar(30) NOT NULL default '',
657 PRIMARY KEY (`class_sort_rule`),
658 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
659 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
662 -- Table structure for table `class_sources`
665 DROP TABLE IF EXISTS `class_sources`;
666 CREATE TABLE `class_sources` (
667 `cn_source` varchar(10) NOT NULL default '',
668 `description` mediumtext,
669 `used` tinyint(4) NOT NULL default 0,
670 `class_sort_rule` varchar(10) NOT NULL default '',
671 PRIMARY KEY (`cn_source`),
672 UNIQUE KEY `cn_source_idx` (`cn_source`),
673 KEY `used_idx` (`used`),
674 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
675 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
678 -- Table structure for table `currency`
681 DROP TABLE IF EXISTS `currency`;
682 CREATE TABLE `currency` (
683 `currency` varchar(10) NOT NULL default '',
684 `symbol` varchar(5) default NULL,
685 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
686 `rate` float(7,5) default NULL,
687 PRIMARY KEY (`currency`)
688 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
691 -- Table structure for table `deletedbiblio`
694 DROP TABLE IF EXISTS `deletedbiblio`;
695 CREATE TABLE `deletedbiblio` (
696 `biblionumber` int(11) NOT NULL default 0,
697 `frameworkcode` varchar(4) NOT NULL default '',
700 `unititle` mediumtext,
702 `serial` tinyint(1) default NULL,
703 `seriestitle` mediumtext,
704 `copyrightdate` smallint(6) default NULL,
705 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
706 `datecreated` DATE NOT NULL,
707 `abstract` mediumtext,
708 PRIMARY KEY (`biblionumber`),
709 KEY `blbnoidx` (`biblionumber`)
710 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
713 -- Table structure for table `deletedbiblioitems`
716 DROP TABLE IF EXISTS `deletedbiblioitems`;
717 CREATE TABLE `deletedbiblioitems` (
718 `biblioitemnumber` int(11) NOT NULL default 0,
719 `biblionumber` int(11) NOT NULL default 0,
722 `itemtype` varchar(10) default NULL,
723 `isbn` varchar(14) default NULL,
724 `issn` varchar(9) default NULL,
725 `publicationyear` text,
726 `publishercode` varchar(255) default NULL,
727 `volumedate` date default NULL,
729 `collectiontitle` mediumtext default NULL,
730 `collectionissn` text default NULL,
731 `collectionvolume` mediumtext default NULL,
732 `editionstatement` text default NULL,
733 `editionresponsibility` text default NULL,
734 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
735 `illus` varchar(255) default NULL,
736 `pages` varchar(255) default NULL,
738 `size` varchar(255) default NULL,
739 `place` varchar(255) default NULL,
740 `lccn` varchar(25) default NULL,
742 `url` varchar(255) default NULL,
743 `cn_source` varchar(10) default NULL,
744 `cn_class` varchar(30) default NULL,
745 `cn_item` varchar(10) default NULL,
746 `cn_suffix` varchar(10) default NULL,
747 `cn_sort` varchar(30) default NULL,
748 `totalissues` int(10),
749 `marcxml` longtext NOT NULL,
750 PRIMARY KEY (`biblioitemnumber`),
751 KEY `bibinoidx` (`biblioitemnumber`),
752 KEY `bibnoidx` (`biblionumber`),
754 KEY `publishercode` (`publishercode`)
755 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
758 -- Table structure for table `deletedborrowers`
761 DROP TABLE IF EXISTS `deletedborrowers`;
762 CREATE TABLE `deletedborrowers` (
763 `borrowernumber` int(11) NOT NULL default 0,
764 `cardnumber` varchar(9) NOT NULL default '',
765 `surname` mediumtext NOT NULL,
768 `othernames` mediumtext,
770 `streetnumber` varchar(10) default NULL,
771 `streettype` varchar(50) default NULL,
772 `address` mediumtext NOT NULL,
774 `city` mediumtext NOT NULL,
775 `zipcode` varchar(25) default NULL,
778 `mobile` varchar(50) default NULL,
782 `B_streetnumber` varchar(10) default NULL,
783 `B_streettype` varchar(50) default NULL,
784 `B_address` varchar(100) default NULL,
786 `B_zipcode` varchar(25) default NULL,
788 `B_phone` mediumtext,
789 `dateofbirth` date default NULL,
790 `branchcode` varchar(10) NOT NULL default '',
791 `categorycode` varchar(2) default NULL,
792 `dateenrolled` date default NULL,
793 `dateexpiry` date default NULL,
794 `gonenoaddress` tinyint(1) default NULL,
795 `lost` tinyint(1) default NULL,
796 `debarred` tinyint(1) default NULL,
797 `contactname` mediumtext,
798 `contactfirstname` text,
800 `guarantorid` int(11) default NULL,
801 `borrowernotes` mediumtext,
802 `relationship` varchar(100) default NULL,
803 `ethnicity` varchar(50) default NULL,
804 `ethnotes` varchar(255) default NULL,
805 `sex` varchar(1) default NULL,
806 `password` varchar(30) default NULL,
807 `flags` int(11) default NULL,
808 `userid` varchar(30) default NULL,
809 `opacnote` mediumtext,
810 `contactnote` varchar(255) default NULL,
811 `sort1` varchar(80) default NULL,
812 `sort2` varchar(80) default NULL,
813 `altcontactfirstname` varchar(255) default NULL,
814 `altcontactsurname` varchar(255) default NULL,
815 `altcontactaddress1` varchar(255) default NULL,
816 `altcontactaddress2` varchar(255) default NULL,
817 `altcontactaddress3` varchar(255) default NULL,
818 `altcontactzipcode` varchar(50) default NULL,
819 `altcontactphone` varchar(50) default NULL,
820 KEY `borrowernumber` (`borrowernumber`),
821 KEY `cardnumber` (`cardnumber`)
822 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
825 -- Table structure for table `deleteditems`
828 DROP TABLE IF EXISTS `deleteditems`;
829 CREATE TABLE `deleteditems` (
830 `itemnumber` int(11) NOT NULL default 0,
831 `biblionumber` int(11) NOT NULL default 0,
832 `biblioitemnumber` int(11) NOT NULL default 0,
833 `barcode` varchar(20) default NULL,
834 `dateaccessioned` date default NULL,
835 `booksellerid` mediumtext default NULL,
836 `homebranch` varchar(10) default NULL,
837 `price` decimal(8,2) default NULL,
838 `replacementprice` decimal(8,2) default NULL,
839 `replacementpricedate` date default NULL,
840 `datelastborrowed` date default NULL,
841 `datelastseen` date default NULL,
842 `stack` tinyint(1) default NULL,
843 `notforloan` tinyint(1) NOT NULL default 0,
844 `damaged` tinyint(1) NOT NULL default 0,
845 `itemlost` tinyint(1) NOT NULL default 0,
846 `wthdrawn` tinyint(1) NOT NULL default 0,
847 `itemcallnumber` varchar(30) default NULL,
848 `issues` smallint(6) default NULL,
849 `renewals` smallint(6) default NULL,
850 `reserves` smallint(6) default NULL,
851 `restricted` tinyint(1) default NULL,
852 `itemnotes` mediumtext,
853 `holdingbranch` varchar(10) default NULL,
854 `paidfor` mediumtext,
855 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
856 `location` varchar(80) default NULL,
857 `onloan` date default NULL,
858 `cn_source` varchar(10) default NULL,
859 `cn_sort` varchar(30) default NULL,
860 `ccode` varchar(10) default NULL,
861 `materials` varchar(10) default NULL,
862 `uri` varchar(255) default NULL,
863 `itype` varchar(10) default NULL,
864 `more_subfields_xml` longtext default NULL,
865 `enumchron` varchar(80) default NULL,
866 `copynumber` smallint(6) default NULL,
868 PRIMARY KEY (`itemnumber`),
869 KEY `delitembarcodeidx` (`barcode`),
870 KEY `delitembinoidx` (`biblioitemnumber`),
871 KEY `delitembibnoidx` (`biblionumber`),
872 KEY `delhomebranch` (`homebranch`),
873 KEY `delholdingbranch` (`holdingbranch`)
874 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
877 -- Table structure for table `ethnicity`
880 DROP TABLE IF EXISTS `ethnicity`;
881 CREATE TABLE `ethnicity` (
882 `code` varchar(10) NOT NULL default '',
883 `name` varchar(255) default NULL,
885 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
888 -- Table structure for table `import_batches`
891 DROP TABLE IF EXISTS `import_batches`;
892 CREATE TABLE `import_batches` (
893 `import_batch_id` int(11) NOT NULL auto_increment,
894 `matcher_id` int(11) default NULL,
895 `template_id` int(11) default NULL,
896 `branchcode` varchar(10) default NULL,
897 `num_biblios` int(11) NOT NULL default 0,
898 `num_items` int(11) NOT NULL default 0,
899 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
900 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
901 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
902 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
903 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
904 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
905 `file_name` varchar(100),
906 `comments` mediumtext,
907 PRIMARY KEY (`import_batch_id`),
908 KEY `branchcode` (`branchcode`)
909 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
912 -- Table structure for table `import_records`
915 DROP TABLE IF EXISTS `import_records`;
916 CREATE TABLE `import_records` (
917 `import_record_id` int(11) NOT NULL auto_increment,
918 `import_batch_id` int(11) NOT NULL,
919 `branchcode` varchar(10) default NULL,
920 `record_sequence` int(11) NOT NULL default 0,
921 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
922 `import_date` DATE default NULL,
923 `marc` longblob NOT NULL,
924 `marcxml` longtext NOT NULL,
925 `marcxml_old` longtext NOT NULL,
926 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
927 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
928 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
929 `import_error` mediumtext,
930 `encoding` varchar(40) NOT NULL default '',
931 `z3950random` varchar(40) default NULL,
932 PRIMARY KEY (`import_record_id`),
933 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
934 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
935 KEY `branchcode` (`branchcode`),
936 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
937 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
940 -- Table structure for `import_record_matches`
942 DROP TABLE IF EXISTS `import_record_matches`;
943 CREATE TABLE `import_record_matches` (
944 `import_record_id` int(11) NOT NULL,
945 `candidate_match_id` int(11) NOT NULL,
946 `score` int(11) NOT NULL default 0,
947 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
948 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
949 KEY `record_score` (`import_record_id`, `score`)
950 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
953 -- Table structure for table `import_biblios`
956 DROP TABLE IF EXISTS `import_biblios`;
957 CREATE TABLE `import_biblios` (
958 `import_record_id` int(11) NOT NULL,
959 `matched_biblionumber` int(11) default NULL,
960 `control_number` varchar(25) default NULL,
961 `original_source` varchar(25) default NULL,
962 `title` varchar(128) default NULL,
963 `author` varchar(80) default NULL,
964 `isbn` varchar(14) default NULL,
965 `issn` varchar(9) default NULL,
966 `has_items` tinyint(1) NOT NULL default 0,
967 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
968 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
969 KEY `matched_biblionumber` (`matched_biblionumber`),
970 KEY `title` (`title`),
972 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
975 -- Table structure for table `import_items`
978 DROP TABLE IF EXISTS `import_items`;
979 CREATE TABLE `import_items` (
980 `import_items_id` int(11) NOT NULL auto_increment,
981 `import_record_id` int(11) NOT NULL,
982 `itemnumber` int(11) default NULL,
983 `branchcode` varchar(10) default NULL,
984 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
985 `marcxml` longtext NOT NULL,
986 `import_error` mediumtext,
987 PRIMARY KEY (`import_items_id`),
988 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
989 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
990 KEY `itemnumber` (`itemnumber`),
991 KEY `branchcode` (`branchcode`)
992 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
995 -- Table structure for table `issues`
998 DROP TABLE IF EXISTS `issues`;
999 CREATE TABLE `issues` (
1000 `borrowernumber` int(11) default NULL,
1001 `itemnumber` int(11) default NULL,
1002 `date_due` date default NULL,
1003 `branchcode` varchar(10) default NULL,
1004 `issuingbranch` varchar(18) default NULL,
1005 `returndate` date default NULL,
1006 `lastreneweddate` date default NULL,
1007 `return` varchar(4) default NULL,
1008 `renewals` tinyint(4) default NULL,
1009 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1010 `issuedate` date default NULL,
1011 KEY `issuesborridx` (`borrowernumber`),
1012 KEY `issuesitemidx` (`itemnumber`),
1013 KEY `bordate` (`borrowernumber`,`timestamp`),
1014 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1015 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1016 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1019 -- Table structure for table `issuingrules`
1022 DROP TABLE IF EXISTS `issuingrules`;
1023 CREATE TABLE `issuingrules` (
1024 `categorycode` varchar(10) NOT NULL default '',
1025 `itemtype` varchar(10) NOT NULL default '',
1026 `restrictedtype` tinyint(1) default NULL,
1027 `rentaldiscount` decimal(28,6) default NULL,
1028 `reservecharge` decimal(28,6) default NULL,
1029 `fine` decimal(28,6) default NULL,
1030 `firstremind` int(11) default NULL,
1031 `chargeperiod` int(11) default NULL,
1032 `accountsent` int(11) default NULL,
1033 `chargename` varchar(100) default NULL,
1034 `maxissueqty` int(4) default NULL,
1035 `issuelength` int(4) default NULL,
1036 `branchcode` varchar(10) NOT NULL default '',
1037 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1038 KEY `categorycode` (`categorycode`),
1039 KEY `itemtype` (`itemtype`)
1040 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1043 -- Table structure for table `items`
1046 DROP TABLE IF EXISTS `items`;
1047 CREATE TABLE `items` (
1048 `itemnumber` int(11) NOT NULL auto_increment,
1049 `biblionumber` int(11) NOT NULL default 0,
1050 `biblioitemnumber` int(11) NOT NULL default 0,
1051 `barcode` varchar(20) default NULL,
1052 `dateaccessioned` date default NULL,
1053 `booksellerid` mediumtext default NULL,
1054 `homebranch` varchar(10) default NULL,
1055 `price` decimal(8,2) default NULL,
1056 `replacementprice` decimal(8,2) default NULL,
1057 `replacementpricedate` date default NULL,
1058 `datelastborrowed` date default NULL,
1059 `datelastseen` date default NULL,
1060 `stack` tinyint(1) default NULL,
1061 `notforloan` tinyint(1) NOT NULL default 0,
1062 `damaged` tinyint(1) NOT NULL default 0,
1063 `itemlost` tinyint(1) NOT NULL default 0,
1064 `wthdrawn` tinyint(1) NOT NULL default 0,
1065 `itemcallnumber` varchar(30) default NULL,
1066 `issues` smallint(6) default NULL,
1067 `renewals` smallint(6) default NULL,
1068 `reserves` smallint(6) default NULL,
1069 `restricted` tinyint(1) default NULL,
1070 `itemnotes` mediumtext,
1071 `holdingbranch` varchar(10) default NULL,
1072 `paidfor` mediumtext,
1073 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1074 `location` varchar(80) default NULL,
1075 `onloan` date default NULL,
1076 `cn_source` varchar(10) default NULL,
1077 `cn_sort` varchar(30) default NULL,
1078 `ccode` varchar(10) default NULL,
1079 `materials` varchar(10) default NULL,
1080 `uri` varchar(255) default NULL,
1081 `itype` varchar(10) default NULL,
1082 `more_subfields_xml` longtext default NULL,
1083 `enumchron` varchar(80) default NULL,
1084 `copynumber` smallint(6) default NULL,
1085 PRIMARY KEY (`itemnumber`),
1086 UNIQUE KEY `itembarcodeidx` (`barcode`),
1087 KEY `itembinoidx` (`biblioitemnumber`),
1088 KEY `itembibnoidx` (`biblionumber`),
1089 KEY `homebranch` (`homebranch`),
1090 KEY `holdingbranch` (`holdingbranch`),
1091 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1092 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1093 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1094 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1097 -- Table structure for table `itemtypes`
1100 DROP TABLE IF EXISTS `itemtypes`;
1101 CREATE TABLE `itemtypes` (
1102 `itemtype` varchar(10) NOT NULL default '',
1103 `description` mediumtext,
1104 `renewalsallowed` smallint(6) default NULL,
1105 `rentalcharge` double(16,4) default NULL,
1106 `notforloan` smallint(6) default NULL,
1107 `imageurl` varchar(200) default NULL,
1109 PRIMARY KEY (`itemtype`),
1110 UNIQUE KEY `itemtype` (`itemtype`)
1111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1114 -- Table structure for table `labels`
1117 DROP TABLE IF EXISTS `labels`;
1118 CREATE TABLE `labels` (
1119 `labelid` int(11) NOT NULL auto_increment,
1120 `batch_id` varchar(10) NOT NULL default 1,
1121 `itemnumber` varchar(100) NOT NULL default '',
1122 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1123 PRIMARY KEY (`labelid`)
1124 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1127 -- Table structure for table `labels_conf`
1130 DROP TABLE IF EXISTS `labels_conf`;
1131 CREATE TABLE `labels_conf` (
1132 `id` int(4) NOT NULL auto_increment,
1133 `barcodetype` char(100) default '',
1134 `title` int(1) default '0',
1135 `subtitle` int(1) default '0',
1136 `itemtype` int(1) default '0',
1137 `barcode` int(1) default '0',
1138 `dewey` int(1) default '0',
1139 `classification` int(1) default NULL,
1140 `subclass` int(1) default '0',
1141 `itemcallnumber` int(1) default '0',
1142 `author` int(1) default '0',
1143 `issn` int(1) default '0',
1144 `isbn` int(1) default '0',
1145 `startlabel` int(2) NOT NULL default '1',
1146 `printingtype` char(32) default 'BAR',
1147 `formatstring` varchar(64) default NULL,
1148 `layoutname` char(20) NOT NULL default 'TEST',
1149 `guidebox` int(1) default '0',
1150 `active` tinyint(1) default '1',
1151 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1152 `ccode` char(4) collate utf8_unicode_ci default NULL,
1153 `callnum_split` int(1) default NULL,
1154 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1159 -- Table structure for table `labels_profile`
1162 DROP TABLE IF EXISTS `labels_profile`;
1163 CREATE TABLE `labels_profile` (
1164 `tmpl_id` int(4) NOT NULL,
1165 `prof_id` int(4) NOT NULL,
1166 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1167 UNIQUE KEY `prof_id` (`prof_id`)
1168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1171 -- Table structure for table `labels_templates`
1174 DROP TABLE IF EXISTS `labels_templates`;
1175 CREATE TABLE `labels_templates` (
1176 `tmpl_id` int(4) NOT NULL auto_increment,
1177 `tmpl_code` char(100) default '',
1178 `tmpl_desc` char(100) default '',
1179 `page_width` float default '0',
1180 `page_height` float default '0',
1181 `label_width` float default '0',
1182 `label_height` float default '0',
1183 `topmargin` float default '0',
1184 `leftmargin` float default '0',
1185 `cols` int(2) default '0',
1186 `rows` int(2) default '0',
1187 `colgap` float default '0',
1188 `rowgap` float default '0',
1189 `active` int(1) default NULL,
1190 `units` char(20) default 'PX',
1191 `fontsize` int(4) NOT NULL default '3',
1192 `font` char(10) NOT NULL default 'TR',
1193 PRIMARY KEY (`tmpl_id`)
1194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1197 -- Table structure for table `letter`
1200 DROP TABLE IF EXISTS `letter`;
1201 CREATE TABLE `letter` (
1202 `module` varchar(20) NOT NULL default '',
1203 `code` varchar(20) NOT NULL default '',
1204 `name` varchar(100) NOT NULL default '',
1205 `title` varchar(200) NOT NULL default '',
1207 PRIMARY KEY (`module`,`code`)
1208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1211 -- Table structure for table `marc_subfield_structure`
1214 DROP TABLE IF EXISTS `marc_subfield_structure`;
1215 CREATE TABLE `marc_subfield_structure` (
1216 `tagfield` varchar(3) NOT NULL default '',
1217 `tagsubfield` varchar(1) NOT NULL default '',
1218 `liblibrarian` varchar(255) NOT NULL default '',
1219 `libopac` varchar(255) NOT NULL default '',
1220 `repeatable` tinyint(4) NOT NULL default 0,
1221 `mandatory` tinyint(4) NOT NULL default 0,
1222 `kohafield` varchar(40) default NULL,
1223 `tab` tinyint(1) default NULL,
1224 `authorised_value` varchar(20) default NULL,
1225 `authtypecode` varchar(20) default NULL,
1226 `value_builder` varchar(80) default NULL,
1227 `isurl` tinyint(1) default NULL,
1228 `hidden` tinyint(1) default NULL,
1229 `frameworkcode` varchar(4) NOT NULL default '',
1230 `seealso` varchar(1100) default NULL,
1231 `link` varchar(80) default NULL,
1232 `defaultvalue` text default NULL,
1233 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1234 KEY `kohafield_2` (`kohafield`),
1235 KEY `tab` (`frameworkcode`,`tab`),
1236 KEY `kohafield` (`frameworkcode`,`kohafield`)
1237 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1240 -- Table structure for table `marc_tag_structure`
1243 DROP TABLE IF EXISTS `marc_tag_structure`;
1244 CREATE TABLE `marc_tag_structure` (
1245 `tagfield` varchar(3) NOT NULL default '',
1246 `liblibrarian` varchar(255) NOT NULL default '',
1247 `libopac` varchar(255) NOT NULL default '',
1248 `repeatable` tinyint(4) NOT NULL default 0,
1249 `mandatory` tinyint(4) NOT NULL default 0,
1250 `authorised_value` varchar(10) default NULL,
1251 `frameworkcode` varchar(4) NOT NULL default '',
1252 PRIMARY KEY (`frameworkcode`,`tagfield`)
1253 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1256 -- Table structure for table `marc_matchers`
1259 DROP TABLE IF EXISTS `marc_matchers`;
1260 CREATE TABLE `marc_matchers` (
1261 `matcher_id` int(11) NOT NULL auto_increment,
1262 `code` varchar(10) NOT NULL default '',
1263 `description` varchar(255) NOT NULL default '',
1264 `record_type` varchar(10) NOT NULL default 'biblio',
1265 `threshold` int(11) NOT NULL default 0,
1266 PRIMARY KEY (`matcher_id`),
1267 KEY `code` (`code`),
1268 KEY `record_type` (`record_type`)
1269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1272 -- Table structure for table `matchpoints`
1274 DROP TABLE IF EXISTS `matchpoints`;
1275 CREATE TABLE `matchpoints` (
1276 `matcher_id` int(11) NOT NULL,
1277 `matchpoint_id` int(11) NOT NULL auto_increment,
1278 `search_index` varchar(30) NOT NULL default '',
1279 `score` int(11) NOT NULL default 0,
1280 PRIMARY KEY (`matchpoint_id`),
1281 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1282 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1283 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1287 -- Table structure for table `matchpoint_components`
1289 DROP TABLE IF EXISTS `matchpoint_components`;
1290 CREATE TABLE `matchpoint_components` (
1291 `matchpoint_id` int(11) NOT NULL,
1292 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1293 sequence int(11) NOT NULL default 0,
1294 tag varchar(3) NOT NULL default '',
1295 subfields varchar(40) NOT NULL default '',
1296 offset int(4) NOT NULL default 0,
1297 length int(4) NOT NULL default 0,
1298 PRIMARY KEY (`matchpoint_component_id`),
1299 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1300 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1301 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1302 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1305 -- Table structure for table `matcher_component_norms`
1307 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1308 CREATE TABLE `matchpoint_component_norms` (
1309 `matchpoint_component_id` int(11) NOT NULL,
1310 `sequence` int(11) NOT NULL default 0,
1311 `norm_routine` varchar(50) NOT NULL default '',
1312 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1313 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1314 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1315 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1318 -- Table structure for table `matcher_matchpoints`
1320 DROP TABLE IF EXISTS `matcher_matchpoints`;
1321 CREATE TABLE `matcher_matchpoints` (
1322 `matcher_id` int(11) NOT NULL,
1323 `matchpoint_id` int(11) NOT NULL,
1324 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1325 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1326 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1327 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1328 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1331 -- Table structure for table `matchchecks`
1333 DROP TABLE IF EXISTS `matchchecks`;
1334 CREATE TABLE `matchchecks` (
1335 `matcher_id` int(11) NOT NULL,
1336 `matchcheck_id` int(11) NOT NULL auto_increment,
1337 `source_matchpoint_id` int(11) NOT NULL,
1338 `target_matchpoint_id` int(11) NOT NULL,
1339 PRIMARY KEY (`matchcheck_id`),
1340 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1341 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1342 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1343 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1344 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1345 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1349 -- Table structure for table `notifys`
1352 DROP TABLE IF EXISTS `notifys`;
1353 CREATE TABLE `notifys` (
1354 `notify_id` int(11) NOT NULL default 0,
1355 `borrowernumber` int(11) NOT NULL default 0,
1356 `itemnumber` int(11) NOT NULL default 0,
1357 `notify_date` date default NULL,
1358 `notify_send_date` date default NULL,
1359 `notify_level` int(1) NOT NULL default 0,
1360 `method` varchar(20) NOT NULL default ''
1361 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1364 -- Table structure for table `nozebra`
1367 DROP TABLE IF EXISTS `nozebra`;
1368 CREATE TABLE `nozebra` (
1369 `server` varchar(20) NOT NULL,
1370 `indexname` varchar(40) NOT NULL,
1371 `value` varchar(250) NOT NULL,
1372 `biblionumbers` longtext NOT NULL,
1373 KEY `indexname` (`server`,`indexname`),
1374 KEY `value` (`server`,`value`))
1375 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1378 -- Table structure for table `old_issues`
1381 DROP TABLE IF EXISTS `old_issues`;
1382 CREATE TABLE `old_issues` (
1383 `borrowernumber` int(11) default NULL,
1384 `itemnumber` int(11) default NULL,
1385 `date_due` date default NULL,
1386 `branchcode` varchar(10) default NULL,
1387 `issuingbranch` varchar(18) default NULL,
1388 `returndate` date default NULL,
1389 `lastreneweddate` date default NULL,
1390 `return` varchar(4) default NULL,
1391 `renewals` tinyint(4) default NULL,
1392 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1393 `issuedate` date default NULL,
1394 KEY `old_issuesborridx` (`borrowernumber`),
1395 KEY `old_issuesitemidx` (`itemnumber`),
1396 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1397 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1398 ON DELETE SET NULL ON UPDATE SET NULL,
1399 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1400 ON DELETE SET NULL ON UPDATE SET NULL
1401 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1404 -- Table structure for table `old_reserves`
1406 DROP TABLE IF EXISTS `old_reserves`;
1407 CREATE TABLE `old_reserves` (
1408 `borrowernumber` int(11) default NULL,
1409 `reservedate` date default NULL,
1410 `biblionumber` int(11) default NULL,
1411 `constrainttype` varchar(1) default NULL,
1412 `branchcode` varchar(10) default NULL,
1413 `notificationdate` date default NULL,
1414 `reminderdate` date default NULL,
1415 `cancellationdate` date default NULL,
1416 `reservenotes` mediumtext,
1417 `priority` smallint(6) default NULL,
1418 `found` varchar(1) default NULL,
1419 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1420 `itemnumber` int(11) default NULL,
1421 `waitingdate` date default NULL,
1422 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1423 KEY `old_reserves_biblionumber` (`biblionumber`),
1424 KEY `old_reserves_itemnumber` (`itemnumber`),
1425 KEY `old_reserves_branchcode` (`branchcode`),
1426 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1427 ON DELETE SET NULL ON UPDATE SET NULL,
1428 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1429 ON DELETE SET NULL ON UPDATE SET NULL,
1430 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1431 ON DELETE SET NULL ON UPDATE SET NULL
1432 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1435 -- Table structure for table `opac_news`
1438 DROP TABLE IF EXISTS `opac_news`;
1439 CREATE TABLE `opac_news` (
1440 `idnew` int(10) unsigned NOT NULL auto_increment,
1441 `title` varchar(250) NOT NULL default '',
1442 `new` text NOT NULL,
1443 `lang` varchar(25) NOT NULL default '',
1444 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1445 `expirationdate` date default NULL,
1446 `number` int(11) default NULL,
1447 PRIMARY KEY (`idnew`)
1448 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1451 -- Table structure for table `overduerules`
1454 DROP TABLE IF EXISTS `overduerules`;
1455 CREATE TABLE `overduerules` (
1456 `branchcode` varchar(10) NOT NULL default '',
1457 `categorycode` varchar(2) NOT NULL default '',
1458 `delay1` int(4) default 0,
1459 `letter1` varchar(20) default NULL,
1460 `debarred1` varchar(1) default 0,
1461 `delay2` int(4) default 0,
1462 `debarred2` varchar(1) default 0,
1463 `letter2` varchar(20) default NULL,
1464 `delay3` int(4) default 0,
1465 `letter3` varchar(20) default NULL,
1466 `debarred3` int(1) default 0,
1467 PRIMARY KEY (`branchcode`,`categorycode`)
1468 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1471 -- Table structure for table `patroncards`
1474 DROP TABLE IF EXISTS `patroncards`;
1475 CREATE TABLE `patroncards` (
1476 `cardid` int(11) NOT NULL auto_increment,
1477 `batch_id` varchar(10) NOT NULL default '1',
1478 `borrowernumber` int(11) NOT NULL,
1479 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1480 PRIMARY KEY (`cardid`),
1481 KEY `patroncards_ibfk_1` (`borrowernumber`),
1482 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1483 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1486 -- Table structure for table `patronimage`
1489 DROP TABLE IF EXISTS `patronimage`;
1490 CREATE TABLE `patronimage` (
1491 `cardnumber` varchar(16) NOT NULL,
1492 `mimetype` varchar(15) NOT NULL,
1493 `imagefile` mediumblob NOT NULL,
1494 PRIMARY KEY (`cardnumber`),
1495 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1496 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1499 -- Table structure for table `printers`
1502 DROP TABLE IF EXISTS `printers`;
1503 CREATE TABLE `printers` (
1504 `printername` varchar(40) NOT NULL default '',
1505 `printqueue` varchar(20) default NULL,
1506 `printtype` varchar(20) default NULL,
1507 PRIMARY KEY (`printername`)
1508 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1511 -- Table structure for table `printers_profile`
1514 DROP TABLE IF EXISTS `printers_profile`;
1515 CREATE TABLE `printers_profile` (
1516 `prof_id` int(4) NOT NULL auto_increment,
1517 `printername` varchar(40) NOT NULL,
1518 `tmpl_id` int(4) NOT NULL,
1519 `paper_bin` varchar(20) NOT NULL,
1520 `offset_horz` float default NULL,
1521 `offset_vert` float default NULL,
1522 `creep_horz` float default NULL,
1523 `creep_vert` float default NULL,
1524 `unit` char(20) NOT NULL default 'POINT',
1525 PRIMARY KEY (`prof_id`),
1526 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1527 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1531 -- Table structure for table `repeatable_holidays`
1534 DROP TABLE IF EXISTS `repeatable_holidays`;
1535 CREATE TABLE `repeatable_holidays` (
1536 `id` int(11) NOT NULL auto_increment,
1537 `branchcode` varchar(10) NOT NULL default '',
1538 `weekday` smallint(6) default NULL,
1539 `day` smallint(6) default NULL,
1540 `month` smallint(6) default NULL,
1541 `title` varchar(50) NOT NULL default '',
1542 `description` text NOT NULL,
1544 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1547 -- Table structure for table `reports_dictionary`
1550 DROP TABLE IF EXISTS `reports_dictionary`;
1551 CREATE TABLE reports_dictionary (
1552 `id` int(11) NOT NULL auto_increment,
1553 `name` varchar(255) default NULL,
1555 `date_created` datetime default NULL,
1556 `date_modified` datetime default NULL,
1558 `area` int(11) default NULL,
1560 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1563 -- Table structure for table `reserveconstraints`
1566 DROP TABLE IF EXISTS `reserveconstraints`;
1567 CREATE TABLE `reserveconstraints` (
1568 `borrowernumber` int(11) NOT NULL default 0,
1569 `reservedate` date default NULL,
1570 `biblionumber` int(11) NOT NULL default 0,
1571 `biblioitemnumber` int(11) default NULL,
1572 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1573 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1576 -- Table structure for table `reserves`
1579 DROP TABLE IF EXISTS `reserves`;
1580 CREATE TABLE `reserves` (
1581 `borrowernumber` int(11) NOT NULL default 0,
1582 `reservedate` date default NULL,
1583 `biblionumber` int(11) NOT NULL default 0,
1584 `constrainttype` varchar(1) default NULL,
1585 `branchcode` varchar(10) default NULL,
1586 `notificationdate` date default NULL,
1587 `reminderdate` date default NULL,
1588 `cancellationdate` date default NULL,
1589 `reservenotes` mediumtext,
1590 `priority` smallint(6) default NULL,
1591 `found` varchar(1) default NULL,
1592 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1593 `itemnumber` int(11) default NULL,
1594 `waitingdate` date default NULL,
1595 KEY `borrowernumber` (`borrowernumber`),
1596 KEY `biblionumber` (`biblionumber`),
1597 KEY `itemnumber` (`itemnumber`),
1598 KEY `branchcode` (`branchcode`),
1599 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1600 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1601 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1602 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1603 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1606 -- Table structure for table `reviews`
1609 DROP TABLE IF EXISTS `reviews`;
1610 CREATE TABLE `reviews` (
1611 `reviewid` int(11) NOT NULL auto_increment,
1612 `borrowernumber` int(11) default NULL,
1613 `biblionumber` int(11) default NULL,
1615 `approved` tinyint(4) default NULL,
1616 `datereviewed` datetime default NULL,
1617 PRIMARY KEY (`reviewid`)
1618 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1621 -- Table structure for table `roadtype`
1624 DROP TABLE IF EXISTS `roadtype`;
1625 CREATE TABLE `roadtype` (
1626 `roadtypeid` int(11) NOT NULL auto_increment,
1627 `road_type` varchar(100) NOT NULL default '',
1628 PRIMARY KEY (`roadtypeid`)
1629 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1632 -- Table structure for table `saved_sql`
1635 DROP TABLE IF EXISTS `saved_sql`;
1636 CREATE TABLE saved_sql (
1637 `id` int(11) NOT NULL auto_increment,
1638 `borrowernumber` int(11) default NULL,
1639 `date_created` datetime default NULL,
1640 `last_modified` datetime default NULL,
1642 `last_run` datetime default NULL,
1643 `report_name` varchar(255) default NULL,
1644 `type` varchar(255) default NULL,
1647 KEY boridx (`borrowernumber`)
1648 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1652 -- Table structure for `saved_reports`
1655 DROP TABLE IF EXISTS `saved_reports`;
1656 CREATE TABLE saved_reports (
1657 `id` int(11) NOT NULL auto_increment,
1658 `report_id` int(11) default NULL,
1660 `date_run` datetime default NULL,
1662 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1666 -- Table structure for table `serial`
1669 DROP TABLE IF EXISTS `serial`;
1670 CREATE TABLE `serial` (
1671 `serialid` int(11) NOT NULL auto_increment,
1672 `biblionumber` varchar(100) NOT NULL default '',
1673 `subscriptionid` varchar(100) NOT NULL default '',
1674 `serialseq` varchar(100) NOT NULL default '',
1675 `status` tinyint(4) NOT NULL default 0,
1676 `planneddate` date default NULL,
1678 `publisheddate` date default NULL,
1679 `itemnumber` text default NULL,
1680 `claimdate` date default NULL,
1681 `routingnotes` text,
1682 PRIMARY KEY (`serialid`)
1683 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1686 -- Table structure for table `sessions`
1689 DROP TABLE IF EXISTS sessions;
1690 CREATE TABLE sessions (
1691 `id` varchar(32) NOT NULL,
1692 `a_session` text NOT NULL,
1694 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1697 -- Table structure for table `special_holidays`
1700 DROP TABLE IF EXISTS `special_holidays`;
1701 CREATE TABLE `special_holidays` (
1702 `id` int(11) NOT NULL auto_increment,
1703 `branchcode` varchar(10) NOT NULL default '',
1704 `day` smallint(6) NOT NULL default 0,
1705 `month` smallint(6) NOT NULL default 0,
1706 `year` smallint(6) NOT NULL default 0,
1707 `isexception` smallint(1) NOT NULL default 1,
1708 `title` varchar(50) NOT NULL default '',
1709 `description` text NOT NULL,
1711 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1714 -- Table structure for table `statistics`
1717 DROP TABLE IF EXISTS `statistics`;
1718 CREATE TABLE `statistics` (
1719 `datetime` datetime default NULL,
1720 `branch` varchar(10) default NULL,
1721 `proccode` varchar(4) default NULL,
1722 `value` double(16,4) default NULL,
1723 `type` varchar(16) default NULL,
1725 `usercode` varchar(10) default NULL,
1726 `itemnumber` int(11) default NULL,
1727 `itemtype` varchar(10) default NULL,
1728 `borrowernumber` int(11) default NULL,
1729 `associatedborrower` int(11) default NULL,
1730 KEY `timeidx` (`datetime`)
1731 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1734 -- Table structure for table `stopwords`
1737 DROP TABLE IF EXISTS `stopwords`;
1738 CREATE TABLE `stopwords` (
1739 `word` varchar(255) default NULL
1740 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1743 -- Table structure for table `subscription`
1746 DROP TABLE IF EXISTS `subscription`;
1747 CREATE TABLE `subscription` (
1748 `biblionumber` int(11) NOT NULL default 0,
1749 `subscriptionid` int(11) NOT NULL auto_increment,
1750 `librarian` varchar(100) default '',
1751 `startdate` date default NULL,
1752 `aqbooksellerid` int(11) default 0,
1753 `cost` int(11) default 0,
1754 `aqbudgetid` int(11) default 0,
1755 `weeklength` int(11) default 0,
1756 `monthlength` int(11) default 0,
1757 `numberlength` int(11) default 0,
1758 `periodicity` tinyint(4) default 0,
1759 `dow` varchar(100) default '',
1760 `numberingmethod` varchar(100) default '',
1762 `status` varchar(100) NOT NULL default '',
1763 `add1` int(11) default 0,
1764 `every1` int(11) default 0,
1765 `whenmorethan1` int(11) default 0,
1766 `setto1` int(11) default NULL,
1767 `lastvalue1` int(11) default NULL,
1768 `add2` int(11) default 0,
1769 `every2` int(11) default 0,
1770 `whenmorethan2` int(11) default 0,
1771 `setto2` int(11) default NULL,
1772 `lastvalue2` int(11) default NULL,
1773 `add3` int(11) default 0,
1774 `every3` int(11) default 0,
1775 `innerloop1` int(11) default 0,
1776 `innerloop2` int(11) default 0,
1777 `innerloop3` int(11) default 0,
1778 `whenmorethan3` int(11) default 0,
1779 `setto3` int(11) default NULL,
1780 `lastvalue3` int(11) default NULL,
1781 `issuesatonce` tinyint(3) NOT NULL default 1,
1782 `firstacquidate` date default NULL,
1783 `manualhistory` tinyint(1) NOT NULL default 0,
1784 `irregularity` text,
1785 `letter` varchar(20) default NULL,
1786 `numberpattern` tinyint(3) default 0,
1787 `distributedto` text,
1788 `internalnotes` longtext,
1790 `branchcode` varchar(10) NOT NULL default '',
1791 `hemisphere` tinyint(3) default 0,
1792 `lastbranch` varchar(10),
1793 `serialsadditems` tinyint(1) NOT NULL default '0',
1794 PRIMARY KEY (`subscriptionid`)
1795 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1798 -- Table structure for table `subscriptionhistory`
1801 DROP TABLE IF EXISTS `subscriptionhistory`;
1802 CREATE TABLE `subscriptionhistory` (
1803 `biblionumber` int(11) NOT NULL default 0,
1804 `subscriptionid` int(11) NOT NULL default 0,
1805 `histstartdate` date default NULL,
1806 `enddate` date default NULL,
1807 `missinglist` longtext NOT NULL,
1808 `recievedlist` longtext NOT NULL,
1809 `opacnote` varchar(150) NOT NULL default '',
1810 `librariannote` varchar(150) NOT NULL default '',
1811 PRIMARY KEY (`subscriptionid`),
1812 KEY `biblionumber` (`biblionumber`)
1813 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1816 -- Table structure for table `subscriptionroutinglist`
1819 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1820 CREATE TABLE `subscriptionroutinglist` (
1821 `routingid` int(11) NOT NULL auto_increment,
1822 `borrowernumber` int(11) default NULL,
1823 `ranking` int(11) default NULL,
1824 `subscriptionid` int(11) default NULL,
1825 PRIMARY KEY (`routingid`)
1826 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1829 -- Table structure for table `suggestions`
1832 DROP TABLE IF EXISTS `suggestions`;
1833 CREATE TABLE `suggestions` (
1834 `suggestionid` int(8) NOT NULL auto_increment,
1835 `suggestedby` int(11) NOT NULL default 0,
1836 `managedby` int(11) default NULL,
1837 `STATUS` varchar(10) NOT NULL default '',
1839 `author` varchar(80) default NULL,
1840 `title` varchar(80) default NULL,
1841 `copyrightdate` smallint(6) default NULL,
1842 `publishercode` varchar(255) default NULL,
1843 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1844 `volumedesc` varchar(255) default NULL,
1845 `publicationyear` smallint(6) default 0,
1846 `place` varchar(255) default NULL,
1847 `isbn` varchar(10) default NULL,
1848 `mailoverseeing` smallint(1) default 0,
1849 `biblionumber` int(11) default NULL,
1851 PRIMARY KEY (`suggestionid`),
1852 KEY `suggestedby` (`suggestedby`),
1853 KEY `managedby` (`managedby`)
1854 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1857 -- Table structure for table `systempreferences`
1860 DROP TABLE IF EXISTS `systempreferences`;
1861 CREATE TABLE `systempreferences` (
1862 `variable` varchar(50) NOT NULL default '',
1864 `options` mediumtext,
1866 `type` varchar(20) default NULL,
1867 PRIMARY KEY (`variable`)
1868 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1871 -- Table structure for table `tags`
1874 DROP TABLE IF EXISTS `tags`;
1875 CREATE TABLE `tags` (
1876 `entry` varchar(255) NOT NULL default '',
1877 `weight` bigint(20) NOT NULL default 0,
1878 PRIMARY KEY (`entry`)
1879 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1882 -- Table structure for table `tags_all`
1885 CREATE TABLE `tags_all` (
1886 `tag_id` int(11) NOT NULL auto_increment,
1887 `borrowernumber` int(11) NOT NULL,
1888 `biblionumber` int(11) NOT NULL,
1889 `term` varchar(255) NOT NULL,
1890 `language` int(4) default NULL,
1891 `date_created` datetime NOT NULL,
1892 PRIMARY KEY (`tag_id`),
1893 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1894 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1895 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1896 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1897 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1898 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1899 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1902 -- Table structure for table `tags_approval`
1905 CREATE TABLE `tags_approval` (
1906 `term` varchar(255) NOT NULL,
1907 `approved` int(1) NOT NULL default '0',
1908 `date_approved` datetime default NULL,
1909 `approved_by` int(11) default NULL,
1910 `weight_total` int(9) NOT NULL default '1',
1911 PRIMARY KEY (`term`),
1912 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1913 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1914 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1915 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1918 -- Table structure for table `tags_index`
1921 CREATE TABLE `tags_index` (
1922 `term` varchar(255) NOT NULL,
1923 `biblionumber` int(11) NOT NULL,
1924 `weight` int(9) NOT NULL default '1',
1925 PRIMARY KEY (`term`,`biblionumber`),
1926 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1927 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1928 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1929 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1930 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1931 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1934 -- Table structure for table `userflags`
1937 DROP TABLE IF EXISTS `userflags`;
1938 CREATE TABLE `userflags` (
1939 `bit` int(11) NOT NULL default 0,
1940 `flag` varchar(30) default NULL,
1941 `flagdesc` varchar(255) default NULL,
1942 `defaulton` int(11) default NULL,
1944 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1947 -- Table structure for table `virtualshelves`
1950 DROP TABLE IF EXISTS `virtualshelves`;
1951 CREATE TABLE `virtualshelves` (
1952 `shelfnumber` int(11) NOT NULL auto_increment,
1953 `shelfname` varchar(255) default NULL,
1954 `owner` varchar(80) default NULL,
1955 `category` varchar(1) default NULL,
1956 `sortfield` varchar(16) default NULL,
1957 PRIMARY KEY (`shelfnumber`)
1958 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1961 -- Table structure for table `virtualshelfcontents`
1964 DROP TABLE IF EXISTS `virtualshelfcontents`;
1965 CREATE TABLE `virtualshelfcontents` (
1966 `shelfnumber` int(11) NOT NULL default 0,
1967 `biblionumber` int(11) NOT NULL default 0,
1968 `flags` int(11) default NULL,
1969 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1970 KEY `shelfnumber` (`shelfnumber`),
1971 KEY `biblionumber` (`biblionumber`),
1972 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1973 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1974 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1977 -- Table structure for table `z3950servers`
1980 DROP TABLE IF EXISTS `z3950servers`;
1981 CREATE TABLE `z3950servers` (
1982 `host` varchar(255) default NULL,
1983 `port` int(11) default NULL,
1984 `db` varchar(255) default NULL,
1985 `userid` varchar(255) default NULL,
1986 `password` varchar(255) default NULL,
1988 `id` int(11) NOT NULL auto_increment,
1989 `checked` smallint(6) default NULL,
1990 `rank` int(11) default NULL,
1991 `syntax` varchar(80) default NULL,
1993 `position` enum('primary','secondary','') NOT NULL default 'primary',
1994 `type` enum('zed','opensearch') NOT NULL default 'zed',
1995 `encoding` text default NULL,
1996 `description` text NOT NULL,
1998 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2001 -- Table structure for table `zebraqueue`
2004 DROP TABLE IF EXISTS `zebraqueue`;
2005 CREATE TABLE `zebraqueue` (
2006 `id` int(11) NOT NULL auto_increment,
2007 `biblio_auth_number` int(11) NOT NULL default '0',
2008 `operation` char(20) NOT NULL default '',
2009 `server` char(20) NOT NULL default '',
2010 `done` int(11) NOT NULL default '0',
2011 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2013 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2016 DROP TABLE IF EXISTS `services_throttle`;
2017 CREATE TABLE `services_throttle` (
2018 `service_type` varchar(10) NOT NULL default '',
2019 `service_count` varchar(45) default NULL,
2020 PRIMARY KEY (`service_type`)
2021 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2023 -- http://www.w3.org/International/articles/language-tags/
2026 DROP TABLE IF EXISTS language_subtag_registry;
2027 CREATE TABLE language_subtag_registry (
2029 type varchar(25), -- language-script-region-variant-extension-privateuse
2030 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2032 KEY `subtag` (`subtag`)
2033 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2035 -- TODO: add suppress_scripts
2036 -- this maps three letter codes defined in iso639.2 back to their
2037 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2038 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2039 CREATE TABLE language_rfc4646_to_iso639 (
2040 rfc4646_subtag varchar(25),
2041 iso639_2_code varchar(25),
2042 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2043 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2045 DROP TABLE IF EXISTS language_descriptions;
2046 CREATE TABLE language_descriptions (
2050 description varchar(255),
2052 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2054 -- bi-directional support, keyed by script subcode
2055 DROP TABLE IF EXISTS language_script_bidi;
2056 CREATE TABLE language_script_bidi (
2057 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2058 bidi varchar(3), -- rtl ltr
2059 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2060 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2062 -- TODO: need to map language subtags to script subtags for detection
2063 -- of bidi when script is not specified (like ar, he)
2064 DROP TABLE IF EXISTS language_script_mapping;
2065 CREATE TABLE language_script_mapping (
2066 language_subtag varchar(25),
2067 script_subtag varchar(25),
2068 KEY `language_subtag` (`language_subtag`)
2069 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2071 DROP TABLE IF EXISTS `permissions`;
2072 CREATE TABLE `permissions` (
2073 `module_bit` int(11) NOT NULL DEFAULT 0,
2074 `code` varchar(30) DEFAULT NULL,
2075 `description` varchar(255) DEFAULT NULL,
2076 PRIMARY KEY (`module_bit`, `code`),
2077 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2078 ON DELETE CASCADE ON UPDATE CASCADE
2079 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2081 DROP TABLE IF EXISTS serialitems;
2082 CREATE TABLE serialitems (
2083 serialid int(11) NOT NULL,
2084 itemnumber int(11) NOT NULL,
2085 UNIQUE KEY `serialididx` (`serialid`)
2086 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2088 DROP TABLE IF EXISTS `user_permissions`;
2089 CREATE TABLE `user_permissions` (
2090 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2091 `module_bit` int(11) NOT NULL DEFAULT 0,
2092 `code` varchar(30) DEFAULT NULL,
2093 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2094 ON DELETE CASCADE ON UPDATE CASCADE,
2095 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2096 ON DELETE CASCADE ON UPDATE CASCADE
2097 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2100 -- Table structure for table `tmp_holdsqueue`
2103 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2104 CREATE TABLE `tmp_holdsqueue` (
2105 `biblionumber` int(11) default NULL,
2106 `itemnumber` int(11) default NULL,
2107 `barcode` varchar(20) default NULL,
2108 `surname` mediumtext NOT NULL,
2111 `borrowernumber` int(11) NOT NULL,
2112 `cardnumber` varchar(16) default NULL,
2113 `reservedate` date default NULL,
2115 `itemcallnumber` varchar(30) default NULL,
2116 `holdingbranch` varchar(10) default NULL,
2117 `pickbranch` varchar(10) default NULL,
2119 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2121 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2122 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2123 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2124 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2125 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2126 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2127 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2128 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;