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,
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,
342 KEY `name` (`category`)
343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
346 -- Table structure for table `biblio`
349 DROP TABLE IF EXISTS `biblio`;
350 CREATE TABLE `biblio` (
351 `biblionumber` int(11) NOT NULL auto_increment,
352 `frameworkcode` varchar(4) NOT NULL default '',
355 `unititle` mediumtext,
357 `serial` tinyint(1) default NULL,
358 `seriestitle` mediumtext,
359 `copyrightdate` smallint(6) default NULL,
360 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
361 `datecreated` DATE NOT NULL,
362 `abstract` mediumtext,
363 PRIMARY KEY (`biblionumber`),
364 KEY `blbnoidx` (`biblionumber`)
365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
368 -- Table structure for table `biblio_framework`
371 DROP TABLE IF EXISTS `biblio_framework`;
372 CREATE TABLE `biblio_framework` (
373 `frameworkcode` varchar(4) NOT NULL default '',
374 `frameworktext` varchar(255) NOT NULL default '',
375 PRIMARY KEY (`frameworkcode`)
376 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
379 -- Table structure for table `biblioitems`
382 DROP TABLE IF EXISTS `biblioitems`;
383 CREATE TABLE `biblioitems` (
384 `biblioitemnumber` int(11) NOT NULL auto_increment,
385 `biblionumber` int(11) NOT NULL default 0,
388 `itemtype` varchar(10) default NULL,
389 `isbn` varchar(14) default NULL,
390 `issn` varchar(9) default NULL,
391 `publicationyear` text,
392 `publishercode` varchar(255) default NULL,
393 `volumedate` date default NULL,
395 `collectiontitle` mediumtext default NULL,
396 `collectionissn` text default NULL,
397 `collectionvolume` mediumtext default NULL,
398 `editionstatement` text default NULL,
399 `editionresponsibility` text default NULL,
400 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
401 `illus` varchar(255) default NULL,
402 `pages` varchar(255) default NULL,
404 `size` varchar(255) default NULL,
405 `place` varchar(255) default NULL,
406 `lccn` varchar(25) default NULL,
408 `url` varchar(255) default NULL,
409 `cn_source` varchar(10) default NULL,
410 `cn_class` varchar(30) default NULL,
411 `cn_item` varchar(10) default NULL,
412 `cn_suffix` varchar(10) default NULL,
413 `cn_sort` varchar(30) default NULL,
414 `totalissues` int(10),
415 `marcxml` longtext NOT NULL,
416 PRIMARY KEY (`biblioitemnumber`),
417 KEY `bibinoidx` (`biblioitemnumber`),
418 KEY `bibnoidx` (`biblionumber`),
420 KEY `publishercode` (`publishercode`),
421 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
422 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
425 -- Table structure for table `borrowers`
428 DROP TABLE IF EXISTS `borrowers`;
429 CREATE TABLE `borrowers` (
430 `borrowernumber` int(11) NOT NULL auto_increment,
431 `cardnumber` varchar(16) default NULL,
432 `surname` mediumtext NOT NULL,
435 `othernames` mediumtext,
437 `streetnumber` varchar(10) default NULL,
438 `streettype` varchar(50) default NULL,
439 `address` mediumtext NOT NULL,
441 `city` mediumtext NOT NULL,
442 `zipcode` varchar(25) default NULL,
445 `mobile` varchar(50) default NULL,
449 `B_streetnumber` varchar(10) default NULL,
450 `B_streettype` varchar(50) default NULL,
451 `B_address` varchar(100) default NULL,
453 `B_zipcode` varchar(25) default NULL,
455 `B_phone` mediumtext,
456 `dateofbirth` date default NULL,
457 `branchcode` varchar(10) NOT NULL default '',
458 `categorycode` varchar(10) NOT NULL default '',
459 `dateenrolled` date default NULL,
460 `dateexpiry` date default NULL,
461 `gonenoaddress` tinyint(1) default NULL,
462 `lost` tinyint(1) default NULL,
463 `debarred` tinyint(1) default NULL,
464 `contactname` mediumtext,
465 `contactfirstname` text,
467 `guarantorid` int(11) default NULL,
468 `borrowernotes` mediumtext,
469 `relationship` varchar(100) default NULL,
470 `ethnicity` varchar(50) default NULL,
471 `ethnotes` varchar(255) default NULL,
472 `sex` varchar(1) default NULL,
473 `password` varchar(30) default NULL,
474 `flags` int(11) default NULL,
475 `userid` varchar(30) default NULL,
476 `opacnote` mediumtext,
477 `contactnote` varchar(255) default NULL,
478 `sort1` varchar(80) default NULL,
479 `sort2` varchar(80) default NULL,
480 `altcontactfirstname` varchar(255) default NULL,
481 `altcontactsurname` varchar(255) default NULL,
482 `altcontactaddress1` varchar(255) default NULL,
483 `altcontactaddress2` varchar(255) default NULL,
484 `altcontactaddress3` varchar(255) default NULL,
485 `altcontactzipcode` varchar(50) default NULL,
486 `altcontactphone` varchar(50) default NULL,
487 UNIQUE KEY `cardnumber` (`cardnumber`),
488 PRIMARY KEY `borrowernumber` (`borrowernumber`),
489 KEY `categorycode` (`categorycode`),
490 KEY `branchcode` (`branchcode`),
491 KEY `userid` (`userid`),
492 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
493 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
497 -- Table structure for table `branchcategories`
500 DROP TABLE IF EXISTS `branchcategories`;
501 CREATE TABLE `branchcategories` (
502 `categorycode` varchar(10) NOT NULL default '',
503 `categoryname` varchar(32),
504 `codedescription` mediumtext,
505 `categorytype` varchar(16),
506 PRIMARY KEY (`categorycode`)
507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
510 -- Table structure for table `branches`
513 DROP TABLE IF EXISTS `branches`;
514 CREATE TABLE `branches` (
515 `branchcode` varchar(10) NOT NULL default '',
516 `branchname` mediumtext NOT NULL,
517 `branchaddress1` mediumtext,
518 `branchaddress2` mediumtext,
519 `branchaddress3` mediumtext,
520 `branchphone` mediumtext,
521 `branchfax` mediumtext,
522 `branchemail` mediumtext,
523 `issuing` tinyint(4) default NULL,
524 `branchip` varchar(15) default NULL,
525 `branchprinter` varchar(100) default NULL,
526 UNIQUE KEY `branchcode` (`branchcode`)
527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
530 -- Table structure for table `branchrelations`
533 DROP TABLE IF EXISTS `branchrelations`;
534 CREATE TABLE `branchrelations` (
535 `branchcode` varchar(10) NOT NULL default '',
536 `categorycode` varchar(10) NOT NULL default '',
537 PRIMARY KEY (`branchcode`,`categorycode`),
538 KEY `branchcode` (`branchcode`),
539 KEY `categorycode` (`categorycode`),
540 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
541 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
542 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
545 -- Table structure for table `branchtransfers`
548 DROP TABLE IF EXISTS `branchtransfers`;
549 CREATE TABLE `branchtransfers` (
550 `itemnumber` int(11) NOT NULL default 0,
551 `datesent` datetime default NULL,
552 `frombranch` varchar(10) NOT NULL default '',
553 `datearrived` datetime default NULL,
554 `tobranch` varchar(10) NOT NULL default '',
555 `comments` mediumtext,
556 KEY `frombranch` (`frombranch`),
557 KEY `tobranch` (`tobranch`),
558 KEY `itemnumber` (`itemnumber`),
559 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
560 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
561 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
566 -- Table structure for table `browser`
568 DROP TABLE IF EXISTS `browser`;
569 CREATE TABLE `browser` (
570 `level` int(11) NOT NULL,
571 `classification` varchar(20) NOT NULL,
572 `description` varchar(255) NOT NULL,
573 `number` bigint(20) NOT NULL,
574 `endnode` tinyint(4) NOT NULL
575 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
578 -- Table structure for table `categories`
581 DROP TABLE IF EXISTS `categories`;
582 CREATE TABLE `categories` (
583 `categorycode` varchar(10) NOT NULL default '',
584 `description` mediumtext,
585 `enrolmentperiod` smallint(6) default NULL,
586 `upperagelimit` smallint(6) default NULL,
587 `dateofbirthrequired` tinyint(1) default NULL,
588 `finetype` varchar(30) default NULL,
589 `bulk` tinyint(1) default NULL,
590 `enrolmentfee` decimal(28,6) default NULL,
591 `overduenoticerequired` tinyint(1) default NULL,
592 `issuelimit` smallint(6) default NULL,
593 `reservefee` decimal(28,6) default NULL,
594 `category_type` varchar(1) NOT NULL default 'A',
595 PRIMARY KEY (`categorycode`),
596 UNIQUE KEY `categorycode` (`categorycode`)
597 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
600 -- Table structure for table `categorytable`
603 DROP TABLE IF EXISTS `categorytable`;
604 CREATE TABLE `categorytable` (
605 `categorycode` varchar(5) NOT NULL default '',
607 `itemtypecodes` text,
608 PRIMARY KEY (`categorycode`)
609 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
612 -- Table structure for table `cities`
615 DROP TABLE IF EXISTS `cities`;
616 CREATE TABLE `cities` (
617 `cityid` int(11) NOT NULL auto_increment,
618 `city_name` varchar(100) NOT NULL default '',
619 `city_zipcode` varchar(20) default NULL,
620 PRIMARY KEY (`cityid`)
621 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
624 -- Table structure for table `class_sort_rules`
627 DROP TABLE IF EXISTS `class_sort_rules`;
628 CREATE TABLE `class_sort_rules` (
629 `class_sort_rule` varchar(10) NOT NULL default '',
630 `description` mediumtext,
631 `sort_routine` varchar(30) NOT NULL default '',
632 PRIMARY KEY (`class_sort_rule`),
633 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
634 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
637 -- Table structure for table `class_sources`
640 DROP TABLE IF EXISTS `class_sources`;
641 CREATE TABLE `class_sources` (
642 `cn_source` varchar(10) NOT NULL default '',
643 `description` mediumtext,
644 `used` tinyint(4) NOT NULL default 0,
645 `class_sort_rule` varchar(10) NOT NULL default '',
646 PRIMARY KEY (`cn_source`),
647 UNIQUE KEY `cn_source_idx` (`cn_source`),
648 KEY `used_idx` (`used`),
649 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
650 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
653 -- Table structure for table `currency`
656 DROP TABLE IF EXISTS `currency`;
657 CREATE TABLE `currency` (
658 `currency` varchar(10) NOT NULL default '',
659 `symbol` varchar(5) default NULL,
660 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
661 `rate` float(7,5) default NULL,
662 PRIMARY KEY (`currency`)
663 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
666 -- Table structure for table `deletedbiblio`
669 DROP TABLE IF EXISTS `deletedbiblio`;
670 CREATE TABLE `deletedbiblio` (
671 `biblionumber` int(11) NOT NULL default 0,
672 `frameworkcode` varchar(4) NOT NULL default '',
675 `unititle` mediumtext,
677 `serial` tinyint(1) default NULL,
678 `seriestitle` mediumtext,
679 `copyrightdate` smallint(6) default NULL,
680 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
681 `datecreated` DATE NOT NULL,
682 `abstract` mediumtext,
683 PRIMARY KEY (`biblionumber`),
684 KEY `blbnoidx` (`biblionumber`)
685 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
688 -- Table structure for table `deletedbiblioitems`
691 DROP TABLE IF EXISTS `deletedbiblioitems`;
692 CREATE TABLE `deletedbiblioitems` (
693 `biblioitemnumber` int(11) NOT NULL default 0,
694 `biblionumber` int(11) NOT NULL default 0,
697 `itemtype` varchar(10) default NULL,
698 `isbn` varchar(14) default NULL,
699 `issn` varchar(9) default NULL,
700 `publicationyear` text,
701 `publishercode` varchar(255) default NULL,
702 `volumedate` date default NULL,
704 `collectiontitle` mediumtext default NULL,
705 `collectionissn` text default NULL,
706 `collectionvolume` mediumtext default NULL,
707 `editionstatement` text default NULL,
708 `editionresponsibility` text default NULL,
709 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
710 `illus` varchar(255) default NULL,
711 `pages` varchar(255) default NULL,
713 `size` varchar(255) default NULL,
714 `place` varchar(255) default NULL,
715 `lccn` varchar(25) default NULL,
717 `url` varchar(255) default NULL,
718 `cn_source` varchar(10) default NULL,
719 `cn_class` varchar(30) default NULL,
720 `cn_item` varchar(10) default NULL,
721 `cn_suffix` varchar(10) default NULL,
722 `cn_sort` varchar(30) default NULL,
723 `totalissues` int(10),
724 `marcxml` longtext NOT NULL,
725 PRIMARY KEY (`biblioitemnumber`),
726 KEY `bibinoidx` (`biblioitemnumber`),
727 KEY `bibnoidx` (`biblionumber`),
729 KEY `publishercode` (`publishercode`)
730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
733 -- Table structure for table `deletedborrowers`
736 DROP TABLE IF EXISTS `deletedborrowers`;
737 CREATE TABLE `deletedborrowers` (
738 `borrowernumber` int(11) NOT NULL default 0,
739 `cardnumber` varchar(9) NOT NULL default '',
740 `surname` mediumtext NOT NULL,
743 `othernames` mediumtext,
745 `streetnumber` varchar(10) default NULL,
746 `streettype` varchar(50) default NULL,
747 `address` mediumtext NOT NULL,
749 `city` mediumtext NOT NULL,
750 `zipcode` varchar(25) default NULL,
753 `mobile` varchar(50) default NULL,
757 `B_streetnumber` varchar(10) default NULL,
758 `B_streettype` varchar(50) default NULL,
759 `B_address` varchar(100) default NULL,
761 `B_zipcode` varchar(25) default NULL,
763 `B_phone` mediumtext,
764 `dateofbirth` date default NULL,
765 `branchcode` varchar(10) NOT NULL default '',
766 `categorycode` varchar(2) default NULL,
767 `dateenrolled` date default NULL,
768 `dateexpiry` date default NULL,
769 `gonenoaddress` tinyint(1) default NULL,
770 `lost` tinyint(1) default NULL,
771 `debarred` tinyint(1) default NULL,
772 `contactname` mediumtext,
773 `contactfirstname` text,
775 `guarantorid` int(11) default NULL,
776 `borrowernotes` mediumtext,
777 `relationship` varchar(100) default NULL,
778 `ethnicity` varchar(50) default NULL,
779 `ethnotes` varchar(255) default NULL,
780 `sex` varchar(1) default NULL,
781 `password` varchar(30) default NULL,
782 `flags` int(11) default NULL,
783 `userid` varchar(30) default NULL,
784 `opacnote` mediumtext,
785 `contactnote` varchar(255) default NULL,
786 `sort1` varchar(80) default NULL,
787 `sort2` varchar(80) default NULL,
788 `altcontactfirstname` varchar(255) default NULL,
789 `altcontactsurname` varchar(255) default NULL,
790 `altcontactaddress1` varchar(255) default NULL,
791 `altcontactaddress2` varchar(255) default NULL,
792 `altcontactaddress3` varchar(255) default NULL,
793 `altcontactzipcode` varchar(50) default NULL,
794 `altcontactphone` varchar(50) default NULL,
795 KEY `borrowernumber` (`borrowernumber`),
796 KEY `cardnumber` (`cardnumber`)
797 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
800 -- Table structure for table `deleteditems`
803 DROP TABLE IF EXISTS `deleteditems`;
804 CREATE TABLE `deleteditems` (
805 `itemnumber` int(11) NOT NULL default 0,
806 `biblionumber` int(11) NOT NULL default 0,
807 `biblioitemnumber` int(11) NOT NULL default 0,
808 `barcode` varchar(20) default NULL,
809 `dateaccessioned` date default NULL,
810 `booksellerid` mediumtext default NULL,
811 `homebranch` varchar(10) default NULL,
812 `price` decimal(8,2) default NULL,
813 `replacementprice` decimal(8,2) default NULL,
814 `replacementpricedate` date default NULL,
815 `datelastborrowed` date default NULL,
816 `datelastseen` date default NULL,
817 `stack` tinyint(1) default NULL,
818 `notforloan` tinyint(1) NOT NULL default 0,
819 `damaged` tinyint(1) NOT NULL default 0,
820 `itemlost` tinyint(1) NOT NULL default 0,
821 `wthdrawn` tinyint(1) NOT NULL default 0,
822 `itemcallnumber` varchar(30) default NULL,
823 `issues` smallint(6) default NULL,
824 `renewals` smallint(6) default NULL,
825 `reserves` smallint(6) default NULL,
826 `restricted` tinyint(1) default NULL,
827 `itemnotes` mediumtext,
828 `holdingbranch` varchar(10) default NULL,
829 `paidfor` mediumtext,
830 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
831 `location` varchar(80) default NULL,
832 `onloan` date default NULL,
833 `cn_source` varchar(10) default NULL,
834 `cn_sort` varchar(30) default NULL,
835 `ccode` varchar(10) default NULL,
836 `materials` varchar(10) default NULL,
837 `uri` varchar(255) default NULL,
838 `itype` varchar(10) default NULL,
839 `more_subfields_xml` longtext default NULL,
840 `enumchron` varchar(80) default NULL,
841 `copynumber` smallint(6) default NULL,
843 PRIMARY KEY (`itemnumber`),
844 KEY `delitembarcodeidx` (`barcode`),
845 KEY `delitembinoidx` (`biblioitemnumber`),
846 KEY `delitembibnoidx` (`biblionumber`),
847 KEY `delhomebranch` (`homebranch`),
848 KEY `delholdingbranch` (`holdingbranch`)
849 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
852 -- Table structure for table `ethnicity`
855 DROP TABLE IF EXISTS `ethnicity`;
856 CREATE TABLE `ethnicity` (
857 `code` varchar(10) NOT NULL default '',
858 `name` varchar(255) default NULL,
860 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
863 -- Table structure for table `import_batches`
866 DROP TABLE IF EXISTS `import_batches`;
867 CREATE TABLE `import_batches` (
868 `import_batch_id` int(11) NOT NULL auto_increment,
869 `matcher_id` int(11) default NULL,
870 `template_id` int(11) default NULL,
871 `branchcode` varchar(10) default NULL,
872 `num_biblios` int(11) NOT NULL default 0,
873 `num_items` int(11) NOT NULL default 0,
874 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
875 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
876 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
877 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
878 `file_name` varchar(100),
879 `comments` mediumtext,
880 PRIMARY KEY (`import_batch_id`),
881 KEY `branchcode` (`branchcode`)
882 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
885 -- Table structure for table `import_records`
888 DROP TABLE IF EXISTS `import_records`;
889 CREATE TABLE `import_records` (
890 `import_record_id` int(11) NOT NULL auto_increment,
891 `import_batch_id` int(11) NOT NULL,
892 `branchcode` varchar(10) default NULL,
893 `record_sequence` int(11) NOT NULL default 0,
894 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
895 `import_date` DATE default NULL,
896 `marc` longblob NOT NULL,
897 `marcxml` longtext NOT NULL,
898 `marcxml_old` longtext NOT NULL,
899 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
900 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
901 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
902 `import_error` mediumtext,
903 `encoding` varchar(40) NOT NULL default '',
904 `z3950random` varchar(40) default NULL,
905 PRIMARY KEY (`import_record_id`),
906 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
907 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
908 KEY `branchcode` (`branchcode`),
909 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
910 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
913 -- Table structure for `import_record_matches`
915 DROP TABLE IF EXISTS `import_record_matches`;
916 CREATE TABLE `import_record_matches` (
917 `import_record_id` int(11) NOT NULL,
918 `candidate_match_id` int(11) NOT NULL,
919 `score` int(11) NOT NULL default 0,
920 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
921 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
922 KEY `record_score` (`import_record_id`, `score`)
923 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
926 -- Table structure for table `import_biblios`
929 DROP TABLE IF EXISTS `import_biblios`;
930 CREATE TABLE `import_biblios` (
931 `import_record_id` int(11) NOT NULL,
932 `matched_biblionumber` int(11) default NULL,
933 `control_number` varchar(25) default NULL,
934 `original_source` varchar(25) default NULL,
935 `title` varchar(128) default NULL,
936 `author` varchar(80) default NULL,
937 `isbn` varchar(14) default NULL,
938 `issn` varchar(9) default NULL,
939 `has_items` tinyint(1) NOT NULL default 0,
940 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
941 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
942 KEY `matched_biblionumber` (`matched_biblionumber`),
943 KEY `title` (`title`),
945 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
948 -- Table structure for table `import_items`
951 DROP TABLE IF EXISTS `import_items`;
952 CREATE TABLE `import_items` (
953 `import_items_id` int(11) NOT NULL auto_increment,
954 `import_record_id` int(11) NOT NULL,
955 `itemnumber` int(11) default NULL,
956 `branchcode` varchar(10) default NULL,
957 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
958 `marcxml` longtext NOT NULL,
959 `import_error` mediumtext,
960 PRIMARY KEY (`import_items_id`),
961 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
962 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
963 KEY `itemnumber` (`itemnumber`),
964 KEY `branchcode` (`branchcode`)
965 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
968 -- Table structure for table `issues`
971 DROP TABLE IF EXISTS `issues`;
972 CREATE TABLE `issues` (
973 `borrowernumber` int(11) default NULL,
974 `itemnumber` int(11) default NULL,
975 `date_due` date default NULL,
976 `branchcode` varchar(10) default NULL,
977 `issuingbranch` varchar(18) default NULL,
978 `returndate` date default NULL,
979 `lastreneweddate` date default NULL,
980 `return` varchar(4) default NULL,
981 `renewals` tinyint(4) default NULL,
982 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
983 `issuedate` date default NULL,
984 KEY `issuesborridx` (`borrowernumber`),
985 KEY `issuesitemidx` (`itemnumber`),
986 KEY `bordate` (`borrowernumber`,`timestamp`),
987 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
988 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
989 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
992 -- Table structure for table `issuingrules`
995 DROP TABLE IF EXISTS `issuingrules`;
996 CREATE TABLE `issuingrules` (
997 `categorycode` varchar(10) NOT NULL default '',
998 `itemtype` varchar(10) NOT NULL default '',
999 `restrictedtype` tinyint(1) default NULL,
1000 `rentaldiscount` decimal(28,6) default NULL,
1001 `reservecharge` decimal(28,6) default NULL,
1002 `fine` decimal(28,6) default NULL,
1003 `firstremind` int(11) default NULL,
1004 `chargeperiod` int(11) default NULL,
1005 `accountsent` int(11) default NULL,
1006 `chargename` varchar(100) default NULL,
1007 `maxissueqty` int(4) default NULL,
1008 `issuelength` int(4) default NULL,
1009 `branchcode` varchar(10) NOT NULL default '',
1010 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1011 KEY `categorycode` (`categorycode`),
1012 KEY `itemtype` (`itemtype`)
1013 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1016 -- Table structure for table `items`
1019 DROP TABLE IF EXISTS `items`;
1020 CREATE TABLE `items` (
1021 `itemnumber` int(11) NOT NULL auto_increment,
1022 `biblionumber` int(11) NOT NULL default 0,
1023 `biblioitemnumber` int(11) NOT NULL default 0,
1024 `barcode` varchar(20) default NULL,
1025 `dateaccessioned` date default NULL,
1026 `booksellerid` mediumtext default NULL,
1027 `homebranch` varchar(10) default NULL,
1028 `price` decimal(8,2) default NULL,
1029 `replacementprice` decimal(8,2) default NULL,
1030 `replacementpricedate` date default NULL,
1031 `datelastborrowed` date default NULL,
1032 `datelastseen` date default NULL,
1033 `stack` tinyint(1) default NULL,
1034 `notforloan` tinyint(1) NOT NULL default 0,
1035 `damaged` tinyint(1) NOT NULL default 0,
1036 `itemlost` tinyint(1) NOT NULL default 0,
1037 `wthdrawn` tinyint(1) NOT NULL default 0,
1038 `itemcallnumber` varchar(30) default NULL,
1039 `issues` smallint(6) default NULL,
1040 `renewals` smallint(6) default NULL,
1041 `reserves` smallint(6) default NULL,
1042 `restricted` tinyint(1) default NULL,
1043 `itemnotes` mediumtext,
1044 `holdingbranch` varchar(10) default NULL,
1045 `paidfor` mediumtext,
1046 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1047 `location` varchar(80) default NULL,
1048 `onloan` date default NULL,
1049 `cn_source` varchar(10) default NULL,
1050 `cn_sort` varchar(30) default NULL,
1051 `ccode` varchar(10) default NULL,
1052 `materials` varchar(10) default NULL,
1053 `uri` varchar(255) default NULL,
1054 `itype` varchar(10) default NULL,
1055 `more_subfields_xml` longtext default NULL,
1056 `enumchron` varchar(80) default NULL,
1057 `copynumber` smallint(6) default NULL,
1058 PRIMARY KEY (`itemnumber`),
1059 UNIQUE KEY `itembarcodeidx` (`barcode`),
1060 KEY `itembinoidx` (`biblioitemnumber`),
1061 KEY `itembibnoidx` (`biblionumber`),
1062 KEY `homebranch` (`homebranch`),
1063 KEY `holdingbranch` (`holdingbranch`),
1064 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1065 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1066 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1067 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1070 -- Table structure for table `itemtypes`
1073 DROP TABLE IF EXISTS `itemtypes`;
1074 CREATE TABLE `itemtypes` (
1075 `itemtype` varchar(10) NOT NULL default '',
1076 `description` mediumtext,
1077 `renewalsallowed` smallint(6) default NULL,
1078 `rentalcharge` double(16,4) default NULL,
1079 `notforloan` smallint(6) default NULL,
1080 `imageurl` varchar(200) default NULL,
1082 PRIMARY KEY (`itemtype`),
1083 UNIQUE KEY `itemtype` (`itemtype`)
1084 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1087 -- Table structure for table `labels`
1090 DROP TABLE IF EXISTS `labels`;
1091 CREATE TABLE `labels` (
1092 `labelid` int(11) NOT NULL auto_increment,
1093 `batch_id` varchar(10) NOT NULL default 1,
1094 `itemnumber` varchar(100) NOT NULL default '',
1095 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1096 PRIMARY KEY (`labelid`)
1097 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1100 -- Table structure for table `labels_conf`
1103 DROP TABLE IF EXISTS `labels_conf`;
1104 CREATE TABLE `labels_conf` (
1105 `id` int(4) NOT NULL auto_increment,
1106 `barcodetype` char(100) default '',
1107 `title` int(1) default '0',
1108 `subtitle` int(1) default '0',
1109 `itemtype` int(1) default '0',
1110 `barcode` int(1) default '0',
1111 `dewey` int(1) default '0',
1112 `classification` int(1) default NULL,
1113 `subclass` int(1) default '0',
1114 `itemcallnumber` int(1) default '0',
1115 `author` int(1) default '0',
1116 `issn` int(1) default '0',
1117 `isbn` int(1) default '0',
1118 `startlabel` int(2) NOT NULL default '1',
1119 `printingtype` char(32) default 'BAR',
1120 `layoutname` char(20) NOT NULL default 'TEST',
1121 `guidebox` int(1) default '0',
1122 `active` tinyint(1) default '1',
1123 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1124 `ccode` char(4) collate utf8_unicode_ci default NULL,
1125 `callnum_split` int(1) default NULL,
1126 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1128 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1131 -- Table structure for table `labels_profile`
1134 DROP TABLE IF EXISTS `labels_profile`;
1135 CREATE TABLE `labels_profile` (
1136 `tmpl_id` int(4) NOT NULL,
1137 `prof_id` int(4) NOT NULL,
1138 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1139 UNIQUE KEY `prof_id` (`prof_id`)
1140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1143 -- Table structure for table `labels_templates`
1146 DROP TABLE IF EXISTS `labels_templates`;
1147 CREATE TABLE `labels_templates` (
1148 `tmpl_id` int(4) NOT NULL auto_increment,
1149 `tmpl_code` char(100) default '',
1150 `tmpl_desc` char(100) default '',
1151 `page_width` float default '0',
1152 `page_height` float default '0',
1153 `label_width` float default '0',
1154 `label_height` float default '0',
1155 `topmargin` float default '0',
1156 `leftmargin` float default '0',
1157 `cols` int(2) default '0',
1158 `rows` int(2) default '0',
1159 `colgap` float default '0',
1160 `rowgap` float default '0',
1161 `active` int(1) default NULL,
1162 `units` char(20) default 'PX',
1163 `fontsize` int(4) NOT NULL default '3',
1164 `font` char(10) NOT NULL default 'TR',
1165 PRIMARY KEY (`tmpl_id`)
1166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1169 -- Table structure for table `letter`
1172 DROP TABLE IF EXISTS `letter`;
1173 CREATE TABLE `letter` (
1174 `module` varchar(20) NOT NULL default '',
1175 `code` varchar(20) NOT NULL default '',
1176 `name` varchar(100) NOT NULL default '',
1177 `title` varchar(200) NOT NULL default '',
1179 PRIMARY KEY (`module`,`code`)
1180 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1183 -- Table structure for table `marc_subfield_structure`
1186 DROP TABLE IF EXISTS `marc_subfield_structure`;
1187 CREATE TABLE `marc_subfield_structure` (
1188 `tagfield` varchar(3) NOT NULL default '',
1189 `tagsubfield` varchar(1) NOT NULL default '',
1190 `liblibrarian` varchar(255) NOT NULL default '',
1191 `libopac` varchar(255) NOT NULL default '',
1192 `repeatable` tinyint(4) NOT NULL default 0,
1193 `mandatory` tinyint(4) NOT NULL default 0,
1194 `kohafield` varchar(40) default NULL,
1195 `tab` tinyint(1) default NULL,
1196 `authorised_value` varchar(20) default NULL,
1197 `authtypecode` varchar(20) default NULL,
1198 `value_builder` varchar(80) default NULL,
1199 `isurl` tinyint(1) default NULL,
1200 `hidden` tinyint(1) default NULL,
1201 `frameworkcode` varchar(4) NOT NULL default '',
1202 `seealso` varchar(1100) default NULL,
1203 `link` varchar(80) default NULL,
1204 `defaultvalue` text default NULL,
1205 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1206 KEY `kohafield_2` (`kohafield`),
1207 KEY `tab` (`frameworkcode`,`tab`),
1208 KEY `kohafield` (`frameworkcode`,`kohafield`)
1209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1212 -- Table structure for table `marc_tag_structure`
1215 DROP TABLE IF EXISTS `marc_tag_structure`;
1216 CREATE TABLE `marc_tag_structure` (
1217 `tagfield` varchar(3) 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 `authorised_value` varchar(10) default NULL,
1223 `frameworkcode` varchar(4) NOT NULL default '',
1224 PRIMARY KEY (`frameworkcode`,`tagfield`)
1225 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1228 -- Table structure for table `marc_matchers`
1231 DROP TABLE IF EXISTS `marc_matchers`;
1232 CREATE TABLE `marc_matchers` (
1233 `matcher_id` int(11) NOT NULL auto_increment,
1234 `code` varchar(10) NOT NULL default '',
1235 `description` varchar(255) NOT NULL default '',
1236 `record_type` varchar(10) NOT NULL default 'biblio',
1237 `threshold` int(11) NOT NULL default 0,
1238 PRIMARY KEY (`matcher_id`),
1239 KEY `code` (`code`),
1240 KEY `record_type` (`record_type`)
1241 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1244 -- Table structure for table `matchpoints`
1246 DROP TABLE IF EXISTS `matchpoints`;
1247 CREATE TABLE `matchpoints` (
1248 `matcher_id` int(11) NOT NULL,
1249 `matchpoint_id` int(11) NOT NULL auto_increment,
1250 `search_index` varchar(30) NOT NULL default '',
1251 `score` int(11) NOT NULL default 0,
1252 PRIMARY KEY (`matchpoint_id`),
1253 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1254 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1255 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1259 -- Table structure for table `matchpoint_components`
1261 DROP TABLE IF EXISTS `matchpoint_components`;
1262 CREATE TABLE `matchpoint_components` (
1263 `matchpoint_id` int(11) NOT NULL,
1264 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1265 sequence int(11) NOT NULL default 0,
1266 tag varchar(3) NOT NULL default '',
1267 subfields varchar(40) NOT NULL default '',
1268 offset int(4) NOT NULL default 0,
1269 length int(4) NOT NULL default 0,
1270 PRIMARY KEY (`matchpoint_component_id`),
1271 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1272 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1273 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1277 -- Table structure for table `matcher_component_norms`
1279 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1280 CREATE TABLE `matchpoint_component_norms` (
1281 `matchpoint_component_id` int(11) NOT NULL,
1282 `sequence` int(11) NOT NULL default 0,
1283 `norm_routine` varchar(50) NOT NULL default '',
1284 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1285 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1286 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1287 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1290 -- Table structure for table `matcher_matchpoints`
1292 DROP TABLE IF EXISTS `matcher_matchpoints`;
1293 CREATE TABLE `matcher_matchpoints` (
1294 `matcher_id` int(11) NOT NULL,
1295 `matchpoint_id` int(11) NOT NULL,
1296 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1297 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1298 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1299 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1303 -- Table structure for table `matchchecks`
1305 DROP TABLE IF EXISTS `matchchecks`;
1306 CREATE TABLE `matchchecks` (
1307 `matcher_id` int(11) NOT NULL,
1308 `matchcheck_id` int(11) NOT NULL auto_increment,
1309 `source_matchpoint_id` int(11) NOT NULL,
1310 `target_matchpoint_id` int(11) NOT NULL,
1311 PRIMARY KEY (`matchcheck_id`),
1312 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1313 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1314 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1315 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1316 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1317 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1318 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1321 -- Table structure for table `mediatypetable`
1324 DROP TABLE IF EXISTS `mediatypetable`;
1325 CREATE TABLE `mediatypetable` (
1326 `mediatypecode` varchar(5) NOT NULL default '',
1328 `itemtypecodes` text,
1329 PRIMARY KEY (`mediatypecode`)
1330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1333 -- Table structure for table `notifys`
1336 DROP TABLE IF EXISTS `notifys`;
1337 CREATE TABLE `notifys` (
1338 `notify_id` int(11) NOT NULL default 0,
1339 `borrowernumber` int(11) NOT NULL default 0,
1340 `itemnumber` int(11) NOT NULL default 0,
1341 `notify_date` date default NULL,
1342 `notify_send_date` date default NULL,
1343 `notify_level` int(1) NOT NULL default 0,
1344 `method` varchar(20) NOT NULL default ''
1345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1348 -- Table structure for table `nozebra`
1351 DROP TABLE IF EXISTS `nozebra`;
1352 CREATE TABLE `nozebra` (
1353 `server` varchar(20) NOT NULL,
1354 `indexname` varchar(40) NOT NULL,
1355 `value` varchar(250) NOT NULL,
1356 `biblionumbers` longtext NOT NULL,
1357 KEY `indexname` (`server`,`indexname`),
1358 KEY `value` (`server`,`value`))
1359 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1362 -- Table structure for table `old_issues`
1365 DROP TABLE IF EXISTS `old_issues`;
1366 CREATE TABLE `old_issues` (
1367 `borrowernumber` int(11) default NULL,
1368 `itemnumber` int(11) default NULL,
1369 `date_due` date default NULL,
1370 `branchcode` varchar(10) default NULL,
1371 `issuingbranch` varchar(18) default NULL,
1372 `returndate` date default NULL,
1373 `lastreneweddate` date default NULL,
1374 `return` varchar(4) default NULL,
1375 `renewals` tinyint(4) default NULL,
1376 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1377 `issuedate` date default NULL,
1378 KEY `old_issuesborridx` (`borrowernumber`),
1379 KEY `old_issuesitemidx` (`itemnumber`),
1380 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1381 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1382 ON DELETE SET NULL ON UPDATE SET NULL,
1383 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1384 ON DELETE SET NULL ON UPDATE SET NULL
1385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1388 -- Table structure for table `old_reserves`
1390 DROP TABLE IF EXISTS `old_reserves`;
1391 CREATE TABLE `old_reserves` (
1392 `borrowernumber` int(11) default NULL,
1393 `reservedate` date default NULL,
1394 `biblionumber` int(11) default NULL,
1395 `constrainttype` varchar(1) default NULL,
1396 `branchcode` varchar(10) default NULL,
1397 `notificationdate` date default NULL,
1398 `reminderdate` date default NULL,
1399 `cancellationdate` date default NULL,
1400 `reservenotes` mediumtext,
1401 `priority` smallint(6) default NULL,
1402 `found` varchar(1) default NULL,
1403 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1404 `itemnumber` int(11) default NULL,
1405 `waitingdate` date default NULL,
1406 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1407 KEY `old_reserves_biblionumber` (`biblionumber`),
1408 KEY `old_reserves_itemnumber` (`itemnumber`),
1409 KEY `old_reserves_branchcode` (`branchcode`),
1410 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1411 ON DELETE SET NULL ON UPDATE SET NULL,
1412 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1413 ON DELETE SET NULL ON UPDATE SET NULL,
1414 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1415 ON DELETE SET NULL ON UPDATE SET NULL
1416 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1419 -- Table structure for table `opac_news`
1422 DROP TABLE IF EXISTS `opac_news`;
1423 CREATE TABLE `opac_news` (
1424 `idnew` int(10) unsigned NOT NULL auto_increment,
1425 `title` varchar(250) NOT NULL default '',
1426 `new` text NOT NULL,
1427 `lang` varchar(25) NOT NULL default '',
1428 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1429 `expirationdate` date default NULL,
1430 `number` int(11) default NULL,
1431 PRIMARY KEY (`idnew`)
1432 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1435 -- Table structure for table `overduerules`
1438 DROP TABLE IF EXISTS `overduerules`;
1439 CREATE TABLE `overduerules` (
1440 `branchcode` varchar(10) NOT NULL default '',
1441 `categorycode` varchar(2) NOT NULL default '',
1442 `delay1` int(4) default 0,
1443 `letter1` varchar(20) default NULL,
1444 `debarred1` varchar(1) default 0,
1445 `delay2` int(4) default 0,
1446 `debarred2` varchar(1) default 0,
1447 `letter2` varchar(20) default NULL,
1448 `delay3` int(4) default 0,
1449 `letter3` varchar(20) default NULL,
1450 `debarred3` int(1) default 0,
1451 PRIMARY KEY (`branchcode`,`categorycode`)
1452 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1455 -- Table structure for table `patroncards`
1458 DROP TABLE IF EXISTS `patroncards`;
1459 CREATE TABLE `patroncards` (
1460 `cardid` int(11) NOT NULL auto_increment,
1461 `batch_id` varchar(10) NOT NULL default '1',
1462 `borrowernumber` int(11) NOT NULL,
1463 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1464 PRIMARY KEY (`cardid`),
1465 KEY `patroncards_ibfk_1` (`borrowernumber`),
1466 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1467 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1470 -- Table structure for table `patronimage`
1473 DROP TABLE IF EXISTS `patronimage`;
1474 CREATE TABLE `patronimage` (
1475 `cardnumber` varchar(16) NOT NULL,
1476 `mimetype` varchar(15) NOT NULL,
1477 `imagefile` mediumblob NOT NULL,
1478 PRIMARY KEY (`cardnumber`),
1479 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1480 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1483 -- Table structure for table `printers`
1486 DROP TABLE IF EXISTS `printers`;
1487 CREATE TABLE `printers` (
1488 `printername` varchar(40) NOT NULL default '',
1489 `printqueue` varchar(20) default NULL,
1490 `printtype` varchar(20) default NULL,
1491 PRIMARY KEY (`printername`)
1492 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1495 -- Table structure for table `printers_profile`
1498 DROP TABLE IF EXISTS `printers_profile`;
1499 CREATE TABLE `printers_profile` (
1500 `prof_id` int(4) NOT NULL auto_increment,
1501 `printername` varchar(40) NOT NULL,
1502 `tmpl_id` int(4) NOT NULL,
1503 `paper_bin` varchar(20) NOT NULL,
1504 `offset_horz` float default NULL,
1505 `offset_vert` float default NULL,
1506 `creep_horz` float default NULL,
1507 `creep_vert` float default NULL,
1508 `unit` char(20) NOT NULL default 'POINT',
1509 PRIMARY KEY (`prof_id`),
1510 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1511 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1512 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1515 -- Table structure for table `repeatable_holidays`
1518 DROP TABLE IF EXISTS `repeatable_holidays`;
1519 CREATE TABLE `repeatable_holidays` (
1520 `id` int(11) NOT NULL auto_increment,
1521 `branchcode` varchar(10) NOT NULL default '',
1522 `weekday` smallint(6) default NULL,
1523 `day` smallint(6) default NULL,
1524 `month` smallint(6) default NULL,
1525 `title` varchar(50) NOT NULL default '',
1526 `description` text NOT NULL,
1528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1531 -- Table structure for table `reports_dictionary`
1534 DROP TABLE IF EXISTS `reports_dictionary`;
1535 CREATE TABLE reports_dictionary (
1536 `id` int(11) NOT NULL auto_increment,
1537 `name` varchar(255) default NULL,
1539 `date_created` datetime default NULL,
1540 `date_modified` datetime default NULL,
1542 `area` int(11) default NULL,
1544 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1547 -- Table structure for table `reserveconstraints`
1550 DROP TABLE IF EXISTS `reserveconstraints`;
1551 CREATE TABLE `reserveconstraints` (
1552 `borrowernumber` int(11) NOT NULL default 0,
1553 `reservedate` date default NULL,
1554 `biblionumber` int(11) NOT NULL default 0,
1555 `biblioitemnumber` int(11) default NULL,
1556 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1557 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1560 -- Table structure for table `reserves`
1563 DROP TABLE IF EXISTS `reserves`;
1564 CREATE TABLE `reserves` (
1565 `borrowernumber` int(11) NOT NULL default 0,
1566 `reservedate` date default NULL,
1567 `biblionumber` int(11) NOT NULL default 0,
1568 `constrainttype` varchar(1) default NULL,
1569 `branchcode` varchar(10) default NULL,
1570 `notificationdate` date default NULL,
1571 `reminderdate` date default NULL,
1572 `cancellationdate` date default NULL,
1573 `reservenotes` mediumtext,
1574 `priority` smallint(6) default NULL,
1575 `found` varchar(1) default NULL,
1576 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1577 `itemnumber` int(11) default NULL,
1578 `waitingdate` date default NULL,
1579 KEY `borrowernumber` (`borrowernumber`),
1580 KEY `biblionumber` (`biblionumber`),
1581 KEY `itemnumber` (`itemnumber`),
1582 KEY `branchcode` (`branchcode`),
1583 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1584 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1585 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1586 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1587 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1590 -- Table structure for table `reviews`
1593 DROP TABLE IF EXISTS `reviews`;
1594 CREATE TABLE `reviews` (
1595 `reviewid` int(11) NOT NULL auto_increment,
1596 `borrowernumber` int(11) default NULL,
1597 `biblionumber` int(11) default NULL,
1599 `approved` tinyint(4) default NULL,
1600 `datereviewed` datetime default NULL,
1601 PRIMARY KEY (`reviewid`)
1602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1605 -- Table structure for table `roadtype`
1608 DROP TABLE IF EXISTS `roadtype`;
1609 CREATE TABLE `roadtype` (
1610 `roadtypeid` int(11) NOT NULL auto_increment,
1611 `road_type` varchar(100) NOT NULL default '',
1612 PRIMARY KEY (`roadtypeid`)
1613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1616 -- Table structure for table `saved_sql`
1619 DROP TABLE IF EXISTS `saved_sql`;
1620 CREATE TABLE saved_sql (
1621 `id` int(11) NOT NULL auto_increment,
1622 `borrowernumber` int(11) default NULL,
1623 `date_created` datetime default NULL,
1624 `last_modified` datetime default NULL,
1626 `last_run` datetime default NULL,
1627 `report_name` varchar(255) default NULL,
1628 `type` varchar(255) default NULL,
1631 KEY boridx (`borrowernumber`)
1632 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1636 -- Table structure for `saved_reports`
1639 DROP TABLE IF EXISTS `saved_reports`;
1640 CREATE TABLE saved_reports (
1641 `id` int(11) NOT NULL auto_increment,
1642 `report_id` int(11) default NULL,
1644 `date_run` datetime default NULL,
1646 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1650 -- Table structure for table `serial`
1653 DROP TABLE IF EXISTS `serial`;
1654 CREATE TABLE `serial` (
1655 `serialid` int(11) NOT NULL auto_increment,
1656 `biblionumber` varchar(100) NOT NULL default '',
1657 `subscriptionid` varchar(100) NOT NULL default '',
1658 `serialseq` varchar(100) NOT NULL default '',
1659 `status` tinyint(4) NOT NULL default 0,
1660 `planneddate` date default NULL,
1662 `publisheddate` date default NULL,
1663 `itemnumber` text default NULL,
1664 `claimdate` date default NULL,
1665 `routingnotes` text,
1666 PRIMARY KEY (`serialid`)
1667 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1670 -- Table structure for table `sessions`
1673 DROP TABLE IF EXISTS sessions;
1674 CREATE TABLE sessions (
1675 `id` varchar(32) NOT NULL,
1676 `a_session` text NOT NULL,
1678 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1681 -- Table structure for table `special_holidays`
1684 DROP TABLE IF EXISTS `special_holidays`;
1685 CREATE TABLE `special_holidays` (
1686 `id` int(11) NOT NULL auto_increment,
1687 `branchcode` varchar(10) NOT NULL default '',
1688 `day` smallint(6) NOT NULL default 0,
1689 `month` smallint(6) NOT NULL default 0,
1690 `year` smallint(6) NOT NULL default 0,
1691 `isexception` smallint(1) NOT NULL default 1,
1692 `title` varchar(50) NOT NULL default '',
1693 `description` text NOT NULL,
1695 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1698 -- Table structure for table `statistics`
1701 DROP TABLE IF EXISTS `statistics`;
1702 CREATE TABLE `statistics` (
1703 `datetime` datetime default NULL,
1704 `branch` varchar(10) default NULL,
1705 `proccode` varchar(4) default NULL,
1706 `value` double(16,4) default NULL,
1707 `type` varchar(16) default NULL,
1709 `usercode` varchar(10) default NULL,
1710 `itemnumber` int(11) default NULL,
1711 `itemtype` varchar(10) default NULL,
1712 `borrowernumber` int(11) default NULL,
1713 `associatedborrower` int(11) default NULL,
1714 KEY `timeidx` (`datetime`)
1715 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1718 -- Table structure for table `stopwords`
1721 DROP TABLE IF EXISTS `stopwords`;
1722 CREATE TABLE `stopwords` (
1723 `word` varchar(255) default NULL
1724 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1727 -- Table structure for table `subcategorytable`
1730 DROP TABLE IF EXISTS `subcategorytable`;
1731 CREATE TABLE `subcategorytable` (
1732 `subcategorycode` varchar(5) NOT NULL default '',
1734 `itemtypecodes` text,
1735 PRIMARY KEY (`subcategorycode`)
1736 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1739 -- Table structure for table `subscription`
1742 DROP TABLE IF EXISTS `subscription`;
1743 CREATE TABLE `subscription` (
1744 `biblionumber` int(11) NOT NULL default 0,
1745 `subscriptionid` int(11) NOT NULL auto_increment,
1746 `librarian` varchar(100) default '',
1747 `startdate` date default NULL,
1748 `aqbooksellerid` int(11) default 0,
1749 `cost` int(11) default 0,
1750 `aqbudgetid` int(11) default 0,
1751 `weeklength` int(11) default 0,
1752 `monthlength` int(11) default 0,
1753 `numberlength` int(11) default 0,
1754 `periodicity` tinyint(4) default 0,
1755 `dow` varchar(100) default '',
1756 `numberingmethod` varchar(100) default '',
1758 `status` varchar(100) NOT NULL default '',
1759 `add1` int(11) default 0,
1760 `every1` int(11) default 0,
1761 `whenmorethan1` int(11) default 0,
1762 `setto1` int(11) default NULL,
1763 `lastvalue1` int(11) default NULL,
1764 `add2` int(11) default 0,
1765 `every2` int(11) default 0,
1766 `whenmorethan2` int(11) default 0,
1767 `setto2` int(11) default NULL,
1768 `lastvalue2` int(11) default NULL,
1769 `add3` int(11) default 0,
1770 `every3` int(11) default 0,
1771 `innerloop1` int(11) default 0,
1772 `innerloop2` int(11) default 0,
1773 `innerloop3` int(11) default 0,
1774 `whenmorethan3` int(11) default 0,
1775 `setto3` int(11) default NULL,
1776 `lastvalue3` int(11) default NULL,
1777 `issuesatonce` tinyint(3) NOT NULL default 1,
1778 `firstacquidate` date default NULL,
1779 `manualhistory` tinyint(1) NOT NULL default 0,
1780 `irregularity` text,
1781 `letter` varchar(20) default NULL,
1782 `numberpattern` tinyint(3) default 0,
1783 `distributedto` text,
1784 `internalnotes` longtext,
1786 `branchcode` varchar(10) NOT NULL default '',
1787 `hemisphere` tinyint(3) default 0,
1788 `lastbranch` varchar(10),
1789 PRIMARY KEY (`subscriptionid`)
1790 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1793 -- Table structure for table `subscriptionhistory`
1796 DROP TABLE IF EXISTS `subscriptionhistory`;
1797 CREATE TABLE `subscriptionhistory` (
1798 `biblionumber` int(11) NOT NULL default 0,
1799 `subscriptionid` int(11) NOT NULL default 0,
1800 `histstartdate` date default NULL,
1801 `enddate` date default NULL,
1802 `missinglist` longtext NOT NULL,
1803 `recievedlist` longtext NOT NULL,
1804 `opacnote` varchar(150) NOT NULL default '',
1805 `librariannote` varchar(150) NOT NULL default '',
1806 PRIMARY KEY (`subscriptionid`),
1807 KEY `biblionumber` (`biblionumber`)
1808 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1811 -- Table structure for table `subscriptionroutinglist`
1814 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1815 CREATE TABLE `subscriptionroutinglist` (
1816 `routingid` int(11) NOT NULL auto_increment,
1817 `borrowernumber` int(11) default NULL,
1818 `ranking` int(11) default NULL,
1819 `subscriptionid` int(11) default NULL,
1820 PRIMARY KEY (`routingid`)
1821 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1824 -- Table structure for table `suggestions`
1827 DROP TABLE IF EXISTS `suggestions`;
1828 CREATE TABLE `suggestions` (
1829 `suggestionid` int(8) NOT NULL auto_increment,
1830 `suggestedby` int(11) NOT NULL default 0,
1831 `managedby` int(11) default NULL,
1832 `STATUS` varchar(10) NOT NULL default '',
1834 `author` varchar(80) default NULL,
1835 `title` varchar(80) default NULL,
1836 `copyrightdate` smallint(6) default NULL,
1837 `publishercode` varchar(255) default NULL,
1838 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1839 `volumedesc` varchar(255) default NULL,
1840 `publicationyear` smallint(6) default 0,
1841 `place` varchar(255) default NULL,
1842 `isbn` varchar(10) default NULL,
1843 `mailoverseeing` smallint(1) default 0,
1844 `biblionumber` int(11) default NULL,
1846 PRIMARY KEY (`suggestionid`),
1847 KEY `suggestedby` (`suggestedby`),
1848 KEY `managedby` (`managedby`)
1849 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1852 -- Table structure for table `systempreferences`
1855 DROP TABLE IF EXISTS `systempreferences`;
1856 CREATE TABLE `systempreferences` (
1857 `variable` varchar(50) NOT NULL default '',
1859 `options` mediumtext,
1861 `type` varchar(20) default NULL,
1862 PRIMARY KEY (`variable`)
1863 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1866 -- Table structure for table `tags`
1869 DROP TABLE IF EXISTS `tags`;
1870 CREATE TABLE `tags` (
1871 `entry` varchar(255) NOT NULL default '',
1872 `weight` bigint(20) NOT NULL default 0,
1873 PRIMARY KEY (`entry`)
1874 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1877 -- Table structure for table `userflags`
1880 DROP TABLE IF EXISTS `userflags`;
1881 CREATE TABLE `userflags` (
1882 `bit` int(11) NOT NULL default 0,
1883 `flag` varchar(30) default NULL,
1884 `flagdesc` varchar(255) default NULL,
1885 `defaulton` int(11) default NULL,
1887 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1890 -- Table structure for table `virtualshelves`
1893 DROP TABLE IF EXISTS `virtualshelves`;
1894 CREATE TABLE `virtualshelves` (
1895 `shelfnumber` int(11) NOT NULL auto_increment,
1896 `shelfname` varchar(255) default NULL,
1897 `owner` varchar(80) default NULL,
1898 `category` varchar(1) default NULL,
1899 `sortfield` varchar(16) default NULL,
1900 PRIMARY KEY (`shelfnumber`)
1901 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1904 -- Table structure for table `virtualshelfcontents`
1907 DROP TABLE IF EXISTS `virtualshelfcontents`;
1908 CREATE TABLE `virtualshelfcontents` (
1909 `shelfnumber` int(11) NOT NULL default 0,
1910 `biblionumber` int(11) NOT NULL default 0,
1911 `flags` int(11) default NULL,
1912 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1913 KEY `shelfnumber` (`shelfnumber`),
1914 KEY `biblionumber` (`biblionumber`),
1915 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1916 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1917 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1920 -- Table structure for table `z3950servers`
1923 DROP TABLE IF EXISTS `z3950servers`;
1924 CREATE TABLE `z3950servers` (
1925 `host` varchar(255) default NULL,
1926 `port` int(11) default NULL,
1927 `db` varchar(255) default NULL,
1928 `userid` varchar(255) default NULL,
1929 `password` varchar(255) default NULL,
1931 `id` int(11) NOT NULL auto_increment,
1932 `checked` smallint(6) default NULL,
1933 `rank` int(11) default NULL,
1934 `syntax` varchar(80) default NULL,
1936 `position` enum('primary','secondary','') NOT NULL default 'primary',
1937 `type` enum('zed','opensearch') NOT NULL default 'zed',
1938 `encoding` text default NULL,
1939 `description` text NOT NULL,
1941 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1944 -- Table structure for table `zebraqueue`
1947 DROP TABLE IF EXISTS `zebraqueue`;
1948 CREATE TABLE `zebraqueue` (
1949 `id` int(11) NOT NULL auto_increment,
1950 `biblio_auth_number` int(11) NOT NULL default '0',
1951 `operation` char(20) NOT NULL default '',
1952 `server` char(20) NOT NULL default '',
1953 `done` int(11) NOT NULL default '0',
1954 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1956 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1957 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1959 DROP TABLE IF EXISTS `services_throttle`;
1960 CREATE TABLE `services_throttle` (
1961 `service_type` varchar(10) NOT NULL default '',
1962 `service_count` varchar(45) default NULL,
1963 PRIMARY KEY (`service_type`)
1964 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1966 -- http://www.w3.org/International/articles/language-tags/
1969 DROP TABLE IF EXISTS language_subtag_registry;
1970 CREATE TABLE language_subtag_registry (
1972 type varchar(25), -- language-script-region-variant-extension-privateuse
1973 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1975 KEY `subtag` (`subtag`)
1976 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1978 -- TODO: add suppress_scripts
1979 -- this maps three letter codes defined in iso639.2 back to their
1980 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1981 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1982 CREATE TABLE language_rfc4646_to_iso639 (
1983 rfc4646_subtag varchar(25),
1984 iso639_2_code varchar(25),
1985 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1986 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1988 DROP TABLE IF EXISTS language_descriptions;
1989 CREATE TABLE language_descriptions (
1993 description varchar(255),
1995 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1997 -- bi-directional support, keyed by script subcode
1998 DROP TABLE IF EXISTS language_script_bidi;
1999 CREATE TABLE language_script_bidi (
2000 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2001 bidi varchar(3), -- rtl ltr
2002 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2003 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2005 -- TODO: need to map language subtags to script subtags for detection
2006 -- of bidi when script is not specified (like ar, he)
2007 DROP TABLE IF EXISTS language_script_mapping;
2008 CREATE TABLE language_script_mapping (
2009 language_subtag varchar(25),
2010 script_subtag varchar(25),
2011 KEY `language_subtag` (`language_subtag`)
2012 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2014 DROP TABLE IF EXISTS `permissions`;
2015 CREATE TABLE `permissions` (
2016 `module_bit` int(11) NOT NULL DEFAULT 0,
2017 `code` varchar(30) DEFAULT NULL,
2018 `description` varchar(255) DEFAULT NULL,
2019 PRIMARY KEY (`module_bit`, `code`),
2020 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2021 ON DELETE CASCADE ON UPDATE CASCADE
2022 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2024 DROP TABLE IF EXISTS serialitems;
2025 CREATE TABLE serialitems (
2026 serialid int(11) NOT NULL,
2027 itemnumber int(11) NOT NULL,
2028 UNIQUE KEY `serialididx` (`serialid`)
2029 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2031 DROP TABLE IF EXISTS `user_permissions`;
2032 CREATE TABLE `user_permissions` (
2033 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2034 `module_bit` int(11) NOT NULL DEFAULT 0,
2035 `code` varchar(30) DEFAULT NULL,
2036 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2037 ON DELETE CASCADE ON UPDATE CASCADE,
2038 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2039 ON DELETE CASCADE ON UPDATE CASCADE
2040 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2042 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2043 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2044 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2045 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2046 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2047 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2048 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2049 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;