From 2585db2f2dd5eff989fd026509727b222018b85a Mon Sep 17 00:00:00 2001 From: waynedunican Date: Wed, 19 Apr 2023 17:15:51 +0100 Subject: [PATCH] Fix postgres sql syntax errors Issue-ID: POLICY-4649 Change-Id: I96bb55ddadae9449f5cb115dd294bf7630b93fcc Signed-off-by: waynedunican --- .../config/policyadmin/postgres/0800/upgrade/0460-pdppolicystatus.sql | 2 +- .../postgres/1200/downgrade/0100-jpapolicyaudit_renameuser.sql | 2 +- .../config/policyadmin/postgres/1200/downgrade/0110-idx_tsidx1.sql | 2 +- .../postgres/1200/upgrade/0100-jpapolicyaudit_renameuser.sql | 2 +- .../config/policyadmin/postgres/1200/upgrade/0110-idx_tsidx1.sql | 2 +- .../config/policyadmin/postgres/1200/upgrade/0120-audit_sequence.sql | 4 +++- .../policyadmin/postgres/1200/upgrade/0130-statistics_sequence.sql | 4 +++- 7 files changed, 11 insertions(+), 7 deletions(-) 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 d2a43b53..d193612a 100644 --- 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 8384df72..1362fa5b 100644 --- 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 8026e4b3..22f6ad37 100644 --- 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 9c6bfe6f..d910c4b0 100644 --- 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 334b6609..0d062901 100644 --- 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 e3909a83..fd56afef 100644 --- 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 ea4ed485..a7249f19 100644 --- 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)); -- 2.16.6