Nie raz, nie dwa mieliśmy sytuację, która wymagała od nas koniunkcji warunków większej ilości danych lub dane te były tekstowe, ale niedługie. Niby nic, klucze załatwiają sprawę, ale sięgając do kodu gry bukmacherskiej, musiałem ją nieco zoptymalizować pod względem częstego wyciągania danych. Baza rozrosła się dość szybko, dlatego niezbędna była lekka modyfikacja jej struktury.

Moim zadaniem było bardzo częste wyciągnięcie ID meczu, który musiał na raz (AND) być zgodny z żądaną datą, nazwą drużyny pierwszej oraz drugiej. Informacji do warunków dostarczał system. Oprócz daty, są to dane tekstowe, więc połączyłem je ze sobą CONCAT i stworzyłem z nich sumę md5. Indeks, po którym baza szukała, był już krótszy od warunków, bo zawierał zawsze 32 znaki. Pierwszym warunkiem koniunkcji zawsze była suma md5 wymienionych wcześniej pól rekordu, nazwałem to suma kontrolna rekordu, potem faktyczna wartość pól, aby w razie zdublowania sumy kontrolnej (czego się nie spodziewamy, bo zakres wariacji jest ogromny, ale dla idei) wybrać prawidłowy rekord. Do tej pory wystarczało…

Gdy baza rozrasta się, problemem staje się wyszukiwanie. O ile suma kontrolna to już krok w stronę optymalizacji, dla >100k rekordów, baza danych potrzebowała co najmniej 0.05 sekundy na zwrócenie wyniku. Postanowiłem dodać odcisk palca sumy kontrolnej. Najlepszym rozwiązaniem okazało się dodanie jednego bajtu, który zrobił magię w bazie danych. Jedno pole TINYINT – 8 bitów, zakres 0-255 bez znaku. Założenia odcisku palca:

• jest wartością liczbową oraz zajmuje tylko jeden bajt, aby oszczędzić miejsca w rekordach oraz indeksach bazy danych,
• nie musi być uniwersalny (unikalny), a jedynie grupować odciski palców w mniejsze, a liczniejsze zbiory.

Rozwiązanie, które zastosowałem przy generowanu odcisku palca sumy kontrolnej, również nie jest skomplikowane:

1. Odcisk palca to suma kolejnych znaków sumy kontrolnej rekordu, gdzie 0 – 9 zachowują swoje wartości, a litery [a-f] przyjmują kolejno [10-15], dokładnie jak w przeliczaniu pojedynczych wyrazów systemu liczbowego o podstawie 16 (HEX) na dziesiętny.
2. Skoro jest to suma, to wartość minimalna jest dla samych zer, zatem MIN = 0.
3. Wartość maksymalną można stworzyć podając same maksymalne wartości F, zatem MAX = 480.
4. 480 mieści się na 9 bitach (min. 2 bajty, zakres 0-65535 bez znaku, tracimy 65055 wartości), dzieląc liczbę przez 2 tracimy unikalność odcisku dwukrotnie, ale zmieścimy się na ośmiu bitach, czyli jednym bajcie – możemy użyć typu TINYINT (zakres 0-255 bez znaku, nasza to 0-240), zatem tracimy tylko 15 niewykorzystanych wartości.

Przeprowadzamy testy naszego rozwiązania.

Stwórzmy przykładową tabelę danych test_md5_index, która będzie przechowywała wartości tekstowe w polach data_content, data_content2, data_content3. Tabela może zawierać pole dodatkowe, ale te trzy będziemy wykorzystywać w naszym wyszukiwaniu. Ważnym jest to, że warunkiem jest koniunkcja (AND), dlatego możemy stworzyć sumę (analogicznie do sumy logicznej) md5 jako odcisk palca tych pól, który zapiszemy w data_sum varchar(32). Dodatkowo stworzymy odcisk palca odcisku palca – jednobajtowe pole data_sum_index TINYINT.

Od razu zakładamy klucz podstawowy na data_id oraz klucz dla zapytania, który będzie go wykorzystywał, czyli szukanie wspólnie po data_sum_index oraz data_sum.

CREATE TABLE test_md5_index (
  data_id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  data_sum_index tinyint(1) UNSIGNED NOT NULL,
  data_sum varchar(32) NOT NULL,
  data_contents text NOT NULL,
  data_contents2 text NOT NULL,
  data_contents3 text NOT NULL,
  PRIMARY KEY (data_id),
  KEY data_index (data_sum_index, data_sum)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Pora stworzyć funkcję, która przeliczy nam nowy, krótszy odcisk palca na podstawie poprzedniego:

CREATE FUNCTION TestIndexChecksum(sSum VARCHAR(32)) RETURNS TINYINT
BEGIN
 
  DECLARE sSumPart VARCHAR(1);
  DECLARE iSumPart TINYINT;
  DECLARE iSum SMALLINT DEFAULT 0;
  DECLARE i INT;
 
  IF (SELECT sSum NOT REGEXP '^([a-z0-9]){32}$') THEN RETURN 0; END IF;
 
  SET i = 1;
 
  WHILE i <= LENGTH(sSum) DO
    SET sSumPart = SUBSTR(sSum, i, 1);
    SET iSumPart = (SELECT (CASE WHEN sSumPart = 'a' THEN 10 WHEN sSumPart = 'b' THEN 11 WHEN sSumPart = 'c' THEN 12 WHEN sSumPart = 'd' THEN 13 WHEN sSumPart = 'e' THEN 14 WHEN sSumPart = 'f' THEN 15 ELSE 0 END));
 
    IF iSumPart = 0 THEN
      SET iSumPart = sSumPart;
    END IF;
 
    SET iSum = iSum + iSumPart;
    SET i = i + 1;
  END WHILE;
 
  RETURN iSum / 2;
END;

Aby przeprowadzać testy, stwórzmy sobie procedurę, która wstawi nam N losowo, jakkolwiek wypełnionych rekordów do bazy danych:

CREATE PROCEDURE TestIndexesPrepareTest(IN i INT)
BEGIN
  TRUNCATE TABLE test_md5_index;
 
  WHILE i > 0 DO
 
    INSERT INTO test_md5_index SET
      data_contents  = (SELECT REPLACE(CONCAT(RAND() * 32), ".", "")),
      data_contents2 = (SELECT REPLACE(CONCAT(RAND() * 32), ".", "")),
      data_contents3 = (SELECT REPLACE(CONCAT(RAND() * 32), ".", "")),
      data_sum = CONCAT(data_contents, data_contents2, data_contents3),
      data_sum_index = TestIndexChecksum(data_sum);
 
    SET i = i - 1;
  END WHILE;
END;

Po wykonaniu CALL TestIndexesPrepareTest(100000) mamy przygotowane małe środowisko testowe.

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

Zwiń
Czytaj na blogu autora...

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...

Zwiń
Czytaj na blogu autora...

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.

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 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...

Zwiń
Czytaj na blogu autora...

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) >= " ... "

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) >= "' . $sDate . '"'

$aTerms[] = 'ticket_date >= "' . 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.

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.

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...

Zwiń
Czytaj na blogu autora...