“Rekurencja” w funkcjach/procedurach MySQL

Odzwyczajony od blogowania, zaabsorbowany przez niekorzystnego dla planet i blogosfery stanu rzeczy Social Media, postanowiłem wrzucić kolejny wpis, w podzięce za porady uzyskane z blogów kolegów z branży. Ostatnio moim zadaniem było zaprojektowanie struktury tabel bazy danych, która wraz z drzewem z zagłębieniem kategorii, ma przechowywać dane na temat cen za ogłoszenia publikowane w tych kategoriach.

Założenia wstępne wyglądały następująco:

1. Każda kategoria może mieć rodzica, ale nie musi.
   Jak komu wygodnie, postanowiłem wybrać rozwiązanie z polem category_parent int(11) UNSIGNED, domyślnie NULL (bez rodzica).
2. Kategorie mogą mieć ustaloną cenę za opublikowanie ogłoszenia, ale nie muszą.
   Dodałem pole category_data_price float(6,2) UNSIGNED, domyślnie NULL (niezdefiniowana).
3. Kategorie, które nie mają zdefiniowanej ceny, a mają rodzica, przejmują cenę rodzica w zagłębieniu do nieskończoności. W przypadku, kiedy cena jest niezdefiniowana również dla rodziców, cena stanowi NULL.

Potrzebne mi było zapytanie, które zwróci mi zwyczajnie listę wszystkich kategorii wraz z cenami, uwzględniając zagłębienia, w jakich dana kategoria się znajduje. Podejścia składowania cen są dwa:

1. Można zapisywać tylko cenę kategorii, która jest dla niej zdefiniowana, a przy wyciąganiu danych rekurencyjnie sprawdzać procedurą/funkcją cenę rodzica, jeżeli jest niezdefiniowana. I tak w kółko do przypadku, gdy któraś z kategorii będzie już miała zdefiniowaną cenę, bądź nie będzie się już gdzie zagłębić (kategoria nie ma rodzica).
2. Można zapisać dwie ceny dodając pole dodatkowe, które przy będzie przechowywało wartość ceny, biorąc ją przy sprawdzaniu cen tylko przy zapisie rekordu do bazy danych.

Sposób drugi wydaje się być bardziej rozsądny w przypadku większej ilości zapytań podejmujących dane z bazy. Pierwszy odwrotnie – częściej wpisujemy coś do bazy. Z początku wybrałem pierwszy sposób, przy założeniu, że ceny będą generowane tylko w panelu administracyjnym, zatem zapytanie nie będzie wykonywane często. Niestety, moje myślenie jest bardziej abstrakcyjne, nastawione na elastyczność rozwiązań, w przyszłości cel istnienia bazy może się zmienić.

Tak czy siak, dla obu rozwiązań, trzeba stworzyć procedurę sprawdzającą ceny po rodzicach. Wygląda mniej więcej tak:

CREATE FUNCTION AnnouncementsCategoriesGetPrice(iCategoryId INT(11)) RETURNS FLOAT(6,2)
BEGIN
DECLARE iResult FLOAT(6,2);
DECLARE iPointer INT(11);
 
SET iPointer = iCategoryId;
SET iResult  = (SELECT category_data_price FROM cms_announcements_categories WHERE category_id = iPointer);
 
WHILE (iResult IS NULL AND iPointer IS NOT NULL) DO
SET iResult  = (SELECT category_data_price FROM cms_announcements_categories WHERE category_id = iPointer);
SET iPointer = (SELECT category_parent FROM cms_announcements_categories WHERE category_id = iPointer);
END WHILE;
 
RETURN iResult;
END

Stosować ją można dla SELECT’u kategorii lub do pozyskania wartości pola tymczasowego przy zapisie do bazy.

Mam nadzieję, że komuś się przyda.

jQuery Animate i Easing

Nie można kwestionować faktu, że jQuery.animate() jest jednym z najbardziej potężnych narzędzi jQuery. Służy on do animowania atrybutów CSS (czyli zmiany ich wartości w czasie od obecnego stanu A do definiowanego stanu B). Najprostszą implementacją jQuery Animate jest podanie zbioru atrybutów CSS, które mają ulec zmianie oraz czasu, w jakim ta zmiana ma nastąpić. Nie będę się zagłębiał w najprostsze przykłady użycia, są one dostępne w oficjalnej dokumentacji jQuery.

Należy pamiętać, że dzięki jQuery jesteśmy w stanie nie tylko płynnie zmieniać kolory, wielkość czcionki, obramowanie, ale także pozycje elementów, nadając stronie dynamicznego kształtu. Domyślnym sposobem animowania (easing) jest płynne przechodzenie. Istnieje natomiast sposób na zmianę adaptera animowania. Robert Penner – autor pluginu jQuery Easing dostarczył nam niewiarygodnie efektowne i proste w implementacji narzędzie. Na oficjalnej stronie pluginu można znaleźć wiele przykładów animacji, które dostarcza nam dodatek. Efekty widoczne są zwłaszcza przy animowaniu pozycji i wymiarów obiektu, ale następują także w przypadku zmiany koloru – czyli są aplikowane do zmiany stanu każdego z atrybutów CSS.

Dziś postaram się pokazać efekty, jakie można uzyskać za pomocą jQuery Animate rozszerzonego o jQuery Interface oraz Easing.

• Przykład różnego rodzaju easingu.

Pierwszym krokiem jest wygenerowanie własnej biblioteki jQuery Interface. Dzięki generatorowi, jesteśmy w stanie ściągnąć tylko te części Interface, które są nam de facto potrzebne, zmniejszając jednocześnie ilość kodu. Klikamy ‘deselect all components’, a w sekcji Effects wybieramy efekty, których będziemy używać. Mnie w tej chwili interesuje Bounce i Slide. W paczce otrzymamy wersję deweloperską (z wcięciami) oraz minified, gotową do publikacji na serwerze.

Do wykorzystania efektu slideowania a’la iPhone (elastyczne odbicie od krawędzi ściany ekranu) sprowadza się drobny kawałek kodu, w którym istotnym jest parametr easing:

$('#example').animate({ left: 500 }, { duration: 1000, easing: 'easeOutElastic' })

Na pewno komuś się przyda.

Chmura tagów Tagcloud w PHP

Ostatnio byłem zobligowany napisać nową klasę tagów do mojego projektu Sypacz.pl, która de facto zachowała stare API, lecz rozszerzyła swoje funkcjonalności, więc w kodzie projektu nie było wielu zmian. Wena spowodowała to, że zacząłem pisać kod od zera.

Cały problem polega na tym, aby napisać na tyle elastyczną klasę tagów, która przyjmie nam zestaw danych, a następnie zaprezentować ją w formie chmury, czym zaopiekuje się arkusz stylów CSS:

Nazwa tagu => Ilość występowań

Parę osób mnie pytało, jak wyciągnąć takie informacje z bazy danych:

SELECT tag_name, COUNT(tag_name) AS tag_times FROM tags GROUP BY tag_name ORDER BY tag_times LIMIT 1, 50

Uwaga! Zaprezentowane wyżej zapytanie jest przykładowe, nieoptymalne, a jedynie służące do testowania chmur tagów na małych, testowych bazach danych. Optymalna implementacja struktur tagów w bazie danych dla większych projektów została opisana we wpisie MySQL tags.

Wykorzystałem obiekt Vframe_Attribute, aby ustandaryzować komponent względem pozostałych w moim frameworku. Jeżeli ktoś nie chce używać obiektu Attribute, może w prosty sposób przekształcić klasę tagów, otrzymując ten sam efekt, deklarując tylko atrybut chroniony protected $_aAttributes = array();. Temat chmury tagów wydaje mi się na tyle trywialny, że nie ma się co nad nim zbyt wiele rozwodzić, zamieszczę tylko klasę i opiszę krótko w przykładach jej możliwości.

• Vframe_Tagcloud – klasa tagów,
• Vframe_Attribute – pomocnicza klasa atrybutów dla stosu $_aAttributes, dziedziczenie można usunąć i zadeklarować atrybut samemu.

Aby stworzyć nowy obiekt tagów, po prostu wywołujemy konstruktor:

$oCloud = new Vframe_Tagcloud();

Konstruktor nie przyjmuje żadnych argumentów, wiec możemy od razu przejść do podawania obiektowi tagów. W tym miejscu warto nadmienić, że każdy znak jest rozróżniany (ze względów elastycznych), więc jeżeli chcesz, aby Nazwatagu oraz nazwatagu były rozpoznawane jako jeden klucz, wypadałoby użyć funkcji strtolower lub mb_strtolower (dla Multibyte Strings):

foreach($aDataTags as $iKey => $aRow)
  $oCloud->add(strtolower($aRow['tag_name']), $aRow['tag_times']);

Najistotniejszą częścią klasy jest sposób renderowania chmury, które może działać w dwóch trybach:

• Tryb prosty zwraca nam nazwę tagu oraz jego wagę po przeliczeniu w formie liczby.
• Tryb zaawansowany zwraca nam nazwę tagu oraz tablicę z danymi:
  • level – waga tagu po przeliczeniu,
  • count – ilość występowań, taka jaką podaliśmy,
  • count_percentage – informacja, w jakiej procentowej części ilości występowań znajduje się tag, przyjmując za 100% tag, który występuje najczęściej.

Aby w prosty sposób wyrenderować chmurę tagów, używamy poniższego przykładu:

$aDataTagsRender = $oCloud->render();

Najczęściej używa się trybu prostego. Oba tryby są dalej rozbudowane, bowiem mamy możliwość zdefiniowania zakresu i dokładności wag tagów. Domyślnie wagi tagów zawierają się pomiędzy 1, a 10. Możemy na przykład przyjąć, że najmniejszą wagą jest liczba 3, największą 5, a precyzja wag tagów to 2 miejsca po przecinku:

$aDataTagsRender = $oCloud->render(3, 5, 2);

W celu uniknięcia precyzji po przecinku (chcemy otrzymać liczby całkowite), ustawimy precyzję na 0.

Aby wywołać tryb zaawansowany, musimy podać 4 argument dla metody render() i ustawić go na true.

$aDataTagsRender = $oCloud->render(1, 5, 0, true);

Warto w tym miejscu nadmienić, że tagi mogą nie być posortowane alfabetycznie (co ma miejsce podczas tworzenia chmury tagów). Wystarczy wywołać funkcję ksort (key sort).

Finalny przykład używania klasy tagów, celem wywołania klas CSS level_X, gdzie X to liczba całkowita z zakresu od 1 do 10, resztę robi CSS (kolorowanie, nakładanie rozmiaru):

if(count($aDataTags)) {
 
$oCloud = new Vframe_Tagcloud();
 
foreach($aDataTags as $iKey => $aRow)
  $oCloud->add(strtolower($aRow['tag_name']), $aRow['tag_times']);
 
$aDataTags = $oCloud->render(1, 10, 0);
ksort($aDataTags);
 
foreach($aDataTags as $sTag => $iTag)
  echo '<a class="level_' . $iTag . '" href="' . $this-&gt;route('tag', $sTag) . '">' . $sTag . '</a>';
 
}

MySQL: remove duplicate entries/rows

Usuwając coś permanentnie z bazy danych musimy być bardzo ostrożni, bowiem przywrócenie danych jest bardzo trudne, czasem niemożliwe. Podstawową strukturę bazy danych powinno się budować na samym początku tworzenia aplikacji, z biegiem czasu rozbudowywać ją, ale unikać przebudowywania. Niestety są przypadki, gdzie trzeba przebudować jedną rzecz, co powoduje zmianę w wielu warstwach nie tyle aplikacji, co strukturze bazodanowej.

Dziś postaram się opisać, jakie kroki trzeba wykonać, aby bezpiecznie usunąć zdublowane rekordy z bazy danych nie tracąc żadnych danych:

1. Tworzymy dwie kopie bazy danych lub tabel, na których będziemy pracowali. Najlepiej, aby pracować na drugiej kopii, nigdy na oryginale, a potem wdrożyć zmiany z drugiej kopii na oryginał. Przezorny zawsze ubezpieczony.
2. Analiza danych w tabeli. Musimy dokładnie wiedzieć jakie są relacje między tabelami, kiedy występują JOIN’y itp. Jeżeli rekordy są zdublowane, a posiadają ustalony ID, do których odwołuje się inny rekord z sąsiedniej tabeli, trzeba będzie w niej zmienić ID rekord zdublowanego na ID “substytuta”, bądź takiego rekordu, który nie spowoduje zmian w serwisie.
3. Wykonanie operacji usunięcia zdublowanych rekordów.

Po wykonaniu kroku pierwszego zabieramy się za kolejny. Jest to najważniejszy moment naszych operacji. Aby ułatwić zrozumienie problemu, podam przykład z życia. Aplikacja posiadała poważny błąd, który umożliwiał zdublowanie użytkowników, ściślej: można było zdublować username. Za każdym razem, gdy użytkownik się logował i pisał komentarze, był ich właścicielem, ale comment_author posiadały różne ID tego samego użytkownika. Zaraz po skopiowaniu bazy danych spróbowałem przepisać ID autorów komentarzy na pierwszy rekord identyfikujący użytkownika, jaki istnieje w tabeli użytkowników. Skonstruowałem zapytanie:

UPDATE cms_comments
JOIN cms_members AS user_original ON(user_original.user_id = comment_author)
SET comment_author = (
  SELECT user_first.user_id FROM cms_members AS user_first
  WHERE user_first.user_name = user_original.user_name
  ORDER BY user_first.user_id ASC LIMIT 0, 1)

Usunięcie zdublowanych użytkowników było już tylko formalnością. Teraz się okaże, dlaczego zależało mi na wyciągnięciu dokładnie pierwszego rekordu reprezentującego “unikalnego” użytkownika: poniższe zapytanie (ALTER IGNORE TABLE ADD UNIQUE) usunie wszystkie kolejne rekordy oznaczone jako duplicated:

ALTER IGNORE TABLE cms_members ADD UNIQUE INDEX(user_name);

Krótki komentarz z manuala do ALTER TABLE:

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key, The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

Pisząc ostatnie posty związane z bazami danych, mam nadzieję, że komuś się przydadzą.

Przypadki w MySQL – CASE WHEN THEN ELSE END

Podobnie jak w PHP, baza danych MySQL ma odpowiednik if, czyli przypadków (inaczej serii warunków, instrukcji warunkowych). Różnicą między implementacją CASE‘a w MySQL i ifa PHP jest to, że baza danych zwraca konkretną wartość z case’a, a nie wykonuje dowolnej ilości dowolnych akcji.

CASE Syntax:

Najprostsza struktura CASE’aprzedstawia się nastepująco:

CASE WHEN [conditions] THEN ... ELSE ... END

Składnia powinna rozpocząć się słowem kluczowym CASE, a zakończyć END. Pomiędzy znajdują się warunki WHEN oraz operacja zwrócenia odpowiedniej wartości, która po nich następuje THEN (mamy możliwość uwzględnić nieskończenie wiele warunków). Jeżeli żaden warunek nie zostanie spełniony możemy użyć opcjonalnie ELSE.

Przykłady z życia.

Wyobraźmy sobie, że mamy posortować listę aukcji przedmiotów na Allegro od najtańszych, do najdroższych. Należy założyć, że są 2 typy aukcji: kup teraz i licytacja. Pole licytacji w bazie danych zawiera największą zaproponowaną kwotę przez użytkowników w procesie licytacji, a cena kup teraz ustalana jest przez sprzedającego. Są to dwa różne pola w bazie danych, a jedno kryterium sortowania, dlatego trzeba scalić cenę w jedną, wybierając odpowiednią. Musimy przewidzieć sytuację, w której aukcja jest typu kup teraz oraz licytacji, wówczas jeżeli najwyższa oferta jest większa od ceny kup teraz, wówczas wybieramy pole z największa propozycją:

SELECT (
  CASE
    WHEN (auction_type = 'bidding' OR auction_price_bid > auction_price_buynow)
      THEN auction_price_bid
    ELSE auction_price_buynow
  END) AS auction_price

Stworzyliśmy pole auction_price, po którym można sortować aukcje od najtańszej do najdroższej i na odwrót.

Mam nadzieję, że krótki wpis przyda się początkującym. Nic więcej nie trzeba opisywać, temat wydaje się co najmniej trywialny.

MVC – Model

Powstało masę artykułów na temat MVC, temat staje się naprawdę oklepany. Postanowiłem zebrać wszystkie informacje w jedno miejsce i streścić je w jednym artykule uzupełniając go o informacje, które nabyłem z własnego doświadczenia oraz zwracając uwagę na najistotniejsze informacje.

Czym jest model

Model to jedna z warstw wzorca projektowego MVC, który odpowiada logikę biznesową, czyli pozyskiwanie oraz modelowanie danych pozyskanych ze źródła danych. Na samym wstępie brzmi to bardzo abstrakcyjnie. W myśl architektury MVC, dostęp do modelu powinien mieć tylko kontroler, a w żadnym wypadku widok. Dodatkowo model musi pobrać i modelować dane w taki sposób, aby można było go ewentualnie wymienić bez jakiejkolwiek ingerencji w kontroler, a co za tym idzie – widok. Niezależnie od tego, z jakiego źródła informacji korzysta (pliki tekstowe, bazy danych, pliki XML)  kontroler powinien otrzymać maksymalnie zbliżone dane podczas wymiany źródła informacji.

Model != baza danych

Często spotykam się z definicją modelu jako źródłem połączenia i wykonywania zapytań do serwera bazy danych. Otóż nie jest to prawdą. Według ideologii MVC model powinien być jedynie pośrednikiem między warstwą aplikacji przeznaczoną do połączenia do bazy danych, wykonywania zapytań itp., a kontrolerem. Dodatkowo powinien pomóc kontrolerowi w zbudowaniu zapytania do źródła informacji (pobranie danych na podstawie kryteriów), zmodelować je i zwrócić. Dlaczego model nie jest połączeniem do bazy danych? Jeżeli model potraktujemy jako pośrednika między kontrolerem a źródłem danych, ma on prawo wybrać dowolny sposób uzyskania żądanych informacji. Wcale nie oznacza to, że model musi używać baz danych, ale może użyć plików XML lub API udostępniane przez konkretny serwis (np. YouTube)

Wymienialność modeli i modelowanie danych

Modelowanie informacji jest to dostosowanie ich do użytku przez kontroler. Zazwyczaj jest to przekazywanie informacji w postaci tablic, wartości logicznych, liczb i ciągów znaków. Przykładem może być pobieranie informacji z bazy danych. Kontroler de facto nie wie skąd są pobierane dane, wie to tylko model, otrzymuje suche informacje. Jak rozumieć modelowanie danych przy projektowaniu aplikacji? Wyobraźmy sobie sytuację, że zmieniamy źródło informacji z bazy danych na pliki XML. W tym przypadku kontroler powinien otrzymać rekordy danych jako tablica o tych samych kluczach i tych samych typach danych, jak miało to miejsce przy używaniu bazy danych. Wymiana modelu odbywa się bez ingerowania w kontroler.

Przykłady modeli

Najpopularniejszym sposobem pozyskania informacji jest połączenie do bazy danych i pobieranie (reprezentowanie) ich na różnoraki sposób. Doskonale wyjaśnia to tekst znajdujący się w wikipedii:

Frameworki MVC do operacji na bazach danych używają modeli i mapowania relacyjno-obiektowego, ORM (ang. object-relationship mapping) – w Railsach jest to ActiveRecord, w Catalyscie np. DBIx::Class, a framework Spring w Javie używa Hibernate. Zwykle jest też możliwe użycie baz danych przez bezpośrednie zapytania SQL. Użycie modeli upraszcza typowe operacje – wyświetlanie ze stronicowaniem, edycję danych, a także uniezależnia od konkretnego typu bazy danych.

Posiadam przykład od siebie. Źródłem danych jest API serwisu Last.fm:

• Model informacji o utworze.
• Klasa abstrakcyjna modelu opartego o API Last.fm (oparty na klasie bazowej modelu frameworka).