👨🏼‍🤝‍👨🏻 🏇🏼 ☝🏽 GitLabとLiquibaseを使用してデータベースを最初から移行するためのCI / CDスクリプトを設定する 🤞🏻 👛 🧚

プロローグ

こんにちは、読者の皆様。ごく最近、CI / CDの新しい領域をマスターし、プロジェクトの1つでデータベース移行スクリプトの配信を最初から設定する必要がありました。同時に、課題が明確に見えても「目が怖い」という最初の段階を乗り越えるのは困難でしたが、どこから始めればよいのかわかりません。しかし、質問は当初の見た目よりもはるかに単純であることが判明しましたが、タイトルに示されているものを除いて、数時間の作業と追加の資金を必要とせずに否定できない利点がありました。

, , , , .

, . C#/.NET, Vue.js Postgres.

CI/CD " " CI/CD, .

, CI/CD, , , , CI/CD, .

GitLab + Liquibase , , , . GitLab CI/CD & Liquibase .

, , . , .

Liquibase. , .

GitLab Community Edition – Git
GitLab Runner – CI/CD
Liquibase, 4.3.4
Liquibase
Liquibase GitLab Runner GitLab . .
3 . .

, , , Premium-.

( ) : https://github.com/Doomer3D/Gliquibase.

GitLab CI/CD

CI/CD , , , ( /) ( ) , . GitLab pipelines, GitLab Runner, GitLab , .

GitLab Community Edition 13.x.

Liquibase

Liquibase («») – c , . , Liquibase (changeset). SQL-, - , . : https://www.liquibase.org/get-started/databases.

Liquibase Java, JVM.

Oracle 19 , . , , .

, , , .

, CI/CD, (pipeline), (stage), (job).

– (deploy), .. . , . , – . , – (deploy-dev) (deploy-prod), . , , , .

(GitLab Runner) – , , . , , / , CI/CD, , , , ..

! GitLab http, , GitLab, GitLab , .

– , , , , .

Liquibase . Liquibase , Liquibase .

, , , . :

failed:

, , . , VPN - GitLab , pending, .. , , . hosts .

, , :

.
, .
, .
, Liquibase.
Liquibase /.
...
Profit!

Liquibase

Liquibase. , , Liquibase.

Liquibase: https://docs.liquibase.com/concepts/basic/home.html

, , , , Liquibase, «», , , .

Liquibase – . (changeset) – . , , , /, . (changelog), , , .

changelog

Liquibase . – , . / . , : SQL, XML, JSON YAML. SQL XML .

, .. . master.xml. . , , . :

, .
, , .

, // - , . .

Liquibase db/changelog, - master.xml

, 156 157, . = . common , -, . -, .

master.xml:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:pro="http://www.liquibase.org/xml/ns/pro"
                   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-4.2.xsd     http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.2.xsd ">

  <preConditions>
    <dbms type="oracle" />
  </preConditions>

  <!--   -->
  <include file="/common/pre_migration.xml" />

  <!--  -->
  <includeAll path="/v156" relativeToChangelogFile="true" />
  <includeAll path="/v157" relativeToChangelogFile="true" />

</databaseChangeLog>

XML- , . XML- Liquibase.

preConditions , , oracle, , . preConditions .

. include ( ) includeAll ( ).

includeAll - , , .. . + , , , .

includeAll - ( ), , .

changeset

. 2021-05-01 TASK-001 CREATE TEST TABLE.xml:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:pro="http://www.liquibase.org/xml/ns/pro"
                   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-4.2.xsd     http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.2.xsd ">

  <changeSet author="Doomer" id="20210501-01">
    <preConditions onFail="MARK_RAN">
      <not>
        <tableExists tableName="TEST"/>
      </not>
    </preConditions>
    <createTable tableName="TEST" remarks=" ">
      <column name="ID" type="NUMBER(28,0)" remarks="">
        <constraints nullable="false" primaryKey="true" primaryKeyName="TEST_PK" />
      </column>
      <column name="CODE" type="VARCHAR2(64)" remarks="">
        <constraints nullable="false" />
      </column>
      <column name="NAME" type="VARCHAR2(256)" remarks="">
        <constraints nullable="false" />
      </column>
    </createTable>
    <rollback>
      <dropTable tableName="TEST" />
    </rollback>
  </changeSet>

</databaseChangeLog>

, TEST . , .

preConditions , , .

rollback , , . , , , .


id	, , . .
author	, , . .
dbms	, .
contexts	, . , , , . .
runAlways	. , . , . .
runOnChange	. , . recreatable-, , , ..

DATABASECHANGELOG

, , DATABASECHANGELOG, DATABASECHANGELOGLOCK, Liquibase.

, – , . , <>-<>< >, 20210501-01KD. , Liquibase, , .

MD5- , , . , Liquibase. , , MD-5 . , DATABASECHANGELOG.

runAlways

, , , , , , Liquibase, - . USER_ID.

- -, . :

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:pro="http://www.liquibase.org/xml/ns/pro"
                   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-4.2.xsd     http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.2.xsd ">

  <changeSet author="SYSTEM" id="PRE_MIGRATION" runAlways="true">
    <sql splitStatements="true" stripComments="true">
      --      liquibase
      CALL DBMS_SESSION.SET_CONTEXT('CLIENTCONTEXT','USER_ID', 13);
    </sql>
  </changeSet>

</databaseChangeLog>

SQL-, USER_ID 13 – Liquibase. , runAlways .

SQL-

SQL, . 2021-05-01 TASK-002 TEST.sql, TEST:

--liquibase formatted sql


--changeset Doomer:20210501-02
--preconditions onFail:MARK_RAN
--precondition-sql-check expectedResult:1 SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME = 'TEST' AND OWNER = 'STROY';
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM TEST WHERE ID = 1;
insert into TEST (ID, CODE, NAME)
values (1, 'TEST', '- ');
--rollback not required


--changeset Doomer:20210501-03
--preconditions onFail:MARK_RAN
--precondition-sql-check expectedResult:1 SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME = 'TEST' AND OWNER = 'STROY';
--precondition-sql-check expectedResult:1 SELECT COUNT(*) FROM TEST WHERE ID = 1;
update TEST
   set NAME = 'CONTEXT USER_ID=' || nvl(SYS_CONTEXT('CLIENTCONTEXT', 'USER_ID'), 'NULL')
 where ID = 1;
--rollback not required

TEST, ID = 1. , , DATABASECHANGELOG (MARK_RAN). preConditions.

USER_ID.

TEST:

, Liquibase , RTFM.

Liquibase

Liquibase , , . , GitLab Runner', , Liquibase .

Liquibase: https://docs.liquibase.com/commands/community/home.html

update –
updateSQL – SQL- ,

, :


changeLogFile	- ,	master.xml
url	,	jdbc:oracle:thin:1.2.3.4:1521:orastb
username	,	vasya
password	,	pupkin
defaultSchemaName		DATA
contexts		dev / prod
driver		oracle.jdbc.OracleDriver
classpath		/usr/share/liquibase/4.3.4/drivers/ojdbc10.jar
outputFile	, updateSQL. , .

, Liquibase c . , (. ). Liquibase , , .

Windows:

call "C:\Temp\liqui\liquibase-4.3.1\liquibase.bat" ^
--defaultSchemaName=STROY ^
--driver=oracle.jdbc.OracleDriver ^
--classpath="C:\Temp\liqui\ojdbc5.jar" ^
--url=jdbc:oracle:thin:@1.2.3.4:1521:dev ^
--username=xxx ^
--password=yyy ^
--changeLogFile=.\master.xml ^
--contexts="dev"
--logLevel=info ^
updateSQL

, , . , , , GitLab, . .

Centos 7, . , Linux.

Java

Liquibase Java 11+, . OpenJRE 11:

sudo yum install java-11-openjdk
java --version

Liquibase

: https://www.liquibase.org/get-started/quickstart

Liquibase , Java. . , /usr/share/liquibase/<version>, /usr/share/liquibase/4.3.4

, Liquibase, drivers . ojdbc10.jar

, Liquibase :

cd /usr/share/liquibase/4.3.4
liquibase --version

Git

Git GitLab Runner , , Centos 7 , , . GitLab Runner , git , 1.8. , GitLab , - , , CI/CD .

, git GitLab Runner:

#    
git --version

#  ,    1.8
sudo yum remove git*

#         (2.30)
sudo yum -y install https://packages.endpoint.com/rhel/7/os/x86_64/endpoint-repo-1.7-1.x86_64.rpm
sudo yum install git

GitLab Runner

: https://docs.gitlab.com/runner/install/linux-manually.html

#  
curl -L "https://packages.gitlab.com/install/repositories/runner/gitlab-runner/script.rpm.sh" | sudo bash

# 
export GITLAB_RUNNER_DISABLE_SKEL=true; sudo -E yum install gitlab-runner

GitLab Runner

, GitLab Runner, Liquibase.

GitLab Runner: https://docs.gitlab.com/runner/configuration/

, , , :

#   
which gitlab-runner # /usr/bin/gitlab-runner

#    
sudo chmod +x /usr/bin/gitlab-runner

, . , .

#  
sudo useradd --comment 'GitLab Runner' --create-home gitlab-runner --shell /bin/bash

#  
sudo gitlab-runner install --user=gitlab-runner --working-directory=/home/gitlab-runner

systemctl:

#  
sudo gitlab-runner status

#  
sudo gitlab-runner start

#  
sudo gitlab-runner stop

#    
sudo gitlab-runner list

GitLab Runner Liquibase. register

, GitLab.

GitLab Settings ⇨ CI/CD ⇨ Runners

. , , . ( ):

GitLab. .
. , .

, . , , , . , , .

sudo gitlab-runner register

Enter the GitLab instance URL

GitLab
Enter the registration token
Enter a description for the runner

, , my-awesome-runner
Enter tags for the runner

. : liquibase,dev

GitLab CI/CD
Enter an executor

. .

shell

shell – . bash.

sudo gitlab-runner list

GitLab CI/CD:

CI/CD

, CI/CD . .gitlab-ci.yml . , bash, , , /ci

.

, , , , .

, .gitlab-ci.yml

, ci/lint

, : https://gitlab.example.com/gitlab-org/my-project/-/ci/lint. . , YAML.

variables:
    LIQUIBASE_VERSION: "4.3.4"

stages:
    - deploy

deploy-dev:
    stage: deploy
    tags:
        - liquibase
        - dev
    script:
        - 'bash ./ci/deploy-db.sh $DEV_DB $DEV_DB_USER $DEV_DB_PASS'
    environment:
        name: dev
    only:
        - dev

deploy-prod:
    stage: deploy
    tags:
        - liquibase
        - prod
    script:
        - 'bash ./ci/deploy-db.sh $DEV_DB $DEV_DB_USER $DEV_DB_PASS'
    environment:
        name: prod
    when: manual
    only:
        - prod

: https://docs.gitlab.com/ee/ci/variables/README.html

variables:
    LIQUIBASE_VERSION: "4.3.4"

, . LIQUIBASE_VERSION Liquibase, . , Liquibase .

, , , , .

Settings ⇨ CI/CD ⇨ Variables

.

, :

, , , CI/CD, , , . , , .

(stages)

stages:
    - deploy

, .

(jobs)

deploy-dev:
    stage: deploy
    tags:
        - liquibase
        - dev
    script:
        - 'bash ./ci/deploy-db.sh $DEV_DB $DEV_DB_USER $DEV_DB_PASS'
    environment:
        name: dev
    only:
        - dev

stage: deploy

, .

, .. liquibase dev. script

. , prod, , , , , . , , .

environment , Operations ⇨ Environments

. (, , ..), , , . , , -, .

only

, . except

, . : https://docs.gitlab.com/ee/ci/jobs/job_control.html.

when: manual

. , , . ~~, -~~ .

script

, . shell- , bash. , .. , .

Liquibase:

#!/bin/bash
echo "Environment: $CI_ENVIRONMENT_NAME"
cd db/changelog
/usr/share/liquibase/$LIQUIBASE_VERSION/liquibase \
    --classpath=/usr/share/liquibase/$LIQUIBASE_VERSION/drivers/ojdbc10.jar \
    --driver=oracle.jdbc.OracleDriver \
    --changeLogFile=master.xml \
    --contexts="$CI_ENVIRONMENT_NAME" \
    --defaultSchemaName=STROY \
    --url=jdbc:oracle:thin:@$1 \
    --username=$2 \
    --password=$3 \
    --logLevel=info \
    update

Liquibase .

DEV_DB, DEV_DB_USER, DEV_DB_PASS $1, $2 $3 . , $CI_ENVIRONMENT_NAME, , - , .

, , .

Liquibase :

, . , CI/CD . , Liquibase , , , .

General ⇨ Merge requests

.

! CI/CD, .

, . - 5 . , , - CI/CD.

舞台裏にいくつかの瞬間が残されていて、それらについてもっと知りたいという願望がある場合は、コメントに書き込んでください。私は心に留めておきますが、私は何も約束しません、tk。たとえば、この記事では、3週間で20時間以上かかりました。これは、平均的な記事の執筆時間の2〜3倍です。

いつものように、あなたがその記事が好きなら、他のものをチェックしてください：

GitLabとLiquibaseを使用してデータベースを最初から移行するためのCI / CDスクリプトを設定する

プロローグ

GitLab CI/CD

Liquibase

Liquibase

changelog

changeset

DATABASECHANGELOG

runAlways

SQL-

Liquibase

Java

Liquibase

Git

GitLab Runner

GitLab Runner

CI/CD

(stages)

(jobs)

More articles: