From 52cbfc9c50eec0f2ee769c9809e9a36c41bd6a7b Mon Sep 17 00:00:00 2001 From: ktimoney Date: Wed, 25 Aug 2021 12:14:54 +0100 Subject: Update pdp_enginestats records during upgrade/downgrade Updates to pdpstatistics have a knock on effect on pdp_enginestats. Table data needs to updated to ensure records are in sync Issue-ID: POLICY-3583 Change-Id: Id7408beceb47e540e933e8ccc0ff05095f8da85b Signed-off-by: ktimoney --- .../0100-jpapdpstatistics_enginestats.sql | 20 -------------------- .../policyadmin/sql/0900/downgrade/0100-pdp.sql | 20 ++++++++++++++++++++ .../sql/0900/downgrade/0110-idx_tsidx1.sql | 20 ++++++++++++++++++++ .../0110-jpapdpstatistics_enginestats.sql | 20 -------------------- .../policyadmin/sql/0900/downgrade/0120-pdp.sql | 20 -------------------- .../sql/0900/downgrade/0120-pk_pdpstatistics.sql | 20 ++++++++++++++++++++ .../sql/0900/downgrade/0130-idx_tsidx1.sql | 20 -------------------- .../sql/0900/downgrade/0130-pdpstatistics.sql | 20 ++++++++++++++++++++ .../sql/0900/downgrade/0140-pk_pdpstatistics.sql | 4 +++- .../sql/0900/downgrade/0150-pdpstatistics.sql | 2 +- .../0160-jpapdpstatistics_enginestats.sql | 20 ++++++++++++++++++++ .../sql/0900/downgrade/0160-pk_pdpstatistics.sql | 22 ---------------------- .../0170-jpapdpstatistics_enginestats.sql | 22 ++++++++++++++++++++++ .../sql/0900/downgrade/0170-pdpstatistics.sql | 20 -------------------- .../0180-jpapdpstatistics_enginestats.sql | 20 ++++++++++++++++++++ .../sql/0900/downgrade/0180-jpapolicyaudit.sql | 20 -------------------- .../0190-JpaPolicyAuditIndex_timestamp.sql | 20 -------------------- .../sql/0900/downgrade/0190-jpapolicyaudit.sql | 20 ++++++++++++++++++++ .../0200-JpaPolicyAuditIndex_timestamp.sql | 20 ++++++++++++++++++++ .../sql/0900/downgrade/0200-sequence.sql | 20 -------------------- .../sql/0900/downgrade/0210-sequence.sql | 2 +- .../sql/0900/downgrade/0220-sequence.sql | 20 ++++++++++++++++++++ .../upgrade/0100-jpapdpstatistics_enginestats.sql | 20 -------------------- .../policyadmin/sql/0900/upgrade/0100-pdp.sql | 20 ++++++++++++++++++++ .../sql/0900/upgrade/0110-idx_tsidx1.sql | 20 ++++++++++++++++++++ .../upgrade/0110-jpapdpstatistics_enginestats.sql | 20 -------------------- .../policyadmin/sql/0900/upgrade/0120-pdp.sql | 20 -------------------- .../sql/0900/upgrade/0120-pk_pdpstatistics.sql | 20 ++++++++++++++++++++ .../sql/0900/upgrade/0130-idx_tsidx1.sql | 20 -------------------- .../sql/0900/upgrade/0130-pdpstatistics.sql | 20 ++++++++++++++++++++ .../sql/0900/upgrade/0140-pk_pdpstatistics.sql | 4 +++- .../sql/0900/upgrade/0150-pdpstatistics.sql | 2 +- .../upgrade/0160-jpapdpstatistics_enginestats.sql | 20 ++++++++++++++++++++ .../sql/0900/upgrade/0160-pk_pdpstatistics.sql | 22 ---------------------- .../upgrade/0170-jpapdpstatistics_enginestats.sql | 22 ++++++++++++++++++++++ .../sql/0900/upgrade/0170-pdpstatistics.sql | 20 -------------------- .../upgrade/0180-jpapdpstatistics_enginestats.sql | 20 ++++++++++++++++++++ .../sql/0900/upgrade/0180-jpapolicyaudit.sql | 20 -------------------- .../upgrade/0190-JpaPolicyAuditIndex_timestamp.sql | 20 -------------------- .../sql/0900/upgrade/0190-jpapolicyaudit.sql | 20 ++++++++++++++++++++ .../upgrade/0200-JpaPolicyAuditIndex_timestamp.sql | 20 ++++++++++++++++++++ .../policyadmin/sql/0900/upgrade/0200-sequence.sql | 20 -------------------- .../policyadmin/sql/0900/upgrade/0210-sequence.sql | 2 +- .../policyadmin/sql/0900/upgrade/0220-sequence.sql | 20 ++++++++++++++++++++ 44 files changed, 414 insertions(+), 370 deletions(-) delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0100-jpapdpstatistics_enginestats.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0100-pdp.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0110-idx_tsidx1.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0110-jpapdpstatistics_enginestats.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0120-pdp.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0120-pk_pdpstatistics.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0130-idx_tsidx1.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0130-pdpstatistics.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0160-jpapdpstatistics_enginestats.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0160-pk_pdpstatistics.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0170-jpapdpstatistics_enginestats.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0170-pdpstatistics.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0180-jpapdpstatistics_enginestats.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0180-jpapolicyaudit.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0190-JpaPolicyAuditIndex_timestamp.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0190-jpapolicyaudit.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0200-JpaPolicyAuditIndex_timestamp.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0200-sequence.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0220-sequence.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0100-jpapdpstatistics_enginestats.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0100-pdp.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0110-idx_tsidx1.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0110-jpapdpstatistics_enginestats.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0120-pdp.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0120-pk_pdpstatistics.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0130-idx_tsidx1.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0130-pdpstatistics.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0160-jpapdpstatistics_enginestats.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0160-pk_pdpstatistics.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0170-jpapdpstatistics_enginestats.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0170-pdpstatistics.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0180-jpapdpstatistics_enginestats.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0180-jpapolicyaudit.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0190-JpaPolicyAuditIndex_timestamp.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0190-jpapolicyaudit.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0200-JpaPolicyAuditIndex_timestamp.sql delete mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0200-sequence.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0220-sequence.sql diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0100-jpapdpstatistics_enginestats.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0100-jpapdpstatistics_enginestats.sql deleted file mode 100644 index 64b1ad54..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0100-jpapdpstatistics_enginestats.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -ALTER TABLE jpapdpstatistics_enginestats ADD COLUMN timeStamp datetime DEFAULT NULL NULL AFTER UPTIME; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0100-pdp.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0100-pdp.sql new file mode 100644 index 00000000..f10c8501 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0100-pdp.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +ALTER TABLE pdp DROP COLUMN LASTUPDATE; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0110-idx_tsidx1.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0110-idx_tsidx1.sql new file mode 100644 index 00000000..bf9927a3 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0110-idx_tsidx1.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +DROP INDEX IDX_TSIDX1 ON pdpstatistics; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0110-jpapdpstatistics_enginestats.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0110-jpapdpstatistics_enginestats.sql deleted file mode 100644 index e3b5a487..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0110-jpapdpstatistics_enginestats.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -ALTER TABLE jpapdpstatistics_enginestats DROP COLUMN ID; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0120-pdp.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0120-pdp.sql deleted file mode 100644 index f10c8501..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0120-pdp.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -ALTER TABLE pdp DROP COLUMN LASTUPDATE; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0120-pk_pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0120-pk_pdpstatistics.sql new file mode 100644 index 00000000..b3d4767d --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0120-pk_pdpstatistics.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +ALTER TABLE pdpstatistics ADD CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (timeStamp, name, version); diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0130-idx_tsidx1.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0130-idx_tsidx1.sql deleted file mode 100644 index bf9927a3..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0130-idx_tsidx1.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -DROP INDEX IDX_TSIDX1 ON pdpstatistics; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0130-pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0130-pdpstatistics.sql new file mode 100644 index 00000000..461f147b --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0130-pdpstatistics.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +ALTER TABLE pdpstatistics DROP COLUMN POLICYUNDEPLOYCOUNT, DROP COLUMN POLICYUNDEPLOYFAILCOUNT, DROP COLUMN POLICYUNDEPLOYSUCCESSCOUNT, DROP COLUMN ID; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0140-pk_pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0140-pk_pdpstatistics.sql index b3d4767d..012344b9 100644 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0140-pk_pdpstatistics.sql +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0140-pk_pdpstatistics.sql @@ -17,4 +17,6 @@ * ============LICENSE_END========================================================= */ -ALTER TABLE pdpstatistics ADD CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (timeStamp, name, version); +ALTER TABLE pdpstatistics DROP CONSTRAINT PRIMARY KEY; + +UPDATE pdpstatistics set ID = 0; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0150-pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0150-pdpstatistics.sql index 461f147b..4bf55e84 100644 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0150-pdpstatistics.sql +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0150-pdpstatistics.sql @@ -17,4 +17,4 @@ * ============LICENSE_END========================================================= */ -ALTER TABLE pdpstatistics DROP COLUMN POLICYUNDEPLOYCOUNT, DROP COLUMN POLICYUNDEPLOYFAILCOUNT, DROP COLUMN POLICYUNDEPLOYSUCCESSCOUNT, DROP COLUMN ID; +ALTER TABLE pdpstatistics MODIFY COLUMN timeStamp datetime NOT NULL; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0160-jpapdpstatistics_enginestats.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0160-jpapdpstatistics_enginestats.sql new file mode 100644 index 00000000..e3b5a487 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0160-jpapdpstatistics_enginestats.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +ALTER TABLE jpapdpstatistics_enginestats DROP COLUMN ID; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0160-pk_pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0160-pk_pdpstatistics.sql deleted file mode 100644 index 012344b9..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0160-pk_pdpstatistics.sql +++ /dev/null @@ -1,22 +0,0 @@ -/* - * ============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========================================================= - */ - -ALTER TABLE pdpstatistics DROP CONSTRAINT PRIMARY KEY; - -UPDATE pdpstatistics set ID = 0; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0170-jpapdpstatistics_enginestats.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0170-jpapdpstatistics_enginestats.sql new file mode 100644 index 00000000..7163431b --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/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.id = b.id + SET a.timeStamp = b.timeStamp; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0170-pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0170-pdpstatistics.sql deleted file mode 100644 index 4bf55e84..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0170-pdpstatistics.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -ALTER TABLE pdpstatistics MODIFY COLUMN timeStamp datetime NOT NULL; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0180-jpapdpstatistics_enginestats.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0180-jpapdpstatistics_enginestats.sql new file mode 100644 index 00000000..64b1ad54 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0180-jpapdpstatistics_enginestats.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +ALTER TABLE jpapdpstatistics_enginestats ADD COLUMN timeStamp datetime DEFAULT NULL NULL AFTER UPTIME; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0180-jpapolicyaudit.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0180-jpapolicyaudit.sql deleted file mode 100644 index b51d8769..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0180-jpapolicyaudit.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -DROP TABLE IF EXISTS `jpapolicyaudit`; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0190-JpaPolicyAuditIndex_timestamp.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0190-JpaPolicyAuditIndex_timestamp.sql deleted file mode 100644 index 68265c5b..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0190-JpaPolicyAuditIndex_timestamp.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -ALTER TABLE jpapolicyaudit DROP INDEX JpaPolicyAuditIndex_timestamp; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0190-jpapolicyaudit.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0190-jpapolicyaudit.sql new file mode 100644 index 00000000..b51d8769 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0190-jpapolicyaudit.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +DROP TABLE IF EXISTS `jpapolicyaudit`; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0200-JpaPolicyAuditIndex_timestamp.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0200-JpaPolicyAuditIndex_timestamp.sql new file mode 100644 index 00000000..68265c5b --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0200-JpaPolicyAuditIndex_timestamp.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +ALTER TABLE jpapolicyaudit DROP INDEX JpaPolicyAuditIndex_timestamp; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0200-sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0200-sequence.sql deleted file mode 100644 index 553b1f16..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0200-sequence.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -DROP TABLE IF EXISTS `sequence`; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0210-sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0210-sequence.sql index 6e3eb083..553b1f16 100644 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0210-sequence.sql +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0210-sequence.sql @@ -17,4 +17,4 @@ * ============LICENSE_END========================================================= */ -DELETE from sequence; +DROP TABLE IF EXISTS `sequence`; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0220-sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0220-sequence.sql new file mode 100644 index 00000000..6e3eb083 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/downgrade/0220-sequence.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +DELETE from sequence; 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/0100-jpapdpstatistics_enginestats.sql deleted file mode 100644 index 16825f4f..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0100-jpapdpstatistics_enginestats.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -ALTER TABLE jpapdpstatistics_enginestats DROP COLUMN timeStamp; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0100-pdp.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0100-pdp.sql new file mode 100644 index 00000000..57a551da --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0100-pdp.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +ALTER TABLE pdp ADD COLUMN LASTUPDATE datetime DEFAULT NULL NULL AFTER HEALTHY; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0110-idx_tsidx1.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0110-idx_tsidx1.sql new file mode 100644 index 00000000..864e4f03 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0110-idx_tsidx1.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +CREATE INDEX IDX_TSIDX1 ON pdpstatistics(timeStamp, name, version); 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/0110-jpapdpstatistics_enginestats.sql deleted file mode 100644 index 6e5bfecd..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0110-jpapdpstatistics_enginestats.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -ALTER TABLE jpapdpstatistics_enginestats ADD COLUMN ID BIGINT DEFAULT NULL NULL AFTER UPTIME; 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/0120-pdp.sql deleted file mode 100644 index 57a551da..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0120-pdp.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -ALTER TABLE pdp ADD COLUMN LASTUPDATE datetime DEFAULT NULL NULL AFTER HEALTHY; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0120-pk_pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0120-pk_pdpstatistics.sql new file mode 100644 index 00000000..4066c012 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0120-pk_pdpstatistics.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +ALTER TABLE pdpstatistics DROP PRIMARY KEY; 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/0130-idx_tsidx1.sql deleted file mode 100644 index 864e4f03..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0130-idx_tsidx1.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -CREATE INDEX IDX_TSIDX1 ON pdpstatistics(timeStamp, name, version); diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0130-pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0130-pdpstatistics.sql new file mode 100644 index 00000000..c4a1123e --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0130-pdpstatistics.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +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/0160-jpapdpstatistics_enginestats.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0160-jpapdpstatistics_enginestats.sql new file mode 100644 index 00000000..6e5bfecd --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0160-jpapdpstatistics_enginestats.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +ALTER TABLE jpapdpstatistics_enginestats ADD COLUMN ID BIGINT DEFAULT NULL NULL AFTER UPTIME; 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/0160-pk_pdpstatistics.sql deleted file mode 100644 index b8a96b68..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0160-pk_pdpstatistics.sql +++ /dev/null @@ -1,22 +0,0 @@ -/* - * ============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 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/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/0170-pdpstatistics.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0170-pdpstatistics.sql deleted file mode 100644 index 98e26993..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0170-pdpstatistics.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -ALTER TABLE pdpstatistics MODIFY COLUMN timeStamp datetime NULL; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0180-jpapdpstatistics_enginestats.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0180-jpapdpstatistics_enginestats.sql new file mode 100644 index 00000000..16825f4f --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0180-jpapdpstatistics_enginestats.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +ALTER TABLE jpapdpstatistics_enginestats DROP COLUMN timeStamp; 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/0180-jpapolicyaudit.sql deleted file mode 100644 index a31d2421..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0180-jpapolicyaudit.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -CREATE TABLE IF NOT EXISTS jpapolicyaudit (ACTION INT DEFAULT NULL NULL, PDPGROUP VARCHAR(255) NULL, PDPTYPE VARCHAR(255) NULL, TIMESTAMP datetime DEFAULT NULL NULL, USER VARCHAR(255) NULL, ID BIGINT NOT NULL, name VARCHAR(120) NOT NULL, version VARCHAR(20) NOT NULL, CONSTRAINT PK_JPAPOLICYAUDIT PRIMARY KEY (ID, name, version)); 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/0190-JpaPolicyAuditIndex_timestamp.sql deleted file mode 100644 index d789d118..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0190-JpaPolicyAuditIndex_timestamp.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -CREATE INDEX JpaPolicyAuditIndex_timestamp ON jpapolicyaudit(TIMESTAMP); diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0190-jpapolicyaudit.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0190-jpapolicyaudit.sql new file mode 100644 index 00000000..a31d2421 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0190-jpapolicyaudit.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +CREATE TABLE IF NOT EXISTS jpapolicyaudit (ACTION INT DEFAULT NULL NULL, PDPGROUP VARCHAR(255) NULL, PDPTYPE VARCHAR(255) NULL, TIMESTAMP datetime DEFAULT NULL NULL, USER VARCHAR(255) NULL, ID BIGINT NOT NULL, name VARCHAR(120) NOT NULL, version VARCHAR(20) NOT NULL, CONSTRAINT PK_JPAPOLICYAUDIT PRIMARY KEY (ID, name, version)); diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0200-JpaPolicyAuditIndex_timestamp.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0200-JpaPolicyAuditIndex_timestamp.sql new file mode 100644 index 00000000..d789d118 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0200-JpaPolicyAuditIndex_timestamp.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +CREATE INDEX JpaPolicyAuditIndex_timestamp ON jpapolicyaudit(TIMESTAMP); 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/0200-sequence.sql deleted file mode 100644 index 64d6f01b..00000000 --- a/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0200-sequence.sql +++ /dev/null @@ -1,20 +0,0 @@ -/* - * ============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========================================================= - */ - -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/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/0220-sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0220-sequence.sql new file mode 100644 index 00000000..c90905ef --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/0900/upgrade/0220-sequence.sql @@ -0,0 +1,20 @@ +/* + * ============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========================================================= + */ + +INSERT INTO sequence(SEQ_NAME, SEQ_COUNT) VALUES('SEQ_GEN', (SELECT IFNULL(max(id),0) FROM pdpstatistics)); -- cgit 1.2.3-korg