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` varchar(5) NOT NULL default '',
110 `bookfundname` mediumtext,
111 `bookfundgroup` varchar(5) default NULL,
112 `branchcode` varchar(4) 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` varchar(5) 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` varchar(4) 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(4) default NULL,
190 `bookfundid` char(5) 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) NOT 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 `abstract` mediumtext,
359 PRIMARY KEY (`biblionumber`),
360 KEY `blbnoidx` (`biblionumber`)
361 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
364 -- Table structure for table `biblio_framework`
367 DROP TABLE IF EXISTS `biblio_framework`;
368 CREATE TABLE `biblio_framework` (
369 `frameworkcode` char(4) NOT NULL default '',
370 `frameworktext` char(255) NOT NULL default '',
371 PRIMARY KEY (`frameworkcode`)
372 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
375 -- Table structure for table `biblioitems`
378 DROP TABLE IF EXISTS `biblioitems`;
379 CREATE TABLE `biblioitems` (
380 `biblioitemnumber` int(11) NOT NULL default 0,
381 `biblionumber` int(11) NOT NULL default 0,
384 `classification` varchar(25) default NULL,
385 `itemtype` varchar(4) default NULL,
386 `isbn` varchar(14) default NULL,
387 `issn` varchar(9) default NULL,
388 `dewey` varchar(30) default '',
389 `subclass` varchar(3) default NULL,
390 `publicationyear` text,
391 `publishercode` varchar(255) default NULL,
392 `volumedate` date default NULL,
394 `collectiontitle` mediumtext NOT NULL,
395 `collectionissn` text NOT NULL,
396 `collectionvolume` mediumtext NOT NULL,
397 `editionstatement` text NOT NULL,
398 `editionreponsability` text NOT NULL,
399 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
400 `illus` varchar(255) default NULL,
401 `pages` varchar(255) default NULL,
403 `size` varchar(255) default NULL,
404 `place` varchar(255) default NULL,
405 `lccn` varchar(25) default NULL,
407 `url` varchar(255) default NULL,
408 `lcsort` varchar(25) default NULL,
409 `ccode` varchar(4) default NULL,
410 `marcxml` longtext NOT NULL,
411 PRIMARY KEY (`biblioitemnumber`),
412 KEY `bibinoidx` (`biblioitemnumber`),
413 KEY `bibnoidx` (`biblionumber`),
415 KEY `publishercode` (`publishercode`),
416 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
417 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
420 -- Table structure for table `borrowers`
423 DROP TABLE IF EXISTS `borrowers`;
424 CREATE TABLE `borrowers` (
425 `borrowernumber` int(11) NOT NULL auto_increment,
426 `cardnumber` varchar(16) default NULL,
427 `surname` mediumtext NOT NULL,
430 `othernames` mediumtext,
432 `streetnumber` varchar(10) default NULL,
433 `streettype` varchar(50) default NULL,
434 `address` mediumtext NOT NULL,
436 `city` mediumtext NOT NULL,
437 `zipcode` varchar(25) default NULL,
440 `mobile` varchar(50) default NULL,
444 `B_streetnumber` varchar(10) default NULL,
445 `B_streettype` varchar(50) default NULL,
446 `B_address` varchar(100) default NULL,
448 `B_zipcode` varchar(25) default NULL,
450 `B_phone` mediumtext,
451 `dateofbirth` date default NULL,
452 `branchcode` varchar(10) NOT NULL default '',
453 `categorycode` varchar(10) NOT NULL default '',
454 `dateenrolled` date default NULL,
455 `dateexpiry` date default NULL,
456 `gonenoaddress` tinyint(1) default NULL,
457 `lost` tinyint(1) default NULL,
458 `debarred` tinyint(1) default NULL,
459 `contactname` mediumtext,
460 `contactfirstname` text,
462 `guarantorid` int(11) default NULL,
463 `borrowernotes` mediumtext,
464 `relationship` varchar(100) default NULL,
465 `ethnicity` varchar(50) default NULL,
466 `ethnotes` varchar(255) default NULL,
467 `sex` char(1) default NULL,
468 `password` varchar(30) default NULL,
469 `flags` int(11) default NULL,
470 `userid` varchar(30) default NULL,
471 `opacnote` mediumtext,
472 `contactnote` varchar(255) default NULL,
473 `sort1` varchar(80) default NULL,
474 `sort2` varchar(80) default NULL,
475 UNIQUE KEY `cardnumber` (`cardnumber`),
476 KEY `borrowernumber` (`borrowernumber`),
477 KEY `categorycode` (`categorycode`),
478 KEY `branchcode` (`branchcode`),
479 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
480 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
481 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
484 -- Table structure for table `branchcategories`
487 DROP TABLE IF EXISTS `branchcategories`;
488 CREATE TABLE `branchcategories` (
489 `categorycode` varchar(4) NOT NULL default '',
490 `categoryname` mediumtext,
491 `codedescription` mediumtext,
492 PRIMARY KEY (`categorycode`)
493 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
496 -- Table structure for table `branches`
499 DROP TABLE IF EXISTS `branches`;
500 CREATE TABLE `branches` (
501 `branchcode` varchar(10) NOT NULL default '',
502 `branchname` mediumtext NOT NULL,
503 `branchaddress1` mediumtext,
504 `branchaddress2` mediumtext,
505 `branchaddress3` mediumtext,
506 `branchphone` mediumtext,
507 `branchfax` mediumtext,
508 `branchemail` mediumtext,
509 `issuing` tinyint(4) default NULL,
510 `branchip` varchar(15) default NULL,
511 `branchprinter` varchar(100) default NULL,
512 UNIQUE KEY `branchcode` (`branchcode`)
513 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
516 -- Table structure for table `branchrelations`
519 DROP TABLE IF EXISTS `branchrelations`;
520 CREATE TABLE `branchrelations` (
521 `branchcode` varchar(4) NOT NULL default '',
522 `categorycode` varchar(4) NOT NULL default '',
523 PRIMARY KEY (`branchcode`,`categorycode`),
524 KEY `branchcode` (`branchcode`),
525 KEY `categorycode` (`categorycode`),
526 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
527 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
531 -- Table structure for table `branchtransfers`
534 DROP TABLE IF EXISTS `branchtransfers`;
535 CREATE TABLE `branchtransfers` (
536 `itemnumber` int(11) NOT NULL default 0,
537 `datesent` datetime default NULL,
538 `frombranch` varchar(10) NOT NULL default '',
539 `datearrived` datetime default NULL,
540 `tobranch` varchar(10) NOT NULL default '',
541 `comments` mediumtext,
542 KEY `frombranch` (`frombranch`),
543 KEY `tobranch` (`tobranch`),
544 KEY `itemnumber` (`itemnumber`),
545 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
546 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
547 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
548 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
551 -- Table structure for table `categories`
554 DROP TABLE IF EXISTS `categories`;
555 CREATE TABLE `categories` (
556 `categorycode` varchar(10) NOT NULL default '',
557 `description` mediumtext,
558 `enrolmentperiod` smallint(6) default NULL,
559 `upperagelimit` smallint(6) default NULL,
560 `dateofbirthrequired` tinyint(1) default NULL,
561 `finetype` varchar(30) default NULL,
562 `bulk` tinyint(1) default NULL,
563 `enrolmentfee` decimal(28,6) default NULL,
564 `overduenoticerequired` tinyint(1) default NULL,
565 `issuelimit` smallint(6) default NULL,
566 `reservefee` decimal(28,6) default NULL,
567 `category_type` char(1) NOT NULL default 'A',
568 PRIMARY KEY (`categorycode`),
569 UNIQUE KEY `categorycode` (`categorycode`)
570 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
573 -- Table structure for table `categorytable`
576 DROP TABLE IF EXISTS `categorytable`;
577 CREATE TABLE `categorytable` (
578 `categorycode` varchar(5) NOT NULL default '',
580 `itemtypecodes` text,
581 PRIMARY KEY (`categorycode`)
582 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
585 -- Table structure for table `cities`
588 DROP TABLE IF EXISTS `cities`;
589 CREATE TABLE `cities` (
590 `cityid` int(11) NOT NULL auto_increment,
591 `city_name` char(100) NOT NULL default '',
592 `city_zipcode` char(20) default NULL,
593 PRIMARY KEY (`cityid`)
594 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
597 -- Table structure for table `currency`
600 DROP TABLE IF EXISTS `currency`;
601 CREATE TABLE `currency` (
602 `currency` varchar(10) NOT NULL default '',
603 `rate` float(7,5) default NULL,
604 PRIMARY KEY (`currency`)
605 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
608 -- Table structure for table `deletedbiblio`
611 DROP TABLE IF EXISTS `deletedbiblio`;
612 CREATE TABLE `deletedbiblio` (
613 `biblionumber` int(11) NOT NULL default 0,
614 `frameworkcode` varchar(4) NOT NULL,
617 `unititle` mediumtext,
619 `serial` tinyint(1) default NULL,
620 `seriestitle` mediumtext,
621 `copyrightdate` smallint(6) default NULL,
622 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
624 `abstract` mediumtext,
625 PRIMARY KEY (`biblionumber`),
626 KEY `blbnoidx` (`biblionumber`)
627 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
630 -- Table structure for table `deletedbiblioitems`
633 DROP TABLE IF EXISTS `deletedbiblioitems`;
634 CREATE TABLE `deletedbiblioitems` (
635 `biblioitemnumber` int(11) NOT NULL default 0,
636 `biblionumber` int(11) NOT NULL default 0,
639 `classification` varchar(25) default NULL,
640 `itemtype` varchar(4) default NULL,
641 `isbn` varchar(14) default NULL,
642 `issn` varchar(9) default NULL,
643 `dewey` double(8,6) default NULL,
644 `subclass` varchar(3) default NULL,
645 `publicationyear` smallint(6) default NULL,
646 `publishercode` varchar(255) default NULL,
647 `volumedate` date default NULL,
648 `volumeddesc` varchar(255) default NULL,
649 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
650 `illus` varchar(255) default NULL,
651 `pages` varchar(255) default NULL,
653 `size` varchar(255) default NULL,
654 `lccn` varchar(25) default NULL,
656 `url` varchar(255) default NULL,
657 `place` varchar(255) default NULL,
658 `lcsort` varchar(25) default NULL,
659 `ccode` varchar(4) default NULL,
660 `marcxml` longtext NOT NULL,
661 `collectiontitle` mediumtext,
662 `collectionissn` mediumtext,
663 `collectionvolume` mediumtext,
664 `editionstatement` text,
665 `editionresponsability` text,
666 PRIMARY KEY (`biblioitemnumber`),
667 KEY `bibinoidx` (`biblioitemnumber`),
668 KEY `bibnoidx` (`biblionumber`)
669 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
672 -- Table structure for table `deletedborrowers`
675 DROP TABLE IF EXISTS `deletedborrowers`;
676 CREATE TABLE `deletedborrowers` (
677 `borrowernumber` int(11) NOT NULL default 0,
678 `cardnumber` varchar(9) NOT NULL default '',
679 `surname` mediumtext NOT NULL,
682 `othernames` mediumtext,
684 `streetnumber` varchar(10) default NULL,
685 `streettype` varchar(50) default NULL,
686 `address` mediumtext NOT NULL,
688 `city` mediumtext NOT NULL,
689 `zipcode` varchar(25) default NULL,
692 `mobile` varchar(50) default NULL,
696 `B_streetnumber` varchar(10) default NULL,
697 `B_streettype` varchar(50) default NULL,
698 `B_address` varchar(100) default NULL,
700 `B_zipcode` varchar(25) default NULL,
702 `B_phone` mediumtext,
703 `dateofbirth` date default NULL,
704 `branchcode` varchar(4) NOT NULL default '',
705 `categorycode` varchar(2) default NULL,
706 `dateenrolled` date default NULL,
707 `dateexpiry` date default NULL,
708 `gonenoaddress` tinyint(1) default NULL,
709 `lost` tinyint(1) default NULL,
710 `debarred` tinyint(1) default NULL,
711 `contactname` mediumtext,
712 `contactfirstname` text,
714 `guarantorid` int(11) default NULL,
715 `borrowernotes` mediumtext,
716 `relationship` varchar(100) default NULL,
717 `ethnicity` varchar(50) default NULL,
718 `ethnotes` varchar(255) default NULL,
719 `sex` char(1) default NULL,
720 `password` varchar(30) default NULL,
721 `flags` int(11) default NULL,
722 `userid` varchar(30) default NULL,
723 `opacnote` mediumtext,
724 `contactnote` varchar(255) default NULL,
725 `sort1` varchar(80) default NULL,
726 `sort2` varchar(80) default NULL,
727 KEY `borrowernumber` (`borrowernumber`),
728 KEY `cardnumber` (`cardnumber`)
729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
732 -- Table structure for table `deleteditems`
735 DROP TABLE IF EXISTS `deleteditems`;
736 CREATE TABLE `deleteditems` (
737 `itemnumber` int(11) NOT NULL default 0,
738 `biblionumber` int(11) NOT NULL default 0,
739 `multivolumepart` varchar(30) default NULL,
740 `biblioitemnumber` int(11) NOT NULL default 0,
741 `barcode` varchar(9) NOT NULL default '',
742 `dateaccessioned` date default NULL,
743 `booksellerid` varchar(10) default NULL,
744 `homebranch` varchar(4) default NULL,
745 `price` decimal(28,6) default NULL,
746 `replacementprice` decimal(28,6) default NULL,
747 `replacementpricedate` date default NULL,
748 `datelastborrowed` date default NULL,
749 `datelastseen` date default NULL,
750 `multivolume` tinyint(1) default NULL,
751 `stack` tinyint(1) default NULL,
752 `notforloan` tinyint(1) default NULL,
753 `itemlost` tinyint(1) default NULL,
754 `wthdrawn` tinyint(1) default NULL,
755 `bulk` varchar(30) default NULL,
756 `issues` smallint(6) default NULL,
757 `renewals` smallint(6) default NULL,
758 `reserves` smallint(6) default NULL,
759 `restricted` tinyint(1) default NULL,
760 `binding` decimal(28,6) default NULL,
761 `itemnotes` mediumtext,
762 `holdingbranch` varchar(4) default NULL,
763 `interim` tinyint(1) default NULL,
764 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
766 `paidfor` mediumtext,
767 `location` varchar(80) default NULL,
768 `itemcallnumber` varchar(30) default NULL,
769 `onloan` date default NULL,
770 `cutterextra` varchar(45) default NULL,
771 `issue_date` date default NULL,
772 `itype` varchar(10) default NULL,
773 PRIMARY KEY (`itemnumber`),
774 UNIQUE KEY `barcode` (`barcode`),
775 KEY `itembarcodeidx` (`barcode`),
776 KEY `itembinoidx` (`biblioitemnumber`),
777 KEY `itembibnoidx` (`biblionumber`)
778 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
781 -- Table structure for table `ethnicity`
784 DROP TABLE IF EXISTS `ethnicity`;
785 CREATE TABLE `ethnicity` (
786 `code` varchar(10) NOT NULL default '',
787 `name` varchar(255) default NULL,
789 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
792 -- Table structure for table `issues`
795 DROP TABLE IF EXISTS `issues`;
796 CREATE TABLE `issues` (
797 `borrowernumber` int(11) default NULL,
798 `itemnumber` int(11) default NULL,
799 `date_due` date default NULL,
800 `branchcode` varchar(10) default NULL,
801 `issuingbranch` varchar(18) default NULL,
802 `returndate` date default NULL,
803 `lastreneweddate` date default NULL,
804 `return` varchar(4) default NULL,
805 `renewals` tinyint(4) default NULL,
806 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
807 `issuedate` date default NULL,
808 KEY `issuesborridx` (`borrowernumber`),
809 KEY `issuesitemidx` (`itemnumber`),
810 KEY `bordate` (`borrowernumber`,`timestamp`),
811 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
812 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
813 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
816 -- Table structure for table `issuingrules`
819 DROP TABLE IF EXISTS `issuingrules`;
820 CREATE TABLE `issuingrules` (
821 `categorycode` varchar(2) NOT NULL default '',
822 `itemtype` varchar(4) NOT NULL default '',
823 `restrictedtype` tinyint(1) default NULL,
824 `rentaldiscount` decimal(28,6) default NULL,
825 `reservecharge` decimal(28,6) default NULL,
826 `fine` decimal(28,6) default NULL,
827 `firstremind` int(11) default NULL,
828 `chargeperiod` int(11) default NULL,
829 `accountsent` int(11) default NULL,
830 `chargename` varchar(100) default NULL,
831 `maxissueqty` int(4) default NULL,
832 `issuelength` int(4) default NULL,
833 `branchcode` varchar(4) NOT NULL default '',
834 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
835 KEY `categorycode` (`categorycode`),
836 KEY `itemtype` (`itemtype`),
837 CONSTRAINT `issuingrules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE,
838 CONSTRAINT `issuingrules_ibfk_2` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
839 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
842 -- Table structure for table `items`
845 DROP TABLE IF EXISTS `items`;
846 CREATE TABLE `items` (
847 `itemnumber` int(11) NOT NULL default 0,
848 `biblionumber` int(11) NOT NULL default 0,
849 `multivolumepart` varchar(30) default NULL,
850 `biblioitemnumber` int(11) NOT NULL default 0,
851 `barcode` varchar(20) default NULL,
852 `dateaccessioned` date default NULL,
853 `booksellerid` varchar(10) default NULL,
854 `homebranch` varchar(4) default NULL,
855 `price` decimal(8,2) default NULL,
856 `replacementprice` decimal(8,2) default NULL,
857 `replacementpricedate` date default NULL,
858 `datelastborrowed` date default NULL,
859 `datelastseen` date default NULL,
860 `multivolume` tinyint(1) default NULL,
861 `stack` tinyint(1) default NULL,
862 `notforloan` tinyint(1) default NULL,
863 `itemlost` tinyint(1) default NULL,
864 `wthdrawn` tinyint(1) default NULL,
865 `itemcallnumber` varchar(30) default NULL,
866 `issues` smallint(6) default NULL,
867 `renewals` smallint(6) default NULL,
868 `reserves` smallint(6) default NULL,
869 `restricted` tinyint(1) default NULL,
870 `binding` decimal(28,6) default NULL,
871 `itemnotes` mediumtext,
872 `holdingbranch` varchar(10) default NULL,
873 `paidfor` mediumtext,
874 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
875 `location` varchar(80) default NULL,
876 `onloan` date default NULL,
877 `cutterextra` varchar(45) default NULL,
878 `issue_date` date default NULL,
879 `itype` varchar(10) default NULL,
880 PRIMARY KEY (`itemnumber`),
881 KEY `itembarcodeidx` (`barcode`),
882 KEY `itembinoidx` (`biblioitemnumber`),
883 KEY `itembibnoidx` (`biblionumber`),
884 KEY `homebranch` (`homebranch`),
885 KEY `holdingbranch` (`holdingbranch`),
886 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
887 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
888 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
889 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
892 -- Table structure for table `itemtypes`
895 DROP TABLE IF EXISTS `itemtypes`;
896 CREATE TABLE `itemtypes` (
897 `itemtype` varchar(10) NOT NULL default '',
898 `description` mediumtext,
899 `renewalsallowed` smallint(6) default NULL,
900 `rentalcharge` double(16,4) default NULL,
901 `notforloan` smallint(6) default NULL,
902 `imageurl` varchar(200) default NULL,
904 PRIMARY KEY (`itemtype`),
905 UNIQUE KEY `itemtype` (`itemtype`)
906 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
909 -- Table structure for table `labels`
912 DROP TABLE IF EXISTS `labels`;
913 CREATE TABLE `labels` (
914 `labelid` int(11) NOT NULL auto_increment,
915 `batch_id` varchar(10) NOT NULL,
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,
938 `subclass` tinyint(1) default '0',
939 `itemcallnumber` tinyint(1) default '0',
940 `printingtype` char(10) default NULL,
941 `guidebox` tinyint(1) default NULL,
943 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
946 -- Table structure for table `labels_templates`
949 DROP TABLE IF EXISTS `labels_templates`;
950 CREATE TABLE `labels_templates` (
951 `tmpl_id` int(4) NOT NULL auto_increment,
952 `tmpl_code` char(100) default '',
953 `tmpl_desc` char(100) default '',
954 `page_width` float default '0',
955 `page_height` float default '0',
956 `label_width` float default '0',
957 `label_height` float default '0',
958 `topmargin` float default '0',
959 `leftmargin` float default '0',
960 `cols` int(2) default '0',
961 `rows` int(2) default '0',
962 `colgap` float default '0',
963 `rowgap` float default '0',
964 `active` int(1) default NULL,
965 `units` char(20) default 'PX',
966 `fontsize` int(4) default NULL,
967 PRIMARY KEY (`tmpl_id`)
968 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
972 -- Table structure for table `letter`
975 DROP TABLE IF EXISTS `letter`;
976 CREATE TABLE `letter` (
977 `module` varchar(20) NOT NULL default '',
978 `code` varchar(20) NOT NULL default '',
979 `name` varchar(100) NOT NULL default '',
980 `title` varchar(200) NOT NULL default '',
982 PRIMARY KEY (`module`,`code`)
983 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
987 -- Table structure for table `marc_breeding`
990 DROP TABLE IF EXISTS `marc_breeding`;
991 CREATE TABLE `marc_breeding` (
992 `id` bigint(20) NOT NULL auto_increment,
993 `file` varchar(80) NOT NULL default '',
994 `isbn` varchar(10) NOT NULL default '',
995 `title` varchar(128) default NULL,
996 `author` varchar(80) default NULL,
998 `encoding` varchar(40) NOT NULL default '',
999 `z3950random` varchar(40) default NULL,
1001 KEY `title` (`title`),
1003 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1006 -- Table structure for table `marc_subfield_structure`
1009 DROP TABLE IF EXISTS `marc_subfield_structure`;
1010 CREATE TABLE `marc_subfield_structure` (
1011 `tagfield` varchar(3) NOT NULL default '',
1012 `tagsubfield` char(1) NOT NULL default '',
1013 `liblibrarian` varchar(255) NOT NULL default '',
1014 `libopac` varchar(255) NOT NULL default '',
1015 `repeatable` tinyint(4) NOT NULL default 0,
1016 `mandatory` tinyint(4) NOT NULL default 0,
1017 `kohafield` varchar(40) default NULL,
1018 `tab` tinyint(1) default NULL,
1019 `authorised_value` varchar(10) default NULL,
1020 `authtypecode` varchar(10) default NULL,
1021 `value_builder` varchar(80) default NULL,
1022 `isurl` tinyint(1) default NULL,
1023 `hidden` tinyint(1) default NULL,
1024 `frameworkcode` varchar(4) NOT NULL default '',
1025 `seealso` varchar(255) default NULL,
1026 `link` varchar(80) default NULL,
1027 `defaultvalue` text default NULL,
1028 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1029 KEY `kohafield_2` (`kohafield`),
1030 KEY `tab` (`frameworkcode`,`tab`),
1031 KEY `kohafield` (`frameworkcode`,`kohafield`)
1032 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1035 -- Table structure for table `marc_tag_structure`
1038 DROP TABLE IF EXISTS `marc_tag_structure`;
1039 CREATE TABLE `marc_tag_structure` (
1040 `tagfield` char(3) NOT NULL default '',
1041 `liblibrarian` char(255) NOT NULL default '',
1042 `libopac` char(255) NOT NULL default '',
1043 `repeatable` tinyint(4) NOT NULL default 0,
1044 `mandatory` tinyint(4) NOT NULL default 0,
1045 `authorised_value` char(10) default NULL,
1046 `frameworkcode` char(4) NOT NULL default '',
1047 PRIMARY KEY (`frameworkcode`,`tagfield`)
1048 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1051 -- Table structure for table `mediatypetable`
1054 DROP TABLE IF EXISTS `mediatypetable`;
1055 CREATE TABLE `mediatypetable` (
1056 `mediatypecode` varchar(5) NOT NULL default '',
1058 `itemtypecodes` text,
1059 PRIMARY KEY (`mediatypecode`)
1060 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1063 -- Table structure for table `notifys`
1066 DROP TABLE IF EXISTS `notifys`;
1067 CREATE TABLE `notifys` (
1068 `notify_id` int(11) NOT NULL default 0,
1069 `borrowernumber` int(11) NOT NULL default 0,
1070 `itemnumber` int(11) NOT NULL default 0,
1071 `notify_date` date default NULL,
1072 `notify_send_date` date default NULL,
1073 `notify_level` int(1) NOT NULL default 0,
1074 `method` varchar(20) NOT NULL default ''
1075 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1078 -- Table structure for table `opac_news`
1081 DROP TABLE IF EXISTS `opac_news`;
1082 CREATE TABLE `opac_news` (
1083 `idnew` int(10) unsigned NOT NULL auto_increment,
1084 `title` varchar(250) NOT NULL default '',
1085 `new` text NOT NULL,
1086 `lang` varchar(4) NOT NULL default '',
1087 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1088 `expirationdate` date default NULL,
1089 `number` int(11) default NULL,
1090 PRIMARY KEY (`idnew`)
1091 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1094 -- Table structure for table `overduerules`
1097 DROP TABLE IF EXISTS `overduerules`;
1098 CREATE TABLE `overduerules` (
1099 `branchcode` varchar(255) NOT NULL default '',
1100 `categorycode` varchar(2) NOT NULL default '',
1101 `delay1` int(4) default 0,
1102 `letter1` varchar(20) default NULL,
1103 `debarred1` char(1) default 0,
1104 `delay2` int(4) default 0,
1105 `debarred2` char(1) default 0,
1106 `letter2` varchar(20) default NULL,
1107 `delay3` int(4) default 0,
1108 `letter3` varchar(20) default NULL,
1109 `debarred3` int(1) default 0,
1110 PRIMARY KEY (`branchcode`,`categorycode`)
1111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1114 -- Table structure for table `printers`
1117 DROP TABLE IF EXISTS `printers`;
1118 CREATE TABLE `printers` (
1119 `printername` char(40) NOT NULL default '',
1120 `printqueue` char(20) default NULL,
1121 `printtype` char(20) default NULL,
1122 PRIMARY KEY (`printername`)
1123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1126 -- Table structure for table `repeatable_holidays`
1129 DROP TABLE IF EXISTS `repeatable_holidays`;
1130 CREATE TABLE `repeatable_holidays` (
1131 `id` int(11) NOT NULL auto_increment,
1132 `branchcode` varchar(4) NOT NULL default '',
1133 `weekday` smallint(6) default NULL,
1134 `day` smallint(6) default NULL,
1135 `month` smallint(6) default NULL,
1136 `title` varchar(50) NOT NULL default '',
1137 `description` text NOT NULL,
1139 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1142 -- Table structure for table `reserveconstraints`
1145 DROP TABLE IF EXISTS `reserveconstraints`;
1146 CREATE TABLE `reserveconstraints` (
1147 `borrowernumber` int(11) NOT NULL default 0,
1148 `reservedate` date default NULL,
1149 `biblionumber` int(11) NOT NULL default 0,
1150 `biblioitemnumber` int(11) default NULL,
1151 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1152 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1155 -- Table structure for table `reserves`
1158 DROP TABLE IF EXISTS `reserves`;
1159 CREATE TABLE `reserves` (
1160 `borrowernumber` int(11) NOT NULL default 0,
1161 `reservedate` date default NULL,
1162 `biblionumber` int(11) NOT NULL default 0,
1163 `constrainttype` char(1) default NULL,
1164 `branchcode` varchar(4) default NULL,
1165 `notificationdate` date default NULL,
1166 `reminderdate` date default NULL,
1167 `cancellationdate` date default NULL,
1168 `reservenotes` mediumtext,
1169 `priority` smallint(6) default NULL,
1170 `found` char(1) default NULL,
1171 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1172 `itemnumber` int(11) default NULL,
1173 `waitingdate` date default NULL,
1174 KEY `borrowernumber` (`borrowernumber`),
1175 KEY `biblionumber` (`biblionumber`),
1176 KEY `itemnumber` (`itemnumber`),
1177 KEY `branchcode` (`branchcode`),
1178 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1179 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1180 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1181 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1185 -- Table structure for table `reviews`
1188 DROP TABLE IF EXISTS `reviews`;
1189 CREATE TABLE `reviews` (
1190 `reviewid` int(11) NOT NULL auto_increment,
1191 `borrowernumber` int(11) default NULL,
1192 `biblionumber` int(11) default NULL,
1194 `approved` tinyint(4) default NULL,
1195 `datereviewed` datetime default NULL,
1196 PRIMARY KEY (`reviewid`)
1197 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1200 -- Table structure for table `roadtype`
1203 DROP TABLE IF EXISTS `roadtype`;
1204 CREATE TABLE `roadtype` (
1205 `roadtypeid` int(11) NOT NULL auto_increment,
1206 `road_type` char(100) NOT NULL default '',
1207 PRIMARY KEY (`roadtypeid`)
1208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1211 -- Table structure for table `serial`
1214 DROP TABLE IF EXISTS `serial`;
1215 CREATE TABLE `serial` (
1216 `serialid` int(11) NOT NULL auto_increment,
1217 `biblionumber` varchar(100) NOT NULL default '',
1218 `subscriptionid` varchar(100) NOT NULL default '',
1219 `serialseq` varchar(100) NOT NULL default '',
1220 `status` tinyint(4) NOT NULL default 0,
1221 `planneddate` date default NULL,
1223 `publisheddate` date default NULL,
1225 `claimdate` date default NULL,
1226 `routingnotes` text,
1227 PRIMARY KEY (`serialid`)
1228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1231 -- Table structure for table `sessions`
1234 DROP TABLE IF EXISTS sessions;
1235 CREATE TABLE sessions (
1236 `id` char(32) NOT NULL,
1237 `a_session` text NOT NULL,
1239 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1242 -- Table structure for table `special_holidays`
1245 DROP TABLE IF EXISTS `special_holidays`;
1246 CREATE TABLE `special_holidays` (
1247 `id` int(11) NOT NULL auto_increment,
1248 `branchcode` varchar(4) NOT NULL default '',
1249 `day` smallint(6) NOT NULL default 0,
1250 `month` smallint(6) NOT NULL default 0,
1251 `year` smallint(6) NOT NULL default 0,
1252 `isexception` smallint(1) NOT NULL default '1',
1253 `title` varchar(50) NOT NULL default '',
1254 `description` text NOT NULL,
1256 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1259 -- Table structure for table `statistics`
1262 DROP TABLE IF EXISTS `statistics`;
1263 CREATE TABLE `statistics` (
1264 `datetime` datetime default NULL,
1265 `branch` varchar(4) default NULL,
1266 `proccode` varchar(4) default NULL,
1267 `value` double(16,4) default NULL,
1268 `type` varchar(16) default NULL,
1270 `usercode` varchar(10) default NULL,
1271 `itemnumber` int(11) default NULL,
1272 `itemtype` varchar(4) default NULL,
1273 `borrowernumber` int(11) default NULL,
1274 `associatedborrower` int(11) default NULL,
1275 KEY `timeidx` (`datetime`)
1276 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1279 -- Table structure for table `stopwords`
1282 DROP TABLE IF EXISTS `stopwords`;
1283 CREATE TABLE `stopwords` (
1284 `word` varchar(255) default NULL
1285 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1288 -- Table structure for table `subcategorytable`
1291 DROP TABLE IF EXISTS `subcategorytable`;
1292 CREATE TABLE `subcategorytable` (
1293 `subcategorycode` varchar(5) NOT NULL default '',
1295 `itemtypecodes` text,
1296 PRIMARY KEY (`subcategorycode`)
1297 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1300 -- Table structure for table `subscription`
1303 DROP TABLE IF EXISTS `subscription`;
1304 CREATE TABLE `subscription` (
1305 `biblionumber` int(11) NOT NULL default 0,
1306 `subscriptionid` int(11) NOT NULL auto_increment,
1307 `librarian` varchar(100) default '',
1308 `startdate` date default NULL,
1309 `aqbooksellerid` int(11) default 0,
1310 `cost` int(11) default 0,
1311 `aqbudgetid` int(11) default 0,
1312 `weeklength` tinyint(4) default 0,
1313 `monthlength` tinyint(4) default 0,
1314 `numberlength` tinyint(4) default 0,
1315 `periodicity` tinyint(4) default 0,
1316 `dow` varchar(100) default '',
1317 `numberingmethod` varchar(100) default '',
1319 `status` varchar(100) NOT NULL default '',
1320 `add1` int(11) default 0,
1321 `every1` int(11) default 0,
1322 `whenmorethan1` int(11) default 0,
1323 `setto1` int(11) default NULL,
1324 `lastvalue1` int(11) default NULL,
1325 `add2` int(11) default 0,
1326 `every2` int(11) default 0,
1327 `whenmorethan2` int(11) default 0,
1328 `setto2` int(11) default NULL,
1329 `lastvalue2` int(11) default NULL,
1330 `add3` int(11) default 0,
1331 `every3` int(11) default 0,
1332 `innerloop1` int(11) default 0,
1333 `innerloop2` int(11) default 0,
1334 `innerloop3` int(11) default 0,
1335 `whenmorethan3` int(11) default 0,
1336 `setto3` int(11) default NULL,
1337 `lastvalue3` int(11) default NULL,
1338 `issuesatonce` tinyint(3) NOT NULL default '1',
1339 `firstacquidate` date default NULL,
1340 `manualhistory` tinyint(1) NOT NULL default 0,
1341 `irregularity` text,
1342 `letter` varchar(20) default NULL,
1343 `numberpattern` tinyint(3) default 0,
1344 `distributedto` text,
1345 `internalnotes` longtext,
1347 `branchcode` varchar(12) NOT NULL default '',
1348 `hemisphere` tinyint(3) default 0,
1349 PRIMARY KEY (`subscriptionid`)
1350 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1353 -- Table structure for table `subscriptionhistory`
1356 DROP TABLE IF EXISTS `subscriptionhistory`;
1357 CREATE TABLE `subscriptionhistory` (
1358 `biblionumber` int(11) NOT NULL default 0,
1359 `subscriptionid` int(11) NOT NULL default 0,
1360 `histstartdate` date default NULL,
1361 `enddate` date default NULL,
1362 `missinglist` longtext NOT NULL,
1363 `recievedlist` longtext NOT NULL,
1364 `opacnote` varchar(150) NOT NULL default '',
1365 `librariannote` varchar(150) NOT NULL default '',
1366 PRIMARY KEY (`subscriptionid`),
1367 KEY `biblionumber` (`biblionumber`)
1368 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1371 -- Table structure for table `subscriptionroutinglist`
1374 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1375 CREATE TABLE `subscriptionroutinglist` (
1376 `routingid` int(11) NOT NULL auto_increment,
1377 `borrowernumber` int(11) default NULL,
1378 `ranking` int(11) default NULL,
1379 `subscriptionid` int(11) default NULL,
1380 PRIMARY KEY (`routingid`)
1381 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1384 -- Table structure for table `suggestions`
1387 DROP TABLE IF EXISTS `suggestions`;
1388 CREATE TABLE `suggestions` (
1389 `suggestionid` int(8) NOT NULL auto_increment,
1390 `suggestedby` int(11) NOT NULL default 0,
1391 `managedby` int(11) default NULL,
1392 `STATUS` varchar(10) NOT NULL default '',
1394 `author` varchar(80) default NULL,
1395 `title` varchar(80) default NULL,
1396 `copyrightdate` smallint(6) default NULL,
1397 `publishercode` varchar(255) default NULL,
1398 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1399 `volumedesc` varchar(255) default NULL,
1400 `publicationyear` smallint(6) default 0,
1401 `place` varchar(255) default NULL,
1402 `isbn` varchar(10) default NULL,
1403 `mailoverseeing` smallint(1) default 0,
1404 `biblionumber` int(11) default NULL,
1406 PRIMARY KEY (`suggestionid`),
1407 KEY `suggestedby` (`suggestedby`),
1408 KEY `managedby` (`managedby`)
1409 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1412 -- Table structure for table `systempreferences`
1415 DROP TABLE IF EXISTS `systempreferences`;
1416 CREATE TABLE `systempreferences` (
1417 `variable` varchar(50) NOT NULL default '',
1419 `options` mediumtext,
1421 `type` varchar(20) default NULL,
1422 PRIMARY KEY (`variable`)
1423 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1426 -- Table structure for table `tags`
1429 DROP TABLE IF EXISTS `tags`;
1430 CREATE TABLE `tags` (
1431 `entry` varchar(255) NOT NULL default '',
1432 `weight` bigint(20) NOT NULL default 0,
1433 PRIMARY KEY (`entry`)
1434 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1437 -- Table structure for table `userflags`
1440 DROP TABLE IF EXISTS `userflags`;
1441 CREATE TABLE `userflags` (
1442 `bit` int(11) NOT NULL default 0,
1443 `flag` char(30) default NULL,
1444 `flagdesc` char(255) default NULL,
1445 `defaulton` int(11) default NULL,
1447 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1450 -- Table structure for table `virtualshelves`
1453 DROP TABLE IF EXISTS `virtualshelves`;
1454 CREATE TABLE `virtualshelves` (
1455 `shelfnumber` int(11) NOT NULL auto_increment,
1456 `shelfname` char(255) default NULL,
1457 `owner` char(80) default NULL,
1458 `category` char(1) default NULL,
1459 PRIMARY KEY (`shelfnumber`)
1460 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1463 -- Table structure for table `virtualshelfcontents`
1466 DROP TABLE IF EXISTS `virtualshelfcontents`;
1467 CREATE TABLE `virtualshelfcontents` (
1468 `shelfnumber` int(11) NOT NULL default 0,
1469 `biblionumber` int(11) NOT NULL default 0,
1470 `flags` int(11) default NULL,
1471 `dateadded` timestamp NULL default NULL,
1472 KEY `shelfnumber` (`shelfnumber`),
1473 KEY `biblionumber` (`biblionumber`),
1474 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1475 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1476 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1479 -- Table structure for table `z3950servers`
1482 DROP TABLE IF EXISTS `z3950servers`;
1483 CREATE TABLE `z3950servers` (
1484 `host` varchar(255) default NULL,
1485 `port` int(11) default NULL,
1486 `db` varchar(255) default NULL,
1487 `userid` varchar(255) default NULL,
1488 `password` varchar(255) default NULL,
1490 `id` int(11) NOT NULL auto_increment,
1491 `checked` smallint(6) default NULL,
1492 `rank` int(11) default NULL,
1493 `syntax` varchar(80) default NULL,
1495 `position` enum('primary','secondary','') NOT NULL default 'primary',
1496 `type` enum('zed','opensearch') NOT NULL default 'zed',
1497 `description` text NOT NULL,
1499 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1502 -- Table structure for table `zebraqueue`
1505 DROP TABLE IF EXISTS `zebraqueue`;
1506 CREATE TABLE `zebraqueue` (
1507 `id` int(11) NOT NULL auto_increment,
1508 `biblio_auth_number` int(11) NOT NULL default 0,
1509 `operation` char(20) NOT NULL default '',
1510 `server` char(20) NOT NULL default '',
1512 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1514 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1515 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1516 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1517 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1518 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1519 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1520 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1521 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;