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`)
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`),
422 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;
535 CREATE TABLE `branch_item_rules` (
536 `branchcode` varchar(10) NOT NULL,
537 `itemtype` varchar(10) NOT NULL,
538 `holdallowed` tinyint(1) default NULL,
539 PRIMARY KEY (`itemtype`,`branchcode`),
540 KEY `branch_item_rules_ibfk_2` (`branchcode`),
541 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
542 ON DELETE CASCADE ON UPDATE CASCADE,
543 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
544 ON DELETE CASCADE ON UPDATE CASCADE
545 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
548 -- Table structure for table `branchcategories`
551 DROP TABLE IF EXISTS `branchcategories`;
552 CREATE TABLE `branchcategories` (
553 `categorycode` varchar(10) NOT NULL default '',
554 `categoryname` varchar(32),
555 `codedescription` mediumtext,
556 `categorytype` varchar(16),
557 PRIMARY KEY (`categorycode`)
558 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
561 -- Table structure for table `branches`
564 DROP TABLE IF EXISTS `branches`;
565 CREATE TABLE `branches` (
566 `branchcode` varchar(10) NOT NULL default '',
567 `branchname` mediumtext NOT NULL,
568 `branchaddress1` mediumtext,
569 `branchaddress2` mediumtext,
570 `branchaddress3` mediumtext,
571 `branchphone` mediumtext,
572 `branchfax` mediumtext,
573 `branchemail` mediumtext,
574 `issuing` tinyint(4) default NULL,
575 `branchip` varchar(15) default NULL,
576 `branchprinter` varchar(100) default NULL,
577 UNIQUE KEY `branchcode` (`branchcode`)
578 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
581 -- Table structure for table `branchrelations`
584 DROP TABLE IF EXISTS `branchrelations`;
585 CREATE TABLE `branchrelations` (
586 `branchcode` varchar(10) NOT NULL default '',
587 `categorycode` varchar(10) NOT NULL default '',
588 PRIMARY KEY (`branchcode`,`categorycode`),
589 KEY `branchcode` (`branchcode`),
590 KEY `categorycode` (`categorycode`),
591 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
592 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
593 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
596 -- Table structure for table `branchtransfers`
599 DROP TABLE IF EXISTS `branchtransfers`;
600 CREATE TABLE `branchtransfers` (
601 `itemnumber` int(11) NOT NULL default 0,
602 `datesent` datetime default NULL,
603 `frombranch` varchar(10) NOT NULL default '',
604 `datearrived` datetime default NULL,
605 `tobranch` varchar(10) NOT NULL default '',
606 `comments` mediumtext,
607 KEY `frombranch` (`frombranch`),
608 KEY `tobranch` (`tobranch`),
609 KEY `itemnumber` (`itemnumber`),
610 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
611 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
612 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
617 -- Table structure for table `browser`
619 DROP TABLE IF EXISTS `browser`;
620 CREATE TABLE `browser` (
621 `level` int(11) NOT NULL,
622 `classification` varchar(20) NOT NULL,
623 `description` varchar(255) NOT NULL,
624 `number` bigint(20) NOT NULL,
625 `endnode` tinyint(4) NOT NULL
626 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
629 -- Table structure for table `categories`
632 DROP TABLE IF EXISTS `categories`;
633 CREATE TABLE `categories` (
634 `categorycode` varchar(10) NOT NULL default '',
635 `description` mediumtext,
636 `enrolmentperiod` smallint(6) default NULL,
637 `upperagelimit` smallint(6) default NULL,
638 `dateofbirthrequired` tinyint(1) default NULL,
639 `finetype` varchar(30) default NULL,
640 `bulk` tinyint(1) default NULL,
641 `enrolmentfee` decimal(28,6) default NULL,
642 `overduenoticerequired` tinyint(1) default NULL,
643 `issuelimit` smallint(6) default NULL,
644 `reservefee` decimal(28,6) default NULL,
645 `category_type` varchar(1) NOT NULL default 'A',
646 PRIMARY KEY (`categorycode`),
647 UNIQUE KEY `categorycode` (`categorycode`)
648 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
651 -- Table structure for table `borrower_branch_circ_rules`
654 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
655 CREATE TABLE `branch_borrower_circ_rules` (
656 `branchcode` VARCHAR(10) NOT NULL,
657 `categorycode` VARCHAR(10) NOT NULL,
658 `maxissueqty` int(4) default NULL,
659 PRIMARY KEY (`categorycode`, `branchcode`),
660 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
661 ON DELETE CASCADE ON UPDATE CASCADE,
662 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
663 ON DELETE CASCADE ON UPDATE CASCADE
664 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
667 -- Table structure for table `default_borrower_circ_rules`
670 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
671 CREATE TABLE `default_borrower_circ_rules` (
672 `categorycode` VARCHAR(10) NOT NULL,
673 `maxissueqty` int(4) default NULL,
674 PRIMARY KEY (`categorycode`),
675 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
676 ON DELETE CASCADE ON UPDATE CASCADE
677 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
680 -- Table structure for table `default_branch_circ_rules`
683 DROP TABLE IF EXISTS `default_branch_circ_rules`;
684 CREATE TABLE `default_branch_circ_rules` (
685 `branchcode` VARCHAR(10) NOT NULL,
686 `maxissueqty` int(4) default NULL,
687 `holdallowed` tinyint(1) default NULL,
688 PRIMARY KEY (`branchcode`),
689 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
690 ON DELETE CASCADE ON UPDATE CASCADE
691 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
694 -- Table structure for table `default_branch_item_rules`
697 CREATE TABLE `default_branch_item_rules` (
698 `itemtype` varchar(10) NOT NULL,
699 `holdallowed` tinyint(1) default NULL,
700 PRIMARY KEY (`itemtype`),
701 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
702 ON DELETE CASCADE ON UPDATE CASCADE
703 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
706 -- Table structure for table `default_circ_rules`
709 DROP TABLE IF EXISTS `default_circ_rules`;
710 CREATE TABLE `default_circ_rules` (
711 `singleton` enum('singleton') NOT NULL default 'singleton',
712 `maxissueqty` int(4) default NULL,
713 `holdallowed` int(1) default NULL,
714 PRIMARY KEY (`singleton`)
715 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
718 -- Table structure for table `cities`
721 DROP TABLE IF EXISTS `cities`;
722 CREATE TABLE `cities` (
723 `cityid` int(11) NOT NULL auto_increment,
724 `city_name` varchar(100) NOT NULL default '',
725 `city_zipcode` varchar(20) default NULL,
726 PRIMARY KEY (`cityid`)
727 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
730 -- Table structure for table `class_sort_rules`
733 DROP TABLE IF EXISTS `class_sort_rules`;
734 CREATE TABLE `class_sort_rules` (
735 `class_sort_rule` varchar(10) NOT NULL default '',
736 `description` mediumtext,
737 `sort_routine` varchar(30) NOT NULL default '',
738 PRIMARY KEY (`class_sort_rule`),
739 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
740 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
743 -- Table structure for table `class_sources`
746 DROP TABLE IF EXISTS `class_sources`;
747 CREATE TABLE `class_sources` (
748 `cn_source` varchar(10) NOT NULL default '',
749 `description` mediumtext,
750 `used` tinyint(4) NOT NULL default 0,
751 `class_sort_rule` varchar(10) NOT NULL default '',
752 PRIMARY KEY (`cn_source`),
753 UNIQUE KEY `cn_source_idx` (`cn_source`),
754 KEY `used_idx` (`used`),
755 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
756 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
759 -- Table structure for table `currency`
762 DROP TABLE IF EXISTS `currency`;
763 CREATE TABLE `currency` (
764 `currency` varchar(10) NOT NULL default '',
765 `symbol` varchar(5) default NULL,
766 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
767 `rate` float(7,5) default NULL,
768 PRIMARY KEY (`currency`)
769 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
772 -- Table structure for table `deletedbiblio`
775 DROP TABLE IF EXISTS `deletedbiblio`;
776 CREATE TABLE `deletedbiblio` (
777 `biblionumber` int(11) NOT NULL default 0,
778 `frameworkcode` varchar(4) NOT NULL default '',
781 `unititle` mediumtext,
783 `serial` tinyint(1) default NULL,
784 `seriestitle` mediumtext,
785 `copyrightdate` smallint(6) default NULL,
786 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
787 `datecreated` DATE NOT NULL,
788 `abstract` mediumtext,
789 PRIMARY KEY (`biblionumber`),
790 KEY `blbnoidx` (`biblionumber`)
791 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
794 -- Table structure for table `deletedbiblioitems`
797 DROP TABLE IF EXISTS `deletedbiblioitems`;
798 CREATE TABLE `deletedbiblioitems` (
799 `biblioitemnumber` int(11) NOT NULL default 0,
800 `biblionumber` int(11) NOT NULL default 0,
803 `itemtype` varchar(10) default NULL,
804 `isbn` varchar(30) default NULL,
805 `issn` varchar(9) default NULL,
806 `publicationyear` text,
807 `publishercode` varchar(255) default NULL,
808 `volumedate` date default NULL,
810 `collectiontitle` mediumtext default NULL,
811 `collectionissn` text default NULL,
812 `collectionvolume` mediumtext default NULL,
813 `editionstatement` text default NULL,
814 `editionresponsibility` text default NULL,
815 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
816 `illus` varchar(255) default NULL,
817 `pages` varchar(255) default NULL,
819 `size` varchar(255) default NULL,
820 `place` varchar(255) default NULL,
821 `lccn` varchar(25) default NULL,
823 `url` varchar(255) default NULL,
824 `cn_source` varchar(10) default NULL,
825 `cn_class` varchar(30) default NULL,
826 `cn_item` varchar(10) default NULL,
827 `cn_suffix` varchar(10) default NULL,
828 `cn_sort` varchar(30) default NULL,
829 `totalissues` int(10),
830 `marcxml` longtext NOT NULL,
831 PRIMARY KEY (`biblioitemnumber`),
832 KEY `bibinoidx` (`biblioitemnumber`),
833 KEY `bibnoidx` (`biblionumber`),
835 KEY `publishercode` (`publishercode`)
836 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
839 -- Table structure for table `deletedborrowers`
842 DROP TABLE IF EXISTS `deletedborrowers`;
843 CREATE TABLE `deletedborrowers` (
844 `borrowernumber` int(11) NOT NULL default 0,
845 `cardnumber` varchar(9) NOT NULL default '',
846 `surname` mediumtext NOT NULL,
849 `othernames` mediumtext,
851 `streetnumber` varchar(10) default NULL,
852 `streettype` varchar(50) default NULL,
853 `address` mediumtext NOT NULL,
855 `city` mediumtext NOT NULL,
856 `zipcode` varchar(25) default NULL,
859 `mobile` varchar(50) default NULL,
863 `B_streetnumber` varchar(10) default NULL,
864 `B_streettype` varchar(50) default NULL,
865 `B_address` varchar(100) default NULL,
867 `B_zipcode` varchar(25) default NULL,
869 `B_phone` mediumtext,
870 `dateofbirth` date default NULL,
871 `branchcode` varchar(10) NOT NULL default '',
872 `categorycode` varchar(10) default NULL,
873 `dateenrolled` date default NULL,
874 `dateexpiry` date default NULL,
875 `gonenoaddress` tinyint(1) default NULL,
876 `lost` tinyint(1) default NULL,
877 `debarred` tinyint(1) default NULL,
878 `contactname` mediumtext,
879 `contactfirstname` text,
881 `guarantorid` int(11) default NULL,
882 `borrowernotes` mediumtext,
883 `relationship` varchar(100) default NULL,
884 `ethnicity` varchar(50) default NULL,
885 `ethnotes` varchar(255) default NULL,
886 `sex` varchar(1) default NULL,
887 `password` varchar(30) default NULL,
888 `flags` int(11) default NULL,
889 `userid` varchar(30) default NULL,
890 `opacnote` mediumtext,
891 `contactnote` varchar(255) default NULL,
892 `sort1` varchar(80) default NULL,
893 `sort2` varchar(80) default NULL,
894 `altcontactfirstname` varchar(255) default NULL,
895 `altcontactsurname` varchar(255) default NULL,
896 `altcontactaddress1` varchar(255) default NULL,
897 `altcontactaddress2` varchar(255) default NULL,
898 `altcontactaddress3` varchar(255) default NULL,
899 `altcontactzipcode` varchar(50) default NULL,
900 `altcontactphone` varchar(50) default NULL,
901 `smsalertnumber` varchar(50) default NULL,
902 KEY `borrowernumber` (`borrowernumber`),
903 KEY `cardnumber` (`cardnumber`)
904 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
907 -- Table structure for table `deleteditems`
910 DROP TABLE IF EXISTS `deleteditems`;
911 CREATE TABLE `deleteditems` (
912 `itemnumber` int(11) NOT NULL default 0,
913 `biblionumber` int(11) NOT NULL default 0,
914 `biblioitemnumber` int(11) NOT NULL default 0,
915 `barcode` varchar(20) default NULL,
916 `dateaccessioned` date default NULL,
917 `booksellerid` mediumtext default NULL,
918 `homebranch` varchar(10) default NULL,
919 `price` decimal(8,2) default NULL,
920 `replacementprice` decimal(8,2) default NULL,
921 `replacementpricedate` date default NULL,
922 `datelastborrowed` date default NULL,
923 `datelastseen` date default NULL,
924 `stack` tinyint(1) default NULL,
925 `notforloan` tinyint(1) NOT NULL default 0,
926 `damaged` tinyint(1) NOT NULL default 0,
927 `itemlost` tinyint(1) NOT NULL default 0,
928 `wthdrawn` tinyint(1) NOT NULL default 0,
929 `itemcallnumber` varchar(30) default NULL,
930 `issues` smallint(6) default NULL,
931 `renewals` smallint(6) default NULL,
932 `reserves` smallint(6) default NULL,
933 `restricted` tinyint(1) default NULL,
934 `itemnotes` mediumtext,
935 `holdingbranch` varchar(10) default NULL,
936 `paidfor` mediumtext,
937 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
938 `location` varchar(80) default NULL,
939 `onloan` date default NULL,
940 `cn_source` varchar(10) default NULL,
941 `cn_sort` varchar(30) default NULL,
942 `ccode` varchar(10) default NULL,
943 `materials` varchar(10) default NULL,
944 `uri` varchar(255) default NULL,
945 `itype` varchar(10) default NULL,
946 `more_subfields_xml` longtext default NULL,
947 `enumchron` varchar(80) default NULL,
948 `copynumber` varchar(32) default NULL,
950 PRIMARY KEY (`itemnumber`),
951 KEY `delitembarcodeidx` (`barcode`),
952 KEY `delitembinoidx` (`biblioitemnumber`),
953 KEY `delitembibnoidx` (`biblionumber`),
954 KEY `delhomebranch` (`homebranch`),
955 KEY `delholdingbranch` (`holdingbranch`)
956 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
959 -- Table structure for table `ethnicity`
962 DROP TABLE IF EXISTS `ethnicity`;
963 CREATE TABLE `ethnicity` (
964 `code` varchar(10) NOT NULL default '',
965 `name` varchar(255) default NULL,
967 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
970 -- Table structure for table `hold_fill_targets`
973 DROP TABLE IF EXISTS `hold_fill_targets`;
974 CREATE TABLE hold_fill_targets (
975 `borrowernumber` int(11) NOT NULL,
976 `biblionumber` int(11) NOT NULL,
977 `itemnumber` int(11) NOT NULL,
978 `source_branchcode` varchar(10) default NULL,
979 `item_level_request` tinyint(4) NOT NULL default 0,
980 PRIMARY KEY `itemnumber` (`itemnumber`),
981 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
982 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
983 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
984 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
985 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
986 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
987 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
988 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
989 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
990 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
993 -- Table structure for table `import_batches`
996 DROP TABLE IF EXISTS `import_batches`;
997 CREATE TABLE `import_batches` (
998 `import_batch_id` int(11) NOT NULL auto_increment,
999 `matcher_id` int(11) default NULL,
1000 `template_id` int(11) default NULL,
1001 `branchcode` varchar(10) default NULL,
1002 `num_biblios` int(11) NOT NULL default 0,
1003 `num_items` int(11) NOT NULL default 0,
1004 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1005 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1006 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1007 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1008 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1009 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1010 `file_name` varchar(100),
1011 `comments` mediumtext,
1012 PRIMARY KEY (`import_batch_id`),
1013 KEY `branchcode` (`branchcode`)
1014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1017 -- Table structure for table `import_records`
1020 DROP TABLE IF EXISTS `import_records`;
1021 CREATE TABLE `import_records` (
1022 `import_record_id` int(11) NOT NULL auto_increment,
1023 `import_batch_id` int(11) NOT NULL,
1024 `branchcode` varchar(10) default NULL,
1025 `record_sequence` int(11) NOT NULL default 0,
1026 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1027 `import_date` DATE default NULL,
1028 `marc` longblob NOT NULL,
1029 `marcxml` longtext NOT NULL,
1030 `marcxml_old` longtext NOT NULL,
1031 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1032 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1033 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1034 `import_error` mediumtext,
1035 `encoding` varchar(40) NOT NULL default '',
1036 `z3950random` varchar(40) default NULL,
1037 PRIMARY KEY (`import_record_id`),
1038 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1039 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1040 KEY `branchcode` (`branchcode`),
1041 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1042 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1045 -- Table structure for `import_record_matches`
1047 DROP TABLE IF EXISTS `import_record_matches`;
1048 CREATE TABLE `import_record_matches` (
1049 `import_record_id` int(11) NOT NULL,
1050 `candidate_match_id` int(11) NOT NULL,
1051 `score` int(11) NOT NULL default 0,
1052 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1053 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1054 KEY `record_score` (`import_record_id`, `score`)
1055 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1058 -- Table structure for table `import_biblios`
1061 DROP TABLE IF EXISTS `import_biblios`;
1062 CREATE TABLE `import_biblios` (
1063 `import_record_id` int(11) NOT NULL,
1064 `matched_biblionumber` int(11) default NULL,
1065 `control_number` varchar(25) default NULL,
1066 `original_source` varchar(25) default NULL,
1067 `title` varchar(128) default NULL,
1068 `author` varchar(80) default NULL,
1069 `isbn` varchar(30) default NULL,
1070 `issn` varchar(9) default NULL,
1071 `has_items` tinyint(1) NOT NULL default 0,
1072 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1073 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1074 KEY `matched_biblionumber` (`matched_biblionumber`),
1075 KEY `title` (`title`),
1077 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1080 -- Table structure for table `import_items`
1083 DROP TABLE IF EXISTS `import_items`;
1084 CREATE TABLE `import_items` (
1085 `import_items_id` int(11) NOT NULL auto_increment,
1086 `import_record_id` int(11) NOT NULL,
1087 `itemnumber` int(11) default NULL,
1088 `branchcode` varchar(10) default NULL,
1089 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1090 `marcxml` longtext NOT NULL,
1091 `import_error` mediumtext,
1092 PRIMARY KEY (`import_items_id`),
1093 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1094 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1095 KEY `itemnumber` (`itemnumber`),
1096 KEY `branchcode` (`branchcode`)
1097 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1100 -- Table structure for table `issues`
1103 DROP TABLE IF EXISTS `issues`;
1104 CREATE TABLE `issues` (
1105 `borrowernumber` int(11) default NULL,
1106 `itemnumber` int(11) default NULL,
1107 `date_due` date default NULL,
1108 `branchcode` varchar(10) default NULL,
1109 `issuingbranch` varchar(18) default NULL,
1110 `returndate` date default NULL,
1111 `lastreneweddate` date default NULL,
1112 `return` varchar(4) default NULL,
1113 `renewals` tinyint(4) default NULL,
1114 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1115 `issuedate` date default NULL,
1116 KEY `issuesborridx` (`borrowernumber`),
1117 KEY `issuesitemidx` (`itemnumber`),
1118 KEY `bordate` (`borrowernumber`,`timestamp`),
1119 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1120 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1124 -- Table structure for table `issuingrules`
1127 DROP TABLE IF EXISTS `issuingrules`;
1128 CREATE TABLE `issuingrules` (
1129 `categorycode` varchar(10) NOT NULL default '',
1130 `itemtype` varchar(10) NOT NULL default '',
1131 `restrictedtype` tinyint(1) default NULL,
1132 `rentaldiscount` decimal(28,6) default NULL,
1133 `reservecharge` decimal(28,6) default NULL,
1134 `fine` decimal(28,6) default NULL,
1135 `firstremind` int(11) default NULL,
1136 `chargeperiod` int(11) default NULL,
1137 `accountsent` int(11) default NULL,
1138 `chargename` varchar(100) default NULL,
1139 `maxissueqty` int(4) default NULL,
1140 `issuelength` int(4) default NULL,
1141 `branchcode` varchar(10) NOT NULL default '',
1142 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1143 KEY `categorycode` (`categorycode`),
1144 KEY `itemtype` (`itemtype`)
1145 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1148 -- Table structure for table `items`
1151 DROP TABLE IF EXISTS `items`;
1152 CREATE TABLE `items` (
1153 `itemnumber` int(11) NOT NULL auto_increment,
1154 `biblionumber` int(11) NOT NULL default 0,
1155 `biblioitemnumber` int(11) NOT NULL default 0,
1156 `barcode` varchar(20) default NULL,
1157 `dateaccessioned` date default NULL,
1158 `booksellerid` mediumtext default NULL,
1159 `homebranch` varchar(10) default NULL,
1160 `price` decimal(8,2) default NULL,
1161 `replacementprice` decimal(8,2) default NULL,
1162 `replacementpricedate` date default NULL,
1163 `datelastborrowed` date default NULL,
1164 `datelastseen` date default NULL,
1165 `stack` tinyint(1) default NULL,
1166 `notforloan` tinyint(1) NOT NULL default 0,
1167 `damaged` tinyint(1) NOT NULL default 0,
1168 `itemlost` tinyint(1) NOT NULL default 0,
1169 `wthdrawn` tinyint(1) NOT NULL default 0,
1170 `itemcallnumber` varchar(30) default NULL,
1171 `issues` smallint(6) default NULL,
1172 `renewals` smallint(6) default NULL,
1173 `reserves` smallint(6) default NULL,
1174 `restricted` tinyint(1) default NULL,
1175 `itemnotes` mediumtext,
1176 `holdingbranch` varchar(10) default NULL,
1177 `paidfor` mediumtext,
1178 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1179 `location` varchar(80) default NULL,
1180 `onloan` date default NULL,
1181 `cn_source` varchar(10) default NULL,
1182 `cn_sort` varchar(30) default NULL,
1183 `ccode` varchar(10) default NULL,
1184 `materials` varchar(10) default NULL,
1185 `uri` varchar(255) default NULL,
1186 `itype` varchar(10) default NULL,
1187 `more_subfields_xml` longtext default NULL,
1188 `enumchron` varchar(80) default NULL,
1189 `copynumber` varchar(32) default NULL,
1190 PRIMARY KEY (`itemnumber`),
1191 UNIQUE KEY `itembarcodeidx` (`barcode`),
1192 KEY `itembinoidx` (`biblioitemnumber`),
1193 KEY `itembibnoidx` (`biblionumber`),
1194 KEY `homebranch` (`homebranch`),
1195 KEY `holdingbranch` (`holdingbranch`),
1196 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1197 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1198 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1202 -- Table structure for table `itemtypes`
1205 DROP TABLE IF EXISTS `itemtypes`;
1206 CREATE TABLE `itemtypes` (
1207 `itemtype` varchar(10) NOT NULL default '',
1208 `description` mediumtext,
1209 `renewalsallowed` smallint(6) default NULL,
1210 `rentalcharge` double(16,4) default NULL,
1211 `notforloan` smallint(6) default NULL,
1212 `imageurl` varchar(200) default NULL,
1214 PRIMARY KEY (`itemtype`),
1215 UNIQUE KEY `itemtype` (`itemtype`)
1216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1219 -- Table structure for table `labels`
1222 DROP TABLE IF EXISTS `labels`;
1223 CREATE TABLE `labels` (
1224 `labelid` int(11) NOT NULL auto_increment,
1225 `batch_id` int(10) NOT NULL default 1,
1226 `itemnumber` varchar(100) NOT NULL default '',
1227 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1228 PRIMARY KEY (`labelid`)
1229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1232 -- Table structure for table `labels_conf`
1235 DROP TABLE IF EXISTS `labels_conf`;
1236 CREATE TABLE `labels_conf` (
1237 `id` int(4) NOT NULL auto_increment,
1238 `barcodetype` char(100) default '',
1239 `title` int(1) default '0',
1240 `subtitle` int(1) default '0',
1241 `itemtype` int(1) default '0',
1242 `barcode` int(1) default '0',
1243 `dewey` int(1) default '0',
1244 `classification` int(1) default NULL,
1245 `subclass` int(1) default '0',
1246 `itemcallnumber` int(1) default '0',
1247 `author` int(1) default '0',
1248 `issn` int(1) default '0',
1249 `isbn` int(1) default '0',
1250 `startlabel` int(2) NOT NULL default '1',
1251 `printingtype` char(32) default 'BAR',
1252 `formatstring` mediumtext default NULL,
1253 `layoutname` char(20) NOT NULL default 'TEST',
1254 `guidebox` int(1) default '0',
1255 `active` tinyint(1) default '1',
1256 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1257 `ccode` char(4) collate utf8_unicode_ci default NULL,
1258 `callnum_split` int(1) default NULL,
1259 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1261 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1264 -- Table structure for table `labels_profile`
1267 DROP TABLE IF EXISTS `labels_profile`;
1268 CREATE TABLE `labels_profile` (
1269 `tmpl_id` int(4) NOT NULL,
1270 `prof_id` int(4) NOT NULL,
1271 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1272 UNIQUE KEY `prof_id` (`prof_id`)
1273 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1276 -- Table structure for table `labels_templates`
1279 DROP TABLE IF EXISTS `labels_templates`;
1280 CREATE TABLE `labels_templates` (
1281 `tmpl_id` int(4) NOT NULL auto_increment,
1282 `tmpl_code` char(100) default '',
1283 `tmpl_desc` char(100) default '',
1284 `page_width` float default '0',
1285 `page_height` float default '0',
1286 `label_width` float default '0',
1287 `label_height` float default '0',
1288 `topmargin` float default '0',
1289 `leftmargin` float default '0',
1290 `cols` int(2) default '0',
1291 `rows` int(2) default '0',
1292 `colgap` float default '0',
1293 `rowgap` float default '0',
1294 `active` int(1) default NULL,
1295 `units` char(20) default 'PX',
1296 `fontsize` int(4) NOT NULL default '3',
1297 `font` char(10) NOT NULL default 'TR',
1298 PRIMARY KEY (`tmpl_id`)
1299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1302 -- Table structure for table `letter`
1305 DROP TABLE IF EXISTS `letter`;
1306 CREATE TABLE `letter` (
1307 `module` varchar(20) NOT NULL default '',
1308 `code` varchar(20) NOT NULL default '',
1309 `name` varchar(100) NOT NULL default '',
1310 `title` varchar(200) NOT NULL default '',
1312 PRIMARY KEY (`module`,`code`)
1313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1316 -- Table structure for table `marc_subfield_structure`
1319 DROP TABLE IF EXISTS `marc_subfield_structure`;
1320 CREATE TABLE `marc_subfield_structure` (
1321 `tagfield` varchar(3) NOT NULL default '',
1322 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1323 `liblibrarian` varchar(255) NOT NULL default '',
1324 `libopac` varchar(255) NOT NULL default '',
1325 `repeatable` tinyint(4) NOT NULL default 0,
1326 `mandatory` tinyint(4) NOT NULL default 0,
1327 `kohafield` varchar(40) default NULL,
1328 `tab` tinyint(1) default NULL,
1329 `authorised_value` varchar(20) default NULL,
1330 `authtypecode` varchar(20) default NULL,
1331 `value_builder` varchar(80) default NULL,
1332 `isurl` tinyint(1) default NULL,
1333 `hidden` tinyint(1) default NULL,
1334 `frameworkcode` varchar(4) NOT NULL default '',
1335 `seealso` varchar(1100) default NULL,
1336 `link` varchar(80) default NULL,
1337 `defaultvalue` text default NULL,
1338 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1339 KEY `kohafield_2` (`kohafield`),
1340 KEY `tab` (`frameworkcode`,`tab`),
1341 KEY `kohafield` (`frameworkcode`,`kohafield`)
1342 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1345 -- Table structure for table `marc_tag_structure`
1348 DROP TABLE IF EXISTS `marc_tag_structure`;
1349 CREATE TABLE `marc_tag_structure` (
1350 `tagfield` varchar(3) NOT NULL default '',
1351 `liblibrarian` varchar(255) NOT NULL default '',
1352 `libopac` varchar(255) NOT NULL default '',
1353 `repeatable` tinyint(4) NOT NULL default 0,
1354 `mandatory` tinyint(4) NOT NULL default 0,
1355 `authorised_value` varchar(10) default NULL,
1356 `frameworkcode` varchar(4) NOT NULL default '',
1357 PRIMARY KEY (`frameworkcode`,`tagfield`)
1358 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1361 -- Table structure for table `marc_matchers`
1364 DROP TABLE IF EXISTS `marc_matchers`;
1365 CREATE TABLE `marc_matchers` (
1366 `matcher_id` int(11) NOT NULL auto_increment,
1367 `code` varchar(10) NOT NULL default '',
1368 `description` varchar(255) NOT NULL default '',
1369 `record_type` varchar(10) NOT NULL default 'biblio',
1370 `threshold` int(11) NOT NULL default 0,
1371 PRIMARY KEY (`matcher_id`),
1372 KEY `code` (`code`),
1373 KEY `record_type` (`record_type`)
1374 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1377 -- Table structure for table `matchpoints`
1379 DROP TABLE IF EXISTS `matchpoints`;
1380 CREATE TABLE `matchpoints` (
1381 `matcher_id` int(11) NOT NULL,
1382 `matchpoint_id` int(11) NOT NULL auto_increment,
1383 `search_index` varchar(30) NOT NULL default '',
1384 `score` int(11) NOT NULL default 0,
1385 PRIMARY KEY (`matchpoint_id`),
1386 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1387 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1388 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1392 -- Table structure for table `matchpoint_components`
1394 DROP TABLE IF EXISTS `matchpoint_components`;
1395 CREATE TABLE `matchpoint_components` (
1396 `matchpoint_id` int(11) NOT NULL,
1397 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1398 sequence int(11) NOT NULL default 0,
1399 tag varchar(3) NOT NULL default '',
1400 subfields varchar(40) NOT NULL default '',
1401 offset int(4) NOT NULL default 0,
1402 length int(4) NOT NULL default 0,
1403 PRIMARY KEY (`matchpoint_component_id`),
1404 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1405 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1406 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1407 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1410 -- Table structure for table `matcher_component_norms`
1412 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1413 CREATE TABLE `matchpoint_component_norms` (
1414 `matchpoint_component_id` int(11) NOT NULL,
1415 `sequence` int(11) NOT NULL default 0,
1416 `norm_routine` varchar(50) NOT NULL default '',
1417 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1418 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1419 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1420 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1423 -- Table structure for table `matcher_matchpoints`
1425 DROP TABLE IF EXISTS `matcher_matchpoints`;
1426 CREATE TABLE `matcher_matchpoints` (
1427 `matcher_id` int(11) NOT NULL,
1428 `matchpoint_id` int(11) NOT NULL,
1429 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1430 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1431 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1432 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1433 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1436 -- Table structure for table `matchchecks`
1438 DROP TABLE IF EXISTS `matchchecks`;
1439 CREATE TABLE `matchchecks` (
1440 `matcher_id` int(11) NOT NULL,
1441 `matchcheck_id` int(11) NOT NULL auto_increment,
1442 `source_matchpoint_id` int(11) NOT NULL,
1443 `target_matchpoint_id` int(11) NOT NULL,
1444 PRIMARY KEY (`matchcheck_id`),
1445 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1446 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1447 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1448 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1449 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1450 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1451 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1454 -- Table structure for table `notifys`
1457 DROP TABLE IF EXISTS `notifys`;
1458 CREATE TABLE `notifys` (
1459 `notify_id` int(11) NOT NULL default 0,
1460 `borrowernumber` int(11) NOT NULL default 0,
1461 `itemnumber` int(11) NOT NULL default 0,
1462 `notify_date` date default NULL,
1463 `notify_send_date` date default NULL,
1464 `notify_level` int(1) NOT NULL default 0,
1465 `method` varchar(20) NOT NULL default ''
1466 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1469 -- Table structure for table `nozebra`
1472 DROP TABLE IF EXISTS `nozebra`;
1473 CREATE TABLE `nozebra` (
1474 `server` varchar(20) NOT NULL,
1475 `indexname` varchar(40) NOT NULL,
1476 `value` varchar(250) NOT NULL,
1477 `biblionumbers` longtext NOT NULL,
1478 KEY `indexname` (`server`,`indexname`),
1479 KEY `value` (`server`,`value`))
1480 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1483 -- Table structure for table `old_issues`
1486 DROP TABLE IF EXISTS `old_issues`;
1487 CREATE TABLE `old_issues` (
1488 `borrowernumber` int(11) default NULL,
1489 `itemnumber` int(11) default NULL,
1490 `date_due` date default NULL,
1491 `branchcode` varchar(10) default NULL,
1492 `issuingbranch` varchar(18) default NULL,
1493 `returndate` date default NULL,
1494 `lastreneweddate` date default NULL,
1495 `return` varchar(4) default NULL,
1496 `renewals` tinyint(4) default NULL,
1497 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1498 `issuedate` date default NULL,
1499 KEY `old_issuesborridx` (`borrowernumber`),
1500 KEY `old_issuesitemidx` (`itemnumber`),
1501 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1502 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1503 ON DELETE SET NULL ON UPDATE SET NULL,
1504 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1505 ON DELETE SET NULL ON UPDATE SET NULL
1506 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1509 -- Table structure for table `old_reserves`
1511 DROP TABLE IF EXISTS `old_reserves`;
1512 CREATE TABLE `old_reserves` (
1513 `borrowernumber` int(11) default NULL,
1514 `reservedate` date default NULL,
1515 `biblionumber` int(11) default NULL,
1516 `constrainttype` varchar(1) default NULL,
1517 `branchcode` varchar(10) default NULL,
1518 `notificationdate` date default NULL,
1519 `reminderdate` date default NULL,
1520 `cancellationdate` date default NULL,
1521 `reservenotes` mediumtext,
1522 `priority` smallint(6) default NULL,
1523 `found` varchar(1) default NULL,
1524 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1525 `itemnumber` int(11) default NULL,
1526 `waitingdate` date default NULL,
1527 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1528 KEY `old_reserves_biblionumber` (`biblionumber`),
1529 KEY `old_reserves_itemnumber` (`itemnumber`),
1530 KEY `old_reserves_branchcode` (`branchcode`),
1531 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1532 ON DELETE SET NULL ON UPDATE SET NULL,
1533 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1534 ON DELETE SET NULL ON UPDATE SET NULL,
1535 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1536 ON DELETE SET NULL ON UPDATE SET NULL
1537 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1540 -- Table structure for table `opac_news`
1543 DROP TABLE IF EXISTS `opac_news`;
1544 CREATE TABLE `opac_news` (
1545 `idnew` int(10) unsigned NOT NULL auto_increment,
1546 `title` varchar(250) NOT NULL default '',
1547 `new` text NOT NULL,
1548 `lang` varchar(25) NOT NULL default '',
1549 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1550 `expirationdate` date default NULL,
1551 `number` int(11) default NULL,
1552 PRIMARY KEY (`idnew`)
1553 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1556 -- Table structure for table `overduerules`
1559 DROP TABLE IF EXISTS `overduerules`;
1560 CREATE TABLE `overduerules` (
1561 `branchcode` varchar(10) NOT NULL default '',
1562 `categorycode` varchar(10) NOT NULL default '',
1563 `delay1` int(4) default 0,
1564 `letter1` varchar(20) default NULL,
1565 `debarred1` varchar(1) default 0,
1566 `delay2` int(4) default 0,
1567 `debarred2` varchar(1) default 0,
1568 `letter2` varchar(20) default NULL,
1569 `delay3` int(4) default 0,
1570 `letter3` varchar(20) default NULL,
1571 `debarred3` int(1) default 0,
1572 PRIMARY KEY (`branchcode`,`categorycode`)
1573 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1576 -- Table structure for table `patroncards`
1579 DROP TABLE IF EXISTS `patroncards`;
1580 CREATE TABLE `patroncards` (
1581 `cardid` int(11) NOT NULL auto_increment,
1582 `batch_id` varchar(10) NOT NULL default '1',
1583 `borrowernumber` int(11) NOT NULL,
1584 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1585 PRIMARY KEY (`cardid`),
1586 KEY `patroncards_ibfk_1` (`borrowernumber`),
1587 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1588 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1591 -- Table structure for table `patronimage`
1594 DROP TABLE IF EXISTS `patronimage`;
1595 CREATE TABLE `patronimage` (
1596 `cardnumber` varchar(16) NOT NULL,
1597 `mimetype` varchar(15) NOT NULL,
1598 `imagefile` mediumblob NOT NULL,
1599 PRIMARY KEY (`cardnumber`),
1600 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1601 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1604 -- Table structure for table `printers`
1607 DROP TABLE IF EXISTS `printers`;
1608 CREATE TABLE `printers` (
1609 `printername` varchar(40) NOT NULL default '',
1610 `printqueue` varchar(20) default NULL,
1611 `printtype` varchar(20) default NULL,
1612 PRIMARY KEY (`printername`)
1613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1616 -- Table structure for table `printers_profile`
1619 DROP TABLE IF EXISTS `printers_profile`;
1620 CREATE TABLE `printers_profile` (
1621 `prof_id` int(4) NOT NULL auto_increment,
1622 `printername` varchar(40) NOT NULL,
1623 `tmpl_id` int(4) NOT NULL,
1624 `paper_bin` varchar(20) NOT NULL,
1625 `offset_horz` float default NULL,
1626 `offset_vert` float default NULL,
1627 `creep_horz` float default NULL,
1628 `creep_vert` float default NULL,
1629 `unit` char(20) NOT NULL default 'POINT',
1630 PRIMARY KEY (`prof_id`),
1631 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1632 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1633 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1636 -- Table structure for table `repeatable_holidays`
1639 DROP TABLE IF EXISTS `repeatable_holidays`;
1640 CREATE TABLE `repeatable_holidays` (
1641 `id` int(11) NOT NULL auto_increment,
1642 `branchcode` varchar(10) NOT NULL default '',
1643 `weekday` smallint(6) default NULL,
1644 `day` smallint(6) default NULL,
1645 `month` smallint(6) default NULL,
1646 `title` varchar(50) NOT NULL default '',
1647 `description` text NOT NULL,
1649 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1652 -- Table structure for table `reports_dictionary`
1655 DROP TABLE IF EXISTS `reports_dictionary`;
1656 CREATE TABLE reports_dictionary (
1657 `id` int(11) NOT NULL auto_increment,
1658 `name` varchar(255) default NULL,
1660 `date_created` datetime default NULL,
1661 `date_modified` datetime default NULL,
1663 `area` int(11) default NULL,
1665 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1668 -- Table structure for table `reserveconstraints`
1671 DROP TABLE IF EXISTS `reserveconstraints`;
1672 CREATE TABLE `reserveconstraints` (
1673 `borrowernumber` int(11) NOT NULL default 0,
1674 `reservedate` date default NULL,
1675 `biblionumber` int(11) NOT NULL default 0,
1676 `biblioitemnumber` int(11) default NULL,
1677 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1678 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1681 -- Table structure for table `reserves`
1684 DROP TABLE IF EXISTS `reserves`;
1685 CREATE TABLE `reserves` (
1686 `borrowernumber` int(11) NOT NULL default 0,
1687 `reservedate` date default NULL,
1688 `biblionumber` int(11) NOT NULL default 0,
1689 `constrainttype` varchar(1) default NULL,
1690 `branchcode` varchar(10) default NULL,
1691 `notificationdate` date default NULL,
1692 `reminderdate` date default NULL,
1693 `cancellationdate` date default NULL,
1694 `reservenotes` mediumtext,
1695 `priority` smallint(6) default NULL,
1696 `found` varchar(1) default NULL,
1697 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1698 `itemnumber` int(11) default NULL,
1699 `waitingdate` date default NULL,
1700 KEY `borrowernumber` (`borrowernumber`),
1701 KEY `biblionumber` (`biblionumber`),
1702 KEY `itemnumber` (`itemnumber`),
1703 KEY `branchcode` (`branchcode`),
1704 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1705 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1706 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1707 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1708 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1711 -- Table structure for table `reviews`
1714 DROP TABLE IF EXISTS `reviews`;
1715 CREATE TABLE `reviews` (
1716 `reviewid` int(11) NOT NULL auto_increment,
1717 `borrowernumber` int(11) default NULL,
1718 `biblionumber` int(11) default NULL,
1720 `approved` tinyint(4) default NULL,
1721 `datereviewed` datetime default NULL,
1722 PRIMARY KEY (`reviewid`)
1723 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1726 -- Table structure for table `roadtype`
1729 DROP TABLE IF EXISTS `roadtype`;
1730 CREATE TABLE `roadtype` (
1731 `roadtypeid` int(11) NOT NULL auto_increment,
1732 `road_type` varchar(100) NOT NULL default '',
1733 PRIMARY KEY (`roadtypeid`)
1734 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1737 -- Table structure for table `saved_sql`
1740 DROP TABLE IF EXISTS `saved_sql`;
1741 CREATE TABLE saved_sql (
1742 `id` int(11) NOT NULL auto_increment,
1743 `borrowernumber` int(11) default NULL,
1744 `date_created` datetime default NULL,
1745 `last_modified` datetime default NULL,
1747 `last_run` datetime default NULL,
1748 `report_name` varchar(255) default NULL,
1749 `type` varchar(255) default NULL,
1752 KEY boridx (`borrowernumber`)
1753 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1757 -- Table structure for `saved_reports`
1760 DROP TABLE IF EXISTS `saved_reports`;
1761 CREATE TABLE saved_reports (
1762 `id` int(11) NOT NULL auto_increment,
1763 `report_id` int(11) default NULL,
1765 `date_run` datetime default NULL,
1767 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1771 -- Table structure for table `serial`
1774 DROP TABLE IF EXISTS `serial`;
1775 CREATE TABLE `serial` (
1776 `serialid` int(11) NOT NULL auto_increment,
1777 `biblionumber` varchar(100) NOT NULL default '',
1778 `subscriptionid` varchar(100) NOT NULL default '',
1779 `serialseq` varchar(100) NOT NULL default '',
1780 `status` tinyint(4) NOT NULL default 0,
1781 `planneddate` date default NULL,
1783 `publisheddate` date default NULL,
1784 `itemnumber` text default NULL,
1785 `claimdate` date default NULL,
1786 `routingnotes` text,
1787 PRIMARY KEY (`serialid`)
1788 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1791 -- Table structure for table `sessions`
1794 DROP TABLE IF EXISTS sessions;
1795 CREATE TABLE sessions (
1796 `id` varchar(32) NOT NULL,
1797 `a_session` text NOT NULL,
1799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1802 -- Table structure for table `special_holidays`
1805 DROP TABLE IF EXISTS `special_holidays`;
1806 CREATE TABLE `special_holidays` (
1807 `id` int(11) NOT NULL auto_increment,
1808 `branchcode` varchar(10) NOT NULL default '',
1809 `day` smallint(6) NOT NULL default 0,
1810 `month` smallint(6) NOT NULL default 0,
1811 `year` smallint(6) NOT NULL default 0,
1812 `isexception` smallint(1) NOT NULL default 1,
1813 `title` varchar(50) NOT NULL default '',
1814 `description` text NOT NULL,
1816 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1819 -- Table structure for table `statistics`
1822 DROP TABLE IF EXISTS `statistics`;
1823 CREATE TABLE `statistics` (
1824 `datetime` datetime default NULL,
1825 `branch` varchar(10) default NULL,
1826 `proccode` varchar(4) default NULL,
1827 `value` double(16,4) default NULL,
1828 `type` varchar(16) default NULL,
1830 `usercode` varchar(10) default NULL,
1831 `itemnumber` int(11) default NULL,
1832 `itemtype` varchar(10) default NULL,
1833 `borrowernumber` int(11) default NULL,
1834 `associatedborrower` int(11) default NULL,
1835 KEY `timeidx` (`datetime`)
1836 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1839 -- Table structure for table `stopwords`
1842 DROP TABLE IF EXISTS `stopwords`;
1843 CREATE TABLE `stopwords` (
1844 `word` varchar(255) default NULL
1845 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1848 -- Table structure for table `subscription`
1851 DROP TABLE IF EXISTS `subscription`;
1852 CREATE TABLE `subscription` (
1853 `biblionumber` int(11) NOT NULL default 0,
1854 `subscriptionid` int(11) NOT NULL auto_increment,
1855 `librarian` varchar(100) default '',
1856 `startdate` date default NULL,
1857 `aqbooksellerid` int(11) default 0,
1858 `cost` int(11) default 0,
1859 `aqbudgetid` int(11) default 0,
1860 `weeklength` int(11) default 0,
1861 `monthlength` int(11) default 0,
1862 `numberlength` int(11) default 0,
1863 `periodicity` tinyint(4) default 0,
1864 `dow` varchar(100) default '',
1865 `numberingmethod` varchar(100) default '',
1867 `status` varchar(100) NOT NULL default '',
1868 `add1` int(11) default 0,
1869 `every1` int(11) default 0,
1870 `whenmorethan1` int(11) default 0,
1871 `setto1` int(11) default NULL,
1872 `lastvalue1` int(11) default NULL,
1873 `add2` int(11) default 0,
1874 `every2` int(11) default 0,
1875 `whenmorethan2` int(11) default 0,
1876 `setto2` int(11) default NULL,
1877 `lastvalue2` int(11) default NULL,
1878 `add3` int(11) default 0,
1879 `every3` int(11) default 0,
1880 `innerloop1` int(11) default 0,
1881 `innerloop2` int(11) default 0,
1882 `innerloop3` int(11) default 0,
1883 `whenmorethan3` int(11) default 0,
1884 `setto3` int(11) default NULL,
1885 `lastvalue3` int(11) default NULL,
1886 `issuesatonce` tinyint(3) NOT NULL default 1,
1887 `firstacquidate` date default NULL,
1888 `manualhistory` tinyint(1) NOT NULL default 0,
1889 `irregularity` text,
1890 `letter` varchar(20) default NULL,
1891 `numberpattern` tinyint(3) default 0,
1892 `distributedto` text,
1893 `internalnotes` longtext,
1895 `branchcode` varchar(10) NOT NULL default '',
1896 `hemisphere` tinyint(3) default 0,
1897 `lastbranch` varchar(10),
1898 `serialsadditems` tinyint(1) NOT NULL default '0',
1899 `staffdisplaycount` VARCHAR(10) NULL,
1900 `opacdisplaycount` VARCHAR(10) NULL,
1901 PRIMARY KEY (`subscriptionid`)
1902 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1905 -- Table structure for table `subscriptionhistory`
1908 DROP TABLE IF EXISTS `subscriptionhistory`;
1909 CREATE TABLE `subscriptionhistory` (
1910 `biblionumber` int(11) NOT NULL default 0,
1911 `subscriptionid` int(11) NOT NULL default 0,
1912 `histstartdate` date default NULL,
1913 `enddate` date default NULL,
1914 `missinglist` longtext NOT NULL,
1915 `recievedlist` longtext NOT NULL,
1916 `opacnote` varchar(150) NOT NULL default '',
1917 `librariannote` varchar(150) NOT NULL default '',
1918 PRIMARY KEY (`subscriptionid`),
1919 KEY `biblionumber` (`biblionumber`)
1920 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1923 -- Table structure for table `subscriptionroutinglist`
1926 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1927 CREATE TABLE `subscriptionroutinglist` (
1928 `routingid` int(11) NOT NULL auto_increment,
1929 `borrowernumber` int(11) default NULL,
1930 `ranking` int(11) default NULL,
1931 `subscriptionid` int(11) default NULL,
1932 PRIMARY KEY (`routingid`)
1933 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1936 -- Table structure for table `suggestions`
1939 DROP TABLE IF EXISTS `suggestions`;
1940 CREATE TABLE `suggestions` (
1941 `suggestionid` int(8) NOT NULL auto_increment,
1942 `suggestedby` int(11) NOT NULL default 0,
1943 `managedby` int(11) default NULL,
1944 `STATUS` varchar(10) NOT NULL default '',
1946 `author` varchar(80) default NULL,
1947 `title` varchar(80) default NULL,
1948 `copyrightdate` smallint(6) default NULL,
1949 `publishercode` varchar(255) default NULL,
1950 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1951 `volumedesc` varchar(255) default NULL,
1952 `publicationyear` smallint(6) default 0,
1953 `place` varchar(255) default NULL,
1954 `isbn` varchar(30) default NULL,
1955 `mailoverseeing` smallint(1) default 0,
1956 `biblionumber` int(11) default NULL,
1958 PRIMARY KEY (`suggestionid`),
1959 KEY `suggestedby` (`suggestedby`),
1960 KEY `managedby` (`managedby`)
1961 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1964 -- Table structure for table `systempreferences`
1967 DROP TABLE IF EXISTS `systempreferences`;
1968 CREATE TABLE `systempreferences` (
1969 `variable` varchar(50) NOT NULL default '',
1971 `options` mediumtext,
1973 `type` varchar(20) default NULL,
1974 PRIMARY KEY (`variable`)
1975 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1978 -- Table structure for table `tags`
1981 DROP TABLE IF EXISTS `tags`;
1982 CREATE TABLE `tags` (
1983 `entry` varchar(255) NOT NULL default '',
1984 `weight` bigint(20) NOT NULL default 0,
1985 PRIMARY KEY (`entry`)
1986 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1989 -- Table structure for table `tags_all`
1992 DROP TABLE IF EXISTS `tags_all`;
1993 CREATE TABLE `tags_all` (
1994 `tag_id` int(11) NOT NULL auto_increment,
1995 `borrowernumber` int(11) NOT NULL,
1996 `biblionumber` int(11) NOT NULL,
1997 `term` varchar(255) NOT NULL,
1998 `language` int(4) default NULL,
1999 `date_created` datetime NOT NULL,
2000 PRIMARY KEY (`tag_id`),
2001 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2002 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2003 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2004 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2005 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2006 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2007 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2010 -- Table structure for table `tags_approval`
2013 DROP TABLE IF EXISTS `tags_approval`;
2014 CREATE TABLE `tags_approval` (
2015 `term` varchar(255) NOT NULL,
2016 `approved` int(1) NOT NULL default '0',
2017 `date_approved` datetime default NULL,
2018 `approved_by` int(11) default NULL,
2019 `weight_total` int(9) NOT NULL default '1',
2020 PRIMARY KEY (`term`),
2021 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2022 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2023 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2024 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2027 -- Table structure for table `tags_index`
2030 DROP TABLE IF EXISTS `tags_index`;
2031 CREATE TABLE `tags_index` (
2032 `term` varchar(255) NOT NULL,
2033 `biblionumber` int(11) NOT NULL,
2034 `weight` int(9) NOT NULL default '1',
2035 PRIMARY KEY (`term`,`biblionumber`),
2036 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2037 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2038 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2039 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2040 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2041 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2044 -- Table structure for table `userflags`
2047 DROP TABLE IF EXISTS `userflags`;
2048 CREATE TABLE `userflags` (
2049 `bit` int(11) NOT NULL default 0,
2050 `flag` varchar(30) default NULL,
2051 `flagdesc` varchar(255) default NULL,
2052 `defaulton` int(11) default NULL,
2054 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2057 -- Table structure for table `virtualshelves`
2060 DROP TABLE IF EXISTS `virtualshelves`;
2061 CREATE TABLE `virtualshelves` (
2062 `shelfnumber` int(11) NOT NULL auto_increment,
2063 `shelfname` varchar(255) default NULL,
2064 `owner` varchar(80) default NULL,
2065 `category` varchar(1) default NULL,
2066 `sortfield` varchar(16) default NULL,
2067 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2068 PRIMARY KEY (`shelfnumber`)
2069 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2072 -- Table structure for table `virtualshelfcontents`
2075 DROP TABLE IF EXISTS `virtualshelfcontents`;
2076 CREATE TABLE `virtualshelfcontents` (
2077 `shelfnumber` int(11) NOT NULL default 0,
2078 `biblionumber` int(11) NOT NULL default 0,
2079 `flags` int(11) default NULL,
2080 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2081 KEY `shelfnumber` (`shelfnumber`),
2082 KEY `biblionumber` (`biblionumber`),
2083 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2084 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2085 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2088 -- Table structure for table `z3950servers`
2091 DROP TABLE IF EXISTS `z3950servers`;
2092 CREATE TABLE `z3950servers` (
2093 `host` varchar(255) default NULL,
2094 `port` int(11) default NULL,
2095 `db` varchar(255) default NULL,
2096 `userid` varchar(255) default NULL,
2097 `password` varchar(255) default NULL,
2099 `id` int(11) NOT NULL auto_increment,
2100 `checked` smallint(6) default NULL,
2101 `rank` int(11) default NULL,
2102 `syntax` varchar(80) default NULL,
2104 `position` enum('primary','secondary','') NOT NULL default 'primary',
2105 `type` enum('zed','opensearch') NOT NULL default 'zed',
2106 `encoding` text default NULL,
2107 `description` text NOT NULL,
2109 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2112 -- Table structure for table `zebraqueue`
2115 DROP TABLE IF EXISTS `zebraqueue`;
2116 CREATE TABLE `zebraqueue` (
2117 `id` int(11) NOT NULL auto_increment,
2118 `biblio_auth_number` int(11) NOT NULL default '0',
2119 `operation` char(20) NOT NULL default '',
2120 `server` char(20) NOT NULL default '',
2121 `done` int(11) NOT NULL default '0',
2122 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2124 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2125 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2127 DROP TABLE IF EXISTS `services_throttle`;
2128 CREATE TABLE `services_throttle` (
2129 `service_type` varchar(10) NOT NULL default '',
2130 `service_count` varchar(45) default NULL,
2131 PRIMARY KEY (`service_type`)
2132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2134 -- http://www.w3.org/International/articles/language-tags/
2137 DROP TABLE IF EXISTS language_subtag_registry;
2138 CREATE TABLE language_subtag_registry (
2140 type varchar(25), -- language-script-region-variant-extension-privateuse
2141 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
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 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2156 DROP TABLE IF EXISTS language_descriptions;
2157 CREATE TABLE language_descriptions (
2161 description varchar(255),
2163 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2165 -- bi-directional support, keyed by script subcode
2166 DROP TABLE IF EXISTS language_script_bidi;
2167 CREATE TABLE language_script_bidi (
2168 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2169 bidi varchar(3), -- rtl ltr
2170 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2171 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2173 -- TODO: need to map language subtags to script subtags for detection
2174 -- of bidi when script is not specified (like ar, he)
2175 DROP TABLE IF EXISTS language_script_mapping;
2176 CREATE TABLE language_script_mapping (
2177 language_subtag varchar(25),
2178 script_subtag varchar(25),
2179 KEY `language_subtag` (`language_subtag`)
2180 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2182 DROP TABLE IF EXISTS `permissions`;
2183 CREATE TABLE `permissions` (
2184 `module_bit` int(11) NOT NULL DEFAULT 0,
2185 `code` varchar(64) DEFAULT NULL,
2186 `description` varchar(255) DEFAULT NULL,
2187 PRIMARY KEY (`module_bit`, `code`),
2188 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2189 ON DELETE CASCADE ON UPDATE CASCADE
2190 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2192 DROP TABLE IF EXISTS `serialitems`;
2193 CREATE TABLE `serialitems` (
2194 `itemnumber` int(11) NOT NULL,
2195 `serialid` int(11) NOT NULL,
2196 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2197 KEY `serialitems_sfk_1` (`serialid`),
2198 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2201 DROP TABLE IF EXISTS `user_permissions`;
2202 CREATE TABLE `user_permissions` (
2203 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2204 `module_bit` int(11) NOT NULL DEFAULT 0,
2205 `code` varchar(64) DEFAULT NULL,
2206 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2207 ON DELETE CASCADE ON UPDATE CASCADE,
2208 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2209 ON DELETE CASCADE ON UPDATE CASCADE
2210 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2213 -- Table structure for table `tmp_holdsqueue`
2216 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2217 CREATE TABLE `tmp_holdsqueue` (
2218 `biblionumber` int(11) default NULL,
2219 `itemnumber` int(11) default NULL,
2220 `barcode` varchar(20) default NULL,
2221 `surname` mediumtext NOT NULL,
2224 `borrowernumber` int(11) NOT NULL,
2225 `cardnumber` varchar(16) default NULL,
2226 `reservedate` date default NULL,
2228 `itemcallnumber` varchar(30) default NULL,
2229 `holdingbranch` varchar(10) default NULL,
2230 `pickbranch` varchar(10) default NULL,
2232 `item_level_request` tinyint(4) NOT NULL default 0
2233 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2236 -- Table structure for table `message_queue`
2239 DROP TABLE IF EXISTS `message_queue`;
2240 CREATE TABLE `message_queue` (
2241 `message_id` int(11) NOT NULL auto_increment,
2242 `borrowernumber` int(11) default NULL,
2245 `metadata` text DEFAULT NULL,
2246 `letter_code` varchar(64) DEFAULT NULL,
2247 `message_transport_type` varchar(20) NOT NULL,
2248 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2249 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2250 `to_address` mediumtext,
2251 `from_address` mediumtext,
2252 `content_type` text,
2253 KEY `message_id` (`message_id`),
2254 KEY `borrowernumber` (`borrowernumber`),
2255 KEY `message_transport_type` (`message_transport_type`),
2256 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2257 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2258 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2261 -- Table structure for table `message_transport_types`
2264 DROP TABLE IF EXISTS `message_transport_types`;
2265 CREATE TABLE `message_transport_types` (
2266 `message_transport_type` varchar(20) NOT NULL,
2267 PRIMARY KEY (`message_transport_type`)
2268 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2271 -- Table structure for table `message_attributes`
2274 DROP TABLE IF EXISTS `message_attributes`;
2275 CREATE TABLE `message_attributes` (
2276 `message_attribute_id` int(11) NOT NULL auto_increment,
2277 `message_name` varchar(20) NOT NULL default '',
2278 `takes_days` tinyint(1) NOT NULL default '0',
2279 PRIMARY KEY (`message_attribute_id`),
2280 UNIQUE KEY `message_name` (`message_name`)
2281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2284 -- Table structure for table `message_transports`
2287 DROP TABLE IF EXISTS `message_transports`;
2288 CREATE TABLE `message_transports` (
2289 `message_attribute_id` int(11) NOT NULL,
2290 `message_transport_type` varchar(20) NOT NULL,
2291 `is_digest` tinyint(1) NOT NULL default '0',
2292 `letter_module` varchar(20) NOT NULL default '',
2293 `letter_code` varchar(20) NOT NULL default '',
2294 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2295 KEY `message_transport_type` (`message_transport_type`),
2296 KEY `letter_module` (`letter_module`,`letter_code`),
2297 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2298 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2299 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2303 -- Table structure for table `borrower_message_preferences`
2306 DROP TABLE IF EXISTS `borrower_message_preferences`;
2307 CREATE TABLE `borrower_message_preferences` (
2308 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2309 `borrowernumber` int(11) NOT NULL default '0',
2310 `message_attribute_id` int(11) default '0',
2311 `days_in_advance` int(11) default '0',
2312 `wants_digest` tinyint(1) NOT NULL default '0',
2313 PRIMARY KEY (`borrower_message_preference_id`),
2314 KEY `borrowernumber` (`borrowernumber`),
2315 KEY `message_attribute_id` (`message_attribute_id`),
2316 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2317 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
2318 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2321 -- Table structure for table `borrower_message_transport_preferences`
2324 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2325 CREATE TABLE `borrower_message_transport_preferences` (
2326 `borrower_message_preference_id` int(11) NOT NULL default '0',
2327 `message_transport_type` varchar(20) NOT NULL default '0',
2328 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2329 KEY `message_transport_type` (`message_transport_type`),
2330 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,
2331 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
2332 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2335 -- Table structure for the table branch_transfer_limits
2338 DROP TABLE IF EXISTS `branch_transfer_limits`;
2339 CREATE TABLE branch_transfer_limits (
2340 limitId int(8) NOT NULL auto_increment,
2341 toBranch varchar(4) NOT NULL,
2342 fromBranch varchar(4) NOT NULL,
2343 itemtype varchar(4) NULL,
2344 ccode varchar(10) NULL,
2345 PRIMARY KEY (limitId)
2346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2349 -- Table structure for table `item_circulation_alert_preferences`
2352 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2353 CREATE TABLE `item_circulation_alert_preferences` (
2354 `id` int(11) NOT NULL auto_increment,
2355 `branchcode` varchar(10) NOT NULL,
2356 `categorycode` varchar(10) NOT NULL,
2357 `item_type` varchar(10) NOT NULL,
2358 `notification` varchar(16) NOT NULL,
2360 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2361 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2363 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2364 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2365 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2366 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2367 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2368 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2369 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2370 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;