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 |
- |
|
|