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`),
346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
349 -- Table structure for table `biblio`
352 DROP TABLE IF EXISTS `biblio`;
353 CREATE TABLE `biblio` (
354 `biblionumber` int(11) NOT NULL auto_increment,
355 `frameworkcode` varchar(4) NOT NULL default '',
358 `unititle` mediumtext,
360 `serial` tinyint(1) default NULL,
361 `seriestitle` mediumtext,
362 `copyrightdate` smallint(6) default NULL,
363 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
364 `datecreated` DATE NOT NULL,
365 `abstract` mediumtext,
366 PRIMARY KEY (`biblionumber`),
367 KEY `blbnoidx` (`biblionumber`)
368 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
371 -- Table structure for table `biblio_framework`
374 DROP TABLE IF EXISTS `biblio_framework`;
375 CREATE TABLE `biblio_framework` (
376 `frameworkcode` varchar(4) NOT NULL default '',
377 `frameworktext` varchar(255) NOT NULL default '',
378 PRIMARY KEY (`frameworkcode`)
379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
382 -- Table structure for table `biblioitems`
385 DROP TABLE IF EXISTS `biblioitems`;
386 CREATE TABLE `biblioitems` (
387 `biblioitemnumber` int(11) NOT NULL auto_increment,
388 `biblionumber` int(11) NOT NULL default 0,
391 `itemtype` varchar(10) default NULL,
392 `isbn` varchar(30) default NULL,
393 `issn` varchar(9) default NULL,
394 `publicationyear` text,
395 `publishercode` varchar(255) default NULL,
396 `volumedate` date default NULL,
398 `collectiontitle` mediumtext default NULL,
399 `collectionissn` text default NULL,
400 `collectionvolume` mediumtext default NULL,
401 `editionstatement` text default NULL,
402 `editionresponsibility` text default NULL,
403 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
404 `illus` varchar(255) default NULL,
405 `pages` varchar(255) default NULL,
407 `size` varchar(255) default NULL,
408 `place` varchar(255) default NULL,
409 `lccn` varchar(25) default NULL,
411 `url` varchar(255) default NULL,
412 `cn_source` varchar(10) default NULL,
413 `cn_class` varchar(30) default NULL,
414 `cn_item` varchar(10) default NULL,
415 `cn_suffix` varchar(10) default NULL,
416 `cn_sort` varchar(30) default NULL,
417 `totalissues` int(10),
418 `marcxml` longtext NOT NULL,
419 PRIMARY KEY (`biblioitemnumber`),
420 KEY `bibinoidx` (`biblioitemnumber`),
421 KEY `bibnoidx` (`biblionumber`),
423 KEY `publishercode` (`publishercode`),
425 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
429 -- Table structure for table `borrowers`
432 DROP TABLE IF EXISTS `borrowers`;
433 CREATE TABLE `borrowers` (
434 `borrowernumber` int(11) NOT NULL auto_increment,
435 `cardnumber` varchar(16) default NULL,
436 `surname` mediumtext NOT NULL,
439 `othernames` mediumtext,
441 `streetnumber` varchar(10) default NULL,
442 `streettype` varchar(50) default NULL,
443 `address` mediumtext NOT NULL,
445 `city` mediumtext NOT NULL,
446 `zipcode` varchar(25) default NULL,
450 `mobile` varchar(50) default NULL,
454 `B_streetnumber` varchar(10) default NULL,
455 `B_streettype` varchar(50) default NULL,
456 `B_address` varchar(100) default NULL,
458 `B_zipcode` varchar(25) default NULL,
461 `B_phone` mediumtext,
462 `dateofbirth` date default NULL,
463 `branchcode` varchar(10) NOT NULL default '',
464 `categorycode` varchar(10) NOT NULL default '',
465 `dateenrolled` date default NULL,
466 `dateexpiry` date default NULL,
467 `gonenoaddress` tinyint(1) default NULL,
468 `lost` tinyint(1) default NULL,
469 `debarred` tinyint(1) default NULL,
470 `contactname` mediumtext,
471 `contactfirstname` text,
473 `guarantorid` int(11) default NULL,
474 `borrowernotes` mediumtext,
475 `relationship` varchar(100) default NULL,
476 `ethnicity` varchar(50) default NULL,
477 `ethnotes` varchar(255) default NULL,
478 `sex` varchar(1) default NULL,
479 `password` varchar(30) default NULL,
480 `flags` int(11) default NULL,
481 `userid` varchar(30) default NULL,
482 `opacnote` mediumtext,
483 `contactnote` varchar(255) default NULL,
484 `sort1` varchar(80) default NULL,
485 `sort2` varchar(80) default NULL,
486 `altcontactfirstname` varchar(255) default NULL,
487 `altcontactsurname` varchar(255) default NULL,
488 `altcontactaddress1` varchar(255) default NULL,
489 `altcontactaddress2` varchar(255) default NULL,
490 `altcontactaddress3` varchar(255) default NULL,
491 `altcontactzipcode` varchar(50) default NULL,
492 `altcontactphone` varchar(50) default NULL,
493 `smsalertnumber` varchar(50) default NULL,
494 UNIQUE KEY `cardnumber` (`cardnumber`),
495 PRIMARY KEY `borrowernumber` (`borrowernumber`),
496 KEY `categorycode` (`categorycode`),
497 KEY `branchcode` (`branchcode`),
498 KEY `userid` (`userid`),
499 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
500 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
501 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
504 -- Table structure for table `borrower_attribute_types`
507 DROP TABLE IF EXISTS `borrower_attribute_types`;
508 CREATE TABLE `borrower_attribute_types` (
509 `code` varchar(10) NOT NULL,
510 `description` varchar(255) NOT NULL,
511 `repeatable` tinyint(1) NOT NULL default 0,
512 `unique_id` tinyint(1) NOT NULL default 0,
513 `opac_display` tinyint(1) NOT NULL default 0,
514 `password_allowed` tinyint(1) NOT NULL default 0,
515 `staff_searchable` tinyint(1) NOT NULL default 0,
516 `authorised_value_category` varchar(10) default NULL,
518 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
521 -- Table structure for table `borrower_attributes`
524 DROP TABLE IF EXISTS `borrower_attributes`;
525 CREATE TABLE `borrower_attributes` (
526 `borrowernumber` int(11) NOT NULL,
527 `code` varchar(10) NOT NULL,
528 `attribute` varchar(64) default NULL,
529 `password` varchar(64) default NULL,
530 KEY `borrowernumber` (`borrowernumber`),
531 KEY `code_attribute` (`code`, `attribute`),
532 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
533 ON DELETE CASCADE ON UPDATE CASCADE,
534 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
535 ON DELETE CASCADE ON UPDATE CASCADE
536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
538 CREATE TABLE `branch_item_rules` (
539 `branchcode` varchar(10) NOT NULL,
540 `itemtype` varchar(10) NOT NULL,
541 `holdallowed` tinyint(1) default NULL,
542 PRIMARY KEY (`itemtype`,`branchcode`),
543 KEY `branch_item_rules_ibfk_2` (`branchcode`),
544 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
545 ON DELETE CASCADE ON UPDATE CASCADE,
546 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
547 ON DELETE CASCADE ON UPDATE CASCADE
548 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
551 -- Table structure for table `branchcategories`
554 DROP TABLE IF EXISTS `branchcategories`;
555 CREATE TABLE `branchcategories` (
556 `categorycode` varchar(10) NOT NULL default '',
557 `categoryname` varchar(32),
558 `codedescription` mediumtext,
559 `categorytype` varchar(16),
560 PRIMARY KEY (`categorycode`)
561 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
564 -- Table structure for table `branches`
567 DROP TABLE IF EXISTS `branches`;
568 CREATE TABLE `branches` (
569 `branchcode` varchar(10) NOT NULL default '',
570 `branchname` mediumtext NOT NULL,
571 `branchaddress1` mediumtext,
572 `branchaddress2` mediumtext,
573 `branchaddress3` mediumtext,
574 `branchzip` varchar(25) default NULL,
575 `branchcity` mediumtext,
576 `branchcountry` text,
577 `branchphone` mediumtext,
578 `branchfax` mediumtext,
579 `branchemail` mediumtext,
580 `branchurl` mediumtext,
581 `issuing` tinyint(4) default NULL,
582 `branchip` varchar(15) default NULL,
583 `branchprinter` varchar(100) default NULL,
584 `branchnotes` mediumtext,
585 UNIQUE KEY `branchcode` (`branchcode`)
586 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
589 -- Table structure for table `branchrelations`
592 DROP TABLE IF EXISTS `branchrelations`;
593 CREATE TABLE `branchrelations` (
594 `branchcode` varchar(10) NOT NULL default '',
595 `categorycode` varchar(10) NOT NULL default '',
596 PRIMARY KEY (`branchcode`,`categorycode`),
597 KEY `branchcode` (`branchcode`),
598 KEY `categorycode` (`categorycode`),
599 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
600 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
601 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
604 -- Table structure for table `branchtransfers`
607 DROP TABLE IF EXISTS `branchtransfers`;
608 CREATE TABLE `branchtransfers` (
609 `itemnumber` int(11) NOT NULL default 0,
610 `datesent` datetime default NULL,
611 `frombranch` varchar(10) NOT NULL default '',
612 `datearrived` datetime default NULL,
613 `tobranch` varchar(10) NOT NULL default '',
614 `comments` mediumtext,
615 KEY `frombranch` (`frombranch`),
616 KEY `tobranch` (`tobranch`),
617 KEY `itemnumber` (`itemnumber`),
618 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
619 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
620 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
621 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
625 -- Table structure for table `browser`
627 DROP TABLE IF EXISTS `browser`;
628 CREATE TABLE `browser` (
629 `level` int(11) NOT NULL,
630 `classification` varchar(20) NOT NULL,
631 `description` varchar(255) NOT NULL,
632 `number` bigint(20) NOT NULL,
633 `endnode` tinyint(4) NOT NULL
634 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
637 -- Table structure for table `categories`
640 DROP TABLE IF EXISTS `categories`;
641 CREATE TABLE `categories` (
642 `categorycode` varchar(10) NOT NULL default '',
643 `description` mediumtext,
644 `enrolmentperiod` smallint(6) default NULL,
645 `upperagelimit` smallint(6) default NULL,
646 `dateofbirthrequired` tinyint(1) default NULL,
647 `finetype` varchar(30) default NULL,
648 `bulk` tinyint(1) default NULL,
649 `enrolmentfee` decimal(28,6) default NULL,
650 `overduenoticerequired` tinyint(1) default NULL,
651 `issuelimit` smallint(6) default NULL,
652 `reservefee` decimal(28,6) default NULL,
653 `category_type` varchar(1) NOT NULL default 'A',
654 PRIMARY KEY (`categorycode`),
655 UNIQUE KEY `categorycode` (`categorycode`)
656 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
659 -- Table structure for table `borrower_branch_circ_rules`
662 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
663 CREATE TABLE `branch_borrower_circ_rules` (
664 `branchcode` VARCHAR(10) NOT NULL,
665 `categorycode` VARCHAR(10) NOT NULL,
666 `maxissueqty` int(4) default NULL,
667 PRIMARY KEY (`categorycode`, `branchcode`),
668 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
669 ON DELETE CASCADE ON UPDATE CASCADE,
670 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
671 ON DELETE CASCADE ON UPDATE CASCADE
672 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
675 -- Table structure for table `default_borrower_circ_rules`
678 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
679 CREATE TABLE `default_borrower_circ_rules` (
680 `categorycode` VARCHAR(10) NOT NULL,
681 `maxissueqty` int(4) default NULL,
682 PRIMARY KEY (`categorycode`),
683 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
684 ON DELETE CASCADE ON UPDATE CASCADE
685 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
688 -- Table structure for table `default_branch_circ_rules`
691 DROP TABLE IF EXISTS `default_branch_circ_rules`;
692 CREATE TABLE `default_branch_circ_rules` (
693 `branchcode` VARCHAR(10) NOT NULL,
694 `maxissueqty` int(4) default NULL,
695 `holdallowed` tinyint(1) default NULL,
696 PRIMARY KEY (`branchcode`),
697 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
698 ON DELETE CASCADE ON UPDATE CASCADE
699 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
702 -- Table structure for table `default_branch_item_rules`
705 CREATE TABLE `default_branch_item_rules` (
706 `itemtype` varchar(10) NOT NULL,
707 `holdallowed` tinyint(1) default NULL,
708 PRIMARY KEY (`itemtype`),
709 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
710 ON DELETE CASCADE ON UPDATE CASCADE
711 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
714 -- Table structure for table `default_circ_rules`
717 DROP TABLE IF EXISTS `default_circ_rules`;
718 CREATE TABLE `default_circ_rules` (
719 `singleton` enum('singleton') NOT NULL default 'singleton',
720 `maxissueqty` int(4) default NULL,
721 `holdallowed` int(1) default NULL,
722 PRIMARY KEY (`singleton`)
723 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
726 -- Table structure for table `cities`
729 DROP TABLE IF EXISTS `cities`;
730 CREATE TABLE `cities` (
731 `cityid` int(11) NOT NULL auto_increment,
732 `city_name` varchar(100) NOT NULL default '',
733 `city_zipcode` varchar(20) default NULL,
734 PRIMARY KEY (`cityid`)
735 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
738 -- Table structure for table `class_sort_rules`
741 DROP TABLE IF EXISTS `class_sort_rules`;
742 CREATE TABLE `class_sort_rules` (
743 `class_sort_rule` varchar(10) NOT NULL default '',
744 `description` mediumtext,
745 `sort_routine` varchar(30) NOT NULL default '',
746 PRIMARY KEY (`class_sort_rule`),
747 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
748 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
751 -- Table structure for table `class_sources`
754 DROP TABLE IF EXISTS `class_sources`;
755 CREATE TABLE `class_sources` (
756 `cn_source` varchar(10) NOT NULL default '',
757 `description` mediumtext,
758 `used` tinyint(4) NOT NULL default 0,
759 `class_sort_rule` varchar(10) NOT NULL default '',
760 PRIMARY KEY (`cn_source`),
761 UNIQUE KEY `cn_source_idx` (`cn_source`),
762 KEY `used_idx` (`used`),
763 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
764 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
767 -- Table structure for table `currency`
770 DROP TABLE IF EXISTS `currency`;
771 CREATE TABLE `currency` (
772 `currency` varchar(10) NOT NULL default '',
773 `symbol` varchar(5) default NULL,
774 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
775 `rate` float(7,5) default NULL,
776 PRIMARY KEY (`currency`)
777 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
780 -- Table structure for table `deletedbiblio`
783 DROP TABLE IF EXISTS `deletedbiblio`;
784 CREATE TABLE `deletedbiblio` (
785 `biblionumber` int(11) NOT NULL default 0,
786 `frameworkcode` varchar(4) NOT NULL default '',
789 `unititle` mediumtext,
791 `serial` tinyint(1) default NULL,
792 `seriestitle` mediumtext,
793 `copyrightdate` smallint(6) default NULL,
794 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
795 `datecreated` DATE NOT NULL,
796 `abstract` mediumtext,
797 PRIMARY KEY (`biblionumber`),
798 KEY `blbnoidx` (`biblionumber`)
799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
802 -- Table structure for table `deletedbiblioitems`
805 DROP TABLE IF EXISTS `deletedbiblioitems`;
806 CREATE TABLE `deletedbiblioitems` (
807 `biblioitemnumber` int(11) NOT NULL default 0,
808 `biblionumber` int(11) NOT NULL default 0,
811 `itemtype` varchar(10) default NULL,
812 `isbn` varchar(30) default NULL,
813 `issn` varchar(9) default NULL,
814 `publicationyear` text,
815 `publishercode` varchar(255) default NULL,
816 `volumedate` date default NULL,
818 `collectiontitle` mediumtext default NULL,
819 `collectionissn` text default NULL,
820 `collectionvolume` mediumtext default NULL,
821 `editionstatement` text default NULL,
822 `editionresponsibility` text default NULL,
823 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
824 `illus` varchar(255) default NULL,
825 `pages` varchar(255) default NULL,
827 `size` varchar(255) default NULL,
828 `place` varchar(255) default NULL,
829 `lccn` varchar(25) default NULL,
831 `url` varchar(255) default NULL,
832 `cn_source` varchar(10) default NULL,
833 `cn_class` varchar(30) default NULL,
834 `cn_item` varchar(10) default NULL,
835 `cn_suffix` varchar(10) default NULL,
836 `cn_sort` varchar(30) default NULL,
837 `totalissues` int(10),
838 `marcxml` longtext NOT NULL,
839 PRIMARY KEY (`biblioitemnumber`),
840 KEY `bibinoidx` (`biblioitemnumber`),
841 KEY `bibnoidx` (`biblionumber`),
843 KEY `publishercode` (`publishercode`)
844 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
847 -- Table structure for table `deletedborrowers`
850 DROP TABLE IF EXISTS `deletedborrowers`;
851 CREATE TABLE `deletedborrowers` (
852 `borrowernumber` int(11) NOT NULL default 0,
853 `cardnumber` varchar(9) NOT NULL default '',
854 `surname` mediumtext NOT NULL,
857 `othernames` mediumtext,
859 `streetnumber` varchar(10) default NULL,
860 `streettype` varchar(50) default NULL,
861 `address` mediumtext NOT NULL,
863 `city` mediumtext NOT NULL,
864 `zipcode` varchar(25) default NULL,
868 `mobile` varchar(50) default NULL,
872 `B_streetnumber` varchar(10) default NULL,
873 `B_streettype` varchar(50) default NULL,
874 `B_address` varchar(100) default NULL,
876 `B_zipcode` varchar(25) default NULL,
879 `B_phone` mediumtext,
880 `dateofbirth` date default NULL,
881 `branchcode` varchar(10) NOT NULL default '',
882 `categorycode` varchar(10) default NULL,
883 `dateenrolled` date default NULL,
884 `dateexpiry` date default NULL,
885 `gonenoaddress` tinyint(1) default NULL,
886 `lost` tinyint(1) default NULL,
887 `debarred` tinyint(1) default NULL,
888 `contactname` mediumtext,
889 `contactfirstname` text,
891 `guarantorid` int(11) default NULL,
892 `borrowernotes` mediumtext,
893 `relationship` varchar(100) default NULL,
894 `ethnicity` varchar(50) default NULL,
895 `ethnotes` varchar(255) default NULL,
896 `sex` varchar(1) default NULL,
897 `password` varchar(30) default NULL,
898 `flags` int(11) default NULL,
899 `userid` varchar(30) default NULL,
900 `opacnote` mediumtext,
901 `contactnote` varchar(255) default NULL,
902 `sort1` varchar(80) default NULL,
903 `sort2` varchar(80) default NULL,
904 `altcontactfirstname` varchar(255) default NULL,
905 `altcontactsurname` varchar(255) default NULL,
906 `altcontactaddress1` varchar(255) default NULL,
907 `altcontactaddress2` varchar(255) default NULL,
908 `altcontactaddress3` varchar(255) default NULL,
909 `altcontactzipcode` varchar(50) default NULL,
910 `altcontactphone` varchar(50) default NULL,
911 `smsalertnumber` varchar(50) default NULL,
912 KEY `borrowernumber` (`borrowernumber`),
913 KEY `cardnumber` (`cardnumber`)
914 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
917 -- Table structure for table `deleteditems`
920 DROP TABLE IF EXISTS `deleteditems`;
921 CREATE TABLE `deleteditems` (
922 `itemnumber` int(11) NOT NULL default 0,
923 `biblionumber` int(11) NOT NULL default 0,
924 `biblioitemnumber` int(11) NOT NULL default 0,
925 `barcode` varchar(20) default NULL,
926 `dateaccessioned` date default NULL,
927 `booksellerid` mediumtext default NULL,
928 `homebranch` varchar(10) default NULL,
929 `price` decimal(8,2) default NULL,
930 `replacementprice` decimal(8,2) default NULL,
931 `replacementpricedate` date default NULL,
932 `datelastborrowed` date default NULL,
933 `datelastseen` date default NULL,
934 `stack` tinyint(1) default NULL,
935 `notforloan` tinyint(1) NOT NULL default 0,
936 `damaged` tinyint(1) NOT NULL default 0,
937 `itemlost` tinyint(1) NOT NULL default 0,
938 `wthdrawn` tinyint(1) NOT NULL default 0,
939 `itemcallnumber` varchar(255) default NULL,
940 `issues` smallint(6) default NULL,
941 `renewals` smallint(6) default NULL,
942 `reserves` smallint(6) default NULL,
943 `restricted` tinyint(1) default NULL,
944 `itemnotes` mediumtext,
945 `holdingbranch` varchar(10) default NULL,
946 `paidfor` mediumtext,
947 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
948 `location` varchar(80) default NULL,
949 `permanent_location` varchar(80) default NULL,
950 `onloan` date default NULL,
951 `cn_source` varchar(10) default NULL,
952 `cn_sort` varchar(30) default NULL,
953 `ccode` varchar(10) default NULL,
954 `materials` varchar(10) default NULL,
955 `uri` varchar(255) default NULL,
956 `itype` varchar(10) default NULL,
957 `more_subfields_xml` longtext default NULL,
958 `enumchron` varchar(80) default NULL,
959 `copynumber` varchar(32) default NULL,
961 PRIMARY KEY (`itemnumber`),
962 KEY `delitembarcodeidx` (`barcode`),
963 KEY `delitembinoidx` (`biblioitemnumber`),
964 KEY `delitembibnoidx` (`biblionumber`),
965 KEY `delhomebranch` (`homebranch`),
966 KEY `delholdingbranch` (`holdingbranch`)
967 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
970 -- Table structure for table `ethnicity`
973 DROP TABLE IF EXISTS `ethnicity`;
974 CREATE TABLE `ethnicity` (
975 `code` varchar(10) NOT NULL default '',
976 `name` varchar(255) default NULL,
978 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
981 -- Table structure for table `hold_fill_targets`
984 DROP TABLE IF EXISTS `hold_fill_targets`;
985 CREATE TABLE hold_fill_targets (
986 `borrowernumber` int(11) NOT NULL,
987 `biblionumber` int(11) NOT NULL,
988 `itemnumber` int(11) NOT NULL,
989 `source_branchcode` varchar(10) default NULL,
990 `item_level_request` tinyint(4) NOT NULL default 0,
991 PRIMARY KEY `itemnumber` (`itemnumber`),
992 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
993 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
994 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
995 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
996 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
997 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
998 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
999 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
1000 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1004 -- Table structure for table `import_batches`
1007 DROP TABLE IF EXISTS `import_batches`;
1008 CREATE TABLE `import_batches` (
1009 `import_batch_id` int(11) NOT NULL auto_increment,
1010 `matcher_id` int(11) default NULL,
1011 `template_id` int(11) default NULL,
1012 `branchcode` varchar(10) default NULL,
1013 `num_biblios` int(11) NOT NULL default 0,
1014 `num_items` int(11) NOT NULL default 0,
1015 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1016 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1017 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1018 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1019 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1020 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1021 `file_name` varchar(100),
1022 `comments` mediumtext,
1023 PRIMARY KEY (`import_batch_id`),
1024 KEY `branchcode` (`branchcode`)
1025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1028 -- Table structure for table `import_records`
1031 DROP TABLE IF EXISTS `import_records`;
1032 CREATE TABLE `import_records` (
1033 `import_record_id` int(11) NOT NULL auto_increment,
1034 `import_batch_id` int(11) NOT NULL,
1035 `branchcode` varchar(10) default NULL,
1036 `record_sequence` int(11) NOT NULL default 0,
1037 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1038 `import_date` DATE default NULL,
1039 `marc` longblob NOT NULL,
1040 `marcxml` longtext NOT NULL,
1041 `marcxml_old` longtext NOT NULL,
1042 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1043 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1044 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1045 `import_error` mediumtext,
1046 `encoding` varchar(40) NOT NULL default '',
1047 `z3950random` varchar(40) default NULL,
1048 PRIMARY KEY (`import_record_id`),
1049 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1050 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1051 KEY `branchcode` (`branchcode`),
1052 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1053 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1056 -- Table structure for `import_record_matches`
1058 DROP TABLE IF EXISTS `import_record_matches`;
1059 CREATE TABLE `import_record_matches` (
1060 `import_record_id` int(11) NOT NULL,
1061 `candidate_match_id` int(11) NOT NULL,
1062 `score` int(11) NOT NULL default 0,
1063 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1064 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1065 KEY `record_score` (`import_record_id`, `score`)
1066 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1069 -- Table structure for table `import_biblios`
1072 DROP TABLE IF EXISTS `import_biblios`;
1073 CREATE TABLE `import_biblios` (
1074 `import_record_id` int(11) NOT NULL,
1075 `matched_biblionumber` int(11) default NULL,
1076 `control_number` varchar(25) default NULL,
1077 `original_source` varchar(25) default NULL,
1078 `title` varchar(128) default NULL,
1079 `author` varchar(80) default NULL,
1080 `isbn` varchar(30) default NULL,
1081 `issn` varchar(9) default NULL,
1082 `has_items` tinyint(1) NOT NULL default 0,
1083 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1084 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1085 KEY `matched_biblionumber` (`matched_biblionumber`),
1086 KEY `title` (`title`),
1088 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1091 -- Table structure for table `import_items`
1094 DROP TABLE IF EXISTS `import_items`;
1095 CREATE TABLE `import_items` (
1096 `import_items_id` int(11) NOT NULL auto_increment,
1097 `import_record_id` int(11) NOT NULL,
1098 `itemnumber` int(11) default NULL,
1099 `branchcode` varchar(10) default NULL,
1100 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1101 `marcxml` longtext NOT NULL,
1102 `import_error` mediumtext,
1103 PRIMARY KEY (`import_items_id`),
1104 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1105 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1106 KEY `itemnumber` (`itemnumber`),
1107 KEY `branchcode` (`branchcode`)
1108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1111 -- Table structure for table `issues`
1114 DROP TABLE IF EXISTS `issues`;
1115 CREATE TABLE `issues` (
1116 `borrowernumber` int(11) default NULL,
1117 `itemnumber` int(11) default NULL,
1118 `date_due` date default NULL,
1119 `branchcode` varchar(10) default NULL,
1120 `issuingbranch` varchar(18) default NULL,
1121 `returndate` date default NULL,
1122 `lastreneweddate` date default NULL,
1123 `return` varchar(4) default NULL,
1124 `renewals` tinyint(4) default NULL,
1125 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1126 `issuedate` date default NULL,
1127 KEY `issuesborridx` (`borrowernumber`),
1128 KEY `issuesitemidx` (`itemnumber`),
1129 KEY `bordate` (`borrowernumber`,`timestamp`),
1130 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1131 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1135 -- Table structure for table `issuingrules`
1138 DROP TABLE IF EXISTS `issuingrules`;
1139 CREATE TABLE `issuingrules` (
1140 `categorycode` varchar(10) NOT NULL default '',
1141 `itemtype` varchar(10) NOT NULL default '',
1142 `restrictedtype` tinyint(1) default NULL,
1143 `rentaldiscount` decimal(28,6) default NULL,
1144 `reservecharge` decimal(28,6) default NULL,
1145 `fine` decimal(28,6) default NULL,
1146 `firstremind` int(11) default NULL,
1147 `chargeperiod` int(11) default NULL,
1148 `accountsent` int(11) default NULL,
1149 `chargename` varchar(100) default NULL,
1150 `maxissueqty` int(4) default NULL,
1151 `issuelength` int(4) default NULL,
1152 `branchcode` varchar(10) NOT NULL default '',
1153 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1154 KEY `categorycode` (`categorycode`),
1155 KEY `itemtype` (`itemtype`)
1156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1159 -- Table structure for table `items`
1162 DROP TABLE IF EXISTS `items`;
1163 CREATE TABLE `items` (
1164 `itemnumber` int(11) NOT NULL auto_increment,
1165 `biblionumber` int(11) NOT NULL default 0,
1166 `biblioitemnumber` int(11) NOT NULL default 0,
1167 `barcode` varchar(20) default NULL,
1168 `dateaccessioned` date default NULL,
1169 `booksellerid` mediumtext default NULL,
1170 `homebranch` varchar(10) default NULL,
1171 `price` decimal(8,2) default NULL,
1172 `replacementprice` decimal(8,2) default NULL,
1173 `replacementpricedate` date default NULL,
1174 `datelastborrowed` date default NULL,
1175 `datelastseen` date default NULL,
1176 `stack` tinyint(1) default NULL,
1177 `notforloan` tinyint(1) NOT NULL default 0,
1178 `damaged` tinyint(1) NOT NULL default 0,
1179 `itemlost` tinyint(1) NOT NULL default 0,
1180 `wthdrawn` tinyint(1) NOT NULL default 0,
1181 `itemcallnumber` varchar(255) default NULL,
1182 `issues` smallint(6) default NULL,
1183 `renewals` smallint(6) default NULL,
1184 `reserves` smallint(6) default NULL,
1185 `restricted` tinyint(1) default NULL,
1186 `itemnotes` mediumtext,
1187 `holdingbranch` varchar(10) default NULL,
1188 `paidfor` mediumtext,
1189 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1190 `location` varchar(80) default NULL,
1191 `permanent_location` varchar(80) default NULL,
1192 `onloan` date default NULL,
1193 `cn_source` varchar(10) default NULL,
1194 `cn_sort` varchar(30) default NULL,
1195 `ccode` varchar(10) default NULL,
1196 `materials` varchar(10) default NULL,
1197 `uri` varchar(255) default NULL,
1198 `itype` varchar(10) default NULL,
1199 `more_subfields_xml` longtext default NULL,
1200 `enumchron` varchar(80) default NULL,
1201 `copynumber` varchar(32) default NULL,
1202 PRIMARY KEY (`itemnumber`),
1203 UNIQUE KEY `itembarcodeidx` (`barcode`),
1204 KEY `itembinoidx` (`biblioitemnumber`),
1205 KEY `itembibnoidx` (`biblionumber`),
1206 KEY `homebranch` (`homebranch`),
1207 KEY `holdingbranch` (`holdingbranch`),
1208 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1209 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1210 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1214 -- Table structure for table `itemtypes`
1217 DROP TABLE IF EXISTS `itemtypes`;
1218 CREATE TABLE `itemtypes` (
1219 `itemtype` varchar(10) NOT NULL default '',
1220 `description` mediumtext,
1221 `renewalsallowed` smallint(6) default NULL,
1222 `rentalcharge` double(16,4) default NULL,
1223 `notforloan` smallint(6) default NULL,
1224 `imageurl` varchar(200) default NULL,
1226 PRIMARY KEY (`itemtype`),
1227 UNIQUE KEY `itemtype` (`itemtype`)
1228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1231 -- Table structure for table `labels`
1234 DROP TABLE IF EXISTS `labels`;
1235 CREATE TABLE `labels` (
1236 `labelid` int(11) NOT NULL auto_increment,
1237 `batch_id` int(10) NOT NULL default 1,
1238 `itemnumber` varchar(100) NOT NULL default '',
1239 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1240 PRIMARY KEY (`labelid`)
1241 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1244 -- Table structure for table `labels_conf`
1247 DROP TABLE IF EXISTS `labels_conf`;
1248 CREATE TABLE `labels_conf` (
1249 `id` int(4) NOT NULL auto_increment,
1250 `barcodetype` char(100) default '',
1251 `title` int(1) default '0',
1252 `subtitle` int(1) default '0',
1253 `itemtype` int(1) default '0',
1254 `barcode` int(1) default '0',
1255 `dewey` int(1) default '0',
1256 `classification` int(1) default NULL,
1257 `subclass` int(1) default '0',
1258 `itemcallnumber` int(1) default '0',
1259 `author` int(1) default '0',
1260 `issn` int(1) default '0',
1261 `isbn` int(1) default '0',
1262 `startlabel` int(2) NOT NULL default '1',
1263 `printingtype` char(32) default 'BAR',
1264 `formatstring` mediumtext default NULL,
1265 `layoutname` char(20) NOT NULL default 'TEST',
1266 `guidebox` int(1) default '0',
1267 `active` tinyint(1) default '1',
1268 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1269 `ccode` char(4) collate utf8_unicode_ci default NULL,
1270 `callnum_split` int(1) default NULL,
1271 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1273 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1276 -- Table structure for table `labels_profile`
1279 DROP TABLE IF EXISTS `labels_profile`;
1280 CREATE TABLE `labels_profile` (
1281 `tmpl_id` int(4) NOT NULL,
1282 `prof_id` int(4) NOT NULL,
1283 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1284 UNIQUE KEY `prof_id` (`prof_id`)
1285 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1288 -- Table structure for table `labels_templates`
1291 DROP TABLE IF EXISTS `labels_templates`;
1292 CREATE TABLE `labels_templates` (
1293 `tmpl_id` int(4) NOT NULL auto_increment,
1294 `tmpl_code` char(100) default '',
1295 `tmpl_desc` char(100) default '',
1296 `page_width` float default '0',
1297 `page_height` float default '0',
1298 `label_width` float default '0',
1299 `label_height` float default '0',
1300 `topmargin` float default '0',
1301 `leftmargin` float default '0',
1302 `cols` int(2) default '0',
1303 `rows` int(2) default '0',
1304 `colgap` float default '0',
1305 `rowgap` float default '0',
1306 `active` int(1) default NULL,
1307 `units` char(20) default 'PX',
1308 `fontsize` int(4) NOT NULL default '3',
1309 `font` char(10) NOT NULL default 'TR',
1310 PRIMARY KEY (`tmpl_id`)
1311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1314 -- Table structure for table `letter`
1317 DROP TABLE IF EXISTS `letter`;
1318 CREATE TABLE `letter` (
1319 `module` varchar(20) NOT NULL default '',
1320 `code` varchar(20) NOT NULL default '',
1321 `name` varchar(100) NOT NULL default '',
1322 `title` varchar(200) NOT NULL default '',
1324 PRIMARY KEY (`module`,`code`)
1325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1328 -- Table structure for table `marc_subfield_structure`
1331 DROP TABLE IF EXISTS `marc_subfield_structure`;
1332 CREATE TABLE `marc_subfield_structure` (
1333 `tagfield` varchar(3) NOT NULL default '',
1334 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1335 `liblibrarian` varchar(255) NOT NULL default '',
1336 `libopac` varchar(255) NOT NULL default '',
1337 `repeatable` tinyint(4) NOT NULL default 0,
1338 `mandatory` tinyint(4) NOT NULL default 0,
1339 `kohafield` varchar(40) default NULL,
1340 `tab` tinyint(1) default NULL,
1341 `authorised_value` varchar(20) default NULL,
1342 `authtypecode` varchar(20) default NULL,
1343 `value_builder` varchar(80) default NULL,
1344 `isurl` tinyint(1) default NULL,
1345 `hidden` tinyint(1) default NULL,
1346 `frameworkcode` varchar(4) NOT NULL default '',
1347 `seealso` varchar(1100) default NULL,
1348 `link` varchar(80) default NULL,
1349 `defaultvalue` text default NULL,
1350 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1351 KEY `kohafield_2` (`kohafield`),
1352 KEY `tab` (`frameworkcode`,`tab`),
1353 KEY `kohafield` (`frameworkcode`,`kohafield`)
1354 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1357 -- Table structure for table `marc_tag_structure`
1360 DROP TABLE IF EXISTS `marc_tag_structure`;
1361 CREATE TABLE `marc_tag_structure` (
1362 `tagfield` varchar(3) NOT NULL default '',
1363 `liblibrarian` varchar(255) NOT NULL default '',
1364 `libopac` varchar(255) NOT NULL default '',
1365 `repeatable` tinyint(4) NOT NULL default 0,
1366 `mandatory` tinyint(4) NOT NULL default 0,
1367 `authorised_value` varchar(10) default NULL,
1368 `frameworkcode` varchar(4) NOT NULL default '',
1369 PRIMARY KEY (`frameworkcode`,`tagfield`)
1370 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1373 -- Table structure for table `marc_matchers`
1376 DROP TABLE IF EXISTS `marc_matchers`;
1377 CREATE TABLE `marc_matchers` (
1378 `matcher_id` int(11) NOT NULL auto_increment,
1379 `code` varchar(10) NOT NULL default '',
1380 `description` varchar(255) NOT NULL default '',
1381 `record_type` varchar(10) NOT NULL default 'biblio',
1382 `threshold` int(11) NOT NULL default 0,
1383 PRIMARY KEY (`matcher_id`),
1384 KEY `code` (`code`),
1385 KEY `record_type` (`record_type`)
1386 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1389 -- Table structure for table `matchpoints`
1391 DROP TABLE IF EXISTS `matchpoints`;
1392 CREATE TABLE `matchpoints` (
1393 `matcher_id` int(11) NOT NULL,
1394 `matchpoint_id` int(11) NOT NULL auto_increment,
1395 `search_index` varchar(30) NOT NULL default '',
1396 `score` int(11) NOT NULL default 0,
1397 PRIMARY KEY (`matchpoint_id`),
1398 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1399 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1400 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1404 -- Table structure for table `matchpoint_components`
1406 DROP TABLE IF EXISTS `matchpoint_components`;
1407 CREATE TABLE `matchpoint_components` (
1408 `matchpoint_id` int(11) NOT NULL,
1409 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1410 sequence int(11) NOT NULL default 0,
1411 tag varchar(3) NOT NULL default '',
1412 subfields varchar(40) NOT NULL default '',
1413 offset int(4) NOT NULL default 0,
1414 length int(4) NOT NULL default 0,
1415 PRIMARY KEY (`matchpoint_component_id`),
1416 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1417 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1418 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1419 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1422 -- Table structure for table `matcher_component_norms`
1424 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1425 CREATE TABLE `matchpoint_component_norms` (
1426 `matchpoint_component_id` int(11) NOT NULL,
1427 `sequence` int(11) NOT NULL default 0,
1428 `norm_routine` varchar(50) NOT NULL default '',
1429 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1430 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1431 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1432 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1435 -- Table structure for table `matcher_matchpoints`
1437 DROP TABLE IF EXISTS `matcher_matchpoints`;
1438 CREATE TABLE `matcher_matchpoints` (
1439 `matcher_id` int(11) NOT NULL,
1440 `matchpoint_id` int(11) NOT NULL,
1441 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1442 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1443 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1444 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1445 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1448 -- Table structure for table `matchchecks`
1450 DROP TABLE IF EXISTS `matchchecks`;
1451 CREATE TABLE `matchchecks` (
1452 `matcher_id` int(11) NOT NULL,
1453 `matchcheck_id` int(11) NOT NULL auto_increment,
1454 `source_matchpoint_id` int(11) NOT NULL,
1455 `target_matchpoint_id` int(11) NOT NULL,
1456 PRIMARY KEY (`matchcheck_id`),
1457 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1458 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1459 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1460 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1461 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1462 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1463 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1466 -- Table structure for table `notifys`
1469 DROP TABLE IF EXISTS `notifys`;
1470 CREATE TABLE `notifys` (
1471 `notify_id` int(11) NOT NULL default 0,
1472 `borrowernumber` int(11) NOT NULL default 0,
1473 `itemnumber` int(11) NOT NULL default 0,
1474 `notify_date` date default NULL,
1475 `notify_send_date` date default NULL,
1476 `notify_level` int(1) NOT NULL default 0,
1477 `method` varchar(20) NOT NULL default ''
1478 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1481 -- Table structure for table `nozebra`
1484 DROP TABLE IF EXISTS `nozebra`;
1485 CREATE TABLE `nozebra` (
1486 `server` varchar(20) NOT NULL,
1487 `indexname` varchar(40) NOT NULL,
1488 `value` varchar(250) NOT NULL,
1489 `biblionumbers` longtext NOT NULL,
1490 KEY `indexname` (`server`,`indexname`),
1491 KEY `value` (`server`,`value`))
1492 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1495 -- Table structure for table `old_issues`
1498 DROP TABLE IF EXISTS `old_issues`;
1499 CREATE TABLE `old_issues` (
1500 `borrowernumber` int(11) default NULL,
1501 `itemnumber` int(11) default NULL,
1502 `date_due` date default NULL,
1503 `branchcode` varchar(10) default NULL,
1504 `issuingbranch` varchar(18) default NULL,
1505 `returndate` date default NULL,
1506 `lastreneweddate` date default NULL,
1507 `return` varchar(4) default NULL,
1508 `renewals` tinyint(4) default NULL,
1509 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1510 `issuedate` date default NULL,
1511 KEY `old_issuesborridx` (`borrowernumber`),
1512 KEY `old_issuesitemidx` (`itemnumber`),
1513 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1514 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1515 ON DELETE SET NULL ON UPDATE SET NULL,
1516 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1517 ON DELETE SET NULL ON UPDATE SET NULL
1518 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1521 -- Table structure for table `old_reserves`
1523 DROP TABLE IF EXISTS `old_reserves`;
1524 CREATE TABLE `old_reserves` (
1525 `borrowernumber` int(11) default NULL,
1526 `reservedate` date default NULL,
1527 `biblionumber` int(11) default NULL,
1528 `constrainttype` varchar(1) default NULL,
1529 `branchcode` varchar(10) default NULL,
1530 `notificationdate` date default NULL,
1531 `reminderdate` date default NULL,
1532 `cancellationdate` date default NULL,
1533 `reservenotes` mediumtext,
1534 `priority` smallint(6) default NULL,
1535 `found` varchar(1) default NULL,
1536 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1537 `itemnumber` int(11) default NULL,
1538 `waitingdate` date default NULL,
1539 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1540 KEY `old_reserves_biblionumber` (`biblionumber`),
1541 KEY `old_reserves_itemnumber` (`itemnumber`),
1542 KEY `old_reserves_branchcode` (`branchcode`),
1543 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1544 ON DELETE SET NULL ON UPDATE SET NULL,
1545 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1546 ON DELETE SET NULL ON UPDATE SET NULL,
1547 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1548 ON DELETE SET NULL ON UPDATE SET NULL
1549 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1552 -- Table structure for table `opac_news`
1555 DROP TABLE IF EXISTS `opac_news`;
1556 CREATE TABLE `opac_news` (
1557 `idnew` int(10) unsigned NOT NULL auto_increment,
1558 `title` varchar(250) NOT NULL default '',
1559 `new` text NOT NULL,
1560 `lang` varchar(25) NOT NULL default '',
1561 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1562 `expirationdate` date default NULL,
1563 `number` int(11) default NULL,
1564 PRIMARY KEY (`idnew`)
1565 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1568 -- Table structure for table `overduerules`
1571 DROP TABLE IF EXISTS `overduerules`;
1572 CREATE TABLE `overduerules` (
1573 `branchcode` varchar(10) NOT NULL default '',
1574 `categorycode` varchar(10) NOT NULL default '',
1575 `delay1` int(4) default 0,
1576 `letter1` varchar(20) default NULL,
1577 `debarred1` varchar(1) default 0,
1578 `delay2` int(4) default 0,
1579 `debarred2` varchar(1) default 0,
1580 `letter2` varchar(20) default NULL,
1581 `delay3` int(4) default 0,
1582 `letter3` varchar(20) default NULL,
1583 `debarred3` int(1) default 0,
1584 PRIMARY KEY (`branchcode`,`categorycode`)
1585 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1588 -- Table structure for table `patroncards`
1591 DROP TABLE IF EXISTS `patroncards`;
1592 CREATE TABLE `patroncards` (
1593 `cardid` int(11) NOT NULL auto_increment,
1594 `batch_id` varchar(10) NOT NULL default '1',
1595 `borrowernumber` int(11) NOT NULL,
1596 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1597 PRIMARY KEY (`cardid`),
1598 KEY `patroncards_ibfk_1` (`borrowernumber`),
1599 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1600 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1603 -- Table structure for table `patronimage`
1606 DROP TABLE IF EXISTS `patronimage`;
1607 CREATE TABLE `patronimage` (
1608 `cardnumber` varchar(16) NOT NULL,
1609 `mimetype` varchar(15) NOT NULL,
1610 `imagefile` mediumblob NOT NULL,
1611 PRIMARY KEY (`cardnumber`),
1612 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1616 -- Table structure for table `printers`
1619 DROP TABLE IF EXISTS `printers`;
1620 CREATE TABLE `printers` (
1621 `printername` varchar(40) NOT NULL default '',
1622 `printqueue` varchar(20) default NULL,
1623 `printtype` varchar(20) default NULL,
1624 PRIMARY KEY (`printername`)
1625 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1628 -- Table structure for table `printers_profile`
1631 DROP TABLE IF EXISTS `printers_profile`;
1632 CREATE TABLE `printers_profile` (
1633 `prof_id` int(4) NOT NULL auto_increment,
1634 `printername` varchar(40) NOT NULL,
1635 `tmpl_id` int(4) NOT NULL,
1636 `paper_bin` varchar(20) NOT NULL,
1637 `offset_horz` float default NULL,
1638 `offset_vert` float default NULL,
1639 `creep_horz` float default NULL,
1640 `creep_vert` float default NULL,
1641 `unit` char(20) NOT NULL default 'POINT',
1642 PRIMARY KEY (`prof_id`),
1643 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1644 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1645 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1648 -- Table structure for table `repeatable_holidays`
1651 DROP TABLE IF EXISTS `repeatable_holidays`;
1652 CREATE TABLE `repeatable_holidays` (
1653 `id` int(11) NOT NULL auto_increment,
1654 `branchcode` varchar(10) NOT NULL default '',
1655 `weekday` smallint(6) default NULL,
1656 `day` smallint(6) default NULL,
1657 `month` smallint(6) default NULL,
1658 `title` varchar(50) NOT NULL default '',
1659 `description` text NOT NULL,
1661 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1664 -- Table structure for table `reports_dictionary`
1667 DROP TABLE IF EXISTS `reports_dictionary`;
1668 CREATE TABLE reports_dictionary (
1669 `id` int(11) NOT NULL auto_increment,
1670 `name` varchar(255) default NULL,
1672 `date_created` datetime default NULL,
1673 `date_modified` datetime default NULL,
1675 `area` int(11) default NULL,
1677 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1680 -- Table structure for table `reserveconstraints`
1683 DROP TABLE IF EXISTS `reserveconstraints`;
1684 CREATE TABLE `reserveconstraints` (
1685 `borrowernumber` int(11) NOT NULL default 0,
1686 `reservedate` date default NULL,
1687 `biblionumber` int(11) NOT NULL default 0,
1688 `biblioitemnumber` int(11) default NULL,
1689 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1690 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1693 -- Table structure for table `reserves`
1696 DROP TABLE IF EXISTS `reserves`;
1697 CREATE TABLE `reserves` (
1698 `borrowernumber` int(11) NOT NULL default 0,
1699 `reservedate` date default NULL,
1700 `biblionumber` int(11) NOT NULL default 0,
1701 `constrainttype` varchar(1) default NULL,
1702 `branchcode` varchar(10) default NULL,
1703 `notificationdate` date default NULL,
1704 `reminderdate` date default NULL,
1705 `cancellationdate` date default NULL,
1706 `reservenotes` mediumtext,
1707 `priority` smallint(6) default NULL,
1708 `found` varchar(1) default NULL,
1709 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1710 `itemnumber` int(11) default NULL,
1711 `waitingdate` date default NULL,
1712 KEY `borrowernumber` (`borrowernumber`),
1713 KEY `biblionumber` (`biblionumber`),
1714 KEY `itemnumber` (`itemnumber`),
1715 KEY `branchcode` (`branchcode`),
1716 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1717 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1718 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1719 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1720 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1723 -- Table structure for table `reviews`
1726 DROP TABLE IF EXISTS `reviews`;
1727 CREATE TABLE `reviews` (
1728 `reviewid` int(11) NOT NULL auto_increment,
1729 `borrowernumber` int(11) default NULL,
1730 `biblionumber` int(11) default NULL,
1732 `approved` tinyint(4) default NULL,
1733 `datereviewed` datetime default NULL,
1734 PRIMARY KEY (`reviewid`)
1735 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1738 -- Table structure for table `roadtype`
1741 DROP TABLE IF EXISTS `roadtype`;
1742 CREATE TABLE `roadtype` (
1743 `roadtypeid` int(11) NOT NULL auto_increment,
1744 `road_type` varchar(100) NOT NULL default '',
1745 PRIMARY KEY (`roadtypeid`)
1746 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1749 -- Table structure for table `saved_sql`
1752 DROP TABLE IF EXISTS `saved_sql`;
1753 CREATE TABLE saved_sql (
1754 `id` int(11) NOT NULL auto_increment,
1755 `borrowernumber` int(11) default NULL,
1756 `date_created` datetime default NULL,
1757 `last_modified` datetime default NULL,
1759 `last_run` datetime default NULL,
1760 `report_name` varchar(255) default NULL,
1761 `type` varchar(255) default NULL,
1764 KEY boridx (`borrowernumber`)
1765 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1769 -- Table structure for `saved_reports`
1772 DROP TABLE IF EXISTS `saved_reports`;
1773 CREATE TABLE saved_reports (
1774 `id` int(11) NOT NULL auto_increment,
1775 `report_id` int(11) default NULL,
1777 `date_run` datetime default NULL,
1779 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1783 -- Table structure for table `serial`
1786 DROP TABLE IF EXISTS `serial`;
1787 CREATE TABLE `serial` (
1788 `serialid` int(11) NOT NULL auto_increment,
1789 `biblionumber` varchar(100) NOT NULL default '',
1790 `subscriptionid` varchar(100) NOT NULL default '',
1791 `serialseq` varchar(100) NOT NULL default '',
1792 `status` tinyint(4) NOT NULL default 0,
1793 `planneddate` date default NULL,
1795 `publisheddate` date default NULL,
1796 `itemnumber` text default NULL,
1797 `claimdate` date default NULL,
1798 `routingnotes` text,
1799 PRIMARY KEY (`serialid`)
1800 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1803 -- Table structure for table `sessions`
1806 DROP TABLE IF EXISTS sessions;
1807 CREATE TABLE sessions (
1808 `id` varchar(32) NOT NULL,
1809 `a_session` text NOT NULL,
1811 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1814 -- Table structure for table `special_holidays`
1817 DROP TABLE IF EXISTS `special_holidays`;
1818 CREATE TABLE `special_holidays` (
1819 `id` int(11) NOT NULL auto_increment,
1820 `branchcode` varchar(10) NOT NULL default '',
1821 `day` smallint(6) NOT NULL default 0,
1822 `month` smallint(6) NOT NULL default 0,
1823 `year` smallint(6) NOT NULL default 0,
1824 `isexception` smallint(1) NOT NULL default 1,
1825 `title` varchar(50) NOT NULL default '',
1826 `description` text NOT NULL,
1828 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1831 -- Table structure for table `statistics`
1834 DROP TABLE IF EXISTS `statistics`;
1835 CREATE TABLE `statistics` (
1836 `datetime` datetime default NULL,
1837 `branch` varchar(10) default NULL,
1838 `proccode` varchar(4) default NULL,
1839 `value` double(16,4) default NULL,
1840 `type` varchar(16) default NULL,
1842 `usercode` varchar(10) default NULL,
1843 `itemnumber` int(11) default NULL,
1844 `itemtype` varchar(10) default NULL,
1845 `borrowernumber` int(11) default NULL,
1846 `associatedborrower` int(11) default NULL,
1847 KEY `timeidx` (`datetime`)
1848 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1851 -- Table structure for table `stopwords`
1854 DROP TABLE IF EXISTS `stopwords`;
1855 CREATE TABLE `stopwords` (
1856 `word` varchar(255) default NULL
1857 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1860 -- Table structure for table `subscription`
1863 DROP TABLE IF EXISTS `subscription`;
1864 CREATE TABLE `subscription` (
1865 `biblionumber` int(11) NOT NULL default 0,
1866 `subscriptionid` int(11) NOT NULL auto_increment,
1867 `librarian` varchar(100) default '',
1868 `startdate` date default NULL,
1869 `aqbooksellerid` int(11) default 0,
1870 `cost` int(11) default 0,
1871 `aqbudgetid` int(11) default 0,
1872 `weeklength` int(11) default 0,
1873 `monthlength` int(11) default 0,
1874 `numberlength` int(11) default 0,
1875 `periodicity` tinyint(4) default 0,
1876 `dow` varchar(100) default '',
1877 `numberingmethod` varchar(100) default '',
1879 `status` varchar(100) NOT NULL default '',
1880 `add1` int(11) default 0,
1881 `every1` int(11) default 0,
1882 `whenmorethan1` int(11) default 0,
1883 `setto1` int(11) default NULL,
1884 `lastvalue1` int(11) default NULL,
1885 `add2` int(11) default 0,
1886 `every2` int(11) default 0,
1887 `whenmorethan2` int(11) default 0,
1888 `setto2` int(11) default NULL,
1889 `lastvalue2` int(11) default NULL,
1890 `add3` int(11) default 0,
1891 `every3` int(11) default 0,
1892 `innerloop1` int(11) default 0,
1893 `innerloop2` int(11) default 0,
1894 `innerloop3` int(11) default 0,
1895 `whenmorethan3` int(11) default 0,
1896 `setto3` int(11) default NULL,
1897 `lastvalue3` int(11) default NULL,
1898 `issuesatonce` tinyint(3) NOT NULL default 1,
1899 `firstacquidate` date default NULL,
1900 `manualhistory` tinyint(1) NOT NULL default 0,
1901 `irregularity` text,
1902 `letter` varchar(20) default NULL,
1903 `numberpattern` tinyint(3) default 0,
1904 `distributedto` text,
1905 `internalnotes` longtext,
1907 `location` varchar(80) NULL default '',
1908 `branchcode` varchar(10) NOT NULL default '',
1909 `hemisphere` tinyint(3) default 0,
1910 `lastbranch` varchar(10),
1911 `serialsadditems` tinyint(1) NOT NULL default '0',
1912 `staffdisplaycount` VARCHAR(10) NULL,
1913 `opacdisplaycount` VARCHAR(10) NULL,
1914 `graceperiod` int(11) NOT NULL default '0',
1915 PRIMARY KEY (`subscriptionid`)
1916 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1919 -- Table structure for table `subscriptionhistory`
1922 DROP TABLE IF EXISTS `subscriptionhistory`;
1923 CREATE TABLE `subscriptionhistory` (
1924 `biblionumber` int(11) NOT NULL default 0,
1925 `subscriptionid` int(11) NOT NULL default 0,
1926 `histstartdate` date default NULL,
1927 `enddate` date default NULL,
1928 `missinglist` longtext NOT NULL,
1929 `recievedlist` longtext NOT NULL,
1930 `opacnote` varchar(150) NOT NULL default '',
1931 `librariannote` varchar(150) NOT NULL default '',
1932 PRIMARY KEY (`subscriptionid`),
1933 KEY `biblionumber` (`biblionumber`)
1934 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1937 -- Table structure for table `subscriptionroutinglist`
1940 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1941 CREATE TABLE `subscriptionroutinglist` (
1942 `routingid` int(11) NOT NULL auto_increment,
1943 `borrowernumber` int(11) default NULL,
1944 `ranking` int(11) default NULL,
1945 `subscriptionid` int(11) default NULL,
1946 PRIMARY KEY (`routingid`)
1947 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1950 -- Table structure for table `suggestions`
1953 DROP TABLE IF EXISTS `suggestions`;
1954 CREATE TABLE `suggestions` (
1955 `suggestionid` int(8) NOT NULL auto_increment,
1956 `suggestedby` int(11) NOT NULL default 0,
1957 `managedby` int(11) default NULL,
1958 `STATUS` varchar(10) NOT NULL default '',
1960 `author` varchar(80) default NULL,
1961 `title` varchar(80) default NULL,
1962 `copyrightdate` smallint(6) default NULL,
1963 `publishercode` varchar(255) default NULL,
1964 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1965 `volumedesc` varchar(255) default NULL,
1966 `publicationyear` smallint(6) default 0,
1967 `place` varchar(255) default NULL,
1968 `isbn` varchar(30) default NULL,
1969 `mailoverseeing` smallint(1) default 0,
1970 `biblionumber` int(11) default NULL,
1972 PRIMARY KEY (`suggestionid`),
1973 KEY `suggestedby` (`suggestedby`),
1974 KEY `managedby` (`managedby`)
1975 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1978 -- Table structure for table `systempreferences`
1981 DROP TABLE IF EXISTS `systempreferences`;
1982 CREATE TABLE `systempreferences` (
1983 `variable` varchar(50) NOT NULL default '',
1985 `options` mediumtext,
1987 `type` varchar(20) default NULL,
1988 PRIMARY KEY (`variable`)
1989 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1992 -- Table structure for table `tags`
1995 DROP TABLE IF EXISTS `tags`;
1996 CREATE TABLE `tags` (
1997 `entry` varchar(255) NOT NULL default '',
1998 `weight` bigint(20) NOT NULL default 0,
1999 PRIMARY KEY (`entry`)
2000 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2003 -- Table structure for table `tags_all`
2006 DROP TABLE IF EXISTS `tags_all`;
2007 CREATE TABLE `tags_all` (
2008 `tag_id` int(11) NOT NULL auto_increment,
2009 `borrowernumber` int(11) NOT NULL,
2010 `biblionumber` int(11) NOT NULL,
2011 `term` varchar(255) NOT NULL,
2012 `language` int(4) default NULL,
2013 `date_created` datetime NOT NULL,
2014 PRIMARY KEY (`tag_id`),
2015 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2016 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2017 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2018 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2019 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2020 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2021 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2024 -- Table structure for table `tags_approval`
2027 DROP TABLE IF EXISTS `tags_approval`;
2028 CREATE TABLE `tags_approval` (
2029 `term` varchar(255) NOT NULL,
2030 `approved` int(1) NOT NULL default '0',
2031 `date_approved` datetime default NULL,
2032 `approved_by` int(11) default NULL,
2033 `weight_total` int(9) NOT NULL default '1',
2034 PRIMARY KEY (`term`),
2035 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2036 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2037 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2038 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2041 -- Table structure for table `tags_index`
2044 DROP TABLE IF EXISTS `tags_index`;
2045 CREATE TABLE `tags_index` (
2046 `term` varchar(255) NOT NULL,
2047 `biblionumber` int(11) NOT NULL,
2048 `weight` int(9) NOT NULL default '1',
2049 PRIMARY KEY (`term`,`biblionumber`),
2050 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2051 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2052 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2053 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2054 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2055 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2058 -- Table structure for table `userflags`
2061 DROP TABLE IF EXISTS `userflags`;
2062 CREATE TABLE `userflags` (
2063 `bit` int(11) NOT NULL default 0,
2064 `flag` varchar(30) default NULL,
2065 `flagdesc` varchar(255) default NULL,
2066 `defaulton` int(11) default NULL,
2068 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2071 -- Table structure for table `virtualshelves`
2074 DROP TABLE IF EXISTS `virtualshelves`;
2075 CREATE TABLE `virtualshelves` (
2076 `shelfnumber` int(11) NOT NULL auto_increment,
2077 `shelfname` varchar(255) default NULL,
2078 `owner` varchar(80) default NULL,
2079 `category` varchar(1) default NULL,
2080 `sortfield` varchar(16) default NULL,
2081 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2082 PRIMARY KEY (`shelfnumber`)
2083 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2086 -- Table structure for table `virtualshelfcontents`
2089 DROP TABLE IF EXISTS `virtualshelfcontents`;
2090 CREATE TABLE `virtualshelfcontents` (
2091 `shelfnumber` int(11) NOT NULL default 0,
2092 `biblionumber` int(11) NOT NULL default 0,
2093 `flags` int(11) default NULL,
2094 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2095 KEY `shelfnumber` (`shelfnumber`),
2096 KEY `biblionumber` (`biblionumber`),
2097 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2098 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2099 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2102 -- Table structure for table `z3950servers`
2105 DROP TABLE IF EXISTS `z3950servers`;
2106 CREATE TABLE `z3950servers` (
2107 `host` varchar(255) default NULL,
2108 `port` int(11) default NULL,
2109 `db` varchar(255) default NULL,
2110 `userid` varchar(255) default NULL,
2111 `password` varchar(255) default NULL,
2113 `id` int(11) NOT NULL auto_increment,
2114 `checked` smallint(6) default NULL,
2115 `rank` int(11) default NULL,
2116 `syntax` varchar(80) default NULL,
2118 `position` enum('primary','secondary','') NOT NULL default 'primary',
2119 `type` enum('zed','opensearch') NOT NULL default 'zed',
2120 `encoding` text default NULL,
2121 `description` text NOT NULL,
2123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2126 -- Table structure for table `zebraqueue`
2129 DROP TABLE IF EXISTS `zebraqueue`;
2130 CREATE TABLE `zebraqueue` (
2131 `id` int(11) NOT NULL auto_increment,
2132 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2133 `operation` char(20) NOT NULL default '',
2134 `server` char(20) NOT NULL default '',
2135 `done` int(11) NOT NULL default '0',
2136 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2138 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2139 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2141 DROP TABLE IF EXISTS `services_throttle`;
2142 CREATE TABLE `services_throttle` (
2143 `service_type` varchar(10) NOT NULL default '',
2144 `service_count` varchar(45) default NULL,
2145 PRIMARY KEY (`service_type`)
2146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2148 -- http://www.w3.org/International/articles/language-tags/
2151 DROP TABLE IF EXISTS language_subtag_registry;
2152 CREATE TABLE language_subtag_registry (
2154 type varchar(25), -- language-script-region-variant-extension-privateuse
2155 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2157 KEY `subtag` (`subtag`)
2158 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2160 -- TODO: add suppress_scripts
2161 -- this maps three letter codes defined in iso639.2 back to their
2162 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2163 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2164 CREATE TABLE language_rfc4646_to_iso639 (
2165 rfc4646_subtag varchar(25),
2166 iso639_2_code varchar(25),
2167 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2170 DROP TABLE IF EXISTS language_descriptions;
2171 CREATE TABLE language_descriptions (
2175 description varchar(255),
2177 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2179 -- bi-directional support, keyed by script subcode
2180 DROP TABLE IF EXISTS language_script_bidi;
2181 CREATE TABLE language_script_bidi (
2182 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2183 bidi varchar(3), -- rtl ltr
2184 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2187 -- TODO: need to map language subtags to script subtags for detection
2188 -- of bidi when script is not specified (like ar, he)
2189 DROP TABLE IF EXISTS language_script_mapping;
2190 CREATE TABLE language_script_mapping (
2191 language_subtag varchar(25),
2192 script_subtag varchar(25),
2193 KEY `language_subtag` (`language_subtag`)
2194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2196 DROP TABLE IF EXISTS `permissions`;
2197 CREATE TABLE `permissions` (
2198 `module_bit` int(11) NOT NULL DEFAULT 0,
2199 `code` varchar(64) DEFAULT NULL,
2200 `description` varchar(255) DEFAULT NULL,
2201 PRIMARY KEY (`module_bit`, `code`),
2202 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2203 ON DELETE CASCADE ON UPDATE CASCADE
2204 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2206 DROP TABLE IF EXISTS `serialitems`;
2207 CREATE TABLE `serialitems` (
2208 `itemnumber` int(11) NOT NULL,
2209 `serialid` int(11) NOT NULL,
2210 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2211 KEY `serialitems_sfk_1` (`serialid`),
2212 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2215 DROP TABLE IF EXISTS `user_permissions`;
2216 CREATE TABLE `user_permissions` (
2217 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2218 `module_bit` int(11) NOT NULL DEFAULT 0,
2219 `code` varchar(64) DEFAULT NULL,
2220 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2221 ON DELETE CASCADE ON UPDATE CASCADE,
2222 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2223 ON DELETE CASCADE ON UPDATE CASCADE
2224 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2227 -- Table structure for table `tmp_holdsqueue`
2230 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2231 CREATE TABLE `tmp_holdsqueue` (
2232 `biblionumber` int(11) default NULL,
2233 `itemnumber` int(11) default NULL,
2234 `barcode` varchar(20) default NULL,
2235 `surname` mediumtext NOT NULL,
2238 `borrowernumber` int(11) NOT NULL,
2239 `cardnumber` varchar(16) default NULL,
2240 `reservedate` date default NULL,
2242 `itemcallnumber` varchar(255) default NULL,
2243 `holdingbranch` varchar(10) default NULL,
2244 `pickbranch` varchar(10) default NULL,
2246 `item_level_request` tinyint(4) NOT NULL default 0
2247 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2250 -- Table structure for table `message_queue`
2253 DROP TABLE IF EXISTS `message_queue`;
2254 CREATE TABLE `message_queue` (
2255 `message_id` int(11) NOT NULL auto_increment,
2256 `borrowernumber` int(11) default NULL,
2259 `metadata` text DEFAULT NULL,
2260 `letter_code` varchar(64) DEFAULT NULL,
2261 `message_transport_type` varchar(20) NOT NULL,
2262 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2263 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2264 `to_address` mediumtext,
2265 `from_address` mediumtext,
2266 `content_type` text,
2267 KEY `message_id` (`message_id`),
2268 KEY `borrowernumber` (`borrowernumber`),
2269 KEY `message_transport_type` (`message_transport_type`),
2270 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2271 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2275 -- Table structure for table `message_transport_types`
2278 DROP TABLE IF EXISTS `message_transport_types`;
2279 CREATE TABLE `message_transport_types` (
2280 `message_transport_type` varchar(20) NOT NULL,
2281 PRIMARY KEY (`message_transport_type`)
2282 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2285 -- Table structure for table `message_attributes`
2288 DROP TABLE IF EXISTS `message_attributes`;
2289 CREATE TABLE `message_attributes` (
2290 `message_attribute_id` int(11) NOT NULL auto_increment,
2291 `message_name` varchar(20) NOT NULL default '',
2292 `takes_days` tinyint(1) NOT NULL default '0',
2293 PRIMARY KEY (`message_attribute_id`),
2294 UNIQUE KEY `message_name` (`message_name`)
2295 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2298 -- Table structure for table `message_transports`
2301 DROP TABLE IF EXISTS `message_transports`;
2302 CREATE TABLE `message_transports` (
2303 `message_attribute_id` int(11) NOT NULL,
2304 `message_transport_type` varchar(20) NOT NULL,
2305 `is_digest` tinyint(1) NOT NULL default '0',
2306 `letter_module` varchar(20) NOT NULL default '',
2307 `letter_code` varchar(20) NOT NULL default '',
2308 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2309 KEY `message_transport_type` (`message_transport_type`),
2310 KEY `letter_module` (`letter_module`,`letter_code`),
2311 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2312 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2313 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2317 -- Table structure for table `borrower_message_preferences`
2320 DROP TABLE IF EXISTS `borrower_message_preferences`;
2321 CREATE TABLE `borrower_message_preferences` (
2322 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2323 `borrowernumber` int(11) default NULL,
2324 `categorycode` varchar(10) default NULL,
2325 `message_attribute_id` int(11) default '0',
2326 `days_in_advance` int(11) default '0',
2327 `wants_digest` tinyint(1) NOT NULL default '0',
2328 PRIMARY KEY (`borrower_message_preference_id`),
2329 KEY `borrowernumber` (`borrowernumber`),
2330 KEY `categorycode` (`categorycode`),
2331 KEY `message_attribute_id` (`message_attribute_id`),
2332 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2333 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2334 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2335 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2338 -- Table structure for table `borrower_message_transport_preferences`
2341 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2342 CREATE TABLE `borrower_message_transport_preferences` (
2343 `borrower_message_preference_id` int(11) NOT NULL default '0',
2344 `message_transport_type` varchar(20) NOT NULL default '0',
2345 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2346 KEY `message_transport_type` (`message_transport_type`),
2347 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,
2348 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
2349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2352 -- Table structure for the table branch_transfer_limits
2355 DROP TABLE IF EXISTS `branch_transfer_limits`;
2356 CREATE TABLE branch_transfer_limits (
2357 limitId int(8) NOT NULL auto_increment,
2358 toBranch varchar(10) NOT NULL,
2359 fromBranch varchar(10) NOT NULL,
2360 itemtype varchar(10) NULL,
2361 ccode varchar(10) NULL,
2362 PRIMARY KEY (limitId)
2363 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2366 -- Table structure for table `item_circulation_alert_preferences`
2369 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2370 CREATE TABLE `item_circulation_alert_preferences` (
2371 `id` int(11) NOT NULL auto_increment,
2372 `branchcode` varchar(10) NOT NULL,
2373 `categorycode` varchar(10) NOT NULL,
2374 `item_type` varchar(10) NOT NULL,
2375 `notification` varchar(16) NOT NULL,
2377 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2380 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2381 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2382 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2383 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2384 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2385 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2386 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2387 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;