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 `action_id` int(11) NOT NULL auto_increment,
63 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
64 `user` int(11) NOT NULL default 0,
67 `object` int(11) default NULL,
69 PRIMARY KEY (`action_id`),
70 KEY (`timestamp`,`user`)
71 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
74 -- Table structure for table `alert`
77 DROP TABLE IF EXISTS `alert`;
78 CREATE TABLE `alert` (
79 `alertid` int(11) NOT NULL auto_increment,
80 `borrowernumber` int(11) NOT NULL default 0,
81 `type` varchar(10) NOT NULL default '',
82 `externalid` varchar(20) NOT NULL default '',
83 PRIMARY KEY (`alertid`),
84 KEY `borrowernumber` (`borrowernumber`),
85 KEY `type` (`type`,`externalid`)
86 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
89 -- Table structure for table `aqbasket`
92 DROP TABLE IF EXISTS `aqbasket`;
93 CREATE TABLE `aqbasket` (
94 `basketno` int(11) NOT NULL auto_increment,
95 `creationdate` date default NULL,
96 `closedate` date default NULL,
97 `booksellerid` int(11) NOT NULL default 1,
98 `authorisedby` varchar(10) default NULL,
99 `booksellerinvoicenumber` mediumtext,
100 PRIMARY KEY (`basketno`),
101 KEY `booksellerid` (`booksellerid`),
102 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
103 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
106 -- Table structure for table `aqbookfund`
109 DROP TABLE IF EXISTS `aqbookfund`;
110 CREATE TABLE `aqbookfund` (
111 `bookfundid` varchar(10) NOT NULL default '',
112 `bookfundname` mediumtext,
113 `bookfundgroup` varchar(5) default NULL,
114 `branchcode` varchar(10) NOT NULL default '',
115 PRIMARY KEY (`bookfundid`,`branchcode`)
116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
119 -- Table structure for table `aqbooksellers`
122 DROP TABLE IF EXISTS `aqbooksellers`;
123 CREATE TABLE `aqbooksellers` (
124 `id` int(11) NOT NULL auto_increment,
126 `address1` mediumtext,
127 `address2` mediumtext,
128 `address3` mediumtext,
129 `address4` mediumtext,
130 `phone` varchar(30) default NULL,
131 `accountnumber` mediumtext,
132 `othersupplier` mediumtext,
133 `currency` varchar(3) NOT NULL default '',
134 `deliverydays` smallint(6) default NULL,
135 `followupdays` smallint(6) default NULL,
136 `followupscancel` smallint(6) default NULL,
137 `specialty` mediumtext,
138 `booksellerfax` mediumtext,
140 `bookselleremail` mediumtext,
141 `booksellerurl` mediumtext,
142 `contact` varchar(100) default NULL,
144 `url` varchar(255) default NULL,
145 `contpos` varchar(100) default NULL,
146 `contphone` varchar(100) default NULL,
147 `contfax` varchar(100) default NULL,
148 `contaltphone` varchar(100) default NULL,
149 `contemail` varchar(100) default NULL,
150 `contnotes` mediumtext,
151 `active` tinyint(4) default NULL,
152 `listprice` varchar(10) default NULL,
153 `invoiceprice` varchar(10) default NULL,
154 `gstreg` tinyint(4) default NULL,
155 `listincgst` tinyint(4) default NULL,
156 `invoiceincgst` tinyint(4) default NULL,
157 `discount` float(6,4) default NULL,
158 `fax` varchar(50) default NULL,
159 `nocalc` int(11) default NULL,
160 `invoicedisc` float(6,4) default NULL,
162 KEY `listprice` (`listprice`),
163 KEY `invoiceprice` (`invoiceprice`),
164 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
165 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
169 -- Table structure for table `aqbudget`
172 DROP TABLE IF EXISTS `aqbudget`;
173 CREATE TABLE `aqbudget` (
174 `bookfundid` varchar(10) NOT NULL default '',
175 `startdate` date NOT NULL default 0,
176 `enddate` date default NULL,
177 `budgetamount` decimal(13,2) default NULL,
178 `aqbudgetid` tinyint(4) NOT NULL auto_increment,
179 `branchcode` varchar(10) default NULL,
180 PRIMARY KEY (`aqbudgetid`)
181 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
184 -- Table structure for table `aqorderbreakdown`
187 DROP TABLE IF EXISTS `aqorderbreakdown`;
188 CREATE TABLE `aqorderbreakdown` (
189 `ordernumber` int(11) default NULL,
190 `linenumber` int(11) default NULL,
191 `branchcode` varchar(10) default NULL,
192 `bookfundid` varchar(10) NOT NULL default '',
193 `allocation` smallint(6) default NULL,
194 KEY `ordernumber` (`ordernumber`),
195 KEY `bookfundid` (`bookfundid`),
196 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
197 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
201 -- Table structure for table `aqorderdelivery`
204 DROP TABLE IF EXISTS `aqorderdelivery`;
205 CREATE TABLE `aqorderdelivery` (
206 `ordernumber` date default NULL,
207 `deliverynumber` smallint(6) NOT NULL default 0,
208 `deliverydate` varchar(18) default NULL,
209 `qtydelivered` smallint(6) default NULL,
210 `deliverycomments` mediumtext
211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
214 -- Table structure for table `aqorders`
217 DROP TABLE IF EXISTS `aqorders`;
218 CREATE TABLE `aqorders` (
219 `ordernumber` int(11) NOT NULL auto_increment,
220 `biblionumber` int(11) default NULL,
222 `entrydate` date default NULL,
223 `quantity` smallint(6) default NULL,
224 `currency` varchar(3) default NULL,
225 `listprice` decimal(28,6) default NULL,
226 `totalamount` decimal(28,6) default NULL,
227 `datereceived` date default NULL,
228 `booksellerinvoicenumber` mediumtext,
229 `freight` decimal(28,6) default NULL,
230 `unitprice` decimal(28,6) default NULL,
231 `quantityreceived` smallint(6) default NULL,
232 `cancelledby` varchar(10) default NULL,
233 `datecancellationprinted` date default NULL,
235 `supplierreference` mediumtext,
236 `purchaseordernumber` mediumtext,
237 `subscription` tinyint(1) default NULL,
238 `serialid` varchar(30) default NULL,
239 `basketno` int(11) default NULL,
240 `biblioitemnumber` int(11) default NULL,
241 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
242 `rrp` decimal(13,2) default NULL,
243 `ecost` decimal(13,2) default NULL,
244 `gst` decimal(13,2) default NULL,
245 `budgetdate` date default NULL,
246 `sort1` varchar(80) default NULL,
247 `sort2` varchar(80) default NULL,
248 PRIMARY KEY (`ordernumber`),
249 KEY `basketno` (`basketno`),
250 KEY `biblionumber` (`biblionumber`),
251 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
252 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
253 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
256 -- Table structure for table `auth_header`
259 DROP TABLE IF EXISTS `auth_header`;
260 CREATE TABLE `auth_header` (
261 `authid` bigint(20) unsigned NOT NULL auto_increment,
262 `authtypecode` varchar(10) NOT NULL default '',
263 `datecreated` date default NULL,
264 `datemodified` date default NULL,
265 `origincode` varchar(20) default NULL,
266 `authtrees` mediumtext,
268 `linkid` bigint(20) default NULL,
269 `marcxml` longtext NOT NULL,
270 PRIMARY KEY (`authid`),
271 KEY `origincode` (`origincode`)
272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
275 -- Table structure for table `auth_subfield_structure`
278 DROP TABLE IF EXISTS `auth_subfield_structure`;
279 CREATE TABLE `auth_subfield_structure` (
280 `authtypecode` varchar(10) NOT NULL default '',
281 `tagfield` varchar(3) NOT NULL default '',
282 `tagsubfield` varchar(1) NOT NULL default '',
283 `liblibrarian` varchar(255) NOT NULL default '',
284 `libopac` varchar(255) NOT NULL default '',
285 `repeatable` tinyint(4) NOT NULL default 0,
286 `mandatory` tinyint(4) NOT NULL default 0,
287 `tab` tinyint(1) default NULL,
288 `authorised_value` varchar(10) default NULL,
289 `value_builder` varchar(80) default NULL,
290 `seealso` varchar(255) default NULL,
291 `isurl` tinyint(1) default NULL,
292 `hidden` tinyint(3) NOT NULL default 0,
293 `linkid` tinyint(1) NOT NULL default 0,
294 `kohafield` varchar(45) NULL default '',
295 `frameworkcode` varchar(8) NOT NULL default '',
296 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
297 KEY `tab` (`authtypecode`,`tab`)
298 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
301 -- Table structure for table `auth_tag_structure`
304 DROP TABLE IF EXISTS `auth_tag_structure`;
305 CREATE TABLE `auth_tag_structure` (
306 `authtypecode` varchar(10) NOT NULL default '',
307 `tagfield` varchar(3) NOT NULL default '',
308 `liblibrarian` varchar(255) NOT NULL default '',
309 `libopac` varchar(255) NOT NULL default '',
310 `repeatable` tinyint(4) NOT NULL default 0,
311 `mandatory` tinyint(4) NOT NULL default 0,
312 `authorised_value` varchar(10) default NULL,
313 PRIMARY KEY (`authtypecode`,`tagfield`),
314 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
315 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
318 -- Table structure for table `auth_types`
321 DROP TABLE IF EXISTS `auth_types`;
322 CREATE TABLE `auth_types` (
323 `authtypecode` varchar(10) NOT NULL default '',
324 `authtypetext` varchar(255) NOT NULL default '',
325 `auth_tag_to_report` varchar(3) NOT NULL default '',
326 `summary` mediumtext NOT NULL,
327 PRIMARY KEY (`authtypecode`)
328 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
331 -- Table structure for table `authorised_values`
334 DROP TABLE IF EXISTS `authorised_values`;
335 CREATE TABLE `authorised_values` (
336 `id` int(11) NOT NULL auto_increment,
337 `category` varchar(10) NOT NULL default '',
338 `authorised_value` varchar(80) NOT NULL default '',
339 `lib` varchar(80) default NULL,
341 KEY `name` (`category`)
342 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
345 -- Table structure for table `biblio`
348 DROP TABLE IF EXISTS `biblio`;
349 CREATE TABLE `biblio` (
350 `biblionumber` int(11) NOT NULL auto_increment,
351 `frameworkcode` varchar(4) NOT NULL default '',
354 `unititle` mediumtext,
356 `serial` tinyint(1) default NULL,
357 `seriestitle` mediumtext,
358 `copyrightdate` smallint(6) default NULL,
359 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
360 `datecreated` DATE NOT NULL,
361 `abstract` mediumtext,
362 PRIMARY KEY (`biblionumber`),
363 KEY `blbnoidx` (`biblionumber`)
364 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
367 -- Table structure for table `biblio_framework`
370 DROP TABLE IF EXISTS `biblio_framework`;
371 CREATE TABLE `biblio_framework` (
372 `frameworkcode` varchar(4) NOT NULL default '',
373 `frameworktext` varchar(255) NOT NULL default '',
374 PRIMARY KEY (`frameworkcode`)
375 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
378 -- Table structure for table `biblioitems`
381 DROP TABLE IF EXISTS `biblioitems`;
382 CREATE TABLE `biblioitems` (
383 `biblioitemnumber` int(11) NOT NULL auto_increment,
384 `biblionumber` int(11) NOT NULL default 0,
387 `itemtype` varchar(10) default NULL,
388 `isbn` varchar(14) default NULL,
389 `issn` varchar(9) default NULL,
390 `publicationyear` text,
391 `publishercode` varchar(255) default NULL,
392 `volumedate` date default NULL,
394 `collectiontitle` mediumtext default NULL,
395 `collectionissn` text default NULL,
396 `collectionvolume` mediumtext default NULL,
397 `editionstatement` text default NULL,
398 `editionresponsibility` text default 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 `cn_source` varchar(10) default NULL,
409 `cn_class` varchar(30) default NULL,
410 `cn_item` varchar(10) default NULL,
411 `cn_suffix` varchar(10) default NULL,
412 `cn_sort` varchar(30) default NULL,
413 `totalissues` int(10),
414 `marcxml` longtext NOT NULL,
415 PRIMARY KEY (`biblioitemnumber`),
416 KEY `bibinoidx` (`biblioitemnumber`),
417 KEY `bibnoidx` (`biblionumber`),
419 KEY `publishercode` (`publishercode`),
420 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
421 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
424 -- Table structure for table `borrowers`
427 DROP TABLE IF EXISTS `borrowers`;
428 CREATE TABLE `borrowers` (
429 `borrowernumber` int(11) NOT NULL auto_increment,
430 `cardnumber` varchar(16) default NULL,
431 `surname` mediumtext NOT NULL,
434 `othernames` mediumtext,
436 `streetnumber` varchar(10) default NULL,
437 `streettype` varchar(50) default NULL,
438 `address` mediumtext NOT NULL,
440 `city` mediumtext NOT NULL,
441 `zipcode` varchar(25) default NULL,
444 `mobile` varchar(50) default NULL,
448 `B_streetnumber` varchar(10) default NULL,
449 `B_streettype` varchar(50) default NULL,
450 `B_address` varchar(100) default NULL,
452 `B_zipcode` varchar(25) default NULL,
454 `B_phone` mediumtext,
455 `dateofbirth` date default NULL,
456 `branchcode` varchar(10) NOT NULL default '',
457 `categorycode` varchar(10) NOT NULL default '',
458 `dateenrolled` date default NULL,
459 `dateexpiry` date default NULL,
460 `gonenoaddress` tinyint(1) default NULL,
461 `lost` tinyint(1) default NULL,
462 `debarred` tinyint(1) default NULL,
463 `contactname` mediumtext,
464 `contactfirstname` text,
466 `guarantorid` int(11) default NULL,
467 `borrowernotes` mediumtext,
468 `relationship` varchar(100) default NULL,
469 `ethnicity` varchar(50) default NULL,
470 `ethnotes` varchar(255) default NULL,
471 `sex` varchar(1) default NULL,
472 `password` varchar(30) default NULL,
473 `flags` int(11) default NULL,
474 `userid` varchar(30) default NULL,
475 `opacnote` mediumtext,
476 `contactnote` varchar(255) default NULL,
477 `sort1` varchar(80) default NULL,
478 `sort2` varchar(80) default NULL,
479 UNIQUE KEY `cardnumber` (`cardnumber`),
480 PRIMARY KEY `borrowernumber` (`borrowernumber`),
481 KEY `categorycode` (`categorycode`),
482 KEY `branchcode` (`branchcode`),
483 KEY `userid` (`userid`),
484 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
485 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
486 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
489 -- Table structure for table `branchcategories`
492 DROP TABLE IF EXISTS `branchcategories`;
493 CREATE TABLE `branchcategories` (
494 `categorycode` varchar(10) NOT NULL default '',
495 `categoryname` varchar(32),
496 `codedescription` mediumtext,
497 `categorytype` varchar(16),
498 PRIMARY KEY (`categorycode`)
499 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
502 -- Table structure for table `branches`
505 DROP TABLE IF EXISTS `branches`;
506 CREATE TABLE `branches` (
507 `branchcode` varchar(10) NOT NULL default '',
508 `branchname` mediumtext NOT NULL,
509 `branchaddress1` mediumtext,
510 `branchaddress2` mediumtext,
511 `branchaddress3` mediumtext,
512 `branchphone` mediumtext,
513 `branchfax` mediumtext,
514 `branchemail` mediumtext,
515 `issuing` tinyint(4) default NULL,
516 `branchip` varchar(15) default NULL,
517 `branchprinter` varchar(100) default NULL,
518 UNIQUE KEY `branchcode` (`branchcode`)
519 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
522 -- Table structure for table `branchrelations`
525 DROP TABLE IF EXISTS `branchrelations`;
526 CREATE TABLE `branchrelations` (
527 `branchcode` varchar(10) NOT NULL default '',
528 `categorycode` varchar(10) NOT NULL default '',
529 PRIMARY KEY (`branchcode`,`categorycode`),
530 KEY `branchcode` (`branchcode`),
531 KEY `categorycode` (`categorycode`),
532 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
533 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
534 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
537 -- Table structure for table `branchtransfers`
540 DROP TABLE IF EXISTS `branchtransfers`;
541 CREATE TABLE `branchtransfers` (
542 `itemnumber` int(11) NOT NULL default 0,
543 `datesent` datetime default NULL,
544 `frombranch` varchar(10) NOT NULL default '',
545 `datearrived` datetime default NULL,
546 `tobranch` varchar(10) NOT NULL default '',
547 `comments` mediumtext,
548 KEY `frombranch` (`frombranch`),
549 KEY `tobranch` (`tobranch`),
550 KEY `itemnumber` (`itemnumber`),
551 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
552 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
553 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
554 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
558 -- Table structure for table `browser`
560 DROP TABLE IF EXISTS `browser`;
561 CREATE TABLE `browser` (
562 `level` int(11) NOT NULL,
563 `classification` varchar(20) NOT NULL,
564 `description` varchar(255) NOT NULL,
565 `number` bigint(20) NOT NULL,
566 `endnode` tinyint(4) NOT NULL
567 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
570 -- Table structure for table `categories`
573 DROP TABLE IF EXISTS `categories`;
574 CREATE TABLE `categories` (
575 `categorycode` varchar(10) NOT NULL default '',
576 `description` mediumtext,
577 `enrolmentperiod` smallint(6) default NULL,
578 `upperagelimit` smallint(6) default NULL,
579 `dateofbirthrequired` tinyint(1) default NULL,
580 `finetype` varchar(30) default NULL,
581 `bulk` tinyint(1) default NULL,
582 `enrolmentfee` decimal(28,6) default NULL,
583 `overduenoticerequired` tinyint(1) default NULL,
584 `issuelimit` smallint(6) default NULL,
585 `reservefee` decimal(28,6) default NULL,
586 `category_type` varchar(1) NOT NULL default 'A',
587 PRIMARY KEY (`categorycode`),
588 UNIQUE KEY `categorycode` (`categorycode`)
589 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
592 -- Table structure for table `categorytable`
595 DROP TABLE IF EXISTS `categorytable`;
596 CREATE TABLE `categorytable` (
597 `categorycode` varchar(5) NOT NULL default '',
599 `itemtypecodes` text,
600 PRIMARY KEY (`categorycode`)
601 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
604 -- Table structure for table `cities`
607 DROP TABLE IF EXISTS `cities`;
608 CREATE TABLE `cities` (
609 `cityid` int(11) NOT NULL auto_increment,
610 `city_name` varchar(100) NOT NULL default '',
611 `city_zipcode` varchar(20) default NULL,
612 PRIMARY KEY (`cityid`)
613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
616 -- Table structure for table `class_sort_rules`
619 DROP TABLE IF EXISTS `class_sort_rules`;
620 CREATE TABLE `class_sort_rules` (
621 `class_sort_rule` varchar(10) NOT NULL default '',
622 `description` mediumtext,
623 `sort_routine` varchar(30) NOT NULL default '',
624 PRIMARY KEY (`class_sort_rule`),
625 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
626 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
629 -- Table structure for table `class_sources`
632 DROP TABLE IF EXISTS `class_sources`;
633 CREATE TABLE `class_sources` (
634 `cn_source` varchar(10) NOT NULL default '',
635 `description` mediumtext,
636 `used` tinyint(4) NOT NULL default 0,
637 `class_sort_rule` varchar(10) NOT NULL default '',
638 PRIMARY KEY (`cn_source`),
639 UNIQUE KEY `cn_source_idx` (`cn_source`),
640 KEY `used_idx` (`used`),
641 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
642 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
645 -- Table structure for table `currency`
648 DROP TABLE IF EXISTS `currency`;
649 CREATE TABLE `currency` (
650 `currency` varchar(10) NOT NULL default '',
651 `rate` float(7,5) default NULL,
652 PRIMARY KEY (`currency`)
653 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
656 -- Table structure for table `deletedbiblio`
659 DROP TABLE IF EXISTS `deletedbiblio`;
660 CREATE TABLE `deletedbiblio` (
661 `biblionumber` int(11) NOT NULL default 0,
662 `frameworkcode` varchar(4) NOT NULL default '',
665 `unititle` mediumtext,
667 `serial` tinyint(1) default NULL,
668 `seriestitle` mediumtext,
669 `copyrightdate` smallint(6) default NULL,
670 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
671 `datecreated` DATE NOT NULL,
672 `abstract` mediumtext,
673 PRIMARY KEY (`biblionumber`),
674 KEY `blbnoidx` (`biblionumber`)
675 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
678 -- Table structure for table `deletedbiblioitems`
681 DROP TABLE IF EXISTS `deletedbiblioitems`;
682 CREATE TABLE `deletedbiblioitems` (
683 `biblioitemnumber` int(11) NOT NULL default 0,
684 `biblionumber` int(11) NOT NULL default 0,
687 `itemtype` varchar(10) default NULL,
688 `isbn` varchar(14) default NULL,
689 `issn` varchar(9) default NULL,
690 `publicationyear` text,
691 `publishercode` varchar(255) default NULL,
692 `volumedate` date default NULL,
694 `collectiontitle` mediumtext default NULL,
695 `collectionissn` text default NULL,
696 `collectionvolume` mediumtext default NULL,
697 `editionstatement` text default NULL,
698 `editionresponsibility` text default NULL,
699 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
700 `illus` varchar(255) default NULL,
701 `pages` varchar(255) default NULL,
703 `size` varchar(255) default NULL,
704 `place` varchar(255) default NULL,
705 `lccn` varchar(25) default NULL,
707 `url` varchar(255) default NULL,
708 `cn_source` varchar(10) default NULL,
709 `cn_class` varchar(30) default NULL,
710 `cn_item` varchar(10) default NULL,
711 `cn_suffix` varchar(10) default NULL,
712 `cn_sort` varchar(30) default NULL,
713 `totalissues` int(10),
714 `marcxml` longtext NOT NULL,
715 PRIMARY KEY (`biblioitemnumber`),
716 KEY `bibinoidx` (`biblioitemnumber`),
717 KEY `bibnoidx` (`biblionumber`),
719 KEY `publishercode` (`publishercode`)
720 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
723 -- Table structure for table `deletedborrowers`
726 DROP TABLE IF EXISTS `deletedborrowers`;
727 CREATE TABLE `deletedborrowers` (
728 `borrowernumber` int(11) NOT NULL default 0,
729 `cardnumber` varchar(9) NOT NULL default '',
730 `surname` mediumtext NOT NULL,
733 `othernames` mediumtext,
735 `streetnumber` varchar(10) default NULL,
736 `streettype` varchar(50) default NULL,
737 `address` mediumtext NOT NULL,
739 `city` mediumtext NOT NULL,
740 `zipcode` varchar(25) default NULL,
743 `mobile` varchar(50) default NULL,
747 `B_streetnumber` varchar(10) default NULL,
748 `B_streettype` varchar(50) default NULL,
749 `B_address` varchar(100) default NULL,
751 `B_zipcode` varchar(25) default NULL,
753 `B_phone` mediumtext,
754 `dateofbirth` date default NULL,
755 `branchcode` varchar(10) NOT NULL default '',
756 `categorycode` varchar(2) default NULL,
757 `dateenrolled` date default NULL,
758 `dateexpiry` date default NULL,
759 `gonenoaddress` tinyint(1) default NULL,
760 `lost` tinyint(1) default NULL,
761 `debarred` tinyint(1) default NULL,
762 `contactname` mediumtext,
763 `contactfirstname` text,
765 `guarantorid` int(11) default NULL,
766 `borrowernotes` mediumtext,
767 `relationship` varchar(100) default NULL,
768 `ethnicity` varchar(50) default NULL,
769 `ethnotes` varchar(255) default NULL,
770 `sex` varchar(1) default NULL,
771 `password` varchar(30) default NULL,
772 `flags` int(11) default NULL,
773 `userid` varchar(30) default NULL,
774 `opacnote` mediumtext,
775 `contactnote` varchar(255) default NULL,
776 `sort1` varchar(80) default NULL,
777 `sort2` varchar(80) default NULL,
778 KEY `borrowernumber` (`borrowernumber`),
779 KEY `cardnumber` (`cardnumber`)
780 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
783 -- Table structure for table `deleteditems`
786 DROP TABLE IF EXISTS `deleteditems`;
787 CREATE TABLE `deleteditems` (
788 `itemnumber` int(11) NOT NULL default 0,
789 `biblionumber` int(11) NOT NULL default 0,
790 `biblioitemnumber` int(11) NOT NULL default 0,
791 `barcode` varchar(20) default NULL,
792 `dateaccessioned` date default NULL,
793 `booksellerid` varchar(10) default NULL,
794 `homebranch` varchar(10) default NULL,
795 `price` decimal(8,2) default NULL,
796 `replacementprice` decimal(8,2) default NULL,
797 `replacementpricedate` date default NULL,
798 `datelastborrowed` date default NULL,
799 `datelastseen` date default NULL,
800 `stack` tinyint(1) default NULL,
801 `notforloan` tinyint(1) default NULL,
802 `damaged` tinyint(1) default NULL,
803 `itemlost` tinyint(1) default NULL,
804 `wthdrawn` tinyint(1) default NULL,
805 `itemcallnumber` varchar(30) default NULL,
806 `issues` smallint(6) default NULL,
807 `renewals` smallint(6) default NULL,
808 `reserves` smallint(6) default NULL,
809 `restricted` tinyint(1) default NULL,
810 `itemnotes` mediumtext,
811 `holdingbranch` varchar(10) default NULL,
812 `paidfor` mediumtext,
813 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
814 `location` varchar(80) default NULL,
815 `onloan` date default NULL,
816 `cn_source` varchar(10) default NULL,
817 `cn_sort` varchar(30) default NULL,
818 `ccode` varchar(10) default NULL,
819 `materials` varchar(10) default NULL,
820 `uri` varchar(255) default NULL,
821 `itype` varchar(10) default NULL,
823 PRIMARY KEY (`itemnumber`),
824 KEY `delitembarcodeidx` (`barcode`),
825 KEY `delitembinoidx` (`biblioitemnumber`),
826 KEY `delitembibnoidx` (`biblionumber`),
827 KEY `delhomebranch` (`homebranch`),
828 KEY `delholdingbranch` (`holdingbranch`)
829 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
832 -- Table structure for table `ethnicity`
835 DROP TABLE IF EXISTS `ethnicity`;
836 CREATE TABLE `ethnicity` (
837 `code` varchar(10) NOT NULL default '',
838 `name` varchar(255) default NULL,
840 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
843 -- Table structure for table `import_batches`
846 DROP TABLE IF EXISTS `import_batches`;
847 CREATE TABLE `import_batches` (
848 `import_batch_id` int(11) NOT NULL auto_increment,
849 `template_id` int(11) default NULL,
850 `branchcode` varchar(10) default NULL,
851 `num_biblios` int(11) NOT NULL default 0,
852 `num_items` int(11) NOT NULL default 0,
853 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
854 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
855 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
856 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
857 `file_name` varchar(100),
858 `comments` mediumtext,
859 PRIMARY KEY (`import_batch_id`),
860 KEY `branchcode` (`branchcode`)
861 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
864 -- Table structure for table `import_records`
867 DROP TABLE IF EXISTS `import_records`;
868 CREATE TABLE `import_records` (
869 `import_record_id` int(11) NOT NULL auto_increment,
870 `import_batch_id` int(11) NOT NULL,
871 `branchcode` varchar(10) default NULL,
872 `record_sequence` int(11) NOT NULL default 0,
873 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
874 `import_date` DATE default NULL,
875 `marc` longblob NOT NULL,
876 `marcxml` longtext NOT NULL,
877 `marcxml_old` longtext NOT NULL,
878 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
879 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
880 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
881 `import_error` mediumtext,
882 `encoding` varchar(40) NOT NULL default '',
883 `z3950random` varchar(40) default NULL,
884 PRIMARY KEY (`import_record_id`),
885 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
886 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
887 KEY `branchcode` (`branchcode`),
888 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
889 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
892 -- Table structure for `import_record_matches`
894 DROP TABLE IF EXISTS `import_record_matches`;
895 CREATE TABLE `import_record_matches` (
896 `import_record_id` int(11) NOT NULL,
897 `candidate_match_id` int(11) NOT NULL,
898 `score` int(11) NOT NULL default 0,
899 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
900 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
901 KEY `record_score` (`import_record_id`, `score`)
902 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
905 -- Table structure for table `import_biblios`
908 DROP TABLE IF EXISTS `import_biblios`;
909 CREATE TABLE `import_biblios` (
910 `import_record_id` int(11) NOT NULL,
911 `matched_biblionumber` int(11) default NULL,
912 `control_number` varchar(25) default NULL,
913 `original_source` varchar(25) default NULL,
914 `title` varchar(128) default NULL,
915 `author` varchar(80) default NULL,
916 `isbn` varchar(14) default NULL,
917 `issn` varchar(9) default NULL,
918 `has_items` tinyint(1) NOT NULL default 0,
919 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
920 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
921 KEY `matched_biblionumber` (`matched_biblionumber`),
922 KEY `title` (`title`),
924 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
927 -- Table structure for table `import_items`
930 DROP TABLE IF EXISTS `import_items`;
931 CREATE TABLE `import_items` (
932 `import_items_id` int(11) NOT NULL auto_increment,
933 `import_record_id` int(11) NOT NULL,
934 `itemnumber` int(11) default NULL,
935 `branchcode` varchar(10) default NULL,
936 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
937 `marcxml` longtext NOT NULL,
938 `import_error` mediumtext,
939 PRIMARY KEY (`import_items_id`),
940 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
941 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
942 KEY `itemnumber` (`itemnumber`),
943 KEY `branchcode` (`branchcode`)
944 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
947 -- Table structure for table `issues`
950 DROP TABLE IF EXISTS `issues`;
951 CREATE TABLE `issues` (
952 `borrowernumber` int(11) default NULL,
953 `itemnumber` int(11) default NULL,
954 `date_due` date default NULL,
955 `branchcode` varchar(10) default NULL,
956 `issuingbranch` varchar(18) default NULL,
957 `returndate` date default NULL,
958 `lastreneweddate` date default NULL,
959 `return` varchar(4) default NULL,
960 `renewals` tinyint(4) default NULL,
961 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
962 `issuedate` date default NULL,
963 KEY `issuesborridx` (`borrowernumber`),
964 KEY `issuesitemidx` (`itemnumber`),
965 KEY `bordate` (`borrowernumber`,`timestamp`),
966 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
967 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
968 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
971 -- Table structure for table `issuingrules`
974 DROP TABLE IF EXISTS `issuingrules`;
975 CREATE TABLE `issuingrules` (
976 `categorycode` varchar(10) NOT NULL default '',
977 `itemtype` varchar(10) NOT NULL default '',
978 `restrictedtype` tinyint(1) default NULL,
979 `rentaldiscount` decimal(28,6) default NULL,
980 `reservecharge` decimal(28,6) default NULL,
981 `fine` decimal(28,6) default NULL,
982 `firstremind` int(11) default NULL,
983 `chargeperiod` int(11) default NULL,
984 `accountsent` int(11) default NULL,
985 `chargename` varchar(100) default NULL,
986 `maxissueqty` int(4) default NULL,
987 `issuelength` int(4) default NULL,
988 `branchcode` varchar(10) NOT NULL default '',
989 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
990 KEY `categorycode` (`categorycode`),
991 KEY `itemtype` (`itemtype`)
992 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
995 -- Table structure for table `items`
998 DROP TABLE IF EXISTS `items`;
999 CREATE TABLE `items` (
1000 `itemnumber` int(11) NOT NULL auto_increment,
1001 `biblionumber` int(11) NOT NULL default 0,
1002 `biblioitemnumber` int(11) NOT NULL default 0,
1003 `barcode` varchar(20) default NULL,
1004 `dateaccessioned` date default NULL,
1005 `booksellerid` varchar(10) default NULL,
1006 `homebranch` varchar(10) default NULL,
1007 `price` decimal(8,2) default NULL,
1008 `replacementprice` decimal(8,2) default NULL,
1009 `replacementpricedate` date default NULL,
1010 `datelastborrowed` date default NULL,
1011 `datelastseen` date default NULL,
1012 `stack` tinyint(1) default NULL,
1013 `notforloan` tinyint(1) default NULL,
1014 `damaged` tinyint(1) default NULL,
1015 `itemlost` tinyint(1) default NULL,
1016 `wthdrawn` tinyint(1) default NULL,
1017 `itemcallnumber` varchar(30) default NULL,
1018 `issues` smallint(6) default NULL,
1019 `renewals` smallint(6) default NULL,
1020 `reserves` smallint(6) default NULL,
1021 `restricted` tinyint(1) default NULL,
1022 `itemnotes` mediumtext,
1023 `holdingbranch` varchar(10) default NULL,
1024 `paidfor` mediumtext,
1025 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1026 `location` varchar(80) default NULL,
1027 `onloan` date default NULL,
1028 `cn_source` varchar(10) default NULL,
1029 `cn_sort` varchar(30) default NULL,
1030 `ccode` varchar(10) default NULL,
1031 `materials` varchar(10) default NULL,
1032 `uri` varchar(255) default NULL,
1033 `itype` varchar(10) default NULL,
1034 PRIMARY KEY (`itemnumber`),
1035 UNIQUE KEY `itembarcodeidx` (`barcode`),
1036 KEY `itembinoidx` (`biblioitemnumber`),
1037 KEY `itembibnoidx` (`biblionumber`),
1038 KEY `homebranch` (`homebranch`),
1039 KEY `holdingbranch` (`holdingbranch`),
1040 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1041 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1042 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1043 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1046 -- Table structure for table `itemtypes`
1049 DROP TABLE IF EXISTS `itemtypes`;
1050 CREATE TABLE `itemtypes` (
1051 `itemtype` varchar(10) NOT NULL default '',
1052 `description` mediumtext,
1053 `renewalsallowed` smallint(6) default NULL,
1054 `rentalcharge` double(16,4) default NULL,
1055 `notforloan` smallint(6) default NULL,
1056 `imageurl` varchar(200) default NULL,
1058 PRIMARY KEY (`itemtype`),
1059 UNIQUE KEY `itemtype` (`itemtype`)
1060 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1063 -- Table structure for table `labels`
1066 DROP TABLE IF EXISTS `labels`;
1067 CREATE TABLE `labels` (
1068 `labelid` int(11) NOT NULL auto_increment,
1069 `batch_id` varchar(10) NOT NULL default 1,
1070 `itemnumber` varchar(100) NOT NULL default '',
1071 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1072 PRIMARY KEY (`labelid`)
1073 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1076 -- Table structure for table `labels_conf`
1079 DROP TABLE IF EXISTS `labels_conf`;
1080 CREATE TABLE `labels_conf` (
1081 `id` int(4) NOT NULL auto_increment,
1082 `barcodetype` char(100) default '',
1083 `title` int(1) default '0',
1084 `subtitle` int(1) default '0',
1085 `itemtype` int(1) default '0',
1086 `barcode` int(1) default '0',
1087 `dewey` int(1) default '0',
1088 `class` int(1) default NULL,
1089 `subclass` int(1) default '0',
1090 `itemcallnumber` int(1) default '0',
1091 `author` int(1) default '0',
1092 `issn` int(1) default '0',
1093 `isbn` int(1) default '0',
1094 `startlabel` int(2) NOT NULL default '1',
1095 `printingtype` char(32) default 'BAR',
1096 `layoutname` char(20) NOT NULL default 'TEST',
1097 `guidebox` int(1) default '0',
1098 `active` tinyint(1) default '1',
1099 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1100 `ccode` char(4) collate utf8_unicode_ci default NULL,
1101 `callnum_split` int(1) default NULL,
1102 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1104 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1107 -- Table structure for table `labels_templates`
1110 DROP TABLE IF EXISTS `labels_templates`;
1111 CREATE TABLE `labels_templates` (
1112 `tmpl_id` int(4) NOT NULL auto_increment,
1113 `tmpl_code` char(100) default '',
1114 `tmpl_desc` char(100) default '',
1115 `page_width` float default '0',
1116 `page_height` float default '0',
1117 `label_width` float default '0',
1118 `label_height` float default '0',
1119 `topmargin` float default '0',
1120 `leftmargin` float default '0',
1121 `cols` int(2) default '0',
1122 `rows` int(2) default '0',
1123 `colgap` float default '0',
1124 `rowgap` float default '0',
1125 `active` int(1) default NULL,
1126 `units` char(20) default 'PX',
1127 `fontsize` int(4) NOT NULL default '3',
1128 PRIMARY KEY (`tmpl_id`)
1129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1132 -- Table structure for table `letter`
1135 DROP TABLE IF EXISTS `letter`;
1136 CREATE TABLE `letter` (
1137 `module` varchar(20) NOT NULL default '',
1138 `code` varchar(20) NOT NULL default '',
1139 `name` varchar(100) NOT NULL default '',
1140 `title` varchar(200) NOT NULL default '',
1142 PRIMARY KEY (`module`,`code`)
1143 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1146 -- Table structure for table `marc_subfield_structure`
1149 DROP TABLE IF EXISTS `marc_subfield_structure`;
1150 CREATE TABLE `marc_subfield_structure` (
1151 `tagfield` varchar(3) NOT NULL default '',
1152 `tagsubfield` varchar(1) NOT NULL default '',
1153 `liblibrarian` varchar(255) NOT NULL default '',
1154 `libopac` varchar(255) NOT NULL default '',
1155 `repeatable` tinyint(4) NOT NULL default 0,
1156 `mandatory` tinyint(4) NOT NULL default 0,
1157 `kohafield` varchar(40) default NULL,
1158 `tab` tinyint(1) default NULL,
1159 `authorised_value` varchar(20) default NULL,
1160 `authtypecode` varchar(20) default NULL,
1161 `value_builder` varchar(80) default NULL,
1162 `isurl` tinyint(1) default NULL,
1163 `hidden` tinyint(1) default NULL,
1164 `frameworkcode` varchar(4) NOT NULL default '',
1165 `seealso` varchar(1100) default NULL,
1166 `link` varchar(80) default NULL,
1167 `defaultvalue` text default NULL,
1168 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1169 KEY `kohafield_2` (`kohafield`),
1170 KEY `tab` (`frameworkcode`,`tab`),
1171 KEY `kohafield` (`frameworkcode`,`kohafield`)
1172 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1175 -- Table structure for table `marc_tag_structure`
1178 DROP TABLE IF EXISTS `marc_tag_structure`;
1179 CREATE TABLE `marc_tag_structure` (
1180 `tagfield` varchar(3) NOT NULL default '',
1181 `liblibrarian` varchar(255) NOT NULL default '',
1182 `libopac` varchar(255) NOT NULL default '',
1183 `repeatable` tinyint(4) NOT NULL default 0,
1184 `mandatory` tinyint(4) NOT NULL default 0,
1185 `authorised_value` varchar(10) default NULL,
1186 `frameworkcode` varchar(4) NOT NULL default '',
1187 PRIMARY KEY (`frameworkcode`,`tagfield`)
1188 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1191 -- Table structure for table `marc_matchers`
1194 DROP TABLE IF EXISTS `marc_matchers`;
1195 CREATE TABLE `marc_matchers` (
1196 `matcher_id` int(11) NOT NULL auto_increment,
1197 `code` varchar(10) NOT NULL default '',
1198 `description` varchar(255) NOT NULL default '',
1199 `record_type` varchar(10) NOT NULL default 'biblio',
1200 `threshold` int(11) NOT NULL default 0,
1201 PRIMARY KEY (`matcher_id`),
1202 KEY `code` (`code`),
1203 KEY `record_type` (`record_type`)
1204 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1207 -- Table structure for table `matchpoints`
1209 DROP TABLE IF EXISTS `matchpoints`;
1210 CREATE TABLE `matchpoints` (
1211 `matcher_id` int(11) NOT NULL,
1212 `matchpoint_id` int(11) NOT NULL auto_increment,
1213 `search_index` varchar(30) NOT NULL default '',
1214 `score` int(11) NOT NULL default 0,
1215 PRIMARY KEY (`matchpoint_id`),
1216 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1217 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1218 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1222 -- Table structure for table `matchpoint_components`
1224 DROP TABLE IF EXISTS `matchpoint_components`;
1225 CREATE TABLE `matchpoint_components` (
1226 `matchpoint_id` int(11) NOT NULL,
1227 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1228 sequence int(11) NOT NULL default 0,
1229 tag varchar(3) NOT NULL default '',
1230 subfields varchar(40) NOT NULL default '',
1231 offset int(4) NOT NULL default 0,
1232 length int(4) NOT NULL default 0,
1233 PRIMARY KEY (`matchpoint_component_id`),
1234 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1235 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1236 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1237 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1240 -- Table structure for table `matcher_component_norms`
1242 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1243 CREATE TABLE `matchpoint_component_norms` (
1244 `matchpoint_component_id` int(11) NOT NULL,
1245 `sequence` int(11) NOT NULL default 0,
1246 `norm_routine` varchar(50) NOT NULL default '',
1247 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1248 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1249 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1250 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1253 -- Table structure for table `matcher_matchpoints`
1255 DROP TABLE IF EXISTS `matcher_matchpoints`;
1256 CREATE TABLE `matcher_matchpoints` (
1257 `matcher_id` int(11) NOT NULL,
1258 `matchpoint_id` int(11) NOT NULL,
1259 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1260 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1261 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1262 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1266 -- Table structure for table `matchchecks`
1268 DROP TABLE IF EXISTS `matchchecks`;
1269 CREATE TABLE `matchchecks` (
1270 `matcher_id` int(11) NOT NULL,
1271 `matchcheck_id` int(11) NOT NULL auto_increment,
1272 `source_matchpoint_id` int(11) NOT NULL,
1273 `target_matchpoint_id` int(11) NOT NULL,
1274 PRIMARY KEY (`matchcheck_id`),
1275 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1276 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1277 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1278 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1279 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1280 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1284 -- Table structure for table `mediatypetable`
1287 DROP TABLE IF EXISTS `mediatypetable`;
1288 CREATE TABLE `mediatypetable` (
1289 `mediatypecode` varchar(5) NOT NULL default '',
1291 `itemtypecodes` text,
1292 PRIMARY KEY (`mediatypecode`)
1293 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1296 -- Table structure for table `notifys`
1299 DROP TABLE IF EXISTS `notifys`;
1300 CREATE TABLE `notifys` (
1301 `notify_id` int(11) NOT NULL default 0,
1302 `borrowernumber` int(11) NOT NULL default 0,
1303 `itemnumber` int(11) NOT NULL default 0,
1304 `notify_date` date default NULL,
1305 `notify_send_date` date default NULL,
1306 `notify_level` int(1) NOT NULL default 0,
1307 `method` varchar(20) NOT NULL default ''
1308 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1311 -- Table structure for table `nozebra`
1313 DROP TABLE IF EXISTS `nozebra`;
1314 CREATE TABLE `nozebra` (
1315 `server` varchar(20) NOT NULL,
1316 `indexname` varchar(40) NOT NULL,
1317 `value` varchar(250) NOT NULL,
1318 `biblionumbers` longtext NOT NULL,
1319 KEY `indexname` (`server`,`indexname`),
1320 KEY `value` (`server`,`value`))
1321 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1324 -- Table structure for table `opac_news`
1327 DROP TABLE IF EXISTS `opac_news`;
1328 CREATE TABLE `opac_news` (
1329 `idnew` int(10) unsigned NOT NULL auto_increment,
1330 `title` varchar(250) NOT NULL default '',
1331 `new` text NOT NULL,
1332 `lang` varchar(4) NOT NULL default '',
1333 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1334 `expirationdate` date default NULL,
1335 `number` int(11) default NULL,
1336 PRIMARY KEY (`idnew`)
1337 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1340 -- Table structure for table `overduerules`
1343 DROP TABLE IF EXISTS `overduerules`;
1344 CREATE TABLE `overduerules` (
1345 `branchcode` varchar(10) NOT NULL default '',
1346 `categorycode` varchar(2) NOT NULL default '',
1347 `delay1` int(4) default 0,
1348 `letter1` varchar(20) default NULL,
1349 `debarred1` varchar(1) default 0,
1350 `delay2` int(4) default 0,
1351 `debarred2` varchar(1) default 0,
1352 `letter2` varchar(20) default NULL,
1353 `delay3` int(4) default 0,
1354 `letter3` varchar(20) default NULL,
1355 `debarred3` int(1) default 0,
1356 PRIMARY KEY (`branchcode`,`categorycode`)
1357 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1360 -- Table structure for table `printers`
1363 DROP TABLE IF EXISTS `printers`;
1364 CREATE TABLE `printers` (
1365 `printername` varchar(40) NOT NULL default '',
1366 `printqueue` varchar(20) default NULL,
1367 `printtype` varchar(20) default NULL,
1368 PRIMARY KEY (`printername`)
1369 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1372 -- Table structure for table `repeatable_holidays`
1375 DROP TABLE IF EXISTS `repeatable_holidays`;
1376 CREATE TABLE `repeatable_holidays` (
1377 `id` int(11) NOT NULL auto_increment,
1378 `branchcode` varchar(10) NOT NULL default '',
1379 `weekday` smallint(6) default NULL,
1380 `day` smallint(6) default NULL,
1381 `month` smallint(6) default NULL,
1382 `title` varchar(50) NOT NULL default '',
1383 `description` text NOT NULL,
1385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1388 -- Table structure for table `reports_dictionary`
1391 DROP TABLE IF EXISTS `reports_dictionary`;
1392 CREATE TABLE reports_dictionary (
1393 `id` int(11) NOT NULL auto_increment,
1394 `name` varchar(255) default NULL,
1396 `date_created` datetime default NULL,
1397 `date_modified` datetime default NULL,
1399 `area` int(11) default NULL,
1401 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1404 -- Table structure for table `reserveconstraints`
1407 DROP TABLE IF EXISTS `reserveconstraints`;
1408 CREATE TABLE `reserveconstraints` (
1409 `borrowernumber` int(11) NOT NULL default 0,
1410 `reservedate` date default NULL,
1411 `biblionumber` int(11) NOT NULL default 0,
1412 `biblioitemnumber` int(11) default NULL,
1413 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1414 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1417 -- Table structure for table `reserves`
1420 DROP TABLE IF EXISTS `reserves`;
1421 CREATE TABLE `reserves` (
1422 `borrowernumber` int(11) NOT NULL default 0,
1423 `reservedate` date default NULL,
1424 `biblionumber` int(11) NOT NULL default 0,
1425 `constrainttype` varchar(1) default NULL,
1426 `branchcode` varchar(10) default NULL,
1427 `notificationdate` date default NULL,
1428 `reminderdate` date default NULL,
1429 `cancellationdate` date default NULL,
1430 `reservenotes` mediumtext,
1431 `priority` smallint(6) default NULL,
1432 `found` varchar(1) default NULL,
1433 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1434 `itemnumber` int(11) default NULL,
1435 `waitingdate` date default NULL,
1436 KEY `borrowernumber` (`borrowernumber`),
1437 KEY `biblionumber` (`biblionumber`),
1438 KEY `itemnumber` (`itemnumber`),
1439 KEY `branchcode` (`branchcode`),
1440 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1441 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1442 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1443 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1444 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1447 -- Table structure for table `reviews`
1450 DROP TABLE IF EXISTS `reviews`;
1451 CREATE TABLE `reviews` (
1452 `reviewid` int(11) NOT NULL auto_increment,
1453 `borrowernumber` int(11) default NULL,
1454 `biblionumber` int(11) default NULL,
1456 `approved` tinyint(4) default NULL,
1457 `datereviewed` datetime default NULL,
1458 PRIMARY KEY (`reviewid`)
1459 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1462 -- Table structure for table `roadtype`
1465 DROP TABLE IF EXISTS `roadtype`;
1466 CREATE TABLE `roadtype` (
1467 `roadtypeid` int(11) NOT NULL auto_increment,
1468 `road_type` varchar(100) NOT NULL default '',
1469 PRIMARY KEY (`roadtypeid`)
1470 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1473 -- Table structure for table `saved_sql`
1476 DROP TABLE IF EXISTS `saved_sql`;
1477 CREATE TABLE saved_sql (
1478 `id` int(11) NOT NULL auto_increment,
1479 `borrowernumber` int(11) default NULL,
1480 `date_created` datetime default NULL,
1481 `last_modified` datetime default NULL,
1483 `last_run` datetime default NULL,
1484 `report_name` varchar(255) default NULL,
1485 `type` varchar(255) default NULL,
1488 KEY boridx (`borrowernumber`)
1489 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1493 -- Table structure for `saved_reports`
1496 DROP TABLE IF EXISTS `saved_reports`;
1497 CREATE TABLE saved_reports (
1498 `id` int(11) NOT NULL auto_increment,
1499 `report_id` int(11) default NULL,
1501 `date_run` datetime default NULL,
1503 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1507 -- Table structure for table `serial`
1510 DROP TABLE IF EXISTS `serial`;
1511 CREATE TABLE `serial` (
1512 `serialid` int(11) NOT NULL auto_increment,
1513 `biblionumber` varchar(100) NOT NULL default '',
1514 `subscriptionid` varchar(100) NOT NULL default '',
1515 `serialseq` varchar(100) NOT NULL default '',
1516 `status` tinyint(4) NOT NULL default 0,
1517 `planneddate` date default NULL,
1519 `publisheddate` date default NULL,
1521 `claimdate` date default NULL,
1522 `routingnotes` text,
1523 PRIMARY KEY (`serialid`)
1524 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1527 -- Table structure for table `sessions`
1530 DROP TABLE IF EXISTS sessions;
1531 CREATE TABLE sessions (
1532 `id` varchar(32) NOT NULL,
1533 `a_session` text NOT NULL,
1535 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1538 -- Table structure for table `special_holidays`
1541 DROP TABLE IF EXISTS `special_holidays`;
1542 CREATE TABLE `special_holidays` (
1543 `id` int(11) NOT NULL auto_increment,
1544 `branchcode` varchar(10) NOT NULL default '',
1545 `day` smallint(6) NOT NULL default 0,
1546 `month` smallint(6) NOT NULL default 0,
1547 `year` smallint(6) NOT NULL default 0,
1548 `isexception` smallint(1) NOT NULL default 1,
1549 `title` varchar(50) NOT NULL default '',
1550 `description` text NOT NULL,
1552 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1555 -- Table structure for table `statistics`
1558 DROP TABLE IF EXISTS `statistics`;
1559 CREATE TABLE `statistics` (
1560 `datetime` datetime default NULL,
1561 `branch` varchar(10) default NULL,
1562 `proccode` varchar(4) default NULL,
1563 `value` double(16,4) default NULL,
1564 `type` varchar(16) default NULL,
1566 `usercode` varchar(10) default NULL,
1567 `itemnumber` int(11) default NULL,
1568 `itemtype` varchar(10) default NULL,
1569 `borrowernumber` int(11) default NULL,
1570 `associatedborrower` int(11) default NULL,
1571 KEY `timeidx` (`datetime`)
1572 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1575 -- Table structure for table `stopwords`
1578 DROP TABLE IF EXISTS `stopwords`;
1579 CREATE TABLE `stopwords` (
1580 `word` varchar(255) default NULL
1581 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1584 -- Table structure for table `subcategorytable`
1587 DROP TABLE IF EXISTS `subcategorytable`;
1588 CREATE TABLE `subcategorytable` (
1589 `subcategorycode` varchar(5) NOT NULL default '',
1591 `itemtypecodes` text,
1592 PRIMARY KEY (`subcategorycode`)
1593 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1596 -- Table structure for table `subscription`
1599 DROP TABLE IF EXISTS `subscription`;
1600 CREATE TABLE `subscription` (
1601 `biblionumber` int(11) NOT NULL default 0,
1602 `subscriptionid` int(11) NOT NULL auto_increment,
1603 `librarian` varchar(100) default '',
1604 `startdate` date default NULL,
1605 `aqbooksellerid` int(11) default 0,
1606 `cost` int(11) default 0,
1607 `aqbudgetid` int(11) default 0,
1608 `weeklength` tinyint(4) default 0,
1609 `monthlength` tinyint(4) default 0,
1610 `numberlength` tinyint(4) default 0,
1611 `periodicity` tinyint(4) default 0,
1612 `dow` varchar(100) default '',
1613 `numberingmethod` varchar(100) default '',
1615 `status` varchar(100) NOT NULL default '',
1616 `add1` int(11) default 0,
1617 `every1` int(11) default 0,
1618 `whenmorethan1` int(11) default 0,
1619 `setto1` int(11) default NULL,
1620 `lastvalue1` int(11) default NULL,
1621 `add2` int(11) default 0,
1622 `every2` int(11) default 0,
1623 `whenmorethan2` int(11) default 0,
1624 `setto2` int(11) default NULL,
1625 `lastvalue2` int(11) default NULL,
1626 `add3` int(11) default 0,
1627 `every3` int(11) default 0,
1628 `innerloop1` int(11) default 0,
1629 `innerloop2` int(11) default 0,
1630 `innerloop3` int(11) default 0,
1631 `whenmorethan3` int(11) default 0,
1632 `setto3` int(11) default NULL,
1633 `lastvalue3` int(11) default NULL,
1634 `issuesatonce` tinyint(3) NOT NULL default 1,
1635 `firstacquidate` date default NULL,
1636 `manualhistory` tinyint(1) NOT NULL default 0,
1637 `irregularity` text,
1638 `letter` varchar(20) default NULL,
1639 `numberpattern` tinyint(3) default 0,
1640 `distributedto` text,
1641 `internalnotes` longtext,
1643 `branchcode` varchar(10) NOT NULL default '',
1644 `hemisphere` tinyint(3) default 0,
1645 `lastbranch` varchar(10),
1646 PRIMARY KEY (`subscriptionid`)
1647 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1650 -- Table structure for table `subscriptionhistory`
1653 DROP TABLE IF EXISTS `subscriptionhistory`;
1654 CREATE TABLE `subscriptionhistory` (
1655 `biblionumber` int(11) NOT NULL default 0,
1656 `subscriptionid` int(11) NOT NULL default 0,
1657 `histstartdate` date default NULL,
1658 `enddate` date default NULL,
1659 `missinglist` longtext NOT NULL,
1660 `recievedlist` longtext NOT NULL,
1661 `opacnote` varchar(150) NOT NULL default '',
1662 `librariannote` varchar(150) NOT NULL default '',
1663 PRIMARY KEY (`subscriptionid`),
1664 KEY `biblionumber` (`biblionumber`)
1665 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1668 -- Table structure for table `subscriptionroutinglist`
1671 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1672 CREATE TABLE `subscriptionroutinglist` (
1673 `routingid` int(11) NOT NULL auto_increment,
1674 `borrowernumber` int(11) default NULL,
1675 `ranking` int(11) default NULL,
1676 `subscriptionid` int(11) default NULL,
1677 PRIMARY KEY (`routingid`)
1678 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1681 -- Table structure for table `suggestions`
1684 DROP TABLE IF EXISTS `suggestions`;
1685 CREATE TABLE `suggestions` (
1686 `suggestionid` int(8) NOT NULL auto_increment,
1687 `suggestedby` int(11) NOT NULL default 0,
1688 `managedby` int(11) default NULL,
1689 `STATUS` varchar(10) NOT NULL default '',
1691 `author` varchar(80) default NULL,
1692 `title` varchar(80) default NULL,
1693 `copyrightdate` smallint(6) default NULL,
1694 `publishercode` varchar(255) default NULL,
1695 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1696 `volumedesc` varchar(255) default NULL,
1697 `publicationyear` smallint(6) default 0,
1698 `place` varchar(255) default NULL,
1699 `isbn` varchar(10) default NULL,
1700 `mailoverseeing` smallint(1) default 0,
1701 `biblionumber` int(11) default NULL,
1703 PRIMARY KEY (`suggestionid`),
1704 KEY `suggestedby` (`suggestedby`),
1705 KEY `managedby` (`managedby`)
1706 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1709 -- Table structure for table `systempreferences`
1712 DROP TABLE IF EXISTS `systempreferences`;
1713 CREATE TABLE `systempreferences` (
1714 `variable` varchar(50) NOT NULL default '',
1716 `options` mediumtext,
1718 `type` varchar(20) default NULL,
1719 PRIMARY KEY (`variable`)
1720 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1723 -- Table structure for table `tags`
1726 DROP TABLE IF EXISTS `tags`;
1727 CREATE TABLE `tags` (
1728 `entry` varchar(255) NOT NULL default '',
1729 `weight` bigint(20) NOT NULL default 0,
1730 PRIMARY KEY (`entry`)
1731 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1734 -- Table structure for table `userflags`
1737 DROP TABLE IF EXISTS `userflags`;
1738 CREATE TABLE `userflags` (
1739 `bit` int(11) NOT NULL default 0,
1740 `flag` varchar(30) default NULL,
1741 `flagdesc` varchar(255) default NULL,
1742 `defaulton` int(11) default NULL,
1744 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1747 -- Table structure for table `virtualshelves`
1750 DROP TABLE IF EXISTS `virtualshelves`;
1751 CREATE TABLE `virtualshelves` (
1752 `shelfnumber` int(11) NOT NULL auto_increment,
1753 `shelfname` varchar(255) default NULL,
1754 `owner` varchar(80) default NULL,
1755 `category` varchar(1) default NULL,
1756 PRIMARY KEY (`shelfnumber`)
1757 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1760 -- Table structure for table `virtualshelfcontents`
1763 DROP TABLE IF EXISTS `virtualshelfcontents`;
1764 CREATE TABLE `virtualshelfcontents` (
1765 `shelfnumber` int(11) NOT NULL default 0,
1766 `biblionumber` int(11) NOT NULL default 0,
1767 `flags` int(11) default NULL,
1768 `dateadded` timestamp NULL default NULL,
1769 KEY `shelfnumber` (`shelfnumber`),
1770 KEY `biblionumber` (`biblionumber`),
1771 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1772 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1773 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1776 -- Table structure for table `z3950servers`
1779 DROP TABLE IF EXISTS `z3950servers`;
1780 CREATE TABLE `z3950servers` (
1781 `host` varchar(255) default NULL,
1782 `port` int(11) default NULL,
1783 `db` varchar(255) default NULL,
1784 `userid` varchar(255) default NULL,
1785 `password` varchar(255) default NULL,
1787 `id` int(11) NOT NULL auto_increment,
1788 `checked` smallint(6) default NULL,
1789 `rank` int(11) default NULL,
1790 `syntax` varchar(80) default NULL,
1792 `position` enum('primary','secondary','') NOT NULL default 'primary',
1793 `type` enum('zed','opensearch') NOT NULL default 'zed',
1794 `description` text NOT NULL,
1796 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1799 -- Table structure for table `zebraqueue`
1802 DROP TABLE IF EXISTS `zebraqueue`;
1803 CREATE TABLE `zebraqueue` (
1804 `id` int(11) NOT NULL auto_increment,
1805 `biblio_auth_number` int(11) NOT NULL default '0',
1806 `operation` char(20) NOT NULL default '',
1807 `server` char(20) NOT NULL default '',
1808 `done` int(11) NOT NULL default '0',
1809 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1811 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1813 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1814 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1815 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1816 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1817 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1818 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1819 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1820 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;