Fix postgres sql syntax errors

Issue-ID: POLICY-4649
Change-Id: I96bb55ddadae9449f5cb115dd294bf7630b93fcc
Signed-off-by: waynedunican <wayne.dunican@est.tech>

diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/0800/upgrade/0460-pdppolicystatus.sql b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/0800/upgrade/0460-pdppolicystatus.sql
index d2a43b5..d193612 100644 (file)
--- a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/0800/upgrade/0460-pdppolicystatus.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/0800/upgrade/0460-pdppolicystatus.sql
@@ -17,4 +17,4 @@
  *  ============LICENSE_END=========================================================
  */
 
-CREATE TABLE IF NOT EXISTS pdppolicystatus (DEPLOY BIT DEFAULT 0::bit NULL, PDPGROUP VARCHAR(255) NULL, PDPTYPE VARCHAR(255) NULL, STATE INT DEFAULT NULL NULL, parentLocalName VARCHAR(120) NOT NULL, localName VARCHAR(120) NOT NULL, parentKeyVersion VARCHAR(15) NOT NULL, parentKeyName VARCHAR(120) NOT NULL, name VARCHAR(120) NULL, version VARCHAR(20) NULL, CONSTRAINT PK_PDPPOLICYSTATUS PRIMARY KEY (parentLocalName, localName, parentKeyVersion, parentKeyName));
+CREATE TABLE IF NOT EXISTS pdppolicystatus (DEPLOY BOOLEAN NOT NULL, PDPGROUP VARCHAR(255) NULL, PDPTYPE VARCHAR(255) NULL, STATE INT DEFAULT NULL NULL, parentLocalName VARCHAR(120) NOT NULL, localName VARCHAR(120) NOT NULL, parentKeyVersion VARCHAR(15) NOT NULL, parentKeyName VARCHAR(120) NOT NULL, name VARCHAR(120) NULL, version VARCHAR(20) NULL, CONSTRAINT PK_PDPPOLICYSTATUS PRIMARY KEY (parentLocalName, localName, parentKeyVersion, parentKeyName));

diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0100-jpapolicyaudit_renameuser.sql b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0100-jpapolicyaudit_renameuser.sql
index 8384df7..1362fa5 100644 (file)
--- a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0100-jpapolicyaudit_renameuser.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0100-jpapolicyaudit_renameuser.sql
@@ -17,4 +17,4 @@
  *  ============LICENSE_END=========================================================
  */
 
-ALTER TABLE jpapolicyaudit RENAME COLUMN USERNAME TO USER;
+ALTER TABLE jpapolicyaudit RENAME COLUMN USERNAME TO "USER";

diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0110-idx_tsidx1.sql b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0110-idx_tsidx1.sql
index 8026e4b..22f6ad3 100644 (file)
--- a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0110-idx_tsidx1.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/downgrade/0110-idx_tsidx1.sql
@@ -17,5 +17,5 @@
  *  ============LICENSE_END=========================================================
  */
 
-DROP INDEX IDXTSIDX1 ON pdpstatistics;
+DROP INDEX IDXTSIDX1;
 CREATE INDEX IDX_TSIDX1 ON pdpstatistics(timeStamp, name, version);

diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0100-jpapolicyaudit_renameuser.sql b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0100-jpapolicyaudit_renameuser.sql
index 9c6bfe6..d910c4b 100644 (file)
--- a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0100-jpapolicyaudit_renameuser.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0100-jpapolicyaudit_renameuser.sql
@@ -17,4 +17,4 @@
  *  ============LICENSE_END=========================================================
  */
 
-ALTER TABLE jpapolicyaudit RENAME COLUMN USER TO USERNAME;
+ALTER TABLE jpapolicyaudit RENAME COLUMN "USER" TO USERNAME;

diff --git a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0110-idx_tsidx1.sql b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0110-idx_tsidx1.sql
index 334b660..0d06290 100644 (file)
--- a/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0110-idx_tsidx1.sql
+++ b/policy-db-migrator/src/main/docker/config/policyadmin/postgres/1200/upgrade/0110-idx_tsidx1.sql
@@ -17,5 +17,5 @@
  *  ============LICENSE_END=========================================================
  */
 
-DROP INDEX IDX_TSIDX1 ON pdpstatistics;
+DROP INDEX IDX_TSIDX1;
 CREATE INDEX IDXTSIDX1 ON pdpstatistics(timeStamp, name, version);

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
index e3909a8..fd56afe 100644 (file)
--- 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
@@ -17,4 +17,6 @@
  *  ============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));
+CREATE TABLE IF NOT EXISTS audit_sequence (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT DECIMAL(38) DEFAULT NULL NULL, CONSTRAINT PK_AUDIT_SEQUENCE PRIMARY KEY (SEQ_NAME));
+
+INSERT INTO audit_sequence(SEQ_NAME, SEQ_COUNT) VALUES('SEQ_GEN', (SELECT COALESCE(max(id),0) FROM jpapolicyaudit));

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
index ea4ed48..a7249f1 100644 (file)
--- 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
@@ -17,4 +17,6 @@
  *  ============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));
+CREATE TABLE IF NOT EXISTS statistics_sequence (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT DECIMAL(38) DEFAULT NULL NULL, CONSTRAINT PK_STATS_SEQUENCE PRIMARY KEY (SEQ_NAME));
+
+INSERT INTO statistics_sequence(SEQ_NAME, SEQ_COUNT) VALUES('SEQ_GEN', (SELECT COALESCE(max(id),0) FROM pdpstatistics));