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 `branchphone` mediumtext,
573 `branchfax` mediumtext,
574 `branchemail` mediumtext,
575 `issuing` tinyint(4) default NULL,
576 `branchip` varchar(15) default NULL,
577 `branchprinter` varchar(100) default NULL,
578 UNIQUE KEY `branchcode` (`branchcode`)
579 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
582 -- Table structure for table `branchrelations`
585 DROP TABLE IF EXISTS `branchrelations`;
586 CREATE TABLE `branchrelations` (
587 `branchcode` varchar(10) NOT NULL default '',
588 `categorycode` varchar(10) NOT NULL default '',
589 PRIMARY KEY (`branchcode`,`categorycode`),
590 KEY `branchcode` (`branchcode`),
591 KEY `categorycode` (`categorycode`),
592 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
593 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
594 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
597 -- Table structure for table `branchtransfers`
600 DROP TABLE IF EXISTS `branchtransfers`;
601 CREATE TABLE `branchtransfers` (
602 `itemnumber` int(11) NOT NULL default 0,
603 `datesent` datetime default NULL,
604 `frombranch` varchar(10) NOT NULL default '',
605 `datearrived` datetime default NULL,
606 `tobranch` varchar(10) NOT NULL default '',
607 `comments` mediumtext,
608 KEY `frombranch` (`frombranch`),
609 KEY `tobranch` (`tobranch`),
610 KEY `itemnumber` (`itemnumber`),
611 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
612 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
613 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
618 -- Table structure for table `browser`
620 DROP TABLE IF EXISTS `browser`;
621 CREATE TABLE `browser` (
622 `level` int(11) NOT NULL,
623 `classification` varchar(20) NOT NULL,
624 `description` varchar(255) NOT NULL,
625 `number` bigint(20) NOT NULL,
626 `endnode` tinyint(4) NOT NULL
627 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
630 -- Table structure for table `categories`
633 DROP TABLE IF EXISTS `categories`;
634 CREATE TABLE `categories` (
635 `categorycode` varchar(10) NOT NULL default '',
636 `description` mediumtext,
637 `enrolmentperiod` smallint(6) default NULL,
638 `upperagelimit` smallint(6) default NULL,
639 `dateofbirthrequired` tinyint(1) default NULL,
640 `finetype` varchar(30) default NULL,
641 `bulk` tinyint(1) default NULL,
642 `enrolmentfee` decimal(28,6) default NULL,
643 `overduenoticerequired` tinyint(1) default NULL,
644 `issuelimit` smallint(6) default NULL,
645 `reservefee` decimal(28,6) default NULL,
646 `category_type` varchar(1) NOT NULL default 'A',
647 PRIMARY KEY (`categorycode`),
648 UNIQUE KEY `categorycode` (`categorycode`)
649 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
652 -- Table structure for table `borrower_branch_circ_rules`
655 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
656 CREATE TABLE `branch_borrower_circ_rules` (
657 `branchcode` VARCHAR(10) NOT NULL,
658 `categorycode` VARCHAR(10) NOT NULL,
659 `maxissueqty` int(4) default NULL,
660 PRIMARY KEY (`categorycode`, `branchcode`),
661 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
662 ON DELETE CASCADE ON UPDATE CASCADE,
663 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
664 ON DELETE CASCADE ON UPDATE CASCADE
665 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
668 -- Table structure for table `default_borrower_circ_rules`
671 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
672 CREATE TABLE `default_borrower_circ_rules` (
673 `categorycode` VARCHAR(10) NOT NULL,
674 `maxissueqty` int(4) default NULL,
675 PRIMARY KEY (`categorycode`),
676 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
677 ON DELETE CASCADE ON UPDATE CASCADE
678 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
681 -- Table structure for table `default_branch_circ_rules`
684 DROP TABLE IF EXISTS `default_branch_circ_rules`;
685 CREATE TABLE `default_branch_circ_rules` (
686 `branchcode` VARCHAR(10) NOT NULL,
687 `maxissueqty` int(4) default NULL,
688 `holdallowed` tinyint(1) default NULL,
689 PRIMARY KEY (`branchcode`),
690 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
691 ON DELETE CASCADE ON UPDATE CASCADE
692 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
695 -- Table structure for table `default_branch_item_rules`
698 CREATE TABLE `default_branch_item_rules` (
699 `itemtype` varchar(10) NOT NULL,
700 `holdallowed` tinyint(1) default NULL,
701 PRIMARY KEY (`itemtype`),
702 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
703 ON DELETE CASCADE ON UPDATE CASCADE
704 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
707 -- Table structure for table `default_circ_rules`
710 DROP TABLE IF EXISTS `default_circ_rules`;
711 CREATE TABLE `default_circ_rules` (
712 `singleton` enum('singleton') NOT NULL default 'singleton',
713 `maxissueqty` int(4) default NULL,
714 `holdallowed` int(1) default NULL,
715 PRIMARY KEY (`singleton`)
716 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
719 -- Table structure for table `cities`
722 DROP TABLE IF EXISTS `cities`;
723 CREATE TABLE `cities` (
724 `cityid` int(11) NOT NULL auto_increment,
725 `city_name` varchar(100) NOT NULL default '',
726 `city_zipcode` varchar(20) default NULL,
727 PRIMARY KEY (`cityid`)
728 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
731 -- Table structure for table `class_sort_rules`
734 DROP TABLE IF EXISTS `class_sort_rules`;
735 CREATE TABLE `class_sort_rules` (
736 `class_sort_rule` varchar(10) NOT NULL default '',
737 `description` mediumtext,
738 `sort_routine` varchar(30) NOT NULL default '',
739 PRIMARY KEY (`class_sort_rule`),
740 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
741 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
744 -- Table structure for table `class_sources`
747 DROP TABLE IF EXISTS `class_sources`;
748 CREATE TABLE `class_sources` (
749 `cn_source` varchar(10) NOT NULL default '',
750 `description` mediumtext,
751 `used` tinyint(4) NOT NULL default 0,
752 `class_sort_rule` varchar(10) NOT NULL default '',
753 PRIMARY KEY (`cn_source`),
754 UNIQUE KEY `cn_source_idx` (`cn_source`),
755 KEY `used_idx` (`used`),
756 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
757 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
760 -- Table structure for table `currency`
763 DROP TABLE IF EXISTS `currency`;
764 CREATE TABLE `currency` (
765 `currency` varchar(10) NOT NULL default '',
766 `symbol` varchar(5) default NULL,
767 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
768 `rate` float(7,5) default NULL,
769 PRIMARY KEY (`currency`)
770 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
773 -- Table structure for table `deletedbiblio`
776 DROP TABLE IF EXISTS `deletedbiblio`;
777 CREATE TABLE `deletedbiblio` (
778 `biblionumber` int(11) NOT NULL default 0,
779 `frameworkcode` varchar(4) NOT NULL default '',
782 `unititle` mediumtext,
784 `serial` tinyint(1) default NULL,
785 `seriestitle` mediumtext,
786 `copyrightdate` smallint(6) default NULL,
787 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
788 `datecreated` DATE NOT NULL,
789 `abstract` mediumtext,
790 PRIMARY KEY (`biblionumber`),
791 KEY `blbnoidx` (`biblionumber`)
792 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
795 -- Table structure for table `deletedbiblioitems`
798 DROP TABLE IF EXISTS `deletedbiblioitems`;
799 CREATE TABLE `deletedbiblioitems` (
800 `biblioitemnumber` int(11) NOT NULL default 0,
801 `biblionumber` int(11) NOT NULL default 0,
804 `itemtype` varchar(10) default NULL,
805 `isbn` varchar(30) default NULL,
806 `issn` varchar(9) default NULL,
807 `publicationyear` text,
808 `publishercode` varchar(255) default NULL,
809 `volumedate` date default NULL,
811 `collectiontitle` mediumtext default NULL,
812 `collectionissn` text default NULL,
813 `collectionvolume` mediumtext default NULL,
814 `editionstatement` text default NULL,
815 `editionresponsibility` text default NULL,
816 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
817 `illus` varchar(255) default NULL,
818 `pages` varchar(255) default NULL,
820 `size` varchar(255) default NULL,
821 `place` varchar(255) default NULL,
822 `lccn` varchar(25) default NULL,
824 `url` varchar(255) default NULL,
825 `cn_source` varchar(10) default NULL,
826 `cn_class` varchar(30) default NULL,
827 `cn_item` varchar(10) default NULL,
828 `cn_suffix` varchar(10) default NULL,
829 `cn_sort` varchar(30) default NULL,
830 `totalissues` int(10),
831 `marcxml` longtext NOT NULL,
832 PRIMARY KEY (`biblioitemnumber`),
833 KEY `bibinoidx` (`biblioitemnumber`),
834 KEY `bibnoidx` (`biblionumber`),
836 KEY `publishercode` (`publishercode`)
837 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
840 -- Table structure for table `deletedborrowers`
843 DROP TABLE IF EXISTS `deletedborrowers`;
844 CREATE TABLE `deletedborrowers` (
845 `borrowernumber` int(11) NOT NULL default 0,
846 `cardnumber` varchar(9) NOT NULL default '',
847 `surname` mediumtext NOT NULL,
850 `othernames` mediumtext,
852 `streetnumber` varchar(10) default NULL,
853 `streettype` varchar(50) default NULL,
854 `address` mediumtext NOT NULL,
856 `city` mediumtext NOT NULL,
857 `zipcode` varchar(25) default NULL,
860 `mobile` varchar(50) default NULL,
864 `B_streetnumber` varchar(10) default NULL,
865 `B_streettype` varchar(50) default NULL,
866 `B_address` varchar(100) default NULL,
868 `B_zipcode` varchar(25) default NULL,
870 `B_phone` mediumtext,
871 `dateofbirth` date default NULL,
872 `branchcode` varchar(10) NOT NULL default '',
873 `categorycode` varchar(10) default NULL,
874 `dateenrolled` date default NULL,
875 `dateexpiry` date default NULL,
876 `gonenoaddress` tinyint(1) default NULL,
877 `lost` tinyint(1) default NULL,
878 `debarred` tinyint(1) default NULL,
879 `contactname` mediumtext,
880 `contactfirstname` text,
882 `guarantorid` int(11) default NULL,
883 `borrowernotes` mediumtext,
884 `relationship` varchar(100) default NULL,
885 `ethnicity` varchar(50) default NULL,
886 `ethnotes` varchar(255) default NULL,
887 `sex` varchar(1) default NULL,
888 `password` varchar(30) default NULL,
889 `flags` int(11) default NULL,
890 `userid` varchar(30) default NULL,
891 `opacnote` mediumtext,
892 `contactnote` varchar(255) default NULL,
893 `sort1` varchar(80) default NULL,
894 `sort2` varchar(80) default NULL,
895 `altcontactfirstname` varchar(255) default NULL,
896 `altcontactsurname` varchar(255) default NULL,
897 `altcontactaddress1` varchar(255) default NULL,
898 `altcontactaddress2` varchar(255) default NULL,
899 `altcontactaddress3` varchar(255) default NULL,
900 `altcontactzipcode` varchar(50) default NULL,
901 `altcontactphone` varchar(50) default NULL,
902 `smsalertnumber` varchar(50) default NULL,
903 KEY `borrowernumber` (`borrowernumber`),
904 KEY `cardnumber` (`cardnumber`)
905 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
908 -- Table structure for table `deleteditems`
911 DROP TABLE IF EXISTS `deleteditems`;
912 CREATE TABLE `deleteditems` (
913 `itemnumber` int(11) NOT NULL default 0,
914 `biblionumber` int(11) NOT NULL default 0,
915 `biblioitemnumber` int(11) NOT NULL default 0,
916 `barcode` varchar(20) default NULL,
917 `dateaccessioned` date default NULL,
918 `booksellerid` mediumtext default NULL,
919 `homebranch` varchar(10) default NULL,
920 `price` decimal(8,2) default NULL,
921 `replacementprice` decimal(8,2) default NULL,
922 `replacementpricedate` date default NULL,
923 `datelastborrowed` date default NULL,
924 `datelastseen` date default NULL,
925 `stack` tinyint(1) default NULL,
926 `notforloan` tinyint(1) NOT NULL default 0,
927 `damaged` tinyint(1) NOT NULL default 0,
928 `itemlost` tinyint(1) NOT NULL default 0,
929 `wthdrawn` tinyint(1) NOT NULL default 0,
930 `itemcallnumber` varchar(30) default NULL,
931 `issues` smallint(6) default NULL,
932 `renewals` smallint(6) default NULL,
933 `reserves` smallint(6) default NULL,
934 `restricted` tinyint(1) default NULL,
935 `itemnotes` mediumtext,
936 `holdingbranch` varchar(10) default NULL,
937 `paidfor` mediumtext,
938 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
939 `location` varchar(80) default NULL,
940 `onloan` date default NULL,
941 `cn_source` varchar(10) default NULL,
942 `cn_sort` varchar(30) default NULL,
943 `ccode` varchar(10) default NULL,
944 `materials` varchar(10) default NULL,
945 `uri` varchar(255) default NULL,
946 `itype` varchar(10) default NULL,
947 `more_subfields_xml` longtext default NULL,
948 `enumchron` varchar(80) default NULL,
949 `copynumber` varchar(32) default NULL,
951 PRIMARY KEY (`itemnumber`),
952 KEY `delitembarcodeidx` (`barcode`),
953 KEY `delitembinoidx` (`biblioitemnumber`),
954 KEY `delitembibnoidx` (`biblionumber`),
955 KEY `delhomebranch` (`homebranch`),
956 KEY `delholdingbranch` (`holdingbranch`)
957 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
960 -- Table structure for table `ethnicity`
963 DROP TABLE IF EXISTS `ethnicity`;
964 CREATE TABLE `ethnicity` (
965 `code` varchar(10) NOT NULL default '',
966 `name` varchar(255) default NULL,
968 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
971 -- Table structure for table `hold_fill_targets`
974 DROP TABLE IF EXISTS `hold_fill_targets`;
975 CREATE TABLE hold_fill_targets (
976 `borrowernumber` int(11) NOT NULL,
977 `biblionumber` int(11) NOT NULL,
978 `itemnumber` int(11) NOT NULL,
979 `source_branchcode` varchar(10) default NULL,
980 `item_level_request` tinyint(4) NOT NULL default 0,
981 PRIMARY KEY `itemnumber` (`itemnumber`),
982 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
983 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
984 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
985 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
986 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
987 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
988 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
989 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
990 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
991 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
994 -- Table structure for table `import_batches`
997 DROP TABLE IF EXISTS `import_batches`;
998 CREATE TABLE `import_batches` (
999 `import_batch_id` int(11) NOT NULL auto_increment,
1000 `matcher_id` int(11) default NULL,
1001 `template_id` int(11) default NULL,
1002 `branchcode` varchar(10) default NULL,
1003 `num_biblios` int(11) NOT NULL default 0,
1004 `num_items` int(11) NOT NULL default 0,
1005 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1006 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1007 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1008 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1009 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1010 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1011 `file_name` varchar(100),
1012 `comments` mediumtext,
1013 PRIMARY KEY (`import_batch_id`),
1014 KEY `branchcode` (`branchcode`)
1015 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1018 -- Table structure for table `import_records`
1021 DROP TABLE IF EXISTS `import_records`;
1022 CREATE TABLE `import_records` (
1023 `import_record_id` int(11) NOT NULL auto_increment,
1024 `import_batch_id` int(11) NOT NULL,
1025 `branchcode` varchar(10) default NULL,
1026 `record_sequence` int(11) NOT NULL default 0,
1027 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1028 `import_date` DATE default NULL,
1029 `marc` longblob NOT NULL,
1030 `marcxml` longtext NOT NULL,
1031 `marcxml_old` longtext NOT NULL,
1032 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1033 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1034 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1035 `import_error` mediumtext,
1036 `encoding` varchar(40) NOT NULL default '',
1037 `z3950random` varchar(40) default NULL,
1038 PRIMARY KEY (`import_record_id`),
1039 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1040 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1041 KEY `branchcode` (`branchcode`),
1042 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1043 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1046 -- Table structure for `import_record_matches`
1048 DROP TABLE IF EXISTS `import_record_matches`;
1049 CREATE TABLE `import_record_matches` (
1050 `import_record_id` int(11) NOT NULL,
1051 `candidate_match_id` int(11) NOT NULL,
1052 `score` int(11) NOT NULL default 0,
1053 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1054 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1055 KEY `record_score` (`import_record_id`, `score`)
1056 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1059 -- Table structure for table `import_biblios`
1062 DROP TABLE IF EXISTS `import_biblios`;
1063 CREATE TABLE `import_biblios` (
1064 `import_record_id` int(11) NOT NULL,
1065 `matched_biblionumber` int(11) default NULL,
1066 `control_number` varchar(25) default NULL,
1067 `original_source` varchar(25) default NULL,
1068 `title` varchar(128) default NULL,
1069 `author` varchar(80) default NULL,
1070 `isbn` varchar(30) default NULL,
1071 `issn` varchar(9) default NULL,
1072 `has_items` tinyint(1) NOT NULL default 0,
1073 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1074 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1075 KEY `matched_biblionumber` (`matched_biblionumber`),
1076 KEY `title` (`title`),
1078 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1081 -- Table structure for table `import_items`
1084 DROP TABLE IF EXISTS `import_items`;
1085 CREATE TABLE `import_items` (
1086 `import_items_id` int(11) NOT NULL auto_increment,
1087 `import_record_id` int(11) NOT NULL,
1088 `itemnumber` int(11) default NULL,
1089 `branchcode` varchar(10) default NULL,
1090 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1091 `marcxml` longtext NOT NULL,
1092 `import_error` mediumtext,
1093 PRIMARY KEY (`import_items_id`),
1094 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1095 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1096 KEY `itemnumber` (`itemnumber`),
1097 KEY `branchcode` (`branchcode`)
1098 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1101 -- Table structure for table `issues`
1104 DROP TABLE IF EXISTS `issues`;
1105 CREATE TABLE `issues` (
1106 `borrowernumber` int(11) default NULL,
1107 `itemnumber` int(11) default NULL,
1108 `date_due` date default NULL,
1109 `branchcode` varchar(10) default NULL,
1110 `issuingbranch` varchar(18) default NULL,
1111 `returndate` date default NULL,
1112 `lastreneweddate` date default NULL,
1113 `return` varchar(4) default NULL,
1114 `renewals` tinyint(4) default NULL,
1115 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1116 `issuedate` date default NULL,
1117 KEY `issuesborridx` (`borrowernumber`),
1118 KEY `issuesitemidx` (`itemnumber`),
1119 KEY `bordate` (`borrowernumber`,`timestamp`),
1120 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1121 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1122 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1125 -- Table structure for table `issuingrules`
1128 DROP TABLE IF EXISTS `issuingrules`;
1129 CREATE TABLE `issuingrules` (
1130 `categorycode` varchar(10) NOT NULL default '',
1131 `itemtype` varchar(10) NOT NULL default '',
1132 `restrictedtype` tinyint(1) default NULL,
1133 `rentaldiscount` decimal(28,6) default NULL,
1134 `reservecharge` decimal(28,6) default NULL,
1135 `fine` decimal(28,6) default NULL,
1136 `firstremind` int(11) default NULL,
1137 `chargeperiod` int(11) default NULL,
1138 `accountsent` int(11) default NULL,
1139 `chargename` varchar(100) default NULL,
1140 `maxissueqty` int(4) default NULL,
1141 `issuelength` int(4) default NULL,
1142 `branchcode` varchar(10) NOT NULL default '',
1143 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1144 KEY `categorycode` (`categorycode`),
1145 KEY `itemtype` (`itemtype`)
1146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1149 -- Table structure for table `items`
1152 DROP TABLE IF EXISTS `items`;
1153 CREATE TABLE `items` (
1154 `itemnumber` int(11) NOT NULL auto_increment,
1155 `biblionumber` int(11) NOT NULL default 0,
1156 `biblioitemnumber` int(11) NOT NULL default 0,
1157 `barcode` varchar(20) default NULL,
1158 `dateaccessioned` date default NULL,
1159 `booksellerid` mediumtext default NULL,
1160 `homebranch` varchar(10) default NULL,
1161 `price` decimal(8,2) default NULL,
1162 `replacementprice` decimal(8,2) default NULL,
1163 `replacementpricedate` date default NULL,
1164 `datelastborrowed` date default NULL,
1165 `datelastseen` date default NULL,
1166 `stack` tinyint(1) default NULL,
1167 `notforloan` tinyint(1) NOT NULL default 0,
1168 `damaged` tinyint(1) NOT NULL default 0,
1169 `itemlost` tinyint(1) NOT NULL default 0,
1170 `wthdrawn` tinyint(1) NOT NULL default 0,
1171 `itemcallnumber` varchar(30) default NULL,
1172 `issues` smallint(6) default NULL,
1173 `renewals` smallint(6) default NULL,
1174 `reserves` smallint(6) default NULL,
1175 `restricted` tinyint(1) default NULL,
1176 `itemnotes` mediumtext,
1177 `holdingbranch` varchar(10) default NULL,
1178 `paidfor` mediumtext,
1179 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1180 `location` varchar(80) default NULL,
1181 `onloan` date default NULL,
1182 `cn_source` varchar(10) default NULL,
1183 `cn_sort` varchar(30) default NULL,
1184 `ccode` varchar(10) default NULL,
1185 `materials` varchar(10) default NULL,
1186 `uri` varchar(255) default NULL,
1187 `itype` varchar(10) default NULL,
1188 `more_subfields_xml` longtext default NULL,
1189 `enumchron` varchar(80) default NULL,
1190 `copynumber` varchar(32) default NULL,
1191 PRIMARY KEY (`itemnumber`),
1192 UNIQUE KEY `itembarcodeidx` (`barcode`),
1193 KEY `itembinoidx` (`biblioitemnumber`),
1194 KEY `itembibnoidx` (`biblionumber`),
1195 KEY `homebranch` (`homebranch`),
1196 KEY `holdingbranch` (`holdingbranch`),
1197 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1198 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1199 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1203 -- Table structure for table `itemtypes`
1206 DROP TABLE IF EXISTS `itemtypes`;
1207 CREATE TABLE `itemtypes` (
1208 `itemtype` varchar(10) NOT NULL default '',
1209 `description` mediumtext,
1210 `renewalsallowed` smallint(6) default NULL,
1211 `rentalcharge` double(16,4) default NULL,
1212 `notforloan` smallint(6) default NULL,
1213 `imageurl` varchar(200) default NULL,
1215 PRIMARY KEY (`itemtype`),
1216 UNIQUE KEY `itemtype` (`itemtype`)
1217 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1220 -- Table structure for table `labels`
1223 DROP TABLE IF EXISTS `labels`;
1224 CREATE TABLE `labels` (
1225 `labelid` int(11) NOT NULL auto_increment,
1226 `batch_id` int(10) NOT NULL default 1,
1227 `itemnumber` varchar(100) NOT NULL default '',
1228 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1229 PRIMARY KEY (`labelid`)
1230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1233 -- Table structure for table `labels_conf`
1236 DROP TABLE IF EXISTS `labels_conf`;
1237 CREATE TABLE `labels_conf` (
1238 `id` int(4) NOT NULL auto_increment,
1239 `barcodetype` char(100) default '',
1240 `title` int(1) default '0',
1241 `subtitle` int(1) default '0',
1242 `itemtype` int(1) default '0',
1243 `barcode` int(1) default '0',
1244 `dewey` int(1) default '0',
1245 `classification` int(1) default NULL,
1246 `subclass` int(1) default '0',
1247 `itemcallnumber` int(1) default '0',
1248 `author` int(1) default '0',
1249 `issn` int(1) default '0',
1250 `isbn` int(1) default '0',
1251 `startlabel` int(2) NOT NULL default '1',
1252 `printingtype` char(32) default 'BAR',
1253 `formatstring` mediumtext default NULL,
1254 `layoutname` char(20) NOT NULL default 'TEST',
1255 `guidebox` int(1) default '0',
1256 `active` tinyint(1) default '1',
1257 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1258 `ccode` char(4) collate utf8_unicode_ci default NULL,
1259 `callnum_split` int(1) default NULL,
1260 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1262 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1265 -- Table structure for table `labels_profile`
1268 DROP TABLE IF EXISTS `labels_profile`;
1269 CREATE TABLE `labels_profile` (
1270 `tmpl_id` int(4) NOT NULL,
1271 `prof_id` int(4) NOT NULL,
1272 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1273 UNIQUE KEY `prof_id` (`prof_id`)
1274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1277 -- Table structure for table `labels_templates`
1280 DROP TABLE IF EXISTS `labels_templates`;
1281 CREATE TABLE `labels_templates` (
1282 `tmpl_id` int(4) NOT NULL auto_increment,
1283 `tmpl_code` char(100) default '',
1284 `tmpl_desc` char(100) default '',
1285 `page_width` float default '0',
1286 `page_height` float default '0',
1287 `label_width` float default '0',
1288 `label_height` float default '0',
1289 `topmargin` float default '0',
1290 `leftmargin` float default '0',
1291 `cols` int(2) default '0',
1292 `rows` int(2) default '0',
1293 `colgap` float default '0',
1294 `rowgap` float default '0',
1295 `active` int(1) default NULL,
1296 `units` char(20) default 'PX',
1297 `fontsize` int(4) NOT NULL default '3',
1298 `font` char(10) NOT NULL default 'TR',
1299 PRIMARY KEY (`tmpl_id`)
1300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1303 -- Table structure for table `letter`
1306 DROP TABLE IF EXISTS `letter`;
1307 CREATE TABLE `letter` (
1308 `module` varchar(20) NOT NULL default '',
1309 `code` varchar(20) NOT NULL default '',
1310 `name` varchar(100) NOT NULL default '',
1311 `title` varchar(200) NOT NULL default '',
1313 PRIMARY KEY (`module`,`code`)
1314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1317 -- Table structure for table `marc_subfield_structure`
1320 DROP TABLE IF EXISTS `marc_subfield_structure`;
1321 CREATE TABLE `marc_subfield_structure` (
1322 `tagfield` varchar(3) NOT NULL default '',
1323 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1324 `liblibrarian` varchar(255) NOT NULL default '',
1325 `libopac` varchar(255) NOT NULL default '',
1326 `repeatable` tinyint(4) NOT NULL default 0,
1327 `mandatory` tinyint(4) NOT NULL default 0,
1328 `kohafield` varchar(40) default NULL,
1329 `tab` tinyint(1) default NULL,
1330 `authorised_value` varchar(20) default NULL,
1331 `authtypecode` varchar(20) default NULL,
1332 `value_builder` varchar(80) default NULL,
1333 `isurl` tinyint(1) default NULL,
1334 `hidden` tinyint(1) default NULL,
1335 `frameworkcode` varchar(4) NOT NULL default '',
1336 `seealso` varchar(1100) default NULL,
1337 `link` varchar(80) default NULL,
1338 `defaultvalue` text default NULL,
1339 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1340 KEY `kohafield_2` (`kohafield`),
1341 KEY `tab` (`frameworkcode`,`tab`),
1342 KEY `kohafield` (`frameworkcode`,`kohafield`)
1343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1346 -- Table structure for table `marc_tag_structure`
1349 DROP TABLE IF EXISTS `marc_tag_structure`;
1350 CREATE TABLE `marc_tag_structure` (
1351 `tagfield` varchar(3) NOT NULL default '',
1352 `liblibrarian` varchar(255) NOT NULL default '',
1353 `libopac` varchar(255) NOT NULL default '',
1354 `repeatable` tinyint(4) NOT NULL default 0,
1355 `mandatory` tinyint(4) NOT NULL default 0,
1356 `authorised_value` varchar(10) default NULL,
1357 `frameworkcode` varchar(4) NOT NULL default '',
1358 PRIMARY KEY (`frameworkcode`,`tagfield`)
1359 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1362 -- Table structure for table `marc_matchers`
1365 DROP TABLE IF EXISTS `marc_matchers`;
1366 CREATE TABLE `marc_matchers` (
1367 `matcher_id` int(11) NOT NULL auto_increment,
1368 `code` varchar(10) NOT NULL default '',
1369 `description` varchar(255) NOT NULL default '',
1370 `record_type` varchar(10) NOT NULL default 'biblio',
1371 `threshold` int(11) NOT NULL default 0,
1372 PRIMARY KEY (`matcher_id`),
1373 KEY `code` (`code`),
1374 KEY `record_type` (`record_type`)
1375 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1378 -- Table structure for table `matchpoints`
1380 DROP TABLE IF EXISTS `matchpoints`;
1381 CREATE TABLE `matchpoints` (
1382 `matcher_id` int(11) NOT NULL,
1383 `matchpoint_id` int(11) NOT NULL auto_increment,
1384 `search_index` varchar(30) NOT NULL default '',
1385 `score` int(11) NOT NULL default 0,
1386 PRIMARY KEY (`matchpoint_id`),
1387 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1388 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1389 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1393 -- Table structure for table `matchpoint_components`
1395 DROP TABLE IF EXISTS `matchpoint_components`;
1396 CREATE TABLE `matchpoint_components` (
1397 `matchpoint_id` int(11) NOT NULL,
1398 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1399 sequence int(11) NOT NULL default 0,
1400 tag varchar(3) NOT NULL default '',
1401 subfields varchar(40) NOT NULL default '',
1402 offset int(4) NOT NULL default 0,
1403 length int(4) NOT NULL default 0,
1404 PRIMARY KEY (`matchpoint_component_id`),
1405 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1406 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1407 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1408 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1411 -- Table structure for table `matcher_component_norms`
1413 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1414 CREATE TABLE `matchpoint_component_norms` (
1415 `matchpoint_component_id` int(11) NOT NULL,
1416 `sequence` int(11) NOT NULL default 0,
1417 `norm_routine` varchar(50) NOT NULL default '',
1418 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1419 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1420 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1421 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1424 -- Table structure for table `matcher_matchpoints`
1426 DROP TABLE IF EXISTS `matcher_matchpoints`;
1427 CREATE TABLE `matcher_matchpoints` (
1428 `matcher_id` int(11) NOT NULL,
1429 `matchpoint_id` int(11) NOT NULL,
1430 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1431 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1432 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1433 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1434 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1437 -- Table structure for table `matchchecks`
1439 DROP TABLE IF EXISTS `matchchecks`;
1440 CREATE TABLE `matchchecks` (
1441 `matcher_id` int(11) NOT NULL,
1442 `matchcheck_id` int(11) NOT NULL auto_increment,
1443 `source_matchpoint_id` int(11) NOT NULL,
1444 `target_matchpoint_id` int(11) NOT NULL,
1445 PRIMARY KEY (`matchcheck_id`),
1446 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1447 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1448 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1449 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1450 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1451 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1452 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1455 -- Table structure for table `notifys`
1458 DROP TABLE IF EXISTS `notifys`;
1459 CREATE TABLE `notifys` (
1460 `notify_id` int(11) NOT NULL default 0,
1461 `borrowernumber` int(11) NOT NULL default 0,
1462 `itemnumber` int(11) NOT NULL default 0,
1463 `notify_date` date default NULL,
1464 `notify_send_date` date default NULL,
1465 `notify_level` int(1) NOT NULL default 0,
1466 `method` varchar(20) NOT NULL default ''
1467 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1470 -- Table structure for table `nozebra`
1473 DROP TABLE IF EXISTS `nozebra`;
1474 CREATE TABLE `nozebra` (
1475 `server` varchar(20) NOT NULL,
1476 `indexname` varchar(40) NOT NULL,
1477 `value` varchar(250) NOT NULL,
1478 `biblionumbers` longtext NOT NULL,
1479 KEY `indexname` (`server`,`indexname`),
1480 KEY `value` (`server`,`value`))
1481 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1484 -- Table structure for table `old_issues`
1487 DROP TABLE IF EXISTS `old_issues`;
1488 CREATE TABLE `old_issues` (
1489 `borrowernumber` int(11) default NULL,
1490 `itemnumber` int(11) default NULL,
1491 `date_due` date default NULL,
1492 `branchcode` varchar(10) default NULL,
1493 `issuingbranch` varchar(18) default NULL,
1494 `returndate` date default NULL,
1495 `lastreneweddate` date default NULL,
1496 `return` varchar(4) default NULL,
1497 `renewals` tinyint(4) default NULL,
1498 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1499 `issuedate` date default NULL,
1500 KEY `old_issuesborridx` (`borrowernumber`),
1501 KEY `old_issuesitemidx` (`itemnumber`),
1502 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1503 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1504 ON DELETE SET NULL ON UPDATE SET NULL,
1505 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1506 ON DELETE SET NULL ON UPDATE SET NULL
1507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1510 -- Table structure for table `old_reserves`
1512 DROP TABLE IF EXISTS `old_reserves`;
1513 CREATE TABLE `old_reserves` (
1514 `borrowernumber` int(11) default NULL,
1515 `reservedate` date default NULL,
1516 `biblionumber` int(11) default NULL,
1517 `constrainttype` varchar(1) default NULL,
1518 `branchcode` varchar(10) default NULL,
1519 `notificationdate` date default NULL,
1520 `reminderdate` date default NULL,
1521 `cancellationdate` date default NULL,
1522 `reservenotes` mediumtext,
1523 `priority` smallint(6) default NULL,
1524 `found` varchar(1) default NULL,
1525 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1526 `itemnumber` int(11) default NULL,
1527 `waitingdate` date default NULL,
1528 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1529 KEY `old_reserves_biblionumber` (`biblionumber`),
1530 KEY `old_reserves_itemnumber` (`itemnumber`),
1531 KEY `old_reserves_branchcode` (`branchcode`),
1532 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1533 ON DELETE SET NULL ON UPDATE SET NULL,
1534 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1535 ON DELETE SET NULL ON UPDATE SET NULL,
1536 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1537 ON DELETE SET NULL ON UPDATE SET NULL
1538 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1541 -- Table structure for table `opac_news`
1544 DROP TABLE IF EXISTS `opac_news`;
1545 CREATE TABLE `opac_news` (
1546 `idnew` int(10) unsigned NOT NULL auto_increment,
1547 `title` varchar(250) NOT NULL default '',
1548 `new` text NOT NULL,
1549 `lang` varchar(25) NOT NULL default '',
1550 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1551 `expirationdate` date default NULL,
1552 `number` int(11) default NULL,
1553 PRIMARY KEY (`idnew`)
1554 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1557 -- Table structure for table `overduerules`
1560 DROP TABLE IF EXISTS `overduerules`;
1561 CREATE TABLE `overduerules` (
1562 `branchcode` varchar(10) NOT NULL default '',
1563 `categorycode` varchar(10) NOT NULL default '',
1564 `delay1` int(4) default 0,
1565 `letter1` varchar(20) default NULL,
1566 `debarred1` varchar(1) default 0,
1567 `delay2` int(4) default 0,
1568 `debarred2` varchar(1) default 0,
1569 `letter2` varchar(20) default NULL,
1570 `delay3` int(4) default 0,
1571 `letter3` varchar(20) default NULL,
1572 `debarred3` int(1) default 0,
1573 PRIMARY KEY (`branchcode`,`categorycode`)
1574 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1577 -- Table structure for table `patroncards`
1580 DROP TABLE IF EXISTS `patroncards`;
1581 CREATE TABLE `patroncards` (
1582 `cardid` int(11) NOT NULL auto_increment,
1583 `batch_id` varchar(10) NOT NULL default '1',
1584 `borrowernumber` int(11) NOT NULL,
1585 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1586 PRIMARY KEY (`cardid`),
1587 KEY `patroncards_ibfk_1` (`borrowernumber`),
1588 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1589 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1592 -- Table structure for table `patronimage`
1595 DROP TABLE IF EXISTS `patronimage`;
1596 CREATE TABLE `patronimage` (
1597 `cardnumber` varchar(16) NOT NULL,
1598 `mimetype` varchar(15) NOT NULL,
1599 `imagefile` mediumblob NOT NULL,
1600 PRIMARY KEY (`cardnumber`),
1601 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1605 -- Table structure for table `printers`
1608 DROP TABLE IF EXISTS `printers`;
1609 CREATE TABLE `printers` (
1610 `printername` varchar(40) NOT NULL default '',
1611 `printqueue` varchar(20) default NULL,
1612 `printtype` varchar(20) default NULL,
1613 PRIMARY KEY (`printername`)
1614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1617 -- Table structure for table `printers_profile`
1620 DROP TABLE IF EXISTS `printers_profile`;
1621 CREATE TABLE `printers_profile` (
1622 `prof_id` int(4) NOT NULL auto_increment,
1623 `printername` varchar(40) NOT NULL,
1624 `tmpl_id` int(4) NOT NULL,
1625 `paper_bin` varchar(20) NOT NULL,
1626 `offset_horz` float default NULL,
1627 `offset_vert` float default NULL,
1628 `creep_horz` float default NULL,
1629 `creep_vert` float default NULL,
1630 `unit` char(20) NOT NULL default 'POINT',
1631 PRIMARY KEY (`prof_id`),
1632 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1633 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1634 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1637 -- Table structure for table `repeatable_holidays`
1640 DROP TABLE IF EXISTS `repeatable_holidays`;
1641 CREATE TABLE `repeatable_holidays` (
1642 `id` int(11) NOT NULL auto_increment,
1643 `branchcode` varchar(10) NOT NULL default '',
1644 `weekday` smallint(6) default NULL,
1645 `day` smallint(6) default NULL,
1646 `month` smallint(6) default NULL,
1647 `title` varchar(50) NOT NULL default '',
1648 `description` text NOT NULL,
1650 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1653 -- Table structure for table `reports_dictionary`
1656 DROP TABLE IF EXISTS `reports_dictionary`;
1657 CREATE TABLE reports_dictionary (
1658 `id` int(11) NOT NULL auto_increment,
1659 `name` varchar(255) default NULL,
1661 `date_created` datetime default NULL,
1662 `date_modified` datetime default NULL,
1664 `area` int(11) default NULL,
1666 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1669 -- Table structure for table `reserveconstraints`
1672 DROP TABLE IF EXISTS `reserveconstraints`;
1673 CREATE TABLE `reserveconstraints` (
1674 `borrowernumber` int(11) NOT NULL default 0,
1675 `reservedate` date default NULL,
1676 `biblionumber` int(11) NOT NULL default 0,
1677 `biblioitemnumber` int(11) default NULL,
1678 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1679 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1682 -- Table structure for table `reserves`
1685 DROP TABLE IF EXISTS `reserves`;
1686 CREATE TABLE `reserves` (
1687 `borrowernumber` int(11) NOT NULL default 0,
1688 `reservedate` date default NULL,
1689 `biblionumber` int(11) NOT NULL default 0,
1690 `constrainttype` varchar(1) default NULL,
1691 `branchcode` varchar(10) default NULL,
1692 `notificationdate` date default NULL,
1693 `reminderdate` date default NULL,
1694 `cancellationdate` date default NULL,
1695 `reservenotes` mediumtext,
1696 `priority` smallint(6) default NULL,
1697 `found` varchar(1) default NULL,
1698 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1699 `itemnumber` int(11) default NULL,
1700 `waitingdate` date default NULL,
1701 KEY `borrowernumber` (`borrowernumber`),
1702 KEY `biblionumber` (`biblionumber`),
1703 KEY `itemnumber` (`itemnumber`),
1704 KEY `branchcode` (`branchcode`),
1705 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1706 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1707 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1708 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1712 -- Table structure for table `reviews`
1715 DROP TABLE IF EXISTS `reviews`;
1716 CREATE TABLE `reviews` (
1717 `reviewid` int(11) NOT NULL auto_increment,
1718 `borrowernumber` int(11) default NULL,
1719 `biblionumber` int(11) default NULL,
1721 `approved` tinyint(4) default NULL,
1722 `datereviewed` datetime default NULL,
1723 PRIMARY KEY (`reviewid`)
1724 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1727 -- Table structure for table `roadtype`
1730 DROP TABLE IF EXISTS `roadtype`;
1731 CREATE TABLE `roadtype` (
1732 `roadtypeid` int(11) NOT NULL auto_increment,
1733 `road_type` varchar(100) NOT NULL default '',
1734 PRIMARY KEY (`roadtypeid`)
1735 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1738 -- Table structure for table `saved_sql`
1741 DROP TABLE IF EXISTS `saved_sql`;
1742 CREATE TABLE saved_sql (
1743 `id` int(11) NOT NULL auto_increment,
1744 `borrowernumber` int(11) default NULL,
1745 `date_created` datetime default NULL,
1746 `last_modified` datetime default NULL,
1748 `last_run` datetime default NULL,
1749 `report_name` varchar(255) default NULL,
1750 `type` varchar(255) default NULL,
1753 KEY boridx (`borrowernumber`)
1754 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1758 -- Table structure for `saved_reports`
1761 DROP TABLE IF EXISTS `saved_reports`;
1762 CREATE TABLE saved_reports (
1763 `id` int(11) NOT NULL auto_increment,
1764 `report_id` int(11) default NULL,
1766 `date_run` datetime default NULL,
1768 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1772 -- Table structure for table `serial`
1775 DROP TABLE IF EXISTS `serial`;
1776 CREATE TABLE `serial` (
1777 `serialid` int(11) NOT NULL auto_increment,
1778 `biblionumber` varchar(100) NOT NULL default '',
1779 `subscriptionid` varchar(100) NOT NULL default '',
1780 `serialseq` varchar(100) NOT NULL default '',
1781 `status` tinyint(4) NOT NULL default 0,
1782 `planneddate` date default NULL,
1784 `publisheddate` date default NULL,
1785 `itemnumber` text default NULL,
1786 `claimdate` date default NULL,
1787 `routingnotes` text,
1788 PRIMARY KEY (`serialid`)
1789 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1792 -- Table structure for table `sessions`
1795 DROP TABLE IF EXISTS sessions;
1796 CREATE TABLE sessions (
1797 `id` varchar(32) NOT NULL,
1798 `a_session` text NOT NULL,
1800 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1803 -- Table structure for table `special_holidays`
1806 DROP TABLE IF EXISTS `special_holidays`;
1807 CREATE TABLE `special_holidays` (
1808 `id` int(11) NOT NULL auto_increment,
1809 `branchcode` varchar(10) NOT NULL default '',
1810 `day` smallint(6) NOT NULL default 0,
1811 `month` smallint(6) NOT NULL default 0,
1812 `year` smallint(6) NOT NULL default 0,
1813 `isexception` smallint(1) NOT NULL default 1,
1814 `title` varchar(50) NOT NULL default '',
1815 `description` text NOT NULL,
1817 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1820 -- Table structure for table `statistics`
1823 DROP TABLE IF EXISTS `statistics`;
1824 CREATE TABLE `statistics` (
1825 `datetime` datetime default NULL,
1826 `branch` varchar(10) default NULL,
1827 `proccode` varchar(4) default NULL,
1828 `value` double(16,4) default NULL,
1829 `type` varchar(16) default NULL,
1831 `usercode` varchar(10) default NULL,
1832 `itemnumber` int(11) default NULL,
1833 `itemtype` varchar(10) default NULL,
1834 `borrowernumber` int(11) default NULL,
1835 `associatedborrower` int(11) default NULL,
1836 KEY `timeidx` (`datetime`)
1837 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1840 -- Table structure for table `stopwords`
1843 DROP TABLE IF EXISTS `stopwords`;
1844 CREATE TABLE `stopwords` (
1845 `word` varchar(255) default NULL
1846 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1849 -- Table structure for table `subscription`
1852 DROP TABLE IF EXISTS `subscription`;
1853 CREATE TABLE `subscription` (
1854 `biblionumber` int(11) NOT NULL default 0,
1855 `subscriptionid` int(11) NOT NULL auto_increment,
1856 `librarian` varchar(100) default '',
1857 `startdate` date default NULL,
1858 `aqbooksellerid` int(11) default 0,
1859 `cost` int(11) default 0,
1860 `aqbudgetid` int(11) default 0,
1861 `weeklength` int(11) default 0,
1862 `monthlength` int(11) default 0,
1863 `numberlength` int(11) default 0,
1864 `periodicity` tinyint(4) default 0,
1865 `dow` varchar(100) default '',
1866 `numberingmethod` varchar(100) default '',
1868 `status` varchar(100) NOT NULL default '',
1869 `add1` int(11) default 0,
1870 `every1` int(11) default 0,
1871 `whenmorethan1` int(11) default 0,
1872 `setto1` int(11) default NULL,
1873 `lastvalue1` int(11) default NULL,
1874 `add2` int(11) default 0,
1875 `every2` int(11) default 0,
1876 `whenmorethan2` int(11) default 0,
1877 `setto2` int(11) default NULL,
1878 `lastvalue2` int(11) default NULL,
1879 `add3` int(11) default 0,
1880 `every3` int(11) default 0,
1881 `innerloop1` int(11) default 0,
1882 `innerloop2` int(11) default 0,
1883 `innerloop3` int(11) default 0,
1884 `whenmorethan3` int(11) default 0,
1885 `setto3` int(11) default NULL,
1886 `lastvalue3` int(11) default NULL,
1887 `issuesatonce` tinyint(3) NOT NULL default 1,
1888 `firstacquidate` date default NULL,
1889 `manualhistory` tinyint(1) NOT NULL default 0,
1890 `irregularity` text,
1891 `letter` varchar(20) default NULL,
1892 `numberpattern` tinyint(3) default 0,
1893 `distributedto` text,
1894 `internalnotes` longtext,
1896 `location` varchar(80) NULL default '',
1897 `branchcode` varchar(10) NOT NULL default '',
1898 `hemisphere` tinyint(3) default 0,
1899 `lastbranch` varchar(10),
1900 `serialsadditems` tinyint(1) NOT NULL default '0',
1901 `staffdisplaycount` VARCHAR(10) NULL,
1902 `opacdisplaycount` VARCHAR(10) NULL,
1903 `graceperiod` int(11) NOT NULL default '0',
1904 PRIMARY KEY (`subscriptionid`)
1905 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1908 -- Table structure for table `subscriptionhistory`
1911 DROP TABLE IF EXISTS `subscriptionhistory`;
1912 CREATE TABLE `subscriptionhistory` (
1913 `biblionumber` int(11) NOT NULL default 0,
1914 `subscriptionid` int(11) NOT NULL default 0,
1915 `histstartdate` date default NULL,
1916 `enddate` date default NULL,
1917 `missinglist` longtext NOT NULL,
1918 `recievedlist` longtext NOT NULL,
1919 `opacnote` varchar(150) NOT NULL default '',
1920 `librariannote` varchar(150) NOT NULL default '',
1921 PRIMARY KEY (`subscriptionid`),
1922 KEY `biblionumber` (`biblionumber`)
1923 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1926 -- Table structure for table `subscriptionroutinglist`
1929 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1930 CREATE TABLE `subscriptionroutinglist` (
1931 `routingid` int(11) NOT NULL auto_increment,
1932 `borrowernumber` int(11) default NULL,
1933 `ranking` int(11) default NULL,
1934 `subscriptionid` int(11) default NULL,
1935 PRIMARY KEY (`routingid`)
1936 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1939 -- Table structure for table `suggestions`
1942 DROP TABLE IF EXISTS `suggestions`;
1943 CREATE TABLE `suggestions` (
1944 `suggestionid` int(8) NOT NULL auto_increment,
1945 `suggestedby` int(11) NOT NULL default 0,
1946 `managedby` int(11) default NULL,
1947 `STATUS` varchar(10) NOT NULL default '',
1949 `author` varchar(80) default NULL,
1950 `title` varchar(80) default NULL,
1951 `copyrightdate` smallint(6) default NULL,
1952 `publishercode` varchar(255) default NULL,
1953 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1954 `volumedesc` varchar(255) default NULL,
1955 `publicationyear` smallint(6) default 0,
1956 `place` varchar(255) default NULL,
1957 `isbn` varchar(30) default NULL,
1958 `mailoverseeing` smallint(1) default 0,
1959 `biblionumber` int(11) default NULL,
1961 PRIMARY KEY (`suggestionid`),
1962 KEY `suggestedby` (`suggestedby`),
1963 KEY `managedby` (`managedby`)
1964 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1967 -- Table structure for table `systempreferences`
1970 DROP TABLE IF EXISTS `systempreferences`;
1971 CREATE TABLE `systempreferences` (
1972 `variable` varchar(50) NOT NULL default '',
1974 `options` mediumtext,
1976 `type` varchar(20) default NULL,
1977 PRIMARY KEY (`variable`)
1978 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1981 -- Table structure for table `tags`
1984 DROP TABLE IF EXISTS `tags`;
1985 CREATE TABLE `tags` (
1986 `entry` varchar(255) NOT NULL default '',
1987 `weight` bigint(20) NOT NULL default 0,
1988 PRIMARY KEY (`entry`)
1989 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1992 -- Table structure for table `tags_all`
1995 DROP TABLE IF EXISTS `tags_all`;
1996 CREATE TABLE `tags_all` (
1997 `tag_id` int(11) NOT NULL auto_increment,
1998 `borrowernumber` int(11) NOT NULL,
1999 `biblionumber` int(11) NOT NULL,
2000 `term` varchar(255) NOT NULL,
2001 `language` int(4) default NULL,
2002 `date_created` datetime NOT NULL,
2003 PRIMARY KEY (`tag_id`),
2004 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2005 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2006 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2007 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2008 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2009 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2010 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2013 -- Table structure for table `tags_approval`
2016 DROP TABLE IF EXISTS `tags_approval`;
2017 CREATE TABLE `tags_approval` (
2018 `term` varchar(255) NOT NULL,
2019 `approved` int(1) NOT NULL default '0',
2020 `date_approved` datetime default NULL,
2021 `approved_by` int(11) default NULL,
2022 `weight_total` int(9) NOT NULL default '1',
2023 PRIMARY KEY (`term`),
2024 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2025 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2026 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2027 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2030 -- Table structure for table `tags_index`
2033 DROP TABLE IF EXISTS `tags_index`;
2034 CREATE TABLE `tags_index` (
2035 `term` varchar(255) NOT NULL,
2036 `biblionumber` int(11) NOT NULL,
2037 `weight` int(9) NOT NULL default '1',
2038 PRIMARY KEY (`term`,`biblionumber`),
2039 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2040 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2041 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2042 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2043 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2044 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2047 -- Table structure for table `userflags`
2050 DROP TABLE IF EXISTS `userflags`;
2051 CREATE TABLE `userflags` (
2052 `bit` int(11) NOT NULL default 0,
2053 `flag` varchar(30) default NULL,
2054 `flagdesc` varchar(255) default NULL,
2055 `defaulton` int(11) default NULL,
2057 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2060 -- Table structure for table `virtualshelves`
2063 DROP TABLE IF EXISTS `virtualshelves`;
2064 CREATE TABLE `virtualshelves` (
2065 `shelfnumber` int(11) NOT NULL auto_increment,
2066 `shelfname` varchar(255) default NULL,
2067 `owner` varchar(80) default NULL,
2068 `category` varchar(1) default NULL,
2069 `sortfield` varchar(16) default NULL,
2070 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2071 PRIMARY KEY (`shelfnumber`)
2072 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2075 -- Table structure for table `virtualshelfcontents`
2078 DROP TABLE IF EXISTS `virtualshelfcontents`;
2079 CREATE TABLE `virtualshelfcontents` (
2080 `shelfnumber` int(11) NOT NULL default 0,
2081 `biblionumber` int(11) NOT NULL default 0,
2082 `flags` int(11) default NULL,
2083 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2084 KEY `shelfnumber` (`shelfnumber`),
2085 KEY `biblionumber` (`biblionumber`),
2086 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2087 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2088 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2091 -- Table structure for table `z3950servers`
2094 DROP TABLE IF EXISTS `z3950servers`;
2095 CREATE TABLE `z3950servers` (
2096 `host` varchar(255) default NULL,
2097 `port` int(11) default NULL,
2098 `db` varchar(255) default NULL,
2099 `userid` varchar(255) default NULL,
2100 `password` varchar(255) default NULL,
2102 `id` int(11) NOT NULL auto_increment,
2103 `checked` smallint(6) default NULL,
2104 `rank` int(11) default NULL,
2105 `syntax` varchar(80) default NULL,
2107 `position` enum('primary','secondary','') NOT NULL default 'primary',
2108 `type` enum('zed','opensearch') NOT NULL default 'zed',
2109 `encoding` text default NULL,
2110 `description` text NOT NULL,
2112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2115 -- Table structure for table `zebraqueue`
2118 DROP TABLE IF EXISTS `zebraqueue`;
2119 CREATE TABLE `zebraqueue` (
2120 `id` int(11) NOT NULL auto_increment,
2121 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2122 `operation` char(20) NOT NULL default '',
2123 `server` char(20) NOT NULL default '',
2124 `done` int(11) NOT NULL default '0',
2125 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2127 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2128 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2130 DROP TABLE IF EXISTS `services_throttle`;
2131 CREATE TABLE `services_throttle` (
2132 `service_type` varchar(10) NOT NULL default '',
2133 `service_count` varchar(45) default NULL,
2134 PRIMARY KEY (`service_type`)
2135 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2137 -- http://www.w3.org/International/articles/language-tags/
2140 DROP TABLE IF EXISTS language_subtag_registry;
2141 CREATE TABLE language_subtag_registry (
2143 type varchar(25), -- language-script-region-variant-extension-privateuse
2144 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2146 KEY `subtag` (`subtag`)
2147 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2149 -- TODO: add suppress_scripts
2150 -- this maps three letter codes defined in iso639.2 back to their
2151 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2152 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2153 CREATE TABLE language_rfc4646_to_iso639 (
2154 rfc4646_subtag varchar(25),
2155 iso639_2_code varchar(25),
2156 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2157 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2159 DROP TABLE IF EXISTS language_descriptions;
2160 CREATE TABLE language_descriptions (
2164 description varchar(255),
2166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2168 -- bi-directional support, keyed by script subcode
2169 DROP TABLE IF EXISTS language_script_bidi;
2170 CREATE TABLE language_script_bidi (
2171 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2172 bidi varchar(3), -- rtl ltr
2173 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2174 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2176 -- TODO: need to map language subtags to script subtags for detection
2177 -- of bidi when script is not specified (like ar, he)
2178 DROP TABLE IF EXISTS language_script_mapping;
2179 CREATE TABLE language_script_mapping (
2180 language_subtag varchar(25),
2181 script_subtag varchar(25),
2182 KEY `language_subtag` (`language_subtag`)
2183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2185 DROP TABLE IF EXISTS `permissions`;
2186 CREATE TABLE `permissions` (
2187 `module_bit` int(11) NOT NULL DEFAULT 0,
2188 `code` varchar(64) DEFAULT NULL,
2189 `description` varchar(255) DEFAULT NULL,
2190 PRIMARY KEY (`module_bit`, `code`),
2191 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2192 ON DELETE CASCADE ON UPDATE CASCADE
2193 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2195 DROP TABLE IF EXISTS `serialitems`;
2196 CREATE TABLE `serialitems` (
2197 `itemnumber` int(11) NOT NULL,
2198 `serialid` int(11) NOT NULL,
2199 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2200 KEY `serialitems_sfk_1` (`serialid`),
2201 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2202 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2204 DROP TABLE IF EXISTS `user_permissions`;
2205 CREATE TABLE `user_permissions` (
2206 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2207 `module_bit` int(11) NOT NULL DEFAULT 0,
2208 `code` varchar(64) DEFAULT NULL,
2209 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2210 ON DELETE CASCADE ON UPDATE CASCADE,
2211 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2212 ON DELETE CASCADE ON UPDATE CASCADE
2213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2216 -- Table structure for table `tmp_holdsqueue`
2219 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2220 CREATE TABLE `tmp_holdsqueue` (
2221 `biblionumber` int(11) default NULL,
2222 `itemnumber` int(11) default NULL,
2223 `barcode` varchar(20) default NULL,
2224 `surname` mediumtext NOT NULL,
2227 `borrowernumber` int(11) NOT NULL,
2228 `cardnumber` varchar(16) default NULL,
2229 `reservedate` date default NULL,
2231 `itemcallnumber` varchar(30) default NULL,
2232 `holdingbranch` varchar(10) default NULL,
2233 `pickbranch` varchar(10) default NULL,
2235 `item_level_request` tinyint(4) NOT NULL default 0
2236 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2239 -- Table structure for table `message_queue`
2242 DROP TABLE IF EXISTS `message_queue`;
2243 CREATE TABLE `message_queue` (
2244 `message_id` int(11) NOT NULL auto_increment,
2245 `borrowernumber` int(11) default NULL,
2248 `metadata` text DEFAULT NULL,
2249 `letter_code` varchar(64) DEFAULT NULL,
2250 `message_transport_type` varchar(20) NOT NULL,
2251 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2252 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2253 `to_address` mediumtext,
2254 `from_address` mediumtext,
2255 `content_type` text,
2256 KEY `message_id` (`message_id`),
2257 KEY `borrowernumber` (`borrowernumber`),
2258 KEY `message_transport_type` (`message_transport_type`),
2259 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2260 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2261 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2264 -- Table structure for table `message_transport_types`
2267 DROP TABLE IF EXISTS `message_transport_types`;
2268 CREATE TABLE `message_transport_types` (
2269 `message_transport_type` varchar(20) NOT NULL,
2270 PRIMARY KEY (`message_transport_type`)
2271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2274 -- Table structure for table `message_attributes`
2277 DROP TABLE IF EXISTS `message_attributes`;
2278 CREATE TABLE `message_attributes` (
2279 `message_attribute_id` int(11) NOT NULL auto_increment,
2280 `message_name` varchar(20) NOT NULL default '',
2281 `takes_days` tinyint(1) NOT NULL default '0',
2282 PRIMARY KEY (`message_attribute_id`),
2283 UNIQUE KEY `message_name` (`message_name`)
2284 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2287 -- Table structure for table `message_transports`
2290 DROP TABLE IF EXISTS `message_transports`;
2291 CREATE TABLE `message_transports` (
2292 `message_attribute_id` int(11) NOT NULL,
2293 `message_transport_type` varchar(20) NOT NULL,
2294 `is_digest` tinyint(1) NOT NULL default '0',
2295 `letter_module` varchar(20) NOT NULL default '',
2296 `letter_code` varchar(20) NOT NULL default '',
2297 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2298 KEY `message_transport_type` (`message_transport_type`),
2299 KEY `letter_module` (`letter_module`,`letter_code`),
2300 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2301 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2302 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2303 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2306 -- Table structure for table `borrower_message_preferences`
2309 DROP TABLE IF EXISTS `borrower_message_preferences`;
2310 CREATE TABLE `borrower_message_preferences` (
2311 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2312 `borrowernumber` int(11) default NULL,
2313 `categorycode` varchar(10) default NULL,
2314 `message_attribute_id` int(11) default '0',
2315 `days_in_advance` int(11) default '0',
2316 `wants_digest` tinyint(1) NOT NULL default '0',
2317 PRIMARY KEY (`borrower_message_preference_id`),
2318 KEY `borrowernumber` (`borrowernumber`),
2319 KEY `categorycode` (`categorycode`),
2320 KEY `message_attribute_id` (`message_attribute_id`),
2321 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2322 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2323 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2324 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2327 -- Table structure for table `borrower_message_transport_preferences`
2330 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2331 CREATE TABLE `borrower_message_transport_preferences` (
2332 `borrower_message_preference_id` int(11) NOT NULL default '0',
2333 `message_transport_type` varchar(20) NOT NULL default '0',
2334 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2335 KEY `message_transport_type` (`message_transport_type`),
2336 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,
2337 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
2338 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2341 -- Table structure for the table branch_transfer_limits
2344 DROP TABLE IF EXISTS `branch_transfer_limits`;
2345 CREATE TABLE branch_transfer_limits (
2346 limitId int(8) NOT NULL auto_increment,
2347 toBranch varchar(10) NOT NULL,
2348 fromBranch varchar(10) NOT NULL,
2349 itemtype varchar(10) NULL,
2350 ccode varchar(10) NULL,
2351 PRIMARY KEY (limitId)
2352 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2355 -- Table structure for table `item_circulation_alert_preferences`
2358 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2359 CREATE TABLE `item_circulation_alert_preferences` (
2360 `id` int(11) NOT NULL auto_increment,
2361 `branchcode` varchar(10) NOT NULL,
2362 `categorycode` varchar(10) NOT NULL,
2363 `item_type` varchar(10) NOT NULL,
2364 `notification` varchar(16) NOT NULL,
2366 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2369 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2370 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2371 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2372 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2373 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2374 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2375 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2376 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;