3 -- Host: localhost Database: koha30test
4 -- ------------------------------------------------------
5 -- Server version 4.1.22
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
11 /*!40101 SET NAMES utf8 */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19 -- Table structure for table `accountlines`
22 DROP TABLE IF EXISTS `accountlines`;
23 CREATE TABLE `accountlines` (
24 `borrowernumber` int(11) NOT NULL default 0,
25 `accountno` smallint(6) NOT NULL default 0,
26 `itemnumber` int(11) default NULL,
27 `date` date default NULL,
28 `amount` decimal(28,6) default NULL,
29 `description` mediumtext,
31 `accounttype` varchar(5) default NULL,
32 `amountoutstanding` decimal(28,6) default NULL,
33 `lastincrement` decimal(28,6) default NULL,
34 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
35 `notify_id` int(11) NOT NULL default 0,
36 `notify_level` int(2) NOT NULL default 0,
37 KEY `acctsborridx` (`borrowernumber`),
38 KEY `timeidx` (`timestamp`),
39 KEY `itemnumber` (`itemnumber`),
40 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
41 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
45 -- Table structure for table `accountoffsets`
48 DROP TABLE IF EXISTS `accountoffsets`;
49 CREATE TABLE `accountoffsets` (
50 `borrowernumber` int(11) NOT NULL default 0,
51 `accountno` smallint(6) NOT NULL default 0,
52 `offsetaccount` smallint(6) NOT NULL default 0,
53 `offsetamount` decimal(28,6) default NULL,
54 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
55 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
56 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
59 -- Table structure for table `action_logs`
62 DROP TABLE IF EXISTS `action_logs`;
63 CREATE TABLE `action_logs` (
64 `action_id` int(11) NOT NULL auto_increment,
65 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
66 `user` int(11) NOT NULL default 0,
69 `object` int(11) default NULL,
71 PRIMARY KEY (`action_id`),
72 KEY (`timestamp`,`user`)
73 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
76 -- Table structure for table `alert`
79 DROP TABLE IF EXISTS `alert`;
80 CREATE TABLE `alert` (
81 `alertid` int(11) NOT NULL auto_increment,
82 `borrowernumber` int(11) NOT NULL default 0,
83 `type` varchar(10) NOT NULL default '',
84 `externalid` varchar(20) NOT NULL default '',
85 PRIMARY KEY (`alertid`),
86 KEY `borrowernumber` (`borrowernumber`),
87 KEY `type` (`type`,`externalid`)
88 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
91 -- Table structure for table `aqbasket`
94 DROP TABLE IF EXISTS `aqbasket`;
95 CREATE TABLE `aqbasket` (
96 `basketno` int(11) NOT NULL auto_increment,
97 `creationdate` date default NULL,
98 `closedate` date default NULL,
99 `booksellerid` int(11) NOT NULL default 1,
100 `authorisedby` varchar(10) default NULL,
101 `booksellerinvoicenumber` mediumtext,
102 PRIMARY KEY (`basketno`),
103 KEY `booksellerid` (`booksellerid`),
104 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE
105 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
108 -- Table structure for table `aqbookfund`
111 DROP TABLE IF EXISTS `aqbookfund`;
112 CREATE TABLE `aqbookfund` (
113 `bookfundid` varchar(10) NOT NULL default '',
114 `bookfundname` mediumtext,
115 `bookfundgroup` varchar(5) default NULL,
116 `branchcode` varchar(10) NOT NULL default '',
117 PRIMARY KEY (`bookfundid`,`branchcode`)
118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
121 -- Table structure for table `aqbooksellers`
124 DROP TABLE IF EXISTS `aqbooksellers`;
125 CREATE TABLE `aqbooksellers` (
126 `id` int(11) NOT NULL auto_increment,
127 `name` mediumtext NOT NULL,
128 `address1` mediumtext,
129 `address2` mediumtext,
130 `address3` mediumtext,
131 `address4` mediumtext,
132 `phone` varchar(30) default NULL,
133 `accountnumber` mediumtext,
134 `othersupplier` mediumtext,
135 `currency` varchar(3) NOT NULL default '',
136 `deliverydays` smallint(6) default NULL,
137 `followupdays` smallint(6) default NULL,
138 `followupscancel` smallint(6) default NULL,
139 `specialty` mediumtext,
140 `booksellerfax` mediumtext,
142 `bookselleremail` mediumtext,
143 `booksellerurl` mediumtext,
144 `contact` varchar(100) default NULL,
146 `url` varchar(255) default NULL,
147 `contpos` varchar(100) default NULL,
148 `contphone` varchar(100) default NULL,
149 `contfax` varchar(100) default NULL,
150 `contaltphone` varchar(100) default NULL,
151 `contemail` varchar(100) default NULL,
152 `contnotes` mediumtext,
153 `active` tinyint(4) default NULL,
154 `listprice` varchar(10) default NULL,
155 `invoiceprice` varchar(10) default NULL,
156 `gstreg` tinyint(4) default NULL,
157 `listincgst` tinyint(4) default NULL,
158 `invoiceincgst` tinyint(4) default NULL,
159 `discount` float(6,4) default NULL,
160 `fax` varchar(50) default NULL,
161 `nocalc` int(11) default NULL,
162 `invoicedisc` float(6,4) default NULL,
164 KEY `listprice` (`listprice`),
165 KEY `invoiceprice` (`invoiceprice`),
166 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
167 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
171 -- Table structure for table `aqbudget`
174 DROP TABLE IF EXISTS `aqbudget`;
175 CREATE TABLE `aqbudget` (
176 `bookfundid` varchar(10) NOT NULL default '',
177 `startdate` date NOT NULL default 0,
178 `enddate` date default NULL,
179 `budgetamount` decimal(13,2) default NULL,
180 `aqbudgetid` tinyint(4) NOT NULL auto_increment,
181 `branchcode` varchar(10) default NULL,
182 PRIMARY KEY (`aqbudgetid`)
183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
186 -- Table structure for table `aqorderbreakdown`
189 DROP TABLE IF EXISTS `aqorderbreakdown`;
190 CREATE TABLE `aqorderbreakdown` (
191 `ordernumber` int(11) default NULL,
192 `linenumber` int(11) default NULL,
193 `branchcode` varchar(10) default NULL,
194 `bookfundid` varchar(10) NOT NULL default '',
195 `allocation` smallint(6) default NULL,
196 KEY `ordernumber` (`ordernumber`),
197 KEY `bookfundid` (`bookfundid`),
198 CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
199 CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
203 -- Table structure for table `aqorderdelivery`
206 DROP TABLE IF EXISTS `aqorderdelivery`;
207 CREATE TABLE `aqorderdelivery` (
208 `ordernumber` date default NULL,
209 `deliverynumber` smallint(6) NOT NULL default 0,
210 `deliverydate` varchar(18) default NULL,
211 `qtydelivered` smallint(6) default NULL,
212 `deliverycomments` mediumtext
213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
216 -- Table structure for table `aqorders`
219 DROP TABLE IF EXISTS `aqorders`;
220 CREATE TABLE `aqorders` (
221 `ordernumber` int(11) NOT NULL auto_increment,
222 `biblionumber` int(11) default NULL,
224 `entrydate` date default NULL,
225 `quantity` smallint(6) default NULL,
226 `currency` varchar(3) default NULL,
227 `listprice` decimal(28,6) default NULL,
228 `totalamount` decimal(28,6) default NULL,
229 `datereceived` date default NULL,
230 `booksellerinvoicenumber` mediumtext,
231 `freight` decimal(28,6) default NULL,
232 `unitprice` decimal(28,6) default NULL,
233 `quantityreceived` smallint(6) default NULL,
234 `cancelledby` varchar(10) default NULL,
235 `datecancellationprinted` date default NULL,
237 `supplierreference` mediumtext,
238 `purchaseordernumber` mediumtext,
239 `subscription` tinyint(1) default NULL,
240 `serialid` varchar(30) default NULL,
241 `basketno` int(11) default NULL,
242 `biblioitemnumber` int(11) default NULL,
243 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
244 `rrp` decimal(13,2) default NULL,
245 `ecost` decimal(13,2) default NULL,
246 `gst` decimal(13,2) default NULL,
247 `budgetdate` date default NULL,
248 `sort1` varchar(80) default NULL,
249 `sort2` varchar(80) default NULL,
250 PRIMARY KEY (`ordernumber`),
251 KEY `basketno` (`basketno`),
252 KEY `biblionumber` (`biblionumber`),
253 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
254 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL
255 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
258 -- Table structure for table `auth_header`
261 DROP TABLE IF EXISTS `auth_header`;
262 CREATE TABLE `auth_header` (
263 `authid` bigint(20) unsigned NOT NULL auto_increment,
264 `authtypecode` varchar(10) NOT NULL default '',
265 `datecreated` date default NULL,
266 `datemodified` date default NULL,
267 `origincode` varchar(20) default NULL,
268 `authtrees` mediumtext,
270 `linkid` bigint(20) default NULL,
271 `marcxml` longtext NOT NULL,
272 PRIMARY KEY (`authid`),
273 KEY `origincode` (`origincode`)
274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
277 -- Table structure for table `auth_subfield_structure`
280 DROP TABLE IF EXISTS `auth_subfield_structure`;
281 CREATE TABLE `auth_subfield_structure` (
282 `authtypecode` varchar(10) NOT NULL default '',
283 `tagfield` varchar(3) NOT NULL default '',
284 `tagsubfield` varchar(1) NOT NULL default '',
285 `liblibrarian` varchar(255) NOT NULL default '',
286 `libopac` varchar(255) NOT NULL default '',
287 `repeatable` tinyint(4) NOT NULL default 0,
288 `mandatory` tinyint(4) NOT NULL default 0,
289 `tab` tinyint(1) default NULL,
290 `authorised_value` varchar(10) default NULL,
291 `value_builder` varchar(80) default NULL,
292 `seealso` varchar(255) default NULL,
293 `isurl` tinyint(1) default NULL,
294 `hidden` tinyint(3) NOT NULL default 0,
295 `linkid` tinyint(1) NOT NULL default 0,
296 `kohafield` varchar(45) NULL default '',
297 `frameworkcode` varchar(8) NOT NULL default '',
298 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
299 KEY `tab` (`authtypecode`,`tab`)
300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
303 -- Table structure for table `auth_tag_structure`
306 DROP TABLE IF EXISTS `auth_tag_structure`;
307 CREATE TABLE `auth_tag_structure` (
308 `authtypecode` varchar(10) NOT NULL default '',
309 `tagfield` varchar(3) NOT NULL default '',
310 `liblibrarian` varchar(255) NOT NULL default '',
311 `libopac` varchar(255) NOT NULL default '',
312 `repeatable` tinyint(4) NOT NULL default 0,
313 `mandatory` tinyint(4) NOT NULL default 0,
314 `authorised_value` varchar(10) default NULL,
315 PRIMARY KEY (`authtypecode`,`tagfield`),
316 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
320 -- Table structure for table `auth_types`
323 DROP TABLE IF EXISTS `auth_types`;
324 CREATE TABLE `auth_types` (
325 `authtypecode` varchar(10) NOT NULL default '',
326 `authtypetext` varchar(255) NOT NULL default '',
327 `auth_tag_to_report` varchar(3) NOT NULL default '',
328 `summary` mediumtext NOT NULL,
329 PRIMARY KEY (`authtypecode`)
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
333 -- Table structure for table `authorised_values`
336 DROP TABLE IF EXISTS `authorised_values`;
337 CREATE TABLE `authorised_values` (
338 `id` int(11) NOT NULL auto_increment,
339 `category` varchar(10) NOT NULL default '',
340 `authorised_value` varchar(80) NOT NULL default '',
341 `lib` varchar(80) default NULL,
342 `imageurl` varchar(200) default NULL,
344 KEY `name` (`category`),
346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
349 -- Table structure for table `biblio`
352 DROP TABLE IF EXISTS `biblio`;
353 CREATE TABLE `biblio` (
354 `biblionumber` int(11) NOT NULL auto_increment,
355 `frameworkcode` varchar(4) NOT NULL default '',
358 `unititle` mediumtext,
360 `serial` tinyint(1) default NULL,
361 `seriestitle` mediumtext,
362 `copyrightdate` smallint(6) default NULL,
363 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
364 `datecreated` DATE NOT NULL,
365 `abstract` mediumtext,
366 PRIMARY KEY (`biblionumber`),
367 KEY `blbnoidx` (`biblionumber`)
368 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
371 -- Table structure for table `biblio_framework`
374 DROP TABLE IF EXISTS `biblio_framework`;
375 CREATE TABLE `biblio_framework` (
376 `frameworkcode` varchar(4) NOT NULL default '',
377 `frameworktext` varchar(255) NOT NULL default '',
378 PRIMARY KEY (`frameworkcode`)
379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
382 -- Table structure for table `biblioitems`
385 DROP TABLE IF EXISTS `biblioitems`;
386 CREATE TABLE `biblioitems` (
387 `biblioitemnumber` int(11) NOT NULL auto_increment,
388 `biblionumber` int(11) NOT NULL default 0,
391 `itemtype` varchar(10) default NULL,
392 `isbn` varchar(30) default NULL,
393 `issn` varchar(9) default NULL,
394 `publicationyear` text,
395 `publishercode` varchar(255) default NULL,
396 `volumedate` date default NULL,
398 `collectiontitle` mediumtext default NULL,
399 `collectionissn` text default NULL,
400 `collectionvolume` mediumtext default NULL,
401 `editionstatement` text default NULL,
402 `editionresponsibility` text default NULL,
403 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
404 `illus` varchar(255) default NULL,
405 `pages` varchar(255) default NULL,
407 `size` varchar(255) default NULL,
408 `place` varchar(255) default NULL,
409 `lccn` varchar(25) default NULL,
411 `url` varchar(255) default NULL,
412 `cn_source` varchar(10) default NULL,
413 `cn_class` varchar(30) default NULL,
414 `cn_item` varchar(10) default NULL,
415 `cn_suffix` varchar(10) default NULL,
416 `cn_sort` varchar(30) default NULL,
417 `totalissues` int(10),
418 `marcxml` longtext NOT NULL,
419 PRIMARY KEY (`biblioitemnumber`),
420 KEY `bibinoidx` (`biblioitemnumber`),
421 KEY `bibnoidx` (`biblionumber`),
423 KEY `publishercode` (`publishercode`),
425 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
429 -- Table structure for table `borrowers`
432 DROP TABLE IF EXISTS `borrowers`;
433 CREATE TABLE `borrowers` (
434 `borrowernumber` int(11) NOT NULL auto_increment,
435 `cardnumber` varchar(16) default NULL,
436 `surname` mediumtext NOT NULL,
439 `othernames` mediumtext,
441 `streetnumber` varchar(10) default NULL,
442 `streettype` varchar(50) default NULL,
443 `address` mediumtext NOT NULL,
445 `city` mediumtext NOT NULL,
446 `zipcode` varchar(25) default NULL,
450 `mobile` varchar(50) default NULL,
454 `B_streetnumber` varchar(10) default NULL,
455 `B_streettype` varchar(50) default NULL,
456 `B_address` varchar(100) default NULL,
457 `B_address2` text default NULL,
459 `B_zipcode` varchar(25) default NULL,
462 `B_phone` mediumtext,
463 `dateofbirth` date default NULL,
464 `branchcode` varchar(10) NOT NULL default '',
465 `categorycode` varchar(10) NOT NULL default '',
466 `dateenrolled` date default NULL,
467 `dateexpiry` date default NULL,
468 `gonenoaddress` tinyint(1) default NULL,
469 `lost` tinyint(1) default NULL,
470 `debarred` tinyint(1) default NULL,
471 `contactname` mediumtext,
472 `contactfirstname` text,
474 `guarantorid` int(11) default NULL,
475 `borrowernotes` mediumtext,
476 `relationship` varchar(100) default NULL,
477 `ethnicity` varchar(50) default NULL,
478 `ethnotes` varchar(255) default NULL,
479 `sex` varchar(1) default NULL,
480 `password` varchar(30) default NULL,
481 `flags` int(11) default NULL,
482 `userid` varchar(30) default NULL,
483 `opacnote` mediumtext,
484 `contactnote` varchar(255) default NULL,
485 `sort1` varchar(80) default NULL,
486 `sort2` varchar(80) default NULL,
487 `altcontactfirstname` varchar(255) default NULL,
488 `altcontactsurname` varchar(255) default NULL,
489 `altcontactaddress1` varchar(255) default NULL,
490 `altcontactaddress2` varchar(255) default NULL,
491 `altcontactaddress3` varchar(255) default NULL,
492 `altcontactzipcode` varchar(50) default NULL,
493 `altcontactcountry` text default NULL,
494 `altcontactphone` varchar(50) default NULL,
495 `smsalertnumber` varchar(50) default NULL,
496 UNIQUE KEY `cardnumber` (`cardnumber`),
497 PRIMARY KEY `borrowernumber` (`borrowernumber`),
498 KEY `categorycode` (`categorycode`),
499 KEY `branchcode` (`branchcode`),
500 KEY `userid` (`userid`),
501 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
502 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
503 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
506 -- Table structure for table `borrower_attribute_types`
509 DROP TABLE IF EXISTS `borrower_attribute_types`;
510 CREATE TABLE `borrower_attribute_types` (
511 `code` varchar(10) NOT NULL,
512 `description` varchar(255) NOT NULL,
513 `repeatable` tinyint(1) NOT NULL default 0,
514 `unique_id` tinyint(1) NOT NULL default 0,
515 `opac_display` tinyint(1) NOT NULL default 0,
516 `password_allowed` tinyint(1) NOT NULL default 0,
517 `staff_searchable` tinyint(1) NOT NULL default 0,
518 `authorised_value_category` varchar(10) default NULL,
520 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
523 -- Table structure for table `borrower_attributes`
526 DROP TABLE IF EXISTS `borrower_attributes`;
527 CREATE TABLE `borrower_attributes` (
528 `borrowernumber` int(11) NOT NULL,
529 `code` varchar(10) NOT NULL,
530 `attribute` varchar(64) default NULL,
531 `password` varchar(64) default NULL,
532 KEY `borrowernumber` (`borrowernumber`),
533 KEY `code_attribute` (`code`, `attribute`),
534 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
535 ON DELETE CASCADE ON UPDATE CASCADE,
536 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
537 ON DELETE CASCADE ON UPDATE CASCADE
538 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
540 CREATE TABLE `branch_item_rules` (
541 `branchcode` varchar(10) NOT NULL,
542 `itemtype` varchar(10) NOT NULL,
543 `holdallowed` tinyint(1) default NULL,
544 PRIMARY KEY (`itemtype`,`branchcode`),
545 KEY `branch_item_rules_ibfk_2` (`branchcode`),
546 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
547 ON DELETE CASCADE ON UPDATE CASCADE,
548 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
549 ON DELETE CASCADE ON UPDATE CASCADE
550 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
553 -- Table structure for table `branchcategories`
556 DROP TABLE IF EXISTS `branchcategories`;
557 CREATE TABLE `branchcategories` (
558 `categorycode` varchar(10) NOT NULL default '',
559 `categoryname` varchar(32),
560 `codedescription` mediumtext,
561 `categorytype` varchar(16),
562 PRIMARY KEY (`categorycode`)
563 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
566 -- Table structure for table `branches`
569 DROP TABLE IF EXISTS `branches`;
570 CREATE TABLE `branches` (
571 `branchcode` varchar(10) NOT NULL default '',
572 `branchname` mediumtext NOT NULL,
573 `branchaddress1` mediumtext,
574 `branchaddress2` mediumtext,
575 `branchaddress3` mediumtext,
576 `branchzip` varchar(25) default NULL,
577 `branchcity` mediumtext,
578 `branchcountry` text,
579 `branchphone` mediumtext,
580 `branchfax` mediumtext,
581 `branchemail` mediumtext,
582 `branchurl` mediumtext,
583 `issuing` tinyint(4) default NULL,
584 `branchip` varchar(15) default NULL,
585 `branchprinter` varchar(100) default NULL,
586 `branchnotes` mediumtext,
587 UNIQUE KEY `branchcode` (`branchcode`)
588 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
591 -- Table structure for table `branchrelations`
594 DROP TABLE IF EXISTS `branchrelations`;
595 CREATE TABLE `branchrelations` (
596 `branchcode` varchar(10) NOT NULL default '',
597 `categorycode` varchar(10) NOT NULL default '',
598 PRIMARY KEY (`branchcode`,`categorycode`),
599 KEY `branchcode` (`branchcode`),
600 KEY `categorycode` (`categorycode`),
601 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
602 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
603 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
606 -- Table structure for table `branchtransfers`
609 DROP TABLE IF EXISTS `branchtransfers`;
610 CREATE TABLE `branchtransfers` (
611 `itemnumber` int(11) NOT NULL default 0,
612 `datesent` datetime default NULL,
613 `frombranch` varchar(10) NOT NULL default '',
614 `datearrived` datetime default NULL,
615 `tobranch` varchar(10) NOT NULL default '',
616 `comments` mediumtext,
617 KEY `frombranch` (`frombranch`),
618 KEY `tobranch` (`tobranch`),
619 KEY `itemnumber` (`itemnumber`),
620 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
621 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
622 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
623 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
627 -- Table structure for table `browser`
629 DROP TABLE IF EXISTS `browser`;
630 CREATE TABLE `browser` (
631 `level` int(11) NOT NULL,
632 `classification` varchar(20) NOT NULL,
633 `description` varchar(255) NOT NULL,
634 `number` bigint(20) NOT NULL,
635 `endnode` tinyint(4) NOT NULL
636 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
639 -- Table structure for table `categories`
642 DROP TABLE IF EXISTS `categories`;
643 CREATE TABLE `categories` (
644 `categorycode` varchar(10) NOT NULL default '',
645 `description` mediumtext,
646 `enrolmentperiod` smallint(6) default NULL,
647 `upperagelimit` smallint(6) default NULL,
648 `dateofbirthrequired` tinyint(1) default NULL,
649 `finetype` varchar(30) default NULL,
650 `bulk` tinyint(1) default NULL,
651 `enrolmentfee` decimal(28,6) default NULL,
652 `overduenoticerequired` tinyint(1) default NULL,
653 `issuelimit` smallint(6) default NULL,
654 `reservefee` decimal(28,6) default NULL,
655 `category_type` varchar(1) NOT NULL default 'A',
656 PRIMARY KEY (`categorycode`),
657 UNIQUE KEY `categorycode` (`categorycode`)
658 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
661 -- Table structure for table `borrower_branch_circ_rules`
664 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
665 CREATE TABLE `branch_borrower_circ_rules` (
666 `branchcode` VARCHAR(10) NOT NULL,
667 `categorycode` VARCHAR(10) NOT NULL,
668 `maxissueqty` int(4) default NULL,
669 PRIMARY KEY (`categorycode`, `branchcode`),
670 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
671 ON DELETE CASCADE ON UPDATE CASCADE,
672 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
673 ON DELETE CASCADE ON UPDATE CASCADE
674 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
677 -- Table structure for table `default_borrower_circ_rules`
680 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
681 CREATE TABLE `default_borrower_circ_rules` (
682 `categorycode` VARCHAR(10) NOT NULL,
683 `maxissueqty` int(4) default NULL,
684 PRIMARY KEY (`categorycode`),
685 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
686 ON DELETE CASCADE ON UPDATE CASCADE
687 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
690 -- Table structure for table `default_branch_circ_rules`
693 DROP TABLE IF EXISTS `default_branch_circ_rules`;
694 CREATE TABLE `default_branch_circ_rules` (
695 `branchcode` VARCHAR(10) NOT NULL,
696 `maxissueqty` int(4) default NULL,
697 `holdallowed` tinyint(1) default NULL,
698 PRIMARY KEY (`branchcode`),
699 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
700 ON DELETE CASCADE ON UPDATE CASCADE
701 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
704 -- Table structure for table `default_branch_item_rules`
707 CREATE TABLE `default_branch_item_rules` (
708 `itemtype` varchar(10) NOT NULL,
709 `holdallowed` tinyint(1) default NULL,
710 PRIMARY KEY (`itemtype`),
711 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
712 ON DELETE CASCADE ON UPDATE CASCADE
713 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
716 -- Table structure for table `default_circ_rules`
719 DROP TABLE IF EXISTS `default_circ_rules`;
720 CREATE TABLE `default_circ_rules` (
721 `singleton` enum('singleton') NOT NULL default 'singleton',
722 `maxissueqty` int(4) default NULL,
723 `holdallowed` int(1) default NULL,
724 PRIMARY KEY (`singleton`)
725 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
728 -- Table structure for table `cities`
731 DROP TABLE IF EXISTS `cities`;
732 CREATE TABLE `cities` (
733 `cityid` int(11) NOT NULL auto_increment,
734 `city_name` varchar(100) NOT NULL default '',
735 `city_zipcode` varchar(20) default NULL,
736 PRIMARY KEY (`cityid`)
737 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
740 -- Table structure for table `class_sort_rules`
743 DROP TABLE IF EXISTS `class_sort_rules`;
744 CREATE TABLE `class_sort_rules` (
745 `class_sort_rule` varchar(10) NOT NULL default '',
746 `description` mediumtext,
747 `sort_routine` varchar(30) NOT NULL default '',
748 PRIMARY KEY (`class_sort_rule`),
749 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
750 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
753 -- Table structure for table `class_sources`
756 DROP TABLE IF EXISTS `class_sources`;
757 CREATE TABLE `class_sources` (
758 `cn_source` varchar(10) NOT NULL default '',
759 `description` mediumtext,
760 `used` tinyint(4) NOT NULL default 0,
761 `class_sort_rule` varchar(10) NOT NULL default '',
762 PRIMARY KEY (`cn_source`),
763 UNIQUE KEY `cn_source_idx` (`cn_source`),
764 KEY `used_idx` (`used`),
765 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
766 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
769 -- Table structure for table `currency`
772 DROP TABLE IF EXISTS `currency`;
773 CREATE TABLE `currency` (
774 `currency` varchar(10) NOT NULL default '',
775 `symbol` varchar(5) default NULL,
776 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
777 `rate` float(7,5) default NULL,
778 PRIMARY KEY (`currency`)
779 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
782 -- Table structure for table `deletedbiblio`
785 DROP TABLE IF EXISTS `deletedbiblio`;
786 CREATE TABLE `deletedbiblio` (
787 `biblionumber` int(11) NOT NULL default 0,
788 `frameworkcode` varchar(4) NOT NULL default '',
791 `unititle` mediumtext,
793 `serial` tinyint(1) default NULL,
794 `seriestitle` mediumtext,
795 `copyrightdate` smallint(6) default NULL,
796 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
797 `datecreated` DATE NOT NULL,
798 `abstract` mediumtext,
799 PRIMARY KEY (`biblionumber`),
800 KEY `blbnoidx` (`biblionumber`)
801 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
804 -- Table structure for table `deletedbiblioitems`
807 DROP TABLE IF EXISTS `deletedbiblioitems`;
808 CREATE TABLE `deletedbiblioitems` (
809 `biblioitemnumber` int(11) NOT NULL default 0,
810 `biblionumber` int(11) NOT NULL default 0,
813 `itemtype` varchar(10) default NULL,
814 `isbn` varchar(30) default NULL,
815 `issn` varchar(9) default NULL,
816 `publicationyear` text,
817 `publishercode` varchar(255) default NULL,
818 `volumedate` date default NULL,
820 `collectiontitle` mediumtext default NULL,
821 `collectionissn` text default NULL,
822 `collectionvolume` mediumtext default NULL,
823 `editionstatement` text default NULL,
824 `editionresponsibility` text default NULL,
825 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
826 `illus` varchar(255) default NULL,
827 `pages` varchar(255) default NULL,
829 `size` varchar(255) default NULL,
830 `place` varchar(255) default NULL,
831 `lccn` varchar(25) default NULL,
833 `url` varchar(255) default NULL,
834 `cn_source` varchar(10) default NULL,
835 `cn_class` varchar(30) default NULL,
836 `cn_item` varchar(10) default NULL,
837 `cn_suffix` varchar(10) default NULL,
838 `cn_sort` varchar(30) default NULL,
839 `totalissues` int(10),
840 `marcxml` longtext NOT NULL,
841 PRIMARY KEY (`biblioitemnumber`),
842 KEY `bibinoidx` (`biblioitemnumber`),
843 KEY `bibnoidx` (`biblionumber`),
845 KEY `publishercode` (`publishercode`)
846 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
849 -- Table structure for table `deletedborrowers`
852 DROP TABLE IF EXISTS `deletedborrowers`;
853 CREATE TABLE `deletedborrowers` (
854 `borrowernumber` int(11) NOT NULL default 0,
855 `cardnumber` varchar(9) NOT NULL default '',
856 `surname` mediumtext NOT NULL,
859 `othernames` mediumtext,
861 `streetnumber` varchar(10) default NULL,
862 `streettype` varchar(50) default NULL,
863 `address` mediumtext NOT NULL,
865 `city` mediumtext NOT NULL,
866 `zipcode` varchar(25) default NULL,
870 `mobile` varchar(50) default NULL,
874 `B_streetnumber` varchar(10) default NULL,
875 `B_streettype` varchar(50) default NULL,
876 `B_address` varchar(100) default NULL,
877 `B_address2` text default NULL,
879 `B_zipcode` varchar(25) default NULL,
882 `B_phone` mediumtext,
883 `dateofbirth` date default NULL,
884 `branchcode` varchar(10) NOT NULL default '',
885 `categorycode` varchar(10) default NULL,
886 `dateenrolled` date default NULL,
887 `dateexpiry` date default NULL,
888 `gonenoaddress` tinyint(1) default NULL,
889 `lost` tinyint(1) default NULL,
890 `debarred` tinyint(1) default NULL,
891 `contactname` mediumtext,
892 `contactfirstname` text,
894 `guarantorid` int(11) default NULL,
895 `borrowernotes` mediumtext,
896 `relationship` varchar(100) default NULL,
897 `ethnicity` varchar(50) default NULL,
898 `ethnotes` varchar(255) default NULL,
899 `sex` varchar(1) default NULL,
900 `password` varchar(30) default NULL,
901 `flags` int(11) default NULL,
902 `userid` varchar(30) default NULL,
903 `opacnote` mediumtext,
904 `contactnote` varchar(255) default NULL,
905 `sort1` varchar(80) default NULL,
906 `sort2` varchar(80) default NULL,
907 `altcontactfirstname` varchar(255) default NULL,
908 `altcontactsurname` varchar(255) default NULL,
909 `altcontactaddress1` varchar(255) default NULL,
910 `altcontactaddress2` varchar(255) default NULL,
911 `altcontactaddress3` varchar(255) default NULL,
912 `altcontactzipcode` varchar(50) default NULL,
913 `altcontactcountry` text default NULL,
914 `altcontactphone` varchar(50) default NULL,
915 `smsalertnumber` varchar(50) default NULL,
916 KEY `borrowernumber` (`borrowernumber`),
917 KEY `cardnumber` (`cardnumber`)
918 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
921 -- Table structure for table `deleteditems`
924 DROP TABLE IF EXISTS `deleteditems`;
925 CREATE TABLE `deleteditems` (
926 `itemnumber` int(11) NOT NULL default 0,
927 `biblionumber` int(11) NOT NULL default 0,
928 `biblioitemnumber` int(11) NOT NULL default 0,
929 `barcode` varchar(20) default NULL,
930 `dateaccessioned` date default NULL,
931 `booksellerid` mediumtext default NULL,
932 `homebranch` varchar(10) default NULL,
933 `price` decimal(8,2) default NULL,
934 `replacementprice` decimal(8,2) default NULL,
935 `replacementpricedate` date default NULL,
936 `datelastborrowed` date default NULL,
937 `datelastseen` date default NULL,
938 `stack` tinyint(1) default NULL,
939 `notforloan` tinyint(1) NOT NULL default 0,
940 `damaged` tinyint(1) NOT NULL default 0,
941 `itemlost` tinyint(1) NOT NULL default 0,
942 `wthdrawn` tinyint(1) NOT NULL default 0,
943 `itemcallnumber` varchar(255) default NULL,
944 `issues` smallint(6) default NULL,
945 `renewals` smallint(6) default NULL,
946 `reserves` smallint(6) default NULL,
947 `restricted` tinyint(1) default NULL,
948 `itemnotes` mediumtext,
949 `holdingbranch` varchar(10) default NULL,
950 `paidfor` mediumtext,
951 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
952 `location` varchar(80) default NULL,
953 `permanent_location` varchar(80) default NULL,
954 `onloan` date default NULL,
955 `cn_source` varchar(10) default NULL,
956 `cn_sort` varchar(30) default NULL,
957 `ccode` varchar(10) default NULL,
958 `materials` varchar(10) default NULL,
959 `uri` varchar(255) default NULL,
960 `itype` varchar(10) default NULL,
961 `more_subfields_xml` longtext default NULL,
962 `enumchron` varchar(80) default NULL,
963 `copynumber` varchar(32) default NULL,
965 PRIMARY KEY (`itemnumber`),
966 KEY `delitembarcodeidx` (`barcode`),
967 KEY `delitembinoidx` (`biblioitemnumber`),
968 KEY `delitembibnoidx` (`biblionumber`),
969 KEY `delhomebranch` (`homebranch`),
970 KEY `delholdingbranch` (`holdingbranch`)
971 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
974 -- Table structure for table `ethnicity`
977 DROP TABLE IF EXISTS `ethnicity`;
978 CREATE TABLE `ethnicity` (
979 `code` varchar(10) NOT NULL default '',
980 `name` varchar(255) default NULL,
982 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
985 -- Table structure for table `hold_fill_targets`
988 DROP TABLE IF EXISTS `hold_fill_targets`;
989 CREATE TABLE hold_fill_targets (
990 `borrowernumber` int(11) NOT NULL,
991 `biblionumber` int(11) NOT NULL,
992 `itemnumber` int(11) NOT NULL,
993 `source_branchcode` varchar(10) default NULL,
994 `item_level_request` tinyint(4) NOT NULL default 0,
995 PRIMARY KEY `itemnumber` (`itemnumber`),
996 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
997 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
998 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
999 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
1000 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1001 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
1002 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1003 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
1004 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1005 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1008 -- Table structure for table `import_batches`
1011 DROP TABLE IF EXISTS `import_batches`;
1012 CREATE TABLE `import_batches` (
1013 `import_batch_id` int(11) NOT NULL auto_increment,
1014 `matcher_id` int(11) default NULL,
1015 `template_id` int(11) default NULL,
1016 `branchcode` varchar(10) default NULL,
1017 `num_biblios` int(11) NOT NULL default 0,
1018 `num_items` int(11) NOT NULL default 0,
1019 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1020 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1021 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1022 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1023 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1024 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1025 `file_name` varchar(100),
1026 `comments` mediumtext,
1027 PRIMARY KEY (`import_batch_id`),
1028 KEY `branchcode` (`branchcode`)
1029 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1032 -- Table structure for table `import_records`
1035 DROP TABLE IF EXISTS `import_records`;
1036 CREATE TABLE `import_records` (
1037 `import_record_id` int(11) NOT NULL auto_increment,
1038 `import_batch_id` int(11) NOT NULL,
1039 `branchcode` varchar(10) default NULL,
1040 `record_sequence` int(11) NOT NULL default 0,
1041 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1042 `import_date` DATE default NULL,
1043 `marc` longblob NOT NULL,
1044 `marcxml` longtext NOT NULL,
1045 `marcxml_old` longtext NOT NULL,
1046 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1047 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1048 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1049 `import_error` mediumtext,
1050 `encoding` varchar(40) NOT NULL default '',
1051 `z3950random` varchar(40) default NULL,
1052 PRIMARY KEY (`import_record_id`),
1053 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1054 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1055 KEY `branchcode` (`branchcode`),
1056 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1057 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1060 -- Table structure for `import_record_matches`
1062 DROP TABLE IF EXISTS `import_record_matches`;
1063 CREATE TABLE `import_record_matches` (
1064 `import_record_id` int(11) NOT NULL,
1065 `candidate_match_id` int(11) NOT NULL,
1066 `score` int(11) NOT NULL default 0,
1067 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1068 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1069 KEY `record_score` (`import_record_id`, `score`)
1070 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1073 -- Table structure for table `import_biblios`
1076 DROP TABLE IF EXISTS `import_biblios`;
1077 CREATE TABLE `import_biblios` (
1078 `import_record_id` int(11) NOT NULL,
1079 `matched_biblionumber` int(11) default NULL,
1080 `control_number` varchar(25) default NULL,
1081 `original_source` varchar(25) default NULL,
1082 `title` varchar(128) default NULL,
1083 `author` varchar(80) default NULL,
1084 `isbn` varchar(30) default NULL,
1085 `issn` varchar(9) default NULL,
1086 `has_items` tinyint(1) NOT NULL default 0,
1087 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1088 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1089 KEY `matched_biblionumber` (`matched_biblionumber`),
1090 KEY `title` (`title`),
1092 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1095 -- Table structure for table `import_items`
1098 DROP TABLE IF EXISTS `import_items`;
1099 CREATE TABLE `import_items` (
1100 `import_items_id` int(11) NOT NULL auto_increment,
1101 `import_record_id` int(11) NOT NULL,
1102 `itemnumber` int(11) default NULL,
1103 `branchcode` varchar(10) default NULL,
1104 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1105 `marcxml` longtext NOT NULL,
1106 `import_error` mediumtext,
1107 PRIMARY KEY (`import_items_id`),
1108 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1109 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1110 KEY `itemnumber` (`itemnumber`),
1111 KEY `branchcode` (`branchcode`)
1112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1115 -- Table structure for table `issues`
1118 DROP TABLE IF EXISTS `issues`;
1119 CREATE TABLE `issues` (
1120 `borrowernumber` int(11) default NULL,
1121 `itemnumber` int(11) default NULL,
1122 `date_due` date default NULL,
1123 `branchcode` varchar(10) default NULL,
1124 `issuingbranch` varchar(18) default NULL,
1125 `returndate` date default NULL,
1126 `lastreneweddate` date default NULL,
1127 `return` varchar(4) default NULL,
1128 `renewals` tinyint(4) default NULL,
1129 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1130 `issuedate` date default NULL,
1131 KEY `issuesborridx` (`borrowernumber`),
1132 KEY `issuesitemidx` (`itemnumber`),
1133 KEY `bordate` (`borrowernumber`,`timestamp`),
1134 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1135 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1136 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1139 -- Table structure for table `issuingrules`
1142 DROP TABLE IF EXISTS `issuingrules`;
1143 CREATE TABLE `issuingrules` (
1144 `categorycode` varchar(10) NOT NULL default '',
1145 `itemtype` varchar(10) NOT NULL default '',
1146 `restrictedtype` tinyint(1) default NULL,
1147 `rentaldiscount` decimal(28,6) default NULL,
1148 `reservecharge` decimal(28,6) default NULL,
1149 `fine` decimal(28,6) default NULL,
1150 `firstremind` int(11) default NULL,
1151 `chargeperiod` int(11) default NULL,
1152 `accountsent` int(11) default NULL,
1153 `chargename` varchar(100) default NULL,
1154 `maxissueqty` int(4) default NULL,
1155 `issuelength` int(4) default NULL,
1156 `branchcode` varchar(10) NOT NULL default '',
1157 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1158 KEY `categorycode` (`categorycode`),
1159 KEY `itemtype` (`itemtype`)
1160 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1163 -- Table structure for table `items`
1166 DROP TABLE IF EXISTS `items`;
1167 CREATE TABLE `items` (
1168 `itemnumber` int(11) NOT NULL auto_increment,
1169 `biblionumber` int(11) NOT NULL default 0,
1170 `biblioitemnumber` int(11) NOT NULL default 0,
1171 `barcode` varchar(20) default NULL,
1172 `dateaccessioned` date default NULL,
1173 `booksellerid` mediumtext default NULL,
1174 `homebranch` varchar(10) default NULL,
1175 `price` decimal(8,2) default NULL,
1176 `replacementprice` decimal(8,2) default NULL,
1177 `replacementpricedate` date default NULL,
1178 `datelastborrowed` date default NULL,
1179 `datelastseen` date default NULL,
1180 `stack` tinyint(1) default NULL,
1181 `notforloan` tinyint(1) NOT NULL default 0,
1182 `damaged` tinyint(1) NOT NULL default 0,
1183 `itemlost` tinyint(1) NOT NULL default 0,
1184 `wthdrawn` tinyint(1) NOT NULL default 0,
1185 `itemcallnumber` varchar(255) default NULL,
1186 `issues` smallint(6) default NULL,
1187 `renewals` smallint(6) default NULL,
1188 `reserves` smallint(6) default NULL,
1189 `restricted` tinyint(1) default NULL,
1190 `itemnotes` mediumtext,
1191 `holdingbranch` varchar(10) default NULL,
1192 `paidfor` mediumtext,
1193 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1194 `location` varchar(80) default NULL,
1195 `permanent_location` varchar(80) default NULL,
1196 `onloan` date default NULL,
1197 `cn_source` varchar(10) default NULL,
1198 `cn_sort` varchar(30) default NULL,
1199 `ccode` varchar(10) default NULL,
1200 `materials` varchar(10) default NULL,
1201 `uri` varchar(255) default NULL,
1202 `itype` varchar(10) default NULL,
1203 `more_subfields_xml` longtext default NULL,
1204 `enumchron` varchar(80) default NULL,
1205 `copynumber` varchar(32) default NULL,
1206 PRIMARY KEY (`itemnumber`),
1207 UNIQUE KEY `itembarcodeidx` (`barcode`),
1208 KEY `itembinoidx` (`biblioitemnumber`),
1209 KEY `itembibnoidx` (`biblionumber`),
1210 KEY `homebranch` (`homebranch`),
1211 KEY `holdingbranch` (`holdingbranch`),
1212 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1213 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1214 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1215 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1218 -- Table structure for table `itemtypes`
1221 DROP TABLE IF EXISTS `itemtypes`;
1222 CREATE TABLE `itemtypes` (
1223 `itemtype` varchar(10) NOT NULL default '',
1224 `description` mediumtext,
1225 `renewalsallowed` smallint(6) default NULL,
1226 `rentalcharge` double(16,4) default NULL,
1227 `notforloan` smallint(6) default NULL,
1228 `imageurl` varchar(200) default NULL,
1230 PRIMARY KEY (`itemtype`),
1231 UNIQUE KEY `itemtype` (`itemtype`)
1232 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1235 -- Table structure for table `labels_batches`
1238 DROP TABLE IF EXISTS `labels_batches`;
1239 CREATE TABLE `labels_batches` (
1240 `label_id` int(11) NOT NULL auto_increment,
1241 `batch_id` int(10) NOT NULL default '1',
1242 `item_number` int(11) NOT NULL default '0',
1243 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1244 `branch_code` varchar(10) NOT NULL default 'NB',
1245 PRIMARY KEY USING BTREE (`label_id`),
1246 KEY `branch_fk` (`branch_code`),
1247 KEY `item_fk` (`item_number`),
1248 CONSTRAINT `item_fk_constraint` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE,
1249 CONSTRAINT `branch_fk_constraint` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE
1250 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1253 -- Table structure for table `labels_layouts`
1256 DROP TABLE IF EXISTS `labels_layouts`;
1257 CREATE TABLE `labels_layouts` (
1258 `layout_id` int(4) NOT NULL auto_increment,
1259 `barcode_type` char(100) NOT NULL default 'CODE39',
1260 `printing_type` char(32) NOT NULL default 'BAR',
1261 `layout_name` char(20) NOT NULL default 'DEFAULT',
1262 `guidebox` int(1) default '0',
1263 `font` char(10) character set utf8 collate utf8_unicode_ci NOT NULL default 'TR',
1264 `font_size` int(4) NOT NULL default '10',
1265 `callnum_split` int(1) default '0',
1266 `text_justify` char(1) character set utf8 collate utf8_unicode_ci NOT NULL default 'L',
1267 `format_string` varchar(210) NOT NULL default 'barcode',
1268 PRIMARY KEY USING BTREE (`layout_id`)
1269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1272 -- Table structure for table `labels_templates`
1275 DROP TABLE IF EXISTS `labels_templates`;
1276 CREATE TABLE `labels_templates` (
1277 `template_id` int(4) NOT NULL auto_increment,
1278 `profile_id` int(4) default NULL,
1279 `template_code` char(100) NOT NULL default 'DEFAULT TEMPLATE',
1280 `template_desc` char(100) NOT NULL default 'Default description',
1281 `page_width` float NOT NULL default '0',
1282 `page_height` float NOT NULL default '0',
1283 `label_width` float NOT NULL default '0',
1284 `label_height` float NOT NULL default '0',
1285 `top_text_margin` float NOT NULL default '0',
1286 `left_text_margin` float NOT NULL default '0',
1287 `top_margin` float NOT NULL default '0',
1288 `left_margin` float NOT NULL default '0',
1289 `cols` int(2) NOT NULL default '0',
1290 `rows` int(2) NOT NULL default '0',
1291 `col_gap` float NOT NULL default '0',
1292 `row_gap` float NOT NULL default '0',
1293 `units` char(20) NOT NULL default 'POINT',
1294 PRIMARY KEY (`template_id`),
1295 KEY `template_profile_fk_constraint` (`profile_id`)
1296 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1299 -- Table structure for table `letter`
1302 DROP TABLE IF EXISTS `letter`;
1303 CREATE TABLE `letter` (
1304 `module` varchar(20) NOT NULL default '',
1305 `code` varchar(20) NOT NULL default '',
1306 `name` varchar(100) NOT NULL default '',
1307 `title` varchar(200) NOT NULL default '',
1309 PRIMARY KEY (`module`,`code`)
1310 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1313 -- Table structure for table `marc_subfield_structure`
1316 DROP TABLE IF EXISTS `marc_subfield_structure`;
1317 CREATE TABLE `marc_subfield_structure` (
1318 `tagfield` varchar(3) NOT NULL default '',
1319 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1320 `liblibrarian` varchar(255) NOT NULL default '',
1321 `libopac` varchar(255) NOT NULL default '',
1322 `repeatable` tinyint(4) NOT NULL default 0,
1323 `mandatory` tinyint(4) NOT NULL default 0,
1324 `kohafield` varchar(40) default NULL,
1325 `tab` tinyint(1) default NULL,
1326 `authorised_value` varchar(20) default NULL,
1327 `authtypecode` varchar(20) default NULL,
1328 `value_builder` varchar(80) default NULL,
1329 `isurl` tinyint(1) default NULL,
1330 `hidden` tinyint(1) default NULL,
1331 `frameworkcode` varchar(4) NOT NULL default '',
1332 `seealso` varchar(1100) default NULL,
1333 `link` varchar(80) default NULL,
1334 `defaultvalue` text default NULL,
1335 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1336 KEY `kohafield_2` (`kohafield`),
1337 KEY `tab` (`frameworkcode`,`tab`),
1338 KEY `kohafield` (`frameworkcode`,`kohafield`)
1339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1342 -- Table structure for table `marc_tag_structure`
1345 DROP TABLE IF EXISTS `marc_tag_structure`;
1346 CREATE TABLE `marc_tag_structure` (
1347 `tagfield` varchar(3) NOT NULL default '',
1348 `liblibrarian` varchar(255) NOT NULL default '',
1349 `libopac` varchar(255) NOT NULL default '',
1350 `repeatable` tinyint(4) NOT NULL default 0,
1351 `mandatory` tinyint(4) NOT NULL default 0,
1352 `authorised_value` varchar(10) default NULL,
1353 `frameworkcode` varchar(4) NOT NULL default '',
1354 PRIMARY KEY (`frameworkcode`,`tagfield`)
1355 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1358 -- Table structure for table `marc_matchers`
1361 DROP TABLE IF EXISTS `marc_matchers`;
1362 CREATE TABLE `marc_matchers` (
1363 `matcher_id` int(11) NOT NULL auto_increment,
1364 `code` varchar(10) NOT NULL default '',
1365 `description` varchar(255) NOT NULL default '',
1366 `record_type` varchar(10) NOT NULL default 'biblio',
1367 `threshold` int(11) NOT NULL default 0,
1368 PRIMARY KEY (`matcher_id`),
1369 KEY `code` (`code`),
1370 KEY `record_type` (`record_type`)
1371 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1374 -- Table structure for table `matchpoints`
1376 DROP TABLE IF EXISTS `matchpoints`;
1377 CREATE TABLE `matchpoints` (
1378 `matcher_id` int(11) NOT NULL,
1379 `matchpoint_id` int(11) NOT NULL auto_increment,
1380 `search_index` varchar(30) NOT NULL default '',
1381 `score` int(11) NOT NULL default 0,
1382 PRIMARY KEY (`matchpoint_id`),
1383 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1384 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1389 -- Table structure for table `matchpoint_components`
1391 DROP TABLE IF EXISTS `matchpoint_components`;
1392 CREATE TABLE `matchpoint_components` (
1393 `matchpoint_id` int(11) NOT NULL,
1394 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1395 sequence int(11) NOT NULL default 0,
1396 tag varchar(3) NOT NULL default '',
1397 subfields varchar(40) NOT NULL default '',
1398 offset int(4) NOT NULL default 0,
1399 length int(4) NOT NULL default 0,
1400 PRIMARY KEY (`matchpoint_component_id`),
1401 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1402 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1403 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1404 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1407 -- Table structure for table `matcher_component_norms`
1409 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1410 CREATE TABLE `matchpoint_component_norms` (
1411 `matchpoint_component_id` int(11) NOT NULL,
1412 `sequence` int(11) NOT NULL default 0,
1413 `norm_routine` varchar(50) NOT NULL default '',
1414 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1415 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1416 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1417 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1420 -- Table structure for table `matcher_matchpoints`
1422 DROP TABLE IF EXISTS `matcher_matchpoints`;
1423 CREATE TABLE `matcher_matchpoints` (
1424 `matcher_id` int(11) NOT NULL,
1425 `matchpoint_id` int(11) NOT NULL,
1426 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1427 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1428 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1429 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1430 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1433 -- Table structure for table `matchchecks`
1435 DROP TABLE IF EXISTS `matchchecks`;
1436 CREATE TABLE `matchchecks` (
1437 `matcher_id` int(11) NOT NULL,
1438 `matchcheck_id` int(11) NOT NULL auto_increment,
1439 `source_matchpoint_id` int(11) NOT NULL,
1440 `target_matchpoint_id` int(11) NOT NULL,
1441 PRIMARY KEY (`matchcheck_id`),
1442 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1443 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1444 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1445 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1446 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1447 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1448 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1451 -- Table structure for table `notifys`
1454 DROP TABLE IF EXISTS `notifys`;
1455 CREATE TABLE `notifys` (
1456 `notify_id` int(11) NOT NULL default 0,
1457 `borrowernumber` int(11) NOT NULL default 0,
1458 `itemnumber` int(11) NOT NULL default 0,
1459 `notify_date` date default NULL,
1460 `notify_send_date` date default NULL,
1461 `notify_level` int(1) NOT NULL default 0,
1462 `method` varchar(20) NOT NULL default ''
1463 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1466 -- Table structure for table `nozebra`
1469 DROP TABLE IF EXISTS `nozebra`;
1470 CREATE TABLE `nozebra` (
1471 `server` varchar(20) NOT NULL,
1472 `indexname` varchar(40) NOT NULL,
1473 `value` varchar(250) NOT NULL,
1474 `biblionumbers` longtext NOT NULL,
1475 KEY `indexname` (`server`,`indexname`),
1476 KEY `value` (`server`,`value`))
1477 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1480 -- Table structure for table `old_issues`
1483 DROP TABLE IF EXISTS `old_issues`;
1484 CREATE TABLE `old_issues` (
1485 `borrowernumber` int(11) default NULL,
1486 `itemnumber` int(11) default NULL,
1487 `date_due` date default NULL,
1488 `branchcode` varchar(10) default NULL,
1489 `issuingbranch` varchar(18) default NULL,
1490 `returndate` date default NULL,
1491 `lastreneweddate` date default NULL,
1492 `return` varchar(4) default NULL,
1493 `renewals` tinyint(4) default NULL,
1494 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1495 `issuedate` date default NULL,
1496 KEY `old_issuesborridx` (`borrowernumber`),
1497 KEY `old_issuesitemidx` (`itemnumber`),
1498 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1499 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1500 ON DELETE SET NULL ON UPDATE SET NULL,
1501 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1502 ON DELETE SET NULL ON UPDATE SET NULL
1503 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1506 -- Table structure for table `old_reserves`
1508 DROP TABLE IF EXISTS `old_reserves`;
1509 CREATE TABLE `old_reserves` (
1510 `borrowernumber` int(11) default NULL,
1511 `reservedate` date default NULL,
1512 `biblionumber` int(11) default NULL,
1513 `constrainttype` varchar(1) default NULL,
1514 `branchcode` varchar(10) default NULL,
1515 `notificationdate` date default NULL,
1516 `reminderdate` date default NULL,
1517 `cancellationdate` date default NULL,
1518 `reservenotes` mediumtext,
1519 `priority` smallint(6) default NULL,
1520 `found` varchar(1) default NULL,
1521 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1522 `itemnumber` int(11) default NULL,
1523 `waitingdate` date default NULL,
1524 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1525 KEY `old_reserves_biblionumber` (`biblionumber`),
1526 KEY `old_reserves_itemnumber` (`itemnumber`),
1527 KEY `old_reserves_branchcode` (`branchcode`),
1528 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1529 ON DELETE SET NULL ON UPDATE SET NULL,
1530 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1531 ON DELETE SET NULL ON UPDATE SET NULL,
1532 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1533 ON DELETE SET NULL ON UPDATE SET NULL
1534 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1537 -- Table structure for table `opac_news`
1540 DROP TABLE IF EXISTS `opac_news`;
1541 CREATE TABLE `opac_news` (
1542 `idnew` int(10) unsigned NOT NULL auto_increment,
1543 `title` varchar(250) NOT NULL default '',
1544 `new` text NOT NULL,
1545 `lang` varchar(25) NOT NULL default '',
1546 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1547 `expirationdate` date default NULL,
1548 `number` int(11) default NULL,
1549 PRIMARY KEY (`idnew`)
1550 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1553 -- Table structure for table `overduerules`
1556 DROP TABLE IF EXISTS `overduerules`;
1557 CREATE TABLE `overduerules` (
1558 `branchcode` varchar(10) NOT NULL default '',
1559 `categorycode` varchar(10) NOT NULL default '',
1560 `delay1` int(4) default 0,
1561 `letter1` varchar(20) default NULL,
1562 `debarred1` varchar(1) default 0,
1563 `delay2` int(4) default 0,
1564 `debarred2` varchar(1) default 0,
1565 `letter2` varchar(20) default NULL,
1566 `delay3` int(4) default 0,
1567 `letter3` varchar(20) default NULL,
1568 `debarred3` int(1) default 0,
1569 PRIMARY KEY (`branchcode`,`categorycode`)
1570 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1573 -- Table structure for table `patroncards`
1576 DROP TABLE IF EXISTS `patroncards`;
1577 CREATE TABLE `patroncards` (
1578 `cardid` int(11) NOT NULL auto_increment,
1579 `batch_id` varchar(10) NOT NULL default '1',
1580 `borrowernumber` int(11) NOT NULL,
1581 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1582 PRIMARY KEY (`cardid`),
1583 KEY `patroncards_ibfk_1` (`borrowernumber`),
1584 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1585 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1588 -- Table structure for table `patronimage`
1591 DROP TABLE IF EXISTS `patronimage`;
1592 CREATE TABLE `patronimage` (
1593 `cardnumber` varchar(16) NOT NULL,
1594 `mimetype` varchar(15) NOT NULL,
1595 `imagefile` mediumblob NOT NULL,
1596 PRIMARY KEY (`cardnumber`),
1597 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1598 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1601 -- Table structure for table `printers`
1604 DROP TABLE IF EXISTS `printers`;
1605 CREATE TABLE `printers` (
1606 `printername` varchar(40) NOT NULL default '',
1607 `printqueue` varchar(20) default NULL,
1608 `printtype` varchar(20) default NULL,
1609 PRIMARY KEY (`printername`)
1610 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1613 -- Table structure for table `printers_profile`
1616 DROP TABLE IF EXISTS `printers_profile`;
1617 CREATE TABLE `printers_profile` (
1618 `profile_id` int(4) NOT NULL auto_increment,
1619 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1620 `template_id` int(4) NOT NULL default '0',
1621 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1622 `offset_horz` float NOT NULL default '0',
1623 `offset_vert` float NOT NULL default '0',
1624 `creep_horz` float NOT NULL default '0',
1625 `creep_vert` float NOT NULL default '0',
1626 `units` char(20) NOT NULL default 'POINT',
1627 PRIMARY KEY (`profile_id`),
1628 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`)
1629 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1632 -- Table structure for table `repeatable_holidays`
1635 DROP TABLE IF EXISTS `repeatable_holidays`;
1636 CREATE TABLE `repeatable_holidays` (
1637 `id` int(11) NOT NULL auto_increment,
1638 `branchcode` varchar(10) NOT NULL default '',
1639 `weekday` smallint(6) default NULL,
1640 `day` smallint(6) default NULL,
1641 `month` smallint(6) default NULL,
1642 `title` varchar(50) NOT NULL default '',
1643 `description` text NOT NULL,
1645 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1648 -- Table structure for table `reports_dictionary`
1651 DROP TABLE IF EXISTS `reports_dictionary`;
1652 CREATE TABLE reports_dictionary (
1653 `id` int(11) NOT NULL auto_increment,
1654 `name` varchar(255) default NULL,
1656 `date_created` datetime default NULL,
1657 `date_modified` datetime default NULL,
1659 `area` int(11) default NULL,
1661 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1664 -- Table structure for table `reserveconstraints`
1667 DROP TABLE IF EXISTS `reserveconstraints`;
1668 CREATE TABLE `reserveconstraints` (
1669 `borrowernumber` int(11) NOT NULL default 0,
1670 `reservedate` date default NULL,
1671 `biblionumber` int(11) NOT NULL default 0,
1672 `biblioitemnumber` int(11) default NULL,
1673 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1674 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1677 -- Table structure for table `reserves`
1680 DROP TABLE IF EXISTS `reserves`;
1681 CREATE TABLE `reserves` (
1682 `borrowernumber` int(11) NOT NULL default 0,
1683 `reservedate` date default NULL,
1684 `biblionumber` int(11) NOT NULL default 0,
1685 `constrainttype` varchar(1) default NULL,
1686 `branchcode` varchar(10) default NULL,
1687 `notificationdate` date default NULL,
1688 `reminderdate` date default NULL,
1689 `cancellationdate` date default NULL,
1690 `reservenotes` mediumtext,
1691 `priority` smallint(6) default NULL,
1692 `found` varchar(1) default NULL,
1693 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1694 `itemnumber` int(11) default NULL,
1695 `waitingdate` date default NULL,
1696 KEY `borrowernumber` (`borrowernumber`),
1697 KEY `biblionumber` (`biblionumber`),
1698 KEY `itemnumber` (`itemnumber`),
1699 KEY `branchcode` (`branchcode`),
1700 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1701 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1702 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1703 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1704 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1707 -- Table structure for table `reviews`
1710 DROP TABLE IF EXISTS `reviews`;
1711 CREATE TABLE `reviews` (
1712 `reviewid` int(11) NOT NULL auto_increment,
1713 `borrowernumber` int(11) default NULL,
1714 `biblionumber` int(11) default NULL,
1716 `approved` tinyint(4) default NULL,
1717 `datereviewed` datetime default NULL,
1718 PRIMARY KEY (`reviewid`)
1719 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1722 -- Table structure for table `roadtype`
1725 DROP TABLE IF EXISTS `roadtype`;
1726 CREATE TABLE `roadtype` (
1727 `roadtypeid` int(11) NOT NULL auto_increment,
1728 `road_type` varchar(100) NOT NULL default '',
1729 PRIMARY KEY (`roadtypeid`)
1730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1733 -- Table structure for table `saved_sql`
1736 DROP TABLE IF EXISTS `saved_sql`;
1737 CREATE TABLE saved_sql (
1738 `id` int(11) NOT NULL auto_increment,
1739 `borrowernumber` int(11) default NULL,
1740 `date_created` datetime default NULL,
1741 `last_modified` datetime default NULL,
1743 `last_run` datetime default NULL,
1744 `report_name` varchar(255) default NULL,
1745 `type` varchar(255) default NULL,
1748 KEY boridx (`borrowernumber`)
1749 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1753 -- Table structure for `saved_reports`
1756 DROP TABLE IF EXISTS `saved_reports`;
1757 CREATE TABLE saved_reports (
1758 `id` int(11) NOT NULL auto_increment,
1759 `report_id` int(11) default NULL,
1761 `date_run` datetime default NULL,
1763 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1767 -- Table structure for table `serial`
1770 DROP TABLE IF EXISTS `serial`;
1771 CREATE TABLE `serial` (
1772 `serialid` int(11) NOT NULL auto_increment,
1773 `biblionumber` varchar(100) NOT NULL default '',
1774 `subscriptionid` varchar(100) NOT NULL default '',
1775 `serialseq` varchar(100) NOT NULL default '',
1776 `status` tinyint(4) NOT NULL default 0,
1777 `planneddate` date default NULL,
1779 `publisheddate` date default NULL,
1780 `itemnumber` text default NULL,
1781 `claimdate` date default NULL,
1782 `routingnotes` text,
1783 PRIMARY KEY (`serialid`)
1784 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1787 -- Table structure for table `sessions`
1790 DROP TABLE IF EXISTS sessions;
1791 CREATE TABLE sessions (
1792 `id` varchar(32) NOT NULL,
1793 `a_session` text NOT NULL,
1795 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1798 -- Table structure for table `special_holidays`
1801 DROP TABLE IF EXISTS `special_holidays`;
1802 CREATE TABLE `special_holidays` (
1803 `id` int(11) NOT NULL auto_increment,
1804 `branchcode` varchar(10) NOT NULL default '',
1805 `day` smallint(6) NOT NULL default 0,
1806 `month` smallint(6) NOT NULL default 0,
1807 `year` smallint(6) NOT NULL default 0,
1808 `isexception` smallint(1) NOT NULL default 1,
1809 `title` varchar(50) NOT NULL default '',
1810 `description` text NOT NULL,
1812 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1815 -- Table structure for table `statistics`
1818 DROP TABLE IF EXISTS `statistics`;
1819 CREATE TABLE `statistics` (
1820 `datetime` datetime default NULL,
1821 `branch` varchar(10) default NULL,
1822 `proccode` varchar(4) default NULL,
1823 `value` double(16,4) default NULL,
1824 `type` varchar(16) default NULL,
1826 `usercode` varchar(10) default NULL,
1827 `itemnumber` int(11) default NULL,
1828 `itemtype` varchar(10) default NULL,
1829 `borrowernumber` int(11) default NULL,
1830 `associatedborrower` int(11) default NULL,
1831 KEY `timeidx` (`datetime`)
1832 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1835 -- Table structure for table `stopwords`
1838 DROP TABLE IF EXISTS `stopwords`;
1839 CREATE TABLE `stopwords` (
1840 `word` varchar(255) default NULL
1841 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1844 -- Table structure for table `subscription`
1847 DROP TABLE IF EXISTS `subscription`;
1848 CREATE TABLE `subscription` (
1849 `biblionumber` int(11) NOT NULL default 0,
1850 `subscriptionid` int(11) NOT NULL auto_increment,
1851 `librarian` varchar(100) default '',
1852 `startdate` date default NULL,
1853 `aqbooksellerid` int(11) default 0,
1854 `cost` int(11) default 0,
1855 `aqbudgetid` int(11) default 0,
1856 `weeklength` int(11) default 0,
1857 `monthlength` int(11) default 0,
1858 `numberlength` int(11) default 0,
1859 `periodicity` tinyint(4) default 0,
1860 `dow` varchar(100) default '',
1861 `numberingmethod` varchar(100) default '',
1863 `status` varchar(100) NOT NULL default '',
1864 `add1` int(11) default 0,
1865 `every1` int(11) default 0,
1866 `whenmorethan1` int(11) default 0,
1867 `setto1` int(11) default NULL,
1868 `lastvalue1` int(11) default NULL,
1869 `add2` int(11) default 0,
1870 `every2` int(11) default 0,
1871 `whenmorethan2` int(11) default 0,
1872 `setto2` int(11) default NULL,
1873 `lastvalue2` int(11) default NULL,
1874 `add3` int(11) default 0,
1875 `every3` int(11) default 0,
1876 `innerloop1` int(11) default 0,
1877 `innerloop2` int(11) default 0,
1878 `innerloop3` int(11) default 0,
1879 `whenmorethan3` int(11) default 0,
1880 `setto3` int(11) default NULL,
1881 `lastvalue3` int(11) default NULL,
1882 `issuesatonce` tinyint(3) NOT NULL default 1,
1883 `firstacquidate` date default NULL,
1884 `manualhistory` tinyint(1) NOT NULL default 0,
1885 `irregularity` text,
1886 `letter` varchar(20) default NULL,
1887 `numberpattern` tinyint(3) default 0,
1888 `distributedto` text,
1889 `internalnotes` longtext,
1891 `location` varchar(80) NULL default '',
1892 `branchcode` varchar(10) NOT NULL default '',
1893 `hemisphere` tinyint(3) default 0,
1894 `lastbranch` varchar(10),
1895 `serialsadditems` tinyint(1) NOT NULL default '0',
1896 `staffdisplaycount` VARCHAR(10) NULL,
1897 `opacdisplaycount` VARCHAR(10) NULL,
1898 `graceperiod` int(11) NOT NULL default '0',
1899 PRIMARY KEY (`subscriptionid`)
1900 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1903 -- Table structure for table `subscriptionhistory`
1906 DROP TABLE IF EXISTS `subscriptionhistory`;
1907 CREATE TABLE `subscriptionhistory` (
1908 `biblionumber` int(11) NOT NULL default 0,
1909 `subscriptionid` int(11) NOT NULL default 0,
1910 `histstartdate` date default NULL,
1911 `enddate` date default NULL,
1912 `missinglist` longtext NOT NULL,
1913 `recievedlist` longtext NOT NULL,
1914 `opacnote` varchar(150) NOT NULL default '',
1915 `librariannote` varchar(150) NOT NULL default '',
1916 PRIMARY KEY (`subscriptionid`),
1917 KEY `biblionumber` (`biblionumber`)
1918 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1921 -- Table structure for table `subscriptionroutinglist`
1924 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1925 CREATE TABLE `subscriptionroutinglist` (
1926 `routingid` int(11) NOT NULL auto_increment,
1927 `borrowernumber` int(11) default NULL,
1928 `ranking` int(11) default NULL,
1929 `subscriptionid` int(11) default NULL,
1930 PRIMARY KEY (`routingid`)
1931 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1934 -- Table structure for table `suggestions`
1937 DROP TABLE IF EXISTS `suggestions`;
1938 CREATE TABLE `suggestions` (
1939 `suggestionid` int(8) NOT NULL auto_increment,
1940 `suggestedby` int(11) NOT NULL default 0,
1941 `managedby` int(11) default NULL,
1942 `STATUS` varchar(10) NOT NULL default '',
1944 `author` varchar(80) default NULL,
1945 `title` varchar(80) default NULL,
1946 `copyrightdate` smallint(6) default NULL,
1947 `publishercode` varchar(255) default NULL,
1948 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1949 `volumedesc` varchar(255) default NULL,
1950 `publicationyear` smallint(6) default 0,
1951 `place` varchar(255) default NULL,
1952 `isbn` varchar(30) default NULL,
1953 `mailoverseeing` smallint(1) default 0,
1954 `biblionumber` int(11) default NULL,
1956 PRIMARY KEY (`suggestionid`),
1957 KEY `suggestedby` (`suggestedby`),
1958 KEY `managedby` (`managedby`)
1959 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1962 -- Table structure for table `systempreferences`
1965 DROP TABLE IF EXISTS `systempreferences`;
1966 CREATE TABLE `systempreferences` (
1967 `variable` varchar(50) NOT NULL default '',
1969 `options` mediumtext,
1971 `type` varchar(20) default NULL,
1972 PRIMARY KEY (`variable`)
1973 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1976 -- Table structure for table `tags`
1979 DROP TABLE IF EXISTS `tags`;
1980 CREATE TABLE `tags` (
1981 `entry` varchar(255) NOT NULL default '',
1982 `weight` bigint(20) NOT NULL default 0,
1983 PRIMARY KEY (`entry`)
1984 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1987 -- Table structure for table `tags_all`
1990 DROP TABLE IF EXISTS `tags_all`;
1991 CREATE TABLE `tags_all` (
1992 `tag_id` int(11) NOT NULL auto_increment,
1993 `borrowernumber` int(11) NOT NULL,
1994 `biblionumber` int(11) NOT NULL,
1995 `term` varchar(255) NOT NULL,
1996 `language` int(4) default NULL,
1997 `date_created` datetime NOT NULL,
1998 PRIMARY KEY (`tag_id`),
1999 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2000 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2001 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2002 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2003 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2004 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2005 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2008 -- Table structure for table `tags_approval`
2011 DROP TABLE IF EXISTS `tags_approval`;
2012 CREATE TABLE `tags_approval` (
2013 `term` varchar(255) NOT NULL,
2014 `approved` int(1) NOT NULL default '0',
2015 `date_approved` datetime default NULL,
2016 `approved_by` int(11) default NULL,
2017 `weight_total` int(9) NOT NULL default '1',
2018 PRIMARY KEY (`term`),
2019 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2020 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2021 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2022 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2025 -- Table structure for table `tags_index`
2028 DROP TABLE IF EXISTS `tags_index`;
2029 CREATE TABLE `tags_index` (
2030 `term` varchar(255) NOT NULL,
2031 `biblionumber` int(11) NOT NULL,
2032 `weight` int(9) NOT NULL default '1',
2033 PRIMARY KEY (`term`,`biblionumber`),
2034 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2035 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2036 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2037 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2038 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2039 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2042 -- Table structure for table `userflags`
2045 DROP TABLE IF EXISTS `userflags`;
2046 CREATE TABLE `userflags` (
2047 `bit` int(11) NOT NULL default 0,
2048 `flag` varchar(30) default NULL,
2049 `flagdesc` varchar(255) default NULL,
2050 `defaulton` int(11) default NULL,
2052 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2055 -- Table structure for table `virtualshelves`
2058 DROP TABLE IF EXISTS `virtualshelves`;
2059 CREATE TABLE `virtualshelves` (
2060 `shelfnumber` int(11) NOT NULL auto_increment,
2061 `shelfname` varchar(255) default NULL,
2062 `owner` varchar(80) default NULL,
2063 `category` varchar(1) default NULL,
2064 `sortfield` varchar(16) default NULL,
2065 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2066 PRIMARY KEY (`shelfnumber`)
2067 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2070 -- Table structure for table `virtualshelfcontents`
2073 DROP TABLE IF EXISTS `virtualshelfcontents`;
2074 CREATE TABLE `virtualshelfcontents` (
2075 `shelfnumber` int(11) NOT NULL default 0,
2076 `biblionumber` int(11) NOT NULL default 0,
2077 `flags` int(11) default NULL,
2078 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2079 KEY `shelfnumber` (`shelfnumber`),
2080 KEY `biblionumber` (`biblionumber`),
2081 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2082 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2083 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2086 -- Table structure for table `z3950servers`
2089 DROP TABLE IF EXISTS `z3950servers`;
2090 CREATE TABLE `z3950servers` (
2091 `host` varchar(255) default NULL,
2092 `port` int(11) default NULL,
2093 `db` varchar(255) default NULL,
2094 `userid` varchar(255) default NULL,
2095 `password` varchar(255) default NULL,
2097 `id` int(11) NOT NULL auto_increment,
2098 `checked` smallint(6) default NULL,
2099 `rank` int(11) default NULL,
2100 `syntax` varchar(80) default NULL,
2102 `position` enum('primary','secondary','') NOT NULL default 'primary',
2103 `type` enum('zed','opensearch') NOT NULL default 'zed',
2104 `encoding` text default NULL,
2105 `description` text NOT NULL,
2107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2110 -- Table structure for table `zebraqueue`
2113 DROP TABLE IF EXISTS `zebraqueue`;
2114 CREATE TABLE `zebraqueue` (
2115 `id` int(11) NOT NULL auto_increment,
2116 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2117 `operation` char(20) NOT NULL default '',
2118 `server` char(20) NOT NULL default '',
2119 `done` int(11) NOT NULL default '0',
2120 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2122 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2125 DROP TABLE IF EXISTS `services_throttle`;
2126 CREATE TABLE `services_throttle` (
2127 `service_type` varchar(10) NOT NULL default '',
2128 `service_count` varchar(45) default NULL,
2129 PRIMARY KEY (`service_type`)
2130 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2132 -- http://www.w3.org/International/articles/language-tags/
2135 DROP TABLE IF EXISTS language_subtag_registry;
2136 CREATE TABLE language_subtag_registry (
2138 type varchar(25), -- language-script-region-variant-extension-privateuse
2139 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2141 id int(11) NOT NULL auto_increment,
2143 KEY `subtag` (`subtag`)
2144 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2146 -- TODO: add suppress_scripts
2147 -- this maps three letter codes defined in iso639.2 back to their
2148 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2149 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2150 CREATE TABLE language_rfc4646_to_iso639 (
2151 rfc4646_subtag varchar(25),
2152 iso639_2_code varchar(25),
2153 id int(11) NOT NULL auto_increment,
2155 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2158 DROP TABLE IF EXISTS language_descriptions;
2159 CREATE TABLE language_descriptions (
2163 description varchar(255),
2164 id int(11) NOT NULL auto_increment,
2167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2169 -- bi-directional support, keyed by script subcode
2170 DROP TABLE IF EXISTS language_script_bidi;
2171 CREATE TABLE language_script_bidi (
2172 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2173 bidi varchar(3), -- rtl ltr
2174 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2175 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2177 -- TODO: need to map language subtags to script subtags for detection
2178 -- of bidi when script is not specified (like ar, he)
2179 DROP TABLE IF EXISTS language_script_mapping;
2180 CREATE TABLE language_script_mapping (
2181 language_subtag varchar(25),
2182 script_subtag varchar(25),
2183 KEY `language_subtag` (`language_subtag`)
2184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2186 DROP TABLE IF EXISTS `permissions`;
2187 CREATE TABLE `permissions` (
2188 `module_bit` int(11) NOT NULL DEFAULT 0,
2189 `code` varchar(64) DEFAULT NULL,
2190 `description` varchar(255) DEFAULT NULL,
2191 PRIMARY KEY (`module_bit`, `code`),
2192 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2193 ON DELETE CASCADE ON UPDATE CASCADE
2194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2196 DROP TABLE IF EXISTS `serialitems`;
2197 CREATE TABLE `serialitems` (
2198 `itemnumber` int(11) NOT NULL,
2199 `serialid` int(11) NOT NULL,
2200 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2201 KEY `serialitems_sfk_1` (`serialid`),
2202 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2203 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2205 DROP TABLE IF EXISTS `user_permissions`;
2206 CREATE TABLE `user_permissions` (
2207 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2208 `module_bit` int(11) NOT NULL DEFAULT 0,
2209 `code` varchar(64) DEFAULT NULL,
2210 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2211 ON DELETE CASCADE ON UPDATE CASCADE,
2212 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2213 ON DELETE CASCADE ON UPDATE CASCADE
2214 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2217 -- Table structure for table `tmp_holdsqueue`
2220 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2221 CREATE TABLE `tmp_holdsqueue` (
2222 `biblionumber` int(11) default NULL,
2223 `itemnumber` int(11) default NULL,
2224 `barcode` varchar(20) default NULL,
2225 `surname` mediumtext NOT NULL,
2228 `borrowernumber` int(11) NOT NULL,
2229 `cardnumber` varchar(16) default NULL,
2230 `reservedate` date default NULL,
2232 `itemcallnumber` varchar(255) default NULL,
2233 `holdingbranch` varchar(10) default NULL,
2234 `pickbranch` varchar(10) default NULL,
2236 `item_level_request` tinyint(4) NOT NULL default 0
2237 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2240 -- Table structure for table `message_queue`
2243 DROP TABLE IF EXISTS `message_queue`;
2244 CREATE TABLE `message_queue` (
2245 `message_id` int(11) NOT NULL auto_increment,
2246 `borrowernumber` int(11) default NULL,
2249 `metadata` text DEFAULT NULL,
2250 `letter_code` varchar(64) DEFAULT NULL,
2251 `message_transport_type` varchar(20) NOT NULL,
2252 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2253 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2254 `to_address` mediumtext,
2255 `from_address` mediumtext,
2256 `content_type` text,
2257 KEY `message_id` (`message_id`),
2258 KEY `borrowernumber` (`borrowernumber`),
2259 KEY `message_transport_type` (`message_transport_type`),
2260 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2261 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2262 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2265 -- Table structure for table `message_transport_types`
2268 DROP TABLE IF EXISTS `message_transport_types`;
2269 CREATE TABLE `message_transport_types` (
2270 `message_transport_type` varchar(20) NOT NULL,
2271 PRIMARY KEY (`message_transport_type`)
2272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2275 -- Table structure for table `message_attributes`
2278 DROP TABLE IF EXISTS `message_attributes`;
2279 CREATE TABLE `message_attributes` (
2280 `message_attribute_id` int(11) NOT NULL auto_increment,
2281 `message_name` varchar(20) NOT NULL default '',
2282 `takes_days` tinyint(1) NOT NULL default '0',
2283 PRIMARY KEY (`message_attribute_id`),
2284 UNIQUE KEY `message_name` (`message_name`)
2285 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2288 -- Table structure for table `message_transports`
2291 DROP TABLE IF EXISTS `message_transports`;
2292 CREATE TABLE `message_transports` (
2293 `message_attribute_id` int(11) NOT NULL,
2294 `message_transport_type` varchar(20) NOT NULL,
2295 `is_digest` tinyint(1) NOT NULL default '0',
2296 `letter_module` varchar(20) NOT NULL default '',
2297 `letter_code` varchar(20) NOT NULL default '',
2298 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2299 KEY `message_transport_type` (`message_transport_type`),
2300 KEY `letter_module` (`letter_module`,`letter_code`),
2301 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2302 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2303 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2307 -- Table structure for table `borrower_message_preferences`
2310 DROP TABLE IF EXISTS `borrower_message_preferences`;
2311 CREATE TABLE `borrower_message_preferences` (
2312 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2313 `borrowernumber` int(11) default NULL,
2314 `categorycode` varchar(10) default NULL,
2315 `message_attribute_id` int(11) default '0',
2316 `days_in_advance` int(11) default '0',
2317 `wants_digest` tinyint(1) NOT NULL default '0',
2318 PRIMARY KEY (`borrower_message_preference_id`),
2319 KEY `borrowernumber` (`borrowernumber`),
2320 KEY `categorycode` (`categorycode`),
2321 KEY `message_attribute_id` (`message_attribute_id`),
2322 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2323 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2324 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2328 -- Table structure for table `borrower_message_transport_preferences`
2331 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2332 CREATE TABLE `borrower_message_transport_preferences` (
2333 `borrower_message_preference_id` int(11) NOT NULL default '0',
2334 `message_transport_type` varchar(20) NOT NULL default '0',
2335 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2336 KEY `message_transport_type` (`message_transport_type`),
2337 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,
2338 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
2339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2342 -- Table structure for the table branch_transfer_limits
2345 DROP TABLE IF EXISTS `branch_transfer_limits`;
2346 CREATE TABLE branch_transfer_limits (
2347 limitId int(8) NOT NULL auto_increment,
2348 toBranch varchar(10) NOT NULL,
2349 fromBranch varchar(10) NOT NULL,
2350 itemtype varchar(10) NULL,
2351 ccode varchar(10) NULL,
2352 PRIMARY KEY (limitId)
2353 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2356 -- Table structure for table `item_circulation_alert_preferences`
2359 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2360 CREATE TABLE `item_circulation_alert_preferences` (
2361 `id` int(11) NOT NULL auto_increment,
2362 `branchcode` varchar(10) NOT NULL,
2363 `categorycode` varchar(10) NOT NULL,
2364 `item_type` varchar(10) NOT NULL,
2365 `notification` varchar(16) NOT NULL,
2367 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2368 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2371 -- Table structure for table `messages`
2374 CREATE TABLE `messages` (
2375 `message_id` int(11) NOT NULL auto_increment,
2376 `borrowernumber` int(11) NOT NULL,
2377 `branchcode` varchar(4) default NULL,
2378 `message_type` varchar(1) NOT NULL,
2379 `message` text NOT NULL,
2380 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2381 PRIMARY KEY (`message_id`)
2382 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2384 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2385 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2386 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2387 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2388 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2389 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2390 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2391 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;