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 `lastincrement` decimal(28,6) default NULL,
34 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
35 `notify_id` int(11) NOT NULL default 0,
36 `notify_level` int(2) NOT NULL default 0,
37 KEY `acctsborridx` (`borrowernumber`),
38 KEY `timeidx` (`timestamp`),
39 KEY `itemnumber` (`itemnumber`),
40 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
41 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
45 -- Table structure for table `accountoffsets`
48 DROP TABLE IF EXISTS `accountoffsets`;
49 CREATE TABLE `accountoffsets` (
50 `borrowernumber` int(11) NOT NULL default 0,
51 `accountno` smallint(6) NOT NULL default 0,
52 `offsetaccount` smallint(6) NOT NULL default 0,
53 `offsetamount` decimal(28,6) default NULL,
54 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
55 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,
127 `name` mediumtext NOT NULL,
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,
342 `imageurl` varchar(200) default NULL,
344 KEY `name` (`category`),
346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
349 -- Table structure for table `biblio`
352 DROP TABLE IF EXISTS `biblio`;
353 CREATE TABLE `biblio` (
354 `biblionumber` int(11) NOT NULL auto_increment,
355 `frameworkcode` varchar(4) NOT NULL default '',
358 `unititle` mediumtext,
360 `serial` tinyint(1) default NULL,
361 `seriestitle` mediumtext,
362 `copyrightdate` smallint(6) default NULL,
363 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
364 `datecreated` DATE NOT NULL,
365 `abstract` mediumtext,
366 PRIMARY KEY (`biblionumber`),
367 KEY `blbnoidx` (`biblionumber`)
368 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
371 -- Table structure for table `biblio_framework`
374 DROP TABLE IF EXISTS `biblio_framework`;
375 CREATE TABLE `biblio_framework` (
376 `frameworkcode` varchar(4) NOT NULL default '',
377 `frameworktext` varchar(255) NOT NULL default '',
378 PRIMARY KEY (`frameworkcode`)
379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
382 -- Table structure for table `biblioitems`
385 DROP TABLE IF EXISTS `biblioitems`;
386 CREATE TABLE `biblioitems` (
387 `biblioitemnumber` int(11) NOT NULL auto_increment,
388 `biblionumber` int(11) NOT NULL default 0,
391 `itemtype` varchar(10) default NULL,
392 `isbn` varchar(30) default NULL,
393 `issn` varchar(9) default NULL,
394 `publicationyear` text,
395 `publishercode` varchar(255) default NULL,
396 `volumedate` date default NULL,
398 `collectiontitle` mediumtext default NULL,
399 `collectionissn` text default NULL,
400 `collectionvolume` mediumtext default NULL,
401 `editionstatement` text default NULL,
402 `editionresponsibility` text default NULL,
403 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
404 `illus` varchar(255) default NULL,
405 `pages` varchar(255) default NULL,
407 `size` varchar(255) default NULL,
408 `place` varchar(255) default NULL,
409 `lccn` varchar(25) default NULL,
411 `url` varchar(255) default NULL,
412 `cn_source` varchar(10) default NULL,
413 `cn_class` varchar(30) default NULL,
414 `cn_item` varchar(10) default NULL,
415 `cn_suffix` varchar(10) default NULL,
416 `cn_sort` varchar(30) default NULL,
417 `totalissues` int(10),
418 `marcxml` longtext NOT NULL,
419 PRIMARY KEY (`biblioitemnumber`),
420 KEY `bibinoidx` (`biblioitemnumber`),
421 KEY `bibnoidx` (`biblionumber`),
423 KEY `publishercode` (`publishercode`),
425 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
429 -- Table structure for table `borrowers`
432 DROP TABLE IF EXISTS `borrowers`;
433 CREATE TABLE `borrowers` (
434 `borrowernumber` int(11) NOT NULL auto_increment,
435 `cardnumber` varchar(16) default NULL,
436 `surname` mediumtext NOT NULL,
439 `othernames` mediumtext,
441 `streetnumber` varchar(10) default NULL,
442 `streettype` varchar(50) default NULL,
443 `address` mediumtext NOT NULL,
445 `city` mediumtext NOT NULL,
446 `zipcode` varchar(25) default NULL,
449 `mobile` varchar(50) default NULL,
453 `B_streetnumber` varchar(10) default NULL,
454 `B_streettype` varchar(50) default NULL,
455 `B_address` varchar(100) default NULL,
457 `B_zipcode` varchar(25) default NULL,
459 `B_phone` mediumtext,
460 `dateofbirth` date default NULL,
461 `branchcode` varchar(10) NOT NULL default '',
462 `categorycode` varchar(10) NOT NULL default '',
463 `dateenrolled` date default NULL,
464 `dateexpiry` date default NULL,
465 `gonenoaddress` tinyint(1) default NULL,
466 `lost` tinyint(1) default NULL,
467 `debarred` tinyint(1) default NULL,
468 `contactname` mediumtext,
469 `contactfirstname` text,
471 `guarantorid` int(11) default NULL,
472 `borrowernotes` mediumtext,
473 `relationship` varchar(100) default NULL,
474 `ethnicity` varchar(50) default NULL,
475 `ethnotes` varchar(255) default NULL,
476 `sex` varchar(1) default NULL,
477 `password` varchar(30) default NULL,
478 `flags` int(11) default NULL,
479 `userid` varchar(30) default NULL,
480 `opacnote` mediumtext,
481 `contactnote` varchar(255) default NULL,
482 `sort1` varchar(80) default NULL,
483 `sort2` varchar(80) default NULL,
484 `altcontactfirstname` varchar(255) default NULL,
485 `altcontactsurname` varchar(255) default NULL,
486 `altcontactaddress1` varchar(255) default NULL,
487 `altcontactaddress2` varchar(255) default NULL,
488 `altcontactaddress3` varchar(255) default NULL,
489 `altcontactzipcode` varchar(50) default NULL,
490 `altcontactphone` varchar(50) default NULL,
491 `smsalertnumber` varchar(50) default NULL,
492 UNIQUE KEY `cardnumber` (`cardnumber`),
493 PRIMARY KEY `borrowernumber` (`borrowernumber`),
494 KEY `categorycode` (`categorycode`),
495 KEY `branchcode` (`branchcode`),
496 KEY `userid` (`userid`),
497 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
498 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
499 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
502 -- Table structure for table `borrower_attribute_types`
505 DROP TABLE IF EXISTS `borrower_attribute_types`;
506 CREATE TABLE `borrower_attribute_types` (
507 `code` varchar(10) NOT NULL,
508 `description` varchar(255) NOT NULL,
509 `repeatable` tinyint(1) NOT NULL default 0,
510 `unique_id` tinyint(1) NOT NULL default 0,
511 `opac_display` tinyint(1) NOT NULL default 0,
512 `password_allowed` tinyint(1) NOT NULL default 0,
513 `staff_searchable` tinyint(1) NOT NULL default 0,
514 `authorised_value_category` varchar(10) default NULL,
516 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
519 -- Table structure for table `borrower_attributes`
522 DROP TABLE IF EXISTS `borrower_attributes`;
523 CREATE TABLE `borrower_attributes` (
524 `borrowernumber` int(11) NOT NULL,
525 `code` varchar(10) NOT NULL,
526 `attribute` varchar(64) default NULL,
527 `password` varchar(64) default NULL,
528 KEY `borrowernumber` (`borrowernumber`),
529 KEY `code_attribute` (`code`, `attribute`),
530 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
531 ON DELETE CASCADE ON UPDATE CASCADE,
532 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
533 ON DELETE CASCADE ON UPDATE CASCADE
534 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
536 CREATE TABLE `branch_item_rules` (
537 `branchcode` varchar(10) NOT NULL,
538 `itemtype` varchar(10) NOT NULL,
539 `holdallowed` tinyint(1) default NULL,
540 PRIMARY KEY (`itemtype`,`branchcode`),
541 KEY `branch_item_rules_ibfk_2` (`branchcode`),
542 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
543 ON DELETE CASCADE ON UPDATE CASCADE,
544 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
545 ON DELETE CASCADE ON UPDATE CASCADE
546 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
549 -- Table structure for table `branchcategories`
552 DROP TABLE IF EXISTS `branchcategories`;
553 CREATE TABLE `branchcategories` (
554 `categorycode` varchar(10) NOT NULL default '',
555 `categoryname` varchar(32),
556 `codedescription` mediumtext,
557 `categorytype` varchar(16),
558 PRIMARY KEY (`categorycode`)
559 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
562 -- Table structure for table `branches`
565 DROP TABLE IF EXISTS `branches`;
566 CREATE TABLE `branches` (
567 `branchcode` varchar(10) NOT NULL default '',
568 `branchname` mediumtext NOT NULL,
569 `branchaddress1` mediumtext,
570 `branchaddress2` mediumtext,
571 `branchaddress3` mediumtext,
572 `branchzip` varchar(25) default NULL,
573 `branchcity` mediumtext,
574 `branchcountry` text,
575 `branchphone` mediumtext,
576 `branchfax` mediumtext,
577 `branchemail` mediumtext,
578 `branchurl` mediumtext,
579 `issuing` tinyint(4) default NULL,
580 `branchip` varchar(15) default NULL,
581 `branchprinter` varchar(100) default NULL,
582 `branchnotes` mediumtext,
583 UNIQUE KEY `branchcode` (`branchcode`)
584 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
587 -- Table structure for table `branchrelations`
590 DROP TABLE IF EXISTS `branchrelations`;
591 CREATE TABLE `branchrelations` (
592 `branchcode` varchar(10) NOT NULL default '',
593 `categorycode` varchar(10) NOT NULL default '',
594 PRIMARY KEY (`branchcode`,`categorycode`),
595 KEY `branchcode` (`branchcode`),
596 KEY `categorycode` (`categorycode`),
597 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
598 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
599 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
602 -- Table structure for table `branchtransfers`
605 DROP TABLE IF EXISTS `branchtransfers`;
606 CREATE TABLE `branchtransfers` (
607 `itemnumber` int(11) NOT NULL default 0,
608 `datesent` datetime default NULL,
609 `frombranch` varchar(10) NOT NULL default '',
610 `datearrived` datetime default NULL,
611 `tobranch` varchar(10) NOT NULL default '',
612 `comments` mediumtext,
613 KEY `frombranch` (`frombranch`),
614 KEY `tobranch` (`tobranch`),
615 KEY `itemnumber` (`itemnumber`),
616 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
617 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
618 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
619 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
623 -- Table structure for table `browser`
625 DROP TABLE IF EXISTS `browser`;
626 CREATE TABLE `browser` (
627 `level` int(11) NOT NULL,
628 `classification` varchar(20) NOT NULL,
629 `description` varchar(255) NOT NULL,
630 `number` bigint(20) NOT NULL,
631 `endnode` tinyint(4) NOT NULL
632 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
635 -- Table structure for table `categories`
638 DROP TABLE IF EXISTS `categories`;
639 CREATE TABLE `categories` (
640 `categorycode` varchar(10) NOT NULL default '',
641 `description` mediumtext,
642 `enrolmentperiod` smallint(6) default NULL,
643 `upperagelimit` smallint(6) default NULL,
644 `dateofbirthrequired` tinyint(1) default NULL,
645 `finetype` varchar(30) default NULL,
646 `bulk` tinyint(1) default NULL,
647 `enrolmentfee` decimal(28,6) default NULL,
648 `overduenoticerequired` tinyint(1) default NULL,
649 `issuelimit` smallint(6) default NULL,
650 `reservefee` decimal(28,6) default NULL,
651 `category_type` varchar(1) NOT NULL default 'A',
652 PRIMARY KEY (`categorycode`),
653 UNIQUE KEY `categorycode` (`categorycode`)
654 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
657 -- Table structure for table `borrower_branch_circ_rules`
660 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
661 CREATE TABLE `branch_borrower_circ_rules` (
662 `branchcode` VARCHAR(10) NOT NULL,
663 `categorycode` VARCHAR(10) NOT NULL,
664 `maxissueqty` int(4) default NULL,
665 PRIMARY KEY (`categorycode`, `branchcode`),
666 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
667 ON DELETE CASCADE ON UPDATE CASCADE,
668 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
669 ON DELETE CASCADE ON UPDATE CASCADE
670 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
673 -- Table structure for table `default_borrower_circ_rules`
676 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
677 CREATE TABLE `default_borrower_circ_rules` (
678 `categorycode` VARCHAR(10) NOT NULL,
679 `maxissueqty` int(4) default NULL,
680 PRIMARY KEY (`categorycode`),
681 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
682 ON DELETE CASCADE ON UPDATE CASCADE
683 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
686 -- Table structure for table `default_branch_circ_rules`
689 DROP TABLE IF EXISTS `default_branch_circ_rules`;
690 CREATE TABLE `default_branch_circ_rules` (
691 `branchcode` VARCHAR(10) NOT NULL,
692 `maxissueqty` int(4) default NULL,
693 `holdallowed` tinyint(1) default NULL,
694 PRIMARY KEY (`branchcode`),
695 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
696 ON DELETE CASCADE ON UPDATE CASCADE
697 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
700 -- Table structure for table `default_branch_item_rules`
703 CREATE TABLE `default_branch_item_rules` (
704 `itemtype` varchar(10) NOT NULL,
705 `holdallowed` tinyint(1) default NULL,
706 PRIMARY KEY (`itemtype`),
707 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
708 ON DELETE CASCADE ON UPDATE CASCADE
709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
712 -- Table structure for table `default_circ_rules`
715 DROP TABLE IF EXISTS `default_circ_rules`;
716 CREATE TABLE `default_circ_rules` (
717 `singleton` enum('singleton') NOT NULL default 'singleton',
718 `maxissueqty` int(4) default NULL,
719 `holdallowed` int(1) default NULL,
720 PRIMARY KEY (`singleton`)
721 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
724 -- Table structure for table `cities`
727 DROP TABLE IF EXISTS `cities`;
728 CREATE TABLE `cities` (
729 `cityid` int(11) NOT NULL auto_increment,
730 `city_name` varchar(100) NOT NULL default '',
731 `city_zipcode` varchar(20) default NULL,
732 PRIMARY KEY (`cityid`)
733 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
736 -- Table structure for table `class_sort_rules`
739 DROP TABLE IF EXISTS `class_sort_rules`;
740 CREATE TABLE `class_sort_rules` (
741 `class_sort_rule` varchar(10) NOT NULL default '',
742 `description` mediumtext,
743 `sort_routine` varchar(30) NOT NULL default '',
744 PRIMARY KEY (`class_sort_rule`),
745 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
746 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
749 -- Table structure for table `class_sources`
752 DROP TABLE IF EXISTS `class_sources`;
753 CREATE TABLE `class_sources` (
754 `cn_source` varchar(10) NOT NULL default '',
755 `description` mediumtext,
756 `used` tinyint(4) NOT NULL default 0,
757 `class_sort_rule` varchar(10) NOT NULL default '',
758 PRIMARY KEY (`cn_source`),
759 UNIQUE KEY `cn_source_idx` (`cn_source`),
760 KEY `used_idx` (`used`),
761 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
762 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
765 -- Table structure for table `currency`
768 DROP TABLE IF EXISTS `currency`;
769 CREATE TABLE `currency` (
770 `currency` varchar(10) NOT NULL default '',
771 `symbol` varchar(5) default NULL,
772 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
773 `rate` float(7,5) default NULL,
774 PRIMARY KEY (`currency`)
775 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
778 -- Table structure for table `deletedbiblio`
781 DROP TABLE IF EXISTS `deletedbiblio`;
782 CREATE TABLE `deletedbiblio` (
783 `biblionumber` int(11) NOT NULL default 0,
784 `frameworkcode` varchar(4) NOT NULL default '',
787 `unititle` mediumtext,
789 `serial` tinyint(1) default NULL,
790 `seriestitle` mediumtext,
791 `copyrightdate` smallint(6) default NULL,
792 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
793 `datecreated` DATE NOT NULL,
794 `abstract` mediumtext,
795 PRIMARY KEY (`biblionumber`),
796 KEY `blbnoidx` (`biblionumber`)
797 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
800 -- Table structure for table `deletedbiblioitems`
803 DROP TABLE IF EXISTS `deletedbiblioitems`;
804 CREATE TABLE `deletedbiblioitems` (
805 `biblioitemnumber` int(11) NOT NULL default 0,
806 `biblionumber` int(11) NOT NULL default 0,
809 `itemtype` varchar(10) default NULL,
810 `isbn` varchar(30) default NULL,
811 `issn` varchar(9) default NULL,
812 `publicationyear` text,
813 `publishercode` varchar(255) default NULL,
814 `volumedate` date default NULL,
816 `collectiontitle` mediumtext default NULL,
817 `collectionissn` text default NULL,
818 `collectionvolume` mediumtext default NULL,
819 `editionstatement` text default NULL,
820 `editionresponsibility` text default NULL,
821 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
822 `illus` varchar(255) default NULL,
823 `pages` varchar(255) default NULL,
825 `size` varchar(255) default NULL,
826 `place` varchar(255) default NULL,
827 `lccn` varchar(25) default NULL,
829 `url` varchar(255) default NULL,
830 `cn_source` varchar(10) default NULL,
831 `cn_class` varchar(30) default NULL,
832 `cn_item` varchar(10) default NULL,
833 `cn_suffix` varchar(10) default NULL,
834 `cn_sort` varchar(30) default NULL,
835 `totalissues` int(10),
836 `marcxml` longtext NOT NULL,
837 PRIMARY KEY (`biblioitemnumber`),
838 KEY `bibinoidx` (`biblioitemnumber`),
839 KEY `bibnoidx` (`biblionumber`),
841 KEY `publishercode` (`publishercode`)
842 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
845 -- Table structure for table `deletedborrowers`
848 DROP TABLE IF EXISTS `deletedborrowers`;
849 CREATE TABLE `deletedborrowers` (
850 `borrowernumber` int(11) NOT NULL default 0,
851 `cardnumber` varchar(9) NOT NULL default '',
852 `surname` mediumtext NOT NULL,
855 `othernames` mediumtext,
857 `streetnumber` varchar(10) default NULL,
858 `streettype` varchar(50) default NULL,
859 `address` mediumtext NOT NULL,
861 `city` mediumtext NOT NULL,
862 `zipcode` varchar(25) default NULL,
865 `mobile` varchar(50) default NULL,
869 `B_streetnumber` varchar(10) default NULL,
870 `B_streettype` varchar(50) default NULL,
871 `B_address` varchar(100) default NULL,
873 `B_zipcode` varchar(25) default NULL,
875 `B_phone` mediumtext,
876 `dateofbirth` date default NULL,
877 `branchcode` varchar(10) NOT NULL default '',
878 `categorycode` varchar(10) default NULL,
879 `dateenrolled` date default NULL,
880 `dateexpiry` date default NULL,
881 `gonenoaddress` tinyint(1) default NULL,
882 `lost` tinyint(1) default NULL,
883 `debarred` tinyint(1) default NULL,
884 `contactname` mediumtext,
885 `contactfirstname` text,
887 `guarantorid` int(11) default NULL,
888 `borrowernotes` mediumtext,
889 `relationship` varchar(100) default NULL,
890 `ethnicity` varchar(50) default NULL,
891 `ethnotes` varchar(255) default NULL,
892 `sex` varchar(1) default NULL,
893 `password` varchar(30) default NULL,
894 `flags` int(11) default NULL,
895 `userid` varchar(30) default NULL,
896 `opacnote` mediumtext,
897 `contactnote` varchar(255) default NULL,
898 `sort1` varchar(80) default NULL,
899 `sort2` varchar(80) default NULL,
900 `altcontactfirstname` varchar(255) default NULL,
901 `altcontactsurname` varchar(255) default NULL,
902 `altcontactaddress1` varchar(255) default NULL,
903 `altcontactaddress2` varchar(255) default NULL,
904 `altcontactaddress3` varchar(255) default NULL,
905 `altcontactzipcode` varchar(50) default NULL,
906 `altcontactphone` varchar(50) default NULL,
907 `smsalertnumber` varchar(50) default NULL,
908 KEY `borrowernumber` (`borrowernumber`),
909 KEY `cardnumber` (`cardnumber`)
910 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
913 -- Table structure for table `deleteditems`
916 DROP TABLE IF EXISTS `deleteditems`;
917 CREATE TABLE `deleteditems` (
918 `itemnumber` int(11) NOT NULL default 0,
919 `biblionumber` int(11) NOT NULL default 0,
920 `biblioitemnumber` int(11) NOT NULL default 0,
921 `barcode` varchar(20) default NULL,
922 `dateaccessioned` date default NULL,
923 `booksellerid` mediumtext default NULL,
924 `homebranch` varchar(10) default NULL,
925 `price` decimal(8,2) default NULL,
926 `replacementprice` decimal(8,2) default NULL,
927 `replacementpricedate` date default NULL,
928 `datelastborrowed` date default NULL,
929 `datelastseen` date default NULL,
930 `stack` tinyint(1) default NULL,
931 `notforloan` tinyint(1) NOT NULL default 0,
932 `damaged` tinyint(1) NOT NULL default 0,
933 `itemlost` tinyint(1) NOT NULL default 0,
934 `wthdrawn` tinyint(1) NOT NULL default 0,
935 `itemcallnumber` varchar(30) default NULL,
936 `issues` smallint(6) default NULL,
937 `renewals` smallint(6) default NULL,
938 `reserves` smallint(6) default NULL,
939 `restricted` tinyint(1) default NULL,
940 `itemnotes` mediumtext,
941 `holdingbranch` varchar(10) default NULL,
942 `paidfor` mediumtext,
943 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
944 `location` varchar(80) default NULL,
945 `onloan` date default NULL,
946 `cn_source` varchar(10) default NULL,
947 `cn_sort` varchar(30) default NULL,
948 `ccode` varchar(10) default NULL,
949 `materials` varchar(10) default NULL,
950 `uri` varchar(255) default NULL,
951 `itype` varchar(10) default NULL,
952 `more_subfields_xml` longtext default NULL,
953 `enumchron` varchar(80) default NULL,
954 `copynumber` varchar(32) default NULL,
956 PRIMARY KEY (`itemnumber`),
957 KEY `delitembarcodeidx` (`barcode`),
958 KEY `delitembinoidx` (`biblioitemnumber`),
959 KEY `delitembibnoidx` (`biblionumber`),
960 KEY `delhomebranch` (`homebranch`),
961 KEY `delholdingbranch` (`holdingbranch`)
962 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
965 -- Table structure for table `ethnicity`
968 DROP TABLE IF EXISTS `ethnicity`;
969 CREATE TABLE `ethnicity` (
970 `code` varchar(10) NOT NULL default '',
971 `name` varchar(255) default NULL,
973 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
976 -- Table structure for table `hold_fill_targets`
979 DROP TABLE IF EXISTS `hold_fill_targets`;
980 CREATE TABLE hold_fill_targets (
981 `borrowernumber` int(11) NOT NULL,
982 `biblionumber` int(11) NOT NULL,
983 `itemnumber` int(11) NOT NULL,
984 `source_branchcode` varchar(10) default NULL,
985 `item_level_request` tinyint(4) NOT NULL default 0,
986 PRIMARY KEY `itemnumber` (`itemnumber`),
987 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
988 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
989 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
990 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
991 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
992 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
993 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
994 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
995 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
996 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
999 -- Table structure for table `import_batches`
1002 DROP TABLE IF EXISTS `import_batches`;
1003 CREATE TABLE `import_batches` (
1004 `import_batch_id` int(11) NOT NULL auto_increment,
1005 `matcher_id` int(11) default NULL,
1006 `template_id` int(11) default NULL,
1007 `branchcode` varchar(10) default NULL,
1008 `num_biblios` int(11) NOT NULL default 0,
1009 `num_items` int(11) NOT NULL default 0,
1010 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1011 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1012 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1013 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1014 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1015 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1016 `file_name` varchar(100),
1017 `comments` mediumtext,
1018 PRIMARY KEY (`import_batch_id`),
1019 KEY `branchcode` (`branchcode`)
1020 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1023 -- Table structure for table `import_records`
1026 DROP TABLE IF EXISTS `import_records`;
1027 CREATE TABLE `import_records` (
1028 `import_record_id` int(11) NOT NULL auto_increment,
1029 `import_batch_id` int(11) NOT NULL,
1030 `branchcode` varchar(10) default NULL,
1031 `record_sequence` int(11) NOT NULL default 0,
1032 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1033 `import_date` DATE default NULL,
1034 `marc` longblob NOT NULL,
1035 `marcxml` longtext NOT NULL,
1036 `marcxml_old` longtext NOT NULL,
1037 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1038 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1039 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1040 `import_error` mediumtext,
1041 `encoding` varchar(40) NOT NULL default '',
1042 `z3950random` varchar(40) default NULL,
1043 PRIMARY KEY (`import_record_id`),
1044 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1045 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1046 KEY `branchcode` (`branchcode`),
1047 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1048 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1051 -- Table structure for `import_record_matches`
1053 DROP TABLE IF EXISTS `import_record_matches`;
1054 CREATE TABLE `import_record_matches` (
1055 `import_record_id` int(11) NOT NULL,
1056 `candidate_match_id` int(11) NOT NULL,
1057 `score` int(11) NOT NULL default 0,
1058 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1059 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1060 KEY `record_score` (`import_record_id`, `score`)
1061 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1064 -- Table structure for table `import_biblios`
1067 DROP TABLE IF EXISTS `import_biblios`;
1068 CREATE TABLE `import_biblios` (
1069 `import_record_id` int(11) NOT NULL,
1070 `matched_biblionumber` int(11) default NULL,
1071 `control_number` varchar(25) default NULL,
1072 `original_source` varchar(25) default NULL,
1073 `title` varchar(128) default NULL,
1074 `author` varchar(80) default NULL,
1075 `isbn` varchar(30) default NULL,
1076 `issn` varchar(9) default NULL,
1077 `has_items` tinyint(1) NOT NULL default 0,
1078 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1079 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1080 KEY `matched_biblionumber` (`matched_biblionumber`),
1081 KEY `title` (`title`),
1083 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1086 -- Table structure for table `import_items`
1089 DROP TABLE IF EXISTS `import_items`;
1090 CREATE TABLE `import_items` (
1091 `import_items_id` int(11) NOT NULL auto_increment,
1092 `import_record_id` int(11) NOT NULL,
1093 `itemnumber` int(11) default NULL,
1094 `branchcode` varchar(10) default NULL,
1095 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1096 `marcxml` longtext NOT NULL,
1097 `import_error` mediumtext,
1098 PRIMARY KEY (`import_items_id`),
1099 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1100 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1101 KEY `itemnumber` (`itemnumber`),
1102 KEY `branchcode` (`branchcode`)
1103 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1106 -- Table structure for table `issues`
1109 DROP TABLE IF EXISTS `issues`;
1110 CREATE TABLE `issues` (
1111 `borrowernumber` int(11) default NULL,
1112 `itemnumber` int(11) default NULL,
1113 `date_due` date default NULL,
1114 `branchcode` varchar(10) default NULL,
1115 `issuingbranch` varchar(18) default NULL,
1116 `returndate` date default NULL,
1117 `lastreneweddate` date default NULL,
1118 `return` varchar(4) default NULL,
1119 `renewals` tinyint(4) default NULL,
1120 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1121 `issuedate` date default NULL,
1122 KEY `issuesborridx` (`borrowernumber`),
1123 KEY `issuesitemidx` (`itemnumber`),
1124 KEY `bordate` (`borrowernumber`,`timestamp`),
1125 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1126 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1127 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1130 -- Table structure for table `issuingrules`
1133 DROP TABLE IF EXISTS `issuingrules`;
1134 CREATE TABLE `issuingrules` (
1135 `categorycode` varchar(10) NOT NULL default '',
1136 `itemtype` varchar(10) NOT NULL default '',
1137 `restrictedtype` tinyint(1) default NULL,
1138 `rentaldiscount` decimal(28,6) default NULL,
1139 `reservecharge` decimal(28,6) default NULL,
1140 `fine` decimal(28,6) default NULL,
1141 `firstremind` int(11) default NULL,
1142 `chargeperiod` int(11) default NULL,
1143 `accountsent` int(11) default NULL,
1144 `chargename` varchar(100) default NULL,
1145 `maxissueqty` int(4) default NULL,
1146 `issuelength` int(4) default NULL,
1147 `branchcode` varchar(10) NOT NULL default '',
1148 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1149 KEY `categorycode` (`categorycode`),
1150 KEY `itemtype` (`itemtype`)
1151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1154 -- Table structure for table `items`
1157 DROP TABLE IF EXISTS `items`;
1158 CREATE TABLE `items` (
1159 `itemnumber` int(11) NOT NULL auto_increment,
1160 `biblionumber` int(11) NOT NULL default 0,
1161 `biblioitemnumber` int(11) NOT NULL default 0,
1162 `barcode` varchar(20) default NULL,
1163 `dateaccessioned` date default NULL,
1164 `booksellerid` mediumtext default NULL,
1165 `homebranch` varchar(10) default NULL,
1166 `price` decimal(8,2) default NULL,
1167 `replacementprice` decimal(8,2) default NULL,
1168 `replacementpricedate` date default NULL,
1169 `datelastborrowed` date default NULL,
1170 `datelastseen` date default NULL,
1171 `stack` tinyint(1) default NULL,
1172 `notforloan` tinyint(1) NOT NULL default 0,
1173 `damaged` tinyint(1) NOT NULL default 0,
1174 `itemlost` tinyint(1) NOT NULL default 0,
1175 `wthdrawn` tinyint(1) NOT NULL default 0,
1176 `itemcallnumber` varchar(30) default NULL,
1177 `issues` smallint(6) default NULL,
1178 `renewals` smallint(6) default NULL,
1179 `reserves` smallint(6) default NULL,
1180 `restricted` tinyint(1) default NULL,
1181 `itemnotes` mediumtext,
1182 `holdingbranch` varchar(10) default NULL,
1183 `paidfor` mediumtext,
1184 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1185 `location` varchar(80) default NULL,
1186 `onloan` date default NULL,
1187 `cn_source` varchar(10) default NULL,
1188 `cn_sort` varchar(30) default NULL,
1189 `ccode` varchar(10) default NULL,
1190 `materials` varchar(10) default NULL,
1191 `uri` varchar(255) default NULL,
1192 `itype` varchar(10) default NULL,
1193 `more_subfields_xml` longtext default NULL,
1194 `enumchron` varchar(80) default NULL,
1195 `copynumber` varchar(32) default NULL,
1196 PRIMARY KEY (`itemnumber`),
1197 UNIQUE KEY `itembarcodeidx` (`barcode`),
1198 KEY `itembinoidx` (`biblioitemnumber`),
1199 KEY `itembibnoidx` (`biblionumber`),
1200 KEY `homebranch` (`homebranch`),
1201 KEY `holdingbranch` (`holdingbranch`),
1202 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1203 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1204 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1208 -- Table structure for table `itemtypes`
1211 DROP TABLE IF EXISTS `itemtypes`;
1212 CREATE TABLE `itemtypes` (
1213 `itemtype` varchar(10) NOT NULL default '',
1214 `description` mediumtext,
1215 `renewalsallowed` smallint(6) default NULL,
1216 `rentalcharge` double(16,4) default NULL,
1217 `notforloan` smallint(6) default NULL,
1218 `imageurl` varchar(200) default NULL,
1220 PRIMARY KEY (`itemtype`),
1221 UNIQUE KEY `itemtype` (`itemtype`)
1222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1225 -- Table structure for table `labels`
1228 DROP TABLE IF EXISTS `labels`;
1229 CREATE TABLE `labels` (
1230 `labelid` int(11) NOT NULL auto_increment,
1231 `batch_id` int(10) NOT NULL default 1,
1232 `itemnumber` varchar(100) NOT NULL default '',
1233 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1234 PRIMARY KEY (`labelid`)
1235 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1238 -- Table structure for table `labels_conf`
1241 DROP TABLE IF EXISTS `labels_conf`;
1242 CREATE TABLE `labels_conf` (
1243 `id` int(4) NOT NULL auto_increment,
1244 `barcodetype` char(100) default '',
1245 `title` int(1) default '0',
1246 `subtitle` int(1) default '0',
1247 `itemtype` int(1) default '0',
1248 `barcode` int(1) default '0',
1249 `dewey` int(1) default '0',
1250 `classification` int(1) default NULL,
1251 `subclass` int(1) default '0',
1252 `itemcallnumber` int(1) default '0',
1253 `author` int(1) default '0',
1254 `issn` int(1) default '0',
1255 `isbn` int(1) default '0',
1256 `startlabel` int(2) NOT NULL default '1',
1257 `printingtype` char(32) default 'BAR',
1258 `formatstring` mediumtext default NULL,
1259 `layoutname` char(20) NOT NULL default 'TEST',
1260 `guidebox` int(1) default '0',
1261 `active` tinyint(1) default '1',
1262 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1263 `ccode` char(4) collate utf8_unicode_ci default NULL,
1264 `callnum_split` int(1) default NULL,
1265 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1267 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1270 -- Table structure for table `labels_profile`
1273 DROP TABLE IF EXISTS `labels_profile`;
1274 CREATE TABLE `labels_profile` (
1275 `tmpl_id` int(4) NOT NULL,
1276 `prof_id` int(4) NOT NULL,
1277 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1278 UNIQUE KEY `prof_id` (`prof_id`)
1279 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1282 -- Table structure for table `labels_templates`
1285 DROP TABLE IF EXISTS `labels_templates`;
1286 CREATE TABLE `labels_templates` (
1287 `tmpl_id` int(4) NOT NULL auto_increment,
1288 `tmpl_code` char(100) default '',
1289 `tmpl_desc` char(100) default '',
1290 `page_width` float default '0',
1291 `page_height` float default '0',
1292 `label_width` float default '0',
1293 `label_height` float default '0',
1294 `topmargin` float default '0',
1295 `leftmargin` float default '0',
1296 `cols` int(2) default '0',
1297 `rows` int(2) default '0',
1298 `colgap` float default '0',
1299 `rowgap` float default '0',
1300 `active` int(1) default NULL,
1301 `units` char(20) default 'PX',
1302 `fontsize` int(4) NOT NULL default '3',
1303 `font` char(10) NOT NULL default 'TR',
1304 PRIMARY KEY (`tmpl_id`)
1305 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1308 -- Table structure for table `letter`
1311 DROP TABLE IF EXISTS `letter`;
1312 CREATE TABLE `letter` (
1313 `module` varchar(20) NOT NULL default '',
1314 `code` varchar(20) NOT NULL default '',
1315 `name` varchar(100) NOT NULL default '',
1316 `title` varchar(200) NOT NULL default '',
1318 PRIMARY KEY (`module`,`code`)
1319 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1322 -- Table structure for table `marc_subfield_structure`
1325 DROP TABLE IF EXISTS `marc_subfield_structure`;
1326 CREATE TABLE `marc_subfield_structure` (
1327 `tagfield` varchar(3) NOT NULL default '',
1328 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1329 `liblibrarian` varchar(255) NOT NULL default '',
1330 `libopac` varchar(255) NOT NULL default '',
1331 `repeatable` tinyint(4) NOT NULL default 0,
1332 `mandatory` tinyint(4) NOT NULL default 0,
1333 `kohafield` varchar(40) default NULL,
1334 `tab` tinyint(1) default NULL,
1335 `authorised_value` varchar(20) default NULL,
1336 `authtypecode` varchar(20) default NULL,
1337 `value_builder` varchar(80) default NULL,
1338 `isurl` tinyint(1) default NULL,
1339 `hidden` tinyint(1) default NULL,
1340 `frameworkcode` varchar(4) NOT NULL default '',
1341 `seealso` varchar(1100) default NULL,
1342 `link` varchar(80) default NULL,
1343 `defaultvalue` text default NULL,
1344 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1345 KEY `kohafield_2` (`kohafield`),
1346 KEY `tab` (`frameworkcode`,`tab`),
1347 KEY `kohafield` (`frameworkcode`,`kohafield`)
1348 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1351 -- Table structure for table `marc_tag_structure`
1354 DROP TABLE IF EXISTS `marc_tag_structure`;
1355 CREATE TABLE `marc_tag_structure` (
1356 `tagfield` varchar(3) NOT NULL default '',
1357 `liblibrarian` varchar(255) NOT NULL default '',
1358 `libopac` varchar(255) NOT NULL default '',
1359 `repeatable` tinyint(4) NOT NULL default 0,
1360 `mandatory` tinyint(4) NOT NULL default 0,
1361 `authorised_value` varchar(10) default NULL,
1362 `frameworkcode` varchar(4) NOT NULL default '',
1363 PRIMARY KEY (`frameworkcode`,`tagfield`)
1364 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1367 -- Table structure for table `marc_matchers`
1370 DROP TABLE IF EXISTS `marc_matchers`;
1371 CREATE TABLE `marc_matchers` (
1372 `matcher_id` int(11) NOT NULL auto_increment,
1373 `code` varchar(10) NOT NULL default '',
1374 `description` varchar(255) NOT NULL default '',
1375 `record_type` varchar(10) NOT NULL default 'biblio',
1376 `threshold` int(11) NOT NULL default 0,
1377 PRIMARY KEY (`matcher_id`),
1378 KEY `code` (`code`),
1379 KEY `record_type` (`record_type`)
1380 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1383 -- Table structure for table `matchpoints`
1385 DROP TABLE IF EXISTS `matchpoints`;
1386 CREATE TABLE `matchpoints` (
1387 `matcher_id` int(11) NOT NULL,
1388 `matchpoint_id` int(11) NOT NULL auto_increment,
1389 `search_index` varchar(30) NOT NULL default '',
1390 `score` int(11) NOT NULL default 0,
1391 PRIMARY KEY (`matchpoint_id`),
1392 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1393 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1394 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1398 -- Table structure for table `matchpoint_components`
1400 DROP TABLE IF EXISTS `matchpoint_components`;
1401 CREATE TABLE `matchpoint_components` (
1402 `matchpoint_id` int(11) NOT NULL,
1403 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1404 sequence int(11) NOT NULL default 0,
1405 tag varchar(3) NOT NULL default '',
1406 subfields varchar(40) NOT NULL default '',
1407 offset int(4) NOT NULL default 0,
1408 length int(4) NOT NULL default 0,
1409 PRIMARY KEY (`matchpoint_component_id`),
1410 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1411 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1412 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1413 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1416 -- Table structure for table `matcher_component_norms`
1418 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1419 CREATE TABLE `matchpoint_component_norms` (
1420 `matchpoint_component_id` int(11) NOT NULL,
1421 `sequence` int(11) NOT NULL default 0,
1422 `norm_routine` varchar(50) NOT NULL default '',
1423 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1424 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1425 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1429 -- Table structure for table `matcher_matchpoints`
1431 DROP TABLE IF EXISTS `matcher_matchpoints`;
1432 CREATE TABLE `matcher_matchpoints` (
1433 `matcher_id` int(11) NOT NULL,
1434 `matchpoint_id` int(11) NOT NULL,
1435 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1436 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1437 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1438 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1439 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1442 -- Table structure for table `matchchecks`
1444 DROP TABLE IF EXISTS `matchchecks`;
1445 CREATE TABLE `matchchecks` (
1446 `matcher_id` int(11) NOT NULL,
1447 `matchcheck_id` int(11) NOT NULL auto_increment,
1448 `source_matchpoint_id` int(11) NOT NULL,
1449 `target_matchpoint_id` int(11) NOT NULL,
1450 PRIMARY KEY (`matchcheck_id`),
1451 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1452 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1453 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1454 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1455 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1456 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1457 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1460 -- Table structure for table `notifys`
1463 DROP TABLE IF EXISTS `notifys`;
1464 CREATE TABLE `notifys` (
1465 `notify_id` int(11) NOT NULL default 0,
1466 `borrowernumber` int(11) NOT NULL default 0,
1467 `itemnumber` int(11) NOT NULL default 0,
1468 `notify_date` date default NULL,
1469 `notify_send_date` date default NULL,
1470 `notify_level` int(1) NOT NULL default 0,
1471 `method` varchar(20) NOT NULL default ''
1472 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1475 -- Table structure for table `nozebra`
1478 DROP TABLE IF EXISTS `nozebra`;
1479 CREATE TABLE `nozebra` (
1480 `server` varchar(20) NOT NULL,
1481 `indexname` varchar(40) NOT NULL,
1482 `value` varchar(250) NOT NULL,
1483 `biblionumbers` longtext NOT NULL,
1484 KEY `indexname` (`server`,`indexname`),
1485 KEY `value` (`server`,`value`))
1486 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1489 -- Table structure for table `old_issues`
1492 DROP TABLE IF EXISTS `old_issues`;
1493 CREATE TABLE `old_issues` (
1494 `borrowernumber` int(11) default NULL,
1495 `itemnumber` int(11) default NULL,
1496 `date_due` date default NULL,
1497 `branchcode` varchar(10) default NULL,
1498 `issuingbranch` varchar(18) default NULL,
1499 `returndate` date default NULL,
1500 `lastreneweddate` date default NULL,
1501 `return` varchar(4) default NULL,
1502 `renewals` tinyint(4) default NULL,
1503 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1504 `issuedate` date default NULL,
1505 KEY `old_issuesborridx` (`borrowernumber`),
1506 KEY `old_issuesitemidx` (`itemnumber`),
1507 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1508 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1509 ON DELETE SET NULL ON UPDATE SET NULL,
1510 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1511 ON DELETE SET NULL ON UPDATE SET NULL
1512 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1515 -- Table structure for table `old_reserves`
1517 DROP TABLE IF EXISTS `old_reserves`;
1518 CREATE TABLE `old_reserves` (
1519 `borrowernumber` int(11) default NULL,
1520 `reservedate` date default NULL,
1521 `biblionumber` int(11) default NULL,
1522 `constrainttype` varchar(1) default NULL,
1523 `branchcode` varchar(10) default NULL,
1524 `notificationdate` date default NULL,
1525 `reminderdate` date default NULL,
1526 `cancellationdate` date default NULL,
1527 `reservenotes` mediumtext,
1528 `priority` smallint(6) default NULL,
1529 `found` varchar(1) default NULL,
1530 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1531 `itemnumber` int(11) default NULL,
1532 `waitingdate` date default NULL,
1533 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1534 KEY `old_reserves_biblionumber` (`biblionumber`),
1535 KEY `old_reserves_itemnumber` (`itemnumber`),
1536 KEY `old_reserves_branchcode` (`branchcode`),
1537 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1538 ON DELETE SET NULL ON UPDATE SET NULL,
1539 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1540 ON DELETE SET NULL ON UPDATE SET NULL,
1541 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1542 ON DELETE SET NULL ON UPDATE SET NULL
1543 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1546 -- Table structure for table `opac_news`
1549 DROP TABLE IF EXISTS `opac_news`;
1550 CREATE TABLE `opac_news` (
1551 `idnew` int(10) unsigned NOT NULL auto_increment,
1552 `title` varchar(250) NOT NULL default '',
1553 `new` text NOT NULL,
1554 `lang` varchar(25) NOT NULL default '',
1555 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1556 `expirationdate` date default NULL,
1557 `number` int(11) default NULL,
1558 PRIMARY KEY (`idnew`)
1559 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1562 -- Table structure for table `overduerules`
1565 DROP TABLE IF EXISTS `overduerules`;
1566 CREATE TABLE `overduerules` (
1567 `branchcode` varchar(10) NOT NULL default '',
1568 `categorycode` varchar(10) NOT NULL default '',
1569 `delay1` int(4) default 0,
1570 `letter1` varchar(20) default NULL,
1571 `debarred1` varchar(1) default 0,
1572 `delay2` int(4) default 0,
1573 `debarred2` varchar(1) default 0,
1574 `letter2` varchar(20) default NULL,
1575 `delay3` int(4) default 0,
1576 `letter3` varchar(20) default NULL,
1577 `debarred3` int(1) default 0,
1578 PRIMARY KEY (`branchcode`,`categorycode`)
1579 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1582 -- Table structure for table `patroncards`
1585 DROP TABLE IF EXISTS `patroncards`;
1586 CREATE TABLE `patroncards` (
1587 `cardid` int(11) NOT NULL auto_increment,
1588 `batch_id` varchar(10) NOT NULL default '1',
1589 `borrowernumber` int(11) NOT NULL,
1590 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1591 PRIMARY KEY (`cardid`),
1592 KEY `patroncards_ibfk_1` (`borrowernumber`),
1593 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1594 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1597 -- Table structure for table `patronimage`
1600 DROP TABLE IF EXISTS `patronimage`;
1601 CREATE TABLE `patronimage` (
1602 `cardnumber` varchar(16) NOT NULL,
1603 `mimetype` varchar(15) NOT NULL,
1604 `imagefile` mediumblob NOT NULL,
1605 PRIMARY KEY (`cardnumber`),
1606 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1607 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1610 -- Table structure for table `printers`
1613 DROP TABLE IF EXISTS `printers`;
1614 CREATE TABLE `printers` (
1615 `printername` varchar(40) NOT NULL default '',
1616 `printqueue` varchar(20) default NULL,
1617 `printtype` varchar(20) default NULL,
1618 PRIMARY KEY (`printername`)
1619 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1622 -- Table structure for table `printers_profile`
1625 DROP TABLE IF EXISTS `printers_profile`;
1626 CREATE TABLE `printers_profile` (
1627 `prof_id` int(4) NOT NULL auto_increment,
1628 `printername` varchar(40) NOT NULL,
1629 `tmpl_id` int(4) NOT NULL,
1630 `paper_bin` varchar(20) NOT NULL,
1631 `offset_horz` float default NULL,
1632 `offset_vert` float default NULL,
1633 `creep_horz` float default NULL,
1634 `creep_vert` float default NULL,
1635 `unit` char(20) NOT NULL default 'POINT',
1636 PRIMARY KEY (`prof_id`),
1637 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1638 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1639 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1642 -- Table structure for table `repeatable_holidays`
1645 DROP TABLE IF EXISTS `repeatable_holidays`;
1646 CREATE TABLE `repeatable_holidays` (
1647 `id` int(11) NOT NULL auto_increment,
1648 `branchcode` varchar(10) NOT NULL default '',
1649 `weekday` smallint(6) default NULL,
1650 `day` smallint(6) default NULL,
1651 `month` smallint(6) default NULL,
1652 `title` varchar(50) NOT NULL default '',
1653 `description` text NOT NULL,
1655 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1658 -- Table structure for table `reports_dictionary`
1661 DROP TABLE IF EXISTS `reports_dictionary`;
1662 CREATE TABLE reports_dictionary (
1663 `id` int(11) NOT NULL auto_increment,
1664 `name` varchar(255) default NULL,
1666 `date_created` datetime default NULL,
1667 `date_modified` datetime default NULL,
1669 `area` int(11) default NULL,
1671 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1674 -- Table structure for table `reserveconstraints`
1677 DROP TABLE IF EXISTS `reserveconstraints`;
1678 CREATE TABLE `reserveconstraints` (
1679 `borrowernumber` int(11) NOT NULL default 0,
1680 `reservedate` date default NULL,
1681 `biblionumber` int(11) NOT NULL default 0,
1682 `biblioitemnumber` int(11) default NULL,
1683 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1684 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1687 -- Table structure for table `reserves`
1690 DROP TABLE IF EXISTS `reserves`;
1691 CREATE TABLE `reserves` (
1692 `borrowernumber` int(11) NOT NULL default 0,
1693 `reservedate` date default NULL,
1694 `biblionumber` int(11) NOT NULL default 0,
1695 `constrainttype` varchar(1) default NULL,
1696 `branchcode` varchar(10) default NULL,
1697 `notificationdate` date default NULL,
1698 `reminderdate` date default NULL,
1699 `cancellationdate` date default NULL,
1700 `reservenotes` mediumtext,
1701 `priority` smallint(6) default NULL,
1702 `found` varchar(1) default NULL,
1703 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1704 `itemnumber` int(11) default NULL,
1705 `waitingdate` date default NULL,
1706 KEY `borrowernumber` (`borrowernumber`),
1707 KEY `biblionumber` (`biblionumber`),
1708 KEY `itemnumber` (`itemnumber`),
1709 KEY `branchcode` (`branchcode`),
1710 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1711 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1712 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1713 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1714 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1717 -- Table structure for table `reviews`
1720 DROP TABLE IF EXISTS `reviews`;
1721 CREATE TABLE `reviews` (
1722 `reviewid` int(11) NOT NULL auto_increment,
1723 `borrowernumber` int(11) default NULL,
1724 `biblionumber` int(11) default NULL,
1726 `approved` tinyint(4) default NULL,
1727 `datereviewed` datetime default NULL,
1728 PRIMARY KEY (`reviewid`)
1729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1732 -- Table structure for table `roadtype`
1735 DROP TABLE IF EXISTS `roadtype`;
1736 CREATE TABLE `roadtype` (
1737 `roadtypeid` int(11) NOT NULL auto_increment,
1738 `road_type` varchar(100) NOT NULL default '',
1739 PRIMARY KEY (`roadtypeid`)
1740 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1743 -- Table structure for table `saved_sql`
1746 DROP TABLE IF EXISTS `saved_sql`;
1747 CREATE TABLE saved_sql (
1748 `id` int(11) NOT NULL auto_increment,
1749 `borrowernumber` int(11) default NULL,
1750 `date_created` datetime default NULL,
1751 `last_modified` datetime default NULL,
1753 `last_run` datetime default NULL,
1754 `report_name` varchar(255) default NULL,
1755 `type` varchar(255) default NULL,
1758 KEY boridx (`borrowernumber`)
1759 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1763 -- Table structure for `saved_reports`
1766 DROP TABLE IF EXISTS `saved_reports`;
1767 CREATE TABLE saved_reports (
1768 `id` int(11) NOT NULL auto_increment,
1769 `report_id` int(11) default NULL,
1771 `date_run` datetime default NULL,
1773 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1777 -- Table structure for table `serial`
1780 DROP TABLE IF EXISTS `serial`;
1781 CREATE TABLE `serial` (
1782 `serialid` int(11) NOT NULL auto_increment,
1783 `biblionumber` varchar(100) NOT NULL default '',
1784 `subscriptionid` varchar(100) NOT NULL default '',
1785 `serialseq` varchar(100) NOT NULL default '',
1786 `status` tinyint(4) NOT NULL default 0,
1787 `planneddate` date default NULL,
1789 `publisheddate` date default NULL,
1790 `itemnumber` text default NULL,
1791 `claimdate` date default NULL,
1792 `routingnotes` text,
1793 PRIMARY KEY (`serialid`)
1794 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1797 -- Table structure for table `sessions`
1800 DROP TABLE IF EXISTS sessions;
1801 CREATE TABLE sessions (
1802 `id` varchar(32) NOT NULL,
1803 `a_session` text NOT NULL,
1805 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1808 -- Table structure for table `special_holidays`
1811 DROP TABLE IF EXISTS `special_holidays`;
1812 CREATE TABLE `special_holidays` (
1813 `id` int(11) NOT NULL auto_increment,
1814 `branchcode` varchar(10) NOT NULL default '',
1815 `day` smallint(6) NOT NULL default 0,
1816 `month` smallint(6) NOT NULL default 0,
1817 `year` smallint(6) NOT NULL default 0,
1818 `isexception` smallint(1) NOT NULL default 1,
1819 `title` varchar(50) NOT NULL default '',
1820 `description` text NOT NULL,
1822 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1825 -- Table structure for table `statistics`
1828 DROP TABLE IF EXISTS `statistics`;
1829 CREATE TABLE `statistics` (
1830 `datetime` datetime default NULL,
1831 `branch` varchar(10) default NULL,
1832 `proccode` varchar(4) default NULL,
1833 `value` double(16,4) default NULL,
1834 `type` varchar(16) default NULL,
1836 `usercode` varchar(10) default NULL,
1837 `itemnumber` int(11) default NULL,
1838 `itemtype` varchar(10) default NULL,
1839 `borrowernumber` int(11) default NULL,
1840 `associatedborrower` int(11) default NULL,
1841 KEY `timeidx` (`datetime`)
1842 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1845 -- Table structure for table `stopwords`
1848 DROP TABLE IF EXISTS `stopwords`;
1849 CREATE TABLE `stopwords` (
1850 `word` varchar(255) default NULL
1851 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1854 -- Table structure for table `subscription`
1857 DROP TABLE IF EXISTS `subscription`;
1858 CREATE TABLE `subscription` (
1859 `biblionumber` int(11) NOT NULL default 0,
1860 `subscriptionid` int(11) NOT NULL auto_increment,
1861 `librarian` varchar(100) default '',
1862 `startdate` date default NULL,
1863 `aqbooksellerid` int(11) default 0,
1864 `cost` int(11) default 0,
1865 `aqbudgetid` int(11) default 0,
1866 `weeklength` int(11) default 0,
1867 `monthlength` int(11) default 0,
1868 `numberlength` int(11) default 0,
1869 `periodicity` tinyint(4) default 0,
1870 `dow` varchar(100) default '',
1871 `numberingmethod` varchar(100) default '',
1873 `status` varchar(100) NOT NULL default '',
1874 `add1` int(11) default 0,
1875 `every1` int(11) default 0,
1876 `whenmorethan1` int(11) default 0,
1877 `setto1` int(11) default NULL,
1878 `lastvalue1` int(11) default NULL,
1879 `add2` int(11) default 0,
1880 `every2` int(11) default 0,
1881 `whenmorethan2` int(11) default 0,
1882 `setto2` int(11) default NULL,
1883 `lastvalue2` int(11) default NULL,
1884 `add3` int(11) default 0,
1885 `every3` int(11) default 0,
1886 `innerloop1` int(11) default 0,
1887 `innerloop2` int(11) default 0,
1888 `innerloop3` int(11) default 0,
1889 `whenmorethan3` int(11) default 0,
1890 `setto3` int(11) default NULL,
1891 `lastvalue3` int(11) default NULL,
1892 `issuesatonce` tinyint(3) NOT NULL default 1,
1893 `firstacquidate` date default NULL,
1894 `manualhistory` tinyint(1) NOT NULL default 0,
1895 `irregularity` text,
1896 `letter` varchar(20) default NULL,
1897 `numberpattern` tinyint(3) default 0,
1898 `distributedto` text,
1899 `internalnotes` longtext,
1901 `location` varchar(80) NULL default '',
1902 `branchcode` varchar(10) NOT NULL default '',
1903 `hemisphere` tinyint(3) default 0,
1904 `lastbranch` varchar(10),
1905 `serialsadditems` tinyint(1) NOT NULL default '0',
1906 `staffdisplaycount` VARCHAR(10) NULL,
1907 `opacdisplaycount` VARCHAR(10) NULL,
1908 `graceperiod` int(11) NOT NULL default '0',
1909 PRIMARY KEY (`subscriptionid`)
1910 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1913 -- Table structure for table `subscriptionhistory`
1916 DROP TABLE IF EXISTS `subscriptionhistory`;
1917 CREATE TABLE `subscriptionhistory` (
1918 `biblionumber` int(11) NOT NULL default 0,
1919 `subscriptionid` int(11) NOT NULL default 0,
1920 `histstartdate` date default NULL,
1921 `enddate` date default NULL,
1922 `missinglist` longtext NOT NULL,
1923 `recievedlist` longtext NOT NULL,
1924 `opacnote` varchar(150) NOT NULL default '',
1925 `librariannote` varchar(150) NOT NULL default '',
1926 PRIMARY KEY (`subscriptionid`),
1927 KEY `biblionumber` (`biblionumber`)
1928 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1931 -- Table structure for table `subscriptionroutinglist`
1934 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1935 CREATE TABLE `subscriptionroutinglist` (
1936 `routingid` int(11) NOT NULL auto_increment,
1937 `borrowernumber` int(11) default NULL,
1938 `ranking` int(11) default NULL,
1939 `subscriptionid` int(11) default NULL,
1940 PRIMARY KEY (`routingid`)
1941 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1944 -- Table structure for table `suggestions`
1947 DROP TABLE IF EXISTS `suggestions`;
1948 CREATE TABLE `suggestions` (
1949 `suggestionid` int(8) NOT NULL auto_increment,
1950 `suggestedby` int(11) NOT NULL default 0,
1951 `managedby` int(11) default NULL,
1952 `STATUS` varchar(10) NOT NULL default '',
1954 `author` varchar(80) default NULL,
1955 `title` varchar(80) default NULL,
1956 `copyrightdate` smallint(6) default NULL,
1957 `publishercode` varchar(255) default NULL,
1958 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1959 `volumedesc` varchar(255) default NULL,
1960 `publicationyear` smallint(6) default 0,
1961 `place` varchar(255) default NULL,
1962 `isbn` varchar(30) default NULL,
1963 `mailoverseeing` smallint(1) default 0,
1964 `biblionumber` int(11) default NULL,
1966 PRIMARY KEY (`suggestionid`),
1967 KEY `suggestedby` (`suggestedby`),
1968 KEY `managedby` (`managedby`)
1969 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1972 -- Table structure for table `systempreferences`
1975 DROP TABLE IF EXISTS `systempreferences`;
1976 CREATE TABLE `systempreferences` (
1977 `variable` varchar(50) NOT NULL default '',
1979 `options` mediumtext,
1981 `type` varchar(20) default NULL,
1982 PRIMARY KEY (`variable`)
1983 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1986 -- Table structure for table `tags`
1989 DROP TABLE IF EXISTS `tags`;
1990 CREATE TABLE `tags` (
1991 `entry` varchar(255) NOT NULL default '',
1992 `weight` bigint(20) NOT NULL default 0,
1993 PRIMARY KEY (`entry`)
1994 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1997 -- Table structure for table `tags_all`
2000 DROP TABLE IF EXISTS `tags_all`;
2001 CREATE TABLE `tags_all` (
2002 `tag_id` int(11) NOT NULL auto_increment,
2003 `borrowernumber` int(11) NOT NULL,
2004 `biblionumber` int(11) NOT NULL,
2005 `term` varchar(255) NOT NULL,
2006 `language` int(4) default NULL,
2007 `date_created` datetime NOT NULL,
2008 PRIMARY KEY (`tag_id`),
2009 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2010 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2011 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2012 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2013 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2014 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2015 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2018 -- Table structure for table `tags_approval`
2021 DROP TABLE IF EXISTS `tags_approval`;
2022 CREATE TABLE `tags_approval` (
2023 `term` varchar(255) NOT NULL,
2024 `approved` int(1) NOT NULL default '0',
2025 `date_approved` datetime default NULL,
2026 `approved_by` int(11) default NULL,
2027 `weight_total` int(9) NOT NULL default '1',
2028 PRIMARY KEY (`term`),
2029 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2030 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2031 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2032 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2035 -- Table structure for table `tags_index`
2038 DROP TABLE IF EXISTS `tags_index`;
2039 CREATE TABLE `tags_index` (
2040 `term` varchar(255) NOT NULL,
2041 `biblionumber` int(11) NOT NULL,
2042 `weight` int(9) NOT NULL default '1',
2043 PRIMARY KEY (`term`,`biblionumber`),
2044 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2045 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2046 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2047 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2048 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2049 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2052 -- Table structure for table `userflags`
2055 DROP TABLE IF EXISTS `userflags`;
2056 CREATE TABLE `userflags` (
2057 `bit` int(11) NOT NULL default 0,
2058 `flag` varchar(30) default NULL,
2059 `flagdesc` varchar(255) default NULL,
2060 `defaulton` int(11) default NULL,
2062 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2065 -- Table structure for table `virtualshelves`
2068 DROP TABLE IF EXISTS `virtualshelves`;
2069 CREATE TABLE `virtualshelves` (
2070 `shelfnumber` int(11) NOT NULL auto_increment,
2071 `shelfname` varchar(255) default NULL,
2072 `owner` varchar(80) default NULL,
2073 `category` varchar(1) default NULL,
2074 `sortfield` varchar(16) default NULL,
2075 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2076 PRIMARY KEY (`shelfnumber`)
2077 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2080 -- Table structure for table `virtualshelfcontents`
2083 DROP TABLE IF EXISTS `virtualshelfcontents`;
2084 CREATE TABLE `virtualshelfcontents` (
2085 `shelfnumber` int(11) NOT NULL default 0,
2086 `biblionumber` int(11) NOT NULL default 0,
2087 `flags` int(11) default NULL,
2088 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2089 KEY `shelfnumber` (`shelfnumber`),
2090 KEY `biblionumber` (`biblionumber`),
2091 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2092 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2093 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2096 -- Table structure for table `z3950servers`
2099 DROP TABLE IF EXISTS `z3950servers`;
2100 CREATE TABLE `z3950servers` (
2101 `host` varchar(255) default NULL,
2102 `port` int(11) default NULL,
2103 `db` varchar(255) default NULL,
2104 `userid` varchar(255) default NULL,
2105 `password` varchar(255) default NULL,
2107 `id` int(11) NOT NULL auto_increment,
2108 `checked` smallint(6) default NULL,
2109 `rank` int(11) default NULL,
2110 `syntax` varchar(80) default NULL,
2112 `position` enum('primary','secondary','') NOT NULL default 'primary',
2113 `type` enum('zed','opensearch') NOT NULL default 'zed',
2114 `encoding` text default NULL,
2115 `description` text NOT NULL,
2117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2120 -- Table structure for table `zebraqueue`
2123 DROP TABLE IF EXISTS `zebraqueue`;
2124 CREATE TABLE `zebraqueue` (
2125 `id` int(11) NOT NULL auto_increment,
2126 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2127 `operation` char(20) NOT NULL default '',
2128 `server` char(20) NOT NULL default '',
2129 `done` int(11) NOT NULL default '0',
2130 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2132 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2133 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2135 DROP TABLE IF EXISTS `services_throttle`;
2136 CREATE TABLE `services_throttle` (
2137 `service_type` varchar(10) NOT NULL default '',
2138 `service_count` varchar(45) default NULL,
2139 PRIMARY KEY (`service_type`)
2140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2142 -- http://www.w3.org/International/articles/language-tags/
2145 DROP TABLE IF EXISTS language_subtag_registry;
2146 CREATE TABLE language_subtag_registry (
2148 type varchar(25), -- language-script-region-variant-extension-privateuse
2149 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2151 KEY `subtag` (`subtag`)
2152 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2154 -- TODO: add suppress_scripts
2155 -- this maps three letter codes defined in iso639.2 back to their
2156 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2157 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2158 CREATE TABLE language_rfc4646_to_iso639 (
2159 rfc4646_subtag varchar(25),
2160 iso639_2_code varchar(25),
2161 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2162 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2164 DROP TABLE IF EXISTS language_descriptions;
2165 CREATE TABLE language_descriptions (
2169 description varchar(255),
2171 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2173 -- bi-directional support, keyed by script subcode
2174 DROP TABLE IF EXISTS language_script_bidi;
2175 CREATE TABLE language_script_bidi (
2176 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2177 bidi varchar(3), -- rtl ltr
2178 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2179 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2181 -- TODO: need to map language subtags to script subtags for detection
2182 -- of bidi when script is not specified (like ar, he)
2183 DROP TABLE IF EXISTS language_script_mapping;
2184 CREATE TABLE language_script_mapping (
2185 language_subtag varchar(25),
2186 script_subtag varchar(25),
2187 KEY `language_subtag` (`language_subtag`)
2188 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2190 DROP TABLE IF EXISTS `permissions`;
2191 CREATE TABLE `permissions` (
2192 `module_bit` int(11) NOT NULL DEFAULT 0,
2193 `code` varchar(64) DEFAULT NULL,
2194 `description` varchar(255) DEFAULT NULL,
2195 PRIMARY KEY (`module_bit`, `code`),
2196 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2197 ON DELETE CASCADE ON UPDATE CASCADE
2198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2200 DROP TABLE IF EXISTS `serialitems`;
2201 CREATE TABLE `serialitems` (
2202 `itemnumber` int(11) NOT NULL,
2203 `serialid` int(11) NOT NULL,
2204 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2205 KEY `serialitems_sfk_1` (`serialid`),
2206 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2209 DROP TABLE IF EXISTS `user_permissions`;
2210 CREATE TABLE `user_permissions` (
2211 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2212 `module_bit` int(11) NOT NULL DEFAULT 0,
2213 `code` varchar(64) DEFAULT NULL,
2214 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2215 ON DELETE CASCADE ON UPDATE CASCADE,
2216 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2217 ON DELETE CASCADE ON UPDATE CASCADE
2218 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2221 -- Table structure for table `tmp_holdsqueue`
2224 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2225 CREATE TABLE `tmp_holdsqueue` (
2226 `biblionumber` int(11) default NULL,
2227 `itemnumber` int(11) default NULL,
2228 `barcode` varchar(20) default NULL,
2229 `surname` mediumtext NOT NULL,
2232 `borrowernumber` int(11) NOT NULL,
2233 `cardnumber` varchar(16) default NULL,
2234 `reservedate` date default NULL,
2236 `itemcallnumber` varchar(30) default NULL,
2237 `holdingbranch` varchar(10) default NULL,
2238 `pickbranch` varchar(10) default NULL,
2240 `item_level_request` tinyint(4) NOT NULL default 0
2241 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2244 -- Table structure for table `message_queue`
2247 DROP TABLE IF EXISTS `message_queue`;
2248 CREATE TABLE `message_queue` (
2249 `message_id` int(11) NOT NULL auto_increment,
2250 `borrowernumber` int(11) default NULL,
2253 `metadata` text DEFAULT NULL,
2254 `letter_code` varchar(64) DEFAULT NULL,
2255 `message_transport_type` varchar(20) NOT NULL,
2256 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2257 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2258 `to_address` mediumtext,
2259 `from_address` mediumtext,
2260 `content_type` text,
2261 KEY `message_id` (`message_id`),
2262 KEY `borrowernumber` (`borrowernumber`),
2263 KEY `message_transport_type` (`message_transport_type`),
2264 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2265 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2269 -- Table structure for table `message_transport_types`
2272 DROP TABLE IF EXISTS `message_transport_types`;
2273 CREATE TABLE `message_transport_types` (
2274 `message_transport_type` varchar(20) NOT NULL,
2275 PRIMARY KEY (`message_transport_type`)
2276 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2279 -- Table structure for table `message_attributes`
2282 DROP TABLE IF EXISTS `message_attributes`;
2283 CREATE TABLE `message_attributes` (
2284 `message_attribute_id` int(11) NOT NULL auto_increment,
2285 `message_name` varchar(20) NOT NULL default '',
2286 `takes_days` tinyint(1) NOT NULL default '0',
2287 PRIMARY KEY (`message_attribute_id`),
2288 UNIQUE KEY `message_name` (`message_name`)
2289 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2292 -- Table structure for table `message_transports`
2295 DROP TABLE IF EXISTS `message_transports`;
2296 CREATE TABLE `message_transports` (
2297 `message_attribute_id` int(11) NOT NULL,
2298 `message_transport_type` varchar(20) NOT NULL,
2299 `is_digest` tinyint(1) NOT NULL default '0',
2300 `letter_module` varchar(20) NOT NULL default '',
2301 `letter_code` varchar(20) NOT NULL default '',
2302 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2303 KEY `message_transport_type` (`message_transport_type`),
2304 KEY `letter_module` (`letter_module`,`letter_code`),
2305 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2306 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2307 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2308 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2311 -- Table structure for table `borrower_message_preferences`
2314 DROP TABLE IF EXISTS `borrower_message_preferences`;
2315 CREATE TABLE `borrower_message_preferences` (
2316 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2317 `borrowernumber` int(11) default NULL,
2318 `categorycode` varchar(10) default NULL,
2319 `message_attribute_id` int(11) default '0',
2320 `days_in_advance` int(11) default '0',
2321 `wants_digest` tinyint(1) NOT NULL default '0',
2322 PRIMARY KEY (`borrower_message_preference_id`),
2323 KEY `borrowernumber` (`borrowernumber`),
2324 KEY `categorycode` (`categorycode`),
2325 KEY `message_attribute_id` (`message_attribute_id`),
2326 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2327 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2328 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2332 -- Table structure for table `borrower_message_transport_preferences`
2335 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2336 CREATE TABLE `borrower_message_transport_preferences` (
2337 `borrower_message_preference_id` int(11) NOT NULL default '0',
2338 `message_transport_type` varchar(20) NOT NULL default '0',
2339 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2340 KEY `message_transport_type` (`message_transport_type`),
2341 CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_message_preference_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2342 CONSTRAINT `borrower_message_transport_preferences_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
2343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2346 -- Table structure for the table branch_transfer_limits
2349 DROP TABLE IF EXISTS `branch_transfer_limits`;
2350 CREATE TABLE branch_transfer_limits (
2351 limitId int(8) NOT NULL auto_increment,
2352 toBranch varchar(10) NOT NULL,
2353 fromBranch varchar(10) NOT NULL,
2354 itemtype varchar(10) NULL,
2355 ccode varchar(10) NULL,
2356 PRIMARY KEY (limitId)
2357 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2360 -- Table structure for table `item_circulation_alert_preferences`
2363 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2364 CREATE TABLE `item_circulation_alert_preferences` (
2365 `id` int(11) NOT NULL auto_increment,
2366 `branchcode` varchar(10) NOT NULL,
2367 `categorycode` varchar(10) NOT NULL,
2368 `item_type` varchar(10) NOT NULL,
2369 `notification` varchar(16) NOT NULL,
2371 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2372 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2374 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2375 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2376 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2377 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2378 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2379 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2380 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2381 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;