3 -- Host: localhost Database: koha30test
4 -- ------------------------------------------------------
5 -- Server version 4.1.22
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
11 /*!40101 SET NAMES utf8 */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19 -- Table structure for table `accountlines`
22 DROP TABLE IF EXISTS `accountlines`;
23 CREATE TABLE `accountlines` (
24 `borrowernumber` int(11) NOT NULL default 0,
25 `accountno` smallint(6) NOT NULL default 0,
26 `itemnumber` int(11) default NULL,
27 `date` date default NULL,
28 `amount` decimal(28,6) default NULL,
29 `description` mediumtext,
31 `accounttype` varchar(5) default NULL,
32 `amountoutstanding` decimal(28,6) default NULL,
33 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
34 `notify_id` int(11) NOT NULL default 0,
35 `notify_level` int(2) NOT NULL default 0,
36 KEY `acctsborridx` (`borrowernumber`),
37 KEY `timeidx` (`timestamp`),
38 KEY `itemnumber` (`itemnumber`),
39 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
40 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
41 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
44 -- Table structure for table `accountoffsets`
47 DROP TABLE IF EXISTS `accountoffsets`;
48 CREATE TABLE `accountoffsets` (
49 `borrowernumber` int(11) NOT NULL default 0,
50 `accountno` smallint(6) NOT NULL default 0,
51 `offsetaccount` smallint(6) NOT NULL default 0,
52 `offsetamount` decimal(28,6) default NULL,
53 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
54 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
57 -- Table structure for table `action_logs`
60 DROP TABLE IF EXISTS `action_logs`;
61 CREATE TABLE `action_logs` (
62 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
63 `user` int(11) NOT NULL default 0,
66 `object` int(11) default NULL,
68 PRIMARY KEY (`timestamp`,`user`)
69 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
72 -- Table structure for table `alert`
75 DROP TABLE IF EXISTS `alert`;
76 CREATE TABLE `alert` (
77 `alertid` int(11) NOT NULL auto_increment,
78 `borrowernumber` int(11) NOT NULL default 0,
79 `type` varchar(10) NOT NULL default '',
80 `externalid` varchar(20) NOT NULL default '',
81 PRIMARY KEY (`alertid`),
82 KEY `borrowernumber` (`borrowernumber`),
83 KEY `type` (`type`,`externalid`)
84 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
87 -- Table structure for table `aqbasket`
90 DROP TABLE IF EXISTS `aqbasket`;
91 CREATE TABLE `aqbasket` (
92 `basketno` int(11) NOT NULL auto_increment,
93 `creationdate` date default NULL,
94 `closedate` date default NULL,
95 `booksellerid` int(11) NOT NULL default '1',
96 `authorisedby` varchar(10) default NULL,
97 `booksellerinvoicenumber` mediumtext,
98 PRIMARY KEY (`basketno`),
99 KEY `booksellerid` (`booksellerid`),
100 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
101 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
104 -- Table structure for table `aqbookfund`
107 DROP TABLE IF EXISTS `aqbookfund`;
108 CREATE TABLE `aqbookfund` (
109 `bookfundid` char(10) NOT NULL default '',
110 `bookfundname` mediumtext,
111 `bookfundgroup` varchar(5) default NULL,
112 `branchcode` char(10) NOT NULL default '',
113 PRIMARY KEY (`bookfundid`,`branchcode`)
114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
117 -- Table structure for table `aqbooksellers`
120 DROP TABLE IF EXISTS `aqbooksellers`;
121 CREATE TABLE `aqbooksellers` (
122 `id` int(11) NOT NULL auto_increment,
124 `address1` mediumtext,
125 `address2` mediumtext,
126 `address3` mediumtext,
127 `address4` mediumtext,
128 `phone` varchar(30) default NULL,
129 `accountnumber` mediumtext,
130 `othersupplier` mediumtext,
131 `currency` varchar(3) NOT NULL default '',
132 `deliverydays` smallint(6) default NULL,
133 `followupdays` smallint(6) default NULL,
134 `followupscancel` smallint(6) default NULL,
135 `specialty` mediumtext,
136 `booksellerfax` mediumtext,
138 `bookselleremail` mediumtext,
139 `booksellerurl` mediumtext,
140 `contact` varchar(100) default NULL,
142 `url` varchar(255) default NULL,
143 `contpos` varchar(100) default NULL,
144 `contphone` varchar(100) default NULL,
145 `contfax` varchar(100) default NULL,
146 `contaltphone` varchar(100) default NULL,
147 `contemail` varchar(100) default NULL,
148 `contnotes` mediumtext,
149 `active` tinyint(4) default NULL,
150 `listprice` varchar(10) default NULL,
151 `invoiceprice` varchar(10) default NULL,
152 `gstreg` tinyint(4) default NULL,
153 `listincgst` tinyint(4) default NULL,
154 `invoiceincgst` tinyint(4) default NULL,
155 `discount` float(6,4) default NULL,
156 `fax` varchar(50) default NULL,
157 `nocalc` int(11) default NULL,
158 `invoicedisc` float(6,4) default NULL,
160 KEY `listprice` (`listprice`),
161 KEY `invoiceprice` (`invoiceprice`),
162 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
163 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
164 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
167 -- Table structure for table `aqbudget`
170 DROP TABLE IF EXISTS `aqbudget`;
171 CREATE TABLE `aqbudget` (
172 `bookfundid` char(10) NOT NULL default '',
173 `startdate` date NOT NULL default 0,
174 `enddate` date default NULL,
175 `budgetamount` decimal(13,2) default NULL,
176 `aqbudgetid` tinyint(4) NOT NULL auto_increment,
177 `branchcode` char(10) default NULL,
178 PRIMARY KEY (`aqbudgetid`)
179 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
182 -- Table structure for table `aqorderbreakdown`
185 DROP TABLE IF EXISTS `aqorderbreakdown`;
186 CREATE TABLE `aqorderbreakdown` (
187 `ordernumber` int(11) default NULL,
188 `linenumber` int(11) default NULL,
189 `branchcode` char(10) default NULL,
190 `bookfundid` char(10) NOT NULL default '',
191 `allocation` smallint(6) default NULL,
192 KEY `ordernumber` (`ordernumber`),
193 KEY `bookfundid` (`bookfundid`),
194 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
195 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
196 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
199 -- Table structure for table `aqorderdelivery`
202 DROP TABLE IF EXISTS `aqorderdelivery`;
203 CREATE TABLE `aqorderdelivery` (
204 `ordernumber` date default NULL,
205 `deliverynumber` smallint(6) NOT NULL default 0,
206 `deliverydate` varchar(18) default NULL,
207 `qtydelivered` smallint(6) default NULL,
208 `deliverycomments` mediumtext
209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
212 -- Table structure for table `aqorders`
215 DROP TABLE IF EXISTS `aqorders`;
216 CREATE TABLE `aqorders` (
217 `ordernumber` int(11) NOT NULL auto_increment,
218 `biblionumber` int(11) default NULL,
220 `entrydate` date default NULL,
221 `quantity` smallint(6) default NULL,
222 `currency` varchar(3) default NULL,
223 `listprice` decimal(28,6) default NULL,
224 `totalamount` decimal(28,6) default NULL,
225 `datereceived` date default NULL,
226 `booksellerinvoicenumber` mediumtext,
227 `freight` decimal(28,6) default NULL,
228 `unitprice` decimal(28,6) default NULL,
229 `quantityreceived` smallint(6) default NULL,
230 `cancelledby` varchar(10) default NULL,
231 `datecancellationprinted` date default NULL,
233 `supplierreference` mediumtext,
234 `purchaseordernumber` mediumtext,
235 `subscription` tinyint(1) default NULL,
236 `serialid` varchar(30) default NULL,
237 `basketno` int(11) default NULL,
238 `biblioitemnumber` int(11) default NULL,
239 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
240 `rrp` decimal(13,2) default NULL,
241 `ecost` decimal(13,2) default NULL,
242 `gst` decimal(13,2) default NULL,
243 `budgetdate` date default NULL,
244 `sort1` varchar(80) default NULL,
245 `sort2` varchar(80) default NULL,
246 PRIMARY KEY (`ordernumber`),
247 KEY `basketno` (`basketno`),
248 KEY `biblionumber` (`biblionumber`),
249 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
250 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
251 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
254 -- Table structure for table `auth_header`
257 DROP TABLE IF EXISTS `auth_header`;
258 CREATE TABLE `auth_header` (
259 `authid` bigint(20) unsigned NOT NULL auto_increment,
260 `authtypecode` varchar(10) NOT NULL default '',
261 `datecreated` date default NULL,
262 `datemodified` date default NULL,
263 `origincode` varchar(20) default NULL,
264 `authtrees` mediumtext,
266 `linkid` bigint(20) default NULL,
267 `marcxml` longtext NOT NULL,
268 PRIMARY KEY (`authid`),
269 KEY `origincode` (`origincode`)
270 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
273 -- Table structure for table `auth_subfield_structure`
276 DROP TABLE IF EXISTS `auth_subfield_structure`;
277 CREATE TABLE `auth_subfield_structure` (
278 `authtypecode` varchar(10) NOT NULL default '',
279 `tagfield` varchar(3) NOT NULL default '',
280 `tagsubfield` char(1) NOT NULL default '',
281 `liblibrarian` varchar(255) NOT NULL default '',
282 `libopac` varchar(255) NOT NULL default '',
283 `repeatable` tinyint(4) NOT NULL default 0,
284 `mandatory` tinyint(4) NOT NULL default 0,
285 `tab` tinyint(1) default NULL,
286 `authorised_value` varchar(10) default NULL,
287 `value_builder` varchar(80) default NULL,
288 `seealso` varchar(255) default NULL,
289 `isurl` tinyint(1) default NULL,
290 `hidden` tinyint(3) NOT NULL default 0,
291 `linkid` tinyint(1) NOT NULL default 0,
292 `kohafield` varchar(45) NULL default '',
293 `frameworkcode` varchar(8) NOT NULL default '',
294 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
295 KEY `tab` (`authtypecode`,`tab`)
296 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
299 -- Table structure for table `auth_tag_structure`
302 DROP TABLE IF EXISTS `auth_tag_structure`;
303 CREATE TABLE `auth_tag_structure` (
304 `authtypecode` char(10) NOT NULL default '',
305 `tagfield` char(3) NOT NULL default '',
306 `liblibrarian` char(255) NOT NULL default '',
307 `libopac` char(255) NOT NULL default '',
308 `repeatable` tinyint(4) NOT NULL default 0,
309 `mandatory` tinyint(4) NOT NULL default 0,
310 `authorised_value` char(10) default NULL,
311 PRIMARY KEY (`authtypecode`,`tagfield`),
312 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
316 -- Table structure for table `auth_types`
319 DROP TABLE IF EXISTS `auth_types`;
320 CREATE TABLE `auth_types` (
321 `authtypecode` varchar(10) NOT NULL default '',
322 `authtypetext` varchar(255) NOT NULL default '',
323 `auth_tag_to_report` varchar(3) NOT NULL default '',
324 `summary` mediumtext NOT NULL,
325 PRIMARY KEY (`authtypecode`)
326 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
329 -- Table structure for table `authorised_values`
332 DROP TABLE IF EXISTS `authorised_values`;
333 CREATE TABLE `authorised_values` (
334 `id` int(11) NOT NULL auto_increment,
335 `category` char(10) NOT NULL default '',
336 `authorised_value` char(80) NOT NULL default '',
337 `lib` char(80) default NULL,
339 KEY `name` (`category`)
340 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
343 -- Table structure for table `biblio`
346 DROP TABLE IF EXISTS `biblio`;
347 CREATE TABLE `biblio` (
348 `biblionumber` int(11) NOT NULL default 0,
349 `frameworkcode` varchar(4) NOT NULL default '',
352 `unititle` mediumtext,
354 `serial` tinyint(1) default NULL,
355 `seriestitle` mediumtext,
356 `copyrightdate` smallint(6) default NULL,
357 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
358 `datecreated` DATE NOT NULL,
359 `abstract` mediumtext,
360 PRIMARY KEY (`biblionumber`),
361 KEY `blbnoidx` (`biblionumber`)
362 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
365 -- Table structure for table `biblio_framework`
368 DROP TABLE IF EXISTS `biblio_framework`;
369 CREATE TABLE `biblio_framework` (
370 `frameworkcode` char(4) NOT NULL default '',
371 `frameworktext` char(255) NOT NULL default '',
372 PRIMARY KEY (`frameworkcode`)
373 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
376 -- Table structure for table `biblioitems`
379 DROP TABLE IF EXISTS `biblioitems`;
380 CREATE TABLE `biblioitems` (
381 `biblioitemnumber` int(11) NOT NULL default 0,
382 `biblionumber` int(11) NOT NULL default 0,
385 `itemtype` varchar(10) default NULL,
386 `isbn` varchar(14) default NULL,
387 `issn` varchar(9) default NULL,
388 `publicationyear` text,
389 `publishercode` varchar(255) default NULL,
390 `volumedate` date default NULL,
392 `collectiontitle` mediumtext default NULL,
393 `collectionissn` text default NULL,
394 `collectionvolume` mediumtext default NULL,
395 `editionstatement` text default NULL,
396 `editionresponsibility` text default NULL,
397 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
398 `illus` varchar(255) default NULL,
399 `pages` varchar(255) default NULL,
401 `size` varchar(255) default NULL,
402 `place` varchar(255) default NULL,
403 `lccn` varchar(25) default NULL,
405 `url` varchar(255) default NULL,
406 `cn_source` varchar(10) default NULL,
407 `cn_class` varchar(30) default NULL,
408 `cn_item` varchar(10) default NULL,
409 `cn_suffix` varchar(10) default NULL,
410 `cn_sort` varchar(30) default NULL,
411 `totalissues` int(10),
412 `marcxml` longtext NOT NULL,
413 PRIMARY KEY (`biblioitemnumber`),
414 KEY `bibinoidx` (`biblioitemnumber`),
415 KEY `bibnoidx` (`biblionumber`),
417 KEY `publishercode` (`publishercode`),
418 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
419 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
422 -- Table structure for table `borrowers`
425 DROP TABLE IF EXISTS `borrowers`;
426 CREATE TABLE `borrowers` (
427 `borrowernumber` int(11) NOT NULL auto_increment,
428 `cardnumber` varchar(16) default NULL,
429 `surname` mediumtext NOT NULL,
432 `othernames` mediumtext,
434 `streetnumber` varchar(10) default NULL,
435 `streettype` varchar(50) default NULL,
436 `address` mediumtext NOT NULL,
438 `city` mediumtext NOT NULL,
439 `zipcode` varchar(25) default NULL,
442 `mobile` varchar(50) default NULL,
446 `B_streetnumber` varchar(10) default NULL,
447 `B_streettype` varchar(50) default NULL,
448 `B_address` varchar(100) default NULL,
450 `B_zipcode` varchar(25) default NULL,
452 `B_phone` mediumtext,
453 `dateofbirth` date default NULL,
454 `branchcode` char(10) NOT NULL default '',
455 `categorycode` varchar(10) NOT NULL default '',
456 `dateenrolled` date default NULL,
457 `dateexpiry` date default NULL,
458 `gonenoaddress` tinyint(1) default NULL,
459 `lost` tinyint(1) default NULL,
460 `debarred` tinyint(1) default NULL,
461 `contactname` mediumtext,
462 `contactfirstname` text,
464 `guarantorid` int(11) default NULL,
465 `borrowernotes` mediumtext,
466 `relationship` varchar(100) default NULL,
467 `ethnicity` varchar(50) default NULL,
468 `ethnotes` varchar(255) default NULL,
469 `sex` char(1) default NULL,
470 `password` varchar(30) default NULL,
471 `flags` int(11) default NULL,
472 `userid` varchar(30) default NULL,
473 `opacnote` mediumtext,
474 `contactnote` varchar(255) default NULL,
475 `sort1` varchar(80) default NULL,
476 `sort2` varchar(80) default NULL,
477 UNIQUE KEY `cardnumber` (`cardnumber`),
478 KEY `borrowernumber` (`borrowernumber`),
479 KEY `categorycode` (`categorycode`),
480 KEY `branchcode` (`branchcode`),
481 KEY `userid` (`userid`),
482 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
483 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
484 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
487 -- Table structure for table `branchcategories`
490 DROP TABLE IF EXISTS `branchcategories`;
491 CREATE TABLE `branchcategories` (
492 `categorycode` varchar(4) NOT NULL default '',
493 `categoryname` mediumtext,
494 `codedescription` mediumtext,
495 PRIMARY KEY (`categorycode`)
496 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
499 -- Table structure for table `branches`
502 DROP TABLE IF EXISTS `branches`;
503 CREATE TABLE `branches` (
504 `branchcode` char(10) NOT NULL default '',
505 `branchname` mediumtext NOT NULL,
506 `branchaddress1` mediumtext,
507 `branchaddress2` mediumtext,
508 `branchaddress3` mediumtext,
509 `branchphone` mediumtext,
510 `branchfax` mediumtext,
511 `branchemail` mediumtext,
512 `issuing` tinyint(4) default NULL,
513 `branchip` varchar(15) default NULL,
514 `branchprinter` varchar(100) default NULL,
515 UNIQUE KEY `branchcode` (`branchcode`)
516 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
519 -- Table structure for table `branchrelations`
522 DROP TABLE IF EXISTS `branchrelations`;
523 CREATE TABLE `branchrelations` (
524 `branchcode` char(10) NOT NULL default '',
525 `categorycode` varchar(4) NOT NULL default '',
526 PRIMARY KEY (`branchcode`,`categorycode`),
527 KEY `branchcode` (`branchcode`),
528 KEY `categorycode` (`categorycode`),
529 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
530 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
531 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
534 -- Table structure for table `branchtransfers`
537 DROP TABLE IF EXISTS `branchtransfers`;
538 CREATE TABLE `branchtransfers` (
539 `itemnumber` int(11) NOT NULL default 0,
540 `datesent` datetime default NULL,
541 `frombranch` varchar(10) NOT NULL default '',
542 `datearrived` datetime default NULL,
543 `tobranch` varchar(10) NOT NULL default '',
544 `comments` mediumtext,
545 KEY `frombranch` (`frombranch`),
546 KEY `tobranch` (`tobranch`),
547 KEY `itemnumber` (`itemnumber`),
548 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
549 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
550 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
551 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
555 -- Table structure for table `browser`
557 DROP TABLE IF EXISTS `browser`;
558 CREATE TABLE `browser` (
559 `level` int(11) NOT NULL,
560 `classification` varchar(20) NOT NULL,
561 `description` varchar(255) NOT NULL,
562 `number` bigint(20) NOT NULL,
563 `endnode` tinyint(4) NOT NULL
564 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
567 -- Table structure for table `categories`
570 DROP TABLE IF EXISTS `categories`;
571 CREATE TABLE `categories` (
572 `categorycode` varchar(10) NOT NULL default '',
573 `description` mediumtext,
574 `enrolmentperiod` smallint(6) default NULL,
575 `upperagelimit` smallint(6) default NULL,
576 `dateofbirthrequired` tinyint(1) default NULL,
577 `finetype` varchar(30) default NULL,
578 `bulk` tinyint(1) default NULL,
579 `enrolmentfee` decimal(28,6) default NULL,
580 `overduenoticerequired` tinyint(1) default NULL,
581 `issuelimit` smallint(6) default NULL,
582 `reservefee` decimal(28,6) default NULL,
583 `category_type` char(1) NOT NULL default 'A',
584 PRIMARY KEY (`categorycode`),
585 UNIQUE KEY `categorycode` (`categorycode`)
586 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
589 -- Table structure for table `categorytable`
592 DROP TABLE IF EXISTS `categorytable`;
593 CREATE TABLE `categorytable` (
594 `categorycode` varchar(5) NOT NULL default '',
596 `itemtypecodes` text,
597 PRIMARY KEY (`categorycode`)
598 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
601 -- Table structure for table `cities`
604 DROP TABLE IF EXISTS `cities`;
605 CREATE TABLE `cities` (
606 `cityid` int(11) NOT NULL auto_increment,
607 `city_name` char(100) NOT NULL default '',
608 `city_zipcode` char(20) default NULL,
609 PRIMARY KEY (`cityid`)
610 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
613 -- Table structure for table `currency`
616 DROP TABLE IF EXISTS `currency`;
617 CREATE TABLE `currency` (
618 `currency` varchar(10) NOT NULL default '',
619 `rate` float(7,5) default NULL,
620 PRIMARY KEY (`currency`)
621 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
624 -- Table structure for table `deletedbiblio`
627 DROP TABLE IF EXISTS `deletedbiblio`;
628 CREATE TABLE `deletedbiblio` (
629 `biblionumber` int(11) NOT NULL default 0,
630 `frameworkcode` varchar(4) NOT NULL default '',
633 `unititle` mediumtext,
635 `serial` tinyint(1) default NULL,
636 `seriestitle` mediumtext,
637 `copyrightdate` smallint(6) default NULL,
638 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
639 `datecreated` DATE NOT NULL,
640 `abstract` mediumtext,
641 PRIMARY KEY (`biblionumber`),
642 KEY `blbnoidx` (`biblionumber`)
643 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
646 -- Table structure for table `deletedbiblioitems`
649 DROP TABLE IF EXISTS `deletedbiblioitems`;
650 CREATE TABLE `deletedbiblioitems` (
651 `biblioitemnumber` int(11) NOT NULL default 0,
652 `biblionumber` int(11) NOT NULL default 0,
655 `itemtype` varchar(10) default NULL,
656 `isbn` varchar(14) default NULL,
657 `issn` varchar(9) default NULL,
658 `publicationyear` text,
659 `publishercode` varchar(255) default NULL,
660 `volumedate` date default NULL,
662 `collectiontitle` mediumtext default NULL,
663 `collectionissn` text default NULL,
664 `collectionvolume` mediumtext default NULL,
665 `editionstatement` text default NULL,
666 `editionresponsibility` text default NULL,
667 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
668 `illus` varchar(255) default NULL,
669 `pages` varchar(255) default NULL,
671 `size` varchar(255) default NULL,
672 `place` varchar(255) default NULL,
673 `lccn` varchar(25) default NULL,
675 `url` varchar(255) default NULL,
676 `cn_source` varchar(10) default NULL,
677 `cn_class` varchar(30) default NULL,
678 `cn_item` varchar(10) default NULL,
679 `cn_suffix` varchar(10) default NULL,
680 `cn_sort` varchar(30) default NULL,
681 `totalissues` int(10),
682 `marcxml` longtext NOT NULL,
683 PRIMARY KEY (`biblioitemnumber`),
684 KEY `bibinoidx` (`biblioitemnumber`),
685 KEY `bibnoidx` (`biblionumber`),
687 KEY `publishercode` (`publishercode`)
688 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
691 -- Table structure for table `deletedborrowers`
694 DROP TABLE IF EXISTS `deletedborrowers`;
695 CREATE TABLE `deletedborrowers` (
696 `borrowernumber` int(11) NOT NULL default 0,
697 `cardnumber` varchar(9) NOT NULL default '',
698 `surname` mediumtext NOT NULL,
701 `othernames` mediumtext,
703 `streetnumber` varchar(10) default NULL,
704 `streettype` varchar(50) default NULL,
705 `address` mediumtext NOT NULL,
707 `city` mediumtext NOT NULL,
708 `zipcode` varchar(25) default NULL,
711 `mobile` varchar(50) default NULL,
715 `B_streetnumber` varchar(10) default NULL,
716 `B_streettype` varchar(50) default NULL,
717 `B_address` varchar(100) default NULL,
719 `B_zipcode` varchar(25) default NULL,
721 `B_phone` mediumtext,
722 `dateofbirth` date default NULL,
723 `branchcode` char(10) NOT NULL default '',
724 `categorycode` varchar(2) default NULL,
725 `dateenrolled` date default NULL,
726 `dateexpiry` date default NULL,
727 `gonenoaddress` tinyint(1) default NULL,
728 `lost` tinyint(1) default NULL,
729 `debarred` tinyint(1) default NULL,
730 `contactname` mediumtext,
731 `contactfirstname` text,
733 `guarantorid` int(11) default NULL,
734 `borrowernotes` mediumtext,
735 `relationship` varchar(100) default NULL,
736 `ethnicity` varchar(50) default NULL,
737 `ethnotes` varchar(255) default NULL,
738 `sex` char(1) default NULL,
739 `password` varchar(30) default NULL,
740 `flags` int(11) default NULL,
741 `userid` varchar(30) default NULL,
742 `opacnote` mediumtext,
743 `contactnote` varchar(255) default NULL,
744 `sort1` varchar(80) default NULL,
745 `sort2` varchar(80) default NULL,
746 KEY `borrowernumber` (`borrowernumber`),
747 KEY `cardnumber` (`cardnumber`)
748 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
751 -- Table structure for table `deleteditems`
754 DROP TABLE IF EXISTS `deleteditems`;
755 CREATE TABLE `deleteditems` (
756 `itemnumber` int(11) NOT NULL default 0,
757 `biblionumber` int(11) NOT NULL default 0,
758 `biblioitemnumber` int(11) NOT NULL default 0,
759 `barcode` varchar(20) default NULL,
760 `dateaccessioned` date default NULL,
761 `booksellerid` varchar(10) default NULL,
762 `homebranch` varchar(4) default NULL,
763 `price` decimal(8,2) default NULL,
764 `replacementprice` decimal(8,2) default NULL,
765 `replacementpricedate` date default NULL,
766 `datelastborrowed` date default NULL,
767 `datelastseen` date default NULL,
768 `stack` tinyint(1) default NULL,
769 `notforloan` tinyint(1) default NULL,
770 `damaged` tinyint(1) default NULL,
771 `itemlost` tinyint(1) default NULL,
772 `wthdrawn` tinyint(1) default NULL,
773 `itemcallnumber` varchar(30) default NULL,
774 `issues` smallint(6) default NULL,
775 `renewals` smallint(6) default NULL,
776 `reserves` smallint(6) default NULL,
777 `restricted` tinyint(1) default NULL,
778 `itemnotes` mediumtext,
779 `holdingbranch` varchar(10) default NULL,
780 `paidfor` mediumtext,
781 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
782 `location` varchar(80) default NULL,
783 `onloan` date default NULL,
784 `cn_source` varchar(10) default NULL,
785 `cn_sort` varchar(30) default NULL,
786 `ccode` varchar(10) default NULL,
787 `materials` varchar(10) default NULL,
788 `uri` varchar(255) default NULL,
790 PRIMARY KEY (`itemnumber`),
791 UNIQUE KEY `delitembarcodeidx` (`barcode`),
792 KEY `delitembinoidx` (`biblioitemnumber`),
793 KEY `delitembibnoidx` (`biblionumber`),
794 KEY `delhomebranch` (`homebranch`),
795 KEY `delholdingbranch` (`holdingbranch`)
796 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
799 -- Table structure for table `ethnicity`
802 DROP TABLE IF EXISTS `ethnicity`;
803 CREATE TABLE `ethnicity` (
804 `code` varchar(10) NOT NULL default '',
805 `name` varchar(255) default NULL,
807 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
810 -- Table structure for table `issues`
813 DROP TABLE IF EXISTS `issues`;
814 CREATE TABLE `issues` (
815 `borrowernumber` int(11) default NULL,
816 `itemnumber` int(11) default NULL,
817 `date_due` date default NULL,
818 `branchcode` char(10) default NULL,
819 `issuingbranch` varchar(18) default NULL,
820 `returndate` date default NULL,
821 `lastreneweddate` date default NULL,
822 `return` varchar(4) default NULL,
823 `renewals` tinyint(4) default NULL,
824 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
825 `issuedate` date default NULL,
826 KEY `issuesborridx` (`borrowernumber`),
827 KEY `issuesitemidx` (`itemnumber`),
828 KEY `bordate` (`borrowernumber`,`timestamp`),
829 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
830 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
831 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
834 -- Table structure for table `issuingrules`
837 DROP TABLE IF EXISTS `issuingrules`;
838 CREATE TABLE `issuingrules` (
839 `categorycode` varchar(10) NOT NULL default '',
840 `itemtype` varchar(10) NOT NULL default '',
841 `restrictedtype` tinyint(1) default NULL,
842 `rentaldiscount` decimal(28,6) default NULL,
843 `reservecharge` decimal(28,6) default NULL,
844 `fine` decimal(28,6) default NULL,
845 `firstremind` int(11) default NULL,
846 `chargeperiod` int(11) default NULL,
847 `accountsent` int(11) default NULL,
848 `chargename` varchar(100) default NULL,
849 `maxissueqty` int(4) default NULL,
850 `issuelength` int(4) default NULL,
851 `branchcode` char(10) NOT NULL default '',
852 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
853 KEY `categorycode` (`categorycode`),
854 KEY `itemtype` (`itemtype`)
855 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
858 -- Table structure for table `items`
861 DROP TABLE IF EXISTS `items`;
862 CREATE TABLE `items` (
863 `itemnumber` int(11) NOT NULL default 0,
864 `biblionumber` int(11) NOT NULL default 0,
865 `biblioitemnumber` int(11) NOT NULL default 0,
866 `barcode` varchar(20) default NULL,
867 `dateaccessioned` date default NULL,
868 `booksellerid` varchar(10) default NULL,
869 `homebranch` varchar(4) default NULL,
870 `price` decimal(8,2) default NULL,
871 `replacementprice` decimal(8,2) default NULL,
872 `replacementpricedate` date default NULL,
873 `datelastborrowed` date default NULL,
874 `datelastseen` date default NULL,
875 `stack` tinyint(1) default NULL,
876 `notforloan` tinyint(1) default NULL,
877 `damaged` tinyint(1) default NULL,
878 `itemlost` tinyint(1) default NULL,
879 `wthdrawn` tinyint(1) default NULL,
880 `itemcallnumber` varchar(30) default NULL,
881 `issues` smallint(6) default NULL,
882 `renewals` smallint(6) default NULL,
883 `reserves` smallint(6) default NULL,
884 `restricted` tinyint(1) default NULL,
885 `itemnotes` mediumtext,
886 `holdingbranch` varchar(10) default NULL,
887 `paidfor` mediumtext,
888 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
889 `location` varchar(80) default NULL,
890 `onloan` date default NULL,
891 `cn_source` varchar(10) default NULL,
892 `cn_sort` varchar(30) default NULL,
893 `ccode` varchar(10) default NULL,
894 `materials` varchar(10) default NULL,
895 `uri` varchar(255) default NULL,
896 PRIMARY KEY (`itemnumber`),
897 UNIQUE KEY `itembarcodeidx` (`barcode`),
898 KEY `itembinoidx` (`biblioitemnumber`),
899 KEY `itembibnoidx` (`biblionumber`),
900 KEY `homebranch` (`homebranch`),
901 KEY `holdingbranch` (`holdingbranch`),
902 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
903 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
904 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
905 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
908 -- Table structure for table `itemtypes`
911 DROP TABLE IF EXISTS `itemtypes`;
912 CREATE TABLE `itemtypes` (
913 `itemtype` varchar(10) NOT NULL default '',
914 `description` mediumtext,
915 `renewalsallowed` smallint(6) default NULL,
916 `rentalcharge` double(16,4) default NULL,
917 `notforloan` smallint(6) default NULL,
918 `imageurl` varchar(200) default NULL,
920 PRIMARY KEY (`itemtype`),
921 UNIQUE KEY `itemtype` (`itemtype`)
922 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
925 -- Table structure for table `labels`
928 DROP TABLE IF EXISTS `labels`;
929 CREATE TABLE `labels` (
930 `labelid` int(11) NOT NULL auto_increment,
931 `batch_id` varchar(10) NOT NULL default '1',
932 `itemnumber` varchar(100) NOT NULL default '',
933 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
934 PRIMARY KEY (`labelid`)
935 ) ENGINE=MyISAM AUTO_INCREMENT=12143 DEFAULT CHARSET=utf8;
938 -- Table structure for table `labels_conf`
941 DROP TABLE IF EXISTS `labels_conf`;
942 CREATE TABLE `labels_conf` (
943 `id` int(4) NOT NULL auto_increment,
944 `barcodetype` char(100) default '',
945 `title` int(1) default '0',
946 `itemtype` int(1) default '0',
947 `barcode` int(1) default '0',
948 `dewey` int(1) default '0',
949 `class` int(1) default '0',
950 `subclass` int(1) default '0',
951 `itemcallnumber` int(1) default '0',
952 `author` int(1) default '0',
953 `issn` int(1) default '0',
954 `isbn` int(1) default '0',
955 `startlabel` int(2) NOT NULL default '1',
956 `printingtype` char(32) default 'BAR',
957 `layoutname` char(20) NOT NULL default 'TEST',
958 `guidebox` int(1) default '0',
959 `active` tinyint(1) default '1',
960 `fonttype` char(10) default NULL,
961 `subtitle` int(1) default NULL,
963 ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
966 -- Table structure for table `labels_templates`
969 DROP TABLE IF EXISTS `labels_templates`;
970 CREATE TABLE `labels_templates` (
971 `tmpl_id` int(4) NOT NULL auto_increment,
972 `tmpl_code` char(100) character set utf8 collate utf8_unicode_ci default '',
973 `tmpl_desc` char(100) character set utf8 collate utf8_unicode_ci default '',
974 `page_width` float default '0',
975 `page_height` float default '0',
976 `label_width` float default '0',
977 `label_height` float default '0',
978 `topmargin` float default '0',
979 `leftmargin` float default '0',
980 `cols` int(2) default '0',
981 `rows` int(2) default '0',
982 `colgap` float default '0',
983 `rowgap` float default '0',
984 `active` int(1) default NULL,
985 `units` char(20) character set utf8 collate utf8_unicode_ci default 'PX',
986 `fontsize` int(4) NOT NULL default '3',
987 PRIMARY KEY (`tmpl_id`)
988 ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
991 -- Table structure for table `letter`
994 DROP TABLE IF EXISTS `letter`;
995 CREATE TABLE `letter` (
996 `module` varchar(20) NOT NULL default '',
997 `code` varchar(20) NOT NULL default '',
998 `name` varchar(100) NOT NULL default '',
999 `title` varchar(200) NOT NULL default '',
1001 PRIMARY KEY (`module`,`code`)
1002 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1006 -- Table structure for table `marc_breeding`
1009 DROP TABLE IF EXISTS `marc_breeding`;
1010 CREATE TABLE `marc_breeding` (
1011 `id` bigint(20) NOT NULL auto_increment,
1012 `file` varchar(80) NOT NULL default '',
1013 `isbn` varchar(10) NOT NULL default '',
1014 `title` varchar(128) default NULL,
1015 `author` varchar(80) default NULL,
1017 `encoding` varchar(40) NOT NULL default '',
1018 `z3950random` varchar(40) default NULL,
1020 KEY `title` (`title`),
1022 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1025 -- Table structure for table `marc_subfield_structure`
1028 DROP TABLE IF EXISTS `marc_subfield_structure`;
1029 CREATE TABLE `marc_subfield_structure` (
1030 `tagfield` varchar(3) NOT NULL default '',
1031 `tagsubfield` char(1) NOT NULL default '',
1032 `liblibrarian` varchar(255) NOT NULL default '',
1033 `libopac` varchar(255) NOT NULL default '',
1034 `repeatable` tinyint(4) NOT NULL default 0,
1035 `mandatory` tinyint(4) NOT NULL default 0,
1036 `kohafield` varchar(40) default NULL,
1037 `tab` tinyint(1) default NULL,
1038 `authorised_value` varchar(10) default NULL,
1039 `authtypecode` varchar(10) default NULL,
1040 `value_builder` varchar(80) default NULL,
1041 `isurl` tinyint(1) default NULL,
1042 `hidden` tinyint(1) default NULL,
1043 `frameworkcode` varchar(4) NOT NULL default '',
1044 `seealso` varchar(255) default NULL,
1045 `link` varchar(80) default NULL,
1046 `defaultvalue` text default NULL,
1047 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1048 KEY `kohafield_2` (`kohafield`),
1049 KEY `tab` (`frameworkcode`,`tab`),
1050 KEY `kohafield` (`frameworkcode`,`kohafield`)
1051 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1054 -- Table structure for table `marc_tag_structure`
1057 DROP TABLE IF EXISTS `marc_tag_structure`;
1058 CREATE TABLE `marc_tag_structure` (
1059 `tagfield` char(3) NOT NULL default '',
1060 `liblibrarian` char(255) NOT NULL default '',
1061 `libopac` char(255) NOT NULL default '',
1062 `repeatable` tinyint(4) NOT NULL default 0,
1063 `mandatory` tinyint(4) NOT NULL default 0,
1064 `authorised_value` char(10) default NULL,
1065 `frameworkcode` char(4) NOT NULL default '',
1066 PRIMARY KEY (`frameworkcode`,`tagfield`)
1067 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1070 -- Table structure for table `mediatypetable`
1073 DROP TABLE IF EXISTS `mediatypetable`;
1074 CREATE TABLE `mediatypetable` (
1075 `mediatypecode` varchar(5) NOT NULL default '',
1077 `itemtypecodes` text,
1078 PRIMARY KEY (`mediatypecode`)
1079 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1082 -- Table structure for table `notifys`
1085 DROP TABLE IF EXISTS `notifys`;
1086 CREATE TABLE `notifys` (
1087 `notify_id` int(11) NOT NULL default 0,
1088 `borrowernumber` int(11) NOT NULL default 0,
1089 `itemnumber` int(11) NOT NULL default 0,
1090 `notify_date` date default NULL,
1091 `notify_send_date` date default NULL,
1092 `notify_level` int(1) NOT NULL default 0,
1093 `method` varchar(20) NOT NULL default ''
1094 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1097 -- Table structure for table `nozebra`
1099 CREATE TABLE `nozebra` (
1100 `server` varchar(20) NOT NULL,
1101 `indexname` varchar(40) NOT NULL,
1102 `value` varchar(250) NOT NULL,
1103 `biblionumbers` longtext NOT NULL,
1104 KEY `indexname` (`server`,`indexname`),
1105 KEY `value` (`server`,`value`))
1106 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1109 -- Table structure for table `opac_news`
1112 DROP TABLE IF EXISTS `opac_news`;
1113 CREATE TABLE `opac_news` (
1114 `idnew` int(10) unsigned NOT NULL auto_increment,
1115 `title` varchar(250) NOT NULL default '',
1116 `new` text NOT NULL,
1117 `lang` varchar(4) NOT NULL default '',
1118 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1119 `expirationdate` date default NULL,
1120 `number` int(11) default NULL,
1121 PRIMARY KEY (`idnew`)
1122 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1125 -- Table structure for table `overduerules`
1128 DROP TABLE IF EXISTS `overduerules`;
1129 CREATE TABLE `overduerules` (
1130 `branchcode` char(10) NOT NULL default '',
1131 `categorycode` varchar(2) NOT NULL default '',
1132 `delay1` int(4) default 0,
1133 `letter1` varchar(20) default NULL,
1134 `debarred1` char(1) default 0,
1135 `delay2` int(4) default 0,
1136 `debarred2` char(1) default 0,
1137 `letter2` varchar(20) default NULL,
1138 `delay3` int(4) default 0,
1139 `letter3` varchar(20) default NULL,
1140 `debarred3` int(1) default 0,
1141 PRIMARY KEY (`branchcode`,`categorycode`)
1142 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1145 -- Table structure for table `printers`
1148 DROP TABLE IF EXISTS `printers`;
1149 CREATE TABLE `printers` (
1150 `printername` char(40) NOT NULL default '',
1151 `printqueue` char(20) default NULL,
1152 `printtype` char(20) default NULL,
1153 PRIMARY KEY (`printername`)
1154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1157 -- Table structure for table `repeatable_holidays`
1160 DROP TABLE IF EXISTS `repeatable_holidays`;
1161 CREATE TABLE `repeatable_holidays` (
1162 `id` int(11) NOT NULL auto_increment,
1163 `branchcode` char(10) NOT NULL default '',
1164 `weekday` smallint(6) default NULL,
1165 `day` smallint(6) default NULL,
1166 `month` smallint(6) default NULL,
1167 `title` varchar(50) NOT NULL default '',
1168 `description` text NOT NULL,
1170 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1173 -- Table structure for table `reserveconstraints`
1176 DROP TABLE IF EXISTS `reserveconstraints`;
1177 CREATE TABLE `reserveconstraints` (
1178 `borrowernumber` int(11) NOT NULL default 0,
1179 `reservedate` date default NULL,
1180 `biblionumber` int(11) NOT NULL default 0,
1181 `biblioitemnumber` int(11) default NULL,
1182 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1186 -- Table structure for table `reserves`
1189 DROP TABLE IF EXISTS `reserves`;
1190 CREATE TABLE `reserves` (
1191 `borrowernumber` int(11) NOT NULL default 0,
1192 `reservedate` date default NULL,
1193 `biblionumber` int(11) NOT NULL default 0,
1194 `constrainttype` char(1) default NULL,
1195 `branchcode` char(10) default NULL,
1196 `notificationdate` date default NULL,
1197 `reminderdate` date default NULL,
1198 `cancellationdate` date default NULL,
1199 `reservenotes` mediumtext,
1200 `priority` smallint(6) default NULL,
1201 `found` char(1) default NULL,
1202 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1203 `itemnumber` int(11) default NULL,
1204 `waitingdate` date default NULL,
1205 KEY `borrowernumber` (`borrowernumber`),
1206 KEY `biblionumber` (`biblionumber`),
1207 KEY `itemnumber` (`itemnumber`),
1208 KEY `branchcode` (`branchcode`),
1209 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1210 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1211 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1212 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1216 -- Table structure for table `reviews`
1219 DROP TABLE IF EXISTS `reviews`;
1220 CREATE TABLE `reviews` (
1221 `reviewid` int(11) NOT NULL auto_increment,
1222 `borrowernumber` int(11) default NULL,
1223 `biblionumber` int(11) default NULL,
1225 `approved` tinyint(4) default NULL,
1226 `datereviewed` datetime default NULL,
1227 PRIMARY KEY (`reviewid`)
1228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1231 -- Table structure for table `roadtype`
1234 DROP TABLE IF EXISTS `roadtype`;
1235 CREATE TABLE `roadtype` (
1236 `roadtypeid` int(11) NOT NULL auto_increment,
1237 `road_type` char(100) NOT NULL default '',
1238 PRIMARY KEY (`roadtypeid`)
1239 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1242 -- Table structure for table `serial`
1245 DROP TABLE IF EXISTS `serial`;
1246 CREATE TABLE `serial` (
1247 `serialid` int(11) NOT NULL auto_increment,
1248 `biblionumber` varchar(100) NOT NULL default '',
1249 `subscriptionid` varchar(100) NOT NULL default '',
1250 `serialseq` varchar(100) NOT NULL default '',
1251 `status` tinyint(4) NOT NULL default 0,
1252 `planneddate` date default NULL,
1254 `publisheddate` date default NULL,
1256 `claimdate` date default NULL,
1257 `routingnotes` text,
1258 PRIMARY KEY (`serialid`)
1259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1262 -- Table structure for table `sessions`
1265 DROP TABLE IF EXISTS sessions;
1266 CREATE TABLE sessions (
1267 `id` char(32) NOT NULL,
1268 `a_session` text NOT NULL,
1270 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1273 -- Table structure for table `special_holidays`
1276 DROP TABLE IF EXISTS `special_holidays`;
1277 CREATE TABLE `special_holidays` (
1278 `id` int(11) NOT NULL auto_increment,
1279 `branchcode` char(10) NOT NULL default '',
1280 `day` smallint(6) NOT NULL default 0,
1281 `month` smallint(6) NOT NULL default 0,
1282 `year` smallint(6) NOT NULL default 0,
1283 `isexception` smallint(1) NOT NULL default '1',
1284 `title` varchar(50) NOT NULL default '',
1285 `description` text NOT NULL,
1287 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1290 -- Table structure for table `statistics`
1293 DROP TABLE IF EXISTS `statistics`;
1294 CREATE TABLE `statistics` (
1295 `datetime` datetime default NULL,
1296 `branch` varchar(10) default NULL,
1297 `proccode` varchar(4) default NULL,
1298 `value` double(16,4) default NULL,
1299 `type` varchar(16) default NULL,
1301 `usercode` varchar(10) default NULL,
1302 `itemnumber` int(11) default NULL,
1303 `itemtype` varchar(10) default NULL,
1304 `borrowernumber` int(11) default NULL,
1305 `associatedborrower` int(11) default NULL,
1306 KEY `timeidx` (`datetime`)
1307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1310 -- Table structure for table `stopwords`
1313 DROP TABLE IF EXISTS `stopwords`;
1314 CREATE TABLE `stopwords` (
1315 `word` varchar(255) default NULL
1316 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1319 -- Table structure for table `subcategorytable`
1322 DROP TABLE IF EXISTS `subcategorytable`;
1323 CREATE TABLE `subcategorytable` (
1324 `subcategorycode` varchar(5) NOT NULL default '',
1326 `itemtypecodes` text,
1327 PRIMARY KEY (`subcategorycode`)
1328 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1331 -- Table structure for table `subscription`
1334 DROP TABLE IF EXISTS `subscription`;
1335 CREATE TABLE `subscription` (
1336 `biblionumber` int(11) NOT NULL default 0,
1337 `subscriptionid` int(11) NOT NULL auto_increment,
1338 `librarian` varchar(100) default '',
1339 `startdate` date default NULL,
1340 `aqbooksellerid` int(11) default 0,
1341 `cost` int(11) default 0,
1342 `aqbudgetid` int(11) default 0,
1343 `weeklength` tinyint(4) default 0,
1344 `monthlength` tinyint(4) default 0,
1345 `numberlength` tinyint(4) default 0,
1346 `periodicity` tinyint(4) default 0,
1347 `dow` varchar(100) default '',
1348 `numberingmethod` varchar(100) default '',
1350 `status` varchar(100) NOT NULL default '',
1351 `add1` int(11) default 0,
1352 `every1` int(11) default 0,
1353 `whenmorethan1` int(11) default 0,
1354 `setto1` int(11) default NULL,
1355 `lastvalue1` int(11) default NULL,
1356 `add2` int(11) default 0,
1357 `every2` int(11) default 0,
1358 `whenmorethan2` int(11) default 0,
1359 `setto2` int(11) default NULL,
1360 `lastvalue2` int(11) default NULL,
1361 `add3` int(11) default 0,
1362 `every3` int(11) default 0,
1363 `innerloop1` int(11) default 0,
1364 `innerloop2` int(11) default 0,
1365 `innerloop3` int(11) default 0,
1366 `whenmorethan3` int(11) default 0,
1367 `setto3` int(11) default NULL,
1368 `lastvalue3` int(11) default NULL,
1369 `issuesatonce` tinyint(3) NOT NULL default '1',
1370 `firstacquidate` date default NULL,
1371 `manualhistory` tinyint(1) NOT NULL default 0,
1372 `irregularity` text,
1373 `letter` varchar(20) default NULL,
1374 `numberpattern` tinyint(3) default 0,
1375 `distributedto` text,
1376 `internalnotes` longtext,
1378 `branchcode` char(10) NOT NULL default '',
1379 `hemisphere` tinyint(3) default 0,
1380 PRIMARY KEY (`subscriptionid`)
1381 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1384 -- Table structure for table `subscriptionhistory`
1387 DROP TABLE IF EXISTS `subscriptionhistory`;
1388 CREATE TABLE `subscriptionhistory` (
1389 `biblionumber` int(11) NOT NULL default 0,
1390 `subscriptionid` int(11) NOT NULL default 0,
1391 `histstartdate` date default NULL,
1392 `enddate` date default NULL,
1393 `missinglist` longtext NOT NULL,
1394 `recievedlist` longtext NOT NULL,
1395 `opacnote` varchar(150) NOT NULL default '',
1396 `librariannote` varchar(150) NOT NULL default '',
1397 PRIMARY KEY (`subscriptionid`),
1398 KEY `biblionumber` (`biblionumber`)
1399 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1402 -- Table structure for table `subscriptionroutinglist`
1405 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1406 CREATE TABLE `subscriptionroutinglist` (
1407 `routingid` int(11) NOT NULL auto_increment,
1408 `borrowernumber` int(11) default NULL,
1409 `ranking` int(11) default NULL,
1410 `subscriptionid` int(11) default NULL,
1411 PRIMARY KEY (`routingid`)
1412 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1415 -- Table structure for table `suggestions`
1418 DROP TABLE IF EXISTS `suggestions`;
1419 CREATE TABLE `suggestions` (
1420 `suggestionid` int(8) NOT NULL auto_increment,
1421 `suggestedby` int(11) NOT NULL default 0,
1422 `managedby` int(11) default NULL,
1423 `STATUS` varchar(10) NOT NULL default '',
1425 `author` varchar(80) default NULL,
1426 `title` varchar(80) default NULL,
1427 `copyrightdate` smallint(6) default NULL,
1428 `publishercode` varchar(255) default NULL,
1429 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1430 `volumedesc` varchar(255) default NULL,
1431 `publicationyear` smallint(6) default 0,
1432 `place` varchar(255) default NULL,
1433 `isbn` varchar(10) default NULL,
1434 `mailoverseeing` smallint(1) default 0,
1435 `biblionumber` int(11) default NULL,
1437 PRIMARY KEY (`suggestionid`),
1438 KEY `suggestedby` (`suggestedby`),
1439 KEY `managedby` (`managedby`)
1440 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1443 -- Table structure for table `systempreferences`
1446 DROP TABLE IF EXISTS `systempreferences`;
1447 CREATE TABLE `systempreferences` (
1448 `variable` varchar(50) NOT NULL default '',
1450 `options` mediumtext,
1452 `type` varchar(20) default NULL,
1453 PRIMARY KEY (`variable`)
1454 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1457 -- Table structure for table `tags`
1460 DROP TABLE IF EXISTS `tags`;
1461 CREATE TABLE `tags` (
1462 `entry` varchar(255) NOT NULL default '',
1463 `weight` bigint(20) NOT NULL default 0,
1464 PRIMARY KEY (`entry`)
1465 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1468 -- Table structure for table `userflags`
1471 DROP TABLE IF EXISTS `userflags`;
1472 CREATE TABLE `userflags` (
1473 `bit` int(11) NOT NULL default 0,
1474 `flag` char(30) default NULL,
1475 `flagdesc` char(255) default NULL,
1476 `defaulton` int(11) default NULL,
1478 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1481 -- Table structure for table `virtualshelves`
1484 DROP TABLE IF EXISTS `virtualshelves`;
1485 CREATE TABLE `virtualshelves` (
1486 `shelfnumber` int(11) NOT NULL auto_increment,
1487 `shelfname` char(255) default NULL,
1488 `owner` char(80) default NULL,
1489 `category` char(1) default NULL,
1490 PRIMARY KEY (`shelfnumber`)
1491 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1494 -- Table structure for table `virtualshelfcontents`
1497 DROP TABLE IF EXISTS `virtualshelfcontents`;
1498 CREATE TABLE `virtualshelfcontents` (
1499 `shelfnumber` int(11) NOT NULL default 0,
1500 `biblionumber` int(11) NOT NULL default 0,
1501 `flags` int(11) default NULL,
1502 `dateadded` timestamp NULL default NULL,
1503 KEY `shelfnumber` (`shelfnumber`),
1504 KEY `biblionumber` (`biblionumber`),
1505 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1506 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1510 -- Table structure for table `z3950servers`
1513 DROP TABLE IF EXISTS `z3950servers`;
1514 CREATE TABLE `z3950servers` (
1515 `host` varchar(255) default NULL,
1516 `port` int(11) default NULL,
1517 `db` varchar(255) default NULL,
1518 `userid` varchar(255) default NULL,
1519 `password` varchar(255) default NULL,
1521 `id` int(11) NOT NULL auto_increment,
1522 `checked` smallint(6) default NULL,
1523 `rank` int(11) default NULL,
1524 `syntax` varchar(80) default NULL,
1526 `position` enum('primary','secondary','') NOT NULL default 'primary',
1527 `type` enum('zed','opensearch') NOT NULL default 'zed',
1528 `description` text NOT NULL,
1530 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1533 -- Table structure for table `zebraqueue`
1536 DROP TABLE IF EXISTS `zebraqueue`;
1537 CREATE TABLE `zebraqueue` (
1538 `id` int(11) NOT NULL auto_increment,
1539 `biblio_auth_number` int(11) NOT NULL default 0,
1540 `operation` char(20) NOT NULL default '',
1541 `server` char(20) NOT NULL default '',
1543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1545 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1546 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1547 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1548 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1549 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1550 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1551 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1552 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;