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`)
345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
348 -- Table structure for table `biblio`
351 DROP TABLE IF EXISTS `biblio`;
352 CREATE TABLE `biblio` (
353 `biblionumber` int(11) NOT NULL auto_increment,
354 `frameworkcode` varchar(4) NOT NULL default '',
357 `unititle` mediumtext,
359 `serial` tinyint(1) default NULL,
360 `seriestitle` mediumtext,
361 `copyrightdate` smallint(6) default NULL,
362 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
363 `datecreated` DATE NOT NULL,
364 `abstract` mediumtext,
365 PRIMARY KEY (`biblionumber`),
366 KEY `blbnoidx` (`biblionumber`)
367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
370 -- Table structure for table `biblio_framework`
373 DROP TABLE IF EXISTS `biblio_framework`;
374 CREATE TABLE `biblio_framework` (
375 `frameworkcode` varchar(4) NOT NULL default '',
376 `frameworktext` varchar(255) NOT NULL default '',
377 PRIMARY KEY (`frameworkcode`)
378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
381 -- Table structure for table `biblioitems`
384 DROP TABLE IF EXISTS `biblioitems`;
385 CREATE TABLE `biblioitems` (
386 `biblioitemnumber` int(11) NOT NULL auto_increment,
387 `biblionumber` int(11) NOT NULL default 0,
390 `itemtype` varchar(10) default NULL,
391 `isbn` varchar(30) default NULL,
392 `issn` varchar(9) default NULL,
393 `publicationyear` text,
394 `publishercode` varchar(255) default NULL,
395 `volumedate` date default NULL,
397 `collectiontitle` mediumtext default NULL,
398 `collectionissn` text default NULL,
399 `collectionvolume` mediumtext default NULL,
400 `editionstatement` text default NULL,
401 `editionresponsibility` text default NULL,
402 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
403 `illus` varchar(255) default NULL,
404 `pages` varchar(255) default NULL,
406 `size` varchar(255) default NULL,
407 `place` varchar(255) default NULL,
408 `lccn` varchar(25) default NULL,
410 `url` varchar(255) default NULL,
411 `cn_source` varchar(10) default NULL,
412 `cn_class` varchar(30) default NULL,
413 `cn_item` varchar(10) default NULL,
414 `cn_suffix` varchar(10) default NULL,
415 `cn_sort` varchar(30) default NULL,
416 `totalissues` int(10),
417 `marcxml` longtext NOT NULL,
418 PRIMARY KEY (`biblioitemnumber`),
419 KEY `bibinoidx` (`biblioitemnumber`),
420 KEY `bibnoidx` (`biblionumber`),
423 KEY `publishercode` (`publishercode`),
424 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
425 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
428 -- Table structure for table `borrowers`
431 DROP TABLE IF EXISTS `borrowers`;
432 CREATE TABLE `borrowers` (
433 `borrowernumber` int(11) NOT NULL auto_increment,
434 `cardnumber` varchar(16) default NULL,
435 `surname` mediumtext NOT NULL,
438 `othernames` mediumtext,
440 `streetnumber` varchar(10) default NULL,
441 `streettype` varchar(50) default NULL,
442 `address` mediumtext NOT NULL,
444 `city` mediumtext NOT NULL,
445 `zipcode` varchar(25) default NULL,
448 `mobile` varchar(50) default NULL,
452 `B_streetnumber` varchar(10) default NULL,
453 `B_streettype` varchar(50) default NULL,
454 `B_address` varchar(100) default NULL,
456 `B_zipcode` varchar(25) default NULL,
458 `B_phone` mediumtext,
459 `dateofbirth` date default NULL,
460 `branchcode` varchar(10) NOT NULL default '',
461 `categorycode` varchar(10) NOT NULL default '',
462 `dateenrolled` date default NULL,
463 `dateexpiry` date default NULL,
464 `gonenoaddress` tinyint(1) default NULL,
465 `lost` tinyint(1) default NULL,
466 `debarred` tinyint(1) default NULL,
467 `contactname` mediumtext,
468 `contactfirstname` text,
470 `guarantorid` int(11) default NULL,
471 `borrowernotes` mediumtext,
472 `relationship` varchar(100) default NULL,
473 `ethnicity` varchar(50) default NULL,
474 `ethnotes` varchar(255) default NULL,
475 `sex` varchar(1) default NULL,
476 `password` varchar(30) default NULL,
477 `flags` int(11) default NULL,
478 `userid` varchar(30) default NULL,
479 `opacnote` mediumtext,
480 `contactnote` varchar(255) default NULL,
481 `sort1` varchar(80) default NULL,
482 `sort2` varchar(80) default NULL,
483 `altcontactfirstname` varchar(255) default NULL,
484 `altcontactsurname` varchar(255) default NULL,
485 `altcontactaddress1` varchar(255) default NULL,
486 `altcontactaddress2` varchar(255) default NULL,
487 `altcontactaddress3` varchar(255) default NULL,
488 `altcontactzipcode` varchar(50) default NULL,
489 `altcontactphone` varchar(50) default NULL,
490 `smsalertnumber` varchar(50) default NULL,
491 UNIQUE KEY `cardnumber` (`cardnumber`),
492 PRIMARY KEY `borrowernumber` (`borrowernumber`),
493 KEY `categorycode` (`categorycode`),
494 KEY `branchcode` (`branchcode`),
495 KEY `userid` (`userid`),
496 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
497 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
498 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
501 -- Table structure for table `borrower_attribute_types`
504 DROP TABLE IF EXISTS `borrower_attribute_types`;
505 CREATE TABLE `borrower_attribute_types` (
506 `code` varchar(10) NOT NULL,
507 `description` varchar(255) NOT NULL,
508 `repeatable` tinyint(1) NOT NULL default 0,
509 `unique_id` tinyint(1) NOT NULL default 0,
510 `opac_display` tinyint(1) NOT NULL default 0,
511 `password_allowed` tinyint(1) NOT NULL default 0,
512 `staff_searchable` tinyint(1) NOT NULL default 0,
513 `authorised_value_category` varchar(10) default NULL,
515 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
518 -- Table structure for table `borrower_attributes`
521 DROP TABLE IF EXISTS `borrower_attributes`;
522 CREATE TABLE `borrower_attributes` (
523 `borrowernumber` int(11) NOT NULL,
524 `code` varchar(10) NOT NULL,
525 `attribute` varchar(64) default NULL,
526 `password` varchar(64) default NULL,
527 KEY `borrowernumber` (`borrowernumber`),
528 KEY `code_attribute` (`code`, `attribute`),
529 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
530 ON DELETE CASCADE ON UPDATE CASCADE,
531 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
532 ON DELETE CASCADE ON UPDATE CASCADE
533 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
536 -- Table structure for table `branchcategories`
539 DROP TABLE IF EXISTS `branchcategories`;
540 CREATE TABLE `branchcategories` (
541 `categorycode` varchar(10) NOT NULL default '',
542 `categoryname` varchar(32),
543 `codedescription` mediumtext,
544 `categorytype` varchar(16),
545 PRIMARY KEY (`categorycode`)
546 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
549 -- Table structure for table `branches`
552 DROP TABLE IF EXISTS `branches`;
553 CREATE TABLE `branches` (
554 `branchcode` varchar(10) NOT NULL default '',
555 `branchname` mediumtext NOT NULL,
556 `branchaddress1` mediumtext,
557 `branchaddress2` mediumtext,
558 `branchaddress3` mediumtext,
559 `branchphone` mediumtext,
560 `branchfax` mediumtext,
561 `branchemail` mediumtext,
562 `issuing` tinyint(4) default NULL,
563 `branchip` varchar(15) default NULL,
564 `branchprinter` varchar(100) default NULL,
565 UNIQUE KEY `branchcode` (`branchcode`)
566 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
569 -- Table structure for table `branchrelations`
572 DROP TABLE IF EXISTS `branchrelations`;
573 CREATE TABLE `branchrelations` (
574 `branchcode` varchar(10) NOT NULL default '',
575 `categorycode` varchar(10) NOT NULL default '',
576 PRIMARY KEY (`branchcode`,`categorycode`),
577 KEY `branchcode` (`branchcode`),
578 KEY `categorycode` (`categorycode`),
579 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
580 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
581 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
584 -- Table structure for table `branchtransfers`
587 DROP TABLE IF EXISTS `branchtransfers`;
588 CREATE TABLE `branchtransfers` (
589 `itemnumber` int(11) NOT NULL default 0,
590 `datesent` datetime default NULL,
591 `frombranch` varchar(10) NOT NULL default '',
592 `datearrived` datetime default NULL,
593 `tobranch` varchar(10) NOT NULL default '',
594 `comments` mediumtext,
595 KEY `frombranch` (`frombranch`),
596 KEY `tobranch` (`tobranch`),
597 KEY `itemnumber` (`itemnumber`),
598 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
599 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
600 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
601 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
605 -- Table structure for table `browser`
607 DROP TABLE IF EXISTS `browser`;
608 CREATE TABLE `browser` (
609 `level` int(11) NOT NULL,
610 `classification` varchar(20) NOT NULL,
611 `description` varchar(255) NOT NULL,
612 `number` bigint(20) NOT NULL,
613 `endnode` tinyint(4) NOT NULL
614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
617 -- Table structure for table `categories`
620 DROP TABLE IF EXISTS `categories`;
621 CREATE TABLE `categories` (
622 `categorycode` varchar(10) NOT NULL default '',
623 `description` mediumtext,
624 `enrolmentperiod` smallint(6) default NULL,
625 `upperagelimit` smallint(6) default NULL,
626 `dateofbirthrequired` tinyint(1) default NULL,
627 `finetype` varchar(30) default NULL,
628 `bulk` tinyint(1) default NULL,
629 `enrolmentfee` decimal(28,6) default NULL,
630 `overduenoticerequired` tinyint(1) default NULL,
631 `issuelimit` smallint(6) default NULL,
632 `reservefee` decimal(28,6) default NULL,
633 `category_type` varchar(1) NOT NULL default 'A',
634 PRIMARY KEY (`categorycode`),
635 UNIQUE KEY `categorycode` (`categorycode`)
636 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
639 -- Table structure for table `borrower_branch_circ_rules`
642 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
643 CREATE TABLE `branch_borrower_circ_rules` (
644 `branchcode` VARCHAR(10) NOT NULL,
645 `categorycode` VARCHAR(10) NOT NULL,
646 `maxissueqty` int(4) default NULL,
647 PRIMARY KEY (`categorycode`, `branchcode`),
648 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
649 ON DELETE CASCADE ON UPDATE CASCADE,
650 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
651 ON DELETE CASCADE ON UPDATE CASCADE
652 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
655 -- Table structure for table `default_borrower_circ_rules`
658 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
659 CREATE TABLE `default_borrower_circ_rules` (
660 `categorycode` VARCHAR(10) NOT NULL,
661 `maxissueqty` int(4) default NULL,
662 PRIMARY KEY (`categorycode`),
663 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
664 ON DELETE CASCADE ON UPDATE CASCADE
665 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
668 -- Table structure for table `default_branch_circ_rules`
671 DROP TABLE IF EXISTS `default_branch_circ_rules`;
672 CREATE TABLE `default_branch_circ_rules` (
673 `branchcode` VARCHAR(10) NOT NULL,
674 `maxissueqty` int(4) default NULL,
675 `holdallowed` int(1) default NULL,
676 PRIMARY KEY (`branchcode`),
677 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
678 ON DELETE CASCADE ON UPDATE CASCADE
679 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
682 -- Table structure for table `default_branch_item_rules`
685 CREATE TABLE `default_branch_item_rules` (
686 `itemtype` varchar(10) NOT NULL,
687 `holdallowed` tinyint(1) default NULL,
688 PRIMARY KEY (`itemtype`),
689 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
690 ON DELETE CASCADE ON UPDATE CASCADE
691 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
694 -- Table structure for table `branch_item_rules`
697 CREATE TABLE `branch_item_rules` (
698 `branchcode` varchar(10) NOT NULL,
699 `itemtype` varchar(10) NOT NULL,
700 `holdallowed` tinyint(1) default NULL,
701 PRIMARY KEY (`itemtype`,`branchcode`),
702 KEY `branch_item_rules_ibfk_2` (`branchcode`),
703 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE,
704 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) 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 `B_address` varchar(100) default NULL,
869 `B_zipcode` varchar(25) default NULL,
871 `B_phone` mediumtext,
872 `dateofbirth` date default NULL,
873 `branchcode` varchar(10) NOT NULL default '',
874 `categorycode` varchar(10) default NULL,
875 `dateenrolled` date default NULL,
876 `dateexpiry` date default NULL,
877 `gonenoaddress` tinyint(1) default NULL,
878 `lost` tinyint(1) default NULL,
879 `debarred` tinyint(1) default NULL,
880 `contactname` mediumtext,
881 `contactfirstname` text,
883 `guarantorid` int(11) default NULL,
884 `borrowernotes` mediumtext,
885 `relationship` varchar(100) default NULL,
886 `ethnicity` varchar(50) default NULL,
887 `ethnotes` varchar(255) default NULL,
888 `sex` varchar(1) default NULL,
889 `password` varchar(30) default NULL,
890 `flags` int(11) default NULL,
891 `userid` varchar(30) default NULL,
892 `opacnote` mediumtext,
893 `contactnote` varchar(255) default NULL,
894 `sort1` varchar(80) default NULL,
895 `sort2` varchar(80) default NULL,
896 `altcontactfirstname` varchar(255) default NULL,
897 `altcontactsurname` varchar(255) default NULL,
898 `altcontactaddress1` varchar(255) default NULL,
899 `altcontactaddress2` varchar(255) default NULL,
900 `altcontactaddress3` varchar(255) default NULL,
901 `altcontactzipcode` varchar(50) default NULL,
902 `altcontactphone` varchar(50) default NULL,
903 KEY `borrowernumber` (`borrowernumber`),
904 KEY `cardnumber` (`cardnumber`)
905 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
908 -- Table structure for table `deleteditems`
911 DROP TABLE IF EXISTS `deleteditems`;
912 CREATE TABLE `deleteditems` (
913 `itemnumber` int(11) NOT NULL default 0,
914 `biblionumber` int(11) NOT NULL default 0,
915 `biblioitemnumber` int(11) NOT NULL default 0,
916 `barcode` varchar(20) default NULL,
917 `dateaccessioned` date default NULL,
918 `booksellerid` mediumtext default NULL,
919 `homebranch` varchar(10) default NULL,
920 `price` decimal(8,2) default NULL,
921 `replacementprice` decimal(8,2) default NULL,
922 `replacementpricedate` date default NULL,
923 `datelastborrowed` date default NULL,
924 `datelastseen` date default NULL,
925 `stack` tinyint(1) default NULL,
926 `notforloan` tinyint(1) NOT NULL default 0,
927 `damaged` tinyint(1) NOT NULL default 0,
928 `itemlost` tinyint(1) NOT NULL default 0,
929 `wthdrawn` tinyint(1) NOT NULL default 0,
930 `itemcallnumber` varchar(30) default NULL,
931 `issues` smallint(6) default NULL,
932 `renewals` smallint(6) default NULL,
933 `reserves` smallint(6) default NULL,
934 `restricted` tinyint(1) default NULL,
935 `itemnotes` mediumtext,
936 `holdingbranch` varchar(10) default NULL,
937 `paidfor` mediumtext,
938 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
939 `location` varchar(80) default NULL,
940 `onloan` date default NULL,
941 `cn_source` varchar(10) default NULL,
942 `cn_sort` varchar(30) default NULL,
943 `ccode` varchar(10) default NULL,
944 `materials` varchar(10) default NULL,
945 `uri` varchar(255) default NULL,
946 `itype` varchar(10) default NULL,
947 `more_subfields_xml` longtext default NULL,
948 `enumchron` varchar(80) default NULL,
949 `copynumber` varchar(32) default NULL,
951 PRIMARY KEY (`itemnumber`),
952 KEY `delitembarcodeidx` (`barcode`),
953 KEY `delitembinoidx` (`biblioitemnumber`),
954 KEY `delitembibnoidx` (`biblionumber`),
955 KEY `delhomebranch` (`homebranch`),
956 KEY `delholdingbranch` (`holdingbranch`)
957 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
960 -- Table structure for table `ethnicity`
963 DROP TABLE IF EXISTS `ethnicity`;
964 CREATE TABLE `ethnicity` (
965 `code` varchar(10) NOT NULL default '',
966 `name` varchar(255) default NULL,
968 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
971 -- Table structure for table `export_format`
974 DROP TABLE IF EXISTS `export_format`;
975 CREATE TABLE `export_format` (
976 `export_format_id` int(11) NOT NULL auto_increment,
977 `profile` varchar(255) NOT NULL,
978 `description` mediumtext NOT NULL,
979 `marcfields` mediumtext NOT NULL,
980 `csv_separator` varchar(2) NOT NULL,
981 `field_separator` varchar(2) NOT NULL,
982 `subfield_separator` varchar(2) NOT NULL,
983 `encoding` varchar(255) NOT NULL,
984 PRIMARY KEY (`export_format_id`)
985 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
989 -- Table structure for table `hold_fill_targets`
992 DROP TABLE IF EXISTS `hold_fill_targets`;
993 CREATE TABLE hold_fill_targets (
994 `borrowernumber` int(11) NOT NULL,
995 `biblionumber` int(11) NOT NULL,
996 `itemnumber` int(11) NOT NULL,
997 `source_branchcode` varchar(10) default NULL,
998 `item_level_request` tinyint(4) NOT NULL default 0,
999 PRIMARY KEY `itemnumber` (`itemnumber`),
1000 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
1001 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
1002 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1003 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
1004 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1005 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
1006 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1007 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
1008 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1009 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1012 -- Table structure for table `import_batches`
1015 DROP TABLE IF EXISTS `import_batches`;
1016 CREATE TABLE `import_batches` (
1017 `import_batch_id` int(11) NOT NULL auto_increment,
1018 `matcher_id` int(11) default NULL,
1019 `template_id` int(11) default NULL,
1020 `branchcode` varchar(10) default NULL,
1021 `num_biblios` int(11) NOT NULL default 0,
1022 `num_items` int(11) NOT NULL default 0,
1023 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1024 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1025 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1026 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1027 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1028 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1029 `file_name` varchar(100),
1030 `comments` mediumtext,
1031 PRIMARY KEY (`import_batch_id`),
1032 KEY `branchcode` (`branchcode`)
1033 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1036 -- Table structure for table `import_records`
1039 DROP TABLE IF EXISTS `import_records`;
1040 CREATE TABLE `import_records` (
1041 `import_record_id` int(11) NOT NULL auto_increment,
1042 `import_batch_id` int(11) NOT NULL,
1043 `branchcode` varchar(10) default NULL,
1044 `record_sequence` int(11) NOT NULL default 0,
1045 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1046 `import_date` DATE default NULL,
1047 `marc` longblob NOT NULL,
1048 `marcxml` longtext NOT NULL,
1049 `marcxml_old` longtext NOT NULL,
1050 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1051 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1052 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1053 `import_error` mediumtext,
1054 `encoding` varchar(40) NOT NULL default '',
1055 `z3950random` varchar(40) default NULL,
1056 PRIMARY KEY (`import_record_id`),
1057 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1058 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1059 KEY `branchcode` (`branchcode`),
1060 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1061 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1064 -- Table structure for `import_record_matches`
1066 DROP TABLE IF EXISTS `import_record_matches`;
1067 CREATE TABLE `import_record_matches` (
1068 `import_record_id` int(11) NOT NULL,
1069 `candidate_match_id` int(11) NOT NULL,
1070 `score` int(11) NOT NULL default 0,
1071 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1072 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1073 KEY `record_score` (`import_record_id`, `score`)
1074 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1077 -- Table structure for table `import_biblios`
1080 DROP TABLE IF EXISTS `import_biblios`;
1081 CREATE TABLE `import_biblios` (
1082 `import_record_id` int(11) NOT NULL,
1083 `matched_biblionumber` int(11) default NULL,
1084 `control_number` varchar(25) default NULL,
1085 `original_source` varchar(25) default NULL,
1086 `title` varchar(128) default NULL,
1087 `author` varchar(80) default NULL,
1088 `isbn` varchar(30) default NULL,
1089 `issn` varchar(9) default NULL,
1090 `has_items` tinyint(1) NOT NULL default 0,
1091 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1092 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1093 KEY `matched_biblionumber` (`matched_biblionumber`),
1094 KEY `title` (`title`),
1096 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1099 -- Table structure for table `import_items`
1102 DROP TABLE IF EXISTS `import_items`;
1103 CREATE TABLE `import_items` (
1104 `import_items_id` int(11) NOT NULL auto_increment,
1105 `import_record_id` int(11) NOT NULL,
1106 `itemnumber` int(11) default NULL,
1107 `branchcode` varchar(10) default NULL,
1108 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1109 `marcxml` longtext NOT NULL,
1110 `import_error` mediumtext,
1111 PRIMARY KEY (`import_items_id`),
1112 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1113 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1114 KEY `itemnumber` (`itemnumber`),
1115 KEY `branchcode` (`branchcode`)
1116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1119 -- Table structure for table `issues`
1122 DROP TABLE IF EXISTS `issues`;
1123 CREATE TABLE `issues` (
1124 `borrowernumber` int(11) default NULL,
1125 `itemnumber` int(11) UNIQUE default NULL,
1126 `date_due` date default NULL,
1127 `branchcode` varchar(10) default NULL,
1128 `issuingbranch` varchar(18) default NULL,
1129 `returndate` date default NULL,
1130 `lastreneweddate` date default NULL,
1131 `return` varchar(4) default NULL,
1132 `renewals` tinyint(4) default NULL,
1133 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1134 `issuedate` date default NULL,
1135 KEY `issuesborridx` (`borrowernumber`),
1136 KEY `issuesitemidx` (`itemnumber`),
1137 KEY `bordate` (`borrowernumber`,`timestamp`),
1138 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1139 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1143 -- Table structure for table `issuingrules`
1146 DROP TABLE IF EXISTS `issuingrules`;
1147 CREATE TABLE `issuingrules` (
1148 `categorycode` varchar(10) NOT NULL default '',
1149 `itemtype` varchar(10) NOT NULL default '',
1150 `restrictedtype` tinyint(1) default NULL,
1151 `rentaldiscount` decimal(28,6) default NULL,
1152 `reservecharge` decimal(28,6) default NULL,
1153 `fine` decimal(28,6) default NULL,
1154 `finedays` int(11) default NULL,
1155 `firstremind` int(11) default NULL,
1156 `chargeperiod` int(11) default NULL,
1157 `accountsent` int(11) default NULL,
1158 `chargename` varchar(100) default NULL,
1159 `maxissueqty` int(4) default NULL,
1160 `issuelength` int(4) default NULL,
1161 `branchcode` varchar(10) NOT NULL default '',
1162 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1163 KEY `categorycode` (`categorycode`),
1164 KEY `itemtype` (`itemtype`)
1165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1168 -- Table structure for table `items`
1171 DROP TABLE IF EXISTS `items`;
1172 CREATE TABLE `items` (
1173 `itemnumber` int(11) NOT NULL auto_increment,
1174 `biblionumber` int(11) NOT NULL default 0,
1175 `biblioitemnumber` int(11) NOT NULL default 0,
1176 `barcode` varchar(20) default NULL,
1177 `dateaccessioned` date default NULL,
1178 `booksellerid` mediumtext default NULL,
1179 `homebranch` varchar(10) default NULL,
1180 `price` decimal(8,2) default NULL,
1181 `replacementprice` decimal(8,2) default NULL,
1182 `replacementpricedate` date default NULL,
1183 `datelastborrowed` date default NULL,
1184 `datelastseen` date default NULL,
1185 `stack` tinyint(1) default NULL,
1186 `notforloan` tinyint(1) NOT NULL default 0,
1187 `damaged` tinyint(1) NOT NULL default 0,
1188 `itemlost` tinyint(1) NOT NULL default 0,
1189 `wthdrawn` tinyint(1) NOT NULL default 0,
1190 `itemcallnumber` varchar(30) default NULL,
1191 `issues` smallint(6) default NULL,
1192 `renewals` smallint(6) default NULL,
1193 `reserves` smallint(6) default NULL,
1194 `restricted` tinyint(1) default NULL,
1195 `itemnotes` mediumtext,
1196 `holdingbranch` varchar(10) default NULL,
1197 `paidfor` mediumtext,
1198 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1199 `location` varchar(80) default NULL,
1200 `onloan` date default NULL,
1201 `cn_source` varchar(10) default NULL,
1202 `cn_sort` varchar(30) default NULL,
1203 `ccode` varchar(10) default NULL,
1204 `materials` varchar(10) default NULL,
1205 `uri` varchar(255) default NULL,
1206 `itype` varchar(10) default NULL,
1207 `more_subfields_xml` longtext default NULL,
1208 `enumchron` varchar(80) default NULL,
1209 `copynumber` varchar(32) default NULL,
1210 PRIMARY KEY (`itemnumber`),
1211 UNIQUE KEY `itembarcodeidx` (`barcode`),
1212 KEY `itembinoidx` (`biblioitemnumber`),
1213 KEY `itembibnoidx` (`biblionumber`),
1214 KEY `homebranch` (`homebranch`),
1215 KEY `holdingbranch` (`holdingbranch`),
1216 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1217 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1218 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1219 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1222 -- Table structure for table `itemtypes`
1225 DROP TABLE IF EXISTS `itemtypes`;
1226 CREATE TABLE `itemtypes` (
1227 `itemtype` varchar(10) NOT NULL default '',
1228 `description` mediumtext,
1229 `renewalsallowed` smallint(6) default NULL,
1230 `rentalcharge` double(16,4) default NULL,
1231 `notforloan` smallint(6) default NULL,
1232 `imageurl` varchar(200) default NULL,
1234 PRIMARY KEY (`itemtype`),
1235 UNIQUE KEY `itemtype` (`itemtype`)
1236 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1239 -- Table structure for table `labels`
1242 DROP TABLE IF EXISTS `labels`;
1243 CREATE TABLE `labels` (
1244 `labelid` int(11) NOT NULL auto_increment,
1245 `batch_id` varchar(10) NOT NULL default 1,
1246 `itemnumber` varchar(100) NOT NULL default '',
1247 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1248 PRIMARY KEY (`labelid`)
1249 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1252 -- Table structure for table `labels_conf`
1255 DROP TABLE IF EXISTS `labels_conf`;
1256 CREATE TABLE `labels_conf` (
1257 `id` int(4) NOT NULL auto_increment,
1258 `barcodetype` char(100) default '',
1259 `title` int(1) default '0',
1260 `subtitle` int(1) default '0',
1261 `itemtype` int(1) default '0',
1262 `barcode` int(1) default '0',
1263 `dewey` int(1) default '0',
1264 `classification` int(1) default NULL,
1265 `subclass` int(1) default '0',
1266 `itemcallnumber` int(1) default '0',
1267 `author` int(1) default '0',
1268 `issn` int(1) default '0',
1269 `isbn` int(1) default '0',
1270 `startlabel` int(2) NOT NULL default '1',
1271 `printingtype` char(32) default 'BAR',
1272 `formatstring` varchar(64) default NULL,
1273 `layoutname` char(20) NOT NULL default 'TEST',
1274 `guidebox` int(1) default '0',
1275 `active` tinyint(1) default '1',
1276 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1277 `ccode` char(4) collate utf8_unicode_ci default NULL,
1278 `callnum_split` int(1) default NULL,
1279 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1284 -- Table structure for table `labels_profile`
1287 DROP TABLE IF EXISTS `labels_profile`;
1288 CREATE TABLE `labels_profile` (
1289 `tmpl_id` int(4) NOT NULL,
1290 `prof_id` int(4) NOT NULL,
1291 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1292 UNIQUE KEY `prof_id` (`prof_id`)
1293 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1296 -- Table structure for table `labels_templates`
1299 DROP TABLE IF EXISTS `labels_templates`;
1300 CREATE TABLE `labels_templates` (
1301 `tmpl_id` int(4) NOT NULL auto_increment,
1302 `tmpl_code` char(100) default '',
1303 `tmpl_desc` char(100) default '',
1304 `page_width` float default '0',
1305 `page_height` float default '0',
1306 `label_width` float default '0',
1307 `label_height` float default '0',
1308 `topmargin` float default '0',
1309 `leftmargin` float default '0',
1310 `cols` int(2) default '0',
1311 `rows` int(2) default '0',
1312 `colgap` float default '0',
1313 `rowgap` float default '0',
1314 `active` int(1) default NULL,
1315 `units` char(20) default 'PX',
1316 `fontsize` int(4) NOT NULL default '3',
1317 `font` char(10) NOT NULL default 'TR',
1318 PRIMARY KEY (`tmpl_id`)
1319 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1322 -- Table structure for table `letter`
1325 DROP TABLE IF EXISTS `letter`;
1326 CREATE TABLE `letter` (
1327 `module` varchar(20) NOT NULL default '',
1328 `code` varchar(20) NOT NULL default '',
1329 `name` varchar(100) NOT NULL default '',
1330 `title` varchar(200) NOT NULL default '',
1332 PRIMARY KEY (`module`,`code`)
1333 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1336 -- Table structure for table `marc_subfield_structure`
1339 DROP TABLE IF EXISTS `marc_subfield_structure`;
1340 CREATE TABLE `marc_subfield_structure` (
1341 `tagfield` varchar(3) NOT NULL default '',
1342 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1343 `liblibrarian` varchar(255) NOT NULL default '',
1344 `libopac` varchar(255) NOT NULL default '',
1345 `repeatable` tinyint(4) NOT NULL default 0,
1346 `mandatory` tinyint(4) NOT NULL default 0,
1347 `kohafield` varchar(40) default NULL,
1348 `tab` tinyint(1) default NULL,
1349 `authorised_value` varchar(20) default NULL,
1350 `authtypecode` varchar(20) default NULL,
1351 `value_builder` varchar(80) default NULL,
1352 `isurl` tinyint(1) default NULL,
1353 `hidden` tinyint(1) default NULL,
1354 `frameworkcode` varchar(4) NOT NULL default '',
1355 `seealso` varchar(1100) default NULL,
1356 `link` varchar(80) default NULL,
1357 `defaultvalue` text default NULL,
1358 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1359 KEY `kohafield_2` (`kohafield`),
1360 KEY `tab` (`frameworkcode`,`tab`),
1361 KEY `kohafield` (`frameworkcode`,`kohafield`)
1362 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1365 -- Table structure for table `marc_tag_structure`
1368 DROP TABLE IF EXISTS `marc_tag_structure`;
1369 CREATE TABLE `marc_tag_structure` (
1370 `tagfield` varchar(3) NOT NULL default '',
1371 `liblibrarian` varchar(255) NOT NULL default '',
1372 `libopac` varchar(255) NOT NULL default '',
1373 `repeatable` tinyint(4) NOT NULL default 0,
1374 `mandatory` tinyint(4) NOT NULL default 0,
1375 `authorised_value` varchar(10) default NULL,
1376 `frameworkcode` varchar(4) NOT NULL default '',
1377 PRIMARY KEY (`frameworkcode`,`tagfield`)
1378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1381 -- Table structure for table `marc_matchers`
1384 DROP TABLE IF EXISTS `marc_matchers`;
1385 CREATE TABLE `marc_matchers` (
1386 `matcher_id` int(11) NOT NULL auto_increment,
1387 `code` varchar(10) NOT NULL default '',
1388 `description` varchar(255) NOT NULL default '',
1389 `record_type` varchar(10) NOT NULL default 'biblio',
1390 `threshold` int(11) NOT NULL default 0,
1391 PRIMARY KEY (`matcher_id`),
1392 KEY `code` (`code`),
1393 KEY `record_type` (`record_type`)
1394 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1397 -- Table structure for table `matchpoints`
1399 DROP TABLE IF EXISTS `matchpoints`;
1400 CREATE TABLE `matchpoints` (
1401 `matcher_id` int(11) NOT NULL,
1402 `matchpoint_id` int(11) NOT NULL auto_increment,
1403 `search_index` varchar(30) NOT NULL default '',
1404 `score` int(11) NOT NULL default 0,
1405 PRIMARY KEY (`matchpoint_id`),
1406 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1407 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1408 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1412 -- Table structure for table `matchpoint_components`
1414 DROP TABLE IF EXISTS `matchpoint_components`;
1415 CREATE TABLE `matchpoint_components` (
1416 `matchpoint_id` int(11) NOT NULL,
1417 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1418 sequence int(11) NOT NULL default 0,
1419 tag varchar(3) NOT NULL default '',
1420 subfields varchar(40) NOT NULL default '',
1421 offset int(4) NOT NULL default 0,
1422 length int(4) NOT NULL default 0,
1423 PRIMARY KEY (`matchpoint_component_id`),
1424 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1425 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1426 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1427 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1430 -- Table structure for table `matcher_component_norms`
1432 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1433 CREATE TABLE `matchpoint_component_norms` (
1434 `matchpoint_component_id` int(11) NOT NULL,
1435 `sequence` int(11) NOT NULL default 0,
1436 `norm_routine` varchar(50) NOT NULL default '',
1437 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1438 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1439 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1440 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1443 -- Table structure for table `matcher_matchpoints`
1445 DROP TABLE IF EXISTS `matcher_matchpoints`;
1446 CREATE TABLE `matcher_matchpoints` (
1447 `matcher_id` int(11) NOT NULL,
1448 `matchpoint_id` int(11) NOT NULL,
1449 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1450 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1451 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1452 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1453 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1456 -- Table structure for table `matchchecks`
1458 DROP TABLE IF EXISTS `matchchecks`;
1459 CREATE TABLE `matchchecks` (
1460 `matcher_id` int(11) NOT NULL,
1461 `matchcheck_id` int(11) NOT NULL auto_increment,
1462 `source_matchpoint_id` int(11) NOT NULL,
1463 `target_matchpoint_id` int(11) NOT NULL,
1464 PRIMARY KEY (`matchcheck_id`),
1465 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1466 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1467 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1468 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1469 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1470 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1471 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1474 -- Table structure for table `notifys`
1477 DROP TABLE IF EXISTS `notifys`;
1478 CREATE TABLE `notifys` (
1479 `notify_id` int(11) NOT NULL default 0,
1480 `borrowernumber` int(11) NOT NULL default 0,
1481 `itemnumber` int(11) NOT NULL default 0,
1482 `notify_date` date default NULL,
1483 `notify_send_date` date default NULL,
1484 `notify_level` int(1) NOT NULL default 0,
1485 `method` varchar(20) NOT NULL default ''
1486 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1489 -- Table structure for table `nozebra`
1492 DROP TABLE IF EXISTS `nozebra`;
1493 CREATE TABLE `nozebra` (
1494 `server` varchar(20) NOT NULL,
1495 `indexname` varchar(40) NOT NULL,
1496 `value` varchar(250) NOT NULL,
1497 `biblionumbers` longtext NOT NULL,
1498 KEY `indexname` (`server`,`indexname`),
1499 KEY `value` (`server`,`value`))
1500 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1503 -- Table structure for table `old_issues`
1506 DROP TABLE IF EXISTS `old_issues`;
1507 CREATE TABLE `old_issues` (
1508 `borrowernumber` int(11) default NULL,
1509 `itemnumber` int(11) default NULL,
1510 `date_due` date default NULL,
1511 `branchcode` varchar(10) default NULL,
1512 `issuingbranch` varchar(18) default NULL,
1513 `returndate` date default NULL,
1514 `lastreneweddate` date default NULL,
1515 `return` varchar(4) default NULL,
1516 `renewals` tinyint(4) default NULL,
1517 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1518 `issuedate` date default NULL,
1519 KEY `old_issuesborridx` (`borrowernumber`),
1520 KEY `old_issuesitemidx` (`itemnumber`),
1521 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1522 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1523 ON DELETE SET NULL ON UPDATE SET NULL,
1524 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1525 ON DELETE SET NULL ON UPDATE SET NULL
1526 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1529 -- Table structure for table `old_reserves`
1531 DROP TABLE IF EXISTS `old_reserves`;
1532 CREATE TABLE `old_reserves` (
1533 `borrowernumber` int(11) default NULL,
1534 `reservedate` date default NULL,
1535 `biblionumber` int(11) default NULL,
1536 `constrainttype` varchar(1) default NULL,
1537 `branchcode` varchar(10) default NULL,
1538 `notificationdate` date default NULL,
1539 `reminderdate` date default NULL,
1540 `cancellationdate` date default NULL,
1541 `reservenotes` mediumtext,
1542 `priority` smallint(6) default NULL,
1543 `found` varchar(1) default NULL,
1544 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1545 `itemnumber` int(11) default NULL,
1546 `waitingdate` date default NULL,
1547 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1548 KEY `old_reserves_biblionumber` (`biblionumber`),
1549 KEY `old_reserves_itemnumber` (`itemnumber`),
1550 KEY `old_reserves_branchcode` (`branchcode`),
1551 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1552 ON DELETE SET NULL ON UPDATE SET NULL,
1553 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1554 ON DELETE SET NULL ON UPDATE SET NULL,
1555 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1556 ON DELETE SET NULL ON UPDATE SET NULL
1557 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1560 -- Table structure for table `opac_news`
1563 DROP TABLE IF EXISTS `opac_news`;
1564 CREATE TABLE `opac_news` (
1565 `idnew` int(10) unsigned NOT NULL auto_increment,
1566 `title` varchar(250) NOT NULL default '',
1567 `new` text NOT NULL,
1568 `lang` varchar(25) NOT NULL default '',
1569 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1570 `expirationdate` date default NULL,
1571 `number` int(11) default NULL,
1572 PRIMARY KEY (`idnew`)
1573 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1576 -- Table structure for table `overduerules`
1579 DROP TABLE IF EXISTS `overduerules`;
1580 CREATE TABLE `overduerules` (
1581 `branchcode` varchar(10) NOT NULL default '',
1582 `categorycode` varchar(10) NOT NULL default '',
1583 `delay1` int(4) default 0,
1584 `letter1` varchar(20) default NULL,
1585 `debarred1` varchar(1) default 0,
1586 `delay2` int(4) default 0,
1587 `debarred2` varchar(1) default 0,
1588 `letter2` varchar(20) default NULL,
1589 `delay3` int(4) default 0,
1590 `letter3` varchar(20) default NULL,
1591 `debarred3` int(1) default 0,
1592 PRIMARY KEY (`branchcode`,`categorycode`)
1593 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1596 -- Table structure for table `patroncards`
1599 DROP TABLE IF EXISTS `patroncards`;
1600 CREATE TABLE `patroncards` (
1601 `cardid` int(11) NOT NULL auto_increment,
1602 `batch_id` varchar(10) NOT NULL default '1',
1603 `borrowernumber` int(11) NOT NULL,
1604 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1605 PRIMARY KEY (`cardid`),
1606 KEY `patroncards_ibfk_1` (`borrowernumber`),
1607 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1608 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1611 -- Table structure for table `patronimage`
1614 DROP TABLE IF EXISTS `patronimage`;
1615 CREATE TABLE `patronimage` (
1616 `cardnumber` varchar(16) NOT NULL,
1617 `mimetype` varchar(15) NOT NULL,
1618 `imagefile` mediumblob NOT NULL,
1619 PRIMARY KEY (`cardnumber`),
1620 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1621 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1624 -- Table structure for table `printers`
1627 DROP TABLE IF EXISTS `printers`;
1628 CREATE TABLE `printers` (
1629 `printername` varchar(40) NOT NULL default '',
1630 `printqueue` varchar(20) default NULL,
1631 `printtype` varchar(20) default NULL,
1632 PRIMARY KEY (`printername`)
1633 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1636 -- Table structure for table `printers_profile`
1639 DROP TABLE IF EXISTS `printers_profile`;
1640 CREATE TABLE `printers_profile` (
1641 `prof_id` int(4) NOT NULL auto_increment,
1642 `printername` varchar(40) NOT NULL,
1643 `tmpl_id` int(4) NOT NULL,
1644 `paper_bin` varchar(20) NOT NULL,
1645 `offset_horz` float default NULL,
1646 `offset_vert` float default NULL,
1647 `creep_horz` float default NULL,
1648 `creep_vert` float default NULL,
1649 `unit` char(20) NOT NULL default 'POINT',
1650 PRIMARY KEY (`prof_id`),
1651 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1652 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1653 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1656 -- Table structure for table `repeatable_holidays`
1659 DROP TABLE IF EXISTS `repeatable_holidays`;
1660 CREATE TABLE `repeatable_holidays` (
1661 `id` int(11) NOT NULL auto_increment,
1662 `branchcode` varchar(10) NOT NULL default '',
1663 `weekday` smallint(6) default NULL,
1664 `day` smallint(6) default NULL,
1665 `month` smallint(6) default NULL,
1666 `title` varchar(50) NOT NULL default '',
1667 `description` text NOT NULL,
1669 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1672 -- Table structure for table `reports_dictionary`
1675 DROP TABLE IF EXISTS `reports_dictionary`;
1676 CREATE TABLE reports_dictionary (
1677 `id` int(11) NOT NULL auto_increment,
1678 `name` varchar(255) default NULL,
1680 `date_created` datetime default NULL,
1681 `date_modified` datetime default NULL,
1683 `area` int(11) default NULL,
1685 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1688 -- Table structure for table `reserveconstraints`
1691 DROP TABLE IF EXISTS `reserveconstraints`;
1692 CREATE TABLE `reserveconstraints` (
1693 `borrowernumber` int(11) NOT NULL default 0,
1694 `reservedate` date default NULL,
1695 `biblionumber` int(11) NOT NULL default 0,
1696 `biblioitemnumber` int(11) default NULL,
1697 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1698 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1701 -- Table structure for table `reserves`
1704 DROP TABLE IF EXISTS `reserves`;
1705 CREATE TABLE `reserves` (
1706 `borrowernumber` int(11) NOT NULL default 0,
1707 `reservedate` date default NULL,
1708 `biblionumber` int(11) NOT NULL default 0,
1709 `constrainttype` varchar(1) default NULL,
1710 `branchcode` varchar(10) default NULL,
1711 `notificationdate` date default NULL,
1712 `reminderdate` date default NULL,
1713 `cancellationdate` date default NULL,
1714 `reservenotes` mediumtext,
1715 `priority` smallint(6) default NULL,
1716 `found` varchar(1) default NULL,
1717 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1718 `itemnumber` int(11) default NULL,
1719 `waitingdate` date default NULL,
1720 KEY `borrowernumber` (`borrowernumber`),
1721 KEY `biblionumber` (`biblionumber`),
1722 KEY `itemnumber` (`itemnumber`),
1723 KEY `branchcode` (`branchcode`),
1724 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1725 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1726 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1727 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1728 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1731 -- Table structure for table `reviews`
1734 DROP TABLE IF EXISTS `reviews`;
1735 CREATE TABLE `reviews` (
1736 `reviewid` int(11) NOT NULL auto_increment,
1737 `borrowernumber` int(11) default NULL,
1738 `biblionumber` int(11) default NULL,
1740 `approved` tinyint(4) default NULL,
1741 `datereviewed` datetime default NULL,
1742 PRIMARY KEY (`reviewid`)
1743 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1746 -- Table structure for table `roadtype`
1749 DROP TABLE IF EXISTS `roadtype`;
1750 CREATE TABLE `roadtype` (
1751 `roadtypeid` int(11) NOT NULL auto_increment,
1752 `road_type` varchar(100) NOT NULL default '',
1753 PRIMARY KEY (`roadtypeid`)
1754 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1757 -- Table structure for table `saved_sql`
1760 DROP TABLE IF EXISTS `saved_sql`;
1761 CREATE TABLE saved_sql (
1762 `id` int(11) NOT NULL auto_increment,
1763 `borrowernumber` int(11) default NULL,
1764 `date_created` datetime default NULL,
1765 `last_modified` datetime default NULL,
1767 `last_run` datetime default NULL,
1768 `report_name` varchar(255) default NULL,
1769 `type` varchar(255) default NULL,
1772 KEY boridx (`borrowernumber`)
1773 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1777 -- Table structure for `saved_reports`
1780 DROP TABLE IF EXISTS `saved_reports`;
1781 CREATE TABLE saved_reports (
1782 `id` int(11) NOT NULL auto_increment,
1783 `report_id` int(11) default NULL,
1785 `date_run` datetime default NULL,
1787 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1791 -- Table structure for table `serial`
1794 DROP TABLE IF EXISTS `serial`;
1795 CREATE TABLE `serial` (
1796 `serialid` int(11) NOT NULL auto_increment,
1797 `biblionumber` varchar(100) NOT NULL default '',
1798 `subscriptionid` varchar(100) NOT NULL default '',
1799 `serialseq` varchar(100) NOT NULL default '',
1800 `status` tinyint(4) NOT NULL default 0,
1801 `planneddate` date default NULL,
1803 `publisheddate` date default NULL,
1804 `itemnumber` text default NULL,
1805 `claimdate` date default NULL,
1806 `routingnotes` text,
1807 PRIMARY KEY (`serialid`)
1808 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1811 -- Table structure for table `sessions`
1814 DROP TABLE IF EXISTS sessions;
1815 CREATE TABLE sessions (
1816 `id` varchar(32) NOT NULL,
1817 `a_session` text NOT NULL,
1819 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1822 -- Table structure for table `special_holidays`
1825 DROP TABLE IF EXISTS `special_holidays`;
1826 CREATE TABLE `special_holidays` (
1827 `id` int(11) NOT NULL auto_increment,
1828 `branchcode` varchar(10) NOT NULL default '',
1829 `day` smallint(6) NOT NULL default 0,
1830 `month` smallint(6) NOT NULL default 0,
1831 `year` smallint(6) NOT NULL default 0,
1832 `isexception` smallint(1) NOT NULL default 1,
1833 `title` varchar(50) NOT NULL default '',
1834 `description` text NOT NULL,
1836 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1839 -- Table structure for table `statistics`
1842 DROP TABLE IF EXISTS `statistics`;
1843 CREATE TABLE `statistics` (
1844 `datetime` datetime default NULL,
1845 `branch` varchar(10) default NULL,
1846 `proccode` varchar(4) default NULL,
1847 `value` double(16,4) default NULL,
1848 `type` varchar(16) default NULL,
1850 `usercode` varchar(10) default NULL,
1851 `itemnumber` int(11) default NULL,
1852 `itemtype` varchar(10) default NULL,
1853 `borrowernumber` int(11) default NULL,
1854 `associatedborrower` int(11) default NULL,
1855 KEY `timeidx` (`datetime`)
1856 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1859 -- Table structure for table `stopwords`
1862 DROP TABLE IF EXISTS `stopwords`;
1863 CREATE TABLE `stopwords` (
1864 `word` varchar(255) default NULL
1865 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1868 -- Table structure for table `subscription`
1871 DROP TABLE IF EXISTS `subscription`;
1872 CREATE TABLE `subscription` (
1873 `biblionumber` int(11) NOT NULL default 0,
1874 `subscriptionid` int(11) NOT NULL auto_increment,
1875 `librarian` varchar(100) default '',
1876 `startdate` date default NULL,
1877 `aqbooksellerid` int(11) default 0,
1878 `cost` int(11) default 0,
1879 `aqbudgetid` int(11) default 0,
1880 `weeklength` int(11) default 0,
1881 `monthlength` int(11) default 0,
1882 `numberlength` int(11) default 0,
1883 `periodicity` tinyint(4) default 0,
1884 `dow` varchar(100) default '',
1885 `numberingmethod` varchar(100) default '',
1887 `status` varchar(100) NOT NULL default '',
1888 `add1` int(11) default 0,
1889 `every1` int(11) default 0,
1890 `whenmorethan1` int(11) default 0,
1891 `setto1` int(11) default NULL,
1892 `lastvalue1` int(11) default NULL,
1893 `add2` int(11) default 0,
1894 `every2` int(11) default 0,
1895 `whenmorethan2` int(11) default 0,
1896 `setto2` int(11) default NULL,
1897 `lastvalue2` int(11) default NULL,
1898 `add3` int(11) default 0,
1899 `every3` int(11) default 0,
1900 `innerloop1` int(11) default 0,
1901 `innerloop2` int(11) default 0,
1902 `innerloop3` int(11) default 0,
1903 `whenmorethan3` int(11) default 0,
1904 `setto3` int(11) default NULL,
1905 `lastvalue3` int(11) default NULL,
1906 `issuesatonce` tinyint(3) NOT NULL default 1,
1907 `firstacquidate` date default NULL,
1908 `manualhistory` tinyint(1) NOT NULL default 0,
1909 `irregularity` text,
1910 `letter` varchar(20) default NULL,
1911 `numberpattern` tinyint(3) default 0,
1912 `distributedto` text,
1913 `internalnotes` longtext,
1915 `branchcode` varchar(10) NOT NULL default '',
1916 `hemisphere` tinyint(3) default 0,
1917 `lastbranch` varchar(10),
1918 `serialsadditems` tinyint(1) NOT NULL default '0',
1919 PRIMARY KEY (`subscriptionid`)
1920 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1923 -- Table structure for table `subscriptionhistory`
1926 DROP TABLE IF EXISTS `subscriptionhistory`;
1927 CREATE TABLE `subscriptionhistory` (
1928 `biblionumber` int(11) NOT NULL default 0,
1929 `subscriptionid` int(11) NOT NULL default 0,
1930 `histstartdate` date default NULL,
1931 `enddate` date default NULL,
1932 `missinglist` longtext NOT NULL,
1933 `recievedlist` longtext NOT NULL,
1934 `opacnote` varchar(150) NOT NULL default '',
1935 `librariannote` varchar(150) NOT NULL default '',
1936 PRIMARY KEY (`subscriptionid`),
1937 KEY `biblionumber` (`biblionumber`)
1938 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1941 -- Table structure for table `subscriptionroutinglist`
1944 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1945 CREATE TABLE `subscriptionroutinglist` (
1946 `routingid` int(11) NOT NULL auto_increment,
1947 `borrowernumber` int(11) default NULL,
1948 `ranking` int(11) default NULL,
1949 `subscriptionid` int(11) default NULL,
1950 PRIMARY KEY (`routingid`)
1951 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1954 -- Table structure for table `suggestions`
1957 DROP TABLE IF EXISTS `suggestions`;
1958 CREATE TABLE `suggestions` (
1959 `suggestionid` int(8) NOT NULL auto_increment,
1960 `suggestedby` int(11) NOT NULL default 0,
1961 `managedby` int(11) default NULL,
1962 `STATUS` varchar(10) NOT NULL default '',
1964 `author` varchar(80) default NULL,
1965 `title` varchar(80) default NULL,
1966 `copyrightdate` smallint(6) default NULL,
1967 `publishercode` varchar(255) default NULL,
1968 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1969 `volumedesc` varchar(255) default NULL,
1970 `publicationyear` smallint(6) default 0,
1971 `place` varchar(255) default NULL,
1972 `isbn` varchar(30) default NULL,
1973 `mailoverseeing` smallint(1) default 0,
1974 `biblionumber` int(11) default NULL,
1976 PRIMARY KEY (`suggestionid`),
1977 KEY `suggestedby` (`suggestedby`),
1978 KEY `managedby` (`managedby`)
1979 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1982 -- Table structure for table `systempreferences`
1985 DROP TABLE IF EXISTS `systempreferences`;
1986 CREATE TABLE `systempreferences` (
1987 `variable` varchar(50) NOT NULL default '',
1989 `options` mediumtext,
1991 `type` varchar(20) default NULL,
1992 PRIMARY KEY (`variable`)
1993 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1996 -- Table structure for table `tags`
1999 DROP TABLE IF EXISTS `tags`;
2000 CREATE TABLE `tags` (
2001 `entry` varchar(255) NOT NULL default '',
2002 `weight` bigint(20) NOT NULL default 0,
2003 PRIMARY KEY (`entry`)
2004 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2007 -- Table structure for table `tags_all`
2010 DROP TABLE IF EXISTS `tags_all`;
2011 CREATE TABLE `tags_all` (
2012 `tag_id` int(11) NOT NULL auto_increment,
2013 `borrowernumber` int(11) NOT NULL,
2014 `biblionumber` int(11) NOT NULL,
2015 `term` varchar(255) NOT NULL,
2016 `language` int(4) default NULL,
2017 `date_created` datetime NOT NULL,
2018 PRIMARY KEY (`tag_id`),
2019 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2020 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2021 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2022 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2023 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2024 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2028 -- Table structure for table `tags_approval`
2031 DROP TABLE IF EXISTS `tags_approval`;
2032 CREATE TABLE `tags_approval` (
2033 `term` varchar(255) NOT NULL,
2034 `approved` int(1) NOT NULL default '0',
2035 `date_approved` datetime default NULL,
2036 `approved_by` int(11) default NULL,
2037 `weight_total` int(9) NOT NULL default '1',
2038 PRIMARY KEY (`term`),
2039 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2040 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2041 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2042 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2045 -- Table structure for table `tags_index`
2048 DROP TABLE IF EXISTS `tags_index`;
2049 CREATE TABLE `tags_index` (
2050 `term` varchar(255) NOT NULL,
2051 `biblionumber` int(11) NOT NULL,
2052 `weight` int(9) NOT NULL default '1',
2053 PRIMARY KEY (`term`,`biblionumber`),
2054 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2055 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2056 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2057 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2058 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2059 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2062 -- Table structure for table `userflags`
2065 DROP TABLE IF EXISTS `userflags`;
2066 CREATE TABLE `userflags` (
2067 `bit` int(11) NOT NULL default 0,
2068 `flag` varchar(30) default NULL,
2069 `flagdesc` varchar(255) default NULL,
2070 `defaulton` int(11) default NULL,
2072 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2075 -- Table structure for table `virtualshelves`
2078 DROP TABLE IF EXISTS `virtualshelves`;
2079 CREATE TABLE `virtualshelves` (
2080 `shelfnumber` int(11) NOT NULL auto_increment,
2081 `shelfname` varchar(255) default NULL,
2082 `owner` varchar(80) default NULL,
2083 `category` varchar(1) default NULL,
2084 `sortfield` varchar(16) default NULL,
2085 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2086 PRIMARY KEY (`shelfnumber`)
2087 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2090 -- Table structure for table `virtualshelfcontents`
2093 DROP TABLE IF EXISTS `virtualshelfcontents`;
2094 CREATE TABLE `virtualshelfcontents` (
2095 `shelfnumber` int(11) NOT NULL default 0,
2096 `biblionumber` int(11) NOT NULL default 0,
2097 `flags` int(11) default NULL,
2098 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2099 KEY `shelfnumber` (`shelfnumber`),
2100 KEY `biblionumber` (`biblionumber`),
2101 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2102 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2103 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2106 -- Table structure for table `z3950servers`
2109 DROP TABLE IF EXISTS `z3950servers`;
2110 CREATE TABLE `z3950servers` (
2111 `host` varchar(255) default NULL,
2112 `port` int(11) default NULL,
2113 `db` varchar(255) default NULL,
2114 `userid` varchar(255) default NULL,
2115 `password` varchar(255) default NULL,
2117 `id` int(11) NOT NULL auto_increment,
2118 `checked` smallint(6) default NULL,
2119 `rank` int(11) default NULL,
2120 `syntax` varchar(80) default NULL,
2122 `position` enum('primary','secondary','') NOT NULL default 'primary',
2123 `type` enum('zed','opensearch') NOT NULL default 'zed',
2124 `encoding` text default NULL,
2125 `description` text NOT NULL,
2127 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2130 -- Table structure for table `zebraqueue`
2133 DROP TABLE IF EXISTS `zebraqueue`;
2134 CREATE TABLE `zebraqueue` (
2135 `id` int(11) NOT NULL auto_increment,
2136 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2137 `operation` char(20) NOT NULL default '',
2138 `server` char(20) NOT NULL default '',
2139 `done` int(11) NOT NULL default '0',
2140 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2142 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2143 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2145 DROP TABLE IF EXISTS `services_throttle`;
2146 CREATE TABLE `services_throttle` (
2147 `service_type` varchar(10) NOT NULL default '',
2148 `service_count` varchar(45) default NULL,
2149 PRIMARY KEY (`service_type`)
2150 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2152 -- http://www.w3.org/International/articles/language-tags/
2155 DROP TABLE IF EXISTS language_subtag_registry;
2156 CREATE TABLE language_subtag_registry (
2158 type varchar(25), -- language-script-region-variant-extension-privateuse
2159 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2161 KEY `subtag` (`subtag`)
2162 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2164 -- TODO: add suppress_scripts
2165 -- this maps three letter codes defined in iso639.2 back to their
2166 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2167 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2168 CREATE TABLE language_rfc4646_to_iso639 (
2169 rfc4646_subtag varchar(25),
2170 iso639_2_code varchar(25),
2171 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2172 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2174 DROP TABLE IF EXISTS language_descriptions;
2175 CREATE TABLE language_descriptions (
2179 description varchar(255),
2181 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2183 -- bi-directional support, keyed by script subcode
2184 DROP TABLE IF EXISTS language_script_bidi;
2185 CREATE TABLE language_script_bidi (
2186 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2187 bidi varchar(3), -- rtl ltr
2188 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2191 -- TODO: need to map language subtags to script subtags for detection
2192 -- of bidi when script is not specified (like ar, he)
2193 DROP TABLE IF EXISTS language_script_mapping;
2194 CREATE TABLE language_script_mapping (
2195 language_subtag varchar(25),
2196 script_subtag varchar(25),
2197 KEY `language_subtag` (`language_subtag`)
2198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2200 DROP TABLE IF EXISTS `permissions`;
2201 CREATE TABLE `permissions` (
2202 `module_bit` int(11) NOT NULL DEFAULT 0,
2203 `code` varchar(64) DEFAULT NULL,
2204 `description` varchar(255) DEFAULT NULL,
2205 PRIMARY KEY (`module_bit`, `code`),
2206 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2207 ON DELETE CASCADE ON UPDATE CASCADE
2208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2210 DROP TABLE IF EXISTS `serialitems`;
2211 CREATE TABLE `serialitems` (
2212 `itemnumber` int(11) NOT NULL,
2213 `serialid` int(11) NOT NULL,
2214 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2215 KEY `serialitems_sfk_1` (`serialid`),
2216 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
2217 CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
2218 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2220 DROP TABLE IF EXISTS `user_permissions`;
2221 CREATE TABLE `user_permissions` (
2222 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2223 `module_bit` int(11) NOT NULL DEFAULT 0,
2224 `code` varchar(64) DEFAULT NULL,
2225 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2226 ON DELETE CASCADE ON UPDATE CASCADE,
2227 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2228 ON DELETE CASCADE ON UPDATE CASCADE
2229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2232 -- Table structure for table `tmp_holdsqueue`
2235 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2236 CREATE TABLE `tmp_holdsqueue` (
2237 `biblionumber` int(11) default NULL,
2238 `itemnumber` int(11) default NULL,
2239 `barcode` varchar(20) default NULL,
2240 `surname` mediumtext NOT NULL,
2243 `borrowernumber` int(11) NOT NULL,
2244 `cardnumber` varchar(16) default NULL,
2245 `reservedate` date default NULL,
2247 `itemcallnumber` varchar(30) default NULL,
2248 `holdingbranch` varchar(10) default NULL,
2249 `pickbranch` varchar(10) default NULL,
2251 `item_level_request` tinyint(4) NOT NULL default 0
2252 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2255 -- Table structure for table `message_queue`
2258 DROP TABLE IF EXISTS `message_queue`;
2259 CREATE TABLE `message_queue` (
2260 `message_id` int(11) NOT NULL auto_increment,
2261 `borrowernumber` int(11) default NULL,
2264 `message_transport_type` varchar(20) NOT NULL,
2265 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2266 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2267 `to_address` mediumtext,
2268 `from_address` mediumtext,
2269 `content_type` text,
2270 KEY `message_id` (`message_id`),
2271 KEY `borrowernumber` (`borrowernumber`),
2272 KEY `message_transport_type` (`message_transport_type`),
2273 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2274 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2275 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2278 -- Table structure for table `message_transport_types`
2281 DROP TABLE IF EXISTS `message_transport_types`;
2282 CREATE TABLE `message_transport_types` (
2283 `message_transport_type` varchar(20) NOT NULL,
2284 PRIMARY KEY (`message_transport_type`)
2285 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2288 -- Table structure for table `message_attributes`
2291 DROP TABLE IF EXISTS `message_attributes`;
2292 CREATE TABLE `message_attributes` (
2293 `message_attribute_id` int(11) NOT NULL auto_increment,
2294 `message_name` varchar(20) NOT NULL default '',
2295 `takes_days` tinyint(1) NOT NULL default '0',
2296 PRIMARY KEY (`message_attribute_id`),
2297 UNIQUE KEY `message_name` (`message_name`)
2298 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2301 -- Table structure for table `message_transports`
2304 DROP TABLE IF EXISTS `message_transports`;
2305 CREATE TABLE `message_transports` (
2306 `message_attribute_id` int(11) NOT NULL,
2307 `message_transport_type` varchar(20) NOT NULL,
2308 `is_digest` tinyint(1) NOT NULL default '0',
2309 `letter_module` varchar(20) NOT NULL default '',
2310 `letter_code` varchar(20) NOT NULL default '',
2311 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2312 KEY `message_transport_type` (`message_transport_type`),
2313 KEY `letter_module` (`letter_module`,`letter_code`),
2314 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2315 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2316 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2317 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2320 -- Table structure for table `borrower_message_preferences`
2323 DROP TABLE IF EXISTS `borrower_message_preferences`;
2324 CREATE TABLE `borrower_message_preferences` (
2325 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2326 `borrowernumber` int(11) NOT NULL default '0',
2327 `message_attribute_id` int(11) default '0',
2328 `days_in_advance` int(11) default '0',
2329 `wants_digest` tinyint(1) NOT NULL default '0',
2330 PRIMARY KEY (`borrower_message_preference_id`),
2331 KEY `borrowernumber` (`borrowernumber`),
2332 KEY `message_attribute_id` (`message_attribute_id`),
2333 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2334 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
2335 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2338 -- Table structure for table `borrower_message_transport_preferences`
2341 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2342 CREATE TABLE `borrower_message_transport_preferences` (
2343 `borrower_message_preference_id` int(11) NOT NULL default '0',
2344 `message_transport_type` varchar(20) NOT NULL default '0',
2345 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2346 KEY `message_transport_type` (`message_transport_type`),
2347 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,
2348 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
2349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2352 -- Table structure for table `fieldmapping`
2355 DROP TABLE IF EXISTS `fieldmapping`;
2356 CREATE TABLE `fieldmapping` (
2357 `id` int(11) NOT NULL auto_increment,
2358 `field` varchar(255) NOT NULL,
2359 `frameworkcode` char(4) NOT NULL default '',
2360 `fieldcode` char(3) NOT NULL,
2361 `subfieldcode` char(1) NOT NULL,
2363 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2366 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2367 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2368 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2369 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2370 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2371 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2372 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2373 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;