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>
diff --git a/policy-db-migrator/src/main/docker/Dockerfile b/policy-db-migrator/src/main/docker/Dockerfile
index 8f632c8..5e1fef5 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-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 0000000..a1af817
--- /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 0000000..eb34800
--- /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 0000000..cbb775b
--- /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 0000000..0515bcb
--- /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 0000000..a1af817
--- /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 0000000..eb34800
--- /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 0000000..cbb775b
--- /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 0000000..d4e4aee
--- /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 0000000..fa21382
--- /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 0000000..5df8a14
--- /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 0000000..7567676
--- /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 0000000..e7250ba
--- /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 2261a39..4e1a3df 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 49ed2e6..e282f47 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 @@
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 @@
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 @@
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 @@
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 @@
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