X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=9578164def7b765f6d77e128a10bcaf83697aaf7;hb=86392dde00908b530c45e109a79e9b0693c3fcf9;hp=12ea9d1dbc682186bd4a9ef1c18f53f754cac032;hpb=60186fa42fa0742eb3e8484f1fd4b16e04d32ae1;p=srvgit diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 12ea9d1dbc..9578164def 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, @@ -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`), @@ -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; @@ -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 */;