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,
867 `mobile` varchar(50) default NULL,
871 `B_streetnumber` varchar(10) default NULL,
872 `B_streettype` varchar(50) default NULL,
873 `B_address` varchar(100) default NULL,
875 `B_zipcode` varchar(25) default NULL,
877 `B_phone` mediumtext,
878 `dateofbirth` date default NULL,
879 `branchcode` varchar(10) NOT NULL default '',
880 `categorycode` varchar(10) default NULL,
881 `dateenrolled` date default NULL,
882 `dateexpiry` date default NULL,
883 `gonenoaddress` tinyint(1) default NULL,
884 `lost` tinyint(1) default NULL,
885 `debarred` tinyint(1) default NULL,
886 `contactname` mediumtext,
887 `contactfirstname` text,
889 `guarantorid` int(11) default NULL,
890 `borrowernotes` mediumtext,
891 `relationship` varchar(100) default NULL,
892 `ethnicity` varchar(50) default NULL,
893 `ethnotes` varchar(255) default NULL,
894 `sex` varchar(1) default NULL,
895 `password` varchar(30) default NULL,
896 `flags` int(11) default NULL,
897 `userid` varchar(30) default NULL,
898 `opacnote` mediumtext,
899 `contactnote` varchar(255) default NULL,
900 `sort1` varchar(80) default NULL,
901 `sort2` varchar(80) default NULL,
902 `altcontactfirstname` varchar(255) default NULL,
903 `altcontactsurname` varchar(255) default NULL,
904 `altcontactaddress1` varchar(255) default NULL,
905 `altcontactaddress2` varchar(255) default NULL,
906 `altcontactaddress3` varchar(255) default NULL,
907 `altcontactzipcode` varchar(50) default NULL,
908 `altcontactphone` varchar(50) default NULL,
909 `smsalertnumber` varchar(50) default NULL,
910 KEY `borrowernumber` (`borrowernumber`),
911 KEY `cardnumber` (`cardnumber`)
912 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
915 -- Table structure for table `deleteditems`
918 DROP TABLE IF EXISTS `deleteditems`;
919 CREATE TABLE `deleteditems` (
920 `itemnumber` int(11) NOT NULL default 0,
921 `biblionumber` int(11) NOT NULL default 0,
922 `biblioitemnumber` int(11) NOT NULL default 0,
923 `barcode` varchar(20) default NULL,
924 `dateaccessioned` date default NULL,
925 `booksellerid` mediumtext default NULL,
926 `homebranch` varchar(10) default NULL,
927 `price` decimal(8,2) default NULL,
928 `replacementprice` decimal(8,2) default NULL,
929 `replacementpricedate` date default NULL,
930 `datelastborrowed` date default NULL,
931 `datelastseen` date default NULL,
932 `stack` tinyint(1) default NULL,
933 `notforloan` tinyint(1) NOT NULL default 0,
934 `damaged` tinyint(1) NOT NULL default 0,
935 `itemlost` tinyint(1) NOT NULL default 0,
936 `wthdrawn` tinyint(1) NOT NULL default 0,
937 `itemcallnumber` varchar(30) default NULL,
938 `issues` smallint(6) default NULL,
939 `renewals` smallint(6) default NULL,
940 `reserves` smallint(6) default NULL,
941 `restricted` tinyint(1) default NULL,
942 `itemnotes` mediumtext,
943 `holdingbranch` varchar(10) default NULL,
944 `paidfor` mediumtext,
945 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
946 `location` varchar(80) default NULL,
947 `onloan` date default NULL,
948 `cn_source` varchar(10) default NULL,
949 `cn_sort` varchar(30) default NULL,
950 `ccode` varchar(10) default NULL,
951 `materials` varchar(10) default NULL,
952 `uri` varchar(255) default NULL,
953 `itype` varchar(10) default NULL,
954 `more_subfields_xml` longtext default NULL,
955 `enumchron` varchar(80) default NULL,
956 `copynumber` varchar(32) default NULL,
958 PRIMARY KEY (`itemnumber`),
959 KEY `delitembarcodeidx` (`barcode`),
960 KEY `delitembinoidx` (`biblioitemnumber`),
961 KEY `delitembibnoidx` (`biblionumber`),
962 KEY `delhomebranch` (`homebranch`),
963 KEY `delholdingbranch` (`holdingbranch`)
964 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
967 -- Table structure for table `ethnicity`
970 DROP TABLE IF EXISTS `ethnicity`;
971 CREATE TABLE `ethnicity` (
972 `code` varchar(10) NOT NULL default '',
973 `name` varchar(255) default NULL,
975 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
978 -- Table structure for table `hold_fill_targets`
981 DROP TABLE IF EXISTS `hold_fill_targets`;
982 CREATE TABLE hold_fill_targets (
983 `borrowernumber` int(11) NOT NULL,
984 `biblionumber` int(11) NOT NULL,
985 `itemnumber` int(11) NOT NULL,
986 `source_branchcode` varchar(10) default NULL,
987 `item_level_request` tinyint(4) NOT NULL default 0,
988 PRIMARY KEY `itemnumber` (`itemnumber`),
989 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
990 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
991 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
992 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
993 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
994 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
995 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
996 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
997 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
998 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1001 -- Table structure for table `import_batches`
1004 DROP TABLE IF EXISTS `import_batches`;
1005 CREATE TABLE `import_batches` (
1006 `import_batch_id` int(11) NOT NULL auto_increment,
1007 `matcher_id` int(11) default NULL,
1008 `template_id` int(11) default NULL,
1009 `branchcode` varchar(10) default NULL,
1010 `num_biblios` int(11) NOT NULL default 0,
1011 `num_items` int(11) NOT NULL default 0,
1012 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1013 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1014 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1015 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1016 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1017 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1018 `file_name` varchar(100),
1019 `comments` mediumtext,
1020 PRIMARY KEY (`import_batch_id`),
1021 KEY `branchcode` (`branchcode`)
1022 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1025 -- Table structure for table `import_records`
1028 DROP TABLE IF EXISTS `import_records`;
1029 CREATE TABLE `import_records` (
1030 `import_record_id` int(11) NOT NULL auto_increment,
1031 `import_batch_id` int(11) NOT NULL,
1032 `branchcode` varchar(10) default NULL,
1033 `record_sequence` int(11) NOT NULL default 0,
1034 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1035 `import_date` DATE default NULL,
1036 `marc` longblob NOT NULL,
1037 `marcxml` longtext NOT NULL,
1038 `marcxml_old` longtext NOT NULL,
1039 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1040 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1041 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1042 `import_error` mediumtext,
1043 `encoding` varchar(40) NOT NULL default '',
1044 `z3950random` varchar(40) default NULL,
1045 PRIMARY KEY (`import_record_id`),
1046 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1047 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1048 KEY `branchcode` (`branchcode`),
1049 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1050 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1053 -- Table structure for `import_record_matches`
1055 DROP TABLE IF EXISTS `import_record_matches`;
1056 CREATE TABLE `import_record_matches` (
1057 `import_record_id` int(11) NOT NULL,
1058 `candidate_match_id` int(11) NOT NULL,
1059 `score` int(11) NOT NULL default 0,
1060 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1061 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1062 KEY `record_score` (`import_record_id`, `score`)
1063 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1066 -- Table structure for table `import_biblios`
1069 DROP TABLE IF EXISTS `import_biblios`;
1070 CREATE TABLE `import_biblios` (
1071 `import_record_id` int(11) NOT NULL,
1072 `matched_biblionumber` int(11) default NULL,
1073 `control_number` varchar(25) default NULL,
1074 `original_source` varchar(25) default NULL,
1075 `title` varchar(128) default NULL,
1076 `author` varchar(80) default NULL,
1077 `isbn` varchar(30) default NULL,
1078 `issn` varchar(9) default NULL,
1079 `has_items` tinyint(1) NOT NULL default 0,
1080 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1081 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1082 KEY `matched_biblionumber` (`matched_biblionumber`),
1083 KEY `title` (`title`),
1085 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1088 -- Table structure for table `import_items`
1091 DROP TABLE IF EXISTS `import_items`;
1092 CREATE TABLE `import_items` (
1093 `import_items_id` int(11) NOT NULL auto_increment,
1094 `import_record_id` int(11) NOT NULL,
1095 `itemnumber` int(11) default NULL,
1096 `branchcode` varchar(10) default NULL,
1097 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1098 `marcxml` longtext NOT NULL,
1099 `import_error` mediumtext,
1100 PRIMARY KEY (`import_items_id`),
1101 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1102 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1103 KEY `itemnumber` (`itemnumber`),
1104 KEY `branchcode` (`branchcode`)
1105 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1108 -- Table structure for table `issues`
1111 DROP TABLE IF EXISTS `issues`;
1112 CREATE TABLE `issues` (
1113 `borrowernumber` int(11) default NULL,
1114 `itemnumber` int(11) default NULL,
1115 `date_due` date default NULL,
1116 `branchcode` varchar(10) default NULL,
1117 `issuingbranch` varchar(18) default NULL,
1118 `returndate` date default NULL,
1119 `lastreneweddate` date default NULL,
1120 `return` varchar(4) default NULL,
1121 `renewals` tinyint(4) default NULL,
1122 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1123 `issuedate` date default NULL,
1124 KEY `issuesborridx` (`borrowernumber`),
1125 KEY `issuesitemidx` (`itemnumber`),
1126 KEY `bordate` (`borrowernumber`,`timestamp`),
1127 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1128 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1132 -- Table structure for table `issuingrules`
1135 DROP TABLE IF EXISTS `issuingrules`;
1136 CREATE TABLE `issuingrules` (
1137 `categorycode` varchar(10) NOT NULL default '',
1138 `itemtype` varchar(10) NOT NULL default '',
1139 `restrictedtype` tinyint(1) default NULL,
1140 `rentaldiscount` decimal(28,6) default NULL,
1141 `reservecharge` decimal(28,6) default NULL,
1142 `fine` decimal(28,6) default NULL,
1143 `firstremind` int(11) default NULL,
1144 `chargeperiod` int(11) default NULL,
1145 `accountsent` int(11) default NULL,
1146 `chargename` varchar(100) default NULL,
1147 `maxissueqty` int(4) default NULL,
1148 `issuelength` int(4) default NULL,
1149 `branchcode` varchar(10) NOT NULL default '',
1150 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1151 KEY `categorycode` (`categorycode`),
1152 KEY `itemtype` (`itemtype`)
1153 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1156 -- Table structure for table `items`
1159 DROP TABLE IF EXISTS `items`;
1160 CREATE TABLE `items` (
1161 `itemnumber` int(11) NOT NULL auto_increment,
1162 `biblionumber` int(11) NOT NULL default 0,
1163 `biblioitemnumber` int(11) NOT NULL default 0,
1164 `barcode` varchar(20) default NULL,
1165 `dateaccessioned` date default NULL,
1166 `booksellerid` mediumtext default NULL,
1167 `homebranch` varchar(10) default NULL,
1168 `price` decimal(8,2) default NULL,
1169 `replacementprice` decimal(8,2) default NULL,
1170 `replacementpricedate` date default NULL,
1171 `datelastborrowed` date default NULL,
1172 `datelastseen` date default NULL,
1173 `stack` tinyint(1) default NULL,
1174 `notforloan` tinyint(1) NOT NULL default 0,
1175 `damaged` tinyint(1) NOT NULL default 0,
1176 `itemlost` tinyint(1) NOT NULL default 0,
1177 `wthdrawn` tinyint(1) NOT NULL default 0,
1178 `itemcallnumber` varchar(30) default NULL,
1179 `issues` smallint(6) default NULL,
1180 `renewals` smallint(6) default NULL,
1181 `reserves` smallint(6) default NULL,
1182 `restricted` tinyint(1) default NULL,
1183 `itemnotes` mediumtext,
1184 `holdingbranch` varchar(10) default NULL,
1185 `paidfor` mediumtext,
1186 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1187 `location` varchar(80) default NULL,
1188 `permanent_location` varchar(80) default NULL,
1189 `onloan` date default NULL,
1190 `cn_source` varchar(10) default NULL,
1191 `cn_sort` varchar(30) default NULL,
1192 `ccode` varchar(10) default NULL,
1193 `materials` varchar(10) default NULL,
1194 `uri` varchar(255) default NULL,
1195 `itype` varchar(10) default NULL,
1196 `more_subfields_xml` longtext default NULL,
1197 `enumchron` varchar(80) default NULL,
1198 `copynumber` varchar(32) default NULL,
1199 PRIMARY KEY (`itemnumber`),
1200 UNIQUE KEY `itembarcodeidx` (`barcode`),
1201 KEY `itembinoidx` (`biblioitemnumber`),
1202 KEY `itembibnoidx` (`biblionumber`),
1203 KEY `homebranch` (`homebranch`),
1204 KEY `holdingbranch` (`holdingbranch`),
1205 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1206 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1207 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1211 -- Table structure for table `itemtypes`
1214 DROP TABLE IF EXISTS `itemtypes`;
1215 CREATE TABLE `itemtypes` (
1216 `itemtype` varchar(10) NOT NULL default '',
1217 `description` mediumtext,
1218 `renewalsallowed` smallint(6) default NULL,
1219 `rentalcharge` double(16,4) default NULL,
1220 `notforloan` smallint(6) default NULL,
1221 `imageurl` varchar(200) default NULL,
1223 PRIMARY KEY (`itemtype`),
1224 UNIQUE KEY `itemtype` (`itemtype`)
1225 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1228 -- Table structure for table `labels`
1231 DROP TABLE IF EXISTS `labels`;
1232 CREATE TABLE `labels` (
1233 `labelid` int(11) NOT NULL auto_increment,
1234 `batch_id` int(10) NOT NULL default 1,
1235 `itemnumber` varchar(100) NOT NULL default '',
1236 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1237 PRIMARY KEY (`labelid`)
1238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1241 -- Table structure for table `labels_conf`
1244 DROP TABLE IF EXISTS `labels_conf`;
1245 CREATE TABLE `labels_conf` (
1246 `id` int(4) NOT NULL auto_increment,
1247 `barcodetype` char(100) default '',
1248 `title` int(1) default '0',
1249 `subtitle` int(1) default '0',
1250 `itemtype` int(1) default '0',
1251 `barcode` int(1) default '0',
1252 `dewey` int(1) default '0',
1253 `classification` int(1) default NULL,
1254 `subclass` int(1) default '0',
1255 `itemcallnumber` int(1) default '0',
1256 `author` int(1) default '0',
1257 `issn` int(1) default '0',
1258 `isbn` int(1) default '0',
1259 `startlabel` int(2) NOT NULL default '1',
1260 `printingtype` char(32) default 'BAR',
1261 `formatstring` mediumtext default NULL,
1262 `layoutname` char(20) NOT NULL default 'TEST',
1263 `guidebox` int(1) default '0',
1264 `active` tinyint(1) default '1',
1265 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1266 `ccode` char(4) collate utf8_unicode_ci default NULL,
1267 `callnum_split` int(1) default NULL,
1268 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1270 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1273 -- Table structure for table `labels_profile`
1276 DROP TABLE IF EXISTS `labels_profile`;
1277 CREATE TABLE `labels_profile` (
1278 `tmpl_id` int(4) NOT NULL,
1279 `prof_id` int(4) NOT NULL,
1280 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1281 UNIQUE KEY `prof_id` (`prof_id`)
1282 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1285 -- Table structure for table `labels_templates`
1288 DROP TABLE IF EXISTS `labels_templates`;
1289 CREATE TABLE `labels_templates` (
1290 `tmpl_id` int(4) NOT NULL auto_increment,
1291 `tmpl_code` char(100) default '',
1292 `tmpl_desc` char(100) default '',
1293 `page_width` float default '0',
1294 `page_height` float default '0',
1295 `label_width` float default '0',
1296 `label_height` float default '0',
1297 `topmargin` float default '0',
1298 `leftmargin` float default '0',
1299 `cols` int(2) default '0',
1300 `rows` int(2) default '0',
1301 `colgap` float default '0',
1302 `rowgap` float default '0',
1303 `active` int(1) default NULL,
1304 `units` char(20) default 'PX',
1305 `fontsize` int(4) NOT NULL default '3',
1306 `font` char(10) NOT NULL default 'TR',
1307 PRIMARY KEY (`tmpl_id`)
1308 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1311 -- Table structure for table `letter`
1314 DROP TABLE IF EXISTS `letter`;
1315 CREATE TABLE `letter` (
1316 `module` varchar(20) NOT NULL default '',
1317 `code` varchar(20) NOT NULL default '',
1318 `name` varchar(100) NOT NULL default '',
1319 `title` varchar(200) NOT NULL default '',
1321 PRIMARY KEY (`module`,`code`)
1322 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1325 -- Table structure for table `marc_subfield_structure`
1328 DROP TABLE IF EXISTS `marc_subfield_structure`;
1329 CREATE TABLE `marc_subfield_structure` (
1330 `tagfield` varchar(3) NOT NULL default '',
1331 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1332 `liblibrarian` varchar(255) NOT NULL default '',
1333 `libopac` varchar(255) NOT NULL default '',
1334 `repeatable` tinyint(4) NOT NULL default 0,
1335 `mandatory` tinyint(4) NOT NULL default 0,
1336 `kohafield` varchar(40) default NULL,
1337 `tab` tinyint(1) default NULL,
1338 `authorised_value` varchar(20) default NULL,
1339 `authtypecode` varchar(20) default NULL,
1340 `value_builder` varchar(80) default NULL,
1341 `isurl` tinyint(1) default NULL,
1342 `hidden` tinyint(1) default NULL,
1343 `frameworkcode` varchar(4) NOT NULL default '',
1344 `seealso` varchar(1100) default NULL,
1345 `link` varchar(80) default NULL,
1346 `defaultvalue` text default NULL,
1347 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1348 KEY `kohafield_2` (`kohafield`),
1349 KEY `tab` (`frameworkcode`,`tab`),
1350 KEY `kohafield` (`frameworkcode`,`kohafield`)
1351 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1354 -- Table structure for table `marc_tag_structure`
1357 DROP TABLE IF EXISTS `marc_tag_structure`;
1358 CREATE TABLE `marc_tag_structure` (
1359 `tagfield` varchar(3) NOT NULL default '',
1360 `liblibrarian` varchar(255) NOT NULL default '',
1361 `libopac` varchar(255) NOT NULL default '',
1362 `repeatable` tinyint(4) NOT NULL default 0,
1363 `mandatory` tinyint(4) NOT NULL default 0,
1364 `authorised_value` varchar(10) default NULL,
1365 `frameworkcode` varchar(4) NOT NULL default '',
1366 PRIMARY KEY (`frameworkcode`,`tagfield`)
1367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1370 -- Table structure for table `marc_matchers`
1373 DROP TABLE IF EXISTS `marc_matchers`;
1374 CREATE TABLE `marc_matchers` (
1375 `matcher_id` int(11) NOT NULL auto_increment,
1376 `code` varchar(10) NOT NULL default '',
1377 `description` varchar(255) NOT NULL default '',
1378 `record_type` varchar(10) NOT NULL default 'biblio',
1379 `threshold` int(11) NOT NULL default 0,
1380 PRIMARY KEY (`matcher_id`),
1381 KEY `code` (`code`),
1382 KEY `record_type` (`record_type`)
1383 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1386 -- Table structure for table `matchpoints`
1388 DROP TABLE IF EXISTS `matchpoints`;
1389 CREATE TABLE `matchpoints` (
1390 `matcher_id` int(11) NOT NULL,
1391 `matchpoint_id` int(11) NOT NULL auto_increment,
1392 `search_index` varchar(30) NOT NULL default '',
1393 `score` int(11) NOT NULL default 0,
1394 PRIMARY KEY (`matchpoint_id`),
1395 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1396 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1397 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1401 -- Table structure for table `matchpoint_components`
1403 DROP TABLE IF EXISTS `matchpoint_components`;
1404 CREATE TABLE `matchpoint_components` (
1405 `matchpoint_id` int(11) NOT NULL,
1406 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1407 sequence int(11) NOT NULL default 0,
1408 tag varchar(3) NOT NULL default '',
1409 subfields varchar(40) NOT NULL default '',
1410 offset int(4) NOT NULL default 0,
1411 length int(4) NOT NULL default 0,
1412 PRIMARY KEY (`matchpoint_component_id`),
1413 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1414 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1415 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1416 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1419 -- Table structure for table `matcher_component_norms`
1421 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1422 CREATE TABLE `matchpoint_component_norms` (
1423 `matchpoint_component_id` int(11) NOT NULL,
1424 `sequence` int(11) NOT NULL default 0,
1425 `norm_routine` varchar(50) NOT NULL default '',
1426 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1427 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1428 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1429 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1432 -- Table structure for table `matcher_matchpoints`
1434 DROP TABLE IF EXISTS `matcher_matchpoints`;
1435 CREATE TABLE `matcher_matchpoints` (
1436 `matcher_id` int(11) NOT NULL,
1437 `matchpoint_id` int(11) NOT NULL,
1438 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1439 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1440 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1441 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1442 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1445 -- Table structure for table `matchchecks`
1447 DROP TABLE IF EXISTS `matchchecks`;
1448 CREATE TABLE `matchchecks` (
1449 `matcher_id` int(11) NOT NULL,
1450 `matchcheck_id` int(11) NOT NULL auto_increment,
1451 `source_matchpoint_id` int(11) NOT NULL,
1452 `target_matchpoint_id` int(11) NOT NULL,
1453 PRIMARY KEY (`matchcheck_id`),
1454 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1455 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1456 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1457 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1458 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1459 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1460 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1463 -- Table structure for table `notifys`
1466 DROP TABLE IF EXISTS `notifys`;
1467 CREATE TABLE `notifys` (
1468 `notify_id` int(11) NOT NULL default 0,
1469 `borrowernumber` int(11) NOT NULL default 0,
1470 `itemnumber` int(11) NOT NULL default 0,
1471 `notify_date` date default NULL,
1472 `notify_send_date` date default NULL,
1473 `notify_level` int(1) NOT NULL default 0,
1474 `method` varchar(20) NOT NULL default ''
1475 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1478 -- Table structure for table `nozebra`
1481 DROP TABLE IF EXISTS `nozebra`;
1482 CREATE TABLE `nozebra` (
1483 `server` varchar(20) NOT NULL,
1484 `indexname` varchar(40) NOT NULL,
1485 `value` varchar(250) NOT NULL,
1486 `biblionumbers` longtext NOT NULL,
1487 KEY `indexname` (`server`,`indexname`),
1488 KEY `value` (`server`,`value`))
1489 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1492 -- Table structure for table `old_issues`
1495 DROP TABLE IF EXISTS `old_issues`;
1496 CREATE TABLE `old_issues` (
1497 `borrowernumber` int(11) default NULL,
1498 `itemnumber` int(11) default NULL,
1499 `date_due` date default NULL,
1500 `branchcode` varchar(10) default NULL,
1501 `issuingbranch` varchar(18) default NULL,
1502 `returndate` date default NULL,
1503 `lastreneweddate` date default NULL,
1504 `return` varchar(4) default NULL,
1505 `renewals` tinyint(4) default NULL,
1506 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1507 `issuedate` date default NULL,
1508 KEY `old_issuesborridx` (`borrowernumber`),
1509 KEY `old_issuesitemidx` (`itemnumber`),
1510 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1511 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1512 ON DELETE SET NULL ON UPDATE SET NULL,
1513 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1514 ON DELETE SET NULL ON UPDATE SET NULL
1515 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1518 -- Table structure for table `old_reserves`
1520 DROP TABLE IF EXISTS `old_reserves`;
1521 CREATE TABLE `old_reserves` (
1522 `borrowernumber` int(11) default NULL,
1523 `reservedate` date default NULL,
1524 `biblionumber` int(11) default NULL,
1525 `constrainttype` varchar(1) default NULL,
1526 `branchcode` varchar(10) default NULL,
1527 `notificationdate` date default NULL,
1528 `reminderdate` date default NULL,
1529 `cancellationdate` date default NULL,
1530 `reservenotes` mediumtext,
1531 `priority` smallint(6) default NULL,
1532 `found` varchar(1) default NULL,
1533 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1534 `itemnumber` int(11) default NULL,
1535 `waitingdate` date default NULL,
1536 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1537 KEY `old_reserves_biblionumber` (`biblionumber`),
1538 KEY `old_reserves_itemnumber` (`itemnumber`),
1539 KEY `old_reserves_branchcode` (`branchcode`),
1540 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1541 ON DELETE SET NULL ON UPDATE SET NULL,
1542 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1543 ON DELETE SET NULL ON UPDATE SET NULL,
1544 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1545 ON DELETE SET NULL ON UPDATE SET NULL
1546 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1549 -- Table structure for table `opac_news`
1552 DROP TABLE IF EXISTS `opac_news`;
1553 CREATE TABLE `opac_news` (
1554 `idnew` int(10) unsigned NOT NULL auto_increment,
1555 `title` varchar(250) NOT NULL default '',
1556 `new` text NOT NULL,
1557 `lang` varchar(25) NOT NULL default '',
1558 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1559 `expirationdate` date default NULL,
1560 `number` int(11) default NULL,
1561 PRIMARY KEY (`idnew`)
1562 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1565 -- Table structure for table `overduerules`
1568 DROP TABLE IF EXISTS `overduerules`;
1569 CREATE TABLE `overduerules` (
1570 `branchcode` varchar(10) NOT NULL default '',
1571 `categorycode` varchar(10) NOT NULL default '',
1572 `delay1` int(4) default 0,
1573 `letter1` varchar(20) default NULL,
1574 `debarred1` varchar(1) default 0,
1575 `delay2` int(4) default 0,
1576 `debarred2` varchar(1) default 0,
1577 `letter2` varchar(20) default NULL,
1578 `delay3` int(4) default 0,
1579 `letter3` varchar(20) default NULL,
1580 `debarred3` int(1) default 0,
1581 PRIMARY KEY (`branchcode`,`categorycode`)
1582 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1585 -- Table structure for table `patroncards`
1588 DROP TABLE IF EXISTS `patroncards`;
1589 CREATE TABLE `patroncards` (
1590 `cardid` int(11) NOT NULL auto_increment,
1591 `batch_id` varchar(10) NOT NULL default '1',
1592 `borrowernumber` int(11) NOT NULL,
1593 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1594 PRIMARY KEY (`cardid`),
1595 KEY `patroncards_ibfk_1` (`borrowernumber`),
1596 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1597 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1600 -- Table structure for table `patronimage`
1603 DROP TABLE IF EXISTS `patronimage`;
1604 CREATE TABLE `patronimage` (
1605 `cardnumber` varchar(16) NOT NULL,
1606 `mimetype` varchar(15) NOT NULL,
1607 `imagefile` mediumblob NOT NULL,
1608 PRIMARY KEY (`cardnumber`),
1609 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1610 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1613 -- Table structure for table `printers`
1616 DROP TABLE IF EXISTS `printers`;
1617 CREATE TABLE `printers` (
1618 `printername` varchar(40) NOT NULL default '',
1619 `printqueue` varchar(20) default NULL,
1620 `printtype` varchar(20) default NULL,
1621 PRIMARY KEY (`printername`)
1622 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1625 -- Table structure for table `printers_profile`
1628 DROP TABLE IF EXISTS `printers_profile`;
1629 CREATE TABLE `printers_profile` (
1630 `prof_id` int(4) NOT NULL auto_increment,
1631 `printername` varchar(40) NOT NULL,
1632 `tmpl_id` int(4) NOT NULL,
1633 `paper_bin` varchar(20) NOT NULL,
1634 `offset_horz` float default NULL,
1635 `offset_vert` float default NULL,
1636 `creep_horz` float default NULL,
1637 `creep_vert` float default NULL,
1638 `unit` char(20) NOT NULL default 'POINT',
1639 PRIMARY KEY (`prof_id`),
1640 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1641 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1642 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1645 -- Table structure for table `repeatable_holidays`
1648 DROP TABLE IF EXISTS `repeatable_holidays`;
1649 CREATE TABLE `repeatable_holidays` (
1650 `id` int(11) NOT NULL auto_increment,
1651 `branchcode` varchar(10) NOT NULL default '',
1652 `weekday` smallint(6) default NULL,
1653 `day` smallint(6) default NULL,
1654 `month` smallint(6) default NULL,
1655 `title` varchar(50) NOT NULL default '',
1656 `description` text NOT NULL,
1658 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1661 -- Table structure for table `reports_dictionary`
1664 DROP TABLE IF EXISTS `reports_dictionary`;
1665 CREATE TABLE reports_dictionary (
1666 `id` int(11) NOT NULL auto_increment,
1667 `name` varchar(255) default NULL,
1669 `date_created` datetime default NULL,
1670 `date_modified` datetime default NULL,
1672 `area` int(11) default NULL,
1674 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1677 -- Table structure for table `reserveconstraints`
1680 DROP TABLE IF EXISTS `reserveconstraints`;
1681 CREATE TABLE `reserveconstraints` (
1682 `borrowernumber` int(11) NOT NULL default 0,
1683 `reservedate` date default NULL,
1684 `biblionumber` int(11) NOT NULL default 0,
1685 `biblioitemnumber` int(11) default NULL,
1686 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1687 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1690 -- Table structure for table `reserves`
1693 DROP TABLE IF EXISTS `reserves`;
1694 CREATE TABLE `reserves` (
1695 `borrowernumber` int(11) NOT NULL default 0,
1696 `reservedate` date default NULL,
1697 `biblionumber` int(11) NOT NULL default 0,
1698 `constrainttype` varchar(1) default NULL,
1699 `branchcode` varchar(10) default NULL,
1700 `notificationdate` date default NULL,
1701 `reminderdate` date default NULL,
1702 `cancellationdate` date default NULL,
1703 `reservenotes` mediumtext,
1704 `priority` smallint(6) default NULL,
1705 `found` varchar(1) default NULL,
1706 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1707 `itemnumber` int(11) default NULL,
1708 `waitingdate` date default NULL,
1709 KEY `borrowernumber` (`borrowernumber`),
1710 KEY `biblionumber` (`biblionumber`),
1711 KEY `itemnumber` (`itemnumber`),
1712 KEY `branchcode` (`branchcode`),
1713 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1714 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1715 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1716 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1717 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1720 -- Table structure for table `reviews`
1723 DROP TABLE IF EXISTS `reviews`;
1724 CREATE TABLE `reviews` (
1725 `reviewid` int(11) NOT NULL auto_increment,
1726 `borrowernumber` int(11) default NULL,
1727 `biblionumber` int(11) default NULL,
1729 `approved` tinyint(4) default NULL,
1730 `datereviewed` datetime default NULL,
1731 PRIMARY KEY (`reviewid`)
1732 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1735 -- Table structure for table `roadtype`
1738 DROP TABLE IF EXISTS `roadtype`;
1739 CREATE TABLE `roadtype` (
1740 `roadtypeid` int(11) NOT NULL auto_increment,
1741 `road_type` varchar(100) NOT NULL default '',
1742 PRIMARY KEY (`roadtypeid`)
1743 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1746 -- Table structure for table `saved_sql`
1749 DROP TABLE IF EXISTS `saved_sql`;
1750 CREATE TABLE saved_sql (
1751 `id` int(11) NOT NULL auto_increment,
1752 `borrowernumber` int(11) default NULL,
1753 `date_created` datetime default NULL,
1754 `last_modified` datetime default NULL,
1756 `last_run` datetime default NULL,
1757 `report_name` varchar(255) default NULL,
1758 `type` varchar(255) default NULL,
1761 KEY boridx (`borrowernumber`)
1762 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1766 -- Table structure for `saved_reports`
1769 DROP TABLE IF EXISTS `saved_reports`;
1770 CREATE TABLE saved_reports (
1771 `id` int(11) NOT NULL auto_increment,
1772 `report_id` int(11) default NULL,
1774 `date_run` datetime default NULL,
1776 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1780 -- Table structure for table `serial`
1783 DROP TABLE IF EXISTS `serial`;
1784 CREATE TABLE `serial` (
1785 `serialid` int(11) NOT NULL auto_increment,
1786 `biblionumber` varchar(100) NOT NULL default '',
1787 `subscriptionid` varchar(100) NOT NULL default '',
1788 `serialseq` varchar(100) NOT NULL default '',
1789 `status` tinyint(4) NOT NULL default 0,
1790 `planneddate` date default NULL,
1792 `publisheddate` date default NULL,
1793 `itemnumber` text default NULL,
1794 `claimdate` date default NULL,
1795 `routingnotes` text,
1796 PRIMARY KEY (`serialid`)
1797 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1800 -- Table structure for table `sessions`
1803 DROP TABLE IF EXISTS sessions;
1804 CREATE TABLE sessions (
1805 `id` varchar(32) NOT NULL,
1806 `a_session` text NOT NULL,
1808 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1811 -- Table structure for table `special_holidays`
1814 DROP TABLE IF EXISTS `special_holidays`;
1815 CREATE TABLE `special_holidays` (
1816 `id` int(11) NOT NULL auto_increment,
1817 `branchcode` varchar(10) NOT NULL default '',
1818 `day` smallint(6) NOT NULL default 0,
1819 `month` smallint(6) NOT NULL default 0,
1820 `year` smallint(6) NOT NULL default 0,
1821 `isexception` smallint(1) NOT NULL default 1,
1822 `title` varchar(50) NOT NULL default '',
1823 `description` text NOT NULL,
1825 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1828 -- Table structure for table `statistics`
1831 DROP TABLE IF EXISTS `statistics`;
1832 CREATE TABLE `statistics` (
1833 `datetime` datetime default NULL,
1834 `branch` varchar(10) default NULL,
1835 `proccode` varchar(4) default NULL,
1836 `value` double(16,4) default NULL,
1837 `type` varchar(16) default NULL,
1839 `usercode` varchar(10) default NULL,
1840 `itemnumber` int(11) default NULL,
1841 `itemtype` varchar(10) default NULL,
1842 `borrowernumber` int(11) default NULL,
1843 `associatedborrower` int(11) default NULL,
1844 KEY `timeidx` (`datetime`)
1845 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1848 -- Table structure for table `stopwords`
1851 DROP TABLE IF EXISTS `stopwords`;
1852 CREATE TABLE `stopwords` (
1853 `word` varchar(255) default NULL
1854 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1857 -- Table structure for table `subscription`
1860 DROP TABLE IF EXISTS `subscription`;
1861 CREATE TABLE `subscription` (
1862 `biblionumber` int(11) NOT NULL default 0,
1863 `subscriptionid` int(11) NOT NULL auto_increment,
1864 `librarian` varchar(100) default '',
1865 `startdate` date default NULL,
1866 `aqbooksellerid` int(11) default 0,
1867 `cost` int(11) default 0,
1868 `aqbudgetid` int(11) default 0,
1869 `weeklength` int(11) default 0,
1870 `monthlength` int(11) default 0,
1871 `numberlength` int(11) default 0,
1872 `periodicity` tinyint(4) default 0,
1873 `dow` varchar(100) default '',
1874 `numberingmethod` varchar(100) default '',
1876 `status` varchar(100) NOT NULL default '',
1877 `add1` int(11) default 0,
1878 `every1` int(11) default 0,
1879 `whenmorethan1` int(11) default 0,
1880 `setto1` int(11) default NULL,
1881 `lastvalue1` int(11) default NULL,
1882 `add2` int(11) default 0,
1883 `every2` int(11) default 0,
1884 `whenmorethan2` int(11) default 0,
1885 `setto2` int(11) default NULL,
1886 `lastvalue2` int(11) default NULL,
1887 `add3` int(11) default 0,
1888 `every3` int(11) default 0,
1889 `innerloop1` int(11) default 0,
1890 `innerloop2` int(11) default 0,
1891 `innerloop3` int(11) default 0,
1892 `whenmorethan3` int(11) default 0,
1893 `setto3` int(11) default NULL,
1894 `lastvalue3` int(11) default NULL,
1895 `issuesatonce` tinyint(3) NOT NULL default 1,
1896 `firstacquidate` date default NULL,
1897 `manualhistory` tinyint(1) NOT NULL default 0,
1898 `irregularity` text,
1899 `letter` varchar(20) default NULL,
1900 `numberpattern` tinyint(3) default 0,
1901 `distributedto` text,
1902 `internalnotes` longtext,
1904 `location` varchar(80) NULL default '',
1905 `branchcode` varchar(10) NOT NULL default '',
1906 `hemisphere` tinyint(3) default 0,
1907 `lastbranch` varchar(10),
1908 `serialsadditems` tinyint(1) NOT NULL default '0',
1909 `staffdisplaycount` VARCHAR(10) NULL,
1910 `opacdisplaycount` VARCHAR(10) NULL,
1911 `graceperiod` int(11) NOT NULL default '0',
1912 PRIMARY KEY (`subscriptionid`)
1913 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1916 -- Table structure for table `subscriptionhistory`
1919 DROP TABLE IF EXISTS `subscriptionhistory`;
1920 CREATE TABLE `subscriptionhistory` (
1921 `biblionumber` int(11) NOT NULL default 0,
1922 `subscriptionid` int(11) NOT NULL default 0,
1923 `histstartdate` date default NULL,
1924 `enddate` date default NULL,
1925 `missinglist` longtext NOT NULL,
1926 `recievedlist` longtext NOT NULL,
1927 `opacnote` varchar(150) NOT NULL default '',
1928 `librariannote` varchar(150) NOT NULL default '',
1929 PRIMARY KEY (`subscriptionid`),
1930 KEY `biblionumber` (`biblionumber`)
1931 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1934 -- Table structure for table `subscriptionroutinglist`
1937 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1938 CREATE TABLE `subscriptionroutinglist` (
1939 `routingid` int(11) NOT NULL auto_increment,
1940 `borrowernumber` int(11) default NULL,
1941 `ranking` int(11) default NULL,
1942 `subscriptionid` int(11) default NULL,
1943 PRIMARY KEY (`routingid`)
1944 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1947 -- Table structure for table `suggestions`
1950 DROP TABLE IF EXISTS `suggestions`;
1951 CREATE TABLE `suggestions` (
1952 `suggestionid` int(8) NOT NULL auto_increment,
1953 `suggestedby` int(11) NOT NULL default 0,
1954 `managedby` int(11) default NULL,
1955 `STATUS` varchar(10) NOT NULL default '',
1957 `author` varchar(80) default NULL,
1958 `title` varchar(80) default NULL,
1959 `copyrightdate` smallint(6) default NULL,
1960 `publishercode` varchar(255) default NULL,
1961 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1962 `volumedesc` varchar(255) default NULL,
1963 `publicationyear` smallint(6) default 0,
1964 `place` varchar(255) default NULL,
1965 `isbn` varchar(30) default NULL,
1966 `mailoverseeing` smallint(1) default 0,
1967 `biblionumber` int(11) default NULL,
1969 PRIMARY KEY (`suggestionid`),
1970 KEY `suggestedby` (`suggestedby`),
1971 KEY `managedby` (`managedby`)
1972 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1975 -- Table structure for table `systempreferences`
1978 DROP TABLE IF EXISTS `systempreferences`;
1979 CREATE TABLE `systempreferences` (
1980 `variable` varchar(50) NOT NULL default '',
1982 `options` mediumtext,
1984 `type` varchar(20) default NULL,
1985 PRIMARY KEY (`variable`)
1986 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1989 -- Table structure for table `tags`
1992 DROP TABLE IF EXISTS `tags`;
1993 CREATE TABLE `tags` (
1994 `entry` varchar(255) NOT NULL default '',
1995 `weight` bigint(20) NOT NULL default 0,
1996 PRIMARY KEY (`entry`)
1997 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2000 -- Table structure for table `tags_all`
2003 DROP TABLE IF EXISTS `tags_all`;
2004 CREATE TABLE `tags_all` (
2005 `tag_id` int(11) NOT NULL auto_increment,
2006 `borrowernumber` int(11) NOT NULL,
2007 `biblionumber` int(11) NOT NULL,
2008 `term` varchar(255) NOT NULL,
2009 `language` int(4) default NULL,
2010 `date_created` datetime NOT NULL,
2011 PRIMARY KEY (`tag_id`),
2012 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2013 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2014 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2015 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2016 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2017 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2018 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2021 -- Table structure for table `tags_approval`
2024 DROP TABLE IF EXISTS `tags_approval`;
2025 CREATE TABLE `tags_approval` (
2026 `term` varchar(255) NOT NULL,
2027 `approved` int(1) NOT NULL default '0',
2028 `date_approved` datetime default NULL,
2029 `approved_by` int(11) default NULL,
2030 `weight_total` int(9) NOT NULL default '1',
2031 PRIMARY KEY (`term`),
2032 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2033 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2034 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2035 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2038 -- Table structure for table `tags_index`
2041 DROP TABLE IF EXISTS `tags_index`;
2042 CREATE TABLE `tags_index` (
2043 `term` varchar(255) NOT NULL,
2044 `biblionumber` int(11) NOT NULL,
2045 `weight` int(9) NOT NULL default '1',
2046 PRIMARY KEY (`term`,`biblionumber`),
2047 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2048 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2049 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2050 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2051 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2052 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2055 -- Table structure for table `userflags`
2058 DROP TABLE IF EXISTS `userflags`;
2059 CREATE TABLE `userflags` (
2060 `bit` int(11) NOT NULL default 0,
2061 `flag` varchar(30) default NULL,
2062 `flagdesc` varchar(255) default NULL,
2063 `defaulton` int(11) default NULL,
2065 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2068 -- Table structure for table `virtualshelves`
2071 DROP TABLE IF EXISTS `virtualshelves`;
2072 CREATE TABLE `virtualshelves` (
2073 `shelfnumber` int(11) NOT NULL auto_increment,
2074 `shelfname` varchar(255) default NULL,
2075 `owner` varchar(80) default NULL,
2076 `category` varchar(1) default NULL,
2077 `sortfield` varchar(16) default NULL,
2078 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2079 PRIMARY KEY (`shelfnumber`)
2080 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2083 -- Table structure for table `virtualshelfcontents`
2086 DROP TABLE IF EXISTS `virtualshelfcontents`;
2087 CREATE TABLE `virtualshelfcontents` (
2088 `shelfnumber` int(11) NOT NULL default 0,
2089 `biblionumber` int(11) NOT NULL default 0,
2090 `flags` int(11) default NULL,
2091 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2092 KEY `shelfnumber` (`shelfnumber`),
2093 KEY `biblionumber` (`biblionumber`),
2094 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2095 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2096 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2099 -- Table structure for table `z3950servers`
2102 DROP TABLE IF EXISTS `z3950servers`;
2103 CREATE TABLE `z3950servers` (
2104 `host` varchar(255) default NULL,
2105 `port` int(11) default NULL,
2106 `db` varchar(255) default NULL,
2107 `userid` varchar(255) default NULL,
2108 `password` varchar(255) default NULL,
2110 `id` int(11) NOT NULL auto_increment,
2111 `checked` smallint(6) default NULL,
2112 `rank` int(11) default NULL,
2113 `syntax` varchar(80) default NULL,
2115 `position` enum('primary','secondary','') NOT NULL default 'primary',
2116 `type` enum('zed','opensearch') NOT NULL default 'zed',
2117 `encoding` text default NULL,
2118 `description` text NOT NULL,
2120 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2123 -- Table structure for table `zebraqueue`
2126 DROP TABLE IF EXISTS `zebraqueue`;
2127 CREATE TABLE `zebraqueue` (
2128 `id` int(11) NOT NULL auto_increment,
2129 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2130 `operation` char(20) NOT NULL default '',
2131 `server` char(20) NOT NULL default '',
2132 `done` int(11) NOT NULL default '0',
2133 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2135 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2136 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2138 DROP TABLE IF EXISTS `services_throttle`;
2139 CREATE TABLE `services_throttle` (
2140 `service_type` varchar(10) NOT NULL default '',
2141 `service_count` varchar(45) default NULL,
2142 PRIMARY KEY (`service_type`)
2143 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2145 -- http://www.w3.org/International/articles/language-tags/
2148 DROP TABLE IF EXISTS language_subtag_registry;
2149 CREATE TABLE language_subtag_registry (
2151 type varchar(25), -- language-script-region-variant-extension-privateuse
2152 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2154 KEY `subtag` (`subtag`)
2155 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2157 -- TODO: add suppress_scripts
2158 -- this maps three letter codes defined in iso639.2 back to their
2159 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2160 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2161 CREATE TABLE language_rfc4646_to_iso639 (
2162 rfc4646_subtag varchar(25),
2163 iso639_2_code varchar(25),
2164 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2167 DROP TABLE IF EXISTS language_descriptions;
2168 CREATE TABLE language_descriptions (
2172 description varchar(255),
2174 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2176 -- bi-directional support, keyed by script subcode
2177 DROP TABLE IF EXISTS language_script_bidi;
2178 CREATE TABLE language_script_bidi (
2179 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2180 bidi varchar(3), -- rtl ltr
2181 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2184 -- TODO: need to map language subtags to script subtags for detection
2185 -- of bidi when script is not specified (like ar, he)
2186 DROP TABLE IF EXISTS language_script_mapping;
2187 CREATE TABLE language_script_mapping (
2188 language_subtag varchar(25),
2189 script_subtag varchar(25),
2190 KEY `language_subtag` (`language_subtag`)
2191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2193 DROP TABLE IF EXISTS `permissions`;
2194 CREATE TABLE `permissions` (
2195 `module_bit` int(11) NOT NULL DEFAULT 0,
2196 `code` varchar(64) DEFAULT NULL,
2197 `description` varchar(255) DEFAULT NULL,
2198 PRIMARY KEY (`module_bit`, `code`),
2199 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2200 ON DELETE CASCADE ON UPDATE CASCADE
2201 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2203 DROP TABLE IF EXISTS `serialitems`;
2204 CREATE TABLE `serialitems` (
2205 `itemnumber` int(11) NOT NULL,
2206 `serialid` int(11) NOT NULL,
2207 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2208 KEY `serialitems_sfk_1` (`serialid`),
2209 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2210 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2212 DROP TABLE IF EXISTS `user_permissions`;
2213 CREATE TABLE `user_permissions` (
2214 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2215 `module_bit` int(11) NOT NULL DEFAULT 0,
2216 `code` varchar(64) DEFAULT NULL,
2217 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2218 ON DELETE CASCADE ON UPDATE CASCADE,
2219 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2220 ON DELETE CASCADE ON UPDATE CASCADE
2221 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2224 -- Table structure for table `tmp_holdsqueue`
2227 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2228 CREATE TABLE `tmp_holdsqueue` (
2229 `biblionumber` int(11) default NULL,
2230 `itemnumber` int(11) default NULL,
2231 `barcode` varchar(20) default NULL,
2232 `surname` mediumtext NOT NULL,
2235 `borrowernumber` int(11) NOT NULL,
2236 `cardnumber` varchar(16) default NULL,
2237 `reservedate` date default NULL,
2239 `itemcallnumber` varchar(30) default NULL,
2240 `holdingbranch` varchar(10) default NULL,
2241 `pickbranch` varchar(10) default NULL,
2243 `item_level_request` tinyint(4) NOT NULL default 0
2244 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2247 -- Table structure for table `message_queue`
2250 DROP TABLE IF EXISTS `message_queue`;
2251 CREATE TABLE `message_queue` (
2252 `message_id` int(11) NOT NULL auto_increment,
2253 `borrowernumber` int(11) default NULL,
2256 `metadata` text DEFAULT NULL,
2257 `letter_code` varchar(64) DEFAULT NULL,
2258 `message_transport_type` varchar(20) NOT NULL,
2259 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2260 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2261 `to_address` mediumtext,
2262 `from_address` mediumtext,
2263 `content_type` text,
2264 KEY `message_id` (`message_id`),
2265 KEY `borrowernumber` (`borrowernumber`),
2266 KEY `message_transport_type` (`message_transport_type`),
2267 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2268 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2272 -- Table structure for table `message_transport_types`
2275 DROP TABLE IF EXISTS `message_transport_types`;
2276 CREATE TABLE `message_transport_types` (
2277 `message_transport_type` varchar(20) NOT NULL,
2278 PRIMARY KEY (`message_transport_type`)
2279 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2282 -- Table structure for table `message_attributes`
2285 DROP TABLE IF EXISTS `message_attributes`;
2286 CREATE TABLE `message_attributes` (
2287 `message_attribute_id` int(11) NOT NULL auto_increment,
2288 `message_name` varchar(20) NOT NULL default '',
2289 `takes_days` tinyint(1) NOT NULL default '0',
2290 PRIMARY KEY (`message_attribute_id`),
2291 UNIQUE KEY `message_name` (`message_name`)
2292 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2295 -- Table structure for table `message_transports`
2298 DROP TABLE IF EXISTS `message_transports`;
2299 CREATE TABLE `message_transports` (
2300 `message_attribute_id` int(11) NOT NULL,
2301 `message_transport_type` varchar(20) NOT NULL,
2302 `is_digest` tinyint(1) NOT NULL default '0',
2303 `letter_module` varchar(20) NOT NULL default '',
2304 `letter_code` varchar(20) NOT NULL default '',
2305 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2306 KEY `message_transport_type` (`message_transport_type`),
2307 KEY `letter_module` (`letter_module`,`letter_code`),
2308 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2309 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2310 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2314 -- Table structure for table `borrower_message_preferences`
2317 DROP TABLE IF EXISTS `borrower_message_preferences`;
2318 CREATE TABLE `borrower_message_preferences` (
2319 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2320 `borrowernumber` int(11) default NULL,
2321 `categorycode` varchar(10) default NULL,
2322 `message_attribute_id` int(11) default '0',
2323 `days_in_advance` int(11) default '0',
2324 `wants_digest` tinyint(1) NOT NULL default '0',
2325 PRIMARY KEY (`borrower_message_preference_id`),
2326 KEY `borrowernumber` (`borrowernumber`),
2327 KEY `categorycode` (`categorycode`),
2328 KEY `message_attribute_id` (`message_attribute_id`),
2329 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2330 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2331 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2332 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2335 -- Table structure for table `borrower_message_transport_preferences`
2338 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2339 CREATE TABLE `borrower_message_transport_preferences` (
2340 `borrower_message_preference_id` int(11) NOT NULL default '0',
2341 `message_transport_type` varchar(20) NOT NULL default '0',
2342 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2343 KEY `message_transport_type` (`message_transport_type`),
2344 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,
2345 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
2346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2349 -- Table structure for the table branch_transfer_limits
2352 DROP TABLE IF EXISTS `branch_transfer_limits`;
2353 CREATE TABLE branch_transfer_limits (
2354 limitId int(8) NOT NULL auto_increment,
2355 toBranch varchar(10) NOT NULL,
2356 fromBranch varchar(10) NOT NULL,
2357 itemtype varchar(10) NULL,
2358 ccode varchar(10) NULL,
2359 PRIMARY KEY (limitId)
2360 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2363 -- Table structure for table `item_circulation_alert_preferences`
2366 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2367 CREATE TABLE `item_circulation_alert_preferences` (
2368 `id` int(11) NOT NULL auto_increment,
2369 `branchcode` varchar(10) NOT NULL,
2370 `categorycode` varchar(10) NOT NULL,
2371 `item_type` varchar(10) NOT NULL,
2372 `notification` varchar(16) NOT NULL,
2374 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2375 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2377 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2378 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2379 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2380 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2381 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2382 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2383 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2384 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;