X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=53c1c71b78f9ab2e393c1964ad9299cc27b8d50a;hb=73def1a24a7b35f25580cc698b513384263ef07a;hp=494691af1d69ddb523832d86f70660601b7394c7;hpb=d42e35ef4970c46bf131eb0a0126c8559e89b58a;p=koha_fer diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 494691af1d..53c1c71b78 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -154,6 +154,7 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in `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) + `ean` varchar(13) default NULL, `publicationyear` text, `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b) `volumedate` date default NULL, @@ -365,7 +366,7 @@ CREATE TABLE `branches` ( -- information about your libraries or branches are st `branchprinter` varchar(100) default NULL, -- unused in Koha `branchnotes` mediumtext, -- notes related to your library or branch opac_info text, -- HTML that displays in OPAC - UNIQUE KEY `branchcode` (`branchcode`) + PRIMARY KEY (`branchcode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -388,13 +389,13 @@ CREATE TABLE `branchrelations` ( -- this table links libraries/branches to group -- DROP TABLE IF EXISTS `branchtransfers`; -CREATE TABLE `branchtransfers` ( - `itemnumber` int(11) NOT NULL default 0, - `datesent` datetime default NULL, - `frombranch` varchar(10) NOT NULL default '', - `datearrived` datetime default NULL, - `tobranch` varchar(10) NOT NULL default '', - `comments` mediumtext, +CREATE TABLE `branchtransfers` ( -- information for items that are in transit between branches + `itemnumber` int(11) NOT NULL default 0, -- the itemnumber that it is in transit (items.itemnumber) + `datesent` datetime default NULL, -- the date the transfer was initialized + `frombranch` varchar(10) NOT NULL default '', -- the branch the transfer is coming from + `datearrived` datetime default NULL, -- the date the transfer arrived at its destination + `tobranch` varchar(10) NOT NULL default '', -- the branch the transfer was going to + `comments` mediumtext, -- any comments related to the transfer KEY `frombranch` (`frombranch`), KEY `tobranch` (`tobranch`), KEY `itemnumber` (`itemnumber`), @@ -625,6 +626,7 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t `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) + `ean` varchar(13) default NULL, `publicationyear` text, `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b) `volumedate` date default NULL, @@ -1446,6 +1448,7 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r -- DROP TABLE IF EXISTS `old_reserves`; CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled) + `reserve_id` int(11) NOT NULL, -- primary key `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 @@ -1464,6 +1467,7 @@ CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b `lowestPriority` tinyint(1) NOT NULL, `suspend` BOOLEAN NOT NULL DEFAULT 0, `suspend_until` DATETIME NULL DEFAULT NULL, + PRIMARY KEY (`reserve_id`), KEY `old_reserves_borrowernumber` (`borrowernumber`), KEY `old_reserves_biblionumber` (`biblionumber`), KEY `old_reserves_itemnumber` (`itemnumber`), @@ -1642,6 +1646,7 @@ CREATE TABLE `reserveconstraints` ( DROP TABLE IF EXISTS `reserves`; CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha + `reserve_id` int(11) NOT NULL auto_increment, -- primary key `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 @@ -1660,6 +1665,7 @@ CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha `lowestPriority` tinyint(1) NOT NULL, `suspend` BOOLEAN NOT NULL DEFAULT 0, `suspend_until` DATETIME NULL DEFAULT NULL, + PRIMARY KEY (`reserve_id`), KEY priorityfoundidx (priority,found), KEY `borrowernumber` (`borrowernumber`), KEY `biblionumber` (`biblionumber`), @@ -1744,7 +1750,7 @@ CREATE TABLE IF NOT EXISTS `search_history` ( `userid` int(11) NOT NULL, `sessionid` varchar(32) NOT NULL, `query_desc` varchar(255) NOT NULL, - `query_cgi` varchar(255) NOT NULL, + `query_cgi` text NOT NULL, `total` int(11) NOT NULL, `time` timestamp NOT NULL default CURRENT_TIMESTAMP, KEY `userid` (`userid`), @@ -1780,7 +1786,7 @@ DROP TABLE IF EXISTS sessions; CREATE TABLE sessions ( `id` varchar(32) NOT NULL, `a_session` text NOT NULL, - UNIQUE KEY id (id) + PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -2498,14 +2504,14 @@ CREATE TABLE `accountoffsets` ( -- DROP TABLE IF EXISTS `action_logs`; -CREATE TABLE `action_logs` ( - `action_id` int(11) NOT NULL auto_increment, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `user` int(11) NOT NULL default 0, - `module` text, - `action` text, - `object` int(11) default NULL, - `info` text, +CREATE TABLE `action_logs` ( -- logs of actions taken in Koha (requires that the logs be turned on) + `action_id` int(11) NOT NULL auto_increment, -- unique identifier for each action + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time the action took place + `user` int(11) NOT NULL default 0, -- the staff member who performed the action (borrowers.borrowernumber) + `module` text, -- the module this action was taken against + `action` text, -- the action (includes things like DELETED, ADDED, MODIFY, etc) + `object` int(11) default NULL, -- the object that the action was taken against (could be a borrowernumber, itemnumber, etc) + `info` text, -- information about the action (usually includes SQL statement) PRIMARY KEY (`action_id`), KEY (`timestamp`,`user`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -2621,25 +2627,41 @@ CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquis -- DROP TABLE IF EXISTS `aqbudgets`; -CREATE TABLE `aqbudgets` ( - `budget_id` int(11) NOT NULL auto_increment, - `budget_parent_id` int(11) default NULL, - `budget_code` varchar(30) default NULL, - `budget_name` varchar(80) default NULL, - `budget_branchcode` varchar(10) default NULL, - `budget_amount` decimal(28,6) NULL default '0.00', - `budget_encumb` decimal(28,6) NULL default '0.00', - `budget_expend` decimal(28,6) NULL default '0.00', - `budget_notes` mediumtext, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `budget_period_id` int(11) default NULL, - `sort1_authcat` varchar(80) default NULL, - `sort2_authcat` varchar(80) default NULL, - `budget_owner_id` int(11) default NULL, - `budget_permission` int(1) default '0', +CREATE TABLE `aqbudgets` ( -- information related to Funds + `budget_id` int(11) NOT NULL auto_increment, -- primary key and unique number assigned to each fund by Koha + `budget_parent_id` int(11) default NULL, -- if this fund is a child of another this will include the parent id (aqbudgets.budget_id) + `budget_code` varchar(30) default NULL, -- code assigned to the fund by the user + `budget_name` varchar(80) default NULL, -- name assigned to the fund by the user + `budget_branchcode` varchar(10) default NULL, -- branch that this fund belongs to (branches.branchcode) + `budget_amount` decimal(28,6) NULL default '0.00', -- total amount for this fund + `budget_encumb` decimal(28,6) NULL default '0.00', -- not used in the code + `budget_expend` decimal(28,6) NULL default '0.00', -- not used in the code + `budget_notes` mediumtext, -- notes related to this fund + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this fund was last touched (created or modified) + `budget_period_id` int(11) default NULL, -- id of the budget that this fund belongs to (aqbudgetperiods.budget_period_id) + `sort1_authcat` varchar(80) default NULL, -- statistical category for this fund + `sort2_authcat` varchar(80) default NULL, -- second statistical category for this fund + `budget_owner_id` int(11) default NULL, -- borrowernumber of the person who owns this fund (borrowers.borrowernumber) + `budget_permission` int(1) default '0', -- level of permission for this fund (used only by the owner, only by the library, or anyone) PRIMARY KEY (`budget_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table aqbudgetborrowers +-- + +DROP TABLE IF EXISTS aqbudgetborrowers; +CREATE TABLE aqbudgetborrowers ( + budget_id int(11) NOT NULL, + borrowernumber int(11) NOT NULL, + PRIMARY KEY (budget_id, borrowernumber), + CONSTRAINT aqbudgetborrowers_ibfk_1 FOREIGN KEY (budget_id) + REFERENCES aqbudgets (budget_id) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT aqbudgetborrowers_ibfk_2 FOREIGN KEY (borrowernumber) + REFERENCES borrowers (borrowernumber) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Table structure for table `aqbudgetperiods` @@ -2647,16 +2669,16 @@ CREATE TABLE `aqbudgets` ( DROP TABLE IF EXISTS `aqbudgetperiods`; -CREATE TABLE `aqbudgetperiods` ( - `budget_period_id` int(11) NOT NULL auto_increment, - `budget_period_startdate` date NOT NULL, - `budget_period_enddate` date NOT NULL, - `budget_period_active` tinyint(1) default '0', - `budget_period_description` mediumtext, - `budget_period_total` decimal(28,6), - `budget_period_locked` tinyint(1) default NULL, - `sort1_authcat` varchar(10) default NULL, - `sort2_authcat` varchar(10) default NULL, +CREATE TABLE `aqbudgetperiods` ( -- information related to Budgets + `budget_period_id` int(11) NOT NULL auto_increment, -- primary key and unique number assigned by Koha + `budget_period_startdate` date NOT NULL, -- date when the budget starts + `budget_period_enddate` date NOT NULL, -- date when the budget ends + `budget_period_active` tinyint(1) default '0', -- whether this budget is active or not (1 for yes, 0 for no) + `budget_period_description` mediumtext, -- description assigned to this budget + `budget_period_total` decimal(28,6), -- total amount available in this budget + `budget_period_locked` tinyint(1) default NULL, -- whether this budget is locked or not (1 for yes, 0 for no) + `sort1_authcat` varchar(10) default NULL, -- statistical category for this budget + `sort2_authcat` varchar(10) default NULL, -- second statistical category for this budget PRIMARY KEY (`budget_period_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -2712,42 +2734,42 @@ CREATE TABLE `aqorderdelivery` ( -- DROP TABLE IF EXISTS `aqorders`; -CREATE TABLE `aqorders` ( - `ordernumber` int(11) NOT NULL auto_increment, - `biblionumber` int(11) default NULL, - `entrydate` date default NULL, - `quantity` smallint(6) default NULL, - `currency` varchar(3) default NULL, - `listprice` decimal(28,6) default NULL, - `totalamount` decimal(28,6) default NULL, - `datereceived` date default NULL, - `booksellerinvoicenumber` mediumtext, - `freight` decimal(28,6) default NULL, - `unitprice` decimal(28,6) default NULL, - `quantityreceived` smallint(6) NOT NULL default 0, - `cancelledby` varchar(10) default NULL, - `datecancellationprinted` date default NULL, - `notes` mediumtext, - `supplierreference` mediumtext, - `purchaseordernumber` mediumtext, - `subscription` tinyint(1) default NULL, - `serialid` varchar(30) default NULL, - `basketno` int(11) default NULL, - `biblioitemnumber` int(11) default NULL, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `rrp` decimal(13,2) default NULL, - `ecost` decimal(13,2) default NULL, - `gst` decimal(13,2) default NULL, - `budget_id` int(11) NOT NULL, - `budgetgroup_id` int(11) NOT NULL, - `budgetdate` date default NULL, - `sort1` varchar(80) default NULL, - `sort2` varchar(80) default NULL, +CREATE TABLE `aqorders` ( --information related to the basket line items + `ordernumber` int(11) NOT NULL auto_increment, -- primary key and unique identifier assigned by Koha to each line + `biblionumber` int(11) default NULL, -- links the order to the biblio being ordered (biblio.biblionumber) + `entrydate` date default NULL, -- the date the bib was added to the basket + `quantity` smallint(6) default NULL, -- the quantity ordered + `currency` varchar(3) default NULL, -- the currency used for the purchase + `listprice` decimal(28,6) default NULL, -- the vendor price for this line item + `totalamount` decimal(28,6) default NULL, -- not used? always NULL + `datereceived` date default NULL, -- the date this order was received + `booksellerinvoicenumber` mediumtext, -- the invoice number this line item was received on + `freight` decimal(28,6) default NULL, -- shipping costs (not used) + `unitprice` decimal(28,6) default NULL, -- the actual cost entered when receiving this line item + `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far + `cancelledby` varchar(10) default NULL, -- not used? always NULL + `datecancellationprinted` date default NULL, -- the date the line item was deleted + `notes` mediumtext, -- notes related to this order line + `supplierreference` mediumtext, -- not used? always NULL + `purchaseordernumber` mediumtext, -- not used? always NULL + `subscription` tinyint(1) default NULL, -- not used? always NULL + `serialid` varchar(30) default NULL, -- not used? always NULL + `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno) + `biblioitemnumber` int(11) default NULL, -- links this order line the biblioitems table (biblioitems.biblioitemnumber) + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified + `rrp` decimal(13,2) default NULL, -- the replacement cost for this line item + `ecost` decimal(13,2) default NULL, -- the estimated cost for this line item + `gst` decimal(13,2) default NULL, -- the tax rate for this line item + `budget_id` int(11) NOT NULL, -- the fund this order goes against (aqbudgets.budget_id) + `budgetgroup_id` int(11) NOT NULL, -- not used? always zero + `budgetdate` date default NULL, -- not used? always NULL + `sort1` varchar(80) default NULL, -- statistical field + `sort2` varchar(80) default NULL, -- second statistical field `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, + `uncertainprice` tinyint(1), -- was this price uncertain (1 for yes, 0 for no) + `claims_count` int(11) default 0, -- count of claim letters generated + `claimed_date` date default NULL, -- last date a claim was generated PRIMARY KEY (`ordernumber`), KEY `basketno` (`basketno`), KEY `biblionumber` (`biblionumber`), @@ -2762,10 +2784,10 @@ CREATE TABLE `aqorders` ( -- DROP TABLE IF EXISTS `aqorders_items`; -CREATE TABLE `aqorders_items` ( - `ordernumber` int(11) NOT NULL, - `itemnumber` int(11) NOT NULL, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, +CREATE TABLE `aqorders_items` ( -- information on items entered in the acquisitions process + `ordernumber` int(11) NOT NULL, -- the order this item is attached to (aqorders.ordernumber) + `itemnumber` int(11) NOT NULL, -- the item number for this item (items.itemnumber) + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order item was last touched PRIMARY KEY (`itemnumber`), KEY `ordernumber` (`ordernumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -2821,10 +2843,10 @@ CREATE TABLE IF NOT EXISTS `social_data` ( -- DROP TABLE IF EXISTS ratings; -CREATE TABLE ratings ( - borrowernumber int(11) NOT NULL, -- the borrower this rating is for - biblionumber int(11) NOT NULL, -- the biblio it's for - rating_value tinyint(1) NOT NULL, -- the rating, from 1-5 +CREATE TABLE ratings ( -- information related to the star ratings in the OPAC + borrowernumber int(11) NOT NULL, -- the borrowernumber of the patron who left this rating (borrowers.borrowernumber) + biblionumber int(11) NOT NULL, -- the biblio this rating is for (biblio.biblionumber) + rating_value tinyint(1) NOT NULL, -- the rating, from 1 to 5 timestamp timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (borrowernumber,biblionumber), CONSTRAINT ratings_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, @@ -2842,7 +2864,7 @@ CREATE TABLE `quotes` ( `text` mediumtext NOT NULL, `timestamp` datetime NOT NULL, PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 +) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;