From 37150c600fb38e6b7b35a8a3a061944a889464d0 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 16 Dec 2019 17:20:31 +0100 Subject: [PATCH] Bug 24161: DB changes Sponsored-by: Cork Institute of Technology --- .../data/mysql/atomicupdate/bug_24161.perl | 36 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 14 ++++++-- 2 files changed, 48 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_24161.perl diff --git a/installer/data/mysql/atomicupdate/bug_24161.perl b/installer/data/mysql/atomicupdate/bug_24161.perl new file mode 100644 index 0000000000..16666759e1 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_24161.perl @@ -0,0 +1,36 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + unless ( TableExists( 'aqorders_claims' ) ) { + $dbh->do(q| + CREATE TABLE aqorders_claims ( + id int(11) AUTO_INCREMENT, + ordernumber INT(11) NOT NULL, + claimed_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (id), + CONSTRAINT aqorders_claims_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci + |); + + my $orders = $dbh->selectall_arrayref(q| + SELECT ordernumber, claims_count, claimed_date + FROM aqorders + WHERE claims_count > 0 + |, { Slice => {} }); + my $insert_claim_sth = $dbh->prepare(q| + INSERT INTO aqorders_claims (ordernumber, claimed_on) + VALUES (?,?) + |); + + for my $order ( @$orders ) { + for my $claim (1..$order->{claims_count}) { + $insert_claim_sth->execute($order->{ordernumber}, $order->{claimed_on}); + } + } + + $dbh->do(q|ALTER TABLE aqorders DROP COLUMN claims_count, DROP COLUMN claimed_date|); + } + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 24161 - Add new join table aqorders_claims to keep track of claims)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 4308bcdc21..a8d3e54199 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3198,8 +3198,6 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `sort1_authcat` varchar(10) default NULL, `sort2_authcat` varchar(10) 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 `subscriptionid` int(11) default NULL, -- links this order line to a subscription (subscription.subscriptionid) parent_ordernumber int(11) default NULL, -- ordernumber of parent order line, or same as ordernumber if no parent `orderstatus` varchar(16) default 'new', -- the current status for this line item. Can be 'new', 'ordered', 'partial', 'complete' or 'cancelled' @@ -3264,6 +3262,18 @@ CREATE TABLE aqorders_transfers ( CONSTRAINT aqorders_transfers_ordernumber_to FOREIGN KEY (ordernumber_to) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- +-- Table structure for table aqorders_claims +-- + +DROP TABLE IF EXISTS aqorders_claims; +CREATE TABLE aqorders_claims ( + id int(11) AUTO_INCREMENT, + ordernumber INT(11) NOT NULL, + claimed_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (id), + CONSTRAINT aqorders_claims_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; -- -- Table structure for table `transport_cost` -- 2.20.1