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 `location` varchar(80) NULL default '',
1896 `branchcode` varchar(10) NOT NULL default '',
1897 `hemisphere` tinyint(3) default 0,
1898 `lastbranch` varchar(10),
1899 `serialsadditems` tinyint(1) NOT NULL default '0',
1900 `staffdisplaycount` VARCHAR(10) NULL,
1901 `opacdisplaycount` VARCHAR(10) NULL,
1902 `graceperiod` int(11) NOT NULL default '0',
1903 PRIMARY KEY (`subscriptionid`)
1904 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1907 -- Table structure for table `subscriptionhistory`
1910 DROP TABLE IF EXISTS `subscriptionhistory`;
1911 CREATE TABLE `subscriptionhistory` (
1912 `biblionumber` int(11) NOT NULL default 0,
1913 `subscriptionid` int(11) NOT NULL default 0,
1914 `histstartdate` date default NULL,
1915 `enddate` date default NULL,
1916 `missinglist` longtext NOT NULL,
1917 `recievedlist` longtext NOT NULL,
1918 `opacnote` varchar(150) NOT NULL default '',
1919 `librariannote` varchar(150) NOT NULL default '',
1920 PRIMARY KEY (`subscriptionid`),
1921 KEY `biblionumber` (`biblionumber`)
1922 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1925 -- Table structure for table `subscriptionroutinglist`
1928 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1929 CREATE TABLE `subscriptionroutinglist` (
1930 `routingid` int(11) NOT NULL auto_increment,
1931 `borrowernumber` int(11) default NULL,
1932 `ranking` int(11) default NULL,
1933 `subscriptionid` int(11) default NULL,
1934 PRIMARY KEY (`routingid`)
1935 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1938 -- Table structure for table `suggestions`
1941 DROP TABLE IF EXISTS `suggestions`;
1942 CREATE TABLE `suggestions` (
1943 `suggestionid` int(8) NOT NULL auto_increment,
1944 `suggestedby` int(11) NOT NULL default 0,
1945 `managedby` int(11) default NULL,
1946 `STATUS` varchar(10) NOT NULL default '',
1948 `author` varchar(80) default NULL,
1949 `title` varchar(80) default NULL,
1950 `copyrightdate` smallint(6) default NULL,
1951 `publishercode` varchar(255) default NULL,
1952 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1953 `volumedesc` varchar(255) default NULL,
1954 `publicationyear` smallint(6) default 0,
1955 `place` varchar(255) default NULL,
1956 `isbn` varchar(30) default NULL,
1957 `mailoverseeing` smallint(1) default 0,
1958 `biblionumber` int(11) default NULL,
1960 PRIMARY KEY (`suggestionid`),
1961 KEY `suggestedby` (`suggestedby`),
1962 KEY `managedby` (`managedby`)
1963 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1966 -- Table structure for table `systempreferences`
1969 DROP TABLE IF EXISTS `systempreferences`;
1970 CREATE TABLE `systempreferences` (
1971 `variable` varchar(50) NOT NULL default '',
1973 `options` mediumtext,
1975 `type` varchar(20) default NULL,
1976 PRIMARY KEY (`variable`)
1977 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1980 -- Table structure for table `tags`
1983 DROP TABLE IF EXISTS `tags`;
1984 CREATE TABLE `tags` (
1985 `entry` varchar(255) NOT NULL default '',
1986 `weight` bigint(20) NOT NULL default 0,
1987 PRIMARY KEY (`entry`)
1988 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1991 -- Table structure for table `tags_all`
1994 DROP TABLE IF EXISTS `tags_all`;
1995 CREATE TABLE `tags_all` (
1996 `tag_id` int(11) NOT NULL auto_increment,
1997 `borrowernumber` int(11) NOT NULL,
1998 `biblionumber` int(11) NOT NULL,
1999 `term` varchar(255) NOT NULL,
2000 `language` int(4) default NULL,
2001 `date_created` datetime NOT NULL,
2002 PRIMARY KEY (`tag_id`),
2003 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2004 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2005 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2006 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2007 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2008 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2009 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2012 -- Table structure for table `tags_approval`
2015 DROP TABLE IF EXISTS `tags_approval`;
2016 CREATE TABLE `tags_approval` (
2017 `term` varchar(255) NOT NULL,
2018 `approved` int(1) NOT NULL default '0',
2019 `date_approved` datetime default NULL,
2020 `approved_by` int(11) default NULL,
2021 `weight_total` int(9) NOT NULL default '1',
2022 PRIMARY KEY (`term`),
2023 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2024 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2025 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2026 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2029 -- Table structure for table `tags_index`
2032 DROP TABLE IF EXISTS `tags_index`;
2033 CREATE TABLE `tags_index` (
2034 `term` varchar(255) NOT NULL,
2035 `biblionumber` int(11) NOT NULL,
2036 `weight` int(9) NOT NULL default '1',
2037 PRIMARY KEY (`term`,`biblionumber`),
2038 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2039 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2040 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2041 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2042 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2043 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2046 -- Table structure for table `userflags`
2049 DROP TABLE IF EXISTS `userflags`;
2050 CREATE TABLE `userflags` (
2051 `bit` int(11) NOT NULL default 0,
2052 `flag` varchar(30) default NULL,
2053 `flagdesc` varchar(255) default NULL,
2054 `defaulton` int(11) default NULL,
2056 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2059 -- Table structure for table `virtualshelves`
2062 DROP TABLE IF EXISTS `virtualshelves`;
2063 CREATE TABLE `virtualshelves` (
2064 `shelfnumber` int(11) NOT NULL auto_increment,
2065 `shelfname` varchar(255) default NULL,
2066 `owner` varchar(80) default NULL,
2067 `category` varchar(1) default NULL,
2068 `sortfield` varchar(16) default NULL,
2069 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2070 PRIMARY KEY (`shelfnumber`)
2071 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2074 -- Table structure for table `virtualshelfcontents`
2077 DROP TABLE IF EXISTS `virtualshelfcontents`;
2078 CREATE TABLE `virtualshelfcontents` (
2079 `shelfnumber` int(11) NOT NULL default 0,
2080 `biblionumber` int(11) NOT NULL default 0,
2081 `flags` int(11) default NULL,
2082 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2083 KEY `shelfnumber` (`shelfnumber`),
2084 KEY `biblionumber` (`biblionumber`),
2085 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2086 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2087 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2090 -- Table structure for table `z3950servers`
2093 DROP TABLE IF EXISTS `z3950servers`;
2094 CREATE TABLE `z3950servers` (
2095 `host` varchar(255) default NULL,
2096 `port` int(11) default NULL,
2097 `db` varchar(255) default NULL,
2098 `userid` varchar(255) default NULL,
2099 `password` varchar(255) default NULL,
2101 `id` int(11) NOT NULL auto_increment,
2102 `checked` smallint(6) default NULL,
2103 `rank` int(11) default NULL,
2104 `syntax` varchar(80) default NULL,
2106 `position` enum('primary','secondary','') NOT NULL default 'primary',
2107 `type` enum('zed','opensearch') NOT NULL default 'zed',
2108 `encoding` text default NULL,
2109 `description` text NOT NULL,
2111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2114 -- Table structure for table `zebraqueue`
2117 DROP TABLE IF EXISTS `zebraqueue`;
2118 CREATE TABLE `zebraqueue` (
2119 `id` int(11) NOT NULL auto_increment,
2120 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2121 `operation` char(20) NOT NULL default '',
2122 `server` char(20) NOT NULL default '',
2123 `done` int(11) NOT NULL default '0',
2124 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2126 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2127 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2129 DROP TABLE IF EXISTS `services_throttle`;
2130 CREATE TABLE `services_throttle` (
2131 `service_type` varchar(10) NOT NULL default '',
2132 `service_count` varchar(45) default NULL,
2133 PRIMARY KEY (`service_type`)
2134 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2136 -- http://www.w3.org/International/articles/language-tags/
2139 DROP TABLE IF EXISTS language_subtag_registry;
2140 CREATE TABLE language_subtag_registry (
2142 type varchar(25), -- language-script-region-variant-extension-privateuse
2143 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2145 KEY `subtag` (`subtag`)
2146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2148 -- TODO: add suppress_scripts
2149 -- this maps three letter codes defined in iso639.2 back to their
2150 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2151 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2152 CREATE TABLE language_rfc4646_to_iso639 (
2153 rfc4646_subtag varchar(25),
2154 iso639_2_code varchar(25),
2155 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2158 DROP TABLE IF EXISTS language_descriptions;
2159 CREATE TABLE language_descriptions (
2163 description varchar(255),
2165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2167 -- bi-directional support, keyed by script subcode
2168 DROP TABLE IF EXISTS language_script_bidi;
2169 CREATE TABLE language_script_bidi (
2170 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2171 bidi varchar(3), -- rtl ltr
2172 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2173 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2175 -- TODO: need to map language subtags to script subtags for detection
2176 -- of bidi when script is not specified (like ar, he)
2177 DROP TABLE IF EXISTS language_script_mapping;
2178 CREATE TABLE language_script_mapping (
2179 language_subtag varchar(25),
2180 script_subtag varchar(25),
2181 KEY `language_subtag` (`language_subtag`)
2182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2184 DROP TABLE IF EXISTS `permissions`;
2185 CREATE TABLE `permissions` (
2186 `module_bit` int(11) NOT NULL DEFAULT 0,
2187 `code` varchar(64) DEFAULT NULL,
2188 `description` varchar(255) DEFAULT NULL,
2189 PRIMARY KEY (`module_bit`, `code`),
2190 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2191 ON DELETE CASCADE ON UPDATE CASCADE
2192 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2194 DROP TABLE IF EXISTS `serialitems`;
2195 CREATE TABLE `serialitems` (
2196 `itemnumber` int(11) NOT NULL,
2197 `serialid` int(11) NOT NULL,
2198 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2199 KEY `serialitems_sfk_1` (`serialid`),
2200 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2201 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2203 DROP TABLE IF EXISTS `user_permissions`;
2204 CREATE TABLE `user_permissions` (
2205 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2206 `module_bit` int(11) NOT NULL DEFAULT 0,
2207 `code` varchar(64) DEFAULT NULL,
2208 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2209 ON DELETE CASCADE ON UPDATE CASCADE,
2210 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2211 ON DELETE CASCADE ON UPDATE CASCADE
2212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2215 -- Table structure for table `tmp_holdsqueue`
2218 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2219 CREATE TABLE `tmp_holdsqueue` (
2220 `biblionumber` int(11) default NULL,
2221 `itemnumber` int(11) default NULL,
2222 `barcode` varchar(20) default NULL,
2223 `surname` mediumtext NOT NULL,
2226 `borrowernumber` int(11) NOT NULL,
2227 `cardnumber` varchar(16) default NULL,
2228 `reservedate` date default NULL,
2230 `itemcallnumber` varchar(30) default NULL,
2231 `holdingbranch` varchar(10) default NULL,
2232 `pickbranch` varchar(10) default NULL,
2234 `item_level_request` tinyint(4) NOT NULL default 0
2235 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2238 -- Table structure for table `message_queue`
2241 DROP TABLE IF EXISTS `message_queue`;
2242 CREATE TABLE `message_queue` (
2243 `message_id` int(11) NOT NULL auto_increment,
2244 `borrowernumber` int(11) default NULL,
2247 `metadata` text DEFAULT NULL,
2248 `letter_code` varchar(64) DEFAULT NULL,
2249 `message_transport_type` varchar(20) NOT NULL,
2250 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2251 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2252 `to_address` mediumtext,
2253 `from_address` mediumtext,
2254 `content_type` text,
2255 KEY `message_id` (`message_id`),
2256 KEY `borrowernumber` (`borrowernumber`),
2257 KEY `message_transport_type` (`message_transport_type`),
2258 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2259 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2263 -- Table structure for table `message_transport_types`
2266 DROP TABLE IF EXISTS `message_transport_types`;
2267 CREATE TABLE `message_transport_types` (
2268 `message_transport_type` varchar(20) NOT NULL,
2269 PRIMARY KEY (`message_transport_type`)
2270 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2273 -- Table structure for table `message_attributes`
2276 DROP TABLE IF EXISTS `message_attributes`;
2277 CREATE TABLE `message_attributes` (
2278 `message_attribute_id` int(11) NOT NULL auto_increment,
2279 `message_name` varchar(20) NOT NULL default '',
2280 `takes_days` tinyint(1) NOT NULL default '0',
2281 PRIMARY KEY (`message_attribute_id`),
2282 UNIQUE KEY `message_name` (`message_name`)
2283 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2286 -- Table structure for table `message_transports`
2289 DROP TABLE IF EXISTS `message_transports`;
2290 CREATE TABLE `message_transports` (
2291 `message_attribute_id` int(11) NOT NULL,
2292 `message_transport_type` varchar(20) NOT NULL,
2293 `is_digest` tinyint(1) NOT NULL default '0',
2294 `letter_module` varchar(20) NOT NULL default '',
2295 `letter_code` varchar(20) NOT NULL default '',
2296 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2297 KEY `message_transport_type` (`message_transport_type`),
2298 KEY `letter_module` (`letter_module`,`letter_code`),
2299 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2300 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2301 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2302 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2305 -- Table structure for table `borrower_message_preferences`
2308 DROP TABLE IF EXISTS `borrower_message_preferences`;
2309 CREATE TABLE `borrower_message_preferences` (
2310 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2311 `borrowernumber` int(11) default NULL,
2312 `categorycode` varchar(10) default NULL,
2313 `message_attribute_id` int(11) default '0',
2314 `days_in_advance` int(11) default '0',
2315 `wants_digest` tinyint(1) NOT NULL default '0',
2316 PRIMARY KEY (`borrower_message_preference_id`),
2317 KEY `borrowernumber` (`borrowernumber`),
2318 KEY `categorycode` (`categorycode`),
2319 KEY `message_attribute_id` (`message_attribute_id`),
2320 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2321 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2322 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2323 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2326 -- Table structure for table `borrower_message_transport_preferences`
2329 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2330 CREATE TABLE `borrower_message_transport_preferences` (
2331 `borrower_message_preference_id` int(11) NOT NULL default '0',
2332 `message_transport_type` varchar(20) NOT NULL default '0',
2333 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2334 KEY `message_transport_type` (`message_transport_type`),
2335 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,
2336 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
2337 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2340 -- Table structure for the table branch_transfer_limits
2343 DROP TABLE IF EXISTS `branch_transfer_limits`;
2344 CREATE TABLE branch_transfer_limits (
2345 limitId int(8) NOT NULL auto_increment,
2346 toBranch varchar(10) NOT NULL,
2347 fromBranch varchar(10) NOT NULL,
2348 itemtype varchar(10) NULL,
2349 ccode varchar(10) NULL,
2350 PRIMARY KEY (limitId)
2351 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2354 -- Table structure for table `item_circulation_alert_preferences`
2357 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2358 CREATE TABLE `item_circulation_alert_preferences` (
2359 `id` int(11) NOT NULL auto_increment,
2360 `branchcode` varchar(10) NOT NULL,
2361 `categorycode` varchar(10) NOT NULL,
2362 `item_type` varchar(10) NOT NULL,
2363 `notification` varchar(16) NOT NULL,
2365 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2366 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2368 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2369 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2370 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2371 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2372 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2373 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2374 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2375 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;