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 `aqbasketgroups`
94 DROP TABLE IF EXISTS `aqbasketgroups`;
95 CREATE TABLE `aqbasketgroups` (
96 `id` int(11) NOT NULL auto_increment,
97 `name` varchar(50) default NULL,
98 `closed` tinyint(1) default NULL,
99 `booksellerid` int(11) NOT NULL,
101 KEY `booksellerid` (`booksellerid`),
102 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
103 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
106 -- Table structure for table `aqbasket`
109 DROP TABLE IF EXISTS `aqbasket`;
110 CREATE TABLE `aqbasket` (
111 `basketno` int(11) NOT NULL auto_increment,
112 `basketname` varchar(50) default NULL,
114 `booksellernote` mediumtext,
115 `contractnumber` int(11),
116 `creationdate` date default NULL,
117 `closedate` date default NULL,
118 `booksellerid` int(11) NOT NULL default 1,
119 `authorisedby` varchar(10) default NULL,
120 `booksellerinvoicenumber` mediumtext,
121 `basketgroupid` int(11),
122 PRIMARY KEY (`basketno`),
123 KEY `booksellerid` (`booksellerid`),
124 KEY `basketgroupid` (`basketgroupid`),
125 KEY `contractnumber` (`contractnumber`),
126 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
127 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
128 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
132 -- Table structure for table `aqbooksellers`
135 DROP TABLE IF EXISTS `aqbooksellers`;
136 CREATE TABLE `aqbooksellers` (
137 `id` int(11) NOT NULL auto_increment,
138 `name` mediumtext NOT NULL,
139 `address1` mediumtext,
140 `address2` mediumtext,
141 `address3` mediumtext,
142 `address4` mediumtext,
143 `phone` varchar(30) default NULL,
144 `accountnumber` mediumtext,
145 `othersupplier` mediumtext,
146 `currency` varchar(3) NOT NULL default '',
147 `deliverydays` smallint(6) default NULL,
148 `followupdays` smallint(6) default NULL,
149 `followupscancel` smallint(6) default NULL,
150 `specialty` mediumtext,
151 `booksellerfax` mediumtext,
153 `bookselleremail` mediumtext,
154 `booksellerurl` mediumtext,
155 `contact` varchar(100) default NULL,
157 `url` varchar(255) default NULL,
158 `contpos` varchar(100) default NULL,
159 `contphone` varchar(100) default NULL,
160 `contfax` varchar(100) default NULL,
161 `contaltphone` varchar(100) default NULL,
162 `contemail` varchar(100) default NULL,
163 `contnotes` mediumtext,
164 `active` tinyint(4) default NULL,
165 `listprice` varchar(10) default NULL,
166 `invoiceprice` varchar(10) default NULL,
167 `gstreg` tinyint(4) default NULL,
168 `listincgst` tinyint(4) default NULL,
169 `invoiceincgst` tinyint(4) default NULL,
170 `gstrate` decimal(6,4) default NULL,
171 `discount` float(6,4) default NULL,
172 `fax` varchar(50) default NULL,
173 `nocalc` int(11) default NULL,
174 `invoicedisc` float(6,4) default NULL,
176 KEY `listprice` (`listprice`),
177 KEY `invoiceprice` (`invoiceprice`),
178 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
179 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
180 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
183 -- Table structure for table `aqbudgets`
186 CREATE TABLE `aqbudgets` (
187 `budget_id` int(11) NOT NULL auto_increment,
188 `budget_parent_id` int(11) default NULL,
189 `budget_code` varchar(30) default NULL,
190 `budget_name` varchar(80) default NULL,
191 `budget_branchcode` varchar(10) default NULL,
192 `budget_amount` decimal(28,6) NULL default '0.00',
193 `budget_amount_sublevel` decimal(28,6) NULL default '0.00',
194 `budget_encumb` decimal(28,6) NULL default '0.00',
195 `budget_expend` decimal(28,6) NULL default '0.00',
196 `budget_notes` mediumtext,
197 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
198 `budget_period_id` int(11) default NULL,
199 `sort1_authcat` varchar(80) default NULL,
200 `sort2_authcat` varchar(80) default NULL,
201 `budget_owner_id` int(11) default NULL,
202 `budget_permission` int(1) default '0',
203 PRIMARY KEY (`budget_id`)
204 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
208 -- Table structure for table `aqbudgetperiods`
212 DROP TABLE IF EXISTS `aqbudgetperiods`;
213 CREATE TABLE `aqbudgetperiods` (
214 `budget_period_id` int(11) NOT NULL auto_increment,
215 `budget_period_startdate` date NOT NULL,
216 `budget_period_enddate` date NOT NULL,
217 `budget_period_active` tinyint(1) default '0',
218 `budget_period_description` mediumtext,
219 `budget_period_total` decimal(28,6),
220 `budget_period_locked` tinyint(1) default NULL,
221 `sort1_authcat` varchar(10) default NULL,
222 `sort2_authcat` varchar(10) default NULL,
223 PRIMARY KEY (`budget_period_id`)
224 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
227 -- Table structure for table `aqbudgets_planning`
230 DROP TABLE IF EXISTS `aqbudgets_planning`;
231 CREATE TABLE `aqbudgets_planning` (
232 `plan_id` int(11) NOT NULL auto_increment,
233 `budget_id` int(11) NOT NULL,
234 `budget_period_id` int(11) NOT NULL,
235 `estimated_amount` decimal(28,6) default NULL,
236 `authcat` varchar(30) NOT NULL,
237 `authvalue` varchar(30) NOT NULL,
238 PRIMARY KEY (`plan_id`),
239 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
240 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
243 -- Table structure for table 'aqcontract'
246 DROP TABLE IF EXISTS `aqcontract`;
247 CREATE TABLE `aqcontract` (
248 `contractnumber` int(11) NOT NULL auto_increment,
249 `contractstartdate` date default NULL,
250 `contractenddate` date default NULL,
251 `contractname` varchar(50) default NULL,
252 `contractdescription` mediumtext,
253 `booksellerid` int(11) not NULL,
254 PRIMARY KEY (`contractnumber`),
255 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
256 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
257 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
260 -- Table structure for table `aqorderdelivery`
263 DROP TABLE IF EXISTS `aqorderdelivery`;
264 CREATE TABLE `aqorderdelivery` (
265 `ordernumber` date default NULL,
266 `deliverynumber` smallint(6) NOT NULL default 0,
267 `deliverydate` varchar(18) default NULL,
268 `qtydelivered` smallint(6) default NULL,
269 `deliverycomments` mediumtext
270 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
273 -- Table structure for table `aqorders`
276 DROP TABLE IF EXISTS `aqorders`;
277 CREATE TABLE `aqorders` (
278 `ordernumber` int(11) NOT NULL auto_increment,
279 `biblionumber` int(11) default NULL,
280 `entrydate` date default NULL,
281 `quantity` smallint(6) default NULL,
282 `currency` varchar(3) default NULL,
283 `listprice` decimal(28,6) default NULL,
284 `totalamount` decimal(28,6) default NULL,
285 `datereceived` date default NULL,
286 `booksellerinvoicenumber` mediumtext,
287 `freight` decimal(28,6) default NULL,
288 `unitprice` decimal(28,6) default NULL,
289 `quantityreceived` smallint(6) default NULL,
290 `cancelledby` varchar(10) default NULL,
291 `datecancellationprinted` date default NULL,
293 `supplierreference` mediumtext,
294 `purchaseordernumber` mediumtext,
295 `subscription` tinyint(1) default NULL,
296 `serialid` varchar(30) default NULL,
297 `basketno` int(11) default NULL,
298 `biblioitemnumber` int(11) default NULL,
299 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
300 `rrp` decimal(13,2) default NULL,
301 `ecost` decimal(13,2) default NULL,
302 `gst` decimal(13,2) default NULL,
303 `budget_id` int(11) NOT NULL,
304 `budgetgroup_id` int(11) NOT NULL,
305 `budgetdate` date default NULL,
306 `sort1` varchar(80) default NULL,
307 `sort2` varchar(80) default NULL,
308 `sort1_authcat` varchar(10) default NULL,
309 `sort2_authcat` varchar(10) default NULL,
310 `uncertainprice` tinyint(1),
311 PRIMARY KEY (`ordernumber`),
312 KEY `basketno` (`basketno`),
313 KEY `biblionumber` (`biblionumber`),
314 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
315 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
316 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
319 -- Table structure for table `aqorders_items`
322 DROP TABLE IF EXISTS `aqorders_items`;
323 CREATE TABLE `aqorders_items` (
324 `ordernumber` int(11) NOT NULL,
325 `itemnumber` int(11) NOT NULL,
326 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
327 PRIMARY KEY (`itemnumber`),
328 KEY `ordernumber` (`ordernumber`)
329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
332 -- Table structure for table `auth_header`
335 DROP TABLE IF EXISTS `auth_header`;
336 CREATE TABLE `auth_header` (
337 `authid` bigint(20) unsigned NOT NULL auto_increment,
338 `authtypecode` varchar(10) NOT NULL default '',
339 `datecreated` date default NULL,
340 `datemodified` date default NULL,
341 `origincode` varchar(20) default NULL,
342 `authtrees` mediumtext,
344 `linkid` bigint(20) default NULL,
345 `marcxml` longtext NOT NULL,
346 PRIMARY KEY (`authid`),
347 KEY `origincode` (`origincode`)
348 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
351 -- Table structure for table `auth_subfield_structure`
354 DROP TABLE IF EXISTS `auth_subfield_structure`;
355 CREATE TABLE `auth_subfield_structure` (
356 `authtypecode` varchar(10) NOT NULL default '',
357 `tagfield` varchar(3) NOT NULL default '',
358 `tagsubfield` varchar(1) NOT NULL default '',
359 `liblibrarian` varchar(255) NOT NULL default '',
360 `libopac` varchar(255) NOT NULL default '',
361 `repeatable` tinyint(4) NOT NULL default 0,
362 `mandatory` tinyint(4) NOT NULL default 0,
363 `tab` tinyint(1) default NULL,
364 `authorised_value` varchar(10) default NULL,
365 `value_builder` varchar(80) default NULL,
366 `seealso` varchar(255) default NULL,
367 `isurl` tinyint(1) default NULL,
368 `hidden` tinyint(3) NOT NULL default 0,
369 `linkid` tinyint(1) NOT NULL default 0,
370 `kohafield` varchar(45) NULL default '',
371 `frameworkcode` varchar(8) NOT NULL default '',
372 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
373 KEY `tab` (`authtypecode`,`tab`)
374 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
377 -- Table structure for table `auth_tag_structure`
380 DROP TABLE IF EXISTS `auth_tag_structure`;
381 CREATE TABLE `auth_tag_structure` (
382 `authtypecode` varchar(10) NOT NULL default '',
383 `tagfield` varchar(3) NOT NULL default '',
384 `liblibrarian` varchar(255) NOT NULL default '',
385 `libopac` varchar(255) NOT NULL default '',
386 `repeatable` tinyint(4) NOT NULL default 0,
387 `mandatory` tinyint(4) NOT NULL default 0,
388 `authorised_value` varchar(10) default NULL,
389 PRIMARY KEY (`authtypecode`,`tagfield`),
390 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
391 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
394 -- Table structure for table `auth_types`
397 DROP TABLE IF EXISTS `auth_types`;
398 CREATE TABLE `auth_types` (
399 `authtypecode` varchar(10) NOT NULL default '',
400 `authtypetext` varchar(255) NOT NULL default '',
401 `auth_tag_to_report` varchar(3) NOT NULL default '',
402 `summary` mediumtext NOT NULL,
403 PRIMARY KEY (`authtypecode`)
404 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
407 -- Table structure for table `authorised_values`
410 DROP TABLE IF EXISTS `authorised_values`;
411 CREATE TABLE `authorised_values` (
412 `id` int(11) NOT NULL auto_increment,
413 `category` varchar(10) NOT NULL default '',
414 `authorised_value` varchar(80) NOT NULL default '',
415 `lib` varchar(80) default NULL,
416 `lib_opac` varchar(80) default NULL,
417 `imageurl` varchar(200) default NULL,
419 KEY `name` (`category`),
421 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
424 -- Table structure for table `biblio`
427 DROP TABLE IF EXISTS `biblio`;
428 CREATE TABLE `biblio` (
429 `biblionumber` int(11) NOT NULL auto_increment,
430 `frameworkcode` varchar(4) NOT NULL default '',
433 `unititle` mediumtext,
435 `serial` tinyint(1) default NULL,
436 `seriestitle` mediumtext,
437 `copyrightdate` smallint(6) default NULL,
438 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
439 `datecreated` DATE NOT NULL,
440 `abstract` mediumtext,
441 PRIMARY KEY (`biblionumber`),
442 KEY `blbnoidx` (`biblionumber`)
443 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
446 -- Table structure for table `biblio_framework`
449 DROP TABLE IF EXISTS `biblio_framework`;
450 CREATE TABLE `biblio_framework` (
451 `frameworkcode` varchar(4) NOT NULL default '',
452 `frameworktext` varchar(255) NOT NULL default '',
453 PRIMARY KEY (`frameworkcode`)
454 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
457 -- Table structure for table `biblioitems`
460 DROP TABLE IF EXISTS `biblioitems`;
461 CREATE TABLE `biblioitems` (
462 `biblioitemnumber` int(11) NOT NULL auto_increment,
463 `biblionumber` int(11) NOT NULL default 0,
466 `itemtype` varchar(10) default NULL,
467 `isbn` varchar(30) default NULL,
468 `issn` varchar(9) default NULL,
469 `publicationyear` text,
470 `publishercode` varchar(255) default NULL,
471 `volumedate` date default NULL,
473 `collectiontitle` mediumtext default NULL,
474 `collectionissn` text default NULL,
475 `collectionvolume` mediumtext default NULL,
476 `editionstatement` text default NULL,
477 `editionresponsibility` text default NULL,
478 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
479 `illus` varchar(255) default NULL,
480 `pages` varchar(255) default NULL,
482 `size` varchar(255) default NULL,
483 `place` varchar(255) default NULL,
484 `lccn` varchar(25) default NULL,
486 `url` varchar(255) default NULL,
487 `cn_source` varchar(10) default NULL,
488 `cn_class` varchar(30) default NULL,
489 `cn_item` varchar(10) default NULL,
490 `cn_suffix` varchar(10) default NULL,
491 `cn_sort` varchar(30) default NULL,
492 `totalissues` int(10),
493 `marcxml` longtext NOT NULL,
494 PRIMARY KEY (`biblioitemnumber`),
495 KEY `bibinoidx` (`biblioitemnumber`),
496 KEY `bibnoidx` (`biblionumber`),
498 KEY `publishercode` (`publishercode`),
500 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
501 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
504 -- Table structure for table `borrowers`
507 DROP TABLE IF EXISTS `borrowers`;
508 CREATE TABLE `borrowers` (
509 `borrowernumber` int(11) NOT NULL auto_increment,
510 `cardnumber` varchar(16) default NULL,
511 `surname` mediumtext NOT NULL,
514 `othernames` mediumtext,
516 `streetnumber` varchar(10) default NULL,
517 `streettype` varchar(50) default NULL,
518 `address` mediumtext NOT NULL,
520 `city` mediumtext NOT NULL,
521 `zipcode` varchar(25) default NULL,
525 `mobile` varchar(50) default NULL,
529 `B_streetnumber` varchar(10) default NULL,
530 `B_streettype` varchar(50) default NULL,
531 `B_address` varchar(100) default NULL,
532 `B_address2` text default NULL,
534 `B_zipcode` varchar(25) default NULL,
537 `B_phone` mediumtext,
538 `dateofbirth` date default NULL,
539 `branchcode` varchar(10) NOT NULL default '',
540 `categorycode` varchar(10) NOT NULL default '',
541 `dateenrolled` date default NULL,
542 `dateexpiry` date default NULL,
543 `gonenoaddress` tinyint(1) default NULL,
544 `lost` tinyint(1) default NULL,
545 `debarred` tinyint(1) default NULL,
546 `contactname` mediumtext,
547 `contactfirstname` text,
549 `guarantorid` int(11) default NULL,
550 `borrowernotes` mediumtext,
551 `relationship` varchar(100) default NULL,
552 `ethnicity` varchar(50) default NULL,
553 `ethnotes` varchar(255) default NULL,
554 `sex` varchar(1) default NULL,
555 `password` varchar(30) default NULL,
556 `flags` int(11) default NULL,
557 `userid` varchar(30) default NULL,
558 `opacnote` mediumtext,
559 `contactnote` varchar(255) default NULL,
560 `sort1` varchar(80) default NULL,
561 `sort2` varchar(80) default NULL,
562 `altcontactfirstname` varchar(255) default NULL,
563 `altcontactsurname` varchar(255) default NULL,
564 `altcontactaddress1` varchar(255) default NULL,
565 `altcontactaddress2` varchar(255) default NULL,
566 `altcontactaddress3` varchar(255) default NULL,
567 `altcontactzipcode` varchar(50) default NULL,
568 `altcontactcountry` text default NULL,
569 `altcontactphone` varchar(50) default NULL,
570 `smsalertnumber` varchar(50) default NULL,
571 UNIQUE KEY `cardnumber` (`cardnumber`),
572 PRIMARY KEY `borrowernumber` (`borrowernumber`),
573 KEY `categorycode` (`categorycode`),
574 KEY `branchcode` (`branchcode`),
575 KEY `userid` (`userid`),
576 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
577 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
578 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
581 -- Table structure for table `borrower_attribute_types`
584 DROP TABLE IF EXISTS `borrower_attribute_types`;
585 CREATE TABLE `borrower_attribute_types` (
586 `code` varchar(10) NOT NULL,
587 `description` varchar(255) NOT NULL,
588 `repeatable` tinyint(1) NOT NULL default 0,
589 `unique_id` tinyint(1) NOT NULL default 0,
590 `opac_display` tinyint(1) NOT NULL default 0,
591 `password_allowed` tinyint(1) NOT NULL default 0,
592 `staff_searchable` tinyint(1) NOT NULL default 0,
593 `authorised_value_category` varchar(10) default NULL,
595 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
598 -- Table structure for table `borrower_attributes`
601 DROP TABLE IF EXISTS `borrower_attributes`;
602 CREATE TABLE `borrower_attributes` (
603 `borrowernumber` int(11) NOT NULL,
604 `code` varchar(10) NOT NULL,
605 `attribute` varchar(64) default NULL,
606 `password` varchar(64) default NULL,
607 KEY `borrowernumber` (`borrowernumber`),
608 KEY `code_attribute` (`code`, `attribute`),
609 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
610 ON DELETE CASCADE ON UPDATE CASCADE,
611 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
612 ON DELETE CASCADE ON UPDATE CASCADE
613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
615 CREATE TABLE `branch_item_rules` (
616 `branchcode` varchar(10) NOT NULL,
617 `itemtype` varchar(10) NOT NULL,
618 `holdallowed` tinyint(1) default NULL,
619 PRIMARY KEY (`itemtype`,`branchcode`),
620 KEY `branch_item_rules_ibfk_2` (`branchcode`),
621 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
622 ON DELETE CASCADE ON UPDATE CASCADE,
623 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
624 ON DELETE CASCADE ON UPDATE CASCADE
625 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
628 -- Table structure for table `branchcategories`
631 DROP TABLE IF EXISTS `branchcategories`;
632 CREATE TABLE `branchcategories` (
633 `categorycode` varchar(10) NOT NULL default '',
634 `categoryname` varchar(32),
635 `codedescription` mediumtext,
636 `categorytype` varchar(16),
637 PRIMARY KEY (`categorycode`)
638 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
641 -- Table structure for table `branches`
644 DROP TABLE IF EXISTS `branches`;
645 CREATE TABLE `branches` (
646 `branchcode` varchar(10) NOT NULL default '',
647 `branchname` mediumtext NOT NULL,
648 `branchaddress1` mediumtext,
649 `branchaddress2` mediumtext,
650 `branchaddress3` mediumtext,
651 `branchzip` varchar(25) default NULL,
652 `branchcity` mediumtext,
653 `branchcountry` text,
654 `branchphone` mediumtext,
655 `branchfax` mediumtext,
656 `branchemail` mediumtext,
657 `branchurl` mediumtext,
658 `issuing` tinyint(4) default NULL,
659 `branchip` varchar(15) default NULL,
660 `branchprinter` varchar(100) default NULL,
661 `branchnotes` mediumtext,
662 UNIQUE KEY `branchcode` (`branchcode`)
663 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
666 -- Table structure for table `branchrelations`
669 DROP TABLE IF EXISTS `branchrelations`;
670 CREATE TABLE `branchrelations` (
671 `branchcode` varchar(10) NOT NULL default '',
672 `categorycode` varchar(10) NOT NULL default '',
673 PRIMARY KEY (`branchcode`,`categorycode`),
674 KEY `branchcode` (`branchcode`),
675 KEY `categorycode` (`categorycode`),
676 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
677 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
678 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
681 -- Table structure for table `branchtransfers`
684 DROP TABLE IF EXISTS `branchtransfers`;
685 CREATE TABLE `branchtransfers` (
686 `itemnumber` int(11) NOT NULL default 0,
687 `datesent` datetime default NULL,
688 `frombranch` varchar(10) NOT NULL default '',
689 `datearrived` datetime default NULL,
690 `tobranch` varchar(10) NOT NULL default '',
691 `comments` mediumtext,
692 KEY `frombranch` (`frombranch`),
693 KEY `tobranch` (`tobranch`),
694 KEY `itemnumber` (`itemnumber`),
695 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
696 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
697 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
698 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
702 -- Table structure for table `browser`
704 DROP TABLE IF EXISTS `browser`;
705 CREATE TABLE `browser` (
706 `level` int(11) NOT NULL,
707 `classification` varchar(20) NOT NULL,
708 `description` varchar(255) NOT NULL,
709 `number` bigint(20) NOT NULL,
710 `endnode` tinyint(4) NOT NULL
711 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
714 -- Table structure for table `categories`
717 DROP TABLE IF EXISTS `categories`;
718 CREATE TABLE `categories` (
719 `categorycode` varchar(10) NOT NULL default '',
720 `description` mediumtext,
721 `enrolmentperiod` smallint(6) default NULL,
722 `upperagelimit` smallint(6) default NULL,
723 `dateofbirthrequired` tinyint(1) default NULL,
724 `finetype` varchar(30) default NULL,
725 `bulk` tinyint(1) default NULL,
726 `enrolmentfee` decimal(28,6) default NULL,
727 `overduenoticerequired` tinyint(1) default NULL,
728 `issuelimit` smallint(6) default NULL,
729 `reservefee` decimal(28,6) default NULL,
730 `category_type` varchar(1) NOT NULL default 'A',
731 PRIMARY KEY (`categorycode`),
732 UNIQUE KEY `categorycode` (`categorycode`)
733 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
736 -- Table structure for table `borrower_branch_circ_rules`
739 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
740 CREATE TABLE `branch_borrower_circ_rules` (
741 `branchcode` VARCHAR(10) NOT NULL,
742 `categorycode` VARCHAR(10) NOT NULL,
743 `maxissueqty` int(4) default NULL,
744 PRIMARY KEY (`categorycode`, `branchcode`),
745 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
746 ON DELETE CASCADE ON UPDATE CASCADE,
747 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
748 ON DELETE CASCADE ON UPDATE CASCADE
749 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
752 -- Table structure for table `default_borrower_circ_rules`
755 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
756 CREATE TABLE `default_borrower_circ_rules` (
757 `categorycode` VARCHAR(10) NOT NULL,
758 `maxissueqty` int(4) default NULL,
759 PRIMARY KEY (`categorycode`),
760 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
761 ON DELETE CASCADE ON UPDATE CASCADE
762 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
765 -- Table structure for table `default_branch_circ_rules`
768 DROP TABLE IF EXISTS `default_branch_circ_rules`;
769 CREATE TABLE `default_branch_circ_rules` (
770 `branchcode` VARCHAR(10) NOT NULL,
771 `maxissueqty` int(4) default NULL,
772 `holdallowed` tinyint(1) default NULL,
773 PRIMARY KEY (`branchcode`),
774 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
775 ON DELETE CASCADE ON UPDATE CASCADE
776 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
779 -- Table structure for table `default_branch_item_rules`
782 CREATE TABLE `default_branch_item_rules` (
783 `itemtype` varchar(10) NOT NULL,
784 `holdallowed` tinyint(1) default NULL,
785 PRIMARY KEY (`itemtype`),
786 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
787 ON DELETE CASCADE ON UPDATE CASCADE
788 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
791 -- Table structure for table `default_circ_rules`
794 DROP TABLE IF EXISTS `default_circ_rules`;
795 CREATE TABLE `default_circ_rules` (
796 `singleton` enum('singleton') NOT NULL default 'singleton',
797 `maxissueqty` int(4) default NULL,
798 `holdallowed` int(1) default NULL,
799 PRIMARY KEY (`singleton`)
800 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
803 -- Table structure for table `cities`
806 DROP TABLE IF EXISTS `cities`;
807 CREATE TABLE `cities` (
808 `cityid` int(11) NOT NULL auto_increment,
809 `city_name` varchar(100) NOT NULL default '',
810 `city_zipcode` varchar(20) default NULL,
811 PRIMARY KEY (`cityid`)
812 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
815 -- Table structure for table `class_sort_rules`
818 DROP TABLE IF EXISTS `class_sort_rules`;
819 CREATE TABLE `class_sort_rules` (
820 `class_sort_rule` varchar(10) NOT NULL default '',
821 `description` mediumtext,
822 `sort_routine` varchar(30) NOT NULL default '',
823 PRIMARY KEY (`class_sort_rule`),
824 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
825 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
828 -- Table structure for table `class_sources`
831 DROP TABLE IF EXISTS `class_sources`;
832 CREATE TABLE `class_sources` (
833 `cn_source` varchar(10) NOT NULL default '',
834 `description` mediumtext,
835 `used` tinyint(4) NOT NULL default 0,
836 `class_sort_rule` varchar(10) NOT NULL default '',
837 PRIMARY KEY (`cn_source`),
838 UNIQUE KEY `cn_source_idx` (`cn_source`),
839 KEY `used_idx` (`used`),
840 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
841 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
844 -- Table structure for table `currency`
847 DROP TABLE IF EXISTS `currency`;
848 CREATE TABLE `currency` (
849 `currency` varchar(10) NOT NULL default '',
850 `symbol` varchar(5) default NULL,
851 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
852 `rate` float(7,5) default NULL,
853 `active` tinyint(1) default NULL,
854 PRIMARY KEY (`currency`)
855 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
858 -- Table structure for table `deletedbiblio`
861 DROP TABLE IF EXISTS `deletedbiblio`;
862 CREATE TABLE `deletedbiblio` (
863 `biblionumber` int(11) NOT NULL default 0,
864 `frameworkcode` varchar(4) NOT NULL default '',
867 `unititle` mediumtext,
869 `serial` tinyint(1) default NULL,
870 `seriestitle` mediumtext,
871 `copyrightdate` smallint(6) default NULL,
872 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
873 `datecreated` DATE NOT NULL,
874 `abstract` mediumtext,
875 PRIMARY KEY (`biblionumber`),
876 KEY `blbnoidx` (`biblionumber`)
877 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
880 -- Table structure for table `deletedbiblioitems`
883 DROP TABLE IF EXISTS `deletedbiblioitems`;
884 CREATE TABLE `deletedbiblioitems` (
885 `biblioitemnumber` int(11) NOT NULL default 0,
886 `biblionumber` int(11) NOT NULL default 0,
889 `itemtype` varchar(10) default NULL,
890 `isbn` varchar(30) default NULL,
891 `issn` varchar(9) default NULL,
892 `publicationyear` text,
893 `publishercode` varchar(255) default NULL,
894 `volumedate` date default NULL,
896 `collectiontitle` mediumtext default NULL,
897 `collectionissn` text default NULL,
898 `collectionvolume` mediumtext default NULL,
899 `editionstatement` text default NULL,
900 `editionresponsibility` text default NULL,
901 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
902 `illus` varchar(255) default NULL,
903 `pages` varchar(255) default NULL,
905 `size` varchar(255) default NULL,
906 `place` varchar(255) default NULL,
907 `lccn` varchar(25) default NULL,
909 `url` varchar(255) default NULL,
910 `cn_source` varchar(10) default NULL,
911 `cn_class` varchar(30) default NULL,
912 `cn_item` varchar(10) default NULL,
913 `cn_suffix` varchar(10) default NULL,
914 `cn_sort` varchar(30) default NULL,
915 `totalissues` int(10),
916 `marcxml` longtext NOT NULL,
917 PRIMARY KEY (`biblioitemnumber`),
918 KEY `bibinoidx` (`biblioitemnumber`),
919 KEY `bibnoidx` (`biblionumber`),
921 KEY `publishercode` (`publishercode`)
922 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
925 -- Table structure for table `deletedborrowers`
928 DROP TABLE IF EXISTS `deletedborrowers`;
929 CREATE TABLE `deletedborrowers` (
930 `borrowernumber` int(11) NOT NULL default 0,
931 `cardnumber` varchar(9) NOT NULL default '',
932 `surname` mediumtext NOT NULL,
935 `othernames` mediumtext,
937 `streetnumber` varchar(10) default NULL,
938 `streettype` varchar(50) default NULL,
939 `address` mediumtext NOT NULL,
941 `city` mediumtext NOT NULL,
942 `zipcode` varchar(25) default NULL,
946 `mobile` varchar(50) default NULL,
950 `B_streetnumber` varchar(10) default NULL,
951 `B_streettype` varchar(50) default NULL,
952 `B_address` varchar(100) default NULL,
953 `B_address2` text default NULL,
955 `B_zipcode` varchar(25) default NULL,
958 `B_phone` mediumtext,
959 `dateofbirth` date default NULL,
960 `branchcode` varchar(10) NOT NULL default '',
961 `categorycode` varchar(10) default NULL,
962 `dateenrolled` date default NULL,
963 `dateexpiry` date default NULL,
964 `gonenoaddress` tinyint(1) default NULL,
965 `lost` tinyint(1) default NULL,
966 `debarred` tinyint(1) default NULL,
967 `contactname` mediumtext,
968 `contactfirstname` text,
970 `guarantorid` int(11) default NULL,
971 `borrowernotes` mediumtext,
972 `relationship` varchar(100) default NULL,
973 `ethnicity` varchar(50) default NULL,
974 `ethnotes` varchar(255) default NULL,
975 `sex` varchar(1) default NULL,
976 `password` varchar(30) default NULL,
977 `flags` int(11) default NULL,
978 `userid` varchar(30) default NULL,
979 `opacnote` mediumtext,
980 `contactnote` varchar(255) default NULL,
981 `sort1` varchar(80) default NULL,
982 `sort2` varchar(80) default NULL,
983 `altcontactfirstname` varchar(255) default NULL,
984 `altcontactsurname` varchar(255) default NULL,
985 `altcontactaddress1` varchar(255) default NULL,
986 `altcontactaddress2` varchar(255) default NULL,
987 `altcontactaddress3` varchar(255) default NULL,
988 `altcontactzipcode` varchar(50) default NULL,
989 `altcontactcountry` text default NULL,
990 `altcontactphone` varchar(50) default NULL,
991 `smsalertnumber` varchar(50) default NULL,
992 KEY `borrowernumber` (`borrowernumber`),
993 KEY `cardnumber` (`cardnumber`)
994 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
997 -- Table structure for table `deleteditems`
1000 DROP TABLE IF EXISTS `deleteditems`;
1001 CREATE TABLE `deleteditems` (
1002 `itemnumber` int(11) NOT NULL default 0,
1003 `biblionumber` int(11) NOT NULL default 0,
1004 `biblioitemnumber` int(11) NOT NULL default 0,
1005 `barcode` varchar(20) default NULL,
1006 `dateaccessioned` date default NULL,
1007 `booksellerid` mediumtext default NULL,
1008 `homebranch` varchar(10) default NULL,
1009 `price` decimal(8,2) default NULL,
1010 `replacementprice` decimal(8,2) default NULL,
1011 `replacementpricedate` date default NULL,
1012 `datelastborrowed` date default NULL,
1013 `datelastseen` date default NULL,
1014 `stack` tinyint(1) default NULL,
1015 `notforloan` tinyint(1) NOT NULL default 0,
1016 `damaged` tinyint(1) NOT NULL default 0,
1017 `itemlost` tinyint(1) NOT NULL default 0,
1018 `wthdrawn` tinyint(1) NOT NULL default 0,
1019 `itemcallnumber` varchar(255) default NULL,
1020 `issues` smallint(6) default NULL,
1021 `renewals` smallint(6) default NULL,
1022 `reserves` smallint(6) default NULL,
1023 `restricted` tinyint(1) default NULL,
1024 `itemnotes` mediumtext,
1025 `holdingbranch` varchar(10) default NULL,
1026 `paidfor` mediumtext,
1027 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1028 `location` varchar(80) default NULL,
1029 `permanent_location` varchar(80) default NULL,
1030 `onloan` date default NULL,
1031 `cn_source` varchar(10) default NULL,
1032 `cn_sort` varchar(30) default NULL,
1033 `ccode` varchar(10) default NULL,
1034 `materials` varchar(10) default NULL,
1035 `uri` varchar(255) default NULL,
1036 `itype` varchar(10) default NULL,
1037 `more_subfields_xml` longtext default NULL,
1038 `enumchron` varchar(80) default NULL,
1039 `copynumber` varchar(32) default NULL,
1041 PRIMARY KEY (`itemnumber`),
1042 KEY `delitembarcodeidx` (`barcode`),
1043 KEY `delitembinoidx` (`biblioitemnumber`),
1044 KEY `delitembibnoidx` (`biblionumber`),
1045 KEY `delhomebranch` (`homebranch`),
1046 KEY `delholdingbranch` (`holdingbranch`)
1047 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1050 -- Table structure for table `ethnicity`
1053 DROP TABLE IF EXISTS `ethnicity`;
1054 CREATE TABLE `ethnicity` (
1055 `code` varchar(10) NOT NULL default '',
1056 `name` varchar(255) default NULL,
1057 PRIMARY KEY (`code`)
1058 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1061 -- Table structure for table `hold_fill_targets`
1064 DROP TABLE IF EXISTS `hold_fill_targets`;
1065 CREATE TABLE hold_fill_targets (
1066 `borrowernumber` int(11) NOT NULL,
1067 `biblionumber` int(11) NOT NULL,
1068 `itemnumber` int(11) NOT NULL,
1069 `source_branchcode` varchar(10) default NULL,
1070 `item_level_request` tinyint(4) NOT NULL default 0,
1071 PRIMARY KEY `itemnumber` (`itemnumber`),
1072 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
1073 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
1074 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1075 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
1076 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1077 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
1078 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1079 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
1080 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1081 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1084 -- Table structure for table `import_batches`
1087 DROP TABLE IF EXISTS `import_batches`;
1088 CREATE TABLE `import_batches` (
1089 `import_batch_id` int(11) NOT NULL auto_increment,
1090 `matcher_id` int(11) default NULL,
1091 `template_id` int(11) default NULL,
1092 `branchcode` varchar(10) default NULL,
1093 `num_biblios` int(11) NOT NULL default 0,
1094 `num_items` int(11) NOT NULL default 0,
1095 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1096 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1097 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1098 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1099 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1100 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1101 `file_name` varchar(100),
1102 `comments` mediumtext,
1103 PRIMARY KEY (`import_batch_id`),
1104 KEY `branchcode` (`branchcode`)
1105 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1108 -- Table structure for table `import_records`
1111 DROP TABLE IF EXISTS `import_records`;
1112 CREATE TABLE `import_records` (
1113 `import_record_id` int(11) NOT NULL auto_increment,
1114 `import_batch_id` int(11) NOT NULL,
1115 `branchcode` varchar(10) default NULL,
1116 `record_sequence` int(11) NOT NULL default 0,
1117 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1118 `import_date` DATE default NULL,
1119 `marc` longblob NOT NULL,
1120 `marcxml` longtext NOT NULL,
1121 `marcxml_old` longtext NOT NULL,
1122 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1123 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1124 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1125 `import_error` mediumtext,
1126 `encoding` varchar(40) NOT NULL default '',
1127 `z3950random` varchar(40) default NULL,
1128 PRIMARY KEY (`import_record_id`),
1129 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1130 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1131 KEY `branchcode` (`branchcode`),
1132 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1133 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1136 -- Table structure for `import_record_matches`
1138 DROP TABLE IF EXISTS `import_record_matches`;
1139 CREATE TABLE `import_record_matches` (
1140 `import_record_id` int(11) NOT NULL,
1141 `candidate_match_id` int(11) NOT NULL,
1142 `score` int(11) NOT NULL default 0,
1143 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1144 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1145 KEY `record_score` (`import_record_id`, `score`)
1146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1149 -- Table structure for table `import_biblios`
1152 DROP TABLE IF EXISTS `import_biblios`;
1153 CREATE TABLE `import_biblios` (
1154 `import_record_id` int(11) NOT NULL,
1155 `matched_biblionumber` int(11) default NULL,
1156 `control_number` varchar(25) default NULL,
1157 `original_source` varchar(25) default NULL,
1158 `title` varchar(128) default NULL,
1159 `author` varchar(80) default NULL,
1160 `isbn` varchar(30) default NULL,
1161 `issn` varchar(9) default NULL,
1162 `has_items` tinyint(1) NOT NULL default 0,
1163 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1164 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1165 KEY `matched_biblionumber` (`matched_biblionumber`),
1166 KEY `title` (`title`),
1168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1171 -- Table structure for table `import_items`
1174 DROP TABLE IF EXISTS `import_items`;
1175 CREATE TABLE `import_items` (
1176 `import_items_id` int(11) NOT NULL auto_increment,
1177 `import_record_id` int(11) NOT NULL,
1178 `itemnumber` int(11) default NULL,
1179 `branchcode` varchar(10) default NULL,
1180 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1181 `marcxml` longtext NOT NULL,
1182 `import_error` mediumtext,
1183 PRIMARY KEY (`import_items_id`),
1184 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1185 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1186 KEY `itemnumber` (`itemnumber`),
1187 KEY `branchcode` (`branchcode`)
1188 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1191 -- Table structure for table `issues`
1194 DROP TABLE IF EXISTS `issues`;
1195 CREATE TABLE `issues` (
1196 `borrowernumber` int(11) default NULL,
1197 `itemnumber` int(11) default NULL,
1198 `date_due` date default NULL,
1199 `branchcode` varchar(10) default NULL,
1200 `issuingbranch` varchar(18) default NULL,
1201 `returndate` date default NULL,
1202 `lastreneweddate` date default NULL,
1203 `return` varchar(4) default NULL,
1204 `renewals` tinyint(4) default NULL,
1205 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1206 `issuedate` date default NULL,
1207 KEY `issuesborridx` (`borrowernumber`),
1208 KEY `issuesitemidx` (`itemnumber`),
1209 KEY `bordate` (`borrowernumber`,`timestamp`),
1210 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1211 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1215 -- Table structure for table `issuingrules`
1218 DROP TABLE IF EXISTS `issuingrules`;
1219 CREATE TABLE `issuingrules` (
1220 `categorycode` varchar(10) NOT NULL default '',
1221 `itemtype` varchar(10) NOT NULL default '',
1222 `restrictedtype` tinyint(1) default NULL,
1223 `rentaldiscount` decimal(28,6) default NULL,
1224 `reservecharge` decimal(28,6) default NULL,
1225 `fine` decimal(28,6) default NULL,
1226 `firstremind` int(11) default NULL,
1227 `chargeperiod` int(11) default NULL,
1228 `accountsent` int(11) default NULL,
1229 `chargename` varchar(100) default NULL,
1230 `maxissueqty` int(4) default NULL,
1231 `issuelength` int(4) default NULL,
1232 `branchcode` varchar(10) NOT NULL default '',
1233 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1234 KEY `categorycode` (`categorycode`),
1235 KEY `itemtype` (`itemtype`)
1236 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1239 -- Table structure for table `items`
1242 DROP TABLE IF EXISTS `items`;
1243 CREATE TABLE `items` (
1244 `itemnumber` int(11) NOT NULL auto_increment,
1245 `biblionumber` int(11) NOT NULL default 0,
1246 `biblioitemnumber` int(11) NOT NULL default 0,
1247 `barcode` varchar(20) default NULL,
1248 `dateaccessioned` date default NULL,
1249 `booksellerid` mediumtext default NULL,
1250 `homebranch` varchar(10) default NULL,
1251 `price` decimal(8,2) default NULL,
1252 `replacementprice` decimal(8,2) default NULL,
1253 `replacementpricedate` date default NULL,
1254 `datelastborrowed` date default NULL,
1255 `datelastseen` date default NULL,
1256 `stack` tinyint(1) default NULL,
1257 `notforloan` tinyint(1) NOT NULL default 0,
1258 `damaged` tinyint(1) NOT NULL default 0,
1259 `itemlost` tinyint(1) NOT NULL default 0,
1260 `wthdrawn` tinyint(1) NOT NULL default 0,
1261 `itemcallnumber` varchar(255) default NULL,
1262 `issues` smallint(6) default NULL,
1263 `renewals` smallint(6) default NULL,
1264 `reserves` smallint(6) default NULL,
1265 `restricted` tinyint(1) default NULL,
1266 `itemnotes` mediumtext,
1267 `holdingbranch` varchar(10) default NULL,
1268 `paidfor` mediumtext,
1269 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1270 `location` varchar(80) default NULL,
1271 `permanent_location` varchar(80) default NULL,
1272 `onloan` date default NULL,
1273 `cn_source` varchar(10) default NULL,
1274 `cn_sort` varchar(30) default NULL,
1275 `ccode` varchar(10) default NULL,
1276 `materials` varchar(10) default NULL,
1277 `uri` varchar(255) default NULL,
1278 `itype` varchar(10) default NULL,
1279 `more_subfields_xml` longtext default NULL,
1280 `enumchron` varchar(80) default NULL,
1281 `copynumber` varchar(32) default NULL,
1282 PRIMARY KEY (`itemnumber`),
1283 UNIQUE KEY `itembarcodeidx` (`barcode`),
1284 KEY `itembinoidx` (`biblioitemnumber`),
1285 KEY `itembibnoidx` (`biblionumber`),
1286 KEY `homebranch` (`homebranch`),
1287 KEY `holdingbranch` (`holdingbranch`),
1288 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1289 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1290 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1291 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1294 -- Table structure for table `itemtypes`
1297 DROP TABLE IF EXISTS `itemtypes`;
1298 CREATE TABLE `itemtypes` (
1299 `itemtype` varchar(10) NOT NULL default '',
1300 `description` mediumtext,
1301 `renewalsallowed` smallint(6) default NULL,
1302 `rentalcharge` double(16,4) default NULL,
1303 `notforloan` smallint(6) default NULL,
1304 `imageurl` varchar(200) default NULL,
1306 PRIMARY KEY (`itemtype`),
1307 UNIQUE KEY `itemtype` (`itemtype`)
1308 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1311 -- Table structure for table `labels_batches`
1314 DROP TABLE IF EXISTS `labels_batches`;
1315 CREATE TABLE `labels_batches` (
1316 `label_id` int(11) NOT NULL auto_increment,
1317 `batch_id` int(10) NOT NULL default '1',
1318 `item_number` int(11) NOT NULL default '0',
1319 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1320 `branch_code` varchar(10) NOT NULL default 'NB',
1321 PRIMARY KEY USING BTREE (`label_id`),
1322 KEY `branch_fk` (`branch_code`),
1323 KEY `item_fk` (`item_number`),
1324 CONSTRAINT `item_fk_constraint` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE,
1325 CONSTRAINT `branch_fk_constraint` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE
1326 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1329 -- Table structure for table `labels_layouts`
1332 DROP TABLE IF EXISTS `labels_layouts`;
1333 CREATE TABLE `labels_layouts` (
1334 `layout_id` int(4) NOT NULL auto_increment,
1335 `barcode_type` char(100) NOT NULL default 'CODE39',
1336 `printing_type` char(32) NOT NULL default 'BAR',
1337 `layout_name` char(20) NOT NULL default 'DEFAULT',
1338 `guidebox` int(1) default '0',
1339 `font` char(10) character set utf8 collate utf8_unicode_ci NOT NULL default 'TR',
1340 `font_size` int(4) NOT NULL default '10',
1341 `callnum_split` int(1) default '0',
1342 `text_justify` char(1) character set utf8 collate utf8_unicode_ci NOT NULL default 'L',
1343 `format_string` varchar(210) NOT NULL default 'barcode',
1344 PRIMARY KEY USING BTREE (`layout_id`)
1345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1348 -- Table structure for table `labels_templates`
1351 DROP TABLE IF EXISTS `labels_templates`;
1352 CREATE TABLE `labels_templates` (
1353 `template_id` int(4) NOT NULL auto_increment,
1354 `profile_id` int(4) default NULL,
1355 `template_code` char(100) NOT NULL default 'DEFAULT TEMPLATE',
1356 `template_desc` char(100) NOT NULL default 'Default description',
1357 `page_width` float NOT NULL default '0',
1358 `page_height` float NOT NULL default '0',
1359 `label_width` float NOT NULL default '0',
1360 `label_height` float NOT NULL default '0',
1361 `top_text_margin` float NOT NULL default '0',
1362 `left_text_margin` float NOT NULL default '0',
1363 `top_margin` float NOT NULL default '0',
1364 `left_margin` float NOT NULL default '0',
1365 `cols` int(2) NOT NULL default '0',
1366 `rows` int(2) NOT NULL default '0',
1367 `col_gap` float NOT NULL default '0',
1368 `row_gap` float NOT NULL default '0',
1369 `units` char(20) NOT NULL default 'POINT',
1370 PRIMARY KEY (`template_id`),
1371 KEY `template_profile_fk_constraint` (`profile_id`)
1372 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1375 -- Table structure for table `letter`
1378 DROP TABLE IF EXISTS `letter`;
1379 CREATE TABLE `letter` (
1380 `module` varchar(20) NOT NULL default '',
1381 `code` varchar(20) NOT NULL default '',
1382 `name` varchar(100) NOT NULL default '',
1383 `title` varchar(200) NOT NULL default '',
1385 PRIMARY KEY (`module`,`code`)
1386 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1389 -- Table structure for table `marc_subfield_structure`
1392 DROP TABLE IF EXISTS `marc_subfield_structure`;
1393 CREATE TABLE `marc_subfield_structure` (
1394 `tagfield` varchar(3) NOT NULL default '',
1395 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1396 `liblibrarian` varchar(255) NOT NULL default '',
1397 `libopac` varchar(255) NOT NULL default '',
1398 `repeatable` tinyint(4) NOT NULL default 0,
1399 `mandatory` tinyint(4) NOT NULL default 0,
1400 `kohafield` varchar(40) default NULL,
1401 `tab` tinyint(1) default NULL,
1402 `authorised_value` varchar(20) default NULL,
1403 `authtypecode` varchar(20) default NULL,
1404 `value_builder` varchar(80) default NULL,
1405 `isurl` tinyint(1) default NULL,
1406 `hidden` tinyint(1) default NULL,
1407 `frameworkcode` varchar(4) NOT NULL default '',
1408 `seealso` varchar(1100) default NULL,
1409 `link` varchar(80) default NULL,
1410 `defaultvalue` text default NULL,
1411 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1412 KEY `kohafield_2` (`kohafield`),
1413 KEY `tab` (`frameworkcode`,`tab`),
1414 KEY `kohafield` (`frameworkcode`,`kohafield`)
1415 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1418 -- Table structure for table `marc_tag_structure`
1421 DROP TABLE IF EXISTS `marc_tag_structure`;
1422 CREATE TABLE `marc_tag_structure` (
1423 `tagfield` varchar(3) NOT NULL default '',
1424 `liblibrarian` varchar(255) NOT NULL default '',
1425 `libopac` varchar(255) NOT NULL default '',
1426 `repeatable` tinyint(4) NOT NULL default 0,
1427 `mandatory` tinyint(4) NOT NULL default 0,
1428 `authorised_value` varchar(10) default NULL,
1429 `frameworkcode` varchar(4) NOT NULL default '',
1430 PRIMARY KEY (`frameworkcode`,`tagfield`)
1431 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1434 -- Table structure for table `marc_matchers`
1437 DROP TABLE IF EXISTS `marc_matchers`;
1438 CREATE TABLE `marc_matchers` (
1439 `matcher_id` int(11) NOT NULL auto_increment,
1440 `code` varchar(10) NOT NULL default '',
1441 `description` varchar(255) NOT NULL default '',
1442 `record_type` varchar(10) NOT NULL default 'biblio',
1443 `threshold` int(11) NOT NULL default 0,
1444 PRIMARY KEY (`matcher_id`),
1445 KEY `code` (`code`),
1446 KEY `record_type` (`record_type`)
1447 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1450 -- Table structure for table `matchpoints`
1452 DROP TABLE IF EXISTS `matchpoints`;
1453 CREATE TABLE `matchpoints` (
1454 `matcher_id` int(11) NOT NULL,
1455 `matchpoint_id` int(11) NOT NULL auto_increment,
1456 `search_index` varchar(30) NOT NULL default '',
1457 `score` int(11) NOT NULL default 0,
1458 PRIMARY KEY (`matchpoint_id`),
1459 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1460 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1461 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1465 -- Table structure for table `matchpoint_components`
1467 DROP TABLE IF EXISTS `matchpoint_components`;
1468 CREATE TABLE `matchpoint_components` (
1469 `matchpoint_id` int(11) NOT NULL,
1470 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1471 sequence int(11) NOT NULL default 0,
1472 tag varchar(3) NOT NULL default '',
1473 subfields varchar(40) NOT NULL default '',
1474 offset int(4) NOT NULL default 0,
1475 length int(4) NOT NULL default 0,
1476 PRIMARY KEY (`matchpoint_component_id`),
1477 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1478 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1479 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1480 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1483 -- Table structure for table `matcher_component_norms`
1485 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1486 CREATE TABLE `matchpoint_component_norms` (
1487 `matchpoint_component_id` int(11) NOT NULL,
1488 `sequence` int(11) NOT NULL default 0,
1489 `norm_routine` varchar(50) NOT NULL default '',
1490 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1491 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1492 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1493 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1496 -- Table structure for table `matcher_matchpoints`
1498 DROP TABLE IF EXISTS `matcher_matchpoints`;
1499 CREATE TABLE `matcher_matchpoints` (
1500 `matcher_id` int(11) NOT NULL,
1501 `matchpoint_id` int(11) NOT NULL,
1502 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1503 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1504 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1505 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1506 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1509 -- Table structure for table `matchchecks`
1511 DROP TABLE IF EXISTS `matchchecks`;
1512 CREATE TABLE `matchchecks` (
1513 `matcher_id` int(11) NOT NULL,
1514 `matchcheck_id` int(11) NOT NULL auto_increment,
1515 `source_matchpoint_id` int(11) NOT NULL,
1516 `target_matchpoint_id` int(11) NOT NULL,
1517 PRIMARY KEY (`matchcheck_id`),
1518 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1519 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1520 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1521 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1522 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1523 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1524 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1527 -- Table structure for table `notifys`
1530 DROP TABLE IF EXISTS `notifys`;
1531 CREATE TABLE `notifys` (
1532 `notify_id` int(11) NOT NULL default 0,
1533 `borrowernumber` int(11) NOT NULL default 0,
1534 `itemnumber` int(11) NOT NULL default 0,
1535 `notify_date` date default NULL,
1536 `notify_send_date` date default NULL,
1537 `notify_level` int(1) NOT NULL default 0,
1538 `method` varchar(20) NOT NULL default ''
1539 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1542 -- Table structure for table `nozebra`
1545 DROP TABLE IF EXISTS `nozebra`;
1546 CREATE TABLE `nozebra` (
1547 `server` varchar(20) NOT NULL,
1548 `indexname` varchar(40) NOT NULL,
1549 `value` varchar(250) NOT NULL,
1550 `biblionumbers` longtext NOT NULL,
1551 KEY `indexname` (`server`,`indexname`),
1552 KEY `value` (`server`,`value`))
1553 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1556 -- Table structure for table `old_issues`
1559 DROP TABLE IF EXISTS `old_issues`;
1560 CREATE TABLE `old_issues` (
1561 `borrowernumber` int(11) default NULL,
1562 `itemnumber` int(11) default NULL,
1563 `date_due` date default NULL,
1564 `branchcode` varchar(10) default NULL,
1565 `issuingbranch` varchar(18) default NULL,
1566 `returndate` date default NULL,
1567 `lastreneweddate` date default NULL,
1568 `return` varchar(4) default NULL,
1569 `renewals` tinyint(4) default NULL,
1570 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1571 `issuedate` date default NULL,
1572 KEY `old_issuesborridx` (`borrowernumber`),
1573 KEY `old_issuesitemidx` (`itemnumber`),
1574 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1575 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1576 ON DELETE SET NULL ON UPDATE SET NULL,
1577 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1578 ON DELETE SET NULL ON UPDATE SET NULL
1579 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1582 -- Table structure for table `old_reserves`
1584 DROP TABLE IF EXISTS `old_reserves`;
1585 CREATE TABLE `old_reserves` (
1586 `borrowernumber` int(11) default NULL,
1587 `reservedate` date default NULL,
1588 `biblionumber` int(11) default NULL,
1589 `constrainttype` varchar(1) default NULL,
1590 `branchcode` varchar(10) default NULL,
1591 `notificationdate` date default NULL,
1592 `reminderdate` date default NULL,
1593 `cancellationdate` date default NULL,
1594 `reservenotes` mediumtext,
1595 `priority` smallint(6) default NULL,
1596 `found` varchar(1) default NULL,
1597 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1598 `itemnumber` int(11) default NULL,
1599 `waitingdate` date default NULL,
1600 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1601 KEY `old_reserves_biblionumber` (`biblionumber`),
1602 KEY `old_reserves_itemnumber` (`itemnumber`),
1603 KEY `old_reserves_branchcode` (`branchcode`),
1604 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1605 ON DELETE SET NULL ON UPDATE SET NULL,
1606 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1607 ON DELETE SET NULL ON UPDATE SET NULL,
1608 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1609 ON DELETE SET NULL ON UPDATE SET NULL
1610 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1613 -- Table structure for table `opac_news`
1616 DROP TABLE IF EXISTS `opac_news`;
1617 CREATE TABLE `opac_news` (
1618 `idnew` int(10) unsigned NOT NULL auto_increment,
1619 `title` varchar(250) NOT NULL default '',
1620 `new` text NOT NULL,
1621 `lang` varchar(25) NOT NULL default '',
1622 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1623 `expirationdate` date default NULL,
1624 `number` int(11) default NULL,
1625 PRIMARY KEY (`idnew`)
1626 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1629 -- Table structure for table `overduerules`
1632 DROP TABLE IF EXISTS `overduerules`;
1633 CREATE TABLE `overduerules` (
1634 `branchcode` varchar(10) NOT NULL default '',
1635 `categorycode` varchar(10) NOT NULL default '',
1636 `delay1` int(4) default 0,
1637 `letter1` varchar(20) default NULL,
1638 `debarred1` varchar(1) default 0,
1639 `delay2` int(4) default 0,
1640 `debarred2` varchar(1) default 0,
1641 `letter2` varchar(20) default NULL,
1642 `delay3` int(4) default 0,
1643 `letter3` varchar(20) default NULL,
1644 `debarred3` int(1) default 0,
1645 PRIMARY KEY (`branchcode`,`categorycode`)
1646 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1649 -- Table structure for table `patroncards`
1652 DROP TABLE IF EXISTS `patroncards`;
1653 CREATE TABLE `patroncards` (
1654 `cardid` int(11) NOT NULL auto_increment,
1655 `batch_id` varchar(10) NOT NULL default '1',
1656 `borrowernumber` int(11) NOT NULL,
1657 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1658 PRIMARY KEY (`cardid`),
1659 KEY `patroncards_ibfk_1` (`borrowernumber`),
1660 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1661 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1664 -- Table structure for table `patronimage`
1667 DROP TABLE IF EXISTS `patronimage`;
1668 CREATE TABLE `patronimage` (
1669 `cardnumber` varchar(16) NOT NULL,
1670 `mimetype` varchar(15) NOT NULL,
1671 `imagefile` mediumblob NOT NULL,
1672 PRIMARY KEY (`cardnumber`),
1673 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1674 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1677 -- Table structure for table `printers`
1680 DROP TABLE IF EXISTS `printers`;
1681 CREATE TABLE `printers` (
1682 `printername` varchar(40) NOT NULL default '',
1683 `printqueue` varchar(20) default NULL,
1684 `printtype` varchar(20) default NULL,
1685 PRIMARY KEY (`printername`)
1686 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1689 -- Table structure for table `printers_profile`
1692 DROP TABLE IF EXISTS `printers_profile`;
1693 CREATE TABLE `printers_profile` (
1694 `profile_id` int(4) NOT NULL auto_increment,
1695 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1696 `template_id` int(4) NOT NULL default '0',
1697 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1698 `offset_horz` float NOT NULL default '0',
1699 `offset_vert` float NOT NULL default '0',
1700 `creep_horz` float NOT NULL default '0',
1701 `creep_vert` float NOT NULL default '0',
1702 `units` char(20) NOT NULL default 'POINT',
1703 PRIMARY KEY (`profile_id`),
1704 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`)
1705 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1708 -- Table structure for table `repeatable_holidays`
1711 DROP TABLE IF EXISTS `repeatable_holidays`;
1712 CREATE TABLE `repeatable_holidays` (
1713 `id` int(11) NOT NULL auto_increment,
1714 `branchcode` varchar(10) NOT NULL default '',
1715 `weekday` smallint(6) default NULL,
1716 `day` smallint(6) default NULL,
1717 `month` smallint(6) default NULL,
1718 `title` varchar(50) NOT NULL default '',
1719 `description` text NOT NULL,
1721 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1724 -- Table structure for table `reports_dictionary`
1727 DROP TABLE IF EXISTS `reports_dictionary`;
1728 CREATE TABLE reports_dictionary (
1729 `id` int(11) NOT NULL auto_increment,
1730 `name` varchar(255) default NULL,
1732 `date_created` datetime default NULL,
1733 `date_modified` datetime default NULL,
1735 `area` int(11) default NULL,
1737 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1740 -- Table structure for table `reserveconstraints`
1743 DROP TABLE IF EXISTS `reserveconstraints`;
1744 CREATE TABLE `reserveconstraints` (
1745 `borrowernumber` int(11) NOT NULL default 0,
1746 `reservedate` date default NULL,
1747 `biblionumber` int(11) NOT NULL default 0,
1748 `biblioitemnumber` int(11) default NULL,
1749 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1750 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1753 -- Table structure for table `reserves`
1756 DROP TABLE IF EXISTS `reserves`;
1757 CREATE TABLE `reserves` (
1758 `borrowernumber` int(11) NOT NULL default 0,
1759 `reservedate` date default NULL,
1760 `biblionumber` int(11) NOT NULL default 0,
1761 `constrainttype` varchar(1) default NULL,
1762 `branchcode` varchar(10) default NULL,
1763 `notificationdate` date default NULL,
1764 `reminderdate` date default NULL,
1765 `cancellationdate` date default NULL,
1766 `reservenotes` mediumtext,
1767 `priority` smallint(6) default NULL,
1768 `found` varchar(1) default NULL,
1769 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1770 `itemnumber` int(11) default NULL,
1771 `waitingdate` date default NULL,
1772 KEY `borrowernumber` (`borrowernumber`),
1773 KEY `biblionumber` (`biblionumber`),
1774 KEY `itemnumber` (`itemnumber`),
1775 KEY `branchcode` (`branchcode`),
1776 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1777 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1778 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1779 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1780 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1783 -- Table structure for table `reviews`
1786 DROP TABLE IF EXISTS `reviews`;
1787 CREATE TABLE `reviews` (
1788 `reviewid` int(11) NOT NULL auto_increment,
1789 `borrowernumber` int(11) default NULL,
1790 `biblionumber` int(11) default NULL,
1792 `approved` tinyint(4) default NULL,
1793 `datereviewed` datetime default NULL,
1794 PRIMARY KEY (`reviewid`)
1795 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1798 -- Table structure for table `roadtype`
1801 DROP TABLE IF EXISTS `roadtype`;
1802 CREATE TABLE `roadtype` (
1803 `roadtypeid` int(11) NOT NULL auto_increment,
1804 `road_type` varchar(100) NOT NULL default '',
1805 PRIMARY KEY (`roadtypeid`)
1806 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1809 -- Table structure for table `saved_sql`
1812 DROP TABLE IF EXISTS `saved_sql`;
1813 CREATE TABLE saved_sql (
1814 `id` int(11) NOT NULL auto_increment,
1815 `borrowernumber` int(11) default NULL,
1816 `date_created` datetime default NULL,
1817 `last_modified` datetime default NULL,
1819 `last_run` datetime default NULL,
1820 `report_name` varchar(255) default NULL,
1821 `type` varchar(255) default NULL,
1824 KEY boridx (`borrowernumber`)
1825 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1829 -- Table structure for `saved_reports`
1832 DROP TABLE IF EXISTS `saved_reports`;
1833 CREATE TABLE saved_reports (
1834 `id` int(11) NOT NULL auto_increment,
1835 `report_id` int(11) default NULL,
1837 `date_run` datetime default NULL,
1839 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1843 -- Table structure for table `serial`
1846 DROP TABLE IF EXISTS `serial`;
1847 CREATE TABLE `serial` (
1848 `serialid` int(11) NOT NULL auto_increment,
1849 `biblionumber` varchar(100) NOT NULL default '',
1850 `subscriptionid` varchar(100) NOT NULL default '',
1851 `serialseq` varchar(100) NOT NULL default '',
1852 `status` tinyint(4) NOT NULL default 0,
1853 `planneddate` date default NULL,
1855 `publisheddate` date default NULL,
1856 `itemnumber` text default NULL,
1857 `claimdate` date default NULL,
1858 `routingnotes` text,
1859 PRIMARY KEY (`serialid`)
1860 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1863 -- Table structure for table `sessions`
1866 DROP TABLE IF EXISTS sessions;
1867 CREATE TABLE sessions (
1868 `id` varchar(32) NOT NULL,
1869 `a_session` text NOT NULL,
1871 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1874 -- Table structure for table `special_holidays`
1877 DROP TABLE IF EXISTS `special_holidays`;
1878 CREATE TABLE `special_holidays` (
1879 `id` int(11) NOT NULL auto_increment,
1880 `branchcode` varchar(10) NOT NULL default '',
1881 `day` smallint(6) NOT NULL default 0,
1882 `month` smallint(6) NOT NULL default 0,
1883 `year` smallint(6) NOT NULL default 0,
1884 `isexception` smallint(1) NOT NULL default 1,
1885 `title` varchar(50) NOT NULL default '',
1886 `description` text NOT NULL,
1888 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1891 -- Table structure for table `statistics`
1894 DROP TABLE IF EXISTS `statistics`;
1895 CREATE TABLE `statistics` (
1896 `datetime` datetime default NULL,
1897 `branch` varchar(10) default NULL,
1898 `proccode` varchar(4) default NULL,
1899 `value` double(16,4) default NULL,
1900 `type` varchar(16) default NULL,
1902 `usercode` varchar(10) default NULL,
1903 `itemnumber` int(11) default NULL,
1904 `itemtype` varchar(10) default NULL,
1905 `borrowernumber` int(11) default NULL,
1906 `associatedborrower` int(11) default NULL,
1907 KEY `timeidx` (`datetime`)
1908 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1911 -- Table structure for table `stopwords`
1914 DROP TABLE IF EXISTS `stopwords`;
1915 CREATE TABLE `stopwords` (
1916 `word` varchar(255) default NULL
1917 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1920 -- Table structure for table `subscription`
1923 DROP TABLE IF EXISTS `subscription`;
1924 CREATE TABLE `subscription` (
1925 `biblionumber` int(11) NOT NULL default 0,
1926 `subscriptionid` int(11) NOT NULL auto_increment,
1927 `librarian` varchar(100) default '',
1928 `startdate` date default NULL,
1929 `aqbooksellerid` int(11) default 0,
1930 `cost` int(11) default 0,
1931 `aqbudgetid` int(11) default 0,
1932 `weeklength` int(11) default 0,
1933 `monthlength` int(11) default 0,
1934 `numberlength` int(11) default 0,
1935 `periodicity` tinyint(4) default 0,
1936 `dow` varchar(100) default '',
1937 `numberingmethod` varchar(100) default '',
1939 `status` varchar(100) NOT NULL default '',
1940 `add1` int(11) default 0,
1941 `every1` int(11) default 0,
1942 `whenmorethan1` int(11) default 0,
1943 `setto1` int(11) default NULL,
1944 `lastvalue1` int(11) default NULL,
1945 `add2` int(11) default 0,
1946 `every2` int(11) default 0,
1947 `whenmorethan2` int(11) default 0,
1948 `setto2` int(11) default NULL,
1949 `lastvalue2` int(11) default NULL,
1950 `add3` int(11) default 0,
1951 `every3` int(11) default 0,
1952 `innerloop1` int(11) default 0,
1953 `innerloop2` int(11) default 0,
1954 `innerloop3` int(11) default 0,
1955 `whenmorethan3` int(11) default 0,
1956 `setto3` int(11) default NULL,
1957 `lastvalue3` int(11) default NULL,
1958 `issuesatonce` tinyint(3) NOT NULL default 1,
1959 `firstacquidate` date default NULL,
1960 `manualhistory` tinyint(1) NOT NULL default 0,
1961 `irregularity` text,
1962 `letter` varchar(20) default NULL,
1963 `numberpattern` tinyint(3) default 0,
1964 `distributedto` text,
1965 `internalnotes` longtext,
1967 `location` varchar(80) NULL default '',
1968 `branchcode` varchar(10) NOT NULL default '',
1969 `hemisphere` tinyint(3) default 0,
1970 `lastbranch` varchar(10),
1971 `serialsadditems` tinyint(1) NOT NULL default '0',
1972 `staffdisplaycount` VARCHAR(10) NULL,
1973 `opacdisplaycount` VARCHAR(10) NULL,
1974 `graceperiod` int(11) NOT NULL default '0',
1975 PRIMARY KEY (`subscriptionid`)
1976 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1979 -- Table structure for table `subscriptionhistory`
1982 DROP TABLE IF EXISTS `subscriptionhistory`;
1983 CREATE TABLE `subscriptionhistory` (
1984 `biblionumber` int(11) NOT NULL default 0,
1985 `subscriptionid` int(11) NOT NULL default 0,
1986 `histstartdate` date default NULL,
1987 `enddate` date default NULL,
1988 `missinglist` longtext NOT NULL,
1989 `recievedlist` longtext NOT NULL,
1990 `opacnote` varchar(150) NOT NULL default '',
1991 `librariannote` varchar(150) NOT NULL default '',
1992 PRIMARY KEY (`subscriptionid`),
1993 KEY `biblionumber` (`biblionumber`)
1994 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1997 -- Table structure for table `subscriptionroutinglist`
2000 DROP TABLE IF EXISTS `subscriptionroutinglist`;
2001 CREATE TABLE `subscriptionroutinglist` (
2002 `routingid` int(11) NOT NULL auto_increment,
2003 `borrowernumber` int(11) default NULL,
2004 `ranking` int(11) default NULL,
2005 `subscriptionid` int(11) default NULL,
2006 PRIMARY KEY (`routingid`)
2007 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2010 -- Table structure for table `suggestions`
2013 DROP TABLE IF EXISTS `suggestions`;
2014 CREATE TABLE `suggestions` (
2015 `suggestionid` int(8) NOT NULL auto_increment,
2016 `suggestedby` int(11) NOT NULL default 0,
2017 `managedby` int(11) default NULL,
2018 `STATUS` varchar(10) NOT NULL default '',
2020 `author` varchar(80) default NULL,
2021 `title` varchar(80) default NULL,
2022 `copyrightdate` smallint(6) default NULL,
2023 `publishercode` varchar(255) default NULL,
2024 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2025 `volumedesc` varchar(255) default NULL,
2026 `publicationyear` smallint(6) default 0,
2027 `place` varchar(255) default NULL,
2028 `isbn` varchar(30) default NULL,
2029 `mailoverseeing` smallint(1) default 0,
2030 `biblionumber` int(11) default NULL,
2032 PRIMARY KEY (`suggestionid`),
2033 KEY `suggestedby` (`suggestedby`),
2034 KEY `managedby` (`managedby`)
2035 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2038 -- Table structure for table `systempreferences`
2041 DROP TABLE IF EXISTS `systempreferences`;
2042 CREATE TABLE `systempreferences` (
2043 `variable` varchar(50) NOT NULL default '',
2045 `options` mediumtext,
2047 `type` varchar(20) default NULL,
2048 PRIMARY KEY (`variable`)
2049 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2052 -- Table structure for table `tags`
2055 DROP TABLE IF EXISTS `tags`;
2056 CREATE TABLE `tags` (
2057 `entry` varchar(255) NOT NULL default '',
2058 `weight` bigint(20) NOT NULL default 0,
2059 PRIMARY KEY (`entry`)
2060 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2063 -- Table structure for table `tags_all`
2066 DROP TABLE IF EXISTS `tags_all`;
2067 CREATE TABLE `tags_all` (
2068 `tag_id` int(11) NOT NULL auto_increment,
2069 `borrowernumber` int(11) NOT NULL,
2070 `biblionumber` int(11) NOT NULL,
2071 `term` varchar(255) NOT NULL,
2072 `language` int(4) default NULL,
2073 `date_created` datetime NOT NULL,
2074 PRIMARY KEY (`tag_id`),
2075 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2076 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2077 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2078 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2079 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2080 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2081 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2084 -- Table structure for table `tags_approval`
2087 DROP TABLE IF EXISTS `tags_approval`;
2088 CREATE TABLE `tags_approval` (
2089 `term` varchar(255) NOT NULL,
2090 `approved` int(1) NOT NULL default '0',
2091 `date_approved` datetime default NULL,
2092 `approved_by` int(11) default NULL,
2093 `weight_total` int(9) NOT NULL default '1',
2094 PRIMARY KEY (`term`),
2095 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2096 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2097 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2098 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2101 -- Table structure for table `tags_index`
2104 DROP TABLE IF EXISTS `tags_index`;
2105 CREATE TABLE `tags_index` (
2106 `term` varchar(255) NOT NULL,
2107 `biblionumber` int(11) NOT NULL,
2108 `weight` int(9) NOT NULL default '1',
2109 PRIMARY KEY (`term`,`biblionumber`),
2110 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2111 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2112 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2113 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2114 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2118 -- Table structure for table `userflags`
2121 DROP TABLE IF EXISTS `userflags`;
2122 CREATE TABLE `userflags` (
2123 `bit` int(11) NOT NULL default 0,
2124 `flag` varchar(30) default NULL,
2125 `flagdesc` varchar(255) default NULL,
2126 `defaulton` int(11) default NULL,
2128 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2131 -- Table structure for table `virtualshelves`
2134 DROP TABLE IF EXISTS `virtualshelves`;
2135 CREATE TABLE `virtualshelves` (
2136 `shelfnumber` int(11) NOT NULL auto_increment,
2137 `shelfname` varchar(255) default NULL,
2138 `owner` varchar(80) default NULL,
2139 `category` varchar(1) default NULL,
2140 `sortfield` varchar(16) default NULL,
2141 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2142 PRIMARY KEY (`shelfnumber`)
2143 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2146 -- Table structure for table `virtualshelfcontents`
2149 DROP TABLE IF EXISTS `virtualshelfcontents`;
2150 CREATE TABLE `virtualshelfcontents` (
2151 `shelfnumber` int(11) NOT NULL default 0,
2152 `biblionumber` int(11) NOT NULL default 0,
2153 `flags` int(11) default NULL,
2154 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2155 KEY `shelfnumber` (`shelfnumber`),
2156 KEY `biblionumber` (`biblionumber`),
2157 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2158 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2159 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2162 -- Table structure for table `z3950servers`
2165 DROP TABLE IF EXISTS `z3950servers`;
2166 CREATE TABLE `z3950servers` (
2167 `host` varchar(255) default NULL,
2168 `port` int(11) default NULL,
2169 `db` varchar(255) default NULL,
2170 `userid` varchar(255) default NULL,
2171 `password` varchar(255) default NULL,
2173 `id` int(11) NOT NULL auto_increment,
2174 `checked` smallint(6) default NULL,
2175 `rank` int(11) default NULL,
2176 `syntax` varchar(80) default NULL,
2178 `position` enum('primary','secondary','') NOT NULL default 'primary',
2179 `type` enum('zed','opensearch') NOT NULL default 'zed',
2180 `encoding` text default NULL,
2181 `description` text NOT NULL,
2183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2186 -- Table structure for table `zebraqueue`
2189 DROP TABLE IF EXISTS `zebraqueue`;
2190 CREATE TABLE `zebraqueue` (
2191 `id` int(11) NOT NULL auto_increment,
2192 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2193 `operation` char(20) NOT NULL default '',
2194 `server` char(20) NOT NULL default '',
2195 `done` int(11) NOT NULL default '0',
2196 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2198 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2201 DROP TABLE IF EXISTS `services_throttle`;
2202 CREATE TABLE `services_throttle` (
2203 `service_type` varchar(10) NOT NULL default '',
2204 `service_count` varchar(45) default NULL,
2205 PRIMARY KEY (`service_type`)
2206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2208 -- http://www.w3.org/International/articles/language-tags/
2211 DROP TABLE IF EXISTS language_subtag_registry;
2212 CREATE TABLE language_subtag_registry (
2214 type varchar(25), -- language-script-region-variant-extension-privateuse
2215 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2217 id int(11) NOT NULL auto_increment,
2219 KEY `subtag` (`subtag`)
2220 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2222 -- TODO: add suppress_scripts
2223 -- this maps three letter codes defined in iso639.2 back to their
2224 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2225 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2226 CREATE TABLE language_rfc4646_to_iso639 (
2227 rfc4646_subtag varchar(25),
2228 iso639_2_code varchar(25),
2229 id int(11) NOT NULL auto_increment,
2231 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2232 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2234 DROP TABLE IF EXISTS language_descriptions;
2235 CREATE TABLE language_descriptions (
2239 description varchar(255),
2240 id int(11) NOT NULL auto_increment,
2243 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2245 -- bi-directional support, keyed by script subcode
2246 DROP TABLE IF EXISTS language_script_bidi;
2247 CREATE TABLE language_script_bidi (
2248 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2249 bidi varchar(3), -- rtl ltr
2250 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2251 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2253 -- TODO: need to map language subtags to script subtags for detection
2254 -- of bidi when script is not specified (like ar, he)
2255 DROP TABLE IF EXISTS language_script_mapping;
2256 CREATE TABLE language_script_mapping (
2257 language_subtag varchar(25),
2258 script_subtag varchar(25),
2259 KEY `language_subtag` (`language_subtag`)
2260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2262 DROP TABLE IF EXISTS `permissions`;
2263 CREATE TABLE `permissions` (
2264 `module_bit` int(11) NOT NULL DEFAULT 0,
2265 `code` varchar(64) DEFAULT NULL,
2266 `description` varchar(255) DEFAULT NULL,
2267 PRIMARY KEY (`module_bit`, `code`),
2268 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2269 ON DELETE CASCADE ON UPDATE CASCADE
2270 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2272 DROP TABLE IF EXISTS `serialitems`;
2273 CREATE TABLE `serialitems` (
2274 `itemnumber` int(11) NOT NULL,
2275 `serialid` int(11) NOT NULL,
2276 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2277 KEY `serialitems_sfk_1` (`serialid`),
2278 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2279 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2281 DROP TABLE IF EXISTS `user_permissions`;
2282 CREATE TABLE `user_permissions` (
2283 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2284 `module_bit` int(11) NOT NULL DEFAULT 0,
2285 `code` varchar(64) DEFAULT NULL,
2286 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2287 ON DELETE CASCADE ON UPDATE CASCADE,
2288 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2289 ON DELETE CASCADE ON UPDATE CASCADE
2290 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2293 -- Table structure for table `tmp_holdsqueue`
2296 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2297 CREATE TABLE `tmp_holdsqueue` (
2298 `biblionumber` int(11) default NULL,
2299 `itemnumber` int(11) default NULL,
2300 `barcode` varchar(20) default NULL,
2301 `surname` mediumtext NOT NULL,
2304 `borrowernumber` int(11) NOT NULL,
2305 `cardnumber` varchar(16) default NULL,
2306 `reservedate` date default NULL,
2308 `itemcallnumber` varchar(255) default NULL,
2309 `holdingbranch` varchar(10) default NULL,
2310 `pickbranch` varchar(10) default NULL,
2312 `item_level_request` tinyint(4) NOT NULL default 0
2313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2316 -- Table structure for table `message_queue`
2319 DROP TABLE IF EXISTS `message_queue`;
2320 CREATE TABLE `message_queue` (
2321 `message_id` int(11) NOT NULL auto_increment,
2322 `borrowernumber` int(11) default NULL,
2325 `metadata` text DEFAULT NULL,
2326 `letter_code` varchar(64) DEFAULT NULL,
2327 `message_transport_type` varchar(20) NOT NULL,
2328 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2329 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2330 `to_address` mediumtext,
2331 `from_address` mediumtext,
2332 `content_type` text,
2333 KEY `message_id` (`message_id`),
2334 KEY `borrowernumber` (`borrowernumber`),
2335 KEY `message_transport_type` (`message_transport_type`),
2336 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2337 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2338 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2341 -- Table structure for table `message_transport_types`
2344 DROP TABLE IF EXISTS `message_transport_types`;
2345 CREATE TABLE `message_transport_types` (
2346 `message_transport_type` varchar(20) NOT NULL,
2347 PRIMARY KEY (`message_transport_type`)
2348 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2351 -- Table structure for table `message_attributes`
2354 DROP TABLE IF EXISTS `message_attributes`;
2355 CREATE TABLE `message_attributes` (
2356 `message_attribute_id` int(11) NOT NULL auto_increment,
2357 `message_name` varchar(20) NOT NULL default '',
2358 `takes_days` tinyint(1) NOT NULL default '0',
2359 PRIMARY KEY (`message_attribute_id`),
2360 UNIQUE KEY `message_name` (`message_name`)
2361 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2364 -- Table structure for table `message_transports`
2367 DROP TABLE IF EXISTS `message_transports`;
2368 CREATE TABLE `message_transports` (
2369 `message_attribute_id` int(11) NOT NULL,
2370 `message_transport_type` varchar(20) NOT NULL,
2371 `is_digest` tinyint(1) NOT NULL default '0',
2372 `letter_module` varchar(20) NOT NULL default '',
2373 `letter_code` varchar(20) NOT NULL default '',
2374 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2375 KEY `message_transport_type` (`message_transport_type`),
2376 KEY `letter_module` (`letter_module`,`letter_code`),
2377 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2378 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2379 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2380 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2383 -- Table structure for table `borrower_message_preferences`
2386 DROP TABLE IF EXISTS `borrower_message_preferences`;
2387 CREATE TABLE `borrower_message_preferences` (
2388 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2389 `borrowernumber` int(11) default NULL,
2390 `categorycode` varchar(10) default NULL,
2391 `message_attribute_id` int(11) default '0',
2392 `days_in_advance` int(11) default '0',
2393 `wants_digest` tinyint(1) NOT NULL default '0',
2394 PRIMARY KEY (`borrower_message_preference_id`),
2395 KEY `borrowernumber` (`borrowernumber`),
2396 KEY `categorycode` (`categorycode`),
2397 KEY `message_attribute_id` (`message_attribute_id`),
2398 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2399 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2400 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2401 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2404 -- Table structure for table `borrower_message_transport_preferences`
2407 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2408 CREATE TABLE `borrower_message_transport_preferences` (
2409 `borrower_message_preference_id` int(11) NOT NULL default '0',
2410 `message_transport_type` varchar(20) NOT NULL default '0',
2411 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2412 KEY `message_transport_type` (`message_transport_type`),
2413 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,
2414 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
2415 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2418 -- Table structure for the table branch_transfer_limits
2421 DROP TABLE IF EXISTS `branch_transfer_limits`;
2422 CREATE TABLE branch_transfer_limits (
2423 limitId int(8) NOT NULL auto_increment,
2424 toBranch varchar(10) NOT NULL,
2425 fromBranch varchar(10) NOT NULL,
2426 itemtype varchar(10) NULL,
2427 ccode varchar(10) NULL,
2428 PRIMARY KEY (limitId)
2429 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2432 -- Table structure for table `item_circulation_alert_preferences`
2435 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2436 CREATE TABLE `item_circulation_alert_preferences` (
2437 `id` int(11) NOT NULL auto_increment,
2438 `branchcode` varchar(10) NOT NULL,
2439 `categorycode` varchar(10) NOT NULL,
2440 `item_type` varchar(10) NOT NULL,
2441 `notification` varchar(16) NOT NULL,
2443 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2444 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2447 -- Table structure for table `messages`
2450 CREATE TABLE `messages` (
2451 `message_id` int(11) NOT NULL auto_increment,
2452 `borrowernumber` int(11) NOT NULL,
2453 `branchcode` varchar(4) default NULL,
2454 `message_type` varchar(1) NOT NULL,
2455 `message` text NOT NULL,
2456 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2457 PRIMARY KEY (`message_id`)
2458 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2460 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2461 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2462 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2463 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2464 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2465 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2466 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2467 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;