Kiedyś… dawno, dawno temu czasy były inne. Nie wie czy lepsze, ale inne na pewno, ludziom się chciało. Pisali nawet na łamach 7thGuarda różne ciekawe artykuły http://7thguard.net/news.php?id=2567. Niektóre opatrzone nawet grafiką http://7thguard.net/news.php?id=3.
Po pewnej awanturze o obrazek http://7thguard.net/comments.php?sect=show_hdr&news_id=2664 zacząłem inaczej spoglądać na mój ulubiony serwis, (stwierdziłem, że serwis schodzi na psy… no ale jakoś się przełamałem skoro piszę te słowa). Dawno temu oferty hostingowe też były inne – generalnie proste, a w zasadzie jedyną bazą danych oferowaną przez droższych providerów internetowych był system MySqla.
Może zresztą 'czasy’ nie zmieniły się znowu nie aż tak bardzo – i dziś ciężko o PostgreSQLa, naprawdę nieliczni oferują hostowanie servletów, a może ze dwóch providerów w Polsce hostuje technologie EJB. Weź to pod uwagę jeśli piszesz serwis internetowy i będziesz chciał wynająć kawałek dysku u jakiejś firmy hostingowej (ja nie wziąłem i mam problem).
Dużą popularnością cieszy się MySql. Kiedyś ktoś próbował mi wyjaśnić, że bierze się to z popularności phpMyAdmin, ale jakoś nie za bardzo przemawia do mnie ten argument, w końcu dla postgresqla jest phpPgAdmin. Przy realnym obciążeniu wydajność systemu mysqla spada lawinowo. Brak podzapytań może dać się naprawdę we znaki. Jeśli deklarujesz pole jako VARCHAR(100) i wpisujesz do niej 'nic’ to nie licz na to, że w systemie mysqla zajmie to 4 bajty (1 (długość)+3 (dane)). Stąd między innymi bierze się jego 'szybkość’ działania przy małych obciążeniach – rekordy mają taką samą szerokość. Miałem kiedyś przyjemność przenosić bazę danych, najpierw do mysql-a: 13 GB, po przeniesieniu jej do postgresqla: 3.5 GB (może niezbyt dobrze zgadywałem długości łańcuchów w poszczególnych polach?). Ostatnią najważniejszą chyba sprawą jest brak integralności danych w mysqlu – powiedzmy brak wymuszenia tej integralności na poziomie systemu baz danych. Nie ma to może dużego znaczenia dla niewielkich systemów, ale w większych może być prawdziwym problemem powodującym prawdziwe katastrofy. Oczywiście tych wszystkich wad nie posiada postgresql. Nie o tym jednak chciałem dziś pisać, a o tym co powoduje, że postgresql jest systemem klasy 'opel z szyber dachem’ przy mysqlu którego porównałbym do 'fiata bez klimatyzacji’ (też czujecie ten cień świętej wojny w powietrzu? (osobiście preferuje wojny dla GNOME i KDE)). Oczywiście są też inne systemy baz danych, możesz je umieszczać w mojej skali motoryzacyjnej (jeśli ktoś lubi porównania w branży informatycznej do przemysłu motoryzacyjnego). Oczywiście postgresql ma też swoje wady – np. pusta baza danych zajmuje około 4 MB. Co przy płaceniu za każdy MB zajmowany przez bazę danych może być sporym wydatkiem.
Po tym może nieco przydługim wstępie zaczynamy: przywracanie utraconych danych w PostgreSQLu.
Od czego by tu zacząć… może na początek utwórzmy bazę danych.
Po połączeniu się z serwerem postgresqla wydajemy zapytanie:
template1=# CREATE DATABASE wyzwalanie_postgresa;
można to zrobić również z programu powłoki systemowej, wszystkie programy powłoki dla postgresa powinieneś uruchamiać z flagą -U i nazwą użytkownika postgrea.
$ createdb wyzwalanie_postgresa -U super_user
I tak staliśmy się szczęśliwymi posiadaczami bazy danych wyzwalanie_postgresa.
By zrealizować ambitne zadanie przywracania utraconych danych musimy użyć jednego z języków oferowanego przez postgresqla. Mój wybór padł na PL/PgSQL. Język ten jest podobny do PL/SQL oferowanego przez system oracle, więc możesz upiec półtorej pieczeni przy jednym ogniu.
Musimy jednak dołączyć ten język do naszej bazy danych, robimy to z powłoki systemowej za pomocą polecenia createlang:
$ createlang -U mentat plpgsql wyzwalanie_postgresa
i w tym miejscu czeka Cie niespodzianka – zostaniesz zapytany 4x o hasło (createlang jest skryptem, wywołuje inne programy które proszą oddzielnie o hasło).
Jeśli chciałby? by wszystkie tworzone bazy mogły używać języka plpgsql to wystarczy, że dodasz plpgsql do template1. Nie sprawdzałem tego, ale tak powinno być – każda baza tworzona jest przez kopiowanie struktur template1.
Utwórzmy w naszej bazie danych tabelkę:
CREATE TABLE parent (
id INT,
lan VARCHAR(10),
PRIMARY KEY(id)
);
I tabelkę do do niej siostrzaną:
CREATE TABLE ctrlz_parent (
id INT,
lan VARCHAR(10),
ac CHAR(1) NOT NULL,
cr TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
w tabelce ctrlz_parent będziemy przechowywać te dane które utracimy w tabelce parent. Do tabelki siostrzanej dodałem dwa dodatkowe pola. Umówmy się, że pole ac określa podejmowaną akcję i niech ’u’ określa zapytania UPDATE, a ’d’ niech określa zapytania DELETE. Ostatnie pole określa czas utworzenia rekordu.
Napiszemy teraz dwie funkcje w języku PL/PgSQL, odpowiednio dla poleceń zmian i kasowania rekordów w tabelce parent. Zauważ, że nie koniecznie muszą być wykonywane bezpośrednie zapytania UPDATE czy DELETE na tabeli parent – mogą one wynikać z reakcji triggerów, (wyzwalaczy) w innej części bazy danych, na tabelkę parent. W przypadku triggerów wewnątrz funkcji mamy predefiniowanych kilka zmiennych. I tak zmienna new określa nowe dane, a zmienna old stare dane.
<
br /> Przy wstawianiu wiersza wewnątrz funkcji realizującej trigger możesz sie od
woływać do zmiennej new, przy zapytaniach zmieniających rekord do new i do old, a przy zapytaniach usuwajacych rekord do zmiennej old. Zmienna new zawsze określa nowe dane, a old dane które zostaną usunięte.
Najpierw funkcja która ma być wykonywana przy kasowaniu:
CREATE FUNCTION delete_parent()
RETURNS opaque
AS ’
BEGIN
INSERT INTO ctrlz_parent (id,lan,ac) VALUES (old.id,old.lan,”d”);
RETURN old;
END;’
LANGUAGE 'plpgsql’;
Pierwsza linijka mówi o nazwie funkcji, druga co funkcja zwraca, a sama treść funkcji znajduje się między apostrofami za AS, w ostatniej linijce znajduje się informacja o wykorzystywanym języku. Oczywiście nie musisz wszystkiego formatować tak jak ja – to tylko przykład. Funkcje plpgsql mają część w której deklarujemy zmienne (u nas nie deklarujemy zmiennych, więc jej nie ma) i część w której znajduje się ciało funkcji (między BEGIN i END). W ciele funkcji napisaliśmy zapytanie które wstawia do ctrlz_parent to co jest usuwane z parent. Następująca po niej linijka zwraca rekord (opaque) i na tym działanie funkcji się kończy. Ciekawostką jest, że wewnątrz ciała funkcji dwukrotne użycie apostrofu oznaczałoby normalnie pojedynczy apostrof. Do konkretnych pól odwołujemy się za pomocą operatora kropki. Tak więc old.lan oznacza odwołanie się do pola lan w tabeli parent (na tym etapie nie jest to jeszcze oczywiste – czytaj dalej).
Niestety (albo na szczęście) samo napisanie funkcji nie załatwia sprawy – musimy jeszcze poinformować postgresa, by ją wykonywał gdy będzie podjęta próba kasowania rekordu z tabelki parent. Operacja tego typu nosi nazwę trigger (wyzwalacz). U nas utworzenie triggera wygląda tak:
CREATE TRIGGER tg_delete_parent AFTER DELETE ON parent FOR EACH ROW EXECUTE PROCEDURE delete_parent();
gdzie tg_delete_parent to nazwa tworzonego trigera, AFTER oznacza, że triger zostanie wykonany po usunięciu wiersza, jeśli byłoby BEFORE to trigger zostałby wykonany przed usunięciem wiersza (w naszym przypadku nie ma to znaczenia, ale ogólnie może mieć), DELETE oznacza, że chodzi nam o wykonanie funkcji triggera przy kasowaniu, mogło by w tym miejscu jeszcze być INSERT i UPDATE i mogą one występować łącznie, a delete_parent() jest nazwą naszej procedury.
No to teraz coś wstawmy do parent i skasujmy.
INSERT INTO parent (id,lan) VALUES (1,’Ala’);
INSERT INTO parent (id,lan) VALUES (2,’Adam’);
SELECT * FROM parent;
id | lan
—-+——
1 | Ala
2 | Adam
DELETE FROM parent WHERE id=2;
SELECT * FROM parent;
id | lan
—-+—–
1 | Ala
SELECT * FROM ctrlz_parent ;
id | lan | ac | cr
—-+——+—-+—————————-
2 | Adam | d | 2004-04-22 19:56:53.877721
Jak widać nasza funkcja działa. Nie musimy już się bać, że w głupi sposób pozbędziemy się czegoś ważnego i możemy kasować do woli. Gwarantuję, że w przypadku posługiwania się czystym SQL-em ręce będą mniej drżały.
Teraz funkcja dla zmiany danych w parent:
CREATE FUNCTION update_parent()
RETURNS opaque
AS ’
BEGIN
IF old.id<>new.id O
R old.lan<>new.lan THEN
INSERT INTO ctrlz_parent (id,lan,ac) VALUES (old.id,old.lan,”u”);
END IF;
RETURN old;
END;’
LANGUAGE 'plpgsql’;
Od poprzedniej funkcji różni się tym, że funkcja sprawdza czy faktycznie coś się w rekordzie zmieniło (przy okazji pokazałem instrukcję warunkową w plpgsql). Czyli zapytanie UPDATE parent SET id=id; nie spowoduje wstawienia wierszy do ctrlz_parent i o to chodzi. Teraz wystarczy zdefiniować trigger dla UPDATE:
CREATE TRIGGER tg_update_parent AFTER UPDATE ON parent FOR EACH ROW EXECUTE PROCEDURE update_parent();
Sprawdzmy czy wszystko działa:
UPDATE parent SET lan =’Ewa’;
SELECT * FROM ctrlz_parent ;
id | lan | ac | cr
—-+——+—-+—————————-
2 | Adam | d | 2004-04-27 19:56:53.877721
1 | Ala | u | 2004-04-27 20:10:21.656029
Bingo!
No i to by było na tyle. No może jeszcze jedno… tabelka ctrlz_parent będzie rosła i rosła, a zmiany które chcielibyśmy przywrócić już dawno przywróciliśmy… a te których nie przywróciliśmy już na pewno nie przywrócimy. Powinniśmy więc co jakiś czas (np. raz dziennie) czyścić zawartość ctrlz_parent ze starych np. ponadtygodniowych danych. Wystarczy w tym celu wydać zapytanie:
DELETE FROM ctrlz_parent WHERE CURRENT_TIMESTAMP-cr > interval '7 days’;
Pokazałem dziś najprostszą rzecz jeśli chodzi o triggery. Cała implementacja zajęła kilka linijek, a nie musisz się już przejmować pisząc kod w php, perlu, javie czy w dowolnym innym języku, możesz łączyć się za pomocą accessa i w każdym z tych przypadków nie musisz sie przejmować że coś wykasujesz. Całą pracę wykona za Ciebie zawsze serwer baz danych wykonując backup starych danych w chwili zajścia zdarzenia zmiany czy usunięcia danych.
Integralność całego systemu postgresqla oparta jest o mechanizmy triggerów. PostgreSQL jest systemem dojrzałym, przygotowanym na przyjęcie dużego ruchu z możliwościami które dają dopiero drogie komercyjne systemy bazy danych, a których próżno szukać w innych produktach open source.
I to już naprawdę wszystko. Przynajmniej na dzisiaj. Jeśli chcesz bym napisał następny artykuł o postgresie, to zapraszam do zamieszczenia komentarza. Podlizywanie się mile widziane :-).
(c) 2004 Marcin Dębicki, Kopiowanie wyłącznie całości na lewo i prawo w celach nie komercyjnych mile widziane.
Archiwalny news dodany przez użytkownika: mentat.
Kliknij tutaj by zobaczyć archiwalne komentarze.