[PostgreSQL] Pola typu array jako coś w rodzaju foreign key
Michał Sulik
Załóżmy, że mam bazę wierzchołków i grafów. Wszystko to trzymam sobie
w Postgresie (8.2.6):
CREATE TABLE wierzcholki (id INTEGER, label VARCHAR(255));
INSERT INTO wierzcholki VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),
(6,'F'),(7,'G');
CREATE TABLE grafy (id INTEGER, v1 INTEGER[], v2 INTEGER[], title
VARCHAR(255));
INSERT INTO grafy VALUES(101,'{1,3,6}','{2,5,7}','Graf 101');
INSERT INTO grafy VALUES(102,'{2,2,3,5,7}','{1,3,4,6,7}','Graf 102');
INSERT INTO grafy VALUES(103,'{2,3,4,5,7}','{3,4,5,6,6}','Graf 103');
Każdy rekord tabeli `grafy` to jeden graf. Graf jest definiowany jako
lista par wierzchołków, przy czym v1 to lista "pierwszych" elementów
każdej pary, v2 to lista "drugich" elementów pary. Tak więc na
przykład graf nr 101 zbudowany jest z krawędzi (1,2), (3,5) oraz
(6,7). Ta struktura nie podlega dyskusji; nie mogę jej zmienić,
uprościć ani zdefiniować inaczej.
Chciałbym wyświetlić wszystkie pary wierzchołków grafu nr 102. Wpadłem
więc na takie zapytanie:
SELECT
w1.label,
w2.label
FROM
grafy AS g,
wierzcholki AS w1,
wierzcholki AS w2
WHERE
g.id = 102
AND
(
/* Sprawdzamy zerowa pare */
g.v1[0] = w1.id AND g.v2[0] = w2.id
OR
/* ... pierwsza pare */
g.v1[1] = w1.id AND g.v2[1] = w2.id
OR
/* ... druga pare ... itd */
g.v1[2] = w1.id AND g.v2[2] = w2.id
OR
g.v1[3] = w1.id AND g.v2[3] = w2.id
OR
g.v1[4] = w1.id AND g.v2[4] = w2.id
)
;
Ale może się zdarzyć, że krawędzi w grafie będzie więcej niż 5
(aczkolwiek wiadomo, że nie będzie ich bardzo dużo). Bezsensownym
byłoby dodawanie kolejnych warunków dla v1[5], v2[6] i tak dalej.
Powyższy przykład jest analogią, dobrze ilustrującą pewien prawdziwy
problem. Mamy relację wiele-do-wielu, przy czym nie używamy tabel
pośrednich, tylko odpowiedniki kluczy obcych przechowujemy w polu typu
integer[].
Pytanie: jak powyższe zapytanie uprościć? Idealnym byłoby użycie
własnej funkcji, która w pętli "paruje" elementy, ale czy da się to
obejść bez uciekania się do definiowania własnej funkcji?
Dziękuję za zainteresowanie i pozdrawiam :)
Michał :)
Paweł Matejski
> Witam serdecznie :)
>
> Załóżmy, że mam bazę wierzchołków i grafów. Wszystko to trzymam sobie
> w Postgresie (8.2.6):
>
> CREATE TABLE wierzcholki (id INTEGER, label VARCHAR(255));
> INSERT INTO wierzcholki VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),
> (6,'F'),(7,'G');
>
> CREATE TABLE grafy (id INTEGER, v1 INTEGER[], v2 INTEGER[], title
> VARCHAR(255));
> INSERT INTO grafy VALUES(101,'{1,3,6}','{2,5,7}','Graf 101');
> INSERT INTO grafy VALUES(102,'{2,2,3,5,7}','{1,3,4,6,7}','Graf 102');
> INSERT INTO grafy VALUES(103,'{2,3,4,5,7}','{3,4,5,6,6}','Graf 103');
>
> Każdy rekord tabeli `grafy` to jeden graf. Graf jest definiowany jako
> lista par wierzchołków, przy czym v1 to lista "pierwszych" elementów
> każdej pary, v2 to lista "drugich" elementów pary. Tak więc na
> przykład graf nr 101 zbudowany jest z krawędzi (1,2), (3,5) oraz
> (6,7). Ta struktura nie podlega dyskusji; nie mogę jej zmienić,
> uprościć ani zdefiniować inaczej.
A tu tkwi błąd. Aż się prosi o tabelę krawędzie!
> Chciałbym wyświetlić wszystkie pary wierzchołków grafu nr 102.
>
> Ale może się zdarzyć, że krawędzi w grafie będzie więcej niż 5
> (aczkolwiek wiadomo, że nie będzie ich bardzo dużo). Bezsensownym
> byłoby dodawanie kolejnych warunków dla v1[5], v2[6] i tak dalej.
>
> Powyższy przykład jest analogią, dobrze ilustrującą pewien prawdziwy
> problem. Mamy relację wiele-do-wielu, przy czym nie używamy tabel
> pośrednich, tylko odpowiedniki kluczy obcych przechowujemy w polu typu
> integer[].
To jest idealny przykład na to, dlaczego nie należy używać tablic,
jeśli się nie wie co chcę się zrobić. ;)
Ja stosuję zasadę - nie używać tablic, gdy chce się je użyć do czegoś
innego niż do ich zwrócenia w wyniku zapytani, czyli tylko bezpośrednio
po SELECT.
> Pytanie: jak powyższe zapytanie uprościć? Idealnym byłoby użycie
> własnej funkcji, która w pętli "paruje" elementy, ale czy da się to
> obejść bez uciekania się do definiowania własnej funkcji?
I to jest jedyny sposób przy tej strukturze.
--
P.M.
Wojciech Malinowski
> Ta struktura nie podlega dyskusji; nie mogę jej zmienić,
> uprościć ani zdefiniować inaczej.
Ze zwykłej ciekawości - dlaczego nie możesz?
Pozdrawiam,
Wojciech Malinowski
Michał Sulik
Dziękuję za uwagę :)
Pokrótce opiszę mój problem. Jest to część większego projektu, a tutaj
chciałem tylko uprościć to zagadnienie.
Mnie nada pobrać informacje o referencjach z pg_constraint (http://
www.postgresql.org/docs/8.2/interactive/catalog-pg-constraint.html).
Tam są dwa pola: conkey i confkey typu integer[], które przechowują
odpowiednio: listę kolumn w tabeli referującej oraz listę kolumn w
tabeli referowanej. Ta informacja jest dokładnie w takiej formie, jak
w moim przykładzie w tabeli "grafy", mianowicie, te dwie listy razem
wzięte tworzą pary numerków (jak moje v1 i v2), odnoszących się do
pg_attribute (żeby było zabawniej, to nie są oid'y, tylko attnumy,
czyli numerki, określające kolejność kolumn w tableli, ale to mały
pryszcz, z którym łatwo sobie poradzić).
Sprawdziłem, "jak to się robi" w phpPgAdmin - tam jest zapytanie
podobne do mojego, to znaczy, porównywane są kolejne elementy każdej z
tablic, przy czym warunek ten jest generowany na podstawie liczby
kolumn w kluczu, pobranej przed właściwym zapytaniem.
Z kolei pgAdmin robi tak: pobierane są rekordy z pg_constraint (plus
inne rzeczy), ale informacje z pg_attribute są pobierane dopiero potem
w kodzie C++ w pętli przechodzącej przez tablice conkey i confkey.
A mnie chodzi o uproszczenie tego zagadnienia; o ile to możliwe, bez
przenoszenia roboty do kodu (akurat ja robię to w pythonie) :)
Pozdrawiam,
MS. :)
Michał Sulik
najbardziej mi zależało, wklejam tutaj ku potomności rozwiązanie z
własną funkcją, napisaną w PL/pgSQL:
CREATE FUNCTION pobierz_kolumny(OID, INTEGER[]) RETURNS VARCHAR[] as $
$
DECLARE
/* Wszystkie kolumny tabeli o zadanym oid. */
kolumny VARCHAR[];
BEGIN
/* Pobieramy wszystkie nazwy kolumn z tej tabeli. */
DECLARE
/* Zmienna pomocnicza, będzie przechowywać nazwę
atrybutu wewnątrz iteracji. */
tmp VARCHAR;
BEGIN
FOR tmp IN
SELECT attname
FROM pg_attribute
WHERE attrelid = $1 AND attnum >= 1
ORDER BY attnum
LOOP
/* Operator || wstawia element na koniec
tablicy. */
kolumny := kolumny || tmp;
END LOOP;
END;
/* Spośród zadanych w 2. argumencie numerów kolumn wybieramy
ich nazwy. */
DECLARE
/* Zmienna pomocnicza do numerowania iteracji. */
i INTEGER;
/* Tablica, ktorą zwróci funkcja. */
ret VARCHAR[];
BEGIN
IF (ARRAY_UPPER($2, 1) > 0) THEN
/* Iterujemy po zadanych numerach kolumn. */
i := 1;
LOOP
ret := ret || kolumny[$2[i]];
i := i + 1;
EXIT WHEN i > ARRAY_UPPER($2, 1);
END LOOP;
END IF;
RETURN ret;
END;
END;
$$ language plpgsql;
Natomiast zapytanie wyciągające żądane informacje:
SELECT
pg_constraint.conname,
pg_constraint.contype,
pg_class_rel.relname AS rel_relname,
pg_class_frel.relname AS rel_relname,
pobierz_kolumny(conrelid, pg_constraint.conkey) AS conkeynames,
pobierz_kolumny(confrelid, pg_constraint.confkey) AS confkeynames
FROM
pg_constraint
LEFT JOIN
pg_class AS pg_class_rel
ON
pg_constraint.conrelid = pg_class_rel.oid
LEFT JOIN
pg_class AS pg_class_frel
ON
pg_constraint.confrelid = pg_class_frel.oid
WHERE
pg_constraint.contype IN('f', 'p')
AND
pg_class_rel.relname = 'nazwa_tabeli';
Atrybuty conkeynames oraz confkeynames zawierają wówczas tablice z
nazwami kolumn, wchodzących w skład klucza i odpowiednio: kolumn, do
których odnosi się referencja.
Nie jest to może optymalny sposób, ale z założenia takie zapytania nie
będą wykonywane często. Myślę, że tę metodę można łatwo przełożyć na
bardziej ogólny problem, jaki opisałem w pierwszej wiadomości tego
tematu.
Pozdrawiam,
MS :))