3 -- Host: localhost Database: koha30test
4 -- ------------------------------------------------------
5 -- Server version 4.1.22
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
11 /*!40101 SET NAMES utf8 */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19 -- Table structure for table `accountlines`
22 DROP TABLE IF EXISTS `accountlines`;
23 CREATE TABLE `accountlines` (
24 `borrowernumber` int(11) NOT NULL default 0,
25 `accountno` smallint(6) NOT NULL default 0,
26 `itemnumber` int(11) default NULL,
27 `date` date default NULL,
28 `amount` decimal(28,6) default NULL,
29 `description` mediumtext,
31 `accounttype` varchar(5) default NULL,
32 `amountoutstanding` decimal(28,6) default NULL,
33 `lastincrement` decimal(28,6) default NULL,
34 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
35 `notify_id` int(11) NOT NULL default 0,
36 `notify_level` int(2) NOT NULL default 0,
37 KEY `acctsborridx` (`borrowernumber`),
38 KEY `timeidx` (`timestamp`),
39 KEY `itemnumber` (`itemnumber`),
40 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
41 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
45 -- Table structure for table `accountoffsets`
48 DROP TABLE IF EXISTS `accountoffsets`;
49 CREATE TABLE `accountoffsets` (
50 `borrowernumber` int(11) NOT NULL default 0,
51 `accountno` smallint(6) NOT NULL default 0,
52 `offsetaccount` smallint(6) NOT NULL default 0,
53 `offsetamount` decimal(28,6) default NULL,
54 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
55 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
56 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
59 -- Table structure for table `action_logs`
62 DROP TABLE IF EXISTS `action_logs`;
63 CREATE TABLE `action_logs` (
64 `action_id` int(11) NOT NULL auto_increment,
65 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
66 `user` int(11) NOT NULL default 0,
69 `object` int(11) default NULL,
71 PRIMARY KEY (`action_id`),
72 KEY (`timestamp`,`user`)
73 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
76 -- Table structure for table `alert`
79 DROP TABLE IF EXISTS `alert`;
80 CREATE TABLE `alert` (
81 `alertid` int(11) NOT NULL auto_increment,
82 `borrowernumber` int(11) NOT NULL default 0,
83 `type` varchar(10) NOT NULL default '',
84 `externalid` varchar(20) NOT NULL default '',
85 PRIMARY KEY (`alertid`),
86 KEY `borrowernumber` (`borrowernumber`),
87 KEY `type` (`type`,`externalid`)
88 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
91 -- Table structure for table `aqbasket`
94 DROP TABLE IF EXISTS `aqbasket`;
95 CREATE TABLE `aqbasket` (
96 `basketno` int(11) NOT NULL auto_increment,
97 `creationdate` date default NULL,
98 `closedate` date default NULL,
99 `booksellerid` int(11) NOT NULL default 1,
100 `authorisedby` varchar(10) default NULL,
101 `booksellerinvoicenumber` mediumtext,
102 PRIMARY KEY (`basketno`),
103 KEY `booksellerid` (`booksellerid`),
104 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
105 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
108 -- Table structure for table `aqbookfund`
111 DROP TABLE IF EXISTS `aqbookfund`;
112 CREATE TABLE `aqbookfund` (
113 `bookfundid` varchar(10) NOT NULL default '',
114 `bookfundname` mediumtext,
115 `bookfundgroup` varchar(5) default NULL,
116 `branchcode` varchar(10) NOT NULL default '',
117 PRIMARY KEY (`bookfundid`,`branchcode`)
118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
121 -- Table structure for table `aqbooksellers`
124 DROP TABLE IF EXISTS `aqbooksellers`;
125 CREATE TABLE `aqbooksellers` (
126 `id` int(11) NOT NULL auto_increment,
127 `name` mediumtext NOT NULL,
128 `address1` mediumtext,
129 `address2` mediumtext,
130 `address3` mediumtext,
131 `address4` mediumtext,
132 `phone` varchar(30) default NULL,
133 `accountnumber` mediumtext,
134 `othersupplier` mediumtext,
135 `currency` varchar(3) NOT NULL default '',
136 `deliverydays` smallint(6) default NULL,
137 `followupdays` smallint(6) default NULL,
138 `followupscancel` smallint(6) default NULL,
139 `specialty` mediumtext,
140 `booksellerfax` mediumtext,
142 `bookselleremail` mediumtext,
143 `booksellerurl` mediumtext,
144 `contact` varchar(100) default NULL,
146 `url` varchar(255) default NULL,
147 `contpos` varchar(100) default NULL,
148 `contphone` varchar(100) default NULL,
149 `contfax` varchar(100) default NULL,
150 `contaltphone` varchar(100) default NULL,
151 `contemail` varchar(100) default NULL,
152 `contnotes` mediumtext,
153 `active` tinyint(4) default NULL,
154 `listprice` varchar(10) default NULL,
155 `invoiceprice` varchar(10) default NULL,
156 `gstreg` tinyint(4) default NULL,
157 `listincgst` tinyint(4) default NULL,
158 `invoiceincgst` tinyint(4) default NULL,
159 `discount` float(6,4) default NULL,
160 `fax` varchar(50) default NULL,
161 `nocalc` int(11) default NULL,
162 `invoicedisc` float(6,4) default NULL,
164 KEY `listprice` (`listprice`),
165 KEY `invoiceprice` (`invoiceprice`),
166 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
167 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
171 -- Table structure for table `aqbudget`
174 DROP TABLE IF EXISTS `aqbudget`;
175 CREATE TABLE `aqbudget` (
176 `bookfundid` varchar(10) NOT NULL default '',
177 `startdate` date NOT NULL default 0,
178 `enddate` date default NULL,
179 `budgetamount` decimal(13,2) default NULL,
180 `aqbudgetid` int(11) NOT NULL auto_increment,
181 `branchcode` varchar(10) default NULL,
182 PRIMARY KEY (`aqbudgetid`)
183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
186 -- Table structure for table `aqorderbreakdown`
189 DROP TABLE IF EXISTS `aqorderbreakdown`;
190 CREATE TABLE `aqorderbreakdown` (
191 `ordernumber` int(11) default NULL,
192 `linenumber` int(11) default NULL,
193 `branchcode` varchar(10) default NULL,
194 `bookfundid` varchar(10) NOT NULL default '',
195 `allocation` smallint(6) default NULL,
196 KEY `ordernumber` (`ordernumber`),
197 KEY `bookfundid` (`bookfundid`),
198 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
199 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
203 -- Table structure for table `aqorderdelivery`
206 DROP TABLE IF EXISTS `aqorderdelivery`;
207 CREATE TABLE `aqorderdelivery` (
208 `ordernumber` date default NULL,
209 `deliverynumber` smallint(6) NOT NULL default 0,
210 `deliverydate` varchar(18) default NULL,
211 `qtydelivered` smallint(6) default NULL,
212 `deliverycomments` mediumtext
213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
216 -- Table structure for table `aqorders`
219 DROP TABLE IF EXISTS `aqorders`;
220 CREATE TABLE `aqorders` (
221 `ordernumber` int(11) NOT NULL auto_increment,
222 `biblionumber` int(11) default NULL,
224 `entrydate` date default NULL,
225 `quantity` smallint(6) default NULL,
226 `currency` varchar(3) default NULL,
227 `listprice` decimal(28,6) default NULL,
228 `totalamount` decimal(28,6) default NULL,
229 `datereceived` date default NULL,
230 `booksellerinvoicenumber` mediumtext,
231 `freight` decimal(28,6) default NULL,
232 `unitprice` decimal(28,6) default NULL,
233 `quantityreceived` smallint(6) default NULL,
234 `cancelledby` varchar(10) default NULL,
235 `datecancellationprinted` date default NULL,
237 `supplierreference` mediumtext,
238 `purchaseordernumber` mediumtext,
239 `subscription` tinyint(1) default NULL,
240 `serialid` varchar(30) default NULL,
241 `basketno` int(11) default NULL,
242 `biblioitemnumber` int(11) default NULL,
243 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
244 `rrp` decimal(13,2) default NULL,
245 `ecost` decimal(13,2) default NULL,
246 `gst` decimal(13,2) default NULL,
247 `budgetdate` date default NULL,
248 `sort1` varchar(80) default NULL,
249 `sort2` varchar(80) default NULL,
250 PRIMARY KEY (`ordernumber`),
251 KEY `basketno` (`basketno`),
252 KEY `biblionumber` (`biblionumber`),
253 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
254 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
255 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
258 -- Table structure for table `auth_header`
261 DROP TABLE IF EXISTS `auth_header`;
262 CREATE TABLE `auth_header` (
263 `authid` bigint(20) unsigned NOT NULL auto_increment,
264 `authtypecode` varchar(10) NOT NULL default '',
265 `datecreated` date default NULL,
266 `datemodified` date default NULL,
267 `origincode` varchar(20) default NULL,
268 `authtrees` mediumtext,
270 `linkid` bigint(20) default NULL,
271 `marcxml` longtext NOT NULL,
272 PRIMARY KEY (`authid`),
273 KEY `origincode` (`origincode`)
274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
277 -- Table structure for table `auth_subfield_structure`
280 DROP TABLE IF EXISTS `auth_subfield_structure`;
281 CREATE TABLE `auth_subfield_structure` (
282 `authtypecode` varchar(10) NOT NULL default '',
283 `tagfield` varchar(3) NOT NULL default '',
284 `tagsubfield` varchar(1) NOT NULL default '',
285 `liblibrarian` varchar(255) NOT NULL default '',
286 `libopac` varchar(255) NOT NULL default '',
287 `repeatable` tinyint(4) NOT NULL default 0,
288 `mandatory` tinyint(4) NOT NULL default 0,
289 `tab` tinyint(1) default NULL,
290 `authorised_value` varchar(10) default NULL,
291 `value_builder` varchar(80) default NULL,
292 `seealso` varchar(255) default NULL,
293 `isurl` tinyint(1) default NULL,
294 `hidden` tinyint(3) NOT NULL default 0,
295 `linkid` tinyint(1) NOT NULL default 0,
296 `kohafield` varchar(45) NULL default '',
297 `frameworkcode` varchar(8) NOT NULL default '',
298 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
299 KEY `tab` (`authtypecode`,`tab`)
300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
303 -- Table structure for table `auth_tag_structure`
306 DROP TABLE IF EXISTS `auth_tag_structure`;
307 CREATE TABLE `auth_tag_structure` (
308 `authtypecode` varchar(10) NOT NULL default '',
309 `tagfield` varchar(3) NOT NULL default '',
310 `liblibrarian` varchar(255) NOT NULL default '',
311 `libopac` varchar(255) NOT NULL default '',
312 `repeatable` tinyint(4) NOT NULL default 0,
313 `mandatory` tinyint(4) NOT NULL default 0,
314 `authorised_value` varchar(10) default NULL,
315 PRIMARY KEY (`authtypecode`,`tagfield`),
316 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
320 -- Table structure for table `auth_types`
323 DROP TABLE IF EXISTS `auth_types`;
324 CREATE TABLE `auth_types` (
325 `authtypecode` varchar(10) NOT NULL default '',
326 `authtypetext` varchar(255) NOT NULL default '',
327 `auth_tag_to_report` varchar(3) NOT NULL default '',
328 `summary` mediumtext NOT NULL,
329 PRIMARY KEY (`authtypecode`)
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
333 -- Table structure for table `authorised_values`
336 DROP TABLE IF EXISTS `authorised_values`;
337 CREATE TABLE `authorised_values` (
338 `id` int(11) NOT NULL auto_increment,
339 `category` varchar(10) NOT NULL default '',
340 `authorised_value` varchar(80) NOT NULL default '',
341 `lib` varchar(80) default NULL,
342 `imageurl` varchar(200) default NULL,
344 KEY `name` (`category`)
345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
348 -- Table structure for table `biblio`
351 DROP TABLE IF EXISTS `biblio`;
352 CREATE TABLE `biblio` (
353 `biblionumber` int(11) NOT NULL auto_increment,
354 `frameworkcode` varchar(4) NOT NULL default '',
357 `unititle` mediumtext,
359 `serial` tinyint(1) default NULL,
360 `seriestitle` mediumtext,
361 `copyrightdate` smallint(6) default NULL,
362 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
363 `datecreated` DATE NOT NULL,
364 `abstract` mediumtext,
365 PRIMARY KEY (`biblionumber`),
366 KEY `blbnoidx` (`biblionumber`)
367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
370 -- Table structure for table `biblio_framework`
373 DROP TABLE IF EXISTS `biblio_framework`;
374 CREATE TABLE `biblio_framework` (
375 `frameworkcode` varchar(4) NOT NULL default '',
376 `frameworktext` varchar(255) NOT NULL default '',
377 PRIMARY KEY (`frameworkcode`)
378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
381 -- Table structure for table `biblioitems`
384 DROP TABLE IF EXISTS `biblioitems`;
385 CREATE TABLE `biblioitems` (
386 `biblioitemnumber` int(11) NOT NULL auto_increment,
387 `biblionumber` int(11) NOT NULL default 0,
390 `itemtype` varchar(10) default NULL,
391 `isbn` varchar(30) default NULL,
392 `issn` varchar(9) default NULL,
393 `publicationyear` text,
394 `publishercode` varchar(255) default NULL,
395 `volumedate` date default NULL,
397 `collectiontitle` mediumtext default NULL,
398 `collectionissn` text default NULL,
399 `collectionvolume` mediumtext default NULL,
400 `editionstatement` text default NULL,
401 `editionresponsibility` text default NULL,
402 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
403 `illus` varchar(255) default NULL,
404 `pages` varchar(255) default NULL,
406 `size` varchar(255) default NULL,
407 `place` varchar(255) default NULL,
408 `lccn` varchar(25) default NULL,
410 `url` varchar(255) default NULL,
411 `cn_source` varchar(10) default NULL,
412 `cn_class` varchar(30) default NULL,
413 `cn_item` varchar(10) default NULL,
414 `cn_suffix` varchar(10) default NULL,
415 `cn_sort` varchar(30) default NULL,
416 `totalissues` int(10),
417 `marcxml` longtext NOT NULL,
418 PRIMARY KEY (`biblioitemnumber`),
419 KEY `bibinoidx` (`biblioitemnumber`),
420 KEY `bibnoidx` (`biblionumber`),
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(30) 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(30) 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 `finedays` int(11) default NULL,
1085 `firstremind` int(11) default NULL,
1086 `chargeperiod` int(11) default NULL,
1087 `accountsent` int(11) default NULL,
1088 `chargename` varchar(100) default NULL,
1089 `maxissueqty` int(4) default NULL,
1090 `issuelength` int(4) default NULL,
1091 `branchcode` varchar(10) NOT NULL default '',
1092 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1093 KEY `categorycode` (`categorycode`),
1094 KEY `itemtype` (`itemtype`)
1095 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1098 -- Table structure for table `items`
1101 DROP TABLE IF EXISTS `items`;
1102 CREATE TABLE `items` (
1103 `itemnumber` int(11) NOT NULL auto_increment,
1104 `biblionumber` int(11) NOT NULL default 0,
1105 `biblioitemnumber` int(11) NOT NULL default 0,
1106 `barcode` varchar(20) default NULL,
1107 `dateaccessioned` date default NULL,
1108 `booksellerid` mediumtext default NULL,
1109 `homebranch` varchar(10) default NULL,
1110 `price` decimal(8,2) default NULL,
1111 `replacementprice` decimal(8,2) default NULL,
1112 `replacementpricedate` date default NULL,
1113 `datelastborrowed` date default NULL,
1114 `datelastseen` date default NULL,
1115 `stack` tinyint(1) default NULL,
1116 `notforloan` tinyint(1) NOT NULL default 0,
1117 `damaged` tinyint(1) NOT NULL default 0,
1118 `itemlost` tinyint(1) NOT NULL default 0,
1119 `wthdrawn` tinyint(1) NOT NULL default 0,
1120 `itemcallnumber` varchar(30) default NULL,
1121 `issues` smallint(6) default NULL,
1122 `renewals` smallint(6) default NULL,
1123 `reserves` smallint(6) default NULL,
1124 `restricted` tinyint(1) default NULL,
1125 `itemnotes` mediumtext,
1126 `holdingbranch` varchar(10) default NULL,
1127 `paidfor` mediumtext,
1128 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1129 `location` varchar(80) default NULL,
1130 `onloan` date default NULL,
1131 `cn_source` varchar(10) default NULL,
1132 `cn_sort` varchar(30) default NULL,
1133 `ccode` varchar(10) default NULL,
1134 `materials` varchar(10) default NULL,
1135 `uri` varchar(255) default NULL,
1136 `itype` varchar(10) default NULL,
1137 `more_subfields_xml` longtext default NULL,
1138 `enumchron` varchar(80) default NULL,
1139 `copynumber` varchar(32) default NULL,
1140 PRIMARY KEY (`itemnumber`),
1141 UNIQUE KEY `itembarcodeidx` (`barcode`),
1142 KEY `itembinoidx` (`biblioitemnumber`),
1143 KEY `itembibnoidx` (`biblionumber`),
1144 KEY `homebranch` (`homebranch`),
1145 KEY `holdingbranch` (`holdingbranch`),
1146 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1147 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1148 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1149 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1152 -- Table structure for table `itemtypes`
1155 DROP TABLE IF EXISTS `itemtypes`;
1156 CREATE TABLE `itemtypes` (
1157 `itemtype` varchar(10) NOT NULL default '',
1158 `description` mediumtext,
1159 `renewalsallowed` smallint(6) default NULL,
1160 `rentalcharge` double(16,4) default NULL,
1161 `notforloan` smallint(6) default NULL,
1162 `imageurl` varchar(200) default NULL,
1164 PRIMARY KEY (`itemtype`),
1165 UNIQUE KEY `itemtype` (`itemtype`)
1166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1169 -- Table structure for table `labels`
1172 DROP TABLE IF EXISTS `labels`;
1173 CREATE TABLE `labels` (
1174 `labelid` int(11) NOT NULL auto_increment,
1175 `batch_id` varchar(10) NOT NULL default 1,
1176 `itemnumber` varchar(100) NOT NULL default '',
1177 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1178 PRIMARY KEY (`labelid`)
1179 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1182 -- Table structure for table `labels_conf`
1185 DROP TABLE IF EXISTS `labels_conf`;
1186 CREATE TABLE `labels_conf` (
1187 `id` int(4) NOT NULL auto_increment,
1188 `barcodetype` char(100) default '',
1189 `title` int(1) default '0',
1190 `subtitle` int(1) default '0',
1191 `itemtype` int(1) default '0',
1192 `barcode` int(1) default '0',
1193 `dewey` int(1) default '0',
1194 `classification` int(1) default NULL,
1195 `subclass` int(1) default '0',
1196 `itemcallnumber` int(1) default '0',
1197 `author` int(1) default '0',
1198 `issn` int(1) default '0',
1199 `isbn` int(1) default '0',
1200 `startlabel` int(2) NOT NULL default '1',
1201 `printingtype` char(32) default 'BAR',
1202 `formatstring` varchar(64) default NULL,
1203 `layoutname` char(20) NOT NULL default 'TEST',
1204 `guidebox` int(1) default '0',
1205 `active` tinyint(1) default '1',
1206 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1207 `ccode` char(4) collate utf8_unicode_ci default NULL,
1208 `callnum_split` int(1) default NULL,
1209 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1214 -- Table structure for table `labels_profile`
1217 DROP TABLE IF EXISTS `labels_profile`;
1218 CREATE TABLE `labels_profile` (
1219 `tmpl_id` int(4) NOT NULL,
1220 `prof_id` int(4) NOT NULL,
1221 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1222 UNIQUE KEY `prof_id` (`prof_id`)
1223 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1226 -- Table structure for table `labels_templates`
1229 DROP TABLE IF EXISTS `labels_templates`;
1230 CREATE TABLE `labels_templates` (
1231 `tmpl_id` int(4) NOT NULL auto_increment,
1232 `tmpl_code` char(100) default '',
1233 `tmpl_desc` char(100) default '',
1234 `page_width` float default '0',
1235 `page_height` float default '0',
1236 `label_width` float default '0',
1237 `label_height` float default '0',
1238 `topmargin` float default '0',
1239 `leftmargin` float default '0',
1240 `cols` int(2) default '0',
1241 `rows` int(2) default '0',
1242 `colgap` float default '0',
1243 `rowgap` float default '0',
1244 `active` int(1) default NULL,
1245 `units` char(20) default 'PX',
1246 `fontsize` int(4) NOT NULL default '3',
1247 `font` char(10) NOT NULL default 'TR',
1248 PRIMARY KEY (`tmpl_id`)
1249 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1252 -- Table structure for table `letter`
1255 DROP TABLE IF EXISTS `letter`;
1256 CREATE TABLE `letter` (
1257 `module` varchar(20) NOT NULL default '',
1258 `code` varchar(20) NOT NULL default '',
1259 `name` varchar(100) NOT NULL default '',
1260 `title` varchar(200) NOT NULL default '',
1262 PRIMARY KEY (`module`,`code`)
1263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1266 -- Table structure for table `marc_subfield_structure`
1269 DROP TABLE IF EXISTS `marc_subfield_structure`;
1270 CREATE TABLE `marc_subfield_structure` (
1271 `tagfield` varchar(3) NOT NULL default '',
1272 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1273 `liblibrarian` varchar(255) NOT NULL default '',
1274 `libopac` varchar(255) NOT NULL default '',
1275 `repeatable` tinyint(4) NOT NULL default 0,
1276 `mandatory` tinyint(4) NOT NULL default 0,
1277 `kohafield` varchar(40) default NULL,
1278 `tab` tinyint(1) default NULL,
1279 `authorised_value` varchar(20) default NULL,
1280 `authtypecode` varchar(20) default NULL,
1281 `value_builder` varchar(80) default NULL,
1282 `isurl` tinyint(1) default NULL,
1283 `hidden` tinyint(1) default NULL,
1284 `frameworkcode` varchar(4) NOT NULL default '',
1285 `seealso` varchar(1100) default NULL,
1286 `link` varchar(80) default NULL,
1287 `defaultvalue` text default NULL,
1288 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1289 KEY `kohafield_2` (`kohafield`),
1290 KEY `tab` (`frameworkcode`,`tab`),
1291 KEY `kohafield` (`frameworkcode`,`kohafield`)
1292 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1295 -- Table structure for table `marc_tag_structure`
1298 DROP TABLE IF EXISTS `marc_tag_structure`;
1299 CREATE TABLE `marc_tag_structure` (
1300 `tagfield` varchar(3) NOT NULL default '',
1301 `liblibrarian` varchar(255) NOT NULL default '',
1302 `libopac` varchar(255) NOT NULL default '',
1303 `repeatable` tinyint(4) NOT NULL default 0,
1304 `mandatory` tinyint(4) NOT NULL default 0,
1305 `authorised_value` varchar(10) default NULL,
1306 `frameworkcode` varchar(4) NOT NULL default '',
1307 PRIMARY KEY (`frameworkcode`,`tagfield`)
1308 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1311 -- Table structure for table `marc_matchers`
1314 DROP TABLE IF EXISTS `marc_matchers`;
1315 CREATE TABLE `marc_matchers` (
1316 `matcher_id` int(11) NOT NULL auto_increment,
1317 `code` varchar(10) NOT NULL default '',
1318 `description` varchar(255) NOT NULL default '',
1319 `record_type` varchar(10) NOT NULL default 'biblio',
1320 `threshold` int(11) NOT NULL default 0,
1321 PRIMARY KEY (`matcher_id`),
1322 KEY `code` (`code`),
1323 KEY `record_type` (`record_type`)
1324 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1327 -- Table structure for table `matchpoints`
1329 DROP TABLE IF EXISTS `matchpoints`;
1330 CREATE TABLE `matchpoints` (
1331 `matcher_id` int(11) NOT NULL,
1332 `matchpoint_id` int(11) NOT NULL auto_increment,
1333 `search_index` varchar(30) NOT NULL default '',
1334 `score` int(11) NOT NULL default 0,
1335 PRIMARY KEY (`matchpoint_id`),
1336 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1337 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1338 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1342 -- Table structure for table `matchpoint_components`
1344 DROP TABLE IF EXISTS `matchpoint_components`;
1345 CREATE TABLE `matchpoint_components` (
1346 `matchpoint_id` int(11) NOT NULL,
1347 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1348 sequence int(11) NOT NULL default 0,
1349 tag varchar(3) NOT NULL default '',
1350 subfields varchar(40) NOT NULL default '',
1351 offset int(4) NOT NULL default 0,
1352 length int(4) NOT NULL default 0,
1353 PRIMARY KEY (`matchpoint_component_id`),
1354 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1355 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1356 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1357 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1360 -- Table structure for table `matcher_component_norms`
1362 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1363 CREATE TABLE `matchpoint_component_norms` (
1364 `matchpoint_component_id` int(11) NOT NULL,
1365 `sequence` int(11) NOT NULL default 0,
1366 `norm_routine` varchar(50) NOT NULL default '',
1367 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1368 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1369 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1370 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1373 -- Table structure for table `matcher_matchpoints`
1375 DROP TABLE IF EXISTS `matcher_matchpoints`;
1376 CREATE TABLE `matcher_matchpoints` (
1377 `matcher_id` int(11) NOT NULL,
1378 `matchpoint_id` int(11) NOT NULL,
1379 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1380 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1381 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1382 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1383 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1386 -- Table structure for table `matchchecks`
1388 DROP TABLE IF EXISTS `matchchecks`;
1389 CREATE TABLE `matchchecks` (
1390 `matcher_id` int(11) NOT NULL,
1391 `matchcheck_id` int(11) NOT NULL auto_increment,
1392 `source_matchpoint_id` int(11) NOT NULL,
1393 `target_matchpoint_id` int(11) NOT NULL,
1394 PRIMARY KEY (`matchcheck_id`),
1395 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1396 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1397 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1398 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1399 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1400 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1401 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1404 -- Table structure for table `notifys`
1407 DROP TABLE IF EXISTS `notifys`;
1408 CREATE TABLE `notifys` (
1409 `notify_id` int(11) NOT NULL default 0,
1410 `borrowernumber` int(11) NOT NULL default 0,
1411 `itemnumber` int(11) NOT NULL default 0,
1412 `notify_date` date default NULL,
1413 `notify_send_date` date default NULL,
1414 `notify_level` int(1) NOT NULL default 0,
1415 `method` varchar(20) NOT NULL default ''
1416 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1419 -- Table structure for table `nozebra`
1422 DROP TABLE IF EXISTS `nozebra`;
1423 CREATE TABLE `nozebra` (
1424 `server` varchar(20) NOT NULL,
1425 `indexname` varchar(40) NOT NULL,
1426 `value` varchar(250) NOT NULL,
1427 `biblionumbers` longtext NOT NULL,
1428 KEY `indexname` (`server`,`indexname`),
1429 KEY `value` (`server`,`value`))
1430 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1433 -- Table structure for table `old_issues`
1436 DROP TABLE IF EXISTS `old_issues`;
1437 CREATE TABLE `old_issues` (
1438 `borrowernumber` int(11) default NULL,
1439 `itemnumber` int(11) default NULL,
1440 `date_due` date default NULL,
1441 `branchcode` varchar(10) default NULL,
1442 `issuingbranch` varchar(18) default NULL,
1443 `returndate` date default NULL,
1444 `lastreneweddate` date default NULL,
1445 `return` varchar(4) default NULL,
1446 `renewals` tinyint(4) default NULL,
1447 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1448 `issuedate` date default NULL,
1449 KEY `old_issuesborridx` (`borrowernumber`),
1450 KEY `old_issuesitemidx` (`itemnumber`),
1451 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1452 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1453 ON DELETE SET NULL ON UPDATE SET NULL,
1454 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1455 ON DELETE SET NULL ON UPDATE SET NULL
1456 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1459 -- Table structure for table `old_reserves`
1461 DROP TABLE IF EXISTS `old_reserves`;
1462 CREATE TABLE `old_reserves` (
1463 `borrowernumber` int(11) default NULL,
1464 `reservedate` date default NULL,
1465 `biblionumber` int(11) default NULL,
1466 `constrainttype` varchar(1) default NULL,
1467 `branchcode` varchar(10) default NULL,
1468 `notificationdate` date default NULL,
1469 `reminderdate` date default NULL,
1470 `cancellationdate` date default NULL,
1471 `reservenotes` mediumtext,
1472 `priority` smallint(6) default NULL,
1473 `found` varchar(1) default NULL,
1474 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1475 `itemnumber` int(11) default NULL,
1476 `waitingdate` date default NULL,
1477 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1478 KEY `old_reserves_biblionumber` (`biblionumber`),
1479 KEY `old_reserves_itemnumber` (`itemnumber`),
1480 KEY `old_reserves_branchcode` (`branchcode`),
1481 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1482 ON DELETE SET NULL ON UPDATE SET NULL,
1483 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1484 ON DELETE SET NULL ON UPDATE SET NULL,
1485 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1486 ON DELETE SET NULL ON UPDATE SET NULL
1487 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1490 -- Table structure for table `opac_news`
1493 DROP TABLE IF EXISTS `opac_news`;
1494 CREATE TABLE `opac_news` (
1495 `idnew` int(10) unsigned NOT NULL auto_increment,
1496 `title` varchar(250) NOT NULL default '',
1497 `new` text NOT NULL,
1498 `lang` varchar(25) NOT NULL default '',
1499 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1500 `expirationdate` date default NULL,
1501 `number` int(11) default NULL,
1502 PRIMARY KEY (`idnew`)
1503 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1506 -- Table structure for table `overduerules`
1509 DROP TABLE IF EXISTS `overduerules`;
1510 CREATE TABLE `overduerules` (
1511 `branchcode` varchar(10) NOT NULL default '',
1512 `categorycode` varchar(10) NOT NULL default '',
1513 `delay1` int(4) default 0,
1514 `letter1` varchar(20) default NULL,
1515 `debarred1` varchar(1) default 0,
1516 `delay2` int(4) default 0,
1517 `debarred2` varchar(1) default 0,
1518 `letter2` varchar(20) default NULL,
1519 `delay3` int(4) default 0,
1520 `letter3` varchar(20) default NULL,
1521 `debarred3` int(1) default 0,
1522 PRIMARY KEY (`branchcode`,`categorycode`)
1523 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1526 -- Table structure for table `patroncards`
1529 DROP TABLE IF EXISTS `patroncards`;
1530 CREATE TABLE `patroncards` (
1531 `cardid` int(11) NOT NULL auto_increment,
1532 `batch_id` varchar(10) NOT NULL default '1',
1533 `borrowernumber` int(11) NOT NULL,
1534 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1535 PRIMARY KEY (`cardid`),
1536 KEY `patroncards_ibfk_1` (`borrowernumber`),
1537 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1538 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1541 -- Table structure for table `patronimage`
1544 DROP TABLE IF EXISTS `patronimage`;
1545 CREATE TABLE `patronimage` (
1546 `cardnumber` varchar(16) NOT NULL,
1547 `mimetype` varchar(15) NOT NULL,
1548 `imagefile` mediumblob NOT NULL,
1549 PRIMARY KEY (`cardnumber`),
1550 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1551 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1554 -- Table structure for table `printers`
1557 DROP TABLE IF EXISTS `printers`;
1558 CREATE TABLE `printers` (
1559 `printername` varchar(40) NOT NULL default '',
1560 `printqueue` varchar(20) default NULL,
1561 `printtype` varchar(20) default NULL,
1562 PRIMARY KEY (`printername`)
1563 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1566 -- Table structure for table `printers_profile`
1569 DROP TABLE IF EXISTS `printers_profile`;
1570 CREATE TABLE `printers_profile` (
1571 `prof_id` int(4) NOT NULL auto_increment,
1572 `printername` varchar(40) NOT NULL,
1573 `tmpl_id` int(4) NOT NULL,
1574 `paper_bin` varchar(20) NOT NULL,
1575 `offset_horz` float default NULL,
1576 `offset_vert` float default NULL,
1577 `creep_horz` float default NULL,
1578 `creep_vert` float default NULL,
1579 `unit` char(20) NOT NULL default 'POINT',
1580 PRIMARY KEY (`prof_id`),
1581 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1582 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1583 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1586 -- Table structure for table `repeatable_holidays`
1589 DROP TABLE IF EXISTS `repeatable_holidays`;
1590 CREATE TABLE `repeatable_holidays` (
1591 `id` int(11) NOT NULL auto_increment,
1592 `branchcode` varchar(10) NOT NULL default '',
1593 `weekday` smallint(6) default NULL,
1594 `day` smallint(6) default NULL,
1595 `month` smallint(6) default NULL,
1596 `title` varchar(50) NOT NULL default '',
1597 `description` text NOT NULL,
1599 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1602 -- Table structure for table `reports_dictionary`
1605 DROP TABLE IF EXISTS `reports_dictionary`;
1606 CREATE TABLE reports_dictionary (
1607 `id` int(11) NOT NULL auto_increment,
1608 `name` varchar(255) default NULL,
1610 `date_created` datetime default NULL,
1611 `date_modified` datetime default NULL,
1613 `area` int(11) default NULL,
1615 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1618 -- Table structure for table `reserveconstraints`
1621 DROP TABLE IF EXISTS `reserveconstraints`;
1622 CREATE TABLE `reserveconstraints` (
1623 `borrowernumber` int(11) NOT NULL default 0,
1624 `reservedate` date default NULL,
1625 `biblionumber` int(11) NOT NULL default 0,
1626 `biblioitemnumber` int(11) default NULL,
1627 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1628 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1631 -- Table structure for table `reserves`
1634 DROP TABLE IF EXISTS `reserves`;
1635 CREATE TABLE `reserves` (
1636 `borrowernumber` int(11) NOT NULL default 0,
1637 `reservedate` date default NULL,
1638 `biblionumber` int(11) NOT NULL default 0,
1639 `constrainttype` varchar(1) default NULL,
1640 `branchcode` varchar(10) default NULL,
1641 `notificationdate` date default NULL,
1642 `reminderdate` date default NULL,
1643 `cancellationdate` date default NULL,
1644 `reservenotes` mediumtext,
1645 `priority` smallint(6) default NULL,
1646 `found` varchar(1) default NULL,
1647 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1648 `itemnumber` int(11) default NULL,
1649 `waitingdate` date default NULL,
1650 KEY `borrowernumber` (`borrowernumber`),
1651 KEY `biblionumber` (`biblionumber`),
1652 KEY `itemnumber` (`itemnumber`),
1653 KEY `branchcode` (`branchcode`),
1654 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1655 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1656 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1657 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1658 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1661 -- Table structure for table `reviews`
1664 DROP TABLE IF EXISTS `reviews`;
1665 CREATE TABLE `reviews` (
1666 `reviewid` int(11) NOT NULL auto_increment,
1667 `borrowernumber` int(11) default NULL,
1668 `biblionumber` int(11) default NULL,
1670 `approved` tinyint(4) default NULL,
1671 `datereviewed` datetime default NULL,
1672 PRIMARY KEY (`reviewid`)
1673 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1676 -- Table structure for table `roadtype`
1679 DROP TABLE IF EXISTS `roadtype`;
1680 CREATE TABLE `roadtype` (
1681 `roadtypeid` int(11) NOT NULL auto_increment,
1682 `road_type` varchar(100) NOT NULL default '',
1683 PRIMARY KEY (`roadtypeid`)
1684 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1687 -- Table structure for table `saved_sql`
1690 DROP TABLE IF EXISTS `saved_sql`;
1691 CREATE TABLE saved_sql (
1692 `id` int(11) NOT NULL auto_increment,
1693 `borrowernumber` int(11) default NULL,
1694 `date_created` datetime default NULL,
1695 `last_modified` datetime default NULL,
1697 `last_run` datetime default NULL,
1698 `report_name` varchar(255) default NULL,
1699 `type` varchar(255) default NULL,
1702 KEY boridx (`borrowernumber`)
1703 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1707 -- Table structure for `saved_reports`
1710 DROP TABLE IF EXISTS `saved_reports`;
1711 CREATE TABLE saved_reports (
1712 `id` int(11) NOT NULL auto_increment,
1713 `report_id` int(11) default NULL,
1715 `date_run` datetime default NULL,
1717 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1721 -- Table structure for table `serial`
1724 DROP TABLE IF EXISTS `serial`;
1725 CREATE TABLE `serial` (
1726 `serialid` int(11) NOT NULL auto_increment,
1727 `biblionumber` varchar(100) NOT NULL default '',
1728 `subscriptionid` varchar(100) NOT NULL default '',
1729 `serialseq` varchar(100) NOT NULL default '',
1730 `status` tinyint(4) NOT NULL default 0,
1731 `planneddate` date default NULL,
1733 `publisheddate` date default NULL,
1734 `itemnumber` text default NULL,
1735 `claimdate` date default NULL,
1736 `routingnotes` text,
1737 PRIMARY KEY (`serialid`)
1738 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1741 -- Table structure for table `sessions`
1744 DROP TABLE IF EXISTS sessions;
1745 CREATE TABLE sessions (
1746 `id` varchar(32) NOT NULL,
1747 `a_session` text NOT NULL,
1749 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1752 -- Table structure for table `special_holidays`
1755 DROP TABLE IF EXISTS `special_holidays`;
1756 CREATE TABLE `special_holidays` (
1757 `id` int(11) NOT NULL auto_increment,
1758 `branchcode` varchar(10) NOT NULL default '',
1759 `day` smallint(6) NOT NULL default 0,
1760 `month` smallint(6) NOT NULL default 0,
1761 `year` smallint(6) NOT NULL default 0,
1762 `isexception` smallint(1) NOT NULL default 1,
1763 `title` varchar(50) NOT NULL default '',
1764 `description` text NOT NULL,
1766 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1769 -- Table structure for table `statistics`
1772 DROP TABLE IF EXISTS `statistics`;
1773 CREATE TABLE `statistics` (
1774 `datetime` datetime default NULL,
1775 `branch` varchar(10) default NULL,
1776 `proccode` varchar(4) default NULL,
1777 `value` double(16,4) default NULL,
1778 `type` varchar(16) default NULL,
1780 `usercode` varchar(10) default NULL,
1781 `itemnumber` int(11) default NULL,
1782 `itemtype` varchar(10) default NULL,
1783 `borrowernumber` int(11) default NULL,
1784 `associatedborrower` int(11) default NULL,
1785 KEY `timeidx` (`datetime`)
1786 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1789 -- Table structure for table `stopwords`
1792 DROP TABLE IF EXISTS `stopwords`;
1793 CREATE TABLE `stopwords` (
1794 `word` varchar(255) default NULL
1795 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1798 -- Table structure for table `subscription`
1801 DROP TABLE IF EXISTS `subscription`;
1802 CREATE TABLE `subscription` (
1803 `biblionumber` int(11) NOT NULL default 0,
1804 `subscriptionid` int(11) NOT NULL auto_increment,
1805 `librarian` varchar(100) default '',
1806 `startdate` date default NULL,
1807 `aqbooksellerid` int(11) default 0,
1808 `cost` int(11) default 0,
1809 `aqbudgetid` int(11) default 0,
1810 `weeklength` int(11) default 0,
1811 `monthlength` int(11) default 0,
1812 `numberlength` int(11) default 0,
1813 `periodicity` tinyint(4) default 0,
1814 `dow` varchar(100) default '',
1815 `numberingmethod` varchar(100) default '',
1817 `status` varchar(100) NOT NULL default '',
1818 `add1` int(11) default 0,
1819 `every1` int(11) default 0,
1820 `whenmorethan1` int(11) default 0,
1821 `setto1` int(11) default NULL,
1822 `lastvalue1` int(11) default NULL,
1823 `add2` int(11) default 0,
1824 `every2` int(11) default 0,
1825 `whenmorethan2` int(11) default 0,
1826 `setto2` int(11) default NULL,
1827 `lastvalue2` int(11) default NULL,
1828 `add3` int(11) default 0,
1829 `every3` int(11) default 0,
1830 `innerloop1` int(11) default 0,
1831 `innerloop2` int(11) default 0,
1832 `innerloop3` int(11) default 0,
1833 `whenmorethan3` int(11) default 0,
1834 `setto3` int(11) default NULL,
1835 `lastvalue3` int(11) default NULL,
1836 `issuesatonce` tinyint(3) NOT NULL default 1,
1837 `firstacquidate` date default NULL,
1838 `manualhistory` tinyint(1) NOT NULL default 0,
1839 `irregularity` text,
1840 `letter` varchar(20) default NULL,
1841 `numberpattern` tinyint(3) default 0,
1842 `distributedto` text,
1843 `internalnotes` longtext,
1845 `branchcode` varchar(10) NOT NULL default '',
1846 `hemisphere` tinyint(3) default 0,
1847 `lastbranch` varchar(10),
1848 `serialsadditems` tinyint(1) NOT NULL default '0',
1849 PRIMARY KEY (`subscriptionid`)
1850 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1853 -- Table structure for table `subscriptionhistory`
1856 DROP TABLE IF EXISTS `subscriptionhistory`;
1857 CREATE TABLE `subscriptionhistory` (
1858 `biblionumber` int(11) NOT NULL default 0,
1859 `subscriptionid` int(11) NOT NULL default 0,
1860 `histstartdate` date default NULL,
1861 `enddate` date default NULL,
1862 `missinglist` longtext NOT NULL,
1863 `recievedlist` longtext NOT NULL,
1864 `opacnote` varchar(150) NOT NULL default '',
1865 `librariannote` varchar(150) NOT NULL default '',
1866 PRIMARY KEY (`subscriptionid`),
1867 KEY `biblionumber` (`biblionumber`)
1868 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1871 -- Table structure for table `subscriptionroutinglist`
1874 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1875 CREATE TABLE `subscriptionroutinglist` (
1876 `routingid` int(11) NOT NULL auto_increment,
1877 `borrowernumber` int(11) default NULL,
1878 `ranking` int(11) default NULL,
1879 `subscriptionid` int(11) default NULL,
1880 PRIMARY KEY (`routingid`)
1881 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1884 -- Table structure for table `suggestions`
1887 DROP TABLE IF EXISTS `suggestions`;
1888 CREATE TABLE `suggestions` (
1889 `suggestionid` int(8) NOT NULL auto_increment,
1890 `suggestedby` int(11) NOT NULL default 0,
1891 `managedby` int(11) default NULL,
1892 `STATUS` varchar(10) NOT NULL default '',
1894 `author` varchar(80) default NULL,
1895 `title` varchar(80) default NULL,
1896 `copyrightdate` smallint(6) default NULL,
1897 `publishercode` varchar(255) default NULL,
1898 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1899 `volumedesc` varchar(255) default NULL,
1900 `publicationyear` smallint(6) default 0,
1901 `place` varchar(255) default NULL,
1902 `isbn` varchar(30) default NULL,
1903 `mailoverseeing` smallint(1) default 0,
1904 `biblionumber` int(11) default NULL,
1906 PRIMARY KEY (`suggestionid`),
1907 KEY `suggestedby` (`suggestedby`),
1908 KEY `managedby` (`managedby`)
1909 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1912 -- Table structure for table `systempreferences`
1915 DROP TABLE IF EXISTS `systempreferences`;
1916 CREATE TABLE `systempreferences` (
1917 `variable` varchar(50) NOT NULL default '',
1919 `options` mediumtext,
1921 `type` varchar(20) default NULL,
1922 PRIMARY KEY (`variable`)
1923 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1926 -- Table structure for table `tags`
1929 DROP TABLE IF EXISTS `tags`;
1930 CREATE TABLE `tags` (
1931 `entry` varchar(255) NOT NULL default '',
1932 `weight` bigint(20) NOT NULL default 0,
1933 PRIMARY KEY (`entry`)
1934 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1937 -- Table structure for table `tags_all`
1940 DROP TABLE IF EXISTS `tags_all`;
1941 CREATE TABLE `tags_all` (
1942 `tag_id` int(11) NOT NULL auto_increment,
1943 `borrowernumber` int(11) NOT NULL,
1944 `biblionumber` int(11) NOT NULL,
1945 `term` varchar(255) NOT NULL,
1946 `language` int(4) default NULL,
1947 `date_created` datetime NOT NULL,
1948 PRIMARY KEY (`tag_id`),
1949 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1950 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1951 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1952 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1953 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1954 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1955 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1958 -- Table structure for table `tags_approval`
1961 DROP TABLE IF EXISTS `tags_approval`;
1962 CREATE TABLE `tags_approval` (
1963 `term` varchar(255) NOT NULL,
1964 `approved` int(1) NOT NULL default '0',
1965 `date_approved` datetime default NULL,
1966 `approved_by` int(11) default NULL,
1967 `weight_total` int(9) NOT NULL default '1',
1968 PRIMARY KEY (`term`),
1969 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1970 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1971 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1972 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1975 -- Table structure for table `tags_index`
1978 DROP TABLE IF EXISTS `tags_index`;
1979 CREATE TABLE `tags_index` (
1980 `term` varchar(255) NOT NULL,
1981 `biblionumber` int(11) NOT NULL,
1982 `weight` int(9) NOT NULL default '1',
1983 PRIMARY KEY (`term`,`biblionumber`),
1984 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1985 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1986 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1987 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1988 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1989 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1992 -- Table structure for table `userflags`
1995 DROP TABLE IF EXISTS `userflags`;
1996 CREATE TABLE `userflags` (
1997 `bit` int(11) NOT NULL default 0,
1998 `flag` varchar(30) default NULL,
1999 `flagdesc` varchar(255) default NULL,
2000 `defaulton` int(11) default NULL,
2002 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2005 -- Table structure for table `virtualshelves`
2008 DROP TABLE IF EXISTS `virtualshelves`;
2009 CREATE TABLE `virtualshelves` (
2010 `shelfnumber` int(11) NOT NULL auto_increment,
2011 `shelfname` varchar(255) default NULL,
2012 `owner` varchar(80) default NULL,
2013 `category` varchar(1) default NULL,
2014 `sortfield` varchar(16) default NULL,
2015 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2016 PRIMARY KEY (`shelfnumber`)
2017 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2020 -- Table structure for table `virtualshelfcontents`
2023 DROP TABLE IF EXISTS `virtualshelfcontents`;
2024 CREATE TABLE `virtualshelfcontents` (
2025 `shelfnumber` int(11) NOT NULL default 0,
2026 `biblionumber` int(11) NOT NULL default 0,
2027 `flags` int(11) default NULL,
2028 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2029 KEY `shelfnumber` (`shelfnumber`),
2030 KEY `biblionumber` (`biblionumber`),
2031 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2032 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2033 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2036 -- Table structure for table `z3950servers`
2039 DROP TABLE IF EXISTS `z3950servers`;
2040 CREATE TABLE `z3950servers` (
2041 `host` varchar(255) default NULL,
2042 `port` int(11) default NULL,
2043 `db` varchar(255) default NULL,
2044 `userid` varchar(255) default NULL,
2045 `password` varchar(255) default NULL,
2047 `id` int(11) NOT NULL auto_increment,
2048 `checked` smallint(6) default NULL,
2049 `rank` int(11) default NULL,
2050 `syntax` varchar(80) default NULL,
2052 `position` enum('primary','secondary','') NOT NULL default 'primary',
2053 `type` enum('zed','opensearch') NOT NULL default 'zed',
2054 `encoding` text default NULL,
2055 `description` text NOT NULL,
2057 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2060 -- Table structure for table `zebraqueue`
2063 DROP TABLE IF EXISTS `zebraqueue`;
2064 CREATE TABLE `zebraqueue` (
2065 `id` int(11) NOT NULL auto_increment,
2066 `biblio_auth_number` int(11) NOT NULL default '0',
2067 `operation` char(20) NOT NULL default '',
2068 `server` char(20) NOT NULL default '',
2069 `done` int(11) NOT NULL default '0',
2070 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2072 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2073 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2075 DROP TABLE IF EXISTS `services_throttle`;
2076 CREATE TABLE `services_throttle` (
2077 `service_type` varchar(10) NOT NULL default '',
2078 `service_count` varchar(45) default NULL,
2079 PRIMARY KEY (`service_type`)
2080 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2082 -- http://www.w3.org/International/articles/language-tags/
2085 DROP TABLE IF EXISTS language_subtag_registry;
2086 CREATE TABLE language_subtag_registry (
2088 type varchar(25), -- language-script-region-variant-extension-privateuse
2089 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2091 KEY `subtag` (`subtag`)
2092 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2094 -- TODO: add suppress_scripts
2095 -- this maps three letter codes defined in iso639.2 back to their
2096 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2097 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2098 CREATE TABLE language_rfc4646_to_iso639 (
2099 rfc4646_subtag varchar(25),
2100 iso639_2_code varchar(25),
2101 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2104 DROP TABLE IF EXISTS language_descriptions;
2105 CREATE TABLE language_descriptions (
2109 description varchar(255),
2111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2113 -- bi-directional support, keyed by script subcode
2114 DROP TABLE IF EXISTS language_script_bidi;
2115 CREATE TABLE language_script_bidi (
2116 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2117 bidi varchar(3), -- rtl ltr
2118 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2119 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2121 -- TODO: need to map language subtags to script subtags for detection
2122 -- of bidi when script is not specified (like ar, he)
2123 DROP TABLE IF EXISTS language_script_mapping;
2124 CREATE TABLE language_script_mapping (
2125 language_subtag varchar(25),
2126 script_subtag varchar(25),
2127 KEY `language_subtag` (`language_subtag`)
2128 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2130 DROP TABLE IF EXISTS `permissions`;
2131 CREATE TABLE `permissions` (
2132 `module_bit` int(11) NOT NULL DEFAULT 0,
2133 `code` varchar(30) DEFAULT NULL,
2134 `description` varchar(255) DEFAULT NULL,
2135 PRIMARY KEY (`module_bit`, `code`),
2136 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2137 ON DELETE CASCADE ON UPDATE CASCADE
2138 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2140 DROP TABLE IF EXISTS `serialitems`;
2141 CREATE TABLE `serialitems` (
2142 `itemnumber` int(11) NOT NULL,
2143 `serialid` int(11) NOT NULL,
2144 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2145 KEY `serialitems_sfk_1` (`serialid`),
2146 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2147 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2149 DROP TABLE IF EXISTS `user_permissions`;
2150 CREATE TABLE `user_permissions` (
2151 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2152 `module_bit` int(11) NOT NULL DEFAULT 0,
2153 `code` varchar(30) DEFAULT NULL,
2154 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2155 ON DELETE CASCADE ON UPDATE CASCADE,
2156 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2157 ON DELETE CASCADE ON UPDATE CASCADE
2158 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2161 -- Table structure for table `tmp_holdsqueue`
2164 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2165 CREATE TABLE `tmp_holdsqueue` (
2166 `biblionumber` int(11) default NULL,
2167 `itemnumber` int(11) default NULL,
2168 `barcode` varchar(20) default NULL,
2169 `surname` mediumtext NOT NULL,
2172 `borrowernumber` int(11) NOT NULL,
2173 `cardnumber` varchar(16) default NULL,
2174 `reservedate` date default NULL,
2176 `itemcallnumber` varchar(30) default NULL,
2177 `holdingbranch` varchar(10) default NULL,
2178 `pickbranch` varchar(10) default NULL,
2180 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2183 -- Table structure for table `message_queue`
2186 DROP TABLE IF EXISTS `message_queue`;
2187 CREATE TABLE `message_queue` (
2188 `message_id` int(11) NOT NULL auto_increment,
2189 `borrowernumber` int(11) default NULL,
2192 `message_transport_type` varchar(20) NOT NULL,
2193 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2194 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2195 `to_address` mediumtext,
2196 `from_address` mediumtext,
2197 `content_type` text,
2198 KEY `message_id` (`message_id`),
2199 KEY `borrowernumber` (`borrowernumber`),
2200 KEY `message_transport_type` (`message_transport_type`),
2201 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2202 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2203 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2206 -- Table structure for table `message_transport_types`
2209 DROP TABLE IF EXISTS `message_transport_types`;
2210 CREATE TABLE `message_transport_types` (
2211 `message_transport_type` varchar(20) NOT NULL,
2212 PRIMARY KEY (`message_transport_type`)
2213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2216 -- Table structure for table `message_attributes`
2219 DROP TABLE IF EXISTS `message_attributes`;
2220 CREATE TABLE `message_attributes` (
2221 `message_attribute_id` int(11) NOT NULL auto_increment,
2222 `message_name` varchar(20) NOT NULL default '',
2223 `takes_days` tinyint(1) NOT NULL default '0',
2224 PRIMARY KEY (`message_attribute_id`),
2225 UNIQUE KEY `message_name` (`message_name`)
2226 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2229 -- Table structure for table `message_transports`
2232 DROP TABLE IF EXISTS `message_transports`;
2233 CREATE TABLE `message_transports` (
2234 `message_attribute_id` int(11) NOT NULL,
2235 `message_transport_type` varchar(20) NOT NULL,
2236 `is_digest` tinyint(1) NOT NULL default '0',
2237 `letter_module` varchar(20) NOT NULL default '',
2238 `letter_code` varchar(20) NOT NULL default '',
2239 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2240 KEY `message_transport_type` (`message_transport_type`),
2241 KEY `letter_module` (`letter_module`,`letter_code`),
2242 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2243 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2244 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2245 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2248 -- Table structure for table `borrower_message_preferences`
2251 DROP TABLE IF EXISTS `borrower_message_preferences`;
2252 CREATE TABLE `borrower_message_preferences` (
2253 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2254 `borrowernumber` int(11) NOT NULL default '0',
2255 `message_attribute_id` int(11) default '0',
2256 `days_in_advance` int(11) default '0',
2257 `wants_digest` tinyint(1) NOT NULL default '0',
2258 PRIMARY KEY (`borrower_message_preference_id`),
2259 KEY `borrowernumber` (`borrowernumber`),
2260 KEY `message_attribute_id` (`message_attribute_id`),
2261 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2262 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
2263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2266 -- Table structure for table `borrower_message_transport_preferences`
2269 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2270 CREATE TABLE `borrower_message_transport_preferences` (
2271 `borrower_message_preference_id` int(11) NOT NULL default '0',
2272 `message_transport_type` varchar(20) NOT NULL default '0',
2273 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2274 KEY `message_transport_type` (`message_transport_type`),
2275 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,
2276 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
2277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2279 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2280 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2281 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2282 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2283 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2284 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2285 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2286 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;