From e497f34620e5c9b0658671ac749cdb871c882892 Mon Sep 17 00:00:00 2001 From: danielhanrahan Date: Fri, 1 Aug 2025 11:49:38 +0100 Subject: [PATCH] Convert SQL to Liquibase YAML (Liquibase #11) Each SQL statement is replaced with an equivalent Liquibase change type, such as createTable, addColumn, etc. Issue-ID: POLICY-5398 Change-Id: Ib500871968a31fe8cae41d31de1b97f5f1196771 Signed-off-by: danielhanrahan --- .../resources/db/changelog/changelog-1400.yaml | 340 +++++++++---- .../resources/db/changelog/changelog-1500.yaml | 121 +++-- .../resources/db/changelog/changelog-1600.yaml | 24 +- .../resources/db/changelog/changelog-1601.yaml | 27 +- .../resources/db/changelog/changelog-1700.yaml | 73 ++- .../resources/db/changelog/changelog-1701.yaml | 557 +++++++++++++++------ .../resources/db/changelog/changelog-1702.yaml | 63 ++- 7 files changed, 854 insertions(+), 351 deletions(-) diff --git a/runtime-acm/src/main/resources/db/changelog/changelog-1400.yaml b/runtime-acm/src/main/resources/db/changelog/changelog-1400.yaml index d185aa38b..d682e0978 100644 --- a/runtime-acm/src/main/resources/db/changelog/changelog-1400.yaml +++ b/runtime-acm/src/main/resources/db/changelog/changelog-1400.yaml @@ -28,21 +28,43 @@ databaseChangeLog: - tableExists: tableName: AutomationComposition changes: - - sql: - sql: | - CREATE TABLE AutomationComposition ( - instanceId VARCHAR(255) NOT NULL, - compositionId VARCHAR(255), - compositionTargetId VARCHAR(255), - deployState SMALLINT DEFAULT NULL, - description VARCHAR(255), - lockState SMALLINT DEFAULT NULL, - name VARCHAR(255), - restarting BOOLEAN, - stateChangeResult SMALLINT DEFAULT NULL, - version VARCHAR(255), - CONSTRAINT PK_AUTOMATIONCOMPOSITION PRIMARY KEY (instanceId) - ); + - createTable: + tableName: AutomationComposition + columns: + - column: + name: instanceId + type: VARCHAR(255) + constraints: + primaryKey: true + primaryKeyName: pk_automationcomposition + nullable: false + - column: + name: compositionId + type: VARCHAR(255) + - column: + name: compositionTargetId + type: VARCHAR(255) + - column: + name: deployState + type: SMALLINT + - column: + name: description + type: VARCHAR(255) + - column: + name: lockState + type: SMALLINT + - column: + name: name + type: VARCHAR(255) + - column: + name: restarting + type: BOOLEAN + - column: + name: stateChangeResult + type: SMALLINT + - column: + name: version + type: VARCHAR(255) - changeSet: id: 1400-2 @@ -53,18 +75,34 @@ databaseChangeLog: - tableExists: tableName: AutomationCompositionDefinition changes: - - sql: - sql: | - CREATE TABLE AutomationCompositionDefinition ( - compositionId VARCHAR(255) NOT NULL, - name VARCHAR(255), - restarting BOOLEAN, - serviceTemplate TEXT, - state SMALLINT DEFAULT NULL, - stateChangeResult SMALLINT DEFAULT NULL, - version VARCHAR(255), - CONSTRAINT PK_AUTOMATIONCOMPOSITIONDEFINITION PRIMARY KEY (compositionId) - ); + - createTable: + tableName: AutomationCompositionDefinition + columns: + - column: + name: compositionId + type: VARCHAR(255) + constraints: + primaryKey: true + primaryKeyName: pk_automationcompositiondefinition + nullable: false + - column: + name: name + type: VARCHAR(255) + - column: + name: restarting + type: BOOLEAN + - column: + name: serviceTemplate + type: TEXT + - column: + name: state + type: SMALLINT + - column: + name: stateChangeResult + type: SMALLINT + - column: + name: version + type: VARCHAR(255) - changeSet: id: 1400-3 @@ -75,25 +113,55 @@ databaseChangeLog: - tableExists: tableName: AutomationCompositionElement changes: - - sql: - sql: | - CREATE TABLE AutomationCompositionElement ( - elementId VARCHAR(255) NOT NULL, - definition_name VARCHAR(255), - definition_version VARCHAR(255), - deployState SMALLINT DEFAULT NULL, - description VARCHAR(255), - instanceId VARCHAR(255), - lockState SMALLINT DEFAULT NULL, - message VARCHAR(255), - operationalState VARCHAR(255), - outProperties TEXT, - participantId VARCHAR(255), - properties TEXT, - restarting BOOLEAN, - useState VARCHAR(255), - CONSTRAINT PK_AUTOMATIONCOMPOSITIONELEMENT PRIMARY KEY (elementId) - ); + - createTable: + tableName: AutomationCompositionElement + columns: + - column: + name: elementId + type: VARCHAR(255) + constraints: + primaryKey: true + primaryKeyName: pk_automationcompositionelement + nullable: false + - column: + name: definition_name + type: VARCHAR(255) + - column: + name: definition_version + type: VARCHAR(255) + - column: + name: deployState + type: SMALLINT + - column: + name: description + type: VARCHAR(255) + - column: + name: instanceId + type: VARCHAR(255) + - column: + name: lockState + type: SMALLINT + - column: + name: message + type: VARCHAR(255) + - column: + name: operationalState + type: VARCHAR(255) + - column: + name: outProperties + type: TEXT + - column: + name: participantId + type: VARCHAR(255) + - column: + name: properties + type: TEXT + - column: + name: restarting + type: BOOLEAN + - column: + name: useState + type: VARCHAR(255) - changeSet: id: 1400-4 @@ -104,20 +172,40 @@ databaseChangeLog: - tableExists: tableName: NodeTemplateState changes: - - sql: - sql: | - CREATE TABLE NodeTemplateState ( - nodeTemplateStateId VARCHAR(255) NOT NULL, - compositionId VARCHAR(255), - message VARCHAR(255), - nodeTemplate_name VARCHAR(255), - nodeTemplate_version VARCHAR(255), - outProperties TEXT, - participantId VARCHAR(255), - restarting BOOLEAN, - state SMALLINT DEFAULT NULL, - CONSTRAINT PK_NODETEMPLATESTATE PRIMARY KEY (nodeTemplateStateId) - ); + - createTable: + tableName: NodeTemplateState + columns: + - column: + name: nodeTemplateStateId + type: VARCHAR(255) + constraints: + primaryKey: true + primaryKeyName: pk_nodetemplatestate + nullable: false + - column: + name: compositionId + type: VARCHAR(255) + - column: + name: message + type: VARCHAR(255) + - column: + name: nodeTemplate_name + type: VARCHAR(255) + - column: + name: nodeTemplate_version + type: VARCHAR(255) + - column: + name: outProperties + type: TEXT + - column: + name: participantId + type: VARCHAR(255) + - column: + name: restarting + type: BOOLEAN + - column: + name: state + type: SMALLINT - changeSet: id: 1400-5 @@ -128,14 +216,22 @@ databaseChangeLog: - tableExists: tableName: Participant changes: - - sql: - sql: | - CREATE TABLE Participant ( - participantId VARCHAR(255) NOT NULL, - description VARCHAR(255), - participantState SMALLINT DEFAULT NULL, - CONSTRAINT PK_PARTICIPANT PRIMARY KEY (participantId) - ); + - createTable: + tableName: Participant + columns: + - column: + name: participantId + type: VARCHAR(255) + constraints: + primaryKey: true + primaryKeyName: pk_participant + nullable: false + - column: + name: description + type: VARCHAR(255) + - column: + name: participantState + type: SMALLINT - changeSet: id: 1400-6 @@ -146,15 +242,25 @@ databaseChangeLog: - tableExists: tableName: ParticipantSupportedAcElements changes: - - sql: - sql: | - CREATE TABLE ParticipantSupportedAcElements ( - id VARCHAR(255) NOT NULL, - participantId VARCHAR(255), - typeName VARCHAR(255), - typeVersion VARCHAR(255), - CONSTRAINT PK_PARTICIPANTSUPPORTEDACELEMENTS PRIMARY KEY (id) - ); + - createTable: + tableName: ParticipantSupportedAcElements + columns: + - column: + name: id + type: VARCHAR(255) + constraints: + primaryKey: true + primaryKeyName: pk_participantsupportedacelements + nullable: false + - column: + name: participantId + type: VARCHAR(255) + - column: + name: typeName + type: VARCHAR(255) + - column: + name: typeVersion + type: VARCHAR(255) - changeSet: id: 1400-7 @@ -165,8 +271,12 @@ databaseChangeLog: - indexExists: indexName: ac_compositionId changes: - - sql: - sql: CREATE INDEX ac_compositionId ON AutomationComposition(compositionId); + - createIndex: + indexName: ac_compositionId + tableName: AutomationComposition + columns: + - column: + name: compositionId - changeSet: id: 1400-8 @@ -177,8 +287,12 @@ databaseChangeLog: - indexExists: indexName: ac_element_fk changes: - - sql: - sql: CREATE INDEX ac_element_fk ON AutomationCompositionElement(instanceId); + - createIndex: + indexName: ac_element_fk + tableName: AutomationCompositionElement + columns: + - column: + name: instanceId - changeSet: id: 1400-9 @@ -189,8 +303,12 @@ databaseChangeLog: - indexExists: indexName: dt_element_fk changes: - - sql: - sql: CREATE INDEX dt_element_fk ON NodeTemplateState(compositionId); + - createIndex: + indexName: dt_element_fk + tableName: NodeTemplateState + columns: + - column: + name: compositionId - changeSet: id: 1400-10 @@ -201,8 +319,12 @@ databaseChangeLog: - indexExists: indexName: supported_element_fk changes: - - sql: - sql: CREATE INDEX supported_element_fk ON ParticipantSupportedAcElements(participantId); + - createIndex: + indexName: supported_element_fk + tableName: ParticipantSupportedAcElements + columns: + - column: + name: participantId - changeSet: id: 1400-11 @@ -214,14 +336,14 @@ databaseChangeLog: foreignKeyName: ac_element_fk foreignKeyTableName: AutomationCompositionElement changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionElement - ADD CONSTRAINT ac_element_fk - FOREIGN KEY (instanceId) - REFERENCES AutomationComposition (instanceId) - ON UPDATE RESTRICT - ON DELETE RESTRICT; + - addForeignKeyConstraint: + baseTableName: AutomationCompositionElement + baseColumnNames: instanceId + constraintName: ac_element_fk + referencedTableName: AutomationComposition + referencedColumnNames: instanceId + onUpdate: RESTRICT + onDelete: RESTRICT - changeSet: id: 1400-12 @@ -233,14 +355,14 @@ databaseChangeLog: foreignKeyName: dt_element_fk foreignKeyTableName: NodeTemplateState changes: - - sql: - sql: | - ALTER TABLE NodeTemplateState - ADD CONSTRAINT dt_element_fk - FOREIGN KEY (compositionId) - REFERENCES AutomationCompositionDefinition (compositionId) - ON UPDATE RESTRICT - ON DELETE RESTRICT; + - addForeignKeyConstraint: + baseTableName: NodeTemplateState + baseColumnNames: compositionId + constraintName: dt_element_fk + referencedTableName: AutomationCompositionDefinition + referencedColumnNames: compositionId + onUpdate: RESTRICT + onDelete: RESTRICT - changeSet: id: 1400-13 @@ -252,11 +374,11 @@ databaseChangeLog: foreignKeyName: supported_element_fk foreignKeyTableName: ParticipantSupportedAcElements changes: - - sql: - sql: | - ALTER TABLE ParticipantSupportedAcElements - ADD CONSTRAINT supported_element_fk - FOREIGN KEY (participantId) - REFERENCES Participant (participantId) - ON UPDATE RESTRICT - ON DELETE RESTRICT; + - addForeignKeyConstraint: + baseTableName: ParticipantSupportedAcElements + baseColumnNames: participantId + constraintName: supported_element_fk + referencedTableName: Participant + referencedColumnNames: participantId + onUpdate: RESTRICT + onDelete: RESTRICT diff --git a/runtime-acm/src/main/resources/db/changelog/changelog-1500.yaml b/runtime-acm/src/main/resources/db/changelog/changelog-1500.yaml index 28c8b7ca5..e3b271f09 100644 --- a/runtime-acm/src/main/resources/db/changelog/changelog-1500.yaml +++ b/runtime-acm/src/main/resources/db/changelog/changelog-1500.yaml @@ -29,8 +29,13 @@ databaseChangeLog: tableName: AutomationComposition columnName: lastMsg changes: - - sql: - sql: ALTER TABLE AutomationComposition ADD lastMsg timestamp without time zone DEFAULT Now(); + - addColumn: + tableName: AutomationComposition + columns: + - column: + name: lastMsg + type: TIMESTAMP + defaultValueComputed: CURRENT_TIMESTAMP - changeSet: id: 1500-2 @@ -42,8 +47,13 @@ databaseChangeLog: tableName: AutomationComposition columnName: phase changes: - - sql: - sql: ALTER TABLE AutomationComposition ADD phase SMALLINT DEFAULT 0; + - addColumn: + tableName: AutomationComposition + columns: + - column: + name: phase + type: SMALLINT + defaultValueNumeric: 0 - changeSet: id: 1500-3 @@ -55,8 +65,13 @@ databaseChangeLog: tableName: AutomationCompositionDefinition columnName: lastMsg changes: - - sql: - sql: ALTER TABLE AutomationCompositionDefinition ADD lastMsg timestamp without time zone DEFAULT Now(); + - addColumn: + tableName: AutomationCompositionDefinition + columns: + - column: + name: lastMsg + type: TIMESTAMP + defaultValueComputed: CURRENT_TIMESTAMP - changeSet: id: 1500-4 @@ -67,15 +82,26 @@ databaseChangeLog: - tableExists: tableName: ParticipantReplica changes: - - sql: - sql: | - CREATE TABLE ParticipantReplica ( - replicaId varchar(255) NOT NULL, - lastMsg timestamp without time zone DEFAULT Now(), - participantId varchar(255) DEFAULT NULL, - participantState SMALLINT DEFAULT NULL, - CONSTRAINT PK_PARTICIPANT_REPLICA PRIMARY KEY (replicaId) - ); + - createTable: + tableName: ParticipantReplica + columns: + - column: + name: replicaId + type: VARCHAR(255) + constraints: + primaryKey: true + primaryKeyName: pk_participant_replica + nullable: false + - column: + name: lastMsg + type: TIMESTAMP + defaultValueComputed: CURRENT_TIMESTAMP + - column: + name: participantId + type: VARCHAR(255) + - column: + name: participantState + type: SMALLINT - changeSet: id: 1500-5 @@ -87,8 +113,13 @@ databaseChangeLog: tableName: Participant columnName: lastMsg changes: - - sql: - sql: ALTER TABLE Participant ADD lastMsg timestamp without time zone DEFAULT Now(); + - addColumn: + tableName: Participant + columns: + - column: + name: lastMsg + type: TIMESTAMP + defaultValueComputed: CURRENT_TIMESTAMP - changeSet: id: 1500-6 @@ -99,8 +130,12 @@ databaseChangeLog: - indexExists: indexName: participant_replica_fk changes: - - sql: - sql: CREATE INDEX participant_replica_fk ON ParticipantReplica(participantId); + - createIndex: + indexName: participant_replica_fk + tableName: ParticipantReplica + columns: + - column: + name: participantId - changeSet: id: 1500-7 @@ -112,14 +147,14 @@ databaseChangeLog: foreignKeyName: participant_replica_fk foreignKeyTableName: ParticipantReplica changes: - - sql: - sql: | - ALTER TABLE ParticipantReplica - ADD CONSTRAINT participant_replica_fk - FOREIGN KEY (participantId) - REFERENCES Participant (participantId) - ON UPDATE RESTRICT - ON DELETE RESTRICT; + - addForeignKeyConstraint: + baseTableName: ParticipantReplica + baseColumnNames: participantId + constraintName: participant_replica_fk + referencedTableName: Participant + referencedColumnNames: participantId + onUpdate: RESTRICT + onDelete: RESTRICT - changeSet: id: 1500-8 @@ -130,8 +165,12 @@ databaseChangeLog: tableName: AutomationComposition columnName: restarting changes: - - sql: - sql: UPDATE AutomationComposition SET restarting = NULL; + - update: + tableName: AutomationComposition + columns: + - column: + name: restarting + value: NULL - changeSet: id: 1500-9 @@ -142,8 +181,12 @@ databaseChangeLog: tableName: AutomationCompositionDefinition columnName: restarting changes: - - sql: - sql: UPDATE AutomationCompositionDefinition SET restarting = NULL; + - update: + tableName: AutomationCompositionDefinition + columns: + - column: + name: restarting + value: NULL - changeSet: id: 1500-10 @@ -154,8 +197,12 @@ databaseChangeLog: tableName: AutomationCompositionElement columnName: restarting changes: - - sql: - sql: UPDATE AutomationCompositionElement SET restarting = NULL; + - update: + tableName: AutomationCompositionElement + columns: + - column: + name: restarting + value: NULL - changeSet: id: 1500-11 @@ -166,5 +213,9 @@ databaseChangeLog: tableName: NodeTemplateState columnName: restarting changes: - - sql: - sql: UPDATE NodeTemplateState SET restarting = NULL; + - update: + tableName: NodeTemplateState + columns: + - column: + name: restarting + value: NULL diff --git a/runtime-acm/src/main/resources/db/changelog/changelog-1600.yaml b/runtime-acm/src/main/resources/db/changelog/changelog-1600.yaml index 0077657b0..b2ce6d8e2 100644 --- a/runtime-acm/src/main/resources/db/changelog/changelog-1600.yaml +++ b/runtime-acm/src/main/resources/db/changelog/changelog-1600.yaml @@ -29,8 +29,12 @@ databaseChangeLog: tableName: AutomationComposition columnName: subState changes: - - sql: - sql: ALTER TABLE AutomationComposition ADD subState SMALLINT DEFAULT NULL; + - addColumn: + tableName: AutomationComposition + columns: + - column: + name: subState + type: SMALLINT - changeSet: id: 1600-2 @@ -42,8 +46,12 @@ databaseChangeLog: tableName: AutomationCompositionElement columnName: subState changes: - - sql: - sql: ALTER TABLE AutomationCompositionElement ADD subState SMALLINT DEFAULT NULL; + - addColumn: + tableName: AutomationCompositionElement + columns: + - column: + name: subState + type: SMALLINT - changeSet: id: 1600-3 @@ -55,5 +63,9 @@ databaseChangeLog: tableName: AutomationCompositionElement columnName: stage changes: - - sql: - sql: ALTER TABLE AutomationCompositionElement ADD stage SMALLINT DEFAULT NULL; + - addColumn: + tableName: AutomationCompositionElement + columns: + - column: + name: stage + type: SMALLINT diff --git a/runtime-acm/src/main/resources/db/changelog/changelog-1601.yaml b/runtime-acm/src/main/resources/db/changelog/changelog-1601.yaml index 4fb1b1bbe..2b4092e6c 100644 --- a/runtime-acm/src/main/resources/db/changelog/changelog-1601.yaml +++ b/runtime-acm/src/main/resources/db/changelog/changelog-1601.yaml @@ -23,19 +23,34 @@ databaseChangeLog: id: 1601-1 author: policy changes: - - sql: - sql: UPDATE AutomationComposition SET subState=0 WHERE subState is NULL; + - update: + tableName: AutomationComposition + columns: + - column: + name: subState + valueNumeric: 0 + where: subState IS NULL - changeSet: id: 1601-2 author: policy changes: - - sql: - sql: UPDATE AutomationCompositionElement SET subState=0 WHERE subState is NULL; + - update: + tableName: AutomationCompositionElement + columns: + - column: + name: subState + valueNumeric: 0 + where: subState IS NULL - changeSet: id: 1601-3 author: policy changes: - - sql: - sql: UPDATE AutomationCompositionElement SET stage=0 WHERE stage is NULL; + - update: + tableName: AutomationCompositionElement + columns: + - column: + name: stage + valueNumeric: 0 + where: stage IS NULL diff --git a/runtime-acm/src/main/resources/db/changelog/changelog-1700.yaml b/runtime-acm/src/main/resources/db/changelog/changelog-1700.yaml index 396072a4d..527d7eafb 100644 --- a/runtime-acm/src/main/resources/db/changelog/changelog-1700.yaml +++ b/runtime-acm/src/main/resources/db/changelog/changelog-1700.yaml @@ -28,15 +28,32 @@ databaseChangeLog: - tableExists: tableName: Message changes: - - sql: - sql: | - CREATE TABLE Message ( - messageId varchar(255) NOT NULL, - identificationId varchar(255) NOT NULL, - lastMsg timestamp without time zone NOT NULL DEFAULT now(), - docMessage text NOT NULL, - CONSTRAINT PK_MESSAGE PRIMARY KEY (messageId) - ); + - createTable: + tableName: Message + columns: + - column: + name: messageId + type: VARCHAR(255) + constraints: + primaryKey: true + primaryKeyName: pk_message + nullable: false + - column: + name: identificationId + type: VARCHAR(255) + constraints: + nullable: false + - column: + name: lastMsg + type: TIMESTAMP + defaultValueComputed: CURRENT_TIMESTAMP + constraints: + nullable: false + - column: + name: docMessage + type: TEXT + constraints: + nullable: false - changeSet: id: 1700-2 @@ -47,14 +64,27 @@ databaseChangeLog: - tableExists: tableName: MessageJob changes: - - sql: - sql: | - CREATE TABLE MessageJob ( - jobId varchar(255) NOT NULL, - identificationId varchar(255) NOT NULL, - jobStarted timestamp without time zone NOT NULL DEFAULT now(), - CONSTRAINT PK_MESSAGE_JOB PRIMARY KEY (jobId) - ); + - createTable: + tableName: MessageJob + columns: + - column: + name: jobId + type: VARCHAR(255) + constraints: + primaryKey: true + primaryKeyName: pk_message_job + nullable: false + - column: + name: identificationId + type: VARCHAR(255) + constraints: + nullable: false + - column: + name: jobStarted + type: TIMESTAMP + defaultValueComputed: CURRENT_TIMESTAMP + constraints: + nullable: false - changeSet: id: 1700-3 @@ -65,5 +95,10 @@ databaseChangeLog: - indexExists: indexName: messagejob_identificationId_index changes: - - sql: - sql: CREATE UNIQUE INDEX messagejob_identificationId_index ON MessageJob(identificationId); + - createIndex: + tableName: MessageJob + indexName: messagejob_identificationId_index + unique: true + columns: + - column: + name: identificationId diff --git a/runtime-acm/src/main/resources/db/changelog/changelog-1701.yaml b/runtime-acm/src/main/resources/db/changelog/changelog-1701.yaml index 7092d1caf..43fcc09fe 100644 --- a/runtime-acm/src/main/resources/db/changelog/changelog-1701.yaml +++ b/runtime-acm/src/main/resources/db/changelog/changelog-1701.yaml @@ -28,149 +28,277 @@ databaseChangeLog: - tableExists: tableName: AutomationCompositionRollback changes: - - sql: - sql: | - CREATE TABLE AutomationCompositionRollback ( - instanceId VARCHAR(255) NOT NULL, - compositionId VARCHAR(255) NOT NULL, - elements TEXT NOT NULL, - CONSTRAINT PK_AUTOMATIONCOMPOSITION_ROLLBACK PRIMARY KEY (instanceId) - ); + - createTable: + tableName: AutomationCompositionRollback + columns: + - column: + name: instanceId + type: VARCHAR(255) + constraints: + primaryKey: true + primaryKeyName: pk_automationcomposition_rollback + nullable: false + - column: + name: compositionId + type: VARCHAR(255) + constraints: + nullable: false + - column: + name: elements + type: TEXT + constraints: + nullable: false - changeSet: id: 1701-2 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationComposition ALTER COLUMN compositionId SET NOT NULL; + - addNotNullConstraint: + tableName: AutomationComposition + columnName: compositionId - changeSet: id: 1701-3 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationComposition ALTER COLUMN name SET DEFAULT ''; - UPDATE AutomationComposition SET name = '' WHERE name IS NULL; - ALTER TABLE AutomationComposition ALTER COLUMN name SET NOT NULL; + - addDefaultValue: + tableName: AutomationComposition + columnName: name + defaultValue: '' + - update: + tableName: AutomationComposition + columns: + - column: + name: name + value: '' + where: name IS NULL + - addNotNullConstraint: + tableName: AutomationComposition + columnName: name - changeSet: id: 1701-4 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationComposition ALTER COLUMN version SET DEFAULT '1.0.0'; - UPDATE AutomationComposition SET version = '1.0.0' WHERE version IS NULL; - ALTER TABLE AutomationComposition ALTER COLUMN version SET NOT NULL; + - addDefaultValue: + tableName: AutomationComposition + columnName: version + defaultValue: '1.0.0' + - update: + tableName: AutomationComposition + columns: + - column: + name: version + value: '1.0.0' + where: version IS NULL + - addNotNullConstraint: + tableName: AutomationComposition + columnName: version - changeSet: id: 1701-5 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationComposition ALTER COLUMN deployState SET DEFAULT 2; - UPDATE AutomationComposition SET deployState = 2 WHERE deployState IS NULL; - ALTER TABLE AutomationComposition ALTER COLUMN deployState SET NOT NULL; + - addDefaultValue: + tableName: AutomationComposition + columnName: deployState + defaultValueNumeric: 2 + - update: + tableName: AutomationComposition + columns: + - column: + name: deployState + valueNumeric: 2 + where: deployState IS NULL + - addNotNullConstraint: + tableName: AutomationComposition + columnName: deployState - changeSet: id: 1701-6 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationComposition ALTER COLUMN lockState SET DEFAULT 4; - UPDATE AutomationComposition SET lockState = 4 WHERE lockState IS NULL; - ALTER TABLE AutomationComposition ALTER COLUMN lockState SET NOT NULL; + - addDefaultValue: + tableName: AutomationComposition + columnName: lockState + defaultValueNumeric: 4 + - update: + tableName: AutomationComposition + columns: + - column: + name: lockState + valueNumeric: 4 + where: lockState IS NULL + - addNotNullConstraint: + tableName: AutomationComposition + columnName: lockState - changeSet: id: 1701-7 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationComposition ALTER COLUMN subState SET DEFAULT 0; - UPDATE AutomationComposition SET subState = 0 WHERE subState IS NULL; - ALTER TABLE AutomationComposition ALTER COLUMN subState SET NOT NULL; + - addDefaultValue: + tableName: AutomationComposition + columnName: subState + defaultValueNumeric: 0 + - update: + tableName: AutomationComposition + columns: + - column: + name: subState + valueNumeric: 0 + where: subState IS NULL + - addNotNullConstraint: + tableName: AutomationComposition + columnName: subState - changeSet: id: 1701-8 author: policy changes: - - sql: - sql: | - UPDATE AutomationComposition SET lastMsg = now() WHERE lastMsg IS NULL; - ALTER TABLE AutomationComposition ALTER COLUMN lastMsg SET NOT NULL; + - update: + tableName: AutomationComposition + columns: + - column: + name: lastMsg + valueComputed: CURRENT_TIMESTAMP + where: lastMsg IS NULL + - addNotNullConstraint: + tableName: AutomationComposition + columnName: lastMsg - changeSet: id: 1701-9 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionElement ALTER COLUMN definition_name SET DEFAULT ''; - UPDATE AutomationCompositionElement SET definition_name = '' WHERE definition_name IS NULL; - ALTER TABLE AutomationCompositionElement ALTER COLUMN definition_name SET NOT NULL; + - addDefaultValue: + tableName: AutomationCompositionElement + columnName: definition_name + defaultValue: '' + - update: + tableName: AutomationCompositionElement + columns: + - column: + name: definition_name + value: '' + where: definition_name IS NULL + - addNotNullConstraint: + tableName: AutomationCompositionElement + columnName: definition_name - changeSet: id: 1701-10 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionElement ALTER COLUMN definition_version SET DEFAULT '0.0.0'; - UPDATE AutomationCompositionElement SET definition_version = '0.0.0' WHERE definition_version IS NULL; - ALTER TABLE AutomationCompositionElement ALTER COLUMN definition_version SET NOT NULL; + - addDefaultValue: + tableName: AutomationCompositionElement + columnName: definition_version + defaultValue: '0.0.0' + - update: + tableName: AutomationCompositionElement + columns: + - column: + name: definition_version + value: '0.0.0' + where: definition_version IS NULL + - addNotNullConstraint: + tableName: AutomationCompositionElement + columnName: definition_version - changeSet: id: 1701-11 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionElement ALTER COLUMN deployState SET DEFAULT 2; - UPDATE AutomationCompositionElement SET deployState = 2 WHERE deployState IS NULL; - ALTER TABLE AutomationCompositionElement ALTER COLUMN deployState SET NOT NULL; + - addDefaultValue: + tableName: AutomationCompositionElement + columnName: deployState + defaultValueNumeric: 2 + - update: + tableName: AutomationCompositionElement + columns: + - column: + name: deployState + valueNumeric: 2 + where: deployState IS NULL + - addNotNullConstraint: + tableName: AutomationCompositionElement + columnName: deployState - changeSet: id: 1701-12 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionElement ALTER COLUMN lockState SET DEFAULT 4; - UPDATE AutomationCompositionElement SET lockState = 4 WHERE lockState IS NULL; - ALTER TABLE AutomationCompositionElement ALTER COLUMN lockState SET NOT NULL; + - addDefaultValue: + tableName: AutomationCompositionElement + columnName: lockState + defaultValueNumeric: 4 + - update: + tableName: AutomationCompositionElement + columns: + - column: + name: lockState + valueNumeric: 4 + where: lockState IS NULL + - addNotNullConstraint: + tableName: AutomationCompositionElement + columnName: lockState - changeSet: id: 1701-13 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionElement ALTER COLUMN subState SET DEFAULT 0; - UPDATE AutomationCompositionElement SET subState = 0 WHERE subState IS NULL; - ALTER TABLE AutomationCompositionElement ALTER COLUMN subState SET NOT NULL; + - addDefaultValue: + tableName: AutomationCompositionElement + columnName: subState + defaultValueNumeric: 0 + - update: + tableName: AutomationCompositionElement + columns: + - column: + name: subState + valueNumeric: 0 + where: subState IS NULL + - addNotNullConstraint: + tableName: AutomationCompositionElement + columnName: subState - changeSet: id: 1701-14 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionElement ALTER COLUMN outProperties SET DEFAULT '{}'; - ALTER TABLE AutomationCompositionElement ALTER COLUMN outProperties SET NOT NULL; + - addDefaultValue: + tableName: AutomationCompositionElement + columnName: outProperties + defaultValue: '{}' + - update: + tableName: AutomationCompositionElement + columns: + - column: + name: outProperties + value: '{}' + where: outProperties IS NULL + - addNotNullConstraint: + tableName: AutomationCompositionElement + columnName: outProperties - changeSet: id: 1701-15 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionElement ALTER COLUMN properties SET DEFAULT '{}'; - ALTER TABLE AutomationCompositionElement ALTER COLUMN properties SET NOT NULL; + - addDefaultValue: + tableName: AutomationCompositionElement + columnName: properties + defaultValue: '{}' + - update: + tableName: AutomationCompositionElement + columns: + - column: + name: properties + value: '{}' + where: properties IS NULL + - addNotNullConstraint: + tableName: AutomationCompositionElement + columnName: properties - changeSet: id: 1701-16 @@ -182,103 +310,181 @@ databaseChangeLog: foreignKeyName: ac_composition_fk foreignKeyTableName: AutomationComposition changes: - - sql: - sql: | - ALTER TABLE AutomationComposition - ADD CONSTRAINT ac_composition_fk - FOREIGN KEY (compositionId) - REFERENCES AutomationCompositionDefinition (compositionId) - ON UPDATE RESTRICT - ON DELETE RESTRICT; + - addForeignKeyConstraint: + baseTableName: AutomationComposition + baseColumnNames: compositionId + constraintName: ac_composition_fk + referencedTableName: AutomationCompositionDefinition + referencedColumnNames: compositionId + onUpdate: RESTRICT + onDelete: RESTRICT - changeSet: id: 1701-17 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionDefinition ALTER COLUMN name SET DEFAULT ''; - UPDATE AutomationCompositionDefinition SET name = '' WHERE name IS NULL; - ALTER TABLE AutomationCompositionDefinition ALTER COLUMN name SET NOT NULL; + - addDefaultValue: + tableName: AutomationCompositionDefinition + columnName: name + defaultValue: '' + - update: + tableName: AutomationCompositionDefinition + columns: + - column: + name: name + value: '' + where: name IS NULL + - addNotNullConstraint: + tableName: AutomationCompositionDefinition + columnName: name - changeSet: id: 1701-18 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionDefinition ALTER COLUMN version SET DEFAULT '1.0.0'; - UPDATE AutomationCompositionDefinition SET version = '1.0.0' WHERE version IS NULL; - ALTER TABLE AutomationCompositionDefinition ALTER COLUMN version SET NOT NULL; + - addDefaultValue: + tableName: AutomationCompositionDefinition + columnName: version + defaultValue: '1.0.0' + - update: + tableName: AutomationCompositionDefinition + columns: + - column: + name: version + value: '1.0.0' + where: version IS NULL + - addNotNullConstraint: + tableName: AutomationCompositionDefinition + columnName: version - changeSet: id: 1701-19 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionDefinition ALTER COLUMN state SET DEFAULT 0; - UPDATE AutomationCompositionDefinition SET state = 0 WHERE state IS NULL; - ALTER TABLE AutomationCompositionDefinition ALTER COLUMN state SET NOT NULL; + - addDefaultValue: + tableName: AutomationCompositionDefinition + columnName: state + defaultValueNumeric: 0 + - update: + tableName: AutomationCompositionDefinition + columns: + - column: + name: state + valueNumeric: 0 + where: state IS NULL + - addNotNullConstraint: + tableName: AutomationCompositionDefinition + columnName: state - changeSet: id: 1701-20 author: policy changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionDefinition ALTER COLUMN serviceTemplate SET DEFAULT ''; - UPDATE AutomationCompositionDefinition SET serviceTemplate = '' WHERE serviceTemplate IS NULL; - ALTER TABLE AutomationCompositionDefinition ALTER COLUMN serviceTemplate SET NOT NULL; + - addDefaultValue: + tableName: AutomationCompositionDefinition + columnName: serviceTemplate + defaultValue: '' + - update: + tableName: AutomationCompositionDefinition + columns: + - column: + name: serviceTemplate + value: '' + where: serviceTemplate IS NULL + - addNotNullConstraint: + tableName: AutomationCompositionDefinition + columnName: serviceTemplate - changeSet: id: 1701-21 author: policy changes: - - sql: - sql: | - UPDATE AutomationCompositionDefinition SET lastMsg = now() WHERE lastMsg IS NULL; - ALTER TABLE AutomationCompositionDefinition ALTER COLUMN lastMsg SET NOT NULL; + - update: + tableName: AutomationCompositionDefinition + columns: + - column: + name: lastMsg + valueComputed: CURRENT_TIMESTAMP + where: lastMsg IS NULL + - addNotNullConstraint: + tableName: AutomationCompositionDefinition + columnName: lastMsg - changeSet: id: 1701-22 author: policy changes: - - sql: - sql: | - ALTER TABLE NodeTemplateState ALTER COLUMN nodeTemplate_name SET DEFAULT ''; - UPDATE NodeTemplateState SET nodeTemplate_name = '' WHERE nodeTemplate_name IS NULL; - ALTER TABLE NodeTemplateState ALTER COLUMN nodeTemplate_name SET NOT NULL; + - addDefaultValue: + tableName: NodeTemplateState + columnName: nodeTemplate_name + defaultValue: '' + - update: + tableName: NodeTemplateState + columns: + - column: + name: nodeTemplate_name + value: '' + where: nodeTemplate_name IS NULL + - addNotNullConstraint: + tableName: NodeTemplateState + columnName: nodeTemplate_name - changeSet: id: 1701-23 author: policy changes: - - sql: - sql: | - ALTER TABLE NodeTemplateState ALTER COLUMN nodeTemplate_version SET DEFAULT '1.0.0'; - UPDATE NodeTemplateState SET nodeTemplate_version = '1.0.0' WHERE nodeTemplate_version IS NULL; - ALTER TABLE NodeTemplateState ALTER COLUMN nodeTemplate_version SET NOT NULL; + - addDefaultValue: + tableName: NodeTemplateState + columnName: nodeTemplate_version + defaultValue: '1.0.0' + - update: + tableName: NodeTemplateState + columns: + - column: + name: nodeTemplate_version + value: '1.0.0' + where: nodeTemplate_version IS NULL + - addNotNullConstraint: + tableName: NodeTemplateState + columnName: nodeTemplate_version - changeSet: id: 1701-24 author: policy changes: - - sql: - sql: | - ALTER TABLE NodeTemplateState ALTER COLUMN outProperties SET DEFAULT '{}'; - UPDATE NodeTemplateState SET outProperties = '{}' WHERE outProperties IS NULL; - ALTER TABLE NodeTemplateState ALTER COLUMN outProperties SET NOT NULL; + - addDefaultValue: + tableName: NodeTemplateState + columnName: outProperties + defaultValue: '{}' + - update: + tableName: NodeTemplateState + columns: + - column: + name: outProperties + value: '{}' + where: outProperties IS NULL + - addNotNullConstraint: + tableName: NodeTemplateState + columnName: outProperties - changeSet: id: 1701-25 author: policy changes: - - sql: - sql: | - ALTER TABLE NodeTemplateState ALTER COLUMN state SET DEFAULT 0; - UPDATE NodeTemplateState SET state = 0 WHERE state IS NULL; - ALTER TABLE NodeTemplateState ALTER COLUMN state SET NOT NULL; + - addDefaultValue: + tableName: NodeTemplateState + columnName: state + defaultValueNumeric: 0 + - update: + tableName: NodeTemplateState + columns: + - column: + name: state + valueNumeric: 0 + where: state IS NULL + - addNotNullConstraint: + tableName: NodeTemplateState + columnName: state - changeSet: id: 1701-26 @@ -289,62 +495,105 @@ databaseChangeLog: - indexExists: indexName: mb_identificationId_index changes: - - sql: - sql: CREATE INDEX mb_identificationId_index ON Message(identificationId); + - createIndex: + tableName: Message + indexName: mb_identificationId_index + columns: + - column: + name: identificationId - changeSet: id: 1701-27 author: policy changes: - - sql: - sql: | - UPDATE ParticipantReplica SET lastMsg = now() WHERE lastMsg IS NULL; - ALTER TABLE ParticipantReplica ALTER COLUMN lastMsg SET NOT NULL; + - update: + tableName: ParticipantReplica + columns: + - column: + name: lastMsg + valueComputed: CURRENT_TIMESTAMP + where: lastMsg IS NULL + - addNotNullConstraint: + tableName: ParticipantReplica + columnName: lastMsg - changeSet: id: 1701-28 author: policy changes: - - sql: - sql: | - ALTER TABLE ParticipantReplica ALTER COLUMN participantId SET DEFAULT ''; - ALTER TABLE ParticipantReplica ALTER COLUMN participantId SET NOT NULL; + - addDefaultValue: + tableName: ParticipantReplica + columnName: participantId + defaultValue: '' + - addNotNullConstraint: + tableName: ParticipantReplica + columnName: participantId - changeSet: id: 1701-29 author: policy changes: - - sql: - sql: | - ALTER TABLE ParticipantReplica ALTER COLUMN participantState SET DEFAULT 1; - UPDATE ParticipantReplica SET participantState = '1' WHERE participantState IS NULL; - ALTER TABLE ParticipantReplica ALTER COLUMN participantState SET NOT NULL; + - addDefaultValue: + tableName: ParticipantReplica + columnName: participantState + defaultValueNumeric: 1 + - update: + tableName: ParticipantReplica + columns: + - column: + name: participantState + valueNumeric: 1 + where: participantState IS NULL + - addNotNullConstraint: + tableName: ParticipantReplica + columnName: participantState - changeSet: id: 1701-30 author: policy changes: - - sql: - sql: | - ALTER TABLE ParticipantSupportedAcElements ALTER COLUMN participantId SET DEFAULT ''; - ALTER TABLE ParticipantSupportedAcElements ALTER COLUMN participantId SET NOT NULL; + - addDefaultValue: + tableName: ParticipantSupportedAcElements + columnName: participantId + defaultValue: '' + - addNotNullConstraint: + tableName: ParticipantSupportedAcElements + columnName: participantId - changeSet: id: 1701-31 author: policy changes: - - sql: - sql: | - ALTER TABLE ParticipantSupportedAcElements ALTER COLUMN typeName SET DEFAULT ''; - UPDATE ParticipantSupportedAcElements SET typeName = '' WHERE typeName IS NULL; - ALTER TABLE ParticipantSupportedAcElements ALTER COLUMN typeName SET NOT NULL; + - addDefaultValue: + tableName: ParticipantSupportedAcElements + columnName: typeName + defaultValue: '' + - update: + tableName: ParticipantSupportedAcElements + columns: + - column: + name: typeName + value: '' + where: typeName IS NULL + - addNotNullConstraint: + tableName: ParticipantSupportedAcElements + columnName: typeName - changeSet: id: 1701-32 author: policy changes: - - sql: - sql: | - ALTER TABLE ParticipantSupportedAcElements ALTER COLUMN typeVersion SET DEFAULT '1.0.0'; - UPDATE ParticipantSupportedAcElements SET typeVersion = '1.0.0' WHERE typeVersion IS NULL; - ALTER TABLE ParticipantSupportedAcElements ALTER COLUMN typeVersion SET NOT NULL; + - addDefaultValue: + tableName: ParticipantSupportedAcElements + columnName: typeVersion + defaultValue: '1.0.0' + - update: + tableName: ParticipantSupportedAcElements + columns: + - column: + name: typeVersion + value: '1.0.0' + where: typeVersion IS NULL + - addNotNullConstraint: + tableName: ParticipantSupportedAcElements + columnName: typeVersion diff --git a/runtime-acm/src/main/resources/db/changelog/changelog-1702.yaml b/runtime-acm/src/main/resources/db/changelog/changelog-1702.yaml index b645fdc1b..dc9e08b11 100644 --- a/runtime-acm/src/main/resources/db/changelog/changelog-1702.yaml +++ b/runtime-acm/src/main/resources/db/changelog/changelog-1702.yaml @@ -29,10 +29,15 @@ databaseChangeLog: tableName: AutomationComposition columnName: revisionId changes: - - sql: - sql: | - ALTER TABLE AutomationComposition - ADD COLUMN revisionId VARCHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'; + - addColumn: + tableName: AutomationComposition + columns: + - column: + name: revisionId + type: VARCHAR(36) + defaultValue: '00000000-0000-0000-0000-000000000000' + constraints: + nullable: false - changeSet: author: policy @@ -44,31 +49,41 @@ databaseChangeLog: tableName: AutomationCompositionDefinition columnName: revisionId changes: - - sql: - sql: | - ALTER TABLE AutomationCompositionDefinition - ADD COLUMN revisionId VARCHAR(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'; + - addColumn: + tableName: AutomationCompositionDefinition + columns: + - column: + name: revisionId + type: VARCHAR(36) + defaultValue: '00000000-0000-0000-0000-000000000000' + constraints: + nullable: false - changeSet: author: policy id: 1702-3 changes: - - sql: - sql: ALTER TABLE AutomationComposition ALTER COLUMN phase TYPE INTEGER; + - modifyDataType: + tableName: AutomationComposition + columnName: phase + newDataType: INT - changeSet: author: policy id: 1702-4 changes: - - sql: - sql: ALTER TABLE AutomationCompositionElement ALTER COLUMN stage TYPE INTEGER; + - modifyDataType: + tableName: AutomationCompositionElement + columnName: stage + newDataType: INT - changeSet: author: policy id: 1702-5 changes: - - sql: - sql: ALTER TABLE ParticipantReplica ALTER COLUMN participantId DROP DEFAULT; + - dropDefaultValue: + tableName: ParticipantReplica + columnName: participantId - changeSet: author: policy @@ -79,8 +94,9 @@ databaseChangeLog: tableName: AutomationComposition columnName: restarting changes: - - sql: - sql: ALTER TABLE AutomationComposition DROP COLUMN restarting; + - dropColumn: + tableName: AutomationComposition + columnName: restarting - changeSet: author: policy @@ -91,8 +107,9 @@ databaseChangeLog: tableName: AutomationCompositionDefinition columnName: restarting changes: - - sql: - sql: ALTER TABLE AutomationCompositionDefinition DROP COLUMN restarting; + - dropColumn: + tableName: AutomationCompositionDefinition + columnName: restarting - changeSet: author: policy @@ -103,8 +120,9 @@ databaseChangeLog: tableName: Participant columnName: participantState changes: - - sql: - sql: ALTER TABLE Participant DROP COLUMN participantState; + - dropColumn: + tableName: Participant + columnName: participantState - changeSet: author: policy @@ -115,5 +133,6 @@ databaseChangeLog: tableName: Participant columnName: lastMsg changes: - - sql: - sql: ALTER TABLE Participant DROP COLUMN lastMsg; + - dropColumn: + tableName: Participant + columnName: lastMsg -- 2.16.6