diff options
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 |