|
Lines 35-40
CREATE TABLE `auth_header` (
Link Here
|
| 35 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
35 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 36 |
|
36 |
|
| 37 |
-- |
37 |
-- |
|
|
38 |
-- Table structure for table `auth_types` |
| 39 |
-- |
| 40 |
|
| 41 |
DROP TABLE IF EXISTS `auth_types`; |
| 42 |
CREATE TABLE `auth_types` ( |
| 43 |
`authtypecode` varchar(10) NOT NULL default '', |
| 44 |
`authtypetext` varchar(255) NOT NULL default '', |
| 45 |
`auth_tag_to_report` varchar(3) NOT NULL default '', |
| 46 |
`summary` mediumtext NOT NULL, |
| 47 |
PRIMARY KEY (`authtypecode`) |
| 48 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 49 |
|
| 50 |
-- |
| 38 |
-- Table structure for table `auth_subfield_structure` |
51 |
-- Table structure for table `auth_subfield_structure` |
| 39 |
-- |
52 |
-- |
| 40 |
|
53 |
|
|
Lines 80-98
CREATE TABLE `auth_tag_structure` (
Link Here
|
| 80 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
93 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 81 |
|
94 |
|
| 82 |
-- |
95 |
-- |
| 83 |
-- Table structure for table `auth_types` |
|
|
| 84 |
-- |
| 85 |
|
| 86 |
DROP TABLE IF EXISTS `auth_types`; |
| 87 |
CREATE TABLE `auth_types` ( |
| 88 |
`authtypecode` varchar(10) NOT NULL default '', |
| 89 |
`authtypetext` varchar(255) NOT NULL default '', |
| 90 |
`auth_tag_to_report` varchar(3) NOT NULL default '', |
| 91 |
`summary` mediumtext NOT NULL, |
| 92 |
PRIMARY KEY (`authtypecode`) |
| 93 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 94 |
|
| 95 |
-- |
| 96 |
-- Table structure for table `authorised_values` |
96 |
-- Table structure for table `authorised_values` |
| 97 |
-- |
97 |
-- |
| 98 |
|
98 |
|
|
Lines 194-287
CREATE TABLE `biblioitems` ( -- information related to bibliographic records in
Link Here
|
| 194 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
194 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 195 |
|
195 |
|
| 196 |
-- |
196 |
-- |
| 197 |
-- Table structure for table `borrowers` |
|
|
| 198 |
-- |
| 199 |
|
| 200 |
DROP TABLE IF EXISTS `borrowers`; |
| 201 |
CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members |
| 202 |
`borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers |
| 203 |
`cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers |
| 204 |
`surname` mediumtext NOT NULL, -- patron/borrower's last name (surname) |
| 205 |
`firstname` text, -- patron/borrower's first name |
| 206 |
`title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs. |
| 207 |
`othernames` mediumtext, -- any other names associated with the patron/borrower |
| 208 |
`initials` text, -- initials for your patron/borrower |
| 209 |
`streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address |
| 210 |
`streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address |
| 211 |
`address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address |
| 212 |
`address2` text, -- the second address line for your patron/borrower's primary address |
| 213 |
`city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address |
| 214 |
`state` text default NULL, -- the state or province for your patron/borrower's primary address |
| 215 |
`zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address |
| 216 |
`country` text, -- the country for your patron/borrower's primary address |
| 217 |
`email` mediumtext, -- the primary email address for your patron/borrower's primary address |
| 218 |
`phone` text, -- the primary phone number for your patron/borrower's primary address |
| 219 |
`mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address |
| 220 |
`fax` mediumtext, -- the fax number for your patron/borrower's primary address |
| 221 |
`emailpro` text, -- the secondary email addres for your patron/borrower's primary address |
| 222 |
`phonepro` text, -- the secondary phone number for your patron/borrower's primary address |
| 223 |
`B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address |
| 224 |
`B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address |
| 225 |
`B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address |
| 226 |
`B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address |
| 227 |
`B_city` mediumtext, -- the city or town for your patron/borrower's alternate address |
| 228 |
`B_state` text default NULL, -- the state for your patron/borrower's alternate address |
| 229 |
`B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address |
| 230 |
`B_country` text, -- the country for your patron/borrower's alternate address |
| 231 |
`B_email` text, -- the patron/borrower's alternate email address |
| 232 |
`B_phone` mediumtext, -- the patron/borrower's alternate phone number |
| 233 |
`dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD) |
| 234 |
`branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch |
| 235 |
`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category |
| 236 |
`dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD) |
| 237 |
`dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD) |
| 238 |
`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 |
| 239 |
`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 |
| 240 |
`debarred` date default NULL, -- until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYY-MM-DD) |
| 241 |
`debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron |
| 242 |
`contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name |
| 243 |
`contactfirstname` text, -- used for children to include first name of guarentor |
| 244 |
`contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor |
| 245 |
`guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations |
| 246 |
`borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client |
| 247 |
`relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor |
| 248 |
`sex` varchar(1) default NULL, -- patron/borrower's gender |
| 249 |
`password` varchar(60) default NULL, -- patron/borrower's encrypted password |
| 250 |
`flags` int(11) default NULL, -- will include a number associated with the staff member's permissions |
| 251 |
`userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in |
| 252 |
`opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client |
| 253 |
`contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address |
| 254 |
`sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library |
| 255 |
`sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library |
| 256 |
`altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower |
| 257 |
`altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower |
| 258 |
`altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower |
| 259 |
`altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower |
| 260 |
`altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower |
| 261 |
`altcontactstate` text default NULL, -- the state for the alternate contact for the patron/borrower |
| 262 |
`altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower |
| 263 |
`altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower |
| 264 |
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower |
| 265 |
`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) |
| 266 |
`sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber |
| 267 |
`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history |
| 268 |
`privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts |
| 269 |
UNIQUE KEY `cardnumber` (`cardnumber`), |
| 270 |
PRIMARY KEY `borrowernumber` (`borrowernumber`), |
| 271 |
KEY `categorycode` (`categorycode`), |
| 272 |
KEY `branchcode` (`branchcode`), |
| 273 |
UNIQUE KEY `userid` (`userid`), |
| 274 |
KEY `guarantorid` (`guarantorid`), |
| 275 |
KEY `surname_idx` (`surname`(255)), |
| 276 |
KEY `firstname_idx` (`firstname`(255)), |
| 277 |
KEY `othernames_idx` (`othernames`(255)), |
| 278 |
KEY `sms_provider_id` (`sms_provider_id`), |
| 279 |
CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), |
| 280 |
CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`), |
| 281 |
CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ON UPDATE CASCADE ON DELETE SET NULL |
| 282 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 283 |
|
| 284 |
-- |
| 285 |
-- Table structure for table `borrower_attribute_types` |
197 |
-- Table structure for table `borrower_attribute_types` |
| 286 |
-- |
198 |
-- |
| 287 |
|
199 |
|
|
Lines 302-364
CREATE TABLE `borrower_attribute_types` ( -- definitions for custom patron field
Link Here
|
| 302 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
214 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 303 |
|
215 |
|
| 304 |
-- |
216 |
-- |
| 305 |
-- Table structure for table `borrower_attributes` |
|
|
| 306 |
-- |
| 307 |
|
| 308 |
DROP TABLE IF EXISTS `borrower_attributes`; |
| 309 |
CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as extended patron attributes linked to patrons/borrowers |
| 310 |
`borrowernumber` int(11) NOT NULL, -- foreign key from the borrowers table, defines which patron/borrower has this attribute |
| 311 |
`code` varchar(10) NOT NULL, -- foreign key from the borrower_attribute_types table, defines which custom field this value was entered for |
| 312 |
`attribute` varchar(255) default NULL, -- custom patron field value |
| 313 |
KEY `borrowernumber` (`borrowernumber`), |
| 314 |
KEY `code_attribute` (`code`, `attribute`), |
| 315 |
CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) |
| 316 |
ON DELETE CASCADE ON UPDATE CASCADE, |
| 317 |
CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`) |
| 318 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 319 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 320 |
|
| 321 |
-- |
| 322 |
-- Table structure for table `borrower_debarments` |
| 323 |
-- |
| 324 |
|
| 325 |
DROP TABLE IF EXISTS `borrower_debarments`; |
| 326 |
CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record |
| 327 |
borrower_debarment_id int(11) NOT NULL AUTO_INCREMENT, -- unique key for the restriction |
| 328 |
borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted |
| 329 |
expiration date DEFAULT NULL, -- expiration date of the restriction |
| 330 |
`type` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') NOT NULL DEFAULT 'MANUAL', -- type of restriction |
| 331 |
`comment` text, -- comments about the restriction |
| 332 |
manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction |
| 333 |
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added |
| 334 |
updated timestamp NULL DEFAULT NULL, -- date the restriction was updated |
| 335 |
PRIMARY KEY (borrower_debarment_id), |
| 336 |
KEY borrowernumber (borrowernumber), |
| 337 |
CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) |
| 338 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 339 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 340 |
|
| 341 |
|
| 342 |
-- |
| 343 |
-- Table structure for table `branch_item_rules` |
| 344 |
-- |
| 345 |
|
| 346 |
DROP TABLE IF EXISTS `branch_item_rules`; |
| 347 |
CREATE TABLE `branch_item_rules` ( -- information entered in the circulation and fine rules under 'Holds policy by item type' |
| 348 |
`branchcode` varchar(10) NOT NULL, -- the branch this rule is for (branches.branchcode) |
| 349 |
`itemtype` varchar(10) NOT NULL, -- the item type this rule applies to (items.itype) |
| 350 |
`holdallowed` tinyint(1) default NULL, -- the number of holds allowed |
| 351 |
hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode |
| 352 |
`returnbranch` varchar(15) default NULL, -- the branch the item returns to (homebranch, holdingbranch, noreturn) |
| 353 |
PRIMARY KEY (`itemtype`,`branchcode`), |
| 354 |
KEY `branch_item_rules_ibfk_2` (`branchcode`), |
| 355 |
CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) |
| 356 |
ON DELETE CASCADE ON UPDATE CASCADE, |
| 357 |
CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) |
| 358 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 359 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 360 |
|
| 361 |
-- |
| 362 |
-- Table structure for table `borrower_password_recovery` |
217 |
-- Table structure for table `borrower_password_recovery` |
| 363 |
-- |
218 |
-- |
| 364 |
|
219 |
|
|
Lines 372-396
CREATE TABLE IF NOT EXISTS `borrower_password_recovery` ( -- holds information a
Link Here
|
| 372 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
227 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 373 |
|
228 |
|
| 374 |
-- |
229 |
-- |
| 375 |
-- Table structure for table borrower_sync |
|
|
| 376 |
-- |
| 377 |
|
| 378 |
DROP TABLE IF EXISTS `borrower_sync`; |
| 379 |
CREATE TABLE borrower_sync ( |
| 380 |
borrowersyncid int(11) NOT NULL AUTO_INCREMENT, -- Primary key, unique identifier |
| 381 |
borrowernumber int(11) NOT NULL, -- Connects data about synchronisations to a borrower |
| 382 |
synctype varchar(32) NOT NULL, -- There could potentially be more than one kind of syncing going on, a text string here can be used to tell them apart. E.g.: The Norwegian national patron database uses 'norwegianpatrondb' in this column |
| 383 |
sync tinyint(1) NOT NULL DEFAULT '0', -- A boolean (1/0) for turning syncing off and on for individual borrowers |
| 384 |
syncstatus varchar(10) DEFAULT NULL, -- The sync status for any given borrower. Could be text strings like 'new', 'edited', 'synced' etc. The values used here will depend on the actual syncing being done. |
| 385 |
lastsync varchar(50) DEFAULT NULL, -- Date of the last successfull sync. The date format might be different depending on the service that is being used, so no special date format is being enforced here. |
| 386 |
hashed_pin varchar(64) DEFAULT NULL, -- Perhaps specific to The Norwegian national patron database, this column holds a hashed PIN code |
| 387 |
PRIMARY KEY (borrowersyncid), |
| 388 |
KEY borrowernumber (borrowernumber), |
| 389 |
CONSTRAINT borrower_sync_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE |
| 390 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 391 |
|
| 392 |
|
| 393 |
-- |
| 394 |
-- Table structure for table `branchcategories` |
230 |
-- Table structure for table `branchcategories` |
| 395 |
-- |
231 |
-- |
| 396 |
|
232 |
|
|
Lines 450-476
CREATE TABLE `branchrelations` ( -- this table links libraries/branches to group
Link Here
|
| 450 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
286 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 451 |
|
287 |
|
| 452 |
-- |
288 |
-- |
| 453 |
-- Table structure for table `branchtransfers` |
|
|
| 454 |
-- |
| 455 |
|
| 456 |
DROP TABLE IF EXISTS `branchtransfers`; |
| 457 |
CREATE TABLE `branchtransfers` ( -- information for items that are in transit between branches |
| 458 |
`itemnumber` int(11) NOT NULL default 0, -- the itemnumber that it is in transit (items.itemnumber) |
| 459 |
`datesent` datetime default NULL, -- the date the transfer was initialized |
| 460 |
`frombranch` varchar(10) NOT NULL default '', -- the branch the transfer is coming from |
| 461 |
`datearrived` datetime default NULL, -- the date the transfer arrived at its destination |
| 462 |
`tobranch` varchar(10) NOT NULL default '', -- the branch the transfer was going to |
| 463 |
`comments` mediumtext, -- any comments related to the transfer |
| 464 |
KEY `frombranch` (`frombranch`), |
| 465 |
KEY `tobranch` (`tobranch`), |
| 466 |
KEY `itemnumber` (`itemnumber`), |
| 467 |
CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 468 |
CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 469 |
CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE |
| 470 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 471 |
|
| 472 |
|
| 473 |
-- |
| 474 |
-- Table structure for table `browser` |
289 |
-- Table structure for table `browser` |
| 475 |
-- |
290 |
-- |
| 476 |
DROP TABLE IF EXISTS `browser`; |
291 |
DROP TABLE IF EXISTS `browser`; |
|
Lines 529-534
ALTER TABLE `collections`
Link Here
|
| 529 |
-- |
344 |
-- |
| 530 |
-- Table: collections_tracking |
345 |
-- Table: collections_tracking |
| 531 |
-- |
346 |
-- |
|
|
347 |
|
| 532 |
DROP TABLE IF EXISTS collections_tracking; |
348 |
DROP TABLE IF EXISTS collections_tracking; |
| 533 |
CREATE TABLE collections_tracking ( |
349 |
CREATE TABLE collections_tracking ( |
| 534 |
collections_tracking_id integer(11) NOT NULL auto_increment, |
350 |
collections_tracking_id integer(11) NOT NULL auto_increment, |
|
Lines 538-665
CREATE TABLE collections_tracking (
Link Here
|
| 538 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
354 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 539 |
|
355 |
|
| 540 |
-- |
356 |
-- |
| 541 |
-- Table structure for table `courses` |
357 |
-- Table structure for table `branch_borrower_circ_rules` |
| 542 |
-- |
358 |
-- |
| 543 |
|
359 |
|
| 544 |
-- The courses table stores the courses created for the |
360 |
DROP TABLE IF EXISTS `branch_borrower_circ_rules`; |
| 545 |
-- course reserves feature. |
361 |
CREATE TABLE `branch_borrower_circ_rules` ( -- includes default circulation rules for patron categories found under "Checkout limit by patron category" |
| 546 |
|
362 |
`branchcode` VARCHAR(10) NOT NULL, -- the branch this rule applies to (branches.branchcode) |
| 547 |
DROP TABLE IF EXISTS courses; |
363 |
`categorycode` VARCHAR(10) NOT NULL, -- the patron category this rule applies to (categories.categorycode) |
| 548 |
CREATE TABLE `courses` ( |
364 |
`maxissueqty` int(4) default NULL, -- the maximum number of checkouts this patron category can have at this branch |
| 549 |
`course_id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the course |
365 |
`maxonsiteissueqty` int(4) default NULL, -- the maximum number of on-site checkouts this patron category can have at this branch |
| 550 |
`department` varchar(80) DEFAULT NULL, -- the authorised value for the DEPARTMENT |
366 |
PRIMARY KEY (`categorycode`, `branchcode`), |
| 551 |
`course_number` varchar(255) DEFAULT NULL, -- the "course number" assigned to a course |
367 |
CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) |
| 552 |
`section` varchar(255) DEFAULT NULL, -- the 'section' of a course |
368 |
ON DELETE CASCADE ON UPDATE CASCADE, |
| 553 |
`course_name` varchar(255) DEFAULT NULL, -- the name of the course |
369 |
CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) |
| 554 |
`term` varchar(80) DEFAULT NULL, -- the authorised value for the TERM |
370 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 555 |
`staff_note` mediumtext, -- the text of the staff only note |
371 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 556 |
`public_note` mediumtext, -- the text of the public / opac note |
|
|
| 557 |
`students_count` varchar(20) DEFAULT NULL, -- how many students will be taking this course/section |
| 558 |
`enabled` enum('yes','no') NOT NULL DEFAULT 'yes', -- determines whether the course is active |
| 559 |
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 560 |
PRIMARY KEY (`course_id`) |
| 561 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 562 |
|
372 |
|
| 563 |
-- |
373 |
-- |
| 564 |
-- Table structure for table `course_instructors` |
374 |
-- Table structure for table `default_borrower_circ_rules` |
| 565 |
-- |
|
|
| 566 |
|
| 567 |
-- The course instructors table links Koha borrowers to the |
| 568 |
-- courses they are teaching. Many instructors can teach many |
| 569 |
-- courses. course_instructors is just a many-to-many join table. |
| 570 |
|
| 571 |
DROP TABLE IF EXISTS course_instructors; |
| 572 |
CREATE TABLE `course_instructors` ( |
| 573 |
`course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id |
| 574 |
`borrowernumber` int(11) NOT NULL, -- foreign key to link to borrowers.borrowernumber for instructor information |
| 575 |
PRIMARY KEY (`course_id`,`borrowernumber`), |
| 576 |
KEY `borrowernumber` (`borrowernumber`) |
| 577 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 578 |
|
| 579 |
-- |
| 580 |
-- Constraints for table `course_instructors` |
| 581 |
-- |
| 582 |
ALTER TABLE `course_instructors` |
| 583 |
ADD CONSTRAINT `course_instructors_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`), |
| 584 |
ADD CONSTRAINT `course_instructors_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE; |
| 585 |
|
| 586 |
-- |
| 587 |
-- Table structure for table `course_items` |
| 588 |
-- |
| 589 |
|
| 590 |
-- If an item is placed on course reserve for one or more courses |
| 591 |
-- it will have an entry in this table. No matter how many courses an item |
| 592 |
-- is part of, it will only have one row in this table. |
| 593 |
|
| 594 |
DROP TABLE IF EXISTS course_items; |
| 595 |
CREATE TABLE `course_items` ( |
| 596 |
`ci_id` int(11) NOT NULL AUTO_INCREMENT, -- course item id |
| 597 |
`itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve |
| 598 |
`itype` varchar(10) DEFAULT NULL, -- new itemtype for the item to have while on reserve (optional) |
| 599 |
`ccode` varchar(10) DEFAULT NULL, -- new category code for the item to have while on reserve (optional) |
| 600 |
`holdingbranch` varchar(10) DEFAULT NULL, -- new holding branch for the item to have while on reserve (optional) |
| 601 |
`location` varchar(80) DEFAULT NULL, -- new shelving location for the item to have while on reseve (optional) |
| 602 |
`enabled` enum('yes','no') NOT NULL DEFAULT 'no', -- if at least one enabled course has this item on reseve, this field will be 'yes', otherwise it will be 'no' |
| 603 |
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 604 |
PRIMARY KEY (`ci_id`), |
| 605 |
UNIQUE KEY `itemnumber` (`itemnumber`), |
| 606 |
KEY `holdingbranch` (`holdingbranch`) |
| 607 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 608 |
|
| 609 |
-- |
| 610 |
-- Constraints for table `course_items` |
| 611 |
-- |
| 612 |
ALTER TABLE `course_items` |
| 613 |
ADD CONSTRAINT `course_items_ibfk_2` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 614 |
ADD CONSTRAINT `course_items_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE; |
| 615 |
|
| 616 |
-- |
| 617 |
-- Table structure for table `course_reserves` |
| 618 |
-- |
| 619 |
|
| 620 |
-- This table connects an item placed on course reserve to a course it is on reserve for. |
| 621 |
-- There will be a row in this table for each course an item is on reserve for. |
| 622 |
|
| 623 |
DROP TABLE IF EXISTS course_reserves; |
| 624 |
CREATE TABLE `course_reserves` ( |
| 625 |
`cr_id` int(11) NOT NULL AUTO_INCREMENT, |
| 626 |
`course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id |
| 627 |
`ci_id` int(11) NOT NULL, -- foreign key to link to courses_items.ci_id |
| 628 |
`staff_note` mediumtext, -- staff only note |
| 629 |
`public_note` mediumtext, -- public, OPAC visible note |
| 630 |
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 631 |
PRIMARY KEY (`cr_id`), |
| 632 |
UNIQUE KEY `pseudo_key` (`course_id`,`ci_id`), |
| 633 |
KEY `course_id` (`course_id`) |
| 634 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 635 |
|
| 636 |
-- |
| 637 |
-- Constraints for table `course_reserves` |
| 638 |
-- |
| 639 |
ALTER TABLE `course_reserves` |
| 640 |
ADD CONSTRAINT `course_reserves_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`), |
| 641 |
ADD CONSTRAINT `course_reserves_ibfk_2` FOREIGN KEY (`ci_id`) REFERENCES `course_items` (`ci_id`) ON DELETE CASCADE ON UPDATE CASCADE; |
| 642 |
|
| 643 |
|
| 644 |
-- |
| 645 |
-- Table structure for table `branch_borrower_circ_rules` |
| 646 |
-- |
| 647 |
|
| 648 |
DROP TABLE IF EXISTS `branch_borrower_circ_rules`; |
| 649 |
CREATE TABLE `branch_borrower_circ_rules` ( -- includes default circulation rules for patron categories found under "Checkout limit by patron category" |
| 650 |
`branchcode` VARCHAR(10) NOT NULL, -- the branch this rule applies to (branches.branchcode) |
| 651 |
`categorycode` VARCHAR(10) NOT NULL, -- the patron category this rule applies to (categories.categorycode) |
| 652 |
`maxissueqty` int(4) default NULL, -- the maximum number of checkouts this patron category can have at this branch |
| 653 |
`maxonsiteissueqty` int(4) default NULL, -- the maximum number of on-site checkouts this patron category can have at this branch |
| 654 |
PRIMARY KEY (`categorycode`, `branchcode`), |
| 655 |
CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) |
| 656 |
ON DELETE CASCADE ON UPDATE CASCADE, |
| 657 |
CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) |
| 658 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 659 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 660 |
|
| 661 |
-- |
| 662 |
-- Table structure for table `default_borrower_circ_rules` |
| 663 |
-- |
375 |
-- |
| 664 |
|
376 |
|
| 665 |
DROP TABLE IF EXISTS `default_borrower_circ_rules`; |
377 |
DROP TABLE IF EXISTS `default_borrower_circ_rules`; |
|
Lines 690-709
CREATE TABLE `default_branch_circ_rules` (
Link Here
|
| 690 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
402 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 691 |
|
403 |
|
| 692 |
-- |
404 |
-- |
| 693 |
-- Table structure for table `default_branch_item_rules` |
|
|
| 694 |
-- |
| 695 |
DROP TABLE IF EXISTS `default_branch_item_rules`; |
| 696 |
CREATE TABLE `default_branch_item_rules` ( |
| 697 |
`itemtype` varchar(10) NOT NULL, |
| 698 |
`holdallowed` tinyint(1) default NULL, |
| 699 |
hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode |
| 700 |
`returnbranch` varchar(15) default NULL, |
| 701 |
PRIMARY KEY (`itemtype`), |
| 702 |
CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) |
| 703 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 704 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 705 |
|
| 706 |
-- |
| 707 |
-- Table structure for table `default_circ_rules` |
405 |
-- Table structure for table `default_circ_rules` |
| 708 |
-- |
406 |
-- |
| 709 |
|
407 |
|
|
Lines 1003-1032
CREATE TABLE `export_format` (
Link Here
|
| 1003 |
PRIMARY KEY (`export_format_id`) |
701 |
PRIMARY KEY (`export_format_id`) |
| 1004 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Used for CSV export'; |
702 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Used for CSV export'; |
| 1005 |
|
703 |
|
| 1006 |
|
|
|
| 1007 |
-- |
| 1008 |
-- Table structure for table `hold_fill_targets` |
| 1009 |
-- |
| 1010 |
|
| 1011 |
DROP TABLE IF EXISTS `hold_fill_targets`; |
| 1012 |
CREATE TABLE hold_fill_targets ( |
| 1013 |
`borrowernumber` int(11) NOT NULL, |
| 1014 |
`biblionumber` int(11) NOT NULL, |
| 1015 |
`itemnumber` int(11) NOT NULL, |
| 1016 |
`source_branchcode` varchar(10) default NULL, |
| 1017 |
`item_level_request` tinyint(4) NOT NULL default 0, |
| 1018 |
PRIMARY KEY `itemnumber` (`itemnumber`), |
| 1019 |
KEY `bib_branch` (`biblionumber`, `source_branchcode`), |
| 1020 |
CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`) |
| 1021 |
REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 1022 |
CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`) |
| 1023 |
REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 1024 |
CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`) |
| 1025 |
REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 1026 |
CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`) |
| 1027 |
REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE |
| 1028 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1029 |
|
| 1030 |
-- |
704 |
-- |
| 1031 |
-- Table structure for table `import_batches` |
705 |
-- Table structure for table `import_batches` |
| 1032 |
-- |
706 |
-- |
|
Lines 1153-1187
CREATE TABLE `import_items` (
Link Here
|
| 1153 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
827 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1154 |
|
828 |
|
| 1155 |
-- |
829 |
-- |
| 1156 |
-- Table structure for table `issues` |
|
|
| 1157 |
-- |
| 1158 |
|
| 1159 |
DROP TABLE IF EXISTS `issues`; |
| 1160 |
CREATE TABLE `issues` ( -- information related to check outs or issues |
| 1161 |
`issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table |
| 1162 |
`borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to |
| 1163 |
`itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out |
| 1164 |
`date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss) |
| 1165 |
`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out |
| 1166 |
`returndate` datetime default NULL, -- date the item was returned, will be NULL until moved to old_issues |
| 1167 |
`lastreneweddate` datetime default NULL, -- date the item was last renewed |
| 1168 |
`return` varchar(4) default NULL, |
| 1169 |
`renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed |
| 1170 |
`auto_renew` BOOLEAN default FALSE, -- automatic renewal |
| 1171 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched |
| 1172 |
`issuedate` datetime default NULL, -- date the item was checked out or issued |
| 1173 |
`onsite_checkout` int(1) NOT NULL default 0, -- in house use flag |
| 1174 |
PRIMARY KEY (`issue_id`), |
| 1175 |
UNIQUE KEY `itemnumber` (`itemnumber`), |
| 1176 |
KEY `issuesborridx` (`borrowernumber`), |
| 1177 |
KEY `itemnumber_idx` (`itemnumber`), |
| 1178 |
KEY `branchcode_idx` (`branchcode`), |
| 1179 |
KEY `bordate` (`borrowernumber`,`timestamp`), |
| 1180 |
CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE, |
| 1181 |
CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE |
| 1182 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1183 |
|
| 1184 |
-- |
| 1185 |
-- Table structure for table `issuingrules` |
830 |
-- Table structure for table `issuingrules` |
| 1186 |
-- |
831 |
-- |
| 1187 |
|
832 |
|
|
Lines 1289-1310
CREATE TABLE `items` ( -- holdings/item information
Link Here
|
| 1289 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
934 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1290 |
|
935 |
|
| 1291 |
-- |
936 |
-- |
| 1292 |
-- Table structure for table `items_last_borrower` |
|
|
| 1293 |
-- |
| 1294 |
|
| 1295 |
CREATE TABLE IF NOT EXISTS `items_last_borrower` ( |
| 1296 |
`id` int(11) NOT NULL AUTO_INCREMENT, |
| 1297 |
`itemnumber` int(11) NOT NULL, |
| 1298 |
`borrowernumber` int(11) NOT NULL, |
| 1299 |
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 1300 |
PRIMARY KEY (`id`), |
| 1301 |
UNIQUE KEY `itemnumber` (`itemnumber`), |
| 1302 |
KEY `borrowernumber` (`borrowernumber`), |
| 1303 |
CONSTRAINT `items_last_borrower_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 1304 |
CONSTRAINT `items_last_borrower_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE |
| 1305 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1306 |
|
| 1307 |
-- |
| 1308 |
-- Table structure for table `itemtypes` |
937 |
-- Table structure for table `itemtypes` |
| 1309 |
-- |
938 |
-- |
| 1310 |
|
939 |
|
|
Lines 1326-1352
CREATE TABLE `itemtypes` ( -- defines the item types
Link Here
|
| 1326 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
955 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1327 |
|
956 |
|
| 1328 |
-- |
957 |
-- |
| 1329 |
-- Table structure for table `creator_batches` |
958 |
-- Table structure for table `default_branch_item_rules` |
| 1330 |
-- |
959 |
-- |
| 1331 |
|
960 |
|
| 1332 |
DROP TABLE IF EXISTS `creator_batches`; |
961 |
DROP TABLE IF EXISTS `default_branch_item_rules`; |
| 1333 |
SET @saved_cs_client = @@character_set_client; |
962 |
CREATE TABLE `default_branch_item_rules` ( |
| 1334 |
SET character_set_client = utf8; |
963 |
`itemtype` varchar(10) NOT NULL, |
| 1335 |
CREATE TABLE `creator_batches` ( |
964 |
`holdallowed` tinyint(1) default NULL, |
| 1336 |
`label_id` int(11) NOT NULL AUTO_INCREMENT, |
965 |
hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode |
| 1337 |
`batch_id` int(10) NOT NULL DEFAULT '1', |
966 |
`returnbranch` varchar(15) default NULL, |
| 1338 |
`item_number` int(11) DEFAULT NULL, |
967 |
PRIMARY KEY (`itemtype`), |
| 1339 |
`borrower_number` int(11) DEFAULT NULL, |
968 |
CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) |
| 1340 |
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
969 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 1341 |
`branch_code` varchar(10) NOT NULL DEFAULT 'NB', |
970 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1342 |
`creator` char(15) NOT NULL DEFAULT 'Labels', |
971 |
|
| 1343 |
PRIMARY KEY (`label_id`), |
972 |
-- |
| 1344 |
KEY `branch_fk_constraint` (`branch_code`), |
973 |
-- Table structure for table `branchtransfers` |
| 1345 |
KEY `item_fk_constraint` (`item_number`), |
974 |
-- |
| 1346 |
KEY `borrower_fk_constraint` (`borrower_number`), |
975 |
|
| 1347 |
CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
976 |
DROP TABLE IF EXISTS `branchtransfers`; |
| 1348 |
CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE, |
977 |
CREATE TABLE `branchtransfers` ( -- information for items that are in transit between branches |
| 1349 |
CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE |
978 |
`itemnumber` int(11) NOT NULL default 0, -- the itemnumber that it is in transit (items.itemnumber) |
|
|
979 |
`datesent` datetime default NULL, -- the date the transfer was initialized |
| 980 |
`frombranch` varchar(10) NOT NULL default '', -- the branch the transfer is coming from |
| 981 |
`datearrived` datetime default NULL, -- the date the transfer arrived at its destination |
| 982 |
`tobranch` varchar(10) NOT NULL default '', -- the branch the transfer was going to |
| 983 |
`comments` mediumtext, -- any comments related to the transfer |
| 984 |
KEY `frombranch` (`frombranch`), |
| 985 |
KEY `tobranch` (`tobranch`), |
| 986 |
KEY `itemnumber` (`itemnumber`), |
| 987 |
CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 988 |
CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 989 |
CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE |
| 990 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 991 |
|
| 992 |
-- |
| 993 |
-- Table structure for table `branch_item_rules` |
| 994 |
-- |
| 995 |
|
| 996 |
DROP TABLE IF EXISTS `branch_item_rules`; |
| 997 |
CREATE TABLE `branch_item_rules` ( -- information entered in the circulation and fine rules under 'Holds policy by item type' |
| 998 |
`branchcode` varchar(10) NOT NULL, -- the branch this rule is for (branches.branchcode) |
| 999 |
`itemtype` varchar(10) NOT NULL, -- the item type this rule applies to (items.itype) |
| 1000 |
`holdallowed` tinyint(1) default NULL, -- the number of holds allowed |
| 1001 |
hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode |
| 1002 |
`returnbranch` varchar(15) default NULL, -- the branch the item returns to (homebranch, holdingbranch, noreturn) |
| 1003 |
PRIMARY KEY (`itemtype`,`branchcode`), |
| 1004 |
KEY `branch_item_rules_ibfk_2` (`branchcode`), |
| 1005 |
CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) |
| 1006 |
ON DELETE CASCADE ON UPDATE CASCADE, |
| 1007 |
CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) |
| 1008 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 1350 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
1009 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1351 |
|
1010 |
|
| 1352 |
-- |
1011 |
-- |
|
Lines 1421-1446
CREATE TABLE `creator_templates` (
Link Here
|
| 1421 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
1080 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1422 |
|
1081 |
|
| 1423 |
-- |
1082 |
-- |
| 1424 |
-- Table structure for table `letter` |
1083 |
-- Table structure for table `marc_subfield_structure` |
| 1425 |
-- |
|
|
| 1426 |
|
| 1427 |
DROP TABLE IF EXISTS `letter`; |
| 1428 |
CREATE TABLE `letter` ( -- table for all notice templates in Koha |
| 1429 |
`module` varchar(20) NOT NULL default '', -- Koha module that triggers this notice or slip |
| 1430 |
`code` varchar(20) NOT NULL default '', -- unique identifier for this notice or slip |
| 1431 |
`branchcode` varchar(10) NOT NULL default '', -- the branch this notice or slip is used at (branches.branchcode) |
| 1432 |
`name` varchar(100) NOT NULL default '', -- plain text name for this notice or slip |
| 1433 |
`is_html` tinyint(1) default 0, -- does this notice or slip use HTML (1 for yes, 0 for no) |
| 1434 |
`title` varchar(200) NOT NULL default '', -- subject line of the notice |
| 1435 |
`content` text, -- body text for the notice or slip |
| 1436 |
`message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice |
| 1437 |
PRIMARY KEY (`module`,`code`, `branchcode`, `message_transport_type`), |
| 1438 |
CONSTRAINT `message_transport_type_fk` FOREIGN KEY (`message_transport_type`) |
| 1439 |
REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE |
| 1440 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1441 |
|
| 1442 |
-- |
| 1443 |
-- Table structure for table `marc_subfield_structure` |
| 1444 |
-- |
1084 |
-- |
| 1445 |
|
1085 |
|
| 1446 |
DROP TABLE IF EXISTS `marc_subfield_structure`; |
1086 |
DROP TABLE IF EXISTS `marc_subfield_structure`; |
|
Lines 1654-1750
CREATE TABLE `oai_sets_biblios` (
Link Here
|
| 1654 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
1294 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1655 |
|
1295 |
|
| 1656 |
-- |
1296 |
-- |
| 1657 |
-- Table structure for table `old_issues` |
|
|
| 1658 |
-- |
| 1659 |
|
| 1660 |
DROP TABLE IF EXISTS `old_issues`; |
| 1661 |
CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned |
| 1662 |
`issue_id` int(11) NOT NULL, -- primary key for issues table |
| 1663 |
`borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to |
| 1664 |
`itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out |
| 1665 |
`date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd) |
| 1666 |
`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out |
| 1667 |
`returndate` datetime default NULL, -- date the item was returned |
| 1668 |
`lastreneweddate` datetime default NULL, -- date the item was last renewed |
| 1669 |
`return` varchar(4) default NULL, |
| 1670 |
`renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed |
| 1671 |
`auto_renew` BOOLEAN default FALSE, -- automatic renewal |
| 1672 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched |
| 1673 |
`issuedate` datetime default NULL, -- date the item was checked out or issued |
| 1674 |
`onsite_checkout` int(1) NOT NULL default 0, -- in house use flag |
| 1675 |
PRIMARY KEY (`issue_id`), |
| 1676 |
KEY `old_issuesborridx` (`borrowernumber`), |
| 1677 |
KEY `old_issuesitemidx` (`itemnumber`), |
| 1678 |
KEY `branchcode_idx` (`branchcode`), |
| 1679 |
KEY `old_bordate` (`borrowernumber`,`timestamp`), |
| 1680 |
CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) |
| 1681 |
ON DELETE SET NULL ON UPDATE SET NULL, |
| 1682 |
CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) |
| 1683 |
ON DELETE SET NULL ON UPDATE SET NULL |
| 1684 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1685 |
|
| 1686 |
-- |
| 1687 |
-- Table structure for table `old_reserves` |
| 1688 |
-- |
| 1689 |
DROP TABLE IF EXISTS `old_reserves`; |
| 1690 |
CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled) |
| 1691 |
`reserve_id` int(11) NOT NULL, -- primary key |
| 1692 |
`borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for |
| 1693 |
`reservedate` date default NULL, -- the date the hold was places |
| 1694 |
`biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on |
| 1695 |
`branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at |
| 1696 |
`notificationdate` date default NULL, -- currently unused |
| 1697 |
`reminderdate` date default NULL, -- currently unused |
| 1698 |
`cancellationdate` date default NULL, -- the date this hold was cancelled |
| 1699 |
`reservenotes` mediumtext, -- notes related to this hold |
| 1700 |
`priority` smallint(6) default NULL, -- where in the queue the patron sits |
| 1701 |
`found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed |
| 1702 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated |
| 1703 |
`itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with |
| 1704 |
`waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library |
| 1705 |
`expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) |
| 1706 |
`lowestPriority` tinyint(1) NOT NULL, -- has this hold been pinned to the lowest priority in the holds queue (1 for yes, 0 for no) |
| 1707 |
`suspend` BOOLEAN NOT NULL DEFAULT 0, -- in this hold suspended (1 for yes, 0 for no) |
| 1708 |
`suspend_until` DATETIME NULL DEFAULT NULL, -- the date this hold is suspended until (NULL for infinitely) |
| 1709 |
`itemtype` VARCHAR(10) NULL DEFAULT NULL, -- If record level hold, the optional itemtype of the item the patron is requesting |
| 1710 |
PRIMARY KEY (`reserve_id`), |
| 1711 |
KEY `old_reserves_borrowernumber` (`borrowernumber`), |
| 1712 |
KEY `old_reserves_biblionumber` (`biblionumber`), |
| 1713 |
KEY `old_reserves_itemnumber` (`itemnumber`), |
| 1714 |
KEY `old_reserves_branchcode` (`branchcode`), |
| 1715 |
KEY `old_reserves_itemtype` (`itemtype`), |
| 1716 |
CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) |
| 1717 |
ON DELETE SET NULL ON UPDATE SET NULL, |
| 1718 |
CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) |
| 1719 |
ON DELETE SET NULL ON UPDATE SET NULL, |
| 1720 |
CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) |
| 1721 |
ON DELETE SET NULL ON UPDATE SET NULL, |
| 1722 |
CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) |
| 1723 |
ON DELETE SET NULL ON UPDATE SET NULL |
| 1724 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1725 |
|
| 1726 |
-- |
| 1727 |
-- Table structure for table `opac_news` |
| 1728 |
-- |
| 1729 |
|
| 1730 |
DROP TABLE IF EXISTS `opac_news`; |
| 1731 |
CREATE TABLE `opac_news` ( -- data from the news tool |
| 1732 |
`idnew` int(10) unsigned NOT NULL auto_increment, -- unique identifier for the news article |
| 1733 |
`branchcode` varchar(10) default NULL, -- branch code users to create branch specific news, NULL is every branch. |
| 1734 |
`title` varchar(250) NOT NULL default '', -- title of the news article |
| 1735 |
`new` text NOT NULL, -- the body of your news article |
| 1736 |
`lang` varchar(25) NOT NULL default '', -- location for the article (koha is the staff client, slip is the circulation receipt and language codes are for the opac) |
| 1737 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- pulibcation date and time |
| 1738 |
`expirationdate` date default NULL, -- date the article is set to expire or no longer be visible |
| 1739 |
`number` int(11) default NULL, -- the order in which this article appears in that specific location |
| 1740 |
`borrowernumber` int(11) default NULL, -- The user who created the news article |
| 1741 |
PRIMARY KEY (`idnew`), |
| 1742 |
CONSTRAINT `borrowernumber_fk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, |
| 1743 |
CONSTRAINT opac_news_branchcode_ibfk FOREIGN KEY (branchcode) REFERENCES branches (branchcode) |
| 1744 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 1745 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1746 |
|
| 1747 |
-- |
| 1748 |
-- Table structure for table `overduerules` |
1297 |
-- Table structure for table `overduerules` |
| 1749 |
-- |
1298 |
-- |
| 1750 |
|
1299 |
|
|
Lines 1766-1784
CREATE TABLE `overduerules` ( -- overdue notice status and triggers
Link Here
|
| 1766 |
UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`) |
1315 |
UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`) |
| 1767 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
1316 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1768 |
|
1317 |
|
| 1769 |
-- |
|
|
| 1770 |
-- Table structure for table `patronimage` |
| 1771 |
-- |
| 1772 |
|
| 1773 |
DROP TABLE IF EXISTS `patronimage`; |
| 1774 |
CREATE TABLE `patronimage` ( -- information related to patron images |
| 1775 |
`borrowernumber` int(11) NOT NULL, -- the borrowernumber of the patron this image is attached to (borrowers.borrowernumber) |
| 1776 |
`mimetype` varchar(15) NOT NULL, -- the format of the image (png, jpg, etc) |
| 1777 |
`imagefile` mediumblob NOT NULL, -- the image |
| 1778 |
PRIMARY KEY (`borrowernumber`), |
| 1779 |
CONSTRAINT `patronimage_fk1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE |
| 1780 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1781 |
|
| 1782 |
-- Table structure for table `pending_offline_operations` |
1318 |
-- Table structure for table `pending_offline_operations` |
| 1783 |
-- |
1319 |
-- |
| 1784 |
-- this table is MyISAM, InnoDB tables are growing only and this table is filled/emptied/filled/emptied... |
1320 |
-- this table is MyISAM, InnoDB tables are growing only and this table is filled/emptied/filled/emptied... |
|
Lines 1864-1869
CREATE TABLE reports_dictionary ( -- definitions (or snippets of SQL) stored for
Link Here
|
| 1864 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
1400 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1865 |
|
1401 |
|
| 1866 |
-- |
1402 |
-- |
|
|
1403 |
-- Table structure for table `saved_sql` |
| 1404 |
-- |
| 1405 |
|
| 1406 |
DROP TABLE IF EXISTS `saved_sql`; |
| 1407 |
CREATE TABLE saved_sql ( -- saved sql reports |
| 1408 |
`id` int(11) NOT NULL auto_increment, -- unique id and primary key assigned by Koha |
| 1409 |
`borrowernumber` int(11) default NULL, -- the staff member who created this report (borrowers.borrowernumber) |
| 1410 |
`date_created` datetime default NULL, -- the date this report was created |
| 1411 |
`last_modified` datetime default NULL, -- the date this report was last edited |
| 1412 |
`savedsql` text, -- the SQL for this report |
| 1413 |
`last_run` datetime default NULL, |
| 1414 |
`report_name` varchar(255) NOT NULL default '', -- the name of this report |
| 1415 |
`type` varchar(255) default NULL, -- always 1 for tabular |
| 1416 |
`notes` text, -- the notes or description given to this report |
| 1417 |
`cache_expiry` int NOT NULL default 300, |
| 1418 |
`public` boolean NOT NULL default FALSE, |
| 1419 |
report_area varchar(6) default NULL, |
| 1420 |
report_group varchar(80) default NULL, |
| 1421 |
report_subgroup varchar(80) default NULL, |
| 1422 |
PRIMARY KEY (`id`), |
| 1423 |
KEY sql_area_group_idx (report_group, report_subgroup), |
| 1424 |
KEY boridx (`borrowernumber`) |
| 1425 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1426 |
|
| 1427 |
|
| 1428 |
-- |
| 1429 |
-- Table structure for `saved_reports` |
| 1430 |
-- |
| 1431 |
|
| 1432 |
DROP TABLE IF EXISTS `saved_reports`; |
| 1433 |
CREATE TABLE saved_reports ( |
| 1434 |
`id` int(11) NOT NULL auto_increment, |
| 1435 |
`report_id` int(11) default NULL, |
| 1436 |
`report` longtext, |
| 1437 |
`date_run` datetime default NULL, |
| 1438 |
PRIMARY KEY (`id`) |
| 1439 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1440 |
|
| 1441 |
-- |
| 1442 |
-- Table structure for table 'search_field' |
| 1443 |
-- |
| 1444 |
|
| 1445 |
DROP TABLE IF EXISTS search_field; |
| 1446 |
CREATE TABLE `search_field` ( |
| 1447 |
`id` int(11) NOT NULL AUTO_INCREMENT, |
| 1448 |
`name` varchar(255) NOT NULL COMMENT 'the name of the field as it will be stored in the search engine', |
| 1449 |
`label` varchar(255) NOT NULL COMMENT 'the human readable name of the field, for display', |
| 1450 |
`type` ENUM('string', 'date', 'number', 'boolean', 'sum') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine', |
| 1451 |
PRIMARY KEY (`id`), |
| 1452 |
UNIQUE KEY (`name`) |
| 1453 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1454 |
|
| 1455 |
-- |
| 1456 |
-- Table structure for table `search_history` |
| 1457 |
-- |
| 1458 |
|
| 1459 |
DROP TABLE IF EXISTS `search_history`; |
| 1460 |
CREATE TABLE IF NOT EXISTS `search_history` ( -- patron's opac search history |
| 1461 |
`id` int(11) NOT NULL auto_increment, -- search history id |
| 1462 |
`userid` int(11) NOT NULL, -- the patron who performed the search (borrowers.borrowernumber) |
| 1463 |
`sessionid` varchar(32) NOT NULL, -- a system generated session id |
| 1464 |
`query_desc` varchar(255) NOT NULL, -- the search that was performed |
| 1465 |
`query_cgi` text NOT NULL, -- the string to append to the search url to rerun the search |
| 1466 |
`type` varchar(16) NOT NULL DEFAULT 'biblio', -- search type, must be 'biblio' or 'authority' |
| 1467 |
`total` int(11) NOT NULL, -- the total of results found |
| 1468 |
`time` timestamp NOT NULL default CURRENT_TIMESTAMP, -- the date and time the search was run |
| 1469 |
KEY `userid` (`userid`), |
| 1470 |
KEY `sessionid` (`sessionid`), |
| 1471 |
PRIMARY KEY (`id`) |
| 1472 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Opac search history results'; |
| 1473 |
|
| 1474 |
-- |
| 1475 |
-- Table structure for table 'search_marc_map' |
| 1476 |
-- |
| 1477 |
|
| 1478 |
DROP TABLE IF EXISTS search_marc_map; |
| 1479 |
CREATE TABLE `search_marc_map` ( |
| 1480 |
id int(11) NOT NULL AUTO_INCREMENT, |
| 1481 |
index_name ENUM('biblios','authorities') NOT NULL COMMENT 'what storage index this map is for', |
| 1482 |
marc_type ENUM('marc21', 'unimarc', 'normarc') NOT NULL COMMENT 'what MARC type this map is for', |
| 1483 |
marc_field VARCHAR(255) NOT NULL COMMENT 'the MARC specifier for this field', |
| 1484 |
PRIMARY KEY(`id`), |
| 1485 |
UNIQUE key(index_name, marc_field, marc_type), |
| 1486 |
INDEX (`index_name`) |
| 1487 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1488 |
|
| 1489 |
-- |
| 1490 |
-- Table structure for table 'search_marc_to_field' |
| 1491 |
-- |
| 1492 |
|
| 1493 |
DROP TABLE IF EXISTS search_marc_to_field; |
| 1494 |
CREATE TABLE `search_marc_to_field` ( |
| 1495 |
search_marc_map_id int(11) NOT NULL, |
| 1496 |
search_field_id int(11) NOT NULL, |
| 1497 |
facet boolean DEFAULT FALSE COMMENT 'true if a facet field should be generated for this', |
| 1498 |
suggestible boolean DEFAULT FALSE COMMENT 'true if this field can be used to generate suggestions for browse', |
| 1499 |
sort boolean DEFAULT NULL COMMENT 'true/false creates special sort handling, null doesn''t', |
| 1500 |
PRIMARY KEY(search_marc_map_id, search_field_id), |
| 1501 |
FOREIGN KEY(search_marc_map_id) REFERENCES search_marc_map(id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 1502 |
FOREIGN KEY(search_field_id) REFERENCES search_field(id) ON DELETE CASCADE ON UPDATE CASCADE |
| 1503 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1504 |
|
| 1505 |
-- |
| 1506 |
-- Table structure for table `serial` |
| 1507 |
-- |
| 1508 |
|
| 1509 |
DROP TABLE IF EXISTS `serial`; |
| 1510 |
CREATE TABLE `serial` ( -- issues related to subscriptions |
| 1511 |
`serialid` int(11) NOT NULL auto_increment, -- unique key for the issue |
| 1512 |
`biblionumber` varchar(100) NOT NULL default '', -- foreign key for the biblio.biblionumber that this issue is attached to |
| 1513 |
`subscriptionid` varchar(100) NOT NULL default '', -- foreign key to the subscription.subscriptionid that this issue is part of |
| 1514 |
`serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc) |
| 1515 |
`serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information |
| 1516 |
`serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information |
| 1517 |
`serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information |
| 1518 |
`status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions) |
| 1519 |
`planneddate` date default NULL, -- date expected |
| 1520 |
`notes` text, -- notes |
| 1521 |
`publisheddate` date default NULL, -- date published |
| 1522 |
publisheddatetext varchar(100) default NULL, -- date published (descriptive) |
| 1523 |
`claimdate` date default NULL, -- date claimed |
| 1524 |
claims_count int(11) default 0, -- number of claims made related to this issue |
| 1525 |
`routingnotes` text, -- notes from the routing list |
| 1526 |
PRIMARY KEY (`serialid`) |
| 1527 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1528 |
|
| 1529 |
-- |
| 1530 |
-- Table structure for table `sessions` |
| 1531 |
-- |
| 1532 |
|
| 1533 |
DROP TABLE IF EXISTS sessions; |
| 1534 |
CREATE TABLE sessions ( |
| 1535 |
`id` varchar(32) NOT NULL, |
| 1536 |
`a_session` mediumtext NOT NULL, |
| 1537 |
PRIMARY KEY (`id`) |
| 1538 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1539 |
|
| 1540 |
-- |
| 1541 |
-- Table structure for table `sms_providers` |
| 1542 |
-- |
| 1543 |
|
| 1544 |
DROP TABLE IF EXISTS sms_providers; |
| 1545 |
CREATE TABLE `sms_providers` ( |
| 1546 |
`id` int(11) NOT NULL AUTO_INCREMENT, |
| 1547 |
`name` varchar(255) NOT NULL, |
| 1548 |
`domain` varchar(255) NOT NULL, |
| 1549 |
PRIMARY KEY (`id`), |
| 1550 |
UNIQUE KEY `name` (`name`) |
| 1551 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1552 |
|
| 1553 |
-- |
| 1554 |
-- Table structure for table `borrowers` |
| 1555 |
-- |
| 1556 |
|
| 1557 |
DROP TABLE IF EXISTS `borrowers`; |
| 1558 |
CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members |
| 1559 |
`borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers |
| 1560 |
`cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers |
| 1561 |
`surname` mediumtext NOT NULL, -- patron/borrower's last name (surname) |
| 1562 |
`firstname` text, -- patron/borrower's first name |
| 1563 |
`title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs. |
| 1564 |
`othernames` mediumtext, -- any other names associated with the patron/borrower |
| 1565 |
`initials` text, -- initials for your patron/borrower |
| 1566 |
`streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address |
| 1567 |
`streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address |
| 1568 |
`address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address |
| 1569 |
`address2` text, -- the second address line for your patron/borrower's primary address |
| 1570 |
`city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address |
| 1571 |
`state` text default NULL, -- the state or province for your patron/borrower's primary address |
| 1572 |
`zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address |
| 1573 |
`country` text, -- the country for your patron/borrower's primary address |
| 1574 |
`email` mediumtext, -- the primary email address for your patron/borrower's primary address |
| 1575 |
`phone` text, -- the primary phone number for your patron/borrower's primary address |
| 1576 |
`mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address |
| 1577 |
`fax` mediumtext, -- the fax number for your patron/borrower's primary address |
| 1578 |
`emailpro` text, -- the secondary email addres for your patron/borrower's primary address |
| 1579 |
`phonepro` text, -- the secondary phone number for your patron/borrower's primary address |
| 1580 |
`B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address |
| 1581 |
`B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address |
| 1582 |
`B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address |
| 1583 |
`B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address |
| 1584 |
`B_city` mediumtext, -- the city or town for your patron/borrower's alternate address |
| 1585 |
`B_state` text default NULL, -- the state for your patron/borrower's alternate address |
| 1586 |
`B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address |
| 1587 |
`B_country` text, -- the country for your patron/borrower's alternate address |
| 1588 |
`B_email` text, -- the patron/borrower's alternate email address |
| 1589 |
`B_phone` mediumtext, -- the patron/borrower's alternate phone number |
| 1590 |
`dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD) |
| 1591 |
`branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch |
| 1592 |
`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category |
| 1593 |
`dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD) |
| 1594 |
`dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD) |
| 1595 |
`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 |
| 1596 |
`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 |
| 1597 |
`debarred` date default NULL, -- until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYY-MM-DD) |
| 1598 |
`debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron |
| 1599 |
`contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name |
| 1600 |
`contactfirstname` text, -- used for children to include first name of guarentor |
| 1601 |
`contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor |
| 1602 |
`guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations |
| 1603 |
`borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client |
| 1604 |
`relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor |
| 1605 |
`sex` varchar(1) default NULL, -- patron/borrower's gender |
| 1606 |
`password` varchar(60) default NULL, -- patron/borrower's encrypted password |
| 1607 |
`flags` int(11) default NULL, -- will include a number associated with the staff member's permissions |
| 1608 |
`userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in |
| 1609 |
`opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client |
| 1610 |
`contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address |
| 1611 |
`sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library |
| 1612 |
`sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library |
| 1613 |
`altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower |
| 1614 |
`altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower |
| 1615 |
`altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower |
| 1616 |
`altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower |
| 1617 |
`altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower |
| 1618 |
`altcontactstate` text default NULL, -- the state for the alternate contact for the patron/borrower |
| 1619 |
`altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower |
| 1620 |
`altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower |
| 1621 |
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower |
| 1622 |
`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) |
| 1623 |
`sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber |
| 1624 |
`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history |
| 1625 |
`privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts |
| 1626 |
UNIQUE KEY `cardnumber` (`cardnumber`), |
| 1627 |
PRIMARY KEY `borrowernumber` (`borrowernumber`), |
| 1628 |
KEY `categorycode` (`categorycode`), |
| 1629 |
KEY `branchcode` (`branchcode`), |
| 1630 |
UNIQUE KEY `userid` (`userid`), |
| 1631 |
KEY `guarantorid` (`guarantorid`), |
| 1632 |
KEY `surname_idx` (`surname`(255)), |
| 1633 |
KEY `firstname_idx` (`firstname`(255)), |
| 1634 |
KEY `othernames_idx` (`othernames`(255)), |
| 1635 |
KEY `sms_provider_id` (`sms_provider_id`), |
| 1636 |
CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), |
| 1637 |
CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`), |
| 1638 |
CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ON UPDATE CASCADE ON DELETE SET NULL |
| 1639 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1640 |
|
| 1641 |
-- |
| 1642 |
-- Table structure for table `borrower_attributes` |
| 1643 |
-- |
| 1644 |
|
| 1645 |
DROP TABLE IF EXISTS `borrower_attributes`; |
| 1646 |
CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as extended patron attributes linked to patrons/borrowers |
| 1647 |
`borrowernumber` int(11) NOT NULL, -- foreign key from the borrowers table, defines which patron/borrower has this attribute |
| 1648 |
`code` varchar(10) NOT NULL, -- foreign key from the borrower_attribute_types table, defines which custom field this value was entered for |
| 1649 |
`attribute` varchar(255) default NULL, -- custom patron field value |
| 1650 |
KEY `borrowernumber` (`borrowernumber`), |
| 1651 |
KEY `code_attribute` (`code`, `attribute`), |
| 1652 |
CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) |
| 1653 |
ON DELETE CASCADE ON UPDATE CASCADE, |
| 1654 |
CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`) |
| 1655 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 1656 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1657 |
|
| 1658 |
-- |
| 1659 |
-- Table structure for table `borrower_debarments` |
| 1660 |
-- |
| 1661 |
|
| 1662 |
DROP TABLE IF EXISTS `borrower_debarments`; |
| 1663 |
CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record |
| 1664 |
borrower_debarment_id int(11) NOT NULL AUTO_INCREMENT, -- unique key for the restriction |
| 1665 |
borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted |
| 1666 |
expiration date DEFAULT NULL, -- expiration date of the restriction |
| 1667 |
`type` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') NOT NULL DEFAULT 'MANUAL', -- type of restriction |
| 1668 |
`comment` text, -- comments about the restriction |
| 1669 |
manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction |
| 1670 |
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added |
| 1671 |
updated timestamp NULL DEFAULT NULL, -- date the restriction was updated |
| 1672 |
PRIMARY KEY (borrower_debarment_id), |
| 1673 |
KEY borrowernumber (borrowernumber), |
| 1674 |
CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) |
| 1675 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 1676 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1677 |
|
| 1678 |
-- |
| 1679 |
-- Table structure for table borrower_sync |
| 1680 |
-- |
| 1681 |
|
| 1682 |
DROP TABLE IF EXISTS `borrower_sync`; |
| 1683 |
CREATE TABLE borrower_sync ( |
| 1684 |
borrowersyncid int(11) NOT NULL AUTO_INCREMENT, -- Primary key, unique identifier |
| 1685 |
borrowernumber int(11) NOT NULL, -- Connects data about synchronisations to a borrower |
| 1686 |
synctype varchar(32) NOT NULL, -- There could potentially be more than one kind of syncing going on, a text string here can be used to tell them apart. E.g.: The Norwegian national patron database uses 'norwegianpatrondb' in this column |
| 1687 |
sync tinyint(1) NOT NULL DEFAULT '0', -- A boolean (1/0) for turning syncing off and on for individual borrowers |
| 1688 |
syncstatus varchar(10) DEFAULT NULL, -- The sync status for any given borrower. Could be text strings like 'new', 'edited', 'synced' etc. The values used here will depend on the actual syncing being done. |
| 1689 |
lastsync varchar(50) DEFAULT NULL, -- Date of the last successfull sync. The date format might be different depending on the service that is being used, so no special date format is being enforced here. |
| 1690 |
hashed_pin varchar(64) DEFAULT NULL, -- Perhaps specific to The Norwegian national patron database, this column holds a hashed PIN code |
| 1691 |
PRIMARY KEY (borrowersyncid), |
| 1692 |
KEY borrowernumber (borrowernumber), |
| 1693 |
CONSTRAINT borrower_sync_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE |
| 1694 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1695 |
|
| 1696 |
-- |
| 1697 |
-- Table structure for table `issues` |
| 1698 |
-- |
| 1699 |
|
| 1700 |
DROP TABLE IF EXISTS `issues`; |
| 1701 |
CREATE TABLE `issues` ( -- information related to check outs or issues |
| 1702 |
`issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table |
| 1703 |
`borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to |
| 1704 |
`itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out |
| 1705 |
`date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss) |
| 1706 |
`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out |
| 1707 |
`returndate` datetime default NULL, -- date the item was returned, will be NULL until moved to old_issues |
| 1708 |
`lastreneweddate` datetime default NULL, -- date the item was last renewed |
| 1709 |
`return` varchar(4) default NULL, |
| 1710 |
`renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed |
| 1711 |
`auto_renew` BOOLEAN default FALSE, -- automatic renewal |
| 1712 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched |
| 1713 |
`issuedate` datetime default NULL, -- date the item was checked out or issued |
| 1714 |
`onsite_checkout` int(1) NOT NULL default 0, -- in house use flag |
| 1715 |
PRIMARY KEY (`issue_id`), |
| 1716 |
UNIQUE KEY `itemnumber` (`itemnumber`), |
| 1717 |
KEY `issuesborridx` (`borrowernumber`), |
| 1718 |
KEY `itemnumber_idx` (`itemnumber`), |
| 1719 |
KEY `branchcode_idx` (`branchcode`), |
| 1720 |
KEY `bordate` (`borrowernumber`,`timestamp`), |
| 1721 |
CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE, |
| 1722 |
CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE |
| 1723 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1724 |
|
| 1725 |
-- |
| 1726 |
-- Table structure for table `old_issues` |
| 1727 |
-- |
| 1728 |
|
| 1729 |
DROP TABLE IF EXISTS `old_issues`; |
| 1730 |
CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned |
| 1731 |
`issue_id` int(11) NOT NULL, -- primary key for issues table |
| 1732 |
`borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to |
| 1733 |
`itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out |
| 1734 |
`date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd) |
| 1735 |
`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out |
| 1736 |
`returndate` datetime default NULL, -- date the item was returned |
| 1737 |
`lastreneweddate` datetime default NULL, -- date the item was last renewed |
| 1738 |
`return` varchar(4) default NULL, |
| 1739 |
`renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed |
| 1740 |
`auto_renew` BOOLEAN default FALSE, -- automatic renewal |
| 1741 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched |
| 1742 |
`issuedate` datetime default NULL, -- date the item was checked out or issued |
| 1743 |
`onsite_checkout` int(1) NOT NULL default 0, -- in house use flag |
| 1744 |
PRIMARY KEY (`issue_id`), |
| 1745 |
KEY `old_issuesborridx` (`borrowernumber`), |
| 1746 |
KEY `old_issuesitemidx` (`itemnumber`), |
| 1747 |
KEY `branchcode_idx` (`branchcode`), |
| 1748 |
KEY `old_bordate` (`borrowernumber`,`timestamp`), |
| 1749 |
CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) |
| 1750 |
ON DELETE SET NULL ON UPDATE SET NULL, |
| 1751 |
CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) |
| 1752 |
ON DELETE SET NULL ON UPDATE SET NULL |
| 1753 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1754 |
|
| 1755 |
-- |
| 1756 |
-- Table structure for table `items_last_borrower` |
| 1757 |
-- |
| 1758 |
|
| 1759 |
CREATE TABLE IF NOT EXISTS `items_last_borrower` ( |
| 1760 |
`id` int(11) NOT NULL AUTO_INCREMENT, |
| 1761 |
`itemnumber` int(11) NOT NULL, |
| 1762 |
`borrowernumber` int(11) NOT NULL, |
| 1763 |
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 1764 |
PRIMARY KEY (`id`), |
| 1765 |
UNIQUE KEY `itemnumber` (`itemnumber`), |
| 1766 |
KEY `borrowernumber` (`borrowernumber`), |
| 1767 |
CONSTRAINT `items_last_borrower_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 1768 |
CONSTRAINT `items_last_borrower_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE |
| 1769 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1770 |
|
| 1771 |
-- |
| 1772 |
-- Table structure for table `creator_batches` |
| 1773 |
-- |
| 1774 |
|
| 1775 |
DROP TABLE IF EXISTS `creator_batches`; |
| 1776 |
SET @saved_cs_client = @@character_set_client; |
| 1777 |
SET character_set_client = utf8; |
| 1778 |
CREATE TABLE `creator_batches` ( |
| 1779 |
`label_id` int(11) NOT NULL AUTO_INCREMENT, |
| 1780 |
`batch_id` int(10) NOT NULL DEFAULT '1', |
| 1781 |
`item_number` int(11) DEFAULT NULL, |
| 1782 |
`borrower_number` int(11) DEFAULT NULL, |
| 1783 |
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 1784 |
`branch_code` varchar(10) NOT NULL DEFAULT 'NB', |
| 1785 |
`creator` char(15) NOT NULL DEFAULT 'Labels', |
| 1786 |
PRIMARY KEY (`label_id`), |
| 1787 |
KEY `branch_fk_constraint` (`branch_code`), |
| 1788 |
KEY `item_fk_constraint` (`item_number`), |
| 1789 |
KEY `borrower_fk_constraint` (`borrower_number`), |
| 1790 |
CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 1791 |
CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE, |
| 1792 |
CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE |
| 1793 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1794 |
|
| 1795 |
-- |
| 1796 |
-- Table structure for table `opac_news` |
| 1797 |
-- |
| 1798 |
|
| 1799 |
DROP TABLE IF EXISTS `opac_news`; |
| 1800 |
CREATE TABLE `opac_news` ( -- data from the news tool |
| 1801 |
`idnew` int(10) unsigned NOT NULL auto_increment, -- unique identifier for the news article |
| 1802 |
`branchcode` varchar(10) default NULL, -- branch code users to create branch specific news, NULL is every branch. |
| 1803 |
`title` varchar(250) NOT NULL default '', -- title of the news article |
| 1804 |
`new` text NOT NULL, -- the body of your news article |
| 1805 |
`lang` varchar(25) NOT NULL default '', -- location for the article (koha is the staff client, slip is the circulation receipt and language codes are for the opac) |
| 1806 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- pulibcation date and time |
| 1807 |
`expirationdate` date default NULL, -- date the article is set to expire or no longer be visible |
| 1808 |
`number` int(11) default NULL, -- the order in which this article appears in that specific location |
| 1809 |
`borrowernumber` int(11) default NULL, -- The user who created the news article |
| 1810 |
PRIMARY KEY (`idnew`), |
| 1811 |
CONSTRAINT `borrowernumber_fk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, |
| 1812 |
CONSTRAINT opac_news_branchcode_ibfk FOREIGN KEY (branchcode) REFERENCES branches (branchcode) |
| 1813 |
ON DELETE CASCADE ON UPDATE CASCADE |
| 1814 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1815 |
|
| 1816 |
-- |
| 1817 |
-- Table structure for table `patronimage` |
| 1818 |
-- |
| 1819 |
|
| 1820 |
DROP TABLE IF EXISTS `patronimage`; |
| 1821 |
CREATE TABLE `patronimage` ( -- information related to patron images |
| 1822 |
`borrowernumber` int(11) NOT NULL, -- the borrowernumber of the patron this image is attached to (borrowers.borrowernumber) |
| 1823 |
`mimetype` varchar(15) NOT NULL, -- the format of the image (png, jpg, etc) |
| 1824 |
`imagefile` mediumblob NOT NULL, -- the image |
| 1825 |
PRIMARY KEY (`borrowernumber`), |
| 1826 |
CONSTRAINT `patronimage_fk1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE |
| 1827 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1828 |
|
| 1829 |
-- |
| 1867 |
-- Table structure for table `reserves` |
1830 |
-- Table structure for table `reserves` |
| 1868 |
-- |
1831 |
-- |
| 1869 |
|
1832 |
|
|
Lines 1903-1908
CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
Link Here
|
| 1903 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
1866 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1904 |
|
1867 |
|
| 1905 |
-- |
1868 |
-- |
|
|
1869 |
-- Table structure for table `old_reserves` |
| 1870 |
-- |
| 1871 |
|
| 1872 |
DROP TABLE IF EXISTS `old_reserves`; |
| 1873 |
CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled) |
| 1874 |
`reserve_id` int(11) NOT NULL, -- primary key |
| 1875 |
`borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for |
| 1876 |
`reservedate` date default NULL, -- the date the hold was places |
| 1877 |
`biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on |
| 1878 |
`branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at |
| 1879 |
`notificationdate` date default NULL, -- currently unused |
| 1880 |
`reminderdate` date default NULL, -- currently unused |
| 1881 |
`cancellationdate` date default NULL, -- the date this hold was cancelled |
| 1882 |
`reservenotes` mediumtext, -- notes related to this hold |
| 1883 |
`priority` smallint(6) default NULL, -- where in the queue the patron sits |
| 1884 |
`found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed |
| 1885 |
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated |
| 1886 |
`itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with |
| 1887 |
`waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library |
| 1888 |
`expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) |
| 1889 |
`lowestPriority` tinyint(1) NOT NULL, -- has this hold been pinned to the lowest priority in the holds queue (1 for yes, 0 for no) |
| 1890 |
`suspend` BOOLEAN NOT NULL DEFAULT 0, -- in this hold suspended (1 for yes, 0 for no) |
| 1891 |
`suspend_until` DATETIME NULL DEFAULT NULL, -- the date this hold is suspended until (NULL for infinitely) |
| 1892 |
`itemtype` VARCHAR(10) NULL DEFAULT NULL, -- If record level hold, the optional itemtype of the item the patron is requesting |
| 1893 |
PRIMARY KEY (`reserve_id`), |
| 1894 |
KEY `old_reserves_borrowernumber` (`borrowernumber`), |
| 1895 |
KEY `old_reserves_biblionumber` (`biblionumber`), |
| 1896 |
KEY `old_reserves_itemnumber` (`itemnumber`), |
| 1897 |
KEY `old_reserves_branchcode` (`branchcode`), |
| 1898 |
KEY `old_reserves_itemtype` (`itemtype`), |
| 1899 |
CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) |
| 1900 |
ON DELETE SET NULL ON UPDATE SET NULL, |
| 1901 |
CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) |
| 1902 |
ON DELETE SET NULL ON UPDATE SET NULL, |
| 1903 |
CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) |
| 1904 |
ON DELETE SET NULL ON UPDATE SET NULL, |
| 1905 |
CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) |
| 1906 |
ON DELETE SET NULL ON UPDATE SET NULL |
| 1907 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1908 |
|
| 1909 |
-- |
| 1906 |
-- Table structure for table `reviews` |
1910 |
-- Table structure for table `reviews` |
| 1907 |
-- |
1911 |
-- |
| 1908 |
|
1912 |
|
|
Lines 1920-2076
CREATE TABLE `reviews` ( -- patron opac comments
Link Here
|
| 1920 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
1924 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1921 |
|
1925 |
|
| 1922 |
-- |
1926 |
-- |
| 1923 |
-- Table structure for table `saved_sql` |
|
|
| 1924 |
-- |
| 1925 |
|
| 1926 |
DROP TABLE IF EXISTS `saved_sql`; |
| 1927 |
CREATE TABLE saved_sql ( -- saved sql reports |
| 1928 |
`id` int(11) NOT NULL auto_increment, -- unique id and primary key assigned by Koha |
| 1929 |
`borrowernumber` int(11) default NULL, -- the staff member who created this report (borrowers.borrowernumber) |
| 1930 |
`date_created` datetime default NULL, -- the date this report was created |
| 1931 |
`last_modified` datetime default NULL, -- the date this report was last edited |
| 1932 |
`savedsql` text, -- the SQL for this report |
| 1933 |
`last_run` datetime default NULL, |
| 1934 |
`report_name` varchar(255) NOT NULL default '', -- the name of this report |
| 1935 |
`type` varchar(255) default NULL, -- always 1 for tabular |
| 1936 |
`notes` text, -- the notes or description given to this report |
| 1937 |
`cache_expiry` int NOT NULL default 300, |
| 1938 |
`public` boolean NOT NULL default FALSE, |
| 1939 |
report_area varchar(6) default NULL, |
| 1940 |
report_group varchar(80) default NULL, |
| 1941 |
report_subgroup varchar(80) default NULL, |
| 1942 |
PRIMARY KEY (`id`), |
| 1943 |
KEY sql_area_group_idx (report_group, report_subgroup), |
| 1944 |
KEY boridx (`borrowernumber`) |
| 1945 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1946 |
|
| 1947 |
|
| 1948 |
-- |
| 1949 |
-- Table structure for `saved_reports` |
| 1950 |
-- |
| 1951 |
|
| 1952 |
DROP TABLE IF EXISTS `saved_reports`; |
| 1953 |
CREATE TABLE saved_reports ( |
| 1954 |
`id` int(11) NOT NULL auto_increment, |
| 1955 |
`report_id` int(11) default NULL, |
| 1956 |
`report` longtext, |
| 1957 |
`date_run` datetime default NULL, |
| 1958 |
PRIMARY KEY (`id`) |
| 1959 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1960 |
|
| 1961 |
-- |
| 1962 |
-- Table structure for table 'search_field' |
| 1963 |
-- |
| 1964 |
|
| 1965 |
DROP TABLE IF EXISTS search_field; |
| 1966 |
CREATE TABLE `search_field` ( |
| 1967 |
`id` int(11) NOT NULL AUTO_INCREMENT, |
| 1968 |
`name` varchar(255) NOT NULL COMMENT 'the name of the field as it will be stored in the search engine', |
| 1969 |
`label` varchar(255) NOT NULL COMMENT 'the human readable name of the field, for display', |
| 1970 |
`type` ENUM('string', 'date', 'number', 'boolean', 'sum') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine', |
| 1971 |
PRIMARY KEY (`id`), |
| 1972 |
UNIQUE KEY (`name`) |
| 1973 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 1974 |
|
| 1975 |
-- |
| 1976 |
-- Table structure for table `search_history` |
| 1977 |
-- |
| 1978 |
|
| 1979 |
DROP TABLE IF EXISTS `search_history`; |
| 1980 |
CREATE TABLE IF NOT EXISTS `search_history` ( -- patron's opac search history |
| 1981 |
`id` int(11) NOT NULL auto_increment, -- search history id |
| 1982 |
`userid` int(11) NOT NULL, -- the patron who performed the search (borrowers.borrowernumber) |
| 1983 |
`sessionid` varchar(32) NOT NULL, -- a system generated session id |
| 1984 |
`query_desc` varchar(255) NOT NULL, -- the search that was performed |
| 1985 |
`query_cgi` text NOT NULL, -- the string to append to the search url to rerun the search |
| 1986 |
`type` varchar(16) NOT NULL DEFAULT 'biblio', -- search type, must be 'biblio' or 'authority' |
| 1987 |
`total` int(11) NOT NULL, -- the total of results found |
| 1988 |
`time` timestamp NOT NULL default CURRENT_TIMESTAMP, -- the date and time the search was run |
| 1989 |
KEY `userid` (`userid`), |
| 1990 |
KEY `sessionid` (`sessionid`), |
| 1991 |
PRIMARY KEY (`id`) |
| 1992 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Opac search history results'; |
| 1993 |
|
| 1994 |
-- |
| 1995 |
-- Table structure for table 'search_marc_map' |
| 1996 |
-- |
| 1997 |
|
| 1998 |
DROP TABLE IF EXISTS search_marc_map; |
| 1999 |
CREATE TABLE `search_marc_map` ( |
| 2000 |
id int(11) NOT NULL AUTO_INCREMENT, |
| 2001 |
index_name ENUM('biblios','authorities') NOT NULL COMMENT 'what storage index this map is for', |
| 2002 |
marc_type ENUM('marc21', 'unimarc', 'normarc') NOT NULL COMMENT 'what MARC type this map is for', |
| 2003 |
marc_field VARCHAR(255) NOT NULL COMMENT 'the MARC specifier for this field', |
| 2004 |
PRIMARY KEY(`id`), |
| 2005 |
UNIQUE key(index_name, marc_field, marc_type), |
| 2006 |
INDEX (`index_name`) |
| 2007 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2008 |
|
| 2009 |
-- |
| 2010 |
-- Table structure for table 'search_marc_to_field' |
| 2011 |
-- |
| 2012 |
|
| 2013 |
DROP TABLE IF EXISTS search_marc_to_field; |
| 2014 |
CREATE TABLE `search_marc_to_field` ( |
| 2015 |
search_marc_map_id int(11) NOT NULL, |
| 2016 |
search_field_id int(11) NOT NULL, |
| 2017 |
facet boolean DEFAULT FALSE COMMENT 'true if a facet field should be generated for this', |
| 2018 |
suggestible boolean DEFAULT FALSE COMMENT 'true if this field can be used to generate suggestions for browse', |
| 2019 |
sort boolean DEFAULT NULL COMMENT 'true/false creates special sort handling, null doesn''t', |
| 2020 |
PRIMARY KEY(search_marc_map_id, search_field_id), |
| 2021 |
FOREIGN KEY(search_marc_map_id) REFERENCES search_marc_map(id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 2022 |
FOREIGN KEY(search_field_id) REFERENCES search_field(id) ON DELETE CASCADE ON UPDATE CASCADE |
| 2023 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2024 |
|
| 2025 |
-- |
| 2026 |
-- Table structure for table `serial` |
| 2027 |
-- |
| 2028 |
|
| 2029 |
DROP TABLE IF EXISTS `serial`; |
| 2030 |
CREATE TABLE `serial` ( -- issues related to subscriptions |
| 2031 |
`serialid` int(11) NOT NULL auto_increment, -- unique key for the issue |
| 2032 |
`biblionumber` varchar(100) NOT NULL default '', -- foreign key for the biblio.biblionumber that this issue is attached to |
| 2033 |
`subscriptionid` varchar(100) NOT NULL default '', -- foreign key to the subscription.subscriptionid that this issue is part of |
| 2034 |
`serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc) |
| 2035 |
`serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information |
| 2036 |
`serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information |
| 2037 |
`serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information |
| 2038 |
`status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions) |
| 2039 |
`planneddate` date default NULL, -- date expected |
| 2040 |
`notes` text, -- notes |
| 2041 |
`publisheddate` date default NULL, -- date published |
| 2042 |
publisheddatetext varchar(100) default NULL, -- date published (descriptive) |
| 2043 |
`claimdate` date default NULL, -- date claimed |
| 2044 |
claims_count int(11) default 0, -- number of claims made related to this issue |
| 2045 |
`routingnotes` text, -- notes from the routing list |
| 2046 |
PRIMARY KEY (`serialid`) |
| 2047 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2048 |
|
| 2049 |
-- |
| 2050 |
-- Table structure for table `sessions` |
| 2051 |
-- |
| 2052 |
|
| 2053 |
DROP TABLE IF EXISTS sessions; |
| 2054 |
CREATE TABLE sessions ( |
| 2055 |
`id` varchar(32) NOT NULL, |
| 2056 |
`a_session` mediumtext NOT NULL, |
| 2057 |
PRIMARY KEY (`id`) |
| 2058 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2059 |
|
| 2060 |
-- |
| 2061 |
-- Table structure for table `sms_providers` |
| 2062 |
-- |
| 2063 |
|
| 2064 |
DROP TABLE IF EXISTS sms_providers; |
| 2065 |
CREATE TABLE `sms_providers` ( |
| 2066 |
`id` int(11) NOT NULL AUTO_INCREMENT, |
| 2067 |
`name` varchar(255) NOT NULL, |
| 2068 |
`domain` varchar(255) NOT NULL, |
| 2069 |
PRIMARY KEY (`id`), |
| 2070 |
UNIQUE KEY `name` (`name`) |
| 2071 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2072 |
|
| 2073 |
-- |
| 2074 |
-- Table structure for table `special_holidays` |
1927 |
-- Table structure for table `special_holidays` |
| 2075 |
-- |
1928 |
-- |
| 2076 |
|
1929 |
|
|
Lines 2252-2303
CREATE TABLE `subscriptionroutinglist` ( -- information related to the routing l
Link Here
|
| 2252 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
2105 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2253 |
|
2106 |
|
| 2254 |
-- |
2107 |
-- |
| 2255 |
-- Table structure for table `suggestions` |
|
|
| 2256 |
-- |
| 2257 |
|
| 2258 |
DROP TABLE IF EXISTS `suggestions`; |
| 2259 |
CREATE TABLE `suggestions` ( -- purchase suggestions |
| 2260 |
`suggestionid` int(8) NOT NULL auto_increment, -- unique identifier assigned automatically by Koha |
| 2261 |
`suggestedby` int(11) NOT NULL default 0, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table |
| 2262 |
`suggesteddate` date NOT NULL, -- date the suggestion was submitted |
| 2263 |
`managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table |
| 2264 |
`manageddate` date default NULL, -- date the suggestion was updated |
| 2265 |
acceptedby INT(11) default NULL, -- borrowernumber for the librarian who accepted the suggestion, foreign key linking to the borrowers table |
| 2266 |
accepteddate date default NULL, -- date the suggestion was marked as accepted |
| 2267 |
rejectedby INT(11) default NULL, -- borrowernumber for the librarian who rejected the suggestion, foreign key linking to the borrowers table |
| 2268 |
rejecteddate date default NULL, -- date the suggestion was marked as rejected |
| 2269 |
`STATUS` varchar(10) NOT NULL default '', -- suggestion status (ASKED, CHECKED, ACCEPTED, or REJECTED) |
| 2270 |
`note` mediumtext, -- note entered on the suggestion |
| 2271 |
`author` varchar(80) default NULL, -- author of the suggested item |
| 2272 |
`title` varchar(255) default NULL, -- title of the suggested item |
| 2273 |
`copyrightdate` smallint(6) default NULL, -- copyright date of the suggested item |
| 2274 |
`publishercode` varchar(255) default NULL, -- publisher of the suggested item |
| 2275 |
`date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the suggestion was updated |
| 2276 |
`volumedesc` varchar(255) default NULL, |
| 2277 |
`publicationyear` smallint(6) default 0, |
| 2278 |
`place` varchar(255) default NULL, -- publication place of the suggested item |
| 2279 |
`isbn` varchar(30) default NULL, -- isbn of the suggested item |
| 2280 |
`biblionumber` int(11) default NULL, -- foreign key linking the suggestion to the biblio table after the suggestion has been ordered |
| 2281 |
`reason` text, -- reason for accepting or rejecting the suggestion |
| 2282 |
`patronreason` text, -- reason for making the suggestion |
| 2283 |
budgetid INT(11), -- foreign key linking the suggested budget to the aqbudgets table |
| 2284 |
branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table |
| 2285 |
collectiontitle text default NULL, -- collection name for the suggested item |
| 2286 |
itemtype VARCHAR(30) default NULL, -- suggested item type |
| 2287 |
quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased |
| 2288 |
currency VARCHAR(10) default NULL, -- suggested currency for the suggested price |
| 2289 |
price DECIMAL(28,6) default NULL, -- suggested price |
| 2290 |
total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency) |
| 2291 |
PRIMARY KEY (`suggestionid`), |
| 2292 |
KEY `suggestedby` (`suggestedby`), |
| 2293 |
KEY `managedby` (`managedby`), |
| 2294 |
KEY `status` (`STATUS`), |
| 2295 |
KEY `biblionumber` (`biblionumber`), |
| 2296 |
KEY `branchcode` (`branchcode`), |
| 2297 |
CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE |
| 2298 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2299 |
|
| 2300 |
-- |
| 2301 |
-- Table structure for table `systempreferences` |
2108 |
-- Table structure for table `systempreferences` |
| 2302 |
-- |
2109 |
-- |
| 2303 |
|
2110 |
|
|
Lines 2637-2642
CREATE TABLE `tmp_holdsqueue` (
Link Here
|
| 2637 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
2444 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2638 |
|
2445 |
|
| 2639 |
-- |
2446 |
-- |
|
|
2447 |
-- Table structure for table `message_transport_types` |
| 2448 |
-- |
| 2449 |
|
| 2450 |
DROP TABLE IF EXISTS `message_transport_types`; |
| 2451 |
CREATE TABLE `message_transport_types` ( |
| 2452 |
`message_transport_type` varchar(20) NOT NULL, |
| 2453 |
PRIMARY KEY (`message_transport_type`) |
| 2454 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2455 |
|
| 2456 |
-- |
| 2640 |
-- Table structure for table `message_queue` |
2457 |
-- Table structure for table `message_queue` |
| 2641 |
-- |
2458 |
-- |
| 2642 |
|
2459 |
|
|
Lines 2662-2674
CREATE TABLE `message_queue` (
Link Here
|
| 2662 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
2479 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2663 |
|
2480 |
|
| 2664 |
-- |
2481 |
-- |
| 2665 |
-- Table structure for table `message_transport_types` |
2482 |
-- Table structure for table `letter` |
| 2666 |
-- |
2483 |
-- |
| 2667 |
|
2484 |
|
| 2668 |
DROP TABLE IF EXISTS `message_transport_types`; |
2485 |
DROP TABLE IF EXISTS `letter`; |
| 2669 |
CREATE TABLE `message_transport_types` ( |
2486 |
CREATE TABLE `letter` ( -- table for all notice templates in Koha |
| 2670 |
`message_transport_type` varchar(20) NOT NULL, |
2487 |
`module` varchar(20) NOT NULL default '', -- Koha module that triggers this notice or slip |
| 2671 |
PRIMARY KEY (`message_transport_type`) |
2488 |
`code` varchar(20) NOT NULL default '', -- unique identifier for this notice or slip |
|
|
2489 |
`branchcode` varchar(10) NOT NULL default '', -- the branch this notice or slip is used at (branches.branchcode) |
| 2490 |
`name` varchar(100) NOT NULL default '', -- plain text name for this notice or slip |
| 2491 |
`is_html` tinyint(1) default 0, -- does this notice or slip use HTML (1 for yes, 0 for no) |
| 2492 |
`title` varchar(200) NOT NULL default '', -- subject line of the notice |
| 2493 |
`content` text, -- body text for the notice or slip |
| 2494 |
`message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice |
| 2495 |
PRIMARY KEY (`module`,`code`, `branchcode`, `message_transport_type`), |
| 2496 |
CONSTRAINT `message_transport_type_fk` FOREIGN KEY (`message_transport_type`) |
| 2497 |
REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE |
| 2672 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
2498 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2673 |
|
2499 |
|
| 2674 |
-- |
2500 |
-- |
|
Lines 2898-2967
CREATE TABLE `alert` (
Link Here
|
| 2898 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
2724 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2899 |
|
2725 |
|
| 2900 |
-- |
2726 |
-- |
| 2901 |
-- Table structure for table `aqbasketgroups` |
|
|
| 2902 |
-- |
| 2903 |
|
| 2904 |
DROP TABLE IF EXISTS `aqbasketgroups`; |
| 2905 |
CREATE TABLE `aqbasketgroups` ( |
| 2906 |
`id` int(11) NOT NULL auto_increment, |
| 2907 |
`name` varchar(50) default NULL, |
| 2908 |
`closed` tinyint(1) default NULL, |
| 2909 |
`booksellerid` int(11) NOT NULL, |
| 2910 |
`deliveryplace` varchar(10) default NULL, |
| 2911 |
`freedeliveryplace` text default NULL, |
| 2912 |
`deliverycomment` varchar(255) default NULL, |
| 2913 |
`billingplace` varchar(10) default NULL, |
| 2914 |
PRIMARY KEY (`id`), |
| 2915 |
KEY `booksellerid` (`booksellerid`), |
| 2916 |
CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE |
| 2917 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2918 |
|
| 2919 |
-- |
| 2920 |
-- Table structure for table `aqbasket` |
| 2921 |
-- |
| 2922 |
|
| 2923 |
DROP TABLE IF EXISTS `aqbasket`; |
| 2924 |
CREATE TABLE `aqbasket` ( -- stores data about baskets in acquisitions |
| 2925 |
`basketno` int(11) NOT NULL auto_increment, -- primary key, Koha defined number |
| 2926 |
`basketname` varchar(50) default NULL, -- name given to the basket at creation |
| 2927 |
`note` mediumtext, -- the internal note added at basket creation |
| 2928 |
`booksellernote` mediumtext, -- the vendor note added at basket creation |
| 2929 |
`contractnumber` int(11), -- links this basket to the aqcontract table (aqcontract.contractnumber) |
| 2930 |
`creationdate` date default NULL, -- the date the basket was created |
| 2931 |
`closedate` date default NULL, -- the date the basket was closed |
| 2932 |
`booksellerid` int(11) NOT NULL default 1, -- the Koha assigned ID for the vendor (aqbooksellers.id) |
| 2933 |
`authorisedby` varchar(10) default NULL, -- the borrowernumber of the person who created the basket |
| 2934 |
`booksellerinvoicenumber` mediumtext, -- appears to always be NULL |
| 2935 |
`basketgroupid` int(11), -- links this basket to its group (aqbasketgroups.id) |
| 2936 |
`deliveryplace` varchar(10) default NULL, -- basket delivery place |
| 2937 |
`billingplace` varchar(10) default NULL, -- basket billing place |
| 2938 |
branch varchar(10) default NULL, -- basket branch |
| 2939 |
is_standing TINYINT(1) NOT NULL DEFAULT 0, -- orders in this basket are standing |
| 2940 |
PRIMARY KEY (`basketno`), |
| 2941 |
KEY `booksellerid` (`booksellerid`), |
| 2942 |
KEY `basketgroupid` (`basketgroupid`), |
| 2943 |
KEY `contractnumber` (`contractnumber`), |
| 2944 |
KEY `authorisedby` (`authorisedby`), |
| 2945 |
CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE, |
| 2946 |
CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`), |
| 2947 |
CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE, |
| 2948 |
CONSTRAINT aqbasket_ibfk_4 FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE SET NULL |
| 2949 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2950 |
|
| 2951 |
-- |
| 2952 |
-- Table structure for table aqbasketusers |
| 2953 |
-- |
| 2954 |
|
| 2955 |
DROP TABLE IF EXISTS aqbasketusers; |
| 2956 |
CREATE TABLE aqbasketusers ( |
| 2957 |
basketno int(11) NOT NULL, |
| 2958 |
borrowernumber int(11) NOT NULL, |
| 2959 |
PRIMARY KEY (basketno,borrowernumber), |
| 2960 |
CONSTRAINT aqbasketusers_ibfk_1 FOREIGN KEY (basketno) REFERENCES aqbasket (basketno) ON UPDATE CASCADE ON DELETE CASCADE, |
| 2961 |
CONSTRAINT aqbasketusers_ibfk_2 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON UPDATE CASCADE ON DELETE CASCADE |
| 2962 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2963 |
|
| 2964 |
-- |
| 2965 |
-- Table structure for table `aqbooksellers` |
2727 |
-- Table structure for table `aqbooksellers` |
| 2966 |
-- |
2728 |
-- |
| 2967 |
|
2729 |
|
|
Lines 3002-3007
CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquis
Link Here
|
| 3002 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
2764 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3003 |
|
2765 |
|
| 3004 |
-- |
2766 |
-- |
|
|
2767 |
-- Table structure for table `aqbasketgroups` |
| 2768 |
-- |
| 2769 |
|
| 2770 |
DROP TABLE IF EXISTS `aqbasketgroups`; |
| 2771 |
CREATE TABLE `aqbasketgroups` ( |
| 2772 |
`id` int(11) NOT NULL auto_increment, |
| 2773 |
`name` varchar(50) default NULL, |
| 2774 |
`closed` tinyint(1) default NULL, |
| 2775 |
`booksellerid` int(11) NOT NULL, |
| 2776 |
`deliveryplace` varchar(10) default NULL, |
| 2777 |
`freedeliveryplace` text default NULL, |
| 2778 |
`deliverycomment` varchar(255) default NULL, |
| 2779 |
`billingplace` varchar(10) default NULL, |
| 2780 |
PRIMARY KEY (`id`), |
| 2781 |
KEY `booksellerid` (`booksellerid`), |
| 2782 |
CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE |
| 2783 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2784 |
|
| 2785 |
-- |
| 3005 |
-- Table structure for table `aqbudgets` |
2786 |
-- Table structure for table `aqbudgets` |
| 3006 |
-- |
2787 |
-- |
| 3007 |
|
2788 |
|
|
Lines 3104-3129
CREATE TABLE aqcontacts (
Link Here
|
| 3104 |
serialsprimary BOOLEAN NOT NULL DEFAULT 0, -- is this the primary contact for serials messages |
2885 |
serialsprimary BOOLEAN NOT NULL DEFAULT 0, -- is this the primary contact for serials messages |
| 3105 |
booksellerid int(11) not NULL, |
2886 |
booksellerid int(11) not NULL, |
| 3106 |
PRIMARY KEY (id), |
2887 |
PRIMARY KEY (id), |
| 3107 |
CONSTRAINT booksellerid_aqcontacts_fk FOREIGN KEY (booksellerid) |
2888 |
CONSTRAINT booksellerid_aqcontacts_fk FOREIGN KEY (booksellerid) |
| 3108 |
REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE |
2889 |
REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE |
| 3109 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; |
2890 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; |
|
|
2891 |
|
| 2892 |
-- |
| 2893 |
-- Table structure for table 'aqcontract' |
| 2894 |
-- |
| 2895 |
|
| 2896 |
DROP TABLE IF EXISTS `aqcontract`; |
| 2897 |
CREATE TABLE `aqcontract` ( |
| 2898 |
`contractnumber` int(11) NOT NULL auto_increment, |
| 2899 |
`contractstartdate` date default NULL, |
| 2900 |
`contractenddate` date default NULL, |
| 2901 |
`contractname` varchar(50) default NULL, |
| 2902 |
`contractdescription` mediumtext, |
| 2903 |
`booksellerid` int(11) not NULL, |
| 2904 |
PRIMARY KEY (`contractnumber`), |
| 2905 |
CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`) |
| 2906 |
REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
| 2907 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; |
| 2908 |
|
| 2909 |
-- |
| 2910 |
-- Table structure for table `aqbasket` |
| 2911 |
-- |
| 2912 |
|
| 2913 |
DROP TABLE IF EXISTS `aqbasket`; |
| 2914 |
CREATE TABLE `aqbasket` ( -- stores data about baskets in acquisitions |
| 2915 |
`basketno` int(11) NOT NULL auto_increment, -- primary key, Koha defined number |
| 2916 |
`basketname` varchar(50) default NULL, -- name given to the basket at creation |
| 2917 |
`note` mediumtext, -- the internal note added at basket creation |
| 2918 |
`booksellernote` mediumtext, -- the vendor note added at basket creation |
| 2919 |
`contractnumber` int(11), -- links this basket to the aqcontract table (aqcontract.contractnumber) |
| 2920 |
`creationdate` date default NULL, -- the date the basket was created |
| 2921 |
`closedate` date default NULL, -- the date the basket was closed |
| 2922 |
`booksellerid` int(11) NOT NULL default 1, -- the Koha assigned ID for the vendor (aqbooksellers.id) |
| 2923 |
`authorisedby` varchar(10) default NULL, -- the borrowernumber of the person who created the basket |
| 2924 |
`booksellerinvoicenumber` mediumtext, -- appears to always be NULL |
| 2925 |
`basketgroupid` int(11), -- links this basket to its group (aqbasketgroups.id) |
| 2926 |
`deliveryplace` varchar(10) default NULL, -- basket delivery place |
| 2927 |
`billingplace` varchar(10) default NULL, -- basket billing place |
| 2928 |
branch varchar(10) default NULL, -- basket branch |
| 2929 |
is_standing TINYINT(1) NOT NULL DEFAULT 0, -- orders in this basket are standing |
| 2930 |
PRIMARY KEY (`basketno`), |
| 2931 |
KEY `booksellerid` (`booksellerid`), |
| 2932 |
KEY `basketgroupid` (`basketgroupid`), |
| 2933 |
KEY `contractnumber` (`contractnumber`), |
| 2934 |
KEY `authorisedby` (`authorisedby`), |
| 2935 |
CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE, |
| 2936 |
CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`), |
| 2937 |
CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE, |
| 2938 |
CONSTRAINT aqbasket_ibfk_4 FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE SET NULL |
| 2939 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2940 |
|
| 2941 |
-- |
| 2942 |
-- Table structure for table aqbasketusers |
| 2943 |
-- |
| 2944 |
|
| 2945 |
DROP TABLE IF EXISTS aqbasketusers; |
| 2946 |
CREATE TABLE aqbasketusers ( |
| 2947 |
basketno int(11) NOT NULL, |
| 2948 |
borrowernumber int(11) NOT NULL, |
| 2949 |
PRIMARY KEY (basketno,borrowernumber), |
| 2950 |
CONSTRAINT aqbasketusers_ibfk_1 FOREIGN KEY (basketno) REFERENCES aqbasket (basketno) ON UPDATE CASCADE ON DELETE CASCADE, |
| 2951 |
CONSTRAINT aqbasketusers_ibfk_2 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON UPDATE CASCADE ON DELETE CASCADE |
| 2952 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2953 |
|
| 2954 |
-- |
| 2955 |
-- Table structure for table `suggestions` |
| 2956 |
-- |
| 2957 |
|
| 2958 |
DROP TABLE IF EXISTS `suggestions`; |
| 2959 |
CREATE TABLE `suggestions` ( -- purchase suggestions |
| 2960 |
`suggestionid` int(8) NOT NULL auto_increment, -- unique identifier assigned automatically by Koha |
| 2961 |
`suggestedby` int(11) NOT NULL default 0, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table |
| 2962 |
`suggesteddate` date NOT NULL, -- date the suggestion was submitted |
| 2963 |
`managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table |
| 2964 |
`manageddate` date default NULL, -- date the suggestion was updated |
| 2965 |
acceptedby INT(11) default NULL, -- borrowernumber for the librarian who accepted the suggestion, foreign key linking to the borrowers table |
| 2966 |
accepteddate date default NULL, -- date the suggestion was marked as accepted |
| 2967 |
rejectedby INT(11) default NULL, -- borrowernumber for the librarian who rejected the suggestion, foreign key linking to the borrowers table |
| 2968 |
rejecteddate date default NULL, -- date the suggestion was marked as rejected |
| 2969 |
`STATUS` varchar(10) NOT NULL default '', -- suggestion status (ASKED, CHECKED, ACCEPTED, or REJECTED) |
| 2970 |
`note` mediumtext, -- note entered on the suggestion |
| 2971 |
`author` varchar(80) default NULL, -- author of the suggested item |
| 2972 |
`title` varchar(255) default NULL, -- title of the suggested item |
| 2973 |
`copyrightdate` smallint(6) default NULL, -- copyright date of the suggested item |
| 2974 |
`publishercode` varchar(255) default NULL, -- publisher of the suggested item |
| 2975 |
`date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the suggestion was updated |
| 2976 |
`volumedesc` varchar(255) default NULL, |
| 2977 |
`publicationyear` smallint(6) default 0, |
| 2978 |
`place` varchar(255) default NULL, -- publication place of the suggested item |
| 2979 |
`isbn` varchar(30) default NULL, -- isbn of the suggested item |
| 2980 |
`biblionumber` int(11) default NULL, -- foreign key linking the suggestion to the biblio table after the suggestion has been ordered |
| 2981 |
`reason` text, -- reason for accepting or rejecting the suggestion |
| 2982 |
`patronreason` text, -- reason for making the suggestion |
| 2983 |
budgetid INT(11), -- foreign key linking the suggested budget to the aqbudgets table |
| 2984 |
branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table |
| 2985 |
collectiontitle text default NULL, -- collection name for the suggested item |
| 2986 |
itemtype VARCHAR(30) default NULL, -- suggested item type |
| 2987 |
quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased |
| 2988 |
currency VARCHAR(10) default NULL, -- suggested currency for the suggested price |
| 2989 |
price DECIMAL(28,6) default NULL, -- suggested price |
| 2990 |
total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency) |
| 2991 |
PRIMARY KEY (`suggestionid`), |
| 2992 |
KEY `suggestedby` (`suggestedby`), |
| 2993 |
KEY `managedby` (`managedby`), |
| 2994 |
KEY `status` (`STATUS`), |
| 2995 |
KEY `biblionumber` (`biblionumber`), |
| 2996 |
KEY `branchcode` (`branchcode`), |
| 2997 |
CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE |
| 2998 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 2999 |
|
| 3000 |
-- |
| 3001 |
-- Table structure for table vendor_edi_accounts |
| 3002 |
-- |
| 3003 |
|
| 3004 |
DROP TABLE IF EXISTS vendor_edi_accounts; |
| 3005 |
CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( |
| 3006 |
id INT(11) NOT NULL auto_increment, |
| 3007 |
description TEXT NOT NULL, |
| 3008 |
host VARCHAR(40), |
| 3009 |
username VARCHAR(40), |
| 3010 |
password VARCHAR(40), |
| 3011 |
last_activity DATE, |
| 3012 |
vendor_id INT(11) REFERENCES aqbooksellers( id ), |
| 3013 |
download_directory TEXT, |
| 3014 |
upload_directory TEXT, |
| 3015 |
san VARCHAR(20), |
| 3016 |
id_code_qualifier VARCHAR(3) default '14', |
| 3017 |
transport VARCHAR(6) default 'FTP', |
| 3018 |
quotes_enabled TINYINT(1) not null default 0, |
| 3019 |
invoices_enabled TINYINT(1) not null default 0, |
| 3020 |
orders_enabled TINYINT(1) not null default 0, |
| 3021 |
responses_enabled TINYINT(1) not null default 0, |
| 3022 |
auto_orders TINYINT(1) not null default 0, |
| 3023 |
shipment_budget INTEGER(11) REFERENCES aqbudgets( budget_id ), |
| 3024 |
plugin varchar(256) NOT NULL DEFAULT "", |
| 3025 |
PRIMARY KEY (id), |
| 3026 |
KEY vendorid (vendor_id), |
| 3027 |
KEY shipmentbudget (shipment_budget), |
| 3028 |
CONSTRAINT vfk_vendor_id FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ), |
| 3029 |
CONSTRAINT vfk_shipment_budget FOREIGN KEY ( shipment_budget ) REFERENCES aqbudgets ( budget_id ) |
| 3030 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3031 |
|
| 3032 |
-- |
| 3033 |
-- Table structure for table edifact_messages |
| 3034 |
-- |
| 3035 |
|
| 3036 |
DROP TABLE IF EXISTS edifact_messages; |
| 3037 |
CREATE TABLE IF NOT EXISTS edifact_messages ( |
| 3038 |
id INT(11) NOT NULL auto_increment, |
| 3039 |
message_type VARCHAR(10) NOT NULL, |
| 3040 |
transfer_date DATE, |
| 3041 |
vendor_id INT(11) REFERENCES aqbooksellers( id ), |
| 3042 |
edi_acct INTEGER REFERENCES vendor_edi_accounts( id ), |
| 3043 |
status TEXT, |
| 3044 |
basketno INT(11) REFERENCES aqbasket( basketno), |
| 3045 |
raw_msg MEDIUMTEXT, |
| 3046 |
filename TEXT, |
| 3047 |
deleted BOOLEAN NOT NULL DEFAULT 0, |
| 3048 |
PRIMARY KEY (id), |
| 3049 |
KEY vendorid ( vendor_id), |
| 3050 |
KEY ediacct (edi_acct), |
| 3051 |
KEY basketno ( basketno), |
| 3052 |
CONSTRAINT emfk_vendor FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3053 |
CONSTRAINT emfk_edi_acct FOREIGN KEY ( edi_acct ) REFERENCES vendor_edi_accounts ( id ) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3054 |
CONSTRAINT emfk_basketno FOREIGN KEY ( basketno ) REFERENCES aqbasket ( basketno ) ON DELETE CASCADE ON UPDATE CASCADE |
| 3055 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3110 |
|
3056 |
|
| 3111 |
-- |
3057 |
-- |
| 3112 |
-- Table structure for table 'aqcontract' |
3058 |
-- Table structure for table aqinvoices |
| 3113 |
-- |
3059 |
-- |
| 3114 |
|
3060 |
|
| 3115 |
DROP TABLE IF EXISTS `aqcontract`; |
3061 |
DROP TABLE IF EXISTS aqinvoices; |
| 3116 |
CREATE TABLE `aqcontract` ( |
3062 |
CREATE TABLE aqinvoices ( |
| 3117 |
`contractnumber` int(11) NOT NULL auto_increment, |
3063 |
invoiceid int(11) NOT NULL AUTO_INCREMENT, -- ID of the invoice, primary key |
| 3118 |
`contractstartdate` date default NULL, |
3064 |
invoicenumber mediumtext NOT NULL, -- Name of invoice |
| 3119 |
`contractenddate` date default NULL, |
3065 |
booksellerid int(11) NOT NULL, -- foreign key to aqbooksellers |
| 3120 |
`contractname` varchar(50) default NULL, |
3066 |
shipmentdate date default NULL, -- date of shipment |
| 3121 |
`contractdescription` mediumtext, |
3067 |
billingdate date default NULL, -- date of billing |
| 3122 |
`booksellerid` int(11) not NULL, |
3068 |
closedate date default NULL, -- invoice close date, NULL means the invoice is open |
| 3123 |
PRIMARY KEY (`contractnumber`), |
3069 |
shipmentcost decimal(28,6) default NULL, -- shipment cost |
| 3124 |
CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`) |
3070 |
shipmentcost_budgetid int(11) default NULL, -- foreign key to aqbudgets, link the shipment cost to a budget |
| 3125 |
REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
3071 |
message_id int(11) default NULL, -- foreign key to edifact invoice message |
| 3126 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; |
3072 |
PRIMARY KEY (invoiceid), |
|
|
3073 |
CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3074 |
CONSTRAINT edifact_msg_fk FOREIGN KEY ( message_id ) REFERENCES edifact_messages ( id ) ON DELETE SET NULL, |
| 3075 |
CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE |
| 3076 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3127 |
|
3077 |
|
| 3128 |
-- |
3078 |
-- |
| 3129 |
-- Table structure for table `aqorders` |
3079 |
-- Table structure for table `aqorders` |
|
Lines 3210-3216
CREATE TABLE `aqorders_items` ( -- information on items entered in the acquisiti
Link Here
|
| 3210 |
CONSTRAINT aqorders_items_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE |
3160 |
CONSTRAINT aqorders_items_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE |
| 3211 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
3161 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3212 |
|
3162 |
|
| 3213 |
|
|
|
| 3214 |
-- |
3163 |
-- |
| 3215 |
-- Table structure for table aqorders_transfers |
3164 |
-- Table structure for table aqorders_transfers |
| 3216 |
-- |
3165 |
-- |
|
Lines 3227-3311
CREATE TABLE aqorders_transfers (
Link Here
|
| 3227 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
3176 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3228 |
|
3177 |
|
| 3229 |
-- |
3178 |
-- |
| 3230 |
-- Table structure for table vendor_edi_accounts |
|
|
| 3231 |
-- |
| 3232 |
|
| 3233 |
DROP TABLE IF EXISTS vendor_edi_accounts; |
| 3234 |
CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( |
| 3235 |
id INT(11) NOT NULL auto_increment, |
| 3236 |
description TEXT NOT NULL, |
| 3237 |
host VARCHAR(40), |
| 3238 |
username VARCHAR(40), |
| 3239 |
password VARCHAR(40), |
| 3240 |
last_activity DATE, |
| 3241 |
vendor_id INT(11) REFERENCES aqbooksellers( id ), |
| 3242 |
download_directory TEXT, |
| 3243 |
upload_directory TEXT, |
| 3244 |
san VARCHAR(20), |
| 3245 |
id_code_qualifier VARCHAR(3) default '14', |
| 3246 |
transport VARCHAR(6) default 'FTP', |
| 3247 |
quotes_enabled TINYINT(1) not null default 0, |
| 3248 |
invoices_enabled TINYINT(1) not null default 0, |
| 3249 |
orders_enabled TINYINT(1) not null default 0, |
| 3250 |
responses_enabled TINYINT(1) not null default 0, |
| 3251 |
auto_orders TINYINT(1) not null default 0, |
| 3252 |
shipment_budget INTEGER(11) REFERENCES aqbudgets( budget_id ), |
| 3253 |
plugin varchar(256) NOT NULL DEFAULT "", |
| 3254 |
PRIMARY KEY (id), |
| 3255 |
KEY vendorid (vendor_id), |
| 3256 |
KEY shipmentbudget (shipment_budget), |
| 3257 |
CONSTRAINT vfk_vendor_id FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ), |
| 3258 |
CONSTRAINT vfk_shipment_budget FOREIGN KEY ( shipment_budget ) REFERENCES aqbudgets ( budget_id ) |
| 3259 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3260 |
|
| 3261 |
-- |
| 3262 |
-- Table structure for table edifact_messages |
| 3263 |
-- |
| 3264 |
|
| 3265 |
DROP TABLE IF EXISTS edifact_messages; |
| 3266 |
CREATE TABLE IF NOT EXISTS edifact_messages ( |
| 3267 |
id INT(11) NOT NULL auto_increment, |
| 3268 |
message_type VARCHAR(10) NOT NULL, |
| 3269 |
transfer_date DATE, |
| 3270 |
vendor_id INT(11) REFERENCES aqbooksellers( id ), |
| 3271 |
edi_acct INTEGER REFERENCES vendor_edi_accounts( id ), |
| 3272 |
status TEXT, |
| 3273 |
basketno INT(11) REFERENCES aqbasket( basketno), |
| 3274 |
raw_msg MEDIUMTEXT, |
| 3275 |
filename TEXT, |
| 3276 |
deleted BOOLEAN NOT NULL DEFAULT 0, |
| 3277 |
PRIMARY KEY (id), |
| 3278 |
KEY vendorid ( vendor_id), |
| 3279 |
KEY ediacct (edi_acct), |
| 3280 |
KEY basketno ( basketno), |
| 3281 |
CONSTRAINT emfk_vendor FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3282 |
CONSTRAINT emfk_edi_acct FOREIGN KEY ( edi_acct ) REFERENCES vendor_edi_accounts ( id ) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3283 |
CONSTRAINT emfk_basketno FOREIGN KEY ( basketno ) REFERENCES aqbasket ( basketno ) ON DELETE CASCADE ON UPDATE CASCADE |
| 3284 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3285 |
|
| 3286 |
-- |
| 3287 |
-- Table structure for table aqinvoices |
| 3288 |
-- |
| 3289 |
|
| 3290 |
DROP TABLE IF EXISTS aqinvoices; |
| 3291 |
CREATE TABLE aqinvoices ( |
| 3292 |
invoiceid int(11) NOT NULL AUTO_INCREMENT, -- ID of the invoice, primary key |
| 3293 |
invoicenumber mediumtext NOT NULL, -- Name of invoice |
| 3294 |
booksellerid int(11) NOT NULL, -- foreign key to aqbooksellers |
| 3295 |
shipmentdate date default NULL, -- date of shipment |
| 3296 |
billingdate date default NULL, -- date of billing |
| 3297 |
closedate date default NULL, -- invoice close date, NULL means the invoice is open |
| 3298 |
shipmentcost decimal(28,6) default NULL, -- shipment cost |
| 3299 |
shipmentcost_budgetid int(11) default NULL, -- foreign key to aqbudgets, link the shipment cost to a budget |
| 3300 |
message_id int(11) default NULL, -- foreign key to edifact invoice message |
| 3301 |
PRIMARY KEY (invoiceid), |
| 3302 |
CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3303 |
CONSTRAINT edifact_msg_fk FOREIGN KEY ( message_id ) REFERENCES edifact_messages ( id ) ON DELETE SET NULL, |
| 3304 |
CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE |
| 3305 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3306 |
|
| 3307 |
|
| 3308 |
-- |
| 3309 |
-- Table structure for table `fieldmapping` |
3179 |
-- Table structure for table `fieldmapping` |
| 3310 |
-- |
3180 |
-- |
| 3311 |
|
3181 |
|
|
Lines 3776-3781
CREATE TABLE IF NOT EXISTS edifact_ean (
Link Here
|
| 3776 |
CONSTRAINT efk_branchcode FOREIGN KEY ( branchcode ) REFERENCES branches ( branchcode ) |
3646 |
CONSTRAINT efk_branchcode FOREIGN KEY ( branchcode ) REFERENCES branches ( branchcode ) |
| 3777 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
3647 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3778 |
|
3648 |
|
|
|
3649 |
-- |
| 3650 |
-- Table structure for table `courses` |
| 3651 |
-- |
| 3652 |
|
| 3653 |
-- The courses table stores the courses created for the |
| 3654 |
-- course reserves feature. |
| 3655 |
|
| 3656 |
DROP TABLE IF EXISTS courses; |
| 3657 |
CREATE TABLE `courses` ( |
| 3658 |
`course_id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the course |
| 3659 |
`department` varchar(80) DEFAULT NULL, -- the authorised value for the DEPARTMENT |
| 3660 |
`course_number` varchar(255) DEFAULT NULL, -- the "course number" assigned to a course |
| 3661 |
`section` varchar(255) DEFAULT NULL, -- the 'section' of a course |
| 3662 |
`course_name` varchar(255) DEFAULT NULL, -- the name of the course |
| 3663 |
`term` varchar(80) DEFAULT NULL, -- the authorised value for the TERM |
| 3664 |
`staff_note` mediumtext, -- the text of the staff only note |
| 3665 |
`public_note` mediumtext, -- the text of the public / opac note |
| 3666 |
`students_count` varchar(20) DEFAULT NULL, -- how many students will be taking this course/section |
| 3667 |
`enabled` enum('yes','no') NOT NULL DEFAULT 'yes', -- determines whether the course is active |
| 3668 |
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 3669 |
PRIMARY KEY (`course_id`) |
| 3670 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3671 |
|
| 3672 |
-- |
| 3673 |
-- Table structure for table `course_instructors` |
| 3674 |
-- |
| 3675 |
|
| 3676 |
-- The course instructors table links Koha borrowers to the |
| 3677 |
-- courses they are teaching. Many instructors can teach many |
| 3678 |
-- courses. course_instructors is just a many-to-many join table. |
| 3679 |
|
| 3680 |
DROP TABLE IF EXISTS course_instructors; |
| 3681 |
CREATE TABLE `course_instructors` ( |
| 3682 |
`course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id |
| 3683 |
`borrowernumber` int(11) NOT NULL, -- foreign key to link to borrowers.borrowernumber for instructor information |
| 3684 |
PRIMARY KEY (`course_id`,`borrowernumber`), |
| 3685 |
KEY `borrowernumber` (`borrowernumber`) |
| 3686 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3687 |
|
| 3688 |
-- |
| 3689 |
-- Constraints for table `course_instructors` |
| 3690 |
-- |
| 3691 |
ALTER TABLE `course_instructors` |
| 3692 |
ADD CONSTRAINT `course_instructors_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`), |
| 3693 |
ADD CONSTRAINT `course_instructors_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE; |
| 3694 |
|
| 3695 |
-- |
| 3696 |
-- Table structure for table `course_items` |
| 3697 |
-- |
| 3698 |
|
| 3699 |
-- If an item is placed on course reserve for one or more courses |
| 3700 |
-- it will have an entry in this table. No matter how many courses an item |
| 3701 |
-- is part of, it will only have one row in this table. |
| 3702 |
|
| 3703 |
DROP TABLE IF EXISTS course_items; |
| 3704 |
CREATE TABLE `course_items` ( |
| 3705 |
`ci_id` int(11) NOT NULL AUTO_INCREMENT, -- course item id |
| 3706 |
`itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve |
| 3707 |
`itype` varchar(10) DEFAULT NULL, -- new itemtype for the item to have while on reserve (optional) |
| 3708 |
`ccode` varchar(10) DEFAULT NULL, -- new category code for the item to have while on reserve (optional) |
| 3709 |
`holdingbranch` varchar(10) DEFAULT NULL, -- new holding branch for the item to have while on reserve (optional) |
| 3710 |
`location` varchar(80) DEFAULT NULL, -- new shelving location for the item to have while on reseve (optional) |
| 3711 |
`enabled` enum('yes','no') NOT NULL DEFAULT 'no', -- if at least one enabled course has this item on reseve, this field will be 'yes', otherwise it will be 'no' |
| 3712 |
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 3713 |
PRIMARY KEY (`ci_id`), |
| 3714 |
UNIQUE KEY `itemnumber` (`itemnumber`), |
| 3715 |
KEY `holdingbranch` (`holdingbranch`) |
| 3716 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3717 |
|
| 3718 |
-- |
| 3719 |
-- Constraints for table `course_items` |
| 3720 |
-- |
| 3721 |
ALTER TABLE `course_items` |
| 3722 |
ADD CONSTRAINT `course_items_ibfk_2` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3723 |
ADD CONSTRAINT `course_items_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE; |
| 3724 |
|
| 3725 |
-- |
| 3726 |
-- Table structure for table `course_reserves` |
| 3727 |
-- |
| 3728 |
|
| 3729 |
-- This table connects an item placed on course reserve to a course it is on reserve for. |
| 3730 |
-- There will be a row in this table for each course an item is on reserve for. |
| 3731 |
|
| 3732 |
DROP TABLE IF EXISTS course_reserves; |
| 3733 |
CREATE TABLE `course_reserves` ( |
| 3734 |
`cr_id` int(11) NOT NULL AUTO_INCREMENT, |
| 3735 |
`course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id |
| 3736 |
`ci_id` int(11) NOT NULL, -- foreign key to link to courses_items.ci_id |
| 3737 |
`staff_note` mediumtext, -- staff only note |
| 3738 |
`public_note` mediumtext, -- public, OPAC visible note |
| 3739 |
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 3740 |
PRIMARY KEY (`cr_id`), |
| 3741 |
UNIQUE KEY `pseudo_key` (`course_id`,`ci_id`), |
| 3742 |
KEY `course_id` (`course_id`) |
| 3743 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3744 |
|
| 3745 |
-- |
| 3746 |
-- Constraints for table `course_reserves` |
| 3747 |
-- |
| 3748 |
ALTER TABLE `course_reserves` |
| 3749 |
ADD CONSTRAINT `course_reserves_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`), |
| 3750 |
ADD CONSTRAINT `course_reserves_ibfk_2` FOREIGN KEY (`ci_id`) REFERENCES `course_items` (`ci_id`) ON DELETE CASCADE ON UPDATE CASCADE; |
| 3751 |
|
| 3752 |
-- |
| 3753 |
-- Table structure for table `hold_fill_targets` |
| 3754 |
-- |
| 3755 |
|
| 3756 |
DROP TABLE IF EXISTS `hold_fill_targets`; |
| 3757 |
CREATE TABLE `hold_fill_targets` ( |
| 3758 |
`borrowernumber` int(11) NOT NULL, |
| 3759 |
`biblionumber` int(11) NOT NULL, |
| 3760 |
`itemnumber` int(11) NOT NULL, |
| 3761 |
`source_branchcode` varchar(10) default NULL, |
| 3762 |
`item_level_request` tinyint(4) NOT NULL default 0, |
| 3763 |
PRIMARY KEY `itemnumber` (`itemnumber`), |
| 3764 |
KEY `bib_branch` (`biblionumber`, `source_branchcode`), |
| 3765 |
CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`) |
| 3766 |
REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3767 |
CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`) |
| 3768 |
REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3769 |
CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`) |
| 3770 |
REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 3771 |
CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`) |
| 3772 |
REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE |
| 3773 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| 3774 |
|
| 3779 |
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
3775 |
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; |
| 3780 |
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
3776 |
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; |
| 3781 |
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
3777 |
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
| 3782 |
- |
|
|