3 -- Host: localhost Database: koha30test
4 -- ------------------------------------------------------
5 -- Server version 4.1.22
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
11 /*!40101 SET NAMES utf8 */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19 -- Table structure for table `accountlines`
22 DROP TABLE IF EXISTS `accountlines`;
23 CREATE TABLE `accountlines` (
24 `borrowernumber` int(11) NOT NULL default 0,
25 `accountno` smallint(6) NOT NULL default 0,
26 `itemnumber` int(11) default NULL,
27 `date` date default NULL,
28 `amount` decimal(28,6) default NULL,
29 `description` mediumtext,
31 `accounttype` varchar(5) default NULL,
32 `amountoutstanding` decimal(28,6) default NULL,
33 `lastincrement` decimal(28,6) default NULL,
34 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
35 `notify_id` int(11) NOT NULL default 0,
36 `notify_level` int(2) NOT NULL default 0,
37 KEY `acctsborridx` (`borrowernumber`),
38 KEY `timeidx` (`timestamp`),
39 KEY `itemnumber` (`itemnumber`),
40 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
41 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
45 -- Table structure for table `accountoffsets`
48 DROP TABLE IF EXISTS `accountoffsets`;
49 CREATE TABLE `accountoffsets` (
50 `borrowernumber` int(11) NOT NULL default 0,
51 `accountno` smallint(6) NOT NULL default 0,
52 `offsetaccount` smallint(6) NOT NULL default 0,
53 `offsetamount` decimal(28,6) default NULL,
54 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
55 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
56 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
59 -- Table structure for table `action_logs`
62 DROP TABLE IF EXISTS `action_logs`;
63 CREATE TABLE `action_logs` (
64 `action_id` int(11) NOT NULL auto_increment,
65 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
66 `user` int(11) NOT NULL default 0,
69 `object` int(11) default NULL,
71 PRIMARY KEY (`action_id`),
72 KEY (`timestamp`,`user`)
73 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
76 -- Table structure for table `alert`
79 DROP TABLE IF EXISTS `alert`;
80 CREATE TABLE `alert` (
81 `alertid` int(11) NOT NULL auto_increment,
82 `borrowernumber` int(11) NOT NULL default 0,
83 `type` varchar(10) NOT NULL default '',
84 `externalid` varchar(20) NOT NULL default '',
85 PRIMARY KEY (`alertid`),
86 KEY `borrowernumber` (`borrowernumber`),
87 KEY `type` (`type`,`externalid`)
88 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
91 -- Table structure for table `aqbasket`
94 DROP TABLE IF EXISTS `aqbasket`;
95 CREATE TABLE `aqbasket` (
96 `basketno` int(11) NOT NULL auto_increment,
97 `creationdate` date default NULL,
98 `closedate` date default NULL,
99 `booksellerid` int(11) NOT NULL default 1,
100 `authorisedby` varchar(10) default NULL,
101 `booksellerinvoicenumber` mediumtext,
102 PRIMARY KEY (`basketno`),
103 KEY `booksellerid` (`booksellerid`),
104 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
105 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
108 -- Table structure for table `aqbookfund`
111 DROP TABLE IF EXISTS `aqbookfund`;
112 CREATE TABLE `aqbookfund` (
113 `bookfundid` varchar(10) NOT NULL default '',
114 `bookfundname` mediumtext,
115 `bookfundgroup` varchar(5) default NULL,
116 `branchcode` varchar(10) NOT NULL default '',
117 PRIMARY KEY (`bookfundid`,`branchcode`)
118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
121 -- Table structure for table `aqbooksellers`
124 DROP TABLE IF EXISTS `aqbooksellers`;
125 CREATE TABLE `aqbooksellers` (
126 `id` int(11) NOT NULL auto_increment,
127 `name` mediumtext NOT NULL,
128 `address1` mediumtext,
129 `address2` mediumtext,
130 `address3` mediumtext,
131 `address4` mediumtext,
132 `phone` varchar(30) default NULL,
133 `accountnumber` mediumtext,
134 `othersupplier` mediumtext,
135 `currency` varchar(3) NOT NULL default '',
136 `deliverydays` smallint(6) default NULL,
137 `followupdays` smallint(6) default NULL,
138 `followupscancel` smallint(6) default NULL,
139 `specialty` mediumtext,
140 `booksellerfax` mediumtext,
142 `bookselleremail` mediumtext,
143 `booksellerurl` mediumtext,
144 `contact` varchar(100) default NULL,
146 `url` varchar(255) default NULL,
147 `contpos` varchar(100) default NULL,
148 `contphone` varchar(100) default NULL,
149 `contfax` varchar(100) default NULL,
150 `contaltphone` varchar(100) default NULL,
151 `contemail` varchar(100) default NULL,
152 `contnotes` mediumtext,
153 `active` tinyint(4) default NULL,
154 `listprice` varchar(10) default NULL,
155 `invoiceprice` varchar(10) default NULL,
156 `gstreg` tinyint(4) default NULL,
157 `listincgst` tinyint(4) default NULL,
158 `invoiceincgst` tinyint(4) default NULL,
159 `discount` float(6,4) default NULL,
160 `fax` varchar(50) default NULL,
161 `nocalc` int(11) default NULL,
162 `invoicedisc` float(6,4) default NULL,
164 KEY `listprice` (`listprice`),
165 KEY `invoiceprice` (`invoiceprice`),
166 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
167 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
171 -- Table structure for table `aqbudget`
174 DROP TABLE IF EXISTS `aqbudget`;
175 CREATE TABLE `aqbudget` (
176 `bookfundid` varchar(10) NOT NULL default '',
177 `startdate` date NOT NULL default 0,
178 `enddate` date default NULL,
179 `budgetamount` decimal(13,2) default NULL,
180 `aqbudgetid` int(11) NOT NULL auto_increment,
181 `branchcode` varchar(10) default NULL,
182 PRIMARY KEY (`aqbudgetid`)
183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
186 -- Table structure for table `aqorderbreakdown`
189 DROP TABLE IF EXISTS `aqorderbreakdown`;
190 CREATE TABLE `aqorderbreakdown` (
191 `ordernumber` int(11) default NULL,
192 `linenumber` int(11) default NULL,
193 `branchcode` varchar(10) default NULL,
194 `bookfundid` varchar(10) NOT NULL default '',
195 `allocation` smallint(6) default NULL,
196 KEY `ordernumber` (`ordernumber`),
197 KEY `bookfundid` (`bookfundid`),
198 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
199 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
203 -- Table structure for table `aqorderdelivery`
206 DROP TABLE IF EXISTS `aqorderdelivery`;
207 CREATE TABLE `aqorderdelivery` (
208 `ordernumber` date default NULL,
209 `deliverynumber` smallint(6) NOT NULL default 0,
210 `deliverydate` varchar(18) default NULL,
211 `qtydelivered` smallint(6) default NULL,
212 `deliverycomments` mediumtext
213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
216 -- Table structure for table `aqorders`
219 DROP TABLE IF EXISTS `aqorders`;
220 CREATE TABLE `aqorders` (
221 `ordernumber` int(11) NOT NULL auto_increment,
222 `biblionumber` int(11) default NULL,
224 `entrydate` date default NULL,
225 `quantity` smallint(6) default NULL,
226 `currency` varchar(3) default NULL,
227 `listprice` decimal(28,6) default NULL,
228 `totalamount` decimal(28,6) default NULL,
229 `datereceived` date default NULL,
230 `booksellerinvoicenumber` mediumtext,
231 `freight` decimal(28,6) default NULL,
232 `unitprice` decimal(28,6) default NULL,
233 `quantityreceived` smallint(6) default NULL,
234 `cancelledby` varchar(10) default NULL,
235 `datecancellationprinted` date default NULL,
237 `supplierreference` mediumtext,
238 `purchaseordernumber` mediumtext,
239 `subscription` tinyint(1) default NULL,
240 `serialid` varchar(30) default NULL,
241 `basketno` int(11) default NULL,
242 `biblioitemnumber` int(11) default NULL,
243 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
244 `rrp` decimal(13,2) default NULL,
245 `ecost` decimal(13,2) default NULL,
246 `gst` decimal(13,2) default NULL,
247 `budgetdate` date default NULL,
248 `sort1` varchar(80) default NULL,
249 `sort2` varchar(80) default NULL,
250 PRIMARY KEY (`ordernumber`),
251 KEY `basketno` (`basketno`),
252 KEY `biblionumber` (`biblionumber`),
253 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
254 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
255 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
258 -- Table structure for table `auth_header`
261 DROP TABLE IF EXISTS `auth_header`;
262 CREATE TABLE `auth_header` (
263 `authid` bigint(20) unsigned NOT NULL auto_increment,
264 `authtypecode` varchar(10) NOT NULL default '',
265 `datecreated` date default NULL,
266 `datemodified` date default NULL,
267 `origincode` varchar(20) default NULL,
268 `authtrees` mediumtext,
270 `linkid` bigint(20) default NULL,
271 `marcxml` longtext NOT NULL,
272 PRIMARY KEY (`authid`),
273 KEY `origincode` (`origincode`)
274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
277 -- Table structure for table `auth_subfield_structure`
280 DROP TABLE IF EXISTS `auth_subfield_structure`;
281 CREATE TABLE `auth_subfield_structure` (
282 `authtypecode` varchar(10) NOT NULL default '',
283 `tagfield` varchar(3) NOT NULL default '',
284 `tagsubfield` varchar(1) NOT NULL default '',
285 `liblibrarian` varchar(255) NOT NULL default '',
286 `libopac` varchar(255) NOT NULL default '',
287 `repeatable` tinyint(4) NOT NULL default 0,
288 `mandatory` tinyint(4) NOT NULL default 0,
289 `tab` tinyint(1) default NULL,
290 `authorised_value` varchar(10) default NULL,
291 `value_builder` varchar(80) default NULL,
292 `seealso` varchar(255) default NULL,
293 `isurl` tinyint(1) default NULL,
294 `hidden` tinyint(3) NOT NULL default 0,
295 `linkid` tinyint(1) NOT NULL default 0,
296 `kohafield` varchar(45) NULL default '',
297 `frameworkcode` varchar(8) NOT NULL default '',
298 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
299 KEY `tab` (`authtypecode`,`tab`)
300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
303 -- Table structure for table `auth_tag_structure`
306 DROP TABLE IF EXISTS `auth_tag_structure`;
307 CREATE TABLE `auth_tag_structure` (
308 `authtypecode` varchar(10) NOT NULL default '',
309 `tagfield` varchar(3) NOT NULL default '',
310 `liblibrarian` varchar(255) NOT NULL default '',
311 `libopac` varchar(255) NOT NULL default '',
312 `repeatable` tinyint(4) NOT NULL default 0,
313 `mandatory` tinyint(4) NOT NULL default 0,
314 `authorised_value` varchar(10) default NULL,
315 PRIMARY KEY (`authtypecode`,`tagfield`),
316 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
320 -- Table structure for table `auth_types`
323 DROP TABLE IF EXISTS `auth_types`;
324 CREATE TABLE `auth_types` (
325 `authtypecode` varchar(10) NOT NULL default '',
326 `authtypetext` varchar(255) NOT NULL default '',
327 `auth_tag_to_report` varchar(3) NOT NULL default '',
328 `summary` mediumtext NOT NULL,
329 PRIMARY KEY (`authtypecode`)
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
333 -- Table structure for table `authorised_values`
336 DROP TABLE IF EXISTS `authorised_values`;
337 CREATE TABLE `authorised_values` (
338 `id` int(11) NOT NULL auto_increment,
339 `category` varchar(10) NOT NULL default '',
340 `authorised_value` varchar(80) NOT NULL default '',
341 `lib` varchar(80) default NULL,
342 `imageurl` varchar(200) default NULL,
344 KEY `name` (`category`)
345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
348 -- Table structure for table `biblio`
351 DROP TABLE IF EXISTS `biblio`;
352 CREATE TABLE `biblio` (
353 `biblionumber` int(11) NOT NULL auto_increment,
354 `frameworkcode` varchar(4) NOT NULL default '',
357 `unititle` mediumtext,
359 `serial` tinyint(1) default NULL,
360 `seriestitle` mediumtext,
361 `copyrightdate` smallint(6) default NULL,
362 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
363 `datecreated` DATE NOT NULL,
364 `abstract` mediumtext,
365 PRIMARY KEY (`biblionumber`),
366 KEY `blbnoidx` (`biblionumber`)
367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
370 -- Table structure for table `biblio_framework`
373 DROP TABLE IF EXISTS `biblio_framework`;
374 CREATE TABLE `biblio_framework` (
375 `frameworkcode` varchar(4) NOT NULL default '',
376 `frameworktext` varchar(255) NOT NULL default '',
377 PRIMARY KEY (`frameworkcode`)
378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
381 -- Table structure for table `biblioitems`
384 DROP TABLE IF EXISTS `biblioitems`;
385 CREATE TABLE `biblioitems` (
386 `biblioitemnumber` int(11) NOT NULL auto_increment,
387 `biblionumber` int(11) NOT NULL default 0,
390 `itemtype` varchar(10) default NULL,
391 `isbn` varchar(30) default NULL,
392 `issn` varchar(9) default NULL,
393 `publicationyear` text,
394 `publishercode` varchar(255) default NULL,
395 `volumedate` date default NULL,
397 `collectiontitle` mediumtext default NULL,
398 `collectionissn` text default NULL,
399 `collectionvolume` mediumtext default NULL,
400 `editionstatement` text default NULL,
401 `editionresponsibility` text default NULL,
402 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
403 `illus` varchar(255) default NULL,
404 `pages` varchar(255) default NULL,
406 `size` varchar(255) default NULL,
407 `place` varchar(255) default NULL,
408 `lccn` varchar(25) default NULL,
410 `url` varchar(255) default NULL,
411 `cn_source` varchar(10) default NULL,
412 `cn_class` varchar(30) default NULL,
413 `cn_item` varchar(10) default NULL,
414 `cn_suffix` varchar(10) default NULL,
415 `cn_sort` varchar(30) default NULL,
416 `totalissues` int(10),
417 `marcxml` longtext NOT NULL,
418 PRIMARY KEY (`biblioitemnumber`),
419 KEY `bibinoidx` (`biblioitemnumber`),
420 KEY `bibnoidx` (`biblionumber`),
423 KEY `publishercode` (`publishercode`),
425 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
429 -- Table structure for table `borrowers`
432 DROP TABLE IF EXISTS `borrowers`;
433 CREATE TABLE `borrowers` (
434 `borrowernumber` int(11) NOT NULL auto_increment,
435 `cardnumber` varchar(16) default NULL,
436 `surname` mediumtext NOT NULL,
439 `othernames` mediumtext,
441 `streetnumber` varchar(10) default NULL,
442 `streettype` varchar(50) default NULL,
443 `address` mediumtext NOT NULL,
445 `city` mediumtext NOT NULL,
446 `zipcode` varchar(25) default NULL,
449 `mobile` varchar(50) default NULL,
453 `B_streetnumber` varchar(10) default NULL,
454 `B_streettype` varchar(50) default NULL,
455 `B_address` varchar(100) default NULL,
457 `B_zipcode` varchar(25) default NULL,
459 `B_phone` mediumtext,
460 `dateofbirth` date default NULL,
461 `branchcode` varchar(10) NOT NULL default '',
462 `categorycode` varchar(10) NOT NULL default '',
463 `dateenrolled` date default NULL,
464 `dateexpiry` date default NULL,
465 `gonenoaddress` tinyint(1) default NULL,
466 `lost` tinyint(1) default NULL,
467 `debarred` tinyint(1) default NULL,
468 `contactname` mediumtext,
469 `contactfirstname` text,
471 `guarantorid` int(11) default NULL,
472 `borrowernotes` mediumtext,
473 `relationship` varchar(100) default NULL,
474 `ethnicity` varchar(50) default NULL,
475 `ethnotes` varchar(255) default NULL,
476 `sex` varchar(1) default NULL,
477 `password` varchar(30) default NULL,
478 `flags` int(11) default NULL,
479 `userid` varchar(30) default NULL,
480 `opacnote` mediumtext,
481 `contactnote` varchar(255) default NULL,
482 `sort1` varchar(80) default NULL,
483 `sort2` varchar(80) default NULL,
484 `altcontactfirstname` varchar(255) default NULL,
485 `altcontactsurname` varchar(255) default NULL,
486 `altcontactaddress1` varchar(255) default NULL,
487 `altcontactaddress2` varchar(255) default NULL,
488 `altcontactaddress3` varchar(255) default NULL,
489 `altcontactzipcode` varchar(50) default NULL,
490 `altcontactphone` varchar(50) default NULL,
491 `smsalertnumber` varchar(50) default NULL,
492 UNIQUE KEY `cardnumber` (`cardnumber`),
493 PRIMARY KEY `borrowernumber` (`borrowernumber`),
494 KEY `categorycode` (`categorycode`),
495 KEY `branchcode` (`branchcode`),
496 KEY `userid` (`userid`),
497 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
498 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
499 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
502 -- Table structure for table `borrower_attribute_types`
505 DROP TABLE IF EXISTS `borrower_attribute_types`;
506 CREATE TABLE `borrower_attribute_types` (
507 `code` varchar(10) NOT NULL,
508 `description` varchar(255) NOT NULL,
509 `repeatable` tinyint(1) NOT NULL default 0,
510 `unique_id` tinyint(1) NOT NULL default 0,
511 `opac_display` tinyint(1) NOT NULL default 0,
512 `password_allowed` tinyint(1) NOT NULL default 0,
513 `staff_searchable` tinyint(1) NOT NULL default 0,
514 `authorised_value_category` varchar(10) default NULL,
516 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
519 -- Table structure for table `borrower_attributes`
522 DROP TABLE IF EXISTS `borrower_attributes`;
523 CREATE TABLE `borrower_attributes` (
524 `borrowernumber` int(11) NOT NULL,
525 `code` varchar(10) NOT NULL,
526 `attribute` varchar(64) default NULL,
527 `password` varchar(64) default NULL,
528 KEY `borrowernumber` (`borrowernumber`),
529 KEY `code_attribute` (`code`, `attribute`),
530 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
531 ON DELETE CASCADE ON UPDATE CASCADE,
532 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
533 ON DELETE CASCADE ON UPDATE CASCADE
534 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
537 -- Table structure for table `branchcategories`
540 DROP TABLE IF EXISTS `branchcategories`;
541 CREATE TABLE `branchcategories` (
542 `categorycode` varchar(10) NOT NULL default '',
543 `categoryname` varchar(32),
544 `codedescription` mediumtext,
545 `categorytype` varchar(16),
546 PRIMARY KEY (`categorycode`)
547 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
550 -- Table structure for table `branches`
553 DROP TABLE IF EXISTS `branches`;
554 CREATE TABLE `branches` (
555 `branchcode` varchar(10) NOT NULL default '',
556 `branchname` mediumtext NOT NULL,
557 `branchaddress1` mediumtext,
558 `branchaddress2` mediumtext,
559 `branchaddress3` mediumtext,
560 `branchphone` mediumtext,
561 `branchfax` mediumtext,
562 `branchemail` mediumtext,
563 `issuing` tinyint(4) default NULL,
564 `branchip` varchar(15) default NULL,
565 `branchprinter` varchar(100) default NULL,
566 UNIQUE KEY `branchcode` (`branchcode`)
567 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
570 -- Table structure for table `branchrelations`
573 DROP TABLE IF EXISTS `branchrelations`;
574 CREATE TABLE `branchrelations` (
575 `branchcode` varchar(10) NOT NULL default '',
576 `categorycode` varchar(10) NOT NULL default '',
577 PRIMARY KEY (`branchcode`,`categorycode`),
578 KEY `branchcode` (`branchcode`),
579 KEY `categorycode` (`categorycode`),
580 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
581 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
582 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
585 -- Table structure for table `branchtransfers`
588 DROP TABLE IF EXISTS `branchtransfers`;
589 CREATE TABLE `branchtransfers` (
590 `itemnumber` int(11) NOT NULL default 0,
591 `datesent` datetime default NULL,
592 `frombranch` varchar(10) NOT NULL default '',
593 `datearrived` datetime default NULL,
594 `tobranch` varchar(10) NOT NULL default '',
595 `comments` mediumtext,
596 KEY `frombranch` (`frombranch`),
597 KEY `tobranch` (`tobranch`),
598 KEY `itemnumber` (`itemnumber`),
599 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
600 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
601 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
606 -- Table structure for table `browser`
608 DROP TABLE IF EXISTS `browser`;
609 CREATE TABLE `browser` (
610 `level` int(11) NOT NULL,
611 `classification` varchar(20) NOT NULL,
612 `description` varchar(255) NOT NULL,
613 `number` bigint(20) NOT NULL,
614 `endnode` tinyint(4) NOT NULL
615 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
618 -- Table structure for table `categories`
621 DROP TABLE IF EXISTS `categories`;
622 CREATE TABLE `categories` (
623 `categorycode` varchar(10) NOT NULL default '',
624 `description` mediumtext,
625 `enrolmentperiod` smallint(6) default NULL,
626 `upperagelimit` smallint(6) default NULL,
627 `dateofbirthrequired` tinyint(1) default NULL,
628 `finetype` varchar(30) default NULL,
629 `bulk` tinyint(1) default NULL,
630 `enrolmentfee` decimal(28,6) default NULL,
631 `overduenoticerequired` tinyint(1) default NULL,
632 `issuelimit` smallint(6) default NULL,
633 `reservefee` decimal(28,6) default NULL,
634 `category_type` varchar(1) NOT NULL default 'A',
635 PRIMARY KEY (`categorycode`),
636 UNIQUE KEY `categorycode` (`categorycode`)
637 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
640 -- Table structure for table `borrower_branch_circ_rules`
643 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
644 CREATE TABLE `branch_borrower_circ_rules` (
645 `branchcode` VARCHAR(10) NOT NULL,
646 `categorycode` VARCHAR(10) NOT NULL,
647 `maxissueqty` int(4) default NULL,
648 PRIMARY KEY (`categorycode`, `branchcode`),
649 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
650 ON DELETE CASCADE ON UPDATE CASCADE,
651 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
652 ON DELETE CASCADE ON UPDATE CASCADE
653 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
656 -- Table structure for table `default_borrower_circ_rules`
659 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
660 CREATE TABLE `default_borrower_circ_rules` (
661 `categorycode` VARCHAR(10) NOT NULL,
662 `maxissueqty` int(4) default NULL,
663 PRIMARY KEY (`categorycode`),
664 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
665 ON DELETE CASCADE ON UPDATE CASCADE
666 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
669 -- Table structure for table `default_branch_circ_rules`
672 DROP TABLE IF EXISTS `default_branch_circ_rules`;
673 CREATE TABLE `default_branch_circ_rules` (
674 `branchcode` VARCHAR(10) NOT NULL,
675 `maxissueqty` int(4) default NULL,
676 PRIMARY KEY (`branchcode`),
677 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
678 ON DELETE CASCADE ON UPDATE CASCADE
679 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
682 -- Table structure for table `default_circ_rules`
685 DROP TABLE IF EXISTS `default_circ_rules`;
686 CREATE TABLE `default_circ_rules` (
687 `singleton` enum('singleton') NOT NULL default 'singleton',
688 `maxissueqty` int(4) default NULL,
689 PRIMARY KEY (`singleton`)
690 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
693 -- Table structure for table `cities`
696 DROP TABLE IF EXISTS `cities`;
697 CREATE TABLE `cities` (
698 `cityid` int(11) NOT NULL auto_increment,
699 `city_name` varchar(100) NOT NULL default '',
700 `city_zipcode` varchar(20) default NULL,
701 PRIMARY KEY (`cityid`)
702 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
705 -- Table structure for table `class_sort_rules`
708 DROP TABLE IF EXISTS `class_sort_rules`;
709 CREATE TABLE `class_sort_rules` (
710 `class_sort_rule` varchar(10) NOT NULL default '',
711 `description` mediumtext,
712 `sort_routine` varchar(30) NOT NULL default '',
713 PRIMARY KEY (`class_sort_rule`),
714 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
715 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
718 -- Table structure for table `class_sources`
721 DROP TABLE IF EXISTS `class_sources`;
722 CREATE TABLE `class_sources` (
723 `cn_source` varchar(10) NOT NULL default '',
724 `description` mediumtext,
725 `used` tinyint(4) NOT NULL default 0,
726 `class_sort_rule` varchar(10) NOT NULL default '',
727 PRIMARY KEY (`cn_source`),
728 UNIQUE KEY `cn_source_idx` (`cn_source`),
729 KEY `used_idx` (`used`),
730 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
731 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
734 -- Table structure for table `currency`
737 DROP TABLE IF EXISTS `currency`;
738 CREATE TABLE `currency` (
739 `currency` varchar(10) NOT NULL default '',
740 `symbol` varchar(5) default NULL,
741 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
742 `rate` float(7,5) default NULL,
743 PRIMARY KEY (`currency`)
744 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
747 -- Table structure for table `deletedbiblio`
750 DROP TABLE IF EXISTS `deletedbiblio`;
751 CREATE TABLE `deletedbiblio` (
752 `biblionumber` int(11) NOT NULL default 0,
753 `frameworkcode` varchar(4) NOT NULL default '',
756 `unititle` mediumtext,
758 `serial` tinyint(1) default NULL,
759 `seriestitle` mediumtext,
760 `copyrightdate` smallint(6) default NULL,
761 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
762 `datecreated` DATE NOT NULL,
763 `abstract` mediumtext,
764 PRIMARY KEY (`biblionumber`),
765 KEY `blbnoidx` (`biblionumber`)
766 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
769 -- Table structure for table `deletedbiblioitems`
772 DROP TABLE IF EXISTS `deletedbiblioitems`;
773 CREATE TABLE `deletedbiblioitems` (
774 `biblioitemnumber` int(11) NOT NULL default 0,
775 `biblionumber` int(11) NOT NULL default 0,
778 `itemtype` varchar(10) default NULL,
779 `isbn` varchar(30) default NULL,
780 `issn` varchar(9) default NULL,
781 `publicationyear` text,
782 `publishercode` varchar(255) default NULL,
783 `volumedate` date default NULL,
785 `collectiontitle` mediumtext default NULL,
786 `collectionissn` text default NULL,
787 `collectionvolume` mediumtext default NULL,
788 `editionstatement` text default NULL,
789 `editionresponsibility` text default NULL,
790 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
791 `illus` varchar(255) default NULL,
792 `pages` varchar(255) default NULL,
794 `size` varchar(255) default NULL,
795 `place` varchar(255) default NULL,
796 `lccn` varchar(25) default NULL,
798 `url` varchar(255) default NULL,
799 `cn_source` varchar(10) default NULL,
800 `cn_class` varchar(30) default NULL,
801 `cn_item` varchar(10) default NULL,
802 `cn_suffix` varchar(10) default NULL,
803 `cn_sort` varchar(30) default NULL,
804 `totalissues` int(10),
805 `marcxml` longtext NOT NULL,
806 PRIMARY KEY (`biblioitemnumber`),
807 KEY `bibinoidx` (`biblioitemnumber`),
808 KEY `bibnoidx` (`biblionumber`),
810 KEY `publishercode` (`publishercode`)
811 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
814 -- Table structure for table `deletedborrowers`
817 DROP TABLE IF EXISTS `deletedborrowers`;
818 CREATE TABLE `deletedborrowers` (
819 `borrowernumber` int(11) NOT NULL default 0,
820 `cardnumber` varchar(9) NOT NULL default '',
821 `surname` mediumtext NOT NULL,
824 `othernames` mediumtext,
826 `streetnumber` varchar(10) default NULL,
827 `streettype` varchar(50) default NULL,
828 `address` mediumtext NOT NULL,
830 `city` mediumtext NOT NULL,
831 `zipcode` varchar(25) default NULL,
834 `mobile` varchar(50) default NULL,
838 `B_streetnumber` varchar(10) default NULL,
839 `B_streettype` varchar(50) default NULL,
840 `smsalertnumber` varchar(50) default NULL,
841 `B_address` varchar(100) default NULL,
843 `B_zipcode` varchar(25) default NULL,
845 `B_phone` mediumtext,
846 `dateofbirth` date default NULL,
847 `branchcode` varchar(10) NOT NULL default '',
848 `categorycode` varchar(10) default NULL,
849 `dateenrolled` date default NULL,
850 `dateexpiry` date default NULL,
851 `gonenoaddress` tinyint(1) default NULL,
852 `lost` tinyint(1) default NULL,
853 `debarred` tinyint(1) default NULL,
854 `contactname` mediumtext,
855 `contactfirstname` text,
857 `guarantorid` int(11) default NULL,
858 `borrowernotes` mediumtext,
859 `relationship` varchar(100) default NULL,
860 `ethnicity` varchar(50) default NULL,
861 `ethnotes` varchar(255) default NULL,
862 `sex` varchar(1) default NULL,
863 `password` varchar(30) default NULL,
864 `flags` int(11) default NULL,
865 `userid` varchar(30) default NULL,
866 `opacnote` mediumtext,
867 `contactnote` varchar(255) default NULL,
868 `sort1` varchar(80) default NULL,
869 `sort2` varchar(80) default NULL,
870 `altcontactfirstname` varchar(255) default NULL,
871 `altcontactsurname` varchar(255) default NULL,
872 `altcontactaddress1` varchar(255) default NULL,
873 `altcontactaddress2` varchar(255) default NULL,
874 `altcontactaddress3` varchar(255) default NULL,
875 `altcontactzipcode` varchar(50) default NULL,
876 `altcontactphone` varchar(50) default NULL,
877 KEY `borrowernumber` (`borrowernumber`),
878 KEY `cardnumber` (`cardnumber`)
879 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
882 -- Table structure for table `deleteditems`
885 DROP TABLE IF EXISTS `deleteditems`;
886 CREATE TABLE `deleteditems` (
887 `itemnumber` int(11) NOT NULL default 0,
888 `biblionumber` int(11) NOT NULL default 0,
889 `biblioitemnumber` int(11) NOT NULL default 0,
890 `barcode` varchar(20) default NULL,
891 `dateaccessioned` date default NULL,
892 `booksellerid` mediumtext default NULL,
893 `homebranch` varchar(10) default NULL,
894 `price` decimal(8,2) default NULL,
895 `replacementprice` decimal(8,2) default NULL,
896 `replacementpricedate` date default NULL,
897 `datelastborrowed` date default NULL,
898 `datelastseen` date default NULL,
899 `stack` tinyint(1) default NULL,
900 `notforloan` tinyint(1) NOT NULL default 0,
901 `damaged` tinyint(1) NOT NULL default 0,
902 `itemlost` tinyint(1) NOT NULL default 0,
903 `wthdrawn` tinyint(1) NOT NULL default 0,
904 `itemcallnumber` varchar(30) default NULL,
905 `issues` smallint(6) default NULL,
906 `renewals` smallint(6) default NULL,
907 `reserves` smallint(6) default NULL,
908 `restricted` tinyint(1) default NULL,
909 `itemnotes` mediumtext,
910 `holdingbranch` varchar(10) default NULL,
911 `paidfor` mediumtext,
912 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
913 `location` varchar(80) default NULL,
914 `onloan` date default NULL,
915 `cn_source` varchar(10) default NULL,
916 `cn_sort` varchar(30) default NULL,
917 `ccode` varchar(10) default NULL,
918 `materials` varchar(10) default NULL,
919 `uri` varchar(255) default NULL,
920 `itype` varchar(10) default NULL,
921 `more_subfields_xml` longtext default NULL,
922 `enumchron` varchar(80) default NULL,
923 `copynumber` varchar(32) default NULL,
925 PRIMARY KEY (`itemnumber`),
926 KEY `delitembarcodeidx` (`barcode`),
927 KEY `delitembinoidx` (`biblioitemnumber`),
928 KEY `delitembibnoidx` (`biblionumber`),
929 KEY `delhomebranch` (`homebranch`),
930 KEY `delholdingbranch` (`holdingbranch`)
931 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
934 -- Table structure for table `ethnicity`
937 DROP TABLE IF EXISTS `ethnicity`;
938 CREATE TABLE `ethnicity` (
939 `code` varchar(10) NOT NULL default '',
940 `name` varchar(255) default NULL,
942 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
945 -- Table structure for table `hold_fill_targets`
948 DROP TABLE IF EXISTS `hold_fill_targets`;
949 CREATE TABLE hold_fill_targets (
950 `borrowernumber` int(11) NOT NULL,
951 `biblionumber` int(11) NOT NULL,
952 `itemnumber` int(11) NOT NULL,
953 `source_branchcode` varchar(10) default NULL,
954 `item_level_request` tinyint(4) NOT NULL default 0,
955 PRIMARY KEY `itemnumber` (`itemnumber`),
956 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
957 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
958 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
959 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
960 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
961 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
962 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
963 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
964 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
965 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
968 -- Table structure for table `import_batches`
971 DROP TABLE IF EXISTS `import_batches`;
972 CREATE TABLE `import_batches` (
973 `import_batch_id` int(11) NOT NULL auto_increment,
974 `matcher_id` int(11) default NULL,
975 `template_id` int(11) default NULL,
976 `branchcode` varchar(10) default NULL,
977 `num_biblios` int(11) NOT NULL default 0,
978 `num_items` int(11) NOT NULL default 0,
979 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
980 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
981 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
982 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
983 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
984 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
985 `file_name` varchar(100),
986 `comments` mediumtext,
987 PRIMARY KEY (`import_batch_id`),
988 KEY `branchcode` (`branchcode`)
989 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
992 -- Table structure for table `import_records`
995 DROP TABLE IF EXISTS `import_records`;
996 CREATE TABLE `import_records` (
997 `import_record_id` int(11) NOT NULL auto_increment,
998 `import_batch_id` int(11) NOT NULL,
999 `branchcode` varchar(10) default NULL,
1000 `record_sequence` int(11) NOT NULL default 0,
1001 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1002 `import_date` DATE default NULL,
1003 `marc` longblob NOT NULL,
1004 `marcxml` longtext NOT NULL,
1005 `marcxml_old` longtext NOT NULL,
1006 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1007 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1008 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1009 `import_error` mediumtext,
1010 `encoding` varchar(40) NOT NULL default '',
1011 `z3950random` varchar(40) default NULL,
1012 PRIMARY KEY (`import_record_id`),
1013 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1014 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1015 KEY `branchcode` (`branchcode`),
1016 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1017 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1020 -- Table structure for `import_record_matches`
1022 DROP TABLE IF EXISTS `import_record_matches`;
1023 CREATE TABLE `import_record_matches` (
1024 `import_record_id` int(11) NOT NULL,
1025 `candidate_match_id` int(11) NOT NULL,
1026 `score` int(11) NOT NULL default 0,
1027 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1028 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1029 KEY `record_score` (`import_record_id`, `score`)
1030 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1033 -- Table structure for table `import_biblios`
1036 DROP TABLE IF EXISTS `import_biblios`;
1037 CREATE TABLE `import_biblios` (
1038 `import_record_id` int(11) NOT NULL,
1039 `matched_biblionumber` int(11) default NULL,
1040 `control_number` varchar(25) default NULL,
1041 `original_source` varchar(25) default NULL,
1042 `title` varchar(128) default NULL,
1043 `author` varchar(80) default NULL,
1044 `isbn` varchar(30) default NULL,
1045 `issn` varchar(9) default NULL,
1046 `has_items` tinyint(1) NOT NULL default 0,
1047 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1048 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1049 KEY `matched_biblionumber` (`matched_biblionumber`),
1050 KEY `title` (`title`),
1052 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1055 -- Table structure for table `import_items`
1058 DROP TABLE IF EXISTS `import_items`;
1059 CREATE TABLE `import_items` (
1060 `import_items_id` int(11) NOT NULL auto_increment,
1061 `import_record_id` int(11) NOT NULL,
1062 `itemnumber` int(11) default NULL,
1063 `branchcode` varchar(10) default NULL,
1064 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1065 `marcxml` longtext NOT NULL,
1066 `import_error` mediumtext,
1067 PRIMARY KEY (`import_items_id`),
1068 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1069 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1070 KEY `itemnumber` (`itemnumber`),
1071 KEY `branchcode` (`branchcode`)
1072 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1075 -- Table structure for table `issues`
1078 DROP TABLE IF EXISTS `issues`;
1079 CREATE TABLE `issues` (
1080 `borrowernumber` int(11) default NULL,
1081 `itemnumber` int(11) default NULL,
1082 `date_due` date default NULL,
1083 `branchcode` varchar(10) default NULL,
1084 `issuingbranch` varchar(18) default NULL,
1085 `returndate` date default NULL,
1086 `lastreneweddate` date default NULL,
1087 `return` varchar(4) default NULL,
1088 `renewals` tinyint(4) default NULL,
1089 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1090 `issuedate` date default NULL,
1091 KEY `issuesborridx` (`borrowernumber`),
1092 KEY `issuesitemidx` (`itemnumber`),
1093 KEY `bordate` (`borrowernumber`,`timestamp`),
1094 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1095 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1096 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1099 -- Table structure for table `issuingrules`
1102 DROP TABLE IF EXISTS `issuingrules`;
1103 CREATE TABLE `issuingrules` (
1104 `categorycode` varchar(10) NOT NULL default '',
1105 `itemtype` varchar(10) NOT NULL default '',
1106 `restrictedtype` tinyint(1) default NULL,
1107 `rentaldiscount` decimal(28,6) default NULL,
1108 `reservecharge` decimal(28,6) default NULL,
1109 `fine` decimal(28,6) default NULL,
1110 `finedays` int(11) default NULL,
1111 `firstremind` int(11) default NULL,
1112 `chargeperiod` int(11) default NULL,
1113 `accountsent` int(11) default NULL,
1114 `chargename` varchar(100) default NULL,
1115 `maxissueqty` int(4) default NULL,
1116 `issuelength` int(4) default NULL,
1117 `branchcode` varchar(10) NOT NULL default '',
1118 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1119 KEY `categorycode` (`categorycode`),
1120 KEY `itemtype` (`itemtype`)
1121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1124 -- Table structure for table `items`
1127 DROP TABLE IF EXISTS `items`;
1128 CREATE TABLE `items` (
1129 `itemnumber` int(11) NOT NULL auto_increment,
1130 `biblionumber` int(11) NOT NULL default 0,
1131 `biblioitemnumber` int(11) NOT NULL default 0,
1132 `barcode` varchar(20) default NULL,
1133 `dateaccessioned` date default NULL,
1134 `booksellerid` mediumtext default NULL,
1135 `homebranch` varchar(10) default NULL,
1136 `price` decimal(8,2) default NULL,
1137 `replacementprice` decimal(8,2) default NULL,
1138 `replacementpricedate` date default NULL,
1139 `datelastborrowed` date default NULL,
1140 `datelastseen` date default NULL,
1141 `stack` tinyint(1) default NULL,
1142 `notforloan` tinyint(1) NOT NULL default 0,
1143 `damaged` tinyint(1) NOT NULL default 0,
1144 `itemlost` tinyint(1) NOT NULL default 0,
1145 `wthdrawn` tinyint(1) NOT NULL default 0,
1146 `itemcallnumber` varchar(30) default NULL,
1147 `issues` smallint(6) default NULL,
1148 `renewals` smallint(6) default NULL,
1149 `reserves` smallint(6) default NULL,
1150 `restricted` tinyint(1) default NULL,
1151 `itemnotes` mediumtext,
1152 `holdingbranch` varchar(10) default NULL,
1153 `paidfor` mediumtext,
1154 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1155 `location` varchar(80) default NULL,
1156 `onloan` date default NULL,
1157 `cn_source` varchar(10) default NULL,
1158 `cn_sort` varchar(30) default NULL,
1159 `ccode` varchar(10) default NULL,
1160 `materials` varchar(10) default NULL,
1161 `uri` varchar(255) default NULL,
1162 `itype` varchar(10) default NULL,
1163 `more_subfields_xml` longtext default NULL,
1164 `enumchron` varchar(80) default NULL,
1165 `copynumber` varchar(32) default NULL,
1166 PRIMARY KEY (`itemnumber`),
1167 UNIQUE KEY `itembarcodeidx` (`barcode`),
1168 KEY `itembinoidx` (`biblioitemnumber`),
1169 KEY `itembibnoidx` (`biblionumber`),
1170 KEY `homebranch` (`homebranch`),
1171 KEY `holdingbranch` (`holdingbranch`),
1172 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1173 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1174 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1175 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1178 -- Table structure for table `itemtypes`
1181 DROP TABLE IF EXISTS `itemtypes`;
1182 CREATE TABLE `itemtypes` (
1183 `itemtype` varchar(10) NOT NULL default '',
1184 `description` mediumtext,
1185 `renewalsallowed` smallint(6) default NULL,
1186 `rentalcharge` double(16,4) default NULL,
1187 `notforloan` smallint(6) default NULL,
1188 `imageurl` varchar(200) default NULL,
1190 PRIMARY KEY (`itemtype`),
1191 UNIQUE KEY `itemtype` (`itemtype`)
1192 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1195 -- Table structure for table `labels`
1198 DROP TABLE IF EXISTS `labels`;
1199 CREATE TABLE `labels` (
1200 `labelid` int(11) NOT NULL auto_increment,
1201 `batch_id` varchar(10) NOT NULL default 1,
1202 `itemnumber` varchar(100) NOT NULL default '',
1203 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1204 PRIMARY KEY (`labelid`)
1205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1208 -- Table structure for table `labels_conf`
1211 DROP TABLE IF EXISTS `labels_conf`;
1212 CREATE TABLE `labels_conf` (
1213 `id` int(4) NOT NULL auto_increment,
1214 `barcodetype` char(100) default '',
1215 `title` int(1) default '0',
1216 `subtitle` int(1) default '0',
1217 `itemtype` int(1) default '0',
1218 `barcode` int(1) default '0',
1219 `dewey` int(1) default '0',
1220 `classification` int(1) default NULL,
1221 `subclass` int(1) default '0',
1222 `itemcallnumber` int(1) default '0',
1223 `author` int(1) default '0',
1224 `issn` int(1) default '0',
1225 `isbn` int(1) default '0',
1226 `startlabel` int(2) NOT NULL default '1',
1227 `printingtype` char(32) default 'BAR',
1228 `formatstring` varchar(64) default NULL,
1229 `layoutname` char(20) NOT NULL default 'TEST',
1230 `guidebox` int(1) default '0',
1231 `active` tinyint(1) default '1',
1232 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1233 `ccode` char(4) collate utf8_unicode_ci default NULL,
1234 `callnum_split` int(1) default NULL,
1235 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1237 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1240 -- Table structure for table `labels_profile`
1243 DROP TABLE IF EXISTS `labels_profile`;
1244 CREATE TABLE `labels_profile` (
1245 `tmpl_id` int(4) NOT NULL,
1246 `prof_id` int(4) NOT NULL,
1247 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1248 UNIQUE KEY `prof_id` (`prof_id`)
1249 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1252 -- Table structure for table `labels_templates`
1255 DROP TABLE IF EXISTS `labels_templates`;
1256 CREATE TABLE `labels_templates` (
1257 `tmpl_id` int(4) NOT NULL auto_increment,
1258 `tmpl_code` char(100) default '',
1259 `tmpl_desc` char(100) default '',
1260 `page_width` float default '0',
1261 `page_height` float default '0',
1262 `label_width` float default '0',
1263 `label_height` float default '0',
1264 `topmargin` float default '0',
1265 `leftmargin` float default '0',
1266 `cols` int(2) default '0',
1267 `rows` int(2) default '0',
1268 `colgap` float default '0',
1269 `rowgap` float default '0',
1270 `active` int(1) default NULL,
1271 `units` char(20) default 'PX',
1272 `fontsize` int(4) NOT NULL default '3',
1273 `font` char(10) NOT NULL default 'TR',
1274 PRIMARY KEY (`tmpl_id`)
1275 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1278 -- Table structure for table `letter`
1281 DROP TABLE IF EXISTS `letter`;
1282 CREATE TABLE `letter` (
1283 `module` varchar(20) NOT NULL default '',
1284 `code` varchar(20) NOT NULL default '',
1285 `name` varchar(100) NOT NULL default '',
1286 `title` varchar(200) NOT NULL default '',
1288 PRIMARY KEY (`module`,`code`)
1289 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1292 -- Table structure for table `marc_subfield_structure`
1295 DROP TABLE IF EXISTS `marc_subfield_structure`;
1296 CREATE TABLE `marc_subfield_structure` (
1297 `tagfield` varchar(3) NOT NULL default '',
1298 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1299 `liblibrarian` varchar(255) NOT NULL default '',
1300 `libopac` varchar(255) NOT NULL default '',
1301 `repeatable` tinyint(4) NOT NULL default 0,
1302 `mandatory` tinyint(4) NOT NULL default 0,
1303 `kohafield` varchar(40) default NULL,
1304 `tab` tinyint(1) default NULL,
1305 `authorised_value` varchar(20) default NULL,
1306 `authtypecode` varchar(20) default NULL,
1307 `value_builder` varchar(80) default NULL,
1308 `isurl` tinyint(1) default NULL,
1309 `hidden` tinyint(1) default NULL,
1310 `frameworkcode` varchar(4) NOT NULL default '',
1311 `seealso` varchar(1100) default NULL,
1312 `link` varchar(80) default NULL,
1313 `defaultvalue` text default NULL,
1314 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1315 KEY `kohafield_2` (`kohafield`),
1316 KEY `tab` (`frameworkcode`,`tab`),
1317 KEY `kohafield` (`frameworkcode`,`kohafield`)
1318 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1321 -- Table structure for table `marc_tag_structure`
1324 DROP TABLE IF EXISTS `marc_tag_structure`;
1325 CREATE TABLE `marc_tag_structure` (
1326 `tagfield` varchar(3) NOT NULL default '',
1327 `liblibrarian` varchar(255) NOT NULL default '',
1328 `libopac` varchar(255) NOT NULL default '',
1329 `repeatable` tinyint(4) NOT NULL default 0,
1330 `mandatory` tinyint(4) NOT NULL default 0,
1331 `authorised_value` varchar(10) default NULL,
1332 `frameworkcode` varchar(4) NOT NULL default '',
1333 PRIMARY KEY (`frameworkcode`,`tagfield`)
1334 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1337 -- Table structure for table `marc_matchers`
1340 DROP TABLE IF EXISTS `marc_matchers`;
1341 CREATE TABLE `marc_matchers` (
1342 `matcher_id` int(11) NOT NULL auto_increment,
1343 `code` varchar(10) NOT NULL default '',
1344 `description` varchar(255) NOT NULL default '',
1345 `record_type` varchar(10) NOT NULL default 'biblio',
1346 `threshold` int(11) NOT NULL default 0,
1347 PRIMARY KEY (`matcher_id`),
1348 KEY `code` (`code`),
1349 KEY `record_type` (`record_type`)
1350 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1353 -- Table structure for table `matchpoints`
1355 DROP TABLE IF EXISTS `matchpoints`;
1356 CREATE TABLE `matchpoints` (
1357 `matcher_id` int(11) NOT NULL,
1358 `matchpoint_id` int(11) NOT NULL auto_increment,
1359 `search_index` varchar(30) NOT NULL default '',
1360 `score` int(11) NOT NULL default 0,
1361 PRIMARY KEY (`matchpoint_id`),
1362 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1363 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1364 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1368 -- Table structure for table `matchpoint_components`
1370 DROP TABLE IF EXISTS `matchpoint_components`;
1371 CREATE TABLE `matchpoint_components` (
1372 `matchpoint_id` int(11) NOT NULL,
1373 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1374 sequence int(11) NOT NULL default 0,
1375 tag varchar(3) NOT NULL default '',
1376 subfields varchar(40) NOT NULL default '',
1377 offset int(4) NOT NULL default 0,
1378 length int(4) NOT NULL default 0,
1379 PRIMARY KEY (`matchpoint_component_id`),
1380 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1381 CONSTRAINT `matchpoint_components_ifbk_1` 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 `matcher_component_norms`
1388 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1389 CREATE TABLE `matchpoint_component_norms` (
1390 `matchpoint_component_id` int(11) NOT NULL,
1391 `sequence` int(11) NOT NULL default 0,
1392 `norm_routine` varchar(50) NOT NULL default '',
1393 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1394 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1395 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1396 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1399 -- Table structure for table `matcher_matchpoints`
1401 DROP TABLE IF EXISTS `matcher_matchpoints`;
1402 CREATE TABLE `matcher_matchpoints` (
1403 `matcher_id` int(11) NOT NULL,
1404 `matchpoint_id` int(11) NOT NULL,
1405 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1406 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1407 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1408 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1409 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1412 -- Table structure for table `matchchecks`
1414 DROP TABLE IF EXISTS `matchchecks`;
1415 CREATE TABLE `matchchecks` (
1416 `matcher_id` int(11) NOT NULL,
1417 `matchcheck_id` int(11) NOT NULL auto_increment,
1418 `source_matchpoint_id` int(11) NOT NULL,
1419 `target_matchpoint_id` int(11) NOT NULL,
1420 PRIMARY KEY (`matchcheck_id`),
1421 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1422 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1423 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1424 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1425 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1426 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1427 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1430 -- Table structure for table `notifys`
1433 DROP TABLE IF EXISTS `notifys`;
1434 CREATE TABLE `notifys` (
1435 `notify_id` int(11) NOT NULL default 0,
1436 `borrowernumber` int(11) NOT NULL default 0,
1437 `itemnumber` int(11) NOT NULL default 0,
1438 `notify_date` date default NULL,
1439 `notify_send_date` date default NULL,
1440 `notify_level` int(1) NOT NULL default 0,
1441 `method` varchar(20) NOT NULL default ''
1442 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1445 -- Table structure for table `nozebra`
1448 DROP TABLE IF EXISTS `nozebra`;
1449 CREATE TABLE `nozebra` (
1450 `server` varchar(20) NOT NULL,
1451 `indexname` varchar(40) NOT NULL,
1452 `value` varchar(250) NOT NULL,
1453 `biblionumbers` longtext NOT NULL,
1454 KEY `indexname` (`server`,`indexname`),
1455 KEY `value` (`server`,`value`))
1456 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1459 -- Table structure for table `old_issues`
1462 DROP TABLE IF EXISTS `old_issues`;
1463 CREATE TABLE `old_issues` (
1464 `borrowernumber` int(11) default NULL,
1465 `itemnumber` int(11) default NULL,
1466 `date_due` date default NULL,
1467 `branchcode` varchar(10) default NULL,
1468 `issuingbranch` varchar(18) default NULL,
1469 `returndate` date default NULL,
1470 `lastreneweddate` date default NULL,
1471 `return` varchar(4) default NULL,
1472 `renewals` tinyint(4) default NULL,
1473 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1474 `issuedate` date default NULL,
1475 KEY `old_issuesborridx` (`borrowernumber`),
1476 KEY `old_issuesitemidx` (`itemnumber`),
1477 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1478 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1479 ON DELETE SET NULL ON UPDATE SET NULL,
1480 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1481 ON DELETE SET NULL ON UPDATE SET NULL
1482 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1485 -- Table structure for table `old_reserves`
1487 DROP TABLE IF EXISTS `old_reserves`;
1488 CREATE TABLE `old_reserves` (
1489 `borrowernumber` int(11) default NULL,
1490 `reservedate` date default NULL,
1491 `biblionumber` int(11) default NULL,
1492 `constrainttype` varchar(1) default NULL,
1493 `branchcode` varchar(10) default NULL,
1494 `notificationdate` date default NULL,
1495 `reminderdate` date default NULL,
1496 `cancellationdate` date default NULL,
1497 `reservenotes` mediumtext,
1498 `priority` smallint(6) default NULL,
1499 `found` varchar(1) default NULL,
1500 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1501 `itemnumber` int(11) default NULL,
1502 `waitingdate` date default NULL,
1503 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1504 KEY `old_reserves_biblionumber` (`biblionumber`),
1505 KEY `old_reserves_itemnumber` (`itemnumber`),
1506 KEY `old_reserves_branchcode` (`branchcode`),
1507 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1508 ON DELETE SET NULL ON UPDATE SET NULL,
1509 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1510 ON DELETE SET NULL ON UPDATE SET NULL,
1511 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1512 ON DELETE SET NULL ON UPDATE SET NULL
1513 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1516 -- Table structure for table `opac_news`
1519 DROP TABLE IF EXISTS `opac_news`;
1520 CREATE TABLE `opac_news` (
1521 `idnew` int(10) unsigned NOT NULL auto_increment,
1522 `title` varchar(250) NOT NULL default '',
1523 `new` text NOT NULL,
1524 `lang` varchar(25) NOT NULL default '',
1525 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1526 `expirationdate` date default NULL,
1527 `number` int(11) default NULL,
1528 PRIMARY KEY (`idnew`)
1529 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1532 -- Table structure for table `overduerules`
1535 DROP TABLE IF EXISTS `overduerules`;
1536 CREATE TABLE `overduerules` (
1537 `branchcode` varchar(10) NOT NULL default '',
1538 `categorycode` varchar(10) NOT NULL default '',
1539 `delay1` int(4) default 0,
1540 `letter1` varchar(20) default NULL,
1541 `debarred1` varchar(1) default 0,
1542 `delay2` int(4) default 0,
1543 `debarred2` varchar(1) default 0,
1544 `letter2` varchar(20) default NULL,
1545 `delay3` int(4) default 0,
1546 `letter3` varchar(20) default NULL,
1547 `debarred3` int(1) default 0,
1548 PRIMARY KEY (`branchcode`,`categorycode`)
1549 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1552 -- Table structure for table `patroncards`
1555 DROP TABLE IF EXISTS `patroncards`;
1556 CREATE TABLE `patroncards` (
1557 `cardid` int(11) NOT NULL auto_increment,
1558 `batch_id` varchar(10) NOT NULL default '1',
1559 `borrowernumber` int(11) NOT NULL,
1560 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1561 PRIMARY KEY (`cardid`),
1562 KEY `patroncards_ibfk_1` (`borrowernumber`),
1563 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1564 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1567 -- Table structure for table `patronimage`
1570 DROP TABLE IF EXISTS `patronimage`;
1571 CREATE TABLE `patronimage` (
1572 `cardnumber` varchar(16) NOT NULL,
1573 `mimetype` varchar(15) NOT NULL,
1574 `imagefile` mediumblob NOT NULL,
1575 PRIMARY KEY (`cardnumber`),
1576 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1577 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1580 -- Table structure for table `printers`
1583 DROP TABLE IF EXISTS `printers`;
1584 CREATE TABLE `printers` (
1585 `printername` varchar(40) NOT NULL default '',
1586 `printqueue` varchar(20) default NULL,
1587 `printtype` varchar(20) default NULL,
1588 PRIMARY KEY (`printername`)
1589 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1592 -- Table structure for table `printers_profile`
1595 DROP TABLE IF EXISTS `printers_profile`;
1596 CREATE TABLE `printers_profile` (
1597 `prof_id` int(4) NOT NULL auto_increment,
1598 `printername` varchar(40) NOT NULL,
1599 `tmpl_id` int(4) NOT NULL,
1600 `paper_bin` varchar(20) NOT NULL,
1601 `offset_horz` float default NULL,
1602 `offset_vert` float default NULL,
1603 `creep_horz` float default NULL,
1604 `creep_vert` float default NULL,
1605 `unit` char(20) NOT NULL default 'POINT',
1606 PRIMARY KEY (`prof_id`),
1607 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1608 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1609 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1612 -- Table structure for table `repeatable_holidays`
1615 DROP TABLE IF EXISTS `repeatable_holidays`;
1616 CREATE TABLE `repeatable_holidays` (
1617 `id` int(11) NOT NULL auto_increment,
1618 `branchcode` varchar(10) NOT NULL default '',
1619 `weekday` smallint(6) default NULL,
1620 `day` smallint(6) default NULL,
1621 `month` smallint(6) default NULL,
1622 `title` varchar(50) NOT NULL default '',
1623 `description` text NOT NULL,
1625 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1628 -- Table structure for table `reports_dictionary`
1631 DROP TABLE IF EXISTS `reports_dictionary`;
1632 CREATE TABLE reports_dictionary (
1633 `id` int(11) NOT NULL auto_increment,
1634 `name` varchar(255) default NULL,
1636 `date_created` datetime default NULL,
1637 `date_modified` datetime default NULL,
1639 `area` int(11) default NULL,
1641 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1644 -- Table structure for table `reserveconstraints`
1647 DROP TABLE IF EXISTS `reserveconstraints`;
1648 CREATE TABLE `reserveconstraints` (
1649 `borrowernumber` int(11) NOT NULL default 0,
1650 `reservedate` date default NULL,
1651 `biblionumber` int(11) NOT NULL default 0,
1652 `biblioitemnumber` int(11) default NULL,
1653 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1654 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1657 -- Table structure for table `reserves`
1660 DROP TABLE IF EXISTS `reserves`;
1661 CREATE TABLE `reserves` (
1662 `borrowernumber` int(11) NOT NULL default 0,
1663 `reservedate` date default NULL,
1664 `biblionumber` int(11) NOT NULL default 0,
1665 `constrainttype` varchar(1) default NULL,
1666 `branchcode` varchar(10) default NULL,
1667 `notificationdate` date default NULL,
1668 `reminderdate` date default NULL,
1669 `cancellationdate` date default NULL,
1670 `reservenotes` mediumtext,
1671 `priority` smallint(6) default NULL,
1672 `found` varchar(1) default NULL,
1673 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1674 `itemnumber` int(11) default NULL,
1675 `waitingdate` date default NULL,
1676 KEY `borrowernumber` (`borrowernumber`),
1677 KEY `biblionumber` (`biblionumber`),
1678 KEY `itemnumber` (`itemnumber`),
1679 KEY `branchcode` (`branchcode`),
1680 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1681 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1682 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1683 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1684 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1687 -- Table structure for table `reviews`
1690 DROP TABLE IF EXISTS `reviews`;
1691 CREATE TABLE `reviews` (
1692 `reviewid` int(11) NOT NULL auto_increment,
1693 `borrowernumber` int(11) default NULL,
1694 `biblionumber` int(11) default NULL,
1696 `approved` tinyint(4) default NULL,
1697 `datereviewed` datetime default NULL,
1698 PRIMARY KEY (`reviewid`)
1699 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1702 -- Table structure for table `roadtype`
1705 DROP TABLE IF EXISTS `roadtype`;
1706 CREATE TABLE `roadtype` (
1707 `roadtypeid` int(11) NOT NULL auto_increment,
1708 `road_type` varchar(100) NOT NULL default '',
1709 PRIMARY KEY (`roadtypeid`)
1710 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1713 -- Table structure for table `saved_sql`
1716 DROP TABLE IF EXISTS `saved_sql`;
1717 CREATE TABLE saved_sql (
1718 `id` int(11) NOT NULL auto_increment,
1719 `borrowernumber` int(11) default NULL,
1720 `date_created` datetime default NULL,
1721 `last_modified` datetime default NULL,
1723 `last_run` datetime default NULL,
1724 `report_name` varchar(255) default NULL,
1725 `type` varchar(255) default NULL,
1728 KEY boridx (`borrowernumber`)
1729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1733 -- Table structure for `saved_reports`
1736 DROP TABLE IF EXISTS `saved_reports`;
1737 CREATE TABLE saved_reports (
1738 `id` int(11) NOT NULL auto_increment,
1739 `report_id` int(11) default NULL,
1741 `date_run` datetime default NULL,
1743 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1747 -- Table structure for table `serial`
1750 DROP TABLE IF EXISTS `serial`;
1751 CREATE TABLE `serial` (
1752 `serialid` int(11) NOT NULL auto_increment,
1753 `biblionumber` varchar(100) NOT NULL default '',
1754 `subscriptionid` varchar(100) NOT NULL default '',
1755 `serialseq` varchar(100) NOT NULL default '',
1756 `status` tinyint(4) NOT NULL default 0,
1757 `planneddate` date default NULL,
1759 `publisheddate` date default NULL,
1760 `itemnumber` text default NULL,
1761 `claimdate` date default NULL,
1762 `routingnotes` text,
1763 PRIMARY KEY (`serialid`)
1764 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1767 -- Table structure for table `sessions`
1770 DROP TABLE IF EXISTS sessions;
1771 CREATE TABLE sessions (
1772 `id` varchar(32) NOT NULL,
1773 `a_session` text NOT NULL,
1775 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1778 -- Table structure for table `special_holidays`
1781 DROP TABLE IF EXISTS `special_holidays`;
1782 CREATE TABLE `special_holidays` (
1783 `id` int(11) NOT NULL auto_increment,
1784 `branchcode` varchar(10) NOT NULL default '',
1785 `day` smallint(6) NOT NULL default 0,
1786 `month` smallint(6) NOT NULL default 0,
1787 `year` smallint(6) NOT NULL default 0,
1788 `isexception` smallint(1) NOT NULL default 1,
1789 `title` varchar(50) NOT NULL default '',
1790 `description` text NOT NULL,
1792 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1795 -- Table structure for table `statistics`
1798 DROP TABLE IF EXISTS `statistics`;
1799 CREATE TABLE `statistics` (
1800 `datetime` datetime default NULL,
1801 `branch` varchar(10) default NULL,
1802 `proccode` varchar(4) default NULL,
1803 `value` double(16,4) default NULL,
1804 `type` varchar(16) default NULL,
1806 `usercode` varchar(10) default NULL,
1807 `itemnumber` int(11) default NULL,
1808 `itemtype` varchar(10) default NULL,
1809 `borrowernumber` int(11) default NULL,
1810 `associatedborrower` int(11) default NULL,
1811 KEY `timeidx` (`datetime`)
1812 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1815 -- Table structure for table `stopwords`
1818 DROP TABLE IF EXISTS `stopwords`;
1819 CREATE TABLE `stopwords` (
1820 `word` varchar(255) default NULL
1821 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1824 -- Table structure for table `subscription`
1827 DROP TABLE IF EXISTS `subscription`;
1828 CREATE TABLE `subscription` (
1829 `biblionumber` int(11) NOT NULL default 0,
1830 `subscriptionid` int(11) NOT NULL auto_increment,
1831 `librarian` varchar(100) default '',
1832 `startdate` date default NULL,
1833 `aqbooksellerid` int(11) default 0,
1834 `cost` int(11) default 0,
1835 `aqbudgetid` int(11) default 0,
1836 `weeklength` int(11) default 0,
1837 `monthlength` int(11) default 0,
1838 `numberlength` int(11) default 0,
1839 `periodicity` tinyint(4) default 0,
1840 `dow` varchar(100) default '',
1841 `numberingmethod` varchar(100) default '',
1843 `status` varchar(100) NOT NULL default '',
1844 `add1` int(11) default 0,
1845 `every1` int(11) default 0,
1846 `whenmorethan1` int(11) default 0,
1847 `setto1` int(11) default NULL,
1848 `lastvalue1` int(11) default NULL,
1849 `add2` int(11) default 0,
1850 `every2` int(11) default 0,
1851 `whenmorethan2` int(11) default 0,
1852 `setto2` int(11) default NULL,
1853 `lastvalue2` int(11) default NULL,
1854 `add3` int(11) default 0,
1855 `every3` int(11) default 0,
1856 `innerloop1` int(11) default 0,
1857 `innerloop2` int(11) default 0,
1858 `innerloop3` int(11) default 0,
1859 `whenmorethan3` int(11) default 0,
1860 `setto3` int(11) default NULL,
1861 `lastvalue3` int(11) default NULL,
1862 `issuesatonce` tinyint(3) NOT NULL default 1,
1863 `firstacquidate` date default NULL,
1864 `manualhistory` tinyint(1) NOT NULL default 0,
1865 `irregularity` text,
1866 `letter` varchar(20) default NULL,
1867 `numberpattern` tinyint(3) default 0,
1868 `distributedto` text,
1869 `internalnotes` longtext,
1871 `branchcode` varchar(10) NOT NULL default '',
1872 `hemisphere` tinyint(3) default 0,
1873 `lastbranch` varchar(10),
1874 `serialsadditems` tinyint(1) NOT NULL default '0',
1875 PRIMARY KEY (`subscriptionid`)
1876 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1879 -- Table structure for table `subscriptionhistory`
1882 DROP TABLE IF EXISTS `subscriptionhistory`;
1883 CREATE TABLE `subscriptionhistory` (
1884 `biblionumber` int(11) NOT NULL default 0,
1885 `subscriptionid` int(11) NOT NULL default 0,
1886 `histstartdate` date default NULL,
1887 `enddate` date default NULL,
1888 `missinglist` longtext NOT NULL,
1889 `recievedlist` longtext NOT NULL,
1890 `opacnote` varchar(150) NOT NULL default '',
1891 `librariannote` varchar(150) NOT NULL default '',
1892 PRIMARY KEY (`subscriptionid`),
1893 KEY `biblionumber` (`biblionumber`)
1894 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1897 -- Table structure for table `subscriptionroutinglist`
1900 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1901 CREATE TABLE `subscriptionroutinglist` (
1902 `routingid` int(11) NOT NULL auto_increment,
1903 `borrowernumber` int(11) default NULL,
1904 `ranking` int(11) default NULL,
1905 `subscriptionid` int(11) default NULL,
1906 PRIMARY KEY (`routingid`)
1907 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1910 -- Table structure for table `suggestions`
1913 DROP TABLE IF EXISTS `suggestions`;
1914 CREATE TABLE `suggestions` (
1915 `suggestionid` int(8) NOT NULL auto_increment,
1916 `suggestedby` int(11) NOT NULL default 0,
1917 `managedby` int(11) default NULL,
1918 `STATUS` varchar(10) NOT NULL default '',
1920 `author` varchar(80) default NULL,
1921 `title` varchar(80) default NULL,
1922 `copyrightdate` smallint(6) default NULL,
1923 `publishercode` varchar(255) default NULL,
1924 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1925 `volumedesc` varchar(255) default NULL,
1926 `publicationyear` smallint(6) default 0,
1927 `place` varchar(255) default NULL,
1928 `isbn` varchar(30) default NULL,
1929 `mailoverseeing` smallint(1) default 0,
1930 `biblionumber` int(11) default NULL,
1932 PRIMARY KEY (`suggestionid`),
1933 KEY `suggestedby` (`suggestedby`),
1934 KEY `managedby` (`managedby`)
1935 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1938 -- Table structure for table `systempreferences`
1941 DROP TABLE IF EXISTS `systempreferences`;
1942 CREATE TABLE `systempreferences` (
1943 `variable` varchar(50) NOT NULL default '',
1945 `options` mediumtext,
1947 `type` varchar(20) default NULL,
1948 PRIMARY KEY (`variable`)
1949 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1952 -- Table structure for table `tags`
1955 DROP TABLE IF EXISTS `tags`;
1956 CREATE TABLE `tags` (
1957 `entry` varchar(255) NOT NULL default '',
1958 `weight` bigint(20) NOT NULL default 0,
1959 PRIMARY KEY (`entry`)
1960 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1963 -- Table structure for table `tags_all`
1966 DROP TABLE IF EXISTS `tags_all`;
1967 CREATE TABLE `tags_all` (
1968 `tag_id` int(11) NOT NULL auto_increment,
1969 `borrowernumber` int(11) NOT NULL,
1970 `biblionumber` int(11) NOT NULL,
1971 `term` varchar(255) NOT NULL,
1972 `language` int(4) default NULL,
1973 `date_created` datetime NOT NULL,
1974 PRIMARY KEY (`tag_id`),
1975 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1976 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1977 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1978 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1979 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1980 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1981 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1984 -- Table structure for table `tags_approval`
1987 DROP TABLE IF EXISTS `tags_approval`;
1988 CREATE TABLE `tags_approval` (
1989 `term` varchar(255) NOT NULL,
1990 `approved` int(1) NOT NULL default '0',
1991 `date_approved` datetime default NULL,
1992 `approved_by` int(11) default NULL,
1993 `weight_total` int(9) NOT NULL default '1',
1994 PRIMARY KEY (`term`),
1995 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1996 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1997 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1998 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2001 -- Table structure for table `tags_index`
2004 DROP TABLE IF EXISTS `tags_index`;
2005 CREATE TABLE `tags_index` (
2006 `term` varchar(255) NOT NULL,
2007 `biblionumber` int(11) NOT NULL,
2008 `weight` int(9) NOT NULL default '1',
2009 PRIMARY KEY (`term`,`biblionumber`),
2010 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2011 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2012 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2013 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2014 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2015 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2018 -- Table structure for table `userflags`
2021 DROP TABLE IF EXISTS `userflags`;
2022 CREATE TABLE `userflags` (
2023 `bit` int(11) NOT NULL default 0,
2024 `flag` varchar(30) default NULL,
2025 `flagdesc` varchar(255) default NULL,
2026 `defaulton` int(11) default NULL,
2028 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2031 -- Table structure for table `virtualshelves`
2034 DROP TABLE IF EXISTS `virtualshelves`;
2035 CREATE TABLE `virtualshelves` (
2036 `shelfnumber` int(11) NOT NULL auto_increment,
2037 `shelfname` varchar(255) default NULL,
2038 `owner` varchar(80) default NULL,
2039 `category` varchar(1) default NULL,
2040 `sortfield` varchar(16) default NULL,
2041 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2042 PRIMARY KEY (`shelfnumber`)
2043 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2046 -- Table structure for table `virtualshelfcontents`
2049 DROP TABLE IF EXISTS `virtualshelfcontents`;
2050 CREATE TABLE `virtualshelfcontents` (
2051 `shelfnumber` int(11) NOT NULL default 0,
2052 `biblionumber` int(11) NOT NULL default 0,
2053 `flags` int(11) default NULL,
2054 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2055 KEY `shelfnumber` (`shelfnumber`),
2056 KEY `biblionumber` (`biblionumber`),
2057 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2058 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2059 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2062 -- Table structure for table `z3950servers`
2065 DROP TABLE IF EXISTS `z3950servers`;
2066 CREATE TABLE `z3950servers` (
2067 `host` varchar(255) default NULL,
2068 `port` int(11) default NULL,
2069 `db` varchar(255) default NULL,
2070 `userid` varchar(255) default NULL,
2071 `password` varchar(255) default NULL,
2073 `id` int(11) NOT NULL auto_increment,
2074 `checked` smallint(6) default NULL,
2075 `rank` int(11) default NULL,
2076 `syntax` varchar(80) default NULL,
2078 `position` enum('primary','secondary','') NOT NULL default 'primary',
2079 `type` enum('zed','opensearch') NOT NULL default 'zed',
2080 `encoding` text default NULL,
2081 `description` text NOT NULL,
2083 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2086 -- Table structure for table `zebraqueue`
2089 DROP TABLE IF EXISTS `zebraqueue`;
2090 CREATE TABLE `zebraqueue` (
2091 `id` int(11) NOT NULL auto_increment,
2092 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2093 `operation` char(20) NOT NULL default '',
2094 `server` char(20) NOT NULL default '',
2095 `done` int(11) NOT NULL default '0',
2096 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2098 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2099 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2101 DROP TABLE IF EXISTS `services_throttle`;
2102 CREATE TABLE `services_throttle` (
2103 `service_type` varchar(10) NOT NULL default '',
2104 `service_count` varchar(45) default NULL,
2105 PRIMARY KEY (`service_type`)
2106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2108 -- http://www.w3.org/International/articles/language-tags/
2111 DROP TABLE IF EXISTS language_subtag_registry;
2112 CREATE TABLE language_subtag_registry (
2114 type varchar(25), -- language-script-region-variant-extension-privateuse
2115 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2117 KEY `subtag` (`subtag`)
2118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2120 -- TODO: add suppress_scripts
2121 -- this maps three letter codes defined in iso639.2 back to their
2122 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2123 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2124 CREATE TABLE language_rfc4646_to_iso639 (
2125 rfc4646_subtag varchar(25),
2126 iso639_2_code varchar(25),
2127 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2128 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2130 DROP TABLE IF EXISTS language_descriptions;
2131 CREATE TABLE language_descriptions (
2135 description varchar(255),
2137 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2139 -- bi-directional support, keyed by script subcode
2140 DROP TABLE IF EXISTS language_script_bidi;
2141 CREATE TABLE language_script_bidi (
2142 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2143 bidi varchar(3), -- rtl ltr
2144 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2145 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2147 -- TODO: need to map language subtags to script subtags for detection
2148 -- of bidi when script is not specified (like ar, he)
2149 DROP TABLE IF EXISTS language_script_mapping;
2150 CREATE TABLE language_script_mapping (
2151 language_subtag varchar(25),
2152 script_subtag varchar(25),
2153 KEY `language_subtag` (`language_subtag`)
2154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2156 DROP TABLE IF EXISTS `permissions`;
2157 CREATE TABLE `permissions` (
2158 `module_bit` int(11) NOT NULL DEFAULT 0,
2159 `code` varchar(64) DEFAULT NULL,
2160 `description` varchar(255) DEFAULT NULL,
2161 PRIMARY KEY (`module_bit`, `code`),
2162 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2163 ON DELETE CASCADE ON UPDATE CASCADE
2164 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2166 DROP TABLE IF EXISTS `serialitems`;
2167 CREATE TABLE `serialitems` (
2168 `itemnumber` int(11) NOT NULL,
2169 `serialid` int(11) NOT NULL,
2170 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2171 KEY `serialitems_sfk_1` (`serialid`),
2172 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2173 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2175 DROP TABLE IF EXISTS `user_permissions`;
2176 CREATE TABLE `user_permissions` (
2177 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2178 `module_bit` int(11) NOT NULL DEFAULT 0,
2179 `code` varchar(64) DEFAULT NULL,
2180 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2181 ON DELETE CASCADE ON UPDATE CASCADE,
2182 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2183 ON DELETE CASCADE ON UPDATE CASCADE
2184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2187 -- Table structure for table `tmp_holdsqueue`
2190 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2191 CREATE TABLE `tmp_holdsqueue` (
2192 `biblionumber` int(11) default NULL,
2193 `itemnumber` int(11) default NULL,
2194 `barcode` varchar(20) default NULL,
2195 `surname` mediumtext NOT NULL,
2198 `borrowernumber` int(11) NOT NULL,
2199 `cardnumber` varchar(16) default NULL,
2200 `reservedate` date default NULL,
2202 `itemcallnumber` varchar(30) default NULL,
2203 `holdingbranch` varchar(10) default NULL,
2204 `pickbranch` varchar(10) default NULL,
2206 `item_level_request` tinyint(4) NOT NULL default 0
2207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2210 -- Table structure for table `message_queue`
2213 DROP TABLE IF EXISTS `message_queue`;
2214 CREATE TABLE `message_queue` (
2215 `message_id` int(11) NOT NULL auto_increment,
2216 `borrowernumber` int(11) default NULL,
2219 `message_transport_type` varchar(20) NOT NULL,
2220 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2221 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2222 `to_address` mediumtext,
2223 `from_address` mediumtext,
2224 `content_type` text,
2225 KEY `message_id` (`message_id`),
2226 KEY `borrowernumber` (`borrowernumber`),
2227 KEY `message_transport_type` (`message_transport_type`),
2228 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2229 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2233 -- Table structure for table `message_transport_types`
2236 DROP TABLE IF EXISTS `message_transport_types`;
2237 CREATE TABLE `message_transport_types` (
2238 `message_transport_type` varchar(20) NOT NULL,
2239 PRIMARY KEY (`message_transport_type`)
2240 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2243 -- Table structure for table `message_attributes`
2246 DROP TABLE IF EXISTS `message_attributes`;
2247 CREATE TABLE `message_attributes` (
2248 `message_attribute_id` int(11) NOT NULL auto_increment,
2249 `message_name` varchar(20) NOT NULL default '',
2250 `takes_days` tinyint(1) NOT NULL default '0',
2251 PRIMARY KEY (`message_attribute_id`),
2252 UNIQUE KEY `message_name` (`message_name`)
2253 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2256 -- Table structure for table `message_transports`
2259 DROP TABLE IF EXISTS `message_transports`;
2260 CREATE TABLE `message_transports` (
2261 `message_attribute_id` int(11) NOT NULL,
2262 `message_transport_type` varchar(20) NOT NULL,
2263 `is_digest` tinyint(1) NOT NULL default '0',
2264 `letter_module` varchar(20) NOT NULL default '',
2265 `letter_code` varchar(20) NOT NULL default '',
2266 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2267 KEY `message_transport_type` (`message_transport_type`),
2268 KEY `letter_module` (`letter_module`,`letter_code`),
2269 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2270 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2271 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2275 -- Table structure for table `borrower_message_preferences`
2278 DROP TABLE IF EXISTS `borrower_message_preferences`;
2279 CREATE TABLE `borrower_message_preferences` (
2280 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2281 `borrowernumber` int(11) NOT NULL default '0',
2282 `message_attribute_id` int(11) default '0',
2283 `days_in_advance` int(11) default '0',
2284 `wants_digest` tinyint(1) NOT NULL default '0',
2285 PRIMARY KEY (`borrower_message_preference_id`),
2286 KEY `borrowernumber` (`borrowernumber`),
2287 KEY `message_attribute_id` (`message_attribute_id`),
2288 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2289 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
2290 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2293 -- Table structure for table `borrower_message_transport_preferences`
2296 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2297 CREATE TABLE `borrower_message_transport_preferences` (
2298 `borrower_message_preference_id` int(11) NOT NULL default '0',
2299 `message_transport_type` varchar(20) NOT NULL default '0',
2300 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2301 KEY `message_transport_type` (`message_transport_type`),
2302 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,
2303 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
2304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2306 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2307 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2308 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2309 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2310 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2311 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2312 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2313 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;