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`)
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(14) 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`),
422 KEY `publishercode` (`publishercode`),
423 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
424 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
427 -- Table structure for table `borrowers`
430 DROP TABLE IF EXISTS `borrowers`;
431 CREATE TABLE `borrowers` (
432 `borrowernumber` int(11) NOT NULL auto_increment,
433 `cardnumber` varchar(16) default NULL,
434 `surname` mediumtext NOT NULL,
437 `othernames` mediumtext,
439 `streetnumber` varchar(10) default NULL,
440 `streettype` varchar(50) default NULL,
441 `address` mediumtext NOT NULL,
443 `city` mediumtext NOT NULL,
444 `zipcode` varchar(25) default NULL,
447 `mobile` varchar(50) default NULL,
451 `B_streetnumber` varchar(10) default NULL,
452 `B_streettype` varchar(50) default NULL,
453 `B_address` varchar(100) default NULL,
455 `B_zipcode` varchar(25) default NULL,
457 `B_phone` mediumtext,
458 `dateofbirth` date default NULL,
459 `branchcode` varchar(10) NOT NULL default '',
460 `categorycode` varchar(10) NOT NULL default '',
461 `dateenrolled` date default NULL,
462 `dateexpiry` date default NULL,
463 `gonenoaddress` tinyint(1) default NULL,
464 `lost` tinyint(1) default NULL,
465 `debarred` tinyint(1) default NULL,
466 `contactname` mediumtext,
467 `contactfirstname` text,
469 `guarantorid` int(11) default NULL,
470 `borrowernotes` mediumtext,
471 `relationship` varchar(100) default NULL,
472 `ethnicity` varchar(50) default NULL,
473 `ethnotes` varchar(255) default NULL,
474 `sex` varchar(1) default NULL,
475 `password` varchar(30) default NULL,
476 `flags` int(11) default NULL,
477 `userid` varchar(30) default NULL,
478 `opacnote` mediumtext,
479 `contactnote` varchar(255) default NULL,
480 `sort1` varchar(80) default NULL,
481 `sort2` varchar(80) default NULL,
482 `altcontactfirstname` varchar(255) default NULL,
483 `altcontactsurname` varchar(255) default NULL,
484 `altcontactaddress1` varchar(255) default NULL,
485 `altcontactaddress2` varchar(255) default NULL,
486 `altcontactaddress3` varchar(255) default NULL,
487 `altcontactzipcode` varchar(50) default NULL,
488 `altcontactphone` varchar(50) default NULL,
489 `smsalertnumber` varchar(50) default NULL,
490 UNIQUE KEY `cardnumber` (`cardnumber`),
491 PRIMARY KEY `borrowernumber` (`borrowernumber`),
492 KEY `categorycode` (`categorycode`),
493 KEY `branchcode` (`branchcode`),
494 KEY `userid` (`userid`),
495 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
496 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
497 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
500 -- Table structure for table `borrower_attribute_types`
503 DROP TABLE IF EXISTS `borrower_attribute_types`;
504 CREATE TABLE `borrower_attribute_types` (
505 `code` varchar(10) NOT NULL,
506 `description` varchar(255) NOT NULL,
507 `repeatable` tinyint(1) NOT NULL default 0,
508 `unique_id` tinyint(1) NOT NULL default 0,
509 `opac_display` tinyint(1) NOT NULL default 0,
510 `password_allowed` tinyint(1) NOT NULL default 0,
511 `staff_searchable` tinyint(1) NOT NULL default 0,
512 `authorised_value_category` varchar(10) default NULL,
514 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
517 -- Table structure for table `borrower_attributes`
520 DROP TABLE IF EXISTS `borrower_attributes`;
521 CREATE TABLE `borrower_attributes` (
522 `borrowernumber` int(11) NOT NULL,
523 `code` varchar(10) NOT NULL,
524 `attribute` varchar(30) default NULL,
525 `password` varchar(30) default NULL,
526 KEY `borrowernumber` (`borrowernumber`),
527 KEY `code_attribute` (`code`, `attribute`),
528 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
529 ON DELETE CASCADE ON UPDATE CASCADE,
530 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
531 ON DELETE CASCADE ON UPDATE CASCADE
532 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
535 -- Table structure for table `branchcategories`
538 DROP TABLE IF EXISTS `branchcategories`;
539 CREATE TABLE `branchcategories` (
540 `categorycode` varchar(10) NOT NULL default '',
541 `categoryname` varchar(32),
542 `codedescription` mediumtext,
543 `categorytype` varchar(16),
544 PRIMARY KEY (`categorycode`)
545 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
548 -- Table structure for table `branches`
551 DROP TABLE IF EXISTS `branches`;
552 CREATE TABLE `branches` (
553 `branchcode` varchar(10) NOT NULL default '',
554 `branchname` mediumtext NOT NULL,
555 `branchaddress1` mediumtext,
556 `branchaddress2` mediumtext,
557 `branchaddress3` mediumtext,
558 `branchphone` mediumtext,
559 `branchfax` mediumtext,
560 `branchemail` mediumtext,
561 `issuing` tinyint(4) default NULL,
562 `branchip` varchar(15) default NULL,
563 `branchprinter` varchar(100) default NULL,
564 UNIQUE KEY `branchcode` (`branchcode`)
565 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
568 -- Table structure for table `branchrelations`
571 DROP TABLE IF EXISTS `branchrelations`;
572 CREATE TABLE `branchrelations` (
573 `branchcode` varchar(10) NOT NULL default '',
574 `categorycode` varchar(10) NOT NULL default '',
575 PRIMARY KEY (`branchcode`,`categorycode`),
576 KEY `branchcode` (`branchcode`),
577 KEY `categorycode` (`categorycode`),
578 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
579 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
580 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
583 -- Table structure for table `branchtransfers`
586 DROP TABLE IF EXISTS `branchtransfers`;
587 CREATE TABLE `branchtransfers` (
588 `itemnumber` int(11) NOT NULL default 0,
589 `datesent` datetime default NULL,
590 `frombranch` varchar(10) NOT NULL default '',
591 `datearrived` datetime default NULL,
592 `tobranch` varchar(10) NOT NULL default '',
593 `comments` mediumtext,
594 KEY `frombranch` (`frombranch`),
595 KEY `tobranch` (`tobranch`),
596 KEY `itemnumber` (`itemnumber`),
597 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
598 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
599 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
600 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
604 -- Table structure for table `browser`
606 DROP TABLE IF EXISTS `browser`;
607 CREATE TABLE `browser` (
608 `level` int(11) NOT NULL,
609 `classification` varchar(20) NOT NULL,
610 `description` varchar(255) NOT NULL,
611 `number` bigint(20) NOT NULL,
612 `endnode` tinyint(4) NOT NULL
613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
616 -- Table structure for table `categories`
619 DROP TABLE IF EXISTS `categories`;
620 CREATE TABLE `categories` (
621 `categorycode` varchar(10) NOT NULL default '',
622 `description` mediumtext,
623 `enrolmentperiod` smallint(6) default NULL,
624 `upperagelimit` smallint(6) default NULL,
625 `dateofbirthrequired` tinyint(1) default NULL,
626 `finetype` varchar(30) default NULL,
627 `bulk` tinyint(1) default NULL,
628 `enrolmentfee` decimal(28,6) default NULL,
629 `overduenoticerequired` tinyint(1) default NULL,
630 `issuelimit` smallint(6) default NULL,
631 `reservefee` decimal(28,6) default NULL,
632 `category_type` varchar(1) NOT NULL default 'A',
633 PRIMARY KEY (`categorycode`),
634 UNIQUE KEY `categorycode` (`categorycode`)
635 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
638 -- Table structure for table `borrower_branch_circ_rules`
641 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
642 CREATE TABLE `branch_borrower_circ_rules` (
643 `branchcode` VARCHAR(10) NOT NULL,
644 `categorycode` VARCHAR(10) NOT NULL,
645 `maxissueqty` int(4) default NULL,
646 PRIMARY KEY (`categorycode`, `branchcode`),
647 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
648 ON DELETE CASCADE ON UPDATE CASCADE,
649 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
650 ON DELETE CASCADE ON UPDATE CASCADE
651 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
654 -- Table structure for table `default_borrower_circ_rules`
657 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
658 CREATE TABLE `default_borrower_circ_rules` (
659 `categorycode` VARCHAR(10) NOT NULL,
660 `maxissueqty` int(4) default NULL,
661 PRIMARY KEY (`categorycode`),
662 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
663 ON DELETE CASCADE ON UPDATE CASCADE
664 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
667 -- Table structure for table `default_branch_circ_rules`
670 DROP TABLE IF EXISTS `default_branch_circ_rules`;
671 CREATE TABLE `default_branch_circ_rules` (
672 `branchcode` VARCHAR(10) NOT NULL,
673 `maxissueqty` int(4) default NULL,
674 PRIMARY KEY (`branchcode`),
675 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
676 ON DELETE CASCADE ON UPDATE CASCADE
677 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
680 -- Table structure for table `default_circ_rules`
683 DROP TABLE IF EXISTS `default_circ_rules`;
684 CREATE TABLE `default_circ_rules` (
685 `singleton` enum('singleton') NOT NULL default 'singleton',
686 `maxissueqty` int(4) default NULL,
687 PRIMARY KEY (`singleton`)
688 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
691 -- Table structure for table `cities`
694 DROP TABLE IF EXISTS `cities`;
695 CREATE TABLE `cities` (
696 `cityid` int(11) NOT NULL auto_increment,
697 `city_name` varchar(100) NOT NULL default '',
698 `city_zipcode` varchar(20) default NULL,
699 PRIMARY KEY (`cityid`)
700 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
703 -- Table structure for table `class_sort_rules`
706 DROP TABLE IF EXISTS `class_sort_rules`;
707 CREATE TABLE `class_sort_rules` (
708 `class_sort_rule` varchar(10) NOT NULL default '',
709 `description` mediumtext,
710 `sort_routine` varchar(30) NOT NULL default '',
711 PRIMARY KEY (`class_sort_rule`),
712 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
713 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
716 -- Table structure for table `class_sources`
719 DROP TABLE IF EXISTS `class_sources`;
720 CREATE TABLE `class_sources` (
721 `cn_source` varchar(10) NOT NULL default '',
722 `description` mediumtext,
723 `used` tinyint(4) NOT NULL default 0,
724 `class_sort_rule` varchar(10) NOT NULL default '',
725 PRIMARY KEY (`cn_source`),
726 UNIQUE KEY `cn_source_idx` (`cn_source`),
727 KEY `used_idx` (`used`),
728 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
732 -- Table structure for table `currency`
735 DROP TABLE IF EXISTS `currency`;
736 CREATE TABLE `currency` (
737 `currency` varchar(10) NOT NULL default '',
738 `symbol` varchar(5) default NULL,
739 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
740 `rate` float(7,5) default NULL,
741 PRIMARY KEY (`currency`)
742 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
745 -- Table structure for table `deletedbiblio`
748 DROP TABLE IF EXISTS `deletedbiblio`;
749 CREATE TABLE `deletedbiblio` (
750 `biblionumber` int(11) NOT NULL default 0,
751 `frameworkcode` varchar(4) NOT NULL default '',
754 `unititle` mediumtext,
756 `serial` tinyint(1) default NULL,
757 `seriestitle` mediumtext,
758 `copyrightdate` smallint(6) default NULL,
759 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
760 `datecreated` DATE NOT NULL,
761 `abstract` mediumtext,
762 PRIMARY KEY (`biblionumber`),
763 KEY `blbnoidx` (`biblionumber`)
764 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
767 -- Table structure for table `deletedbiblioitems`
770 DROP TABLE IF EXISTS `deletedbiblioitems`;
771 CREATE TABLE `deletedbiblioitems` (
772 `biblioitemnumber` int(11) NOT NULL default 0,
773 `biblionumber` int(11) NOT NULL default 0,
776 `itemtype` varchar(10) default NULL,
777 `isbn` varchar(14) default NULL,
778 `issn` varchar(9) default NULL,
779 `publicationyear` text,
780 `publishercode` varchar(255) default NULL,
781 `volumedate` date default NULL,
783 `collectiontitle` mediumtext default NULL,
784 `collectionissn` text default NULL,
785 `collectionvolume` mediumtext default NULL,
786 `editionstatement` text default NULL,
787 `editionresponsibility` text default NULL,
788 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
789 `illus` varchar(255) default NULL,
790 `pages` varchar(255) default NULL,
792 `size` varchar(255) default NULL,
793 `place` varchar(255) default NULL,
794 `lccn` varchar(25) default NULL,
796 `url` varchar(255) default NULL,
797 `cn_source` varchar(10) default NULL,
798 `cn_class` varchar(30) default NULL,
799 `cn_item` varchar(10) default NULL,
800 `cn_suffix` varchar(10) default NULL,
801 `cn_sort` varchar(30) default NULL,
802 `totalissues` int(10),
803 `marcxml` longtext NOT NULL,
804 PRIMARY KEY (`biblioitemnumber`),
805 KEY `bibinoidx` (`biblioitemnumber`),
806 KEY `bibnoidx` (`biblionumber`),
808 KEY `publishercode` (`publishercode`)
809 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
812 -- Table structure for table `deletedborrowers`
815 DROP TABLE IF EXISTS `deletedborrowers`;
816 CREATE TABLE `deletedborrowers` (
817 `borrowernumber` int(11) NOT NULL default 0,
818 `cardnumber` varchar(9) NOT NULL default '',
819 `surname` mediumtext NOT NULL,
822 `othernames` mediumtext,
824 `streetnumber` varchar(10) default NULL,
825 `streettype` varchar(50) default NULL,
826 `address` mediumtext NOT NULL,
828 `city` mediumtext NOT NULL,
829 `zipcode` varchar(25) default NULL,
832 `mobile` varchar(50) default NULL,
836 `B_streetnumber` varchar(10) default NULL,
837 `B_streettype` varchar(50) default NULL,
838 `B_address` varchar(100) default NULL,
840 `B_zipcode` varchar(25) default NULL,
842 `B_phone` mediumtext,
843 `dateofbirth` date default NULL,
844 `branchcode` varchar(10) NOT NULL default '',
845 `categorycode` varchar(2) default NULL,
846 `dateenrolled` date default NULL,
847 `dateexpiry` date default NULL,
848 `gonenoaddress` tinyint(1) default NULL,
849 `lost` tinyint(1) default NULL,
850 `debarred` tinyint(1) default NULL,
851 `contactname` mediumtext,
852 `contactfirstname` text,
854 `guarantorid` int(11) default NULL,
855 `borrowernotes` mediumtext,
856 `relationship` varchar(100) default NULL,
857 `ethnicity` varchar(50) default NULL,
858 `ethnotes` varchar(255) default NULL,
859 `sex` varchar(1) default NULL,
860 `password` varchar(30) default NULL,
861 `flags` int(11) default NULL,
862 `userid` varchar(30) default NULL,
863 `opacnote` mediumtext,
864 `contactnote` varchar(255) default NULL,
865 `sort1` varchar(80) default NULL,
866 `sort2` varchar(80) default NULL,
867 `altcontactfirstname` varchar(255) default NULL,
868 `altcontactsurname` varchar(255) default NULL,
869 `altcontactaddress1` varchar(255) default NULL,
870 `altcontactaddress2` varchar(255) default NULL,
871 `altcontactaddress3` varchar(255) default NULL,
872 `altcontactzipcode` varchar(50) default NULL,
873 `altcontactphone` varchar(50) default NULL,
874 KEY `borrowernumber` (`borrowernumber`),
875 KEY `cardnumber` (`cardnumber`)
876 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
879 -- Table structure for table `deleteditems`
882 DROP TABLE IF EXISTS `deleteditems`;
883 CREATE TABLE `deleteditems` (
884 `itemnumber` int(11) NOT NULL default 0,
885 `biblionumber` int(11) NOT NULL default 0,
886 `biblioitemnumber` int(11) NOT NULL default 0,
887 `barcode` varchar(20) default NULL,
888 `dateaccessioned` date default NULL,
889 `booksellerid` mediumtext default NULL,
890 `homebranch` varchar(10) default NULL,
891 `price` decimal(8,2) default NULL,
892 `replacementprice` decimal(8,2) default NULL,
893 `replacementpricedate` date default NULL,
894 `datelastborrowed` date default NULL,
895 `datelastseen` date default NULL,
896 `stack` tinyint(1) default NULL,
897 `notforloan` tinyint(1) NOT NULL default 0,
898 `damaged` tinyint(1) NOT NULL default 0,
899 `itemlost` tinyint(1) NOT NULL default 0,
900 `wthdrawn` tinyint(1) NOT NULL default 0,
901 `itemcallnumber` varchar(30) default NULL,
902 `issues` smallint(6) default NULL,
903 `renewals` smallint(6) default NULL,
904 `reserves` smallint(6) default NULL,
905 `restricted` tinyint(1) default NULL,
906 `itemnotes` mediumtext,
907 `holdingbranch` varchar(10) default NULL,
908 `paidfor` mediumtext,
909 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
910 `location` varchar(80) default NULL,
911 `onloan` date default NULL,
912 `cn_source` varchar(10) default NULL,
913 `cn_sort` varchar(30) default NULL,
914 `ccode` varchar(10) default NULL,
915 `materials` varchar(10) default NULL,
916 `uri` varchar(255) default NULL,
917 `itype` varchar(10) default NULL,
918 `more_subfields_xml` longtext default NULL,
919 `enumchron` varchar(80) default NULL,
920 `copynumber` varchar(32) default NULL,
922 PRIMARY KEY (`itemnumber`),
923 KEY `delitembarcodeidx` (`barcode`),
924 KEY `delitembinoidx` (`biblioitemnumber`),
925 KEY `delitembibnoidx` (`biblionumber`),
926 KEY `delhomebranch` (`homebranch`),
927 KEY `delholdingbranch` (`holdingbranch`)
928 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
931 -- Table structure for table `ethnicity`
934 DROP TABLE IF EXISTS `ethnicity`;
935 CREATE TABLE `ethnicity` (
936 `code` varchar(10) NOT NULL default '',
937 `name` varchar(255) default NULL,
939 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
942 -- Table structure for table `import_batches`
945 DROP TABLE IF EXISTS `import_batches`;
946 CREATE TABLE `import_batches` (
947 `import_batch_id` int(11) NOT NULL auto_increment,
948 `matcher_id` int(11) default NULL,
949 `template_id` int(11) default NULL,
950 `branchcode` varchar(10) default NULL,
951 `num_biblios` int(11) NOT NULL default 0,
952 `num_items` int(11) NOT NULL default 0,
953 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
954 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
955 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
956 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
957 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
958 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
959 `file_name` varchar(100),
960 `comments` mediumtext,
961 PRIMARY KEY (`import_batch_id`),
962 KEY `branchcode` (`branchcode`)
963 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
966 -- Table structure for table `import_records`
969 DROP TABLE IF EXISTS `import_records`;
970 CREATE TABLE `import_records` (
971 `import_record_id` int(11) NOT NULL auto_increment,
972 `import_batch_id` int(11) NOT NULL,
973 `branchcode` varchar(10) default NULL,
974 `record_sequence` int(11) NOT NULL default 0,
975 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
976 `import_date` DATE default NULL,
977 `marc` longblob NOT NULL,
978 `marcxml` longtext NOT NULL,
979 `marcxml_old` longtext NOT NULL,
980 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
981 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
982 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
983 `import_error` mediumtext,
984 `encoding` varchar(40) NOT NULL default '',
985 `z3950random` varchar(40) default NULL,
986 PRIMARY KEY (`import_record_id`),
987 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
988 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
989 KEY `branchcode` (`branchcode`),
990 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
991 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
994 -- Table structure for `import_record_matches`
996 DROP TABLE IF EXISTS `import_record_matches`;
997 CREATE TABLE `import_record_matches` (
998 `import_record_id` int(11) NOT NULL,
999 `candidate_match_id` int(11) NOT NULL,
1000 `score` int(11) NOT NULL default 0,
1001 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1002 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1003 KEY `record_score` (`import_record_id`, `score`)
1004 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1007 -- Table structure for table `import_biblios`
1010 DROP TABLE IF EXISTS `import_biblios`;
1011 CREATE TABLE `import_biblios` (
1012 `import_record_id` int(11) NOT NULL,
1013 `matched_biblionumber` int(11) default NULL,
1014 `control_number` varchar(25) default NULL,
1015 `original_source` varchar(25) default NULL,
1016 `title` varchar(128) default NULL,
1017 `author` varchar(80) default NULL,
1018 `isbn` varchar(14) default NULL,
1019 `issn` varchar(9) default NULL,
1020 `has_items` tinyint(1) NOT NULL default 0,
1021 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1022 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1023 KEY `matched_biblionumber` (`matched_biblionumber`),
1024 KEY `title` (`title`),
1026 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1029 -- Table structure for table `import_items`
1032 DROP TABLE IF EXISTS `import_items`;
1033 CREATE TABLE `import_items` (
1034 `import_items_id` int(11) NOT NULL auto_increment,
1035 `import_record_id` int(11) NOT NULL,
1036 `itemnumber` int(11) default NULL,
1037 `branchcode` varchar(10) default NULL,
1038 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1039 `marcxml` longtext NOT NULL,
1040 `import_error` mediumtext,
1041 PRIMARY KEY (`import_items_id`),
1042 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1043 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1044 KEY `itemnumber` (`itemnumber`),
1045 KEY `branchcode` (`branchcode`)
1046 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1049 -- Table structure for table `issues`
1052 DROP TABLE IF EXISTS `issues`;
1053 CREATE TABLE `issues` (
1054 `borrowernumber` int(11) default NULL,
1055 `itemnumber` int(11) default NULL,
1056 `date_due` date default NULL,
1057 `branchcode` varchar(10) default NULL,
1058 `issuingbranch` varchar(18) default NULL,
1059 `returndate` date default NULL,
1060 `lastreneweddate` date default NULL,
1061 `return` varchar(4) default NULL,
1062 `renewals` tinyint(4) default NULL,
1063 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1064 `issuedate` date default NULL,
1065 KEY `issuesborridx` (`borrowernumber`),
1066 KEY `issuesitemidx` (`itemnumber`),
1067 KEY `bordate` (`borrowernumber`,`timestamp`),
1068 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1069 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1070 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1073 -- Table structure for table `issuingrules`
1076 DROP TABLE IF EXISTS `issuingrules`;
1077 CREATE TABLE `issuingrules` (
1078 `categorycode` varchar(10) NOT NULL default '',
1079 `itemtype` varchar(10) NOT NULL default '',
1080 `restrictedtype` tinyint(1) default NULL,
1081 `rentaldiscount` decimal(28,6) default NULL,
1082 `reservecharge` decimal(28,6) default NULL,
1083 `fine` decimal(28,6) default NULL,
1084 `firstremind` int(11) default NULL,
1085 `chargeperiod` int(11) default NULL,
1086 `accountsent` int(11) default NULL,
1087 `chargename` varchar(100) default NULL,
1088 `maxissueqty` int(4) default NULL,
1089 `issuelength` int(4) default NULL,
1090 `branchcode` varchar(10) NOT NULL default '',
1091 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1092 KEY `categorycode` (`categorycode`),
1093 KEY `itemtype` (`itemtype`)
1094 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1097 -- Table structure for table `items`
1100 DROP TABLE IF EXISTS `items`;
1101 CREATE TABLE `items` (
1102 `itemnumber` int(11) NOT NULL auto_increment,
1103 `biblionumber` int(11) NOT NULL default 0,
1104 `biblioitemnumber` int(11) NOT NULL default 0,
1105 `barcode` varchar(20) default NULL,
1106 `dateaccessioned` date default NULL,
1107 `booksellerid` mediumtext default NULL,
1108 `homebranch` varchar(10) default NULL,
1109 `price` decimal(8,2) default NULL,
1110 `replacementprice` decimal(8,2) default NULL,
1111 `replacementpricedate` date default NULL,
1112 `datelastborrowed` date default NULL,
1113 `datelastseen` date default NULL,
1114 `stack` tinyint(1) default NULL,
1115 `notforloan` tinyint(1) NOT NULL default 0,
1116 `damaged` tinyint(1) NOT NULL default 0,
1117 `itemlost` tinyint(1) NOT NULL default 0,
1118 `wthdrawn` tinyint(1) NOT NULL default 0,
1119 `itemcallnumber` varchar(30) default NULL,
1120 `issues` smallint(6) default NULL,
1121 `renewals` smallint(6) default NULL,
1122 `reserves` smallint(6) default NULL,
1123 `restricted` tinyint(1) default NULL,
1124 `itemnotes` mediumtext,
1125 `holdingbranch` varchar(10) default NULL,
1126 `paidfor` mediumtext,
1127 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1128 `location` varchar(80) default NULL,
1129 `onloan` date default NULL,
1130 `cn_source` varchar(10) default NULL,
1131 `cn_sort` varchar(30) default NULL,
1132 `ccode` varchar(10) default NULL,
1133 `materials` varchar(10) default NULL,
1134 `uri` varchar(255) default NULL,
1135 `itype` varchar(10) default NULL,
1136 `more_subfields_xml` longtext default NULL,
1137 `enumchron` varchar(80) default NULL,
1138 `copynumber` varchar(32) default NULL,
1139 PRIMARY KEY (`itemnumber`),
1140 UNIQUE KEY `itembarcodeidx` (`barcode`),
1141 KEY `itembinoidx` (`biblioitemnumber`),
1142 KEY `itembibnoidx` (`biblionumber`),
1143 KEY `homebranch` (`homebranch`),
1144 KEY `holdingbranch` (`holdingbranch`),
1145 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1146 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1147 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1148 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1151 -- Table structure for table `itemtypes`
1154 DROP TABLE IF EXISTS `itemtypes`;
1155 CREATE TABLE `itemtypes` (
1156 `itemtype` varchar(10) NOT NULL default '',
1157 `description` mediumtext,
1158 `renewalsallowed` smallint(6) default NULL,
1159 `rentalcharge` double(16,4) default NULL,
1160 `notforloan` smallint(6) default NULL,
1161 `imageurl` varchar(200) default NULL,
1163 PRIMARY KEY (`itemtype`),
1164 UNIQUE KEY `itemtype` (`itemtype`)
1165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1168 -- Table structure for table `labels`
1171 DROP TABLE IF EXISTS `labels`;
1172 CREATE TABLE `labels` (
1173 `labelid` int(11) NOT NULL auto_increment,
1174 `batch_id` varchar(10) NOT NULL default 1,
1175 `itemnumber` varchar(100) NOT NULL default '',
1176 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1177 PRIMARY KEY (`labelid`)
1178 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1181 -- Table structure for table `labels_conf`
1184 DROP TABLE IF EXISTS `labels_conf`;
1185 CREATE TABLE `labels_conf` (
1186 `id` int(4) NOT NULL auto_increment,
1187 `barcodetype` char(100) default '',
1188 `title` int(1) default '0',
1189 `subtitle` int(1) default '0',
1190 `itemtype` int(1) default '0',
1191 `barcode` int(1) default '0',
1192 `dewey` int(1) default '0',
1193 `classification` int(1) default NULL,
1194 `subclass` int(1) default '0',
1195 `itemcallnumber` int(1) default '0',
1196 `author` int(1) default '0',
1197 `issn` int(1) default '0',
1198 `isbn` int(1) default '0',
1199 `startlabel` int(2) NOT NULL default '1',
1200 `printingtype` char(32) default 'BAR',
1201 `formatstring` varchar(64) default NULL,
1202 `layoutname` char(20) NOT NULL default 'TEST',
1203 `guidebox` int(1) default '0',
1204 `active` tinyint(1) default '1',
1205 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1206 `ccode` char(4) collate utf8_unicode_ci default NULL,
1207 `callnum_split` int(1) default NULL,
1208 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1210 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1213 -- Table structure for table `labels_profile`
1216 DROP TABLE IF EXISTS `labels_profile`;
1217 CREATE TABLE `labels_profile` (
1218 `tmpl_id` int(4) NOT NULL,
1219 `prof_id` int(4) NOT NULL,
1220 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1221 UNIQUE KEY `prof_id` (`prof_id`)
1222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1225 -- Table structure for table `labels_templates`
1228 DROP TABLE IF EXISTS `labels_templates`;
1229 CREATE TABLE `labels_templates` (
1230 `tmpl_id` int(4) NOT NULL auto_increment,
1231 `tmpl_code` char(100) default '',
1232 `tmpl_desc` char(100) default '',
1233 `page_width` float default '0',
1234 `page_height` float default '0',
1235 `label_width` float default '0',
1236 `label_height` float default '0',
1237 `topmargin` float default '0',
1238 `leftmargin` float default '0',
1239 `cols` int(2) default '0',
1240 `rows` int(2) default '0',
1241 `colgap` float default '0',
1242 `rowgap` float default '0',
1243 `active` int(1) default NULL,
1244 `units` char(20) default 'PX',
1245 `fontsize` int(4) NOT NULL default '3',
1246 `font` char(10) NOT NULL default 'TR',
1247 PRIMARY KEY (`tmpl_id`)
1248 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1251 -- Table structure for table `letter`
1254 DROP TABLE IF EXISTS `letter`;
1255 CREATE TABLE `letter` (
1256 `module` varchar(20) NOT NULL default '',
1257 `code` varchar(20) NOT NULL default '',
1258 `name` varchar(100) NOT NULL default '',
1259 `title` varchar(200) NOT NULL default '',
1261 PRIMARY KEY (`module`,`code`)
1262 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1265 -- Table structure for table `marc_subfield_structure`
1268 DROP TABLE IF EXISTS `marc_subfield_structure`;
1269 CREATE TABLE `marc_subfield_structure` (
1270 `tagfield` varchar(3) NOT NULL default '',
1271 `tagsubfield` varchar(1) NOT NULL default '',
1272 `liblibrarian` varchar(255) NOT NULL default '',
1273 `libopac` varchar(255) NOT NULL default '',
1274 `repeatable` tinyint(4) NOT NULL default 0,
1275 `mandatory` tinyint(4) NOT NULL default 0,
1276 `kohafield` varchar(40) default NULL,
1277 `tab` tinyint(1) default NULL,
1278 `authorised_value` varchar(20) default NULL,
1279 `authtypecode` varchar(20) default NULL,
1280 `value_builder` varchar(80) default NULL,
1281 `isurl` tinyint(1) default NULL,
1282 `hidden` tinyint(1) default NULL,
1283 `frameworkcode` varchar(4) NOT NULL default '',
1284 `seealso` varchar(1100) default NULL,
1285 `link` varchar(80) default NULL,
1286 `defaultvalue` text default NULL,
1287 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1288 KEY `kohafield_2` (`kohafield`),
1289 KEY `tab` (`frameworkcode`,`tab`),
1290 KEY `kohafield` (`frameworkcode`,`kohafield`)
1291 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1294 -- Table structure for table `marc_tag_structure`
1297 DROP TABLE IF EXISTS `marc_tag_structure`;
1298 CREATE TABLE `marc_tag_structure` (
1299 `tagfield` varchar(3) NOT NULL default '',
1300 `liblibrarian` varchar(255) NOT NULL default '',
1301 `libopac` varchar(255) NOT NULL default '',
1302 `repeatable` tinyint(4) NOT NULL default 0,
1303 `mandatory` tinyint(4) NOT NULL default 0,
1304 `authorised_value` varchar(10) default NULL,
1305 `frameworkcode` varchar(4) NOT NULL default '',
1306 PRIMARY KEY (`frameworkcode`,`tagfield`)
1307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1310 -- Table structure for table `marc_matchers`
1313 DROP TABLE IF EXISTS `marc_matchers`;
1314 CREATE TABLE `marc_matchers` (
1315 `matcher_id` int(11) NOT NULL auto_increment,
1316 `code` varchar(10) NOT NULL default '',
1317 `description` varchar(255) NOT NULL default '',
1318 `record_type` varchar(10) NOT NULL default 'biblio',
1319 `threshold` int(11) NOT NULL default 0,
1320 PRIMARY KEY (`matcher_id`),
1321 KEY `code` (`code`),
1322 KEY `record_type` (`record_type`)
1323 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1326 -- Table structure for table `matchpoints`
1328 DROP TABLE IF EXISTS `matchpoints`;
1329 CREATE TABLE `matchpoints` (
1330 `matcher_id` int(11) NOT NULL,
1331 `matchpoint_id` int(11) NOT NULL auto_increment,
1332 `search_index` varchar(30) NOT NULL default '',
1333 `score` int(11) NOT NULL default 0,
1334 PRIMARY KEY (`matchpoint_id`),
1335 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1336 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1337 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1341 -- Table structure for table `matchpoint_components`
1343 DROP TABLE IF EXISTS `matchpoint_components`;
1344 CREATE TABLE `matchpoint_components` (
1345 `matchpoint_id` int(11) NOT NULL,
1346 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1347 sequence int(11) NOT NULL default 0,
1348 tag varchar(3) NOT NULL default '',
1349 subfields varchar(40) NOT NULL default '',
1350 offset int(4) NOT NULL default 0,
1351 length int(4) NOT NULL default 0,
1352 PRIMARY KEY (`matchpoint_component_id`),
1353 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1354 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1355 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1356 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1359 -- Table structure for table `matcher_component_norms`
1361 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1362 CREATE TABLE `matchpoint_component_norms` (
1363 `matchpoint_component_id` int(11) NOT NULL,
1364 `sequence` int(11) NOT NULL default 0,
1365 `norm_routine` varchar(50) NOT NULL default '',
1366 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1367 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1368 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1369 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1372 -- Table structure for table `matcher_matchpoints`
1374 DROP TABLE IF EXISTS `matcher_matchpoints`;
1375 CREATE TABLE `matcher_matchpoints` (
1376 `matcher_id` int(11) NOT NULL,
1377 `matchpoint_id` int(11) NOT NULL,
1378 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1379 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1380 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1381 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1382 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1385 -- Table structure for table `matchchecks`
1387 DROP TABLE IF EXISTS `matchchecks`;
1388 CREATE TABLE `matchchecks` (
1389 `matcher_id` int(11) NOT NULL,
1390 `matchcheck_id` int(11) NOT NULL auto_increment,
1391 `source_matchpoint_id` int(11) NOT NULL,
1392 `target_matchpoint_id` int(11) NOT NULL,
1393 PRIMARY KEY (`matchcheck_id`),
1394 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1395 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1396 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1397 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1398 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1399 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1400 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1403 -- Table structure for table `notifys`
1406 DROP TABLE IF EXISTS `notifys`;
1407 CREATE TABLE `notifys` (
1408 `notify_id` int(11) NOT NULL default 0,
1409 `borrowernumber` int(11) NOT NULL default 0,
1410 `itemnumber` int(11) NOT NULL default 0,
1411 `notify_date` date default NULL,
1412 `notify_send_date` date default NULL,
1413 `notify_level` int(1) NOT NULL default 0,
1414 `method` varchar(20) NOT NULL default ''
1415 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1418 -- Table structure for table `nozebra`
1421 DROP TABLE IF EXISTS `nozebra`;
1422 CREATE TABLE `nozebra` (
1423 `server` varchar(20) NOT NULL,
1424 `indexname` varchar(40) NOT NULL,
1425 `value` varchar(250) NOT NULL,
1426 `biblionumbers` longtext NOT NULL,
1427 KEY `indexname` (`server`,`indexname`),
1428 KEY `value` (`server`,`value`))
1429 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1432 -- Table structure for table `old_issues`
1435 DROP TABLE IF EXISTS `old_issues`;
1436 CREATE TABLE `old_issues` (
1437 `borrowernumber` int(11) default NULL,
1438 `itemnumber` int(11) default NULL,
1439 `date_due` date default NULL,
1440 `branchcode` varchar(10) default NULL,
1441 `issuingbranch` varchar(18) default NULL,
1442 `returndate` date default NULL,
1443 `lastreneweddate` date default NULL,
1444 `return` varchar(4) default NULL,
1445 `renewals` tinyint(4) default NULL,
1446 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1447 `issuedate` date default NULL,
1448 KEY `old_issuesborridx` (`borrowernumber`),
1449 KEY `old_issuesitemidx` (`itemnumber`),
1450 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1451 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1452 ON DELETE SET NULL ON UPDATE SET NULL,
1453 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1454 ON DELETE SET NULL ON UPDATE SET NULL
1455 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1458 -- Table structure for table `old_reserves`
1460 DROP TABLE IF EXISTS `old_reserves`;
1461 CREATE TABLE `old_reserves` (
1462 `borrowernumber` int(11) default NULL,
1463 `reservedate` date default NULL,
1464 `biblionumber` int(11) default NULL,
1465 `constrainttype` varchar(1) default NULL,
1466 `branchcode` varchar(10) default NULL,
1467 `notificationdate` date default NULL,
1468 `reminderdate` date default NULL,
1469 `cancellationdate` date default NULL,
1470 `reservenotes` mediumtext,
1471 `priority` smallint(6) default NULL,
1472 `found` varchar(1) default NULL,
1473 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1474 `itemnumber` int(11) default NULL,
1475 `waitingdate` date default NULL,
1476 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1477 KEY `old_reserves_biblionumber` (`biblionumber`),
1478 KEY `old_reserves_itemnumber` (`itemnumber`),
1479 KEY `old_reserves_branchcode` (`branchcode`),
1480 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1481 ON DELETE SET NULL ON UPDATE SET NULL,
1482 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1483 ON DELETE SET NULL ON UPDATE SET NULL,
1484 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1485 ON DELETE SET NULL ON UPDATE SET NULL
1486 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1489 -- Table structure for table `opac_news`
1492 DROP TABLE IF EXISTS `opac_news`;
1493 CREATE TABLE `opac_news` (
1494 `idnew` int(10) unsigned NOT NULL auto_increment,
1495 `title` varchar(250) NOT NULL default '',
1496 `new` text NOT NULL,
1497 `lang` varchar(25) NOT NULL default '',
1498 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1499 `expirationdate` date default NULL,
1500 `number` int(11) default NULL,
1501 PRIMARY KEY (`idnew`)
1502 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1505 -- Table structure for table `overduerules`
1508 DROP TABLE IF EXISTS `overduerules`;
1509 CREATE TABLE `overduerules` (
1510 `branchcode` varchar(10) NOT NULL default '',
1511 `categorycode` varchar(2) NOT NULL default '',
1512 `delay1` int(4) default 0,
1513 `letter1` varchar(20) default NULL,
1514 `debarred1` varchar(1) default 0,
1515 `delay2` int(4) default 0,
1516 `debarred2` varchar(1) default 0,
1517 `letter2` varchar(20) default NULL,
1518 `delay3` int(4) default 0,
1519 `letter3` varchar(20) default NULL,
1520 `debarred3` int(1) default 0,
1521 PRIMARY KEY (`branchcode`,`categorycode`)
1522 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1525 -- Table structure for table `patroncards`
1528 DROP TABLE IF EXISTS `patroncards`;
1529 CREATE TABLE `patroncards` (
1530 `cardid` int(11) NOT NULL auto_increment,
1531 `batch_id` varchar(10) NOT NULL default '1',
1532 `borrowernumber` int(11) NOT NULL,
1533 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1534 PRIMARY KEY (`cardid`),
1535 KEY `patroncards_ibfk_1` (`borrowernumber`),
1536 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1537 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1540 -- Table structure for table `patronimage`
1543 DROP TABLE IF EXISTS `patronimage`;
1544 CREATE TABLE `patronimage` (
1545 `cardnumber` varchar(16) NOT NULL,
1546 `mimetype` varchar(15) NOT NULL,
1547 `imagefile` mediumblob NOT NULL,
1548 PRIMARY KEY (`cardnumber`),
1549 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1550 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1553 -- Table structure for table `printers`
1556 DROP TABLE IF EXISTS `printers`;
1557 CREATE TABLE `printers` (
1558 `printername` varchar(40) NOT NULL default '',
1559 `printqueue` varchar(20) default NULL,
1560 `printtype` varchar(20) default NULL,
1561 PRIMARY KEY (`printername`)
1562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1565 -- Table structure for table `printers_profile`
1568 DROP TABLE IF EXISTS `printers_profile`;
1569 CREATE TABLE `printers_profile` (
1570 `prof_id` int(4) NOT NULL auto_increment,
1571 `printername` varchar(40) NOT NULL,
1572 `tmpl_id` int(4) NOT NULL,
1573 `paper_bin` varchar(20) NOT NULL,
1574 `offset_horz` float default NULL,
1575 `offset_vert` float default NULL,
1576 `creep_horz` float default NULL,
1577 `creep_vert` float default NULL,
1578 `unit` char(20) NOT NULL default 'POINT',
1579 PRIMARY KEY (`prof_id`),
1580 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1581 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1582 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1585 -- Table structure for table `repeatable_holidays`
1588 DROP TABLE IF EXISTS `repeatable_holidays`;
1589 CREATE TABLE `repeatable_holidays` (
1590 `id` int(11) NOT NULL auto_increment,
1591 `branchcode` varchar(10) NOT NULL default '',
1592 `weekday` smallint(6) default NULL,
1593 `day` smallint(6) default NULL,
1594 `month` smallint(6) default NULL,
1595 `title` varchar(50) NOT NULL default '',
1596 `description` text NOT NULL,
1598 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1601 -- Table structure for table `reports_dictionary`
1604 DROP TABLE IF EXISTS `reports_dictionary`;
1605 CREATE TABLE reports_dictionary (
1606 `id` int(11) NOT NULL auto_increment,
1607 `name` varchar(255) default NULL,
1609 `date_created` datetime default NULL,
1610 `date_modified` datetime default NULL,
1612 `area` int(11) default NULL,
1614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1617 -- Table structure for table `reserveconstraints`
1620 DROP TABLE IF EXISTS `reserveconstraints`;
1621 CREATE TABLE `reserveconstraints` (
1622 `borrowernumber` int(11) NOT NULL default 0,
1623 `reservedate` date default NULL,
1624 `biblionumber` int(11) NOT NULL default 0,
1625 `biblioitemnumber` int(11) default NULL,
1626 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1627 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1630 -- Table structure for table `reserves`
1633 DROP TABLE IF EXISTS `reserves`;
1634 CREATE TABLE `reserves` (
1635 `borrowernumber` int(11) NOT NULL default 0,
1636 `reservedate` date default NULL,
1637 `biblionumber` int(11) NOT NULL default 0,
1638 `constrainttype` varchar(1) default NULL,
1639 `branchcode` varchar(10) default NULL,
1640 `notificationdate` date default NULL,
1641 `reminderdate` date default NULL,
1642 `cancellationdate` date default NULL,
1643 `reservenotes` mediumtext,
1644 `priority` smallint(6) default NULL,
1645 `found` varchar(1) default NULL,
1646 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1647 `itemnumber` int(11) default NULL,
1648 `waitingdate` date default NULL,
1649 KEY `borrowernumber` (`borrowernumber`),
1650 KEY `biblionumber` (`biblionumber`),
1651 KEY `itemnumber` (`itemnumber`),
1652 KEY `branchcode` (`branchcode`),
1653 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1654 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1655 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1656 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1657 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1660 -- Table structure for table `reviews`
1663 DROP TABLE IF EXISTS `reviews`;
1664 CREATE TABLE `reviews` (
1665 `reviewid` int(11) NOT NULL auto_increment,
1666 `borrowernumber` int(11) default NULL,
1667 `biblionumber` int(11) default NULL,
1669 `approved` tinyint(4) default NULL,
1670 `datereviewed` datetime default NULL,
1671 PRIMARY KEY (`reviewid`)
1672 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1675 -- Table structure for table `roadtype`
1678 DROP TABLE IF EXISTS `roadtype`;
1679 CREATE TABLE `roadtype` (
1680 `roadtypeid` int(11) NOT NULL auto_increment,
1681 `road_type` varchar(100) NOT NULL default '',
1682 PRIMARY KEY (`roadtypeid`)
1683 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1686 -- Table structure for table `saved_sql`
1689 DROP TABLE IF EXISTS `saved_sql`;
1690 CREATE TABLE saved_sql (
1691 `id` int(11) NOT NULL auto_increment,
1692 `borrowernumber` int(11) default NULL,
1693 `date_created` datetime default NULL,
1694 `last_modified` datetime default NULL,
1696 `last_run` datetime default NULL,
1697 `report_name` varchar(255) default NULL,
1698 `type` varchar(255) default NULL,
1701 KEY boridx (`borrowernumber`)
1702 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1706 -- Table structure for `saved_reports`
1709 DROP TABLE IF EXISTS `saved_reports`;
1710 CREATE TABLE saved_reports (
1711 `id` int(11) NOT NULL auto_increment,
1712 `report_id` int(11) default NULL,
1714 `date_run` datetime default NULL,
1716 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1720 -- Table structure for table `serial`
1723 DROP TABLE IF EXISTS `serial`;
1724 CREATE TABLE `serial` (
1725 `serialid` int(11) NOT NULL auto_increment,
1726 `biblionumber` varchar(100) NOT NULL default '',
1727 `subscriptionid` varchar(100) NOT NULL default '',
1728 `serialseq` varchar(100) NOT NULL default '',
1729 `status` tinyint(4) NOT NULL default 0,
1730 `planneddate` date default NULL,
1732 `publisheddate` date default NULL,
1733 `itemnumber` text default NULL,
1734 `claimdate` date default NULL,
1735 `routingnotes` text,
1736 PRIMARY KEY (`serialid`)
1737 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1740 -- Table structure for table `sessions`
1743 DROP TABLE IF EXISTS sessions;
1744 CREATE TABLE sessions (
1745 `id` varchar(32) NOT NULL,
1746 `a_session` text NOT NULL,
1748 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1751 -- Table structure for table `special_holidays`
1754 DROP TABLE IF EXISTS `special_holidays`;
1755 CREATE TABLE `special_holidays` (
1756 `id` int(11) NOT NULL auto_increment,
1757 `branchcode` varchar(10) NOT NULL default '',
1758 `day` smallint(6) NOT NULL default 0,
1759 `month` smallint(6) NOT NULL default 0,
1760 `year` smallint(6) NOT NULL default 0,
1761 `isexception` smallint(1) NOT NULL default 1,
1762 `title` varchar(50) NOT NULL default '',
1763 `description` text NOT NULL,
1765 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1768 -- Table structure for table `statistics`
1771 DROP TABLE IF EXISTS `statistics`;
1772 CREATE TABLE `statistics` (
1773 `datetime` datetime default NULL,
1774 `branch` varchar(10) default NULL,
1775 `proccode` varchar(4) default NULL,
1776 `value` double(16,4) default NULL,
1777 `type` varchar(16) default NULL,
1779 `usercode` varchar(10) default NULL,
1780 `itemnumber` int(11) default NULL,
1781 `itemtype` varchar(10) default NULL,
1782 `borrowernumber` int(11) default NULL,
1783 `associatedborrower` int(11) default NULL,
1784 KEY `timeidx` (`datetime`)
1785 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1788 -- Table structure for table `stopwords`
1791 DROP TABLE IF EXISTS `stopwords`;
1792 CREATE TABLE `stopwords` (
1793 `word` varchar(255) default NULL
1794 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1797 -- Table structure for table `subscription`
1800 DROP TABLE IF EXISTS `subscription`;
1801 CREATE TABLE `subscription` (
1802 `biblionumber` int(11) NOT NULL default 0,
1803 `subscriptionid` int(11) NOT NULL auto_increment,
1804 `librarian` varchar(100) default '',
1805 `startdate` date default NULL,
1806 `aqbooksellerid` int(11) default 0,
1807 `cost` int(11) default 0,
1808 `aqbudgetid` int(11) default 0,
1809 `weeklength` int(11) default 0,
1810 `monthlength` int(11) default 0,
1811 `numberlength` int(11) default 0,
1812 `periodicity` tinyint(4) default 0,
1813 `dow` varchar(100) default '',
1814 `numberingmethod` varchar(100) default '',
1816 `status` varchar(100) NOT NULL default '',
1817 `add1` int(11) default 0,
1818 `every1` int(11) default 0,
1819 `whenmorethan1` int(11) default 0,
1820 `setto1` int(11) default NULL,
1821 `lastvalue1` int(11) default NULL,
1822 `add2` int(11) default 0,
1823 `every2` int(11) default 0,
1824 `whenmorethan2` int(11) default 0,
1825 `setto2` int(11) default NULL,
1826 `lastvalue2` int(11) default NULL,
1827 `add3` int(11) default 0,
1828 `every3` int(11) default 0,
1829 `innerloop1` int(11) default 0,
1830 `innerloop2` int(11) default 0,
1831 `innerloop3` int(11) default 0,
1832 `whenmorethan3` int(11) default 0,
1833 `setto3` int(11) default NULL,
1834 `lastvalue3` int(11) default NULL,
1835 `issuesatonce` tinyint(3) NOT NULL default 1,
1836 `firstacquidate` date default NULL,
1837 `manualhistory` tinyint(1) NOT NULL default 0,
1838 `irregularity` text,
1839 `letter` varchar(20) default NULL,
1840 `numberpattern` tinyint(3) default 0,
1841 `distributedto` text,
1842 `internalnotes` longtext,
1844 `branchcode` varchar(10) NOT NULL default '',
1845 `hemisphere` tinyint(3) default 0,
1846 `lastbranch` varchar(10),
1847 `serialsadditems` tinyint(1) NOT NULL default '0',
1848 PRIMARY KEY (`subscriptionid`)
1849 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1852 -- Table structure for table `subscriptionhistory`
1855 DROP TABLE IF EXISTS `subscriptionhistory`;
1856 CREATE TABLE `subscriptionhistory` (
1857 `biblionumber` int(11) NOT NULL default 0,
1858 `subscriptionid` int(11) NOT NULL default 0,
1859 `histstartdate` date default NULL,
1860 `enddate` date default NULL,
1861 `missinglist` longtext NOT NULL,
1862 `recievedlist` longtext NOT NULL,
1863 `opacnote` varchar(150) NOT NULL default '',
1864 `librariannote` varchar(150) NOT NULL default '',
1865 PRIMARY KEY (`subscriptionid`),
1866 KEY `biblionumber` (`biblionumber`)
1867 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1870 -- Table structure for table `subscriptionroutinglist`
1873 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1874 CREATE TABLE `subscriptionroutinglist` (
1875 `routingid` int(11) NOT NULL auto_increment,
1876 `borrowernumber` int(11) default NULL,
1877 `ranking` int(11) default NULL,
1878 `subscriptionid` int(11) default NULL,
1879 PRIMARY KEY (`routingid`)
1880 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1883 -- Table structure for table `suggestions`
1886 DROP TABLE IF EXISTS `suggestions`;
1887 CREATE TABLE `suggestions` (
1888 `suggestionid` int(8) NOT NULL auto_increment,
1889 `suggestedby` int(11) NOT NULL default 0,
1890 `managedby` int(11) default NULL,
1891 `STATUS` varchar(10) NOT NULL default '',
1893 `author` varchar(80) default NULL,
1894 `title` varchar(80) default NULL,
1895 `copyrightdate` smallint(6) default NULL,
1896 `publishercode` varchar(255) default NULL,
1897 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1898 `volumedesc` varchar(255) default NULL,
1899 `publicationyear` smallint(6) default 0,
1900 `place` varchar(255) default NULL,
1901 `isbn` varchar(10) default NULL,
1902 `mailoverseeing` smallint(1) default 0,
1903 `biblionumber` int(11) default NULL,
1905 PRIMARY KEY (`suggestionid`),
1906 KEY `suggestedby` (`suggestedby`),
1907 KEY `managedby` (`managedby`)
1908 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1911 -- Table structure for table `systempreferences`
1914 DROP TABLE IF EXISTS `systempreferences`;
1915 CREATE TABLE `systempreferences` (
1916 `variable` varchar(50) NOT NULL default '',
1918 `options` mediumtext,
1920 `type` varchar(20) default NULL,
1921 PRIMARY KEY (`variable`)
1922 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1925 -- Table structure for table `tags`
1928 DROP TABLE IF EXISTS `tags`;
1929 CREATE TABLE `tags` (
1930 `entry` varchar(255) NOT NULL default '',
1931 `weight` bigint(20) NOT NULL default 0,
1932 PRIMARY KEY (`entry`)
1933 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1936 -- Table structure for table `tags_all`
1939 CREATE TABLE `tags_all` (
1940 `tag_id` int(11) NOT NULL auto_increment,
1941 `borrowernumber` int(11) NOT NULL,
1942 `biblionumber` int(11) NOT NULL,
1943 `term` varchar(255) NOT NULL,
1944 `language` int(4) default NULL,
1945 `date_created` datetime NOT NULL,
1946 PRIMARY KEY (`tag_id`),
1947 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1948 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1949 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1950 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1951 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1952 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1953 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1956 -- Table structure for table `tags_approval`
1959 CREATE TABLE `tags_approval` (
1960 `term` varchar(255) NOT NULL,
1961 `approved` int(1) NOT NULL default '0',
1962 `date_approved` datetime default NULL,
1963 `approved_by` int(11) default NULL,
1964 `weight_total` int(9) NOT NULL default '1',
1965 PRIMARY KEY (`term`),
1966 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1967 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1968 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1969 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1972 -- Table structure for table `tags_index`
1975 CREATE TABLE `tags_index` (
1976 `term` varchar(255) NOT NULL,
1977 `biblionumber` int(11) NOT NULL,
1978 `weight` int(9) NOT NULL default '1',
1979 PRIMARY KEY (`term`,`biblionumber`),
1980 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1981 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1982 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1983 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1984 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1985 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1988 -- Table structure for table `userflags`
1991 DROP TABLE IF EXISTS `userflags`;
1992 CREATE TABLE `userflags` (
1993 `bit` int(11) NOT NULL default 0,
1994 `flag` varchar(30) default NULL,
1995 `flagdesc` varchar(255) default NULL,
1996 `defaulton` int(11) default NULL,
1998 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2001 -- Table structure for table `virtualshelves`
2004 DROP TABLE IF EXISTS `virtualshelves`;
2005 CREATE TABLE `virtualshelves` (
2006 `shelfnumber` int(11) NOT NULL auto_increment,
2007 `shelfname` varchar(255) default NULL,
2008 `owner` varchar(80) default NULL,
2009 `category` varchar(1) default NULL,
2010 `sortfield` varchar(16) default NULL,
2011 PRIMARY KEY (`shelfnumber`)
2012 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2015 -- Table structure for table `virtualshelfcontents`
2018 DROP TABLE IF EXISTS `virtualshelfcontents`;
2019 CREATE TABLE `virtualshelfcontents` (
2020 `shelfnumber` int(11) NOT NULL default 0,
2021 `biblionumber` int(11) NOT NULL default 0,
2022 `flags` int(11) default NULL,
2023 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2024 KEY `shelfnumber` (`shelfnumber`),
2025 KEY `biblionumber` (`biblionumber`),
2026 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2027 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2028 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2031 -- Table structure for table `z3950servers`
2034 DROP TABLE IF EXISTS `z3950servers`;
2035 CREATE TABLE `z3950servers` (
2036 `host` varchar(255) default NULL,
2037 `port` int(11) default NULL,
2038 `db` varchar(255) default NULL,
2039 `userid` varchar(255) default NULL,
2040 `password` varchar(255) default NULL,
2042 `id` int(11) NOT NULL auto_increment,
2043 `checked` smallint(6) default NULL,
2044 `rank` int(11) default NULL,
2045 `syntax` varchar(80) default NULL,
2047 `position` enum('primary','secondary','') NOT NULL default 'primary',
2048 `type` enum('zed','opensearch') NOT NULL default 'zed',
2049 `encoding` text default NULL,
2050 `description` text NOT NULL,
2052 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2055 -- Table structure for table `zebraqueue`
2058 DROP TABLE IF EXISTS `zebraqueue`;
2059 CREATE TABLE `zebraqueue` (
2060 `id` int(11) NOT NULL auto_increment,
2061 `biblio_auth_number` int(11) NOT NULL default '0',
2062 `operation` char(20) NOT NULL default '',
2063 `server` char(20) NOT NULL default '',
2064 `done` int(11) NOT NULL default '0',
2065 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2067 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2068 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2070 DROP TABLE IF EXISTS `services_throttle`;
2071 CREATE TABLE `services_throttle` (
2072 `service_type` varchar(10) NOT NULL default '',
2073 `service_count` varchar(45) default NULL,
2074 PRIMARY KEY (`service_type`)
2075 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2077 -- http://www.w3.org/International/articles/language-tags/
2080 DROP TABLE IF EXISTS language_subtag_registry;
2081 CREATE TABLE language_subtag_registry (
2083 type varchar(25), -- language-script-region-variant-extension-privateuse
2084 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2086 KEY `subtag` (`subtag`)
2087 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2089 -- TODO: add suppress_scripts
2090 -- this maps three letter codes defined in iso639.2 back to their
2091 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2092 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2093 CREATE TABLE language_rfc4646_to_iso639 (
2094 rfc4646_subtag varchar(25),
2095 iso639_2_code varchar(25),
2096 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2097 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2099 DROP TABLE IF EXISTS language_descriptions;
2100 CREATE TABLE language_descriptions (
2104 description varchar(255),
2106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2108 -- bi-directional support, keyed by script subcode
2109 DROP TABLE IF EXISTS language_script_bidi;
2110 CREATE TABLE language_script_bidi (
2111 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2112 bidi varchar(3), -- rtl ltr
2113 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2116 -- TODO: need to map language subtags to script subtags for detection
2117 -- of bidi when script is not specified (like ar, he)
2118 DROP TABLE IF EXISTS language_script_mapping;
2119 CREATE TABLE language_script_mapping (
2120 language_subtag varchar(25),
2121 script_subtag varchar(25),
2122 KEY `language_subtag` (`language_subtag`)
2123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2125 DROP TABLE IF EXISTS `permissions`;
2126 CREATE TABLE `permissions` (
2127 `module_bit` int(11) NOT NULL DEFAULT 0,
2128 `code` varchar(30) DEFAULT NULL,
2129 `description` varchar(255) DEFAULT NULL,
2130 PRIMARY KEY (`module_bit`, `code`),
2131 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2132 ON DELETE CASCADE ON UPDATE CASCADE
2133 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2135 DROP TABLE IF EXISTS serialitems;
2136 CREATE TABLE serialitems (
2137 serialid int(11) NOT NULL,
2138 itemnumber int(11) NOT NULL,
2139 UNIQUE KEY `serialididx` (`serialid`)
2140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2142 DROP TABLE IF EXISTS `user_permissions`;
2143 CREATE TABLE `user_permissions` (
2144 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2145 `module_bit` int(11) NOT NULL DEFAULT 0,
2146 `code` varchar(30) DEFAULT NULL,
2147 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2148 ON DELETE CASCADE ON UPDATE CASCADE,
2149 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2150 ON DELETE CASCADE ON UPDATE CASCADE
2151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2154 -- Table structure for table `tmp_holdsqueue`
2157 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2158 CREATE TABLE `tmp_holdsqueue` (
2159 `biblionumber` int(11) default NULL,
2160 `itemnumber` int(11) default NULL,
2161 `barcode` varchar(20) default NULL,
2162 `surname` mediumtext NOT NULL,
2165 `borrowernumber` int(11) NOT NULL,
2166 `cardnumber` varchar(16) default NULL,
2167 `reservedate` date default NULL,
2169 `itemcallnumber` varchar(30) default NULL,
2170 `holdingbranch` varchar(10) default NULL,
2171 `pickbranch` varchar(10) default NULL,
2173 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2176 -- Table structure for table `message_queue`
2179 DROP TABLE if EXISTS `message_queue`;
2180 CREATE TABLE `message_queue` (
2181 `message_id` int(11) NOT NULL auto_increment,
2182 `borrowernumber` int(11) NOT NULL,
2185 `message_transport_type` varchar(20) NOT NULL,
2186 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2187 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2188 KEY `message_id` (`message_id`),
2189 KEY `borrowernumber` (`borrowernumber`),
2190 KEY `message_transport_type` (`message_transport_type`),
2191 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2192 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2193 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2196 -- Table structure for table `message_transport_types`
2199 DROP TABLE IF EXISTS `message_transport_types`;
2200 CREATE TABLE `message_transport_types` (
2201 `message_transport_type` varchar(20) NOT NULL,
2202 PRIMARY KEY (`message_transport_type`)
2203 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2206 -- Table structure for table `message_attributes`
2209 DROP TABLE IF EXISTS `message_attributes`;
2210 CREATE TABLE `message_attributes` (
2211 `message_attribute_id` int(11) NOT NULL auto_increment,
2212 `message_name` varchar(20) NOT NULL default '',
2213 `takes_days` tinyint(1) NOT NULL default '0',
2214 PRIMARY KEY (`message_attribute_id`),
2215 UNIQUE KEY `message_name` (`message_name`)
2216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2219 -- Table structure for table `message_transports`
2222 DROP TABLE IF EXISTS `message_transports`;
2223 CREATE TABLE `message_transports` (
2224 `message_attribute_id` int(11) NOT NULL,
2225 `message_transport_type` varchar(20) NOT NULL,
2226 `is_digest` tinyint(1) NOT NULL default '0',
2227 `letter_module` varchar(20) NOT NULL default '',
2228 `letter_code` varchar(20) NOT NULL default '',
2229 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2230 KEY `message_transport_type` (`message_transport_type`),
2231 KEY `letter_module` (`letter_module`,`letter_code`),
2232 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2233 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2234 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2235 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2238 -- Table structure for table `borrower_message_preferences`
2241 DROP TABLE IF EXISTS `borrower_message_preferences`;
2242 CREATE TABLE `borrower_message_preferences` (
2243 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2244 `borrowernumber` int(11) NOT NULL default '0',
2245 `message_attribute_id` int(11) default '0',
2246 `days_in_advance` int(11) default '0',
2247 `wants_digest` tinyint(1) NOT NULL default '0',
2248 PRIMARY KEY (`borrower_message_preference_id`),
2249 KEY `borrowernumber` (`borrowernumber`),
2250 KEY `message_attribute_id` (`message_attribute_id`),
2251 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2252 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
2253 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2256 -- Table structure for table `borrower_message_transport_preferences`
2259 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2260 CREATE TABLE `borrower_message_transport_preferences` (
2261 `borrower_message_preference_id` int(11) NOT NULL default '0',
2262 `message_transport_type` varchar(20) NOT NULL default '0',
2263 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2264 KEY `message_transport_type` (`message_transport_type`),
2265 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,
2266 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
2267 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2269 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2270 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2271 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2272 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2273 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2274 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2275 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2276 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;