Implementacja drzew w bazach danych.

Drzewo – w informatyce to struktura danych reprezentująca drzewo matematyczne. W naturalny sposób reprezentuje hierarchię danych (obiektów fizycznych i abstrakcyjnych, pojęć, itp.) jest więc stosowane głównie do tego celu. Drzewa ułatwiają i przyspieszają wyszukiwanie, a także pozwalają w łatwy sposób operować na posortowanych danych.

Definicja

Problem

Mimo częstego stosowania drzew w systemach informatycznych język SQL nie posiada odpowiednich typów danych umożliwiających szybką i efektywną pracę z drzewami. Istnieją implementacje umożliwiające umieszczenie tej struktury w klasycznych tabelach. Nie występuje rozwiązanie idealne i w każdym przypadku należy dobierać rozwiązanie do problemu.

Adjacency list

Rozwiązanie polega na dodaniu do rekordu informacji o identyfikatorze rodzica.

CREATE TABLE tree (
        id INTEGER,
        parent_id INTEGER,
        data_place TEXT       
)

Adjacency list

Zalety

• proste zastosowanie
• szybkie dodawanie, edytowanie i kasowanie danych

Wady

• przeszukiwanie wymagajace rekurencji
• niewydajny dla dużych zbiorów

Nested set

Rozwiązanie polega na dodaniu do każdego wiersza unikalnych identyfikatorów mówiących o zakresie podległych danych. Przykładowo dla drzewa składającego się z jednego elementu będzie to 1 i 2.

CREATE TABLE tree (
        id INTEGER,
        left_id INTEGER,
        right_id INTEGER,
        data_place TEXT       
)

Nested set

Zalety

• szybki odczyt
• umożliwia określenie kolejności 

Wady

• czasochłonne edytowanie, dodawanie i kasowanie, częste przeliczanie całego drzewa
• trudniejsze w implementacji i zrozumieniu

Nested set

Implementacja polega na dodaniu licznika lewego i prawego. Liczniki elementów poniższych zawsze muszą zawierać się w zakresie określonym przez liczniki elementu nadrzędnego.

Nested set

LTREE

Rozwiązanie jest specyficzną strukturą dostępna w bazach PostgreSQL. Polega w znacznej mierze na przechowywaniu ścieżek poszczególnych węzłów. Dla tej struktury dostępny jest szereg specjalnych operatorów.

CREATE TABLE tree (
        id INTEGER,
        path LTREE      
)

LTREE

Wyróżniamy kilka typów danych koniecznych do pełnej implementacji:

• ltree - podstawowa dana zawierająca ścieżkę
• lquery - zawiera wyrażenia regularne ułatwiający dostęp do elementów ścieżek
• ltxtquery - zawiera wyrażenia regularne umożliwiające wyszukiwanie pełnotekstowe

LTREE

Wyróżniamy kilka typów operatorów związanych z wcześniej wskazanymi typami.

• ltree @> ltree, zwraca bool, sprawdza czy lewy element jest rodzicem lub nim samym, istnieje operator odwrotny <@
• ltree ~ lquery, zwraca bool, sprawdza czy element odpowiada zadanemu wyrażeniu
• ltree @ ltxtquery, zwraca bool, sprawdza czy element może zostać wyszukany pełnotekstowo za pomocą zadanego wyrażenia
• ltree || ltree, zwraca ltree, łączy dwa elementy

LTREE

CREATE TABLE test (path ltree);
INSERT INTO test VALUES ('Top');
INSERT INTO test VALUES ('Top.Science');
INSERT INTO test VALUES ('Top.Science.Astronomy');
INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');
INSERT INTO test VALUES ('Top.Hobbies');
INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');
INSERT INTO test VALUES ('Top.Collections');
INSERT INTO test VALUES ('Top.Collections.Pictures');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');

ltreetest=> SELECT path FROM test WHERE path <@ 'Top.Science';
                path
------------------------------------
 Top.Science
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(4 rows)

ltreetest=> SELECT path FROM test WHERE path ~ '*.Astronomy.*';
                     path
-----------------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
 Top.Collections.Pictures.Astronomy
 Top.Collections.Pictures.Astronomy.Stars
 Top.Collections.Pictures.Astronomy.Galaxies
 Top.Collections.Pictures.Astronomy.Astronauts
(7 rows)

LTREE

Scalanie rozwiązań

Rozwiązanie polega na połączeniu adjacency list i nested set.

CREATE TABLE tree (
        id INTEGER,
        parent_id INTEGER,
        left_id INTEGER,
        right_id INTEGER,
        data_place TEXT       
)

Zalety

• odczytanie dzieci nie wymaga pełnej wiedzy o rodzicu
• szybszy odczyt dzieci dzięki pojedynczemu warunkowi

Wady

• nieznacznie czasochłonna dodatkowa operacja zapisu

Scalanie rozwiązań

Dodanie poziomu zagłębienia

Rozwiązanie polega na zapisywaniu poziomu zagłębienia podczas dodawania lub przenoszenia elementu.

CREATE TABLE tree (
        id INTEGER,
        parent_id INTEGER,
        depth INTEGER,
        data_place TEXT       
)

Dodanie poziomu zagłębienia

Zalety

• szybki dostęp do każdego poziomu zagłębienia bez wiedzy o rodzicach 

Wady

• konieczne dodatkowe operacje podczas zapisu

Dodanie listy przejść

Rozwiązanie polega na zapisywaniu wszystkich możliwych połączeń pomiędzy węzłami.

CREATE TABLE tree (
        id INTEGER,
        parent_id INTEGER,
        data_place TEXT       
)

CREATE TABLE transition (
        parent_id INTEGER,
        child_id INTEGER,
        range INTEGER       
)

Dodanie listy przejść

Zalety

• możliwość odwołania się do właściwości dowolnego przodka lub dziecka bez konieczności używania rekurencji 

Wady

• znacznie zwiększa czasochłonność podczas wprowadzania i zmieniania węzłów