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 `imageurl` varchar(200) default NULL,
418 KEY `name` (`category`),
420 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
423 -- Table structure for table `biblio`
426 DROP TABLE IF EXISTS `biblio`;
427 CREATE TABLE `biblio` (
428 `biblionumber` int(11) NOT NULL auto_increment,
429 `frameworkcode` varchar(4) NOT NULL default '',
432 `unititle` mediumtext,
434 `serial` tinyint(1) default NULL,
435 `seriestitle` mediumtext,
436 `copyrightdate` smallint(6) default NULL,
437 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
438 `datecreated` DATE NOT NULL,
439 `abstract` mediumtext,
440 PRIMARY KEY (`biblionumber`),
441 KEY `blbnoidx` (`biblionumber`)
442 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
445 -- Table structure for table `biblio_framework`
448 DROP TABLE IF EXISTS `biblio_framework`;
449 CREATE TABLE `biblio_framework` (
450 `frameworkcode` varchar(4) NOT NULL default '',
451 `frameworktext` varchar(255) NOT NULL default '',
452 PRIMARY KEY (`frameworkcode`)
453 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
456 -- Table structure for table `biblioitems`
459 DROP TABLE IF EXISTS `biblioitems`;
460 CREATE TABLE `biblioitems` (
461 `biblioitemnumber` int(11) NOT NULL auto_increment,
462 `biblionumber` int(11) NOT NULL default 0,
465 `itemtype` varchar(10) default NULL,
466 `isbn` varchar(30) default NULL,
467 `issn` varchar(9) default NULL,
468 `publicationyear` text,
469 `publishercode` varchar(255) default NULL,
470 `volumedate` date default NULL,
472 `collectiontitle` mediumtext default NULL,
473 `collectionissn` text default NULL,
474 `collectionvolume` mediumtext default NULL,
475 `editionstatement` text default NULL,
476 `editionresponsibility` text default NULL,
477 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
478 `illus` varchar(255) default NULL,
479 `pages` varchar(255) default NULL,
481 `size` varchar(255) default NULL,
482 `place` varchar(255) default NULL,
483 `lccn` varchar(25) default NULL,
485 `url` varchar(255) default NULL,
486 `cn_source` varchar(10) default NULL,
487 `cn_class` varchar(30) default NULL,
488 `cn_item` varchar(10) default NULL,
489 `cn_suffix` varchar(10) default NULL,
490 `cn_sort` varchar(30) default NULL,
491 `totalissues` int(10),
492 `marcxml` longtext NOT NULL,
493 PRIMARY KEY (`biblioitemnumber`),
494 KEY `bibinoidx` (`biblioitemnumber`),
495 KEY `bibnoidx` (`biblionumber`),
497 KEY `publishercode` (`publishercode`),
499 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
500 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
503 -- Table structure for table `borrowers`
506 DROP TABLE IF EXISTS `borrowers`;
507 CREATE TABLE `borrowers` (
508 `borrowernumber` int(11) NOT NULL auto_increment,
509 `cardnumber` varchar(16) default NULL,
510 `surname` mediumtext NOT NULL,
513 `othernames` mediumtext,
515 `streetnumber` varchar(10) default NULL,
516 `streettype` varchar(50) default NULL,
517 `address` mediumtext NOT NULL,
519 `city` mediumtext NOT NULL,
520 `zipcode` varchar(25) default NULL,
524 `mobile` varchar(50) default NULL,
528 `B_streetnumber` varchar(10) default NULL,
529 `B_streettype` varchar(50) default NULL,
530 `B_address` varchar(100) default NULL,
531 `B_address2` text default NULL,
533 `B_zipcode` varchar(25) default NULL,
536 `B_phone` mediumtext,
537 `dateofbirth` date default NULL,
538 `branchcode` varchar(10) NOT NULL default '',
539 `categorycode` varchar(10) NOT NULL default '',
540 `dateenrolled` date default NULL,
541 `dateexpiry` date default NULL,
542 `gonenoaddress` tinyint(1) default NULL,
543 `lost` tinyint(1) default NULL,
544 `debarred` tinyint(1) default NULL,
545 `contactname` mediumtext,
546 `contactfirstname` text,
548 `guarantorid` int(11) default NULL,
549 `borrowernotes` mediumtext,
550 `relationship` varchar(100) default NULL,
551 `ethnicity` varchar(50) default NULL,
552 `ethnotes` varchar(255) default NULL,
553 `sex` varchar(1) default NULL,
554 `password` varchar(30) default NULL,
555 `flags` int(11) default NULL,
556 `userid` varchar(30) default NULL,
557 `opacnote` mediumtext,
558 `contactnote` varchar(255) default NULL,
559 `sort1` varchar(80) default NULL,
560 `sort2` varchar(80) default NULL,
561 `altcontactfirstname` varchar(255) default NULL,
562 `altcontactsurname` varchar(255) default NULL,
563 `altcontactaddress1` varchar(255) default NULL,
564 `altcontactaddress2` varchar(255) default NULL,
565 `altcontactaddress3` varchar(255) default NULL,
566 `altcontactzipcode` varchar(50) default NULL,
567 `altcontactcountry` text default NULL,
568 `altcontactphone` varchar(50) default NULL,
569 `smsalertnumber` varchar(50) default NULL,
570 UNIQUE KEY `cardnumber` (`cardnumber`),
571 PRIMARY KEY `borrowernumber` (`borrowernumber`),
572 KEY `categorycode` (`categorycode`),
573 KEY `branchcode` (`branchcode`),
574 KEY `userid` (`userid`),
575 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
576 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
577 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
580 -- Table structure for table `borrower_attribute_types`
583 DROP TABLE IF EXISTS `borrower_attribute_types`;
584 CREATE TABLE `borrower_attribute_types` (
585 `code` varchar(10) NOT NULL,
586 `description` varchar(255) NOT NULL,
587 `repeatable` tinyint(1) NOT NULL default 0,
588 `unique_id` tinyint(1) NOT NULL default 0,
589 `opac_display` tinyint(1) NOT NULL default 0,
590 `password_allowed` tinyint(1) NOT NULL default 0,
591 `staff_searchable` tinyint(1) NOT NULL default 0,
592 `authorised_value_category` varchar(10) default NULL,
594 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
597 -- Table structure for table `borrower_attributes`
600 DROP TABLE IF EXISTS `borrower_attributes`;
601 CREATE TABLE `borrower_attributes` (
602 `borrowernumber` int(11) NOT NULL,
603 `code` varchar(10) NOT NULL,
604 `attribute` varchar(64) default NULL,
605 `password` varchar(64) default NULL,
606 KEY `borrowernumber` (`borrowernumber`),
607 KEY `code_attribute` (`code`, `attribute`),
608 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
609 ON DELETE CASCADE ON UPDATE CASCADE,
610 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
611 ON DELETE CASCADE ON UPDATE CASCADE
612 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
614 CREATE TABLE `branch_item_rules` (
615 `branchcode` varchar(10) NOT NULL,
616 `itemtype` varchar(10) NOT NULL,
617 `holdallowed` tinyint(1) default NULL,
618 PRIMARY KEY (`itemtype`,`branchcode`),
619 KEY `branch_item_rules_ibfk_2` (`branchcode`),
620 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
621 ON DELETE CASCADE ON UPDATE CASCADE,
622 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
623 ON DELETE CASCADE ON UPDATE CASCADE
624 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
627 -- Table structure for table `branchcategories`
630 DROP TABLE IF EXISTS `branchcategories`;
631 CREATE TABLE `branchcategories` (
632 `categorycode` varchar(10) NOT NULL default '',
633 `categoryname` varchar(32),
634 `codedescription` mediumtext,
635 `categorytype` varchar(16),
636 PRIMARY KEY (`categorycode`)
637 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
640 -- Table structure for table `branches`
643 DROP TABLE IF EXISTS `branches`;
644 CREATE TABLE `branches` (
645 `branchcode` varchar(10) NOT NULL default '',
646 `branchname` mediumtext NOT NULL,
647 `branchaddress1` mediumtext,
648 `branchaddress2` mediumtext,
649 `branchaddress3` mediumtext,
650 `branchzip` varchar(25) default NULL,
651 `branchcity` mediumtext,
652 `branchcountry` text,
653 `branchphone` mediumtext,
654 `branchfax` mediumtext,
655 `branchemail` mediumtext,
656 `branchurl` mediumtext,
657 `issuing` tinyint(4) default NULL,
658 `branchip` varchar(15) default NULL,
659 `branchprinter` varchar(100) default NULL,
660 `branchnotes` mediumtext,
661 UNIQUE KEY `branchcode` (`branchcode`)
662 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
665 -- Table structure for table `branchrelations`
668 DROP TABLE IF EXISTS `branchrelations`;
669 CREATE TABLE `branchrelations` (
670 `branchcode` varchar(10) NOT NULL default '',
671 `categorycode` varchar(10) NOT NULL default '',
672 PRIMARY KEY (`branchcode`,`categorycode`),
673 KEY `branchcode` (`branchcode`),
674 KEY `categorycode` (`categorycode`),
675 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
676 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
677 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
680 -- Table structure for table `branchtransfers`
683 DROP TABLE IF EXISTS `branchtransfers`;
684 CREATE TABLE `branchtransfers` (
685 `itemnumber` int(11) NOT NULL default 0,
686 `datesent` datetime default NULL,
687 `frombranch` varchar(10) NOT NULL default '',
688 `datearrived` datetime default NULL,
689 `tobranch` varchar(10) NOT NULL default '',
690 `comments` mediumtext,
691 KEY `frombranch` (`frombranch`),
692 KEY `tobranch` (`tobranch`),
693 KEY `itemnumber` (`itemnumber`),
694 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
695 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
696 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
697 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
701 -- Table structure for table `browser`
703 DROP TABLE IF EXISTS `browser`;
704 CREATE TABLE `browser` (
705 `level` int(11) NOT NULL,
706 `classification` varchar(20) NOT NULL,
707 `description` varchar(255) NOT NULL,
708 `number` bigint(20) NOT NULL,
709 `endnode` tinyint(4) NOT NULL
710 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
713 -- Table structure for table `categories`
716 DROP TABLE IF EXISTS `categories`;
717 CREATE TABLE `categories` (
718 `categorycode` varchar(10) NOT NULL default '',
719 `description` mediumtext,
720 `enrolmentperiod` smallint(6) default NULL,
721 `upperagelimit` smallint(6) default NULL,
722 `dateofbirthrequired` tinyint(1) default NULL,
723 `finetype` varchar(30) default NULL,
724 `bulk` tinyint(1) default NULL,
725 `enrolmentfee` decimal(28,6) default NULL,
726 `overduenoticerequired` tinyint(1) default NULL,
727 `issuelimit` smallint(6) default NULL,
728 `reservefee` decimal(28,6) default NULL,
729 `category_type` varchar(1) NOT NULL default 'A',
730 PRIMARY KEY (`categorycode`),
731 UNIQUE KEY `categorycode` (`categorycode`)
732 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
735 -- Table structure for table `borrower_branch_circ_rules`
738 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
739 CREATE TABLE `branch_borrower_circ_rules` (
740 `branchcode` VARCHAR(10) NOT NULL,
741 `categorycode` VARCHAR(10) NOT NULL,
742 `maxissueqty` int(4) default NULL,
743 PRIMARY KEY (`categorycode`, `branchcode`),
744 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
745 ON DELETE CASCADE ON UPDATE CASCADE,
746 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
747 ON DELETE CASCADE ON UPDATE CASCADE
748 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
751 -- Table structure for table `default_borrower_circ_rules`
754 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
755 CREATE TABLE `default_borrower_circ_rules` (
756 `categorycode` VARCHAR(10) NOT NULL,
757 `maxissueqty` int(4) default NULL,
758 PRIMARY KEY (`categorycode`),
759 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
760 ON DELETE CASCADE ON UPDATE CASCADE
761 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
764 -- Table structure for table `default_branch_circ_rules`
767 DROP TABLE IF EXISTS `default_branch_circ_rules`;
768 CREATE TABLE `default_branch_circ_rules` (
769 `branchcode` VARCHAR(10) NOT NULL,
770 `maxissueqty` int(4) default NULL,
771 `holdallowed` tinyint(1) default NULL,
772 PRIMARY KEY (`branchcode`),
773 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
774 ON DELETE CASCADE ON UPDATE CASCADE
775 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
778 -- Table structure for table `default_branch_item_rules`
781 CREATE TABLE `default_branch_item_rules` (
782 `itemtype` varchar(10) NOT NULL,
783 `holdallowed` tinyint(1) default NULL,
784 PRIMARY KEY (`itemtype`),
785 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
786 ON DELETE CASCADE ON UPDATE CASCADE
787 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
790 -- Table structure for table `default_circ_rules`
793 DROP TABLE IF EXISTS `default_circ_rules`;
794 CREATE TABLE `default_circ_rules` (
795 `singleton` enum('singleton') NOT NULL default 'singleton',
796 `maxissueqty` int(4) default NULL,
797 `holdallowed` int(1) default NULL,
798 PRIMARY KEY (`singleton`)
799 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
802 -- Table structure for table `cities`
805 DROP TABLE IF EXISTS `cities`;
806 CREATE TABLE `cities` (
807 `cityid` int(11) NOT NULL auto_increment,
808 `city_name` varchar(100) NOT NULL default '',
809 `city_zipcode` varchar(20) default NULL,
810 PRIMARY KEY (`cityid`)
811 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
814 -- Table structure for table `class_sort_rules`
817 DROP TABLE IF EXISTS `class_sort_rules`;
818 CREATE TABLE `class_sort_rules` (
819 `class_sort_rule` varchar(10) NOT NULL default '',
820 `description` mediumtext,
821 `sort_routine` varchar(30) NOT NULL default '',
822 PRIMARY KEY (`class_sort_rule`),
823 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
824 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
827 -- Table structure for table `class_sources`
830 DROP TABLE IF EXISTS `class_sources`;
831 CREATE TABLE `class_sources` (
832 `cn_source` varchar(10) NOT NULL default '',
833 `description` mediumtext,
834 `used` tinyint(4) NOT NULL default 0,
835 `class_sort_rule` varchar(10) NOT NULL default '',
836 PRIMARY KEY (`cn_source`),
837 UNIQUE KEY `cn_source_idx` (`cn_source`),
838 KEY `used_idx` (`used`),
839 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
840 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
843 -- Table structure for table `currency`
846 DROP TABLE IF EXISTS `currency`;
847 CREATE TABLE `currency` (
848 `currency` varchar(10) NOT NULL default '',
849 `symbol` varchar(5) default NULL,
850 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
851 `rate` float(7,5) default NULL,
852 `active` tinyint(1) default NULL,
853 PRIMARY KEY (`currency`)
854 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
857 -- Table structure for table `deletedbiblio`
860 DROP TABLE IF EXISTS `deletedbiblio`;
861 CREATE TABLE `deletedbiblio` (
862 `biblionumber` int(11) NOT NULL default 0,
863 `frameworkcode` varchar(4) NOT NULL default '',
866 `unititle` mediumtext,
868 `serial` tinyint(1) default NULL,
869 `seriestitle` mediumtext,
870 `copyrightdate` smallint(6) default NULL,
871 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
872 `datecreated` DATE NOT NULL,
873 `abstract` mediumtext,
874 PRIMARY KEY (`biblionumber`),
875 KEY `blbnoidx` (`biblionumber`)
876 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
879 -- Table structure for table `deletedbiblioitems`
882 DROP TABLE IF EXISTS `deletedbiblioitems`;
883 CREATE TABLE `deletedbiblioitems` (
884 `biblioitemnumber` int(11) NOT NULL default 0,
885 `biblionumber` int(11) NOT NULL default 0,
888 `itemtype` varchar(10) default NULL,
889 `isbn` varchar(30) default NULL,
890 `issn` varchar(9) default NULL,
891 `publicationyear` text,
892 `publishercode` varchar(255) default NULL,
893 `volumedate` date default NULL,
895 `collectiontitle` mediumtext default NULL,
896 `collectionissn` text default NULL,
897 `collectionvolume` mediumtext default NULL,
898 `editionstatement` text default NULL,
899 `editionresponsibility` text default NULL,
900 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
901 `illus` varchar(255) default NULL,
902 `pages` varchar(255) default NULL,
904 `size` varchar(255) default NULL,
905 `place` varchar(255) default NULL,
906 `lccn` varchar(25) default NULL,
908 `url` varchar(255) default NULL,
909 `cn_source` varchar(10) default NULL,
910 `cn_class` varchar(30) default NULL,
911 `cn_item` varchar(10) default NULL,
912 `cn_suffix` varchar(10) default NULL,
913 `cn_sort` varchar(30) default NULL,
914 `totalissues` int(10),
915 `marcxml` longtext NOT NULL,
916 PRIMARY KEY (`biblioitemnumber`),
917 KEY `bibinoidx` (`biblioitemnumber`),
918 KEY `bibnoidx` (`biblionumber`),
920 KEY `publishercode` (`publishercode`)
921 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
924 -- Table structure for table `deletedborrowers`
927 DROP TABLE IF EXISTS `deletedborrowers`;
928 CREATE TABLE `deletedborrowers` (
929 `borrowernumber` int(11) NOT NULL default 0,
930 `cardnumber` varchar(9) NOT NULL default '',
931 `surname` mediumtext NOT NULL,
934 `othernames` mediumtext,
936 `streetnumber` varchar(10) default NULL,
937 `streettype` varchar(50) default NULL,
938 `address` mediumtext NOT NULL,
940 `city` mediumtext NOT NULL,
941 `zipcode` varchar(25) default NULL,
945 `mobile` varchar(50) default NULL,
949 `B_streetnumber` varchar(10) default NULL,
950 `B_streettype` varchar(50) default NULL,
951 `B_address` varchar(100) default NULL,
952 `B_address2` text default NULL,
954 `B_zipcode` varchar(25) default NULL,
957 `B_phone` mediumtext,
958 `dateofbirth` date default NULL,
959 `branchcode` varchar(10) NOT NULL default '',
960 `categorycode` varchar(10) default NULL,
961 `dateenrolled` date default NULL,
962 `dateexpiry` date default NULL,
963 `gonenoaddress` tinyint(1) default NULL,
964 `lost` tinyint(1) default NULL,
965 `debarred` tinyint(1) default NULL,
966 `contactname` mediumtext,
967 `contactfirstname` text,
969 `guarantorid` int(11) default NULL,
970 `borrowernotes` mediumtext,
971 `relationship` varchar(100) default NULL,
972 `ethnicity` varchar(50) default NULL,
973 `ethnotes` varchar(255) default NULL,
974 `sex` varchar(1) default NULL,
975 `password` varchar(30) default NULL,
976 `flags` int(11) default NULL,
977 `userid` varchar(30) default NULL,
978 `opacnote` mediumtext,
979 `contactnote` varchar(255) default NULL,
980 `sort1` varchar(80) default NULL,
981 `sort2` varchar(80) default NULL,
982 `altcontactfirstname` varchar(255) default NULL,
983 `altcontactsurname` varchar(255) default NULL,
984 `altcontactaddress1` varchar(255) default NULL,
985 `altcontactaddress2` varchar(255) default NULL,
986 `altcontactaddress3` varchar(255) default NULL,
987 `altcontactzipcode` varchar(50) default NULL,
988 `altcontactcountry` text default NULL,
989 `altcontactphone` varchar(50) default NULL,
990 `smsalertnumber` varchar(50) default NULL,
991 KEY `borrowernumber` (`borrowernumber`),
992 KEY `cardnumber` (`cardnumber`)
993 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
996 -- Table structure for table `deleteditems`
999 DROP TABLE IF EXISTS `deleteditems`;
1000 CREATE TABLE `deleteditems` (
1001 `itemnumber` int(11) NOT NULL default 0,
1002 `biblionumber` int(11) NOT NULL default 0,
1003 `biblioitemnumber` int(11) NOT NULL default 0,
1004 `barcode` varchar(20) default NULL,
1005 `dateaccessioned` date default NULL,
1006 `booksellerid` mediumtext default NULL,
1007 `homebranch` varchar(10) default NULL,
1008 `price` decimal(8,2) default NULL,
1009 `replacementprice` decimal(8,2) default NULL,
1010 `replacementpricedate` date default NULL,
1011 `datelastborrowed` date default NULL,
1012 `datelastseen` date default NULL,
1013 `stack` tinyint(1) default NULL,
1014 `notforloan` tinyint(1) NOT NULL default 0,
1015 `damaged` tinyint(1) NOT NULL default 0,
1016 `itemlost` tinyint(1) NOT NULL default 0,
1017 `wthdrawn` tinyint(1) NOT NULL default 0,
1018 `itemcallnumber` varchar(255) default NULL,
1019 `issues` smallint(6) default NULL,
1020 `renewals` smallint(6) default NULL,
1021 `reserves` smallint(6) default NULL,
1022 `restricted` tinyint(1) default NULL,
1023 `itemnotes` mediumtext,
1024 `holdingbranch` varchar(10) default NULL,
1025 `paidfor` mediumtext,
1026 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1027 `location` varchar(80) default NULL,
1028 `permanent_location` varchar(80) default NULL,
1029 `onloan` date default NULL,
1030 `cn_source` varchar(10) default NULL,
1031 `cn_sort` varchar(30) default NULL,
1032 `ccode` varchar(10) default NULL,
1033 `materials` varchar(10) default NULL,
1034 `uri` varchar(255) default NULL,
1035 `itype` varchar(10) default NULL,
1036 `more_subfields_xml` longtext default NULL,
1037 `enumchron` varchar(80) default NULL,
1038 `copynumber` varchar(32) default NULL,
1040 PRIMARY KEY (`itemnumber`),
1041 KEY `delitembarcodeidx` (`barcode`),
1042 KEY `delitembinoidx` (`biblioitemnumber`),
1043 KEY `delitembibnoidx` (`biblionumber`),
1044 KEY `delhomebranch` (`homebranch`),
1045 KEY `delholdingbranch` (`holdingbranch`)
1046 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1049 -- Table structure for table `ethnicity`
1052 DROP TABLE IF EXISTS `ethnicity`;
1053 CREATE TABLE `ethnicity` (
1054 `code` varchar(10) NOT NULL default '',
1055 `name` varchar(255) default NULL,
1056 PRIMARY KEY (`code`)
1057 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1060 -- Table structure for table `hold_fill_targets`
1063 DROP TABLE IF EXISTS `hold_fill_targets`;
1064 CREATE TABLE hold_fill_targets (
1065 `borrowernumber` int(11) NOT NULL,
1066 `biblionumber` int(11) NOT NULL,
1067 `itemnumber` int(11) NOT NULL,
1068 `source_branchcode` varchar(10) default NULL,
1069 `item_level_request` tinyint(4) NOT NULL default 0,
1070 PRIMARY KEY `itemnumber` (`itemnumber`),
1071 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
1072 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
1073 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1074 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
1075 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1076 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
1077 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1078 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
1079 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1080 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1083 -- Table structure for table `import_batches`
1086 DROP TABLE IF EXISTS `import_batches`;
1087 CREATE TABLE `import_batches` (
1088 `import_batch_id` int(11) NOT NULL auto_increment,
1089 `matcher_id` int(11) default NULL,
1090 `template_id` int(11) default NULL,
1091 `branchcode` varchar(10) default NULL,
1092 `num_biblios` int(11) NOT NULL default 0,
1093 `num_items` int(11) NOT NULL default 0,
1094 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1095 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
1096 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
1097 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
1098 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
1099 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
1100 `file_name` varchar(100),
1101 `comments` mediumtext,
1102 PRIMARY KEY (`import_batch_id`),
1103 KEY `branchcode` (`branchcode`)
1104 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1107 -- Table structure for table `import_records`
1110 DROP TABLE IF EXISTS `import_records`;
1111 CREATE TABLE `import_records` (
1112 `import_record_id` int(11) NOT NULL auto_increment,
1113 `import_batch_id` int(11) NOT NULL,
1114 `branchcode` varchar(10) default NULL,
1115 `record_sequence` int(11) NOT NULL default 0,
1116 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1117 `import_date` DATE default NULL,
1118 `marc` longblob NOT NULL,
1119 `marcxml` longtext NOT NULL,
1120 `marcxml_old` longtext NOT NULL,
1121 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
1122 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
1123 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
1124 `import_error` mediumtext,
1125 `encoding` varchar(40) NOT NULL default '',
1126 `z3950random` varchar(40) default NULL,
1127 PRIMARY KEY (`import_record_id`),
1128 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
1129 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1130 KEY `branchcode` (`branchcode`),
1131 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
1132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1135 -- Table structure for `import_record_matches`
1137 DROP TABLE IF EXISTS `import_record_matches`;
1138 CREATE TABLE `import_record_matches` (
1139 `import_record_id` int(11) NOT NULL,
1140 `candidate_match_id` int(11) NOT NULL,
1141 `score` int(11) NOT NULL default 0,
1142 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
1143 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1144 KEY `record_score` (`import_record_id`, `score`)
1145 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1148 -- Table structure for table `import_biblios`
1151 DROP TABLE IF EXISTS `import_biblios`;
1152 CREATE TABLE `import_biblios` (
1153 `import_record_id` int(11) NOT NULL,
1154 `matched_biblionumber` int(11) default NULL,
1155 `control_number` varchar(25) default NULL,
1156 `original_source` varchar(25) default NULL,
1157 `title` varchar(128) default NULL,
1158 `author` varchar(80) default NULL,
1159 `isbn` varchar(30) default NULL,
1160 `issn` varchar(9) default NULL,
1161 `has_items` tinyint(1) NOT NULL default 0,
1162 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
1163 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1164 KEY `matched_biblionumber` (`matched_biblionumber`),
1165 KEY `title` (`title`),
1167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1170 -- Table structure for table `import_items`
1173 DROP TABLE IF EXISTS `import_items`;
1174 CREATE TABLE `import_items` (
1175 `import_items_id` int(11) NOT NULL auto_increment,
1176 `import_record_id` int(11) NOT NULL,
1177 `itemnumber` int(11) default NULL,
1178 `branchcode` varchar(10) default NULL,
1179 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
1180 `marcxml` longtext NOT NULL,
1181 `import_error` mediumtext,
1182 PRIMARY KEY (`import_items_id`),
1183 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
1184 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1185 KEY `itemnumber` (`itemnumber`),
1186 KEY `branchcode` (`branchcode`)
1187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1190 -- Table structure for table `issues`
1193 DROP TABLE IF EXISTS `issues`;
1194 CREATE TABLE `issues` (
1195 `borrowernumber` int(11) default NULL,
1196 `itemnumber` int(11) default NULL,
1197 `date_due` date default NULL,
1198 `branchcode` varchar(10) default NULL,
1199 `issuingbranch` varchar(18) default NULL,
1200 `returndate` date default NULL,
1201 `lastreneweddate` date default NULL,
1202 `return` varchar(4) default NULL,
1203 `renewals` tinyint(4) default NULL,
1204 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1205 `issuedate` date default NULL,
1206 KEY `issuesborridx` (`borrowernumber`),
1207 KEY `issuesitemidx` (`itemnumber`),
1208 KEY `bordate` (`borrowernumber`,`timestamp`),
1209 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
1210 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
1211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1214 -- Table structure for table `issuingrules`
1217 DROP TABLE IF EXISTS `issuingrules`;
1218 CREATE TABLE `issuingrules` (
1219 `categorycode` varchar(10) NOT NULL default '',
1220 `itemtype` varchar(10) NOT NULL default '',
1221 `restrictedtype` tinyint(1) default NULL,
1222 `rentaldiscount` decimal(28,6) default NULL,
1223 `reservecharge` decimal(28,6) default NULL,
1224 `fine` decimal(28,6) default NULL,
1225 `firstremind` int(11) default NULL,
1226 `chargeperiod` int(11) default NULL,
1227 `accountsent` int(11) default NULL,
1228 `chargename` varchar(100) default NULL,
1229 `maxissueqty` int(4) default NULL,
1230 `issuelength` int(4) default NULL,
1231 `branchcode` varchar(10) NOT NULL default '',
1232 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
1233 KEY `categorycode` (`categorycode`),
1234 KEY `itemtype` (`itemtype`)
1235 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1238 -- Table structure for table `items`
1241 DROP TABLE IF EXISTS `items`;
1242 CREATE TABLE `items` (
1243 `itemnumber` int(11) NOT NULL auto_increment,
1244 `biblionumber` int(11) NOT NULL default 0,
1245 `biblioitemnumber` int(11) NOT NULL default 0,
1246 `barcode` varchar(20) default NULL,
1247 `dateaccessioned` date default NULL,
1248 `booksellerid` mediumtext default NULL,
1249 `homebranch` varchar(10) default NULL,
1250 `price` decimal(8,2) default NULL,
1251 `replacementprice` decimal(8,2) default NULL,
1252 `replacementpricedate` date default NULL,
1253 `datelastborrowed` date default NULL,
1254 `datelastseen` date default NULL,
1255 `stack` tinyint(1) default NULL,
1256 `notforloan` tinyint(1) NOT NULL default 0,
1257 `damaged` tinyint(1) NOT NULL default 0,
1258 `itemlost` tinyint(1) NOT NULL default 0,
1259 `wthdrawn` tinyint(1) NOT NULL default 0,
1260 `itemcallnumber` varchar(255) default NULL,
1261 `issues` smallint(6) default NULL,
1262 `renewals` smallint(6) default NULL,
1263 `reserves` smallint(6) default NULL,
1264 `restricted` tinyint(1) default NULL,
1265 `itemnotes` mediumtext,
1266 `holdingbranch` varchar(10) default NULL,
1267 `paidfor` mediumtext,
1268 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1269 `location` varchar(80) default NULL,
1270 `permanent_location` varchar(80) default NULL,
1271 `onloan` date default NULL,
1272 `cn_source` varchar(10) default NULL,
1273 `cn_sort` varchar(30) default NULL,
1274 `ccode` varchar(10) default NULL,
1275 `materials` varchar(10) default NULL,
1276 `uri` varchar(255) default NULL,
1277 `itype` varchar(10) default NULL,
1278 `more_subfields_xml` longtext default NULL,
1279 `enumchron` varchar(80) default NULL,
1280 `copynumber` varchar(32) default NULL,
1281 PRIMARY KEY (`itemnumber`),
1282 UNIQUE KEY `itembarcodeidx` (`barcode`),
1283 KEY `itembinoidx` (`biblioitemnumber`),
1284 KEY `itembibnoidx` (`biblionumber`),
1285 KEY `homebranch` (`homebranch`),
1286 KEY `holdingbranch` (`holdingbranch`),
1287 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1288 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1289 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1290 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1293 -- Table structure for table `itemtypes`
1296 DROP TABLE IF EXISTS `itemtypes`;
1297 CREATE TABLE `itemtypes` (
1298 `itemtype` varchar(10) NOT NULL default '',
1299 `description` mediumtext,
1300 `renewalsallowed` smallint(6) default NULL,
1301 `rentalcharge` double(16,4) default NULL,
1302 `notforloan` smallint(6) default NULL,
1303 `imageurl` varchar(200) default NULL,
1305 PRIMARY KEY (`itemtype`),
1306 UNIQUE KEY `itemtype` (`itemtype`)
1307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1310 -- Table structure for table `labels_batches`
1313 DROP TABLE IF EXISTS `labels_batches`;
1314 CREATE TABLE `labels_batches` (
1315 `label_id` int(11) NOT NULL auto_increment,
1316 `batch_id` int(10) NOT NULL default '1',
1317 `item_number` int(11) NOT NULL default '0',
1318 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1319 `branch_code` varchar(10) NOT NULL default 'NB',
1320 PRIMARY KEY USING BTREE (`label_id`),
1321 KEY `branch_fk` (`branch_code`),
1322 KEY `item_fk` (`item_number`),
1323 CONSTRAINT `item_fk_constraint` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE,
1324 CONSTRAINT `branch_fk_constraint` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE
1325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1328 -- Table structure for table `labels_layouts`
1331 DROP TABLE IF EXISTS `labels_layouts`;
1332 CREATE TABLE `labels_layouts` (
1333 `layout_id` int(4) NOT NULL auto_increment,
1334 `barcode_type` char(100) NOT NULL default 'CODE39',
1335 `printing_type` char(32) NOT NULL default 'BAR',
1336 `layout_name` char(20) NOT NULL default 'DEFAULT',
1337 `guidebox` int(1) default '0',
1338 `font` char(10) character set utf8 collate utf8_unicode_ci NOT NULL default 'TR',
1339 `font_size` int(4) NOT NULL default '10',
1340 `callnum_split` int(1) default '0',
1341 `text_justify` char(1) character set utf8 collate utf8_unicode_ci NOT NULL default 'L',
1342 `format_string` varchar(210) NOT NULL default 'barcode',
1343 PRIMARY KEY USING BTREE (`layout_id`)
1344 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1347 -- Table structure for table `labels_templates`
1350 DROP TABLE IF EXISTS `labels_templates`;
1351 CREATE TABLE `labels_templates` (
1352 `template_id` int(4) NOT NULL auto_increment,
1353 `profile_id` int(4) default NULL,
1354 `template_code` char(100) NOT NULL default 'DEFAULT TEMPLATE',
1355 `template_desc` char(100) NOT NULL default 'Default description',
1356 `page_width` float NOT NULL default '0',
1357 `page_height` float NOT NULL default '0',
1358 `label_width` float NOT NULL default '0',
1359 `label_height` float NOT NULL default '0',
1360 `top_text_margin` float NOT NULL default '0',
1361 `left_text_margin` float NOT NULL default '0',
1362 `top_margin` float NOT NULL default '0',
1363 `left_margin` float NOT NULL default '0',
1364 `cols` int(2) NOT NULL default '0',
1365 `rows` int(2) NOT NULL default '0',
1366 `col_gap` float NOT NULL default '0',
1367 `row_gap` float NOT NULL default '0',
1368 `units` char(20) NOT NULL default 'POINT',
1369 PRIMARY KEY (`template_id`),
1370 KEY `template_profile_fk_constraint` (`profile_id`)
1371 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1374 -- Table structure for table `letter`
1377 DROP TABLE IF EXISTS `letter`;
1378 CREATE TABLE `letter` (
1379 `module` varchar(20) NOT NULL default '',
1380 `code` varchar(20) NOT NULL default '',
1381 `name` varchar(100) NOT NULL default '',
1382 `title` varchar(200) NOT NULL default '',
1384 PRIMARY KEY (`module`,`code`)
1385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1388 -- Table structure for table `marc_subfield_structure`
1391 DROP TABLE IF EXISTS `marc_subfield_structure`;
1392 CREATE TABLE `marc_subfield_structure` (
1393 `tagfield` varchar(3) NOT NULL default '',
1394 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1395 `liblibrarian` varchar(255) NOT NULL default '',
1396 `libopac` varchar(255) NOT NULL default '',
1397 `repeatable` tinyint(4) NOT NULL default 0,
1398 `mandatory` tinyint(4) NOT NULL default 0,
1399 `kohafield` varchar(40) default NULL,
1400 `tab` tinyint(1) default NULL,
1401 `authorised_value` varchar(20) default NULL,
1402 `authtypecode` varchar(20) default NULL,
1403 `value_builder` varchar(80) default NULL,
1404 `isurl` tinyint(1) default NULL,
1405 `hidden` tinyint(1) default NULL,
1406 `frameworkcode` varchar(4) NOT NULL default '',
1407 `seealso` varchar(1100) default NULL,
1408 `link` varchar(80) default NULL,
1409 `defaultvalue` text default NULL,
1410 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1411 KEY `kohafield_2` (`kohafield`),
1412 KEY `tab` (`frameworkcode`,`tab`),
1413 KEY `kohafield` (`frameworkcode`,`kohafield`)
1414 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1417 -- Table structure for table `marc_tag_structure`
1420 DROP TABLE IF EXISTS `marc_tag_structure`;
1421 CREATE TABLE `marc_tag_structure` (
1422 `tagfield` varchar(3) NOT NULL default '',
1423 `liblibrarian` varchar(255) NOT NULL default '',
1424 `libopac` varchar(255) NOT NULL default '',
1425 `repeatable` tinyint(4) NOT NULL default 0,
1426 `mandatory` tinyint(4) NOT NULL default 0,
1427 `authorised_value` varchar(10) default NULL,
1428 `frameworkcode` varchar(4) NOT NULL default '',
1429 PRIMARY KEY (`frameworkcode`,`tagfield`)
1430 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1433 -- Table structure for table `marc_matchers`
1436 DROP TABLE IF EXISTS `marc_matchers`;
1437 CREATE TABLE `marc_matchers` (
1438 `matcher_id` int(11) NOT NULL auto_increment,
1439 `code` varchar(10) NOT NULL default '',
1440 `description` varchar(255) NOT NULL default '',
1441 `record_type` varchar(10) NOT NULL default 'biblio',
1442 `threshold` int(11) NOT NULL default 0,
1443 PRIMARY KEY (`matcher_id`),
1444 KEY `code` (`code`),
1445 KEY `record_type` (`record_type`)
1446 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1449 -- Table structure for table `matchpoints`
1451 DROP TABLE IF EXISTS `matchpoints`;
1452 CREATE TABLE `matchpoints` (
1453 `matcher_id` int(11) NOT NULL,
1454 `matchpoint_id` int(11) NOT NULL auto_increment,
1455 `search_index` varchar(30) NOT NULL default '',
1456 `score` int(11) NOT NULL default 0,
1457 PRIMARY KEY (`matchpoint_id`),
1458 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1459 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1460 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1464 -- Table structure for table `matchpoint_components`
1466 DROP TABLE IF EXISTS `matchpoint_components`;
1467 CREATE TABLE `matchpoint_components` (
1468 `matchpoint_id` int(11) NOT NULL,
1469 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1470 sequence int(11) NOT NULL default 0,
1471 tag varchar(3) NOT NULL default '',
1472 subfields varchar(40) NOT NULL default '',
1473 offset int(4) NOT NULL default 0,
1474 length int(4) NOT NULL default 0,
1475 PRIMARY KEY (`matchpoint_component_id`),
1476 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1477 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1478 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1479 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1482 -- Table structure for table `matcher_component_norms`
1484 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1485 CREATE TABLE `matchpoint_component_norms` (
1486 `matchpoint_component_id` int(11) NOT NULL,
1487 `sequence` int(11) NOT NULL default 0,
1488 `norm_routine` varchar(50) NOT NULL default '',
1489 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1490 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1491 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1492 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1495 -- Table structure for table `matcher_matchpoints`
1497 DROP TABLE IF EXISTS `matcher_matchpoints`;
1498 CREATE TABLE `matcher_matchpoints` (
1499 `matcher_id` int(11) NOT NULL,
1500 `matchpoint_id` int(11) NOT NULL,
1501 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1502 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1503 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1504 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1505 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1508 -- Table structure for table `matchchecks`
1510 DROP TABLE IF EXISTS `matchchecks`;
1511 CREATE TABLE `matchchecks` (
1512 `matcher_id` int(11) NOT NULL,
1513 `matchcheck_id` int(11) NOT NULL auto_increment,
1514 `source_matchpoint_id` int(11) NOT NULL,
1515 `target_matchpoint_id` int(11) NOT NULL,
1516 PRIMARY KEY (`matchcheck_id`),
1517 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1518 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1519 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1520 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1521 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1522 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1523 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1526 -- Table structure for table `notifys`
1529 DROP TABLE IF EXISTS `notifys`;
1530 CREATE TABLE `notifys` (
1531 `notify_id` int(11) NOT NULL default 0,
1532 `borrowernumber` int(11) NOT NULL default 0,
1533 `itemnumber` int(11) NOT NULL default 0,
1534 `notify_date` date default NULL,
1535 `notify_send_date` date default NULL,
1536 `notify_level` int(1) NOT NULL default 0,
1537 `method` varchar(20) NOT NULL default ''
1538 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1541 -- Table structure for table `nozebra`
1544 DROP TABLE IF EXISTS `nozebra`;
1545 CREATE TABLE `nozebra` (
1546 `server` varchar(20) NOT NULL,
1547 `indexname` varchar(40) NOT NULL,
1548 `value` varchar(250) NOT NULL,
1549 `biblionumbers` longtext NOT NULL,
1550 KEY `indexname` (`server`,`indexname`),
1551 KEY `value` (`server`,`value`))
1552 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1555 -- Table structure for table `old_issues`
1558 DROP TABLE IF EXISTS `old_issues`;
1559 CREATE TABLE `old_issues` (
1560 `borrowernumber` int(11) default NULL,
1561 `itemnumber` int(11) default NULL,
1562 `date_due` date default NULL,
1563 `branchcode` varchar(10) default NULL,
1564 `issuingbranch` varchar(18) default NULL,
1565 `returndate` date default NULL,
1566 `lastreneweddate` date default NULL,
1567 `return` varchar(4) default NULL,
1568 `renewals` tinyint(4) default NULL,
1569 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1570 `issuedate` date default NULL,
1571 KEY `old_issuesborridx` (`borrowernumber`),
1572 KEY `old_issuesitemidx` (`itemnumber`),
1573 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1574 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1575 ON DELETE SET NULL ON UPDATE SET NULL,
1576 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1577 ON DELETE SET NULL ON UPDATE SET NULL
1578 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1581 -- Table structure for table `old_reserves`
1583 DROP TABLE IF EXISTS `old_reserves`;
1584 CREATE TABLE `old_reserves` (
1585 `borrowernumber` int(11) default NULL,
1586 `reservedate` date default NULL,
1587 `biblionumber` int(11) default NULL,
1588 `constrainttype` varchar(1) default NULL,
1589 `branchcode` varchar(10) default NULL,
1590 `notificationdate` date default NULL,
1591 `reminderdate` date default NULL,
1592 `cancellationdate` date default NULL,
1593 `reservenotes` mediumtext,
1594 `priority` smallint(6) default NULL,
1595 `found` varchar(1) default NULL,
1596 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1597 `itemnumber` int(11) default NULL,
1598 `waitingdate` date default NULL,
1599 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1600 KEY `old_reserves_biblionumber` (`biblionumber`),
1601 KEY `old_reserves_itemnumber` (`itemnumber`),
1602 KEY `old_reserves_branchcode` (`branchcode`),
1603 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1604 ON DELETE SET NULL ON UPDATE SET NULL,
1605 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1606 ON DELETE SET NULL ON UPDATE SET NULL,
1607 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1608 ON DELETE SET NULL ON UPDATE SET NULL
1609 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1612 -- Table structure for table `opac_news`
1615 DROP TABLE IF EXISTS `opac_news`;
1616 CREATE TABLE `opac_news` (
1617 `idnew` int(10) unsigned NOT NULL auto_increment,
1618 `title` varchar(250) NOT NULL default '',
1619 `new` text NOT NULL,
1620 `lang` varchar(25) NOT NULL default '',
1621 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1622 `expirationdate` date default NULL,
1623 `number` int(11) default NULL,
1624 PRIMARY KEY (`idnew`)
1625 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1628 -- Table structure for table `overduerules`
1631 DROP TABLE IF EXISTS `overduerules`;
1632 CREATE TABLE `overduerules` (
1633 `branchcode` varchar(10) NOT NULL default '',
1634 `categorycode` varchar(10) NOT NULL default '',
1635 `delay1` int(4) default 0,
1636 `letter1` varchar(20) default NULL,
1637 `debarred1` varchar(1) default 0,
1638 `delay2` int(4) default 0,
1639 `debarred2` varchar(1) default 0,
1640 `letter2` varchar(20) default NULL,
1641 `delay3` int(4) default 0,
1642 `letter3` varchar(20) default NULL,
1643 `debarred3` int(1) default 0,
1644 PRIMARY KEY (`branchcode`,`categorycode`)
1645 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1648 -- Table structure for table `patroncards`
1651 DROP TABLE IF EXISTS `patroncards`;
1652 CREATE TABLE `patroncards` (
1653 `cardid` int(11) NOT NULL auto_increment,
1654 `batch_id` varchar(10) NOT NULL default '1',
1655 `borrowernumber` int(11) NOT NULL,
1656 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1657 PRIMARY KEY (`cardid`),
1658 KEY `patroncards_ibfk_1` (`borrowernumber`),
1659 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1660 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1663 -- Table structure for table `patronimage`
1666 DROP TABLE IF EXISTS `patronimage`;
1667 CREATE TABLE `patronimage` (
1668 `cardnumber` varchar(16) NOT NULL,
1669 `mimetype` varchar(15) NOT NULL,
1670 `imagefile` mediumblob NOT NULL,
1671 PRIMARY KEY (`cardnumber`),
1672 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1673 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1676 -- Table structure for table `printers`
1679 DROP TABLE IF EXISTS `printers`;
1680 CREATE TABLE `printers` (
1681 `printername` varchar(40) NOT NULL default '',
1682 `printqueue` varchar(20) default NULL,
1683 `printtype` varchar(20) default NULL,
1684 PRIMARY KEY (`printername`)
1685 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1688 -- Table structure for table `printers_profile`
1691 DROP TABLE IF EXISTS `printers_profile`;
1692 CREATE TABLE `printers_profile` (
1693 `profile_id` int(4) NOT NULL auto_increment,
1694 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1695 `template_id` int(4) NOT NULL default '0',
1696 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1697 `offset_horz` float NOT NULL default '0',
1698 `offset_vert` float NOT NULL default '0',
1699 `creep_horz` float NOT NULL default '0',
1700 `creep_vert` float NOT NULL default '0',
1701 `units` char(20) NOT NULL default 'POINT',
1702 PRIMARY KEY (`profile_id`),
1703 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`)
1704 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1707 -- Table structure for table `repeatable_holidays`
1710 DROP TABLE IF EXISTS `repeatable_holidays`;
1711 CREATE TABLE `repeatable_holidays` (
1712 `id` int(11) NOT NULL auto_increment,
1713 `branchcode` varchar(10) NOT NULL default '',
1714 `weekday` smallint(6) default NULL,
1715 `day` smallint(6) default NULL,
1716 `month` smallint(6) default NULL,
1717 `title` varchar(50) NOT NULL default '',
1718 `description` text NOT NULL,
1720 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1723 -- Table structure for table `reports_dictionary`
1726 DROP TABLE IF EXISTS `reports_dictionary`;
1727 CREATE TABLE reports_dictionary (
1728 `id` int(11) NOT NULL auto_increment,
1729 `name` varchar(255) default NULL,
1731 `date_created` datetime default NULL,
1732 `date_modified` datetime default NULL,
1734 `area` int(11) default NULL,
1736 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1739 -- Table structure for table `reserveconstraints`
1742 DROP TABLE IF EXISTS `reserveconstraints`;
1743 CREATE TABLE `reserveconstraints` (
1744 `borrowernumber` int(11) NOT NULL default 0,
1745 `reservedate` date default NULL,
1746 `biblionumber` int(11) NOT NULL default 0,
1747 `biblioitemnumber` int(11) default NULL,
1748 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1749 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1752 -- Table structure for table `reserves`
1755 DROP TABLE IF EXISTS `reserves`;
1756 CREATE TABLE `reserves` (
1757 `borrowernumber` int(11) NOT NULL default 0,
1758 `reservedate` date default NULL,
1759 `biblionumber` int(11) NOT NULL default 0,
1760 `constrainttype` varchar(1) default NULL,
1761 `branchcode` varchar(10) default NULL,
1762 `notificationdate` date default NULL,
1763 `reminderdate` date default NULL,
1764 `cancellationdate` date default NULL,
1765 `reservenotes` mediumtext,
1766 `priority` smallint(6) default NULL,
1767 `found` varchar(1) default NULL,
1768 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1769 `itemnumber` int(11) default NULL,
1770 `waitingdate` date default NULL,
1771 KEY `borrowernumber` (`borrowernumber`),
1772 KEY `biblionumber` (`biblionumber`),
1773 KEY `itemnumber` (`itemnumber`),
1774 KEY `branchcode` (`branchcode`),
1775 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1776 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1777 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1778 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1779 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1782 -- Table structure for table `reviews`
1785 DROP TABLE IF EXISTS `reviews`;
1786 CREATE TABLE `reviews` (
1787 `reviewid` int(11) NOT NULL auto_increment,
1788 `borrowernumber` int(11) default NULL,
1789 `biblionumber` int(11) default NULL,
1791 `approved` tinyint(4) default NULL,
1792 `datereviewed` datetime default NULL,
1793 PRIMARY KEY (`reviewid`)
1794 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1797 -- Table structure for table `roadtype`
1800 DROP TABLE IF EXISTS `roadtype`;
1801 CREATE TABLE `roadtype` (
1802 `roadtypeid` int(11) NOT NULL auto_increment,
1803 `road_type` varchar(100) NOT NULL default '',
1804 PRIMARY KEY (`roadtypeid`)
1805 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1808 -- Table structure for table `saved_sql`
1811 DROP TABLE IF EXISTS `saved_sql`;
1812 CREATE TABLE saved_sql (
1813 `id` int(11) NOT NULL auto_increment,
1814 `borrowernumber` int(11) default NULL,
1815 `date_created` datetime default NULL,
1816 `last_modified` datetime default NULL,
1818 `last_run` datetime default NULL,
1819 `report_name` varchar(255) default NULL,
1820 `type` varchar(255) default NULL,
1823 KEY boridx (`borrowernumber`)
1824 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1828 -- Table structure for `saved_reports`
1831 DROP TABLE IF EXISTS `saved_reports`;
1832 CREATE TABLE saved_reports (
1833 `id` int(11) NOT NULL auto_increment,
1834 `report_id` int(11) default NULL,
1836 `date_run` datetime default NULL,
1838 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1842 -- Table structure for table `serial`
1845 DROP TABLE IF EXISTS `serial`;
1846 CREATE TABLE `serial` (
1847 `serialid` int(11) NOT NULL auto_increment,
1848 `biblionumber` varchar(100) NOT NULL default '',
1849 `subscriptionid` varchar(100) NOT NULL default '',
1850 `serialseq` varchar(100) NOT NULL default '',
1851 `status` tinyint(4) NOT NULL default 0,
1852 `planneddate` date default NULL,
1854 `publisheddate` date default NULL,
1855 `itemnumber` text default NULL,
1856 `claimdate` date default NULL,
1857 `routingnotes` text,
1858 PRIMARY KEY (`serialid`)
1859 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1862 -- Table structure for table `sessions`
1865 DROP TABLE IF EXISTS sessions;
1866 CREATE TABLE sessions (
1867 `id` varchar(32) NOT NULL,
1868 `a_session` text NOT NULL,
1870 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1873 -- Table structure for table `special_holidays`
1876 DROP TABLE IF EXISTS `special_holidays`;
1877 CREATE TABLE `special_holidays` (
1878 `id` int(11) NOT NULL auto_increment,
1879 `branchcode` varchar(10) NOT NULL default '',
1880 `day` smallint(6) NOT NULL default 0,
1881 `month` smallint(6) NOT NULL default 0,
1882 `year` smallint(6) NOT NULL default 0,
1883 `isexception` smallint(1) NOT NULL default 1,
1884 `title` varchar(50) NOT NULL default '',
1885 `description` text NOT NULL,
1887 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1890 -- Table structure for table `statistics`
1893 DROP TABLE IF EXISTS `statistics`;
1894 CREATE TABLE `statistics` (
1895 `datetime` datetime default NULL,
1896 `branch` varchar(10) default NULL,
1897 `proccode` varchar(4) default NULL,
1898 `value` double(16,4) default NULL,
1899 `type` varchar(16) default NULL,
1901 `usercode` varchar(10) default NULL,
1902 `itemnumber` int(11) default NULL,
1903 `itemtype` varchar(10) default NULL,
1904 `borrowernumber` int(11) default NULL,
1905 `associatedborrower` int(11) default NULL,
1906 KEY `timeidx` (`datetime`)
1907 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1910 -- Table structure for table `stopwords`
1913 DROP TABLE IF EXISTS `stopwords`;
1914 CREATE TABLE `stopwords` (
1915 `word` varchar(255) default NULL
1916 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1919 -- Table structure for table `subscription`
1922 DROP TABLE IF EXISTS `subscription`;
1923 CREATE TABLE `subscription` (
1924 `biblionumber` int(11) NOT NULL default 0,
1925 `subscriptionid` int(11) NOT NULL auto_increment,
1926 `librarian` varchar(100) default '',
1927 `startdate` date default NULL,
1928 `aqbooksellerid` int(11) default 0,
1929 `cost` int(11) default 0,
1930 `aqbudgetid` int(11) default 0,
1931 `weeklength` int(11) default 0,
1932 `monthlength` int(11) default 0,
1933 `numberlength` int(11) default 0,
1934 `periodicity` tinyint(4) default 0,
1935 `dow` varchar(100) default '',
1936 `numberingmethod` varchar(100) default '',
1938 `status` varchar(100) NOT NULL default '',
1939 `add1` int(11) default 0,
1940 `every1` int(11) default 0,
1941 `whenmorethan1` int(11) default 0,
1942 `setto1` int(11) default NULL,
1943 `lastvalue1` int(11) default NULL,
1944 `add2` int(11) default 0,
1945 `every2` int(11) default 0,
1946 `whenmorethan2` int(11) default 0,
1947 `setto2` int(11) default NULL,
1948 `lastvalue2` int(11) default NULL,
1949 `add3` int(11) default 0,
1950 `every3` int(11) default 0,
1951 `innerloop1` int(11) default 0,
1952 `innerloop2` int(11) default 0,
1953 `innerloop3` int(11) default 0,
1954 `whenmorethan3` int(11) default 0,
1955 `setto3` int(11) default NULL,
1956 `lastvalue3` int(11) default NULL,
1957 `issuesatonce` tinyint(3) NOT NULL default 1,
1958 `firstacquidate` date default NULL,
1959 `manualhistory` tinyint(1) NOT NULL default 0,
1960 `irregularity` text,
1961 `letter` varchar(20) default NULL,
1962 `numberpattern` tinyint(3) default 0,
1963 `distributedto` text,
1964 `internalnotes` longtext,
1966 `location` varchar(80) NULL default '',
1967 `branchcode` varchar(10) NOT NULL default '',
1968 `hemisphere` tinyint(3) default 0,
1969 `lastbranch` varchar(10),
1970 `serialsadditems` tinyint(1) NOT NULL default '0',
1971 `staffdisplaycount` VARCHAR(10) NULL,
1972 `opacdisplaycount` VARCHAR(10) NULL,
1973 `graceperiod` int(11) NOT NULL default '0',
1974 PRIMARY KEY (`subscriptionid`)
1975 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1978 -- Table structure for table `subscriptionhistory`
1981 DROP TABLE IF EXISTS `subscriptionhistory`;
1982 CREATE TABLE `subscriptionhistory` (
1983 `biblionumber` int(11) NOT NULL default 0,
1984 `subscriptionid` int(11) NOT NULL default 0,
1985 `histstartdate` date default NULL,
1986 `enddate` date default NULL,
1987 `missinglist` longtext NOT NULL,
1988 `recievedlist` longtext NOT NULL,
1989 `opacnote` varchar(150) NOT NULL default '',
1990 `librariannote` varchar(150) NOT NULL default '',
1991 PRIMARY KEY (`subscriptionid`),
1992 KEY `biblionumber` (`biblionumber`)
1993 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1996 -- Table structure for table `subscriptionroutinglist`
1999 DROP TABLE IF EXISTS `subscriptionroutinglist`;
2000 CREATE TABLE `subscriptionroutinglist` (
2001 `routingid` int(11) NOT NULL auto_increment,
2002 `borrowernumber` int(11) default NULL,
2003 `ranking` int(11) default NULL,
2004 `subscriptionid` int(11) default NULL,
2005 PRIMARY KEY (`routingid`)
2006 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2009 -- Table structure for table `suggestions`
2012 DROP TABLE IF EXISTS `suggestions`;
2013 CREATE TABLE `suggestions` (
2014 `suggestionid` int(8) NOT NULL auto_increment,
2015 `suggestedby` int(11) NOT NULL default 0,
2016 `managedby` int(11) default NULL,
2017 `STATUS` varchar(10) NOT NULL default '',
2019 `author` varchar(80) default NULL,
2020 `title` varchar(80) default NULL,
2021 `copyrightdate` smallint(6) default NULL,
2022 `publishercode` varchar(255) default NULL,
2023 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2024 `volumedesc` varchar(255) default NULL,
2025 `publicationyear` smallint(6) default 0,
2026 `place` varchar(255) default NULL,
2027 `isbn` varchar(30) default NULL,
2028 `mailoverseeing` smallint(1) default 0,
2029 `biblionumber` int(11) default NULL,
2031 PRIMARY KEY (`suggestionid`),
2032 KEY `suggestedby` (`suggestedby`),
2033 KEY `managedby` (`managedby`)
2034 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2037 -- Table structure for table `systempreferences`
2040 DROP TABLE IF EXISTS `systempreferences`;
2041 CREATE TABLE `systempreferences` (
2042 `variable` varchar(50) NOT NULL default '',
2044 `options` mediumtext,
2046 `type` varchar(20) default NULL,
2047 PRIMARY KEY (`variable`)
2048 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2051 -- Table structure for table `tags`
2054 DROP TABLE IF EXISTS `tags`;
2055 CREATE TABLE `tags` (
2056 `entry` varchar(255) NOT NULL default '',
2057 `weight` bigint(20) NOT NULL default 0,
2058 PRIMARY KEY (`entry`)
2059 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2062 -- Table structure for table `tags_all`
2065 DROP TABLE IF EXISTS `tags_all`;
2066 CREATE TABLE `tags_all` (
2067 `tag_id` int(11) NOT NULL auto_increment,
2068 `borrowernumber` int(11) NOT NULL,
2069 `biblionumber` int(11) NOT NULL,
2070 `term` varchar(255) NOT NULL,
2071 `language` int(4) default NULL,
2072 `date_created` datetime NOT NULL,
2073 PRIMARY KEY (`tag_id`),
2074 KEY `tags_borrowers_fk_1` (`borrowernumber`),
2075 KEY `tags_biblionumber_fk_1` (`biblionumber`),
2076 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
2077 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2078 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2079 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2080 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2083 -- Table structure for table `tags_approval`
2086 DROP TABLE IF EXISTS `tags_approval`;
2087 CREATE TABLE `tags_approval` (
2088 `term` varchar(255) NOT NULL,
2089 `approved` int(1) NOT NULL default '0',
2090 `date_approved` datetime default NULL,
2091 `approved_by` int(11) default NULL,
2092 `weight_total` int(9) NOT NULL default '1',
2093 PRIMARY KEY (`term`),
2094 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
2095 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
2096 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2097 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2100 -- Table structure for table `tags_index`
2103 DROP TABLE IF EXISTS `tags_index`;
2104 CREATE TABLE `tags_index` (
2105 `term` varchar(255) NOT NULL,
2106 `biblionumber` int(11) NOT NULL,
2107 `weight` int(9) NOT NULL default '1',
2108 PRIMARY KEY (`term`,`biblionumber`),
2109 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
2110 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
2111 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
2112 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
2113 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2117 -- Table structure for table `userflags`
2120 DROP TABLE IF EXISTS `userflags`;
2121 CREATE TABLE `userflags` (
2122 `bit` int(11) NOT NULL default 0,
2123 `flag` varchar(30) default NULL,
2124 `flagdesc` varchar(255) default NULL,
2125 `defaulton` int(11) default NULL,
2127 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2130 -- Table structure for table `virtualshelves`
2133 DROP TABLE IF EXISTS `virtualshelves`;
2134 CREATE TABLE `virtualshelves` (
2135 `shelfnumber` int(11) NOT NULL auto_increment,
2136 `shelfname` varchar(255) default NULL,
2137 `owner` varchar(80) default NULL,
2138 `category` varchar(1) default NULL,
2139 `sortfield` varchar(16) default NULL,
2140 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2141 PRIMARY KEY (`shelfnumber`)
2142 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2145 -- Table structure for table `virtualshelfcontents`
2148 DROP TABLE IF EXISTS `virtualshelfcontents`;
2149 CREATE TABLE `virtualshelfcontents` (
2150 `shelfnumber` int(11) NOT NULL default 0,
2151 `biblionumber` int(11) NOT NULL default 0,
2152 `flags` int(11) default NULL,
2153 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2154 KEY `shelfnumber` (`shelfnumber`),
2155 KEY `biblionumber` (`biblionumber`),
2156 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2157 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
2158 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2161 -- Table structure for table `z3950servers`
2164 DROP TABLE IF EXISTS `z3950servers`;
2165 CREATE TABLE `z3950servers` (
2166 `host` varchar(255) default NULL,
2167 `port` int(11) default NULL,
2168 `db` varchar(255) default NULL,
2169 `userid` varchar(255) default NULL,
2170 `password` varchar(255) default NULL,
2172 `id` int(11) NOT NULL auto_increment,
2173 `checked` smallint(6) default NULL,
2174 `rank` int(11) default NULL,
2175 `syntax` varchar(80) default NULL,
2177 `position` enum('primary','secondary','') NOT NULL default 'primary',
2178 `type` enum('zed','opensearch') NOT NULL default 'zed',
2179 `encoding` text default NULL,
2180 `description` text NOT NULL,
2182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2185 -- Table structure for table `zebraqueue`
2188 DROP TABLE IF EXISTS `zebraqueue`;
2189 CREATE TABLE `zebraqueue` (
2190 `id` int(11) NOT NULL auto_increment,
2191 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2192 `operation` char(20) NOT NULL default '',
2193 `server` char(20) NOT NULL default '',
2194 `done` int(11) NOT NULL default '0',
2195 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2197 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2200 DROP TABLE IF EXISTS `services_throttle`;
2201 CREATE TABLE `services_throttle` (
2202 `service_type` varchar(10) NOT NULL default '',
2203 `service_count` varchar(45) default NULL,
2204 PRIMARY KEY (`service_type`)
2205 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2207 -- http://www.w3.org/International/articles/language-tags/
2210 DROP TABLE IF EXISTS language_subtag_registry;
2211 CREATE TABLE language_subtag_registry (
2213 type varchar(25), -- language-script-region-variant-extension-privateuse
2214 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2216 id int(11) NOT NULL auto_increment,
2218 KEY `subtag` (`subtag`)
2219 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2221 -- TODO: add suppress_scripts
2222 -- this maps three letter codes defined in iso639.2 back to their
2223 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2224 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2225 CREATE TABLE language_rfc4646_to_iso639 (
2226 rfc4646_subtag varchar(25),
2227 iso639_2_code varchar(25),
2228 id int(11) NOT NULL auto_increment,
2230 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2231 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2233 DROP TABLE IF EXISTS language_descriptions;
2234 CREATE TABLE language_descriptions (
2238 description varchar(255),
2239 id int(11) NOT NULL auto_increment,
2242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2244 -- bi-directional support, keyed by script subcode
2245 DROP TABLE IF EXISTS language_script_bidi;
2246 CREATE TABLE language_script_bidi (
2247 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2248 bidi varchar(3), -- rtl ltr
2249 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2250 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2252 -- TODO: need to map language subtags to script subtags for detection
2253 -- of bidi when script is not specified (like ar, he)
2254 DROP TABLE IF EXISTS language_script_mapping;
2255 CREATE TABLE language_script_mapping (
2256 language_subtag varchar(25),
2257 script_subtag varchar(25),
2258 KEY `language_subtag` (`language_subtag`)
2259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2261 DROP TABLE IF EXISTS `permissions`;
2262 CREATE TABLE `permissions` (
2263 `module_bit` int(11) NOT NULL DEFAULT 0,
2264 `code` varchar(64) DEFAULT NULL,
2265 `description` varchar(255) DEFAULT NULL,
2266 PRIMARY KEY (`module_bit`, `code`),
2267 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2268 ON DELETE CASCADE ON UPDATE CASCADE
2269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2271 DROP TABLE IF EXISTS `serialitems`;
2272 CREATE TABLE `serialitems` (
2273 `itemnumber` int(11) NOT NULL,
2274 `serialid` int(11) NOT NULL,
2275 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2276 KEY `serialitems_sfk_1` (`serialid`),
2277 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
2278 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2280 DROP TABLE IF EXISTS `user_permissions`;
2281 CREATE TABLE `user_permissions` (
2282 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2283 `module_bit` int(11) NOT NULL DEFAULT 0,
2284 `code` varchar(64) DEFAULT NULL,
2285 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2286 ON DELETE CASCADE ON UPDATE CASCADE,
2287 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2288 ON DELETE CASCADE ON UPDATE CASCADE
2289 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2292 -- Table structure for table `tmp_holdsqueue`
2295 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2296 CREATE TABLE `tmp_holdsqueue` (
2297 `biblionumber` int(11) default NULL,
2298 `itemnumber` int(11) default NULL,
2299 `barcode` varchar(20) default NULL,
2300 `surname` mediumtext NOT NULL,
2303 `borrowernumber` int(11) NOT NULL,
2304 `cardnumber` varchar(16) default NULL,
2305 `reservedate` date default NULL,
2307 `itemcallnumber` varchar(255) default NULL,
2308 `holdingbranch` varchar(10) default NULL,
2309 `pickbranch` varchar(10) default NULL,
2311 `item_level_request` tinyint(4) NOT NULL default 0
2312 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2315 -- Table structure for table `message_queue`
2318 DROP TABLE IF EXISTS `message_queue`;
2319 CREATE TABLE `message_queue` (
2320 `message_id` int(11) NOT NULL auto_increment,
2321 `borrowernumber` int(11) default NULL,
2324 `metadata` text DEFAULT NULL,
2325 `letter_code` varchar(64) DEFAULT NULL,
2326 `message_transport_type` varchar(20) NOT NULL,
2327 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2328 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2329 `to_address` mediumtext,
2330 `from_address` mediumtext,
2331 `content_type` text,
2332 KEY `message_id` (`message_id`),
2333 KEY `borrowernumber` (`borrowernumber`),
2334 KEY `message_transport_type` (`message_transport_type`),
2335 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2336 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2337 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2340 -- Table structure for table `message_transport_types`
2343 DROP TABLE IF EXISTS `message_transport_types`;
2344 CREATE TABLE `message_transport_types` (
2345 `message_transport_type` varchar(20) NOT NULL,
2346 PRIMARY KEY (`message_transport_type`)
2347 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2350 -- Table structure for table `message_attributes`
2353 DROP TABLE IF EXISTS `message_attributes`;
2354 CREATE TABLE `message_attributes` (
2355 `message_attribute_id` int(11) NOT NULL auto_increment,
2356 `message_name` varchar(20) NOT NULL default '',
2357 `takes_days` tinyint(1) NOT NULL default '0',
2358 PRIMARY KEY (`message_attribute_id`),
2359 UNIQUE KEY `message_name` (`message_name`)
2360 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2363 -- Table structure for table `message_transports`
2366 DROP TABLE IF EXISTS `message_transports`;
2367 CREATE TABLE `message_transports` (
2368 `message_attribute_id` int(11) NOT NULL,
2369 `message_transport_type` varchar(20) NOT NULL,
2370 `is_digest` tinyint(1) NOT NULL default '0',
2371 `letter_module` varchar(20) NOT NULL default '',
2372 `letter_code` varchar(20) NOT NULL default '',
2373 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2374 KEY `message_transport_type` (`message_transport_type`),
2375 KEY `letter_module` (`letter_module`,`letter_code`),
2376 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2377 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2378 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2382 -- Table structure for table `borrower_message_preferences`
2385 DROP TABLE IF EXISTS `borrower_message_preferences`;
2386 CREATE TABLE `borrower_message_preferences` (
2387 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2388 `borrowernumber` int(11) default NULL,
2389 `categorycode` varchar(10) default NULL,
2390 `message_attribute_id` int(11) default '0',
2391 `days_in_advance` int(11) default '0',
2392 `wants_digest` tinyint(1) NOT NULL default '0',
2393 PRIMARY KEY (`borrower_message_preference_id`),
2394 KEY `borrowernumber` (`borrowernumber`),
2395 KEY `categorycode` (`categorycode`),
2396 KEY `message_attribute_id` (`message_attribute_id`),
2397 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2398 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2399 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2400 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2403 -- Table structure for table `borrower_message_transport_preferences`
2406 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2407 CREATE TABLE `borrower_message_transport_preferences` (
2408 `borrower_message_preference_id` int(11) NOT NULL default '0',
2409 `message_transport_type` varchar(20) NOT NULL default '0',
2410 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2411 KEY `message_transport_type` (`message_transport_type`),
2412 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,
2413 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
2414 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2417 -- Table structure for the table branch_transfer_limits
2420 DROP TABLE IF EXISTS `branch_transfer_limits`;
2421 CREATE TABLE branch_transfer_limits (
2422 limitId int(8) NOT NULL auto_increment,
2423 toBranch varchar(10) NOT NULL,
2424 fromBranch varchar(10) NOT NULL,
2425 itemtype varchar(10) NULL,
2426 ccode varchar(10) NULL,
2427 PRIMARY KEY (limitId)
2428 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2431 -- Table structure for table `item_circulation_alert_preferences`
2434 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2435 CREATE TABLE `item_circulation_alert_preferences` (
2436 `id` int(11) NOT NULL auto_increment,
2437 `branchcode` varchar(10) NOT NULL,
2438 `categorycode` varchar(10) NOT NULL,
2439 `item_type` varchar(10) NOT NULL,
2440 `notification` varchar(16) NOT NULL,
2442 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2443 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2446 -- Table structure for table `messages`
2449 CREATE TABLE `messages` (
2450 `message_id` int(11) NOT NULL auto_increment,
2451 `borrowernumber` int(11) NOT NULL,
2452 `branchcode` varchar(4) default NULL,
2453 `message_type` varchar(1) NOT NULL,
2454 `message` text NOT NULL,
2455 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2456 PRIMARY KEY (`message_id`)
2457 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2459 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2460 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2461 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2462 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2463 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2464 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2465 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2466 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;