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 `onloan` date default NULL,
950 `cn_source` varchar(10) default NULL,
951 `cn_sort` varchar(30) default NULL,
952 `ccode` varchar(10) default NULL,
953 `materials` varchar(10) default NULL,
954 `uri` varchar(255) default NULL,
955 `itype` varchar(10) default NULL,
956 `more_subfields_xml` longtext default NULL,
957 `enumchron` varchar(80) default NULL,
958 `copynumber` varchar(32) default NULL,
960 PRIMARY KEY (`itemnumber`),
961 KEY `delitembarcodeidx` (`barcode`),
962 KEY `delitembinoidx` (`biblioitemnumber`),
963 KEY `delitembibnoidx` (`biblionumber`),
964 KEY `delhomebranch` (`homebranch`),
965 KEY `delholdingbranch` (`holdingbranch`)
966 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
969 -- Table structure for table `ethnicity`
972 DROP TABLE IF EXISTS `ethnicity`;
973 CREATE TABLE `ethnicity` (
974 `code` varchar(10) NOT NULL default '',
975 `name` varchar(255) default NULL,
977 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
980 -- Table structure for table `hold_fill_targets`
983 DROP TABLE IF EXISTS `hold_fill_targets`;
984 CREATE TABLE hold_fill_targets (
985 `borrowernumber` int(11) NOT NULL,
986 `biblionumber` int(11) NOT NULL,
987 `itemnumber` int(11) NOT NULL,
988 `source_branchcode` varchar(10) default NULL,
989 `item_level_request` tinyint(4) NOT NULL default 0,
990 PRIMARY KEY `itemnumber` (`itemnumber`),
991 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
992 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
993 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
994 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
995 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
996 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
997 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
998 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
999 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1000 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1003 -- Table structure for table `import_batches`
1006 DROP TABLE IF EXISTS `import_batches`;
1007 CREATE TABLE `import_batches` (
1008 `import_batch_id` int(11) NOT NULL auto_increment,
1009 `matcher_id` int(11) default NULL,
1010 `template_id` int(11) default NULL,
1011 `branchcode` varchar(10) default NULL,
1012 `num_biblios` int(11) NOT NULL default 0,
1013 `num_items` int(11) NOT NULL default 0,
1014 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1015 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1016 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1017 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1018 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1019 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1020 `file_name` varchar(100),
1021 `comments` mediumtext,
1022 PRIMARY KEY (`import_batch_id`),
1023 KEY `branchcode` (`branchcode`)
1024 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1027 -- Table structure for table `import_records`
1030 DROP TABLE IF EXISTS `import_records`;
1031 CREATE TABLE `import_records` (
1032 `import_record_id` int(11) NOT NULL auto_increment,
1033 `import_batch_id` int(11) NOT NULL,
1034 `branchcode` varchar(10) default NULL,
1035 `record_sequence` int(11) NOT NULL default 0,
1036 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1037 `import_date` DATE default NULL,
1038 `marc` longblob NOT NULL,
1039 `marcxml` longtext NOT NULL,
1040 `marcxml_old` longtext NOT NULL,
1041 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1042 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1043 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1044 `import_error` mediumtext,
1045 `encoding` varchar(40) NOT NULL default '',
1046 `z3950random` varchar(40) default NULL,
1047 PRIMARY KEY (`import_record_id`),
1048 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1049 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1050 KEY `branchcode` (`branchcode`),
1051 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1052 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1055 -- Table structure for `import_record_matches`
1057 DROP TABLE IF EXISTS `import_record_matches`;
1058 CREATE TABLE `import_record_matches` (
1059 `import_record_id` int(11) NOT NULL,
1060 `candidate_match_id` int(11) NOT NULL,
1061 `score` int(11) NOT NULL default 0,
1062 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1063 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1064 KEY `record_score` (`import_record_id`, `score`)
1065 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1068 -- Table structure for table `import_biblios`
1071 DROP TABLE IF EXISTS `import_biblios`;
1072 CREATE TABLE `import_biblios` (
1073 `import_record_id` int(11) NOT NULL,
1074 `matched_biblionumber` int(11) default NULL,
1075 `control_number` varchar(25) default NULL,
1076 `original_source` varchar(25) default NULL,
1077 `title` varchar(128) default NULL,
1078 `author` varchar(80) default NULL,
1079 `isbn` varchar(30) default NULL,
1080 `issn` varchar(9) default NULL,
1081 `has_items` tinyint(1) NOT NULL default 0,
1082 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1083 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1084 KEY `matched_biblionumber` (`matched_biblionumber`),
1085 KEY `title` (`title`),
1087 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1090 -- Table structure for table `import_items`
1093 DROP TABLE IF EXISTS `import_items`;
1094 CREATE TABLE `import_items` (
1095 `import_items_id` int(11) NOT NULL auto_increment,
1096 `import_record_id` int(11) NOT NULL,
1097 `itemnumber` int(11) default NULL,
1098 `branchcode` varchar(10) default NULL,
1099 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1100 `marcxml` longtext NOT NULL,
1101 `import_error` mediumtext,
1102 PRIMARY KEY (`import_items_id`),
1103 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1104 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1105 KEY `itemnumber` (`itemnumber`),
1106 KEY `branchcode` (`branchcode`)
1107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1110 -- Table structure for table `issues`
1113 DROP TABLE IF EXISTS `issues`;
1114 CREATE TABLE `issues` (
1115 `borrowernumber` int(11) default NULL,
1116 `itemnumber` int(11) default NULL,
1117 `date_due` date default NULL,
1118 `branchcode` varchar(10) default NULL,
1119 `issuingbranch` varchar(18) default NULL,
1120 `returndate` date default NULL,
1121 `lastreneweddate` date default NULL,
1122 `return` varchar(4) default NULL,
1123 `renewals` tinyint(4) default NULL,
1124 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1125 `issuedate` date default NULL,
1126 KEY `issuesborridx` (`borrowernumber`),
1127 KEY `issuesitemidx` (`itemnumber`),
1128 KEY `bordate` (`borrowernumber`,`timestamp`),
1129 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1130 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1134 -- Table structure for table `issuingrules`
1137 DROP TABLE IF EXISTS `issuingrules`;
1138 CREATE TABLE `issuingrules` (
1139 `categorycode` varchar(10) NOT NULL default '',
1140 `itemtype` varchar(10) NOT NULL default '',
1141 `restrictedtype` tinyint(1) default NULL,
1142 `rentaldiscount` decimal(28,6) default NULL,
1143 `reservecharge` decimal(28,6) default NULL,
1144 `fine` decimal(28,6) default NULL,
1145 `firstremind` int(11) default NULL,
1146 `chargeperiod` int(11) default NULL,
1147 `accountsent` int(11) default NULL,
1148 `chargename` varchar(100) default NULL,
1149 `maxissueqty` int(4) default NULL,
1150 `issuelength` int(4) default NULL,
1151 `branchcode` varchar(10) NOT NULL default '',
1152 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1153 KEY `categorycode` (`categorycode`),
1154 KEY `itemtype` (`itemtype`)
1155 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1158 -- Table structure for table `items`
1161 DROP TABLE IF EXISTS `items`;
1162 CREATE TABLE `items` (
1163 `itemnumber` int(11) NOT NULL auto_increment,
1164 `biblionumber` int(11) NOT NULL default 0,
1165 `biblioitemnumber` int(11) NOT NULL default 0,
1166 `barcode` varchar(20) default NULL,
1167 `dateaccessioned` date default NULL,
1168 `booksellerid` mediumtext default NULL,
1169 `homebranch` varchar(10) default NULL,
1170 `price` decimal(8,2) default NULL,
1171 `replacementprice` decimal(8,2) default NULL,
1172 `replacementpricedate` date default NULL,
1173 `datelastborrowed` date default NULL,
1174 `datelastseen` date default NULL,
1175 `stack` tinyint(1) default NULL,
1176 `notforloan` tinyint(1) NOT NULL default 0,
1177 `damaged` tinyint(1) NOT NULL default 0,
1178 `itemlost` tinyint(1) NOT NULL default 0,
1179 `wthdrawn` tinyint(1) NOT NULL default 0,
1180 `itemcallnumber` varchar(255) default NULL,
1181 `issues` smallint(6) default NULL,
1182 `renewals` smallint(6) default NULL,
1183 `reserves` smallint(6) default NULL,
1184 `restricted` tinyint(1) default NULL,
1185 `itemnotes` mediumtext,
1186 `holdingbranch` varchar(10) default NULL,
1187 `paidfor` mediumtext,
1188 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1189 `location` varchar(80) default NULL,
1190 `permanent_location` varchar(80) default NULL,
1191 `onloan` date default NULL,
1192 `cn_source` varchar(10) default NULL,
1193 `cn_sort` varchar(30) default NULL,
1194 `ccode` varchar(10) default NULL,
1195 `materials` varchar(10) default NULL,
1196 `uri` varchar(255) default NULL,
1197 `itype` varchar(10) default NULL,
1198 `more_subfields_xml` longtext default NULL,
1199 `enumchron` varchar(80) default NULL,
1200 `copynumber` varchar(32) default NULL,
1201 PRIMARY KEY (`itemnumber`),
1202 UNIQUE KEY `itembarcodeidx` (`barcode`),
1203 KEY `itembinoidx` (`biblioitemnumber`),
1204 KEY `itembibnoidx` (`biblionumber`),
1205 KEY `homebranch` (`homebranch`),
1206 KEY `holdingbranch` (`holdingbranch`),
1207 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1208 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1209 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1210 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1213 -- Table structure for table `itemtypes`
1216 DROP TABLE IF EXISTS `itemtypes`;
1217 CREATE TABLE `itemtypes` (
1218 `itemtype` varchar(10) NOT NULL default '',
1219 `description` mediumtext,
1220 `renewalsallowed` smallint(6) default NULL,
1221 `rentalcharge` double(16,4) default NULL,
1222 `notforloan` smallint(6) default NULL,
1223 `imageurl` varchar(200) default NULL,
1225 PRIMARY KEY (`itemtype`),
1226 UNIQUE KEY `itemtype` (`itemtype`)
1227 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1230 -- Table structure for table `labels`
1233 DROP TABLE IF EXISTS `labels`;
1234 CREATE TABLE `labels` (
1235 `labelid` int(11) NOT NULL auto_increment,
1236 `batch_id` int(10) NOT NULL default 1,
1237 `itemnumber` varchar(100) NOT NULL default '',
1238 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1239 PRIMARY KEY (`labelid`)
1240 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1243 -- Table structure for table `labels_conf`
1246 DROP TABLE IF EXISTS `labels_conf`;
1247 CREATE TABLE `labels_conf` (
1248 `id` int(4) NOT NULL auto_increment,
1249 `barcodetype` char(100) default '',
1250 `title` int(1) default '0',
1251 `subtitle` int(1) default '0',
1252 `itemtype` int(1) default '0',
1253 `barcode` int(1) default '0',
1254 `dewey` int(1) default '0',
1255 `classification` int(1) default NULL,
1256 `subclass` int(1) default '0',
1257 `itemcallnumber` int(1) default '0',
1258 `author` int(1) default '0',
1259 `issn` int(1) default '0',
1260 `isbn` int(1) default '0',
1261 `startlabel` int(2) NOT NULL default '1',
1262 `printingtype` char(32) default 'BAR',
1263 `formatstring` mediumtext default NULL,
1264 `layoutname` char(20) NOT NULL default 'TEST',
1265 `guidebox` int(1) default '0',
1266 `active` tinyint(1) default '1',
1267 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1268 `ccode` char(4) collate utf8_unicode_ci default NULL,
1269 `callnum_split` int(1) default NULL,
1270 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1275 -- Table structure for table `labels_profile`
1278 DROP TABLE IF EXISTS `labels_profile`;
1279 CREATE TABLE `labels_profile` (
1280 `tmpl_id` int(4) NOT NULL,
1281 `prof_id` int(4) NOT NULL,
1282 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1283 UNIQUE KEY `prof_id` (`prof_id`)
1284 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1287 -- Table structure for table `labels_templates`
1290 DROP TABLE IF EXISTS `labels_templates`;
1291 CREATE TABLE `labels_templates` (
1292 `tmpl_id` int(4) NOT NULL auto_increment,
1293 `tmpl_code` char(100) default '',
1294 `tmpl_desc` char(100) default '',
1295 `page_width` float default '0',
1296 `page_height` float default '0',
1297 `label_width` float default '0',
1298 `label_height` float default '0',
1299 `topmargin` float default '0',
1300 `leftmargin` float default '0',
1301 `cols` int(2) default '0',
1302 `rows` int(2) default '0',
1303 `colgap` float default '0',
1304 `rowgap` float default '0',
1305 `active` int(1) default NULL,
1306 `units` char(20) default 'PX',
1307 `fontsize` int(4) NOT NULL default '3',
1308 `font` char(10) NOT NULL default 'TR',
1309 PRIMARY KEY (`tmpl_id`)
1310 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1313 -- Table structure for table `letter`
1316 DROP TABLE IF EXISTS `letter`;
1317 CREATE TABLE `letter` (
1318 `module` varchar(20) NOT NULL default '',
1319 `code` varchar(20) NOT NULL default '',
1320 `name` varchar(100) NOT NULL default '',
1321 `title` varchar(200) NOT NULL default '',
1323 PRIMARY KEY (`module`,`code`)
1324 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1327 -- Table structure for table `marc_subfield_structure`
1330 DROP TABLE IF EXISTS `marc_subfield_structure`;
1331 CREATE TABLE `marc_subfield_structure` (
1332 `tagfield` varchar(3) NOT NULL default '',
1333 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1334 `liblibrarian` varchar(255) NOT NULL default '',
1335 `libopac` varchar(255) NOT NULL default '',
1336 `repeatable` tinyint(4) NOT NULL default 0,
1337 `mandatory` tinyint(4) NOT NULL default 0,
1338 `kohafield` varchar(40) default NULL,
1339 `tab` tinyint(1) default NULL,
1340 `authorised_value` varchar(20) default NULL,
1341 `authtypecode` varchar(20) default NULL,
1342 `value_builder` varchar(80) default NULL,
1343 `isurl` tinyint(1) default NULL,
1344 `hidden` tinyint(1) default NULL,
1345 `frameworkcode` varchar(4) NOT NULL default '',
1346 `seealso` varchar(1100) default NULL,
1347 `link` varchar(80) default NULL,
1348 `defaultvalue` text default NULL,
1349 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1350 KEY `kohafield_2` (`kohafield`),
1351 KEY `tab` (`frameworkcode`,`tab`),
1352 KEY `kohafield` (`frameworkcode`,`kohafield`)
1353 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1356 -- Table structure for table `marc_tag_structure`
1359 DROP TABLE IF EXISTS `marc_tag_structure`;
1360 CREATE TABLE `marc_tag_structure` (
1361 `tagfield` varchar(3) NOT NULL default '',
1362 `liblibrarian` varchar(255) NOT NULL default '',
1363 `libopac` varchar(255) NOT NULL default '',
1364 `repeatable` tinyint(4) NOT NULL default 0,
1365 `mandatory` tinyint(4) NOT NULL default 0,
1366 `authorised_value` varchar(10) default NULL,
1367 `frameworkcode` varchar(4) NOT NULL default '',
1368 PRIMARY KEY (`frameworkcode`,`tagfield`)
1369 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1372 -- Table structure for table `marc_matchers`
1375 DROP TABLE IF EXISTS `marc_matchers`;
1376 CREATE TABLE `marc_matchers` (
1377 `matcher_id` int(11) NOT NULL auto_increment,
1378 `code` varchar(10) NOT NULL default '',
1379 `description` varchar(255) NOT NULL default '',
1380 `record_type` varchar(10) NOT NULL default 'biblio',
1381 `threshold` int(11) NOT NULL default 0,
1382 PRIMARY KEY (`matcher_id`),
1383 KEY `code` (`code`),
1384 KEY `record_type` (`record_type`)
1385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1388 -- Table structure for table `matchpoints`
1390 DROP TABLE IF EXISTS `matchpoints`;
1391 CREATE TABLE `matchpoints` (
1392 `matcher_id` int(11) NOT NULL,
1393 `matchpoint_id` int(11) NOT NULL auto_increment,
1394 `search_index` varchar(30) NOT NULL default '',
1395 `score` int(11) NOT NULL default 0,
1396 PRIMARY KEY (`matchpoint_id`),
1397 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1398 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1399 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1403 -- Table structure for table `matchpoint_components`
1405 DROP TABLE IF EXISTS `matchpoint_components`;
1406 CREATE TABLE `matchpoint_components` (
1407 `matchpoint_id` int(11) NOT NULL,
1408 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1409 sequence int(11) NOT NULL default 0,
1410 tag varchar(3) NOT NULL default '',
1411 subfields varchar(40) NOT NULL default '',
1412 offset int(4) NOT NULL default 0,
1413 length int(4) NOT NULL default 0,
1414 PRIMARY KEY (`matchpoint_component_id`),
1415 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1416 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1417 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1418 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1421 -- Table structure for table `matcher_component_norms`
1423 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1424 CREATE TABLE `matchpoint_component_norms` (
1425 `matchpoint_component_id` int(11) NOT NULL,
1426 `sequence` int(11) NOT NULL default 0,
1427 `norm_routine` varchar(50) NOT NULL default '',
1428 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1429 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1430 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1431 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1434 -- Table structure for table `matcher_matchpoints`
1436 DROP TABLE IF EXISTS `matcher_matchpoints`;
1437 CREATE TABLE `matcher_matchpoints` (
1438 `matcher_id` int(11) NOT NULL,
1439 `matchpoint_id` int(11) NOT NULL,
1440 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1441 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1442 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1443 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1444 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1447 -- Table structure for table `matchchecks`
1449 DROP TABLE IF EXISTS `matchchecks`;
1450 CREATE TABLE `matchchecks` (
1451 `matcher_id` int(11) NOT NULL,
1452 `matchcheck_id` int(11) NOT NULL auto_increment,
1453 `source_matchpoint_id` int(11) NOT NULL,
1454 `target_matchpoint_id` int(11) NOT NULL,
1455 PRIMARY KEY (`matchcheck_id`),
1456 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1457 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1458 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1459 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1460 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1461 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1462 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1465 -- Table structure for table `notifys`
1468 DROP TABLE IF EXISTS `notifys`;
1469 CREATE TABLE `notifys` (
1470 `notify_id` int(11) NOT NULL default 0,
1471 `borrowernumber` int(11) NOT NULL default 0,
1472 `itemnumber` int(11) NOT NULL default 0,
1473 `notify_date` date default NULL,
1474 `notify_send_date` date default NULL,
1475 `notify_level` int(1) NOT NULL default 0,
1476 `method` varchar(20) NOT NULL default ''
1477 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1480 -- Table structure for table `nozebra`
1483 DROP TABLE IF EXISTS `nozebra`;
1484 CREATE TABLE `nozebra` (
1485 `server` varchar(20) NOT NULL,
1486 `indexname` varchar(40) NOT NULL,
1487 `value` varchar(250) NOT NULL,
1488 `biblionumbers` longtext NOT NULL,
1489 KEY `indexname` (`server`,`indexname`),
1490 KEY `value` (`server`,`value`))
1491 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1494 -- Table structure for table `old_issues`
1497 DROP TABLE IF EXISTS `old_issues`;
1498 CREATE TABLE `old_issues` (
1499 `borrowernumber` int(11) default NULL,
1500 `itemnumber` int(11) default NULL,
1501 `date_due` date default NULL,
1502 `branchcode` varchar(10) default NULL,
1503 `issuingbranch` varchar(18) default NULL,
1504 `returndate` date default NULL,
1505 `lastreneweddate` date default NULL,
1506 `return` varchar(4) default NULL,
1507 `renewals` tinyint(4) default NULL,
1508 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1509 `issuedate` date default NULL,
1510 KEY `old_issuesborridx` (`borrowernumber`),
1511 KEY `old_issuesitemidx` (`itemnumber`),
1512 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1513 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1514 ON DELETE SET NULL ON UPDATE SET NULL,
1515 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1516 ON DELETE SET NULL ON UPDATE SET NULL
1517 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1520 -- Table structure for table `old_reserves`
1522 DROP TABLE IF EXISTS `old_reserves`;
1523 CREATE TABLE `old_reserves` (
1524 `borrowernumber` int(11) default NULL,
1525 `reservedate` date default NULL,
1526 `biblionumber` int(11) default NULL,
1527 `constrainttype` varchar(1) default NULL,
1528 `branchcode` varchar(10) default NULL,
1529 `notificationdate` date default NULL,
1530 `reminderdate` date default NULL,
1531 `cancellationdate` date default NULL,
1532 `reservenotes` mediumtext,
1533 `priority` smallint(6) default NULL,
1534 `found` varchar(1) default NULL,
1535 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1536 `itemnumber` int(11) default NULL,
1537 `waitingdate` date default NULL,
1538 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1539 KEY `old_reserves_biblionumber` (`biblionumber`),
1540 KEY `old_reserves_itemnumber` (`itemnumber`),
1541 KEY `old_reserves_branchcode` (`branchcode`),
1542 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1543 ON DELETE SET NULL ON UPDATE SET NULL,
1544 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1545 ON DELETE SET NULL ON UPDATE SET NULL,
1546 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1547 ON DELETE SET NULL ON UPDATE SET NULL
1548 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1551 -- Table structure for table `opac_news`
1554 DROP TABLE IF EXISTS `opac_news`;
1555 CREATE TABLE `opac_news` (
1556 `idnew` int(10) unsigned NOT NULL auto_increment,
1557 `title` varchar(250) NOT NULL default '',
1558 `new` text NOT NULL,
1559 `lang` varchar(25) NOT NULL default '',
1560 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1561 `expirationdate` date default NULL,
1562 `number` int(11) default NULL,
1563 PRIMARY KEY (`idnew`)
1564 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1567 -- Table structure for table `overduerules`
1570 DROP TABLE IF EXISTS `overduerules`;
1571 CREATE TABLE `overduerules` (
1572 `branchcode` varchar(10) NOT NULL default '',
1573 `categorycode` varchar(10) NOT NULL default '',
1574 `delay1` int(4) default 0,
1575 `letter1` varchar(20) default NULL,
1576 `debarred1` varchar(1) default 0,
1577 `delay2` int(4) default 0,
1578 `debarred2` varchar(1) default 0,
1579 `letter2` varchar(20) default NULL,
1580 `delay3` int(4) default 0,
1581 `letter3` varchar(20) default NULL,
1582 `debarred3` int(1) default 0,
1583 PRIMARY KEY (`branchcode`,`categorycode`)
1584 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1587 -- Table structure for table `patroncards`
1590 DROP TABLE IF EXISTS `patroncards`;
1591 CREATE TABLE `patroncards` (
1592 `cardid` int(11) NOT NULL auto_increment,
1593 `batch_id` varchar(10) NOT NULL default '1',
1594 `borrowernumber` int(11) NOT NULL,
1595 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1596 PRIMARY KEY (`cardid`),
1597 KEY `patroncards_ibfk_1` (`borrowernumber`),
1598 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1599 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1602 -- Table structure for table `patronimage`
1605 DROP TABLE IF EXISTS `patronimage`;
1606 CREATE TABLE `patronimage` (
1607 `cardnumber` varchar(16) NOT NULL,
1608 `mimetype` varchar(15) NOT NULL,
1609 `imagefile` mediumblob NOT NULL,
1610 PRIMARY KEY (`cardnumber`),
1611 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1612 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1615 -- Table structure for table `printers`
1618 DROP TABLE IF EXISTS `printers`;
1619 CREATE TABLE `printers` (
1620 `printername` varchar(40) NOT NULL default '',
1621 `printqueue` varchar(20) default NULL,
1622 `printtype` varchar(20) default NULL,
1623 PRIMARY KEY (`printername`)
1624 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1627 -- Table structure for table `printers_profile`
1630 DROP TABLE IF EXISTS `printers_profile`;
1631 CREATE TABLE `printers_profile` (
1632 `prof_id` int(4) NOT NULL auto_increment,
1633 `printername` varchar(40) NOT NULL,
1634 `tmpl_id` int(4) NOT NULL,
1635 `paper_bin` varchar(20) NOT NULL,
1636 `offset_horz` float default NULL,
1637 `offset_vert` float default NULL,
1638 `creep_horz` float default NULL,
1639 `creep_vert` float default NULL,
1640 `unit` char(20) NOT NULL default 'POINT',
1641 PRIMARY KEY (`prof_id`),
1642 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1643 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1644 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1647 -- Table structure for table `repeatable_holidays`
1650 DROP TABLE IF EXISTS `repeatable_holidays`;
1651 CREATE TABLE `repeatable_holidays` (
1652 `id` int(11) NOT NULL auto_increment,
1653 `branchcode` varchar(10) NOT NULL default '',
1654 `weekday` smallint(6) default NULL,
1655 `day` smallint(6) default NULL,
1656 `month` smallint(6) default NULL,
1657 `title` varchar(50) NOT NULL default '',
1658 `description` text NOT NULL,
1660 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1663 -- Table structure for table `reports_dictionary`
1666 DROP TABLE IF EXISTS `reports_dictionary`;
1667 CREATE TABLE reports_dictionary (
1668 `id` int(11) NOT NULL auto_increment,
1669 `name` varchar(255) default NULL,
1671 `date_created` datetime default NULL,
1672 `date_modified` datetime default NULL,
1674 `area` int(11) default NULL,
1676 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1679 -- Table structure for table `reserveconstraints`
1682 DROP TABLE IF EXISTS `reserveconstraints`;
1683 CREATE TABLE `reserveconstraints` (
1684 `borrowernumber` int(11) NOT NULL default 0,
1685 `reservedate` date default NULL,
1686 `biblionumber` int(11) NOT NULL default 0,
1687 `biblioitemnumber` int(11) default NULL,
1688 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1689 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1692 -- Table structure for table `reserves`
1695 DROP TABLE IF EXISTS `reserves`;
1696 CREATE TABLE `reserves` (
1697 `borrowernumber` int(11) NOT NULL default 0,
1698 `reservedate` date default NULL,
1699 `biblionumber` int(11) NOT NULL default 0,
1700 `constrainttype` varchar(1) default NULL,
1701 `branchcode` varchar(10) default NULL,
1702 `notificationdate` date default NULL,
1703 `reminderdate` date default NULL,
1704 `cancellationdate` date default NULL,
1705 `reservenotes` mediumtext,
1706 `priority` smallint(6) default NULL,
1707 `found` varchar(1) default NULL,
1708 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1709 `itemnumber` int(11) default NULL,
1710 `waitingdate` date default NULL,
1711 KEY `borrowernumber` (`borrowernumber`),
1712 KEY `biblionumber` (`biblionumber`),
1713 KEY `itemnumber` (`itemnumber`),
1714 KEY `branchcode` (`branchcode`),
1715 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1716 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1717 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1718 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1719 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1722 -- Table structure for table `reviews`
1725 DROP TABLE IF EXISTS `reviews`;
1726 CREATE TABLE `reviews` (
1727 `reviewid` int(11) NOT NULL auto_increment,
1728 `borrowernumber` int(11) default NULL,
1729 `biblionumber` int(11) default NULL,
1731 `approved` tinyint(4) default NULL,
1732 `datereviewed` datetime default NULL,
1733 PRIMARY KEY (`reviewid`)
1734 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1737 -- Table structure for table `roadtype`
1740 DROP TABLE IF EXISTS `roadtype`;
1741 CREATE TABLE `roadtype` (
1742 `roadtypeid` int(11) NOT NULL auto_increment,
1743 `road_type` varchar(100) NOT NULL default '',
1744 PRIMARY KEY (`roadtypeid`)
1745 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1748 -- Table structure for table `saved_sql`
1751 DROP TABLE IF EXISTS `saved_sql`;
1752 CREATE TABLE saved_sql (
1753 `id` int(11) NOT NULL auto_increment,
1754 `borrowernumber` int(11) default NULL,
1755 `date_created` datetime default NULL,
1756 `last_modified` datetime default NULL,
1758 `last_run` datetime default NULL,
1759 `report_name` varchar(255) default NULL,
1760 `type` varchar(255) default NULL,
1763 KEY boridx (`borrowernumber`)
1764 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1768 -- Table structure for `saved_reports`
1771 DROP TABLE IF EXISTS `saved_reports`;
1772 CREATE TABLE saved_reports (
1773 `id` int(11) NOT NULL auto_increment,
1774 `report_id` int(11) default NULL,
1776 `date_run` datetime default NULL,
1778 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1782 -- Table structure for table `serial`
1785 DROP TABLE IF EXISTS `serial`;
1786 CREATE TABLE `serial` (
1787 `serialid` int(11) NOT NULL auto_increment,
1788 `biblionumber` varchar(100) NOT NULL default '',
1789 `subscriptionid` varchar(100) NOT NULL default '',
1790 `serialseq` varchar(100) NOT NULL default '',
1791 `status` tinyint(4) NOT NULL default 0,
1792 `planneddate` date default NULL,
1794 `publisheddate` date default NULL,
1795 `itemnumber` text default NULL,
1796 `claimdate` date default NULL,
1797 `routingnotes` text,
1798 PRIMARY KEY (`serialid`)
1799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1802 -- Table structure for table `sessions`
1805 DROP TABLE IF EXISTS sessions;
1806 CREATE TABLE sessions (
1807 `id` varchar(32) NOT NULL,
1808 `a_session` text NOT NULL,
1810 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1813 -- Table structure for table `special_holidays`
1816 DROP TABLE IF EXISTS `special_holidays`;
1817 CREATE TABLE `special_holidays` (
1818 `id` int(11) NOT NULL auto_increment,
1819 `branchcode` varchar(10) NOT NULL default '',
1820 `day` smallint(6) NOT NULL default 0,
1821 `month` smallint(6) NOT NULL default 0,
1822 `year` smallint(6) NOT NULL default 0,
1823 `isexception` smallint(1) NOT NULL default 1,
1824 `title` varchar(50) NOT NULL default '',
1825 `description` text NOT NULL,
1827 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1830 -- Table structure for table `statistics`
1833 DROP TABLE IF EXISTS `statistics`;
1834 CREATE TABLE `statistics` (
1835 `datetime` datetime default NULL,
1836 `branch` varchar(10) default NULL,
1837 `proccode` varchar(4) default NULL,
1838 `value` double(16,4) default NULL,
1839 `type` varchar(16) default NULL,
1841 `usercode` varchar(10) default NULL,
1842 `itemnumber` int(11) default NULL,
1843 `itemtype` varchar(10) default NULL,
1844 `borrowernumber` int(11) default NULL,
1845 `associatedborrower` int(11) default NULL,
1846 KEY `timeidx` (`datetime`)
1847 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1850 -- Table structure for table `stopwords`
1853 DROP TABLE IF EXISTS `stopwords`;
1854 CREATE TABLE `stopwords` (
1855 `word` varchar(255) default NULL
1856 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1859 -- Table structure for table `subscription`
1862 DROP TABLE IF EXISTS `subscription`;
1863 CREATE TABLE `subscription` (
1864 `biblionumber` int(11) NOT NULL default 0,
1865 `subscriptionid` int(11) NOT NULL auto_increment,
1866 `librarian` varchar(100) default '',
1867 `startdate` date default NULL,
1868 `aqbooksellerid` int(11) default 0,
1869 `cost` int(11) default 0,
1870 `aqbudgetid` int(11) default 0,
1871 `weeklength` int(11) default 0,
1872 `monthlength` int(11) default 0,
1873 `numberlength` int(11) default 0,
1874 `periodicity` tinyint(4) default 0,
1875 `dow` varchar(100) default '',
1876 `numberingmethod` varchar(100) default '',
1878 `status` varchar(100) NOT NULL default '',
1879 `add1` int(11) default 0,
1880 `every1` int(11) default 0,
1881 `whenmorethan1` int(11) default 0,
1882 `setto1` int(11) default NULL,
1883 `lastvalue1` int(11) default NULL,
1884 `add2` int(11) default 0,
1885 `every2` int(11) default 0,
1886 `whenmorethan2` int(11) default 0,
1887 `setto2` int(11) default NULL,
1888 `lastvalue2` int(11) default NULL,
1889 `add3` int(11) default 0,
1890 `every3` int(11) default 0,
1891 `innerloop1` int(11) default 0,
1892 `innerloop2` int(11) default 0,
1893 `innerloop3` int(11) default 0,
1894 `whenmorethan3` int(11) default 0,
1895 `setto3` int(11) default NULL,
1896 `lastvalue3` int(11) default NULL,
1897 `issuesatonce` tinyint(3) NOT NULL default 1,
1898 `firstacquidate` date default NULL,
1899 `manualhistory` tinyint(1) NOT NULL default 0,
1900 `irregularity` text,
1901 `letter` varchar(20) default NULL,
1902 `numberpattern` tinyint(3) default 0,
1903 `distributedto` text,
1904 `internalnotes` longtext,
1906 `location` varchar(80) NULL default '',
1907 `branchcode` varchar(10) NOT NULL default '',
1908 `hemisphere` tinyint(3) default 0,
1909 `lastbranch` varchar(10),
1910 `serialsadditems` tinyint(1) NOT NULL default '0',
1911 `staffdisplaycount` VARCHAR(10) NULL,
1912 `opacdisplaycount` VARCHAR(10) NULL,
1913 `graceperiod` int(11) NOT NULL default '0',
1914 PRIMARY KEY (`subscriptionid`)
1915 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1918 -- Table structure for table `subscriptionhistory`
1921 DROP TABLE IF EXISTS `subscriptionhistory`;
1922 CREATE TABLE `subscriptionhistory` (
1923 `biblionumber` int(11) NOT NULL default 0,
1924 `subscriptionid` int(11) NOT NULL default 0,
1925 `histstartdate` date default NULL,
1926 `enddate` date default NULL,
1927 `missinglist` longtext NOT NULL,
1928 `recievedlist` longtext NOT NULL,
1929 `opacnote` varchar(150) NOT NULL default '',
1930 `librariannote` varchar(150) NOT NULL default '',
1931 PRIMARY KEY (`subscriptionid`),
1932 KEY `biblionumber` (`biblionumber`)
1933 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1936 -- Table structure for table `subscriptionroutinglist`
1939 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1940 CREATE TABLE `subscriptionroutinglist` (
1941 `routingid` int(11) NOT NULL auto_increment,
1942 `borrowernumber` int(11) default NULL,
1943 `ranking` int(11) default NULL,
1944 `subscriptionid` int(11) default NULL,
1945 PRIMARY KEY (`routingid`)
1946 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1949 -- Table structure for table `suggestions`
1952 DROP TABLE IF EXISTS `suggestions`;
1953 CREATE TABLE `suggestions` (
1954 `suggestionid` int(8) NOT NULL auto_increment,
1955 `suggestedby` int(11) NOT NULL default 0,
1956 `managedby` int(11) default NULL,
1957 `STATUS` varchar(10) NOT NULL default '',
1959 `author` varchar(80) default NULL,
1960 `title` varchar(80) default NULL,
1961 `copyrightdate` smallint(6) default NULL,
1962 `publishercode` varchar(255) default NULL,
1963 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1964 `volumedesc` varchar(255) default NULL,
1965 `publicationyear` smallint(6) default 0,
1966 `place` varchar(255) default NULL,
1967 `isbn` varchar(30) default NULL,
1968 `mailoverseeing` smallint(1) default 0,
1969 `biblionumber` int(11) default NULL,
1971 PRIMARY KEY (`suggestionid`),
1972 KEY `suggestedby` (`suggestedby`),
1973 KEY `managedby` (`managedby`)
1974 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1977 -- Table structure for table `systempreferences`
1980 DROP TABLE IF EXISTS `systempreferences`;
1981 CREATE TABLE `systempreferences` (
1982 `variable` varchar(50) NOT NULL default '',
1984 `options` mediumtext,
1986 `type` varchar(20) default NULL,
1987 PRIMARY KEY (`variable`)
1988 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1991 -- Table structure for table `tags`
1994 DROP TABLE IF EXISTS `tags`;
1995 CREATE TABLE `tags` (
1996 `entry` varchar(255) NOT NULL default '',
1997 `weight` bigint(20) NOT NULL default 0,
1998 PRIMARY KEY (`entry`)
1999 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2002 -- Table structure for table `tags_all`
2005 DROP TABLE IF EXISTS `tags_all`;
2006 CREATE TABLE `tags_all` (
2007 `tag_id` int(11) NOT NULL auto_increment,
2008 `borrowernumber` int(11) NOT NULL,
2009 `biblionumber` int(11) NOT NULL,
2010 `term` varchar(255) NOT NULL,
2011 `language` int(4) default NULL,
2012 `date_created` datetime NOT NULL,
2013 PRIMARY KEY (`tag_id`),
2014 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2015 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2016 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2017 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2018 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2019 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2020 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2023 -- Table structure for table `tags_approval`
2026 DROP TABLE IF EXISTS `tags_approval`;
2027 CREATE TABLE `tags_approval` (
2028 `term` varchar(255) NOT NULL,
2029 `approved` int(1) NOT NULL default '0',
2030 `date_approved` datetime default NULL,
2031 `approved_by` int(11) default NULL,
2032 `weight_total` int(9) NOT NULL default '1',
2033 PRIMARY KEY (`term`),
2034 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2035 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2036 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2037 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2040 -- Table structure for table `tags_index`
2043 DROP TABLE IF EXISTS `tags_index`;
2044 CREATE TABLE `tags_index` (
2045 `term` varchar(255) NOT NULL,
2046 `biblionumber` int(11) NOT NULL,
2047 `weight` int(9) NOT NULL default '1',
2048 PRIMARY KEY (`term`,`biblionumber`),
2049 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2050 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2051 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2052 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2053 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2054 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2057 -- Table structure for table `userflags`
2060 DROP TABLE IF EXISTS `userflags`;
2061 CREATE TABLE `userflags` (
2062 `bit` int(11) NOT NULL default 0,
2063 `flag` varchar(30) default NULL,
2064 `flagdesc` varchar(255) default NULL,
2065 `defaulton` int(11) default NULL,
2067 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2070 -- Table structure for table `virtualshelves`
2073 DROP TABLE IF EXISTS `virtualshelves`;
2074 CREATE TABLE `virtualshelves` (
2075 `shelfnumber` int(11) NOT NULL auto_increment,
2076 `shelfname` varchar(255) default NULL,
2077 `owner` varchar(80) default NULL,
2078 `category` varchar(1) default NULL,
2079 `sortfield` varchar(16) default NULL,
2080 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2081 PRIMARY KEY (`shelfnumber`)
2082 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2085 -- Table structure for table `virtualshelfcontents`
2088 DROP TABLE IF EXISTS `virtualshelfcontents`;
2089 CREATE TABLE `virtualshelfcontents` (
2090 `shelfnumber` int(11) NOT NULL default 0,
2091 `biblionumber` int(11) NOT NULL default 0,
2092 `flags` int(11) default NULL,
2093 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2094 KEY `shelfnumber` (`shelfnumber`),
2095 KEY `biblionumber` (`biblionumber`),
2096 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2097 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2098 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2101 -- Table structure for table `z3950servers`
2104 DROP TABLE IF EXISTS `z3950servers`;
2105 CREATE TABLE `z3950servers` (
2106 `host` varchar(255) default NULL,
2107 `port` int(11) default NULL,
2108 `db` varchar(255) default NULL,
2109 `userid` varchar(255) default NULL,
2110 `password` varchar(255) default NULL,
2112 `id` int(11) NOT NULL auto_increment,
2113 `checked` smallint(6) default NULL,
2114 `rank` int(11) default NULL,
2115 `syntax` varchar(80) default NULL,
2117 `position` enum('primary','secondary','') NOT NULL default 'primary',
2118 `type` enum('zed','opensearch') NOT NULL default 'zed',
2119 `encoding` text default NULL,
2120 `description` text NOT NULL,
2122 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2125 -- Table structure for table `zebraqueue`
2128 DROP TABLE IF EXISTS `zebraqueue`;
2129 CREATE TABLE `zebraqueue` (
2130 `id` int(11) NOT NULL auto_increment,
2131 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2132 `operation` char(20) NOT NULL default '',
2133 `server` char(20) NOT NULL default '',
2134 `done` int(11) NOT NULL default '0',
2135 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2137 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2138 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2140 DROP TABLE IF EXISTS `services_throttle`;
2141 CREATE TABLE `services_throttle` (
2142 `service_type` varchar(10) NOT NULL default '',
2143 `service_count` varchar(45) default NULL,
2144 PRIMARY KEY (`service_type`)
2145 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2147 -- http://www.w3.org/International/articles/language-tags/
2150 DROP TABLE IF EXISTS language_subtag_registry;
2151 CREATE TABLE language_subtag_registry (
2153 type varchar(25), -- language-script-region-variant-extension-privateuse
2154 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2156 KEY `subtag` (`subtag`)
2157 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2159 -- TODO: add suppress_scripts
2160 -- this maps three letter codes defined in iso639.2 back to their
2161 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2162 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2163 CREATE TABLE language_rfc4646_to_iso639 (
2164 rfc4646_subtag varchar(25),
2165 iso639_2_code varchar(25),
2166 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2169 DROP TABLE IF EXISTS language_descriptions;
2170 CREATE TABLE language_descriptions (
2174 description varchar(255),
2176 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2178 -- bi-directional support, keyed by script subcode
2179 DROP TABLE IF EXISTS language_script_bidi;
2180 CREATE TABLE language_script_bidi (
2181 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2182 bidi varchar(3), -- rtl ltr
2183 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2186 -- TODO: need to map language subtags to script subtags for detection
2187 -- of bidi when script is not specified (like ar, he)
2188 DROP TABLE IF EXISTS language_script_mapping;
2189 CREATE TABLE language_script_mapping (
2190 language_subtag varchar(25),
2191 script_subtag varchar(25),
2192 KEY `language_subtag` (`language_subtag`)
2193 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2195 DROP TABLE IF EXISTS `permissions`;
2196 CREATE TABLE `permissions` (
2197 `module_bit` int(11) NOT NULL DEFAULT 0,
2198 `code` varchar(64) DEFAULT NULL,
2199 `description` varchar(255) DEFAULT NULL,
2200 PRIMARY KEY (`module_bit`, `code`),
2201 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2202 ON DELETE CASCADE ON UPDATE CASCADE
2203 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2205 DROP TABLE IF EXISTS `serialitems`;
2206 CREATE TABLE `serialitems` (
2207 `itemnumber` int(11) NOT NULL,
2208 `serialid` int(11) NOT NULL,
2209 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2210 KEY `serialitems_sfk_1` (`serialid`),
2211 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2214 DROP TABLE IF EXISTS `user_permissions`;
2215 CREATE TABLE `user_permissions` (
2216 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2217 `module_bit` int(11) NOT NULL DEFAULT 0,
2218 `code` varchar(64) DEFAULT NULL,
2219 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2220 ON DELETE CASCADE ON UPDATE CASCADE,
2221 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2222 ON DELETE CASCADE ON UPDATE CASCADE
2223 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2226 -- Table structure for table `tmp_holdsqueue`
2229 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2230 CREATE TABLE `tmp_holdsqueue` (
2231 `biblionumber` int(11) default NULL,
2232 `itemnumber` int(11) default NULL,
2233 `barcode` varchar(20) default NULL,
2234 `surname` mediumtext NOT NULL,
2237 `borrowernumber` int(11) NOT NULL,
2238 `cardnumber` varchar(16) default NULL,
2239 `reservedate` date default NULL,
2241 `itemcallnumber` varchar(255) default NULL,
2242 `holdingbranch` varchar(10) default NULL,
2243 `pickbranch` varchar(10) default NULL,
2245 `item_level_request` tinyint(4) NOT NULL default 0
2246 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2249 -- Table structure for table `message_queue`
2252 DROP TABLE IF EXISTS `message_queue`;
2253 CREATE TABLE `message_queue` (
2254 `message_id` int(11) NOT NULL auto_increment,
2255 `borrowernumber` int(11) default NULL,
2258 `metadata` text DEFAULT NULL,
2259 `letter_code` varchar(64) DEFAULT NULL,
2260 `message_transport_type` varchar(20) NOT NULL,
2261 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2262 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2263 `to_address` mediumtext,
2264 `from_address` mediumtext,
2265 `content_type` text,
2266 KEY `message_id` (`message_id`),
2267 KEY `borrowernumber` (`borrowernumber`),
2268 KEY `message_transport_type` (`message_transport_type`),
2269 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2270 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2274 -- Table structure for table `message_transport_types`
2277 DROP TABLE IF EXISTS `message_transport_types`;
2278 CREATE TABLE `message_transport_types` (
2279 `message_transport_type` varchar(20) NOT NULL,
2280 PRIMARY KEY (`message_transport_type`)
2281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2284 -- Table structure for table `message_attributes`
2287 DROP TABLE IF EXISTS `message_attributes`;
2288 CREATE TABLE `message_attributes` (
2289 `message_attribute_id` int(11) NOT NULL auto_increment,
2290 `message_name` varchar(20) NOT NULL default '',
2291 `takes_days` tinyint(1) NOT NULL default '0',
2292 PRIMARY KEY (`message_attribute_id`),
2293 UNIQUE KEY `message_name` (`message_name`)
2294 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2297 -- Table structure for table `message_transports`
2300 DROP TABLE IF EXISTS `message_transports`;
2301 CREATE TABLE `message_transports` (
2302 `message_attribute_id` int(11) NOT NULL,
2303 `message_transport_type` varchar(20) NOT NULL,
2304 `is_digest` tinyint(1) NOT NULL default '0',
2305 `letter_module` varchar(20) NOT NULL default '',
2306 `letter_code` varchar(20) NOT NULL default '',
2307 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2308 KEY `message_transport_type` (`message_transport_type`),
2309 KEY `letter_module` (`letter_module`,`letter_code`),
2310 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2311 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2312 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2316 -- Table structure for table `borrower_message_preferences`
2319 DROP TABLE IF EXISTS `borrower_message_preferences`;
2320 CREATE TABLE `borrower_message_preferences` (
2321 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2322 `borrowernumber` int(11) default NULL,
2323 `categorycode` varchar(10) default NULL,
2324 `message_attribute_id` int(11) default '0',
2325 `days_in_advance` int(11) default '0',
2326 `wants_digest` tinyint(1) NOT NULL default '0',
2327 PRIMARY KEY (`borrower_message_preference_id`),
2328 KEY `borrowernumber` (`borrowernumber`),
2329 KEY `categorycode` (`categorycode`),
2330 KEY `message_attribute_id` (`message_attribute_id`),
2331 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2332 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2333 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2334 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2337 -- Table structure for table `borrower_message_transport_preferences`
2340 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2341 CREATE TABLE `borrower_message_transport_preferences` (
2342 `borrower_message_preference_id` int(11) NOT NULL default '0',
2343 `message_transport_type` varchar(20) NOT NULL default '0',
2344 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2345 KEY `message_transport_type` (`message_transport_type`),
2346 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,
2347 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
2348 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2351 -- Table structure for the table branch_transfer_limits
2354 DROP TABLE IF EXISTS `branch_transfer_limits`;
2355 CREATE TABLE branch_transfer_limits (
2356 limitId int(8) NOT NULL auto_increment,
2357 toBranch varchar(10) NOT NULL,
2358 fromBranch varchar(10) NOT NULL,
2359 itemtype varchar(10) NULL,
2360 ccode varchar(10) NULL,
2361 PRIMARY KEY (limitId)
2362 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2365 -- Table structure for table `item_circulation_alert_preferences`
2368 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2369 CREATE TABLE `item_circulation_alert_preferences` (
2370 `id` int(11) NOT NULL auto_increment,
2371 `branchcode` varchar(10) NOT NULL,
2372 `categorycode` varchar(10) NOT NULL,
2373 `item_type` varchar(10) NOT NULL,
2374 `notification` varchar(16) NOT NULL,
2376 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2377 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2379 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2380 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2381 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2382 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2383 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2384 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2385 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2386 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;