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(10) 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 '' COLLATE utf8_bin,
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(10) 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 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2012 PRIMARY KEY (`shelfnumber`)
2013 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2016 -- Table structure for table `virtualshelfcontents`
2019 DROP TABLE IF EXISTS `virtualshelfcontents`;
2020 CREATE TABLE `virtualshelfcontents` (
2021 `shelfnumber` int(11) NOT NULL default 0,
2022 `biblionumber` int(11) NOT NULL default 0,
2023 `flags` int(11) default NULL,
2024 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2025 KEY `shelfnumber` (`shelfnumber`),
2026 KEY `biblionumber` (`biblionumber`),
2027 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2028 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2029 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2032 -- Table structure for table `z3950servers`
2035 DROP TABLE IF EXISTS `z3950servers`;
2036 CREATE TABLE `z3950servers` (
2037 `host` varchar(255) default NULL,
2038 `port` int(11) default NULL,
2039 `db` varchar(255) default NULL,
2040 `userid` varchar(255) default NULL,
2041 `password` varchar(255) default NULL,
2043 `id` int(11) NOT NULL auto_increment,
2044 `checked` smallint(6) default NULL,
2045 `rank` int(11) default NULL,
2046 `syntax` varchar(80) default NULL,
2048 `position` enum('primary','secondary','') NOT NULL default 'primary',
2049 `type` enum('zed','opensearch') NOT NULL default 'zed',
2050 `encoding` text default NULL,
2051 `description` text NOT NULL,
2053 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2056 -- Table structure for table `zebraqueue`
2059 DROP TABLE IF EXISTS `zebraqueue`;
2060 CREATE TABLE `zebraqueue` (
2061 `id` int(11) NOT NULL auto_increment,
2062 `biblio_auth_number` int(11) NOT NULL default '0',
2063 `operation` char(20) NOT NULL default '',
2064 `server` char(20) NOT NULL default '',
2065 `done` int(11) NOT NULL default '0',
2066 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2068 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2069 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2071 DROP TABLE IF EXISTS `services_throttle`;
2072 CREATE TABLE `services_throttle` (
2073 `service_type` varchar(10) NOT NULL default '',
2074 `service_count` varchar(45) default NULL,
2075 PRIMARY KEY (`service_type`)
2076 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2078 -- http://www.w3.org/International/articles/language-tags/
2081 DROP TABLE IF EXISTS language_subtag_registry;
2082 CREATE TABLE language_subtag_registry (
2084 type varchar(25), -- language-script-region-variant-extension-privateuse
2085 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2087 KEY `subtag` (`subtag`)
2088 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2090 -- TODO: add suppress_scripts
2091 -- this maps three letter codes defined in iso639.2 back to their
2092 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2093 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2094 CREATE TABLE language_rfc4646_to_iso639 (
2095 rfc4646_subtag varchar(25),
2096 iso639_2_code varchar(25),
2097 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2098 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2100 DROP TABLE IF EXISTS language_descriptions;
2101 CREATE TABLE language_descriptions (
2105 description varchar(255),
2107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2109 -- bi-directional support, keyed by script subcode
2110 DROP TABLE IF EXISTS language_script_bidi;
2111 CREATE TABLE language_script_bidi (
2112 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2113 bidi varchar(3), -- rtl ltr
2114 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2117 -- TODO: need to map language subtags to script subtags for detection
2118 -- of bidi when script is not specified (like ar, he)
2119 DROP TABLE IF EXISTS language_script_mapping;
2120 CREATE TABLE language_script_mapping (
2121 language_subtag varchar(25),
2122 script_subtag varchar(25),
2123 KEY `language_subtag` (`language_subtag`)
2124 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2126 DROP TABLE IF EXISTS `permissions`;
2127 CREATE TABLE `permissions` (
2128 `module_bit` int(11) NOT NULL DEFAULT 0,
2129 `code` varchar(30) DEFAULT NULL,
2130 `description` varchar(255) DEFAULT NULL,
2131 PRIMARY KEY (`module_bit`, `code`),
2132 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2133 ON DELETE CASCADE ON UPDATE CASCADE
2134 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2136 DROP TABLE IF EXISTS serialitems;
2137 CREATE TABLE serialitems (
2138 serialid int(11) NOT NULL,
2139 itemnumber int(11) NOT NULL,
2140 UNIQUE KEY `serialididx` (`serialid`)
2141 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2143 DROP TABLE IF EXISTS `user_permissions`;
2144 CREATE TABLE `user_permissions` (
2145 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2146 `module_bit` int(11) NOT NULL DEFAULT 0,
2147 `code` varchar(30) DEFAULT NULL,
2148 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2149 ON DELETE CASCADE ON UPDATE CASCADE,
2150 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2151 ON DELETE CASCADE ON UPDATE CASCADE
2152 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2155 -- Table structure for table `tmp_holdsqueue`
2158 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2159 CREATE TABLE `tmp_holdsqueue` (
2160 `biblionumber` int(11) default NULL,
2161 `itemnumber` int(11) default NULL,
2162 `barcode` varchar(20) default NULL,
2163 `surname` mediumtext NOT NULL,
2166 `borrowernumber` int(11) NOT NULL,
2167 `cardnumber` varchar(16) default NULL,
2168 `reservedate` date default NULL,
2170 `itemcallnumber` varchar(30) default NULL,
2171 `holdingbranch` varchar(10) default NULL,
2172 `pickbranch` varchar(10) default NULL,
2174 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2177 -- Table structure for table `message_queue`
2180 CREATE TABLE `message_queue` (
2181 `message_id` int(11) NOT NULL auto_increment,
2182 `borrowernumber` int(11) default 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 `to_address` mediumtext,
2189 `from_address` mediumtext,
2190 `content_type` text,
2191 KEY `message_id` (`message_id`),
2192 KEY `borrowernumber` (`borrowernumber`),
2193 KEY `message_transport_type` (`message_transport_type`),
2194 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2195 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2196 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2199 -- Table structure for table `message_transport_types`
2202 DROP TABLE IF EXISTS `message_transport_types`;
2203 CREATE TABLE `message_transport_types` (
2204 `message_transport_type` varchar(20) NOT NULL,
2205 PRIMARY KEY (`message_transport_type`)
2206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2209 -- Table structure for table `message_attributes`
2212 DROP TABLE IF EXISTS `message_attributes`;
2213 CREATE TABLE `message_attributes` (
2214 `message_attribute_id` int(11) NOT NULL auto_increment,
2215 `message_name` varchar(20) NOT NULL default '',
2216 `takes_days` tinyint(1) NOT NULL default '0',
2217 PRIMARY KEY (`message_attribute_id`),
2218 UNIQUE KEY `message_name` (`message_name`)
2219 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2222 -- Table structure for table `message_transports`
2225 DROP TABLE IF EXISTS `message_transports`;
2226 CREATE TABLE `message_transports` (
2227 `message_attribute_id` int(11) NOT NULL,
2228 `message_transport_type` varchar(20) NOT NULL,
2229 `is_digest` tinyint(1) NOT NULL default '0',
2230 `letter_module` varchar(20) NOT NULL default '',
2231 `letter_code` varchar(20) NOT NULL default '',
2232 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2233 KEY `message_transport_type` (`message_transport_type`),
2234 KEY `letter_module` (`letter_module`,`letter_code`),
2235 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2236 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2237 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2241 -- Table structure for table `borrower_message_preferences`
2244 DROP TABLE IF EXISTS `borrower_message_preferences`;
2245 CREATE TABLE `borrower_message_preferences` (
2246 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2247 `borrowernumber` int(11) NOT NULL default '0',
2248 `message_attribute_id` int(11) default '0',
2249 `days_in_advance` int(11) default '0',
2250 `wants_digest` tinyint(1) NOT NULL default '0',
2251 PRIMARY KEY (`borrower_message_preference_id`),
2252 KEY `borrowernumber` (`borrowernumber`),
2253 KEY `message_attribute_id` (`message_attribute_id`),
2254 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2255 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
2256 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2259 -- Table structure for table `borrower_message_transport_preferences`
2262 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2263 CREATE TABLE `borrower_message_transport_preferences` (
2264 `borrower_message_preference_id` int(11) NOT NULL default '0',
2265 `message_transport_type` varchar(20) NOT NULL default '0',
2266 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2267 KEY `message_transport_type` (`message_transport_type`),
2268 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,
2269 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
2270 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2272 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2273 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2274 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2275 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2276 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2277 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2278 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2279 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;