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`),
346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
349 -- Table structure for table `biblio`
352 DROP TABLE IF EXISTS `biblio`;
353 CREATE TABLE `biblio` (
354 `biblionumber` int(11) NOT NULL auto_increment,
355 `frameworkcode` varchar(4) NOT NULL default '',
358 `unititle` mediumtext,
360 `serial` tinyint(1) default NULL,
361 `seriestitle` mediumtext,
362 `copyrightdate` smallint(6) default NULL,
363 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
364 `datecreated` DATE NOT NULL,
365 `abstract` mediumtext,
366 PRIMARY KEY (`biblionumber`),
367 KEY `blbnoidx` (`biblionumber`)
368 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
371 -- Table structure for table `biblio_framework`
374 DROP TABLE IF EXISTS `biblio_framework`;
375 CREATE TABLE `biblio_framework` (
376 `frameworkcode` varchar(4) NOT NULL default '',
377 `frameworktext` varchar(255) NOT NULL default '',
378 PRIMARY KEY (`frameworkcode`)
379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
382 -- Table structure for table `biblioitems`
385 DROP TABLE IF EXISTS `biblioitems`;
386 CREATE TABLE `biblioitems` (
387 `biblioitemnumber` int(11) NOT NULL auto_increment,
388 `biblionumber` int(11) NOT NULL default 0,
391 `itemtype` varchar(10) default NULL,
392 `isbn` varchar(30) default NULL,
393 `issn` varchar(9) default NULL,
394 `publicationyear` text,
395 `publishercode` varchar(255) default NULL,
396 `volumedate` date default NULL,
398 `collectiontitle` mediumtext default NULL,
399 `collectionissn` text default NULL,
400 `collectionvolume` mediumtext default NULL,
401 `editionstatement` text default NULL,
402 `editionresponsibility` text default NULL,
403 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
404 `illus` varchar(255) default NULL,
405 `pages` varchar(255) default NULL,
407 `size` varchar(255) default NULL,
408 `place` varchar(255) default NULL,
409 `lccn` varchar(25) default NULL,
411 `url` varchar(255) default NULL,
412 `cn_source` varchar(10) default NULL,
413 `cn_class` varchar(30) default NULL,
414 `cn_item` varchar(10) default NULL,
415 `cn_suffix` varchar(10) default NULL,
416 `cn_sort` varchar(30) default NULL,
417 `totalissues` int(10),
418 `marcxml` longtext NOT NULL,
419 PRIMARY KEY (`biblioitemnumber`),
420 KEY `bibinoidx` (`biblioitemnumber`),
421 KEY `bibnoidx` (`biblionumber`),
423 KEY `publishercode` (`publishercode`),
425 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
429 -- Table structure for table `borrowers`
432 DROP TABLE IF EXISTS `borrowers`;
433 CREATE TABLE `borrowers` (
434 `borrowernumber` int(11) NOT NULL auto_increment,
435 `cardnumber` varchar(16) default NULL,
436 `surname` mediumtext NOT NULL,
439 `othernames` mediumtext,
441 `streetnumber` varchar(10) default NULL,
442 `streettype` varchar(50) default NULL,
443 `address` mediumtext NOT NULL,
445 `city` mediumtext NOT NULL,
446 `zipcode` varchar(25) default NULL,
450 `mobile` varchar(50) default NULL,
454 `B_streetnumber` varchar(10) default NULL,
455 `B_streettype` varchar(50) default NULL,
456 `B_address` varchar(100) default NULL,
458 `B_zipcode` varchar(25) default NULL,
461 `B_phone` mediumtext,
462 `dateofbirth` date default NULL,
463 `branchcode` varchar(10) NOT NULL default '',
464 `categorycode` varchar(10) NOT NULL default '',
465 `dateenrolled` date default NULL,
466 `dateexpiry` date default NULL,
467 `gonenoaddress` tinyint(1) default NULL,
468 `lost` tinyint(1) default NULL,
469 `debarred` tinyint(1) default NULL,
470 `contactname` mediumtext,
471 `contactfirstname` text,
473 `guarantorid` int(11) default NULL,
474 `borrowernotes` mediumtext,
475 `relationship` varchar(100) default NULL,
476 `ethnicity` varchar(50) default NULL,
477 `ethnotes` varchar(255) default NULL,
478 `sex` varchar(1) default NULL,
479 `password` varchar(30) default NULL,
480 `flags` int(11) default NULL,
481 `userid` varchar(30) default NULL,
482 `opacnote` mediumtext,
483 `contactnote` varchar(255) default NULL,
484 `sort1` varchar(80) default NULL,
485 `sort2` varchar(80) default NULL,
486 `altcontactfirstname` varchar(255) default NULL,
487 `altcontactsurname` varchar(255) default NULL,
488 `altcontactaddress1` varchar(255) default NULL,
489 `altcontactaddress2` varchar(255) default NULL,
490 `altcontactaddress3` varchar(255) default NULL,
491 `altcontactzipcode` varchar(50) default NULL,
492 `altcontactphone` varchar(50) default NULL,
493 `smsalertnumber` varchar(50) default NULL,
494 UNIQUE KEY `cardnumber` (`cardnumber`),
495 PRIMARY KEY `borrowernumber` (`borrowernumber`),
496 KEY `categorycode` (`categorycode`),
497 KEY `branchcode` (`branchcode`),
498 KEY `userid` (`userid`),
499 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
500 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
501 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
504 -- Table structure for table `borrower_attribute_types`
507 DROP TABLE IF EXISTS `borrower_attribute_types`;
508 CREATE TABLE `borrower_attribute_types` (
509 `code` varchar(10) NOT NULL,
510 `description` varchar(255) NOT NULL,
511 `repeatable` tinyint(1) NOT NULL default 0,
512 `unique_id` tinyint(1) NOT NULL default 0,
513 `opac_display` tinyint(1) NOT NULL default 0,
514 `password_allowed` tinyint(1) NOT NULL default 0,
515 `staff_searchable` tinyint(1) NOT NULL default 0,
516 `authorised_value_category` varchar(10) default NULL,
518 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
521 -- Table structure for table `borrower_attributes`
524 DROP TABLE IF EXISTS `borrower_attributes`;
525 CREATE TABLE `borrower_attributes` (
526 `borrowernumber` int(11) NOT NULL,
527 `code` varchar(10) NOT NULL,
528 `attribute` varchar(64) default NULL,
529 `password` varchar(64) default NULL,
530 KEY `borrowernumber` (`borrowernumber`),
531 KEY `code_attribute` (`code`, `attribute`),
532 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
533 ON DELETE CASCADE ON UPDATE CASCADE,
534 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
535 ON DELETE CASCADE ON UPDATE CASCADE
536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
538 CREATE TABLE `branch_item_rules` (
539 `branchcode` varchar(10) NOT NULL,
540 `itemtype` varchar(10) NOT NULL,
541 `holdallowed` tinyint(1) default NULL,
542 PRIMARY KEY (`itemtype`,`branchcode`),
543 KEY `branch_item_rules_ibfk_2` (`branchcode`),
544 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
545 ON DELETE CASCADE ON UPDATE CASCADE,
546 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
547 ON DELETE CASCADE ON UPDATE CASCADE
548 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
551 -- Table structure for table `branchcategories`
554 DROP TABLE IF EXISTS `branchcategories`;
555 CREATE TABLE `branchcategories` (
556 `categorycode` varchar(10) NOT NULL default '',
557 `categoryname` varchar(32),
558 `codedescription` mediumtext,
559 `categorytype` varchar(16),
560 PRIMARY KEY (`categorycode`)
561 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
564 -- Table structure for table `branches`
567 DROP TABLE IF EXISTS `branches`;
568 CREATE TABLE `branches` (
569 `branchcode` varchar(10) NOT NULL default '',
570 `branchname` mediumtext NOT NULL,
571 `branchaddress1` mediumtext,
572 `branchaddress2` mediumtext,
573 `branchaddress3` mediumtext,
574 `branchzip` varchar(25) default NULL,
575 `branchcity` mediumtext,
576 `branchcountry` text,
577 `branchphone` mediumtext,
578 `branchfax` mediumtext,
579 `branchemail` mediumtext,
580 `branchurl` mediumtext,
581 `issuing` tinyint(4) default NULL,
582 `branchip` varchar(15) default NULL,
583 `branchprinter` varchar(100) default NULL,
584 `branchnotes` mediumtext,
585 UNIQUE KEY `branchcode` (`branchcode`)
586 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
589 -- Table structure for table `branchrelations`
592 DROP TABLE IF EXISTS `branchrelations`;
593 CREATE TABLE `branchrelations` (
594 `branchcode` varchar(10) NOT NULL default '',
595 `categorycode` varchar(10) NOT NULL default '',
596 PRIMARY KEY (`branchcode`,`categorycode`),
597 KEY `branchcode` (`branchcode`),
598 KEY `categorycode` (`categorycode`),
599 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
600 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
601 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
604 -- Table structure for table `branchtransfers`
607 DROP TABLE IF EXISTS `branchtransfers`;
608 CREATE TABLE `branchtransfers` (
609 `itemnumber` int(11) NOT NULL default 0,
610 `datesent` datetime default NULL,
611 `frombranch` varchar(10) NOT NULL default '',
612 `datearrived` datetime default NULL,
613 `tobranch` varchar(10) NOT NULL default '',
614 `comments` mediumtext,
615 KEY `frombranch` (`frombranch`),
616 KEY `tobranch` (`tobranch`),
617 KEY `itemnumber` (`itemnumber`),
618 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
619 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
620 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
621 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
625 -- Table structure for table `browser`
627 DROP TABLE IF EXISTS `browser`;
628 CREATE TABLE `browser` (
629 `level` int(11) NOT NULL,
630 `classification` varchar(20) NOT NULL,
631 `description` varchar(255) NOT NULL,
632 `number` bigint(20) NOT NULL,
633 `endnode` tinyint(4) NOT NULL
634 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
637 -- Table structure for table `categories`
640 DROP TABLE IF EXISTS `categories`;
641 CREATE TABLE `categories` (
642 `categorycode` varchar(10) NOT NULL default '',
643 `description` mediumtext,
644 `enrolmentperiod` smallint(6) default NULL,
645 `upperagelimit` smallint(6) default NULL,
646 `dateofbirthrequired` tinyint(1) default NULL,
647 `finetype` varchar(30) default NULL,
648 `bulk` tinyint(1) default NULL,
649 `enrolmentfee` decimal(28,6) default NULL,
650 `overduenoticerequired` tinyint(1) default NULL,
651 `issuelimit` smallint(6) default NULL,
652 `reservefee` decimal(28,6) default NULL,
653 `category_type` varchar(1) NOT NULL default 'A',
654 PRIMARY KEY (`categorycode`),
655 UNIQUE KEY `categorycode` (`categorycode`)
656 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
659 -- Table structure for table `borrower_branch_circ_rules`
662 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
663 CREATE TABLE `branch_borrower_circ_rules` (
664 `branchcode` VARCHAR(10) NOT NULL,
665 `categorycode` VARCHAR(10) NOT NULL,
666 `maxissueqty` int(4) default NULL,
667 PRIMARY KEY (`categorycode`, `branchcode`),
668 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
669 ON DELETE CASCADE ON UPDATE CASCADE,
670 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
671 ON DELETE CASCADE ON UPDATE CASCADE
672 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
675 -- Table structure for table `default_borrower_circ_rules`
678 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
679 CREATE TABLE `default_borrower_circ_rules` (
680 `categorycode` VARCHAR(10) NOT NULL,
681 `maxissueqty` int(4) default NULL,
682 PRIMARY KEY (`categorycode`),
683 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
684 ON DELETE CASCADE ON UPDATE CASCADE
685 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
688 -- Table structure for table `default_branch_circ_rules`
691 DROP TABLE IF EXISTS `default_branch_circ_rules`;
692 CREATE TABLE `default_branch_circ_rules` (
693 `branchcode` VARCHAR(10) NOT NULL,
694 `maxissueqty` int(4) default NULL,
695 `holdallowed` tinyint(1) default NULL,
696 PRIMARY KEY (`branchcode`),
697 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
698 ON DELETE CASCADE ON UPDATE CASCADE
699 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
702 -- Table structure for table `default_branch_item_rules`
705 CREATE TABLE `default_branch_item_rules` (
706 `itemtype` varchar(10) NOT NULL,
707 `holdallowed` tinyint(1) default NULL,
708 PRIMARY KEY (`itemtype`),
709 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
710 ON DELETE CASCADE ON UPDATE CASCADE
711 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
714 -- Table structure for table `default_circ_rules`
717 DROP TABLE IF EXISTS `default_circ_rules`;
718 CREATE TABLE `default_circ_rules` (
719 `singleton` enum('singleton') NOT NULL default 'singleton',
720 `maxissueqty` int(4) default NULL,
721 `holdallowed` int(1) default NULL,
722 PRIMARY KEY (`singleton`)
723 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
726 -- Table structure for table `cities`
729 DROP TABLE IF EXISTS `cities`;
730 CREATE TABLE `cities` (
731 `cityid` int(11) NOT NULL auto_increment,
732 `city_name` varchar(100) NOT NULL default '',
733 `city_zipcode` varchar(20) default NULL,
734 PRIMARY KEY (`cityid`)
735 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
738 -- Table structure for table `class_sort_rules`
741 DROP TABLE IF EXISTS `class_sort_rules`;
742 CREATE TABLE `class_sort_rules` (
743 `class_sort_rule` varchar(10) NOT NULL default '',
744 `description` mediumtext,
745 `sort_routine` varchar(30) NOT NULL default '',
746 PRIMARY KEY (`class_sort_rule`),
747 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
748 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
751 -- Table structure for table `class_sources`
754 DROP TABLE IF EXISTS `class_sources`;
755 CREATE TABLE `class_sources` (
756 `cn_source` varchar(10) NOT NULL default '',
757 `description` mediumtext,
758 `used` tinyint(4) NOT NULL default 0,
759 `class_sort_rule` varchar(10) NOT NULL default '',
760 PRIMARY KEY (`cn_source`),
761 UNIQUE KEY `cn_source_idx` (`cn_source`),
762 KEY `used_idx` (`used`),
763 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
764 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
767 -- Table structure for table `currency`
770 DROP TABLE IF EXISTS `currency`;
771 CREATE TABLE `currency` (
772 `currency` varchar(10) NOT NULL default '',
773 `symbol` varchar(5) default NULL,
774 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
775 `rate` float(7,5) default NULL,
776 PRIMARY KEY (`currency`)
777 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
780 -- Table structure for table `deletedbiblio`
783 DROP TABLE IF EXISTS `deletedbiblio`;
784 CREATE TABLE `deletedbiblio` (
785 `biblionumber` int(11) NOT NULL default 0,
786 `frameworkcode` varchar(4) NOT NULL default '',
789 `unititle` mediumtext,
791 `serial` tinyint(1) default NULL,
792 `seriestitle` mediumtext,
793 `copyrightdate` smallint(6) default NULL,
794 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
795 `datecreated` DATE NOT NULL,
796 `abstract` mediumtext,
797 PRIMARY KEY (`biblionumber`),
798 KEY `blbnoidx` (`biblionumber`)
799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
802 -- Table structure for table `deletedbiblioitems`
805 DROP TABLE IF EXISTS `deletedbiblioitems`;
806 CREATE TABLE `deletedbiblioitems` (
807 `biblioitemnumber` int(11) NOT NULL default 0,
808 `biblionumber` int(11) NOT NULL default 0,
811 `itemtype` varchar(10) default NULL,
812 `isbn` varchar(30) default NULL,
813 `issn` varchar(9) default NULL,
814 `publicationyear` text,
815 `publishercode` varchar(255) default NULL,
816 `volumedate` date default NULL,
818 `collectiontitle` mediumtext default NULL,
819 `collectionissn` text default NULL,
820 `collectionvolume` mediumtext default NULL,
821 `editionstatement` text default NULL,
822 `editionresponsibility` text default NULL,
823 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
824 `illus` varchar(255) default NULL,
825 `pages` varchar(255) default NULL,
827 `size` varchar(255) default NULL,
828 `place` varchar(255) default NULL,
829 `lccn` varchar(25) default NULL,
831 `url` varchar(255) default NULL,
832 `cn_source` varchar(10) default NULL,
833 `cn_class` varchar(30) default NULL,
834 `cn_item` varchar(10) default NULL,
835 `cn_suffix` varchar(10) default NULL,
836 `cn_sort` varchar(30) default NULL,
837 `totalissues` int(10),
838 `marcxml` longtext NOT NULL,
839 PRIMARY KEY (`biblioitemnumber`),
840 KEY `bibinoidx` (`biblioitemnumber`),
841 KEY `bibnoidx` (`biblionumber`),
843 KEY `publishercode` (`publishercode`)
844 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
847 -- Table structure for table `deletedborrowers`
850 DROP TABLE IF EXISTS `deletedborrowers`;
851 CREATE TABLE `deletedborrowers` (
852 `borrowernumber` int(11) NOT NULL default 0,
853 `cardnumber` varchar(9) NOT NULL default '',
854 `surname` mediumtext NOT NULL,
857 `othernames` mediumtext,
859 `streetnumber` varchar(10) default NULL,
860 `streettype` varchar(50) default NULL,
861 `address` mediumtext NOT NULL,
863 `city` mediumtext NOT NULL,
864 `zipcode` varchar(25) default NULL,
868 `mobile` varchar(50) default NULL,
872 `B_streetnumber` varchar(10) default NULL,
873 `B_streettype` varchar(50) default NULL,
874 `B_address` varchar(100) default NULL,
876 `B_zipcode` varchar(25) default NULL,
879 `B_phone` mediumtext,
880 `dateofbirth` date default NULL,
881 `branchcode` varchar(10) NOT NULL default '',
882 `categorycode` varchar(10) default NULL,
883 `dateenrolled` date default NULL,
884 `dateexpiry` date default NULL,
885 `gonenoaddress` tinyint(1) default NULL,
886 `lost` tinyint(1) default NULL,
887 `debarred` tinyint(1) default NULL,
888 `contactname` mediumtext,
889 `contactfirstname` text,
891 `guarantorid` int(11) default NULL,
892 `borrowernotes` mediumtext,
893 `relationship` varchar(100) default NULL,
894 `ethnicity` varchar(50) default NULL,
895 `ethnotes` varchar(255) default NULL,
896 `sex` varchar(1) default NULL,
897 `password` varchar(30) default NULL,
898 `flags` int(11) default NULL,
899 `userid` varchar(30) default NULL,
900 `opacnote` mediumtext,
901 `contactnote` varchar(255) default NULL,
902 `sort1` varchar(80) default NULL,
903 `sort2` varchar(80) default NULL,
904 `altcontactfirstname` varchar(255) default NULL,
905 `altcontactsurname` varchar(255) default NULL,
906 `altcontactaddress1` varchar(255) default NULL,
907 `altcontactaddress2` varchar(255) default NULL,
908 `altcontactaddress3` varchar(255) default NULL,
909 `altcontactzipcode` varchar(50) default NULL,
910 `altcontactphone` varchar(50) default NULL,
911 `smsalertnumber` varchar(50) default NULL,
912 KEY `borrowernumber` (`borrowernumber`),
913 KEY `cardnumber` (`cardnumber`)
914 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
917 -- Table structure for table `deleteditems`
920 DROP TABLE IF EXISTS `deleteditems`;
921 CREATE TABLE `deleteditems` (
922 `itemnumber` int(11) NOT NULL default 0,
923 `biblionumber` int(11) NOT NULL default 0,
924 `biblioitemnumber` int(11) NOT NULL default 0,
925 `barcode` varchar(20) default NULL,
926 `dateaccessioned` date default NULL,
927 `booksellerid` mediumtext default NULL,
928 `homebranch` varchar(10) default NULL,
929 `price` decimal(8,2) default NULL,
930 `replacementprice` decimal(8,2) default NULL,
931 `replacementpricedate` date default NULL,
932 `datelastborrowed` date default NULL,
933 `datelastseen` date default NULL,
934 `stack` tinyint(1) default NULL,
935 `notforloan` tinyint(1) NOT NULL default 0,
936 `damaged` tinyint(1) NOT NULL default 0,
937 `itemlost` tinyint(1) NOT NULL default 0,
938 `wthdrawn` tinyint(1) NOT NULL default 0,
939 `itemcallnumber` varchar(255) default NULL,
940 `issues` smallint(6) default NULL,
941 `renewals` smallint(6) default NULL,
942 `reserves` smallint(6) default NULL,
943 `restricted` tinyint(1) default NULL,
944 `itemnotes` mediumtext,
945 `holdingbranch` varchar(10) default NULL,
946 `paidfor` mediumtext,
947 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
948 `location` varchar(80) default NULL,
949 `permanent_location` varchar(80) default NULL,
950 `onloan` date default NULL,
951 `cn_source` varchar(10) default NULL,
952 `cn_sort` varchar(30) default NULL,
953 `ccode` varchar(10) default NULL,
954 `materials` varchar(10) default NULL,
955 `uri` varchar(255) default NULL,
956 `itype` varchar(10) default NULL,
957 `more_subfields_xml` longtext default NULL,
958 `enumchron` varchar(80) default NULL,
959 `copynumber` varchar(32) default NULL,
961 PRIMARY KEY (`itemnumber`),
962 KEY `delitembarcodeidx` (`barcode`),
963 KEY `delitembinoidx` (`biblioitemnumber`),
964 KEY `delitembibnoidx` (`biblionumber`),
965 KEY `delhomebranch` (`homebranch`),
966 KEY `delholdingbranch` (`holdingbranch`)
967 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
970 -- Table structure for table `ethnicity`
973 DROP TABLE IF EXISTS `ethnicity`;
974 CREATE TABLE `ethnicity` (
975 `code` varchar(10) NOT NULL default '',
976 `name` varchar(255) default NULL,
978 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
981 -- Table structure for table `hold_fill_targets`
984 DROP TABLE IF EXISTS `hold_fill_targets`;
985 CREATE TABLE hold_fill_targets (
986 `borrowernumber` int(11) NOT NULL,
987 `biblionumber` int(11) NOT NULL,
988 `itemnumber` int(11) NOT NULL,
989 `source_branchcode` varchar(10) default NULL,
990 `item_level_request` tinyint(4) NOT NULL default 0,
991 PRIMARY KEY `itemnumber` (`itemnumber`),
992 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
993 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
994 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
995 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
996 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
997 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
998 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
999 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
1000 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1004 -- Table structure for table `import_batches`
1007 DROP TABLE IF EXISTS `import_batches`;
1008 CREATE TABLE `import_batches` (
1009 `import_batch_id` int(11) NOT NULL auto_increment,
1010 `matcher_id` int(11) default NULL,
1011 `template_id` int(11) default NULL,
1012 `branchcode` varchar(10) default NULL,
1013 `num_biblios` int(11) NOT NULL default 0,
1014 `num_items` int(11) NOT NULL default 0,
1015 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1016 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1017 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1018 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1019 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1020 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1021 `file_name` varchar(100),
1022 `comments` mediumtext,
1023 PRIMARY KEY (`import_batch_id`),
1024 KEY `branchcode` (`branchcode`)
1025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1028 -- Table structure for table `import_records`
1031 DROP TABLE IF EXISTS `import_records`;
1032 CREATE TABLE `import_records` (
1033 `import_record_id` int(11) NOT NULL auto_increment,
1034 `import_batch_id` int(11) NOT NULL,
1035 `branchcode` varchar(10) default NULL,
1036 `record_sequence` int(11) NOT NULL default 0,
1037 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1038 `import_date` DATE default NULL,
1039 `marc` longblob NOT NULL,
1040 `marcxml` longtext NOT NULL,
1041 `marcxml_old` longtext NOT NULL,
1042 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1043 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1044 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1045 `import_error` mediumtext,
1046 `encoding` varchar(40) NOT NULL default '',
1047 `z3950random` varchar(40) default NULL,
1048 PRIMARY KEY (`import_record_id`),
1049 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1050 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1051 KEY `branchcode` (`branchcode`),
1052 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1053 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1056 -- Table structure for `import_record_matches`
1058 DROP TABLE IF EXISTS `import_record_matches`;
1059 CREATE TABLE `import_record_matches` (
1060 `import_record_id` int(11) NOT NULL,
1061 `candidate_match_id` int(11) NOT NULL,
1062 `score` int(11) NOT NULL default 0,
1063 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1064 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1065 KEY `record_score` (`import_record_id`, `score`)
1066 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1069 -- Table structure for table `import_biblios`
1072 DROP TABLE IF EXISTS `import_biblios`;
1073 CREATE TABLE `import_biblios` (
1074 `import_record_id` int(11) NOT NULL,
1075 `matched_biblionumber` int(11) default NULL,
1076 `control_number` varchar(25) default NULL,
1077 `original_source` varchar(25) default NULL,
1078 `title` varchar(128) default NULL,
1079 `author` varchar(80) default NULL,
1080 `isbn` varchar(30) default NULL,
1081 `issn` varchar(9) default NULL,
1082 `has_items` tinyint(1) NOT NULL default 0,
1083 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1084 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1085 KEY `matched_biblionumber` (`matched_biblionumber`),
1086 KEY `title` (`title`),
1088 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1091 -- Table structure for table `import_items`
1094 DROP TABLE IF EXISTS `import_items`;
1095 CREATE TABLE `import_items` (
1096 `import_items_id` int(11) NOT NULL auto_increment,
1097 `import_record_id` int(11) NOT NULL,
1098 `itemnumber` int(11) default NULL,
1099 `branchcode` varchar(10) default NULL,
1100 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1101 `marcxml` longtext NOT NULL,
1102 `import_error` mediumtext,
1103 PRIMARY KEY (`import_items_id`),
1104 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1105 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1106 KEY `itemnumber` (`itemnumber`),
1107 KEY `branchcode` (`branchcode`)
1108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1111 -- Table structure for table `issues`
1114 DROP TABLE IF EXISTS `issues`;
1115 CREATE TABLE `issues` (
1116 `borrowernumber` int(11) default NULL,
1117 `itemnumber` int(11) default NULL,
1118 `date_due` date default NULL,
1119 `branchcode` varchar(10) default NULL,
1120 `issuingbranch` varchar(18) default NULL,
1121 `returndate` date default NULL,
1122 `lastreneweddate` date default NULL,
1123 `return` varchar(4) default NULL,
1124 `renewals` tinyint(4) default NULL,
1125 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1126 `issuedate` date default NULL,
1127 KEY `issuesborridx` (`borrowernumber`),
1128 KEY `issuesitemidx` (`itemnumber`),
1129 KEY `bordate` (`borrowernumber`,`timestamp`),
1130 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1131 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1135 -- Table structure for table `issuingrules`
1138 DROP TABLE IF EXISTS `issuingrules`;
1139 CREATE TABLE `issuingrules` (
1140 `categorycode` varchar(10) NOT NULL default '',
1141 `itemtype` varchar(10) NOT NULL default '',
1142 `restrictedtype` tinyint(1) default NULL,
1143 `rentaldiscount` decimal(28,6) default NULL,
1144 `reservecharge` decimal(28,6) default NULL,
1145 `fine` decimal(28,6) default NULL,
1146 `firstremind` int(11) default NULL,
1147 `chargeperiod` int(11) default NULL,
1148 `accountsent` int(11) default NULL,
1149 `chargename` varchar(100) default NULL,
1150 `maxissueqty` int(4) default NULL,
1151 `issuelength` int(4) default NULL,
1152 `branchcode` varchar(10) NOT NULL default '',
1153 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1154 KEY `categorycode` (`categorycode`),
1155 KEY `itemtype` (`itemtype`)
1156 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1159 -- Table structure for table `items`
1162 DROP TABLE IF EXISTS `items`;
1163 CREATE TABLE `items` (
1164 `itemnumber` int(11) NOT NULL auto_increment,
1165 `biblionumber` int(11) NOT NULL default 0,
1166 `biblioitemnumber` int(11) NOT NULL default 0,
1167 `barcode` varchar(20) default NULL,
1168 `dateaccessioned` date default NULL,
1169 `booksellerid` mediumtext default NULL,
1170 `homebranch` varchar(10) default NULL,
1171 `price` decimal(8,2) default NULL,
1172 `replacementprice` decimal(8,2) default NULL,
1173 `replacementpricedate` date default NULL,
1174 `datelastborrowed` date default NULL,
1175 `datelastseen` date default NULL,
1176 `stack` tinyint(1) default NULL,
1177 `notforloan` tinyint(1) NOT NULL default 0,
1178 `damaged` tinyint(1) NOT NULL default 0,
1179 `itemlost` tinyint(1) NOT NULL default 0,
1180 `wthdrawn` tinyint(1) NOT NULL default 0,
1181 `itemcallnumber` varchar(255) default NULL,
1182 `issues` smallint(6) default NULL,
1183 `renewals` smallint(6) default NULL,
1184 `reserves` smallint(6) default NULL,
1185 `restricted` tinyint(1) default NULL,
1186 `itemnotes` mediumtext,
1187 `holdingbranch` varchar(10) default NULL,
1188 `paidfor` mediumtext,
1189 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1190 `location` varchar(80) default NULL,
1191 `permanent_location` varchar(80) default NULL,
1192 `onloan` date default NULL,
1193 `cn_source` varchar(10) default NULL,
1194 `cn_sort` varchar(30) default NULL,
1195 `ccode` varchar(10) default NULL,
1196 `materials` varchar(10) default NULL,
1197 `uri` varchar(255) default NULL,
1198 `itype` varchar(10) default NULL,
1199 `more_subfields_xml` longtext default NULL,
1200 `enumchron` varchar(80) default NULL,
1201 `copynumber` varchar(32) default NULL,
1202 PRIMARY KEY (`itemnumber`),
1203 UNIQUE KEY `itembarcodeidx` (`barcode`),
1204 KEY `itembinoidx` (`biblioitemnumber`),
1205 KEY `itembibnoidx` (`biblionumber`),
1206 KEY `homebranch` (`homebranch`),
1207 KEY `holdingbranch` (`holdingbranch`),
1208 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1209 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1210 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1214 -- Table structure for table `itemtypes`
1217 DROP TABLE IF EXISTS `itemtypes`;
1218 CREATE TABLE `itemtypes` (
1219 `itemtype` varchar(10) NOT NULL default '',
1220 `description` mediumtext,
1221 `renewalsallowed` smallint(6) default NULL,
1222 `rentalcharge` double(16,4) default NULL,
1223 `notforloan` smallint(6) default NULL,
1224 `imageurl` varchar(200) default NULL,
1226 PRIMARY KEY (`itemtype`),
1227 UNIQUE KEY `itemtype` (`itemtype`)
1228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1231 -- Table structure for table `labels_batches`
1234 DROP TABLE IF EXISTS `labels_batches`;
1235 CREATE TABLE `labels_batches` (
1236 `label_id` int(11) NOT NULL auto_increment,
1237 `batch_id` int(10) NOT NULL default '1',
1238 `item_number` int(11) NOT NULL default '0',
1239 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1240 `branch_code` varchar(10) NOT NULL default 'NB',
1241 PRIMARY KEY USING BTREE (`label_id`),
1242 KEY `branch_fk` (`branch_code`),
1243 KEY `item_fk` (`item_number`),
1244 CONSTRAINT `item_fk_constraint` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE,
1245 CONSTRAINT `branch_fk_constraint` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE
1246 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1249 -- Table structure for table `labels_layouts`
1252 DROP TABLE IF EXISTS `labels_layouts`;
1253 CREATE TABLE `labels_layouts` (
1254 `layout_id` int(4) NOT NULL auto_increment,
1255 `barcode_type` char(100) NOT NULL default 'CODE39',
1256 `printing_type` char(32) NOT NULL default 'BAR',
1257 `layout_name` char(20) NOT NULL default 'DEFAULT',
1258 `guidebox` int(1) default '0',
1259 `font` char(10) character set utf8 collate utf8_unicode_ci NOT NULL default 'TR',
1260 `font_size` int(4) NOT NULL default '10',
1261 `callnum_split` int(1) default '0',
1262 `text_justify` char(1) character set utf8 collate utf8_unicode_ci NOT NULL default 'L',
1263 `format_string` varchar(210) NOT NULL default 'barcode',
1264 PRIMARY KEY USING BTREE (`layout_id`)
1265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1268 -- Table structure for table `labels_templates`
1271 DROP TABLE IF EXISTS `labels_templates`;
1272 CREATE TABLE `labels_templates` (
1273 `template_id` int(4) NOT NULL auto_increment,
1274 `profile_id` int(4) default NULL,
1275 `template_code` char(100) NOT NULL default 'DEFAULT TEMPLATE',
1276 `template_desc` char(100) NOT NULL default 'Default description',
1277 `page_width` float NOT NULL default '0',
1278 `page_height` float NOT NULL default '0',
1279 `label_width` float NOT NULL default '0',
1280 `label_height` float NOT NULL default '0',
1281 `top_text_margin` float NOT NULL default '0',
1282 `left_text_margin` float NOT NULL default '0',
1283 `top_margin` float NOT NULL default '0',
1284 `left_margin` float NOT NULL default '0',
1285 `cols` int(2) NOT NULL default '0',
1286 `rows` int(2) NOT NULL default '0',
1287 `col_gap` float NOT NULL default '0',
1288 `row_gap` float NOT NULL default '0',
1289 `units` char(20) NOT NULL default 'POINT',
1290 PRIMARY KEY (`template_id`),
1291 KEY `template_profile_fk_constraint` (`profile_id`)
1292 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1295 -- Table structure for table `letter`
1298 DROP TABLE IF EXISTS `letter`;
1299 CREATE TABLE `letter` (
1300 `module` varchar(20) NOT NULL default '',
1301 `code` varchar(20) NOT NULL default '',
1302 `name` varchar(100) NOT NULL default '',
1303 `title` varchar(200) NOT NULL default '',
1305 PRIMARY KEY (`module`,`code`)
1306 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1309 -- Table structure for table `marc_subfield_structure`
1312 DROP TABLE IF EXISTS `marc_subfield_structure`;
1313 CREATE TABLE `marc_subfield_structure` (
1314 `tagfield` varchar(3) NOT NULL default '',
1315 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1316 `liblibrarian` varchar(255) NOT NULL default '',
1317 `libopac` varchar(255) NOT NULL default '',
1318 `repeatable` tinyint(4) NOT NULL default 0,
1319 `mandatory` tinyint(4) NOT NULL default 0,
1320 `kohafield` varchar(40) default NULL,
1321 `tab` tinyint(1) default NULL,
1322 `authorised_value` varchar(20) default NULL,
1323 `authtypecode` varchar(20) default NULL,
1324 `value_builder` varchar(80) default NULL,
1325 `isurl` tinyint(1) default NULL,
1326 `hidden` tinyint(1) default NULL,
1327 `frameworkcode` varchar(4) NOT NULL default '',
1328 `seealso` varchar(1100) default NULL,
1329 `link` varchar(80) default NULL,
1330 `defaultvalue` text default NULL,
1331 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1332 KEY `kohafield_2` (`kohafield`),
1333 KEY `tab` (`frameworkcode`,`tab`),
1334 KEY `kohafield` (`frameworkcode`,`kohafield`)
1335 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1338 -- Table structure for table `marc_tag_structure`
1341 DROP TABLE IF EXISTS `marc_tag_structure`;
1342 CREATE TABLE `marc_tag_structure` (
1343 `tagfield` varchar(3) NOT NULL default '',
1344 `liblibrarian` varchar(255) NOT NULL default '',
1345 `libopac` varchar(255) NOT NULL default '',
1346 `repeatable` tinyint(4) NOT NULL default 0,
1347 `mandatory` tinyint(4) NOT NULL default 0,
1348 `authorised_value` varchar(10) default NULL,
1349 `frameworkcode` varchar(4) NOT NULL default '',
1350 PRIMARY KEY (`frameworkcode`,`tagfield`)
1351 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1354 -- Table structure for table `marc_matchers`
1357 DROP TABLE IF EXISTS `marc_matchers`;
1358 CREATE TABLE `marc_matchers` (
1359 `matcher_id` int(11) NOT NULL auto_increment,
1360 `code` varchar(10) NOT NULL default '',
1361 `description` varchar(255) NOT NULL default '',
1362 `record_type` varchar(10) NOT NULL default 'biblio',
1363 `threshold` int(11) NOT NULL default 0,
1364 PRIMARY KEY (`matcher_id`),
1365 KEY `code` (`code`),
1366 KEY `record_type` (`record_type`)
1367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1370 -- Table structure for table `matchpoints`
1372 DROP TABLE IF EXISTS `matchpoints`;
1373 CREATE TABLE `matchpoints` (
1374 `matcher_id` int(11) NOT NULL,
1375 `matchpoint_id` int(11) NOT NULL auto_increment,
1376 `search_index` varchar(30) NOT NULL default '',
1377 `score` int(11) NOT NULL default 0,
1378 PRIMARY KEY (`matchpoint_id`),
1379 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1380 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1381 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1385 -- Table structure for table `matchpoint_components`
1387 DROP TABLE IF EXISTS `matchpoint_components`;
1388 CREATE TABLE `matchpoint_components` (
1389 `matchpoint_id` int(11) NOT NULL,
1390 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1391 sequence int(11) NOT NULL default 0,
1392 tag varchar(3) NOT NULL default '',
1393 subfields varchar(40) NOT NULL default '',
1394 offset int(4) NOT NULL default 0,
1395 length int(4) NOT NULL default 0,
1396 PRIMARY KEY (`matchpoint_component_id`),
1397 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1398 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1399 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1400 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1403 -- Table structure for table `matcher_component_norms`
1405 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1406 CREATE TABLE `matchpoint_component_norms` (
1407 `matchpoint_component_id` int(11) NOT NULL,
1408 `sequence` int(11) NOT NULL default 0,
1409 `norm_routine` varchar(50) NOT NULL default '',
1410 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1411 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1412 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1413 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1416 -- Table structure for table `matcher_matchpoints`
1418 DROP TABLE IF EXISTS `matcher_matchpoints`;
1419 CREATE TABLE `matcher_matchpoints` (
1420 `matcher_id` int(11) NOT NULL,
1421 `matchpoint_id` int(11) NOT NULL,
1422 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1423 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1424 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1425 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1426 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1429 -- Table structure for table `matchchecks`
1431 DROP TABLE IF EXISTS `matchchecks`;
1432 CREATE TABLE `matchchecks` (
1433 `matcher_id` int(11) NOT NULL,
1434 `matchcheck_id` int(11) NOT NULL auto_increment,
1435 `source_matchpoint_id` int(11) NOT NULL,
1436 `target_matchpoint_id` int(11) NOT NULL,
1437 PRIMARY KEY (`matchcheck_id`),
1438 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1439 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1440 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1441 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1442 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1443 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1444 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1447 -- Table structure for table `notifys`
1450 DROP TABLE IF EXISTS `notifys`;
1451 CREATE TABLE `notifys` (
1452 `notify_id` int(11) NOT NULL default 0,
1453 `borrowernumber` int(11) NOT NULL default 0,
1454 `itemnumber` int(11) NOT NULL default 0,
1455 `notify_date` date default NULL,
1456 `notify_send_date` date default NULL,
1457 `notify_level` int(1) NOT NULL default 0,
1458 `method` varchar(20) NOT NULL default ''
1459 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1462 -- Table structure for table `nozebra`
1465 DROP TABLE IF EXISTS `nozebra`;
1466 CREATE TABLE `nozebra` (
1467 `server` varchar(20) NOT NULL,
1468 `indexname` varchar(40) NOT NULL,
1469 `value` varchar(250) NOT NULL,
1470 `biblionumbers` longtext NOT NULL,
1471 KEY `indexname` (`server`,`indexname`),
1472 KEY `value` (`server`,`value`))
1473 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1476 -- Table structure for table `old_issues`
1479 DROP TABLE IF EXISTS `old_issues`;
1480 CREATE TABLE `old_issues` (
1481 `borrowernumber` int(11) default NULL,
1482 `itemnumber` int(11) default NULL,
1483 `date_due` date default NULL,
1484 `branchcode` varchar(10) default NULL,
1485 `issuingbranch` varchar(18) default NULL,
1486 `returndate` date default NULL,
1487 `lastreneweddate` date default NULL,
1488 `return` varchar(4) default NULL,
1489 `renewals` tinyint(4) default NULL,
1490 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1491 `issuedate` date default NULL,
1492 KEY `old_issuesborridx` (`borrowernumber`),
1493 KEY `old_issuesitemidx` (`itemnumber`),
1494 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1495 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1496 ON DELETE SET NULL ON UPDATE SET NULL,
1497 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1498 ON DELETE SET NULL ON UPDATE SET NULL
1499 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1502 -- Table structure for table `old_reserves`
1504 DROP TABLE IF EXISTS `old_reserves`;
1505 CREATE TABLE `old_reserves` (
1506 `borrowernumber` int(11) default NULL,
1507 `reservedate` date default NULL,
1508 `biblionumber` int(11) default NULL,
1509 `constrainttype` varchar(1) default NULL,
1510 `branchcode` varchar(10) default NULL,
1511 `notificationdate` date default NULL,
1512 `reminderdate` date default NULL,
1513 `cancellationdate` date default NULL,
1514 `reservenotes` mediumtext,
1515 `priority` smallint(6) default NULL,
1516 `found` varchar(1) default NULL,
1517 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1518 `itemnumber` int(11) default NULL,
1519 `waitingdate` date default NULL,
1520 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1521 KEY `old_reserves_biblionumber` (`biblionumber`),
1522 KEY `old_reserves_itemnumber` (`itemnumber`),
1523 KEY `old_reserves_branchcode` (`branchcode`),
1524 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1525 ON DELETE SET NULL ON UPDATE SET NULL,
1526 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1527 ON DELETE SET NULL ON UPDATE SET NULL,
1528 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1529 ON DELETE SET NULL ON UPDATE SET NULL
1530 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1533 -- Table structure for table `opac_news`
1536 DROP TABLE IF EXISTS `opac_news`;
1537 CREATE TABLE `opac_news` (
1538 `idnew` int(10) unsigned NOT NULL auto_increment,
1539 `title` varchar(250) NOT NULL default '',
1540 `new` text NOT NULL,
1541 `lang` varchar(25) NOT NULL default '',
1542 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1543 `expirationdate` date default NULL,
1544 `number` int(11) default NULL,
1545 PRIMARY KEY (`idnew`)
1546 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1549 -- Table structure for table `overduerules`
1552 DROP TABLE IF EXISTS `overduerules`;
1553 CREATE TABLE `overduerules` (
1554 `branchcode` varchar(10) NOT NULL default '',
1555 `categorycode` varchar(10) NOT NULL default '',
1556 `delay1` int(4) default 0,
1557 `letter1` varchar(20) default NULL,
1558 `debarred1` varchar(1) default 0,
1559 `delay2` int(4) default 0,
1560 `debarred2` varchar(1) default 0,
1561 `letter2` varchar(20) default NULL,
1562 `delay3` int(4) default 0,
1563 `letter3` varchar(20) default NULL,
1564 `debarred3` int(1) default 0,
1565 PRIMARY KEY (`branchcode`,`categorycode`)
1566 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1569 -- Table structure for table `patroncards`
1572 DROP TABLE IF EXISTS `patroncards`;
1573 CREATE TABLE `patroncards` (
1574 `cardid` int(11) NOT NULL auto_increment,
1575 `batch_id` varchar(10) NOT NULL default '1',
1576 `borrowernumber` int(11) NOT NULL,
1577 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1578 PRIMARY KEY (`cardid`),
1579 KEY `patroncards_ibfk_1` (`borrowernumber`),
1580 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1581 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1584 -- Table structure for table `patronimage`
1587 DROP TABLE IF EXISTS `patronimage`;
1588 CREATE TABLE `patronimage` (
1589 `cardnumber` varchar(16) NOT NULL,
1590 `mimetype` varchar(15) NOT NULL,
1591 `imagefile` mediumblob NOT NULL,
1592 PRIMARY KEY (`cardnumber`),
1593 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1594 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1597 -- Table structure for table `printers`
1600 DROP TABLE IF EXISTS `printers`;
1601 CREATE TABLE `printers` (
1602 `printername` varchar(40) NOT NULL default '',
1603 `printqueue` varchar(20) default NULL,
1604 `printtype` varchar(20) default NULL,
1605 PRIMARY KEY (`printername`)
1606 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1609 -- Table structure for table `printers_profile`
1612 DROP TABLE IF EXISTS `printers_profile`;
1613 CREATE TABLE `printers_profile` (
1614 `profile_id` int(4) NOT NULL auto_increment,
1615 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1616 `template_id` int(4) NOT NULL default '0',
1617 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1618 `offset_horz` float NOT NULL default '0',
1619 `offset_vert` float NOT NULL default '0',
1620 `creep_horz` float NOT NULL default '0',
1621 `creep_vert` float NOT NULL default '0',
1622 `units` char(20) NOT NULL default 'POINT',
1623 PRIMARY KEY (`profile_id`),
1624 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`)
1625 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1628 -- Table structure for table `repeatable_holidays`
1631 DROP TABLE IF EXISTS `repeatable_holidays`;
1632 CREATE TABLE `repeatable_holidays` (
1633 `id` int(11) NOT NULL auto_increment,
1634 `branchcode` varchar(10) NOT NULL default '',
1635 `weekday` smallint(6) default NULL,
1636 `day` smallint(6) default NULL,
1637 `month` smallint(6) default NULL,
1638 `title` varchar(50) NOT NULL default '',
1639 `description` text NOT NULL,
1641 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1644 -- Table structure for table `reports_dictionary`
1647 DROP TABLE IF EXISTS `reports_dictionary`;
1648 CREATE TABLE reports_dictionary (
1649 `id` int(11) NOT NULL auto_increment,
1650 `name` varchar(255) default NULL,
1652 `date_created` datetime default NULL,
1653 `date_modified` datetime default NULL,
1655 `area` int(11) default NULL,
1657 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1660 -- Table structure for table `reserveconstraints`
1663 DROP TABLE IF EXISTS `reserveconstraints`;
1664 CREATE TABLE `reserveconstraints` (
1665 `borrowernumber` int(11) NOT NULL default 0,
1666 `reservedate` date default NULL,
1667 `biblionumber` int(11) NOT NULL default 0,
1668 `biblioitemnumber` int(11) default NULL,
1669 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1670 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1673 -- Table structure for table `reserves`
1676 DROP TABLE IF EXISTS `reserves`;
1677 CREATE TABLE `reserves` (
1678 `borrowernumber` int(11) NOT NULL default 0,
1679 `reservedate` date default NULL,
1680 `biblionumber` int(11) NOT NULL default 0,
1681 `constrainttype` varchar(1) default NULL,
1682 `branchcode` varchar(10) default NULL,
1683 `notificationdate` date default NULL,
1684 `reminderdate` date default NULL,
1685 `cancellationdate` date default NULL,
1686 `reservenotes` mediumtext,
1687 `priority` smallint(6) default NULL,
1688 `found` varchar(1) default NULL,
1689 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1690 `itemnumber` int(11) default NULL,
1691 `waitingdate` date default NULL,
1692 KEY `borrowernumber` (`borrowernumber`),
1693 KEY `biblionumber` (`biblionumber`),
1694 KEY `itemnumber` (`itemnumber`),
1695 KEY `branchcode` (`branchcode`),
1696 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1697 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1698 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1699 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1700 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1703 -- Table structure for table `reviews`
1706 DROP TABLE IF EXISTS `reviews`;
1707 CREATE TABLE `reviews` (
1708 `reviewid` int(11) NOT NULL auto_increment,
1709 `borrowernumber` int(11) default NULL,
1710 `biblionumber` int(11) default NULL,
1712 `approved` tinyint(4) default NULL,
1713 `datereviewed` datetime default NULL,
1714 PRIMARY KEY (`reviewid`)
1715 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1718 -- Table structure for table `roadtype`
1721 DROP TABLE IF EXISTS `roadtype`;
1722 CREATE TABLE `roadtype` (
1723 `roadtypeid` int(11) NOT NULL auto_increment,
1724 `road_type` varchar(100) NOT NULL default '',
1725 PRIMARY KEY (`roadtypeid`)
1726 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1729 -- Table structure for table `saved_sql`
1732 DROP TABLE IF EXISTS `saved_sql`;
1733 CREATE TABLE saved_sql (
1734 `id` int(11) NOT NULL auto_increment,
1735 `borrowernumber` int(11) default NULL,
1736 `date_created` datetime default NULL,
1737 `last_modified` datetime default NULL,
1739 `last_run` datetime default NULL,
1740 `report_name` varchar(255) default NULL,
1741 `type` varchar(255) default NULL,
1744 KEY boridx (`borrowernumber`)
1745 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1749 -- Table structure for `saved_reports`
1752 DROP TABLE IF EXISTS `saved_reports`;
1753 CREATE TABLE saved_reports (
1754 `id` int(11) NOT NULL auto_increment,
1755 `report_id` int(11) default NULL,
1757 `date_run` datetime default NULL,
1759 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1763 -- Table structure for table `serial`
1766 DROP TABLE IF EXISTS `serial`;
1767 CREATE TABLE `serial` (
1768 `serialid` int(11) NOT NULL auto_increment,
1769 `biblionumber` varchar(100) NOT NULL default '',
1770 `subscriptionid` varchar(100) NOT NULL default '',
1771 `serialseq` varchar(100) NOT NULL default '',
1772 `status` tinyint(4) NOT NULL default 0,
1773 `planneddate` date default NULL,
1775 `publisheddate` date default NULL,
1776 `itemnumber` text default NULL,
1777 `claimdate` date default NULL,
1778 `routingnotes` text,
1779 PRIMARY KEY (`serialid`)
1780 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1783 -- Table structure for table `sessions`
1786 DROP TABLE IF EXISTS sessions;
1787 CREATE TABLE sessions (
1788 `id` varchar(32) NOT NULL,
1789 `a_session` text NOT NULL,
1791 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1794 -- Table structure for table `special_holidays`
1797 DROP TABLE IF EXISTS `special_holidays`;
1798 CREATE TABLE `special_holidays` (
1799 `id` int(11) NOT NULL auto_increment,
1800 `branchcode` varchar(10) NOT NULL default '',
1801 `day` smallint(6) NOT NULL default 0,
1802 `month` smallint(6) NOT NULL default 0,
1803 `year` smallint(6) NOT NULL default 0,
1804 `isexception` smallint(1) NOT NULL default 1,
1805 `title` varchar(50) NOT NULL default '',
1806 `description` text NOT NULL,
1808 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1811 -- Table structure for table `statistics`
1814 DROP TABLE IF EXISTS `statistics`;
1815 CREATE TABLE `statistics` (
1816 `datetime` datetime default NULL,
1817 `branch` varchar(10) default NULL,
1818 `proccode` varchar(4) default NULL,
1819 `value` double(16,4) default NULL,
1820 `type` varchar(16) default NULL,
1822 `usercode` varchar(10) default NULL,
1823 `itemnumber` int(11) default NULL,
1824 `itemtype` varchar(10) default NULL,
1825 `borrowernumber` int(11) default NULL,
1826 `associatedborrower` int(11) default NULL,
1827 KEY `timeidx` (`datetime`)
1828 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1831 -- Table structure for table `stopwords`
1834 DROP TABLE IF EXISTS `stopwords`;
1835 CREATE TABLE `stopwords` (
1836 `word` varchar(255) default NULL
1837 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1840 -- Table structure for table `subscription`
1843 DROP TABLE IF EXISTS `subscription`;
1844 CREATE TABLE `subscription` (
1845 `biblionumber` int(11) NOT NULL default 0,
1846 `subscriptionid` int(11) NOT NULL auto_increment,
1847 `librarian` varchar(100) default '',
1848 `startdate` date default NULL,
1849 `aqbooksellerid` int(11) default 0,
1850 `cost` int(11) default 0,
1851 `aqbudgetid` int(11) default 0,
1852 `weeklength` int(11) default 0,
1853 `monthlength` int(11) default 0,
1854 `numberlength` int(11) default 0,
1855 `periodicity` tinyint(4) default 0,
1856 `dow` varchar(100) default '',
1857 `numberingmethod` varchar(100) default '',
1859 `status` varchar(100) NOT NULL default '',
1860 `add1` int(11) default 0,
1861 `every1` int(11) default 0,
1862 `whenmorethan1` int(11) default 0,
1863 `setto1` int(11) default NULL,
1864 `lastvalue1` int(11) default NULL,
1865 `add2` int(11) default 0,
1866 `every2` int(11) default 0,
1867 `whenmorethan2` int(11) default 0,
1868 `setto2` int(11) default NULL,
1869 `lastvalue2` int(11) default NULL,
1870 `add3` int(11) default 0,
1871 `every3` int(11) default 0,
1872 `innerloop1` int(11) default 0,
1873 `innerloop2` int(11) default 0,
1874 `innerloop3` int(11) default 0,
1875 `whenmorethan3` int(11) default 0,
1876 `setto3` int(11) default NULL,
1877 `lastvalue3` int(11) default NULL,
1878 `issuesatonce` tinyint(3) NOT NULL default 1,
1879 `firstacquidate` date default NULL,
1880 `manualhistory` tinyint(1) NOT NULL default 0,
1881 `irregularity` text,
1882 `letter` varchar(20) default NULL,
1883 `numberpattern` tinyint(3) default 0,
1884 `distributedto` text,
1885 `internalnotes` longtext,
1887 `location` varchar(80) NULL default '',
1888 `branchcode` varchar(10) NOT NULL default '',
1889 `hemisphere` tinyint(3) default 0,
1890 `lastbranch` varchar(10),
1891 `serialsadditems` tinyint(1) NOT NULL default '0',
1892 `staffdisplaycount` VARCHAR(10) NULL,
1893 `opacdisplaycount` VARCHAR(10) NULL,
1894 `graceperiod` int(11) NOT NULL default '0',
1895 PRIMARY KEY (`subscriptionid`)
1896 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1899 -- Table structure for table `subscriptionhistory`
1902 DROP TABLE IF EXISTS `subscriptionhistory`;
1903 CREATE TABLE `subscriptionhistory` (
1904 `biblionumber` int(11) NOT NULL default 0,
1905 `subscriptionid` int(11) NOT NULL default 0,
1906 `histstartdate` date default NULL,
1907 `enddate` date default NULL,
1908 `missinglist` longtext NOT NULL,
1909 `recievedlist` longtext NOT NULL,
1910 `opacnote` varchar(150) NOT NULL default '',
1911 `librariannote` varchar(150) NOT NULL default '',
1912 PRIMARY KEY (`subscriptionid`),
1913 KEY `biblionumber` (`biblionumber`)
1914 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1917 -- Table structure for table `subscriptionroutinglist`
1920 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1921 CREATE TABLE `subscriptionroutinglist` (
1922 `routingid` int(11) NOT NULL auto_increment,
1923 `borrowernumber` int(11) default NULL,
1924 `ranking` int(11) default NULL,
1925 `subscriptionid` int(11) default NULL,
1926 PRIMARY KEY (`routingid`)
1927 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1930 -- Table structure for table `suggestions`
1933 DROP TABLE IF EXISTS `suggestions`;
1934 CREATE TABLE `suggestions` (
1935 `suggestionid` int(8) NOT NULL auto_increment,
1936 `suggestedby` int(11) NOT NULL default 0,
1937 `managedby` int(11) default NULL,
1938 `STATUS` varchar(10) NOT NULL default '',
1940 `author` varchar(80) default NULL,
1941 `title` varchar(80) default NULL,
1942 `copyrightdate` smallint(6) default NULL,
1943 `publishercode` varchar(255) default NULL,
1944 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1945 `volumedesc` varchar(255) default NULL,
1946 `publicationyear` smallint(6) default 0,
1947 `place` varchar(255) default NULL,
1948 `isbn` varchar(30) default NULL,
1949 `mailoverseeing` smallint(1) default 0,
1950 `biblionumber` int(11) default NULL,
1952 PRIMARY KEY (`suggestionid`),
1953 KEY `suggestedby` (`suggestedby`),
1954 KEY `managedby` (`managedby`)
1955 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1958 -- Table structure for table `systempreferences`
1961 DROP TABLE IF EXISTS `systempreferences`;
1962 CREATE TABLE `systempreferences` (
1963 `variable` varchar(50) NOT NULL default '',
1965 `options` mediumtext,
1967 `type` varchar(20) default NULL,
1968 PRIMARY KEY (`variable`)
1969 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1972 -- Table structure for table `tags`
1975 DROP TABLE IF EXISTS `tags`;
1976 CREATE TABLE `tags` (
1977 `entry` varchar(255) NOT NULL default '',
1978 `weight` bigint(20) NOT NULL default 0,
1979 PRIMARY KEY (`entry`)
1980 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1983 -- Table structure for table `tags_all`
1986 DROP TABLE IF EXISTS `tags_all`;
1987 CREATE TABLE `tags_all` (
1988 `tag_id` int(11) NOT NULL auto_increment,
1989 `borrowernumber` int(11) NOT NULL,
1990 `biblionumber` int(11) NOT NULL,
1991 `term` varchar(255) NOT NULL,
1992 `language` int(4) default NULL,
1993 `date_created` datetime NOT NULL,
1994 PRIMARY KEY (`tag_id`),
1995 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1996 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1997 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1998 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1999 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2000 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2004 -- Table structure for table `tags_approval`
2007 DROP TABLE IF EXISTS `tags_approval`;
2008 CREATE TABLE `tags_approval` (
2009 `term` varchar(255) NOT NULL,
2010 `approved` int(1) NOT NULL default '0',
2011 `date_approved` datetime default NULL,
2012 `approved_by` int(11) default NULL,
2013 `weight_total` int(9) NOT NULL default '1',
2014 PRIMARY KEY (`term`),
2015 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2016 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2017 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2018 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2021 -- Table structure for table `tags_index`
2024 DROP TABLE IF EXISTS `tags_index`;
2025 CREATE TABLE `tags_index` (
2026 `term` varchar(255) NOT NULL,
2027 `biblionumber` int(11) NOT NULL,
2028 `weight` int(9) NOT NULL default '1',
2029 PRIMARY KEY (`term`,`biblionumber`),
2030 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2031 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2032 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2033 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2034 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2035 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2038 -- Table structure for table `userflags`
2041 DROP TABLE IF EXISTS `userflags`;
2042 CREATE TABLE `userflags` (
2043 `bit` int(11) NOT NULL default 0,
2044 `flag` varchar(30) default NULL,
2045 `flagdesc` varchar(255) default NULL,
2046 `defaulton` int(11) default NULL,
2048 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2051 -- Table structure for table `virtualshelves`
2054 DROP TABLE IF EXISTS `virtualshelves`;
2055 CREATE TABLE `virtualshelves` (
2056 `shelfnumber` int(11) NOT NULL auto_increment,
2057 `shelfname` varchar(255) default NULL,
2058 `owner` varchar(80) default NULL,
2059 `category` varchar(1) default NULL,
2060 `sortfield` varchar(16) default NULL,
2061 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2062 PRIMARY KEY (`shelfnumber`)
2063 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2066 -- Table structure for table `virtualshelfcontents`
2069 DROP TABLE IF EXISTS `virtualshelfcontents`;
2070 CREATE TABLE `virtualshelfcontents` (
2071 `shelfnumber` int(11) NOT NULL default 0,
2072 `biblionumber` int(11) NOT NULL default 0,
2073 `flags` int(11) default NULL,
2074 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2075 KEY `shelfnumber` (`shelfnumber`),
2076 KEY `biblionumber` (`biblionumber`),
2077 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2078 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2079 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2082 -- Table structure for table `z3950servers`
2085 DROP TABLE IF EXISTS `z3950servers`;
2086 CREATE TABLE `z3950servers` (
2087 `host` varchar(255) default NULL,
2088 `port` int(11) default NULL,
2089 `db` varchar(255) default NULL,
2090 `userid` varchar(255) default NULL,
2091 `password` varchar(255) default NULL,
2093 `id` int(11) NOT NULL auto_increment,
2094 `checked` smallint(6) default NULL,
2095 `rank` int(11) default NULL,
2096 `syntax` varchar(80) default NULL,
2098 `position` enum('primary','secondary','') NOT NULL default 'primary',
2099 `type` enum('zed','opensearch') NOT NULL default 'zed',
2100 `encoding` text default NULL,
2101 `description` text NOT NULL,
2103 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2106 -- Table structure for table `zebraqueue`
2109 DROP TABLE IF EXISTS `zebraqueue`;
2110 CREATE TABLE `zebraqueue` (
2111 `id` int(11) NOT NULL auto_increment,
2112 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2113 `operation` char(20) NOT NULL default '',
2114 `server` char(20) NOT NULL default '',
2115 `done` int(11) NOT NULL default '0',
2116 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2118 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2119 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2121 DROP TABLE IF EXISTS `services_throttle`;
2122 CREATE TABLE `services_throttle` (
2123 `service_type` varchar(10) NOT NULL default '',
2124 `service_count` varchar(45) default NULL,
2125 PRIMARY KEY (`service_type`)
2126 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2128 -- http://www.w3.org/International/articles/language-tags/
2131 DROP TABLE IF EXISTS language_subtag_registry;
2132 CREATE TABLE language_subtag_registry (
2134 type varchar(25), -- language-script-region-variant-extension-privateuse
2135 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2137 KEY `subtag` (`subtag`)
2138 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2140 -- TODO: add suppress_scripts
2141 -- this maps three letter codes defined in iso639.2 back to their
2142 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2143 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2144 CREATE TABLE language_rfc4646_to_iso639 (
2145 rfc4646_subtag varchar(25),
2146 iso639_2_code varchar(25),
2147 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2148 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2150 DROP TABLE IF EXISTS language_descriptions;
2151 CREATE TABLE language_descriptions (
2155 description varchar(255),
2157 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2159 -- bi-directional support, keyed by script subcode
2160 DROP TABLE IF EXISTS language_script_bidi;
2161 CREATE TABLE language_script_bidi (
2162 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2163 bidi varchar(3), -- rtl ltr
2164 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2165 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2167 -- TODO: need to map language subtags to script subtags for detection
2168 -- of bidi when script is not specified (like ar, he)
2169 DROP TABLE IF EXISTS language_script_mapping;
2170 CREATE TABLE language_script_mapping (
2171 language_subtag varchar(25),
2172 script_subtag varchar(25),
2173 KEY `language_subtag` (`language_subtag`)
2174 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2176 DROP TABLE IF EXISTS `permissions`;
2177 CREATE TABLE `permissions` (
2178 `module_bit` int(11) NOT NULL DEFAULT 0,
2179 `code` varchar(64) DEFAULT NULL,
2180 `description` varchar(255) DEFAULT NULL,
2181 PRIMARY KEY (`module_bit`, `code`),
2182 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2183 ON DELETE CASCADE ON UPDATE CASCADE
2184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2186 DROP TABLE IF EXISTS `serialitems`;
2187 CREATE TABLE `serialitems` (
2188 `itemnumber` int(11) NOT NULL,
2189 `serialid` int(11) NOT NULL,
2190 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2191 KEY `serialitems_sfk_1` (`serialid`),
2192 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2193 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2195 DROP TABLE IF EXISTS `user_permissions`;
2196 CREATE TABLE `user_permissions` (
2197 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2198 `module_bit` int(11) NOT NULL DEFAULT 0,
2199 `code` varchar(64) DEFAULT NULL,
2200 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2201 ON DELETE CASCADE ON UPDATE CASCADE,
2202 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2203 ON DELETE CASCADE ON UPDATE CASCADE
2204 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2207 -- Table structure for table `tmp_holdsqueue`
2210 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2211 CREATE TABLE `tmp_holdsqueue` (
2212 `biblionumber` int(11) default NULL,
2213 `itemnumber` int(11) default NULL,
2214 `barcode` varchar(20) default NULL,
2215 `surname` mediumtext NOT NULL,
2218 `borrowernumber` int(11) NOT NULL,
2219 `cardnumber` varchar(16) default NULL,
2220 `reservedate` date default NULL,
2222 `itemcallnumber` varchar(255) default NULL,
2223 `holdingbranch` varchar(10) default NULL,
2224 `pickbranch` varchar(10) default NULL,
2226 `item_level_request` tinyint(4) NOT NULL default 0
2227 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2230 -- Table structure for table `message_queue`
2233 DROP TABLE IF EXISTS `message_queue`;
2234 CREATE TABLE `message_queue` (
2235 `message_id` int(11) NOT NULL auto_increment,
2236 `borrowernumber` int(11) default NULL,
2239 `metadata` text DEFAULT NULL,
2240 `letter_code` varchar(64) DEFAULT NULL,
2241 `message_transport_type` varchar(20) NOT NULL,
2242 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2243 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2244 `to_address` mediumtext,
2245 `from_address` mediumtext,
2246 `content_type` text,
2247 KEY `message_id` (`message_id`),
2248 KEY `borrowernumber` (`borrowernumber`),
2249 KEY `message_transport_type` (`message_transport_type`),
2250 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2251 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2252 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2255 -- Table structure for table `message_transport_types`
2258 DROP TABLE IF EXISTS `message_transport_types`;
2259 CREATE TABLE `message_transport_types` (
2260 `message_transport_type` varchar(20) NOT NULL,
2261 PRIMARY KEY (`message_transport_type`)
2262 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2265 -- Table structure for table `message_attributes`
2268 DROP TABLE IF EXISTS `message_attributes`;
2269 CREATE TABLE `message_attributes` (
2270 `message_attribute_id` int(11) NOT NULL auto_increment,
2271 `message_name` varchar(20) NOT NULL default '',
2272 `takes_days` tinyint(1) NOT NULL default '0',
2273 PRIMARY KEY (`message_attribute_id`),
2274 UNIQUE KEY `message_name` (`message_name`)
2275 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2278 -- Table structure for table `message_transports`
2281 DROP TABLE IF EXISTS `message_transports`;
2282 CREATE TABLE `message_transports` (
2283 `message_attribute_id` int(11) NOT NULL,
2284 `message_transport_type` varchar(20) NOT NULL,
2285 `is_digest` tinyint(1) NOT NULL default '0',
2286 `letter_module` varchar(20) NOT NULL default '',
2287 `letter_code` varchar(20) NOT NULL default '',
2288 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2289 KEY `message_transport_type` (`message_transport_type`),
2290 KEY `letter_module` (`letter_module`,`letter_code`),
2291 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2292 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2293 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2294 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2297 -- Table structure for table `borrower_message_preferences`
2300 DROP TABLE IF EXISTS `borrower_message_preferences`;
2301 CREATE TABLE `borrower_message_preferences` (
2302 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2303 `borrowernumber` int(11) default NULL,
2304 `categorycode` varchar(10) default NULL,
2305 `message_attribute_id` int(11) default '0',
2306 `days_in_advance` int(11) default '0',
2307 `wants_digest` tinyint(1) NOT NULL default '0',
2308 PRIMARY KEY (`borrower_message_preference_id`),
2309 KEY `borrowernumber` (`borrowernumber`),
2310 KEY `categorycode` (`categorycode`),
2311 KEY `message_attribute_id` (`message_attribute_id`),
2312 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2313 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2314 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2315 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2318 -- Table structure for table `borrower_message_transport_preferences`
2321 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2322 CREATE TABLE `borrower_message_transport_preferences` (
2323 `borrower_message_preference_id` int(11) NOT NULL default '0',
2324 `message_transport_type` varchar(20) NOT NULL default '0',
2325 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2326 KEY `message_transport_type` (`message_transport_type`),
2327 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,
2328 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
2329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2332 -- Table structure for the table branch_transfer_limits
2335 DROP TABLE IF EXISTS `branch_transfer_limits`;
2336 CREATE TABLE branch_transfer_limits (
2337 limitId int(8) NOT NULL auto_increment,
2338 toBranch varchar(10) NOT NULL,
2339 fromBranch varchar(10) NOT NULL,
2340 itemtype varchar(10) NULL,
2341 ccode varchar(10) NULL,
2342 PRIMARY KEY (limitId)
2343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2346 -- Table structure for table `item_circulation_alert_preferences`
2349 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2350 CREATE TABLE `item_circulation_alert_preferences` (
2351 `id` int(11) NOT NULL auto_increment,
2352 `branchcode` varchar(10) NOT NULL,
2353 `categorycode` varchar(10) NOT NULL,
2354 `item_type` varchar(10) NOT NULL,
2355 `notification` varchar(16) NOT NULL,
2357 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2358 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2360 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2361 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2362 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2363 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2364 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2365 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2366 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2367 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;