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