Compatibility with mariadb 10.x and mysql 8.x

Additional support for extra flags for example is secured mysql/mariadb server
is desired when provisioning tables at initialization.

Issue-ID: POLICY-4183
Issue-ID: POLICY-4340
Signed-off-by: jhh <jorge.hernandez-herrero@att.com>
Change-Id: Id873b3692237cd867815f2928b56492c9261082e

3 files changed, 79 insertions, 20 deletions

diff --git a/packages/policy-xacmlpdp-tarball/src/main/resources/mysql/bin/create-guard-table.sh b/packages/policy-xacmlpdp-tarball/src/main/resources/mysql/bin/create-guard-table.sh
index 09a5d409..85350c46 100644
--- a/packages/policy-xacmlpdp-tarball/src/main/resources/mysql/bin/create-guard-table.sh
+++ b/packages/policy-xacmlpdp-tarball/src/main/resources/mysql/bin/create-guard-table.sh
@@ -20,7 +20,11 @@
 # SPDX-License-Identifier: Apache-2.0
 # ============LICENSE_END=========================================================
 #
+
+set -x
+
 SQL_FILE="${POLICY_HOME}/mysql/sql/createguardtable.sql"
+SQL_ADDON_FILE="${POLICY_HOME}/mysql/sql/db.sql"
 
 # Remove escape backslashes if present and save output in temp file
 sed 's/\\//g' "${POLICY_HOME}"/apps/guard/xacml.properties > /tmp/temp.xacml.properties
@@ -54,5 +58,15 @@ if [ -z "$DB_PASSWORD" ]; then
     exit 2
 fi
 
+if [ -z "$MYSQL_CMD" ]; then
+    MYSQL_CMD="mysql"
+fi
+
 # Execute sql command using sql file to create table
-mysql -u${DB_USERNAME} -p${DB_PASSWORD} -h${DB_HOSTNAME} < "${SQL_FILE}"
+${MYSQL_CMD} -u${DB_USERNAME} -p${DB_PASSWORD} -h${DB_HOSTNAME} < "${SQL_FILE}"
+
+# Execute additional SQL configuration if provided
+if [ -f "${POLICY_HOME}/mysql/sql/db.sql" ]; then
+    echo "additional SQL to be loaded found"
+    ${MYSQL_CMD} -u${DB_USERNAME} -p${DB_PASSWORD} -h${DB_HOSTNAME} < "${SQL_ADDON_FILE}"
+fi
diff --git a/packages/policy-xacmlpdp-tarball/src/main/resources/mysql/sql/createguardtable.sql b/packages/policy-xacmlpdp-tarball/src/main/resources/mysql/sql/createguardtable.sql
index aea02f70..34851a6f 100644
--- a/packages/policy-xacmlpdp-tarball/src/main/resources/mysql/sql/createguardtable.sql
+++ b/packages/policy-xacmlpdp-tarball/src/main/resources/mysql/sql/createguardtable.sql
@@ -1,5 +1,5 @@
 -- ============LICENSE_START=======================================================
--- Copyright (C) 2019-2021 AT&T Intellectual Property. All rights reserved.
+-- Copyright (C) 2019-2022 AT&T Intellectual Property. All rights reserved.
 -- ================================================================================
 -- Licensed under the Apache License, Version 2.0 (the "License");
 -- you may not use this file except in compliance with the License.
@@ -14,25 +14,58 @@
 -- limitations under the License.
 -- ============LICENSE_END=========================================================
 
-use operationshistory;
-
-create table if not exists operationshistory (
-    id int(11) not null auto_increment,
-    closedLoopName varchar(255) not null,
-    requestId varchar(50),
-    actor varchar(50) not null,
-    operation varchar(50) not null,
-    target varchar(50) not null,
-    starttime timestamp not null,
-    outcome varchar(50) not null,
-    message varchar(255),
-    subrequestId varchar(50),
-    endtime timestamp not null default current_timestamp,
+USE operationshistory;
+
+CREATE TABLE IF NOT EXISTS operationshistory (
+    id INT(11) NOT NULL AUTO_INCREMENT,
+    closedLoopName VARCHAR(255) NOT NULL,
+    requestId VARCHAR(50),
+    actor VARCHAR(50) NOT NULL,
+    operation VARCHAR(50) NOT NULL,
+    target VARCHAR(50) NOT NULL,
+    starttime timestamp NOT NULL,
+    outcome VARCHAR(50) NOT NULL,
+    message VARCHAR(255),
+    subrequestId VARCHAR(50),
+    endtime timestamp NULL DEFAULT current_timestamp,
     PRIMARY KEY (id)
 );
 
-create index if not exists operationshistory_clreqid_index on
-    operationshistory(requestId, closedLoopName);
+DROP PROCEDURE IF EXISTS create_clreqid_index;
+
+\d $$
+CREATE PROCEDURE create_clreqid_index()
+BEGIN
+    DECLARE index_count INT DEFAULT 1;
+
+    SELECT count(index_name) INTO index_count FROM information_schema.statistics
+    WHERE table_schema=DATABASE() AND table_name='operationshistory' AND index_name='operationshistory_clreqid_index';
+
+    IF index_count = 0 THEN
+        CREATE INDEX operationshistory_clreqid_index ON operationshistory(requestId, closedLoopName);
+    END IF;
+END
+$$
+
+\d ;
+
+CALL create_clreqid_index();
+
+DROP PROCEDURE IF EXISTS create_target_index;
+
+\d $$
+CREATE PROCEDURE create_target_index()
+BEGIN
+    DECLARE index_count INT DEFAULT 1;
+
+    SELECT count(index_name) INTO index_count FROM information_schema.statistics
+    WHERE table_schema=DATABASE() AND table_name='operationshistory' AND index_name='operationshistory_target_index';
+
+    IF index_count = 0 THEN
+        CREATE INDEX operationshistory_target_index ON operationshistory(target, operation, actor, endtime);
+    END IF;
+END
+$$
 
-create index if not exists operationshistory_target_index on
-    operationshistory(target, operation, actor, endtime);
+CALL create_target_index();
+\d ;
diff --git a/packages/policy-xacmlpdp-tarball/src/main/resources/postgres/bin/create-guard-table-pg.sh b/packages/policy-xacmlpdp-tarball/src/main/resources/postgres/bin/create-guard-table-pg.sh
index 1a71f8b4..bb2f2034 100644
--- a/packages/policy-xacmlpdp-tarball/src/main/resources/postgres/bin/create-guard-table-pg.sh
+++ b/packages/policy-xacmlpdp-tarball/src/main/resources/postgres/bin/create-guard-table-pg.sh
@@ -2,6 +2,7 @@
 #
 # ============LICENSE_START=======================================================
 #  Copyright (C) 2022 Nordix Foundation. All rights reserved.
+#  Modifications Copyright (C) 2022 AT&T Intellectual Property.
 # ================================================================================
 # Licensed under the Apache License, Version 2.0 (the "License");
 # you may not use this file except in compliance with the License.
@@ -18,7 +19,11 @@
 # SPDX-License-Identifier: Apache-2.0
 # ============LICENSE_END=========================================================
 #
+
+set -x
+
 SQL_FILE="${POLICY_HOME}/mysql/sql/createguardtable-pg.sql"
+SQL_ADDON_FILE="${POLICY_HOME}/mysql/sql/db-pg.sql"
 
 # Remove escape backslashes if present and save output in temp file
 sed 's/\\//g' "${POLICY_HOME}"/apps/guard/xacml-pg.properties > /tmp/temp.xacml-pg.properties
@@ -54,3 +59,10 @@ fi
 
 # Execute sql command using sql file to create table
 psql -U postgres -h ${DB_HOSTNAME} -f ${SQL_FILE}
+
+# Execute additional SQL configuration if provided
+if [ -f "${POLICY_HOME}/mysql/sql/db-pg.sql" ]; then
+    echo "additional SQL to be loaded found"
+    psql -U postgres -h ${DB_HOSTNAME} -f ${SQL_ADDON_FILE}
+fi
+