3 -- Host: localhost Database: koha30test
4 -- ------------------------------------------------------
5 -- Server version 4.1.22
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
11 /*!40101 SET NAMES utf8 */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19 -- Table structure for table `accountlines`
22 DROP TABLE IF EXISTS `accountlines`;
23 CREATE TABLE `accountlines` (
24 `borrowernumber` int(11) NOT NULL default 0,
25 `accountno` smallint(6) NOT NULL default 0,
26 `itemnumber` int(11) default NULL,
27 `date` date default NULL,
28 `amount` decimal(28,6) default NULL,
29 `description` mediumtext,
31 `accounttype` varchar(5) default NULL,
32 `amountoutstanding` decimal(28,6) default NULL,
33 `lastincrement` decimal(28,6) default NULL,
34 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
35 `notify_id` int(11) NOT NULL default 0,
36 `notify_level` int(2) NOT NULL default 0,
37 KEY `acctsborridx` (`borrowernumber`),
38 KEY `timeidx` (`timestamp`),
39 KEY `itemnumber` (`itemnumber`),
40 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
41 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
45 -- Table structure for table `accountoffsets`
48 DROP TABLE IF EXISTS `accountoffsets`;
49 CREATE TABLE `accountoffsets` (
50 `borrowernumber` int(11) NOT NULL default 0,
51 `accountno` smallint(6) NOT NULL default 0,
52 `offsetaccount` smallint(6) NOT NULL default 0,
53 `offsetamount` decimal(28,6) default NULL,
54 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
55 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
56 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
59 -- Table structure for table `action_logs`
62 DROP TABLE IF EXISTS `action_logs`;
63 CREATE TABLE `action_logs` (
64 `action_id` int(11) NOT NULL auto_increment,
65 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
66 `user` int(11) NOT NULL default 0,
69 `object` int(11) default NULL,
71 PRIMARY KEY (`action_id`),
72 KEY (`timestamp`,`user`)
73 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
76 -- Table structure for table `alert`
79 DROP TABLE IF EXISTS `alert`;
80 CREATE TABLE `alert` (
81 `alertid` int(11) NOT NULL auto_increment,
82 `borrowernumber` int(11) NOT NULL default 0,
83 `type` varchar(10) NOT NULL default '',
84 `externalid` varchar(20) NOT NULL default '',
85 PRIMARY KEY (`alertid`),
86 KEY `borrowernumber` (`borrowernumber`),
87 KEY `type` (`type`,`externalid`)
88 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
91 -- Table structure for table `aqbasket`
94 DROP TABLE IF EXISTS `aqbasket`;
95 CREATE TABLE `aqbasket` (
96 `basketno` int(11) NOT NULL auto_increment,
97 `creationdate` date default NULL,
98 `closedate` date default NULL,
99 `booksellerid` int(11) NOT NULL default 1,
100 `authorisedby` varchar(10) default NULL,
101 `booksellerinvoicenumber` mediumtext,
102 PRIMARY KEY (`basketno`),
103 KEY `booksellerid` (`booksellerid`),
104 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
105 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
108 -- Table structure for table `aqbookfund`
111 DROP TABLE IF EXISTS `aqbookfund`;
112 CREATE TABLE `aqbookfund` (
113 `bookfundid` varchar(10) NOT NULL default '',
114 `bookfundname` mediumtext,
115 `bookfundgroup` varchar(5) default NULL,
116 `branchcode` varchar(10) NOT NULL default '',
117 PRIMARY KEY (`bookfundid`,`branchcode`)
118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
121 -- Table structure for table `aqbooksellers`
124 DROP TABLE IF EXISTS `aqbooksellers`;
125 CREATE TABLE `aqbooksellers` (
126 `id` int(11) NOT NULL auto_increment,
127 `name` mediumtext NOT NULL,
128 `address1` mediumtext,
129 `address2` mediumtext,
130 `address3` mediumtext,
131 `address4` mediumtext,
132 `phone` varchar(30) default NULL,
133 `accountnumber` mediumtext,
134 `othersupplier` mediumtext,
135 `currency` varchar(3) NOT NULL default '',
136 `deliverydays` smallint(6) default NULL,
137 `followupdays` smallint(6) default NULL,
138 `followupscancel` smallint(6) default NULL,
139 `specialty` mediumtext,
140 `booksellerfax` mediumtext,
142 `bookselleremail` mediumtext,
143 `booksellerurl` mediumtext,
144 `contact` varchar(100) default NULL,
146 `url` varchar(255) default NULL,
147 `contpos` varchar(100) default NULL,
148 `contphone` varchar(100) default NULL,
149 `contfax` varchar(100) default NULL,
150 `contaltphone` varchar(100) default NULL,
151 `contemail` varchar(100) default NULL,
152 `contnotes` mediumtext,
153 `active` tinyint(4) default NULL,
154 `listprice` varchar(10) default NULL,
155 `invoiceprice` varchar(10) default NULL,
156 `gstreg` tinyint(4) default NULL,
157 `listincgst` tinyint(4) default NULL,
158 `invoiceincgst` tinyint(4) default NULL,
159 `discount` float(6,4) default NULL,
160 `fax` varchar(50) default NULL,
161 `nocalc` int(11) default NULL,
162 `invoicedisc` float(6,4) default NULL,
164 KEY `listprice` (`listprice`),
165 KEY `invoiceprice` (`invoiceprice`),
166 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
167 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
171 -- Table structure for table `aqbudget`
174 DROP TABLE IF EXISTS `aqbudget`;
175 CREATE TABLE `aqbudget` (
176 `bookfundid` varchar(10) NOT NULL default '',
177 `startdate` date NOT NULL default 0,
178 `enddate` date default NULL,
179 `budgetamount` decimal(13,2) default NULL,
180 `aqbudgetid` tinyint(4) NOT NULL auto_increment,
181 `branchcode` varchar(10) default NULL,
182 PRIMARY KEY (`aqbudgetid`)
183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
186 -- Table structure for table `aqorderbreakdown`
189 DROP TABLE IF EXISTS `aqorderbreakdown`;
190 CREATE TABLE `aqorderbreakdown` (
191 `ordernumber` int(11) default NULL,
192 `linenumber` int(11) default NULL,
193 `branchcode` varchar(10) default NULL,
194 `bookfundid` varchar(10) NOT NULL default '',
195 `allocation` smallint(6) default NULL,
196 KEY `ordernumber` (`ordernumber`),
197 KEY `bookfundid` (`bookfundid`),
198 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
199 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
203 -- Table structure for table `aqorderdelivery`
206 DROP TABLE IF EXISTS `aqorderdelivery`;
207 CREATE TABLE `aqorderdelivery` (
208 `ordernumber` date default NULL,
209 `deliverynumber` smallint(6) NOT NULL default 0,
210 `deliverydate` varchar(18) default NULL,
211 `qtydelivered` smallint(6) default NULL,
212 `deliverycomments` mediumtext
213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
216 -- Table structure for table `aqorders`
219 DROP TABLE IF EXISTS `aqorders`;
220 CREATE TABLE `aqorders` (
221 `ordernumber` int(11) NOT NULL auto_increment,
222 `biblionumber` int(11) default NULL,
224 `entrydate` date default NULL,
225 `quantity` smallint(6) default NULL,
226 `currency` varchar(3) default NULL,
227 `listprice` decimal(28,6) default NULL,
228 `totalamount` decimal(28,6) default NULL,
229 `datereceived` date default NULL,
230 `booksellerinvoicenumber` mediumtext,
231 `freight` decimal(28,6) default NULL,
232 `unitprice` decimal(28,6) default NULL,
233 `quantityreceived` smallint(6) default NULL,
234 `cancelledby` varchar(10) default NULL,
235 `datecancellationprinted` date default NULL,
237 `supplierreference` mediumtext,
238 `purchaseordernumber` mediumtext,
239 `subscription` tinyint(1) default NULL,
240 `serialid` varchar(30) default NULL,
241 `basketno` int(11) default NULL,
242 `biblioitemnumber` int(11) default NULL,
243 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
244 `rrp` decimal(13,2) default NULL,
245 `ecost` decimal(13,2) default NULL,
246 `gst` decimal(13,2) default NULL,
247 `budgetdate` date default NULL,
248 `sort1` varchar(80) default NULL,
249 `sort2` varchar(80) default NULL,
250 PRIMARY KEY (`ordernumber`),
251 KEY `basketno` (`basketno`),
252 KEY `biblionumber` (`biblionumber`),
253 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
254 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
255 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
258 -- Table structure for table `auth_header`
261 DROP TABLE IF EXISTS `auth_header`;
262 CREATE TABLE `auth_header` (
263 `authid` bigint(20) unsigned NOT NULL auto_increment,
264 `authtypecode` varchar(10) NOT NULL default '',
265 `datecreated` date default NULL,
266 `datemodified` date default NULL,
267 `origincode` varchar(20) default NULL,
268 `authtrees` mediumtext,
270 `linkid` bigint(20) default NULL,
271 `marcxml` longtext NOT NULL,
272 PRIMARY KEY (`authid`),
273 KEY `origincode` (`origincode`)
274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
277 -- Table structure for table `auth_subfield_structure`
280 DROP TABLE IF EXISTS `auth_subfield_structure`;
281 CREATE TABLE `auth_subfield_structure` (
282 `authtypecode` varchar(10) NOT NULL default '',
283 `tagfield` varchar(3) NOT NULL default '',
284 `tagsubfield` varchar(1) NOT NULL default '',
285 `liblibrarian` varchar(255) NOT NULL default '',
286 `libopac` varchar(255) NOT NULL default '',
287 `repeatable` tinyint(4) NOT NULL default 0,
288 `mandatory` tinyint(4) NOT NULL default 0,
289 `tab` tinyint(1) default NULL,
290 `authorised_value` varchar(10) default NULL,
291 `value_builder` varchar(80) default NULL,
292 `seealso` varchar(255) default NULL,
293 `isurl` tinyint(1) default NULL,
294 `hidden` tinyint(3) NOT NULL default 0,
295 `linkid` tinyint(1) NOT NULL default 0,
296 `kohafield` varchar(45) NULL default '',
297 `frameworkcode` varchar(8) NOT NULL default '',
298 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
299 KEY `tab` (`authtypecode`,`tab`)
300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
303 -- Table structure for table `auth_tag_structure`
306 DROP TABLE IF EXISTS `auth_tag_structure`;
307 CREATE TABLE `auth_tag_structure` (
308 `authtypecode` varchar(10) NOT NULL default '',
309 `tagfield` varchar(3) NOT NULL default '',
310 `liblibrarian` varchar(255) NOT NULL default '',
311 `libopac` varchar(255) NOT NULL default '',
312 `repeatable` tinyint(4) NOT NULL default 0,
313 `mandatory` tinyint(4) NOT NULL default 0,
314 `authorised_value` varchar(10) default NULL,
315 PRIMARY KEY (`authtypecode`,`tagfield`),
316 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
320 -- Table structure for table `auth_types`
323 DROP TABLE IF EXISTS `auth_types`;
324 CREATE TABLE `auth_types` (
325 `authtypecode` varchar(10) NOT NULL default '',
326 `authtypetext` varchar(255) NOT NULL default '',
327 `auth_tag_to_report` varchar(3) NOT NULL default '',
328 `summary` mediumtext NOT NULL,
329 PRIMARY KEY (`authtypecode`)
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
333 -- Table structure for table `authorised_values`
336 DROP TABLE IF EXISTS `authorised_values`;
337 CREATE TABLE `authorised_values` (
338 `id` int(11) NOT NULL auto_increment,
339 `category` varchar(10) NOT NULL default '',
340 `authorised_value` varchar(80) NOT NULL default '',
341 `lib` varchar(80) default NULL,
342 `imageurl` varchar(200) default NULL,
344 KEY `name` (`category`)
345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
348 -- Table structure for table `biblio`
351 DROP TABLE IF EXISTS `biblio`;
352 CREATE TABLE `biblio` (
353 `biblionumber` int(11) NOT NULL auto_increment,
354 `frameworkcode` varchar(4) NOT NULL default '',
357 `unititle` mediumtext,
359 `serial` tinyint(1) default NULL,
360 `seriestitle` mediumtext,
361 `copyrightdate` smallint(6) default NULL,
362 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
363 `datecreated` DATE NOT NULL,
364 `abstract` mediumtext,
365 PRIMARY KEY (`biblionumber`),
366 KEY `blbnoidx` (`biblionumber`)
367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
370 -- Table structure for table `biblio_framework`
373 DROP TABLE IF EXISTS `biblio_framework`;
374 CREATE TABLE `biblio_framework` (
375 `frameworkcode` varchar(4) NOT NULL default '',
376 `frameworktext` varchar(255) NOT NULL default '',
377 PRIMARY KEY (`frameworkcode`)
378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
381 -- Table structure for table `biblioitems`
384 DROP TABLE IF EXISTS `biblioitems`;
385 CREATE TABLE `biblioitems` (
386 `biblioitemnumber` int(11) NOT NULL auto_increment,
387 `biblionumber` int(11) NOT NULL default 0,
390 `itemtype` varchar(10) default NULL,
391 `isbn` varchar(14) default NULL,
392 `issn` varchar(9) default NULL,
393 `publicationyear` text,
394 `publishercode` varchar(255) default NULL,
395 `volumedate` date default NULL,
397 `collectiontitle` mediumtext default NULL,
398 `collectionissn` text default NULL,
399 `collectionvolume` mediumtext default NULL,
400 `editionstatement` text default NULL,
401 `editionresponsibility` text default NULL,
402 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
403 `illus` varchar(255) default NULL,
404 `pages` varchar(255) default NULL,
406 `size` varchar(255) default NULL,
407 `place` varchar(255) default NULL,
408 `lccn` varchar(25) default NULL,
410 `url` varchar(255) default NULL,
411 `cn_source` varchar(10) default NULL,
412 `cn_class` varchar(30) default NULL,
413 `cn_item` varchar(10) default NULL,
414 `cn_suffix` varchar(10) default NULL,
415 `cn_sort` varchar(30) default NULL,
416 `totalissues` int(10),
417 `marcxml` longtext NOT NULL,
418 PRIMARY KEY (`biblioitemnumber`),
419 KEY `bibinoidx` (`biblioitemnumber`),
420 KEY `bibnoidx` (`biblionumber`),
422 KEY `publishercode` (`publishercode`),
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(14) 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(14) 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 `firstremind` int(11) default NULL,
1109 `chargeperiod` int(11) default NULL,
1110 `accountsent` int(11) default NULL,
1111 `chargename` varchar(100) default NULL,
1112 `maxissueqty` int(4) default NULL,
1113 `issuelength` int(4) default NULL,
1114 `branchcode` varchar(10) NOT NULL default '',
1115 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1116 KEY `categorycode` (`categorycode`),
1117 KEY `itemtype` (`itemtype`)
1118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1121 -- Table structure for table `items`
1124 DROP TABLE IF EXISTS `items`;
1125 CREATE TABLE `items` (
1126 `itemnumber` int(11) NOT NULL auto_increment,
1127 `biblionumber` int(11) NOT NULL default 0,
1128 `biblioitemnumber` int(11) NOT NULL default 0,
1129 `barcode` varchar(20) default NULL,
1130 `dateaccessioned` date default NULL,
1131 `booksellerid` mediumtext default NULL,
1132 `homebranch` varchar(10) default NULL,
1133 `price` decimal(8,2) default NULL,
1134 `replacementprice` decimal(8,2) default NULL,
1135 `replacementpricedate` date default NULL,
1136 `datelastborrowed` date default NULL,
1137 `datelastseen` date default NULL,
1138 `stack` tinyint(1) default NULL,
1139 `notforloan` tinyint(1) NOT NULL default 0,
1140 `damaged` tinyint(1) NOT NULL default 0,
1141 `itemlost` tinyint(1) NOT NULL default 0,
1142 `wthdrawn` tinyint(1) NOT NULL default 0,
1143 `itemcallnumber` varchar(30) default NULL,
1144 `issues` smallint(6) default NULL,
1145 `renewals` smallint(6) default NULL,
1146 `reserves` smallint(6) default NULL,
1147 `restricted` tinyint(1) default NULL,
1148 `itemnotes` mediumtext,
1149 `holdingbranch` varchar(10) default NULL,
1150 `paidfor` mediumtext,
1151 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1152 `location` varchar(80) default NULL,
1153 `onloan` date default NULL,
1154 `cn_source` varchar(10) default NULL,
1155 `cn_sort` varchar(30) default NULL,
1156 `ccode` varchar(10) default NULL,
1157 `materials` varchar(10) default NULL,
1158 `uri` varchar(255) default NULL,
1159 `itype` varchar(10) default NULL,
1160 `more_subfields_xml` longtext default NULL,
1161 `enumchron` varchar(80) default NULL,
1162 `copynumber` varchar(32) default NULL,
1163 PRIMARY KEY (`itemnumber`),
1164 UNIQUE KEY `itembarcodeidx` (`barcode`),
1165 KEY `itembinoidx` (`biblioitemnumber`),
1166 KEY `itembibnoidx` (`biblionumber`),
1167 KEY `homebranch` (`homebranch`),
1168 KEY `holdingbranch` (`holdingbranch`),
1169 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1170 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1171 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1172 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1175 -- Table structure for table `itemtypes`
1178 DROP TABLE IF EXISTS `itemtypes`;
1179 CREATE TABLE `itemtypes` (
1180 `itemtype` varchar(10) NOT NULL default '',
1181 `description` mediumtext,
1182 `renewalsallowed` smallint(6) default NULL,
1183 `rentalcharge` double(16,4) default NULL,
1184 `notforloan` smallint(6) default NULL,
1185 `imageurl` varchar(200) default NULL,
1187 PRIMARY KEY (`itemtype`),
1188 UNIQUE KEY `itemtype` (`itemtype`)
1189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1192 -- Table structure for table `labels`
1195 DROP TABLE IF EXISTS `labels`;
1196 CREATE TABLE `labels` (
1197 `labelid` int(11) NOT NULL auto_increment,
1198 `batch_id` varchar(10) NOT NULL default 1,
1199 `itemnumber` varchar(100) NOT NULL default '',
1200 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1201 PRIMARY KEY (`labelid`)
1202 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1205 -- Table structure for table `labels_conf`
1208 DROP TABLE IF EXISTS `labels_conf`;
1209 CREATE TABLE `labels_conf` (
1210 `id` int(4) NOT NULL auto_increment,
1211 `barcodetype` char(100) default '',
1212 `title` int(1) default '0',
1213 `subtitle` int(1) default '0',
1214 `itemtype` int(1) default '0',
1215 `barcode` int(1) default '0',
1216 `dewey` int(1) default '0',
1217 `classification` int(1) default NULL,
1218 `subclass` int(1) default '0',
1219 `itemcallnumber` int(1) default '0',
1220 `author` int(1) default '0',
1221 `issn` int(1) default '0',
1222 `isbn` int(1) default '0',
1223 `startlabel` int(2) NOT NULL default '1',
1224 `printingtype` char(32) default 'BAR',
1225 `formatstring` varchar(64) default NULL,
1226 `layoutname` char(20) NOT NULL default 'TEST',
1227 `guidebox` int(1) default '0',
1228 `active` tinyint(1) default '1',
1229 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1230 `ccode` char(4) collate utf8_unicode_ci default NULL,
1231 `callnum_split` int(1) default NULL,
1232 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1234 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1237 -- Table structure for table `labels_profile`
1240 DROP TABLE IF EXISTS `labels_profile`;
1241 CREATE TABLE `labels_profile` (
1242 `tmpl_id` int(4) NOT NULL,
1243 `prof_id` int(4) NOT NULL,
1244 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1245 UNIQUE KEY `prof_id` (`prof_id`)
1246 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1249 -- Table structure for table `labels_templates`
1252 DROP TABLE IF EXISTS `labels_templates`;
1253 CREATE TABLE `labels_templates` (
1254 `tmpl_id` int(4) NOT NULL auto_increment,
1255 `tmpl_code` char(100) default '',
1256 `tmpl_desc` char(100) default '',
1257 `page_width` float default '0',
1258 `page_height` float default '0',
1259 `label_width` float default '0',
1260 `label_height` float default '0',
1261 `topmargin` float default '0',
1262 `leftmargin` float default '0',
1263 `cols` int(2) default '0',
1264 `rows` int(2) default '0',
1265 `colgap` float default '0',
1266 `rowgap` float default '0',
1267 `active` int(1) default NULL,
1268 `units` char(20) default 'PX',
1269 `fontsize` int(4) NOT NULL default '3',
1270 `font` char(10) NOT NULL default 'TR',
1271 PRIMARY KEY (`tmpl_id`)
1272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1275 -- Table structure for table `letter`
1278 DROP TABLE IF EXISTS `letter`;
1279 CREATE TABLE `letter` (
1280 `module` varchar(20) NOT NULL default '',
1281 `code` varchar(20) NOT NULL default '',
1282 `name` varchar(100) NOT NULL default '',
1283 `title` varchar(200) NOT NULL default '',
1285 PRIMARY KEY (`module`,`code`)
1286 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1289 -- Table structure for table `marc_subfield_structure`
1292 DROP TABLE IF EXISTS `marc_subfield_structure`;
1293 CREATE TABLE `marc_subfield_structure` (
1294 `tagfield` varchar(3) NOT NULL default '',
1295 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1296 `liblibrarian` varchar(255) NOT NULL default '',
1297 `libopac` varchar(255) NOT NULL default '',
1298 `repeatable` tinyint(4) NOT NULL default 0,
1299 `mandatory` tinyint(4) NOT NULL default 0,
1300 `kohafield` varchar(40) default NULL,
1301 `tab` tinyint(1) default NULL,
1302 `authorised_value` varchar(20) default NULL,
1303 `authtypecode` varchar(20) default NULL,
1304 `value_builder` varchar(80) default NULL,
1305 `isurl` tinyint(1) default NULL,
1306 `hidden` tinyint(1) default NULL,
1307 `frameworkcode` varchar(4) NOT NULL default '',
1308 `seealso` varchar(1100) default NULL,
1309 `link` varchar(80) default NULL,
1310 `defaultvalue` text default NULL,
1311 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1312 KEY `kohafield_2` (`kohafield`),
1313 KEY `tab` (`frameworkcode`,`tab`),
1314 KEY `kohafield` (`frameworkcode`,`kohafield`)
1315 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1318 -- Table structure for table `marc_tag_structure`
1321 DROP TABLE IF EXISTS `marc_tag_structure`;
1322 CREATE TABLE `marc_tag_structure` (
1323 `tagfield` varchar(3) NOT NULL default '',
1324 `liblibrarian` varchar(255) NOT NULL default '',
1325 `libopac` varchar(255) NOT NULL default '',
1326 `repeatable` tinyint(4) NOT NULL default 0,
1327 `mandatory` tinyint(4) NOT NULL default 0,
1328 `authorised_value` varchar(10) default NULL,
1329 `frameworkcode` varchar(4) NOT NULL default '',
1330 PRIMARY KEY (`frameworkcode`,`tagfield`)
1331 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1334 -- Table structure for table `marc_matchers`
1337 DROP TABLE IF EXISTS `marc_matchers`;
1338 CREATE TABLE `marc_matchers` (
1339 `matcher_id` int(11) NOT NULL auto_increment,
1340 `code` varchar(10) NOT NULL default '',
1341 `description` varchar(255) NOT NULL default '',
1342 `record_type` varchar(10) NOT NULL default 'biblio',
1343 `threshold` int(11) NOT NULL default 0,
1344 PRIMARY KEY (`matcher_id`),
1345 KEY `code` (`code`),
1346 KEY `record_type` (`record_type`)
1347 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1350 -- Table structure for table `matchpoints`
1352 DROP TABLE IF EXISTS `matchpoints`;
1353 CREATE TABLE `matchpoints` (
1354 `matcher_id` int(11) NOT NULL,
1355 `matchpoint_id` int(11) NOT NULL auto_increment,
1356 `search_index` varchar(30) NOT NULL default '',
1357 `score` int(11) NOT NULL default 0,
1358 PRIMARY KEY (`matchpoint_id`),
1359 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1360 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1361 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1365 -- Table structure for table `matchpoint_components`
1367 DROP TABLE IF EXISTS `matchpoint_components`;
1368 CREATE TABLE `matchpoint_components` (
1369 `matchpoint_id` int(11) NOT NULL,
1370 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1371 sequence int(11) NOT NULL default 0,
1372 tag varchar(3) NOT NULL default '',
1373 subfields varchar(40) NOT NULL default '',
1374 offset int(4) NOT NULL default 0,
1375 length int(4) NOT NULL default 0,
1376 PRIMARY KEY (`matchpoint_component_id`),
1377 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1378 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1379 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1380 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1383 -- Table structure for table `matcher_component_norms`
1385 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1386 CREATE TABLE `matchpoint_component_norms` (
1387 `matchpoint_component_id` int(11) NOT NULL,
1388 `sequence` int(11) NOT NULL default 0,
1389 `norm_routine` varchar(50) NOT NULL default '',
1390 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1391 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1392 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1393 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1396 -- Table structure for table `matcher_matchpoints`
1398 DROP TABLE IF EXISTS `matcher_matchpoints`;
1399 CREATE TABLE `matcher_matchpoints` (
1400 `matcher_id` int(11) NOT NULL,
1401 `matchpoint_id` int(11) NOT NULL,
1402 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1403 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1404 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1405 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1406 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1409 -- Table structure for table `matchchecks`
1411 DROP TABLE IF EXISTS `matchchecks`;
1412 CREATE TABLE `matchchecks` (
1413 `matcher_id` int(11) NOT NULL,
1414 `matchcheck_id` int(11) NOT NULL auto_increment,
1415 `source_matchpoint_id` int(11) NOT NULL,
1416 `target_matchpoint_id` int(11) NOT NULL,
1417 PRIMARY KEY (`matchcheck_id`),
1418 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1419 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1420 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1421 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1422 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1423 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1424 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1427 -- Table structure for table `notifys`
1430 DROP TABLE IF EXISTS `notifys`;
1431 CREATE TABLE `notifys` (
1432 `notify_id` int(11) NOT NULL default 0,
1433 `borrowernumber` int(11) NOT NULL default 0,
1434 `itemnumber` int(11) NOT NULL default 0,
1435 `notify_date` date default NULL,
1436 `notify_send_date` date default NULL,
1437 `notify_level` int(1) NOT NULL default 0,
1438 `method` varchar(20) NOT NULL default ''
1439 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1442 -- Table structure for table `nozebra`
1445 DROP TABLE IF EXISTS `nozebra`;
1446 CREATE TABLE `nozebra` (
1447 `server` varchar(20) NOT NULL,
1448 `indexname` varchar(40) NOT NULL,
1449 `value` varchar(250) NOT NULL,
1450 `biblionumbers` longtext NOT NULL,
1451 KEY `indexname` (`server`,`indexname`),
1452 KEY `value` (`server`,`value`))
1453 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1456 -- Table structure for table `old_issues`
1459 DROP TABLE IF EXISTS `old_issues`;
1460 CREATE TABLE `old_issues` (
1461 `borrowernumber` int(11) default NULL,
1462 `itemnumber` int(11) default NULL,
1463 `date_due` date default NULL,
1464 `branchcode` varchar(10) default NULL,
1465 `issuingbranch` varchar(18) default NULL,
1466 `returndate` date default NULL,
1467 `lastreneweddate` date default NULL,
1468 `return` varchar(4) default NULL,
1469 `renewals` tinyint(4) default NULL,
1470 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1471 `issuedate` date default NULL,
1472 KEY `old_issuesborridx` (`borrowernumber`),
1473 KEY `old_issuesitemidx` (`itemnumber`),
1474 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1475 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1476 ON DELETE SET NULL ON UPDATE SET NULL,
1477 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1478 ON DELETE SET NULL ON UPDATE SET NULL
1479 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1482 -- Table structure for table `old_reserves`
1484 DROP TABLE IF EXISTS `old_reserves`;
1485 CREATE TABLE `old_reserves` (
1486 `borrowernumber` int(11) default NULL,
1487 `reservedate` date default NULL,
1488 `biblionumber` int(11) default NULL,
1489 `constrainttype` varchar(1) default NULL,
1490 `branchcode` varchar(10) default NULL,
1491 `notificationdate` date default NULL,
1492 `reminderdate` date default NULL,
1493 `cancellationdate` date default NULL,
1494 `reservenotes` mediumtext,
1495 `priority` smallint(6) default NULL,
1496 `found` varchar(1) default NULL,
1497 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1498 `itemnumber` int(11) default NULL,
1499 `waitingdate` date default NULL,
1500 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1501 KEY `old_reserves_biblionumber` (`biblionumber`),
1502 KEY `old_reserves_itemnumber` (`itemnumber`),
1503 KEY `old_reserves_branchcode` (`branchcode`),
1504 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1505 ON DELETE SET NULL ON UPDATE SET NULL,
1506 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1507 ON DELETE SET NULL ON UPDATE SET NULL,
1508 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1509 ON DELETE SET NULL ON UPDATE SET NULL
1510 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1513 -- Table structure for table `opac_news`
1516 DROP TABLE IF EXISTS `opac_news`;
1517 CREATE TABLE `opac_news` (
1518 `idnew` int(10) unsigned NOT NULL auto_increment,
1519 `title` varchar(250) NOT NULL default '',
1520 `new` text NOT NULL,
1521 `lang` varchar(25) NOT NULL default '',
1522 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1523 `expirationdate` date default NULL,
1524 `number` int(11) default NULL,
1525 PRIMARY KEY (`idnew`)
1526 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1529 -- Table structure for table `overduerules`
1532 DROP TABLE IF EXISTS `overduerules`;
1533 CREATE TABLE `overduerules` (
1534 `branchcode` varchar(10) NOT NULL default '',
1535 `categorycode` varchar(10) NOT NULL default '',
1536 `delay1` int(4) default 0,
1537 `letter1` varchar(20) default NULL,
1538 `debarred1` varchar(1) default 0,
1539 `delay2` int(4) default 0,
1540 `debarred2` varchar(1) default 0,
1541 `letter2` varchar(20) default NULL,
1542 `delay3` int(4) default 0,
1543 `letter3` varchar(20) default NULL,
1544 `debarred3` int(1) default 0,
1545 PRIMARY KEY (`branchcode`,`categorycode`)
1546 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1549 -- Table structure for table `patroncards`
1552 DROP TABLE IF EXISTS `patroncards`;
1553 CREATE TABLE `patroncards` (
1554 `cardid` int(11) NOT NULL auto_increment,
1555 `batch_id` varchar(10) NOT NULL default '1',
1556 `borrowernumber` int(11) NOT NULL,
1557 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1558 PRIMARY KEY (`cardid`),
1559 KEY `patroncards_ibfk_1` (`borrowernumber`),
1560 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1561 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1564 -- Table structure for table `patronimage`
1567 DROP TABLE IF EXISTS `patronimage`;
1568 CREATE TABLE `patronimage` (
1569 `cardnumber` varchar(16) NOT NULL,
1570 `mimetype` varchar(15) NOT NULL,
1571 `imagefile` mediumblob NOT NULL,
1572 PRIMARY KEY (`cardnumber`),
1573 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1574 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1577 -- Table structure for table `printers`
1580 DROP TABLE IF EXISTS `printers`;
1581 CREATE TABLE `printers` (
1582 `printername` varchar(40) NOT NULL default '',
1583 `printqueue` varchar(20) default NULL,
1584 `printtype` varchar(20) default NULL,
1585 PRIMARY KEY (`printername`)
1586 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1589 -- Table structure for table `printers_profile`
1592 DROP TABLE IF EXISTS `printers_profile`;
1593 CREATE TABLE `printers_profile` (
1594 `prof_id` int(4) NOT NULL auto_increment,
1595 `printername` varchar(40) NOT NULL,
1596 `tmpl_id` int(4) NOT NULL,
1597 `paper_bin` varchar(20) NOT NULL,
1598 `offset_horz` float default NULL,
1599 `offset_vert` float default NULL,
1600 `creep_horz` float default NULL,
1601 `creep_vert` float default NULL,
1602 `unit` char(20) NOT NULL default 'POINT',
1603 PRIMARY KEY (`prof_id`),
1604 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1605 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1606 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1609 -- Table structure for table `repeatable_holidays`
1612 DROP TABLE IF EXISTS `repeatable_holidays`;
1613 CREATE TABLE `repeatable_holidays` (
1614 `id` int(11) NOT NULL auto_increment,
1615 `branchcode` varchar(10) NOT NULL default '',
1616 `weekday` smallint(6) default NULL,
1617 `day` smallint(6) default NULL,
1618 `month` smallint(6) default NULL,
1619 `title` varchar(50) NOT NULL default '',
1620 `description` text NOT NULL,
1622 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1625 -- Table structure for table `reports_dictionary`
1628 DROP TABLE IF EXISTS `reports_dictionary`;
1629 CREATE TABLE reports_dictionary (
1630 `id` int(11) NOT NULL auto_increment,
1631 `name` varchar(255) default NULL,
1633 `date_created` datetime default NULL,
1634 `date_modified` datetime default NULL,
1636 `area` int(11) default NULL,
1638 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1641 -- Table structure for table `reserveconstraints`
1644 DROP TABLE IF EXISTS `reserveconstraints`;
1645 CREATE TABLE `reserveconstraints` (
1646 `borrowernumber` int(11) NOT NULL default 0,
1647 `reservedate` date default NULL,
1648 `biblionumber` int(11) NOT NULL default 0,
1649 `biblioitemnumber` int(11) default NULL,
1650 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1651 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1654 -- Table structure for table `reserves`
1657 DROP TABLE IF EXISTS `reserves`;
1658 CREATE TABLE `reserves` (
1659 `borrowernumber` int(11) NOT NULL default 0,
1660 `reservedate` date default NULL,
1661 `biblionumber` int(11) NOT NULL default 0,
1662 `constrainttype` varchar(1) default NULL,
1663 `branchcode` varchar(10) default NULL,
1664 `notificationdate` date default NULL,
1665 `reminderdate` date default NULL,
1666 `cancellationdate` date default NULL,
1667 `reservenotes` mediumtext,
1668 `priority` smallint(6) default NULL,
1669 `found` varchar(1) default NULL,
1670 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1671 `itemnumber` int(11) default NULL,
1672 `waitingdate` date default NULL,
1673 KEY `borrowernumber` (`borrowernumber`),
1674 KEY `biblionumber` (`biblionumber`),
1675 KEY `itemnumber` (`itemnumber`),
1676 KEY `branchcode` (`branchcode`),
1677 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1678 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1679 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1680 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1681 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1684 -- Table structure for table `reviews`
1687 DROP TABLE IF EXISTS `reviews`;
1688 CREATE TABLE `reviews` (
1689 `reviewid` int(11) NOT NULL auto_increment,
1690 `borrowernumber` int(11) default NULL,
1691 `biblionumber` int(11) default NULL,
1693 `approved` tinyint(4) default NULL,
1694 `datereviewed` datetime default NULL,
1695 PRIMARY KEY (`reviewid`)
1696 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1699 -- Table structure for table `roadtype`
1702 DROP TABLE IF EXISTS `roadtype`;
1703 CREATE TABLE `roadtype` (
1704 `roadtypeid` int(11) NOT NULL auto_increment,
1705 `road_type` varchar(100) NOT NULL default '',
1706 PRIMARY KEY (`roadtypeid`)
1707 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1710 -- Table structure for table `saved_sql`
1713 DROP TABLE IF EXISTS `saved_sql`;
1714 CREATE TABLE saved_sql (
1715 `id` int(11) NOT NULL auto_increment,
1716 `borrowernumber` int(11) default NULL,
1717 `date_created` datetime default NULL,
1718 `last_modified` datetime default NULL,
1720 `last_run` datetime default NULL,
1721 `report_name` varchar(255) default NULL,
1722 `type` varchar(255) default NULL,
1725 KEY boridx (`borrowernumber`)
1726 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1730 -- Table structure for `saved_reports`
1733 DROP TABLE IF EXISTS `saved_reports`;
1734 CREATE TABLE saved_reports (
1735 `id` int(11) NOT NULL auto_increment,
1736 `report_id` int(11) default NULL,
1738 `date_run` datetime default NULL,
1740 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1744 -- Table structure for table `serial`
1747 DROP TABLE IF EXISTS `serial`;
1748 CREATE TABLE `serial` (
1749 `serialid` int(11) NOT NULL auto_increment,
1750 `biblionumber` varchar(100) NOT NULL default '',
1751 `subscriptionid` varchar(100) NOT NULL default '',
1752 `serialseq` varchar(100) NOT NULL default '',
1753 `status` tinyint(4) NOT NULL default 0,
1754 `planneddate` date default NULL,
1756 `publisheddate` date default NULL,
1757 `itemnumber` text default NULL,
1758 `claimdate` date default NULL,
1759 `routingnotes` text,
1760 PRIMARY KEY (`serialid`)
1761 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1764 -- Table structure for table `sessions`
1767 DROP TABLE IF EXISTS sessions;
1768 CREATE TABLE sessions (
1769 `id` varchar(32) NOT NULL,
1770 `a_session` text NOT NULL,
1772 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1775 -- Table structure for table `special_holidays`
1778 DROP TABLE IF EXISTS `special_holidays`;
1779 CREATE TABLE `special_holidays` (
1780 `id` int(11) NOT NULL auto_increment,
1781 `branchcode` varchar(10) NOT NULL default '',
1782 `day` smallint(6) NOT NULL default 0,
1783 `month` smallint(6) NOT NULL default 0,
1784 `year` smallint(6) NOT NULL default 0,
1785 `isexception` smallint(1) NOT NULL default 1,
1786 `title` varchar(50) NOT NULL default '',
1787 `description` text NOT NULL,
1789 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1792 -- Table structure for table `statistics`
1795 DROP TABLE IF EXISTS `statistics`;
1796 CREATE TABLE `statistics` (
1797 `datetime` datetime default NULL,
1798 `branch` varchar(10) default NULL,
1799 `proccode` varchar(4) default NULL,
1800 `value` double(16,4) default NULL,
1801 `type` varchar(16) default NULL,
1803 `usercode` varchar(10) default NULL,
1804 `itemnumber` int(11) default NULL,
1805 `itemtype` varchar(10) default NULL,
1806 `borrowernumber` int(11) default NULL,
1807 `associatedborrower` int(11) default NULL,
1808 KEY `timeidx` (`datetime`)
1809 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1812 -- Table structure for table `stopwords`
1815 DROP TABLE IF EXISTS `stopwords`;
1816 CREATE TABLE `stopwords` (
1817 `word` varchar(255) default NULL
1818 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1821 -- Table structure for table `subscription`
1824 DROP TABLE IF EXISTS `subscription`;
1825 CREATE TABLE `subscription` (
1826 `biblionumber` int(11) NOT NULL default 0,
1827 `subscriptionid` int(11) NOT NULL auto_increment,
1828 `librarian` varchar(100) default '',
1829 `startdate` date default NULL,
1830 `aqbooksellerid` int(11) default 0,
1831 `cost` int(11) default 0,
1832 `aqbudgetid` int(11) default 0,
1833 `weeklength` int(11) default 0,
1834 `monthlength` int(11) default 0,
1835 `numberlength` int(11) default 0,
1836 `periodicity` tinyint(4) default 0,
1837 `dow` varchar(100) default '',
1838 `numberingmethod` varchar(100) default '',
1840 `status` varchar(100) NOT NULL default '',
1841 `add1` int(11) default 0,
1842 `every1` int(11) default 0,
1843 `whenmorethan1` int(11) default 0,
1844 `setto1` int(11) default NULL,
1845 `lastvalue1` int(11) default NULL,
1846 `add2` int(11) default 0,
1847 `every2` int(11) default 0,
1848 `whenmorethan2` int(11) default 0,
1849 `setto2` int(11) default NULL,
1850 `lastvalue2` int(11) default NULL,
1851 `add3` int(11) default 0,
1852 `every3` int(11) default 0,
1853 `innerloop1` int(11) default 0,
1854 `innerloop2` int(11) default 0,
1855 `innerloop3` int(11) default 0,
1856 `whenmorethan3` int(11) default 0,
1857 `setto3` int(11) default NULL,
1858 `lastvalue3` int(11) default NULL,
1859 `issuesatonce` tinyint(3) NOT NULL default 1,
1860 `firstacquidate` date default NULL,
1861 `manualhistory` tinyint(1) NOT NULL default 0,
1862 `irregularity` text,
1863 `letter` varchar(20) default NULL,
1864 `numberpattern` tinyint(3) default 0,
1865 `distributedto` text,
1866 `internalnotes` longtext,
1868 `branchcode` varchar(10) NOT NULL default '',
1869 `hemisphere` tinyint(3) default 0,
1870 `lastbranch` varchar(10),
1871 `serialsadditems` tinyint(1) NOT NULL default '0',
1872 PRIMARY KEY (`subscriptionid`)
1873 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1876 -- Table structure for table `subscriptionhistory`
1879 DROP TABLE IF EXISTS `subscriptionhistory`;
1880 CREATE TABLE `subscriptionhistory` (
1881 `biblionumber` int(11) NOT NULL default 0,
1882 `subscriptionid` int(11) NOT NULL default 0,
1883 `histstartdate` date default NULL,
1884 `enddate` date default NULL,
1885 `missinglist` longtext NOT NULL,
1886 `recievedlist` longtext NOT NULL,
1887 `opacnote` varchar(150) NOT NULL default '',
1888 `librariannote` varchar(150) NOT NULL default '',
1889 PRIMARY KEY (`subscriptionid`),
1890 KEY `biblionumber` (`biblionumber`)
1891 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1894 -- Table structure for table `subscriptionroutinglist`
1897 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1898 CREATE TABLE `subscriptionroutinglist` (
1899 `routingid` int(11) NOT NULL auto_increment,
1900 `borrowernumber` int(11) default NULL,
1901 `ranking` int(11) default NULL,
1902 `subscriptionid` int(11) default NULL,
1903 PRIMARY KEY (`routingid`)
1904 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1907 -- Table structure for table `suggestions`
1910 DROP TABLE IF EXISTS `suggestions`;
1911 CREATE TABLE `suggestions` (
1912 `suggestionid` int(8) NOT NULL auto_increment,
1913 `suggestedby` int(11) NOT NULL default 0,
1914 `managedby` int(11) default NULL,
1915 `STATUS` varchar(10) NOT NULL default '',
1917 `author` varchar(80) default NULL,
1918 `title` varchar(80) default NULL,
1919 `copyrightdate` smallint(6) default NULL,
1920 `publishercode` varchar(255) default NULL,
1921 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1922 `volumedesc` varchar(255) default NULL,
1923 `publicationyear` smallint(6) default 0,
1924 `place` varchar(255) default NULL,
1925 `isbn` varchar(10) default NULL,
1926 `mailoverseeing` smallint(1) default 0,
1927 `biblionumber` int(11) default NULL,
1929 PRIMARY KEY (`suggestionid`),
1930 KEY `suggestedby` (`suggestedby`),
1931 KEY `managedby` (`managedby`)
1932 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1935 -- Table structure for table `systempreferences`
1938 DROP TABLE IF EXISTS `systempreferences`;
1939 CREATE TABLE `systempreferences` (
1940 `variable` varchar(50) NOT NULL default '',
1942 `options` mediumtext,
1944 `type` varchar(20) default NULL,
1945 PRIMARY KEY (`variable`)
1946 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1949 -- Table structure for table `tags`
1952 DROP TABLE IF EXISTS `tags`;
1953 CREATE TABLE `tags` (
1954 `entry` varchar(255) NOT NULL default '',
1955 `weight` bigint(20) NOT NULL default 0,
1956 PRIMARY KEY (`entry`)
1957 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1960 -- Table structure for table `tags_all`
1963 DROP TABLE IF EXISTS `tags_all`;
1964 CREATE TABLE `tags_all` (
1965 `tag_id` int(11) NOT NULL auto_increment,
1966 `borrowernumber` int(11) NOT NULL,
1967 `biblionumber` int(11) NOT NULL,
1968 `term` varchar(255) NOT NULL,
1969 `language` int(4) default NULL,
1970 `date_created` datetime NOT NULL,
1971 PRIMARY KEY (`tag_id`),
1972 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1973 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1974 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1975 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1976 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1977 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1978 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1981 -- Table structure for table `tags_approval`
1984 DROP TABLE IF EXISTS `tags_approval`;
1985 CREATE TABLE `tags_approval` (
1986 `term` varchar(255) NOT NULL,
1987 `approved` int(1) NOT NULL default '0',
1988 `date_approved` datetime default NULL,
1989 `approved_by` int(11) default NULL,
1990 `weight_total` int(9) NOT NULL default '1',
1991 PRIMARY KEY (`term`),
1992 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1993 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1994 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1995 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1998 -- Table structure for table `tags_index`
2001 DROP TABLE IF EXISTS `tags_index`;
2002 CREATE TABLE `tags_index` (
2003 `term` varchar(255) NOT NULL,
2004 `biblionumber` int(11) NOT NULL,
2005 `weight` int(9) NOT NULL default '1',
2006 PRIMARY KEY (`term`,`biblionumber`),
2007 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2008 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2009 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2010 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2011 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2012 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2015 -- Table structure for table `userflags`
2018 DROP TABLE IF EXISTS `userflags`;
2019 CREATE TABLE `userflags` (
2020 `bit` int(11) NOT NULL default 0,
2021 `flag` varchar(30) default NULL,
2022 `flagdesc` varchar(255) default NULL,
2023 `defaulton` int(11) default NULL,
2025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2028 -- Table structure for table `virtualshelves`
2031 DROP TABLE IF EXISTS `virtualshelves`;
2032 CREATE TABLE `virtualshelves` (
2033 `shelfnumber` int(11) NOT NULL auto_increment,
2034 `shelfname` varchar(255) default NULL,
2035 `owner` varchar(80) default NULL,
2036 `category` varchar(1) default NULL,
2037 `sortfield` varchar(16) default NULL,
2038 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2039 PRIMARY KEY (`shelfnumber`)
2040 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2043 -- Table structure for table `virtualshelfcontents`
2046 DROP TABLE IF EXISTS `virtualshelfcontents`;
2047 CREATE TABLE `virtualshelfcontents` (
2048 `shelfnumber` int(11) NOT NULL default 0,
2049 `biblionumber` int(11) NOT NULL default 0,
2050 `flags` int(11) default NULL,
2051 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2052 KEY `shelfnumber` (`shelfnumber`),
2053 KEY `biblionumber` (`biblionumber`),
2054 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2055 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2056 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2059 -- Table structure for table `z3950servers`
2062 DROP TABLE IF EXISTS `z3950servers`;
2063 CREATE TABLE `z3950servers` (
2064 `host` varchar(255) default NULL,
2065 `port` int(11) default NULL,
2066 `db` varchar(255) default NULL,
2067 `userid` varchar(255) default NULL,
2068 `password` varchar(255) default NULL,
2070 `id` int(11) NOT NULL auto_increment,
2071 `checked` smallint(6) default NULL,
2072 `rank` int(11) default NULL,
2073 `syntax` varchar(80) default NULL,
2075 `position` enum('primary','secondary','') NOT NULL default 'primary',
2076 `type` enum('zed','opensearch') NOT NULL default 'zed',
2077 `encoding` text default NULL,
2078 `description` text NOT NULL,
2080 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2083 -- Table structure for table `zebraqueue`
2086 DROP TABLE IF EXISTS `zebraqueue`;
2087 CREATE TABLE `zebraqueue` (
2088 `id` int(11) NOT NULL auto_increment,
2089 `biblio_auth_number` int(11) NOT NULL default '0',
2090 `operation` char(20) NOT NULL default '',
2091 `server` char(20) NOT NULL default '',
2092 `done` int(11) NOT NULL default '0',
2093 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2095 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2096 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2098 DROP TABLE IF EXISTS `services_throttle`;
2099 CREATE TABLE `services_throttle` (
2100 `service_type` varchar(10) NOT NULL default '',
2101 `service_count` varchar(45) default NULL,
2102 PRIMARY KEY (`service_type`)
2103 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2105 -- http://www.w3.org/International/articles/language-tags/
2108 DROP TABLE IF EXISTS language_subtag_registry;
2109 CREATE TABLE language_subtag_registry (
2111 type varchar(25), -- language-script-region-variant-extension-privateuse
2112 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2114 KEY `subtag` (`subtag`)
2115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2117 -- TODO: add suppress_scripts
2118 -- this maps three letter codes defined in iso639.2 back to their
2119 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2120 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2121 CREATE TABLE language_rfc4646_to_iso639 (
2122 rfc4646_subtag varchar(25),
2123 iso639_2_code varchar(25),
2124 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2125 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2127 DROP TABLE IF EXISTS language_descriptions;
2128 CREATE TABLE language_descriptions (
2132 description varchar(255),
2134 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2136 -- bi-directional support, keyed by script subcode
2137 DROP TABLE IF EXISTS language_script_bidi;
2138 CREATE TABLE language_script_bidi (
2139 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2140 bidi varchar(3), -- rtl ltr
2141 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2142 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2144 -- TODO: need to map language subtags to script subtags for detection
2145 -- of bidi when script is not specified (like ar, he)
2146 DROP TABLE IF EXISTS language_script_mapping;
2147 CREATE TABLE language_script_mapping (
2148 language_subtag varchar(25),
2149 script_subtag varchar(25),
2150 KEY `language_subtag` (`language_subtag`)
2151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2153 DROP TABLE IF EXISTS `permissions`;
2154 CREATE TABLE `permissions` (
2155 `module_bit` int(11) NOT NULL DEFAULT 0,
2156 `code` varchar(64) DEFAULT NULL,
2157 `description` varchar(255) DEFAULT NULL,
2158 PRIMARY KEY (`module_bit`, `code`),
2159 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2160 ON DELETE CASCADE ON UPDATE CASCADE
2161 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2163 DROP TABLE IF EXISTS `serialitems`;
2164 CREATE TABLE `serialitems` (
2165 `itemnumber` int(11) NOT NULL,
2166 `serialid` int(11) NOT NULL,
2167 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2168 KEY `serialitems_sfk_1` (`serialid`),
2169 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2170 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2172 DROP TABLE IF EXISTS `user_permissions`;
2173 CREATE TABLE `user_permissions` (
2174 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2175 `module_bit` int(11) NOT NULL DEFAULT 0,
2176 `code` varchar(64) DEFAULT NULL,
2177 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2178 ON DELETE CASCADE ON UPDATE CASCADE,
2179 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2180 ON DELETE CASCADE ON UPDATE CASCADE
2181 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2184 -- Table structure for table `tmp_holdsqueue`
2187 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2188 CREATE TABLE `tmp_holdsqueue` (
2189 `biblionumber` int(11) default NULL,
2190 `itemnumber` int(11) default NULL,
2191 `barcode` varchar(20) default NULL,
2192 `surname` mediumtext NOT NULL,
2195 `borrowernumber` int(11) NOT NULL,
2196 `cardnumber` varchar(16) default NULL,
2197 `reservedate` date default NULL,
2199 `itemcallnumber` varchar(30) default NULL,
2200 `holdingbranch` varchar(10) default NULL,
2201 `pickbranch` varchar(10) default NULL,
2203 `item_level_request` tinyint(4) NOT NULL default 0
2204 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2207 -- Table structure for table `message_queue`
2210 DROP TABLE IF EXISTS `message_queue`;
2211 CREATE TABLE `message_queue` (
2212 `message_id` int(11) NOT NULL auto_increment,
2213 `borrowernumber` int(11) default NULL,
2216 `message_transport_type` varchar(20) NOT NULL,
2217 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2218 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2219 `to_address` mediumtext,
2220 `from_address` mediumtext,
2221 `content_type` text,
2222 KEY `message_id` (`message_id`),
2223 KEY `borrowernumber` (`borrowernumber`),
2224 KEY `message_transport_type` (`message_transport_type`),
2225 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2226 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2227 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2230 -- Table structure for table `message_transport_types`
2233 DROP TABLE IF EXISTS `message_transport_types`;
2234 CREATE TABLE `message_transport_types` (
2235 `message_transport_type` varchar(20) NOT NULL,
2236 PRIMARY KEY (`message_transport_type`)
2237 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2240 -- Table structure for table `message_attributes`
2243 DROP TABLE IF EXISTS `message_attributes`;
2244 CREATE TABLE `message_attributes` (
2245 `message_attribute_id` int(11) NOT NULL auto_increment,
2246 `message_name` varchar(20) NOT NULL default '',
2247 `takes_days` tinyint(1) NOT NULL default '0',
2248 PRIMARY KEY (`message_attribute_id`),
2249 UNIQUE KEY `message_name` (`message_name`)
2250 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2253 -- Table structure for table `message_transports`
2256 DROP TABLE IF EXISTS `message_transports`;
2257 CREATE TABLE `message_transports` (
2258 `message_attribute_id` int(11) NOT NULL,
2259 `message_transport_type` varchar(20) NOT NULL,
2260 `is_digest` tinyint(1) NOT NULL default '0',
2261 `letter_module` varchar(20) NOT NULL default '',
2262 `letter_code` varchar(20) NOT NULL default '',
2263 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2264 KEY `message_transport_type` (`message_transport_type`),
2265 KEY `letter_module` (`letter_module`,`letter_code`),
2266 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2267 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2268 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2272 -- Table structure for table `borrower_message_preferences`
2275 DROP TABLE IF EXISTS `borrower_message_preferences`;
2276 CREATE TABLE `borrower_message_preferences` (
2277 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2278 `borrowernumber` int(11) NOT NULL default '0',
2279 `message_attribute_id` int(11) default '0',
2280 `days_in_advance` int(11) default '0',
2281 `wants_digest` tinyint(1) NOT NULL default '0',
2282 PRIMARY KEY (`borrower_message_preference_id`),
2283 KEY `borrowernumber` (`borrowernumber`),
2284 KEY `message_attribute_id` (`message_attribute_id`),
2285 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2286 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
2287 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2290 -- Table structure for table `borrower_message_transport_preferences`
2293 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2294 CREATE TABLE `borrower_message_transport_preferences` (
2295 `borrower_message_preference_id` int(11) NOT NULL default '0',
2296 `message_transport_type` varchar(20) NOT NULL default '0',
2297 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2298 KEY `message_transport_type` (`message_transport_type`),
2299 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,
2300 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
2301 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2304 -- Table structure for the table branch_transfer_limits
2307 DROP TABLE IF EXISTS `branch_transfer_limits`;
2308 CREATE TABLE branch_transfer_limits (
2309 limitId int(8) NOT NULL auto_increment,
2310 toBranch varchar(4) NOT NULL,
2311 fromBranch varchar(4) NOT NULL,
2312 itemtype varchar(4) NOT NULL,
2313 PRIMARY KEY (limitId)
2314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2316 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2317 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2318 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2319 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2320 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2321 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2322 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2323 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;