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`),
423 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
424 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
427 -- Table structure for table `borrowers`
430 DROP TABLE IF EXISTS `borrowers`;
431 CREATE TABLE `borrowers` (
432 `borrowernumber` int(11) NOT NULL auto_increment,
433 `cardnumber` varchar(16) default NULL,
434 `surname` mediumtext NOT NULL,
437 `othernames` mediumtext,
439 `streetnumber` varchar(10) default NULL,
440 `streettype` varchar(50) default NULL,
441 `address` mediumtext NOT NULL,
443 `city` mediumtext NOT NULL,
444 `zipcode` varchar(25) default NULL,
447 `mobile` varchar(50) default NULL,
451 `B_streetnumber` varchar(10) default NULL,
452 `B_streettype` varchar(50) default NULL,
453 `B_address` varchar(100) default NULL,
455 `B_zipcode` varchar(25) default NULL,
457 `B_phone` mediumtext,
458 `dateofbirth` date default NULL,
459 `branchcode` varchar(10) NOT NULL default '',
460 `categorycode` varchar(10) NOT NULL default '',
461 `dateenrolled` date default NULL,
462 `dateexpiry` date default NULL,
463 `gonenoaddress` tinyint(1) default NULL,
464 `lost` tinyint(1) default NULL,
465 `debarred` tinyint(1) default NULL,
466 `contactname` mediumtext,
467 `contactfirstname` text,
469 `guarantorid` int(11) default NULL,
470 `borrowernotes` mediumtext,
471 `relationship` varchar(100) default NULL,
472 `ethnicity` varchar(50) default NULL,
473 `ethnotes` varchar(255) default NULL,
474 `sex` varchar(1) default NULL,
475 `password` varchar(30) default NULL,
476 `flags` int(11) default NULL,
477 `userid` varchar(30) default NULL,
478 `opacnote` mediumtext,
479 `contactnote` varchar(255) default NULL,
480 `sort1` varchar(80) default NULL,
481 `sort2` varchar(80) default NULL,
482 `altcontactfirstname` varchar(255) default NULL,
483 `altcontactsurname` varchar(255) default NULL,
484 `altcontactaddress1` varchar(255) default NULL,
485 `altcontactaddress2` varchar(255) default NULL,
486 `altcontactaddress3` varchar(255) default NULL,
487 `altcontactzipcode` varchar(50) default NULL,
488 `altcontactphone` varchar(50) default NULL,
489 `smsalertnumber` varchar(50) default NULL,
490 UNIQUE KEY `cardnumber` (`cardnumber`),
491 PRIMARY KEY `borrowernumber` (`borrowernumber`),
492 KEY `categorycode` (`categorycode`),
493 KEY `branchcode` (`branchcode`),
494 KEY `userid` (`userid`),
495 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
496 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
497 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
500 -- Table structure for table `borrower_attribute_types`
503 DROP TABLE IF EXISTS `borrower_attribute_types`;
504 CREATE TABLE `borrower_attribute_types` (
505 `code` varchar(10) NOT NULL,
506 `description` varchar(255) NOT NULL,
507 `repeatable` tinyint(1) NOT NULL default 0,
508 `unique_id` tinyint(1) NOT NULL default 0,
509 `opac_display` tinyint(1) NOT NULL default 0,
510 `password_allowed` tinyint(1) NOT NULL default 0,
511 `staff_searchable` tinyint(1) NOT NULL default 0,
512 `authorised_value_category` varchar(10) default NULL,
514 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
517 -- Table structure for table `borrower_attributes`
520 DROP TABLE IF EXISTS `borrower_attributes`;
521 CREATE TABLE `borrower_attributes` (
522 `borrowernumber` int(11) NOT NULL,
523 `code` varchar(10) NOT NULL,
524 `attribute` varchar(30) default NULL,
525 `password` varchar(30) default NULL,
526 KEY `borrowernumber` (`borrowernumber`),
527 KEY `code_attribute` (`code`, `attribute`),
528 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
529 ON DELETE CASCADE ON UPDATE CASCADE,
530 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
531 ON DELETE CASCADE ON UPDATE CASCADE
532 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
535 -- Table structure for table `branchcategories`
538 DROP TABLE IF EXISTS `branchcategories`;
539 CREATE TABLE `branchcategories` (
540 `categorycode` varchar(10) NOT NULL default '',
541 `categoryname` varchar(32),
542 `codedescription` mediumtext,
543 `categorytype` varchar(16),
544 PRIMARY KEY (`categorycode`)
545 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
548 -- Table structure for table `branches`
551 DROP TABLE IF EXISTS `branches`;
552 CREATE TABLE `branches` (
553 `branchcode` varchar(10) NOT NULL default '',
554 `branchname` mediumtext NOT NULL,
555 `branchaddress1` mediumtext,
556 `branchaddress2` mediumtext,
557 `branchaddress3` mediumtext,
558 `branchphone` mediumtext,
559 `branchfax` mediumtext,
560 `branchemail` mediumtext,
561 `issuing` tinyint(4) default NULL,
562 `branchip` varchar(15) default NULL,
563 `branchprinter` varchar(100) default NULL,
564 UNIQUE KEY `branchcode` (`branchcode`)
565 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
568 -- Table structure for table `branchrelations`
571 DROP TABLE IF EXISTS `branchrelations`;
572 CREATE TABLE `branchrelations` (
573 `branchcode` varchar(10) NOT NULL default '',
574 `categorycode` varchar(10) NOT NULL default '',
575 PRIMARY KEY (`branchcode`,`categorycode`),
576 KEY `branchcode` (`branchcode`),
577 KEY `categorycode` (`categorycode`),
578 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
579 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
580 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
583 -- Table structure for table `branchtransfers`
586 DROP TABLE IF EXISTS `branchtransfers`;
587 CREATE TABLE `branchtransfers` (
588 `itemnumber` int(11) NOT NULL default 0,
589 `datesent` datetime default NULL,
590 `frombranch` varchar(10) NOT NULL default '',
591 `datearrived` datetime default NULL,
592 `tobranch` varchar(10) NOT NULL default '',
593 `comments` mediumtext,
594 KEY `frombranch` (`frombranch`),
595 KEY `tobranch` (`tobranch`),
596 KEY `itemnumber` (`itemnumber`),
597 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
598 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
599 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
600 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
604 -- Table structure for table `browser`
606 DROP TABLE IF EXISTS `browser`;
607 CREATE TABLE `browser` (
608 `level` int(11) NOT NULL,
609 `classification` varchar(20) NOT NULL,
610 `description` varchar(255) NOT NULL,
611 `number` bigint(20) NOT NULL,
612 `endnode` tinyint(4) NOT NULL
613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
616 -- Table structure for table `categories`
619 DROP TABLE IF EXISTS `categories`;
620 CREATE TABLE `categories` (
621 `categorycode` varchar(10) NOT NULL default '',
622 `description` mediumtext,
623 `enrolmentperiod` smallint(6) default NULL,
624 `upperagelimit` smallint(6) default NULL,
625 `dateofbirthrequired` tinyint(1) default NULL,
626 `finetype` varchar(30) default NULL,
627 `bulk` tinyint(1) default NULL,
628 `enrolmentfee` decimal(28,6) default NULL,
629 `overduenoticerequired` tinyint(1) default NULL,
630 `issuelimit` smallint(6) default NULL,
631 `reservefee` decimal(28,6) default NULL,
632 `category_type` varchar(1) NOT NULL default 'A',
633 PRIMARY KEY (`categorycode`),
634 UNIQUE KEY `categorycode` (`categorycode`)
635 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
638 -- Table structure for table `borrower_branch_circ_rules`
641 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
642 CREATE TABLE `branch_borrower_circ_rules` (
643 `branchcode` VARCHAR(10) NOT NULL,
644 `categorycode` VARCHAR(10) NOT NULL,
645 `maxissueqty` int(4) default NULL,
646 PRIMARY KEY (`categorycode`, `branchcode`),
647 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
648 ON DELETE CASCADE ON UPDATE CASCADE,
649 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
650 ON DELETE CASCADE ON UPDATE CASCADE
651 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
654 -- Table structure for table `default_borrower_circ_rules`
657 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
658 CREATE TABLE `default_borrower_circ_rules` (
659 `categorycode` VARCHAR(10) NOT NULL,
660 `maxissueqty` int(4) default NULL,
661 PRIMARY KEY (`categorycode`),
662 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
663 ON DELETE CASCADE ON UPDATE CASCADE
664 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
667 -- Table structure for table `default_branch_circ_rules`
670 DROP TABLE IF EXISTS `default_branch_circ_rules`;
671 CREATE TABLE `default_branch_circ_rules` (
672 `branchcode` VARCHAR(10) NOT NULL,
673 `maxissueqty` int(4) default NULL,
674 PRIMARY KEY (`branchcode`),
675 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
676 ON DELETE CASCADE ON UPDATE CASCADE
677 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
680 -- Table structure for table `default_circ_rules`
683 DROP TABLE IF EXISTS `default_circ_rules`;
684 CREATE TABLE `default_circ_rules` (
685 `singleton` enum('singleton') NOT NULL default 'singleton',
686 `maxissueqty` int(4) default NULL,
687 PRIMARY KEY (`singleton`)
688 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
691 -- Table structure for table `cities`
694 DROP TABLE IF EXISTS `cities`;
695 CREATE TABLE `cities` (
696 `cityid` int(11) NOT NULL auto_increment,
697 `city_name` varchar(100) NOT NULL default '',
698 `city_zipcode` varchar(20) default NULL,
699 PRIMARY KEY (`cityid`)
700 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
703 -- Table structure for table `class_sort_rules`
706 DROP TABLE IF EXISTS `class_sort_rules`;
707 CREATE TABLE `class_sort_rules` (
708 `class_sort_rule` varchar(10) NOT NULL default '',
709 `description` mediumtext,
710 `sort_routine` varchar(30) NOT NULL default '',
711 PRIMARY KEY (`class_sort_rule`),
712 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
713 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
716 -- Table structure for table `class_sources`
719 DROP TABLE IF EXISTS `class_sources`;
720 CREATE TABLE `class_sources` (
721 `cn_source` varchar(10) NOT NULL default '',
722 `description` mediumtext,
723 `used` tinyint(4) NOT NULL default 0,
724 `class_sort_rule` varchar(10) NOT NULL default '',
725 PRIMARY KEY (`cn_source`),
726 UNIQUE KEY `cn_source_idx` (`cn_source`),
727 KEY `used_idx` (`used`),
728 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
732 -- Table structure for table `currency`
735 DROP TABLE IF EXISTS `currency`;
736 CREATE TABLE `currency` (
737 `currency` varchar(10) NOT NULL default '',
738 `symbol` varchar(5) default NULL,
739 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
740 `rate` float(7,5) default NULL,
741 PRIMARY KEY (`currency`)
742 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
745 -- Table structure for table `deletedbiblio`
748 DROP TABLE IF EXISTS `deletedbiblio`;
749 CREATE TABLE `deletedbiblio` (
750 `biblionumber` int(11) NOT NULL default 0,
751 `frameworkcode` varchar(4) NOT NULL default '',
754 `unititle` mediumtext,
756 `serial` tinyint(1) default NULL,
757 `seriestitle` mediumtext,
758 `copyrightdate` smallint(6) default NULL,
759 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
760 `datecreated` DATE NOT NULL,
761 `abstract` mediumtext,
762 PRIMARY KEY (`biblionumber`),
763 KEY `blbnoidx` (`biblionumber`)
764 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
767 -- Table structure for table `deletedbiblioitems`
770 DROP TABLE IF EXISTS `deletedbiblioitems`;
771 CREATE TABLE `deletedbiblioitems` (
772 `biblioitemnumber` int(11) NOT NULL default 0,
773 `biblionumber` int(11) NOT NULL default 0,
776 `itemtype` varchar(10) default NULL,
777 `isbn` varchar(14) default NULL,
778 `issn` varchar(9) default NULL,
779 `publicationyear` text,
780 `publishercode` varchar(255) default NULL,
781 `volumedate` date default NULL,
783 `collectiontitle` mediumtext default NULL,
784 `collectionissn` text default NULL,
785 `collectionvolume` mediumtext default NULL,
786 `editionstatement` text default NULL,
787 `editionresponsibility` text default NULL,
788 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
789 `illus` varchar(255) default NULL,
790 `pages` varchar(255) default NULL,
792 `size` varchar(255) default NULL,
793 `place` varchar(255) default NULL,
794 `lccn` varchar(25) default NULL,
796 `url` varchar(255) default NULL,
797 `cn_source` varchar(10) default NULL,
798 `cn_class` varchar(30) default NULL,
799 `cn_item` varchar(10) default NULL,
800 `cn_suffix` varchar(10) default NULL,
801 `cn_sort` varchar(30) default NULL,
802 `totalissues` int(10),
803 `marcxml` longtext NOT NULL,
804 PRIMARY KEY (`biblioitemnumber`),
805 KEY `bibinoidx` (`biblioitemnumber`),
806 KEY `bibnoidx` (`biblionumber`),
808 KEY `publishercode` (`publishercode`)
809 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
812 -- Table structure for table `deletedborrowers`
815 DROP TABLE IF EXISTS `deletedborrowers`;
816 CREATE TABLE `deletedborrowers` (
817 `borrowernumber` int(11) NOT NULL default 0,
818 `cardnumber` varchar(9) NOT NULL default '',
819 `surname` mediumtext NOT NULL,
822 `othernames` mediumtext,
824 `streetnumber` varchar(10) default NULL,
825 `streettype` varchar(50) default NULL,
826 `address` mediumtext NOT NULL,
828 `city` mediumtext NOT NULL,
829 `zipcode` varchar(25) default NULL,
832 `mobile` varchar(50) default NULL,
836 `B_streetnumber` varchar(10) default NULL,
837 `B_streettype` varchar(50) default NULL,
838 `B_address` varchar(100) default NULL,
840 `B_zipcode` varchar(25) default NULL,
842 `B_phone` mediumtext,
843 `dateofbirth` date default NULL,
844 `branchcode` varchar(10) NOT NULL default '',
845 `categorycode` varchar(10) default NULL,
846 `dateenrolled` date default NULL,
847 `dateexpiry` date default NULL,
848 `gonenoaddress` tinyint(1) default NULL,
849 `lost` tinyint(1) default NULL,
850 `debarred` tinyint(1) default NULL,
851 `contactname` mediumtext,
852 `contactfirstname` text,
854 `guarantorid` int(11) default NULL,
855 `borrowernotes` mediumtext,
856 `relationship` varchar(100) default NULL,
857 `ethnicity` varchar(50) default NULL,
858 `ethnotes` varchar(255) default NULL,
859 `sex` varchar(1) default NULL,
860 `password` varchar(30) default NULL,
861 `flags` int(11) default NULL,
862 `userid` varchar(30) default NULL,
863 `opacnote` mediumtext,
864 `contactnote` varchar(255) default NULL,
865 `sort1` varchar(80) default NULL,
866 `sort2` varchar(80) default NULL,
867 `altcontactfirstname` varchar(255) default NULL,
868 `altcontactsurname` varchar(255) default NULL,
869 `altcontactaddress1` varchar(255) default NULL,
870 `altcontactaddress2` varchar(255) default NULL,
871 `altcontactaddress3` varchar(255) default NULL,
872 `altcontactzipcode` varchar(50) default NULL,
873 `altcontactphone` varchar(50) default NULL,
874 KEY `borrowernumber` (`borrowernumber`),
875 KEY `cardnumber` (`cardnumber`)
876 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
879 -- Table structure for table `deleteditems`
882 DROP TABLE IF EXISTS `deleteditems`;
883 CREATE TABLE `deleteditems` (
884 `itemnumber` int(11) NOT NULL default 0,
885 `biblionumber` int(11) NOT NULL default 0,
886 `biblioitemnumber` int(11) NOT NULL default 0,
887 `barcode` varchar(20) default NULL,
888 `dateaccessioned` date default NULL,
889 `booksellerid` mediumtext default NULL,
890 `homebranch` varchar(10) default NULL,
891 `price` decimal(8,2) default NULL,
892 `replacementprice` decimal(8,2) default NULL,
893 `replacementpricedate` date default NULL,
894 `datelastborrowed` date default NULL,
895 `datelastseen` date default NULL,
896 `stack` tinyint(1) default NULL,
897 `notforloan` tinyint(1) NOT NULL default 0,
898 `damaged` tinyint(1) NOT NULL default 0,
899 `itemlost` tinyint(1) NOT NULL default 0,
900 `wthdrawn` tinyint(1) NOT NULL default 0,
901 `itemcallnumber` varchar(30) default NULL,
902 `issues` smallint(6) default NULL,
903 `renewals` smallint(6) default NULL,
904 `reserves` smallint(6) default NULL,
905 `restricted` tinyint(1) default NULL,
906 `itemnotes` mediumtext,
907 `holdingbranch` varchar(10) default NULL,
908 `paidfor` mediumtext,
909 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
910 `location` varchar(80) default NULL,
911 `onloan` date default NULL,
912 `cn_source` varchar(10) default NULL,
913 `cn_sort` varchar(30) default NULL,
914 `ccode` varchar(10) default NULL,
915 `materials` varchar(10) default NULL,
916 `uri` varchar(255) default NULL,
917 `itype` varchar(10) default NULL,
918 `more_subfields_xml` longtext default NULL,
919 `enumchron` varchar(80) default NULL,
920 `copynumber` varchar(32) default NULL,
922 PRIMARY KEY (`itemnumber`),
923 KEY `delitembarcodeidx` (`barcode`),
924 KEY `delitembinoidx` (`biblioitemnumber`),
925 KEY `delitembibnoidx` (`biblionumber`),
926 KEY `delhomebranch` (`homebranch`),
927 KEY `delholdingbranch` (`holdingbranch`)
928 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
931 -- Table structure for table `ethnicity`
934 DROP TABLE IF EXISTS `ethnicity`;
935 CREATE TABLE `ethnicity` (
936 `code` varchar(10) NOT NULL default '',
937 `name` varchar(255) default NULL,
939 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
942 -- Table structure for table `hold_fill_targets`
945 DROP TABLE IF EXISTS `hold_fill_targets`;
946 CREATE TABLE hold_fill_targets (
947 `borrowernumber` int(11) NOT NULL,
948 `biblionumber` int(11) NOT NULL,
949 `itemnumber` int(11) NOT NULL,
950 `source_branchcode` varchar(10) default NULL,
951 `item_level_request` tinyint(4) NOT NULL default 0,
952 PRIMARY KEY `itemnumber` (`itemnumber`),
953 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
954 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
955 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
956 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
957 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
958 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
959 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
960 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
961 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
962 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
965 -- Table structure for table `import_batches`
968 DROP TABLE IF EXISTS `import_batches`;
969 CREATE TABLE `import_batches` (
970 `import_batch_id` int(11) NOT NULL auto_increment,
971 `matcher_id` int(11) default NULL,
972 `template_id` int(11) default NULL,
973 `branchcode` varchar(10) default NULL,
974 `num_biblios` int(11) NOT NULL default 0,
975 `num_items` int(11) NOT NULL default 0,
976 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
977 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
978 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
979 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
980 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
981 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
982 `file_name` varchar(100),
983 `comments` mediumtext,
984 PRIMARY KEY (`import_batch_id`),
985 KEY `branchcode` (`branchcode`)
986 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
989 -- Table structure for table `import_records`
992 DROP TABLE IF EXISTS `import_records`;
993 CREATE TABLE `import_records` (
994 `import_record_id` int(11) NOT NULL auto_increment,
995 `import_batch_id` int(11) NOT NULL,
996 `branchcode` varchar(10) default NULL,
997 `record_sequence` int(11) NOT NULL default 0,
998 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
999 `import_date` DATE default NULL,
1000 `marc` longblob NOT NULL,
1001 `marcxml` longtext NOT NULL,
1002 `marcxml_old` longtext NOT NULL,
1003 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1004 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1005 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1006 `import_error` mediumtext,
1007 `encoding` varchar(40) NOT NULL default '',
1008 `z3950random` varchar(40) default NULL,
1009 PRIMARY KEY (`import_record_id`),
1010 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1011 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1012 KEY `branchcode` (`branchcode`),
1013 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1017 -- Table structure for `import_record_matches`
1019 DROP TABLE IF EXISTS `import_record_matches`;
1020 CREATE TABLE `import_record_matches` (
1021 `import_record_id` int(11) NOT NULL,
1022 `candidate_match_id` int(11) NOT NULL,
1023 `score` int(11) NOT NULL default 0,
1024 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1025 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1026 KEY `record_score` (`import_record_id`, `score`)
1027 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1030 -- Table structure for table `import_biblios`
1033 DROP TABLE IF EXISTS `import_biblios`;
1034 CREATE TABLE `import_biblios` (
1035 `import_record_id` int(11) NOT NULL,
1036 `matched_biblionumber` int(11) default NULL,
1037 `control_number` varchar(25) default NULL,
1038 `original_source` varchar(25) default NULL,
1039 `title` varchar(128) default NULL,
1040 `author` varchar(80) default NULL,
1041 `isbn` varchar(14) default NULL,
1042 `issn` varchar(9) default NULL,
1043 `has_items` tinyint(1) NOT NULL default 0,
1044 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1045 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1046 KEY `matched_biblionumber` (`matched_biblionumber`),
1047 KEY `title` (`title`),
1049 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1052 -- Table structure for table `import_items`
1055 DROP TABLE IF EXISTS `import_items`;
1056 CREATE TABLE `import_items` (
1057 `import_items_id` int(11) NOT NULL auto_increment,
1058 `import_record_id` int(11) NOT NULL,
1059 `itemnumber` int(11) default NULL,
1060 `branchcode` varchar(10) default NULL,
1061 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1062 `marcxml` longtext NOT NULL,
1063 `import_error` mediumtext,
1064 PRIMARY KEY (`import_items_id`),
1065 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1066 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1067 KEY `itemnumber` (`itemnumber`),
1068 KEY `branchcode` (`branchcode`)
1069 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1072 -- Table structure for table `issues`
1075 DROP TABLE IF EXISTS `issues`;
1076 CREATE TABLE `issues` (
1077 `borrowernumber` int(11) default NULL,
1078 `itemnumber` int(11) default NULL,
1079 `date_due` date default NULL,
1080 `branchcode` varchar(10) default NULL,
1081 `issuingbranch` varchar(18) default NULL,
1082 `returndate` date default NULL,
1083 `lastreneweddate` date default NULL,
1084 `return` varchar(4) default NULL,
1085 `renewals` tinyint(4) default NULL,
1086 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1087 `issuedate` date default NULL,
1088 KEY `issuesborridx` (`borrowernumber`),
1089 KEY `issuesitemidx` (`itemnumber`),
1090 KEY `bordate` (`borrowernumber`,`timestamp`),
1091 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1092 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1093 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1096 -- Table structure for table `issuingrules`
1099 DROP TABLE IF EXISTS `issuingrules`;
1100 CREATE TABLE `issuingrules` (
1101 `categorycode` varchar(10) NOT NULL default '',
1102 `itemtype` varchar(10) NOT NULL default '',
1103 `restrictedtype` tinyint(1) default NULL,
1104 `rentaldiscount` decimal(28,6) default NULL,
1105 `reservecharge` decimal(28,6) default NULL,
1106 `fine` decimal(28,6) default NULL,
1107 `firstremind` int(11) default NULL,
1108 `chargeperiod` int(11) default NULL,
1109 `accountsent` int(11) default NULL,
1110 `chargename` varchar(100) default NULL,
1111 `maxissueqty` int(4) default NULL,
1112 `issuelength` int(4) default NULL,
1113 `branchcode` varchar(10) NOT NULL default '',
1114 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1115 KEY `categorycode` (`categorycode`),
1116 KEY `itemtype` (`itemtype`)
1117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1120 -- Table structure for table `items`
1123 DROP TABLE IF EXISTS `items`;
1124 CREATE TABLE `items` (
1125 `itemnumber` int(11) NOT NULL auto_increment,
1126 `biblionumber` int(11) NOT NULL default 0,
1127 `biblioitemnumber` int(11) NOT NULL default 0,
1128 `barcode` varchar(20) default NULL,
1129 `dateaccessioned` date default NULL,
1130 `booksellerid` mediumtext default NULL,
1131 `homebranch` varchar(10) default NULL,
1132 `price` decimal(8,2) default NULL,
1133 `replacementprice` decimal(8,2) default NULL,
1134 `replacementpricedate` date default NULL,
1135 `datelastborrowed` date default NULL,
1136 `datelastseen` date default NULL,
1137 `stack` tinyint(1) default NULL,
1138 `notforloan` tinyint(1) NOT NULL default 0,
1139 `damaged` tinyint(1) NOT NULL default 0,
1140 `itemlost` tinyint(1) NOT NULL default 0,
1141 `wthdrawn` tinyint(1) NOT NULL default 0,
1142 `itemcallnumber` varchar(30) default NULL,
1143 `issues` smallint(6) default NULL,
1144 `renewals` smallint(6) default NULL,
1145 `reserves` smallint(6) default NULL,
1146 `restricted` tinyint(1) default NULL,
1147 `itemnotes` mediumtext,
1148 `holdingbranch` varchar(10) default NULL,
1149 `paidfor` mediumtext,
1150 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1151 `location` varchar(80) default NULL,
1152 `onloan` date default NULL,
1153 `cn_source` varchar(10) default NULL,
1154 `cn_sort` varchar(30) default NULL,
1155 `ccode` varchar(10) default NULL,
1156 `materials` varchar(10) default NULL,
1157 `uri` varchar(255) default NULL,
1158 `itype` varchar(10) default NULL,
1159 `more_subfields_xml` longtext default NULL,
1160 `enumchron` varchar(80) default NULL,
1161 `copynumber` varchar(32) default NULL,
1162 PRIMARY KEY (`itemnumber`),
1163 UNIQUE KEY `itembarcodeidx` (`barcode`),
1164 KEY `itembinoidx` (`biblioitemnumber`),
1165 KEY `itembibnoidx` (`biblionumber`),
1166 KEY `homebranch` (`homebranch`),
1167 KEY `holdingbranch` (`holdingbranch`),
1168 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1169 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1170 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1171 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1174 -- Table structure for table `itemtypes`
1177 DROP TABLE IF EXISTS `itemtypes`;
1178 CREATE TABLE `itemtypes` (
1179 `itemtype` varchar(10) NOT NULL default '',
1180 `description` mediumtext,
1181 `renewalsallowed` smallint(6) default NULL,
1182 `rentalcharge` double(16,4) default NULL,
1183 `notforloan` smallint(6) default NULL,
1184 `imageurl` varchar(200) default NULL,
1186 PRIMARY KEY (`itemtype`),
1187 UNIQUE KEY `itemtype` (`itemtype`)
1188 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1191 -- Table structure for table `labels`
1194 DROP TABLE IF EXISTS `labels`;
1195 CREATE TABLE `labels` (
1196 `labelid` int(11) NOT NULL auto_increment,
1197 `batch_id` varchar(10) NOT NULL default 1,
1198 `itemnumber` varchar(100) NOT NULL default '',
1199 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1200 PRIMARY KEY (`labelid`)
1201 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1204 -- Table structure for table `labels_conf`
1207 DROP TABLE IF EXISTS `labels_conf`;
1208 CREATE TABLE `labels_conf` (
1209 `id` int(4) NOT NULL auto_increment,
1210 `barcodetype` char(100) default '',
1211 `title` int(1) default '0',
1212 `subtitle` int(1) default '0',
1213 `itemtype` int(1) default '0',
1214 `barcode` int(1) default '0',
1215 `dewey` int(1) default '0',
1216 `classification` int(1) default NULL,
1217 `subclass` int(1) default '0',
1218 `itemcallnumber` int(1) default '0',
1219 `author` int(1) default '0',
1220 `issn` int(1) default '0',
1221 `isbn` int(1) default '0',
1222 `startlabel` int(2) NOT NULL default '1',
1223 `printingtype` char(32) default 'BAR',
1224 `formatstring` varchar(64) default NULL,
1225 `layoutname` char(20) NOT NULL default 'TEST',
1226 `guidebox` int(1) default '0',
1227 `active` tinyint(1) default '1',
1228 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1229 `ccode` char(4) collate utf8_unicode_ci default NULL,
1230 `callnum_split` int(1) default NULL,
1231 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1233 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1236 -- Table structure for table `labels_profile`
1239 DROP TABLE IF EXISTS `labels_profile`;
1240 CREATE TABLE `labels_profile` (
1241 `tmpl_id` int(4) NOT NULL,
1242 `prof_id` int(4) NOT NULL,
1243 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1244 UNIQUE KEY `prof_id` (`prof_id`)
1245 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1248 -- Table structure for table `labels_templates`
1251 DROP TABLE IF EXISTS `labels_templates`;
1252 CREATE TABLE `labels_templates` (
1253 `tmpl_id` int(4) NOT NULL auto_increment,
1254 `tmpl_code` char(100) default '',
1255 `tmpl_desc` char(100) default '',
1256 `page_width` float default '0',
1257 `page_height` float default '0',
1258 `label_width` float default '0',
1259 `label_height` float default '0',
1260 `topmargin` float default '0',
1261 `leftmargin` float default '0',
1262 `cols` int(2) default '0',
1263 `rows` int(2) default '0',
1264 `colgap` float default '0',
1265 `rowgap` float default '0',
1266 `active` int(1) default NULL,
1267 `units` char(20) default 'PX',
1268 `fontsize` int(4) NOT NULL default '3',
1269 `font` char(10) NOT NULL default 'TR',
1270 PRIMARY KEY (`tmpl_id`)
1271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1274 -- Table structure for table `letter`
1277 DROP TABLE IF EXISTS `letter`;
1278 CREATE TABLE `letter` (
1279 `module` varchar(20) NOT NULL default '',
1280 `code` varchar(20) NOT NULL default '',
1281 `name` varchar(100) NOT NULL default '',
1282 `title` varchar(200) NOT NULL default '',
1284 PRIMARY KEY (`module`,`code`)
1285 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1288 -- Table structure for table `marc_subfield_structure`
1291 DROP TABLE IF EXISTS `marc_subfield_structure`;
1292 CREATE TABLE `marc_subfield_structure` (
1293 `tagfield` varchar(3) NOT NULL default '',
1294 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1295 `liblibrarian` varchar(255) NOT NULL default '',
1296 `libopac` varchar(255) NOT NULL default '',
1297 `repeatable` tinyint(4) NOT NULL default 0,
1298 `mandatory` tinyint(4) NOT NULL default 0,
1299 `kohafield` varchar(40) default NULL,
1300 `tab` tinyint(1) default NULL,
1301 `authorised_value` varchar(20) default NULL,
1302 `authtypecode` varchar(20) default NULL,
1303 `value_builder` varchar(80) default NULL,
1304 `isurl` tinyint(1) default NULL,
1305 `hidden` tinyint(1) default NULL,
1306 `frameworkcode` varchar(4) NOT NULL default '',
1307 `seealso` varchar(1100) default NULL,
1308 `link` varchar(80) default NULL,
1309 `defaultvalue` text default NULL,
1310 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1311 KEY `kohafield_2` (`kohafield`),
1312 KEY `tab` (`frameworkcode`,`tab`),
1313 KEY `kohafield` (`frameworkcode`,`kohafield`)
1314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1317 -- Table structure for table `marc_tag_structure`
1320 DROP TABLE IF EXISTS `marc_tag_structure`;
1321 CREATE TABLE `marc_tag_structure` (
1322 `tagfield` varchar(3) NOT NULL default '',
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 `authorised_value` varchar(10) default NULL,
1328 `frameworkcode` varchar(4) NOT NULL default '',
1329 PRIMARY KEY (`frameworkcode`,`tagfield`)
1330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1333 -- Table structure for table `marc_matchers`
1336 DROP TABLE IF EXISTS `marc_matchers`;
1337 CREATE TABLE `marc_matchers` (
1338 `matcher_id` int(11) NOT NULL auto_increment,
1339 `code` varchar(10) NOT NULL default '',
1340 `description` varchar(255) NOT NULL default '',
1341 `record_type` varchar(10) NOT NULL default 'biblio',
1342 `threshold` int(11) NOT NULL default 0,
1343 PRIMARY KEY (`matcher_id`),
1344 KEY `code` (`code`),
1345 KEY `record_type` (`record_type`)
1346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1349 -- Table structure for table `matchpoints`
1351 DROP TABLE IF EXISTS `matchpoints`;
1352 CREATE TABLE `matchpoints` (
1353 `matcher_id` int(11) NOT NULL,
1354 `matchpoint_id` int(11) NOT NULL auto_increment,
1355 `search_index` varchar(30) NOT NULL default '',
1356 `score` int(11) NOT NULL default 0,
1357 PRIMARY KEY (`matchpoint_id`),
1358 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1359 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1360 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1364 -- Table structure for table `matchpoint_components`
1366 DROP TABLE IF EXISTS `matchpoint_components`;
1367 CREATE TABLE `matchpoint_components` (
1368 `matchpoint_id` int(11) NOT NULL,
1369 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1370 sequence int(11) NOT NULL default 0,
1371 tag varchar(3) NOT NULL default '',
1372 subfields varchar(40) NOT NULL default '',
1373 offset int(4) NOT NULL default 0,
1374 length int(4) NOT NULL default 0,
1375 PRIMARY KEY (`matchpoint_component_id`),
1376 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1377 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1378 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1382 -- Table structure for table `matcher_component_norms`
1384 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1385 CREATE TABLE `matchpoint_component_norms` (
1386 `matchpoint_component_id` int(11) NOT NULL,
1387 `sequence` int(11) NOT NULL default 0,
1388 `norm_routine` varchar(50) NOT NULL default '',
1389 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1390 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1391 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1392 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1395 -- Table structure for table `matcher_matchpoints`
1397 DROP TABLE IF EXISTS `matcher_matchpoints`;
1398 CREATE TABLE `matcher_matchpoints` (
1399 `matcher_id` int(11) NOT NULL,
1400 `matchpoint_id` int(11) NOT NULL,
1401 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1402 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1403 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1404 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1405 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1408 -- Table structure for table `matchchecks`
1410 DROP TABLE IF EXISTS `matchchecks`;
1411 CREATE TABLE `matchchecks` (
1412 `matcher_id` int(11) NOT NULL,
1413 `matchcheck_id` int(11) NOT NULL auto_increment,
1414 `source_matchpoint_id` int(11) NOT NULL,
1415 `target_matchpoint_id` int(11) NOT NULL,
1416 PRIMARY KEY (`matchcheck_id`),
1417 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1418 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1419 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1420 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1421 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1422 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1423 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1426 -- Table structure for table `notifys`
1429 DROP TABLE IF EXISTS `notifys`;
1430 CREATE TABLE `notifys` (
1431 `notify_id` int(11) NOT NULL default 0,
1432 `borrowernumber` int(11) NOT NULL default 0,
1433 `itemnumber` int(11) NOT NULL default 0,
1434 `notify_date` date default NULL,
1435 `notify_send_date` date default NULL,
1436 `notify_level` int(1) NOT NULL default 0,
1437 `method` varchar(20) NOT NULL default ''
1438 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1441 -- Table structure for table `nozebra`
1444 DROP TABLE IF EXISTS `nozebra`;
1445 CREATE TABLE `nozebra` (
1446 `server` varchar(20) NOT NULL,
1447 `indexname` varchar(40) NOT NULL,
1448 `value` varchar(250) NOT NULL,
1449 `biblionumbers` longtext NOT NULL,
1450 KEY `indexname` (`server`,`indexname`),
1451 KEY `value` (`server`,`value`))
1452 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1455 -- Table structure for table `old_issues`
1458 DROP TABLE IF EXISTS `old_issues`;
1459 CREATE TABLE `old_issues` (
1460 `borrowernumber` int(11) default NULL,
1461 `itemnumber` int(11) default NULL,
1462 `date_due` date default NULL,
1463 `branchcode` varchar(10) default NULL,
1464 `issuingbranch` varchar(18) default NULL,
1465 `returndate` date default NULL,
1466 `lastreneweddate` date default NULL,
1467 `return` varchar(4) default NULL,
1468 `renewals` tinyint(4) default NULL,
1469 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1470 `issuedate` date default NULL,
1471 KEY `old_issuesborridx` (`borrowernumber`),
1472 KEY `old_issuesitemidx` (`itemnumber`),
1473 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1474 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1475 ON DELETE SET NULL ON UPDATE SET NULL,
1476 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1477 ON DELETE SET NULL ON UPDATE SET NULL
1478 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1481 -- Table structure for table `old_reserves`
1483 DROP TABLE IF EXISTS `old_reserves`;
1484 CREATE TABLE `old_reserves` (
1485 `borrowernumber` int(11) default NULL,
1486 `reservedate` date default NULL,
1487 `biblionumber` int(11) default NULL,
1488 `constrainttype` varchar(1) default NULL,
1489 `branchcode` varchar(10) default NULL,
1490 `notificationdate` date default NULL,
1491 `reminderdate` date default NULL,
1492 `cancellationdate` date default NULL,
1493 `reservenotes` mediumtext,
1494 `priority` smallint(6) default NULL,
1495 `found` varchar(1) default NULL,
1496 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1497 `itemnumber` int(11) default NULL,
1498 `waitingdate` date default NULL,
1499 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1500 KEY `old_reserves_biblionumber` (`biblionumber`),
1501 KEY `old_reserves_itemnumber` (`itemnumber`),
1502 KEY `old_reserves_branchcode` (`branchcode`),
1503 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1504 ON DELETE SET NULL ON UPDATE SET NULL,
1505 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1506 ON DELETE SET NULL ON UPDATE SET NULL,
1507 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1508 ON DELETE SET NULL ON UPDATE SET NULL
1509 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1512 -- Table structure for table `opac_news`
1515 DROP TABLE IF EXISTS `opac_news`;
1516 CREATE TABLE `opac_news` (
1517 `idnew` int(10) unsigned NOT NULL auto_increment,
1518 `title` varchar(250) NOT NULL default '',
1519 `new` text NOT NULL,
1520 `lang` varchar(25) NOT NULL default '',
1521 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1522 `expirationdate` date default NULL,
1523 `number` int(11) default NULL,
1524 PRIMARY KEY (`idnew`)
1525 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1528 -- Table structure for table `overduerules`
1531 DROP TABLE IF EXISTS `overduerules`;
1532 CREATE TABLE `overduerules` (
1533 `branchcode` varchar(10) NOT NULL default '',
1534 `categorycode` varchar(10) NOT NULL default '',
1535 `delay1` int(4) default 0,
1536 `letter1` varchar(20) default NULL,
1537 `debarred1` varchar(1) default 0,
1538 `delay2` int(4) default 0,
1539 `debarred2` varchar(1) default 0,
1540 `letter2` varchar(20) default NULL,
1541 `delay3` int(4) default 0,
1542 `letter3` varchar(20) default NULL,
1543 `debarred3` int(1) default 0,
1544 PRIMARY KEY (`branchcode`,`categorycode`)
1545 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1548 -- Table structure for table `patroncards`
1551 DROP TABLE IF EXISTS `patroncards`;
1552 CREATE TABLE `patroncards` (
1553 `cardid` int(11) NOT NULL auto_increment,
1554 `batch_id` varchar(10) NOT NULL default '1',
1555 `borrowernumber` int(11) NOT NULL,
1556 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1557 PRIMARY KEY (`cardid`),
1558 KEY `patroncards_ibfk_1` (`borrowernumber`),
1559 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1560 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1563 -- Table structure for table `patronimage`
1566 DROP TABLE IF EXISTS `patronimage`;
1567 CREATE TABLE `patronimage` (
1568 `cardnumber` varchar(16) NOT NULL,
1569 `mimetype` varchar(15) NOT NULL,
1570 `imagefile` mediumblob NOT NULL,
1571 PRIMARY KEY (`cardnumber`),
1572 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1573 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1576 -- Table structure for table `printers`
1579 DROP TABLE IF EXISTS `printers`;
1580 CREATE TABLE `printers` (
1581 `printername` varchar(40) NOT NULL default '',
1582 `printqueue` varchar(20) default NULL,
1583 `printtype` varchar(20) default NULL,
1584 PRIMARY KEY (`printername`)
1585 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1588 -- Table structure for table `printers_profile`
1591 DROP TABLE IF EXISTS `printers_profile`;
1592 CREATE TABLE `printers_profile` (
1593 `prof_id` int(4) NOT NULL auto_increment,
1594 `printername` varchar(40) NOT NULL,
1595 `tmpl_id` int(4) NOT NULL,
1596 `paper_bin` varchar(20) NOT NULL,
1597 `offset_horz` float default NULL,
1598 `offset_vert` float default NULL,
1599 `creep_horz` float default NULL,
1600 `creep_vert` float default NULL,
1601 `unit` char(20) NOT NULL default 'POINT',
1602 PRIMARY KEY (`prof_id`),
1603 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1604 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1605 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1608 -- Table structure for table `repeatable_holidays`
1611 DROP TABLE IF EXISTS `repeatable_holidays`;
1612 CREATE TABLE `repeatable_holidays` (
1613 `id` int(11) NOT NULL auto_increment,
1614 `branchcode` varchar(10) NOT NULL default '',
1615 `weekday` smallint(6) default NULL,
1616 `day` smallint(6) default NULL,
1617 `month` smallint(6) default NULL,
1618 `title` varchar(50) NOT NULL default '',
1619 `description` text NOT NULL,
1621 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1624 -- Table structure for table `reports_dictionary`
1627 DROP TABLE IF EXISTS `reports_dictionary`;
1628 CREATE TABLE reports_dictionary (
1629 `id` int(11) NOT NULL auto_increment,
1630 `name` varchar(255) default NULL,
1632 `date_created` datetime default NULL,
1633 `date_modified` datetime default NULL,
1635 `area` int(11) default NULL,
1637 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1640 -- Table structure for table `reserveconstraints`
1643 DROP TABLE IF EXISTS `reserveconstraints`;
1644 CREATE TABLE `reserveconstraints` (
1645 `borrowernumber` int(11) NOT NULL default 0,
1646 `reservedate` date default NULL,
1647 `biblionumber` int(11) NOT NULL default 0,
1648 `biblioitemnumber` int(11) default NULL,
1649 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1650 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1653 -- Table structure for table `reserves`
1656 DROP TABLE IF EXISTS `reserves`;
1657 CREATE TABLE `reserves` (
1658 `borrowernumber` int(11) NOT NULL default 0,
1659 `reservedate` date default NULL,
1660 `biblionumber` int(11) NOT NULL default 0,
1661 `constrainttype` varchar(1) default NULL,
1662 `branchcode` varchar(10) default NULL,
1663 `notificationdate` date default NULL,
1664 `reminderdate` date default NULL,
1665 `cancellationdate` date default NULL,
1666 `reservenotes` mediumtext,
1667 `priority` smallint(6) default NULL,
1668 `found` varchar(1) default NULL,
1669 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1670 `itemnumber` int(11) default NULL,
1671 `waitingdate` date default NULL,
1672 KEY `borrowernumber` (`borrowernumber`),
1673 KEY `biblionumber` (`biblionumber`),
1674 KEY `itemnumber` (`itemnumber`),
1675 KEY `branchcode` (`branchcode`),
1676 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1677 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1678 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1679 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1680 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1683 -- Table structure for table `reviews`
1686 DROP TABLE IF EXISTS `reviews`;
1687 CREATE TABLE `reviews` (
1688 `reviewid` int(11) NOT NULL auto_increment,
1689 `borrowernumber` int(11) default NULL,
1690 `biblionumber` int(11) default NULL,
1692 `approved` tinyint(4) default NULL,
1693 `datereviewed` datetime default NULL,
1694 PRIMARY KEY (`reviewid`)
1695 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1698 -- Table structure for table `roadtype`
1701 DROP TABLE IF EXISTS `roadtype`;
1702 CREATE TABLE `roadtype` (
1703 `roadtypeid` int(11) NOT NULL auto_increment,
1704 `road_type` varchar(100) NOT NULL default '',
1705 PRIMARY KEY (`roadtypeid`)
1706 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1709 -- Table structure for table `saved_sql`
1712 DROP TABLE IF EXISTS `saved_sql`;
1713 CREATE TABLE saved_sql (
1714 `id` int(11) NOT NULL auto_increment,
1715 `borrowernumber` int(11) default NULL,
1716 `date_created` datetime default NULL,
1717 `last_modified` datetime default NULL,
1719 `last_run` datetime default NULL,
1720 `report_name` varchar(255) default NULL,
1721 `type` varchar(255) default NULL,
1724 KEY boridx (`borrowernumber`)
1725 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1729 -- Table structure for `saved_reports`
1732 DROP TABLE IF EXISTS `saved_reports`;
1733 CREATE TABLE saved_reports (
1734 `id` int(11) NOT NULL auto_increment,
1735 `report_id` int(11) default NULL,
1737 `date_run` datetime default NULL,
1739 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1743 -- Table structure for table `serial`
1746 DROP TABLE IF EXISTS `serial`;
1747 CREATE TABLE `serial` (
1748 `serialid` int(11) NOT NULL auto_increment,
1749 `biblionumber` varchar(100) NOT NULL default '',
1750 `subscriptionid` varchar(100) NOT NULL default '',
1751 `serialseq` varchar(100) NOT NULL default '',
1752 `status` tinyint(4) NOT NULL default 0,
1753 `planneddate` date default NULL,
1755 `publisheddate` date default NULL,
1756 `itemnumber` text default NULL,
1757 `claimdate` date default NULL,
1758 `routingnotes` text,
1759 PRIMARY KEY (`serialid`)
1760 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1763 -- Table structure for table `sessions`
1766 DROP TABLE IF EXISTS sessions;
1767 CREATE TABLE sessions (
1768 `id` varchar(32) NOT NULL,
1769 `a_session` text NOT NULL,
1771 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1774 -- Table structure for table `special_holidays`
1777 DROP TABLE IF EXISTS `special_holidays`;
1778 CREATE TABLE `special_holidays` (
1779 `id` int(11) NOT NULL auto_increment,
1780 `branchcode` varchar(10) NOT NULL default '',
1781 `day` smallint(6) NOT NULL default 0,
1782 `month` smallint(6) NOT NULL default 0,
1783 `year` smallint(6) NOT NULL default 0,
1784 `isexception` smallint(1) NOT NULL default 1,
1785 `title` varchar(50) NOT NULL default '',
1786 `description` text NOT NULL,
1788 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1791 -- Table structure for table `statistics`
1794 DROP TABLE IF EXISTS `statistics`;
1795 CREATE TABLE `statistics` (
1796 `datetime` datetime default NULL,
1797 `branch` varchar(10) default NULL,
1798 `proccode` varchar(4) default NULL,
1799 `value` double(16,4) default NULL,
1800 `type` varchar(16) default NULL,
1802 `usercode` varchar(10) default NULL,
1803 `itemnumber` int(11) default NULL,
1804 `itemtype` varchar(10) default NULL,
1805 `borrowernumber` int(11) default NULL,
1806 `associatedborrower` int(11) default NULL,
1807 KEY `timeidx` (`datetime`)
1808 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1811 -- Table structure for table `stopwords`
1814 DROP TABLE IF EXISTS `stopwords`;
1815 CREATE TABLE `stopwords` (
1816 `word` varchar(255) default NULL
1817 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1820 -- Table structure for table `subscription`
1823 DROP TABLE IF EXISTS `subscription`;
1824 CREATE TABLE `subscription` (
1825 `biblionumber` int(11) NOT NULL default 0,
1826 `subscriptionid` int(11) NOT NULL auto_increment,
1827 `librarian` varchar(100) default '',
1828 `startdate` date default NULL,
1829 `aqbooksellerid` int(11) default 0,
1830 `cost` int(11) default 0,
1831 `aqbudgetid` int(11) default 0,
1832 `weeklength` int(11) default 0,
1833 `monthlength` int(11) default 0,
1834 `numberlength` int(11) default 0,
1835 `periodicity` tinyint(4) default 0,
1836 `dow` varchar(100) default '',
1837 `numberingmethod` varchar(100) default '',
1839 `status` varchar(100) NOT NULL default '',
1840 `add1` int(11) default 0,
1841 `every1` int(11) default 0,
1842 `whenmorethan1` int(11) default 0,
1843 `setto1` int(11) default NULL,
1844 `lastvalue1` int(11) default NULL,
1845 `add2` int(11) default 0,
1846 `every2` int(11) default 0,
1847 `whenmorethan2` int(11) default 0,
1848 `setto2` int(11) default NULL,
1849 `lastvalue2` int(11) default NULL,
1850 `add3` int(11) default 0,
1851 `every3` int(11) default 0,
1852 `innerloop1` int(11) default 0,
1853 `innerloop2` int(11) default 0,
1854 `innerloop3` int(11) default 0,
1855 `whenmorethan3` int(11) default 0,
1856 `setto3` int(11) default NULL,
1857 `lastvalue3` int(11) default NULL,
1858 `issuesatonce` tinyint(3) NOT NULL default 1,
1859 `firstacquidate` date default NULL,
1860 `manualhistory` tinyint(1) NOT NULL default 0,
1861 `irregularity` text,
1862 `letter` varchar(20) default NULL,
1863 `numberpattern` tinyint(3) default 0,
1864 `distributedto` text,
1865 `internalnotes` longtext,
1867 `branchcode` varchar(10) NOT NULL default '',
1868 `hemisphere` tinyint(3) default 0,
1869 `lastbranch` varchar(10),
1870 `serialsadditems` tinyint(1) NOT NULL default '0',
1871 PRIMARY KEY (`subscriptionid`)
1872 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1875 -- Table structure for table `subscriptionhistory`
1878 DROP TABLE IF EXISTS `subscriptionhistory`;
1879 CREATE TABLE `subscriptionhistory` (
1880 `biblionumber` int(11) NOT NULL default 0,
1881 `subscriptionid` int(11) NOT NULL default 0,
1882 `histstartdate` date default NULL,
1883 `enddate` date default NULL,
1884 `missinglist` longtext NOT NULL,
1885 `recievedlist` longtext NOT NULL,
1886 `opacnote` varchar(150) NOT NULL default '',
1887 `librariannote` varchar(150) NOT NULL default '',
1888 PRIMARY KEY (`subscriptionid`),
1889 KEY `biblionumber` (`biblionumber`)
1890 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1893 -- Table structure for table `subscriptionroutinglist`
1896 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1897 CREATE TABLE `subscriptionroutinglist` (
1898 `routingid` int(11) NOT NULL auto_increment,
1899 `borrowernumber` int(11) default NULL,
1900 `ranking` int(11) default NULL,
1901 `subscriptionid` int(11) default NULL,
1902 PRIMARY KEY (`routingid`)
1903 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1906 -- Table structure for table `suggestions`
1909 DROP TABLE IF EXISTS `suggestions`;
1910 CREATE TABLE `suggestions` (
1911 `suggestionid` int(8) NOT NULL auto_increment,
1912 `suggestedby` int(11) NOT NULL default 0,
1913 `managedby` int(11) default NULL,
1914 `STATUS` varchar(10) NOT NULL default '',
1916 `author` varchar(80) default NULL,
1917 `title` varchar(80) default NULL,
1918 `copyrightdate` smallint(6) default NULL,
1919 `publishercode` varchar(255) default NULL,
1920 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1921 `volumedesc` varchar(255) default NULL,
1922 `publicationyear` smallint(6) default 0,
1923 `place` varchar(255) default NULL,
1924 `isbn` varchar(10) default NULL,
1925 `mailoverseeing` smallint(1) default 0,
1926 `biblionumber` int(11) default NULL,
1928 PRIMARY KEY (`suggestionid`),
1929 KEY `suggestedby` (`suggestedby`),
1930 KEY `managedby` (`managedby`)
1931 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1934 -- Table structure for table `systempreferences`
1937 DROP TABLE IF EXISTS `systempreferences`;
1938 CREATE TABLE `systempreferences` (
1939 `variable` varchar(50) NOT NULL default '',
1941 `options` mediumtext,
1943 `type` varchar(20) default NULL,
1944 PRIMARY KEY (`variable`)
1945 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1948 -- Table structure for table `tags`
1951 DROP TABLE IF EXISTS `tags`;
1952 CREATE TABLE `tags` (
1953 `entry` varchar(255) NOT NULL default '',
1954 `weight` bigint(20) NOT NULL default 0,
1955 PRIMARY KEY (`entry`)
1956 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1959 -- Table structure for table `tags_all`
1962 DROP TABLE IF EXISTS `tags_all`;
1963 CREATE TABLE `tags_all` (
1964 `tag_id` int(11) NOT NULL auto_increment,
1965 `borrowernumber` int(11) NOT NULL,
1966 `biblionumber` int(11) NOT NULL,
1967 `term` varchar(255) NOT NULL,
1968 `language` int(4) default NULL,
1969 `date_created` datetime NOT NULL,
1970 PRIMARY KEY (`tag_id`),
1971 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1972 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1973 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1974 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1975 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1976 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1977 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1980 -- Table structure for table `tags_approval`
1983 DROP TABLE IF EXISTS `tags_approval`;
1984 CREATE TABLE `tags_approval` (
1985 `term` varchar(255) NOT NULL,
1986 `approved` int(1) NOT NULL default '0',
1987 `date_approved` datetime default NULL,
1988 `approved_by` int(11) default NULL,
1989 `weight_total` int(9) NOT NULL default '1',
1990 PRIMARY KEY (`term`),
1991 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1992 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1993 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1994 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1997 -- Table structure for table `tags_index`
2000 DROP TABLE IF EXISTS `tags_index`;
2001 CREATE TABLE `tags_index` (
2002 `term` varchar(255) NOT NULL,
2003 `biblionumber` int(11) NOT NULL,
2004 `weight` int(9) NOT NULL default '1',
2005 PRIMARY KEY (`term`,`biblionumber`),
2006 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2007 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2008 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2009 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2010 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2011 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2014 -- Table structure for table `userflags`
2017 DROP TABLE IF EXISTS `userflags`;
2018 CREATE TABLE `userflags` (
2019 `bit` int(11) NOT NULL default 0,
2020 `flag` varchar(30) default NULL,
2021 `flagdesc` varchar(255) default NULL,
2022 `defaulton` int(11) default NULL,
2024 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2027 -- Table structure for table `virtualshelves`
2030 DROP TABLE IF EXISTS `virtualshelves`;
2031 CREATE TABLE `virtualshelves` (
2032 `shelfnumber` int(11) NOT NULL auto_increment,
2033 `shelfname` varchar(255) default NULL,
2034 `owner` varchar(80) default NULL,
2035 `category` varchar(1) default NULL,
2036 `sortfield` varchar(16) default NULL,
2037 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2038 PRIMARY KEY (`shelfnumber`)
2039 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2042 -- Table structure for table `virtualshelfcontents`
2045 DROP TABLE IF EXISTS `virtualshelfcontents`;
2046 CREATE TABLE `virtualshelfcontents` (
2047 `shelfnumber` int(11) NOT NULL default 0,
2048 `biblionumber` int(11) NOT NULL default 0,
2049 `flags` int(11) default NULL,
2050 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2051 KEY `shelfnumber` (`shelfnumber`),
2052 KEY `biblionumber` (`biblionumber`),
2053 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2054 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2055 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2058 -- Table structure for table `z3950servers`
2061 DROP TABLE IF EXISTS `z3950servers`;
2062 CREATE TABLE `z3950servers` (
2063 `host` varchar(255) default NULL,
2064 `port` int(11) default NULL,
2065 `db` varchar(255) default NULL,
2066 `userid` varchar(255) default NULL,
2067 `password` varchar(255) default NULL,
2069 `id` int(11) NOT NULL auto_increment,
2070 `checked` smallint(6) default NULL,
2071 `rank` int(11) default NULL,
2072 `syntax` varchar(80) default NULL,
2074 `position` enum('primary','secondary','') NOT NULL default 'primary',
2075 `type` enum('zed','opensearch') NOT NULL default 'zed',
2076 `encoding` text default NULL,
2077 `description` text NOT NULL,
2079 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2082 -- Table structure for table `zebraqueue`
2085 DROP TABLE IF EXISTS `zebraqueue`;
2086 CREATE TABLE `zebraqueue` (
2087 `id` int(11) NOT NULL auto_increment,
2088 `biblio_auth_number` int(11) NOT NULL default '0',
2089 `operation` char(20) NOT NULL default '',
2090 `server` char(20) NOT NULL default '',
2091 `done` int(11) NOT NULL default '0',
2092 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2094 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2095 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2097 DROP TABLE IF EXISTS `services_throttle`;
2098 CREATE TABLE `services_throttle` (
2099 `service_type` varchar(10) NOT NULL default '',
2100 `service_count` varchar(45) default NULL,
2101 PRIMARY KEY (`service_type`)
2102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2104 -- http://www.w3.org/International/articles/language-tags/
2107 DROP TABLE IF EXISTS language_subtag_registry;
2108 CREATE TABLE language_subtag_registry (
2110 type varchar(25), -- language-script-region-variant-extension-privateuse
2111 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2113 KEY `subtag` (`subtag`)
2114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2116 -- TODO: add suppress_scripts
2117 -- this maps three letter codes defined in iso639.2 back to their
2118 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2119 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2120 CREATE TABLE language_rfc4646_to_iso639 (
2121 rfc4646_subtag varchar(25),
2122 iso639_2_code varchar(25),
2123 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2124 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2126 DROP TABLE IF EXISTS language_descriptions;
2127 CREATE TABLE language_descriptions (
2131 description varchar(255),
2133 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2135 -- bi-directional support, keyed by script subcode
2136 DROP TABLE IF EXISTS language_script_bidi;
2137 CREATE TABLE language_script_bidi (
2138 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2139 bidi varchar(3), -- rtl ltr
2140 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2141 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2143 -- TODO: need to map language subtags to script subtags for detection
2144 -- of bidi when script is not specified (like ar, he)
2145 DROP TABLE IF EXISTS language_script_mapping;
2146 CREATE TABLE language_script_mapping (
2147 language_subtag varchar(25),
2148 script_subtag varchar(25),
2149 KEY `language_subtag` (`language_subtag`)
2150 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2152 DROP TABLE IF EXISTS `permissions`;
2153 CREATE TABLE `permissions` (
2154 `module_bit` int(11) NOT NULL DEFAULT 0,
2155 `code` varchar(30) DEFAULT NULL,
2156 `description` varchar(255) DEFAULT NULL,
2157 PRIMARY KEY (`module_bit`, `code`),
2158 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2159 ON DELETE CASCADE ON UPDATE CASCADE
2160 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2162 DROP TABLE IF EXISTS `serialitems`;
2163 CREATE TABLE `serialitems` (
2164 `itemnumber` int(11) NOT NULL,
2165 `serialid` int(11) NOT NULL,
2166 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2167 KEY `serialitems_sfk_1` (`serialid`),
2168 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2169 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2171 DROP TABLE IF EXISTS `user_permissions`;
2172 CREATE TABLE `user_permissions` (
2173 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2174 `module_bit` int(11) NOT NULL DEFAULT 0,
2175 `code` varchar(30) DEFAULT NULL,
2176 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2177 ON DELETE CASCADE ON UPDATE CASCADE,
2178 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2179 ON DELETE CASCADE ON UPDATE CASCADE
2180 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2183 -- Table structure for table `tmp_holdsqueue`
2186 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2187 CREATE TABLE `tmp_holdsqueue` (
2188 `biblionumber` int(11) default NULL,
2189 `itemnumber` int(11) default NULL,
2190 `barcode` varchar(20) default NULL,
2191 `surname` mediumtext NOT NULL,
2194 `borrowernumber` int(11) NOT NULL,
2195 `cardnumber` varchar(16) default NULL,
2196 `reservedate` date default NULL,
2198 `itemcallnumber` varchar(30) default NULL,
2199 `holdingbranch` varchar(10) default NULL,
2200 `pickbranch` varchar(10) default NULL,
2202 `item_level_request` tinyint(4) NOT NULL default 0
2203 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2206 -- Table structure for table `message_queue`
2209 DROP TABLE IF EXISTS `message_queue`;
2210 CREATE TABLE `message_queue` (
2211 `message_id` int(11) NOT NULL auto_increment,
2212 `borrowernumber` int(11) default NULL,
2215 `message_transport_type` varchar(20) NOT NULL,
2216 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2217 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2218 `to_address` mediumtext,
2219 `from_address` mediumtext,
2220 `content_type` text,
2221 KEY `message_id` (`message_id`),
2222 KEY `borrowernumber` (`borrowernumber`),
2223 KEY `message_transport_type` (`message_transport_type`),
2224 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2225 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2226 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2229 -- Table structure for table `message_transport_types`
2232 DROP TABLE IF EXISTS `message_transport_types`;
2233 CREATE TABLE `message_transport_types` (
2234 `message_transport_type` varchar(20) NOT NULL,
2235 PRIMARY KEY (`message_transport_type`)
2236 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2239 -- Table structure for table `message_attributes`
2242 DROP TABLE IF EXISTS `message_attributes`;
2243 CREATE TABLE `message_attributes` (
2244 `message_attribute_id` int(11) NOT NULL auto_increment,
2245 `message_name` varchar(20) NOT NULL default '',
2246 `takes_days` tinyint(1) NOT NULL default '0',
2247 PRIMARY KEY (`message_attribute_id`),
2248 UNIQUE KEY `message_name` (`message_name`)
2249 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2252 -- Table structure for table `message_transports`
2255 DROP TABLE IF EXISTS `message_transports`;
2256 CREATE TABLE `message_transports` (
2257 `message_attribute_id` int(11) NOT NULL,
2258 `message_transport_type` varchar(20) NOT NULL,
2259 `is_digest` tinyint(1) NOT NULL default '0',
2260 `letter_module` varchar(20) NOT NULL default '',
2261 `letter_code` varchar(20) NOT NULL default '',
2262 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2263 KEY `message_transport_type` (`message_transport_type`),
2264 KEY `letter_module` (`letter_module`,`letter_code`),
2265 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2266 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2267 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2268 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2271 -- Table structure for table `borrower_message_preferences`
2274 DROP TABLE IF EXISTS `borrower_message_preferences`;
2275 CREATE TABLE `borrower_message_preferences` (
2276 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2277 `borrowernumber` int(11) NOT NULL default '0',
2278 `message_attribute_id` int(11) default '0',
2279 `days_in_advance` int(11) default '0',
2280 `wants_digest` tinyint(1) NOT NULL default '0',
2281 PRIMARY KEY (`borrower_message_preference_id`),
2282 KEY `borrowernumber` (`borrowernumber`),
2283 KEY `message_attribute_id` (`message_attribute_id`),
2284 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2285 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
2286 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2289 -- Table structure for table `borrower_message_transport_preferences`
2292 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2293 CREATE TABLE `borrower_message_transport_preferences` (
2294 `borrower_message_preference_id` int(11) NOT NULL default '0',
2295 `message_transport_type` varchar(20) NOT NULL default '0',
2296 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2297 KEY `message_transport_type` (`message_transport_type`),
2298 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,
2299 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
2300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2302 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2303 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2304 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2305 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2306 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2307 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2308 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2309 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;