X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=336aaa1994165be8b368c05ac80967c6eb9cb4b6;hb=b4fb5d4095665f1543da7bd714584524e6311f8b;hp=e9edf2541911828a9cac2046b344c71f75ebdb76;hpb=8f21144fb154b68747d004eecca53fe84a020f22;p=koha_gimpoz diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e9edf25419..336aaa1994 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -95,13 +95,13 @@ CREATE TABLE `auth_types` ( -- DROP TABLE IF EXISTS `authorised_values`; -CREATE TABLE `authorised_values` ( - `id` int(11) NOT NULL auto_increment, - `category` varchar(10) NOT NULL default '', - `authorised_value` varchar(80) NOT NULL default '', - `lib` varchar(80) default NULL, - `lib_opac` VARCHAR(80) default NULL, - `imageurl` varchar(200) default NULL, +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(10) 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(80) default NULL, -- authorized value description as printed in the staff client + `lib_opac` VARCHAR(80) default NULL, -- authorized value description as printed in the OPAC + `imageurl` varchar(200) default NULL, -- authorized value URL PRIMARY KEY (`id`), KEY `name` (`category`), KEY `lib` (`lib`), @@ -113,19 +113,19 @@ CREATE TABLE `authorised_values` ( -- DROP TABLE IF EXISTS `biblio`; -CREATE TABLE `biblio` ( - `biblionumber` int(11) NOT NULL auto_increment, - `frameworkcode` varchar(4) NOT NULL default '', - `author` mediumtext, - `title` mediumtext, - `unititle` mediumtext, - `notes` mediumtext, - `serial` tinyint(1) default NULL, +CREATE TABLE `biblio` ( -- table that stores bibliographic information + `biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record + `frameworkcode` varchar(4) NOT NULL default '', -- foriegn key from the biblio_framework table to identify which framework was used in cataloging this record + `author` mediumtext, -- statement of responsibility from MARC record (100$a in MARC21) + `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 `seriestitle` mediumtext, - `copyrightdate` smallint(6) default NULL, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `datecreated` DATE NOT NULL, - `abstract` 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 + `datecreated` DATE NOT NULL, -- the date this record was added to Koha + `abstract` mediumtext, -- summary from the MARC record (520$a in MARC21) PRIMARY KEY (`biblionumber`), KEY `blbnoidx` (`biblionumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -146,39 +146,39 @@ CREATE TABLE `biblio_framework` ( -- DROP TABLE IF EXISTS `biblioitems`; -CREATE TABLE `biblioitems` ( - `biblioitemnumber` int(11) NOT NULL auto_increment, - `biblionumber` int(11) NOT NULL default 0, +CREATE TABLE `biblioitems` ( -- information related to bibliographic records in Koha + `biblioitemnumber` int(11) NOT NULL auto_increment, -- primary key, unique identifier assigned by Koha + `biblionumber` int(11) NOT NULL default 0, -- foreign key linking this table to the biblio table `volume` mediumtext, `number` mediumtext, - `itemtype` varchar(10) default NULL, - `isbn` varchar(30) default NULL, - `issn` varchar(9) default NULL, + `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) `publicationyear` text, - `publishercode` varchar(255) default NULL, + `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b) `volumedate` date default NULL, - `volumedesc` text, + `volumedesc` text, -- volume information (MARC21 362$a) `collectiontitle` mediumtext default NULL, `collectionissn` text default NULL, `collectionvolume` mediumtext default NULL, `editionstatement` text default NULL, `editionresponsibility` text default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `illus` varchar(255) default NULL, - `pages` varchar(255) default NULL, + `illus` varchar(255) default NULL, -- illustrations (MARC21 300$b) + `pages` varchar(255) default NULL, -- number of pages (MARC21 300$c) `notes` mediumtext, - `size` varchar(255) default NULL, - `place` varchar(255) default NULL, - `lccn` varchar(25) default NULL, - `marc` longblob, - `url` varchar(255) default NULL, - `cn_source` varchar(10) default NULL, + `size` varchar(255) default NULL, -- material size (MARC21 300$c) + `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) + `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, `totalissues` int(10), - `marcxml` longtext NOT NULL, + `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML PRIMARY KEY (`biblioitemnumber`), KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), @@ -193,73 +193,74 @@ CREATE TABLE `biblioitems` ( -- DROP TABLE IF EXISTS `borrowers`; -CREATE TABLE `borrowers` ( - `borrowernumber` int(11) NOT NULL auto_increment, - `cardnumber` varchar(16) default NULL, - `surname` mediumtext NOT NULL, - `firstname` text, - `title` mediumtext, - `othernames` mediumtext, - `initials` text, - `streetnumber` varchar(10) default NULL, - `streettype` varchar(50) default NULL, - `address` mediumtext NOT NULL, - `address2` text, - `city` mediumtext NOT NULL, - `state` text default NULL, - `zipcode` varchar(25) default NULL, - `country` text, - `email` mediumtext, - `phone` text, - `mobile` varchar(50) default NULL, - `fax` mediumtext, - `emailpro` text, - `phonepro` text, - `B_streetnumber` varchar(10) default NULL, - `B_streettype` varchar(50) default NULL, - `B_address` varchar(100) default NULL, - `B_address2` text default NULL, - `B_city` mediumtext, - `B_state` text default NULL, - `B_zipcode` varchar(25) default NULL, - `B_country` text, - `B_email` text, - `B_phone` mediumtext, - `dateofbirth` date default NULL, - `branchcode` varchar(10) NOT NULL default '', - `categorycode` varchar(10) NOT NULL default '', - `dateenrolled` date default NULL, - `dateexpiry` date default NULL, - `gonenoaddress` tinyint(1) default NULL, - `lost` tinyint(1) default NULL, - `debarred` tinyint(1) default NULL, - `contactname` mediumtext, - `contactfirstname` text, - `contacttitle` text, - `guarantorid` int(11) default NULL, - `borrowernotes` mediumtext, - `relationship` varchar(100) default NULL, - `ethnicity` varchar(50) default NULL, - `ethnotes` varchar(255) default NULL, - `sex` varchar(1) default NULL, - `password` varchar(30) default NULL, - `flags` int(11) default NULL, - `userid` varchar(30) default NULL, - `opacnote` mediumtext, - `contactnote` varchar(255) default NULL, - `sort1` varchar(80) default NULL, - `sort2` varchar(80) default NULL, - `altcontactfirstname` varchar(255) default NULL, - `altcontactsurname` varchar(255) default NULL, - `altcontactaddress1` varchar(255) default NULL, - `altcontactaddress2` varchar(255) default NULL, - `altcontactaddress3` varchar(255) default NULL, - `altcontactstate` text default NULL, - `altcontactzipcode` varchar(50) default NULL, - `altcontactcountry` text default NULL, - `altcontactphone` varchar(50) default NULL, - `smsalertnumber` varchar(50) default NULL, - `privacy` integer(11) DEFAULT '1' NOT NULL, +CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members + `borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers + `cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers + `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname) + `firstname` text, -- patron/borrower's first name + `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs. + `othernames` mediumtext, -- any other names associated with the patron/borrower + `initials` text, -- initials for your patron/borrower + `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address + `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address + `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address + `address2` text, -- the second address line for your patron/borrower's primary address + `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address + `state` text default NULL, -- the state or province for your patron/borrower's primary address + `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address + `country` text, -- the country for your patron/borrower's primary address + `email` mediumtext, -- the primary email address for your patron/borrower's primary address + `phone` text, -- the primary phone number for your patron/borrower's primary address + `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address + `fax` mediumtext, -- the fax number for your patron/borrower's primary address + `emailpro` text, -- the secondary email addres for your patron/borrower's primary address + `phonepro` text, -- the secondary phone number for your patron/borrower's primary address + `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address + `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address + `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address + `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address + `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address + `B_state` text default NULL, -- the state for your patron/borrower's alternate address + `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address + `B_country` text, -- the country for your patron/borrower's alternate address + `B_email` text, -- the patron/borrower's alternate email address + `B_phone` mediumtext, -- the patron/borrower's alternate phone number + `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD) + `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch + `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category + `dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD) + `dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD) + `gonenoaddress` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having an unconfirmed address + `lost` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having lost their card + `debarred` date default NULL, -- until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYY-MM-DD) + `debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron + `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name + `contactfirstname` text, -- used for children to include first name of guarentor + `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor + `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations + `borrowernotes` mediumtext, -- a note on the patron/borroewr's account that is only visible in the staff client + `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor + `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 + `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions + `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in + `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client + `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address + `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library + `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library + `altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower + `altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower + `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower + `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower + `altcontactaddress3` varchar(255) default NULL, -- the third address line for the alternate contact for the patron/borrower + `altcontactstate` text default NULL, -- the city and state for the alternate contact for the patron/borrower + `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower + `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower + `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower + `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) + `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history UNIQUE KEY `cardnumber` (`cardnumber`), PRIMARY KEY `borrowernumber` (`borrowernumber`), KEY `categorycode` (`categorycode`), @@ -275,15 +276,16 @@ CREATE TABLE `borrowers` ( -- 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 + `display_checkout` tinyint(1) NOT NULL default 0,-- defines if this field displays in checkout screens PRIMARY KEY (`code`), KEY `auth_val_cat_idx` (`authorised_value_category`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -293,11 +295,11 @@ CREATE TABLE `borrower_attribute_types` ( -- 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`) @@ -306,6 +308,10 @@ CREATE TABLE `borrower_attributes` ( 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, @@ -324,11 +330,11 @@ CREATE TABLE `branch_item_rules` ( -- 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; @@ -337,24 +343,24 @@ CREATE TABLE `branchcategories` ( -- 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; @@ -363,9 +369,9 @@ CREATE TABLE `branches` ( -- 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`), @@ -411,21 +417,21 @@ CREATE TABLE `browser` ( -- DROP TABLE IF EXISTS `categories`; -CREATE TABLE `categories` ( - `categorycode` varchar(10) NOT NULL default '', - `description` mediumtext, - `enrolmentperiod` smallint(6) default NULL, - `enrolmentperioddate` DATE NULL DEFAULT NULL, - `upperagelimit` smallint(6) default NULL, +CREATE TABLE `categories` ( -- this table shows information related to Koha patron categories + `categorycode` varchar(10) NOT NULL default '', -- unique primary key used to idenfity the patron category + `description` mediumtext, -- description of the patron category + `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, - `finetype` varchar(30) default NULL, + `finetype` varchar(30) default NULL, -- unused in Koha `bulk` tinyint(1) default NULL, - `enrolmentfee` decimal(28,6) default NULL, - `overduenoticerequired` tinyint(1) default NULL, - `issuelimit` smallint(6) default NULL, - `reservefee` decimal(28,6) default NULL, - `hidelostitems` tinyint(1) NOT NULL default '0', - `category_type` varchar(1) NOT NULL default 'A', + `enrolmentfee` decimal(28,6) default NULL, -- enrollment fee for the patron + `overduenoticerequired` tinyint(1) default NULL, -- are overdue notices sent to this patron category (1 for yes, 0 for no) + `issuelimit` smallint(6) default NULL, -- unused in Koha + `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) PRIMARY KEY (`categorycode`), UNIQUE KEY `categorycode` (`categorycode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -433,6 +439,7 @@ CREATE TABLE `categories` ( -- -- Table: collections -- +DROP TABLE IF EXISTS collections; CREATE TABLE collections ( colId integer(11) NOT NULL auto_increment, colTitle varchar(100) NOT NULL DEFAULT '', @@ -444,6 +451,7 @@ CREATE TABLE collections ( -- -- Table: collections_tracking -- +DROP TABLE IF EXISTS collections_tracking; CREATE TABLE collections_tracking ( ctId integer(11) NOT NULL auto_increment, colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId', @@ -523,12 +531,12 @@ CREATE TABLE `default_circ_rules` ( -- 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; @@ -580,19 +588,19 @@ CREATE TABLE `currency` ( -- DROP TABLE IF EXISTS `deletedbiblio`; -CREATE TABLE `deletedbiblio` ( - `biblionumber` int(11) NOT NULL default 0, - `frameworkcode` varchar(4) NOT NULL default '', - `author` mediumtext, - `title` mediumtext, - `unititle` mediumtext, - `notes` mediumtext, - `serial` tinyint(1) default NULL, +CREATE TABLE `deletedbiblio` ( -- stores information about bibliographic records that have been deleted + `biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record + `frameworkcode` varchar(4) NOT NULL default '', -- foriegn key from the biblio_framework table to identify which framework was used in cataloging this record + `author` mediumtext, -- statement of responsibility from MARC record (100$a in MARC21) + `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 `seriestitle` mediumtext, - `copyrightdate` smallint(6) default NULL, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `datecreated` DATE NOT NULL, - `abstract` 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 + `datecreated` DATE NOT NULL, -- the date this record was added to Koha + `abstract` mediumtext, -- summary from the MARC record (520$a in MARC21) PRIMARY KEY (`biblionumber`), KEY `blbnoidx` (`biblionumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -602,39 +610,39 @@ CREATE TABLE `deletedbiblio` ( -- DROP TABLE IF EXISTS `deletedbiblioitems`; -CREATE TABLE `deletedbiblioitems` ( - `biblioitemnumber` int(11) NOT NULL default 0, - `biblionumber` int(11) NOT NULL default 0, +CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records that have been deleted + `biblioitemnumber` int(11) NOT NULL default 0, -- primary key, unique identifier assigned by Koha + `biblionumber` int(11) NOT NULL default 0, -- foreign key linking this table to the biblio table `volume` mediumtext, `number` mediumtext, - `itemtype` varchar(10) default NULL, - `isbn` varchar(30) default NULL, - `issn` varchar(9) default NULL, + `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) `publicationyear` text, - `publishercode` varchar(255) default NULL, + `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b) `volumedate` date default NULL, - `volumedesc` text, + `volumedesc` text, -- volume information (MARC21 362$a) `collectiontitle` mediumtext default NULL, `collectionissn` text default NULL, `collectionvolume` mediumtext default NULL, `editionstatement` text default NULL, `editionresponsibility` text default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `illus` varchar(255) default NULL, - `pages` varchar(255) default NULL, + `illus` varchar(255) default NULL, -- illustrations (MARC21 300$b) + `pages` varchar(255) default NULL, -- number of pages (MARC21 300$c) `notes` mediumtext, - `size` varchar(255) default NULL, - `place` varchar(255) default NULL, - `lccn` varchar(25) default NULL, - `marc` longblob, - `url` varchar(255) default NULL, - `cn_source` varchar(10) default NULL, + `size` varchar(255) default NULL, -- material size (MARC21 300$c) + `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) + `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, `totalissues` int(10), - `marcxml` longtext NOT NULL, + `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML PRIMARY KEY (`biblioitemnumber`), KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), @@ -647,74 +655,75 @@ CREATE TABLE `deletedbiblioitems` ( -- DROP TABLE IF EXISTS `deletedborrowers`; -CREATE TABLE `deletedborrowers` ( - `borrowernumber` int(11) NOT NULL default 0, - `cardnumber` varchar(16) NOT NULL default '', - `surname` mediumtext NOT NULL, - `firstname` text, - `title` mediumtext, - `othernames` mediumtext, - `initials` text, - `streetnumber` varchar(10) default NULL, - `streettype` varchar(50) default NULL, - `address` mediumtext NOT NULL, - `address2` text, - `city` mediumtext NOT NULL, - `state` text default NULL, - `zipcode` varchar(25) default NULL, - `country` text, - `email` mediumtext, - `phone` text, - `mobile` varchar(50) default NULL, - `fax` mediumtext, - `emailpro` text, - `phonepro` text, - `B_streetnumber` varchar(10) default NULL, - `B_streettype` varchar(50) default NULL, - `B_address` varchar(100) default NULL, - `B_address2` text default NULL, - `B_city` mediumtext, - `B_state` text default NULL, - `B_zipcode` varchar(25) default NULL, - `B_country` text, - `B_email` text, - `B_phone` mediumtext, - `dateofbirth` date default NULL, - `branchcode` varchar(10) NOT NULL default '', - `categorycode` varchar(10) default NULL, - `dateenrolled` date default NULL, - `dateexpiry` date default NULL, - `gonenoaddress` tinyint(1) default NULL, - `lost` tinyint(1) default NULL, - `debarred` tinyint(1) default NULL, - `contactname` mediumtext, - `contactfirstname` text, - `contacttitle` text, - `guarantorid` int(11) default NULL, - `borrowernotes` mediumtext, - `relationship` varchar(100) default NULL, - `ethnicity` varchar(50) default NULL, - `ethnotes` varchar(255) default NULL, - `sex` varchar(1) default NULL, - `password` varchar(30) default NULL, - `flags` int(11) default NULL, - `userid` varchar(30) default NULL, - `opacnote` mediumtext, - `contactnote` varchar(255) default NULL, - `sort1` varchar(80) default NULL, - `sort2` varchar(80) default NULL, - `altcontactfirstname` varchar(255) default NULL, - `altcontactsurname` varchar(255) default NULL, - `altcontactaddress1` varchar(255) default NULL, - `altcontactaddress2` varchar(255) default NULL, - `altcontactaddress3` varchar(255) default NULL, - `altcontactstate` text default NULL, - `altcontactzipcode` varchar(50) default NULL, - `altcontactcountry` text default NULL, - `altcontactphone` varchar(50) default NULL, - `smsalertnumber` varchar(50) default NULL, - `privacy` integer(11) DEFAULT '1' NOT NULL, - KEY `borrowernumber` (`borrowernumber`), +CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrowers you have deleted + `borrowernumber` int(11) NOT NULL default 0, -- primary key, Koha assigned ID number for patrons/borrowers + `cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers + `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname) + `firstname` text, -- patron/borrower's first name + `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs. + `othernames` mediumtext, -- any other names associated with the patron/borrower + `initials` text, -- initials for your patron/borrower + `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address + `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address + `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address + `address2` text, -- the second address line for your patron/borrower's primary address + `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address + `state` text default NULL, -- the state or province for your patron/borrower's primary address + `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address + `country` text, -- the country for your patron/borrower's primary address + `email` mediumtext, -- the primary email address for your patron/borrower's primary address + `phone` text, -- the primary phone number for your patron/borrower's primary address + `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address + `fax` mediumtext, -- the fax number for your patron/borrower's primary address + `emailpro` text, -- the secondary email addres for your patron/borrower's primary address + `phonepro` text, -- the secondary phone number for your patron/borrower's primary address + `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address + `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address + `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address + `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address + `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address + `B_state` text default NULL, -- the state for your patron/borrower's alternate address + `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address + `B_country` text, -- the country for your patron/borrower's alternate address + `B_email` text, -- the patron/borrower's alternate email address + `B_phone` mediumtext, -- the patron/borrower's alternate phone number + `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD) + `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch + `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category + `dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD) + `dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD) + `gonenoaddress` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having an unconfirmed address + `lost` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having lost their card + `debarred` date default NULL, -- until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYY-MM-DD) + `debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of patron + `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name + `contactfirstname` text, -- used for children to include first name of guarentor + `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor + `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations + `borrowernotes` mediumtext, -- a note on the patron/borroewr's account that is only visible in the staff client + `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor + `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 + `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions + `userid` varchar(30) default NULL, -- patron/borrower's opac and/or staff client log in + `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client + `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address + `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library + `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library + `altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower + `altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower + `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower + `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower + `altcontactaddress3` varchar(255) default NULL, -- the third address line for the alternate contact for the patron/borrower + `altcontactstate` text default NULL, -- the city and state for the alternate contact for the patron/borrower + `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower + `altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower + `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower + `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on) + `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; @@ -724,46 +733,46 @@ CREATE TABLE `deletedborrowers` ( DROP TABLE IF EXISTS `deleteditems`; CREATE TABLE `deleteditems` ( - `itemnumber` int(11) NOT NULL default 0, - `biblionumber` int(11) NOT NULL default 0, - `biblioitemnumber` int(11) NOT NULL default 0, - `barcode` varchar(20) default NULL, - `dateaccessioned` date default NULL, - `booksellerid` mediumtext default NULL, - `homebranch` varchar(10) default NULL, - `price` decimal(8,2) default NULL, - `replacementprice` decimal(8,2) default NULL, - `replacementpricedate` date default NULL, - `datelastborrowed` date default NULL, - `datelastseen` date default NULL, + `itemnumber` int(11) NOT NULL default 0, -- primary key and unique identifier added by Koha + `biblionumber` int(11) NOT NULL default 0, -- foreign key from biblio table used to link this item to the right bib record + `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information + `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p) + `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d) + `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e) + `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a) + `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g) + `replacementprice` decimal(8,2) default NULL, -- cost the library charges to replace the item if it has been marked lost (MARC21 952$v) + `replacementpricedate` date default NULL, -- the date the price is effective from (MARC21 952$w) + `datelastborrowed` date default NULL, -- the date the item was last checked out + `datelastseen` date default NULL, -- the date the item was last see (usually the last time the barcode was scanned or inventory was done) `stack` tinyint(1) default NULL, - `notforloan` tinyint(1) NOT NULL default 0, - `damaged` tinyint(1) NOT NULL default 0, - `itemlost` tinyint(1) NOT NULL default 0, - `wthdrawn` tinyint(1) NOT NULL default 0, - `itemcallnumber` varchar(255) default NULL, - `issues` smallint(6) default NULL, - `renewals` smallint(6) default NULL, - `reserves` smallint(6) default NULL, - `restricted` tinyint(1) default NULL, - `itemnotes` mediumtext, - `holdingbranch` varchar(10) default NULL, + `notforloan` tinyint(1) NOT NULL default 0, -- authorized value defining why this item is not for loan (MARC21 952$7) + `damaged` tinyint(1) NOT NULL default 0, -- authorized value defining this item as damaged (MARC21 952$4) + `itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1) + `wthdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0) + `itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o) + `issues` smallint(6) default NULL, -- number of times this item has been checked out + `renewals` smallint(6) default NULL, -- number of times this item has been renewed + `reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved + `restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5) + `itemnotes` mediumtext, -- public notes on this item (MARC21 952$x) + `holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b) `paidfor` mediumtext, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `location` varchar(80) default NULL, - `permanent_location` varchar(80) default NULL, - `onloan` date default NULL, - `cn_source` varchar(10) default NULL, - `cn_sort` varchar(30) default NULL, - `ccode` varchar(10) default NULL, - `materials` varchar(10) default NULL, - `uri` varchar(255) default NULL, - `itype` varchar(10) default NULL, - `more_subfields_xml` longtext default NULL, - `enumchron` text default NULL, - `copynumber` varchar(32) default NULL, - `stocknumber` varchar(32) default NULL, - `marc` longblob, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered + `location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c) + `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 this item is currently checked out (1 for yes, 0 for no) + `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 + `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) + `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`), @@ -937,23 +946,22 @@ CREATE TABLE `import_items` ( -- DROP TABLE IF EXISTS `issues`; -CREATE TABLE `issues` ( - `borrowernumber` int(11) default NULL, - `itemnumber` int(11) default NULL, - `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, + `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 `issuesitemidx` (`itemnumber`), KEY `bordate` (`borrowernumber`,`timestamp`), - CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL + 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; -- @@ -990,46 +998,46 @@ CREATE TABLE `issuingrules` ( -- DROP TABLE IF EXISTS `items`; -CREATE TABLE `items` ( - `itemnumber` int(11) NOT NULL auto_increment, - `biblionumber` int(11) NOT NULL default 0, - `biblioitemnumber` int(11) NOT NULL default 0, - `barcode` varchar(20) default NULL, - `dateaccessioned` date default NULL, - `booksellerid` mediumtext default NULL, - `homebranch` varchar(10) default NULL, - `price` decimal(8,2) default NULL, - `replacementprice` decimal(8,2) default NULL, - `replacementpricedate` date default NULL, - `datelastborrowed` date default NULL, - `datelastseen` date default NULL, +CREATE TABLE `items` ( -- holdings/item information + `itemnumber` int(11) NOT NULL auto_increment, -- primary key and unique identifier added by Koha + `biblionumber` int(11) NOT NULL default 0, -- foreign key from biblio table used to link this item to the right bib record + `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information + `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p) + `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d) + `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e) + `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a) + `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g) + `replacementprice` decimal(8,2) default NULL, -- cost the library charges to replace the item if it has been marked lost (MARC21 952$v) + `replacementpricedate` date default NULL, -- the date the price is effective from (MARC21 952$w) + `datelastborrowed` date default NULL, -- the date the item was last checked out/issued + `datelastseen` date default NULL, -- the date the item was last see (usually the last time the barcode was scanned or inventory was done) `stack` tinyint(1) default NULL, - `notforloan` tinyint(1) NOT NULL default 0, - `damaged` tinyint(1) NOT NULL default 0, - `itemlost` tinyint(1) NOT NULL default 0, - `wthdrawn` tinyint(1) NOT NULL default 0, - `itemcallnumber` varchar(255) default NULL, - `issues` smallint(6) default NULL, - `renewals` smallint(6) default NULL, - `reserves` smallint(6) default NULL, - `restricted` tinyint(1) default NULL, - `itemnotes` mediumtext, - `holdingbranch` varchar(10) default NULL, + `notforloan` tinyint(1) NOT NULL default 0, -- authorized value defining why this item is not for loan (MARC21 952$7) + `damaged` tinyint(1) NOT NULL default 0, -- authorized value defining this item as damaged (MARC21 952$4) + `itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1) + `wthdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0) + `itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o) + `issues` smallint(6) default NULL, -- number of times this item has been checked out/issued + `renewals` smallint(6) default NULL, -- number of times this item has been renewed + `reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved + `restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5) + `itemnotes` mediumtext, -- public notes on this item (MARC21 952$x) + `holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b) `paidfor` mediumtext, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `location` varchar(80) default NULL, - `permanent_location` varchar(80) default NULL, - `onloan` date default NULL, - `cn_source` varchar(10) default NULL, - `cn_sort` varchar(30) default NULL, - `ccode` varchar(10) default NULL, - `materials` varchar(10) default NULL, - `uri` varchar(255) default NULL, - `itype` varchar(10) default NULL, - `more_subfields_xml` longtext default NULL, - `enumchron` text default NULL, - `copynumber` varchar(32) default NULL, - `stocknumber` varchar(32) default NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered + `location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c) + `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 this item is currently checked out (1 for yes, 0 for no) + `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 + `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) + `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) PRIMARY KEY (`itemnumber`), UNIQUE KEY `itembarcodeidx` (`barcode`), KEY `itemstocknumberidx` (`stocknumber`), @@ -1047,13 +1055,13 @@ CREATE TABLE `items` ( -- 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; @@ -1157,12 +1165,12 @@ CREATE TABLE `creator_templates` ( -- 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; @@ -1305,6 +1313,17 @@ CREATE TABLE `matchchecks` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table structure for table `need_merge_authorities` +-- + +DROP TABLE IF EXISTS `need_merge_authorities`; +CREATE TABLE `need_merge_authorities` ( -- keeping track of authority records still to be merged by merge_authority cron job (used only if pref dontmerge is ON) + `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; + +-- -- Table structure for table `notifys` -- @@ -1338,18 +1357,18 @@ CREATE TABLE `nozebra` ( -- 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`), @@ -1363,22 +1382,22 @@ CREATE TABLE `old_issues` ( -- Table structure for table `old_reserves` -- DROP TABLE IF EXISTS `old_reserves`; -CREATE TABLE `old_reserves` ( - `borrowernumber` int(11) default NULL, - `reservedate` date default NULL, - `biblionumber` int(11) default NULL, +CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled) + `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for + `reservedate` date default NULL, -- the date the hold was places + `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on `constrainttype` varchar(1) default NULL, - `branchcode` varchar(10) default NULL, - `notificationdate` date default NULL, - `reminderdate` date default NULL, - `cancellationdate` date default NULL, - `reservenotes` mediumtext, - `priority` smallint(6) default NULL, - `found` varchar(1) default NULL, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `itemnumber` int(11) default NULL, - `waitingdate` date default NULL, - `expirationdate` DATE DEFAULT NULL, + `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at + `notificationdate` date default NULL, -- currently unused + `reminderdate` date default NULL, -- currently unused + `cancellationdate` date default NULL, -- the date this hold was cancelled + `reservenotes` mediumtext, -- notes related to this hold + `priority` smallint(6) default NULL, -- where in the queue the patron sits + `found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated + `itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with + `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library + `expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) `lowestPriority` tinyint(1) NOT NULL, KEY `old_reserves_borrowernumber` (`borrowernumber`), KEY `old_reserves_biblionumber` (`biblionumber`), @@ -1397,14 +1416,14 @@ CREATE TABLE `old_reserves` ( -- 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; @@ -1413,18 +1432,18 @@ CREATE TABLE `opac_news` ( -- 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; @@ -1493,14 +1512,14 @@ CREATE TABLE `printers_profile` ( -- DROP TABLE IF EXISTS `repeatable_holidays`; -CREATE TABLE `repeatable_holidays` ( - `id` int(11) NOT NULL auto_increment, - `branchcode` varchar(10) NOT NULL default '', - `weekday` smallint(6) default NULL, - `day` smallint(6) default NULL, - `month` smallint(6) default NULL, - `title` varchar(50) NOT NULL default '', - `description` text NOT NULL, +CREATE TABLE `repeatable_holidays` ( -- information for the days the library is closed + `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha + `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, defines which branch this closing is for + `weekday` smallint(6) default NULL, -- day of the week (0=Sunday, 1=Monday, etc) this closing is repeated on + `day` smallint(6) default NULL, -- day of the month this closing is on + `month` smallint(6) default NULL, -- month this closing is in + `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; @@ -1509,14 +1528,14 @@ CREATE TABLE `repeatable_holidays` ( -- DROP TABLE IF EXISTS `reports_dictionary`; -CREATE TABLE reports_dictionary ( - `id` int(11) NOT NULL auto_increment, - `name` varchar(255) default NULL, - `description` text, - `date_created` datetime default NULL, - `date_modified` datetime default NULL, - `saved_sql` text, - `area` int(11) default NULL, +CREATE TABLE reports_dictionary ( -- definitions (or snippets of SQL) stored for use in reports + `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha + `name` varchar(255) default NULL, -- name for this definition + `description` text, -- description for this definition + `date_created` datetime default NULL, -- date and time this definition was created + `date_modified` datetime default NULL, -- date and time this definition was last modified + `saved_sql` text, -- SQL snippet for us in reports + `area` int(11) default NULL, -- Koha module this definition is for (1 = Circulation, 2 = Catalog, 3 = Patrons, 4 = Acquistions, 5 = Accounts) PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1538,23 +1557,24 @@ CREATE TABLE `reserveconstraints` ( -- DROP TABLE IF EXISTS `reserves`; -CREATE TABLE `reserves` ( - `borrowernumber` int(11) NOT NULL default 0, - `reservedate` date default NULL, - `biblionumber` int(11) NOT NULL default 0, +CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha + `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for + `reservedate` date default NULL, -- the date the hold was places + `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on `constrainttype` varchar(1) default NULL, - `branchcode` varchar(10) default NULL, - `notificationdate` date default NULL, - `reminderdate` date default NULL, - `cancellationdate` date default NULL, - `reservenotes` mediumtext, - `priority` smallint(6) default NULL, - `found` varchar(1) default NULL, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `itemnumber` int(11) default NULL, - `waitingdate` date default NULL, - `expirationdate` DATE DEFAULT NULL, + `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at + `notificationdate` date default NULL, -- currently unused + `reminderdate` date default NULL, -- currently unused + `cancellationdate` date default NULL, -- the date this hold was cancelled + `reservenotes` mediumtext, -- notes related to this hold + `priority` smallint(6) default NULL, -- where in the queue the patron sits + `found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated + `itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with + `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library + `expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) `lowestPriority` tinyint(1) NOT NULL, + KEY priorityfoundidx (priority,found), KEY `borrowernumber` (`borrowernumber`), KEY `biblionumber` (`biblionumber`), KEY `itemnumber` (`itemnumber`), @@ -1570,13 +1590,13 @@ CREATE TABLE `reserves` ( -- DROP TABLE IF EXISTS `reviews`; -CREATE TABLE `reviews` ( - `reviewid` int(11) NOT NULL auto_increment, - `borrowernumber` int(11) default NULL, - `biblionumber` int(11) default NULL, - `review` text, - `approved` tinyint(4) default NULL, - `datereviewed` datetime default NULL, +CREATE TABLE `reviews` ( -- patron opac comments + `reviewid` int(11) NOT NULL auto_increment, -- unique identifier for this comment + `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron left this comment + `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bibliographic record this comment is for + `review` text, -- the body of the comment + `approved` tinyint(4) default NULL, -- whether this comment has been approved by a librarian (1 for yes, 0 for no) + `datereviewed` datetime default NULL, -- the date the comment was left PRIMARY KEY (`reviewid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1585,9 +1605,9 @@ CREATE TABLE `reviews` ( -- DROP TABLE IF EXISTS `roadtype`; -CREATE TABLE `roadtype` ( - `roadtypeid` int(11) NOT NULL auto_increment, - `road_type` varchar(100) NOT NULL default '', +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; @@ -1678,15 +1698,15 @@ CREATE TABLE sessions ( -- DROP TABLE IF EXISTS `special_holidays`; -CREATE TABLE `special_holidays` ( - `id` int(11) NOT NULL auto_increment, - `branchcode` varchar(10) NOT NULL default '', - `day` smallint(6) NOT NULL default 0, - `month` smallint(6) NOT NULL default 0, - `year` smallint(6) NOT NULL default 0, - `isexception` smallint(1) NOT NULL default 1, - `title` varchar(50) NOT NULL default '', - `description` text NOT NULL, +CREATE TABLE `special_holidays` ( -- non repeatable holidays/library closings + `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha + `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, defines which branch this closing is for + `day` smallint(6) NOT NULL default 0, -- day of the month this closing is on + `month` smallint(6) NOT NULL default 0, -- month this closing is in + `year` smallint(6) NOT NULL default 0, -- year this closing is in + `isexception` smallint(1) NOT NULL default 1, -- is this a holiday exception to a repeatable holiday (1 for yes, 0 for no) + `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; @@ -1695,17 +1715,17 @@ CREATE TABLE `special_holidays` ( -- 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; @@ -1802,11 +1822,11 @@ CREATE TABLE `subscriptionhistory` ( -- DROP TABLE IF EXISTS `subscriptionroutinglist`; -CREATE TABLE `subscriptionroutinglist` ( - `routingid` int(11) NOT NULL auto_increment, - `borrowernumber` int(11) NOT NULL, - `ranking` int(11) default NULL, - `subscriptionid` int(11) NOT NULL, +CREATE TABLE `subscriptionroutinglist` ( -- information related to the routing lists attached to subscriptions + `routingid` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha + `borrowernumber` int(11) NOT NULL, -- foreign key from the borrowers table, defines with patron is on the routing list + `ranking` int(11) default NULL, -- where the patron stands in line to receive the serial + `subscriptionid` int(11) NOT NULL, -- foreign key from the subscription table, defines which subscription this routing list is for PRIMARY KEY (`routingid`), UNIQUE (`subscriptionid`, `borrowernumber`), CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) @@ -1820,38 +1840,39 @@ CREATE TABLE `subscriptionroutinglist` ( -- DROP TABLE IF EXISTS `suggestions`; -CREATE TABLE `suggestions` ( - `suggestionid` int(8) NOT NULL auto_increment, - `suggestedby` int(11) NOT NULL default 0, - `suggesteddate` date NOT NULL default 0, - `managedby` int(11) default NULL, - `manageddate` date default NULL, - acceptedby INT(11) default NULL, - accepteddate date default NULL, - rejectedby INT(11) default NULL, - rejecteddate date default NULL, - `STATUS` varchar(10) NOT NULL default '', - `note` mediumtext, - `author` varchar(80) default NULL, - `title` varchar(80) default NULL, - `copyrightdate` smallint(6) default NULL, - `publishercode` varchar(255) default NULL, - `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, +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 + `managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table + `manageddate` date default NULL, -- date the suggestion was updated + acceptedby INT(11) default NULL, -- borrowernumber for the librarian who accepted the suggestion, foreign key linking to the borrowers table + accepteddate date default NULL, -- date the suggestion was marked as accepted + rejectedby INT(11) default NULL, -- borrowernumber for the librarian who rejected the suggestion, foreign key linking to the borrowers table + rejecteddate date default NULL, -- date the suggestion was marked as rejected + `STATUS` varchar(10) NOT NULL default '', -- suggestion status (ASKED, CHECKED, ACCEPTED, or REJECTED) + `note` mediumtext, -- note entered on the suggestion + `author` varchar(80) default NULL, -- author of the suggested item + `title` varchar(80) default NULL, -- title of the suggested item + `copyrightdate` smallint(6) default NULL, -- copyright date of the suggested item + `publishercode` varchar(255) default NULL, -- publisher of the suggested item + `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the suggestion was updated `volumedesc` varchar(255) default NULL, `publicationyear` smallint(6) default 0, - `place` varchar(255) default NULL, - `isbn` varchar(30) default NULL, + `place` varchar(255) default NULL, -- publication place of the suggested item + `isbn` varchar(30) default NULL, -- isbn of the suggested item `mailoverseeing` smallint(1) default 0, - `biblionumber` int(11) default NULL, - `reason` text, - budgetid INT(11), - branchcode VARCHAR(10) default NULL, - collectiontitle text default NULL, - itemtype VARCHAR(30) default NULL, - quantity SMALLINT(6) default NULL, - currency VARCHAR(3) default NULL, - price DECIMAL(28,6) default NULL, - total DECIMAL(28,6) default NULL, + `biblionumber` int(11) default NULL, -- foreign key linking the suggestion to the biblio table after the suggestion has been ordered + `reason` text, -- reason for accepting or rejecting the suggestion + `patronreason` text, -- reason for making the suggestion + budgetid INT(11), -- foreign key linking the suggested budget to the aqbudgets table + branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table + collectiontitle text default NULL, -- collection name for the suggested item + itemtype VARCHAR(30) default NULL, -- suggested item type + quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased + currency VARCHAR(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`) @@ -1862,12 +1883,12 @@ CREATE TABLE `suggestions` ( -- DROP TABLE IF EXISTS `systempreferences`; -CREATE TABLE `systempreferences` ( - `variable` varchar(50) NOT NULL default '', - `value` text, - `options` mediumtext, - `explanation` text, - `type` varchar(20) default NULL, +CREATE TABLE `systempreferences` ( -- global system preferences + `variable` varchar(50) NOT NULL default '', -- system preference name + `value` text, -- system preference values + `options` mediumtext, -- options for multiple choice system preferences + `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; @@ -1955,13 +1976,13 @@ CREATE TABLE `userflags` ( -- DROP TABLE IF EXISTS `virtualshelves`; -CREATE TABLE `virtualshelves` ( - `shelfnumber` int(11) NOT NULL auto_increment, - `shelfname` varchar(255) default NULL, - `owner` varchar(80) default NULL, - `category` varchar(1) default NULL, - `sortfield` varchar(16) default NULL, - `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, +CREATE TABLE `virtualshelves` ( -- information about lists (or virtual shelves) + `shelfnumber` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha + `shelfname` varchar(255) default NULL, -- name of the list + `owner` varchar(80) default NULL, -- foriegn key linking to the borrowers table (using borrowernumber) for the creator of this list + `category` varchar(1) default NULL, -- type of list (public [2], private [1] or open [3]) + `sortfield` varchar(16) default NULL, -- the field this list is sorted on + `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the list was last modified PRIMARY KEY (`shelfnumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1970,11 +1991,11 @@ CREATE TABLE `virtualshelves` ( -- DROP TABLE IF EXISTS `virtualshelfcontents`; -CREATE TABLE `virtualshelfcontents` ( - `shelfnumber` int(11) NOT NULL default 0, - `biblionumber` int(11) NOT NULL default 0, +CREATE TABLE `virtualshelfcontents` ( -- information about the titles in a list (or virtual shelf) + `shelfnumber` int(11) NOT NULL default 0, -- foreign key linking to the virtualshelves table, defines the list that this record has been added to + `biblionumber` int(11) NOT NULL default 0, -- foreign key linking to the biblio table, defines the bib record that has been added to the list `flags` int(11) default NULL, - `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date and time this bib record was added to the list KEY `shelfnumber` (`shelfnumber`), KEY `biblionumber` (`biblionumber`), CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE, @@ -1986,22 +2007,22 @@ CREATE TABLE `virtualshelfcontents` ( -- DROP TABLE IF EXISTS `z3950servers`; -CREATE TABLE `z3950servers` ( - `host` varchar(255) default NULL, - `port` int(11) default NULL, - `db` varchar(255) default NULL, - `userid` varchar(255) default NULL, - `password` varchar(255) default NULL, - `name` mediumtext, - `id` int(11) NOT NULL auto_increment, - `checked` smallint(6) default NULL, - `rank` int(11) default NULL, - `syntax` varchar(80) default NULL, - `icon` text, +CREATE TABLE `z3950servers` ( -- connection information for the Z39.50 targets used in cataloging + `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 + `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 + `icon` text, -- unused in Koha `position` enum('primary','secondary','') NOT NULL default 'primary', `type` enum('zed','opensearch') NOT NULL default 'zed', - `encoding` text default NULL, - `description` text NOT NULL, + `encoding` text default NULL, -- characters encoding provided by this target + `description` text NOT NULL, -- unused in Koha PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -2021,6 +2042,10 @@ CREATE TABLE `zebraqueue` ( 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 '', @@ -2028,9 +2053,12 @@ CREATE TABLE `services_throttle` ( 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), @@ -2042,9 +2070,13 @@ CREATE TABLE language_subtag_registry ( 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), @@ -2054,6 +2086,10 @@ CREATE TABLE language_rfc4646_to_iso639 ( 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), @@ -2066,7 +2102,11 @@ CREATE TABLE language_descriptions ( 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. @@ -2074,8 +2114,12 @@ CREATE TABLE language_script_bidi ( 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), @@ -2083,6 +2127,10 @@ CREATE TABLE language_script_mapping ( 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, @@ -2093,6 +2141,10 @@ CREATE TABLE `permissions` ( 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, @@ -2100,9 +2152,13 @@ CREATE TABLE `serialitems` ( UNIQUE KEY `serialitemsidx` (`itemnumber`), 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 + 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, @@ -2272,13 +2328,13 @@ CREATE TABLE `item_circulation_alert_preferences` ( -- Table structure for table `messages` -- DROP TABLE IF EXISTS `messages`; -CREATE TABLE `messages` ( - `message_id` int(11) NOT NULL auto_increment, - `borrowernumber` int(11) NOT NULL, - `branchcode` varchar(10) default NULL, - `message_type` varchar(1) NOT NULL, - `message` text NOT NULL, - `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, +CREATE TABLE `messages` ( -- circulation messages left via the patron's check out screen + `message_id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha + `borrowernumber` int(11) NOT NULL, -- foreign key linking this message to the borrowers table + `branchcode` varchar(10) default NULL, -- foreign key linking the message to the branches table + `message_type` varchar(1) NOT NULL, -- whether the message is for the librarians (L) or the patron (B) + `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; @@ -2406,39 +2462,39 @@ CREATE TABLE `aqbasket` ( -- DROP TABLE IF EXISTS `aqbooksellers`; -CREATE TABLE `aqbooksellers` ( - `id` int(11) NOT NULL auto_increment, - `name` mediumtext NOT NULL, - `address1` mediumtext, - `address2` mediumtext, - `address3` mediumtext, - `address4` mediumtext, - `phone` varchar(30) default NULL, - `accountnumber` mediumtext, - `othersupplier` mediumtext, - `currency` varchar(3) NOT NULL default '', - `booksellerfax` mediumtext, - `notes` mediumtext, - `bookselleremail` mediumtext, - `booksellerurl` mediumtext, - `contact` varchar(100) default NULL, - `postal` mediumtext, - `url` varchar(255) default NULL, - `contpos` varchar(100) default NULL, - `contphone` varchar(100) default NULL, - `contfax` varchar(100) default NULL, - `contaltphone` varchar(100) default NULL, - `contemail` varchar(100) default NULL, - `contnotes` mediumtext, - `active` tinyint(4) default NULL, - `listprice` varchar(10) default NULL, - `invoiceprice` varchar(10) default NULL, - `gstreg` tinyint(4) default NULL, - `listincgst` tinyint(4) default NULL, - `invoiceincgst` tinyint(4) default NULL, - `gstrate` decimal(6,4) default NULL, - `discount` float(6,4) default NULL, - `fax` varchar(50) default NULL, +CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquisitions + `id` int(11) NOT NULL auto_increment, -- primary key and unique identifier assigned by Koha + `name` mediumtext NOT NULL, -- vendor name + `address1` mediumtext, -- first line of vendor physical address + `address2` mediumtext, -- second line of vendor physical address + `address3` mediumtext, -- third line of vendor physical address + `address4` mediumtext, -- fourth line of vendor physical address + `phone` varchar(30) default NULL, -- vendor phone number + `accountnumber` mediumtext, -- unused in Koha + `othersupplier` mediumtext, -- unused in Koha + `currency` varchar(3) NOT NULL default '', -- unused in Koha + `booksellerfax` mediumtext, -- vendor fax number + `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 + `gstreg` tinyint(4) default NULL, -- is your library charged tax (1 for yes, 0 for no) + `listincgst` tinyint(4) default NULL, -- is tax included in list prices (1 for yes, 0 for no) + `invoiceincgst` tinyint(4) default NULL, -- is tax included in invoice prices (1 for yes, 0 for no) + `gstrate` decimal(6,4) default NULL, -- the tax rate the library is charged + `discount` float(6,4) default NULL, -- discount offered on all items ordered from this vendor + `fax` varchar(50) default NULL, -- vendor fax number PRIMARY KEY (`id`), KEY `listprice` (`listprice`), KEY `invoiceprice` (`invoiceprice`), @@ -2576,6 +2632,8 @@ CREATE TABLE `aqorders` ( `sort1_authcat` varchar(10) default NULL, `sort2_authcat` varchar(10) default NULL, `uncertainprice` tinyint(1), + `claims_count` int(11) default 0, + `claimed_date` date default NULL, PRIMARY KEY (`ordernumber`), KEY `basketno` (`basketno`), KEY `biblionumber` (`biblionumber`), @@ -2603,15 +2661,30 @@ CREATE TABLE `aqorders_items` ( -- DROP TABLE IF EXISTS `fieldmapping`; -CREATE TABLE `fieldmapping` ( - `id` int(11) NOT NULL auto_increment, - `field` varchar(255) NOT NULL, - `frameworkcode` char(4) NOT NULL default '', - `fieldcode` char(3) NOT NULL, - `subfieldcode` char(1) NOT NULL, +CREATE TABLE `fieldmapping` ( -- koha to keyword mapping + `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha + `field` varchar(255) NOT NULL, -- keyword to be mapped to (ex. subtitle) + `frameworkcode` char(4) NOT NULL default '', -- foreign key from the biblio_framework table to link this mapping to a specific framework + `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; +-- +-- Table structure for table `bibliocoverimage` +-- + +DROP TABLE IF EXISTS `bibliocoverimage`; + +CREATE TABLE `bibliocoverimage` ( + `imagenumber` int(11) NOT NULL AUTO_INCREMENT, + `biblionumber` int(11) NOT NULL, + `mimetype` varchar(15) NOT NULL, + `imagefile` mediumblob NOT NULL, + `thumbnail` mediumblob NOT NULL, + PRIMARY KEY (`imagenumber`), + CONSTRAINT `bibliocoverimage_fk1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;