Niezalogowany [ logowanie ]
Subskrybuj kanał ATOM Kanał ATOM    Subskrybuj kanał ATOM dla tagu sql Kanał ATOM (tag: sql)

Autor wpisu: Athlan, dodany: 10.01.2011 00:36, tagi: mysql, sql, internet

We wpisie Chmura tagów w PHP, w którym został przedstawiony problem budowy chmury tagów zapisałem przykładowe zapytanie prezentujące przykładowe dane dla klasy, które dosłownie zabija bazę danych zliczając za każdym razem ilość występowań tagów. Dostając feedbacki, zauważyłem, że problem ten jest bagatelizowany przez wiele osób. Spróbujmy zbudować bardziej optymalne rozwiązanie zarządzania strukturą danych w taki sposób, aby dane wyciągać bardzo bezboleśnie.

Zbudujmy przykładową strukturę bazy danych tagów, do której będziemy przypinać różne rzeczy – newsy, artykuły, galerie zdjęć, zdjęcia, cokolwiek.

Najprostsza tabela db_tags o polach:

  • tag_id, UNSIGNED, aby zwiększyć zakres INT – wartości ujemne nie są nam porzebne. Oczywiście primary key oraz auto increment.
  • tag_name, chociażby varchar(255)
  • tag_count, UNSIGNED, INT, ponownie bez znaku, aby zwiększyć zakres, wartości ujemne są nam niepotrzebne. Tutaj będziemy przechowywać liczbę reprezentującą, ile razy użyto tagu do oznaczenia dowolnego zestawu informacji.
CREATE TABLE db_tags (
  tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  tag_name VARCHAR(255) NOT NULL ,
  tag_count INT UNSIGNED NOT NULL
) ENGINE = INNODB;

Zastanówmy się, po czym będziemy sortować tagi. Warto założyć klucz na pole tag_count, znacznie przyspieszy późniejsze sortowanie wyników po najpopularniejszych tagach. Jeżeli chcemy sortować po liczbie występowań tagu oraz nazwie (aby chmura była alfabetycznie), warto założyć wspólny klucz na tag_name oraz tag_count. Osobiście sortowanie alfabetyczne zostawiam implementacji klasie tagów dla ksort(), bowiem zapytanie wyciągające tagi jest obarczone limitem, zatem wspólny klucz w bazie danych nie jest mi potrzebny – mniej danych w indeksach.

ALTER TABLE db_tags ADD INDEX (tag_count);

Tworzymy dowolną strukturę danych, która będzie podpinała się do naszych tagów. Pamiętajmy, że do tagów może podpinać się (a przynajmniej powinno, zależy od założeń początkowych projektu) wiele struktur jednocześnie. Wybrałem najbardziej pospolite – newsy w tabeli db_news.

CREATE TABLE db_news (
  news_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  news_title TEXT NOT NULL,
  news_content TEXT NOT NULL
) ENGINE = INNODB;

Pozostało nam stworzyć tabelę wiążącą nasze newsy z tagami (nie tagi z newsami). Tabelę nazwałem db_news_tags. Zawierać ona będzie tylko dwa pola przechowujące identyfikator newsa oraz przypisanego do niego tagu, zachowując typ danych wiążących, czyli INT UNSIGNED. Zakładam wspólny primary key dla obu pól.

  • handler_item – klucz ID newsa,
  • handler_node – klucz ID tagu.
CREATE TABLE db_news_tags (
  handler_item INT UNSIGNED NOT NULL,
  handler_node INT UNSIGNED NOT NULL,
PRIMARY KEY (handler_item, handler_node)
) ENGINE = INNODB;

Buduję relacyjną bazę danych. Gdy jakiś tag zostanie usunięty, bądź gdy jakiś news zostanie usunięty, automatycznie powinien zniknąć wpis z tabeli db_news_tags, zatem używamy kluczy obcych:

ALTER TABLE db_news_tags ADD FOREIGN KEY (handler_item) REFERENCES db_news (news_id) ON DELETE CASCADE;
ALTER TABLE db_news_tags ADD FOREIGN KEY (handler_node) REFERENCES db_tags (tag_id) ON DELETE CASCADE;

Tak zaprojektowaną strukturę danych mogę spokojnie używać do przechowywania danych. Pozostaje kwestia obliczania ilości występowań tagów. Istnieją co najmniej dwie szkoły.

  1. Każda zmiana danych w db_news_handler wywołuje procedurę liczącą tagi. Trzeba mieć na uwadze, że tagi są przeliczane od początku mielenie bazy, ale de facto proces odbywa się po kluczach. Zaletą rozwiązania jest to, że przy bardzo rozbudowanych strukturach (np. liczymy tylko aktywne i widoczne tagi) procedura uwspólnia nam warunki podliczania, używając jej w wielu miejscach nie musimy się martwić o redefiniowanie triggerów.
  2. Dla przedstawionego przykładu w tym poście wystarczy inkrementacja licznika przy dodaniu i dekrementacja przy usunięciu tagu. W większości przypadków właśnie takiego rozwiązania powinno się używać.

Luźny komentarz techniczny (problems, tips & tricks): Aby ominąć problemy wynikłe z założenia w punkcie pierwszym, równie dobrze możemy napisać procedury, które inkrementują/dekrementują liczbę tagów w zależności od warunków (np. tylko wtedy, kiedy tag jest aktywny i widoczny w serwisie). Nikt nie powiedział, że procedury muszą liczyć wszystko od początku możemy się na takie rozwiązanie zgodzić, rezygnujemy natomiast z synchronizacji licznika podczas zmiany warunków, wówczas podczas każdej zmiany warunków, trzeba przekręcić licznik od początku, zliczając wszystkie rekordy wg. ustalonych warunków ręcznie. Triggera należałoby również umieścić w UPDATE (zmiana stanu tagu, np. z niewidocznego na widoczny, z aktywnego na nieaktywny). I to jest najrozsądniejsze rozwiązanie.

W naszym przypadku ograniczymy się do dwóch triggerów, które będą trzymały rękę na pulsie w momencie przypisania tagu do struktury INSERT oraz zerwaniu przypisania DELETE. Zatem:

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Autor wpisu: Athlan, dodany: 02.01.2011 12:19, tagi: mysql, sql

Składowanie danych w kilku tabelach połączonych relacyjnie to bardzo dobry pomysł. Chyba najprostszym przykładem jest forum dyskusyjne: struktura oraz content postów mogą spokojnie być trzymane w osobnych tabelach. To samo tyczy się danych użytkowników. Rekordy rozbite na kilka tabel stają się mniej rozbudowane, o ile w ogóle występują - istnienie zawartości pola nie jest wtedy wymagane (użytkownik nie podał danych = nie ma rekordu).

Zaznaczając JOIN‘ujemy tabele w zależności od potrzeb, co zdarza się bardzo często. O UPDATE JOIN już wspominałem, też bardzo wygodna operacja, natomiast, co w przypadku, gdy musimy usunąć rekord uzależniony od wartości pola w innej tabeli? Sprawa jest banalnie prosta.

Na początek kilka technicznych uwag, na które łatwo można się nadziać:

  • Najczęściej będziemy mieli przypadek, w którym wartość pola musi być znana = rekord w tabeli obok musi istnieć. Nie zapomnijmy o pełnym złączeniu tabel INNER JOIN.
  • Gdy czyścimy śmieci w bazie danych, chcemy, aby wartość pola była konkretna lub niezdefiniowana, tabele możemy złączyć lewostronnie LEFT JOIN.
  • Składnia DELETE FROM jest bardzo podobna do SELECT. Zaznaczamy alias_tabeli.* jako wybór rekordu do usunięcia. Możemy usuwać rekordy z kilku tabel oddzielając zaznaczenia przecinkami. Przy JOIN’ach wymagane jest zdefiniowanie aliasów i sprecyzowanie, co chcemy usunąć alias_tabeli.* (edit: nie jest wymagane definiowanie aliasów, przykłady niżej)

Przykłady.

Dane userów mam składowane w dwóch tabelach – w jednej podstawowe dane (id, name, pass, pass_salt, mail, status_active), w kolejnej dane (data [jako handler user -> user_data], data_* [* - jakieśdane]). Chcę usunąć wszystkich użytkowników, którzy zarejestrowali się przed 48-godzinami i nie aktywowali swoich kont, aby zwolnić unikalne nazwy użytkowników i adresy email. Jednym kryterium jest user_status_active z tabeli users, kolejnym jest data user_data_join z tabeli users_data. Jako, że mam założony kaskadowy foregin key na pole user_data w tabeli users_data, przy usunięciu rekordu z tabeli users pozbędę się również jego danych, o co dbać nie muszę przy wypisywaniu alias_tabeli.*. W przypadku, kiedy nie miałbym założonego foregin key, musiałbym obsłużyć usunięcie rekordu z users_data wypisując po przecinku tabelę. Zatem:

DELETE item.* FROM `cms_members` AS `item`
INNER JOIN `cms_members_data` AS `item_data` ON (item.user_id = item_data.user_data)
WHERE item.user_state_active = 0 AND item_data.user_data_join < NOW()

~Tiraeth przesłał rozwiązanie beż użycia aliasów i słowa kluczowego JOIN, odwołujemy się po nazwie tabeli:

DELETE cms_members.* FROM `cms_members`, `cms_members_data`
WHERE cms_members.user_id = cms_members_data.user_data AND user_state_active = 0 AND user_data_join < NOW()

Podzapytanie oraz INNER JOIN generuje nam iloczyn kartezjański:

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

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

Autor wpisu: cojack, dodany: 14.08.2010 19:09, tagi: sql

PostgreSQL Na początku chciałbym przeprosić blogera Wojciecha Soczyńskiego, za to iż miałem napisać o klasie interpretującej interfejs Iterator, wpis już jest w trakcie budowy, ale nie mogę się zabrać by go skończyć. Także wyczekuj ;) Z cyklu artykułów o PL/pgSQL, chciałbym przedstawić kolejną z możliwości tego języka proceduralnego w bazie danych PostgreSQL.

Pętle cd.

Miałem napisać o pętlach, w poprzednim wpisie, ale materiał jest na tyle obszerny że postanowiłem to przenieść i poświęcić im cały wpis. No to jedziemy.

Pętle jak nam ładnie pokazuje manual:

[ <<label>> ]
LOOP
    statements
END LOOP [ label ];

Proste co? A teraz, krótki opis, otóż każda pętla może zostać „nazwana”, czyli posiadać tzw. label ( z ang. etykietę ), po co? Po to gdy będziemy chcieli zakończyć pętle, a będziemy mieli zagnieżdżenie pętel, to w przypadku chęci wyjścia całkiem z wszystkich pętli, wystarczy podać nazwę pętli wyżej. Służy nam temu polecenie:

EXIT [ label ] [ WHEN boolean-expression ];

Dlaczego nie RETURN a EXIT? Otóż możemy równie dobrze użyć RETURN w pętli by z niej wyjść, ale słowo kluczowe RETURN mówi nam coś innego, zwróć. A co my zwracamy wychodząc z pętli? No nic, także te słowo jest mylne.

Wracając do pętli, może się dziwnie wydawać że nie wiadomo kiedy się ona skończy, gdyż nie jest podoba do żadnej z pętli znanych nam z języka php, ani to for, ani do … while, ani while itd. No właśnie, gdybyśmy nie mieli żadnego warunku w pętli kończącego ją, to dostalibyśmy pętlę nieskończoną. Dlatego taka pętla musi posiadać w sobie warunek skończenia. Musimy na własną rękę inkrementować (itp..) jakaś wartość i sprawdzać ją w pętli. Otóż samej pętli LOOP to uwierzcie mi lub nie, to rzadko będziecie używać. No jest sama w sobie mało przydatna.

Iteracja tablicy elementów

Definicja pętli:

[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
    statements
END LOOP [ label ];

Tu jest moc, ukłony dla dev z postgre za wskaźniki. Dzięki językowi proceduralnemu PL/pgSQL, nie musimy się troszczyć o deklaracje wskaźników w pętli i ich wykorzystywanie, są one automatycznie definiowane i używane przez silnik bazy. Dobre co? W postgresie mamy typy tablicowe. Deklaracja jest bardzo podobna jak w innych językach:

  • INT[]
  • VARCHAR[]

I tak dalej, dobra dajmy na to że mamy funkcję której jednym z argumentów jest właśnie np VARCHAR[], co lepsze możemy zdefiniować argument funkcji jako ANYARRAY, i dzięki temu, możemy do takiej funkcji przesyłać tablicę różnego typu, bez potrzeby martwienia się o to, w jaki sposób to przesyłamy, i tak później przy wrzucaniu danych do bazy musimy rzutować typ. Wracając do sedna, to mamy taką tablice, która składa się np z 6 elementów. Indeksy tablic w postgresql są numerowane od 1 :D Też sobie wymyślili, no ale dobra. Mamy taką tablice, i dajmy na to że składa się z 240 wierszy. Czyli jest 240 indeksów. Albo i nie, ponieważ taka tablica może być dynamiczna i w sumie nie wiemy jak wielka jest ta tablica, wiemy jedynie że będzie posiadać 6 elementów jeden wiersz. I jak to zrobić? Patrzcie:

FOR i IN ARRAY_LOWER( "_someVar", 1 ) .. ARRAY_UPPER( "_someVar", 1 )
  LOOP
 
    INSERT INTO
      "someTable"
    (
      ... -- jakieś tam kolumny
    )
    VALUES
    (
      "_someVar"[ i ][ 1 ]::INT,
      "_someVar"[ i ][ 2 ]::VARCHAR,
      "_someVar"[ i ][ 3 ]::BOOLEAN,
      "_someVar"[ i ][ 4 ]::BIT,
      "_someVar"[ i ][ 5 ]::NUMERIC,
      "_someVar"[ i ][ 6 ]::BYTEA,
    );
 
  END LOOP;

Co to jest i ? i jest numerem indeksu, nie musimy go definiować w bloku DECLARE, jest on w biegu definiowany.

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Autor wpisu: Athlan, dodany: 17.07.2010 12:04, tagi: sql

Oblicza MySQL nie są do końca znane przy tworzeniu aplikacji, a problemy optymalizacyjne stają się nie lada problemem przy funkcjonowaniu wersji produkcyjnej projektu. Nie sposób przewidzieć wszystkich możliwości użycia pól, założenia zarówno wspólnych, jak i pojedynczych indeksów posiadających zakładaną przez nas moc i zajętą pamięć na dysku.

Ostatnimi czasy budowałem dość skomplikowany projekt, jeżeli chodzi o złożoność zapytań i wykonywanych przez nie operacje matematyczne. Pomimo tego, że aplikacja była doskonale przemyślana, a struktury bazy danych perfekcyjnie jej podporządkowane, gdzieś tkwił problem, bowiem jedno z zapytań generowało pozornie prosty (wizualnie) rezultat, baza reagowała na zapytanie dopiero po 2.5 sekundy dla 30k+ rekordów. Patrząc na strukturę kluczy i zapytania, zwłaszcza, że pola, na których operowałem były różnego rodzaju liczbami i datami zacząłem się poważnie martwić i rozkładać zapytanie na czynniki pierwsze, kończąc na warunkach. Wyobraźcie sobie moje zdziwienie, gdy doszedłem do tego, że całe obciążenie (ponad 2.3 sekundy) generował warunek:

WHERE DATE(ticket_date) &gt;= " ... "

Gdzie ticket_date to pole typu DATETIME. Od razu doszedłem do wniosku, że w parze idzie złe przygotowanie danych przez PHP, a angażowana jest w to wszystko baza, na której forsuje się użycie funkcji DATE(). Przynajmniej dla 30k+ rekordów zindeksowanego pola. Prosty zabieg zamiany jednej linijki kodu na drugą przyniósł porządane efekty.

$aTerms[] = 'DATE(ticket_date) &gt;= "' . $sDate . '"'
$aTerms[] = 'ticket_date &gt;= "' . date('Y-m-d H:i:s', strtotime($sDate)) . '"'

Budując aplikację zwracam szczególną uwagę na strukturę bazy, indeksowanie pól, rysuję diagramy przewidujące wykorzystanie danych pod różne zapytania, ale… tak banalny błąd przy przeanalizowanej aplikacji rozłożył mnie na łopatki. Z drugiej strony, zapomniałem o jednej bardzo ważnej rzeczy: maksymalnym odciążeniu bazy danych przy preparowaniu argumentów warunków, skoro warunki te mogą być w odpowiedni i przede wszystkim szybki sposób spreparowane na poziomie modelu (abstrakcyjnie rzecz ujmując, pozbywam się pojęcia PHP), który przygotują zapytanie tylko do wykonania operacji na surowych danych, bez konieczności ich ewentualnego przeliczania. Oczywiście nie zawsze taki efekt da się uzyskać, ale należy to maksymalnie optymalizować.

Jedno jest wiadome: przeliczanie DATE() dla rekordów w warunku jest nieoptymalne dla pola DATETIME.

Autor wpisu: cojack, dodany: 27.06.2010 11:37, tagi: sql

PostgreSQL Nadeszła w końcu ta chwila, w której miałem wolny czas by usiąść i dokończyć implementację ltree. Udało mi się, uzyskałem to co chciałem, i to na czym by każdemu programiście zależało. Działa tak jak ja chce, a nie tak jak baza chce. Do rzeczy, w ostatnim i zarazem pierwszym wpisie o ltree w postgresie nie mogłem sobie poradzić z sortowaniem, kombinowałem razem z depeszem jak by go tu posortować, w końcu jakieś rozwiązanie padło. Tylko te rozwiązanie depesz wziął z swojej struktury drzewiastej, otóż mówię temu stanowcze NIE! Dlaczego? Ltree zostało napisane po to by nie robić nic rekursywnie, to po prostu miażdży podejście do drzew w każdym calu, żadne nested sety i inne śmiecie z rightem i leftem. Tam to idzie zęby połamać a nie to ugryźć. Poza tym jest to mało optymalne, to już wolę id, parent_id (taki joke).

Sortowanie w ltree

Rozwiązanie było bliżej niż można było się tego spodziewać. Miałem z trylion pomysłów jak to rozkminić. Pomyślałem o liczbie rzeczywistej której każda kolejna część po przecinku będzie przedstawiała nr zagłębienia, tylko to nie jest tak łatwo oprogramować i mogłyby wyjść niezłe kaszany, także odpuściłem sobie to. I olśniło mnie ARRAY! No jacha! To przecież jest tak proste i oczywiste że nie wiem o czym my rozmawiamy. Sam sobie ten problem urodziłem, a powodem tego było iż nikt wcześniej nie pisał o tym, w necie to nawet nie ma słowa o tym module, parę rzeczy znajdziemy w manualu i stronie twórców. Ale nikt nie pokwapił się z implementacją tego z sortowaniem i się nie podzielił.

Implementacja ltree z sortowaniem

Otóż sprawa jest banalnie prosta (teraz już jest):

CREATE TABLE "category" (
   "idCategory"  SERIAL PRIMARY KEY NOT NULL,
   "categoryPath"   LTREE,
   "ordering" INT[]
);

I mógłbym powiedzieć amen. Alę dodam jeszcze:

INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top', '{1}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Science', '{1,1}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Science.Astronomy', '{1,1,1}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Science.Astronomy.Astrophysics', '{1,1,1,1}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Science.Astronomy.Cosmology'. '{1,1,1,2}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Science.Astronomy.Planets'. '{1,1,1,3}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Hobbies', '{1,2}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Hobbies.Amateurs_Astronomy', '{1,2,1}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Hobbies.Swiming', '{1,2,2}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Hobbies.Football', '{1,2,3}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Hobbies.Chess', '{1,2,4}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Hobbies.Basketball', '{1,2,5}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Hobbies.Voleyball', '{1,2,6}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Hobbies.Checkers', '{1,2,7}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Hobbies.Cards', '{1,2,8}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Hobbies.Skis', '{1,2,9}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Hobbies.Post_Cards', '{1,2,10}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Hobbies.Book', '{1,2,11}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections', '{1,3}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections.Pictures', '{1,3,1}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections.Pictures.Astronomy', '{1,3,1,1}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections.Pictures.Astronomy.Stars', '{1,3,1,1,1}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections.Pictures.Astronomy.Galaxies', '{1,3,1,1,2}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections.Pictures.Astronomy.Astronauts', '{1,3,1,1,3}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections.Pictures.Astronomy.Planets', '{1,3,1,1,4}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections.Pictures.Astronomy.Sun', '{1,3,1,1,5}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections.Pictures.Astronomy.Earth', '{1,3,1,1,6}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections.Pictures.Astronomy.Asteroids', '{1,3,1,1,7}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections.Pictures.Astronomy.UFO', '{1,3,1,1,8}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections.Pictures.Astronomy.Milk_Way', '{1,3,1,1,9}');
INSERT INTO category ( "categoryPath", ordering ) VALUES ('Top.Collections.Pictures.Astronomy.Space_Ships', '{1,3,1,1,10}');

To powyżej to demo do tabeli, o zapomniałbym o indeksach:

CREATE INDEX "categoryPath_gist_idx" ON "category" USING gist( "categoryPath" );
CREATE INDEX "categoryPath_idx" ON "category" USING btree( "categoryPath" );
CREATE INDEX "ordering_gist_idx" ON "category" USING gist( "categoryPath" );
CREATE INDEX "ordering_idx" ON "category" USING btree( "categoryPath" );

I sobie protestujcie sami jak to świetnie działa. W następnym i przy okazji ostatnim wpisie o ltree, przedstawię wszystkie metody do zarządzania drzewem, oraz widoki.

Autor wpisu: cojack, dodany: 05.06.2010 16:55, tagi: sql

PostgreSQL Ostatni wpis dotyczył postgresa i zgadnijcie czego będzie dotyczył ten. Otóż dzisiaj chciałbym napisać o instrukcjach warunkowych i pętlach. Sprawa może się wydawać banalne, ale oczywiście nie musi. Dlaczego? Ponieważ instrukcje warunkowe to nic jak najzwyklejsze IF … ELSE IF … ELSE. Oprócz tego postgres udostępnia nam inne opcje warunków które możemy wykorzystać w zapytaniach sql a mowa tutaj o CASE. A teraz dwa słowa o pętlach. Te bynajmniej ni w ząb ni w pietruchę nie są podobne do niczego. Składnia zwykłego LOOP w postgresie to powiedzmy samo LOOP … END LOOP, ha a gdzie warunki co? Otóż do tego zaraz przejdziemy.

Instrukcje warunkowe

Ten dział będzie poświęcony po prostu warunkom. Otóż jak wyżej wspomniałem w postgresie możemy stosować różnego rodzaju warunki, począwszy od IFów poprzez CASE skończywszy na COALESCE lub nie. Popatrzmy na przykład użycia IFa w funkcji:

CREATE..... RETURNS VOID AS $BODY$
  BEGIN
    SELECT
      ....
    FROM
      ...
    WHERE ....;
 
    IF NOT FOUND THEN
      -- coś tu robimy jak nie znaleziono
    ELSE
      -- w innym wypadku co innego lub kończymy RETURNem
    END IF;
  END;
$BODY$ LANGUAGE 'plpgsql';

Jak widać składnia IFa jest trochę podobna do tej której znamy z innych języków z tym że tutaj mamy słowo kluczowe THEN, które nie w każdym języku jest (bodajże w pascalu było ale mi się szukać nie chce). Kończywszy IFa musimy użyć składni END IF; Jak widać nie mamy tutaj żadnych klamer, dlatego ważne jest by stosować wcięcia przy kodowaniu by się samemu nie pogubić w zagłębieniach kodu. Taki kod od razu staje się czytelniejszy.

Jak widać dzięki takim rozwiązaniom ograniczymy się do wywołania tylko jednej funkcji z kodu php, dzięki temu i kod staje się czytelniejszy, w ten sposób można przerzucić pewną cześć logiki aplikacji na bazę danych, co w znacznym stopniu ułatwia pracę (ale i też może utrudnić, o tym później). Nie chce też znowu wałkować manuala, dlatego link do instrukcji warunkowych znajdziecie na dole, ale co jest ważne, konstrukcja musi zwrócić wartość BOOLEAN, ale to jest chyba oczywiste. W postgresie nie mamy znaku porównania tak jak w innych językach, a chodzi mi o podwójny znak równa się ==, tutaj jest tylko pojedynczy znak równa się. No i teraz, kiedy rozróżnić oba te znaki? Otóż bardzo prosto, porównanie jest tylko i wyłącznie w konstrukcjach warunkowych, użycie znaku równa się w postaci:

"_someVar" = "someVar2";

Jest niczym innym jak przypisaniem, i tak samo jak w każdym innym języku programowania jest l-value i r-value, ale o tym już kiedyś wspominałem, także nie będę tego przytaczał ponownie. Jest także inna konstrukcja która pozwoli nam przypisywać wartość zmiennej. Może ktoś z Was kojarzy język “Prolog”? Jak nie to nie ważne, ale w nim jest użyty taki przełącznik warunkowy “:-” a w postgresie mamy “:=” jako alias do “=” z tym że jest to tylko i wyłącznie przypisanie wartości do zmiennej. Także zachęcam Was do używania takiej oto konstrukcji przypisywania:

"_someVar" := "someVar2";

Jest ona czytelna, i oczywista, z niczym innym się kojarzyć nie może jak z przypisaniem.

Wracając do konstrukcji warunkowych to pozostało nam do opisania jeszcze CASE, jest to po prostu cudowna rzecz, coś wspaniałego, gdyż używanie IFów w SELECTcie jest po prostu mało czytelne i niewygodne. Dlatego dostaliśmy CASE, taki postgresowy switch. Do rzeczy, składnia CASE jest dwojaka:

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

albo:

CASE expression
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
END

W zależności od potrzeb, używamy jednej z nich, ale mała podpowiedź kiedy którą wybrać, otóż gdy mamy warunek na danej kolumnie to lepiej wybrać wariant drugi, podając tą kolumnę jako expression, gdy mamy parę warunków na różnych kolumnach łatwiej jest wtedy operować nimi na pierwszym przykładzie, przykłady w manualau, a ja dwa słowa o wyniku z THEN, otóż podana fraza w THEN zostanie bezwzględnie dorzucona do wyniku zapytania, czyli dzięki temu w THEN możemy podać np nazwę kolumny jaką byśmy chcieli pobrać w zależności o warunku, który zostanie spełniony. nie sprawdzałem ale pewnie też z tego poziomu można by wywołać funkcję. Warto spróbować kiedyś.

To by było tyle na temat CASE i IF, oprócz tego mamy jeszcze takie funkcje jak NULLIF, COALESCE, GREATEST i LEAST. Krótko o tych funkcjach:

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Autor wpisu: batman, dodany: 31.05.2010 18:00, tagi: sql

Zdarza się czasami, że musimy posortować dane inaczej niż zgodnie z kolejnością alfabetyczną. Załóżmy taką sytuację. Mamy tabelę zawierającą statusy produktów. Tak się zdarzyło, że zostały one zapisane w postaci tekstowej. Poniżej schemat tabeli

CREATE TABLE produkty
(
  id serial NOT NULL,
  nazwa character varying(500),
  status character varying(50)
)
WITH (
  OIDS=TRUE
);

oraz przykładowe dane

INSERT INTO produkty (id, nazwa, status) VALUES (1, 'produkt 1', 'magazyn 1');
INSERT INTO produkty (id, nazwa, status) VALUES (2, 'inny produkt', 'magazyn 2');
INSERT INTO produkty (id, nazwa, status) VALUES (3, 'super produkt', 'brak');
INSERT INTO produkty (id, nazwa, status) VALUES (4, 'produkt 2', 'magazyn 1');
INSERT INTO produkty (id, nazwa, status) VALUES (5, 'inny super produkt', 'dostawa');
INSERT INTO produkty (id, nazwa, status) VALUES (6, 'produkt 3', 'magazyn 2');

Jeśli chcielibyśmy posortować te dane w taki sposób, że najpierw wyświetlają się produkty znajdujące się w dowolnym magazynie, następnie produkty, których nie ma na składzie, a na końcu wszystkie, które są w dostawie, mielibyśmy spory problem. Na szczęście w postgresie jest takie coś jak wyrażenie warunkowe CASE, które idealnie nada się  do naszego problemu. Wprawdzie zapytanie się nieco rozrośnie, zwłaszcza jeśli mamy dużo warunków do sprawdzenia, ale pożądany efekt zostanie uzyskany bez konieczności zaprzęgania do pracy dodatkowego języka.

select 
	* 
from 
	produkty
order by 
	case
		when status = 'magazyn 1' then 1
		when status = 'magazyn 2' then 2
		when status = 'brak' then 3
		when status = 'dostawa' then 4
		else 5
	end

Powyższy sposób sprawdzi się również w przypadku sortowania danych numerycznych. Uzyskanie kolejności 6, 2, 3, pozostałe liczby malejąco,  jest równie prosty. Dla powyższych danych będzie to wyglądało następująco

select 
	* 
from 
	produkty
order by 
	case
		when id = 6 then 1
		when id = 2 then 2
		when id = 3 then 3
		else 4
	end
	, id desc

W ten oto prosty sposób, uzyskaliśmy niestandardowe sortowanie naszych danych.

Wszystkie wpisy należą do ich twórców. PHP.pl nie ponosi odpowiedzialności za treść wpisów.