View | Details | Raw Unified | Return to bug 13669
Collapse All | Expand All

(-)a/installer/data/mysql/kohastructure.sql (-905 / +900 lines)
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
- 

Return to bug 13669