🚉 🕦 🆗 Einrichten von CI / CD-Skripten für die Datenbankmigration von Grund auf mit GitLab und Liquibase 🧑🏼‍🤝‍🧑🏻 🤵🏻 🍗

Prolog

Guten Tag, liebe Leser. Vor kurzem musste ich einen neuen Bereich von CI / CD beherrschen und die Bereitstellung von Datenbankmigrationsskripten in einem der Projekte von Grund auf neu einrichten. Gleichzeitig war es schwierig, die allererste Phase von "Die Augen haben Angst" zu überwinden, wenn die Aufgabe klar zu sein scheint, Sie aber nicht wissen, wo Sie anfangen sollen. Die Frage stellte sich jedoch als viel einfacher heraus, als es zunächst schien, während sie unbestreitbare Vorteile auf Kosten von mehreren Arbeitsstunden bot und keine zusätzlichen Mittel erforderte, außer den im Titel angegebenen.

, , , , .

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

. , , . ~~, -~~ .

Zusammenführen in Produkt, manueller Start — ,

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.

Wenn einige Momente hinter den Kulissen bleiben und Sie mehr darüber erfahren möchten, schreiben Sie in die Kommentare. Ich werde daran denken, aber ich werde nichts versprechen, tk. Dieser Artikel hat zum Beispiel drei Wochen lang über 20 Stunden gedauert, was dem Zwei- bis Dreifachen der durchschnittlichen Artikelschreibzeit entspricht.

Wenn Ihnen der Artikel gefallen hat, schauen Sie sich wie gewohnt die anderen an:

Interessanter Artikel, den man unbedingt lesen muss: Cleverer Parser einer in Worten geschriebenen Zahl
Automatische Aktualisierung von Skripten nach der Bereitstellung
JavaScript-Klassenserialisierung mit zyklischen Referenzen

Einrichten von CI / CD-Skripten für die Datenbankmigration von Grund auf mit GitLab und Liquibase

Prolog

GitLab CI/CD

Liquibase

Liquibase

changelog

changeset

DATABASECHANGELOG

runAlways

SQL-

Liquibase

Java

Liquibase

Git

GitLab Runner

GitLab Runner

CI/CD

(stages)

(jobs)

More articles: