👩🏽‍🤝‍👨🏾 👩🏾‍💻 😶 Configuration de scripts CI / CD pour la migration de base de données à partir de zéro à l'aide de GitLab et Liquibase ⏬ 🤘🏻 👉

Prologue

Bonjour, chers lecteurs. Tout récemment, j'ai dû maîtriser un nouveau domaine de CI / CD, mettant en place à partir de zéro la livraison de scripts de migration de base de données dans l'un des projets. Dans le même temps, il était difficile de surmonter la toute première étape de «les yeux ont peur», lorsque la tâche semble claire, mais que vous ne savez pas par où commencer. Cependant, la question s'est avérée beaucoup plus simple qu'il n'y paraissait au départ, tout en offrant des avantages indéniables au prix de plusieurs heures de travail et ne nécessitant pas de fonds supplémentaires, à l'exception de ceux indiqués dans le titre.

, , , , .

, . 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.

Si certains moments sont laissés dans les coulisses et que vous souhaitez en savoir plus sur eux, écrivez dans les commentaires. Je garderai à l'esprit, mais je ne promettrai rien, tk. cet article, par exemple, m'a pris plus de 20 heures de temps sur trois semaines, soit deux à trois fois le temps d'écriture moyen d'un article.

Comme d'habitude, si vous avez aimé l'article, consultez les autres:

Article à lire absolument: analyseur intelligent d'un nombre écrit avec des mots
Mise à jour automatique des scripts après le déploiement
Sérialisation de classe JavaScript avec références cycliques

Configuration de scripts CI / CD pour la migration de base de données à partir de zéro à l'aide de GitLab et Liquibase

Prologue

GitLab CI/CD

Liquibase

Liquibase

changelog

changeset

DATABASECHANGELOG

runAlways

SQL-

Liquibase

Java

Liquibase

Git

GitLab Runner

GitLab Runner

CI/CD

(stages)

(jobs)

More articles: