Adding Columns

Introduction of Liquibase

All liquibase-changelog.sql is under the following directory: /javasdk/src/main/resources/liquibase/changelogs/

When the existing liquibase-changelog.sql is getting larger and affect the speed to load the changelog, we can just add another file in the same directory but make sure to follow the format as follows:

liquibase-changelogV2.sql
liquibase-changelogV3.sql
liquibase-changelogVx.sql

The changelogs will be executed in alphabetical order. Therefore, we need to name the files appropriately.

Steps to follow when adding new column:

1. Make changes on the liquibase file accordingly

Firstly, before creating any new changeset in the liquibase file, we need to make sure we add (--changeset developerName:YYYYMMDDHHMM) and follow the correct liquibase changeset format as follows:

--changeset {name}:YYYYMMDDHHMM
ALTER TABLE bl_fi_category_to_addon_item_link
    ADD COLUMN IF NOT EXISTS input_tax_type varchar(50),
    ADD COLUMN IF NOT EXISTS output_wht_tax_rate numeric(70,22),
    ADD COLUMN IF NOT EXISTS input_tax_code varchar(50) NOT NULL;
COMMENT ON COLUMN bl_fi_category_to_addon_item_link.input_tax_type IS 'purchase tax type';
COMMENT ON COLUMN bl_fi_category_to_addon_item_link.output_wht_tax_rate IS 'sales withhold tax rate';
COMMENT ON COLUMN bl_fi_category_to_addon_item_link.input_tax_code IS 'purchase tax code';

Remember to write brief comments within the code to give context to future readers. Avoid adding extra spaces in between the (--changeset developerName:YYYYMMDDHHMM) as it might not considered the changes to be a new changeset.

2. Make changes on the Java data class

Add new java property, setter (mutators) and getter (accessors)

public class bl_fi_category_to_addon_item_link extends DatabaseTable  {
    private String input_tax_type;
    private BigDecimal output_wht_tax_rate;

     public String getInput_tax_type() {
        return input_tax_type;
    }

    public void setInput_tax_type(String input_tax_type) {
        this.input_tax_type = input_tax_type;
    }

    public BigDecimal getOutput_wht_tax_rate() {
        return output_wht_tax_rate;
    }

    public void setOutput_wht_tax_rate(BigDecimal output_wht_tax_rate) {
        this.output_wht_tax_rate = output_wht_tax_rate;
    }
}

3. Make changes on the QueryCriteriaClass (Optional)

If the added columns should be used in filters, we need to make changes to the following classes:

Liquibase

CREATE INDEX IF NOT EXISTS index_name_1 ON table_name (column1);
CREATE INDEX IF NOT EXISTS index_name_2 ON table_name (column2);

Query Criteria

public class CategoryToAddonItemLinkQueryCriteria extends BaseQueryCriteria {
    private String input_tax_type;
    private BigDecimal output_wht_tax_rate;

     public String getInput_tax_type() {
        return input_tax_type;
    }

    public void setInput_tax_type(String input_tax_type) {
        this.input_tax_type = input_tax_type;
    }

    public BigDecimal getOutput_wht_tax_rate() {
        return output_wht_tax_rate;
    }

    public void setOutput_wht_tax_rate(BigDecimal output_wht_tax_rate) {
        this.output_wht_tax_rate = output_wht_tax_rate;
    }
}

UOW class

Make changes to the UOW class SQL query part:

public String generateWhereClauseByCriteria(CategoryToAddonItemLinkQueryCriteria criteria) {
        final String hdr = bl_fi_category_to_addon_item_link.tableAlias;
        final String sql =
                String.format("select %s.guid from bl_fi_category_to_addon_item_link as %s ", hdr, hdr)
                        + "where 1=1 "
                        // conditions
                        + (Objects.nonNull(criteria.getInput_tax_type()) ? String.format("AND (%s.input_tax_type = :input_tax_type) ", hdr) : "")
                        + (Objects.nonNull(criteria.getOutput_wht_tax_rate()) ? String.format("AND (%s.output_wht_tax_rate = :output_wht_tax_rate) ", hdr) : "")
                        + (Objects.nonNull(criteria.getStatus()) ? String.format("AND (%s.status = '" + criteria.getStatus(), hdr) + "')" : String.format("AND (%s.status !='DELETED') ", hdr))
                        + String.format(" group by %s.guid ", hdr);
        return " hdr.guid IN (" + sql + ")";
    }

4. Make changes on the DataConsistencyClass (DCO) depending on the column constraints

if Column.isForeignKey -> {
   FKOptional -> check if it exists in the referred table & do not check for null in the DCO class
   FKMandatory -> check if it exists in the referred table & check for not null constraints in the DCO class
} else {
   no change is needed for DCO class
}

5. Run the related unit test classes just to make sure the new changes are not breaking the existing functionality

check which unit test classes is using this table
or run the whole unit test classes

6. Make changes on the Stoplight documentation if exist

7. Update DbSchema

Please refer to the Documentation of Updating DbSchema Details

8. Update ts/lib

BitBucket repository link: https://bitbucket.org/bigledger/blg-akaun-ts-lib/src/master/

In the dbschema folder, find the corresponding schema and add the new columns.

Make sure the import path is from the current directory instead of global.

import * as fromDataType from 'projects/blg-akaun-ts-lib/src/lib/models/datatype-library/index' // incorrect
import * as fromDataType from '../datatype-library/index' // correct

export interface app_perm_template_request_hdr_RowInterface {
    guid: fromDataType.GuidDataFieldInterface;
    namespace: fromDataType.VarcharDatatypeInterface;
}

export class app_perm_template_request_hdr_RowClass implements app_perm_template_request_hdr_RowInterface {
    guid: fromDataType.GuidDataFieldInterface;
    namespace: fromDataType.VarcharDatatypeInterface;
}

9. Update version in projects > blg-akaun-ts-lib > package.json

{
    "name": "blg-akaun-ts-lib",
    "version": "0.2.9100225", // update this version 0.2.9100225 -> 0.2.9100226
    "author": {
        "name": "Hassan",
        "email": "hassan@wavelet.net"
    },
    "description": "Bigledger Akaun ts lib.",
    "license": "MIT",
    "peerDependencies": {
        "@angular/common": "^8.2.14",
        "@angular/core": "^8.2.14"
    }
}

10. Push to master branch and ask for deployment (front-end team)

SQL Example

Add FK column to existing table

Optional FK:

ALTER TABLE tableName ADD CONSTRAINT fk_tableName_FkColumnName FOREIGN KEY ( FkColumnName) REFERENCES refeered_Table( guid ) NOT VALID;

--  NOT VALID is to tell postgres to apply this constraint only on new record and skip checking for existing data.

Mandotory FK:

ALTER TABLE tableName ADD CONSTRAINT fk_tableName_FkColumnName FOREIGN KEY ( FkColumnName) REFERENCES refeered_Table( guid ) NOT VALID;

ALTER TABLE tableName ADD CONSTRAINT guid_fkColumnName_not_null_check CHECK (FkColumnNameIS NOT NULL) NOT VALID;

-- the check function is to validate any create or update record that fk shoul not be null

Change From Mandatory FK to Optional FK

ALTER TABLE bl_fi_category_to_addon_item_link
    ALTER COLUMN  addon_fi_item_line_guid DROP NOT NULL;

Documentation of Updating DbSchema Details

Prerequisites:

Before updating and syncing the DbSchema with database, make sure you have installed DbSchema and Git Bash in your computer.

Updating DbSchema Videos:

How to update DbSchema

Clone (bigledger-dbschema repository)

You can open Git Bash straight from the bigledger-dbschema folder.

Git Bash BigLedger DbSchema

Create a new issue under this epic
In terminal (Git Bash), git pull the latest master branch. Then create a new branch with the name of newly created issue in step 2. Therefore, switch to the new branch and start updating DbSchema.
In bigledger-dbschema, open BigLedger.dbs using DbSchema. Make sure you are in the right tenant (database) connection and right folder.

Open BigLedger.dbs

Notice: tnthassan_code is no longer in use. Ask team leader to ensure which database to connect to.

Click Refresh Local Schemas From Database.

Refresh Local Schems

Tick public.

Tick Public

If your DbSchema already synced with the database, information dialog with words of "Schemas are identical" will appear.

Schemas are identical

Follow this documentation: DbSchema Guide to make all changes
Next, you can arrange the tables if you found out the tables are quite messy. Notice: You should ask Wei Xian if you do not know how to arrange & group new tables

For example:

DbSchema Table 1

DbSchema Table 2

Separate and arrange them to become like this:

DbSchema Table 3

You can edit or group the tables by right clicking the table.

DbSchema Table 4

After making all changes, you will neeed to export the DbSchema into HTML file and rename it as Default+Layout.html to replace the old file.
Layout > Export HTML5/PDF Documentation

Export HTML5/PDF Documentation

Export and override Default+Layout.html
Git add and git commit all changes in the local branch:
- git add .
- git commit -m "YOUR MESSAGE"
- git push

There should be 2 modified files in the local branch:

Modified Files

Notice: "YOUR MESSAGE" format is: “Issue_name Update DbSchema“. Do not enter general message such as “Update” or “Update dbschema”. 11. Assign to Wei Xian for code review.

The following steps are outdated and should not be followed by junior developers.