przy wielu projektach pojawia się potrzeba przechowywania zmiennej listy “cech" jakichś obiektów.

weźmy na przykład sklep internetowy: mamy jakieś tam kategorie (struktura drzewiasta, moja ulubiona 🙂 ), w nich produkty. każdy produkt ma pewne cechy stałe – cena, tytuł, opis. natomiast produkty w określonych kategoriach mają swoje własne cechy dodatkowe.

np. dla samochodów możemy chcieć przechowywać:

• ilość drzwi
• pojemność silnika
• typ paliwa
• rodzaj skrzyni biegów

z drugiej strony, ogłoszenia w kategorii komputery będą miały pola takie jak:

• ilość pamięci
• wielkość dysku
• typ procesora

najprostszym rozwiązaniem jest trzymanie produktów w każdej kategorii w oddzielnej tabelce – gdzie każda z tych tabelek ma różną strukturę (inna lista pól).

jest to rozwiązanie nieakceptowalne – osobiście uważam, że jakiekolwiek rozwiązanie zakładające modyfikacje struktury bazy danych w trakcie normalnego użytkowania jest błędne.

inną metodą jest zrobienie sobie tabelki typu:

 CREATE TABLE zmienne_cechy (
id         SERIAL PRIMARY KEY,
produkt_id INT  NOT NULL REFERENCES produkty(id),
cecha1     TEXT,
cecha2     TEXT,
cecha3     TEXT,
cecha4     TEXT,
...
);

i pilnowanie, że dla danego produktu kolumna cecha1 oznacza pojemność silnika, a dla innego jest to ilość pamięci.

takie rozwiązanie ma swoje zalety – najważniejszą jest to, że aby wyciągnąć informacje o wszystkich polach dla danego produktu wystarczy pobrać jeden rekord z bazy.

ale dopóki nie obsługujesz miliona page-views dziennie w swoim sklepie – ten problem jest mało istotny 🙂

zdecydowanie najskuteczniejszą metodą jest tabelka:

 CREATE TABLE zmienne_cechy (
id         SERIAL PRIMARY KEY,
produkt_id INT  NOT NULL REFERENCES produkty(id),
cecha      TEXT,
wartosc    TEXT
);

taka tabelka w jednej prostej strukturze pozwala na zapisanie wszystkich mozliwych cech i łatwe wyszukiwanie. no właśnie. czy na pewno łatwe?

tabelka pokazana taka jak tu – jest może i fajna, ale brakuje jej jeszcze jednej rzeczy:

create unique index ui_zmienne_cechy_pic on zmienne_cechy (produkt_id, cecha);

jeśli nie czytacie sql'i ze 100% zrozumieniem, to powyższe powoduje, że dany produkt może mieć tylko jedną wartość danej cechy. może mieć dowolnie wiele cech, ale żadna z cech nie może mieć wielu wartości.

zazwyczaj takie ograniczenie w niczym nie przeszkadza. zdarzają się czasem (ale bardzo rzadko) sytuacje, że istnieje potrzeba wielu wartości jednej cechy – sugeruję by wtedy nie kasować tego indeksu/klucza unikalnego tylko po prostu użyć ciut innych cech.

czemu?

otóż taka tabelka z pokazanym kluczem unikalnym pozwala nam w trywialny sposób zrobienie tego co bez klucza jest dużo trudniejsze (zasobochłonne): znalezienia produktów w/g kilku cech jednocześnie.

załóżmy, że chcemy znaleźć samochody o pojemności silnika 2000 z automatyczną skrzynią biegów.

bez klucza unikalnego jesteśmy skazani na coś takiego:

 SELECT zc1.produkt_id
FROM zmienne_cechy zc1 JOIN zmienne_cechy zc2 ON zc1.produkt_id = zc2.produkt_id
WHERE zc1.cecha='pojemnosc silnika' AND zc1.wartosc = '2000' AND zc2.cecha = 'skrzynia biegow' AND zc2.wartosc = 'automat';

nie jest to oczywiście takie złe. ale przy dużej ilości produktów stanie się problematyczne. nie mówiąc o tym jak będziemy chcieli sprawdzić produkty w/g np. 5 cech na raz. 5 joinów? jeśli w bazie jest np. 1000 produktów i każdy ma średnio 10 cech, to łączymy 5 razy ze sobą tabelę o 50000 rekordów. i szukamy na nich wszystkich. mało przyjemne.

dodanie wspomnianego wyżej klucza unikalnego pozwala na użycie w naszym select'cie rzadko używanej (i słabo znanej) klauzuli HAVING:

 SELECT produkt_id
FROM zmienne_cechy
WHERE
(cecha='pojemnosc silnika' AND wartosc='2000')
OR
(cecha='skrzynia biegow' AND wartosc = 'automat')
GROUP BY produkt_id
HAVING COUNT(*) = 2;

powstałe zapytanie ma kilka zalet:

• jest trywialnie rozbudowywalne o kolejne cechy – bez konieczności dodatkowych joinów = wystarczy dodać dodatkowe warunki i podbić wartość w klauzuli having
• jeśli używamy postgresql'a 8.1 i mamy dodatkowo indeks dwupoowy na (cecha, wartosc), to postgresql uzyje bardzo szybkich bitmap-or'ów

moje testy wykazały, że na postgresie 8.1 obie metody dają bardzo podobne wyniki (chodzi o czas zapytania) jeśli szukamy dwóch cech, natomiast już od 3 przewaga rozwiązania z having jest olbrzymia.

wyszedł nowy postgresql.

co nowego:

• poprawka  związana z możliwością robienia jakichś sql-injection. szczegółów nie czytałem, ale coś mocno skomplikowanego, bo zrobili specjalną stronę tylko o tym.
• istotne jest to, że nowy postgres będzie odrzucał escape'owanie apostrofów (‘) przez backslash (\). tzn. nie będzie można zrobić:
  select ‘costam \'bleble';
  tylko trzeba będzie napisać:
  select ‘costam "bleble';
  ta druga metoda jest zgodna ze standardami i ogólnie lepsza. jeśli używać \' – nie desperuj. zakaz używania tego tyczy tylko niektórych kodowań (zasadniczo azjatyckich).
• w pgcrypto poprawiono funkcję generującą słabe klucze – tyczy się to tylko sytuacji gdy buduje się pgcrypto bez używania openssl'a – czyli dosyć niszowa sprawa
• poprawiono funkcje konwertujące między różnymi charsetami. nic związanego z polską (na oko), ale jeśli pracujecie z cyrlicą to może was zainteresować (o ile używacie konwersji win1251_to_iso)
• procesy autovacuuma są teraz widoczne w pg_stat_activity (wreszcie!)
• wyłączono funkcję full_page_writes. funkcja ta w pewnych sytuacjach może uszkodzić dane. sensowny fix będzie w 8.2, na razie zostało to po prostu wyłączone.
• trochę poprawek do planera i optymalizatora (związanych z lepszą obsługą join'ów, bitmap scanów, oraz specyficznych optymalizacji dla agregatorów min() i max())
• poprawka błędu który mógl doprowadzić do padu backendu jeśli w jednej transakcji jakaś funkcja była modyfikowana i później (w tej samej transakcji) używana.
• poprawka odtwarzania danych z rekordów WAL w sytuacji gdy indekxy btree były obcinane
• usunięcie błędu związanego z nieprawidłową obsługą operatora | w wyrażeniach SIMILAR TO (takie jakby LIKE skrzyżowane z regexpami)
• 2 zapytania tworzące tabele (select into i create table as) tworzą tabele w domyślnym tablespace, a nie w głównym.
• poprawka do serwera tycząca negocjacji ssl'i (a dokładniej nieprawidłowego obsługiwania parametrów wymiany diffie-helmana)
• poprawiona wydajność sortowania algorytmem qsort (ta poprawka tyczy się kodu który jest używany jedynie na solarisie)
• korekty działania bonjour w architekturze x86 (bonjour to taki system wykrywania usług, oryginalnie stworzony na OS/X)
• kilka poprawek drobnych memleaków
• poprawka tycząca wprowadzania hasła na systemach win32
• pg_dump lepiej zapisuje wartości domyślne dla domen
• pg_dumpall lepiej obsługuje sytuacje gdy dumpuje z systemu mającego i usera i grupę userów o tej samej nazwie (tyczy się tylko dumpowania z postgresql'a < 8.1
• pg_restore zostało poprawione tak, aby switch -n działał zgodnie z dokumentacją 🙂 (-n XXX odtworzy tylko obiekty z tej schemy)

poprawki związane z bezpieczeństwem zostały też sportowane do wcześniejszych wersji postgresa (8.0 (8.0.8), 7.4 (7.4.13) i 7.3 (7.3.15))

czasem jest taka potrzeba by zwrócić kilka połączonych stringów z kolejnych rekordów.

np.:

mając tabelkę z takimi danymi:

> select pracownik from pracownicy where firma_id = 1;

• Jan Kowalski
• Hubert Lubaczewski
• Piotr Nowak
• Adam Słodowy

chcielibyśmy dostać jedno pole z zawartością:

Jan Kowalski, Hubert Lubaczewski, Piotr Nowak, Adam Słodowy

lub wręcz posortowane:

Adam Słodowy, Hubert Lubaczewski, Jan Kowalski, Piotr Nowak

aby to zrobić najwygodniej jest zdefiniować własny agregat (czyli funkcję taką jak min(), max(), sum(), avg()).

najpierw zdefiniujemy funkcję łączącą:

CREATE OR replace function agg_text_sum(txt_before TEXT, txt_new TEXT) RETURNS TEXT AS
$BODY$
DECLARE
BEGIN
IF (txt_before IS NULL) THEN
RETURN txt_new;
END IF;
RETURN txt_before || ‘, ‘ || txt_new;
END;
$BODY$
LANGUAGE ‘plpgsql';

następnie tworzymy agregat:

CREATE aggregate text_sum (
basetype = TEXT,
sfunc = agg_text_sum,
stype = TEXT
);

i już możemy:

> select text_sum(pracownik) from pracownicy where firma_id = 1;

• Jan Kowalski, Hubert Lubaczewski, Piotr Nowak, Adam Słodowy

aby to posortować trzeba uciec się do podzapytań:

> select text_sum(pracownik) from (select pracownik from pracownicy where firma_id = 1 order by pracownik) x;

• Adam Słodowy, Hubert Lubaczewski, Jan Kowalski, Piotr Nowak

kolega z pracy zapytał:

jak wstawić rekord do tabelki, ale tylko jeśli go tam jeszcze nie ma?

teoretycznie sprawa jest prosta: select, jak nic nie zwróci – insert. no ale jak będzie to działało równolegle to jest spora szansa na wysypanie się takiego kodu.

potrzebna jest więc inna metoda.

może insert i przechwycenie błędu.

w sumie to nie takie głupie:

• BEGIN;
• INSERT;
• COMMIT;

jeśli insert się nie udał, no to commit zrobi to samo co rollback.

fajnie. zadziała. ale jest jakoś mało eleganckie.

a może inaczej … chwila zastanowienia i … jest. proste rozwiązanie:

INSERT INTO tabelka (pole_1, pole_2, …, pole_n)
SELECT wartosc_1, wartosc_2, …, wartosc_n
WHERE NOT EXISTS ( SELECT * FROM tabelka WHERE pole_1 = wartosc_1 AND pole_2 = wartosc_2 AND … AND pole_n = wartosc_n);

nie jest to może najśliczniejsze, ale działa na pewno. nie wywala się niezależnie od tego czy wstawiło czy nie (więc można bezpiecznie używać w transakcjach). same plusy 🙂