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 `permanent_location` varchar(80) default NULL,
1187 `onloan` date default NULL,
1188 `cn_source` varchar(10) default NULL,
1189 `cn_sort` varchar(30) default NULL,
1190 `ccode` varchar(10) default NULL,
1191 `materials` varchar(10) default NULL,
1192 `uri` varchar(255) default NULL,
1193 `itype` varchar(10) default NULL,
1194 `more_subfields_xml` longtext default NULL,
1195 `enumchron` varchar(80) default NULL,
1196 `copynumber` varchar(32) default NULL,
1197 PRIMARY KEY (`itemnumber`),
1198 UNIQUE KEY `itembarcodeidx` (`barcode`),
1199 KEY `itembinoidx` (`biblioitemnumber`),
1200 KEY `itembibnoidx` (`biblionumber`),
1201 KEY `homebranch` (`homebranch`),
1202 KEY `holdingbranch` (`holdingbranch`),
1203 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1204 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1205 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1209 -- Table structure for table `itemtypes`
1212 DROP TABLE IF EXISTS `itemtypes`;
1213 CREATE TABLE `itemtypes` (
1214 `itemtype` varchar(10) NOT NULL default '',
1215 `description` mediumtext,
1216 `renewalsallowed` smallint(6) default NULL,
1217 `rentalcharge` double(16,4) default NULL,
1218 `notforloan` smallint(6) default NULL,
1219 `imageurl` varchar(200) default NULL,
1221 PRIMARY KEY (`itemtype`),
1222 UNIQUE KEY `itemtype` (`itemtype`)
1223 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1226 -- Table structure for table `labels`
1229 DROP TABLE IF EXISTS `labels`;
1230 CREATE TABLE `labels` (
1231 `labelid` int(11) NOT NULL auto_increment,
1232 `batch_id` int(10) NOT NULL default 1,
1233 `itemnumber` varchar(100) NOT NULL default '',
1234 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1235 PRIMARY KEY (`labelid`)
1236 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1239 -- Table structure for table `labels_conf`
1242 DROP TABLE IF EXISTS `labels_conf`;
1243 CREATE TABLE `labels_conf` (
1244 `id` int(4) NOT NULL auto_increment,
1245 `barcodetype` char(100) default '',
1246 `title` int(1) default '0',
1247 `subtitle` int(1) default '0',
1248 `itemtype` int(1) default '0',
1249 `barcode` int(1) default '0',
1250 `dewey` int(1) default '0',
1251 `classification` int(1) default NULL,
1252 `subclass` int(1) default '0',
1253 `itemcallnumber` int(1) default '0',
1254 `author` int(1) default '0',
1255 `issn` int(1) default '0',
1256 `isbn` int(1) default '0',
1257 `startlabel` int(2) NOT NULL default '1',
1258 `printingtype` char(32) default 'BAR',
1259 `formatstring` mediumtext default NULL,
1260 `layoutname` char(20) NOT NULL default 'TEST',
1261 `guidebox` int(1) default '0',
1262 `active` tinyint(1) default '1',
1263 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1264 `ccode` char(4) collate utf8_unicode_ci default NULL,
1265 `callnum_split` int(1) default NULL,
1266 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1268 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1271 -- Table structure for table `labels_profile`
1274 DROP TABLE IF EXISTS `labels_profile`;
1275 CREATE TABLE `labels_profile` (
1276 `tmpl_id` int(4) NOT NULL,
1277 `prof_id` int(4) NOT NULL,
1278 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1279 UNIQUE KEY `prof_id` (`prof_id`)
1280 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1283 -- Table structure for table `labels_templates`
1286 DROP TABLE IF EXISTS `labels_templates`;
1287 CREATE TABLE `labels_templates` (
1288 `tmpl_id` int(4) NOT NULL auto_increment,
1289 `tmpl_code` char(100) default '',
1290 `tmpl_desc` char(100) default '',
1291 `page_width` float default '0',
1292 `page_height` float default '0',
1293 `label_width` float default '0',
1294 `label_height` float default '0',
1295 `topmargin` float default '0',
1296 `leftmargin` float default '0',
1297 `cols` int(2) default '0',
1298 `rows` int(2) default '0',
1299 `colgap` float default '0',
1300 `rowgap` float default '0',
1301 `active` int(1) default NULL,
1302 `units` char(20) default 'PX',
1303 `fontsize` int(4) NOT NULL default '3',
1304 `font` char(10) NOT NULL default 'TR',
1305 PRIMARY KEY (`tmpl_id`)
1306 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1309 -- Table structure for table `letter`
1312 DROP TABLE IF EXISTS `letter`;
1313 CREATE TABLE `letter` (
1314 `module` varchar(20) NOT NULL default '',
1315 `code` varchar(20) NOT NULL default '',
1316 `name` varchar(100) NOT NULL default '',
1317 `title` varchar(200) NOT NULL default '',
1319 PRIMARY KEY (`module`,`code`)
1320 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1323 -- Table structure for table `marc_subfield_structure`
1326 DROP TABLE IF EXISTS `marc_subfield_structure`;
1327 CREATE TABLE `marc_subfield_structure` (
1328 `tagfield` varchar(3) NOT NULL default '',
1329 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1330 `liblibrarian` varchar(255) NOT NULL default '',
1331 `libopac` varchar(255) NOT NULL default '',
1332 `repeatable` tinyint(4) NOT NULL default 0,
1333 `mandatory` tinyint(4) NOT NULL default 0,
1334 `kohafield` varchar(40) default NULL,
1335 `tab` tinyint(1) default NULL,
1336 `authorised_value` varchar(20) default NULL,
1337 `authtypecode` varchar(20) default NULL,
1338 `value_builder` varchar(80) default NULL,
1339 `isurl` tinyint(1) default NULL,
1340 `hidden` tinyint(1) default NULL,
1341 `frameworkcode` varchar(4) NOT NULL default '',
1342 `seealso` varchar(1100) default NULL,
1343 `link` varchar(80) default NULL,
1344 `defaultvalue` text default NULL,
1345 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1346 KEY `kohafield_2` (`kohafield`),
1347 KEY `tab` (`frameworkcode`,`tab`),
1348 KEY `kohafield` (`frameworkcode`,`kohafield`)
1349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1352 -- Table structure for table `marc_tag_structure`
1355 DROP TABLE IF EXISTS `marc_tag_structure`;
1356 CREATE TABLE `marc_tag_structure` (
1357 `tagfield` varchar(3) NOT NULL default '',
1358 `liblibrarian` varchar(255) NOT NULL default '',
1359 `libopac` varchar(255) NOT NULL default '',
1360 `repeatable` tinyint(4) NOT NULL default 0,
1361 `mandatory` tinyint(4) NOT NULL default 0,
1362 `authorised_value` varchar(10) default NULL,
1363 `frameworkcode` varchar(4) NOT NULL default '',
1364 PRIMARY KEY (`frameworkcode`,`tagfield`)
1365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1368 -- Table structure for table `marc_matchers`
1371 DROP TABLE IF EXISTS `marc_matchers`;
1372 CREATE TABLE `marc_matchers` (
1373 `matcher_id` int(11) NOT NULL auto_increment,
1374 `code` varchar(10) NOT NULL default '',
1375 `description` varchar(255) NOT NULL default '',
1376 `record_type` varchar(10) NOT NULL default 'biblio',
1377 `threshold` int(11) NOT NULL default 0,
1378 PRIMARY KEY (`matcher_id`),
1379 KEY `code` (`code`),
1380 KEY `record_type` (`record_type`)
1381 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1384 -- Table structure for table `matchpoints`
1386 DROP TABLE IF EXISTS `matchpoints`;
1387 CREATE TABLE `matchpoints` (
1388 `matcher_id` int(11) NOT NULL,
1389 `matchpoint_id` int(11) NOT NULL auto_increment,
1390 `search_index` varchar(30) NOT NULL default '',
1391 `score` int(11) NOT NULL default 0,
1392 PRIMARY KEY (`matchpoint_id`),
1393 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1394 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1395 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1399 -- Table structure for table `matchpoint_components`
1401 DROP TABLE IF EXISTS `matchpoint_components`;
1402 CREATE TABLE `matchpoint_components` (
1403 `matchpoint_id` int(11) NOT NULL,
1404 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1405 sequence int(11) NOT NULL default 0,
1406 tag varchar(3) NOT NULL default '',
1407 subfields varchar(40) NOT NULL default '',
1408 offset int(4) NOT NULL default 0,
1409 length int(4) NOT NULL default 0,
1410 PRIMARY KEY (`matchpoint_component_id`),
1411 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1412 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1413 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1414 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1417 -- Table structure for table `matcher_component_norms`
1419 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1420 CREATE TABLE `matchpoint_component_norms` (
1421 `matchpoint_component_id` int(11) NOT NULL,
1422 `sequence` int(11) NOT NULL default 0,
1423 `norm_routine` varchar(50) NOT NULL default '',
1424 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1425 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1426 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1427 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1430 -- Table structure for table `matcher_matchpoints`
1432 DROP TABLE IF EXISTS `matcher_matchpoints`;
1433 CREATE TABLE `matcher_matchpoints` (
1434 `matcher_id` int(11) NOT NULL,
1435 `matchpoint_id` int(11) NOT NULL,
1436 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1437 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1438 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1439 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1440 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1443 -- Table structure for table `matchchecks`
1445 DROP TABLE IF EXISTS `matchchecks`;
1446 CREATE TABLE `matchchecks` (
1447 `matcher_id` int(11) NOT NULL,
1448 `matchcheck_id` int(11) NOT NULL auto_increment,
1449 `source_matchpoint_id` int(11) NOT NULL,
1450 `target_matchpoint_id` int(11) NOT NULL,
1451 PRIMARY KEY (`matchcheck_id`),
1452 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1453 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1454 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1455 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1456 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1457 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1458 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1461 -- Table structure for table `notifys`
1464 DROP TABLE IF EXISTS `notifys`;
1465 CREATE TABLE `notifys` (
1466 `notify_id` int(11) NOT NULL default 0,
1467 `borrowernumber` int(11) NOT NULL default 0,
1468 `itemnumber` int(11) NOT NULL default 0,
1469 `notify_date` date default NULL,
1470 `notify_send_date` date default NULL,
1471 `notify_level` int(1) NOT NULL default 0,
1472 `method` varchar(20) NOT NULL default ''
1473 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1476 -- Table structure for table `nozebra`
1479 DROP TABLE IF EXISTS `nozebra`;
1480 CREATE TABLE `nozebra` (
1481 `server` varchar(20) NOT NULL,
1482 `indexname` varchar(40) NOT NULL,
1483 `value` varchar(250) NOT NULL,
1484 `biblionumbers` longtext NOT NULL,
1485 KEY `indexname` (`server`,`indexname`),
1486 KEY `value` (`server`,`value`))
1487 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1490 -- Table structure for table `old_issues`
1493 DROP TABLE IF EXISTS `old_issues`;
1494 CREATE TABLE `old_issues` (
1495 `borrowernumber` int(11) default NULL,
1496 `itemnumber` int(11) default NULL,
1497 `date_due` date default NULL,
1498 `branchcode` varchar(10) default NULL,
1499 `issuingbranch` varchar(18) default NULL,
1500 `returndate` date default NULL,
1501 `lastreneweddate` date default NULL,
1502 `return` varchar(4) default NULL,
1503 `renewals` tinyint(4) default NULL,
1504 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1505 `issuedate` date default NULL,
1506 KEY `old_issuesborridx` (`borrowernumber`),
1507 KEY `old_issuesitemidx` (`itemnumber`),
1508 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1509 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1510 ON DELETE SET NULL ON UPDATE SET NULL,
1511 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1512 ON DELETE SET NULL ON UPDATE SET NULL
1513 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1516 -- Table structure for table `old_reserves`
1518 DROP TABLE IF EXISTS `old_reserves`;
1519 CREATE TABLE `old_reserves` (
1520 `borrowernumber` int(11) default NULL,
1521 `reservedate` date default NULL,
1522 `biblionumber` int(11) default NULL,
1523 `constrainttype` varchar(1) default NULL,
1524 `branchcode` varchar(10) default NULL,
1525 `notificationdate` date default NULL,
1526 `reminderdate` date default NULL,
1527 `cancellationdate` date default NULL,
1528 `reservenotes` mediumtext,
1529 `priority` smallint(6) default NULL,
1530 `found` varchar(1) default NULL,
1531 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1532 `itemnumber` int(11) default NULL,
1533 `waitingdate` date default NULL,
1534 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1535 KEY `old_reserves_biblionumber` (`biblionumber`),
1536 KEY `old_reserves_itemnumber` (`itemnumber`),
1537 KEY `old_reserves_branchcode` (`branchcode`),
1538 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1539 ON DELETE SET NULL ON UPDATE SET NULL,
1540 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1541 ON DELETE SET NULL ON UPDATE SET NULL,
1542 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1543 ON DELETE SET NULL ON UPDATE SET NULL
1544 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1547 -- Table structure for table `opac_news`
1550 DROP TABLE IF EXISTS `opac_news`;
1551 CREATE TABLE `opac_news` (
1552 `idnew` int(10) unsigned NOT NULL auto_increment,
1553 `title` varchar(250) NOT NULL default '',
1554 `new` text NOT NULL,
1555 `lang` varchar(25) NOT NULL default '',
1556 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1557 `expirationdate` date default NULL,
1558 `number` int(11) default NULL,
1559 PRIMARY KEY (`idnew`)
1560 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1563 -- Table structure for table `overduerules`
1566 DROP TABLE IF EXISTS `overduerules`;
1567 CREATE TABLE `overduerules` (
1568 `branchcode` varchar(10) NOT NULL default '',
1569 `categorycode` varchar(10) NOT NULL default '',
1570 `delay1` int(4) default 0,
1571 `letter1` varchar(20) default NULL,
1572 `debarred1` varchar(1) default 0,
1573 `delay2` int(4) default 0,
1574 `debarred2` varchar(1) default 0,
1575 `letter2` varchar(20) default NULL,
1576 `delay3` int(4) default 0,
1577 `letter3` varchar(20) default NULL,
1578 `debarred3` int(1) default 0,
1579 PRIMARY KEY (`branchcode`,`categorycode`)
1580 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1583 -- Table structure for table `patroncards`
1586 DROP TABLE IF EXISTS `patroncards`;
1587 CREATE TABLE `patroncards` (
1588 `cardid` int(11) NOT NULL auto_increment,
1589 `batch_id` varchar(10) NOT NULL default '1',
1590 `borrowernumber` int(11) NOT NULL,
1591 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1592 PRIMARY KEY (`cardid`),
1593 KEY `patroncards_ibfk_1` (`borrowernumber`),
1594 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1595 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1598 -- Table structure for table `patronimage`
1601 DROP TABLE IF EXISTS `patronimage`;
1602 CREATE TABLE `patronimage` (
1603 `cardnumber` varchar(16) NOT NULL,
1604 `mimetype` varchar(15) NOT NULL,
1605 `imagefile` mediumblob NOT NULL,
1606 PRIMARY KEY (`cardnumber`),
1607 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1608 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1611 -- Table structure for table `printers`
1614 DROP TABLE IF EXISTS `printers`;
1615 CREATE TABLE `printers` (
1616 `printername` varchar(40) NOT NULL default '',
1617 `printqueue` varchar(20) default NULL,
1618 `printtype` varchar(20) default NULL,
1619 PRIMARY KEY (`printername`)
1620 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1623 -- Table structure for table `printers_profile`
1626 DROP TABLE IF EXISTS `printers_profile`;
1627 CREATE TABLE `printers_profile` (
1628 `prof_id` int(4) NOT NULL auto_increment,
1629 `printername` varchar(40) NOT NULL,
1630 `tmpl_id` int(4) NOT NULL,
1631 `paper_bin` varchar(20) NOT NULL,
1632 `offset_horz` float default NULL,
1633 `offset_vert` float default NULL,
1634 `creep_horz` float default NULL,
1635 `creep_vert` float default NULL,
1636 `unit` char(20) NOT NULL default 'POINT',
1637 PRIMARY KEY (`prof_id`),
1638 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1639 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1640 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1643 -- Table structure for table `repeatable_holidays`
1646 DROP TABLE IF EXISTS `repeatable_holidays`;
1647 CREATE TABLE `repeatable_holidays` (
1648 `id` int(11) NOT NULL auto_increment,
1649 `branchcode` varchar(10) NOT NULL default '',
1650 `weekday` smallint(6) default NULL,
1651 `day` smallint(6) default NULL,
1652 `month` smallint(6) default NULL,
1653 `title` varchar(50) NOT NULL default '',
1654 `description` text NOT NULL,
1656 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1659 -- Table structure for table `reports_dictionary`
1662 DROP TABLE IF EXISTS `reports_dictionary`;
1663 CREATE TABLE reports_dictionary (
1664 `id` int(11) NOT NULL auto_increment,
1665 `name` varchar(255) default NULL,
1667 `date_created` datetime default NULL,
1668 `date_modified` datetime default NULL,
1670 `area` int(11) default NULL,
1672 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1675 -- Table structure for table `reserveconstraints`
1678 DROP TABLE IF EXISTS `reserveconstraints`;
1679 CREATE TABLE `reserveconstraints` (
1680 `borrowernumber` int(11) NOT NULL default 0,
1681 `reservedate` date default NULL,
1682 `biblionumber` int(11) NOT NULL default 0,
1683 `biblioitemnumber` int(11) default NULL,
1684 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1685 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1688 -- Table structure for table `reserves`
1691 DROP TABLE IF EXISTS `reserves`;
1692 CREATE TABLE `reserves` (
1693 `borrowernumber` int(11) NOT NULL default 0,
1694 `reservedate` date default NULL,
1695 `biblionumber` int(11) NOT NULL default 0,
1696 `constrainttype` varchar(1) default NULL,
1697 `branchcode` varchar(10) default NULL,
1698 `notificationdate` date default NULL,
1699 `reminderdate` date default NULL,
1700 `cancellationdate` date default NULL,
1701 `reservenotes` mediumtext,
1702 `priority` smallint(6) default NULL,
1703 `found` varchar(1) default NULL,
1704 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1705 `itemnumber` int(11) default NULL,
1706 `waitingdate` date default NULL,
1707 KEY `borrowernumber` (`borrowernumber`),
1708 KEY `biblionumber` (`biblionumber`),
1709 KEY `itemnumber` (`itemnumber`),
1710 KEY `branchcode` (`branchcode`),
1711 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1712 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1713 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1714 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1715 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1718 -- Table structure for table `reviews`
1721 DROP TABLE IF EXISTS `reviews`;
1722 CREATE TABLE `reviews` (
1723 `reviewid` int(11) NOT NULL auto_increment,
1724 `borrowernumber` int(11) default NULL,
1725 `biblionumber` int(11) default NULL,
1727 `approved` tinyint(4) default NULL,
1728 `datereviewed` datetime default NULL,
1729 PRIMARY KEY (`reviewid`)
1730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1733 -- Table structure for table `roadtype`
1736 DROP TABLE IF EXISTS `roadtype`;
1737 CREATE TABLE `roadtype` (
1738 `roadtypeid` int(11) NOT NULL auto_increment,
1739 `road_type` varchar(100) NOT NULL default '',
1740 PRIMARY KEY (`roadtypeid`)
1741 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1744 -- Table structure for table `saved_sql`
1747 DROP TABLE IF EXISTS `saved_sql`;
1748 CREATE TABLE saved_sql (
1749 `id` int(11) NOT NULL auto_increment,
1750 `borrowernumber` int(11) default NULL,
1751 `date_created` datetime default NULL,
1752 `last_modified` datetime default NULL,
1754 `last_run` datetime default NULL,
1755 `report_name` varchar(255) default NULL,
1756 `type` varchar(255) default NULL,
1759 KEY boridx (`borrowernumber`)
1760 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1764 -- Table structure for `saved_reports`
1767 DROP TABLE IF EXISTS `saved_reports`;
1768 CREATE TABLE saved_reports (
1769 `id` int(11) NOT NULL auto_increment,
1770 `report_id` int(11) default NULL,
1772 `date_run` datetime default NULL,
1774 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1778 -- Table structure for table `serial`
1781 DROP TABLE IF EXISTS `serial`;
1782 CREATE TABLE `serial` (
1783 `serialid` int(11) NOT NULL auto_increment,
1784 `biblionumber` varchar(100) NOT NULL default '',
1785 `subscriptionid` varchar(100) NOT NULL default '',
1786 `serialseq` varchar(100) NOT NULL default '',
1787 `status` tinyint(4) NOT NULL default 0,
1788 `planneddate` date default NULL,
1790 `publisheddate` date default NULL,
1791 `itemnumber` text default NULL,
1792 `claimdate` date default NULL,
1793 `routingnotes` text,
1794 PRIMARY KEY (`serialid`)
1795 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1798 -- Table structure for table `sessions`
1801 DROP TABLE IF EXISTS sessions;
1802 CREATE TABLE sessions (
1803 `id` varchar(32) NOT NULL,
1804 `a_session` text NOT NULL,
1806 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1809 -- Table structure for table `special_holidays`
1812 DROP TABLE IF EXISTS `special_holidays`;
1813 CREATE TABLE `special_holidays` (
1814 `id` int(11) NOT NULL auto_increment,
1815 `branchcode` varchar(10) NOT NULL default '',
1816 `day` smallint(6) NOT NULL default 0,
1817 `month` smallint(6) NOT NULL default 0,
1818 `year` smallint(6) NOT NULL default 0,
1819 `isexception` smallint(1) NOT NULL default 1,
1820 `title` varchar(50) NOT NULL default '',
1821 `description` text NOT NULL,
1823 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1826 -- Table structure for table `statistics`
1829 DROP TABLE IF EXISTS `statistics`;
1830 CREATE TABLE `statistics` (
1831 `datetime` datetime default NULL,
1832 `branch` varchar(10) default NULL,
1833 `proccode` varchar(4) default NULL,
1834 `value` double(16,4) default NULL,
1835 `type` varchar(16) default NULL,
1837 `usercode` varchar(10) default NULL,
1838 `itemnumber` int(11) default NULL,
1839 `itemtype` varchar(10) default NULL,
1840 `borrowernumber` int(11) default NULL,
1841 `associatedborrower` int(11) default NULL,
1842 KEY `timeidx` (`datetime`)
1843 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1846 -- Table structure for table `stopwords`
1849 DROP TABLE IF EXISTS `stopwords`;
1850 CREATE TABLE `stopwords` (
1851 `word` varchar(255) default NULL
1852 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1855 -- Table structure for table `subscription`
1858 DROP TABLE IF EXISTS `subscription`;
1859 CREATE TABLE `subscription` (
1860 `biblionumber` int(11) NOT NULL default 0,
1861 `subscriptionid` int(11) NOT NULL auto_increment,
1862 `librarian` varchar(100) default '',
1863 `startdate` date default NULL,
1864 `aqbooksellerid` int(11) default 0,
1865 `cost` int(11) default 0,
1866 `aqbudgetid` int(11) default 0,
1867 `weeklength` int(11) default 0,
1868 `monthlength` int(11) default 0,
1869 `numberlength` int(11) default 0,
1870 `periodicity` tinyint(4) default 0,
1871 `dow` varchar(100) default '',
1872 `numberingmethod` varchar(100) default '',
1874 `status` varchar(100) NOT NULL default '',
1875 `add1` int(11) default 0,
1876 `every1` int(11) default 0,
1877 `whenmorethan1` int(11) default 0,
1878 `setto1` int(11) default NULL,
1879 `lastvalue1` int(11) default NULL,
1880 `add2` int(11) default 0,
1881 `every2` int(11) default 0,
1882 `whenmorethan2` int(11) default 0,
1883 `setto2` int(11) default NULL,
1884 `lastvalue2` int(11) default NULL,
1885 `add3` int(11) default 0,
1886 `every3` int(11) default 0,
1887 `innerloop1` int(11) default 0,
1888 `innerloop2` int(11) default 0,
1889 `innerloop3` int(11) default 0,
1890 `whenmorethan3` int(11) default 0,
1891 `setto3` int(11) default NULL,
1892 `lastvalue3` int(11) default NULL,
1893 `issuesatonce` tinyint(3) NOT NULL default 1,
1894 `firstacquidate` date default NULL,
1895 `manualhistory` tinyint(1) NOT NULL default 0,
1896 `irregularity` text,
1897 `letter` varchar(20) default NULL,
1898 `numberpattern` tinyint(3) default 0,
1899 `distributedto` text,
1900 `internalnotes` longtext,
1902 `location` varchar(80) NULL default '',
1903 `branchcode` varchar(10) NOT NULL default '',
1904 `hemisphere` tinyint(3) default 0,
1905 `lastbranch` varchar(10),
1906 `serialsadditems` tinyint(1) NOT NULL default '0',
1907 `staffdisplaycount` VARCHAR(10) NULL,
1908 `opacdisplaycount` VARCHAR(10) NULL,
1909 `graceperiod` int(11) NOT NULL default '0',
1910 PRIMARY KEY (`subscriptionid`)
1911 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1914 -- Table structure for table `subscriptionhistory`
1917 DROP TABLE IF EXISTS `subscriptionhistory`;
1918 CREATE TABLE `subscriptionhistory` (
1919 `biblionumber` int(11) NOT NULL default 0,
1920 `subscriptionid` int(11) NOT NULL default 0,
1921 `histstartdate` date default NULL,
1922 `enddate` date default NULL,
1923 `missinglist` longtext NOT NULL,
1924 `recievedlist` longtext NOT NULL,
1925 `opacnote` varchar(150) NOT NULL default '',
1926 `librariannote` varchar(150) NOT NULL default '',
1927 PRIMARY KEY (`subscriptionid`),
1928 KEY `biblionumber` (`biblionumber`)
1929 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1932 -- Table structure for table `subscriptionroutinglist`
1935 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1936 CREATE TABLE `subscriptionroutinglist` (
1937 `routingid` int(11) NOT NULL auto_increment,
1938 `borrowernumber` int(11) default NULL,
1939 `ranking` int(11) default NULL,
1940 `subscriptionid` int(11) default NULL,
1941 PRIMARY KEY (`routingid`)
1942 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1945 -- Table structure for table `suggestions`
1948 DROP TABLE IF EXISTS `suggestions`;
1949 CREATE TABLE `suggestions` (
1950 `suggestionid` int(8) NOT NULL auto_increment,
1951 `suggestedby` int(11) NOT NULL default 0,
1952 `managedby` int(11) default NULL,
1953 `STATUS` varchar(10) NOT NULL default '',
1955 `author` varchar(80) default NULL,
1956 `title` varchar(80) default NULL,
1957 `copyrightdate` smallint(6) default NULL,
1958 `publishercode` varchar(255) default NULL,
1959 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1960 `volumedesc` varchar(255) default NULL,
1961 `publicationyear` smallint(6) default 0,
1962 `place` varchar(255) default NULL,
1963 `isbn` varchar(30) default NULL,
1964 `mailoverseeing` smallint(1) default 0,
1965 `biblionumber` int(11) default NULL,
1967 PRIMARY KEY (`suggestionid`),
1968 KEY `suggestedby` (`suggestedby`),
1969 KEY `managedby` (`managedby`)
1970 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1973 -- Table structure for table `systempreferences`
1976 DROP TABLE IF EXISTS `systempreferences`;
1977 CREATE TABLE `systempreferences` (
1978 `variable` varchar(50) NOT NULL default '',
1980 `options` mediumtext,
1982 `type` varchar(20) default NULL,
1983 PRIMARY KEY (`variable`)
1984 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1987 -- Table structure for table `tags`
1990 DROP TABLE IF EXISTS `tags`;
1991 CREATE TABLE `tags` (
1992 `entry` varchar(255) NOT NULL default '',
1993 `weight` bigint(20) NOT NULL default 0,
1994 PRIMARY KEY (`entry`)
1995 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1998 -- Table structure for table `tags_all`
2001 DROP TABLE IF EXISTS `tags_all`;
2002 CREATE TABLE `tags_all` (
2003 `tag_id` int(11) NOT NULL auto_increment,
2004 `borrowernumber` int(11) NOT NULL,
2005 `biblionumber` int(11) NOT NULL,
2006 `term` varchar(255) NOT NULL,
2007 `language` int(4) default NULL,
2008 `date_created` datetime NOT NULL,
2009 PRIMARY KEY (`tag_id`),
2010 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2011 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2012 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2013 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2014 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2015 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2016 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2019 -- Table structure for table `tags_approval`
2022 DROP TABLE IF EXISTS `tags_approval`;
2023 CREATE TABLE `tags_approval` (
2024 `term` varchar(255) NOT NULL,
2025 `approved` int(1) NOT NULL default '0',
2026 `date_approved` datetime default NULL,
2027 `approved_by` int(11) default NULL,
2028 `weight_total` int(9) NOT NULL default '1',
2029 PRIMARY KEY (`term`),
2030 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2031 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2032 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2033 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2036 -- Table structure for table `tags_index`
2039 DROP TABLE IF EXISTS `tags_index`;
2040 CREATE TABLE `tags_index` (
2041 `term` varchar(255) NOT NULL,
2042 `biblionumber` int(11) NOT NULL,
2043 `weight` int(9) NOT NULL default '1',
2044 PRIMARY KEY (`term`,`biblionumber`),
2045 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2046 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2047 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2048 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2049 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2050 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2053 -- Table structure for table `userflags`
2056 DROP TABLE IF EXISTS `userflags`;
2057 CREATE TABLE `userflags` (
2058 `bit` int(11) NOT NULL default 0,
2059 `flag` varchar(30) default NULL,
2060 `flagdesc` varchar(255) default NULL,
2061 `defaulton` int(11) default NULL,
2063 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2066 -- Table structure for table `virtualshelves`
2069 DROP TABLE IF EXISTS `virtualshelves`;
2070 CREATE TABLE `virtualshelves` (
2071 `shelfnumber` int(11) NOT NULL auto_increment,
2072 `shelfname` varchar(255) default NULL,
2073 `owner` varchar(80) default NULL,
2074 `category` varchar(1) default NULL,
2075 `sortfield` varchar(16) default NULL,
2076 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2077 PRIMARY KEY (`shelfnumber`)
2078 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2081 -- Table structure for table `virtualshelfcontents`
2084 DROP TABLE IF EXISTS `virtualshelfcontents`;
2085 CREATE TABLE `virtualshelfcontents` (
2086 `shelfnumber` int(11) NOT NULL default 0,
2087 `biblionumber` int(11) NOT NULL default 0,
2088 `flags` int(11) default NULL,
2089 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2090 KEY `shelfnumber` (`shelfnumber`),
2091 KEY `biblionumber` (`biblionumber`),
2092 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2093 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2094 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2097 -- Table structure for table `z3950servers`
2100 DROP TABLE IF EXISTS `z3950servers`;
2101 CREATE TABLE `z3950servers` (
2102 `host` varchar(255) default NULL,
2103 `port` int(11) default NULL,
2104 `db` varchar(255) default NULL,
2105 `userid` varchar(255) default NULL,
2106 `password` varchar(255) default NULL,
2108 `id` int(11) NOT NULL auto_increment,
2109 `checked` smallint(6) default NULL,
2110 `rank` int(11) default NULL,
2111 `syntax` varchar(80) default NULL,
2113 `position` enum('primary','secondary','') NOT NULL default 'primary',
2114 `type` enum('zed','opensearch') NOT NULL default 'zed',
2115 `encoding` text default NULL,
2116 `description` text NOT NULL,
2118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2121 -- Table structure for table `zebraqueue`
2124 DROP TABLE IF EXISTS `zebraqueue`;
2125 CREATE TABLE `zebraqueue` (
2126 `id` int(11) NOT NULL auto_increment,
2127 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2128 `operation` char(20) NOT NULL default '',
2129 `server` char(20) NOT NULL default '',
2130 `done` int(11) NOT NULL default '0',
2131 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2133 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2134 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2136 DROP TABLE IF EXISTS `services_throttle`;
2137 CREATE TABLE `services_throttle` (
2138 `service_type` varchar(10) NOT NULL default '',
2139 `service_count` varchar(45) default NULL,
2140 PRIMARY KEY (`service_type`)
2141 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2143 -- http://www.w3.org/International/articles/language-tags/
2146 DROP TABLE IF EXISTS language_subtag_registry;
2147 CREATE TABLE language_subtag_registry (
2149 type varchar(25), -- language-script-region-variant-extension-privateuse
2150 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2152 KEY `subtag` (`subtag`)
2153 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2155 -- TODO: add suppress_scripts
2156 -- this maps three letter codes defined in iso639.2 back to their
2157 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2158 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2159 CREATE TABLE language_rfc4646_to_iso639 (
2160 rfc4646_subtag varchar(25),
2161 iso639_2_code varchar(25),
2162 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2163 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2165 DROP TABLE IF EXISTS language_descriptions;
2166 CREATE TABLE language_descriptions (
2170 description varchar(255),
2172 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2174 -- bi-directional support, keyed by script subcode
2175 DROP TABLE IF EXISTS language_script_bidi;
2176 CREATE TABLE language_script_bidi (
2177 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2178 bidi varchar(3), -- rtl ltr
2179 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2180 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2182 -- TODO: need to map language subtags to script subtags for detection
2183 -- of bidi when script is not specified (like ar, he)
2184 DROP TABLE IF EXISTS language_script_mapping;
2185 CREATE TABLE language_script_mapping (
2186 language_subtag varchar(25),
2187 script_subtag varchar(25),
2188 KEY `language_subtag` (`language_subtag`)
2189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2191 DROP TABLE IF EXISTS `permissions`;
2192 CREATE TABLE `permissions` (
2193 `module_bit` int(11) NOT NULL DEFAULT 0,
2194 `code` varchar(64) DEFAULT NULL,
2195 `description` varchar(255) DEFAULT NULL,
2196 PRIMARY KEY (`module_bit`, `code`),
2197 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2198 ON DELETE CASCADE ON UPDATE CASCADE
2199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2201 DROP TABLE IF EXISTS `serialitems`;
2202 CREATE TABLE `serialitems` (
2203 `itemnumber` int(11) NOT NULL,
2204 `serialid` int(11) NOT NULL,
2205 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2206 KEY `serialitems_sfk_1` (`serialid`),
2207 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2210 DROP TABLE IF EXISTS `user_permissions`;
2211 CREATE TABLE `user_permissions` (
2212 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2213 `module_bit` int(11) NOT NULL DEFAULT 0,
2214 `code` varchar(64) DEFAULT NULL,
2215 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2216 ON DELETE CASCADE ON UPDATE CASCADE,
2217 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2218 ON DELETE CASCADE ON UPDATE CASCADE
2219 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2222 -- Table structure for table `tmp_holdsqueue`
2225 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2226 CREATE TABLE `tmp_holdsqueue` (
2227 `biblionumber` int(11) default NULL,
2228 `itemnumber` int(11) default NULL,
2229 `barcode` varchar(20) default NULL,
2230 `surname` mediumtext NOT NULL,
2233 `borrowernumber` int(11) NOT NULL,
2234 `cardnumber` varchar(16) default NULL,
2235 `reservedate` date default NULL,
2237 `itemcallnumber` varchar(30) default NULL,
2238 `holdingbranch` varchar(10) default NULL,
2239 `pickbranch` varchar(10) default NULL,
2241 `item_level_request` tinyint(4) NOT NULL default 0
2242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2245 -- Table structure for table `message_queue`
2248 DROP TABLE IF EXISTS `message_queue`;
2249 CREATE TABLE `message_queue` (
2250 `message_id` int(11) NOT NULL auto_increment,
2251 `borrowernumber` int(11) default NULL,
2254 `metadata` text DEFAULT NULL,
2255 `letter_code` varchar(64) DEFAULT NULL,
2256 `message_transport_type` varchar(20) NOT NULL,
2257 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2258 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2259 `to_address` mediumtext,
2260 `from_address` mediumtext,
2261 `content_type` text,
2262 KEY `message_id` (`message_id`),
2263 KEY `borrowernumber` (`borrowernumber`),
2264 KEY `message_transport_type` (`message_transport_type`),
2265 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2266 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2267 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2270 -- Table structure for table `message_transport_types`
2273 DROP TABLE IF EXISTS `message_transport_types`;
2274 CREATE TABLE `message_transport_types` (
2275 `message_transport_type` varchar(20) NOT NULL,
2276 PRIMARY KEY (`message_transport_type`)
2277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2280 -- Table structure for table `message_attributes`
2283 DROP TABLE IF EXISTS `message_attributes`;
2284 CREATE TABLE `message_attributes` (
2285 `message_attribute_id` int(11) NOT NULL auto_increment,
2286 `message_name` varchar(20) NOT NULL default '',
2287 `takes_days` tinyint(1) NOT NULL default '0',
2288 PRIMARY KEY (`message_attribute_id`),
2289 UNIQUE KEY `message_name` (`message_name`)
2290 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2293 -- Table structure for table `message_transports`
2296 DROP TABLE IF EXISTS `message_transports`;
2297 CREATE TABLE `message_transports` (
2298 `message_attribute_id` int(11) NOT NULL,
2299 `message_transport_type` varchar(20) NOT NULL,
2300 `is_digest` tinyint(1) NOT NULL default '0',
2301 `letter_module` varchar(20) NOT NULL default '',
2302 `letter_code` varchar(20) NOT NULL default '',
2303 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2304 KEY `message_transport_type` (`message_transport_type`),
2305 KEY `letter_module` (`letter_module`,`letter_code`),
2306 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2307 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2308 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2309 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2312 -- Table structure for table `borrower_message_preferences`
2315 DROP TABLE IF EXISTS `borrower_message_preferences`;
2316 CREATE TABLE `borrower_message_preferences` (
2317 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2318 `borrowernumber` int(11) default NULL,
2319 `categorycode` varchar(10) default NULL,
2320 `message_attribute_id` int(11) default '0',
2321 `days_in_advance` int(11) default '0',
2322 `wants_digest` tinyint(1) NOT NULL default '0',
2323 PRIMARY KEY (`borrower_message_preference_id`),
2324 KEY `borrowernumber` (`borrowernumber`),
2325 KEY `categorycode` (`categorycode`),
2326 KEY `message_attribute_id` (`message_attribute_id`),
2327 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2328 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2329 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2333 -- Table structure for table `borrower_message_transport_preferences`
2336 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2337 CREATE TABLE `borrower_message_transport_preferences` (
2338 `borrower_message_preference_id` int(11) NOT NULL default '0',
2339 `message_transport_type` varchar(20) NOT NULL default '0',
2340 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2341 KEY `message_transport_type` (`message_transport_type`),
2342 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,
2343 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
2344 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2347 -- Table structure for the table branch_transfer_limits
2350 DROP TABLE IF EXISTS `branch_transfer_limits`;
2351 CREATE TABLE branch_transfer_limits (
2352 limitId int(8) NOT NULL auto_increment,
2353 toBranch varchar(10) NOT NULL,
2354 fromBranch varchar(10) NOT NULL,
2355 itemtype varchar(10) NULL,
2356 ccode varchar(10) NULL,
2357 PRIMARY KEY (limitId)
2358 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2361 -- Table structure for table `item_circulation_alert_preferences`
2364 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2365 CREATE TABLE `item_circulation_alert_preferences` (
2366 `id` int(11) NOT NULL auto_increment,
2367 `branchcode` varchar(10) NOT NULL,
2368 `categorycode` varchar(10) NOT NULL,
2369 `item_type` varchar(10) NOT NULL,
2370 `notification` varchar(16) NOT NULL,
2372 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2373 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2375 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2376 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2377 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2378 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2379 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2380 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2381 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2382 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;