From 0c403490963a2f57b563c0bd11845cd0cf198181 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Wed, 9 Mar 2016 13:29:08 +0100 Subject: [PATCH] Bug 15084: [QA Follow-up] Correct field length of currency Bug 15084 added a FK constraint while the fields in the database are not in sync as to length. This will produce errors when using currency codes longer than three characters. Probably you won't, but nobody stopped users from entering EURO or DOLLAR etc. Not to speak about TestBuilder too. This patch corrects the database revision for aqorders in updatedatabase, because we need to change the field length before adding the FK constraint. It also updates other currency fields < 10 chars (via atomicupdate). RM: So please add that dbrev too in updatedatabase. Note that another report should deal with adding missing constraints on the currency code in suggestions and aqbooksellers. Also note that the aqorder fields listprice and invoiceprice refer to currency. Imo these are very poor names for currency codes; you should never call something a price when you mean a currency code! Similar changes are applied to kohastructure. Signed-off-by: Marcel de Rooy Tested the db revisions. Signed-off-by: Brendan A Gallagher --- installer/data/mysql/atomicupdate/15084_currency.sql | 4 ++++ installer/data/mysql/kohastructure.sql | 6 +++--- installer/data/mysql/updatedatabase.pl | 6 ++++-- 3 files changed, 11 insertions(+), 5 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/15084_currency.sql diff --git a/installer/data/mysql/atomicupdate/15084_currency.sql b/installer/data/mysql/atomicupdate/15084_currency.sql new file mode 100644 index 0000000000..71c68a5622 --- /dev/null +++ b/installer/data/mysql/atomicupdate/15084_currency.sql @@ -0,0 +1,4 @@ +ALTER TABLE suggestions + MODIFY COLUMN currency varchar(10) default NULL; +ALTER TABLE aqbooksellers + MODIFY COLUMN currency varchar(10) default NULL; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 45a0323705..63fefe7380 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2232,7 +2232,7 @@ CREATE TABLE `suggestions` ( -- purchase suggestions 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 + currency VARCHAR(10) 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`), @@ -2921,7 +2921,7 @@ CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquis `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 + `currency` varchar(10) NOT NULL default '', -- unused in Koha `booksellerfax` mediumtext, -- vendor fax number `notes` mediumtext, -- order notes `bookselleremail` mediumtext, -- vendor email @@ -3080,7 +3080,7 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `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 + `currency` varchar(10) default NULL, -- the currency used for the purchase `listprice` decimal(28,6) default NULL, -- the vendor price for this line item `datereceived` date default NULL, -- the date this order was received invoiceid int(11) default NULL, -- id of invoice diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index c6b777ba18..ec7df9338c 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -11987,6 +11987,8 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { $dbh->do(q{ INSERT INTO currency(currency, archived) SELECT distinct currency, 1 FROM aqorders WHERE currency NOT IN (SELECT currency FROM currency); }); +## Correct the field length in aqorders before adding FK too + $dbh->do(q{ ALTER TABLE aqorders MODIFY COLUMN currency varchar(10) default NULL; }); ## And finally add the FK $dbh->do(q{ ALTER TABLE aqorders ADD FOREIGN KEY (currency) REFERENCES currency(currency) ON DELETE SET NULL ON UPDATE SET null; @@ -11994,7 +11996,7 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { print "Upgrade to $DBversion done (Bug 15084 - Move the currency related code to Koha::Acquisition::Currenc[y|ies])\n"; SetVersion($DBversion); - } +} $DBversion = "3.23.00.038"; if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { @@ -12003,7 +12005,7 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { }); print "Upgrade to $DBversion done (Bug 14694 - Make decreaseloanHighHolds more flexible)\n"; SetVersion($DBversion); - } +} # DEVELOPER PROCESS, search for anything to execute in the db_update directory # SEE bug 13068 -- 2.39.2