Basic instructions for Liquibase

introduce

Liquibase is an open source tool for Database Reconstruction and migration. It records database changes in the form of log files, and then performs modifications in the log files to update or rollback the database to a consistent state. Its goal is to provide a database type independent solution to achieve migration by executing schema type files. Its advantages mainly include the following:

Support almost all mainstream databases, such as MySQL, PostgreSQL, Oracle, Sql Server, DB2, etc;
Support the collaborative maintenance of multiple developers;
Log files support multiple formats, such as XML, YAML, JSON, SQL, etc;
It supports multiple operation modes, such as command line, Spring integration, Maven plug-in, Gradle plug-in, etc.

install

Download and unzip Liquibase: https://download.liquibase.org
Install java (configure environment variables)
Download the database driver package and put it in lib under the Liquibase directory

use

There are two types of usage: one is used in a completely new project, and the other is used in an old project (that is, the database already has a historical table structure and data, so it is necessary to reverse generate changeLog).

Two liquibase schemes are used for old projects:

1: You can sort out the current ddl in the project database (maybe not only ddl, but also some table data should be maintained in liquibase, depending on the specific project requirements), and maintain it in an initialized changeLog as an initialization script, In this way, the initialization script can be executed in the new environment (* * in this case, special attention should be paid to specifying context in the changeSet, and context must also be specified during execution to avoid executing the initialization script in the old environment * *).

2: liquibase has a special command generateChangeLog to reverse generate changeLog. (I haven't tried whether it's easy to use or not. I'll add it when I have tried...)

liquibase building directories in a project

src/main/db/sql/init-personal_bank_swift.sql

Define an sql file that can be directly referenced and executed by changeSet in another changeLog

INSERT INTO personal_bank_swift (bank_code, clearing_code, swift_code, create_by, create_at, updated_by, updated_at) VALUES 
('bank_code2300', '012', 'BKCHHKHHXXX', null, null, null, null),
('bank_code2302', '009', 'CCBQHKAXXXX', null, null, null, null),
('bank_code2304', '041', 'LCHBHKHHXXX', null, null, null, null),
('bank_code2305', '040', 'DSBAHKHHXXX', null, null, null, null),
('bank_code2306', '032', null, null, null, null, null);

src/main/db/2019/08/2600-init-tables.xml

Changelog:xml (* * refers to the concept of changelog in liquibase, which can reference other changelogs in multiple layers * *)
include: label referencing changelog
File: texture of the referenced changelog file
changeSet: a changeLog can contain multiple changeSet tags. Each changeSet is uniquely identified by the ID, author and filepath attributes. When Liquibase executes the database changeLog, it reads the changesets in sequence, and checks the databasechangelog table for each changeSet to see whether the combination of id/author/filepath has been run. If it is already running, the changeSet will be skipped unless there is a real runAlways tag. After running all the changes in changeSet, Liquibase inserts a new row with id/author/filepath and MD5Sum of changeSet into databasechangelog. The transactions of each changeSet are separate. The best practice is to ensure that each changeSet is atomically changed as much as possible to avoid the failure of the remaining unprocessed statements in the database in the unknown state.
author: author
id: id of changeSet (* * it is better to use the date format of 2019082600-01 to make it clearer. If a target database may be executed by multiple project liquibase s, this id will be written to the same databasechangelog table, which can be distinguished by adding the project name or the abbreviation of the project name before the date, such as reference-2019082600-01)
runAlways: perform changes to the settings at each run time, even if the changes have been run before
Context: it can be used to flexibly control the environment in which scripts are executed. Our system generally defines the environment (such as team2,uat,prod). If there are multiple environments, it can be used. or and segmentation is also supported! The negative form of prod (* * if context is defined, context must be specified during execution. If it is not specified, all sql will be retrieved and executed, and the context will become invalid)
Comment: description of changeset.
preConditions: preConditions that must be passed before changeSet will be executed. It can be used to perform data integrity check for unrecoverable content
rollback: describes how to RollBACK SQL statement or refactoring label for changeSet
createTable: create a label for the table
tableName: table name
column: label of table field
Name: field name
Type: field type
Remarks: field remarks
Constraints: field constraints
primaryKey: true means primary key
nullable: true,false
unique: true,false
createIndex: create index label
indexName: index name
tableName: the name of the table to create the index
column: label of the index field (multiple represents the union index)
Name: field name
addColumn: add field label
tableName: the table name of the added field
column: added field
Name: field name
Type: field type
sql: sql tag. The content can be sql statements directly
endDelimiter: the delimiter to apply to the end of the statement. The default value is;, Can be set to ''.
splitStatement: true,false
stripComments: set to true to delete any comments in the SQL before execution, otherwise false. If
If not set, the default value is false
Comment: comment
sqlFile: label referencing sql file
path: the address of the referenced sql file

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
   <!--The first tab creation method-->
  <changeSet author="future_zwp (generated)" id="reference-2019082600-00" context="team2,uat,prod">
        <createTable tableName="personal_bank_swift">
            <column name="id" type="serial">
                <constraints primaryKey="true"/>
            </column>
            <column name="bank_code" type="text" remarks="Bank code"></column>
            <column name="clearing_code" type="text" ></column>
            <column name="swift_code" type="text" ></column>
            <column name="create_by" type="text" ></column>
            <column name="created_at" type="timestamp"></column>
            <column name="updated_by" type="text" ></column>
            <column name="updated_at" type="timestamp"></column>
            <column name="pt" type="text"></column>
        </createTable>
        <rollback>
            <dropTable tableName="personal_bank_swift"/>
        </rollback>
    </changeSet>

    <changeSet author="future_zwp (generated)" id="reference-2019082600-01" context="team2,uat,prod">
        <createIndex indexName="idx_bank_info_bank_clearing" tableName="personal_bank_swift">
            <column name="bank_code"/>
            <column name="clearing_code"/>
        </createIndex>
    </changeSet>

    <changeSet author="future_zwp (generated)" id="reference-2019082600-02" context="team2,uat,prod">
        <createIndex indexName="idx_personal_bank_swift_swift_code" tableName="personal_bank_swift">
            <column name="swift_code"/>
        </createIndex>
    </changeSet> 
  
   <!--Second sql Table creation method,be-all sql Statements are supported, with low learning cost and more flexibility-->
  <changeSet author="zhaowenpeng" id="reference-2019082600-03" context="team2,uat,prod">
        <sql splitStatements="true">
            drop table if exists personal_bank_swift;

            create table personal_bank_swift
            (
              id serial primary key,
            bank_code     text,
            clearing_code text,
            swift_code    text,
            create_by     text,
            create_at     timestamp(6),
            updated_by    text,
            updated_at    timestamp(6)
            );

            comment on column personal_bank_swift.bank_code
            is 'Bank code';

            create index idx_bank_info_bank_clearing on personal_bank_swift(bank_code,clearing_code);
            create index idx_personal_bank_swift_swift_code on personal_bank_swift(swift_code);
        </sql>
    </changeSet>
  
   <!--quote sql file-->
   <changeSet author="zhaowenpeng" id="reference-2019082600-04" context="team2,uat,prod">
        <sqlFile  path="sql/init-personal_bank_swift.sql"></sqlFile>
    </changeSet>
</databaseChangeLog>

src/main/db/2019/driver.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd">


    <include file="2019/08/2600-init-tables.xml"/>


</databaseChangeLog>

src/main/db/driver.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd">

    <include file="2019/driver.xml"/>

    <include file="2020/driver.xml"/>

</databaseChangeLog>

Execute changeLog locally

src/main/db/update.sh (this file is only executed locally. idea can install bashSupport, and right-click the file to execute it)

The contexts value corresponds to the context value of the changeSet (if it is not satisfied, the changeSet will not be executed), and the changeLogFile will get the read execution

The lines are the same as the driver xml

#!/usr/bin/env bash
liquibase   \
    --driver=org.postgresql.Driver       \
    --changeLogFile=driver.xml     \
    --url=jdbc:postgresql://118.31.105.14:3546/team2_reference_data   \
    --username=team2_app    \
    --password=8sVG98uziKfAEqzM     \
    --contexts=team2 \
   update

Execution completed:

Jenkins perform steps

src/main/db/maycur/update-db.sh

#!/bin/bash

DB_HOST="127.0.0.1"
DB_PORT="3306"
DB_TYPE="mysql"
DB_NAME="maycur-pro"
DB_USER="maycur"
DB_PASSWORD="Maycur@2018"

DB_DRIVER="com.mysql.jdbc.Driver"
OPERATOR="update"
CONTEXTS=''

function help_info() {
    echo "Please use the canonical setttings: "
    echo "./update-db.sh -h <DB_HOST> -p <DB_PORT> -d <DB_NAME> -u <DB_USER> -w <PASSWORD> -t <DB_TYPE> \
          -o <OPERATOR>"
    echo "ATTENTION! DB_TYPE alternatives would be *mysql* or *postgresql*, nothing more"
    exit 1
}

while [[ $# -gt 0 ]]
do 
    case "$1" in
        "-h")
            shift
            echo "DB_HOST: $1"
            DB_HOST=$1
            ;;
        "-p")
            shift
            echo "DB_PORT: $1"
            DB_PORT=$1
            ;;
        "-d")
            shift
            echo "DB_NAME: $1"
            DB_NAME=$1
            ;;
        "-u")
            shift
            echo "DB_USER: $1"
            DB_USER=$1
            ;;
        "-w")
            shift
            #echo "DB_PASSWORD: $1"
            DB_PASSWORD=$1
            ;;
        "-t")
            shift
            echo "DB_TYPE: $1"
            DB_TYPE=$1
            if [ "$DB_TYPE" = "mysql" ]; then
                DB_DRIVER="com.mysql.jdbc.Driver"  
            elif [ "$DB_TYPE" = "postgresql" ]; then
                DB_DRIVER="org.postgresql.Driver"
            else 
                help_info
            fi
            echo "DB_DRIVER: $DB_DRIVER"
            ;;
        "-c")
            shift
            echo "CONTEXTS: $1"
            CONTEXTS=$1
            ;;
        "-o")
            shift
            OPERATOR=$1
            ;;
        *)
            shift
            ;;
    esac
    shift
done

echo "NOW COMES the EXECUTION..."

/opt/liquibase/liquibase \
            --driver=$DB_DRIVER \
            --changeLogFile=driver.xml \
            --url="jdbc:$DB_TYPE://$DB_HOST:$DB_PORT/$DB_NAME?useSSL=false&useUnicode=yes" \
            --username=$DB_USER \
            --password=$DB_PASSWORD \
            --contexts=$CONTEXTS \
                    $OPERATOR

if [ $? -eq 0 ]; then
    echo "Congratulations! Things are all set, you are good to go!"
else 
    echo "Oops! Something just went wrong. You're gonna have to take a look at it"
    exit 1
fi

Create a project on jenkins and configure

cd src/main/db
/bin/bash update-db.sh -h maycurpg10-uat.pg.rds.aliyuncs.com -p 3433 -u team2_app -d team2_reference_data -w ${TEAM2_DB_PASSWORD} -t postgresql -c team2 -o update

Just save the execution (before execution, make sure whether the server has installed liquibase and whether the required database driver jar package is placed in the lib folder under the liquibase directory)

update-db.sh Parameter Description:

cd src/main/db this is to enter update db SH existing directory

-h database connection host

-p database connection port

-u database connection user name

-d database name

-w database connection user password (the name configured in jenkins is referenced here)

-Tdatabase type

-c specifies the contexts. It needs to cooperate with the context in the changeSet to conditionally execute sql

-o update,clearCheckSums

After the first execution, there will be two more tables in the target database:

DATABASECHANGELOG table

Liquibase uses the databasechangelog table to track the changesets that have been run.

The table tracks each change setting as a row, identified by a combination of the id of the path where the changelog file is stored, and the author and filename columns.

column	Standard data type	describe
ID	VARCHAR(255)	id attribute value in changeSet
AUTHOR	VARCHAR(255)	author property value in changeSet
FILENAME	VARCHAR(255)	Path to changelog. This may be an absolute path or a relative path, depending on how changelog is passed to Liquibase. For best results, it should be a relative path
DATEEXECUTED	DATETIME	Date / time the changeSet was executed. Used with ORDEREXECUTED to determine rollback order
ORDEREXECUTED	INT	The order in which changeSet is executed. In addition to DATE EXECUTED, it is also used to ensure that the sequence is correct, even if the database date and time support poor precision. Note: the guaranteed value increases only in a single update run. Sometimes they restart at zero.
EXECTYPE	VARCHAR(10)	Description of how changeSet is executed. Possible values are EXECUTED, FAILED, SKIPPED, RERAN, and MARK_RAN
MD5SUM	VARCHAR(35)	Verification when changeSet is executed. Used for each run to ensure that the changeSet in the changelog file has not changed unexpectedly
DESCRIPTION	VARCHAR(255)	Readable description generated by changeSet
COMMENTS	VARCHAR(255)	Value of the comment tag of changeSet
TAG	VARCHAR(255)	The trace of changeSet corresponds to label operation.
LIQUIBASE	VARCHAR(20)	Liquibase version used to execute changeSet

**The table has no primary key. This is to avoid any database specific restrictions on key length.

DATABASECHANGELOGLOCK table

Liquibase uses the databasechangeloglock table to ensure that only one instance of liquibase is running at a time.

Because Liquibase only reads from the databasechangelog table to determine the changeSet that needs to be run, conflicts will occur if multiple Liquibase instances are executed on the same database at the same time. This can happen if multiple developers use the same database instance, or if multiple servers in the cluster automatically run Liquibase at startup.

column	Standard data type	describe
ID	INT	ID of the lock. There is only one lock at present, but it will be useful in the future
LOCKED	INT	Set to '1' if Liquibase is running against this database. Otherwise set to "0"
LOCKGRANTED	DATETIME	Date and time the lock was acquired
LOCKEDBY	VARCHAR(255)	Description of who is locked

**If Liquibase does not exit cleanly, locked rows may remain locked. You can clear the current lock by running UPDATE DATABASECHANGELOGLOCK SET LOCKED=0

Common errors:

1: After modifying the historical script file, the following errors will be reported during execution

At this time, if you need to ignore the changes of this script, you can set src/main/db/update Change the update value in SH to clearCheckSums, and then execute the script again

2: If the execution cannot be completed, the database exception liquibaselock may be locked.

At this time, you can connect to the database executing the script, select * from databasechangeloglock;

If it is found that locked is locked, stop the script, change locked to 0, and then re execute the script.

ps: file package optimization

1. scenario 1:

If you put all the xml files in one package, the file structure will not be very beautiful after a long time. Driver The content in xml is also very long. Each time a script is added, it needs to be pulled to the bottom of the file. The experience is extremely bad. Therefore, it is very important to define a sustainable extended file structure from the beginning. The solution is as shown in the figure: add a new year directory in one year

Create db directory
Create a directory of the year under the db directory
Create month directory and year level driver under year directory xml
Create a script to execute under the month directory
Create a driver at the year level under the year directory xml (include xml files in all months under the directory of the year)
Create a db level driver in the db directory XML (include driver.xml files in all years under the db directory)
When executing, execute the driver in the db directory XML is OK

2. scenario 2:

We have two product lines maycur and ng. Both product lines use the same project. However, some sql scripts in the two product lines may be inconsistent due to differences in some product lines. At this time, it is necessary to distinguish the execution. The strategy of this scheme is to classify the executed files, put the files that can be executed by both product lines into the common folder, put the files that can only be executed by maycur into the bulletproof maycur folder, and put the files that can only be executed by ng into the ng folder. There are separate drivers under the three directories common, maycur, ng, and XML and update sh. In this way, the deployment can be differentiated:

maycur: execute update sh
ng: execute update sh

3. scenario 3:

Sometimes, because of the version problem of a specific environment database, some sql maintained in liquibase will fail to execute. At this time, the script maintained in liquibase will not be modified. Generally, the scheme of modifying sql will be directly executed in the database to achieve the goal. However, this scheme will also be accompanied by some problems (the changeset where the sql is located will not be executed successfully and cannot be executed downward). At this time, we should consider how to ignore this script.

Scheme I (not very recommended):

Manually insert an execution record into the databasechangelog table in the target database. Each changeSet is uniquely identified by the id, author and filepath attributes, Therefore, these three values must strictly correspond to the script to be ignored (you can also insert them from the databasechangelog record of the successfully executed database that copies the corresponding sql. Note that the mismatched values of contexts and liquibase versions need to be changed). Because the md5 values may be different, you should execute clearCheckSums and then re execute the script.

Scheme II (recommended):

A changeSet may have multiple sql scripts. In this scenario, we may change only one sql. At this time, we can change the corresponding sql into a meaningless sql (for example, select 1) to execute the liquibase. At this time, the databasechangelog table has the corresponding execution records. At this time, we can change the sql back to clearCheckSums.

4. scenario 4:

There are many tables with large data volume in our report service. Each time we use liquibase to perform DDL operations on these tables during publishing, because of the characteristics of polardb, it takes a very long time. After the liquibase is finished, we have to run a full number of initialization data warehouse tasks (this operation will clear the table data first), which leads to a further extension of the publishing time. In order to solve this problem, we summarized a scheme.

Release steps:

1. Update changeLogFile in SH specifies 2020/0515/driver_ 1. Execute update sh

2. Data warehouse initialization tasks related to data warehouse running

3 update The changeLogFile in SH specifies the driver Execute update sh

4. Deploy front and back end services

(the first and second steps can be executed in advance because they are both temporary tables.)

We create a package for each version, such as 0515.

Create driver under this package_ 1.xml,driver_2.xml two files

1,driver_1.xml include s the XML file to be executed in the first step, as shown in the following figure. When we want to_ fee_ dynamic_ When adding fields to the detail table, we will create a custom_fee_dynamic_detail_temp, and then in custom_fee_dynamic_detail_ Add fields to the temp table. The initialization task output of the data warehouse can be specified to be output to custom_fee_dynamic_detail_temp

2,driver_2.xml can include the XML file to be executed in the second step, as shown in the following figure. When the initialization task of the data warehouse is specified to be output to custom_fee_dynamic_detail_ After temp, we will drop the old data structure table custom_fee_dynamic_detail, and then we will_ fee_ dynamic_ detail_ Temp temp temp table rename back to custom_fee_dynamic_detail, and then the custom of the new data structure can be referenced elsewhere_ fee_ dynamic_ Detail table