Bugzilla – Attachment 51601 Details for
Bug 13669
Web installer fails to load sample data on MySQL 5.6+
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 13669: (followup) Fix kohastructure.sql so FK dependencies are sorted
Bug-13669-followup-Fix-kohastructuresql-so-FK-depe.patch (text/plain), 110.40 KB, created by
Chris Cormack
on 2016-05-18 21:47:39 UTC
(
hide
)
Description:
Bug 13669: (followup) Fix kohastructure.sql so FK dependencies are sorted
Filename:
MIME Type:
Creator:
Chris Cormack
Created:
2016-05-18 21:47:39 UTC
Size:
110.40 KB
patch
obsolete
>From 2402044f2616ac330cca4e0e7ef31b9b20987a3f Mon Sep 17 00:00:00 2001 >From: Tomas Cohen Arazi <tomascohen@theke.io> >Date: Wed, 18 May 2016 17:34:06 -0300 >Subject: [PATCH] Bug 13669: (followup) Fix kohastructure.sql so FK > dependencies are sorted > >The current table creation order is left to mysql's strategy, which is not >suitable for parsing the SQL files and passing one statement at a time in >the current order. > >This patch just moves table creation statements around so FK constraints are >defined for previously created tables. > >Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz> >--- > installer/data/mysql/kohastructure.sql | 1804 ++++++++++++++++---------------- > 1 file changed, 900 insertions(+), 904 deletions(-) > >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index 781d26d..42bff46 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -35,6 +35,19 @@ CREATE TABLE `auth_header` ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >+-- Table structure for table `auth_types` >+-- >+ >+DROP TABLE IF EXISTS `auth_types`; >+CREATE TABLE `auth_types` ( >+ `authtypecode` varchar(10) NOT NULL default '', >+ `authtypetext` varchar(255) NOT NULL default '', >+ `auth_tag_to_report` varchar(3) NOT NULL default '', >+ `summary` mediumtext NOT NULL, >+ PRIMARY KEY (`authtypecode`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- > -- Table structure for table `auth_subfield_structure` > -- > >@@ -80,19 +93,6 @@ CREATE TABLE `auth_tag_structure` ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `auth_types` >--- >- >-DROP TABLE IF EXISTS `auth_types`; >-CREATE TABLE `auth_types` ( >- `authtypecode` varchar(10) NOT NULL default '', >- `authtypetext` varchar(255) NOT NULL default '', >- `auth_tag_to_report` varchar(3) NOT NULL default '', >- `summary` mediumtext NOT NULL, >- PRIMARY KEY (`authtypecode`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- > -- Table structure for table `authorised_values` > -- > >@@ -194,94 +194,6 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `borrowers` >--- >- >-DROP TABLE IF EXISTS `borrowers`; >-CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members >- `borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers >- `cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers >- `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname) >- `firstname` text, -- patron/borrower's first name >- `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs. >- `othernames` mediumtext, -- any other names associated with the patron/borrower >- `initials` text, -- initials for your patron/borrower >- `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address >- `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address >- `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address >- `address2` text, -- the second address line for your patron/borrower's primary address >- `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address >- `state` text default NULL, -- the state or province for your patron/borrower's primary address >- `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address >- `country` text, -- the country for your patron/borrower's primary address >- `email` mediumtext, -- the primary email address for your patron/borrower's primary address >- `phone` text, -- the primary phone number for your patron/borrower's primary address >- `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address >- `fax` mediumtext, -- the fax number for your patron/borrower's primary address >- `emailpro` text, -- the secondary email addres for your patron/borrower's primary address >- `phonepro` text, -- the secondary phone number for your patron/borrower's primary address >- `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address >- `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address >- `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address >- `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address >- `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address >- `B_state` text default NULL, -- the state for your patron/borrower's alternate address >- `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address >- `B_country` text, -- the country for your patron/borrower's alternate address >- `B_email` text, -- the patron/borrower's alternate email address >- `B_phone` mediumtext, -- the patron/borrower's alternate phone number >- `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD) >- `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch >- `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category >- `dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD) >- `dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD) >- `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 >- `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 >- `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) >- `debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron >- `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name >- `contactfirstname` text, -- used for children to include first name of guarentor >- `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor >- `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations >- `borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client >- `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor >- `sex` varchar(1) default NULL, -- patron/borrower's gender >- `password` varchar(60) default NULL, -- patron/borrower's encrypted password >- `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions >- `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in >- `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client >- `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address >- `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library >- `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library >- `altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower >- `altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower >- `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower >- `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower >- `altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower >- `altcontactstate` text default NULL, -- the state for the alternate contact for the patron/borrower >- `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower >- `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower >- `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower >- `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) >- `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber >- `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history >- `privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts >- UNIQUE KEY `cardnumber` (`cardnumber`), >- PRIMARY KEY `borrowernumber` (`borrowernumber`), >- KEY `categorycode` (`categorycode`), >- KEY `branchcode` (`branchcode`), >- UNIQUE KEY `userid` (`userid`), >- KEY `guarantorid` (`guarantorid`), >- KEY `surname_idx` (`surname`(255)), >- KEY `firstname_idx` (`firstname`(255)), >- KEY `othernames_idx` (`othernames`(255)), >- KEY `sms_provider_id` (`sms_provider_id`), >- CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), >- CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`), >- CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ON UPDATE CASCADE ON DELETE SET NULL >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- > -- Table structure for table `borrower_attribute_types` > -- > >@@ -302,63 +214,6 @@ CREATE TABLE `borrower_attribute_types` ( -- definitions for custom patron field > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `borrower_attributes` >--- >- >-DROP TABLE IF EXISTS `borrower_attributes`; >-CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as extended patron attributes linked to patrons/borrowers >- `borrowernumber` int(11) NOT NULL, -- foreign key from the borrowers table, defines which patron/borrower has this attribute >- `code` varchar(10) NOT NULL, -- foreign key from the borrower_attribute_types table, defines which custom field this value was entered for >- `attribute` varchar(255) default NULL, -- custom patron field value >- KEY `borrowernumber` (`borrowernumber`), >- KEY `code_attribute` (`code`, `attribute`), >- CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) >- ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`) >- ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table `borrower_debarments` >--- >- >-DROP TABLE IF EXISTS `borrower_debarments`; >-CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record >- borrower_debarment_id int(11) NOT NULL AUTO_INCREMENT, -- unique key for the restriction >- borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted >- expiration date DEFAULT NULL, -- expiration date of the restriction >- `type` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') NOT NULL DEFAULT 'MANUAL', -- type of restriction >- `comment` text, -- comments about the restriction >- manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction >- created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added >- updated timestamp NULL DEFAULT NULL, -- date the restriction was updated >- PRIMARY KEY (borrower_debarment_id), >- KEY borrowernumber (borrowernumber), >- CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) >- ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >- >--- >--- Table structure for table `branch_item_rules` >--- >- >-DROP TABLE IF EXISTS `branch_item_rules`; >-CREATE TABLE `branch_item_rules` ( -- information entered in the circulation and fine rules under 'Holds policy by item type' >- `branchcode` varchar(10) NOT NULL, -- the branch this rule is for (branches.branchcode) >- `itemtype` varchar(10) NOT NULL, -- the item type this rule applies to (items.itype) >- `holdallowed` tinyint(1) default NULL, -- the number of holds allowed >- hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode >- `returnbranch` varchar(15) default NULL, -- the branch the item returns to (homebranch, holdingbranch, noreturn) >- PRIMARY KEY (`itemtype`,`branchcode`), >- KEY `branch_item_rules_ibfk_2` (`branchcode`), >- CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) >- ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) >- ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- > -- Table structure for table `borrower_password_recovery` > -- > >@@ -372,25 +227,6 @@ CREATE TABLE IF NOT EXISTS `borrower_password_recovery` ( -- holds information a > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table borrower_sync >--- >- >-DROP TABLE IF EXISTS `borrower_sync`; >-CREATE TABLE borrower_sync ( >- borrowersyncid int(11) NOT NULL AUTO_INCREMENT, -- Primary key, unique identifier >- borrowernumber int(11) NOT NULL, -- Connects data about synchronisations to a borrower >- 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 >- sync tinyint(1) NOT NULL DEFAULT '0', -- A boolean (1/0) for turning syncing off and on for individual borrowers >- 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. >- 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. >- hashed_pin varchar(64) DEFAULT NULL, -- Perhaps specific to The Norwegian national patron database, this column holds a hashed PIN code >- PRIMARY KEY (borrowersyncid), >- KEY borrowernumber (borrowernumber), >- CONSTRAINT borrower_sync_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >- >--- > -- Table structure for table `branchcategories` > -- > >@@ -450,27 +286,6 @@ CREATE TABLE `branchrelations` ( -- this table links libraries/branches to group > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `branchtransfers` >--- >- >-DROP TABLE IF EXISTS `branchtransfers`; >-CREATE TABLE `branchtransfers` ( -- information for items that are in transit between branches >- `itemnumber` int(11) NOT NULL default 0, -- the itemnumber that it is in transit (items.itemnumber) >- `datesent` datetime default NULL, -- the date the transfer was initialized >- `frombranch` varchar(10) NOT NULL default '', -- the branch the transfer is coming from >- `datearrived` datetime default NULL, -- the date the transfer arrived at its destination >- `tobranch` varchar(10) NOT NULL default '', -- the branch the transfer was going to >- `comments` mediumtext, -- any comments related to the transfer >- KEY `frombranch` (`frombranch`), >- KEY `tobranch` (`tobranch`), >- KEY `itemnumber` (`itemnumber`), >- CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >- >--- > -- Table structure for table `browser` > -- > DROP TABLE IF EXISTS `browser`; >@@ -529,6 +344,7 @@ ALTER TABLE `collections` > -- > -- Table: collections_tracking > -- >+ > DROP TABLE IF EXISTS collections_tracking; > CREATE TABLE collections_tracking ( > collections_tracking_id integer(11) NOT NULL auto_increment, >@@ -538,128 +354,24 @@ CREATE TABLE collections_tracking ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `courses` >+-- Table structure for table `branch_borrower_circ_rules` > -- > >--- The courses table stores the courses created for the >--- course reserves feature. >- >-DROP TABLE IF EXISTS courses; >-CREATE TABLE `courses` ( >- `course_id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the course >- `department` varchar(80) DEFAULT NULL, -- the authorised value for the DEPARTMENT >- `course_number` varchar(255) DEFAULT NULL, -- the "course number" assigned to a course >- `section` varchar(255) DEFAULT NULL, -- the 'section' of a course >- `course_name` varchar(255) DEFAULT NULL, -- the name of the course >- `term` varchar(80) DEFAULT NULL, -- the authorised value for the TERM >- `staff_note` mediumtext, -- the text of the staff only note >- `public_note` mediumtext, -- the text of the public / opac note >- `students_count` varchar(20) DEFAULT NULL, -- how many students will be taking this course/section >- `enabled` enum('yes','no') NOT NULL DEFAULT 'yes', -- determines whether the course is active >- `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, >- PRIMARY KEY (`course_id`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+DROP TABLE IF EXISTS `branch_borrower_circ_rules`; >+CREATE TABLE `branch_borrower_circ_rules` ( -- includes default circulation rules for patron categories found under "Checkout limit by patron category" >+ `branchcode` VARCHAR(10) NOT NULL, -- the branch this rule applies to (branches.branchcode) >+ `categorycode` VARCHAR(10) NOT NULL, -- the patron category this rule applies to (categories.categorycode) >+ `maxissueqty` int(4) default NULL, -- the maximum number of checkouts this patron category can have at this branch >+ `maxonsiteissueqty` int(4) default NULL, -- the maximum number of on-site checkouts this patron category can have at this branch >+ PRIMARY KEY (`categorycode`, `branchcode`), >+ CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) >+ ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) >+ ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `course_instructors` >--- >- >--- The course instructors table links Koha borrowers to the >--- courses they are teaching. Many instructors can teach many >--- courses. course_instructors is just a many-to-many join table. >- >-DROP TABLE IF EXISTS course_instructors; >-CREATE TABLE `course_instructors` ( >- `course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id >- `borrowernumber` int(11) NOT NULL, -- foreign key to link to borrowers.borrowernumber for instructor information >- PRIMARY KEY (`course_id`,`borrowernumber`), >- KEY `borrowernumber` (`borrowernumber`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Constraints for table `course_instructors` >--- >-ALTER TABLE `course_instructors` >- ADD CONSTRAINT `course_instructors_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`), >- ADD CONSTRAINT `course_instructors_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE; >- >--- >--- Table structure for table `course_items` >--- >- >--- If an item is placed on course reserve for one or more courses >--- it will have an entry in this table. No matter how many courses an item >--- is part of, it will only have one row in this table. >- >-DROP TABLE IF EXISTS course_items; >-CREATE TABLE `course_items` ( >- `ci_id` int(11) NOT NULL AUTO_INCREMENT, -- course item id >- `itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve >- `itype` varchar(10) DEFAULT NULL, -- new itemtype for the item to have while on reserve (optional) >- `ccode` varchar(10) DEFAULT NULL, -- new category code for the item to have while on reserve (optional) >- `holdingbranch` varchar(10) DEFAULT NULL, -- new holding branch for the item to have while on reserve (optional) >- `location` varchar(80) DEFAULT NULL, -- new shelving location for the item to have while on reseve (optional) >- `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' >- `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, >- PRIMARY KEY (`ci_id`), >- UNIQUE KEY `itemnumber` (`itemnumber`), >- KEY `holdingbranch` (`holdingbranch`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Constraints for table `course_items` >--- >-ALTER TABLE `course_items` >- ADD CONSTRAINT `course_items_ibfk_2` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, >- ADD CONSTRAINT `course_items_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE; >- >--- >--- Table structure for table `course_reserves` >--- >- >--- This table connects an item placed on course reserve to a course it is on reserve for. >--- There will be a row in this table for each course an item is on reserve for. >- >-DROP TABLE IF EXISTS course_reserves; >-CREATE TABLE `course_reserves` ( >- `cr_id` int(11) NOT NULL AUTO_INCREMENT, >- `course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id >- `ci_id` int(11) NOT NULL, -- foreign key to link to courses_items.ci_id >- `staff_note` mediumtext, -- staff only note >- `public_note` mediumtext, -- public, OPAC visible note >- `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, >- PRIMARY KEY (`cr_id`), >- UNIQUE KEY `pseudo_key` (`course_id`,`ci_id`), >- KEY `course_id` (`course_id`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Constraints for table `course_reserves` >--- >-ALTER TABLE `course_reserves` >- ADD CONSTRAINT `course_reserves_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`), >- ADD CONSTRAINT `course_reserves_ibfk_2` FOREIGN KEY (`ci_id`) REFERENCES `course_items` (`ci_id`) ON DELETE CASCADE ON UPDATE CASCADE; >- >- >--- >--- Table structure for table `branch_borrower_circ_rules` >--- >- >-DROP TABLE IF EXISTS `branch_borrower_circ_rules`; >-CREATE TABLE `branch_borrower_circ_rules` ( -- includes default circulation rules for patron categories found under "Checkout limit by patron category" >- `branchcode` VARCHAR(10) NOT NULL, -- the branch this rule applies to (branches.branchcode) >- `categorycode` VARCHAR(10) NOT NULL, -- the patron category this rule applies to (categories.categorycode) >- `maxissueqty` int(4) default NULL, -- the maximum number of checkouts this patron category can have at this branch >- `maxonsiteissueqty` int(4) default NULL, -- the maximum number of on-site checkouts this patron category can have at this branch >- PRIMARY KEY (`categorycode`, `branchcode`), >- CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) >- ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) >- ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table `default_borrower_circ_rules` >+-- Table structure for table `default_borrower_circ_rules` > -- > > DROP TABLE IF EXISTS `default_borrower_circ_rules`; >@@ -690,20 +402,6 @@ CREATE TABLE `default_branch_circ_rules` ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `default_branch_item_rules` >--- >-DROP TABLE IF EXISTS `default_branch_item_rules`; >-CREATE TABLE `default_branch_item_rules` ( >- `itemtype` varchar(10) NOT NULL, >- `holdallowed` tinyint(1) default NULL, >- hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode >- `returnbranch` varchar(15) default NULL, >- PRIMARY KEY (`itemtype`), >- CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) >- ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- > -- Table structure for table `default_circ_rules` > -- > >@@ -1003,30 +701,6 @@ CREATE TABLE `export_format` ( > PRIMARY KEY (`export_format_id`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Used for CSV export'; > >- >--- >--- Table structure for table `hold_fill_targets` >--- >- >-DROP TABLE IF EXISTS `hold_fill_targets`; >-CREATE TABLE hold_fill_targets ( >- `borrowernumber` int(11) NOT NULL, >- `biblionumber` int(11) NOT NULL, >- `itemnumber` int(11) NOT NULL, >- `source_branchcode` varchar(10) default NULL, >- `item_level_request` tinyint(4) NOT NULL default 0, >- PRIMARY KEY `itemnumber` (`itemnumber`), >- KEY `bib_branch` (`biblionumber`, `source_branchcode`), >- CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`) >- REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`) >- REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`) >- REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`) >- REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- > -- > -- Table structure for table `import_batches` > -- >@@ -1153,35 +827,6 @@ CREATE TABLE `import_items` ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `issues` >--- >- >-DROP TABLE IF EXISTS `issues`; >-CREATE TABLE `issues` ( -- information related to check outs or issues >- `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table >- `borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to >- `itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out >- `date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss) >- `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out >- `returndate` datetime default NULL, -- date the item was returned, will be NULL until moved to old_issues >- `lastreneweddate` datetime default NULL, -- date the item was last renewed >- `return` varchar(4) default NULL, >- `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed >- `auto_renew` BOOLEAN default FALSE, -- automatic renewal >- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched >- `issuedate` datetime default NULL, -- date the item was checked out or issued >- `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag >- PRIMARY KEY (`issue_id`), >- UNIQUE KEY `itemnumber` (`itemnumber`), >- KEY `issuesborridx` (`borrowernumber`), >- KEY `itemnumber_idx` (`itemnumber`), >- KEY `branchcode_idx` (`branchcode`), >- KEY `bordate` (`borrowernumber`,`timestamp`), >- CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE, >- CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- > -- Table structure for table `issuingrules` > -- > >@@ -1289,22 +934,6 @@ CREATE TABLE `items` ( -- holdings/item information > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `items_last_borrower` >--- >- >-CREATE TABLE IF NOT EXISTS `items_last_borrower` ( >- `id` int(11) NOT NULL AUTO_INCREMENT, >- `itemnumber` int(11) NOT NULL, >- `borrowernumber` int(11) NOT NULL, >- `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, >- PRIMARY KEY (`id`), >- UNIQUE KEY `itemnumber` (`itemnumber`), >- KEY `borrowernumber` (`borrowernumber`), >- CONSTRAINT `items_last_borrower_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT `items_last_borrower_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- > -- Table structure for table `itemtypes` > -- > >@@ -1326,27 +955,57 @@ CREATE TABLE `itemtypes` ( -- defines the item types > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `creator_batches` >+-- Table structure for table `default_branch_item_rules` > -- > >-DROP TABLE IF EXISTS `creator_batches`; >-SET @saved_cs_client = @@character_set_client; >-SET character_set_client = utf8; >-CREATE TABLE `creator_batches` ( >- `label_id` int(11) NOT NULL AUTO_INCREMENT, >- `batch_id` int(10) NOT NULL DEFAULT '1', >- `item_number` int(11) DEFAULT NULL, >- `borrower_number` int(11) DEFAULT NULL, >- `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, >- `branch_code` varchar(10) NOT NULL DEFAULT 'NB', >- `creator` char(15) NOT NULL DEFAULT 'Labels', >- PRIMARY KEY (`label_id`), >- KEY `branch_fk_constraint` (`branch_code`), >- KEY `item_fk_constraint` (`item_number`), >- KEY `borrower_fk_constraint` (`borrower_number`), >- CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE, >- CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE >+DROP TABLE IF EXISTS `default_branch_item_rules`; >+CREATE TABLE `default_branch_item_rules` ( >+ `itemtype` varchar(10) NOT NULL, >+ `holdallowed` tinyint(1) default NULL, >+ hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode >+ `returnbranch` varchar(15) default NULL, >+ PRIMARY KEY (`itemtype`), >+ CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) >+ ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `branchtransfers` >+-- >+ >+DROP TABLE IF EXISTS `branchtransfers`; >+CREATE TABLE `branchtransfers` ( -- information for items that are in transit between branches >+ `itemnumber` int(11) NOT NULL default 0, -- the itemnumber that it is in transit (items.itemnumber) >+ `datesent` datetime default NULL, -- the date the transfer was initialized >+ `frombranch` varchar(10) NOT NULL default '', -- the branch the transfer is coming from >+ `datearrived` datetime default NULL, -- the date the transfer arrived at its destination >+ `tobranch` varchar(10) NOT NULL default '', -- the branch the transfer was going to >+ `comments` mediumtext, -- any comments related to the transfer >+ KEY `frombranch` (`frombranch`), >+ KEY `tobranch` (`tobranch`), >+ KEY `itemnumber` (`itemnumber`), >+ CONSTRAINT `branchtransfers_ibfk_1` FOREIGN KEY (`frombranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT `branchtransfers_ibfk_2` FOREIGN KEY (`tobranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT `branchtransfers_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `branch_item_rules` >+-- >+ >+DROP TABLE IF EXISTS `branch_item_rules`; >+CREATE TABLE `branch_item_rules` ( -- information entered in the circulation and fine rules under 'Holds policy by item type' >+ `branchcode` varchar(10) NOT NULL, -- the branch this rule is for (branches.branchcode) >+ `itemtype` varchar(10) NOT NULL, -- the item type this rule applies to (items.itype) >+ `holdallowed` tinyint(1) default NULL, -- the number of holds allowed >+ hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode >+ `returnbranch` varchar(15) default NULL, -- the branch the item returns to (homebranch, holdingbranch, noreturn) >+ PRIMARY KEY (`itemtype`,`branchcode`), >+ KEY `branch_item_rules_ibfk_2` (`branchcode`), >+ CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) >+ ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) >+ ON DELETE CASCADE ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >@@ -1421,26 +1080,7 @@ CREATE TABLE `creator_templates` ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `letter` >--- >- >-DROP TABLE IF EXISTS `letter`; >-CREATE TABLE `letter` ( -- table for all notice templates in Koha >- `module` varchar(20) NOT NULL default '', -- Koha module that triggers this notice or slip >- `code` varchar(20) NOT NULL default '', -- unique identifier for this notice or slip >- `branchcode` varchar(10) NOT NULL default '', -- the branch this notice or slip is used at (branches.branchcode) >- `name` varchar(100) NOT NULL default '', -- plain text name for this notice or slip >- `is_html` tinyint(1) default 0, -- does this notice or slip use HTML (1 for yes, 0 for no) >- `title` varchar(200) NOT NULL default '', -- subject line of the notice >- `content` text, -- body text for the notice or slip >- `message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice >- PRIMARY KEY (`module`,`code`, `branchcode`, `message_transport_type`), >- CONSTRAINT `message_transport_type_fk` FOREIGN KEY (`message_transport_type`) >- REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table `marc_subfield_structure` >+-- Table structure for table `marc_subfield_structure` > -- > > DROP TABLE IF EXISTS `marc_subfield_structure`; >@@ -1654,97 +1294,6 @@ CREATE TABLE `oai_sets_biblios` ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `old_issues` >--- >- >-DROP TABLE IF EXISTS `old_issues`; >-CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned >- `issue_id` int(11) NOT NULL, -- primary key for issues table >- `borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to >- `itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out >- `date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd) >- `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out >- `returndate` datetime default NULL, -- date the item was returned >- `lastreneweddate` datetime default NULL, -- date the item was last renewed >- `return` varchar(4) default NULL, >- `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed >- `auto_renew` BOOLEAN default FALSE, -- automatic renewal >- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched >- `issuedate` datetime default NULL, -- date the item was checked out or issued >- `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag >- PRIMARY KEY (`issue_id`), >- KEY `old_issuesborridx` (`borrowernumber`), >- KEY `old_issuesitemidx` (`itemnumber`), >- KEY `branchcode_idx` (`branchcode`), >- KEY `old_bordate` (`borrowernumber`,`timestamp`), >- CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) >- ON DELETE SET NULL ON UPDATE SET NULL, >- CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) >- ON DELETE SET NULL ON UPDATE SET NULL >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table `old_reserves` >--- >-DROP TABLE IF EXISTS `old_reserves`; >-CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled) >- `reserve_id` int(11) NOT NULL, -- primary key >- `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for >- `reservedate` date default NULL, -- the date the hold was places >- `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on >- `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at >- `notificationdate` date default NULL, -- currently unused >- `reminderdate` date default NULL, -- currently unused >- `cancellationdate` date default NULL, -- the date this hold was cancelled >- `reservenotes` mediumtext, -- notes related to this hold >- `priority` smallint(6) default NULL, -- where in the queue the patron sits >- `found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed >- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated >- `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 >- `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library >- `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) >- `lowestPriority` tinyint(1) NOT NULL, -- has this hold been pinned to the lowest priority in the holds queue (1 for yes, 0 for no) >- `suspend` BOOLEAN NOT NULL DEFAULT 0, -- in this hold suspended (1 for yes, 0 for no) >- `suspend_until` DATETIME NULL DEFAULT NULL, -- the date this hold is suspended until (NULL for infinitely) >- `itemtype` VARCHAR(10) NULL DEFAULT NULL, -- If record level hold, the optional itemtype of the item the patron is requesting >- PRIMARY KEY (`reserve_id`), >- KEY `old_reserves_borrowernumber` (`borrowernumber`), >- KEY `old_reserves_biblionumber` (`biblionumber`), >- KEY `old_reserves_itemnumber` (`itemnumber`), >- KEY `old_reserves_branchcode` (`branchcode`), >- KEY `old_reserves_itemtype` (`itemtype`), >- CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) >- ON DELETE SET NULL ON UPDATE SET NULL, >- CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) >- ON DELETE SET NULL ON UPDATE SET NULL, >- CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) >- ON DELETE SET NULL ON UPDATE SET NULL, >- CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) >- ON DELETE SET NULL ON UPDATE SET NULL >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table `opac_news` >--- >- >-DROP TABLE IF EXISTS `opac_news`; >-CREATE TABLE `opac_news` ( -- data from the news tool >- `idnew` int(10) unsigned NOT NULL auto_increment, -- unique identifier for the news article >- `branchcode` varchar(10) default NULL, -- branch code users to create branch specific news, NULL is every branch. >- `title` varchar(250) NOT NULL default '', -- title of the news article >- `new` text NOT NULL, -- the body of your news article >- `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) >- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- pulibcation date and time >- `expirationdate` date default NULL, -- date the article is set to expire or no longer be visible >- `number` int(11) default NULL, -- the order in which this article appears in that specific location >- `borrowernumber` int(11) default NULL, -- The user who created the news article >- PRIMARY KEY (`idnew`), >- CONSTRAINT `borrowernumber_fk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, >- CONSTRAINT opac_news_branchcode_ibfk FOREIGN KEY (branchcode) REFERENCES branches (branchcode) >- ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- > -- Table structure for table `overduerules` > -- > >@@ -1766,19 +1315,6 @@ CREATE TABLE `overduerules` ( -- overdue notice status and triggers > UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > >--- >--- Table structure for table `patronimage` >--- >- >-DROP TABLE IF EXISTS `patronimage`; >-CREATE TABLE `patronimage` ( -- information related to patron images >- `borrowernumber` int(11) NOT NULL, -- the borrowernumber of the patron this image is attached to (borrowers.borrowernumber) >- `mimetype` varchar(15) NOT NULL, -- the format of the image (png, jpg, etc) >- `imagefile` mediumblob NOT NULL, -- the image >- PRIMARY KEY (`borrowernumber`), >- CONSTRAINT `patronimage_fk1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- > -- Table structure for table `pending_offline_operations` > -- > -- this table is MyISAM, InnoDB tables are growing only and this table is filled/emptied/filled/emptied... >@@ -1864,6 +1400,433 @@ CREATE TABLE reports_dictionary ( -- definitions (or snippets of SQL) stored for > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >+-- Table structure for table `saved_sql` >+-- >+ >+DROP TABLE IF EXISTS `saved_sql`; >+CREATE TABLE saved_sql ( -- saved sql reports >+ `id` int(11) NOT NULL auto_increment, -- unique id and primary key assigned by Koha >+ `borrowernumber` int(11) default NULL, -- the staff member who created this report (borrowers.borrowernumber) >+ `date_created` datetime default NULL, -- the date this report was created >+ `last_modified` datetime default NULL, -- the date this report was last edited >+ `savedsql` text, -- the SQL for this report >+ `last_run` datetime default NULL, >+ `report_name` varchar(255) NOT NULL default '', -- the name of this report >+ `type` varchar(255) default NULL, -- always 1 for tabular >+ `notes` text, -- the notes or description given to this report >+ `cache_expiry` int NOT NULL default 300, >+ `public` boolean NOT NULL default FALSE, >+ report_area varchar(6) default NULL, >+ report_group varchar(80) default NULL, >+ report_subgroup varchar(80) default NULL, >+ PRIMARY KEY (`id`), >+ KEY sql_area_group_idx (report_group, report_subgroup), >+ KEY boridx (`borrowernumber`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+ >+-- >+-- Table structure for `saved_reports` >+-- >+ >+DROP TABLE IF EXISTS `saved_reports`; >+CREATE TABLE saved_reports ( >+ `id` int(11) NOT NULL auto_increment, >+ `report_id` int(11) default NULL, >+ `report` longtext, >+ `date_run` datetime default NULL, >+ PRIMARY KEY (`id`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table 'search_field' >+-- >+ >+DROP TABLE IF EXISTS search_field; >+CREATE TABLE `search_field` ( >+ `id` int(11) NOT NULL AUTO_INCREMENT, >+ `name` varchar(255) NOT NULL COMMENT 'the name of the field as it will be stored in the search engine', >+ `label` varchar(255) NOT NULL COMMENT 'the human readable name of the field, for display', >+ `type` ENUM('string', 'date', 'number', 'boolean', 'sum') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine', >+ PRIMARY KEY (`id`), >+ UNIQUE KEY (`name`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `search_history` >+-- >+ >+DROP TABLE IF EXISTS `search_history`; >+CREATE TABLE IF NOT EXISTS `search_history` ( -- patron's opac search history >+ `id` int(11) NOT NULL auto_increment, -- search history id >+ `userid` int(11) NOT NULL, -- the patron who performed the search (borrowers.borrowernumber) >+ `sessionid` varchar(32) NOT NULL, -- a system generated session id >+ `query_desc` varchar(255) NOT NULL, -- the search that was performed >+ `query_cgi` text NOT NULL, -- the string to append to the search url to rerun the search >+ `type` varchar(16) NOT NULL DEFAULT 'biblio', -- search type, must be 'biblio' or 'authority' >+ `total` int(11) NOT NULL, -- the total of results found >+ `time` timestamp NOT NULL default CURRENT_TIMESTAMP, -- the date and time the search was run >+ KEY `userid` (`userid`), >+ KEY `sessionid` (`sessionid`), >+ PRIMARY KEY (`id`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Opac search history results'; >+ >+-- >+-- Table structure for table 'search_marc_map' >+-- >+ >+DROP TABLE IF EXISTS search_marc_map; >+CREATE TABLE `search_marc_map` ( >+ id int(11) NOT NULL AUTO_INCREMENT, >+ index_name ENUM('biblios','authorities') NOT NULL COMMENT 'what storage index this map is for', >+ marc_type ENUM('marc21', 'unimarc', 'normarc') NOT NULL COMMENT 'what MARC type this map is for', >+ marc_field VARCHAR(255) NOT NULL COMMENT 'the MARC specifier for this field', >+ PRIMARY KEY(`id`), >+ UNIQUE key(index_name, marc_field, marc_type), >+ INDEX (`index_name`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table 'search_marc_to_field' >+-- >+ >+DROP TABLE IF EXISTS search_marc_to_field; >+CREATE TABLE `search_marc_to_field` ( >+ search_marc_map_id int(11) NOT NULL, >+ search_field_id int(11) NOT NULL, >+ facet boolean DEFAULT FALSE COMMENT 'true if a facet field should be generated for this', >+ suggestible boolean DEFAULT FALSE COMMENT 'true if this field can be used to generate suggestions for browse', >+ sort boolean DEFAULT NULL COMMENT 'true/false creates special sort handling, null doesn''t', >+ PRIMARY KEY(search_marc_map_id, search_field_id), >+ FOREIGN KEY(search_marc_map_id) REFERENCES search_marc_map(id) ON DELETE CASCADE ON UPDATE CASCADE, >+ FOREIGN KEY(search_field_id) REFERENCES search_field(id) ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `serial` >+-- >+ >+DROP TABLE IF EXISTS `serial`; >+CREATE TABLE `serial` ( -- issues related to subscriptions >+ `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue >+ `biblionumber` varchar(100) NOT NULL default '', -- foreign key for the biblio.biblionumber that this issue is attached to >+ `subscriptionid` varchar(100) NOT NULL default '', -- foreign key to the subscription.subscriptionid that this issue is part of >+ `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc) >+ `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information >+ `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information >+ `serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information >+ `status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions) >+ `planneddate` date default NULL, -- date expected >+ `notes` text, -- notes >+ `publisheddate` date default NULL, -- date published >+ publisheddatetext varchar(100) default NULL, -- date published (descriptive) >+ `claimdate` date default NULL, -- date claimed >+ claims_count int(11) default 0, -- number of claims made related to this issue >+ `routingnotes` text, -- notes from the routing list >+ PRIMARY KEY (`serialid`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `sessions` >+-- >+ >+DROP TABLE IF EXISTS sessions; >+CREATE TABLE sessions ( >+ `id` varchar(32) NOT NULL, >+ `a_session` mediumtext NOT NULL, >+ PRIMARY KEY (`id`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `sms_providers` >+-- >+ >+DROP TABLE IF EXISTS sms_providers; >+CREATE TABLE `sms_providers` ( >+ `id` int(11) NOT NULL AUTO_INCREMENT, >+ `name` varchar(255) NOT NULL, >+ `domain` varchar(255) NOT NULL, >+ PRIMARY KEY (`id`), >+ UNIQUE KEY `name` (`name`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `borrowers` >+-- >+ >+DROP TABLE IF EXISTS `borrowers`; >+CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members >+ `borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers >+ `cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers >+ `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname) >+ `firstname` text, -- patron/borrower's first name >+ `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs. >+ `othernames` mediumtext, -- any other names associated with the patron/borrower >+ `initials` text, -- initials for your patron/borrower >+ `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address >+ `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address >+ `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address >+ `address2` text, -- the second address line for your patron/borrower's primary address >+ `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address >+ `state` text default NULL, -- the state or province for your patron/borrower's primary address >+ `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address >+ `country` text, -- the country for your patron/borrower's primary address >+ `email` mediumtext, -- the primary email address for your patron/borrower's primary address >+ `phone` text, -- the primary phone number for your patron/borrower's primary address >+ `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address >+ `fax` mediumtext, -- the fax number for your patron/borrower's primary address >+ `emailpro` text, -- the secondary email addres for your patron/borrower's primary address >+ `phonepro` text, -- the secondary phone number for your patron/borrower's primary address >+ `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address >+ `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address >+ `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address >+ `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address >+ `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address >+ `B_state` text default NULL, -- the state for your patron/borrower's alternate address >+ `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address >+ `B_country` text, -- the country for your patron/borrower's alternate address >+ `B_email` text, -- the patron/borrower's alternate email address >+ `B_phone` mediumtext, -- the patron/borrower's alternate phone number >+ `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD) >+ `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch >+ `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category >+ `dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD) >+ `dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD) >+ `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 >+ `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 >+ `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) >+ `debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron >+ `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name >+ `contactfirstname` text, -- used for children to include first name of guarentor >+ `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor >+ `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations >+ `borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client >+ `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor >+ `sex` varchar(1) default NULL, -- patron/borrower's gender >+ `password` varchar(60) default NULL, -- patron/borrower's encrypted password >+ `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions >+ `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in >+ `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client >+ `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address >+ `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library >+ `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library >+ `altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower >+ `altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower >+ `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower >+ `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower >+ `altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower >+ `altcontactstate` text default NULL, -- the state for the alternate contact for the patron/borrower >+ `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower >+ `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower >+ `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower >+ `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) >+ `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber >+ `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history >+ `privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts >+ UNIQUE KEY `cardnumber` (`cardnumber`), >+ PRIMARY KEY `borrowernumber` (`borrowernumber`), >+ KEY `categorycode` (`categorycode`), >+ KEY `branchcode` (`branchcode`), >+ UNIQUE KEY `userid` (`userid`), >+ KEY `guarantorid` (`guarantorid`), >+ KEY `surname_idx` (`surname`(255)), >+ KEY `firstname_idx` (`firstname`(255)), >+ KEY `othernames_idx` (`othernames`(255)), >+ KEY `sms_provider_id` (`sms_provider_id`), >+ CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), >+ CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`), >+ CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ON UPDATE CASCADE ON DELETE SET NULL >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `borrower_attributes` >+-- >+ >+DROP TABLE IF EXISTS `borrower_attributes`; >+CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as extended patron attributes linked to patrons/borrowers >+ `borrowernumber` int(11) NOT NULL, -- foreign key from the borrowers table, defines which patron/borrower has this attribute >+ `code` varchar(10) NOT NULL, -- foreign key from the borrower_attribute_types table, defines which custom field this value was entered for >+ `attribute` varchar(255) default NULL, -- custom patron field value >+ KEY `borrowernumber` (`borrowernumber`), >+ KEY `code_attribute` (`code`, `attribute`), >+ CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) >+ ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`) >+ ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `borrower_debarments` >+-- >+ >+DROP TABLE IF EXISTS `borrower_debarments`; >+CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record >+ borrower_debarment_id int(11) NOT NULL AUTO_INCREMENT, -- unique key for the restriction >+ borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted >+ expiration date DEFAULT NULL, -- expiration date of the restriction >+ `type` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') NOT NULL DEFAULT 'MANUAL', -- type of restriction >+ `comment` text, -- comments about the restriction >+ manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction >+ created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added >+ updated timestamp NULL DEFAULT NULL, -- date the restriction was updated >+ PRIMARY KEY (borrower_debarment_id), >+ KEY borrowernumber (borrowernumber), >+ CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) >+ ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table borrower_sync >+-- >+ >+DROP TABLE IF EXISTS `borrower_sync`; >+CREATE TABLE borrower_sync ( >+ borrowersyncid int(11) NOT NULL AUTO_INCREMENT, -- Primary key, unique identifier >+ borrowernumber int(11) NOT NULL, -- Connects data about synchronisations to a borrower >+ 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 >+ sync tinyint(1) NOT NULL DEFAULT '0', -- A boolean (1/0) for turning syncing off and on for individual borrowers >+ 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. >+ 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. >+ hashed_pin varchar(64) DEFAULT NULL, -- Perhaps specific to The Norwegian national patron database, this column holds a hashed PIN code >+ PRIMARY KEY (borrowersyncid), >+ KEY borrowernumber (borrowernumber), >+ CONSTRAINT borrower_sync_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `issues` >+-- >+ >+DROP TABLE IF EXISTS `issues`; >+CREATE TABLE `issues` ( -- information related to check outs or issues >+ `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table >+ `borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to >+ `itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out >+ `date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss) >+ `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out >+ `returndate` datetime default NULL, -- date the item was returned, will be NULL until moved to old_issues >+ `lastreneweddate` datetime default NULL, -- date the item was last renewed >+ `return` varchar(4) default NULL, >+ `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed >+ `auto_renew` BOOLEAN default FALSE, -- automatic renewal >+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched >+ `issuedate` datetime default NULL, -- date the item was checked out or issued >+ `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag >+ PRIMARY KEY (`issue_id`), >+ UNIQUE KEY `itemnumber` (`itemnumber`), >+ KEY `issuesborridx` (`borrowernumber`), >+ KEY `itemnumber_idx` (`itemnumber`), >+ KEY `branchcode_idx` (`branchcode`), >+ KEY `bordate` (`borrowernumber`,`timestamp`), >+ CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE, >+ CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `old_issues` >+-- >+ >+DROP TABLE IF EXISTS `old_issues`; >+CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned >+ `issue_id` int(11) NOT NULL, -- primary key for issues table >+ `borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to >+ `itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out >+ `date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd) >+ `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out >+ `returndate` datetime default NULL, -- date the item was returned >+ `lastreneweddate` datetime default NULL, -- date the item was last renewed >+ `return` varchar(4) default NULL, >+ `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed >+ `auto_renew` BOOLEAN default FALSE, -- automatic renewal >+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched >+ `issuedate` datetime default NULL, -- date the item was checked out or issued >+ `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag >+ PRIMARY KEY (`issue_id`), >+ KEY `old_issuesborridx` (`borrowernumber`), >+ KEY `old_issuesitemidx` (`itemnumber`), >+ KEY `branchcode_idx` (`branchcode`), >+ KEY `old_bordate` (`borrowernumber`,`timestamp`), >+ CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) >+ ON DELETE SET NULL ON UPDATE SET NULL, >+ CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) >+ ON DELETE SET NULL ON UPDATE SET NULL >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `items_last_borrower` >+-- >+ >+CREATE TABLE IF NOT EXISTS `items_last_borrower` ( >+ `id` int(11) NOT NULL AUTO_INCREMENT, >+ `itemnumber` int(11) NOT NULL, >+ `borrowernumber` int(11) NOT NULL, >+ `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, >+ PRIMARY KEY (`id`), >+ UNIQUE KEY `itemnumber` (`itemnumber`), >+ KEY `borrowernumber` (`borrowernumber`), >+ CONSTRAINT `items_last_borrower_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT `items_last_borrower_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `creator_batches` >+-- >+ >+DROP TABLE IF EXISTS `creator_batches`; >+SET @saved_cs_client = @@character_set_client; >+SET character_set_client = utf8; >+CREATE TABLE `creator_batches` ( >+ `label_id` int(11) NOT NULL AUTO_INCREMENT, >+ `batch_id` int(10) NOT NULL DEFAULT '1', >+ `item_number` int(11) DEFAULT NULL, >+ `borrower_number` int(11) DEFAULT NULL, >+ `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, >+ `branch_code` varchar(10) NOT NULL DEFAULT 'NB', >+ `creator` char(15) NOT NULL DEFAULT 'Labels', >+ PRIMARY KEY (`label_id`), >+ KEY `branch_fk_constraint` (`branch_code`), >+ KEY `item_fk_constraint` (`item_number`), >+ KEY `borrower_fk_constraint` (`borrower_number`), >+ CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE, >+ CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `opac_news` >+-- >+ >+DROP TABLE IF EXISTS `opac_news`; >+CREATE TABLE `opac_news` ( -- data from the news tool >+ `idnew` int(10) unsigned NOT NULL auto_increment, -- unique identifier for the news article >+ `branchcode` varchar(10) default NULL, -- branch code users to create branch specific news, NULL is every branch. >+ `title` varchar(250) NOT NULL default '', -- title of the news article >+ `new` text NOT NULL, -- the body of your news article >+ `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) >+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- pulibcation date and time >+ `expirationdate` date default NULL, -- date the article is set to expire or no longer be visible >+ `number` int(11) default NULL, -- the order in which this article appears in that specific location >+ `borrowernumber` int(11) default NULL, -- The user who created the news article >+ PRIMARY KEY (`idnew`), >+ CONSTRAINT `borrowernumber_fk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, >+ CONSTRAINT opac_news_branchcode_ibfk FOREIGN KEY (branchcode) REFERENCES branches (branchcode) >+ ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `patronimage` >+-- >+ >+DROP TABLE IF EXISTS `patronimage`; >+CREATE TABLE `patronimage` ( -- information related to patron images >+ `borrowernumber` int(11) NOT NULL, -- the borrowernumber of the patron this image is attached to (borrowers.borrowernumber) >+ `mimetype` varchar(15) NOT NULL, -- the format of the image (png, jpg, etc) >+ `imagefile` mediumblob NOT NULL, -- the image >+ PRIMARY KEY (`borrowernumber`), >+ CONSTRAINT `patronimage_fk1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- > -- Table structure for table `reserves` > -- > >@@ -1903,6 +1866,47 @@ CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >+-- Table structure for table `old_reserves` >+-- >+ >+DROP TABLE IF EXISTS `old_reserves`; >+CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled) >+ `reserve_id` int(11) NOT NULL, -- primary key >+ `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for >+ `reservedate` date default NULL, -- the date the hold was places >+ `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on >+ `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at >+ `notificationdate` date default NULL, -- currently unused >+ `reminderdate` date default NULL, -- currently unused >+ `cancellationdate` date default NULL, -- the date this hold was cancelled >+ `reservenotes` mediumtext, -- notes related to this hold >+ `priority` smallint(6) default NULL, -- where in the queue the patron sits >+ `found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed >+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated >+ `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 >+ `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library >+ `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) >+ `lowestPriority` tinyint(1) NOT NULL, -- has this hold been pinned to the lowest priority in the holds queue (1 for yes, 0 for no) >+ `suspend` BOOLEAN NOT NULL DEFAULT 0, -- in this hold suspended (1 for yes, 0 for no) >+ `suspend_until` DATETIME NULL DEFAULT NULL, -- the date this hold is suspended until (NULL for infinitely) >+ `itemtype` VARCHAR(10) NULL DEFAULT NULL, -- If record level hold, the optional itemtype of the item the patron is requesting >+ PRIMARY KEY (`reserve_id`), >+ KEY `old_reserves_borrowernumber` (`borrowernumber`), >+ KEY `old_reserves_biblionumber` (`biblionumber`), >+ KEY `old_reserves_itemnumber` (`itemnumber`), >+ KEY `old_reserves_branchcode` (`branchcode`), >+ KEY `old_reserves_itemtype` (`itemtype`), >+ CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) >+ ON DELETE SET NULL ON UPDATE SET NULL, >+ CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) >+ ON DELETE SET NULL ON UPDATE SET NULL, >+ CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) >+ ON DELETE SET NULL ON UPDATE SET NULL, >+ CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) >+ ON DELETE SET NULL ON UPDATE SET NULL >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- > -- Table structure for table `reviews` > -- > >@@ -1920,157 +1924,6 @@ CREATE TABLE `reviews` ( -- patron opac comments > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `saved_sql` >--- >- >-DROP TABLE IF EXISTS `saved_sql`; >-CREATE TABLE saved_sql ( -- saved sql reports >- `id` int(11) NOT NULL auto_increment, -- unique id and primary key assigned by Koha >- `borrowernumber` int(11) default NULL, -- the staff member who created this report (borrowers.borrowernumber) >- `date_created` datetime default NULL, -- the date this report was created >- `last_modified` datetime default NULL, -- the date this report was last edited >- `savedsql` text, -- the SQL for this report >- `last_run` datetime default NULL, >- `report_name` varchar(255) NOT NULL default '', -- the name of this report >- `type` varchar(255) default NULL, -- always 1 for tabular >- `notes` text, -- the notes or description given to this report >- `cache_expiry` int NOT NULL default 300, >- `public` boolean NOT NULL default FALSE, >- report_area varchar(6) default NULL, >- report_group varchar(80) default NULL, >- report_subgroup varchar(80) default NULL, >- PRIMARY KEY (`id`), >- KEY sql_area_group_idx (report_group, report_subgroup), >- KEY boridx (`borrowernumber`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >- >--- >--- Table structure for `saved_reports` >--- >- >-DROP TABLE IF EXISTS `saved_reports`; >-CREATE TABLE saved_reports ( >- `id` int(11) NOT NULL auto_increment, >- `report_id` int(11) default NULL, >- `report` longtext, >- `date_run` datetime default NULL, >- PRIMARY KEY (`id`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table 'search_field' >--- >- >-DROP TABLE IF EXISTS search_field; >-CREATE TABLE `search_field` ( >- `id` int(11) NOT NULL AUTO_INCREMENT, >- `name` varchar(255) NOT NULL COMMENT 'the name of the field as it will be stored in the search engine', >- `label` varchar(255) NOT NULL COMMENT 'the human readable name of the field, for display', >- `type` ENUM('string', 'date', 'number', 'boolean', 'sum') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine', >- PRIMARY KEY (`id`), >- UNIQUE KEY (`name`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table `search_history` >--- >- >-DROP TABLE IF EXISTS `search_history`; >-CREATE TABLE IF NOT EXISTS `search_history` ( -- patron's opac search history >- `id` int(11) NOT NULL auto_increment, -- search history id >- `userid` int(11) NOT NULL, -- the patron who performed the search (borrowers.borrowernumber) >- `sessionid` varchar(32) NOT NULL, -- a system generated session id >- `query_desc` varchar(255) NOT NULL, -- the search that was performed >- `query_cgi` text NOT NULL, -- the string to append to the search url to rerun the search >- `type` varchar(16) NOT NULL DEFAULT 'biblio', -- search type, must be 'biblio' or 'authority' >- `total` int(11) NOT NULL, -- the total of results found >- `time` timestamp NOT NULL default CURRENT_TIMESTAMP, -- the date and time the search was run >- KEY `userid` (`userid`), >- KEY `sessionid` (`sessionid`), >- PRIMARY KEY (`id`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Opac search history results'; >- >--- >--- Table structure for table 'search_marc_map' >--- >- >-DROP TABLE IF EXISTS search_marc_map; >-CREATE TABLE `search_marc_map` ( >- id int(11) NOT NULL AUTO_INCREMENT, >- index_name ENUM('biblios','authorities') NOT NULL COMMENT 'what storage index this map is for', >- marc_type ENUM('marc21', 'unimarc', 'normarc') NOT NULL COMMENT 'what MARC type this map is for', >- marc_field VARCHAR(255) NOT NULL COMMENT 'the MARC specifier for this field', >- PRIMARY KEY(`id`), >- UNIQUE key(index_name, marc_field, marc_type), >- INDEX (`index_name`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table 'search_marc_to_field' >--- >- >-DROP TABLE IF EXISTS search_marc_to_field; >-CREATE TABLE `search_marc_to_field` ( >- search_marc_map_id int(11) NOT NULL, >- search_field_id int(11) NOT NULL, >- facet boolean DEFAULT FALSE COMMENT 'true if a facet field should be generated for this', >- suggestible boolean DEFAULT FALSE COMMENT 'true if this field can be used to generate suggestions for browse', >- sort boolean DEFAULT NULL COMMENT 'true/false creates special sort handling, null doesn''t', >- PRIMARY KEY(search_marc_map_id, search_field_id), >- FOREIGN KEY(search_marc_map_id) REFERENCES search_marc_map(id) ON DELETE CASCADE ON UPDATE CASCADE, >- FOREIGN KEY(search_field_id) REFERENCES search_field(id) ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table `serial` >--- >- >-DROP TABLE IF EXISTS `serial`; >-CREATE TABLE `serial` ( -- issues related to subscriptions >- `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue >- `biblionumber` varchar(100) NOT NULL default '', -- foreign key for the biblio.biblionumber that this issue is attached to >- `subscriptionid` varchar(100) NOT NULL default '', -- foreign key to the subscription.subscriptionid that this issue is part of >- `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc) >- `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information >- `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information >- `serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information >- `status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions) >- `planneddate` date default NULL, -- date expected >- `notes` text, -- notes >- `publisheddate` date default NULL, -- date published >- publisheddatetext varchar(100) default NULL, -- date published (descriptive) >- `claimdate` date default NULL, -- date claimed >- claims_count int(11) default 0, -- number of claims made related to this issue >- `routingnotes` text, -- notes from the routing list >- PRIMARY KEY (`serialid`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table `sessions` >--- >- >-DROP TABLE IF EXISTS sessions; >-CREATE TABLE sessions ( >- `id` varchar(32) NOT NULL, >- `a_session` mediumtext NOT NULL, >- PRIMARY KEY (`id`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table `sms_providers` >--- >- >-DROP TABLE IF EXISTS sms_providers; >-CREATE TABLE `sms_providers` ( >- `id` int(11) NOT NULL AUTO_INCREMENT, >- `name` varchar(255) NOT NULL, >- `domain` varchar(255) NOT NULL, >- PRIMARY KEY (`id`), >- UNIQUE KEY `name` (`name`) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- > -- Table structure for table `special_holidays` > -- > >@@ -2252,52 +2105,6 @@ CREATE TABLE `subscriptionroutinglist` ( -- information related to the routing l > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `suggestions` >--- >- >-DROP TABLE IF EXISTS `suggestions`; >-CREATE TABLE `suggestions` ( -- purchase suggestions >- `suggestionid` int(8) NOT NULL auto_increment, -- unique identifier assigned automatically by Koha >- `suggestedby` int(11) NOT NULL default 0, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table >- `suggesteddate` date NOT NULL, -- date the suggestion was submitted >- `managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table >- `manageddate` date default NULL, -- date the suggestion was updated >- acceptedby INT(11) default NULL, -- borrowernumber for the librarian who accepted the suggestion, foreign key linking to the borrowers table >- accepteddate date default NULL, -- date the suggestion was marked as accepted >- rejectedby INT(11) default NULL, -- borrowernumber for the librarian who rejected the suggestion, foreign key linking to the borrowers table >- rejecteddate date default NULL, -- date the suggestion was marked as rejected >- `STATUS` varchar(10) NOT NULL default '', -- suggestion status (ASKED, CHECKED, ACCEPTED, or REJECTED) >- `note` mediumtext, -- note entered on the suggestion >- `author` varchar(80) default NULL, -- author of the suggested item >- `title` varchar(255) default NULL, -- title of the suggested item >- `copyrightdate` smallint(6) default NULL, -- copyright date of the suggested item >- `publishercode` varchar(255) default NULL, -- publisher of the suggested item >- `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the suggestion was updated >- `volumedesc` varchar(255) default NULL, >- `publicationyear` smallint(6) default 0, >- `place` varchar(255) default NULL, -- publication place of the suggested item >- `isbn` varchar(30) default NULL, -- isbn of the suggested item >- `biblionumber` int(11) default NULL, -- foreign key linking the suggestion to the biblio table after the suggestion has been ordered >- `reason` text, -- reason for accepting or rejecting the suggestion >- `patronreason` text, -- reason for making the suggestion >- budgetid INT(11), -- foreign key linking the suggested budget to the aqbudgets table >- branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table >- collectiontitle text default NULL, -- collection name for the suggested item >- itemtype VARCHAR(30) default NULL, -- suggested item type >- quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased >- currency VARCHAR(10) default NULL, -- suggested currency for the suggested price >- price DECIMAL(28,6) default NULL, -- suggested price >- total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency) >- PRIMARY KEY (`suggestionid`), >- KEY `suggestedby` (`suggestedby`), >- KEY `managedby` (`managedby`), >- KEY `status` (`STATUS`), >- KEY `biblionumber` (`biblionumber`), >- KEY `branchcode` (`branchcode`), >- CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- > -- Table structure for table `systempreferences` > -- > >@@ -2637,6 +2444,16 @@ CREATE TABLE `tmp_holdsqueue` ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >+-- Table structure for table `message_transport_types` >+-- >+ >+DROP TABLE IF EXISTS `message_transport_types`; >+CREATE TABLE `message_transport_types` ( >+ `message_transport_type` varchar(20) NOT NULL, >+ PRIMARY KEY (`message_transport_type`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- > -- Table structure for table `message_queue` > -- > >@@ -2662,13 +2479,22 @@ CREATE TABLE `message_queue` ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `message_transport_types` >+-- Table structure for table `letter` > -- > >-DROP TABLE IF EXISTS `message_transport_types`; >-CREATE TABLE `message_transport_types` ( >- `message_transport_type` varchar(20) NOT NULL, >- PRIMARY KEY (`message_transport_type`) >+DROP TABLE IF EXISTS `letter`; >+CREATE TABLE `letter` ( -- table for all notice templates in Koha >+ `module` varchar(20) NOT NULL default '', -- Koha module that triggers this notice or slip >+ `code` varchar(20) NOT NULL default '', -- unique identifier for this notice or slip >+ `branchcode` varchar(10) NOT NULL default '', -- the branch this notice or slip is used at (branches.branchcode) >+ `name` varchar(100) NOT NULL default '', -- plain text name for this notice or slip >+ `is_html` tinyint(1) default 0, -- does this notice or slip use HTML (1 for yes, 0 for no) >+ `title` varchar(200) NOT NULL default '', -- subject line of the notice >+ `content` text, -- body text for the notice or slip >+ `message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice >+ PRIMARY KEY (`module`,`code`, `branchcode`, `message_transport_type`), >+ CONSTRAINT `message_transport_type_fk` FOREIGN KEY (`message_transport_type`) >+ REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >@@ -2898,70 +2724,6 @@ CREATE TABLE `alert` ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table `aqbasketgroups` >--- >- >-DROP TABLE IF EXISTS `aqbasketgroups`; >-CREATE TABLE `aqbasketgroups` ( >- `id` int(11) NOT NULL auto_increment, >- `name` varchar(50) default NULL, >- `closed` tinyint(1) default NULL, >- `booksellerid` int(11) NOT NULL, >- `deliveryplace` varchar(10) default NULL, >- `freedeliveryplace` text default NULL, >- `deliverycomment` varchar(255) default NULL, >- `billingplace` varchar(10) default NULL, >- PRIMARY KEY (`id`), >- KEY `booksellerid` (`booksellerid`), >- CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table `aqbasket` >--- >- >-DROP TABLE IF EXISTS `aqbasket`; >-CREATE TABLE `aqbasket` ( -- stores data about baskets in acquisitions >- `basketno` int(11) NOT NULL auto_increment, -- primary key, Koha defined number >- `basketname` varchar(50) default NULL, -- name given to the basket at creation >- `note` mediumtext, -- the internal note added at basket creation >- `booksellernote` mediumtext, -- the vendor note added at basket creation >- `contractnumber` int(11), -- links this basket to the aqcontract table (aqcontract.contractnumber) >- `creationdate` date default NULL, -- the date the basket was created >- `closedate` date default NULL, -- the date the basket was closed >- `booksellerid` int(11) NOT NULL default 1, -- the Koha assigned ID for the vendor (aqbooksellers.id) >- `authorisedby` varchar(10) default NULL, -- the borrowernumber of the person who created the basket >- `booksellerinvoicenumber` mediumtext, -- appears to always be NULL >- `basketgroupid` int(11), -- links this basket to its group (aqbasketgroups.id) >- `deliveryplace` varchar(10) default NULL, -- basket delivery place >- `billingplace` varchar(10) default NULL, -- basket billing place >- branch varchar(10) default NULL, -- basket branch >- is_standing TINYINT(1) NOT NULL DEFAULT 0, -- orders in this basket are standing >- PRIMARY KEY (`basketno`), >- KEY `booksellerid` (`booksellerid`), >- KEY `basketgroupid` (`basketgroupid`), >- KEY `contractnumber` (`contractnumber`), >- KEY `authorisedby` (`authorisedby`), >- CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE, >- CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`), >- CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE, >- CONSTRAINT aqbasket_ibfk_4 FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE SET NULL >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table aqbasketusers >--- >- >-DROP TABLE IF EXISTS aqbasketusers; >-CREATE TABLE aqbasketusers ( >- basketno int(11) NOT NULL, >- borrowernumber int(11) NOT NULL, >- PRIMARY KEY (basketno,borrowernumber), >- CONSTRAINT aqbasketusers_ibfk_1 FOREIGN KEY (basketno) REFERENCES aqbasket (basketno) ON UPDATE CASCADE ON DELETE CASCADE, >- CONSTRAINT aqbasketusers_ibfk_2 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON UPDATE CASCADE ON DELETE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- > -- Table structure for table `aqbooksellers` > -- > >@@ -3002,6 +2764,25 @@ CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquis > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >+-- Table structure for table `aqbasketgroups` >+-- >+ >+DROP TABLE IF EXISTS `aqbasketgroups`; >+CREATE TABLE `aqbasketgroups` ( >+ `id` int(11) NOT NULL auto_increment, >+ `name` varchar(50) default NULL, >+ `closed` tinyint(1) default NULL, >+ `booksellerid` int(11) NOT NULL, >+ `deliveryplace` varchar(10) default NULL, >+ `freedeliveryplace` text default NULL, >+ `deliverycomment` varchar(255) default NULL, >+ `billingplace` varchar(10) default NULL, >+ PRIMARY KEY (`id`), >+ KEY `booksellerid` (`booksellerid`), >+ CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- > -- Table structure for table `aqbudgets` > -- > >@@ -3104,26 +2885,195 @@ CREATE TABLE aqcontacts ( > serialsprimary BOOLEAN NOT NULL DEFAULT 0, -- is this the primary contact for serials messages > booksellerid int(11) not NULL, > PRIMARY KEY (id), >- CONSTRAINT booksellerid_aqcontacts_fk FOREIGN KEY (booksellerid) >- REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; >+ CONSTRAINT booksellerid_aqcontacts_fk FOREIGN KEY (booksellerid) >+ REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; >+ >+-- >+-- Table structure for table 'aqcontract' >+-- >+ >+DROP TABLE IF EXISTS `aqcontract`; >+CREATE TABLE `aqcontract` ( >+ `contractnumber` int(11) NOT NULL auto_increment, >+ `contractstartdate` date default NULL, >+ `contractenddate` date default NULL, >+ `contractname` varchar(50) default NULL, >+ `contractdescription` mediumtext, >+ `booksellerid` int(11) not NULL, >+ PRIMARY KEY (`contractnumber`), >+ CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`) >+ REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; >+ >+-- >+-- Table structure for table `aqbasket` >+-- >+ >+DROP TABLE IF EXISTS `aqbasket`; >+CREATE TABLE `aqbasket` ( -- stores data about baskets in acquisitions >+ `basketno` int(11) NOT NULL auto_increment, -- primary key, Koha defined number >+ `basketname` varchar(50) default NULL, -- name given to the basket at creation >+ `note` mediumtext, -- the internal note added at basket creation >+ `booksellernote` mediumtext, -- the vendor note added at basket creation >+ `contractnumber` int(11), -- links this basket to the aqcontract table (aqcontract.contractnumber) >+ `creationdate` date default NULL, -- the date the basket was created >+ `closedate` date default NULL, -- the date the basket was closed >+ `booksellerid` int(11) NOT NULL default 1, -- the Koha assigned ID for the vendor (aqbooksellers.id) >+ `authorisedby` varchar(10) default NULL, -- the borrowernumber of the person who created the basket >+ `booksellerinvoicenumber` mediumtext, -- appears to always be NULL >+ `basketgroupid` int(11), -- links this basket to its group (aqbasketgroups.id) >+ `deliveryplace` varchar(10) default NULL, -- basket delivery place >+ `billingplace` varchar(10) default NULL, -- basket billing place >+ branch varchar(10) default NULL, -- basket branch >+ is_standing TINYINT(1) NOT NULL DEFAULT 0, -- orders in this basket are standing >+ PRIMARY KEY (`basketno`), >+ KEY `booksellerid` (`booksellerid`), >+ KEY `basketgroupid` (`basketgroupid`), >+ KEY `contractnumber` (`contractnumber`), >+ KEY `authorisedby` (`authorisedby`), >+ CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE, >+ CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`), >+ CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE, >+ CONSTRAINT aqbasket_ibfk_4 FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE SET NULL >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table aqbasketusers >+-- >+ >+DROP TABLE IF EXISTS aqbasketusers; >+CREATE TABLE aqbasketusers ( >+ basketno int(11) NOT NULL, >+ borrowernumber int(11) NOT NULL, >+ PRIMARY KEY (basketno,borrowernumber), >+ CONSTRAINT aqbasketusers_ibfk_1 FOREIGN KEY (basketno) REFERENCES aqbasket (basketno) ON UPDATE CASCADE ON DELETE CASCADE, >+ CONSTRAINT aqbasketusers_ibfk_2 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON UPDATE CASCADE ON DELETE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `suggestions` >+-- >+ >+DROP TABLE IF EXISTS `suggestions`; >+CREATE TABLE `suggestions` ( -- purchase suggestions >+ `suggestionid` int(8) NOT NULL auto_increment, -- unique identifier assigned automatically by Koha >+ `suggestedby` int(11) NOT NULL default 0, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table >+ `suggesteddate` date NOT NULL, -- date the suggestion was submitted >+ `managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table >+ `manageddate` date default NULL, -- date the suggestion was updated >+ acceptedby INT(11) default NULL, -- borrowernumber for the librarian who accepted the suggestion, foreign key linking to the borrowers table >+ accepteddate date default NULL, -- date the suggestion was marked as accepted >+ rejectedby INT(11) default NULL, -- borrowernumber for the librarian who rejected the suggestion, foreign key linking to the borrowers table >+ rejecteddate date default NULL, -- date the suggestion was marked as rejected >+ `STATUS` varchar(10) NOT NULL default '', -- suggestion status (ASKED, CHECKED, ACCEPTED, or REJECTED) >+ `note` mediumtext, -- note entered on the suggestion >+ `author` varchar(80) default NULL, -- author of the suggested item >+ `title` varchar(255) default NULL, -- title of the suggested item >+ `copyrightdate` smallint(6) default NULL, -- copyright date of the suggested item >+ `publishercode` varchar(255) default NULL, -- publisher of the suggested item >+ `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the suggestion was updated >+ `volumedesc` varchar(255) default NULL, >+ `publicationyear` smallint(6) default 0, >+ `place` varchar(255) default NULL, -- publication place of the suggested item >+ `isbn` varchar(30) default NULL, -- isbn of the suggested item >+ `biblionumber` int(11) default NULL, -- foreign key linking the suggestion to the biblio table after the suggestion has been ordered >+ `reason` text, -- reason for accepting or rejecting the suggestion >+ `patronreason` text, -- reason for making the suggestion >+ budgetid INT(11), -- foreign key linking the suggested budget to the aqbudgets table >+ branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table >+ collectiontitle text default NULL, -- collection name for the suggested item >+ itemtype VARCHAR(30) default NULL, -- suggested item type >+ quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased >+ currency VARCHAR(10) default NULL, -- suggested currency for the suggested price >+ price DECIMAL(28,6) default NULL, -- suggested price >+ total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency) >+ PRIMARY KEY (`suggestionid`), >+ KEY `suggestedby` (`suggestedby`), >+ KEY `managedby` (`managedby`), >+ KEY `status` (`STATUS`), >+ KEY `biblionumber` (`biblionumber`), >+ KEY `branchcode` (`branchcode`), >+ CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table vendor_edi_accounts >+-- >+ >+DROP TABLE IF EXISTS vendor_edi_accounts; >+CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( >+ id INT(11) NOT NULL auto_increment, >+ description TEXT NOT NULL, >+ host VARCHAR(40), >+ username VARCHAR(40), >+ password VARCHAR(40), >+ last_activity DATE, >+ vendor_id INT(11) REFERENCES aqbooksellers( id ), >+ download_directory TEXT, >+ upload_directory TEXT, >+ san VARCHAR(20), >+ id_code_qualifier VARCHAR(3) default '14', >+ transport VARCHAR(6) default 'FTP', >+ quotes_enabled TINYINT(1) not null default 0, >+ invoices_enabled TINYINT(1) not null default 0, >+ orders_enabled TINYINT(1) not null default 0, >+ responses_enabled TINYINT(1) not null default 0, >+ auto_orders TINYINT(1) not null default 0, >+ shipment_budget INTEGER(11) REFERENCES aqbudgets( budget_id ), >+ plugin varchar(256) NOT NULL DEFAULT "", >+ PRIMARY KEY (id), >+ KEY vendorid (vendor_id), >+ KEY shipmentbudget (shipment_budget), >+ CONSTRAINT vfk_vendor_id FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ), >+ CONSTRAINT vfk_shipment_budget FOREIGN KEY ( shipment_budget ) REFERENCES aqbudgets ( budget_id ) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table edifact_messages >+-- >+ >+DROP TABLE IF EXISTS edifact_messages; >+CREATE TABLE IF NOT EXISTS edifact_messages ( >+ id INT(11) NOT NULL auto_increment, >+ message_type VARCHAR(10) NOT NULL, >+ transfer_date DATE, >+ vendor_id INT(11) REFERENCES aqbooksellers( id ), >+ edi_acct INTEGER REFERENCES vendor_edi_accounts( id ), >+ status TEXT, >+ basketno INT(11) REFERENCES aqbasket( basketno), >+ raw_msg MEDIUMTEXT, >+ filename TEXT, >+ deleted BOOLEAN NOT NULL DEFAULT 0, >+ PRIMARY KEY (id), >+ KEY vendorid ( vendor_id), >+ KEY ediacct (edi_acct), >+ KEY basketno ( basketno), >+ CONSTRAINT emfk_vendor FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ) ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT emfk_edi_acct FOREIGN KEY ( edi_acct ) REFERENCES vendor_edi_accounts ( id ) ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT emfk_basketno FOREIGN KEY ( basketno ) REFERENCES aqbasket ( basketno ) ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table 'aqcontract' >+-- Table structure for table aqinvoices > -- > >-DROP TABLE IF EXISTS `aqcontract`; >-CREATE TABLE `aqcontract` ( >- `contractnumber` int(11) NOT NULL auto_increment, >- `contractstartdate` date default NULL, >- `contractenddate` date default NULL, >- `contractname` varchar(50) default NULL, >- `contractdescription` mediumtext, >- `booksellerid` int(11) not NULL, >- PRIMARY KEY (`contractnumber`), >- CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`) >- REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; >+DROP TABLE IF EXISTS aqinvoices; >+CREATE TABLE aqinvoices ( >+ invoiceid int(11) NOT NULL AUTO_INCREMENT, -- ID of the invoice, primary key >+ invoicenumber mediumtext NOT NULL, -- Name of invoice >+ booksellerid int(11) NOT NULL, -- foreign key to aqbooksellers >+ shipmentdate date default NULL, -- date of shipment >+ billingdate date default NULL, -- date of billing >+ closedate date default NULL, -- invoice close date, NULL means the invoice is open >+ shipmentcost decimal(28,6) default NULL, -- shipment cost >+ shipmentcost_budgetid int(11) default NULL, -- foreign key to aqbudgets, link the shipment cost to a budget >+ message_id int(11) default NULL, -- foreign key to edifact invoice message >+ PRIMARY KEY (invoiceid), >+ CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT edifact_msg_fk FOREIGN KEY ( message_id ) REFERENCES edifact_messages ( id ) ON DELETE SET NULL, >+ CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- > -- Table structure for table `aqorders` >@@ -3210,7 +3160,6 @@ CREATE TABLE `aqorders_items` ( -- information on items entered in the acquisiti > CONSTRAINT aqorders_items_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > >- > -- > -- Table structure for table aqorders_transfers > -- >@@ -3227,85 +3176,6 @@ CREATE TABLE aqorders_transfers ( > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > > -- >--- Table structure for table vendor_edi_accounts >--- >- >-DROP TABLE IF EXISTS vendor_edi_accounts; >-CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( >- id INT(11) NOT NULL auto_increment, >- description TEXT NOT NULL, >- host VARCHAR(40), >- username VARCHAR(40), >- password VARCHAR(40), >- last_activity DATE, >- vendor_id INT(11) REFERENCES aqbooksellers( id ), >- download_directory TEXT, >- upload_directory TEXT, >- san VARCHAR(20), >- id_code_qualifier VARCHAR(3) default '14', >- transport VARCHAR(6) default 'FTP', >- quotes_enabled TINYINT(1) not null default 0, >- invoices_enabled TINYINT(1) not null default 0, >- orders_enabled TINYINT(1) not null default 0, >- responses_enabled TINYINT(1) not null default 0, >- auto_orders TINYINT(1) not null default 0, >- shipment_budget INTEGER(11) REFERENCES aqbudgets( budget_id ), >- plugin varchar(256) NOT NULL DEFAULT "", >- PRIMARY KEY (id), >- KEY vendorid (vendor_id), >- KEY shipmentbudget (shipment_budget), >- CONSTRAINT vfk_vendor_id FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ), >- CONSTRAINT vfk_shipment_budget FOREIGN KEY ( shipment_budget ) REFERENCES aqbudgets ( budget_id ) >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table edifact_messages >--- >- >-DROP TABLE IF EXISTS edifact_messages; >-CREATE TABLE IF NOT EXISTS edifact_messages ( >- id INT(11) NOT NULL auto_increment, >- message_type VARCHAR(10) NOT NULL, >- transfer_date DATE, >- vendor_id INT(11) REFERENCES aqbooksellers( id ), >- edi_acct INTEGER REFERENCES vendor_edi_accounts( id ), >- status TEXT, >- basketno INT(11) REFERENCES aqbasket( basketno), >- raw_msg MEDIUMTEXT, >- filename TEXT, >- deleted BOOLEAN NOT NULL DEFAULT 0, >- PRIMARY KEY (id), >- KEY vendorid ( vendor_id), >- KEY ediacct (edi_acct), >- KEY basketno ( basketno), >- CONSTRAINT emfk_vendor FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ) ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT emfk_edi_acct FOREIGN KEY ( edi_acct ) REFERENCES vendor_edi_accounts ( id ) ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT emfk_basketno FOREIGN KEY ( basketno ) REFERENCES aqbasket ( basketno ) ON DELETE CASCADE ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >--- >--- Table structure for table aqinvoices >--- >- >-DROP TABLE IF EXISTS aqinvoices; >-CREATE TABLE aqinvoices ( >- invoiceid int(11) NOT NULL AUTO_INCREMENT, -- ID of the invoice, primary key >- invoicenumber mediumtext NOT NULL, -- Name of invoice >- booksellerid int(11) NOT NULL, -- foreign key to aqbooksellers >- shipmentdate date default NULL, -- date of shipment >- billingdate date default NULL, -- date of billing >- closedate date default NULL, -- invoice close date, NULL means the invoice is open >- shipmentcost decimal(28,6) default NULL, -- shipment cost >- shipmentcost_budgetid int(11) default NULL, -- foreign key to aqbudgets, link the shipment cost to a budget >- message_id int(11) default NULL, -- foreign key to edifact invoice message >- PRIMARY KEY (invoiceid), >- CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE, >- CONSTRAINT edifact_msg_fk FOREIGN KEY ( message_id ) REFERENCES edifact_messages ( id ) ON DELETE SET NULL, >- CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE >-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >- >- >--- > -- Table structure for table `fieldmapping` > -- > >@@ -3776,6 +3646,132 @@ CREATE TABLE IF NOT EXISTS edifact_ean ( > CONSTRAINT efk_branchcode FOREIGN KEY ( branchcode ) REFERENCES branches ( branchcode ) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > >+-- >+-- Table structure for table `courses` >+-- >+ >+-- The courses table stores the courses created for the >+-- course reserves feature. >+ >+DROP TABLE IF EXISTS courses; >+CREATE TABLE `courses` ( >+ `course_id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the course >+ `department` varchar(80) DEFAULT NULL, -- the authorised value for the DEPARTMENT >+ `course_number` varchar(255) DEFAULT NULL, -- the "course number" assigned to a course >+ `section` varchar(255) DEFAULT NULL, -- the 'section' of a course >+ `course_name` varchar(255) DEFAULT NULL, -- the name of the course >+ `term` varchar(80) DEFAULT NULL, -- the authorised value for the TERM >+ `staff_note` mediumtext, -- the text of the staff only note >+ `public_note` mediumtext, -- the text of the public / opac note >+ `students_count` varchar(20) DEFAULT NULL, -- how many students will be taking this course/section >+ `enabled` enum('yes','no') NOT NULL DEFAULT 'yes', -- determines whether the course is active >+ `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, >+ PRIMARY KEY (`course_id`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Table structure for table `course_instructors` >+-- >+ >+-- The course instructors table links Koha borrowers to the >+-- courses they are teaching. Many instructors can teach many >+-- courses. course_instructors is just a many-to-many join table. >+ >+DROP TABLE IF EXISTS course_instructors; >+CREATE TABLE `course_instructors` ( >+ `course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id >+ `borrowernumber` int(11) NOT NULL, -- foreign key to link to borrowers.borrowernumber for instructor information >+ PRIMARY KEY (`course_id`,`borrowernumber`), >+ KEY `borrowernumber` (`borrowernumber`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Constraints for table `course_instructors` >+-- >+ALTER TABLE `course_instructors` >+ ADD CONSTRAINT `course_instructors_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`), >+ ADD CONSTRAINT `course_instructors_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE; >+ >+-- >+-- Table structure for table `course_items` >+-- >+ >+-- If an item is placed on course reserve for one or more courses >+-- it will have an entry in this table. No matter how many courses an item >+-- is part of, it will only have one row in this table. >+ >+DROP TABLE IF EXISTS course_items; >+CREATE TABLE `course_items` ( >+ `ci_id` int(11) NOT NULL AUTO_INCREMENT, -- course item id >+ `itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve >+ `itype` varchar(10) DEFAULT NULL, -- new itemtype for the item to have while on reserve (optional) >+ `ccode` varchar(10) DEFAULT NULL, -- new category code for the item to have while on reserve (optional) >+ `holdingbranch` varchar(10) DEFAULT NULL, -- new holding branch for the item to have while on reserve (optional) >+ `location` varchar(80) DEFAULT NULL, -- new shelving location for the item to have while on reseve (optional) >+ `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' >+ `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, >+ PRIMARY KEY (`ci_id`), >+ UNIQUE KEY `itemnumber` (`itemnumber`), >+ KEY `holdingbranch` (`holdingbranch`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Constraints for table `course_items` >+-- >+ALTER TABLE `course_items` >+ ADD CONSTRAINT `course_items_ibfk_2` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, >+ ADD CONSTRAINT `course_items_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE; >+ >+-- >+-- Table structure for table `course_reserves` >+-- >+ >+-- This table connects an item placed on course reserve to a course it is on reserve for. >+-- There will be a row in this table for each course an item is on reserve for. >+ >+DROP TABLE IF EXISTS course_reserves; >+CREATE TABLE `course_reserves` ( >+ `cr_id` int(11) NOT NULL AUTO_INCREMENT, >+ `course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id >+ `ci_id` int(11) NOT NULL, -- foreign key to link to courses_items.ci_id >+ `staff_note` mediumtext, -- staff only note >+ `public_note` mediumtext, -- public, OPAC visible note >+ `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, >+ PRIMARY KEY (`cr_id`), >+ UNIQUE KEY `pseudo_key` (`course_id`,`ci_id`), >+ KEY `course_id` (`course_id`) >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ >+-- >+-- Constraints for table `course_reserves` >+-- >+ALTER TABLE `course_reserves` >+ ADD CONSTRAINT `course_reserves_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`), >+ ADD CONSTRAINT `course_reserves_ibfk_2` FOREIGN KEY (`ci_id`) REFERENCES `course_items` (`ci_id`) ON DELETE CASCADE ON UPDATE CASCADE; >+ >+-- >+-- Table structure for table `hold_fill_targets` >+-- >+ >+DROP TABLE IF EXISTS `hold_fill_targets`; >+CREATE TABLE `hold_fill_targets` ( >+ `borrowernumber` int(11) NOT NULL, >+ `biblionumber` int(11) NOT NULL, >+ `itemnumber` int(11) NOT NULL, >+ `source_branchcode` varchar(10) default NULL, >+ `item_level_request` tinyint(4) NOT NULL default 0, >+ PRIMARY KEY `itemnumber` (`itemnumber`), >+ KEY `bib_branch` (`biblionumber`, `source_branchcode`), >+ CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`) >+ REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`) >+ REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`) >+ REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, >+ CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`) >+ REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE >+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; >+ > /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; > /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; > /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; >-- >2.8.1
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 13669
:
51596
|
51597
|
51598
|
51599
|
51600
|
51601
|
51607
|
51608
|
51610
|
51611
|
51612
|
51671
|
51672
|
51680
|
51681
|
51682
|
51683
|
51684
|
51685
|
51736
|
51738
|
51746