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 `rate` float(7,5) default NULL,
660 PRIMARY KEY (`currency`)
661 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
664 -- Table structure for table `deletedbiblio`
667 DROP TABLE IF EXISTS `deletedbiblio`;
668 CREATE TABLE `deletedbiblio` (
669 `biblionumber` int(11) NOT NULL default 0,
670 `frameworkcode` varchar(4) NOT NULL default '',
673 `unititle` mediumtext,
675 `serial` tinyint(1) default NULL,
676 `seriestitle` mediumtext,
677 `copyrightdate` smallint(6) default NULL,
678 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
679 `datecreated` DATE NOT NULL,
680 `abstract` mediumtext,
681 PRIMARY KEY (`biblionumber`),
682 KEY `blbnoidx` (`biblionumber`)
683 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
686 -- Table structure for table `deletedbiblioitems`
689 DROP TABLE IF EXISTS `deletedbiblioitems`;
690 CREATE TABLE `deletedbiblioitems` (
691 `biblioitemnumber` int(11) NOT NULL default 0,
692 `biblionumber` int(11) NOT NULL default 0,
695 `itemtype` varchar(10) default NULL,
696 `isbn` varchar(14) default NULL,
697 `issn` varchar(9) default NULL,
698 `publicationyear` text,
699 `publishercode` varchar(255) default NULL,
700 `volumedate` date default NULL,
702 `collectiontitle` mediumtext default NULL,
703 `collectionissn` text default NULL,
704 `collectionvolume` mediumtext default NULL,
705 `editionstatement` text default NULL,
706 `editionresponsibility` text default NULL,
707 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
708 `illus` varchar(255) default NULL,
709 `pages` varchar(255) default NULL,
711 `size` varchar(255) default NULL,
712 `place` varchar(255) default NULL,
713 `lccn` varchar(25) default NULL,
715 `url` varchar(255) default NULL,
716 `cn_source` varchar(10) default NULL,
717 `cn_class` varchar(30) default NULL,
718 `cn_item` varchar(10) default NULL,
719 `cn_suffix` varchar(10) default NULL,
720 `cn_sort` varchar(30) default NULL,
721 `totalissues` int(10),
722 `marcxml` longtext NOT NULL,
723 PRIMARY KEY (`biblioitemnumber`),
724 KEY `bibinoidx` (`biblioitemnumber`),
725 KEY `bibnoidx` (`biblionumber`),
727 KEY `publishercode` (`publishercode`)
728 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
731 -- Table structure for table `deletedborrowers`
734 DROP TABLE IF EXISTS `deletedborrowers`;
735 CREATE TABLE `deletedborrowers` (
736 `borrowernumber` int(11) NOT NULL default 0,
737 `cardnumber` varchar(9) NOT NULL default '',
738 `surname` mediumtext NOT NULL,
741 `othernames` mediumtext,
743 `streetnumber` varchar(10) default NULL,
744 `streettype` varchar(50) default NULL,
745 `address` mediumtext NOT NULL,
747 `city` mediumtext NOT NULL,
748 `zipcode` varchar(25) default NULL,
751 `mobile` varchar(50) default NULL,
755 `B_streetnumber` varchar(10) default NULL,
756 `B_streettype` varchar(50) default NULL,
757 `B_address` varchar(100) default NULL,
759 `B_zipcode` varchar(25) default NULL,
761 `B_phone` mediumtext,
762 `dateofbirth` date default NULL,
763 `branchcode` varchar(10) NOT NULL default '',
764 `categorycode` varchar(2) default NULL,
765 `dateenrolled` date default NULL,
766 `dateexpiry` date default NULL,
767 `gonenoaddress` tinyint(1) default NULL,
768 `lost` tinyint(1) default NULL,
769 `debarred` tinyint(1) default NULL,
770 `contactname` mediumtext,
771 `contactfirstname` text,
773 `guarantorid` int(11) default NULL,
774 `borrowernotes` mediumtext,
775 `relationship` varchar(100) default NULL,
776 `ethnicity` varchar(50) default NULL,
777 `ethnotes` varchar(255) default NULL,
778 `sex` varchar(1) default NULL,
779 `password` varchar(30) default NULL,
780 `flags` int(11) default NULL,
781 `userid` varchar(30) default NULL,
782 `opacnote` mediumtext,
783 `contactnote` varchar(255) default NULL,
784 `sort1` varchar(80) default NULL,
785 `sort2` varchar(80) default NULL,
786 KEY `borrowernumber` (`borrowernumber`),
787 KEY `cardnumber` (`cardnumber`)
788 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
791 -- Table structure for table `deleteditems`
794 DROP TABLE IF EXISTS `deleteditems`;
795 CREATE TABLE `deleteditems` (
796 `itemnumber` int(11) NOT NULL default 0,
797 `biblionumber` int(11) NOT NULL default 0,
798 `biblioitemnumber` int(11) NOT NULL default 0,
799 `barcode` varchar(20) default NULL,
800 `dateaccessioned` date default NULL,
801 `booksellerid` varchar(10) default NULL,
802 `homebranch` varchar(10) default NULL,
803 `price` decimal(8,2) default NULL,
804 `replacementprice` decimal(8,2) default NULL,
805 `replacementpricedate` date default NULL,
806 `datelastborrowed` date default NULL,
807 `datelastseen` date default NULL,
808 `stack` tinyint(1) default NULL,
809 `notforloan` tinyint(1) NOT NULL default 0,
810 `damaged` tinyint(1) NOT NULL default 0,
811 `itemlost` tinyint(1) NOT NULL default 0,
812 `wthdrawn` tinyint(1) NOT NULL default 0,
813 `itemcallnumber` varchar(30) default NULL,
814 `issues` smallint(6) default NULL,
815 `renewals` smallint(6) default NULL,
816 `reserves` smallint(6) default NULL,
817 `restricted` tinyint(1) default NULL,
818 `itemnotes` mediumtext,
819 `holdingbranch` varchar(10) default NULL,
820 `paidfor` mediumtext,
821 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
822 `location` varchar(80) default NULL,
823 `onloan` date default NULL,
824 `cn_source` varchar(10) default NULL,
825 `cn_sort` varchar(30) default NULL,
826 `ccode` varchar(10) default NULL,
827 `materials` varchar(10) default NULL,
828 `uri` varchar(255) default NULL,
829 `itype` varchar(10) default NULL,
831 PRIMARY KEY (`itemnumber`),
832 KEY `delitembarcodeidx` (`barcode`),
833 KEY `delitembinoidx` (`biblioitemnumber`),
834 KEY `delitembibnoidx` (`biblionumber`),
835 KEY `delhomebranch` (`homebranch`),
836 KEY `delholdingbranch` (`holdingbranch`)
837 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
840 -- Table structure for table `ethnicity`
843 DROP TABLE IF EXISTS `ethnicity`;
844 CREATE TABLE `ethnicity` (
845 `code` varchar(10) NOT NULL default '',
846 `name` varchar(255) default NULL,
848 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
851 -- Table structure for table `import_batches`
854 DROP TABLE IF EXISTS `import_batches`;
855 CREATE TABLE `import_batches` (
856 `import_batch_id` int(11) NOT NULL auto_increment,
857 `matcher_id` int(11) default NULL,
858 `template_id` int(11) default NULL,
859 `branchcode` varchar(10) default NULL,
860 `num_biblios` int(11) NOT NULL default 0,
861 `num_items` int(11) NOT NULL default 0,
862 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
863 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
864 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
865 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
866 `file_name` varchar(100),
867 `comments` mediumtext,
868 PRIMARY KEY (`import_batch_id`),
869 KEY `branchcode` (`branchcode`)
870 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
873 -- Table structure for table `import_records`
876 DROP TABLE IF EXISTS `import_records`;
877 CREATE TABLE `import_records` (
878 `import_record_id` int(11) NOT NULL auto_increment,
879 `import_batch_id` int(11) NOT NULL,
880 `branchcode` varchar(10) default NULL,
881 `record_sequence` int(11) NOT NULL default 0,
882 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
883 `import_date` DATE default NULL,
884 `marc` longblob NOT NULL,
885 `marcxml` longtext NOT NULL,
886 `marcxml_old` longtext NOT NULL,
887 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
888 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
889 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
890 `import_error` mediumtext,
891 `encoding` varchar(40) NOT NULL default '',
892 `z3950random` varchar(40) default NULL,
893 PRIMARY KEY (`import_record_id`),
894 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
895 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
896 KEY `branchcode` (`branchcode`),
897 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
898 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
901 -- Table structure for `import_record_matches`
903 DROP TABLE IF EXISTS `import_record_matches`;
904 CREATE TABLE `import_record_matches` (
905 `import_record_id` int(11) NOT NULL,
906 `candidate_match_id` int(11) NOT NULL,
907 `score` int(11) NOT NULL default 0,
908 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
909 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
910 KEY `record_score` (`import_record_id`, `score`)
911 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
914 -- Table structure for table `import_biblios`
917 DROP TABLE IF EXISTS `import_biblios`;
918 CREATE TABLE `import_biblios` (
919 `import_record_id` int(11) NOT NULL,
920 `matched_biblionumber` int(11) default NULL,
921 `control_number` varchar(25) default NULL,
922 `original_source` varchar(25) default NULL,
923 `title` varchar(128) default NULL,
924 `author` varchar(80) default NULL,
925 `isbn` varchar(14) default NULL,
926 `issn` varchar(9) default NULL,
927 `has_items` tinyint(1) NOT NULL default 0,
928 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
929 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
930 KEY `matched_biblionumber` (`matched_biblionumber`),
931 KEY `title` (`title`),
933 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
936 -- Table structure for table `import_items`
939 DROP TABLE IF EXISTS `import_items`;
940 CREATE TABLE `import_items` (
941 `import_items_id` int(11) NOT NULL auto_increment,
942 `import_record_id` int(11) NOT NULL,
943 `itemnumber` int(11) default NULL,
944 `branchcode` varchar(10) default NULL,
945 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
946 `marcxml` longtext NOT NULL,
947 `import_error` mediumtext,
948 PRIMARY KEY (`import_items_id`),
949 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
950 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
951 KEY `itemnumber` (`itemnumber`),
952 KEY `branchcode` (`branchcode`)
953 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
956 -- Table structure for table `issues`
959 DROP TABLE IF EXISTS `issues`;
960 CREATE TABLE `issues` (
961 `borrowernumber` int(11) default NULL,
962 `itemnumber` int(11) default NULL,
963 `date_due` date default NULL,
964 `branchcode` varchar(10) default NULL,
965 `issuingbranch` varchar(18) default NULL,
966 `returndate` date default NULL,
967 `lastreneweddate` date default NULL,
968 `return` varchar(4) default NULL,
969 `renewals` tinyint(4) default NULL,
970 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
971 `issuedate` date default NULL,
972 KEY `issuesborridx` (`borrowernumber`),
973 KEY `issuesitemidx` (`itemnumber`),
974 KEY `bordate` (`borrowernumber`,`timestamp`),
975 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
976 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
977 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
980 -- Table structure for table `issuingrules`
983 DROP TABLE IF EXISTS `issuingrules`;
984 CREATE TABLE `issuingrules` (
985 `categorycode` varchar(10) NOT NULL default '',
986 `itemtype` varchar(10) NOT NULL default '',
987 `restrictedtype` tinyint(1) default NULL,
988 `rentaldiscount` decimal(28,6) default NULL,
989 `reservecharge` decimal(28,6) default NULL,
990 `fine` decimal(28,6) default NULL,
991 `firstremind` int(11) default NULL,
992 `chargeperiod` int(11) default NULL,
993 `accountsent` int(11) default NULL,
994 `chargename` varchar(100) default NULL,
995 `maxissueqty` int(4) default NULL,
996 `issuelength` int(4) default NULL,
997 `branchcode` varchar(10) NOT NULL default '',
998 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
999 KEY `categorycode` (`categorycode`),
1000 KEY `itemtype` (`itemtype`)
1001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1004 -- Table structure for table `items`
1007 DROP TABLE IF EXISTS `items`;
1008 CREATE TABLE `items` (
1009 `itemnumber` int(11) NOT NULL auto_increment,
1010 `biblionumber` int(11) NOT NULL default 0,
1011 `biblioitemnumber` int(11) NOT NULL default 0,
1012 `barcode` varchar(20) default NULL,
1013 `dateaccessioned` date default NULL,
1014 `booksellerid` varchar(10) default NULL,
1015 `homebranch` varchar(10) default NULL,
1016 `price` decimal(8,2) default NULL,
1017 `replacementprice` decimal(8,2) default NULL,
1018 `replacementpricedate` date default NULL,
1019 `datelastborrowed` date default NULL,
1020 `datelastseen` date default NULL,
1021 `stack` tinyint(1) default NULL,
1022 `notforloan` tinyint(1) NOT NULL default 0,
1023 `damaged` tinyint(1) NOT NULL default 0,
1024 `itemlost` tinyint(1) NOT NULL default 0,
1025 `wthdrawn` tinyint(1) NOT NULL default 0,
1026 `itemcallnumber` varchar(30) default NULL,
1027 `issues` smallint(6) default NULL,
1028 `renewals` smallint(6) default NULL,
1029 `reserves` smallint(6) default NULL,
1030 `restricted` tinyint(1) default NULL,
1031 `itemnotes` mediumtext,
1032 `holdingbranch` varchar(10) default NULL,
1033 `paidfor` mediumtext,
1034 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1035 `location` varchar(80) default NULL,
1036 `onloan` date default NULL,
1037 `cn_source` varchar(10) default NULL,
1038 `cn_sort` varchar(30) default NULL,
1039 `ccode` varchar(10) default NULL,
1040 `materials` varchar(10) default NULL,
1041 `uri` varchar(255) default NULL,
1042 `itype` varchar(10) default NULL,
1043 PRIMARY KEY (`itemnumber`),
1044 UNIQUE KEY `itembarcodeidx` (`barcode`),
1045 KEY `itembinoidx` (`biblioitemnumber`),
1046 KEY `itembibnoidx` (`biblionumber`),
1047 KEY `homebranch` (`homebranch`),
1048 KEY `holdingbranch` (`holdingbranch`),
1049 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1050 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1051 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1052 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1055 -- Table structure for table `itemtypes`
1058 DROP TABLE IF EXISTS `itemtypes`;
1059 CREATE TABLE `itemtypes` (
1060 `itemtype` varchar(10) NOT NULL default '',
1061 `description` mediumtext,
1062 `renewalsallowed` smallint(6) default NULL,
1063 `rentalcharge` double(16,4) default NULL,
1064 `notforloan` smallint(6) default NULL,
1065 `imageurl` varchar(200) default NULL,
1067 PRIMARY KEY (`itemtype`),
1068 UNIQUE KEY `itemtype` (`itemtype`)
1069 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1072 -- Table structure for table `labels`
1075 DROP TABLE IF EXISTS `labels`;
1076 CREATE TABLE `labels` (
1077 `labelid` int(11) NOT NULL auto_increment,
1078 `batch_id` varchar(10) NOT NULL default 1,
1079 `itemnumber` varchar(100) NOT NULL default '',
1080 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1081 PRIMARY KEY (`labelid`)
1082 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1085 -- Table structure for table `labels_conf`
1088 DROP TABLE IF EXISTS `labels_conf`;
1089 CREATE TABLE `labels_conf` (
1090 `id` int(4) NOT NULL auto_increment,
1091 `barcodetype` char(100) default '',
1092 `title` int(1) default '0',
1093 `subtitle` int(1) default '0',
1094 `itemtype` int(1) default '0',
1095 `barcode` int(1) default '0',
1096 `dewey` int(1) default '0',
1097 `class` int(1) default NULL,
1098 `subclass` int(1) default '0',
1099 `itemcallnumber` int(1) default '0',
1100 `author` int(1) default '0',
1101 `issn` int(1) default '0',
1102 `isbn` int(1) default '0',
1103 `startlabel` int(2) NOT NULL default '1',
1104 `printingtype` char(32) default 'BAR',
1105 `layoutname` char(20) NOT NULL default 'TEST',
1106 `guidebox` int(1) default '0',
1107 `active` tinyint(1) default '1',
1108 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1109 `ccode` char(4) collate utf8_unicode_ci default NULL,
1110 `callnum_split` int(1) default NULL,
1111 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1116 -- Table structure for table `labels_templates`
1119 DROP TABLE IF EXISTS `labels_templates`;
1120 CREATE TABLE `labels_templates` (
1121 `tmpl_id` int(4) NOT NULL auto_increment,
1122 `tmpl_code` char(100) default '',
1123 `tmpl_desc` char(100) default '',
1124 `page_width` float default '0',
1125 `page_height` float default '0',
1126 `label_width` float default '0',
1127 `label_height` float default '0',
1128 `topmargin` float default '0',
1129 `leftmargin` float default '0',
1130 `cols` int(2) default '0',
1131 `rows` int(2) default '0',
1132 `colgap` float default '0',
1133 `rowgap` float default '0',
1134 `active` int(1) default NULL,
1135 `units` char(20) default 'PX',
1136 `fontsize` int(4) NOT NULL default '3',
1137 PRIMARY KEY (`tmpl_id`)
1138 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1141 -- Table structure for table `letter`
1144 DROP TABLE IF EXISTS `letter`;
1145 CREATE TABLE `letter` (
1146 `module` varchar(20) NOT NULL default '',
1147 `code` varchar(20) NOT NULL default '',
1148 `name` varchar(100) NOT NULL default '',
1149 `title` varchar(200) NOT NULL default '',
1151 PRIMARY KEY (`module`,`code`)
1152 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1155 -- Table structure for table `marc_subfield_structure`
1158 DROP TABLE IF EXISTS `marc_subfield_structure`;
1159 CREATE TABLE `marc_subfield_structure` (
1160 `tagfield` varchar(3) NOT NULL default '',
1161 `tagsubfield` varchar(1) NOT NULL default '',
1162 `liblibrarian` varchar(255) NOT NULL default '',
1163 `libopac` varchar(255) NOT NULL default '',
1164 `repeatable` tinyint(4) NOT NULL default 0,
1165 `mandatory` tinyint(4) NOT NULL default 0,
1166 `kohafield` varchar(40) default NULL,
1167 `tab` tinyint(1) default NULL,
1168 `authorised_value` varchar(20) default NULL,
1169 `authtypecode` varchar(20) default NULL,
1170 `value_builder` varchar(80) default NULL,
1171 `isurl` tinyint(1) default NULL,
1172 `hidden` tinyint(1) default NULL,
1173 `frameworkcode` varchar(4) NOT NULL default '',
1174 `seealso` varchar(1100) default NULL,
1175 `link` varchar(80) default NULL,
1176 `defaultvalue` text default NULL,
1177 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1178 KEY `kohafield_2` (`kohafield`),
1179 KEY `tab` (`frameworkcode`,`tab`),
1180 KEY `kohafield` (`frameworkcode`,`kohafield`)
1181 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1184 -- Table structure for table `marc_tag_structure`
1187 DROP TABLE IF EXISTS `marc_tag_structure`;
1188 CREATE TABLE `marc_tag_structure` (
1189 `tagfield` varchar(3) NOT NULL default '',
1190 `liblibrarian` varchar(255) NOT NULL default '',
1191 `libopac` varchar(255) NOT NULL default '',
1192 `repeatable` tinyint(4) NOT NULL default 0,
1193 `mandatory` tinyint(4) NOT NULL default 0,
1194 `authorised_value` varchar(10) default NULL,
1195 `frameworkcode` varchar(4) NOT NULL default '',
1196 PRIMARY KEY (`frameworkcode`,`tagfield`)
1197 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1200 -- Table structure for table `marc_matchers`
1203 DROP TABLE IF EXISTS `marc_matchers`;
1204 CREATE TABLE `marc_matchers` (
1205 `matcher_id` int(11) NOT NULL auto_increment,
1206 `code` varchar(10) NOT NULL default '',
1207 `description` varchar(255) NOT NULL default '',
1208 `record_type` varchar(10) NOT NULL default 'biblio',
1209 `threshold` int(11) NOT NULL default 0,
1210 PRIMARY KEY (`matcher_id`),
1211 KEY `code` (`code`),
1212 KEY `record_type` (`record_type`)
1213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1216 -- Table structure for table `matchpoints`
1218 DROP TABLE IF EXISTS `matchpoints`;
1219 CREATE TABLE `matchpoints` (
1220 `matcher_id` int(11) NOT NULL,
1221 `matchpoint_id` int(11) NOT NULL auto_increment,
1222 `search_index` varchar(30) NOT NULL default '',
1223 `score` int(11) NOT NULL default 0,
1224 PRIMARY KEY (`matchpoint_id`),
1225 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1226 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1227 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1231 -- Table structure for table `matchpoint_components`
1233 DROP TABLE IF EXISTS `matchpoint_components`;
1234 CREATE TABLE `matchpoint_components` (
1235 `matchpoint_id` int(11) NOT NULL,
1236 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1237 sequence int(11) NOT NULL default 0,
1238 tag varchar(3) NOT NULL default '',
1239 subfields varchar(40) NOT NULL default '',
1240 offset int(4) NOT NULL default 0,
1241 length int(4) NOT NULL default 0,
1242 PRIMARY KEY (`matchpoint_component_id`),
1243 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1244 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1245 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1246 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1249 -- Table structure for table `matcher_component_norms`
1251 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1252 CREATE TABLE `matchpoint_component_norms` (
1253 `matchpoint_component_id` int(11) NOT NULL,
1254 `sequence` int(11) NOT NULL default 0,
1255 `norm_routine` varchar(50) NOT NULL default '',
1256 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1257 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1258 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1262 -- Table structure for table `matcher_matchpoints`
1264 DROP TABLE IF EXISTS `matcher_matchpoints`;
1265 CREATE TABLE `matcher_matchpoints` (
1266 `matcher_id` int(11) NOT NULL,
1267 `matchpoint_id` int(11) NOT NULL,
1268 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1269 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1270 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1271 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1275 -- Table structure for table `matchchecks`
1277 DROP TABLE IF EXISTS `matchchecks`;
1278 CREATE TABLE `matchchecks` (
1279 `matcher_id` int(11) NOT NULL,
1280 `matchcheck_id` int(11) NOT NULL auto_increment,
1281 `source_matchpoint_id` int(11) NOT NULL,
1282 `target_matchpoint_id` int(11) NOT NULL,
1283 PRIMARY KEY (`matchcheck_id`),
1284 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1285 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1286 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1287 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1288 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1289 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1290 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1293 -- Table structure for table `mediatypetable`
1296 DROP TABLE IF EXISTS `mediatypetable`;
1297 CREATE TABLE `mediatypetable` (
1298 `mediatypecode` varchar(5) NOT NULL default '',
1300 `itemtypecodes` text,
1301 PRIMARY KEY (`mediatypecode`)
1302 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1305 -- Table structure for table `notifys`
1308 DROP TABLE IF EXISTS `notifys`;
1309 CREATE TABLE `notifys` (
1310 `notify_id` int(11) NOT NULL default 0,
1311 `borrowernumber` int(11) NOT NULL default 0,
1312 `itemnumber` int(11) NOT NULL default 0,
1313 `notify_date` date default NULL,
1314 `notify_send_date` date default NULL,
1315 `notify_level` int(1) NOT NULL default 0,
1316 `method` varchar(20) NOT NULL default ''
1317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1320 -- Table structure for table `nozebra`
1322 DROP TABLE IF EXISTS `nozebra`;
1323 CREATE TABLE `nozebra` (
1324 `server` varchar(20) NOT NULL,
1325 `indexname` varchar(40) NOT NULL,
1326 `value` varchar(250) NOT NULL,
1327 `biblionumbers` longtext NOT NULL,
1328 KEY `indexname` (`server`,`indexname`),
1329 KEY `value` (`server`,`value`))
1330 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1333 -- Table structure for table `opac_news`
1336 DROP TABLE IF EXISTS `opac_news`;
1337 CREATE TABLE `opac_news` (
1338 `idnew` int(10) unsigned NOT NULL auto_increment,
1339 `title` varchar(250) NOT NULL default '',
1340 `new` text NOT NULL,
1341 `lang` varchar(4) NOT NULL default '',
1342 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1343 `expirationdate` date default NULL,
1344 `number` int(11) default NULL,
1345 PRIMARY KEY (`idnew`)
1346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1349 -- Table structure for table `overduerules`
1352 DROP TABLE IF EXISTS `overduerules`;
1353 CREATE TABLE `overduerules` (
1354 `branchcode` varchar(10) NOT NULL default '',
1355 `categorycode` varchar(2) NOT NULL default '',
1356 `delay1` int(4) default 0,
1357 `letter1` varchar(20) default NULL,
1358 `debarred1` varchar(1) default 0,
1359 `delay2` int(4) default 0,
1360 `debarred2` varchar(1) default 0,
1361 `letter2` varchar(20) default NULL,
1362 `delay3` int(4) default 0,
1363 `letter3` varchar(20) default NULL,
1364 `debarred3` int(1) default 0,
1365 PRIMARY KEY (`branchcode`,`categorycode`)
1366 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1369 -- Table structure for table `printers`
1372 DROP TABLE IF EXISTS `printers`;
1373 CREATE TABLE `printers` (
1374 `printername` varchar(40) NOT NULL default '',
1375 `printqueue` varchar(20) default NULL,
1376 `printtype` varchar(20) default NULL,
1377 PRIMARY KEY (`printername`)
1378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1381 -- Table structure for table `repeatable_holidays`
1384 DROP TABLE IF EXISTS `repeatable_holidays`;
1385 CREATE TABLE `repeatable_holidays` (
1386 `id` int(11) NOT NULL auto_increment,
1387 `branchcode` varchar(10) NOT NULL default '',
1388 `weekday` smallint(6) default NULL,
1389 `day` smallint(6) default NULL,
1390 `month` smallint(6) default NULL,
1391 `title` varchar(50) NOT NULL default '',
1392 `description` text NOT NULL,
1394 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1397 -- Table structure for table `reports_dictionary`
1400 DROP TABLE IF EXISTS `reports_dictionary`;
1401 CREATE TABLE reports_dictionary (
1402 `id` int(11) NOT NULL auto_increment,
1403 `name` varchar(255) default NULL,
1405 `date_created` datetime default NULL,
1406 `date_modified` datetime default NULL,
1408 `area` int(11) default NULL,
1410 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1413 -- Table structure for table `reserveconstraints`
1416 DROP TABLE IF EXISTS `reserveconstraints`;
1417 CREATE TABLE `reserveconstraints` (
1418 `borrowernumber` int(11) NOT NULL default 0,
1419 `reservedate` date default NULL,
1420 `biblionumber` int(11) NOT NULL default 0,
1421 `biblioitemnumber` int(11) default NULL,
1422 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1423 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1426 -- Table structure for table `reserves`
1429 DROP TABLE IF EXISTS `reserves`;
1430 CREATE TABLE `reserves` (
1431 `borrowernumber` int(11) NOT NULL default 0,
1432 `reservedate` date default NULL,
1433 `biblionumber` int(11) NOT NULL default 0,
1434 `constrainttype` varchar(1) default NULL,
1435 `branchcode` varchar(10) default NULL,
1436 `notificationdate` date default NULL,
1437 `reminderdate` date default NULL,
1438 `cancellationdate` date default NULL,
1439 `reservenotes` mediumtext,
1440 `priority` smallint(6) default NULL,
1441 `found` varchar(1) default NULL,
1442 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1443 `itemnumber` int(11) default NULL,
1444 `waitingdate` date default NULL,
1445 KEY `borrowernumber` (`borrowernumber`),
1446 KEY `biblionumber` (`biblionumber`),
1447 KEY `itemnumber` (`itemnumber`),
1448 KEY `branchcode` (`branchcode`),
1449 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1450 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1451 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1452 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1453 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1456 -- Table structure for table `reviews`
1459 DROP TABLE IF EXISTS `reviews`;
1460 CREATE TABLE `reviews` (
1461 `reviewid` int(11) NOT NULL auto_increment,
1462 `borrowernumber` int(11) default NULL,
1463 `biblionumber` int(11) default NULL,
1465 `approved` tinyint(4) default NULL,
1466 `datereviewed` datetime default NULL,
1467 PRIMARY KEY (`reviewid`)
1468 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1471 -- Table structure for table `roadtype`
1474 DROP TABLE IF EXISTS `roadtype`;
1475 CREATE TABLE `roadtype` (
1476 `roadtypeid` int(11) NOT NULL auto_increment,
1477 `road_type` varchar(100) NOT NULL default '',
1478 PRIMARY KEY (`roadtypeid`)
1479 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1482 -- Table structure for table `saved_sql`
1485 DROP TABLE IF EXISTS `saved_sql`;
1486 CREATE TABLE saved_sql (
1487 `id` int(11) NOT NULL auto_increment,
1488 `borrowernumber` int(11) default NULL,
1489 `date_created` datetime default NULL,
1490 `last_modified` datetime default NULL,
1492 `last_run` datetime default NULL,
1493 `report_name` varchar(255) default NULL,
1494 `type` varchar(255) default NULL,
1497 KEY boridx (`borrowernumber`)
1498 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1502 -- Table structure for `saved_reports`
1505 DROP TABLE IF EXISTS `saved_reports`;
1506 CREATE TABLE saved_reports (
1507 `id` int(11) NOT NULL auto_increment,
1508 `report_id` int(11) default NULL,
1510 `date_run` datetime default NULL,
1512 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1516 -- Table structure for table `serial`
1519 DROP TABLE IF EXISTS `serial`;
1520 CREATE TABLE `serial` (
1521 `serialid` int(11) NOT NULL auto_increment,
1522 `biblionumber` varchar(100) NOT NULL default '',
1523 `subscriptionid` varchar(100) NOT NULL default '',
1524 `serialseq` varchar(100) NOT NULL default '',
1525 `status` tinyint(4) NOT NULL default 0,
1526 `planneddate` date default NULL,
1528 `publisheddate` date default NULL,
1530 `claimdate` date default NULL,
1531 `routingnotes` text,
1532 PRIMARY KEY (`serialid`)
1533 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1536 -- Table structure for table `sessions`
1539 DROP TABLE IF EXISTS sessions;
1540 CREATE TABLE sessions (
1541 `id` varchar(32) NOT NULL,
1542 `a_session` text NOT NULL,
1544 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1547 -- Table structure for table `special_holidays`
1550 DROP TABLE IF EXISTS `special_holidays`;
1551 CREATE TABLE `special_holidays` (
1552 `id` int(11) NOT NULL auto_increment,
1553 `branchcode` varchar(10) NOT NULL default '',
1554 `day` smallint(6) NOT NULL default 0,
1555 `month` smallint(6) NOT NULL default 0,
1556 `year` smallint(6) NOT NULL default 0,
1557 `isexception` smallint(1) NOT NULL default 1,
1558 `title` varchar(50) NOT NULL default '',
1559 `description` text NOT NULL,
1561 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1564 -- Table structure for table `statistics`
1567 DROP TABLE IF EXISTS `statistics`;
1568 CREATE TABLE `statistics` (
1569 `datetime` datetime default NULL,
1570 `branch` varchar(10) default NULL,
1571 `proccode` varchar(4) default NULL,
1572 `value` double(16,4) default NULL,
1573 `type` varchar(16) default NULL,
1575 `usercode` varchar(10) default NULL,
1576 `itemnumber` int(11) default NULL,
1577 `itemtype` varchar(10) default NULL,
1578 `borrowernumber` int(11) default NULL,
1579 `associatedborrower` int(11) default NULL,
1580 KEY `timeidx` (`datetime`)
1581 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1584 -- Table structure for table `stopwords`
1587 DROP TABLE IF EXISTS `stopwords`;
1588 CREATE TABLE `stopwords` (
1589 `word` varchar(255) default NULL
1590 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1593 -- Table structure for table `subcategorytable`
1596 DROP TABLE IF EXISTS `subcategorytable`;
1597 CREATE TABLE `subcategorytable` (
1598 `subcategorycode` varchar(5) NOT NULL default '',
1600 `itemtypecodes` text,
1601 PRIMARY KEY (`subcategorycode`)
1602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1605 -- Table structure for table `subscription`
1608 DROP TABLE IF EXISTS `subscription`;
1609 CREATE TABLE `subscription` (
1610 `biblionumber` int(11) NOT NULL default 0,
1611 `subscriptionid` int(11) NOT NULL auto_increment,
1612 `librarian` varchar(100) default '',
1613 `startdate` date default NULL,
1614 `aqbooksellerid` int(11) default 0,
1615 `cost` int(11) default 0,
1616 `aqbudgetid` int(11) default 0,
1617 `weeklength` tinyint(4) default 0,
1618 `monthlength` tinyint(4) default 0,
1619 `numberlength` tinyint(4) default 0,
1620 `periodicity` tinyint(4) default 0,
1621 `dow` varchar(100) default '',
1622 `numberingmethod` varchar(100) default '',
1624 `status` varchar(100) NOT NULL default '',
1625 `add1` int(11) default 0,
1626 `every1` int(11) default 0,
1627 `whenmorethan1` int(11) default 0,
1628 `setto1` int(11) default NULL,
1629 `lastvalue1` int(11) default NULL,
1630 `add2` int(11) default 0,
1631 `every2` int(11) default 0,
1632 `whenmorethan2` int(11) default 0,
1633 `setto2` int(11) default NULL,
1634 `lastvalue2` int(11) default NULL,
1635 `add3` int(11) default 0,
1636 `every3` int(11) default 0,
1637 `innerloop1` int(11) default 0,
1638 `innerloop2` int(11) default 0,
1639 `innerloop3` int(11) default 0,
1640 `whenmorethan3` int(11) default 0,
1641 `setto3` int(11) default NULL,
1642 `lastvalue3` int(11) default NULL,
1643 `issuesatonce` tinyint(3) NOT NULL default 1,
1644 `firstacquidate` date default NULL,
1645 `manualhistory` tinyint(1) NOT NULL default 0,
1646 `irregularity` text,
1647 `letter` varchar(20) default NULL,
1648 `numberpattern` tinyint(3) default 0,
1649 `distributedto` text,
1650 `internalnotes` longtext,
1652 `branchcode` varchar(10) NOT NULL default '',
1653 `hemisphere` tinyint(3) default 0,
1654 `lastbranch` varchar(10),
1655 PRIMARY KEY (`subscriptionid`)
1656 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1659 -- Table structure for table `subscriptionhistory`
1662 DROP TABLE IF EXISTS `subscriptionhistory`;
1663 CREATE TABLE `subscriptionhistory` (
1664 `biblionumber` int(11) NOT NULL default 0,
1665 `subscriptionid` int(11) NOT NULL default 0,
1666 `histstartdate` date default NULL,
1667 `enddate` date default NULL,
1668 `missinglist` longtext NOT NULL,
1669 `recievedlist` longtext NOT NULL,
1670 `opacnote` varchar(150) NOT NULL default '',
1671 `librariannote` varchar(150) NOT NULL default '',
1672 PRIMARY KEY (`subscriptionid`),
1673 KEY `biblionumber` (`biblionumber`)
1674 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1677 -- Table structure for table `subscriptionroutinglist`
1680 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1681 CREATE TABLE `subscriptionroutinglist` (
1682 `routingid` int(11) NOT NULL auto_increment,
1683 `borrowernumber` int(11) default NULL,
1684 `ranking` int(11) default NULL,
1685 `subscriptionid` int(11) default NULL,
1686 PRIMARY KEY (`routingid`)
1687 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1690 -- Table structure for table `suggestions`
1693 DROP TABLE IF EXISTS `suggestions`;
1694 CREATE TABLE `suggestions` (
1695 `suggestionid` int(8) NOT NULL auto_increment,
1696 `suggestedby` int(11) NOT NULL default 0,
1697 `managedby` int(11) default NULL,
1698 `STATUS` varchar(10) NOT NULL default '',
1700 `author` varchar(80) default NULL,
1701 `title` varchar(80) default NULL,
1702 `copyrightdate` smallint(6) default NULL,
1703 `publishercode` varchar(255) default NULL,
1704 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1705 `volumedesc` varchar(255) default NULL,
1706 `publicationyear` smallint(6) default 0,
1707 `place` varchar(255) default NULL,
1708 `isbn` varchar(10) default NULL,
1709 `mailoverseeing` smallint(1) default 0,
1710 `biblionumber` int(11) default NULL,
1712 PRIMARY KEY (`suggestionid`),
1713 KEY `suggestedby` (`suggestedby`),
1714 KEY `managedby` (`managedby`)
1715 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1718 -- Table structure for table `systempreferences`
1721 DROP TABLE IF EXISTS `systempreferences`;
1722 CREATE TABLE `systempreferences` (
1723 `variable` varchar(50) NOT NULL default '',
1725 `options` mediumtext,
1727 `type` varchar(20) default NULL,
1728 PRIMARY KEY (`variable`)
1729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1732 -- Table structure for table `tags`
1735 DROP TABLE IF EXISTS `tags`;
1736 CREATE TABLE `tags` (
1737 `entry` varchar(255) NOT NULL default '',
1738 `weight` bigint(20) NOT NULL default 0,
1739 PRIMARY KEY (`entry`)
1740 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1743 -- Table structure for table `userflags`
1746 DROP TABLE IF EXISTS `userflags`;
1747 CREATE TABLE `userflags` (
1748 `bit` int(11) NOT NULL default 0,
1749 `flag` varchar(30) default NULL,
1750 `flagdesc` varchar(255) default NULL,
1751 `defaulton` int(11) default NULL,
1753 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1756 -- Table structure for table `virtualshelves`
1759 DROP TABLE IF EXISTS `virtualshelves`;
1760 CREATE TABLE `virtualshelves` (
1761 `shelfnumber` int(11) NOT NULL auto_increment,
1762 `shelfname` varchar(255) default NULL,
1763 `owner` varchar(80) default NULL,
1764 `category` varchar(1) default NULL,
1765 `sortfield` varchar(16) default NULL,
1766 PRIMARY KEY (`shelfnumber`)
1767 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1770 -- Table structure for table `virtualshelfcontents`
1773 DROP TABLE IF EXISTS `virtualshelfcontents`;
1774 CREATE TABLE `virtualshelfcontents` (
1775 `shelfnumber` int(11) NOT NULL default 0,
1776 `biblionumber` int(11) NOT NULL default 0,
1777 `flags` int(11) default NULL,
1778 `dateadded` timestamp NULL default NULL,
1779 KEY `shelfnumber` (`shelfnumber`),
1780 KEY `biblionumber` (`biblionumber`),
1781 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1782 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1783 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1786 -- Table structure for table `z3950servers`
1789 DROP TABLE IF EXISTS `z3950servers`;
1790 CREATE TABLE `z3950servers` (
1791 `host` varchar(255) default NULL,
1792 `port` int(11) default NULL,
1793 `db` varchar(255) default NULL,
1794 `userid` varchar(255) default NULL,
1795 `password` varchar(255) default NULL,
1797 `id` int(11) NOT NULL auto_increment,
1798 `checked` smallint(6) default NULL,
1799 `rank` int(11) default NULL,
1800 `syntax` varchar(80) default NULL,
1802 `position` enum('primary','secondary','') NOT NULL default 'primary',
1803 `type` enum('zed','opensearch') NOT NULL default 'zed',
1804 `description` text NOT NULL,
1806 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1809 -- Table structure for table `zebraqueue`
1812 DROP TABLE IF EXISTS `zebraqueue`;
1813 CREATE TABLE `zebraqueue` (
1814 `id` int(11) NOT NULL auto_increment,
1815 `biblio_auth_number` int(11) NOT NULL default '0',
1816 `operation` char(20) NOT NULL default '',
1817 `server` char(20) NOT NULL default '',
1818 `done` int(11) NOT NULL default '0',
1819 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1821 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1823 DROP TABLE IF EXISTS `services_throttle`;
1824 CREATE TABLE `services_throttle` (
1825 `service_type` varchar(10) NOT NULL default '',
1826 `service_count` varchar(45) default NULL,
1827 PRIMARY KEY (`service_type`)
1828 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1830 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1831 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1832 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1833 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1834 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1835 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1836 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1837 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;