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` varchar(10) 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,
841 PRIMARY KEY (`itemnumber`),
842 KEY `delitembarcodeidx` (`barcode`),
843 KEY `delitembinoidx` (`biblioitemnumber`),
844 KEY `delitembibnoidx` (`biblionumber`),
845 KEY `delhomebranch` (`homebranch`),
846 KEY `delholdingbranch` (`holdingbranch`)
847 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
850 -- Table structure for table `ethnicity`
853 DROP TABLE IF EXISTS `ethnicity`;
854 CREATE TABLE `ethnicity` (
855 `code` varchar(10) NOT NULL default '',
856 `name` varchar(255) default NULL,
858 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
861 -- Table structure for table `import_batches`
864 DROP TABLE IF EXISTS `import_batches`;
865 CREATE TABLE `import_batches` (
866 `import_batch_id` int(11) NOT NULL auto_increment,
867 `matcher_id` int(11) default NULL,
868 `template_id` int(11) default NULL,
869 `branchcode` varchar(10) default NULL,
870 `num_biblios` int(11) NOT NULL default 0,
871 `num_items` int(11) NOT NULL default 0,
872 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
873 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
874 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
875 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
876 `file_name` varchar(100),
877 `comments` mediumtext,
878 PRIMARY KEY (`import_batch_id`),
879 KEY `branchcode` (`branchcode`)
880 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
883 -- Table structure for table `import_records`
886 DROP TABLE IF EXISTS `import_records`;
887 CREATE TABLE `import_records` (
888 `import_record_id` int(11) NOT NULL auto_increment,
889 `import_batch_id` int(11) NOT NULL,
890 `branchcode` varchar(10) default NULL,
891 `record_sequence` int(11) NOT NULL default 0,
892 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
893 `import_date` DATE default NULL,
894 `marc` longblob NOT NULL,
895 `marcxml` longtext NOT NULL,
896 `marcxml_old` longtext NOT NULL,
897 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
898 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
899 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
900 `import_error` mediumtext,
901 `encoding` varchar(40) NOT NULL default '',
902 `z3950random` varchar(40) default NULL,
903 PRIMARY KEY (`import_record_id`),
904 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
905 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
906 KEY `branchcode` (`branchcode`),
907 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
908 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
911 -- Table structure for `import_record_matches`
913 DROP TABLE IF EXISTS `import_record_matches`;
914 CREATE TABLE `import_record_matches` (
915 `import_record_id` int(11) NOT NULL,
916 `candidate_match_id` int(11) NOT NULL,
917 `score` int(11) NOT NULL default 0,
918 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
919 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
920 KEY `record_score` (`import_record_id`, `score`)
921 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
924 -- Table structure for table `import_biblios`
927 DROP TABLE IF EXISTS `import_biblios`;
928 CREATE TABLE `import_biblios` (
929 `import_record_id` int(11) NOT NULL,
930 `matched_biblionumber` int(11) default NULL,
931 `control_number` varchar(25) default NULL,
932 `original_source` varchar(25) default NULL,
933 `title` varchar(128) default NULL,
934 `author` varchar(80) default NULL,
935 `isbn` varchar(14) default NULL,
936 `issn` varchar(9) default NULL,
937 `has_items` tinyint(1) NOT NULL default 0,
938 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
939 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
940 KEY `matched_biblionumber` (`matched_biblionumber`),
941 KEY `title` (`title`),
943 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
946 -- Table structure for table `import_items`
949 DROP TABLE IF EXISTS `import_items`;
950 CREATE TABLE `import_items` (
951 `import_items_id` int(11) NOT NULL auto_increment,
952 `import_record_id` int(11) NOT NULL,
953 `itemnumber` int(11) default NULL,
954 `branchcode` varchar(10) default NULL,
955 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
956 `marcxml` longtext NOT NULL,
957 `import_error` mediumtext,
958 PRIMARY KEY (`import_items_id`),
959 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
960 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
961 KEY `itemnumber` (`itemnumber`),
962 KEY `branchcode` (`branchcode`)
963 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
966 -- Table structure for table `issues`
969 DROP TABLE IF EXISTS `issues`;
970 CREATE TABLE `issues` (
971 `borrowernumber` int(11) default NULL,
972 `itemnumber` int(11) default NULL,
973 `date_due` date default NULL,
974 `branchcode` varchar(10) default NULL,
975 `issuingbranch` varchar(18) default NULL,
976 `returndate` date default NULL,
977 `lastreneweddate` date default NULL,
978 `return` varchar(4) default NULL,
979 `renewals` tinyint(4) default NULL,
980 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
981 `issuedate` date default NULL,
982 KEY `issuesborridx` (`borrowernumber`),
983 KEY `issuesitemidx` (`itemnumber`),
984 KEY `bordate` (`borrowernumber`,`timestamp`),
985 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
986 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
987 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
990 -- Table structure for table `issuingrules`
993 DROP TABLE IF EXISTS `issuingrules`;
994 CREATE TABLE `issuingrules` (
995 `categorycode` varchar(10) NOT NULL default '',
996 `itemtype` varchar(10) NOT NULL default '',
997 `restrictedtype` tinyint(1) default NULL,
998 `rentaldiscount` decimal(28,6) default NULL,
999 `reservecharge` decimal(28,6) default NULL,
1000 `fine` decimal(28,6) default NULL,
1001 `firstremind` int(11) default NULL,
1002 `chargeperiod` int(11) default NULL,
1003 `accountsent` int(11) default NULL,
1004 `chargename` varchar(100) default NULL,
1005 `maxissueqty` int(4) default NULL,
1006 `issuelength` int(4) default NULL,
1007 `branchcode` varchar(10) NOT NULL default '',
1008 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1009 KEY `categorycode` (`categorycode`),
1010 KEY `itemtype` (`itemtype`)
1011 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1014 -- Table structure for table `items`
1017 DROP TABLE IF EXISTS `items`;
1018 CREATE TABLE `items` (
1019 `itemnumber` int(11) NOT NULL auto_increment,
1020 `biblionumber` int(11) NOT NULL default 0,
1021 `biblioitemnumber` int(11) NOT NULL default 0,
1022 `barcode` varchar(20) default NULL,
1023 `dateaccessioned` date default NULL,
1024 `booksellerid` varchar(10) default NULL,
1025 `homebranch` varchar(10) default NULL,
1026 `price` decimal(8,2) default NULL,
1027 `replacementprice` decimal(8,2) default NULL,
1028 `replacementpricedate` date default NULL,
1029 `datelastborrowed` date default NULL,
1030 `datelastseen` date default NULL,
1031 `stack` tinyint(1) default NULL,
1032 `notforloan` tinyint(1) NOT NULL default 0,
1033 `damaged` tinyint(1) NOT NULL default 0,
1034 `itemlost` tinyint(1) NOT NULL default 0,
1035 `wthdrawn` tinyint(1) NOT NULL default 0,
1036 `itemcallnumber` varchar(30) default NULL,
1037 `issues` smallint(6) default NULL,
1038 `renewals` smallint(6) default NULL,
1039 `reserves` smallint(6) default NULL,
1040 `restricted` tinyint(1) default NULL,
1041 `itemnotes` mediumtext,
1042 `holdingbranch` varchar(10) default NULL,
1043 `paidfor` mediumtext,
1044 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1045 `location` varchar(80) default NULL,
1046 `onloan` date default NULL,
1047 `cn_source` varchar(10) default NULL,
1048 `cn_sort` varchar(30) default NULL,
1049 `ccode` varchar(10) default NULL,
1050 `materials` varchar(10) default NULL,
1051 `uri` varchar(255) default NULL,
1052 `itype` varchar(10) default NULL,
1053 `more_subfields_xml` longtext default NULL,
1054 `enumchron` varchar(80) default NULL,
1055 PRIMARY KEY (`itemnumber`),
1056 UNIQUE KEY `itembarcodeidx` (`barcode`),
1057 KEY `itembinoidx` (`biblioitemnumber`),
1058 KEY `itembibnoidx` (`biblionumber`),
1059 KEY `homebranch` (`homebranch`),
1060 KEY `holdingbranch` (`holdingbranch`),
1061 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1062 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1063 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1064 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1067 -- Table structure for table `itemtypes`
1070 DROP TABLE IF EXISTS `itemtypes`;
1071 CREATE TABLE `itemtypes` (
1072 `itemtype` varchar(10) NOT NULL default '',
1073 `description` mediumtext,
1074 `renewalsallowed` smallint(6) default NULL,
1075 `rentalcharge` double(16,4) default NULL,
1076 `notforloan` smallint(6) default NULL,
1077 `imageurl` varchar(200) default NULL,
1079 PRIMARY KEY (`itemtype`),
1080 UNIQUE KEY `itemtype` (`itemtype`)
1081 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1084 -- Table structure for table `labels`
1087 DROP TABLE IF EXISTS `labels`;
1088 CREATE TABLE `labels` (
1089 `labelid` int(11) NOT NULL auto_increment,
1090 `batch_id` varchar(10) NOT NULL default 1,
1091 `itemnumber` varchar(100) NOT NULL default '',
1092 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1093 PRIMARY KEY (`labelid`)
1094 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1097 -- Table structure for table `labels_conf`
1100 DROP TABLE IF EXISTS `labels_conf`;
1101 CREATE TABLE `labels_conf` (
1102 `id` int(4) NOT NULL auto_increment,
1103 `barcodetype` char(100) default '',
1104 `title` int(1) default '0',
1105 `subtitle` int(1) default '0',
1106 `itemtype` int(1) default '0',
1107 `barcode` int(1) default '0',
1108 `dewey` int(1) default '0',
1109 `class` int(1) default NULL,
1110 `subclass` int(1) default '0',
1111 `itemcallnumber` int(1) default '0',
1112 `author` int(1) default '0',
1113 `issn` int(1) default '0',
1114 `isbn` int(1) default '0',
1115 `startlabel` int(2) NOT NULL default '1',
1116 `printingtype` char(32) default 'BAR',
1117 `layoutname` char(20) NOT NULL default 'TEST',
1118 `guidebox` int(1) default '0',
1119 `active` tinyint(1) default '1',
1120 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1121 `ccode` char(4) collate utf8_unicode_ci default NULL,
1122 `callnum_split` int(1) default NULL,
1123 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1125 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1128 -- Table structure for table `labels_profile`
1131 DROP TABLE IF EXISTS `labels_profile`;
1132 CREATE TABLE `labels_profile` (
1133 `tmpl_id` int(4) NOT NULL,
1134 `prof_id` int(4) NOT NULL,
1135 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1136 UNIQUE KEY `prof_id` (`prof_id`)
1137 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1140 -- Table structure for table `labels_templates`
1143 DROP TABLE IF EXISTS `labels_templates`;
1144 CREATE TABLE `labels_templates` (
1145 `tmpl_id` int(4) NOT NULL auto_increment,
1146 `tmpl_code` char(100) default '',
1147 `tmpl_desc` char(100) default '',
1148 `page_width` float default '0',
1149 `page_height` float default '0',
1150 `label_width` float default '0',
1151 `label_height` float default '0',
1152 `topmargin` float default '0',
1153 `leftmargin` float default '0',
1154 `cols` int(2) default '0',
1155 `rows` int(2) default '0',
1156 `colgap` float default '0',
1157 `rowgap` float default '0',
1158 `active` int(1) default NULL,
1159 `units` char(20) default 'PX',
1160 `fontsize` int(4) NOT NULL default '3',
1161 `font` char(10) NOT NULL default 'TR',
1162 PRIMARY KEY (`tmpl_id`)
1163 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1166 -- Table structure for table `letter`
1169 DROP TABLE IF EXISTS `letter`;
1170 CREATE TABLE `letter` (
1171 `module` varchar(20) NOT NULL default '',
1172 `code` varchar(20) NOT NULL default '',
1173 `name` varchar(100) NOT NULL default '',
1174 `title` varchar(200) NOT NULL default '',
1176 PRIMARY KEY (`module`,`code`)
1177 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1180 -- Table structure for table `marc_subfield_structure`
1183 DROP TABLE IF EXISTS `marc_subfield_structure`;
1184 CREATE TABLE `marc_subfield_structure` (
1185 `tagfield` varchar(3) NOT NULL default '',
1186 `tagsubfield` varchar(1) NOT NULL default '',
1187 `liblibrarian` varchar(255) NOT NULL default '',
1188 `libopac` varchar(255) NOT NULL default '',
1189 `repeatable` tinyint(4) NOT NULL default 0,
1190 `mandatory` tinyint(4) NOT NULL default 0,
1191 `kohafield` varchar(40) default NULL,
1192 `tab` tinyint(1) default NULL,
1193 `authorised_value` varchar(20) default NULL,
1194 `authtypecode` varchar(20) default NULL,
1195 `value_builder` varchar(80) default NULL,
1196 `isurl` tinyint(1) default NULL,
1197 `hidden` tinyint(1) default NULL,
1198 `frameworkcode` varchar(4) NOT NULL default '',
1199 `seealso` varchar(1100) default NULL,
1200 `link` varchar(80) default NULL,
1201 `defaultvalue` text default NULL,
1202 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1203 KEY `kohafield_2` (`kohafield`),
1204 KEY `tab` (`frameworkcode`,`tab`),
1205 KEY `kohafield` (`frameworkcode`,`kohafield`)
1206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1209 -- Table structure for table `marc_tag_structure`
1212 DROP TABLE IF EXISTS `marc_tag_structure`;
1213 CREATE TABLE `marc_tag_structure` (
1214 `tagfield` varchar(3) NOT NULL default '',
1215 `liblibrarian` varchar(255) NOT NULL default '',
1216 `libopac` varchar(255) NOT NULL default '',
1217 `repeatable` tinyint(4) NOT NULL default 0,
1218 `mandatory` tinyint(4) NOT NULL default 0,
1219 `authorised_value` varchar(10) default NULL,
1220 `frameworkcode` varchar(4) NOT NULL default '',
1221 PRIMARY KEY (`frameworkcode`,`tagfield`)
1222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1225 -- Table structure for table `marc_matchers`
1228 DROP TABLE IF EXISTS `marc_matchers`;
1229 CREATE TABLE `marc_matchers` (
1230 `matcher_id` int(11) NOT NULL auto_increment,
1231 `code` varchar(10) NOT NULL default '',
1232 `description` varchar(255) NOT NULL default '',
1233 `record_type` varchar(10) NOT NULL default 'biblio',
1234 `threshold` int(11) NOT NULL default 0,
1235 PRIMARY KEY (`matcher_id`),
1236 KEY `code` (`code`),
1237 KEY `record_type` (`record_type`)
1238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1241 -- Table structure for table `matchpoints`
1243 DROP TABLE IF EXISTS `matchpoints`;
1244 CREATE TABLE `matchpoints` (
1245 `matcher_id` int(11) NOT NULL,
1246 `matchpoint_id` int(11) NOT NULL auto_increment,
1247 `search_index` varchar(30) NOT NULL default '',
1248 `score` int(11) NOT NULL default 0,
1249 PRIMARY KEY (`matchpoint_id`),
1250 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1251 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1252 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1256 -- Table structure for table `matchpoint_components`
1258 DROP TABLE IF EXISTS `matchpoint_components`;
1259 CREATE TABLE `matchpoint_components` (
1260 `matchpoint_id` int(11) NOT NULL,
1261 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1262 sequence int(11) NOT NULL default 0,
1263 tag varchar(3) NOT NULL default '',
1264 subfields varchar(40) NOT NULL default '',
1265 offset int(4) NOT NULL default 0,
1266 length int(4) NOT NULL default 0,
1267 PRIMARY KEY (`matchpoint_component_id`),
1268 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1269 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1270 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1274 -- Table structure for table `matcher_component_norms`
1276 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1277 CREATE TABLE `matchpoint_component_norms` (
1278 `matchpoint_component_id` int(11) NOT NULL,
1279 `sequence` int(11) NOT NULL default 0,
1280 `norm_routine` varchar(50) NOT NULL default '',
1281 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1282 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1283 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1284 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1287 -- Table structure for table `matcher_matchpoints`
1289 DROP TABLE IF EXISTS `matcher_matchpoints`;
1290 CREATE TABLE `matcher_matchpoints` (
1291 `matcher_id` int(11) NOT NULL,
1292 `matchpoint_id` int(11) NOT NULL,
1293 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1294 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1295 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1296 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1297 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1300 -- Table structure for table `matchchecks`
1302 DROP TABLE IF EXISTS `matchchecks`;
1303 CREATE TABLE `matchchecks` (
1304 `matcher_id` int(11) NOT NULL,
1305 `matchcheck_id` int(11) NOT NULL auto_increment,
1306 `source_matchpoint_id` int(11) NOT NULL,
1307 `target_matchpoint_id` int(11) NOT NULL,
1308 PRIMARY KEY (`matchcheck_id`),
1309 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1310 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1311 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1312 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1313 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1314 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1315 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1318 -- Table structure for table `mediatypetable`
1321 DROP TABLE IF EXISTS `mediatypetable`;
1322 CREATE TABLE `mediatypetable` (
1323 `mediatypecode` varchar(5) NOT NULL default '',
1325 `itemtypecodes` text,
1326 PRIMARY KEY (`mediatypecode`)
1327 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1330 -- Table structure for table `notifys`
1333 DROP TABLE IF EXISTS `notifys`;
1334 CREATE TABLE `notifys` (
1335 `notify_id` int(11) NOT NULL default 0,
1336 `borrowernumber` int(11) NOT NULL default 0,
1337 `itemnumber` int(11) NOT NULL default 0,
1338 `notify_date` date default NULL,
1339 `notify_send_date` date default NULL,
1340 `notify_level` int(1) NOT NULL default 0,
1341 `method` varchar(20) NOT NULL default ''
1342 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1345 -- Table structure for table `nozebra`
1348 DROP TABLE IF EXISTS `nozebra`;
1349 CREATE TABLE `nozebra` (
1350 `server` varchar(20) NOT NULL,
1351 `indexname` varchar(40) NOT NULL,
1352 `value` varchar(250) NOT NULL,
1353 `biblionumbers` longtext NOT NULL,
1354 KEY `indexname` (`server`,`indexname`),
1355 KEY `value` (`server`,`value`))
1356 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1359 -- Table structure for table `old_issues`
1362 DROP TABLE IF EXISTS `old_issues`;
1363 CREATE TABLE `old_issues` (
1364 `borrowernumber` int(11) default NULL,
1365 `itemnumber` int(11) default NULL,
1366 `date_due` date default NULL,
1367 `branchcode` varchar(10) default NULL,
1368 `issuingbranch` varchar(18) default NULL,
1369 `returndate` date default NULL,
1370 `lastreneweddate` date default NULL,
1371 `return` varchar(4) default NULL,
1372 `renewals` tinyint(4) default NULL,
1373 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1374 `issuedate` date default NULL,
1375 KEY `old_issuesborridx` (`borrowernumber`),
1376 KEY `old_issuesitemidx` (`itemnumber`),
1377 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1378 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1379 ON DELETE SET NULL ON UPDATE SET NULL,
1380 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1381 ON DELETE SET NULL ON UPDATE SET NULL
1382 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1385 -- Table structure for table `old_reserves`
1387 DROP TABLE IF EXISTS `old_reserves`;
1388 CREATE TABLE `old_reserves` (
1389 `borrowernumber` int(11) default NULL,
1390 `reservedate` date default NULL,
1391 `biblionumber` int(11) default NULL,
1392 `constrainttype` varchar(1) default NULL,
1393 `branchcode` varchar(10) default NULL,
1394 `notificationdate` date default NULL,
1395 `reminderdate` date default NULL,
1396 `cancellationdate` date default NULL,
1397 `reservenotes` mediumtext,
1398 `priority` smallint(6) default NULL,
1399 `found` varchar(1) default NULL,
1400 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1401 `itemnumber` int(11) default NULL,
1402 `waitingdate` date default NULL,
1403 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1404 KEY `old_reserves_biblionumber` (`biblionumber`),
1405 KEY `old_reserves_itemnumber` (`itemnumber`),
1406 KEY `old_reserves_branchcode` (`branchcode`),
1407 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1408 ON DELETE SET NULL ON UPDATE SET NULL,
1409 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1410 ON DELETE SET NULL ON UPDATE SET NULL,
1411 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1412 ON DELETE SET NULL ON UPDATE SET NULL
1413 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1416 -- Table structure for table `opac_news`
1419 DROP TABLE IF EXISTS `opac_news`;
1420 CREATE TABLE `opac_news` (
1421 `idnew` int(10) unsigned NOT NULL auto_increment,
1422 `title` varchar(250) NOT NULL default '',
1423 `new` text NOT NULL,
1424 `lang` varchar(25) NOT NULL default '',
1425 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1426 `expirationdate` date default NULL,
1427 `number` int(11) default NULL,
1428 PRIMARY KEY (`idnew`)
1429 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1432 -- Table structure for table `overduerules`
1435 DROP TABLE IF EXISTS `overduerules`;
1436 CREATE TABLE `overduerules` (
1437 `branchcode` varchar(10) NOT NULL default '',
1438 `categorycode` varchar(2) NOT NULL default '',
1439 `delay1` int(4) default 0,
1440 `letter1` varchar(20) default NULL,
1441 `debarred1` varchar(1) default 0,
1442 `delay2` int(4) default 0,
1443 `debarred2` varchar(1) default 0,
1444 `letter2` varchar(20) default NULL,
1445 `delay3` int(4) default 0,
1446 `letter3` varchar(20) default NULL,
1447 `debarred3` int(1) default 0,
1448 PRIMARY KEY (`branchcode`,`categorycode`)
1449 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1452 -- Table structure for table `patronimage`
1455 DROP TABLE IF EXISTS `patronimage`;
1456 CREATE TABLE `patronimage` (
1457 `cardnumber` varchar(16) NOT NULL,
1458 `mimetype` varchar(15) NOT NULL,
1459 `imagefile` mediumblob NOT NULL,
1460 PRIMARY KEY (`cardnumber`),
1461 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1462 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1465 -- Table structure for table `printers`
1468 DROP TABLE IF EXISTS `printers`;
1469 CREATE TABLE `printers` (
1470 `printername` varchar(40) NOT NULL default '',
1471 `printqueue` varchar(20) default NULL,
1472 `printtype` varchar(20) default NULL,
1473 PRIMARY KEY (`printername`)
1474 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1477 -- Table structure for table `printers_profile`
1480 DROP TABLE IF EXISTS `printers_profile`;
1481 CREATE TABLE `printers_profile` (
1482 `prof_id` int(4) NOT NULL auto_increment,
1483 `printername` varchar(40) NOT NULL,
1484 `tmpl_id` int(4) NOT NULL,
1485 `paper_bin` varchar(20) NOT NULL,
1486 `offset_horz` float default NULL,
1487 `offset_vert` float default NULL,
1488 `creep_horz` float default NULL,
1489 `creep_vert` float default NULL,
1490 `unit` char(20) NOT NULL default 'POINT',
1491 PRIMARY KEY (`prof_id`),
1492 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1493 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1497 -- Table structure for table `repeatable_holidays`
1500 DROP TABLE IF EXISTS `repeatable_holidays`;
1501 CREATE TABLE `repeatable_holidays` (
1502 `id` int(11) NOT NULL auto_increment,
1503 `branchcode` varchar(10) NOT NULL default '',
1504 `weekday` smallint(6) default NULL,
1505 `day` smallint(6) default NULL,
1506 `month` smallint(6) default NULL,
1507 `title` varchar(50) NOT NULL default '',
1508 `description` text NOT NULL,
1510 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1513 -- Table structure for table `reports_dictionary`
1516 DROP TABLE IF EXISTS `reports_dictionary`;
1517 CREATE TABLE reports_dictionary (
1518 `id` int(11) NOT NULL auto_increment,
1519 `name` varchar(255) default NULL,
1521 `date_created` datetime default NULL,
1522 `date_modified` datetime default NULL,
1524 `area` int(11) default NULL,
1526 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1529 -- Table structure for table `reserveconstraints`
1532 DROP TABLE IF EXISTS `reserveconstraints`;
1533 CREATE TABLE `reserveconstraints` (
1534 `borrowernumber` int(11) NOT NULL default 0,
1535 `reservedate` date default NULL,
1536 `biblionumber` int(11) NOT NULL default 0,
1537 `biblioitemnumber` int(11) default NULL,
1538 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1539 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1542 -- Table structure for table `reserves`
1545 DROP TABLE IF EXISTS `reserves`;
1546 CREATE TABLE `reserves` (
1547 `borrowernumber` int(11) NOT NULL default 0,
1548 `reservedate` date default NULL,
1549 `biblionumber` int(11) NOT NULL default 0,
1550 `constrainttype` varchar(1) default NULL,
1551 `branchcode` varchar(10) default NULL,
1552 `notificationdate` date default NULL,
1553 `reminderdate` date default NULL,
1554 `cancellationdate` date default NULL,
1555 `reservenotes` mediumtext,
1556 `priority` smallint(6) default NULL,
1557 `found` varchar(1) default NULL,
1558 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1559 `itemnumber` int(11) default NULL,
1560 `waitingdate` date default NULL,
1561 KEY `borrowernumber` (`borrowernumber`),
1562 KEY `biblionumber` (`biblionumber`),
1563 KEY `itemnumber` (`itemnumber`),
1564 KEY `branchcode` (`branchcode`),
1565 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1566 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1567 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1568 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1569 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1572 -- Table structure for table `reviews`
1575 DROP TABLE IF EXISTS `reviews`;
1576 CREATE TABLE `reviews` (
1577 `reviewid` int(11) NOT NULL auto_increment,
1578 `borrowernumber` int(11) default NULL,
1579 `biblionumber` int(11) default NULL,
1581 `approved` tinyint(4) default NULL,
1582 `datereviewed` datetime default NULL,
1583 PRIMARY KEY (`reviewid`)
1584 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1587 -- Table structure for table `roadtype`
1590 DROP TABLE IF EXISTS `roadtype`;
1591 CREATE TABLE `roadtype` (
1592 `roadtypeid` int(11) NOT NULL auto_increment,
1593 `road_type` varchar(100) NOT NULL default '',
1594 PRIMARY KEY (`roadtypeid`)
1595 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1598 -- Table structure for table `saved_sql`
1601 DROP TABLE IF EXISTS `saved_sql`;
1602 CREATE TABLE saved_sql (
1603 `id` int(11) NOT NULL auto_increment,
1604 `borrowernumber` int(11) default NULL,
1605 `date_created` datetime default NULL,
1606 `last_modified` datetime default NULL,
1608 `last_run` datetime default NULL,
1609 `report_name` varchar(255) default NULL,
1610 `type` varchar(255) default NULL,
1613 KEY boridx (`borrowernumber`)
1614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1618 -- Table structure for `saved_reports`
1621 DROP TABLE IF EXISTS `saved_reports`;
1622 CREATE TABLE saved_reports (
1623 `id` int(11) NOT NULL auto_increment,
1624 `report_id` int(11) default NULL,
1626 `date_run` datetime default NULL,
1628 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1632 -- Table structure for table `serial`
1635 DROP TABLE IF EXISTS `serial`;
1636 CREATE TABLE `serial` (
1637 `serialid` int(11) NOT NULL auto_increment,
1638 `biblionumber` varchar(100) NOT NULL default '',
1639 `subscriptionid` varchar(100) NOT NULL default '',
1640 `serialseq` varchar(100) NOT NULL default '',
1641 `status` tinyint(4) NOT NULL default 0,
1642 `planneddate` date default NULL,
1644 `publisheddate` date default NULL,
1645 `itemnumber` text default NULL,
1646 `claimdate` date default NULL,
1647 `routingnotes` text,
1648 PRIMARY KEY (`serialid`)
1649 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1652 -- Table structure for table `sessions`
1655 DROP TABLE IF EXISTS sessions;
1656 CREATE TABLE sessions (
1657 `id` varchar(32) NOT NULL,
1658 `a_session` text NOT NULL,
1660 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1663 -- Table structure for table `special_holidays`
1666 DROP TABLE IF EXISTS `special_holidays`;
1667 CREATE TABLE `special_holidays` (
1668 `id` int(11) NOT NULL auto_increment,
1669 `branchcode` varchar(10) NOT NULL default '',
1670 `day` smallint(6) NOT NULL default 0,
1671 `month` smallint(6) NOT NULL default 0,
1672 `year` smallint(6) NOT NULL default 0,
1673 `isexception` smallint(1) NOT NULL default 1,
1674 `title` varchar(50) NOT NULL default '',
1675 `description` text NOT NULL,
1677 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1680 -- Table structure for table `statistics`
1683 DROP TABLE IF EXISTS `statistics`;
1684 CREATE TABLE `statistics` (
1685 `datetime` datetime default NULL,
1686 `branch` varchar(10) default NULL,
1687 `proccode` varchar(4) default NULL,
1688 `value` double(16,4) default NULL,
1689 `type` varchar(16) default NULL,
1691 `usercode` varchar(10) default NULL,
1692 `itemnumber` int(11) default NULL,
1693 `itemtype` varchar(10) default NULL,
1694 `borrowernumber` int(11) default NULL,
1695 `associatedborrower` int(11) default NULL,
1696 KEY `timeidx` (`datetime`)
1697 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1700 -- Table structure for table `stopwords`
1703 DROP TABLE IF EXISTS `stopwords`;
1704 CREATE TABLE `stopwords` (
1705 `word` varchar(255) default NULL
1706 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1709 -- Table structure for table `subcategorytable`
1712 DROP TABLE IF EXISTS `subcategorytable`;
1713 CREATE TABLE `subcategorytable` (
1714 `subcategorycode` varchar(5) NOT NULL default '',
1716 `itemtypecodes` text,
1717 PRIMARY KEY (`subcategorycode`)
1718 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1721 -- Table structure for table `subscription`
1724 DROP TABLE IF EXISTS `subscription`;
1725 CREATE TABLE `subscription` (
1726 `biblionumber` int(11) NOT NULL default 0,
1727 `subscriptionid` int(11) NOT NULL auto_increment,
1728 `librarian` varchar(100) default '',
1729 `startdate` date default NULL,
1730 `aqbooksellerid` int(11) default 0,
1731 `cost` int(11) default 0,
1732 `aqbudgetid` int(11) default 0,
1733 `weeklength` int(11) default 0,
1734 `monthlength` int(11) default 0,
1735 `numberlength` int(11) default 0,
1736 `periodicity` tinyint(4) default 0,
1737 `dow` varchar(100) default '',
1738 `numberingmethod` varchar(100) default '',
1740 `status` varchar(100) NOT NULL default '',
1741 `add1` int(11) default 0,
1742 `every1` int(11) default 0,
1743 `whenmorethan1` int(11) default 0,
1744 `setto1` int(11) default NULL,
1745 `lastvalue1` int(11) default NULL,
1746 `add2` int(11) default 0,
1747 `every2` int(11) default 0,
1748 `whenmorethan2` int(11) default 0,
1749 `setto2` int(11) default NULL,
1750 `lastvalue2` int(11) default NULL,
1751 `add3` int(11) default 0,
1752 `every3` int(11) default 0,
1753 `innerloop1` int(11) default 0,
1754 `innerloop2` int(11) default 0,
1755 `innerloop3` int(11) default 0,
1756 `whenmorethan3` int(11) default 0,
1757 `setto3` int(11) default NULL,
1758 `lastvalue3` int(11) default NULL,
1759 `issuesatonce` tinyint(3) NOT NULL default 1,
1760 `firstacquidate` date default NULL,
1761 `manualhistory` tinyint(1) NOT NULL default 0,
1762 `irregularity` text,
1763 `letter` varchar(20) default NULL,
1764 `numberpattern` tinyint(3) default 0,
1765 `distributedto` text,
1766 `internalnotes` longtext,
1768 `branchcode` varchar(10) NOT NULL default '',
1769 `hemisphere` tinyint(3) default 0,
1770 `lastbranch` varchar(10),
1771 PRIMARY KEY (`subscriptionid`)
1772 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1775 -- Table structure for table `subscriptionhistory`
1778 DROP TABLE IF EXISTS `subscriptionhistory`;
1779 CREATE TABLE `subscriptionhistory` (
1780 `biblionumber` int(11) NOT NULL default 0,
1781 `subscriptionid` int(11) NOT NULL default 0,
1782 `histstartdate` date default NULL,
1783 `enddate` date default NULL,
1784 `missinglist` longtext NOT NULL,
1785 `recievedlist` longtext NOT NULL,
1786 `opacnote` varchar(150) NOT NULL default '',
1787 `librariannote` varchar(150) NOT NULL default '',
1788 PRIMARY KEY (`subscriptionid`),
1789 KEY `biblionumber` (`biblionumber`)
1790 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1793 -- Table structure for table `subscriptionroutinglist`
1796 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1797 CREATE TABLE `subscriptionroutinglist` (
1798 `routingid` int(11) NOT NULL auto_increment,
1799 `borrowernumber` int(11) default NULL,
1800 `ranking` int(11) default NULL,
1801 `subscriptionid` int(11) default NULL,
1802 PRIMARY KEY (`routingid`)
1803 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1806 -- Table structure for table `suggestions`
1809 DROP TABLE IF EXISTS `suggestions`;
1810 CREATE TABLE `suggestions` (
1811 `suggestionid` int(8) NOT NULL auto_increment,
1812 `suggestedby` int(11) NOT NULL default 0,
1813 `managedby` int(11) default NULL,
1814 `STATUS` varchar(10) NOT NULL default '',
1816 `author` varchar(80) default NULL,
1817 `title` varchar(80) default NULL,
1818 `copyrightdate` smallint(6) default NULL,
1819 `publishercode` varchar(255) default NULL,
1820 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1821 `volumedesc` varchar(255) default NULL,
1822 `publicationyear` smallint(6) default 0,
1823 `place` varchar(255) default NULL,
1824 `isbn` varchar(10) default NULL,
1825 `mailoverseeing` smallint(1) default 0,
1826 `biblionumber` int(11) default NULL,
1828 PRIMARY KEY (`suggestionid`),
1829 KEY `suggestedby` (`suggestedby`),
1830 KEY `managedby` (`managedby`)
1831 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1834 -- Table structure for table `systempreferences`
1837 DROP TABLE IF EXISTS `systempreferences`;
1838 CREATE TABLE `systempreferences` (
1839 `variable` varchar(50) NOT NULL default '',
1841 `options` mediumtext,
1843 `type` varchar(20) default NULL,
1844 PRIMARY KEY (`variable`)
1845 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1848 -- Table structure for table `tags`
1851 DROP TABLE IF EXISTS `tags`;
1852 CREATE TABLE `tags` (
1853 `entry` varchar(255) NOT NULL default '',
1854 `weight` bigint(20) NOT NULL default 0,
1855 PRIMARY KEY (`entry`)
1856 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1859 -- Table structure for table `userflags`
1862 DROP TABLE IF EXISTS `userflags`;
1863 CREATE TABLE `userflags` (
1864 `bit` int(11) NOT NULL default 0,
1865 `flag` varchar(30) default NULL,
1866 `flagdesc` varchar(255) default NULL,
1867 `defaulton` int(11) default NULL,
1869 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1872 -- Table structure for table `virtualshelves`
1875 DROP TABLE IF EXISTS `virtualshelves`;
1876 CREATE TABLE `virtualshelves` (
1877 `shelfnumber` int(11) NOT NULL auto_increment,
1878 `shelfname` varchar(255) default NULL,
1879 `owner` varchar(80) default NULL,
1880 `category` varchar(1) default NULL,
1881 `sortfield` varchar(16) default NULL,
1882 PRIMARY KEY (`shelfnumber`)
1883 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1886 -- Table structure for table `virtualshelfcontents`
1889 DROP TABLE IF EXISTS `virtualshelfcontents`;
1890 CREATE TABLE `virtualshelfcontents` (
1891 `shelfnumber` int(11) NOT NULL default 0,
1892 `biblionumber` int(11) NOT NULL default 0,
1893 `flags` int(11) default NULL,
1894 `dateadded` timestamp NULL default NULL,
1895 KEY `shelfnumber` (`shelfnumber`),
1896 KEY `biblionumber` (`biblionumber`),
1897 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1898 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1899 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1902 -- Table structure for table `z3950servers`
1905 DROP TABLE IF EXISTS `z3950servers`;
1906 CREATE TABLE `z3950servers` (
1907 `host` varchar(255) default NULL,
1908 `port` int(11) default NULL,
1909 `db` varchar(255) default NULL,
1910 `userid` varchar(255) default NULL,
1911 `password` varchar(255) default NULL,
1913 `id` int(11) NOT NULL auto_increment,
1914 `checked` smallint(6) default NULL,
1915 `rank` int(11) default NULL,
1916 `syntax` varchar(80) default NULL,
1918 `position` enum('primary','secondary','') NOT NULL default 'primary',
1919 `type` enum('zed','opensearch') NOT NULL default 'zed',
1920 `encoding` text default NULL,
1921 `description` text NOT NULL,
1923 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1926 -- Table structure for table `zebraqueue`
1929 DROP TABLE IF EXISTS `zebraqueue`;
1930 CREATE TABLE `zebraqueue` (
1931 `id` int(11) NOT NULL auto_increment,
1932 `biblio_auth_number` int(11) NOT NULL default '0',
1933 `operation` char(20) NOT NULL default '',
1934 `server` char(20) NOT NULL default '',
1935 `done` int(11) NOT NULL default '0',
1936 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1938 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1939 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1941 DROP TABLE IF EXISTS `services_throttle`;
1942 CREATE TABLE `services_throttle` (
1943 `service_type` varchar(10) NOT NULL default '',
1944 `service_count` varchar(45) default NULL,
1945 PRIMARY KEY (`service_type`)
1946 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1948 -- http://www.w3.org/International/articles/language-tags/
1951 DROP TABLE IF EXISTS language_subtag_registry;
1952 CREATE TABLE language_subtag_registry (
1954 type varchar(25), -- language-script-region-variant-extension-privateuse
1955 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1957 KEY `subtag` (`subtag`)
1958 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1960 -- TODO: add suppress_scripts
1961 -- this maps three letter codes defined in iso639.2 back to their
1962 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1963 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1964 CREATE TABLE language_rfc4646_to_iso639 (
1965 rfc4646_subtag varchar(25),
1966 iso639_2_code varchar(25),
1967 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1968 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1970 DROP TABLE IF EXISTS language_descriptions;
1971 CREATE TABLE language_descriptions (
1975 description varchar(255),
1977 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1979 -- bi-directional support, keyed by script subcode
1980 DROP TABLE IF EXISTS language_script_bidi;
1981 CREATE TABLE language_script_bidi (
1982 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1983 bidi varchar(3), -- rtl ltr
1984 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1985 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1987 -- TODO: need to map language subtags to script subtags for detection
1988 -- of bidi when script is not specified (like ar, he)
1989 DROP TABLE IF EXISTS language_script_mapping;
1990 CREATE TABLE language_script_mapping (
1991 language_subtag varchar(25),
1992 script_subtag varchar(25),
1993 KEY `language_subtag` (`language_subtag`)
1994 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1996 DROP TABLE IF EXISTS serialitems;
1997 CREATE TABLE serialitems (
1998 serialid int(11) NOT NULL,
1999 itemnumber int(11) NOT NULL,
2000 UNIQUE KEY `serialididx` (`serialid`)
2001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2003 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2004 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2005 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2006 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2007 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2008 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2009 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2010 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;