--
DROP TABLE IF EXISTS `borrower_attribute_types`;
-CREATE TABLE `borrower_attribute_types` (
- `code` varchar(10) NOT NULL,
- `description` varchar(255) NOT NULL,
- `repeatable` tinyint(1) NOT NULL default 0,
- `unique_id` tinyint(1) NOT NULL default 0,
- `opac_display` tinyint(1) NOT NULL default 0,
- `password_allowed` tinyint(1) NOT NULL default 0,
- `staff_searchable` tinyint(1) NOT NULL default 0,
- `authorised_value_category` varchar(10) default NULL,
+CREATE TABLE `borrower_attribute_types` ( -- definitions for custom patron fields known as extended patron attributes
+ `code` varchar(10) NOT NULL, -- unique key used to identify each custom field
+ `description` varchar(255) NOT NULL, -- description for each custom field
+ `repeatable` tinyint(1) NOT NULL default 0, -- defines whether one patron/borrower can have multiple values for this custom field (1 for yes, 0 for no)
+ `unique_id` tinyint(1) NOT NULL default 0, -- defines if this value needs to be unique (1 for yes, 0 for no)
+ `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
PRIMARY KEY (`code`),
KEY `auth_val_cat_idx` (`authorised_value_category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `borrower_attributes`;
-CREATE TABLE `borrower_attributes` (
- `borrowernumber` int(11) NOT NULL,
- `code` varchar(10) NOT NULL,
- `attribute` varchar(64) default NULL,
- `password` varchar(64) default NULL,
+CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as extended patron attributes linked to patrons/borrowers
+ `borrowernumber` int(11) NOT NULL, -- foreign key from the borrowers table, defines which patron/borrower has this attribute
+ `code` varchar(10) NOT NULL, -- foreign key from the borrower_attribute_types table, defines which custom field this value was entered for
+ `attribute` varchar(64) default NULL, -- custom patron field value
+ `password` varchar(64) default NULL, -- password associated with this field
KEY `borrowernumber` (`borrowernumber`),
KEY `code_attribute` (`code`, `attribute`),
CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `branch_item_rules`
+--
+
DROP TABLE IF EXISTS `branch_item_rules`;
CREATE TABLE `branch_item_rules` (
`branchcode` varchar(10) NOT NULL,
--
DROP TABLE IF EXISTS `branchcategories`;
-CREATE TABLE `branchcategories` (
- `categorycode` varchar(10) NOT NULL default '',
- `categoryname` varchar(32),
- `codedescription` mediumtext,
- `categorytype` varchar(16),
+CREATE TABLE `branchcategories` ( -- information related to library/branch groups
+ `categorycode` varchar(10) NOT NULL default '', -- unique identifier for the library/branch group
+ `categoryname` varchar(32), -- name of the library/branch group
+ `codedescription` mediumtext, -- longer description of the library/branch group
+ `categorytype` varchar(16), -- says whether this is a search group or a properties group
PRIMARY KEY (`categorycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `branches`;
-CREATE TABLE `branches` (
- `branchcode` varchar(10) NOT NULL default '',
- `branchname` mediumtext NOT NULL,
- `branchaddress1` mediumtext,
- `branchaddress2` mediumtext,
- `branchaddress3` mediumtext,
- `branchzip` varchar(25) default NULL,
- `branchcity` mediumtext,
- `branchstate` mediumtext,
- `branchcountry` text,
- `branchphone` mediumtext,
- `branchfax` mediumtext,
- `branchemail` mediumtext,
- `branchurl` mediumtext,
- `issuing` tinyint(4) default NULL,
- `branchip` varchar(15) default NULL,
- `branchprinter` varchar(100) default NULL,
- `branchnotes` mediumtext,
+CREATE TABLE `branches` ( -- information about your libraries or branches are stored here
+ `branchcode` varchar(10) NOT NULL default '', -- a unique key assigned to each branch
+ `branchname` mediumtext NOT NULL, -- the name of your library or branch
+ `branchaddress1` mediumtext, -- the first address line of for your library or branch
+ `branchaddress2` mediumtext, -- the second address line of for your library or branch
+ `branchaddress3` mediumtext, -- the third address line of for your library or branch
+ `branchzip` varchar(25) default NULL, -- the zip or postal code for your library or branch
+ `branchcity` mediumtext, -- the city or province for your library or branch
+ `branchstate` mediumtext, -- the state for your library or branch
+ `branchcountry` text, -- the county for your library or branch
+ `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
+ `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
+ `branchprinter` varchar(100) default NULL, -- unused in Koha
+ `branchnotes` mediumtext, -- notes related to your library or branch
UNIQUE KEY `branchcode` (`branchcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `branchrelations`;
-CREATE TABLE `branchrelations` (
- `branchcode` varchar(10) NOT NULL default '',
- `categorycode` varchar(10) NOT NULL default '',
+CREATE TABLE `branchrelations` ( -- this table links libraries/branches to groups
+ `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table to identify the branch
+ `categorycode` varchar(10) NOT NULL default '', -- foreign key from the branchcategories table to identify the group
PRIMARY KEY (`branchcode`,`categorycode`),
KEY `branchcode` (`branchcode`),
KEY `categorycode` (`categorycode`),
--
DROP TABLE IF EXISTS `cities`;
-CREATE TABLE `cities` (
- `cityid` int(11) NOT NULL auto_increment,
- `city_name` varchar(100) NOT NULL default '',
- `city_state` VARCHAR( 100 ) NULL DEFAULT NULL,
- `city_country` VARCHAR( 100 ) NULL DEFAULT NULL,
- `city_zipcode` varchar(20) default NULL,
+CREATE TABLE `cities` ( -- authorized values for cities/states/countries to choose when adding/editing a patron/borrower
+ `cityid` int(11) NOT NULL auto_increment, -- unique identifier added by Koha
+ `city_name` varchar(100) NOT NULL default '', -- name of the city
+ `city_state` VARCHAR( 100 ) NULL DEFAULT NULL, -- name of the state/province
+ `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;
--
DROP TABLE IF EXISTS `issues`;
-CREATE TABLE `issues` (
- `borrowernumber` int(11),
- `itemnumber` int(11),
- `date_due` date default NULL,
- `branchcode` varchar(10) default NULL,
+CREATE TABLE `issues` ( -- information related to check outs or issues
+ `borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to
+ `itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out
+ `date_due` date default NULL, -- date the item is due (yyyy-mm-dd)
+ `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
`issuingbranch` varchar(18) default NULL,
- `returndate` date default NULL,
- `lastreneweddate` date default NULL,
+ `returndate` date default NULL, -- date the item was returned, will be NULL until moved to old_issues
+ `lastreneweddate` date default NULL, -- date the item was last renewed
`return` varchar(4) default NULL,
- `renewals` tinyint(4) default NULL,
- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
- `issuedate` date default NULL,
- PRIMARY KEY (`itemnumber`),
+ `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
+ `issuedate` date default NULL, -- date the item was checked out or issued
KEY `issuesborridx` (`borrowernumber`),
KEY `bordate` (`borrowernumber`,`timestamp`),
CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
--
DROP TABLE IF EXISTS `itemtypes`;
-CREATE TABLE `itemtypes` (
- `itemtype` varchar(10) NOT NULL default '',
- `description` mediumtext,
- `rentalcharge` double(16,4) default NULL,
- `notforloan` smallint(6) default NULL,
- `imageurl` varchar(200) default NULL,
- `summary` text,
+CREATE TABLE `itemtypes` ( -- defines the item types
+ `itemtype` varchar(10) NOT NULL default '', -- unique key, a code associated with the item type
+ `description` mediumtext, -- a plain text explanation of the item type
+ `rentalcharge` double(16,4) default NULL, -- the amount charged when this item is checked out/issued
+ `notforloan` smallint(6) default NULL, -- 1 if the item is not for loan, 0 if the item is available for loan
+ `imageurl` varchar(200) default NULL, -- URL for the item type icon
+ `summary` text, -- information from the summary field, may include HTML
PRIMARY KEY (`itemtype`),
UNIQUE KEY `itemtype` (`itemtype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `letter`;
-CREATE TABLE `letter` (
- `module` varchar(20) NOT NULL default '',
- `code` varchar(20) NOT NULL default '',
- `name` varchar(100) NOT NULL default '',
- `title` varchar(200) NOT NULL default '',
- `content` text,
+CREATE TABLE `letter` ( -- table for all notice templates in Koha
+ `module` varchar(20) NOT NULL default '', -- Koha module that triggers this notice
+ `code` varchar(20) NOT NULL default '', -- unique identifier for this notice
+ `name` varchar(100) NOT NULL default '', -- plain text name for this notice
+ `title` varchar(200) NOT NULL default '', -- subject line of the notice
+ `content` text, -- body text for the notice
PRIMARY KEY (`module`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `old_issues`;
-CREATE TABLE `old_issues` (
- `borrowernumber` int(11) default NULL,
- `itemnumber` int(11) default NULL,
- `date_due` date default NULL,
- `branchcode` varchar(10) default NULL,
+CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned
+ `borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to
+ `itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out
+ `date_due` date default NULL, -- date the item is due (yyyy-mm-dd)
+ `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
`issuingbranch` varchar(18) default NULL,
- `returndate` date default NULL,
- `lastreneweddate` date default NULL,
+ `returndate` date default NULL, -- date the item was returned
+ `lastreneweddate` date default NULL, -- date the item was last renewed
`return` varchar(4) default NULL,
- `renewals` tinyint(4) default NULL,
- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
- `issuedate` date default NULL,
+ `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
+ `issuedate` date default NULL, -- date the item was checked out or issued
KEY `old_issuesborridx` (`borrowernumber`),
KEY `old_issuesitemidx` (`itemnumber`),
KEY `old_bordate` (`borrowernumber`,`timestamp`),
--
DROP TABLE IF EXISTS `opac_news`;
-CREATE TABLE `opac_news` (
- `idnew` int(10) unsigned NOT NULL auto_increment,
- `title` varchar(250) NOT NULL default '',
- `new` text NOT NULL,
- `lang` varchar(25) NOT NULL default '',
- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
- `expirationdate` date default NULL,
- `number` int(11) default NULL,
+CREATE TABLE `opac_news` ( -- data from the news tool
+ `idnew` int(10) unsigned NOT NULL auto_increment, -- unique identifier for the news article
+ `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;
--
DROP TABLE IF EXISTS `overduerules`;
-CREATE TABLE `overduerules` (
- `branchcode` varchar(10) NOT NULL default '',
- `categorycode` varchar(10) NOT NULL default '',
- `delay1` int(4) default NULL,
- `letter1` varchar(20) default NULL,
- `debarred1` varchar(1) default 0,
- `delay2` int(4) default NULL,
- `debarred2` varchar(1) default 0,
- `letter2` varchar(20) default NULL,
- `delay3` int(4) default NULL,
- `letter3` varchar(20) default NULL,
- `debarred3` int(1) default 0,
+CREATE TABLE `overduerules` ( -- overdue notice status and triggers
+ `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table to define which branch this rule is for (if blank it's all libraries)
+ `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
+ `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
+ `letter1` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the first notice
+ `debarred1` varchar(1) default 0, -- is the patron restricted when the first notice is sent (1 for yes, 0 for no)
+ `delay2` int(4) default NULL, -- number of days after the item is overdue that the second notice is sent
+ `debarred2` varchar(1) default 0, -- is the patron restricted when the second notice is sent (1 for yes, 0 for no)
+ `letter2` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the second notice
+ `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
+ `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;
--
DROP TABLE IF EXISTS `statistics`;
-CREATE TABLE `statistics` (
- `datetime` datetime default NULL,
- `branch` varchar(10) default NULL,
- `proccode` varchar(4) default NULL,
- `value` double(16,4) default NULL,
- `type` varchar(16) default NULL,
+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
+ `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,
- `itemnumber` int(11) default NULL,
- `itemtype` varchar(10) default NULL,
- `borrowernumber` int(11) default NULL,
+ `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,
KEY `timeidx` (`datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `services_throttle`
+--
+
DROP TABLE IF EXISTS `services_throttle`;
CREATE TABLE `services_throttle` (
`service_type` varchar(10) NOT NULL default '',
PRIMARY KEY (`service_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `language_subtag_registry`
-- http://www.w3.org/International/articles/language-tags/
-
-- RFC4646
+--
+
DROP TABLE IF EXISTS language_subtag_registry;
CREATE TABLE language_subtag_registry (
subtag varchar(25),
KEY `subtag` (`subtag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `language_rfc4646_to_iso639`
-- TODO: add suppress_scripts
-- this maps three letter codes defined in iso639.2 back to their
-- two letter equivilents in rfc4646 (LOC maintains iso639+)
+--
+
DROP TABLE IF EXISTS language_rfc4646_to_iso639;
CREATE TABLE language_rfc4646_to_iso639 (
rfc4646_subtag varchar(25),
KEY `rfc4646_subtag` (`rfc4646_subtag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `language_descriptions`
+--
+
DROP TABLE IF EXISTS language_descriptions;
CREATE TABLE language_descriptions (
subtag varchar(25),
KEY `subtag_type_lang` (`subtag`, `type`, `lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `language_script_bidi`
-- bi-directional support, keyed by script subcode
+--
+
DROP TABLE IF EXISTS language_script_bidi;
CREATE TABLE language_script_bidi (
rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
KEY `rfc4646_subtag` (`rfc4646_subtag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `language_script_mapping`
-- TODO: need to map language subtags to script subtags for detection
-- of bidi when script is not specified (like ar, he)
+--
+
DROP TABLE IF EXISTS language_script_mapping;
CREATE TABLE language_script_mapping (
language_subtag varchar(25),
KEY `language_subtag` (`language_subtag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `permissions`
+--
+
DROP TABLE IF EXISTS `permissions`;
CREATE TABLE `permissions` (
`module_bit` int(11) NOT NULL DEFAULT 0,
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `serialitems`
+--
+
DROP TABLE IF EXISTS `serialitems`;
CREATE TABLE `serialitems` (
`itemnumber` int(11) NOT NULL,
CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `user_permissions`
+--
+
DROP TABLE IF EXISTS `user_permissions`;
CREATE TABLE `user_permissions` (
`borrowernumber` int(11) NOT NULL DEFAULT 0,
projects / koha_gimpoz / blobdiff