--
-- Host: localhost Database: koha30test
-- ------------------------------------------------------
--- Server version 4.1.22
+-- Server version 4.1.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
`marcxml` longtext NOT NULL,
PRIMARY KEY (`authid`),
KEY `origincode` (`origincode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `auth_subfield_structure`
`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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `auth_tag_structure`
`authorised_value` varchar(10) default NULL,
PRIMARY KEY (`authtypecode`,`tagfield`),
CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `auth_types`
`auth_tag_to_report` varchar(3) NOT NULL default '',
`summary` mediumtext NOT NULL,
PRIMARY KEY (`authtypecode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `authorised_values`
DROP TABLE IF EXISTS `authorised_values`;
CREATE TABLE `authorised_values` ( -- stores values for authorized values categories and values
`id` int(11) NOT NULL auto_increment, -- unique key, used to identify the authorized value
- `category` varchar(16) NOT NULL default '', -- key used to identify the authorized value category
+ `category` varchar(32) NOT NULL default '', -- key used to identify the authorized value category
`authorised_value` varchar(80) NOT NULL default '', -- code use to identify the authorized value
`lib` varchar(200) default NULL, -- authorized value description as printed in the staff client
`lib_opac` varchar(200) default NULL, -- authorized value description as printed in the OPAC
KEY `name` (`category`),
KEY `lib` (`lib`),
KEY `auth_value_idx` (`authorised_value`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `biblio`
`title` mediumtext, -- title (without the subtitle) from the MARC record (245$a in MARC21)
`unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
`notes` mediumtext, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
- `serial` tinyint(1) default NULL, -- foreign key, linking to the subscriptionid in the serial table
+ `serial` tinyint(1) default NULL, -- Boolean indicating whether biblio is for a serial
`seriestitle` mediumtext,
`copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
`abstract` mediumtext, -- summary from the MARC record (520$a in MARC21)
PRIMARY KEY (`biblionumber`),
KEY `blbnoidx` (`biblionumber`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `biblio_framework`
`frameworkcode` varchar(4) NOT NULL default '', -- the unique code assigned to the framework
`frameworktext` varchar(255) NOT NULL default '', -- the description/name given to the framework
PRIMARY KEY (`frameworkcode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `biblioitems`
`volume` mediumtext,
`number` mediumtext,
`itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
- `isbn` varchar(30) default NULL, -- ISBN (MARC21 020$a)
- `issn` varchar(9) default NULL, -- ISSN (MARC21 022$a)
+ `isbn` mediumtext, -- ISBN (MARC21 020$a)
+ `issn` mediumtext, -- ISSN (MARC21 022$a)
`ean` varchar(13) default NULL,
`publicationyear` text,
`publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
`place` varchar(255) default NULL, -- publication place (MARC21 260$a)
`lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
`marc` longblob, -- full bibliographic MARC record
- `url` varchar(255) default NULL, -- url (MARC21 856$u)
+ `url` text default NULL, -- url (MARC21 856$u)
`cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
`cn_class` varchar(30) default NULL,
`cn_item` varchar(10) default NULL,
`cn_suffix` varchar(10) default NULL,
- `cn_sort` varchar(30) default NULL, -- normalized version of the call number used for sorting
+ `cn_sort` varchar(255) default NULL, -- normalized version of the call number used for sorting
`agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
`totalissues` int(10),
- `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
+ `marcxml` longtext, -- full bibliographic MARC record in MARCXML
PRIMARY KEY (`biblioitemnumber`),
KEY `bibinoidx` (`biblioitemnumber`),
KEY `bibnoidx` (`biblionumber`),
KEY `itemtype_idx` (`itemtype`),
- KEY `isbn` (`isbn`),
- KEY `issn` (`issn`),
+ KEY `isbn` (`isbn`(255)),
+ KEY `issn` (`issn`(255)),
KEY `publishercode` (`publishercode`),
CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `borrowers`
`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
PRIMARY KEY `borrowernumber` (`borrowernumber`),
KEY `categorycode` (`categorycode`),
KEY `branchcode` (`branchcode`),
- KEY `userid` (`userid`),
+ UNIQUE KEY `userid` (`userid`),
KEY `guarantorid` (`guarantorid`),
+ KEY `surname_idx` (`surname`(255)),
+ KEY `firstname_idx` (`firstname`(255)),
+ KEY `othernames_idx` (`othernames`(255)),
CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `borrower_attribute_types`
`opac_display` tinyint(1) NOT NULL default 0, -- defines if this field is visible to patrons on their account in the OPAC (1 for yes, 0 for no)
`password_allowed` tinyint(1) NOT NULL default 0, -- defines if it is possible to associate a password with this custom field (1 for yes, 0 for no)
`staff_searchable` tinyint(1) NOT NULL default 0, -- defines if this field is searchable via the patron search in the staff client (1 for yes, 0 for no)
- `authorised_value_category` varchar(10) default NULL, -- foreign key from authorised_values that links this custom field to an authorized value category
+ `authorised_value_category` varchar(32) default NULL, -- foreign key from authorised_values that links this custom field to an authorized value category
`display_checkout` tinyint(1) NOT NULL default 0,-- defines if this field displays in checkout screens
`category_code` VARCHAR(10) NULL DEFAULT NULL,-- defines a category for an attribute_type
`class` VARCHAR(255) NOT NULL DEFAULT '',-- defines a class for an attribute_type
PRIMARY KEY (`code`),
KEY `auth_val_cat_idx` (`authorised_value_category`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `borrower_attributes`
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;
+) 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') 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`
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 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`
`show_in_pulldown` tinyint(1) NOT NULL DEFAULT '0', -- says this group should be in the opac libararies pulldown if it is enabled
PRIMARY KEY (`categorycode`),
KEY `show_in_pulldown` (`show_in_pulldown`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `branches`
`branchphone` mediumtext, -- the primary phone for your library or branch
`branchfax` mediumtext, -- the fax number for your library or branch
`branchemail` mediumtext, -- the primary email address for your library or branch
+ `branchreplyto` mediumtext, -- the email to be used as a Reply-To
+ `branchreturnpath` mediumtext, -- the email to be used as Return-Path
`branchurl` mediumtext, -- the URL for your library or branch's website
`issuing` tinyint(4) default NULL, -- unused in Koha
`branchip` varchar(15) default NULL, -- the IP address for your library or branch
`branchnotes` mediumtext, -- notes related to your library or branch
opac_info text, -- HTML that displays in OPAC
PRIMARY KEY (`branchcode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `branchrelations`
KEY `categorycode` (`categorycode`),
CONSTRAINT `branchrelations_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `branchrelations_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `branchcategories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `branchtransfers`
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`description` varchar(255) NOT NULL,
`number` bigint(20) NOT NULL,
`endnode` tinyint(4) NOT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `categories`
`enrolmentperiod` smallint(6) default NULL, -- number of months the patron is enrolled for (will be NULL if enrolmentperioddate is set)
`enrolmentperioddate` DATE NULL DEFAULT NULL, -- date the patron is enrolled until (will be NULL if enrolmentperiod is set)
`upperagelimit` smallint(6) default NULL, -- age limit for the patron
- `dateofbirthrequired` tinyint(1) default NULL,
+ `dateofbirthrequired` tinyint(1) default NULL, -- the minimum age required for the patron category
`finetype` varchar(30) default NULL, -- unused in Koha
`bulk` tinyint(1) default NULL,
`enrolmentfee` decimal(28,6) default NULL, -- enrollment fee for the patron
`reservefee` decimal(28,6) default NULL, -- cost to place holds
`hidelostitems` tinyint(1) NOT NULL default '0', -- are lost items shown to this category (1 for yes, 0 for no)
`category_type` varchar(1) NOT NULL default 'A', -- type of Koha patron (Adult, Child, Professional, Organizational, Statistical, Staff)
+ `BlockExpiredPatronOpacActions` tinyint(1) NOT NULL default '-1', -- wheither or not a patron of this category can renew books or place holds once their card has expired. 0 means they can, 1 means they cannot, -1 means use syspref BlockExpiredPatronOpacActions
+ `default_privacy` ENUM( 'default', 'never', 'forever' ) NOT NULL DEFAULT 'default', -- Default privacy setting for this patron category
PRIMARY KEY (`categorycode`),
UNIQUE KEY `categorycode` (`categorycode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table: collections
colId integer(11) NOT NULL auto_increment,
colTitle varchar(100) NOT NULL DEFAULT '',
colDesc text NOT NULL,
- colBranchcode varchar(4) DEFAULT NULL comment 'branchcode for branch where item should be held.',
+ colBranchcode varchar(10) DEFAULT NULL, -- 'branchcode for branch where item should be held.'
PRIMARY KEY (colId)
-) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Constraints for table `collections`
+--
+ALTER TABLE `collections`
+ ADD CONSTRAINT `collections_ibfk_1` FOREIGN KEY (`colBranchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Table: collections_tracking
--
DROP TABLE IF EXISTS collections_tracking;
CREATE TABLE collections_tracking (
- ctId integer(11) NOT NULL auto_increment,
+ collections_tracking_id integer(11) NOT NULL auto_increment,
colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId',
itemnumber integer(11) NOT NULL DEFAULT 0 comment 'items.itemnumber',
- PRIMARY KEY (ctId)
-) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
+ PRIMARY KEY (collections_tracking_id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `courses`
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
+ `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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `course_instructors`
DROP TABLE IF EXISTS course_instructors;
CREATE TABLE `course_instructors` (
- `course_id` int(11) NOT NULL,
- `borrowernumber` int(11) NOT NULL,
+ `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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Constraints for table `course_instructors`
DROP TABLE IF EXISTS course_items;
CREATE TABLE `course_items` (
- `ci_id` int(11) NOT NULL AUTO_INCREMENT,
+ `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, -- 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'
+ `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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Constraints for table `course_items`
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
+ `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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Constraints for table `course_reserves`
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `default_borrower_circ_rules`
PRIMARY KEY (`categorycode`),
CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `default_branch_circ_rules`
PRIMARY KEY (`branchcode`),
CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `default_branch_item_rules`
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `default_circ_rules`
`holdallowed` int(1) default NULL,
`returnbranch` varchar(15) default NULL,
PRIMARY KEY (`singleton`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `cities`
`city_country` VARCHAR( 100 ) NULL DEFAULT NULL, -- name of the country
`city_zipcode` varchar(20) default NULL, -- zip or postal code
PRIMARY KEY (`cityid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `class_sort_rules`
`sort_routine` varchar(30) NOT NULL default '',
PRIMARY KEY (`class_sort_rule`),
UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `class_sources`
UNIQUE KEY `cn_source_idx` (`cn_source`),
KEY `used_idx` (`used`),
CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`) REFERENCES `class_sort_rules` (`class_sort_rule`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `currency`
CREATE TABLE `currency` (
`currency` varchar(10) NOT NULL default '',
`symbol` varchar(5) default NULL,
+ `isocode` varchar(5) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`rate` float(15,5) default NULL,
`active` tinyint(1) default NULL,
PRIMARY KEY (`currency`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `deletedbiblio`
`title` mediumtext, -- title (without the subtitle) from the MARC record (245$a in MARC21)
`unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
`notes` mediumtext, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
- `serial` tinyint(1) default NULL, -- foreign key, linking to the subscriptionid in the serial table
+ `serial` tinyint(1) default NULL, -- Boolean indicating whether biblio is for a serial
`seriestitle` mediumtext,
`copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
`abstract` mediumtext, -- summary from the MARC record (520$a in MARC21)
PRIMARY KEY (`biblionumber`),
KEY `blbnoidx` (`biblionumber`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `deletedbiblioitems`
`volume` mediumtext,
`number` mediumtext,
`itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
- `isbn` varchar(30) default NULL, -- ISBN (MARC21 020$a)
- `issn` varchar(9) default NULL, -- ISSN (MARC21 022$a)
+ `isbn` mediumtext default NULL, -- ISBN (MARC21 020$a)
+ `issn` mediumtext default NULL, -- ISSN (MARC21 022$a)
`ean` varchar(13) default NULL,
`publicationyear` text,
`publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
`place` varchar(255) default NULL, -- publication place (MARC21 260$a)
`lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
`marc` longblob, -- full bibliographic MARC record
- `url` varchar(255) default NULL, -- url (MARC21 856$u)
+ `url` text default NULL, -- url (MARC21 856$u)
`cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
`cn_class` varchar(30) default NULL,
`cn_item` varchar(10) default NULL,
`cn_suffix` varchar(10) default NULL,
- `cn_sort` varchar(30) default NULL, -- normalized version of the call number used for sorting
+ `cn_sort` varchar(255) default NULL, -- normalized version of the call number used for sorting
`agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
`totalissues` int(10),
- `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
+ `marcxml` longtext, -- full bibliographic MARC record in MARCXML
PRIMARY KEY (`biblioitemnumber`),
KEY `bibinoidx` (`biblioitemnumber`),
KEY `bibnoidx` (`biblionumber`),
KEY `itemtype_idx` (`itemtype`),
- KEY `isbn` (`isbn`),
+ KEY `isbn` (`isbn`(255)),
KEY `publishercode` (`publishercode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `deletedborrowers`
`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history KEY `borrowernumber` (`borrowernumber`),
KEY borrowernumber (borrowernumber),
KEY `cardnumber` (`cardnumber`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `deleteditems`
`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)
+ `itemlost_on` datetime DEFAULT NULL, -- the date and time an item was last marked as lost, NULL if not lost
`withdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
+ `withdrawn_on` datetime DEFAULT NULL, -- the date and time an item was last marked as withdrawn, NULL if not withdrawn
`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
`permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
`onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and checkout date for checked out)
`cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
- `cn_sort` varchar(30) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting
+ `cn_sort` varchar(255) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting
`ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8)
- `materials` varchar(10) default NULL, -- materials specified (MARC21 952$3)
+ `materials` text default NULL, -- materials specified (MARC21 952$3)
`uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u)
`itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y)
`more_subfields_xml` longtext default NULL, -- additional 952 subfields in XML format
`enumchron` text default NULL, -- serial enumeration/chronology for the item (MARC21 952$h)
`copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t)
`stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i)
- `marc` longblob, -- unused in Koha
PRIMARY KEY (`itemnumber`),
KEY `delitembarcodeidx` (`barcode`),
KEY `delitemstocknumberidx` (`stocknumber`),
KEY `delhomebranch` (`homebranch`),
KEY `delholdingbranch` (`holdingbranch`),
KEY `itype_idx` (`itype`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `ethnicity`
`code` varchar(10) NOT NULL default '',
`name` varchar(255) default NULL,
PRIMARY KEY (`code`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `export_format`
`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';
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Used for CSV export';
--
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `import_batches`
`upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- date and time the file was uploaded
`overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new', -- how to handle duplicate records
`nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new', -- how to handle records where no match is found
- `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add', -- what to do with item records
+ `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore', 'replace') NOT NULL default 'always_add', -- what to do with item records
`import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging', -- the status of the imported file
`batch_type` enum('batch', 'z3950', 'webservice') NOT NULL default 'batch', -- where this batch has come from
`record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio', -- type of record in the batch
`comments` mediumtext, -- any comments added when the file was uploaded
PRIMARY KEY (`import_batch_id`),
KEY `branchcode` (`branchcode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `import_records`
KEY `branchcode` (`branchcode`),
KEY `batch_sequence` (`import_batch_id`, `record_sequence`),
KEY `batch_id_record_type` (`import_batch_id`,`record_type`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for `import_record_matches`
CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
KEY `record_score` (`import_record_id`, `score`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `import_auths`
CONSTRAINT `import_auths_ibfk_1` FOREIGN KEY (`import_record_id`)
REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
KEY `matched_authid` (`matched_authid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `import_biblios`
KEY `matched_biblionumber` (`matched_biblionumber`),
KEY `title` (`title`),
KEY `isbn` (`isbn`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `import_items`
REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
KEY `itemnumber` (`itemnumber`),
KEY `branchcode` (`branchcode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `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
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `issuingrules`
`reservecharge` decimal(28,6) default NULL,
`fine` decimal(28,6) default NULL, -- fine amount
`finedays` int(11) default NULL, -- suspension in days
+ `maxsuspensiondays` int(11) default NULL, -- max suspension days
`firstremind` int(11) default NULL, -- fine grace period
`chargeperiod` int(11) default NULL, -- how often the fine amount is charged
`accountsent` int(11) default NULL, -- not used? always NULL
`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
+ `norenewalbefore` int(4) default NULL, -- no renewal allowed until X days or hours before due date. In the unit set in issuingrules.lengthunit
+ `auto_renew` BOOLEAN default FALSE, -- automatic renewal
`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(28,6) default NULL, -- the maximum amount of an overdue fine
+ onshelfholds tinyint(1) NOT NULL default 0, -- allow holds for items that are on shelf
+ opacitemholds char(1) NOT NULL default 'N', -- allow opac users to place specific items on hold
PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
KEY `categorycode` (`categorycode`),
KEY `itemtype` (`itemtype`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `items`
`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)
+ `itemlost_on` datetime DEFAULT NULL, -- the date and time an item was last marked as lost, NULL if not lost
`withdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
+ `withdrawn_on` datetime DEFAULT NULL, -- the date and time an item was last marked as withdrawn, NULL if not withdrawn
`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
`permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
`onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and checkout date for checked out)
`cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
- `cn_sort` varchar(30) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting
+ `cn_sort` varchar(255) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting
`ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8)
`materials` text default NULL, -- materials specified (MARC21 952$3)
`uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u)
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
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `itemtypes`
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"
+ sip_media_type VARCHAR(3) DEFAULT NULL, -- SIP2 protocol media type for this itemtype
PRIMARY KEY (`itemtype`),
UNIQUE KEY `itemtype` (`itemtype`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `creator_batches`
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `creator_images`
`image_name` char(20) NOT NULL DEFAULT 'DEFAULT',
PRIMARY KEY (`image_id`),
UNIQUE KEY `image_name_index` (`image_name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `creator_layouts`
`layout_xml` text NOT NULL,
`creator` char(15) NOT NULL DEFAULT 'Labels',
PRIMARY KEY (`layout_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `creator_templates`
`creator` char(15) NOT NULL DEFAULT 'Labels',
PRIMARY KEY (`template_id`),
KEY `template_profile_fk_constraint` (`profile_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `letter`
`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
- PRIMARY KEY (`module`,`code`, `branchcode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+ `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`
KEY `kohafield_2` (`kohafield`),
KEY `tab` (`frameworkcode`,`tab`),
KEY `kohafield` (`frameworkcode`,`kohafield`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `marc_tag_structure`
`authorised_value` varchar(10) default NULL,
`frameworkcode` varchar(4) NOT NULL default '',
PRIMARY KEY (`frameworkcode`,`tagfield`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `marc_matchers`
PRIMARY KEY (`matcher_id`),
KEY `code` (`code`),
KEY `record_type` (`record_type`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `matchpoints`
PRIMARY KEY (`matchpoint_id`),
CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
KEY `by_sequence` (`matchpoint_id`, `sequence`),
CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `matcher_component_norms`
KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `matcher_matchpoints`
REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `matchchecks`
REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `need_merge_authorities`
`id` int NOT NULL auto_increment PRIMARY KEY, -- unique id
`authid` bigint NOT NULL, -- reference to authority record
`done` tinyint DEFAULT 0 -- indication whether merge has been executed (0=not done, 1= done, 2= in progress)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `notifys`
`notify_send_date` date default NULL,
`notify_level` int(1) NOT NULL default 0,
`method` varchar(20) NOT NULL default ''
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `oai_sets`
`spec` varchar(80) NOT NULL UNIQUE,
`name` varchar(80) NOT NULL,
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `oai_sets_descriptions`
`set_id` int(11) NOT NULL,
`description` varchar(255) NOT NULL,
CONSTRAINT `oai_sets_descriptions_ibfk_1` FOREIGN KEY (`set_id`) REFERENCES `oai_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `oai_sets_mappings`
`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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `oai_sets_biblios`
PRIMARY KEY (`biblionumber`, `set_id`),
CONSTRAINT `oai_sets_biblios_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `oai_sets_biblios_ibfk_2` FOREIGN KEY (`set_id`) REFERENCES `oai_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `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
KEY `old_issuesborridx` (`borrowernumber`),
KEY `old_issuesitemidx` (`itemnumber`),
KEY `branchcode_idx` (`branchcode`),
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `old_reserves`
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
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) 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
- PRIMARY KEY (`idnew`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+ PRIMARY KEY (`idnew`),
+ 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`
`letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
`debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
PRIMARY KEY (`branchcode`,`categorycode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `patroncards`
PRIMARY KEY (`cardid`),
KEY `patroncards_ibfk_1` (`borrowernumber`),
CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) 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
- `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
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+ 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`
--
cardnumber varchar(16) DEFAULT NULL,
amount decimal(28,6) DEFAULT NULL,
PRIMARY KEY (operationid)
-) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`printqueue` varchar(20) default NULL,
`printtype` varchar(20) default NULL,
PRIMARY KEY (`printername`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `printers_profile`
`creator` char(15) NOT NULL DEFAULT 'Labels',
PRIMARY KEY (`profile_id`),
UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `repeatable_holidays`
`title` varchar(50) NOT NULL default '', -- title of this closing
`description` text NOT NULL, -- description for this closing
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `reports_dictionary`
report_area varchar(6) DEFAULT NULL, -- Koha module this definition is for Circulation, Catalog, Patrons, Acquistions, Accounts)
PRIMARY KEY (id),
KEY dictionary_area_idx (report_area)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `reserveconstraints`
`biblionumber` int(11) NOT NULL default 0,
`biblioitemnumber` int(11) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `reserves`
CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `reviews`
PRIMARY KEY (`reviewid`),
CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
---
--- Table structure for table `roadtype`
---
-
-DROP TABLE IF EXISTS `roadtype`;
-CREATE TABLE `roadtype` ( -- road types defined in administration and used in patron management
- `roadtypeid` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha for each road type
- `road_type` varchar(100) NOT NULL default '', -- text for road type
- PRIMARY KEY (`roadtypeid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `saved_sql`
`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) default NULL, -- the name of this report
+ `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,
PRIMARY KEY (`id`),
KEY sql_area_group_idx (report_group, report_subgroup),
KEY boridx (`borrowernumber`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`report` longtext,
`date_run` datetime default NULL,
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
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`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
+ KEY `sessionid` (`sessionid`),
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Opac search history results';
--
--
DROP TABLE IF EXISTS `serial`;
-CREATE TABLE `serial` (
- `serialid` int(11) NOT NULL auto_increment,
- `biblionumber` varchar(100) NOT NULL default '',
- `subscriptionid` varchar(100) NOT NULL default '',
- `serialseq` varchar(100) NOT NULL default '',
- `status` tinyint(4) NOT NULL default 0,
- `planneddate` date default NULL,
- `notes` text,
- `publisheddate` date default NULL,
- `claimdate` date default NULL,
- `routingnotes` text,
+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)
+ `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
+ `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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `sessions`
`id` varchar(32) NOT NULL,
`a_session` text NOT NULL,
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `special_holidays`
`title` varchar(50) NOT NULL default '', -- title for this closing
`description` text NOT NULL, -- description of this closing
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `statistics`
CREATE TABLE `statistics` ( -- information related to transactions (circulation and fines) in Koha
`datetime` datetime default NULL, -- date and time of the transaction
`branch` varchar(10) default NULL, -- foreign key, branch where the transaction occurred
- `proccode` varchar(4) default NULL, -- proceedure code
+ `proccode` varchar(4) default NULL, -- type of procedure used when making payments (does not appear in the code)
`value` double(16,4) default NULL, -- monetary value associated with the transaction
`type` varchar(16) default NULL, -- transaction type (locause, issue, return, renew, writeoff, payment, Credit*)
- `other` mediumtext,
- `usercode` varchar(10) default NULL,
+ `other` mediumtext, -- used by SIP
+ `usercode` varchar(10) default NULL, -- unused in Koha
`itemnumber` int(11) default NULL, -- foreign key from the items table, links transaction to a specific item
`itemtype` varchar(10) default NULL, -- foreign key from the itemtypes table, links transaction to a specific item type
`borrowernumber` int(11) default NULL, -- foreign key from the borrowers table, links transaction to a specific borrower
- `associatedborrower` int(11) default NULL,
+ `associatedborrower` int(11) default NULL, -- unused in Koha
`ccode` varchar(10) default NULL, -- foreign key from the items table, links transaction to a specific collection code
KEY `timeidx` (`datetime`),
KEY `branch_idx` (`branch`),
KEY `borrowernumber_idx` (`borrowernumber`),
KEY `associatedborrower_idx` (`associatedborrower`),
KEY `ccode_idx` (`ccode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `stopwords`
DROP TABLE IF EXISTS `stopwords`;
CREATE TABLE `stopwords` (
`word` varchar(255) default NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- 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 COLLATE=utf8_unicode_ci;
+
+--
+-- 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 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `subscription`
--
DROP TABLE IF EXISTS `subscription`;
-CREATE TABLE `subscription` (
- `biblionumber` int(11) NOT NULL default 0,
- `subscriptionid` int(11) NOT NULL auto_increment,
- `librarian` varchar(100) default '',
- `startdate` date default NULL,
- `aqbooksellerid` int(11) default 0,
+CREATE TABLE `subscription` ( -- information related to the subscription
+ `biblionumber` int(11) NOT NULL default 0, -- foreign key for biblio.biblionumber that this subscription is attached to
+ `subscriptionid` int(11) NOT NULL auto_increment, -- unique key for this subscription
+ `librarian` varchar(100) default '', -- the librarian's username from borrowers.userid
+ `startdate` date default NULL, -- start date for this subscription
+ `aqbooksellerid` int(11) default 0, -- foreign key for aqbooksellers.id to link to the vendor
`cost` int(11) default 0,
`aqbudgetid` int(11) default 0,
- `weeklength` int(11) default 0,
- `monthlength` int(11) default 0,
- `numberlength` int(11) default 0,
- `periodicity` tinyint(4) default 0,
- `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,
+ `weeklength` int(11) default 0, -- subscription length in weeks (will not be filled in if monthlength or numberlength is set)
+ `monthlength` int(11) default 0, -- subscription length in weeks (will not be filled in if weeklength or numberlength is set)
+ `numberlength` int(11) default 0, -- subscription length in weeks (will not be filled in if monthlength or weeklength is set)
+ `periodicity` integer default null, -- frequency type links to subscription_frequencies.id
+ countissuesperunit INTEGER NOT NULL DEFAULT 1,
+ `notes` mediumtext, -- notes
+ `status` varchar(100) NOT NULL default '', -- status of this subscription
`lastvalue1` int(11) default NULL,
- `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,
+ `lastvalue2` int(11) default NULL,
`innerloop2` int(11) default 0,
- `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,
+ `innerloop3` int(11) default 0,
+ `firstacquidate` date default NULL, -- first issue received date
+ `manualhistory` tinyint(1) NOT NULL default 0, -- yes or no to managing the history manually
+ `irregularity` text, -- any irregularities in the subscription
+ skip_serialseq BOOLEAN NOT NULL DEFAULT 0,
`letter` varchar(20) default NULL,
- `numberpattern` tinyint(3) default 0,
+ `numberpattern` integer default null, -- the numbering pattern used links to subscription_numberpatterns.id
+ locale VARCHAR(80) DEFAULT NULL, -- for foreign language subscriptions to display months, seasons, etc correctly
`distributedto` text,
`internalnotes` longtext,
- `callnumber` text,
- `location` varchar(80) NULL default '',
- `branchcode` varchar(10) NOT NULL default '',
- `hemisphere` tinyint(3) default 0,
+ `callnumber` text, -- default call number
+ `location` varchar(80) NULL default '', -- default shelving location (items.location)
+ `branchcode` varchar(10) NOT NULL default '', -- default branches (items.homebranch)
`lastbranch` varchar(10),
- `serialsadditems` tinyint(1) NOT NULL default '0',
- `staffdisplaycount` VARCHAR(10) NULL,
- `opacdisplaycount` VARCHAR(10) NULL,
- `graceperiod` int(11) NOT NULL default '0',
- `enddate` date default NULL,
- `closed` INT(1) NOT NULL DEFAULT 0,
- `reneweddate` date default NULL,
- PRIMARY KEY (`subscriptionid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+ `serialsadditems` tinyint(1) NOT NULL default '0', -- does receiving this serial create an item record
+ `staffdisplaycount` VARCHAR(10) NULL, -- how many issues to show to the staff
+ `opacdisplaycount` VARCHAR(10) NULL, -- how many issues to show to the public
+ `graceperiod` int(11) NOT NULL default '0', -- grace period in days
+ `enddate` date default NULL, -- subscription end date
+ `closed` INT(1) NOT NULL DEFAULT 0, -- yes / no if the subscription is closed
+ `reneweddate` date default NULL, -- date of last renewal for the subscription
+ 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
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `subscriptionhistory`
`librariannote` varchar(150) NOT NULL default '',
PRIMARY KEY (`subscriptionid`),
KEY `biblionumber` (`biblionumber`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `subscriptionroutinglist`
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `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 default 0, -- date the suggestion was submitted
+ `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
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(3) 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)
+ quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased
+ currency VARCHAR(3) 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`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+ KEY `managedby` (`managedby`),
+ 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`
`explanation` text, -- descriptive text for the system preference
`type` varchar(20) default NULL, -- type of question this preference asks (multiple choice, plain text, yes or no, etc)
PRIMARY KEY (`variable`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `tags`
`entry` varchar(255) NOT NULL default '',
`weight` bigint(20) NOT NULL default 0,
PRIMARY KEY (`entry`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `tags_all`
REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `tags_approval`
KEY `tags_approval_borrowers_fk_1` (`approved_by`),
CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `tags_index`
REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `userflags`
`flagdesc` varchar(255) default NULL,
`defaulton` int(11) default NULL,
PRIMARY KEY (`bit`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `virtualshelves`
`allow_delete_other` tinyint(1) default 0, -- permission for deleting entries from list that another person added
PRIMARY KEY (`shelfnumber`),
CONSTRAINT `virtualshelves_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in VirtualShelves.pm
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `virtualshelfcontents`
CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `shelfcontents_ibfk_3` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in VirtualShelves.pm
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `virtualshelfshares`
`sharedate` datetime, -- date of invitation or acceptance of invitation
CONSTRAINT `virtualshelfshares_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `virtualshelfshares_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in VirtualShelves.pm
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `z3950servers`
DROP TABLE IF EXISTS `z3950servers`;
CREATE TABLE `z3950servers` ( -- connection information for the Z39.50 targets used in cataloging
+ `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
`host` varchar(255) default NULL, -- target's host name
`port` int(11) default NULL, -- port number used to connect to target
`db` varchar(255) default NULL, -- target's database name
`userid` varchar(255) default NULL, -- username needed to log in to target
`password` varchar(255) default NULL, -- password needed to log in to target
- `name` mediumtext, -- name given to the target by the library
- `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
+ `servername` mediumtext NOT NULL, -- name given to the target by the library
`checked` smallint(6) default NULL, -- whether this target is checked by default (1 for yes, 0 for no)
`rank` int(11) default NULL, -- where this target appears in the list of targets
`syntax` varchar(80) default NULL, -- marc format provided by this target
`timeout` int(11) NOT NULL DEFAULT '0', -- number of seconds before Koha stops trying to access this server
- `icon` text, -- unused in Koha
- `position` enum('primary','secondary','') NOT NULL default 'primary',
- `type` enum('zed','opensearch') NOT NULL default 'zed',
+ `servertype` enum('zed','sru') NOT NULL default 'zed', -- zed means z39.50 server
`encoding` text default NULL, -- characters encoding provided by this target
- `description` text NOT NULL, -- unused in Koha
+ `recordtype` enum('authority','biblio') NOT NULL default 'biblio', -- server contains bibliographic or authority records
+ `sru_options` varchar(255) default NULL, -- options like sru=get, sru_version=1.1; will be passed to the server via ZOOM
+ `sru_fields` mediumtext default NULL, -- contains the mapping between the Z3950 search fields and the specific SRU server indexes
+ `add_xslt` mediumtext default NULL, -- zero or more paths to XSLT files to be processed on the search results
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `zebraqueue`
`time` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `services_throttle`
`service_type` varchar(10) NOT NULL default '',
`service_count` varchar(45) default NULL,
PRIMARY KEY (`service_type`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `language_subtag_registry`
id int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
KEY `subtag` (`subtag`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `language_rfc4646_to_iso639`
id int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
KEY `rfc4646_subtag` (`rfc4646_subtag`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `language_descriptions`
PRIMARY KEY (`id`),
KEY `lang` (`lang`),
KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `language_script_bidi`
rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
bidi varchar(3), -- rtl ltr
KEY `rfc4646_subtag` (`rfc4646_subtag`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `language_script_mapping`
language_subtag varchar(25),
script_subtag varchar(25),
KEY `language_subtag` (`language_subtag`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `permissions`
PRIMARY KEY (`module_bit`, `code`),
CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `serialitems`
KEY `serialitems_sfk_1` (`serialid`),
CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `user_permissions`
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `tmp_holdsqueue`
`pickbranch` varchar(10) default NULL,
`notes` text,
`item_level_request` tinyint(4) NOT NULL default 0
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `message_queue`
KEY `message_transport_type` (`message_transport_type`),
CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table `overduerules_transport_types`
+--
+
+DROP TABLE IF EXISTS `overduerules_transport_types`;
+CREATE TABLE overduerules_transport_types(
+ `id` INT(11) NOT NULL AUTO_INCREMENT,
+ `branchcode` varchar(10) NOT NULL DEFAULT '',
+ `categorycode` VARCHAR(10) NOT NULL DEFAULT '',
+ `letternumber` INT(1) NOT NULL DEFAULT 1,
+ `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email',
+ PRIMARY KEY (id),
+ CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT mtt_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 `message_attributes`
`takes_days` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`message_attribute_id`),
UNIQUE KEY `message_name` (`message_name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `message_transports`
CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `borrower_files`
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `borrower_message_preferences`
CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `borrower_message_transport_preferences`
KEY `message_transport_type` (`message_transport_type`),
CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_message_preference_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `borrower_message_transport_preferences_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for the table branch_transfer_limits
itemtype varchar(10) NULL,
ccode varchar(10) NULL,
PRIMARY KEY (limitId)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `item_circulation_alert_preferences`
`notification` varchar(16) NOT NULL,
PRIMARY KEY (`id`),
KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `messages`
`message` text NOT NULL, -- the text of the message
`message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- the date and time the message was written
PRIMARY KEY (`message_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `accountlines`
KEY `itemnumber` (`itemnumber`),
CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `accountoffsets`
`offsetamount` decimal(28,6) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `action_logs`
`object` int(11) default NULL, -- the object that the action was taken against (could be a borrowernumber, itemnumber, etc)
`info` text, -- information about the action (usually includes SQL statement)
PRIMARY KEY (`action_id`),
- KEY (`timestamp`,`user`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+ KEY `timestamp_idx` (`timestamp`),
+ KEY `user_idx` (`user`),
+ KEY `module_idx` (`module`(255)),
+ KEY `action_idx` (`action`(255)),
+ KEY `object_idx` (`object`),
+ KEY `info_idx` (`info`(255))
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `alert`
PRIMARY KEY (`alertid`),
KEY `borrowernumber` (`borrowernumber`),
KEY `type` (`type`,`externalid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `aqbasketgroups`
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `aqbasket`
`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
PRIMARY KEY (`basketno`),
KEY `booksellerid` (`booksellerid`),
KEY `basketgroupid` (`basketgroupid`),
KEY `contractnumber` (`contractnumber`),
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
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+ 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`
`notes` mediumtext, -- order notes
`bookselleremail` mediumtext, -- vendor email
`booksellerurl` mediumtext, -- unused in Koha
- `contact` varchar(100) default NULL, -- name of contact at vendor
`postal` mediumtext, -- vendor postal address (all lines)
`url` varchar(255) default NULL, -- vendor web address
- `contpos` varchar(100) default NULL, -- contact person's position
- `contphone` varchar(100) default NULL, -- contact's phone number
- `contfax` varchar(100) default NULL, -- contact's fax number
- `contaltphone` varchar(100) default NULL, -- contact's alternate phone number
- `contemail` varchar(100) default NULL, -- contact's email address
- `contnotes` mediumtext, -- notes related to the contact
`active` tinyint(4) default NULL, -- is this vendor active (1 for yes, 0 for no)
`listprice` varchar(10) default NULL, -- currency code for list prices
`invoiceprice` varchar(10) default NULL, -- currency code for invoice prices
KEY `invoiceprice` (`invoiceprice`),
CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `aqbudgets`
`budget_owner_id` int(11) default NULL, -- borrowernumber of the person who owns this fund (borrowers.borrowernumber)
`budget_permission` int(1) default '0', -- level of permission for this fund (used only by the owner, only by the library, or anyone)
PRIMARY KEY (`budget_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table aqbudgetborrowers
CONSTRAINT aqbudgetborrowers_ibfk_2 FOREIGN KEY (borrowernumber)
REFERENCES borrowers (borrowernumber)
ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `aqbudgetperiods`
`sort1_authcat` varchar(10) default NULL, -- statistical category for this budget
`sort2_authcat` varchar(10) default NULL, -- second statistical category for this budget
PRIMARY KEY (`budget_period_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `aqbudgets_planning`
`display` tinyint(1) DEFAULT 1,
PRIMARY KEY (`plan_id`),
CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'aqcontacts'
+--
+
+DROP TABLE IF EXISTS aqcontacts;
+CREATE TABLE aqcontacts (
+ id int(11) NOT NULL auto_increment, -- primary key and unique number assigned by Koha
+ name varchar(100) default NULL, -- name of contact at vendor
+ position varchar(100) default NULL, -- contact person's position
+ phone varchar(100) default NULL, -- contact's phone number
+ altphone varchar(100) default NULL, -- contact's alternate phone number
+ fax varchar(100) default NULL, -- contact's fax number
+ email varchar(100) default NULL, -- contact's email address
+ notes mediumtext, -- notes related to the contact
+ claimacquisition BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive acquisitions claims
+ claimissues BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive serial claims
+ acqprimary BOOLEAN NOT NULL DEFAULT 0, -- is this the primary contact for acquisitions messages
+ 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;
--
-- Table structure for table 'aqcontract'
PRIMARY KEY (`contractnumber`),
CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-
---
--- Table structure for table `aqorderdelivery`
---
-
-DROP TABLE IF EXISTS `aqorderdelivery`;
-CREATE TABLE `aqorderdelivery` (
- `ordernumber` date default NULL,
- `deliverynumber` smallint(6) NOT NULL default 0,
- `deliverydate` varchar(18) default NULL,
- `qtydelivered` smallint(6) default NULL,
- `deliverycomments` mediumtext
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
--
-- Table structure for table `aqorders`
`freight` decimal(28,6) default NULL, -- shipping costs (not used)
`unitprice` decimal(28,6) default NULL, -- the actual cost entered when receiving this line item
`quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far
- `cancelledby` varchar(10) default NULL, -- not used? always NULL
`datecancellationprinted` date default NULL, -- the date the line item was deleted
- `notes` mediumtext, -- notes related to this order line
+ `cancellationreason` text default NULL, -- reason of cancellation
+ `order_internalnote` mediumtext, -- notes related to this order line, made for staff
+ `order_vendornote` mediumtext, -- notes related to this order line, made for vendor
`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)
`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
+ `orderstatus` varchar(16) default 'new', -- the current status for this line item. Can be 'new', 'ordered', 'partial', 'complete' or 'cancelled'
PRIMARY KEY (`ordernumber`),
KEY `basketno` (`basketno`),
KEY `biblionumber` (`biblionumber`),
KEY `budget_id` (`budget_id`),
+ CONSTRAINT `aqorders_budget_id_fk` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE,
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_subscriptionid` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+--
+-- Table structure for table `aqorder_users`
+--
+
+DROP TABLE IF EXISTS `aqorder_users`;
+CREATE TABLE aqorder_users ( -- Mapping orders to patrons for notification sending
+ ordernumber int(11) NOT NULL, -- the order this patrons receive notifications from (aqorders.ordernumber)
+ borrowernumber int(11) NOT NULL, -- the borrowernumber for the patron receiving notifications for this order (borrowers.borrowernumber)
+ PRIMARY KEY (ordernumber, borrowernumber),
+ CONSTRAINT aqorder_users_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT aqorder_users_ibfk_2 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 `aqorders_items`
PRIMARY KEY (`itemnumber`),
KEY `ordernumber` (`ordernumber`),
CONSTRAINT aqorders_items_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table aqinvoices
PRIMARY KEY (invoiceid),
CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`fieldcode` char(3) NOT NULL, -- marc field number to map to this keyword
`subfieldcode` char(1) NOT NULL, -- marc subfield associated with the fieldcode to map to this keyword
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `transport_cost`
PRIMARY KEY (frombranch, tobranch),
CONSTRAINT transport_cost_ibfk_1 FOREIGN KEY (frombranch) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT transport_cost_ibfk_2 FOREIGN KEY (tobranch) REFERENCES branches (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `biblioimages`
`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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `social_data`
`score_avg` DECIMAL(5,2),
`num_scores` INT,
PRIMARY KEY (`isbn`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- 'Ratings' table. This tracks the star ratings set by borrowers.
PRIMARY KEY (borrowernumber,biblionumber),
CONSTRAINT ratings_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT ratings_ibfk_2 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `quotes`
`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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table categories_branches
branchcode VARCHAR(10),
FOREIGN KEY (categorycode) REFERENCES categories(categorycode) ON DELETE CASCADE,
FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
-) ENGINE=INNODB DEFAULT CHARSET=utf8;
+) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table authorised_values_branches
branchcode VARCHAR(10),
FOREIGN KEY (av_id) REFERENCES authorised_values(id) ON DELETE CASCADE,
FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
-) ENGINE=INNODB DEFAULT CHARSET=utf8;
+) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
b_branchcode VARCHAR(10),
FOREIGN KEY (bat_code) REFERENCES borrower_attribute_types(code) ON DELETE CASCADE,
FOREIGN KEY (b_branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
-) ENGINE=INNODB DEFAULT CHARSET=utf8;
+) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `borrower_modifications`
PRIMARY KEY (`verification_token`,`borrowernumber`),
KEY `verification_token` (`verification_token`),
KEY `borrowernumber` (`borrowernumber`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table linktracker
KEY itemidx (itemnumber),
KEY borridx (borrowernumber),
KEY dateidx (timeclicked)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table 'plugin_data'
plugin_key varchar(255) NOT NULL,
plugin_value text,
PRIMARY KEY (plugin_class,plugin_key)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- 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 COLLATE=utf8_unicode_ci;
+
+--
+-- 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 COLLATE=utf8_unicode_ci;
+
+--
+-- 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;
+
+--
+-- Table structure for table 'marc_modification_templates'
+--
+
+CREATE TABLE IF NOT EXISTS marc_modification_templates (
+ template_id int(11) NOT NULL AUTO_INCREMENT,
+ name text NOT NULL,
+ PRIMARY KEY (template_id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'marc_modification_template_actions'
+--
+
+CREATE TABLE IF NOT EXISTS marc_modification_template_actions (
+ mmta_id int(11) NOT NULL AUTO_INCREMENT,
+ template_id int(11) NOT NULL,
+ ordering int(3) NOT NULL,
+ action enum('delete_field','update_field','move_field','copy_field') NOT NULL,
+ field_number smallint(6) NOT NULL DEFAULT '0',
+ from_field varchar(3) NOT NULL,
+ from_subfield varchar(1) DEFAULT NULL,
+ field_value varchar(100) DEFAULT NULL,
+ to_field varchar(3) DEFAULT NULL,
+ to_subfield varchar(1) DEFAULT NULL,
+ to_regex_search text,
+ to_regex_replace text,
+ to_regex_modifiers varchar(8) DEFAULT '',
+ conditional enum('if','unless') DEFAULT NULL,
+ conditional_field varchar(3) DEFAULT NULL,
+ conditional_subfield varchar(1) DEFAULT NULL,
+ conditional_comparison enum('exists','not_exists','equals','not_equals') DEFAULT NULL,
+ conditional_value text,
+ conditional_regex tinyint(1) NOT NULL DEFAULT '0',
+ description text,
+ PRIMARY KEY (mmta_id),
+ CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table `misc_files`
+--
+
+CREATE TABLE IF NOT EXISTS `misc_files` ( -- miscellaneous files attached to records from various tables
+ `file_id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the file record
+ `table_tag` varchar(255) NOT NULL, -- usually table name, or arbitrary unique tag
+ `record_id` int(11) NOT NULL, -- record id from the table this file is associated to
+ `file_name` varchar(255) NOT NULL, -- file name
+ `file_type` varchar(255) NOT NULL, -- MIME type of the file
+ `file_description` varchar(255) DEFAULT NULL, -- description given to the file
+ `file_content` longblob NOT NULL, -- file content
+ `date_uploaded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- date and time the file was added
+ PRIMARY KEY (`file_id`),
+ KEY `table_tag` (`table_tag`),
+ KEY `record_id` (`record_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table `columns_settings`
+--
+
+CREATE TABLE IF NOT EXISTS columns_settings (
+ module varchar(255) NOT NULL,
+ page varchar(255) NOT NULL,
+ tablename varchar(255) NOT NULL,
+ columnname varchar(255) NOT NULL,
+ cannot_be_toggled int(1) NOT NULL DEFAULT 0,
+ is_hidden int(1) NOT NULL DEFAULT 0,
+ PRIMARY KEY(module, page, tablename, columnname)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'items_search_fields'
+--
+
+DROP TABLE IF EXISTS items_search_fields;
+CREATE TABLE items_search_fields (
+ name VARCHAR(255) NOT NULL,
+ label VARCHAR(255) NOT NULL,
+ tagfield CHAR(3) NOT NULL,
+ tagsubfield CHAR(1) NULL DEFAULT NULL,
+ authorised_values_category VARCHAR(32) NULL DEFAULT NULL,
+ PRIMARY KEY(name),
+ CONSTRAINT items_search_fields_authorised_values_category
+ FOREIGN KEY (authorised_values_category) REFERENCES authorised_values (category)
+ ON DELETE SET NULL 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 */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-
projects / koha.git / blobdiff