X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=b46dc6adb0a770a62897abe8968d2af9e42ad845;hb=HEAD;hp=dddd2c0071092e1e192824e174157f8a839f7b7d;hpb=2be50eba0fe16a7243722f3edac56447c2b6fa75;p=koha_fer diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index dddd2c0071..b46dc6adb0 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -153,8 +153,8 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in `volume` mediumtext, `number` mediumtext, `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c) - `isbn` varchar(30) default NULL, -- ISBN (MARC21 020$a) - `issn` varchar(9) default NULL, -- ISSN (MARC21 022$a) + `isbn` mediumtext, -- ISBN (MARC21 020$a) + `issn` mediumtext, -- ISSN (MARC21 022$a) `ean` varchar(13) default NULL, `publicationyear` text, `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b) @@ -186,8 +186,8 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), KEY `itemtype_idx` (`itemtype`), - KEY `isbn` (`isbn`), - KEY `issn` (`issn`), + KEY `isbn` (`isbn`(255)), + KEY `issn` (`issn`(255)), KEY `publishercode` (`publishercode`), CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -466,6 +466,8 @@ CREATE TABLE `categories` ( -- this table shows information related to Koha patr `reservefee` decimal(28,6) default NULL, -- cost to place holds `hidelostitems` tinyint(1) NOT NULL default '0', -- are lost items shown to this category (1 for yes, 0 for no) `category_type` varchar(1) NOT NULL default 'A', -- type of Koha patron (Adult, Child, Professional, Organizational, Statistical, Staff) + `BlockExpiredPatronOpacActions` tinyint(1) NOT NULL default '-1', -- wheither or not a patron of this category can renew books or place holds once their card has expired. 0 means they can, 1 means they cannot, -1 means use syspref BlockExpiredPatronOpacActions + `default_privacy` ENUM( 'default', 'never', 'forever' ) NOT NULL DEFAULT 'default', -- Default privacy setting for this patron category PRIMARY KEY (`categorycode`), UNIQUE KEY `categorycode` (`categorycode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -716,6 +718,7 @@ DROP TABLE IF EXISTS `currency`; CREATE TABLE `currency` ( `currency` varchar(10) NOT NULL default '', `symbol` varchar(5) default NULL, + `isocode` varchar(5) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `rate` float(15,5) default NULL, `active` tinyint(1) default NULL, @@ -755,8 +758,8 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t `volume` mediumtext, `number` mediumtext, `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c) - `isbn` varchar(30) default NULL, -- ISBN (MARC21 020$a) - `issn` varchar(9) default NULL, -- ISSN (MARC21 022$a) + `isbn` mediumtext default NULL, -- ISBN (MARC21 020$a) + `issn` mediumtext default NULL, -- ISSN (MARC21 022$a) `ean` varchar(13) default NULL, `publicationyear` text, `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b) @@ -788,7 +791,7 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), KEY `itemtype_idx` (`itemtype`), - KEY `isbn` (`isbn`), + KEY `isbn` (`isbn`(255)), KEY `publishercode` (`publishercode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1144,6 +1147,7 @@ CREATE TABLE `issuingrules` ( -- circulation and fine rules `reservecharge` decimal(28,6) default NULL, `fine` decimal(28,6) default NULL, -- fine amount `finedays` int(11) default NULL, -- suspension in days + `maxsuspensiondays` int(11) default NULL, -- max suspension days `firstremind` int(11) default NULL, -- fine grace period `chargeperiod` int(11) default NULL, -- how often the fine amount is charged `accountsent` int(11) default NULL, -- not used? always NULL @@ -1155,6 +1159,7 @@ CREATE TABLE `issuingrules` ( -- circulation and fine rules `hardduedatecompare` tinyint NOT NULL default "0", -- type of hard due date (1 = after, 0 = on, -1 = before) `renewalsallowed` smallint(6) NOT NULL default "0", -- how many renewals are allowed `renewalperiod` int(4) default NULL, -- renewal period in the unit set in issuingrules.lengthunit + `norenewalbefore` int(4) default NULL, -- no renewal allowed until X days or hours before due date. In the unit set in issuingrules.lengthunit `reservesallowed` smallint(6) NOT NULL default "0", -- how many holds are allowed `branchcode` varchar(10) NOT NULL default '', -- the branch this rule is for (branches.branchcode) overduefinescap decimal(28,6) default NULL, -- the maximum amount of an overdue fine @@ -1241,6 +1246,7 @@ CREATE TABLE `itemtypes` ( -- defines the item types summary text, -- information from the summary field, may include HTML checkinmsg VARCHAR(255), -- message that is displayed when an item with the given item type is checked in checkinmsgtype CHAR(16) DEFAULT 'message' NOT NULL, -- type (CSS class) for the checkinmsg, can be "alert" or "message" + sip_media_type VARCHAR(3) DEFAULT NULL, -- SIP2 protocol media type for this itemtype PRIMARY KEY (`itemtype`), UNIQUE KEY `itemtype` (`itemtype`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1352,7 +1358,10 @@ CREATE TABLE `letter` ( -- table for all notice templates in Koha `is_html` tinyint(1) default 0, -- does this notice or slip use HTML (1 for yes, 0 for no) `title` varchar(200) NOT NULL default '', -- subject line of the notice `content` text, -- body text for the notice or slip - PRIMARY KEY (`module`,`code`, `branchcode`) + `message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice + PRIMARY KEY (`module`,`code`, `branchcode`, `message_transport_type`), + CONSTRAINT `message_transport_type_fk` FOREIGN KEY (`message_transport_type`) + REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -1642,13 +1651,16 @@ CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b DROP TABLE IF EXISTS `opac_news`; CREATE TABLE `opac_news` ( -- data from the news tool `idnew` int(10) unsigned NOT NULL auto_increment, -- unique identifier for the news article + `branchcode` varchar(10) default NULL, -- branch code users to create branch specific news, NULL is every branch. `title` varchar(250) NOT NULL default '', -- title of the news article `new` text NOT NULL, -- the body of your news article `lang` varchar(25) NOT NULL default '', -- location for the article (koha is the staff client, slip is the circulation receipt and language codes are for the opac) `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- pulibcation date and time `expirationdate` date default NULL, -- date the article is set to expire or no longer be visible `number` int(11) default NULL, -- the order in which this article appears in that specific location - PRIMARY KEY (`idnew`) + PRIMARY KEY (`idnew`), + CONSTRAINT opac_news_branchcode_ibfk FOREIGN KEY (branchcode) REFERENCES branches (branchcode) + ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -1900,6 +1912,7 @@ CREATE TABLE IF NOT EXISTS `search_history` ( -- patron's opac search history `sessionid` varchar(32) NOT NULL, -- a system generated session id `query_desc` varchar(255) NOT NULL, -- the search that was performed `query_cgi` text NOT NULL, -- the string to append to the search url to rerun the search + `type` varchar(16) NOT NULL DEFAULT 'biblio', -- search type, must be 'biblio' or 'authority' `total` int(11) NOT NULL, -- the total of results found `time` timestamp NOT NULL default CURRENT_TIMESTAMP, -- the date and time the search was run KEY `userid` (`userid`), @@ -2544,6 +2557,22 @@ CREATE TABLE `message_transport_types` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table structure for table `overduerules_transport_types` +-- + +DROP TABLE IF EXISTS `overduerules_transport_types`; +CREATE TABLE overduerules_transport_types( + `id` INT(11) NOT NULL AUTO_INCREMENT, + `branchcode` varchar(10) NOT NULL DEFAULT '', + `categorycode` VARCHAR(10) NOT NULL DEFAULT '', + `letternumber` INT(1) NOT NULL DEFAULT 1, + `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email', + PRIMARY KEY (id), + CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table structure for table `message_attributes` -- @@ -2730,7 +2759,12 @@ CREATE TABLE `action_logs` ( -- logs of actions taken in Koha (requires that the `object` int(11) default NULL, -- the object that the action was taken against (could be a borrowernumber, itemnumber, etc) `info` text, -- information about the action (usually includes SQL statement) PRIMARY KEY (`action_id`), - KEY (`timestamp`,`user`) + KEY `timestamp_idx` (`timestamp`), + KEY `user_idx` (`user`), + KEY `module_idx` (`module`(255)), + KEY `action_idx` (`action`(255)), + KEY `object_idx` (`object`), + KEY `info_idx` (`info`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -2951,19 +2985,6 @@ CREATE TABLE `aqcontract` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- --- Table structure for table `aqorderdelivery` --- - -DROP TABLE IF EXISTS `aqorderdelivery`; -CREATE TABLE `aqorderdelivery` ( - `ordernumber` date default NULL, - `deliverynumber` smallint(6) NOT NULL default 0, - `deliverydate` varchar(18) default NULL, - `qtydelivered` smallint(6) default NULL, - `deliverycomments` mediumtext -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- -- Table structure for table `aqorders` -- @@ -2983,7 +3004,8 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far `cancelledby` varchar(10) default NULL, -- not used? always NULL `datecancellationprinted` date default NULL, -- the date the line item was deleted - `notes` mediumtext, -- notes related to this order line + `order_internalnote` mediumtext, -- notes related to this order line, made for staff + `order_vendornote` mediumtext, -- notes related to this order line, made for vendor `supplierreference` mediumtext, -- not used? always NULL `purchaseordernumber` mediumtext, -- not used? always NULL `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno) @@ -3382,6 +3404,24 @@ CREATE TABLE IF NOT EXISTS marc_modification_template_actions ( CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `misc_files` +-- + +CREATE TABLE IF NOT EXISTS `misc_files` ( -- miscellaneous files attached to records from various tables + `file_id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the file record + `table_tag` varchar(255) NOT NULL, -- usually table name, or arbitrary unique tag + `record_id` int(11) NOT NULL, -- record id from the table this file is associated to + `file_name` varchar(255) NOT NULL, -- file name + `file_type` varchar(255) NOT NULL, -- MIME type of the file + `file_description` varchar(255) DEFAULT NULL, -- description given to the file + `file_content` longblob NOT NULL, -- file content + `date_uploaded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- date and time the file was added + PRIMARY KEY (`file_id`), + KEY `table_tag` (`table_tag`), + KEY `record_id` (`record_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;