From 97bf83970811b52530eaa5a4334531f470ec36e5 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 Content-Type: text/plain; charset=utf-8 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. --- 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 0000000..71c68a5 --- /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 45a0323..63fefe7 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 c6b777b..ec7df93 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 -- 1.7.10.4