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 /*!40101 SET NAMES utf8 */;
11 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
12 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
13 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
14 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17 -- Table structure for table `accountlines`
20 DROP TABLE IF EXISTS `accountlines`;
21 CREATE TABLE `accountlines` (
22 `borrowernumber` int(11) NOT NULL default '0',
23 `accountno` smallint(6) NOT NULL default '0',
24 `itemnumber` int(11) default NULL,
25 `date` date default NULL,
26 `amount` decimal(28,6) default NULL,
27 `description` mediumtext,
29 `accounttype` varchar(5) default NULL,
30 `amountoutstanding` decimal(28,6) default NULL,
31 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
32 `notify_id` int(11) NOT NULL default '0',
33 `notify_level` int(2) NOT NULL default '0',
34 KEY `acctsborridx` (`borrowernumber`),
35 KEY `timeidx` (`timestamp`),
36 KEY `itemnumber` (`itemnumber`),
37 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
38 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
39 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
42 -- Table structure for table `accountoffsets`
45 DROP TABLE IF EXISTS `accountoffsets`;
46 CREATE TABLE `accountoffsets` (
47 `borrowernumber` int(11) NOT NULL default '0',
48 `accountno` smallint(6) NOT NULL default '0',
49 `offsetaccount` smallint(6) NOT NULL default '0',
50 `offsetamount` decimal(28,6) default NULL,
51 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
52 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
55 -- Table structure for table `action_logs`
58 DROP TABLE IF EXISTS `action_logs`;
59 CREATE TABLE `action_logs` (
60 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
61 `user` int(11) NOT NULL default '0',
64 `object` int(11) default NULL,
66 PRIMARY KEY (`timestamp`,`user`)
67 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
70 -- Table structure for table `alert`
73 DROP TABLE IF EXISTS `alert`;
74 CREATE TABLE `alert` (
75 `alertid` int(11) NOT NULL auto_increment,
76 `borrowernumber` int(11) NOT NULL default '0',
77 `type` varchar(10) NOT NULL default '',
78 `externalid` varchar(20) NOT NULL default '',
79 PRIMARY KEY (`alertid`),
80 KEY `borrowernumber` (`borrowernumber`),
81 KEY `type` (`type`,`externalid`)
82 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
85 -- Table structure for table `aqbasket`
88 DROP TABLE IF EXISTS `aqbasket`;
89 CREATE TABLE `aqbasket` (
90 `basketno` int(11) NOT NULL auto_increment,
91 `creationdate` date default NULL,
92 `closedate` date default NULL,
93 `booksellerid` int(11) NOT NULL default '1',
94 `authorisedby` varchar(10) default NULL,
95 `booksellerinvoicenumber` mediumtext,
96 PRIMARY KEY (`basketno`),
97 KEY `booksellerid` (`booksellerid`),
98 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
99 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
102 -- Table structure for table `aqbookfund`
105 DROP TABLE IF EXISTS `aqbookfund`;
106 CREATE TABLE `aqbookfund` (
107 `bookfundid` varchar(5) NOT NULL default '''''',
108 `bookfundname` mediumtext,
109 `bookfundgroup` varchar(5) default NULL,
110 `branchcode` varchar(4) NOT NULL default '',
111 PRIMARY KEY (`bookfundid`,`branchcode`)
112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
115 -- Table structure for table `aqbooksellers`
118 DROP TABLE IF EXISTS `aqbooksellers`;
119 CREATE TABLE `aqbooksellers` (
120 `id` int(11) NOT NULL auto_increment,
122 `address1` mediumtext,
123 `address2` mediumtext,
124 `address3` mediumtext,
125 `address4` mediumtext,
126 `phone` varchar(30) default NULL,
127 `accountnumber` mediumtext,
128 `othersupplier` mediumtext,
129 `currency` varchar(3) NOT NULL default '',
130 `deliverydays` smallint(6) default NULL,
131 `followupdays` smallint(6) default NULL,
132 `followupscancel` smallint(6) default NULL,
133 `specialty` mediumtext,
134 `booksellerfax` mediumtext,
136 `bookselleremail` mediumtext,
137 `booksellerurl` mediumtext,
138 `contact` varchar(100) default NULL,
140 `url` varchar(255) default NULL,
141 `contpos` varchar(100) default NULL,
142 `contphone` varchar(100) default NULL,
143 `contfax` varchar(100) default NULL,
144 `contaltphone` varchar(100) default NULL,
145 `contemail` varchar(100) default NULL,
146 `contnotes` mediumtext,
147 `active` tinyint(4) default NULL,
148 `listprice` varchar(10) default NULL,
149 `invoiceprice` varchar(10) default NULL,
150 `gstreg` tinyint(4) default NULL,
151 `listincgst` tinyint(4) default NULL,
152 `invoiceincgst` tinyint(4) default NULL,
153 `discount` float(6,4) default NULL,
154 `fax` varchar(50) default NULL,
155 `nocalc` int(11) default NULL,
156 `invoicedisc` float(6,4) default NULL,
158 KEY `listprice` (`listprice`),
159 KEY `invoiceprice` (`invoiceprice`),
160 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
161 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
162 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
165 -- Table structure for table `aqbudget`
168 DROP TABLE IF EXISTS `aqbudget`;
169 CREATE TABLE `aqbudget` (
170 `bookfundid` varchar(5) NOT NULL default '',
171 `startdate` date NOT NULL default '0000-00-00',
172 `enddate` date default NULL,
173 `budgetamount` decimal(13,2) default NULL,
174 `aqbudgetid` tinyint(4) NOT NULL auto_increment,
175 `branchcode` varchar(4) default NULL,
176 PRIMARY KEY (`aqbudgetid`)
177 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
180 -- Table structure for table `aqorderbreakdown`
183 DROP TABLE IF EXISTS `aqorderbreakdown`;
184 CREATE TABLE `aqorderbreakdown` (
185 `ordernumber` int(11) default NULL,
186 `bookfundid` char(5) NOT NULL default '',
187 KEY `ordernumber` (`ordernumber`),
188 KEY `bookfundid` (`bookfundid`),
189 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
190 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
194 -- Table structure for table `aqorderdelivery`
197 DROP TABLE IF EXISTS `aqorderdelivery`;
198 CREATE TABLE `aqorderdelivery` (
199 `ordernumber` date NOT NULL default '0000-00-00',
200 `deliverynumber` smallint(6) NOT NULL default '0',
201 `deliverydate` varchar(18) default NULL,
202 `qtydelivered` smallint(6) default NULL,
203 `deliverycomments` mediumtext
204 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
207 -- Table structure for table `aqorders`
210 DROP TABLE IF EXISTS `aqorders`;
211 CREATE TABLE `aqorders` (
212 `ordernumber` int(11) NOT NULL auto_increment,
213 `biblionumber` int(11) default NULL,
215 `entrydate` date default NULL,
216 `quantity` smallint(6) default NULL,
217 `currency` varchar(3) default NULL,
218 `listprice` decimal(28,6) default NULL,
219 `totalamount` decimal(28,6) default NULL,
220 `datereceived` date default NULL,
221 `booksellerinvoicenumber` mediumtext,
222 `freight` decimal(28,6) default NULL,
223 `unitprice` decimal(28,6) default NULL,
224 `quantityreceived` smallint(6) default NULL,
225 `cancelledby` varchar(10) default NULL,
226 `datecancellationprinted` date default NULL,
228 `supplierreference` mediumtext,
229 `purchaseordernumber` mediumtext,
230 `subscription` tinyint(1) default NULL,
231 `serialid` varchar(30) default NULL,
232 `basketno` int(11) default NULL,
233 `biblioitemnumber` int(11) default NULL,
234 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
235 `rrp` decimal(13,2) default NULL,
236 `ecost` decimal(13,2) default NULL,
237 `gst` decimal(13,2) default NULL,
238 `budgetdate` date default NULL,
239 `sort1` varchar(80) default NULL,
240 `sort2` varchar(80) default NULL,
241 PRIMARY KEY (`ordernumber`),
242 KEY `basketno` (`basketno`),
243 KEY `biblionumber` (`biblionumber`),
244 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
245 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
246 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
249 -- Table structure for table `auth_header`
252 DROP TABLE IF EXISTS `auth_header`;
253 CREATE TABLE `auth_header` (
254 `authid` bigint(20) unsigned NOT NULL auto_increment,
255 `authtypecode` varchar(10) NOT NULL default '',
256 `datecreated` date NOT NULL default '0000-00-00',
257 `datemodified` date default NULL,
258 `origincode` varchar(20) default NULL,
259 `authtrees` mediumtext,
261 `linkid` bigint(20) default NULL,
262 `marcxml` longtext NOT NULL,
263 PRIMARY KEY (`authid`),
264 KEY `origincode` (`origincode`)
265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
268 -- Table structure for table `auth_subfield_structure`
271 DROP TABLE IF EXISTS `auth_subfield_structure`;
272 CREATE TABLE `auth_subfield_structure` (
273 `authtypecode` varchar(10) NOT NULL default '',
274 `tagfield` varchar(3) NOT NULL default '',
275 `tagsubfield` char(1) NOT NULL default '',
276 `liblibrarian` varchar(255) NOT NULL default '',
277 `libopac` varchar(255) NOT NULL default '',
278 `repeatable` tinyint(4) NOT NULL default '0',
279 `mandatory` tinyint(4) NOT NULL default '0',
280 `tab` tinyint(1) default NULL,
281 `authorised_value` varchar(10) default NULL,
282 `value_builder` varchar(80) default NULL,
283 `seealso` varchar(255) default NULL,
284 `isurl` tinyint(1) default NULL,
285 `hidden` tinyint(3) NOT NULL default '0',
286 `linkid` tinyint(1) NOT NULL default '0',
287 `kohafield` varchar(45) NOT NULL default '',
288 `frameworkcode` varchar(8) NOT NULL default '',
289 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
290 KEY `tab` (`authtypecode`,`tab`)
291 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
294 -- Table structure for table `auth_tag_structure`
297 DROP TABLE IF EXISTS `auth_tag_structure`;
298 CREATE TABLE `auth_tag_structure` (
299 `authtypecode` char(10) NOT NULL default '',
300 `tagfield` char(3) NOT NULL default '',
301 `liblibrarian` char(255) NOT NULL default '',
302 `libopac` char(255) NOT NULL default '',
303 `repeatable` tinyint(4) NOT NULL default '0',
304 `mandatory` tinyint(4) NOT NULL default '0',
305 `authorised_value` char(10) default NULL,
306 PRIMARY KEY (`authtypecode`,`tagfield`),
307 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
308 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
311 -- Table structure for table `auth_types`
314 DROP TABLE IF EXISTS `auth_types`;
315 CREATE TABLE `auth_types` (
316 `authtypecode` varchar(10) NOT NULL default '',
317 `authtypetext` varchar(255) NOT NULL default '',
318 `auth_tag_to_report` varchar(3) NOT NULL default '',
319 `summary` mediumtext NOT NULL,
320 PRIMARY KEY (`authtypecode`)
321 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
324 -- Table structure for table `authorised_values`
327 DROP TABLE IF EXISTS `authorised_values`;
328 CREATE TABLE `authorised_values` (
329 `id` int(11) NOT NULL auto_increment,
330 `category` char(10) NOT NULL default '',
331 `authorised_value` char(80) NOT NULL default '',
332 `lib` char(80) default NULL,
334 KEY `name` (`category`)
335 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
338 -- Table structure for table `biblio`
341 DROP TABLE IF EXISTS `biblio`;
342 CREATE TABLE `biblio` (
343 `biblionumber` int(11) NOT NULL default '0',
344 `frameworkcode` varchar(4) NOT NULL default '',
347 `unititle` mediumtext,
349 `serial` tinyint(1) default NULL,
350 `seriestitle` mediumtext,
351 `copyrightdate` smallint(6) default NULL,
352 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
353 `abstract` mediumtext,
354 PRIMARY KEY (`biblionumber`),
355 KEY `blbnoidx` (`biblionumber`)
356 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
359 -- Table structure for table `biblio_framework`
362 DROP TABLE IF EXISTS `biblio_framework`;
363 CREATE TABLE `biblio_framework` (
364 `frameworkcode` char(4) NOT NULL default '',
365 `frameworktext` char(255) NOT NULL default '',
366 PRIMARY KEY (`frameworkcode`)
367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
370 -- Table structure for table `biblioitems`
373 DROP TABLE IF EXISTS `biblioitems`;
374 CREATE TABLE `biblioitems` (
375 `biblioitemnumber` int(11) NOT NULL default '0',
376 `biblionumber` int(11) NOT NULL default '0',
379 `classification` varchar(25) default NULL,
380 `itemtype` varchar(4) default NULL,
381 `isbn` varchar(14) default NULL,
382 `issn` varchar(9) default NULL,
383 `dewey` varchar(30) default '',
384 `subclass` varchar(3) default NULL,
385 `publicationyear` text,
386 `publishercode` varchar(255) default NULL,
387 `volumedate` date default NULL,
389 `collectiontitle` mediumtext NOT NULL,
390 `collectionissn` text NOT NULL,
391 `collectionvolume` mediumtext NOT NULL,
392 `editionstatement` text NOT NULL,
393 `editionreponsability` text NOT NULL,
394 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
395 `illus` varchar(255) default NULL,
396 `pages` varchar(255) default NULL,
398 `size` varchar(255) default NULL,
399 `place` varchar(255) default NULL,
400 `lccn` varchar(25) default NULL,
402 `url` varchar(255) default NULL,
403 `lcsort` varchar(25) default NULL,
404 `ccode` varchar(4) default NULL,
405 `marcxml` longtext NOT NULL,
406 PRIMARY KEY (`biblioitemnumber`),
407 KEY `bibinoidx` (`biblioitemnumber`),
408 KEY `bibnoidx` (`biblionumber`),
410 KEY `publishercode` (`publishercode`),
411 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
412 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
415 -- Table structure for table `borrowers`
418 DROP TABLE IF EXISTS `borrowers`;
419 CREATE TABLE `borrowers` (
420 `borrowernumber` int(11) NOT NULL auto_increment,
421 `cardnumber` varchar(16) default NULL,
422 `surname` mediumtext NOT NULL,
425 `othernames` mediumtext,
427 `streetnumber` varchar(10) default NULL,
428 `streettype` varchar(50) default NULL,
429 `address` mediumtext NOT NULL,
431 `city` mediumtext NOT NULL,
432 `zipcode` varchar(25) default NULL,
435 `mobile` varchar(50) default NULL,
439 `B_streetnumber` varchar(10) default NULL,
440 `B_streettype` varchar(50) default NULL,
441 `B_address` varchar(100) default NULL,
443 `B_zipcode` varchar(25) default NULL,
445 `B_phone` mediumtext,
446 `dateofbirth` date default NULL,
447 `branchcode` varchar(10) NOT NULL default '',
448 `categorycode` varchar(10) NOT NULL default '',
449 `dateenrolled` date default NULL,
450 `dateexpiry` date default NULL,
451 `gonenoaddress` tinyint(1) default NULL,
452 `lost` tinyint(1) default NULL,
453 `debarred` tinyint(1) default NULL,
454 `contactname` mediumtext,
455 `contactfirstname` text,
457 `guarantorid` int(11) default NULL,
458 `borrowernotes` mediumtext,
459 `relationship` varchar(100) default NULL,
460 `ethnicity` varchar(50) default NULL,
461 `ethnotes` varchar(255) default NULL,
462 `sex` char(1) default NULL,
463 `password` varchar(30) default NULL,
464 `flags` int(11) default NULL,
465 `userid` varchar(30) default NULL,
466 `opacnote` mediumtext,
467 `contactnote` varchar(255) default NULL,
468 `sort1` varchar(80) default NULL,
469 `sort2` varchar(80) default NULL,
470 UNIQUE KEY `cardnumber` (`cardnumber`),
471 KEY `borrowernumber` (`borrowernumber`),
472 KEY `categorycode` (`categorycode`),
473 KEY `branchcode` (`branchcode`),
474 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
475 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
476 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
479 -- Table structure for table `branchcategories`
482 DROP TABLE IF EXISTS `branchcategories`;
483 CREATE TABLE `branchcategories` (
484 `categorycode` varchar(4) NOT NULL default '',
485 `categoryname` mediumtext,
486 `codedescription` mediumtext,
487 PRIMARY KEY (`categorycode`)
488 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
491 -- Table structure for table `branches`
494 DROP TABLE IF EXISTS `branches`;
495 CREATE TABLE `branches` (
496 `branchcode` varchar(10) NOT NULL default '',
497 `branchname` mediumtext NOT NULL,
498 `branchaddress1` mediumtext,
499 `branchaddress2` mediumtext,
500 `branchaddress3` mediumtext,
501 `branchphone` mediumtext,
502 `branchfax` mediumtext,
503 `branchemail` mediumtext,
504 `issuing` tinyint(4) default NULL,
505 `branchip` varchar(15) default NULL,
506 `branchprinter` varchar(100) default NULL,
507 UNIQUE KEY `branchcode` (`branchcode`)
508 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
511 -- Table structure for table `branchrelations`
514 DROP TABLE IF EXISTS `branchrelations`;
515 CREATE TABLE `branchrelations` (
516 `branchcode` varchar(4) NOT NULL default '',
517 `categorycode` varchar(4) NOT NULL default '',
518 PRIMARY KEY (`branchcode`,`categorycode`),
519 KEY `branchcode` (`branchcode`),
520 KEY `categorycode` (`categorycode`),
521 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
522 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
523 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
526 -- Table structure for table `branchtransfers`
529 DROP TABLE IF EXISTS `branchtransfers`;
530 CREATE TABLE `branchtransfers` (
531 `itemnumber` int(11) NOT NULL default '0',
532 `datesent` datetime default NULL,
533 `frombranch` varchar(10) NOT NULL default '',
534 `datearrived` datetime default NULL,
535 `tobranch` varchar(10) NOT NULL default '',
536 `comments` mediumtext,
537 KEY `frombranch` (`frombranch`),
538 KEY `tobranch` (`tobranch`),
539 KEY `itemnumber` (`itemnumber`),
540 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
541 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
542 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
546 -- Table structure for table `categories`
549 DROP TABLE IF EXISTS `categories`;
550 CREATE TABLE `categories` (
551 `categorycode` varchar(10) NOT NULL default '',
552 `description` mediumtext,
553 `enrolmentperiod` smallint(6) default NULL,
554 `upperagelimit` smallint(6) default NULL,
555 `dateofbirthrequired` tinyint(1) default NULL,
556 `finetype` varchar(30) default NULL,
557 `bulk` tinyint(1) default NULL,
558 `enrolmentfee` decimal(28,6) default NULL,
559 `overduenoticerequired` tinyint(1) default NULL,
560 `issuelimit` smallint(6) default NULL,
561 `reservefee` decimal(28,6) default NULL,
562 `category_type` char(1) NOT NULL default 'A',
563 PRIMARY KEY (`categorycode`),
564 UNIQUE KEY `categorycode` (`categorycode`)
565 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
568 -- Table structure for table `categorytable`
571 DROP TABLE IF EXISTS `categorytable`;
572 CREATE TABLE `categorytable` (
573 `categorycode` varchar(5) NOT NULL default '',
575 `itemtypecodes` text,
576 PRIMARY KEY (`categorycode`)
577 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
580 -- Table structure for table `cities`
583 DROP TABLE IF EXISTS `cities`;
584 CREATE TABLE `cities` (
585 `cityid` int(11) NOT NULL auto_increment,
586 `city_name` char(100) NOT NULL default '',
587 `city_zipcode` char(20) default NULL,
588 PRIMARY KEY (`cityid`)
589 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
592 -- Table structure for table `currency`
595 DROP TABLE IF EXISTS `currency`;
596 CREATE TABLE `currency` (
597 `currency` varchar(10) NOT NULL default '',
598 `rate` float(7,5) default NULL,
599 PRIMARY KEY (`currency`)
600 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
603 -- Table structure for table `deletedbiblio`
606 DROP TABLE IF EXISTS `deletedbiblio`;
607 CREATE TABLE `deletedbiblio` (
608 `biblionumber` int(11) NOT NULL default '0',
611 `unititle` mediumtext,
613 `serial` tinyint(1) default NULL,
614 `seriestitle` mediumtext,
615 `copyrightdate` smallint(6) default NULL,
616 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
618 `abstract` mediumtext,
619 PRIMARY KEY (`biblionumber`),
620 KEY `blbnoidx` (`biblionumber`)
621 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
624 -- Table structure for table `deletedbiblioitems`
627 DROP TABLE IF EXISTS `deletedbiblioitems`;
628 CREATE TABLE `deletedbiblioitems` (
629 `biblioitemnumber` int(11) NOT NULL default '0',
630 `biblionumber` int(11) NOT NULL default '0',
633 `classification` varchar(25) default NULL,
634 `itemtype` varchar(4) default NULL,
635 `isbn` varchar(14) default NULL,
636 `issn` varchar(9) default NULL,
637 `dewey` double(8,6) default NULL,
638 `subclass` varchar(3) default NULL,
639 `publicationyear` smallint(6) default NULL,
640 `publishercode` varchar(255) default NULL,
641 `volumedate` date default NULL,
642 `volumeddesc` varchar(255) default NULL,
643 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
644 `illus` varchar(255) default NULL,
645 `pages` varchar(255) default NULL,
647 `size` varchar(255) default NULL,
648 `lccn` varchar(25) default NULL,
650 `url` varchar(255) default NULL,
651 `place` varchar(255) default NULL,
652 `lcsort` varchar(25) default NULL,
653 `ccode` varchar(4) default NULL,
654 PRIMARY KEY (`biblioitemnumber`),
655 KEY `bibinoidx` (`biblioitemnumber`),
656 KEY `bibnoidx` (`biblionumber`)
657 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
660 -- Table structure for table `deletedborrowers`
663 DROP TABLE IF EXISTS `deletedborrowers`;
664 CREATE TABLE `deletedborrowers` (
665 `borrowernumber` int(11) NOT NULL default '0',
666 `cardnumber` varchar(9) NOT NULL default '',
667 `surname` mediumtext NOT NULL,
670 `othernames` mediumtext,
672 `streetnumber` varchar(10) default NULL,
673 `streettype` varchar(50) default NULL,
674 `address` mediumtext NOT NULL,
676 `city` mediumtext NOT NULL,
677 `zipcode` varchar(25) default NULL,
680 `mobile` varchar(50) default NULL,
684 `B_streetnumber` varchar(10) default NULL,
685 `B_streettype` varchar(50) default NULL,
686 `B_address` varchar(100) default NULL,
688 `B_zipcode` varchar(25) default NULL,
690 `B_phone` mediumtext,
691 `dateofbirth` date default NULL,
692 `branchcode` varchar(4) NOT NULL default '',
693 `categorycode` varchar(2) default NULL,
694 `dateenrolled` date default NULL,
695 `dateexpiry` date default NULL,
696 `gonenoaddress` tinyint(1) default NULL,
697 `lost` tinyint(1) default NULL,
698 `debarred` tinyint(1) default NULL,
699 `contactname` mediumtext,
700 `contactfirstname` text,
702 `guarantorid` int(11) default NULL,
703 `borrowernotes` mediumtext,
704 `relationship` varchar(100) default NULL,
705 `ethnicity` varchar(50) default NULL,
706 `ethnotes` varchar(255) default NULL,
707 `sex` char(1) default NULL,
708 `password` varchar(30) default NULL,
709 `flags` int(11) default NULL,
710 `userid` varchar(30) default NULL,
711 `opacnote` mediumtext,
712 `contactnote` varchar(255) default NULL,
713 `sort1` varchar(80) default NULL,
714 `sort2` varchar(80) default NULL,
715 KEY `borrowernumber` (`borrowernumber`),
716 KEY `cardnumber` (`cardnumber`)
717 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
720 -- Table structure for table `deleteditems`
723 DROP TABLE IF EXISTS `deleteditems`;
724 CREATE TABLE `deleteditems` (
725 `itemnumber` int(11) NOT NULL default '0',
726 `biblionumber` int(11) NOT NULL default '0',
727 `multivolumepart` varchar(30) default NULL,
728 `biblioitemnumber` int(11) NOT NULL default '0',
729 `barcode` varchar(9) NOT NULL default '',
730 `dateaccessioned` date default NULL,
731 `booksellerid` varchar(10) default NULL,
732 `homebranch` varchar(4) default NULL,
733 `price` decimal(28,6) default NULL,
734 `replacementprice` decimal(28,6) default NULL,
735 `replacementpricedate` date default NULL,
736 `datelastborrowed` date default NULL,
737 `datelastseen` date default NULL,
738 `multivolume` tinyint(1) default NULL,
739 `stack` tinyint(1) default NULL,
740 `notforloan` tinyint(1) default NULL,
741 `itemlost` tinyint(1) default NULL,
742 `wthdrawn` tinyint(1) default NULL,
743 `bulk` varchar(30) default NULL,
744 `issues` smallint(6) default NULL,
745 `renewals` smallint(6) default NULL,
746 `reserves` smallint(6) default NULL,
747 `restricted` tinyint(1) default NULL,
748 `binding` decimal(28,6) default NULL,
749 `itemnotes` mediumtext,
750 `holdingbranch` varchar(4) default NULL,
751 `interim` tinyint(1) default NULL,
752 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
754 `paidfor` mediumtext,
755 `location` varchar(80) default NULL,
756 `itemcallnumber` varchar(30) default NULL,
757 `onloan` date default '0000-00-00',
758 `cutterextra` varchar(45) default NULL,
759 `issue_date` date default NULL,
760 `itype` varchar(10) default NULL,
761 PRIMARY KEY (`itemnumber`),
762 UNIQUE KEY `barcode` (`barcode`),
763 KEY `itembarcodeidx` (`barcode`),
764 KEY `itembinoidx` (`biblioitemnumber`),
765 KEY `itembibnoidx` (`biblionumber`)
766 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
769 -- Table structure for table `ethnicity`
772 DROP TABLE IF EXISTS `ethnicity`;
773 CREATE TABLE `ethnicity` (
774 `code` varchar(10) NOT NULL default '',
775 `name` varchar(255) default NULL,
777 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
780 -- Table structure for table `issues`
783 DROP TABLE IF EXISTS `issues`;
784 CREATE TABLE `issues` (
785 `borrowernumber` int(11) default NULL,
786 `itemnumber` int(11) default NULL,
787 `date_due` date default NULL,
788 `branchcode` varchar(10) default NULL,
789 `issuingbranch` varchar(18) default NULL,
790 `returndate` date default NULL,
791 `lastreneweddate` date default NULL,
792 `return` varchar(4) default NULL,
793 `renewals` tinyint(4) default NULL,
794 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
795 `issuedate` date NOT NULL default '0000-00-00',
796 KEY `issuesborridx` (`borrowernumber`),
797 KEY `issuesitemidx` (`itemnumber`),
798 KEY `bordate` (`borrowernumber`,`timestamp`),
799 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
800 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
801 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
804 -- Table structure for table `issuingrules`
807 DROP TABLE IF EXISTS `issuingrules`;
808 CREATE TABLE `issuingrules` (
809 `categorycode` varchar(2) NOT NULL default '',
810 `itemtype` varchar(4) NOT NULL default '',
811 `restrictedtype` tinyint(1) default NULL,
812 `rentaldiscount` decimal(28,6) default NULL,
813 `reservecharge` decimal(28,6) default NULL,
814 `fine` decimal(28,6) default NULL,
815 `firstremind` int(11) default NULL,
816 `chargeperiod` int(11) default NULL,
817 `accountsent` int(11) default NULL,
818 `chargename` varchar(100) default NULL,
819 `maxissueqty` int(4) default NULL,
820 `issuelength` int(4) default NULL,
821 `branchcode` varchar(4) NOT NULL default '',
822 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
823 KEY `categorycode` (`categorycode`),
824 KEY `itemtype` (`itemtype`),
825 CONSTRAINT `issuingrules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE,
826 CONSTRAINT `issuingrules_ibfk_2` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
827 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
830 -- Table structure for table `items`
833 DROP TABLE IF EXISTS `items`;
834 CREATE TABLE `items` (
835 `itemnumber` int(11) NOT NULL default '0',
836 `biblionumber` int(11) NOT NULL default '0',
837 `multivolumepart` varchar(30) default NULL,
838 `biblioitemnumber` int(11) NOT NULL default '0',
839 `barcode` varchar(20) default NULL,
840 `dateaccessioned` date default NULL,
841 `booksellerid` varchar(10) default NULL,
842 `homebranch` varchar(4) default NULL,
843 `price` decimal(8,2) default NULL,
844 `replacementprice` decimal(8,2) default NULL,
845 `replacementpricedate` date default NULL,
846 `datelastborrowed` date default NULL,
847 `datelastseen` date default NULL,
848 `multivolume` tinyint(1) default NULL,
849 `stack` tinyint(1) default NULL,
850 `notforloan` tinyint(1) default NULL,
851 `itemlost` tinyint(1) default NULL,
852 `wthdrawn` tinyint(1) default NULL,
853 `itemcallnumber` varchar(30) default NULL,
854 `issues` smallint(6) default NULL,
855 `renewals` smallint(6) default NULL,
856 `reserves` smallint(6) default NULL,
857 `restricted` tinyint(1) default NULL,
858 `binding` decimal(28,6) default NULL,
859 `itemnotes` mediumtext,
860 `holdingbranch` varchar(10) default NULL,
861 `paidfor` mediumtext,
862 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
863 `location` varchar(80) default NULL,
864 `onloan` date default '0000-00-00',
865 `cutterextra` varchar(45) default NULL,
866 `issue_date` date default NULL,
867 `itype` varchar(10) default NULL,
868 PRIMARY KEY (`itemnumber`),
869 KEY `itembarcodeidx` (`barcode`),
870 KEY `itembinoidx` (`biblioitemnumber`),
871 KEY `itembibnoidx` (`biblionumber`),
872 KEY `homebranch` (`homebranch`),
873 KEY `holdingbranch` (`holdingbranch`),
874 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
875 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
876 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
877 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
880 -- Table structure for table `itemtypes`
883 DROP TABLE IF EXISTS `itemtypes`;
884 CREATE TABLE `itemtypes` (
885 `itemtype` varchar(10) NOT NULL default '',
886 `description` mediumtext,
887 `renewalsallowed` smallint(6) default NULL,
888 `rentalcharge` double(16,4) default NULL,
889 `notforloan` smallint(6) default NULL,
890 `imageurl` varchar(200) default NULL,
892 PRIMARY KEY (`itemtype`),
893 UNIQUE KEY `itemtype` (`itemtype`)
894 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
897 -- Table structure for table `labels`
900 DROP TABLE IF EXISTS `labels`;
901 CREATE TABLE `labels` (
902 `labelid` int(11) NOT NULL auto_increment,
903 `itemnumber` varchar(100) NOT NULL default '',
904 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
905 PRIMARY KEY (`labelid`)
906 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
909 -- Table structure for table `labels_conf`
912 DROP TABLE IF EXISTS `labels_conf`;
913 CREATE TABLE `labels_conf` (
914 `id` int(4) NOT NULL auto_increment,
915 `barcodetype` char(100) default '',
916 `title` tinyint(1) default '0',
917 `isbn` tinyint(1) default '0',
918 `itemtype` tinyint(1) default '0',
919 `barcode` tinyint(1) default '0',
920 `dewey` tinyint(1) default '0',
921 `class` tinyint(1) default '0',
922 `author` tinyint(1) default '0',
923 `papertype` char(100) default '',
924 `startrow` int(2) default NULL,
926 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
929 -- Table structure for table `letter`
932 DROP TABLE IF EXISTS `letter`;
933 CREATE TABLE `letter` (
934 `module` varchar(20) NOT NULL default '',
935 `code` varchar(20) NOT NULL default '',
936 `name` varchar(100) NOT NULL default '',
937 `title` varchar(200) NOT NULL default '',
939 PRIMARY KEY (`module`,`code`)
940 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
944 -- Table structure for table `marc_breeding`
947 DROP TABLE IF EXISTS `marc_breeding`;
948 CREATE TABLE `marc_breeding` (
949 `id` bigint(20) NOT NULL auto_increment,
950 `file` varchar(80) NOT NULL default '',
951 `isbn` varchar(10) NOT NULL default '',
952 `title` varchar(128) default NULL,
953 `author` varchar(80) default NULL,
955 `encoding` varchar(40) NOT NULL default '',
956 `z3950random` varchar(40) default NULL,
958 KEY `title` (`title`),
960 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
963 -- Table structure for table `marc_subfield_structure`
966 DROP TABLE IF EXISTS `marc_subfield_structure`;
967 CREATE TABLE `marc_subfield_structure` (
968 `tagfield` varchar(3) NOT NULL default '',
969 `tagsubfield` char(1) NOT NULL default '',
970 `liblibrarian` varchar(255) NOT NULL default '',
971 `libopac` varchar(255) NOT NULL default '',
972 `repeatable` tinyint(4) NOT NULL default '0',
973 `mandatory` tinyint(4) NOT NULL default '0',
974 `kohafield` varchar(40) default NULL,
975 `tab` tinyint(1) default NULL,
976 `authorised_value` varchar(10) default NULL,
977 `authtypecode` varchar(10) default NULL,
978 `value_builder` varchar(80) default NULL,
979 `isurl` tinyint(1) default NULL,
980 `hidden` tinyint(1) default NULL,
981 `frameworkcode` varchar(4) NOT NULL default '',
982 `seealso` varchar(255) default NULL,
983 `link` varchar(80) default NULL,
984 `defaultvalue` text default NULL,
985 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
986 KEY `kohafield_2` (`kohafield`),
987 KEY `tab` (`frameworkcode`,`tab`),
988 KEY `kohafield` (`frameworkcode`,`kohafield`)
989 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
992 -- Table structure for table `marc_tag_structure`
995 DROP TABLE IF EXISTS `marc_tag_structure`;
996 CREATE TABLE `marc_tag_structure` (
997 `tagfield` char(3) NOT NULL default '',
998 `liblibrarian` char(255) NOT NULL default '',
999 `libopac` char(255) NOT NULL default '',
1000 `repeatable` tinyint(4) NOT NULL default '0',
1001 `mandatory` tinyint(4) NOT NULL default '0',
1002 `authorised_value` char(10) default NULL,
1003 `frameworkcode` char(4) NOT NULL default '',
1004 PRIMARY KEY (`frameworkcode`,`tagfield`)
1005 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1008 -- Table structure for table `mediatypetable`
1011 DROP TABLE IF EXISTS `mediatypetable`;
1012 CREATE TABLE `mediatypetable` (
1013 `mediatypecode` varchar(5) NOT NULL default '',
1015 `itemtypecodes` text,
1016 PRIMARY KEY (`mediatypecode`)
1017 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1020 -- Table structure for table `notifys`
1023 DROP TABLE IF EXISTS `notifys`;
1024 CREATE TABLE `notifys` (
1025 `notify_id` int(11) NOT NULL default '0',
1026 `borrowernumber` int(11) NOT NULL default '0',
1027 `itemnumber` int(11) NOT NULL default '0',
1028 `notify_date` date NOT NULL default '0000-00-00',
1029 `notify_send_date` date default NULL,
1030 `notify_level` int(1) NOT NULL default '0',
1031 `method` varchar(20) NOT NULL default ''
1032 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1035 -- Table structure for table `opac_news`
1038 DROP TABLE IF EXISTS `opac_news`;
1039 CREATE TABLE `opac_news` (
1040 `idnew` int(10) unsigned NOT NULL auto_increment,
1041 `title` varchar(250) NOT NULL default '',
1042 `new` text NOT NULL,
1043 `lang` varchar(4) NOT NULL default '',
1044 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1045 `expirationdate` date default NULL,
1046 `number` int(11) default NULL,
1047 PRIMARY KEY (`idnew`)
1048 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1051 -- Table structure for table `overduerules`
1054 DROP TABLE IF EXISTS `overduerules`;
1055 CREATE TABLE `overduerules` (
1056 `branchcode` varchar(255) NOT NULL default '',
1057 `categorycode` varchar(2) NOT NULL default '',
1058 `delay1` int(4) default '0',
1059 `letter1` varchar(20) default NULL,
1060 `debarred1` char(1) default '0',
1061 `delay2` int(4) default '0',
1062 `debarred2` char(1) default '0',
1063 `letter2` varchar(20) default NULL,
1064 `delay3` int(4) default '0',
1065 `letter3` varchar(20) default NULL,
1066 `debarred3` int(1) default '0',
1067 PRIMARY KEY (`branchcode`,`categorycode`)
1068 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1071 -- Table structure for table `printers`
1074 DROP TABLE IF EXISTS `printers`;
1075 CREATE TABLE `printers` (
1076 `printername` char(40) NOT NULL default '''''',
1077 `printqueue` char(20) default NULL,
1078 `printtype` char(20) default NULL,
1079 PRIMARY KEY (`printername`)
1080 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1083 -- Table structure for table `repeatable_holidays`
1086 DROP TABLE IF EXISTS `repeatable_holidays`;
1087 CREATE TABLE `repeatable_holidays` (
1088 `id` int(11) NOT NULL auto_increment,
1089 `branchcode` varchar(4) NOT NULL default '',
1090 `weekday` smallint(6) default NULL,
1091 `day` smallint(6) default NULL,
1092 `month` smallint(6) default NULL,
1093 `title` varchar(50) NOT NULL default '',
1094 `description` text NOT NULL,
1096 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1099 -- Table structure for table `reserveconstraints`
1102 DROP TABLE IF EXISTS `reserveconstraints`;
1103 CREATE TABLE `reserveconstraints` (
1104 `borrowernumber` int(11) NOT NULL default '0',
1105 `reservedate` date NOT NULL default '0000-00-00',
1106 `biblionumber` int(11) NOT NULL default '0',
1107 `biblioitemnumber` int(11) default NULL,
1108 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1109 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1112 -- Table structure for table `reserves`
1115 DROP TABLE IF EXISTS `reserves`;
1116 CREATE TABLE `reserves` (
1117 `borrowernumber` int(11) NOT NULL default '0',
1118 `reservedate` date NOT NULL default '0000-00-00',
1119 `biblionumber` int(11) NOT NULL default '0',
1120 `constrainttype` char(1) default NULL,
1121 `branchcode` varchar(4) default NULL,
1122 `notificationdate` date default NULL,
1123 `reminderdate` date default NULL,
1124 `cancellationdate` date default NULL,
1125 `reservenotes` mediumtext,
1126 `priority` smallint(6) default NULL,
1127 `found` char(1) default NULL,
1128 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1129 `itemnumber` int(11) default NULL,
1130 `waitingdate` date default NULL,
1131 KEY `borrowernumber` (`borrowernumber`),
1132 KEY `biblionumber` (`biblionumber`),
1133 KEY `itemnumber` (`itemnumber`),
1134 KEY `branchcode` (`branchcode`),
1135 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1136 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1137 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1138 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1139 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1142 -- Table structure for table `reviews`
1145 DROP TABLE IF EXISTS `reviews`;
1146 CREATE TABLE `reviews` (
1147 `reviewid` int(11) NOT NULL auto_increment,
1148 `borrowernumber` int(11) default NULL,
1149 `biblionumber` int(11) default NULL,
1151 `approved` tinyint(4) default NULL,
1152 `datereviewed` datetime default NULL,
1153 PRIMARY KEY (`reviewid`)
1154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1157 -- Table structure for table `roadtype`
1160 DROP TABLE IF EXISTS `roadtype`;
1161 CREATE TABLE `roadtype` (
1162 `roadtypeid` int(11) NOT NULL auto_increment,
1163 `road_type` char(100) NOT NULL default '',
1164 PRIMARY KEY (`roadtypeid`)
1165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1168 -- Table structure for table `serial`
1171 DROP TABLE IF EXISTS `serial`;
1172 CREATE TABLE `serial` (
1173 `serialid` int(11) NOT NULL auto_increment,
1174 `biblionumber` varchar(100) NOT NULL default '',
1175 `subscriptionid` varchar(100) NOT NULL default '',
1176 `serialseq` varchar(100) NOT NULL default '',
1177 `status` tinyint(4) NOT NULL default '0',
1178 `planneddate` date NOT NULL default '0000-00-00',
1180 `publisheddate` date default NULL,
1182 `claimdate` date default NULL,
1183 `routingnotes` text,
1184 PRIMARY KEY (`serialid`)
1185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1188 -- Table structure for table `sessions`
1191 DROP TABLE IF EXISTS sessions;
1192 CREATE TABLE sessions (
1193 `id` char(32) NOT NULL,
1194 `a_session` text NOT NULL,
1196 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1199 -- Table structure for table `special_holidays`
1202 DROP TABLE IF EXISTS `special_holidays`;
1203 CREATE TABLE `special_holidays` (
1204 `id` int(11) NOT NULL auto_increment,
1205 `branchcode` varchar(4) NOT NULL default '',
1206 `day` smallint(6) NOT NULL default '0',
1207 `month` smallint(6) NOT NULL default '0',
1208 `year` smallint(6) NOT NULL default '0',
1209 `isexception` smallint(1) NOT NULL default '1',
1210 `title` varchar(50) NOT NULL default '',
1211 `description` text NOT NULL,
1213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1216 -- Table structure for table `statistics`
1219 DROP TABLE IF EXISTS `statistics`;
1220 CREATE TABLE `statistics` (
1221 `datetime` datetime NOT NULL default '0000-00-00 00:00:00',
1222 `branch` varchar(4) default NULL,
1223 `proccode` varchar(4) default NULL,
1224 `value` double(16,4) default NULL,
1225 `type` varchar(16) default NULL,
1227 `usercode` varchar(10) default NULL,
1228 `itemnumber` int(11) default NULL,
1229 `itemtype` varchar(4) default NULL,
1230 `borrowernumber` int(11) default NULL,
1231 `associatedborrower` int(11) default NULL,
1232 KEY `timeidx` (`datetime`)
1233 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1236 -- Table structure for table `stopwords`
1239 DROP TABLE IF EXISTS `stopwords`;
1240 CREATE TABLE `stopwords` (
1241 `word` varchar(255) default NULL
1242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1245 -- Table structure for table `subcategorytable`
1248 DROP TABLE IF EXISTS `subcategorytable`;
1249 CREATE TABLE `subcategorytable` (
1250 `subcategorycode` varchar(5) NOT NULL default '',
1252 `itemtypecodes` text,
1253 PRIMARY KEY (`subcategorycode`)
1254 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1257 -- Table structure for table `subscription`
1260 DROP TABLE IF EXISTS `subscription`;
1261 CREATE TABLE `subscription` (
1262 `biblionumber` int(11) NOT NULL default '0',
1263 `subscriptionid` int(11) NOT NULL auto_increment,
1264 `librarian` varchar(100) default '',
1265 `startdate` date default '0000-00-00',
1266 `aqbooksellerid` int(11) default '0',
1267 `cost` int(11) default '0',
1268 `aqbudgetid` int(11) default '0',
1269 `weeklength` tinyint(4) default '0',
1270 `monthlength` tinyint(4) default '0',
1271 `numberlength` tinyint(4) default '0',
1272 `periodicity` tinyint(4) default '0',
1273 `dow` varchar(100) default '',
1274 `numberingmethod` varchar(100) default '',
1276 `status` varchar(100) NOT NULL default '',
1277 `add1` int(11) default '0',
1278 `every1` int(11) default '0',
1279 `whenmorethan1` int(11) default '0',
1280 `setto1` int(11) default NULL,
1281 `lastvalue1` int(11) default NULL,
1282 `add2` int(11) default '0',
1283 `every2` int(11) default '0',
1284 `whenmorethan2` int(11) default '0',
1285 `setto2` int(11) default NULL,
1286 `lastvalue2` int(11) default NULL,
1287 `add3` int(11) default '0',
1288 `every3` int(11) default '0',
1289 `innerloop1` int(11) default '0',
1290 `innerloop2` int(11) default '0',
1291 `innerloop3` int(11) default '0',
1292 `whenmorethan3` int(11) default '0',
1293 `setto3` int(11) default NULL,
1294 `lastvalue3` int(11) default NULL,
1295 `issuesatonce` tinyint(3) NOT NULL default '1',
1296 `firstacquidate` date NOT NULL default '0000-00-00',
1297 `manualhistory` tinyint(1) NOT NULL default '0',
1298 `irregularity` text,
1299 `letter` varchar(20) default NULL,
1300 `numberpattern` tinyint(3) default '0',
1301 `distributedto` text,
1302 `internalnotes` longtext,
1304 `branchcode` varchar(12) NOT NULL default '',
1305 `hemisphere` tinyint(3) default '0',
1306 PRIMARY KEY (`subscriptionid`)
1307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1310 -- Table structure for table `subscriptionhistory`
1313 DROP TABLE IF EXISTS `subscriptionhistory`;
1314 CREATE TABLE `subscriptionhistory` (
1315 `biblionumber` int(11) NOT NULL default '0',
1316 `subscriptionid` int(11) NOT NULL default '0',
1317 `histstartdate` date NOT NULL default '0000-00-00',
1318 `enddate` date default '0000-00-00',
1319 `missinglist` longtext NOT NULL,
1320 `recievedlist` longtext NOT NULL,
1321 `opacnote` varchar(150) NOT NULL default '',
1322 `librariannote` varchar(150) NOT NULL default '',
1323 PRIMARY KEY (`subscriptionid`),
1324 KEY `biblionumber` (`biblionumber`)
1325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1328 -- Table structure for table `subscriptionroutinglist`
1331 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1332 CREATE TABLE `subscriptionroutinglist` (
1333 `routingid` int(11) NOT NULL auto_increment,
1334 `borrowernumber` int(11) default NULL,
1335 `ranking` int(11) default NULL,
1336 `subscriptionid` int(11) default NULL,
1337 PRIMARY KEY (`routingid`)
1338 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1341 -- Table structure for table `suggestions`
1344 DROP TABLE IF EXISTS `suggestions`;
1345 CREATE TABLE `suggestions` (
1346 `suggestionid` int(8) NOT NULL auto_increment,
1347 `suggestedby` int(11) NOT NULL default '0',
1348 `managedby` int(11) default NULL,
1349 `STATUS` varchar(10) NOT NULL default '',
1351 `author` varchar(80) default NULL,
1352 `title` varchar(80) default NULL,
1353 `copyrightdate` smallint(6) default NULL,
1354 `publishercode` varchar(255) default NULL,
1355 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1356 `volumedesc` varchar(255) default NULL,
1357 `publicationyear` smallint(6) default '0',
1358 `place` varchar(255) default NULL,
1359 `isbn` varchar(10) default NULL,
1360 `mailoverseeing` smallint(1) default '0',
1361 `biblionumber` int(11) default NULL,
1363 PRIMARY KEY (`suggestionid`),
1364 KEY `suggestedby` (`suggestedby`),
1365 KEY `managedby` (`managedby`)
1366 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1369 -- Table structure for table `systempreferences`
1372 DROP TABLE IF EXISTS `systempreferences`;
1373 CREATE TABLE `systempreferences` (
1374 `variable` varchar(50) NOT NULL default '',
1376 `options` mediumtext,
1378 `type` varchar(20) default NULL,
1379 PRIMARY KEY (`variable`)
1380 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1383 -- Table structure for table `tags`
1386 DROP TABLE IF EXISTS `tags`;
1387 CREATE TABLE `tags` (
1388 `entry` varchar(255) NOT NULL default '',
1389 `weight` bigint(20) NOT NULL default '0',
1390 PRIMARY KEY (`entry`)
1391 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1394 -- Table structure for table `userflags`
1397 DROP TABLE IF EXISTS `userflags`;
1398 CREATE TABLE `userflags` (
1399 `bit` int(11) NOT NULL default '0',
1400 `flag` char(30) default NULL,
1401 `flagdesc` char(255) default NULL,
1402 `defaulton` int(11) default NULL,
1404 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1407 -- Table structure for table `virtualshelves`
1410 DROP TABLE IF EXISTS `virtualshelves`;
1411 CREATE TABLE `virtualshelves` (
1412 `shelfnumber` int(11) NOT NULL auto_increment,
1413 `shelfname` char(255) default NULL,
1414 `owner` char(80) default NULL,
1415 `category` char(1) default NULL,
1416 PRIMARY KEY (`shelfnumber`)
1417 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1420 -- Table structure for table `virtualshelfcontents`
1423 DROP TABLE IF EXISTS `virtualshelfcontents`;
1424 CREATE TABLE `virtualshelfcontents` (
1425 `shelfnumber` int(11) NOT NULL default '0',
1426 `biblionumber` int(11) NOT NULL default '0',
1427 `flags` int(11) default NULL,
1428 `dateadded` timestamp NULL default NULL,
1429 KEY `shelfnumber` (`shelfnumber`),
1430 KEY `biblionumber` (`biblionumber`),
1431 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1432 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1433 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1436 -- Table structure for table `z3950servers`
1439 DROP TABLE IF EXISTS `z3950servers`;
1440 CREATE TABLE `z3950servers` (
1441 `host` varchar(255) default NULL,
1442 `port` int(11) default NULL,
1443 `db` varchar(255) default NULL,
1444 `userid` varchar(255) default NULL,
1445 `password` varchar(255) default NULL,
1447 `id` int(11) NOT NULL auto_increment,
1448 `checked` smallint(6) default NULL,
1449 `rank` int(11) default NULL,
1450 `syntax` varchar(80) default NULL,
1452 `position` enum('primary','secondary','') NOT NULL default 'primary',
1453 `type` enum('zed','opensearch') NOT NULL default 'zed',
1454 `description` text NOT NULL,
1456 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1459 -- Table structure for table `zebraqueue`
1462 DROP TABLE IF EXISTS `zebraqueue`;
1463 CREATE TABLE `zebraqueue` (
1464 `id` int(11) NOT NULL auto_increment,
1465 `biblio_auth_number` int(11) NOT NULL default '0',
1466 `operation` char(20) NOT NULL default '',
1467 `server` char(20) NOT NULL default '',
1469 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1471 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1472 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1473 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1474 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1475 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1476 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1477 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;