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 `auth_header`
22 DROP TABLE IF EXISTS `auth_header`;
23 CREATE TABLE `auth_header` (
24 `authid` bigint(20) unsigned NOT NULL auto_increment,
25 `authtypecode` varchar(10) NOT NULL default '',
26 `datecreated` date default NULL,
27 `datemodified` date default NULL,
28 `origincode` varchar(20) default NULL,
29 `authtrees` mediumtext,
31 `linkid` bigint(20) default NULL,
32 `marcxml` longtext NOT NULL,
33 PRIMARY KEY (`authid`),
34 KEY `origincode` (`origincode`)
35 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
38 -- Table structure for table `auth_subfield_structure`
41 DROP TABLE IF EXISTS `auth_subfield_structure`;
42 CREATE TABLE `auth_subfield_structure` (
43 `authtypecode` varchar(10) NOT NULL default '',
44 `tagfield` varchar(3) NOT NULL default '',
45 `tagsubfield` varchar(1) NOT NULL default '',
46 `liblibrarian` varchar(255) NOT NULL default '',
47 `libopac` varchar(255) NOT NULL default '',
48 `repeatable` tinyint(4) NOT NULL default 0,
49 `mandatory` tinyint(4) NOT NULL default 0,
50 `tab` tinyint(1) default NULL,
51 `authorised_value` varchar(10) default NULL,
52 `value_builder` varchar(80) default NULL,
53 `seealso` varchar(255) default NULL,
54 `isurl` tinyint(1) default NULL,
55 `hidden` tinyint(3) NOT NULL default 0,
56 `linkid` tinyint(1) NOT NULL default 0,
57 `kohafield` varchar(45) NULL default '',
58 `frameworkcode` varchar(10) NOT NULL default '',
59 PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
60 KEY `tab` (`authtypecode`,`tab`)
61 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
64 -- Table structure for table `auth_tag_structure`
67 DROP TABLE IF EXISTS `auth_tag_structure`;
68 CREATE TABLE `auth_tag_structure` (
69 `authtypecode` varchar(10) NOT NULL default '',
70 `tagfield` varchar(3) NOT NULL default '',
71 `liblibrarian` varchar(255) NOT NULL default '',
72 `libopac` varchar(255) NOT NULL default '',
73 `repeatable` tinyint(4) NOT NULL default 0,
74 `mandatory` tinyint(4) NOT NULL default 0,
75 `authorised_value` varchar(10) default NULL,
76 PRIMARY KEY (`authtypecode`,`tagfield`),
77 CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
78 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
81 -- Table structure for table `auth_types`
84 DROP TABLE IF EXISTS `auth_types`;
85 CREATE TABLE `auth_types` (
86 `authtypecode` varchar(10) NOT NULL default '',
87 `authtypetext` varchar(255) NOT NULL default '',
88 `auth_tag_to_report` varchar(3) NOT NULL default '',
89 `summary` mediumtext NOT NULL,
90 PRIMARY KEY (`authtypecode`)
91 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
94 -- Table structure for table `authorised_values`
97 DROP TABLE IF EXISTS `authorised_values`;
98 CREATE TABLE `authorised_values` ( -- stores values for authorized values categories and values
99 `id` int(11) NOT NULL auto_increment, -- unique key, used to identify the authorized value
100 `category` varchar(10) NOT NULL default '', -- key used to identify the authorized value category
101 `authorised_value` varchar(80) NOT NULL default '', -- code use to identify the authorized value
102 `lib` varchar(80) default NULL, -- authorized value description as printed in the staff client
103 `lib_opac` VARCHAR(80) default NULL, -- authorized value description as printed in the OPAC
104 `imageurl` varchar(200) default NULL, -- authorized value URL
106 KEY `name` (`category`),
108 KEY `auth_value_idx` (`authorised_value`)
109 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
112 -- Table structure for table `biblio`
115 DROP TABLE IF EXISTS `biblio`;
116 CREATE TABLE `biblio` ( -- table that stores bibliographic information
117 `biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
118 `frameworkcode` varchar(4) NOT NULL default '', -- foriegn key from the biblio_framework table to identify which framework was used in cataloging this record
119 `author` mediumtext, -- statement of responsibility from MARC record (100 in MARC21)
120 `title` mediumtext, -- title (without the subtitle) from the MARC record (245 in MARC21)
121 `unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240 in MARC21)
122 `notes` mediumtext, -- values from the general notes field in the MARC record (500 in MARC21) split by bar (|)
123 `serial` tinyint(1) default NULL, -- foreign key, linking to the subscriptionid in the serial table
124 `seriestitle` mediumtext,
125 `copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
126 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
127 `datecreated` DATE NOT NULL, -- the date this record was added to Koha
128 `abstract` mediumtext, -- summary from the MARC record (520 in MARC21)
129 PRIMARY KEY (`biblionumber`),
130 KEY `blbnoidx` (`biblionumber`)
131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
134 -- Table structure for table `biblio_framework`
137 DROP TABLE IF EXISTS `biblio_framework`;
138 CREATE TABLE `biblio_framework` (
139 `frameworkcode` varchar(4) NOT NULL default '',
140 `frameworktext` varchar(255) NOT NULL default '',
141 PRIMARY KEY (`frameworkcode`)
142 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
145 -- Table structure for table `biblioitems`
148 DROP TABLE IF EXISTS `biblioitems`;
149 CREATE TABLE `biblioitems` (
150 `biblioitemnumber` int(11) NOT NULL auto_increment,
151 `biblionumber` int(11) NOT NULL default 0,
154 `itemtype` varchar(10) default NULL,
155 `isbn` varchar(30) default NULL,
156 `issn` varchar(9) default NULL,
157 `publicationyear` text,
158 `publishercode` varchar(255) default NULL,
159 `volumedate` date default NULL,
161 `collectiontitle` mediumtext default NULL,
162 `collectionissn` text default NULL,
163 `collectionvolume` mediumtext default NULL,
164 `editionstatement` text default NULL,
165 `editionresponsibility` text default NULL,
166 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
167 `illus` varchar(255) default NULL,
168 `pages` varchar(255) default NULL,
170 `size` varchar(255) default NULL,
171 `place` varchar(255) default NULL,
172 `lccn` varchar(25) default NULL,
174 `url` varchar(255) default NULL,
175 `cn_source` varchar(10) default NULL,
176 `cn_class` varchar(30) default NULL,
177 `cn_item` varchar(10) default NULL,
178 `cn_suffix` varchar(10) default NULL,
179 `cn_sort` varchar(30) default NULL,
180 `totalissues` int(10),
181 `marcxml` longtext NOT NULL,
182 PRIMARY KEY (`biblioitemnumber`),
183 KEY `bibinoidx` (`biblioitemnumber`),
184 KEY `bibnoidx` (`biblionumber`),
187 KEY `publishercode` (`publishercode`),
188 CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
192 -- Table structure for table `borrowers`
195 DROP TABLE IF EXISTS `borrowers`;
196 CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members
197 `borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers
198 `cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers
199 `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname)
200 `firstname` text, -- patron/borrower's first name
201 `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs.
202 `othernames` mediumtext, -- any other names associated with the patron/borrower
203 `initials` text, -- initials for your patron/borrower
204 `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
205 `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
206 `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address
207 `address2` text, -- the second address line for your patron/borrower's primary address
208 `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address
209 `state` text default NULL, -- the state or province for your patron/borrower's primary address
210 `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
211 `country` text, -- the country for your patron/borrower's primary address
212 `email` mediumtext, -- the primary email address for your patron/borrower's primary address
213 `phone` text, -- the primary phone number for your patron/borrower's primary address
214 `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
215 `fax` mediumtext, -- the fax number for your patron/borrower's primary address
216 `emailpro` text, -- the secondary email addres for your patron/borrower's primary address
217 `phonepro` text, -- the secondary phone number for your patron/borrower's primary address
218 `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
219 `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
220 `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
221 `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address
222 `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address
223 `B_state` text default NULL, -- the state for your patron/borrower's alternate address
224 `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
225 `B_country` text, -- the country for your patron/borrower's alternate address
226 `B_email` text, -- the patron/borrower's alternate email address
227 `B_phone` mediumtext, -- the patron/borrower's alternate phone number
228 `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD)
229 `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch
230 `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category
231 `dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD)
232 `dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD)
233 `gonenoaddress` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having an unconfirmed address
234 `lost` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having lost their card
235 `debarred` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as being restricted
236 `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name
237 `contactfirstname` text, -- used for children to include first name of guarentor
238 `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor
239 `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations
240 `borrowernotes` mediumtext, -- a note on the patron/borroewr's account that is only visible in the staff client
241 `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor
242 `ethnicity` varchar(50) default NULL, -- unused in Koha
243 `ethnotes` varchar(255) default NULL, -- unused in Koha
244 `sex` varchar(1) default NULL, -- patron/borrower's gender
245 `password` varchar(30) default NULL, -- patron/borrower's encrypted password
246 `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
247 `userid` varchar(30) default NULL, -- patron/borrower's opac and/or staff client log in
248 `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
249 `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
250 `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
251 `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
252 `altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower
253 `altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower
254 `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
255 `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
256 `altcontactaddress3` varchar(255) default NULL, -- the third address line for the alternate contact for the patron/borrower
257 `altcontactstate` text default NULL, -- the city and state for the alternate contact for the patron/borrower
258 `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
259 `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower
260 `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
261 `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
262 `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history
263 UNIQUE KEY `cardnumber` (`cardnumber`),
264 PRIMARY KEY `borrowernumber` (`borrowernumber`),
265 KEY `categorycode` (`categorycode`),
266 KEY `branchcode` (`branchcode`),
267 KEY `userid` (`userid`),
268 KEY `guarantorid` (`guarantorid`),
269 CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
270 CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
274 -- Table structure for table `borrower_attribute_types`
277 DROP TABLE IF EXISTS `borrower_attribute_types`;
278 CREATE TABLE `borrower_attribute_types` (
279 `code` varchar(10) NOT NULL,
280 `description` varchar(255) NOT NULL,
281 `repeatable` tinyint(1) NOT NULL default 0,
282 `unique_id` tinyint(1) NOT NULL default 0,
283 `opac_display` tinyint(1) NOT NULL default 0,
284 `password_allowed` tinyint(1) NOT NULL default 0,
285 `staff_searchable` tinyint(1) NOT NULL default 0,
286 `authorised_value_category` varchar(10) default NULL,
287 PRIMARY KEY (`code`),
288 KEY `auth_val_cat_idx` (`authorised_value_category`)
289 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
292 -- Table structure for table `borrower_attributes`
295 DROP TABLE IF EXISTS `borrower_attributes`;
296 CREATE TABLE `borrower_attributes` (
297 `borrowernumber` int(11) NOT NULL,
298 `code` varchar(10) NOT NULL,
299 `attribute` varchar(64) default NULL,
300 `password` varchar(64) default NULL,
301 KEY `borrowernumber` (`borrowernumber`),
302 KEY `code_attribute` (`code`, `attribute`),
303 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
304 ON DELETE CASCADE ON UPDATE CASCADE,
305 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
306 ON DELETE CASCADE ON UPDATE CASCADE
307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
309 DROP TABLE IF EXISTS `branch_item_rules`;
310 CREATE TABLE `branch_item_rules` (
311 `branchcode` varchar(10) NOT NULL,
312 `itemtype` varchar(10) NOT NULL,
313 `holdallowed` tinyint(1) default NULL,
314 PRIMARY KEY (`itemtype`,`branchcode`),
315 KEY `branch_item_rules_ibfk_2` (`branchcode`),
316 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
317 ON DELETE CASCADE ON UPDATE CASCADE,
318 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
319 ON DELETE CASCADE ON UPDATE CASCADE
320 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
323 -- Table structure for table `branchcategories`
326 DROP TABLE IF EXISTS `branchcategories`;
327 CREATE TABLE `branchcategories` (
328 `categorycode` varchar(10) NOT NULL default '',
329 `categoryname` varchar(32),
330 `codedescription` mediumtext,
331 `categorytype` varchar(16),
332 PRIMARY KEY (`categorycode`)
333 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
336 -- Table structure for table `branches`
339 DROP TABLE IF EXISTS `branches`;
340 CREATE TABLE `branches` (
341 `branchcode` varchar(10) NOT NULL default '',
342 `branchname` mediumtext NOT NULL,
343 `branchaddress1` mediumtext,
344 `branchaddress2` mediumtext,
345 `branchaddress3` mediumtext,
346 `branchzip` varchar(25) default NULL,
347 `branchcity` mediumtext,
348 `branchstate` mediumtext,
349 `branchcountry` text,
350 `branchphone` mediumtext,
351 `branchfax` mediumtext,
352 `branchemail` mediumtext,
353 `branchurl` mediumtext,
354 `issuing` tinyint(4) default NULL,
355 `branchip` varchar(15) default NULL,
356 `branchprinter` varchar(100) default NULL,
357 `branchnotes` mediumtext,
358 UNIQUE KEY `branchcode` (`branchcode`)
359 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
362 -- Table structure for table `branchrelations`
365 DROP TABLE IF EXISTS `branchrelations`;
366 CREATE TABLE `branchrelations` (
367 `branchcode` varchar(10) NOT NULL default '',
368 `categorycode` varchar(10) NOT NULL default '',
369 PRIMARY KEY (`branchcode`,`categorycode`),
370 KEY `branchcode` (`branchcode`),
371 KEY `categorycode` (`categorycode`),
372 CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
373 CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
374 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
377 -- Table structure for table `branchtransfers`
380 DROP TABLE IF EXISTS `branchtransfers`;
381 CREATE TABLE `branchtransfers` (
382 `itemnumber` int(11) NOT NULL default 0,
383 `datesent` datetime default NULL,
384 `frombranch` varchar(10) NOT NULL default '',
385 `datearrived` datetime default NULL,
386 `tobranch` varchar(10) NOT NULL default '',
387 `comments` mediumtext,
388 KEY `frombranch` (`frombranch`),
389 KEY `tobranch` (`tobranch`),
390 KEY `itemnumber` (`itemnumber`),
391 CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
392 CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
393 CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
394 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
398 -- Table structure for table `browser`
400 DROP TABLE IF EXISTS `browser`;
401 CREATE TABLE `browser` (
402 `level` int(11) NOT NULL,
403 `classification` varchar(20) NOT NULL,
404 `description` varchar(255) NOT NULL,
405 `number` bigint(20) NOT NULL,
406 `endnode` tinyint(4) NOT NULL
407 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
410 -- Table structure for table `categories`
413 DROP TABLE IF EXISTS `categories`;
414 CREATE TABLE `categories` (
415 `categorycode` varchar(10) NOT NULL default '',
416 `description` mediumtext,
417 `enrolmentperiod` smallint(6) default NULL,
418 `enrolmentperioddate` DATE NULL DEFAULT NULL,
419 `upperagelimit` smallint(6) default NULL,
420 `dateofbirthrequired` tinyint(1) default NULL,
421 `finetype` varchar(30) default NULL,
422 `bulk` tinyint(1) default NULL,
423 `enrolmentfee` decimal(28,6) default NULL,
424 `overduenoticerequired` tinyint(1) default NULL,
425 `issuelimit` smallint(6) default NULL,
426 `reservefee` decimal(28,6) default NULL,
427 `hidelostitems` tinyint(1) NOT NULL default '0',
428 `category_type` varchar(1) NOT NULL default 'A',
429 PRIMARY KEY (`categorycode`),
430 UNIQUE KEY `categorycode` (`categorycode`)
431 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
434 -- Table: collections
436 DROP TABLE IF EXISTS collections;
437 CREATE TABLE collections (
438 colId integer(11) NOT NULL auto_increment,
439 colTitle varchar(100) NOT NULL DEFAULT '',
440 colDesc text NOT NULL,
441 colBranchcode varchar(4) DEFAULT NULL comment 'branchcode for branch where item should be held.',
443 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
446 -- Table: collections_tracking
448 DROP TABLE IF EXISTS collections_tracking;
449 CREATE TABLE collections_tracking (
450 ctId integer(11) NOT NULL auto_increment,
451 colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId',
452 itemnumber integer(11) NOT NULL DEFAULT 0 comment 'items.itemnumber',
454 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
457 -- Table structure for table `borrower_branch_circ_rules`
460 DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
461 CREATE TABLE `branch_borrower_circ_rules` (
462 `branchcode` VARCHAR(10) NOT NULL,
463 `categorycode` VARCHAR(10) NOT NULL,
464 `maxissueqty` int(4) default NULL,
465 PRIMARY KEY (`categorycode`, `branchcode`),
466 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
467 ON DELETE CASCADE ON UPDATE CASCADE,
468 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
469 ON DELETE CASCADE ON UPDATE CASCADE
470 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
473 -- Table structure for table `default_borrower_circ_rules`
476 DROP TABLE IF EXISTS `default_borrower_circ_rules`;
477 CREATE TABLE `default_borrower_circ_rules` (
478 `categorycode` VARCHAR(10) NOT NULL,
479 `maxissueqty` int(4) default NULL,
480 PRIMARY KEY (`categorycode`),
481 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
482 ON DELETE CASCADE ON UPDATE CASCADE
483 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
486 -- Table structure for table `default_branch_circ_rules`
489 DROP TABLE IF EXISTS `default_branch_circ_rules`;
490 CREATE TABLE `default_branch_circ_rules` (
491 `branchcode` VARCHAR(10) NOT NULL,
492 `maxissueqty` int(4) default NULL,
493 `holdallowed` tinyint(1) default NULL,
494 PRIMARY KEY (`branchcode`),
495 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
496 ON DELETE CASCADE ON UPDATE CASCADE
497 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
500 -- Table structure for table `default_branch_item_rules`
502 DROP TABLE IF EXISTS `default_branch_item_rules`;
503 CREATE TABLE `default_branch_item_rules` (
504 `itemtype` varchar(10) NOT NULL,
505 `holdallowed` tinyint(1) default NULL,
506 PRIMARY KEY (`itemtype`),
507 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
508 ON DELETE CASCADE ON UPDATE CASCADE
509 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
512 -- Table structure for table `default_circ_rules`
515 DROP TABLE IF EXISTS `default_circ_rules`;
516 CREATE TABLE `default_circ_rules` (
517 `singleton` enum('singleton') NOT NULL default 'singleton',
518 `maxissueqty` int(4) default NULL,
519 `holdallowed` int(1) default NULL,
520 PRIMARY KEY (`singleton`)
521 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
524 -- Table structure for table `cities`
527 DROP TABLE IF EXISTS `cities`;
528 CREATE TABLE `cities` (
529 `cityid` int(11) NOT NULL auto_increment,
530 `city_name` varchar(100) NOT NULL default '',
531 `city_state` VARCHAR( 100 ) NULL DEFAULT NULL,
532 `city_country` VARCHAR( 100 ) NULL DEFAULT NULL,
533 `city_zipcode` varchar(20) default NULL,
534 PRIMARY KEY (`cityid`)
535 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
538 -- Table structure for table `class_sort_rules`
541 DROP TABLE IF EXISTS `class_sort_rules`;
542 CREATE TABLE `class_sort_rules` (
543 `class_sort_rule` varchar(10) NOT NULL default '',
544 `description` mediumtext,
545 `sort_routine` varchar(30) NOT NULL default '',
546 PRIMARY KEY (`class_sort_rule`),
547 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
548 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
551 -- Table structure for table `class_sources`
554 DROP TABLE IF EXISTS `class_sources`;
555 CREATE TABLE `class_sources` (
556 `cn_source` varchar(10) NOT NULL default '',
557 `description` mediumtext,
558 `used` tinyint(4) NOT NULL default 0,
559 `class_sort_rule` varchar(10) NOT NULL default '',
560 PRIMARY KEY (`cn_source`),
561 UNIQUE KEY `cn_source_idx` (`cn_source`),
562 KEY `used_idx` (`used`),
563 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
564 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
567 -- Table structure for table `currency`
570 DROP TABLE IF EXISTS `currency`;
571 CREATE TABLE `currency` (
572 `currency` varchar(10) NOT NULL default '',
573 `symbol` varchar(5) default NULL,
574 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
575 `rate` float(15,5) default NULL,
576 `active` tinyint(1) default NULL,
577 PRIMARY KEY (`currency`)
578 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
581 -- Table structure for table `deletedbiblio`
584 DROP TABLE IF EXISTS `deletedbiblio`;
585 CREATE TABLE `deletedbiblio` (
586 `biblionumber` int(11) NOT NULL default 0,
587 `frameworkcode` varchar(4) NOT NULL default '',
590 `unititle` mediumtext,
592 `serial` tinyint(1) default NULL,
593 `seriestitle` mediumtext,
594 `copyrightdate` smallint(6) default NULL,
595 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
596 `datecreated` DATE NOT NULL,
597 `abstract` mediumtext,
598 PRIMARY KEY (`biblionumber`),
599 KEY `blbnoidx` (`biblionumber`)
600 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
603 -- Table structure for table `deletedbiblioitems`
606 DROP TABLE IF EXISTS `deletedbiblioitems`;
607 CREATE TABLE `deletedbiblioitems` (
608 `biblioitemnumber` int(11) NOT NULL default 0,
609 `biblionumber` int(11) NOT NULL default 0,
612 `itemtype` varchar(10) default NULL,
613 `isbn` varchar(30) default NULL,
614 `issn` varchar(9) default NULL,
615 `publicationyear` text,
616 `publishercode` varchar(255) default NULL,
617 `volumedate` date default NULL,
619 `collectiontitle` mediumtext default NULL,
620 `collectionissn` text default NULL,
621 `collectionvolume` mediumtext default NULL,
622 `editionstatement` text default NULL,
623 `editionresponsibility` text default NULL,
624 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
625 `illus` varchar(255) default NULL,
626 `pages` varchar(255) default NULL,
628 `size` varchar(255) default NULL,
629 `place` varchar(255) default NULL,
630 `lccn` varchar(25) default NULL,
632 `url` varchar(255) default NULL,
633 `cn_source` varchar(10) default NULL,
634 `cn_class` varchar(30) default NULL,
635 `cn_item` varchar(10) default NULL,
636 `cn_suffix` varchar(10) default NULL,
637 `cn_sort` varchar(30) default NULL,
638 `totalissues` int(10),
639 `marcxml` longtext NOT NULL,
640 PRIMARY KEY (`biblioitemnumber`),
641 KEY `bibinoidx` (`biblioitemnumber`),
642 KEY `bibnoidx` (`biblionumber`),
644 KEY `publishercode` (`publishercode`)
645 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
648 -- Table structure for table `deletedborrowers`
651 DROP TABLE IF EXISTS `deletedborrowers`;
652 CREATE TABLE `deletedborrowers` (
653 `borrowernumber` int(11) NOT NULL default 0,
654 `cardnumber` varchar(16) NOT NULL default '',
655 `surname` mediumtext NOT NULL,
658 `othernames` mediumtext,
660 `streetnumber` varchar(10) default NULL,
661 `streettype` varchar(50) default NULL,
662 `address` mediumtext NOT NULL,
664 `city` mediumtext NOT NULL,
665 `state` text default NULL,
666 `zipcode` varchar(25) default NULL,
670 `mobile` varchar(50) default NULL,
674 `B_streetnumber` varchar(10) default NULL,
675 `B_streettype` varchar(50) default NULL,
676 `B_address` varchar(100) default NULL,
677 `B_address2` text default NULL,
679 `B_state` text default NULL,
680 `B_zipcode` varchar(25) default NULL,
683 `B_phone` mediumtext,
684 `dateofbirth` date default NULL,
685 `branchcode` varchar(10) NOT NULL default '',
686 `categorycode` varchar(10) default NULL,
687 `dateenrolled` date default NULL,
688 `dateexpiry` date default NULL,
689 `gonenoaddress` tinyint(1) default NULL,
690 `lost` tinyint(1) default NULL,
691 `debarred` tinyint(1) default NULL,
692 `contactname` mediumtext,
693 `contactfirstname` text,
695 `guarantorid` int(11) default NULL,
696 `borrowernotes` mediumtext,
697 `relationship` varchar(100) default NULL,
698 `ethnicity` varchar(50) default NULL,
699 `ethnotes` varchar(255) default NULL,
700 `sex` varchar(1) default NULL,
701 `password` varchar(30) default NULL,
702 `flags` int(11) default NULL,
703 `userid` varchar(30) default NULL,
704 `opacnote` mediumtext,
705 `contactnote` varchar(255) default NULL,
706 `sort1` varchar(80) default NULL,
707 `sort2` varchar(80) default NULL,
708 `altcontactfirstname` varchar(255) default NULL,
709 `altcontactsurname` varchar(255) default NULL,
710 `altcontactaddress1` varchar(255) default NULL,
711 `altcontactaddress2` varchar(255) default NULL,
712 `altcontactaddress3` varchar(255) default NULL,
713 `altcontactstate` text default NULL,
714 `altcontactzipcode` varchar(50) default NULL,
715 `altcontactcountry` text default NULL,
716 `altcontactphone` varchar(50) default NULL,
717 `smsalertnumber` varchar(50) default NULL,
718 `privacy` integer(11) DEFAULT '1' NOT NULL,
719 KEY `borrowernumber` (`borrowernumber`),
720 KEY `cardnumber` (`cardnumber`)
721 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
724 -- Table structure for table `deleteditems`
727 DROP TABLE IF EXISTS `deleteditems`;
728 CREATE TABLE `deleteditems` (
729 `itemnumber` int(11) NOT NULL default 0,
730 `biblionumber` int(11) NOT NULL default 0,
731 `biblioitemnumber` int(11) NOT NULL default 0,
732 `barcode` varchar(20) default NULL,
733 `dateaccessioned` date default NULL,
734 `booksellerid` mediumtext default NULL,
735 `homebranch` varchar(10) default NULL,
736 `price` decimal(8,2) default NULL,
737 `replacementprice` decimal(8,2) default NULL,
738 `replacementpricedate` date default NULL,
739 `datelastborrowed` date default NULL,
740 `datelastseen` date default NULL,
741 `stack` tinyint(1) default NULL,
742 `notforloan` tinyint(1) NOT NULL default 0,
743 `damaged` tinyint(1) NOT NULL default 0,
744 `itemlost` tinyint(1) NOT NULL default 0,
745 `wthdrawn` tinyint(1) NOT NULL default 0,
746 `itemcallnumber` varchar(255) default NULL,
747 `issues` smallint(6) default NULL,
748 `renewals` smallint(6) default NULL,
749 `reserves` smallint(6) default NULL,
750 `restricted` tinyint(1) default NULL,
751 `itemnotes` mediumtext,
752 `holdingbranch` varchar(10) default NULL,
753 `paidfor` mediumtext,
754 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
755 `location` varchar(80) default NULL,
756 `permanent_location` varchar(80) default NULL,
757 `onloan` date default NULL,
758 `cn_source` varchar(10) default NULL,
759 `cn_sort` varchar(30) default NULL,
760 `ccode` varchar(10) default NULL,
761 `materials` varchar(10) default NULL,
762 `uri` varchar(255) default NULL,
763 `itype` varchar(10) default NULL,
764 `more_subfields_xml` longtext default NULL,
765 `enumchron` text default NULL,
766 `copynumber` varchar(32) default NULL,
767 `stocknumber` varchar(32) default NULL,
769 PRIMARY KEY (`itemnumber`),
770 KEY `delitembarcodeidx` (`barcode`),
771 KEY `delitemstocknumberidx` (`stocknumber`),
772 KEY `delitembinoidx` (`biblioitemnumber`),
773 KEY `delitembibnoidx` (`biblionumber`),
774 KEY `delhomebranch` (`homebranch`),
775 KEY `delholdingbranch` (`holdingbranch`)
776 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
779 -- Table structure for table `ethnicity`
782 DROP TABLE IF EXISTS `ethnicity`;
783 CREATE TABLE `ethnicity` (
784 `code` varchar(10) NOT NULL default '',
785 `name` varchar(255) default NULL,
787 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
790 -- Table structure for table `export_format`
793 DROP TABLE IF EXISTS `export_format`;
794 CREATE TABLE `export_format` (
795 `export_format_id` int(11) NOT NULL auto_increment,
796 `profile` varchar(255) NOT NULL,
797 `description` mediumtext NOT NULL,
798 `marcfields` mediumtext NOT NULL,
799 `csv_separator` varchar(2) NOT NULL,
800 `field_separator` varchar(2) NOT NULL,
801 `subfield_separator` varchar(2) NOT NULL,
802 `encoding` varchar(255) NOT NULL,
803 PRIMARY KEY (`export_format_id`)
804 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
808 -- Table structure for table `hold_fill_targets`
811 DROP TABLE IF EXISTS `hold_fill_targets`;
812 CREATE TABLE hold_fill_targets (
813 `borrowernumber` int(11) NOT NULL,
814 `biblionumber` int(11) NOT NULL,
815 `itemnumber` int(11) NOT NULL,
816 `source_branchcode` varchar(10) default NULL,
817 `item_level_request` tinyint(4) NOT NULL default 0,
818 PRIMARY KEY `itemnumber` (`itemnumber`),
819 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
820 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
821 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
822 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
823 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
824 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
825 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
826 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
827 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
828 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
831 -- Table structure for table `import_batches`
834 DROP TABLE IF EXISTS `import_batches`;
835 CREATE TABLE `import_batches` (
836 `import_batch_id` int(11) NOT NULL auto_increment,
837 `matcher_id` int(11) default NULL,
838 `template_id` int(11) default NULL,
839 `branchcode` varchar(10) default NULL,
840 `num_biblios` int(11) NOT NULL default 0,
841 `num_items` int(11) NOT NULL default 0,
842 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
843 `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
844 `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
845 `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
846 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
847 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
848 `file_name` varchar(100),
849 `comments` mediumtext,
850 PRIMARY KEY (`import_batch_id`),
851 KEY `branchcode` (`branchcode`)
852 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
855 -- Table structure for table `import_records`
858 DROP TABLE IF EXISTS `import_records`;
859 CREATE TABLE `import_records` (
860 `import_record_id` int(11) NOT NULL auto_increment,
861 `import_batch_id` int(11) NOT NULL,
862 `branchcode` varchar(10) default NULL,
863 `record_sequence` int(11) NOT NULL default 0,
864 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
865 `import_date` DATE default NULL,
866 `marc` longblob NOT NULL,
867 `marcxml` longtext NOT NULL,
868 `marcxml_old` longtext NOT NULL,
869 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
870 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
871 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged',
872 `import_error` mediumtext,
873 `encoding` varchar(40) NOT NULL default '',
874 `z3950random` varchar(40) default NULL,
875 PRIMARY KEY (`import_record_id`),
876 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
877 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
878 KEY `branchcode` (`branchcode`),
879 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
880 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
883 -- Table structure for `import_record_matches`
885 DROP TABLE IF EXISTS `import_record_matches`;
886 CREATE TABLE `import_record_matches` (
887 `import_record_id` int(11) NOT NULL,
888 `candidate_match_id` int(11) NOT NULL,
889 `score` int(11) NOT NULL default 0,
890 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
891 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
892 KEY `record_score` (`import_record_id`, `score`)
893 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
896 -- Table structure for table `import_biblios`
899 DROP TABLE IF EXISTS `import_biblios`;
900 CREATE TABLE `import_biblios` (
901 `import_record_id` int(11) NOT NULL,
902 `matched_biblionumber` int(11) default NULL,
903 `control_number` varchar(25) default NULL,
904 `original_source` varchar(25) default NULL,
905 `title` varchar(128) default NULL,
906 `author` varchar(80) default NULL,
907 `isbn` varchar(30) default NULL,
908 `issn` varchar(9) default NULL,
909 `has_items` tinyint(1) NOT NULL default 0,
910 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
911 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
912 KEY `matched_biblionumber` (`matched_biblionumber`),
913 KEY `title` (`title`),
915 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
918 -- Table structure for table `import_items`
921 DROP TABLE IF EXISTS `import_items`;
922 CREATE TABLE `import_items` (
923 `import_items_id` int(11) NOT NULL auto_increment,
924 `import_record_id` int(11) NOT NULL,
925 `itemnumber` int(11) default NULL,
926 `branchcode` varchar(10) default NULL,
927 `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
928 `marcxml` longtext NOT NULL,
929 `import_error` mediumtext,
930 PRIMARY KEY (`import_items_id`),
931 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
932 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
933 KEY `itemnumber` (`itemnumber`),
934 KEY `branchcode` (`branchcode`)
935 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
938 -- Table structure for table `issues`
941 DROP TABLE IF EXISTS `issues`;
942 CREATE TABLE `issues` (
943 `borrowernumber` int(11),
944 `itemnumber` int(11),
945 `date_due` date default NULL,
946 `branchcode` varchar(10) default NULL,
947 `issuingbranch` varchar(18) default NULL,
948 `returndate` date default NULL,
949 `lastreneweddate` date default NULL,
950 `return` varchar(4) default NULL,
951 `renewals` tinyint(4) default NULL,
952 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
953 `issuedate` date default NULL,
954 PRIMARY KEY (`itemnumber`),
955 KEY `issuesborridx` (`borrowernumber`),
956 KEY `bordate` (`borrowernumber`,`timestamp`),
957 CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
958 CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
959 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
962 -- Table structure for table `issuingrules`
965 DROP TABLE IF EXISTS `issuingrules`;
966 CREATE TABLE `issuingrules` (
967 `categorycode` varchar(10) NOT NULL default '',
968 `itemtype` varchar(10) NOT NULL default '',
969 `restrictedtype` tinyint(1) default NULL,
970 `rentaldiscount` decimal(28,6) default NULL,
971 `reservecharge` decimal(28,6) default NULL,
972 `fine` decimal(28,6) default NULL,
973 `finedays` int(11) default NULL,
974 `firstremind` int(11) default NULL,
975 `chargeperiod` int(11) default NULL,
976 `accountsent` int(11) default NULL,
977 `chargename` varchar(100) default NULL,
978 `maxissueqty` int(4) default NULL,
979 `issuelength` int(4) default NULL,
980 `hardduedate` date default NULL,
981 `hardduedatecompare` tinyint NOT NULL default "0",
982 `renewalsallowed` smallint(6) NOT NULL default "0",
983 `reservesallowed` smallint(6) NOT NULL default "0",
984 `branchcode` varchar(10) NOT NULL default '',
985 PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
986 KEY `categorycode` (`categorycode`),
987 KEY `itemtype` (`itemtype`)
988 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
991 -- Table structure for table `items`
994 DROP TABLE IF EXISTS `items`;
995 CREATE TABLE `items` (
996 `itemnumber` int(11) NOT NULL auto_increment,
997 `biblionumber` int(11) NOT NULL default 0,
998 `biblioitemnumber` int(11) NOT NULL default 0,
999 `barcode` varchar(20) default NULL,
1000 `dateaccessioned` date default NULL,
1001 `booksellerid` mediumtext default NULL,
1002 `homebranch` varchar(10) default NULL,
1003 `price` decimal(8,2) default NULL,
1004 `replacementprice` decimal(8,2) default NULL,
1005 `replacementpricedate` date default NULL,
1006 `datelastborrowed` date default NULL,
1007 `datelastseen` date default NULL,
1008 `stack` tinyint(1) default NULL,
1009 `notforloan` tinyint(1) NOT NULL default 0,
1010 `damaged` tinyint(1) NOT NULL default 0,
1011 `itemlost` tinyint(1) NOT NULL default 0,
1012 `wthdrawn` tinyint(1) NOT NULL default 0,
1013 `itemcallnumber` varchar(255) default NULL,
1014 `issues` smallint(6) default NULL,
1015 `renewals` smallint(6) default NULL,
1016 `reserves` smallint(6) default NULL,
1017 `restricted` tinyint(1) default NULL,
1018 `itemnotes` mediumtext,
1019 `holdingbranch` varchar(10) default NULL,
1020 `paidfor` mediumtext,
1021 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1022 `location` varchar(80) default NULL,
1023 `permanent_location` varchar(80) default NULL,
1024 `onloan` date default NULL,
1025 `cn_source` varchar(10) default NULL,
1026 `cn_sort` varchar(30) default NULL,
1027 `ccode` varchar(10) default NULL,
1028 `materials` varchar(10) default NULL,
1029 `uri` varchar(255) default NULL,
1030 `itype` varchar(10) default NULL,
1031 `more_subfields_xml` longtext default NULL,
1032 `enumchron` text default NULL,
1033 `copynumber` varchar(32) default NULL,
1034 `stocknumber` varchar(32) default NULL,
1035 PRIMARY KEY (`itemnumber`),
1036 UNIQUE KEY `itembarcodeidx` (`barcode`),
1037 KEY `itemstocknumberidx` (`stocknumber`),
1038 KEY `itembinoidx` (`biblioitemnumber`),
1039 KEY `itembibnoidx` (`biblionumber`),
1040 KEY `homebranch` (`homebranch`),
1041 KEY `holdingbranch` (`holdingbranch`),
1042 CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1043 CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1044 CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1045 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1048 -- Table structure for table `itemtypes`
1051 DROP TABLE IF EXISTS `itemtypes`;
1052 CREATE TABLE `itemtypes` (
1053 `itemtype` varchar(10) NOT NULL default '',
1054 `description` mediumtext,
1055 `rentalcharge` double(16,4) default NULL,
1056 `notforloan` smallint(6) default NULL,
1057 `imageurl` varchar(200) default NULL,
1059 PRIMARY KEY (`itemtype`),
1060 UNIQUE KEY `itemtype` (`itemtype`)
1061 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1064 -- Table structure for table `creator_batches`
1067 DROP TABLE IF EXISTS `creator_batches`;
1068 SET @saved_cs_client = @@character_set_client;
1069 SET character_set_client = utf8;
1070 CREATE TABLE `creator_batches` (
1071 `label_id` int(11) NOT NULL AUTO_INCREMENT,
1072 `batch_id` int(10) NOT NULL DEFAULT '1',
1073 `item_number` int(11) DEFAULT NULL,
1074 `borrower_number` int(11) DEFAULT NULL,
1075 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1076 `branch_code` varchar(10) NOT NULL DEFAULT 'NB',
1077 `creator` char(15) NOT NULL DEFAULT 'Labels',
1078 PRIMARY KEY (`label_id`),
1079 KEY `branch_fk_constraint` (`branch_code`),
1080 KEY `item_fk_constraint` (`item_number`),
1081 KEY `borrower_fk_constraint` (`borrower_number`),
1082 CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1083 CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE,
1084 CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
1085 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1088 -- Table structure for table `creator_images`
1091 DROP TABLE IF EXISTS `creator_images`;
1092 SET @saved_cs_client = @@character_set_client;
1093 SET character_set_client = utf8;
1094 CREATE TABLE `creator_images` (
1095 `image_id` int(4) NOT NULL AUTO_INCREMENT,
1096 `imagefile` mediumblob,
1097 `image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1098 PRIMARY KEY (`image_id`),
1099 UNIQUE KEY `image_name_index` (`image_name`)
1100 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1103 -- Table structure for table `creator_layouts`
1106 DROP TABLE IF EXISTS `creator_layouts`;
1107 SET @saved_cs_client = @@character_set_client;
1108 SET character_set_client = utf8;
1109 CREATE TABLE `creator_layouts` (
1110 `layout_id` int(4) NOT NULL AUTO_INCREMENT,
1111 `barcode_type` char(100) NOT NULL DEFAULT 'CODE39',
1112 `start_label` int(2) NOT NULL DEFAULT '1',
1113 `printing_type` char(32) NOT NULL DEFAULT 'BAR',
1114 `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
1115 `guidebox` int(1) DEFAULT '0',
1116 `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
1117 `font_size` int(4) NOT NULL DEFAULT '10',
1118 `units` char(20) NOT NULL DEFAULT 'POINT',
1119 `callnum_split` int(1) DEFAULT '0',
1120 `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L',
1121 `format_string` varchar(210) NOT NULL DEFAULT 'barcode',
1122 `layout_xml` text NOT NULL,
1123 `creator` char(15) NOT NULL DEFAULT 'Labels',
1124 PRIMARY KEY (`layout_id`)
1125 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1128 -- Table structure for table `creator_templates`
1131 DROP TABLE IF EXISTS `creator_templates`;
1132 SET @saved_cs_client = @@character_set_client;
1133 SET character_set_client = utf8;
1134 CREATE TABLE `creator_templates` (
1135 `template_id` int(4) NOT NULL AUTO_INCREMENT,
1136 `profile_id` int(4) DEFAULT NULL,
1137 `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE',
1138 `template_desc` char(100) NOT NULL DEFAULT 'Default description',
1139 `page_width` float NOT NULL DEFAULT '0',
1140 `page_height` float NOT NULL DEFAULT '0',
1141 `label_width` float NOT NULL DEFAULT '0',
1142 `label_height` float NOT NULL DEFAULT '0',
1143 `top_text_margin` float NOT NULL DEFAULT '0',
1144 `left_text_margin` float NOT NULL DEFAULT '0',
1145 `top_margin` float NOT NULL DEFAULT '0',
1146 `left_margin` float NOT NULL DEFAULT '0',
1147 `cols` int(2) NOT NULL DEFAULT '0',
1148 `rows` int(2) NOT NULL DEFAULT '0',
1149 `col_gap` float NOT NULL DEFAULT '0',
1150 `row_gap` float NOT NULL DEFAULT '0',
1151 `units` char(20) NOT NULL DEFAULT 'POINT',
1152 `creator` char(15) NOT NULL DEFAULT 'Labels',
1153 PRIMARY KEY (`template_id`),
1154 KEY `template_profile_fk_constraint` (`profile_id`)
1155 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1158 -- Table structure for table `letter`
1161 DROP TABLE IF EXISTS `letter`;
1162 CREATE TABLE `letter` (
1163 `module` varchar(20) NOT NULL default '',
1164 `code` varchar(20) NOT NULL default '',
1165 `name` varchar(100) NOT NULL default '',
1166 `title` varchar(200) NOT NULL default '',
1168 PRIMARY KEY (`module`,`code`)
1169 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1172 -- Table structure for table `marc_subfield_structure`
1175 DROP TABLE IF EXISTS `marc_subfield_structure`;
1176 CREATE TABLE `marc_subfield_structure` (
1177 `tagfield` varchar(3) NOT NULL default '',
1178 `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin,
1179 `liblibrarian` varchar(255) NOT NULL default '',
1180 `libopac` varchar(255) NOT NULL default '',
1181 `repeatable` tinyint(4) NOT NULL default 0,
1182 `mandatory` tinyint(4) NOT NULL default 0,
1183 `kohafield` varchar(40) default NULL,
1184 `tab` tinyint(1) default NULL,
1185 `authorised_value` varchar(20) default NULL,
1186 `authtypecode` varchar(20) default NULL,
1187 `value_builder` varchar(80) default NULL,
1188 `isurl` tinyint(1) default NULL,
1189 `hidden` tinyint(1) default NULL,
1190 `frameworkcode` varchar(4) NOT NULL default '',
1191 `seealso` varchar(1100) default NULL,
1192 `link` varchar(80) default NULL,
1193 `defaultvalue` text default NULL,
1194 PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
1195 KEY `kohafield_2` (`kohafield`),
1196 KEY `tab` (`frameworkcode`,`tab`),
1197 KEY `kohafield` (`frameworkcode`,`kohafield`)
1198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1201 -- Table structure for table `marc_tag_structure`
1204 DROP TABLE IF EXISTS `marc_tag_structure`;
1205 CREATE TABLE `marc_tag_structure` (
1206 `tagfield` varchar(3) NOT NULL default '',
1207 `liblibrarian` varchar(255) NOT NULL default '',
1208 `libopac` varchar(255) NOT NULL default '',
1209 `repeatable` tinyint(4) NOT NULL default 0,
1210 `mandatory` tinyint(4) NOT NULL default 0,
1211 `authorised_value` varchar(10) default NULL,
1212 `frameworkcode` varchar(4) NOT NULL default '',
1213 PRIMARY KEY (`frameworkcode`,`tagfield`)
1214 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1217 -- Table structure for table `marc_matchers`
1220 DROP TABLE IF EXISTS `marc_matchers`;
1221 CREATE TABLE `marc_matchers` (
1222 `matcher_id` int(11) NOT NULL auto_increment,
1223 `code` varchar(10) NOT NULL default '',
1224 `description` varchar(255) NOT NULL default '',
1225 `record_type` varchar(10) NOT NULL default 'biblio',
1226 `threshold` int(11) NOT NULL default 0,
1227 PRIMARY KEY (`matcher_id`),
1228 KEY `code` (`code`),
1229 KEY `record_type` (`record_type`)
1230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1233 -- Table structure for table `matchpoints`
1235 DROP TABLE IF EXISTS `matchpoints`;
1236 CREATE TABLE `matchpoints` (
1237 `matcher_id` int(11) NOT NULL,
1238 `matchpoint_id` int(11) NOT NULL auto_increment,
1239 `search_index` varchar(30) NOT NULL default '',
1240 `score` int(11) NOT NULL default 0,
1241 PRIMARY KEY (`matchpoint_id`),
1242 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1243 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
1244 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1248 -- Table structure for table `matchpoint_components`
1250 DROP TABLE IF EXISTS `matchpoint_components`;
1251 CREATE TABLE `matchpoint_components` (
1252 `matchpoint_id` int(11) NOT NULL,
1253 `matchpoint_component_id` int(11) NOT NULL auto_increment,
1254 sequence int(11) NOT NULL default 0,
1255 tag varchar(3) NOT NULL default '',
1256 subfields varchar(40) NOT NULL default '',
1257 offset int(4) NOT NULL default 0,
1258 length int(4) NOT NULL default 0,
1259 PRIMARY KEY (`matchpoint_component_id`),
1260 KEY `by_sequence` (`matchpoint_id`, `sequence`),
1261 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
1262 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1266 -- Table structure for table `matcher_component_norms`
1268 DROP TABLE IF EXISTS `matchpoint_component_norms`;
1269 CREATE TABLE `matchpoint_component_norms` (
1270 `matchpoint_component_id` int(11) NOT NULL,
1271 `sequence` int(11) NOT NULL default 0,
1272 `norm_routine` varchar(50) NOT NULL default '',
1273 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
1274 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
1275 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
1276 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1279 -- Table structure for table `matcher_matchpoints`
1281 DROP TABLE IF EXISTS `matcher_matchpoints`;
1282 CREATE TABLE `matcher_matchpoints` (
1283 `matcher_id` int(11) NOT NULL,
1284 `matchpoint_id` int(11) NOT NULL,
1285 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
1286 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1287 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
1288 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1289 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1292 -- Table structure for table `matchchecks`
1294 DROP TABLE IF EXISTS `matchchecks`;
1295 CREATE TABLE `matchchecks` (
1296 `matcher_id` int(11) NOT NULL,
1297 `matchcheck_id` int(11) NOT NULL auto_increment,
1298 `source_matchpoint_id` int(11) NOT NULL,
1299 `target_matchpoint_id` int(11) NOT NULL,
1300 PRIMARY KEY (`matchcheck_id`),
1301 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
1302 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1303 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
1304 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1305 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
1306 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
1307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1310 -- Table structure for table `notifys`
1313 DROP TABLE IF EXISTS `notifys`;
1314 CREATE TABLE `notifys` (
1315 `notify_id` int(11) NOT NULL default 0,
1316 `borrowernumber` int(11) NOT NULL default 0,
1317 `itemnumber` int(11) NOT NULL default 0,
1318 `notify_date` date default NULL,
1319 `notify_send_date` date default NULL,
1320 `notify_level` int(1) NOT NULL default 0,
1321 `method` varchar(20) NOT NULL default ''
1322 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1325 -- Table structure for table `nozebra`
1328 DROP TABLE IF EXISTS `nozebra`;
1329 CREATE TABLE `nozebra` (
1330 `server` varchar(20) NOT NULL,
1331 `indexname` varchar(40) NOT NULL,
1332 `value` varchar(250) NOT NULL,
1333 `biblionumbers` longtext NOT NULL,
1334 KEY `indexname` (`server`,`indexname`),
1335 KEY `value` (`server`,`value`))
1336 ENGINE=InnoDB DEFAULT CHARSET=utf8;
1339 -- Table structure for table `old_issues`
1342 DROP TABLE IF EXISTS `old_issues`;
1343 CREATE TABLE `old_issues` (
1344 `borrowernumber` int(11) default NULL,
1345 `itemnumber` int(11) default NULL,
1346 `date_due` date default NULL,
1347 `branchcode` varchar(10) default NULL,
1348 `issuingbranch` varchar(18) default NULL,
1349 `returndate` date default NULL,
1350 `lastreneweddate` date default NULL,
1351 `return` varchar(4) default NULL,
1352 `renewals` tinyint(4) default NULL,
1353 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1354 `issuedate` date default NULL,
1355 KEY `old_issuesborridx` (`borrowernumber`),
1356 KEY `old_issuesitemidx` (`itemnumber`),
1357 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1358 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1359 ON DELETE SET NULL ON UPDATE SET NULL,
1360 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1361 ON DELETE SET NULL ON UPDATE SET NULL
1362 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1365 -- Table structure for table `old_reserves`
1367 DROP TABLE IF EXISTS `old_reserves`;
1368 CREATE TABLE `old_reserves` (
1369 `borrowernumber` int(11) default NULL,
1370 `reservedate` date default NULL,
1371 `biblionumber` int(11) default NULL,
1372 `constrainttype` varchar(1) default NULL,
1373 `branchcode` varchar(10) default NULL,
1374 `notificationdate` date default NULL,
1375 `reminderdate` date default NULL,
1376 `cancellationdate` date default NULL,
1377 `reservenotes` mediumtext,
1378 `priority` smallint(6) default NULL,
1379 `found` varchar(1) default NULL,
1380 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1381 `itemnumber` int(11) default NULL,
1382 `waitingdate` date default NULL,
1383 `expirationdate` DATE DEFAULT NULL,
1384 `lowestPriority` tinyint(1) NOT NULL,
1385 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1386 KEY `old_reserves_biblionumber` (`biblionumber`),
1387 KEY `old_reserves_itemnumber` (`itemnumber`),
1388 KEY `old_reserves_branchcode` (`branchcode`),
1389 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1390 ON DELETE SET NULL ON UPDATE SET NULL,
1391 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1392 ON DELETE SET NULL ON UPDATE SET NULL,
1393 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1394 ON DELETE SET NULL ON UPDATE SET NULL
1395 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1398 -- Table structure for table `opac_news`
1401 DROP TABLE IF EXISTS `opac_news`;
1402 CREATE TABLE `opac_news` (
1403 `idnew` int(10) unsigned NOT NULL auto_increment,
1404 `title` varchar(250) NOT NULL default '',
1405 `new` text NOT NULL,
1406 `lang` varchar(25) NOT NULL default '',
1407 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
1408 `expirationdate` date default NULL,
1409 `number` int(11) default NULL,
1410 PRIMARY KEY (`idnew`)
1411 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1414 -- Table structure for table `overduerules`
1417 DROP TABLE IF EXISTS `overduerules`;
1418 CREATE TABLE `overduerules` (
1419 `branchcode` varchar(10) NOT NULL default '',
1420 `categorycode` varchar(10) NOT NULL default '',
1421 `delay1` int(4) default NULL,
1422 `letter1` varchar(20) default NULL,
1423 `debarred1` varchar(1) default 0,
1424 `delay2` int(4) default NULL,
1425 `debarred2` varchar(1) default 0,
1426 `letter2` varchar(20) default NULL,
1427 `delay3` int(4) default NULL,
1428 `letter3` varchar(20) default NULL,
1429 `debarred3` int(1) default 0,
1430 PRIMARY KEY (`branchcode`,`categorycode`)
1431 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1434 -- Table structure for table `patroncards`
1437 DROP TABLE IF EXISTS `patroncards`;
1438 CREATE TABLE `patroncards` (
1439 `cardid` int(11) NOT NULL auto_increment,
1440 `batch_id` varchar(10) NOT NULL default '1',
1441 `borrowernumber` int(11) NOT NULL,
1442 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1443 PRIMARY KEY (`cardid`),
1444 KEY `patroncards_ibfk_1` (`borrowernumber`),
1445 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1446 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1449 -- Table structure for table `patronimage`
1452 DROP TABLE IF EXISTS `patronimage`;
1453 CREATE TABLE `patronimage` (
1454 `cardnumber` varchar(16) NOT NULL,
1455 `mimetype` varchar(15) NOT NULL,
1456 `imagefile` mediumblob NOT NULL,
1457 PRIMARY KEY (`cardnumber`),
1458 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1459 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1462 -- Table structure for table `printers`
1465 DROP TABLE IF EXISTS `printers`;
1466 CREATE TABLE `printers` (
1467 `printername` varchar(40) NOT NULL default '',
1468 `printqueue` varchar(20) default NULL,
1469 `printtype` varchar(20) default NULL,
1470 PRIMARY KEY (`printername`)
1471 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1474 -- Table structure for table `printers_profile`
1477 DROP TABLE IF EXISTS `printers_profile`;
1478 CREATE TABLE `printers_profile` (
1479 `profile_id` int(4) NOT NULL auto_increment,
1480 `printer_name` varchar(40) NOT NULL default 'Default Printer',
1481 `template_id` int(4) NOT NULL default '0',
1482 `paper_bin` varchar(20) NOT NULL default 'Bypass',
1483 `offset_horz` float NOT NULL default '0',
1484 `offset_vert` float NOT NULL default '0',
1485 `creep_horz` float NOT NULL default '0',
1486 `creep_vert` float NOT NULL default '0',
1487 `units` char(20) NOT NULL default 'POINT',
1488 `creator` char(15) NOT NULL DEFAULT 'Labels',
1489 PRIMARY KEY (`profile_id`),
1490 UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
1491 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1494 -- Table structure for table `repeatable_holidays`
1497 DROP TABLE IF EXISTS `repeatable_holidays`;
1498 CREATE TABLE `repeatable_holidays` (
1499 `id` int(11) NOT NULL auto_increment,
1500 `branchcode` varchar(10) NOT NULL default '',
1501 `weekday` smallint(6) default NULL,
1502 `day` smallint(6) default NULL,
1503 `month` smallint(6) default NULL,
1504 `title` varchar(50) NOT NULL default '',
1505 `description` text NOT NULL,
1507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1510 -- Table structure for table `reports_dictionary`
1513 DROP TABLE IF EXISTS `reports_dictionary`;
1514 CREATE TABLE reports_dictionary (
1515 `id` int(11) NOT NULL auto_increment,
1516 `name` varchar(255) default NULL,
1518 `date_created` datetime default NULL,
1519 `date_modified` datetime default NULL,
1521 `area` int(11) default NULL,
1523 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1526 -- Table structure for table `reserveconstraints`
1529 DROP TABLE IF EXISTS `reserveconstraints`;
1530 CREATE TABLE `reserveconstraints` (
1531 `borrowernumber` int(11) NOT NULL default 0,
1532 `reservedate` date default NULL,
1533 `biblionumber` int(11) NOT NULL default 0,
1534 `biblioitemnumber` int(11) default NULL,
1535 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
1536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1539 -- Table structure for table `reserves`
1542 DROP TABLE IF EXISTS `reserves`;
1543 CREATE TABLE `reserves` (
1544 `borrowernumber` int(11) NOT NULL default 0,
1545 `reservedate` date default NULL,
1546 `biblionumber` int(11) NOT NULL default 0,
1547 `constrainttype` varchar(1) default NULL,
1548 `branchcode` varchar(10) default NULL,
1549 `notificationdate` date default NULL,
1550 `reminderdate` date default NULL,
1551 `cancellationdate` date default NULL,
1552 `reservenotes` mediumtext,
1553 `priority` smallint(6) default NULL,
1554 `found` varchar(1) default NULL,
1555 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1556 `itemnumber` int(11) default NULL,
1557 `waitingdate` date default NULL,
1558 `expirationdate` DATE DEFAULT NULL,
1559 `lowestPriority` tinyint(1) NOT NULL,
1560 KEY priorityfoundidx (priority,found),
1561 KEY `borrowernumber` (`borrowernumber`),
1562 KEY `biblionumber` (`biblionumber`),
1563 KEY `itemnumber` (`itemnumber`),
1564 KEY `branchcode` (`branchcode`),
1565 CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1566 CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1567 CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1568 CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1569 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1572 -- Table structure for table `reviews`
1575 DROP TABLE IF EXISTS `reviews`;
1576 CREATE TABLE `reviews` (
1577 `reviewid` int(11) NOT NULL auto_increment,
1578 `borrowernumber` int(11) default NULL,
1579 `biblionumber` int(11) default NULL,
1581 `approved` tinyint(4) default NULL,
1582 `datereviewed` datetime default NULL,
1583 PRIMARY KEY (`reviewid`)
1584 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1587 -- Table structure for table `roadtype`
1590 DROP TABLE IF EXISTS `roadtype`;
1591 CREATE TABLE `roadtype` (
1592 `roadtypeid` int(11) NOT NULL auto_increment,
1593 `road_type` varchar(100) NOT NULL default '',
1594 PRIMARY KEY (`roadtypeid`)
1595 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1598 -- Table structure for table `saved_sql`
1601 DROP TABLE IF EXISTS `saved_sql`;
1602 CREATE TABLE saved_sql (
1603 `id` int(11) NOT NULL auto_increment,
1604 `borrowernumber` int(11) default NULL,
1605 `date_created` datetime default NULL,
1606 `last_modified` datetime default NULL,
1608 `last_run` datetime default NULL,
1609 `report_name` varchar(255) default NULL,
1610 `type` varchar(255) default NULL,
1613 KEY boridx (`borrowernumber`)
1614 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1618 -- Table structure for `saved_reports`
1621 DROP TABLE IF EXISTS `saved_reports`;
1622 CREATE TABLE saved_reports (
1623 `id` int(11) NOT NULL auto_increment,
1624 `report_id` int(11) default NULL,
1626 `date_run` datetime default NULL,
1628 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1632 -- Table structure for table `search_history`
1635 DROP TABLE IF EXISTS `search_history`;
1636 CREATE TABLE IF NOT EXISTS `search_history` (
1637 `userid` int(11) NOT NULL,
1638 `sessionid` varchar(32) NOT NULL,
1639 `query_desc` varchar(255) NOT NULL,
1640 `query_cgi` varchar(255) NOT NULL,
1641 `total` int(11) NOT NULL,
1642 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
1643 KEY `userid` (`userid`),
1644 KEY `sessionid` (`sessionid`)
1645 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
1649 -- Table structure for table `serial`
1652 DROP TABLE IF EXISTS `serial`;
1653 CREATE TABLE `serial` (
1654 `serialid` int(11) NOT NULL auto_increment,
1655 `biblionumber` varchar(100) NOT NULL default '',
1656 `subscriptionid` varchar(100) NOT NULL default '',
1657 `serialseq` varchar(100) NOT NULL default '',
1658 `status` tinyint(4) NOT NULL default 0,
1659 `planneddate` date default NULL,
1661 `publisheddate` date default NULL,
1662 `itemnumber` text default NULL,
1663 `claimdate` date default NULL,
1664 `routingnotes` text,
1665 PRIMARY KEY (`serialid`)
1666 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1669 -- Table structure for table `sessions`
1672 DROP TABLE IF EXISTS sessions;
1673 CREATE TABLE sessions (
1674 `id` varchar(32) NOT NULL,
1675 `a_session` text NOT NULL,
1677 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1680 -- Table structure for table `special_holidays`
1683 DROP TABLE IF EXISTS `special_holidays`;
1684 CREATE TABLE `special_holidays` (
1685 `id` int(11) NOT NULL auto_increment,
1686 `branchcode` varchar(10) NOT NULL default '',
1687 `day` smallint(6) NOT NULL default 0,
1688 `month` smallint(6) NOT NULL default 0,
1689 `year` smallint(6) NOT NULL default 0,
1690 `isexception` smallint(1) NOT NULL default 1,
1691 `title` varchar(50) NOT NULL default '',
1692 `description` text NOT NULL,
1694 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1697 -- Table structure for table `statistics`
1700 DROP TABLE IF EXISTS `statistics`;
1701 CREATE TABLE `statistics` (
1702 `datetime` datetime default NULL,
1703 `branch` varchar(10) default NULL,
1704 `proccode` varchar(4) default NULL,
1705 `value` double(16,4) default NULL,
1706 `type` varchar(16) default NULL,
1708 `usercode` varchar(10) default NULL,
1709 `itemnumber` int(11) default NULL,
1710 `itemtype` varchar(10) default NULL,
1711 `borrowernumber` int(11) default NULL,
1712 `associatedborrower` int(11) default NULL,
1713 KEY `timeidx` (`datetime`)
1714 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1717 -- Table structure for table `stopwords`
1720 DROP TABLE IF EXISTS `stopwords`;
1721 CREATE TABLE `stopwords` (
1722 `word` varchar(255) default NULL
1723 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1726 -- Table structure for table `subscription`
1729 DROP TABLE IF EXISTS `subscription`;
1730 CREATE TABLE `subscription` (
1731 `biblionumber` int(11) NOT NULL default 0,
1732 `subscriptionid` int(11) NOT NULL auto_increment,
1733 `librarian` varchar(100) default '',
1734 `startdate` date default NULL,
1735 `aqbooksellerid` int(11) default 0,
1736 `cost` int(11) default 0,
1737 `aqbudgetid` int(11) default 0,
1738 `weeklength` int(11) default 0,
1739 `monthlength` int(11) default 0,
1740 `numberlength` int(11) default 0,
1741 `periodicity` tinyint(4) default 0,
1742 `dow` varchar(100) default '',
1743 `numberingmethod` varchar(100) default '',
1745 `status` varchar(100) NOT NULL default '',
1746 `add1` int(11) default 0,
1747 `every1` int(11) default 0,
1748 `whenmorethan1` int(11) default 0,
1749 `setto1` int(11) default NULL,
1750 `lastvalue1` int(11) default NULL,
1751 `add2` int(11) default 0,
1752 `every2` int(11) default 0,
1753 `whenmorethan2` int(11) default 0,
1754 `setto2` int(11) default NULL,
1755 `lastvalue2` int(11) default NULL,
1756 `add3` int(11) default 0,
1757 `every3` int(11) default 0,
1758 `innerloop1` int(11) default 0,
1759 `innerloop2` int(11) default 0,
1760 `innerloop3` int(11) default 0,
1761 `whenmorethan3` int(11) default 0,
1762 `setto3` int(11) default NULL,
1763 `lastvalue3` int(11) default NULL,
1764 `issuesatonce` tinyint(3) NOT NULL default 1,
1765 `firstacquidate` date default NULL,
1766 `manualhistory` tinyint(1) NOT NULL default 0,
1767 `irregularity` text,
1768 `letter` varchar(20) default NULL,
1769 `numberpattern` tinyint(3) default 0,
1770 `distributedto` text,
1771 `internalnotes` longtext,
1773 `location` varchar(80) NULL default '',
1774 `branchcode` varchar(10) NOT NULL default '',
1775 `hemisphere` tinyint(3) default 0,
1776 `lastbranch` varchar(10),
1777 `serialsadditems` tinyint(1) NOT NULL default '0',
1778 `staffdisplaycount` VARCHAR(10) NULL,
1779 `opacdisplaycount` VARCHAR(10) NULL,
1780 `graceperiod` int(11) NOT NULL default '0',
1781 `enddate` date default NULL,
1782 PRIMARY KEY (`subscriptionid`)
1783 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1786 -- Table structure for table `subscriptionhistory`
1789 DROP TABLE IF EXISTS `subscriptionhistory`;
1790 CREATE TABLE `subscriptionhistory` (
1791 `biblionumber` int(11) NOT NULL default 0,
1792 `subscriptionid` int(11) NOT NULL default 0,
1793 `histstartdate` date default NULL,
1794 `histenddate` date default NULL,
1795 `missinglist` longtext NOT NULL,
1796 `recievedlist` longtext NOT NULL,
1797 `opacnote` varchar(150) NOT NULL default '',
1798 `librariannote` varchar(150) NOT NULL default '',
1799 PRIMARY KEY (`subscriptionid`),
1800 KEY `biblionumber` (`biblionumber`)
1801 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1804 -- Table structure for table `subscriptionroutinglist`
1807 DROP TABLE IF EXISTS `subscriptionroutinglist`;
1808 CREATE TABLE `subscriptionroutinglist` (
1809 `routingid` int(11) NOT NULL auto_increment,
1810 `borrowernumber` int(11) NOT NULL,
1811 `ranking` int(11) default NULL,
1812 `subscriptionid` int(11) NOT NULL,
1813 PRIMARY KEY (`routingid`),
1814 UNIQUE (`subscriptionid`, `borrowernumber`),
1815 CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1816 ON DELETE CASCADE ON UPDATE CASCADE,
1817 CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
1818 ON DELETE CASCADE ON UPDATE CASCADE
1819 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1822 -- Table structure for table `suggestions`
1825 DROP TABLE IF EXISTS `suggestions`;
1826 CREATE TABLE `suggestions` (
1827 `suggestionid` int(8) NOT NULL auto_increment,
1828 `suggestedby` int(11) NOT NULL default 0,
1829 `suggesteddate` date NOT NULL default 0,
1830 `managedby` int(11) default NULL,
1831 `manageddate` date default NULL,
1832 acceptedby INT(11) default NULL,
1833 accepteddate date default NULL,
1834 rejectedby INT(11) default NULL,
1835 rejecteddate date default NULL,
1836 `STATUS` varchar(10) NOT NULL default '',
1838 `author` varchar(80) default NULL,
1839 `title` varchar(80) default NULL,
1840 `copyrightdate` smallint(6) default NULL,
1841 `publishercode` varchar(255) default NULL,
1842 `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1843 `volumedesc` varchar(255) default NULL,
1844 `publicationyear` smallint(6) default 0,
1845 `place` varchar(255) default NULL,
1846 `isbn` varchar(30) default NULL,
1847 `mailoverseeing` smallint(1) default 0,
1848 `biblionumber` int(11) default NULL,
1851 branchcode VARCHAR(10) default NULL,
1852 collectiontitle text default NULL,
1853 itemtype VARCHAR(30) default NULL,
1854 quantity SMALLINT(6) default NULL,
1855 currency VARCHAR(3) default NULL,
1856 price DECIMAL(28,6) default NULL,
1857 total DECIMAL(28,6) default NULL,
1858 PRIMARY KEY (`suggestionid`),
1859 KEY `suggestedby` (`suggestedby`),
1860 KEY `managedby` (`managedby`)
1861 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1864 -- Table structure for table `systempreferences`
1867 DROP TABLE IF EXISTS `systempreferences`;
1868 CREATE TABLE `systempreferences` (
1869 `variable` varchar(50) NOT NULL default '',
1871 `options` mediumtext,
1873 `type` varchar(20) default NULL,
1874 PRIMARY KEY (`variable`)
1875 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1878 -- Table structure for table `tags`
1881 DROP TABLE IF EXISTS `tags`;
1882 CREATE TABLE `tags` (
1883 `entry` varchar(255) NOT NULL default '',
1884 `weight` bigint(20) NOT NULL default 0,
1885 PRIMARY KEY (`entry`)
1886 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1889 -- Table structure for table `tags_all`
1892 DROP TABLE IF EXISTS `tags_all`;
1893 CREATE TABLE `tags_all` (
1894 `tag_id` int(11) NOT NULL auto_increment,
1895 `borrowernumber` int(11) NOT NULL,
1896 `biblionumber` int(11) NOT NULL,
1897 `term` varchar(255) NOT NULL,
1898 `language` int(4) default NULL,
1899 `date_created` datetime NOT NULL,
1900 PRIMARY KEY (`tag_id`),
1901 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1902 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1903 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1904 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1905 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1906 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1907 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1910 -- Table structure for table `tags_approval`
1913 DROP TABLE IF EXISTS `tags_approval`;
1914 CREATE TABLE `tags_approval` (
1915 `term` varchar(255) NOT NULL,
1916 `approved` int(1) NOT NULL default '0',
1917 `date_approved` datetime default NULL,
1918 `approved_by` int(11) default NULL,
1919 `weight_total` int(9) NOT NULL default '1',
1920 PRIMARY KEY (`term`),
1921 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1922 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1923 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1924 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1927 -- Table structure for table `tags_index`
1930 DROP TABLE IF EXISTS `tags_index`;
1931 CREATE TABLE `tags_index` (
1932 `term` varchar(255) NOT NULL,
1933 `biblionumber` int(11) NOT NULL,
1934 `weight` int(9) NOT NULL default '1',
1935 PRIMARY KEY (`term`,`biblionumber`),
1936 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1937 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1938 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1939 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1940 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1941 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1944 -- Table structure for table `userflags`
1947 DROP TABLE IF EXISTS `userflags`;
1948 CREATE TABLE `userflags` (
1949 `bit` int(11) NOT NULL default 0,
1950 `flag` varchar(30) default NULL,
1951 `flagdesc` varchar(255) default NULL,
1952 `defaulton` int(11) default NULL,
1954 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1957 -- Table structure for table `virtualshelves`
1960 DROP TABLE IF EXISTS `virtualshelves`;
1961 CREATE TABLE `virtualshelves` (
1962 `shelfnumber` int(11) NOT NULL auto_increment,
1963 `shelfname` varchar(255) default NULL,
1964 `owner` varchar(80) default NULL,
1965 `category` varchar(1) default NULL,
1966 `sortfield` varchar(16) default NULL,
1967 `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1968 PRIMARY KEY (`shelfnumber`)
1969 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1972 -- Table structure for table `virtualshelfcontents`
1975 DROP TABLE IF EXISTS `virtualshelfcontents`;
1976 CREATE TABLE `virtualshelfcontents` (
1977 `shelfnumber` int(11) NOT NULL default 0,
1978 `biblionumber` int(11) NOT NULL default 0,
1979 `flags` int(11) default NULL,
1980 `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1981 KEY `shelfnumber` (`shelfnumber`),
1982 KEY `biblionumber` (`biblionumber`),
1983 CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1984 CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1985 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1988 -- Table structure for table `z3950servers`
1991 DROP TABLE IF EXISTS `z3950servers`;
1992 CREATE TABLE `z3950servers` (
1993 `host` varchar(255) default NULL,
1994 `port` int(11) default NULL,
1995 `db` varchar(255) default NULL,
1996 `userid` varchar(255) default NULL,
1997 `password` varchar(255) default NULL,
1999 `id` int(11) NOT NULL auto_increment,
2000 `checked` smallint(6) default NULL,
2001 `rank` int(11) default NULL,
2002 `syntax` varchar(80) default NULL,
2004 `position` enum('primary','secondary','') NOT NULL default 'primary',
2005 `type` enum('zed','opensearch') NOT NULL default 'zed',
2006 `encoding` text default NULL,
2007 `description` text NOT NULL,
2009 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2012 -- Table structure for table `zebraqueue`
2015 DROP TABLE IF EXISTS `zebraqueue`;
2016 CREATE TABLE `zebraqueue` (
2017 `id` int(11) NOT NULL auto_increment,
2018 `biblio_auth_number` bigint(20) unsigned NOT NULL default '0',
2019 `operation` char(20) NOT NULL default '',
2020 `server` char(20) NOT NULL default '',
2021 `done` int(11) NOT NULL default '0',
2022 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2024 KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
2025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2027 DROP TABLE IF EXISTS `services_throttle`;
2028 CREATE TABLE `services_throttle` (
2029 `service_type` varchar(10) NOT NULL default '',
2030 `service_count` varchar(45) default NULL,
2031 PRIMARY KEY (`service_type`)
2032 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2034 -- http://www.w3.org/International/articles/language-tags/
2037 DROP TABLE IF EXISTS language_subtag_registry;
2038 CREATE TABLE language_subtag_registry (
2040 type varchar(25), -- language-script-region-variant-extension-privateuse
2041 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
2043 id int(11) NOT NULL auto_increment,
2045 KEY `subtag` (`subtag`)
2046 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2048 -- TODO: add suppress_scripts
2049 -- this maps three letter codes defined in iso639.2 back to their
2050 -- two letter equivilents in rfc4646 (LOC maintains iso639+)
2051 DROP TABLE IF EXISTS language_rfc4646_to_iso639;
2052 CREATE TABLE language_rfc4646_to_iso639 (
2053 rfc4646_subtag varchar(25),
2054 iso639_2_code varchar(25),
2055 id int(11) NOT NULL auto_increment,
2057 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2058 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2060 DROP TABLE IF EXISTS language_descriptions;
2061 CREATE TABLE language_descriptions (
2065 description varchar(255),
2066 id int(11) NOT NULL auto_increment,
2068 KEY `lang` (`lang`),
2069 KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
2070 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2072 -- bi-directional support, keyed by script subcode
2073 DROP TABLE IF EXISTS language_script_bidi;
2074 CREATE TABLE language_script_bidi (
2075 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
2076 bidi varchar(3), -- rtl ltr
2077 KEY `rfc4646_subtag` (`rfc4646_subtag`)
2078 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2080 -- TODO: need to map language subtags to script subtags for detection
2081 -- of bidi when script is not specified (like ar, he)
2082 DROP TABLE IF EXISTS language_script_mapping;
2083 CREATE TABLE language_script_mapping (
2084 language_subtag varchar(25),
2085 script_subtag varchar(25),
2086 KEY `language_subtag` (`language_subtag`)
2087 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2089 DROP TABLE IF EXISTS `permissions`;
2090 CREATE TABLE `permissions` (
2091 `module_bit` int(11) NOT NULL DEFAULT 0,
2092 `code` varchar(64) DEFAULT NULL,
2093 `description` varchar(255) DEFAULT NULL,
2094 PRIMARY KEY (`module_bit`, `code`),
2095 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
2096 ON DELETE CASCADE ON UPDATE CASCADE
2097 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2099 DROP TABLE IF EXISTS `serialitems`;
2100 CREATE TABLE `serialitems` (
2101 `itemnumber` int(11) NOT NULL,
2102 `serialid` int(11) NOT NULL,
2103 UNIQUE KEY `serialitemsidx` (`itemnumber`),
2104 KEY `serialitems_sfk_1` (`serialid`),
2105 CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
2106 CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
2107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2109 DROP TABLE IF EXISTS `user_permissions`;
2110 CREATE TABLE `user_permissions` (
2111 `borrowernumber` int(11) NOT NULL DEFAULT 0,
2112 `module_bit` int(11) NOT NULL DEFAULT 0,
2113 `code` varchar(64) DEFAULT NULL,
2114 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
2115 ON DELETE CASCADE ON UPDATE CASCADE,
2116 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
2117 ON DELETE CASCADE ON UPDATE CASCADE
2118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2121 -- Table structure for table `tmp_holdsqueue`
2124 DROP TABLE IF EXISTS `tmp_holdsqueue`;
2125 CREATE TABLE `tmp_holdsqueue` (
2126 `biblionumber` int(11) default NULL,
2127 `itemnumber` int(11) default NULL,
2128 `barcode` varchar(20) default NULL,
2129 `surname` mediumtext NOT NULL,
2132 `borrowernumber` int(11) NOT NULL,
2133 `cardnumber` varchar(16) default NULL,
2134 `reservedate` date default NULL,
2136 `itemcallnumber` varchar(255) default NULL,
2137 `holdingbranch` varchar(10) default NULL,
2138 `pickbranch` varchar(10) default NULL,
2140 `item_level_request` tinyint(4) NOT NULL default 0
2141 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2144 -- Table structure for table `message_queue`
2147 DROP TABLE IF EXISTS `message_queue`;
2148 CREATE TABLE `message_queue` (
2149 `message_id` int(11) NOT NULL auto_increment,
2150 `borrowernumber` int(11) default NULL,
2153 `metadata` text DEFAULT NULL,
2154 `letter_code` varchar(64) DEFAULT NULL,
2155 `message_transport_type` varchar(20) NOT NULL,
2156 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
2157 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2158 `to_address` mediumtext,
2159 `from_address` mediumtext,
2160 `content_type` text,
2161 KEY `message_id` (`message_id`),
2162 KEY `borrowernumber` (`borrowernumber`),
2163 KEY `message_transport_type` (`message_transport_type`),
2164 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2165 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
2166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2169 -- Table structure for table `message_transport_types`
2172 DROP TABLE IF EXISTS `message_transport_types`;
2173 CREATE TABLE `message_transport_types` (
2174 `message_transport_type` varchar(20) NOT NULL,
2175 PRIMARY KEY (`message_transport_type`)
2176 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2179 -- Table structure for table `message_attributes`
2182 DROP TABLE IF EXISTS `message_attributes`;
2183 CREATE TABLE `message_attributes` (
2184 `message_attribute_id` int(11) NOT NULL auto_increment,
2185 `message_name` varchar(40) NOT NULL default '',
2186 `takes_days` tinyint(1) NOT NULL default '0',
2187 PRIMARY KEY (`message_attribute_id`),
2188 UNIQUE KEY `message_name` (`message_name`)
2189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2192 -- Table structure for table `message_transports`
2195 DROP TABLE IF EXISTS `message_transports`;
2196 CREATE TABLE `message_transports` (
2197 `message_attribute_id` int(11) NOT NULL,
2198 `message_transport_type` varchar(20) NOT NULL,
2199 `is_digest` tinyint(1) NOT NULL default '0',
2200 `letter_module` varchar(20) NOT NULL default '',
2201 `letter_code` varchar(20) NOT NULL default '',
2202 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
2203 KEY `message_transport_type` (`message_transport_type`),
2204 KEY `letter_module` (`letter_module`,`letter_code`),
2205 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2206 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
2207 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
2208 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2211 -- Table structure for table `borrower_message_preferences`
2214 DROP TABLE IF EXISTS `borrower_message_preferences`;
2215 CREATE TABLE `borrower_message_preferences` (
2216 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
2217 `borrowernumber` int(11) default NULL,
2218 `categorycode` varchar(10) default NULL,
2219 `message_attribute_id` int(11) default '0',
2220 `days_in_advance` int(11) default '0',
2221 `wants_digest` tinyint(1) NOT NULL default '0',
2222 PRIMARY KEY (`borrower_message_preference_id`),
2223 KEY `borrowernumber` (`borrowernumber`),
2224 KEY `categorycode` (`categorycode`),
2225 KEY `message_attribute_id` (`message_attribute_id`),
2226 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2227 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
2228 CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
2229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2232 -- Table structure for table `borrower_message_transport_preferences`
2235 DROP TABLE IF EXISTS `borrower_message_transport_preferences`;
2236 CREATE TABLE `borrower_message_transport_preferences` (
2237 `borrower_message_preference_id` int(11) NOT NULL default '0',
2238 `message_transport_type` varchar(20) NOT NULL default '0',
2239 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
2240 KEY `message_transport_type` (`message_transport_type`),
2241 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,
2242 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
2243 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2246 -- Table structure for the table branch_transfer_limits
2249 DROP TABLE IF EXISTS `branch_transfer_limits`;
2250 CREATE TABLE branch_transfer_limits (
2251 limitId int(8) NOT NULL auto_increment,
2252 toBranch varchar(10) NOT NULL,
2253 fromBranch varchar(10) NOT NULL,
2254 itemtype varchar(10) NULL,
2255 ccode varchar(10) NULL,
2256 PRIMARY KEY (limitId)
2257 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2260 -- Table structure for table `item_circulation_alert_preferences`
2263 DROP TABLE IF EXISTS `item_circulation_alert_preferences`;
2264 CREATE TABLE `item_circulation_alert_preferences` (
2265 `id` int(11) NOT NULL auto_increment,
2266 `branchcode` varchar(10) NOT NULL,
2267 `categorycode` varchar(10) NOT NULL,
2268 `item_type` varchar(10) NOT NULL,
2269 `notification` varchar(16) NOT NULL,
2271 KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
2272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2275 -- Table structure for table `messages`
2277 DROP TABLE IF EXISTS `messages`;
2278 CREATE TABLE `messages` (
2279 `message_id` int(11) NOT NULL auto_increment,
2280 `borrowernumber` int(11) NOT NULL,
2281 `branchcode` varchar(10) default NULL,
2282 `message_type` varchar(1) NOT NULL,
2283 `message` text NOT NULL,
2284 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2285 PRIMARY KEY (`message_id`)
2286 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2289 -- Table structure for table `accountlines`
2292 DROP TABLE IF EXISTS `accountlines`;
2293 CREATE TABLE `accountlines` (
2294 `borrowernumber` int(11) NOT NULL default 0,
2295 `accountno` smallint(6) NOT NULL default 0,
2296 `itemnumber` int(11) default NULL,
2297 `date` date default NULL,
2298 `amount` decimal(28,6) default NULL,
2299 `description` mediumtext,
2300 `dispute` mediumtext,
2301 `accounttype` varchar(5) default NULL,
2302 `amountoutstanding` decimal(28,6) default NULL,
2303 `lastincrement` decimal(28,6) default NULL,
2304 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2305 `notify_id` int(11) NOT NULL default 0,
2306 `notify_level` int(2) NOT NULL default 0,
2307 `note` text NULL default NULL,
2308 `manager_id` int(11) NULL,
2309 KEY `acctsborridx` (`borrowernumber`),
2310 KEY `timeidx` (`timestamp`),
2311 KEY `itemnumber` (`itemnumber`),
2312 CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2313 CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
2314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2317 -- Table structure for table `accountoffsets`
2320 DROP TABLE IF EXISTS `accountoffsets`;
2321 CREATE TABLE `accountoffsets` (
2322 `borrowernumber` int(11) NOT NULL default 0,
2323 `accountno` smallint(6) NOT NULL default 0,
2324 `offsetaccount` smallint(6) NOT NULL default 0,
2325 `offsetamount` decimal(28,6) default NULL,
2326 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2327 CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
2328 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2331 -- Table structure for table `action_logs`
2334 DROP TABLE IF EXISTS `action_logs`;
2335 CREATE TABLE `action_logs` (
2336 `action_id` int(11) NOT NULL auto_increment,
2337 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2338 `user` int(11) NOT NULL default 0,
2341 `object` int(11) default NULL,
2343 PRIMARY KEY (`action_id`),
2344 KEY (`timestamp`,`user`)
2345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2348 -- Table structure for table `alert`
2351 DROP TABLE IF EXISTS `alert`;
2352 CREATE TABLE `alert` (
2353 `alertid` int(11) NOT NULL auto_increment,
2354 `borrowernumber` int(11) NOT NULL default 0,
2355 `type` varchar(10) NOT NULL default '',
2356 `externalid` varchar(20) NOT NULL default '',
2357 PRIMARY KEY (`alertid`),
2358 KEY `borrowernumber` (`borrowernumber`),
2359 KEY `type` (`type`,`externalid`)
2360 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2363 -- Table structure for table `aqbasketgroups`
2366 DROP TABLE IF EXISTS `aqbasketgroups`;
2367 CREATE TABLE `aqbasketgroups` (
2368 `id` int(11) NOT NULL auto_increment,
2369 `name` varchar(50) default NULL,
2370 `closed` tinyint(1) default NULL,
2371 `booksellerid` int(11) NOT NULL,
2372 `deliveryplace` varchar(10) default NULL,
2373 `freedeliveryplace` text default NULL,
2374 `deliverycomment` varchar(255) default NULL,
2375 `billingplace` varchar(10) default NULL,
2377 KEY `booksellerid` (`booksellerid`),
2378 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2382 -- Table structure for table `aqbasket`
2385 DROP TABLE IF EXISTS `aqbasket`;
2386 CREATE TABLE `aqbasket` (
2387 `basketno` int(11) NOT NULL auto_increment,
2388 `basketname` varchar(50) default NULL,
2390 `booksellernote` mediumtext,
2391 `contractnumber` int(11),
2392 `creationdate` date default NULL,
2393 `closedate` date default NULL,
2394 `booksellerid` int(11) NOT NULL default 1,
2395 `authorisedby` varchar(10) default NULL,
2396 `booksellerinvoicenumber` mediumtext,
2397 `basketgroupid` int(11),
2398 PRIMARY KEY (`basketno`),
2399 KEY `booksellerid` (`booksellerid`),
2400 KEY `basketgroupid` (`basketgroupid`),
2401 KEY `contractnumber` (`contractnumber`),
2402 CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE,
2403 CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`),
2404 CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE
2405 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2408 -- Table structure for table `aqbooksellers`
2411 DROP TABLE IF EXISTS `aqbooksellers`;
2412 CREATE TABLE `aqbooksellers` (
2413 `id` int(11) NOT NULL auto_increment,
2414 `name` mediumtext NOT NULL,
2415 `address1` mediumtext,
2416 `address2` mediumtext,
2417 `address3` mediumtext,
2418 `address4` mediumtext,
2419 `phone` varchar(30) default NULL,
2420 `accountnumber` mediumtext,
2421 `othersupplier` mediumtext,
2422 `currency` varchar(3) NOT NULL default '',
2423 `booksellerfax` mediumtext,
2425 `bookselleremail` mediumtext,
2426 `booksellerurl` mediumtext,
2427 `contact` varchar(100) default NULL,
2428 `postal` mediumtext,
2429 `url` varchar(255) default NULL,
2430 `contpos` varchar(100) default NULL,
2431 `contphone` varchar(100) default NULL,
2432 `contfax` varchar(100) default NULL,
2433 `contaltphone` varchar(100) default NULL,
2434 `contemail` varchar(100) default NULL,
2435 `contnotes` mediumtext,
2436 `active` tinyint(4) default NULL,
2437 `listprice` varchar(10) default NULL,
2438 `invoiceprice` varchar(10) default NULL,
2439 `gstreg` tinyint(4) default NULL,
2440 `listincgst` tinyint(4) default NULL,
2441 `invoiceincgst` tinyint(4) default NULL,
2442 `gstrate` decimal(6,4) default NULL,
2443 `discount` float(6,4) default NULL,
2444 `fax` varchar(50) default NULL,
2446 KEY `listprice` (`listprice`),
2447 KEY `invoiceprice` (`invoiceprice`),
2448 CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
2449 CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
2450 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2453 -- Table structure for table `aqbudgets`
2456 DROP TABLE IF EXISTS `aqbudgets`;
2457 CREATE TABLE `aqbudgets` (
2458 `budget_id` int(11) NOT NULL auto_increment,
2459 `budget_parent_id` int(11) default NULL,
2460 `budget_code` varchar(30) default NULL,
2461 `budget_name` varchar(80) default NULL,
2462 `budget_branchcode` varchar(10) default NULL,
2463 `budget_amount` decimal(28,6) NULL default '0.00',
2464 `budget_encumb` decimal(28,6) NULL default '0.00',
2465 `budget_expend` decimal(28,6) NULL default '0.00',
2466 `budget_notes` mediumtext,
2467 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2468 `budget_period_id` int(11) default NULL,
2469 `sort1_authcat` varchar(80) default NULL,
2470 `sort2_authcat` varchar(80) default NULL,
2471 `budget_owner_id` int(11) default NULL,
2472 `budget_permission` int(1) default '0',
2473 PRIMARY KEY (`budget_id`)
2474 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2478 -- Table structure for table `aqbudgetperiods`
2482 DROP TABLE IF EXISTS `aqbudgetperiods`;
2483 CREATE TABLE `aqbudgetperiods` (
2484 `budget_period_id` int(11) NOT NULL auto_increment,
2485 `budget_period_startdate` date NOT NULL,
2486 `budget_period_enddate` date NOT NULL,
2487 `budget_period_active` tinyint(1) default '0',
2488 `budget_period_description` mediumtext,
2489 `budget_period_total` decimal(28,6),
2490 `budget_period_locked` tinyint(1) default NULL,
2491 `sort1_authcat` varchar(10) default NULL,
2492 `sort2_authcat` varchar(10) default NULL,
2493 PRIMARY KEY (`budget_period_id`)
2494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2497 -- Table structure for table `aqbudgets_planning`
2500 DROP TABLE IF EXISTS `aqbudgets_planning`;
2501 CREATE TABLE `aqbudgets_planning` (
2502 `plan_id` int(11) NOT NULL auto_increment,
2503 `budget_id` int(11) NOT NULL,
2504 `budget_period_id` int(11) NOT NULL,
2505 `estimated_amount` decimal(28,6) default NULL,
2506 `authcat` varchar(30) NOT NULL,
2507 `authvalue` varchar(30) NOT NULL,
2508 `display` tinyint(1) DEFAULT 1,
2509 PRIMARY KEY (`plan_id`),
2510 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2511 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2514 -- Table structure for table 'aqcontract'
2517 DROP TABLE IF EXISTS `aqcontract`;
2518 CREATE TABLE `aqcontract` (
2519 `contractnumber` int(11) NOT NULL auto_increment,
2520 `contractstartdate` date default NULL,
2521 `contractenddate` date default NULL,
2522 `contractname` varchar(50) default NULL,
2523 `contractdescription` mediumtext,
2524 `booksellerid` int(11) not NULL,
2525 PRIMARY KEY (`contractnumber`),
2526 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2527 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2531 -- Table structure for table `aqorderdelivery`
2534 DROP TABLE IF EXISTS `aqorderdelivery`;
2535 CREATE TABLE `aqorderdelivery` (
2536 `ordernumber` date default NULL,
2537 `deliverynumber` smallint(6) NOT NULL default 0,
2538 `deliverydate` varchar(18) default NULL,
2539 `qtydelivered` smallint(6) default NULL,
2540 `deliverycomments` mediumtext
2541 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2544 -- Table structure for table `aqorders`
2547 DROP TABLE IF EXISTS `aqorders`;
2548 CREATE TABLE `aqorders` (
2549 `ordernumber` int(11) NOT NULL auto_increment,
2550 `biblionumber` int(11) default NULL,
2551 `entrydate` date default NULL,
2552 `quantity` smallint(6) default NULL,
2553 `currency` varchar(3) default NULL,
2554 `listprice` decimal(28,6) default NULL,
2555 `totalamount` decimal(28,6) default NULL,
2556 `datereceived` date default NULL,
2557 `booksellerinvoicenumber` mediumtext,
2558 `freight` decimal(28,6) default NULL,
2559 `unitprice` decimal(28,6) default NULL,
2560 `quantityreceived` smallint(6) NOT NULL default 0,
2561 `cancelledby` varchar(10) default NULL,
2562 `datecancellationprinted` date default NULL,
2564 `supplierreference` mediumtext,
2565 `purchaseordernumber` mediumtext,
2566 `subscription` tinyint(1) default NULL,
2567 `serialid` varchar(30) default NULL,
2568 `basketno` int(11) default NULL,
2569 `biblioitemnumber` int(11) default NULL,
2570 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2571 `rrp` decimal(13,2) default NULL,
2572 `ecost` decimal(13,2) default NULL,
2573 `gst` decimal(13,2) default NULL,
2574 `budget_id` int(11) NOT NULL,
2575 `budgetgroup_id` int(11) NOT NULL,
2576 `budgetdate` date default NULL,
2577 `sort1` varchar(80) default NULL,
2578 `sort2` varchar(80) default NULL,
2579 `sort1_authcat` varchar(10) default NULL,
2580 `sort2_authcat` varchar(10) default NULL,
2581 `uncertainprice` tinyint(1),
2582 PRIMARY KEY (`ordernumber`),
2583 KEY `basketno` (`basketno`),
2584 KEY `biblionumber` (`biblionumber`),
2585 KEY `budget_id` (`budget_id`),
2586 CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
2587 CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
2588 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2592 -- Table structure for table `aqorders_items`
2595 DROP TABLE IF EXISTS `aqorders_items`;
2596 CREATE TABLE `aqorders_items` (
2597 `ordernumber` int(11) NOT NULL,
2598 `itemnumber` int(11) NOT NULL,
2599 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2600 PRIMARY KEY (`itemnumber`),
2601 KEY `ordernumber` (`ordernumber`)
2602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2605 -- Table structure for table `fieldmapping`
2608 DROP TABLE IF EXISTS `fieldmapping`;
2609 CREATE TABLE `fieldmapping` (
2610 `id` int(11) NOT NULL auto_increment,
2611 `field` varchar(255) NOT NULL,
2612 `frameworkcode` char(4) NOT NULL default '',
2613 `fieldcode` char(3) NOT NULL,
2614 `subfieldcode` char(1) NOT NULL,
2616 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2619 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2620 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2621 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2622 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2623 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2624 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2625 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2626 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;