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 `description` varchar(255) NOT NULL default '',
1198 `record_type` varchar(10) NOT NULL default 'biblio',
1199 `threshold` int(11) NOT NULL default 0,
1200 PRIMARY KEY (`matcher_id`)
1201 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1204 -- Table structure for table `matchpoints`
1206 DROP TABLE IF EXISTS `matchpoints`;
1207 CREATE TABLE `matchpoints` (
1208 `matcher_id` int(11) NOT NULL,
1209 `matchpoint_id` int(11) NOT NULL auto_increment,
1210 `search_index` varchar(30) NOT NULL default '',
1211 `score` int(11) NOT NULL default 0,
1212 PRIMARY KEY (`matchpoint_id`),
1213 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1214 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1215 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1219 -- Table structure for table `matchpoint_components`
1221 DROP TABLE IF EXISTS `matchpoint_components`;
1222 CREATE TABLE `matchpoint_components` (
1223 `matchpoint_id` int(11) NOT NULL,
1224 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1225 sequence int(11) NOT NULL default 0,
1226 tag varchar(3) NOT NULL default '',
1227 subfields varchar(40) NOT NULL default '',
1228 offset int(4) NOT NULL default 0,
1229 length int(4) NOT NULL default 0,
1230 PRIMARY KEY (`matchpoint_component_id`),
1231 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1232 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1233 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1236 -- Table structure for table `matcher_component_norms`
1238 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1239 CREATE TABLE `matchpoint_component_norms` (
1240 `matchpoint_component_id` int(11) NOT NULL,
1241 `sequence` int(11) NOT NULL default 0,
1242 `norm_routine` varchar(50) NOT NULL default '',
1243 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1244 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1245 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1246 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1249 -- Table structure for table `matcher_matchpoints`
1251 DROP TABLE IF EXISTS `matcher_matchpoints`;
1252 CREATE TABLE `matcher_matchpoints` (
1253 `matcher_id` int(11) NOT NULL,
1254 `matchpoint_id` int(11) NOT NULL,
1255 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1256 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1257 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1258 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1262 -- Table structure for table `matchchecks`
1264 DROP TABLE IF EXISTS `matchchecks`;
1265 CREATE TABLE `matchchecks` (
1266 `matcher_id` int(11) NOT NULL,
1267 `matchcheck_id` int(11) NOT NULL auto_increment,
1268 `source_matchpoint_id` int(11) NOT NULL,
1269 `target_matchpoint_id` int(11) NOT NULL,
1270 PRIMARY KEY (`matchcheck_id`),
1271 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1272 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1273 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1274 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1275 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1276 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1280 -- Table structure for table `mediatypetable`
1283 DROP TABLE IF EXISTS `mediatypetable`;
1284 CREATE TABLE `mediatypetable` (
1285 `mediatypecode` varchar(5) NOT NULL default '',
1287 `itemtypecodes` text,
1288 PRIMARY KEY (`mediatypecode`)
1289 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1292 -- Table structure for table `notifys`
1295 DROP TABLE IF EXISTS `notifys`;
1296 CREATE TABLE `notifys` (
1297 `notify_id` int(11) NOT NULL default 0,
1298 `borrowernumber` int(11) NOT NULL default 0,
1299 `itemnumber` int(11) NOT NULL default 0,
1300 `notify_date` date default NULL,
1301 `notify_send_date` date default NULL,
1302 `notify_level` int(1) NOT NULL default 0,
1303 `method` varchar(20) NOT NULL default ''
1304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1307 -- Table structure for table `nozebra`
1309 DROP TABLE IF EXISTS `nozebra`;
1310 CREATE TABLE `nozebra` (
1311 `server` varchar(20) NOT NULL,
1312 `indexname` varchar(40) NOT NULL,
1313 `value` varchar(250) NOT NULL,
1314 `biblionumbers` longtext NOT NULL,
1315 KEY `indexname` (`server`,`indexname`),
1316 KEY `value` (`server`,`value`))
1317 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1320 -- Table structure for table `opac_news`
1323 DROP TABLE IF EXISTS `opac_news`;
1324 CREATE TABLE `opac_news` (
1325 `idnew` int(10) unsigned NOT NULL auto_increment,
1326 `title` varchar(250) NOT NULL default '',
1327 `new` text NOT NULL,
1328 `lang` varchar(4) NOT NULL default '',
1329 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1330 `expirationdate` date default NULL,
1331 `number` int(11) default NULL,
1332 PRIMARY KEY (`idnew`)
1333 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1336 -- Table structure for table `overduerules`
1339 DROP TABLE IF EXISTS `overduerules`;
1340 CREATE TABLE `overduerules` (
1341 `branchcode` varchar(10) NOT NULL default '',
1342 `categorycode` varchar(2) NOT NULL default '',
1343 `delay1` int(4) default 0,
1344 `letter1` varchar(20) default NULL,
1345 `debarred1` varchar(1) default 0,
1346 `delay2` int(4) default 0,
1347 `debarred2` varchar(1) default 0,
1348 `letter2` varchar(20) default NULL,
1349 `delay3` int(4) default 0,
1350 `letter3` varchar(20) default NULL,
1351 `debarred3` int(1) default 0,
1352 PRIMARY KEY (`branchcode`,`categorycode`)
1353 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1356 -- Table structure for table `printers`
1359 DROP TABLE IF EXISTS `printers`;
1360 CREATE TABLE `printers` (
1361 `printername` varchar(40) NOT NULL default '',
1362 `printqueue` varchar(20) default NULL,
1363 `printtype` varchar(20) default NULL,
1364 PRIMARY KEY (`printername`)
1365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1368 -- Table structure for table `repeatable_holidays`
1371 DROP TABLE IF EXISTS `repeatable_holidays`;
1372 CREATE TABLE `repeatable_holidays` (
1373 `id` int(11) NOT NULL auto_increment,
1374 `branchcode` varchar(10) NOT NULL default '',
1375 `weekday` smallint(6) default NULL,
1376 `day` smallint(6) default NULL,
1377 `month` smallint(6) default NULL,
1378 `title` varchar(50) NOT NULL default '',
1379 `description` text NOT NULL,
1381 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1384 -- Table structure for table `reports_dictionary`
1387 DROP TABLE IF EXISTS `reports_dictionary`;
1388 CREATE TABLE reports_dictionary (
1389 `id` int(11) NOT NULL auto_increment,
1390 `name` varchar(255) default NULL,
1392 `date_created` datetime default NULL,
1393 `date_modified` datetime default NULL,
1395 `area` int(11) default NULL,
1397 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1400 -- Table structure for table `reserveconstraints`
1403 DROP TABLE IF EXISTS `reserveconstraints`;
1404 CREATE TABLE `reserveconstraints` (
1405 `borrowernumber` int(11) NOT NULL default 0,
1406 `reservedate` date default NULL,
1407 `biblionumber` int(11) NOT NULL default 0,
1408 `biblioitemnumber` int(11) default NULL,
1409 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1410 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1413 -- Table structure for table `reserves`
1416 DROP TABLE IF EXISTS `reserves`;
1417 CREATE TABLE `reserves` (
1418 `borrowernumber` int(11) NOT NULL default 0,
1419 `reservedate` date default NULL,
1420 `biblionumber` int(11) NOT NULL default 0,
1421 `constrainttype` varchar(1) default NULL,
1422 `branchcode` varchar(10) default NULL,
1423 `notificationdate` date default NULL,
1424 `reminderdate` date default NULL,
1425 `cancellationdate` date default NULL,
1426 `reservenotes` mediumtext,
1427 `priority` smallint(6) default NULL,
1428 `found` varchar(1) default NULL,
1429 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1430 `itemnumber` int(11) default NULL,
1431 `waitingdate` date default NULL,
1432 KEY `borrowernumber` (`borrowernumber`),
1433 KEY `biblionumber` (`biblionumber`),
1434 KEY `itemnumber` (`itemnumber`),
1435 KEY `branchcode` (`branchcode`),
1436 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1437 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1438 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1439 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1440 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1443 -- Table structure for table `reviews`
1446 DROP TABLE IF EXISTS `reviews`;
1447 CREATE TABLE `reviews` (
1448 `reviewid` int(11) NOT NULL auto_increment,
1449 `borrowernumber` int(11) default NULL,
1450 `biblionumber` int(11) default NULL,
1452 `approved` tinyint(4) default NULL,
1453 `datereviewed` datetime default NULL,
1454 PRIMARY KEY (`reviewid`)
1455 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1458 -- Table structure for table `roadtype`
1461 DROP TABLE IF EXISTS `roadtype`;
1462 CREATE TABLE `roadtype` (
1463 `roadtypeid` int(11) NOT NULL auto_increment,
1464 `road_type` varchar(100) NOT NULL default '',
1465 PRIMARY KEY (`roadtypeid`)
1466 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1469 -- Table structure for table `saved_sql`
1472 DROP TABLE IF EXISTS `saved_sql`;
1473 CREATE TABLE saved_sql (
1474 `id` int(11) NOT NULL auto_increment,
1475 `borrowernumber` int(11) default NULL,
1476 `date_created` datetime default NULL,
1477 `last_modified` datetime default NULL,
1479 `last_run` datetime default NULL,
1480 `report_name` varchar(255) default NULL,
1481 `type` varchar(255) default NULL,
1484 KEY boridx (`borrowernumber`)
1485 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1489 -- Table structure for `saved_reports`
1492 DROP TABLE IF EXISTS `saved_reports`;
1493 CREATE TABLE saved_reports (
1494 `id` int(11) NOT NULL auto_increment,
1495 `report_id` int(11) default NULL,
1497 `date_run` datetime default NULL,
1499 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1503 -- Table structure for table `serial`
1506 DROP TABLE IF EXISTS `serial`;
1507 CREATE TABLE `serial` (
1508 `serialid` int(11) NOT NULL auto_increment,
1509 `biblionumber` varchar(100) NOT NULL default '',
1510 `subscriptionid` varchar(100) NOT NULL default '',
1511 `serialseq` varchar(100) NOT NULL default '',
1512 `status` tinyint(4) NOT NULL default 0,
1513 `planneddate` date default NULL,
1515 `publisheddate` date default NULL,
1517 `claimdate` date default NULL,
1518 `routingnotes` text,
1519 PRIMARY KEY (`serialid`)
1520 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1523 -- Table structure for table `sessions`
1526 DROP TABLE IF EXISTS sessions;
1527 CREATE TABLE sessions (
1528 `id` varchar(32) NOT NULL,
1529 `a_session` text NOT NULL,
1531 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1534 -- Table structure for table `special_holidays`
1537 DROP TABLE IF EXISTS `special_holidays`;
1538 CREATE TABLE `special_holidays` (
1539 `id` int(11) NOT NULL auto_increment,
1540 `branchcode` varchar(10) NOT NULL default '',
1541 `day` smallint(6) NOT NULL default 0,
1542 `month` smallint(6) NOT NULL default 0,
1543 `year` smallint(6) NOT NULL default 0,
1544 `isexception` smallint(1) NOT NULL default 1,
1545 `title` varchar(50) NOT NULL default '',
1546 `description` text NOT NULL,
1548 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1551 -- Table structure for table `statistics`
1554 DROP TABLE IF EXISTS `statistics`;
1555 CREATE TABLE `statistics` (
1556 `datetime` datetime default NULL,
1557 `branch` varchar(10) default NULL,
1558 `proccode` varchar(4) default NULL,
1559 `value` double(16,4) default NULL,
1560 `type` varchar(16) default NULL,
1562 `usercode` varchar(10) default NULL,
1563 `itemnumber` int(11) default NULL,
1564 `itemtype` varchar(10) default NULL,
1565 `borrowernumber` int(11) default NULL,
1566 `associatedborrower` int(11) default NULL,
1567 KEY `timeidx` (`datetime`)
1568 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1571 -- Table structure for table `stopwords`
1574 DROP TABLE IF EXISTS `stopwords`;
1575 CREATE TABLE `stopwords` (
1576 `word` varchar(255) default NULL
1577 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1580 -- Table structure for table `subcategorytable`
1583 DROP TABLE IF EXISTS `subcategorytable`;
1584 CREATE TABLE `subcategorytable` (
1585 `subcategorycode` varchar(5) NOT NULL default '',
1587 `itemtypecodes` text,
1588 PRIMARY KEY (`subcategorycode`)
1589 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1592 -- Table structure for table `subscription`
1595 DROP TABLE IF EXISTS `subscription`;
1596 CREATE TABLE `subscription` (
1597 `biblionumber` int(11) NOT NULL default 0,
1598 `subscriptionid` int(11) NOT NULL auto_increment,
1599 `librarian` varchar(100) default '',
1600 `startdate` date default NULL,
1601 `aqbooksellerid` int(11) default 0,
1602 `cost` int(11) default 0,
1603 `aqbudgetid` int(11) default 0,
1604 `weeklength` tinyint(4) default 0,
1605 `monthlength` tinyint(4) default 0,
1606 `numberlength` tinyint(4) default 0,
1607 `periodicity` tinyint(4) default 0,
1608 `dow` varchar(100) default '',
1609 `numberingmethod` varchar(100) default '',
1611 `status` varchar(100) NOT NULL default '',
1612 `add1` int(11) default 0,
1613 `every1` int(11) default 0,
1614 `whenmorethan1` int(11) default 0,
1615 `setto1` int(11) default NULL,
1616 `lastvalue1` int(11) default NULL,
1617 `add2` int(11) default 0,
1618 `every2` int(11) default 0,
1619 `whenmorethan2` int(11) default 0,
1620 `setto2` int(11) default NULL,
1621 `lastvalue2` int(11) default NULL,
1622 `add3` int(11) default 0,
1623 `every3` int(11) default 0,
1624 `innerloop1` int(11) default 0,
1625 `innerloop2` int(11) default 0,
1626 `innerloop3` int(11) default 0,
1627 `whenmorethan3` int(11) default 0,
1628 `setto3` int(11) default NULL,
1629 `lastvalue3` int(11) default NULL,
1630 `issuesatonce` tinyint(3) NOT NULL default 1,
1631 `firstacquidate` date default NULL,
1632 `manualhistory` tinyint(1) NOT NULL default 0,
1633 `irregularity` text,
1634 `letter` varchar(20) default NULL,
1635 `numberpattern` tinyint(3) default 0,
1636 `distributedto` text,
1637 `internalnotes` longtext,
1639 `branchcode` varchar(10) NOT NULL default '',
1640 `hemisphere` tinyint(3) default 0,
1641 `lastbranch` varchar(10),
1642 PRIMARY KEY (`subscriptionid`)
1643 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1646 -- Table structure for table `subscriptionhistory`
1649 DROP TABLE IF EXISTS `subscriptionhistory`;
1650 CREATE TABLE `subscriptionhistory` (
1651 `biblionumber` int(11) NOT NULL default 0,
1652 `subscriptionid` int(11) NOT NULL default 0,
1653 `histstartdate` date default NULL,
1654 `enddate` date default NULL,
1655 `missinglist` longtext NOT NULL,
1656 `recievedlist` longtext NOT NULL,
1657 `opacnote` varchar(150) NOT NULL default '',
1658 `librariannote` varchar(150) NOT NULL default '',
1659 PRIMARY KEY (`subscriptionid`),
1660 KEY `biblionumber` (`biblionumber`)
1661 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1664 -- Table structure for table `subscriptionroutinglist`
1667 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1668 CREATE TABLE `subscriptionroutinglist` (
1669 `routingid` int(11) NOT NULL auto_increment,
1670 `borrowernumber` int(11) default NULL,
1671 `ranking` int(11) default NULL,
1672 `subscriptionid` int(11) default NULL,
1673 PRIMARY KEY (`routingid`)
1674 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1677 -- Table structure for table `suggestions`
1680 DROP TABLE IF EXISTS `suggestions`;
1681 CREATE TABLE `suggestions` (
1682 `suggestionid` int(8) NOT NULL auto_increment,
1683 `suggestedby` int(11) NOT NULL default 0,
1684 `managedby` int(11) default NULL,
1685 `STATUS` varchar(10) NOT NULL default '',
1687 `author` varchar(80) default NULL,
1688 `title` varchar(80) default NULL,
1689 `copyrightdate` smallint(6) default NULL,
1690 `publishercode` varchar(255) default NULL,
1691 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1692 `volumedesc` varchar(255) default NULL,
1693 `publicationyear` smallint(6) default 0,
1694 `place` varchar(255) default NULL,
1695 `isbn` varchar(10) default NULL,
1696 `mailoverseeing` smallint(1) default 0,
1697 `biblionumber` int(11) default NULL,
1699 PRIMARY KEY (`suggestionid`),
1700 KEY `suggestedby` (`suggestedby`),
1701 KEY `managedby` (`managedby`)
1702 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1705 -- Table structure for table `systempreferences`
1708 DROP TABLE IF EXISTS `systempreferences`;
1709 CREATE TABLE `systempreferences` (
1710 `variable` varchar(50) NOT NULL default '',
1712 `options` mediumtext,
1714 `type` varchar(20) default NULL,
1715 PRIMARY KEY (`variable`)
1716 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1719 -- Table structure for table `tags`
1722 DROP TABLE IF EXISTS `tags`;
1723 CREATE TABLE `tags` (
1724 `entry` varchar(255) NOT NULL default '',
1725 `weight` bigint(20) NOT NULL default 0,
1726 PRIMARY KEY (`entry`)
1727 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1730 -- Table structure for table `userflags`
1733 DROP TABLE IF EXISTS `userflags`;
1734 CREATE TABLE `userflags` (
1735 `bit` int(11) NOT NULL default 0,
1736 `flag` varchar(30) default NULL,
1737 `flagdesc` varchar(255) default NULL,
1738 `defaulton` int(11) default NULL,
1740 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1743 -- Table structure for table `virtualshelves`
1746 DROP TABLE IF EXISTS `virtualshelves`;
1747 CREATE TABLE `virtualshelves` (
1748 `shelfnumber` int(11) NOT NULL auto_increment,
1749 `shelfname` varchar(255) default NULL,
1750 `owner` varchar(80) default NULL,
1751 `category` varchar(1) default NULL,
1752 PRIMARY KEY (`shelfnumber`)
1753 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1756 -- Table structure for table `virtualshelfcontents`
1759 DROP TABLE IF EXISTS `virtualshelfcontents`;
1760 CREATE TABLE `virtualshelfcontents` (
1761 `shelfnumber` int(11) NOT NULL default 0,
1762 `biblionumber` int(11) NOT NULL default 0,
1763 `flags` int(11) default NULL,
1764 `dateadded` timestamp NULL default NULL,
1765 KEY `shelfnumber` (`shelfnumber`),
1766 KEY `biblionumber` (`biblionumber`),
1767 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1768 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1769 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1772 -- Table structure for table `z3950servers`
1775 DROP TABLE IF EXISTS `z3950servers`;
1776 CREATE TABLE `z3950servers` (
1777 `host` varchar(255) default NULL,
1778 `port` int(11) default NULL,
1779 `db` varchar(255) default NULL,
1780 `userid` varchar(255) default NULL,
1781 `password` varchar(255) default NULL,
1783 `id` int(11) NOT NULL auto_increment,
1784 `checked` smallint(6) default NULL,
1785 `rank` int(11) default NULL,
1786 `syntax` varchar(80) default NULL,
1788 `position` enum('primary','secondary','') NOT NULL default 'primary',
1789 `type` enum('zed','opensearch') NOT NULL default 'zed',
1790 `description` text NOT NULL,
1792 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1795 -- Table structure for table `zebraqueue`
1798 DROP TABLE IF EXISTS `zebraqueue`;
1799 CREATE TABLE `zebraqueue` (
1800 `id` int(11) NOT NULL auto_increment,
1801 `biblio_auth_number` int(11) NOT NULL default '0',
1802 `operation` char(20) NOT NULL default '',
1803 `server` char(20) NOT NULL default '',
1804 `done` int(11) NOT NULL default '0',
1805 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1807 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1809 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1810 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1811 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1812 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1813 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1814 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1815 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1816 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;