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 `branchcategories`
501 DROP TABLE IF EXISTS `branchcategories`;
502 CREATE TABLE `branchcategories` (
503 `categorycode` varchar(10) NOT NULL default '',
504 `categoryname` varchar(32),
505 `codedescription` mediumtext,
506 `categorytype` varchar(16),
507 PRIMARY KEY (`categorycode`)
508 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
511 -- Table structure for table `branches`
514 DROP TABLE IF EXISTS `branches`;
515 CREATE TABLE `branches` (
516 `branchcode` varchar(10) NOT NULL default '',
517 `branchname` mediumtext NOT NULL,
518 `branchaddress1` mediumtext,
519 `branchaddress2` mediumtext,
520 `branchaddress3` mediumtext,
521 `branchphone` mediumtext,
522 `branchfax` mediumtext,
523 `branchemail` mediumtext,
524 `issuing` tinyint(4) default NULL,
525 `branchip` varchar(15) default NULL,
526 `branchprinter` varchar(100) default NULL,
527 UNIQUE KEY `branchcode` (`branchcode`)
528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
531 -- Table structure for table `branchrelations`
534 DROP TABLE IF EXISTS `branchrelations`;
535 CREATE TABLE `branchrelations` (
536 `branchcode` varchar(10) NOT NULL default '',
537 `categorycode` varchar(10) NOT NULL default '',
538 PRIMARY KEY (`branchcode`,`categorycode`),
539 KEY `branchcode` (`branchcode`),
540 KEY `categorycode` (`categorycode`),
541 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
542 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
546 -- Table structure for table `branchtransfers`
549 DROP TABLE IF EXISTS `branchtransfers`;
550 CREATE TABLE `branchtransfers` (
551 `itemnumber` int(11) NOT NULL default 0,
552 `datesent` datetime default NULL,
553 `frombranch` varchar(10) NOT NULL default '',
554 `datearrived` datetime default NULL,
555 `tobranch` varchar(10) NOT NULL default '',
556 `comments` mediumtext,
557 KEY `frombranch` (`frombranch`),
558 KEY `tobranch` (`tobranch`),
559 KEY `itemnumber` (`itemnumber`),
560 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
561 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
562 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
563 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
567 -- Table structure for table `browser`
569 DROP TABLE IF EXISTS `browser`;
570 CREATE TABLE `browser` (
571 `level` int(11) NOT NULL,
572 `classification` varchar(20) NOT NULL,
573 `description` varchar(255) NOT NULL,
574 `number` bigint(20) NOT NULL,
575 `endnode` tinyint(4) NOT NULL
576 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
579 -- Table structure for table `categories`
582 DROP TABLE IF EXISTS `categories`;
583 CREATE TABLE `categories` (
584 `categorycode` varchar(10) NOT NULL default '',
585 `description` mediumtext,
586 `enrolmentperiod` smallint(6) default NULL,
587 `upperagelimit` smallint(6) default NULL,
588 `dateofbirthrequired` tinyint(1) default NULL,
589 `finetype` varchar(30) default NULL,
590 `bulk` tinyint(1) default NULL,
591 `enrolmentfee` decimal(28,6) default NULL,
592 `overduenoticerequired` tinyint(1) default NULL,
593 `issuelimit` smallint(6) default NULL,
594 `reservefee` decimal(28,6) default NULL,
595 `category_type` varchar(1) NOT NULL default 'A',
596 PRIMARY KEY (`categorycode`),
597 UNIQUE KEY `categorycode` (`categorycode`)
598 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
601 -- Table structure for table `cities`
604 DROP TABLE IF EXISTS `cities`;
605 CREATE TABLE `cities` (
606 `cityid` int(11) NOT NULL auto_increment,
607 `city_name` varchar(100) NOT NULL default '',
608 `city_zipcode` varchar(20) default NULL,
609 PRIMARY KEY (`cityid`)
610 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
613 -- Table structure for table `class_sort_rules`
616 DROP TABLE IF EXISTS `class_sort_rules`;
617 CREATE TABLE `class_sort_rules` (
618 `class_sort_rule` varchar(10) NOT NULL default '',
619 `description` mediumtext,
620 `sort_routine` varchar(30) NOT NULL default '',
621 PRIMARY KEY (`class_sort_rule`),
622 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
623 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
626 -- Table structure for table `class_sources`
629 DROP TABLE IF EXISTS `class_sources`;
630 CREATE TABLE `class_sources` (
631 `cn_source` varchar(10) NOT NULL default '',
632 `description` mediumtext,
633 `used` tinyint(4) NOT NULL default 0,
634 `class_sort_rule` varchar(10) NOT NULL default '',
635 PRIMARY KEY (`cn_source`),
636 UNIQUE KEY `cn_source_idx` (`cn_source`),
637 KEY `used_idx` (`used`),
638 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
639 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
642 -- Table structure for table `currency`
645 DROP TABLE IF EXISTS `currency`;
646 CREATE TABLE `currency` (
647 `currency` varchar(10) NOT NULL default '',
648 `symbol` varchar(5) default NULL,
649 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
650 `rate` float(7,5) default NULL,
651 PRIMARY KEY (`currency`)
652 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
655 -- Table structure for table `deletedbiblio`
658 DROP TABLE IF EXISTS `deletedbiblio`;
659 CREATE TABLE `deletedbiblio` (
660 `biblionumber` int(11) NOT NULL default 0,
661 `frameworkcode` varchar(4) NOT NULL default '',
664 `unititle` mediumtext,
666 `serial` tinyint(1) default NULL,
667 `seriestitle` mediumtext,
668 `copyrightdate` smallint(6) default NULL,
669 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
670 `datecreated` DATE NOT NULL,
671 `abstract` mediumtext,
672 PRIMARY KEY (`biblionumber`),
673 KEY `blbnoidx` (`biblionumber`)
674 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
677 -- Table structure for table `deletedbiblioitems`
680 DROP TABLE IF EXISTS `deletedbiblioitems`;
681 CREATE TABLE `deletedbiblioitems` (
682 `biblioitemnumber` int(11) NOT NULL default 0,
683 `biblionumber` int(11) NOT NULL default 0,
686 `itemtype` varchar(10) default NULL,
687 `isbn` varchar(14) default NULL,
688 `issn` varchar(9) default NULL,
689 `publicationyear` text,
690 `publishercode` varchar(255) default NULL,
691 `volumedate` date default NULL,
693 `collectiontitle` mediumtext default NULL,
694 `collectionissn` text default NULL,
695 `collectionvolume` mediumtext default NULL,
696 `editionstatement` text default NULL,
697 `editionresponsibility` text default NULL,
698 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
699 `illus` varchar(255) default NULL,
700 `pages` varchar(255) default NULL,
702 `size` varchar(255) default NULL,
703 `place` varchar(255) default NULL,
704 `lccn` varchar(25) default NULL,
706 `url` varchar(255) default NULL,
707 `cn_source` varchar(10) default NULL,
708 `cn_class` varchar(30) default NULL,
709 `cn_item` varchar(10) default NULL,
710 `cn_suffix` varchar(10) default NULL,
711 `cn_sort` varchar(30) default NULL,
712 `totalissues` int(10),
713 `marcxml` longtext NOT NULL,
714 PRIMARY KEY (`biblioitemnumber`),
715 KEY `bibinoidx` (`biblioitemnumber`),
716 KEY `bibnoidx` (`biblionumber`),
718 KEY `publishercode` (`publishercode`)
719 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
722 -- Table structure for table `deletedborrowers`
725 DROP TABLE IF EXISTS `deletedborrowers`;
726 CREATE TABLE `deletedborrowers` (
727 `borrowernumber` int(11) NOT NULL default 0,
728 `cardnumber` varchar(9) NOT NULL default '',
729 `surname` mediumtext NOT NULL,
732 `othernames` mediumtext,
734 `streetnumber` varchar(10) default NULL,
735 `streettype` varchar(50) default NULL,
736 `address` mediumtext NOT NULL,
738 `city` mediumtext NOT NULL,
739 `zipcode` varchar(25) default NULL,
742 `mobile` varchar(50) default NULL,
746 `B_streetnumber` varchar(10) default NULL,
747 `B_streettype` varchar(50) default NULL,
748 `B_address` varchar(100) default NULL,
750 `B_zipcode` varchar(25) default NULL,
752 `B_phone` mediumtext,
753 `dateofbirth` date default NULL,
754 `branchcode` varchar(10) NOT NULL default '',
755 `categorycode` varchar(2) default NULL,
756 `dateenrolled` date default NULL,
757 `dateexpiry` date default NULL,
758 `gonenoaddress` tinyint(1) default NULL,
759 `lost` tinyint(1) default NULL,
760 `debarred` tinyint(1) default NULL,
761 `contactname` mediumtext,
762 `contactfirstname` text,
764 `guarantorid` int(11) default NULL,
765 `borrowernotes` mediumtext,
766 `relationship` varchar(100) default NULL,
767 `ethnicity` varchar(50) default NULL,
768 `ethnotes` varchar(255) default NULL,
769 `sex` varchar(1) default NULL,
770 `password` varchar(30) default NULL,
771 `flags` int(11) default NULL,
772 `userid` varchar(30) default NULL,
773 `opacnote` mediumtext,
774 `contactnote` varchar(255) default NULL,
775 `sort1` varchar(80) default NULL,
776 `sort2` varchar(80) default NULL,
777 `altcontactfirstname` varchar(255) default NULL,
778 `altcontactsurname` varchar(255) default NULL,
779 `altcontactaddress1` varchar(255) default NULL,
780 `altcontactaddress2` varchar(255) default NULL,
781 `altcontactaddress3` varchar(255) default NULL,
782 `altcontactzipcode` varchar(50) default NULL,
783 `altcontactphone` varchar(50) default NULL,
784 KEY `borrowernumber` (`borrowernumber`),
785 KEY `cardnumber` (`cardnumber`)
786 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
789 -- Table structure for table `deleteditems`
792 DROP TABLE IF EXISTS `deleteditems`;
793 CREATE TABLE `deleteditems` (
794 `itemnumber` int(11) NOT NULL default 0,
795 `biblionumber` int(11) NOT NULL default 0,
796 `biblioitemnumber` int(11) NOT NULL default 0,
797 `barcode` varchar(20) default NULL,
798 `dateaccessioned` date default NULL,
799 `booksellerid` mediumtext default NULL,
800 `homebranch` varchar(10) default NULL,
801 `price` decimal(8,2) default NULL,
802 `replacementprice` decimal(8,2) default NULL,
803 `replacementpricedate` date default NULL,
804 `datelastborrowed` date default NULL,
805 `datelastseen` date default NULL,
806 `stack` tinyint(1) default NULL,
807 `notforloan` tinyint(1) NOT NULL default 0,
808 `damaged` tinyint(1) NOT NULL default 0,
809 `itemlost` tinyint(1) NOT NULL default 0,
810 `wthdrawn` tinyint(1) NOT NULL default 0,
811 `itemcallnumber` varchar(30) default NULL,
812 `issues` smallint(6) default NULL,
813 `renewals` smallint(6) default NULL,
814 `reserves` smallint(6) default NULL,
815 `restricted` tinyint(1) default NULL,
816 `itemnotes` mediumtext,
817 `holdingbranch` varchar(10) default NULL,
818 `paidfor` mediumtext,
819 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
820 `location` varchar(80) default NULL,
821 `onloan` date default NULL,
822 `cn_source` varchar(10) default NULL,
823 `cn_sort` varchar(30) default NULL,
824 `ccode` varchar(10) default NULL,
825 `materials` varchar(10) default NULL,
826 `uri` varchar(255) default NULL,
827 `itype` varchar(10) default NULL,
828 `more_subfields_xml` longtext default NULL,
829 `enumchron` varchar(80) default NULL,
830 `copynumber` smallint(6) default NULL,
832 PRIMARY KEY (`itemnumber`),
833 KEY `delitembarcodeidx` (`barcode`),
834 KEY `delitembinoidx` (`biblioitemnumber`),
835 KEY `delitembibnoidx` (`biblionumber`),
836 KEY `delhomebranch` (`homebranch`),
837 KEY `delholdingbranch` (`holdingbranch`)
838 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
841 -- Table structure for table `ethnicity`
844 DROP TABLE IF EXISTS `ethnicity`;
845 CREATE TABLE `ethnicity` (
846 `code` varchar(10) NOT NULL default '',
847 `name` varchar(255) default NULL,
849 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
852 -- Table structure for table `import_batches`
855 DROP TABLE IF EXISTS `import_batches`;
856 CREATE TABLE `import_batches` (
857 `import_batch_id` int(11) NOT NULL auto_increment,
858 `matcher_id` int(11) default NULL,
859 `template_id` int(11) default NULL,
860 `branchcode` varchar(10) default NULL,
861 `num_biblios` int(11) NOT NULL default 0,
862 `num_items` int(11) NOT NULL default 0,
863 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
864 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
865 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
866 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
867 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
868 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
869 `file_name` varchar(100),
870 `comments` mediumtext,
871 PRIMARY KEY (`import_batch_id`),
872 KEY `branchcode` (`branchcode`)
873 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
876 -- Table structure for table `import_records`
879 DROP TABLE IF EXISTS `import_records`;
880 CREATE TABLE `import_records` (
881 `import_record_id` int(11) NOT NULL auto_increment,
882 `import_batch_id` int(11) NOT NULL,
883 `branchcode` varchar(10) default NULL,
884 `record_sequence` int(11) NOT NULL default 0,
885 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
886 `import_date` DATE default NULL,
887 `marc` longblob NOT NULL,
888 `marcxml` longtext NOT NULL,
889 `marcxml_old` longtext NOT NULL,
890 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
891 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
892 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
893 `import_error` mediumtext,
894 `encoding` varchar(40) NOT NULL default '',
895 `z3950random` varchar(40) default NULL,
896 PRIMARY KEY (`import_record_id`),
897 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
898 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
899 KEY `branchcode` (`branchcode`),
900 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
901 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
904 -- Table structure for `import_record_matches`
906 DROP TABLE IF EXISTS `import_record_matches`;
907 CREATE TABLE `import_record_matches` (
908 `import_record_id` int(11) NOT NULL,
909 `candidate_match_id` int(11) NOT NULL,
910 `score` int(11) NOT NULL default 0,
911 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
912 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
913 KEY `record_score` (`import_record_id`, `score`)
914 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
917 -- Table structure for table `import_biblios`
920 DROP TABLE IF EXISTS `import_biblios`;
921 CREATE TABLE `import_biblios` (
922 `import_record_id` int(11) NOT NULL,
923 `matched_biblionumber` int(11) default NULL,
924 `control_number` varchar(25) default NULL,
925 `original_source` varchar(25) default NULL,
926 `title` varchar(128) default NULL,
927 `author` varchar(80) default NULL,
928 `isbn` varchar(14) default NULL,
929 `issn` varchar(9) default NULL,
930 `has_items` tinyint(1) NOT NULL default 0,
931 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
932 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
933 KEY `matched_biblionumber` (`matched_biblionumber`),
934 KEY `title` (`title`),
936 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
939 -- Table structure for table `import_items`
942 DROP TABLE IF EXISTS `import_items`;
943 CREATE TABLE `import_items` (
944 `import_items_id` int(11) NOT NULL auto_increment,
945 `import_record_id` int(11) NOT NULL,
946 `itemnumber` int(11) default NULL,
947 `branchcode` varchar(10) default NULL,
948 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
949 `marcxml` longtext NOT NULL,
950 `import_error` mediumtext,
951 PRIMARY KEY (`import_items_id`),
952 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
953 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
954 KEY `itemnumber` (`itemnumber`),
955 KEY `branchcode` (`branchcode`)
956 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
959 -- Table structure for table `issues`
962 DROP TABLE IF EXISTS `issues`;
963 CREATE TABLE `issues` (
964 `borrowernumber` int(11) default NULL,
965 `itemnumber` int(11) default NULL,
966 `date_due` date default NULL,
967 `branchcode` varchar(10) default NULL,
968 `issuingbranch` varchar(18) default NULL,
969 `returndate` date default NULL,
970 `lastreneweddate` date default NULL,
971 `return` varchar(4) default NULL,
972 `renewals` tinyint(4) default NULL,
973 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
974 `issuedate` date default NULL,
975 KEY `issuesborridx` (`borrowernumber`),
976 KEY `issuesitemidx` (`itemnumber`),
977 KEY `bordate` (`borrowernumber`,`timestamp`),
978 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
979 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
980 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
983 -- Table structure for table `issuingrules`
986 DROP TABLE IF EXISTS `issuingrules`;
987 CREATE TABLE `issuingrules` (
988 `categorycode` varchar(10) NOT NULL default '',
989 `itemtype` varchar(10) NOT NULL default '',
990 `restrictedtype` tinyint(1) default NULL,
991 `rentaldiscount` decimal(28,6) default NULL,
992 `reservecharge` decimal(28,6) default NULL,
993 `fine` decimal(28,6) default NULL,
994 `firstremind` int(11) default NULL,
995 `chargeperiod` int(11) default NULL,
996 `accountsent` int(11) default NULL,
997 `chargename` varchar(100) default NULL,
998 `maxissueqty` int(4) default NULL,
999 `issuelength` int(4) default NULL,
1000 `branchcode` varchar(10) NOT NULL default '',
1001 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1002 KEY `categorycode` (`categorycode`),
1003 KEY `itemtype` (`itemtype`)
1004 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1007 -- Table structure for table `items`
1010 DROP TABLE IF EXISTS `items`;
1011 CREATE TABLE `items` (
1012 `itemnumber` int(11) NOT NULL auto_increment,
1013 `biblionumber` int(11) NOT NULL default 0,
1014 `biblioitemnumber` int(11) NOT NULL default 0,
1015 `barcode` varchar(20) default NULL,
1016 `dateaccessioned` date default NULL,
1017 `booksellerid` mediumtext default NULL,
1018 `homebranch` varchar(10) default NULL,
1019 `price` decimal(8,2) default NULL,
1020 `replacementprice` decimal(8,2) default NULL,
1021 `replacementpricedate` date default NULL,
1022 `datelastborrowed` date default NULL,
1023 `datelastseen` date default NULL,
1024 `stack` tinyint(1) default NULL,
1025 `notforloan` tinyint(1) NOT NULL default 0,
1026 `damaged` tinyint(1) NOT NULL default 0,
1027 `itemlost` tinyint(1) NOT NULL default 0,
1028 `wthdrawn` tinyint(1) NOT NULL default 0,
1029 `itemcallnumber` varchar(30) default NULL,
1030 `issues` smallint(6) default NULL,
1031 `renewals` smallint(6) default NULL,
1032 `reserves` smallint(6) default NULL,
1033 `restricted` tinyint(1) default NULL,
1034 `itemnotes` mediumtext,
1035 `holdingbranch` varchar(10) default NULL,
1036 `paidfor` mediumtext,
1037 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1038 `location` varchar(80) default NULL,
1039 `onloan` date default NULL,
1040 `cn_source` varchar(10) default NULL,
1041 `cn_sort` varchar(30) default NULL,
1042 `ccode` varchar(10) default NULL,
1043 `materials` varchar(10) default NULL,
1044 `uri` varchar(255) default NULL,
1045 `itype` varchar(10) default NULL,
1046 `more_subfields_xml` longtext default NULL,
1047 `enumchron` varchar(80) default NULL,
1048 `copynumber` smallint(6) default NULL,
1049 PRIMARY KEY (`itemnumber`),
1050 UNIQUE KEY `itembarcodeidx` (`barcode`),
1051 KEY `itembinoidx` (`biblioitemnumber`),
1052 KEY `itembibnoidx` (`biblionumber`),
1053 KEY `homebranch` (`homebranch`),
1054 KEY `holdingbranch` (`holdingbranch`),
1055 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1056 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1057 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1058 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1061 -- Table structure for table `itemtypes`
1064 DROP TABLE IF EXISTS `itemtypes`;
1065 CREATE TABLE `itemtypes` (
1066 `itemtype` varchar(10) NOT NULL default '',
1067 `description` mediumtext,
1068 `renewalsallowed` smallint(6) default NULL,
1069 `rentalcharge` double(16,4) default NULL,
1070 `notforloan` smallint(6) default NULL,
1071 `imageurl` varchar(200) default NULL,
1073 PRIMARY KEY (`itemtype`),
1074 UNIQUE KEY `itemtype` (`itemtype`)
1075 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1078 -- Table structure for table `labels`
1081 DROP TABLE IF EXISTS `labels`;
1082 CREATE TABLE `labels` (
1083 `labelid` int(11) NOT NULL auto_increment,
1084 `batch_id` varchar(10) NOT NULL default 1,
1085 `itemnumber` varchar(100) NOT NULL default '',
1086 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1087 PRIMARY KEY (`labelid`)
1088 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1091 -- Table structure for table `labels_conf`
1094 DROP TABLE IF EXISTS `labels_conf`;
1095 CREATE TABLE `labels_conf` (
1096 `id` int(4) NOT NULL auto_increment,
1097 `barcodetype` char(100) default '',
1098 `title` int(1) default '0',
1099 `subtitle` int(1) default '0',
1100 `itemtype` int(1) default '0',
1101 `barcode` int(1) default '0',
1102 `dewey` int(1) default '0',
1103 `classification` int(1) default NULL,
1104 `subclass` int(1) default '0',
1105 `itemcallnumber` int(1) default '0',
1106 `author` int(1) default '0',
1107 `issn` int(1) default '0',
1108 `isbn` int(1) default '0',
1109 `startlabel` int(2) NOT NULL default '1',
1110 `printingtype` char(32) default 'BAR',
1111 `formatstring` varchar(64) default NULL,
1112 `layoutname` char(20) NOT NULL default 'TEST',
1113 `guidebox` int(1) default '0',
1114 `active` tinyint(1) default '1',
1115 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1116 `ccode` char(4) collate utf8_unicode_ci default NULL,
1117 `callnum_split` int(1) default NULL,
1118 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1120 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1123 -- Table structure for table `labels_profile`
1126 DROP TABLE IF EXISTS `labels_profile`;
1127 CREATE TABLE `labels_profile` (
1128 `tmpl_id` int(4) NOT NULL,
1129 `prof_id` int(4) NOT NULL,
1130 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1131 UNIQUE KEY `prof_id` (`prof_id`)
1132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1135 -- Table structure for table `labels_templates`
1138 DROP TABLE IF EXISTS `labels_templates`;
1139 CREATE TABLE `labels_templates` (
1140 `tmpl_id` int(4) NOT NULL auto_increment,
1141 `tmpl_code` char(100) default '',
1142 `tmpl_desc` char(100) default '',
1143 `page_width` float default '0',
1144 `page_height` float default '0',
1145 `label_width` float default '0',
1146 `label_height` float default '0',
1147 `topmargin` float default '0',
1148 `leftmargin` float default '0',
1149 `cols` int(2) default '0',
1150 `rows` int(2) default '0',
1151 `colgap` float default '0',
1152 `rowgap` float default '0',
1153 `active` int(1) default NULL,
1154 `units` char(20) default 'PX',
1155 `fontsize` int(4) NOT NULL default '3',
1156 `font` char(10) NOT NULL default 'TR',
1157 PRIMARY KEY (`tmpl_id`)
1158 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1161 -- Table structure for table `letter`
1164 DROP TABLE IF EXISTS `letter`;
1165 CREATE TABLE `letter` (
1166 `module` varchar(20) NOT NULL default '',
1167 `code` varchar(20) NOT NULL default '',
1168 `name` varchar(100) NOT NULL default '',
1169 `title` varchar(200) NOT NULL default '',
1171 PRIMARY KEY (`module`,`code`)
1172 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1175 -- Table structure for table `marc_subfield_structure`
1178 DROP TABLE IF EXISTS `marc_subfield_structure`;
1179 CREATE TABLE `marc_subfield_structure` (
1180 `tagfield` varchar(3) NOT NULL default '',
1181 `tagsubfield` varchar(1) NOT NULL default '',
1182 `liblibrarian` varchar(255) NOT NULL default '',
1183 `libopac` varchar(255) NOT NULL default '',
1184 `repeatable` tinyint(4) NOT NULL default 0,
1185 `mandatory` tinyint(4) NOT NULL default 0,
1186 `kohafield` varchar(40) default NULL,
1187 `tab` tinyint(1) default NULL,
1188 `authorised_value` varchar(20) default NULL,
1189 `authtypecode` varchar(20) default NULL,
1190 `value_builder` varchar(80) default NULL,
1191 `isurl` tinyint(1) default NULL,
1192 `hidden` tinyint(1) default NULL,
1193 `frameworkcode` varchar(4) NOT NULL default '',
1194 `seealso` varchar(1100) default NULL,
1195 `link` varchar(80) default NULL,
1196 `defaultvalue` text default NULL,
1197 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1198 KEY `kohafield_2` (`kohafield`),
1199 KEY `tab` (`frameworkcode`,`tab`),
1200 KEY `kohafield` (`frameworkcode`,`kohafield`)
1201 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1204 -- Table structure for table `marc_tag_structure`
1207 DROP TABLE IF EXISTS `marc_tag_structure`;
1208 CREATE TABLE `marc_tag_structure` (
1209 `tagfield` varchar(3) NOT NULL default '',
1210 `liblibrarian` varchar(255) NOT NULL default '',
1211 `libopac` varchar(255) NOT NULL default '',
1212 `repeatable` tinyint(4) NOT NULL default 0,
1213 `mandatory` tinyint(4) NOT NULL default 0,
1214 `authorised_value` varchar(10) default NULL,
1215 `frameworkcode` varchar(4) NOT NULL default '',
1216 PRIMARY KEY (`frameworkcode`,`tagfield`)
1217 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1220 -- Table structure for table `marc_matchers`
1223 DROP TABLE IF EXISTS `marc_matchers`;
1224 CREATE TABLE `marc_matchers` (
1225 `matcher_id` int(11) NOT NULL auto_increment,
1226 `code` varchar(10) NOT NULL default '',
1227 `description` varchar(255) NOT NULL default '',
1228 `record_type` varchar(10) NOT NULL default 'biblio',
1229 `threshold` int(11) NOT NULL default 0,
1230 PRIMARY KEY (`matcher_id`),
1231 KEY `code` (`code`),
1232 KEY `record_type` (`record_type`)
1233 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1236 -- Table structure for table `matchpoints`
1238 DROP TABLE IF EXISTS `matchpoints`;
1239 CREATE TABLE `matchpoints` (
1240 `matcher_id` int(11) NOT NULL,
1241 `matchpoint_id` int(11) NOT NULL auto_increment,
1242 `search_index` varchar(30) NOT NULL default '',
1243 `score` int(11) NOT NULL default 0,
1244 PRIMARY KEY (`matchpoint_id`),
1245 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1246 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1247 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1251 -- Table structure for table `matchpoint_components`
1253 DROP TABLE IF EXISTS `matchpoint_components`;
1254 CREATE TABLE `matchpoint_components` (
1255 `matchpoint_id` int(11) NOT NULL,
1256 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1257 sequence int(11) NOT NULL default 0,
1258 tag varchar(3) NOT NULL default '',
1259 subfields varchar(40) NOT NULL default '',
1260 offset int(4) NOT NULL default 0,
1261 length int(4) NOT NULL default 0,
1262 PRIMARY KEY (`matchpoint_component_id`),
1263 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1264 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1265 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1269 -- Table structure for table `matcher_component_norms`
1271 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1272 CREATE TABLE `matchpoint_component_norms` (
1273 `matchpoint_component_id` int(11) NOT NULL,
1274 `sequence` int(11) NOT NULL default 0,
1275 `norm_routine` varchar(50) NOT NULL default '',
1276 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1277 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1278 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1279 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1282 -- Table structure for table `matcher_matchpoints`
1284 DROP TABLE IF EXISTS `matcher_matchpoints`;
1285 CREATE TABLE `matcher_matchpoints` (
1286 `matcher_id` int(11) NOT NULL,
1287 `matchpoint_id` int(11) NOT NULL,
1288 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1289 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1290 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1291 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1292 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1295 -- Table structure for table `matchchecks`
1297 DROP TABLE IF EXISTS `matchchecks`;
1298 CREATE TABLE `matchchecks` (
1299 `matcher_id` int(11) NOT NULL,
1300 `matchcheck_id` int(11) NOT NULL auto_increment,
1301 `source_matchpoint_id` int(11) NOT NULL,
1302 `target_matchpoint_id` int(11) NOT NULL,
1303 PRIMARY KEY (`matchcheck_id`),
1304 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1305 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1306 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1307 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1308 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1309 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1310 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1313 -- Table structure for table `notifys`
1316 DROP TABLE IF EXISTS `notifys`;
1317 CREATE TABLE `notifys` (
1318 `notify_id` int(11) NOT NULL default 0,
1319 `borrowernumber` int(11) NOT NULL default 0,
1320 `itemnumber` int(11) NOT NULL default 0,
1321 `notify_date` date default NULL,
1322 `notify_send_date` date default NULL,
1323 `notify_level` int(1) NOT NULL default 0,
1324 `method` varchar(20) NOT NULL default ''
1325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1328 -- Table structure for table `nozebra`
1331 DROP TABLE IF EXISTS `nozebra`;
1332 CREATE TABLE `nozebra` (
1333 `server` varchar(20) NOT NULL,
1334 `indexname` varchar(40) NOT NULL,
1335 `value` varchar(250) NOT NULL,
1336 `biblionumbers` longtext NOT NULL,
1337 KEY `indexname` (`server`,`indexname`),
1338 KEY `value` (`server`,`value`))
1339 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1342 -- Table structure for table `old_issues`
1345 DROP TABLE IF EXISTS `old_issues`;
1346 CREATE TABLE `old_issues` (
1347 `borrowernumber` int(11) default NULL,
1348 `itemnumber` int(11) default NULL,
1349 `date_due` date default NULL,
1350 `branchcode` varchar(10) default NULL,
1351 `issuingbranch` varchar(18) default NULL,
1352 `returndate` date default NULL,
1353 `lastreneweddate` date default NULL,
1354 `return` varchar(4) default NULL,
1355 `renewals` tinyint(4) default NULL,
1356 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1357 `issuedate` date default NULL,
1358 KEY `old_issuesborridx` (`borrowernumber`),
1359 KEY `old_issuesitemidx` (`itemnumber`),
1360 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1361 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1362 ON DELETE SET NULL ON UPDATE SET NULL,
1363 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1364 ON DELETE SET NULL ON UPDATE SET NULL
1365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1368 -- Table structure for table `old_reserves`
1370 DROP TABLE IF EXISTS `old_reserves`;
1371 CREATE TABLE `old_reserves` (
1372 `borrowernumber` int(11) default NULL,
1373 `reservedate` date default NULL,
1374 `biblionumber` int(11) default NULL,
1375 `constrainttype` varchar(1) default NULL,
1376 `branchcode` varchar(10) default NULL,
1377 `notificationdate` date default NULL,
1378 `reminderdate` date default NULL,
1379 `cancellationdate` date default NULL,
1380 `reservenotes` mediumtext,
1381 `priority` smallint(6) default NULL,
1382 `found` varchar(1) default NULL,
1383 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1384 `itemnumber` int(11) default NULL,
1385 `waitingdate` date default NULL,
1386 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1387 KEY `old_reserves_biblionumber` (`biblionumber`),
1388 KEY `old_reserves_itemnumber` (`itemnumber`),
1389 KEY `old_reserves_branchcode` (`branchcode`),
1390 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1391 ON DELETE SET NULL ON UPDATE SET NULL,
1392 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1393 ON DELETE SET NULL ON UPDATE SET NULL,
1394 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1395 ON DELETE SET NULL ON UPDATE SET NULL
1396 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1399 -- Table structure for table `opac_news`
1402 DROP TABLE IF EXISTS `opac_news`;
1403 CREATE TABLE `opac_news` (
1404 `idnew` int(10) unsigned NOT NULL auto_increment,
1405 `title` varchar(250) NOT NULL default '',
1406 `new` text NOT NULL,
1407 `lang` varchar(25) NOT NULL default '',
1408 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1409 `expirationdate` date default NULL,
1410 `number` int(11) default NULL,
1411 PRIMARY KEY (`idnew`)
1412 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1415 -- Table structure for table `overduerules`
1418 DROP TABLE IF EXISTS `overduerules`;
1419 CREATE TABLE `overduerules` (
1420 `branchcode` varchar(10) NOT NULL default '',
1421 `categorycode` varchar(2) NOT NULL default '',
1422 `delay1` int(4) default 0,
1423 `letter1` varchar(20) default NULL,
1424 `debarred1` varchar(1) default 0,
1425 `delay2` int(4) default 0,
1426 `debarred2` varchar(1) default 0,
1427 `letter2` varchar(20) default NULL,
1428 `delay3` int(4) default 0,
1429 `letter3` varchar(20) default NULL,
1430 `debarred3` int(1) default 0,
1431 PRIMARY KEY (`branchcode`,`categorycode`)
1432 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1435 -- Table structure for table `patroncards`
1438 DROP TABLE IF EXISTS `patroncards`;
1439 CREATE TABLE `patroncards` (
1440 `cardid` int(11) NOT NULL auto_increment,
1441 `batch_id` varchar(10) NOT NULL default '1',
1442 `borrowernumber` int(11) NOT NULL,
1443 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1444 PRIMARY KEY (`cardid`),
1445 KEY `patroncards_ibfk_1` (`borrowernumber`),
1446 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1447 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1450 -- Table structure for table `patronimage`
1453 DROP TABLE IF EXISTS `patronimage`;
1454 CREATE TABLE `patronimage` (
1455 `cardnumber` varchar(16) NOT NULL,
1456 `mimetype` varchar(15) NOT NULL,
1457 `imagefile` mediumblob NOT NULL,
1458 PRIMARY KEY (`cardnumber`),
1459 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1460 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1463 -- Table structure for table `printers`
1466 DROP TABLE IF EXISTS `printers`;
1467 CREATE TABLE `printers` (
1468 `printername` varchar(40) NOT NULL default '',
1469 `printqueue` varchar(20) default NULL,
1470 `printtype` varchar(20) default NULL,
1471 PRIMARY KEY (`printername`)
1472 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1475 -- Table structure for table `printers_profile`
1478 DROP TABLE IF EXISTS `printers_profile`;
1479 CREATE TABLE `printers_profile` (
1480 `prof_id` int(4) NOT NULL auto_increment,
1481 `printername` varchar(40) NOT NULL,
1482 `tmpl_id` int(4) NOT NULL,
1483 `paper_bin` varchar(20) NOT NULL,
1484 `offset_horz` float default NULL,
1485 `offset_vert` float default NULL,
1486 `creep_horz` float default NULL,
1487 `creep_vert` float default NULL,
1488 `unit` char(20) NOT NULL default 'POINT',
1489 PRIMARY KEY (`prof_id`),
1490 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1491 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1492 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1495 -- Table structure for table `repeatable_holidays`
1498 DROP TABLE IF EXISTS `repeatable_holidays`;
1499 CREATE TABLE `repeatable_holidays` (
1500 `id` int(11) NOT NULL auto_increment,
1501 `branchcode` varchar(10) NOT NULL default '',
1502 `weekday` smallint(6) default NULL,
1503 `day` smallint(6) default NULL,
1504 `month` smallint(6) default NULL,
1505 `title` varchar(50) NOT NULL default '',
1506 `description` text NOT NULL,
1508 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1511 -- Table structure for table `reports_dictionary`
1514 DROP TABLE IF EXISTS `reports_dictionary`;
1515 CREATE TABLE reports_dictionary (
1516 `id` int(11) NOT NULL auto_increment,
1517 `name` varchar(255) default NULL,
1519 `date_created` datetime default NULL,
1520 `date_modified` datetime default NULL,
1522 `area` int(11) default NULL,
1524 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1527 -- Table structure for table `reserveconstraints`
1530 DROP TABLE IF EXISTS `reserveconstraints`;
1531 CREATE TABLE `reserveconstraints` (
1532 `borrowernumber` int(11) NOT NULL default 0,
1533 `reservedate` date default NULL,
1534 `biblionumber` int(11) NOT NULL default 0,
1535 `biblioitemnumber` int(11) default NULL,
1536 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1537 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1540 -- Table structure for table `reserves`
1543 DROP TABLE IF EXISTS `reserves`;
1544 CREATE TABLE `reserves` (
1545 `borrowernumber` int(11) NOT NULL default 0,
1546 `reservedate` date default NULL,
1547 `biblionumber` int(11) NOT NULL default 0,
1548 `constrainttype` varchar(1) default NULL,
1549 `branchcode` varchar(10) default NULL,
1550 `notificationdate` date default NULL,
1551 `reminderdate` date default NULL,
1552 `cancellationdate` date default NULL,
1553 `reservenotes` mediumtext,
1554 `priority` smallint(6) default NULL,
1555 `found` varchar(1) default NULL,
1556 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1557 `itemnumber` int(11) default NULL,
1558 `waitingdate` date default NULL,
1559 KEY `borrowernumber` (`borrowernumber`),
1560 KEY `biblionumber` (`biblionumber`),
1561 KEY `itemnumber` (`itemnumber`),
1562 KEY `branchcode` (`branchcode`),
1563 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1564 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1565 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1566 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1567 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1570 -- Table structure for table `reviews`
1573 DROP TABLE IF EXISTS `reviews`;
1574 CREATE TABLE `reviews` (
1575 `reviewid` int(11) NOT NULL auto_increment,
1576 `borrowernumber` int(11) default NULL,
1577 `biblionumber` int(11) default NULL,
1579 `approved` tinyint(4) default NULL,
1580 `datereviewed` datetime default NULL,
1581 PRIMARY KEY (`reviewid`)
1582 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1585 -- Table structure for table `roadtype`
1588 DROP TABLE IF EXISTS `roadtype`;
1589 CREATE TABLE `roadtype` (
1590 `roadtypeid` int(11) NOT NULL auto_increment,
1591 `road_type` varchar(100) NOT NULL default '',
1592 PRIMARY KEY (`roadtypeid`)
1593 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1596 -- Table structure for table `saved_sql`
1599 DROP TABLE IF EXISTS `saved_sql`;
1600 CREATE TABLE saved_sql (
1601 `id` int(11) NOT NULL auto_increment,
1602 `borrowernumber` int(11) default NULL,
1603 `date_created` datetime default NULL,
1604 `last_modified` datetime default NULL,
1606 `last_run` datetime default NULL,
1607 `report_name` varchar(255) default NULL,
1608 `type` varchar(255) default NULL,
1611 KEY boridx (`borrowernumber`)
1612 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1616 -- Table structure for `saved_reports`
1619 DROP TABLE IF EXISTS `saved_reports`;
1620 CREATE TABLE saved_reports (
1621 `id` int(11) NOT NULL auto_increment,
1622 `report_id` int(11) default NULL,
1624 `date_run` datetime default NULL,
1626 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1630 -- Table structure for table `serial`
1633 DROP TABLE IF EXISTS `serial`;
1634 CREATE TABLE `serial` (
1635 `serialid` int(11) NOT NULL auto_increment,
1636 `biblionumber` varchar(100) NOT NULL default '',
1637 `subscriptionid` varchar(100) NOT NULL default '',
1638 `serialseq` varchar(100) NOT NULL default '',
1639 `status` tinyint(4) NOT NULL default 0,
1640 `planneddate` date default NULL,
1642 `publisheddate` date default NULL,
1643 `itemnumber` text default NULL,
1644 `claimdate` date default NULL,
1645 `routingnotes` text,
1646 PRIMARY KEY (`serialid`)
1647 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1650 -- Table structure for table `sessions`
1653 DROP TABLE IF EXISTS sessions;
1654 CREATE TABLE sessions (
1655 `id` varchar(32) NOT NULL,
1656 `a_session` text NOT NULL,
1658 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1661 -- Table structure for table `special_holidays`
1664 DROP TABLE IF EXISTS `special_holidays`;
1665 CREATE TABLE `special_holidays` (
1666 `id` int(11) NOT NULL auto_increment,
1667 `branchcode` varchar(10) NOT NULL default '',
1668 `day` smallint(6) NOT NULL default 0,
1669 `month` smallint(6) NOT NULL default 0,
1670 `year` smallint(6) NOT NULL default 0,
1671 `isexception` smallint(1) NOT NULL default 1,
1672 `title` varchar(50) NOT NULL default '',
1673 `description` text NOT NULL,
1675 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1678 -- Table structure for table `statistics`
1681 DROP TABLE IF EXISTS `statistics`;
1682 CREATE TABLE `statistics` (
1683 `datetime` datetime default NULL,
1684 `branch` varchar(10) default NULL,
1685 `proccode` varchar(4) default NULL,
1686 `value` double(16,4) default NULL,
1687 `type` varchar(16) default NULL,
1689 `usercode` varchar(10) default NULL,
1690 `itemnumber` int(11) default NULL,
1691 `itemtype` varchar(10) default NULL,
1692 `borrowernumber` int(11) default NULL,
1693 `associatedborrower` int(11) default NULL,
1694 KEY `timeidx` (`datetime`)
1695 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1698 -- Table structure for table `stopwords`
1701 DROP TABLE IF EXISTS `stopwords`;
1702 CREATE TABLE `stopwords` (
1703 `word` varchar(255) default NULL
1704 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1707 -- Table structure for table `subscription`
1710 DROP TABLE IF EXISTS `subscription`;
1711 CREATE TABLE `subscription` (
1712 `biblionumber` int(11) NOT NULL default 0,
1713 `subscriptionid` int(11) NOT NULL auto_increment,
1714 `librarian` varchar(100) default '',
1715 `startdate` date default NULL,
1716 `aqbooksellerid` int(11) default 0,
1717 `cost` int(11) default 0,
1718 `aqbudgetid` int(11) default 0,
1719 `weeklength` int(11) default 0,
1720 `monthlength` int(11) default 0,
1721 `numberlength` int(11) default 0,
1722 `periodicity` tinyint(4) default 0,
1723 `dow` varchar(100) default '',
1724 `numberingmethod` varchar(100) default '',
1726 `status` varchar(100) NOT NULL default '',
1727 `add1` int(11) default 0,
1728 `every1` int(11) default 0,
1729 `whenmorethan1` int(11) default 0,
1730 `setto1` int(11) default NULL,
1731 `lastvalue1` int(11) default NULL,
1732 `add2` int(11) default 0,
1733 `every2` int(11) default 0,
1734 `whenmorethan2` int(11) default 0,
1735 `setto2` int(11) default NULL,
1736 `lastvalue2` int(11) default NULL,
1737 `add3` int(11) default 0,
1738 `every3` int(11) default 0,
1739 `innerloop1` int(11) default 0,
1740 `innerloop2` int(11) default 0,
1741 `innerloop3` int(11) default 0,
1742 `whenmorethan3` int(11) default 0,
1743 `setto3` int(11) default NULL,
1744 `lastvalue3` int(11) default NULL,
1745 `issuesatonce` tinyint(3) NOT NULL default 1,
1746 `firstacquidate` date default NULL,
1747 `manualhistory` tinyint(1) NOT NULL default 0,
1748 `irregularity` text,
1749 `letter` varchar(20) default NULL,
1750 `numberpattern` tinyint(3) default 0,
1751 `distributedto` text,
1752 `internalnotes` longtext,
1754 `branchcode` varchar(10) NOT NULL default '',
1755 `hemisphere` tinyint(3) default 0,
1756 `lastbranch` varchar(10),
1757 `serialsadditems` tinyint(1) NOT NULL default '0',
1758 PRIMARY KEY (`subscriptionid`)
1759 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1762 -- Table structure for table `subscriptionhistory`
1765 DROP TABLE IF EXISTS `subscriptionhistory`;
1766 CREATE TABLE `subscriptionhistory` (
1767 `biblionumber` int(11) NOT NULL default 0,
1768 `subscriptionid` int(11) NOT NULL default 0,
1769 `histstartdate` date default NULL,
1770 `enddate` date default NULL,
1771 `missinglist` longtext NOT NULL,
1772 `recievedlist` longtext NOT NULL,
1773 `opacnote` varchar(150) NOT NULL default '',
1774 `librariannote` varchar(150) NOT NULL default '',
1775 PRIMARY KEY (`subscriptionid`),
1776 KEY `biblionumber` (`biblionumber`)
1777 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1780 -- Table structure for table `subscriptionroutinglist`
1783 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1784 CREATE TABLE `subscriptionroutinglist` (
1785 `routingid` int(11) NOT NULL auto_increment,
1786 `borrowernumber` int(11) default NULL,
1787 `ranking` int(11) default NULL,
1788 `subscriptionid` int(11) default NULL,
1789 PRIMARY KEY (`routingid`)
1790 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1793 -- Table structure for table `suggestions`
1796 DROP TABLE IF EXISTS `suggestions`;
1797 CREATE TABLE `suggestions` (
1798 `suggestionid` int(8) NOT NULL auto_increment,
1799 `suggestedby` int(11) NOT NULL default 0,
1800 `managedby` int(11) default NULL,
1801 `STATUS` varchar(10) NOT NULL default '',
1803 `author` varchar(80) default NULL,
1804 `title` varchar(80) default NULL,
1805 `copyrightdate` smallint(6) default NULL,
1806 `publishercode` varchar(255) default NULL,
1807 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1808 `volumedesc` varchar(255) default NULL,
1809 `publicationyear` smallint(6) default 0,
1810 `place` varchar(255) default NULL,
1811 `isbn` varchar(10) default NULL,
1812 `mailoverseeing` smallint(1) default 0,
1813 `biblionumber` int(11) default NULL,
1815 PRIMARY KEY (`suggestionid`),
1816 KEY `suggestedby` (`suggestedby`),
1817 KEY `managedby` (`managedby`)
1818 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1821 -- Table structure for table `systempreferences`
1824 DROP TABLE IF EXISTS `systempreferences`;
1825 CREATE TABLE `systempreferences` (
1826 `variable` varchar(50) NOT NULL default '',
1828 `options` mediumtext,
1830 `type` varchar(20) default NULL,
1831 PRIMARY KEY (`variable`)
1832 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1835 -- Table structure for table `tags`
1838 DROP TABLE IF EXISTS `tags`;
1839 CREATE TABLE `tags` (
1840 `entry` varchar(255) NOT NULL default '',
1841 `weight` bigint(20) NOT NULL default 0,
1842 PRIMARY KEY (`entry`)
1843 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1846 -- Table structure for table `tags_all`
1849 CREATE TABLE `tags_all` (
1850 `tag_id` int(11) NOT NULL auto_increment,
1851 `borrowernumber` int(11) NOT NULL,
1852 `biblionumber` int(11) NOT NULL,
1853 `term` varchar(255) NOT NULL,
1854 `language` int(4) default NULL,
1855 `date_created` datetime NOT NULL,
1856 PRIMARY KEY (`tag_id`),
1857 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1858 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1859 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1860 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1861 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1862 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1863 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1866 -- Table structure for table `tags_approval`
1869 CREATE TABLE `tags_approval` (
1870 `term` varchar(255) NOT NULL,
1871 `approved` int(1) NOT NULL default '0',
1872 `date_approved` datetime default NULL,
1873 `approved_by` int(11) default NULL,
1874 `weight_total` int(9) NOT NULL default '1',
1875 PRIMARY KEY (`term`),
1876 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1877 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1878 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1879 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1882 -- Table structure for table `tags_index`
1885 CREATE TABLE `tags_index` (
1886 `term` varchar(255) NOT NULL,
1887 `biblionumber` int(11) NOT NULL,
1888 `weight` int(9) NOT NULL default '1',
1889 PRIMARY KEY (`term`,`biblionumber`),
1890 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1891 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1892 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1893 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1894 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1895 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1898 -- Table structure for table `userflags`
1901 DROP TABLE IF EXISTS `userflags`;
1902 CREATE TABLE `userflags` (
1903 `bit` int(11) NOT NULL default 0,
1904 `flag` varchar(30) default NULL,
1905 `flagdesc` varchar(255) default NULL,
1906 `defaulton` int(11) default NULL,
1908 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1911 -- Table structure for table `virtualshelves`
1914 DROP TABLE IF EXISTS `virtualshelves`;
1915 CREATE TABLE `virtualshelves` (
1916 `shelfnumber` int(11) NOT NULL auto_increment,
1917 `shelfname` varchar(255) default NULL,
1918 `owner` varchar(80) default NULL,
1919 `category` varchar(1) default NULL,
1920 `sortfield` varchar(16) default NULL,
1921 PRIMARY KEY (`shelfnumber`)
1922 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1925 -- Table structure for table `virtualshelfcontents`
1928 DROP TABLE IF EXISTS `virtualshelfcontents`;
1929 CREATE TABLE `virtualshelfcontents` (
1930 `shelfnumber` int(11) NOT NULL default 0,
1931 `biblionumber` int(11) NOT NULL default 0,
1932 `flags` int(11) default NULL,
1933 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1934 KEY `shelfnumber` (`shelfnumber`),
1935 KEY `biblionumber` (`biblionumber`),
1936 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1937 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1938 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1941 -- Table structure for table `z3950servers`
1944 DROP TABLE IF EXISTS `z3950servers`;
1945 CREATE TABLE `z3950servers` (
1946 `host` varchar(255) default NULL,
1947 `port` int(11) default NULL,
1948 `db` varchar(255) default NULL,
1949 `userid` varchar(255) default NULL,
1950 `password` varchar(255) default NULL,
1952 `id` int(11) NOT NULL auto_increment,
1953 `checked` smallint(6) default NULL,
1954 `rank` int(11) default NULL,
1955 `syntax` varchar(80) default NULL,
1957 `position` enum('primary','secondary','') NOT NULL default 'primary',
1958 `type` enum('zed','opensearch') NOT NULL default 'zed',
1959 `encoding` text default NULL,
1960 `description` text NOT NULL,
1962 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1965 -- Table structure for table `zebraqueue`
1968 DROP TABLE IF EXISTS `zebraqueue`;
1969 CREATE TABLE `zebraqueue` (
1970 `id` int(11) NOT NULL auto_increment,
1971 `biblio_auth_number` int(11) NOT NULL default '0',
1972 `operation` char(20) NOT NULL default '',
1973 `server` char(20) NOT NULL default '',
1974 `done` int(11) NOT NULL default '0',
1975 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1977 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1978 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1980 DROP TABLE IF EXISTS `services_throttle`;
1981 CREATE TABLE `services_throttle` (
1982 `service_type` varchar(10) NOT NULL default '',
1983 `service_count` varchar(45) default NULL,
1984 PRIMARY KEY (`service_type`)
1985 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1987 -- http://www.w3.org/International/articles/language-tags/
1990 DROP TABLE IF EXISTS language_subtag_registry;
1991 CREATE TABLE language_subtag_registry (
1993 type varchar(25), -- language-script-region-variant-extension-privateuse
1994 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1996 KEY `subtag` (`subtag`)
1997 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1999 -- TODO: add suppress_scripts
2000 -- this maps three letter codes defined in iso639.2 back to their
2001 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2002 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2003 CREATE TABLE language_rfc4646_to_iso639 (
2004 rfc4646_subtag varchar(25),
2005 iso639_2_code varchar(25),
2006 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2007 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2009 DROP TABLE IF EXISTS language_descriptions;
2010 CREATE TABLE language_descriptions (
2014 description varchar(255),
2016 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2018 -- bi-directional support, keyed by script subcode
2019 DROP TABLE IF EXISTS language_script_bidi;
2020 CREATE TABLE language_script_bidi (
2021 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2022 bidi varchar(3), -- rtl ltr
2023 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2024 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2026 -- TODO: need to map language subtags to script subtags for detection
2027 -- of bidi when script is not specified (like ar, he)
2028 DROP TABLE IF EXISTS language_script_mapping;
2029 CREATE TABLE language_script_mapping (
2030 language_subtag varchar(25),
2031 script_subtag varchar(25),
2032 KEY `language_subtag` (`language_subtag`)
2033 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2035 DROP TABLE IF EXISTS `permissions`;
2036 CREATE TABLE `permissions` (
2037 `module_bit` int(11) NOT NULL DEFAULT 0,
2038 `code` varchar(30) DEFAULT NULL,
2039 `description` varchar(255) DEFAULT NULL,
2040 PRIMARY KEY (`module_bit`, `code`),
2041 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2042 ON DELETE CASCADE ON UPDATE CASCADE
2043 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2045 DROP TABLE IF EXISTS serialitems;
2046 CREATE TABLE serialitems (
2047 serialid int(11) NOT NULL,
2048 itemnumber int(11) NOT NULL,
2049 UNIQUE KEY `serialididx` (`serialid`)
2050 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2052 DROP TABLE IF EXISTS `user_permissions`;
2053 CREATE TABLE `user_permissions` (
2054 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2055 `module_bit` int(11) NOT NULL DEFAULT 0,
2056 `code` varchar(30) DEFAULT NULL,
2057 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2058 ON DELETE CASCADE ON UPDATE CASCADE,
2059 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2060 ON DELETE CASCADE ON UPDATE CASCADE
2061 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2063 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2064 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2065 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2066 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2067 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2068 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2069 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2070 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;