SQL

Tworzenie skryptów automatyzujących proces struktury bazy (migracje)

Proste i zaawansowane skrypty, migracje, rozwój bazy — INF.03

Teoria Automatyzacja budowy i migracji struktury bazy

Migracje i skrypty automatyzujące pozwalają „programować” bazę danych tak jak kod aplikacji: w wersjach, krok po kroku, z możliwością cofnięcia zmian.
Skrypt batch (CREATE TABLE, INSERT, GRANT) – zawiera serię poleceń tworzących całą bazę od zera.
Migracja schematu – opis pojedynczej zmiany w strukturze bazy (nowa kolumna, tabela, indeks) w osobnym pliku.
Kilkanaście plików migracji tworzy historię rozwoju bazy – jak commit-y w systemie kontroli wersji.
Dzięki migracjom łatwo wdrażać kolejne wersje systemu w różnych środowiskach (uczeń, pracownia, serwer produkcyjny).
Dobra migracja składa się z etapów: przygotowanie danych, zmiana struktury, weryfikacja wyniku.
W większych projektach używa się tabeli wersji (np. schema_versions), która przechowuje listę zastosowanych migracji.
Narzędzia typu Flyway lub Liquibase pilnują kolejności migracji i zapisują, co już zostało wykonane.

W INF.03 ważne jest, aby umieć zbudować bazę z jednego skryptu, a potem rozwijać ją kolejnymi migracjami, zamiast „klikać” zmiany ręcznie w narzędziu.

Ćwiczenie 1 Prosty skrypt automatyczny — baza „szkola”

Zadanie: Napisz plik buduj_uczniowie.sql, który:

usuwa starą bazę,
tworzy bazę szkola i tabelę uczniowie,
wstawia kilka przykładowych rekordów,
tworzy użytkownika z prawem tylko do odczytu.

DROP DATABASE IF EXISTS szkola;
CREATE DATABASE szkola;
USE szkola;

CREATE TABLE uczniowie (
  id INT PRIMARY KEY AUTO_INCREMENT,
  imie VARCHAR(30),
  nazwisko VARCHAR(40)
);

INSERT INTO uczniowie (imie, nazwisko) VALUES
('Kamil', 'Radek'),
('Ola', 'Kowal');

CREATE USER 'praktykant'@'localhost' IDENTIFIED BY 'Uczniowie2025!';
GRANT SELECT ON szkola.* TO 'praktykant'@'localhost';
FLUSH PRIVILEGES;

Ćwiczenie 2 Migracja — zmiana struktury tabeli

Zadanie: Stwórz plik migruj_klasy.sql, który:

dodaje kolumnę profil do tabeli klasy,
tworzy indeks ułatwiający wyszukiwanie po profilu.

ALTER TABLE klasy ADD profil VARCHAR(40);
CREATE INDEX idx_klasa_profil ON klasy (profil);

Ćwiczenie 3 Zaawansowany batch — budowa, migracje i testy

Zadanie: Napisz skrypt, który:

tworzy bazę migracje_test i tabele klasy, uczniowie,
wstawia przykładowe dane,
w drugiej części modyfikuje typy, dodaje klucze i sprawdza spójność.

-- Część 1: budowa bazy
DROP DATABASE IF EXISTS migracje_test;
CREATE DATABASE migracje_test;
USE migracje_test;

CREATE TABLE klasy (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nazwa VARCHAR(12)
);

CREATE TABLE uczniowie (
  id INT PRIMARY KEY AUTO_INCREMENT,
  imie VARCHAR(30),
  nazwisko VARCHAR(40),
  klasa_id INT
);

INSERT INTO klasy (nazwa) VALUES ('1A'), ('2A');

INSERT INTO uczniowie (imie, nazwisko, klasa_id) VALUES
('Adam','Lis',1),
('Ewa','Mik',2);

-- Część 2: migracje (utwardzanie schematu)
ALTER TABLE uczniowie ADD pesel CHAR(11) UNIQUE;
ALTER TABLE klasy MODIFY nazwa CHAR(12) NOT NULL;
ALTER TABLE uczniowie
  ADD CONSTRAINT fk_klasa FOREIGN KEY (klasa_id) REFERENCES klasy(id);

Ćwiczenie 4 Migracja z rollbackiem — wersjonowanie

Zadanie: Przygotuj dwie migawki zmian:

skrypt migracji dodający nową kolumnę,
skrypt rollback usuwający tę kolumnę.

-- Migracja V002: dodanie kolumny adres_email
ALTER TABLE uczniowie ADD adres_email VARCHAR(180);

-- Rollback V002: usunięcie kolumny adres_email
ALTER TABLE uczniowie DROP COLUMN adres_email;

Ćwiczenie 5 Refaktoryzacja tabeli z zachowaniem danych

Sytuacja: Masz tabelę adresy_uczniow z kolumną miasto_kod (np. „Kraków 30-001”). Chcesz rozbić ją na dwie kolumny: miasto i kod_pocztowy. Zadanie: Zaproponuj migrację w dwóch krokach:

dodanie nowych kolumn, przepisanie danych,
usunięcie starej kolumny po weryfikacji.

-- Krok 1: dodanie nowych kolumn
ALTER TABLE adresy_uczniow
  ADD miasto VARCHAR(50),
  ADD kod_pocztowy CHAR(6);

-- Krok 2: przepisanie danych (przykład: miasto i kod oddzielone spacją)
UPDATE adresy_uczniow
SET miasto = SUBSTRING_INDEX(miasto_kod, ' ', 1),
    kod_pocztowy = SUBSTRING_INDEX(miasto_kod, ' ', -1);

-- Po sprawdzeniu danych można usunąć starą kolumnę
ALTER TABLE adresy_uczniow DROP COLUMN miasto_kod;

Zadanie INF.03 Wersjonowanie schematu „Biblioteka”

Opis: Masz bazę biblioteka z tabelami: ksiazki(id, tytul, autor), czytelnicy(id, imie, nazwisko), wypozyczenia(id, ksiazka_id, czytelnik_id, data_wyp). Projekt się rozwija – trzeba:

dodać datę zwrotu do wypożyczeń,
dodać pole „rok_wydania” do książek,
zapamiętywać adres e‑mail czytelnika.

Przygotuj migrację V001 z dodaniem nowych kolumn.
Napisz osobny plik rollback V001, który cofnie te zmiany.
Zaproponuj prostą tabelę schema_versions do śledzenia migracji.

-- Migracja V001: dodanie nowych kolumn
USE biblioteka;

ALTER TABLE wypozyczenia
  ADD data_zwrotu DATE;

ALTER TABLE ksiazki
  ADD rok_wydania SMALLINT;

ALTER TABLE czytelnicy
  ADD email VARCHAR(80);

-- Tabela do śledzenia migracji
CREATE TABLE IF NOT EXISTS schema_versions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  version VARCHAR(20) NOT NULL,
  opis VARCHAR(255),
  applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO schema_versions (version, opis)
VALUES ('V001', 'Dodanie daty zwrotu, roku wydania i e-mail czytelnika');

-- Rollback V001 (osobny plik):
ALTER TABLE wypozyczenia DROP COLUMN data_zwrotu;
ALTER TABLE ksiazki DROP COLUMN rok_wydania;
ALTER TABLE czytelnicy DROP COLUMN email;

DELETE FROM schema_versions WHERE version = 'V001';

Quiz Sprawdź wiedzę z migracji i automatyzacji!

Dlaczego warto pisać skrypty do budowy i migracji bazy zamiast klikać zmiany ręcznie?
Co to jest migracja schematu i co zwykle zawiera?
Na czym polega rollback migracji?
Po co tworzyć tabelę z wersjami schematu?
Jak zadbać o przenośność skryptów migracyjnych między różnymi komputerami?

Ponieważ skrypty można uruchomić wiele razy, w różnych środowiskach, zapewniając powtarzalny efekt i łatwe odtworzenie bazy.
To plik/zbiór poleceń opisujący jedną zmianę w schemacie, np. ALTER TABLE, CREATE/DROP INDEX, dodanie kolumn.
Rollback wykonuje odwrotne operacje niż migracja, przywracając poprzedni stan struktury bazy.
Aby wiedzieć, które migracje zostały już wykonane, w jakiej kolejności i kiedy, oraz uniknąć powtórnego ich uruchamiania.
Unikać ścieżek specyficznych dla jednego komputera, korzystać z ustawień serwera i pisać SQL zgodny ze stosowaną bazą (np. MySQL).

Checklista Sprawdź umiejętności

Tworzę skrypt batch budujący całą bazę z tabelami i przykładowymi danymi.
Piszę migracje zmieniające strukturę istniejącej bazy (dodanie kolumn, indeksów, kluczy obcych).
Projektuję migracje z możliwością rollbacku, aby móc cofnąć błędne zmiany.
Potrafię zorganizować migracje w wersje i śledzić je w tabeli schematu.