Resolve NXCALS-7195 "Feature improve constraints on changelog"

Merge request template

COMPATIBILITY NOTE

Please make sure that this change is BACKWARDS COMPATIBLE and a new server will work correctly with clients using old APIs versions.

It may be that data sent in the new format will break the old clients.

In case of any potential incompatibility we have to inform our clients and schedule release & deployment with them.

---

@acc-logging-team - please have a look for approval.

Please describe the merge request here.

Closes NXCALS-7195

service-db/src/main/resources/sql/versions/NXCALS-7195/NXCALS-7195-add-nn-constraints-to-changelogs.xml

@@ -5,98 +5,200 @@
          http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
 
     <changeSet author="psowinsk" id="NXCALS-7195-add-nn-constraints-to-changelogs">
-        <sql>
-            ALTER TABLE entities_changelog modify (entity_id not null);
-            ALTER TABLE entities_changelog modify (create_time_utc not null);
-            ALTER TABLE entities_changelog modify (op_type not null);
-
-            ALTER TABLE entities_hist_changelog modify (entity_hist_id not null);
-            ALTER TABLE entities_hist_changelog modify (create_time_utc not null);
-            ALTER TABLE entities_hist_changelog modify (op_type not null);
-
-            ALTER TABLE group_entities_changelog modify (create_time_utc not null);
-
-            ALTER TABLE group_properties_changelog modify (create_time_utc not null);
-
-            ALTER TABLE group_variables_changelog modify (create_time_utc not null);
-
-            ALTER TABLE groups_changelog modify (create_time_utc not null);
-            ALTER TABLE groups_changelog modify (op_type not null);
-
-            ALTER TABLE hierarchies_changelog modify (hierarchy_id not null);
-            ALTER TABLE hierarchies_changelog modify (create_time_utc not null);
-            ALTER TABLE hierarchies_changelog modify (op_type not null);
-
-            ALTER TABLE partition_resource_hists_changelog modify (partition_resource_hist_id not null);
-            ALTER TABLE partition_resource_hists_changelog modify (create_time_utc not null);
-            ALTER TABLE partition_resource_hists_changelog modify (op_type not null);
-
-            ALTER TABLE partition_resources_changelog modify (partition_resource_id not null);
-            ALTER TABLE partition_resources_changelog modify (create_time_utc not null);
-            ALTER TABLE partition_resources_changelog modify (op_type not null);
-
-            ALTER TABLE partitions_changelog modify (partition_id not null);
-            ALTER TABLE partitions_changelog modify (create_time_utc not null);
-            ALTER TABLE partitions_changelog modify (op_type not null);
-
-            ALTER TABLE schemas_changelog modify (schema_id not null);
-            ALTER TABLE schemas_changelog modify (create_time_utc not null);
-            ALTER TABLE schemas_changelog modify (op_type not null);
-
-            ALTER TABLE variable_configs_changelog modify (variable_config_id not null);
-            ALTER TABLE variable_configs_changelog modify (create_time_utc not null);
-            ALTER TABLE variable_configs_changelog modify (op_type not null);
-
-            ALTER TABLE variables_changelog modify (variable_id not null);
-            ALTER TABLE variables_changelog modify (create_time_utc not null);
-            ALTER TABLE variables_changelog modify (op_type not null);
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                    SELECT table_name, column_name
+                    FROM user_tab_columns
+                    where table_name = 'ENTITIES_CHANGELOG'
+                      and column_name in ('ENTITY_ID','CREATE_TIME_UTC', 'OP_TYPE')
+                      and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
         </sql>
-        <rollback>
-            ALTER TABLE entities_changelog modify (entity_id null);
-            ALTER TABLE entities_changelog modify (create_time_utc null);
-            ALTER TABLE entities_changelog modify (op_type null);
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                    SELECT table_name, column_name
+                    FROM user_tab_columns
+                    where table_name = 'ENTITIES_HIST_CHANGELOG'
+                      and column_name in ('ENTITY_HIST_ID','CREATE_TIME_UTC', 'OP_TYPE')
+                      and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
-            ALTER TABLE entities_hist_changelog modify (entity_hist_id null);
-            ALTER TABLE entities_hist_changelog modify (create_time_utc null);
-            ALTER TABLE entities_hist_changelog modify (op_type null);
+        </sql>
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                    SELECT table_name, column_name
+                    FROM user_tab_columns
+                    where table_name = 'GROUP_ENTITIES_CHANGELOG'
+                      and column_name in ('CREATE_TIME_UTC', 'OP_TYPE')
+                      and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
-            ALTER TABLE group_entities_changelog modify (create_time_utc null);
+        </sql>
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                    SELECT table_name, column_name
+                    FROM user_tab_columns
+                    where table_name = 'GROUP_PROPERTIES_CHANGELOG'
+                      and column_name in ('CREATE_TIME_UTC', 'OP_TYPE')
+                      and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
-            ALTER TABLE group_properties_changelog modify (create_time_utc null);
+        </sql>
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                    SELECT table_name, column_name
+                    FROM user_tab_columns
+                    where table_name = 'GROUP_VARIABLES_CHANGELOG'
+                      and column_name in ('CREATE_TIME_UTC', 'OP_TYPE')
+                      and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
-            ALTER TABLE group_variables_changelog modify (create_time_utc null);
+        </sql>
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                SELECT table_name, column_name
+                FROM user_tab_columns
+                where table_name = 'GROUPS_CHANGELOG'
+                  and column_name in ('CREATE_TIME_UTC', 'OP_TYPE')
+                  and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
-            ALTER TABLE groups_changelog modify (create_time_utc null);
-            ALTER TABLE groups_changelog modify (op_type null);
+        </sql>
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                    SELECT table_name, column_name
+                    FROM user_tab_columns
+                    where table_name = 'HIERARCHIES_CHANGELOG'
+                      and column_name in ('HIERARCHY_ID','CREATE_TIME_UTC', 'OP_TYPE')
+                      and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
-            ALTER TABLE hierarchies_changelog modify (hierarchy_id null);
-            ALTER TABLE hierarchies_changelog modify (create_time_utc null);
-            ALTER TABLE hierarchies_changelog modify (op_type null);
+        </sql>
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                    SELECT table_name, column_name
+                    FROM user_tab_columns
+                    where table_name = 'PARTITION_RESOURCE_HISTS_CHANGELOG'
+                      and column_name in ('PARTITION_RESOURCE_HIST_ID','CREATE_TIME_UTC', 'OP_TYPE')
+                      and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
-            ALTER TABLE partition_resource_hists_changelog modify (partition_resource_hist_id null);
-            ALTER TABLE partition_resource_hists_changelog modify (create_time_utc null);
-            ALTER TABLE partition_resource_hists_changelog modify (op_type null);
+        </sql>
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                    SELECT table_name, column_name
+                    FROM user_tab_columns
+                    where table_name = 'PARTITION_RESOURCES_CHANGELOG'
+                      and column_name in ('PARTITION_RESOURCE_ID','CREATE_TIME_UTC', 'OP_TYPE')
+                      and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
-            ALTER TABLE partition_resources_changelog modify (partition_resource_id null);
-            ALTER TABLE partition_resources_changelog modify (create_time_utc null);
-            ALTER TABLE partition_resources_changelog modify (op_type null);
+        </sql>
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                    SELECT table_name, column_name
+                    FROM user_tab_columns
+                    where table_name = 'PARTITIONS_CHANGELOG'
+                      and column_name in ('PARTITION_ID','CREATE_TIME_UTC', 'OP_TYPE')
+                      and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
-            ALTER TABLE partitions_changelog modify (partition_id null);
-            ALTER TABLE partitions_changelog modify (create_time_utc null);
-            ALTER TABLE partitions_changelog modify (op_type null);
+        </sql>
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                    SELECT table_name, column_name
+                    FROM user_tab_columns
+                    where table_name = 'SCHEMAS_CHANGELOG'
+                      and column_name in ('SCHEMA_ID','CREATE_TIME_UTC', 'OP_TYPE')
+                      and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
-            ALTER TABLE schemas_changelog modify (schema_id null);
-            ALTER TABLE schemas_changelog modify (create_time_utc null);
-            ALTER TABLE schemas_changelog modify (op_type null);
+        </sql>
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                    SELECT table_name, column_name
+                    FROM user_tab_columns
+                    where table_name = 'VARIABLE_CONFIGS_CHANGELOG'
+                      and column_name in ('VARIABLE_CONFIG_ID','CREATE_TIME_UTC', 'OP_TYPE')
+                      and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
-            ALTER TABLE variable_configs_changelog modify (variable_config_id null);
-            ALTER TABLE variable_configs_changelog modify (create_time_utc null);
-            ALTER TABLE variable_configs_changelog modify (op_type null);
+        </sql>
+        <sql endDelimiter="#">
+            DECLARE
+                CURSOR cursor_columns IS
+                    SELECT table_name, column_name
+                    FROM user_tab_columns
+                    where table_name = 'VARIABLES_CHANGELOG'
+                      and column_name in ('VARIABLE_ID','CREATE_TIME_UTC', 'OP_TYPE')
+                      and nullable ='Y';
+            BEGIN
+                FOR rec IN cursor_columns LOOP
+                    EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MODIFY (' || rec.column_name || ' NOT NULL)';
+                END LOOP;
+            END;
 
-            ALTER TABLE variables_changelog modify (variable_id null);
-            ALTER TABLE variables_changelog modify (create_time_utc null);
-            ALTER TABLE variables_changelog modify (op_type null);
-        </rollback>
+        </sql>
     </changeSet>
 </databaseChangeLog>