Fragment table includes dataspace_id and anchor_id columns. Anchor table also has dataspace_id column. Since an Anchor belongs to only one Dataspace, and a Fragment belongs to only one Anchor, the dataspace_id column in Fragment is redundant (since the fragment's dataspace_id is a duplicate of the anchor's dataspace_id). It is at best redundant to have the same data duplicated in multiple database tables, and at worst could lead to inconsistencies.

This task is to remove dataspace_id column from the Fragment table.

In addition to wasting space on duplicate data, the Fragment table also includes an index for dataspace_id, a foreign key constraint, and dataspace_id is included in the uniqueness constraint for Fragment. Removing all these may improve write performance (albeit a small improvement). There are no negative performance impacts expected, as the only functionality relying on dataspace_id is query-across-anchors (relevant SQL queries can be changed to use an inexpensive table JOIN on Anchor table).

This task is to remove dataspace_id column from the Fragment table.

Implementation Proposal

It is proposed to remove the dataspace_id column from the Fragment table in the Liquibase changelog. This also involves removing associated indexes and constraints.

Proposed Liquibase changes

Code Block

language	yml

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:

Code Block

language	sql

UPDATE
    fragment AS f
SET
    dataspace_id = a.dataspace_id
FROM
    anchor AS a
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

...

It is proposed to:

drop the index for Fragment.dataspace_id
change the uniqueness constraint for Fragment, to exclude dataspace_id
drop the foreign key constraint for Fragment.dataspace_id
drop the Fragment.dataspace_id column
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.

Code Block

language	yml

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

  - changeSet:
      id: 19-5
      author: cps
      changes:
        - addNotNullConstraint:
            tableName: fragment
            columnName: anchor_id
            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:

Code Block

language	sql

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

Before update
id	xpath	attributes	anchor_id	parent_id	dataspace_id	schema_node_id
1	/cps-ran-schema	{}	1	null	1	null
2	/cps-ran-schema/NearRTRIC[@idNearRTRIC="11"]	{"idNearRTRIC": "11"}	1	1	1	null
137	/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15549"]/attributes	{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15549}	1	136	1	null
139	/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15360"]/attributes	{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15360}	1	138	1	null
141	/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15361"]/attributes	{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15361}	1	140	1	null
144	/dmi-registry	{}	2	null	2	null
145	/dmi-registry/cm-handles[@id='336a9e2779b64a1cac04fee3208a1c76']	{"id": "336a9e2779b64a1cac04fee3208a1c76", "dmi-service-name": "http://ncmp-dmi-plugin-stub:8080", "dmi-data-service-name": "", "dmi-model-service-name": ""}	2	144	2	null
After update
id	xpath	attributes	anchor_id	parent_id	schema_node_id
1	/cps-ran-schema	{}	1	null	null
2	/cps-ran-schema/NearRTRIC[@idNearRTRIC="11"]	{"idNearRTRIC": "11"}	1	1	null
137	/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15549"]/attributes	{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15549}	1	136	null
139	/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15360"]/attributes	{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15360}	1	138	null
141	/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15361"]/attributes	{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15361}	1	140	null
144	/dmi-registry	{}	2	null	null
145	/dmi-registry/cm-handles[@id='48d42e05942b457dafdfa24195fdb092']	{"id": "48d42e05942b457dafdfa24195fdb092", "dmi-service-name": "http://ncmp-dmi-plugin-stub:8080", "dmi-data-service-name": "", "dmi-model-service-name": ""}	2	144	null
After rollback
id	xpath	attributes	anchor_id	parent_id	schema_node_id	dataspace_id
1	/cps-ran-schema	{}	1	null	null	1
2	/cps-ran-schema/NearRTRIC[@idNearRTRIC="11"]	{"idNearRTRIC": "11"}	1	1	null	1
137	/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15549"]/attributes	{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15549}	1	136	null	1
139	/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15360"]/attributes	{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15360}	1	138	null	1
141	/cps-ran-schema/NearRTRIC[@idNearRTRIC="22"]/GNBDUFunction[@idGNBDUFunction="6"]/NRCellDU[@idNRCellDU="15361"]/attributes	{"nRPCI": 12, "nRTAC": 310, "cellLocalId": 15361}	1	140	null	1
144	/dmi-registry	{}	2	null	null	2
145	/dmi-registry/cm-handles[@id='48d42e05942b457dafdfa24195fdb092']	{"id": "48d42e05942b457dafdfa24195fdb092", "dmi-service-name": "http://ncmp-dmi-plugin-stub:8080", "dmi-data-service-name": "", "dmi-model-service-name": ""}	2	144	null	2

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  |           | 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)

Observe that:

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.

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

Versions Compared

Old Version 2

New Version Current

Key

Implementation Proposal

Proposed Liquibase changes

Results

Table description before update, after update, and after rollback

Fragment table before update, after update, and after rollback

Test Results

Fragment table before update, after update, and after rollback

Table description before update, after update, and after rollback

Before update

After update

After rollback

Page Comparison

Versions Compared

Old Version 2

New Version Current

Key

Implementation Proposal

Proposed Liquibase changes

Results

Table description before update, after update, and after rollback

Fragment table before update, after update, and after rollback

Test Results

Fragment table before update, after update, and after rollback

Table description before update, after update, and after rollback

Before update

After update

After rollback