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.

Większość z programistów zdaje sobie sprawę z tego, jakim zagrożeniem może być błędnie napisany skrypt pozwalający na upload plików na serwer. Dlatego, większość ogranicza możliwość wgrywania plików przez odpowiednie filtrowanie na podstawie mime-type np. w taki sposób:

<?php
if($_FILES["type"] == "image/jpeg") {
// logika wgrywania dla plików JPEG
}
else {
echo "Nie można wgrać pliku takiego typu - tylko JPEG!";
}

Wszystko ładnie - wiemy, że wgrywany plik, jest de-facto plikiem jpeg.

Na pewno?

Otóż nie - dane, które znajdujemy w tablicy _FILES (name, size, type) są danymi, które podała nam przeglądarka podczas wysyłania pliku! Nie daje to nam w żaden sposób pewności, że złośliwy użytkownik nie zmienił swojej przeglądarki / nie użył innego programu który wysłał nam plik.php przy okazji dodając, że jest to format image/jpeg !

Jeżeli nie zabezpieczymy się przed tego typu zagraniami otwieramy serwer na wszelkiego rodzaju ataki. Zostają dwa rozwiązania, które najlepiej stosować jednocześnie

1. sprawdź rozszerzenie pliku - rozszerzenie wyciągamy w następujący sposób:

   <?
   $ext = substr($file, strrpos($file,".") + 1);
   

2. upewnij się, że plik rzeczywiście jest typu image/jpeg - np. przy użyciu funkcji getimagesize która jako wynik zwraca tablicę asocjacyjną - pod kluczem mime znajduje się typ pliku odczytany już na naszym serwerze

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

W poprzednim wpisie poruszyłem temat wykorzystania XCache jako rozszerzenia do cachowania danych w PHP. Temat przechowywania wartości zmiennych był tam tylko lekko poruszony, jednak ostatnio sytuacja zmusiła mnie do ponownego rozpatrzenia sprawy.

Parę dni temu odbyły się polskie eliminacje do Eurowizji - jako, że strona eurowizja.com.pl, która utrzymywana jest na jednym z moich serwerów, w ciągu dwóch dni (dzień eliminacji i dzień po) wygenerowała 4 razy więcej ruchu niż w całym styczniu, serwer zaczął ledwo zipać. Postanowiłem zakasać rękawy i użyć cachowania zmiennych do przechowywania często pobieranych, ale nie często modyfikowanych informacji.

Konfiguracja PHP

Pierwsze co musimy zrobić, to zmodyfikować ponownie nasz pliki php.ini aktywując cachowanie zmiennych:

xcache.var_size = 4M
xcache.var_count = 1
xcache.var_slots = 8K
xcache.var_ttl = 3600
xcache.var_maxttl = 0
xcache.var_gc_interval = 300

Większość zmiennych konfiguracyjnych jest bardzo podobna do omawianych w poprzednim artykule, więc w razie wątpliwości odsyłam do owego wpisu. Pojawiają się właściwie dwie nowe:

xcache.var_maxttl - określa maksymalny czas życia zmiennej, jaki możemy ustawić podczas jej definiowania
xcache.var_gc_interval - określa częstotliwość (w sekundach) z jaką powinien być uruchamiany Garbage Collector, którego zadaniem jest usuwanie nie używanych danych z pamięci

Nowe funkcje z rozszerzenia XCache

Kiedy już mamy wszystko poprawnie skonfigurowane (i zresetowaliśmy serwer www!) możemy przystąpić do przerabiania naszej aplikacji. Przydadzą nam się teraz 4-ry funckje dodane przez XCache do PHP:

bool xcache_isset(string name)
bool xcache_unset(string name)
int xcache_inc(string name [, int value [, int ttl]])
int xcache_dec(string name [, int value [, int ttl]])

Przy ich pomocy możemy odpowiednio zmodyfikować nasze skrypty tak, by dane, które są nam potrzebne ale trudne do zdobycia (wymagają dużego nakładu na SQL, znajdują się na innym serwerze etc.) były zawsze pod ręką.

Przykład użycia

Poniżej zamieszczam przykładowy kod, który pobiera dane z bazy SQL tylko wtedy, kiedy ich potrzebuje.

<?
// dołącz nagłówki, połączenia z bazą etc.
if(!xcache_isset('xcache_data')) { // 1
$data = pobierzBardzoWazneDaneZSQL();
xcache_set('xcache_data', $data, 3600); // 2
}
else $data = xcache_get('xcache_data'); // 3

Krótkie wyjaśnienie

1. Sprawdzamy, czy XCache przechowuje jakieś zmienne w swojej prywatnej zmiennej xcache_data
2. Zapisujemy nasze informacje w zmiennej xcache_data i zastrzegamy, że dane te mają być uważane za aktualne przez najbliższe 3600 sekund (godzinę)
3. Jeżeli jednak dane są przechowywane w pamięci podręczne, pobieramy je z niej

Należy zwrócić uwagę na dwie rzeczy:

1. Nazwa zmiennej, w jakiej my przechowujemy dane nie jest nazwą zmiennej, pod jaką są one przechowywane w xcache!
2. Ponieważ możmy zapisać dane pod dowolną nazwą w xcache polecam stosowanie prefixów, tak by przypadkiem nie nadpisać sobie danych w innym ppliku / skrypcie. Ja osobiście używam prefixu będącego wynikiem md5() ze stałej __FILE__ co powoduje prawie pewną unikalność nazw zmiennych

Usuwanie danych z cache

W naszym skrypcie zdecydowaliśmy, że dane mają być przechowywane w xache przez minutę (patrz pkt. 1) - oc jeżeli dane zostaną przez nas umyślnie zmodyfikowane (np. aktualność zostanie zaktualizowana, ktoś zagłosuje w sondzie)? Do pomocy mamy dwa mechanizmy:

1. Możemy zalogować się do XCache Admin (więcej na ten temat w poprzednim wpisie)
2. Możemy użyć funkcji xcache_unset do usunięcia zmiennej z cache

Podsumowanie

Po zastosowaniu XCache na wspomnianej stronie Eurowizji stabilność serwera uległa zdecydowanemu polepszeniu - ilość wątków Apache i MySQL spadła o połowę, load wrócił do normy, a ja nie zobaczyłem już więcej wiadomości “strona nie działa!” od klienta :-)