From 1ed9d7b38cdce5890fbe8408f13ff3a95d279f24 Mon Sep 17 00:00:00 2001 From: liamfallon Date: Thu, 9 Feb 2023 11:11:55 +0000 Subject: Add sequence tables for Hibernate Hibernate does not allow the same sequence table to be used for more than one sequence number generator JPA class. Issue-ID: POLICY-4533 Change-Id: If7e70164a5da7e0678aeb192d563bafc213f8bb6 Signed-off-by: liamfallon Signed-off-by: adheli.tavares --- .../1200/downgrade/0120-audit_sequence.sql | 27 ++++++++++++++++++++++ .../1200/downgrade/0130-statistics_sequence.sql | 20 ++++++++++++++++ .../postgres/1200/upgrade/0120-audit_sequence.sql | 20 ++++++++++++++++ .../1200/upgrade/0130-statistics_sequence.sql | 20 ++++++++++++++++ .../sql/1200/downgrade/0120-audit_sequence.sql | 27 ++++++++++++++++++++++ .../1200/downgrade/0130-statistics_sequence.sql | 20 ++++++++++++++++ .../sql/1200/upgrade/0120-audit_sequence.sql | 22 ++++++++++++++++++ .../sql/1200/upgrade/0130-statistics_sequence.sql | 22 ++++++++++++++++++ 8 files changed, 178 insertions(+) create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0120-audit_sequence.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0130-statistics_sequence.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0120-audit_sequence.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0130-statistics_sequence.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/downgrade/0120-audit_sequence.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/downgrade/0130-statistics_sequence.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/upgrade/0120-audit_sequence.sql create mode 100644 policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/upgrade/0130-statistics_sequence.sql diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0120-audit_sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0120-audit_sequence.sql new file mode 100644 index 00000000..3446e33a --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0120-audit_sequence.sql @@ -0,0 +1,27 @@ +/* + * ============LICENSE_START======================================================= + * Copyright (C) 2023 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 COALESCE(MAX(id), 0) FROM ( + SELECT MAX(id) as id FROM pdpstatistics + UNION + SELECT MAX(id) as id FROM jpapolicyaudit +) AS subquery)); + +DROP TABLE audit_sequence; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0130-statistics_sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0130-statistics_sequence.sql new file mode 100644 index 00000000..aea12698 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0130-statistics_sequence.sql @@ -0,0 +1,20 @@ +/* + * ============LICENSE_START======================================================= + * Copyright (C) 2023 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 statistics_sequence; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0120-audit_sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0120-audit_sequence.sql new file mode 100644 index 00000000..e3909a83 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0120-audit_sequence.sql @@ -0,0 +1,20 @@ +/* + * ============LICENSE_START======================================================= + * Copyright (C) 2023 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 audit_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/postgres/1200/upgrade/0130-statistics_sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0130-statistics_sequence.sql new file mode 100644 index 00000000..ea4ed485 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0130-statistics_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========================================================= + */ + +CREATE TABLE IF NOT EXISTS statistics_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/1200/downgrade/0120-audit_sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/downgrade/0120-audit_sequence.sql new file mode 100644 index 00000000..c1cc38bb --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/downgrade/0120-audit_sequence.sql @@ -0,0 +1,27 @@ +/* + * ============LICENSE_START======================================================= + * Copyright (C) 2023 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 ( + SELECT MAX(id) as id FROM pdpstatistics + UNION + SELECT MAX(id) as id FROM jpapolicyaudit +) AS subquery)); + +DROP TABLE audit_sequence; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/downgrade/0130-statistics_sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/downgrade/0130-statistics_sequence.sql new file mode 100644 index 00000000..aea12698 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/downgrade/0130-statistics_sequence.sql @@ -0,0 +1,20 @@ +/* + * ============LICENSE_START======================================================= + * Copyright (C) 2023 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 statistics_sequence; diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/upgrade/0120-audit_sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/upgrade/0120-audit_sequence.sql new file mode 100644 index 00000000..6f7b62a1 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/upgrade/0120-audit_sequence.sql @@ -0,0 +1,22 @@ +/* + * ============LICENSE_START======================================================= + * Copyright (C) 2023 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 audit_sequence (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT DECIMAL(38) DEFAULT NULL, PRIMARY KEY PK_SEQUENCE (SEQ_NAME)); + +INSERT INTO audit_sequence(SEQ_NAME, SEQ_COUNT) VALUES('SEQ_GEN', (SELECT IFNULL(max(id),0) FROM jpapolicyaudit)); diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/upgrade/0130-statistics_sequence.sql b/policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/upgrade/0130-statistics_sequence.sql new file mode 100644 index 00000000..7215d811 --- /dev/null +++ b/policy-db-migrator/src/main/docker/config/policyadmin/sql/1200/upgrade/0130-statistics_sequence.sql @@ -0,0 +1,22 @@ +/* + * ============LICENSE_START======================================================= + * Copyright (C) 2023 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 statistics_sequence (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT DECIMAL(38) DEFAULT NULL, PRIMARY KEY PK_SEQUENCE (SEQ_NAME)); + +INSERT INTO statistics_sequence(SEQ_NAME, SEQ_COUNT) VALUES('SEQ_GEN', (SELECT IFNULL(max(id),0) FROM pdpstatistics)); -- cgit 1.2.3-korg