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 `branchcode` varchar(10) NOT NULL default '',
1147 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1148 KEY `categorycode` (`categorycode`),
1149 KEY `itemtype` (`itemtype`)
1150 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1153 -- Table structure for table `items`
1156 DROP TABLE IF EXISTS `items`;
1157 CREATE TABLE `items` (
1158 `itemnumber` int(11) NOT NULL auto_increment,
1159 `biblionumber` int(11) NOT NULL default 0,
1160 `biblioitemnumber` int(11) NOT NULL default 0,
1161 `barcode` varchar(20) default NULL,
1162 `dateaccessioned` date default NULL,
1163 `booksellerid` mediumtext default NULL,
1164 `homebranch` varchar(10) default NULL,
1165 `price` decimal(8,2) default NULL,
1166 `replacementprice` decimal(8,2) default NULL,
1167 `replacementpricedate` date default NULL,
1168 `datelastborrowed` date default NULL,
1169 `datelastseen` date default NULL,
1170 `stack` tinyint(1) default NULL,
1171 `notforloan` tinyint(1) NOT NULL default 0,
1172 `damaged` tinyint(1) NOT NULL default 0,
1173 `itemlost` tinyint(1) NOT NULL default 0,
1174 `wthdrawn` tinyint(1) NOT NULL default 0,
1175 `itemcallnumber` varchar(30) default NULL,
1176 `issues` smallint(6) default NULL,
1177 `renewals` smallint(6) default NULL,
1178 `reserves` smallint(6) default NULL,
1179 `restricted` tinyint(1) default NULL,
1180 `itemnotes` mediumtext,
1181 `holdingbranch` varchar(10) default NULL,
1182 `paidfor` mediumtext,
1183 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1184 `location` varchar(80) default NULL,
1185 `onloan` date default NULL,
1186 `cn_source` varchar(10) default NULL,
1187 `cn_sort` varchar(30) default NULL,
1188 `ccode` varchar(10) default NULL,
1189 `materials` varchar(10) default NULL,
1190 `uri` varchar(255) default NULL,
1191 `itype` varchar(10) default NULL,
1192 `more_subfields_xml` longtext default NULL,
1193 `enumchron` varchar(80) default NULL,
1194 `copynumber` varchar(32) default NULL,
1195 PRIMARY KEY (`itemnumber`),
1196 UNIQUE KEY `itembarcodeidx` (`barcode`),
1197 KEY `itembinoidx` (`biblioitemnumber`),
1198 KEY `itembibnoidx` (`biblionumber`),
1199 KEY `homebranch` (`homebranch`),
1200 KEY `holdingbranch` (`holdingbranch`),
1201 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1202 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1203 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1204 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1207 -- Table structure for table `itemtypes`
1210 DROP TABLE IF EXISTS `itemtypes`;
1211 CREATE TABLE `itemtypes` (
1212 `itemtype` varchar(10) NOT NULL default '',
1213 `description` mediumtext,
1214 `rentalcharge` double(16,4) default NULL,
1215 `notforloan` smallint(6) default NULL,
1216 `imageurl` varchar(200) default NULL,
1218 PRIMARY KEY (`itemtype`),
1219 UNIQUE KEY `itemtype` (`itemtype`)
1220 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1223 -- Table structure for table `labels`
1226 DROP TABLE IF EXISTS `labels`;
1227 CREATE TABLE `labels` (
1228 `labelid` int(11) NOT NULL auto_increment,
1229 `batch_id` int(10) NOT NULL default 1,
1230 `itemnumber` varchar(100) NOT NULL default '',
1231 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1232 PRIMARY KEY (`labelid`)
1233 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1236 -- Table structure for table `labels_conf`
1239 DROP TABLE IF EXISTS `labels_conf`;
1240 CREATE TABLE `labels_conf` (
1241 `id` int(4) NOT NULL auto_increment,
1242 `barcodetype` char(100) default '',
1243 `title` int(1) default '0',
1244 `subtitle` int(1) default '0',
1245 `itemtype` int(1) default '0',
1246 `barcode` int(1) default '0',
1247 `dewey` int(1) default '0',
1248 `classification` int(1) default NULL,
1249 `subclass` int(1) default '0',
1250 `itemcallnumber` int(1) default '0',
1251 `author` int(1) default '0',
1252 `issn` int(1) default '0',
1253 `isbn` int(1) default '0',
1254 `startlabel` int(2) NOT NULL default '1',
1255 `printingtype` char(32) default 'BAR',
1256 `formatstring` mediumtext default NULL,
1257 `layoutname` char(20) NOT NULL default 'TEST',
1258 `guidebox` int(1) default '0',
1259 `active` tinyint(1) default '1',
1260 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1261 `ccode` char(4) collate utf8_unicode_ci default NULL,
1262 `callnum_split` int(1) default NULL,
1263 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1268 -- Table structure for table `labels_profile`
1271 DROP TABLE IF EXISTS `labels_profile`;
1272 CREATE TABLE `labels_profile` (
1273 `tmpl_id` int(4) NOT NULL,
1274 `prof_id` int(4) NOT NULL,
1275 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1276 UNIQUE KEY `prof_id` (`prof_id`)
1277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1280 -- Table structure for table `labels_templates`
1283 DROP TABLE IF EXISTS `labels_templates`;
1284 CREATE TABLE `labels_templates` (
1285 `tmpl_id` int(4) NOT NULL auto_increment,
1286 `tmpl_code` char(100) default '',
1287 `tmpl_desc` char(100) default '',
1288 `page_width` float default '0',
1289 `page_height` float default '0',
1290 `label_width` float default '0',
1291 `label_height` float default '0',
1292 `topmargin` float default '0',
1293 `leftmargin` float default '0',
1294 `cols` int(2) default '0',
1295 `rows` int(2) default '0',
1296 `colgap` float default '0',
1297 `rowgap` float default '0',
1298 `active` int(1) default NULL,
1299 `units` char(20) default 'PX',
1300 `fontsize` int(4) NOT NULL default '3',
1301 `font` char(10) NOT NULL default 'TR',
1302 PRIMARY KEY (`tmpl_id`)
1303 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1306 -- Table structure for table `letter`
1309 DROP TABLE IF EXISTS `letter`;
1310 CREATE TABLE `letter` (
1311 `module` varchar(20) NOT NULL default '',
1312 `code` varchar(20) NOT NULL default '',
1313 `name` varchar(100) NOT NULL default '',
1314 `title` varchar(200) NOT NULL default '',
1316 PRIMARY KEY (`module`,`code`)
1317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1320 -- Table structure for table `marc_subfield_structure`
1323 DROP TABLE IF EXISTS `marc_subfield_structure`;
1324 CREATE TABLE `marc_subfield_structure` (
1325 `tagfield` varchar(3) NOT NULL default '',
1326 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1327 `liblibrarian` varchar(255) NOT NULL default '',
1328 `libopac` varchar(255) NOT NULL default '',
1329 `repeatable` tinyint(4) NOT NULL default 0,
1330 `mandatory` tinyint(4) NOT NULL default 0,
1331 `kohafield` varchar(40) default NULL,
1332 `tab` tinyint(1) default NULL,
1333 `authorised_value` varchar(20) default NULL,
1334 `authtypecode` varchar(20) default NULL,
1335 `value_builder` varchar(80) default NULL,
1336 `isurl` tinyint(1) default NULL,
1337 `hidden` tinyint(1) default NULL,
1338 `frameworkcode` varchar(4) NOT NULL default '',
1339 `seealso` varchar(1100) default NULL,
1340 `link` varchar(80) default NULL,
1341 `defaultvalue` text default NULL,
1342 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1343 KEY `kohafield_2` (`kohafield`),
1344 KEY `tab` (`frameworkcode`,`tab`),
1345 KEY `kohafield` (`frameworkcode`,`kohafield`)
1346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1349 -- Table structure for table `marc_tag_structure`
1352 DROP TABLE IF EXISTS `marc_tag_structure`;
1353 CREATE TABLE `marc_tag_structure` (
1354 `tagfield` varchar(3) NOT NULL default '',
1355 `liblibrarian` varchar(255) NOT NULL default '',
1356 `libopac` varchar(255) NOT NULL default '',
1357 `repeatable` tinyint(4) NOT NULL default 0,
1358 `mandatory` tinyint(4) NOT NULL default 0,
1359 `authorised_value` varchar(10) default NULL,
1360 `frameworkcode` varchar(4) NOT NULL default '',
1361 PRIMARY KEY (`frameworkcode`,`tagfield`)
1362 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1365 -- Table structure for table `marc_matchers`
1368 DROP TABLE IF EXISTS `marc_matchers`;
1369 CREATE TABLE `marc_matchers` (
1370 `matcher_id` int(11) NOT NULL auto_increment,
1371 `code` varchar(10) NOT NULL default '',
1372 `description` varchar(255) NOT NULL default '',
1373 `record_type` varchar(10) NOT NULL default 'biblio',
1374 `threshold` int(11) NOT NULL default 0,
1375 PRIMARY KEY (`matcher_id`),
1376 KEY `code` (`code`),
1377 KEY `record_type` (`record_type`)
1378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1381 -- Table structure for table `matchpoints`
1383 DROP TABLE IF EXISTS `matchpoints`;
1384 CREATE TABLE `matchpoints` (
1385 `matcher_id` int(11) NOT NULL,
1386 `matchpoint_id` int(11) NOT NULL auto_increment,
1387 `search_index` varchar(30) NOT NULL default '',
1388 `score` int(11) NOT NULL default 0,
1389 PRIMARY KEY (`matchpoint_id`),
1390 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1391 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1392 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1396 -- Table structure for table `matchpoint_components`
1398 DROP TABLE IF EXISTS `matchpoint_components`;
1399 CREATE TABLE `matchpoint_components` (
1400 `matchpoint_id` int(11) NOT NULL,
1401 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1402 sequence int(11) NOT NULL default 0,
1403 tag varchar(3) NOT NULL default '',
1404 subfields varchar(40) NOT NULL default '',
1405 offset int(4) NOT NULL default 0,
1406 length int(4) NOT NULL default 0,
1407 PRIMARY KEY (`matchpoint_component_id`),
1408 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1409 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1410 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1411 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1414 -- Table structure for table `matcher_component_norms`
1416 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1417 CREATE TABLE `matchpoint_component_norms` (
1418 `matchpoint_component_id` int(11) NOT NULL,
1419 `sequence` int(11) NOT NULL default 0,
1420 `norm_routine` varchar(50) NOT NULL default '',
1421 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1422 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1423 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1424 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1427 -- Table structure for table `matcher_matchpoints`
1429 DROP TABLE IF EXISTS `matcher_matchpoints`;
1430 CREATE TABLE `matcher_matchpoints` (
1431 `matcher_id` int(11) NOT NULL,
1432 `matchpoint_id` int(11) NOT NULL,
1433 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1434 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1435 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1436 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1437 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1440 -- Table structure for table `matchchecks`
1442 DROP TABLE IF EXISTS `matchchecks`;
1443 CREATE TABLE `matchchecks` (
1444 `matcher_id` int(11) NOT NULL,
1445 `matchcheck_id` int(11) NOT NULL auto_increment,
1446 `source_matchpoint_id` int(11) NOT NULL,
1447 `target_matchpoint_id` int(11) NOT NULL,
1448 PRIMARY KEY (`matchcheck_id`),
1449 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1450 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1451 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1452 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1453 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1454 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1455 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1458 -- Table structure for table `notifys`
1461 DROP TABLE IF EXISTS `notifys`;
1462 CREATE TABLE `notifys` (
1463 `notify_id` int(11) NOT NULL default 0,
1464 `borrowernumber` int(11) NOT NULL default 0,
1465 `itemnumber` int(11) NOT NULL default 0,
1466 `notify_date` date default NULL,
1467 `notify_send_date` date default NULL,
1468 `notify_level` int(1) NOT NULL default 0,
1469 `method` varchar(20) NOT NULL default ''
1470 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1473 -- Table structure for table `nozebra`
1476 DROP TABLE IF EXISTS `nozebra`;
1477 CREATE TABLE `nozebra` (
1478 `server` varchar(20) NOT NULL,
1479 `indexname` varchar(40) NOT NULL,
1480 `value` varchar(250) NOT NULL,
1481 `biblionumbers` longtext NOT NULL,
1482 KEY `indexname` (`server`,`indexname`),
1483 KEY `value` (`server`,`value`))
1484 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1487 -- Table structure for table `old_issues`
1490 DROP TABLE IF EXISTS `old_issues`;
1491 CREATE TABLE `old_issues` (
1492 `borrowernumber` int(11) default NULL,
1493 `itemnumber` int(11) default NULL,
1494 `date_due` date default NULL,
1495 `branchcode` varchar(10) default NULL,
1496 `issuingbranch` varchar(18) default NULL,
1497 `returndate` date default NULL,
1498 `lastreneweddate` date default NULL,
1499 `return` varchar(4) default NULL,
1500 `renewals` tinyint(4) default NULL,
1501 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1502 `issuedate` date default NULL,
1503 KEY `old_issuesborridx` (`borrowernumber`),
1504 KEY `old_issuesitemidx` (`itemnumber`),
1505 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1506 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1507 ON DELETE SET NULL ON UPDATE SET NULL,
1508 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1509 ON DELETE SET NULL ON UPDATE SET NULL
1510 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1513 -- Table structure for table `old_reserves`
1515 DROP TABLE IF EXISTS `old_reserves`;
1516 CREATE TABLE `old_reserves` (
1517 `borrowernumber` int(11) default NULL,
1518 `reservedate` date default NULL,
1519 `biblionumber` int(11) default NULL,
1520 `constrainttype` varchar(1) default NULL,
1521 `branchcode` varchar(10) default NULL,
1522 `notificationdate` date default NULL,
1523 `reminderdate` date default NULL,
1524 `cancellationdate` date default NULL,
1525 `reservenotes` mediumtext,
1526 `priority` smallint(6) default NULL,
1527 `found` varchar(1) default NULL,
1528 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1529 `itemnumber` int(11) default NULL,
1530 `waitingdate` date default NULL,
1531 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1532 KEY `old_reserves_biblionumber` (`biblionumber`),
1533 KEY `old_reserves_itemnumber` (`itemnumber`),
1534 KEY `old_reserves_branchcode` (`branchcode`),
1535 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1536 ON DELETE SET NULL ON UPDATE SET NULL,
1537 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1538 ON DELETE SET NULL ON UPDATE SET NULL,
1539 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1540 ON DELETE SET NULL ON UPDATE SET NULL
1541 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1544 -- Table structure for table `opac_news`
1547 DROP TABLE IF EXISTS `opac_news`;
1548 CREATE TABLE `opac_news` (
1549 `idnew` int(10) unsigned NOT NULL auto_increment,
1550 `title` varchar(250) NOT NULL default '',
1551 `new` text NOT NULL,
1552 `lang` varchar(25) NOT NULL default '',
1553 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1554 `expirationdate` date default NULL,
1555 `number` int(11) default NULL,
1556 PRIMARY KEY (`idnew`)
1557 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1560 -- Table structure for table `overduerules`
1563 DROP TABLE IF EXISTS `overduerules`;
1564 CREATE TABLE `overduerules` (
1565 `branchcode` varchar(10) NOT NULL default '',
1566 `categorycode` varchar(10) NOT NULL default '',
1567 `delay1` int(4) default 0,
1568 `letter1` varchar(20) default NULL,
1569 `debarred1` varchar(1) default 0,
1570 `delay2` int(4) default 0,
1571 `debarred2` varchar(1) default 0,
1572 `letter2` varchar(20) default NULL,
1573 `delay3` int(4) default 0,
1574 `letter3` varchar(20) default NULL,
1575 `debarred3` int(1) default 0,
1576 PRIMARY KEY (`branchcode`,`categorycode`)
1577 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1580 -- Table structure for table `patroncards`
1583 DROP TABLE IF EXISTS `patroncards`;
1584 CREATE TABLE `patroncards` (
1585 `cardid` int(11) NOT NULL auto_increment,
1586 `batch_id` varchar(10) NOT NULL default '1',
1587 `borrowernumber` int(11) NOT NULL,
1588 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1589 PRIMARY KEY (`cardid`),
1590 KEY `patroncards_ibfk_1` (`borrowernumber`),
1591 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1592 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1595 -- Table structure for table `patronimage`
1598 DROP TABLE IF EXISTS `patronimage`;
1599 CREATE TABLE `patronimage` (
1600 `cardnumber` varchar(16) NOT NULL,
1601 `mimetype` varchar(15) NOT NULL,
1602 `imagefile` mediumblob NOT NULL,
1603 PRIMARY KEY (`cardnumber`),
1604 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1605 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1608 -- Table structure for table `printers`
1611 DROP TABLE IF EXISTS `printers`;
1612 CREATE TABLE `printers` (
1613 `printername` varchar(40) NOT NULL default '',
1614 `printqueue` varchar(20) default NULL,
1615 `printtype` varchar(20) default NULL,
1616 PRIMARY KEY (`printername`)
1617 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1620 -- Table structure for table `printers_profile`
1623 DROP TABLE IF EXISTS `printers_profile`;
1624 CREATE TABLE `printers_profile` (
1625 `prof_id` int(4) NOT NULL auto_increment,
1626 `printername` varchar(40) NOT NULL,
1627 `tmpl_id` int(4) NOT NULL,
1628 `paper_bin` varchar(20) NOT NULL,
1629 `offset_horz` float default NULL,
1630 `offset_vert` float default NULL,
1631 `creep_horz` float default NULL,
1632 `creep_vert` float default NULL,
1633 `unit` char(20) NOT NULL default 'POINT',
1634 PRIMARY KEY (`prof_id`),
1635 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1636 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1637 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1640 -- Table structure for table `repeatable_holidays`
1643 DROP TABLE IF EXISTS `repeatable_holidays`;
1644 CREATE TABLE `repeatable_holidays` (
1645 `id` int(11) NOT NULL auto_increment,
1646 `branchcode` varchar(10) NOT NULL default '',
1647 `weekday` smallint(6) default NULL,
1648 `day` smallint(6) default NULL,
1649 `month` smallint(6) default NULL,
1650 `title` varchar(50) NOT NULL default '',
1651 `description` text NOT NULL,
1653 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1656 -- Table structure for table `reports_dictionary`
1659 DROP TABLE IF EXISTS `reports_dictionary`;
1660 CREATE TABLE reports_dictionary (
1661 `id` int(11) NOT NULL auto_increment,
1662 `name` varchar(255) default NULL,
1664 `date_created` datetime default NULL,
1665 `date_modified` datetime default NULL,
1667 `area` int(11) default NULL,
1669 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1672 -- Table structure for table `reserveconstraints`
1675 DROP TABLE IF EXISTS `reserveconstraints`;
1676 CREATE TABLE `reserveconstraints` (
1677 `borrowernumber` int(11) NOT NULL default 0,
1678 `reservedate` date default NULL,
1679 `biblionumber` int(11) NOT NULL default 0,
1680 `biblioitemnumber` int(11) default NULL,
1681 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1682 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1685 -- Table structure for table `reserves`
1688 DROP TABLE IF EXISTS `reserves`;
1689 CREATE TABLE `reserves` (
1690 `borrowernumber` int(11) NOT NULL default 0,
1691 `reservedate` date default NULL,
1692 `biblionumber` int(11) NOT NULL default 0,
1693 `constrainttype` varchar(1) default NULL,
1694 `branchcode` varchar(10) default NULL,
1695 `notificationdate` date default NULL,
1696 `reminderdate` date default NULL,
1697 `cancellationdate` date default NULL,
1698 `reservenotes` mediumtext,
1699 `priority` smallint(6) default NULL,
1700 `found` varchar(1) default NULL,
1701 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1702 `itemnumber` int(11) default NULL,
1703 `waitingdate` date default NULL,
1704 KEY `borrowernumber` (`borrowernumber`),
1705 KEY `biblionumber` (`biblionumber`),
1706 KEY `itemnumber` (`itemnumber`),
1707 KEY `branchcode` (`branchcode`),
1708 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1709 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1710 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1711 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1712 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1715 -- Table structure for table `reviews`
1718 DROP TABLE IF EXISTS `reviews`;
1719 CREATE TABLE `reviews` (
1720 `reviewid` int(11) NOT NULL auto_increment,
1721 `borrowernumber` int(11) default NULL,
1722 `biblionumber` int(11) default NULL,
1724 `approved` tinyint(4) default NULL,
1725 `datereviewed` datetime default NULL,
1726 PRIMARY KEY (`reviewid`)
1727 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1730 -- Table structure for table `roadtype`
1733 DROP TABLE IF EXISTS `roadtype`;
1734 CREATE TABLE `roadtype` (
1735 `roadtypeid` int(11) NOT NULL auto_increment,
1736 `road_type` varchar(100) NOT NULL default '',
1737 PRIMARY KEY (`roadtypeid`)
1738 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1741 -- Table structure for table `saved_sql`
1744 DROP TABLE IF EXISTS `saved_sql`;
1745 CREATE TABLE saved_sql (
1746 `id` int(11) NOT NULL auto_increment,
1747 `borrowernumber` int(11) default NULL,
1748 `date_created` datetime default NULL,
1749 `last_modified` datetime default NULL,
1751 `last_run` datetime default NULL,
1752 `report_name` varchar(255) default NULL,
1753 `type` varchar(255) default NULL,
1756 KEY boridx (`borrowernumber`)
1757 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1761 -- Table structure for `saved_reports`
1764 DROP TABLE IF EXISTS `saved_reports`;
1765 CREATE TABLE saved_reports (
1766 `id` int(11) NOT NULL auto_increment,
1767 `report_id` int(11) default NULL,
1769 `date_run` datetime default NULL,
1771 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1775 -- Table structure for table `serial`
1778 DROP TABLE IF EXISTS `serial`;
1779 CREATE TABLE `serial` (
1780 `serialid` int(11) NOT NULL auto_increment,
1781 `biblionumber` varchar(100) NOT NULL default '',
1782 `subscriptionid` varchar(100) NOT NULL default '',
1783 `serialseq` varchar(100) NOT NULL default '',
1784 `status` tinyint(4) NOT NULL default 0,
1785 `planneddate` date default NULL,
1787 `publisheddate` date default NULL,
1788 `itemnumber` text default NULL,
1789 `claimdate` date default NULL,
1790 `routingnotes` text,
1791 PRIMARY KEY (`serialid`)
1792 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1795 -- Table structure for table `sessions`
1798 DROP TABLE IF EXISTS sessions;
1799 CREATE TABLE sessions (
1800 `id` varchar(32) NOT NULL,
1801 `a_session` text NOT NULL,
1803 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1806 -- Table structure for table `special_holidays`
1809 DROP TABLE IF EXISTS `special_holidays`;
1810 CREATE TABLE `special_holidays` (
1811 `id` int(11) NOT NULL auto_increment,
1812 `branchcode` varchar(10) NOT NULL default '',
1813 `day` smallint(6) NOT NULL default 0,
1814 `month` smallint(6) NOT NULL default 0,
1815 `year` smallint(6) NOT NULL default 0,
1816 `isexception` smallint(1) NOT NULL default 1,
1817 `title` varchar(50) NOT NULL default '',
1818 `description` text NOT NULL,
1820 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1823 -- Table structure for table `statistics`
1826 DROP TABLE IF EXISTS `statistics`;
1827 CREATE TABLE `statistics` (
1828 `datetime` datetime default NULL,
1829 `branch` varchar(10) default NULL,
1830 `proccode` varchar(4) default NULL,
1831 `value` double(16,4) default NULL,
1832 `type` varchar(16) default NULL,
1834 `usercode` varchar(10) default NULL,
1835 `itemnumber` int(11) default NULL,
1836 `itemtype` varchar(10) default NULL,
1837 `borrowernumber` int(11) default NULL,
1838 `associatedborrower` int(11) default NULL,
1839 KEY `timeidx` (`datetime`)
1840 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1843 -- Table structure for table `stopwords`
1846 DROP TABLE IF EXISTS `stopwords`;
1847 CREATE TABLE `stopwords` (
1848 `word` varchar(255) default NULL
1849 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1852 -- Table structure for table `subscription`
1855 DROP TABLE IF EXISTS `subscription`;
1856 CREATE TABLE `subscription` (
1857 `biblionumber` int(11) NOT NULL default 0,
1858 `subscriptionid` int(11) NOT NULL auto_increment,
1859 `librarian` varchar(100) default '',
1860 `startdate` date default NULL,
1861 `aqbooksellerid` int(11) default 0,
1862 `cost` int(11) default 0,
1863 `aqbudgetid` int(11) default 0,
1864 `weeklength` int(11) default 0,
1865 `monthlength` int(11) default 0,
1866 `numberlength` int(11) default 0,
1867 `periodicity` tinyint(4) default 0,
1868 `dow` varchar(100) default '',
1869 `numberingmethod` varchar(100) default '',
1871 `status` varchar(100) NOT NULL default '',
1872 `add1` int(11) default 0,
1873 `every1` int(11) default 0,
1874 `whenmorethan1` int(11) default 0,
1875 `setto1` int(11) default NULL,
1876 `lastvalue1` int(11) default NULL,
1877 `add2` int(11) default 0,
1878 `every2` int(11) default 0,
1879 `whenmorethan2` int(11) default 0,
1880 `setto2` int(11) default NULL,
1881 `lastvalue2` int(11) default NULL,
1882 `add3` int(11) default 0,
1883 `every3` int(11) default 0,
1884 `innerloop1` int(11) default 0,
1885 `innerloop2` int(11) default 0,
1886 `innerloop3` int(11) default 0,
1887 `whenmorethan3` int(11) default 0,
1888 `setto3` int(11) default NULL,
1889 `lastvalue3` int(11) default NULL,
1890 `issuesatonce` tinyint(3) NOT NULL default 1,
1891 `firstacquidate` date default NULL,
1892 `manualhistory` tinyint(1) NOT NULL default 0,
1893 `irregularity` text,
1894 `letter` varchar(20) default NULL,
1895 `numberpattern` tinyint(3) default 0,
1896 `distributedto` text,
1897 `internalnotes` longtext,
1899 `location` varchar(80) NULL default '',
1900 `branchcode` varchar(10) NOT NULL default '',
1901 `hemisphere` tinyint(3) default 0,
1902 `lastbranch` varchar(10),
1903 `serialsadditems` tinyint(1) NOT NULL default '0',
1904 `staffdisplaycount` VARCHAR(10) NULL,
1905 `opacdisplaycount` VARCHAR(10) NULL,
1906 `graceperiod` int(11) NOT NULL default '0',
1907 PRIMARY KEY (`subscriptionid`)
1908 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1911 -- Table structure for table `subscriptionhistory`
1914 DROP TABLE IF EXISTS `subscriptionhistory`;
1915 CREATE TABLE `subscriptionhistory` (
1916 `biblionumber` int(11) NOT NULL default 0,
1917 `subscriptionid` int(11) NOT NULL default 0,
1918 `histstartdate` date default NULL,
1919 `enddate` date default NULL,
1920 `missinglist` longtext NOT NULL,
1921 `recievedlist` longtext NOT NULL,
1922 `opacnote` varchar(150) NOT NULL default '',
1923 `librariannote` varchar(150) NOT NULL default '',
1924 PRIMARY KEY (`subscriptionid`),
1925 KEY `biblionumber` (`biblionumber`)
1926 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1929 -- Table structure for table `subscriptionroutinglist`
1932 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1933 CREATE TABLE `subscriptionroutinglist` (
1934 `routingid` int(11) NOT NULL auto_increment,
1935 `borrowernumber` int(11) default NULL,
1936 `ranking` int(11) default NULL,
1937 `subscriptionid` int(11) default NULL,
1938 PRIMARY KEY (`routingid`)
1939 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1942 -- Table structure for table `suggestions`
1945 DROP TABLE IF EXISTS `suggestions`;
1946 CREATE TABLE `suggestions` (
1947 `suggestionid` int(8) NOT NULL auto_increment,
1948 `suggestedby` int(11) NOT NULL default 0,
1949 `managedby` int(11) default NULL,
1950 `STATUS` varchar(10) NOT NULL default '',
1952 `author` varchar(80) default NULL,
1953 `title` varchar(80) default NULL,
1954 `copyrightdate` smallint(6) default NULL,
1955 `publishercode` varchar(255) default NULL,
1956 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1957 `volumedesc` varchar(255) default NULL,
1958 `publicationyear` smallint(6) default 0,
1959 `place` varchar(255) default NULL,
1960 `isbn` varchar(30) default NULL,
1961 `mailoverseeing` smallint(1) default 0,
1962 `biblionumber` int(11) default NULL,
1964 PRIMARY KEY (`suggestionid`),
1965 KEY `suggestedby` (`suggestedby`),
1966 KEY `managedby` (`managedby`)
1967 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1970 -- Table structure for table `systempreferences`
1973 DROP TABLE IF EXISTS `systempreferences`;
1974 CREATE TABLE `systempreferences` (
1975 `variable` varchar(50) NOT NULL default '',
1977 `options` mediumtext,
1979 `type` varchar(20) default NULL,
1980 PRIMARY KEY (`variable`)
1981 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1984 -- Table structure for table `tags`
1987 DROP TABLE IF EXISTS `tags`;
1988 CREATE TABLE `tags` (
1989 `entry` varchar(255) NOT NULL default '',
1990 `weight` bigint(20) NOT NULL default 0,
1991 PRIMARY KEY (`entry`)
1992 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1995 -- Table structure for table `tags_all`
1998 DROP TABLE IF EXISTS `tags_all`;
1999 CREATE TABLE `tags_all` (
2000 `tag_id` int(11) NOT NULL auto_increment,
2001 `borrowernumber` int(11) NOT NULL,
2002 `biblionumber` int(11) NOT NULL,
2003 `term` varchar(255) NOT NULL,
2004 `language` int(4) default NULL,
2005 `date_created` datetime NOT NULL,
2006 PRIMARY KEY (`tag_id`),
2007 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2008 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2009 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2010 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2011 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2012 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2013 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2016 -- Table structure for table `tags_approval`
2019 DROP TABLE IF EXISTS `tags_approval`;
2020 CREATE TABLE `tags_approval` (
2021 `term` varchar(255) NOT NULL,
2022 `approved` int(1) NOT NULL default '0',
2023 `date_approved` datetime default NULL,
2024 `approved_by` int(11) default NULL,
2025 `weight_total` int(9) NOT NULL default '1',
2026 PRIMARY KEY (`term`),
2027 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2028 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2029 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2030 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2033 -- Table structure for table `tags_index`
2036 DROP TABLE IF EXISTS `tags_index`;
2037 CREATE TABLE `tags_index` (
2038 `term` varchar(255) NOT NULL,
2039 `biblionumber` int(11) NOT NULL,
2040 `weight` int(9) NOT NULL default '1',
2041 PRIMARY KEY (`term`,`biblionumber`),
2042 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2043 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2044 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2045 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2046 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2047 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2050 -- Table structure for table `userflags`
2053 DROP TABLE IF EXISTS `userflags`;
2054 CREATE TABLE `userflags` (
2055 `bit` int(11) NOT NULL default 0,
2056 `flag` varchar(30) default NULL,
2057 `flagdesc` varchar(255) default NULL,
2058 `defaulton` int(11) default NULL,
2060 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2063 -- Table structure for table `virtualshelves`
2066 DROP TABLE IF EXISTS `virtualshelves`;
2067 CREATE TABLE `virtualshelves` (
2068 `shelfnumber` int(11) NOT NULL auto_increment,
2069 `shelfname` varchar(255) default NULL,
2070 `owner` varchar(80) default NULL,
2071 `category` varchar(1) default NULL,
2072 `sortfield` varchar(16) default NULL,
2073 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2074 PRIMARY KEY (`shelfnumber`)
2075 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2078 -- Table structure for table `virtualshelfcontents`
2081 DROP TABLE IF EXISTS `virtualshelfcontents`;
2082 CREATE TABLE `virtualshelfcontents` (
2083 `shelfnumber` int(11) NOT NULL default 0,
2084 `biblionumber` int(11) NOT NULL default 0,
2085 `flags` int(11) default NULL,
2086 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2087 KEY `shelfnumber` (`shelfnumber`),
2088 KEY `biblionumber` (`biblionumber`),
2089 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2090 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2091 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2094 -- Table structure for table `z3950servers`
2097 DROP TABLE IF EXISTS `z3950servers`;
2098 CREATE TABLE `z3950servers` (
2099 `host` varchar(255) default NULL,
2100 `port` int(11) default NULL,
2101 `db` varchar(255) default NULL,
2102 `userid` varchar(255) default NULL,
2103 `password` varchar(255) default NULL,
2105 `id` int(11) NOT NULL auto_increment,
2106 `checked` smallint(6) default NULL,
2107 `rank` int(11) default NULL,
2108 `syntax` varchar(80) default NULL,
2110 `position` enum('primary','secondary','') NOT NULL default 'primary',
2111 `type` enum('zed','opensearch') NOT NULL default 'zed',
2112 `encoding` text default NULL,
2113 `description` text NOT NULL,
2115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2118 -- Table structure for table `zebraqueue`
2121 DROP TABLE IF EXISTS `zebraqueue`;
2122 CREATE TABLE `zebraqueue` (
2123 `id` int(11) NOT NULL auto_increment,
2124 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2125 `operation` char(20) NOT NULL default '',
2126 `server` char(20) NOT NULL default '',
2127 `done` int(11) NOT NULL default '0',
2128 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2130 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2133 DROP TABLE IF EXISTS `services_throttle`;
2134 CREATE TABLE `services_throttle` (
2135 `service_type` varchar(10) NOT NULL default '',
2136 `service_count` varchar(45) default NULL,
2137 PRIMARY KEY (`service_type`)
2138 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2140 -- http://www.w3.org/International/articles/language-tags/
2143 DROP TABLE IF EXISTS language_subtag_registry;
2144 CREATE TABLE language_subtag_registry (
2146 type varchar(25), -- language-script-region-variant-extension-privateuse
2147 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2149 KEY `subtag` (`subtag`)
2150 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2152 -- TODO: add suppress_scripts
2153 -- this maps three letter codes defined in iso639.2 back to their
2154 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2155 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2156 CREATE TABLE language_rfc4646_to_iso639 (
2157 rfc4646_subtag varchar(25),
2158 iso639_2_code varchar(25),
2159 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2160 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2162 DROP TABLE IF EXISTS language_descriptions;
2163 CREATE TABLE language_descriptions (
2167 description varchar(255),
2169 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2171 -- bi-directional support, keyed by script subcode
2172 DROP TABLE IF EXISTS language_script_bidi;
2173 CREATE TABLE language_script_bidi (
2174 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2175 bidi varchar(3), -- rtl ltr
2176 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2177 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2179 -- TODO: need to map language subtags to script subtags for detection
2180 -- of bidi when script is not specified (like ar, he)
2181 DROP TABLE IF EXISTS language_script_mapping;
2182 CREATE TABLE language_script_mapping (
2183 language_subtag varchar(25),
2184 script_subtag varchar(25),
2185 KEY `language_subtag` (`language_subtag`)
2186 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2188 DROP TABLE IF EXISTS `permissions`;
2189 CREATE TABLE `permissions` (
2190 `module_bit` int(11) NOT NULL DEFAULT 0,
2191 `code` varchar(64) DEFAULT NULL,
2192 `description` varchar(255) DEFAULT NULL,
2193 PRIMARY KEY (`module_bit`, `code`),
2194 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2195 ON DELETE CASCADE ON UPDATE CASCADE
2196 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2198 DROP TABLE IF EXISTS `serialitems`;
2199 CREATE TABLE `serialitems` (
2200 `itemnumber` int(11) NOT NULL,
2201 `serialid` int(11) NOT NULL,
2202 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2203 KEY `serialitems_sfk_1` (`serialid`),
2204 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2207 DROP TABLE IF EXISTS `user_permissions`;
2208 CREATE TABLE `user_permissions` (
2209 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2210 `module_bit` int(11) NOT NULL DEFAULT 0,
2211 `code` varchar(64) DEFAULT NULL,
2212 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2213 ON DELETE CASCADE ON UPDATE CASCADE,
2214 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2215 ON DELETE CASCADE ON UPDATE CASCADE
2216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2219 -- Table structure for table `tmp_holdsqueue`
2222 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2223 CREATE TABLE `tmp_holdsqueue` (
2224 `biblionumber` int(11) default NULL,
2225 `itemnumber` int(11) default NULL,
2226 `barcode` varchar(20) default NULL,
2227 `surname` mediumtext NOT NULL,
2230 `borrowernumber` int(11) NOT NULL,
2231 `cardnumber` varchar(16) default NULL,
2232 `reservedate` date default NULL,
2234 `itemcallnumber` varchar(30) default NULL,
2235 `holdingbranch` varchar(10) default NULL,
2236 `pickbranch` varchar(10) default NULL,
2238 `item_level_request` tinyint(4) NOT NULL default 0
2239 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2242 -- Table structure for table `message_queue`
2245 DROP TABLE IF EXISTS `message_queue`;
2246 CREATE TABLE `message_queue` (
2247 `message_id` int(11) NOT NULL auto_increment,
2248 `borrowernumber` int(11) default NULL,
2251 `metadata` text DEFAULT NULL,
2252 `letter_code` varchar(64) DEFAULT NULL,
2253 `message_transport_type` varchar(20) NOT NULL,
2254 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2255 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2256 `to_address` mediumtext,
2257 `from_address` mediumtext,
2258 `content_type` text,
2259 KEY `message_id` (`message_id`),
2260 KEY `borrowernumber` (`borrowernumber`),
2261 KEY `message_transport_type` (`message_transport_type`),
2262 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2263 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2264 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2267 -- Table structure for table `message_transport_types`
2270 DROP TABLE IF EXISTS `message_transport_types`;
2271 CREATE TABLE `message_transport_types` (
2272 `message_transport_type` varchar(20) NOT NULL,
2273 PRIMARY KEY (`message_transport_type`)
2274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2277 -- Table structure for table `message_attributes`
2280 DROP TABLE IF EXISTS `message_attributes`;
2281 CREATE TABLE `message_attributes` (
2282 `message_attribute_id` int(11) NOT NULL auto_increment,
2283 `message_name` varchar(20) NOT NULL default '',
2284 `takes_days` tinyint(1) NOT NULL default '0',
2285 PRIMARY KEY (`message_attribute_id`),
2286 UNIQUE KEY `message_name` (`message_name`)
2287 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2290 -- Table structure for table `message_transports`
2293 DROP TABLE IF EXISTS `message_transports`;
2294 CREATE TABLE `message_transports` (
2295 `message_attribute_id` int(11) NOT NULL,
2296 `message_transport_type` varchar(20) NOT NULL,
2297 `is_digest` tinyint(1) NOT NULL default '0',
2298 `letter_module` varchar(20) NOT NULL default '',
2299 `letter_code` varchar(20) NOT NULL default '',
2300 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2301 KEY `message_transport_type` (`message_transport_type`),
2302 KEY `letter_module` (`letter_module`,`letter_code`),
2303 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2304 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2305 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2306 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2309 -- Table structure for table `borrower_message_preferences`
2312 DROP TABLE IF EXISTS `borrower_message_preferences`;
2313 CREATE TABLE `borrower_message_preferences` (
2314 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2315 `borrowernumber` int(11) default NULL,
2316 `categorycode` varchar(10) default NULL,
2317 `message_attribute_id` int(11) default '0',
2318 `days_in_advance` int(11) default '0',
2319 `wants_digest` tinyint(1) NOT NULL default '0',
2320 PRIMARY KEY (`borrower_message_preference_id`),
2321 KEY `borrowernumber` (`borrowernumber`),
2322 KEY `categorycode` (`categorycode`),
2323 KEY `message_attribute_id` (`message_attribute_id`),
2324 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2325 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2326 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2327 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2330 -- Table structure for table `borrower_message_transport_preferences`
2333 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2334 CREATE TABLE `borrower_message_transport_preferences` (
2335 `borrower_message_preference_id` int(11) NOT NULL default '0',
2336 `message_transport_type` varchar(20) NOT NULL default '0',
2337 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2338 KEY `message_transport_type` (`message_transport_type`),
2339 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,
2340 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
2341 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2344 -- Table structure for the table branch_transfer_limits
2347 DROP TABLE IF EXISTS `branch_transfer_limits`;
2348 CREATE TABLE branch_transfer_limits (
2349 limitId int(8) NOT NULL auto_increment,
2350 toBranch varchar(10) NOT NULL,
2351 fromBranch varchar(10) NOT NULL,
2352 itemtype varchar(10) NULL,
2353 ccode varchar(10) NULL,
2354 PRIMARY KEY (limitId)
2355 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2358 -- Table structure for table `item_circulation_alert_preferences`
2361 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2362 CREATE TABLE `item_circulation_alert_preferences` (
2363 `id` int(11) NOT NULL auto_increment,
2364 `branchcode` varchar(10) NOT NULL,
2365 `categorycode` varchar(10) NOT NULL,
2366 `item_type` varchar(10) NOT NULL,
2367 `notification` varchar(16) NOT NULL,
2369 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2370 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2372 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2373 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2374 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2375 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2376 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2377 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2378 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2379 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;