Examples (liquibase)

Introduction

For inserting a single quote as in "I love liquibase's features"

Escape it doubling the single quote as in 'I love liquibase''s features'

Reference

Change set 

    http://www.liquibase.org/documentation/changeset.html

Column

    https://www.liquibase.org/documentation/column.html

Foreign key constraint

    https://www.liquibase.org/documentation/changes/add_foreign_key_constraint.html

SQL types

    https://stackoverflow.com/questions/16890723/list-all-liquibase-sql-types

addAutoIncrement

https://docs.liquibase.com/change-types/add-auto-increment.html

Might be needed, not sure, for primary key which autoincrements at the entity w/ @GeneratedValue(strategy = GenerationType.IDENTITY)

      changes:                

        - addAutoIncrement:

            columnDataType: bigint 

            columnName: code

            incrementBy: 1

            startWith: 1

            tableName: calendari_tf_estructura

addColumn (example)

Used for alter table. With a foreign key:

      changes:

        - addColumn:

            tableName: application

            columns:

             - column:

                name: status_code

                type: int

                constraints:

                  nullable: false

                  foreignKeyName: fk_application_application_status_code

                  references: application_status(code)

                remarks: "Application code"

addDefaultValue (example)

Adds a default value to the database definition for the specified column.

#

databaseChangeLog:

  - changeSet:

      id: "118"

      author: thatsme

      failOnError: true

      comment: "Add default value to field active of table application_status"

      changes:

        - addDefaultValue:

            tableName: application_status

            columnName: active

            defaultValueBoolean: true 

addForeignKeyConstraint (example)

With one field, see column.

With multiple fields:

        - addForeignKeyConstraint:

             baseColumnNames: "institution_code,learning_unit_code,calendar_code"

             baseTableName: learning_unit_organicunit_program

             constraintName: fk_learning_unit_organicunit_program_to_learning_unit

             deferrable: false

             initiallyDeferred: false

             onDelete: RESTRICT

             onUpdate: RESTRICT

             referencedColumnNames: "institution_code,learning_unit_code,calendar_code"

             referencedTableName: learning_unit

addNotNullConstraint (example)

# Changes student_code to not null

databaseChangeLog:

  - changeSet:

      id: "087"

      author: thatsme

      failOnError: true

      comment: "Make student_code not null"

      changes:

        - addNotNullConstraint:

            tableName: my_table

            columnName: student_code

            columnDataType: bigint

            validate: true

create index (example)

#

databaseChangeLog:

  - changeSet:

      id: "056"

      author: wsparcie

      failOnError: true

      comment: "Add 2 indexes"

      changes:  

        - createIndex:

            indexName: cal_sub_presents_idx

            tableName: asig_mat

            columns:

              - column:

                  name: any_academico

                  type: varchar(5)

              - column:

                  name: cod_asignatura

                  type: varchar(8)

              - column:

                  name: present

                  type: boolean

        - createIndex:

            indexName: cal_sub_ar_idx 

            tableName: asig_mat

            columns:

              - column:

                  name: any_academico

                  type: varchar(5)

              - column:

                  name: cod_asignatura

                  type: varchar(8)

              - column:

                  name: academic_record

                  type: bigint(10)

create table (example)

create table (technical fields, PK w/ auto increment, foreign keys, index w/ two fields)

Create new table:

• Technical fields: version, created_instant, created_by, modified_instant & modified_by

• Primary key w/ auto increment [Might be needed, not sure, for primary key which autoincrements at the entity w/ @GeneratedValue(strategy = GenerationType.IDENTITY)]

• Foreign keys

• Index w/ two fields

#

databaseChangeLog:

  - changeSet:

      id: "113"

      author: thatsme

      failOnError: true

      comment: "Deliverables attached to a learning object application"

      changes:

        - createTable:

            tableName: application_learning_object_delivery

            columns:

              - column:

                  name: version

                  type: bigint

                  defaultValue: 0

                  constraints:

                    nullable: false

                  remarks: "JPA version"

              - column:

                  name: created_instant

                  type: datetime

                  defaultValueComputed: current_timestamp

                  constraints:

                    nullable: false

                  remarks: "Created instant"

              - column:

                  name: created_by

                  type: bigint

                  constraints:

                    nullable: true

                  remarks: "Created by"

              - column:

                  name: modified_instant

                  type: datetime

                  defaultValueComputed: current_timestamp

                  constraints:

                    nullable: false

                  remarks: "Modified instant"

              - column:

                  name: modified_by

                  type: bigint

                  constraints:

                    nullable: true

                  remarks: "Modified by"

              - column:

                  name: id

                  type: bigint

                  autoIncrement: true

                  startWith: 1

                  incrementBy: 1

                  constraints:

                    nullable: false

                  remarks: "pk (generated)"

              - column:

                  name: active

                  type: boolean

                  constraints:

                    nullable: false

                  defaultValue: true

                  remarks: "It indicates if the record is active"

              - column:

                  name: application_code

                  type: bigint

                  constraints:

                    nullable: false

                  remarks: "Application code"

              - column:

                  name: biz_modified_instant

                  type: datetime

                  constraints:

                    nullable: true

                  remarks: "Last business user modification instant [not a technical field]"               

              - column:

                  name: biz_modified_by

                  type: bigint

                  constraints:

                    nullable: true

                  remarks: "Last business user modifier [not a technical field]"                 

              - column:

                  name: status

                  type: INTEGER

                  constraints:

                    nullable: false

                  remarks: "Status"                    

              - column:

                  name: learning_object_name

                  type: VARCHAR(1024)

                  constraints:

                    nullable: true

                  remarks: "Learning object name"                 

              - column:

                  name: learning_object_description

                  type: VARCHAR(4000)

                  constraints:

                    nullable: true

                  remarks: "Learning object description"                    

              - column:

                  name: remarks_uploader

                  type: VARCHAR(4000)

                  constraints:

                    nullable: true

                  remarks: "Remarks uploader"                       

              - column:

                  name: remarks_library

                  type: VARCHAR(4000)

                  constraints:

                    nullable: true

                  remarks: "Remarks library"     

              - column:

                  name: file_upload_type

                  type: VARCHAR(7)

                  constraints:

                    nullable: false

                  remarks: "File upload type. Check in ('PARTIAL',TOTAL')"

              - column:

                  name: s3_bucket_name

                  type: clob

                  constraints:

                    nullable: true

                  remarks: "S3 bucket name"

              - column:

                  name: s3_object_key

                  type: clob

                  constraints:

                    nullable: true

                  remarks: "S3 object key"

        - addPrimaryKey:

            columnNames: id

            constraintName: pk_alod

            tableName: application_learning_object_delivery

        - addForeignKeyConstraint:

            baseColumnNames: application_code

            baseTableName: application_learning_object_delivery

            constraintName: fk_alod_application

            referencedColumnNames: code

            referencedTableName: application

        - addForeignKeyConstraint:

            baseColumnNames: status_code

            baseTableName: application_learning_object_delivery

            constraintName: fk_alod_application

            referencedColumnNames: id

            referencedTableName: learning_object_delivery_status

        - createIndex:

            indexName: app_act_idx

            tableName: application_learning_object_delivery

            columns:

              - column:

                  name: application_code

                  type: bigint

              - column:

                  name: active

                  type: boolean

create table (PK w/ auto increment, JSONB)

JSONB type is specific of PostgreSQL. H2 uses type JSON

databaseChangeLog:

  - changeSet:

      id: "000"

      author: myusername

      failOnError: true

      comment: "JSONB stores data in a decomposed binary format, which is faster to process and supports indexing "

      changes:

        - createTable:

            tableName: your_table_name

            columns:

              - column:

                  name: id

                  type: bigint

                  autoIncrement: true

                  startWith: 1

                  incrementBy: 1

                  constraints:

                    primaryKey: true

                    nullable: false

              - column:

                  name: data

                  type: JSONB

                  constraints:

                    nullable: false

delete (example)

Delete one or more records of a table.

# DELETE records with agent_idp 2717705

databaseChangeLog:

  - changeSet:

      id: "026"

      author: myusername

      failOnError: true

      comment: "Deleting records with agent_idp 2717705"

      changes:

        - delete:

            tableName: autho_agent_function

            where: agent_idp = 2717705

drop column (example)

Drop specified columns from table.

# This is a YAML comment

databaseChangeLog:

- changeSet:

    id: "077"

    author: thatsme

    failOnError: true

    comment: "Drop a couple of fields"

    changes:

    - dropColumn:

        tableName: mytable

        columns:

        - column: 

            name: my1stfield

        - column: 

            name: my2ndfield

drop index (example)

Drop index of table.

databaseChangeLog:

  - changeSet:

      ...

        - dropIndex:

            tableName: application

            indexName: idx_triplet_status

drop table (example)

databaseChangeLog:

# Audit

  - changeSet:

      id: "045"

      author: myusername

      failOnError: true

      comment: "Drop tables: notif_academic_record, notif_veteran"

      changes:

        - dropTable:

            tableName: notif_academic_record_aud

            cascadeConstraints: false

        - dropTable:

            tableName: notif_veteran_aud

            cascadeConstraints: false

# Regular

        - dropTable:

            tableName: notif_academic_record

            cascadeConstraints: false

        - dropTable:

            tableName: notif_veteran

            cascadeConstraints: false

insert (example)

Insert records into database table.

Under databaseChangeLog > changeSet > changes:

        - insert:

            tableName:  learning_object_delivery_status

            columns:  

              - column:  

                  name: id 

                  value: 222

              - column:  

                  name: msgid 

                  value: lods_pending_library

              - column:  

                  name: meaning 

                  value: Library pending

primary key modification (example)

databaseChangeLog:

  - changeSet:

      id: "037"

      author: wsparcie

      failOnError: true

      comment: "PK certificate"

      changes:

        - dropPrimaryKey:

            tableName: certificate

        - dropNotNullConstraint:

            columnDataType: varchar(8)

            columnName: certificate_code

            tableName: certificate

        - addNotNullConstraint:

            columnDataType: bigint

            columnName: certificate_application_code

            tableName: certificate

        - addPrimaryKey:  

            columnNames: academic_record_code, certificate_application_code

            constraintName: pk_certificate

            tableName: certificate  

 # Audit

        - addNotNullConstraint:

            columnDataType: bigint

            columnName: rev

            tableName: certificate_aud

        - addNotNullConstraint:

            columnDataType: bigint

            columnName: academic_record_code

            tableName: certificate_aud

        - addNotNullConstraint:

            columnDataType: bigint

            columnName: certificate_application_code

            tableName: certificate_aud

        - addPrimaryKey:  

            columnNames: rev, academic_record_code, certificate_application_code

            constraintName: pk_certificate_aud

            tableName: certificate_aud

renameColumn (example)

databaseChangeLog:

  - changeSet:

      id: "086"

      author: thatsme

      failOnError: true

      comment: "Rename column 'value' to 'val'"

      changes:

        - renameColumn:

            tableName: config_var

            oldColumnName: value

            newColumnName:  val

            columnDataType: varchar

            remarks:  Value of 'code'

update (example)

databaseChangeLog:

  - changeSet:

      id: "027"

      author: thatsme

      failOnError: true

      comment: "Deactivation of record"

      changes:

        - update:

            tableName: my_table_name

            columns:

             - column:

                  name: my_field_name

                  type: boolean

                  value: false

            where: another_field = 2 AND other_field = 7