Bazy danych, szablony i surowe tablice

Jedna z technik programowania witryn internetowych, które wykorzystują bazy danych, polega na przekazywaniu do szablonu wyników zapytań SQL. Rola skryptu PHP sprowadza się do ustalenia wybranej podstrony, pobrania informacji z bazy danych oraz przekazania ich do szablonu. Zaletą takiego rozwiązania jest łatwość rozbudowy. Technikę taką omówię na przykładzie aplikacji, która służy do analizy logów serwera Apache.

Struktura pliku log

Żądania HTTP napływające z sieci do serwera Apache są zapisywane w pliku nazywanym logiem. Plik ten – zależnie od konfiguracji serwera Apache – może się nazywać access.log.

Przykładowy wiersz pliku log ma postać (ze względu na długość wiersz ten został podzielony na osiem linii):

efx202.internetdsl.tpnet.pl
- -
[04/Mar/2006:11:38:46 +0100]
\"GET /index.html HTTP/1.0\"
200
6693
\"http://szukaj.onet.pl/query.html?qt=gimp\"
\"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)\"

Podany wpis mówi o tym, że w dniu 4 marca 2006, o godzinie 11:38:46, z komputera o adresie efx202.internetdsl.tpnet.pl nadeszło żądanie HTTP postaci: {stala}GET /index.html HTTP/1.0{/stala}. Słowo GET to kod tak zwanej metody protokołu HTTP.

W odpowiedzi serwer wysłał kod 200 (czyli: poprawne żądanie, dokument odnaleziony) oraz dokument /index.html zawierający 6693 bajtów. Dokument ten ma oczywiście rozszerzenie .html. Przeglądarką użytkownika był Internet Explorer 6.0 (mówi o tym napis MSIE 6.0 zawarty w ostatnim polu pliku log). Internauta trafił do nas po wyszukaniu wyszukiwarką Onet wyrazu \”gimp\”, o czym informuje nas pole \”http://szukaj.onet.pl/query.html?qt=gimp\”.

Dwie poziome kreski występujące po nazwie hosta to nazwa konta i hasło użytkownika.

Baza danych log

Informacje zawarte w logu serwera Apache przekształcamy w bazę danych. Struktura bazy danych została przedstawiona na rys. 1.

Baza danych składa się z ośmiu tabel:

• thost – adresy, z jakich przyszły żądania,
• tua – przeglądarki,
• turl – adresy wysyłanych stron,
• treferer – adresy skierowujące,
• thttpmethod – metody protokołu HTTP,
• thttpstatuscode – odpowiedzi protokołu HTTP,
• tfilenameextension – rozszerzenia plików,
• thttprequest – żądania HTTP.

Najważniejszą i najbardziej rozbudowaną tabelą jest tabela thttprequest. W tabeli tej znajdują się pola będące kluczami obcymi, odwołujące się do pozostałych siedmiu tabel.

Zadanie 1

Pierwsze z zadań polega na wyświetleniu zawartości tabel bazy danych log. Należy przygotować witrynę, której menu główne zawiera opcje: REQUESTS, HOSTS, URLS, USER AGENTS, REFERER, METHODS, HTTP STATUS CODES, FILENAME EXTENSIONS. Każda z opcji ma powodować wyświetlenie rekordów zawartych w odpowiedniej tabeli.

W przypadku opcji REQUESTS wyświetlana tabela ma zawierać wszystkie dostępne informacje, czyli m.in. adres komputera, z jakiego nadeszło żądanie, pobrany adres URL czy nazwę przeglądarki użytkownika, jak to przedstawiono na rys. 2.

Natomiast pozostałe opcje mają wyświetlać listę rekordów wraz z informacją o odpowiadającej im liczbie żądań HTTP. Dane mają być posortowane malejąco pod względem liczby żądań (rys. 3).

Rozwiązanie zadania 1

Rozwiązanie zadania rozpoczynamy od przygotowania klasy DBA odpowiedzialnej za wykonywanie zapytań SQL. Klasa ta wykorzystuje klasę DB i zawiera – oprócz konstruktora – metody odpowiedzialne za pobranie informacji z odpowiednich tabel bazy danych.

Na przykład metoda {stala}DBA_thost(){/stala} zwraca tablicę zawierającą dane o wszystkich hostach. W analogiczny sposób należy przygotować metody {stala}DBA_turl(){/stala}, {stala}DBA_treferer(){/stala} oraz wszystkie pozostałe (listing 1).

class DBA
{
    private $Fdba;

    public function __construct()
    {
        $dsn = \'mysql://loger:@localhost/log\';
        $this->Fdba = DB::connect($dsn);
        if (DB::isError($this->Fdba)) {
            die(__LINE__ . \', \' . $this->Fdba->getMessage());
        }

        $this->Fdba->setFetchMode(DB_FETCHMODE_ASSOC);
    }

    public function DBA_thost()
    {
        $q = \'
            SELECT
                h.thost_id as no,
                h.thost_id,
                h.host,
                COUNT(q.thttprequest_id) as ile
            FROM
                thost as h,
                thttprequest as q
            WHERE
                h.thost_id = q.thost_id
            GROUP BY
                q.thost_id
            ORDER BY
                ile DESC, host ASC
        \';

        $w = $this->Fdba->getAll($q);

        if (DB::isError($w)) {
            die(__LINE__ . \', \' . $w->getMessage());
        } else {
            $ile = count($w);
            for ($i = 0; $i < $ile; $i++) {
                $w[$i][\'no\'] = $i +1;
            }
            return $w;
        }
    }

    ...

}

Po przygotowaniu klasy DBA przystępujemy do pracy nad skryptem głównym index.php. W skrypcie tym znajdziemy cztery etapy przetwarzania.

W etapie pierwszym dołączamy do skryptu wymagane pliki i inicjalizujemy zmienne. W etapie drugim przechodzimy do sprawdzenia wszystkich zmiennych przekazanych do skryptu (czyli badamy zawartość tablicy {stala}$_GET{/stala}). Etapem zasadniczym jest etap trzeci. W nim pobieramy dane z bazy i przekazujemy je do szablonu. Zadanie to realizujemy za pośrednictwem klasy DBA. Ten fragment aplikacji będzie zazwyczaj ujęty w dużą instrukcję {stala}switch{/stala}, w której wykonywany jest jeden przypadek:

switch ($akcja) {
...
case 3:
    $dane = $db->DBA_thttprequest();
    $s->assign(\'dane\', $dane);
    break;
case 4:
    $dane = $db->DBA_thost();
    $s->assign(\'dane\', $dane);
    break;
case 5:
    $dane = $db->DBA_turl();
    $s->assign(\'dane\', $dane);
    break;
...
}

Zwróćmy uwagę, że w skrypcie index.php nie zachodzi żadne przetwarzanie danych pobranych z bazy. Wyniki wywołania metody {stala}DBA_thost(){/stala} są przypisywane do zmiennej {stala}$dane{/stala}, po czym zostają przekazane do szablonu.

W etapie czwartym przetwarzamy szablon i wysyłamy stronę WWW.

Ostatnim zadaniem jest przygotowanie szablonu. Szablon rozbijamy na kilka mniejszych plików przetwarzanych warunkowo. Plik główny, szablon.tpl, zawiera instrukcje {stala}if{/stala} dołączające odpowiedni plik na podstawie zmiennej {stala}$akcja{/stala}:

...
{elseif $akcja == 3}
    {include file=\"thttprequest.tpl\"}
{elseif $akcja == 4}
    {include file=\"thost.tpl\"}
{elseif $akcja == 5}
    {include file=\"turl.tpl\"}
...

Natomiast poszczególne pliki thttprequest.tpl, thost.tpl, turl.tpl i wszystkie pozostałe zawierają sekcje przetwarzające tablicę {stala}$dane{/stala} przekazaną do szablonu w instrukcji {stala}switch{/stala} pliku index.php:

{section name=i loop=$dane}

{/section}


  

  
HOST
  
REQUESTS

  
{$dane[i].no}.
  
{$dane[i].host}
  
{$dane[i].ile}

Uwagi do zadania 1

Na co należy zwrócić uwagę stosując opisaną metodę rozwiązywania zadań? Po pierwsze należy stosować ścisły podział na kod PHP oraz na szablon. Innymi słowy, w skrypcie PHP nie stosujemy żadnych instrukcji {stala}echo{/stala} ani żadnych znaczników HTML. Kod HTML może się pojawić wyłącznie w szablonie (tj. w plikach .tpl) oraz ewentualnie w bazie danych.

Po drugie wszystkie operacje dotyczące bazy danych zamykamy w klasie DBA. Zapytania SQL mają prawo wystąpić wyłącznie w pliku dba.class.php. Cała pozostała część aplikacji kontaktuje się z bazą danych za pośrednictwem klasy DBA.

Dane pobierane z bazy danych za pośrednictwem klasy DBA i przekazywane - bez żadnych modyfikacji - do szablonu:

$dane = $db->DBA_thost();
$s->assign(\'dane\', $dane);

nazywam \"surowymi\", by podkreślić fakt, że skrypt index.php nie ingeruje w żaden sposób w wyniki zwrócone przez klasę DBA.

Dążymy również do tego, by - o ile to możliwe - wszystkie operacje na danych były wykonane w języku PHP. Nie sortujemy zwracanych wyników, a stosujemy klauzulę ORDER BY. Do ograniczania wyników wykorzystujemy klauzulę ORDER BY, a nie operacje na tablicach.

Jeśli zechcemy zrezygnować ze ścisłego stosowania do takich zasad, to pamiętajmy, by kod modyfikujący dane ukryć w klasie DBA lub ewentualnie - w bardziej rozbudowanych przykładach - w dodatkowej klasie. W taki sposób wykonałem numerowanie wierszy zwracanych przez bazę danych. Pętla {stala}for{/stala} zwarta w metodzie {stala}DBA_thost(){/stala} klasy DBA odpowiada za ponumerowanie poszczególnych wierszy zawartych w tabeli {stala}$w{/stala}. To samo zadanie możemy zrealizować stosując dwa zapytania SQL oraz zmienne MySQL (tabela 1).

query($q1);

$q = \"
    SELECT
        @i := @i + 1, host
    FROM
        thost
    ORDER BY
        host
\";

$tmp = $dba->getAll($q);

if (DB::isError($tmp)) {
    echo \'Blad: getAll()\';
    exit();
}

foreach ($tmp as $p) {
    echo \"\";
    echo \"\";
    echo \"\";
    echo \"\n\n\";
}

$dba->disconnect();


?>


{$p[0]}.
{$p[1]}

Jeszcze bardziej skomplikowane okazało się przygotowanie wyników dotyczących metod protokołu HTTP oraz kodów odpowiedzi (zawartość tabel thttpmethod oraz thttpstatuscode). Metody {stala}DBA_thttpmethod(){/stala} oraz {stala}DBA_thttpstatuscode(){/stala} wykonują po dwa zapytania SQL. Wyniki zapytań są w specjalny sposób łączone.

Zwróćmy uwagę na trik ułatwiający przygotowanie szablonu. Zastosowanie trybu FETCH_MODE_ASSOC w metodach klasy DB (patrz: wywołanie metody {stala}setFetchMode(){/stala} w konstruktorze klasy DBA) powoduje, że wyniki zapytań zwracane metodą {stala}getAll(){/stala} klasy DB będą zawarte w tablicach asocjacyjnych. Dzięki temu możemy modyfikować zapytania SQL (szczególnie: kolejność kolumn), zaś szablon pozostanie poprawny.

Na przykład zapytanie:

SELECT
    ua.tua_id as no,
    ua.tua_id,
    ua.ua,
    COUNT(q.tua_id) as ile
FROM
...

po wywołaniu metody {stala}getAll(){/stala} zwróci tablicę, którą przetworzy sekcja:

{section name=i loop=$dane}

  {$dane[i].no}.
  {$dane[i].ua|truncate:\"100\"}
  {$dane[i].ile}

{/section}

Do pól poszczególnych wierszy tablicy {stala}$dane{/stala} odwołujemy się stosując notację z kropką (wykorzystywaną między innymi do struktur w C oraz rekordów w Pascalu):

$dane[i].no
$dane[i].ua
$dane[i].ile
$dane[i].tua_id

Zmiana kolejności kolumn w zapytaniu:

SELECT
    COUNT(q.tua_id) as ile,
    ua.tua_id,
    ua.ua,
    ua.tua_id as no,
FROM
...

nie pociąga za sobą konieczności zmiany szablonu. Zauważ, że kolumny posiadające aliasy (czyli \'as ile\', \'as no\') są indeksowane aliasami, zaś pozostałe - nazwami kolumn. Warto to zapamiętać. Zmiany nazw kolumn w tabelach możemy zatem \"przysłonić\" przed szablonem stosując aliasy.

Dopóki aplikacja nie jest przetestowana i poprawna, warto korzystać z funkcji {stala}die(){/stala} oraz metody {stala}getMessage(){/stala} informującej o błędach klasy DB. Drukowane informacje wzbogać o stałą {stala}__LINE__{/stala}:

die(__LINE__ . \', \' . $this->Fdba->getMessage());

Dzięki temu można łatwiej zlokalizować błędy.

Gdy rozwiązanie jest gotowe, przyjrzyjmy się klasie DBA. Metody {stala}DBA_thost(){/stala}, {stala}DBA_turl(){/stala}, {stala}DBA_treferer(){/stala} są niemal identyczne. Różni je jedynie wykonywane zapytanie SQL. Zatem usuwamy redundancję w kodzie, definiując metodę {stala}DBA_query1(){/stala}.

Analogiczne podobieństwo cechuje metody {stala}DBA_thttpmethod(){/stala} oraz {stala}DBA_thttpstatuscode(){/stala}. Metoda {stala}DBA_query2(){/stala} usuwa drugi rodzaj redundancji.

Oba rozwiązania (to z redundantnymi funkcjami {stala}DBA_txxxxx{/stala} oraz bez redundancji) są zawarte w tabeli 1.

Zadanie 2

Najważniejszą wadą poprzedniego rozwiązania jest brak stronicowania wyników. Baza danych będzie zawierała dziesiątki tysięcy rekordów. Wydrukowanie tabeli z kilkoma tysiącami wierszy nie ma sensu. Zatem w zadaniu drugim należy poprzednie rozwiązanie wzbogacić o stronicowanie wyników.

Rys. 4 przedstawia tabelę zapytań HTTP podzieloną na strony. Na pojedynczej stronie znajduje się co najwyżej 100 wierszy tabeli. Ponad tabelą znajduje się wskaźnik bieżącej strony.

Jeśli wszystkich rekordów jest 385, to wyniki prezentujemy w postaci czterech stron, na których znajdują się rekordy:

strona pierwsza: od   1 do 100
strona pierwsza: od 101 do 200
strona pierwsza: od 201 do 300
strona pierwsza: od 301 do 385

W celu ułatwienia nawigacji, nad tabelą umieszczamy wskaźnik bieżącej strony oraz hiperłącza do stron: pierwszej, poprzedniej, następnej oraz ostatniej.

Rozwiązanie zadania 2

Ogólna struktura rozwiązania pozostaje bez zmian. Aplikacja składa się z:

• klasy DBA (plik dba.class.php),
• skryptu głównego index.php,
• plików szablonu .tpl.

Modyfikacji poddajemy po pierwsze klasę DBA. Dodajemy metody {stala}DBA_thost_count_pages(){/stala}, {stala}DBA_thost_is_valid_page(){/stala} oraz {stala}DBA_thost_count_records(){/stala} oraz modyfikujemy metodę {stala}DBA_thost(){/stala}, wzbogacając ją o dodatkowy parametr ustalający numer strony zwracanych wyników (listing 2).

public function DBA_thost($ANumerStrony)
{
    $tmp    = DBA_LICZBA_REKORDOW_NA_STRONIE;
    $offset = ($ANumerStrony - 1) *
              DBA_LICZBA_REKORDOW_NA_STRONIE;

    $q = \"
        SELECT
            h.thost_id as no,
            h.thost_id,
            h.host,
            COUNT(q.thttprequest_id) as ile
        FROM
            thost as h,
            thttprequest as q
        WHERE
            h.thost_id = q.thost_id
        GROUP BY
            q.thost_id
        ORDER BY
            ile DESC, host ASC
        LIMIT
            $offset, $tmp
    \";

    $w = $this->Fdba->getAll($q);

    if (DB::isError($w)) {
        die(__LINE__ . \', \' . $w->getMessage());
    } else {
        $ile = count($w);
        for ($i = 0; $i < $ile; $i++) {
            $w[$i][\'no\'] =
                ($ANumerStrony - 1) *
                DBA_LICZBA_REKORDOW_NA_STRONIE +
                $i + 1;
        }
        return $w;
    }
}

W skrypcie index.php skomplikuje się etap drugi, w którym dokonujemy walidacji zmiennych zawartych w tablicy {stala}$_GET{/stala}. Ponadto przypadki instrukcji {stala}switch{/stala} będą zawierały instrukcje ustalające zmienne konieczne do wskaźnika aktualnie wybranej strony wyników:

case 4:
    $dane            = $db->DBA_thost($_GET[\'id2\']);
    $liczba_rekordow = $db->DBA_thost_count_records();
    $liczba_stron    = $db->DBA_thost_count_pages();
    $s->assign(\'dane\',            $dane);
    $s->assign(\'liczba_rekordow\', $liczba_rekordow);
    $s->assign(\'liczba_stron\',    $liczba_stron);
    $strona_act  = $_GET[\'id2\'];
    $strona_next = $_GET[\'id2\'] + 1;
    $strona_prev = $_GET[\'id2\'] - 1;
    if ($strona_next > $liczba_stron) {
        $strona_next = false;
    }
    if ($strona_prev == 0) {
        $strona_prev = false;
    }
    $s->assign(\'strona_act\',   $strona_act);
    $s->assign(\'strona_next\',  $strona_next);
    $s->assign(\'strona_prev\',  $strona_prev);
    break;

Ostatnią modyfikacją jest wzbogacenie szablonu o wskaźnik aktualnie wybranej strony.

Uwagi do zadania 2

Warto zwrócić uwagę na rozwiązanie stronicowania wyników. Po pierwsze w pliku dba.class.php definiujemy zmienną:

define(\'DBA_LICZBA_REKORDOW_NA_STRONIE\', 100);

Przyjmujemy, że strony wyników są numerowane od 1. Jeśli na przykład wyników jest 27, a liczba rekordów na stronie wynosi 10, to dostępnymi stronami są:

str. 1 === rekordy od  1 do 10
str. 2 === rekordy od 11 do 20
str. 3 === rekordy od 21 do 27

W zapytaniu SELECT podajemy klauzulę LIMIT:

SELECT
...
LIMIT
    $offset, $tmp

przy czym zmienne {stala}$offset{/stala} oraz {stala}$tmp{/stala} wyznaczamy następująco:

$tmp    = DBA_LICZBA_REKORDOW_NA_STRONIE;
$offset = ($ANumerStrony - 1) * DBA_LICZBA_REKORDOW_NA_STRONIE;

W zwróconych wynikach znajduje się jedna zbędna kolumna identyfikatorów oznaczona aliasem no:

SELECT
    h.thost_id as no,
    h.thost_id,
...

W kolumnie tej umieszczamy później - pętlą {stala}for{/stala} - numery rekordów.

Podobnie jak i w przypadku zadania pierwszego, na zakończenie usuwamy redundancje w kodzie.

Zadanie 3

Poprzednie rozwiązanie wzbogacamy o informacje szczegółowe dotyczące wybranego rekordu.

W rozwiązaniu tym każda pozycja tabeli hostów ma stanowić hiperłącze do strony przedstawiającej wszystkie zapytania, które nadeszły z danego hosta.

Podobnie w przypadku pozostałych tabel. Czyli tabela adresów URL zawiera wszystkie adresy URL, które wystąpiły w pliku log, posortowane malejąco liczbą żądań. Każdy adres jest hiperłączem do strony prezentującej wszystkie zapytania, które dotyczyły jednego konkretnego adresu URL.

Tabela metod protokołu HTTP zawiera wszystkie metody posortowane malejąco liczbą żądań. Każda metoda jest hiperłączem do strony przedstawiającej wszystkie zapytania, które dotyczyły wybranej metody.

Rys. 5 przedstawia tabelę wszystkich żądań, które dotyczyły hosta ege34.neoplus.adsl.tpnet.pl. Wyniki ze względu na ich ilość zostały poddane stronicowaniu (liczba wierszy na stronie: 5).

Rozwiązanie zadania 3

Klasę DBA wzbogacamy o metody sparametryzowane nazwą tabeli. Na przykład metoda:

DBA_X_queries($AId, $ANumerStrony, $ATable)

zwraca stronicowaną tabelę zapytań HTTP dotyczących wybranego rodzaju danych o podanym identyfikatorze. Na przykład trzecią stronę wyników zapytań dotyczących adresu URL o identyfikatorze 17 ustalimy wywołaniem:

DBA_X_queries(17, 3, \'turl\')

Zaś siódmą stronę zapytań dotyczących hosta o identyfikatorze 51 wyznacza zapytanie:

DBA_X_queries(51, 7, \'thost\')

Powyższa parametryzacja umożliwia znaczne zmniejszenie liczby metod klasy DBA. Stosując parametryzację nazwą tabeli przygotowujemy jedynie pięć metod:

DBA_X_queries($AId, $ANumerStrony, $ATable)
DBA_X_count_records($AId, $ATable)
DBA_X_count_pages($AId, $ATable)
DBA_X_is_valid_page($AId, $APage, $ATable)
DBA_X_is_valid_id($AId, $ATable)

W przeciwnym razie należałoby przygotować 5 x 7 = 35 metod (po jednej dla każdej tabeli!).

Uwagi do zadania 3

Wadą podanego rozwiązania jest redundancja kodu polegająca na tym, że niemal identyczna instrukcja {stala}if{/stala} występuje w kodzie trzykrotnie: raz przy walidacji danych, drugi raz jako switch, zaś trzeci - w szablonie.