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 `cities`
603 DROP TABLE IF EXISTS `cities`;
604 CREATE TABLE `cities` (
605 `cityid` int(11) NOT NULL auto_increment,
606 `city_name` varchar(100) NOT NULL default '',
607 `city_zipcode` varchar(20) default NULL,
608 PRIMARY KEY (`cityid`)
609 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
612 -- Table structure for table `class_sort_rules`
615 DROP TABLE IF EXISTS `class_sort_rules`;
616 CREATE TABLE `class_sort_rules` (
617 `class_sort_rule` varchar(10) NOT NULL default '',
618 `description` mediumtext,
619 `sort_routine` varchar(30) NOT NULL default '',
620 PRIMARY KEY (`class_sort_rule`),
621 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
622 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
625 -- Table structure for table `class_sources`
628 DROP TABLE IF EXISTS `class_sources`;
629 CREATE TABLE `class_sources` (
630 `cn_source` varchar(10) NOT NULL default '',
631 `description` mediumtext,
632 `used` tinyint(4) NOT NULL default 0,
633 `class_sort_rule` varchar(10) NOT NULL default '',
634 PRIMARY KEY (`cn_source`),
635 UNIQUE KEY `cn_source_idx` (`cn_source`),
636 KEY `used_idx` (`used`),
637 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
638 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
641 -- Table structure for table `currency`
644 DROP TABLE IF EXISTS `currency`;
645 CREATE TABLE `currency` (
646 `currency` varchar(10) NOT NULL default '',
647 `symbol` varchar(5) default NULL,
648 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
649 `rate` float(7,5) default NULL,
650 PRIMARY KEY (`currency`)
651 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
654 -- Table structure for table `deletedbiblio`
657 DROP TABLE IF EXISTS `deletedbiblio`;
658 CREATE TABLE `deletedbiblio` (
659 `biblionumber` int(11) NOT NULL default 0,
660 `frameworkcode` varchar(4) NOT NULL default '',
663 `unititle` mediumtext,
665 `serial` tinyint(1) default NULL,
666 `seriestitle` mediumtext,
667 `copyrightdate` smallint(6) default NULL,
668 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
669 `datecreated` DATE NOT NULL,
670 `abstract` mediumtext,
671 PRIMARY KEY (`biblionumber`),
672 KEY `blbnoidx` (`biblionumber`)
673 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
676 -- Table structure for table `deletedbiblioitems`
679 DROP TABLE IF EXISTS `deletedbiblioitems`;
680 CREATE TABLE `deletedbiblioitems` (
681 `biblioitemnumber` int(11) NOT NULL default 0,
682 `biblionumber` int(11) NOT NULL default 0,
685 `itemtype` varchar(10) default NULL,
686 `isbn` varchar(14) default NULL,
687 `issn` varchar(9) default NULL,
688 `publicationyear` text,
689 `publishercode` varchar(255) default NULL,
690 `volumedate` date default NULL,
692 `collectiontitle` mediumtext default NULL,
693 `collectionissn` text default NULL,
694 `collectionvolume` mediumtext default NULL,
695 `editionstatement` text default NULL,
696 `editionresponsibility` text default NULL,
697 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
698 `illus` varchar(255) default NULL,
699 `pages` varchar(255) default NULL,
701 `size` varchar(255) default NULL,
702 `place` varchar(255) default NULL,
703 `lccn` varchar(25) default NULL,
705 `url` varchar(255) default NULL,
706 `cn_source` varchar(10) default NULL,
707 `cn_class` varchar(30) default NULL,
708 `cn_item` varchar(10) default NULL,
709 `cn_suffix` varchar(10) default NULL,
710 `cn_sort` varchar(30) default NULL,
711 `totalissues` int(10),
712 `marcxml` longtext NOT NULL,
713 PRIMARY KEY (`biblioitemnumber`),
714 KEY `bibinoidx` (`biblioitemnumber`),
715 KEY `bibnoidx` (`biblionumber`),
717 KEY `publishercode` (`publishercode`)
718 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
721 -- Table structure for table `deletedborrowers`
724 DROP TABLE IF EXISTS `deletedborrowers`;
725 CREATE TABLE `deletedborrowers` (
726 `borrowernumber` int(11) NOT NULL default 0,
727 `cardnumber` varchar(9) NOT NULL default '',
728 `surname` mediumtext NOT NULL,
731 `othernames` mediumtext,
733 `streetnumber` varchar(10) default NULL,
734 `streettype` varchar(50) default NULL,
735 `address` mediumtext NOT NULL,
737 `city` mediumtext NOT NULL,
738 `zipcode` varchar(25) default NULL,
741 `mobile` varchar(50) default NULL,
745 `B_streetnumber` varchar(10) default NULL,
746 `B_streettype` varchar(50) default NULL,
747 `B_address` varchar(100) default NULL,
749 `B_zipcode` varchar(25) default NULL,
751 `B_phone` mediumtext,
752 `dateofbirth` date default NULL,
753 `branchcode` varchar(10) NOT NULL default '',
754 `categorycode` varchar(2) default NULL,
755 `dateenrolled` date default NULL,
756 `dateexpiry` date default NULL,
757 `gonenoaddress` tinyint(1) default NULL,
758 `lost` tinyint(1) default NULL,
759 `debarred` tinyint(1) default NULL,
760 `contactname` mediumtext,
761 `contactfirstname` text,
763 `guarantorid` int(11) default NULL,
764 `borrowernotes` mediumtext,
765 `relationship` varchar(100) default NULL,
766 `ethnicity` varchar(50) default NULL,
767 `ethnotes` varchar(255) default NULL,
768 `sex` varchar(1) default NULL,
769 `password` varchar(30) default NULL,
770 `flags` int(11) default NULL,
771 `userid` varchar(30) default NULL,
772 `opacnote` mediumtext,
773 `contactnote` varchar(255) default NULL,
774 `sort1` varchar(80) default NULL,
775 `sort2` varchar(80) default NULL,
776 `altcontactfirstname` varchar(255) default NULL,
777 `altcontactsurname` varchar(255) default NULL,
778 `altcontactaddress1` varchar(255) default NULL,
779 `altcontactaddress2` varchar(255) default NULL,
780 `altcontactaddress3` varchar(255) default NULL,
781 `altcontactzipcode` varchar(50) default NULL,
782 `altcontactphone` varchar(50) default NULL,
783 KEY `borrowernumber` (`borrowernumber`),
784 KEY `cardnumber` (`cardnumber`)
785 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
788 -- Table structure for table `deleteditems`
791 DROP TABLE IF EXISTS `deleteditems`;
792 CREATE TABLE `deleteditems` (
793 `itemnumber` int(11) NOT NULL default 0,
794 `biblionumber` int(11) NOT NULL default 0,
795 `biblioitemnumber` int(11) NOT NULL default 0,
796 `barcode` varchar(20) default NULL,
797 `dateaccessioned` date default NULL,
798 `booksellerid` mediumtext default NULL,
799 `homebranch` varchar(10) default NULL,
800 `price` decimal(8,2) default NULL,
801 `replacementprice` decimal(8,2) default NULL,
802 `replacementpricedate` date default NULL,
803 `datelastborrowed` date default NULL,
804 `datelastseen` date default NULL,
805 `stack` tinyint(1) default NULL,
806 `notforloan` tinyint(1) NOT NULL default 0,
807 `damaged` tinyint(1) NOT NULL default 0,
808 `itemlost` tinyint(1) NOT NULL default 0,
809 `wthdrawn` tinyint(1) NOT NULL default 0,
810 `itemcallnumber` varchar(30) default NULL,
811 `issues` smallint(6) default NULL,
812 `renewals` smallint(6) default NULL,
813 `reserves` smallint(6) default NULL,
814 `restricted` tinyint(1) default NULL,
815 `itemnotes` mediumtext,
816 `holdingbranch` varchar(10) default NULL,
817 `paidfor` mediumtext,
818 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
819 `location` varchar(80) default NULL,
820 `onloan` date default NULL,
821 `cn_source` varchar(10) default NULL,
822 `cn_sort` varchar(30) default NULL,
823 `ccode` varchar(10) default NULL,
824 `materials` varchar(10) default NULL,
825 `uri` varchar(255) default NULL,
826 `itype` varchar(10) default NULL,
827 `more_subfields_xml` longtext default NULL,
828 `enumchron` varchar(80) default NULL,
829 `copynumber` smallint(6) default NULL,
831 PRIMARY KEY (`itemnumber`),
832 KEY `delitembarcodeidx` (`barcode`),
833 KEY `delitembinoidx` (`biblioitemnumber`),
834 KEY `delitembibnoidx` (`biblionumber`),
835 KEY `delhomebranch` (`homebranch`),
836 KEY `delholdingbranch` (`holdingbranch`)
837 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
840 -- Table structure for table `ethnicity`
843 DROP TABLE IF EXISTS `ethnicity`;
844 CREATE TABLE `ethnicity` (
845 `code` varchar(10) NOT NULL default '',
846 `name` varchar(255) default NULL,
848 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
851 -- Table structure for table `import_batches`
854 DROP TABLE IF EXISTS `import_batches`;
855 CREATE TABLE `import_batches` (
856 `import_batch_id` int(11) NOT NULL auto_increment,
857 `matcher_id` int(11) default NULL,
858 `template_id` int(11) default NULL,
859 `branchcode` varchar(10) default NULL,
860 `num_biblios` int(11) NOT NULL default 0,
861 `num_items` int(11) NOT NULL default 0,
862 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
863 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
864 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
865 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
866 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
867 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
868 `file_name` varchar(100),
869 `comments` mediumtext,
870 PRIMARY KEY (`import_batch_id`),
871 KEY `branchcode` (`branchcode`)
872 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
875 -- Table structure for table `import_records`
878 DROP TABLE IF EXISTS `import_records`;
879 CREATE TABLE `import_records` (
880 `import_record_id` int(11) NOT NULL auto_increment,
881 `import_batch_id` int(11) NOT NULL,
882 `branchcode` varchar(10) default NULL,
883 `record_sequence` int(11) NOT NULL default 0,
884 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
885 `import_date` DATE default NULL,
886 `marc` longblob NOT NULL,
887 `marcxml` longtext NOT NULL,
888 `marcxml_old` longtext NOT NULL,
889 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
890 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
891 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
892 `import_error` mediumtext,
893 `encoding` varchar(40) NOT NULL default '',
894 `z3950random` varchar(40) default NULL,
895 PRIMARY KEY (`import_record_id`),
896 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
897 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
898 KEY `branchcode` (`branchcode`),
899 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
900 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
903 -- Table structure for `import_record_matches`
905 DROP TABLE IF EXISTS `import_record_matches`;
906 CREATE TABLE `import_record_matches` (
907 `import_record_id` int(11) NOT NULL,
908 `candidate_match_id` int(11) NOT NULL,
909 `score` int(11) NOT NULL default 0,
910 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
911 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
912 KEY `record_score` (`import_record_id`, `score`)
913 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
916 -- Table structure for table `import_biblios`
919 DROP TABLE IF EXISTS `import_biblios`;
920 CREATE TABLE `import_biblios` (
921 `import_record_id` int(11) NOT NULL,
922 `matched_biblionumber` int(11) default NULL,
923 `control_number` varchar(25) default NULL,
924 `original_source` varchar(25) default NULL,
925 `title` varchar(128) default NULL,
926 `author` varchar(80) default NULL,
927 `isbn` varchar(14) default NULL,
928 `issn` varchar(9) default NULL,
929 `has_items` tinyint(1) NOT NULL default 0,
930 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
931 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
932 KEY `matched_biblionumber` (`matched_biblionumber`),
933 KEY `title` (`title`),
935 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
938 -- Table structure for table `import_items`
941 DROP TABLE IF EXISTS `import_items`;
942 CREATE TABLE `import_items` (
943 `import_items_id` int(11) NOT NULL auto_increment,
944 `import_record_id` int(11) NOT NULL,
945 `itemnumber` int(11) default NULL,
946 `branchcode` varchar(10) default NULL,
947 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
948 `marcxml` longtext NOT NULL,
949 `import_error` mediumtext,
950 PRIMARY KEY (`import_items_id`),
951 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
952 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
953 KEY `itemnumber` (`itemnumber`),
954 KEY `branchcode` (`branchcode`)
955 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
958 -- Table structure for table `issues`
961 DROP TABLE IF EXISTS `issues`;
962 CREATE TABLE `issues` (
963 `borrowernumber` int(11) default NULL,
964 `itemnumber` int(11) default NULL,
965 `date_due` date default NULL,
966 `branchcode` varchar(10) default NULL,
967 `issuingbranch` varchar(18) default NULL,
968 `returndate` date default NULL,
969 `lastreneweddate` date default NULL,
970 `return` varchar(4) default NULL,
971 `renewals` tinyint(4) default NULL,
972 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
973 `issuedate` date default NULL,
974 KEY `issuesborridx` (`borrowernumber`),
975 KEY `issuesitemidx` (`itemnumber`),
976 KEY `bordate` (`borrowernumber`,`timestamp`),
977 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
978 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
979 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
982 -- Table structure for table `issuingrules`
985 DROP TABLE IF EXISTS `issuingrules`;
986 CREATE TABLE `issuingrules` (
987 `categorycode` varchar(10) NOT NULL default '',
988 `itemtype` varchar(10) NOT NULL default '',
989 `restrictedtype` tinyint(1) default NULL,
990 `rentaldiscount` decimal(28,6) default NULL,
991 `reservecharge` decimal(28,6) default NULL,
992 `fine` decimal(28,6) default NULL,
993 `firstremind` int(11) default NULL,
994 `chargeperiod` int(11) default NULL,
995 `accountsent` int(11) default NULL,
996 `chargename` varchar(100) default NULL,
997 `maxissueqty` int(4) default NULL,
998 `issuelength` int(4) default NULL,
999 `branchcode` varchar(10) NOT NULL default '',
1000 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1001 KEY `categorycode` (`categorycode`),
1002 KEY `itemtype` (`itemtype`)
1003 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1006 -- Table structure for table `items`
1009 DROP TABLE IF EXISTS `items`;
1010 CREATE TABLE `items` (
1011 `itemnumber` int(11) NOT NULL auto_increment,
1012 `biblionumber` int(11) NOT NULL default 0,
1013 `biblioitemnumber` int(11) NOT NULL default 0,
1014 `barcode` varchar(20) default NULL,
1015 `dateaccessioned` date default NULL,
1016 `booksellerid` mediumtext default NULL,
1017 `homebranch` varchar(10) default NULL,
1018 `price` decimal(8,2) default NULL,
1019 `replacementprice` decimal(8,2) default NULL,
1020 `replacementpricedate` date default NULL,
1021 `datelastborrowed` date default NULL,
1022 `datelastseen` date default NULL,
1023 `stack` tinyint(1) default NULL,
1024 `notforloan` tinyint(1) NOT NULL default 0,
1025 `damaged` tinyint(1) NOT NULL default 0,
1026 `itemlost` tinyint(1) NOT NULL default 0,
1027 `wthdrawn` tinyint(1) NOT NULL default 0,
1028 `itemcallnumber` varchar(30) default NULL,
1029 `issues` smallint(6) default NULL,
1030 `renewals` smallint(6) default NULL,
1031 `reserves` smallint(6) default NULL,
1032 `restricted` tinyint(1) default NULL,
1033 `itemnotes` mediumtext,
1034 `holdingbranch` varchar(10) default NULL,
1035 `paidfor` mediumtext,
1036 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1037 `location` varchar(80) default NULL,
1038 `onloan` date default NULL,
1039 `cn_source` varchar(10) default NULL,
1040 `cn_sort` varchar(30) default NULL,
1041 `ccode` varchar(10) default NULL,
1042 `materials` varchar(10) default NULL,
1043 `uri` varchar(255) default NULL,
1044 `itype` varchar(10) default NULL,
1045 `more_subfields_xml` longtext default NULL,
1046 `enumchron` varchar(80) default NULL,
1047 `copynumber` smallint(6) default NULL,
1048 PRIMARY KEY (`itemnumber`),
1049 UNIQUE KEY `itembarcodeidx` (`barcode`),
1050 KEY `itembinoidx` (`biblioitemnumber`),
1051 KEY `itembibnoidx` (`biblionumber`),
1052 KEY `homebranch` (`homebranch`),
1053 KEY `holdingbranch` (`holdingbranch`),
1054 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1055 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1056 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1057 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1060 -- Table structure for table `itemtypes`
1063 DROP TABLE IF EXISTS `itemtypes`;
1064 CREATE TABLE `itemtypes` (
1065 `itemtype` varchar(10) NOT NULL default '',
1066 `description` mediumtext,
1067 `renewalsallowed` smallint(6) default NULL,
1068 `rentalcharge` double(16,4) default NULL,
1069 `notforloan` smallint(6) default NULL,
1070 `imageurl` varchar(200) default NULL,
1072 PRIMARY KEY (`itemtype`),
1073 UNIQUE KEY `itemtype` (`itemtype`)
1074 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1077 -- Table structure for table `labels`
1080 DROP TABLE IF EXISTS `labels`;
1081 CREATE TABLE `labels` (
1082 `labelid` int(11) NOT NULL auto_increment,
1083 `batch_id` varchar(10) NOT NULL default 1,
1084 `itemnumber` varchar(100) NOT NULL default '',
1085 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1086 PRIMARY KEY (`labelid`)
1087 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1090 -- Table structure for table `labels_conf`
1093 DROP TABLE IF EXISTS `labels_conf`;
1094 CREATE TABLE `labels_conf` (
1095 `id` int(4) NOT NULL auto_increment,
1096 `barcodetype` char(100) default '',
1097 `title` int(1) default '0',
1098 `subtitle` int(1) default '0',
1099 `itemtype` int(1) default '0',
1100 `barcode` int(1) default '0',
1101 `dewey` int(1) default '0',
1102 `classification` int(1) default NULL,
1103 `subclass` int(1) default '0',
1104 `itemcallnumber` int(1) default '0',
1105 `author` int(1) default '0',
1106 `issn` int(1) default '0',
1107 `isbn` int(1) default '0',
1108 `startlabel` int(2) NOT NULL default '1',
1109 `printingtype` char(32) default 'BAR',
1110 `formatstring` varchar(64) default NULL,
1111 `layoutname` char(20) NOT NULL default 'TEST',
1112 `guidebox` int(1) default '0',
1113 `active` tinyint(1) default '1',
1114 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1115 `ccode` char(4) collate utf8_unicode_ci default NULL,
1116 `callnum_split` int(1) default NULL,
1117 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1119 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1122 -- Table structure for table `labels_profile`
1125 DROP TABLE IF EXISTS `labels_profile`;
1126 CREATE TABLE `labels_profile` (
1127 `tmpl_id` int(4) NOT NULL,
1128 `prof_id` int(4) NOT NULL,
1129 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1130 UNIQUE KEY `prof_id` (`prof_id`)
1131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1134 -- Table structure for table `labels_templates`
1137 DROP TABLE IF EXISTS `labels_templates`;
1138 CREATE TABLE `labels_templates` (
1139 `tmpl_id` int(4) NOT NULL auto_increment,
1140 `tmpl_code` char(100) default '',
1141 `tmpl_desc` char(100) default '',
1142 `page_width` float default '0',
1143 `page_height` float default '0',
1144 `label_width` float default '0',
1145 `label_height` float default '0',
1146 `topmargin` float default '0',
1147 `leftmargin` float default '0',
1148 `cols` int(2) default '0',
1149 `rows` int(2) default '0',
1150 `colgap` float default '0',
1151 `rowgap` float default '0',
1152 `active` int(1) default NULL,
1153 `units` char(20) default 'PX',
1154 `fontsize` int(4) NOT NULL default '3',
1155 `font` char(10) NOT NULL default 'TR',
1156 PRIMARY KEY (`tmpl_id`)
1157 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1160 -- Table structure for table `letter`
1163 DROP TABLE IF EXISTS `letter`;
1164 CREATE TABLE `letter` (
1165 `module` varchar(20) NOT NULL default '',
1166 `code` varchar(20) NOT NULL default '',
1167 `name` varchar(100) NOT NULL default '',
1168 `title` varchar(200) NOT NULL default '',
1170 PRIMARY KEY (`module`,`code`)
1171 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1174 -- Table structure for table `marc_subfield_structure`
1177 DROP TABLE IF EXISTS `marc_subfield_structure`;
1178 CREATE TABLE `marc_subfield_structure` (
1179 `tagfield` varchar(3) NOT NULL default '',
1180 `tagsubfield` varchar(1) NOT NULL default '',
1181 `liblibrarian` varchar(255) NOT NULL default '',
1182 `libopac` varchar(255) NOT NULL default '',
1183 `repeatable` tinyint(4) NOT NULL default 0,
1184 `mandatory` tinyint(4) NOT NULL default 0,
1185 `kohafield` varchar(40) default NULL,
1186 `tab` tinyint(1) default NULL,
1187 `authorised_value` varchar(20) default NULL,
1188 `authtypecode` varchar(20) default NULL,
1189 `value_builder` varchar(80) default NULL,
1190 `isurl` tinyint(1) default NULL,
1191 `hidden` tinyint(1) default NULL,
1192 `frameworkcode` varchar(4) NOT NULL default '',
1193 `seealso` varchar(1100) default NULL,
1194 `link` varchar(80) default NULL,
1195 `defaultvalue` text default NULL,
1196 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1197 KEY `kohafield_2` (`kohafield`),
1198 KEY `tab` (`frameworkcode`,`tab`),
1199 KEY `kohafield` (`frameworkcode`,`kohafield`)
1200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1203 -- Table structure for table `marc_tag_structure`
1206 DROP TABLE IF EXISTS `marc_tag_structure`;
1207 CREATE TABLE `marc_tag_structure` (
1208 `tagfield` varchar(3) NOT NULL default '',
1209 `liblibrarian` varchar(255) NOT NULL default '',
1210 `libopac` varchar(255) NOT NULL default '',
1211 `repeatable` tinyint(4) NOT NULL default 0,
1212 `mandatory` tinyint(4) NOT NULL default 0,
1213 `authorised_value` varchar(10) default NULL,
1214 `frameworkcode` varchar(4) NOT NULL default '',
1215 PRIMARY KEY (`frameworkcode`,`tagfield`)
1216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1219 -- Table structure for table `marc_matchers`
1222 DROP TABLE IF EXISTS `marc_matchers`;
1223 CREATE TABLE `marc_matchers` (
1224 `matcher_id` int(11) NOT NULL auto_increment,
1225 `code` varchar(10) NOT NULL default '',
1226 `description` varchar(255) NOT NULL default '',
1227 `record_type` varchar(10) NOT NULL default 'biblio',
1228 `threshold` int(11) NOT NULL default 0,
1229 PRIMARY KEY (`matcher_id`),
1230 KEY `code` (`code`),
1231 KEY `record_type` (`record_type`)
1232 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1235 -- Table structure for table `matchpoints`
1237 DROP TABLE IF EXISTS `matchpoints`;
1238 CREATE TABLE `matchpoints` (
1239 `matcher_id` int(11) NOT NULL,
1240 `matchpoint_id` int(11) NOT NULL auto_increment,
1241 `search_index` varchar(30) NOT NULL default '',
1242 `score` int(11) NOT NULL default 0,
1243 PRIMARY KEY (`matchpoint_id`),
1244 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1245 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1246 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1250 -- Table structure for table `matchpoint_components`
1252 DROP TABLE IF EXISTS `matchpoint_components`;
1253 CREATE TABLE `matchpoint_components` (
1254 `matchpoint_id` int(11) NOT NULL,
1255 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1256 sequence int(11) NOT NULL default 0,
1257 tag varchar(3) NOT NULL default '',
1258 subfields varchar(40) NOT NULL default '',
1259 offset int(4) NOT NULL default 0,
1260 length int(4) NOT NULL default 0,
1261 PRIMARY KEY (`matchpoint_component_id`),
1262 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1263 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1264 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1268 -- Table structure for table `matcher_component_norms`
1270 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1271 CREATE TABLE `matchpoint_component_norms` (
1272 `matchpoint_component_id` int(11) NOT NULL,
1273 `sequence` int(11) NOT NULL default 0,
1274 `norm_routine` varchar(50) NOT NULL default '',
1275 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1276 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1277 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1278 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1281 -- Table structure for table `matcher_matchpoints`
1283 DROP TABLE IF EXISTS `matcher_matchpoints`;
1284 CREATE TABLE `matcher_matchpoints` (
1285 `matcher_id` int(11) NOT NULL,
1286 `matchpoint_id` int(11) NOT NULL,
1287 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1288 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1289 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1290 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1291 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1294 -- Table structure for table `matchchecks`
1296 DROP TABLE IF EXISTS `matchchecks`;
1297 CREATE TABLE `matchchecks` (
1298 `matcher_id` int(11) NOT NULL,
1299 `matchcheck_id` int(11) NOT NULL auto_increment,
1300 `source_matchpoint_id` int(11) NOT NULL,
1301 `target_matchpoint_id` int(11) NOT NULL,
1302 PRIMARY KEY (`matchcheck_id`),
1303 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1304 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1305 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1306 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1307 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1308 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1309 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1312 -- Table structure for table `notifys`
1315 DROP TABLE IF EXISTS `notifys`;
1316 CREATE TABLE `notifys` (
1317 `notify_id` int(11) NOT NULL default 0,
1318 `borrowernumber` int(11) NOT NULL default 0,
1319 `itemnumber` int(11) NOT NULL default 0,
1320 `notify_date` date default NULL,
1321 `notify_send_date` date default NULL,
1322 `notify_level` int(1) NOT NULL default 0,
1323 `method` varchar(20) NOT NULL default ''
1324 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1327 -- Table structure for table `nozebra`
1330 DROP TABLE IF EXISTS `nozebra`;
1331 CREATE TABLE `nozebra` (
1332 `server` varchar(20) NOT NULL,
1333 `indexname` varchar(40) NOT NULL,
1334 `value` varchar(250) NOT NULL,
1335 `biblionumbers` longtext NOT NULL,
1336 KEY `indexname` (`server`,`indexname`),
1337 KEY `value` (`server`,`value`))
1338 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1341 -- Table structure for table `old_issues`
1344 DROP TABLE IF EXISTS `old_issues`;
1345 CREATE TABLE `old_issues` (
1346 `borrowernumber` int(11) default NULL,
1347 `itemnumber` int(11) default NULL,
1348 `date_due` date default NULL,
1349 `branchcode` varchar(10) default NULL,
1350 `issuingbranch` varchar(18) default NULL,
1351 `returndate` date default NULL,
1352 `lastreneweddate` date default NULL,
1353 `return` varchar(4) default NULL,
1354 `renewals` tinyint(4) default NULL,
1355 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1356 `issuedate` date default NULL,
1357 KEY `old_issuesborridx` (`borrowernumber`),
1358 KEY `old_issuesitemidx` (`itemnumber`),
1359 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1360 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1361 ON DELETE SET NULL ON UPDATE SET NULL,
1362 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1363 ON DELETE SET NULL ON UPDATE SET NULL
1364 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1367 -- Table structure for table `old_reserves`
1369 DROP TABLE IF EXISTS `old_reserves`;
1370 CREATE TABLE `old_reserves` (
1371 `borrowernumber` int(11) default NULL,
1372 `reservedate` date default NULL,
1373 `biblionumber` int(11) default NULL,
1374 `constrainttype` varchar(1) default NULL,
1375 `branchcode` varchar(10) default NULL,
1376 `notificationdate` date default NULL,
1377 `reminderdate` date default NULL,
1378 `cancellationdate` date default NULL,
1379 `reservenotes` mediumtext,
1380 `priority` smallint(6) default NULL,
1381 `found` varchar(1) default NULL,
1382 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1383 `itemnumber` int(11) default NULL,
1384 `waitingdate` date default NULL,
1385 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1386 KEY `old_reserves_biblionumber` (`biblionumber`),
1387 KEY `old_reserves_itemnumber` (`itemnumber`),
1388 KEY `old_reserves_branchcode` (`branchcode`),
1389 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1390 ON DELETE SET NULL ON UPDATE SET NULL,
1391 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1392 ON DELETE SET NULL ON UPDATE SET NULL,
1393 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1394 ON DELETE SET NULL ON UPDATE SET NULL
1395 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1398 -- Table structure for table `opac_news`
1401 DROP TABLE IF EXISTS `opac_news`;
1402 CREATE TABLE `opac_news` (
1403 `idnew` int(10) unsigned NOT NULL auto_increment,
1404 `title` varchar(250) NOT NULL default '',
1405 `new` text NOT NULL,
1406 `lang` varchar(25) NOT NULL default '',
1407 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1408 `expirationdate` date default NULL,
1409 `number` int(11) default NULL,
1410 PRIMARY KEY (`idnew`)
1411 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1414 -- Table structure for table `overduerules`
1417 DROP TABLE IF EXISTS `overduerules`;
1418 CREATE TABLE `overduerules` (
1419 `branchcode` varchar(10) NOT NULL default '',
1420 `categorycode` varchar(2) NOT NULL default '',
1421 `delay1` int(4) default 0,
1422 `letter1` varchar(20) default NULL,
1423 `debarred1` varchar(1) default 0,
1424 `delay2` int(4) default 0,
1425 `debarred2` varchar(1) default 0,
1426 `letter2` varchar(20) default NULL,
1427 `delay3` int(4) default 0,
1428 `letter3` varchar(20) default NULL,
1429 `debarred3` int(1) default 0,
1430 PRIMARY KEY (`branchcode`,`categorycode`)
1431 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1434 -- Table structure for table `patroncards`
1437 DROP TABLE IF EXISTS `patroncards`;
1438 CREATE TABLE `patroncards` (
1439 `cardid` int(11) NOT NULL auto_increment,
1440 `batch_id` varchar(10) NOT NULL default '1',
1441 `borrowernumber` int(11) NOT NULL,
1442 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1443 PRIMARY KEY (`cardid`),
1444 KEY `patroncards_ibfk_1` (`borrowernumber`),
1445 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1446 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1449 -- Table structure for table `patronimage`
1452 DROP TABLE IF EXISTS `patronimage`;
1453 CREATE TABLE `patronimage` (
1454 `cardnumber` varchar(16) NOT NULL,
1455 `mimetype` varchar(15) NOT NULL,
1456 `imagefile` mediumblob NOT NULL,
1457 PRIMARY KEY (`cardnumber`),
1458 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1459 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1462 -- Table structure for table `printers`
1465 DROP TABLE IF EXISTS `printers`;
1466 CREATE TABLE `printers` (
1467 `printername` varchar(40) NOT NULL default '',
1468 `printqueue` varchar(20) default NULL,
1469 `printtype` varchar(20) default NULL,
1470 PRIMARY KEY (`printername`)
1471 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1474 -- Table structure for table `printers_profile`
1477 DROP TABLE IF EXISTS `printers_profile`;
1478 CREATE TABLE `printers_profile` (
1479 `prof_id` int(4) NOT NULL auto_increment,
1480 `printername` varchar(40) NOT NULL,
1481 `tmpl_id` int(4) NOT NULL,
1482 `paper_bin` varchar(20) NOT NULL,
1483 `offset_horz` float default NULL,
1484 `offset_vert` float default NULL,
1485 `creep_horz` float default NULL,
1486 `creep_vert` float default NULL,
1487 `unit` char(20) NOT NULL default 'POINT',
1488 PRIMARY KEY (`prof_id`),
1489 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1490 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1491 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1494 -- Table structure for table `repeatable_holidays`
1497 DROP TABLE IF EXISTS `repeatable_holidays`;
1498 CREATE TABLE `repeatable_holidays` (
1499 `id` int(11) NOT NULL auto_increment,
1500 `branchcode` varchar(10) NOT NULL default '',
1501 `weekday` smallint(6) default NULL,
1502 `day` smallint(6) default NULL,
1503 `month` smallint(6) default NULL,
1504 `title` varchar(50) NOT NULL default '',
1505 `description` text NOT NULL,
1507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1510 -- Table structure for table `reports_dictionary`
1513 DROP TABLE IF EXISTS `reports_dictionary`;
1514 CREATE TABLE reports_dictionary (
1515 `id` int(11) NOT NULL auto_increment,
1516 `name` varchar(255) default NULL,
1518 `date_created` datetime default NULL,
1519 `date_modified` datetime default NULL,
1521 `area` int(11) default NULL,
1523 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1526 -- Table structure for table `reserveconstraints`
1529 DROP TABLE IF EXISTS `reserveconstraints`;
1530 CREATE TABLE `reserveconstraints` (
1531 `borrowernumber` int(11) NOT NULL default 0,
1532 `reservedate` date default NULL,
1533 `biblionumber` int(11) NOT NULL default 0,
1534 `biblioitemnumber` int(11) default NULL,
1535 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1539 -- Table structure for table `reserves`
1542 DROP TABLE IF EXISTS `reserves`;
1543 CREATE TABLE `reserves` (
1544 `borrowernumber` int(11) NOT NULL default 0,
1545 `reservedate` date default NULL,
1546 `biblionumber` int(11) NOT NULL default 0,
1547 `constrainttype` varchar(1) default NULL,
1548 `branchcode` varchar(10) default NULL,
1549 `notificationdate` date default NULL,
1550 `reminderdate` date default NULL,
1551 `cancellationdate` date default NULL,
1552 `reservenotes` mediumtext,
1553 `priority` smallint(6) default NULL,
1554 `found` varchar(1) default NULL,
1555 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1556 `itemnumber` int(11) default NULL,
1557 `waitingdate` date default NULL,
1558 KEY `borrowernumber` (`borrowernumber`),
1559 KEY `biblionumber` (`biblionumber`),
1560 KEY `itemnumber` (`itemnumber`),
1561 KEY `branchcode` (`branchcode`),
1562 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1563 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1564 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1565 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1566 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1569 -- Table structure for table `reviews`
1572 DROP TABLE IF EXISTS `reviews`;
1573 CREATE TABLE `reviews` (
1574 `reviewid` int(11) NOT NULL auto_increment,
1575 `borrowernumber` int(11) default NULL,
1576 `biblionumber` int(11) default NULL,
1578 `approved` tinyint(4) default NULL,
1579 `datereviewed` datetime default NULL,
1580 PRIMARY KEY (`reviewid`)
1581 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1584 -- Table structure for table `roadtype`
1587 DROP TABLE IF EXISTS `roadtype`;
1588 CREATE TABLE `roadtype` (
1589 `roadtypeid` int(11) NOT NULL auto_increment,
1590 `road_type` varchar(100) NOT NULL default '',
1591 PRIMARY KEY (`roadtypeid`)
1592 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1595 -- Table structure for table `saved_sql`
1598 DROP TABLE IF EXISTS `saved_sql`;
1599 CREATE TABLE saved_sql (
1600 `id` int(11) NOT NULL auto_increment,
1601 `borrowernumber` int(11) default NULL,
1602 `date_created` datetime default NULL,
1603 `last_modified` datetime default NULL,
1605 `last_run` datetime default NULL,
1606 `report_name` varchar(255) default NULL,
1607 `type` varchar(255) default NULL,
1610 KEY boridx (`borrowernumber`)
1611 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1615 -- Table structure for `saved_reports`
1618 DROP TABLE IF EXISTS `saved_reports`;
1619 CREATE TABLE saved_reports (
1620 `id` int(11) NOT NULL auto_increment,
1621 `report_id` int(11) default NULL,
1623 `date_run` datetime default NULL,
1625 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1629 -- Table structure for table `serial`
1632 DROP TABLE IF EXISTS `serial`;
1633 CREATE TABLE `serial` (
1634 `serialid` int(11) NOT NULL auto_increment,
1635 `biblionumber` varchar(100) NOT NULL default '',
1636 `subscriptionid` varchar(100) NOT NULL default '',
1637 `serialseq` varchar(100) NOT NULL default '',
1638 `status` tinyint(4) NOT NULL default 0,
1639 `planneddate` date default NULL,
1641 `publisheddate` date default NULL,
1642 `itemnumber` text default NULL,
1643 `claimdate` date default NULL,
1644 `routingnotes` text,
1645 PRIMARY KEY (`serialid`)
1646 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1649 -- Table structure for table `sessions`
1652 DROP TABLE IF EXISTS sessions;
1653 CREATE TABLE sessions (
1654 `id` varchar(32) NOT NULL,
1655 `a_session` text NOT NULL,
1657 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1660 -- Table structure for table `special_holidays`
1663 DROP TABLE IF EXISTS `special_holidays`;
1664 CREATE TABLE `special_holidays` (
1665 `id` int(11) NOT NULL auto_increment,
1666 `branchcode` varchar(10) NOT NULL default '',
1667 `day` smallint(6) NOT NULL default 0,
1668 `month` smallint(6) NOT NULL default 0,
1669 `year` smallint(6) NOT NULL default 0,
1670 `isexception` smallint(1) NOT NULL default 1,
1671 `title` varchar(50) NOT NULL default '',
1672 `description` text NOT NULL,
1674 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1677 -- Table structure for table `statistics`
1680 DROP TABLE IF EXISTS `statistics`;
1681 CREATE TABLE `statistics` (
1682 `datetime` datetime default NULL,
1683 `branch` varchar(10) default NULL,
1684 `proccode` varchar(4) default NULL,
1685 `value` double(16,4) default NULL,
1686 `type` varchar(16) default NULL,
1688 `usercode` varchar(10) default NULL,
1689 `itemnumber` int(11) default NULL,
1690 `itemtype` varchar(10) default NULL,
1691 `borrowernumber` int(11) default NULL,
1692 `associatedborrower` int(11) default NULL,
1693 KEY `timeidx` (`datetime`)
1694 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1697 -- Table structure for table `stopwords`
1700 DROP TABLE IF EXISTS `stopwords`;
1701 CREATE TABLE `stopwords` (
1702 `word` varchar(255) default NULL
1703 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1706 -- Table structure for table `subscription`
1709 DROP TABLE IF EXISTS `subscription`;
1710 CREATE TABLE `subscription` (
1711 `biblionumber` int(11) NOT NULL default 0,
1712 `subscriptionid` int(11) NOT NULL auto_increment,
1713 `librarian` varchar(100) default '',
1714 `startdate` date default NULL,
1715 `aqbooksellerid` int(11) default 0,
1716 `cost` int(11) default 0,
1717 `aqbudgetid` int(11) default 0,
1718 `weeklength` int(11) default 0,
1719 `monthlength` int(11) default 0,
1720 `numberlength` int(11) default 0,
1721 `periodicity` tinyint(4) default 0,
1722 `dow` varchar(100) default '',
1723 `numberingmethod` varchar(100) default '',
1725 `status` varchar(100) NOT NULL default '',
1726 `add1` int(11) default 0,
1727 `every1` int(11) default 0,
1728 `whenmorethan1` int(11) default 0,
1729 `setto1` int(11) default NULL,
1730 `lastvalue1` int(11) default NULL,
1731 `add2` int(11) default 0,
1732 `every2` int(11) default 0,
1733 `whenmorethan2` int(11) default 0,
1734 `setto2` int(11) default NULL,
1735 `lastvalue2` int(11) default NULL,
1736 `add3` int(11) default 0,
1737 `every3` int(11) default 0,
1738 `innerloop1` int(11) default 0,
1739 `innerloop2` int(11) default 0,
1740 `innerloop3` int(11) default 0,
1741 `whenmorethan3` int(11) default 0,
1742 `setto3` int(11) default NULL,
1743 `lastvalue3` int(11) default NULL,
1744 `issuesatonce` tinyint(3) NOT NULL default 1,
1745 `firstacquidate` date default NULL,
1746 `manualhistory` tinyint(1) NOT NULL default 0,
1747 `irregularity` text,
1748 `letter` varchar(20) default NULL,
1749 `numberpattern` tinyint(3) default 0,
1750 `distributedto` text,
1751 `internalnotes` longtext,
1753 `branchcode` varchar(10) NOT NULL default '',
1754 `hemisphere` tinyint(3) default 0,
1755 `lastbranch` varchar(10),
1756 PRIMARY KEY (`subscriptionid`)
1757 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1760 -- Table structure for table `subscriptionhistory`
1763 DROP TABLE IF EXISTS `subscriptionhistory`;
1764 CREATE TABLE `subscriptionhistory` (
1765 `biblionumber` int(11) NOT NULL default 0,
1766 `subscriptionid` int(11) NOT NULL default 0,
1767 `histstartdate` date default NULL,
1768 `enddate` date default NULL,
1769 `missinglist` longtext NOT NULL,
1770 `recievedlist` longtext NOT NULL,
1771 `opacnote` varchar(150) NOT NULL default '',
1772 `librariannote` varchar(150) NOT NULL default '',
1773 PRIMARY KEY (`subscriptionid`),
1774 KEY `biblionumber` (`biblionumber`)
1775 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1778 -- Table structure for table `subscriptionroutinglist`
1781 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1782 CREATE TABLE `subscriptionroutinglist` (
1783 `routingid` int(11) NOT NULL auto_increment,
1784 `borrowernumber` int(11) default NULL,
1785 `ranking` int(11) default NULL,
1786 `subscriptionid` int(11) default NULL,
1787 PRIMARY KEY (`routingid`)
1788 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1791 -- Table structure for table `suggestions`
1794 DROP TABLE IF EXISTS `suggestions`;
1795 CREATE TABLE `suggestions` (
1796 `suggestionid` int(8) NOT NULL auto_increment,
1797 `suggestedby` int(11) NOT NULL default 0,
1798 `managedby` int(11) default NULL,
1799 `STATUS` varchar(10) NOT NULL default '',
1801 `author` varchar(80) default NULL,
1802 `title` varchar(80) default NULL,
1803 `copyrightdate` smallint(6) default NULL,
1804 `publishercode` varchar(255) default NULL,
1805 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1806 `volumedesc` varchar(255) default NULL,
1807 `publicationyear` smallint(6) default 0,
1808 `place` varchar(255) default NULL,
1809 `isbn` varchar(10) default NULL,
1810 `mailoverseeing` smallint(1) default 0,
1811 `biblionumber` int(11) default NULL,
1813 PRIMARY KEY (`suggestionid`),
1814 KEY `suggestedby` (`suggestedby`),
1815 KEY `managedby` (`managedby`)
1816 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1819 -- Table structure for table `systempreferences`
1822 DROP TABLE IF EXISTS `systempreferences`;
1823 CREATE TABLE `systempreferences` (
1824 `variable` varchar(50) NOT NULL default '',
1826 `options` mediumtext,
1828 `type` varchar(20) default NULL,
1829 PRIMARY KEY (`variable`)
1830 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1833 -- Table structure for table `tags`
1836 DROP TABLE IF EXISTS `tags`;
1837 CREATE TABLE `tags` (
1838 `entry` varchar(255) NOT NULL default '',
1839 `weight` bigint(20) NOT NULL default 0,
1840 PRIMARY KEY (`entry`)
1841 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1844 -- Table structure for table `tags_all`
1847 CREATE TABLE `tags_all` (
1848 `tag_id` int(11) NOT NULL auto_increment,
1849 `borrowernumber` int(11) NOT NULL,
1850 `biblionumber` int(11) NOT NULL,
1851 `term` varchar(255) NOT NULL,
1852 `language` int(4) default NULL,
1853 `date_created` datetime NOT NULL,
1854 PRIMARY KEY (`tag_id`),
1855 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1856 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1857 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1858 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1859 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1860 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1861 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1864 -- Table structure for table `tags_approval`
1867 CREATE TABLE `tags_approval` (
1868 `term` varchar(255) NOT NULL,
1869 `approved` int(1) NOT NULL default '0',
1870 `date_approved` datetime default NULL,
1871 `approved_by` int(11) default NULL,
1872 `weight_total` int(9) NOT NULL default '1',
1873 PRIMARY KEY (`term`),
1874 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1875 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1876 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1877 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1880 -- Table structure for table `tags_index`
1883 CREATE TABLE `tags_index` (
1884 `term` varchar(255) NOT NULL,
1885 `biblionumber` int(11) NOT NULL,
1886 `weight` int(9) NOT NULL default '1',
1887 PRIMARY KEY (`term`,`biblionumber`),
1888 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1889 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1890 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1891 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1892 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1893 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1896 -- Table structure for table `userflags`
1899 DROP TABLE IF EXISTS `userflags`;
1900 CREATE TABLE `userflags` (
1901 `bit` int(11) NOT NULL default 0,
1902 `flag` varchar(30) default NULL,
1903 `flagdesc` varchar(255) default NULL,
1904 `defaulton` int(11) default NULL,
1906 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1909 -- Table structure for table `virtualshelves`
1912 DROP TABLE IF EXISTS `virtualshelves`;
1913 CREATE TABLE `virtualshelves` (
1914 `shelfnumber` int(11) NOT NULL auto_increment,
1915 `shelfname` varchar(255) default NULL,
1916 `owner` varchar(80) default NULL,
1917 `category` varchar(1) default NULL,
1918 `sortfield` varchar(16) default NULL,
1919 PRIMARY KEY (`shelfnumber`)
1920 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1923 -- Table structure for table `virtualshelfcontents`
1926 DROP TABLE IF EXISTS `virtualshelfcontents`;
1927 CREATE TABLE `virtualshelfcontents` (
1928 `shelfnumber` int(11) NOT NULL default 0,
1929 `biblionumber` int(11) NOT NULL default 0,
1930 `flags` int(11) default NULL,
1931 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1932 KEY `shelfnumber` (`shelfnumber`),
1933 KEY `biblionumber` (`biblionumber`),
1934 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1935 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1936 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1939 -- Table structure for table `z3950servers`
1942 DROP TABLE IF EXISTS `z3950servers`;
1943 CREATE TABLE `z3950servers` (
1944 `host` varchar(255) default NULL,
1945 `port` int(11) default NULL,
1946 `db` varchar(255) default NULL,
1947 `userid` varchar(255) default NULL,
1948 `password` varchar(255) default NULL,
1950 `id` int(11) NOT NULL auto_increment,
1951 `checked` smallint(6) default NULL,
1952 `rank` int(11) default NULL,
1953 `syntax` varchar(80) default NULL,
1955 `position` enum('primary','secondary','') NOT NULL default 'primary',
1956 `type` enum('zed','opensearch') NOT NULL default 'zed',
1957 `encoding` text default NULL,
1958 `description` text NOT NULL,
1960 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1963 -- Table structure for table `zebraqueue`
1966 DROP TABLE IF EXISTS `zebraqueue`;
1967 CREATE TABLE `zebraqueue` (
1968 `id` int(11) NOT NULL auto_increment,
1969 `biblio_auth_number` int(11) NOT NULL default '0',
1970 `operation` char(20) NOT NULL default '',
1971 `server` char(20) NOT NULL default '',
1972 `done` int(11) NOT NULL default '0',
1973 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1975 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1976 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1978 DROP TABLE IF EXISTS `services_throttle`;
1979 CREATE TABLE `services_throttle` (
1980 `service_type` varchar(10) NOT NULL default '',
1981 `service_count` varchar(45) default NULL,
1982 PRIMARY KEY (`service_type`)
1983 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1985 -- http://www.w3.org/International/articles/language-tags/
1988 DROP TABLE IF EXISTS language_subtag_registry;
1989 CREATE TABLE language_subtag_registry (
1991 type varchar(25), -- language-script-region-variant-extension-privateuse
1992 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1994 KEY `subtag` (`subtag`)
1995 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1997 -- TODO: add suppress_scripts
1998 -- this maps three letter codes defined in iso639.2 back to their
1999 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2000 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2001 CREATE TABLE language_rfc4646_to_iso639 (
2002 rfc4646_subtag varchar(25),
2003 iso639_2_code varchar(25),
2004 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2005 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2007 DROP TABLE IF EXISTS language_descriptions;
2008 CREATE TABLE language_descriptions (
2012 description varchar(255),
2014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2016 -- bi-directional support, keyed by script subcode
2017 DROP TABLE IF EXISTS language_script_bidi;
2018 CREATE TABLE language_script_bidi (
2019 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2020 bidi varchar(3), -- rtl ltr
2021 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2022 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2024 -- TODO: need to map language subtags to script subtags for detection
2025 -- of bidi when script is not specified (like ar, he)
2026 DROP TABLE IF EXISTS language_script_mapping;
2027 CREATE TABLE language_script_mapping (
2028 language_subtag varchar(25),
2029 script_subtag varchar(25),
2030 KEY `language_subtag` (`language_subtag`)
2031 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2033 DROP TABLE IF EXISTS `permissions`;
2034 CREATE TABLE `permissions` (
2035 `module_bit` int(11) NOT NULL DEFAULT 0,
2036 `code` varchar(30) DEFAULT NULL,
2037 `description` varchar(255) DEFAULT NULL,
2038 PRIMARY KEY (`module_bit`, `code`),
2039 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2040 ON DELETE CASCADE ON UPDATE CASCADE
2041 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2043 DROP TABLE IF EXISTS serialitems;
2044 CREATE TABLE serialitems (
2045 serialid int(11) NOT NULL,
2046 itemnumber int(11) NOT NULL,
2047 UNIQUE KEY `serialididx` (`serialid`)
2048 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2050 DROP TABLE IF EXISTS `user_permissions`;
2051 CREATE TABLE `user_permissions` (
2052 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2053 `module_bit` int(11) NOT NULL DEFAULT 0,
2054 `code` varchar(30) DEFAULT NULL,
2055 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2056 ON DELETE CASCADE ON UPDATE CASCADE,
2057 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2058 ON DELETE CASCADE ON UPDATE CASCADE
2059 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2061 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2062 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2063 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2064 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2065 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2066 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2067 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2068 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;