PRIMARY KEY (`biblioitemnumber`),
KEY `bibinoidx` (`biblioitemnumber`),
KEY `bibnoidx` (`biblionumber`),
+ KEY `itemtype_idx` (`itemtype`),
KEY `isbn` (`isbn`),
KEY `issn` (`issn`),
KEY `publishercode` (`publishercode`),
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
--
--- Table structure for table `borrower_branch_circ_rules`
+-- 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,
+ `department` varchar(20) DEFAULT NULL, -- Stores the authorised value DEPT
+ `course_number` varchar(255) DEFAULT NULL, -- An arbitrary field meant to store the "course number" assigned to a course
+ `section` varchar(255) DEFAULT NULL, -- Also arbitrary, but for the 'section' of a course.
+ `course_name` varchar(255) DEFAULT NULL,
+ `term` varchar(20) DEFAULT NULL, -- Stores the authorised value TERM
+ `staff_note` mediumtext,
+ `public_note` mediumtext,
+ `students_count` varchar(20) DEFAULT NULL, -- Meant to be just an estimate of 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;
+
+--
+-- 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,
+ `borrowernumber` int(11) NOT NULL,
+ PRIMARY KEY (`course_id`,`borrowernumber`),
+ KEY `borrowernumber` (`borrowernumber`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- 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,
+ `itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve
+ `itype` varchar(10) DEFAULT NULL, -- an optional new itemtype for the item to have while on reserve
+ `ccode` varchar(10) DEFAULT NULL, -- an optional new category code for the item to have while on reserve
+ `holdingbranch` varchar(10) DEFAULT NULL, -- an optional new holding branch for the item to have while on reserve
+ `location` varchar(80) DEFAULT NULL, -- an optional new shelving location for the item to have while on reseve
+ `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;
+
+--
+-- 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 the courses table
+ `ci_id` int(11) NOT NULL, -- Foreign key to the course_items table
+ `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;
+
+--
+-- Constraints for table `course_reserves`
+--
+ALTER TABLE `course_reserves`
+ ADD CONSTRAINT `course_reserves_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`);
+
+--
+-- Table structure for table `branch_borrower_circ_rules`
--
DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
PRIMARY KEY (`biblioitemnumber`),
KEY `bibinoidx` (`biblioitemnumber`),
KEY `bibnoidx` (`biblionumber`),
+ KEY `itemtype_idx` (`itemtype`),
KEY `isbn` (`isbn`),
KEY `publishercode` (`publishercode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
KEY `delitembinoidx` (`biblioitemnumber`),
KEY `delitembibnoidx` (`biblionumber`),
KEY `delhomebranch` (`homebranch`),
- KEY `delholdingbranch` (`holdingbranch`)
+ KEY `delholdingbranch` (`holdingbranch`),
+ KEY `itype_idx` (`itype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
`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
KEY `issuesborridx` (`borrowernumber`),
+ KEY `itemnumber_idx` (`itemnumber`),
+ KEY `branchcode_idx` (`branchcode`),
+ KEY `issuingbranch_idx` (`issuingbranch`),
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
`hardduedate` date default NULL, -- hard due date
`hardduedatecompare` tinyint NOT NULL default "0", -- type of hard due date (1 = after, 0 = on, -1 = before)
`renewalsallowed` smallint(6) NOT NULL default "0", -- how many renewals are allowed
- `renewalperiod` int(4) default NULL -- renewal period in the unit set in issuingrules.lengthunit
+ `renewalperiod` int(4) default NULL, -- renewal period in the unit set in issuingrules.lengthunit
`reservesallowed` smallint(6) NOT NULL default "0", -- how many holds are allowed
`branchcode` varchar(10) NOT NULL default '', -- the branch this rule is for (branches.branchcode)
overduefinescap decimal default NULL, -- the maximum amount of an overdue fine
KEY `itemcallnumber` (`itemcallnumber`),
KEY `items_location` (`location`),
KEY `items_ccode` (`ccode`),
+ KEY `itype_idx` (`itype`),
CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
`issuedate` datetime default NULL, -- date the item was checked out or issued
KEY `old_issuesborridx` (`borrowernumber`),
KEY `old_issuesitemidx` (`itemnumber`),
+ KEY `branchcode_idx` (`branchcode`),
+ KEY `issuingbranch_idx` (`issuingbranch`),
KEY `old_bordate` (`borrowernumber`,`timestamp`),
CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
ON DELETE SET NULL ON UPDATE SET NULL,
`planneddate` date default NULL,
`notes` text,
`publisheddate` date default NULL,
- `itemnumber` text default NULL,
`claimdate` date default NULL,
`routingnotes` text,
PRIMARY KEY (`serialid`)
`borrowernumber` int(11) default NULL, -- foreign key from the borrowers table, links transaction to a specific borrower
`associatedborrower` int(11) default NULL,
`ccode` varchar(10) default NULL, -- foreign key from the items table, links transaction to a specific collection code
- KEY `timeidx` (`datetime`)
+ KEY `timeidx` (`datetime`),
+ KEY `branch_idx` (`branch`),
+ KEY `proccode_idx` (`proccode`),
+ KEY `type_idx` (`type`),
+ KEY `usercode_idx` (`usercode`),
+ KEY `itemnumber_idx` (`itemnumber`),
+ KEY `itemtype_idx` (`itemtype`),
+ KEY `borrowernumber_idx` (`borrowernumber`),
+ KEY `associatedborrower_idx` (`associatedborrower`),
+ KEY `ccode_idx` (`ccode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
`word` varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
---
--- Table structure for table subscription_frequencies
---
-
-DROP TABLE IF EXISTS subscription_frequencies;
-CREATE TABLE subscription_frequencies (
- id INTEGER NOT NULL AUTO_INCREMENT,
- description TEXT NOT NULL,
- displayorder INT DEFAULT NULL,
- unit ENUM('day','week','month','year') DEFAULT NULL,
- unitsperissue INTEGER NOT NULL DEFAULT '1',
- issuesperunit INTEGER NOT NULL DEFAULT '1',
- PRIMARY KEY (id)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table subscription_numberpatterns
---
-
-DROP TABLE IF EXISTS subscription_numberpatterns;
-CREATE TABLE subscription_numberpatterns (
- id INTEGER NOT NULL AUTO_INCREMENT,
- label VARCHAR(255) NOT NULL,
- displayorder INTEGER DEFAULT NULL,
- description TEXT NOT NULL,
- numberingmethod VARCHAR(255) NOT NULL,
- label1 VARCHAR(255) DEFAULT NULL,
- add1 INTEGER DEFAULT NULL,
- every1 INTEGER DEFAULT NULL,
- whenmorethan1 INTEGER DEFAULT NULL,
- setto1 INTEGER DEFAULT NULL,
- numbering1 VARCHAR(255) DEFAULT NULL,
- label2 VARCHAR(255) DEFAULT NULL,
- add2 INTEGER DEFAULT NULL,
- every2 INTEGER DEFAULT NULL,
- whenmorethan2 INTEGER DEFAULT NULL,
- setto2 INTEGER DEFAULT NULL,
- numbering2 VARCHAR(255) DEFAULT NULL,
- label3 VARCHAR(255) DEFAULT NULL,
- add3 INTEGER DEFAULT NULL,
- every3 INTEGER DEFAULT NULL,
- whenmorethan3 INTEGER DEFAULT NULL,
- setto3 INTEGER DEFAULT NULL,
- numbering3 VARCHAR(255) DEFAULT NULL,
- PRIMARY KEY (id)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
--
-- Table structure for table `subscription`
--
`monthlength` int(11) default 0,
`numberlength` int(11) default 0,
`periodicity` tinyint(4) default 0,
- countissuesperunit INTEGER NOT NULL DEFAULT 1,
+ `dow` varchar(100) default '',
+ `numberingmethod` varchar(100) default '',
`notes` mediumtext,
`status` varchar(100) NOT NULL default '',
+ `add1` int(11) default 0,
+ `every1` int(11) default 0,
+ `whenmorethan1` int(11) default 0,
+ `setto1` int(11) default NULL,
`lastvalue1` int(11) default NULL,
- `innerloop1` int(11) default 0,
+ `add2` int(11) default 0,
+ `every2` int(11) default 0,
+ `whenmorethan2` int(11) default 0,
+ `setto2` int(11) default NULL,
`lastvalue2` int(11) default NULL,
+ `add3` int(11) default 0,
+ `every3` int(11) default 0,
+ `innerloop1` int(11) default 0,
`innerloop2` int(11) default 0,
- `lastvalue3` int(11) default NULL,
`innerloop3` int(11) default 0,
+ `whenmorethan3` int(11) default 0,
+ `setto3` int(11) default NULL,
+ `lastvalue3` int(11) default NULL,
+ `issuesatonce` tinyint(3) NOT NULL default 1,
`firstacquidate` date default NULL,
`manualhistory` tinyint(1) NOT NULL default 0,
`irregularity` text,
- skip_serialseq BOOLEAN NOT NULL DEFAULT 0,
`letter` varchar(20) default NULL,
`numberpattern` tinyint(3) default 0,
- locale VARCHAR(80) DEFAULT NULL,
`distributedto` text,
`internalnotes` longtext,
`callnumber` text,
`location` varchar(80) NULL default '',
`branchcode` varchar(10) NOT NULL default '',
+ `hemisphere` tinyint(3) default 0,
`lastbranch` varchar(10),
`serialsadditems` tinyint(1) NOT NULL default '0',
`staffdisplaycount` VARCHAR(10) NULL,
`graceperiod` int(11) NOT NULL default '0',
`enddate` date default NULL,
`closed` INT(1) NOT NULL DEFAULT 0,
- PRIMARY KEY (`subscriptionid`),
- CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id) ON DELETE SET NULL ON UPDATE CASCADE,
- CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ON DELETE SET NULL ON UPDATE CASCADE
+ `reneweddate` date default NULL,
+ PRIMARY KEY (`subscriptionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
`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(80) default NULL, -- title 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
CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`, `branchcode`) REFERENCES `letter` (`module`, `code`, `branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `borrower_files`
+--
+
+DROP TABLE IF EXISTS `borrower_files`;
+CREATE TABLE IF NOT EXISTS `borrower_files` (
+ `file_id` int(11) NOT NULL AUTO_INCREMENT,
+ `borrowernumber` int(11) NOT NULL,
+ `file_name` varchar(255) NOT NULL,
+ `file_type` varchar(255) NOT NULL,
+ `file_description` varchar(255) DEFAULT NULL,
+ `file_content` longblob NOT NULL,
+ `date_uploaded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ PRIMARY KEY (`file_id`),
+ KEY `borrowernumber` (`borrowernumber`),
+ CONSTRAINT borrower_files_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
--
-- Table structure for table `borrower_message_preferences`
--
`notes` mediumtext, -- notes related to this order line
`supplierreference` mediumtext, -- not used? always NULL
`purchaseordernumber` mediumtext, -- not used? always NULL
- `subscription` tinyint(1) default NULL, -- not used? always NULL
- `serialid` varchar(30) default NULL, -- not used? always NULL
`basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno)
- `biblioitemnumber` int(11) default NULL, -- links this order line the biblioitems table (biblioitems.biblioitemnumber)
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified
`rrp` decimal(13,2) default NULL, -- the replacement cost for this line item
`ecost` decimal(13,2) default NULL, -- the estimated cost for this line item
`uncertainprice` tinyint(1), -- was this price uncertain (1 for yes, 0 for no)
`claims_count` int(11) default 0, -- count of claim letters generated
`claimed_date` date default NULL, -- last date a claim was generated
+ `subscriptionid` int(11) default NULL, -- links this order line to a subscription (subscription.subscriptionid)
parent_ordernumber int(11) default NULL, -- ordernumber of parent order line, or same as ordernumber if no parent
PRIMARY KEY (`ordernumber`),
KEY `basketno` (`basketno`),
KEY `budget_id` (`budget_id`),
CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
- CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE
+ CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `aqorders_subscriptionid` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
projects / koha.git / blobdiff