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_branch_item_rules`
684 CREATE TABLE `default_branch_item_rules` (
685 `itemtype` varchar(10) NOT NULL,
686 `holdallowed` tinyint(1) default NULL,
687 PRIMARY KEY (`itemtype`),
688 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
689 ON DELETE CASCADE ON UPDATE CASCADE
690 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
693 -- Table structure for table `default_circ_rules`
696 DROP TABLE IF EXISTS `default_circ_rules`;
697 CREATE TABLE `default_circ_rules` (
698 `singleton` enum('singleton') NOT NULL default 'singleton',
699 `maxissueqty` int(4) default NULL,
700 PRIMARY KEY (`singleton`)
701 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
704 -- Table structure for table `cities`
707 DROP TABLE IF EXISTS `cities`;
708 CREATE TABLE `cities` (
709 `cityid` int(11) NOT NULL auto_increment,
710 `city_name` varchar(100) NOT NULL default '',
711 `city_zipcode` varchar(20) default NULL,
712 PRIMARY KEY (`cityid`)
713 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
716 -- Table structure for table `class_sort_rules`
719 DROP TABLE IF EXISTS `class_sort_rules`;
720 CREATE TABLE `class_sort_rules` (
721 `class_sort_rule` varchar(10) NOT NULL default '',
722 `description` mediumtext,
723 `sort_routine` varchar(30) NOT NULL default '',
724 PRIMARY KEY (`class_sort_rule`),
725 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
726 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
729 -- Table structure for table `class_sources`
732 DROP TABLE IF EXISTS `class_sources`;
733 CREATE TABLE `class_sources` (
734 `cn_source` varchar(10) NOT NULL default '',
735 `description` mediumtext,
736 `used` tinyint(4) NOT NULL default 0,
737 `class_sort_rule` varchar(10) NOT NULL default '',
738 PRIMARY KEY (`cn_source`),
739 UNIQUE KEY `cn_source_idx` (`cn_source`),
740 KEY `used_idx` (`used`),
741 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
742 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
745 -- Table structure for table `currency`
748 DROP TABLE IF EXISTS `currency`;
749 CREATE TABLE `currency` (
750 `currency` varchar(10) NOT NULL default '',
751 `symbol` varchar(5) default NULL,
752 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
753 `rate` float(7,5) default NULL,
754 PRIMARY KEY (`currency`)
755 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
758 -- Table structure for table `deletedbiblio`
761 DROP TABLE IF EXISTS `deletedbiblio`;
762 CREATE TABLE `deletedbiblio` (
763 `biblionumber` int(11) NOT NULL default 0,
764 `frameworkcode` varchar(4) NOT NULL default '',
767 `unititle` mediumtext,
769 `serial` tinyint(1) default NULL,
770 `seriestitle` mediumtext,
771 `copyrightdate` smallint(6) default NULL,
772 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
773 `datecreated` DATE NOT NULL,
774 `abstract` mediumtext,
775 PRIMARY KEY (`biblionumber`),
776 KEY `blbnoidx` (`biblionumber`)
777 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
780 -- Table structure for table `deletedbiblioitems`
783 DROP TABLE IF EXISTS `deletedbiblioitems`;
784 CREATE TABLE `deletedbiblioitems` (
785 `biblioitemnumber` int(11) NOT NULL default 0,
786 `biblionumber` int(11) NOT NULL default 0,
789 `itemtype` varchar(10) default NULL,
790 `isbn` varchar(30) default NULL,
791 `issn` varchar(9) default NULL,
792 `publicationyear` text,
793 `publishercode` varchar(255) default NULL,
794 `volumedate` date default NULL,
796 `collectiontitle` mediumtext default NULL,
797 `collectionissn` text default NULL,
798 `collectionvolume` mediumtext default NULL,
799 `editionstatement` text default NULL,
800 `editionresponsibility` text default NULL,
801 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
802 `illus` varchar(255) default NULL,
803 `pages` varchar(255) default NULL,
805 `size` varchar(255) default NULL,
806 `place` varchar(255) default NULL,
807 `lccn` varchar(25) default NULL,
809 `url` varchar(255) default NULL,
810 `cn_source` varchar(10) default NULL,
811 `cn_class` varchar(30) default NULL,
812 `cn_item` varchar(10) default NULL,
813 `cn_suffix` varchar(10) default NULL,
814 `cn_sort` varchar(30) default NULL,
815 `totalissues` int(10),
816 `marcxml` longtext NOT NULL,
817 PRIMARY KEY (`biblioitemnumber`),
818 KEY `bibinoidx` (`biblioitemnumber`),
819 KEY `bibnoidx` (`biblionumber`),
821 KEY `publishercode` (`publishercode`)
822 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
825 -- Table structure for table `deletedborrowers`
828 DROP TABLE IF EXISTS `deletedborrowers`;
829 CREATE TABLE `deletedborrowers` (
830 `borrowernumber` int(11) NOT NULL default 0,
831 `cardnumber` varchar(9) NOT NULL default '',
832 `surname` mediumtext NOT NULL,
835 `othernames` mediumtext,
837 `streetnumber` varchar(10) default NULL,
838 `streettype` varchar(50) default NULL,
839 `address` mediumtext NOT NULL,
841 `city` mediumtext NOT NULL,
842 `zipcode` varchar(25) default NULL,
845 `mobile` varchar(50) default NULL,
849 `B_streetnumber` varchar(10) default NULL,
850 `B_streettype` varchar(50) default NULL,
851 `B_address` varchar(100) default NULL,
853 `B_zipcode` varchar(25) default NULL,
855 `B_phone` mediumtext,
856 `dateofbirth` date default NULL,
857 `branchcode` varchar(10) NOT NULL default '',
858 `categorycode` varchar(10) default NULL,
859 `dateenrolled` date default NULL,
860 `dateexpiry` date default NULL,
861 `gonenoaddress` tinyint(1) default NULL,
862 `lost` tinyint(1) default NULL,
863 `debarred` tinyint(1) default NULL,
864 `contactname` mediumtext,
865 `contactfirstname` text,
867 `guarantorid` int(11) default NULL,
868 `borrowernotes` mediumtext,
869 `relationship` varchar(100) default NULL,
870 `ethnicity` varchar(50) default NULL,
871 `ethnotes` varchar(255) default NULL,
872 `sex` varchar(1) default NULL,
873 `password` varchar(30) default NULL,
874 `flags` int(11) default NULL,
875 `userid` varchar(30) default NULL,
876 `opacnote` mediumtext,
877 `contactnote` varchar(255) default NULL,
878 `sort1` varchar(80) default NULL,
879 `sort2` varchar(80) default NULL,
880 `altcontactfirstname` varchar(255) default NULL,
881 `altcontactsurname` varchar(255) default NULL,
882 `altcontactaddress1` varchar(255) default NULL,
883 `altcontactaddress2` varchar(255) default NULL,
884 `altcontactaddress3` varchar(255) default NULL,
885 `altcontactzipcode` varchar(50) default NULL,
886 `altcontactphone` varchar(50) default NULL,
887 KEY `borrowernumber` (`borrowernumber`),
888 KEY `cardnumber` (`cardnumber`)
889 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
892 -- Table structure for table `deleteditems`
895 DROP TABLE IF EXISTS `deleteditems`;
896 CREATE TABLE `deleteditems` (
897 `itemnumber` int(11) NOT NULL default 0,
898 `biblionumber` int(11) NOT NULL default 0,
899 `biblioitemnumber` int(11) NOT NULL default 0,
900 `barcode` varchar(20) default NULL,
901 `dateaccessioned` date default NULL,
902 `booksellerid` mediumtext default NULL,
903 `homebranch` varchar(10) default NULL,
904 `price` decimal(8,2) default NULL,
905 `replacementprice` decimal(8,2) default NULL,
906 `replacementpricedate` date default NULL,
907 `datelastborrowed` date default NULL,
908 `datelastseen` date default NULL,
909 `stack` tinyint(1) default NULL,
910 `notforloan` tinyint(1) NOT NULL default 0,
911 `damaged` tinyint(1) NOT NULL default 0,
912 `itemlost` tinyint(1) NOT NULL default 0,
913 `wthdrawn` tinyint(1) NOT NULL default 0,
914 `itemcallnumber` varchar(30) default NULL,
915 `issues` smallint(6) default NULL,
916 `renewals` smallint(6) default NULL,
917 `reserves` smallint(6) default NULL,
918 `restricted` tinyint(1) default NULL,
919 `itemnotes` mediumtext,
920 `holdingbranch` varchar(10) default NULL,
921 `paidfor` mediumtext,
922 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
923 `location` varchar(80) default NULL,
924 `onloan` date default NULL,
925 `cn_source` varchar(10) default NULL,
926 `cn_sort` varchar(30) default NULL,
927 `ccode` varchar(10) default NULL,
928 `materials` varchar(10) default NULL,
929 `uri` varchar(255) default NULL,
930 `itype` varchar(10) default NULL,
931 `more_subfields_xml` longtext default NULL,
932 `enumchron` varchar(80) default NULL,
933 `copynumber` varchar(32) default NULL,
935 PRIMARY KEY (`itemnumber`),
936 KEY `delitembarcodeidx` (`barcode`),
937 KEY `delitembinoidx` (`biblioitemnumber`),
938 KEY `delitembibnoidx` (`biblionumber`),
939 KEY `delhomebranch` (`homebranch`),
940 KEY `delholdingbranch` (`holdingbranch`)
941 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
944 -- Table structure for table `ethnicity`
947 DROP TABLE IF EXISTS `ethnicity`;
948 CREATE TABLE `ethnicity` (
949 `code` varchar(10) NOT NULL default '',
950 `name` varchar(255) default NULL,
952 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
955 -- Table structure for table `hold_fill_targets`
958 DROP TABLE IF EXISTS `hold_fill_targets`;
959 CREATE TABLE hold_fill_targets (
960 `borrowernumber` int(11) NOT NULL,
961 `biblionumber` int(11) NOT NULL,
962 `itemnumber` int(11) NOT NULL,
963 `source_branchcode` varchar(10) default NULL,
964 `item_level_request` tinyint(4) NOT NULL default 0,
965 PRIMARY KEY `itemnumber` (`itemnumber`),
966 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
967 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
968 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
969 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
970 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
971 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
972 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
973 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
974 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
975 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
978 -- Table structure for table `import_batches`
981 DROP TABLE IF EXISTS `import_batches`;
982 CREATE TABLE `import_batches` (
983 `import_batch_id` int(11) NOT NULL auto_increment,
984 `matcher_id` int(11) default NULL,
985 `template_id` int(11) default NULL,
986 `branchcode` varchar(10) default NULL,
987 `num_biblios` int(11) NOT NULL default 0,
988 `num_items` int(11) NOT NULL default 0,
989 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
990 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
991 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
992 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
993 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
994 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
995 `file_name` varchar(100),
996 `comments` mediumtext,
997 PRIMARY KEY (`import_batch_id`),
998 KEY `branchcode` (`branchcode`)
999 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1002 -- Table structure for table `import_records`
1005 DROP TABLE IF EXISTS `import_records`;
1006 CREATE TABLE `import_records` (
1007 `import_record_id` int(11) NOT NULL auto_increment,
1008 `import_batch_id` int(11) NOT NULL,
1009 `branchcode` varchar(10) default NULL,
1010 `record_sequence` int(11) NOT NULL default 0,
1011 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1012 `import_date` DATE default NULL,
1013 `marc` longblob NOT NULL,
1014 `marcxml` longtext NOT NULL,
1015 `marcxml_old` longtext NOT NULL,
1016 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1017 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1018 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1019 `import_error` mediumtext,
1020 `encoding` varchar(40) NOT NULL default '',
1021 `z3950random` varchar(40) default NULL,
1022 PRIMARY KEY (`import_record_id`),
1023 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1024 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1025 KEY `branchcode` (`branchcode`),
1026 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1027 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1030 -- Table structure for `import_record_matches`
1032 DROP TABLE IF EXISTS `import_record_matches`;
1033 CREATE TABLE `import_record_matches` (
1034 `import_record_id` int(11) NOT NULL,
1035 `candidate_match_id` int(11) NOT NULL,
1036 `score` int(11) NOT NULL default 0,
1037 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1038 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1039 KEY `record_score` (`import_record_id`, `score`)
1040 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1043 -- Table structure for table `import_biblios`
1046 DROP TABLE IF EXISTS `import_biblios`;
1047 CREATE TABLE `import_biblios` (
1048 `import_record_id` int(11) NOT NULL,
1049 `matched_biblionumber` int(11) default NULL,
1050 `control_number` varchar(25) default NULL,
1051 `original_source` varchar(25) default NULL,
1052 `title` varchar(128) default NULL,
1053 `author` varchar(80) default NULL,
1054 `isbn` varchar(30) default NULL,
1055 `issn` varchar(9) default NULL,
1056 `has_items` tinyint(1) NOT NULL default 0,
1057 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1058 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1059 KEY `matched_biblionumber` (`matched_biblionumber`),
1060 KEY `title` (`title`),
1062 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1065 -- Table structure for table `import_items`
1068 DROP TABLE IF EXISTS `import_items`;
1069 CREATE TABLE `import_items` (
1070 `import_items_id` int(11) NOT NULL auto_increment,
1071 `import_record_id` int(11) NOT NULL,
1072 `itemnumber` int(11) default NULL,
1073 `branchcode` varchar(10) default NULL,
1074 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1075 `marcxml` longtext NOT NULL,
1076 `import_error` mediumtext,
1077 PRIMARY KEY (`import_items_id`),
1078 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1079 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1080 KEY `itemnumber` (`itemnumber`),
1081 KEY `branchcode` (`branchcode`)
1082 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1085 -- Table structure for table `issues`
1088 DROP TABLE IF EXISTS `issues`;
1089 CREATE TABLE `issues` (
1090 `borrowernumber` int(11) default NULL,
1091 `itemnumber` int(11) default NULL,
1092 `date_due` date default NULL,
1093 `branchcode` varchar(10) default NULL,
1094 `issuingbranch` varchar(18) default NULL,
1095 `returndate` date default NULL,
1096 `lastreneweddate` date default NULL,
1097 `return` varchar(4) default NULL,
1098 `renewals` tinyint(4) default NULL,
1099 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1100 `issuedate` date default NULL,
1101 KEY `issuesborridx` (`borrowernumber`),
1102 KEY `issuesitemidx` (`itemnumber`),
1103 KEY `bordate` (`borrowernumber`,`timestamp`),
1104 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1105 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1109 -- Table structure for table `issuingrules`
1112 DROP TABLE IF EXISTS `issuingrules`;
1113 CREATE TABLE `issuingrules` (
1114 `categorycode` varchar(10) NOT NULL default '',
1115 `itemtype` varchar(10) NOT NULL default '',
1116 `restrictedtype` tinyint(1) default NULL,
1117 `rentaldiscount` decimal(28,6) default NULL,
1118 `reservecharge` decimal(28,6) default NULL,
1119 `fine` decimal(28,6) default NULL,
1120 `finedays` int(11) default NULL,
1121 `firstremind` int(11) default NULL,
1122 `chargeperiod` int(11) default NULL,
1123 `accountsent` int(11) default NULL,
1124 `chargename` varchar(100) default NULL,
1125 `maxissueqty` int(4) default NULL,
1126 `issuelength` int(4) default NULL,
1127 `branchcode` varchar(10) NOT NULL default '',
1128 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1129 KEY `categorycode` (`categorycode`),
1130 KEY `itemtype` (`itemtype`)
1131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1134 -- Table structure for table `items`
1137 DROP TABLE IF EXISTS `items`;
1138 CREATE TABLE `items` (
1139 `itemnumber` int(11) NOT NULL auto_increment,
1140 `biblionumber` int(11) NOT NULL default 0,
1141 `biblioitemnumber` int(11) NOT NULL default 0,
1142 `barcode` varchar(20) default NULL,
1143 `dateaccessioned` date default NULL,
1144 `booksellerid` mediumtext default NULL,
1145 `homebranch` varchar(10) default NULL,
1146 `price` decimal(8,2) default NULL,
1147 `replacementprice` decimal(8,2) default NULL,
1148 `replacementpricedate` date default NULL,
1149 `datelastborrowed` date default NULL,
1150 `datelastseen` date default NULL,
1151 `stack` tinyint(1) default NULL,
1152 `notforloan` tinyint(1) NOT NULL default 0,
1153 `damaged` tinyint(1) NOT NULL default 0,
1154 `itemlost` tinyint(1) NOT NULL default 0,
1155 `wthdrawn` tinyint(1) NOT NULL default 0,
1156 `itemcallnumber` varchar(30) default NULL,
1157 `issues` smallint(6) default NULL,
1158 `renewals` smallint(6) default NULL,
1159 `reserves` smallint(6) default NULL,
1160 `restricted` tinyint(1) default NULL,
1161 `itemnotes` mediumtext,
1162 `holdingbranch` varchar(10) default NULL,
1163 `paidfor` mediumtext,
1164 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1165 `location` varchar(80) default NULL,
1166 `onloan` date default NULL,
1167 `cn_source` varchar(10) default NULL,
1168 `cn_sort` varchar(30) default NULL,
1169 `ccode` varchar(10) default NULL,
1170 `materials` varchar(10) default NULL,
1171 `uri` varchar(255) default NULL,
1172 `itype` varchar(10) default NULL,
1173 `more_subfields_xml` longtext default NULL,
1174 `enumchron` varchar(80) default NULL,
1175 `copynumber` varchar(32) default NULL,
1176 PRIMARY KEY (`itemnumber`),
1177 UNIQUE KEY `itembarcodeidx` (`barcode`),
1178 KEY `itembinoidx` (`biblioitemnumber`),
1179 KEY `itembibnoidx` (`biblionumber`),
1180 KEY `homebranch` (`homebranch`),
1181 KEY `holdingbranch` (`holdingbranch`),
1182 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1183 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1184 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1188 -- Table structure for table `itemtypes`
1191 DROP TABLE IF EXISTS `itemtypes`;
1192 CREATE TABLE `itemtypes` (
1193 `itemtype` varchar(10) NOT NULL default '',
1194 `description` mediumtext,
1195 `renewalsallowed` smallint(6) default NULL,
1196 `rentalcharge` double(16,4) default NULL,
1197 `notforloan` smallint(6) default NULL,
1198 `imageurl` varchar(200) default NULL,
1200 PRIMARY KEY (`itemtype`),
1201 UNIQUE KEY `itemtype` (`itemtype`)
1202 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1205 -- Table structure for table `labels`
1208 DROP TABLE IF EXISTS `labels`;
1209 CREATE TABLE `labels` (
1210 `labelid` int(11) NOT NULL auto_increment,
1211 `batch_id` varchar(10) NOT NULL default 1,
1212 `itemnumber` varchar(100) NOT NULL default '',
1213 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1214 PRIMARY KEY (`labelid`)
1215 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1218 -- Table structure for table `labels_conf`
1221 DROP TABLE IF EXISTS `labels_conf`;
1222 CREATE TABLE `labels_conf` (
1223 `id` int(4) NOT NULL auto_increment,
1224 `barcodetype` char(100) default '',
1225 `title` int(1) default '0',
1226 `subtitle` int(1) default '0',
1227 `itemtype` int(1) default '0',
1228 `barcode` int(1) default '0',
1229 `dewey` int(1) default '0',
1230 `classification` int(1) default NULL,
1231 `subclass` int(1) default '0',
1232 `itemcallnumber` int(1) default '0',
1233 `author` int(1) default '0',
1234 `issn` int(1) default '0',
1235 `isbn` int(1) default '0',
1236 `startlabel` int(2) NOT NULL default '1',
1237 `printingtype` char(32) default 'BAR',
1238 `formatstring` varchar(64) default NULL,
1239 `layoutname` char(20) NOT NULL default 'TEST',
1240 `guidebox` int(1) default '0',
1241 `active` tinyint(1) default '1',
1242 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1243 `ccode` char(4) collate utf8_unicode_ci default NULL,
1244 `callnum_split` int(1) default NULL,
1245 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1247 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1250 -- Table structure for table `labels_profile`
1253 DROP TABLE IF EXISTS `labels_profile`;
1254 CREATE TABLE `labels_profile` (
1255 `tmpl_id` int(4) NOT NULL,
1256 `prof_id` int(4) NOT NULL,
1257 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1258 UNIQUE KEY `prof_id` (`prof_id`)
1259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1262 -- Table structure for table `labels_templates`
1265 DROP TABLE IF EXISTS `labels_templates`;
1266 CREATE TABLE `labels_templates` (
1267 `tmpl_id` int(4) NOT NULL auto_increment,
1268 `tmpl_code` char(100) default '',
1269 `tmpl_desc` char(100) default '',
1270 `page_width` float default '0',
1271 `page_height` float default '0',
1272 `label_width` float default '0',
1273 `label_height` float default '0',
1274 `topmargin` float default '0',
1275 `leftmargin` float default '0',
1276 `cols` int(2) default '0',
1277 `rows` int(2) default '0',
1278 `colgap` float default '0',
1279 `rowgap` float default '0',
1280 `active` int(1) default NULL,
1281 `units` char(20) default 'PX',
1282 `fontsize` int(4) NOT NULL default '3',
1283 `font` char(10) NOT NULL default 'TR',
1284 PRIMARY KEY (`tmpl_id`)
1285 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1288 -- Table structure for table `letter`
1291 DROP TABLE IF EXISTS `letter`;
1292 CREATE TABLE `letter` (
1293 `module` varchar(20) NOT NULL default '',
1294 `code` varchar(20) NOT NULL default '',
1295 `name` varchar(100) NOT NULL default '',
1296 `title` varchar(200) NOT NULL default '',
1298 PRIMARY KEY (`module`,`code`)
1299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1302 -- Table structure for table `marc_subfield_structure`
1305 DROP TABLE IF EXISTS `marc_subfield_structure`;
1306 CREATE TABLE `marc_subfield_structure` (
1307 `tagfield` varchar(3) NOT NULL default '',
1308 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1309 `liblibrarian` varchar(255) NOT NULL default '',
1310 `libopac` varchar(255) NOT NULL default '',
1311 `repeatable` tinyint(4) NOT NULL default 0,
1312 `mandatory` tinyint(4) NOT NULL default 0,
1313 `kohafield` varchar(40) default NULL,
1314 `tab` tinyint(1) default NULL,
1315 `authorised_value` varchar(20) default NULL,
1316 `authtypecode` varchar(20) default NULL,
1317 `value_builder` varchar(80) default NULL,
1318 `isurl` tinyint(1) default NULL,
1319 `hidden` tinyint(1) default NULL,
1320 `frameworkcode` varchar(4) NOT NULL default '',
1321 `seealso` varchar(1100) default NULL,
1322 `link` varchar(80) default NULL,
1323 `defaultvalue` text default NULL,
1324 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1325 KEY `kohafield_2` (`kohafield`),
1326 KEY `tab` (`frameworkcode`,`tab`),
1327 KEY `kohafield` (`frameworkcode`,`kohafield`)
1328 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1331 -- Table structure for table `marc_tag_structure`
1334 DROP TABLE IF EXISTS `marc_tag_structure`;
1335 CREATE TABLE `marc_tag_structure` (
1336 `tagfield` varchar(3) NOT NULL default '',
1337 `liblibrarian` varchar(255) NOT NULL default '',
1338 `libopac` varchar(255) NOT NULL default '',
1339 `repeatable` tinyint(4) NOT NULL default 0,
1340 `mandatory` tinyint(4) NOT NULL default 0,
1341 `authorised_value` varchar(10) default NULL,
1342 `frameworkcode` varchar(4) NOT NULL default '',
1343 PRIMARY KEY (`frameworkcode`,`tagfield`)
1344 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1347 -- Table structure for table `marc_matchers`
1350 DROP TABLE IF EXISTS `marc_matchers`;
1351 CREATE TABLE `marc_matchers` (
1352 `matcher_id` int(11) NOT NULL auto_increment,
1353 `code` varchar(10) NOT NULL default '',
1354 `description` varchar(255) NOT NULL default '',
1355 `record_type` varchar(10) NOT NULL default 'biblio',
1356 `threshold` int(11) NOT NULL default 0,
1357 PRIMARY KEY (`matcher_id`),
1358 KEY `code` (`code`),
1359 KEY `record_type` (`record_type`)
1360 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1363 -- Table structure for table `matchpoints`
1365 DROP TABLE IF EXISTS `matchpoints`;
1366 CREATE TABLE `matchpoints` (
1367 `matcher_id` int(11) NOT NULL,
1368 `matchpoint_id` int(11) NOT NULL auto_increment,
1369 `search_index` varchar(30) NOT NULL default '',
1370 `score` int(11) NOT NULL default 0,
1371 PRIMARY KEY (`matchpoint_id`),
1372 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1373 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1374 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1378 -- Table structure for table `matchpoint_components`
1380 DROP TABLE IF EXISTS `matchpoint_components`;
1381 CREATE TABLE `matchpoint_components` (
1382 `matchpoint_id` int(11) NOT NULL,
1383 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1384 sequence int(11) NOT NULL default 0,
1385 tag varchar(3) NOT NULL default '',
1386 subfields varchar(40) NOT NULL default '',
1387 offset int(4) NOT NULL default 0,
1388 length int(4) NOT NULL default 0,
1389 PRIMARY KEY (`matchpoint_component_id`),
1390 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1391 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1392 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1393 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1396 -- Table structure for table `matcher_component_norms`
1398 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1399 CREATE TABLE `matchpoint_component_norms` (
1400 `matchpoint_component_id` int(11) NOT NULL,
1401 `sequence` int(11) NOT NULL default 0,
1402 `norm_routine` varchar(50) NOT NULL default '',
1403 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1404 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1405 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1406 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1409 -- Table structure for table `matcher_matchpoints`
1411 DROP TABLE IF EXISTS `matcher_matchpoints`;
1412 CREATE TABLE `matcher_matchpoints` (
1413 `matcher_id` int(11) NOT NULL,
1414 `matchpoint_id` int(11) NOT NULL,
1415 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1416 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1417 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1418 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1419 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1422 -- Table structure for table `matchchecks`
1424 DROP TABLE IF EXISTS `matchchecks`;
1425 CREATE TABLE `matchchecks` (
1426 `matcher_id` int(11) NOT NULL,
1427 `matchcheck_id` int(11) NOT NULL auto_increment,
1428 `source_matchpoint_id` int(11) NOT NULL,
1429 `target_matchpoint_id` int(11) NOT NULL,
1430 PRIMARY KEY (`matchcheck_id`),
1431 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1432 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1433 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1434 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1435 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1436 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1437 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1440 -- Table structure for table `notifys`
1443 DROP TABLE IF EXISTS `notifys`;
1444 CREATE TABLE `notifys` (
1445 `notify_id` int(11) NOT NULL default 0,
1446 `borrowernumber` int(11) NOT NULL default 0,
1447 `itemnumber` int(11) NOT NULL default 0,
1448 `notify_date` date default NULL,
1449 `notify_send_date` date default NULL,
1450 `notify_level` int(1) NOT NULL default 0,
1451 `method` varchar(20) NOT NULL default ''
1452 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1455 -- Table structure for table `nozebra`
1458 DROP TABLE IF EXISTS `nozebra`;
1459 CREATE TABLE `nozebra` (
1460 `server` varchar(20) NOT NULL,
1461 `indexname` varchar(40) NOT NULL,
1462 `value` varchar(250) NOT NULL,
1463 `biblionumbers` longtext NOT NULL,
1464 KEY `indexname` (`server`,`indexname`),
1465 KEY `value` (`server`,`value`))
1466 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1469 -- Table structure for table `old_issues`
1472 DROP TABLE IF EXISTS `old_issues`;
1473 CREATE TABLE `old_issues` (
1474 `borrowernumber` int(11) default NULL,
1475 `itemnumber` int(11) default NULL,
1476 `date_due` date default NULL,
1477 `branchcode` varchar(10) default NULL,
1478 `issuingbranch` varchar(18) default NULL,
1479 `returndate` date default NULL,
1480 `lastreneweddate` date default NULL,
1481 `return` varchar(4) default NULL,
1482 `renewals` tinyint(4) default NULL,
1483 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1484 `issuedate` date default NULL,
1485 KEY `old_issuesborridx` (`borrowernumber`),
1486 KEY `old_issuesitemidx` (`itemnumber`),
1487 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1488 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1489 ON DELETE SET NULL ON UPDATE SET NULL,
1490 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1491 ON DELETE SET NULL ON UPDATE SET NULL
1492 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1495 -- Table structure for table `old_reserves`
1497 DROP TABLE IF EXISTS `old_reserves`;
1498 CREATE TABLE `old_reserves` (
1499 `borrowernumber` int(11) default NULL,
1500 `reservedate` date default NULL,
1501 `biblionumber` int(11) default NULL,
1502 `constrainttype` varchar(1) default NULL,
1503 `branchcode` varchar(10) default NULL,
1504 `notificationdate` date default NULL,
1505 `reminderdate` date default NULL,
1506 `cancellationdate` date default NULL,
1507 `reservenotes` mediumtext,
1508 `priority` smallint(6) default NULL,
1509 `found` varchar(1) default NULL,
1510 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1511 `itemnumber` int(11) default NULL,
1512 `waitingdate` date default NULL,
1513 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1514 KEY `old_reserves_biblionumber` (`biblionumber`),
1515 KEY `old_reserves_itemnumber` (`itemnumber`),
1516 KEY `old_reserves_branchcode` (`branchcode`),
1517 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1518 ON DELETE SET NULL ON UPDATE SET NULL,
1519 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1520 ON DELETE SET NULL ON UPDATE SET NULL,
1521 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1522 ON DELETE SET NULL ON UPDATE SET NULL
1523 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1526 -- Table structure for table `opac_news`
1529 DROP TABLE IF EXISTS `opac_news`;
1530 CREATE TABLE `opac_news` (
1531 `idnew` int(10) unsigned NOT NULL auto_increment,
1532 `title` varchar(250) NOT NULL default '',
1533 `new` text NOT NULL,
1534 `lang` varchar(25) NOT NULL default '',
1535 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1536 `expirationdate` date default NULL,
1537 `number` int(11) default NULL,
1538 PRIMARY KEY (`idnew`)
1539 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1542 -- Table structure for table `overduerules`
1545 DROP TABLE IF EXISTS `overduerules`;
1546 CREATE TABLE `overduerules` (
1547 `branchcode` varchar(10) NOT NULL default '',
1548 `categorycode` varchar(10) NOT NULL default '',
1549 `delay1` int(4) default 0,
1550 `letter1` varchar(20) default NULL,
1551 `debarred1` varchar(1) default 0,
1552 `delay2` int(4) default 0,
1553 `debarred2` varchar(1) default 0,
1554 `letter2` varchar(20) default NULL,
1555 `delay3` int(4) default 0,
1556 `letter3` varchar(20) default NULL,
1557 `debarred3` int(1) default 0,
1558 PRIMARY KEY (`branchcode`,`categorycode`)
1559 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1562 -- Table structure for table `patroncards`
1565 DROP TABLE IF EXISTS `patroncards`;
1566 CREATE TABLE `patroncards` (
1567 `cardid` int(11) NOT NULL auto_increment,
1568 `batch_id` varchar(10) NOT NULL default '1',
1569 `borrowernumber` int(11) NOT NULL,
1570 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1571 PRIMARY KEY (`cardid`),
1572 KEY `patroncards_ibfk_1` (`borrowernumber`),
1573 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1574 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1577 -- Table structure for table `patronimage`
1580 DROP TABLE IF EXISTS `patronimage`;
1581 CREATE TABLE `patronimage` (
1582 `cardnumber` varchar(16) NOT NULL,
1583 `mimetype` varchar(15) NOT NULL,
1584 `imagefile` mediumblob NOT NULL,
1585 PRIMARY KEY (`cardnumber`),
1586 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1587 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1590 -- Table structure for table `printers`
1593 DROP TABLE IF EXISTS `printers`;
1594 CREATE TABLE `printers` (
1595 `printername` varchar(40) NOT NULL default '',
1596 `printqueue` varchar(20) default NULL,
1597 `printtype` varchar(20) default NULL,
1598 PRIMARY KEY (`printername`)
1599 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1602 -- Table structure for table `printers_profile`
1605 DROP TABLE IF EXISTS `printers_profile`;
1606 CREATE TABLE `printers_profile` (
1607 `prof_id` int(4) NOT NULL auto_increment,
1608 `printername` varchar(40) NOT NULL,
1609 `tmpl_id` int(4) NOT NULL,
1610 `paper_bin` varchar(20) NOT NULL,
1611 `offset_horz` float default NULL,
1612 `offset_vert` float default NULL,
1613 `creep_horz` float default NULL,
1614 `creep_vert` float default NULL,
1615 `unit` char(20) NOT NULL default 'POINT',
1616 PRIMARY KEY (`prof_id`),
1617 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1618 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1619 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1622 -- Table structure for table `repeatable_holidays`
1625 DROP TABLE IF EXISTS `repeatable_holidays`;
1626 CREATE TABLE `repeatable_holidays` (
1627 `id` int(11) NOT NULL auto_increment,
1628 `branchcode` varchar(10) NOT NULL default '',
1629 `weekday` smallint(6) default NULL,
1630 `day` smallint(6) default NULL,
1631 `month` smallint(6) default NULL,
1632 `title` varchar(50) NOT NULL default '',
1633 `description` text NOT NULL,
1635 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1638 -- Table structure for table `reports_dictionary`
1641 DROP TABLE IF EXISTS `reports_dictionary`;
1642 CREATE TABLE reports_dictionary (
1643 `id` int(11) NOT NULL auto_increment,
1644 `name` varchar(255) default NULL,
1646 `date_created` datetime default NULL,
1647 `date_modified` datetime default NULL,
1649 `area` int(11) default NULL,
1651 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1654 -- Table structure for table `reserveconstraints`
1657 DROP TABLE IF EXISTS `reserveconstraints`;
1658 CREATE TABLE `reserveconstraints` (
1659 `borrowernumber` int(11) NOT NULL default 0,
1660 `reservedate` date default NULL,
1661 `biblionumber` int(11) NOT NULL default 0,
1662 `biblioitemnumber` int(11) default NULL,
1663 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1664 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1667 -- Table structure for table `reserves`
1670 DROP TABLE IF EXISTS `reserves`;
1671 CREATE TABLE `reserves` (
1672 `borrowernumber` int(11) NOT NULL default 0,
1673 `reservedate` date default NULL,
1674 `biblionumber` int(11) NOT NULL default 0,
1675 `constrainttype` varchar(1) default NULL,
1676 `branchcode` varchar(10) default NULL,
1677 `notificationdate` date default NULL,
1678 `reminderdate` date default NULL,
1679 `cancellationdate` date default NULL,
1680 `reservenotes` mediumtext,
1681 `priority` smallint(6) default NULL,
1682 `found` varchar(1) default NULL,
1683 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1684 `itemnumber` int(11) default NULL,
1685 `waitingdate` date default NULL,
1686 KEY `borrowernumber` (`borrowernumber`),
1687 KEY `biblionumber` (`biblionumber`),
1688 KEY `itemnumber` (`itemnumber`),
1689 KEY `branchcode` (`branchcode`),
1690 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1691 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1692 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1693 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1694 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1697 -- Table structure for table `reviews`
1700 DROP TABLE IF EXISTS `reviews`;
1701 CREATE TABLE `reviews` (
1702 `reviewid` int(11) NOT NULL auto_increment,
1703 `borrowernumber` int(11) default NULL,
1704 `biblionumber` int(11) default NULL,
1706 `approved` tinyint(4) default NULL,
1707 `datereviewed` datetime default NULL,
1708 PRIMARY KEY (`reviewid`)
1709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1712 -- Table structure for table `roadtype`
1715 DROP TABLE IF EXISTS `roadtype`;
1716 CREATE TABLE `roadtype` (
1717 `roadtypeid` int(11) NOT NULL auto_increment,
1718 `road_type` varchar(100) NOT NULL default '',
1719 PRIMARY KEY (`roadtypeid`)
1720 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1723 -- Table structure for table `saved_sql`
1726 DROP TABLE IF EXISTS `saved_sql`;
1727 CREATE TABLE saved_sql (
1728 `id` int(11) NOT NULL auto_increment,
1729 `borrowernumber` int(11) default NULL,
1730 `date_created` datetime default NULL,
1731 `last_modified` datetime default NULL,
1733 `last_run` datetime default NULL,
1734 `report_name` varchar(255) default NULL,
1735 `type` varchar(255) default NULL,
1738 KEY boridx (`borrowernumber`)
1739 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1743 -- Table structure for `saved_reports`
1746 DROP TABLE IF EXISTS `saved_reports`;
1747 CREATE TABLE saved_reports (
1748 `id` int(11) NOT NULL auto_increment,
1749 `report_id` int(11) default NULL,
1751 `date_run` datetime default NULL,
1753 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1757 -- Table structure for table `serial`
1760 DROP TABLE IF EXISTS `serial`;
1761 CREATE TABLE `serial` (
1762 `serialid` int(11) NOT NULL auto_increment,
1763 `biblionumber` varchar(100) NOT NULL default '',
1764 `subscriptionid` varchar(100) NOT NULL default '',
1765 `serialseq` varchar(100) NOT NULL default '',
1766 `status` tinyint(4) NOT NULL default 0,
1767 `planneddate` date default NULL,
1769 `publisheddate` date default NULL,
1770 `itemnumber` text default NULL,
1771 `claimdate` date default NULL,
1772 `routingnotes` text,
1773 PRIMARY KEY (`serialid`)
1774 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1777 -- Table structure for table `sessions`
1780 DROP TABLE IF EXISTS sessions;
1781 CREATE TABLE sessions (
1782 `id` varchar(32) NOT NULL,
1783 `a_session` text NOT NULL,
1785 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1788 -- Table structure for table `special_holidays`
1791 DROP TABLE IF EXISTS `special_holidays`;
1792 CREATE TABLE `special_holidays` (
1793 `id` int(11) NOT NULL auto_increment,
1794 `branchcode` varchar(10) NOT NULL default '',
1795 `day` smallint(6) NOT NULL default 0,
1796 `month` smallint(6) NOT NULL default 0,
1797 `year` smallint(6) NOT NULL default 0,
1798 `isexception` smallint(1) NOT NULL default 1,
1799 `title` varchar(50) NOT NULL default '',
1800 `description` text NOT NULL,
1802 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1805 -- Table structure for table `statistics`
1808 DROP TABLE IF EXISTS `statistics`;
1809 CREATE TABLE `statistics` (
1810 `datetime` datetime default NULL,
1811 `branch` varchar(10) default NULL,
1812 `proccode` varchar(4) default NULL,
1813 `value` double(16,4) default NULL,
1814 `type` varchar(16) default NULL,
1816 `usercode` varchar(10) default NULL,
1817 `itemnumber` int(11) default NULL,
1818 `itemtype` varchar(10) default NULL,
1819 `borrowernumber` int(11) default NULL,
1820 `associatedborrower` int(11) default NULL,
1821 KEY `timeidx` (`datetime`)
1822 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1825 -- Table structure for table `stopwords`
1828 DROP TABLE IF EXISTS `stopwords`;
1829 CREATE TABLE `stopwords` (
1830 `word` varchar(255) default NULL
1831 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1834 -- Table structure for table `subscription`
1837 DROP TABLE IF EXISTS `subscription`;
1838 CREATE TABLE `subscription` (
1839 `biblionumber` int(11) NOT NULL default 0,
1840 `subscriptionid` int(11) NOT NULL auto_increment,
1841 `librarian` varchar(100) default '',
1842 `startdate` date default NULL,
1843 `aqbooksellerid` int(11) default 0,
1844 `cost` int(11) default 0,
1845 `aqbudgetid` int(11) default 0,
1846 `weeklength` int(11) default 0,
1847 `monthlength` int(11) default 0,
1848 `numberlength` int(11) default 0,
1849 `periodicity` tinyint(4) default 0,
1850 `dow` varchar(100) default '',
1851 `numberingmethod` varchar(100) default '',
1853 `status` varchar(100) NOT NULL default '',
1854 `add1` int(11) default 0,
1855 `every1` int(11) default 0,
1856 `whenmorethan1` int(11) default 0,
1857 `setto1` int(11) default NULL,
1858 `lastvalue1` int(11) default NULL,
1859 `add2` int(11) default 0,
1860 `every2` int(11) default 0,
1861 `whenmorethan2` int(11) default 0,
1862 `setto2` int(11) default NULL,
1863 `lastvalue2` int(11) default NULL,
1864 `add3` int(11) default 0,
1865 `every3` int(11) default 0,
1866 `innerloop1` int(11) default 0,
1867 `innerloop2` int(11) default 0,
1868 `innerloop3` int(11) default 0,
1869 `whenmorethan3` int(11) default 0,
1870 `setto3` int(11) default NULL,
1871 `lastvalue3` int(11) default NULL,
1872 `issuesatonce` tinyint(3) NOT NULL default 1,
1873 `firstacquidate` date default NULL,
1874 `manualhistory` tinyint(1) NOT NULL default 0,
1875 `irregularity` text,
1876 `letter` varchar(20) default NULL,
1877 `numberpattern` tinyint(3) default 0,
1878 `distributedto` text,
1879 `internalnotes` longtext,
1881 `branchcode` varchar(10) NOT NULL default '',
1882 `hemisphere` tinyint(3) default 0,
1883 `lastbranch` varchar(10),
1884 `serialsadditems` tinyint(1) NOT NULL default '0',
1885 PRIMARY KEY (`subscriptionid`)
1886 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1889 -- Table structure for table `subscriptionhistory`
1892 DROP TABLE IF EXISTS `subscriptionhistory`;
1893 CREATE TABLE `subscriptionhistory` (
1894 `biblionumber` int(11) NOT NULL default 0,
1895 `subscriptionid` int(11) NOT NULL default 0,
1896 `histstartdate` date default NULL,
1897 `enddate` date default NULL,
1898 `missinglist` longtext NOT NULL,
1899 `recievedlist` longtext NOT NULL,
1900 `opacnote` varchar(150) NOT NULL default '',
1901 `librariannote` varchar(150) NOT NULL default '',
1902 PRIMARY KEY (`subscriptionid`),
1903 KEY `biblionumber` (`biblionumber`)
1904 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1907 -- Table structure for table `subscriptionroutinglist`
1910 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1911 CREATE TABLE `subscriptionroutinglist` (
1912 `routingid` int(11) NOT NULL auto_increment,
1913 `borrowernumber` int(11) default NULL,
1914 `ranking` int(11) default NULL,
1915 `subscriptionid` int(11) default NULL,
1916 PRIMARY KEY (`routingid`)
1917 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1920 -- Table structure for table `suggestions`
1923 DROP TABLE IF EXISTS `suggestions`;
1924 CREATE TABLE `suggestions` (
1925 `suggestionid` int(8) NOT NULL auto_increment,
1926 `suggestedby` int(11) NOT NULL default 0,
1927 `managedby` int(11) default NULL,
1928 `STATUS` varchar(10) NOT NULL default '',
1930 `author` varchar(80) default NULL,
1931 `title` varchar(80) default NULL,
1932 `copyrightdate` smallint(6) default NULL,
1933 `publishercode` varchar(255) default NULL,
1934 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1935 `volumedesc` varchar(255) default NULL,
1936 `publicationyear` smallint(6) default 0,
1937 `place` varchar(255) default NULL,
1938 `isbn` varchar(30) default NULL,
1939 `mailoverseeing` smallint(1) default 0,
1940 `biblionumber` int(11) default NULL,
1942 PRIMARY KEY (`suggestionid`),
1943 KEY `suggestedby` (`suggestedby`),
1944 KEY `managedby` (`managedby`)
1945 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1948 -- Table structure for table `systempreferences`
1951 DROP TABLE IF EXISTS `systempreferences`;
1952 CREATE TABLE `systempreferences` (
1953 `variable` varchar(50) NOT NULL default '',
1955 `options` mediumtext,
1957 `type` varchar(20) default NULL,
1958 PRIMARY KEY (`variable`)
1959 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1962 -- Table structure for table `tags`
1965 DROP TABLE IF EXISTS `tags`;
1966 CREATE TABLE `tags` (
1967 `entry` varchar(255) NOT NULL default '',
1968 `weight` bigint(20) NOT NULL default 0,
1969 PRIMARY KEY (`entry`)
1970 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1973 -- Table structure for table `tags_all`
1976 DROP TABLE IF EXISTS `tags_all`;
1977 CREATE TABLE `tags_all` (
1978 `tag_id` int(11) NOT NULL auto_increment,
1979 `borrowernumber` int(11) NOT NULL,
1980 `biblionumber` int(11) NOT NULL,
1981 `term` varchar(255) NOT NULL,
1982 `language` int(4) default NULL,
1983 `date_created` datetime NOT NULL,
1984 PRIMARY KEY (`tag_id`),
1985 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1986 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1987 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1988 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1989 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1990 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1991 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1994 -- Table structure for table `tags_approval`
1997 DROP TABLE IF EXISTS `tags_approval`;
1998 CREATE TABLE `tags_approval` (
1999 `term` varchar(255) NOT NULL,
2000 `approved` int(1) NOT NULL default '0',
2001 `date_approved` datetime default NULL,
2002 `approved_by` int(11) default NULL,
2003 `weight_total` int(9) NOT NULL default '1',
2004 PRIMARY KEY (`term`),
2005 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2006 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2007 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2008 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2011 -- Table structure for table `tags_index`
2014 DROP TABLE IF EXISTS `tags_index`;
2015 CREATE TABLE `tags_index` (
2016 `term` varchar(255) NOT NULL,
2017 `biblionumber` int(11) NOT NULL,
2018 `weight` int(9) NOT NULL default '1',
2019 PRIMARY KEY (`term`,`biblionumber`),
2020 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2021 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2022 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2023 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2024 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2028 -- Table structure for table `userflags`
2031 DROP TABLE IF EXISTS `userflags`;
2032 CREATE TABLE `userflags` (
2033 `bit` int(11) NOT NULL default 0,
2034 `flag` varchar(30) default NULL,
2035 `flagdesc` varchar(255) default NULL,
2036 `defaulton` int(11) default NULL,
2038 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2041 -- Table structure for table `virtualshelves`
2044 DROP TABLE IF EXISTS `virtualshelves`;
2045 CREATE TABLE `virtualshelves` (
2046 `shelfnumber` int(11) NOT NULL auto_increment,
2047 `shelfname` varchar(255) default NULL,
2048 `owner` varchar(80) default NULL,
2049 `category` varchar(1) default NULL,
2050 `sortfield` varchar(16) default NULL,
2051 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2052 PRIMARY KEY (`shelfnumber`)
2053 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2056 -- Table structure for table `virtualshelfcontents`
2059 DROP TABLE IF EXISTS `virtualshelfcontents`;
2060 CREATE TABLE `virtualshelfcontents` (
2061 `shelfnumber` int(11) NOT NULL default 0,
2062 `biblionumber` int(11) NOT NULL default 0,
2063 `flags` int(11) default NULL,
2064 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2065 KEY `shelfnumber` (`shelfnumber`),
2066 KEY `biblionumber` (`biblionumber`),
2067 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2068 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2069 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2072 -- Table structure for table `z3950servers`
2075 DROP TABLE IF EXISTS `z3950servers`;
2076 CREATE TABLE `z3950servers` (
2077 `host` varchar(255) default NULL,
2078 `port` int(11) default NULL,
2079 `db` varchar(255) default NULL,
2080 `userid` varchar(255) default NULL,
2081 `password` varchar(255) default NULL,
2083 `id` int(11) NOT NULL auto_increment,
2084 `checked` smallint(6) default NULL,
2085 `rank` int(11) default NULL,
2086 `syntax` varchar(80) default NULL,
2088 `position` enum('primary','secondary','') NOT NULL default 'primary',
2089 `type` enum('zed','opensearch') NOT NULL default 'zed',
2090 `encoding` text default NULL,
2091 `description` text NOT NULL,
2093 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2096 -- Table structure for table `zebraqueue`
2099 DROP TABLE IF EXISTS `zebraqueue`;
2100 CREATE TABLE `zebraqueue` (
2101 `id` int(11) NOT NULL auto_increment,
2102 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2103 `operation` char(20) NOT NULL default '',
2104 `server` char(20) NOT NULL default '',
2105 `done` int(11) NOT NULL default '0',
2106 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2108 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2109 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2111 DROP TABLE IF EXISTS `services_throttle`;
2112 CREATE TABLE `services_throttle` (
2113 `service_type` varchar(10) NOT NULL default '',
2114 `service_count` varchar(45) default NULL,
2115 PRIMARY KEY (`service_type`)
2116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2118 -- http://www.w3.org/International/articles/language-tags/
2121 DROP TABLE IF EXISTS language_subtag_registry;
2122 CREATE TABLE language_subtag_registry (
2124 type varchar(25), -- language-script-region-variant-extension-privateuse
2125 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2127 KEY `subtag` (`subtag`)
2128 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2130 -- TODO: add suppress_scripts
2131 -- this maps three letter codes defined in iso639.2 back to their
2132 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2133 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2134 CREATE TABLE language_rfc4646_to_iso639 (
2135 rfc4646_subtag varchar(25),
2136 iso639_2_code varchar(25),
2137 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2138 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2140 DROP TABLE IF EXISTS language_descriptions;
2141 CREATE TABLE language_descriptions (
2145 description varchar(255),
2147 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2149 -- bi-directional support, keyed by script subcode
2150 DROP TABLE IF EXISTS language_script_bidi;
2151 CREATE TABLE language_script_bidi (
2152 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2153 bidi varchar(3), -- rtl ltr
2154 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2155 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2157 -- TODO: need to map language subtags to script subtags for detection
2158 -- of bidi when script is not specified (like ar, he)
2159 DROP TABLE IF EXISTS language_script_mapping;
2160 CREATE TABLE language_script_mapping (
2161 language_subtag varchar(25),
2162 script_subtag varchar(25),
2163 KEY `language_subtag` (`language_subtag`)
2164 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2166 DROP TABLE IF EXISTS `permissions`;
2167 CREATE TABLE `permissions` (
2168 `module_bit` int(11) NOT NULL DEFAULT 0,
2169 `code` varchar(64) DEFAULT NULL,
2170 `description` varchar(255) DEFAULT NULL,
2171 PRIMARY KEY (`module_bit`, `code`),
2172 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2173 ON DELETE CASCADE ON UPDATE CASCADE
2174 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2176 DROP TABLE IF EXISTS `serialitems`;
2177 CREATE TABLE `serialitems` (
2178 `itemnumber` int(11) NOT NULL,
2179 `serialid` int(11) NOT NULL,
2180 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2181 KEY `serialitems_sfk_1` (`serialid`),
2182 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2185 DROP TABLE IF EXISTS `user_permissions`;
2186 CREATE TABLE `user_permissions` (
2187 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2188 `module_bit` int(11) NOT NULL DEFAULT 0,
2189 `code` varchar(64) DEFAULT NULL,
2190 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2191 ON DELETE CASCADE ON UPDATE CASCADE,
2192 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2193 ON DELETE CASCADE ON UPDATE CASCADE
2194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2197 -- Table structure for table `tmp_holdsqueue`
2200 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2201 CREATE TABLE `tmp_holdsqueue` (
2202 `biblionumber` int(11) default NULL,
2203 `itemnumber` int(11) default NULL,
2204 `barcode` varchar(20) default NULL,
2205 `surname` mediumtext NOT NULL,
2208 `borrowernumber` int(11) NOT NULL,
2209 `cardnumber` varchar(16) default NULL,
2210 `reservedate` date default NULL,
2212 `itemcallnumber` varchar(30) default NULL,
2213 `holdingbranch` varchar(10) default NULL,
2214 `pickbranch` varchar(10) default NULL,
2216 `item_level_request` tinyint(4) NOT NULL default 0
2217 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2220 -- Table structure for table `message_queue`
2223 DROP TABLE IF EXISTS `message_queue`;
2224 CREATE TABLE `message_queue` (
2225 `message_id` int(11) NOT NULL auto_increment,
2226 `borrowernumber` int(11) default NULL,
2229 `message_transport_type` varchar(20) NOT NULL,
2230 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2231 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2232 `to_address` mediumtext,
2233 `from_address` mediumtext,
2234 `content_type` text,
2235 KEY `message_id` (`message_id`),
2236 KEY `borrowernumber` (`borrowernumber`),
2237 KEY `message_transport_type` (`message_transport_type`),
2238 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2239 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2240 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2243 -- Table structure for table `message_transport_types`
2246 DROP TABLE IF EXISTS `message_transport_types`;
2247 CREATE TABLE `message_transport_types` (
2248 `message_transport_type` varchar(20) NOT NULL,
2249 PRIMARY KEY (`message_transport_type`)
2250 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2253 -- Table structure for table `message_attributes`
2256 DROP TABLE IF EXISTS `message_attributes`;
2257 CREATE TABLE `message_attributes` (
2258 `message_attribute_id` int(11) NOT NULL auto_increment,
2259 `message_name` varchar(20) NOT NULL default '',
2260 `takes_days` tinyint(1) NOT NULL default '0',
2261 PRIMARY KEY (`message_attribute_id`),
2262 UNIQUE KEY `message_name` (`message_name`)
2263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2266 -- Table structure for table `message_transports`
2269 DROP TABLE IF EXISTS `message_transports`;
2270 CREATE TABLE `message_transports` (
2271 `message_attribute_id` int(11) NOT NULL,
2272 `message_transport_type` varchar(20) NOT NULL,
2273 `is_digest` tinyint(1) NOT NULL default '0',
2274 `letter_module` varchar(20) NOT NULL default '',
2275 `letter_code` varchar(20) NOT NULL default '',
2276 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2277 KEY `message_transport_type` (`message_transport_type`),
2278 KEY `letter_module` (`letter_module`,`letter_code`),
2279 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2280 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2281 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2282 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2285 -- Table structure for table `borrower_message_preferences`
2288 DROP TABLE IF EXISTS `borrower_message_preferences`;
2289 CREATE TABLE `borrower_message_preferences` (
2290 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2291 `borrowernumber` int(11) NOT NULL default '0',
2292 `message_attribute_id` int(11) default '0',
2293 `days_in_advance` int(11) default '0',
2294 `wants_digest` tinyint(1) NOT NULL default '0',
2295 PRIMARY KEY (`borrower_message_preference_id`),
2296 KEY `borrowernumber` (`borrowernumber`),
2297 KEY `message_attribute_id` (`message_attribute_id`),
2298 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2299 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
2300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2303 -- Table structure for table `borrower_message_transport_preferences`
2306 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2307 CREATE TABLE `borrower_message_transport_preferences` (
2308 `borrower_message_preference_id` int(11) NOT NULL default '0',
2309 `message_transport_type` varchar(20) NOT NULL default '0',
2310 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2311 KEY `message_transport_type` (`message_transport_type`),
2312 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,
2313 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
2314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2317 -- Table structure for table `fieldmapping`
2320 DROP TABLE IF EXISTS `fieldmapping`;
2321 CREATE TABLE `fieldmapping` (
2322 `id` int(11) NOT NULL auto_increment,
2323 `field` varchar(255) NOT NULL,
2324 `frameworkcode` char(4) NOT NULL default '',
2325 `fieldcode` char(3) NOT NULL,
2326 `subfieldcode` char(1) NOT NULL,
2328 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2331 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2332 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2333 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2334 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2335 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2336 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2337 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2338 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;