3 -- Host: localhost Database: koha30test
4 -- ------------------------------------------------------
5 -- Server version 4.1.22
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
11 /*!40101 SET NAMES utf8 */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19 -- Table structure for table `accountlines`
22 DROP TABLE IF EXISTS `accountlines`;
23 CREATE TABLE `accountlines` (
24 `borrowernumber` int(11) NOT NULL default 0,
25 `accountno` smallint(6) NOT NULL default 0,
26 `itemnumber` int(11) default NULL,
27 `date` date default NULL,
28 `amount` decimal(28,6) default NULL,
29 `description` mediumtext,
31 `accounttype` varchar(5) default NULL,
32 `amountoutstanding` decimal(28,6) default NULL,
33 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
34 `notify_id` int(11) NOT NULL default 0,
35 `notify_level` int(2) NOT NULL default 0,
36 KEY `acctsborridx` (`borrowernumber`),
37 KEY `timeidx` (`timestamp`),
38 KEY `itemnumber` (`itemnumber`),
39 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
40 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
41 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
44 -- Table structure for table `accountoffsets`
47 DROP TABLE IF EXISTS `accountoffsets`;
48 CREATE TABLE `accountoffsets` (
49 `borrowernumber` int(11) NOT NULL default 0,
50 `accountno` smallint(6) NOT NULL default 0,
51 `offsetaccount` smallint(6) NOT NULL default 0,
52 `offsetamount` decimal(28,6) default NULL,
53 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
54 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
55 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
58 -- Table structure for table `action_logs`
61 DROP TABLE IF EXISTS `action_logs`;
62 CREATE TABLE `action_logs` (
63 `action_id` int(11) NOT NULL auto_increment,
64 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
65 `user` int(11) NOT NULL default 0,
68 `object` int(11) default NULL,
70 PRIMARY KEY (`action_id`),
71 KEY (`timestamp`,`user`)
72 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
75 -- Table structure for table `alert`
78 DROP TABLE IF EXISTS `alert`;
79 CREATE TABLE `alert` (
80 `alertid` int(11) NOT NULL auto_increment,
81 `borrowernumber` int(11) NOT NULL default 0,
82 `type` varchar(10) NOT NULL default '',
83 `externalid` varchar(20) NOT NULL default '',
84 PRIMARY KEY (`alertid`),
85 KEY `borrowernumber` (`borrowernumber`),
86 KEY `type` (`type`,`externalid`)
87 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
90 -- Table structure for table `aqbasket`
93 DROP TABLE IF EXISTS `aqbasket`;
94 CREATE TABLE `aqbasket` (
95 `basketno` int(11) NOT NULL auto_increment,
96 `creationdate` date default NULL,
97 `closedate` date default NULL,
98 `booksellerid` int(11) NOT NULL default 1,
99 `authorisedby` varchar(10) default NULL,
100 `booksellerinvoicenumber` mediumtext,
101 PRIMARY KEY (`basketno`),
102 KEY `booksellerid` (`booksellerid`),
103 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
104 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
107 -- Table structure for table `aqbookfund`
110 DROP TABLE IF EXISTS `aqbookfund`;
111 CREATE TABLE `aqbookfund` (
112 `bookfundid` varchar(10) NOT NULL default '',
113 `bookfundname` mediumtext,
114 `bookfundgroup` varchar(5) default NULL,
115 `branchcode` varchar(10) NOT NULL default '',
116 PRIMARY KEY (`bookfundid`,`branchcode`)
117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
120 -- Table structure for table `aqbooksellers`
123 DROP TABLE IF EXISTS `aqbooksellers`;
124 CREATE TABLE `aqbooksellers` (
125 `id` int(11) NOT NULL auto_increment,
127 `address1` mediumtext,
128 `address2` mediumtext,
129 `address3` mediumtext,
130 `address4` mediumtext,
131 `phone` varchar(30) default NULL,
132 `accountnumber` mediumtext,
133 `othersupplier` mediumtext,
134 `currency` varchar(3) NOT NULL default '',
135 `deliverydays` smallint(6) default NULL,
136 `followupdays` smallint(6) default NULL,
137 `followupscancel` smallint(6) default NULL,
138 `specialty` mediumtext,
139 `booksellerfax` mediumtext,
141 `bookselleremail` mediumtext,
142 `booksellerurl` mediumtext,
143 `contact` varchar(100) default NULL,
145 `url` varchar(255) default NULL,
146 `contpos` varchar(100) default NULL,
147 `contphone` varchar(100) default NULL,
148 `contfax` varchar(100) default NULL,
149 `contaltphone` varchar(100) default NULL,
150 `contemail` varchar(100) default NULL,
151 `contnotes` mediumtext,
152 `active` tinyint(4) default NULL,
153 `listprice` varchar(10) default NULL,
154 `invoiceprice` varchar(10) default NULL,
155 `gstreg` tinyint(4) default NULL,
156 `listincgst` tinyint(4) default NULL,
157 `invoiceincgst` tinyint(4) default NULL,
158 `discount` float(6,4) default NULL,
159 `fax` varchar(50) default NULL,
160 `nocalc` int(11) default NULL,
161 `invoicedisc` float(6,4) default NULL,
163 KEY `listprice` (`listprice`),
164 KEY `invoiceprice` (`invoiceprice`),
165 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
166 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
170 -- Table structure for table `aqbudget`
173 DROP TABLE IF EXISTS `aqbudget`;
174 CREATE TABLE `aqbudget` (
175 `bookfundid` varchar(10) NOT NULL default '',
176 `startdate` date NOT NULL default 0,
177 `enddate` date default NULL,
178 `budgetamount` decimal(13,2) default NULL,
179 `aqbudgetid` tinyint(4) NOT NULL auto_increment,
180 `branchcode` varchar(10) default NULL,
181 PRIMARY KEY (`aqbudgetid`)
182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
185 -- Table structure for table `aqorderbreakdown`
188 DROP TABLE IF EXISTS `aqorderbreakdown`;
189 CREATE TABLE `aqorderbreakdown` (
190 `ordernumber` int(11) default NULL,
191 `linenumber` int(11) default NULL,
192 `branchcode` varchar(10) default NULL,
193 `bookfundid` varchar(10) NOT NULL default '',
194 `allocation` smallint(6) default NULL,
195 KEY `ordernumber` (`ordernumber`),
196 KEY `bookfundid` (`bookfundid`),
197 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
198 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
202 -- Table structure for table `aqorderdelivery`
205 DROP TABLE IF EXISTS `aqorderdelivery`;
206 CREATE TABLE `aqorderdelivery` (
207 `ordernumber` date default NULL,
208 `deliverynumber` smallint(6) NOT NULL default 0,
209 `deliverydate` varchar(18) default NULL,
210 `qtydelivered` smallint(6) default NULL,
211 `deliverycomments` mediumtext
212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
215 -- Table structure for table `aqorders`
218 DROP TABLE IF EXISTS `aqorders`;
219 CREATE TABLE `aqorders` (
220 `ordernumber` int(11) NOT NULL auto_increment,
221 `biblionumber` int(11) default NULL,
223 `entrydate` date default NULL,
224 `quantity` smallint(6) default NULL,
225 `currency` varchar(3) default NULL,
226 `listprice` decimal(28,6) default NULL,
227 `totalamount` decimal(28,6) default NULL,
228 `datereceived` date default NULL,
229 `booksellerinvoicenumber` mediumtext,
230 `freight` decimal(28,6) default NULL,
231 `unitprice` decimal(28,6) default NULL,
232 `quantityreceived` smallint(6) default NULL,
233 `cancelledby` varchar(10) default NULL,
234 `datecancellationprinted` date default NULL,
236 `supplierreference` mediumtext,
237 `purchaseordernumber` mediumtext,
238 `subscription` tinyint(1) default NULL,
239 `serialid` varchar(30) default NULL,
240 `basketno` int(11) default NULL,
241 `biblioitemnumber` int(11) default NULL,
242 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
243 `rrp` decimal(13,2) default NULL,
244 `ecost` decimal(13,2) default NULL,
245 `gst` decimal(13,2) default NULL,
246 `budgetdate` date default NULL,
247 `sort1` varchar(80) default NULL,
248 `sort2` varchar(80) default NULL,
249 PRIMARY KEY (`ordernumber`),
250 KEY `basketno` (`basketno`),
251 KEY `biblionumber` (`biblionumber`),
252 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
253 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
254 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
257 -- Table structure for table `auth_header`
260 DROP TABLE IF EXISTS `auth_header`;
261 CREATE TABLE `auth_header` (
262 `authid` bigint(20) unsigned NOT NULL auto_increment,
263 `authtypecode` varchar(10) NOT NULL default '',
264 `datecreated` date default NULL,
265 `datemodified` date default NULL,
266 `origincode` varchar(20) default NULL,
267 `authtrees` mediumtext,
269 `linkid` bigint(20) default NULL,
270 `marcxml` longtext NOT NULL,
271 PRIMARY KEY (`authid`),
272 KEY `origincode` (`origincode`)
273 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
276 -- Table structure for table `auth_subfield_structure`
279 DROP TABLE IF EXISTS `auth_subfield_structure`;
280 CREATE TABLE `auth_subfield_structure` (
281 `authtypecode` varchar(10) NOT NULL default '',
282 `tagfield` varchar(3) NOT NULL default '',
283 `tagsubfield` varchar(1) NOT NULL default '',
284 `liblibrarian` varchar(255) NOT NULL default '',
285 `libopac` varchar(255) NOT NULL default '',
286 `repeatable` tinyint(4) NOT NULL default 0,
287 `mandatory` tinyint(4) NOT NULL default 0,
288 `tab` tinyint(1) default NULL,
289 `authorised_value` varchar(10) default NULL,
290 `value_builder` varchar(80) default NULL,
291 `seealso` varchar(255) default NULL,
292 `isurl` tinyint(1) default NULL,
293 `hidden` tinyint(3) NOT NULL default 0,
294 `linkid` tinyint(1) NOT NULL default 0,
295 `kohafield` varchar(45) NULL default '',
296 `frameworkcode` varchar(8) NOT NULL default '',
297 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
298 KEY `tab` (`authtypecode`,`tab`)
299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
302 -- Table structure for table `auth_tag_structure`
305 DROP TABLE IF EXISTS `auth_tag_structure`;
306 CREATE TABLE `auth_tag_structure` (
307 `authtypecode` varchar(10) NOT NULL default '',
308 `tagfield` varchar(3) NOT NULL default '',
309 `liblibrarian` varchar(255) NOT NULL default '',
310 `libopac` varchar(255) NOT NULL default '',
311 `repeatable` tinyint(4) NOT NULL default 0,
312 `mandatory` tinyint(4) NOT NULL default 0,
313 `authorised_value` varchar(10) default NULL,
314 PRIMARY KEY (`authtypecode`,`tagfield`),
315 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
316 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
319 -- Table structure for table `auth_types`
322 DROP TABLE IF EXISTS `auth_types`;
323 CREATE TABLE `auth_types` (
324 `authtypecode` varchar(10) NOT NULL default '',
325 `authtypetext` varchar(255) NOT NULL default '',
326 `auth_tag_to_report` varchar(3) NOT NULL default '',
327 `summary` mediumtext NOT NULL,
328 PRIMARY KEY (`authtypecode`)
329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
332 -- Table structure for table `authorised_values`
335 DROP TABLE IF EXISTS `authorised_values`;
336 CREATE TABLE `authorised_values` (
337 `id` int(11) NOT NULL auto_increment,
338 `category` varchar(10) NOT NULL default '',
339 `authorised_value` varchar(80) NOT NULL default '',
340 `lib` varchar(80) default NULL,
342 KEY `name` (`category`)
343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
346 -- Table structure for table `biblio`
349 DROP TABLE IF EXISTS `biblio`;
350 CREATE TABLE `biblio` (
351 `biblionumber` int(11) NOT NULL auto_increment,
352 `frameworkcode` varchar(4) NOT NULL default '',
355 `unititle` mediumtext,
357 `serial` tinyint(1) default NULL,
358 `seriestitle` mediumtext,
359 `copyrightdate` smallint(6) default NULL,
360 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
361 `datecreated` DATE NOT NULL,
362 `abstract` mediumtext,
363 PRIMARY KEY (`biblionumber`),
364 KEY `blbnoidx` (`biblionumber`)
365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
368 -- Table structure for table `biblio_framework`
371 DROP TABLE IF EXISTS `biblio_framework`;
372 CREATE TABLE `biblio_framework` (
373 `frameworkcode` varchar(4) NOT NULL default '',
374 `frameworktext` varchar(255) NOT NULL default '',
375 PRIMARY KEY (`frameworkcode`)
376 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
379 -- Table structure for table `biblioitems`
382 DROP TABLE IF EXISTS `biblioitems`;
383 CREATE TABLE `biblioitems` (
384 `biblioitemnumber` int(11) NOT NULL auto_increment,
385 `biblionumber` int(11) NOT NULL default 0,
388 `itemtype` varchar(10) default NULL,
389 `isbn` varchar(14) default NULL,
390 `issn` varchar(9) default NULL,
391 `publicationyear` text,
392 `publishercode` varchar(255) default NULL,
393 `volumedate` date default NULL,
395 `collectiontitle` mediumtext default NULL,
396 `collectionissn` text default NULL,
397 `collectionvolume` mediumtext default NULL,
398 `editionstatement` text default NULL,
399 `editionresponsibility` text default NULL,
400 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
401 `illus` varchar(255) default NULL,
402 `pages` varchar(255) default NULL,
404 `size` varchar(255) default NULL,
405 `place` varchar(255) default NULL,
406 `lccn` varchar(25) default NULL,
408 `url` varchar(255) default NULL,
409 `cn_source` varchar(10) default NULL,
410 `cn_class` varchar(30) default NULL,
411 `cn_item` varchar(10) default NULL,
412 `cn_suffix` varchar(10) default NULL,
413 `cn_sort` varchar(30) default NULL,
414 `totalissues` int(10),
415 `marcxml` longtext NOT NULL,
416 PRIMARY KEY (`biblioitemnumber`),
417 KEY `bibinoidx` (`biblioitemnumber`),
418 KEY `bibnoidx` (`biblionumber`),
420 KEY `publishercode` (`publishercode`),
421 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
422 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
425 -- Table structure for table `borrowers`
428 DROP TABLE IF EXISTS `borrowers`;
429 CREATE TABLE `borrowers` (
430 `borrowernumber` int(11) NOT NULL auto_increment,
431 `cardnumber` varchar(16) default NULL,
432 `surname` mediumtext NOT NULL,
435 `othernames` mediumtext,
437 `streetnumber` varchar(10) default NULL,
438 `streettype` varchar(50) default NULL,
439 `address` mediumtext NOT NULL,
441 `city` mediumtext NOT NULL,
442 `zipcode` varchar(25) default NULL,
445 `mobile` varchar(50) default NULL,
449 `B_streetnumber` varchar(10) default NULL,
450 `B_streettype` varchar(50) default NULL,
451 `B_address` varchar(100) default NULL,
453 `B_zipcode` varchar(25) default NULL,
455 `B_phone` mediumtext,
456 `dateofbirth` date default NULL,
457 `branchcode` varchar(10) NOT NULL default '',
458 `categorycode` varchar(10) NOT NULL default '',
459 `dateenrolled` date default NULL,
460 `dateexpiry` date default NULL,
461 `gonenoaddress` tinyint(1) default NULL,
462 `lost` tinyint(1) default NULL,
463 `debarred` tinyint(1) default NULL,
464 `contactname` mediumtext,
465 `contactfirstname` text,
467 `guarantorid` int(11) default NULL,
468 `borrowernotes` mediumtext,
469 `relationship` varchar(100) default NULL,
470 `ethnicity` varchar(50) default NULL,
471 `ethnotes` varchar(255) default NULL,
472 `sex` varchar(1) default NULL,
473 `password` varchar(30) default NULL,
474 `flags` int(11) default NULL,
475 `userid` varchar(30) default NULL,
476 `opacnote` mediumtext,
477 `contactnote` varchar(255) default NULL,
478 `sort1` varchar(80) default NULL,
479 `sort2` varchar(80) default NULL,
480 `altcontactfirstname` varchar(255) default NULL,
481 `altcontactsurname` varchar(255) default NULL,
482 `altcontactaddress1` varchar(255) default NULL,
483 `altcontactaddress2` varchar(255) default NULL,
484 `altcontactaddress3` varchar(255) default NULL,
485 `altcontactzipcode` varchar(50) default NULL,
486 `altcontactphone` varchar(50) default NULL,
487 UNIQUE KEY `cardnumber` (`cardnumber`),
488 PRIMARY KEY `borrowernumber` (`borrowernumber`),
489 KEY `categorycode` (`categorycode`),
490 KEY `branchcode` (`branchcode`),
491 KEY `userid` (`userid`),
492 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
493 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
497 -- Table structure for table `branchcategories`
500 DROP TABLE IF EXISTS `branchcategories`;
501 CREATE TABLE `branchcategories` (
502 `categorycode` varchar(10) NOT NULL default '',
503 `categoryname` varchar(32),
504 `codedescription` mediumtext,
505 `categorytype` varchar(16),
506 PRIMARY KEY (`categorycode`)
507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
510 -- Table structure for table `branches`
513 DROP TABLE IF EXISTS `branches`;
514 CREATE TABLE `branches` (
515 `branchcode` varchar(10) NOT NULL default '',
516 `branchname` mediumtext NOT NULL,
517 `branchaddress1` mediumtext,
518 `branchaddress2` mediumtext,
519 `branchaddress3` mediumtext,
520 `branchphone` mediumtext,
521 `branchfax` mediumtext,
522 `branchemail` mediumtext,
523 `issuing` tinyint(4) default NULL,
524 `branchip` varchar(15) default NULL,
525 `branchprinter` varchar(100) default NULL,
526 UNIQUE KEY `branchcode` (`branchcode`)
527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
530 -- Table structure for table `branchrelations`
533 DROP TABLE IF EXISTS `branchrelations`;
534 CREATE TABLE `branchrelations` (
535 `branchcode` varchar(10) NOT NULL default '',
536 `categorycode` varchar(10) NOT NULL default '',
537 PRIMARY KEY (`branchcode`,`categorycode`),
538 KEY `branchcode` (`branchcode`),
539 KEY `categorycode` (`categorycode`),
540 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
541 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
542 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
545 -- Table structure for table `branchtransfers`
548 DROP TABLE IF EXISTS `branchtransfers`;
549 CREATE TABLE `branchtransfers` (
550 `itemnumber` int(11) NOT NULL default 0,
551 `datesent` datetime default NULL,
552 `frombranch` varchar(10) NOT NULL default '',
553 `datearrived` datetime default NULL,
554 `tobranch` varchar(10) NOT NULL default '',
555 `comments` mediumtext,
556 KEY `frombranch` (`frombranch`),
557 KEY `tobranch` (`tobranch`),
558 KEY `itemnumber` (`itemnumber`),
559 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
560 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
561 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
566 -- Table structure for table `browser`
568 DROP TABLE IF EXISTS `browser`;
569 CREATE TABLE `browser` (
570 `level` int(11) NOT NULL,
571 `classification` varchar(20) NOT NULL,
572 `description` varchar(255) NOT NULL,
573 `number` bigint(20) NOT NULL,
574 `endnode` tinyint(4) NOT NULL
575 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
578 -- Table structure for table `categories`
581 DROP TABLE IF EXISTS `categories`;
582 CREATE TABLE `categories` (
583 `categorycode` varchar(10) NOT NULL default '',
584 `description` mediumtext,
585 `enrolmentperiod` smallint(6) default NULL,
586 `upperagelimit` smallint(6) default NULL,
587 `dateofbirthrequired` tinyint(1) default NULL,
588 `finetype` varchar(30) default NULL,
589 `bulk` tinyint(1) default NULL,
590 `enrolmentfee` decimal(28,6) default NULL,
591 `overduenoticerequired` tinyint(1) default NULL,
592 `issuelimit` smallint(6) default NULL,
593 `reservefee` decimal(28,6) default NULL,
594 `category_type` varchar(1) NOT NULL default 'A',
595 PRIMARY KEY (`categorycode`),
596 UNIQUE KEY `categorycode` (`categorycode`)
597 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
600 -- Table structure for table `categorytable`
603 DROP TABLE IF EXISTS `categorytable`;
604 CREATE TABLE `categorytable` (
605 `categorycode` varchar(5) NOT NULL default '',
607 `itemtypecodes` text,
608 PRIMARY KEY (`categorycode`)
609 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
612 -- Table structure for table `cities`
615 DROP TABLE IF EXISTS `cities`;
616 CREATE TABLE `cities` (
617 `cityid` int(11) NOT NULL auto_increment,
618 `city_name` varchar(100) NOT NULL default '',
619 `city_zipcode` varchar(20) default NULL,
620 PRIMARY KEY (`cityid`)
621 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
624 -- Table structure for table `class_sort_rules`
627 DROP TABLE IF EXISTS `class_sort_rules`;
628 CREATE TABLE `class_sort_rules` (
629 `class_sort_rule` varchar(10) NOT NULL default '',
630 `description` mediumtext,
631 `sort_routine` varchar(30) NOT NULL default '',
632 PRIMARY KEY (`class_sort_rule`),
633 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
634 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
637 -- Table structure for table `class_sources`
640 DROP TABLE IF EXISTS `class_sources`;
641 CREATE TABLE `class_sources` (
642 `cn_source` varchar(10) NOT NULL default '',
643 `description` mediumtext,
644 `used` tinyint(4) NOT NULL default 0,
645 `class_sort_rule` varchar(10) NOT NULL default '',
646 PRIMARY KEY (`cn_source`),
647 UNIQUE KEY `cn_source_idx` (`cn_source`),
648 KEY `used_idx` (`used`),
649 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
650 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
653 -- Table structure for table `currency`
656 DROP TABLE IF EXISTS `currency`;
657 CREATE TABLE `currency` (
658 `currency` varchar(10) NOT NULL default '',
659 `symbol` varchar(5) default NULL,
660 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
661 `rate` float(7,5) default NULL,
662 PRIMARY KEY (`currency`)
663 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
666 -- Table structure for table `deletedbiblio`
669 DROP TABLE IF EXISTS `deletedbiblio`;
670 CREATE TABLE `deletedbiblio` (
671 `biblionumber` int(11) NOT NULL default 0,
672 `frameworkcode` varchar(4) NOT NULL default '',
675 `unititle` mediumtext,
677 `serial` tinyint(1) default NULL,
678 `seriestitle` mediumtext,
679 `copyrightdate` smallint(6) default NULL,
680 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
681 `datecreated` DATE NOT NULL,
682 `abstract` mediumtext,
683 PRIMARY KEY (`biblionumber`),
684 KEY `blbnoidx` (`biblionumber`)
685 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
688 -- Table structure for table `deletedbiblioitems`
691 DROP TABLE IF EXISTS `deletedbiblioitems`;
692 CREATE TABLE `deletedbiblioitems` (
693 `biblioitemnumber` int(11) NOT NULL default 0,
694 `biblionumber` int(11) NOT NULL default 0,
697 `itemtype` varchar(10) default NULL,
698 `isbn` varchar(14) default NULL,
699 `issn` varchar(9) default NULL,
700 `publicationyear` text,
701 `publishercode` varchar(255) default NULL,
702 `volumedate` date default NULL,
704 `collectiontitle` mediumtext default NULL,
705 `collectionissn` text default NULL,
706 `collectionvolume` mediumtext default NULL,
707 `editionstatement` text default NULL,
708 `editionresponsibility` text default NULL,
709 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
710 `illus` varchar(255) default NULL,
711 `pages` varchar(255) default NULL,
713 `size` varchar(255) default NULL,
714 `place` varchar(255) default NULL,
715 `lccn` varchar(25) default NULL,
717 `url` varchar(255) default NULL,
718 `cn_source` varchar(10) default NULL,
719 `cn_class` varchar(30) default NULL,
720 `cn_item` varchar(10) default NULL,
721 `cn_suffix` varchar(10) default NULL,
722 `cn_sort` varchar(30) default NULL,
723 `totalissues` int(10),
724 `marcxml` longtext NOT NULL,
725 PRIMARY KEY (`biblioitemnumber`),
726 KEY `bibinoidx` (`biblioitemnumber`),
727 KEY `bibnoidx` (`biblionumber`),
729 KEY `publishercode` (`publishercode`)
730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
733 -- Table structure for table `deletedborrowers`
736 DROP TABLE IF EXISTS `deletedborrowers`;
737 CREATE TABLE `deletedborrowers` (
738 `borrowernumber` int(11) NOT NULL default 0,
739 `cardnumber` varchar(9) NOT NULL default '',
740 `surname` mediumtext NOT NULL,
743 `othernames` mediumtext,
745 `streetnumber` varchar(10) default NULL,
746 `streettype` varchar(50) default NULL,
747 `address` mediumtext NOT NULL,
749 `city` mediumtext NOT NULL,
750 `zipcode` varchar(25) default NULL,
753 `mobile` varchar(50) default NULL,
757 `B_streetnumber` varchar(10) default NULL,
758 `B_streettype` varchar(50) default NULL,
759 `B_address` varchar(100) default NULL,
761 `B_zipcode` varchar(25) default NULL,
763 `B_phone` mediumtext,
764 `dateofbirth` date default NULL,
765 `branchcode` varchar(10) NOT NULL default '',
766 `categorycode` varchar(2) default NULL,
767 `dateenrolled` date default NULL,
768 `dateexpiry` date default NULL,
769 `gonenoaddress` tinyint(1) default NULL,
770 `lost` tinyint(1) default NULL,
771 `debarred` tinyint(1) default NULL,
772 `contactname` mediumtext,
773 `contactfirstname` text,
775 `guarantorid` int(11) default NULL,
776 `borrowernotes` mediumtext,
777 `relationship` varchar(100) default NULL,
778 `ethnicity` varchar(50) default NULL,
779 `ethnotes` varchar(255) default NULL,
780 `sex` varchar(1) default NULL,
781 `password` varchar(30) default NULL,
782 `flags` int(11) default NULL,
783 `userid` varchar(30) default NULL,
784 `opacnote` mediumtext,
785 `contactnote` varchar(255) default NULL,
786 `sort1` varchar(80) default NULL,
787 `sort2` varchar(80) default NULL,
788 `altcontactfirstname` varchar(255) default NULL,
789 `altcontactsurname` varchar(255) default NULL,
790 `altcontactaddress1` varchar(255) default NULL,
791 `altcontactaddress2` varchar(255) default NULL,
792 `altcontactaddress3` varchar(255) default NULL,
793 `altcontactzipcode` varchar(50) default NULL,
794 `altcontactphone` varchar(50) default NULL,
795 KEY `borrowernumber` (`borrowernumber`),
796 KEY `cardnumber` (`cardnumber`)
797 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
800 -- Table structure for table `deleteditems`
803 DROP TABLE IF EXISTS `deleteditems`;
804 CREATE TABLE `deleteditems` (
805 `itemnumber` int(11) NOT NULL default 0,
806 `biblionumber` int(11) NOT NULL default 0,
807 `biblioitemnumber` int(11) NOT NULL default 0,
808 `barcode` varchar(20) default NULL,
809 `dateaccessioned` date default NULL,
810 `booksellerid` mediumtext default NULL,
811 `homebranch` varchar(10) default NULL,
812 `price` decimal(8,2) default NULL,
813 `replacementprice` decimal(8,2) default NULL,
814 `replacementpricedate` date default NULL,
815 `datelastborrowed` date default NULL,
816 `datelastseen` date default NULL,
817 `stack` tinyint(1) default NULL,
818 `notforloan` tinyint(1) NOT NULL default 0,
819 `damaged` tinyint(1) NOT NULL default 0,
820 `itemlost` tinyint(1) NOT NULL default 0,
821 `wthdrawn` tinyint(1) NOT NULL default 0,
822 `itemcallnumber` varchar(30) default NULL,
823 `issues` smallint(6) default NULL,
824 `renewals` smallint(6) default NULL,
825 `reserves` smallint(6) default NULL,
826 `restricted` tinyint(1) default NULL,
827 `itemnotes` mediumtext,
828 `holdingbranch` varchar(10) default NULL,
829 `paidfor` mediumtext,
830 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
831 `location` varchar(80) default NULL,
832 `onloan` date default NULL,
833 `cn_source` varchar(10) default NULL,
834 `cn_sort` varchar(30) default NULL,
835 `ccode` varchar(10) default NULL,
836 `materials` varchar(10) default NULL,
837 `uri` varchar(255) default NULL,
838 `itype` varchar(10) default NULL,
839 `more_subfields_xml` longtext default NULL,
840 `enumchron` varchar(80) default NULL,
841 `copynumber` smallint(6) default NULL,
843 `copynumber` smallint(6) default NULL,
844 PRIMARY KEY (`itemnumber`),
845 KEY `delitembarcodeidx` (`barcode`),
846 KEY `delitembinoidx` (`biblioitemnumber`),
847 KEY `delitembibnoidx` (`biblionumber`),
848 KEY `delhomebranch` (`homebranch`),
849 KEY `delholdingbranch` (`holdingbranch`)
850 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
853 -- Table structure for table `ethnicity`
856 DROP TABLE IF EXISTS `ethnicity`;
857 CREATE TABLE `ethnicity` (
858 `code` varchar(10) NOT NULL default '',
859 `name` varchar(255) default NULL,
861 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
864 -- Table structure for table `import_batches`
867 DROP TABLE IF EXISTS `import_batches`;
868 CREATE TABLE `import_batches` (
869 `import_batch_id` int(11) NOT NULL auto_increment,
870 `matcher_id` int(11) default NULL,
871 `template_id` int(11) default NULL,
872 `branchcode` varchar(10) default NULL,
873 `num_biblios` int(11) NOT NULL default 0,
874 `num_items` int(11) NOT NULL default 0,
875 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
876 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
877 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
878 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
879 `file_name` varchar(100),
880 `comments` mediumtext,
881 PRIMARY KEY (`import_batch_id`),
882 KEY `branchcode` (`branchcode`)
883 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
886 -- Table structure for table `import_records`
889 DROP TABLE IF EXISTS `import_records`;
890 CREATE TABLE `import_records` (
891 `import_record_id` int(11) NOT NULL auto_increment,
892 `import_batch_id` int(11) NOT NULL,
893 `branchcode` varchar(10) default NULL,
894 `record_sequence` int(11) NOT NULL default 0,
895 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
896 `import_date` DATE default NULL,
897 `marc` longblob NOT NULL,
898 `marcxml` longtext NOT NULL,
899 `marcxml_old` longtext NOT NULL,
900 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
901 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
902 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
903 `import_error` mediumtext,
904 `encoding` varchar(40) NOT NULL default '',
905 `z3950random` varchar(40) default NULL,
906 PRIMARY KEY (`import_record_id`),
907 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
908 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
909 KEY `branchcode` (`branchcode`),
910 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
911 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
914 -- Table structure for `import_record_matches`
916 DROP TABLE IF EXISTS `import_record_matches`;
917 CREATE TABLE `import_record_matches` (
918 `import_record_id` int(11) NOT NULL,
919 `candidate_match_id` int(11) NOT NULL,
920 `score` int(11) NOT NULL default 0,
921 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
922 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
923 KEY `record_score` (`import_record_id`, `score`)
924 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
927 -- Table structure for table `import_biblios`
930 DROP TABLE IF EXISTS `import_biblios`;
931 CREATE TABLE `import_biblios` (
932 `import_record_id` int(11) NOT NULL,
933 `matched_biblionumber` int(11) default NULL,
934 `control_number` varchar(25) default NULL,
935 `original_source` varchar(25) default NULL,
936 `title` varchar(128) default NULL,
937 `author` varchar(80) default NULL,
938 `isbn` varchar(14) default NULL,
939 `issn` varchar(9) default NULL,
940 `has_items` tinyint(1) NOT NULL default 0,
941 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
942 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
943 KEY `matched_biblionumber` (`matched_biblionumber`),
944 KEY `title` (`title`),
946 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
949 -- Table structure for table `import_items`
952 DROP TABLE IF EXISTS `import_items`;
953 CREATE TABLE `import_items` (
954 `import_items_id` int(11) NOT NULL auto_increment,
955 `import_record_id` int(11) NOT NULL,
956 `itemnumber` int(11) default NULL,
957 `branchcode` varchar(10) default NULL,
958 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
959 `marcxml` longtext NOT NULL,
960 `import_error` mediumtext,
961 PRIMARY KEY (`import_items_id`),
962 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
963 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
964 KEY `itemnumber` (`itemnumber`),
965 KEY `branchcode` (`branchcode`)
966 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
969 -- Table structure for table `issues`
972 DROP TABLE IF EXISTS `issues`;
973 CREATE TABLE `issues` (
974 `borrowernumber` int(11) default NULL,
975 `itemnumber` int(11) default NULL,
976 `date_due` date default NULL,
977 `branchcode` varchar(10) default NULL,
978 `issuingbranch` varchar(18) default NULL,
979 `returndate` date default NULL,
980 `lastreneweddate` date default NULL,
981 `return` varchar(4) default NULL,
982 `renewals` tinyint(4) default NULL,
983 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
984 `issuedate` date default NULL,
985 KEY `issuesborridx` (`borrowernumber`),
986 KEY `issuesitemidx` (`itemnumber`),
987 KEY `bordate` (`borrowernumber`,`timestamp`),
988 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
989 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
990 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
993 -- Table structure for table `issuingrules`
996 DROP TABLE IF EXISTS `issuingrules`;
997 CREATE TABLE `issuingrules` (
998 `categorycode` varchar(10) NOT NULL default '',
999 `itemtype` varchar(10) NOT NULL default '',
1000 `restrictedtype` tinyint(1) default NULL,
1001 `rentaldiscount` decimal(28,6) default NULL,
1002 `reservecharge` decimal(28,6) default NULL,
1003 `fine` decimal(28,6) default NULL,
1004 `firstremind` int(11) default NULL,
1005 `chargeperiod` int(11) default NULL,
1006 `accountsent` int(11) default NULL,
1007 `chargename` varchar(100) default NULL,
1008 `maxissueqty` int(4) default NULL,
1009 `issuelength` int(4) default NULL,
1010 `branchcode` varchar(10) NOT NULL default '',
1011 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1012 KEY `categorycode` (`categorycode`),
1013 KEY `itemtype` (`itemtype`)
1014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1017 -- Table structure for table `items`
1020 DROP TABLE IF EXISTS `items`;
1021 CREATE TABLE `items` (
1022 `itemnumber` int(11) NOT NULL auto_increment,
1023 `biblionumber` int(11) NOT NULL default 0,
1024 `biblioitemnumber` int(11) NOT NULL default 0,
1025 `barcode` varchar(20) default NULL,
1026 `dateaccessioned` date default NULL,
1027 `booksellerid` mediumtext default NULL,
1028 `homebranch` varchar(10) default NULL,
1029 `price` decimal(8,2) default NULL,
1030 `replacementprice` decimal(8,2) default NULL,
1031 `replacementpricedate` date default NULL,
1032 `datelastborrowed` date default NULL,
1033 `datelastseen` date default NULL,
1034 `stack` tinyint(1) default NULL,
1035 `notforloan` tinyint(1) NOT NULL default 0,
1036 `damaged` tinyint(1) NOT NULL default 0,
1037 `itemlost` tinyint(1) NOT NULL default 0,
1038 `wthdrawn` tinyint(1) NOT NULL default 0,
1039 `itemcallnumber` varchar(30) default NULL,
1040 `issues` smallint(6) default NULL,
1041 `renewals` smallint(6) default NULL,
1042 `reserves` smallint(6) default NULL,
1043 `restricted` tinyint(1) default NULL,
1044 `itemnotes` mediumtext,
1045 `holdingbranch` varchar(10) default NULL,
1046 `paidfor` mediumtext,
1047 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1048 `location` varchar(80) default NULL,
1049 `onloan` date default NULL,
1050 `cn_source` varchar(10) default NULL,
1051 `cn_sort` varchar(30) default NULL,
1052 `ccode` varchar(10) default NULL,
1053 `materials` varchar(10) default NULL,
1054 `uri` varchar(255) default NULL,
1055 `itype` varchar(10) default NULL,
1056 `more_subfields_xml` longtext default NULL,
1057 `enumchron` varchar(80) default NULL,
1058 `copynumber` smallint(6) default NULL,
1059 PRIMARY KEY (`itemnumber`),
1060 UNIQUE KEY `itembarcodeidx` (`barcode`),
1061 KEY `itembinoidx` (`biblioitemnumber`),
1062 KEY `itembibnoidx` (`biblionumber`),
1063 KEY `homebranch` (`homebranch`),
1064 KEY `holdingbranch` (`holdingbranch`),
1065 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1066 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1067 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1068 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1071 -- Table structure for table `itemtypes`
1074 DROP TABLE IF EXISTS `itemtypes`;
1075 CREATE TABLE `itemtypes` (
1076 `itemtype` varchar(10) NOT NULL default '',
1077 `description` mediumtext,
1078 `renewalsallowed` smallint(6) default NULL,
1079 `rentalcharge` double(16,4) default NULL,
1080 `notforloan` smallint(6) default NULL,
1081 `imageurl` varchar(200) default NULL,
1083 PRIMARY KEY (`itemtype`),
1084 UNIQUE KEY `itemtype` (`itemtype`)
1085 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1088 -- Table structure for table `labels`
1091 DROP TABLE IF EXISTS `labels`;
1092 CREATE TABLE `labels` (
1093 `labelid` int(11) NOT NULL auto_increment,
1094 `batch_id` varchar(10) NOT NULL default 1,
1095 `itemnumber` varchar(100) NOT NULL default '',
1096 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1097 PRIMARY KEY (`labelid`)
1098 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1101 -- Table structure for table `labels_conf`
1104 DROP TABLE IF EXISTS `labels_conf`;
1105 CREATE TABLE `labels_conf` (
1106 `id` int(4) NOT NULL auto_increment,
1107 `barcodetype` char(100) default '',
1108 `title` int(1) default '0',
1109 `subtitle` int(1) default '0',
1110 `itemtype` int(1) default '0',
1111 `barcode` int(1) default '0',
1112 `dewey` int(1) default '0',
1113 `class` int(1) default NULL,
1114 `subclass` int(1) default '0',
1115 `itemcallnumber` int(1) default '0',
1116 `author` int(1) default '0',
1117 `issn` int(1) default '0',
1118 `isbn` int(1) default '0',
1119 `startlabel` int(2) NOT NULL default '1',
1120 `printingtype` char(32) default 'BAR',
1121 `layoutname` char(20) NOT NULL default 'TEST',
1122 `guidebox` int(1) default '0',
1123 `active` tinyint(1) default '1',
1124 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1125 `ccode` char(4) collate utf8_unicode_ci default NULL,
1126 `callnum_split` int(1) default NULL,
1127 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1132 -- Table structure for table `labels_profile`
1135 DROP TABLE IF EXISTS `labels_profile`;
1136 CREATE TABLE `labels_profile` (
1137 `tmpl_id` int(4) NOT NULL,
1138 `prof_id` int(4) NOT NULL,
1139 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1140 UNIQUE KEY `prof_id` (`prof_id`)
1141 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1144 -- Table structure for table `labels_templates`
1147 DROP TABLE IF EXISTS `labels_templates`;
1148 CREATE TABLE `labels_templates` (
1149 `tmpl_id` int(4) NOT NULL auto_increment,
1150 `tmpl_code` char(100) default '',
1151 `tmpl_desc` char(100) default '',
1152 `page_width` float default '0',
1153 `page_height` float default '0',
1154 `label_width` float default '0',
1155 `label_height` float default '0',
1156 `topmargin` float default '0',
1157 `leftmargin` float default '0',
1158 `cols` int(2) default '0',
1159 `rows` int(2) default '0',
1160 `colgap` float default '0',
1161 `rowgap` float default '0',
1162 `active` int(1) default NULL,
1163 `units` char(20) default 'PX',
1164 `fontsize` int(4) NOT NULL default '3',
1165 `font` char(10) NOT NULL default 'TR',
1166 PRIMARY KEY (`tmpl_id`)
1167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1170 -- Table structure for table `letter`
1173 DROP TABLE IF EXISTS `letter`;
1174 CREATE TABLE `letter` (
1175 `module` varchar(20) NOT NULL default '',
1176 `code` varchar(20) NOT NULL default '',
1177 `name` varchar(100) NOT NULL default '',
1178 `title` varchar(200) NOT NULL default '',
1180 PRIMARY KEY (`module`,`code`)
1181 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1184 -- Table structure for table `marc_subfield_structure`
1187 DROP TABLE IF EXISTS `marc_subfield_structure`;
1188 CREATE TABLE `marc_subfield_structure` (
1189 `tagfield` varchar(3) NOT NULL default '',
1190 `tagsubfield` varchar(1) NOT NULL default '',
1191 `liblibrarian` varchar(255) NOT NULL default '',
1192 `libopac` varchar(255) NOT NULL default '',
1193 `repeatable` tinyint(4) NOT NULL default 0,
1194 `mandatory` tinyint(4) NOT NULL default 0,
1195 `kohafield` varchar(40) default NULL,
1196 `tab` tinyint(1) default NULL,
1197 `authorised_value` varchar(20) default NULL,
1198 `authtypecode` varchar(20) default NULL,
1199 `value_builder` varchar(80) default NULL,
1200 `isurl` tinyint(1) default NULL,
1201 `hidden` tinyint(1) default NULL,
1202 `frameworkcode` varchar(4) NOT NULL default '',
1203 `seealso` varchar(1100) default NULL,
1204 `link` varchar(80) default NULL,
1205 `defaultvalue` text default NULL,
1206 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1207 KEY `kohafield_2` (`kohafield`),
1208 KEY `tab` (`frameworkcode`,`tab`),
1209 KEY `kohafield` (`frameworkcode`,`kohafield`)
1210 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1213 -- Table structure for table `marc_tag_structure`
1216 DROP TABLE IF EXISTS `marc_tag_structure`;
1217 CREATE TABLE `marc_tag_structure` (
1218 `tagfield` varchar(3) NOT NULL default '',
1219 `liblibrarian` varchar(255) NOT NULL default '',
1220 `libopac` varchar(255) NOT NULL default '',
1221 `repeatable` tinyint(4) NOT NULL default 0,
1222 `mandatory` tinyint(4) NOT NULL default 0,
1223 `authorised_value` varchar(10) default NULL,
1224 `frameworkcode` varchar(4) NOT NULL default '',
1225 PRIMARY KEY (`frameworkcode`,`tagfield`)
1226 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1229 -- Table structure for table `marc_matchers`
1232 DROP TABLE IF EXISTS `marc_matchers`;
1233 CREATE TABLE `marc_matchers` (
1234 `matcher_id` int(11) NOT NULL auto_increment,
1235 `code` varchar(10) NOT NULL default '',
1236 `description` varchar(255) NOT NULL default '',
1237 `record_type` varchar(10) NOT NULL default 'biblio',
1238 `threshold` int(11) NOT NULL default 0,
1239 PRIMARY KEY (`matcher_id`),
1240 KEY `code` (`code`),
1241 KEY `record_type` (`record_type`)
1242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1245 -- Table structure for table `matchpoints`
1247 DROP TABLE IF EXISTS `matchpoints`;
1248 CREATE TABLE `matchpoints` (
1249 `matcher_id` int(11) NOT NULL,
1250 `matchpoint_id` int(11) NOT NULL auto_increment,
1251 `search_index` varchar(30) NOT NULL default '',
1252 `score` int(11) NOT NULL default 0,
1253 PRIMARY KEY (`matchpoint_id`),
1254 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1255 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1256 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1260 -- Table structure for table `matchpoint_components`
1262 DROP TABLE IF EXISTS `matchpoint_components`;
1263 CREATE TABLE `matchpoint_components` (
1264 `matchpoint_id` int(11) NOT NULL,
1265 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1266 sequence int(11) NOT NULL default 0,
1267 tag varchar(3) NOT NULL default '',
1268 subfields varchar(40) NOT NULL default '',
1269 offset int(4) NOT NULL default 0,
1270 length int(4) NOT NULL default 0,
1271 PRIMARY KEY (`matchpoint_component_id`),
1272 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1273 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1274 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1275 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1278 -- Table structure for table `matcher_component_norms`
1280 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1281 CREATE TABLE `matchpoint_component_norms` (
1282 `matchpoint_component_id` int(11) NOT NULL,
1283 `sequence` int(11) NOT NULL default 0,
1284 `norm_routine` varchar(50) NOT NULL default '',
1285 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1286 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1287 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1288 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1291 -- Table structure for table `matcher_matchpoints`
1293 DROP TABLE IF EXISTS `matcher_matchpoints`;
1294 CREATE TABLE `matcher_matchpoints` (
1295 `matcher_id` int(11) NOT NULL,
1296 `matchpoint_id` int(11) NOT NULL,
1297 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1298 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1299 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1300 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1301 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1304 -- Table structure for table `matchchecks`
1306 DROP TABLE IF EXISTS `matchchecks`;
1307 CREATE TABLE `matchchecks` (
1308 `matcher_id` int(11) NOT NULL,
1309 `matchcheck_id` int(11) NOT NULL auto_increment,
1310 `source_matchpoint_id` int(11) NOT NULL,
1311 `target_matchpoint_id` int(11) NOT NULL,
1312 PRIMARY KEY (`matchcheck_id`),
1313 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1314 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1315 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1316 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1317 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1318 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1319 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1322 -- Table structure for table `mediatypetable`
1325 DROP TABLE IF EXISTS `mediatypetable`;
1326 CREATE TABLE `mediatypetable` (
1327 `mediatypecode` varchar(5) NOT NULL default '',
1329 `itemtypecodes` text,
1330 PRIMARY KEY (`mediatypecode`)
1331 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1334 -- Table structure for table `notifys`
1337 DROP TABLE IF EXISTS `notifys`;
1338 CREATE TABLE `notifys` (
1339 `notify_id` int(11) NOT NULL default 0,
1340 `borrowernumber` int(11) NOT NULL default 0,
1341 `itemnumber` int(11) NOT NULL default 0,
1342 `notify_date` date default NULL,
1343 `notify_send_date` date default NULL,
1344 `notify_level` int(1) NOT NULL default 0,
1345 `method` varchar(20) NOT NULL default ''
1346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1349 -- Table structure for table `nozebra`
1352 DROP TABLE IF EXISTS `nozebra`;
1353 CREATE TABLE `nozebra` (
1354 `server` varchar(20) NOT NULL,
1355 `indexname` varchar(40) NOT NULL,
1356 `value` varchar(250) NOT NULL,
1357 `biblionumbers` longtext NOT NULL,
1358 KEY `indexname` (`server`,`indexname`),
1359 KEY `value` (`server`,`value`))
1360 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1363 -- Table structure for table `old_issues`
1366 DROP TABLE IF EXISTS `old_issues`;
1367 CREATE TABLE `old_issues` (
1368 `borrowernumber` int(11) default NULL,
1369 `itemnumber` int(11) default NULL,
1370 `date_due` date default NULL,
1371 `branchcode` varchar(10) default NULL,
1372 `issuingbranch` varchar(18) default NULL,
1373 `returndate` date default NULL,
1374 `lastreneweddate` date default NULL,
1375 `return` varchar(4) default NULL,
1376 `renewals` tinyint(4) default NULL,
1377 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1378 `issuedate` date default NULL,
1379 KEY `old_issuesborridx` (`borrowernumber`),
1380 KEY `old_issuesitemidx` (`itemnumber`),
1381 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1382 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1383 ON DELETE SET NULL ON UPDATE SET NULL,
1384 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1385 ON DELETE SET NULL ON UPDATE SET NULL
1386 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1389 -- Table structure for table `old_reserves`
1391 DROP TABLE IF EXISTS `old_reserves`;
1392 CREATE TABLE `old_reserves` (
1393 `borrowernumber` int(11) default NULL,
1394 `reservedate` date default NULL,
1395 `biblionumber` int(11) default NULL,
1396 `constrainttype` varchar(1) default NULL,
1397 `branchcode` varchar(10) default NULL,
1398 `notificationdate` date default NULL,
1399 `reminderdate` date default NULL,
1400 `cancellationdate` date default NULL,
1401 `reservenotes` mediumtext,
1402 `priority` smallint(6) default NULL,
1403 `found` varchar(1) default NULL,
1404 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1405 `itemnumber` int(11) default NULL,
1406 `waitingdate` date default NULL,
1407 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1408 KEY `old_reserves_biblionumber` (`biblionumber`),
1409 KEY `old_reserves_itemnumber` (`itemnumber`),
1410 KEY `old_reserves_branchcode` (`branchcode`),
1411 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1412 ON DELETE SET NULL ON UPDATE SET NULL,
1413 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1414 ON DELETE SET NULL ON UPDATE SET NULL,
1415 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1416 ON DELETE SET NULL ON UPDATE SET NULL
1417 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1420 -- Table structure for table `opac_news`
1423 DROP TABLE IF EXISTS `opac_news`;
1424 CREATE TABLE `opac_news` (
1425 `idnew` int(10) unsigned NOT NULL auto_increment,
1426 `title` varchar(250) NOT NULL default '',
1427 `new` text NOT NULL,
1428 `lang` varchar(25) NOT NULL default '',
1429 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1430 `expirationdate` date default NULL,
1431 `number` int(11) default NULL,
1432 PRIMARY KEY (`idnew`)
1433 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1436 -- Table structure for table `overduerules`
1439 DROP TABLE IF EXISTS `overduerules`;
1440 CREATE TABLE `overduerules` (
1441 `branchcode` varchar(10) NOT NULL default '',
1442 `categorycode` varchar(2) NOT NULL default '',
1443 `delay1` int(4) default 0,
1444 `letter1` varchar(20) default NULL,
1445 `debarred1` varchar(1) default 0,
1446 `delay2` int(4) default 0,
1447 `debarred2` varchar(1) default 0,
1448 `letter2` varchar(20) default NULL,
1449 `delay3` int(4) default 0,
1450 `letter3` varchar(20) default NULL,
1451 `debarred3` int(1) default 0,
1452 PRIMARY KEY (`branchcode`,`categorycode`)
1453 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1456 -- Table structure for table `patronimage`
1459 DROP TABLE IF EXISTS `patronimage`;
1460 CREATE TABLE `patronimage` (
1461 `cardnumber` varchar(16) NOT NULL,
1462 `mimetype` varchar(15) NOT NULL,
1463 `imagefile` mediumblob NOT NULL,
1464 PRIMARY KEY (`cardnumber`),
1465 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1466 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1469 -- Table structure for table `printers`
1472 DROP TABLE IF EXISTS `printers`;
1473 CREATE TABLE `printers` (
1474 `printername` varchar(40) NOT NULL default '',
1475 `printqueue` varchar(20) default NULL,
1476 `printtype` varchar(20) default NULL,
1477 PRIMARY KEY (`printername`)
1478 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1481 -- Table structure for table `printers_profile`
1484 DROP TABLE IF EXISTS `printers_profile`;
1485 CREATE TABLE `printers_profile` (
1486 `prof_id` int(4) NOT NULL auto_increment,
1487 `printername` varchar(40) NOT NULL,
1488 `tmpl_id` int(4) NOT NULL,
1489 `paper_bin` varchar(20) NOT NULL,
1490 `offset_horz` float default NULL,
1491 `offset_vert` float default NULL,
1492 `creep_horz` float default NULL,
1493 `creep_vert` float default NULL,
1494 `unit` char(20) NOT NULL default 'POINT',
1495 PRIMARY KEY (`prof_id`),
1496 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1497 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1498 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1501 -- Table structure for table `repeatable_holidays`
1504 DROP TABLE IF EXISTS `repeatable_holidays`;
1505 CREATE TABLE `repeatable_holidays` (
1506 `id` int(11) NOT NULL auto_increment,
1507 `branchcode` varchar(10) NOT NULL default '',
1508 `weekday` smallint(6) default NULL,
1509 `day` smallint(6) default NULL,
1510 `month` smallint(6) default NULL,
1511 `title` varchar(50) NOT NULL default '',
1512 `description` text NOT NULL,
1514 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1517 -- Table structure for table `reports_dictionary`
1520 DROP TABLE IF EXISTS `reports_dictionary`;
1521 CREATE TABLE reports_dictionary (
1522 `id` int(11) NOT NULL auto_increment,
1523 `name` varchar(255) default NULL,
1525 `date_created` datetime default NULL,
1526 `date_modified` datetime default NULL,
1528 `area` int(11) default NULL,
1530 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1533 -- Table structure for table `reserveconstraints`
1536 DROP TABLE IF EXISTS `reserveconstraints`;
1537 CREATE TABLE `reserveconstraints` (
1538 `borrowernumber` int(11) NOT NULL default 0,
1539 `reservedate` date default NULL,
1540 `biblionumber` int(11) NOT NULL default 0,
1541 `biblioitemnumber` int(11) default NULL,
1542 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1546 -- Table structure for table `reserves`
1549 DROP TABLE IF EXISTS `reserves`;
1550 CREATE TABLE `reserves` (
1551 `borrowernumber` int(11) NOT NULL default 0,
1552 `reservedate` date default NULL,
1553 `biblionumber` int(11) NOT NULL default 0,
1554 `constrainttype` varchar(1) default NULL,
1555 `branchcode` varchar(10) default NULL,
1556 `notificationdate` date default NULL,
1557 `reminderdate` date default NULL,
1558 `cancellationdate` date default NULL,
1559 `reservenotes` mediumtext,
1560 `priority` smallint(6) default NULL,
1561 `found` varchar(1) default NULL,
1562 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1563 `itemnumber` int(11) default NULL,
1564 `waitingdate` date default NULL,
1565 KEY `borrowernumber` (`borrowernumber`),
1566 KEY `biblionumber` (`biblionumber`),
1567 KEY `itemnumber` (`itemnumber`),
1568 KEY `branchcode` (`branchcode`),
1569 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1570 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1571 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1572 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1573 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1576 -- Table structure for table `reviews`
1579 DROP TABLE IF EXISTS `reviews`;
1580 CREATE TABLE `reviews` (
1581 `reviewid` int(11) NOT NULL auto_increment,
1582 `borrowernumber` int(11) default NULL,
1583 `biblionumber` int(11) default NULL,
1585 `approved` tinyint(4) default NULL,
1586 `datereviewed` datetime default NULL,
1587 PRIMARY KEY (`reviewid`)
1588 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1591 -- Table structure for table `roadtype`
1594 DROP TABLE IF EXISTS `roadtype`;
1595 CREATE TABLE `roadtype` (
1596 `roadtypeid` int(11) NOT NULL auto_increment,
1597 `road_type` varchar(100) NOT NULL default '',
1598 PRIMARY KEY (`roadtypeid`)
1599 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1602 -- Table structure for table `saved_sql`
1605 DROP TABLE IF EXISTS `saved_sql`;
1606 CREATE TABLE saved_sql (
1607 `id` int(11) NOT NULL auto_increment,
1608 `borrowernumber` int(11) default NULL,
1609 `date_created` datetime default NULL,
1610 `last_modified` datetime default NULL,
1612 `last_run` datetime default NULL,
1613 `report_name` varchar(255) default NULL,
1614 `type` varchar(255) default NULL,
1617 KEY boridx (`borrowernumber`)
1618 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1622 -- Table structure for `saved_reports`
1625 DROP TABLE IF EXISTS `saved_reports`;
1626 CREATE TABLE saved_reports (
1627 `id` int(11) NOT NULL auto_increment,
1628 `report_id` int(11) default NULL,
1630 `date_run` datetime default NULL,
1632 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1636 -- Table structure for table `serial`
1639 DROP TABLE IF EXISTS `serial`;
1640 CREATE TABLE `serial` (
1641 `serialid` int(11) NOT NULL auto_increment,
1642 `biblionumber` varchar(100) NOT NULL default '',
1643 `subscriptionid` varchar(100) NOT NULL default '',
1644 `serialseq` varchar(100) NOT NULL default '',
1645 `status` tinyint(4) NOT NULL default 0,
1646 `planneddate` date default NULL,
1648 `publisheddate` date default NULL,
1649 `itemnumber` text default NULL,
1650 `claimdate` date default NULL,
1651 `routingnotes` text,
1652 PRIMARY KEY (`serialid`)
1653 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1656 -- Table structure for table `sessions`
1659 DROP TABLE IF EXISTS sessions;
1660 CREATE TABLE sessions (
1661 `id` varchar(32) NOT NULL,
1662 `a_session` text NOT NULL,
1664 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1667 -- Table structure for table `special_holidays`
1670 DROP TABLE IF EXISTS `special_holidays`;
1671 CREATE TABLE `special_holidays` (
1672 `id` int(11) NOT NULL auto_increment,
1673 `branchcode` varchar(10) NOT NULL default '',
1674 `day` smallint(6) NOT NULL default 0,
1675 `month` smallint(6) NOT NULL default 0,
1676 `year` smallint(6) NOT NULL default 0,
1677 `isexception` smallint(1) NOT NULL default 1,
1678 `title` varchar(50) NOT NULL default '',
1679 `description` text NOT NULL,
1681 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1684 -- Table structure for table `statistics`
1687 DROP TABLE IF EXISTS `statistics`;
1688 CREATE TABLE `statistics` (
1689 `datetime` datetime default NULL,
1690 `branch` varchar(10) default NULL,
1691 `proccode` varchar(4) default NULL,
1692 `value` double(16,4) default NULL,
1693 `type` varchar(16) default NULL,
1695 `usercode` varchar(10) default NULL,
1696 `itemnumber` int(11) default NULL,
1697 `itemtype` varchar(10) default NULL,
1698 `borrowernumber` int(11) default NULL,
1699 `associatedborrower` int(11) default NULL,
1700 KEY `timeidx` (`datetime`)
1701 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1704 -- Table structure for table `stopwords`
1707 DROP TABLE IF EXISTS `stopwords`;
1708 CREATE TABLE `stopwords` (
1709 `word` varchar(255) default NULL
1710 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1713 -- Table structure for table `subcategorytable`
1716 DROP TABLE IF EXISTS `subcategorytable`;
1717 CREATE TABLE `subcategorytable` (
1718 `subcategorycode` varchar(5) NOT NULL default '',
1720 `itemtypecodes` text,
1721 PRIMARY KEY (`subcategorycode`)
1722 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1725 -- Table structure for table `subscription`
1728 DROP TABLE IF EXISTS `subscription`;
1729 CREATE TABLE `subscription` (
1730 `biblionumber` int(11) NOT NULL default 0,
1731 `subscriptionid` int(11) NOT NULL auto_increment,
1732 `librarian` varchar(100) default '',
1733 `startdate` date default NULL,
1734 `aqbooksellerid` int(11) default 0,
1735 `cost` int(11) default 0,
1736 `aqbudgetid` int(11) default 0,
1737 `weeklength` int(11) default 0,
1738 `monthlength` int(11) default 0,
1739 `numberlength` int(11) default 0,
1740 `periodicity` tinyint(4) default 0,
1741 `dow` varchar(100) default '',
1742 `numberingmethod` varchar(100) default '',
1744 `status` varchar(100) NOT NULL default '',
1745 `add1` int(11) default 0,
1746 `every1` int(11) default 0,
1747 `whenmorethan1` int(11) default 0,
1748 `setto1` int(11) default NULL,
1749 `lastvalue1` int(11) default NULL,
1750 `add2` int(11) default 0,
1751 `every2` int(11) default 0,
1752 `whenmorethan2` int(11) default 0,
1753 `setto2` int(11) default NULL,
1754 `lastvalue2` int(11) default NULL,
1755 `add3` int(11) default 0,
1756 `every3` int(11) default 0,
1757 `innerloop1` int(11) default 0,
1758 `innerloop2` int(11) default 0,
1759 `innerloop3` int(11) default 0,
1760 `whenmorethan3` int(11) default 0,
1761 `setto3` int(11) default NULL,
1762 `lastvalue3` int(11) default NULL,
1763 `issuesatonce` tinyint(3) NOT NULL default 1,
1764 `firstacquidate` date default NULL,
1765 `manualhistory` tinyint(1) NOT NULL default 0,
1766 `irregularity` text,
1767 `letter` varchar(20) default NULL,
1768 `numberpattern` tinyint(3) default 0,
1769 `distributedto` text,
1770 `internalnotes` longtext,
1772 `branchcode` varchar(10) NOT NULL default '',
1773 `hemisphere` tinyint(3) default 0,
1774 `lastbranch` varchar(10),
1775 PRIMARY KEY (`subscriptionid`)
1776 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1779 -- Table structure for table `subscriptionhistory`
1782 DROP TABLE IF EXISTS `subscriptionhistory`;
1783 CREATE TABLE `subscriptionhistory` (
1784 `biblionumber` int(11) NOT NULL default 0,
1785 `subscriptionid` int(11) NOT NULL default 0,
1786 `histstartdate` date default NULL,
1787 `enddate` date default NULL,
1788 `missinglist` longtext NOT NULL,
1789 `recievedlist` longtext NOT NULL,
1790 `opacnote` varchar(150) NOT NULL default '',
1791 `librariannote` varchar(150) NOT NULL default '',
1792 PRIMARY KEY (`subscriptionid`),
1793 KEY `biblionumber` (`biblionumber`)
1794 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1797 -- Table structure for table `subscriptionroutinglist`
1800 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1801 CREATE TABLE `subscriptionroutinglist` (
1802 `routingid` int(11) NOT NULL auto_increment,
1803 `borrowernumber` int(11) default NULL,
1804 `ranking` int(11) default NULL,
1805 `subscriptionid` int(11) default NULL,
1806 PRIMARY KEY (`routingid`)
1807 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1810 -- Table structure for table `suggestions`
1813 DROP TABLE IF EXISTS `suggestions`;
1814 CREATE TABLE `suggestions` (
1815 `suggestionid` int(8) NOT NULL auto_increment,
1816 `suggestedby` int(11) NOT NULL default 0,
1817 `managedby` int(11) default NULL,
1818 `STATUS` varchar(10) NOT NULL default '',
1820 `author` varchar(80) default NULL,
1821 `title` varchar(80) default NULL,
1822 `copyrightdate` smallint(6) default NULL,
1823 `publishercode` varchar(255) default NULL,
1824 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1825 `volumedesc` varchar(255) default NULL,
1826 `publicationyear` smallint(6) default 0,
1827 `place` varchar(255) default NULL,
1828 `isbn` varchar(10) default NULL,
1829 `mailoverseeing` smallint(1) default 0,
1830 `biblionumber` int(11) default NULL,
1832 PRIMARY KEY (`suggestionid`),
1833 KEY `suggestedby` (`suggestedby`),
1834 KEY `managedby` (`managedby`)
1835 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1838 -- Table structure for table `systempreferences`
1841 DROP TABLE IF EXISTS `systempreferences`;
1842 CREATE TABLE `systempreferences` (
1843 `variable` varchar(50) NOT NULL default '',
1845 `options` mediumtext,
1847 `type` varchar(20) default NULL,
1848 PRIMARY KEY (`variable`)
1849 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1852 -- Table structure for table `tags`
1855 DROP TABLE IF EXISTS `tags`;
1856 CREATE TABLE `tags` (
1857 `entry` varchar(255) NOT NULL default '',
1858 `weight` bigint(20) NOT NULL default 0,
1859 PRIMARY KEY (`entry`)
1860 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1863 -- Table structure for table `userflags`
1866 DROP TABLE IF EXISTS `userflags`;
1867 CREATE TABLE `userflags` (
1868 `bit` int(11) NOT NULL default 0,
1869 `flag` varchar(30) default NULL,
1870 `flagdesc` varchar(255) default NULL,
1871 `defaulton` int(11) default NULL,
1873 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1876 -- Table structure for table `virtualshelves`
1879 DROP TABLE IF EXISTS `virtualshelves`;
1880 CREATE TABLE `virtualshelves` (
1881 `shelfnumber` int(11) NOT NULL auto_increment,
1882 `shelfname` varchar(255) default NULL,
1883 `owner` varchar(80) default NULL,
1884 `category` varchar(1) default NULL,
1885 `sortfield` varchar(16) default NULL,
1886 PRIMARY KEY (`shelfnumber`)
1887 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1890 -- Table structure for table `virtualshelfcontents`
1893 DROP TABLE IF EXISTS `virtualshelfcontents`;
1894 CREATE TABLE `virtualshelfcontents` (
1895 `shelfnumber` int(11) NOT NULL default 0,
1896 `biblionumber` int(11) NOT NULL default 0,
1897 `flags` int(11) default NULL,
1898 `dateadded` timestamp NULL default NULL,
1899 KEY `shelfnumber` (`shelfnumber`),
1900 KEY `biblionumber` (`biblionumber`),
1901 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1902 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1903 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1906 -- Table structure for table `z3950servers`
1909 DROP TABLE IF EXISTS `z3950servers`;
1910 CREATE TABLE `z3950servers` (
1911 `host` varchar(255) default NULL,
1912 `port` int(11) default NULL,
1913 `db` varchar(255) default NULL,
1914 `userid` varchar(255) default NULL,
1915 `password` varchar(255) default NULL,
1917 `id` int(11) NOT NULL auto_increment,
1918 `checked` smallint(6) default NULL,
1919 `rank` int(11) default NULL,
1920 `syntax` varchar(80) default NULL,
1922 `position` enum('primary','secondary','') NOT NULL default 'primary',
1923 `type` enum('zed','opensearch') NOT NULL default 'zed',
1924 `encoding` text default NULL,
1925 `description` text NOT NULL,
1927 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1930 -- Table structure for table `zebraqueue`
1933 DROP TABLE IF EXISTS `zebraqueue`;
1934 CREATE TABLE `zebraqueue` (
1935 `id` int(11) NOT NULL auto_increment,
1936 `biblio_auth_number` int(11) NOT NULL default '0',
1937 `operation` char(20) NOT NULL default '',
1938 `server` char(20) NOT NULL default '',
1939 `done` int(11) NOT NULL default '0',
1940 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1942 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1943 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1945 DROP TABLE IF EXISTS `services_throttle`;
1946 CREATE TABLE `services_throttle` (
1947 `service_type` varchar(10) NOT NULL default '',
1948 `service_count` varchar(45) default NULL,
1949 PRIMARY KEY (`service_type`)
1950 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1952 -- http://www.w3.org/International/articles/language-tags/
1955 DROP TABLE IF EXISTS language_subtag_registry;
1956 CREATE TABLE language_subtag_registry (
1958 type varchar(25), -- language-script-region-variant-extension-privateuse
1959 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1961 KEY `subtag` (`subtag`)
1962 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1964 -- TODO: add suppress_scripts
1965 -- this maps three letter codes defined in iso639.2 back to their
1966 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1967 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1968 CREATE TABLE language_rfc4646_to_iso639 (
1969 rfc4646_subtag varchar(25),
1970 iso639_2_code varchar(25),
1971 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1972 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1974 DROP TABLE IF EXISTS language_descriptions;
1975 CREATE TABLE language_descriptions (
1979 description varchar(255),
1981 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1983 -- bi-directional support, keyed by script subcode
1984 DROP TABLE IF EXISTS language_script_bidi;
1985 CREATE TABLE language_script_bidi (
1986 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1987 bidi varchar(3), -- rtl ltr
1988 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1989 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1991 -- TODO: need to map language subtags to script subtags for detection
1992 -- of bidi when script is not specified (like ar, he)
1993 DROP TABLE IF EXISTS language_script_mapping;
1994 CREATE TABLE language_script_mapping (
1995 language_subtag varchar(25),
1996 script_subtag varchar(25),
1997 KEY `language_subtag` (`language_subtag`)
1998 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2000 DROP TABLE IF EXISTS serialitems;
2001 CREATE TABLE serialitems (
2002 serialid int(11) NOT NULL,
2003 itemnumber int(11) NOT NULL,
2004 UNIQUE KEY `serialididx` (`serialid`)
2005 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2007 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2008 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2009 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2010 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2011 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2012 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2013 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2014 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;