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 `matcher_id` int(11) default NULL,
850 `template_id` int(11) default NULL,
851 `branchcode` varchar(10) default NULL,
852 `num_biblios` int(11) NOT NULL default 0,
853 `num_items` int(11) NOT NULL default 0,
854 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
855 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
856 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
857 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
858 `file_name` varchar(100),
859 `comments` mediumtext,
860 PRIMARY KEY (`import_batch_id`),
861 KEY `branchcode` (`branchcode`)
862 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
865 -- Table structure for table `import_records`
868 DROP TABLE IF EXISTS `import_records`;
869 CREATE TABLE `import_records` (
870 `import_record_id` int(11) NOT NULL auto_increment,
871 `import_batch_id` int(11) NOT NULL,
872 `branchcode` varchar(10) default NULL,
873 `record_sequence` int(11) NOT NULL default 0,
874 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
875 `import_date` DATE default NULL,
876 `marc` longblob NOT NULL,
877 `marcxml` longtext NOT NULL,
878 `marcxml_old` longtext NOT NULL,
879 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
880 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
881 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
882 `import_error` mediumtext,
883 `encoding` varchar(40) NOT NULL default '',
884 `z3950random` varchar(40) default NULL,
885 PRIMARY KEY (`import_record_id`),
886 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
887 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
888 KEY `branchcode` (`branchcode`),
889 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
890 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
893 -- Table structure for `import_record_matches`
895 DROP TABLE IF EXISTS `import_record_matches`;
896 CREATE TABLE `import_record_matches` (
897 `import_record_id` int(11) NOT NULL,
898 `candidate_match_id` int(11) NOT NULL,
899 `score` int(11) NOT NULL default 0,
900 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
901 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
902 KEY `record_score` (`import_record_id`, `score`)
903 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
906 -- Table structure for table `import_biblios`
909 DROP TABLE IF EXISTS `import_biblios`;
910 CREATE TABLE `import_biblios` (
911 `import_record_id` int(11) NOT NULL,
912 `matched_biblionumber` int(11) default NULL,
913 `control_number` varchar(25) default NULL,
914 `original_source` varchar(25) default NULL,
915 `title` varchar(128) default NULL,
916 `author` varchar(80) default NULL,
917 `isbn` varchar(14) default NULL,
918 `issn` varchar(9) default NULL,
919 `has_items` tinyint(1) NOT NULL default 0,
920 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
921 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
922 KEY `matched_biblionumber` (`matched_biblionumber`),
923 KEY `title` (`title`),
925 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
928 -- Table structure for table `import_items`
931 DROP TABLE IF EXISTS `import_items`;
932 CREATE TABLE `import_items` (
933 `import_items_id` int(11) NOT NULL auto_increment,
934 `import_record_id` int(11) NOT NULL,
935 `itemnumber` int(11) default NULL,
936 `branchcode` varchar(10) default NULL,
937 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
938 `marcxml` longtext NOT NULL,
939 `import_error` mediumtext,
940 PRIMARY KEY (`import_items_id`),
941 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
942 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
943 KEY `itemnumber` (`itemnumber`),
944 KEY `branchcode` (`branchcode`)
945 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
948 -- Table structure for table `issues`
951 DROP TABLE IF EXISTS `issues`;
952 CREATE TABLE `issues` (
953 `borrowernumber` int(11) default NULL,
954 `itemnumber` int(11) default NULL,
955 `date_due` date default NULL,
956 `branchcode` varchar(10) default NULL,
957 `issuingbranch` varchar(18) default NULL,
958 `returndate` date default NULL,
959 `lastreneweddate` date default NULL,
960 `return` varchar(4) default NULL,
961 `renewals` tinyint(4) default NULL,
962 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
963 `issuedate` date default NULL,
964 KEY `issuesborridx` (`borrowernumber`),
965 KEY `issuesitemidx` (`itemnumber`),
966 KEY `bordate` (`borrowernumber`,`timestamp`),
967 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
968 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
969 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
972 -- Table structure for table `issuingrules`
975 DROP TABLE IF EXISTS `issuingrules`;
976 CREATE TABLE `issuingrules` (
977 `categorycode` varchar(10) NOT NULL default '',
978 `itemtype` varchar(10) NOT NULL default '',
979 `restrictedtype` tinyint(1) default NULL,
980 `rentaldiscount` decimal(28,6) default NULL,
981 `reservecharge` decimal(28,6) default NULL,
982 `fine` decimal(28,6) default NULL,
983 `firstremind` int(11) default NULL,
984 `chargeperiod` int(11) default NULL,
985 `accountsent` int(11) default NULL,
986 `chargename` varchar(100) default NULL,
987 `maxissueqty` int(4) default NULL,
988 `issuelength` int(4) default NULL,
989 `branchcode` varchar(10) NOT NULL default '',
990 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
991 KEY `categorycode` (`categorycode`),
992 KEY `itemtype` (`itemtype`)
993 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
996 -- Table structure for table `items`
999 DROP TABLE IF EXISTS `items`;
1000 CREATE TABLE `items` (
1001 `itemnumber` int(11) NOT NULL auto_increment,
1002 `biblionumber` int(11) NOT NULL default 0,
1003 `biblioitemnumber` int(11) NOT NULL default 0,
1004 `barcode` varchar(20) default NULL,
1005 `dateaccessioned` date default NULL,
1006 `booksellerid` varchar(10) default NULL,
1007 `homebranch` varchar(10) default NULL,
1008 `price` decimal(8,2) default NULL,
1009 `replacementprice` decimal(8,2) default NULL,
1010 `replacementpricedate` date default NULL,
1011 `datelastborrowed` date default NULL,
1012 `datelastseen` date default NULL,
1013 `stack` tinyint(1) default NULL,
1014 `notforloan` tinyint(1) default NULL,
1015 `damaged` tinyint(1) default NULL,
1016 `itemlost` tinyint(1) default NULL,
1017 `wthdrawn` tinyint(1) default NULL,
1018 `itemcallnumber` varchar(30) default NULL,
1019 `issues` smallint(6) default NULL,
1020 `renewals` smallint(6) default NULL,
1021 `reserves` smallint(6) default NULL,
1022 `restricted` tinyint(1) default NULL,
1023 `itemnotes` mediumtext,
1024 `holdingbranch` varchar(10) default NULL,
1025 `paidfor` mediumtext,
1026 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1027 `location` varchar(80) default NULL,
1028 `onloan` date default NULL,
1029 `cn_source` varchar(10) default NULL,
1030 `cn_sort` varchar(30) default NULL,
1031 `ccode` varchar(10) default NULL,
1032 `materials` varchar(10) default NULL,
1033 `uri` varchar(255) default NULL,
1034 `itype` varchar(10) default NULL,
1035 PRIMARY KEY (`itemnumber`),
1036 UNIQUE KEY `itembarcodeidx` (`barcode`),
1037 KEY `itembinoidx` (`biblioitemnumber`),
1038 KEY `itembibnoidx` (`biblionumber`),
1039 KEY `homebranch` (`homebranch`),
1040 KEY `holdingbranch` (`holdingbranch`),
1041 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1042 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1043 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1044 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1047 -- Table structure for table `itemtypes`
1050 DROP TABLE IF EXISTS `itemtypes`;
1051 CREATE TABLE `itemtypes` (
1052 `itemtype` varchar(10) NOT NULL default '',
1053 `description` mediumtext,
1054 `renewalsallowed` smallint(6) default NULL,
1055 `rentalcharge` double(16,4) default NULL,
1056 `notforloan` smallint(6) default NULL,
1057 `imageurl` varchar(200) default NULL,
1059 PRIMARY KEY (`itemtype`),
1060 UNIQUE KEY `itemtype` (`itemtype`)
1061 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1064 -- Table structure for table `labels`
1067 DROP TABLE IF EXISTS `labels`;
1068 CREATE TABLE `labels` (
1069 `labelid` int(11) NOT NULL auto_increment,
1070 `batch_id` varchar(10) NOT NULL default 1,
1071 `itemnumber` varchar(100) NOT NULL default '',
1072 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1073 PRIMARY KEY (`labelid`)
1074 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1077 -- Table structure for table `labels_conf`
1080 DROP TABLE IF EXISTS `labels_conf`;
1081 CREATE TABLE `labels_conf` (
1082 `id` int(4) NOT NULL auto_increment,
1083 `barcodetype` char(100) default '',
1084 `title` int(1) default '0',
1085 `subtitle` int(1) default '0',
1086 `itemtype` int(1) default '0',
1087 `barcode` int(1) default '0',
1088 `dewey` int(1) default '0',
1089 `class` int(1) default NULL,
1090 `subclass` int(1) default '0',
1091 `itemcallnumber` int(1) default '0',
1092 `author` int(1) default '0',
1093 `issn` int(1) default '0',
1094 `isbn` int(1) default '0',
1095 `startlabel` int(2) NOT NULL default '1',
1096 `printingtype` char(32) default 'BAR',
1097 `layoutname` char(20) NOT NULL default 'TEST',
1098 `guidebox` int(1) default '0',
1099 `active` tinyint(1) default '1',
1100 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1101 `ccode` char(4) collate utf8_unicode_ci default NULL,
1102 `callnum_split` int(1) default NULL,
1103 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1105 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1108 -- Table structure for table `labels_templates`
1111 DROP TABLE IF EXISTS `labels_templates`;
1112 CREATE TABLE `labels_templates` (
1113 `tmpl_id` int(4) NOT NULL auto_increment,
1114 `tmpl_code` char(100) default '',
1115 `tmpl_desc` char(100) default '',
1116 `page_width` float default '0',
1117 `page_height` float default '0',
1118 `label_width` float default '0',
1119 `label_height` float default '0',
1120 `topmargin` float default '0',
1121 `leftmargin` float default '0',
1122 `cols` int(2) default '0',
1123 `rows` int(2) default '0',
1124 `colgap` float default '0',
1125 `rowgap` float default '0',
1126 `active` int(1) default NULL,
1127 `units` char(20) default 'PX',
1128 `fontsize` int(4) NOT NULL default '3',
1129 PRIMARY KEY (`tmpl_id`)
1130 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1133 -- Table structure for table `letter`
1136 DROP TABLE IF EXISTS `letter`;
1137 CREATE TABLE `letter` (
1138 `module` varchar(20) NOT NULL default '',
1139 `code` varchar(20) NOT NULL default '',
1140 `name` varchar(100) NOT NULL default '',
1141 `title` varchar(200) NOT NULL default '',
1143 PRIMARY KEY (`module`,`code`)
1144 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1147 -- Table structure for table `marc_subfield_structure`
1150 DROP TABLE IF EXISTS `marc_subfield_structure`;
1151 CREATE TABLE `marc_subfield_structure` (
1152 `tagfield` varchar(3) NOT NULL default '',
1153 `tagsubfield` varchar(1) NOT NULL default '',
1154 `liblibrarian` varchar(255) NOT NULL default '',
1155 `libopac` varchar(255) NOT NULL default '',
1156 `repeatable` tinyint(4) NOT NULL default 0,
1157 `mandatory` tinyint(4) NOT NULL default 0,
1158 `kohafield` varchar(40) default NULL,
1159 `tab` tinyint(1) default NULL,
1160 `authorised_value` varchar(20) default NULL,
1161 `authtypecode` varchar(20) default NULL,
1162 `value_builder` varchar(80) default NULL,
1163 `isurl` tinyint(1) default NULL,
1164 `hidden` tinyint(1) default NULL,
1165 `frameworkcode` varchar(4) NOT NULL default '',
1166 `seealso` varchar(1100) default NULL,
1167 `link` varchar(80) default NULL,
1168 `defaultvalue` text default NULL,
1169 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1170 KEY `kohafield_2` (`kohafield`),
1171 KEY `tab` (`frameworkcode`,`tab`),
1172 KEY `kohafield` (`frameworkcode`,`kohafield`)
1173 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1176 -- Table structure for table `marc_tag_structure`
1179 DROP TABLE IF EXISTS `marc_tag_structure`;
1180 CREATE TABLE `marc_tag_structure` (
1181 `tagfield` varchar(3) NOT NULL default '',
1182 `liblibrarian` varchar(255) NOT NULL default '',
1183 `libopac` varchar(255) NOT NULL default '',
1184 `repeatable` tinyint(4) NOT NULL default 0,
1185 `mandatory` tinyint(4) NOT NULL default 0,
1186 `authorised_value` varchar(10) default NULL,
1187 `frameworkcode` varchar(4) NOT NULL default '',
1188 PRIMARY KEY (`frameworkcode`,`tagfield`)
1189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1192 -- Table structure for table `marc_matchers`
1195 DROP TABLE IF EXISTS `marc_matchers`;
1196 CREATE TABLE `marc_matchers` (
1197 `matcher_id` int(11) NOT NULL auto_increment,
1198 `code` varchar(10) NOT NULL default '',
1199 `description` varchar(255) NOT NULL default '',
1200 `record_type` varchar(10) NOT NULL default 'biblio',
1201 `threshold` int(11) NOT NULL default 0,
1202 PRIMARY KEY (`matcher_id`),
1203 KEY `code` (`code`),
1204 KEY `record_type` (`record_type`)
1205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1208 -- Table structure for table `matchpoints`
1210 DROP TABLE IF EXISTS `matchpoints`;
1211 CREATE TABLE `matchpoints` (
1212 `matcher_id` int(11) NOT NULL,
1213 `matchpoint_id` int(11) NOT NULL auto_increment,
1214 `search_index` varchar(30) NOT NULL default '',
1215 `score` int(11) NOT NULL default 0,
1216 PRIMARY KEY (`matchpoint_id`),
1217 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1218 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1219 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1223 -- Table structure for table `matchpoint_components`
1225 DROP TABLE IF EXISTS `matchpoint_components`;
1226 CREATE TABLE `matchpoint_components` (
1227 `matchpoint_id` int(11) NOT NULL,
1228 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1229 sequence int(11) NOT NULL default 0,
1230 tag varchar(3) NOT NULL default '',
1231 subfields varchar(40) NOT NULL default '',
1232 offset int(4) NOT NULL default 0,
1233 length int(4) NOT NULL default 0,
1234 PRIMARY KEY (`matchpoint_component_id`),
1235 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1236 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1237 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1241 -- Table structure for table `matcher_component_norms`
1243 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1244 CREATE TABLE `matchpoint_component_norms` (
1245 `matchpoint_component_id` int(11) NOT NULL,
1246 `sequence` int(11) NOT NULL default 0,
1247 `norm_routine` varchar(50) NOT NULL default '',
1248 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1249 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1250 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1251 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1254 -- Table structure for table `matcher_matchpoints`
1256 DROP TABLE IF EXISTS `matcher_matchpoints`;
1257 CREATE TABLE `matcher_matchpoints` (
1258 `matcher_id` int(11) NOT NULL,
1259 `matchpoint_id` int(11) NOT NULL,
1260 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1261 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1262 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1263 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1264 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1267 -- Table structure for table `matchchecks`
1269 DROP TABLE IF EXISTS `matchchecks`;
1270 CREATE TABLE `matchchecks` (
1271 `matcher_id` int(11) NOT NULL,
1272 `matchcheck_id` int(11) NOT NULL auto_increment,
1273 `source_matchpoint_id` int(11) NOT NULL,
1274 `target_matchpoint_id` int(11) NOT NULL,
1275 PRIMARY KEY (`matchcheck_id`),
1276 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1277 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1278 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1279 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1280 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1281 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1282 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1285 -- Table structure for table `mediatypetable`
1288 DROP TABLE IF EXISTS `mediatypetable`;
1289 CREATE TABLE `mediatypetable` (
1290 `mediatypecode` varchar(5) NOT NULL default '',
1292 `itemtypecodes` text,
1293 PRIMARY KEY (`mediatypecode`)
1294 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1297 -- Table structure for table `notifys`
1300 DROP TABLE IF EXISTS `notifys`;
1301 CREATE TABLE `notifys` (
1302 `notify_id` int(11) NOT NULL default 0,
1303 `borrowernumber` int(11) NOT NULL default 0,
1304 `itemnumber` int(11) NOT NULL default 0,
1305 `notify_date` date default NULL,
1306 `notify_send_date` date default NULL,
1307 `notify_level` int(1) NOT NULL default 0,
1308 `method` varchar(20) NOT NULL default ''
1309 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1312 -- Table structure for table `nozebra`
1314 DROP TABLE IF EXISTS `nozebra`;
1315 CREATE TABLE `nozebra` (
1316 `server` varchar(20) NOT NULL,
1317 `indexname` varchar(40) NOT NULL,
1318 `value` varchar(250) NOT NULL,
1319 `biblionumbers` longtext NOT NULL,
1320 KEY `indexname` (`server`,`indexname`),
1321 KEY `value` (`server`,`value`))
1322 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1325 -- Table structure for table `opac_news`
1328 DROP TABLE IF EXISTS `opac_news`;
1329 CREATE TABLE `opac_news` (
1330 `idnew` int(10) unsigned NOT NULL auto_increment,
1331 `title` varchar(250) NOT NULL default '',
1332 `new` text NOT NULL,
1333 `lang` varchar(4) NOT NULL default '',
1334 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1335 `expirationdate` date default NULL,
1336 `number` int(11) default NULL,
1337 PRIMARY KEY (`idnew`)
1338 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1341 -- Table structure for table `overduerules`
1344 DROP TABLE IF EXISTS `overduerules`;
1345 CREATE TABLE `overduerules` (
1346 `branchcode` varchar(10) NOT NULL default '',
1347 `categorycode` varchar(2) NOT NULL default '',
1348 `delay1` int(4) default 0,
1349 `letter1` varchar(20) default NULL,
1350 `debarred1` varchar(1) default 0,
1351 `delay2` int(4) default 0,
1352 `debarred2` varchar(1) default 0,
1353 `letter2` varchar(20) default NULL,
1354 `delay3` int(4) default 0,
1355 `letter3` varchar(20) default NULL,
1356 `debarred3` int(1) default 0,
1357 PRIMARY KEY (`branchcode`,`categorycode`)
1358 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1361 -- Table structure for table `printers`
1364 DROP TABLE IF EXISTS `printers`;
1365 CREATE TABLE `printers` (
1366 `printername` varchar(40) NOT NULL default '',
1367 `printqueue` varchar(20) default NULL,
1368 `printtype` varchar(20) default NULL,
1369 PRIMARY KEY (`printername`)
1370 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1373 -- Table structure for table `repeatable_holidays`
1376 DROP TABLE IF EXISTS `repeatable_holidays`;
1377 CREATE TABLE `repeatable_holidays` (
1378 `id` int(11) NOT NULL auto_increment,
1379 `branchcode` varchar(10) NOT NULL default '',
1380 `weekday` smallint(6) default NULL,
1381 `day` smallint(6) default NULL,
1382 `month` smallint(6) default NULL,
1383 `title` varchar(50) NOT NULL default '',
1384 `description` text NOT NULL,
1386 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1389 -- Table structure for table `reports_dictionary`
1392 DROP TABLE IF EXISTS `reports_dictionary`;
1393 CREATE TABLE reports_dictionary (
1394 `id` int(11) NOT NULL auto_increment,
1395 `name` varchar(255) default NULL,
1397 `date_created` datetime default NULL,
1398 `date_modified` datetime default NULL,
1400 `area` int(11) default NULL,
1402 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1405 -- Table structure for table `reserveconstraints`
1408 DROP TABLE IF EXISTS `reserveconstraints`;
1409 CREATE TABLE `reserveconstraints` (
1410 `borrowernumber` int(11) NOT NULL default 0,
1411 `reservedate` date default NULL,
1412 `biblionumber` int(11) NOT NULL default 0,
1413 `biblioitemnumber` int(11) default NULL,
1414 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1415 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1418 -- Table structure for table `reserves`
1421 DROP TABLE IF EXISTS `reserves`;
1422 CREATE TABLE `reserves` (
1423 `borrowernumber` int(11) NOT NULL default 0,
1424 `reservedate` date default NULL,
1425 `biblionumber` int(11) NOT NULL default 0,
1426 `constrainttype` varchar(1) default NULL,
1427 `branchcode` varchar(10) default NULL,
1428 `notificationdate` date default NULL,
1429 `reminderdate` date default NULL,
1430 `cancellationdate` date default NULL,
1431 `reservenotes` mediumtext,
1432 `priority` smallint(6) default NULL,
1433 `found` varchar(1) default NULL,
1434 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1435 `itemnumber` int(11) default NULL,
1436 `waitingdate` date default NULL,
1437 KEY `borrowernumber` (`borrowernumber`),
1438 KEY `biblionumber` (`biblionumber`),
1439 KEY `itemnumber` (`itemnumber`),
1440 KEY `branchcode` (`branchcode`),
1441 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1442 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1443 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1444 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1445 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1448 -- Table structure for table `reviews`
1451 DROP TABLE IF EXISTS `reviews`;
1452 CREATE TABLE `reviews` (
1453 `reviewid` int(11) NOT NULL auto_increment,
1454 `borrowernumber` int(11) default NULL,
1455 `biblionumber` int(11) default NULL,
1457 `approved` tinyint(4) default NULL,
1458 `datereviewed` datetime default NULL,
1459 PRIMARY KEY (`reviewid`)
1460 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1463 -- Table structure for table `roadtype`
1466 DROP TABLE IF EXISTS `roadtype`;
1467 CREATE TABLE `roadtype` (
1468 `roadtypeid` int(11) NOT NULL auto_increment,
1469 `road_type` varchar(100) NOT NULL default '',
1470 PRIMARY KEY (`roadtypeid`)
1471 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1474 -- Table structure for table `saved_sql`
1477 DROP TABLE IF EXISTS `saved_sql`;
1478 CREATE TABLE saved_sql (
1479 `id` int(11) NOT NULL auto_increment,
1480 `borrowernumber` int(11) default NULL,
1481 `date_created` datetime default NULL,
1482 `last_modified` datetime default NULL,
1484 `last_run` datetime default NULL,
1485 `report_name` varchar(255) default NULL,
1486 `type` varchar(255) default NULL,
1489 KEY boridx (`borrowernumber`)
1490 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1494 -- Table structure for `saved_reports`
1497 DROP TABLE IF EXISTS `saved_reports`;
1498 CREATE TABLE saved_reports (
1499 `id` int(11) NOT NULL auto_increment,
1500 `report_id` int(11) default NULL,
1502 `date_run` datetime default NULL,
1504 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1508 -- Table structure for table `serial`
1511 DROP TABLE IF EXISTS `serial`;
1512 CREATE TABLE `serial` (
1513 `serialid` int(11) NOT NULL auto_increment,
1514 `biblionumber` varchar(100) NOT NULL default '',
1515 `subscriptionid` varchar(100) NOT NULL default '',
1516 `serialseq` varchar(100) NOT NULL default '',
1517 `status` tinyint(4) NOT NULL default 0,
1518 `planneddate` date default NULL,
1520 `publisheddate` date default NULL,
1522 `claimdate` date default NULL,
1523 `routingnotes` text,
1524 PRIMARY KEY (`serialid`)
1525 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1528 -- Table structure for table `sessions`
1531 DROP TABLE IF EXISTS sessions;
1532 CREATE TABLE sessions (
1533 `id` varchar(32) NOT NULL,
1534 `a_session` text NOT NULL,
1536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1539 -- Table structure for table `special_holidays`
1542 DROP TABLE IF EXISTS `special_holidays`;
1543 CREATE TABLE `special_holidays` (
1544 `id` int(11) NOT NULL auto_increment,
1545 `branchcode` varchar(10) NOT NULL default '',
1546 `day` smallint(6) NOT NULL default 0,
1547 `month` smallint(6) NOT NULL default 0,
1548 `year` smallint(6) NOT NULL default 0,
1549 `isexception` smallint(1) NOT NULL default 1,
1550 `title` varchar(50) NOT NULL default '',
1551 `description` text NOT NULL,
1553 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1556 -- Table structure for table `statistics`
1559 DROP TABLE IF EXISTS `statistics`;
1560 CREATE TABLE `statistics` (
1561 `datetime` datetime default NULL,
1562 `branch` varchar(10) default NULL,
1563 `proccode` varchar(4) default NULL,
1564 `value` double(16,4) default NULL,
1565 `type` varchar(16) default NULL,
1567 `usercode` varchar(10) default NULL,
1568 `itemnumber` int(11) default NULL,
1569 `itemtype` varchar(10) default NULL,
1570 `borrowernumber` int(11) default NULL,
1571 `associatedborrower` int(11) default NULL,
1572 KEY `timeidx` (`datetime`)
1573 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1576 -- Table structure for table `stopwords`
1579 DROP TABLE IF EXISTS `stopwords`;
1580 CREATE TABLE `stopwords` (
1581 `word` varchar(255) default NULL
1582 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1585 -- Table structure for table `subcategorytable`
1588 DROP TABLE IF EXISTS `subcategorytable`;
1589 CREATE TABLE `subcategorytable` (
1590 `subcategorycode` varchar(5) NOT NULL default '',
1592 `itemtypecodes` text,
1593 PRIMARY KEY (`subcategorycode`)
1594 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1597 -- Table structure for table `subscription`
1600 DROP TABLE IF EXISTS `subscription`;
1601 CREATE TABLE `subscription` (
1602 `biblionumber` int(11) NOT NULL default 0,
1603 `subscriptionid` int(11) NOT NULL auto_increment,
1604 `librarian` varchar(100) default '',
1605 `startdate` date default NULL,
1606 `aqbooksellerid` int(11) default 0,
1607 `cost` int(11) default 0,
1608 `aqbudgetid` int(11) default 0,
1609 `weeklength` tinyint(4) default 0,
1610 `monthlength` tinyint(4) default 0,
1611 `numberlength` tinyint(4) default 0,
1612 `periodicity` tinyint(4) default 0,
1613 `dow` varchar(100) default '',
1614 `numberingmethod` varchar(100) default '',
1616 `status` varchar(100) NOT NULL default '',
1617 `add1` int(11) default 0,
1618 `every1` int(11) default 0,
1619 `whenmorethan1` int(11) default 0,
1620 `setto1` int(11) default NULL,
1621 `lastvalue1` int(11) default NULL,
1622 `add2` int(11) default 0,
1623 `every2` int(11) default 0,
1624 `whenmorethan2` int(11) default 0,
1625 `setto2` int(11) default NULL,
1626 `lastvalue2` int(11) default NULL,
1627 `add3` int(11) default 0,
1628 `every3` int(11) default 0,
1629 `innerloop1` int(11) default 0,
1630 `innerloop2` int(11) default 0,
1631 `innerloop3` int(11) default 0,
1632 `whenmorethan3` int(11) default 0,
1633 `setto3` int(11) default NULL,
1634 `lastvalue3` int(11) default NULL,
1635 `issuesatonce` tinyint(3) NOT NULL default 1,
1636 `firstacquidate` date default NULL,
1637 `manualhistory` tinyint(1) NOT NULL default 0,
1638 `irregularity` text,
1639 `letter` varchar(20) default NULL,
1640 `numberpattern` tinyint(3) default 0,
1641 `distributedto` text,
1642 `internalnotes` longtext,
1644 `branchcode` varchar(10) NOT NULL default '',
1645 `hemisphere` tinyint(3) default 0,
1646 `lastbranch` varchar(10),
1647 PRIMARY KEY (`subscriptionid`)
1648 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1651 -- Table structure for table `subscriptionhistory`
1654 DROP TABLE IF EXISTS `subscriptionhistory`;
1655 CREATE TABLE `subscriptionhistory` (
1656 `biblionumber` int(11) NOT NULL default 0,
1657 `subscriptionid` int(11) NOT NULL default 0,
1658 `histstartdate` date default NULL,
1659 `enddate` date default NULL,
1660 `missinglist` longtext NOT NULL,
1661 `recievedlist` longtext NOT NULL,
1662 `opacnote` varchar(150) NOT NULL default '',
1663 `librariannote` varchar(150) NOT NULL default '',
1664 PRIMARY KEY (`subscriptionid`),
1665 KEY `biblionumber` (`biblionumber`)
1666 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1669 -- Table structure for table `subscriptionroutinglist`
1672 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1673 CREATE TABLE `subscriptionroutinglist` (
1674 `routingid` int(11) NOT NULL auto_increment,
1675 `borrowernumber` int(11) default NULL,
1676 `ranking` int(11) default NULL,
1677 `subscriptionid` int(11) default NULL,
1678 PRIMARY KEY (`routingid`)
1679 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1682 -- Table structure for table `suggestions`
1685 DROP TABLE IF EXISTS `suggestions`;
1686 CREATE TABLE `suggestions` (
1687 `suggestionid` int(8) NOT NULL auto_increment,
1688 `suggestedby` int(11) NOT NULL default 0,
1689 `managedby` int(11) default NULL,
1690 `STATUS` varchar(10) NOT NULL default '',
1692 `author` varchar(80) default NULL,
1693 `title` varchar(80) default NULL,
1694 `copyrightdate` smallint(6) default NULL,
1695 `publishercode` varchar(255) default NULL,
1696 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1697 `volumedesc` varchar(255) default NULL,
1698 `publicationyear` smallint(6) default 0,
1699 `place` varchar(255) default NULL,
1700 `isbn` varchar(10) default NULL,
1701 `mailoverseeing` smallint(1) default 0,
1702 `biblionumber` int(11) default NULL,
1704 PRIMARY KEY (`suggestionid`),
1705 KEY `suggestedby` (`suggestedby`),
1706 KEY `managedby` (`managedby`)
1707 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1710 -- Table structure for table `systempreferences`
1713 DROP TABLE IF EXISTS `systempreferences`;
1714 CREATE TABLE `systempreferences` (
1715 `variable` varchar(50) NOT NULL default '',
1717 `options` mediumtext,
1719 `type` varchar(20) default NULL,
1720 PRIMARY KEY (`variable`)
1721 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1724 -- Table structure for table `tags`
1727 DROP TABLE IF EXISTS `tags`;
1728 CREATE TABLE `tags` (
1729 `entry` varchar(255) NOT NULL default '',
1730 `weight` bigint(20) NOT NULL default 0,
1731 PRIMARY KEY (`entry`)
1732 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1735 -- Table structure for table `userflags`
1738 DROP TABLE IF EXISTS `userflags`;
1739 CREATE TABLE `userflags` (
1740 `bit` int(11) NOT NULL default 0,
1741 `flag` varchar(30) default NULL,
1742 `flagdesc` varchar(255) default NULL,
1743 `defaulton` int(11) default NULL,
1745 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1748 -- Table structure for table `virtualshelves`
1751 DROP TABLE IF EXISTS `virtualshelves`;
1752 CREATE TABLE `virtualshelves` (
1753 `shelfnumber` int(11) NOT NULL auto_increment,
1754 `shelfname` varchar(255) default NULL,
1755 `owner` varchar(80) default NULL,
1756 `category` varchar(1) default NULL,
1757 PRIMARY KEY (`shelfnumber`)
1758 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1761 -- Table structure for table `virtualshelfcontents`
1764 DROP TABLE IF EXISTS `virtualshelfcontents`;
1765 CREATE TABLE `virtualshelfcontents` (
1766 `shelfnumber` int(11) NOT NULL default 0,
1767 `biblionumber` int(11) NOT NULL default 0,
1768 `flags` int(11) default NULL,
1769 `dateadded` timestamp NULL default NULL,
1770 KEY `shelfnumber` (`shelfnumber`),
1771 KEY `biblionumber` (`biblionumber`),
1772 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1773 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1774 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1777 -- Table structure for table `z3950servers`
1780 DROP TABLE IF EXISTS `z3950servers`;
1781 CREATE TABLE `z3950servers` (
1782 `host` varchar(255) default NULL,
1783 `port` int(11) default NULL,
1784 `db` varchar(255) default NULL,
1785 `userid` varchar(255) default NULL,
1786 `password` varchar(255) default NULL,
1788 `id` int(11) NOT NULL auto_increment,
1789 `checked` smallint(6) default NULL,
1790 `rank` int(11) default NULL,
1791 `syntax` varchar(80) default NULL,
1793 `position` enum('primary','secondary','') NOT NULL default 'primary',
1794 `type` enum('zed','opensearch') NOT NULL default 'zed',
1795 `description` text NOT NULL,
1797 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1800 -- Table structure for table `zebraqueue`
1803 DROP TABLE IF EXISTS `zebraqueue`;
1804 CREATE TABLE `zebraqueue` (
1805 `id` int(11) NOT NULL auto_increment,
1806 `biblio_auth_number` int(11) NOT NULL default '0',
1807 `operation` char(20) NOT NULL default '',
1808 `server` char(20) NOT NULL default '',
1809 `done` int(11) NOT NULL default '0',
1810 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1812 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1814 DROP TABLE IF EXISTS `services_throttle`;
1815 CREATE TABLE `services_throttle` (
1816 `service_type` varchar(10) NOT NULL default '',
1817 `service_count` varchar(45) default NULL,
1818 PRIMARY KEY (`service_type`)
1819 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1821 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1822 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1823 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1824 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1825 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1826 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1827 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1828 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;