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,
840 PRIMARY KEY (`itemnumber`),
841 KEY `delitembarcodeidx` (`barcode`),
842 KEY `delitembinoidx` (`biblioitemnumber`),
843 KEY `delitembibnoidx` (`biblionumber`),
844 KEY `delhomebranch` (`homebranch`),
845 KEY `delholdingbranch` (`holdingbranch`)
846 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
849 -- Table structure for table `ethnicity`
852 DROP TABLE IF EXISTS `ethnicity`;
853 CREATE TABLE `ethnicity` (
854 `code` varchar(10) NOT NULL default '',
855 `name` varchar(255) default NULL,
857 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
860 -- Table structure for table `import_batches`
863 DROP TABLE IF EXISTS `import_batches`;
864 CREATE TABLE `import_batches` (
865 `import_batch_id` int(11) NOT NULL auto_increment,
866 `matcher_id` int(11) default NULL,
867 `template_id` int(11) default NULL,
868 `branchcode` varchar(10) default NULL,
869 `num_biblios` int(11) NOT NULL default 0,
870 `num_items` int(11) NOT NULL default 0,
871 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
872 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
873 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
874 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
875 `file_name` varchar(100),
876 `comments` mediumtext,
877 PRIMARY KEY (`import_batch_id`),
878 KEY `branchcode` (`branchcode`)
879 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
882 -- Table structure for table `import_records`
885 DROP TABLE IF EXISTS `import_records`;
886 CREATE TABLE `import_records` (
887 `import_record_id` int(11) NOT NULL auto_increment,
888 `import_batch_id` int(11) NOT NULL,
889 `branchcode` varchar(10) default NULL,
890 `record_sequence` int(11) NOT NULL default 0,
891 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
892 `import_date` DATE default NULL,
893 `marc` longblob NOT NULL,
894 `marcxml` longtext NOT NULL,
895 `marcxml_old` longtext NOT NULL,
896 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
897 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
898 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
899 `import_error` mediumtext,
900 `encoding` varchar(40) NOT NULL default '',
901 `z3950random` varchar(40) default NULL,
902 PRIMARY KEY (`import_record_id`),
903 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
904 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
905 KEY `branchcode` (`branchcode`),
906 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
907 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
910 -- Table structure for `import_record_matches`
912 DROP TABLE IF EXISTS `import_record_matches`;
913 CREATE TABLE `import_record_matches` (
914 `import_record_id` int(11) NOT NULL,
915 `candidate_match_id` int(11) NOT NULL,
916 `score` int(11) NOT NULL default 0,
917 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
918 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
919 KEY `record_score` (`import_record_id`, `score`)
920 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
923 -- Table structure for table `import_biblios`
926 DROP TABLE IF EXISTS `import_biblios`;
927 CREATE TABLE `import_biblios` (
928 `import_record_id` int(11) NOT NULL,
929 `matched_biblionumber` int(11) default NULL,
930 `control_number` varchar(25) default NULL,
931 `original_source` varchar(25) default NULL,
932 `title` varchar(128) default NULL,
933 `author` varchar(80) default NULL,
934 `isbn` varchar(14) default NULL,
935 `issn` varchar(9) default NULL,
936 `has_items` tinyint(1) NOT NULL default 0,
937 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
938 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
939 KEY `matched_biblionumber` (`matched_biblionumber`),
940 KEY `title` (`title`),
942 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
945 -- Table structure for table `import_items`
948 DROP TABLE IF EXISTS `import_items`;
949 CREATE TABLE `import_items` (
950 `import_items_id` int(11) NOT NULL auto_increment,
951 `import_record_id` int(11) NOT NULL,
952 `itemnumber` int(11) default NULL,
953 `branchcode` varchar(10) default NULL,
954 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
955 `marcxml` longtext NOT NULL,
956 `import_error` mediumtext,
957 PRIMARY KEY (`import_items_id`),
958 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
959 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
960 KEY `itemnumber` (`itemnumber`),
961 KEY `branchcode` (`branchcode`)
962 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
965 -- Table structure for table `issues`
968 DROP TABLE IF EXISTS `issues`;
969 CREATE TABLE `issues` (
970 `borrowernumber` int(11) default NULL,
971 `itemnumber` int(11) default NULL,
972 `date_due` date default NULL,
973 `branchcode` varchar(10) default NULL,
974 `issuingbranch` varchar(18) default NULL,
975 `returndate` date default NULL,
976 `lastreneweddate` date default NULL,
977 `return` varchar(4) default NULL,
978 `renewals` tinyint(4) default NULL,
979 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
980 `issuedate` date default NULL,
981 KEY `issuesborridx` (`borrowernumber`),
982 KEY `issuesitemidx` (`itemnumber`),
983 KEY `bordate` (`borrowernumber`,`timestamp`),
984 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
985 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
986 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
989 -- Table structure for table `issuingrules`
992 DROP TABLE IF EXISTS `issuingrules`;
993 CREATE TABLE `issuingrules` (
994 `categorycode` varchar(10) NOT NULL default '',
995 `itemtype` varchar(10) NOT NULL default '',
996 `restrictedtype` tinyint(1) default NULL,
997 `rentaldiscount` decimal(28,6) default NULL,
998 `reservecharge` decimal(28,6) default NULL,
999 `fine` decimal(28,6) default NULL,
1000 `firstremind` int(11) default NULL,
1001 `chargeperiod` int(11) default NULL,
1002 `accountsent` int(11) default NULL,
1003 `chargename` varchar(100) default NULL,
1004 `maxissueqty` int(4) default NULL,
1005 `issuelength` int(4) default NULL,
1006 `branchcode` varchar(10) NOT NULL default '',
1007 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1008 KEY `categorycode` (`categorycode`),
1009 KEY `itemtype` (`itemtype`)
1010 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1013 -- Table structure for table `items`
1016 DROP TABLE IF EXISTS `items`;
1017 CREATE TABLE `items` (
1018 `itemnumber` int(11) NOT NULL auto_increment,
1019 `biblionumber` int(11) NOT NULL default 0,
1020 `biblioitemnumber` int(11) NOT NULL default 0,
1021 `barcode` varchar(20) default NULL,
1022 `dateaccessioned` date default NULL,
1023 `booksellerid` varchar(10) default NULL,
1024 `homebranch` varchar(10) default NULL,
1025 `price` decimal(8,2) default NULL,
1026 `replacementprice` decimal(8,2) default NULL,
1027 `replacementpricedate` date default NULL,
1028 `datelastborrowed` date default NULL,
1029 `datelastseen` date default NULL,
1030 `stack` tinyint(1) default NULL,
1031 `notforloan` tinyint(1) NOT NULL default 0,
1032 `damaged` tinyint(1) NOT NULL default 0,
1033 `itemlost` tinyint(1) NOT NULL default 0,
1034 `wthdrawn` tinyint(1) NOT NULL default 0,
1035 `itemcallnumber` varchar(30) default NULL,
1036 `issues` smallint(6) default NULL,
1037 `renewals` smallint(6) default NULL,
1038 `reserves` smallint(6) default NULL,
1039 `restricted` tinyint(1) default NULL,
1040 `itemnotes` mediumtext,
1041 `holdingbranch` varchar(10) default NULL,
1042 `paidfor` mediumtext,
1043 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1044 `location` varchar(80) default NULL,
1045 `onloan` date default NULL,
1046 `cn_source` varchar(10) default NULL,
1047 `cn_sort` varchar(30) default NULL,
1048 `ccode` varchar(10) default NULL,
1049 `materials` varchar(10) default NULL,
1050 `uri` varchar(255) default NULL,
1051 `itype` varchar(10) default NULL,
1052 `more_subfields_xml` longtext default NULL,
1053 PRIMARY KEY (`itemnumber`),
1054 UNIQUE KEY `itembarcodeidx` (`barcode`),
1055 KEY `itembinoidx` (`biblioitemnumber`),
1056 KEY `itembibnoidx` (`biblionumber`),
1057 KEY `homebranch` (`homebranch`),
1058 KEY `holdingbranch` (`holdingbranch`),
1059 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1060 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1061 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1062 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1065 -- Table structure for table `itemtypes`
1068 DROP TABLE IF EXISTS `itemtypes`;
1069 CREATE TABLE `itemtypes` (
1070 `itemtype` varchar(10) NOT NULL default '',
1071 `description` mediumtext,
1072 `renewalsallowed` smallint(6) default NULL,
1073 `rentalcharge` double(16,4) default NULL,
1074 `notforloan` smallint(6) default NULL,
1075 `imageurl` varchar(200) default NULL,
1077 PRIMARY KEY (`itemtype`),
1078 UNIQUE KEY `itemtype` (`itemtype`)
1079 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1082 -- Table structure for table `labels`
1085 DROP TABLE IF EXISTS `labels`;
1086 CREATE TABLE `labels` (
1087 `labelid` int(11) NOT NULL auto_increment,
1088 `batch_id` varchar(10) NOT NULL default 1,
1089 `itemnumber` varchar(100) NOT NULL default '',
1090 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1091 PRIMARY KEY (`labelid`)
1092 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1095 -- Table structure for table `labels_conf`
1098 DROP TABLE IF EXISTS `labels_conf`;
1099 CREATE TABLE `labels_conf` (
1100 `id` int(4) NOT NULL auto_increment,
1101 `barcodetype` char(100) default '',
1102 `title` int(1) default '0',
1103 `subtitle` int(1) default '0',
1104 `itemtype` int(1) default '0',
1105 `barcode` int(1) default '0',
1106 `dewey` int(1) default '0',
1107 `class` int(1) default NULL,
1108 `subclass` int(1) default '0',
1109 `itemcallnumber` int(1) default '0',
1110 `author` int(1) default '0',
1111 `issn` int(1) default '0',
1112 `isbn` int(1) default '0',
1113 `startlabel` int(2) NOT NULL default '1',
1114 `printingtype` char(32) default 'BAR',
1115 `layoutname` char(20) NOT NULL default 'TEST',
1116 `guidebox` int(1) default '0',
1117 `active` tinyint(1) default '1',
1118 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1119 `ccode` char(4) collate utf8_unicode_ci default NULL,
1120 `callnum_split` int(1) default NULL,
1121 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1126 -- Table structure for table `labels_templates`
1129 DROP TABLE IF EXISTS `labels_templates`;
1130 CREATE TABLE `labels_templates` (
1131 `tmpl_id` int(4) NOT NULL auto_increment,
1132 `tmpl_code` char(100) default '',
1133 `tmpl_desc` char(100) default '',
1134 `page_width` float default '0',
1135 `page_height` float default '0',
1136 `label_width` float default '0',
1137 `label_height` float default '0',
1138 `topmargin` float default '0',
1139 `leftmargin` float default '0',
1140 `cols` int(2) default '0',
1141 `rows` int(2) default '0',
1142 `colgap` float default '0',
1143 `rowgap` float default '0',
1144 `active` int(1) default NULL,
1145 `units` char(20) default 'PX',
1146 `fontsize` int(4) NOT NULL default '3',
1147 PRIMARY KEY (`tmpl_id`)
1148 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1151 -- Table structure for table `letter`
1154 DROP TABLE IF EXISTS `letter`;
1155 CREATE TABLE `letter` (
1156 `module` varchar(20) NOT NULL default '',
1157 `code` varchar(20) NOT NULL default '',
1158 `name` varchar(100) NOT NULL default '',
1159 `title` varchar(200) NOT NULL default '',
1161 PRIMARY KEY (`module`,`code`)
1162 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1165 -- Table structure for table `marc_subfield_structure`
1168 DROP TABLE IF EXISTS `marc_subfield_structure`;
1169 CREATE TABLE `marc_subfield_structure` (
1170 `tagfield` varchar(3) NOT NULL default '',
1171 `tagsubfield` varchar(1) NOT NULL default '',
1172 `liblibrarian` varchar(255) NOT NULL default '',
1173 `libopac` varchar(255) NOT NULL default '',
1174 `repeatable` tinyint(4) NOT NULL default 0,
1175 `mandatory` tinyint(4) NOT NULL default 0,
1176 `kohafield` varchar(40) default NULL,
1177 `tab` tinyint(1) default NULL,
1178 `authorised_value` varchar(20) default NULL,
1179 `authtypecode` varchar(20) default NULL,
1180 `value_builder` varchar(80) default NULL,
1181 `isurl` tinyint(1) default NULL,
1182 `hidden` tinyint(1) default NULL,
1183 `frameworkcode` varchar(4) NOT NULL default '',
1184 `seealso` varchar(1100) default NULL,
1185 `link` varchar(80) default NULL,
1186 `defaultvalue` text default NULL,
1187 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1188 KEY `kohafield_2` (`kohafield`),
1189 KEY `tab` (`frameworkcode`,`tab`),
1190 KEY `kohafield` (`frameworkcode`,`kohafield`)
1191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1194 -- Table structure for table `marc_tag_structure`
1197 DROP TABLE IF EXISTS `marc_tag_structure`;
1198 CREATE TABLE `marc_tag_structure` (
1199 `tagfield` varchar(3) NOT NULL default '',
1200 `liblibrarian` varchar(255) NOT NULL default '',
1201 `libopac` varchar(255) NOT NULL default '',
1202 `repeatable` tinyint(4) NOT NULL default 0,
1203 `mandatory` tinyint(4) NOT NULL default 0,
1204 `authorised_value` varchar(10) default NULL,
1205 `frameworkcode` varchar(4) NOT NULL default '',
1206 PRIMARY KEY (`frameworkcode`,`tagfield`)
1207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1210 -- Table structure for table `marc_matchers`
1213 DROP TABLE IF EXISTS `marc_matchers`;
1214 CREATE TABLE `marc_matchers` (
1215 `matcher_id` int(11) NOT NULL auto_increment,
1216 `code` varchar(10) NOT NULL default '',
1217 `description` varchar(255) NOT NULL default '',
1218 `record_type` varchar(10) NOT NULL default 'biblio',
1219 `threshold` int(11) NOT NULL default 0,
1220 PRIMARY KEY (`matcher_id`),
1221 KEY `code` (`code`),
1222 KEY `record_type` (`record_type`)
1223 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1226 -- Table structure for table `matchpoints`
1228 DROP TABLE IF EXISTS `matchpoints`;
1229 CREATE TABLE `matchpoints` (
1230 `matcher_id` int(11) NOT NULL,
1231 `matchpoint_id` int(11) NOT NULL auto_increment,
1232 `search_index` varchar(30) NOT NULL default '',
1233 `score` int(11) NOT NULL default 0,
1234 PRIMARY KEY (`matchpoint_id`),
1235 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1236 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1237 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1241 -- Table structure for table `matchpoint_components`
1243 DROP TABLE IF EXISTS `matchpoint_components`;
1244 CREATE TABLE `matchpoint_components` (
1245 `matchpoint_id` int(11) NOT NULL,
1246 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1247 sequence int(11) NOT NULL default 0,
1248 tag varchar(3) NOT NULL default '',
1249 subfields varchar(40) NOT NULL default '',
1250 offset int(4) NOT NULL default 0,
1251 length int(4) NOT NULL default 0,
1252 PRIMARY KEY (`matchpoint_component_id`),
1253 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1254 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1255 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1256 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1259 -- Table structure for table `matcher_component_norms`
1261 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1262 CREATE TABLE `matchpoint_component_norms` (
1263 `matchpoint_component_id` int(11) NOT NULL,
1264 `sequence` int(11) NOT NULL default 0,
1265 `norm_routine` varchar(50) NOT NULL default '',
1266 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1267 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1268 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1272 -- Table structure for table `matcher_matchpoints`
1274 DROP TABLE IF EXISTS `matcher_matchpoints`;
1275 CREATE TABLE `matcher_matchpoints` (
1276 `matcher_id` int(11) NOT NULL,
1277 `matchpoint_id` int(11) NOT NULL,
1278 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1279 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1280 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1281 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1282 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1285 -- Table structure for table `matchchecks`
1287 DROP TABLE IF EXISTS `matchchecks`;
1288 CREATE TABLE `matchchecks` (
1289 `matcher_id` int(11) NOT NULL,
1290 `matchcheck_id` int(11) NOT NULL auto_increment,
1291 `source_matchpoint_id` int(11) NOT NULL,
1292 `target_matchpoint_id` int(11) NOT NULL,
1293 PRIMARY KEY (`matchcheck_id`),
1294 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1295 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1296 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1297 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1298 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1299 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1303 -- Table structure for table `mediatypetable`
1306 DROP TABLE IF EXISTS `mediatypetable`;
1307 CREATE TABLE `mediatypetable` (
1308 `mediatypecode` varchar(5) NOT NULL default '',
1310 `itemtypecodes` text,
1311 PRIMARY KEY (`mediatypecode`)
1312 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1315 -- Table structure for table `notifys`
1318 DROP TABLE IF EXISTS `notifys`;
1319 CREATE TABLE `notifys` (
1320 `notify_id` int(11) NOT NULL default 0,
1321 `borrowernumber` int(11) NOT NULL default 0,
1322 `itemnumber` int(11) NOT NULL default 0,
1323 `notify_date` date default NULL,
1324 `notify_send_date` date default NULL,
1325 `notify_level` int(1) NOT NULL default 0,
1326 `method` varchar(20) NOT NULL default ''
1327 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1330 -- Table structure for table `nozebra`
1332 DROP TABLE IF EXISTS `nozebra`;
1333 CREATE TABLE `nozebra` (
1334 `server` varchar(20) NOT NULL,
1335 `indexname` varchar(40) NOT NULL,
1336 `value` varchar(250) NOT NULL,
1337 `biblionumbers` longtext NOT NULL,
1338 KEY `indexname` (`server`,`indexname`),
1339 KEY `value` (`server`,`value`))
1340 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1343 -- Table structure for table `opac_news`
1346 DROP TABLE IF EXISTS `opac_news`;
1347 CREATE TABLE `opac_news` (
1348 `idnew` int(10) unsigned NOT NULL auto_increment,
1349 `title` varchar(250) NOT NULL default '',
1350 `new` text NOT NULL,
1351 `lang` varchar(4) NOT NULL default '',
1352 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1353 `expirationdate` date default NULL,
1354 `number` int(11) default NULL,
1355 PRIMARY KEY (`idnew`)
1356 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1359 -- Table structure for table `overduerules`
1362 DROP TABLE IF EXISTS `overduerules`;
1363 CREATE TABLE `overduerules` (
1364 `branchcode` varchar(10) NOT NULL default '',
1365 `categorycode` varchar(2) NOT NULL default '',
1366 `delay1` int(4) default 0,
1367 `letter1` varchar(20) default NULL,
1368 `debarred1` varchar(1) default 0,
1369 `delay2` int(4) default 0,
1370 `debarred2` varchar(1) default 0,
1371 `letter2` varchar(20) default NULL,
1372 `delay3` int(4) default 0,
1373 `letter3` varchar(20) default NULL,
1374 `debarred3` int(1) default 0,
1375 PRIMARY KEY (`branchcode`,`categorycode`)
1376 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1379 -- Table structure for table `printers`
1382 DROP TABLE IF EXISTS `printers`;
1383 CREATE TABLE `printers` (
1384 `printername` varchar(40) NOT NULL default '',
1385 `printqueue` varchar(20) default NULL,
1386 `printtype` varchar(20) default NULL,
1387 PRIMARY KEY (`printername`)
1388 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1391 -- Table structure for table `repeatable_holidays`
1394 DROP TABLE IF EXISTS `repeatable_holidays`;
1395 CREATE TABLE `repeatable_holidays` (
1396 `id` int(11) NOT NULL auto_increment,
1397 `branchcode` varchar(10) NOT NULL default '',
1398 `weekday` smallint(6) default NULL,
1399 `day` smallint(6) default NULL,
1400 `month` smallint(6) default NULL,
1401 `title` varchar(50) NOT NULL default '',
1402 `description` text NOT NULL,
1404 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1407 -- Table structure for table `reports_dictionary`
1410 DROP TABLE IF EXISTS `reports_dictionary`;
1411 CREATE TABLE reports_dictionary (
1412 `id` int(11) NOT NULL auto_increment,
1413 `name` varchar(255) default NULL,
1415 `date_created` datetime default NULL,
1416 `date_modified` datetime default NULL,
1418 `area` int(11) default NULL,
1420 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1423 -- Table structure for table `reserveconstraints`
1426 DROP TABLE IF EXISTS `reserveconstraints`;
1427 CREATE TABLE `reserveconstraints` (
1428 `borrowernumber` int(11) NOT NULL default 0,
1429 `reservedate` date default NULL,
1430 `biblionumber` int(11) NOT NULL default 0,
1431 `biblioitemnumber` int(11) default NULL,
1432 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1433 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1436 -- Table structure for table `reserves`
1439 DROP TABLE IF EXISTS `reserves`;
1440 CREATE TABLE `reserves` (
1441 `borrowernumber` int(11) NOT NULL default 0,
1442 `reservedate` date default NULL,
1443 `biblionumber` int(11) NOT NULL default 0,
1444 `constrainttype` varchar(1) default NULL,
1445 `branchcode` varchar(10) default NULL,
1446 `notificationdate` date default NULL,
1447 `reminderdate` date default NULL,
1448 `cancellationdate` date default NULL,
1449 `reservenotes` mediumtext,
1450 `priority` smallint(6) default NULL,
1451 `found` varchar(1) default NULL,
1452 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1453 `itemnumber` int(11) default NULL,
1454 `waitingdate` date default NULL,
1455 KEY `borrowernumber` (`borrowernumber`),
1456 KEY `biblionumber` (`biblionumber`),
1457 KEY `itemnumber` (`itemnumber`),
1458 KEY `branchcode` (`branchcode`),
1459 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1460 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1461 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1462 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1463 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1466 -- Table structure for table `reviews`
1469 DROP TABLE IF EXISTS `reviews`;
1470 CREATE TABLE `reviews` (
1471 `reviewid` int(11) NOT NULL auto_increment,
1472 `borrowernumber` int(11) default NULL,
1473 `biblionumber` int(11) default NULL,
1475 `approved` tinyint(4) default NULL,
1476 `datereviewed` datetime default NULL,
1477 PRIMARY KEY (`reviewid`)
1478 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1481 -- Table structure for table `roadtype`
1484 DROP TABLE IF EXISTS `roadtype`;
1485 CREATE TABLE `roadtype` (
1486 `roadtypeid` int(11) NOT NULL auto_increment,
1487 `road_type` varchar(100) NOT NULL default '',
1488 PRIMARY KEY (`roadtypeid`)
1489 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1492 -- Table structure for table `saved_sql`
1495 DROP TABLE IF EXISTS `saved_sql`;
1496 CREATE TABLE saved_sql (
1497 `id` int(11) NOT NULL auto_increment,
1498 `borrowernumber` int(11) default NULL,
1499 `date_created` datetime default NULL,
1500 `last_modified` datetime default NULL,
1502 `last_run` datetime default NULL,
1503 `report_name` varchar(255) default NULL,
1504 `type` varchar(255) default NULL,
1507 KEY boridx (`borrowernumber`)
1508 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1512 -- Table structure for `saved_reports`
1515 DROP TABLE IF EXISTS `saved_reports`;
1516 CREATE TABLE saved_reports (
1517 `id` int(11) NOT NULL auto_increment,
1518 `report_id` int(11) default NULL,
1520 `date_run` datetime default NULL,
1522 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1526 -- Table structure for table `serial`
1529 DROP TABLE IF EXISTS `serial`;
1530 CREATE TABLE `serial` (
1531 `serialid` int(11) NOT NULL auto_increment,
1532 `biblionumber` varchar(100) NOT NULL default '',
1533 `subscriptionid` varchar(100) NOT NULL default '',
1534 `serialseq` varchar(100) NOT NULL default '',
1535 `status` tinyint(4) NOT NULL default 0,
1536 `planneddate` date default NULL,
1538 `publisheddate` date default NULL,
1539 `itemnumber` text default NULL,
1540 `claimdate` date default NULL,
1541 `routingnotes` text,
1542 PRIMARY KEY (`serialid`)
1543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1546 -- Table structure for table `sessions`
1549 DROP TABLE IF EXISTS sessions;
1550 CREATE TABLE sessions (
1551 `id` varchar(32) NOT NULL,
1552 `a_session` text NOT NULL,
1554 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1557 -- Table structure for table `special_holidays`
1560 DROP TABLE IF EXISTS `special_holidays`;
1561 CREATE TABLE `special_holidays` (
1562 `id` int(11) NOT NULL auto_increment,
1563 `branchcode` varchar(10) NOT NULL default '',
1564 `day` smallint(6) NOT NULL default 0,
1565 `month` smallint(6) NOT NULL default 0,
1566 `year` smallint(6) NOT NULL default 0,
1567 `isexception` smallint(1) NOT NULL default 1,
1568 `title` varchar(50) NOT NULL default '',
1569 `description` text NOT NULL,
1571 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1574 -- Table structure for table `statistics`
1577 DROP TABLE IF EXISTS `statistics`;
1578 CREATE TABLE `statistics` (
1579 `datetime` datetime default NULL,
1580 `branch` varchar(10) default NULL,
1581 `proccode` varchar(4) default NULL,
1582 `value` double(16,4) default NULL,
1583 `type` varchar(16) default NULL,
1585 `usercode` varchar(10) default NULL,
1586 `itemnumber` int(11) default NULL,
1587 `itemtype` varchar(10) default NULL,
1588 `borrowernumber` int(11) default NULL,
1589 `associatedborrower` int(11) default NULL,
1590 KEY `timeidx` (`datetime`)
1591 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1594 -- Table structure for table `stopwords`
1597 DROP TABLE IF EXISTS `stopwords`;
1598 CREATE TABLE `stopwords` (
1599 `word` varchar(255) default NULL
1600 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1603 -- Table structure for table `subcategorytable`
1606 DROP TABLE IF EXISTS `subcategorytable`;
1607 CREATE TABLE `subcategorytable` (
1608 `subcategorycode` varchar(5) NOT NULL default '',
1610 `itemtypecodes` text,
1611 PRIMARY KEY (`subcategorycode`)
1612 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1615 -- Table structure for table `subscription`
1618 DROP TABLE IF EXISTS `subscription`;
1619 CREATE TABLE `subscription` (
1620 `biblionumber` int(11) NOT NULL default 0,
1621 `subscriptionid` int(11) NOT NULL auto_increment,
1622 `librarian` varchar(100) default '',
1623 `startdate` date default NULL,
1624 `aqbooksellerid` int(11) default 0,
1625 `cost` int(11) default 0,
1626 `aqbudgetid` int(11) default 0,
1627 `weeklength` int(11) default 0,
1628 `monthlength` int(11) default 0,
1629 `numberlength` int(11) default 0,
1630 `periodicity` tinyint(4) default 0,
1631 `dow` varchar(100) default '',
1632 `numberingmethod` varchar(100) default '',
1634 `status` varchar(100) NOT NULL default '',
1635 `add1` int(11) default 0,
1636 `every1` int(11) default 0,
1637 `whenmorethan1` int(11) default 0,
1638 `setto1` int(11) default NULL,
1639 `lastvalue1` int(11) default NULL,
1640 `add2` int(11) default 0,
1641 `every2` int(11) default 0,
1642 `whenmorethan2` int(11) default 0,
1643 `setto2` int(11) default NULL,
1644 `lastvalue2` int(11) default NULL,
1645 `add3` int(11) default 0,
1646 `every3` int(11) default 0,
1647 `innerloop1` int(11) default 0,
1648 `innerloop2` int(11) default 0,
1649 `innerloop3` int(11) default 0,
1650 `whenmorethan3` int(11) default 0,
1651 `setto3` int(11) default NULL,
1652 `lastvalue3` int(11) default NULL,
1653 `issuesatonce` tinyint(3) NOT NULL default 1,
1654 `firstacquidate` date default NULL,
1655 `manualhistory` tinyint(1) NOT NULL default 0,
1656 `irregularity` text,
1657 `letter` varchar(20) default NULL,
1658 `numberpattern` tinyint(3) default 0,
1659 `distributedto` text,
1660 `internalnotes` longtext,
1662 `branchcode` varchar(10) NOT NULL default '',
1663 `hemisphere` tinyint(3) default 0,
1664 `lastbranch` varchar(10),
1665 PRIMARY KEY (`subscriptionid`)
1666 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1669 -- Table structure for table `subscriptionhistory`
1672 DROP TABLE IF EXISTS `subscriptionhistory`;
1673 CREATE TABLE `subscriptionhistory` (
1674 `biblionumber` int(11) NOT NULL default 0,
1675 `subscriptionid` int(11) NOT NULL default 0,
1676 `histstartdate` date default NULL,
1677 `enddate` date default NULL,
1678 `missinglist` longtext NOT NULL,
1679 `recievedlist` longtext NOT NULL,
1680 `opacnote` varchar(150) NOT NULL default '',
1681 `librariannote` varchar(150) NOT NULL default '',
1682 PRIMARY KEY (`subscriptionid`),
1683 KEY `biblionumber` (`biblionumber`)
1684 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1687 -- Table structure for table `subscriptionroutinglist`
1690 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1691 CREATE TABLE `subscriptionroutinglist` (
1692 `routingid` int(11) NOT NULL auto_increment,
1693 `borrowernumber` int(11) default NULL,
1694 `ranking` int(11) default NULL,
1695 `subscriptionid` int(11) default NULL,
1696 PRIMARY KEY (`routingid`)
1697 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1700 -- Table structure for table `suggestions`
1703 DROP TABLE IF EXISTS `suggestions`;
1704 CREATE TABLE `suggestions` (
1705 `suggestionid` int(8) NOT NULL auto_increment,
1706 `suggestedby` int(11) NOT NULL default 0,
1707 `managedby` int(11) default NULL,
1708 `STATUS` varchar(10) NOT NULL default '',
1710 `author` varchar(80) default NULL,
1711 `title` varchar(80) default NULL,
1712 `copyrightdate` smallint(6) default NULL,
1713 `publishercode` varchar(255) default NULL,
1714 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1715 `volumedesc` varchar(255) default NULL,
1716 `publicationyear` smallint(6) default 0,
1717 `place` varchar(255) default NULL,
1718 `isbn` varchar(10) default NULL,
1719 `mailoverseeing` smallint(1) default 0,
1720 `biblionumber` int(11) default NULL,
1722 PRIMARY KEY (`suggestionid`),
1723 KEY `suggestedby` (`suggestedby`),
1724 KEY `managedby` (`managedby`)
1725 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1728 -- Table structure for table `systempreferences`
1731 DROP TABLE IF EXISTS `systempreferences`;
1732 CREATE TABLE `systempreferences` (
1733 `variable` varchar(50) NOT NULL default '',
1735 `options` mediumtext,
1737 `type` varchar(20) default NULL,
1738 PRIMARY KEY (`variable`)
1739 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1742 -- Table structure for table `tags`
1745 DROP TABLE IF EXISTS `tags`;
1746 CREATE TABLE `tags` (
1747 `entry` varchar(255) NOT NULL default '',
1748 `weight` bigint(20) NOT NULL default 0,
1749 PRIMARY KEY (`entry`)
1750 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1753 -- Table structure for table `userflags`
1756 DROP TABLE IF EXISTS `userflags`;
1757 CREATE TABLE `userflags` (
1758 `bit` int(11) NOT NULL default 0,
1759 `flag` varchar(30) default NULL,
1760 `flagdesc` varchar(255) default NULL,
1761 `defaulton` int(11) default NULL,
1763 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1766 -- Table structure for table `virtualshelves`
1769 DROP TABLE IF EXISTS `virtualshelves`;
1770 CREATE TABLE `virtualshelves` (
1771 `shelfnumber` int(11) NOT NULL auto_increment,
1772 `shelfname` varchar(255) default NULL,
1773 `owner` varchar(80) default NULL,
1774 `category` varchar(1) default NULL,
1775 `sortfield` varchar(16) default NULL,
1776 PRIMARY KEY (`shelfnumber`)
1777 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1780 -- Table structure for table `virtualshelfcontents`
1783 DROP TABLE IF EXISTS `virtualshelfcontents`;
1784 CREATE TABLE `virtualshelfcontents` (
1785 `shelfnumber` int(11) NOT NULL default 0,
1786 `biblionumber` int(11) NOT NULL default 0,
1787 `flags` int(11) default NULL,
1788 `dateadded` timestamp NULL default NULL,
1789 KEY `shelfnumber` (`shelfnumber`),
1790 KEY `biblionumber` (`biblionumber`),
1791 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1792 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1793 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1796 -- Table structure for table `z3950servers`
1799 DROP TABLE IF EXISTS `z3950servers`;
1800 CREATE TABLE `z3950servers` (
1801 `host` varchar(255) default NULL,
1802 `port` int(11) default NULL,
1803 `db` varchar(255) default NULL,
1804 `userid` varchar(255) default NULL,
1805 `password` varchar(255) default NULL,
1807 `id` int(11) NOT NULL auto_increment,
1808 `checked` smallint(6) default NULL,
1809 `rank` int(11) default NULL,
1810 `syntax` varchar(80) default NULL,
1812 `position` enum('primary','secondary','') NOT NULL default 'primary',
1813 `type` enum('zed','opensearch') NOT NULL default 'zed',
1814 `encoding` text default NULL,
1815 `description` text NOT NULL,
1817 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1820 -- Table structure for table `zebraqueue`
1823 DROP TABLE IF EXISTS `zebraqueue`;
1824 CREATE TABLE `zebraqueue` (
1825 `id` int(11) NOT NULL auto_increment,
1826 `biblio_auth_number` int(11) NOT NULL default '0',
1827 `operation` char(20) NOT NULL default '',
1828 `server` char(20) NOT NULL default '',
1829 `done` int(11) NOT NULL default '0',
1830 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1832 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1834 DROP TABLE IF EXISTS `services_throttle`;
1835 CREATE TABLE `services_throttle` (
1836 `service_type` varchar(10) NOT NULL default '',
1837 `service_count` varchar(45) default NULL,
1838 PRIMARY KEY (`service_type`)
1839 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1841 -- http://www.w3.org/International/articles/language-tags/
1844 DROP TABLE IF EXISTS language_subtag_registry;
1845 CREATE TABLE language_subtag_registry (
1847 type varchar(25), -- language-script-region-variant-extension-privateuse
1848 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1850 KEY `subtag` (`subtag`)
1851 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1853 -- TODO: add suppress_scripts
1854 -- this maps three letter codes defined in iso639.2 back to their
1855 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1856 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1857 CREATE TABLE language_rfc4646_to_iso639 (
1858 rfc4646_subtag varchar(25),
1859 iso639_2_code varchar(25),
1860 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1861 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1863 DROP TABLE IF EXISTS language_descriptions;
1864 CREATE TABLE language_descriptions (
1868 description varchar(255),
1870 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1872 -- bi-directional support, keyed by script subcode
1873 DROP TABLE IF EXISTS language_script_bidi;
1874 CREATE TABLE language_script_bidi (
1875 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1876 bidi varchar(3), -- rtl ltr
1877 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1878 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1880 -- TODO: need to map language subtags to script subtags for detection
1881 -- of bidi when script is not specified (like ar, he)
1882 DROP TABLE IF EXISTS language_script_mapping;
1883 CREATE TABLE language_script_mapping (
1884 language_subtag varchar(25),
1885 script_subtag varchar(25),
1886 KEY `language_subtag` (`language_subtag`)
1887 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1889 DROP TABLE IF EXISTS serialitems;
1890 CREATE TABLE serialitems (
1891 serialid int(11) NOT NULL,
1892 itemnumber int(11) NOT NULL,
1893 UNIQUE KEY `serialididx` (`serialid`)
1894 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1896 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1897 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1898 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1899 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1900 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1901 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1902 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1903 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;