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` int(11) 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`),
424 KEY `publishercode` (`publishercode`),
426 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
427 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
430 -- Table structure for table `borrowers`
433 DROP TABLE IF EXISTS `borrowers`;
434 CREATE TABLE `borrowers` (
435 `borrowernumber` int(11) NOT NULL auto_increment,
436 `cardnumber` varchar(16) default NULL,
437 `surname` mediumtext NOT NULL,
440 `othernames` mediumtext,
442 `streetnumber` varchar(10) default NULL,
443 `streettype` varchar(50) default NULL,
444 `address` mediumtext NOT NULL,
446 `city` mediumtext NOT NULL,
447 `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,
460 `B_phone` mediumtext,
461 `dateofbirth` date default NULL,
462 `branchcode` varchar(10) NOT NULL default '',
463 `categorycode` varchar(10) NOT NULL default '',
464 `dateenrolled` date default NULL,
465 `dateexpiry` date default NULL,
466 `gonenoaddress` tinyint(1) default NULL,
467 `lost` tinyint(1) default NULL,
468 `debarred` tinyint(1) default NULL,
469 `contactname` mediumtext,
470 `contactfirstname` text,
472 `guarantorid` int(11) default NULL,
473 `borrowernotes` mediumtext,
474 `relationship` varchar(100) default NULL,
475 `ethnicity` varchar(50) default NULL,
476 `ethnotes` varchar(255) default NULL,
477 `sex` varchar(1) default NULL,
478 `password` varchar(30) default NULL,
479 `flags` int(11) default NULL,
480 `userid` varchar(30) default NULL,
481 `opacnote` mediumtext,
482 `contactnote` varchar(255) default NULL,
483 `sort1` varchar(80) default NULL,
484 `sort2` varchar(80) default NULL,
485 `altcontactfirstname` varchar(255) default NULL,
486 `altcontactsurname` varchar(255) default NULL,
487 `altcontactaddress1` varchar(255) default NULL,
488 `altcontactaddress2` varchar(255) default NULL,
489 `altcontactaddress3` varchar(255) default NULL,
490 `altcontactzipcode` varchar(50) default NULL,
491 `altcontactphone` varchar(50) default NULL,
492 `smsalertnumber` varchar(50) default NULL,
493 UNIQUE KEY `cardnumber` (`cardnumber`),
494 PRIMARY KEY `borrowernumber` (`borrowernumber`),
495 KEY `categorycode` (`categorycode`),
496 KEY `branchcode` (`branchcode`),
497 KEY `userid` (`userid`),
498 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
499 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
500 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
503 -- Table structure for table `borrower_attribute_types`
506 DROP TABLE IF EXISTS `borrower_attribute_types`;
507 CREATE TABLE `borrower_attribute_types` (
508 `code` varchar(10) NOT NULL,
509 `description` varchar(255) NOT NULL,
510 `repeatable` tinyint(1) NOT NULL default 0,
511 `unique_id` tinyint(1) NOT NULL default 0,
512 `opac_display` tinyint(1) NOT NULL default 0,
513 `password_allowed` tinyint(1) NOT NULL default 0,
514 `staff_searchable` tinyint(1) NOT NULL default 0,
515 `authorised_value_category` varchar(10) default NULL,
517 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
520 -- Table structure for table `borrower_attributes`
523 DROP TABLE IF EXISTS `borrower_attributes`;
524 CREATE TABLE `borrower_attributes` (
525 `borrowernumber` int(11) NOT NULL,
526 `code` varchar(10) NOT NULL,
527 `attribute` varchar(64) default NULL,
528 `password` varchar(64) default NULL,
529 KEY `borrowernumber` (`borrowernumber`),
530 KEY `code_attribute` (`code`, `attribute`),
531 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
532 ON DELETE CASCADE ON UPDATE CASCADE,
533 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
534 ON DELETE CASCADE ON UPDATE CASCADE
535 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
537 CREATE TABLE `branch_item_rules` (
538 `branchcode` varchar(10) NOT NULL,
539 `itemtype` varchar(10) NOT NULL,
540 `holdallowed` tinyint(1) default NULL,
541 PRIMARY KEY (`itemtype`,`branchcode`),
542 KEY `branch_item_rules_ibfk_2` (`branchcode`),
543 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
544 ON DELETE CASCADE ON UPDATE CASCADE,
545 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
546 ON DELETE CASCADE ON UPDATE CASCADE
547 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
550 -- Table structure for table `branchcategories`
553 DROP TABLE IF EXISTS `branchcategories`;
554 CREATE TABLE `branchcategories` (
555 `categorycode` varchar(10) NOT NULL default '',
556 `categoryname` varchar(32),
557 `codedescription` mediumtext,
558 `categorytype` varchar(16),
559 PRIMARY KEY (`categorycode`)
560 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
563 -- Table structure for table `branches`
566 DROP TABLE IF EXISTS `branches`;
567 CREATE TABLE `branches` (
568 `branchcode` varchar(10) NOT NULL default '',
569 `branchname` mediumtext NOT NULL,
570 `branchaddress1` mediumtext,
571 `branchaddress2` mediumtext,
572 `branchaddress3` mediumtext,
573 `branchphone` mediumtext,
574 `branchfax` mediumtext,
575 `branchemail` mediumtext,
576 `issuing` tinyint(4) default NULL,
577 `branchip` varchar(15) default NULL,
578 `branchprinter` varchar(100) default NULL,
579 UNIQUE KEY `branchcode` (`branchcode`)
580 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
583 -- Table structure for table `branchrelations`
586 DROP TABLE IF EXISTS `branchrelations`;
587 CREATE TABLE `branchrelations` (
588 `branchcode` varchar(10) NOT NULL default '',
589 `categorycode` varchar(10) NOT NULL default '',
590 PRIMARY KEY (`branchcode`,`categorycode`),
591 KEY `branchcode` (`branchcode`),
592 KEY `categorycode` (`categorycode`),
593 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
594 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
595 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
598 -- Table structure for table `branchtransfers`
601 DROP TABLE IF EXISTS `branchtransfers`;
602 CREATE TABLE `branchtransfers` (
603 `itemnumber` int(11) NOT NULL default 0,
604 `datesent` datetime default NULL,
605 `frombranch` varchar(10) NOT NULL default '',
606 `datearrived` datetime default NULL,
607 `tobranch` varchar(10) NOT NULL default '',
608 `comments` mediumtext,
609 KEY `frombranch` (`frombranch`),
610 KEY `tobranch` (`tobranch`),
611 KEY `itemnumber` (`itemnumber`),
612 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
613 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
614 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
615 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
619 -- Table structure for table `browser`
621 DROP TABLE IF EXISTS `browser`;
622 CREATE TABLE `browser` (
623 `level` int(11) NOT NULL,
624 `classification` varchar(20) NOT NULL,
625 `description` varchar(255) NOT NULL,
626 `number` bigint(20) NOT NULL,
627 `endnode` tinyint(4) NOT NULL
628 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
631 -- Table structure for table `categories`
634 DROP TABLE IF EXISTS `categories`;
635 CREATE TABLE `categories` (
636 `categorycode` varchar(10) NOT NULL default '',
637 `description` mediumtext,
638 `enrolmentperiod` smallint(6) default NULL,
639 `upperagelimit` smallint(6) default NULL,
640 `dateofbirthrequired` tinyint(1) default NULL,
641 `finetype` varchar(30) default NULL,
642 `bulk` tinyint(1) default NULL,
643 `enrolmentfee` decimal(28,6) default NULL,
644 `overduenoticerequired` tinyint(1) default NULL,
645 `issuelimit` smallint(6) default NULL,
646 `reservefee` decimal(28,6) default NULL,
647 `category_type` varchar(1) NOT NULL default 'A',
648 PRIMARY KEY (`categorycode`),
649 UNIQUE KEY `categorycode` (`categorycode`)
650 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
653 -- Table structure for table `borrower_branch_circ_rules`
656 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
657 CREATE TABLE `branch_borrower_circ_rules` (
658 `branchcode` VARCHAR(10) NOT NULL,
659 `categorycode` VARCHAR(10) NOT NULL,
660 `maxissueqty` int(4) default NULL,
661 PRIMARY KEY (`categorycode`, `branchcode`),
662 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
663 ON DELETE CASCADE ON UPDATE CASCADE,
664 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
665 ON DELETE CASCADE ON UPDATE CASCADE
666 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
669 -- Table structure for table `default_borrower_circ_rules`
672 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
673 CREATE TABLE `default_borrower_circ_rules` (
674 `categorycode` VARCHAR(10) NOT NULL,
675 `maxissueqty` int(4) default NULL,
676 PRIMARY KEY (`categorycode`),
677 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
678 ON DELETE CASCADE ON UPDATE CASCADE
679 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
682 -- Table structure for table `default_branch_circ_rules`
685 DROP TABLE IF EXISTS `default_branch_circ_rules`;
686 CREATE TABLE `default_branch_circ_rules` (
687 `branchcode` VARCHAR(10) NOT NULL,
688 `maxissueqty` int(4) default NULL,
689 `holdallowed` tinyint(1) default NULL,
690 PRIMARY KEY (`branchcode`),
691 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
692 ON DELETE CASCADE ON UPDATE CASCADE
693 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
696 -- Table structure for table `default_branch_item_rules`
699 CREATE TABLE `default_branch_item_rules` (
700 `itemtype` varchar(10) NOT NULL,
701 `holdallowed` tinyint(1) default NULL,
702 PRIMARY KEY (`itemtype`),
703 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
704 ON DELETE CASCADE ON UPDATE CASCADE
705 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
708 -- Table structure for table `default_circ_rules`
711 DROP TABLE IF EXISTS `default_circ_rules`;
712 CREATE TABLE `default_circ_rules` (
713 `singleton` enum('singleton') NOT NULL default 'singleton',
714 `maxissueqty` int(4) default NULL,
715 `holdallowed` int(1) default NULL,
716 PRIMARY KEY (`singleton`)
717 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
720 -- Table structure for table `cities`
723 DROP TABLE IF EXISTS `cities`;
724 CREATE TABLE `cities` (
725 `cityid` int(11) NOT NULL auto_increment,
726 `city_name` varchar(100) NOT NULL default '',
727 `city_zipcode` varchar(20) default NULL,
728 PRIMARY KEY (`cityid`)
729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
732 -- Table structure for table `class_sort_rules`
735 DROP TABLE IF EXISTS `class_sort_rules`;
736 CREATE TABLE `class_sort_rules` (
737 `class_sort_rule` varchar(10) NOT NULL default '',
738 `description` mediumtext,
739 `sort_routine` varchar(30) NOT NULL default '',
740 PRIMARY KEY (`class_sort_rule`),
741 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
742 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
745 -- Table structure for table `class_sources`
748 DROP TABLE IF EXISTS `class_sources`;
749 CREATE TABLE `class_sources` (
750 `cn_source` varchar(10) NOT NULL default '',
751 `description` mediumtext,
752 `used` tinyint(4) NOT NULL default 0,
753 `class_sort_rule` varchar(10) NOT NULL default '',
754 PRIMARY KEY (`cn_source`),
755 UNIQUE KEY `cn_source_idx` (`cn_source`),
756 KEY `used_idx` (`used`),
757 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
758 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
761 -- Table structure for table `currency`
764 DROP TABLE IF EXISTS `currency`;
765 CREATE TABLE `currency` (
766 `currency` varchar(10) NOT NULL default '',
767 `symbol` varchar(5) default NULL,
768 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
769 `rate` float(7,5) default NULL,
770 PRIMARY KEY (`currency`)
771 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
774 -- Table structure for table `deletedbiblio`
777 DROP TABLE IF EXISTS `deletedbiblio`;
778 CREATE TABLE `deletedbiblio` (
779 `biblionumber` int(11) NOT NULL default 0,
780 `frameworkcode` varchar(4) NOT NULL default '',
783 `unititle` mediumtext,
785 `serial` tinyint(1) default NULL,
786 `seriestitle` mediumtext,
787 `copyrightdate` smallint(6) default NULL,
788 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
789 `datecreated` DATE NOT NULL,
790 `abstract` mediumtext,
791 PRIMARY KEY (`biblionumber`),
792 KEY `blbnoidx` (`biblionumber`)
793 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
796 -- Table structure for table `deletedbiblioitems`
799 DROP TABLE IF EXISTS `deletedbiblioitems`;
800 CREATE TABLE `deletedbiblioitems` (
801 `biblioitemnumber` int(11) NOT NULL default 0,
802 `biblionumber` int(11) NOT NULL default 0,
805 `itemtype` varchar(10) default NULL,
806 `isbn` varchar(30) default NULL,
807 `issn` varchar(9) default NULL,
808 `publicationyear` text,
809 `publishercode` varchar(255) default NULL,
810 `volumedate` date default NULL,
812 `collectiontitle` mediumtext default NULL,
813 `collectionissn` text default NULL,
814 `collectionvolume` mediumtext default NULL,
815 `editionstatement` text default NULL,
816 `editionresponsibility` text default NULL,
817 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
818 `illus` varchar(255) default NULL,
819 `pages` varchar(255) default NULL,
821 `size` varchar(255) default NULL,
822 `place` varchar(255) default NULL,
823 `lccn` varchar(25) default NULL,
825 `url` varchar(255) default NULL,
826 `cn_source` varchar(10) default NULL,
827 `cn_class` varchar(30) default NULL,
828 `cn_item` varchar(10) default NULL,
829 `cn_suffix` varchar(10) default NULL,
830 `cn_sort` varchar(30) default NULL,
831 `totalissues` int(10),
832 `marcxml` longtext NOT NULL,
833 PRIMARY KEY (`biblioitemnumber`),
834 KEY `bibinoidx` (`biblioitemnumber`),
835 KEY `bibnoidx` (`biblionumber`),
837 KEY `publishercode` (`publishercode`)
838 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
841 -- Table structure for table `deletedborrowers`
844 DROP TABLE IF EXISTS `deletedborrowers`;
845 CREATE TABLE `deletedborrowers` (
846 `borrowernumber` int(11) NOT NULL default 0,
847 `cardnumber` varchar(9) NOT NULL default '',
848 `surname` mediumtext NOT NULL,
851 `othernames` mediumtext,
853 `streetnumber` varchar(10) default NULL,
854 `streettype` varchar(50) default NULL,
855 `address` mediumtext NOT NULL,
857 `city` mediumtext NOT NULL,
858 `zipcode` varchar(25) default NULL,
861 `mobile` varchar(50) default NULL,
865 `B_streetnumber` varchar(10) default NULL,
866 `B_streettype` varchar(50) default NULL,
867 `smsalertnumber` varchar(50) default NULL,
868 `B_address` varchar(100) default NULL,
870 `B_zipcode` varchar(25) default NULL,
872 `B_phone` mediumtext,
873 `dateofbirth` date default NULL,
874 `branchcode` varchar(10) NOT NULL default '',
875 `categorycode` varchar(10) default NULL,
876 `dateenrolled` date default NULL,
877 `dateexpiry` date default NULL,
878 `gonenoaddress` tinyint(1) default NULL,
879 `lost` tinyint(1) default NULL,
880 `debarred` tinyint(1) default NULL,
881 `contactname` mediumtext,
882 `contactfirstname` text,
884 `guarantorid` int(11) default NULL,
885 `borrowernotes` mediumtext,
886 `relationship` varchar(100) default NULL,
887 `ethnicity` varchar(50) default NULL,
888 `ethnotes` varchar(255) default NULL,
889 `sex` varchar(1) default NULL,
890 `password` varchar(30) default NULL,
891 `flags` int(11) default NULL,
892 `userid` varchar(30) default NULL,
893 `opacnote` mediumtext,
894 `contactnote` varchar(255) default NULL,
895 `sort1` varchar(80) default NULL,
896 `sort2` varchar(80) default NULL,
897 `altcontactfirstname` varchar(255) default NULL,
898 `altcontactsurname` varchar(255) default NULL,
899 `altcontactaddress1` varchar(255) default NULL,
900 `altcontactaddress2` varchar(255) default NULL,
901 `altcontactaddress3` varchar(255) default NULL,
902 `altcontactzipcode` varchar(50) default NULL,
903 `altcontactphone` varchar(50) default NULL,
904 `smsalertnumber` varchar(50) default NULL,
905 KEY `borrowernumber` (`borrowernumber`),
906 KEY `cardnumber` (`cardnumber`)
907 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
910 -- Table structure for table `deleteditems`
913 DROP TABLE IF EXISTS `deleteditems`;
914 CREATE TABLE `deleteditems` (
915 `itemnumber` int(11) NOT NULL default 0,
916 `biblionumber` int(11) NOT NULL default 0,
917 `biblioitemnumber` int(11) NOT NULL default 0,
918 `barcode` varchar(20) default NULL,
919 `dateaccessioned` date default NULL,
920 `booksellerid` mediumtext default NULL,
921 `homebranch` varchar(10) default NULL,
922 `price` decimal(8,2) default NULL,
923 `replacementprice` decimal(8,2) default NULL,
924 `replacementpricedate` date default NULL,
925 `datelastborrowed` date default NULL,
926 `datelastseen` date default NULL,
927 `stack` tinyint(1) default NULL,
928 `notforloan` tinyint(1) NOT NULL default 0,
929 `damaged` tinyint(1) NOT NULL default 0,
930 `itemlost` tinyint(1) NOT NULL default 0,
931 `wthdrawn` tinyint(1) NOT NULL default 0,
932 `itemcallnumber` varchar(30) default NULL,
933 `issues` smallint(6) default NULL,
934 `renewals` smallint(6) default NULL,
935 `reserves` smallint(6) default NULL,
936 `restricted` tinyint(1) default NULL,
937 `itemnotes` mediumtext,
938 `holdingbranch` varchar(10) default NULL,
939 `paidfor` mediumtext,
940 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
941 `location` varchar(80) default NULL,
942 `onloan` date default NULL,
943 `cn_source` varchar(10) default NULL,
944 `cn_sort` varchar(30) default NULL,
945 `ccode` varchar(10) default NULL,
946 `materials` varchar(10) default NULL,
947 `uri` varchar(255) default NULL,
948 `itype` varchar(10) default NULL,
949 `more_subfields_xml` longtext default NULL,
950 `enumchron` varchar(80) default NULL,
951 `copynumber` varchar(32) default NULL,
953 PRIMARY KEY (`itemnumber`),
954 KEY `delitembarcodeidx` (`barcode`),
955 KEY `delitembinoidx` (`biblioitemnumber`),
956 KEY `delitembibnoidx` (`biblionumber`),
957 KEY `delhomebranch` (`homebranch`),
958 KEY `delholdingbranch` (`holdingbranch`)
959 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
962 -- Table structure for table `ethnicity`
965 DROP TABLE IF EXISTS `ethnicity`;
966 CREATE TABLE `ethnicity` (
967 `code` varchar(10) NOT NULL default '',
968 `name` varchar(255) default NULL,
970 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
973 -- Table structure for table `hold_fill_targets`
976 DROP TABLE IF EXISTS `hold_fill_targets`;
977 CREATE TABLE hold_fill_targets (
978 `borrowernumber` int(11) NOT NULL,
979 `biblionumber` int(11) NOT NULL,
980 `itemnumber` int(11) NOT NULL,
981 `source_branchcode` varchar(10) default NULL,
982 `item_level_request` tinyint(4) NOT NULL default 0,
983 PRIMARY KEY `itemnumber` (`itemnumber`),
984 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
985 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
986 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
987 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
988 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
989 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
990 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
991 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
992 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
993 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
996 -- Table structure for table `import_batches`
999 DROP TABLE IF EXISTS `import_batches`;
1000 CREATE TABLE `import_batches` (
1001 `import_batch_id` int(11) NOT NULL auto_increment,
1002 `matcher_id` int(11) default NULL,
1003 `template_id` int(11) default NULL,
1004 `branchcode` varchar(10) default NULL,
1005 `num_biblios` int(11) NOT NULL default 0,
1006 `num_items` int(11) NOT NULL default 0,
1007 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1008 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1009 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1010 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1011 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1012 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1013 `file_name` varchar(100),
1014 `comments` mediumtext,
1015 PRIMARY KEY (`import_batch_id`),
1016 KEY `branchcode` (`branchcode`)
1017 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1020 -- Table structure for table `import_records`
1023 DROP TABLE IF EXISTS `import_records`;
1024 CREATE TABLE `import_records` (
1025 `import_record_id` int(11) NOT NULL auto_increment,
1026 `import_batch_id` int(11) NOT NULL,
1027 `branchcode` varchar(10) default NULL,
1028 `record_sequence` int(11) NOT NULL default 0,
1029 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1030 `import_date` DATE default NULL,
1031 `marc` longblob NOT NULL,
1032 `marcxml` longtext NOT NULL,
1033 `marcxml_old` longtext NOT NULL,
1034 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1035 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1036 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1037 `import_error` mediumtext,
1038 `encoding` varchar(40) NOT NULL default '',
1039 `z3950random` varchar(40) default NULL,
1040 PRIMARY KEY (`import_record_id`),
1041 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1042 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1043 KEY `branchcode` (`branchcode`),
1044 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1045 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1048 -- Table structure for `import_record_matches`
1050 DROP TABLE IF EXISTS `import_record_matches`;
1051 CREATE TABLE `import_record_matches` (
1052 `import_record_id` int(11) NOT NULL,
1053 `candidate_match_id` int(11) NOT NULL,
1054 `score` int(11) NOT NULL default 0,
1055 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1056 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1057 KEY `record_score` (`import_record_id`, `score`)
1058 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1061 -- Table structure for table `import_biblios`
1064 DROP TABLE IF EXISTS `import_biblios`;
1065 CREATE TABLE `import_biblios` (
1066 `import_record_id` int(11) NOT NULL,
1067 `matched_biblionumber` int(11) default NULL,
1068 `control_number` varchar(25) default NULL,
1069 `original_source` varchar(25) default NULL,
1070 `title` varchar(128) default NULL,
1071 `author` varchar(80) default NULL,
1072 `isbn` varchar(30) default NULL,
1073 `issn` varchar(9) default NULL,
1074 `has_items` tinyint(1) NOT NULL default 0,
1075 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1076 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1077 KEY `matched_biblionumber` (`matched_biblionumber`),
1078 KEY `title` (`title`),
1080 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1083 -- Table structure for table `import_items`
1086 DROP TABLE IF EXISTS `import_items`;
1087 CREATE TABLE `import_items` (
1088 `import_items_id` int(11) NOT NULL auto_increment,
1089 `import_record_id` int(11) NOT NULL,
1090 `itemnumber` int(11) default NULL,
1091 `branchcode` varchar(10) default NULL,
1092 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1093 `marcxml` longtext NOT NULL,
1094 `import_error` mediumtext,
1095 PRIMARY KEY (`import_items_id`),
1096 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1097 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1098 KEY `itemnumber` (`itemnumber`),
1099 KEY `branchcode` (`branchcode`)
1100 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1103 -- Table structure for table `issues`
1106 DROP TABLE IF EXISTS `issues`;
1107 CREATE TABLE `issues` (
1108 `borrowernumber` int(11) default NULL,
1109 `itemnumber` int(11) default NULL,
1110 `date_due` date default NULL,
1111 `branchcode` varchar(10) default NULL,
1112 `issuingbranch` varchar(18) default NULL,
1113 `returndate` date default NULL,
1114 `lastreneweddate` date default NULL,
1115 `return` varchar(4) default NULL,
1116 `renewals` tinyint(4) default NULL,
1117 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1118 `issuedate` date default NULL,
1119 KEY `issuesborridx` (`borrowernumber`),
1120 KEY `issuesitemidx` (`itemnumber`),
1121 KEY `bordate` (`borrowernumber`,`timestamp`),
1122 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1123 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1124 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1127 -- Table structure for table `issuingrules`
1130 DROP TABLE IF EXISTS `issuingrules`;
1131 CREATE TABLE `issuingrules` (
1132 `categorycode` varchar(10) NOT NULL default '',
1133 `itemtype` varchar(10) NOT NULL default '',
1134 `restrictedtype` tinyint(1) default NULL,
1135 `rentaldiscount` decimal(28,6) default NULL,
1136 `reservecharge` decimal(28,6) default NULL,
1137 `fine` decimal(28,6) default NULL,
1138 `finedays` int(11) default NULL,
1139 `firstremind` int(11) default NULL,
1140 `chargeperiod` int(11) default NULL,
1141 `accountsent` int(11) default NULL,
1142 `chargename` varchar(100) default NULL,
1143 `maxissueqty` int(4) default NULL,
1144 `issuelength` int(4) default NULL,
1145 `renewalsallowed` smallint(6) NOT NULL default "0",
1146 `reservesallowed` smallint(6) NOT NULL default "0",
1147 `branchcode` varchar(10) NOT NULL default '',
1148 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1149 KEY `categorycode` (`categorycode`),
1150 KEY `itemtype` (`itemtype`)
1151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1154 -- Table structure for table `items`
1157 DROP TABLE IF EXISTS `items`;
1158 CREATE TABLE `items` (
1159 `itemnumber` int(11) NOT NULL auto_increment,
1160 `biblionumber` int(11) NOT NULL default 0,
1161 `biblioitemnumber` int(11) NOT NULL default 0,
1162 `barcode` varchar(20) default NULL,
1163 `dateaccessioned` date default NULL,
1164 `booksellerid` mediumtext default NULL,
1165 `homebranch` varchar(10) default NULL,
1166 `price` decimal(8,2) default NULL,
1167 `replacementprice` decimal(8,2) default NULL,
1168 `replacementpricedate` date default NULL,
1169 `datelastborrowed` date default NULL,
1170 `datelastseen` date default NULL,
1171 `stack` tinyint(1) default NULL,
1172 `notforloan` tinyint(1) NOT NULL default 0,
1173 `damaged` tinyint(1) NOT NULL default 0,
1174 `itemlost` tinyint(1) NOT NULL default 0,
1175 `wthdrawn` tinyint(1) NOT NULL default 0,
1176 `itemcallnumber` varchar(30) default NULL,
1177 `issues` smallint(6) default NULL,
1178 `renewals` smallint(6) default NULL,
1179 `reserves` smallint(6) default NULL,
1180 `restricted` tinyint(1) default NULL,
1181 `itemnotes` mediumtext,
1182 `holdingbranch` varchar(10) default NULL,
1183 `paidfor` mediumtext,
1184 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1185 `location` varchar(80) default NULL,
1186 `onloan` date default NULL,
1187 `cn_source` varchar(10) default NULL,
1188 `cn_sort` varchar(30) default NULL,
1189 `ccode` varchar(10) default NULL,
1190 `materials` varchar(10) default NULL,
1191 `uri` varchar(255) default NULL,
1192 `itype` varchar(10) default NULL,
1193 `more_subfields_xml` longtext default NULL,
1194 `enumchron` varchar(80) default NULL,
1195 `copynumber` varchar(32) default NULL,
1196 PRIMARY KEY (`itemnumber`),
1197 UNIQUE KEY `itembarcodeidx` (`barcode`),
1198 KEY `itembinoidx` (`biblioitemnumber`),
1199 KEY `itembibnoidx` (`biblionumber`),
1200 KEY `homebranch` (`homebranch`),
1201 KEY `holdingbranch` (`holdingbranch`),
1202 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1203 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1204 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1208 -- Table structure for table `itemtypes`
1211 DROP TABLE IF EXISTS `itemtypes`;
1212 CREATE TABLE `itemtypes` (
1213 `itemtype` varchar(10) NOT NULL default '',
1214 `description` mediumtext,
1215 `rentalcharge` double(16,4) default NULL,
1216 `notforloan` smallint(6) default NULL,
1217 `imageurl` varchar(200) default NULL,
1219 PRIMARY KEY (`itemtype`),
1220 UNIQUE KEY `itemtype` (`itemtype`)
1221 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1224 -- Table structure for table `labels`
1227 DROP TABLE IF EXISTS `labels`;
1228 CREATE TABLE `labels` (
1229 `labelid` int(11) NOT NULL auto_increment,
1230 `batch_id` int(10) NOT NULL default 1,
1231 `itemnumber` varchar(100) NOT NULL default '',
1232 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1233 PRIMARY KEY (`labelid`)
1234 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1237 -- Table structure for table `labels_conf`
1240 DROP TABLE IF EXISTS `labels_conf`;
1241 CREATE TABLE `labels_conf` (
1242 `id` int(4) NOT NULL auto_increment,
1243 `barcodetype` char(100) default '',
1244 `title` int(1) default '0',
1245 `subtitle` int(1) default '0',
1246 `itemtype` int(1) default '0',
1247 `barcode` int(1) default '0',
1248 `dewey` int(1) default '0',
1249 `classification` int(1) default NULL,
1250 `subclass` int(1) default '0',
1251 `itemcallnumber` int(1) default '0',
1252 `author` int(1) default '0',
1253 `issn` int(1) default '0',
1254 `isbn` int(1) default '0',
1255 `startlabel` int(2) NOT NULL default '1',
1256 `printingtype` char(32) default 'BAR',
1257 `formatstring` mediumtext default NULL,
1258 `layoutname` char(20) NOT NULL default 'TEST',
1259 `guidebox` int(1) default '0',
1260 `active` tinyint(1) default '1',
1261 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1262 `ccode` char(4) collate utf8_unicode_ci default NULL,
1263 `callnum_split` int(1) default NULL,
1264 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1269 -- Table structure for table `labels_profile`
1272 DROP TABLE IF EXISTS `labels_profile`;
1273 CREATE TABLE `labels_profile` (
1274 `tmpl_id` int(4) NOT NULL,
1275 `prof_id` int(4) NOT NULL,
1276 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1277 UNIQUE KEY `prof_id` (`prof_id`)
1278 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1281 -- Table structure for table `labels_templates`
1284 DROP TABLE IF EXISTS `labels_templates`;
1285 CREATE TABLE `labels_templates` (
1286 `tmpl_id` int(4) NOT NULL auto_increment,
1287 `tmpl_code` char(100) default '',
1288 `tmpl_desc` char(100) default '',
1289 `page_width` float default '0',
1290 `page_height` float default '0',
1291 `label_width` float default '0',
1292 `label_height` float default '0',
1293 `topmargin` float default '0',
1294 `leftmargin` float default '0',
1295 `cols` int(2) default '0',
1296 `rows` int(2) default '0',
1297 `colgap` float default '0',
1298 `rowgap` float default '0',
1299 `active` int(1) default NULL,
1300 `units` char(20) default 'PX',
1301 `fontsize` int(4) NOT NULL default '3',
1302 `font` char(10) NOT NULL default 'TR',
1303 PRIMARY KEY (`tmpl_id`)
1304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1307 -- Table structure for table `letter`
1310 DROP TABLE IF EXISTS `letter`;
1311 CREATE TABLE `letter` (
1312 `module` varchar(20) NOT NULL default '',
1313 `code` varchar(20) NOT NULL default '',
1314 `name` varchar(100) NOT NULL default '',
1315 `title` varchar(200) NOT NULL default '',
1317 PRIMARY KEY (`module`,`code`)
1318 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1321 -- Table structure for table `marc_subfield_structure`
1324 DROP TABLE IF EXISTS `marc_subfield_structure`;
1325 CREATE TABLE `marc_subfield_structure` (
1326 `tagfield` varchar(3) NOT NULL default '',
1327 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1328 `liblibrarian` varchar(255) NOT NULL default '',
1329 `libopac` varchar(255) NOT NULL default '',
1330 `repeatable` tinyint(4) NOT NULL default 0,
1331 `mandatory` tinyint(4) NOT NULL default 0,
1332 `kohafield` varchar(40) default NULL,
1333 `tab` tinyint(1) default NULL,
1334 `authorised_value` varchar(20) default NULL,
1335 `authtypecode` varchar(20) default NULL,
1336 `value_builder` varchar(80) default NULL,
1337 `isurl` tinyint(1) default NULL,
1338 `hidden` tinyint(1) default NULL,
1339 `frameworkcode` varchar(4) NOT NULL default '',
1340 `seealso` varchar(1100) default NULL,
1341 `link` varchar(80) default NULL,
1342 `defaultvalue` text default NULL,
1343 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1344 KEY `kohafield_2` (`kohafield`),
1345 KEY `tab` (`frameworkcode`,`tab`),
1346 KEY `kohafield` (`frameworkcode`,`kohafield`)
1347 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1350 -- Table structure for table `marc_tag_structure`
1353 DROP TABLE IF EXISTS `marc_tag_structure`;
1354 CREATE TABLE `marc_tag_structure` (
1355 `tagfield` varchar(3) NOT NULL default '',
1356 `liblibrarian` varchar(255) NOT NULL default '',
1357 `libopac` varchar(255) NOT NULL default '',
1358 `repeatable` tinyint(4) NOT NULL default 0,
1359 `mandatory` tinyint(4) NOT NULL default 0,
1360 `authorised_value` varchar(10) default NULL,
1361 `frameworkcode` varchar(4) NOT NULL default '',
1362 PRIMARY KEY (`frameworkcode`,`tagfield`)
1363 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1366 -- Table structure for table `marc_matchers`
1369 DROP TABLE IF EXISTS `marc_matchers`;
1370 CREATE TABLE `marc_matchers` (
1371 `matcher_id` int(11) NOT NULL auto_increment,
1372 `code` varchar(10) NOT NULL default '',
1373 `description` varchar(255) NOT NULL default '',
1374 `record_type` varchar(10) NOT NULL default 'biblio',
1375 `threshold` int(11) NOT NULL default 0,
1376 PRIMARY KEY (`matcher_id`),
1377 KEY `code` (`code`),
1378 KEY `record_type` (`record_type`)
1379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1382 -- Table structure for table `matchpoints`
1384 DROP TABLE IF EXISTS `matchpoints`;
1385 CREATE TABLE `matchpoints` (
1386 `matcher_id` int(11) NOT NULL,
1387 `matchpoint_id` int(11) NOT NULL auto_increment,
1388 `search_index` varchar(30) NOT NULL default '',
1389 `score` int(11) NOT NULL default 0,
1390 PRIMARY KEY (`matchpoint_id`),
1391 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1392 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1393 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1397 -- Table structure for table `matchpoint_components`
1399 DROP TABLE IF EXISTS `matchpoint_components`;
1400 CREATE TABLE `matchpoint_components` (
1401 `matchpoint_id` int(11) NOT NULL,
1402 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1403 sequence int(11) NOT NULL default 0,
1404 tag varchar(3) NOT NULL default '',
1405 subfields varchar(40) NOT NULL default '',
1406 offset int(4) NOT NULL default 0,
1407 length int(4) NOT NULL default 0,
1408 PRIMARY KEY (`matchpoint_component_id`),
1409 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1410 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1411 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1412 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1415 -- Table structure for table `matcher_component_norms`
1417 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1418 CREATE TABLE `matchpoint_component_norms` (
1419 `matchpoint_component_id` int(11) NOT NULL,
1420 `sequence` int(11) NOT NULL default 0,
1421 `norm_routine` varchar(50) NOT NULL default '',
1422 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1423 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1424 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1425 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1428 -- Table structure for table `matcher_matchpoints`
1430 DROP TABLE IF EXISTS `matcher_matchpoints`;
1431 CREATE TABLE `matcher_matchpoints` (
1432 `matcher_id` int(11) NOT NULL,
1433 `matchpoint_id` int(11) NOT NULL,
1434 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1435 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1436 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1437 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1438 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1441 -- Table structure for table `matchchecks`
1443 DROP TABLE IF EXISTS `matchchecks`;
1444 CREATE TABLE `matchchecks` (
1445 `matcher_id` int(11) NOT NULL,
1446 `matchcheck_id` int(11) NOT NULL auto_increment,
1447 `source_matchpoint_id` int(11) NOT NULL,
1448 `target_matchpoint_id` int(11) NOT NULL,
1449 PRIMARY KEY (`matchcheck_id`),
1450 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1451 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1452 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1453 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1454 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1455 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1456 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1459 -- Table structure for table `notifys`
1462 DROP TABLE IF EXISTS `notifys`;
1463 CREATE TABLE `notifys` (
1464 `notify_id` int(11) NOT NULL default 0,
1465 `borrowernumber` int(11) NOT NULL default 0,
1466 `itemnumber` int(11) NOT NULL default 0,
1467 `notify_date` date default NULL,
1468 `notify_send_date` date default NULL,
1469 `notify_level` int(1) NOT NULL default 0,
1470 `method` varchar(20) NOT NULL default ''
1471 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1474 -- Table structure for table `nozebra`
1477 DROP TABLE IF EXISTS `nozebra`;
1478 CREATE TABLE `nozebra` (
1479 `server` varchar(20) NOT NULL,
1480 `indexname` varchar(40) NOT NULL,
1481 `value` varchar(250) NOT NULL,
1482 `biblionumbers` longtext NOT NULL,
1483 KEY `indexname` (`server`,`indexname`),
1484 KEY `value` (`server`,`value`))
1485 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1488 -- Table structure for table `old_issues`
1491 DROP TABLE IF EXISTS `old_issues`;
1492 CREATE TABLE `old_issues` (
1493 `borrowernumber` int(11) default NULL,
1494 `itemnumber` int(11) default NULL,
1495 `date_due` date default NULL,
1496 `branchcode` varchar(10) default NULL,
1497 `issuingbranch` varchar(18) default NULL,
1498 `returndate` date default NULL,
1499 `lastreneweddate` date default NULL,
1500 `return` varchar(4) default NULL,
1501 `renewals` tinyint(4) default NULL,
1502 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1503 `issuedate` date default NULL,
1504 KEY `old_issuesborridx` (`borrowernumber`),
1505 KEY `old_issuesitemidx` (`itemnumber`),
1506 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1507 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1508 ON DELETE SET NULL ON UPDATE SET NULL,
1509 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1510 ON DELETE SET NULL ON UPDATE SET NULL
1511 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1514 -- Table structure for table `old_reserves`
1516 DROP TABLE IF EXISTS `old_reserves`;
1517 CREATE TABLE `old_reserves` (
1518 `borrowernumber` int(11) default NULL,
1519 `reservedate` date default NULL,
1520 `biblionumber` int(11) default NULL,
1521 `constrainttype` varchar(1) default NULL,
1522 `branchcode` varchar(10) default NULL,
1523 `notificationdate` date default NULL,
1524 `reminderdate` date default NULL,
1525 `cancellationdate` date default NULL,
1526 `reservenotes` mediumtext,
1527 `priority` smallint(6) default NULL,
1528 `found` varchar(1) default NULL,
1529 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1530 `itemnumber` int(11) default NULL,
1531 `waitingdate` date default NULL,
1532 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1533 KEY `old_reserves_biblionumber` (`biblionumber`),
1534 KEY `old_reserves_itemnumber` (`itemnumber`),
1535 KEY `old_reserves_branchcode` (`branchcode`),
1536 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1537 ON DELETE SET NULL ON UPDATE SET NULL,
1538 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1539 ON DELETE SET NULL ON UPDATE SET NULL,
1540 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1541 ON DELETE SET NULL ON UPDATE SET NULL
1542 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1545 -- Table structure for table `opac_news`
1548 DROP TABLE IF EXISTS `opac_news`;
1549 CREATE TABLE `opac_news` (
1550 `idnew` int(10) unsigned NOT NULL auto_increment,
1551 `title` varchar(250) NOT NULL default '',
1552 `new` text NOT NULL,
1553 `lang` varchar(25) NOT NULL default '',
1554 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1555 `expirationdate` date default NULL,
1556 `number` int(11) default NULL,
1557 PRIMARY KEY (`idnew`)
1558 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1561 -- Table structure for table `overduerules`
1564 DROP TABLE IF EXISTS `overduerules`;
1565 CREATE TABLE `overduerules` (
1566 `branchcode` varchar(10) NOT NULL default '',
1567 `categorycode` varchar(10) NOT NULL default '',
1568 `delay1` int(4) default 0,
1569 `letter1` varchar(20) default NULL,
1570 `debarred1` varchar(1) default 0,
1571 `delay2` int(4) default 0,
1572 `debarred2` varchar(1) default 0,
1573 `letter2` varchar(20) default NULL,
1574 `delay3` int(4) default 0,
1575 `letter3` varchar(20) default NULL,
1576 `debarred3` int(1) default 0,
1577 PRIMARY KEY (`branchcode`,`categorycode`)
1578 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1581 -- Table structure for table `patroncards`
1584 DROP TABLE IF EXISTS `patroncards`;
1585 CREATE TABLE `patroncards` (
1586 `cardid` int(11) NOT NULL auto_increment,
1587 `batch_id` varchar(10) NOT NULL default '1',
1588 `borrowernumber` int(11) NOT NULL,
1589 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1590 PRIMARY KEY (`cardid`),
1591 KEY `patroncards_ibfk_1` (`borrowernumber`),
1592 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1593 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1596 -- Table structure for table `patronimage`
1599 DROP TABLE IF EXISTS `patronimage`;
1600 CREATE TABLE `patronimage` (
1601 `cardnumber` varchar(16) NOT NULL,
1602 `mimetype` varchar(15) NOT NULL,
1603 `imagefile` mediumblob NOT NULL,
1604 PRIMARY KEY (`cardnumber`),
1605 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1606 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1609 -- Table structure for table `printers`
1612 DROP TABLE IF EXISTS `printers`;
1613 CREATE TABLE `printers` (
1614 `printername` varchar(40) NOT NULL default '',
1615 `printqueue` varchar(20) default NULL,
1616 `printtype` varchar(20) default NULL,
1617 PRIMARY KEY (`printername`)
1618 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1621 -- Table structure for table `printers_profile`
1624 DROP TABLE IF EXISTS `printers_profile`;
1625 CREATE TABLE `printers_profile` (
1626 `prof_id` int(4) NOT NULL auto_increment,
1627 `printername` varchar(40) NOT NULL,
1628 `tmpl_id` int(4) NOT NULL,
1629 `paper_bin` varchar(20) NOT NULL,
1630 `offset_horz` float default NULL,
1631 `offset_vert` float default NULL,
1632 `creep_horz` float default NULL,
1633 `creep_vert` float default NULL,
1634 `unit` char(20) NOT NULL default 'POINT',
1635 PRIMARY KEY (`prof_id`),
1636 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1637 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1638 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1641 -- Table structure for table `repeatable_holidays`
1644 DROP TABLE IF EXISTS `repeatable_holidays`;
1645 CREATE TABLE `repeatable_holidays` (
1646 `id` int(11) NOT NULL auto_increment,
1647 `branchcode` varchar(10) NOT NULL default '',
1648 `weekday` smallint(6) default NULL,
1649 `day` smallint(6) default NULL,
1650 `month` smallint(6) default NULL,
1651 `title` varchar(50) NOT NULL default '',
1652 `description` text NOT NULL,
1654 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1657 -- Table structure for table `reports_dictionary`
1660 DROP TABLE IF EXISTS `reports_dictionary`;
1661 CREATE TABLE reports_dictionary (
1662 `id` int(11) NOT NULL auto_increment,
1663 `name` varchar(255) default NULL,
1665 `date_created` datetime default NULL,
1666 `date_modified` datetime default NULL,
1668 `area` int(11) default NULL,
1670 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1673 -- Table structure for table `reserveconstraints`
1676 DROP TABLE IF EXISTS `reserveconstraints`;
1677 CREATE TABLE `reserveconstraints` (
1678 `borrowernumber` int(11) NOT NULL default 0,
1679 `reservedate` date default NULL,
1680 `biblionumber` int(11) NOT NULL default 0,
1681 `biblioitemnumber` int(11) default NULL,
1682 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1683 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1686 -- Table structure for table `reserves`
1689 DROP TABLE IF EXISTS `reserves`;
1690 CREATE TABLE `reserves` (
1691 `borrowernumber` int(11) NOT NULL default 0,
1692 `reservedate` date default NULL,
1693 `biblionumber` int(11) NOT NULL default 0,
1694 `constrainttype` varchar(1) default NULL,
1695 `branchcode` varchar(10) default NULL,
1696 `notificationdate` date default NULL,
1697 `reminderdate` date default NULL,
1698 `cancellationdate` date default NULL,
1699 `reservenotes` mediumtext,
1700 `priority` smallint(6) default NULL,
1701 `found` varchar(1) default NULL,
1702 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1703 `itemnumber` int(11) default NULL,
1704 `waitingdate` date default NULL,
1705 KEY `borrowernumber` (`borrowernumber`),
1706 KEY `biblionumber` (`biblionumber`),
1707 KEY `itemnumber` (`itemnumber`),
1708 KEY `branchcode` (`branchcode`),
1709 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1710 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1711 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1712 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1713 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1716 -- Table structure for table `reviews`
1719 DROP TABLE IF EXISTS `reviews`;
1720 CREATE TABLE `reviews` (
1721 `reviewid` int(11) NOT NULL auto_increment,
1722 `borrowernumber` int(11) default NULL,
1723 `biblionumber` int(11) default NULL,
1725 `approved` tinyint(4) default NULL,
1726 `datereviewed` datetime default NULL,
1727 PRIMARY KEY (`reviewid`)
1728 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1731 -- Table structure for table `roadtype`
1734 DROP TABLE IF EXISTS `roadtype`;
1735 CREATE TABLE `roadtype` (
1736 `roadtypeid` int(11) NOT NULL auto_increment,
1737 `road_type` varchar(100) NOT NULL default '',
1738 PRIMARY KEY (`roadtypeid`)
1739 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1742 -- Table structure for table `saved_sql`
1745 DROP TABLE IF EXISTS `saved_sql`;
1746 CREATE TABLE saved_sql (
1747 `id` int(11) NOT NULL auto_increment,
1748 `borrowernumber` int(11) default NULL,
1749 `date_created` datetime default NULL,
1750 `last_modified` datetime default NULL,
1752 `last_run` datetime default NULL,
1753 `report_name` varchar(255) default NULL,
1754 `type` varchar(255) default NULL,
1757 KEY boridx (`borrowernumber`)
1758 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1762 -- Table structure for `saved_reports`
1765 DROP TABLE IF EXISTS `saved_reports`;
1766 CREATE TABLE saved_reports (
1767 `id` int(11) NOT NULL auto_increment,
1768 `report_id` int(11) default NULL,
1770 `date_run` datetime default NULL,
1772 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1776 -- Table structure for table `serial`
1779 DROP TABLE IF EXISTS `serial`;
1780 CREATE TABLE `serial` (
1781 `serialid` int(11) NOT NULL auto_increment,
1782 `biblionumber` varchar(100) NOT NULL default '',
1783 `subscriptionid` varchar(100) NOT NULL default '',
1784 `serialseq` varchar(100) NOT NULL default '',
1785 `status` tinyint(4) NOT NULL default 0,
1786 `planneddate` date default NULL,
1788 `publisheddate` date default NULL,
1789 `itemnumber` text default NULL,
1790 `claimdate` date default NULL,
1791 `routingnotes` text,
1792 PRIMARY KEY (`serialid`)
1793 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1796 -- Table structure for table `sessions`
1799 DROP TABLE IF EXISTS sessions;
1800 CREATE TABLE sessions (
1801 `id` varchar(32) NOT NULL,
1802 `a_session` text NOT NULL,
1804 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1807 -- Table structure for table `special_holidays`
1810 DROP TABLE IF EXISTS `special_holidays`;
1811 CREATE TABLE `special_holidays` (
1812 `id` int(11) NOT NULL auto_increment,
1813 `branchcode` varchar(10) NOT NULL default '',
1814 `day` smallint(6) NOT NULL default 0,
1815 `month` smallint(6) NOT NULL default 0,
1816 `year` smallint(6) NOT NULL default 0,
1817 `isexception` smallint(1) NOT NULL default 1,
1818 `title` varchar(50) NOT NULL default '',
1819 `description` text NOT NULL,
1821 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1824 -- Table structure for table `statistics`
1827 DROP TABLE IF EXISTS `statistics`;
1828 CREATE TABLE `statistics` (
1829 `datetime` datetime default NULL,
1830 `branch` varchar(10) default NULL,
1831 `proccode` varchar(4) default NULL,
1832 `value` double(16,4) default NULL,
1833 `type` varchar(16) default NULL,
1835 `usercode` varchar(10) default NULL,
1836 `itemnumber` int(11) default NULL,
1837 `itemtype` varchar(10) default NULL,
1838 `borrowernumber` int(11) default NULL,
1839 `associatedborrower` int(11) default NULL,
1840 KEY `timeidx` (`datetime`)
1841 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1844 -- Table structure for table `stopwords`
1847 DROP TABLE IF EXISTS `stopwords`;
1848 CREATE TABLE `stopwords` (
1849 `word` varchar(255) default NULL
1850 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1853 -- Table structure for table `subscription`
1856 DROP TABLE IF EXISTS `subscription`;
1857 CREATE TABLE `subscription` (
1858 `biblionumber` int(11) NOT NULL default 0,
1859 `subscriptionid` int(11) NOT NULL auto_increment,
1860 `librarian` varchar(100) default '',
1861 `startdate` date default NULL,
1862 `aqbooksellerid` int(11) default 0,
1863 `cost` int(11) default 0,
1864 `aqbudgetid` int(11) default 0,
1865 `weeklength` int(11) default 0,
1866 `monthlength` int(11) default 0,
1867 `numberlength` int(11) default 0,
1868 `periodicity` tinyint(4) default 0,
1869 `dow` varchar(100) default '',
1870 `numberingmethod` varchar(100) default '',
1872 `status` varchar(100) NOT NULL default '',
1873 `add1` int(11) default 0,
1874 `every1` int(11) default 0,
1875 `whenmorethan1` int(11) default 0,
1876 `setto1` int(11) default NULL,
1877 `lastvalue1` int(11) default NULL,
1878 `add2` int(11) default 0,
1879 `every2` int(11) default 0,
1880 `whenmorethan2` int(11) default 0,
1881 `setto2` int(11) default NULL,
1882 `lastvalue2` int(11) default NULL,
1883 `add3` int(11) default 0,
1884 `every3` int(11) default 0,
1885 `innerloop1` int(11) default 0,
1886 `innerloop2` int(11) default 0,
1887 `innerloop3` int(11) default 0,
1888 `whenmorethan3` int(11) default 0,
1889 `setto3` int(11) default NULL,
1890 `lastvalue3` int(11) default NULL,
1891 `issuesatonce` tinyint(3) NOT NULL default 1,
1892 `firstacquidate` date default NULL,
1893 `manualhistory` tinyint(1) NOT NULL default 0,
1894 `irregularity` text,
1895 `letter` varchar(20) default NULL,
1896 `numberpattern` tinyint(3) default 0,
1897 `distributedto` text,
1898 `internalnotes` longtext,
1900 `location` varchar(80) NULL default '',
1901 `branchcode` varchar(10) NOT NULL default '',
1902 `hemisphere` tinyint(3) default 0,
1903 `lastbranch` varchar(10),
1904 `serialsadditems` tinyint(1) NOT NULL default '0',
1905 `staffdisplaycount` VARCHAR(10) NULL,
1906 `opacdisplaycount` VARCHAR(10) NULL,
1907 `graceperiod` int(11) NOT NULL default '0',
1908 PRIMARY KEY (`subscriptionid`)
1909 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1912 -- Table structure for table `subscriptionhistory`
1915 DROP TABLE IF EXISTS `subscriptionhistory`;
1916 CREATE TABLE `subscriptionhistory` (
1917 `biblionumber` int(11) NOT NULL default 0,
1918 `subscriptionid` int(11) NOT NULL default 0,
1919 `histstartdate` date default NULL,
1920 `enddate` date default NULL,
1921 `missinglist` longtext NOT NULL,
1922 `recievedlist` longtext NOT NULL,
1923 `opacnote` varchar(150) NOT NULL default '',
1924 `librariannote` varchar(150) NOT NULL default '',
1925 PRIMARY KEY (`subscriptionid`),
1926 KEY `biblionumber` (`biblionumber`)
1927 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1930 -- Table structure for table `subscriptionroutinglist`
1933 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1934 CREATE TABLE `subscriptionroutinglist` (
1935 `routingid` int(11) NOT NULL auto_increment,
1936 `borrowernumber` int(11) default NULL,
1937 `ranking` int(11) default NULL,
1938 `subscriptionid` int(11) default NULL,
1939 PRIMARY KEY (`routingid`)
1940 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1943 -- Table structure for table `suggestions`
1946 DROP TABLE IF EXISTS `suggestions`;
1947 CREATE TABLE `suggestions` (
1948 `suggestionid` int(8) NOT NULL auto_increment,
1949 `suggestedby` int(11) NOT NULL default 0,
1950 `managedby` int(11) default NULL,
1951 `STATUS` varchar(10) NOT NULL default '',
1953 `author` varchar(80) default NULL,
1954 `title` varchar(80) default NULL,
1955 `copyrightdate` smallint(6) default NULL,
1956 `publishercode` varchar(255) default NULL,
1957 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1958 `volumedesc` varchar(255) default NULL,
1959 `publicationyear` smallint(6) default 0,
1960 `place` varchar(255) default NULL,
1961 `isbn` varchar(30) default NULL,
1962 `mailoverseeing` smallint(1) default 0,
1963 `biblionumber` int(11) default NULL,
1965 PRIMARY KEY (`suggestionid`),
1966 KEY `suggestedby` (`suggestedby`),
1967 KEY `managedby` (`managedby`)
1968 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1971 -- Table structure for table `systempreferences`
1974 DROP TABLE IF EXISTS `systempreferences`;
1975 CREATE TABLE `systempreferences` (
1976 `variable` varchar(50) NOT NULL default '',
1978 `options` mediumtext,
1980 `type` varchar(20) default NULL,
1981 PRIMARY KEY (`variable`)
1982 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1985 -- Table structure for table `tags`
1988 DROP TABLE IF EXISTS `tags`;
1989 CREATE TABLE `tags` (
1990 `entry` varchar(255) NOT NULL default '',
1991 `weight` bigint(20) NOT NULL default 0,
1992 PRIMARY KEY (`entry`)
1993 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1996 -- Table structure for table `tags_all`
1999 DROP TABLE IF EXISTS `tags_all`;
2000 CREATE TABLE `tags_all` (
2001 `tag_id` int(11) NOT NULL auto_increment,
2002 `borrowernumber` int(11) NOT NULL,
2003 `biblionumber` int(11) NOT NULL,
2004 `term` varchar(255) NOT NULL,
2005 `language` int(4) default NULL,
2006 `date_created` datetime NOT NULL,
2007 PRIMARY KEY (`tag_id`),
2008 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2009 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2010 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2011 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2012 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2013 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2017 -- Table structure for table `tags_approval`
2020 DROP TABLE IF EXISTS `tags_approval`;
2021 CREATE TABLE `tags_approval` (
2022 `term` varchar(255) NOT NULL,
2023 `approved` int(1) NOT NULL default '0',
2024 `date_approved` datetime default NULL,
2025 `approved_by` int(11) default NULL,
2026 `weight_total` int(9) NOT NULL default '1',
2027 PRIMARY KEY (`term`),
2028 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2029 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2030 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2031 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2034 -- Table structure for table `tags_index`
2037 DROP TABLE IF EXISTS `tags_index`;
2038 CREATE TABLE `tags_index` (
2039 `term` varchar(255) NOT NULL,
2040 `biblionumber` int(11) NOT NULL,
2041 `weight` int(9) NOT NULL default '1',
2042 PRIMARY KEY (`term`,`biblionumber`),
2043 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2044 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2045 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2046 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2047 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2048 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2051 -- Table structure for table `userflags`
2054 DROP TABLE IF EXISTS `userflags`;
2055 CREATE TABLE `userflags` (
2056 `bit` int(11) NOT NULL default 0,
2057 `flag` varchar(30) default NULL,
2058 `flagdesc` varchar(255) default NULL,
2059 `defaulton` int(11) default NULL,
2061 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2064 -- Table structure for table `virtualshelves`
2067 DROP TABLE IF EXISTS `virtualshelves`;
2068 CREATE TABLE `virtualshelves` (
2069 `shelfnumber` int(11) NOT NULL auto_increment,
2070 `shelfname` varchar(255) default NULL,
2071 `owner` varchar(80) default NULL,
2072 `category` varchar(1) default NULL,
2073 `sortfield` varchar(16) default NULL,
2074 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2075 PRIMARY KEY (`shelfnumber`)
2076 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2079 -- Table structure for table `virtualshelfcontents`
2082 DROP TABLE IF EXISTS `virtualshelfcontents`;
2083 CREATE TABLE `virtualshelfcontents` (
2084 `shelfnumber` int(11) NOT NULL default 0,
2085 `biblionumber` int(11) NOT NULL default 0,
2086 `flags` int(11) default NULL,
2087 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2088 KEY `shelfnumber` (`shelfnumber`),
2089 KEY `biblionumber` (`biblionumber`),
2090 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2091 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2092 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2095 -- Table structure for table `z3950servers`
2098 DROP TABLE IF EXISTS `z3950servers`;
2099 CREATE TABLE `z3950servers` (
2100 `host` varchar(255) default NULL,
2101 `port` int(11) default NULL,
2102 `db` varchar(255) default NULL,
2103 `userid` varchar(255) default NULL,
2104 `password` varchar(255) default NULL,
2106 `id` int(11) NOT NULL auto_increment,
2107 `checked` smallint(6) default NULL,
2108 `rank` int(11) default NULL,
2109 `syntax` varchar(80) default NULL,
2111 `position` enum('primary','secondary','') NOT NULL default 'primary',
2112 `type` enum('zed','opensearch') NOT NULL default 'zed',
2113 `encoding` text default NULL,
2114 `description` text NOT NULL,
2116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2119 -- Table structure for table `zebraqueue`
2122 DROP TABLE IF EXISTS `zebraqueue`;
2123 CREATE TABLE `zebraqueue` (
2124 `id` int(11) NOT NULL auto_increment,
2125 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2126 `operation` char(20) NOT NULL default '',
2127 `server` char(20) NOT NULL default '',
2128 `done` int(11) NOT NULL default '0',
2129 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2131 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2134 DROP TABLE IF EXISTS `services_throttle`;
2135 CREATE TABLE `services_throttle` (
2136 `service_type` varchar(10) NOT NULL default '',
2137 `service_count` varchar(45) default NULL,
2138 PRIMARY KEY (`service_type`)
2139 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2141 -- http://www.w3.org/International/articles/language-tags/
2144 DROP TABLE IF EXISTS language_subtag_registry;
2145 CREATE TABLE language_subtag_registry (
2147 type varchar(25), -- language-script-region-variant-extension-privateuse
2148 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2150 KEY `subtag` (`subtag`)
2151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2153 -- TODO: add suppress_scripts
2154 -- this maps three letter codes defined in iso639.2 back to their
2155 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2156 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2157 CREATE TABLE language_rfc4646_to_iso639 (
2158 rfc4646_subtag varchar(25),
2159 iso639_2_code varchar(25),
2160 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2161 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2163 DROP TABLE IF EXISTS language_descriptions;
2164 CREATE TABLE language_descriptions (
2168 description varchar(255),
2170 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2172 -- bi-directional support, keyed by script subcode
2173 DROP TABLE IF EXISTS language_script_bidi;
2174 CREATE TABLE language_script_bidi (
2175 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2176 bidi varchar(3), -- rtl ltr
2177 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2178 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2180 -- TODO: need to map language subtags to script subtags for detection
2181 -- of bidi when script is not specified (like ar, he)
2182 DROP TABLE IF EXISTS language_script_mapping;
2183 CREATE TABLE language_script_mapping (
2184 language_subtag varchar(25),
2185 script_subtag varchar(25),
2186 KEY `language_subtag` (`language_subtag`)
2187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2189 DROP TABLE IF EXISTS `permissions`;
2190 CREATE TABLE `permissions` (
2191 `module_bit` int(11) NOT NULL DEFAULT 0,
2192 `code` varchar(64) DEFAULT NULL,
2193 `description` varchar(255) DEFAULT NULL,
2194 PRIMARY KEY (`module_bit`, `code`),
2195 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2196 ON DELETE CASCADE ON UPDATE CASCADE
2197 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2199 DROP TABLE IF EXISTS `serialitems`;
2200 CREATE TABLE `serialitems` (
2201 `itemnumber` int(11) NOT NULL,
2202 `serialid` int(11) NOT NULL,
2203 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2204 KEY `serialitems_sfk_1` (`serialid`),
2205 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2208 DROP TABLE IF EXISTS `user_permissions`;
2209 CREATE TABLE `user_permissions` (
2210 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2211 `module_bit` int(11) NOT NULL DEFAULT 0,
2212 `code` varchar(64) DEFAULT NULL,
2213 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2214 ON DELETE CASCADE ON UPDATE CASCADE,
2215 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2216 ON DELETE CASCADE ON UPDATE CASCADE
2217 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2220 -- Table structure for table `tmp_holdsqueue`
2223 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2224 CREATE TABLE `tmp_holdsqueue` (
2225 `biblionumber` int(11) default NULL,
2226 `itemnumber` int(11) default NULL,
2227 `barcode` varchar(20) default NULL,
2228 `surname` mediumtext NOT NULL,
2231 `borrowernumber` int(11) NOT NULL,
2232 `cardnumber` varchar(16) default NULL,
2233 `reservedate` date default NULL,
2235 `itemcallnumber` varchar(30) default NULL,
2236 `holdingbranch` varchar(10) default NULL,
2237 `pickbranch` varchar(10) default NULL,
2239 `item_level_request` tinyint(4) NOT NULL default 0
2240 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2243 -- Table structure for table `message_queue`
2246 DROP TABLE IF EXISTS `message_queue`;
2247 CREATE TABLE `message_queue` (
2248 `message_id` int(11) NOT NULL auto_increment,
2249 `borrowernumber` int(11) default NULL,
2252 `metadata` text DEFAULT NULL,
2253 `letter_code` varchar(64) DEFAULT NULL,
2254 `message_transport_type` varchar(20) NOT NULL,
2255 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2256 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2257 `to_address` mediumtext,
2258 `from_address` mediumtext,
2259 `content_type` text,
2260 KEY `message_id` (`message_id`),
2261 KEY `borrowernumber` (`borrowernumber`),
2262 KEY `message_transport_type` (`message_transport_type`),
2263 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2264 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2268 -- Table structure for table `message_transport_types`
2271 DROP TABLE IF EXISTS `message_transport_types`;
2272 CREATE TABLE `message_transport_types` (
2273 `message_transport_type` varchar(20) NOT NULL,
2274 PRIMARY KEY (`message_transport_type`)
2275 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2278 -- Table structure for table `message_attributes`
2281 DROP TABLE IF EXISTS `message_attributes`;
2282 CREATE TABLE `message_attributes` (
2283 `message_attribute_id` int(11) NOT NULL auto_increment,
2284 `message_name` varchar(20) NOT NULL default '',
2285 `takes_days` tinyint(1) NOT NULL default '0',
2286 PRIMARY KEY (`message_attribute_id`),
2287 UNIQUE KEY `message_name` (`message_name`)
2288 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2291 -- Table structure for table `message_transports`
2294 DROP TABLE IF EXISTS `message_transports`;
2295 CREATE TABLE `message_transports` (
2296 `message_attribute_id` int(11) NOT NULL,
2297 `message_transport_type` varchar(20) NOT NULL,
2298 `is_digest` tinyint(1) NOT NULL default '0',
2299 `letter_module` varchar(20) NOT NULL default '',
2300 `letter_code` varchar(20) NOT NULL default '',
2301 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2302 KEY `message_transport_type` (`message_transport_type`),
2303 KEY `letter_module` (`letter_module`,`letter_code`),
2304 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2305 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2306 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2310 -- Table structure for table `borrower_message_preferences`
2313 DROP TABLE IF EXISTS `borrower_message_preferences`;
2314 CREATE TABLE `borrower_message_preferences` (
2315 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2316 `borrowernumber` int(11) default NULL,
2317 `categorycode` varchar(10) default NULL,
2318 `message_attribute_id` int(11) default '0',
2319 `days_in_advance` int(11) default '0',
2320 `wants_digest` tinyint(1) NOT NULL default '0',
2321 PRIMARY KEY (`borrower_message_preference_id`),
2322 KEY `borrowernumber` (`borrowernumber`),
2323 KEY `categorycode` (`categorycode`),
2324 KEY `message_attribute_id` (`message_attribute_id`),
2325 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2326 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2327 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2328 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2331 -- Table structure for table `borrower_message_transport_preferences`
2334 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2335 CREATE TABLE `borrower_message_transport_preferences` (
2336 `borrower_message_preference_id` int(11) NOT NULL default '0',
2337 `message_transport_type` varchar(20) NOT NULL default '0',
2338 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2339 KEY `message_transport_type` (`message_transport_type`),
2340 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,
2341 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
2342 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2345 -- Table structure for the table branch_transfer_limits
2348 DROP TABLE IF EXISTS `branch_transfer_limits`;
2349 CREATE TABLE branch_transfer_limits (
2350 limitId int(8) NOT NULL auto_increment,
2351 toBranch varchar(10) NOT NULL,
2352 fromBranch varchar(10) NOT NULL,
2353 itemtype varchar(10) NULL,
2354 ccode varchar(10) NULL,
2355 PRIMARY KEY (limitId)
2356 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2359 -- Table structure for table `item_circulation_alert_preferences`
2362 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2363 CREATE TABLE `item_circulation_alert_preferences` (
2364 `id` int(11) NOT NULL auto_increment,
2365 `branchcode` varchar(10) NOT NULL,
2366 `categorycode` varchar(10) NOT NULL,
2367 `item_type` varchar(10) NOT NULL,
2368 `notification` varchar(16) NOT NULL,
2370 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2371 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2373 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2374 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2375 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2376 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2377 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2378 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2379 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2380 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;