Wymagany MySQL >= 5
Przykładowo mamy 3 tabele:
- Kod: Zaznacz cały
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| news |
| news_history |
| users |
+----------------+
3 rows in set (0.01 sec)
mysql>
Pierwsza tabela jest typowo okrojona, stworzona na potrzeby testow:
- Kod: Zaznacz cały
mysql> show columns from news;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| uid | int(11) | NO | | NULL | |
| title | varchar(255) | NO | | NULL | |
| text | text | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
id wiadomo, uid jest identyfikatorem użytkownika z tabeli users, reszta oczywista..
- Kod: Zaznacz cały
mysql> show columns from news_history;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| news_id | int(11) | NO | | NULL | |
| user | varchar(30) | NO | | NULL | |
| time | int(11) | NO | | NULL | |
| old_text | text | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql>
W tej tabeli będziemy zapisywać edycję newsów przez użytkowników. No i w końcu:
- Kod: Zaznacz cały
mysql> show columns from users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| nick | varchar(30) | NO | | NULL | |
| pass | varchar(32) | NO | | NULL | |
| mail | varchar(100) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
Wypełniłem tabele danymi:
- Kod: Zaznacz cały
mysql> select * from users;
+----+-------+----------------------------------+--------+
| id | nick | pass | mail |
+----+-------+----------------------------------+--------+
| 1 | userA | 76a2173be6393254e72ffa4d6df1030a | a@a.pl |
| 2 | userB | 76a2173be6393254e72ffa4d6df1030a | b@b.pl |
+----+-------+----------------------------------+--------+
2 rows in set (0.00 sec)
mysql> select * from news;
+----+-----+-------+-------+
| id | uid | title | text |
+----+-----+-------+-------+
| 1 | 1 | tytul | tresc |
+----+-----+-------+-------+
1 row in set (0.00 sec)
mysql> select * from news_history;
Empty set (0.00 sec)
mysql>
Tabela news_history jest pusta. Do niej właśnie będzie pisał wyzwalacz. Stwórzmy go zatem:
- Kod: Zaznacz cały
mysql> delimiter //
mysql> create trigger newsEdit before update on news
-> for each row
-> begin
-> set @nick = (select nick from users where id = new.id);
-> insert into news_history values(new.id, @nick, unix_timestamp(), old.text);
-> end;//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
Po polsku można ten kod przedstawić następująco:
- - Stwórz wyzwalacz uruchamiany przed edycją tabeli news
- przejdź przez wszystkie wiersze
- na początku
- przypisz nick usera edytującego do zmiennej @nick
- Zapisz dane do tabeli news_history
- zakończ
Wyzwalacz udostępnia nam prefixy old i new, odpowiednio przypisane do danych występujących (old) jak i ich odpowiedników nadpisywanych (new). Dzięki temu mysql automatycznie będzie zapisywał historię zmian w momencie edycji tabeli news. Sprawdźmy:
- Kod: Zaznacz cały
mysql> update news set uid=2, text='nowa tresc' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from news;
+----+-----+-------+------------+
| id | uid | title | text |
+----+-----+-------+------------+
| 1 | 2 | tytul | nowa tresc |
+----+-----+-------+------------+
1 row in set (0.00 sec)
mysql> select * from news_history;
+---------+-------+------------+----------+
| news_id | user | time | old_text |
+---------+-------+------------+----------+
| 1 | userA | 1248963464 | tresc |
+---------+-------+------------+----------+
1 row in set (0.00 sec)
mysql>
Jest to jeden z najprostszych systemów śledzenia użytkowników. Każda zmiana zostanie odnotowana. Można również stworzyć wyzwalacze dla operacji typu insert czy delete, dla większej kontroli, ale to już zostawiam Waszej fantazji.
Mamy już wyzwalacz, pozostaje widok. Zauważ, że w tabeli news nie ma użytkownika, ani czasu ostatniej modyfikacji. Dla wygody stworzymy widok przedstawiający dane, które potrzebujemy na wyjściu (najczęściej skryptu):
- Kod: Zaznacz cały
mysql> create view vNews as
-> select n.id, n.title, n.text, u.id uid, u.nick, u.mail, from_unixtime(h.time, '%d.%m.%Y') lastMod
-> from news n left join users u on u.id = n.uid left join news_history h on h.news_id = n.id;
Query OK, 0 rows affected (0.00 sec)
mysql>
Od tego momentu możemy korzystać z widoku jak ze zwykłej tabeli:
- Kod: Zaznacz cały
mysql> select * from vNews;
+----+-------+------------+-----+-------+--------+------------+
| id | title | text | uid | nick | mail | lastMod |
+----+-------+------------+-----+-------+--------+------------+
| 1 | tytul | nowa tresc | 2 | userB | b@b.pl | 30.07.2009 |
+----+-------+------------+-----+-------+--------+------------+
1 row in set (0.00 sec)
mysql>
Jest to bardzo przydatne, gdy potrzebujemy pobrać dane z wielu tabel naraz w kodzie. Widoki ułatwiają pracę, gdyż nie jesteśmy zmuszeni przez większość kodu pisać długich zapytań, wystarczy raz stworzyć odpowiedni widok, którego oczekujemy.
Materiały teoretyczne:
http://dev.mysql.com/doc/refman/5.0/en/create-view.html
http://dev.mysql.com/doc/refman/5.0/en/ ... yntax.html
http://th-www.if.uj.edu.pl/zfs/gora/bazy08/wyklad10.pdf
Pozdro
