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(14) 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` int(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(14) 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 KEY `borrowernumber` (`borrowernumber`),
902 KEY `cardnumber` (`cardnumber`)
903 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
906 -- Table structure for table `deleteditems`
909 DROP TABLE IF EXISTS `deleteditems`;
910 CREATE TABLE `deleteditems` (
911 `itemnumber` int(11) NOT NULL default 0,
912 `biblionumber` int(11) NOT NULL default 0,
913 `biblioitemnumber` int(11) NOT NULL default 0,
914 `barcode` varchar(20) default NULL,
915 `dateaccessioned` date default NULL,
916 `booksellerid` mediumtext default NULL,
917 `homebranch` varchar(10) default NULL,
918 `price` decimal(8,2) default NULL,
919 `replacementprice` decimal(8,2) default NULL,
920 `replacementpricedate` date default NULL,
921 `datelastborrowed` date default NULL,
922 `datelastseen` date default NULL,
923 `stack` tinyint(1) default NULL,
924 `notforloan` tinyint(1) NOT NULL default 0,
925 `damaged` tinyint(1) NOT NULL default 0,
926 `itemlost` tinyint(1) NOT NULL default 0,
927 `wthdrawn` tinyint(1) NOT NULL default 0,
928 `itemcallnumber` varchar(30) default NULL,
929 `issues` smallint(6) default NULL,
930 `renewals` smallint(6) default NULL,
931 `reserves` smallint(6) default NULL,
932 `restricted` tinyint(1) default NULL,
933 `itemnotes` mediumtext,
934 `holdingbranch` varchar(10) default NULL,
935 `paidfor` mediumtext,
936 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
937 `location` varchar(80) default NULL,
938 `onloan` date default NULL,
939 `cn_source` varchar(10) default NULL,
940 `cn_sort` varchar(30) default NULL,
941 `ccode` varchar(10) default NULL,
942 `materials` varchar(10) default NULL,
943 `uri` varchar(255) default NULL,
944 `itype` varchar(10) default NULL,
945 `more_subfields_xml` longtext default NULL,
946 `enumchron` varchar(80) default NULL,
947 `copynumber` varchar(32) default NULL,
949 PRIMARY KEY (`itemnumber`),
950 KEY `delitembarcodeidx` (`barcode`),
951 KEY `delitembinoidx` (`biblioitemnumber`),
952 KEY `delitembibnoidx` (`biblionumber`),
953 KEY `delhomebranch` (`homebranch`),
954 KEY `delholdingbranch` (`holdingbranch`)
955 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
958 -- Table structure for table `ethnicity`
961 DROP TABLE IF EXISTS `ethnicity`;
962 CREATE TABLE `ethnicity` (
963 `code` varchar(10) NOT NULL default '',
964 `name` varchar(255) default NULL,
966 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
969 -- Table structure for table `hold_fill_targets`
972 DROP TABLE IF EXISTS `hold_fill_targets`;
973 CREATE TABLE hold_fill_targets (
974 `borrowernumber` int(11) NOT NULL,
975 `biblionumber` int(11) NOT NULL,
976 `itemnumber` int(11) NOT NULL,
977 `source_branchcode` varchar(10) default NULL,
978 `item_level_request` tinyint(4) NOT NULL default 0,
979 PRIMARY KEY `itemnumber` (`itemnumber`),
980 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
981 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
982 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
983 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
984 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
985 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
986 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
987 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
988 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
989 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
992 -- Table structure for table `import_batches`
995 DROP TABLE IF EXISTS `import_batches`;
996 CREATE TABLE `import_batches` (
997 `import_batch_id` int(11) NOT NULL auto_increment,
998 `matcher_id` int(11) default NULL,
999 `template_id` int(11) default NULL,
1000 `branchcode` varchar(10) default NULL,
1001 `num_biblios` int(11) NOT NULL default 0,
1002 `num_items` int(11) NOT NULL default 0,
1003 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1004 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1005 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1006 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1007 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1008 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1009 `file_name` varchar(100),
1010 `comments` mediumtext,
1011 PRIMARY KEY (`import_batch_id`),
1012 KEY `branchcode` (`branchcode`)
1013 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1016 -- Table structure for table `import_records`
1019 DROP TABLE IF EXISTS `import_records`;
1020 CREATE TABLE `import_records` (
1021 `import_record_id` int(11) NOT NULL auto_increment,
1022 `import_batch_id` int(11) NOT NULL,
1023 `branchcode` varchar(10) default NULL,
1024 `record_sequence` int(11) NOT NULL default 0,
1025 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1026 `import_date` DATE default NULL,
1027 `marc` longblob NOT NULL,
1028 `marcxml` longtext NOT NULL,
1029 `marcxml_old` longtext NOT NULL,
1030 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1031 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1032 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1033 `import_error` mediumtext,
1034 `encoding` varchar(40) NOT NULL default '',
1035 `z3950random` varchar(40) default NULL,
1036 PRIMARY KEY (`import_record_id`),
1037 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1038 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1039 KEY `branchcode` (`branchcode`),
1040 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1041 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1044 -- Table structure for `import_record_matches`
1046 DROP TABLE IF EXISTS `import_record_matches`;
1047 CREATE TABLE `import_record_matches` (
1048 `import_record_id` int(11) NOT NULL,
1049 `candidate_match_id` int(11) NOT NULL,
1050 `score` int(11) NOT NULL default 0,
1051 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1052 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1053 KEY `record_score` (`import_record_id`, `score`)
1054 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1057 -- Table structure for table `import_biblios`
1060 DROP TABLE IF EXISTS `import_biblios`;
1061 CREATE TABLE `import_biblios` (
1062 `import_record_id` int(11) NOT NULL,
1063 `matched_biblionumber` int(11) default NULL,
1064 `control_number` varchar(25) default NULL,
1065 `original_source` varchar(25) default NULL,
1066 `title` varchar(128) default NULL,
1067 `author` varchar(80) default NULL,
1068 `isbn` varchar(14) default NULL,
1069 `issn` varchar(9) default NULL,
1070 `has_items` tinyint(1) NOT NULL default 0,
1071 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1072 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1073 KEY `matched_biblionumber` (`matched_biblionumber`),
1074 KEY `title` (`title`),
1076 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1079 -- Table structure for table `import_items`
1082 DROP TABLE IF EXISTS `import_items`;
1083 CREATE TABLE `import_items` (
1084 `import_items_id` int(11) NOT NULL auto_increment,
1085 `import_record_id` int(11) NOT NULL,
1086 `itemnumber` int(11) default NULL,
1087 `branchcode` varchar(10) default NULL,
1088 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1089 `marcxml` longtext NOT NULL,
1090 `import_error` mediumtext,
1091 PRIMARY KEY (`import_items_id`),
1092 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1093 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1094 KEY `itemnumber` (`itemnumber`),
1095 KEY `branchcode` (`branchcode`)
1096 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1099 -- Table structure for table `issues`
1102 DROP TABLE IF EXISTS `issues`;
1103 CREATE TABLE `issues` (
1104 `borrowernumber` int(11) default NULL,
1105 `itemnumber` int(11) default NULL,
1106 `date_due` date default NULL,
1107 `branchcode` varchar(10) default NULL,
1108 `issuingbranch` varchar(18) default NULL,
1109 `returndate` date default NULL,
1110 `lastreneweddate` date default NULL,
1111 `return` varchar(4) default NULL,
1112 `renewals` tinyint(4) default NULL,
1113 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1114 `issuedate` date default NULL,
1115 KEY `issuesborridx` (`borrowernumber`),
1116 KEY `issuesitemidx` (`itemnumber`),
1117 KEY `bordate` (`borrowernumber`,`timestamp`),
1118 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1119 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1120 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1123 -- Table structure for table `issuingrules`
1126 DROP TABLE IF EXISTS `issuingrules`;
1127 CREATE TABLE `issuingrules` (
1128 `categorycode` varchar(10) NOT NULL default '',
1129 `itemtype` varchar(10) NOT NULL default '',
1130 `restrictedtype` tinyint(1) default NULL,
1131 `rentaldiscount` decimal(28,6) default NULL,
1132 `reservecharge` decimal(28,6) default NULL,
1133 `fine` decimal(28,6) default NULL,
1134 `firstremind` int(11) default NULL,
1135 `chargeperiod` int(11) default NULL,
1136 `accountsent` int(11) default NULL,
1137 `chargename` varchar(100) default NULL,
1138 `maxissueqty` int(4) default NULL,
1139 `issuelength` int(4) default NULL,
1140 `branchcode` varchar(10) NOT NULL default '',
1141 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1142 KEY `categorycode` (`categorycode`),
1143 KEY `itemtype` (`itemtype`)
1144 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1147 -- Table structure for table `items`
1150 DROP TABLE IF EXISTS `items`;
1151 CREATE TABLE `items` (
1152 `itemnumber` int(11) NOT NULL auto_increment,
1153 `biblionumber` int(11) NOT NULL default 0,
1154 `biblioitemnumber` int(11) NOT NULL default 0,
1155 `barcode` varchar(20) default NULL,
1156 `dateaccessioned` date default NULL,
1157 `booksellerid` mediumtext default NULL,
1158 `homebranch` varchar(10) default NULL,
1159 `price` decimal(8,2) default NULL,
1160 `replacementprice` decimal(8,2) default NULL,
1161 `replacementpricedate` date default NULL,
1162 `datelastborrowed` date default NULL,
1163 `datelastseen` date default NULL,
1164 `stack` tinyint(1) default NULL,
1165 `notforloan` tinyint(1) NOT NULL default 0,
1166 `damaged` tinyint(1) NOT NULL default 0,
1167 `itemlost` tinyint(1) NOT NULL default 0,
1168 `wthdrawn` tinyint(1) NOT NULL default 0,
1169 `itemcallnumber` varchar(30) default NULL,
1170 `issues` smallint(6) default NULL,
1171 `renewals` smallint(6) default NULL,
1172 `reserves` smallint(6) default NULL,
1173 `restricted` tinyint(1) default NULL,
1174 `itemnotes` mediumtext,
1175 `holdingbranch` varchar(10) default NULL,
1176 `paidfor` mediumtext,
1177 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1178 `location` varchar(80) default NULL,
1179 `onloan` date default NULL,
1180 `cn_source` varchar(10) default NULL,
1181 `cn_sort` varchar(30) default NULL,
1182 `ccode` varchar(10) default NULL,
1183 `materials` varchar(10) default NULL,
1184 `uri` varchar(255) default NULL,
1185 `itype` varchar(10) default NULL,
1186 `more_subfields_xml` longtext default NULL,
1187 `enumchron` varchar(80) default NULL,
1188 `copynumber` varchar(32) default NULL,
1189 PRIMARY KEY (`itemnumber`),
1190 UNIQUE KEY `itembarcodeidx` (`barcode`),
1191 KEY `itembinoidx` (`biblioitemnumber`),
1192 KEY `itembibnoidx` (`biblionumber`),
1193 KEY `homebranch` (`homebranch`),
1194 KEY `holdingbranch` (`holdingbranch`),
1195 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1196 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1197 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1201 -- Table structure for table `itemtypes`
1204 DROP TABLE IF EXISTS `itemtypes`;
1205 CREATE TABLE `itemtypes` (
1206 `itemtype` varchar(10) NOT NULL default '',
1207 `description` mediumtext,
1208 `renewalsallowed` smallint(6) default NULL,
1209 `rentalcharge` double(16,4) default NULL,
1210 `notforloan` smallint(6) default NULL,
1211 `imageurl` varchar(200) default NULL,
1213 PRIMARY KEY (`itemtype`),
1214 UNIQUE KEY `itemtype` (`itemtype`)
1215 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1218 -- Table structure for table `labels`
1221 DROP TABLE IF EXISTS `labels`;
1222 CREATE TABLE `labels` (
1223 `labelid` int(11) NOT NULL auto_increment,
1224 `batch_id` varchar(10) NOT NULL default 1,
1225 `itemnumber` varchar(100) NOT NULL default '',
1226 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1227 PRIMARY KEY (`labelid`)
1228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1231 -- Table structure for table `labels_conf`
1234 DROP TABLE IF EXISTS `labels_conf`;
1235 CREATE TABLE `labels_conf` (
1236 `id` int(4) NOT NULL auto_increment,
1237 `barcodetype` char(100) default '',
1238 `title` int(1) default '0',
1239 `subtitle` int(1) default '0',
1240 `itemtype` int(1) default '0',
1241 `barcode` int(1) default '0',
1242 `dewey` int(1) default '0',
1243 `classification` int(1) default NULL,
1244 `subclass` int(1) default '0',
1245 `itemcallnumber` int(1) default '0',
1246 `author` int(1) default '0',
1247 `issn` int(1) default '0',
1248 `isbn` int(1) default '0',
1249 `startlabel` int(2) NOT NULL default '1',
1250 `printingtype` char(32) default 'BAR',
1251 `formatstring` varchar(64) default NULL,
1252 `layoutname` char(20) NOT NULL default 'TEST',
1253 `guidebox` int(1) default '0',
1254 `active` tinyint(1) default '1',
1255 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1256 `ccode` char(4) collate utf8_unicode_ci default NULL,
1257 `callnum_split` int(1) default NULL,
1258 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1263 -- Table structure for table `labels_profile`
1266 DROP TABLE IF EXISTS `labels_profile`;
1267 CREATE TABLE `labels_profile` (
1268 `tmpl_id` int(4) NOT NULL,
1269 `prof_id` int(4) NOT NULL,
1270 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1271 UNIQUE KEY `prof_id` (`prof_id`)
1272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1275 -- Table structure for table `labels_templates`
1278 DROP TABLE IF EXISTS `labels_templates`;
1279 CREATE TABLE `labels_templates` (
1280 `tmpl_id` int(4) NOT NULL auto_increment,
1281 `tmpl_code` char(100) default '',
1282 `tmpl_desc` char(100) default '',
1283 `page_width` float default '0',
1284 `page_height` float default '0',
1285 `label_width` float default '0',
1286 `label_height` float default '0',
1287 `topmargin` float default '0',
1288 `leftmargin` float default '0',
1289 `cols` int(2) default '0',
1290 `rows` int(2) default '0',
1291 `colgap` float default '0',
1292 `rowgap` float default '0',
1293 `active` int(1) default NULL,
1294 `units` char(20) default 'PX',
1295 `fontsize` int(4) NOT NULL default '3',
1296 `font` char(10) NOT NULL default 'TR',
1297 PRIMARY KEY (`tmpl_id`)
1298 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1301 -- Table structure for table `letter`
1304 DROP TABLE IF EXISTS `letter`;
1305 CREATE TABLE `letter` (
1306 `module` varchar(20) NOT NULL default '',
1307 `code` varchar(20) NOT NULL default '',
1308 `name` varchar(100) NOT NULL default '',
1309 `title` varchar(200) NOT NULL default '',
1311 PRIMARY KEY (`module`,`code`)
1312 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1315 -- Table structure for table `marc_subfield_structure`
1318 DROP TABLE IF EXISTS `marc_subfield_structure`;
1319 CREATE TABLE `marc_subfield_structure` (
1320 `tagfield` varchar(3) NOT NULL default '',
1321 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1322 `liblibrarian` varchar(255) NOT NULL default '',
1323 `libopac` varchar(255) NOT NULL default '',
1324 `repeatable` tinyint(4) NOT NULL default 0,
1325 `mandatory` tinyint(4) NOT NULL default 0,
1326 `kohafield` varchar(40) default NULL,
1327 `tab` tinyint(1) default NULL,
1328 `authorised_value` varchar(20) default NULL,
1329 `authtypecode` varchar(20) default NULL,
1330 `value_builder` varchar(80) default NULL,
1331 `isurl` tinyint(1) default NULL,
1332 `hidden` tinyint(1) default NULL,
1333 `frameworkcode` varchar(4) NOT NULL default '',
1334 `seealso` varchar(1100) default NULL,
1335 `link` varchar(80) default NULL,
1336 `defaultvalue` text default NULL,
1337 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1338 KEY `kohafield_2` (`kohafield`),
1339 KEY `tab` (`frameworkcode`,`tab`),
1340 KEY `kohafield` (`frameworkcode`,`kohafield`)
1341 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1344 -- Table structure for table `marc_tag_structure`
1347 DROP TABLE IF EXISTS `marc_tag_structure`;
1348 CREATE TABLE `marc_tag_structure` (
1349 `tagfield` varchar(3) NOT NULL default '',
1350 `liblibrarian` varchar(255) NOT NULL default '',
1351 `libopac` varchar(255) NOT NULL default '',
1352 `repeatable` tinyint(4) NOT NULL default 0,
1353 `mandatory` tinyint(4) NOT NULL default 0,
1354 `authorised_value` varchar(10) default NULL,
1355 `frameworkcode` varchar(4) NOT NULL default '',
1356 PRIMARY KEY (`frameworkcode`,`tagfield`)
1357 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1360 -- Table structure for table `marc_matchers`
1363 DROP TABLE IF EXISTS `marc_matchers`;
1364 CREATE TABLE `marc_matchers` (
1365 `matcher_id` int(11) NOT NULL auto_increment,
1366 `code` varchar(10) NOT NULL default '',
1367 `description` varchar(255) NOT NULL default '',
1368 `record_type` varchar(10) NOT NULL default 'biblio',
1369 `threshold` int(11) NOT NULL default 0,
1370 PRIMARY KEY (`matcher_id`),
1371 KEY `code` (`code`),
1372 KEY `record_type` (`record_type`)
1373 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1376 -- Table structure for table `matchpoints`
1378 DROP TABLE IF EXISTS `matchpoints`;
1379 CREATE TABLE `matchpoints` (
1380 `matcher_id` int(11) NOT NULL,
1381 `matchpoint_id` int(11) NOT NULL auto_increment,
1382 `search_index` varchar(30) NOT NULL default '',
1383 `score` int(11) NOT NULL default 0,
1384 PRIMARY KEY (`matchpoint_id`),
1385 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1386 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1387 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1391 -- Table structure for table `matchpoint_components`
1393 DROP TABLE IF EXISTS `matchpoint_components`;
1394 CREATE TABLE `matchpoint_components` (
1395 `matchpoint_id` int(11) NOT NULL,
1396 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1397 sequence int(11) NOT NULL default 0,
1398 tag varchar(3) NOT NULL default '',
1399 subfields varchar(40) NOT NULL default '',
1400 offset int(4) NOT NULL default 0,
1401 length int(4) NOT NULL default 0,
1402 PRIMARY KEY (`matchpoint_component_id`),
1403 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1404 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1405 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1406 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1409 -- Table structure for table `matcher_component_norms`
1411 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1412 CREATE TABLE `matchpoint_component_norms` (
1413 `matchpoint_component_id` int(11) NOT NULL,
1414 `sequence` int(11) NOT NULL default 0,
1415 `norm_routine` varchar(50) NOT NULL default '',
1416 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1417 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1418 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1419 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1422 -- Table structure for table `matcher_matchpoints`
1424 DROP TABLE IF EXISTS `matcher_matchpoints`;
1425 CREATE TABLE `matcher_matchpoints` (
1426 `matcher_id` int(11) NOT NULL,
1427 `matchpoint_id` int(11) NOT NULL,
1428 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1429 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1430 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1431 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1432 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1435 -- Table structure for table `matchchecks`
1437 DROP TABLE IF EXISTS `matchchecks`;
1438 CREATE TABLE `matchchecks` (
1439 `matcher_id` int(11) NOT NULL,
1440 `matchcheck_id` int(11) NOT NULL auto_increment,
1441 `source_matchpoint_id` int(11) NOT NULL,
1442 `target_matchpoint_id` int(11) NOT NULL,
1443 PRIMARY KEY (`matchcheck_id`),
1444 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1445 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1446 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1447 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1448 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1449 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1450 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1453 -- Table structure for table `notifys`
1456 DROP TABLE IF EXISTS `notifys`;
1457 CREATE TABLE `notifys` (
1458 `notify_id` int(11) NOT NULL default 0,
1459 `borrowernumber` int(11) NOT NULL default 0,
1460 `itemnumber` int(11) NOT NULL default 0,
1461 `notify_date` date default NULL,
1462 `notify_send_date` date default NULL,
1463 `notify_level` int(1) NOT NULL default 0,
1464 `method` varchar(20) NOT NULL default ''
1465 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1468 -- Table structure for table `nozebra`
1471 DROP TABLE IF EXISTS `nozebra`;
1472 CREATE TABLE `nozebra` (
1473 `server` varchar(20) NOT NULL,
1474 `indexname` varchar(40) NOT NULL,
1475 `value` varchar(250) NOT NULL,
1476 `biblionumbers` longtext NOT NULL,
1477 KEY `indexname` (`server`,`indexname`),
1478 KEY `value` (`server`,`value`))
1479 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1482 -- Table structure for table `old_issues`
1485 DROP TABLE IF EXISTS `old_issues`;
1486 CREATE TABLE `old_issues` (
1487 `borrowernumber` int(11) default NULL,
1488 `itemnumber` int(11) default NULL,
1489 `date_due` date default NULL,
1490 `branchcode` varchar(10) default NULL,
1491 `issuingbranch` varchar(18) default NULL,
1492 `returndate` date default NULL,
1493 `lastreneweddate` date default NULL,
1494 `return` varchar(4) default NULL,
1495 `renewals` tinyint(4) default NULL,
1496 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1497 `issuedate` date default NULL,
1498 KEY `old_issuesborridx` (`borrowernumber`),
1499 KEY `old_issuesitemidx` (`itemnumber`),
1500 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1501 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1502 ON DELETE SET NULL ON UPDATE SET NULL,
1503 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1504 ON DELETE SET NULL ON UPDATE SET NULL
1505 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1508 -- Table structure for table `old_reserves`
1510 DROP TABLE IF EXISTS `old_reserves`;
1511 CREATE TABLE `old_reserves` (
1512 `borrowernumber` int(11) default NULL,
1513 `reservedate` date default NULL,
1514 `biblionumber` int(11) default NULL,
1515 `constrainttype` varchar(1) default NULL,
1516 `branchcode` varchar(10) default NULL,
1517 `notificationdate` date default NULL,
1518 `reminderdate` date default NULL,
1519 `cancellationdate` date default NULL,
1520 `reservenotes` mediumtext,
1521 `priority` smallint(6) default NULL,
1522 `found` varchar(1) default NULL,
1523 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1524 `itemnumber` int(11) default NULL,
1525 `waitingdate` date default NULL,
1526 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1527 KEY `old_reserves_biblionumber` (`biblionumber`),
1528 KEY `old_reserves_itemnumber` (`itemnumber`),
1529 KEY `old_reserves_branchcode` (`branchcode`),
1530 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1531 ON DELETE SET NULL ON UPDATE SET NULL,
1532 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1533 ON DELETE SET NULL ON UPDATE SET NULL,
1534 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1535 ON DELETE SET NULL ON UPDATE SET NULL
1536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1539 -- Table structure for table `opac_news`
1542 DROP TABLE IF EXISTS `opac_news`;
1543 CREATE TABLE `opac_news` (
1544 `idnew` int(10) unsigned NOT NULL auto_increment,
1545 `title` varchar(250) NOT NULL default '',
1546 `new` text NOT NULL,
1547 `lang` varchar(25) NOT NULL default '',
1548 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1549 `expirationdate` date default NULL,
1550 `number` int(11) default NULL,
1551 PRIMARY KEY (`idnew`)
1552 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1555 -- Table structure for table `overduerules`
1558 DROP TABLE IF EXISTS `overduerules`;
1559 CREATE TABLE `overduerules` (
1560 `branchcode` varchar(10) NOT NULL default '',
1561 `categorycode` varchar(10) NOT NULL default '',
1562 `delay1` int(4) default 0,
1563 `letter1` varchar(20) default NULL,
1564 `debarred1` varchar(1) default 0,
1565 `delay2` int(4) default 0,
1566 `debarred2` varchar(1) default 0,
1567 `letter2` varchar(20) default NULL,
1568 `delay3` int(4) default 0,
1569 `letter3` varchar(20) default NULL,
1570 `debarred3` int(1) default 0,
1571 PRIMARY KEY (`branchcode`,`categorycode`)
1572 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1575 -- Table structure for table `patroncards`
1578 DROP TABLE IF EXISTS `patroncards`;
1579 CREATE TABLE `patroncards` (
1580 `cardid` int(11) NOT NULL auto_increment,
1581 `batch_id` varchar(10) NOT NULL default '1',
1582 `borrowernumber` int(11) NOT NULL,
1583 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1584 PRIMARY KEY (`cardid`),
1585 KEY `patroncards_ibfk_1` (`borrowernumber`),
1586 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1587 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1590 -- Table structure for table `patronimage`
1593 DROP TABLE IF EXISTS `patronimage`;
1594 CREATE TABLE `patronimage` (
1595 `cardnumber` varchar(16) NOT NULL,
1596 `mimetype` varchar(15) NOT NULL,
1597 `imagefile` mediumblob NOT NULL,
1598 PRIMARY KEY (`cardnumber`),
1599 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1600 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1603 -- Table structure for table `printers`
1606 DROP TABLE IF EXISTS `printers`;
1607 CREATE TABLE `printers` (
1608 `printername` varchar(40) NOT NULL default '',
1609 `printqueue` varchar(20) default NULL,
1610 `printtype` varchar(20) default NULL,
1611 PRIMARY KEY (`printername`)
1612 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1615 -- Table structure for table `printers_profile`
1618 DROP TABLE IF EXISTS `printers_profile`;
1619 CREATE TABLE `printers_profile` (
1620 `prof_id` int(4) NOT NULL auto_increment,
1621 `printername` varchar(40) NOT NULL,
1622 `tmpl_id` int(4) NOT NULL,
1623 `paper_bin` varchar(20) NOT NULL,
1624 `offset_horz` float default NULL,
1625 `offset_vert` float default NULL,
1626 `creep_horz` float default NULL,
1627 `creep_vert` float default NULL,
1628 `unit` char(20) NOT NULL default 'POINT',
1629 PRIMARY KEY (`prof_id`),
1630 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1631 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1632 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1635 -- Table structure for table `repeatable_holidays`
1638 DROP TABLE IF EXISTS `repeatable_holidays`;
1639 CREATE TABLE `repeatable_holidays` (
1640 `id` int(11) NOT NULL auto_increment,
1641 `branchcode` varchar(10) NOT NULL default '',
1642 `weekday` smallint(6) default NULL,
1643 `day` smallint(6) default NULL,
1644 `month` smallint(6) default NULL,
1645 `title` varchar(50) NOT NULL default '',
1646 `description` text NOT NULL,
1648 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1651 -- Table structure for table `reports_dictionary`
1654 DROP TABLE IF EXISTS `reports_dictionary`;
1655 CREATE TABLE reports_dictionary (
1656 `id` int(11) NOT NULL auto_increment,
1657 `name` varchar(255) default NULL,
1659 `date_created` datetime default NULL,
1660 `date_modified` datetime default NULL,
1662 `area` int(11) default NULL,
1664 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1667 -- Table structure for table `reserveconstraints`
1670 DROP TABLE IF EXISTS `reserveconstraints`;
1671 CREATE TABLE `reserveconstraints` (
1672 `borrowernumber` int(11) NOT NULL default 0,
1673 `reservedate` date default NULL,
1674 `biblionumber` int(11) NOT NULL default 0,
1675 `biblioitemnumber` int(11) default NULL,
1676 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1677 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1680 -- Table structure for table `reserves`
1683 DROP TABLE IF EXISTS `reserves`;
1684 CREATE TABLE `reserves` (
1685 `borrowernumber` int(11) NOT NULL default 0,
1686 `reservedate` date default NULL,
1687 `biblionumber` int(11) NOT NULL default 0,
1688 `constrainttype` varchar(1) default NULL,
1689 `branchcode` varchar(10) default NULL,
1690 `notificationdate` date default NULL,
1691 `reminderdate` date default NULL,
1692 `cancellationdate` date default NULL,
1693 `reservenotes` mediumtext,
1694 `priority` smallint(6) default NULL,
1695 `found` varchar(1) default NULL,
1696 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1697 `itemnumber` int(11) default NULL,
1698 `waitingdate` date default NULL,
1699 KEY `borrowernumber` (`borrowernumber`),
1700 KEY `biblionumber` (`biblionumber`),
1701 KEY `itemnumber` (`itemnumber`),
1702 KEY `branchcode` (`branchcode`),
1703 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1704 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1705 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1706 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1707 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1710 -- Table structure for table `reviews`
1713 DROP TABLE IF EXISTS `reviews`;
1714 CREATE TABLE `reviews` (
1715 `reviewid` int(11) NOT NULL auto_increment,
1716 `borrowernumber` int(11) default NULL,
1717 `biblionumber` int(11) default NULL,
1719 `approved` tinyint(4) default NULL,
1720 `datereviewed` datetime default NULL,
1721 PRIMARY KEY (`reviewid`)
1722 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1725 -- Table structure for table `roadtype`
1728 DROP TABLE IF EXISTS `roadtype`;
1729 CREATE TABLE `roadtype` (
1730 `roadtypeid` int(11) NOT NULL auto_increment,
1731 `road_type` varchar(100) NOT NULL default '',
1732 PRIMARY KEY (`roadtypeid`)
1733 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1736 -- Table structure for table `saved_sql`
1739 DROP TABLE IF EXISTS `saved_sql`;
1740 CREATE TABLE saved_sql (
1741 `id` int(11) NOT NULL auto_increment,
1742 `borrowernumber` int(11) default NULL,
1743 `date_created` datetime default NULL,
1744 `last_modified` datetime default NULL,
1746 `last_run` datetime default NULL,
1747 `report_name` varchar(255) default NULL,
1748 `type` varchar(255) default NULL,
1751 KEY boridx (`borrowernumber`)
1752 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1756 -- Table structure for `saved_reports`
1759 DROP TABLE IF EXISTS `saved_reports`;
1760 CREATE TABLE saved_reports (
1761 `id` int(11) NOT NULL auto_increment,
1762 `report_id` int(11) default NULL,
1764 `date_run` datetime default NULL,
1766 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1770 -- Table structure for table `serial`
1773 DROP TABLE IF EXISTS `serial`;
1774 CREATE TABLE `serial` (
1775 `serialid` int(11) NOT NULL auto_increment,
1776 `biblionumber` varchar(100) NOT NULL default '',
1777 `subscriptionid` varchar(100) NOT NULL default '',
1778 `serialseq` varchar(100) NOT NULL default '',
1779 `status` tinyint(4) NOT NULL default 0,
1780 `planneddate` date default NULL,
1782 `publisheddate` date default NULL,
1783 `itemnumber` text default NULL,
1784 `claimdate` date default NULL,
1785 `routingnotes` text,
1786 PRIMARY KEY (`serialid`)
1787 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1790 -- Table structure for table `sessions`
1793 DROP TABLE IF EXISTS sessions;
1794 CREATE TABLE sessions (
1795 `id` varchar(32) NOT NULL,
1796 `a_session` text NOT NULL,
1798 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1801 -- Table structure for table `special_holidays`
1804 DROP TABLE IF EXISTS `special_holidays`;
1805 CREATE TABLE `special_holidays` (
1806 `id` int(11) NOT NULL auto_increment,
1807 `branchcode` varchar(10) NOT NULL default '',
1808 `day` smallint(6) NOT NULL default 0,
1809 `month` smallint(6) NOT NULL default 0,
1810 `year` smallint(6) NOT NULL default 0,
1811 `isexception` smallint(1) NOT NULL default 1,
1812 `title` varchar(50) NOT NULL default '',
1813 `description` text NOT NULL,
1815 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1818 -- Table structure for table `statistics`
1821 DROP TABLE IF EXISTS `statistics`;
1822 CREATE TABLE `statistics` (
1823 `datetime` datetime default NULL,
1824 `branch` varchar(10) default NULL,
1825 `proccode` varchar(4) default NULL,
1826 `value` double(16,4) default NULL,
1827 `type` varchar(16) default NULL,
1829 `usercode` varchar(10) default NULL,
1830 `itemnumber` int(11) default NULL,
1831 `itemtype` varchar(10) default NULL,
1832 `borrowernumber` int(11) default NULL,
1833 `associatedborrower` int(11) default NULL,
1834 KEY `timeidx` (`datetime`)
1835 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1838 -- Table structure for table `stopwords`
1841 DROP TABLE IF EXISTS `stopwords`;
1842 CREATE TABLE `stopwords` (
1843 `word` varchar(255) default NULL
1844 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1847 -- Table structure for table `subscription`
1850 DROP TABLE IF EXISTS `subscription`;
1851 CREATE TABLE `subscription` (
1852 `biblionumber` int(11) NOT NULL default 0,
1853 `subscriptionid` int(11) NOT NULL auto_increment,
1854 `librarian` varchar(100) default '',
1855 `startdate` date default NULL,
1856 `aqbooksellerid` int(11) default 0,
1857 `cost` int(11) default 0,
1858 `aqbudgetid` int(11) default 0,
1859 `weeklength` int(11) default 0,
1860 `monthlength` int(11) default 0,
1861 `numberlength` int(11) default 0,
1862 `periodicity` tinyint(4) default 0,
1863 `dow` varchar(100) default '',
1864 `numberingmethod` varchar(100) default '',
1866 `status` varchar(100) NOT NULL default '',
1867 `add1` int(11) default 0,
1868 `every1` int(11) default 0,
1869 `whenmorethan1` int(11) default 0,
1870 `setto1` int(11) default NULL,
1871 `lastvalue1` int(11) default NULL,
1872 `add2` int(11) default 0,
1873 `every2` int(11) default 0,
1874 `whenmorethan2` int(11) default 0,
1875 `setto2` int(11) default NULL,
1876 `lastvalue2` int(11) default NULL,
1877 `add3` int(11) default 0,
1878 `every3` int(11) default 0,
1879 `innerloop1` int(11) default 0,
1880 `innerloop2` int(11) default 0,
1881 `innerloop3` int(11) default 0,
1882 `whenmorethan3` int(11) default 0,
1883 `setto3` int(11) default NULL,
1884 `lastvalue3` int(11) default NULL,
1885 `issuesatonce` tinyint(3) NOT NULL default 1,
1886 `firstacquidate` date default NULL,
1887 `manualhistory` tinyint(1) NOT NULL default 0,
1888 `irregularity` text,
1889 `letter` varchar(20) default NULL,
1890 `numberpattern` tinyint(3) default 0,
1891 `distributedto` text,
1892 `internalnotes` longtext,
1894 `branchcode` varchar(10) NOT NULL default '',
1895 `hemisphere` tinyint(3) default 0,
1896 `lastbranch` varchar(10),
1897 `serialsadditems` tinyint(1) NOT NULL default '0',
1898 PRIMARY KEY (`subscriptionid`)
1899 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1902 -- Table structure for table `subscriptionhistory`
1905 DROP TABLE IF EXISTS `subscriptionhistory`;
1906 CREATE TABLE `subscriptionhistory` (
1907 `biblionumber` int(11) NOT NULL default 0,
1908 `subscriptionid` int(11) NOT NULL default 0,
1909 `histstartdate` date default NULL,
1910 `enddate` date default NULL,
1911 `missinglist` longtext NOT NULL,
1912 `recievedlist` longtext NOT NULL,
1913 `opacnote` varchar(150) NOT NULL default '',
1914 `librariannote` varchar(150) NOT NULL default '',
1915 PRIMARY KEY (`subscriptionid`),
1916 KEY `biblionumber` (`biblionumber`)
1917 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1920 -- Table structure for table `subscriptionroutinglist`
1923 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1924 CREATE TABLE `subscriptionroutinglist` (
1925 `routingid` int(11) NOT NULL auto_increment,
1926 `borrowernumber` int(11) default NULL,
1927 `ranking` int(11) default NULL,
1928 `subscriptionid` int(11) default NULL,
1929 PRIMARY KEY (`routingid`)
1930 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1933 -- Table structure for table `suggestions`
1936 DROP TABLE IF EXISTS `suggestions`;
1937 CREATE TABLE `suggestions` (
1938 `suggestionid` int(8) NOT NULL auto_increment,
1939 `suggestedby` int(11) NOT NULL default 0,
1940 `managedby` int(11) default NULL,
1941 `STATUS` varchar(10) NOT NULL default '',
1943 `author` varchar(80) default NULL,
1944 `title` varchar(80) default NULL,
1945 `copyrightdate` smallint(6) default NULL,
1946 `publishercode` varchar(255) default NULL,
1947 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1948 `volumedesc` varchar(255) default NULL,
1949 `publicationyear` smallint(6) default 0,
1950 `place` varchar(255) default NULL,
1951 `isbn` varchar(10) default NULL,
1952 `mailoverseeing` smallint(1) default 0,
1953 `biblionumber` int(11) default NULL,
1955 PRIMARY KEY (`suggestionid`),
1956 KEY `suggestedby` (`suggestedby`),
1957 KEY `managedby` (`managedby`)
1958 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1961 -- Table structure for table `systempreferences`
1964 DROP TABLE IF EXISTS `systempreferences`;
1965 CREATE TABLE `systempreferences` (
1966 `variable` varchar(50) NOT NULL default '',
1968 `options` mediumtext,
1970 `type` varchar(20) default NULL,
1971 PRIMARY KEY (`variable`)
1972 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1975 -- Table structure for table `tags`
1978 DROP TABLE IF EXISTS `tags`;
1979 CREATE TABLE `tags` (
1980 `entry` varchar(255) NOT NULL default '',
1981 `weight` bigint(20) NOT NULL default 0,
1982 PRIMARY KEY (`entry`)
1983 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1986 -- Table structure for table `tags_all`
1989 DROP TABLE IF EXISTS `tags_all`;
1990 CREATE TABLE `tags_all` (
1991 `tag_id` int(11) NOT NULL auto_increment,
1992 `borrowernumber` int(11) NOT NULL,
1993 `biblionumber` int(11) NOT NULL,
1994 `term` varchar(255) NOT NULL,
1995 `language` int(4) default NULL,
1996 `date_created` datetime NOT NULL,
1997 PRIMARY KEY (`tag_id`),
1998 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1999 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2000 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2001 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2002 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2003 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2004 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2007 -- Table structure for table `tags_approval`
2010 DROP TABLE IF EXISTS `tags_approval`;
2011 CREATE TABLE `tags_approval` (
2012 `term` varchar(255) NOT NULL,
2013 `approved` int(1) NOT NULL default '0',
2014 `date_approved` datetime default NULL,
2015 `approved_by` int(11) default NULL,
2016 `weight_total` int(9) NOT NULL default '1',
2017 PRIMARY KEY (`term`),
2018 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2019 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2020 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2021 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2024 -- Table structure for table `tags_index`
2027 DROP TABLE IF EXISTS `tags_index`;
2028 CREATE TABLE `tags_index` (
2029 `term` varchar(255) NOT NULL,
2030 `biblionumber` int(11) NOT NULL,
2031 `weight` int(9) NOT NULL default '1',
2032 PRIMARY KEY (`term`,`biblionumber`),
2033 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2034 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2035 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2036 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2037 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2038 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2041 -- Table structure for table `userflags`
2044 DROP TABLE IF EXISTS `userflags`;
2045 CREATE TABLE `userflags` (
2046 `bit` int(11) NOT NULL default 0,
2047 `flag` varchar(30) default NULL,
2048 `flagdesc` varchar(255) default NULL,
2049 `defaulton` int(11) default NULL,
2051 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2054 -- Table structure for table `virtualshelves`
2057 DROP TABLE IF EXISTS `virtualshelves`;
2058 CREATE TABLE `virtualshelves` (
2059 `shelfnumber` int(11) NOT NULL auto_increment,
2060 `shelfname` varchar(255) default NULL,
2061 `owner` varchar(80) default NULL,
2062 `category` varchar(1) default NULL,
2063 `sortfield` varchar(16) default NULL,
2064 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2065 PRIMARY KEY (`shelfnumber`)
2066 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2069 -- Table structure for table `virtualshelfcontents`
2072 DROP TABLE IF EXISTS `virtualshelfcontents`;
2073 CREATE TABLE `virtualshelfcontents` (
2074 `shelfnumber` int(11) NOT NULL default 0,
2075 `biblionumber` int(11) NOT NULL default 0,
2076 `flags` int(11) default NULL,
2077 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2078 KEY `shelfnumber` (`shelfnumber`),
2079 KEY `biblionumber` (`biblionumber`),
2080 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2081 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2082 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2085 -- Table structure for table `z3950servers`
2088 DROP TABLE IF EXISTS `z3950servers`;
2089 CREATE TABLE `z3950servers` (
2090 `host` varchar(255) default NULL,
2091 `port` int(11) default NULL,
2092 `db` varchar(255) default NULL,
2093 `userid` varchar(255) default NULL,
2094 `password` varchar(255) default NULL,
2096 `id` int(11) NOT NULL auto_increment,
2097 `checked` smallint(6) default NULL,
2098 `rank` int(11) default NULL,
2099 `syntax` varchar(80) default NULL,
2101 `position` enum('primary','secondary','') NOT NULL default 'primary',
2102 `type` enum('zed','opensearch') NOT NULL default 'zed',
2103 `encoding` text default NULL,
2104 `description` text NOT NULL,
2106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2109 -- Table structure for table `zebraqueue`
2112 DROP TABLE IF EXISTS `zebraqueue`;
2113 CREATE TABLE `zebraqueue` (
2114 `id` int(11) NOT NULL auto_increment,
2115 `biblio_auth_number` int(11) NOT NULL default '0',
2116 `operation` char(20) NOT NULL default '',
2117 `server` char(20) NOT NULL default '',
2118 `done` int(11) NOT NULL default '0',
2119 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2121 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2122 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2124 DROP TABLE IF EXISTS `services_throttle`;
2125 CREATE TABLE `services_throttle` (
2126 `service_type` varchar(10) NOT NULL default '',
2127 `service_count` varchar(45) default NULL,
2128 PRIMARY KEY (`service_type`)
2129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2131 -- http://www.w3.org/International/articles/language-tags/
2134 DROP TABLE IF EXISTS language_subtag_registry;
2135 CREATE TABLE language_subtag_registry (
2137 type varchar(25), -- language-script-region-variant-extension-privateuse
2138 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2140 KEY `subtag` (`subtag`)
2141 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2143 -- TODO: add suppress_scripts
2144 -- this maps three letter codes defined in iso639.2 back to their
2145 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2146 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2147 CREATE TABLE language_rfc4646_to_iso639 (
2148 rfc4646_subtag varchar(25),
2149 iso639_2_code varchar(25),
2150 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2153 DROP TABLE IF EXISTS language_descriptions;
2154 CREATE TABLE language_descriptions (
2158 description varchar(255),
2160 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2162 -- bi-directional support, keyed by script subcode
2163 DROP TABLE IF EXISTS language_script_bidi;
2164 CREATE TABLE language_script_bidi (
2165 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2166 bidi varchar(3), -- rtl ltr
2167 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2170 -- TODO: need to map language subtags to script subtags for detection
2171 -- of bidi when script is not specified (like ar, he)
2172 DROP TABLE IF EXISTS language_script_mapping;
2173 CREATE TABLE language_script_mapping (
2174 language_subtag varchar(25),
2175 script_subtag varchar(25),
2176 KEY `language_subtag` (`language_subtag`)
2177 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2179 DROP TABLE IF EXISTS `permissions`;
2180 CREATE TABLE `permissions` (
2181 `module_bit` int(11) NOT NULL DEFAULT 0,
2182 `code` varchar(64) DEFAULT NULL,
2183 `description` varchar(255) DEFAULT NULL,
2184 PRIMARY KEY (`module_bit`, `code`),
2185 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2186 ON DELETE CASCADE ON UPDATE CASCADE
2187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2189 DROP TABLE IF EXISTS `serialitems`;
2190 CREATE TABLE `serialitems` (
2191 `itemnumber` int(11) NOT NULL,
2192 `serialid` int(11) NOT NULL,
2193 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2194 KEY `serialitems_sfk_1` (`serialid`),
2195 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2196 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2198 DROP TABLE IF EXISTS `user_permissions`;
2199 CREATE TABLE `user_permissions` (
2200 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2201 `module_bit` int(11) NOT NULL DEFAULT 0,
2202 `code` varchar(64) DEFAULT NULL,
2203 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2204 ON DELETE CASCADE ON UPDATE CASCADE,
2205 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2206 ON DELETE CASCADE ON UPDATE CASCADE
2207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2210 -- Table structure for table `tmp_holdsqueue`
2213 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2214 CREATE TABLE `tmp_holdsqueue` (
2215 `biblionumber` int(11) default NULL,
2216 `itemnumber` int(11) default NULL,
2217 `barcode` varchar(20) default NULL,
2218 `surname` mediumtext NOT NULL,
2221 `borrowernumber` int(11) NOT NULL,
2222 `cardnumber` varchar(16) default NULL,
2223 `reservedate` date default NULL,
2225 `itemcallnumber` varchar(30) default NULL,
2226 `holdingbranch` varchar(10) default NULL,
2227 `pickbranch` varchar(10) default NULL,
2229 `item_level_request` tinyint(4) NOT NULL default 0
2230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2233 -- Table structure for table `message_queue`
2236 DROP TABLE IF EXISTS `message_queue`;
2237 CREATE TABLE `message_queue` (
2238 `message_id` int(11) NOT NULL auto_increment,
2239 `borrowernumber` int(11) default NULL,
2242 `message_transport_type` varchar(20) NOT NULL,
2243 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2244 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2245 `to_address` mediumtext,
2246 `from_address` mediumtext,
2247 `content_type` text,
2248 KEY `message_id` (`message_id`),
2249 KEY `borrowernumber` (`borrowernumber`),
2250 KEY `message_transport_type` (`message_transport_type`),
2251 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2252 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2253 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2256 -- Table structure for table `message_transport_types`
2259 DROP TABLE IF EXISTS `message_transport_types`;
2260 CREATE TABLE `message_transport_types` (
2261 `message_transport_type` varchar(20) NOT NULL,
2262 PRIMARY KEY (`message_transport_type`)
2263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2266 -- Table structure for table `message_attributes`
2269 DROP TABLE IF EXISTS `message_attributes`;
2270 CREATE TABLE `message_attributes` (
2271 `message_attribute_id` int(11) NOT NULL auto_increment,
2272 `message_name` varchar(20) NOT NULL default '',
2273 `takes_days` tinyint(1) NOT NULL default '0',
2274 PRIMARY KEY (`message_attribute_id`),
2275 UNIQUE KEY `message_name` (`message_name`)
2276 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2279 -- Table structure for table `message_transports`
2282 DROP TABLE IF EXISTS `message_transports`;
2283 CREATE TABLE `message_transports` (
2284 `message_attribute_id` int(11) NOT NULL,
2285 `message_transport_type` varchar(20) NOT NULL,
2286 `is_digest` tinyint(1) NOT NULL default '0',
2287 `letter_module` varchar(20) NOT NULL default '',
2288 `letter_code` varchar(20) NOT NULL default '',
2289 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2290 KEY `message_transport_type` (`message_transport_type`),
2291 KEY `letter_module` (`letter_module`,`letter_code`),
2292 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2293 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2294 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2295 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2298 -- Table structure for table `borrower_message_preferences`
2301 DROP TABLE IF EXISTS `borrower_message_preferences`;
2302 CREATE TABLE `borrower_message_preferences` (
2303 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2304 `borrowernumber` int(11) NOT NULL default '0',
2305 `message_attribute_id` int(11) default '0',
2306 `days_in_advance` int(11) default '0',
2307 `wants_digest` tinyint(1) NOT NULL default '0',
2308 PRIMARY KEY (`borrower_message_preference_id`),
2309 KEY `borrowernumber` (`borrowernumber`),
2310 KEY `message_attribute_id` (`message_attribute_id`),
2311 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2312 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
2313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2316 -- Table structure for table `borrower_message_transport_preferences`
2319 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2320 CREATE TABLE `borrower_message_transport_preferences` (
2321 `borrower_message_preference_id` int(11) NOT NULL default '0',
2322 `message_transport_type` varchar(20) NOT NULL default '0',
2323 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2324 KEY `message_transport_type` (`message_transport_type`),
2325 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,
2326 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
2327 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2330 -- Table structure for the table branch_transfer_limits
2333 DROP TABLE IF EXISTS `branch_transfer_limits`;
2334 CREATE TABLE branch_transfer_limits (
2335 limitId int(8) NOT NULL auto_increment,
2336 toBranch varchar(4) NOT NULL,
2337 fromBranch varchar(4) NOT NULL,
2338 itemtype varchar(4) NOT NULL,
2339 PRIMARY KEY (limitId)
2340 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2342 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2343 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2344 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2345 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2346 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2347 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2348 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2349 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;