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 PRIMARY KEY (`itemnumber`),
1053 UNIQUE KEY `itembarcodeidx` (`barcode`),
1054 KEY `itembinoidx` (`biblioitemnumber`),
1055 KEY `itembibnoidx` (`biblionumber`),
1056 KEY `homebranch` (`homebranch`),
1057 KEY `holdingbranch` (`holdingbranch`),
1058 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1059 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1060 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1061 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1064 -- Table structure for table `itemtypes`
1067 DROP TABLE IF EXISTS `itemtypes`;
1068 CREATE TABLE `itemtypes` (
1069 `itemtype` varchar(10) NOT NULL default '',
1070 `description` mediumtext,
1071 `renewalsallowed` smallint(6) default NULL,
1072 `rentalcharge` double(16,4) default NULL,
1073 `notforloan` smallint(6) default NULL,
1074 `imageurl` varchar(200) default NULL,
1076 PRIMARY KEY (`itemtype`),
1077 UNIQUE KEY `itemtype` (`itemtype`)
1078 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1081 -- Table structure for table `labels`
1084 DROP TABLE IF EXISTS `labels`;
1085 CREATE TABLE `labels` (
1086 `labelid` int(11) NOT NULL auto_increment,
1087 `batch_id` varchar(10) NOT NULL default 1,
1088 `itemnumber` varchar(100) NOT NULL default '',
1089 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1090 PRIMARY KEY (`labelid`)
1091 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1094 -- Table structure for table `labels_conf`
1097 DROP TABLE IF EXISTS `labels_conf`;
1098 CREATE TABLE `labels_conf` (
1099 `id` int(4) NOT NULL auto_increment,
1100 `barcodetype` char(100) default '',
1101 `title` int(1) default '0',
1102 `subtitle` int(1) default '0',
1103 `itemtype` int(1) default '0',
1104 `barcode` int(1) default '0',
1105 `dewey` int(1) default '0',
1106 `class` int(1) default NULL,
1107 `subclass` int(1) default '0',
1108 `itemcallnumber` int(1) default '0',
1109 `author` int(1) default '0',
1110 `issn` int(1) default '0',
1111 `isbn` int(1) default '0',
1112 `startlabel` int(2) NOT NULL default '1',
1113 `printingtype` char(32) default 'BAR',
1114 `layoutname` char(20) NOT NULL default 'TEST',
1115 `guidebox` int(1) default '0',
1116 `active` tinyint(1) default '1',
1117 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1118 `ccode` char(4) collate utf8_unicode_ci default NULL,
1119 `callnum_split` int(1) default NULL,
1120 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1122 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1125 -- Table structure for table `labels_templates`
1128 DROP TABLE IF EXISTS `labels_templates`;
1129 CREATE TABLE `labels_templates` (
1130 `tmpl_id` int(4) NOT NULL auto_increment,
1131 `tmpl_code` char(100) default '',
1132 `tmpl_desc` char(100) default '',
1133 `page_width` float default '0',
1134 `page_height` float default '0',
1135 `label_width` float default '0',
1136 `label_height` float default '0',
1137 `topmargin` float default '0',
1138 `leftmargin` float default '0',
1139 `cols` int(2) default '0',
1140 `rows` int(2) default '0',
1141 `colgap` float default '0',
1142 `rowgap` float default '0',
1143 `active` int(1) default NULL,
1144 `units` char(20) default 'PX',
1145 `fontsize` int(4) NOT NULL default '3',
1146 PRIMARY KEY (`tmpl_id`)
1147 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1150 -- Table structure for table `letter`
1153 DROP TABLE IF EXISTS `letter`;
1154 CREATE TABLE `letter` (
1155 `module` varchar(20) NOT NULL default '',
1156 `code` varchar(20) NOT NULL default '',
1157 `name` varchar(100) NOT NULL default '',
1158 `title` varchar(200) NOT NULL default '',
1160 PRIMARY KEY (`module`,`code`)
1161 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1164 -- Table structure for table `marc_subfield_structure`
1167 DROP TABLE IF EXISTS `marc_subfield_structure`;
1168 CREATE TABLE `marc_subfield_structure` (
1169 `tagfield` varchar(3) NOT NULL default '',
1170 `tagsubfield` varchar(1) NOT NULL default '',
1171 `liblibrarian` varchar(255) NOT NULL default '',
1172 `libopac` varchar(255) NOT NULL default '',
1173 `repeatable` tinyint(4) NOT NULL default 0,
1174 `mandatory` tinyint(4) NOT NULL default 0,
1175 `kohafield` varchar(40) default NULL,
1176 `tab` tinyint(1) default NULL,
1177 `authorised_value` varchar(20) default NULL,
1178 `authtypecode` varchar(20) default NULL,
1179 `value_builder` varchar(80) default NULL,
1180 `isurl` tinyint(1) default NULL,
1181 `hidden` tinyint(1) default NULL,
1182 `frameworkcode` varchar(4) NOT NULL default '',
1183 `seealso` varchar(1100) default NULL,
1184 `link` varchar(80) default NULL,
1185 `defaultvalue` text default NULL,
1186 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1187 KEY `kohafield_2` (`kohafield`),
1188 KEY `tab` (`frameworkcode`,`tab`),
1189 KEY `kohafield` (`frameworkcode`,`kohafield`)
1190 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1193 -- Table structure for table `marc_tag_structure`
1196 DROP TABLE IF EXISTS `marc_tag_structure`;
1197 CREATE TABLE `marc_tag_structure` (
1198 `tagfield` varchar(3) NOT NULL default '',
1199 `liblibrarian` varchar(255) NOT NULL default '',
1200 `libopac` varchar(255) NOT NULL default '',
1201 `repeatable` tinyint(4) NOT NULL default 0,
1202 `mandatory` tinyint(4) NOT NULL default 0,
1203 `authorised_value` varchar(10) default NULL,
1204 `frameworkcode` varchar(4) NOT NULL default '',
1205 PRIMARY KEY (`frameworkcode`,`tagfield`)
1206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1209 -- Table structure for table `marc_matchers`
1212 DROP TABLE IF EXISTS `marc_matchers`;
1213 CREATE TABLE `marc_matchers` (
1214 `matcher_id` int(11) NOT NULL auto_increment,
1215 `code` varchar(10) NOT NULL default '',
1216 `description` varchar(255) NOT NULL default '',
1217 `record_type` varchar(10) NOT NULL default 'biblio',
1218 `threshold` int(11) NOT NULL default 0,
1219 PRIMARY KEY (`matcher_id`),
1220 KEY `code` (`code`),
1221 KEY `record_type` (`record_type`)
1222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1225 -- Table structure for table `matchpoints`
1227 DROP TABLE IF EXISTS `matchpoints`;
1228 CREATE TABLE `matchpoints` (
1229 `matcher_id` int(11) NOT NULL,
1230 `matchpoint_id` int(11) NOT NULL auto_increment,
1231 `search_index` varchar(30) NOT NULL default '',
1232 `score` int(11) NOT NULL default 0,
1233 PRIMARY KEY (`matchpoint_id`),
1234 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1235 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1236 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1240 -- Table structure for table `matchpoint_components`
1242 DROP TABLE IF EXISTS `matchpoint_components`;
1243 CREATE TABLE `matchpoint_components` (
1244 `matchpoint_id` int(11) NOT NULL,
1245 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1246 sequence int(11) NOT NULL default 0,
1247 tag varchar(3) NOT NULL default '',
1248 subfields varchar(40) NOT NULL default '',
1249 offset int(4) NOT NULL default 0,
1250 length int(4) NOT NULL default 0,
1251 PRIMARY KEY (`matchpoint_component_id`),
1252 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1253 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1254 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1255 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1258 -- Table structure for table `matcher_component_norms`
1260 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1261 CREATE TABLE `matchpoint_component_norms` (
1262 `matchpoint_component_id` int(11) NOT NULL,
1263 `sequence` int(11) NOT NULL default 0,
1264 `norm_routine` varchar(50) NOT NULL default '',
1265 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1266 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1267 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1268 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1271 -- Table structure for table `matcher_matchpoints`
1273 DROP TABLE IF EXISTS `matcher_matchpoints`;
1274 CREATE TABLE `matcher_matchpoints` (
1275 `matcher_id` int(11) NOT NULL,
1276 `matchpoint_id` int(11) NOT NULL,
1277 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1278 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1279 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1280 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1284 -- Table structure for table `matchchecks`
1286 DROP TABLE IF EXISTS `matchchecks`;
1287 CREATE TABLE `matchchecks` (
1288 `matcher_id` int(11) NOT NULL,
1289 `matchcheck_id` int(11) NOT NULL auto_increment,
1290 `source_matchpoint_id` int(11) NOT NULL,
1291 `target_matchpoint_id` int(11) NOT NULL,
1292 PRIMARY KEY (`matchcheck_id`),
1293 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1294 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1295 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1296 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1297 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1298 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1302 -- Table structure for table `mediatypetable`
1305 DROP TABLE IF EXISTS `mediatypetable`;
1306 CREATE TABLE `mediatypetable` (
1307 `mediatypecode` varchar(5) NOT NULL default '',
1309 `itemtypecodes` text,
1310 PRIMARY KEY (`mediatypecode`)
1311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1314 -- Table structure for table `notifys`
1317 DROP TABLE IF EXISTS `notifys`;
1318 CREATE TABLE `notifys` (
1319 `notify_id` int(11) NOT NULL default 0,
1320 `borrowernumber` int(11) NOT NULL default 0,
1321 `itemnumber` int(11) NOT NULL default 0,
1322 `notify_date` date default NULL,
1323 `notify_send_date` date default NULL,
1324 `notify_level` int(1) NOT NULL default 0,
1325 `method` varchar(20) NOT NULL default ''
1326 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1329 -- Table structure for table `nozebra`
1331 DROP TABLE IF EXISTS `nozebra`;
1332 CREATE TABLE `nozebra` (
1333 `server` varchar(20) NOT NULL,
1334 `indexname` varchar(40) NOT NULL,
1335 `value` varchar(250) NOT NULL,
1336 `biblionumbers` longtext NOT NULL,
1337 KEY `indexname` (`server`,`indexname`),
1338 KEY `value` (`server`,`value`))
1339 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1342 -- Table structure for table `opac_news`
1345 DROP TABLE IF EXISTS `opac_news`;
1346 CREATE TABLE `opac_news` (
1347 `idnew` int(10) unsigned NOT NULL auto_increment,
1348 `title` varchar(250) NOT NULL default '',
1349 `new` text NOT NULL,
1350 `lang` varchar(4) NOT NULL default '',
1351 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1352 `expirationdate` date default NULL,
1353 `number` int(11) default NULL,
1354 PRIMARY KEY (`idnew`)
1355 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1358 -- Table structure for table `overduerules`
1361 DROP TABLE IF EXISTS `overduerules`;
1362 CREATE TABLE `overduerules` (
1363 `branchcode` varchar(10) NOT NULL default '',
1364 `categorycode` varchar(2) NOT NULL default '',
1365 `delay1` int(4) default 0,
1366 `letter1` varchar(20) default NULL,
1367 `debarred1` varchar(1) default 0,
1368 `delay2` int(4) default 0,
1369 `debarred2` varchar(1) default 0,
1370 `letter2` varchar(20) default NULL,
1371 `delay3` int(4) default 0,
1372 `letter3` varchar(20) default NULL,
1373 `debarred3` int(1) default 0,
1374 PRIMARY KEY (`branchcode`,`categorycode`)
1375 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1378 -- Table structure for table `printers`
1381 DROP TABLE IF EXISTS `printers`;
1382 CREATE TABLE `printers` (
1383 `printername` varchar(40) NOT NULL default '',
1384 `printqueue` varchar(20) default NULL,
1385 `printtype` varchar(20) default NULL,
1386 PRIMARY KEY (`printername`)
1387 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1390 -- Table structure for table `repeatable_holidays`
1393 DROP TABLE IF EXISTS `repeatable_holidays`;
1394 CREATE TABLE `repeatable_holidays` (
1395 `id` int(11) NOT NULL auto_increment,
1396 `branchcode` varchar(10) NOT NULL default '',
1397 `weekday` smallint(6) default NULL,
1398 `day` smallint(6) default NULL,
1399 `month` smallint(6) default NULL,
1400 `title` varchar(50) NOT NULL default '',
1401 `description` text NOT NULL,
1403 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1406 -- Table structure for table `reports_dictionary`
1409 DROP TABLE IF EXISTS `reports_dictionary`;
1410 CREATE TABLE reports_dictionary (
1411 `id` int(11) NOT NULL auto_increment,
1412 `name` varchar(255) default NULL,
1414 `date_created` datetime default NULL,
1415 `date_modified` datetime default NULL,
1417 `area` int(11) default NULL,
1419 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1422 -- Table structure for table `reserveconstraints`
1425 DROP TABLE IF EXISTS `reserveconstraints`;
1426 CREATE TABLE `reserveconstraints` (
1427 `borrowernumber` int(11) NOT NULL default 0,
1428 `reservedate` date default NULL,
1429 `biblionumber` int(11) NOT NULL default 0,
1430 `biblioitemnumber` int(11) default NULL,
1431 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1432 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1435 -- Table structure for table `reserves`
1438 DROP TABLE IF EXISTS `reserves`;
1439 CREATE TABLE `reserves` (
1440 `borrowernumber` int(11) NOT NULL default 0,
1441 `reservedate` date default NULL,
1442 `biblionumber` int(11) NOT NULL default 0,
1443 `constrainttype` varchar(1) default NULL,
1444 `branchcode` varchar(10) default NULL,
1445 `notificationdate` date default NULL,
1446 `reminderdate` date default NULL,
1447 `cancellationdate` date default NULL,
1448 `reservenotes` mediumtext,
1449 `priority` smallint(6) default NULL,
1450 `found` varchar(1) default NULL,
1451 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1452 `itemnumber` int(11) default NULL,
1453 `waitingdate` date default NULL,
1454 KEY `borrowernumber` (`borrowernumber`),
1455 KEY `biblionumber` (`biblionumber`),
1456 KEY `itemnumber` (`itemnumber`),
1457 KEY `branchcode` (`branchcode`),
1458 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1459 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1460 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1461 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1462 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1465 -- Table structure for table `reviews`
1468 DROP TABLE IF EXISTS `reviews`;
1469 CREATE TABLE `reviews` (
1470 `reviewid` int(11) NOT NULL auto_increment,
1471 `borrowernumber` int(11) default NULL,
1472 `biblionumber` int(11) default NULL,
1474 `approved` tinyint(4) default NULL,
1475 `datereviewed` datetime default NULL,
1476 PRIMARY KEY (`reviewid`)
1477 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1480 -- Table structure for table `roadtype`
1483 DROP TABLE IF EXISTS `roadtype`;
1484 CREATE TABLE `roadtype` (
1485 `roadtypeid` int(11) NOT NULL auto_increment,
1486 `road_type` varchar(100) NOT NULL default '',
1487 PRIMARY KEY (`roadtypeid`)
1488 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1491 -- Table structure for table `saved_sql`
1494 DROP TABLE IF EXISTS `saved_sql`;
1495 CREATE TABLE saved_sql (
1496 `id` int(11) NOT NULL auto_increment,
1497 `borrowernumber` int(11) default NULL,
1498 `date_created` datetime default NULL,
1499 `last_modified` datetime default NULL,
1501 `last_run` datetime default NULL,
1502 `report_name` varchar(255) default NULL,
1503 `type` varchar(255) default NULL,
1506 KEY boridx (`borrowernumber`)
1507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1511 -- Table structure for `saved_reports`
1514 DROP TABLE IF EXISTS `saved_reports`;
1515 CREATE TABLE saved_reports (
1516 `id` int(11) NOT NULL auto_increment,
1517 `report_id` int(11) default NULL,
1519 `date_run` datetime default NULL,
1521 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1525 -- Table structure for table `serial`
1528 DROP TABLE IF EXISTS `serial`;
1529 CREATE TABLE `serial` (
1530 `serialid` int(11) NOT NULL auto_increment,
1531 `biblionumber` varchar(100) NOT NULL default '',
1532 `subscriptionid` varchar(100) NOT NULL default '',
1533 `serialseq` varchar(100) NOT NULL default '',
1534 `status` tinyint(4) NOT NULL default 0,
1535 `planneddate` date default NULL,
1537 `publisheddate` date default NULL,
1539 `claimdate` date default NULL,
1540 `routingnotes` text,
1541 PRIMARY KEY (`serialid`)
1542 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1545 -- Table structure for table `sessions`
1548 DROP TABLE IF EXISTS sessions;
1549 CREATE TABLE sessions (
1550 `id` varchar(32) NOT NULL,
1551 `a_session` text NOT NULL,
1553 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1556 -- Table structure for table `special_holidays`
1559 DROP TABLE IF EXISTS `special_holidays`;
1560 CREATE TABLE `special_holidays` (
1561 `id` int(11) NOT NULL auto_increment,
1562 `branchcode` varchar(10) NOT NULL default '',
1563 `day` smallint(6) NOT NULL default 0,
1564 `month` smallint(6) NOT NULL default 0,
1565 `year` smallint(6) NOT NULL default 0,
1566 `isexception` smallint(1) NOT NULL default 1,
1567 `title` varchar(50) NOT NULL default '',
1568 `description` text NOT NULL,
1570 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1573 -- Table structure for table `statistics`
1576 DROP TABLE IF EXISTS `statistics`;
1577 CREATE TABLE `statistics` (
1578 `datetime` datetime default NULL,
1579 `branch` varchar(10) default NULL,
1580 `proccode` varchar(4) default NULL,
1581 `value` double(16,4) default NULL,
1582 `type` varchar(16) default NULL,
1584 `usercode` varchar(10) default NULL,
1585 `itemnumber` int(11) default NULL,
1586 `itemtype` varchar(10) default NULL,
1587 `borrowernumber` int(11) default NULL,
1588 `associatedborrower` int(11) default NULL,
1589 KEY `timeidx` (`datetime`)
1590 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1593 -- Table structure for table `stopwords`
1596 DROP TABLE IF EXISTS `stopwords`;
1597 CREATE TABLE `stopwords` (
1598 `word` varchar(255) default NULL
1599 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1602 -- Table structure for table `subcategorytable`
1605 DROP TABLE IF EXISTS `subcategorytable`;
1606 CREATE TABLE `subcategorytable` (
1607 `subcategorycode` varchar(5) NOT NULL default '',
1609 `itemtypecodes` text,
1610 PRIMARY KEY (`subcategorycode`)
1611 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1614 -- Table structure for table `subscription`
1617 DROP TABLE IF EXISTS `subscription`;
1618 CREATE TABLE `subscription` (
1619 `biblionumber` int(11) NOT NULL default 0,
1620 `subscriptionid` int(11) NOT NULL auto_increment,
1621 `librarian` varchar(100) default '',
1622 `startdate` date default NULL,
1623 `aqbooksellerid` int(11) default 0,
1624 `cost` int(11) default 0,
1625 `aqbudgetid` int(11) default 0,
1626 `weeklength` tinyint(4) default 0,
1627 `monthlength` tinyint(4) default 0,
1628 `numberlength` tinyint(4) default 0,
1629 `periodicity` tinyint(4) default 0,
1630 `dow` varchar(100) default '',
1631 `numberingmethod` varchar(100) default '',
1633 `status` varchar(100) NOT NULL default '',
1634 `add1` int(11) default 0,
1635 `every1` int(11) default 0,
1636 `whenmorethan1` int(11) default 0,
1637 `setto1` int(11) default NULL,
1638 `lastvalue1` int(11) default NULL,
1639 `add2` int(11) default 0,
1640 `every2` int(11) default 0,
1641 `whenmorethan2` int(11) default 0,
1642 `setto2` int(11) default NULL,
1643 `lastvalue2` int(11) default NULL,
1644 `add3` int(11) default 0,
1645 `every3` int(11) default 0,
1646 `innerloop1` int(11) default 0,
1647 `innerloop2` int(11) default 0,
1648 `innerloop3` int(11) default 0,
1649 `whenmorethan3` int(11) default 0,
1650 `setto3` int(11) default NULL,
1651 `lastvalue3` int(11) default NULL,
1652 `issuesatonce` tinyint(3) NOT NULL default 1,
1653 `firstacquidate` date default NULL,
1654 `manualhistory` tinyint(1) NOT NULL default 0,
1655 `irregularity` text,
1656 `letter` varchar(20) default NULL,
1657 `numberpattern` tinyint(3) default 0,
1658 `distributedto` text,
1659 `internalnotes` longtext,
1661 `branchcode` varchar(10) NOT NULL default '',
1662 `hemisphere` tinyint(3) default 0,
1663 `lastbranch` varchar(10),
1664 PRIMARY KEY (`subscriptionid`)
1665 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1668 -- Table structure for table `subscriptionhistory`
1671 DROP TABLE IF EXISTS `subscriptionhistory`;
1672 CREATE TABLE `subscriptionhistory` (
1673 `biblionumber` int(11) NOT NULL default 0,
1674 `subscriptionid` int(11) NOT NULL default 0,
1675 `histstartdate` date default NULL,
1676 `enddate` date default NULL,
1677 `missinglist` longtext NOT NULL,
1678 `recievedlist` longtext NOT NULL,
1679 `opacnote` varchar(150) NOT NULL default '',
1680 `librariannote` varchar(150) NOT NULL default '',
1681 PRIMARY KEY (`subscriptionid`),
1682 KEY `biblionumber` (`biblionumber`)
1683 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1686 -- Table structure for table `subscriptionroutinglist`
1689 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1690 CREATE TABLE `subscriptionroutinglist` (
1691 `routingid` int(11) NOT NULL auto_increment,
1692 `borrowernumber` int(11) default NULL,
1693 `ranking` int(11) default NULL,
1694 `subscriptionid` int(11) default NULL,
1695 PRIMARY KEY (`routingid`)
1696 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1699 -- Table structure for table `suggestions`
1702 DROP TABLE IF EXISTS `suggestions`;
1703 CREATE TABLE `suggestions` (
1704 `suggestionid` int(8) NOT NULL auto_increment,
1705 `suggestedby` int(11) NOT NULL default 0,
1706 `managedby` int(11) default NULL,
1707 `STATUS` varchar(10) NOT NULL default '',
1709 `author` varchar(80) default NULL,
1710 `title` varchar(80) default NULL,
1711 `copyrightdate` smallint(6) default NULL,
1712 `publishercode` varchar(255) default NULL,
1713 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1714 `volumedesc` varchar(255) default NULL,
1715 `publicationyear` smallint(6) default 0,
1716 `place` varchar(255) default NULL,
1717 `isbn` varchar(10) default NULL,
1718 `mailoverseeing` smallint(1) default 0,
1719 `biblionumber` int(11) default NULL,
1721 PRIMARY KEY (`suggestionid`),
1722 KEY `suggestedby` (`suggestedby`),
1723 KEY `managedby` (`managedby`)
1724 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1727 -- Table structure for table `systempreferences`
1730 DROP TABLE IF EXISTS `systempreferences`;
1731 CREATE TABLE `systempreferences` (
1732 `variable` varchar(50) NOT NULL default '',
1734 `options` mediumtext,
1736 `type` varchar(20) default NULL,
1737 PRIMARY KEY (`variable`)
1738 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1741 -- Table structure for table `tags`
1744 DROP TABLE IF EXISTS `tags`;
1745 CREATE TABLE `tags` (
1746 `entry` varchar(255) NOT NULL default '',
1747 `weight` bigint(20) NOT NULL default 0,
1748 PRIMARY KEY (`entry`)
1749 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1752 -- Table structure for table `userflags`
1755 DROP TABLE IF EXISTS `userflags`;
1756 CREATE TABLE `userflags` (
1757 `bit` int(11) NOT NULL default 0,
1758 `flag` varchar(30) default NULL,
1759 `flagdesc` varchar(255) default NULL,
1760 `defaulton` int(11) default NULL,
1762 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1765 -- Table structure for table `virtualshelves`
1768 DROP TABLE IF EXISTS `virtualshelves`;
1769 CREATE TABLE `virtualshelves` (
1770 `shelfnumber` int(11) NOT NULL auto_increment,
1771 `shelfname` varchar(255) default NULL,
1772 `owner` varchar(80) default NULL,
1773 `category` varchar(1) default NULL,
1774 `sortfield` varchar(16) default NULL,
1775 PRIMARY KEY (`shelfnumber`)
1776 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1779 -- Table structure for table `virtualshelfcontents`
1782 DROP TABLE IF EXISTS `virtualshelfcontents`;
1783 CREATE TABLE `virtualshelfcontents` (
1784 `shelfnumber` int(11) NOT NULL default 0,
1785 `biblionumber` int(11) NOT NULL default 0,
1786 `flags` int(11) default NULL,
1787 `dateadded` timestamp NULL default NULL,
1788 KEY `shelfnumber` (`shelfnumber`),
1789 KEY `biblionumber` (`biblionumber`),
1790 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1791 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1792 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1795 -- Table structure for table `z3950servers`
1798 DROP TABLE IF EXISTS `z3950servers`;
1799 CREATE TABLE `z3950servers` (
1800 `host` varchar(255) default NULL,
1801 `port` int(11) default NULL,
1802 `db` varchar(255) default NULL,
1803 `userid` varchar(255) default NULL,
1804 `password` varchar(255) default NULL,
1806 `id` int(11) NOT NULL auto_increment,
1807 `checked` smallint(6) default NULL,
1808 `rank` int(11) default NULL,
1809 `syntax` varchar(80) default NULL,
1811 `position` enum('primary','secondary','') NOT NULL default 'primary',
1812 `type` enum('zed','opensearch') NOT NULL default 'zed',
1813 `description` text NOT NULL,
1815 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1818 -- Table structure for table `zebraqueue`
1821 DROP TABLE IF EXISTS `zebraqueue`;
1822 CREATE TABLE `zebraqueue` (
1823 `id` int(11) NOT NULL auto_increment,
1824 `biblio_auth_number` int(11) NOT NULL default '0',
1825 `operation` char(20) NOT NULL default '',
1826 `server` char(20) NOT NULL default '',
1827 `done` int(11) NOT NULL default '0',
1828 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1830 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1832 DROP TABLE IF EXISTS `services_throttle`;
1833 CREATE TABLE `services_throttle` (
1834 `service_type` varchar(10) NOT NULL default '',
1835 `service_count` varchar(45) default NULL,
1836 PRIMARY KEY (`service_type`)
1837 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1839 -- http://www.w3.org/International/articles/language-tags/
1842 DROP TABLE IF EXISTS language_subtag_registry;
1843 CREATE TABLE language_subtag_registry (
1845 type varchar(25), -- language-script-region-variant-extension-privateuse
1846 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1848 KEY `subtag` (`subtag`)
1849 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1851 -- TODO: add suppress_scripts
1852 -- this maps three letter codes defined in iso639.2 back to their
1853 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1854 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1855 CREATE TABLE language_rfc4646_to_iso639 (
1856 rfc4646_subtag varchar(25),
1857 iso639_2_code varchar(25),
1858 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1859 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1861 DROP TABLE IF EXISTS language_descriptions;
1862 CREATE TABLE language_descriptions (
1866 description varchar(255),
1868 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1870 -- bi-directional support, keyed by script subcode
1871 DROP TABLE IF EXISTS language_script_bidi;
1872 CREATE TABLE language_script_bidi (
1873 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1874 bidi varchar(3), -- rtl ltr
1875 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1876 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1878 -- TODO: need to map language subtags to script subtags for detection
1879 -- of bidi when script is not specified (like ar, he)
1880 DROP TABLE IF EXISTS language_script_mapping;
1881 CREATE TABLE language_script_mapping (
1882 language_subtag varchar(25),
1883 script_subtag varchar(25),
1884 KEY `language_subtag` (`language_subtag`)
1885 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1887 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1888 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1889 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1890 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1891 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1892 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1893 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1894 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;