Prolog
Selamat siang, para pembaca yang budiman. Baru-baru ini, saya harus menguasai area baru CI / CD, menyiapkan dari awal pengiriman skrip migrasi database di salah satu proyek. Pada saat yang sama, sulit untuk mengatasi tahap pertama "mata takut", saat tugas tampak jelas, tetapi Anda tidak tahu harus mulai dari mana. Namun, pertanyaan tersebut ternyata jauh lebih sederhana dari yang terlihat pada awalnya, sekaligus memberikan keuntungan yang tak terbantahkan dengan biaya beberapa jam kerja dan tidak memerlukan dana tambahan, kecuali yang disebutkan dalam judul.
, , , , .
, . C#/.NET, Vue.js Postgres.
CI/CD " " CI/CD, .
, CI/CD, , , , CI/CD, .
GitLab + Liquibase , , , . GitLab CI/CD & 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 , , . , , , .
.
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
:
, :
|
|
|
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
.
, :
(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
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.
Jika beberapa momen tertinggal di belakang layar, dan ada keinginan untuk mempelajarinya lebih lanjut, tulis di komentar. Aku akan mengingatnya, tapi aku tidak akan menjanjikan apapun, tk. artikel ini, misalnya, membutuhkan waktu lebih dari 20 jam selama tiga minggu, yang berarti dua hingga tiga kali lipat waktu penulisan artikel rata-rata.
Seperti biasa, jika Anda menyukai artikel itu, lihat artikel lainnya: