You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 10 Next »

Work in progress


Prerequisites

  1. Agree Scheme changes (ie. create proposal page for the required schema change e.g. with proposed yang file) 
    Example: CPS-677: Support 'public' Cm Handle Properties

Build Docker for Postgres

To start an Postgres database instance using docker issue the following command

docker run --name postgres -p 5432:5432 -d -e POSTGRES_DB=cpsdb -e POSTGRES_USER=cps -e POSTGRES_PASSWORD=cps postgres:13.2-alpine


Adding New Tables

To add new tables to tables within CPS using the Liquibase change-log go to the 01-createCPSTables.yaml within the cps-ri->src->main->resources->changelog→db.changes directory.

Under the databaseChangeLog object each change-set is defined with a unique id (Similar to how an array is defined within YAML).

Here the columns are also defined, along with the constraints of a specific column.

Anchor Table 
databaseChangeLog:
  - changeSet:
      id: 1-1
      author: cps
      changes:
        - createTable:
            columns:
              - column:
                  autoIncrement: true
                  constraints:
                    nullable: false
                    primaryKey: true
                    primaryKeyName: anchor_pkey
                  name: id
                  type: BIGINT
              - column:
                  name: name
                  type: TEXT
              - column:
                  name: schema_set_id
                  type: INTEGER
              - column:
                  constraints:
                    nullable: false
                  name: dataspace_id
                  type: INTEGER
            tableName: anchor


To add a new table, under the latest change-set add a new change-set, incrementing the ID by one.

- changeSet:
    id: 1-38
    author: cps


Define a changes object, all new edits will go under this section. Create a new createTable object, and under here the table name can be defined along with the column information/constraints as stated above.

Test Table Example 
  - changeSet:
      id: 1-38
      author: cps
      changes:
        - createTable:
            columns:
              - column:
                  autoIncrement: true
                  constraints:
                    nullable: false
                    primaryKey: true
                    primaryKeyName: test_table_pkey
                  name: id
                  type: INTEGER
              - column:
                  constraints:
                    nullable: false
                  name: column_1
                  type: TEXT
            tableName: test_table


Table once created within SqlDeveloper

Adding/Editing Columns

Once the table is created, columns can be added and edited using a similar syntax to above.

To add foreign key constraints, within the 01-createCPSTables.yaml file create a new change-set and increment the change-set id as done above.

In the following example, I have created a second test table, which will contain a foreign key shared with the primary key of the first test_table.


Test Table With Foreign Key Example 
  - changeSet:
      id: 1-39
      author: cps
      changes:
        - createTable:
            columns:
              - column:
                  autoIncrement: true
                  constraints:
                    nullable: false
                    primaryKey: true
                    primaryKeyName: test_table__with_fk_pkey
                  name: test_table_with_fk_id
                  type: INTEGER
              - column:
                  constraints:
                    nullable: false
                  name: column_1
                  type: TEXT
              - column:
                  constraints:
                    nullable: false
                  name: test_table_pk
                  type: INTEGER
            tableName: test_table_with_fk

From the table above we have created a column of type integer called test_table_pk. This is a reference to the primary key within the first test table created above, which will be our foreign key in this table.

Below is the syntax within YAML for creating a foreign key relation between both tables, the base table and column being the table we are creating the constraint on, and the referenced table and column being the table which contains the PK (id) column we are creating our FK relationship with.

Creating Foreign Key Relation Example 
  - changeSet:
      id: 1-40
      author: cps
      changes:
        - addForeignKeyConstraint:
            baseColumnNames: test_table_pk
            baseTableName: test_table_with_fk
            constraintName: test_table_with_fk_fkey
            deferrable: false
            initiallyDeferred: false
            onDelete: NO ACTION
            onUpdate: NO ACTION
            referencedColumnNames: id
            referencedTableName: test_table
            validate: true


test table with fk model.

Insert Data


Adding New Yang Resource


Rollback


Applying Pending Changes

Potential Issues

  • No labels