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
55 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
58 -- Table structure for table `action_logs`
61 DROP TABLE IF EXISTS `action_logs`;
62 CREATE TABLE `action_logs` (
63 `action_id` int(11) NOT NULL auto_increment,
64 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
65 `user` int(11) NOT NULL default 0,
68 `object` int(11) default NULL,
70 PRIMARY KEY (`action_id`),
71 KEY (`timestamp`,`user`)
72 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
75 -- Table structure for table `alert`
78 DROP TABLE IF EXISTS `alert`;
79 CREATE TABLE `alert` (
80 `alertid` int(11) NOT NULL auto_increment,
81 `borrowernumber` int(11) NOT NULL default 0,
82 `type` varchar(10) NOT NULL default '',
83 `externalid` varchar(20) NOT NULL default '',
84 PRIMARY KEY (`alertid`),
85 KEY `borrowernumber` (`borrowernumber`),
86 KEY `type` (`type`,`externalid`)
87 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
90 -- Table structure for table `aqbasket`
93 DROP TABLE IF EXISTS `aqbasket`;
94 CREATE TABLE `aqbasket` (
95 `basketno` int(11) NOT NULL auto_increment,
96 `creationdate` date default NULL,
97 `closedate` date default NULL,
98 `booksellerid` int(11) NOT NULL default 1,
99 `authorisedby` varchar(10) default NULL,
100 `booksellerinvoicenumber` mediumtext,
101 PRIMARY KEY (`basketno`),
102 KEY `booksellerid` (`booksellerid`),
103 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
104 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
107 -- Table structure for table `aqbookfund`
110 DROP TABLE IF EXISTS `aqbookfund`;
111 CREATE TABLE `aqbookfund` (
112 `bookfundid` varchar(10) NOT NULL default '',
113 `bookfundname` mediumtext,
114 `bookfundgroup` varchar(5) default NULL,
115 `branchcode` varchar(10) NOT NULL default '',
116 PRIMARY KEY (`bookfundid`,`branchcode`)
117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
120 -- Table structure for table `aqbooksellers`
123 DROP TABLE IF EXISTS `aqbooksellers`;
124 CREATE TABLE `aqbooksellers` (
125 `id` int(11) NOT NULL auto_increment,
127 `address1` mediumtext,
128 `address2` mediumtext,
129 `address3` mediumtext,
130 `address4` mediumtext,
131 `phone` varchar(30) default NULL,
132 `accountnumber` mediumtext,
133 `othersupplier` mediumtext,
134 `currency` varchar(3) NOT NULL default '',
135 `deliverydays` smallint(6) default NULL,
136 `followupdays` smallint(6) default NULL,
137 `followupscancel` smallint(6) default NULL,
138 `specialty` mediumtext,
139 `booksellerfax` mediumtext,
141 `bookselleremail` mediumtext,
142 `booksellerurl` mediumtext,
143 `contact` varchar(100) default NULL,
145 `url` varchar(255) default NULL,
146 `contpos` varchar(100) default NULL,
147 `contphone` varchar(100) default NULL,
148 `contfax` varchar(100) default NULL,
149 `contaltphone` varchar(100) default NULL,
150 `contemail` varchar(100) default NULL,
151 `contnotes` mediumtext,
152 `active` tinyint(4) default NULL,
153 `listprice` varchar(10) default NULL,
154 `invoiceprice` varchar(10) default NULL,
155 `gstreg` tinyint(4) default NULL,
156 `listincgst` tinyint(4) default NULL,
157 `invoiceincgst` tinyint(4) default NULL,
158 `discount` float(6,4) default NULL,
159 `fax` varchar(50) default NULL,
160 `nocalc` int(11) default NULL,
161 `invoicedisc` float(6,4) default NULL,
163 KEY `listprice` (`listprice`),
164 KEY `invoiceprice` (`invoiceprice`),
165 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
166 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
170 -- Table structure for table `aqbudget`
173 DROP TABLE IF EXISTS `aqbudget`;
174 CREATE TABLE `aqbudget` (
175 `bookfundid` varchar(10) NOT NULL default '',
176 `startdate` date NOT NULL default 0,
177 `enddate` date default NULL,
178 `budgetamount` decimal(13,2) default NULL,
179 `aqbudgetid` tinyint(4) NOT NULL auto_increment,
180 `branchcode` varchar(10) default NULL,
181 PRIMARY KEY (`aqbudgetid`)
182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
185 -- Table structure for table `aqorderbreakdown`
188 DROP TABLE IF EXISTS `aqorderbreakdown`;
189 CREATE TABLE `aqorderbreakdown` (
190 `ordernumber` int(11) default NULL,
191 `linenumber` int(11) default NULL,
192 `branchcode` varchar(10) default NULL,
193 `bookfundid` varchar(10) NOT NULL default '',
194 `allocation` smallint(6) default NULL,
195 KEY `ordernumber` (`ordernumber`),
196 KEY `bookfundid` (`bookfundid`),
197 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
198 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
202 -- Table structure for table `aqorderdelivery`
205 DROP TABLE IF EXISTS `aqorderdelivery`;
206 CREATE TABLE `aqorderdelivery` (
207 `ordernumber` date default NULL,
208 `deliverynumber` smallint(6) NOT NULL default 0,
209 `deliverydate` varchar(18) default NULL,
210 `qtydelivered` smallint(6) default NULL,
211 `deliverycomments` mediumtext
212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
215 -- Table structure for table `aqorders`
218 DROP TABLE IF EXISTS `aqorders`;
219 CREATE TABLE `aqorders` (
220 `ordernumber` int(11) NOT NULL auto_increment,
221 `biblionumber` int(11) default NULL,
223 `entrydate` date default NULL,
224 `quantity` smallint(6) default NULL,
225 `currency` varchar(3) default NULL,
226 `listprice` decimal(28,6) default NULL,
227 `totalamount` decimal(28,6) default NULL,
228 `datereceived` date default NULL,
229 `booksellerinvoicenumber` mediumtext,
230 `freight` decimal(28,6) default NULL,
231 `unitprice` decimal(28,6) default NULL,
232 `quantityreceived` smallint(6) default NULL,
233 `cancelledby` varchar(10) default NULL,
234 `datecancellationprinted` date default NULL,
236 `supplierreference` mediumtext,
237 `purchaseordernumber` mediumtext,
238 `subscription` tinyint(1) default NULL,
239 `serialid` varchar(30) default NULL,
240 `basketno` int(11) default NULL,
241 `biblioitemnumber` int(11) default NULL,
242 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
243 `rrp` decimal(13,2) default NULL,
244 `ecost` decimal(13,2) default NULL,
245 `gst` decimal(13,2) default NULL,
246 `budgetdate` date default NULL,
247 `sort1` varchar(80) default NULL,
248 `sort2` varchar(80) default NULL,
249 PRIMARY KEY (`ordernumber`),
250 KEY `basketno` (`basketno`),
251 KEY `biblionumber` (`biblionumber`),
252 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
253 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
254 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
257 -- Table structure for table `auth_header`
260 DROP TABLE IF EXISTS `auth_header`;
261 CREATE TABLE `auth_header` (
262 `authid` bigint(20) unsigned NOT NULL auto_increment,
263 `authtypecode` varchar(10) NOT NULL default '',
264 `datecreated` date default NULL,
265 `datemodified` date default NULL,
266 `origincode` varchar(20) default NULL,
267 `authtrees` mediumtext,
269 `linkid` bigint(20) default NULL,
270 `marcxml` longtext NOT NULL,
271 PRIMARY KEY (`authid`),
272 KEY `origincode` (`origincode`)
273 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
276 -- Table structure for table `auth_subfield_structure`
279 DROP TABLE IF EXISTS `auth_subfield_structure`;
280 CREATE TABLE `auth_subfield_structure` (
281 `authtypecode` varchar(10) NOT NULL default '',
282 `tagfield` varchar(3) NOT NULL default '',
283 `tagsubfield` varchar(1) NOT NULL default '',
284 `liblibrarian` varchar(255) NOT NULL default '',
285 `libopac` varchar(255) NOT NULL default '',
286 `repeatable` tinyint(4) NOT NULL default 0,
287 `mandatory` tinyint(4) NOT NULL default 0,
288 `tab` tinyint(1) default NULL,
289 `authorised_value` varchar(10) default NULL,
290 `value_builder` varchar(80) default NULL,
291 `seealso` varchar(255) default NULL,
292 `isurl` tinyint(1) default NULL,
293 `hidden` tinyint(3) NOT NULL default 0,
294 `linkid` tinyint(1) NOT NULL default 0,
295 `kohafield` varchar(45) NULL default '',
296 `frameworkcode` varchar(8) NOT NULL default '',
297 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
298 KEY `tab` (`authtypecode`,`tab`)
299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
302 -- Table structure for table `auth_tag_structure`
305 DROP TABLE IF EXISTS `auth_tag_structure`;
306 CREATE TABLE `auth_tag_structure` (
307 `authtypecode` varchar(10) NOT NULL default '',
308 `tagfield` varchar(3) NOT NULL default '',
309 `liblibrarian` varchar(255) NOT NULL default '',
310 `libopac` varchar(255) NOT NULL default '',
311 `repeatable` tinyint(4) NOT NULL default 0,
312 `mandatory` tinyint(4) NOT NULL default 0,
313 `authorised_value` varchar(10) default NULL,
314 PRIMARY KEY (`authtypecode`,`tagfield`),
315 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
316 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
319 -- Table structure for table `auth_types`
322 DROP TABLE IF EXISTS `auth_types`;
323 CREATE TABLE `auth_types` (
324 `authtypecode` varchar(10) NOT NULL default '',
325 `authtypetext` varchar(255) NOT NULL default '',
326 `auth_tag_to_report` varchar(3) NOT NULL default '',
327 `summary` mediumtext NOT NULL,
328 PRIMARY KEY (`authtypecode`)
329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
332 -- Table structure for table `authorised_values`
335 DROP TABLE IF EXISTS `authorised_values`;
336 CREATE TABLE `authorised_values` (
337 `id` int(11) NOT NULL auto_increment,
338 `category` varchar(10) NOT NULL default '',
339 `authorised_value` varchar(80) NOT NULL default '',
340 `lib` varchar(80) default NULL,
342 KEY `name` (`category`)
343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
346 -- Table structure for table `biblio`
349 DROP TABLE IF EXISTS `biblio`;
350 CREATE TABLE `biblio` (
351 `biblionumber` int(11) NOT NULL auto_increment,
352 `frameworkcode` varchar(4) NOT NULL default '',
355 `unititle` mediumtext,
357 `serial` tinyint(1) default NULL,
358 `seriestitle` mediumtext,
359 `copyrightdate` smallint(6) default NULL,
360 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
361 `datecreated` DATE NOT NULL,
362 `abstract` mediumtext,
363 PRIMARY KEY (`biblionumber`),
364 KEY `blbnoidx` (`biblionumber`)
365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
368 -- Table structure for table `biblio_framework`
371 DROP TABLE IF EXISTS `biblio_framework`;
372 CREATE TABLE `biblio_framework` (
373 `frameworkcode` varchar(4) NOT NULL default '',
374 `frameworktext` varchar(255) NOT NULL default '',
375 PRIMARY KEY (`frameworkcode`)
376 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
379 -- Table structure for table `biblioitems`
382 DROP TABLE IF EXISTS `biblioitems`;
383 CREATE TABLE `biblioitems` (
384 `biblioitemnumber` int(11) NOT NULL auto_increment,
385 `biblionumber` int(11) NOT NULL default 0,
388 `itemtype` varchar(10) default NULL,
389 `isbn` varchar(14) default NULL,
390 `issn` varchar(9) default NULL,
391 `publicationyear` text,
392 `publishercode` varchar(255) default NULL,
393 `volumedate` date default NULL,
395 `collectiontitle` mediumtext default NULL,
396 `collectionissn` text default NULL,
397 `collectionvolume` mediumtext default NULL,
398 `editionstatement` text default NULL,
399 `editionresponsibility` text default NULL,
400 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
401 `illus` varchar(255) default NULL,
402 `pages` varchar(255) default NULL,
404 `size` varchar(255) default NULL,
405 `place` varchar(255) default NULL,
406 `lccn` varchar(25) default NULL,
408 `url` varchar(255) default NULL,
409 `cn_source` varchar(10) default NULL,
410 `cn_class` varchar(30) default NULL,
411 `cn_item` varchar(10) default NULL,
412 `cn_suffix` varchar(10) default NULL,
413 `cn_sort` varchar(30) default NULL,
414 `totalissues` int(10),
415 `marcxml` longtext NOT NULL,
416 PRIMARY KEY (`biblioitemnumber`),
417 KEY `bibinoidx` (`biblioitemnumber`),
418 KEY `bibnoidx` (`biblionumber`),
420 KEY `publishercode` (`publishercode`),
421 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
422 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
425 -- Table structure for table `borrowers`
428 DROP TABLE IF EXISTS `borrowers`;
429 CREATE TABLE `borrowers` (
430 `borrowernumber` int(11) NOT NULL auto_increment,
431 `cardnumber` varchar(16) default NULL,
432 `surname` mediumtext NOT NULL,
435 `othernames` mediumtext,
437 `streetnumber` varchar(10) default NULL,
438 `streettype` varchar(50) default NULL,
439 `address` mediumtext NOT NULL,
441 `city` mediumtext NOT NULL,
442 `zipcode` varchar(25) default NULL,
445 `mobile` varchar(50) default NULL,
449 `B_streetnumber` varchar(10) default NULL,
450 `B_streettype` varchar(50) default NULL,
451 `B_address` varchar(100) default NULL,
453 `B_zipcode` varchar(25) default NULL,
455 `B_phone` mediumtext,
456 `dateofbirth` date default NULL,
457 `branchcode` varchar(10) NOT NULL default '',
458 `categorycode` varchar(10) NOT NULL default '',
459 `dateenrolled` date default NULL,
460 `dateexpiry` date default NULL,
461 `gonenoaddress` tinyint(1) default NULL,
462 `lost` tinyint(1) default NULL,
463 `debarred` tinyint(1) default NULL,
464 `contactname` mediumtext,
465 `contactfirstname` text,
467 `guarantorid` int(11) default NULL,
468 `borrowernotes` mediumtext,
469 `relationship` varchar(100) default NULL,
470 `ethnicity` varchar(50) default NULL,
471 `ethnotes` varchar(255) default NULL,
472 `sex` varchar(1) default NULL,
473 `password` varchar(30) default NULL,
474 `flags` int(11) default NULL,
475 `userid` varchar(30) default NULL,
476 `opacnote` mediumtext,
477 `contactnote` varchar(255) default NULL,
478 `sort1` varchar(80) default NULL,
479 `sort2` varchar(80) default NULL,
480 `altcontactfirstname` varchar(255) default NULL,
481 `altcontactsurname` varchar(255) default NULL,
482 `altcontactaddress1` varchar(255) default NULL,
483 `altcontactaddress2` varchar(255) default NULL,
484 `altcontactaddress3` varchar(255) default NULL,
485 `altcontactzipcode` varchar(50) default NULL,
486 `altcontactphone` varchar(50) default NULL,
487 UNIQUE KEY `cardnumber` (`cardnumber`),
488 PRIMARY KEY `borrowernumber` (`borrowernumber`),
489 KEY `categorycode` (`categorycode`),
490 KEY `branchcode` (`branchcode`),
491 KEY `userid` (`userid`),
492 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
493 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
497 -- Table structure for table `branchcategories`
500 DROP TABLE IF EXISTS `branchcategories`;
501 CREATE TABLE `branchcategories` (
502 `categorycode` varchar(10) NOT NULL default '',
503 `categoryname` varchar(32),
504 `codedescription` mediumtext,
505 `categorytype` varchar(16),
506 PRIMARY KEY (`categorycode`)
507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
510 -- Table structure for table `branches`
513 DROP TABLE IF EXISTS `branches`;
514 CREATE TABLE `branches` (
515 `branchcode` varchar(10) NOT NULL default '',
516 `branchname` mediumtext NOT NULL,
517 `branchaddress1` mediumtext,
518 `branchaddress2` mediumtext,
519 `branchaddress3` mediumtext,
520 `branchphone` mediumtext,
521 `branchfax` mediumtext,
522 `branchemail` mediumtext,
523 `issuing` tinyint(4) default NULL,
524 `branchip` varchar(15) default NULL,
525 `branchprinter` varchar(100) default NULL,
526 UNIQUE KEY `branchcode` (`branchcode`)
527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
530 -- Table structure for table `branchrelations`
533 DROP TABLE IF EXISTS `branchrelations`;
534 CREATE TABLE `branchrelations` (
535 `branchcode` varchar(10) NOT NULL default '',
536 `categorycode` varchar(10) NOT NULL default '',
537 PRIMARY KEY (`branchcode`,`categorycode`),
538 KEY `branchcode` (`branchcode`),
539 KEY `categorycode` (`categorycode`),
540 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
541 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
542 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
545 -- Table structure for table `branchtransfers`
548 DROP TABLE IF EXISTS `branchtransfers`;
549 CREATE TABLE `branchtransfers` (
550 `itemnumber` int(11) NOT NULL default 0,
551 `datesent` datetime default NULL,
552 `frombranch` varchar(10) NOT NULL default '',
553 `datearrived` datetime default NULL,
554 `tobranch` varchar(10) NOT NULL default '',
555 `comments` mediumtext,
556 KEY `frombranch` (`frombranch`),
557 KEY `tobranch` (`tobranch`),
558 KEY `itemnumber` (`itemnumber`),
559 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
560 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
561 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
566 -- Table structure for table `browser`
568 DROP TABLE IF EXISTS `browser`;
569 CREATE TABLE `browser` (
570 `level` int(11) NOT NULL,
571 `classification` varchar(20) NOT NULL,
572 `description` varchar(255) NOT NULL,
573 `number` bigint(20) NOT NULL,
574 `endnode` tinyint(4) NOT NULL
575 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
578 -- Table structure for table `categories`
581 DROP TABLE IF EXISTS `categories`;
582 CREATE TABLE `categories` (
583 `categorycode` varchar(10) NOT NULL default '',
584 `description` mediumtext,
585 `enrolmentperiod` smallint(6) default NULL,
586 `upperagelimit` smallint(6) default NULL,
587 `dateofbirthrequired` tinyint(1) default NULL,
588 `finetype` varchar(30) default NULL,
589 `bulk` tinyint(1) default NULL,
590 `enrolmentfee` decimal(28,6) default NULL,
591 `overduenoticerequired` tinyint(1) default NULL,
592 `issuelimit` smallint(6) default NULL,
593 `reservefee` decimal(28,6) default NULL,
594 `category_type` varchar(1) NOT NULL default 'A',
595 PRIMARY KEY (`categorycode`),
596 UNIQUE KEY `categorycode` (`categorycode`)
597 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
600 -- Table structure for table `categorytable`
603 DROP TABLE IF EXISTS `categorytable`;
604 CREATE TABLE `categorytable` (
605 `categorycode` varchar(5) NOT NULL default '',
607 `itemtypecodes` text,
608 PRIMARY KEY (`categorycode`)
609 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
612 -- Table structure for table `cities`
615 DROP TABLE IF EXISTS `cities`;
616 CREATE TABLE `cities` (
617 `cityid` int(11) NOT NULL auto_increment,
618 `city_name` varchar(100) NOT NULL default '',
619 `city_zipcode` varchar(20) default NULL,
620 PRIMARY KEY (`cityid`)
621 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
624 -- Table structure for table `class_sort_rules`
627 DROP TABLE IF EXISTS `class_sort_rules`;
628 CREATE TABLE `class_sort_rules` (
629 `class_sort_rule` varchar(10) NOT NULL default '',
630 `description` mediumtext,
631 `sort_routine` varchar(30) NOT NULL default '',
632 PRIMARY KEY (`class_sort_rule`),
633 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
634 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
637 -- Table structure for table `class_sources`
640 DROP TABLE IF EXISTS `class_sources`;
641 CREATE TABLE `class_sources` (
642 `cn_source` varchar(10) NOT NULL default '',
643 `description` mediumtext,
644 `used` tinyint(4) NOT NULL default 0,
645 `class_sort_rule` varchar(10) NOT NULL default '',
646 PRIMARY KEY (`cn_source`),
647 UNIQUE KEY `cn_source_idx` (`cn_source`),
648 KEY `used_idx` (`used`),
649 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
650 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
653 -- Table structure for table `currency`
656 DROP TABLE IF EXISTS `currency`;
657 CREATE TABLE `currency` (
658 `currency` varchar(10) NOT NULL default '',
659 `symbol` varchar(5) default NULL,
660 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
661 `rate` float(7,5) default NULL,
662 PRIMARY KEY (`currency`)
663 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
666 -- Table structure for table `deletedbiblio`
669 DROP TABLE IF EXISTS `deletedbiblio`;
670 CREATE TABLE `deletedbiblio` (
671 `biblionumber` int(11) NOT NULL default 0,
672 `frameworkcode` varchar(4) NOT NULL default '',
675 `unititle` mediumtext,
677 `serial` tinyint(1) default NULL,
678 `seriestitle` mediumtext,
679 `copyrightdate` smallint(6) default NULL,
680 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
681 `datecreated` DATE NOT NULL,
682 `abstract` mediumtext,
683 PRIMARY KEY (`biblionumber`),
684 KEY `blbnoidx` (`biblionumber`)
685 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
688 -- Table structure for table `deletedbiblioitems`
691 DROP TABLE IF EXISTS `deletedbiblioitems`;
692 CREATE TABLE `deletedbiblioitems` (
693 `biblioitemnumber` int(11) NOT NULL default 0,
694 `biblionumber` int(11) NOT NULL default 0,
697 `itemtype` varchar(10) default NULL,
698 `isbn` varchar(14) default NULL,
699 `issn` varchar(9) default NULL,
700 `publicationyear` text,
701 `publishercode` varchar(255) default NULL,
702 `volumedate` date default NULL,
704 `collectiontitle` mediumtext default NULL,
705 `collectionissn` text default NULL,
706 `collectionvolume` mediumtext default NULL,
707 `editionstatement` text default NULL,
708 `editionresponsibility` text default NULL,
709 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
710 `illus` varchar(255) default NULL,
711 `pages` varchar(255) default NULL,
713 `size` varchar(255) default NULL,
714 `place` varchar(255) default NULL,
715 `lccn` varchar(25) default NULL,
717 `url` varchar(255) default NULL,
718 `cn_source` varchar(10) default NULL,
719 `cn_class` varchar(30) default NULL,
720 `cn_item` varchar(10) default NULL,
721 `cn_suffix` varchar(10) default NULL,
722 `cn_sort` varchar(30) default NULL,
723 `totalissues` int(10),
724 `marcxml` longtext NOT NULL,
725 PRIMARY KEY (`biblioitemnumber`),
726 KEY `bibinoidx` (`biblioitemnumber`),
727 KEY `bibnoidx` (`biblionumber`),
729 KEY `publishercode` (`publishercode`)
730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
733 -- Table structure for table `deletedborrowers`
736 DROP TABLE IF EXISTS `deletedborrowers`;
737 CREATE TABLE `deletedborrowers` (
738 `borrowernumber` int(11) NOT NULL default 0,
739 `cardnumber` varchar(9) NOT NULL default '',
740 `surname` mediumtext NOT NULL,
743 `othernames` mediumtext,
745 `streetnumber` varchar(10) default NULL,
746 `streettype` varchar(50) default NULL,
747 `address` mediumtext NOT NULL,
749 `city` mediumtext NOT NULL,
750 `zipcode` varchar(25) default NULL,
753 `mobile` varchar(50) default NULL,
757 `B_streetnumber` varchar(10) default NULL,
758 `B_streettype` varchar(50) default NULL,
759 `B_address` varchar(100) default NULL,
761 `B_zipcode` varchar(25) default NULL,
763 `B_phone` mediumtext,
764 `dateofbirth` date default NULL,
765 `branchcode` varchar(10) NOT NULL default '',
766 `categorycode` varchar(2) default NULL,
767 `dateenrolled` date default NULL,
768 `dateexpiry` date default NULL,
769 `gonenoaddress` tinyint(1) default NULL,
770 `lost` tinyint(1) default NULL,
771 `debarred` tinyint(1) default NULL,
772 `contactname` mediumtext,
773 `contactfirstname` text,
775 `guarantorid` int(11) default NULL,
776 `borrowernotes` mediumtext,
777 `relationship` varchar(100) default NULL,
778 `ethnicity` varchar(50) default NULL,
779 `ethnotes` varchar(255) default NULL,
780 `sex` varchar(1) default NULL,
781 `password` varchar(30) default NULL,
782 `flags` int(11) default NULL,
783 `userid` varchar(30) default NULL,
784 `opacnote` mediumtext,
785 `contactnote` varchar(255) default NULL,
786 `sort1` varchar(80) default NULL,
787 `sort2` varchar(80) default NULL,
788 `altcontactfirstname` varchar(255) default NULL,
789 `altcontactsurname` varchar(255) default NULL,
790 `altcontactaddress1` varchar(255) default NULL,
791 `altcontactaddress2` varchar(255) default NULL,
792 `altcontactaddress3` varchar(255) default NULL,
793 `altcontactzipcode` varchar(50) default NULL,
794 `altcontactphone` varchar(50) default NULL,
795 KEY `borrowernumber` (`borrowernumber`),
796 KEY `cardnumber` (`cardnumber`)
797 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
800 -- Table structure for table `deleteditems`
803 DROP TABLE IF EXISTS `deleteditems`;
804 CREATE TABLE `deleteditems` (
805 `itemnumber` int(11) NOT NULL default 0,
806 `biblionumber` int(11) NOT NULL default 0,
807 `biblioitemnumber` int(11) NOT NULL default 0,
808 `barcode` varchar(20) default NULL,
809 `dateaccessioned` date default NULL,
810 `booksellerid` mediumtext default NULL,
811 `homebranch` varchar(10) default NULL,
812 `price` decimal(8,2) default NULL,
813 `replacementprice` decimal(8,2) default NULL,
814 `replacementpricedate` date default NULL,
815 `datelastborrowed` date default NULL,
816 `datelastseen` date default NULL,
817 `stack` tinyint(1) default NULL,
818 `notforloan` tinyint(1) NOT NULL default 0,
819 `damaged` tinyint(1) NOT NULL default 0,
820 `itemlost` tinyint(1) NOT NULL default 0,
821 `wthdrawn` tinyint(1) NOT NULL default 0,
822 `itemcallnumber` varchar(30) default NULL,
823 `issues` smallint(6) default NULL,
824 `renewals` smallint(6) default NULL,
825 `reserves` smallint(6) default NULL,
826 `restricted` tinyint(1) default NULL,
827 `itemnotes` mediumtext,
828 `holdingbranch` varchar(10) default NULL,
829 `paidfor` mediumtext,
830 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
831 `location` varchar(80) default NULL,
832 `onloan` date default NULL,
833 `cn_source` varchar(10) default NULL,
834 `cn_sort` varchar(30) default NULL,
835 `ccode` varchar(10) default NULL,
836 `materials` varchar(10) default NULL,
837 `uri` varchar(255) default NULL,
838 `itype` varchar(10) default NULL,
839 `more_subfields_xml` longtext default NULL,
841 `copynumber` smallint(6) default NULL,
842 PRIMARY KEY (`itemnumber`),
843 KEY `delitembarcodeidx` (`barcode`),
844 KEY `delitembinoidx` (`biblioitemnumber`),
845 KEY `delitembibnoidx` (`biblionumber`),
846 KEY `delhomebranch` (`homebranch`),
847 KEY `delholdingbranch` (`holdingbranch`)
848 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
851 -- Table structure for table `ethnicity`
854 DROP TABLE IF EXISTS `ethnicity`;
855 CREATE TABLE `ethnicity` (
856 `code` varchar(10) NOT NULL default '',
857 `name` varchar(255) default NULL,
859 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
862 -- Table structure for table `import_batches`
865 DROP TABLE IF EXISTS `import_batches`;
866 CREATE TABLE `import_batches` (
867 `import_batch_id` int(11) NOT NULL auto_increment,
868 `matcher_id` int(11) default NULL,
869 `template_id` int(11) default NULL,
870 `branchcode` varchar(10) default NULL,
871 `num_biblios` int(11) NOT NULL default 0,
872 `num_items` int(11) NOT NULL default 0,
873 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
874 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
875 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
876 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
877 `file_name` varchar(100),
878 `comments` mediumtext,
879 PRIMARY KEY (`import_batch_id`),
880 KEY `branchcode` (`branchcode`)
881 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
884 -- Table structure for table `import_records`
887 DROP TABLE IF EXISTS `import_records`;
888 CREATE TABLE `import_records` (
889 `import_record_id` int(11) NOT NULL auto_increment,
890 `import_batch_id` int(11) NOT NULL,
891 `branchcode` varchar(10) default NULL,
892 `record_sequence` int(11) NOT NULL default 0,
893 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
894 `import_date` DATE default NULL,
895 `marc` longblob NOT NULL,
896 `marcxml` longtext NOT NULL,
897 `marcxml_old` longtext NOT NULL,
898 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
899 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
900 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
901 `import_error` mediumtext,
902 `encoding` varchar(40) NOT NULL default '',
903 `z3950random` varchar(40) default NULL,
904 PRIMARY KEY (`import_record_id`),
905 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
906 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
907 KEY `branchcode` (`branchcode`),
908 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
909 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
912 -- Table structure for `import_record_matches`
914 DROP TABLE IF EXISTS `import_record_matches`;
915 CREATE TABLE `import_record_matches` (
916 `import_record_id` int(11) NOT NULL,
917 `candidate_match_id` int(11) NOT NULL,
918 `score` int(11) NOT NULL default 0,
919 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
920 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
921 KEY `record_score` (`import_record_id`, `score`)
922 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
925 -- Table structure for table `import_biblios`
928 DROP TABLE IF EXISTS `import_biblios`;
929 CREATE TABLE `import_biblios` (
930 `import_record_id` int(11) NOT NULL,
931 `matched_biblionumber` int(11) default NULL,
932 `control_number` varchar(25) default NULL,
933 `original_source` varchar(25) default NULL,
934 `title` varchar(128) default NULL,
935 `author` varchar(80) default NULL,
936 `isbn` varchar(14) default NULL,
937 `issn` varchar(9) default NULL,
938 `has_items` tinyint(1) NOT NULL default 0,
939 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
940 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
941 KEY `matched_biblionumber` (`matched_biblionumber`),
942 KEY `title` (`title`),
944 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
947 -- Table structure for table `import_items`
950 DROP TABLE IF EXISTS `import_items`;
951 CREATE TABLE `import_items` (
952 `import_items_id` int(11) NOT NULL auto_increment,
953 `import_record_id` int(11) NOT NULL,
954 `itemnumber` int(11) default NULL,
955 `branchcode` varchar(10) default NULL,
956 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
957 `marcxml` longtext NOT NULL,
958 `import_error` mediumtext,
959 PRIMARY KEY (`import_items_id`),
960 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
961 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
962 KEY `itemnumber` (`itemnumber`),
963 KEY `branchcode` (`branchcode`)
964 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
967 -- Table structure for table `issues`
970 DROP TABLE IF EXISTS `issues`;
971 CREATE TABLE `issues` (
972 `borrowernumber` int(11) default NULL,
973 `itemnumber` int(11) default NULL,
974 `date_due` date default NULL,
975 `branchcode` varchar(10) default NULL,
976 `issuingbranch` varchar(18) default NULL,
977 `returndate` date default NULL,
978 `lastreneweddate` date default NULL,
979 `return` varchar(4) default NULL,
980 `renewals` tinyint(4) default NULL,
981 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
982 `issuedate` date default NULL,
983 KEY `issuesborridx` (`borrowernumber`),
984 KEY `issuesitemidx` (`itemnumber`),
985 KEY `bordate` (`borrowernumber`,`timestamp`),
986 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
987 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
988 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
991 -- Table structure for table `issuingrules`
994 DROP TABLE IF EXISTS `issuingrules`;
995 CREATE TABLE `issuingrules` (
996 `categorycode` varchar(10) NOT NULL default '',
997 `itemtype` varchar(10) NOT NULL default '',
998 `restrictedtype` tinyint(1) default NULL,
999 `rentaldiscount` decimal(28,6) default NULL,
1000 `reservecharge` decimal(28,6) default NULL,
1001 `fine` decimal(28,6) default NULL,
1002 `firstremind` int(11) default NULL,
1003 `chargeperiod` int(11) default NULL,
1004 `accountsent` int(11) default NULL,
1005 `chargename` varchar(100) default NULL,
1006 `maxissueqty` int(4) default NULL,
1007 `issuelength` int(4) default NULL,
1008 `branchcode` varchar(10) NOT NULL default '',
1009 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1010 KEY `categorycode` (`categorycode`),
1011 KEY `itemtype` (`itemtype`)
1012 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1015 -- Table structure for table `items`
1018 DROP TABLE IF EXISTS `items`;
1019 CREATE TABLE `items` (
1020 `itemnumber` int(11) NOT NULL auto_increment,
1021 `biblionumber` int(11) NOT NULL default 0,
1022 `biblioitemnumber` int(11) NOT NULL default 0,
1023 `barcode` varchar(20) default NULL,
1024 `dateaccessioned` date default NULL,
1025 `booksellerid` mediumtext default NULL,
1026 `homebranch` varchar(10) default NULL,
1027 `price` decimal(8,2) default NULL,
1028 `replacementprice` decimal(8,2) default NULL,
1029 `replacementpricedate` date default NULL,
1030 `datelastborrowed` date default NULL,
1031 `datelastseen` date default NULL,
1032 `stack` tinyint(1) default NULL,
1033 `notforloan` tinyint(1) NOT NULL default 0,
1034 `damaged` tinyint(1) NOT NULL default 0,
1035 `itemlost` tinyint(1) NOT NULL default 0,
1036 `wthdrawn` tinyint(1) NOT NULL default 0,
1037 `itemcallnumber` varchar(30) default NULL,
1038 `issues` smallint(6) default NULL,
1039 `renewals` smallint(6) default NULL,
1040 `reserves` smallint(6) default NULL,
1041 `restricted` tinyint(1) default NULL,
1042 `itemnotes` mediumtext,
1043 `holdingbranch` varchar(10) default NULL,
1044 `paidfor` mediumtext,
1045 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1046 `location` varchar(80) default NULL,
1047 `onloan` date default NULL,
1048 `cn_source` varchar(10) default NULL,
1049 `cn_sort` varchar(30) default NULL,
1050 `ccode` varchar(10) default NULL,
1051 `materials` varchar(10) default NULL,
1052 `uri` varchar(255) default NULL,
1053 `itype` varchar(10) default NULL,
1054 `more_subfields_xml` longtext default NULL,
1055 `enumchron` varchar(80) default NULL,
1056 `copynumber` smallint(6) default NULL,
1057 PRIMARY KEY (`itemnumber`),
1058 UNIQUE KEY `itembarcodeidx` (`barcode`),
1059 KEY `itembinoidx` (`biblioitemnumber`),
1060 KEY `itembibnoidx` (`biblionumber`),
1061 KEY `homebranch` (`homebranch`),
1062 KEY `holdingbranch` (`holdingbranch`),
1063 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1064 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1065 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1066 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1069 -- Table structure for table `itemtypes`
1072 DROP TABLE IF EXISTS `itemtypes`;
1073 CREATE TABLE `itemtypes` (
1074 `itemtype` varchar(10) NOT NULL default '',
1075 `description` mediumtext,
1076 `renewalsallowed` smallint(6) default NULL,
1077 `rentalcharge` double(16,4) default NULL,
1078 `notforloan` smallint(6) default NULL,
1079 `imageurl` varchar(200) default NULL,
1081 PRIMARY KEY (`itemtype`),
1082 UNIQUE KEY `itemtype` (`itemtype`)
1083 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1086 -- Table structure for table `labels`
1089 DROP TABLE IF EXISTS `labels`;
1090 CREATE TABLE `labels` (
1091 `labelid` int(11) NOT NULL auto_increment,
1092 `batch_id` varchar(10) NOT NULL default 1,
1093 `itemnumber` varchar(100) NOT NULL default '',
1094 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1095 PRIMARY KEY (`labelid`)
1096 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1099 -- Table structure for table `labels_conf`
1102 DROP TABLE IF EXISTS `labels_conf`;
1103 CREATE TABLE `labels_conf` (
1104 `id` int(4) NOT NULL auto_increment,
1105 `barcodetype` char(100) default '',
1106 `title` int(1) default '0',
1107 `subtitle` int(1) default '0',
1108 `itemtype` int(1) default '0',
1109 `barcode` int(1) default '0',
1110 `dewey` int(1) default '0',
1111 `class` int(1) default NULL,
1112 `subclass` int(1) default '0',
1113 `itemcallnumber` int(1) default '0',
1114 `author` int(1) default '0',
1115 `issn` int(1) default '0',
1116 `isbn` int(1) default '0',
1117 `startlabel` int(2) NOT NULL default '1',
1118 `printingtype` char(32) default 'BAR',
1119 `layoutname` char(20) NOT NULL default 'TEST',
1120 `guidebox` int(1) default '0',
1121 `active` tinyint(1) default '1',
1122 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1123 `ccode` char(4) collate utf8_unicode_ci default NULL,
1124 `callnum_split` int(1) default NULL,
1125 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1127 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1130 -- Table structure for table `labels_profile`
1133 DROP TABLE IF EXISTS `labels_profile`;
1134 CREATE TABLE `labels_profile` (
1135 `tmpl_id` int(4) NOT NULL,
1136 `prof_id` int(4) NOT NULL,
1137 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1138 UNIQUE KEY `prof_id` (`prof_id`)
1139 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1142 -- Table structure for table `labels_templates`
1145 DROP TABLE IF EXISTS `labels_templates`;
1146 CREATE TABLE `labels_templates` (
1147 `tmpl_id` int(4) NOT NULL auto_increment,
1148 `tmpl_code` char(100) default '',
1149 `tmpl_desc` char(100) default '',
1150 `page_width` float default '0',
1151 `page_height` float default '0',
1152 `label_width` float default '0',
1153 `label_height` float default '0',
1154 `topmargin` float default '0',
1155 `leftmargin` float default '0',
1156 `cols` int(2) default '0',
1157 `rows` int(2) default '0',
1158 `colgap` float default '0',
1159 `rowgap` float default '0',
1160 `active` int(1) default NULL,
1161 `units` char(20) default 'PX',
1162 `fontsize` int(4) NOT NULL default '3',
1163 `font` char(10) NOT NULL default 'TR',
1164 PRIMARY KEY (`tmpl_id`)
1165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1168 -- Table structure for table `letter`
1171 DROP TABLE IF EXISTS `letter`;
1172 CREATE TABLE `letter` (
1173 `module` varchar(20) NOT NULL default '',
1174 `code` varchar(20) NOT NULL default '',
1175 `name` varchar(100) NOT NULL default '',
1176 `title` varchar(200) NOT NULL default '',
1178 PRIMARY KEY (`module`,`code`)
1179 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1182 -- Table structure for table `marc_subfield_structure`
1185 DROP TABLE IF EXISTS `marc_subfield_structure`;
1186 CREATE TABLE `marc_subfield_structure` (
1187 `tagfield` varchar(3) NOT NULL default '',
1188 `tagsubfield` varchar(1) NOT NULL default '',
1189 `liblibrarian` varchar(255) NOT NULL default '',
1190 `libopac` varchar(255) NOT NULL default '',
1191 `repeatable` tinyint(4) NOT NULL default 0,
1192 `mandatory` tinyint(4) NOT NULL default 0,
1193 `kohafield` varchar(40) default NULL,
1194 `tab` tinyint(1) default NULL,
1195 `authorised_value` varchar(20) default NULL,
1196 `authtypecode` varchar(20) default NULL,
1197 `value_builder` varchar(80) default NULL,
1198 `isurl` tinyint(1) default NULL,
1199 `hidden` tinyint(1) default NULL,
1200 `frameworkcode` varchar(4) NOT NULL default '',
1201 `seealso` varchar(1100) default NULL,
1202 `link` varchar(80) default NULL,
1203 `defaultvalue` text default NULL,
1204 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1205 KEY `kohafield_2` (`kohafield`),
1206 KEY `tab` (`frameworkcode`,`tab`),
1207 KEY `kohafield` (`frameworkcode`,`kohafield`)
1208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1211 -- Table structure for table `marc_tag_structure`
1214 DROP TABLE IF EXISTS `marc_tag_structure`;
1215 CREATE TABLE `marc_tag_structure` (
1216 `tagfield` varchar(3) NOT NULL default '',
1217 `liblibrarian` varchar(255) NOT NULL default '',
1218 `libopac` varchar(255) NOT NULL default '',
1219 `repeatable` tinyint(4) NOT NULL default 0,
1220 `mandatory` tinyint(4) NOT NULL default 0,
1221 `authorised_value` varchar(10) default NULL,
1222 `frameworkcode` varchar(4) NOT NULL default '',
1223 PRIMARY KEY (`frameworkcode`,`tagfield`)
1224 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1227 -- Table structure for table `marc_matchers`
1230 DROP TABLE IF EXISTS `marc_matchers`;
1231 CREATE TABLE `marc_matchers` (
1232 `matcher_id` int(11) NOT NULL auto_increment,
1233 `code` varchar(10) NOT NULL default '',
1234 `description` varchar(255) NOT NULL default '',
1235 `record_type` varchar(10) NOT NULL default 'biblio',
1236 `threshold` int(11) NOT NULL default 0,
1237 PRIMARY KEY (`matcher_id`),
1238 KEY `code` (`code`),
1239 KEY `record_type` (`record_type`)
1240 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1243 -- Table structure for table `matchpoints`
1245 DROP TABLE IF EXISTS `matchpoints`;
1246 CREATE TABLE `matchpoints` (
1247 `matcher_id` int(11) NOT NULL,
1248 `matchpoint_id` int(11) NOT NULL auto_increment,
1249 `search_index` varchar(30) NOT NULL default '',
1250 `score` int(11) NOT NULL default 0,
1251 PRIMARY KEY (`matchpoint_id`),
1252 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1253 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1254 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1258 -- Table structure for table `matchpoint_components`
1260 DROP TABLE IF EXISTS `matchpoint_components`;
1261 CREATE TABLE `matchpoint_components` (
1262 `matchpoint_id` int(11) NOT NULL,
1263 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1264 sequence int(11) NOT NULL default 0,
1265 tag varchar(3) NOT NULL default '',
1266 subfields varchar(40) NOT NULL default '',
1267 offset int(4) NOT NULL default 0,
1268 length int(4) NOT NULL default 0,
1269 PRIMARY KEY (`matchpoint_component_id`),
1270 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1271 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1272 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1273 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1276 -- Table structure for table `matcher_component_norms`
1278 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1279 CREATE TABLE `matchpoint_component_norms` (
1280 `matchpoint_component_id` int(11) NOT NULL,
1281 `sequence` int(11) NOT NULL default 0,
1282 `norm_routine` varchar(50) NOT NULL default '',
1283 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1284 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1285 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1286 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1289 -- Table structure for table `matcher_matchpoints`
1291 DROP TABLE IF EXISTS `matcher_matchpoints`;
1292 CREATE TABLE `matcher_matchpoints` (
1293 `matcher_id` int(11) NOT NULL,
1294 `matchpoint_id` int(11) NOT NULL,
1295 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1296 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1297 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1298 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1302 -- Table structure for table `matchchecks`
1304 DROP TABLE IF EXISTS `matchchecks`;
1305 CREATE TABLE `matchchecks` (
1306 `matcher_id` int(11) NOT NULL,
1307 `matchcheck_id` int(11) NOT NULL auto_increment,
1308 `source_matchpoint_id` int(11) NOT NULL,
1309 `target_matchpoint_id` int(11) NOT NULL,
1310 PRIMARY KEY (`matchcheck_id`),
1311 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1312 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1313 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1314 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1315 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1316 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1320 -- Table structure for table `mediatypetable`
1323 DROP TABLE IF EXISTS `mediatypetable`;
1324 CREATE TABLE `mediatypetable` (
1325 `mediatypecode` varchar(5) NOT NULL default '',
1327 `itemtypecodes` text,
1328 PRIMARY KEY (`mediatypecode`)
1329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1332 -- Table structure for table `notifys`
1335 DROP TABLE IF EXISTS `notifys`;
1336 CREATE TABLE `notifys` (
1337 `notify_id` int(11) NOT NULL default 0,
1338 `borrowernumber` int(11) NOT NULL default 0,
1339 `itemnumber` int(11) NOT NULL default 0,
1340 `notify_date` date default NULL,
1341 `notify_send_date` date default NULL,
1342 `notify_level` int(1) NOT NULL default 0,
1343 `method` varchar(20) NOT NULL default ''
1344 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1347 -- Table structure for table `nozebra`
1350 DROP TABLE IF EXISTS `nozebra`;
1351 CREATE TABLE `nozebra` (
1352 `server` varchar(20) NOT NULL,
1353 `indexname` varchar(40) NOT NULL,
1354 `value` varchar(250) NOT NULL,
1355 `biblionumbers` longtext NOT NULL,
1356 KEY `indexname` (`server`,`indexname`),
1357 KEY `value` (`server`,`value`))
1358 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1361 -- Table structure for table `old_issues`
1364 DROP TABLE IF EXISTS `old_issues`;
1365 CREATE TABLE `old_issues` (
1366 `borrowernumber` int(11) default NULL,
1367 `itemnumber` int(11) default NULL,
1368 `date_due` date default NULL,
1369 `branchcode` varchar(10) default NULL,
1370 `issuingbranch` varchar(18) default NULL,
1371 `returndate` date default NULL,
1372 `lastreneweddate` date default NULL,
1373 `return` varchar(4) default NULL,
1374 `renewals` tinyint(4) default NULL,
1375 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1376 `issuedate` date default NULL,
1377 KEY `old_issuesborridx` (`borrowernumber`),
1378 KEY `old_issuesitemidx` (`itemnumber`),
1379 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1380 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1381 ON DELETE SET NULL ON UPDATE SET NULL,
1382 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1383 ON DELETE SET NULL ON UPDATE SET NULL
1384 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1387 -- Table structure for table `old_reserves`
1389 DROP TABLE IF EXISTS `old_reserves`;
1390 CREATE TABLE `old_reserves` (
1391 `borrowernumber` int(11) default NULL,
1392 `reservedate` date default NULL,
1393 `biblionumber` int(11) default NULL,
1394 `constrainttype` varchar(1) default NULL,
1395 `branchcode` varchar(10) default NULL,
1396 `notificationdate` date default NULL,
1397 `reminderdate` date default NULL,
1398 `cancellationdate` date default NULL,
1399 `reservenotes` mediumtext,
1400 `priority` smallint(6) default NULL,
1401 `found` varchar(1) default NULL,
1402 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1403 `itemnumber` int(11) default NULL,
1404 `waitingdate` date default NULL,
1405 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1406 KEY `old_reserves_biblionumber` (`biblionumber`),
1407 KEY `old_reserves_itemnumber` (`itemnumber`),
1408 KEY `old_reserves_branchcode` (`branchcode`),
1409 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1410 ON DELETE SET NULL ON UPDATE SET NULL,
1411 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1412 ON DELETE SET NULL ON UPDATE SET NULL,
1413 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1414 ON DELETE SET NULL ON UPDATE SET NULL
1415 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1418 -- Table structure for table `opac_news`
1421 DROP TABLE IF EXISTS `opac_news`;
1422 CREATE TABLE `opac_news` (
1423 `idnew` int(10) unsigned NOT NULL auto_increment,
1424 `title` varchar(250) NOT NULL default '',
1425 `new` text NOT NULL,
1426 `lang` varchar(25) NOT NULL default '',
1427 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1428 `expirationdate` date default NULL,
1429 `number` int(11) default NULL,
1430 PRIMARY KEY (`idnew`)
1431 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1434 -- Table structure for table `overduerules`
1437 DROP TABLE IF EXISTS `overduerules`;
1438 CREATE TABLE `overduerules` (
1439 `branchcode` varchar(10) NOT NULL default '',
1440 `categorycode` varchar(2) NOT NULL default '',
1441 `delay1` int(4) default 0,
1442 `letter1` varchar(20) default NULL,
1443 `debarred1` varchar(1) default 0,
1444 `delay2` int(4) default 0,
1445 `debarred2` varchar(1) default 0,
1446 `letter2` varchar(20) default NULL,
1447 `delay3` int(4) default 0,
1448 `letter3` varchar(20) default NULL,
1449 `debarred3` int(1) default 0,
1450 PRIMARY KEY (`branchcode`,`categorycode`)
1451 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1454 -- Table structure for table `patronimage`
1457 DROP TABLE IF EXISTS `patronimage`;
1458 CREATE TABLE `patronimage` (
1459 `cardnumber` varchar(16) NOT NULL,
1460 `mimetype` varchar(15) NOT NULL,
1461 `imagefile` mediumblob NOT NULL,
1462 PRIMARY KEY (`cardnumber`),
1463 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1464 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1467 -- Table structure for table `printers`
1470 DROP TABLE IF EXISTS `printers`;
1471 CREATE TABLE `printers` (
1472 `printername` varchar(40) NOT NULL default '',
1473 `printqueue` varchar(20) default NULL,
1474 `printtype` varchar(20) default NULL,
1475 PRIMARY KEY (`printername`)
1476 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1479 -- Table structure for table `printers_profile`
1482 DROP TABLE IF EXISTS `printers_profile`;
1483 CREATE TABLE `printers_profile` (
1484 `prof_id` int(4) NOT NULL auto_increment,
1485 `printername` varchar(40) NOT NULL,
1486 `tmpl_id` int(4) NOT NULL,
1487 `paper_bin` varchar(20) NOT NULL,
1488 `offset_horz` float default NULL,
1489 `offset_vert` float default NULL,
1490 `creep_horz` float default NULL,
1491 `creep_vert` float default NULL,
1492 `unit` char(20) NOT NULL default 'POINT',
1493 PRIMARY KEY (`prof_id`),
1494 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1495 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1496 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1499 -- Table structure for table `repeatable_holidays`
1502 DROP TABLE IF EXISTS `repeatable_holidays`;
1503 CREATE TABLE `repeatable_holidays` (
1504 `id` int(11) NOT NULL auto_increment,
1505 `branchcode` varchar(10) NOT NULL default '',
1506 `weekday` smallint(6) default NULL,
1507 `day` smallint(6) default NULL,
1508 `month` smallint(6) default NULL,
1509 `title` varchar(50) NOT NULL default '',
1510 `description` text NOT NULL,
1512 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1515 -- Table structure for table `reports_dictionary`
1518 DROP TABLE IF EXISTS `reports_dictionary`;
1519 CREATE TABLE reports_dictionary (
1520 `id` int(11) NOT NULL auto_increment,
1521 `name` varchar(255) default NULL,
1523 `date_created` datetime default NULL,
1524 `date_modified` datetime default NULL,
1526 `area` int(11) default NULL,
1528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1531 -- Table structure for table `reserveconstraints`
1534 DROP TABLE IF EXISTS `reserveconstraints`;
1535 CREATE TABLE `reserveconstraints` (
1536 `borrowernumber` int(11) NOT NULL default 0,
1537 `reservedate` date default NULL,
1538 `biblionumber` int(11) NOT NULL default 0,
1539 `biblioitemnumber` int(11) default NULL,
1540 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1541 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1544 -- Table structure for table `reserves`
1547 DROP TABLE IF EXISTS `reserves`;
1548 CREATE TABLE `reserves` (
1549 `borrowernumber` int(11) NOT NULL default 0,
1550 `reservedate` date default NULL,
1551 `biblionumber` int(11) NOT NULL default 0,
1552 `constrainttype` varchar(1) default NULL,
1553 `branchcode` varchar(10) default NULL,
1554 `notificationdate` date default NULL,
1555 `reminderdate` date default NULL,
1556 `cancellationdate` date default NULL,
1557 `reservenotes` mediumtext,
1558 `priority` smallint(6) default NULL,
1559 `found` varchar(1) default NULL,
1560 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1561 `itemnumber` int(11) default NULL,
1562 `waitingdate` date default NULL,
1563 KEY `borrowernumber` (`borrowernumber`),
1564 KEY `biblionumber` (`biblionumber`),
1565 KEY `itemnumber` (`itemnumber`),
1566 KEY `branchcode` (`branchcode`),
1567 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1568 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1569 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1570 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1571 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1574 -- Table structure for table `reviews`
1577 DROP TABLE IF EXISTS `reviews`;
1578 CREATE TABLE `reviews` (
1579 `reviewid` int(11) NOT NULL auto_increment,
1580 `borrowernumber` int(11) default NULL,
1581 `biblionumber` int(11) default NULL,
1583 `approved` tinyint(4) default NULL,
1584 `datereviewed` datetime default NULL,
1585 PRIMARY KEY (`reviewid`)
1586 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1589 -- Table structure for table `roadtype`
1592 DROP TABLE IF EXISTS `roadtype`;
1593 CREATE TABLE `roadtype` (
1594 `roadtypeid` int(11) NOT NULL auto_increment,
1595 `road_type` varchar(100) NOT NULL default '',
1596 PRIMARY KEY (`roadtypeid`)
1597 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1600 -- Table structure for table `saved_sql`
1603 DROP TABLE IF EXISTS `saved_sql`;
1604 CREATE TABLE saved_sql (
1605 `id` int(11) NOT NULL auto_increment,
1606 `borrowernumber` int(11) default NULL,
1607 `date_created` datetime default NULL,
1608 `last_modified` datetime default NULL,
1610 `last_run` datetime default NULL,
1611 `report_name` varchar(255) default NULL,
1612 `type` varchar(255) default NULL,
1615 KEY boridx (`borrowernumber`)
1616 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1620 -- Table structure for `saved_reports`
1623 DROP TABLE IF EXISTS `saved_reports`;
1624 CREATE TABLE saved_reports (
1625 `id` int(11) NOT NULL auto_increment,
1626 `report_id` int(11) default NULL,
1628 `date_run` datetime default NULL,
1630 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1634 -- Table structure for table `serial`
1637 DROP TABLE IF EXISTS `serial`;
1638 CREATE TABLE `serial` (
1639 `serialid` int(11) NOT NULL auto_increment,
1640 `biblionumber` varchar(100) NOT NULL default '',
1641 `subscriptionid` varchar(100) NOT NULL default '',
1642 `serialseq` varchar(100) NOT NULL default '',
1643 `status` tinyint(4) NOT NULL default 0,
1644 `planneddate` date default NULL,
1646 `publisheddate` date default NULL,
1647 `itemnumber` text default NULL,
1648 `claimdate` date default NULL,
1649 `routingnotes` text,
1650 PRIMARY KEY (`serialid`)
1651 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1654 -- Table structure for table `sessions`
1657 DROP TABLE IF EXISTS sessions;
1658 CREATE TABLE sessions (
1659 `id` varchar(32) NOT NULL,
1660 `a_session` text NOT NULL,
1662 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1665 -- Table structure for table `special_holidays`
1668 DROP TABLE IF EXISTS `special_holidays`;
1669 CREATE TABLE `special_holidays` (
1670 `id` int(11) NOT NULL auto_increment,
1671 `branchcode` varchar(10) NOT NULL default '',
1672 `day` smallint(6) NOT NULL default 0,
1673 `month` smallint(6) NOT NULL default 0,
1674 `year` smallint(6) NOT NULL default 0,
1675 `isexception` smallint(1) NOT NULL default 1,
1676 `title` varchar(50) NOT NULL default '',
1677 `description` text NOT NULL,
1679 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1682 -- Table structure for table `statistics`
1685 DROP TABLE IF EXISTS `statistics`;
1686 CREATE TABLE `statistics` (
1687 `datetime` datetime default NULL,
1688 `branch` varchar(10) default NULL,
1689 `proccode` varchar(4) default NULL,
1690 `value` double(16,4) default NULL,
1691 `type` varchar(16) default NULL,
1693 `usercode` varchar(10) default NULL,
1694 `itemnumber` int(11) default NULL,
1695 `itemtype` varchar(10) default NULL,
1696 `borrowernumber` int(11) default NULL,
1697 `associatedborrower` int(11) default NULL,
1698 KEY `timeidx` (`datetime`)
1699 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1702 -- Table structure for table `stopwords`
1705 DROP TABLE IF EXISTS `stopwords`;
1706 CREATE TABLE `stopwords` (
1707 `word` varchar(255) default NULL
1708 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1711 -- Table structure for table `subcategorytable`
1714 DROP TABLE IF EXISTS `subcategorytable`;
1715 CREATE TABLE `subcategorytable` (
1716 `subcategorycode` varchar(5) NOT NULL default '',
1718 `itemtypecodes` text,
1719 PRIMARY KEY (`subcategorycode`)
1720 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1723 -- Table structure for table `subscription`
1726 DROP TABLE IF EXISTS `subscription`;
1727 CREATE TABLE `subscription` (
1728 `biblionumber` int(11) NOT NULL default 0,
1729 `subscriptionid` int(11) NOT NULL auto_increment,
1730 `librarian` varchar(100) default '',
1731 `startdate` date default NULL,
1732 `aqbooksellerid` int(11) default 0,
1733 `cost` int(11) default 0,
1734 `aqbudgetid` int(11) default 0,
1735 `weeklength` int(11) default 0,
1736 `monthlength` int(11) default 0,
1737 `numberlength` int(11) default 0,
1738 `periodicity` tinyint(4) default 0,
1739 `dow` varchar(100) default '',
1740 `numberingmethod` varchar(100) default '',
1742 `status` varchar(100) NOT NULL default '',
1743 `add1` int(11) default 0,
1744 `every1` int(11) default 0,
1745 `whenmorethan1` int(11) default 0,
1746 `setto1` int(11) default NULL,
1747 `lastvalue1` int(11) default NULL,
1748 `add2` int(11) default 0,
1749 `every2` int(11) default 0,
1750 `whenmorethan2` int(11) default 0,
1751 `setto2` int(11) default NULL,
1752 `lastvalue2` int(11) default NULL,
1753 `add3` int(11) default 0,
1754 `every3` int(11) default 0,
1755 `innerloop1` int(11) default 0,
1756 `innerloop2` int(11) default 0,
1757 `innerloop3` int(11) default 0,
1758 `whenmorethan3` int(11) default 0,
1759 `setto3` int(11) default NULL,
1760 `lastvalue3` int(11) default NULL,
1761 `issuesatonce` tinyint(3) NOT NULL default 1,
1762 `firstacquidate` date default NULL,
1763 `manualhistory` tinyint(1) NOT NULL default 0,
1764 `irregularity` text,
1765 `letter` varchar(20) default NULL,
1766 `numberpattern` tinyint(3) default 0,
1767 `distributedto` text,
1768 `internalnotes` longtext,
1770 `branchcode` varchar(10) NOT NULL default '',
1771 `hemisphere` tinyint(3) default 0,
1772 `lastbranch` varchar(10),
1773 PRIMARY KEY (`subscriptionid`)
1774 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1777 -- Table structure for table `subscriptionhistory`
1780 DROP TABLE IF EXISTS `subscriptionhistory`;
1781 CREATE TABLE `subscriptionhistory` (
1782 `biblionumber` int(11) NOT NULL default 0,
1783 `subscriptionid` int(11) NOT NULL default 0,
1784 `histstartdate` date default NULL,
1785 `enddate` date default NULL,
1786 `missinglist` longtext NOT NULL,
1787 `recievedlist` longtext NOT NULL,
1788 `opacnote` varchar(150) NOT NULL default '',
1789 `librariannote` varchar(150) NOT NULL default '',
1790 PRIMARY KEY (`subscriptionid`),
1791 KEY `biblionumber` (`biblionumber`)
1792 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1795 -- Table structure for table `subscriptionroutinglist`
1798 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1799 CREATE TABLE `subscriptionroutinglist` (
1800 `routingid` int(11) NOT NULL auto_increment,
1801 `borrowernumber` int(11) default NULL,
1802 `ranking` int(11) default NULL,
1803 `subscriptionid` int(11) default NULL,
1804 PRIMARY KEY (`routingid`)
1805 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1808 -- Table structure for table `suggestions`
1811 DROP TABLE IF EXISTS `suggestions`;
1812 CREATE TABLE `suggestions` (
1813 `suggestionid` int(8) NOT NULL auto_increment,
1814 `suggestedby` int(11) NOT NULL default 0,
1815 `managedby` int(11) default NULL,
1816 `STATUS` varchar(10) NOT NULL default '',
1818 `author` varchar(80) default NULL,
1819 `title` varchar(80) default NULL,
1820 `copyrightdate` smallint(6) default NULL,
1821 `publishercode` varchar(255) default NULL,
1822 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1823 `volumedesc` varchar(255) default NULL,
1824 `publicationyear` smallint(6) default 0,
1825 `place` varchar(255) default NULL,
1826 `isbn` varchar(10) default NULL,
1827 `mailoverseeing` smallint(1) default 0,
1828 `biblionumber` int(11) default NULL,
1830 PRIMARY KEY (`suggestionid`),
1831 KEY `suggestedby` (`suggestedby`),
1832 KEY `managedby` (`managedby`)
1833 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1836 -- Table structure for table `systempreferences`
1839 DROP TABLE IF EXISTS `systempreferences`;
1840 CREATE TABLE `systempreferences` (
1841 `variable` varchar(50) NOT NULL default '',
1843 `options` mediumtext,
1845 `type` varchar(20) default NULL,
1846 PRIMARY KEY (`variable`)
1847 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1850 -- Table structure for table `tags`
1853 DROP TABLE IF EXISTS `tags`;
1854 CREATE TABLE `tags` (
1855 `entry` varchar(255) NOT NULL default '',
1856 `weight` bigint(20) NOT NULL default 0,
1857 PRIMARY KEY (`entry`)
1858 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1861 -- Table structure for table `userflags`
1864 DROP TABLE IF EXISTS `userflags`;
1865 CREATE TABLE `userflags` (
1866 `bit` int(11) NOT NULL default 0,
1867 `flag` varchar(30) default NULL,
1868 `flagdesc` varchar(255) default NULL,
1869 `defaulton` int(11) default NULL,
1871 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1874 -- Table structure for table `virtualshelves`
1877 DROP TABLE IF EXISTS `virtualshelves`;
1878 CREATE TABLE `virtualshelves` (
1879 `shelfnumber` int(11) NOT NULL auto_increment,
1880 `shelfname` varchar(255) default NULL,
1881 `owner` varchar(80) default NULL,
1882 `category` varchar(1) default NULL,
1883 `sortfield` varchar(16) default NULL,
1884 PRIMARY KEY (`shelfnumber`)
1885 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1888 -- Table structure for table `virtualshelfcontents`
1891 DROP TABLE IF EXISTS `virtualshelfcontents`;
1892 CREATE TABLE `virtualshelfcontents` (
1893 `shelfnumber` int(11) NOT NULL default 0,
1894 `biblionumber` int(11) NOT NULL default 0,
1895 `flags` int(11) default NULL,
1896 `dateadded` timestamp NULL default NULL,
1897 KEY `shelfnumber` (`shelfnumber`),
1898 KEY `biblionumber` (`biblionumber`),
1899 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1900 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1901 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1904 -- Table structure for table `z3950servers`
1907 DROP TABLE IF EXISTS `z3950servers`;
1908 CREATE TABLE `z3950servers` (
1909 `host` varchar(255) default NULL,
1910 `port` int(11) default NULL,
1911 `db` varchar(255) default NULL,
1912 `userid` varchar(255) default NULL,
1913 `password` varchar(255) default NULL,
1915 `id` int(11) NOT NULL auto_increment,
1916 `checked` smallint(6) default NULL,
1917 `rank` int(11) default NULL,
1918 `syntax` varchar(80) default NULL,
1920 `position` enum('primary','secondary','') NOT NULL default 'primary',
1921 `type` enum('zed','opensearch') NOT NULL default 'zed',
1922 `encoding` text default NULL,
1923 `description` text NOT NULL,
1925 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1928 -- Table structure for table `zebraqueue`
1931 DROP TABLE IF EXISTS `zebraqueue`;
1932 CREATE TABLE `zebraqueue` (
1933 `id` int(11) NOT NULL auto_increment,
1934 `biblio_auth_number` int(11) NOT NULL default '0',
1935 `operation` char(20) NOT NULL default '',
1936 `server` char(20) NOT NULL default '',
1937 `done` int(11) NOT NULL default '0',
1938 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1940 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
1941 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1943 DROP TABLE IF EXISTS `services_throttle`;
1944 CREATE TABLE `services_throttle` (
1945 `service_type` varchar(10) NOT NULL default '',
1946 `service_count` varchar(45) default NULL,
1947 PRIMARY KEY (`service_type`)
1948 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1950 -- http://www.w3.org/International/articles/language-tags/
1953 DROP TABLE IF EXISTS language_subtag_registry;
1954 CREATE TABLE language_subtag_registry (
1956 type varchar(25), -- language-script-region-variant-extension-privateuse
1957 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
1959 KEY `subtag` (`subtag`)
1960 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1962 -- TODO: add suppress_scripts
1963 -- this maps three letter codes defined in iso639.2 back to their
1964 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
1965 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
1966 CREATE TABLE language_rfc4646_to_iso639 (
1967 rfc4646_subtag varchar(25),
1968 iso639_2_code varchar(25),
1969 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1970 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1972 DROP TABLE IF EXISTS language_descriptions;
1973 CREATE TABLE language_descriptions (
1977 description varchar(255),
1979 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1981 -- bi-directional support, keyed by script subcode
1982 DROP TABLE IF EXISTS language_script_bidi;
1983 CREATE TABLE language_script_bidi (
1984 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
1985 bidi varchar(3), -- rtl ltr
1986 KEY `rfc4646_subtag` (`rfc4646_subtag`)
1987 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1989 -- TODO: need to map language subtags to script subtags for detection
1990 -- of bidi when script is not specified (like ar, he)
1991 DROP TABLE IF EXISTS language_script_mapping;
1992 CREATE TABLE language_script_mapping (
1993 language_subtag varchar(25),
1994 script_subtag varchar(25),
1995 KEY `language_subtag` (`language_subtag`)
1996 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1998 DROP TABLE IF EXISTS serialitems;
1999 CREATE TABLE serialitems (
2000 serialid int(11) NOT NULL,
2001 itemnumber int(11) NOT NULL,
2002 UNIQUE KEY `serialididx` (`serialid`)
2003 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2005 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2006 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2007 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2008 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2009 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2010 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2011 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2012 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;