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(30) default NULL,
392 `issn` varchar(9) default NULL,
393 `publicationyear` text,
394 `publishercode` varchar(255) default NULL,
395 `volumedate` date default NULL,
397 `collectiontitle` mediumtext default NULL,
398 `collectionissn` text default NULL,
399 `collectionvolume` mediumtext default NULL,
400 `editionstatement` text default NULL,
401 `editionresponsibility` text default NULL,
402 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
403 `illus` varchar(255) default NULL,
404 `pages` varchar(255) default NULL,
406 `size` varchar(255) default NULL,
407 `place` varchar(255) default NULL,
408 `lccn` varchar(25) default NULL,
410 `url` varchar(255) default NULL,
411 `cn_source` varchar(10) default NULL,
412 `cn_class` varchar(30) default NULL,
413 `cn_item` varchar(10) default NULL,
414 `cn_suffix` varchar(10) default NULL,
415 `cn_sort` varchar(30) default NULL,
416 `totalissues` int(10),
417 `marcxml` longtext NOT NULL,
418 PRIMARY KEY (`biblioitemnumber`),
419 KEY `bibinoidx` (`biblioitemnumber`),
420 KEY `bibnoidx` (`biblionumber`),
422 KEY `publishercode` (`publishercode`),
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;
535 CREATE TABLE `branch_item_rules` (
536 `branchcode` varchar(10) NOT NULL,
537 `itemtype` varchar(10) NOT NULL,
538 `holdallowed` tinyint(1) default NULL,
539 PRIMARY KEY (`itemtype`,`branchcode`),
540 KEY `branch_item_rules_ibfk_2` (`branchcode`),
541 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
542 ON DELETE CASCADE ON UPDATE CASCADE,
543 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
544 ON DELETE CASCADE ON UPDATE CASCADE
545 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
548 -- Table structure for table `branchcategories`
551 DROP TABLE IF EXISTS `branchcategories`;
552 CREATE TABLE `branchcategories` (
553 `categorycode` varchar(10) NOT NULL default '',
554 `categoryname` varchar(32),
555 `codedescription` mediumtext,
556 `categorytype` varchar(16),
557 PRIMARY KEY (`categorycode`)
558 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
561 -- Table structure for table `branches`
564 DROP TABLE IF EXISTS `branches`;
565 CREATE TABLE `branches` (
566 `branchcode` varchar(10) NOT NULL default '',
567 `branchname` mediumtext NOT NULL,
568 `branchaddress1` mediumtext,
569 `branchaddress2` mediumtext,
570 `branchaddress3` mediumtext,
571 `branchphone` mediumtext,
572 `branchfax` mediumtext,
573 `branchemail` mediumtext,
574 `issuing` tinyint(4) default NULL,
575 `branchip` varchar(15) default NULL,
576 `branchprinter` varchar(100) default NULL,
577 UNIQUE KEY `branchcode` (`branchcode`)
578 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
581 -- Table structure for table `branchrelations`
584 DROP TABLE IF EXISTS `branchrelations`;
585 CREATE TABLE `branchrelations` (
586 `branchcode` varchar(10) NOT NULL default '',
587 `categorycode` varchar(10) NOT NULL default '',
588 PRIMARY KEY (`branchcode`,`categorycode`),
589 KEY `branchcode` (`branchcode`),
590 KEY `categorycode` (`categorycode`),
591 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
592 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
593 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
596 -- Table structure for table `branchtransfers`
599 DROP TABLE IF EXISTS `branchtransfers`;
600 CREATE TABLE `branchtransfers` (
601 `itemnumber` int(11) NOT NULL default 0,
602 `datesent` datetime default NULL,
603 `frombranch` varchar(10) NOT NULL default '',
604 `datearrived` datetime default NULL,
605 `tobranch` varchar(10) NOT NULL default '',
606 `comments` mediumtext,
607 KEY `frombranch` (`frombranch`),
608 KEY `tobranch` (`tobranch`),
609 KEY `itemnumber` (`itemnumber`),
610 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
611 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
612 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
617 -- Table structure for table `browser`
619 DROP TABLE IF EXISTS `browser`;
620 CREATE TABLE `browser` (
621 `level` int(11) NOT NULL,
622 `classification` varchar(20) NOT NULL,
623 `description` varchar(255) NOT NULL,
624 `number` bigint(20) NOT NULL,
625 `endnode` tinyint(4) NOT NULL
626 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
629 -- Table structure for table `categories`
632 DROP TABLE IF EXISTS `categories`;
633 CREATE TABLE `categories` (
634 `categorycode` varchar(10) NOT NULL default '',
635 `description` mediumtext,
636 `enrolmentperiod` smallint(6) default NULL,
637 `upperagelimit` smallint(6) default NULL,
638 `dateofbirthrequired` tinyint(1) default NULL,
639 `finetype` varchar(30) default NULL,
640 `bulk` tinyint(1) default NULL,
641 `enrolmentfee` decimal(28,6) default NULL,
642 `overduenoticerequired` tinyint(1) default NULL,
643 `issuelimit` smallint(6) default NULL,
644 `reservefee` decimal(28,6) default NULL,
645 `category_type` varchar(1) NOT NULL default 'A',
646 PRIMARY KEY (`categorycode`),
647 UNIQUE KEY `categorycode` (`categorycode`)
648 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
651 -- Table structure for table `borrower_branch_circ_rules`
654 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
655 CREATE TABLE `branch_borrower_circ_rules` (
656 `branchcode` VARCHAR(10) NOT NULL,
657 `categorycode` VARCHAR(10) NOT NULL,
658 `maxissueqty` int(4) default NULL,
659 PRIMARY KEY (`categorycode`, `branchcode`),
660 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
661 ON DELETE CASCADE ON UPDATE CASCADE,
662 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
663 ON DELETE CASCADE ON UPDATE CASCADE
664 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
667 -- Table structure for table `default_borrower_circ_rules`
670 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
671 CREATE TABLE `default_borrower_circ_rules` (
672 `categorycode` VARCHAR(10) NOT NULL,
673 `maxissueqty` int(4) default NULL,
674 PRIMARY KEY (`categorycode`),
675 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
676 ON DELETE CASCADE ON UPDATE CASCADE
677 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
680 -- Table structure for table `default_branch_circ_rules`
683 DROP TABLE IF EXISTS `default_branch_circ_rules`;
684 CREATE TABLE `default_branch_circ_rules` (
685 `branchcode` VARCHAR(10) NOT NULL,
686 `maxissueqty` int(4) default NULL,
687 `holdallowed` tinyint(1) default NULL,
688 PRIMARY KEY (`branchcode`),
689 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
690 ON DELETE CASCADE ON UPDATE CASCADE
691 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
694 -- Table structure for table `default_branch_item_rules`
697 CREATE TABLE `default_branch_item_rules` (
698 `itemtype` varchar(10) NOT NULL,
699 `holdallowed` tinyint(1) default NULL,
700 PRIMARY KEY (`itemtype`),
701 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
702 ON DELETE CASCADE ON UPDATE CASCADE
703 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
706 -- Table structure for table `default_circ_rules`
709 DROP TABLE IF EXISTS `default_circ_rules`;
710 CREATE TABLE `default_circ_rules` (
711 `singleton` enum('singleton') NOT NULL default 'singleton',
712 `maxissueqty` int(4) default NULL,
713 `holdallowed` int(1) default NULL,
714 PRIMARY KEY (`singleton`)
715 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
718 -- Table structure for table `cities`
721 DROP TABLE IF EXISTS `cities`;
722 CREATE TABLE `cities` (
723 `cityid` int(11) NOT NULL auto_increment,
724 `city_name` varchar(100) NOT NULL default '',
725 `city_zipcode` varchar(20) default NULL,
726 PRIMARY KEY (`cityid`)
727 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
730 -- Table structure for table `class_sort_rules`
733 DROP TABLE IF EXISTS `class_sort_rules`;
734 CREATE TABLE `class_sort_rules` (
735 `class_sort_rule` varchar(10) NOT NULL default '',
736 `description` mediumtext,
737 `sort_routine` varchar(30) NOT NULL default '',
738 PRIMARY KEY (`class_sort_rule`),
739 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
740 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
743 -- Table structure for table `class_sources`
746 DROP TABLE IF EXISTS `class_sources`;
747 CREATE TABLE `class_sources` (
748 `cn_source` varchar(10) NOT NULL default '',
749 `description` mediumtext,
750 `used` tinyint(4) NOT NULL default 0,
751 `class_sort_rule` varchar(10) NOT NULL default '',
752 PRIMARY KEY (`cn_source`),
753 UNIQUE KEY `cn_source_idx` (`cn_source`),
754 KEY `used_idx` (`used`),
755 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
756 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
759 -- Table structure for table `currency`
762 DROP TABLE IF EXISTS `currency`;
763 CREATE TABLE `currency` (
764 `currency` varchar(10) NOT NULL default '',
765 `symbol` varchar(5) default NULL,
766 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
767 `rate` float(7,5) default NULL,
768 PRIMARY KEY (`currency`)
769 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
772 -- Table structure for table `deletedbiblio`
775 DROP TABLE IF EXISTS `deletedbiblio`;
776 CREATE TABLE `deletedbiblio` (
777 `biblionumber` int(11) NOT NULL default 0,
778 `frameworkcode` varchar(4) NOT NULL default '',
781 `unititle` mediumtext,
783 `serial` tinyint(1) default NULL,
784 `seriestitle` mediumtext,
785 `copyrightdate` smallint(6) default NULL,
786 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
787 `datecreated` DATE NOT NULL,
788 `abstract` mediumtext,
789 PRIMARY KEY (`biblionumber`),
790 KEY `blbnoidx` (`biblionumber`)
791 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
794 -- Table structure for table `deletedbiblioitems`
797 DROP TABLE IF EXISTS `deletedbiblioitems`;
798 CREATE TABLE `deletedbiblioitems` (
799 `biblioitemnumber` int(11) NOT NULL default 0,
800 `biblionumber` int(11) NOT NULL default 0,
803 `itemtype` varchar(10) default NULL,
804 `isbn` varchar(30) default NULL,
805 `issn` varchar(9) default NULL,
806 `publicationyear` text,
807 `publishercode` varchar(255) default NULL,
808 `volumedate` date default NULL,
810 `collectiontitle` mediumtext default NULL,
811 `collectionissn` text default NULL,
812 `collectionvolume` mediumtext default NULL,
813 `editionstatement` text default NULL,
814 `editionresponsibility` text default NULL,
815 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
816 `illus` varchar(255) default NULL,
817 `pages` varchar(255) default NULL,
819 `size` varchar(255) default NULL,
820 `place` varchar(255) default NULL,
821 `lccn` varchar(25) default NULL,
823 `url` varchar(255) default NULL,
824 `cn_source` varchar(10) default NULL,
825 `cn_class` varchar(30) default NULL,
826 `cn_item` varchar(10) default NULL,
827 `cn_suffix` varchar(10) default NULL,
828 `cn_sort` varchar(30) default NULL,
829 `totalissues` int(10),
830 `marcxml` longtext NOT NULL,
831 PRIMARY KEY (`biblioitemnumber`),
832 KEY `bibinoidx` (`biblioitemnumber`),
833 KEY `bibnoidx` (`biblionumber`),
835 KEY `publishercode` (`publishercode`)
836 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
839 -- Table structure for table `deletedborrowers`
842 DROP TABLE IF EXISTS `deletedborrowers`;
843 CREATE TABLE `deletedborrowers` (
844 `borrowernumber` int(11) NOT NULL default 0,
845 `cardnumber` varchar(9) NOT NULL default '',
846 `surname` mediumtext NOT NULL,
849 `othernames` mediumtext,
851 `streetnumber` varchar(10) default NULL,
852 `streettype` varchar(50) default NULL,
853 `address` mediumtext NOT NULL,
855 `city` mediumtext NOT NULL,
856 `zipcode` varchar(25) default NULL,
859 `mobile` varchar(50) default NULL,
863 `B_streetnumber` varchar(10) default NULL,
864 `B_streettype` varchar(50) default NULL,
865 `B_address` varchar(100) default NULL,
867 `B_zipcode` varchar(25) default NULL,
869 `B_phone` mediumtext,
870 `dateofbirth` date default NULL,
871 `branchcode` varchar(10) NOT NULL default '',
872 `categorycode` varchar(10) default NULL,
873 `dateenrolled` date default NULL,
874 `dateexpiry` date default NULL,
875 `gonenoaddress` tinyint(1) default NULL,
876 `lost` tinyint(1) default NULL,
877 `debarred` tinyint(1) default NULL,
878 `contactname` mediumtext,
879 `contactfirstname` text,
881 `guarantorid` int(11) default NULL,
882 `borrowernotes` mediumtext,
883 `relationship` varchar(100) default NULL,
884 `ethnicity` varchar(50) default NULL,
885 `ethnotes` varchar(255) default NULL,
886 `sex` varchar(1) default NULL,
887 `password` varchar(30) default NULL,
888 `flags` int(11) default NULL,
889 `userid` varchar(30) default NULL,
890 `opacnote` mediumtext,
891 `contactnote` varchar(255) default NULL,
892 `sort1` varchar(80) default NULL,
893 `sort2` varchar(80) default NULL,
894 `altcontactfirstname` varchar(255) default NULL,
895 `altcontactsurname` varchar(255) default NULL,
896 `altcontactaddress1` varchar(255) default NULL,
897 `altcontactaddress2` varchar(255) default NULL,
898 `altcontactaddress3` varchar(255) default NULL,
899 `altcontactzipcode` varchar(50) default NULL,
900 `altcontactphone` varchar(50) default NULL,
901 `smsalertnumber` varchar(50) default NULL,
902 KEY `borrowernumber` (`borrowernumber`),
903 KEY `cardnumber` (`cardnumber`)
904 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
907 -- Table structure for table `deleteditems`
910 DROP TABLE IF EXISTS `deleteditems`;
911 CREATE TABLE `deleteditems` (
912 `itemnumber` int(11) NOT NULL default 0,
913 `biblionumber` int(11) NOT NULL default 0,
914 `biblioitemnumber` int(11) NOT NULL default 0,
915 `barcode` varchar(20) default NULL,
916 `dateaccessioned` date default NULL,
917 `booksellerid` mediumtext default NULL,
918 `homebranch` varchar(10) default NULL,
919 `price` decimal(8,2) default NULL,
920 `replacementprice` decimal(8,2) default NULL,
921 `replacementpricedate` date default NULL,
922 `datelastborrowed` date default NULL,
923 `datelastseen` date default NULL,
924 `stack` tinyint(1) default NULL,
925 `notforloan` tinyint(1) NOT NULL default 0,
926 `damaged` tinyint(1) NOT NULL default 0,
927 `itemlost` tinyint(1) NOT NULL default 0,
928 `wthdrawn` tinyint(1) NOT NULL default 0,
929 `itemcallnumber` varchar(30) default NULL,
930 `issues` smallint(6) default NULL,
931 `renewals` smallint(6) default NULL,
932 `reserves` smallint(6) default NULL,
933 `restricted` tinyint(1) default NULL,
934 `itemnotes` mediumtext,
935 `holdingbranch` varchar(10) default NULL,
936 `paidfor` mediumtext,
937 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
938 `location` varchar(80) default NULL,
939 `onloan` date default NULL,
940 `cn_source` varchar(10) default NULL,
941 `cn_sort` varchar(30) default NULL,
942 `ccode` varchar(10) default NULL,
943 `materials` varchar(10) default NULL,
944 `uri` varchar(255) default NULL,
945 `itype` varchar(10) default NULL,
946 `more_subfields_xml` longtext default NULL,
947 `enumchron` varchar(80) default NULL,
948 `copynumber` varchar(32) default NULL,
950 PRIMARY KEY (`itemnumber`),
951 KEY `delitembarcodeidx` (`barcode`),
952 KEY `delitembinoidx` (`biblioitemnumber`),
953 KEY `delitembibnoidx` (`biblionumber`),
954 KEY `delhomebranch` (`homebranch`),
955 KEY `delholdingbranch` (`holdingbranch`)
956 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
959 -- Table structure for table `ethnicity`
962 DROP TABLE IF EXISTS `ethnicity`;
963 CREATE TABLE `ethnicity` (
964 `code` varchar(10) NOT NULL default '',
965 `name` varchar(255) default NULL,
967 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
970 -- Table structure for table `hold_fill_targets`
973 DROP TABLE IF EXISTS `hold_fill_targets`;
974 CREATE TABLE hold_fill_targets (
975 `borrowernumber` int(11) NOT NULL,
976 `biblionumber` int(11) NOT NULL,
977 `itemnumber` int(11) NOT NULL,
978 `source_branchcode` varchar(10) default NULL,
979 `item_level_request` tinyint(4) NOT NULL default 0,
980 PRIMARY KEY `itemnumber` (`itemnumber`),
981 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
982 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
983 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
984 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
985 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
986 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
987 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
988 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
989 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
990 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
993 -- Table structure for table `import_batches`
996 DROP TABLE IF EXISTS `import_batches`;
997 CREATE TABLE `import_batches` (
998 `import_batch_id` int(11) NOT NULL auto_increment,
999 `matcher_id` int(11) default NULL,
1000 `template_id` int(11) default NULL,
1001 `branchcode` varchar(10) default NULL,
1002 `num_biblios` int(11) NOT NULL default 0,
1003 `num_items` int(11) NOT NULL default 0,
1004 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1005 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1006 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1007 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1008 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1009 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1010 `file_name` varchar(100),
1011 `comments` mediumtext,
1012 PRIMARY KEY (`import_batch_id`),
1013 KEY `branchcode` (`branchcode`)
1014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1017 -- Table structure for table `import_records`
1020 DROP TABLE IF EXISTS `import_records`;
1021 CREATE TABLE `import_records` (
1022 `import_record_id` int(11) NOT NULL auto_increment,
1023 `import_batch_id` int(11) NOT NULL,
1024 `branchcode` varchar(10) default NULL,
1025 `record_sequence` int(11) NOT NULL default 0,
1026 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1027 `import_date` DATE default NULL,
1028 `marc` longblob NOT NULL,
1029 `marcxml` longtext NOT NULL,
1030 `marcxml_old` longtext NOT NULL,
1031 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1032 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1033 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1034 `import_error` mediumtext,
1035 `encoding` varchar(40) NOT NULL default '',
1036 `z3950random` varchar(40) default NULL,
1037 PRIMARY KEY (`import_record_id`),
1038 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1039 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1040 KEY `branchcode` (`branchcode`),
1041 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1042 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1045 -- Table structure for `import_record_matches`
1047 DROP TABLE IF EXISTS `import_record_matches`;
1048 CREATE TABLE `import_record_matches` (
1049 `import_record_id` int(11) NOT NULL,
1050 `candidate_match_id` int(11) NOT NULL,
1051 `score` int(11) NOT NULL default 0,
1052 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1053 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1054 KEY `record_score` (`import_record_id`, `score`)
1055 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1058 -- Table structure for table `import_biblios`
1061 DROP TABLE IF EXISTS `import_biblios`;
1062 CREATE TABLE `import_biblios` (
1063 `import_record_id` int(11) NOT NULL,
1064 `matched_biblionumber` int(11) default NULL,
1065 `control_number` varchar(25) default NULL,
1066 `original_source` varchar(25) default NULL,
1067 `title` varchar(128) default NULL,
1068 `author` varchar(80) default NULL,
1069 `isbn` varchar(30) default NULL,
1070 `issn` varchar(9) default NULL,
1071 `has_items` tinyint(1) NOT NULL default 0,
1072 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1073 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1074 KEY `matched_biblionumber` (`matched_biblionumber`),
1075 KEY `title` (`title`),
1077 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1080 -- Table structure for table `import_items`
1083 DROP TABLE IF EXISTS `import_items`;
1084 CREATE TABLE `import_items` (
1085 `import_items_id` int(11) NOT NULL auto_increment,
1086 `import_record_id` int(11) NOT NULL,
1087 `itemnumber` int(11) default NULL,
1088 `branchcode` varchar(10) default NULL,
1089 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1090 `marcxml` longtext NOT NULL,
1091 `import_error` mediumtext,
1092 PRIMARY KEY (`import_items_id`),
1093 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1094 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1095 KEY `itemnumber` (`itemnumber`),
1096 KEY `branchcode` (`branchcode`)
1097 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1100 -- Table structure for table `issues`
1103 DROP TABLE IF EXISTS `issues`;
1104 CREATE TABLE `issues` (
1105 `borrowernumber` int(11) default NULL,
1106 `itemnumber` int(11) default NULL,
1107 `date_due` date default NULL,
1108 `branchcode` varchar(10) default NULL,
1109 `issuingbranch` varchar(18) default NULL,
1110 `returndate` date default NULL,
1111 `lastreneweddate` date default NULL,
1112 `return` varchar(4) default NULL,
1113 `renewals` tinyint(4) default NULL,
1114 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1115 `issuedate` date default NULL,
1116 KEY `issuesborridx` (`borrowernumber`),
1117 KEY `issuesitemidx` (`itemnumber`),
1118 KEY `bordate` (`borrowernumber`,`timestamp`),
1119 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1120 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1124 -- Table structure for table `issuingrules`
1127 DROP TABLE IF EXISTS `issuingrules`;
1128 CREATE TABLE `issuingrules` (
1129 `categorycode` varchar(10) NOT NULL default '',
1130 `itemtype` varchar(10) NOT NULL default '',
1131 `restrictedtype` tinyint(1) default NULL,
1132 `rentaldiscount` decimal(28,6) default NULL,
1133 `reservecharge` decimal(28,6) default NULL,
1134 `fine` decimal(28,6) default NULL,
1135 `firstremind` int(11) default NULL,
1136 `chargeperiod` int(11) default NULL,
1137 `accountsent` int(11) default NULL,
1138 `chargename` varchar(100) default NULL,
1139 `maxissueqty` int(4) default NULL,
1140 `issuelength` int(4) default NULL,
1141 `branchcode` varchar(10) NOT NULL default '',
1142 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1143 KEY `categorycode` (`categorycode`),
1144 KEY `itemtype` (`itemtype`)
1145 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1148 -- Table structure for table `items`
1151 DROP TABLE IF EXISTS `items`;
1152 CREATE TABLE `items` (
1153 `itemnumber` int(11) NOT NULL auto_increment,
1154 `biblionumber` int(11) NOT NULL default 0,
1155 `biblioitemnumber` int(11) NOT NULL default 0,
1156 `barcode` varchar(20) default NULL,
1157 `dateaccessioned` date default NULL,
1158 `booksellerid` mediumtext default NULL,
1159 `homebranch` varchar(10) default NULL,
1160 `price` decimal(8,2) default NULL,
1161 `replacementprice` decimal(8,2) default NULL,
1162 `replacementpricedate` date default NULL,
1163 `datelastborrowed` date default NULL,
1164 `datelastseen` date default NULL,
1165 `stack` tinyint(1) default NULL,
1166 `notforloan` tinyint(1) NOT NULL default 0,
1167 `damaged` tinyint(1) NOT NULL default 0,
1168 `itemlost` tinyint(1) NOT NULL default 0,
1169 `wthdrawn` tinyint(1) NOT NULL default 0,
1170 `itemcallnumber` varchar(30) default NULL,
1171 `issues` smallint(6) default NULL,
1172 `renewals` smallint(6) default NULL,
1173 `reserves` smallint(6) default NULL,
1174 `restricted` tinyint(1) default NULL,
1175 `itemnotes` mediumtext,
1176 `holdingbranch` varchar(10) default NULL,
1177 `paidfor` mediumtext,
1178 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1179 `location` varchar(80) default NULL,
1180 `onloan` date default NULL,
1181 `cn_source` varchar(10) default NULL,
1182 `cn_sort` varchar(30) default NULL,
1183 `ccode` varchar(10) default NULL,
1184 `materials` varchar(10) default NULL,
1185 `uri` varchar(255) default NULL,
1186 `itype` varchar(10) default NULL,
1187 `more_subfields_xml` longtext default NULL,
1188 `enumchron` varchar(80) default NULL,
1189 `copynumber` varchar(32) default NULL,
1190 PRIMARY KEY (`itemnumber`),
1191 UNIQUE KEY `itembarcodeidx` (`barcode`),
1192 KEY `itembinoidx` (`biblioitemnumber`),
1193 KEY `itembibnoidx` (`biblionumber`),
1194 KEY `homebranch` (`homebranch`),
1195 KEY `holdingbranch` (`holdingbranch`),
1196 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1197 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1198 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1202 -- Table structure for table `itemtypes`
1205 DROP TABLE IF EXISTS `itemtypes`;
1206 CREATE TABLE `itemtypes` (
1207 `itemtype` varchar(10) NOT NULL default '',
1208 `description` mediumtext,
1209 `renewalsallowed` smallint(6) default NULL,
1210 `rentalcharge` double(16,4) default NULL,
1211 `notforloan` smallint(6) default NULL,
1212 `imageurl` varchar(200) default NULL,
1214 PRIMARY KEY (`itemtype`),
1215 UNIQUE KEY `itemtype` (`itemtype`)
1216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1219 -- Table structure for table `labels`
1222 DROP TABLE IF EXISTS `labels`;
1223 CREATE TABLE `labels` (
1224 `labelid` int(11) NOT NULL auto_increment,
1225 `batch_id` int(10) NOT NULL default 1,
1226 `itemnumber` varchar(100) NOT NULL default '',
1227 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1228 PRIMARY KEY (`labelid`)
1229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1232 -- Table structure for table `labels_conf`
1235 DROP TABLE IF EXISTS `labels_conf`;
1236 CREATE TABLE `labels_conf` (
1237 `id` int(4) NOT NULL auto_increment,
1238 `barcodetype` char(100) default '',
1239 `title` int(1) default '0',
1240 `subtitle` int(1) default '0',
1241 `itemtype` int(1) default '0',
1242 `barcode` int(1) default '0',
1243 `dewey` int(1) default '0',
1244 `classification` int(1) default NULL,
1245 `subclass` int(1) default '0',
1246 `itemcallnumber` int(1) default '0',
1247 `author` int(1) default '0',
1248 `issn` int(1) default '0',
1249 `isbn` int(1) default '0',
1250 `startlabel` int(2) NOT NULL default '1',
1251 `printingtype` char(32) default 'BAR',
1252 `formatstring` mediumtext default NULL,
1253 `layoutname` char(20) NOT NULL default 'TEST',
1254 `guidebox` int(1) default '0',
1255 `active` tinyint(1) default '1',
1256 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1257 `ccode` char(4) collate utf8_unicode_ci default NULL,
1258 `callnum_split` int(1) default NULL,
1259 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1261 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1264 -- Table structure for table `labels_profile`
1267 DROP TABLE IF EXISTS `labels_profile`;
1268 CREATE TABLE `labels_profile` (
1269 `tmpl_id` int(4) NOT NULL,
1270 `prof_id` int(4) NOT NULL,
1271 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1272 UNIQUE KEY `prof_id` (`prof_id`)
1273 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1276 -- Table structure for table `labels_templates`
1279 DROP TABLE IF EXISTS `labels_templates`;
1280 CREATE TABLE `labels_templates` (
1281 `tmpl_id` int(4) NOT NULL auto_increment,
1282 `tmpl_code` char(100) default '',
1283 `tmpl_desc` char(100) default '',
1284 `page_width` float default '0',
1285 `page_height` float default '0',
1286 `label_width` float default '0',
1287 `label_height` float default '0',
1288 `topmargin` float default '0',
1289 `leftmargin` float default '0',
1290 `cols` int(2) default '0',
1291 `rows` int(2) default '0',
1292 `colgap` float default '0',
1293 `rowgap` float default '0',
1294 `active` int(1) default NULL,
1295 `units` char(20) default 'PX',
1296 `fontsize` int(4) NOT NULL default '3',
1297 `font` char(10) NOT NULL default 'TR',
1298 PRIMARY KEY (`tmpl_id`)
1299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1302 -- Table structure for table `letter`
1305 DROP TABLE IF EXISTS `letter`;
1306 CREATE TABLE `letter` (
1307 `module` varchar(20) NOT NULL default '',
1308 `code` varchar(20) NOT NULL default '',
1309 `name` varchar(100) NOT NULL default '',
1310 `title` varchar(200) NOT NULL default '',
1312 PRIMARY KEY (`module`,`code`)
1313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1316 -- Table structure for table `marc_subfield_structure`
1319 DROP TABLE IF EXISTS `marc_subfield_structure`;
1320 CREATE TABLE `marc_subfield_structure` (
1321 `tagfield` varchar(3) NOT NULL default '',
1322 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1323 `liblibrarian` varchar(255) NOT NULL default '',
1324 `libopac` varchar(255) NOT NULL default '',
1325 `repeatable` tinyint(4) NOT NULL default 0,
1326 `mandatory` tinyint(4) NOT NULL default 0,
1327 `kohafield` varchar(40) default NULL,
1328 `tab` tinyint(1) default NULL,
1329 `authorised_value` varchar(20) default NULL,
1330 `authtypecode` varchar(20) default NULL,
1331 `value_builder` varchar(80) default NULL,
1332 `isurl` tinyint(1) default NULL,
1333 `hidden` tinyint(1) default NULL,
1334 `frameworkcode` varchar(4) NOT NULL default '',
1335 `seealso` varchar(1100) default NULL,
1336 `link` varchar(80) default NULL,
1337 `defaultvalue` text default NULL,
1338 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1339 KEY `kohafield_2` (`kohafield`),
1340 KEY `tab` (`frameworkcode`,`tab`),
1341 KEY `kohafield` (`frameworkcode`,`kohafield`)
1342 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1345 -- Table structure for table `marc_tag_structure`
1348 DROP TABLE IF EXISTS `marc_tag_structure`;
1349 CREATE TABLE `marc_tag_structure` (
1350 `tagfield` varchar(3) NOT NULL default '',
1351 `liblibrarian` varchar(255) NOT NULL default '',
1352 `libopac` varchar(255) NOT NULL default '',
1353 `repeatable` tinyint(4) NOT NULL default 0,
1354 `mandatory` tinyint(4) NOT NULL default 0,
1355 `authorised_value` varchar(10) default NULL,
1356 `frameworkcode` varchar(4) NOT NULL default '',
1357 PRIMARY KEY (`frameworkcode`,`tagfield`)
1358 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1361 -- Table structure for table `marc_matchers`
1364 DROP TABLE IF EXISTS `marc_matchers`;
1365 CREATE TABLE `marc_matchers` (
1366 `matcher_id` int(11) NOT NULL auto_increment,
1367 `code` varchar(10) NOT NULL default '',
1368 `description` varchar(255) NOT NULL default '',
1369 `record_type` varchar(10) NOT NULL default 'biblio',
1370 `threshold` int(11) NOT NULL default 0,
1371 PRIMARY KEY (`matcher_id`),
1372 KEY `code` (`code`),
1373 KEY `record_type` (`record_type`)
1374 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1377 -- Table structure for table `matchpoints`
1379 DROP TABLE IF EXISTS `matchpoints`;
1380 CREATE TABLE `matchpoints` (
1381 `matcher_id` int(11) NOT NULL,
1382 `matchpoint_id` int(11) NOT NULL auto_increment,
1383 `search_index` varchar(30) NOT NULL default '',
1384 `score` int(11) NOT NULL default 0,
1385 PRIMARY KEY (`matchpoint_id`),
1386 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1387 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1388 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1392 -- Table structure for table `matchpoint_components`
1394 DROP TABLE IF EXISTS `matchpoint_components`;
1395 CREATE TABLE `matchpoint_components` (
1396 `matchpoint_id` int(11) NOT NULL,
1397 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1398 sequence int(11) NOT NULL default 0,
1399 tag varchar(3) NOT NULL default '',
1400 subfields varchar(40) NOT NULL default '',
1401 offset int(4) NOT NULL default 0,
1402 length int(4) NOT NULL default 0,
1403 PRIMARY KEY (`matchpoint_component_id`),
1404 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1405 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1406 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1407 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1410 -- Table structure for table `matcher_component_norms`
1412 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1413 CREATE TABLE `matchpoint_component_norms` (
1414 `matchpoint_component_id` int(11) NOT NULL,
1415 `sequence` int(11) NOT NULL default 0,
1416 `norm_routine` varchar(50) NOT NULL default '',
1417 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1418 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1419 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1420 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1423 -- Table structure for table `matcher_matchpoints`
1425 DROP TABLE IF EXISTS `matcher_matchpoints`;
1426 CREATE TABLE `matcher_matchpoints` (
1427 `matcher_id` int(11) NOT NULL,
1428 `matchpoint_id` int(11) NOT NULL,
1429 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1430 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1431 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1432 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1433 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1436 -- Table structure for table `matchchecks`
1438 DROP TABLE IF EXISTS `matchchecks`;
1439 CREATE TABLE `matchchecks` (
1440 `matcher_id` int(11) NOT NULL,
1441 `matchcheck_id` int(11) NOT NULL auto_increment,
1442 `source_matchpoint_id` int(11) NOT NULL,
1443 `target_matchpoint_id` int(11) NOT NULL,
1444 PRIMARY KEY (`matchcheck_id`),
1445 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1446 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1447 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1448 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1449 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1450 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1451 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1454 -- Table structure for table `notifys`
1457 DROP TABLE IF EXISTS `notifys`;
1458 CREATE TABLE `notifys` (
1459 `notify_id` int(11) NOT NULL default 0,
1460 `borrowernumber` int(11) NOT NULL default 0,
1461 `itemnumber` int(11) NOT NULL default 0,
1462 `notify_date` date default NULL,
1463 `notify_send_date` date default NULL,
1464 `notify_level` int(1) NOT NULL default 0,
1465 `method` varchar(20) NOT NULL default ''
1466 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1469 -- Table structure for table `nozebra`
1472 DROP TABLE IF EXISTS `nozebra`;
1473 CREATE TABLE `nozebra` (
1474 `server` varchar(20) NOT NULL,
1475 `indexname` varchar(40) NOT NULL,
1476 `value` varchar(250) NOT NULL,
1477 `biblionumbers` longtext NOT NULL,
1478 KEY `indexname` (`server`,`indexname`),
1479 KEY `value` (`server`,`value`))
1480 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1483 -- Table structure for table `old_issues`
1486 DROP TABLE IF EXISTS `old_issues`;
1487 CREATE TABLE `old_issues` (
1488 `borrowernumber` int(11) default NULL,
1489 `itemnumber` int(11) default NULL,
1490 `date_due` date default NULL,
1491 `branchcode` varchar(10) default NULL,
1492 `issuingbranch` varchar(18) default NULL,
1493 `returndate` date default NULL,
1494 `lastreneweddate` date default NULL,
1495 `return` varchar(4) default NULL,
1496 `renewals` tinyint(4) default NULL,
1497 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1498 `issuedate` date default NULL,
1499 KEY `old_issuesborridx` (`borrowernumber`),
1500 KEY `old_issuesitemidx` (`itemnumber`),
1501 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1502 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1503 ON DELETE SET NULL ON UPDATE SET NULL,
1504 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1505 ON DELETE SET NULL ON UPDATE SET NULL
1506 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1509 -- Table structure for table `old_reserves`
1511 DROP TABLE IF EXISTS `old_reserves`;
1512 CREATE TABLE `old_reserves` (
1513 `borrowernumber` int(11) default NULL,
1514 `reservedate` date default NULL,
1515 `biblionumber` int(11) default NULL,
1516 `constrainttype` varchar(1) default NULL,
1517 `branchcode` varchar(10) default NULL,
1518 `notificationdate` date default NULL,
1519 `reminderdate` date default NULL,
1520 `cancellationdate` date default NULL,
1521 `reservenotes` mediumtext,
1522 `priority` smallint(6) default NULL,
1523 `found` varchar(1) default NULL,
1524 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1525 `itemnumber` int(11) default NULL,
1526 `waitingdate` date default NULL,
1527 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1528 KEY `old_reserves_biblionumber` (`biblionumber`),
1529 KEY `old_reserves_itemnumber` (`itemnumber`),
1530 KEY `old_reserves_branchcode` (`branchcode`),
1531 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1532 ON DELETE SET NULL ON UPDATE SET NULL,
1533 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1534 ON DELETE SET NULL ON UPDATE SET NULL,
1535 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1536 ON DELETE SET NULL ON UPDATE SET NULL
1537 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1540 -- Table structure for table `opac_news`
1543 DROP TABLE IF EXISTS `opac_news`;
1544 CREATE TABLE `opac_news` (
1545 `idnew` int(10) unsigned NOT NULL auto_increment,
1546 `title` varchar(250) NOT NULL default '',
1547 `new` text NOT NULL,
1548 `lang` varchar(25) NOT NULL default '',
1549 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1550 `expirationdate` date default NULL,
1551 `number` int(11) default NULL,
1552 PRIMARY KEY (`idnew`)
1553 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1556 -- Table structure for table `overduerules`
1559 DROP TABLE IF EXISTS `overduerules`;
1560 CREATE TABLE `overduerules` (
1561 `branchcode` varchar(10) NOT NULL default '',
1562 `categorycode` varchar(10) NOT NULL default '',
1563 `delay1` int(4) default 0,
1564 `letter1` varchar(20) default NULL,
1565 `debarred1` varchar(1) default 0,
1566 `delay2` int(4) default 0,
1567 `debarred2` varchar(1) default 0,
1568 `letter2` varchar(20) default NULL,
1569 `delay3` int(4) default 0,
1570 `letter3` varchar(20) default NULL,
1571 `debarred3` int(1) default 0,
1572 PRIMARY KEY (`branchcode`,`categorycode`)
1573 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1576 -- Table structure for table `patroncards`
1579 DROP TABLE IF EXISTS `patroncards`;
1580 CREATE TABLE `patroncards` (
1581 `cardid` int(11) NOT NULL auto_increment,
1582 `batch_id` varchar(10) NOT NULL default '1',
1583 `borrowernumber` int(11) NOT NULL,
1584 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1585 PRIMARY KEY (`cardid`),
1586 KEY `patroncards_ibfk_1` (`borrowernumber`),
1587 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1588 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1591 -- Table structure for table `patronimage`
1594 DROP TABLE IF EXISTS `patronimage`;
1595 CREATE TABLE `patronimage` (
1596 `cardnumber` varchar(16) NOT NULL,
1597 `mimetype` varchar(15) NOT NULL,
1598 `imagefile` mediumblob NOT NULL,
1599 PRIMARY KEY (`cardnumber`),
1600 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1601 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1604 -- Table structure for table `printers`
1607 DROP TABLE IF EXISTS `printers`;
1608 CREATE TABLE `printers` (
1609 `printername` varchar(40) NOT NULL default '',
1610 `printqueue` varchar(20) default NULL,
1611 `printtype` varchar(20) default NULL,
1612 PRIMARY KEY (`printername`)
1613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1616 -- Table structure for table `printers_profile`
1619 DROP TABLE IF EXISTS `printers_profile`;
1620 CREATE TABLE `printers_profile` (
1621 `prof_id` int(4) NOT NULL auto_increment,
1622 `printername` varchar(40) NOT NULL,
1623 `tmpl_id` int(4) NOT NULL,
1624 `paper_bin` varchar(20) NOT NULL,
1625 `offset_horz` float default NULL,
1626 `offset_vert` float default NULL,
1627 `creep_horz` float default NULL,
1628 `creep_vert` float default NULL,
1629 `unit` char(20) NOT NULL default 'POINT',
1630 PRIMARY KEY (`prof_id`),
1631 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1632 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1633 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1636 -- Table structure for table `repeatable_holidays`
1639 DROP TABLE IF EXISTS `repeatable_holidays`;
1640 CREATE TABLE `repeatable_holidays` (
1641 `id` int(11) NOT NULL auto_increment,
1642 `branchcode` varchar(10) NOT NULL default '',
1643 `weekday` smallint(6) default NULL,
1644 `day` smallint(6) default NULL,
1645 `month` smallint(6) default NULL,
1646 `title` varchar(50) NOT NULL default '',
1647 `description` text NOT NULL,
1649 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1652 -- Table structure for table `reports_dictionary`
1655 DROP TABLE IF EXISTS `reports_dictionary`;
1656 CREATE TABLE reports_dictionary (
1657 `id` int(11) NOT NULL auto_increment,
1658 `name` varchar(255) default NULL,
1660 `date_created` datetime default NULL,
1661 `date_modified` datetime default NULL,
1663 `area` int(11) default NULL,
1665 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1668 -- Table structure for table `reserveconstraints`
1671 DROP TABLE IF EXISTS `reserveconstraints`;
1672 CREATE TABLE `reserveconstraints` (
1673 `borrowernumber` int(11) NOT NULL default 0,
1674 `reservedate` date default NULL,
1675 `biblionumber` int(11) NOT NULL default 0,
1676 `biblioitemnumber` int(11) default NULL,
1677 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1678 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1681 -- Table structure for table `reserves`
1684 DROP TABLE IF EXISTS `reserves`;
1685 CREATE TABLE `reserves` (
1686 `borrowernumber` int(11) NOT NULL default 0,
1687 `reservedate` date default NULL,
1688 `biblionumber` int(11) NOT NULL default 0,
1689 `constrainttype` varchar(1) default NULL,
1690 `branchcode` varchar(10) default NULL,
1691 `notificationdate` date default NULL,
1692 `reminderdate` date default NULL,
1693 `cancellationdate` date default NULL,
1694 `reservenotes` mediumtext,
1695 `priority` smallint(6) default NULL,
1696 `found` varchar(1) default NULL,
1697 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1698 `itemnumber` int(11) default NULL,
1699 `waitingdate` date default NULL,
1700 KEY `borrowernumber` (`borrowernumber`),
1701 KEY `biblionumber` (`biblionumber`),
1702 KEY `itemnumber` (`itemnumber`),
1703 KEY `branchcode` (`branchcode`),
1704 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1705 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1706 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1707 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1708 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1711 -- Table structure for table `reviews`
1714 DROP TABLE IF EXISTS `reviews`;
1715 CREATE TABLE `reviews` (
1716 `reviewid` int(11) NOT NULL auto_increment,
1717 `borrowernumber` int(11) default NULL,
1718 `biblionumber` int(11) default NULL,
1720 `approved` tinyint(4) default NULL,
1721 `datereviewed` datetime default NULL,
1722 PRIMARY KEY (`reviewid`)
1723 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1726 -- Table structure for table `roadtype`
1729 DROP TABLE IF EXISTS `roadtype`;
1730 CREATE TABLE `roadtype` (
1731 `roadtypeid` int(11) NOT NULL auto_increment,
1732 `road_type` varchar(100) NOT NULL default '',
1733 PRIMARY KEY (`roadtypeid`)
1734 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1737 -- Table structure for table `saved_sql`
1740 DROP TABLE IF EXISTS `saved_sql`;
1741 CREATE TABLE saved_sql (
1742 `id` int(11) NOT NULL auto_increment,
1743 `borrowernumber` int(11) default NULL,
1744 `date_created` datetime default NULL,
1745 `last_modified` datetime default NULL,
1747 `last_run` datetime default NULL,
1748 `report_name` varchar(255) default NULL,
1749 `type` varchar(255) default NULL,
1752 KEY boridx (`borrowernumber`)
1753 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1757 -- Table structure for `saved_reports`
1760 DROP TABLE IF EXISTS `saved_reports`;
1761 CREATE TABLE saved_reports (
1762 `id` int(11) NOT NULL auto_increment,
1763 `report_id` int(11) default NULL,
1765 `date_run` datetime default NULL,
1767 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1771 -- Table structure for table `serial`
1774 DROP TABLE IF EXISTS `serial`;
1775 CREATE TABLE `serial` (
1776 `serialid` int(11) NOT NULL auto_increment,
1777 `biblionumber` varchar(100) NOT NULL default '',
1778 `subscriptionid` varchar(100) NOT NULL default '',
1779 `serialseq` varchar(100) NOT NULL default '',
1780 `status` tinyint(4) NOT NULL default 0,
1781 `planneddate` date default NULL,
1783 `publisheddate` date default NULL,
1784 `itemnumber` text default NULL,
1785 `claimdate` date default NULL,
1786 `routingnotes` text,
1787 PRIMARY KEY (`serialid`)
1788 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1791 -- Table structure for table `sessions`
1794 DROP TABLE IF EXISTS sessions;
1795 CREATE TABLE sessions (
1796 `id` varchar(32) NOT NULL,
1797 `a_session` text NOT NULL,
1799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1802 -- Table structure for table `special_holidays`
1805 DROP TABLE IF EXISTS `special_holidays`;
1806 CREATE TABLE `special_holidays` (
1807 `id` int(11) NOT NULL auto_increment,
1808 `branchcode` varchar(10) NOT NULL default '',
1809 `day` smallint(6) NOT NULL default 0,
1810 `month` smallint(6) NOT NULL default 0,
1811 `year` smallint(6) NOT NULL default 0,
1812 `isexception` smallint(1) NOT NULL default 1,
1813 `title` varchar(50) NOT NULL default '',
1814 `description` text NOT NULL,
1816 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1819 -- Table structure for table `statistics`
1822 DROP TABLE IF EXISTS `statistics`;
1823 CREATE TABLE `statistics` (
1824 `datetime` datetime default NULL,
1825 `branch` varchar(10) default NULL,
1826 `proccode` varchar(4) default NULL,
1827 `value` double(16,4) default NULL,
1828 `type` varchar(16) default NULL,
1830 `usercode` varchar(10) default NULL,
1831 `itemnumber` int(11) default NULL,
1832 `itemtype` varchar(10) default NULL,
1833 `borrowernumber` int(11) default NULL,
1834 `associatedborrower` int(11) default NULL,
1835 KEY `timeidx` (`datetime`)
1836 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1839 -- Table structure for table `stopwords`
1842 DROP TABLE IF EXISTS `stopwords`;
1843 CREATE TABLE `stopwords` (
1844 `word` varchar(255) default NULL
1845 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1848 -- Table structure for table `subscription`
1851 DROP TABLE IF EXISTS `subscription`;
1852 CREATE TABLE `subscription` (
1853 `biblionumber` int(11) NOT NULL default 0,
1854 `subscriptionid` int(11) NOT NULL auto_increment,
1855 `librarian` varchar(100) default '',
1856 `startdate` date default NULL,
1857 `aqbooksellerid` int(11) default 0,
1858 `cost` int(11) default 0,
1859 `aqbudgetid` int(11) default 0,
1860 `weeklength` int(11) default 0,
1861 `monthlength` int(11) default 0,
1862 `numberlength` int(11) default 0,
1863 `periodicity` tinyint(4) default 0,
1864 `dow` varchar(100) default '',
1865 `numberingmethod` varchar(100) default '',
1867 `status` varchar(100) NOT NULL default '',
1868 `add1` int(11) default 0,
1869 `every1` int(11) default 0,
1870 `whenmorethan1` int(11) default 0,
1871 `setto1` int(11) default NULL,
1872 `lastvalue1` int(11) default NULL,
1873 `add2` int(11) default 0,
1874 `every2` int(11) default 0,
1875 `whenmorethan2` int(11) default 0,
1876 `setto2` int(11) default NULL,
1877 `lastvalue2` int(11) default NULL,
1878 `add3` int(11) default 0,
1879 `every3` int(11) default 0,
1880 `innerloop1` int(11) default 0,
1881 `innerloop2` int(11) default 0,
1882 `innerloop3` int(11) default 0,
1883 `whenmorethan3` int(11) default 0,
1884 `setto3` int(11) default NULL,
1885 `lastvalue3` int(11) default NULL,
1886 `issuesatonce` tinyint(3) NOT NULL default 1,
1887 `firstacquidate` date default NULL,
1888 `manualhistory` tinyint(1) NOT NULL default 0,
1889 `irregularity` text,
1890 `letter` varchar(20) default NULL,
1891 `numberpattern` tinyint(3) default 0,
1892 `distributedto` text,
1893 `internalnotes` longtext,
1895 `branchcode` varchar(10) NOT NULL default '',
1896 `hemisphere` tinyint(3) default 0,
1897 `lastbranch` varchar(10),
1898 `serialsadditems` tinyint(1) NOT NULL default '0',
1899 `staffdisplaycount` VARCHAR(10) NULL,
1900 `opacdisplaycount` VARCHAR(10) NULL,
1901 `graceperiod` int(11) NOT NULL default '0',
1902 PRIMARY KEY (`subscriptionid`)
1903 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1906 -- Table structure for table `subscriptionhistory`
1909 DROP TABLE IF EXISTS `subscriptionhistory`;
1910 CREATE TABLE `subscriptionhistory` (
1911 `biblionumber` int(11) NOT NULL default 0,
1912 `subscriptionid` int(11) NOT NULL default 0,
1913 `histstartdate` date default NULL,
1914 `enddate` date default NULL,
1915 `missinglist` longtext NOT NULL,
1916 `recievedlist` longtext NOT NULL,
1917 `opacnote` varchar(150) NOT NULL default '',
1918 `librariannote` varchar(150) NOT NULL default '',
1919 PRIMARY KEY (`subscriptionid`),
1920 KEY `biblionumber` (`biblionumber`)
1921 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1924 -- Table structure for table `subscriptionroutinglist`
1927 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1928 CREATE TABLE `subscriptionroutinglist` (
1929 `routingid` int(11) NOT NULL auto_increment,
1930 `borrowernumber` int(11) default NULL,
1931 `ranking` int(11) default NULL,
1932 `subscriptionid` int(11) default NULL,
1933 PRIMARY KEY (`routingid`)
1934 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1937 -- Table structure for table `suggestions`
1940 DROP TABLE IF EXISTS `suggestions`;
1941 CREATE TABLE `suggestions` (
1942 `suggestionid` int(8) NOT NULL auto_increment,
1943 `suggestedby` int(11) NOT NULL default 0,
1944 `managedby` int(11) default NULL,
1945 `STATUS` varchar(10) NOT NULL default '',
1947 `author` varchar(80) default NULL,
1948 `title` varchar(80) default NULL,
1949 `copyrightdate` smallint(6) default NULL,
1950 `publishercode` varchar(255) default NULL,
1951 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1952 `volumedesc` varchar(255) default NULL,
1953 `publicationyear` smallint(6) default 0,
1954 `place` varchar(255) default NULL,
1955 `isbn` varchar(30) default NULL,
1956 `mailoverseeing` smallint(1) default 0,
1957 `biblionumber` int(11) default NULL,
1959 PRIMARY KEY (`suggestionid`),
1960 KEY `suggestedby` (`suggestedby`),
1961 KEY `managedby` (`managedby`)
1962 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1965 -- Table structure for table `systempreferences`
1968 DROP TABLE IF EXISTS `systempreferences`;
1969 CREATE TABLE `systempreferences` (
1970 `variable` varchar(50) NOT NULL default '',
1972 `options` mediumtext,
1974 `type` varchar(20) default NULL,
1975 PRIMARY KEY (`variable`)
1976 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1979 -- Table structure for table `tags`
1982 DROP TABLE IF EXISTS `tags`;
1983 CREATE TABLE `tags` (
1984 `entry` varchar(255) NOT NULL default '',
1985 `weight` bigint(20) NOT NULL default 0,
1986 PRIMARY KEY (`entry`)
1987 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1990 -- Table structure for table `tags_all`
1993 DROP TABLE IF EXISTS `tags_all`;
1994 CREATE TABLE `tags_all` (
1995 `tag_id` int(11) NOT NULL auto_increment,
1996 `borrowernumber` int(11) NOT NULL,
1997 `biblionumber` int(11) NOT NULL,
1998 `term` varchar(255) NOT NULL,
1999 `language` int(4) default NULL,
2000 `date_created` datetime NOT NULL,
2001 PRIMARY KEY (`tag_id`),
2002 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2003 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2004 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2005 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2006 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2007 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2008 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2011 -- Table structure for table `tags_approval`
2014 DROP TABLE IF EXISTS `tags_approval`;
2015 CREATE TABLE `tags_approval` (
2016 `term` varchar(255) NOT NULL,
2017 `approved` int(1) NOT NULL default '0',
2018 `date_approved` datetime default NULL,
2019 `approved_by` int(11) default NULL,
2020 `weight_total` int(9) NOT NULL default '1',
2021 PRIMARY KEY (`term`),
2022 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2023 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2024 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2028 -- Table structure for table `tags_index`
2031 DROP TABLE IF EXISTS `tags_index`;
2032 CREATE TABLE `tags_index` (
2033 `term` varchar(255) NOT NULL,
2034 `biblionumber` int(11) NOT NULL,
2035 `weight` int(9) NOT NULL default '1',
2036 PRIMARY KEY (`term`,`biblionumber`),
2037 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2038 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2039 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2040 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2041 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2042 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2045 -- Table structure for table `userflags`
2048 DROP TABLE IF EXISTS `userflags`;
2049 CREATE TABLE `userflags` (
2050 `bit` int(11) NOT NULL default 0,
2051 `flag` varchar(30) default NULL,
2052 `flagdesc` varchar(255) default NULL,
2053 `defaulton` int(11) default NULL,
2055 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2058 -- Table structure for table `virtualshelves`
2061 DROP TABLE IF EXISTS `virtualshelves`;
2062 CREATE TABLE `virtualshelves` (
2063 `shelfnumber` int(11) NOT NULL auto_increment,
2064 `shelfname` varchar(255) default NULL,
2065 `owner` varchar(80) default NULL,
2066 `category` varchar(1) default NULL,
2067 `sortfield` varchar(16) default NULL,
2068 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2069 PRIMARY KEY (`shelfnumber`)
2070 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2073 -- Table structure for table `virtualshelfcontents`
2076 DROP TABLE IF EXISTS `virtualshelfcontents`;
2077 CREATE TABLE `virtualshelfcontents` (
2078 `shelfnumber` int(11) NOT NULL default 0,
2079 `biblionumber` int(11) NOT NULL default 0,
2080 `flags` int(11) default NULL,
2081 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2082 KEY `shelfnumber` (`shelfnumber`),
2083 KEY `biblionumber` (`biblionumber`),
2084 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2085 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2086 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2089 -- Table structure for table `z3950servers`
2092 DROP TABLE IF EXISTS `z3950servers`;
2093 CREATE TABLE `z3950servers` (
2094 `host` varchar(255) default NULL,
2095 `port` int(11) default NULL,
2096 `db` varchar(255) default NULL,
2097 `userid` varchar(255) default NULL,
2098 `password` varchar(255) default NULL,
2100 `id` int(11) NOT NULL auto_increment,
2101 `checked` smallint(6) default NULL,
2102 `rank` int(11) default NULL,
2103 `syntax` varchar(80) default NULL,
2105 `position` enum('primary','secondary','') NOT NULL default 'primary',
2106 `type` enum('zed','opensearch') NOT NULL default 'zed',
2107 `encoding` text default NULL,
2108 `description` text NOT NULL,
2110 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2113 -- Table structure for table `zebraqueue`
2116 DROP TABLE IF EXISTS `zebraqueue`;
2117 CREATE TABLE `zebraqueue` (
2118 `id` int(11) NOT NULL auto_increment,
2119 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2120 `operation` char(20) NOT NULL default '',
2121 `server` char(20) NOT NULL default '',
2122 `done` int(11) NOT NULL default '0',
2123 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2125 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2126 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2128 DROP TABLE IF EXISTS `services_throttle`;
2129 CREATE TABLE `services_throttle` (
2130 `service_type` varchar(10) NOT NULL default '',
2131 `service_count` varchar(45) default NULL,
2132 PRIMARY KEY (`service_type`)
2133 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2135 -- http://www.w3.org/International/articles/language-tags/
2138 DROP TABLE IF EXISTS language_subtag_registry;
2139 CREATE TABLE language_subtag_registry (
2141 type varchar(25), -- language-script-region-variant-extension-privateuse
2142 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2144 KEY `subtag` (`subtag`)
2145 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2147 -- TODO: add suppress_scripts
2148 -- this maps three letter codes defined in iso639.2 back to their
2149 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2150 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2151 CREATE TABLE language_rfc4646_to_iso639 (
2152 rfc4646_subtag varchar(25),
2153 iso639_2_code varchar(25),
2154 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2155 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2157 DROP TABLE IF EXISTS language_descriptions;
2158 CREATE TABLE language_descriptions (
2162 description varchar(255),
2164 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2166 -- bi-directional support, keyed by script subcode
2167 DROP TABLE IF EXISTS language_script_bidi;
2168 CREATE TABLE language_script_bidi (
2169 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2170 bidi varchar(3), -- rtl ltr
2171 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2172 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2174 -- TODO: need to map language subtags to script subtags for detection
2175 -- of bidi when script is not specified (like ar, he)
2176 DROP TABLE IF EXISTS language_script_mapping;
2177 CREATE TABLE language_script_mapping (
2178 language_subtag varchar(25),
2179 script_subtag varchar(25),
2180 KEY `language_subtag` (`language_subtag`)
2181 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2183 DROP TABLE IF EXISTS `permissions`;
2184 CREATE TABLE `permissions` (
2185 `module_bit` int(11) NOT NULL DEFAULT 0,
2186 `code` varchar(64) DEFAULT NULL,
2187 `description` varchar(255) DEFAULT NULL,
2188 PRIMARY KEY (`module_bit`, `code`),
2189 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2190 ON DELETE CASCADE ON UPDATE CASCADE
2191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2193 DROP TABLE IF EXISTS `serialitems`;
2194 CREATE TABLE `serialitems` (
2195 `itemnumber` int(11) NOT NULL,
2196 `serialid` int(11) NOT NULL,
2197 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2198 KEY `serialitems_sfk_1` (`serialid`),
2199 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2202 DROP TABLE IF EXISTS `user_permissions`;
2203 CREATE TABLE `user_permissions` (
2204 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2205 `module_bit` int(11) NOT NULL DEFAULT 0,
2206 `code` varchar(64) DEFAULT NULL,
2207 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2208 ON DELETE CASCADE ON UPDATE CASCADE,
2209 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2210 ON DELETE CASCADE ON UPDATE CASCADE
2211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2214 -- Table structure for table `tmp_holdsqueue`
2217 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2218 CREATE TABLE `tmp_holdsqueue` (
2219 `biblionumber` int(11) default NULL,
2220 `itemnumber` int(11) default NULL,
2221 `barcode` varchar(20) default NULL,
2222 `surname` mediumtext NOT NULL,
2225 `borrowernumber` int(11) NOT NULL,
2226 `cardnumber` varchar(16) default NULL,
2227 `reservedate` date default NULL,
2229 `itemcallnumber` varchar(30) default NULL,
2230 `holdingbranch` varchar(10) default NULL,
2231 `pickbranch` varchar(10) default NULL,
2233 `item_level_request` tinyint(4) NOT NULL default 0
2234 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2237 -- Table structure for table `message_queue`
2240 DROP TABLE IF EXISTS `message_queue`;
2241 CREATE TABLE `message_queue` (
2242 `message_id` int(11) NOT NULL auto_increment,
2243 `borrowernumber` int(11) default NULL,
2246 `metadata` text DEFAULT NULL,
2247 `letter_code` varchar(64) DEFAULT NULL,
2248 `message_transport_type` varchar(20) NOT NULL,
2249 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2250 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2251 `to_address` mediumtext,
2252 `from_address` mediumtext,
2253 `content_type` text,
2254 KEY `message_id` (`message_id`),
2255 KEY `borrowernumber` (`borrowernumber`),
2256 KEY `message_transport_type` (`message_transport_type`),
2257 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2258 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2262 -- Table structure for table `message_transport_types`
2265 DROP TABLE IF EXISTS `message_transport_types`;
2266 CREATE TABLE `message_transport_types` (
2267 `message_transport_type` varchar(20) NOT NULL,
2268 PRIMARY KEY (`message_transport_type`)
2269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2272 -- Table structure for table `message_attributes`
2275 DROP TABLE IF EXISTS `message_attributes`;
2276 CREATE TABLE `message_attributes` (
2277 `message_attribute_id` int(11) NOT NULL auto_increment,
2278 `message_name` varchar(20) NOT NULL default '',
2279 `takes_days` tinyint(1) NOT NULL default '0',
2280 PRIMARY KEY (`message_attribute_id`),
2281 UNIQUE KEY `message_name` (`message_name`)
2282 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2285 -- Table structure for table `message_transports`
2288 DROP TABLE IF EXISTS `message_transports`;
2289 CREATE TABLE `message_transports` (
2290 `message_attribute_id` int(11) NOT NULL,
2291 `message_transport_type` varchar(20) NOT NULL,
2292 `is_digest` tinyint(1) NOT NULL default '0',
2293 `letter_module` varchar(20) NOT NULL default '',
2294 `letter_code` varchar(20) NOT NULL default '',
2295 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2296 KEY `message_transport_type` (`message_transport_type`),
2297 KEY `letter_module` (`letter_module`,`letter_code`),
2298 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2299 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2300 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2301 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2304 -- Table structure for table `borrower_message_preferences`
2307 DROP TABLE IF EXISTS `borrower_message_preferences`;
2308 CREATE TABLE `borrower_message_preferences` (
2309 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2310 `borrowernumber` int(11) default NULL,
2311 `categorycode` varchar(10) default NULL,
2312 `message_attribute_id` int(11) default '0',
2313 `days_in_advance` int(11) default '0',
2314 `wants_digest` tinyint(1) NOT NULL default '0',
2315 PRIMARY KEY (`borrower_message_preference_id`),
2316 KEY `borrowernumber` (`borrowernumber`),
2317 KEY `categorycode` (`categorycode`),
2318 KEY `message_attribute_id` (`message_attribute_id`),
2319 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2320 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2321 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2322 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2325 -- Table structure for table `borrower_message_transport_preferences`
2328 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2329 CREATE TABLE `borrower_message_transport_preferences` (
2330 `borrower_message_preference_id` int(11) NOT NULL default '0',
2331 `message_transport_type` varchar(20) NOT NULL default '0',
2332 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2333 KEY `message_transport_type` (`message_transport_type`),
2334 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,
2335 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
2336 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2339 -- Table structure for the table branch_transfer_limits
2342 DROP TABLE IF EXISTS `branch_transfer_limits`;
2343 CREATE TABLE branch_transfer_limits (
2344 limitId int(8) NOT NULL auto_increment,
2345 toBranch varchar(10) NOT NULL,
2346 fromBranch varchar(10) NOT NULL,
2347 itemtype varchar(10) NULL,
2348 ccode varchar(10) NULL,
2349 PRIMARY KEY (limitId)
2350 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2353 -- Table structure for table `item_circulation_alert_preferences`
2356 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2357 CREATE TABLE `item_circulation_alert_preferences` (
2358 `id` int(11) NOT NULL auto_increment,
2359 `branchcode` varchar(10) NOT NULL,
2360 `categorycode` varchar(10) NOT NULL,
2361 `item_type` varchar(10) NOT NULL,
2362 `notification` varchar(16) NOT NULL,
2364 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2367 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2368 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2369 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2370 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2371 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2372 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2373 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2374 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;