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 UNIQUE KEY `cardnumber` (`cardnumber`),
481 PRIMARY KEY `borrowernumber` (`borrowernumber`),
482 KEY `categorycode` (`categorycode`),
483 KEY `branchcode` (`branchcode`),
484 KEY `userid` (`userid`),
485 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
486 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
487 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
490 -- Table structure for table `branchcategories`
493 DROP TABLE IF EXISTS `branchcategories`;
494 CREATE TABLE `branchcategories` (
495 `categorycode` varchar(10) NOT NULL default '',
496 `categoryname` varchar(32),
497 `codedescription` mediumtext,
498 `categorytype` varchar(16),
499 PRIMARY KEY (`categorycode`)
500 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
503 -- Table structure for table `branches`
506 DROP TABLE IF EXISTS `branches`;
507 CREATE TABLE `branches` (
508 `branchcode` varchar(10) NOT NULL default '',
509 `branchname` mediumtext NOT NULL,
510 `branchaddress1` mediumtext,
511 `branchaddress2` mediumtext,
512 `branchaddress3` mediumtext,
513 `branchphone` mediumtext,
514 `branchfax` mediumtext,
515 `branchemail` mediumtext,
516 `issuing` tinyint(4) default NULL,
517 `branchip` varchar(15) default NULL,
518 `branchprinter` varchar(100) default NULL,
519 UNIQUE KEY `branchcode` (`branchcode`)
520 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
523 -- Table structure for table `branchrelations`
526 DROP TABLE IF EXISTS `branchrelations`;
527 CREATE TABLE `branchrelations` (
528 `branchcode` varchar(10) NOT NULL default '',
529 `categorycode` varchar(10) NOT NULL default '',
530 PRIMARY KEY (`branchcode`,`categorycode`),
531 KEY `branchcode` (`branchcode`),
532 KEY `categorycode` (`categorycode`),
533 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
534 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
535 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
538 -- Table structure for table `branchtransfers`
541 DROP TABLE IF EXISTS `branchtransfers`;
542 CREATE TABLE `branchtransfers` (
543 `itemnumber` int(11) NOT NULL default 0,
544 `datesent` datetime default NULL,
545 `frombranch` varchar(10) NOT NULL default '',
546 `datearrived` datetime default NULL,
547 `tobranch` varchar(10) NOT NULL default '',
548 `comments` mediumtext,
549 KEY `frombranch` (`frombranch`),
550 KEY `tobranch` (`tobranch`),
551 KEY `itemnumber` (`itemnumber`),
552 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
553 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
554 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
555 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
559 -- Table structure for table `browser`
561 DROP TABLE IF EXISTS `browser`;
562 CREATE TABLE `browser` (
563 `level` int(11) NOT NULL,
564 `classification` varchar(20) NOT NULL,
565 `description` varchar(255) NOT NULL,
566 `number` bigint(20) NOT NULL,
567 `endnode` tinyint(4) NOT NULL
568 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
571 -- Table structure for table `categories`
574 DROP TABLE IF EXISTS `categories`;
575 CREATE TABLE `categories` (
576 `categorycode` varchar(10) NOT NULL default '',
577 `description` mediumtext,
578 `enrolmentperiod` smallint(6) default NULL,
579 `upperagelimit` smallint(6) default NULL,
580 `dateofbirthrequired` tinyint(1) default NULL,
581 `finetype` varchar(30) default NULL,
582 `bulk` tinyint(1) default NULL,
583 `enrolmentfee` decimal(28,6) default NULL,
584 `overduenoticerequired` tinyint(1) default NULL,
585 `issuelimit` smallint(6) default NULL,
586 `reservefee` decimal(28,6) default NULL,
587 `category_type` varchar(1) NOT NULL default 'A',
588 PRIMARY KEY (`categorycode`),
589 UNIQUE KEY `categorycode` (`categorycode`)
590 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
593 -- Table structure for table `categorytable`
596 DROP TABLE IF EXISTS `categorytable`;
597 CREATE TABLE `categorytable` (
598 `categorycode` varchar(5) NOT NULL default '',
600 `itemtypecodes` text,
601 PRIMARY KEY (`categorycode`)
602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
605 -- Table structure for table `cities`
608 DROP TABLE IF EXISTS `cities`;
609 CREATE TABLE `cities` (
610 `cityid` int(11) NOT NULL auto_increment,
611 `city_name` varchar(100) NOT NULL default '',
612 `city_zipcode` varchar(20) default NULL,
613 PRIMARY KEY (`cityid`)
614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
617 -- Table structure for table `class_sort_rules`
620 DROP TABLE IF EXISTS `class_sort_rules`;
621 CREATE TABLE `class_sort_rules` (
622 `class_sort_rule` varchar(10) NOT NULL default '',
623 `description` mediumtext,
624 `sort_routine` varchar(30) NOT NULL default '',
625 PRIMARY KEY (`class_sort_rule`),
626 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
627 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
630 -- Table structure for table `class_sources`
633 DROP TABLE IF EXISTS `class_sources`;
634 CREATE TABLE `class_sources` (
635 `cn_source` varchar(10) NOT NULL default '',
636 `description` mediumtext,
637 `used` tinyint(4) NOT NULL default 0,
638 `class_sort_rule` varchar(10) NOT NULL default '',
639 PRIMARY KEY (`cn_source`),
640 UNIQUE KEY `cn_source_idx` (`cn_source`),
641 KEY `used_idx` (`used`),
642 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
643 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
646 -- Table structure for table `currency`
649 DROP TABLE IF EXISTS `currency`;
650 CREATE TABLE `currency` (
651 `currency` varchar(10) NOT NULL default '',
652 `rate` float(7,5) default NULL,
653 PRIMARY KEY (`currency`)
654 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
657 -- Table structure for table `deletedbiblio`
660 DROP TABLE IF EXISTS `deletedbiblio`;
661 CREATE TABLE `deletedbiblio` (
662 `biblionumber` int(11) NOT NULL default 0,
663 `frameworkcode` varchar(4) NOT NULL default '',
666 `unititle` mediumtext,
668 `serial` tinyint(1) default NULL,
669 `seriestitle` mediumtext,
670 `copyrightdate` smallint(6) default NULL,
671 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
672 `datecreated` DATE NOT NULL,
673 `abstract` mediumtext,
674 PRIMARY KEY (`biblionumber`),
675 KEY `blbnoidx` (`biblionumber`)
676 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
679 -- Table structure for table `deletedbiblioitems`
682 DROP TABLE IF EXISTS `deletedbiblioitems`;
683 CREATE TABLE `deletedbiblioitems` (
684 `biblioitemnumber` int(11) NOT NULL default 0,
685 `biblionumber` int(11) NOT NULL default 0,
688 `itemtype` varchar(10) default NULL,
689 `isbn` varchar(14) default NULL,
690 `issn` varchar(9) default NULL,
691 `publicationyear` text,
692 `publishercode` varchar(255) default NULL,
693 `volumedate` date default NULL,
695 `collectiontitle` mediumtext default NULL,
696 `collectionissn` text default NULL,
697 `collectionvolume` mediumtext default NULL,
698 `editionstatement` text default NULL,
699 `editionresponsibility` text default NULL,
700 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
701 `illus` varchar(255) default NULL,
702 `pages` varchar(255) default NULL,
704 `size` varchar(255) default NULL,
705 `place` varchar(255) default NULL,
706 `lccn` varchar(25) default NULL,
708 `url` varchar(255) default NULL,
709 `cn_source` varchar(10) default NULL,
710 `cn_class` varchar(30) default NULL,
711 `cn_item` varchar(10) default NULL,
712 `cn_suffix` varchar(10) default NULL,
713 `cn_sort` varchar(30) default NULL,
714 `totalissues` int(10),
715 `marcxml` longtext NOT NULL,
716 PRIMARY KEY (`biblioitemnumber`),
717 KEY `bibinoidx` (`biblioitemnumber`),
718 KEY `bibnoidx` (`biblionumber`),
720 KEY `publishercode` (`publishercode`)
721 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
724 -- Table structure for table `deletedborrowers`
727 DROP TABLE IF EXISTS `deletedborrowers`;
728 CREATE TABLE `deletedborrowers` (
729 `borrowernumber` int(11) NOT NULL default 0,
730 `cardnumber` varchar(9) NOT NULL default '',
731 `surname` mediumtext NOT NULL,
734 `othernames` mediumtext,
736 `streetnumber` varchar(10) default NULL,
737 `streettype` varchar(50) default NULL,
738 `address` mediumtext NOT NULL,
740 `city` mediumtext NOT NULL,
741 `zipcode` varchar(25) default NULL,
744 `mobile` varchar(50) default NULL,
748 `B_streetnumber` varchar(10) default NULL,
749 `B_streettype` varchar(50) default NULL,
750 `B_address` varchar(100) default NULL,
752 `B_zipcode` varchar(25) default NULL,
754 `B_phone` mediumtext,
755 `dateofbirth` date default NULL,
756 `branchcode` varchar(10) NOT NULL default '',
757 `categorycode` varchar(2) default NULL,
758 `dateenrolled` date default NULL,
759 `dateexpiry` date default NULL,
760 `gonenoaddress` tinyint(1) default NULL,
761 `lost` tinyint(1) default NULL,
762 `debarred` tinyint(1) default NULL,
763 `contactname` mediumtext,
764 `contactfirstname` text,
766 `guarantorid` int(11) default NULL,
767 `borrowernotes` mediumtext,
768 `relationship` varchar(100) default NULL,
769 `ethnicity` varchar(50) default NULL,
770 `ethnotes` varchar(255) default NULL,
771 `sex` varchar(1) default NULL,
772 `password` varchar(30) default NULL,
773 `flags` int(11) default NULL,
774 `userid` varchar(30) default NULL,
775 `opacnote` mediumtext,
776 `contactnote` varchar(255) default NULL,
777 `sort1` varchar(80) default NULL,
778 `sort2` varchar(80) default NULL,
779 KEY `borrowernumber` (`borrowernumber`),
780 KEY `cardnumber` (`cardnumber`)
781 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
784 -- Table structure for table `deleteditems`
787 DROP TABLE IF EXISTS `deleteditems`;
788 CREATE TABLE `deleteditems` (
789 `itemnumber` int(11) NOT NULL default 0,
790 `biblionumber` int(11) NOT NULL default 0,
791 `biblioitemnumber` int(11) NOT NULL default 0,
792 `barcode` varchar(20) default NULL,
793 `dateaccessioned` date default NULL,
794 `booksellerid` varchar(10) default NULL,
795 `homebranch` varchar(10) default NULL,
796 `price` decimal(8,2) default NULL,
797 `replacementprice` decimal(8,2) default NULL,
798 `replacementpricedate` date default NULL,
799 `datelastborrowed` date default NULL,
800 `datelastseen` date default NULL,
801 `stack` tinyint(1) default NULL,
802 `notforloan` tinyint(1) default NULL,
803 `damaged` tinyint(1) default NULL,
804 `itemlost` tinyint(1) default NULL,
805 `wthdrawn` tinyint(1) default NULL,
806 `itemcallnumber` varchar(30) default NULL,
807 `issues` smallint(6) default NULL,
808 `renewals` smallint(6) default NULL,
809 `reserves` smallint(6) default NULL,
810 `restricted` tinyint(1) default NULL,
811 `itemnotes` mediumtext,
812 `holdingbranch` varchar(10) default NULL,
813 `paidfor` mediumtext,
814 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
815 `location` varchar(80) default NULL,
816 `onloan` date default NULL,
817 `cn_source` varchar(10) default NULL,
818 `cn_sort` varchar(30) default NULL,
819 `ccode` varchar(10) default NULL,
820 `materials` varchar(10) default NULL,
821 `uri` varchar(255) default NULL,
822 `itype` varchar(10) default NULL,
824 PRIMARY KEY (`itemnumber`),
825 KEY `delitembarcodeidx` (`barcode`),
826 KEY `delitembinoidx` (`biblioitemnumber`),
827 KEY `delitembibnoidx` (`biblionumber`),
828 KEY `delhomebranch` (`homebranch`),
829 KEY `delholdingbranch` (`holdingbranch`)
830 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
833 -- Table structure for table `ethnicity`
836 DROP TABLE IF EXISTS `ethnicity`;
837 CREATE TABLE `ethnicity` (
838 `code` varchar(10) NOT NULL default '',
839 `name` varchar(255) default NULL,
841 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
844 -- Table structure for table `import_batches`
847 DROP TABLE IF EXISTS `import_batches`;
848 CREATE TABLE `import_batches` (
849 `import_batch_id` int(11) NOT NULL auto_increment,
850 `matcher_id` int(11) default NULL,
851 `template_id` int(11) default NULL,
852 `branchcode` varchar(10) default NULL,
853 `num_biblios` int(11) NOT NULL default 0,
854 `num_items` int(11) NOT NULL default 0,
855 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
856 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
857 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
858 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
859 `file_name` varchar(100),
860 `comments` mediumtext,
861 PRIMARY KEY (`import_batch_id`),
862 KEY `branchcode` (`branchcode`)
863 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
866 -- Table structure for table `import_records`
869 DROP TABLE IF EXISTS `import_records`;
870 CREATE TABLE `import_records` (
871 `import_record_id` int(11) NOT NULL auto_increment,
872 `import_batch_id` int(11) NOT NULL,
873 `branchcode` varchar(10) default NULL,
874 `record_sequence` int(11) NOT NULL default 0,
875 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
876 `import_date` DATE default NULL,
877 `marc` longblob NOT NULL,
878 `marcxml` longtext NOT NULL,
879 `marcxml_old` longtext NOT NULL,
880 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
881 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
882 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
883 `import_error` mediumtext,
884 `encoding` varchar(40) NOT NULL default '',
885 `z3950random` varchar(40) default NULL,
886 PRIMARY KEY (`import_record_id`),
887 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
888 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
889 KEY `branchcode` (`branchcode`),
890 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
891 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
894 -- Table structure for `import_record_matches`
896 DROP TABLE IF EXISTS `import_record_matches`;
897 CREATE TABLE `import_record_matches` (
898 `import_record_id` int(11) NOT NULL,
899 `candidate_match_id` int(11) NOT NULL,
900 `score` int(11) NOT NULL default 0,
901 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
902 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
903 KEY `record_score` (`import_record_id`, `score`)
904 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
907 -- Table structure for table `import_biblios`
910 DROP TABLE IF EXISTS `import_biblios`;
911 CREATE TABLE `import_biblios` (
912 `import_record_id` int(11) NOT NULL,
913 `matched_biblionumber` int(11) default NULL,
914 `control_number` varchar(25) default NULL,
915 `original_source` varchar(25) default NULL,
916 `title` varchar(128) default NULL,
917 `author` varchar(80) default NULL,
918 `isbn` varchar(14) default NULL,
919 `issn` varchar(9) default NULL,
920 `has_items` tinyint(1) NOT NULL default 0,
921 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
922 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
923 KEY `matched_biblionumber` (`matched_biblionumber`),
924 KEY `title` (`title`),
926 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
929 -- Table structure for table `import_items`
932 DROP TABLE IF EXISTS `import_items`;
933 CREATE TABLE `import_items` (
934 `import_items_id` int(11) NOT NULL auto_increment,
935 `import_record_id` int(11) NOT NULL,
936 `itemnumber` int(11) default NULL,
937 `branchcode` varchar(10) default NULL,
938 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
939 `marcxml` longtext NOT NULL,
940 `import_error` mediumtext,
941 PRIMARY KEY (`import_items_id`),
942 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
943 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
944 KEY `itemnumber` (`itemnumber`),
945 KEY `branchcode` (`branchcode`)
946 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
949 -- Table structure for table `issues`
952 DROP TABLE IF EXISTS `issues`;
953 CREATE TABLE `issues` (
954 `borrowernumber` int(11) default NULL,
955 `itemnumber` int(11) default NULL,
956 `date_due` date default NULL,
957 `branchcode` varchar(10) default NULL,
958 `issuingbranch` varchar(18) default NULL,
959 `returndate` date default NULL,
960 `lastreneweddate` date default NULL,
961 `return` varchar(4) default NULL,
962 `renewals` tinyint(4) default NULL,
963 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
964 `issuedate` date default NULL,
965 KEY `issuesborridx` (`borrowernumber`),
966 KEY `issuesitemidx` (`itemnumber`),
967 KEY `bordate` (`borrowernumber`,`timestamp`),
968 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
969 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
970 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
973 -- Table structure for table `issuingrules`
976 DROP TABLE IF EXISTS `issuingrules`;
977 CREATE TABLE `issuingrules` (
978 `categorycode` varchar(10) NOT NULL default '',
979 `itemtype` varchar(10) NOT NULL default '',
980 `restrictedtype` tinyint(1) default NULL,
981 `rentaldiscount` decimal(28,6) default NULL,
982 `reservecharge` decimal(28,6) default NULL,
983 `fine` decimal(28,6) default NULL,
984 `firstremind` int(11) default NULL,
985 `chargeperiod` int(11) default NULL,
986 `accountsent` int(11) default NULL,
987 `chargename` varchar(100) default NULL,
988 `maxissueqty` int(4) default NULL,
989 `issuelength` int(4) default NULL,
990 `branchcode` varchar(10) NOT NULL default '',
991 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
992 KEY `categorycode` (`categorycode`),
993 KEY `itemtype` (`itemtype`)
994 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
997 -- Table structure for table `items`
1000 DROP TABLE IF EXISTS `items`;
1001 CREATE TABLE `items` (
1002 `itemnumber` int(11) NOT NULL auto_increment,
1003 `biblionumber` int(11) NOT NULL default 0,
1004 `biblioitemnumber` int(11) NOT NULL default 0,
1005 `barcode` varchar(20) default NULL,
1006 `dateaccessioned` date default NULL,
1007 `booksellerid` varchar(10) default NULL,
1008 `homebranch` varchar(10) default NULL,
1009 `price` decimal(8,2) default NULL,
1010 `replacementprice` decimal(8,2) default NULL,
1011 `replacementpricedate` date default NULL,
1012 `datelastborrowed` date default NULL,
1013 `datelastseen` date default NULL,
1014 `stack` tinyint(1) default NULL,
1015 `notforloan` tinyint(1) default NULL,
1016 `damaged` tinyint(1) default NULL,
1017 `itemlost` tinyint(1) default NULL,
1018 `wthdrawn` tinyint(1) default NULL,
1019 `itemcallnumber` varchar(30) default NULL,
1020 `issues` smallint(6) default NULL,
1021 `renewals` smallint(6) default NULL,
1022 `reserves` smallint(6) default NULL,
1023 `restricted` tinyint(1) default NULL,
1024 `itemnotes` mediumtext,
1025 `holdingbranch` varchar(10) default NULL,
1026 `paidfor` mediumtext,
1027 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1028 `location` varchar(80) default NULL,
1029 `onloan` date default NULL,
1030 `cn_source` varchar(10) default NULL,
1031 `cn_sort` varchar(30) default NULL,
1032 `ccode` varchar(10) default NULL,
1033 `materials` varchar(10) default NULL,
1034 `uri` varchar(255) default NULL,
1035 `itype` varchar(10) default NULL,
1036 PRIMARY KEY (`itemnumber`),
1037 UNIQUE KEY `itembarcodeidx` (`barcode`),
1038 KEY `itembinoidx` (`biblioitemnumber`),
1039 KEY `itembibnoidx` (`biblionumber`),
1040 KEY `homebranch` (`homebranch`),
1041 KEY `holdingbranch` (`holdingbranch`),
1042 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1043 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1044 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1045 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1048 -- Table structure for table `itemtypes`
1051 DROP TABLE IF EXISTS `itemtypes`;
1052 CREATE TABLE `itemtypes` (
1053 `itemtype` varchar(10) NOT NULL default '',
1054 `description` mediumtext,
1055 `renewalsallowed` smallint(6) default NULL,
1056 `rentalcharge` double(16,4) default NULL,
1057 `notforloan` smallint(6) default NULL,
1058 `imageurl` varchar(200) default NULL,
1060 PRIMARY KEY (`itemtype`),
1061 UNIQUE KEY `itemtype` (`itemtype`)
1062 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1065 -- Table structure for table `labels`
1068 DROP TABLE IF EXISTS `labels`;
1069 CREATE TABLE `labels` (
1070 `labelid` int(11) NOT NULL auto_increment,
1071 `batch_id` varchar(10) NOT NULL default 1,
1072 `itemnumber` varchar(100) NOT NULL default '',
1073 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1074 PRIMARY KEY (`labelid`)
1075 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1078 -- Table structure for table `labels_conf`
1081 DROP TABLE IF EXISTS `labels_conf`;
1082 CREATE TABLE `labels_conf` (
1083 `id` int(4) NOT NULL auto_increment,
1084 `barcodetype` char(100) default '',
1085 `title` int(1) default '0',
1086 `subtitle` int(1) default '0',
1087 `itemtype` int(1) default '0',
1088 `barcode` int(1) default '0',
1089 `dewey` int(1) default '0',
1090 `class` int(1) default NULL,
1091 `subclass` int(1) default '0',
1092 `itemcallnumber` int(1) default '0',
1093 `author` int(1) default '0',
1094 `issn` int(1) default '0',
1095 `isbn` int(1) default '0',
1096 `startlabel` int(2) NOT NULL default '1',
1097 `printingtype` char(32) default 'BAR',
1098 `layoutname` char(20) NOT NULL default 'TEST',
1099 `guidebox` int(1) default '0',
1100 `active` tinyint(1) default '1',
1101 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1102 `ccode` char(4) collate utf8_unicode_ci default NULL,
1103 `callnum_split` int(1) default NULL,
1104 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1109 -- Table structure for table `labels_templates`
1112 DROP TABLE IF EXISTS `labels_templates`;
1113 CREATE TABLE `labels_templates` (
1114 `tmpl_id` int(4) NOT NULL auto_increment,
1115 `tmpl_code` char(100) default '',
1116 `tmpl_desc` char(100) default '',
1117 `page_width` float default '0',
1118 `page_height` float default '0',
1119 `label_width` float default '0',
1120 `label_height` float default '0',
1121 `topmargin` float default '0',
1122 `leftmargin` float default '0',
1123 `cols` int(2) default '0',
1124 `rows` int(2) default '0',
1125 `colgap` float default '0',
1126 `rowgap` float default '0',
1127 `active` int(1) default NULL,
1128 `units` char(20) default 'PX',
1129 `fontsize` int(4) NOT NULL default '3',
1130 PRIMARY KEY (`tmpl_id`)
1131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1134 -- Table structure for table `letter`
1137 DROP TABLE IF EXISTS `letter`;
1138 CREATE TABLE `letter` (
1139 `module` varchar(20) NOT NULL default '',
1140 `code` varchar(20) NOT NULL default '',
1141 `name` varchar(100) NOT NULL default '',
1142 `title` varchar(200) NOT NULL default '',
1144 PRIMARY KEY (`module`,`code`)
1145 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1148 -- Table structure for table `marc_subfield_structure`
1151 DROP TABLE IF EXISTS `marc_subfield_structure`;
1152 CREATE TABLE `marc_subfield_structure` (
1153 `tagfield` varchar(3) NOT NULL default '',
1154 `tagsubfield` varchar(1) NOT NULL default '',
1155 `liblibrarian` varchar(255) NOT NULL default '',
1156 `libopac` varchar(255) NOT NULL default '',
1157 `repeatable` tinyint(4) NOT NULL default 0,
1158 `mandatory` tinyint(4) NOT NULL default 0,
1159 `kohafield` varchar(40) default NULL,
1160 `tab` tinyint(1) default NULL,
1161 `authorised_value` varchar(20) default NULL,
1162 `authtypecode` varchar(20) default NULL,
1163 `value_builder` varchar(80) default NULL,
1164 `isurl` tinyint(1) default NULL,
1165 `hidden` tinyint(1) default NULL,
1166 `frameworkcode` varchar(4) NOT NULL default '',
1167 `seealso` varchar(1100) default NULL,
1168 `link` varchar(80) default NULL,
1169 `defaultvalue` text default NULL,
1170 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1171 KEY `kohafield_2` (`kohafield`),
1172 KEY `tab` (`frameworkcode`,`tab`),
1173 KEY `kohafield` (`frameworkcode`,`kohafield`)
1174 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1177 -- Table structure for table `marc_tag_structure`
1180 DROP TABLE IF EXISTS `marc_tag_structure`;
1181 CREATE TABLE `marc_tag_structure` (
1182 `tagfield` varchar(3) NOT NULL default '',
1183 `liblibrarian` varchar(255) NOT NULL default '',
1184 `libopac` varchar(255) NOT NULL default '',
1185 `repeatable` tinyint(4) NOT NULL default 0,
1186 `mandatory` tinyint(4) NOT NULL default 0,
1187 `authorised_value` varchar(10) default NULL,
1188 `frameworkcode` varchar(4) NOT NULL default '',
1189 PRIMARY KEY (`frameworkcode`,`tagfield`)
1190 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1193 -- Table structure for table `marc_matchers`
1196 DROP TABLE IF EXISTS `marc_matchers`;
1197 CREATE TABLE `marc_matchers` (
1198 `matcher_id` int(11) NOT NULL auto_increment,
1199 `code` varchar(10) NOT NULL default '',
1200 `description` varchar(255) NOT NULL default '',
1201 `record_type` varchar(10) NOT NULL default 'biblio',
1202 `threshold` int(11) NOT NULL default 0,
1203 PRIMARY KEY (`matcher_id`),
1204 KEY `code` (`code`),
1205 KEY `record_type` (`record_type`)
1206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1209 -- Table structure for table `matchpoints`
1211 DROP TABLE IF EXISTS `matchpoints`;
1212 CREATE TABLE `matchpoints` (
1213 `matcher_id` int(11) NOT NULL,
1214 `matchpoint_id` int(11) NOT NULL auto_increment,
1215 `search_index` varchar(30) NOT NULL default '',
1216 `score` int(11) NOT NULL default 0,
1217 PRIMARY KEY (`matchpoint_id`),
1218 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1219 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1220 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1224 -- Table structure for table `matchpoint_components`
1226 DROP TABLE IF EXISTS `matchpoint_components`;
1227 CREATE TABLE `matchpoint_components` (
1228 `matchpoint_id` int(11) NOT NULL,
1229 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1230 sequence int(11) NOT NULL default 0,
1231 tag varchar(3) NOT NULL default '',
1232 subfields varchar(40) NOT NULL default '',
1233 offset int(4) NOT NULL default 0,
1234 length int(4) NOT NULL default 0,
1235 PRIMARY KEY (`matchpoint_component_id`),
1236 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1237 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1238 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1239 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1242 -- Table structure for table `matcher_component_norms`
1244 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1245 CREATE TABLE `matchpoint_component_norms` (
1246 `matchpoint_component_id` int(11) NOT NULL,
1247 `sequence` int(11) NOT NULL default 0,
1248 `norm_routine` varchar(50) NOT NULL default '',
1249 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1250 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1251 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1252 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1255 -- Table structure for table `matcher_matchpoints`
1257 DROP TABLE IF EXISTS `matcher_matchpoints`;
1258 CREATE TABLE `matcher_matchpoints` (
1259 `matcher_id` int(11) NOT NULL,
1260 `matchpoint_id` int(11) NOT NULL,
1261 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1262 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1263 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1264 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1268 -- Table structure for table `matchchecks`
1270 DROP TABLE IF EXISTS `matchchecks`;
1271 CREATE TABLE `matchchecks` (
1272 `matcher_id` int(11) NOT NULL,
1273 `matchcheck_id` int(11) NOT NULL auto_increment,
1274 `source_matchpoint_id` int(11) NOT NULL,
1275 `target_matchpoint_id` int(11) NOT NULL,
1276 PRIMARY KEY (`matchcheck_id`),
1277 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1278 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1279 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1280 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1281 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1282 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1283 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1286 -- Table structure for table `mediatypetable`
1289 DROP TABLE IF EXISTS `mediatypetable`;
1290 CREATE TABLE `mediatypetable` (
1291 `mediatypecode` varchar(5) NOT NULL default '',
1293 `itemtypecodes` text,
1294 PRIMARY KEY (`mediatypecode`)
1295 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1298 -- Table structure for table `notifys`
1301 DROP TABLE IF EXISTS `notifys`;
1302 CREATE TABLE `notifys` (
1303 `notify_id` int(11) NOT NULL default 0,
1304 `borrowernumber` int(11) NOT NULL default 0,
1305 `itemnumber` int(11) NOT NULL default 0,
1306 `notify_date` date default NULL,
1307 `notify_send_date` date default NULL,
1308 `notify_level` int(1) NOT NULL default 0,
1309 `method` varchar(20) NOT NULL default ''
1310 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1313 -- Table structure for table `nozebra`
1315 DROP TABLE IF EXISTS `nozebra`;
1316 CREATE TABLE `nozebra` (
1317 `server` varchar(20) NOT NULL,
1318 `indexname` varchar(40) NOT NULL,
1319 `value` varchar(250) NOT NULL,
1320 `biblionumbers` longtext NOT NULL,
1321 KEY `indexname` (`server`,`indexname`),
1322 KEY `value` (`server`,`value`))
1323 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1326 -- Table structure for table `opac_news`
1329 DROP TABLE IF EXISTS `opac_news`;
1330 CREATE TABLE `opac_news` (
1331 `idnew` int(10) unsigned NOT NULL auto_increment,
1332 `title` varchar(250) NOT NULL default '',
1333 `new` text NOT NULL,
1334 `lang` varchar(4) NOT NULL default '',
1335 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1336 `expirationdate` date default NULL,
1337 `number` int(11) default NULL,
1338 PRIMARY KEY (`idnew`)
1339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1342 -- Table structure for table `overduerules`
1345 DROP TABLE IF EXISTS `overduerules`;
1346 CREATE TABLE `overduerules` (
1347 `branchcode` varchar(10) NOT NULL default '',
1348 `categorycode` varchar(2) NOT NULL default '',
1349 `delay1` int(4) default 0,
1350 `letter1` varchar(20) default NULL,
1351 `debarred1` varchar(1) default 0,
1352 `delay2` int(4) default 0,
1353 `debarred2` varchar(1) default 0,
1354 `letter2` varchar(20) default NULL,
1355 `delay3` int(4) default 0,
1356 `letter3` varchar(20) default NULL,
1357 `debarred3` int(1) default 0,
1358 PRIMARY KEY (`branchcode`,`categorycode`)
1359 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1362 -- Table structure for table `printers`
1365 DROP TABLE IF EXISTS `printers`;
1366 CREATE TABLE `printers` (
1367 `printername` varchar(40) NOT NULL default '',
1368 `printqueue` varchar(20) default NULL,
1369 `printtype` varchar(20) default NULL,
1370 PRIMARY KEY (`printername`)
1371 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1374 -- Table structure for table `repeatable_holidays`
1377 DROP TABLE IF EXISTS `repeatable_holidays`;
1378 CREATE TABLE `repeatable_holidays` (
1379 `id` int(11) NOT NULL auto_increment,
1380 `branchcode` varchar(10) NOT NULL default '',
1381 `weekday` smallint(6) default NULL,
1382 `day` smallint(6) default NULL,
1383 `month` smallint(6) default NULL,
1384 `title` varchar(50) NOT NULL default '',
1385 `description` text NOT NULL,
1387 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1390 -- Table structure for table `reports_dictionary`
1393 DROP TABLE IF EXISTS `reports_dictionary`;
1394 CREATE TABLE reports_dictionary (
1395 `id` int(11) NOT NULL auto_increment,
1396 `name` varchar(255) default NULL,
1398 `date_created` datetime default NULL,
1399 `date_modified` datetime default NULL,
1401 `area` int(11) default NULL,
1403 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1406 -- Table structure for table `reserveconstraints`
1409 DROP TABLE IF EXISTS `reserveconstraints`;
1410 CREATE TABLE `reserveconstraints` (
1411 `borrowernumber` int(11) NOT NULL default 0,
1412 `reservedate` date default NULL,
1413 `biblionumber` int(11) NOT NULL default 0,
1414 `biblioitemnumber` int(11) default NULL,
1415 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1416 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1419 -- Table structure for table `reserves`
1422 DROP TABLE IF EXISTS `reserves`;
1423 CREATE TABLE `reserves` (
1424 `borrowernumber` int(11) NOT NULL default 0,
1425 `reservedate` date default NULL,
1426 `biblionumber` int(11) NOT NULL default 0,
1427 `constrainttype` varchar(1) default NULL,
1428 `branchcode` varchar(10) default NULL,
1429 `notificationdate` date default NULL,
1430 `reminderdate` date default NULL,
1431 `cancellationdate` date default NULL,
1432 `reservenotes` mediumtext,
1433 `priority` smallint(6) default NULL,
1434 `found` varchar(1) default NULL,
1435 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1436 `itemnumber` int(11) default NULL,
1437 `waitingdate` date default NULL,
1438 KEY `borrowernumber` (`borrowernumber`),
1439 KEY `biblionumber` (`biblionumber`),
1440 KEY `itemnumber` (`itemnumber`),
1441 KEY `branchcode` (`branchcode`),
1442 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1443 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1444 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1445 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1446 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1449 -- Table structure for table `reviews`
1452 DROP TABLE IF EXISTS `reviews`;
1453 CREATE TABLE `reviews` (
1454 `reviewid` int(11) NOT NULL auto_increment,
1455 `borrowernumber` int(11) default NULL,
1456 `biblionumber` int(11) default NULL,
1458 `approved` tinyint(4) default NULL,
1459 `datereviewed` datetime default NULL,
1460 PRIMARY KEY (`reviewid`)
1461 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1464 -- Table structure for table `roadtype`
1467 DROP TABLE IF EXISTS `roadtype`;
1468 CREATE TABLE `roadtype` (
1469 `roadtypeid` int(11) NOT NULL auto_increment,
1470 `road_type` varchar(100) NOT NULL default '',
1471 PRIMARY KEY (`roadtypeid`)
1472 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1475 -- Table structure for table `saved_sql`
1478 DROP TABLE IF EXISTS `saved_sql`;
1479 CREATE TABLE saved_sql (
1480 `id` int(11) NOT NULL auto_increment,
1481 `borrowernumber` int(11) default NULL,
1482 `date_created` datetime default NULL,
1483 `last_modified` datetime default NULL,
1485 `last_run` datetime default NULL,
1486 `report_name` varchar(255) default NULL,
1487 `type` varchar(255) default NULL,
1490 KEY boridx (`borrowernumber`)
1491 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1495 -- Table structure for `saved_reports`
1498 DROP TABLE IF EXISTS `saved_reports`;
1499 CREATE TABLE saved_reports (
1500 `id` int(11) NOT NULL auto_increment,
1501 `report_id` int(11) default NULL,
1503 `date_run` datetime default NULL,
1505 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1509 -- Table structure for table `serial`
1512 DROP TABLE IF EXISTS `serial`;
1513 CREATE TABLE `serial` (
1514 `serialid` int(11) NOT NULL auto_increment,
1515 `biblionumber` varchar(100) NOT NULL default '',
1516 `subscriptionid` varchar(100) NOT NULL default '',
1517 `serialseq` varchar(100) NOT NULL default '',
1518 `status` tinyint(4) NOT NULL default 0,
1519 `planneddate` date default NULL,
1521 `publisheddate` date default NULL,
1523 `claimdate` date default NULL,
1524 `routingnotes` text,
1525 PRIMARY KEY (`serialid`)
1526 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1529 -- Table structure for table `sessions`
1532 DROP TABLE IF EXISTS sessions;
1533 CREATE TABLE sessions (
1534 `id` varchar(32) NOT NULL,
1535 `a_session` text NOT NULL,
1537 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1540 -- Table structure for table `special_holidays`
1543 DROP TABLE IF EXISTS `special_holidays`;
1544 CREATE TABLE `special_holidays` (
1545 `id` int(11) NOT NULL auto_increment,
1546 `branchcode` varchar(10) NOT NULL default '',
1547 `day` smallint(6) NOT NULL default 0,
1548 `month` smallint(6) NOT NULL default 0,
1549 `year` smallint(6) NOT NULL default 0,
1550 `isexception` smallint(1) NOT NULL default 1,
1551 `title` varchar(50) NOT NULL default '',
1552 `description` text NOT NULL,
1554 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1557 -- Table structure for table `statistics`
1560 DROP TABLE IF EXISTS `statistics`;
1561 CREATE TABLE `statistics` (
1562 `datetime` datetime default NULL,
1563 `branch` varchar(10) default NULL,
1564 `proccode` varchar(4) default NULL,
1565 `value` double(16,4) default NULL,
1566 `type` varchar(16) default NULL,
1568 `usercode` varchar(10) default NULL,
1569 `itemnumber` int(11) default NULL,
1570 `itemtype` varchar(10) default NULL,
1571 `borrowernumber` int(11) default NULL,
1572 `associatedborrower` int(11) default NULL,
1573 KEY `timeidx` (`datetime`)
1574 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1577 -- Table structure for table `stopwords`
1580 DROP TABLE IF EXISTS `stopwords`;
1581 CREATE TABLE `stopwords` (
1582 `word` varchar(255) default NULL
1583 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1586 -- Table structure for table `subcategorytable`
1589 DROP TABLE IF EXISTS `subcategorytable`;
1590 CREATE TABLE `subcategorytable` (
1591 `subcategorycode` varchar(5) NOT NULL default '',
1593 `itemtypecodes` text,
1594 PRIMARY KEY (`subcategorycode`)
1595 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1598 -- Table structure for table `subscription`
1601 DROP TABLE IF EXISTS `subscription`;
1602 CREATE TABLE `subscription` (
1603 `biblionumber` int(11) NOT NULL default 0,
1604 `subscriptionid` int(11) NOT NULL auto_increment,
1605 `librarian` varchar(100) default '',
1606 `startdate` date default NULL,
1607 `aqbooksellerid` int(11) default 0,
1608 `cost` int(11) default 0,
1609 `aqbudgetid` int(11) default 0,
1610 `weeklength` tinyint(4) default 0,
1611 `monthlength` tinyint(4) default 0,
1612 `numberlength` tinyint(4) default 0,
1613 `periodicity` tinyint(4) default 0,
1614 `dow` varchar(100) default '',
1615 `numberingmethod` varchar(100) default '',
1617 `status` varchar(100) NOT NULL default '',
1618 `add1` int(11) default 0,
1619 `every1` int(11) default 0,
1620 `whenmorethan1` int(11) default 0,
1621 `setto1` int(11) default NULL,
1622 `lastvalue1` int(11) default NULL,
1623 `add2` int(11) default 0,
1624 `every2` int(11) default 0,
1625 `whenmorethan2` int(11) default 0,
1626 `setto2` int(11) default NULL,
1627 `lastvalue2` int(11) default NULL,
1628 `add3` int(11) default 0,
1629 `every3` int(11) default 0,
1630 `innerloop1` int(11) default 0,
1631 `innerloop2` int(11) default 0,
1632 `innerloop3` int(11) default 0,
1633 `whenmorethan3` int(11) default 0,
1634 `setto3` int(11) default NULL,
1635 `lastvalue3` int(11) default NULL,
1636 `issuesatonce` tinyint(3) NOT NULL default 1,
1637 `firstacquidate` date default NULL,
1638 `manualhistory` tinyint(1) NOT NULL default 0,
1639 `irregularity` text,
1640 `letter` varchar(20) default NULL,
1641 `numberpattern` tinyint(3) default 0,
1642 `distributedto` text,
1643 `internalnotes` longtext,
1645 `branchcode` varchar(10) NOT NULL default '',
1646 `hemisphere` tinyint(3) default 0,
1647 `lastbranch` varchar(10),
1648 PRIMARY KEY (`subscriptionid`)
1649 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1652 -- Table structure for table `subscriptionhistory`
1655 DROP TABLE IF EXISTS `subscriptionhistory`;
1656 CREATE TABLE `subscriptionhistory` (
1657 `biblionumber` int(11) NOT NULL default 0,
1658 `subscriptionid` int(11) NOT NULL default 0,
1659 `histstartdate` date default NULL,
1660 `enddate` date default NULL,
1661 `missinglist` longtext NOT NULL,
1662 `recievedlist` longtext NOT NULL,
1663 `opacnote` varchar(150) NOT NULL default '',
1664 `librariannote` varchar(150) NOT NULL default '',
1665 PRIMARY KEY (`subscriptionid`),
1666 KEY `biblionumber` (`biblionumber`)
1667 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1670 -- Table structure for table `subscriptionroutinglist`
1673 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1674 CREATE TABLE `subscriptionroutinglist` (
1675 `routingid` int(11) NOT NULL auto_increment,
1676 `borrowernumber` int(11) default NULL,
1677 `ranking` int(11) default NULL,
1678 `subscriptionid` int(11) default NULL,
1679 PRIMARY KEY (`routingid`)
1680 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1683 -- Table structure for table `suggestions`
1686 DROP TABLE IF EXISTS `suggestions`;
1687 CREATE TABLE `suggestions` (
1688 `suggestionid` int(8) NOT NULL auto_increment,
1689 `suggestedby` int(11) NOT NULL default 0,
1690 `managedby` int(11) default NULL,
1691 `STATUS` varchar(10) NOT NULL default '',
1693 `author` varchar(80) default NULL,
1694 `title` varchar(80) default NULL,
1695 `copyrightdate` smallint(6) default NULL,
1696 `publishercode` varchar(255) default NULL,
1697 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1698 `volumedesc` varchar(255) default NULL,
1699 `publicationyear` smallint(6) default 0,
1700 `place` varchar(255) default NULL,
1701 `isbn` varchar(10) default NULL,
1702 `mailoverseeing` smallint(1) default 0,
1703 `biblionumber` int(11) default NULL,
1705 PRIMARY KEY (`suggestionid`),
1706 KEY `suggestedby` (`suggestedby`),
1707 KEY `managedby` (`managedby`)
1708 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1711 -- Table structure for table `systempreferences`
1714 DROP TABLE IF EXISTS `systempreferences`;
1715 CREATE TABLE `systempreferences` (
1716 `variable` varchar(50) NOT NULL default '',
1718 `options` mediumtext,
1720 `type` varchar(20) default NULL,
1721 PRIMARY KEY (`variable`)
1722 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1725 -- Table structure for table `tags`
1728 DROP TABLE IF EXISTS `tags`;
1729 CREATE TABLE `tags` (
1730 `entry` varchar(255) NOT NULL default '',
1731 `weight` bigint(20) NOT NULL default 0,
1732 PRIMARY KEY (`entry`)
1733 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1736 -- Table structure for table `userflags`
1739 DROP TABLE IF EXISTS `userflags`;
1740 CREATE TABLE `userflags` (
1741 `bit` int(11) NOT NULL default 0,
1742 `flag` varchar(30) default NULL,
1743 `flagdesc` varchar(255) default NULL,
1744 `defaulton` int(11) default NULL,
1746 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1749 -- Table structure for table `virtualshelves`
1752 DROP TABLE IF EXISTS `virtualshelves`;
1753 CREATE TABLE `virtualshelves` (
1754 `shelfnumber` int(11) NOT NULL auto_increment,
1755 `shelfname` varchar(255) default NULL,
1756 `owner` varchar(80) default NULL,
1757 `category` varchar(1) default NULL,
1758 `sortfield` varchar(16) default NULL,
1759 PRIMARY KEY (`shelfnumber`)
1760 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1763 -- Table structure for table `virtualshelfcontents`
1766 DROP TABLE IF EXISTS `virtualshelfcontents`;
1767 CREATE TABLE `virtualshelfcontents` (
1768 `shelfnumber` int(11) NOT NULL default 0,
1769 `biblionumber` int(11) NOT NULL default 0,
1770 `flags` int(11) default NULL,
1771 `dateadded` timestamp NULL default NULL,
1772 KEY `shelfnumber` (`shelfnumber`),
1773 KEY `biblionumber` (`biblionumber`),
1774 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1775 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1776 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1779 -- Table structure for table `z3950servers`
1782 DROP TABLE IF EXISTS `z3950servers`;
1783 CREATE TABLE `z3950servers` (
1784 `host` varchar(255) default NULL,
1785 `port` int(11) default NULL,
1786 `db` varchar(255) default NULL,
1787 `userid` varchar(255) default NULL,
1788 `password` varchar(255) default NULL,
1790 `id` int(11) NOT NULL auto_increment,
1791 `checked` smallint(6) default NULL,
1792 `rank` int(11) default NULL,
1793 `syntax` varchar(80) default NULL,
1795 `position` enum('primary','secondary','') NOT NULL default 'primary',
1796 `type` enum('zed','opensearch') NOT NULL default 'zed',
1797 `description` text NOT NULL,
1799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1802 -- Table structure for table `zebraqueue`
1805 DROP TABLE IF EXISTS `zebraqueue`;
1806 CREATE TABLE `zebraqueue` (
1807 `id` int(11) NOT NULL auto_increment,
1808 `biblio_auth_number` int(11) NOT NULL default '0',
1809 `operation` char(20) NOT NULL default '',
1810 `server` char(20) NOT NULL default '',
1811 `done` int(11) NOT NULL default '0',
1812 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1814 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1816 DROP TABLE IF EXISTS `services_throttle`;
1817 CREATE TABLE `services_throttle` (
1818 `service_type` varchar(10) NOT NULL default '',
1819 `service_count` varchar(45) default NULL,
1820 PRIMARY KEY (`service_type`)
1821 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1823 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1824 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1825 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1826 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1827 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1828 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1829 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1830 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;