3 -- Host: localhost Database: koha30test
4 -- ------------------------------------------------------
5 -- Server version 4.1.22
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40101 SET NAMES utf8 */;
11 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
12 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
13 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
14 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17 -- Table structure for table `accountlines`
20 DROP TABLE IF EXISTS `accountlines`;
21 CREATE TABLE `accountlines` (
22 `borrowernumber` int(11) NOT NULL default '0',
23 `accountno` smallint(6) NOT NULL default '0',
24 `itemnumber` int(11) default NULL,
25 `date` date default NULL,
26 `amount` decimal(28,6) default NULL,
27 `description` mediumtext,
29 `accounttype` varchar(5) default NULL,
30 `amountoutstanding` decimal(28,6) default NULL,
31 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
32 `notify_id` int(11) NOT NULL default '0',
33 `notify_level` int(2) NOT NULL default '0',
34 KEY `acctsborridx` (`borrowernumber`),
35 KEY `timeidx` (`timestamp`),
36 KEY `itemnumber` (`itemnumber`),
37 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
38 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
39 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
42 -- Table structure for table `accountoffsets`
45 DROP TABLE IF EXISTS `accountoffsets`;
46 CREATE TABLE `accountoffsets` (
47 `borrowernumber` int(11) NOT NULL default '0',
48 `accountno` smallint(6) NOT NULL default '0',
49 `offsetaccount` smallint(6) NOT NULL default '0',
50 `offsetamount` decimal(28,6) default NULL,
51 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
52 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
55 -- Table structure for table `action_logs`
58 DROP TABLE IF EXISTS `action_logs`;
59 CREATE TABLE `action_logs` (
60 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
61 `user` int(11) NOT NULL default '0',
64 `object` int(11) default NULL,
66 PRIMARY KEY (`timestamp`,`user`)
67 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
70 -- Table structure for table `alert`
73 DROP TABLE IF EXISTS `alert`;
74 CREATE TABLE `alert` (
75 `alertid` int(11) NOT NULL auto_increment,
76 `borrowernumber` int(11) NOT NULL default '0',
77 `type` varchar(10) NOT NULL default '',
78 `externalid` varchar(20) NOT NULL default '',
79 PRIMARY KEY (`alertid`),
80 KEY `borrowernumber` (`borrowernumber`),
81 KEY `type` (`type`,`externalid`)
82 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
85 -- Table structure for table `aqbasket`
88 DROP TABLE IF EXISTS `aqbasket`;
89 CREATE TABLE `aqbasket` (
90 `basketno` int(11) NOT NULL auto_increment,
91 `creationdate` date default NULL,
92 `closedate` date default NULL,
93 `booksellerid` int(11) NOT NULL default '1',
94 `authorisedby` varchar(10) default NULL,
95 `booksellerinvoicenumber` mediumtext,
96 PRIMARY KEY (`basketno`),
97 KEY `booksellerid` (`booksellerid`),
98 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
99 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
102 -- Table structure for table `aqbookfund`
105 DROP TABLE IF EXISTS `aqbookfund`;
106 CREATE TABLE `aqbookfund` (
107 `bookfundid` varchar(5) NOT NULL default '''''',
108 `bookfundname` mediumtext,
109 `bookfundgroup` varchar(5) default NULL,
110 `branchcode` varchar(4) NOT NULL default '',
111 PRIMARY KEY (`bookfundid`,`branchcode`)
112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
115 -- Table structure for table `aqbooksellers`
118 DROP TABLE IF EXISTS `aqbooksellers`;
119 CREATE TABLE `aqbooksellers` (
120 `id` int(11) NOT NULL auto_increment,
122 `address1` mediumtext,
123 `address2` mediumtext,
124 `address3` mediumtext,
125 `address4` mediumtext,
126 `phone` varchar(30) default NULL,
127 `accountnumber` mediumtext,
128 `othersupplier` mediumtext,
129 `currency` varchar(3) NOT NULL default '',
130 `deliverydays` smallint(6) default NULL,
131 `followupdays` smallint(6) default NULL,
132 `followupscancel` smallint(6) default NULL,
133 `specialty` mediumtext,
134 `booksellerfax` mediumtext,
136 `bookselleremail` mediumtext,
137 `booksellerurl` mediumtext,
138 `contact` varchar(100) default NULL,
140 `url` varchar(255) default NULL,
141 `contpos` varchar(100) default NULL,
142 `contphone` varchar(100) default NULL,
143 `contfax` varchar(100) default NULL,
144 `contaltphone` varchar(100) default NULL,
145 `contemail` varchar(100) default NULL,
146 `contnotes` mediumtext,
147 `active` tinyint(4) default NULL,
148 `listprice` varchar(10) default NULL,
149 `invoiceprice` varchar(10) default NULL,
150 `gstreg` tinyint(4) default NULL,
151 `listincgst` tinyint(4) default NULL,
152 `invoiceincgst` tinyint(4) default NULL,
153 `discount` float(6,4) default NULL,
154 `fax` varchar(50) default NULL,
155 `nocalc` int(11) default NULL,
156 `invoicedisc` float(6,4) default NULL,
158 KEY `listprice` (`listprice`),
159 KEY `invoiceprice` (`invoiceprice`),
160 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
161 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
162 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
165 -- Table structure for table `aqbudget`
168 DROP TABLE IF EXISTS `aqbudget`;
169 CREATE TABLE `aqbudget` (
170 `bookfundid` varchar(5) NOT NULL default '',
171 `startdate` date NOT NULL default '0000-00-00',
172 `enddate` date default NULL,
173 `budgetamount` decimal(13,2) default NULL,
174 `aqbudgetid` tinyint(4) NOT NULL auto_increment,
175 `branchcode` varchar(4) default NULL,
176 PRIMARY KEY (`aqbudgetid`)
177 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
180 -- Table structure for table `aqorderbreakdown`
183 DROP TABLE IF EXISTS `aqorderbreakdown`;
184 CREATE TABLE `aqorderbreakdown` (
185 `ordernumber` int(11) default NULL,
186 `linenumber` int(11) default NULL,
187 `branchcode` char(4) default NULL,
188 `bookfundid` char(5) NOT NULL default '',
189 `allocation` smallint(6) default NULL,
190 KEY `ordernumber` (`ordernumber`),
191 KEY `bookfundid` (`bookfundid`),
192 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
193 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
197 -- Table structure for table `aqorderdelivery`
200 DROP TABLE IF EXISTS `aqorderdelivery`;
201 CREATE TABLE `aqorderdelivery` (
202 `ordernumber` date NOT NULL default '0000-00-00',
203 `deliverynumber` smallint(6) NOT NULL default '0',
204 `deliverydate` varchar(18) default NULL,
205 `qtydelivered` smallint(6) default NULL,
206 `deliverycomments` mediumtext
207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
210 -- Table structure for table `aqorders`
213 DROP TABLE IF EXISTS `aqorders`;
214 CREATE TABLE `aqorders` (
215 `ordernumber` int(11) NOT NULL auto_increment,
216 `biblionumber` int(11) default NULL,
218 `entrydate` date default NULL,
219 `quantity` smallint(6) default NULL,
220 `currency` varchar(3) default NULL,
221 `listprice` decimal(28,6) default NULL,
222 `totalamount` decimal(28,6) default NULL,
223 `datereceived` date default NULL,
224 `booksellerinvoicenumber` mediumtext,
225 `freight` decimal(28,6) default NULL,
226 `unitprice` decimal(28,6) default NULL,
227 `quantityreceived` smallint(6) default NULL,
228 `cancelledby` varchar(10) default NULL,
229 `datecancellationprinted` date default NULL,
231 `supplierreference` mediumtext,
232 `purchaseordernumber` mediumtext,
233 `subscription` tinyint(1) default NULL,
234 `serialid` varchar(30) default NULL,
235 `basketno` int(11) default NULL,
236 `biblioitemnumber` int(11) default NULL,
237 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
238 `rrp` decimal(13,2) default NULL,
239 `ecost` decimal(13,2) default NULL,
240 `gst` decimal(13,2) default NULL,
241 `budgetdate` date default NULL,
242 `sort1` varchar(80) default NULL,
243 `sort2` varchar(80) default NULL,
244 PRIMARY KEY (`ordernumber`),
245 KEY `basketno` (`basketno`),
246 KEY `biblionumber` (`biblionumber`),
247 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
248 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
249 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
252 -- Table structure for table `auth_header`
255 DROP TABLE IF EXISTS `auth_header`;
256 CREATE TABLE `auth_header` (
257 `authid` bigint(20) unsigned NOT NULL auto_increment,
258 `authtypecode` varchar(10) NOT NULL default '',
259 `datecreated` date NOT NULL default '0000-00-00',
260 `datemodified` date default NULL,
261 `origincode` varchar(20) default NULL,
262 `authtrees` mediumtext,
264 `linkid` bigint(20) default NULL,
265 `marcxml` longtext NOT NULL,
266 PRIMARY KEY (`authid`),
267 KEY `origincode` (`origincode`)
268 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
271 -- Table structure for table `auth_subfield_structure`
274 DROP TABLE IF EXISTS `auth_subfield_structure`;
275 CREATE TABLE `auth_subfield_structure` (
276 `authtypecode` varchar(10) NOT NULL default '',
277 `tagfield` varchar(3) NOT NULL default '',
278 `tagsubfield` char(1) NOT NULL default '',
279 `liblibrarian` varchar(255) NOT NULL default '',
280 `libopac` varchar(255) NOT NULL default '',
281 `repeatable` tinyint(4) NOT NULL default '0',
282 `mandatory` tinyint(4) NOT NULL default '0',
283 `tab` tinyint(1) default NULL,
284 `authorised_value` varchar(10) default NULL,
285 `value_builder` varchar(80) default NULL,
286 `seealso` varchar(255) default NULL,
287 `isurl` tinyint(1) default NULL,
288 `hidden` tinyint(3) NOT NULL default '0',
289 `linkid` tinyint(1) NOT NULL default '0',
290 `kohafield` varchar(45) NOT NULL default '',
291 `frameworkcode` varchar(8) NOT NULL default '',
292 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
293 KEY `tab` (`authtypecode`,`tab`)
294 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
297 -- Table structure for table `auth_subfield_table`
300 DROP TABLE IF EXISTS `auth_subfield_table`;
301 CREATE TABLE `auth_subfield_table` (
302 `subfieldid` bigint(20) unsigned NOT NULL auto_increment,
303 `authid` bigint(20) unsigned NOT NULL default '0',
304 `tag` varchar(3) NOT NULL default '',
305 `tagorder` tinyint(4) NOT NULL default '1',
306 `tag_indicator` varchar(2) NOT NULL default '',
307 `subfieldcode` char(1) NOT NULL default '',
308 `subfieldorder` tinyint(4) NOT NULL default '1',
309 `subfieldvalue` varchar(255) default NULL,
310 PRIMARY KEY (`subfieldid`),
311 KEY `authid` (`authid`),
313 KEY `subfieldcode` (`subfieldcode`),
314 KEY `subfieldvalue` (`subfieldvalue`)
315 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
318 -- Table structure for table `auth_tag_structure`
321 DROP TABLE IF EXISTS `auth_tag_structure`;
322 CREATE TABLE `auth_tag_structure` (
323 `authtypecode` char(10) NOT NULL default '',
324 `tagfield` char(3) NOT NULL default '',
325 `liblibrarian` char(255) NOT NULL default '',
326 `libopac` char(255) NOT NULL default '',
327 `repeatable` tinyint(4) NOT NULL default '0',
328 `mandatory` tinyint(4) NOT NULL default '0',
329 `authorised_value` char(10) default NULL,
330 PRIMARY KEY (`authtypecode`,`tagfield`),
331 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
332 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
335 -- Table structure for table `auth_types`
338 DROP TABLE IF EXISTS `auth_types`;
339 CREATE TABLE `auth_types` (
340 `authtypecode` varchar(10) NOT NULL default '',
341 `authtypetext` varchar(255) NOT NULL default '',
342 `auth_tag_to_report` varchar(3) NOT NULL default '',
343 `summary` mediumtext NOT NULL,
344 PRIMARY KEY (`authtypecode`)
345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
348 -- Table structure for table `authorised_values`
351 DROP TABLE IF EXISTS `authorised_values`;
352 CREATE TABLE `authorised_values` (
353 `id` int(11) NOT NULL auto_increment,
354 `category` char(10) NOT NULL default '',
355 `authorised_value` char(80) NOT NULL default '',
356 `lib` char(80) default NULL,
358 KEY `name` (`category`)
359 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
362 -- Table structure for table `biblio`
365 DROP TABLE IF EXISTS `biblio`;
366 CREATE TABLE `biblio` (
367 `biblionumber` int(11) NOT NULL default '0',
368 `frameworkcode` varchar(4) NOT NULL default '',
371 `unititle` mediumtext,
373 `serial` tinyint(1) default NULL,
374 `seriestitle` mediumtext,
375 `copyrightdate` smallint(6) default NULL,
376 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
377 `abstract` mediumtext,
378 PRIMARY KEY (`biblionumber`),
379 KEY `blbnoidx` (`biblionumber`)
380 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
383 -- Table structure for table `biblio_framework`
386 DROP TABLE IF EXISTS `biblio_framework`;
387 CREATE TABLE `biblio_framework` (
388 `frameworkcode` char(4) NOT NULL default '',
389 `frameworktext` char(255) NOT NULL default '',
390 PRIMARY KEY (`frameworkcode`)
391 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
394 -- Table structure for table `biblioanalysis`
397 DROP TABLE IF EXISTS `biblioanalysis`;
398 CREATE TABLE `biblioanalysis` (
399 `analyticaltitle` mediumtext,
400 `biblionumber` int(11) NOT NULL default '0',
401 `analyticalauthor` mediumtext
402 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
405 -- Table structure for table `biblioitems`
408 DROP TABLE IF EXISTS `biblioitems`;
409 CREATE TABLE `biblioitems` (
410 `biblioitemnumber` int(11) NOT NULL default '0',
411 `biblionumber` int(11) NOT NULL default '0',
414 `classification` varchar(25) default NULL,
415 `itemtype` varchar(4) default NULL,
416 `isbn` varchar(14) default NULL,
417 `issn` varchar(9) default NULL,
418 `dewey` varchar(30) default '',
419 `subclass` varchar(3) default NULL,
420 `publicationyear` text,
421 `publishercode` varchar(255) default NULL,
422 `volumedate` date default NULL,
424 `collectiontitle` mediumtext NOT NULL,
425 `collectionissn` text NOT NULL,
426 `collectionvolume` mediumtext NOT NULL,
427 `editionstatement` text NOT NULL,
428 `editionreponsability` text NOT NULL,
429 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
430 `illus` varchar(255) default NULL,
431 `pages` varchar(255) default NULL,
433 `size` varchar(255) default NULL,
434 `place` varchar(255) default NULL,
435 `lccn` varchar(25) default NULL,
437 `url` varchar(255) default NULL,
438 `lcsort` varchar(25) default NULL,
439 `ccode` varchar(4) default NULL,
440 `marcxml` longtext NOT NULL,
441 PRIMARY KEY (`biblioitemnumber`),
442 KEY `bibinoidx` (`biblioitemnumber`),
443 KEY `bibnoidx` (`biblionumber`),
445 KEY `publishercode` (`publishercode`),
446 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
447 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
450 -- Table structure for table `bookshelf`
453 DROP TABLE IF EXISTS `bookshelf`;
454 CREATE TABLE `bookshelf` (
455 `shelfnumber` int(11) NOT NULL auto_increment,
456 `shelfname` char(255) default NULL,
457 `owner` char(80) default NULL,
458 `category` char(1) default NULL,
459 PRIMARY KEY (`shelfnumber`)
460 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
463 -- Table structure for table `borexp`
466 DROP TABLE IF EXISTS `borexp`;
467 CREATE TABLE `borexp` (
468 `borrowernumber` int(11) default NULL,
469 `newexp` date default NULL
470 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
473 -- Table structure for table `borrowers`
476 DROP TABLE IF EXISTS `borrowers`;
477 CREATE TABLE `borrowers` (
478 `borrowernumber` int(11) NOT NULL auto_increment,
479 `cardnumber` varchar(16) default NULL,
480 `surname` mediumtext NOT NULL,
483 `othernames` mediumtext,
485 `streetnumber` varchar(10) default NULL,
486 `streettype` varchar(50) default NULL,
487 `address` mediumtext NOT NULL,
489 `city` mediumtext NOT NULL,
490 `zipcode` varchar(25) default NULL,
493 `mobile` varchar(50) default NULL,
497 `B_streetnumber` varchar(10) default NULL,
498 `B_streettype` varchar(50) default NULL,
499 `B_address` varchar(100) default NULL,
501 `B_zipcode` varchar(25) default NULL,
503 `B_phone` mediumtext,
504 `dateofbirth` date default NULL,
505 `branchcode` varchar(10) NOT NULL default '',
506 `categorycode` varchar(10) NOT NULL default '',
507 `dateenrolled` date default NULL,
508 `dateexpiry` date default NULL,
509 `gonenoaddress` tinyint(1) default NULL,
510 `lost` tinyint(1) default NULL,
511 `debarred` tinyint(1) default NULL,
512 `contactname` mediumtext,
513 `contactfirstname` text,
515 `guarantorid` int(11) default NULL,
516 `borrowernotes` mediumtext,
517 `relationship` varchar(100) default NULL,
518 `ethnicity` varchar(50) default NULL,
519 `ethnotes` varchar(255) default NULL,
520 `sex` char(1) default NULL,
521 `password` varchar(30) default NULL,
522 `flags` int(11) default NULL,
523 `userid` varchar(30) default NULL,
524 `opacnote` mediumtext,
525 `contactnote` varchar(255) default NULL,
526 `sort1` varchar(80) default NULL,
527 `sort2` varchar(80) default NULL,
528 UNIQUE KEY `cardnumber` (`cardnumber`),
529 KEY `borrowernumber` (`borrowernumber`),
530 KEY `categorycode` (`categorycode`),
531 KEY `branchcode` (`branchcode`),
532 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
533 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
534 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
537 -- Table structure for table `borrowers_to_borrowers`
540 DROP TABLE IF EXISTS `borrowers_to_borrowers`;
541 CREATE TABLE `borrowers_to_borrowers` (
542 `borrower1` int(11) default NULL,
543 `borrower2` int(11) default NULL
544 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
547 -- Table structure for table `branchcategories`
550 DROP TABLE IF EXISTS `branchcategories`;
551 CREATE TABLE `branchcategories` (
552 `categorycode` varchar(4) NOT NULL default '',
553 `categoryname` mediumtext,
554 `codedescription` mediumtext,
555 PRIMARY KEY (`categorycode`)
556 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
559 -- Table structure for table `branches`
562 DROP TABLE IF EXISTS `branches`;
563 CREATE TABLE `branches` (
564 `branchcode` varchar(10) NOT NULL default '',
565 `branchname` mediumtext NOT NULL,
566 `branchaddress1` mediumtext,
567 `branchaddress2` mediumtext,
568 `branchaddress3` mediumtext,
569 `branchphone` mediumtext,
570 `branchfax` mediumtext,
571 `branchemail` mediumtext,
572 `issuing` tinyint(4) default NULL,
573 `branchip` varchar(15) default NULL,
574 `branchprinter` varchar(100) default NULL,
575 UNIQUE KEY `branchcode` (`branchcode`)
576 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
579 -- Table structure for table `branchrelations`
582 DROP TABLE IF EXISTS `branchrelations`;
583 CREATE TABLE `branchrelations` (
584 `branchcode` varchar(4) NOT NULL default '',
585 `categorycode` varchar(4) NOT NULL default '',
586 PRIMARY KEY (`branchcode`,`categorycode`),
587 KEY `branchcode` (`branchcode`),
588 KEY `categorycode` (`categorycode`),
589 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
590 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
591 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
594 -- Table structure for table `branchtransfers`
597 DROP TABLE IF EXISTS `branchtransfers`;
598 CREATE TABLE `branchtransfers` (
599 `itemnumber` int(11) NOT NULL default '0',
600 `datesent` datetime default NULL,
601 `frombranch` varchar(10) NOT NULL default '',
602 `datearrived` datetime default NULL,
603 `tobranch` varchar(10) NOT NULL default '',
604 `comments` mediumtext,
605 KEY `frombranch` (`frombranch`),
606 KEY `tobranch` (`tobranch`),
607 KEY `itemnumber` (`itemnumber`),
608 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
609 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
610 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
611 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
614 -- Table structure for table `catalogueentry`
617 DROP TABLE IF EXISTS `catalogueentry`;
618 CREATE TABLE `catalogueentry` (
619 `catalogueentry` mediumtext NOT NULL,
620 `entrytype` varchar(2) default NULL,
622 `seealso` mediumtext,
623 `seeinstead` mediumtext,
624 `biblionumber` int(11) default NULL,
625 KEY `entrytype` (`entrytype`,`catalogueentry`(250))
626 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
629 -- Table structure for table `categories`
632 DROP TABLE IF EXISTS `categories`;
633 CREATE TABLE `categories` (
634 `categorycode` varchar(10) NOT NULL default '',
635 `description` mediumtext,
636 `enrolmentperiod` smallint(6) default NULL,
637 `upperagelimit` smallint(6) default NULL,
638 `dateofbirthrequired` tinyint(1) default NULL,
639 `finetype` varchar(30) default NULL,
640 `bulk` tinyint(1) default NULL,
641 `enrolmentfee` decimal(28,6) default NULL,
642 `overduenoticerequired` tinyint(1) default NULL,
643 `issuelimit` smallint(6) default NULL,
644 `reservefee` decimal(28,6) default NULL,
645 `category_type` char(1) NOT NULL default 'A',
646 PRIMARY KEY (`categorycode`),
647 UNIQUE KEY `categorycode` (`categorycode`)
648 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
651 -- Table structure for table `categorytable`
654 DROP TABLE IF EXISTS `categorytable`;
655 CREATE TABLE `categorytable` (
656 `categorycode` varchar(5) NOT NULL default '',
658 `itemtypecodes` text,
659 PRIMARY KEY (`categorycode`)
660 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
663 -- Table structure for table `charges`
666 DROP TABLE IF EXISTS `charges`;
667 CREATE TABLE `charges` (
668 `charge_id` varchar(5) NOT NULL default '',
669 `description` text NOT NULL,
670 `amount` decimal(28,6) NOT NULL default '0.000000',
671 `min` int(4) NOT NULL default '0',
672 `max` int(4) NOT NULL default '0',
673 `level` int(1) NOT NULL default '0',
674 PRIMARY KEY (`charge_id`)
675 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
678 -- Table structure for table `cities`
681 DROP TABLE IF EXISTS `cities`;
682 CREATE TABLE `cities` (
683 `cityid` int(11) NOT NULL auto_increment,
684 `city_name` char(100) NOT NULL default '',
685 `city_zipcode` char(20) default NULL,
686 PRIMARY KEY (`cityid`)
687 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
690 -- Table structure for table `currency`
693 DROP TABLE IF EXISTS `currency`;
694 CREATE TABLE `currency` (
695 `currency` varchar(10) NOT NULL default '',
696 `rate` float(7,5) default NULL,
697 PRIMARY KEY (`currency`)
698 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
701 -- Table structure for table `deletedbiblio`
704 DROP TABLE IF EXISTS `deletedbiblio`;
705 CREATE TABLE `deletedbiblio` (
706 `biblionumber` int(11) NOT NULL default '0',
709 `unititle` mediumtext,
711 `serial` tinyint(1) default NULL,
712 `seriestitle` mediumtext,
713 `copyrightdate` smallint(6) default NULL,
714 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
716 `abstract` mediumtext,
717 PRIMARY KEY (`biblionumber`),
718 KEY `blbnoidx` (`biblionumber`)
719 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
722 -- Table structure for table `deletedbiblioitems`
725 DROP TABLE IF EXISTS `deletedbiblioitems`;
726 CREATE TABLE `deletedbiblioitems` (
727 `biblioitemnumber` int(11) NOT NULL default '0',
728 `biblionumber` int(11) NOT NULL default '0',
731 `classification` varchar(25) default NULL,
732 `itemtype` varchar(4) default NULL,
733 `isbn` varchar(14) default NULL,
734 `issn` varchar(9) default NULL,
735 `dewey` double(8,6) default NULL,
736 `subclass` varchar(3) default NULL,
737 `publicationyear` smallint(6) default NULL,
738 `publishercode` varchar(255) default NULL,
739 `volumedate` date default NULL,
740 `volumeddesc` varchar(255) default NULL,
741 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
742 `illus` varchar(255) default NULL,
743 `pages` varchar(255) default NULL,
745 `size` varchar(255) default NULL,
746 `lccn` varchar(25) default NULL,
748 `url` varchar(255) default NULL,
749 `place` varchar(255) default NULL,
750 `lcsort` varchar(25) default NULL,
751 `ccode` varchar(4) default NULL,
752 PRIMARY KEY (`biblioitemnumber`),
753 KEY `bibinoidx` (`biblioitemnumber`),
754 KEY `bibnoidx` (`biblionumber`)
755 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
758 -- Table structure for table `deletedborrowers`
761 DROP TABLE IF EXISTS `deletedborrowers`;
762 CREATE TABLE `deletedborrowers` (
763 `borrowernumber` int(11) NOT NULL default '0',
764 `cardnumber` varchar(9) NOT NULL default '',
765 `surname` mediumtext NOT NULL,
768 `othernames` mediumtext,
770 `streetnumber` varchar(10) default NULL,
771 `streettype` varchar(50) default NULL,
772 `address` mediumtext NOT NULL,
774 `city` mediumtext NOT NULL,
775 `zipcode` varchar(25) default NULL,
778 `mobile` varchar(50) default NULL,
782 `B_streetnumber` varchar(10) default NULL,
783 `B_streettype` varchar(50) default NULL,
784 `B_address` varchar(100) default NULL,
786 `B_zipcode` varchar(25) default NULL,
788 `B_phone` mediumtext,
789 `dateofbirth` date default NULL,
790 `branchcode` varchar(4) NOT NULL default '',
791 `categorycode` varchar(2) default NULL,
792 `dateenrolled` date default NULL,
793 `dateexpiry` date default NULL,
794 `gonenoaddress` tinyint(1) default NULL,
795 `lost` tinyint(1) default NULL,
796 `debarred` tinyint(1) default NULL,
797 `contactname` mediumtext,
798 `contactfirstname` text,
800 `guarantorid` int(11) default NULL,
801 `borrowernotes` mediumtext,
802 `relationship` varchar(100) default NULL,
803 `ethnicity` varchar(50) default NULL,
804 `ethnotes` varchar(255) default NULL,
805 `sex` char(1) default NULL,
806 `password` varchar(30) default NULL,
807 `flags` int(11) default NULL,
808 `userid` varchar(30) default NULL,
809 `opacnote` mediumtext,
810 `contactnote` varchar(255) default NULL,
811 `sort1` varchar(80) default NULL,
812 `sort2` varchar(80) default NULL,
813 KEY `borrowernumber` (`borrowernumber`),
814 KEY `cardnumber` (`cardnumber`)
815 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
818 -- Table structure for table `deleteditems`
821 DROP TABLE IF EXISTS `deleteditems`;
822 CREATE TABLE `deleteditems` (
823 `itemnumber` int(11) NOT NULL default '0',
824 `biblionumber` int(11) NOT NULL default '0',
825 `multivolumepart` varchar(30) default NULL,
826 `biblioitemnumber` int(11) NOT NULL default '0',
827 `barcode` varchar(9) NOT NULL default '',
828 `dateaccessioned` date default NULL,
829 `booksellerid` varchar(10) default NULL,
830 `homebranch` varchar(4) default NULL,
831 `price` decimal(28,6) default NULL,
832 `replacementprice` decimal(28,6) default NULL,
833 `replacementpricedate` date default NULL,
834 `datelastborrowed` date default NULL,
835 `datelastseen` date default NULL,
836 `multivolume` tinyint(1) default NULL,
837 `stack` tinyint(1) default NULL,
838 `notforloan` tinyint(1) default NULL,
839 `itemlost` tinyint(1) default NULL,
840 `wthdrawn` tinyint(1) default NULL,
841 `bulk` varchar(30) default NULL,
842 `issues` smallint(6) default NULL,
843 `renewals` smallint(6) default NULL,
844 `reserves` smallint(6) default NULL,
845 `restricted` tinyint(1) default NULL,
846 `binding` decimal(28,6) default NULL,
847 `itemnotes` mediumtext,
848 `holdingbranch` varchar(4) default NULL,
849 `interim` tinyint(1) default NULL,
850 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
852 `paidfor` mediumtext,
853 `location` varchar(80) default NULL,
854 `itemcallnumber` varchar(30) default NULL,
855 `onloan` date default '0000-00-00',
856 `cutterextra` varchar(45) default NULL,
857 `issue_date` date default NULL,
858 `itype` varchar(10) default NULL,
859 PRIMARY KEY (`itemnumber`),
860 UNIQUE KEY `barcode` (`barcode`),
861 KEY `itembarcodeidx` (`barcode`),
862 KEY `itembinoidx` (`biblioitemnumber`),
863 KEY `itembibnoidx` (`biblionumber`)
864 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
867 -- Table structure for table `ethnicity`
870 DROP TABLE IF EXISTS `ethnicity`;
871 CREATE TABLE `ethnicity` (
872 `code` varchar(10) NOT NULL default '',
873 `name` varchar(255) default NULL,
875 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
878 -- Table structure for table `issues`
881 DROP TABLE IF EXISTS `issues`;
882 CREATE TABLE `issues` (
883 `borrowernumber` int(11) default NULL,
884 `itemnumber` int(11) default NULL,
885 `date_due` date default NULL,
886 `branchcode` varchar(10) default NULL,
887 `issuingbranch` varchar(18) default NULL,
888 `returndate` date default NULL,
889 `lastreneweddate` date default NULL,
890 `return` varchar(4) default NULL,
891 `renewals` tinyint(4) default NULL,
892 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
893 `issuedate` date NOT NULL default '0000-00-00',
894 KEY `issuesborridx` (`borrowernumber`),
895 KEY `issuesitemidx` (`itemnumber`),
896 KEY `bordate` (`borrowernumber`,`timestamp`),
897 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
898 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
899 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
902 -- Table structure for table `issuingrules`
905 DROP TABLE IF EXISTS `issuingrules`;
906 CREATE TABLE `issuingrules` (
907 `categorycode` varchar(2) NOT NULL default '',
908 `itemtype` varchar(4) NOT NULL default '',
909 `restrictedtype` tinyint(1) default NULL,
910 `rentaldiscount` decimal(28,6) default NULL,
911 `reservecharge` decimal(28,6) default NULL,
912 `fine` decimal(28,6) default NULL,
913 `firstremind` int(11) default NULL,
914 `chargeperiod` int(11) default NULL,
915 `accountsent` int(11) default NULL,
916 `chargename` varchar(100) default NULL,
917 `maxissueqty` int(4) default NULL,
918 `issuelength` int(4) default NULL,
919 `branchcode` varchar(4) NOT NULL default '',
920 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
921 KEY `categorycode` (`categorycode`),
922 KEY `itemtype` (`itemtype`),
923 CONSTRAINT `issuingrules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE,
924 CONSTRAINT `issuingrules_ibfk_2` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
925 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
928 -- Table structure for table `items`
931 DROP TABLE IF EXISTS `items`;
932 CREATE TABLE `items` (
933 `itemnumber` int(11) NOT NULL default '0',
934 `biblionumber` int(11) NOT NULL default '0',
935 `multivolumepart` varchar(30) default NULL,
936 `biblioitemnumber` int(11) NOT NULL default '0',
937 `barcode` varchar(20) default NULL,
938 `dateaccessioned` date default NULL,
939 `booksellerid` varchar(10) default NULL,
940 `homebranch` varchar(4) default NULL,
941 `price` decimal(8,2) default NULL,
942 `replacementprice` decimal(8,2) default NULL,
943 `replacementpricedate` date default NULL,
944 `datelastborrowed` date default NULL,
945 `datelastseen` date default NULL,
946 `multivolume` tinyint(1) default NULL,
947 `stack` tinyint(1) default NULL,
948 `notforloan` tinyint(1) default NULL,
949 `itemlost` tinyint(1) default NULL,
950 `wthdrawn` tinyint(1) default NULL,
951 `itemcallnumber` varchar(30) default NULL,
952 `issues` smallint(6) default NULL,
953 `renewals` smallint(6) default NULL,
954 `reserves` smallint(6) default NULL,
955 `restricted` tinyint(1) default NULL,
956 `binding` decimal(28,6) default NULL,
957 `itemnotes` mediumtext,
958 `holdingbranch` varchar(10) default NULL,
959 `paidfor` mediumtext,
960 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
961 `location` varchar(80) default NULL,
962 `onloan` date default '0000-00-00',
963 `cutterextra` varchar(45) default NULL,
964 `issue_date` date default NULL,
965 `itype` varchar(10) default NULL,
966 PRIMARY KEY (`itemnumber`),
967 KEY `itembarcodeidx` (`barcode`),
968 KEY `itembinoidx` (`biblioitemnumber`),
969 KEY `itembibnoidx` (`biblionumber`),
970 KEY `homebranch` (`homebranch`),
971 KEY `holdingbranch` (`holdingbranch`),
972 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
973 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
974 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
975 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
978 -- Table structure for table `itemsprices`
981 DROP TABLE IF EXISTS `itemsprices`;
982 CREATE TABLE `itemsprices` (
983 `itemnumber` int(11) default NULL,
984 `price1` decimal(28,6) default NULL,
985 `price2` decimal(28,6) default NULL
986 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
989 -- Table structure for table `itemtypes`
992 DROP TABLE IF EXISTS `itemtypes`;
993 CREATE TABLE `itemtypes` (
994 `itemtype` varchar(10) NOT NULL default '',
995 `description` mediumtext,
996 `renewalsallowed` smallint(6) default NULL,
997 `rentalcharge` double(16,4) default NULL,
998 `notforloan` smallint(6) default NULL,
999 `imageurl` varchar(200) default NULL,
1001 PRIMARY KEY (`itemtype`),
1002 UNIQUE KEY `itemtype` (`itemtype`)
1003 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1006 -- Table structure for table `labels`
1009 DROP TABLE IF EXISTS `labels`;
1010 CREATE TABLE `labels` (
1011 `labelid` int(11) NOT NULL auto_increment,
1012 `itemnumber` varchar(100) NOT NULL default '',
1013 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1014 PRIMARY KEY (`labelid`)
1015 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1018 -- Table structure for table `labels_conf`
1021 DROP TABLE IF EXISTS `labels_conf`;
1022 CREATE TABLE `labels_conf` (
1023 `id` int(4) NOT NULL auto_increment,
1024 `barcodetype` char(100) default '',
1025 `title` tinyint(1) default '0',
1026 `isbn` tinyint(1) default '0',
1027 `itemtype` tinyint(1) default '0',
1028 `barcode` tinyint(1) default '0',
1029 `dewey` tinyint(1) default '0',
1030 `class` tinyint(1) default '0',
1031 `author` tinyint(1) default '0',
1032 `papertype` char(100) default '',
1033 `startrow` int(2) default NULL,
1035 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1038 -- Table structure for table `letter`
1041 DROP TABLE IF EXISTS `letter`;
1042 CREATE TABLE `letter` (
1043 `module` varchar(20) NOT NULL default '',
1044 `code` varchar(20) NOT NULL default '',
1045 `name` varchar(100) NOT NULL default '',
1046 `title` varchar(200) NOT NULL default '',
1048 PRIMARY KEY (`module`,`code`)
1049 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1052 -- Table structure for table `marc_biblio`
1055 DROP TABLE IF EXISTS `marc_biblio`;
1056 CREATE TABLE `marc_biblio` (
1057 `bibid` bigint(20) unsigned NOT NULL auto_increment,
1058 `biblionumber` int(11) NOT NULL default '0',
1059 `datecreated` date NOT NULL default '0000-00-00',
1060 `datemodified` date default NULL,
1061 `origincode` char(20) default NULL,
1062 `frameworkcode` char(4) NOT NULL default '',
1063 PRIMARY KEY (`bibid`),
1064 KEY `origincode` (`origincode`),
1065 KEY `biblionumber` (`biblionumber`)
1066 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1069 -- Table structure for table `marc_blob_subfield`
1072 DROP TABLE IF EXISTS `marc_blob_subfield`;
1073 CREATE TABLE `marc_blob_subfield` (
1074 `blobidlink` bigint(20) NOT NULL auto_increment,
1075 `subfieldvalue` longtext NOT NULL,
1076 PRIMARY KEY (`blobidlink`)
1077 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1080 -- Table structure for table `marc_breeding`
1083 DROP TABLE IF EXISTS `marc_breeding`;
1084 CREATE TABLE `marc_breeding` (
1085 `id` bigint(20) NOT NULL auto_increment,
1086 `file` varchar(80) NOT NULL default '',
1087 `isbn` varchar(10) NOT NULL default '',
1088 `title` varchar(128) default NULL,
1089 `author` varchar(80) default NULL,
1091 `encoding` varchar(40) NOT NULL default '',
1092 `z3950random` varchar(40) default NULL,
1094 KEY `title` (`title`),
1096 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1099 -- Table structure for table `marc_subfield_structure`
1102 DROP TABLE IF EXISTS `marc_subfield_structure`;
1103 CREATE TABLE `marc_subfield_structure` (
1104 `tagfield` varchar(3) NOT NULL default '',
1105 `tagsubfield` char(1) NOT NULL default '',
1106 `liblibrarian` varchar(255) NOT NULL default '',
1107 `libopac` varchar(255) NOT NULL default '',
1108 `repeatable` tinyint(4) NOT NULL default '0',
1109 `mandatory` tinyint(4) NOT NULL default '0',
1110 `kohafield` varchar(40) default NULL,
1111 `tab` tinyint(1) default NULL,
1112 `authorised_value` varchar(10) default NULL,
1113 `authtypecode` varchar(10) default NULL,
1114 `value_builder` varchar(80) default NULL,
1115 `isurl` tinyint(1) default NULL,
1116 `hidden` tinyint(1) default NULL,
1117 `frameworkcode` varchar(4) NOT NULL default '',
1118 `seealso` varchar(255) default NULL,
1119 `link` varchar(80) default NULL,
1120 `defaultvalue` text default NULL,
1121 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1122 KEY `kohafield_2` (`kohafield`),
1123 KEY `tab` (`frameworkcode`,`tab`),
1124 KEY `kohafield` (`frameworkcode`,`kohafield`)
1125 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1128 -- Table structure for table `marc_subfield_table`
1131 DROP TABLE IF EXISTS `marc_subfield_table`;
1132 CREATE TABLE `marc_subfield_table` (
1133 `subfieldid` bigint(20) unsigned NOT NULL auto_increment,
1134 `bibid` bigint(20) unsigned NOT NULL default '0',
1135 `tag` varchar(3) NOT NULL default '',
1136 `tagorder` int(11) NOT NULL default '1',
1137 `tag_indicator` varchar(2) NOT NULL default '',
1138 `subfieldcode` char(1) NOT NULL default '',
1139 `subfieldorder` tinyint(4) NOT NULL default '1',
1140 `subfieldvalue` varchar(255) default NULL,
1141 `valuebloblink` bigint(20) default NULL,
1142 PRIMARY KEY (`subfieldid`),
1143 KEY `bibid` (`bibid`),
1145 KEY `tag_indicator` (`tag_indicator`),
1146 KEY `subfieldorder` (`subfieldorder`),
1147 KEY `subfieldcode` (`subfieldcode`),
1148 KEY `subfieldvalue` (`subfieldvalue`),
1149 KEY `tagorder` (`tagorder`),
1150 KEY `marc_speed` (`tag`,`subfieldcode`,`subfieldvalue`)
1151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1154 -- Table structure for table `marc_tag_structure`
1157 DROP TABLE IF EXISTS `marc_tag_structure`;
1158 CREATE TABLE `marc_tag_structure` (
1159 `tagfield` char(3) NOT NULL default '',
1160 `liblibrarian` char(255) NOT NULL default '',
1161 `libopac` char(255) NOT NULL default '',
1162 `repeatable` tinyint(4) NOT NULL default '0',
1163 `mandatory` tinyint(4) NOT NULL default '0',
1164 `authorised_value` char(10) default NULL,
1165 `frameworkcode` char(4) NOT NULL default '',
1166 PRIMARY KEY (`frameworkcode`,`tagfield`)
1167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1170 -- Table structure for table `marcrecorddone`
1173 DROP TABLE IF EXISTS `marcrecorddone`;
1174 CREATE TABLE `marcrecorddone` (
1175 `isbn` char(40) default NULL,
1176 `issn` char(40) default NULL,
1177 `lccn` char(40) default NULL,
1178 `controlnumber` char(40) default NULL
1179 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1182 -- Table structure for table `mediatypetable`
1185 DROP TABLE IF EXISTS `mediatypetable`;
1186 CREATE TABLE `mediatypetable` (
1187 `mediatypecode` varchar(5) NOT NULL default '',
1189 `itemtypecodes` text,
1190 PRIMARY KEY (`mediatypecode`)
1191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1194 -- Table structure for table `notifys`
1197 DROP TABLE IF EXISTS `notifys`;
1198 CREATE TABLE `notifys` (
1199 `notify_id` int(11) NOT NULL default '0',
1200 `borrowernumber` int(11) NOT NULL default '0',
1201 `itemnumber` int(11) NOT NULL default '0',
1202 `notify_date` date NOT NULL default '0000-00-00',
1203 `notify_send_date` date default NULL,
1204 `notify_level` int(1) NOT NULL default '0',
1205 `method` varchar(20) NOT NULL default ''
1206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1209 -- Table structure for table `opac_news`
1212 DROP TABLE IF EXISTS `opac_news`;
1213 CREATE TABLE `opac_news` (
1214 `idnew` int(10) unsigned NOT NULL auto_increment,
1215 `title` varchar(250) NOT NULL default '',
1216 `new` text NOT NULL,
1217 `lang` varchar(4) NOT NULL default '',
1218 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1219 `expirationdate` date default NULL,
1220 `number` int(11) default NULL,
1221 PRIMARY KEY (`idnew`)
1222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1225 -- Table structure for table `overduerules`
1228 DROP TABLE IF EXISTS `overduerules`;
1229 CREATE TABLE `overduerules` (
1230 `branchcode` varchar(255) NOT NULL default '',
1231 `categorycode` varchar(2) NOT NULL default '',
1232 `delay1` int(4) default '0',
1233 `letter1` varchar(20) default NULL,
1234 `debarred1` char(1) default '0',
1235 `delay2` int(4) default '0',
1236 `debarred2` char(1) default '0',
1237 `letter2` varchar(20) default NULL,
1238 `delay3` int(4) default '0',
1239 `letter3` varchar(20) default NULL,
1240 `debarred3` int(1) default '0',
1241 PRIMARY KEY (`branchcode`,`categorycode`)
1242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1245 -- Table structure for table `printers`
1248 DROP TABLE IF EXISTS `printers`;
1249 CREATE TABLE `printers` (
1250 `printername` char(40) NOT NULL default '''''',
1251 `printqueue` char(20) default NULL,
1252 `printtype` char(20) default NULL,
1253 PRIMARY KEY (`printername`)
1254 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1257 -- Table structure for table `repeatable_holidays`
1260 DROP TABLE IF EXISTS `repeatable_holidays`;
1261 CREATE TABLE `repeatable_holidays` (
1262 `id` int(11) NOT NULL auto_increment,
1263 `branchcode` varchar(4) NOT NULL default '',
1264 `weekday` smallint(6) default NULL,
1265 `day` smallint(6) default NULL,
1266 `month` smallint(6) default NULL,
1267 `title` varchar(50) NOT NULL default '',
1268 `description` text NOT NULL,
1270 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1273 -- Table structure for table `reserveconstraints`
1276 DROP TABLE IF EXISTS `reserveconstraints`;
1277 CREATE TABLE `reserveconstraints` (
1278 `borrowernumber` int(11) NOT NULL default '0',
1279 `reservedate` date NOT NULL default '0000-00-00',
1280 `biblionumber` int(11) NOT NULL default '0',
1281 `biblioitemnumber` int(11) default NULL,
1282 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1283 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1286 -- Table structure for table `reserves`
1289 DROP TABLE IF EXISTS `reserves`;
1290 CREATE TABLE `reserves` (
1291 `borrowernumber` int(11) NOT NULL default '0',
1292 `reservedate` date NOT NULL default '0000-00-00',
1293 `biblionumber` int(11) NOT NULL default '0',
1294 `constrainttype` char(1) default NULL,
1295 `branchcode` varchar(4) default NULL,
1296 `notificationdate` date default NULL,
1297 `reminderdate` date default NULL,
1298 `cancellationdate` date default NULL,
1299 `reservenotes` mediumtext,
1300 `priority` smallint(6) default NULL,
1301 `found` char(1) default NULL,
1302 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1303 `itemnumber` int(11) default NULL,
1304 `waitingdate` date default NULL,
1305 KEY `borrowernumber` (`borrowernumber`),
1306 KEY `biblionumber` (`biblionumber`),
1307 KEY `itemnumber` (`itemnumber`),
1308 KEY `branchcode` (`branchcode`),
1309 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1310 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1311 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1312 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1316 -- Table structure for table `reviews`
1319 DROP TABLE IF EXISTS `reviews`;
1320 CREATE TABLE `reviews` (
1321 `reviewid` int(11) NOT NULL auto_increment,
1322 `borrowernumber` int(11) default NULL,
1323 `biblionumber` int(11) default NULL,
1325 `approved` tinyint(4) default NULL,
1326 `datereviewed` datetime default NULL,
1327 PRIMARY KEY (`reviewid`)
1328 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1331 -- Table structure for table `roadtype`
1334 DROP TABLE IF EXISTS `roadtype`;
1335 CREATE TABLE `roadtype` (
1336 `roadtypeid` int(11) NOT NULL auto_increment,
1337 `road_type` char(100) NOT NULL default '',
1338 PRIMARY KEY (`roadtypeid`)
1339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1342 -- Table structure for table `serial`
1345 DROP TABLE IF EXISTS `serial`;
1346 CREATE TABLE `serial` (
1347 `serialid` int(11) NOT NULL auto_increment,
1348 `biblionumber` varchar(100) NOT NULL default '',
1349 `subscriptionid` varchar(100) NOT NULL default '',
1350 `serialseq` varchar(100) NOT NULL default '',
1351 `status` tinyint(4) NOT NULL default '0',
1352 `planneddate` date NOT NULL default '0000-00-00',
1354 `publisheddate` date default NULL,
1356 `claimdate` date default NULL,
1357 `routingnotes` text,
1358 PRIMARY KEY (`serialid`)
1359 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1362 -- Table structure for table `sessionqueries`
1365 DROP TABLE IF EXISTS `sessionqueries`;
1366 CREATE TABLE `sessionqueries` (
1367 `sessionID` varchar(255) NOT NULL default '',
1368 `userid` varchar(100) NOT NULL default '',
1369 `ip` varchar(18) NOT NULL default '',
1370 `url` mediumtext NOT NULL
1371 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1374 -- Table structure for table `sessions`
1377 DROP TABLE IF EXISTS `sessions`;
1378 CREATE TABLE `sessions` (
1379 `sessionID` varchar(255) NOT NULL default '',
1380 `userid` varchar(255) default NULL,
1381 `ip` varchar(16) default NULL,
1382 `lasttime` int(11) default NULL,
1383 PRIMARY KEY (`sessionID`)
1384 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1387 -- Table structure for table `shelfcontents`
1390 DROP TABLE IF EXISTS `shelfcontents`;
1391 CREATE TABLE `shelfcontents` (
1392 `shelfnumber` int(11) NOT NULL default '0',
1393 `itemnumber` int(11) NOT NULL default '0',
1394 `flags` int(11) default NULL,
1395 `dateadded` timestamp NULL default NULL,
1396 KEY `shelfnumber` (`shelfnumber`),
1397 KEY `itemnumber` (`itemnumber`),
1398 CONSTRAINT `shelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `bookshelf` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1399 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1400 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1403 -- Table structure for table `special_holidays`
1406 DROP TABLE IF EXISTS `special_holidays`;
1407 CREATE TABLE `special_holidays` (
1408 `id` int(11) NOT NULL auto_increment,
1409 `branchcode` varchar(4) NOT NULL default '',
1410 `day` smallint(6) NOT NULL default '0',
1411 `month` smallint(6) NOT NULL default '0',
1412 `year` smallint(6) NOT NULL default '0',
1413 `isexception` smallint(1) NOT NULL default '1',
1414 `title` varchar(50) NOT NULL default '',
1415 `description` text NOT NULL,
1417 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1420 -- Table structure for table `statistics`
1423 DROP TABLE IF EXISTS `statistics`;
1424 CREATE TABLE `statistics` (
1425 `datetime` datetime NOT NULL default '0000-00-00 00:00:00',
1426 `branch` varchar(4) default NULL,
1427 `proccode` varchar(4) default NULL,
1428 `value` double(16,4) default NULL,
1429 `type` varchar(16) default NULL,
1431 `usercode` varchar(10) default NULL,
1432 `itemnumber` int(11) default NULL,
1433 `itemtype` varchar(4) default NULL,
1434 `borrowernumber` int(11) default NULL,
1435 `associatedborrower` int(11) default NULL,
1436 KEY `timeidx` (`datetime`)
1437 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1440 -- Table structure for table `stopwords`
1443 DROP TABLE IF EXISTS `stopwords`;
1444 CREATE TABLE `stopwords` (
1445 `word` varchar(255) default NULL
1446 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1449 -- Table structure for table `subcategorytable`
1452 DROP TABLE IF EXISTS `subcategorytable`;
1453 CREATE TABLE `subcategorytable` (
1454 `subcategorycode` varchar(5) NOT NULL default '',
1456 `itemtypecodes` text,
1457 PRIMARY KEY (`subcategorycode`)
1458 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1461 -- Table structure for table `subscription`
1464 DROP TABLE IF EXISTS `subscription`;
1465 CREATE TABLE `subscription` (
1466 `biblionumber` int(11) NOT NULL default '0',
1467 `subscriptionid` int(11) NOT NULL auto_increment,
1468 `librarian` varchar(100) default '',
1469 `startdate` date default '0000-00-00',
1470 `aqbooksellerid` int(11) default '0',
1471 `cost` int(11) default '0',
1472 `aqbudgetid` int(11) default '0',
1473 `weeklength` tinyint(4) default '0',
1474 `monthlength` tinyint(4) default '0',
1475 `numberlength` tinyint(4) default '0',
1476 `periodicity` tinyint(4) default '0',
1477 `dow` varchar(100) default '',
1478 `numberingmethod` varchar(100) default '',
1480 `status` varchar(100) NOT NULL default '',
1481 `add1` int(11) default '0',
1482 `every1` int(11) default '0',
1483 `whenmorethan1` int(11) default '0',
1484 `setto1` int(11) default NULL,
1485 `lastvalue1` int(11) default NULL,
1486 `add2` int(11) default '0',
1487 `every2` int(11) default '0',
1488 `whenmorethan2` int(11) default '0',
1489 `setto2` int(11) default NULL,
1490 `lastvalue2` int(11) default NULL,
1491 `add3` int(11) default '0',
1492 `every3` int(11) default '0',
1493 `innerloop1` int(11) default '0',
1494 `innerloop2` int(11) default '0',
1495 `innerloop3` int(11) default '0',
1496 `whenmorethan3` int(11) default '0',
1497 `setto3` int(11) default NULL,
1498 `lastvalue3` int(11) default NULL,
1499 `issuesatonce` tinyint(3) NOT NULL default '1',
1500 `firstacquidate` date NOT NULL default '0000-00-00',
1501 `manualhistory` tinyint(1) NOT NULL default '0',
1502 `irregularity` text,
1503 `letter` varchar(20) default NULL,
1504 `numberpattern` tinyint(3) default '0',
1505 `distributedto` text,
1506 `internalnotes` longtext,
1508 `branchcode` varchar(12) NOT NULL default '',
1509 `hemisphere` tinyint(3) default '0',
1510 PRIMARY KEY (`subscriptionid`)
1511 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1514 -- Table structure for table `subscriptionhistory`
1517 DROP TABLE IF EXISTS `subscriptionhistory`;
1518 CREATE TABLE `subscriptionhistory` (
1519 `biblionumber` int(11) NOT NULL default '0',
1520 `subscriptionid` int(11) NOT NULL default '0',
1521 `histstartdate` date NOT NULL default '0000-00-00',
1522 `enddate` date default '0000-00-00',
1523 `missinglist` longtext NOT NULL,
1524 `recievedlist` longtext NOT NULL,
1525 `opacnote` varchar(150) NOT NULL default '',
1526 `librariannote` varchar(150) NOT NULL default '',
1527 PRIMARY KEY (`subscriptionid`),
1528 KEY `biblionumber` (`biblionumber`)
1529 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1532 -- Table structure for table `subscriptionroutinglist`
1535 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1536 CREATE TABLE `subscriptionroutinglist` (
1537 `routingid` int(11) NOT NULL auto_increment,
1538 `borrowernumber` int(11) default NULL,
1539 `ranking` int(11) default NULL,
1540 `subscriptionid` int(11) default NULL,
1541 PRIMARY KEY (`routingid`)
1542 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1545 -- Table structure for table `suggestions`
1548 DROP TABLE IF EXISTS `suggestions`;
1549 CREATE TABLE `suggestions` (
1550 `suggestionid` int(8) NOT NULL auto_increment,
1551 `suggestedby` int(11) NOT NULL default '0',
1552 `managedby` int(11) default NULL,
1553 `STATUS` varchar(10) NOT NULL default '',
1555 `author` varchar(80) default NULL,
1556 `title` varchar(80) default NULL,
1557 `copyrightdate` smallint(6) default NULL,
1558 `publishercode` varchar(255) default NULL,
1559 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1560 `volumedesc` varchar(255) default NULL,
1561 `publicationyear` smallint(6) default '0',
1562 `place` varchar(255) default NULL,
1563 `isbn` varchar(10) default NULL,
1564 `mailoverseeing` smallint(1) default '0',
1565 `biblionumber` int(11) default NULL,
1567 PRIMARY KEY (`suggestionid`),
1568 KEY `suggestedby` (`suggestedby`),
1569 KEY `managedby` (`managedby`)
1570 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1573 -- Table structure for table `systempreferences`
1576 DROP TABLE IF EXISTS `systempreferences`;
1577 CREATE TABLE `systempreferences` (
1578 `variable` varchar(50) NOT NULL default '',
1580 `options` mediumtext,
1582 `type` varchar(20) default NULL,
1583 PRIMARY KEY (`variable`)
1584 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1587 -- Table structure for table `tags`
1590 DROP TABLE IF EXISTS `tags`;
1591 CREATE TABLE `tags` (
1592 `entry` varchar(255) NOT NULL default '',
1593 `weight` bigint(20) NOT NULL default '0',
1594 PRIMARY KEY (`entry`)
1595 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1598 -- Table structure for table `uploadedmarc`
1601 DROP TABLE IF EXISTS `uploadedmarc`;
1602 CREATE TABLE `uploadedmarc` (
1603 `id` int(11) NOT NULL auto_increment,
1605 `hidden` smallint(6) default NULL,
1606 `name` varchar(255) default NULL,
1608 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1611 -- Table structure for table `userflags`
1614 DROP TABLE IF EXISTS `userflags`;
1615 CREATE TABLE `userflags` (
1616 `bit` int(11) NOT NULL default '0',
1617 `flag` char(30) default NULL,
1618 `flagdesc` char(255) default NULL,
1619 `defaulton` int(11) default NULL,
1621 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1624 -- Table structure for table `users`
1627 DROP TABLE IF EXISTS `users`;
1628 CREATE TABLE `users` (
1629 `usercode` varchar(10) default NULL,
1630 `username` mediumtext,
1631 `password` mediumtext,
1632 `level` smallint(6) default NULL
1633 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1636 -- Table structure for table `websites`
1639 DROP TABLE IF EXISTS `websites`;
1640 CREATE TABLE `websites` (
1641 `websitenumber` int(11) NOT NULL auto_increment,
1642 `biblionumber` int(11) NOT NULL default '0',
1644 `description` mediumtext,
1645 `url` varchar(255) default NULL,
1646 PRIMARY KEY (`websitenumber`)
1647 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1650 -- Table structure for table `z3950queue`
1653 DROP TABLE IF EXISTS `z3950queue`;
1654 CREATE TABLE `z3950queue` (
1655 `id` int(11) NOT NULL auto_increment,
1657 `type` varchar(10) default NULL,
1658 `startdate` int(11) default NULL,
1659 `enddate` int(11) default NULL,
1660 `done` smallint(6) default NULL,
1662 `numrecords` int(11) default NULL,
1663 `servers` mediumtext,
1664 `identifier` varchar(30) default NULL,
1666 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1669 -- Table structure for table `z3950results`
1672 DROP TABLE IF EXISTS `z3950results`;
1673 CREATE TABLE `z3950results` (
1674 `id` int(11) NOT NULL auto_increment,
1675 `queryid` int(11) default NULL,
1676 `server` varchar(255) default NULL,
1677 `startdate` int(11) default NULL,
1678 `enddate` int(11) default NULL,
1680 `numrecords` int(11) default NULL,
1681 `numdownloaded` int(11) default NULL,
1682 `highestseen` int(11) default NULL,
1683 `active` smallint(6) default NULL,
1685 UNIQUE KEY `query_server` (`queryid`,`server`)
1686 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1689 -- Table structure for table `z3950servers`
1692 DROP TABLE IF EXISTS `z3950servers`;
1693 CREATE TABLE `z3950servers` (
1694 `host` varchar(255) default NULL,
1695 `port` int(11) default NULL,
1696 `db` varchar(255) default NULL,
1697 `userid` varchar(255) default NULL,
1698 `password` varchar(255) default NULL,
1700 `id` int(11) NOT NULL auto_increment,
1701 `checked` smallint(6) default NULL,
1702 `rank` int(11) default NULL,
1703 `syntax` varchar(80) default NULL,
1705 `position` enum('primary','secondary','') NOT NULL default 'primary',
1706 `type` enum('zed','opensearch') NOT NULL default 'zed',
1707 `description` text NOT NULL,
1709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1712 -- Table structure for table `zebraqueue`
1715 DROP TABLE IF EXISTS `zebraqueue`;
1716 CREATE TABLE `zebraqueue` (
1717 `id` int(11) NOT NULL auto_increment,
1718 `biblio_auth_number` int(11) NOT NULL default '0',
1719 `operation` char(20) NOT NULL default '',
1720 `server` char(20) NOT NULL default '',
1722 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1724 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1725 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1726 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1727 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1728 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1729 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1730 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;