Miesiąc: grudzień 2008

Sortowanie względem pola nie będącego w DISTINCT ON

Problem:
Chcemy wyświetlić jedynie unikalne rekordy względem konkretnego pola/pól i posortować innym polu. W naszym przykładzie chcemy otrzymać zestawienie id_user – ostatnie logowanie

Posiadane dane:
Tabela z datami logowań użytkowników aplikacji w tabeli postaci:

CREATE TABLE logs(

	id SERIAL PRIMARY KEY, -- unikalny klucz główny

	id_user INTEGER, -- klucz obcy tabeli użytkowników

	date TIMESTAMP -- data logowania

);

Przykładowe rekordy:

INSERT INTO logs(id_user,date) VALUES(1,'2008-01-01');

INSERT INTO logs(id_user,date) VALUES(1,'2008-01-01');

INSERT INTO logs(id_user,date) VALUES(4,'2008-01-01');

INSERT INTO logs(id_user,date) VALUES(2,'2008-01-01');

INSERT INTO logs(id_user,date) VALUES(3,'2008-04-01');

INSERT INTO logs(id_user,date) VALUES(7,'2008-04-01');

INSERT INTO logs(id_user,date) VALUES(1,'2008-05-01');

INSERT INTO logs(id_user,date) VALUES(4,'2008-05-01');

INSERT INTO logs(id_user,date) VALUES(5,'2008-06-01');

INSERT INTO logs(id_user,date) VALUES(5,'2008-08-01');

INSERT INTO logs(id_user,date) VALUES(7,'2008-10-01');

Błędne zapytanie nr 1:
W pierwszym momencie wielu początkujących programistów pomyśli o zapytaniu:

SELECT

 DISTINCT ON (id_user)

 *

FROM

 logs

ORDER BY

 date DESC;

Niestety nie jest to poprawne zapytanie, postgres wyrzuci błąd:

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Oznacza to, że pola, które zawiera klauzula ORDER BY muszą się znaleźć na początku klauzuli DISTINCT ON.
W tym przypadku poprawne zapytanie musiało by zawierać DISTINCT ON (date, id_user).
Niestety wtedy nie dostaniemy pożądanego rezultatu (unikalność po kolumnie id_user) gdyż badana będzie
unikalność względem dwóch pól.

Błędne zapytanie nr 2:

SELECT

 *

FROM

 (SELECT

 	DISTINCT ON (id_user)

 	*

 FROM

 	logs

) AS sub

ORDER BY

 sub.date DESC;

Idąc tropem unikalności można użyć podzapytania, które zwraca unikalne względem id_user rekordy a dopiero później, zwrócone przez nie rekordy posortować. Powyższe zapytanie wykona się i zwróci pewne rezultaty.

id id_user date
11 7 2008-10-01 00:00:00
9 5 2008-06-01 00:00:00
8 4 2008-05-01 00:00:00
5 3 2008-04-01 00:00:00
2 1 2008-01-01 00:00:00
4 2 2008-01-01 00:00:00

Niestety nie będą one do końca poprawne gdyż podzapytanie DISTINCT pozostawia pierwszy napotkany rekord zawierający unikalną wartość id_user. Zatem jeżeli dane nie są posortowane względem id_user i daty chronologicznie to uzyskamy wyniki nieprawidłowe. W naszym przypadku id_user=1 została zwrócona data=2008-01-01 gdyż jest ona bliżej początku niż rekord z datą=2008-05-01.

Rozwiązanie:

SELECT

 *

FROM

 (SELECT

 	DISTINCT ON (sub2.id_user)

 	sub2.*

 FROM

 	(SELECT

 		*

 	FROM

 		logs

 	ORDER BY

 		id_user ASC,

 		date DESC

 	) AS sub2

) AS sub

ORDER BY

 sub.date DESC;

W podzapytaniu sub2 sortujemy dane po id_user i date malejąco tak aby każdy id_user na czele miał rekord z datą najpóźniejszą. Takie dane przekazujemy zapytaniu wcześniejszemu, które najpierw wyciąga rekordy unikalne względem kolumny id_user a następnie tak ograniczony zbiór sortuje względem kolumny date. Jako że dostarczyliśmy dane posortowane otrzymane rezultaty są zgodne z prawdą:

id id_user date
11 7 2008-10-01 00:00:00
10 5 2008-08-01 00:00:00
7 1 2008-05-01 00:00:00
8 4 2008-05-01 00:00:00
5 3 2008-04-01 00:00:00
4 2 2008-01-01 00:00:00

Zapytanie grupujące minimalnne wartości w grupie danych

Problem:
Chcemy wyświetlić miesięczne zestawienie liczby nowo zarejestrowanych
użytkowników mając jedynie daty ich logowań.

Posiadane dane:
Tabela z datami logowań użytkowników aplikacji w tabeli postaci:

	CREATE TABLE logs(
		id SERIAL PRIMARY KEY, -- unikalny klucz główny
		id_user INTEGER, -- klucz obcy tabeli użytkowników
		date TIMESTAMP -- data logowania
	);

Przykładowe rekordy:

INSERT INTO logs(id_user,date) VALUES(1,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(1,'2008-01-05');
INSERT INTO logs(id_user,date) VALUES(4,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(2,'2008-01-01');
INSERT INTO logs(id_user,date) VALUES(3,'2008-04-01');
INSERT INTO logs(id_user,date) VALUES(7,'2008-04-01');
INSERT INTO logs(id_user,date) VALUES(1,'2008-05-01');
INSERT INTO logs(id_user,date) VALUES(4,'2008-05-01');
INSERT INTO logs(id_user,date) VALUES(5,'2008-06-01');
INSERT INTO logs(id_user,date) VALUES(5,'2008-08-01');
INSERT INTO logs(id_user,date) VALUES(7,'2008-10-01');

Rozwiązanie:
Rejestracje można utożsamić z pierwszymi logowaniami, zatem:
Najpierw grupujemy dane według użytkownika i dla każdego id_user znajdujemy najwcześniejszą datę logowania. Następnie otrzymane dane grupujemy po roku i miesiącu.

Realizuje to podwójnie grupujące zapytanie:

SELECT 
	extract(year from date) || '-' || extract(month from date) AS month, 
	count(*) AS amount 
FROM 
	logs 
WHERE date IN (
	SELECT 
		min(date) 
	FROM 
		logs 
	GROUP BY 
		id_user 
) 
GROUP BY 
	extract(year from date) || '-' || extract(month from date)
ORDER BY
	amount DESC;

Można też ciut lepiej zoptymalizować zapytanie i operować bezpośrednio na wynikach zwróconych
przez podzapytanie:

	
SELECT 
	extract(year from sub.date) || '-' || extract(month from sub.date) AS month, 
	count(*) AS amount 
FROM 
 (
	SELECT 
		min(date) AS date
	FROM 
		logs 
	GROUP BY 
		id_user 
) AS sub
GROUP BY 
	extract(year from sub.date) || '-' || extract(month from sub.date)
ORDER BY
	amount DESC;

W przypadku gdy zapytanie to będzie wykonywane często warto założyć index na kolumne z datą:

CREATE INDEX date_idx ON logs(date);

FirePHP – przyjazne debug’owanie aplikacji internetowych

Debugowanie to poważny problem programistów

Bez względu na to jakimi dobrymi programistami jesteśmy nigdy nie ustrzeżemy się błędów. To normalne, błędy popełnia każdy, różnica pomiędzy dobrym programistą a złym polega m. in. na szybkości wykrycia błędnego kodu i naprawienia go.

Mówiąc o błędnym kodzie nie mam na myśli jedynie błędów syntaktycznych, ale również błędów logicznych, które nie powodują widocznych error’ów a jedynie błędne działanie aplikacji. Takie błędy są dużo trudniejsze do wykrycia.

Aby wykryć błędny kod niezbędne jest zdebugowanie zmiennych, obiektów, zasobów, które wykorzystywane są w tworzonej przez nas aplikacji. Brzmi banalnie, jednak w rzeczywistości nie jest to takie łatwe gdyż:

  • po pierwsze trzeba wstawić w odpowiednie miejsca funkcje var_dump() lub print_r()
  • po drugie funkcje te nie wyświetlają przejrzyste i łatwe do zinterpretowania dane
  • po trzecie debug’owanie na ekran może spowodować błędy aplikacji, a debugowanie do pliku jest niewygodne w odczycie (szczególnie w przypadku aplikacji flash’owych lub AJAX’owych)
  • po czwarte zdarza się, że format zwracanych danych uniemożliwia swobodne dodawanie własnych danych do debugowania (np. XML, JSon).
  • po piąge po skończeniu debugowania trzeba usunąć wszystkie instrukcje debugujące

Rozwiązaniem problemu debugowania jest FirePHP

Dużo tych wad… całe szczęście jest na to rozwiązanie. FirePHP to darmowa, open source’owa biblioteka, która wraz z pluginem do Firebug’a idealnie sprawdza się w roli debugera.

FirePHP debuguje dane w konsoli Firebug’a, informacje do debugowania są umieszczone w specjalnych nagłówkach HTTP przez co 'output’ aplikacji pozostaje w nienaruszonym stanie. Rewelacja prawda?

Instalacja firePHP

Wymagania FirePHP to PHP 5.2+, Firefox z pluginem Firebug.

  • Instalujemy plugina do firefox’a Firebug
  • Instalujemy plugina do Firebug’a FirePHP
  • Ściągamy źródła FirePHP, warto zauważyć, że na stronie dostępne są informacje, jak zintegrować bibliotekę z wieloma znanymi frameworkami (CakePHP, CodeIgniter, Drupal, Kohana, ExpressionEngine, PRADO, Symfony, TYPO3, Zend Framework).
  • Integrujemy FirePHP z naszą aplikacją
  • Odpalamy stronę aplikacji w Firefox z uruchomionymi plugin’ami Firebug i FirePHP
  • Klikamy na ikonę Firebug’a a następnie przechodzimy do konsoli. W konsoli powinny być zaprezentowane zdebugowane zmienne naszej aplikacji.
  • W przypadku problemów przydać się mogą: mini tutorial how to, forum dyskusyjne o FirePHP.

Przykład działania

Pod adresem http://blog.adiasz.pl/examples/firephp/ stworzyłem prosty skrypt AJAX’owy łączący się z bazą i wykonujący prościutkie operacje. Jest zintegrowany z FirePHP, zachęcam do testów. Naprawdę warto! (Oczywiście musicie najpierw zainstalować plugin FireBug i FirePHP). Wszystkie pliki wykorzystane w tym przykładzie dostępne są tutaj.

Przykład powinien zaprezentować coś w stylu:

Na koniec, bardzo ważna informacja: po opublikowaniu projektu musicie wyłączyć debugowanie FirePHP poprzez dyrektywę:

$fp = FirePHP::getInstance( true );
$fp->setEnabled ( false );

W przeciwnym razie każdy internauta posiadający zainstalowany ten plugin będzie miał dostęp do niebezpiecznych z punktu bezpieczeństwa danych. Najlepiej w konfigu aplikacji sterować to poprzez zmienną typu boolean $development = true/false. Samych instrukcji debugujących nie trzeba kasować gdyż nie wiadomo kiedy się jeszcze przydadzą.

ps. FirePHP dostępne jest także dla innych niż PHP języków programowania aplikacji webowych: ASP, Python, Ruby.

Darmowe narzędzia dla webmasterów: statystyki serwisu

Ostatnio w ramach optymalizacji czasu i automatyzacji wykonywanych przez zemnie zadań stworzyłem mini aplikację sprawdzającą statystyki moich serwisów w wyszukiwarkach.

Aktualnie sprawdzam: Google PR, Google link, Google site, Yahoo link, Yahoo site, MSN site. Monitoringu pozycji słów kluczowych na razie nie robiłem, gdyż jest wiele tego typu dobrych i darmowych sprawdzaczy na necie – więc szkoda czasu.

Aplikacja działa tak:
* skrypty sprawdzające statystyki wywoływane są z automatycznie z cron’a, dane zapisują w bazie
* po zalogowaniu prezentowana jest lista najnowszych statystyk
* generowane są dzienne wykresy poszczególnych statystyk serwisów

Prościutkie, teraz zamiast sprawdzać ręcznie tysiące parametrów wystarczy się zalogować do panelu. Wyniki zostaną zaprezentowane szybki i w przejrzysty sposób.

Dla szerokiego internetu udostępniłem wersję uproszczoną – możliwość sprawdzenia wyżej wymienionych statystyk dla podanego adresu: zapraszam na darmowe narzędzia dla webmasterów. Nic odkrywczego, ale może komuś się przyda.

Aplikacja została napisana we framework’u: Kohana, o którym postaram się napisać niedługo, a już teraz polecam się z nim zapoznać.