Merge "Update pdp_enginestats records during upgrade/downgrade"

13 files changed, 30 insertions, 8 deletions

diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0120-pdp.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0100-pdp.sql
index 57a551da..57a551da 100644
--- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0120-pdp.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0100-pdp.sql
diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0130-idx_tsidx1.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0110-idx_tsidx1.sql
index 864e4f03..864e4f03 100644
--- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0130-idx_tsidx1.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0110-idx_tsidx1.sql
diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0170-pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0120-pk_pdpstatistics.sql
index 98e26993..4066c012 100644
--- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0170-pdpstatistics.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0120-pk_pdpstatistics.sql
@@ -17,4 +17,4 @@
  *  ============LICENSE_END=========================================================
  */
 
-ALTER TABLE pdpstatistics MODIFY COLUMN timeStamp datetime NULL;
+ALTER TABLE pdpstatistics DROP PRIMARY KEY;
diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0160-pk_pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0130-pdpstatistics.sql
index b8a96b68..c4a1123e 100644
--- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0160-pk_pdpstatistics.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0130-pdpstatistics.sql
@@ -17,6 +17,4 @@
  *  ============LICENSE_END=========================================================
  */
 
-UPDATE pdpstatistics as p JOIN (SELECT name, version, timeStamp, ROW_NUMBER() OVER (ORDER BY timeStamp ASC) AS row_num FROM pdpstatistics GROUP BY name, version, timeStamp) AS t ON (p.name=t.name AND p.version=t.version AND p.timeStamp = t.timeStamp) SET p.id=t.row_num;
-
-ALTER TABLE pdpstatistics ADD CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, name, version);
+ALTER TABLE pdpstatistics ADD COLUMN POLICYUNDEPLOYCOUNT BIGINT DEFAULT NULL NULL AFTER POLICYEXECUTEDSUCCESSCOUNT, ADD COLUMN POLICYUNDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL, ADD COLUMN POLICYUNDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL, ADD COLUMN ID BIGINT NOT NULL;
diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0140-pk_pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0140-pk_pdpstatistics.sql
index 4066c012..b8a96b68 100644
--- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0140-pk_pdpstatistics.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0140-pk_pdpstatistics.sql
@@ -17,4 +17,6 @@
  *  ============LICENSE_END=========================================================
  */
 
-ALTER TABLE pdpstatistics DROP PRIMARY KEY;
+UPDATE pdpstatistics as p JOIN (SELECT name, version, timeStamp, ROW_NUMBER() OVER (ORDER BY timeStamp ASC) AS row_num FROM pdpstatistics GROUP BY name, version, timeStamp) AS t ON (p.name=t.name AND p.version=t.version AND p.timeStamp = t.timeStamp) SET p.id=t.row_num;
+
+ALTER TABLE pdpstatistics ADD CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, name, version);
diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0150-pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0150-pdpstatistics.sql
index c4a1123e..98e26993 100644
--- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0150-pdpstatistics.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0150-pdpstatistics.sql
@@ -17,4 +17,4 @@
  *  ============LICENSE_END=========================================================
  */
 
-ALTER TABLE pdpstatistics ADD COLUMN POLICYUNDEPLOYCOUNT BIGINT DEFAULT NULL NULL AFTER POLICYEXECUTEDSUCCESSCOUNT, ADD COLUMN POLICYUNDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL, ADD COLUMN POLICYUNDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL, ADD COLUMN ID BIGINT NOT NULL;
+ALTER TABLE pdpstatistics MODIFY COLUMN timeStamp datetime NULL;
diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0110-jpapdpstatistics_enginestats.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0160-jpapdpstatistics_enginestats.sql
index 6e5bfecd..6e5bfecd 100644
--- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0110-jpapdpstatistics_enginestats.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0160-jpapdpstatistics_enginestats.sql
diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0170-jpapdpstatistics_enginestats.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0170-jpapdpstatistics_enginestats.sql
new file mode 100644
index 00000000..ce78c340
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0170-jpapdpstatistics_enginestats.sql
@@ -0,0 +1,22 @@
+/*
+ * ============LICENSE_START=======================================================
+ *  Copyright (C) 2021 Nordix Foundation
+ *  ================================================================================
+ *  Licensed under the Apache License, Version 2.0 (the "License");
+ *  you may not use this file except in compliance with the License.
+ *  You may obtain a copy of the License at
+ *
+ *        http://www.apache.org/licenses/LICENSE-2.0
+ *  Unless required by applicable law or agreed to in writing, software
+ *  distributed under the License is distributed on an "AS IS" BASIS,
+ *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ *  See the License for the specific language governing permissions and
+ *  limitations under the License.
+ *
+ *  SPDX-License-Identifier: Apache-2.0
+ *  ============LICENSE_END=========================================================
+ */
+UPDATE jpapdpstatistics_enginestats a
+  JOIN pdpstatistics b
+    ON a.name = b.name AND a.version = b.version AND a.timeStamp = b.timeStamp
+   SET a.id = b.id;
diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0100-jpapdpstatistics_enginestats.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0180-jpapdpstatistics_enginestats.sql
index 16825f4f..16825f4f 100644
--- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0100-jpapdpstatistics_enginestats.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0180-jpapdpstatistics_enginestats.sql
diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0180-jpapolicyaudit.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0190-jpapolicyaudit.sql
index a31d2421..a31d2421 100644
--- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0180-jpapolicyaudit.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0190-jpapolicyaudit.sql
diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0190-JpaPolicyAuditIndex_timestamp.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0200-JpaPolicyAuditIndex_timestamp.sql
index d789d118..d789d118 100644
--- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0190-JpaPolicyAuditIndex_timestamp.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0200-JpaPolicyAuditIndex_timestamp.sql
diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0210-sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0210-sequence.sql
index c90905ef..64d6f01b 100644
--- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0210-sequence.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0210-sequence.sql
@@ -17,4 +17,4 @@
  *  ============LICENSE_END=========================================================
  */
 
-INSERT INTO sequence(SEQ_NAME, SEQ_COUNT) VALUES('SEQ_GEN', (SELECT IFNULL(max(id),0) FROM pdpstatistics));
+CREATE TABLE IF NOT EXISTS sequence (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT DECIMAL(38) DEFAULT NULL NULL, CONSTRAINT PK_SEQUENCE PRIMARY KEY (SEQ_NAME));
diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0200-sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0220-sequence.sql
index 64d6f01b..c90905ef 100644
--- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0200-sequence.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0220-sequence.sql
@@ -17,4 +17,4 @@
  *  ============LICENSE_END=========================================================
  */
 
-CREATE TABLE IF NOT EXISTS sequence (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT DECIMAL(38) DEFAULT NULL NULL, CONSTRAINT PK_SEQUENCE PRIMARY KEY (SEQ_NAME));
+INSERT INTO sequence(SEQ_NAME, SEQ_COUNT) VALUES('SEQ_GEN', (SELECT IFNULL(max(id),0) FROM pdpstatistics));