Migracje bazy danych w pracy projektowej na kilku przykładach

W pracy wielu deweloperów często pojawia się potrzeba migracji bazy danych. W tym artykule skupimy się na przykładach migracji relacyjnej bazy danych korzystającej z silnika Oracle, który jest obecnie jednym z najpopularniejszych systemów zarządzania relacyjnymi bazami danych.

Przykłady migracji baz danych:

1. Tworzenie nowej tabeli

W Oracle, podobnie jak w innych RDBMS, indeksy są automatycznie tworzone na kluczach głównych i unikalnych ograniczeniach. Jednak Oracle nie tworzy indeksów na kluczach obcych, dlatego dodanie indeksu na FK (kluczu obcym) może być konieczne, aby uniknąć problemów z wydajnością. Przykładowo:

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)
);

ALTER TABLE machine_translation ADD CONSTRAINT pk_machine_translation PRIMARY KEY (id);
ALTER TABLE machine_translation ADD CONSTRAINT fk_machine_translation_cc FOREIGN KEY (context) REFERENCES context (id);
CREATE INDEX i_fk_machinetranslation_cc ON machine_translation(context);
CREATE SEQUENCE sq_machine_translation START WITH 10000 INCREMENT BY 1 NOCACHE;

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

Zmiana relacji one-to-many na many-to-many często wiąże się z aktualizacją modelu biznesowego i koniecznością migracji istniejących danych. Przykład:

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);

3. Wyodrębnienie BLOB’a do nowej tabeli i implementacja copy-on-write

Implementacja copy-on-write dla BLOB’ów pozwala na efektywne zarządzanie dużymi plikami i minimalizuje redundancję danych. Przykład:

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;

ALTER TABLE picture_description
    ADD picture_object_id NUMBER(18);

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;

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;

Podsumowanie

Migracje bazodanowe są nieodłącznym elementem pracy nad aplikacjami typu CRUD. Umiejętność pisania kwerend SQL oraz znajomość specyfiki dialektów SQL jest kluczowa, aby unikać błędów i efektywnie migrować zarówno schemat, jak i dane. Przed przystąpieniem do migracji warto zastanowić się nad optymalnym rozwiązaniem, uwzględniając cel biznesowy i sposób działania aplikacji.