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 CREATE TABLE hold_fill_targets (
946 `borrowernumber` int(11) NOT NULL,
947 `biblionumber` int(11) NOT NULL,
948 `itemnumber` int(11) NOT NULL,
949 `source_branchcode` varchar(10) default NULL,
950 `item_level_request` tinyint(4) NOT NULL default 0,
951 PRIMARY KEY `itemnumber` (`itemnumber`),
952 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
953 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
954 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
955 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
956 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
957 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
958 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
959 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
960 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
961 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
964 -- Table structure for table `import_batches`
967 DROP TABLE IF EXISTS `import_batches`;
968 CREATE TABLE `import_batches` (
969 `import_batch_id` int(11) NOT NULL auto_increment,
970 `matcher_id` int(11) default NULL,
971 `template_id` int(11) default NULL,
972 `branchcode` varchar(10) default NULL,
973 `num_biblios` int(11) NOT NULL default 0,
974 `num_items` int(11) NOT NULL default 0,
975 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
976 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
977 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
978 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
979 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
980 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
981 `file_name` varchar(100),
982 `comments` mediumtext,
983 PRIMARY KEY (`import_batch_id`),
984 KEY `branchcode` (`branchcode`)
985 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
988 -- Table structure for table `import_records`
991 DROP TABLE IF EXISTS `import_records`;
992 CREATE TABLE `import_records` (
993 `import_record_id` int(11) NOT NULL auto_increment,
994 `import_batch_id` int(11) NOT NULL,
995 `branchcode` varchar(10) default NULL,
996 `record_sequence` int(11) NOT NULL default 0,
997 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
998 `import_date` DATE default NULL,
999 `marc` longblob NOT NULL,
1000 `marcxml` longtext NOT NULL,
1001 `marcxml_old` longtext NOT NULL,
1002 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1003 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1004 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1005 `import_error` mediumtext,
1006 `encoding` varchar(40) NOT NULL default '',
1007 `z3950random` varchar(40) default NULL,
1008 PRIMARY KEY (`import_record_id`),
1009 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1010 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1011 KEY `branchcode` (`branchcode`),
1012 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1013 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1016 -- Table structure for `import_record_matches`
1018 DROP TABLE IF EXISTS `import_record_matches`;
1019 CREATE TABLE `import_record_matches` (
1020 `import_record_id` int(11) NOT NULL,
1021 `candidate_match_id` int(11) NOT NULL,
1022 `score` int(11) NOT NULL default 0,
1023 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1024 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1025 KEY `record_score` (`import_record_id`, `score`)
1026 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1029 -- Table structure for table `import_biblios`
1032 DROP TABLE IF EXISTS `import_biblios`;
1033 CREATE TABLE `import_biblios` (
1034 `import_record_id` int(11) NOT NULL,
1035 `matched_biblionumber` int(11) default NULL,
1036 `control_number` varchar(25) default NULL,
1037 `original_source` varchar(25) default NULL,
1038 `title` varchar(128) default NULL,
1039 `author` varchar(80) default NULL,
1040 `isbn` varchar(14) default NULL,
1041 `issn` varchar(9) default NULL,
1042 `has_items` tinyint(1) NOT NULL default 0,
1043 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1044 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1045 KEY `matched_biblionumber` (`matched_biblionumber`),
1046 KEY `title` (`title`),
1048 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1051 -- Table structure for table `import_items`
1054 DROP TABLE IF EXISTS `import_items`;
1055 CREATE TABLE `import_items` (
1056 `import_items_id` int(11) NOT NULL auto_increment,
1057 `import_record_id` int(11) NOT NULL,
1058 `itemnumber` int(11) default NULL,
1059 `branchcode` varchar(10) default NULL,
1060 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1061 `marcxml` longtext NOT NULL,
1062 `import_error` mediumtext,
1063 PRIMARY KEY (`import_items_id`),
1064 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1065 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1066 KEY `itemnumber` (`itemnumber`),
1067 KEY `branchcode` (`branchcode`)
1068 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1071 -- Table structure for table `issues`
1074 DROP TABLE IF EXISTS `issues`;
1075 CREATE TABLE `issues` (
1076 `borrowernumber` int(11) default NULL,
1077 `itemnumber` int(11) default NULL,
1078 `date_due` date default NULL,
1079 `branchcode` varchar(10) default NULL,
1080 `issuingbranch` varchar(18) default NULL,
1081 `returndate` date default NULL,
1082 `lastreneweddate` date default NULL,
1083 `return` varchar(4) default NULL,
1084 `renewals` tinyint(4) default NULL,
1085 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1086 `issuedate` date default NULL,
1087 KEY `issuesborridx` (`borrowernumber`),
1088 KEY `issuesitemidx` (`itemnumber`),
1089 KEY `bordate` (`borrowernumber`,`timestamp`),
1090 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1091 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1092 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1095 -- Table structure for table `issuingrules`
1098 DROP TABLE IF EXISTS `issuingrules`;
1099 CREATE TABLE `issuingrules` (
1100 `categorycode` varchar(10) NOT NULL default '',
1101 `itemtype` varchar(10) NOT NULL default '',
1102 `restrictedtype` tinyint(1) default NULL,
1103 `rentaldiscount` decimal(28,6) default NULL,
1104 `reservecharge` decimal(28,6) default NULL,
1105 `fine` decimal(28,6) default NULL,
1106 `firstremind` int(11) default NULL,
1107 `chargeperiod` int(11) default NULL,
1108 `accountsent` int(11) default NULL,
1109 `chargename` varchar(100) default NULL,
1110 `maxissueqty` int(4) default NULL,
1111 `issuelength` int(4) default NULL,
1112 `branchcode` varchar(10) NOT NULL default '',
1113 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1114 KEY `categorycode` (`categorycode`),
1115 KEY `itemtype` (`itemtype`)
1116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1119 -- Table structure for table `items`
1122 DROP TABLE IF EXISTS `items`;
1123 CREATE TABLE `items` (
1124 `itemnumber` int(11) NOT NULL auto_increment,
1125 `biblionumber` int(11) NOT NULL default 0,
1126 `biblioitemnumber` int(11) NOT NULL default 0,
1127 `barcode` varchar(20) default NULL,
1128 `dateaccessioned` date default NULL,
1129 `booksellerid` mediumtext default NULL,
1130 `homebranch` varchar(10) default NULL,
1131 `price` decimal(8,2) default NULL,
1132 `replacementprice` decimal(8,2) default NULL,
1133 `replacementpricedate` date default NULL,
1134 `datelastborrowed` date default NULL,
1135 `datelastseen` date default NULL,
1136 `stack` tinyint(1) default NULL,
1137 `notforloan` tinyint(1) NOT NULL default 0,
1138 `damaged` tinyint(1) NOT NULL default 0,
1139 `itemlost` tinyint(1) NOT NULL default 0,
1140 `wthdrawn` tinyint(1) NOT NULL default 0,
1141 `itemcallnumber` varchar(30) default NULL,
1142 `issues` smallint(6) default NULL,
1143 `renewals` smallint(6) default NULL,
1144 `reserves` smallint(6) default NULL,
1145 `restricted` tinyint(1) default NULL,
1146 `itemnotes` mediumtext,
1147 `holdingbranch` varchar(10) default NULL,
1148 `paidfor` mediumtext,
1149 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1150 `location` varchar(80) default NULL,
1151 `onloan` date default NULL,
1152 `cn_source` varchar(10) default NULL,
1153 `cn_sort` varchar(30) default NULL,
1154 `ccode` varchar(10) default NULL,
1155 `materials` varchar(10) default NULL,
1156 `uri` varchar(255) default NULL,
1157 `itype` varchar(10) default NULL,
1158 `more_subfields_xml` longtext default NULL,
1159 `enumchron` varchar(80) default NULL,
1160 `copynumber` varchar(32) default NULL,
1161 PRIMARY KEY (`itemnumber`),
1162 UNIQUE KEY `itembarcodeidx` (`barcode`),
1163 KEY `itembinoidx` (`biblioitemnumber`),
1164 KEY `itembibnoidx` (`biblionumber`),
1165 KEY `homebranch` (`homebranch`),
1166 KEY `holdingbranch` (`holdingbranch`),
1167 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1168 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1169 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1170 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1173 -- Table structure for table `itemtypes`
1176 DROP TABLE IF EXISTS `itemtypes`;
1177 CREATE TABLE `itemtypes` (
1178 `itemtype` varchar(10) NOT NULL default '',
1179 `description` mediumtext,
1180 `renewalsallowed` smallint(6) default NULL,
1181 `rentalcharge` double(16,4) default NULL,
1182 `notforloan` smallint(6) default NULL,
1183 `imageurl` varchar(200) default NULL,
1185 PRIMARY KEY (`itemtype`),
1186 UNIQUE KEY `itemtype` (`itemtype`)
1187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1190 -- Table structure for table `labels`
1193 DROP TABLE IF EXISTS `labels`;
1194 CREATE TABLE `labels` (
1195 `labelid` int(11) NOT NULL auto_increment,
1196 `batch_id` varchar(10) NOT NULL default 1,
1197 `itemnumber` varchar(100) NOT NULL default '',
1198 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1199 PRIMARY KEY (`labelid`)
1200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1203 -- Table structure for table `labels_conf`
1206 DROP TABLE IF EXISTS `labels_conf`;
1207 CREATE TABLE `labels_conf` (
1208 `id` int(4) NOT NULL auto_increment,
1209 `barcodetype` char(100) default '',
1210 `title` int(1) default '0',
1211 `subtitle` int(1) default '0',
1212 `itemtype` int(1) default '0',
1213 `barcode` int(1) default '0',
1214 `dewey` int(1) default '0',
1215 `classification` int(1) default NULL,
1216 `subclass` int(1) default '0',
1217 `itemcallnumber` int(1) default '0',
1218 `author` int(1) default '0',
1219 `issn` int(1) default '0',
1220 `isbn` int(1) default '0',
1221 `startlabel` int(2) NOT NULL default '1',
1222 `printingtype` char(32) default 'BAR',
1223 `formatstring` varchar(64) default NULL,
1224 `layoutname` char(20) NOT NULL default 'TEST',
1225 `guidebox` int(1) default '0',
1226 `active` tinyint(1) default '1',
1227 `fonttype` char(10) collate utf8_unicode_ci default NULL,
1228 `ccode` char(4) collate utf8_unicode_ci default NULL,
1229 `callnum_split` int(1) default NULL,
1230 `text_justify` char(1) collate utf8_unicode_ci default NULL,
1232 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1235 -- Table structure for table `labels_profile`
1238 DROP TABLE IF EXISTS `labels_profile`;
1239 CREATE TABLE `labels_profile` (
1240 `tmpl_id` int(4) NOT NULL,
1241 `prof_id` int(4) NOT NULL,
1242 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1243 UNIQUE KEY `prof_id` (`prof_id`)
1244 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1247 -- Table structure for table `labels_templates`
1250 DROP TABLE IF EXISTS `labels_templates`;
1251 CREATE TABLE `labels_templates` (
1252 `tmpl_id` int(4) NOT NULL auto_increment,
1253 `tmpl_code` char(100) default '',
1254 `tmpl_desc` char(100) default '',
1255 `page_width` float default '0',
1256 `page_height` float default '0',
1257 `label_width` float default '0',
1258 `label_height` float default '0',
1259 `topmargin` float default '0',
1260 `leftmargin` float default '0',
1261 `cols` int(2) default '0',
1262 `rows` int(2) default '0',
1263 `colgap` float default '0',
1264 `rowgap` float default '0',
1265 `active` int(1) default NULL,
1266 `units` char(20) default 'PX',
1267 `fontsize` int(4) NOT NULL default '3',
1268 `font` char(10) NOT NULL default 'TR',
1269 PRIMARY KEY (`tmpl_id`)
1270 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1273 -- Table structure for table `letter`
1276 DROP TABLE IF EXISTS `letter`;
1277 CREATE TABLE `letter` (
1278 `module` varchar(20) NOT NULL default '',
1279 `code` varchar(20) NOT NULL default '',
1280 `name` varchar(100) NOT NULL default '',
1281 `title` varchar(200) NOT NULL default '',
1283 PRIMARY KEY (`module`,`code`)
1284 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1287 -- Table structure for table `marc_subfield_structure`
1290 DROP TABLE IF EXISTS `marc_subfield_structure`;
1291 CREATE TABLE `marc_subfield_structure` (
1292 `tagfield` varchar(3) NOT NULL default '',
1293 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1294 `liblibrarian` varchar(255) NOT NULL default '',
1295 `libopac` varchar(255) NOT NULL default '',
1296 `repeatable` tinyint(4) NOT NULL default 0,
1297 `mandatory` tinyint(4) NOT NULL default 0,
1298 `kohafield` varchar(40) default NULL,
1299 `tab` tinyint(1) default NULL,
1300 `authorised_value` varchar(20) default NULL,
1301 `authtypecode` varchar(20) default NULL,
1302 `value_builder` varchar(80) default NULL,
1303 `isurl` tinyint(1) default NULL,
1304 `hidden` tinyint(1) default NULL,
1305 `frameworkcode` varchar(4) NOT NULL default '',
1306 `seealso` varchar(1100) default NULL,
1307 `link` varchar(80) default NULL,
1308 `defaultvalue` text default NULL,
1309 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1310 KEY `kohafield_2` (`kohafield`),
1311 KEY `tab` (`frameworkcode`,`tab`),
1312 KEY `kohafield` (`frameworkcode`,`kohafield`)
1313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1316 -- Table structure for table `marc_tag_structure`
1319 DROP TABLE IF EXISTS `marc_tag_structure`;
1320 CREATE TABLE `marc_tag_structure` (
1321 `tagfield` varchar(3) NOT NULL default '',
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 `authorised_value` varchar(10) default NULL,
1327 `frameworkcode` varchar(4) NOT NULL default '',
1328 PRIMARY KEY (`frameworkcode`,`tagfield`)
1329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1332 -- Table structure for table `marc_matchers`
1335 DROP TABLE IF EXISTS `marc_matchers`;
1336 CREATE TABLE `marc_matchers` (
1337 `matcher_id` int(11) NOT NULL auto_increment,
1338 `code` varchar(10) NOT NULL default '',
1339 `description` varchar(255) NOT NULL default '',
1340 `record_type` varchar(10) NOT NULL default 'biblio',
1341 `threshold` int(11) NOT NULL default 0,
1342 PRIMARY KEY (`matcher_id`),
1343 KEY `code` (`code`),
1344 KEY `record_type` (`record_type`)
1345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1348 -- Table structure for table `matchpoints`
1350 DROP TABLE IF EXISTS `matchpoints`;
1351 CREATE TABLE `matchpoints` (
1352 `matcher_id` int(11) NOT NULL,
1353 `matchpoint_id` int(11) NOT NULL auto_increment,
1354 `search_index` varchar(30) NOT NULL default '',
1355 `score` int(11) NOT NULL default 0,
1356 PRIMARY KEY (`matchpoint_id`),
1357 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1358 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1359 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1363 -- Table structure for table `matchpoint_components`
1365 DROP TABLE IF EXISTS `matchpoint_components`;
1366 CREATE TABLE `matchpoint_components` (
1367 `matchpoint_id` int(11) NOT NULL,
1368 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1369 sequence int(11) NOT NULL default 0,
1370 tag varchar(3) NOT NULL default '',
1371 subfields varchar(40) NOT NULL default '',
1372 offset int(4) NOT NULL default 0,
1373 length int(4) NOT NULL default 0,
1374 PRIMARY KEY (`matchpoint_component_id`),
1375 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1376 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1377 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1381 -- Table structure for table `matcher_component_norms`
1383 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1384 CREATE TABLE `matchpoint_component_norms` (
1385 `matchpoint_component_id` int(11) NOT NULL,
1386 `sequence` int(11) NOT NULL default 0,
1387 `norm_routine` varchar(50) NOT NULL default '',
1388 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1389 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1390 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1391 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1394 -- Table structure for table `matcher_matchpoints`
1396 DROP TABLE IF EXISTS `matcher_matchpoints`;
1397 CREATE TABLE `matcher_matchpoints` (
1398 `matcher_id` int(11) NOT NULL,
1399 `matchpoint_id` int(11) NOT NULL,
1400 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1401 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1402 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1403 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1404 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1407 -- Table structure for table `matchchecks`
1409 DROP TABLE IF EXISTS `matchchecks`;
1410 CREATE TABLE `matchchecks` (
1411 `matcher_id` int(11) NOT NULL,
1412 `matchcheck_id` int(11) NOT NULL auto_increment,
1413 `source_matchpoint_id` int(11) NOT NULL,
1414 `target_matchpoint_id` int(11) NOT NULL,
1415 PRIMARY KEY (`matchcheck_id`),
1416 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1417 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1418 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1419 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1420 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1421 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1422 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1425 -- Table structure for table `notifys`
1428 DROP TABLE IF EXISTS `notifys`;
1429 CREATE TABLE `notifys` (
1430 `notify_id` int(11) NOT NULL default 0,
1431 `borrowernumber` int(11) NOT NULL default 0,
1432 `itemnumber` int(11) NOT NULL default 0,
1433 `notify_date` date default NULL,
1434 `notify_send_date` date default NULL,
1435 `notify_level` int(1) NOT NULL default 0,
1436 `method` varchar(20) NOT NULL default ''
1437 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1440 -- Table structure for table `nozebra`
1443 DROP TABLE IF EXISTS `nozebra`;
1444 CREATE TABLE `nozebra` (
1445 `server` varchar(20) NOT NULL,
1446 `indexname` varchar(40) NOT NULL,
1447 `value` varchar(250) NOT NULL,
1448 `biblionumbers` longtext NOT NULL,
1449 KEY `indexname` (`server`,`indexname`),
1450 KEY `value` (`server`,`value`))
1451 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1454 -- Table structure for table `old_issues`
1457 DROP TABLE IF EXISTS `old_issues`;
1458 CREATE TABLE `old_issues` (
1459 `borrowernumber` int(11) default NULL,
1460 `itemnumber` int(11) default NULL,
1461 `date_due` date default NULL,
1462 `branchcode` varchar(10) default NULL,
1463 `issuingbranch` varchar(18) default NULL,
1464 `returndate` date default NULL,
1465 `lastreneweddate` date default NULL,
1466 `return` varchar(4) default NULL,
1467 `renewals` tinyint(4) default NULL,
1468 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1469 `issuedate` date default NULL,
1470 KEY `old_issuesborridx` (`borrowernumber`),
1471 KEY `old_issuesitemidx` (`itemnumber`),
1472 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1473 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1474 ON DELETE SET NULL ON UPDATE SET NULL,
1475 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1476 ON DELETE SET NULL ON UPDATE SET NULL
1477 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1480 -- Table structure for table `old_reserves`
1482 DROP TABLE IF EXISTS `old_reserves`;
1483 CREATE TABLE `old_reserves` (
1484 `borrowernumber` int(11) default NULL,
1485 `reservedate` date default NULL,
1486 `biblionumber` int(11) default NULL,
1487 `constrainttype` varchar(1) default NULL,
1488 `branchcode` varchar(10) default NULL,
1489 `notificationdate` date default NULL,
1490 `reminderdate` date default NULL,
1491 `cancellationdate` date default NULL,
1492 `reservenotes` mediumtext,
1493 `priority` smallint(6) default NULL,
1494 `found` varchar(1) default NULL,
1495 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1496 `itemnumber` int(11) default NULL,
1497 `waitingdate` date default NULL,
1498 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1499 KEY `old_reserves_biblionumber` (`biblionumber`),
1500 KEY `old_reserves_itemnumber` (`itemnumber`),
1501 KEY `old_reserves_branchcode` (`branchcode`),
1502 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1503 ON DELETE SET NULL ON UPDATE SET NULL,
1504 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1505 ON DELETE SET NULL ON UPDATE SET NULL,
1506 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1507 ON DELETE SET NULL ON UPDATE SET NULL
1508 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1511 -- Table structure for table `opac_news`
1514 DROP TABLE IF EXISTS `opac_news`;
1515 CREATE TABLE `opac_news` (
1516 `idnew` int(10) unsigned NOT NULL auto_increment,
1517 `title` varchar(250) NOT NULL default '',
1518 `new` text NOT NULL,
1519 `lang` varchar(25) NOT NULL default '',
1520 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1521 `expirationdate` date default NULL,
1522 `number` int(11) default NULL,
1523 PRIMARY KEY (`idnew`)
1524 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1527 -- Table structure for table `overduerules`
1530 DROP TABLE IF EXISTS `overduerules`;
1531 CREATE TABLE `overduerules` (
1532 `branchcode` varchar(10) NOT NULL default '',
1533 `categorycode` varchar(10) NOT NULL default '',
1534 `delay1` int(4) default 0,
1535 `letter1` varchar(20) default NULL,
1536 `debarred1` varchar(1) default 0,
1537 `delay2` int(4) default 0,
1538 `debarred2` varchar(1) default 0,
1539 `letter2` varchar(20) default NULL,
1540 `delay3` int(4) default 0,
1541 `letter3` varchar(20) default NULL,
1542 `debarred3` int(1) default 0,
1543 PRIMARY KEY (`branchcode`,`categorycode`)
1544 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1547 -- Table structure for table `patroncards`
1550 DROP TABLE IF EXISTS `patroncards`;
1551 CREATE TABLE `patroncards` (
1552 `cardid` int(11) NOT NULL auto_increment,
1553 `batch_id` varchar(10) NOT NULL default '1',
1554 `borrowernumber` int(11) NOT NULL,
1555 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1556 PRIMARY KEY (`cardid`),
1557 KEY `patroncards_ibfk_1` (`borrowernumber`),
1558 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1559 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1562 -- Table structure for table `patronimage`
1565 DROP TABLE IF EXISTS `patronimage`;
1566 CREATE TABLE `patronimage` (
1567 `cardnumber` varchar(16) NOT NULL,
1568 `mimetype` varchar(15) NOT NULL,
1569 `imagefile` mediumblob NOT NULL,
1570 PRIMARY KEY (`cardnumber`),
1571 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1572 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1575 -- Table structure for table `printers`
1578 DROP TABLE IF EXISTS `printers`;
1579 CREATE TABLE `printers` (
1580 `printername` varchar(40) NOT NULL default '',
1581 `printqueue` varchar(20) default NULL,
1582 `printtype` varchar(20) default NULL,
1583 PRIMARY KEY (`printername`)
1584 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1587 -- Table structure for table `printers_profile`
1590 DROP TABLE IF EXISTS `printers_profile`;
1591 CREATE TABLE `printers_profile` (
1592 `prof_id` int(4) NOT NULL auto_increment,
1593 `printername` varchar(40) NOT NULL,
1594 `tmpl_id` int(4) NOT NULL,
1595 `paper_bin` varchar(20) NOT NULL,
1596 `offset_horz` float default NULL,
1597 `offset_vert` float default NULL,
1598 `creep_horz` float default NULL,
1599 `creep_vert` float default NULL,
1600 `unit` char(20) NOT NULL default 'POINT',
1601 PRIMARY KEY (`prof_id`),
1602 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1603 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1604 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1607 -- Table structure for table `repeatable_holidays`
1610 DROP TABLE IF EXISTS `repeatable_holidays`;
1611 CREATE TABLE `repeatable_holidays` (
1612 `id` int(11) NOT NULL auto_increment,
1613 `branchcode` varchar(10) NOT NULL default '',
1614 `weekday` smallint(6) default NULL,
1615 `day` smallint(6) default NULL,
1616 `month` smallint(6) default NULL,
1617 `title` varchar(50) NOT NULL default '',
1618 `description` text NOT NULL,
1620 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1623 -- Table structure for table `reports_dictionary`
1626 DROP TABLE IF EXISTS `reports_dictionary`;
1627 CREATE TABLE reports_dictionary (
1628 `id` int(11) NOT NULL auto_increment,
1629 `name` varchar(255) default NULL,
1631 `date_created` datetime default NULL,
1632 `date_modified` datetime default NULL,
1634 `area` int(11) default NULL,
1636 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1639 -- Table structure for table `reserveconstraints`
1642 DROP TABLE IF EXISTS `reserveconstraints`;
1643 CREATE TABLE `reserveconstraints` (
1644 `borrowernumber` int(11) NOT NULL default 0,
1645 `reservedate` date default NULL,
1646 `biblionumber` int(11) NOT NULL default 0,
1647 `biblioitemnumber` int(11) default NULL,
1648 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1649 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1652 -- Table structure for table `reserves`
1655 DROP TABLE IF EXISTS `reserves`;
1656 CREATE TABLE `reserves` (
1657 `borrowernumber` int(11) NOT NULL default 0,
1658 `reservedate` date default NULL,
1659 `biblionumber` int(11) NOT NULL default 0,
1660 `constrainttype` varchar(1) default NULL,
1661 `branchcode` varchar(10) default NULL,
1662 `notificationdate` date default NULL,
1663 `reminderdate` date default NULL,
1664 `cancellationdate` date default NULL,
1665 `reservenotes` mediumtext,
1666 `priority` smallint(6) default NULL,
1667 `found` varchar(1) default NULL,
1668 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1669 `itemnumber` int(11) default NULL,
1670 `waitingdate` date default NULL,
1671 KEY `borrowernumber` (`borrowernumber`),
1672 KEY `biblionumber` (`biblionumber`),
1673 KEY `itemnumber` (`itemnumber`),
1674 KEY `branchcode` (`branchcode`),
1675 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1676 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1677 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1678 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1679 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1682 -- Table structure for table `reviews`
1685 DROP TABLE IF EXISTS `reviews`;
1686 CREATE TABLE `reviews` (
1687 `reviewid` int(11) NOT NULL auto_increment,
1688 `borrowernumber` int(11) default NULL,
1689 `biblionumber` int(11) default NULL,
1691 `approved` tinyint(4) default NULL,
1692 `datereviewed` datetime default NULL,
1693 PRIMARY KEY (`reviewid`)
1694 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1697 -- Table structure for table `roadtype`
1700 DROP TABLE IF EXISTS `roadtype`;
1701 CREATE TABLE `roadtype` (
1702 `roadtypeid` int(11) NOT NULL auto_increment,
1703 `road_type` varchar(100) NOT NULL default '',
1704 PRIMARY KEY (`roadtypeid`)
1705 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1708 -- Table structure for table `saved_sql`
1711 DROP TABLE IF EXISTS `saved_sql`;
1712 CREATE TABLE saved_sql (
1713 `id` int(11) NOT NULL auto_increment,
1714 `borrowernumber` int(11) default NULL,
1715 `date_created` datetime default NULL,
1716 `last_modified` datetime default NULL,
1718 `last_run` datetime default NULL,
1719 `report_name` varchar(255) default NULL,
1720 `type` varchar(255) default NULL,
1723 KEY boridx (`borrowernumber`)
1724 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1728 -- Table structure for `saved_reports`
1731 DROP TABLE IF EXISTS `saved_reports`;
1732 CREATE TABLE saved_reports (
1733 `id` int(11) NOT NULL auto_increment,
1734 `report_id` int(11) default NULL,
1736 `date_run` datetime default NULL,
1738 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1742 -- Table structure for table `serial`
1745 DROP TABLE IF EXISTS `serial`;
1746 CREATE TABLE `serial` (
1747 `serialid` int(11) NOT NULL auto_increment,
1748 `biblionumber` varchar(100) NOT NULL default '',
1749 `subscriptionid` varchar(100) NOT NULL default '',
1750 `serialseq` varchar(100) NOT NULL default '',
1751 `status` tinyint(4) NOT NULL default 0,
1752 `planneddate` date default NULL,
1754 `publisheddate` date default NULL,
1755 `itemnumber` text default NULL,
1756 `claimdate` date default NULL,
1757 `routingnotes` text,
1758 PRIMARY KEY (`serialid`)
1759 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1762 -- Table structure for table `sessions`
1765 DROP TABLE IF EXISTS sessions;
1766 CREATE TABLE sessions (
1767 `id` varchar(32) NOT NULL,
1768 `a_session` text NOT NULL,
1770 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1773 -- Table structure for table `special_holidays`
1776 DROP TABLE IF EXISTS `special_holidays`;
1777 CREATE TABLE `special_holidays` (
1778 `id` int(11) NOT NULL auto_increment,
1779 `branchcode` varchar(10) NOT NULL default '',
1780 `day` smallint(6) NOT NULL default 0,
1781 `month` smallint(6) NOT NULL default 0,
1782 `year` smallint(6) NOT NULL default 0,
1783 `isexception` smallint(1) NOT NULL default 1,
1784 `title` varchar(50) NOT NULL default '',
1785 `description` text NOT NULL,
1787 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1790 -- Table structure for table `statistics`
1793 DROP TABLE IF EXISTS `statistics`;
1794 CREATE TABLE `statistics` (
1795 `datetime` datetime default NULL,
1796 `branch` varchar(10) default NULL,
1797 `proccode` varchar(4) default NULL,
1798 `value` double(16,4) default NULL,
1799 `type` varchar(16) default NULL,
1801 `usercode` varchar(10) default NULL,
1802 `itemnumber` int(11) default NULL,
1803 `itemtype` varchar(10) default NULL,
1804 `borrowernumber` int(11) default NULL,
1805 `associatedborrower` int(11) default NULL,
1806 KEY `timeidx` (`datetime`)
1807 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1810 -- Table structure for table `stopwords`
1813 DROP TABLE IF EXISTS `stopwords`;
1814 CREATE TABLE `stopwords` (
1815 `word` varchar(255) default NULL
1816 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1819 -- Table structure for table `subscription`
1822 DROP TABLE IF EXISTS `subscription`;
1823 CREATE TABLE `subscription` (
1824 `biblionumber` int(11) NOT NULL default 0,
1825 `subscriptionid` int(11) NOT NULL auto_increment,
1826 `librarian` varchar(100) default '',
1827 `startdate` date default NULL,
1828 `aqbooksellerid` int(11) default 0,
1829 `cost` int(11) default 0,
1830 `aqbudgetid` int(11) default 0,
1831 `weeklength` int(11) default 0,
1832 `monthlength` int(11) default 0,
1833 `numberlength` int(11) default 0,
1834 `periodicity` tinyint(4) default 0,
1835 `dow` varchar(100) default '',
1836 `numberingmethod` varchar(100) default '',
1838 `status` varchar(100) NOT NULL default '',
1839 `add1` int(11) default 0,
1840 `every1` int(11) default 0,
1841 `whenmorethan1` int(11) default 0,
1842 `setto1` int(11) default NULL,
1843 `lastvalue1` int(11) default NULL,
1844 `add2` int(11) default 0,
1845 `every2` int(11) default 0,
1846 `whenmorethan2` int(11) default 0,
1847 `setto2` int(11) default NULL,
1848 `lastvalue2` int(11) default NULL,
1849 `add3` int(11) default 0,
1850 `every3` int(11) default 0,
1851 `innerloop1` int(11) default 0,
1852 `innerloop2` int(11) default 0,
1853 `innerloop3` int(11) default 0,
1854 `whenmorethan3` int(11) default 0,
1855 `setto3` int(11) default NULL,
1856 `lastvalue3` int(11) default NULL,
1857 `issuesatonce` tinyint(3) NOT NULL default 1,
1858 `firstacquidate` date default NULL,
1859 `manualhistory` tinyint(1) NOT NULL default 0,
1860 `irregularity` text,
1861 `letter` varchar(20) default NULL,
1862 `numberpattern` tinyint(3) default 0,
1863 `distributedto` text,
1864 `internalnotes` longtext,
1866 `branchcode` varchar(10) NOT NULL default '',
1867 `hemisphere` tinyint(3) default 0,
1868 `lastbranch` varchar(10),
1869 `serialsadditems` tinyint(1) NOT NULL default '0',
1870 PRIMARY KEY (`subscriptionid`)
1871 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1874 -- Table structure for table `subscriptionhistory`
1877 DROP TABLE IF EXISTS `subscriptionhistory`;
1878 CREATE TABLE `subscriptionhistory` (
1879 `biblionumber` int(11) NOT NULL default 0,
1880 `subscriptionid` int(11) NOT NULL default 0,
1881 `histstartdate` date default NULL,
1882 `enddate` date default NULL,
1883 `missinglist` longtext NOT NULL,
1884 `recievedlist` longtext NOT NULL,
1885 `opacnote` varchar(150) NOT NULL default '',
1886 `librariannote` varchar(150) NOT NULL default '',
1887 PRIMARY KEY (`subscriptionid`),
1888 KEY `biblionumber` (`biblionumber`)
1889 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1892 -- Table structure for table `subscriptionroutinglist`
1895 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1896 CREATE TABLE `subscriptionroutinglist` (
1897 `routingid` int(11) NOT NULL auto_increment,
1898 `borrowernumber` int(11) default NULL,
1899 `ranking` int(11) default NULL,
1900 `subscriptionid` int(11) default NULL,
1901 PRIMARY KEY (`routingid`)
1902 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1905 -- Table structure for table `suggestions`
1908 DROP TABLE IF EXISTS `suggestions`;
1909 CREATE TABLE `suggestions` (
1910 `suggestionid` int(8) NOT NULL auto_increment,
1911 `suggestedby` int(11) NOT NULL default 0,
1912 `managedby` int(11) default NULL,
1913 `STATUS` varchar(10) NOT NULL default '',
1915 `author` varchar(80) default NULL,
1916 `title` varchar(80) default NULL,
1917 `copyrightdate` smallint(6) default NULL,
1918 `publishercode` varchar(255) default NULL,
1919 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1920 `volumedesc` varchar(255) default NULL,
1921 `publicationyear` smallint(6) default 0,
1922 `place` varchar(255) default NULL,
1923 `isbn` varchar(10) default NULL,
1924 `mailoverseeing` smallint(1) default 0,
1925 `biblionumber` int(11) default NULL,
1927 PRIMARY KEY (`suggestionid`),
1928 KEY `suggestedby` (`suggestedby`),
1929 KEY `managedby` (`managedby`)
1930 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1933 -- Table structure for table `systempreferences`
1936 DROP TABLE IF EXISTS `systempreferences`;
1937 CREATE TABLE `systempreferences` (
1938 `variable` varchar(50) NOT NULL default '',
1940 `options` mediumtext,
1942 `type` varchar(20) default NULL,
1943 PRIMARY KEY (`variable`)
1944 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1947 -- Table structure for table `tags`
1950 DROP TABLE IF EXISTS `tags`;
1951 CREATE TABLE `tags` (
1952 `entry` varchar(255) NOT NULL default '',
1953 `weight` bigint(20) NOT NULL default 0,
1954 PRIMARY KEY (`entry`)
1955 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1958 -- Table structure for table `tags_all`
1961 CREATE TABLE `tags_all` (
1962 `tag_id` int(11) NOT NULL auto_increment,
1963 `borrowernumber` int(11) NOT NULL,
1964 `biblionumber` int(11) NOT NULL,
1965 `term` varchar(255) NOT NULL,
1966 `language` int(4) default NULL,
1967 `date_created` datetime NOT NULL,
1968 PRIMARY KEY (`tag_id`),
1969 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1970 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1971 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1972 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1973 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1974 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1975 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1978 -- Table structure for table `tags_approval`
1981 CREATE TABLE `tags_approval` (
1982 `term` varchar(255) NOT NULL,
1983 `approved` int(1) NOT NULL default '0',
1984 `date_approved` datetime default NULL,
1985 `approved_by` int(11) default NULL,
1986 `weight_total` int(9) NOT NULL default '1',
1987 PRIMARY KEY (`term`),
1988 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1989 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1990 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1991 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1994 -- Table structure for table `tags_index`
1997 CREATE TABLE `tags_index` (
1998 `term` varchar(255) NOT NULL,
1999 `biblionumber` int(11) NOT NULL,
2000 `weight` int(9) NOT NULL default '1',
2001 PRIMARY KEY (`term`,`biblionumber`),
2002 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2003 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2004 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2005 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2006 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2007 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2010 -- Table structure for table `userflags`
2013 DROP TABLE IF EXISTS `userflags`;
2014 CREATE TABLE `userflags` (
2015 `bit` int(11) NOT NULL default 0,
2016 `flag` varchar(30) default NULL,
2017 `flagdesc` varchar(255) default NULL,
2018 `defaulton` int(11) default NULL,
2020 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2023 -- Table structure for table `virtualshelves`
2026 DROP TABLE IF EXISTS `virtualshelves`;
2027 CREATE TABLE `virtualshelves` (
2028 `shelfnumber` int(11) NOT NULL auto_increment,
2029 `shelfname` varchar(255) default NULL,
2030 `owner` varchar(80) default NULL,
2031 `category` varchar(1) default NULL,
2032 `sortfield` varchar(16) default NULL,
2033 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2034 PRIMARY KEY (`shelfnumber`)
2035 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2038 -- Table structure for table `virtualshelfcontents`
2041 DROP TABLE IF EXISTS `virtualshelfcontents`;
2042 CREATE TABLE `virtualshelfcontents` (
2043 `shelfnumber` int(11) NOT NULL default 0,
2044 `biblionumber` int(11) NOT NULL default 0,
2045 `flags` int(11) default NULL,
2046 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2047 KEY `shelfnumber` (`shelfnumber`),
2048 KEY `biblionumber` (`biblionumber`),
2049 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2050 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2051 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2054 -- Table structure for table `z3950servers`
2057 DROP TABLE IF EXISTS `z3950servers`;
2058 CREATE TABLE `z3950servers` (
2059 `host` varchar(255) default NULL,
2060 `port` int(11) default NULL,
2061 `db` varchar(255) default NULL,
2062 `userid` varchar(255) default NULL,
2063 `password` varchar(255) default NULL,
2065 `id` int(11) NOT NULL auto_increment,
2066 `checked` smallint(6) default NULL,
2067 `rank` int(11) default NULL,
2068 `syntax` varchar(80) default NULL,
2070 `position` enum('primary','secondary','') NOT NULL default 'primary',
2071 `type` enum('zed','opensearch') NOT NULL default 'zed',
2072 `encoding` text default NULL,
2073 `description` text NOT NULL,
2075 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2078 -- Table structure for table `zebraqueue`
2081 DROP TABLE IF EXISTS `zebraqueue`;
2082 CREATE TABLE `zebraqueue` (
2083 `id` int(11) NOT NULL auto_increment,
2084 `biblio_auth_number` int(11) NOT NULL default '0',
2085 `operation` char(20) NOT NULL default '',
2086 `server` char(20) NOT NULL default '',
2087 `done` int(11) NOT NULL default '0',
2088 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2090 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2091 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2093 DROP TABLE IF EXISTS `services_throttle`;
2094 CREATE TABLE `services_throttle` (
2095 `service_type` varchar(10) NOT NULL default '',
2096 `service_count` varchar(45) default NULL,
2097 PRIMARY KEY (`service_type`)
2098 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2100 -- http://www.w3.org/International/articles/language-tags/
2103 DROP TABLE IF EXISTS language_subtag_registry;
2104 CREATE TABLE language_subtag_registry (
2106 type varchar(25), -- language-script-region-variant-extension-privateuse
2107 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2109 KEY `subtag` (`subtag`)
2110 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2112 -- TODO: add suppress_scripts
2113 -- this maps three letter codes defined in iso639.2 back to their
2114 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2115 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2116 CREATE TABLE language_rfc4646_to_iso639 (
2117 rfc4646_subtag varchar(25),
2118 iso639_2_code varchar(25),
2119 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2120 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2122 DROP TABLE IF EXISTS language_descriptions;
2123 CREATE TABLE language_descriptions (
2127 description varchar(255),
2129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2131 -- bi-directional support, keyed by script subcode
2132 DROP TABLE IF EXISTS language_script_bidi;
2133 CREATE TABLE language_script_bidi (
2134 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2135 bidi varchar(3), -- rtl ltr
2136 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2137 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2139 -- TODO: need to map language subtags to script subtags for detection
2140 -- of bidi when script is not specified (like ar, he)
2141 DROP TABLE IF EXISTS language_script_mapping;
2142 CREATE TABLE language_script_mapping (
2143 language_subtag varchar(25),
2144 script_subtag varchar(25),
2145 KEY `language_subtag` (`language_subtag`)
2146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2148 DROP TABLE IF EXISTS `permissions`;
2149 CREATE TABLE `permissions` (
2150 `module_bit` int(11) NOT NULL DEFAULT 0,
2151 `code` varchar(30) DEFAULT NULL,
2152 `description` varchar(255) DEFAULT NULL,
2153 PRIMARY KEY (`module_bit`, `code`),
2154 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2155 ON DELETE CASCADE ON UPDATE CASCADE
2156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2158 DROP TABLE IF EXISTS `serialitems`;
2159 CREATE TABLE `serialitems` (
2160 `itemnumber` int(11) NOT NULL,
2161 `serialid` int(11) NOT NULL,
2162 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2163 KEY `serialitems_sfk_1` (`serialid`),
2164 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2167 DROP TABLE IF EXISTS `user_permissions`;
2168 CREATE TABLE `user_permissions` (
2169 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2170 `module_bit` int(11) NOT NULL DEFAULT 0,
2171 `code` varchar(30) DEFAULT NULL,
2172 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2173 ON DELETE CASCADE ON UPDATE CASCADE,
2174 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2175 ON DELETE CASCADE ON UPDATE CASCADE
2176 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2179 -- Table structure for table `tmp_holdsqueue`
2182 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2183 CREATE TABLE `tmp_holdsqueue` (
2184 `biblionumber` int(11) default NULL,
2185 `itemnumber` int(11) default NULL,
2186 `barcode` varchar(20) default NULL,
2187 `surname` mediumtext NOT NULL,
2190 `borrowernumber` int(11) NOT NULL,
2191 `cardnumber` varchar(16) default NULL,
2192 `reservedate` date default NULL,
2194 `itemcallnumber` varchar(30) default NULL,
2195 `holdingbranch` varchar(10) default NULL,
2196 `pickbranch` varchar(10) default NULL,
2198 `item_level_request` tinyint(4) NOT NULL default 0
2199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2202 -- Table structure for table `message_queue`
2205 CREATE TABLE `message_queue` (
2206 `message_id` int(11) NOT NULL auto_increment,
2207 `borrowernumber` int(11) default NULL,
2210 `message_transport_type` varchar(20) NOT NULL,
2211 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2212 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2213 `to_address` mediumtext,
2214 `from_address` mediumtext,
2215 `content_type` text,
2216 KEY `message_id` (`message_id`),
2217 KEY `borrowernumber` (`borrowernumber`),
2218 KEY `message_transport_type` (`message_transport_type`),
2219 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2220 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2221 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2224 -- Table structure for table `message_transport_types`
2227 DROP TABLE IF EXISTS `message_transport_types`;
2228 CREATE TABLE `message_transport_types` (
2229 `message_transport_type` varchar(20) NOT NULL,
2230 PRIMARY KEY (`message_transport_type`)
2231 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2234 -- Table structure for table `message_attributes`
2237 DROP TABLE IF EXISTS `message_attributes`;
2238 CREATE TABLE `message_attributes` (
2239 `message_attribute_id` int(11) NOT NULL auto_increment,
2240 `message_name` varchar(20) NOT NULL default '',
2241 `takes_days` tinyint(1) NOT NULL default '0',
2242 PRIMARY KEY (`message_attribute_id`),
2243 UNIQUE KEY `message_name` (`message_name`)
2244 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2247 -- Table structure for table `message_transports`
2250 DROP TABLE IF EXISTS `message_transports`;
2251 CREATE TABLE `message_transports` (
2252 `message_attribute_id` int(11) NOT NULL,
2253 `message_transport_type` varchar(20) NOT NULL,
2254 `is_digest` tinyint(1) NOT NULL default '0',
2255 `letter_module` varchar(20) NOT NULL default '',
2256 `letter_code` varchar(20) NOT NULL default '',
2257 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2258 KEY `message_transport_type` (`message_transport_type`),
2259 KEY `letter_module` (`letter_module`,`letter_code`),
2260 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2261 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2262 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2266 -- Table structure for table `borrower_message_preferences`
2269 DROP TABLE IF EXISTS `borrower_message_preferences`;
2270 CREATE TABLE `borrower_message_preferences` (
2271 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2272 `borrowernumber` int(11) NOT NULL default '0',
2273 `message_attribute_id` int(11) default '0',
2274 `days_in_advance` int(11) default '0',
2275 `wants_digest` tinyint(1) NOT NULL default '0',
2276 PRIMARY KEY (`borrower_message_preference_id`),
2277 KEY `borrowernumber` (`borrowernumber`),
2278 KEY `message_attribute_id` (`message_attribute_id`),
2279 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2280 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
2281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2284 -- Table structure for table `borrower_message_transport_preferences`
2287 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2288 CREATE TABLE `borrower_message_transport_preferences` (
2289 `borrower_message_preference_id` int(11) NOT NULL default '0',
2290 `message_transport_type` varchar(20) NOT NULL default '0',
2291 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2292 KEY `message_transport_type` (`message_transport_type`),
2293 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,
2294 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
2295 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2297 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2298 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2299 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2300 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2301 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2302 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2303 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2304 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;