fbpx

Migracje bazy danych w pracy projektowej na kilku przykładach

Migracje bazy danych w pracy projektowej na kilku przykładach 

Autor: Piotr Kubicki

W pracy projektowej zdecydowanej większości deweloperów występuje potrzeba migracji bazy danych. W tym artykule skupimy się na kilku przykładach migracji relacyjnej bazy danych na silniku Oracle, który jest obecnie najbardziej popularnym systemem zarządzania relacyjnymi bazami danych, jak pokazano na rysunku poniżej. 

Fot. Ranking popularności relacyjnych baz danych na sierpień 2020. Źródło: https://db-engines.com/en/ranking/relational+dbms

Zwykle w projektach do migracji używa się dedykowanych framework’ów, takich jak np. popularny Flyway. Flyway używa zaś frameworku Ant do wykonania migracji na bazie danych. Konfiguracja Flyway’a składa się ogólnie z dwóch kroków: 

  1. W pliku mavenowym pom.xml odpowiednim dla pakietu związanego z warstwą persystencji należy dołożyć zależność do Flyway’a oraz do pluginu Anta skonfigurowanego pod konkretny sterownik konkretnej bazy danych, por. przykład niżej: 
<! -- to compile java migrations --> 
<dependencies> 
    <dependency> 
        <groupId>org.flywaydb</groupId> 
        <artifactId>flyway-core</artifactId> 
    </dependency> 
</dependencies> 
 
<build> 
    <plugins> 
        <! -- we call ant to execute the db-deployment with scripts and flyway --> 
        <plugin> 
            <groupId>org.apache.maven.plugins</groupId> 
            <artifactId>maven-antrun-plugin</artifactId> 
            <version>1.7</version> 
            <configuration> 
                <target> 
                    <fail unless="dbd-command" message="no dbd command given via (dbd-command)"/> 
                    <property name="maven_compile_classpath" refid="maven.compile.classpath"/> 
                    <ant antfile="${basedir}/db-deploy.ant.xml" target="dbd-${dbd-command}"> 
                    </ant> 
                </target> 
            </configuration> 
            <dependencies> 
                <dependency> 
                    <groupId>org.flywaydb</groupId> 
                    <artifactId>flyway-ant</artifactId> 
                    <version>${version.flyway}</version> 
                </dependency> 
                <dependency> 
                    <groupId>com.oracle</groupId> 
                    <artifactId>ojdbc6</artifactId> 
                    <version>11.2.0.4.0</version> 
                </dependency> 
            </dependencies> 
        </plugin> 
    </plugins> 
</build> 
  1. Należy utworzyć i skonfigurować plik Ant’a (antfile), w którym znajdą się wszystkie informacje potrzebne do nawiązania połączenia z bazą danych i komendy migracji. 

Migracje bazy danych sprowadzają się wtedy do napisania skryptu migracyjnego SQL z odpowiednią, podwyższoną (niekoniecznie o jeden) wersją, który umieszcza się w odpowiednim katalogu w zależności od tego, czy ma być wykonany automatycznie przez framework, czy manualnie podczas deployment’u na środowisku testowym lub integracyjnym. Drzewo projektu dla interesującego nas pakietu wygląda wtedy następująco: 

W przypadku średnich i dużych projektów rozwojowych, a zwłaszcza aplikacji typu CRUD, potrzeba migracji bazy danych występuje praktycznie co sprint (mam tu na myśli pracę w metodyce Agile/Scrum). Pracując w obecnym projekcie mamy średnio kilka nowych skryptów migracyjnych w każdym sprincie i to napisanych przez różne osoby, dla różnych stories, praktycznie w ostatecznym release nigdy niebędące fixami poprzednich. Dość często są to zmiany nieduże, dotyczące nowych atrybutów, zmiany wymagalności (nullable lub not null), zmiany dopuszczalnej długości atrybutów itp. Niewiele rzadziej są to kompletnie nowe tabele, często mapujące, tworzące nowe połączenia biznesowe z często już istniejącymi tabelami. W dłużej istniejących projektach nierzadko zdarza się jednak potrzeba zmiany schematu bazy danych połączona z migracją danych. W tym artykule przyjrzę się różnym przykładowym migracjom bazy danych. 

Przykłady migracji bazodanowych 

1.Tworzenie nowej tabeli 

W Oracle, jak we wszystkich RDBS-ach, indeks bazodanowy1 tworzony jest na kluczu głównym (primary key, PK) oraz więzach unikalności (unique constraints) automatycznie. Natomiast w przeciwieństwie do wielu, Oracle nie tworzy indeksów na kluczach obcych (foreign key, FK), stąd poniżej i_fk_machinetranslation_cc. W większości przypadków chcemy utworzyć indeks na FK, bo ilekroć aplikacja musi mieć możliwość usunięcia wiersza w tabeli nadrzędnej lub zaktualizowania wartości PK, silnik bazodanowy będzie „cierpiał”, jeśli nie istnieje żaden indeks, ponieważ będzie musiał zablokować całą tabelę podrzędną. Przypadkiem, w którym zwykle nie dodamy indeksu na FK, jest sytuacja, w której FK znajduje się w tabeli „danych statycznych”, która definiuje dziedzinę kolumny (np. tabela kodów statusu), gdzie aktualizacje i usuwanie w tabeli nadrzędnej nigdy nie są wykonywane bezpośrednio przez aplikację. Jeśli jednak dodanie indeksu do kolumny daje korzyści dla ważnych zapytań w aplikacji, to indeks nadal będzie dobrym pomysłem. Zwykle przyjmuje się, że tworzenie osobnych sekwencji na każdej z tabel jest wskazane, choć możliwe jest też używanie jednej i tej samej sekwencji przez wiele różnych tabel. Niezależnie od tego warto pamiętać, że sekwencje to najbardziej efektywny wybór w kwestii optymalizacji zawołań na generowanie syntetycznych identyfikatorów wierszy, o czym więcej w tym artykule: https://vladmihalcea.com/why-you-should-never-use-the-table-identifier-generator-with-jpa-and-hibernate/.  Określenie NOCACHE wskazuje, że wartości sekwencji nie są wstępnie przydzielone. ID o najmniejszej wartości będzie wynosić 10000. 

1 Indeks bazy danych to struktura danych, która poprawia szybkość operacji pobierania danych w tabeli bazy danych kosztem dodatkowych zapisów i przestrzeni dyskowej w celu utrzymania struktury danych indeksu. Indeksy służą do szybkiego lokalizowania danych bez konieczności przeszukiwania każdego wiersza w tabeli bazy danych za każdym razem, gdy uzyskiwany jest dostęp do tabeli bazy danych. 

CREATE TABLE machine_translation ( 
    id NUMBER(18) NOT NULL, 
    context NUMBER(18) NOT NULL, 
    webservice_id VARCHAR2(256 CHAR) NOT NULL, 
    field VARCHAR2(100 CHAR) NOT NULL, 
    source_language VARCHAR2(10 CHAR) NOT NULL, 
    target_language VARCHAR2(10 CHAR) NOT NULL, 
    translation_start TIMESTAMP(6) NOT NULL, 
    translation_end TIMESTAMP(6), 
    error_status VARCHAR2(200 CHAR) 
); 
 
-- primary key 
ALTER TABLE machine_translation ADD CONSTRAINT pk_machine_translation PRIMARY KEY (id); 
 
-- foreign key 
ALTER TABLE machine_translation ADD CONSTRAINT fk_machine_translation_cc FOREIGN KEY (context) REFERENCES context (id); 
 
-- index 
CREATE INDEX i_fk_machinetranslation_cc ON machine_translation(context); 
 
-- sequence 
CREATE SEQUENCE sq_machine_translation START WITH 10000 INCREMENT BY 1 NOCACHE; 

2. Zamiana relacji z one-to-many na many-to-many z migracją danych 

Zmiana rodzaju relacji wynika z aktualizacji modelu biznesowego i w projektach w początkowej fazie rozwoju zdarza się zwykle dość często. Najczęściej ma miejsce już po upływie pewnego czasu, co niesie ze sobą konieczność migracji nie tylko schematu, ale też istniejących danych. Poniżej przykład takiej relacji. 

Na początku mieliśmy relację jeden-do-wielu, w tabeli CONSTRUCTION_MEASURE był klucz obcy FK_CONSTR_MEASURE_CHACON, który używając kolumny ID_CONTEXT wskazywał na tabelę CONTEXT (jeden CONTEXT mógł mieć wiele CONSTRUCTION_MEASURE). 

Po pewnym czasie uznano, że CONSTRUCTION_MEASURE jest w zasadzie obiektem współdzielonym dla wielu CONTEXT ’ów, i że oczywiście nie ma sensu w takich sytuacjach duplikować wierszy dotyczących biznesowo tego samego obiektu w tabeli CONSTRUCTION_MEASURE, w związku z tym trzeba zmienić tę relację na wiele-do-wielu (wtedy współdzielony CONSTRUCTION_MEASURE może się łączyć z wieloma CONTEXT ‘ami). W tym celu w skrypcie migracyjnym tworzymy nową tabelę – tzw. mapującą – która zastępuje dotychczasowe połączenie. Każdy wpis w tej tabeli wskazuje na jeden wpis w tabeli CONTEXT i jeden wpis w tabeli CONSTRUCTION_MEASURE.  

CREATE TABLE context_to_cm ( 
    id                        NUMBER(18) NOT NULL, 
    id_context         NUMBER(18) NOT NULL, 
    id_construction_measure   NUMBER(18) NOT NULL, 
    is_deleted                NUMBER(1) NOT NULL 
); 
ALTER TABLE context_to_cm ADD ( CONSTRAINT pk_cctocm PRIMARY KEY (id)); 
 
CREATE SEQUENCE sq_cctocm START WITH 10000 INCREMENT BY 1 NOCACHE NOCYCLE; 
 
INSERT INTO context_to_cm (id, id_context, id_construction_measure, is_deleted) 
SELECT sq_cctocm.nextval, cm.id_context, cm.id, 0 FROM t_tais_construction_measure cm; 
 
ALTER TABLE t_tais_construction_measure DROP COLUMN id_context; 
 

ALTER TABLE context_to_cm ADD CONSTRAINT uc_for_pair_ccid_cmid UNIQUE (id_context, id_construction_measure); 

Należy pamiętać o tym, by zapewnić unikalność takich połączeń (tzn. by nie istniała możliwość stworzenia więcej niż jednego wpisu w tabeli mapującej odwzorowujących to samo połączenie). W tym celu tworzymy więzy unikalności (por. uc_for_pair_ccid_cmid w skrypcie). Model połączenia po migracji wygląda następująco (tabele CONTEXT i CONSTRUCTION_MEASURE uproszczono): 

3. Wyodrębnienie BLOB’a do nowej tabeli i implementacja koncepcji copy-on-write dla obrazka z opisem przy wersjonowaniu informacji 

W niektórych aplikacjach implementuje się wersjonowanie informacji. Polega to na udzielaniu możliwości użytkownikowi, aby mógł on tworzyć kopie (rewizje) obecnego stanu informacji, by wszelkie zmiany jakich potem dokona wykonały się na kopii, a nie na pierwotnej, tym samym zwersjonowanej rewizji. Umożliwia to wgląd w historię zmian oraz ewentualne korekty do poprzednich wartości. Najprostszą koncepcją implementacji wersjonowania jest duplikowanie wszystkich relewantnych informacji, zmieniają się jedynie: klucz syntetyczny (ID, bo tworzymy nowe wiersze w tabelach) i przechowywana zwykle w głównym węźle tak utworzonego drzewa wpisów informacja o numerze rewizji. Ideę przedstawiam na obrazku poniżej: 

Jeśli operujemy na prostych danych (słowa i liczby) i wersjonowanie nie odbywa się bardzo często (lub nowe rewizji niosą w zdecydowanej większości nowe informacje), taka koncepcja jest do zaakceptowania. Problemem staje się już sytuacja, w której mamy do czynienia z typem BLOB, który używamy w bazach danych do przechowywania plików. Ze względów zajętości miejsca w pamięci, takie gorliwe duplikowanie jest w oczywisty sposób nieakceptowalne. W takiej sytuacji chcemy przynajmniej dla samego BLOB’a, wydzielonego w osobnej tabeli, zaimplementować koncepcję copy-on-write, która stworzy nowy wpis w tabeli tylko wtedy, gdy będziemy chcieli obrazek zaktualizować (w bieżącej rewizji nadpisać, w następnej utworzyć nowy). Nowy wpis z BLOB-em ma się utworzyć dopiero wtedy, gdy chcemy obrazek nadpisać. Jeśli robimy to dla starego opisu, to po prostu kasujemy stary nigdzie nie używany obrazek. Ciekawym przypadkiem jest sytuacja, w której mamy stary, zwersjonowany opis (którego nie można już usunąć, bo służy do oglądania historii zmian), a do niego podpięty jest obrazek, na który wskazuje również nowy opis. I teraz dla tego nowego opisu chcemy nadpisać obrazek. Skąd się dowiedzieć, czy taki obrazek można nadpisać, czy też jest on w zwersjonowanej relacji i trzeba utworzyć nowy? Wystarczy mieć podwójną relację, w której obrazek wskazuje też na opis. W tej sytuacji, jeśli obrazek będzie wskazywał na stary opis, z którym został utworzony, to mając nowy opis i chcąc podjąć decyzję możemy sprawdzić, że ma on inne ID niż to, na które wskazuje obrazek, więc nie możemy obrazka nadpisać, lecz musimy utworzyć nowy. Między innymi dla takich przypadków relacji many-to-one mamy podwójną relację, gdzie obie encje wskazują na siebie nawzajem. Zarówno tu, jak i w relacjach 1:1 rezygnujemy z tworzenia sekwencji w tabeli pochodnej, ponieważ jej kluczem głównym / ID będzie dokładnie klucz główny tabeli głównej. Schemat poniżej: 

Stan przed migracją: 

Migracja: 

- create blob table 
CREATE TABLE picture_object 
( 
    parent_picture_desc_id NUMBER(18) NOT NULL PRIMARY KEY, 
    picture_object          BLOB      NOT NULL 
); 
ALTER TABLE picture_object 
    ADD CONSTRAINT fk_picture_blob 
        FOREIGN KEY (parent_picture_desc_id) REFERENCES picture_description (picture_id) DEFERRABLE INITIALLY DEFERRED; 
 
-- add new column into investigation model picture 
ALTER TABLE picture_description 
    ADD picture_object_id NUMBER(18); 
 
-- migrate data 
INSERT INTO picture_object (parent_picture_desc_id, picture_object) 
SELECT pd.picture_id, pd.picture_object 
FROM picture_description pd; 
 
UPDATE picture_description pd 
SET pd.picture_object_id = pd.picture_id; 
 
-- adjust picture_description after data migration and set foreign key on column picture_object_id 
ALTER TABLE picture_description 
    ADD CONSTRAINT fk_pic_desc_pic_object 
        FOREIGN KEY (picture_object_id) REFERENCES picture_object (parent_picture_desc_id) DEFERRABLE INITIALLY DEFERRED; 
ALTER TABLE picture_description 
    DROP ( picture_object ); 
ALTER TABLE picture_description 
    MODIFY picture_object_id NUMBER(18) NOT NULL; 

Stan po migracji: 

Warto zwrócić uwagę na fakt, że klucze obce obu tabel wskazujących na siebie są DEFERRABLE INITIALLY DEFERRED. Oznacza to, że dopóki nie zostanie wykonany Commit na transakcji, oba mogą być nullem. Umożliwia to stworzenie obu obiektów i następnie po kolei dodania jednego do pierwszego i pierwszego do drugiego bez wystąpienia błędu (bo walidacja spójności jest odroczona). 

Podsumowanie 

Migracje bazodanowe w projektach aplikacji typu CRUD są nieoderwalnym elementem pracy programisty, stąd niezbędna jest umiejętność tworzenia kwerend w języku SQL, a znajomość niuansów konkretnego dialektu SQL umożliwia ustrzec się przed błędami. Migrując istniejący schemat trzeba zmigrować także dane. Przed migracją warto się zastanowić, czy uwzględniając sposób, w jaki ma działać aplikacja, a przede wszystkim cel biznesowy, nie da się zaproponować lepszego schematu w migracji. 

Migracje bazy danych w pracy projektowej na kilku przykładach
Przewiń do góry