Zarządzanie zamianami w relacyjnych bazach danych

Zarządzanie zmianami w relacyjnych bazachdanych

Marcin Stachniuk

30 listopada 2015

Marcin Stachniuk Zarządzanie zmianami w relacyjnych bazach danych 1/63

O mnie

Marcin Stachniuk

Chorąży na chamberconf.pl

Speaker: WrocJUG, dbconf.pl, warsjawa.pl, careercon.pl,chamberconf.pl, 33rd degree 4 charity

Recenzent książki: practicalunittesting.com

[email protected]

mstachniuk.blogspot.com

@MarcinStachniuk

github.com/mstachniuk

Jak zarządzać zmianami w relacyjnych bazach danych?

Jak zarządzać zmianami wrelacyjnych bazach danych?

Ręcznie, bezpośrednio na bazie

Czyli...

...albo...

Word, ERwin

Odpalając SQL-e

Możliwe rozwiązania:

Flyway

c5-db-migration

dbdeploy.com

MyBatis Migrations

MIGRATEdb

DbMaintain

AutoPatch

Porównanie systemów: http://flywaydb.org/#featuresMarcin Stachniuk Zarządzanie zmianami w relacyjnych bazach danych 8/63

Z modelu danych

Hibernate hbm2ddl:

1: spring.jpa.hibernate.ddl-auto=create-drop

Z poziomu kodu Javy

migrate4j

1: public class Migration_1 implements Migration {2:3: public void up() {4: createTable(5: table("simple_table",6: column("id", INTEGER, primarykey(), notnull()),7: column("desc", VARCHAR, length(50), defaultValue("NA"))));8: }9:10: public void down() {11: dropTable("simple_table");12: }13: }

Odstawmy ”Badziewie do badziewia”

Kopsnij Drina - Wielki test piw w plastikuMarcin Stachniuk Zarządzanie zmianami w relacyjnych bazach danych 11/63

Liquibase

http://www.liquibase.org/Marcin Stachniuk Zarządzanie zmianami w relacyjnych bazach danych 12/63

Agenda

1 Jak zarządzać zmianami w relacyjnej bazie danych?

2 Jak działa Liquibase?

3 Od czego zaczać?

4 Tworzenie nowych tabel

5 Migracja danych

6 Różnice w bazach danych

7 Co jeszcze?

8 Podsumowanie

Agenda

3 Od czego zaczać?

5 Migracja danych

7 Co jeszcze?

8 Podsumowanie

Zapisujemy zmiany w changelog’u w XML’u

db.changelog.xml

1: <changeSet id="Release2-01" author="staszek">2: <createTable tableName="user">3: <column name="id" type="INT" autoIncrement="true">4: <constraints nullable="false" primaryKey="true"/>5: </column>6: <column name="email" type="VARCHAR(255)">7: <constraints unique="true" nullable="false"/>8: </column>9: <column name="password" type="VARCHAR(255)">10: <constraints nullable="false"/>11: </column>12: </createTable>13: </changeSet>

Zapisujemy zmiany w changelog’u w XML’u

Lub w innym wspieranym formacie:

Uruchamiamy liquibase:update

Wspierane bazy danych

PostgreSQL

Oracle

Sql Server

Sybase

HSQLDB

Informix

Firebird

SQLite

InterSystems Cache

SAP MaxDB

IBM DB2 for iSeries

Agenda

3 Od czego zaczać?

5 Migracja danych

7 Co jeszcze?

8 Podsumowanie

Jak dodać Liquibase do nowego Spring’owego projektu?

1. Dołączamy zależność

1: <dependency>2: <groupId>org.liquibase</groupId>3: <artifactId>liquibase-core</artifactId>4: <version>3.4.1</version>5: </dependency>

2. Tworzymy changelog’a w:

1: src/main/resources/db.changelog.xml

3. Konfigurujemy połączenie do bazy

4. Wyłączmy hbm2ddl (jak mamy Hibernate’a)

1: spring.jpa.hibernate.ddl-auto=none

Jak dodać Liquibase do nowego Spring’owego projektu?

5. Definiujemy Bean’a

1: @Bean2: public SpringLiquibase liquibase() {3: SpringLiquibase liquibase = new SpringLiquibase();4: liquibase.setChangeLog("classpath:db.changelog.xml");5: liquibase.setDataSource(dataSource());6: return liquibase;7: }

1: <bean id="liquibase" class="liquibase.integration.spring.SpringLiquibase">2: <property name="dataSource" ref="myDataSource" />3: <property name="changeLog" value="classpath:db.changelog.xml" />4: </bean>

6. I działa!

A w przypadku nowego projektu ze Spring Boot’em

Wystarczy dodać zależność do liquibase-corei zdefiniować plik ze zmianami w odpowiednim miejscu:

src/main/resources/db/changelog/db.changelog.xml

Jak wprowadzić Liquibase do istniejącego projektu?

1. Generujemy changelog’a dla istniejącego stanu bazy

1: <plugin>2: <groupId>org.liquibase</groupId>3: <artifactId>liquibase-maven-plugin</artifactId>4: <version>3.4.1</version>5: <configuration>6: <changeLogFile>src/main/resources/db/changelog/db.changelog.xml7: </changeLogFile>8: <driver>com.mysql.jdbc.Driver</driver>9: <url>jdbc:mysql://localhost:3306/public</url>10: <username>liqui</username>11: <password>liqui</password>12: <dropFirst>false</dropFirst>13: </configuration>14: </plugin>

1: mvn liquibase:generateChangeLog

I zapisujemy w:src/main/resources/db/changelog/db.changelog.xml

2. Uruchamiamy:

1: mvn liquibase:changeLogSync

Skutki:

Utworzenie tabel Liquibase’owych

Wypełnienie tabeli DATABASECHANGELOG wpisami napodstawie changelog’a

Operacje zdefiniowane w changset’ach NIE zostaną wykonane

3. Od teraz wszelkie zmiany we wszystkich bazach (w tym naprodukcji) idą przez Liquibase’a!

Agenda

3 Od czego zaczać?

5 Migracja danych

7 Co jeszcze?

8 Podsumowanie

Tworzenie nowych tabel

Przykładowe wymaganie projektowe

Chcę mieć możliwość definiowania użytkowników i ról.

Każdy użytkownik może posiadać kilka ról w systemie.

Na poziomie roli mogę zdefiniować w przyszłości, jakieczynności może wykonać dany użytkownik.Możliwe role:

useradminchangeConfigurationorderAcceptor.

Tworzenie nowych tabel

1: <changeSet id="Release2-01" author="staszek">2: <createTable tableName="user">3: <column name="id" type="INT" autoIncrement="true">4: <constraints nullable="false" primaryKey="true"/>5: </column>6: <column name="email" type="VARCHAR(255)">7: <constraints unique="true" nullable="false"/>8: </column>9: <column name="password" type="VARCHAR(255)">10: <constraints nullable="false"/>11: </column>12: </createTable>13: </changeSet>

Tworzenie kluczy obcych

1: <changeSet id="Release2-04" author="staszek">2: <createTable tableName="user_rights">3: <column name="id" type="INT" autoIncrement="true">4: <constraints nullable="false" primaryKey="true"/>5: </column>6: <column name="user_id" type="INT">7: <constraints nullable="false" foreignKeyName="user_rights_fk"8: referencedTableName="user" referencedColumnNames="id"/>9: </column>10: <column name="role_name" type="VARCHAR(128)">11: <constraints nullable="false"/>12: </column>13: <column name="description" type="VARCHAR(512)">14: <constraints nullable="false"/>15: </column>16: </createTable>17: </changeSet>

Agenda

3 Od czego zaczać?

5 Migracja danych

7 Co jeszcze?

8 Podsumowanie

Tworzymy nowe tabele user rights new i user rights x

Migracja danych

1: <changeSet id="Release3-07" author="staszek">2: <sql>3: insert into user_rights_new select min(id) as id, role_name,4: description from user_rights5: group by role_name6: </sql>7: </changeSet>8: <changeSet id="Release3-08" author="staszek">9: <sql>10: insert into user_rights_x(user_id, right_id) select u.id, urn.id11: from user u join user_rights ur on u.id = ur.user_id12: join user_rights_new urn on ur.role_name = urn.role_name13: </sql>14: </changeSet>

Po migracji danych

Agenda

3 Od czego zaczać?

5 Migracja danych

7 Co jeszcze?

8 Podsumowanie

Modelowanie róźnic w bazach danych

za pomocą tag’a dbms:1: <changeSet id="Release5-01" author="staszek" dbms="h2, mysql, hsqldb">2: <createTable tableName="settings">3: <column name="id" type="INT">4: <constraints primaryKey="true" nullable="false"/>5: </column>6: <column name="is_active" type="bool"/>7: </createTable>8: </changeSet>9:10: <changeSet id="Release5-01" author="staszek" dbms="db2">11: <createTable tableName="settings">12: <column name="id" type="INT">13: <constraints primaryKey="true" nullable="false"/>14: </column>15: <column name="is_active" type="decimal(1)"/>16: </createTable>17: </changeSet>

Modelowanie róźnic w bazach danych

za pomocą parameters (properties):

1: <property name="blob.type" value="blob" dbms="oracle,h2"/>2: <property name="blob.type" value="longtext" dbms="mysql"/>3:4: <changeSet id="Release6-01" author="staszek" >5: <createTable tableName="big_binary_data">6: <column name="id" type="INT">7: <constraints primaryKey="true" nullable="false"/>8: </column>9: <column name="binary_data" type="${blob.type}"/>10: </createTable>11: </changeSet>

Property można przekazać z zewnątrz. Można również uzależnićod kontekstu.

Context’y

application.properties

1: liquibase.contexts = web-test

1: <changeSet id="Release7-01" author="staszek" context="int-test, web-test">2: <insert tableName="settings">3: <column name="id" valueNumeric="1"/>4: <column name="is_active" valueBoolean="true"/>5: </insert>6: </changeSet>

Uwaga!

Jeśli nie zdefiniujemy żadnego kontekstu to WSZYSTKIE zostanąwykonane!

http://www.liquibase.org/documentation/contexts.htmlMarcin Stachniuk Zarządzanie zmianami w relacyjnych bazach danych 39/63

Agenda

3 Od czego zaczać?

5 Migracja danych

7 Co jeszcze?

8 Podsumowanie

Preconditions

Gdy musimy skorzystać z innego użytkownika, zależnie od typubazy danych

1: <preConditions>2: <or>3: <and>4: <dbms type="oracle" />5: <runningAs username="SYSTEM" />6: </and>7: <and>8: <dbms type="mssql" />9: <runningAs username="sa" />10: </and>11: </or>12: </preConditions>

Generowanie rollback’ów

Dla operacji typu:

tworzenie tabel

zmiana nazwy kolumny

dodawanie kolumny

rollback może zostać wygenerowany automatycznie.

Generowanie rollback’ów

Dla innych operacji możemy sami sobie zdefiniować:

1: <changeSet id="Release7-01" author="staszek">2: <insert tableName="settings">3: <column name="id" valueNumeric="1"/>4: <column name="is_active" valueBoolean="true"/>5: </insert>6: <rollback>7: <delete tableName="settings">8: <where>id = 1</where>9: </delete>10: </rollback>11: </changeSet>

Porównywanie baz danych

Diff mode

1: sh liquibase \2: --classpath="h2-1.3.174.jar:mysql-connector-java-5.1.36.jar" \3: --driver=org.h2.Driver \4: --url="jdbc:h2:~/liquibasedemo;AUTO_SERVER=TRUE" \5: --username=liquibasedemo \6: --password=liquibasedemo \7: diff \8: --referenceUrl=jdbc:mysql://localhost:3306/public \9: --referenceUsername=liqui \10: --referencePassword=liqui

działa w linii komend i ant’cie

1: Diff Results:2: Reference Database: liqui@localhost @ jdbc:mysql://localhost:3306/public (Default Schema: public)3: Comparison Database: LIQUIBASEDEMO @ jdbc:h2:~/liquibasedemo (Default Schema: PUBLIC)4: Product Name:5: Reference: ’MySQL’6: Target: ’H2’7: Product Version:8: Reference: ’5.6.24’9: Target: ’1.3.174 (2013-10-19)’10: Missing Catalog(s): NONE11: Unexpected Catalog(s): NONE12: Changed Catalog(s):13: public14: name changed from ’public’ to ’LIQUIBASEDEMO’15: Missing Column(s):16: user_rights.description17: user_rights_new.description18: user.email19: ...20: Unexpected Column(s): NONE21: ...

DiffChangeLog mode

1: sh liquibase \2: --classpath="h2-1.3.174.jar:mysql-connector-java-5.1.36.jar" \3: --driver=org.h2.Driver \4: --url="jdbc:h2:~/liquibasedemo;AUTO_SERVER=TRUE" \5: --username=liquibasedemo \6: --password=liquibasedemo \7: diffChangeLog \8: --referenceUrl=jdbc:mysql://localhost:3306/public \9: --referenceUsername=liqui \10: --referencePassword=liqui

działa w linii komend i ant’cie

1: <?xml version="1.0" encoding="UTF-8" standalone="no"?>2: <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">3: <changeSet author="staszek (generated)" id="1442839118428-1">4: <createTable tableName="user">5: <column autoIncrement="true" name="id" type="INT">6: <constraints primaryKey="true" primaryKeyName="PRIMARY"/>7: </column>8: <column name="email" type="VARCHAR(255)">9: <constraints nullable="false"/>10: </column>11: <column name="password" type="VARCHAR(255)">12: <constraints nullable="false"/>13: </column>14: </createTable>15: </changeSet>16: <changeSet author="staszek (generated)" id="1442839118428-2">17: <createTable tableName="user_rights">18: <column autoIncrement="true" name="id" type="INT">19: <constraints primaryKey="true" primaryKeyName="PRIMARY"/>20: </column>21: <column name="user_id" type="INT">22: ...

DBDoc - generowanie dokumentacji w stylu JavaDoc

Działa tylko z poziomu command line

1: sh liquibase \2: --classpath="h2-1.3.174.jar" \3: --driver="org.h2.Driver" \4: --url="jdbc:h2:~/liquibasedemo;AUTO_SERVER=TRUE" \5: --username=liquibasedemo \6: --password=liquibasedemo \7: --changeLogFile=db.changelog.xml \8: dbDoc ./dbdoc

Trochę niedopracowane narzędzie

DBDoc - generowanie dokumentacji w stylu JavaDoc

Blokowanie wykonywania zmian

Blokowanie wykonywania zmian, jak sobie z tym radzić

1: INFO 21.09.15 16:23: liquibase: Waiting for changelog lock....2: INFO 21.09.15 16:24: liquibase: Waiting for changelog lock....3: ...4: Caused by: liquibase.exception.LockException: Could not acquire change log lock.Currently locked by fe80:0:0:0:1234:5678:9abc:def0%awdl0 (fe80:0:0:0:1234:5678:9abc:def0%awdl0) since 21.09.15 16:14

5: at liquibase.lockservice.StandardLockService.waitForLock(StandardLockService.java:175)6: at liquibase.Liquibase.update(Liquibase.java:192)

1: select * from DATABASECHANGELOGLOCK

1: mvn liquibase:releaseLocks

Agenda

3 Od czego zaczać?

5 Migracja danych

7 Co jeszcze?

8 Podsumowanie

Dobre praktyki korzystania z Liquibase

Organizacja Changelog’ów w wiele plików (plik / release)

Zarządzanie wszystkim co się da przez Liquibase

Jedna zmiana na jeden ChangeSet

Konwencja nazewnicza dla ID w ChangeSet’ach

Komentarze w ChangeSet’ach

Myśl od rolback’u

Unikaj rollback’u (bądź fast-forward)

http://www.liquibase.org/bestpractices.html

Liquibase - podstawowe właściwości

Raz definiujemy wygląd naszej bazy

Uruchamiamy na wielu wspieranych bazach danych

Mamy dokumentację zmian

Możemy generować różnice (diff’y)

Generuje SQL do review dla DBA

Wykonuje migrację od punktu w jakim znajduje się bazadanych

Współpracuje z: Ant, Maven, Spring, Command Line, Servlet,JEE CDI, Spring Boot

Inne dobre praktyki korzystania z relacyjnych baz danych

Każdy developer może mieć instancję bazy na swojej maszynie

Automatyczne testy integracyjne na bazie docelowej(specyficzne błędy dla danej bazy)

Automatyczne testy na wszystkich wspieranych bazach

Mechanizm czyszczenia bazy na potrzeby testów (backup,dbUnit, inne)

Tworzenie danych do automatycznych testów

Zautomatyzowane przenoszenie danych z produkcji (nażądanie)

Test produkcyjnego deploymentu

A co gdy trzeba nam czegoś więcej?

Datical DB www.datical.com/product/

Wsparcie dla funkcji, procedur składowanych, pakietów,synonimów, triggerów, walidacji więzów integralności,walidacji zależnych obiektów, zarządzanie prawamiużytkowników, przestrzenie tabel, partycje, duże voluminy

Integracja z CVS, SVN i Git

Build tools (Jenkins, Bamboo, Puppet,...)

Preview Schema Changes

Corporate Standards Enforcement

Organizational Collaboration

Audit and Compliance Support

Easy of Use (GUI-dashboard)

www.datical.com/liquibase/

Liquibase vs Flyway

Minusy Liquibase’a:

Wolniejszy niż flyway (parsowanie XML’a, budowanie SQL’a)

Plusy Flyway’a:

Szybszy

Czysty SQL

Minusy Flyway’a:

Słabe wsparcie dla wielu różnych baz (osobne zmiany dlakażdej bazy)

Brak wsparcia dla wracania do starej wersji

Liquibase vs Flyway

Plusy Flyway’a:

Szybszy

Czysty SQL

Minusy Flyway’a:

Liquibase vs Flyway

Plusy Flyway’a:

Szybszy

Czysty SQL

Minusy Flyway’a:

Liquibase vs Flyway

Plusy Flyway’a:

Szybszy

Czysty SQL

Minusy Flyway’a:

Liquibase vs Flyway

Plusy Flyway’a:

Szybszy

Czysty SQL

Minusy Flyway’a:

Liquibase vs Flyway

Plusy Flyway’a:

Szybszy

Czysty SQL

Minusy Flyway’a:

Liquibase vs Flyway

Plusy Flyway’a:

Szybszy

Czysty SQL

Minusy Flyway’a:

Liquibase vs Flyway

Plusy Flyway’a:

Szybszy

Czysty SQL

Minusy Flyway’a:

Pytania

Więcej informacji

Marcin Stachniuk Blog: mstachniuk.blogspot.com

Liquibase: http://www.liquibase.org/

Feature Comparison (Flyway): http://flywaydb.org/#features

Liquibase best practices:http://www.liquibase.org/bestpractices.html

Examples: https://github.com/mstachniuk/movies-database

Zarządzanie zmianami w relacyjnych bazachdanych

Marcin Stachniuk

Dziękuję!30 listopada 2015

Zarządzanie zamianami w relacyjnych bazach danych

Technology

Transcript of Zarządzanie zamianami w relacyjnych bazach danych