...

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:
      id: 19-1

databaseChangeLog:

  - changeSet:
      id: 19-1
      author: cps
      changes:
        - dropIndex:
            indexName: FKI_FRAGMENT_DATASPACE_ID_FK
            tableName: fragment
      rollback:
        - createIndex:
            columnsauthor: cps
      changes:
        - columndropIndex:
                  nameindexName: dataspace_idFKI_FRAGMENT_DATASPACE_ID_FK
            indexNametableName: FKI_FRAGMENT_DATASPACE_ID_FKfragment
       rollback:
     tableName: fragment

  - changeSetcreateIndex:
      id: 19-2
      authorcolumns: cps
      changes:
        - dropUniqueConstraintcolumn:
                  constraintNamename: fragment_dataspace_id_anchor_id_xpath_key
            indexName: FKI_FRAGMENT_DATASPACE_ID_FK
            tableName: fragment

  - changeSet:
      id: 19- addUniqueConstraint:
2
      author: cps
      changes:
       columnNames: anchor_id, xpath - dropUniqueConstraint:
            constraintName: fragment_dataspace_id_anchor_id_xpath_key
            tableName: fragment
      rollback:
  - addUniqueConstraint:
           - dropUniqueConstraint:columnNames: anchor_id, xpath
            constraintName: fragment_anchor_id_xpath_key
            tableName: fragment
        - addUniqueConstraintrollback:
        -    columnNames: dataspace_id, anchor_id, xpathdropUniqueConstraint:
            constraintName: fragment_dataspace_id_anchor_id_xpath_key
            tableName: fragment

  - changeSet:
     - idaddUniqueConstraint: 19-3
      author: cps
      changescolumnNames:
 dataspace_id,       - dropForeignKeyConstraint:anchor_id, xpath
            baseTableNameconstraintName: fragment_dataspace_id_anchor_id_xpath_key
            constraintNametableName: fragment_dataspace_id_fkey

  -    rollbackchangeSet:
      id:  19-3
 addForeignKeyConstraint:
     author: cps
      baseColumnNames: dataspace_idchanges:
        - dropForeignKeyConstraint:
            baseTableName: fragment
            constraintName: fragment_dataspace_id_fkey
      rollback:
        - deferrableaddForeignKeyConstraint: false
            initiallyDeferredbaseColumnNames: falsedataspace_id
            onDeletebaseTableName: NO ACTIONfragment
            onUpdateconstraintName: NO ACTIONfragment_dataspace_id_fkey
            referencedColumnNamesdeferrable: idfalse
            referencedTableNameinitiallyDeferred: dataspacefalse
            validateonDelete: true

NO ACTION
     - changeSet:
      idonUpdate: NO 19-4ACTION
      author: cps
     referencedColumnNames: changes:id
           - dropColumnreferencedTableName: dataspace
            columnName: dataspace_id
validate: true

  - changeSet:
      id: 19-4
      tableNameauthor: fragmentcps
      rollbackchanges:
        - addColumndropColumn:
            columnName: dataspace_id
            tableName: fragment
      rollback:
        - addColumn:
            tableName: fragment
            columns:
              - column:
                  name: dataspace_id
                  type: INTEGER
        - sqlFile:
            path: changelog/db/changes/19-repopulate-dataspace-id-for-rollback.sql
        - addNotNullConstraint:
            tableName: fragment
            columnName: dataspace_id
            columnDataType: INTEGER

...

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

Test Results

Table description before update, after update, and after rollback

Fragment table before update, after update, and after rollback

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