...

Proposed Liquibase changes

...

...

It is proposed to:

1. drop the index for Fragment.dataspace_id
2. change the uniqueness constraint for Fragment, to exclude dataspace_id
3. drop the foreign key constraint for Fragment.dataspace_id
4. drop the Fragment.dataspace_id column

It is proposed that each of the above be implemented as a separate change in the Liquibase changelog (with a corresponding rollback for each). Notably, for dropping the column (step 4), the rollback will need to repopulate the column's data. SQL will need to be written for this.

databaseChangeLog:

  - changeSet:
       columnsid: 19-1
      author: cps
     - columnchanges:
        - dropIndex:
        name: dataspace_id             indexName: FKIindexName: FKI_FRAGMENT_DATASPACE_ID_FK
            tableName: fragment
     - changeSetrollback:
       id: 19-2  createIndex:
    author: cps       changescolumns:

       - dropUniqueConstraint:      -  column:
    constraintName: fragment_dataspace_id_anchor_id_xpath_key             tableNamename: fragmentdataspace_id
        - addUniqueConstraint:   indexName: FKI_FRAGMENT_DATASPACE_ID_FK
         columnNames: anchor_id, xpath tableName: fragment

  - changeSet:
     constraintName: fragment_anchor_id_xpath_key
id: 19-2
           tableNameauthor: fragmentcps
      rollbackchanges:
        - dropUniqueConstraint:
            constraintName: fragment_dataspace_id_anchor_id_xpath_key
            tableName: fragment
        - addUniqueConstraint:
            columnNames: dataspace_id, anchor_id, xpath
            constraintName: fragment_dataspace_id_anchor_id_xpath_key
            tableName: fragment
    - changeSet rollback:
       id: 19-3 dropUniqueConstraint:
      author: cps     constraintName:  changes:fragment_anchor_id_xpath_key
         - dropForeignKeyConstraint:  tableName: fragment
        - baseTableNameaddUniqueConstraint:
fragment             constraintNamecolumnNames: fragment_dataspace_id, anchor_fkeyid, xpath
     rollback:         - addForeignKeyConstraint:
   constraintName: fragment_dataspace_id_anchor_id_xpath_key
         baseColumnNames: dataspace_id  tableName: fragment

  - changeSet:
    baseTableName: fragment id: 19-3
      author: cps
  constraintName: fragment_dataspace_id_fkey   changes:
        - deferrabledropForeignKeyConstraint:
false             initiallyDeferredbaseTableName: falsefragment
            onDeleteconstraintName: NO ACTIONfragment_dataspace_id_fkey
      rollback:
       onUpdate: NO- ACTIONaddForeignKeyConstraint:
            referencedColumnNamesbaseColumnNames: dataspace_id
            referencedTableNamebaseTableName: dataspacefragment
            validate: trueconstraintName: fragment_dataspace_id_fkey
    - changeSet:       iddeferrable: 19-4
 false
    author: cps       changesinitiallyDeferred: false
       - dropColumn:    onDelete: NO ACTION
      columnName: dataspace_id     onUpdate: NO ACTION
     tableName: fragment       rollback:referencedColumnNames: id
           - addColumnreferencedTableName: dataspace
            tableNamevalidate: fragmenttrue

  - changeSet:
       columnsid: 19-4
      author: cps
     - columnchanges:
        - dropColumn:
            namecolumnName: dataspace_id
            tableName: fragment
      typerollback: INTEGER
        - sqlFileaddColumn:
            pathtableName: changelog/db/changes/19-repopulate-dataspace-id-for-rollback.sql fragment
            columns:
 - addNotNullConstraint:            - tableNamecolumn:
     fragment             columnNamename: dataspace_id
                  columnDataTypetype: INTEGER

As part of the rollback procedure, the removed column must be re-populated with data. The following SQL is used to do this:

UPDATE
        - sqlFile:
     fragment AS f SET    path: dataspace_id = a.dataspace_id
FROMchangelog/db/changes/19-repopulate-dataspace-id-for-rollback.sql
       anchor AS- aaddNotNullConstraint:
WHERE     f.anchor_id = a.id;

Results

Table description before update, after update, and after rollback

Fragment table before update, after update, and after rollback

...

      tableName: fragment
            columnName: dataspace_id
            columnDataType: INTEGER

As part of the rollback procedure, the removed column must be re-populated with data. The following SQL is used to do this:

UPDATE
    fragment AS f
SET
    dataspace_id = a.dataspace_id
FROM
    anchor AS a
WHERE
    f.anchor_id = a.id;

Test Results

The following commands were used to test updating and rolling back liquibase changes, from within the cps-ri directory:

• To update:
  mvn compile org.liquibase:liquibase-maven-plugin:4.16.0:update -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb -Dliquibase.username=cps -Dliquibase.password=cps -Dliquibase.changeLogFile=changelog/changelog-master.yaml
• To rollback:
  mvn compile org.liquibase:liquibase-maven-plugin:4.16.0:rollback -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb -Dliquibase.username=cps -Dliquibase.password=cps -Dliquibase.changeLogFile=changelog/changelog-master.yaml -Dliquibase.rollbackCount=4

The reason for liquibase.rollbackCount=4 in the above, is that 4 changesets were applied, as described in previous section.

Fragment table before update, after update, and after rollback

As can be seen, the same data is restored after update and rollback (though the ordering of the columns has changed - testing has shown this to have no impact on functionality).

Table description before update, after update, and after rollback

The following data was obtained by connecting the Postgres database, and issuing a "\d public.fragment" command, to describe the table.

Before update

                              Table "public.fragment"
     Column     |  Type   | Collation | Nullable |             Default              
 id             | bigint  |           | not null | generated by default as identity
 xpath          | text    |           | not null | 
 attributes     | jsonb   |           |          | 
 anchor_id      | bigint  |           |          | 
 parent_id      | bigint  |           |          | 
 dataspace_id   | integer |           | not null | 
 schema_node_id | integer |           |          | 
Indexes:
    "fragment_pkey" PRIMARY KEY, btree (id)
    "fki_fragment_anchor_id_fk" btree (anchor_id)
    "fki_fragment_dataspace_id_fk" btree (dataspace_id)
    "fki_fragment_parent_id_fk" btree (parent_id)
    "fki_schema_node_id_to_id" btree (schema_node_id)
    "fragment_dataspace_id_anchor_id_xpath_key" UNIQUE CONSTRAINT, btree (dataspace_id, anchor_id, xpath)
Foreign-key constraints:
    "fragment_anchor_id_fkey" FOREIGN KEY (anchor_id) REFERENCES anchor(id)
    "fragment_dataspace_id_fkey" FOREIGN KEY (dataspace_id) REFERENCES dataspace(id)
    "fragment_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES fragment(id) ON DELETE CASCADE
    "fragment_schema_node_id_fkey" FOREIGN KEY (schema_node_id) REFERENCES schema_node(id)
Referenced by:
    TABLE "fragment" CONSTRAINT "fragment_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES fragment(id) ON DELETE CASCADE
    TABLE "relation" CONSTRAINT "relation_from_fragment_id_fkey" FOREIGN KEY (from_fragment_id) REFERENCES fragment(id)
    TABLE "relation" CONSTRAINT "relation_to_fragment_id_fkey" FOREIGN KEY (to_fragment_id) REFERENCES fragment(id)

After update

                              Table "public.fragment"
     Column     |  Type   | Collation | Nullable |             Default              
 id             | bigint  |           | not null | generated by default as identity
 xpath          | text    |           | not null | 
 attributes     | jsonb   |           |          | 
 anchor_id      | bigint  |           |          | 
 parent_id      | bigint  |           |          | 
 schema_node_id | integer |           |          | 
Indexes:
    "fragment_pkey" PRIMARY KEY, btree (id)
    "fki_fragment_anchor_id_fk" btree (anchor_id)
    "fki_fragment_parent_id_fk" btree (parent_id)
    "fki_schema_node_id_to_id" btree (schema_node_id)
    "fragment_anchor_id_xpath_key" UNIQUE CONSTRAINT, btree (anchor_id, xpath)
Foreign-key constraints:
    "fragment_anchor_id_fkey" FOREIGN KEY (anchor_id) REFERENCES anchor(id)
    "fragment_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES fragment(id) ON DELETE CASCADE
    "fragment_schema_node_id_fkey" FOREIGN KEY (schema_node_id) REFERENCES schema_node(id)
Referenced by:
    TABLE "fragment" CONSTRAINT "fragment_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES fragment(id) ON DELETE CASCADE
    TABLE "relation" CONSTRAINT "relation_from_fragment_id_fkey" FOREIGN KEY (from_fragment_id) REFERENCES fragment(id)
    TABLE "relation" CONSTRAINT "relation_to_fragment_id_fkey" FOREIGN KEY (to_fragment_id) REFERENCES fragment(id)

Observe that:

• dataspace_id column is no longer in the table
• fki_fragment_dataspace_id_fk index was removed
• fragment_dataspace_id_fkey foreign key constraint was removed
• fragment_dataspace_id_anchor_id_xpath_key UNIQUE CONSTRAINT was replaced with fragment_anchor_id_xpath_key.

After rollback

                              Table "public.fragment"
     Column     |  Type   | Collation | Nullable |             Default              
 id             | bigint  |           | not null | generated by default as identity
 xpath          | text    |           | not null | 
 attributes     | jsonb   |           |          | 
 anchor_id      | bigint  |           |          | 
 parent_id      | bigint  |           |          | 
 schema_node_id | integer |           |          | 
 dataspace_id   | integer |           | not null | 
Indexes:
    "fragment_pkey" PRIMARY KEY, btree (id)
    "fki_fragment_anchor_id_fk" btree (anchor_id)
    "fki_fragment_dataspace_id_fk" btree (dataspace_id)
    "fki_fragment_parent_id_fk" btree (parent_id)
    "fki_schema_node_id_to_id" btree (schema_node_id)
    "fragment_dataspace_id_anchor_id_xpath_key" UNIQUE CONSTRAINT, btree (dataspace_id, anchor_id, xpath)
Foreign-key constraints:
    "fragment_anchor_id_fkey" FOREIGN KEY (anchor_id) REFERENCES anchor(id)
    "fragment_dataspace_id_fkey" FOREIGN KEY (dataspace_id) REFERENCES dataspace(id)
    "fragment_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES fragment(id) ON DELETE CASCADE
    "fragment_schema_node_id_fkey" FOREIGN KEY (schema_node_id) REFERENCES schema_node(id)
Referenced by:
    TABLE "fragment" CONSTRAINT "fragment_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES fragment(id) ON DELETE CASCADE
    TABLE "relation" CONSTRAINT "relation_from_fragment_id_fkey" FOREIGN KEY (from_fragment_id) REFERENCES fragment(id)
    TABLE "relation" CONSTRAINT "relation_to_fragment_id_fkey" FOREIGN KEY (to_fragment_id) REFERENCES fragment(id)

Observe that:

• dataspace_id column is added to the table again
• fki_fragment_dataspace_id_fk index is added again
• fragment_dataspace_id_fkey foreign key constraint is added again
• fragment_dataspace_id_anchor_id_xpath_key UNIQUE CONSTRAINT is added again