`linkid` tinyint(1) NOT NULL default 0,
`kohafield` varchar(45) NULL default '',
`frameworkcode` varchar(10) NOT NULL default '',
+ `defaultvalue` TEXT DEFAULT '',
PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
KEY `tab` (`authtypecode`,`tab`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`ethnicity` varchar(50) default NULL, -- unused in Koha
`ethnotes` varchar(255) default NULL, -- unused in Koha
`sex` varchar(1) default NULL, -- patron/borrower's gender
- `password` varchar(30) default NULL, -- patron/borrower's encrypted password
+ `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
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
--
+-- 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(80) 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(80) 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`
--
`notforloan` tinyint(1) NOT NULL default 0, -- authorized value defining why this item is not for loan (MARC21 952$7)
`damaged` tinyint(1) NOT NULL default 0, -- authorized value defining this item as damaged (MARC21 952$4)
`itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1)
- `wthdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
+ `withdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
`itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o)
`coded_location_qualifier` varchar(10) default NULL, -- coded location qualifier(MARC21 952$f)
`issues` smallint(6) default NULL, -- number of times this item has been checked out
`export_format_id` int(11) NOT NULL auto_increment,
`profile` varchar(255) NOT NULL,
`description` mediumtext NOT NULL,
- `marcfields` mediumtext NOT NULL,
+ `content` mediumtext NOT NULL,
`csv_separator` varchar(2) NOT NULL,
`field_separator` varchar(2) NOT NULL,
`subfield_separator` varchar(2) NOT NULL,
`encoding` varchar(255) NOT NULL,
+ `type` varchar(255) DEFAULT 'marc',
PRIMARY KEY (`export_format_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
`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
+ overduefinescap decimal(28,6) default NULL, -- the maximum amount of an overdue fine
PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
KEY `categorycode` (`categorycode`),
KEY `itemtype` (`itemtype`)
`notforloan` tinyint(1) NOT NULL default 0, -- authorized value defining why this item is not for loan (MARC21 952$7)
`damaged` tinyint(1) NOT NULL default 0, -- authorized value defining this item as damaged (MARC21 952$4)
`itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1)
- `wthdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
+ `withdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
`itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o)
`coded_location_qualifier` varchar(10) default NULL, -- coded location qualifier(MARC21 952$f)
`issues` smallint(6) default NULL, -- number of times this item has been checked out/issued
DROP TABLE IF EXISTS `itemtypes`;
CREATE TABLE `itemtypes` ( -- defines the item types
- `itemtype` varchar(10) NOT NULL default '', -- unique key, a code associated with the item type
- `description` mediumtext, -- a plain text explanation of the item type
- `rentalcharge` double(16,4) default NULL, -- the amount charged when this item is checked out/issued
- `notforloan` smallint(6) default NULL, -- 1 if the item is not for loan, 0 if the item is available for loan
- `imageurl` varchar(200) default NULL, -- URL for the item type icon
- `summary` text, -- information from the summary field, may include HTML
+ itemtype varchar(10) NOT NULL default '', -- unique key, a code associated with the item type
+ description mediumtext, -- a plain text explanation of the item type
+ rentalcharge double(16,4) default NULL, -- the amount charged when this item is checked out/issued
+ notforloan smallint(6) default NULL, -- 1 if the item is not for loan, 0 if the item is available for loan
+ imageurl varchar(200) default NULL, -- URL for the item type icon
+ summary text, -- information from the summary field, may include HTML
+ checkinmsg VARCHAR(255), -- message that is displayed when an item with the given item type is checked in
+ checkinmsgtype CHAR(16) DEFAULT 'message' NOT NULL, -- type (CSS class) for the checkinmsg, can be "alert" or "message"
PRIMARY KEY (`itemtype`),
UNIQUE KEY `itemtype` (`itemtype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`set_id` int(11) NOT NULL,
`marcfield` char(3) NOT NULL,
`marcsubfield` char(1) NOT NULL,
+ `operator` varchar(8) NOT NULL default 'equal',
`marcvalue` varchar(80) NOT NULL,
CONSTRAINT `oai_sets_mappings_ibfk_1` FOREIGN KEY (`set_id`) REFERENCES `oai_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `patronimage`;
CREATE TABLE `patronimage` ( -- information related to patron images
- `cardnumber` varchar(16) NOT NULL, -- the cardnumber of the patron this image is attached to (borrowers.cardnumber)
+ `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 (`cardnumber`),
- CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
+ PRIMARY KEY (`borrowernumber`),
+ CONSTRAINT `patronimage_fk1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table structure for table `pending_offline_operations`
`planneddate` date default NULL,
`notes` text,
`publisheddate` date default NULL,
- `itemnumber` text default NULL,
`claimdate` date default NULL,
`routingnotes` text,
PRIMARY KEY (`serialid`)
`type` enum('zed','opensearch') NOT NULL default 'zed',
`encoding` text default NULL, -- characters encoding provided by this target
`description` text NOT NULL, -- unused in Koha
+ `recordtype` varchar(45) NOT NULL default 'biblio', -- server contains bibliographic or authority records
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
+-- Table structure for table `borrower_files`
+--
+
+DROP TABLE IF EXISTS `borrower_files`;
+CREATE TABLE IF NOT EXISTS `borrower_files` ( -- files attached to the patron/borrower record
+ `file_id` int(11) NOT NULL AUTO_INCREMENT, -- unique key
+ `borrowernumber` int(11) NOT NULL, -- foreign key linking to the patron via the borrowernumber
+ `file_name` varchar(255) NOT NULL, -- file name
+ `file_type` varchar(255) NOT NULL, -- type of file
+ `file_description` varchar(255) DEFAULT NULL, -- description given to the file
+ `file_content` longblob NOT NULL, -- the file
+ `date_uploaded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- date and time the file was added
+ 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`
--
`supplierreference` mediumtext, -- not used? always NULL
`purchaseordernumber` mediumtext, -- 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
--
+-- Table structure for table aqorders_transfers
+--
+
+DROP TABLE IF EXISTS aqorders_transfers;
+CREATE TABLE aqorders_transfers (
+ ordernumber_from int(11) NULL,
+ ordernumber_to int(11) NULL,
+ timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ UNIQUE KEY ordernumber_from (ordernumber_from),
+ UNIQUE KEY ordernumber_to (ordernumber_to),
+ CONSTRAINT aqorders_transfers_ordernumber_from FOREIGN KEY (ordernumber_from) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT aqorders_transfers_ordernumber_to FOREIGN KEY (ordernumber_to) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
-- Table structure for table aqinvoices
--
DROP TABLE IF EXISTS `biblioimages`;
-CREATE TABLE `biblioimages` (
- `imagenumber` int(11) NOT NULL AUTO_INCREMENT,
- `biblionumber` int(11) NOT NULL,
- `mimetype` varchar(15) NOT NULL,
- `imagefile` mediumblob NOT NULL,
- `thumbnail` mediumblob NOT NULL,
+CREATE TABLE `biblioimages` ( -- local cover images
+ `imagenumber` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the image
+ `biblionumber` int(11) NOT NULL, -- foreign key from biblio table to link to biblionumber
+ `mimetype` varchar(15) NOT NULL, -- image type
+ `imagefile` mediumblob NOT NULL, -- image file contents
+ `thumbnail` mediumblob NOT NULL, -- thumbnail file contents
PRIMARY KEY (`imagenumber`),
CONSTRAINT `bibliocoverimage_fk1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS quotes;
-CREATE TABLE `quotes` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `source` text DEFAULT NULL,
- `text` mediumtext NOT NULL,
- `timestamp` datetime NOT NULL,
+CREATE TABLE `quotes` ( -- data for the quote of the day feature
+ `id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the quote
+ `source` text DEFAULT NULL, -- source/credit for the quote
+ `text` mediumtext NOT NULL, -- text of the quote
+ `timestamp` datetime NOT NULL, -- date and time that the quote last appeared in the opac
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
PRIMARY KEY (plugin_class,plugin_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `patron_lists`
+--
+
+DROP TABLE IF EXISTS patron_lists;
+CREATE TABLE patron_lists (
+ patron_list_id int(11) NOT NULL AUTO_INCREMENT, -- unique identifier
+ name varchar(255) CHARACTER SET utf8 NOT NULL, -- the list's name
+ owner int(11) NOT NULL, -- borrowernumber of the list creator
+ PRIMARY KEY (patron_list_id),
+ KEY owner (owner)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Constraints for table `patron_lists`
+--
+ALTER TABLE `patron_lists`
+ ADD CONSTRAINT patron_lists_ibfk_1 FOREIGN KEY (`owner`) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE;
+
+--
+-- Table structure for table 'patron_list_patrons'
+--
+
+DROP TABLE IF EXISTS patron_list_patrons;
+CREATE TABLE patron_list_patrons (
+ patron_list_patron_id int(11) NOT NULL AUTO_INCREMENT, -- unique identifier
+ patron_list_id int(11) NOT NULL, -- the list this entry is part of
+ borrowernumber int(11) NOT NULL, -- the borrower that is part of this list
+ PRIMARY KEY (patron_list_patron_id),
+ KEY patron_list_id (patron_list_id),
+ KEY borrowernumber (borrowernumber)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Constraints for table `patron_list_patrons`
+--
+ALTER TABLE `patron_list_patrons`
+ ADD CONSTRAINT patron_list_patrons_ibfk_1 FOREIGN KEY (patron_list_id) REFERENCES patron_lists (patron_list_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ ADD CONSTRAINT patron_list_patrons_ibfk_2 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE;
+
+
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
projects / koha_fer / blobdiff