...

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
5. make Fragment.anchor_id not null

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
      author: cps
      changes:
        - dropIndex:
            indexName: FKI_FRAGMENT_DATASPACE_ID_FK
            tableName: fragment
      rollback:
        - createIndex:
            columns:
              - column:
                  name: dataspace_id
            indexName: FKI_FRAGMENT_DATASPACE_ID_FK
            tableName: fragment

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

  - changeSet:
      id: 19-3
      author: cps
      changes:
        - dropForeignKeyConstraint:
            baseTableName: fragment
            constraintName: fragment_dataspace_id_fkey
      rollback:
        - addForeignKeyConstraint:
            baseColumnNames: dataspace_id
            baseTableName: fragment
            constraintName: fragment_dataspace_id_fkey
            deferrable: false
            initiallyDeferred: false
            onDelete: NO ACTION
            onUpdate: NO ACTION
            referencedColumnNames: id
            referencedTableName: dataspace
            validate: true

  - changeSet:
      id: 19-4
      author: cps
      changes:
        - dropColumn:
            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

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

UPDATE  - changeSet:
    fragment  AS f
SET
id: 19-5
     dataspace_id = a.dataspace_id
FROM
author: cps
    anchor AS a
WHERE changes:
        - addNotNullConstraint:
            tableName: fragment
    f.        columnName: 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:

            columnDataType: BIGINT
      rollback:
        - dropNotNullConstraint:
            tableName: fragment
            columnName: anchor_id
            columnDataType: BIGINT
 

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-
• 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 update -Dliquibase.url=jdbc:postgresql://localhost:5432/cpsdb -Dliquibase.username=cps -Dliquibase.password=cps -Dliquibase.changeLogFile=changelog/changelog-master.yaml -Dliquibase.rollbackCount=4
• 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 The reason for liquibase.rollbackCount=4 in the above, is that 4 changesets were applied, as described in previous section.

...

                              Table "public.fragment"
     Column     |  Type   | Collation | Nullable |             Default              
 id             | bigint  |           | not null | generated by default as identity
 xpath----------------+---------+-----------+----------+----------------------------------
 id              | textbigint    |           | not null | 
 attributes     | jsonb generated by default as identity
 xpath   |       | text     |           | not null | 
 anchor_id attributes     | jsonb   | bigint           |          | 
 anchor_id      | bigint  |           | not null | 
 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)

...

• dataspace_id column is no longer in the table
• anchor_id is not null
• 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.

...

                              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)

...