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 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
56 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
59 -- Table structure for table `action_logs`
62 DROP TABLE IF EXISTS `action_logs`;
63 CREATE TABLE `action_logs` (
64 `action_id` int(11) NOT NULL auto_increment,
65 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
66 `user` int(11) NOT NULL default 0,
69 `object` int(11) default NULL,
71 PRIMARY KEY (`action_id`),
72 KEY (`timestamp`,`user`)
73 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
76 -- Table structure for table `alert`
79 DROP TABLE IF EXISTS `alert`;
80 CREATE TABLE `alert` (
81 `alertid` int(11) NOT NULL auto_increment,
82 `borrowernumber` int(11) NOT NULL default 0,
83 `type` varchar(10) NOT NULL default '',
84 `externalid` varchar(20) NOT NULL default '',
85 PRIMARY KEY (`alertid`),
86 KEY `borrowernumber` (`borrowernumber`),
87 KEY `type` (`type`,`externalid`)
88 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
91 -- Table structure for table `aqbasket`
94 DROP TABLE IF EXISTS `aqbasket`;
95 CREATE TABLE `aqbasket` (
96 `basketno` int(11) NOT NULL auto_increment,
97 `creationdate` date default NULL,
98 `closedate` date default NULL,
99 `booksellerid` int(11) NOT NULL default 1,
100 `authorisedby` varchar(10) default NULL,
101 `booksellerinvoicenumber` mediumtext,
102 PRIMARY KEY (`basketno`),
103 KEY `booksellerid` (`booksellerid`),
104 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
105 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
108 -- Table structure for table `aqbookfund`
111 DROP TABLE IF EXISTS `aqbookfund`;
112 CREATE TABLE `aqbookfund` (
113 `bookfundid` varchar(10) NOT NULL default '',
114 `bookfundname` mediumtext,
115 `bookfundgroup` varchar(5) default NULL,
116 `branchcode` varchar(10) NOT NULL default '',
117 PRIMARY KEY (`bookfundid`,`branchcode`)
118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
121 -- Table structure for table `aqbooksellers`
124 DROP TABLE IF EXISTS `aqbooksellers`;
125 CREATE TABLE `aqbooksellers` (
126 `id` int(11) NOT NULL auto_increment,
128 `address1` mediumtext,
129 `address2` mediumtext,
130 `address3` mediumtext,
131 `address4` mediumtext,
132 `phone` varchar(30) default NULL,
133 `accountnumber` mediumtext,
134 `othersupplier` mediumtext,
135 `currency` varchar(3) NOT NULL default '',
136 `deliverydays` smallint(6) default NULL,
137 `followupdays` smallint(6) default NULL,
138 `followupscancel` smallint(6) default NULL,
139 `specialty` mediumtext,
140 `booksellerfax` mediumtext,
142 `bookselleremail` mediumtext,
143 `booksellerurl` mediumtext,
144 `contact` varchar(100) default NULL,
146 `url` varchar(255) default NULL,
147 `contpos` varchar(100) default NULL,
148 `contphone` varchar(100) default NULL,
149 `contfax` varchar(100) default NULL,
150 `contaltphone` varchar(100) default NULL,
151 `contemail` varchar(100) default NULL,
152 `contnotes` mediumtext,
153 `active` tinyint(4) default NULL,
154 `listprice` varchar(10) default NULL,
155 `invoiceprice` varchar(10) default NULL,
156 `gstreg` tinyint(4) default NULL,
157 `listincgst` tinyint(4) default NULL,
158 `invoiceincgst` tinyint(4) default NULL,
159 `discount` float(6,4) default NULL,
160 `fax` varchar(50) default NULL,
161 `nocalc` int(11) default NULL,
162 `invoicedisc` float(6,4) default NULL,
164 KEY `listprice` (`listprice`),
165 KEY `invoiceprice` (`invoiceprice`),
166 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
167 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
171 -- Table structure for table `aqbudget`
174 DROP TABLE IF EXISTS `aqbudget`;
175 CREATE TABLE `aqbudget` (
176 `bookfundid` varchar(10) NOT NULL default '',
177 `startdate` date NOT NULL default 0,
178 `enddate` date default NULL,
179 `budgetamount` decimal(13,2) default NULL,
180 `aqbudgetid` tinyint(4) NOT NULL auto_increment,
181 `branchcode` varchar(10) default NULL,
182 PRIMARY KEY (`aqbudgetid`)
183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
186 -- Table structure for table `aqorderbreakdown`
189 DROP TABLE IF EXISTS `aqorderbreakdown`;
190 CREATE TABLE `aqorderbreakdown` (
191 `ordernumber` int(11) default NULL,
192 `linenumber` int(11) default NULL,
193 `branchcode` varchar(10) default NULL,
194 `bookfundid` varchar(10) NOT NULL default '',
195 `allocation` smallint(6) default NULL,
196 KEY `ordernumber` (`ordernumber`),
197 KEY `bookfundid` (`bookfundid`),
198 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
199 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
203 -- Table structure for table `aqorderdelivery`
206 DROP TABLE IF EXISTS `aqorderdelivery`;
207 CREATE TABLE `aqorderdelivery` (
208 `ordernumber` date default NULL,
209 `deliverynumber` smallint(6) NOT NULL default 0,
210 `deliverydate` varchar(18) default NULL,
211 `qtydelivered` smallint(6) default NULL,
212 `deliverycomments` mediumtext
213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
216 -- Table structure for table `aqorders`
219 DROP TABLE IF EXISTS `aqorders`;
220 CREATE TABLE `aqorders` (
221 `ordernumber` int(11) NOT NULL auto_increment,
222 `biblionumber` int(11) default NULL,
224 `entrydate` date default NULL,
225 `quantity` smallint(6) default NULL,
226 `currency` varchar(3) default NULL,
227 `listprice` decimal(28,6) default NULL,
228 `totalamount` decimal(28,6) default NULL,
229 `datereceived` date default NULL,
230 `booksellerinvoicenumber` mediumtext,
231 `freight` decimal(28,6) default NULL,
232 `unitprice` decimal(28,6) default NULL,
233 `quantityreceived` smallint(6) default NULL,
234 `cancelledby` varchar(10) default NULL,
235 `datecancellationprinted` date default NULL,
237 `supplierreference` mediumtext,
238 `purchaseordernumber` mediumtext,
239 `subscription` tinyint(1) default NULL,
240 `serialid` varchar(30) default NULL,
241 `basketno` int(11) default NULL,
242 `biblioitemnumber` int(11) default NULL,
243 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
244 `rrp` decimal(13,2) default NULL,
245 `ecost` decimal(13,2) default NULL,
246 `gst` decimal(13,2) default NULL,
247 `budgetdate` date default NULL,
248 `sort1` varchar(80) default NULL,
249 `sort2` varchar(80) default NULL,
250 PRIMARY KEY (`ordernumber`),
251 KEY `basketno` (`basketno`),
252 KEY `biblionumber` (`biblionumber`),
253 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
254 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
255 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
258 -- Table structure for table `auth_header`
261 DROP TABLE IF EXISTS `auth_header`;
262 CREATE TABLE `auth_header` (
263 `authid` bigint(20) unsigned NOT NULL auto_increment,
264 `authtypecode` varchar(10) NOT NULL default '',
265 `datecreated` date default NULL,
266 `datemodified` date default NULL,
267 `origincode` varchar(20) default NULL,
268 `authtrees` mediumtext,
270 `linkid` bigint(20) default NULL,
271 `marcxml` longtext NOT NULL,
272 PRIMARY KEY (`authid`),
273 KEY `origincode` (`origincode`)
274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
277 -- Table structure for table `auth_subfield_structure`
280 DROP TABLE IF EXISTS `auth_subfield_structure`;
281 CREATE TABLE `auth_subfield_structure` (
282 `authtypecode` varchar(10) NOT NULL default '',
283 `tagfield` varchar(3) NOT NULL default '',
284 `tagsubfield` varchar(1) NOT NULL default '',
285 `liblibrarian` varchar(255) NOT NULL default '',
286 `libopac` varchar(255) NOT NULL default '',
287 `repeatable` tinyint(4) NOT NULL default 0,
288 `mandatory` tinyint(4) NOT NULL default 0,
289 `tab` tinyint(1) default NULL,
290 `authorised_value` varchar(10) default NULL,
291 `value_builder` varchar(80) default NULL,
292 `seealso` varchar(255) default NULL,
293 `isurl` tinyint(1) default NULL,
294 `hidden` tinyint(3) NOT NULL default 0,
295 `linkid` tinyint(1) NOT NULL default 0,
296 `kohafield` varchar(45) NULL default '',
297 `frameworkcode` varchar(8) NOT NULL default '',
298 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
299 KEY `tab` (`authtypecode`,`tab`)
300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
303 -- Table structure for table `auth_tag_structure`
306 DROP TABLE IF EXISTS `auth_tag_structure`;
307 CREATE TABLE `auth_tag_structure` (
308 `authtypecode` varchar(10) NOT NULL default '',
309 `tagfield` varchar(3) NOT NULL default '',
310 `liblibrarian` varchar(255) NOT NULL default '',
311 `libopac` varchar(255) NOT NULL default '',
312 `repeatable` tinyint(4) NOT NULL default 0,
313 `mandatory` tinyint(4) NOT NULL default 0,
314 `authorised_value` varchar(10) default NULL,
315 PRIMARY KEY (`authtypecode`,`tagfield`),
316 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
320 -- Table structure for table `auth_types`
323 DROP TABLE IF EXISTS `auth_types`;
324 CREATE TABLE `auth_types` (
325 `authtypecode` varchar(10) NOT NULL default '',
326 `authtypetext` varchar(255) NOT NULL default '',
327 `auth_tag_to_report` varchar(3) NOT NULL default '',
328 `summary` mediumtext NOT NULL,
329 PRIMARY KEY (`authtypecode`)
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
333 -- Table structure for table `authorised_values`
336 DROP TABLE IF EXISTS `authorised_values`;
337 CREATE TABLE `authorised_values` (
338 `id` int(11) NOT NULL auto_increment,
339 `category` varchar(10) NOT NULL default '',
340 `authorised_value` varchar(80) NOT NULL default '',
341 `lib` varchar(80) default NULL,
343 KEY `name` (`category`)
344 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
347 -- Table structure for table `biblio`
350 DROP TABLE IF EXISTS `biblio`;
351 CREATE TABLE `biblio` (
352 `biblionumber` int(11) NOT NULL auto_increment,
353 `frameworkcode` varchar(4) NOT NULL default '',
356 `unititle` mediumtext,
358 `serial` tinyint(1) default NULL,
359 `seriestitle` mediumtext,
360 `copyrightdate` smallint(6) default NULL,
361 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
362 `datecreated` DATE NOT NULL,
363 `abstract` mediumtext,
364 PRIMARY KEY (`biblionumber`),
365 KEY `blbnoidx` (`biblionumber`)
366 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
369 -- Table structure for table `biblio_framework`
372 DROP TABLE IF EXISTS `biblio_framework`;
373 CREATE TABLE `biblio_framework` (
374 `frameworkcode` varchar(4) NOT NULL default '',
375 `frameworktext` varchar(255) NOT NULL default '',
376 PRIMARY KEY (`frameworkcode`)
377 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
380 -- Table structure for table `biblioitems`
383 DROP TABLE IF EXISTS `biblioitems`;
384 CREATE TABLE `biblioitems` (
385 `biblioitemnumber` int(11) NOT NULL auto_increment,
386 `biblionumber` int(11) NOT NULL default 0,
389 `itemtype` varchar(10) default NULL,
390 `isbn` varchar(14) default NULL,
391 `issn` varchar(9) default NULL,
392 `publicationyear` text,
393 `publishercode` varchar(255) default NULL,
394 `volumedate` date default NULL,
396 `collectiontitle` mediumtext default NULL,
397 `collectionissn` text default NULL,
398 `collectionvolume` mediumtext default NULL,
399 `editionstatement` text default NULL,
400 `editionresponsibility` text default NULL,
401 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
402 `illus` varchar(255) default NULL,
403 `pages` varchar(255) default NULL,
405 `size` varchar(255) default NULL,
406 `place` varchar(255) default NULL,
407 `lccn` varchar(25) default NULL,
409 `url` varchar(255) default NULL,
410 `cn_source` varchar(10) default NULL,
411 `cn_class` varchar(30) default NULL,
412 `cn_item` varchar(10) default NULL,
413 `cn_suffix` varchar(10) default NULL,
414 `cn_sort` varchar(30) default NULL,
415 `totalissues` int(10),
416 `marcxml` longtext NOT NULL,
417 PRIMARY KEY (`biblioitemnumber`),
418 KEY `bibinoidx` (`biblioitemnumber`),
419 KEY `bibnoidx` (`biblionumber`),
421 KEY `publishercode` (`publishercode`),
422 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
423 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
426 -- Table structure for table `borrowers`
429 DROP TABLE IF EXISTS `borrowers`;
430 CREATE TABLE `borrowers` (
431 `borrowernumber` int(11) NOT NULL auto_increment,
432 `cardnumber` varchar(16) default NULL,
433 `surname` mediumtext NOT NULL,
436 `othernames` mediumtext,
438 `streetnumber` varchar(10) default NULL,
439 `streettype` varchar(50) default NULL,
440 `address` mediumtext NOT NULL,
442 `city` mediumtext NOT NULL,
443 `zipcode` varchar(25) default NULL,
446 `mobile` varchar(50) default NULL,
450 `B_streetnumber` varchar(10) default NULL,
451 `B_streettype` varchar(50) default NULL,
452 `B_address` varchar(100) default NULL,
454 `B_zipcode` varchar(25) default NULL,
456 `B_phone` mediumtext,
457 `dateofbirth` date default NULL,
458 `branchcode` varchar(10) NOT NULL default '',
459 `categorycode` varchar(10) NOT NULL default '',
460 `dateenrolled` date default NULL,
461 `dateexpiry` date default NULL,
462 `gonenoaddress` tinyint(1) default NULL,
463 `lost` tinyint(1) default NULL,
464 `debarred` tinyint(1) default NULL,
465 `contactname` mediumtext,
466 `contactfirstname` text,
468 `guarantorid` int(11) default NULL,
469 `borrowernotes` mediumtext,
470 `relationship` varchar(100) default NULL,
471 `ethnicity` varchar(50) default NULL,
472 `ethnotes` varchar(255) default NULL,
473 `sex` varchar(1) default NULL,
474 `password` varchar(30) default NULL,
475 `flags` int(11) default NULL,
476 `userid` varchar(30) default NULL,
477 `opacnote` mediumtext,
478 `contactnote` varchar(255) default NULL,
479 `sort1` varchar(80) default NULL,
480 `sort2` varchar(80) default NULL,
481 UNIQUE KEY `cardnumber` (`cardnumber`),
482 PRIMARY KEY `borrowernumber` (`borrowernumber`),
483 KEY `categorycode` (`categorycode`),
484 KEY `branchcode` (`branchcode`),
485 KEY `userid` (`userid`),
486 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
487 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
488 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
491 -- Table structure for table `branchcategories`
494 DROP TABLE IF EXISTS `branchcategories`;
495 CREATE TABLE `branchcategories` (
496 `categorycode` varchar(10) NOT NULL default '',
497 `categoryname` varchar(32),
498 `codedescription` mediumtext,
499 `categorytype` varchar(16),
500 PRIMARY KEY (`categorycode`)
501 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
504 -- Table structure for table `branches`
507 DROP TABLE IF EXISTS `branches`;
508 CREATE TABLE `branches` (
509 `branchcode` varchar(10) NOT NULL default '',
510 `branchname` mediumtext NOT NULL,
511 `branchaddress1` mediumtext,
512 `branchaddress2` mediumtext,
513 `branchaddress3` mediumtext,
514 `branchphone` mediumtext,
515 `branchfax` mediumtext,
516 `branchemail` mediumtext,
517 `issuing` tinyint(4) default NULL,
518 `branchip` varchar(15) default NULL,
519 `branchprinter` varchar(100) default NULL,
520 UNIQUE KEY `branchcode` (`branchcode`)
521 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
524 -- Table structure for table `branchrelations`
527 DROP TABLE IF EXISTS `branchrelations`;
528 CREATE TABLE `branchrelations` (
529 `branchcode` varchar(10) NOT NULL default '',
530 `categorycode` varchar(10) NOT NULL default '',
531 PRIMARY KEY (`branchcode`,`categorycode`),
532 KEY `branchcode` (`branchcode`),
533 KEY `categorycode` (`categorycode`),
534 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
535 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
539 -- Table structure for table `branchtransfers`
542 DROP TABLE IF EXISTS `branchtransfers`;
543 CREATE TABLE `branchtransfers` (
544 `itemnumber` int(11) NOT NULL default 0,
545 `datesent` datetime default NULL,
546 `frombranch` varchar(10) NOT NULL default '',
547 `datearrived` datetime default NULL,
548 `tobranch` varchar(10) NOT NULL default '',
549 `comments` mediumtext,
550 KEY `frombranch` (`frombranch`),
551 KEY `tobranch` (`tobranch`),
552 KEY `itemnumber` (`itemnumber`),
553 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
554 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
555 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
556 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
560 -- Table structure for table `browser`
562 DROP TABLE IF EXISTS `browser`;
563 CREATE TABLE `browser` (
564 `level` int(11) NOT NULL,
565 `classification` varchar(20) NOT NULL,
566 `description` varchar(255) NOT NULL,
567 `number` bigint(20) NOT NULL,
568 `endnode` tinyint(4) NOT NULL
569 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
572 -- Table structure for table `categories`
575 DROP TABLE IF EXISTS `categories`;
576 CREATE TABLE `categories` (
577 `categorycode` varchar(10) NOT NULL default '',
578 `description` mediumtext,
579 `enrolmentperiod` smallint(6) default NULL,
580 `upperagelimit` smallint(6) default NULL,
581 `dateofbirthrequired` tinyint(1) default NULL,
582 `finetype` varchar(30) default NULL,
583 `bulk` tinyint(1) default NULL,
584 `enrolmentfee` decimal(28,6) default NULL,
585 `overduenoticerequired` tinyint(1) default NULL,
586 `issuelimit` smallint(6) default NULL,
587 `reservefee` decimal(28,6) default NULL,
588 `category_type` varchar(1) NOT NULL default 'A',
589 PRIMARY KEY (`categorycode`),
590 UNIQUE KEY `categorycode` (`categorycode`)
591 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
594 -- Table structure for table `categorytable`
597 DROP TABLE IF EXISTS `categorytable`;
598 CREATE TABLE `categorytable` (
599 `categorycode` varchar(5) NOT NULL default '',
601 `itemtypecodes` text,
602 PRIMARY KEY (`categorycode`)
603 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
606 -- Table structure for table `cities`
609 DROP TABLE IF EXISTS `cities`;
610 CREATE TABLE `cities` (
611 `cityid` int(11) NOT NULL auto_increment,
612 `city_name` varchar(100) NOT NULL default '',
613 `city_zipcode` varchar(20) default NULL,
614 PRIMARY KEY (`cityid`)
615 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
618 -- Table structure for table `class_sort_rules`
621 DROP TABLE IF EXISTS `class_sort_rules`;
622 CREATE TABLE `class_sort_rules` (
623 `class_sort_rule` varchar(10) NOT NULL default '',
624 `description` mediumtext,
625 `sort_routine` varchar(30) NOT NULL default '',
626 PRIMARY KEY (`class_sort_rule`),
627 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
628 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
631 -- Table structure for table `class_sources`
634 DROP TABLE IF EXISTS `class_sources`;
635 CREATE TABLE `class_sources` (
636 `cn_source` varchar(10) NOT NULL default '',
637 `description` mediumtext,
638 `used` tinyint(4) NOT NULL default 0,
639 `class_sort_rule` varchar(10) NOT NULL default '',
640 PRIMARY KEY (`cn_source`),
641 UNIQUE KEY `cn_source_idx` (`cn_source`),
642 KEY `used_idx` (`used`),
643 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
644 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
647 -- Table structure for table `currency`
650 DROP TABLE IF EXISTS `currency`;
651 CREATE TABLE `currency` (
652 `currency` varchar(10) NOT NULL default '',
653 `rate` float(7,5) default NULL,
654 PRIMARY KEY (`currency`)
655 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
658 -- Table structure for table `deletedbiblio`
661 DROP TABLE IF EXISTS `deletedbiblio`;
662 CREATE TABLE `deletedbiblio` (
663 `biblionumber` int(11) NOT NULL default 0,
664 `frameworkcode` varchar(4) NOT NULL default '',
667 `unititle` mediumtext,
669 `serial` tinyint(1) default NULL,
670 `seriestitle` mediumtext,
671 `copyrightdate` smallint(6) default NULL,
672 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
673 `datecreated` DATE NOT NULL,
674 `abstract` mediumtext,
675 PRIMARY KEY (`biblionumber`),
676 KEY `blbnoidx` (`biblionumber`)
677 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
680 -- Table structure for table `deletedbiblioitems`
683 DROP TABLE IF EXISTS `deletedbiblioitems`;
684 CREATE TABLE `deletedbiblioitems` (
685 `biblioitemnumber` int(11) NOT NULL default 0,
686 `biblionumber` int(11) NOT NULL default 0,
689 `itemtype` varchar(10) default NULL,
690 `isbn` varchar(14) default NULL,
691 `issn` varchar(9) default NULL,
692 `publicationyear` text,
693 `publishercode` varchar(255) default NULL,
694 `volumedate` date default NULL,
696 `collectiontitle` mediumtext default NULL,
697 `collectionissn` text default NULL,
698 `collectionvolume` mediumtext default NULL,
699 `editionstatement` text default NULL,
700 `editionresponsibility` text default NULL,
701 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
702 `illus` varchar(255) default NULL,
703 `pages` varchar(255) default NULL,
705 `size` varchar(255) default NULL,
706 `place` varchar(255) default NULL,
707 `lccn` varchar(25) default NULL,
709 `url` varchar(255) default NULL,
710 `cn_source` varchar(10) default NULL,
711 `cn_class` varchar(30) default NULL,
712 `cn_item` varchar(10) default NULL,
713 `cn_suffix` varchar(10) default NULL,
714 `cn_sort` varchar(30) default NULL,
715 `totalissues` int(10),
716 `marcxml` longtext NOT NULL,
717 PRIMARY KEY (`biblioitemnumber`),
718 KEY `bibinoidx` (`biblioitemnumber`),
719 KEY `bibnoidx` (`biblionumber`),
721 KEY `publishercode` (`publishercode`)
722 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
725 -- Table structure for table `deletedborrowers`
728 DROP TABLE IF EXISTS `deletedborrowers`;
729 CREATE TABLE `deletedborrowers` (
730 `borrowernumber` int(11) NOT NULL default 0,
731 `cardnumber` varchar(9) NOT NULL default '',
732 `surname` mediumtext NOT NULL,
735 `othernames` mediumtext,
737 `streetnumber` varchar(10) default NULL,
738 `streettype` varchar(50) default NULL,
739 `address` mediumtext NOT NULL,
741 `city` mediumtext NOT NULL,
742 `zipcode` varchar(25) default NULL,
745 `mobile` varchar(50) default NULL,
749 `B_streetnumber` varchar(10) default NULL,
750 `B_streettype` varchar(50) default NULL,
751 `B_address` varchar(100) default NULL,
753 `B_zipcode` varchar(25) default NULL,
755 `B_phone` mediumtext,
756 `dateofbirth` date default NULL,
757 `branchcode` varchar(10) NOT NULL default '',
758 `categorycode` varchar(2) default NULL,
759 `dateenrolled` date default NULL,
760 `dateexpiry` date default NULL,
761 `gonenoaddress` tinyint(1) default NULL,
762 `lost` tinyint(1) default NULL,
763 `debarred` tinyint(1) default NULL,
764 `contactname` mediumtext,
765 `contactfirstname` text,
767 `guarantorid` int(11) default NULL,
768 `borrowernotes` mediumtext,
769 `relationship` varchar(100) default NULL,
770 `ethnicity` varchar(50) default NULL,
771 `ethnotes` varchar(255) default NULL,
772 `sex` varchar(1) default NULL,
773 `password` varchar(30) default NULL,
774 `flags` int(11) default NULL,
775 `userid` varchar(30) default NULL,
776 `opacnote` mediumtext,
777 `contactnote` varchar(255) default NULL,
778 `sort1` varchar(80) default NULL,
779 `sort2` varchar(80) default NULL,
780 KEY `borrowernumber` (`borrowernumber`),
781 KEY `cardnumber` (`cardnumber`)
782 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
785 -- Table structure for table `deleteditems`
788 DROP TABLE IF EXISTS `deleteditems`;
789 CREATE TABLE `deleteditems` (
790 `itemnumber` int(11) NOT NULL default 0,
791 `biblionumber` int(11) NOT NULL default 0,
792 `biblioitemnumber` int(11) NOT NULL default 0,
793 `barcode` varchar(20) default NULL,
794 `dateaccessioned` date default NULL,
795 `booksellerid` varchar(10) default NULL,
796 `homebranch` varchar(10) default NULL,
797 `price` decimal(8,2) default NULL,
798 `replacementprice` decimal(8,2) default NULL,
799 `replacementpricedate` date default NULL,
800 `datelastborrowed` date default NULL,
801 `datelastseen` date default NULL,
802 `stack` tinyint(1) default NULL,
803 `notforloan` tinyint(1) default NULL,
804 `damaged` tinyint(1) default NULL,
805 `itemlost` tinyint(1) default NULL,
806 `wthdrawn` tinyint(1) default NULL,
807 `itemcallnumber` varchar(30) default NULL,
808 `issues` smallint(6) default NULL,
809 `renewals` smallint(6) default NULL,
810 `reserves` smallint(6) default NULL,
811 `restricted` tinyint(1) default NULL,
812 `itemnotes` mediumtext,
813 `holdingbranch` varchar(10) default NULL,
814 `paidfor` mediumtext,
815 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
816 `location` varchar(80) default NULL,
817 `onloan` date default NULL,
818 `cn_source` varchar(10) default NULL,
819 `cn_sort` varchar(30) default NULL,
820 `ccode` varchar(10) default NULL,
821 `materials` varchar(10) default NULL,
822 `uri` varchar(255) default NULL,
823 `itype` varchar(10) default NULL,
825 PRIMARY KEY (`itemnumber`),
826 KEY `delitembarcodeidx` (`barcode`),
827 KEY `delitembinoidx` (`biblioitemnumber`),
828 KEY `delitembibnoidx` (`biblionumber`),
829 KEY `delhomebranch` (`homebranch`),
830 KEY `delholdingbranch` (`holdingbranch`)
831 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
834 -- Table structure for table `ethnicity`
837 DROP TABLE IF EXISTS `ethnicity`;
838 CREATE TABLE `ethnicity` (
839 `code` varchar(10) NOT NULL default '',
840 `name` varchar(255) default NULL,
842 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
845 -- Table structure for table `import_batches`
848 DROP TABLE IF EXISTS `import_batches`;
849 CREATE TABLE `import_batches` (
850 `import_batch_id` int(11) NOT NULL auto_increment,
851 `matcher_id` int(11) default NULL,
852 `template_id` int(11) default NULL,
853 `branchcode` varchar(10) default NULL,
854 `num_biblios` int(11) NOT NULL default 0,
855 `num_items` int(11) NOT NULL default 0,
856 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
857 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
858 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
859 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
860 `file_name` varchar(100),
861 `comments` mediumtext,
862 PRIMARY KEY (`import_batch_id`),
863 KEY `branchcode` (`branchcode`)
864 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
867 -- Table structure for table `import_records`
870 DROP TABLE IF EXISTS `import_records`;
871 CREATE TABLE `import_records` (
872 `import_record_id` int(11) NOT NULL auto_increment,
873 `import_batch_id` int(11) NOT NULL,
874 `branchcode` varchar(10) default NULL,
875 `record_sequence` int(11) NOT NULL default 0,
876 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
877 `import_date` DATE default NULL,
878 `marc` longblob NOT NULL,
879 `marcxml` longtext NOT NULL,
880 `marcxml_old` longtext NOT NULL,
881 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
882 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
883 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
884 `import_error` mediumtext,
885 `encoding` varchar(40) NOT NULL default '',
886 `z3950random` varchar(40) default NULL,
887 PRIMARY KEY (`import_record_id`),
888 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
889 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
890 KEY `branchcode` (`branchcode`),
891 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
892 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
895 -- Table structure for `import_record_matches`
897 DROP TABLE IF EXISTS `import_record_matches`;
898 CREATE TABLE `import_record_matches` (
899 `import_record_id` int(11) NOT NULL,
900 `candidate_match_id` int(11) NOT NULL,
901 `score` int(11) NOT NULL default 0,
902 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
903 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
904 KEY `record_score` (`import_record_id`, `score`)
905 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
908 -- Table structure for table `import_biblios`
911 DROP TABLE IF EXISTS `import_biblios`;
912 CREATE TABLE `import_biblios` (
913 `import_record_id` int(11) NOT NULL,
914 `matched_biblionumber` int(11) default NULL,
915 `control_number` varchar(25) default NULL,
916 `original_source` varchar(25) default NULL,
917 `title` varchar(128) default NULL,
918 `author` varchar(80) default NULL,
919 `isbn` varchar(14) default NULL,
920 `issn` varchar(9) default NULL,
921 `has_items` tinyint(1) NOT NULL default 0,
922 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
923 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
924 KEY `matched_biblionumber` (`matched_biblionumber`),
925 KEY `title` (`title`),
927 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
930 -- Table structure for table `import_items`
933 DROP TABLE IF EXISTS `import_items`;
934 CREATE TABLE `import_items` (
935 `import_items_id` int(11) NOT NULL auto_increment,
936 `import_record_id` int(11) NOT NULL,
937 `itemnumber` int(11) default NULL,
938 `branchcode` varchar(10) default NULL,
939 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
940 `marcxml` longtext NOT NULL,
941 `import_error` mediumtext,
942 PRIMARY KEY (`import_items_id`),
943 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
944 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
945 KEY `itemnumber` (`itemnumber`),
946 KEY `branchcode` (`branchcode`)
947 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
950 -- Table structure for table `issues`
953 DROP TABLE IF EXISTS `issues`;
954 CREATE TABLE `issues` (
955 `borrowernumber` int(11) default NULL,
956 `itemnumber` int(11) default NULL,
957 `date_due` date default NULL,
958 `branchcode` varchar(10) default NULL,
959 `issuingbranch` varchar(18) default NULL,
960 `returndate` date default NULL,
961 `lastreneweddate` date default NULL,
962 `return` varchar(4) default NULL,
963 `renewals` tinyint(4) default NULL,
964 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
965 `issuedate` date default NULL,
966 KEY `issuesborridx` (`borrowernumber`),
967 KEY `issuesitemidx` (`itemnumber`),
968 KEY `bordate` (`borrowernumber`,`timestamp`),
969 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
970 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
971 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
974 -- Table structure for table `issuingrules`
977 DROP TABLE IF EXISTS `issuingrules`;
978 CREATE TABLE `issuingrules` (
979 `categorycode` varchar(10) NOT NULL default '',
980 `itemtype` varchar(10) NOT NULL default '',
981 `restrictedtype` tinyint(1) default NULL,
982 `rentaldiscount` decimal(28,6) default NULL,
983 `reservecharge` decimal(28,6) default NULL,
984 `fine` decimal(28,6) default NULL,
985 `firstremind` int(11) default NULL,
986 `chargeperiod` int(11) default NULL,
987 `accountsent` int(11) default NULL,
988 `chargename` varchar(100) default NULL,
989 `maxissueqty` int(4) default NULL,
990 `issuelength` int(4) default NULL,
991 `branchcode` varchar(10) NOT NULL default '',
992 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
993 KEY `categorycode` (`categorycode`),
994 KEY `itemtype` (`itemtype`)
995 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
998 -- Table structure for table `items`
1001 DROP TABLE IF EXISTS `items`;
1002 CREATE TABLE `items` (
1003 `itemnumber` int(11) NOT NULL auto_increment,
1004 `biblionumber` int(11) NOT NULL default 0,
1005 `biblioitemnumber` int(11) NOT NULL default 0,
1006 `barcode` varchar(20) default NULL,
1007 `dateaccessioned` date default NULL,
1008 `booksellerid` varchar(10) default NULL,
1009 `homebranch` varchar(10) default NULL,
1010 `price` decimal(8,2) default NULL,
1011 `replacementprice` decimal(8,2) default NULL,
1012 `replacementpricedate` date default NULL,
1013 `datelastborrowed` date default NULL,
1014 `datelastseen` date default NULL,
1015 `stack` tinyint(1) default NULL,
1016 `notforloan` tinyint(1) default NULL,
1017 `damaged` tinyint(1) default NULL,
1018 `itemlost` tinyint(1) default NULL,
1019 `wthdrawn` tinyint(1) default NULL,
1020 `itemcallnumber` varchar(30) default NULL,
1021 `issues` smallint(6) default NULL,
1022 `renewals` smallint(6) default NULL,
1023 `reserves` smallint(6) default NULL,
1024 `restricted` tinyint(1) default NULL,
1025 `itemnotes` mediumtext,
1026 `holdingbranch` varchar(10) default NULL,
1027 `paidfor` mediumtext,
1028 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1029 `location` varchar(80) default NULL,
1030 `onloan` date default NULL,
1031 `cn_source` varchar(10) default NULL,
1032 `cn_sort` varchar(30) default NULL,
1033 `ccode` varchar(10) default NULL,
1034 `materials` varchar(10) default NULL,
1035 `uri` varchar(255) default NULL,
1036 `itype` varchar(10) default NULL,
1037 PRIMARY KEY (`itemnumber`),
1038 UNIQUE KEY `itembarcodeidx` (`barcode`),
1039 KEY `itembinoidx` (`biblioitemnumber`),
1040 KEY `itembibnoidx` (`biblionumber`),
1041 KEY `homebranch` (`homebranch`),
1042 KEY `holdingbranch` (`holdingbranch`),
1043 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1044 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1045 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1046 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1049 -- Table structure for table `itemtypes`
1052 DROP TABLE IF EXISTS `itemtypes`;
1053 CREATE TABLE `itemtypes` (
1054 `itemtype` varchar(10) NOT NULL default '',
1055 `description` mediumtext,
1056 `renewalsallowed` smallint(6) default NULL,
1057 `rentalcharge` double(16,4) default NULL,
1058 `notforloan` smallint(6) default NULL,
1059 `imageurl` varchar(200) default NULL,
1061 PRIMARY KEY (`itemtype`),
1062 UNIQUE KEY `itemtype` (`itemtype`)
1063 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1066 -- Table structure for table `labels`
1069 DROP TABLE IF EXISTS `labels`;
1070 CREATE TABLE `labels` (
1071 `labelid` int(11) NOT NULL auto_increment,
1072 `batch_id` varchar(10) NOT NULL default 1,
1073 `itemnumber` varchar(100) NOT NULL default '',
1074 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1075 PRIMARY KEY (`labelid`)
1076 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1079 -- Table structure for table `labels_conf`
1082 DROP TABLE IF EXISTS `labels_conf`;
1083 CREATE TABLE `labels_conf` (
1084 `id` int(4) NOT NULL auto_increment,
1085 `barcodetype` char(100) default '',
1086 `title` int(1) default '0',
1087 `subtitle` int(1) default '0',
1088 `itemtype` int(1) default '0',
1089 `barcode` int(1) default '0',
1090 `dewey` int(1) default '0',
1091 `class` int(1) default NULL,
1092 `subclass` int(1) default '0',
1093 `itemcallnumber` int(1) default '0',
1094 `author` int(1) default '0',
1095 `issn` int(1) default '0',
1096 `isbn` int(1) default '0',
1097 `startlabel` int(2) NOT NULL default '1',
1098 `printingtype` char(32) default 'BAR',
1099 `layoutname` char(20) NOT NULL default 'TEST',
1100 `guidebox` int(1) default '0',
1101 `active` tinyint(1) default '1',
1102 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1103 `ccode` char(4) collate utf8_unicode_ci default NULL,
1104 `callnum_split` int(1) default NULL,
1105 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1110 -- Table structure for table `labels_templates`
1113 DROP TABLE IF EXISTS `labels_templates`;
1114 CREATE TABLE `labels_templates` (
1115 `tmpl_id` int(4) NOT NULL auto_increment,
1116 `tmpl_code` char(100) default '',
1117 `tmpl_desc` char(100) default '',
1118 `page_width` float default '0',
1119 `page_height` float default '0',
1120 `label_width` float default '0',
1121 `label_height` float default '0',
1122 `topmargin` float default '0',
1123 `leftmargin` float default '0',
1124 `cols` int(2) default '0',
1125 `rows` int(2) default '0',
1126 `colgap` float default '0',
1127 `rowgap` float default '0',
1128 `active` int(1) default NULL,
1129 `units` char(20) default 'PX',
1130 `fontsize` int(4) NOT NULL default '3',
1131 PRIMARY KEY (`tmpl_id`)
1132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1135 -- Table structure for table `letter`
1138 DROP TABLE IF EXISTS `letter`;
1139 CREATE TABLE `letter` (
1140 `module` varchar(20) NOT NULL default '',
1141 `code` varchar(20) NOT NULL default '',
1142 `name` varchar(100) NOT NULL default '',
1143 `title` varchar(200) NOT NULL default '',
1145 PRIMARY KEY (`module`,`code`)
1146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1149 -- Table structure for table `marc_subfield_structure`
1152 DROP TABLE IF EXISTS `marc_subfield_structure`;
1153 CREATE TABLE `marc_subfield_structure` (
1154 `tagfield` varchar(3) NOT NULL default '',
1155 `tagsubfield` varchar(1) NOT NULL default '',
1156 `liblibrarian` varchar(255) NOT NULL default '',
1157 `libopac` varchar(255) NOT NULL default '',
1158 `repeatable` tinyint(4) NOT NULL default 0,
1159 `mandatory` tinyint(4) NOT NULL default 0,
1160 `kohafield` varchar(40) default NULL,
1161 `tab` tinyint(1) default NULL,
1162 `authorised_value` varchar(20) default NULL,
1163 `authtypecode` varchar(20) default NULL,
1164 `value_builder` varchar(80) default NULL,
1165 `isurl` tinyint(1) default NULL,
1166 `hidden` tinyint(1) default NULL,
1167 `frameworkcode` varchar(4) NOT NULL default '',
1168 `seealso` varchar(1100) default NULL,
1169 `link` varchar(80) default NULL,
1170 `defaultvalue` text default NULL,
1171 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1172 KEY `kohafield_2` (`kohafield`),
1173 KEY `tab` (`frameworkcode`,`tab`),
1174 KEY `kohafield` (`frameworkcode`,`kohafield`)
1175 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1178 -- Table structure for table `marc_tag_structure`
1181 DROP TABLE IF EXISTS `marc_tag_structure`;
1182 CREATE TABLE `marc_tag_structure` (
1183 `tagfield` varchar(3) NOT NULL default '',
1184 `liblibrarian` varchar(255) NOT NULL default '',
1185 `libopac` varchar(255) NOT NULL default '',
1186 `repeatable` tinyint(4) NOT NULL default 0,
1187 `mandatory` tinyint(4) NOT NULL default 0,
1188 `authorised_value` varchar(10) default NULL,
1189 `frameworkcode` varchar(4) NOT NULL default '',
1190 PRIMARY KEY (`frameworkcode`,`tagfield`)
1191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1194 -- Table structure for table `marc_matchers`
1197 DROP TABLE IF EXISTS `marc_matchers`;
1198 CREATE TABLE `marc_matchers` (
1199 `matcher_id` int(11) NOT NULL auto_increment,
1200 `code` varchar(10) NOT NULL default '',
1201 `description` varchar(255) NOT NULL default '',
1202 `record_type` varchar(10) NOT NULL default 'biblio',
1203 `threshold` int(11) NOT NULL default 0,
1204 PRIMARY KEY (`matcher_id`),
1205 KEY `code` (`code`),
1206 KEY `record_type` (`record_type`)
1207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1210 -- Table structure for table `matchpoints`
1212 DROP TABLE IF EXISTS `matchpoints`;
1213 CREATE TABLE `matchpoints` (
1214 `matcher_id` int(11) NOT NULL,
1215 `matchpoint_id` int(11) NOT NULL auto_increment,
1216 `search_index` varchar(30) NOT NULL default '',
1217 `score` int(11) NOT NULL default 0,
1218 PRIMARY KEY (`matchpoint_id`),
1219 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1220 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1221 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1225 -- Table structure for table `matchpoint_components`
1227 DROP TABLE IF EXISTS `matchpoint_components`;
1228 CREATE TABLE `matchpoint_components` (
1229 `matchpoint_id` int(11) NOT NULL,
1230 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1231 sequence int(11) NOT NULL default 0,
1232 tag varchar(3) NOT NULL default '',
1233 subfields varchar(40) NOT NULL default '',
1234 offset int(4) NOT NULL default 0,
1235 length int(4) NOT NULL default 0,
1236 PRIMARY KEY (`matchpoint_component_id`),
1237 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1238 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1239 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1240 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1243 -- Table structure for table `matcher_component_norms`
1245 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1246 CREATE TABLE `matchpoint_component_norms` (
1247 `matchpoint_component_id` int(11) NOT NULL,
1248 `sequence` int(11) NOT NULL default 0,
1249 `norm_routine` varchar(50) NOT NULL default '',
1250 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1251 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1252 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1253 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1256 -- Table structure for table `matcher_matchpoints`
1258 DROP TABLE IF EXISTS `matcher_matchpoints`;
1259 CREATE TABLE `matcher_matchpoints` (
1260 `matcher_id` int(11) NOT NULL,
1261 `matchpoint_id` int(11) NOT NULL,
1262 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1263 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1264 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1265 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1269 -- Table structure for table `matchchecks`
1271 DROP TABLE IF EXISTS `matchchecks`;
1272 CREATE TABLE `matchchecks` (
1273 `matcher_id` int(11) NOT NULL,
1274 `matchcheck_id` int(11) NOT NULL auto_increment,
1275 `source_matchpoint_id` int(11) NOT NULL,
1276 `target_matchpoint_id` int(11) NOT NULL,
1277 PRIMARY KEY (`matchcheck_id`),
1278 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1279 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1280 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1281 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1282 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1283 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1284 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1287 -- Table structure for table `mediatypetable`
1290 DROP TABLE IF EXISTS `mediatypetable`;
1291 CREATE TABLE `mediatypetable` (
1292 `mediatypecode` varchar(5) NOT NULL default '',
1294 `itemtypecodes` text,
1295 PRIMARY KEY (`mediatypecode`)
1296 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1299 -- Table structure for table `notifys`
1302 DROP TABLE IF EXISTS `notifys`;
1303 CREATE TABLE `notifys` (
1304 `notify_id` int(11) NOT NULL default 0,
1305 `borrowernumber` int(11) NOT NULL default 0,
1306 `itemnumber` int(11) NOT NULL default 0,
1307 `notify_date` date default NULL,
1308 `notify_send_date` date default NULL,
1309 `notify_level` int(1) NOT NULL default 0,
1310 `method` varchar(20) NOT NULL default ''
1311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1314 -- Table structure for table `nozebra`
1316 DROP TABLE IF EXISTS `nozebra`;
1317 CREATE TABLE `nozebra` (
1318 `server` varchar(20) NOT NULL,
1319 `indexname` varchar(40) NOT NULL,
1320 `value` varchar(250) NOT NULL,
1321 `biblionumbers` longtext NOT NULL,
1322 KEY `indexname` (`server`,`indexname`),
1323 KEY `value` (`server`,`value`))
1324 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1327 -- Table structure for table `opac_news`
1330 DROP TABLE IF EXISTS `opac_news`;
1331 CREATE TABLE `opac_news` (
1332 `idnew` int(10) unsigned NOT NULL auto_increment,
1333 `title` varchar(250) NOT NULL default '',
1334 `new` text NOT NULL,
1335 `lang` varchar(4) NOT NULL default '',
1336 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1337 `expirationdate` date default NULL,
1338 `number` int(11) default NULL,
1339 PRIMARY KEY (`idnew`)
1340 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1343 -- Table structure for table `overduerules`
1346 DROP TABLE IF EXISTS `overduerules`;
1347 CREATE TABLE `overduerules` (
1348 `branchcode` varchar(10) NOT NULL default '',
1349 `categorycode` varchar(2) NOT NULL default '',
1350 `delay1` int(4) default 0,
1351 `letter1` varchar(20) default NULL,
1352 `debarred1` varchar(1) default 0,
1353 `delay2` int(4) default 0,
1354 `debarred2` varchar(1) default 0,
1355 `letter2` varchar(20) default NULL,
1356 `delay3` int(4) default 0,
1357 `letter3` varchar(20) default NULL,
1358 `debarred3` int(1) default 0,
1359 PRIMARY KEY (`branchcode`,`categorycode`)
1360 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1363 -- Table structure for table `printers`
1366 DROP TABLE IF EXISTS `printers`;
1367 CREATE TABLE `printers` (
1368 `printername` varchar(40) NOT NULL default '',
1369 `printqueue` varchar(20) default NULL,
1370 `printtype` varchar(20) default NULL,
1371 PRIMARY KEY (`printername`)
1372 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1375 -- Table structure for table `repeatable_holidays`
1378 DROP TABLE IF EXISTS `repeatable_holidays`;
1379 CREATE TABLE `repeatable_holidays` (
1380 `id` int(11) NOT NULL auto_increment,
1381 `branchcode` varchar(10) NOT NULL default '',
1382 `weekday` smallint(6) default NULL,
1383 `day` smallint(6) default NULL,
1384 `month` smallint(6) default NULL,
1385 `title` varchar(50) NOT NULL default '',
1386 `description` text NOT NULL,
1388 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1391 -- Table structure for table `reports_dictionary`
1394 DROP TABLE IF EXISTS `reports_dictionary`;
1395 CREATE TABLE reports_dictionary (
1396 `id` int(11) NOT NULL auto_increment,
1397 `name` varchar(255) default NULL,
1399 `date_created` datetime default NULL,
1400 `date_modified` datetime default NULL,
1402 `area` int(11) default NULL,
1404 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1407 -- Table structure for table `reserveconstraints`
1410 DROP TABLE IF EXISTS `reserveconstraints`;
1411 CREATE TABLE `reserveconstraints` (
1412 `borrowernumber` int(11) NOT NULL default 0,
1413 `reservedate` date default NULL,
1414 `biblionumber` int(11) NOT NULL default 0,
1415 `biblioitemnumber` int(11) default NULL,
1416 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1417 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1420 -- Table structure for table `reserves`
1423 DROP TABLE IF EXISTS `reserves`;
1424 CREATE TABLE `reserves` (
1425 `borrowernumber` int(11) NOT NULL default 0,
1426 `reservedate` date default NULL,
1427 `biblionumber` int(11) NOT NULL default 0,
1428 `constrainttype` varchar(1) default NULL,
1429 `branchcode` varchar(10) default NULL,
1430 `notificationdate` date default NULL,
1431 `reminderdate` date default NULL,
1432 `cancellationdate` date default NULL,
1433 `reservenotes` mediumtext,
1434 `priority` smallint(6) default NULL,
1435 `found` varchar(1) default NULL,
1436 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1437 `itemnumber` int(11) default NULL,
1438 `waitingdate` date default NULL,
1439 KEY `borrowernumber` (`borrowernumber`),
1440 KEY `biblionumber` (`biblionumber`),
1441 KEY `itemnumber` (`itemnumber`),
1442 KEY `branchcode` (`branchcode`),
1443 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1444 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1445 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1446 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1447 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1450 -- Table structure for table `reviews`
1453 DROP TABLE IF EXISTS `reviews`;
1454 CREATE TABLE `reviews` (
1455 `reviewid` int(11) NOT NULL auto_increment,
1456 `borrowernumber` int(11) default NULL,
1457 `biblionumber` int(11) default NULL,
1459 `approved` tinyint(4) default NULL,
1460 `datereviewed` datetime default NULL,
1461 PRIMARY KEY (`reviewid`)
1462 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1465 -- Table structure for table `roadtype`
1468 DROP TABLE IF EXISTS `roadtype`;
1469 CREATE TABLE `roadtype` (
1470 `roadtypeid` int(11) NOT NULL auto_increment,
1471 `road_type` varchar(100) NOT NULL default '',
1472 PRIMARY KEY (`roadtypeid`)
1473 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1476 -- Table structure for table `saved_sql`
1479 DROP TABLE IF EXISTS `saved_sql`;
1480 CREATE TABLE saved_sql (
1481 `id` int(11) NOT NULL auto_increment,
1482 `borrowernumber` int(11) default NULL,
1483 `date_created` datetime default NULL,
1484 `last_modified` datetime default NULL,
1486 `last_run` datetime default NULL,
1487 `report_name` varchar(255) default NULL,
1488 `type` varchar(255) default NULL,
1491 KEY boridx (`borrowernumber`)
1492 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1496 -- Table structure for `saved_reports`
1499 DROP TABLE IF EXISTS `saved_reports`;
1500 CREATE TABLE saved_reports (
1501 `id` int(11) NOT NULL auto_increment,
1502 `report_id` int(11) default NULL,
1504 `date_run` datetime default NULL,
1506 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1510 -- Table structure for table `serial`
1513 DROP TABLE IF EXISTS `serial`;
1514 CREATE TABLE `serial` (
1515 `serialid` int(11) NOT NULL auto_increment,
1516 `biblionumber` varchar(100) NOT NULL default '',
1517 `subscriptionid` varchar(100) NOT NULL default '',
1518 `serialseq` varchar(100) NOT NULL default '',
1519 `status` tinyint(4) NOT NULL default 0,
1520 `planneddate` date default NULL,
1522 `publisheddate` date default NULL,
1524 `claimdate` date default NULL,
1525 `routingnotes` text,
1526 PRIMARY KEY (`serialid`)
1527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1530 -- Table structure for table `sessions`
1533 DROP TABLE IF EXISTS sessions;
1534 CREATE TABLE sessions (
1535 `id` varchar(32) NOT NULL,
1536 `a_session` text NOT NULL,
1538 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1541 -- Table structure for table `special_holidays`
1544 DROP TABLE IF EXISTS `special_holidays`;
1545 CREATE TABLE `special_holidays` (
1546 `id` int(11) NOT NULL auto_increment,
1547 `branchcode` varchar(10) NOT NULL default '',
1548 `day` smallint(6) NOT NULL default 0,
1549 `month` smallint(6) NOT NULL default 0,
1550 `year` smallint(6) NOT NULL default 0,
1551 `isexception` smallint(1) NOT NULL default 1,
1552 `title` varchar(50) NOT NULL default '',
1553 `description` text NOT NULL,
1555 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1558 -- Table structure for table `statistics`
1561 DROP TABLE IF EXISTS `statistics`;
1562 CREATE TABLE `statistics` (
1563 `datetime` datetime default NULL,
1564 `branch` varchar(10) default NULL,
1565 `proccode` varchar(4) default NULL,
1566 `value` double(16,4) default NULL,
1567 `type` varchar(16) default NULL,
1569 `usercode` varchar(10) default NULL,
1570 `itemnumber` int(11) default NULL,
1571 `itemtype` varchar(10) default NULL,
1572 `borrowernumber` int(11) default NULL,
1573 `associatedborrower` int(11) default NULL,
1574 KEY `timeidx` (`datetime`)
1575 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1578 -- Table structure for table `stopwords`
1581 DROP TABLE IF EXISTS `stopwords`;
1582 CREATE TABLE `stopwords` (
1583 `word` varchar(255) default NULL
1584 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1587 -- Table structure for table `subcategorytable`
1590 DROP TABLE IF EXISTS `subcategorytable`;
1591 CREATE TABLE `subcategorytable` (
1592 `subcategorycode` varchar(5) NOT NULL default '',
1594 `itemtypecodes` text,
1595 PRIMARY KEY (`subcategorycode`)
1596 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1599 -- Table structure for table `subscription`
1602 DROP TABLE IF EXISTS `subscription`;
1603 CREATE TABLE `subscription` (
1604 `biblionumber` int(11) NOT NULL default 0,
1605 `subscriptionid` int(11) NOT NULL auto_increment,
1606 `librarian` varchar(100) default '',
1607 `startdate` date default NULL,
1608 `aqbooksellerid` int(11) default 0,
1609 `cost` int(11) default 0,
1610 `aqbudgetid` int(11) default 0,
1611 `weeklength` tinyint(4) default 0,
1612 `monthlength` tinyint(4) default 0,
1613 `numberlength` tinyint(4) default 0,
1614 `periodicity` tinyint(4) default 0,
1615 `dow` varchar(100) default '',
1616 `numberingmethod` varchar(100) default '',
1618 `status` varchar(100) NOT NULL default '',
1619 `add1` int(11) default 0,
1620 `every1` int(11) default 0,
1621 `whenmorethan1` int(11) default 0,
1622 `setto1` int(11) default NULL,
1623 `lastvalue1` int(11) default NULL,
1624 `add2` int(11) default 0,
1625 `every2` int(11) default 0,
1626 `whenmorethan2` int(11) default 0,
1627 `setto2` int(11) default NULL,
1628 `lastvalue2` int(11) default NULL,
1629 `add3` int(11) default 0,
1630 `every3` int(11) default 0,
1631 `innerloop1` int(11) default 0,
1632 `innerloop2` int(11) default 0,
1633 `innerloop3` int(11) default 0,
1634 `whenmorethan3` int(11) default 0,
1635 `setto3` int(11) default NULL,
1636 `lastvalue3` int(11) default NULL,
1637 `issuesatonce` tinyint(3) NOT NULL default 1,
1638 `firstacquidate` date default NULL,
1639 `manualhistory` tinyint(1) NOT NULL default 0,
1640 `irregularity` text,
1641 `letter` varchar(20) default NULL,
1642 `numberpattern` tinyint(3) default 0,
1643 `distributedto` text,
1644 `internalnotes` longtext,
1646 `branchcode` varchar(10) NOT NULL default '',
1647 `hemisphere` tinyint(3) default 0,
1648 `lastbranch` varchar(10),
1649 PRIMARY KEY (`subscriptionid`)
1650 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1653 -- Table structure for table `subscriptionhistory`
1656 DROP TABLE IF EXISTS `subscriptionhistory`;
1657 CREATE TABLE `subscriptionhistory` (
1658 `biblionumber` int(11) NOT NULL default 0,
1659 `subscriptionid` int(11) NOT NULL default 0,
1660 `histstartdate` date default NULL,
1661 `enddate` date default NULL,
1662 `missinglist` longtext NOT NULL,
1663 `recievedlist` longtext NOT NULL,
1664 `opacnote` varchar(150) NOT NULL default '',
1665 `librariannote` varchar(150) NOT NULL default '',
1666 PRIMARY KEY (`subscriptionid`),
1667 KEY `biblionumber` (`biblionumber`)
1668 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1671 -- Table structure for table `subscriptionroutinglist`
1674 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1675 CREATE TABLE `subscriptionroutinglist` (
1676 `routingid` int(11) NOT NULL auto_increment,
1677 `borrowernumber` int(11) default NULL,
1678 `ranking` int(11) default NULL,
1679 `subscriptionid` int(11) default NULL,
1680 PRIMARY KEY (`routingid`)
1681 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1684 -- Table structure for table `suggestions`
1687 DROP TABLE IF EXISTS `suggestions`;
1688 CREATE TABLE `suggestions` (
1689 `suggestionid` int(8) NOT NULL auto_increment,
1690 `suggestedby` int(11) NOT NULL default 0,
1691 `managedby` int(11) default NULL,
1692 `STATUS` varchar(10) NOT NULL default '',
1694 `author` varchar(80) default NULL,
1695 `title` varchar(80) default NULL,
1696 `copyrightdate` smallint(6) default NULL,
1697 `publishercode` varchar(255) default NULL,
1698 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1699 `volumedesc` varchar(255) default NULL,
1700 `publicationyear` smallint(6) default 0,
1701 `place` varchar(255) default NULL,
1702 `isbn` varchar(10) default NULL,
1703 `mailoverseeing` smallint(1) default 0,
1704 `biblionumber` int(11) default NULL,
1706 PRIMARY KEY (`suggestionid`),
1707 KEY `suggestedby` (`suggestedby`),
1708 KEY `managedby` (`managedby`)
1709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1712 -- Table structure for table `systempreferences`
1715 DROP TABLE IF EXISTS `systempreferences`;
1716 CREATE TABLE `systempreferences` (
1717 `variable` varchar(50) NOT NULL default '',
1719 `options` mediumtext,
1721 `type` varchar(20) default NULL,
1722 PRIMARY KEY (`variable`)
1723 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1726 -- Table structure for table `tags`
1729 DROP TABLE IF EXISTS `tags`;
1730 CREATE TABLE `tags` (
1731 `entry` varchar(255) NOT NULL default '',
1732 `weight` bigint(20) NOT NULL default 0,
1733 PRIMARY KEY (`entry`)
1734 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1737 -- Table structure for table `userflags`
1740 DROP TABLE IF EXISTS `userflags`;
1741 CREATE TABLE `userflags` (
1742 `bit` int(11) NOT NULL default 0,
1743 `flag` varchar(30) default NULL,
1744 `flagdesc` varchar(255) default NULL,
1745 `defaulton` int(11) default NULL,
1747 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1750 -- Table structure for table `virtualshelves`
1753 DROP TABLE IF EXISTS `virtualshelves`;
1754 CREATE TABLE `virtualshelves` (
1755 `shelfnumber` int(11) NOT NULL auto_increment,
1756 `shelfname` varchar(255) default NULL,
1757 `owner` varchar(80) default NULL,
1758 `category` varchar(1) default NULL,
1759 PRIMARY KEY (`shelfnumber`)
1760 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1763 -- Table structure for table `virtualshelfcontents`
1766 DROP TABLE IF EXISTS `virtualshelfcontents`;
1767 CREATE TABLE `virtualshelfcontents` (
1768 `shelfnumber` int(11) NOT NULL default 0,
1769 `biblionumber` int(11) NOT NULL default 0,
1770 `flags` int(11) default NULL,
1771 `dateadded` timestamp NULL default NULL,
1772 KEY `shelfnumber` (`shelfnumber`),
1773 KEY `biblionumber` (`biblionumber`),
1774 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1775 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1776 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1779 -- Table structure for table `z3950servers`
1782 DROP TABLE IF EXISTS `z3950servers`;
1783 CREATE TABLE `z3950servers` (
1784 `host` varchar(255) default NULL,
1785 `port` int(11) default NULL,
1786 `db` varchar(255) default NULL,
1787 `userid` varchar(255) default NULL,
1788 `password` varchar(255) default NULL,
1790 `id` int(11) NOT NULL auto_increment,
1791 `checked` smallint(6) default NULL,
1792 `rank` int(11) default NULL,
1793 `syntax` varchar(80) default NULL,
1795 `position` enum('primary','secondary','') NOT NULL default 'primary',
1796 `type` enum('zed','opensearch') NOT NULL default 'zed',
1797 `description` text NOT NULL,
1799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1802 -- Table structure for table `zebraqueue`
1805 DROP TABLE IF EXISTS `zebraqueue`;
1806 CREATE TABLE `zebraqueue` (
1807 `id` int(11) NOT NULL auto_increment,
1808 `biblio_auth_number` int(11) NOT NULL default '0',
1809 `operation` char(20) NOT NULL default '',
1810 `server` char(20) NOT NULL default '',
1811 `done` int(11) NOT NULL default '0',
1812 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1814 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1816 DROP TABLE IF EXISTS `services_throttle`;
1817 CREATE TABLE `services_throttle` (
1818 `service_type` varchar(10) NOT NULL default '',
1819 `service_count` varchar(45) default NULL,
1820 PRIMARY KEY (`service_type`)
1821 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1823 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1824 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1825 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1826 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1827 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1828 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1829 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1830 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;