Celem kolejnego ze zleceń jakie otrzymaliśmy jest napisanie rozbudowanego systemu ankiet z możliwością powrotu i zmiany odpowiedzi a co za tym idzie śledzeniem zmian.

Zanim talen wspomniał coś przy innej okazji nt. TRIGGERów w MySQL zapewne zrobił bym to używając kilku SELECT połączonych z UPDATE i INSERT. Jednak dzięki mechanizmowi TRIGGERów możemy to spłycić do jednego zapytania. Jak? Za chwilkę, najpierw o mechanizmie.

TRIGGERy to coś na kształt EVENTów - odpalają się w bazie kiedy zajdzie opisana przez programistę podczas ich tworzenia sytuacja - np, jakiś wiersz zostanie zmodyfikowany, usunięty lub dodany - pozawala nam to na jednokrotne zaprogramowanie takiego zdarzenia w MySQL i nie zawracanie sobie już tym głowy w warstwie aplikacji.

Przejdźmy do zadania - tworzymy sobie prostą tabelę:

CREATE TABLE IF NOT EXISTS `answers` (
        `question_id` int(11) NOT NULL,
        `user_id` int(11) NOT NULL,
        `answer_id` int(11) NOT NULL,
        UNIQUE KEY `question_id` (`question_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Jak widać, założyłem klucz UNIQUE na pola question_id i user_id - zapobiega to dodaniu wielu odpowiedzi na jedno pytanie przez tego samego użytkownika. Następnym krokiem jest stworzenie tabeli, która przechowywać będzie archiwalne odpowiedzi - kod jest prawie identyczny:

 CREATE TABLE IF NOT EXISTS `answers_old` (
        `question_id` int(11) NOT NULL,
        `user_id` int(11) NOT NULL,
        `answer_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Tabela nie posiada żadnych indexów, ponieważ każda odpowiedź mogła być zmieniona więcej niż raz, więc będziemy mieli więcej niż jedno archiwum.

Następnym krokiem jest stworzenie samego TRIGGERa:

DELIMITER //
        CREATE TRIGGER `test`.`archiwizuj` BEFORE UPDATE ON `test`.`answers`
                FOR EACH ROW BEGIN
                        INSERT INTO answers_old SET question_id = OLD.question_id, answer_id = OLD.answer_id, user_id = OLD.user_id;
                END
        //
DELIMITER ;

Przed utworzeniem samego TRIGERRA zmieniamy znak końca funkcji na coś innego niż standardowy średnik, by średniki w treści zapytania nie wykrzaczyły nam operacji. Jeżeli robicie to przez phpMyAdmin i po wydaniu polecenia zwrócony zostanie błąd, a macie pewność, że wasza baza obsługuje TRIGERRy sprawdźcie czy mimo wszystko nie został on pomyślnie utworzony - u mnie tak właśnie było, a ja jak głupi szukałem błędu. Cóż.

Teraz demonstracja:

 INSERT INTO `answers` SET `question_id` = 1, `user_id` = 1, `answer_id` = 1 ON DUPLICATE KEY UPDATE `answer_id` = 1;

Proste zapytanie, ustawia odpowiedź “1″ dla użytkownika “1″ i pytania “1″ - jeżeli istnieje już wpis użytkownika dla tego pytania, zaktualizuj odpowiedź na 1.

Po tym wywołaniu mamy jedynki w tabeli. Następne podejście:

INSERT INTO `answers` SET `question_id` = 1, `user_id` = 1, `answer_id` = 2 ON DUPLICATE KEY UPDATE `answer_id` = 2;

Użytkownik aktualizuje odpowiedź - pole `answer_id` zmieniło się na 2. Jak dotąd - nic nowego. Jednak jeżeli zajrzymy do tabeli `answers_old` zobaczymy tam wpis, który widzieliśmy poprzednio w `answers`. Jeszcze jedno zapytanie:

INSERT INTO `answers` SET `question_id` = 1, `user_id` = 1, `answer_id` = 3 ON DUPLICATE KEY UPDATE `answer_id` = 3;

W tabeli `answers` mamy odpowiedź numer 3, zaś w `answers_old` są już dwa wpisy z odpowiedziami 1 i 2.

Jak widać całość działa świetnie i zostało nam już tylko napisanie mechanizmu obsługującego informowanie administratora o zmianie odpowiedzi i wyświetlanie historii. Oczywiście ten przykład odnosi się nie tylko do ankiet, ale do wszelkich rodzaju danych, których stare wersje chcemy zachowywać w bazie danych - np. na potrzeby archiwizowania starej wersji strony w celu jej późniejszego odtworzenia etc etc.

Ostatnio przyszło mi pisać dwa serwisy od podstaw (znaczy: nie używając firmowych CMS-ów) więc miałem wolną rękę w kwestii zastosowanych skryptów, wzorców projektowych etc. Jako że strony docelowo mają mieć dużą odpowiedzialność postanowiłem przyłożyć się do optymalizacji.

Wprowadzenie

Najczęściej używaną funkcją, która może jednocześnie najbardziej obciążać bazę jest wyszukiwanie. Produkty wyszukiwane są pod kątem wielu parametrów przechowywanych w znormalizowanej bazie danych. Na pewno ułatwia to utrzymanie porządku, ale generuje też skomplikowane (zarówno pod wzg. długości jak i obciążenia bazy) zapytania SQL. Dlatego postanowiłem zastosować pewien prosty ale jakże pomocny trick.

Wyszukiwanie + zapis w bazie

Po wysłaniu przez danych użytkownika wyszukujemy wszystkie produkty spełniające kryteria wyszukiwania. W ten sposób w jednym zbiorze mamy wszystkie produkty (a prawdę mówiąc wystarczą nam ID produktów) oraz ich ilość. To wszystko (wraz z parametrami wyszukiwania o ile tego potrzebujemy) zapisujemy do bazy danych (np. stosując serializację) i zwracamy sobie ID wyszukiwania.

Kontynuacja wyszukiwania

Posiadając już ID elementów, które spełniają nasze kryteria wyszukiwania możemy w prosty sposób wyświetlić te, które nas interesują - do dzielenia na strony możemy użyć np. array_slice, następnie wybrać z bazy tylko te wyniki (w celu odpowiedniego posortowania można użyć funkcji FIELD opisywanej wcześnie).

Poza oczywistym zyskiem nie przeszukiwania n tabel za każdym razem kiedy osoba wyszukująca zmieni stronę możemy używać prostszych adresów URI - zamiast ?szukaj=KL5000&producent=asus&cena[od]=1000&cena[do]=3000&cpu=&gpu=&strona=5 możemy utworzyć adresy typu ?szukaj=12&strona=5

Zapewne wielu z was (nas) miało nie raz styczność z poniższym zagadnieniem:

System aktualności z podziałem na kategorie, z założeniem, że jedna aktualność może należeć do wielu kategorii - np. system blogowy. Zakładając, że wiemy co to normalizacja bazy danych, robimy coś mniej więcej takiego:

1. tabela `posts` (id, title, text)
2. tabela `tags` (id, name)
3. tabela `tags_to_posts` (post_id, tag_id)

Następnie, w celu wybrania postów i kategorii doń należących wywołujemy minimum 2 zaptytania:

Wybieramy wiadomości (zapytanie A):

SELECT * FROM `posts`

I w pętli wybieramy kategorie (zapytanie B):

SELECT t.name FROM `tags_to_posts` LEFT JOIN `tags` ON tags.id = tags_to_posts.tag_id WHERE tags_to_posts.post_id = ID_POSTU

Niby nic złego, i wiele osób zasugeruje takie właśnie rozwiązanie. Istnieje natomiast prostsze rozwiązanie, pozwalające nam wyciągnąć wszystkie interesujące nas informacje za jednym razem - jedyny warunek - MySQL >= 4.1

Powitajmy GROUP_CONCAT()

GROUP_CONCAT(nazwa_pola ORDER BY nazwa_pola SEPARATOR ',')

Przykład zastosowania (zapytanie C):

SELECT p.*, GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', ' ) AS `kategorie` FROM `posts` AS `p` LEFT JOIN `tags_to_posts` AS `ttp` ON ttp.post_id = p.id LEFT JOIN `tags` AS `t` ON t.id = ttp.tag_id GROUP BY p.id

W wyniku zapytania otrzymamy wszystkie dane z tabeli `posts` ORAZ nową kolumnę o nazwie kategorie zawierającą wszystkie kategorie do których należy dany post oddzielone przecinkiem.

Co prawda zamiast dwóch prostych SELECTów zastosowaliśmy dwa LEFT JOINy - ale jeżeli przyjrzymy się bliżej, okazuje się, że zaoszczędziliśmy N zapytań, gdzie N to ilość pobranych przez zapytanie A aktualności (zapytanie B wykonywane jest w pętli dla każdego rekordu pobranego w zapytaniu A).

Więcej informacji (oraz przykładów zastosowania) znajdziesz w manualu MySQL.

Funkcja FIELD() w MySQL zwraca pozycję pierwszego podanego stringu w zbiorze stringów, podanych jako kolejne argumenty funkcji.

Niby nic specjalnego, ale jednak ma ona jedno kluczowe zastosowanie!

Wyobraźmy sobie taką kwerendę:

SELECT * FROM `items` WHERE `id` IN(1, 8, 2, 14, 16, 7)

Najprostsza w świecie kwerenda zwracająca nam przedmioty z jakiegośtam zbioru. W jakiej kolejności będą posortowane wyniki? Najprawdopodobniej wg. PRIMARY KEY. Ale co zrobić, jeżeli chcemy by były w takiej, jaka widnieje w IN() - może są to dane uzyskane z zewnętrznego źródła, posortowane tak, jak oczekuje tego klient?

Tu z pomocą przychodzi nam FIELD() - dzięki tej funkcji wszystko posortujemy tak jak chcemy i to błyskiem. Wystarczy przerobić kwerendę na następującą:

SELECT * FROM `items` WHERE `id` IN(1, 8, 2, 14, 16, 7) ORDER BY FIELD(`id`, 1, 8, 2, 14, 16, 7)

Dzięki temu wyniki będą posortowane dokładnie w takiej kolejności, w jakiej występują w podanym zbiorze!