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` int(11) 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`)
345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
348 -- Table structure for table `biblio`
351 DROP TABLE IF EXISTS `biblio`;
352 CREATE TABLE `biblio` (
353 `biblionumber` int(11) NOT NULL auto_increment,
354 `frameworkcode` varchar(4) NOT NULL default '',
357 `unititle` mediumtext,
359 `serial` tinyint(1) default NULL,
360 `seriestitle` mediumtext,
361 `copyrightdate` smallint(6) default NULL,
362 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
363 `datecreated` DATE NOT NULL,
364 `abstract` mediumtext,
365 PRIMARY KEY (`biblionumber`),
366 KEY `blbnoidx` (`biblionumber`)
367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
370 -- Table structure for table `biblio_framework`
373 DROP TABLE IF EXISTS `biblio_framework`;
374 CREATE TABLE `biblio_framework` (
375 `frameworkcode` varchar(4) NOT NULL default '',
376 `frameworktext` varchar(255) NOT NULL default '',
377 PRIMARY KEY (`frameworkcode`)
378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
381 -- Table structure for table `biblioitems`
384 DROP TABLE IF EXISTS `biblioitems`;
385 CREATE TABLE `biblioitems` (
386 `biblioitemnumber` int(11) NOT NULL auto_increment,
387 `biblionumber` int(11) NOT NULL default 0,
390 `itemtype` varchar(10) default NULL,
391 `isbn` varchar(30) default NULL,
392 `issn` varchar(9) default NULL,
393 `publicationyear` text,
394 `publishercode` varchar(255) default NULL,
395 `volumedate` date default NULL,
397 `collectiontitle` mediumtext default NULL,
398 `collectionissn` text default NULL,
399 `collectionvolume` mediumtext default NULL,
400 `editionstatement` text default NULL,
401 `editionresponsibility` text default NULL,
402 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
403 `illus` varchar(255) default NULL,
404 `pages` varchar(255) default NULL,
406 `size` varchar(255) default NULL,
407 `place` varchar(255) default NULL,
408 `lccn` varchar(25) default NULL,
410 `url` varchar(255) default NULL,
411 `cn_source` varchar(10) default NULL,
412 `cn_class` varchar(30) default NULL,
413 `cn_item` varchar(10) default NULL,
414 `cn_suffix` varchar(10) default NULL,
415 `cn_sort` varchar(30) default NULL,
416 `totalissues` int(10),
417 `marcxml` longtext NOT NULL,
418 PRIMARY KEY (`biblioitemnumber`),
419 KEY `bibinoidx` (`biblioitemnumber`),
420 KEY `bibnoidx` (`biblionumber`),
423 KEY `publishercode` (`publishercode`),
424 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
425 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
428 -- Table structure for table `borrowers`
431 DROP TABLE IF EXISTS `borrowers`;
432 CREATE TABLE `borrowers` (
433 `borrowernumber` int(11) NOT NULL auto_increment,
434 `cardnumber` varchar(16) default NULL,
435 `surname` mediumtext NOT NULL,
438 `othernames` mediumtext,
440 `streetnumber` varchar(10) default NULL,
441 `streettype` varchar(50) default NULL,
442 `address` mediumtext NOT NULL,
444 `city` mediumtext NOT NULL,
445 `zipcode` varchar(25) default NULL,
448 `mobile` varchar(50) default NULL,
452 `B_streetnumber` varchar(10) default NULL,
453 `B_streettype` varchar(50) default NULL,
454 `B_address` varchar(100) default NULL,
456 `B_zipcode` varchar(25) default NULL,
458 `B_phone` mediumtext,
459 `dateofbirth` date default NULL,
460 `branchcode` varchar(10) NOT NULL default '',
461 `categorycode` varchar(10) NOT NULL default '',
462 `dateenrolled` date default NULL,
463 `dateexpiry` date default NULL,
464 `gonenoaddress` tinyint(1) default NULL,
465 `lost` tinyint(1) default NULL,
466 `debarred` tinyint(1) default NULL,
467 `contactname` mediumtext,
468 `contactfirstname` text,
470 `guarantorid` int(11) default NULL,
471 `borrowernotes` mediumtext,
472 `relationship` varchar(100) default NULL,
473 `ethnicity` varchar(50) default NULL,
474 `ethnotes` varchar(255) default NULL,
475 `sex` varchar(1) default NULL,
476 `password` varchar(30) default NULL,
477 `flags` int(11) default NULL,
478 `userid` varchar(30) default NULL,
479 `opacnote` mediumtext,
480 `contactnote` varchar(255) default NULL,
481 `sort1` varchar(80) default NULL,
482 `sort2` varchar(80) default NULL,
483 `altcontactfirstname` varchar(255) default NULL,
484 `altcontactsurname` varchar(255) default NULL,
485 `altcontactaddress1` varchar(255) default NULL,
486 `altcontactaddress2` varchar(255) default NULL,
487 `altcontactaddress3` varchar(255) default NULL,
488 `altcontactzipcode` varchar(50) default NULL,
489 `altcontactphone` varchar(50) default NULL,
490 `smsalertnumber` varchar(50) default NULL,
491 UNIQUE KEY `cardnumber` (`cardnumber`),
492 PRIMARY KEY `borrowernumber` (`borrowernumber`),
493 KEY `categorycode` (`categorycode`),
494 KEY `branchcode` (`branchcode`),
495 KEY `userid` (`userid`),
496 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
497 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
498 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
501 -- Table structure for table `borrower_attribute_types`
504 DROP TABLE IF EXISTS `borrower_attribute_types`;
505 CREATE TABLE `borrower_attribute_types` (
506 `code` varchar(10) NOT NULL,
507 `description` varchar(255) NOT NULL,
508 `repeatable` tinyint(1) NOT NULL default 0,
509 `unique_id` tinyint(1) NOT NULL default 0,
510 `opac_display` tinyint(1) NOT NULL default 0,
511 `password_allowed` tinyint(1) NOT NULL default 0,
512 `staff_searchable` tinyint(1) NOT NULL default 0,
513 `authorised_value_category` varchar(10) default NULL,
515 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
518 -- Table structure for table `borrower_attributes`
521 DROP TABLE IF EXISTS `borrower_attributes`;
522 CREATE TABLE `borrower_attributes` (
523 `borrowernumber` int(11) NOT NULL,
524 `code` varchar(10) NOT NULL,
525 `attribute` varchar(64) default NULL,
526 `password` varchar(64) default NULL,
527 KEY `borrowernumber` (`borrowernumber`),
528 KEY `code_attribute` (`code`, `attribute`),
529 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
530 ON DELETE CASCADE ON UPDATE CASCADE,
531 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
532 ON DELETE CASCADE ON UPDATE CASCADE
533 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
536 -- Table structure for table `branchcategories`
539 DROP TABLE IF EXISTS `branchcategories`;
540 CREATE TABLE `branchcategories` (
541 `categorycode` varchar(10) NOT NULL default '',
542 `categoryname` varchar(32),
543 `codedescription` mediumtext,
544 `categorytype` varchar(16),
545 PRIMARY KEY (`categorycode`)
546 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
549 -- Table structure for table `branches`
552 DROP TABLE IF EXISTS `branches`;
553 CREATE TABLE `branches` (
554 `branchcode` varchar(10) NOT NULL default '',
555 `branchname` mediumtext NOT NULL,
556 `branchaddress1` mediumtext,
557 `branchaddress2` mediumtext,
558 `branchaddress3` mediumtext,
559 `branchphone` mediumtext,
560 `branchfax` mediumtext,
561 `branchemail` mediumtext,
562 `issuing` tinyint(4) default NULL,
563 `branchip` varchar(15) default NULL,
564 `branchprinter` varchar(100) default NULL,
565 UNIQUE KEY `branchcode` (`branchcode`)
566 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
569 -- Table structure for table `branchrelations`
572 DROP TABLE IF EXISTS `branchrelations`;
573 CREATE TABLE `branchrelations` (
574 `branchcode` varchar(10) NOT NULL default '',
575 `categorycode` varchar(10) NOT NULL default '',
576 PRIMARY KEY (`branchcode`,`categorycode`),
577 KEY `branchcode` (`branchcode`),
578 KEY `categorycode` (`categorycode`),
579 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
580 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
581 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
584 -- Table structure for table `branchtransfers`
587 DROP TABLE IF EXISTS `branchtransfers`;
588 CREATE TABLE `branchtransfers` (
589 `itemnumber` int(11) NOT NULL default 0,
590 `datesent` datetime default NULL,
591 `frombranch` varchar(10) NOT NULL default '',
592 `datearrived` datetime default NULL,
593 `tobranch` varchar(10) NOT NULL default '',
594 `comments` mediumtext,
595 KEY `frombranch` (`frombranch`),
596 KEY `tobranch` (`tobranch`),
597 KEY `itemnumber` (`itemnumber`),
598 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
599 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
600 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
601 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
605 -- Table structure for table `browser`
607 DROP TABLE IF EXISTS `browser`;
608 CREATE TABLE `browser` (
609 `level` int(11) NOT NULL,
610 `classification` varchar(20) NOT NULL,
611 `description` varchar(255) NOT NULL,
612 `number` bigint(20) NOT NULL,
613 `endnode` tinyint(4) NOT NULL
614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
617 -- Table structure for table `categories`
620 DROP TABLE IF EXISTS `categories`;
621 CREATE TABLE `categories` (
622 `categorycode` varchar(10) NOT NULL default '',
623 `description` mediumtext,
624 `enrolmentperiod` smallint(6) default NULL,
625 `upperagelimit` smallint(6) default NULL,
626 `dateofbirthrequired` tinyint(1) default NULL,
627 `finetype` varchar(30) default NULL,
628 `bulk` tinyint(1) default NULL,
629 `enrolmentfee` decimal(28,6) default NULL,
630 `overduenoticerequired` tinyint(1) default NULL,
631 `issuelimit` smallint(6) default NULL,
632 `reservefee` decimal(28,6) default NULL,
633 `category_type` varchar(1) NOT NULL default 'A',
634 PRIMARY KEY (`categorycode`),
635 UNIQUE KEY `categorycode` (`categorycode`)
636 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
639 -- Table structure for table `borrower_branch_circ_rules`
642 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
643 CREATE TABLE `branch_borrower_circ_rules` (
644 `branchcode` VARCHAR(10) NOT NULL,
645 `categorycode` VARCHAR(10) NOT NULL,
646 `maxissueqty` int(4) default NULL,
647 PRIMARY KEY (`categorycode`, `branchcode`),
648 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
649 ON DELETE CASCADE ON UPDATE CASCADE,
650 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
651 ON DELETE CASCADE ON UPDATE CASCADE
652 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
655 -- Table structure for table `default_borrower_circ_rules`
658 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
659 CREATE TABLE `default_borrower_circ_rules` (
660 `categorycode` VARCHAR(10) NOT NULL,
661 `maxissueqty` int(4) default NULL,
662 PRIMARY KEY (`categorycode`),
663 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
664 ON DELETE CASCADE ON UPDATE CASCADE
665 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
668 -- Table structure for table `default_branch_circ_rules`
671 DROP TABLE IF EXISTS `default_branch_circ_rules`;
672 CREATE TABLE `default_branch_circ_rules` (
673 `branchcode` VARCHAR(10) NOT NULL,
674 `maxissueqty` int(4) default NULL,
675 PRIMARY KEY (`branchcode`),
676 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
677 ON DELETE CASCADE ON UPDATE CASCADE
678 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
681 -- Table structure for table `default_circ_rules`
684 DROP TABLE IF EXISTS `default_circ_rules`;
685 CREATE TABLE `default_circ_rules` (
686 `singleton` enum('singleton') NOT NULL default 'singleton',
687 `maxissueqty` int(4) default NULL,
688 PRIMARY KEY (`singleton`)
689 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
692 -- Table structure for table `cities`
695 DROP TABLE IF EXISTS `cities`;
696 CREATE TABLE `cities` (
697 `cityid` int(11) NOT NULL auto_increment,
698 `city_name` varchar(100) NOT NULL default '',
699 `city_zipcode` varchar(20) default NULL,
700 PRIMARY KEY (`cityid`)
701 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
704 -- Table structure for table `class_sort_rules`
707 DROP TABLE IF EXISTS `class_sort_rules`;
708 CREATE TABLE `class_sort_rules` (
709 `class_sort_rule` varchar(10) NOT NULL default '',
710 `description` mediumtext,
711 `sort_routine` varchar(30) NOT NULL default '',
712 PRIMARY KEY (`class_sort_rule`),
713 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
714 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
717 -- Table structure for table `class_sources`
720 DROP TABLE IF EXISTS `class_sources`;
721 CREATE TABLE `class_sources` (
722 `cn_source` varchar(10) NOT NULL default '',
723 `description` mediumtext,
724 `used` tinyint(4) NOT NULL default 0,
725 `class_sort_rule` varchar(10) NOT NULL default '',
726 PRIMARY KEY (`cn_source`),
727 UNIQUE KEY `cn_source_idx` (`cn_source`),
728 KEY `used_idx` (`used`),
729 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
733 -- Table structure for table `currency`
736 DROP TABLE IF EXISTS `currency`;
737 CREATE TABLE `currency` (
738 `currency` varchar(10) NOT NULL default '',
739 `symbol` varchar(5) default NULL,
740 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
741 `rate` float(7,5) default NULL,
742 PRIMARY KEY (`currency`)
743 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
746 -- Table structure for table `deletedbiblio`
749 DROP TABLE IF EXISTS `deletedbiblio`;
750 CREATE TABLE `deletedbiblio` (
751 `biblionumber` int(11) NOT NULL default 0,
752 `frameworkcode` varchar(4) NOT NULL default '',
755 `unititle` mediumtext,
757 `serial` tinyint(1) default NULL,
758 `seriestitle` mediumtext,
759 `copyrightdate` smallint(6) default NULL,
760 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
761 `datecreated` DATE NOT NULL,
762 `abstract` mediumtext,
763 PRIMARY KEY (`biblionumber`),
764 KEY `blbnoidx` (`biblionumber`)
765 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
768 -- Table structure for table `deletedbiblioitems`
771 DROP TABLE IF EXISTS `deletedbiblioitems`;
772 CREATE TABLE `deletedbiblioitems` (
773 `biblioitemnumber` int(11) NOT NULL default 0,
774 `biblionumber` int(11) NOT NULL default 0,
777 `itemtype` varchar(10) default NULL,
778 `isbn` varchar(30) default NULL,
779 `issn` varchar(9) default NULL,
780 `publicationyear` text,
781 `publishercode` varchar(255) default NULL,
782 `volumedate` date default NULL,
784 `collectiontitle` mediumtext default NULL,
785 `collectionissn` text default NULL,
786 `collectionvolume` mediumtext default NULL,
787 `editionstatement` text default NULL,
788 `editionresponsibility` text default NULL,
789 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
790 `illus` varchar(255) default NULL,
791 `pages` varchar(255) default NULL,
793 `size` varchar(255) default NULL,
794 `place` varchar(255) default NULL,
795 `lccn` varchar(25) default NULL,
797 `url` varchar(255) default NULL,
798 `cn_source` varchar(10) default NULL,
799 `cn_class` varchar(30) default NULL,
800 `cn_item` varchar(10) default NULL,
801 `cn_suffix` varchar(10) default NULL,
802 `cn_sort` varchar(30) default NULL,
803 `totalissues` int(10),
804 `marcxml` longtext NOT NULL,
805 PRIMARY KEY (`biblioitemnumber`),
806 KEY `bibinoidx` (`biblioitemnumber`),
807 KEY `bibnoidx` (`biblionumber`),
809 KEY `publishercode` (`publishercode`)
810 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
813 -- Table structure for table `deletedborrowers`
816 DROP TABLE IF EXISTS `deletedborrowers`;
817 CREATE TABLE `deletedborrowers` (
818 `borrowernumber` int(11) NOT NULL default 0,
819 `cardnumber` varchar(9) NOT NULL default '',
820 `surname` mediumtext NOT NULL,
823 `othernames` mediumtext,
825 `streetnumber` varchar(10) default NULL,
826 `streettype` varchar(50) default NULL,
827 `address` mediumtext NOT NULL,
829 `city` mediumtext NOT NULL,
830 `zipcode` varchar(25) default NULL,
833 `mobile` varchar(50) default NULL,
837 `B_streetnumber` varchar(10) default NULL,
838 `B_streettype` varchar(50) default NULL,
839 `smsalertnumber` varchar(50) default NULL,
840 `B_address` varchar(100) default NULL,
842 `B_zipcode` varchar(25) default NULL,
844 `B_phone` mediumtext,
845 `dateofbirth` date default NULL,
846 `branchcode` varchar(10) NOT NULL default '',
847 `categorycode` varchar(10) default NULL,
848 `dateenrolled` date default NULL,
849 `dateexpiry` date default NULL,
850 `gonenoaddress` tinyint(1) default NULL,
851 `lost` tinyint(1) default NULL,
852 `debarred` tinyint(1) default NULL,
853 `contactname` mediumtext,
854 `contactfirstname` text,
856 `guarantorid` int(11) default NULL,
857 `borrowernotes` mediumtext,
858 `relationship` varchar(100) default NULL,
859 `ethnicity` varchar(50) default NULL,
860 `ethnotes` varchar(255) default NULL,
861 `sex` varchar(1) default NULL,
862 `password` varchar(30) default NULL,
863 `flags` int(11) default NULL,
864 `userid` varchar(30) default NULL,
865 `opacnote` mediumtext,
866 `contactnote` varchar(255) default NULL,
867 `sort1` varchar(80) default NULL,
868 `sort2` varchar(80) default NULL,
869 `altcontactfirstname` varchar(255) default NULL,
870 `altcontactsurname` varchar(255) default NULL,
871 `altcontactaddress1` varchar(255) default NULL,
872 `altcontactaddress2` varchar(255) default NULL,
873 `altcontactaddress3` varchar(255) default NULL,
874 `altcontactzipcode` varchar(50) default NULL,
875 `altcontactphone` varchar(50) default NULL,
876 KEY `borrowernumber` (`borrowernumber`),
877 KEY `cardnumber` (`cardnumber`)
878 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
881 -- Table structure for table `deleteditems`
884 DROP TABLE IF EXISTS `deleteditems`;
885 CREATE TABLE `deleteditems` (
886 `itemnumber` int(11) NOT NULL default 0,
887 `biblionumber` int(11) NOT NULL default 0,
888 `biblioitemnumber` int(11) NOT NULL default 0,
889 `barcode` varchar(20) default NULL,
890 `dateaccessioned` date default NULL,
891 `booksellerid` mediumtext default NULL,
892 `homebranch` varchar(10) default NULL,
893 `price` decimal(8,2) default NULL,
894 `replacementprice` decimal(8,2) default NULL,
895 `replacementpricedate` date default NULL,
896 `datelastborrowed` date default NULL,
897 `datelastseen` date default NULL,
898 `stack` tinyint(1) default NULL,
899 `notforloan` tinyint(1) NOT NULL default 0,
900 `damaged` tinyint(1) NOT NULL default 0,
901 `itemlost` tinyint(1) NOT NULL default 0,
902 `wthdrawn` tinyint(1) NOT NULL default 0,
903 `itemcallnumber` varchar(30) default NULL,
904 `issues` smallint(6) default NULL,
905 `renewals` smallint(6) default NULL,
906 `reserves` smallint(6) default NULL,
907 `restricted` tinyint(1) default NULL,
908 `itemnotes` mediumtext,
909 `holdingbranch` varchar(10) default NULL,
910 `paidfor` mediumtext,
911 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
912 `location` varchar(80) default NULL,
913 `onloan` date default NULL,
914 `cn_source` varchar(10) default NULL,
915 `cn_sort` varchar(30) default NULL,
916 `ccode` varchar(10) default NULL,
917 `materials` varchar(10) default NULL,
918 `uri` varchar(255) default NULL,
919 `itype` varchar(10) default NULL,
920 `more_subfields_xml` longtext default NULL,
921 `enumchron` varchar(80) default NULL,
922 `copynumber` varchar(32) default NULL,
924 PRIMARY KEY (`itemnumber`),
925 KEY `delitembarcodeidx` (`barcode`),
926 KEY `delitembinoidx` (`biblioitemnumber`),
927 KEY `delitembibnoidx` (`biblionumber`),
928 KEY `delhomebranch` (`homebranch`),
929 KEY `delholdingbranch` (`holdingbranch`)
930 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
933 -- Table structure for table `ethnicity`
936 DROP TABLE IF EXISTS `ethnicity`;
937 CREATE TABLE `ethnicity` (
938 `code` varchar(10) NOT NULL default '',
939 `name` varchar(255) default NULL,
941 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
944 -- Table structure for table `import_batches`
947 DROP TABLE IF EXISTS `import_batches`;
948 CREATE TABLE `import_batches` (
949 `import_batch_id` int(11) NOT NULL auto_increment,
950 `matcher_id` int(11) default NULL,
951 `template_id` int(11) default NULL,
952 `branchcode` varchar(10) default NULL,
953 `num_biblios` int(11) NOT NULL default 0,
954 `num_items` int(11) NOT NULL default 0,
955 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
956 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
957 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
958 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
959 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
960 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
961 `file_name` varchar(100),
962 `comments` mediumtext,
963 PRIMARY KEY (`import_batch_id`),
964 KEY `branchcode` (`branchcode`)
965 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
968 -- Table structure for table `import_records`
971 DROP TABLE IF EXISTS `import_records`;
972 CREATE TABLE `import_records` (
973 `import_record_id` int(11) NOT NULL auto_increment,
974 `import_batch_id` int(11) NOT NULL,
975 `branchcode` varchar(10) default NULL,
976 `record_sequence` int(11) NOT NULL default 0,
977 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
978 `import_date` DATE default NULL,
979 `marc` longblob NOT NULL,
980 `marcxml` longtext NOT NULL,
981 `marcxml_old` longtext NOT NULL,
982 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
983 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
984 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
985 `import_error` mediumtext,
986 `encoding` varchar(40) NOT NULL default '',
987 `z3950random` varchar(40) default NULL,
988 PRIMARY KEY (`import_record_id`),
989 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
990 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
991 KEY `branchcode` (`branchcode`),
992 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
993 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
996 -- Table structure for `import_record_matches`
998 DROP TABLE IF EXISTS `import_record_matches`;
999 CREATE TABLE `import_record_matches` (
1000 `import_record_id` int(11) NOT NULL,
1001 `candidate_match_id` int(11) NOT NULL,
1002 `score` int(11) NOT NULL default 0,
1003 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1004 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1005 KEY `record_score` (`import_record_id`, `score`)
1006 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1009 -- Table structure for table `import_biblios`
1012 DROP TABLE IF EXISTS `import_biblios`;
1013 CREATE TABLE `import_biblios` (
1014 `import_record_id` int(11) NOT NULL,
1015 `matched_biblionumber` int(11) default NULL,
1016 `control_number` varchar(25) default NULL,
1017 `original_source` varchar(25) default NULL,
1018 `title` varchar(128) default NULL,
1019 `author` varchar(80) default NULL,
1020 `isbn` varchar(30) default NULL,
1021 `issn` varchar(9) default NULL,
1022 `has_items` tinyint(1) NOT NULL default 0,
1023 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1024 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1025 KEY `matched_biblionumber` (`matched_biblionumber`),
1026 KEY `title` (`title`),
1028 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1031 -- Table structure for table `import_items`
1034 DROP TABLE IF EXISTS `import_items`;
1035 CREATE TABLE `import_items` (
1036 `import_items_id` int(11) NOT NULL auto_increment,
1037 `import_record_id` int(11) NOT NULL,
1038 `itemnumber` int(11) default NULL,
1039 `branchcode` varchar(10) default NULL,
1040 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1041 `marcxml` longtext NOT NULL,
1042 `import_error` mediumtext,
1043 PRIMARY KEY (`import_items_id`),
1044 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1045 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1046 KEY `itemnumber` (`itemnumber`),
1047 KEY `branchcode` (`branchcode`)
1048 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1051 -- Table structure for table `issues`
1054 DROP TABLE IF EXISTS `issues`;
1055 CREATE TABLE `issues` (
1056 `borrowernumber` int(11) default NULL,
1057 `itemnumber` int(11) default NULL,
1058 `date_due` date default NULL,
1059 `branchcode` varchar(10) default NULL,
1060 `issuingbranch` varchar(18) default NULL,
1061 `returndate` date default NULL,
1062 `lastreneweddate` date default NULL,
1063 `return` varchar(4) default NULL,
1064 `renewals` tinyint(4) default NULL,
1065 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1066 `issuedate` date default NULL,
1067 KEY `issuesborridx` (`borrowernumber`),
1068 KEY `issuesitemidx` (`itemnumber`),
1069 KEY `bordate` (`borrowernumber`,`timestamp`),
1070 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1071 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1072 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1075 -- Table structure for table `issuingrules`
1078 DROP TABLE IF EXISTS `issuingrules`;
1079 CREATE TABLE `issuingrules` (
1080 `categorycode` varchar(10) NOT NULL default '',
1081 `itemtype` varchar(10) NOT NULL default '',
1082 `restrictedtype` tinyint(1) default NULL,
1083 `rentaldiscount` decimal(28,6) default NULL,
1084 `reservecharge` decimal(28,6) default NULL,
1085 `fine` decimal(28,6) default NULL,
1086 `finedays` int(11) default NULL,
1087 `firstremind` int(11) default NULL,
1088 `chargeperiod` int(11) default NULL,
1089 `accountsent` int(11) default NULL,
1090 `chargename` varchar(100) default NULL,
1091 `maxissueqty` int(4) default NULL,
1092 `issuelength` int(4) default NULL,
1093 `branchcode` varchar(10) NOT NULL default '',
1094 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1095 KEY `categorycode` (`categorycode`),
1096 KEY `itemtype` (`itemtype`)
1097 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1100 -- Table structure for table `items`
1103 DROP TABLE IF EXISTS `items`;
1104 CREATE TABLE `items` (
1105 `itemnumber` int(11) NOT NULL auto_increment,
1106 `biblionumber` int(11) NOT NULL default 0,
1107 `biblioitemnumber` int(11) NOT NULL default 0,
1108 `barcode` varchar(20) default NULL,
1109 `dateaccessioned` date default NULL,
1110 `booksellerid` mediumtext default NULL,
1111 `homebranch` varchar(10) default NULL,
1112 `price` decimal(8,2) default NULL,
1113 `replacementprice` decimal(8,2) default NULL,
1114 `replacementpricedate` date default NULL,
1115 `datelastborrowed` date default NULL,
1116 `datelastseen` date default NULL,
1117 `stack` tinyint(1) default NULL,
1118 `notforloan` tinyint(1) NOT NULL default 0,
1119 `damaged` tinyint(1) NOT NULL default 0,
1120 `itemlost` tinyint(1) NOT NULL default 0,
1121 `wthdrawn` tinyint(1) NOT NULL default 0,
1122 `itemcallnumber` varchar(30) default NULL,
1123 `issues` smallint(6) default NULL,
1124 `renewals` smallint(6) default NULL,
1125 `reserves` smallint(6) default NULL,
1126 `restricted` tinyint(1) default NULL,
1127 `itemnotes` mediumtext,
1128 `holdingbranch` varchar(10) default NULL,
1129 `paidfor` mediumtext,
1130 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1131 `location` varchar(80) default NULL,
1132 `onloan` date default NULL,
1133 `cn_source` varchar(10) default NULL,
1134 `cn_sort` varchar(30) default NULL,
1135 `ccode` varchar(10) default NULL,
1136 `materials` varchar(10) default NULL,
1137 `uri` varchar(255) default NULL,
1138 `itype` varchar(10) default NULL,
1139 `more_subfields_xml` longtext default NULL,
1140 `enumchron` varchar(80) default NULL,
1141 `copynumber` varchar(32) default NULL,
1142 PRIMARY KEY (`itemnumber`),
1143 UNIQUE KEY `itembarcodeidx` (`barcode`),
1144 KEY `itembinoidx` (`biblioitemnumber`),
1145 KEY `itembibnoidx` (`biblionumber`),
1146 KEY `homebranch` (`homebranch`),
1147 KEY `holdingbranch` (`holdingbranch`),
1148 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1149 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1150 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1154 -- Table structure for table `itemtypes`
1157 DROP TABLE IF EXISTS `itemtypes`;
1158 CREATE TABLE `itemtypes` (
1159 `itemtype` varchar(10) NOT NULL default '',
1160 `description` mediumtext,
1161 `renewalsallowed` smallint(6) default NULL,
1162 `rentalcharge` double(16,4) default NULL,
1163 `notforloan` smallint(6) default NULL,
1164 `imageurl` varchar(200) default NULL,
1166 PRIMARY KEY (`itemtype`),
1167 UNIQUE KEY `itemtype` (`itemtype`)
1168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1171 -- Table structure for table `labels`
1174 DROP TABLE IF EXISTS `labels`;
1175 CREATE TABLE `labels` (
1176 `labelid` int(11) NOT NULL auto_increment,
1177 `batch_id` varchar(10) NOT NULL default 1,
1178 `itemnumber` varchar(100) NOT NULL default '',
1179 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1180 PRIMARY KEY (`labelid`)
1181 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1184 -- Table structure for table `labels_conf`
1187 DROP TABLE IF EXISTS `labels_conf`;
1188 CREATE TABLE `labels_conf` (
1189 `id` int(4) NOT NULL auto_increment,
1190 `barcodetype` char(100) default '',
1191 `title` int(1) default '0',
1192 `subtitle` int(1) default '0',
1193 `itemtype` int(1) default '0',
1194 `barcode` int(1) default '0',
1195 `dewey` int(1) default '0',
1196 `classification` int(1) default NULL,
1197 `subclass` int(1) default '0',
1198 `itemcallnumber` int(1) default '0',
1199 `author` int(1) default '0',
1200 `issn` int(1) default '0',
1201 `isbn` int(1) default '0',
1202 `startlabel` int(2) NOT NULL default '1',
1203 `printingtype` char(32) default 'BAR',
1204 `formatstring` varchar(64) default NULL,
1205 `layoutname` char(20) NOT NULL default 'TEST',
1206 `guidebox` int(1) default '0',
1207 `active` tinyint(1) default '1',
1208 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1209 `ccode` char(4) collate utf8_unicode_ci default NULL,
1210 `callnum_split` int(1) default NULL,
1211 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1216 -- Table structure for table `labels_profile`
1219 DROP TABLE IF EXISTS `labels_profile`;
1220 CREATE TABLE `labels_profile` (
1221 `tmpl_id` int(4) NOT NULL,
1222 `prof_id` int(4) NOT NULL,
1223 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1224 UNIQUE KEY `prof_id` (`prof_id`)
1225 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1228 -- Table structure for table `labels_templates`
1231 DROP TABLE IF EXISTS `labels_templates`;
1232 CREATE TABLE `labels_templates` (
1233 `tmpl_id` int(4) NOT NULL auto_increment,
1234 `tmpl_code` char(100) default '',
1235 `tmpl_desc` char(100) default '',
1236 `page_width` float default '0',
1237 `page_height` float default '0',
1238 `label_width` float default '0',
1239 `label_height` float default '0',
1240 `topmargin` float default '0',
1241 `leftmargin` float default '0',
1242 `cols` int(2) default '0',
1243 `rows` int(2) default '0',
1244 `colgap` float default '0',
1245 `rowgap` float default '0',
1246 `active` int(1) default NULL,
1247 `units` char(20) default 'PX',
1248 `fontsize` int(4) NOT NULL default '3',
1249 `font` char(10) NOT NULL default 'TR',
1250 PRIMARY KEY (`tmpl_id`)
1251 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1254 -- Table structure for table `letter`
1257 DROP TABLE IF EXISTS `letter`;
1258 CREATE TABLE `letter` (
1259 `module` varchar(20) NOT NULL default '',
1260 `code` varchar(20) NOT NULL default '',
1261 `name` varchar(100) NOT NULL default '',
1262 `title` varchar(200) NOT NULL default '',
1264 PRIMARY KEY (`module`,`code`)
1265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1268 -- Table structure for table `marc_subfield_structure`
1271 DROP TABLE IF EXISTS `marc_subfield_structure`;
1272 CREATE TABLE `marc_subfield_structure` (
1273 `tagfield` varchar(3) NOT NULL default '',
1274 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1275 `liblibrarian` varchar(255) NOT NULL default '',
1276 `libopac` varchar(255) NOT NULL default '',
1277 `repeatable` tinyint(4) NOT NULL default 0,
1278 `mandatory` tinyint(4) NOT NULL default 0,
1279 `kohafield` varchar(40) default NULL,
1280 `tab` tinyint(1) default NULL,
1281 `authorised_value` varchar(20) default NULL,
1282 `authtypecode` varchar(20) default NULL,
1283 `value_builder` varchar(80) default NULL,
1284 `isurl` tinyint(1) default NULL,
1285 `hidden` tinyint(1) default NULL,
1286 `frameworkcode` varchar(4) NOT NULL default '',
1287 `seealso` varchar(1100) default NULL,
1288 `link` varchar(80) default NULL,
1289 `defaultvalue` text default NULL,
1290 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1291 KEY `kohafield_2` (`kohafield`),
1292 KEY `tab` (`frameworkcode`,`tab`),
1293 KEY `kohafield` (`frameworkcode`,`kohafield`)
1294 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1297 -- Table structure for table `marc_tag_structure`
1300 DROP TABLE IF EXISTS `marc_tag_structure`;
1301 CREATE TABLE `marc_tag_structure` (
1302 `tagfield` varchar(3) NOT NULL default '',
1303 `liblibrarian` varchar(255) NOT NULL default '',
1304 `libopac` varchar(255) NOT NULL default '',
1305 `repeatable` tinyint(4) NOT NULL default 0,
1306 `mandatory` tinyint(4) NOT NULL default 0,
1307 `authorised_value` varchar(10) default NULL,
1308 `frameworkcode` varchar(4) NOT NULL default '',
1309 PRIMARY KEY (`frameworkcode`,`tagfield`)
1310 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1313 -- Table structure for table `marc_matchers`
1316 DROP TABLE IF EXISTS `marc_matchers`;
1317 CREATE TABLE `marc_matchers` (
1318 `matcher_id` int(11) NOT NULL auto_increment,
1319 `code` varchar(10) NOT NULL default '',
1320 `description` varchar(255) NOT NULL default '',
1321 `record_type` varchar(10) NOT NULL default 'biblio',
1322 `threshold` int(11) NOT NULL default 0,
1323 PRIMARY KEY (`matcher_id`),
1324 KEY `code` (`code`),
1325 KEY `record_type` (`record_type`)
1326 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1329 -- Table structure for table `matchpoints`
1331 DROP TABLE IF EXISTS `matchpoints`;
1332 CREATE TABLE `matchpoints` (
1333 `matcher_id` int(11) NOT NULL,
1334 `matchpoint_id` int(11) NOT NULL auto_increment,
1335 `search_index` varchar(30) NOT NULL default '',
1336 `score` int(11) NOT NULL default 0,
1337 PRIMARY KEY (`matchpoint_id`),
1338 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1339 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1340 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1344 -- Table structure for table `matchpoint_components`
1346 DROP TABLE IF EXISTS `matchpoint_components`;
1347 CREATE TABLE `matchpoint_components` (
1348 `matchpoint_id` int(11) NOT NULL,
1349 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1350 sequence int(11) NOT NULL default 0,
1351 tag varchar(3) NOT NULL default '',
1352 subfields varchar(40) NOT NULL default '',
1353 offset int(4) NOT NULL default 0,
1354 length int(4) NOT NULL default 0,
1355 PRIMARY KEY (`matchpoint_component_id`),
1356 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1357 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1358 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1359 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1362 -- Table structure for table `matcher_component_norms`
1364 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1365 CREATE TABLE `matchpoint_component_norms` (
1366 `matchpoint_component_id` int(11) NOT NULL,
1367 `sequence` int(11) NOT NULL default 0,
1368 `norm_routine` varchar(50) NOT NULL default '',
1369 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1370 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1371 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1372 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1375 -- Table structure for table `matcher_matchpoints`
1377 DROP TABLE IF EXISTS `matcher_matchpoints`;
1378 CREATE TABLE `matcher_matchpoints` (
1379 `matcher_id` int(11) NOT NULL,
1380 `matchpoint_id` int(11) NOT NULL,
1381 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1382 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1383 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1384 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1388 -- Table structure for table `matchchecks`
1390 DROP TABLE IF EXISTS `matchchecks`;
1391 CREATE TABLE `matchchecks` (
1392 `matcher_id` int(11) NOT NULL,
1393 `matchcheck_id` int(11) NOT NULL auto_increment,
1394 `source_matchpoint_id` int(11) NOT NULL,
1395 `target_matchpoint_id` int(11) NOT NULL,
1396 PRIMARY KEY (`matchcheck_id`),
1397 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1398 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1399 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1400 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1401 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1402 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1403 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1406 -- Table structure for table `notifys`
1409 DROP TABLE IF EXISTS `notifys`;
1410 CREATE TABLE `notifys` (
1411 `notify_id` int(11) NOT NULL default 0,
1412 `borrowernumber` int(11) NOT NULL default 0,
1413 `itemnumber` int(11) NOT NULL default 0,
1414 `notify_date` date default NULL,
1415 `notify_send_date` date default NULL,
1416 `notify_level` int(1) NOT NULL default 0,
1417 `method` varchar(20) NOT NULL default ''
1418 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1421 -- Table structure for table `nozebra`
1424 DROP TABLE IF EXISTS `nozebra`;
1425 CREATE TABLE `nozebra` (
1426 `server` varchar(20) NOT NULL,
1427 `indexname` varchar(40) NOT NULL,
1428 `value` varchar(250) NOT NULL,
1429 `biblionumbers` longtext NOT NULL,
1430 KEY `indexname` (`server`,`indexname`),
1431 KEY `value` (`server`,`value`))
1432 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1435 -- Table structure for table `old_issues`
1438 DROP TABLE IF EXISTS `old_issues`;
1439 CREATE TABLE `old_issues` (
1440 `borrowernumber` int(11) default NULL,
1441 `itemnumber` int(11) default NULL,
1442 `date_due` date default NULL,
1443 `branchcode` varchar(10) default NULL,
1444 `issuingbranch` varchar(18) default NULL,
1445 `returndate` date default NULL,
1446 `lastreneweddate` date default NULL,
1447 `return` varchar(4) default NULL,
1448 `renewals` tinyint(4) default NULL,
1449 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1450 `issuedate` date default NULL,
1451 KEY `old_issuesborridx` (`borrowernumber`),
1452 KEY `old_issuesitemidx` (`itemnumber`),
1453 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1454 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1455 ON DELETE SET NULL ON UPDATE SET NULL,
1456 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1457 ON DELETE SET NULL ON UPDATE SET NULL
1458 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1461 -- Table structure for table `old_reserves`
1463 DROP TABLE IF EXISTS `old_reserves`;
1464 CREATE TABLE `old_reserves` (
1465 `borrowernumber` int(11) default NULL,
1466 `reservedate` date default NULL,
1467 `biblionumber` int(11) default NULL,
1468 `constrainttype` varchar(1) default NULL,
1469 `branchcode` varchar(10) default NULL,
1470 `notificationdate` date default NULL,
1471 `reminderdate` date default NULL,
1472 `cancellationdate` date default NULL,
1473 `reservenotes` mediumtext,
1474 `priority` smallint(6) default NULL,
1475 `found` varchar(1) default NULL,
1476 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1477 `itemnumber` int(11) default NULL,
1478 `waitingdate` date default NULL,
1479 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1480 KEY `old_reserves_biblionumber` (`biblionumber`),
1481 KEY `old_reserves_itemnumber` (`itemnumber`),
1482 KEY `old_reserves_branchcode` (`branchcode`),
1483 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1484 ON DELETE SET NULL ON UPDATE SET NULL,
1485 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1486 ON DELETE SET NULL ON UPDATE SET NULL,
1487 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1488 ON DELETE SET NULL ON UPDATE SET NULL
1489 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1492 -- Table structure for table `opac_news`
1495 DROP TABLE IF EXISTS `opac_news`;
1496 CREATE TABLE `opac_news` (
1497 `idnew` int(10) unsigned NOT NULL auto_increment,
1498 `title` varchar(250) NOT NULL default '',
1499 `new` text NOT NULL,
1500 `lang` varchar(25) NOT NULL default '',
1501 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1502 `expirationdate` date default NULL,
1503 `number` int(11) default NULL,
1504 PRIMARY KEY (`idnew`)
1505 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1508 -- Table structure for table `overduerules`
1511 DROP TABLE IF EXISTS `overduerules`;
1512 CREATE TABLE `overduerules` (
1513 `branchcode` varchar(10) NOT NULL default '',
1514 `categorycode` varchar(10) NOT NULL default '',
1515 `delay1` int(4) default 0,
1516 `letter1` varchar(20) default NULL,
1517 `debarred1` varchar(1) default 0,
1518 `delay2` int(4) default 0,
1519 `debarred2` varchar(1) default 0,
1520 `letter2` varchar(20) default NULL,
1521 `delay3` int(4) default 0,
1522 `letter3` varchar(20) default NULL,
1523 `debarred3` int(1) default 0,
1524 PRIMARY KEY (`branchcode`,`categorycode`)
1525 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1528 -- Table structure for table `patroncards`
1531 DROP TABLE IF EXISTS `patroncards`;
1532 CREATE TABLE `patroncards` (
1533 `cardid` int(11) NOT NULL auto_increment,
1534 `batch_id` varchar(10) NOT NULL default '1',
1535 `borrowernumber` int(11) NOT NULL,
1536 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1537 PRIMARY KEY (`cardid`),
1538 KEY `patroncards_ibfk_1` (`borrowernumber`),
1539 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1540 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1543 -- Table structure for table `patronimage`
1546 DROP TABLE IF EXISTS `patronimage`;
1547 CREATE TABLE `patronimage` (
1548 `cardnumber` varchar(16) NOT NULL,
1549 `mimetype` varchar(15) NOT NULL,
1550 `imagefile` mediumblob NOT NULL,
1551 PRIMARY KEY (`cardnumber`),
1552 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1553 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1556 -- Table structure for table `printers`
1559 DROP TABLE IF EXISTS `printers`;
1560 CREATE TABLE `printers` (
1561 `printername` varchar(40) NOT NULL default '',
1562 `printqueue` varchar(20) default NULL,
1563 `printtype` varchar(20) default NULL,
1564 PRIMARY KEY (`printername`)
1565 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1568 -- Table structure for table `printers_profile`
1571 DROP TABLE IF EXISTS `printers_profile`;
1572 CREATE TABLE `printers_profile` (
1573 `prof_id` int(4) NOT NULL auto_increment,
1574 `printername` varchar(40) NOT NULL,
1575 `tmpl_id` int(4) NOT NULL,
1576 `paper_bin` varchar(20) NOT NULL,
1577 `offset_horz` float default NULL,
1578 `offset_vert` float default NULL,
1579 `creep_horz` float default NULL,
1580 `creep_vert` float default NULL,
1581 `unit` char(20) NOT NULL default 'POINT',
1582 PRIMARY KEY (`prof_id`),
1583 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1584 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1585 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1588 -- Table structure for table `repeatable_holidays`
1591 DROP TABLE IF EXISTS `repeatable_holidays`;
1592 CREATE TABLE `repeatable_holidays` (
1593 `id` int(11) NOT NULL auto_increment,
1594 `branchcode` varchar(10) NOT NULL default '',
1595 `weekday` smallint(6) default NULL,
1596 `day` smallint(6) default NULL,
1597 `month` smallint(6) default NULL,
1598 `title` varchar(50) NOT NULL default '',
1599 `description` text NOT NULL,
1601 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1604 -- Table structure for table `reports_dictionary`
1607 DROP TABLE IF EXISTS `reports_dictionary`;
1608 CREATE TABLE reports_dictionary (
1609 `id` int(11) NOT NULL auto_increment,
1610 `name` varchar(255) default NULL,
1612 `date_created` datetime default NULL,
1613 `date_modified` datetime default NULL,
1615 `area` int(11) default NULL,
1617 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1620 -- Table structure for table `reserveconstraints`
1623 DROP TABLE IF EXISTS `reserveconstraints`;
1624 CREATE TABLE `reserveconstraints` (
1625 `borrowernumber` int(11) NOT NULL default 0,
1626 `reservedate` date default NULL,
1627 `biblionumber` int(11) NOT NULL default 0,
1628 `biblioitemnumber` int(11) default NULL,
1629 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1630 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1633 -- Table structure for table `reserves`
1636 DROP TABLE IF EXISTS `reserves`;
1637 CREATE TABLE `reserves` (
1638 `borrowernumber` int(11) NOT NULL default 0,
1639 `reservedate` date default NULL,
1640 `biblionumber` int(11) NOT NULL default 0,
1641 `constrainttype` varchar(1) default NULL,
1642 `branchcode` varchar(10) default NULL,
1643 `notificationdate` date default NULL,
1644 `reminderdate` date default NULL,
1645 `cancellationdate` date default NULL,
1646 `reservenotes` mediumtext,
1647 `priority` smallint(6) default NULL,
1648 `found` varchar(1) default NULL,
1649 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1650 `itemnumber` int(11) default NULL,
1651 `waitingdate` date default NULL,
1652 KEY `borrowernumber` (`borrowernumber`),
1653 KEY `biblionumber` (`biblionumber`),
1654 KEY `itemnumber` (`itemnumber`),
1655 KEY `branchcode` (`branchcode`),
1656 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1657 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1658 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1659 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1660 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1663 -- Table structure for table `reviews`
1666 DROP TABLE IF EXISTS `reviews`;
1667 CREATE TABLE `reviews` (
1668 `reviewid` int(11) NOT NULL auto_increment,
1669 `borrowernumber` int(11) default NULL,
1670 `biblionumber` int(11) default NULL,
1672 `approved` tinyint(4) default NULL,
1673 `datereviewed` datetime default NULL,
1674 PRIMARY KEY (`reviewid`)
1675 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1678 -- Table structure for table `roadtype`
1681 DROP TABLE IF EXISTS `roadtype`;
1682 CREATE TABLE `roadtype` (
1683 `roadtypeid` int(11) NOT NULL auto_increment,
1684 `road_type` varchar(100) NOT NULL default '',
1685 PRIMARY KEY (`roadtypeid`)
1686 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1689 -- Table structure for table `saved_sql`
1692 DROP TABLE IF EXISTS `saved_sql`;
1693 CREATE TABLE saved_sql (
1694 `id` int(11) NOT NULL auto_increment,
1695 `borrowernumber` int(11) default NULL,
1696 `date_created` datetime default NULL,
1697 `last_modified` datetime default NULL,
1699 `last_run` datetime default NULL,
1700 `report_name` varchar(255) default NULL,
1701 `type` varchar(255) default NULL,
1704 KEY boridx (`borrowernumber`)
1705 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1709 -- Table structure for `saved_reports`
1712 DROP TABLE IF EXISTS `saved_reports`;
1713 CREATE TABLE saved_reports (
1714 `id` int(11) NOT NULL auto_increment,
1715 `report_id` int(11) default NULL,
1717 `date_run` datetime default NULL,
1719 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1723 -- Table structure for table `serial`
1726 DROP TABLE IF EXISTS `serial`;
1727 CREATE TABLE `serial` (
1728 `serialid` int(11) NOT NULL auto_increment,
1729 `biblionumber` varchar(100) NOT NULL default '',
1730 `subscriptionid` varchar(100) NOT NULL default '',
1731 `serialseq` varchar(100) NOT NULL default '',
1732 `status` tinyint(4) NOT NULL default 0,
1733 `planneddate` date default NULL,
1735 `publisheddate` date default NULL,
1736 `itemnumber` text default NULL,
1737 `claimdate` date default NULL,
1738 `routingnotes` text,
1739 PRIMARY KEY (`serialid`)
1740 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1743 -- Table structure for table `sessions`
1746 DROP TABLE IF EXISTS sessions;
1747 CREATE TABLE sessions (
1748 `id` varchar(32) NOT NULL,
1749 `a_session` text NOT NULL,
1751 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1754 -- Table structure for table `special_holidays`
1757 DROP TABLE IF EXISTS `special_holidays`;
1758 CREATE TABLE `special_holidays` (
1759 `id` int(11) NOT NULL auto_increment,
1760 `branchcode` varchar(10) NOT NULL default '',
1761 `day` smallint(6) NOT NULL default 0,
1762 `month` smallint(6) NOT NULL default 0,
1763 `year` smallint(6) NOT NULL default 0,
1764 `isexception` smallint(1) NOT NULL default 1,
1765 `title` varchar(50) NOT NULL default '',
1766 `description` text NOT NULL,
1768 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1771 -- Table structure for table `statistics`
1774 DROP TABLE IF EXISTS `statistics`;
1775 CREATE TABLE `statistics` (
1776 `datetime` datetime default NULL,
1777 `branch` varchar(10) default NULL,
1778 `proccode` varchar(4) default NULL,
1779 `value` double(16,4) default NULL,
1780 `type` varchar(16) default NULL,
1782 `usercode` varchar(10) default NULL,
1783 `itemnumber` int(11) default NULL,
1784 `itemtype` varchar(10) default NULL,
1785 `borrowernumber` int(11) default NULL,
1786 `associatedborrower` int(11) default NULL,
1787 KEY `timeidx` (`datetime`)
1788 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1791 -- Table structure for table `stopwords`
1794 DROP TABLE IF EXISTS `stopwords`;
1795 CREATE TABLE `stopwords` (
1796 `word` varchar(255) default NULL
1797 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1800 -- Table structure for table `subscription`
1803 DROP TABLE IF EXISTS `subscription`;
1804 CREATE TABLE `subscription` (
1805 `biblionumber` int(11) NOT NULL default 0,
1806 `subscriptionid` int(11) NOT NULL auto_increment,
1807 `librarian` varchar(100) default '',
1808 `startdate` date default NULL,
1809 `aqbooksellerid` int(11) default 0,
1810 `cost` int(11) default 0,
1811 `aqbudgetid` int(11) default 0,
1812 `weeklength` int(11) default 0,
1813 `monthlength` int(11) default 0,
1814 `numberlength` int(11) default 0,
1815 `periodicity` tinyint(4) default 0,
1816 `dow` varchar(100) default '',
1817 `numberingmethod` varchar(100) default '',
1819 `status` varchar(100) NOT NULL default '',
1820 `add1` int(11) default 0,
1821 `every1` int(11) default 0,
1822 `whenmorethan1` int(11) default 0,
1823 `setto1` int(11) default NULL,
1824 `lastvalue1` int(11) default NULL,
1825 `add2` int(11) default 0,
1826 `every2` int(11) default 0,
1827 `whenmorethan2` int(11) default 0,
1828 `setto2` int(11) default NULL,
1829 `lastvalue2` int(11) default NULL,
1830 `add3` int(11) default 0,
1831 `every3` int(11) default 0,
1832 `innerloop1` int(11) default 0,
1833 `innerloop2` int(11) default 0,
1834 `innerloop3` int(11) default 0,
1835 `whenmorethan3` int(11) default 0,
1836 `setto3` int(11) default NULL,
1837 `lastvalue3` int(11) default NULL,
1838 `issuesatonce` tinyint(3) NOT NULL default 1,
1839 `firstacquidate` date default NULL,
1840 `manualhistory` tinyint(1) NOT NULL default 0,
1841 `irregularity` text,
1842 `letter` varchar(20) default NULL,
1843 `numberpattern` tinyint(3) default 0,
1844 `distributedto` text,
1845 `internalnotes` longtext,
1847 `branchcode` varchar(10) NOT NULL default '',
1848 `hemisphere` tinyint(3) default 0,
1849 `lastbranch` varchar(10),
1850 `serialsadditems` tinyint(1) NOT NULL default '0',
1851 PRIMARY KEY (`subscriptionid`)
1852 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1855 -- Table structure for table `subscriptionhistory`
1858 DROP TABLE IF EXISTS `subscriptionhistory`;
1859 CREATE TABLE `subscriptionhistory` (
1860 `biblionumber` int(11) NOT NULL default 0,
1861 `subscriptionid` int(11) NOT NULL default 0,
1862 `histstartdate` date default NULL,
1863 `enddate` date default NULL,
1864 `missinglist` longtext NOT NULL,
1865 `recievedlist` longtext NOT NULL,
1866 `opacnote` varchar(150) NOT NULL default '',
1867 `librariannote` varchar(150) NOT NULL default '',
1868 PRIMARY KEY (`subscriptionid`),
1869 KEY `biblionumber` (`biblionumber`)
1870 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1873 -- Table structure for table `subscriptionroutinglist`
1876 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1877 CREATE TABLE `subscriptionroutinglist` (
1878 `routingid` int(11) NOT NULL auto_increment,
1879 `borrowernumber` int(11) default NULL,
1880 `ranking` int(11) default NULL,
1881 `subscriptionid` int(11) default NULL,
1882 PRIMARY KEY (`routingid`)
1883 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1886 -- Table structure for table `suggestions`
1889 DROP TABLE IF EXISTS `suggestions`;
1890 CREATE TABLE `suggestions` (
1891 `suggestionid` int(8) NOT NULL auto_increment,
1892 `suggestedby` int(11) NOT NULL default 0,
1893 `managedby` int(11) default NULL,
1894 `STATUS` varchar(10) NOT NULL default '',
1896 `author` varchar(80) default NULL,
1897 `title` varchar(80) default NULL,
1898 `copyrightdate` smallint(6) default NULL,
1899 `publishercode` varchar(255) default NULL,
1900 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1901 `volumedesc` varchar(255) default NULL,
1902 `publicationyear` smallint(6) default 0,
1903 `place` varchar(255) default NULL,
1904 `isbn` varchar(30) default NULL,
1905 `mailoverseeing` smallint(1) default 0,
1906 `biblionumber` int(11) default NULL,
1908 PRIMARY KEY (`suggestionid`),
1909 KEY `suggestedby` (`suggestedby`),
1910 KEY `managedby` (`managedby`)
1911 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1914 -- Table structure for table `systempreferences`
1917 DROP TABLE IF EXISTS `systempreferences`;
1918 CREATE TABLE `systempreferences` (
1919 `variable` varchar(50) NOT NULL default '',
1921 `options` mediumtext,
1923 `type` varchar(20) default NULL,
1924 PRIMARY KEY (`variable`)
1925 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1928 -- Table structure for table `tags`
1931 DROP TABLE IF EXISTS `tags`;
1932 CREATE TABLE `tags` (
1933 `entry` varchar(255) NOT NULL default '',
1934 `weight` bigint(20) NOT NULL default 0,
1935 PRIMARY KEY (`entry`)
1936 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1939 -- Table structure for table `tags_all`
1942 DROP TABLE IF EXISTS `tags_all`;
1943 CREATE TABLE `tags_all` (
1944 `tag_id` int(11) NOT NULL auto_increment,
1945 `borrowernumber` int(11) NOT NULL,
1946 `biblionumber` int(11) NOT NULL,
1947 `term` varchar(255) NOT NULL,
1948 `language` int(4) default NULL,
1949 `date_created` datetime NOT NULL,
1950 PRIMARY KEY (`tag_id`),
1951 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1952 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1953 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1954 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1955 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1956 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1957 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1960 -- Table structure for table `tags_approval`
1963 DROP TABLE IF EXISTS `tags_approval`;
1964 CREATE TABLE `tags_approval` (
1965 `term` varchar(255) NOT NULL,
1966 `approved` int(1) NOT NULL default '0',
1967 `date_approved` datetime default NULL,
1968 `approved_by` int(11) default NULL,
1969 `weight_total` int(9) NOT NULL default '1',
1970 PRIMARY KEY (`term`),
1971 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1972 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1973 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1974 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1977 -- Table structure for table `tags_index`
1980 DROP TABLE IF EXISTS `tags_index`;
1981 CREATE TABLE `tags_index` (
1982 `term` varchar(255) NOT NULL,
1983 `biblionumber` int(11) NOT NULL,
1984 `weight` int(9) NOT NULL default '1',
1985 PRIMARY KEY (`term`,`biblionumber`),
1986 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1987 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1988 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1989 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1990 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1991 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1994 -- Table structure for table `userflags`
1997 DROP TABLE IF EXISTS `userflags`;
1998 CREATE TABLE `userflags` (
1999 `bit` int(11) NOT NULL default 0,
2000 `flag` varchar(30) default NULL,
2001 `flagdesc` varchar(255) default NULL,
2002 `defaulton` int(11) default NULL,
2004 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2007 -- Table structure for table `virtualshelves`
2010 DROP TABLE IF EXISTS `virtualshelves`;
2011 CREATE TABLE `virtualshelves` (
2012 `shelfnumber` int(11) NOT NULL auto_increment,
2013 `shelfname` varchar(255) default NULL,
2014 `owner` varchar(80) default NULL,
2015 `category` varchar(1) default NULL,
2016 `sortfield` varchar(16) default NULL,
2017 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2018 PRIMARY KEY (`shelfnumber`)
2019 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2022 -- Table structure for table `virtualshelfcontents`
2025 DROP TABLE IF EXISTS `virtualshelfcontents`;
2026 CREATE TABLE `virtualshelfcontents` (
2027 `shelfnumber` int(11) NOT NULL default 0,
2028 `biblionumber` int(11) NOT NULL default 0,
2029 `flags` int(11) default NULL,
2030 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2031 KEY `shelfnumber` (`shelfnumber`),
2032 KEY `biblionumber` (`biblionumber`),
2033 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2034 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2035 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2038 -- Table structure for table `z3950servers`
2041 DROP TABLE IF EXISTS `z3950servers`;
2042 CREATE TABLE `z3950servers` (
2043 `host` varchar(255) default NULL,
2044 `port` int(11) default NULL,
2045 `db` varchar(255) default NULL,
2046 `userid` varchar(255) default NULL,
2047 `password` varchar(255) default NULL,
2049 `id` int(11) NOT NULL auto_increment,
2050 `checked` smallint(6) default NULL,
2051 `rank` int(11) default NULL,
2052 `syntax` varchar(80) default NULL,
2054 `position` enum('primary','secondary','') NOT NULL default 'primary',
2055 `type` enum('zed','opensearch') NOT NULL default 'zed',
2056 `encoding` text default NULL,
2057 `description` text NOT NULL,
2059 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2062 -- Table structure for table `zebraqueue`
2065 DROP TABLE IF EXISTS `zebraqueue`;
2066 CREATE TABLE `zebraqueue` (
2067 `id` int(11) NOT NULL auto_increment,
2068 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2069 `operation` char(20) NOT NULL default '',
2070 `server` char(20) NOT NULL default '',
2071 `done` int(11) NOT NULL default '0',
2072 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2074 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2075 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2077 DROP TABLE IF EXISTS `services_throttle`;
2078 CREATE TABLE `services_throttle` (
2079 `service_type` varchar(10) NOT NULL default '',
2080 `service_count` varchar(45) default NULL,
2081 PRIMARY KEY (`service_type`)
2082 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2084 -- http://www.w3.org/International/articles/language-tags/
2087 DROP TABLE IF EXISTS language_subtag_registry;
2088 CREATE TABLE language_subtag_registry (
2090 type varchar(25), -- language-script-region-variant-extension-privateuse
2091 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2093 KEY `subtag` (`subtag`)
2094 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2096 -- TODO: add suppress_scripts
2097 -- this maps three letter codes defined in iso639.2 back to their
2098 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2099 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2100 CREATE TABLE language_rfc4646_to_iso639 (
2101 rfc4646_subtag varchar(25),
2102 iso639_2_code varchar(25),
2103 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2104 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2106 DROP TABLE IF EXISTS language_descriptions;
2107 CREATE TABLE language_descriptions (
2111 description varchar(255),
2113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2115 -- bi-directional support, keyed by script subcode
2116 DROP TABLE IF EXISTS language_script_bidi;
2117 CREATE TABLE language_script_bidi (
2118 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2119 bidi varchar(3), -- rtl ltr
2120 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2123 -- TODO: need to map language subtags to script subtags for detection
2124 -- of bidi when script is not specified (like ar, he)
2125 DROP TABLE IF EXISTS language_script_mapping;
2126 CREATE TABLE language_script_mapping (
2127 language_subtag varchar(25),
2128 script_subtag varchar(25),
2129 KEY `language_subtag` (`language_subtag`)
2130 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2132 DROP TABLE IF EXISTS `permissions`;
2133 CREATE TABLE `permissions` (
2134 `module_bit` int(11) NOT NULL DEFAULT 0,
2135 `code` varchar(30) DEFAULT NULL,
2136 `description` varchar(255) DEFAULT NULL,
2137 PRIMARY KEY (`module_bit`, `code`),
2138 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2139 ON DELETE CASCADE ON UPDATE CASCADE
2140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2142 DROP TABLE IF EXISTS `serialitems`;
2143 CREATE TABLE `serialitems` (
2144 `itemnumber` int(11) NOT NULL,
2145 `serialid` int(11) NOT NULL,
2146 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2147 KEY `serialitems_sfk_1` (`serialid`),
2148 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2149 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2151 DROP TABLE IF EXISTS `user_permissions`;
2152 CREATE TABLE `user_permissions` (
2153 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2154 `module_bit` int(11) NOT NULL DEFAULT 0,
2155 `code` varchar(30) DEFAULT NULL,
2156 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2157 ON DELETE CASCADE ON UPDATE CASCADE,
2158 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2159 ON DELETE CASCADE ON UPDATE CASCADE
2160 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2163 -- Table structure for table `tmp_holdsqueue`
2166 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2167 CREATE TABLE `tmp_holdsqueue` (
2168 `biblionumber` int(11) default NULL,
2169 `itemnumber` int(11) default NULL,
2170 `barcode` varchar(20) default NULL,
2171 `surname` mediumtext NOT NULL,
2174 `borrowernumber` int(11) NOT NULL,
2175 `cardnumber` varchar(16) default NULL,
2176 `reservedate` date default NULL,
2178 `itemcallnumber` varchar(30) default NULL,
2179 `holdingbranch` varchar(10) default NULL,
2180 `pickbranch` varchar(10) default NULL,
2182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2185 -- Table structure for table `message_queue`
2188 DROP TABLE IF EXISTS `message_queue`;
2189 CREATE TABLE `message_queue` (
2190 `message_id` int(11) NOT NULL auto_increment,
2191 `borrowernumber` int(11) default NULL,
2194 `message_transport_type` varchar(20) NOT NULL,
2195 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2196 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2197 `to_address` mediumtext,
2198 `from_address` mediumtext,
2199 `content_type` text,
2200 KEY `message_id` (`message_id`),
2201 KEY `borrowernumber` (`borrowernumber`),
2202 KEY `message_transport_type` (`message_transport_type`),
2203 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2204 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2208 -- Table structure for table `message_transport_types`
2211 DROP TABLE IF EXISTS `message_transport_types`;
2212 CREATE TABLE `message_transport_types` (
2213 `message_transport_type` varchar(20) NOT NULL,
2214 PRIMARY KEY (`message_transport_type`)
2215 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2218 -- Table structure for table `message_attributes`
2221 DROP TABLE IF EXISTS `message_attributes`;
2222 CREATE TABLE `message_attributes` (
2223 `message_attribute_id` int(11) NOT NULL auto_increment,
2224 `message_name` varchar(20) NOT NULL default '',
2225 `takes_days` tinyint(1) NOT NULL default '0',
2226 PRIMARY KEY (`message_attribute_id`),
2227 UNIQUE KEY `message_name` (`message_name`)
2228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2231 -- Table structure for table `message_transports`
2234 DROP TABLE IF EXISTS `message_transports`;
2235 CREATE TABLE `message_transports` (
2236 `message_attribute_id` int(11) NOT NULL,
2237 `message_transport_type` varchar(20) NOT NULL,
2238 `is_digest` tinyint(1) NOT NULL default '0',
2239 `letter_module` varchar(20) NOT NULL default '',
2240 `letter_code` varchar(20) NOT NULL default '',
2241 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2242 KEY `message_transport_type` (`message_transport_type`),
2243 KEY `letter_module` (`letter_module`,`letter_code`),
2244 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2245 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2246 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2247 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2250 -- Table structure for table `borrower_message_preferences`
2253 DROP TABLE IF EXISTS `borrower_message_preferences`;
2254 CREATE TABLE `borrower_message_preferences` (
2255 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2256 `borrowernumber` int(11) NOT NULL default '0',
2257 `message_attribute_id` int(11) default '0',
2258 `days_in_advance` int(11) default '0',
2259 `wants_digest` tinyint(1) NOT NULL default '0',
2260 PRIMARY KEY (`borrower_message_preference_id`),
2261 KEY `borrowernumber` (`borrowernumber`),
2262 KEY `message_attribute_id` (`message_attribute_id`),
2263 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2264 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
2265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2268 -- Table structure for table `borrower_message_transport_preferences`
2271 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2272 CREATE TABLE `borrower_message_transport_preferences` (
2273 `borrower_message_preference_id` int(11) NOT NULL default '0',
2274 `message_transport_type` varchar(20) NOT NULL default '0',
2275 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2276 KEY `message_transport_type` (`message_transport_type`),
2277 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,
2278 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
2279 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2281 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2282 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2283 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2284 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2285 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2286 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2287 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2288 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;