Moving drools and xacml schema/tables management to db-migrator

- operationshistory is a shared table for drools and xacml, hence
moving it also
- postgres support added with taking management away from
components.

Issue-ID: POLICY-5107
Change-Id: I9bd263336bd7e65ab8812af63467773b48d388a4
Signed-off-by: adheli.tavares <adheli.tavares@est.tech>

15 files changed, 317 insertions, 11 deletions

diff --git a/policy-db-migrator/src/main/docker/Dockerfile b/policy-db-migrator/src/main/docker/Dockerfile
index 8f632c87..5e1fef5b 100644
--- a/policy-db-migrator/src/main/docker/Dockerfile
+++ b/policy-db-migrator/src/main/docker/Dockerfile
@@ -54,10 +54,7 @@ COPY --chown=policy:policy ./db-migrator $POLICY_BIN/
 COPY --chown=policy:policy ./db-migrator-pg $POLICY_BIN/
 COPY --chown=policy:policy ./prepare_upgrade.sh $POLICY_BIN/
 COPY --chown=policy:policy ./prepare_downgrade.sh $POLICY_BIN/
-COPY --chown=policy:policy ./config/policyadmin/sql /home/policyadmin/sql
-COPY --chown=policy:policy ./config/policyadmin/postgres /home/policyadmin/postgres
-COPY --chown=policy:policy ./config/clampacm/sql /home/clampacm/sql
-COPY --chown=policy:policy ./config/clampacm/postgres /home/clampacm/postgres
+COPY --chown=policy:policy ./config /home
 
 WORKDIR $POLICY_BIN
 USER policy:policy
diff --git a/policy-db-migrator/src/main/docker/config/operationshistory/postgres/1600/downgrade/0100-ophistory_id_sequence.sql b/policy-db-migrator/src/main/docker/config/operationshistory/postgres/1600/downgrade/0100-ophistory_id_sequence.sql
new file mode 100644
index 00000000..a1af817d
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/operationshistory/postgres/1600/downgrade/0100-ophistory_id_sequence.sql
@@ -0,0 +1,19 @@
+/*
+*  ============LICENSE_START=======================================================
+*  Copyright (C) 2024 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.
+*  ============LICENSE_END=========================================================
+*/
+
+DROP TABLE IF EXISTS ophistory_id_sequence;
diff --git a/policy-db-migrator/src/main/docker/config/operationshistory/postgres/1600/downgrade/0110-operationshistory.sql b/policy-db-migrator/src/main/docker/config/operationshistory/postgres/1600/downgrade/0110-operationshistory.sql
new file mode 100644
index 00000000..eb348002
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/operationshistory/postgres/1600/downgrade/0110-operationshistory.sql
@@ -0,0 +1,19 @@
+/*
+*  ============LICENSE_START=======================================================
+*  Copyright (C) 2024 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.
+*  ============LICENSE_END=========================================================
+*/
+
+DROP TABLE IF EXISTS operationshistory;
diff --git a/policy-db-migrator/src/main/docker/config/operationshistory/postgres/1600/upgrade/0100-ophistory_id_sequence.sql b/policy-db-migrator/src/main/docker/config/operationshistory/postgres/1600/upgrade/0100-ophistory_id_sequence.sql
new file mode 100644
index 00000000..cbb775b0
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/operationshistory/postgres/1600/upgrade/0100-ophistory_id_sequence.sql
@@ -0,0 +1,28 @@
+/*
+*  ============LICENSE_START=======================================================
+*  Copyright (C) 2024 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.
+*  ============LICENSE_END=========================================================
+*/
+
+CREATE TABLE ophistory_id_sequence
+(
+    SEQ_NAME  VARCHAR(50) NOT NULL,
+    SEQ_COUNT BIGINT      NOT NULL,
+    PRIMARY KEY (SEQ_NAME)
+);
+
+-- Initialize the sequence
+INSERT INTO ophistory_id_sequence (SEQ_NAME, SEQ_COUNT)
+VALUES ('SEQ_GEN', 1);
diff --git a/policy-db-migrator/src/main/docker/config/operationshistory/postgres/1600/upgrade/0110-operationshistory.sql b/policy-db-migrator/src/main/docker/config/operationshistory/postgres/1600/upgrade/0110-operationshistory.sql
new file mode 100644
index 00000000..0515bcb7
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/operationshistory/postgres/1600/upgrade/0110-operationshistory.sql
@@ -0,0 +1,36 @@
+/*
+*  ============LICENSE_START=======================================================
+*  Copyright (C) 2024 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.
+*  ============LICENSE_END=========================================================
+*/
+
+CREATE TABLE operationshistory
+(
+    id             BIGINT NOT NULL,
+    closedLoopName VARCHAR(255),
+    requestId      VARCHAR(50),
+    subrequestId   VARCHAR(50),
+    actor          VARCHAR(50),
+    operation      VARCHAR(50),
+    target         VARCHAR(50),
+    starttime      TIMESTAMP,
+    outcome        VARCHAR(50),
+    message        VARCHAR(255),
+    endtime        TIMESTAMP,
+    PRIMARY KEY (id)
+);
+
+CREATE INDEX operationshistory_clreqid_index ON operationshistory (requestId, closedLoopName);
+CREATE INDEX operationshistory_target_index ON operationshistory (target, operation, actor, endtime);
diff --git a/policy-db-migrator/src/main/docker/config/operationshistory/sql/1600/downgrade/0100-ophistory_id_sequence.sql b/policy-db-migrator/src/main/docker/config/operationshistory/sql/1600/downgrade/0100-ophistory_id_sequence.sql
new file mode 100644
index 00000000..a1af817d
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/operationshistory/sql/1600/downgrade/0100-ophistory_id_sequence.sql
@@ -0,0 +1,19 @@
+/*
+*  ============LICENSE_START=======================================================
+*  Copyright (C) 2024 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.
+*  ============LICENSE_END=========================================================
+*/
+
+DROP TABLE IF EXISTS ophistory_id_sequence;
diff --git a/policy-db-migrator/src/main/docker/config/operationshistory/sql/1600/downgrade/0110-operationshistory.sql b/policy-db-migrator/src/main/docker/config/operationshistory/sql/1600/downgrade/0110-operationshistory.sql
new file mode 100644
index 00000000..eb348002
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/operationshistory/sql/1600/downgrade/0110-operationshistory.sql
@@ -0,0 +1,19 @@
+/*
+*  ============LICENSE_START=======================================================
+*  Copyright (C) 2024 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.
+*  ============LICENSE_END=========================================================
+*/
+
+DROP TABLE IF EXISTS operationshistory;
diff --git a/policy-db-migrator/src/main/docker/config/operationshistory/sql/1600/upgrade/0100-ophistory_id_sequence.sql b/policy-db-migrator/src/main/docker/config/operationshistory/sql/1600/upgrade/0100-ophistory_id_sequence.sql
new file mode 100644
index 00000000..cbb775b0
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/operationshistory/sql/1600/upgrade/0100-ophistory_id_sequence.sql
@@ -0,0 +1,28 @@
+/*
+*  ============LICENSE_START=======================================================
+*  Copyright (C) 2024 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.
+*  ============LICENSE_END=========================================================
+*/
+
+CREATE TABLE ophistory_id_sequence
+(
+    SEQ_NAME  VARCHAR(50) NOT NULL,
+    SEQ_COUNT BIGINT      NOT NULL,
+    PRIMARY KEY (SEQ_NAME)
+);
+
+-- Initialize the sequence
+INSERT INTO ophistory_id_sequence (SEQ_NAME, SEQ_COUNT)
+VALUES ('SEQ_GEN', 1);
diff --git a/policy-db-migrator/src/main/docker/config/operationshistory/sql/1600/upgrade/0110-operationshistory.sql b/policy-db-migrator/src/main/docker/config/operationshistory/sql/1600/upgrade/0110-operationshistory.sql
new file mode 100644
index 00000000..d4e4aee2
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/operationshistory/sql/1600/upgrade/0110-operationshistory.sql
@@ -0,0 +1,36 @@
+/*
+*  ============LICENSE_START=======================================================
+*  Copyright (C) 2024 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.
+*  ============LICENSE_END=========================================================
+*/
+
+CREATE TABLE operationshistory (
+    id BIGINT NOT NULL,
+    closedLoopName VARCHAR(255),
+    requestId VARCHAR(50),
+    subrequestId VARCHAR(50),
+    actor VARCHAR(50),
+    operation VARCHAR(50),
+    target VARCHAR(50),
+    starttime DATETIME,
+    outcome VARCHAR(50),
+    message VARCHAR(255),
+    endtime DATETIME,
+    PRIMARY KEY (id)
+);
+
+CREATE INDEX operationshistory_clreqid_index ON operationshistory (requestId, closedLoopName);
+CREATE INDEX operationshistory_target_index ON operationshistory (target, operation, actor, endtime);
+
diff --git a/policy-db-migrator/src/main/docker/config/pooling/postgres/1600/downgrade/0100-distributed.locking.sql b/policy-db-migrator/src/main/docker/config/pooling/postgres/1600/downgrade/0100-distributed.locking.sql
new file mode 100644
index 00000000..fa213827
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/pooling/postgres/1600/downgrade/0100-distributed.locking.sql
@@ -0,0 +1,21 @@
+/*
+*  ============LICENSE_START=======================================================
+*  feature-distributed-locking
+*  ===============================================================================
+*  Copyright (C) 2024 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.
+*  ============LICENSE_END=========================================================
+*/
+
+DROP TABLE IF EXISTS locks;
diff --git a/policy-db-migrator/src/main/docker/config/pooling/postgres/1600/upgrade/0100-distributed.locking.sql b/policy-db-migrator/src/main/docker/config/pooling/postgres/1600/upgrade/0100-distributed.locking.sql
new file mode 100644
index 00000000..5df8a14f
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/pooling/postgres/1600/upgrade/0100-distributed.locking.sql
@@ -0,0 +1,31 @@
+/*
+*  ============LICENSE_START=======================================================
+*  feature-distributed-locking
+*  ===============================================================================
+*  Copyright (C) 2024 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.
+*  ============LICENSE_END=========================================================
+*/
+
+CREATE TABLE IF NOT EXISTS locks
+(
+    resourceId     VARCHAR(128) PRIMARY KEY,
+    host           VARCHAR(128),
+    owner          VARCHAR(128),
+    expirationTime TIMESTAMP DEFAULT '1971-01-01 00:00:00'
+);
+
+-- Add indexes
+CREATE INDEX idx_expirationTime ON locks(expirationTime);
+CREATE INDEX idx_host ON locks(host);
diff --git a/policy-db-migrator/src/main/docker/config/pooling/sql/1600/downgrade/0100-distributed.locking.sql b/policy-db-migrator/src/main/docker/config/pooling/sql/1600/downgrade/0100-distributed.locking.sql
new file mode 100644
index 00000000..75676767
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/pooling/sql/1600/downgrade/0100-distributed.locking.sql
@@ -0,0 +1,21 @@
+/*
+*  ============LICENSE_START=======================================================
+*  feature-distributed-locking
+*  ================================================================================
+*  Copyright (C) 2024 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.
+*  ============LICENSE_END=========================================================
+*/
+
+drop table if exists locks;
diff --git a/policy-db-migrator/src/main/docker/config/pooling/sql/1600/upgrade/0100-distributed.locking.sql b/policy-db-migrator/src/main/docker/config/pooling/sql/1600/upgrade/0100-distributed.locking.sql
new file mode 100644
index 00000000..e7250bac
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/config/pooling/sql/1600/upgrade/0100-distributed.locking.sql
@@ -0,0 +1,32 @@
+/*
+*  ============LICENSE_START=======================================================
+*  feature-distributed-locking
+*  ================================================================================
+*  Copyright (C) 2024 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.
+*  ============LICENSE_END=========================================================
+*/
+
+SET foreign_key_checks=0;
+
+CREATE TABLE if NOT EXISTS locks(
+    resourceId VARCHAR(128),
+    host VARCHAR(128),
+    owner VARCHAR(128),
+    expirationTime TIMESTAMP DEFAULT '1971-01-01 00:00:00.000000',
+    PRIMARY KEY (resourceId),
+    INDEX idx_expirationTime(expirationTime),
+    INDEX idx_host(host));
+
+SET foreign_key_checks=1;
diff --git a/policy-db-migrator/src/main/docker/db-migrator b/policy-db-migrator/src/main/docker/db-migrator
index 2261a394..4e1a3df7 100755
--- a/policy-db-migrator/src/main/docker/db-migrator
+++ b/policy-db-migrator/src/main/docker/db-migrator
@@ -41,7 +41,7 @@
 # to be upgraded/downgraded.
 #
 # The repository of upgrade/downgrade scripts is located in the
-# /home/${SQL_DB}/sql directory.
+# /home/${SCHEMA}/sql directory.
 # Two additional scripts have been provided to prepare the directories/files
 # needed to perform the upgrade/downgrade.
 #
diff --git a/policy-db-migrator/src/main/docker/db-migrator-pg b/policy-db-migrator/src/main/docker/db-migrator-pg
index 49ed2e61..e282f47b 100755
--- a/policy-db-migrator/src/main/docker/db-migrator-pg
+++ b/policy-db-migrator/src/main/docker/db-migrator-pg
@@ -41,7 +41,7 @@
 # to be upgraded/downgraded.
 #
 # The repository of upgrade/downgrade scripts is located in the
-# /home/${SQL_DB}/sql directory.
+# /home/${SCHEMA}/sql directory.
 # Two additional scripts have been provided to prepare the directories/files
 # needed to perform the upgrade/downgrade.
 #
@@ -111,7 +111,7 @@ function current_schema() {
     local rc
     local query="SELECT count(table_name) from information_schema.tables where table_schema='${SCHEMA}'"
 
-    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SQL_DB}"
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SCHEMA}"
     TABLES_IN_SCHEMA=$(${PSQL} -X -A --tuples-only --quiet --command "${query}")
     if [ ${TABLES_IN_SCHEMA} -gt 0 ] && [ "${BASE_VERSION}" \> "${CURRENT_RELEASE}" ]; then
         set_current_release "${BASE_VERSION}"
@@ -274,7 +274,7 @@ function current_release() {
     fi
 
     local rc
-    local query="SELECT version FROM schema_versions WHERE name='${SQL_DB}'"
+    local query="SELECT version FROM schema_versions WHERE name='${SCHEMA}'"
 
     PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
     CURRENT_RELEASE=$(${PSQL} -X -A -t --command "${query}")
@@ -387,7 +387,7 @@ function run_script() {
     echo
     echo "> ${operation} ${script}"
 
-    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SQL_DB}"
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SCHEMA}"
     ${PSQL} <"${scriptPath}"
     local rc=$?
     if [ ${rc} -ne 0 ]; then
@@ -633,7 +633,7 @@ if [ -z "${SQL_HOST}" ] || [ -z "${SQL_USER}" ] || [ -z "${SQL_PASSWORD}" ]; the
     exit 4
 fi
 
-PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SQL_DB}"
+PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SCHEMA}"
 if ! ${PSQL} --command '\l'; then
     echo "error: No DB connectivity to ${SQL_HOST} for ${SQL_USER}"
     exit 5
@@ -703,7 +703,7 @@ for dbPath in ${SCHEMA_S}; do
         fi
     fi
 
-    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SQL_DB};"
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SCHEMA};"
     # Check if the schema has already been installed
     current_schema
     RETRY=0