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 `virtualshelves`
418 DROP TABLE IF EXISTS `virtualshelves`;
419 CREATE TABLE `virtualshelves` (
420 `shelfnumber` int(11) NOT NULL auto_increment,
421 `shelfname` char(255) default NULL,
422 `owner` char(80) default NULL,
423 `category` char(1) default NULL,
424 PRIMARY KEY (`shelfnumber`)
425 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
428 -- Table structure for table `borrowers`
431 DROP TABLE IF EXISTS `borrowers`;
432 CREATE TABLE `borrowers` (
433 `borrowernumber` int(11) NOT NULL auto_increment,
434 `cardnumber` varchar(16) default NULL,
435 `surname` mediumtext NOT NULL,
438 `othernames` mediumtext,
440 `streetnumber` varchar(10) default NULL,
441 `streettype` varchar(50) default NULL,
442 `address` mediumtext NOT NULL,
444 `city` mediumtext NOT NULL,
445 `zipcode` varchar(25) default NULL,
448 `mobile` varchar(50) default NULL,
452 `B_streetnumber` varchar(10) default NULL,
453 `B_streettype` varchar(50) default NULL,
454 `B_address` varchar(100) default NULL,
456 `B_zipcode` varchar(25) default NULL,
458 `B_phone` mediumtext,
459 `dateofbirth` date default NULL,
460 `branchcode` varchar(10) NOT NULL default '',
461 `categorycode` varchar(10) NOT NULL default '',
462 `dateenrolled` date default NULL,
463 `dateexpiry` date default NULL,
464 `gonenoaddress` tinyint(1) default NULL,
465 `lost` tinyint(1) default NULL,
466 `debarred` tinyint(1) default NULL,
467 `contactname` mediumtext,
468 `contactfirstname` text,
470 `guarantorid` int(11) default NULL,
471 `borrowernotes` mediumtext,
472 `relationship` varchar(100) default NULL,
473 `ethnicity` varchar(50) default NULL,
474 `ethnotes` varchar(255) default NULL,
475 `sex` char(1) default NULL,
476 `password` varchar(30) default NULL,
477 `flags` int(11) default NULL,
478 `userid` varchar(30) default NULL,
479 `opacnote` mediumtext,
480 `contactnote` varchar(255) default NULL,
481 `sort1` varchar(80) default NULL,
482 `sort2` varchar(80) default NULL,
483 UNIQUE KEY `cardnumber` (`cardnumber`),
484 KEY `borrowernumber` (`borrowernumber`),
485 KEY `categorycode` (`categorycode`),
486 KEY `branchcode` (`branchcode`),
487 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
488 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
489 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
492 -- Table structure for table `branchcategories`
495 DROP TABLE IF EXISTS `branchcategories`;
496 CREATE TABLE `branchcategories` (
497 `categorycode` varchar(4) NOT NULL default '',
498 `categoryname` mediumtext,
499 `codedescription` mediumtext,
500 PRIMARY KEY (`categorycode`)
501 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
504 -- Table structure for table `branches`
507 DROP TABLE IF EXISTS `branches`;
508 CREATE TABLE `branches` (
509 `branchcode` varchar(10) NOT NULL default '',
510 `branchname` mediumtext NOT NULL,
511 `branchaddress1` mediumtext,
512 `branchaddress2` mediumtext,
513 `branchaddress3` mediumtext,
514 `branchphone` mediumtext,
515 `branchfax` mediumtext,
516 `branchemail` mediumtext,
517 `issuing` tinyint(4) default NULL,
518 `branchip` varchar(15) default NULL,
519 `branchprinter` varchar(100) default NULL,
520 UNIQUE KEY `branchcode` (`branchcode`)
521 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
524 -- Table structure for table `branchrelations`
527 DROP TABLE IF EXISTS `branchrelations`;
528 CREATE TABLE `branchrelations` (
529 `branchcode` varchar(4) NOT NULL default '',
530 `categorycode` varchar(4) NOT NULL default '',
531 PRIMARY KEY (`branchcode`,`categorycode`),
532 KEY `branchcode` (`branchcode`),
533 KEY `categorycode` (`categorycode`),
534 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
535 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
539 -- Table structure for table `branchtransfers`
542 DROP TABLE IF EXISTS `branchtransfers`;
543 CREATE TABLE `branchtransfers` (
544 `itemnumber` int(11) NOT NULL default '0',
545 `datesent` datetime default NULL,
546 `frombranch` varchar(10) NOT NULL default '',
547 `datearrived` datetime default NULL,
548 `tobranch` varchar(10) NOT NULL default '',
549 `comments` mediumtext,
550 KEY `frombranch` (`frombranch`),
551 KEY `tobranch` (`tobranch`),
552 KEY `itemnumber` (`itemnumber`),
553 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
554 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
555 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
556 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
559 -- Table structure for table `categories`
562 DROP TABLE IF EXISTS `categories`;
563 CREATE TABLE `categories` (
564 `categorycode` varchar(10) NOT NULL default '',
565 `description` mediumtext,
566 `enrolmentperiod` smallint(6) default NULL,
567 `upperagelimit` smallint(6) default NULL,
568 `dateofbirthrequired` tinyint(1) default NULL,
569 `finetype` varchar(30) default NULL,
570 `bulk` tinyint(1) default NULL,
571 `enrolmentfee` decimal(28,6) default NULL,
572 `overduenoticerequired` tinyint(1) default NULL,
573 `issuelimit` smallint(6) default NULL,
574 `reservefee` decimal(28,6) default NULL,
575 `category_type` char(1) NOT NULL default 'A',
576 PRIMARY KEY (`categorycode`),
577 UNIQUE KEY `categorycode` (`categorycode`)
578 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
581 -- Table structure for table `categorytable`
584 DROP TABLE IF EXISTS `categorytable`;
585 CREATE TABLE `categorytable` (
586 `categorycode` varchar(5) NOT NULL default '',
588 `itemtypecodes` text,
589 PRIMARY KEY (`categorycode`)
590 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
593 -- Table structure for table `cities`
596 DROP TABLE IF EXISTS `cities`;
597 CREATE TABLE `cities` (
598 `cityid` int(11) NOT NULL auto_increment,
599 `city_name` char(100) NOT NULL default '',
600 `city_zipcode` char(20) default NULL,
601 PRIMARY KEY (`cityid`)
602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
605 -- Table structure for table `currency`
608 DROP TABLE IF EXISTS `currency`;
609 CREATE TABLE `currency` (
610 `currency` varchar(10) NOT NULL default '',
611 `rate` float(7,5) default NULL,
612 PRIMARY KEY (`currency`)
613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
616 -- Table structure for table `deletedbiblio`
619 DROP TABLE IF EXISTS `deletedbiblio`;
620 CREATE TABLE `deletedbiblio` (
621 `biblionumber` int(11) NOT NULL default '0',
624 `unititle` mediumtext,
626 `serial` tinyint(1) default NULL,
627 `seriestitle` mediumtext,
628 `copyrightdate` smallint(6) default NULL,
629 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
631 `abstract` mediumtext,
632 PRIMARY KEY (`biblionumber`),
633 KEY `blbnoidx` (`biblionumber`)
634 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
637 -- Table structure for table `deletedbiblioitems`
640 DROP TABLE IF EXISTS `deletedbiblioitems`;
641 CREATE TABLE `deletedbiblioitems` (
642 `biblioitemnumber` int(11) NOT NULL default '0',
643 `biblionumber` int(11) NOT NULL default '0',
646 `classification` varchar(25) default NULL,
647 `itemtype` varchar(4) default NULL,
648 `isbn` varchar(14) default NULL,
649 `issn` varchar(9) default NULL,
650 `dewey` double(8,6) default NULL,
651 `subclass` varchar(3) default NULL,
652 `publicationyear` smallint(6) default NULL,
653 `publishercode` varchar(255) default NULL,
654 `volumedate` date default NULL,
655 `volumeddesc` varchar(255) default NULL,
656 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
657 `illus` varchar(255) default NULL,
658 `pages` varchar(255) default NULL,
660 `size` varchar(255) default NULL,
661 `lccn` varchar(25) default NULL,
663 `url` varchar(255) default NULL,
664 `place` varchar(255) default NULL,
665 `lcsort` varchar(25) default NULL,
666 `ccode` varchar(4) default NULL,
667 PRIMARY KEY (`biblioitemnumber`),
668 KEY `bibinoidx` (`biblioitemnumber`),
669 KEY `bibnoidx` (`biblionumber`)
670 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
673 -- Table structure for table `deletedborrowers`
676 DROP TABLE IF EXISTS `deletedborrowers`;
677 CREATE TABLE `deletedborrowers` (
678 `borrowernumber` int(11) NOT NULL default '0',
679 `cardnumber` varchar(9) NOT NULL default '',
680 `surname` mediumtext NOT NULL,
683 `othernames` mediumtext,
685 `streetnumber` varchar(10) default NULL,
686 `streettype` varchar(50) default NULL,
687 `address` mediumtext NOT NULL,
689 `city` mediumtext NOT NULL,
690 `zipcode` varchar(25) default NULL,
693 `mobile` varchar(50) default NULL,
697 `B_streetnumber` varchar(10) default NULL,
698 `B_streettype` varchar(50) default NULL,
699 `B_address` varchar(100) default NULL,
701 `B_zipcode` varchar(25) default NULL,
703 `B_phone` mediumtext,
704 `dateofbirth` date default NULL,
705 `branchcode` varchar(4) NOT NULL default '',
706 `categorycode` varchar(2) default NULL,
707 `dateenrolled` date default NULL,
708 `dateexpiry` date default NULL,
709 `gonenoaddress` tinyint(1) default NULL,
710 `lost` tinyint(1) default NULL,
711 `debarred` tinyint(1) default NULL,
712 `contactname` mediumtext,
713 `contactfirstname` text,
715 `guarantorid` int(11) default NULL,
716 `borrowernotes` mediumtext,
717 `relationship` varchar(100) default NULL,
718 `ethnicity` varchar(50) default NULL,
719 `ethnotes` varchar(255) default NULL,
720 `sex` char(1) default NULL,
721 `password` varchar(30) default NULL,
722 `flags` int(11) default NULL,
723 `userid` varchar(30) default NULL,
724 `opacnote` mediumtext,
725 `contactnote` varchar(255) default NULL,
726 `sort1` varchar(80) default NULL,
727 `sort2` varchar(80) default NULL,
728 KEY `borrowernumber` (`borrowernumber`),
729 KEY `cardnumber` (`cardnumber`)
730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
733 -- Table structure for table `deleteditems`
736 DROP TABLE IF EXISTS `deleteditems`;
737 CREATE TABLE `deleteditems` (
738 `itemnumber` int(11) NOT NULL default '0',
739 `biblionumber` int(11) NOT NULL default '0',
740 `multivolumepart` varchar(30) default NULL,
741 `biblioitemnumber` int(11) NOT NULL default '0',
742 `barcode` varchar(9) NOT NULL default '',
743 `dateaccessioned` date default NULL,
744 `booksellerid` varchar(10) default NULL,
745 `homebranch` varchar(4) default NULL,
746 `price` decimal(28,6) default NULL,
747 `replacementprice` decimal(28,6) default NULL,
748 `replacementpricedate` date default NULL,
749 `datelastborrowed` date default NULL,
750 `datelastseen` date default NULL,
751 `multivolume` tinyint(1) default NULL,
752 `stack` tinyint(1) default NULL,
753 `notforloan` tinyint(1) default NULL,
754 `itemlost` tinyint(1) default NULL,
755 `wthdrawn` tinyint(1) default NULL,
756 `bulk` varchar(30) default NULL,
757 `issues` smallint(6) default NULL,
758 `renewals` smallint(6) default NULL,
759 `reserves` smallint(6) default NULL,
760 `restricted` tinyint(1) default NULL,
761 `binding` decimal(28,6) default NULL,
762 `itemnotes` mediumtext,
763 `holdingbranch` varchar(4) default NULL,
764 `interim` tinyint(1) default NULL,
765 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
767 `paidfor` mediumtext,
768 `location` varchar(80) default NULL,
769 `itemcallnumber` varchar(30) default NULL,
770 `onloan` date default '0000-00-00',
771 `cutterextra` varchar(45) default NULL,
772 `issue_date` date default NULL,
773 `itype` varchar(10) default NULL,
774 PRIMARY KEY (`itemnumber`),
775 UNIQUE KEY `barcode` (`barcode`),
776 KEY `itembarcodeidx` (`barcode`),
777 KEY `itembinoidx` (`biblioitemnumber`),
778 KEY `itembibnoidx` (`biblionumber`)
779 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
782 -- Table structure for table `ethnicity`
785 DROP TABLE IF EXISTS `ethnicity`;
786 CREATE TABLE `ethnicity` (
787 `code` varchar(10) NOT NULL default '',
788 `name` varchar(255) default NULL,
790 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
793 -- Table structure for table `issues`
796 DROP TABLE IF EXISTS `issues`;
797 CREATE TABLE `issues` (
798 `borrowernumber` int(11) default NULL,
799 `itemnumber` int(11) default NULL,
800 `date_due` date default NULL,
801 `branchcode` varchar(10) default NULL,
802 `issuingbranch` varchar(18) default NULL,
803 `returndate` date default NULL,
804 `lastreneweddate` date default NULL,
805 `return` varchar(4) default NULL,
806 `renewals` tinyint(4) default NULL,
807 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
808 `issuedate` date NOT NULL default '0000-00-00',
809 KEY `issuesborridx` (`borrowernumber`),
810 KEY `issuesitemidx` (`itemnumber`),
811 KEY `bordate` (`borrowernumber`,`timestamp`),
812 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
813 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
814 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
817 -- Table structure for table `issuingrules`
820 DROP TABLE IF EXISTS `issuingrules`;
821 CREATE TABLE `issuingrules` (
822 `categorycode` varchar(2) NOT NULL default '',
823 `itemtype` varchar(4) NOT NULL default '',
824 `restrictedtype` tinyint(1) default NULL,
825 `rentaldiscount` decimal(28,6) default NULL,
826 `reservecharge` decimal(28,6) default NULL,
827 `fine` decimal(28,6) default NULL,
828 `firstremind` int(11) default NULL,
829 `chargeperiod` int(11) default NULL,
830 `accountsent` int(11) default NULL,
831 `chargename` varchar(100) default NULL,
832 `maxissueqty` int(4) default NULL,
833 `issuelength` int(4) default NULL,
834 `branchcode` varchar(4) NOT NULL default '',
835 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
836 KEY `categorycode` (`categorycode`),
837 KEY `itemtype` (`itemtype`),
838 CONSTRAINT `issuingrules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE,
839 CONSTRAINT `issuingrules_ibfk_2` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
840 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
843 -- Table structure for table `items`
846 DROP TABLE IF EXISTS `items`;
847 CREATE TABLE `items` (
848 `itemnumber` int(11) NOT NULL default '0',
849 `biblionumber` int(11) NOT NULL default '0',
850 `multivolumepart` varchar(30) default NULL,
851 `biblioitemnumber` int(11) NOT NULL default '0',
852 `barcode` varchar(20) default NULL,
853 `dateaccessioned` date default NULL,
854 `booksellerid` varchar(10) default NULL,
855 `homebranch` varchar(4) default NULL,
856 `price` decimal(8,2) default NULL,
857 `replacementprice` decimal(8,2) default NULL,
858 `replacementpricedate` date default NULL,
859 `datelastborrowed` date default NULL,
860 `datelastseen` date default NULL,
861 `multivolume` tinyint(1) default NULL,
862 `stack` tinyint(1) default NULL,
863 `notforloan` tinyint(1) default NULL,
864 `itemlost` tinyint(1) default NULL,
865 `wthdrawn` tinyint(1) default NULL,
866 `itemcallnumber` varchar(30) default NULL,
867 `issues` smallint(6) default NULL,
868 `renewals` smallint(6) default NULL,
869 `reserves` smallint(6) default NULL,
870 `restricted` tinyint(1) default NULL,
871 `binding` decimal(28,6) default NULL,
872 `itemnotes` mediumtext,
873 `holdingbranch` varchar(10) default NULL,
874 `paidfor` mediumtext,
875 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
876 `location` varchar(80) default NULL,
877 `onloan` date default '0000-00-00',
878 `cutterextra` varchar(45) default NULL,
879 `issue_date` date default NULL,
880 `itype` varchar(10) default NULL,
881 PRIMARY KEY (`itemnumber`),
882 KEY `itembarcodeidx` (`barcode`),
883 KEY `itembinoidx` (`biblioitemnumber`),
884 KEY `itembibnoidx` (`biblionumber`),
885 KEY `homebranch` (`homebranch`),
886 KEY `holdingbranch` (`holdingbranch`),
887 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
888 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
889 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
890 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
893 -- Table structure for table `itemtypes`
896 DROP TABLE IF EXISTS `itemtypes`;
897 CREATE TABLE `itemtypes` (
898 `itemtype` varchar(10) NOT NULL default '',
899 `description` mediumtext,
900 `renewalsallowed` smallint(6) default NULL,
901 `rentalcharge` double(16,4) default NULL,
902 `notforloan` smallint(6) default NULL,
903 `imageurl` varchar(200) default NULL,
905 PRIMARY KEY (`itemtype`),
906 UNIQUE KEY `itemtype` (`itemtype`)
907 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
910 -- Table structure for table `labels`
913 DROP TABLE IF EXISTS `labels`;
914 CREATE TABLE `labels` (
915 `labelid` int(11) NOT NULL auto_increment,
916 `itemnumber` varchar(100) NOT NULL default '',
917 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
918 PRIMARY KEY (`labelid`)
919 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
922 -- Table structure for table `labels_conf`
925 DROP TABLE IF EXISTS `labels_conf`;
926 CREATE TABLE `labels_conf` (
927 `id` int(4) NOT NULL auto_increment,
928 `barcodetype` char(100) default '',
929 `title` tinyint(1) default '0',
930 `isbn` tinyint(1) default '0',
931 `itemtype` tinyint(1) default '0',
932 `barcode` tinyint(1) default '0',
933 `dewey` tinyint(1) default '0',
934 `class` tinyint(1) default '0',
935 `author` tinyint(1) default '0',
936 `papertype` char(100) default '',
937 `startrow` int(2) default NULL,
939 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
942 -- Table structure for table `letter`
945 DROP TABLE IF EXISTS `letter`;
946 CREATE TABLE `letter` (
947 `module` varchar(20) NOT NULL default '',
948 `code` varchar(20) NOT NULL default '',
949 `name` varchar(100) NOT NULL default '',
950 `title` varchar(200) NOT NULL default '',
952 PRIMARY KEY (`module`,`code`)
953 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
957 -- Table structure for table `marc_breeding`
960 DROP TABLE IF EXISTS `marc_breeding`;
961 CREATE TABLE `marc_breeding` (
962 `id` bigint(20) NOT NULL auto_increment,
963 `file` varchar(80) NOT NULL default '',
964 `isbn` varchar(10) NOT NULL default '',
965 `title` varchar(128) default NULL,
966 `author` varchar(80) default NULL,
968 `encoding` varchar(40) NOT NULL default '',
969 `z3950random` varchar(40) default NULL,
971 KEY `title` (`title`),
973 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
976 -- Table structure for table `marc_subfield_structure`
979 DROP TABLE IF EXISTS `marc_subfield_structure`;
980 CREATE TABLE `marc_subfield_structure` (
981 `tagfield` varchar(3) NOT NULL default '',
982 `tagsubfield` char(1) NOT NULL default '',
983 `liblibrarian` varchar(255) NOT NULL default '',
984 `libopac` varchar(255) NOT NULL default '',
985 `repeatable` tinyint(4) NOT NULL default '0',
986 `mandatory` tinyint(4) NOT NULL default '0',
987 `kohafield` varchar(40) default NULL,
988 `tab` tinyint(1) default NULL,
989 `authorised_value` varchar(10) default NULL,
990 `authtypecode` varchar(10) default NULL,
991 `value_builder` varchar(80) default NULL,
992 `isurl` tinyint(1) default NULL,
993 `hidden` tinyint(1) default NULL,
994 `frameworkcode` varchar(4) NOT NULL default '',
995 `seealso` varchar(255) default NULL,
996 `link` varchar(80) default NULL,
997 `defaultvalue` text default NULL,
998 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
999 KEY `kohafield_2` (`kohafield`),
1000 KEY `tab` (`frameworkcode`,`tab`),
1001 KEY `kohafield` (`frameworkcode`,`kohafield`)
1002 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1005 -- Table structure for table `marc_tag_structure`
1008 DROP TABLE IF EXISTS `marc_tag_structure`;
1009 CREATE TABLE `marc_tag_structure` (
1010 `tagfield` char(3) NOT NULL default '',
1011 `liblibrarian` char(255) NOT NULL default '',
1012 `libopac` char(255) NOT NULL default '',
1013 `repeatable` tinyint(4) NOT NULL default '0',
1014 `mandatory` tinyint(4) NOT NULL default '0',
1015 `authorised_value` char(10) default NULL,
1016 `frameworkcode` char(4) NOT NULL default '',
1017 PRIMARY KEY (`frameworkcode`,`tagfield`)
1018 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1021 -- Table structure for table `mediatypetable`
1024 DROP TABLE IF EXISTS `mediatypetable`;
1025 CREATE TABLE `mediatypetable` (
1026 `mediatypecode` varchar(5) NOT NULL default '',
1028 `itemtypecodes` text,
1029 PRIMARY KEY (`mediatypecode`)
1030 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1033 -- Table structure for table `notifys`
1036 DROP TABLE IF EXISTS `notifys`;
1037 CREATE TABLE `notifys` (
1038 `notify_id` int(11) NOT NULL default '0',
1039 `borrowernumber` int(11) NOT NULL default '0',
1040 `itemnumber` int(11) NOT NULL default '0',
1041 `notify_date` date NOT NULL default '0000-00-00',
1042 `notify_send_date` date default NULL,
1043 `notify_level` int(1) NOT NULL default '0',
1044 `method` varchar(20) NOT NULL default ''
1045 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1048 -- Table structure for table `opac_news`
1051 DROP TABLE IF EXISTS `opac_news`;
1052 CREATE TABLE `opac_news` (
1053 `idnew` int(10) unsigned NOT NULL auto_increment,
1054 `title` varchar(250) NOT NULL default '',
1055 `new` text NOT NULL,
1056 `lang` varchar(4) NOT NULL default '',
1057 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1058 `expirationdate` date default NULL,
1059 `number` int(11) default NULL,
1060 PRIMARY KEY (`idnew`)
1061 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1064 -- Table structure for table `overduerules`
1067 DROP TABLE IF EXISTS `overduerules`;
1068 CREATE TABLE `overduerules` (
1069 `branchcode` varchar(255) NOT NULL default '',
1070 `categorycode` varchar(2) NOT NULL default '',
1071 `delay1` int(4) default '0',
1072 `letter1` varchar(20) default NULL,
1073 `debarred1` char(1) default '0',
1074 `delay2` int(4) default '0',
1075 `debarred2` char(1) default '0',
1076 `letter2` varchar(20) default NULL,
1077 `delay3` int(4) default '0',
1078 `letter3` varchar(20) default NULL,
1079 `debarred3` int(1) default '0',
1080 PRIMARY KEY (`branchcode`,`categorycode`)
1081 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1084 -- Table structure for table `printers`
1087 DROP TABLE IF EXISTS `printers`;
1088 CREATE TABLE `printers` (
1089 `printername` char(40) NOT NULL default '''''',
1090 `printqueue` char(20) default NULL,
1091 `printtype` char(20) default NULL,
1092 PRIMARY KEY (`printername`)
1093 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1096 -- Table structure for table `repeatable_holidays`
1099 DROP TABLE IF EXISTS `repeatable_holidays`;
1100 CREATE TABLE `repeatable_holidays` (
1101 `id` int(11) NOT NULL auto_increment,
1102 `branchcode` varchar(4) NOT NULL default '',
1103 `weekday` smallint(6) default NULL,
1104 `day` smallint(6) default NULL,
1105 `month` smallint(6) default NULL,
1106 `title` varchar(50) NOT NULL default '',
1107 `description` text NOT NULL,
1109 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1112 -- Table structure for table `reserveconstraints`
1115 DROP TABLE IF EXISTS `reserveconstraints`;
1116 CREATE TABLE `reserveconstraints` (
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 `biblioitemnumber` int(11) default NULL,
1121 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1122 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1125 -- Table structure for table `reserves`
1128 DROP TABLE IF EXISTS `reserves`;
1129 CREATE TABLE `reserves` (
1130 `borrowernumber` int(11) NOT NULL default '0',
1131 `reservedate` date NOT NULL default '0000-00-00',
1132 `biblionumber` int(11) NOT NULL default '0',
1133 `constrainttype` char(1) default NULL,
1134 `branchcode` varchar(4) default NULL,
1135 `notificationdate` date default NULL,
1136 `reminderdate` date default NULL,
1137 `cancellationdate` date default NULL,
1138 `reservenotes` mediumtext,
1139 `priority` smallint(6) default NULL,
1140 `found` char(1) default NULL,
1141 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1142 `itemnumber` int(11) default NULL,
1143 `waitingdate` date default NULL,
1144 KEY `borrowernumber` (`borrowernumber`),
1145 KEY `biblionumber` (`biblionumber`),
1146 KEY `itemnumber` (`itemnumber`),
1147 KEY `branchcode` (`branchcode`),
1148 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1149 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1150 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1151 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1152 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1155 -- Table structure for table `reviews`
1158 DROP TABLE IF EXISTS `reviews`;
1159 CREATE TABLE `reviews` (
1160 `reviewid` int(11) NOT NULL auto_increment,
1161 `borrowernumber` int(11) default NULL,
1162 `biblionumber` int(11) default NULL,
1164 `approved` tinyint(4) default NULL,
1165 `datereviewed` datetime default NULL,
1166 PRIMARY KEY (`reviewid`)
1167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1170 -- Table structure for table `roadtype`
1173 DROP TABLE IF EXISTS `roadtype`;
1174 CREATE TABLE `roadtype` (
1175 `roadtypeid` int(11) NOT NULL auto_increment,
1176 `road_type` char(100) NOT NULL default '',
1177 PRIMARY KEY (`roadtypeid`)
1178 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1181 -- Table structure for table `serial`
1184 DROP TABLE IF EXISTS `serial`;
1185 CREATE TABLE `serial` (
1186 `serialid` int(11) NOT NULL auto_increment,
1187 `biblionumber` varchar(100) NOT NULL default '',
1188 `subscriptionid` varchar(100) NOT NULL default '',
1189 `serialseq` varchar(100) NOT NULL default '',
1190 `status` tinyint(4) NOT NULL default '0',
1191 `planneddate` date NOT NULL default '0000-00-00',
1193 `publisheddate` date default NULL,
1195 `claimdate` date default NULL,
1196 `routingnotes` text,
1197 PRIMARY KEY (`serialid`)
1198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1201 -- Table structure for table `sessions`
1204 DROP TABLE IF EXISTS sessions;
1205 CREATE TABLE sessions (
1206 `id` char(32) NOT NULL,
1207 `a_session` text NOT NULL,
1209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1212 -- Table structure for table `virtualshelfcontents`
1215 DROP TABLE IF EXISTS `virtualshelfcontents`;
1216 CREATE TABLE `virtualshelfcontents` (
1217 `shelfnumber` int(11) NOT NULL default '0',
1218 `biblionumber` int(11) NOT NULL default '0',
1219 `flags` int(11) default NULL,
1220 `dateadded` timestamp NULL default NULL,
1221 KEY `shelfnumber` (`shelfnumber`),
1222 KEY `biblionumber` (`biblionumber`),
1223 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1224 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1225 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1228 -- Table structure for table `special_holidays`
1231 DROP TABLE IF EXISTS `special_holidays`;
1232 CREATE TABLE `special_holidays` (
1233 `id` int(11) NOT NULL auto_increment,
1234 `branchcode` varchar(4) NOT NULL default '',
1235 `day` smallint(6) NOT NULL default '0',
1236 `month` smallint(6) NOT NULL default '0',
1237 `year` smallint(6) NOT NULL default '0',
1238 `isexception` smallint(1) NOT NULL default '1',
1239 `title` varchar(50) NOT NULL default '',
1240 `description` text NOT NULL,
1242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1245 -- Table structure for table `statistics`
1248 DROP TABLE IF EXISTS `statistics`;
1249 CREATE TABLE `statistics` (
1250 `datetime` datetime NOT NULL default '0000-00-00 00:00:00',
1251 `branch` varchar(4) default NULL,
1252 `proccode` varchar(4) default NULL,
1253 `value` double(16,4) default NULL,
1254 `type` varchar(16) default NULL,
1256 `usercode` varchar(10) default NULL,
1257 `itemnumber` int(11) default NULL,
1258 `itemtype` varchar(4) default NULL,
1259 `borrowernumber` int(11) default NULL,
1260 `associatedborrower` int(11) default NULL,
1261 KEY `timeidx` (`datetime`)
1262 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1265 -- Table structure for table `stopwords`
1268 DROP TABLE IF EXISTS `stopwords`;
1269 CREATE TABLE `stopwords` (
1270 `word` varchar(255) default NULL
1271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1274 -- Table structure for table `subcategorytable`
1277 DROP TABLE IF EXISTS `subcategorytable`;
1278 CREATE TABLE `subcategorytable` (
1279 `subcategorycode` varchar(5) NOT NULL default '',
1281 `itemtypecodes` text,
1282 PRIMARY KEY (`subcategorycode`)
1283 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1286 -- Table structure for table `subscription`
1289 DROP TABLE IF EXISTS `subscription`;
1290 CREATE TABLE `subscription` (
1291 `biblionumber` int(11) NOT NULL default '0',
1292 `subscriptionid` int(11) NOT NULL auto_increment,
1293 `librarian` varchar(100) default '',
1294 `startdate` date default '0000-00-00',
1295 `aqbooksellerid` int(11) default '0',
1296 `cost` int(11) default '0',
1297 `aqbudgetid` int(11) default '0',
1298 `weeklength` tinyint(4) default '0',
1299 `monthlength` tinyint(4) default '0',
1300 `numberlength` tinyint(4) default '0',
1301 `periodicity` tinyint(4) default '0',
1302 `dow` varchar(100) default '',
1303 `numberingmethod` varchar(100) default '',
1305 `status` varchar(100) NOT NULL default '',
1306 `add1` int(11) default '0',
1307 `every1` int(11) default '0',
1308 `whenmorethan1` int(11) default '0',
1309 `setto1` int(11) default NULL,
1310 `lastvalue1` int(11) default NULL,
1311 `add2` int(11) default '0',
1312 `every2` int(11) default '0',
1313 `whenmorethan2` int(11) default '0',
1314 `setto2` int(11) default NULL,
1315 `lastvalue2` int(11) default NULL,
1316 `add3` int(11) default '0',
1317 `every3` int(11) default '0',
1318 `innerloop1` int(11) default '0',
1319 `innerloop2` int(11) default '0',
1320 `innerloop3` int(11) default '0',
1321 `whenmorethan3` int(11) default '0',
1322 `setto3` int(11) default NULL,
1323 `lastvalue3` int(11) default NULL,
1324 `issuesatonce` tinyint(3) NOT NULL default '1',
1325 `firstacquidate` date NOT NULL default '0000-00-00',
1326 `manualhistory` tinyint(1) NOT NULL default '0',
1327 `irregularity` text,
1328 `letter` varchar(20) default NULL,
1329 `numberpattern` tinyint(3) default '0',
1330 `distributedto` text,
1331 `internalnotes` longtext,
1333 `branchcode` varchar(12) NOT NULL default '',
1334 `hemisphere` tinyint(3) default '0',
1335 PRIMARY KEY (`subscriptionid`)
1336 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1339 -- Table structure for table `subscriptionhistory`
1342 DROP TABLE IF EXISTS `subscriptionhistory`;
1343 CREATE TABLE `subscriptionhistory` (
1344 `biblionumber` int(11) NOT NULL default '0',
1345 `subscriptionid` int(11) NOT NULL default '0',
1346 `histstartdate` date NOT NULL default '0000-00-00',
1347 `enddate` date default '0000-00-00',
1348 `missinglist` longtext NOT NULL,
1349 `recievedlist` longtext NOT NULL,
1350 `opacnote` varchar(150) NOT NULL default '',
1351 `librariannote` varchar(150) NOT NULL default '',
1352 PRIMARY KEY (`subscriptionid`),
1353 KEY `biblionumber` (`biblionumber`)
1354 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1357 -- Table structure for table `subscriptionroutinglist`
1360 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1361 CREATE TABLE `subscriptionroutinglist` (
1362 `routingid` int(11) NOT NULL auto_increment,
1363 `borrowernumber` int(11) default NULL,
1364 `ranking` int(11) default NULL,
1365 `subscriptionid` int(11) default NULL,
1366 PRIMARY KEY (`routingid`)
1367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1370 -- Table structure for table `suggestions`
1373 DROP TABLE IF EXISTS `suggestions`;
1374 CREATE TABLE `suggestions` (
1375 `suggestionid` int(8) NOT NULL auto_increment,
1376 `suggestedby` int(11) NOT NULL default '0',
1377 `managedby` int(11) default NULL,
1378 `STATUS` varchar(10) NOT NULL default '',
1380 `author` varchar(80) default NULL,
1381 `title` varchar(80) default NULL,
1382 `copyrightdate` smallint(6) default NULL,
1383 `publishercode` varchar(255) default NULL,
1384 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1385 `volumedesc` varchar(255) default NULL,
1386 `publicationyear` smallint(6) default '0',
1387 `place` varchar(255) default NULL,
1388 `isbn` varchar(10) default NULL,
1389 `mailoverseeing` smallint(1) default '0',
1390 `biblionumber` int(11) default NULL,
1392 PRIMARY KEY (`suggestionid`),
1393 KEY `suggestedby` (`suggestedby`),
1394 KEY `managedby` (`managedby`)
1395 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1398 -- Table structure for table `systempreferences`
1401 DROP TABLE IF EXISTS `systempreferences`;
1402 CREATE TABLE `systempreferences` (
1403 `variable` varchar(50) NOT NULL default '',
1405 `options` mediumtext,
1407 `type` varchar(20) default NULL,
1408 PRIMARY KEY (`variable`)
1409 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1412 -- Table structure for table `tags`
1415 DROP TABLE IF EXISTS `tags`;
1416 CREATE TABLE `tags` (
1417 `entry` varchar(255) NOT NULL default '',
1418 `weight` bigint(20) NOT NULL default '0',
1419 PRIMARY KEY (`entry`)
1420 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1423 -- Table structure for table `userflags`
1426 DROP TABLE IF EXISTS `userflags`;
1427 CREATE TABLE `userflags` (
1428 `bit` int(11) NOT NULL default '0',
1429 `flag` char(30) default NULL,
1430 `flagdesc` char(255) default NULL,
1431 `defaulton` int(11) default NULL,
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 */;