zapewne każdy kto choć raz robił coś ciut większego w bazach danych zetknął się z problemem przechowywania zmian w tabelkach.
ci co nie mają triggerów, muszą używać kodu po stronie aplikacji klienckiej. co ma swoje wady.
z drugiej strony – w postgresie triggery są. i działają 🙂 więc można do roboty zaprząc postgresa.
załóżmy, że mamy bardzo prostą tabelkę z danymi:
CREATE TABLE objects ( id BIGSERIAL, o_type TEXT NOT NULL DEFAULT '', PRIMARY KEY (id) );
chcemy zapisywać sobie modyfikacje. robimy więc tabelkę na zapis historii zmian:
CREATE TABLE history_objects ( id BIGSERIAL, modified_on TIMESTAMP NOT NULL DEFAULT now(), modification_type TEXT NOT NULL DEFAULT '', modified_id INT8, modified_o_type TEXT, PRIMARY KEY (id) );
rekordy w history_object zawierają:
- modified_on – kiedy dana modyfikacja miała miejsce
- modification_type – typ modyfikacji – insert, update czy delete.
- modified_id – id zmodyfikowanego rekordu
- modified_o_type – o_type w tym rekordzie
proste i miłe. tworzymy więc dwa triggery:
CREATE OR REPLACE FUNCTION trg_objects_ui() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN INSERT INTO history_objects (modified_id, modified_o_type, modification_type) VALUES (NEW.id, NEW.o_type, TG_OP); RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER trg_objects_ui AFTER INSERT OR UPDATE ON objects FOR EACH ROW EXECUTE PROCEDURE trg_objects_ui(); CREATE OR REPLACE FUNCTION trg_objects_d() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN INSERT INTO history_objects (modified_id, modified_o_type, modification_type) VALUES (OLD.id, OLD.o_type, TG_OP); RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER trg_objects_d AFTER DELETE ON objects FOR EACH ROW EXECUTE PROCEDURE trg_objects_d();i testujemy:
testujemy:
> SELECT * FROM objects; id | o_type ----+-------- (0 ROWS) > SELECT * FROM history_objects; id | modified_on | modification_type | modified_id | modified_o_type ----+-------------+-------------------+-------------+----------------- (0 ROWS) > INSERT INTO objects (o_type) VALUES ('bleble'); INSERT 0 1 > INSERT INTO objects (o_type) VALUES ('bleble2'); INSERT 0 1 > UPDATE objects SET o_type = 'xxx' WHERE o_type = 'bleble'; UPDATE 1 > DELETE FROM objects WHERE o_type = 'bleble2'; DELETE 1 > SELECT * FROM objects; id | o_type ----+-------- 1 | xxx (1 ROW) > SELECT * FROM history_objects; id | modified_on | modification_type | modified_id | modified_o_type ----+----------------------------+-------------------+-------------+----------------- 1 | 2006-07-15 12:14:56.138695 | INSERT | 1 | bleble 2 | 2006-07-15 12:14:56.138695 | INSERT | 2 | bleble2 3 | 2006-07-15 12:14:56.138695 | UPDATE | 1 | xxx 4 | 2006-07-15 12:14:56.138695 | DELETE | 2 | bleble2 (4 ROWS)
działa ślicznie.
ale jest jedno ale.
czasem chcielibyśmy logować więcej informacji. w projekcie nad którym pracują koledzy w firmy chcieli logować nazwę akcji webowej (coś jakby url) i nazwę użytkownika które spowodowały taką modyfikację.
tu pojawiają się schody – baza danych nie zna urla. baza danych nie zna nazwy użytkownika który się zalogował na www – wie tylko jaki user jest zalogowany do bazy danych, ale to jest zazwyczaj zawsze jeden i ten sam user – niezależnie od tego na kogo się użytkownik loguje via www.
hmm. trzeba więc logować z poziomu aplikacji, a nie bazy.
no tak, ale jeśli user w jednej akcji powoduje modyfikacje wielu rekordów czy wielu tabel – sprawa zaczyna się komplikować.
na szczęście jest rozwiązanie.
wystarczy założyć dodatkową tabelkę do której będziemy dodawać nowy rekord przy rozpoczynaniu procedury obsługi każdego requestu, i będziemy tam wpisywać url'a i nazwę usera. a potem tylko baza musi umieć z tego skorzystać.
aby baza mogła powiązać dane z tej nowej tabelki (nazwijmy ją sobie “actions"), musi być w stanie jakoś powiązać późniejsze insert'y, update'y i delete'y z wpisami w actions. najlepiej to zrobić używając pidu (numer identyfikacyjny procesu) backendu postgresa.
w postgresie jest funkcja która zwraca ten pid:
SELECT pg_backend_pid();
dodajmy więc tabelkę actions, od razu z indeksem na to po czym będziemy szukać:
CREATE TABLE actions ( id BIGSERIAL, backend_pid INT4 NOT NULL DEFAULT pg_backend_pid(), action_on TIMESTAMP NOT NULL DEFAULT now(), action_url TEXT, action_user TEXT, PRIMARY KEY (id) ); CREATE INDEX ui_actions_bpao ON actions (backend_pid, action_on);
tabelkę history_objects modyfikujemy tak aby zawierała pola na urla i username (zamiast tego można dodać action_id, ale dodanie całych pól daje nam dodatkowe możliwości):
CREATE TABLE history_objects ( id BIGSERIAL, modified_on TIMESTAMP NOT NULL DEFAULT now(), modification_type TEXT NOT NULL DEFAULT '', modification_url TEXT, modification_user TEXT, modified_id INT8, modified_o_type TEXT, PRIMARY KEY (id) );
pozostało zmodyfikować triggery:
CREATE OR REPLACE FUNCTION trg_objects_ui() RETURNS TRIGGER AS $BODY$ DECLARE temprec actions%ROWTYPE; BEGIN SELECT * INTO temprec FROM actions WHERE backend_pid = pg_backend_pid() ORDER BY backend_pid DESC, action_on DESC LIMIT 1; IF NOT FOUND THEN temprec.action_url := 'unknown url. direct database access?'; temprec.action_user := 'database user: ' || CURRENT_USER; END IF; INSERT INTO history_objects (modified_id, modified_o_type, modification_type, modification_url, modification_user) VALUES (NEW.id, NEW.o_type, TG_OP, temprec.action_url, temprec.action_user); RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER trg_objects_ui AFTER INSERT OR UPDATE ON objects FOR EACH ROW EXECUTE PROCEDURE trg_objects_ui(); CREATE OR REPLACE FUNCTION trg_objects_d() RETURNS TRIGGER AS $BODY$ DECLARE temprec actions%ROWTYPE; BEGIN SELECT * INTO temprec FROM actions WHERE backend_pid = pg_backend_pid() ORDER BY backend_pid DESC, action_on DESC LIMIT 1; IF NOT FOUND THEN temprec.action_url := 'unknown url. direct database access?'; temprec.action_user := 'database user: ' || CURRENT_USER; END IF; INSERT INTO history_objects (modified_id, modified_o_type, modification_type, modification_url, modification_user) VALUES (OLD.id, OLD.o_type, TG_OP, temprec.action_url, temprec.action_user); RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER trg_objects_d AFTER DELETE ON objects FOR EACH ROW EXECUTE PROCEDURE trg_objects_d();
no to pora na test. najpierw zobaczymy jak to działa gdy nie zapiszemy nic do actions:
> SELECT * FROM objects; id | o_type ----+-------- (0 ROWS) > SELECT * FROM history_objects; id | modified_on | modification_type | modification_url | modification_user | modified_id | modified_o_type ----+-------------+-------------------+------------------+-------------------+-------------+----------------- (0 ROWS) > SELECT * FROM actions; id | backend_pid | action_on | action_url | action_user ----+-------------+-----------+------------+------------- (0 ROWS) > INSERT INTO objects (o_type) VALUES ('bleble'); INSERT 0 1 > INSERT INTO objects (o_type) VALUES ('bleble2'); INSERT 0 1 > UPDATE objects SET o_type = 'xxx' WHERE o_type = 'bleble'; UPDATE 1 > DELETE FROM objects WHERE o_type = 'bleble2'; DELETE 1 > SELECT * FROM objects; id | o_type ----+-------- 1 | xxx (1 ROW) > SELECT * FROM history_objects; id | modified_on | modification_type | modification_url | modification_user | modified_id | modified_o_type ----+----------------------------+-------------------+--------------------------------------+-----------------------+-------------+----------------- 1 | 2006-07-15 12:58:54.16915 | INSERT | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz | 1 | bleble 2 | 2006-07-15 12:58:58.46624 | INSERT | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz | 2 | bleble2 3 | 2006-07-15 12:59:04.454578 | UPDATE | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz | 1 | xxx 4 | 2006-07-15 12:59:09.519242 | DELETE | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz | 2 | bleble2 (4 ROWS)
działa ładnie. to teraz zapiszmy coś do actions i powtórzmy test:
> INSERT INTO actions (action_url, action_user) VALUES ('/jakis_url.html', 'admin'); INSERT 0 1 > INSERT INTO objects (o_type) VALUES ('A:bleble'); INSERT 0 1 > INSERT INTO objects (o_type) VALUES ('A:bleble2'); INSERT 0 1 > UPDATE objects SET o_type = 'A:xxx' WHERE o_type = 'A:bleble'; UPDATE 1 > DELETE FROM objects WHERE o_type = 'A:bleble2'; DELETE 1 > SELECT * FROM objects; id | o_type ----+-------- 1 | xxx 3 | A:xxx (2 ROWS) > SELECT * FROM history_objects; id | modified_on | modification_type | modification_url | modification_user | modified_id | modified_o_type ----+----------------------------+-------------------+--------------------------------------+-----------------------+-------------+----------------- 1 | 2006-07-15 12:58:54.16915 | INSERT | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz | 1 | bleble 2 | 2006-07-15 12:58:58.46624 | INSERT | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz | 2 | bleble2 3 | 2006-07-15 12:59:04.454578 | UPDATE | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz | 1 | xxx 4 | 2006-07-15 12:59:09.519242 | DELETE | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz | 2 | bleble2 5 | 2006-07-15 13:00:15.098039 | INSERT | /jakis_url.html | admin | 3 | A:bleble 6 | 2006-07-15 13:00:18.315004 | INSERT | /jakis_url.html | admin | 4 | A:bleble2 7 | 2006-07-15 13:00:21.304025 | UPDATE | /jakis_url.html | admin | 3 | A:xxx 8 | 2006-07-15 13:00:24.483061 | DELETE | /jakis_url.html | admin | 4 | A:bleble2 (8 ROWS)
działa. i to całkiem ładnie. oczywiście mechanizm można dalej rozszerzać. dodawać nowe pola (ip, browser) czy z części rezygnować.
Uhm: backend_pid = pg_backend_pid() to dodaje dużo smaczku do Twojego rozwiązania. Ale czy na pewno jest bezpieczne? Co w przypadku, jeżeli aplikacja korzysta z warstwy pośredniej do bazy danych, np.: hibernate?
nie wiem. nie uzywam hibernate’a.
trzeba by potestowac lub poszukac w dokumentacji od uzywanego object2relation mappera jak to wyglada.
depesz
SELECT * INTO temprec FROM actions WHERE backend_pid = pg_backend_pid() ORDER BY backend_pid desc, action_on desc LIMIT 1;
Dlaczego tu jest sortowanie po backend_pid? Chyba powinno być po id. 🙂
po action_on powinno byc i jest. sortowanie po backend_pid jest dodane bo dzięki temu można użyć jeden index on actions (backend_pid, action_on) dzieki czemu wynik tego zapytania to bedzie po prostu odczytanie ostatniego rekordu z indeksu (backward index scan) i juz. czytaj – szybkie.
Racja, chciałem uzuskac ten sam efekt, tylko jakoś umknęło mi co to jest action_on.
chciałem zastosować powyższe rozwiązanie do swojego projektu. Z poziomu bazy danych wszystko działa. Natomiast z poziomu php po zalogowaniu i dodaniu do tabeli actions kto się zalogował, trigger nie wpisuje w tabele jednej kolumny, właśnie tej kto się zalogował czyli kto dokonał zmian w tabeli objects. Reszte wstawia typ zmiany , id mienionego rekordu, zmiane. Czy ktoś pomoże rozszyfrować tą różnice wstawienia action_user z poziomu bazy i z poziomu php ?
@Cikus:
zaloguj wszystkie zapytania które idą do bazy i porównaj czym się różnią.
jak chcesz mogę to zdiagnozować dla ciebie, jako normalne zlecenie, ale to temat nie na komentarze – jeśli jesteś zainteresowany – napisz maila. depesz@depesz.com.