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', 'ignore') NOT NULL default 'create_new',
876 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
877 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
878 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
879 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
880 `file_name` varchar(100),
881 `comments` mediumtext,
882 PRIMARY KEY (`import_batch_id`),
883 KEY `branchcode` (`branchcode`)
884 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
887 -- Table structure for table `import_records`
890 DROP TABLE IF EXISTS `import_records`;
891 CREATE TABLE `import_records` (
892 `import_record_id` int(11) NOT NULL auto_increment,
893 `import_batch_id` int(11) NOT NULL,
894 `branchcode` varchar(10) default NULL,
895 `record_sequence` int(11) NOT NULL default 0,
896 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
897 `import_date` DATE default NULL,
898 `marc` longblob NOT NULL,
899 `marcxml` longtext NOT NULL,
900 `marcxml_old` longtext NOT NULL,
901 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
902 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
903 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
904 `import_error` mediumtext,
905 `encoding` varchar(40) NOT NULL default '',
906 `z3950random` varchar(40) default NULL,
907 PRIMARY KEY (`import_record_id`),
908 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
909 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
910 KEY `branchcode` (`branchcode`),
911 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
912 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
915 -- Table structure for `import_record_matches`
917 DROP TABLE IF EXISTS `import_record_matches`;
918 CREATE TABLE `import_record_matches` (
919 `import_record_id` int(11) NOT NULL,
920 `candidate_match_id` int(11) NOT NULL,
921 `score` int(11) NOT NULL default 0,
922 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
923 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
924 KEY `record_score` (`import_record_id`, `score`)
925 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
928 -- Table structure for table `import_biblios`
931 DROP TABLE IF EXISTS `import_biblios`;
932 CREATE TABLE `import_biblios` (
933 `import_record_id` int(11) NOT NULL,
934 `matched_biblionumber` int(11) default NULL,
935 `control_number` varchar(25) default NULL,
936 `original_source` varchar(25) default NULL,
937 `title` varchar(128) default NULL,
938 `author` varchar(80) default NULL,
939 `isbn` varchar(14) default NULL,
940 `issn` varchar(9) default NULL,
941 `has_items` tinyint(1) NOT NULL default 0,
942 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
943 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
944 KEY `matched_biblionumber` (`matched_biblionumber`),
945 KEY `title` (`title`),
947 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
950 -- Table structure for table `import_items`
953 DROP TABLE IF EXISTS `import_items`;
954 CREATE TABLE `import_items` (
955 `import_items_id` int(11) NOT NULL auto_increment,
956 `import_record_id` int(11) NOT NULL,
957 `itemnumber` int(11) default NULL,
958 `branchcode` varchar(10) default NULL,
959 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
960 `marcxml` longtext NOT NULL,
961 `import_error` mediumtext,
962 PRIMARY KEY (`import_items_id`),
963 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
964 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
965 KEY `itemnumber` (`itemnumber`),
966 KEY `branchcode` (`branchcode`)
967 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
970 -- Table structure for table `issues`
973 DROP TABLE IF EXISTS `issues`;
974 CREATE TABLE `issues` (
975 `borrowernumber` int(11) default NULL,
976 `itemnumber` int(11) default NULL,
977 `date_due` date default NULL,
978 `branchcode` varchar(10) default NULL,
979 `issuingbranch` varchar(18) default NULL,
980 `returndate` date default NULL,
981 `lastreneweddate` date default NULL,
982 `return` varchar(4) default NULL,
983 `renewals` tinyint(4) default NULL,
984 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
985 `issuedate` date default NULL,
986 KEY `issuesborridx` (`borrowernumber`),
987 KEY `issuesitemidx` (`itemnumber`),
988 KEY `bordate` (`borrowernumber`,`timestamp`),
989 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
990 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
991 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
994 -- Table structure for table `issuingrules`
997 DROP TABLE IF EXISTS `issuingrules`;
998 CREATE TABLE `issuingrules` (
999 `categorycode` varchar(10) NOT NULL default '',
1000 `itemtype` varchar(10) NOT NULL default '',
1001 `restrictedtype` tinyint(1) default NULL,
1002 `rentaldiscount` decimal(28,6) default NULL,
1003 `reservecharge` decimal(28,6) default NULL,
1004 `fine` decimal(28,6) default NULL,
1005 `firstremind` int(11) default NULL,
1006 `chargeperiod` int(11) default NULL,
1007 `accountsent` int(11) default NULL,
1008 `chargename` varchar(100) default NULL,
1009 `maxissueqty` int(4) default NULL,
1010 `issuelength` int(4) default NULL,
1011 `branchcode` varchar(10) NOT NULL default '',
1012 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1013 KEY `categorycode` (`categorycode`),
1014 KEY `itemtype` (`itemtype`)
1015 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1018 -- Table structure for table `items`
1021 DROP TABLE IF EXISTS `items`;
1022 CREATE TABLE `items` (
1023 `itemnumber` int(11) NOT NULL auto_increment,
1024 `biblionumber` int(11) NOT NULL default 0,
1025 `biblioitemnumber` int(11) NOT NULL default 0,
1026 `barcode` varchar(20) default NULL,
1027 `dateaccessioned` date default NULL,
1028 `booksellerid` mediumtext default NULL,
1029 `homebranch` varchar(10) default NULL,
1030 `price` decimal(8,2) default NULL,
1031 `replacementprice` decimal(8,2) default NULL,
1032 `replacementpricedate` date default NULL,
1033 `datelastborrowed` date default NULL,
1034 `datelastseen` date default NULL,
1035 `stack` tinyint(1) default NULL,
1036 `notforloan` tinyint(1) NOT NULL default 0,
1037 `damaged` tinyint(1) NOT NULL default 0,
1038 `itemlost` tinyint(1) NOT NULL default 0,
1039 `wthdrawn` tinyint(1) NOT NULL default 0,
1040 `itemcallnumber` varchar(30) default NULL,
1041 `issues` smallint(6) default NULL,
1042 `renewals` smallint(6) default NULL,
1043 `reserves` smallint(6) default NULL,
1044 `restricted` tinyint(1) default NULL,
1045 `itemnotes` mediumtext,
1046 `holdingbranch` varchar(10) default NULL,
1047 `paidfor` mediumtext,
1048 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1049 `location` varchar(80) default NULL,
1050 `onloan` date default NULL,
1051 `cn_source` varchar(10) default NULL,
1052 `cn_sort` varchar(30) default NULL,
1053 `ccode` varchar(10) default NULL,
1054 `materials` varchar(10) default NULL,
1055 `uri` varchar(255) default NULL,
1056 `itype` varchar(10) default NULL,
1057 `more_subfields_xml` longtext default NULL,
1058 `enumchron` varchar(80) default NULL,
1059 `copynumber` smallint(6) default NULL,
1060 PRIMARY KEY (`itemnumber`),
1061 UNIQUE KEY `itembarcodeidx` (`barcode`),
1062 KEY `itembinoidx` (`biblioitemnumber`),
1063 KEY `itembibnoidx` (`biblionumber`),
1064 KEY `homebranch` (`homebranch`),
1065 KEY `holdingbranch` (`holdingbranch`),
1066 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1067 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1068 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1069 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1072 -- Table structure for table `itemtypes`
1075 DROP TABLE IF EXISTS `itemtypes`;
1076 CREATE TABLE `itemtypes` (
1077 `itemtype` varchar(10) NOT NULL default '',
1078 `description` mediumtext,
1079 `renewalsallowed` smallint(6) default NULL,
1080 `rentalcharge` double(16,4) default NULL,
1081 `notforloan` smallint(6) default NULL,
1082 `imageurl` varchar(200) default NULL,
1084 PRIMARY KEY (`itemtype`),
1085 UNIQUE KEY `itemtype` (`itemtype`)
1086 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1089 -- Table structure for table `labels`
1092 DROP TABLE IF EXISTS `labels`;
1093 CREATE TABLE `labels` (
1094 `labelid` int(11) NOT NULL auto_increment,
1095 `batch_id` varchar(10) NOT NULL default 1,
1096 `itemnumber` varchar(100) NOT NULL default '',
1097 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1098 PRIMARY KEY (`labelid`)
1099 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1102 -- Table structure for table `labels_conf`
1105 DROP TABLE IF EXISTS `labels_conf`;
1106 CREATE TABLE `labels_conf` (
1107 `id` int(4) NOT NULL auto_increment,
1108 `barcodetype` char(100) default '',
1109 `title` int(1) default '0',
1110 `subtitle` int(1) default '0',
1111 `itemtype` int(1) default '0',
1112 `barcode` int(1) default '0',
1113 `dewey` int(1) default '0',
1114 `classification` int(1) default NULL,
1115 `subclass` int(1) default '0',
1116 `itemcallnumber` int(1) default '0',
1117 `author` int(1) default '0',
1118 `issn` int(1) default '0',
1119 `isbn` int(1) default '0',
1120 `startlabel` int(2) NOT NULL default '1',
1121 `printingtype` char(32) default 'BAR',
1122 `formatstring` varchar(64) default NULL,
1123 `layoutname` char(20) NOT NULL default 'TEST',
1124 `guidebox` int(1) default '0',
1125 `active` tinyint(1) default '1',
1126 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1127 `ccode` char(4) collate utf8_unicode_ci default NULL,
1128 `callnum_split` int(1) default NULL,
1129 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1134 -- Table structure for table `labels_profile`
1137 DROP TABLE IF EXISTS `labels_profile`;
1138 CREATE TABLE `labels_profile` (
1139 `tmpl_id` int(4) NOT NULL,
1140 `prof_id` int(4) NOT NULL,
1141 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1142 UNIQUE KEY `prof_id` (`prof_id`)
1143 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1146 -- Table structure for table `labels_templates`
1149 DROP TABLE IF EXISTS `labels_templates`;
1150 CREATE TABLE `labels_templates` (
1151 `tmpl_id` int(4) NOT NULL auto_increment,
1152 `tmpl_code` char(100) default '',
1153 `tmpl_desc` char(100) default '',
1154 `page_width` float default '0',
1155 `page_height` float default '0',
1156 `label_width` float default '0',
1157 `label_height` float default '0',
1158 `topmargin` float default '0',
1159 `leftmargin` float default '0',
1160 `cols` int(2) default '0',
1161 `rows` int(2) default '0',
1162 `colgap` float default '0',
1163 `rowgap` float default '0',
1164 `active` int(1) default NULL,
1165 `units` char(20) default 'PX',
1166 `fontsize` int(4) NOT NULL default '3',
1167 `font` char(10) NOT NULL default 'TR',
1168 PRIMARY KEY (`tmpl_id`)
1169 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1172 -- Table structure for table `letter`
1175 DROP TABLE IF EXISTS `letter`;
1176 CREATE TABLE `letter` (
1177 `module` varchar(20) NOT NULL default '',
1178 `code` varchar(20) NOT NULL default '',
1179 `name` varchar(100) NOT NULL default '',
1180 `title` varchar(200) NOT NULL default '',
1182 PRIMARY KEY (`module`,`code`)
1183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1186 -- Table structure for table `marc_subfield_structure`
1189 DROP TABLE IF EXISTS `marc_subfield_structure`;
1190 CREATE TABLE `marc_subfield_structure` (
1191 `tagfield` varchar(3) NOT NULL default '',
1192 `tagsubfield` varchar(1) NOT NULL default '',
1193 `liblibrarian` varchar(255) NOT NULL default '',
1194 `libopac` varchar(255) NOT NULL default '',
1195 `repeatable` tinyint(4) NOT NULL default 0,
1196 `mandatory` tinyint(4) NOT NULL default 0,
1197 `kohafield` varchar(40) default NULL,
1198 `tab` tinyint(1) default NULL,
1199 `authorised_value` varchar(20) default NULL,
1200 `authtypecode` varchar(20) default NULL,
1201 `value_builder` varchar(80) default NULL,
1202 `isurl` tinyint(1) default NULL,
1203 `hidden` tinyint(1) default NULL,
1204 `frameworkcode` varchar(4) NOT NULL default '',
1205 `seealso` varchar(1100) default NULL,
1206 `link` varchar(80) default NULL,
1207 `defaultvalue` text default NULL,
1208 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1209 KEY `kohafield_2` (`kohafield`),
1210 KEY `tab` (`frameworkcode`,`tab`),
1211 KEY `kohafield` (`frameworkcode`,`kohafield`)
1212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1215 -- Table structure for table `marc_tag_structure`
1218 DROP TABLE IF EXISTS `marc_tag_structure`;
1219 CREATE TABLE `marc_tag_structure` (
1220 `tagfield` varchar(3) NOT NULL default '',
1221 `liblibrarian` varchar(255) NOT NULL default '',
1222 `libopac` varchar(255) NOT NULL default '',
1223 `repeatable` tinyint(4) NOT NULL default 0,
1224 `mandatory` tinyint(4) NOT NULL default 0,
1225 `authorised_value` varchar(10) default NULL,
1226 `frameworkcode` varchar(4) NOT NULL default '',
1227 PRIMARY KEY (`frameworkcode`,`tagfield`)
1228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1231 -- Table structure for table `marc_matchers`
1234 DROP TABLE IF EXISTS `marc_matchers`;
1235 CREATE TABLE `marc_matchers` (
1236 `matcher_id` int(11) NOT NULL auto_increment,
1237 `code` varchar(10) NOT NULL default '',
1238 `description` varchar(255) NOT NULL default '',
1239 `record_type` varchar(10) NOT NULL default 'biblio',
1240 `threshold` int(11) NOT NULL default 0,
1241 PRIMARY KEY (`matcher_id`),
1242 KEY `code` (`code`),
1243 KEY `record_type` (`record_type`)
1244 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1247 -- Table structure for table `matchpoints`
1249 DROP TABLE IF EXISTS `matchpoints`;
1250 CREATE TABLE `matchpoints` (
1251 `matcher_id` int(11) NOT NULL,
1252 `matchpoint_id` int(11) NOT NULL auto_increment,
1253 `search_index` varchar(30) NOT NULL default '',
1254 `score` int(11) NOT NULL default 0,
1255 PRIMARY KEY (`matchpoint_id`),
1256 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1257 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1258 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1262 -- Table structure for table `matchpoint_components`
1264 DROP TABLE IF EXISTS `matchpoint_components`;
1265 CREATE TABLE `matchpoint_components` (
1266 `matchpoint_id` int(11) NOT NULL,
1267 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1268 sequence int(11) NOT NULL default 0,
1269 tag varchar(3) NOT NULL default '',
1270 subfields varchar(40) NOT NULL default '',
1271 offset int(4) NOT NULL default 0,
1272 length int(4) NOT NULL default 0,
1273 PRIMARY KEY (`matchpoint_component_id`),
1274 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1275 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1276 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1280 -- Table structure for table `matcher_component_norms`
1282 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1283 CREATE TABLE `matchpoint_component_norms` (
1284 `matchpoint_component_id` int(11) NOT NULL,
1285 `sequence` int(11) NOT NULL default 0,
1286 `norm_routine` varchar(50) NOT NULL default '',
1287 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1288 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1289 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1290 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1293 -- Table structure for table `matcher_matchpoints`
1295 DROP TABLE IF EXISTS `matcher_matchpoints`;
1296 CREATE TABLE `matcher_matchpoints` (
1297 `matcher_id` int(11) NOT NULL,
1298 `matchpoint_id` int(11) NOT NULL,
1299 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1300 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1301 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1302 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1303 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1306 -- Table structure for table `matchchecks`
1308 DROP TABLE IF EXISTS `matchchecks`;
1309 CREATE TABLE `matchchecks` (
1310 `matcher_id` int(11) NOT NULL,
1311 `matchcheck_id` int(11) NOT NULL auto_increment,
1312 `source_matchpoint_id` int(11) NOT NULL,
1313 `target_matchpoint_id` int(11) NOT NULL,
1314 PRIMARY KEY (`matchcheck_id`),
1315 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1316 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1317 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1318 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1319 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1320 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1321 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1324 -- Table structure for table `mediatypetable`
1327 DROP TABLE IF EXISTS `mediatypetable`;
1328 CREATE TABLE `mediatypetable` (
1329 `mediatypecode` varchar(5) NOT NULL default '',
1331 `itemtypecodes` text,
1332 PRIMARY KEY (`mediatypecode`)
1333 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1336 -- Table structure for table `notifys`
1339 DROP TABLE IF EXISTS `notifys`;
1340 CREATE TABLE `notifys` (
1341 `notify_id` int(11) NOT NULL default 0,
1342 `borrowernumber` int(11) NOT NULL default 0,
1343 `itemnumber` int(11) NOT NULL default 0,
1344 `notify_date` date default NULL,
1345 `notify_send_date` date default NULL,
1346 `notify_level` int(1) NOT NULL default 0,
1347 `method` varchar(20) NOT NULL default ''
1348 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1351 -- Table structure for table `nozebra`
1354 DROP TABLE IF EXISTS `nozebra`;
1355 CREATE TABLE `nozebra` (
1356 `server` varchar(20) NOT NULL,
1357 `indexname` varchar(40) NOT NULL,
1358 `value` varchar(250) NOT NULL,
1359 `biblionumbers` longtext NOT NULL,
1360 KEY `indexname` (`server`,`indexname`),
1361 KEY `value` (`server`,`value`))
1362 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1365 -- Table structure for table `old_issues`
1368 DROP TABLE IF EXISTS `old_issues`;
1369 CREATE TABLE `old_issues` (
1370 `borrowernumber` int(11) default NULL,
1371 `itemnumber` int(11) default NULL,
1372 `date_due` date default NULL,
1373 `branchcode` varchar(10) default NULL,
1374 `issuingbranch` varchar(18) default NULL,
1375 `returndate` date default NULL,
1376 `lastreneweddate` date default NULL,
1377 `return` varchar(4) default NULL,
1378 `renewals` tinyint(4) default NULL,
1379 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1380 `issuedate` date default NULL,
1381 KEY `old_issuesborridx` (`borrowernumber`),
1382 KEY `old_issuesitemidx` (`itemnumber`),
1383 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1384 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1385 ON DELETE SET NULL ON UPDATE SET NULL,
1386 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1387 ON DELETE SET NULL ON UPDATE SET NULL
1388 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1391 -- Table structure for table `old_reserves`
1393 DROP TABLE IF EXISTS `old_reserves`;
1394 CREATE TABLE `old_reserves` (
1395 `borrowernumber` int(11) default NULL,
1396 `reservedate` date default NULL,
1397 `biblionumber` int(11) default NULL,
1398 `constrainttype` varchar(1) default NULL,
1399 `branchcode` varchar(10) default NULL,
1400 `notificationdate` date default NULL,
1401 `reminderdate` date default NULL,
1402 `cancellationdate` date default NULL,
1403 `reservenotes` mediumtext,
1404 `priority` smallint(6) default NULL,
1405 `found` varchar(1) default NULL,
1406 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1407 `itemnumber` int(11) default NULL,
1408 `waitingdate` date default NULL,
1409 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1410 KEY `old_reserves_biblionumber` (`biblionumber`),
1411 KEY `old_reserves_itemnumber` (`itemnumber`),
1412 KEY `old_reserves_branchcode` (`branchcode`),
1413 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1414 ON DELETE SET NULL ON UPDATE SET NULL,
1415 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1416 ON DELETE SET NULL ON UPDATE SET NULL,
1417 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1418 ON DELETE SET NULL ON UPDATE SET NULL
1419 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1422 -- Table structure for table `opac_news`
1425 DROP TABLE IF EXISTS `opac_news`;
1426 CREATE TABLE `opac_news` (
1427 `idnew` int(10) unsigned NOT NULL auto_increment,
1428 `title` varchar(250) NOT NULL default '',
1429 `new` text NOT NULL,
1430 `lang` varchar(25) NOT NULL default '',
1431 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1432 `expirationdate` date default NULL,
1433 `number` int(11) default NULL,
1434 PRIMARY KEY (`idnew`)
1435 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1438 -- Table structure for table `overduerules`
1441 DROP TABLE IF EXISTS `overduerules`;
1442 CREATE TABLE `overduerules` (
1443 `branchcode` varchar(10) NOT NULL default '',
1444 `categorycode` varchar(2) NOT NULL default '',
1445 `delay1` int(4) default 0,
1446 `letter1` varchar(20) default NULL,
1447 `debarred1` varchar(1) default 0,
1448 `delay2` int(4) default 0,
1449 `debarred2` varchar(1) default 0,
1450 `letter2` varchar(20) default NULL,
1451 `delay3` int(4) default 0,
1452 `letter3` varchar(20) default NULL,
1453 `debarred3` int(1) default 0,
1454 PRIMARY KEY (`branchcode`,`categorycode`)
1455 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1458 -- Table structure for table `patroncards`
1461 DROP TABLE IF EXISTS `patroncards`;
1462 CREATE TABLE `patroncards` (
1463 `cardid` int(11) NOT NULL auto_increment,
1464 `batch_id` varchar(10) NOT NULL default '1',
1465 `borrowernumber` int(11) NOT NULL,
1466 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1467 PRIMARY KEY (`cardid`),
1468 KEY `patroncards_ibfk_1` (`borrowernumber`),
1469 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1470 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1473 -- Table structure for table `patronimage`
1476 DROP TABLE IF EXISTS `patronimage`;
1477 CREATE TABLE `patronimage` (
1478 `cardnumber` varchar(16) NOT NULL,
1479 `mimetype` varchar(15) NOT NULL,
1480 `imagefile` mediumblob NOT NULL,
1481 PRIMARY KEY (`cardnumber`),
1482 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1483 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1486 -- Table structure for table `printers`
1489 DROP TABLE IF EXISTS `printers`;
1490 CREATE TABLE `printers` (
1491 `printername` varchar(40) NOT NULL default '',
1492 `printqueue` varchar(20) default NULL,
1493 `printtype` varchar(20) default NULL,
1494 PRIMARY KEY (`printername`)
1495 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1498 -- Table structure for table `printers_profile`
1501 DROP TABLE IF EXISTS `printers_profile`;
1502 CREATE TABLE `printers_profile` (
1503 `prof_id` int(4) NOT NULL auto_increment,
1504 `printername` varchar(40) NOT NULL,
1505 `tmpl_id` int(4) NOT NULL,
1506 `paper_bin` varchar(20) NOT NULL,
1507 `offset_horz` float default NULL,
1508 `offset_vert` float default NULL,
1509 `creep_horz` float default NULL,
1510 `creep_vert` float default NULL,
1511 `unit` char(20) NOT NULL default 'POINT',
1512 PRIMARY KEY (`prof_id`),
1513 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1514 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1515 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1518 -- Table structure for table `repeatable_holidays`
1521 DROP TABLE IF EXISTS `repeatable_holidays`;
1522 CREATE TABLE `repeatable_holidays` (
1523 `id` int(11) NOT NULL auto_increment,
1524 `branchcode` varchar(10) NOT NULL default '',
1525 `weekday` smallint(6) default NULL,
1526 `day` smallint(6) default NULL,
1527 `month` smallint(6) default NULL,
1528 `title` varchar(50) NOT NULL default '',
1529 `description` text NOT NULL,
1531 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1534 -- Table structure for table `reports_dictionary`
1537 DROP TABLE IF EXISTS `reports_dictionary`;
1538 CREATE TABLE reports_dictionary (
1539 `id` int(11) NOT NULL auto_increment,
1540 `name` varchar(255) default NULL,
1542 `date_created` datetime default NULL,
1543 `date_modified` datetime default NULL,
1545 `area` int(11) default NULL,
1547 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1550 -- Table structure for table `reserveconstraints`
1553 DROP TABLE IF EXISTS `reserveconstraints`;
1554 CREATE TABLE `reserveconstraints` (
1555 `borrowernumber` int(11) NOT NULL default 0,
1556 `reservedate` date default NULL,
1557 `biblionumber` int(11) NOT NULL default 0,
1558 `biblioitemnumber` int(11) default NULL,
1559 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1560 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1563 -- Table structure for table `reserves`
1566 DROP TABLE IF EXISTS `reserves`;
1567 CREATE TABLE `reserves` (
1568 `borrowernumber` int(11) NOT NULL default 0,
1569 `reservedate` date default NULL,
1570 `biblionumber` int(11) NOT NULL default 0,
1571 `constrainttype` varchar(1) default NULL,
1572 `branchcode` varchar(10) default NULL,
1573 `notificationdate` date default NULL,
1574 `reminderdate` date default NULL,
1575 `cancellationdate` date default NULL,
1576 `reservenotes` mediumtext,
1577 `priority` smallint(6) default NULL,
1578 `found` varchar(1) default NULL,
1579 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1580 `itemnumber` int(11) default NULL,
1581 `waitingdate` date default NULL,
1582 KEY `borrowernumber` (`borrowernumber`),
1583 KEY `biblionumber` (`biblionumber`),
1584 KEY `itemnumber` (`itemnumber`),
1585 KEY `branchcode` (`branchcode`),
1586 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1587 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1588 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1589 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1590 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1593 -- Table structure for table `reviews`
1596 DROP TABLE IF EXISTS `reviews`;
1597 CREATE TABLE `reviews` (
1598 `reviewid` int(11) NOT NULL auto_increment,
1599 `borrowernumber` int(11) default NULL,
1600 `biblionumber` int(11) default NULL,
1602 `approved` tinyint(4) default NULL,
1603 `datereviewed` datetime default NULL,
1604 PRIMARY KEY (`reviewid`)
1605 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1608 -- Table structure for table `roadtype`
1611 DROP TABLE IF EXISTS `roadtype`;
1612 CREATE TABLE `roadtype` (
1613 `roadtypeid` int(11) NOT NULL auto_increment,
1614 `road_type` varchar(100) NOT NULL default '',
1615 PRIMARY KEY (`roadtypeid`)
1616 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1619 -- Table structure for table `saved_sql`
1622 DROP TABLE IF EXISTS `saved_sql`;
1623 CREATE TABLE saved_sql (
1624 `id` int(11) NOT NULL auto_increment,
1625 `borrowernumber` int(11) default NULL,
1626 `date_created` datetime default NULL,
1627 `last_modified` datetime default NULL,
1629 `last_run` datetime default NULL,
1630 `report_name` varchar(255) default NULL,
1631 `type` varchar(255) default NULL,
1634 KEY boridx (`borrowernumber`)
1635 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1639 -- Table structure for `saved_reports`
1642 DROP TABLE IF EXISTS `saved_reports`;
1643 CREATE TABLE saved_reports (
1644 `id` int(11) NOT NULL auto_increment,
1645 `report_id` int(11) default NULL,
1647 `date_run` datetime default NULL,
1649 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1653 -- Table structure for table `serial`
1656 DROP TABLE IF EXISTS `serial`;
1657 CREATE TABLE `serial` (
1658 `serialid` int(11) NOT NULL auto_increment,
1659 `biblionumber` varchar(100) NOT NULL default '',
1660 `subscriptionid` varchar(100) NOT NULL default '',
1661 `serialseq` varchar(100) NOT NULL default '',
1662 `status` tinyint(4) NOT NULL default 0,
1663 `planneddate` date default NULL,
1665 `publisheddate` date default NULL,
1666 `itemnumber` text default NULL,
1667 `claimdate` date default NULL,
1668 `routingnotes` text,
1669 PRIMARY KEY (`serialid`)
1670 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1673 -- Table structure for table `sessions`
1676 DROP TABLE IF EXISTS sessions;
1677 CREATE TABLE sessions (
1678 `id` varchar(32) NOT NULL,
1679 `a_session` text NOT NULL,
1681 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1684 -- Table structure for table `special_holidays`
1687 DROP TABLE IF EXISTS `special_holidays`;
1688 CREATE TABLE `special_holidays` (
1689 `id` int(11) NOT NULL auto_increment,
1690 `branchcode` varchar(10) NOT NULL default '',
1691 `day` smallint(6) NOT NULL default 0,
1692 `month` smallint(6) NOT NULL default 0,
1693 `year` smallint(6) NOT NULL default 0,
1694 `isexception` smallint(1) NOT NULL default 1,
1695 `title` varchar(50) NOT NULL default '',
1696 `description` text NOT NULL,
1698 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1701 -- Table structure for table `statistics`
1704 DROP TABLE IF EXISTS `statistics`;
1705 CREATE TABLE `statistics` (
1706 `datetime` datetime default NULL,
1707 `branch` varchar(10) default NULL,
1708 `proccode` varchar(4) default NULL,
1709 `value` double(16,4) default NULL,
1710 `type` varchar(16) default NULL,
1712 `usercode` varchar(10) default NULL,
1713 `itemnumber` int(11) default NULL,
1714 `itemtype` varchar(10) default NULL,
1715 `borrowernumber` int(11) default NULL,
1716 `associatedborrower` int(11) default NULL,
1717 KEY `timeidx` (`datetime`)
1718 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1721 -- Table structure for table `stopwords`
1724 DROP TABLE IF EXISTS `stopwords`;
1725 CREATE TABLE `stopwords` (
1726 `word` varchar(255) default NULL
1727 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1730 -- Table structure for table `subcategorytable`
1733 DROP TABLE IF EXISTS `subcategorytable`;
1734 CREATE TABLE `subcategorytable` (
1735 `subcategorycode` varchar(5) NOT NULL default '',
1737 `itemtypecodes` text,
1738 PRIMARY KEY (`subcategorycode`)
1739 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1742 -- Table structure for table `subscription`
1745 DROP TABLE IF EXISTS `subscription`;
1746 CREATE TABLE `subscription` (
1747 `biblionumber` int(11) NOT NULL default 0,
1748 `subscriptionid` int(11) NOT NULL auto_increment,
1749 `librarian` varchar(100) default '',
1750 `startdate` date default NULL,
1751 `aqbooksellerid` int(11) default 0,
1752 `cost` int(11) default 0,
1753 `aqbudgetid` int(11) default 0,
1754 `weeklength` int(11) default 0,
1755 `monthlength` int(11) default 0,
1756 `numberlength` int(11) default 0,
1757 `periodicity` tinyint(4) default 0,
1758 `dow` varchar(100) default '',
1759 `numberingmethod` varchar(100) default '',
1761 `status` varchar(100) NOT NULL default '',
1762 `add1` int(11) default 0,
1763 `every1` int(11) default 0,
1764 `whenmorethan1` int(11) default 0,
1765 `setto1` int(11) default NULL,
1766 `lastvalue1` int(11) default NULL,
1767 `add2` int(11) default 0,
1768 `every2` int(11) default 0,
1769 `whenmorethan2` int(11) default 0,
1770 `setto2` int(11) default NULL,
1771 `lastvalue2` int(11) default NULL,
1772 `add3` int(11) default 0,
1773 `every3` int(11) default 0,
1774 `innerloop1` int(11) default 0,
1775 `innerloop2` int(11) default 0,
1776 `innerloop3` int(11) default 0,
1777 `whenmorethan3` int(11) default 0,
1778 `setto3` int(11) default NULL,
1779 `lastvalue3` int(11) default NULL,
1780 `issuesatonce` tinyint(3) NOT NULL default 1,
1781 `firstacquidate` date default NULL,
1782 `manualhistory` tinyint(1) NOT NULL default 0,
1783 `irregularity` text,
1784 `letter` varchar(20) default NULL,
1785 `numberpattern` tinyint(3) default 0,
1786 `distributedto` text,
1787 `internalnotes` longtext,
1789 `branchcode` varchar(10) NOT NULL default '',
1790 `hemisphere` tinyint(3) default 0,
1791 `lastbranch` varchar(10),
1792 PRIMARY KEY (`subscriptionid`)
1793 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1796 -- Table structure for table `subscriptionhistory`
1799 DROP TABLE IF EXISTS `subscriptionhistory`;
1800 CREATE TABLE `subscriptionhistory` (
1801 `biblionumber` int(11) NOT NULL default 0,
1802 `subscriptionid` int(11) NOT NULL default 0,
1803 `histstartdate` date default NULL,
1804 `enddate` date default NULL,
1805 `missinglist` longtext NOT NULL,
1806 `recievedlist` longtext NOT NULL,
1807 `opacnote` varchar(150) NOT NULL default '',
1808 `librariannote` varchar(150) NOT NULL default '',
1809 PRIMARY KEY (`subscriptionid`),
1810 KEY `biblionumber` (`biblionumber`)
1811 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1814 -- Table structure for table `subscriptionroutinglist`
1817 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1818 CREATE TABLE `subscriptionroutinglist` (
1819 `routingid` int(11) NOT NULL auto_increment,
1820 `borrowernumber` int(11) default NULL,
1821 `ranking` int(11) default NULL,
1822 `subscriptionid` int(11) default NULL,
1823 PRIMARY KEY (`routingid`)
1824 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1827 -- Table structure for table `suggestions`
1830 DROP TABLE IF EXISTS `suggestions`;
1831 CREATE TABLE `suggestions` (
1832 `suggestionid` int(8) NOT NULL auto_increment,
1833 `suggestedby` int(11) NOT NULL default 0,
1834 `managedby` int(11) default NULL,
1835 `STATUS` varchar(10) NOT NULL default '',
1837 `author` varchar(80) default NULL,
1838 `title` varchar(80) default NULL,
1839 `copyrightdate` smallint(6) default NULL,
1840 `publishercode` varchar(255) default NULL,
1841 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1842 `volumedesc` varchar(255) default NULL,
1843 `publicationyear` smallint(6) default 0,
1844 `place` varchar(255) default NULL,
1845 `isbn` varchar(10) default NULL,
1846 `mailoverseeing` smallint(1) default 0,
1847 `biblionumber` int(11) default NULL,
1849 PRIMARY KEY (`suggestionid`),
1850 KEY `suggestedby` (`suggestedby`),
1851 KEY `managedby` (`managedby`)
1852 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1855 -- Table structure for table `systempreferences`
1858 DROP TABLE IF EXISTS `systempreferences`;
1859 CREATE TABLE `systempreferences` (
1860 `variable` varchar(50) NOT NULL default '',
1862 `options` mediumtext,
1864 `type` varchar(20) default NULL,
1865 PRIMARY KEY (`variable`)
1866 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1869 -- Table structure for table `tags`
1872 DROP TABLE IF EXISTS `tags`;
1873 CREATE TABLE `tags` (
1874 `entry` varchar(255) NOT NULL default '',
1875 `weight` bigint(20) NOT NULL default 0,
1876 PRIMARY KEY (`entry`)
1877 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1880 -- Table structure for table `userflags`
1883 DROP TABLE IF EXISTS `userflags`;
1884 CREATE TABLE `userflags` (
1885 `bit` int(11) NOT NULL default 0,
1886 `flag` varchar(30) default NULL,
1887 `flagdesc` varchar(255) default NULL,
1888 `defaulton` int(11) default NULL,
1890 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1893 -- Table structure for table `virtualshelves`
1896 DROP TABLE IF EXISTS `virtualshelves`;
1897 CREATE TABLE `virtualshelves` (
1898 `shelfnumber` int(11) NOT NULL auto_increment,
1899 `shelfname` varchar(255) default NULL,
1900 `owner` varchar(80) default NULL,
1901 `category` varchar(1) default NULL,
1902 `sortfield` varchar(16) default NULL,
1903 PRIMARY KEY (`shelfnumber`)
1904 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1907 -- Table structure for table `virtualshelfcontents`
1910 DROP TABLE IF EXISTS `virtualshelfcontents`;
1911 CREATE TABLE `virtualshelfcontents` (
1912 `shelfnumber` int(11) NOT NULL default 0,
1913 `biblionumber` int(11) NOT NULL default 0,
1914 `flags` int(11) default NULL,
1915 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1916 KEY `shelfnumber` (`shelfnumber`),
1917 KEY `biblionumber` (`biblionumber`),
1918 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1919 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1920 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1923 -- Table structure for table `z3950servers`
1926 DROP TABLE IF EXISTS `z3950servers`;
1927 CREATE TABLE `z3950servers` (
1928 `host` varchar(255) default NULL,
1929 `port` int(11) default NULL,
1930 `db` varchar(255) default NULL,
1931 `userid` varchar(255) default NULL,
1932 `password` varchar(255) default NULL,
1934 `id` int(11) NOT NULL auto_increment,
1935 `checked` smallint(6) default NULL,
1936 `rank` int(11) default NULL,
1937 `syntax` varchar(80) default NULL,
1939 `position` enum('primary','secondary','') NOT NULL default 'primary',
1940 `type` enum('zed','opensearch') NOT NULL default 'zed',
1941 `encoding` text default NULL,
1942 `description` text NOT NULL,
1944 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1947 -- Table structure for table `zebraqueue`
1950 DROP TABLE IF EXISTS `zebraqueue`;
1951 CREATE TABLE `zebraqueue` (
1952 `id` int(11) NOT NULL auto_increment,
1953 `biblio_auth_number` int(11) NOT NULL default '0',
1954 `operation` char(20) NOT NULL default '',
1955 `server` char(20) NOT NULL default '',
1956 `done` int(11) NOT NULL default '0',
1957 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1959 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1960 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1962 DROP TABLE IF EXISTS `services_throttle`;
1963 CREATE TABLE `services_throttle` (
1964 `service_type` varchar(10) NOT NULL default '',
1965 `service_count` varchar(45) default NULL,
1966 PRIMARY KEY (`service_type`)
1967 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1969 -- http://www.w3.org/International/articles/language-tags/
1972 DROP TABLE IF EXISTS language_subtag_registry;
1973 CREATE TABLE language_subtag_registry (
1975 type varchar(25), -- language-script-region-variant-extension-privateuse
1976 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1978 KEY `subtag` (`subtag`)
1979 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1981 -- TODO: add suppress_scripts
1982 -- this maps three letter codes defined in iso639.2 back to their
1983 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1984 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1985 CREATE TABLE language_rfc4646_to_iso639 (
1986 rfc4646_subtag varchar(25),
1987 iso639_2_code varchar(25),
1988 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1989 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1991 DROP TABLE IF EXISTS language_descriptions;
1992 CREATE TABLE language_descriptions (
1996 description varchar(255),
1998 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2000 -- bi-directional support, keyed by script subcode
2001 DROP TABLE IF EXISTS language_script_bidi;
2002 CREATE TABLE language_script_bidi (
2003 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2004 bidi varchar(3), -- rtl ltr
2005 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2006 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2008 -- TODO: need to map language subtags to script subtags for detection
2009 -- of bidi when script is not specified (like ar, he)
2010 DROP TABLE IF EXISTS language_script_mapping;
2011 CREATE TABLE language_script_mapping (
2012 language_subtag varchar(25),
2013 script_subtag varchar(25),
2014 KEY `language_subtag` (`language_subtag`)
2015 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2017 DROP TABLE IF EXISTS `permissions`;
2018 CREATE TABLE `permissions` (
2019 `module_bit` int(11) NOT NULL DEFAULT 0,
2020 `code` varchar(30) DEFAULT NULL,
2021 `description` varchar(255) DEFAULT NULL,
2022 PRIMARY KEY (`module_bit`, `code`),
2023 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2024 ON DELETE CASCADE ON UPDATE CASCADE
2025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2027 DROP TABLE IF EXISTS serialitems;
2028 CREATE TABLE serialitems (
2029 serialid int(11) NOT NULL,
2030 itemnumber int(11) NOT NULL,
2031 UNIQUE KEY `serialididx` (`serialid`)
2032 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2034 DROP TABLE IF EXISTS `user_permissions`;
2035 CREATE TABLE `user_permissions` (
2036 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2037 `module_bit` int(11) NOT NULL DEFAULT 0,
2038 `code` varchar(30) DEFAULT NULL,
2039 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2040 ON DELETE CASCADE ON UPDATE CASCADE,
2041 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2042 ON DELETE CASCADE ON UPDATE CASCADE
2043 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2045 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2046 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2047 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2048 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2049 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2050 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2051 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2052 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;