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 KEY `borrowernumber` (`borrowernumber`),
789 KEY `cardnumber` (`cardnumber`)
790 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
793 -- Table structure for table `deleteditems`
796 DROP TABLE IF EXISTS `deleteditems`;
797 CREATE TABLE `deleteditems` (
798 `itemnumber` int(11) NOT NULL default 0,
799 `biblionumber` int(11) NOT NULL default 0,
800 `biblioitemnumber` int(11) NOT NULL default 0,
801 `barcode` varchar(20) default NULL,
802 `dateaccessioned` date default NULL,
803 `booksellerid` varchar(10) default NULL,
804 `homebranch` varchar(10) default NULL,
805 `price` decimal(8,2) default NULL,
806 `replacementprice` decimal(8,2) default NULL,
807 `replacementpricedate` date default NULL,
808 `datelastborrowed` date default NULL,
809 `datelastseen` date default NULL,
810 `stack` tinyint(1) default NULL,
811 `notforloan` tinyint(1) NOT NULL default 0,
812 `damaged` tinyint(1) NOT NULL default 0,
813 `itemlost` tinyint(1) NOT NULL default 0,
814 `wthdrawn` tinyint(1) NOT NULL default 0,
815 `itemcallnumber` varchar(30) default NULL,
816 `issues` smallint(6) default NULL,
817 `renewals` smallint(6) default NULL,
818 `reserves` smallint(6) default NULL,
819 `restricted` tinyint(1) default NULL,
820 `itemnotes` mediumtext,
821 `holdingbranch` varchar(10) default NULL,
822 `paidfor` mediumtext,
823 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
824 `location` varchar(80) default NULL,
825 `onloan` date default NULL,
826 `cn_source` varchar(10) default NULL,
827 `cn_sort` varchar(30) default NULL,
828 `ccode` varchar(10) default NULL,
829 `materials` varchar(10) default NULL,
830 `uri` varchar(255) default NULL,
831 `itype` varchar(10) default NULL,
833 PRIMARY KEY (`itemnumber`),
834 KEY `delitembarcodeidx` (`barcode`),
835 KEY `delitembinoidx` (`biblioitemnumber`),
836 KEY `delitembibnoidx` (`biblionumber`),
837 KEY `delhomebranch` (`homebranch`),
838 KEY `delholdingbranch` (`holdingbranch`)
839 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
842 -- Table structure for table `ethnicity`
845 DROP TABLE IF EXISTS `ethnicity`;
846 CREATE TABLE `ethnicity` (
847 `code` varchar(10) NOT NULL default '',
848 `name` varchar(255) default NULL,
850 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
853 -- Table structure for table `import_batches`
856 DROP TABLE IF EXISTS `import_batches`;
857 CREATE TABLE `import_batches` (
858 `import_batch_id` int(11) NOT NULL auto_increment,
859 `matcher_id` int(11) default NULL,
860 `template_id` int(11) default NULL,
861 `branchcode` varchar(10) default NULL,
862 `num_biblios` int(11) NOT NULL default 0,
863 `num_items` int(11) NOT NULL default 0,
864 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
865 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
866 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
867 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
868 `file_name` varchar(100),
869 `comments` mediumtext,
870 PRIMARY KEY (`import_batch_id`),
871 KEY `branchcode` (`branchcode`)
872 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
875 -- Table structure for table `import_records`
878 DROP TABLE IF EXISTS `import_records`;
879 CREATE TABLE `import_records` (
880 `import_record_id` int(11) NOT NULL auto_increment,
881 `import_batch_id` int(11) NOT NULL,
882 `branchcode` varchar(10) default NULL,
883 `record_sequence` int(11) NOT NULL default 0,
884 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
885 `import_date` DATE default NULL,
886 `marc` longblob NOT NULL,
887 `marcxml` longtext NOT NULL,
888 `marcxml_old` longtext NOT NULL,
889 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
890 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
891 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
892 `import_error` mediumtext,
893 `encoding` varchar(40) NOT NULL default '',
894 `z3950random` varchar(40) default NULL,
895 PRIMARY KEY (`import_record_id`),
896 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
897 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
898 KEY `branchcode` (`branchcode`),
899 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
900 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
903 -- Table structure for `import_record_matches`
905 DROP TABLE IF EXISTS `import_record_matches`;
906 CREATE TABLE `import_record_matches` (
907 `import_record_id` int(11) NOT NULL,
908 `candidate_match_id` int(11) NOT NULL,
909 `score` int(11) NOT NULL default 0,
910 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
911 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
912 KEY `record_score` (`import_record_id`, `score`)
913 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
916 -- Table structure for table `import_biblios`
919 DROP TABLE IF EXISTS `import_biblios`;
920 CREATE TABLE `import_biblios` (
921 `import_record_id` int(11) NOT NULL,
922 `matched_biblionumber` int(11) default NULL,
923 `control_number` varchar(25) default NULL,
924 `original_source` varchar(25) default NULL,
925 `title` varchar(128) default NULL,
926 `author` varchar(80) default NULL,
927 `isbn` varchar(14) default NULL,
928 `issn` varchar(9) default NULL,
929 `has_items` tinyint(1) NOT NULL default 0,
930 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
931 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
932 KEY `matched_biblionumber` (`matched_biblionumber`),
933 KEY `title` (`title`),
935 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
938 -- Table structure for table `import_items`
941 DROP TABLE IF EXISTS `import_items`;
942 CREATE TABLE `import_items` (
943 `import_items_id` int(11) NOT NULL auto_increment,
944 `import_record_id` int(11) NOT NULL,
945 `itemnumber` int(11) default NULL,
946 `branchcode` varchar(10) default NULL,
947 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
948 `marcxml` longtext NOT NULL,
949 `import_error` mediumtext,
950 PRIMARY KEY (`import_items_id`),
951 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
952 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
953 KEY `itemnumber` (`itemnumber`),
954 KEY `branchcode` (`branchcode`)
955 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
958 -- Table structure for table `issues`
961 DROP TABLE IF EXISTS `issues`;
962 CREATE TABLE `issues` (
963 `borrowernumber` int(11) default NULL,
964 `itemnumber` int(11) default NULL,
965 `date_due` date default NULL,
966 `branchcode` varchar(10) default NULL,
967 `issuingbranch` varchar(18) default NULL,
968 `returndate` date default NULL,
969 `lastreneweddate` date default NULL,
970 `return` varchar(4) default NULL,
971 `renewals` tinyint(4) default NULL,
972 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
973 `issuedate` date default NULL,
974 KEY `issuesborridx` (`borrowernumber`),
975 KEY `issuesitemidx` (`itemnumber`),
976 KEY `bordate` (`borrowernumber`,`timestamp`),
977 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
978 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
979 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
982 -- Table structure for table `issuingrules`
985 DROP TABLE IF EXISTS `issuingrules`;
986 CREATE TABLE `issuingrules` (
987 `categorycode` varchar(10) NOT NULL default '',
988 `itemtype` varchar(10) NOT NULL default '',
989 `restrictedtype` tinyint(1) default NULL,
990 `rentaldiscount` decimal(28,6) default NULL,
991 `reservecharge` decimal(28,6) default NULL,
992 `fine` decimal(28,6) default NULL,
993 `firstremind` int(11) default NULL,
994 `chargeperiod` int(11) default NULL,
995 `accountsent` int(11) default NULL,
996 `chargename` varchar(100) default NULL,
997 `maxissueqty` int(4) default NULL,
998 `issuelength` int(4) default NULL,
999 `branchcode` varchar(10) NOT NULL default '',
1000 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1001 KEY `categorycode` (`categorycode`),
1002 KEY `itemtype` (`itemtype`)
1003 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1006 -- Table structure for table `items`
1009 DROP TABLE IF EXISTS `items`;
1010 CREATE TABLE `items` (
1011 `itemnumber` int(11) NOT NULL auto_increment,
1012 `biblionumber` int(11) NOT NULL default 0,
1013 `biblioitemnumber` int(11) NOT NULL default 0,
1014 `barcode` varchar(20) default NULL,
1015 `dateaccessioned` date default NULL,
1016 `booksellerid` varchar(10) default NULL,
1017 `homebranch` varchar(10) default NULL,
1018 `price` decimal(8,2) default NULL,
1019 `replacementprice` decimal(8,2) default NULL,
1020 `replacementpricedate` date default NULL,
1021 `datelastborrowed` date default NULL,
1022 `datelastseen` date default NULL,
1023 `stack` tinyint(1) default NULL,
1024 `notforloan` tinyint(1) NOT NULL default 0,
1025 `damaged` tinyint(1) NOT NULL default 0,
1026 `itemlost` tinyint(1) NOT NULL default 0,
1027 `wthdrawn` tinyint(1) NOT NULL default 0,
1028 `itemcallnumber` varchar(30) default NULL,
1029 `issues` smallint(6) default NULL,
1030 `renewals` smallint(6) default NULL,
1031 `reserves` smallint(6) default NULL,
1032 `restricted` tinyint(1) default NULL,
1033 `itemnotes` mediumtext,
1034 `holdingbranch` varchar(10) default NULL,
1035 `paidfor` mediumtext,
1036 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1037 `location` varchar(80) default NULL,
1038 `onloan` date default NULL,
1039 `cn_source` varchar(10) default NULL,
1040 `cn_sort` varchar(30) default NULL,
1041 `ccode` varchar(10) default NULL,
1042 `materials` varchar(10) default NULL,
1043 `uri` varchar(255) default NULL,
1044 `itype` varchar(10) default NULL,
1045 PRIMARY KEY (`itemnumber`),
1046 UNIQUE KEY `itembarcodeidx` (`barcode`),
1047 KEY `itembinoidx` (`biblioitemnumber`),
1048 KEY `itembibnoidx` (`biblionumber`),
1049 KEY `homebranch` (`homebranch`),
1050 KEY `holdingbranch` (`holdingbranch`),
1051 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1052 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1053 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1054 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1057 -- Table structure for table `itemtypes`
1060 DROP TABLE IF EXISTS `itemtypes`;
1061 CREATE TABLE `itemtypes` (
1062 `itemtype` varchar(10) NOT NULL default '',
1063 `description` mediumtext,
1064 `renewalsallowed` smallint(6) default NULL,
1065 `rentalcharge` double(16,4) default NULL,
1066 `notforloan` smallint(6) default NULL,
1067 `imageurl` varchar(200) default NULL,
1069 PRIMARY KEY (`itemtype`),
1070 UNIQUE KEY `itemtype` (`itemtype`)
1071 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1074 -- Table structure for table `labels`
1077 DROP TABLE IF EXISTS `labels`;
1078 CREATE TABLE `labels` (
1079 `labelid` int(11) NOT NULL auto_increment,
1080 `batch_id` varchar(10) NOT NULL default 1,
1081 `itemnumber` varchar(100) NOT NULL default '',
1082 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1083 PRIMARY KEY (`labelid`)
1084 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1087 -- Table structure for table `labels_conf`
1090 DROP TABLE IF EXISTS `labels_conf`;
1091 CREATE TABLE `labels_conf` (
1092 `id` int(4) NOT NULL auto_increment,
1093 `barcodetype` char(100) default '',
1094 `title` int(1) default '0',
1095 `subtitle` int(1) default '0',
1096 `itemtype` int(1) default '0',
1097 `barcode` int(1) default '0',
1098 `dewey` int(1) default '0',
1099 `class` int(1) default NULL,
1100 `subclass` int(1) default '0',
1101 `itemcallnumber` int(1) default '0',
1102 `author` int(1) default '0',
1103 `issn` int(1) default '0',
1104 `isbn` int(1) default '0',
1105 `startlabel` int(2) NOT NULL default '1',
1106 `printingtype` char(32) default 'BAR',
1107 `layoutname` char(20) NOT NULL default 'TEST',
1108 `guidebox` int(1) default '0',
1109 `active` tinyint(1) default '1',
1110 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1111 `ccode` char(4) collate utf8_unicode_ci default NULL,
1112 `callnum_split` int(1) default NULL,
1113 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1118 -- Table structure for table `labels_templates`
1121 DROP TABLE IF EXISTS `labels_templates`;
1122 CREATE TABLE `labels_templates` (
1123 `tmpl_id` int(4) NOT NULL auto_increment,
1124 `tmpl_code` char(100) default '',
1125 `tmpl_desc` char(100) default '',
1126 `page_width` float default '0',
1127 `page_height` float default '0',
1128 `label_width` float default '0',
1129 `label_height` float default '0',
1130 `topmargin` float default '0',
1131 `leftmargin` float default '0',
1132 `cols` int(2) default '0',
1133 `rows` int(2) default '0',
1134 `colgap` float default '0',
1135 `rowgap` float default '0',
1136 `active` int(1) default NULL,
1137 `units` char(20) default 'PX',
1138 `fontsize` int(4) NOT NULL default '3',
1139 PRIMARY KEY (`tmpl_id`)
1140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1143 -- Table structure for table `letter`
1146 DROP TABLE IF EXISTS `letter`;
1147 CREATE TABLE `letter` (
1148 `module` varchar(20) NOT NULL default '',
1149 `code` varchar(20) NOT NULL default '',
1150 `name` varchar(100) NOT NULL default '',
1151 `title` varchar(200) NOT NULL default '',
1153 PRIMARY KEY (`module`,`code`)
1154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1157 -- Table structure for table `marc_subfield_structure`
1160 DROP TABLE IF EXISTS `marc_subfield_structure`;
1161 CREATE TABLE `marc_subfield_structure` (
1162 `tagfield` varchar(3) NOT NULL default '',
1163 `tagsubfield` varchar(1) NOT NULL default '',
1164 `liblibrarian` varchar(255) NOT NULL default '',
1165 `libopac` varchar(255) NOT NULL default '',
1166 `repeatable` tinyint(4) NOT NULL default 0,
1167 `mandatory` tinyint(4) NOT NULL default 0,
1168 `kohafield` varchar(40) default NULL,
1169 `tab` tinyint(1) default NULL,
1170 `authorised_value` varchar(20) default NULL,
1171 `authtypecode` varchar(20) default NULL,
1172 `value_builder` varchar(80) default NULL,
1173 `isurl` tinyint(1) default NULL,
1174 `hidden` tinyint(1) default NULL,
1175 `frameworkcode` varchar(4) NOT NULL default '',
1176 `seealso` varchar(1100) default NULL,
1177 `link` varchar(80) default NULL,
1178 `defaultvalue` text default NULL,
1179 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1180 KEY `kohafield_2` (`kohafield`),
1181 KEY `tab` (`frameworkcode`,`tab`),
1182 KEY `kohafield` (`frameworkcode`,`kohafield`)
1183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1186 -- Table structure for table `marc_tag_structure`
1189 DROP TABLE IF EXISTS `marc_tag_structure`;
1190 CREATE TABLE `marc_tag_structure` (
1191 `tagfield` varchar(3) NOT NULL default '',
1192 `liblibrarian` varchar(255) NOT NULL default '',
1193 `libopac` varchar(255) NOT NULL default '',
1194 `repeatable` tinyint(4) NOT NULL default 0,
1195 `mandatory` tinyint(4) NOT NULL default 0,
1196 `authorised_value` varchar(10) default NULL,
1197 `frameworkcode` varchar(4) NOT NULL default '',
1198 PRIMARY KEY (`frameworkcode`,`tagfield`)
1199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1202 -- Table structure for table `marc_matchers`
1205 DROP TABLE IF EXISTS `marc_matchers`;
1206 CREATE TABLE `marc_matchers` (
1207 `matcher_id` int(11) NOT NULL auto_increment,
1208 `code` varchar(10) NOT NULL default '',
1209 `description` varchar(255) NOT NULL default '',
1210 `record_type` varchar(10) NOT NULL default 'biblio',
1211 `threshold` int(11) NOT NULL default 0,
1212 PRIMARY KEY (`matcher_id`),
1213 KEY `code` (`code`),
1214 KEY `record_type` (`record_type`)
1215 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1218 -- Table structure for table `matchpoints`
1220 DROP TABLE IF EXISTS `matchpoints`;
1221 CREATE TABLE `matchpoints` (
1222 `matcher_id` int(11) NOT NULL,
1223 `matchpoint_id` int(11) NOT NULL auto_increment,
1224 `search_index` varchar(30) NOT NULL default '',
1225 `score` int(11) NOT NULL default 0,
1226 PRIMARY KEY (`matchpoint_id`),
1227 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1228 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1233 -- Table structure for table `matchpoint_components`
1235 DROP TABLE IF EXISTS `matchpoint_components`;
1236 CREATE TABLE `matchpoint_components` (
1237 `matchpoint_id` int(11) NOT NULL,
1238 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1239 sequence int(11) NOT NULL default 0,
1240 tag varchar(3) NOT NULL default '',
1241 subfields varchar(40) NOT NULL default '',
1242 offset int(4) NOT NULL default 0,
1243 length int(4) NOT NULL default 0,
1244 PRIMARY KEY (`matchpoint_component_id`),
1245 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1246 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1247 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1248 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1251 -- Table structure for table `matcher_component_norms`
1253 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1254 CREATE TABLE `matchpoint_component_norms` (
1255 `matchpoint_component_id` int(11) NOT NULL,
1256 `sequence` int(11) NOT NULL default 0,
1257 `norm_routine` varchar(50) NOT NULL default '',
1258 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1259 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1260 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1261 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1264 -- Table structure for table `matcher_matchpoints`
1266 DROP TABLE IF EXISTS `matcher_matchpoints`;
1267 CREATE TABLE `matcher_matchpoints` (
1268 `matcher_id` int(11) NOT NULL,
1269 `matchpoint_id` int(11) NOT NULL,
1270 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1271 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1272 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1273 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1277 -- Table structure for table `matchchecks`
1279 DROP TABLE IF EXISTS `matchchecks`;
1280 CREATE TABLE `matchchecks` (
1281 `matcher_id` int(11) NOT NULL,
1282 `matchcheck_id` int(11) NOT NULL auto_increment,
1283 `source_matchpoint_id` int(11) NOT NULL,
1284 `target_matchpoint_id` int(11) NOT NULL,
1285 PRIMARY KEY (`matchcheck_id`),
1286 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1287 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1288 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1289 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1290 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1291 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1292 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1295 -- Table structure for table `mediatypetable`
1298 DROP TABLE IF EXISTS `mediatypetable`;
1299 CREATE TABLE `mediatypetable` (
1300 `mediatypecode` varchar(5) NOT NULL default '',
1302 `itemtypecodes` text,
1303 PRIMARY KEY (`mediatypecode`)
1304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1307 -- Table structure for table `notifys`
1310 DROP TABLE IF EXISTS `notifys`;
1311 CREATE TABLE `notifys` (
1312 `notify_id` int(11) NOT NULL default 0,
1313 `borrowernumber` int(11) NOT NULL default 0,
1314 `itemnumber` int(11) NOT NULL default 0,
1315 `notify_date` date default NULL,
1316 `notify_send_date` date default NULL,
1317 `notify_level` int(1) NOT NULL default 0,
1318 `method` varchar(20) NOT NULL default ''
1319 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1322 -- Table structure for table `nozebra`
1324 DROP TABLE IF EXISTS `nozebra`;
1325 CREATE TABLE `nozebra` (
1326 `server` varchar(20) NOT NULL,
1327 `indexname` varchar(40) NOT NULL,
1328 `value` varchar(250) NOT NULL,
1329 `biblionumbers` longtext NOT NULL,
1330 KEY `indexname` (`server`,`indexname`),
1331 KEY `value` (`server`,`value`))
1332 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1335 -- Table structure for table `opac_news`
1338 DROP TABLE IF EXISTS `opac_news`;
1339 CREATE TABLE `opac_news` (
1340 `idnew` int(10) unsigned NOT NULL auto_increment,
1341 `title` varchar(250) NOT NULL default '',
1342 `new` text NOT NULL,
1343 `lang` varchar(4) NOT NULL default '',
1344 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1345 `expirationdate` date default NULL,
1346 `number` int(11) default NULL,
1347 PRIMARY KEY (`idnew`)
1348 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1351 -- Table structure for table `overduerules`
1354 DROP TABLE IF EXISTS `overduerules`;
1355 CREATE TABLE `overduerules` (
1356 `branchcode` varchar(10) NOT NULL default '',
1357 `categorycode` varchar(2) NOT NULL default '',
1358 `delay1` int(4) default 0,
1359 `letter1` varchar(20) default NULL,
1360 `debarred1` varchar(1) default 0,
1361 `delay2` int(4) default 0,
1362 `debarred2` varchar(1) default 0,
1363 `letter2` varchar(20) default NULL,
1364 `delay3` int(4) default 0,
1365 `letter3` varchar(20) default NULL,
1366 `debarred3` int(1) default 0,
1367 PRIMARY KEY (`branchcode`,`categorycode`)
1368 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1371 -- Table structure for table `printers`
1374 DROP TABLE IF EXISTS `printers`;
1375 CREATE TABLE `printers` (
1376 `printername` varchar(40) NOT NULL default '',
1377 `printqueue` varchar(20) default NULL,
1378 `printtype` varchar(20) default NULL,
1379 PRIMARY KEY (`printername`)
1380 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1383 -- Table structure for table `repeatable_holidays`
1386 DROP TABLE IF EXISTS `repeatable_holidays`;
1387 CREATE TABLE `repeatable_holidays` (
1388 `id` int(11) NOT NULL auto_increment,
1389 `branchcode` varchar(10) NOT NULL default '',
1390 `weekday` smallint(6) default NULL,
1391 `day` smallint(6) default NULL,
1392 `month` smallint(6) default NULL,
1393 `title` varchar(50) NOT NULL default '',
1394 `description` text NOT NULL,
1396 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1399 -- Table structure for table `reports_dictionary`
1402 DROP TABLE IF EXISTS `reports_dictionary`;
1403 CREATE TABLE reports_dictionary (
1404 `id` int(11) NOT NULL auto_increment,
1405 `name` varchar(255) default NULL,
1407 `date_created` datetime default NULL,
1408 `date_modified` datetime default NULL,
1410 `area` int(11) default NULL,
1412 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1415 -- Table structure for table `reserveconstraints`
1418 DROP TABLE IF EXISTS `reserveconstraints`;
1419 CREATE TABLE `reserveconstraints` (
1420 `borrowernumber` int(11) NOT NULL default 0,
1421 `reservedate` date default NULL,
1422 `biblionumber` int(11) NOT NULL default 0,
1423 `biblioitemnumber` int(11) default NULL,
1424 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1425 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1428 -- Table structure for table `reserves`
1431 DROP TABLE IF EXISTS `reserves`;
1432 CREATE TABLE `reserves` (
1433 `borrowernumber` int(11) NOT NULL default 0,
1434 `reservedate` date default NULL,
1435 `biblionumber` int(11) NOT NULL default 0,
1436 `constrainttype` varchar(1) default NULL,
1437 `branchcode` varchar(10) default NULL,
1438 `notificationdate` date default NULL,
1439 `reminderdate` date default NULL,
1440 `cancellationdate` date default NULL,
1441 `reservenotes` mediumtext,
1442 `priority` smallint(6) default NULL,
1443 `found` varchar(1) default NULL,
1444 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1445 `itemnumber` int(11) default NULL,
1446 `waitingdate` date default NULL,
1447 KEY `borrowernumber` (`borrowernumber`),
1448 KEY `biblionumber` (`biblionumber`),
1449 KEY `itemnumber` (`itemnumber`),
1450 KEY `branchcode` (`branchcode`),
1451 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1452 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1453 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1454 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1455 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1458 -- Table structure for table `reviews`
1461 DROP TABLE IF EXISTS `reviews`;
1462 CREATE TABLE `reviews` (
1463 `reviewid` int(11) NOT NULL auto_increment,
1464 `borrowernumber` int(11) default NULL,
1465 `biblionumber` int(11) default NULL,
1467 `approved` tinyint(4) default NULL,
1468 `datereviewed` datetime default NULL,
1469 PRIMARY KEY (`reviewid`)
1470 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1473 -- Table structure for table `roadtype`
1476 DROP TABLE IF EXISTS `roadtype`;
1477 CREATE TABLE `roadtype` (
1478 `roadtypeid` int(11) NOT NULL auto_increment,
1479 `road_type` varchar(100) NOT NULL default '',
1480 PRIMARY KEY (`roadtypeid`)
1481 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1484 -- Table structure for table `saved_sql`
1487 DROP TABLE IF EXISTS `saved_sql`;
1488 CREATE TABLE saved_sql (
1489 `id` int(11) NOT NULL auto_increment,
1490 `borrowernumber` int(11) default NULL,
1491 `date_created` datetime default NULL,
1492 `last_modified` datetime default NULL,
1494 `last_run` datetime default NULL,
1495 `report_name` varchar(255) default NULL,
1496 `type` varchar(255) default NULL,
1499 KEY boridx (`borrowernumber`)
1500 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1504 -- Table structure for `saved_reports`
1507 DROP TABLE IF EXISTS `saved_reports`;
1508 CREATE TABLE saved_reports (
1509 `id` int(11) NOT NULL auto_increment,
1510 `report_id` int(11) default NULL,
1512 `date_run` datetime default NULL,
1514 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1518 -- Table structure for table `serial`
1521 DROP TABLE IF EXISTS `serial`;
1522 CREATE TABLE `serial` (
1523 `serialid` int(11) NOT NULL auto_increment,
1524 `biblionumber` varchar(100) NOT NULL default '',
1525 `subscriptionid` varchar(100) NOT NULL default '',
1526 `serialseq` varchar(100) NOT NULL default '',
1527 `status` tinyint(4) NOT NULL default 0,
1528 `planneddate` date default NULL,
1530 `publisheddate` date default NULL,
1532 `claimdate` date default NULL,
1533 `routingnotes` text,
1534 PRIMARY KEY (`serialid`)
1535 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1538 -- Table structure for table `sessions`
1541 DROP TABLE IF EXISTS sessions;
1542 CREATE TABLE sessions (
1543 `id` varchar(32) NOT NULL,
1544 `a_session` text NOT NULL,
1546 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1549 -- Table structure for table `special_holidays`
1552 DROP TABLE IF EXISTS `special_holidays`;
1553 CREATE TABLE `special_holidays` (
1554 `id` int(11) NOT NULL auto_increment,
1555 `branchcode` varchar(10) NOT NULL default '',
1556 `day` smallint(6) NOT NULL default 0,
1557 `month` smallint(6) NOT NULL default 0,
1558 `year` smallint(6) NOT NULL default 0,
1559 `isexception` smallint(1) NOT NULL default 1,
1560 `title` varchar(50) NOT NULL default '',
1561 `description` text NOT NULL,
1563 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1566 -- Table structure for table `statistics`
1569 DROP TABLE IF EXISTS `statistics`;
1570 CREATE TABLE `statistics` (
1571 `datetime` datetime default NULL,
1572 `branch` varchar(10) default NULL,
1573 `proccode` varchar(4) default NULL,
1574 `value` double(16,4) default NULL,
1575 `type` varchar(16) default NULL,
1577 `usercode` varchar(10) default NULL,
1578 `itemnumber` int(11) default NULL,
1579 `itemtype` varchar(10) default NULL,
1580 `borrowernumber` int(11) default NULL,
1581 `associatedborrower` int(11) default NULL,
1582 KEY `timeidx` (`datetime`)
1583 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1586 -- Table structure for table `stopwords`
1589 DROP TABLE IF EXISTS `stopwords`;
1590 CREATE TABLE `stopwords` (
1591 `word` varchar(255) default NULL
1592 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1595 -- Table structure for table `subcategorytable`
1598 DROP TABLE IF EXISTS `subcategorytable`;
1599 CREATE TABLE `subcategorytable` (
1600 `subcategorycode` varchar(5) NOT NULL default '',
1602 `itemtypecodes` text,
1603 PRIMARY KEY (`subcategorycode`)
1604 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1607 -- Table structure for table `subscription`
1610 DROP TABLE IF EXISTS `subscription`;
1611 CREATE TABLE `subscription` (
1612 `biblionumber` int(11) NOT NULL default 0,
1613 `subscriptionid` int(11) NOT NULL auto_increment,
1614 `librarian` varchar(100) default '',
1615 `startdate` date default NULL,
1616 `aqbooksellerid` int(11) default 0,
1617 `cost` int(11) default 0,
1618 `aqbudgetid` int(11) default 0,
1619 `weeklength` tinyint(4) default 0,
1620 `monthlength` tinyint(4) default 0,
1621 `numberlength` tinyint(4) default 0,
1622 `periodicity` tinyint(4) default 0,
1623 `dow` varchar(100) default '',
1624 `numberingmethod` varchar(100) default '',
1626 `status` varchar(100) NOT NULL default '',
1627 `add1` int(11) default 0,
1628 `every1` int(11) default 0,
1629 `whenmorethan1` int(11) default 0,
1630 `setto1` int(11) default NULL,
1631 `lastvalue1` int(11) default NULL,
1632 `add2` int(11) default 0,
1633 `every2` int(11) default 0,
1634 `whenmorethan2` int(11) default 0,
1635 `setto2` int(11) default NULL,
1636 `lastvalue2` int(11) default NULL,
1637 `add3` int(11) default 0,
1638 `every3` int(11) default 0,
1639 `innerloop1` int(11) default 0,
1640 `innerloop2` int(11) default 0,
1641 `innerloop3` int(11) default 0,
1642 `whenmorethan3` int(11) default 0,
1643 `setto3` int(11) default NULL,
1644 `lastvalue3` int(11) default NULL,
1645 `issuesatonce` tinyint(3) NOT NULL default 1,
1646 `firstacquidate` date default NULL,
1647 `manualhistory` tinyint(1) NOT NULL default 0,
1648 `irregularity` text,
1649 `letter` varchar(20) default NULL,
1650 `numberpattern` tinyint(3) default 0,
1651 `distributedto` text,
1652 `internalnotes` longtext,
1654 `branchcode` varchar(10) NOT NULL default '',
1655 `hemisphere` tinyint(3) default 0,
1656 `lastbranch` varchar(10),
1657 PRIMARY KEY (`subscriptionid`)
1658 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1661 -- Table structure for table `subscriptionhistory`
1664 DROP TABLE IF EXISTS `subscriptionhistory`;
1665 CREATE TABLE `subscriptionhistory` (
1666 `biblionumber` int(11) NOT NULL default 0,
1667 `subscriptionid` int(11) NOT NULL default 0,
1668 `histstartdate` date default NULL,
1669 `enddate` date default NULL,
1670 `missinglist` longtext NOT NULL,
1671 `recievedlist` longtext NOT NULL,
1672 `opacnote` varchar(150) NOT NULL default '',
1673 `librariannote` varchar(150) NOT NULL default '',
1674 PRIMARY KEY (`subscriptionid`),
1675 KEY `biblionumber` (`biblionumber`)
1676 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1679 -- Table structure for table `subscriptionroutinglist`
1682 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1683 CREATE TABLE `subscriptionroutinglist` (
1684 `routingid` int(11) NOT NULL auto_increment,
1685 `borrowernumber` int(11) default NULL,
1686 `ranking` int(11) default NULL,
1687 `subscriptionid` int(11) default NULL,
1688 PRIMARY KEY (`routingid`)
1689 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1692 -- Table structure for table `suggestions`
1695 DROP TABLE IF EXISTS `suggestions`;
1696 CREATE TABLE `suggestions` (
1697 `suggestionid` int(8) NOT NULL auto_increment,
1698 `suggestedby` int(11) NOT NULL default 0,
1699 `managedby` int(11) default NULL,
1700 `STATUS` varchar(10) NOT NULL default '',
1702 `author` varchar(80) default NULL,
1703 `title` varchar(80) default NULL,
1704 `copyrightdate` smallint(6) default NULL,
1705 `publishercode` varchar(255) default NULL,
1706 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1707 `volumedesc` varchar(255) default NULL,
1708 `publicationyear` smallint(6) default 0,
1709 `place` varchar(255) default NULL,
1710 `isbn` varchar(10) default NULL,
1711 `mailoverseeing` smallint(1) default 0,
1712 `biblionumber` int(11) default NULL,
1714 PRIMARY KEY (`suggestionid`),
1715 KEY `suggestedby` (`suggestedby`),
1716 KEY `managedby` (`managedby`)
1717 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1720 -- Table structure for table `systempreferences`
1723 DROP TABLE IF EXISTS `systempreferences`;
1724 CREATE TABLE `systempreferences` (
1725 `variable` varchar(50) NOT NULL default '',
1727 `options` mediumtext,
1729 `type` varchar(20) default NULL,
1730 PRIMARY KEY (`variable`)
1731 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1734 -- Table structure for table `tags`
1737 DROP TABLE IF EXISTS `tags`;
1738 CREATE TABLE `tags` (
1739 `entry` varchar(255) NOT NULL default '',
1740 `weight` bigint(20) NOT NULL default 0,
1741 PRIMARY KEY (`entry`)
1742 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1745 -- Table structure for table `userflags`
1748 DROP TABLE IF EXISTS `userflags`;
1749 CREATE TABLE `userflags` (
1750 `bit` int(11) NOT NULL default 0,
1751 `flag` varchar(30) default NULL,
1752 `flagdesc` varchar(255) default NULL,
1753 `defaulton` int(11) default NULL,
1755 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1758 -- Table structure for table `virtualshelves`
1761 DROP TABLE IF EXISTS `virtualshelves`;
1762 CREATE TABLE `virtualshelves` (
1763 `shelfnumber` int(11) NOT NULL auto_increment,
1764 `shelfname` varchar(255) default NULL,
1765 `owner` varchar(80) default NULL,
1766 `category` varchar(1) default NULL,
1767 `sortfield` varchar(16) default NULL,
1768 PRIMARY KEY (`shelfnumber`)
1769 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1772 -- Table structure for table `virtualshelfcontents`
1775 DROP TABLE IF EXISTS `virtualshelfcontents`;
1776 CREATE TABLE `virtualshelfcontents` (
1777 `shelfnumber` int(11) NOT NULL default 0,
1778 `biblionumber` int(11) NOT NULL default 0,
1779 `flags` int(11) default NULL,
1780 `dateadded` timestamp NULL default NULL,
1781 KEY `shelfnumber` (`shelfnumber`),
1782 KEY `biblionumber` (`biblionumber`),
1783 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1784 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1785 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1788 -- Table structure for table `z3950servers`
1791 DROP TABLE IF EXISTS `z3950servers`;
1792 CREATE TABLE `z3950servers` (
1793 `host` varchar(255) default NULL,
1794 `port` int(11) default NULL,
1795 `db` varchar(255) default NULL,
1796 `userid` varchar(255) default NULL,
1797 `password` varchar(255) default NULL,
1799 `id` int(11) NOT NULL auto_increment,
1800 `checked` smallint(6) default NULL,
1801 `rank` int(11) default NULL,
1802 `syntax` varchar(80) default NULL,
1804 `position` enum('primary','secondary','') NOT NULL default 'primary',
1805 `type` enum('zed','opensearch') NOT NULL default 'zed',
1806 `description` text NOT NULL,
1808 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1811 -- Table structure for table `zebraqueue`
1814 DROP TABLE IF EXISTS `zebraqueue`;
1815 CREATE TABLE `zebraqueue` (
1816 `id` int(11) NOT NULL auto_increment,
1817 `biblio_auth_number` int(11) NOT NULL default '0',
1818 `operation` char(20) NOT NULL default '',
1819 `server` char(20) NOT NULL default '',
1820 `done` int(11) NOT NULL default '0',
1821 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1823 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1825 DROP TABLE IF EXISTS `services_throttle`;
1826 CREATE TABLE `services_throttle` (
1827 `service_type` varchar(10) NOT NULL default '',
1828 `service_count` varchar(45) default NULL,
1829 PRIMARY KEY (`service_type`)
1830 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1832 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1833 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1834 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1835 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1836 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1837 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1838 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1839 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;