Zapytania SQL

Do realizacji powyższych zadań będą nam potrzebne czasem dość skomplikowane zapytania do bazy danych. Stanowią one właściwie o wszystkim, ponieważ nie lubię używać php do obrabiania danych z bazy i dlatego zapytania przejmują cały ciężar formatowania i pozyskiwania danych. Postaram się dokładnie opisać oraz podać przykłady prezentowanych zapytań tak, aby ich działanie było jasne.

Pobieranie danych o rodzicu

Zapytaniem, które będzie powtarzać się kilkakrotnie jest zapytanie o dane rodzica. Wygląda ono następująco:

SELECT cluster,SUBSTRING(level, 1, INSTR(level,'0')-1) AS cutLevel, INSTR(level,'0')-1 AS depth
FROM groups
WHERE id=[*parent_id*]

#
#Czyli dla rodzica "WINDOWS":
#

SELECT cluster,SUBSTRING(level, 1, INSTR(level,'0')-1) AS cutLevel, INSTR(level,'0')-1 AS depth
FROM groups
WHERE id=47

#
#Uzyskany wynik to:
#

+---------+----------+-------+
| cluster | cutLevel | depth |
+---------+----------+-------+
|       1 | 1        |     1 |
+---------+----------+-------+

Dzięki temu zapytaniu pobieramy z bazy numer grupy rodzica (cluster), jego level z którego od razu usuwamy końcowe zera (cutLevel) oraz głębokość (depth). Możecie zauważyć na przykładzie tego zapytania, że dzięki levelowi możemy wyznaczyć również głębokość zagnieżdżenia danej wartości. Odczytywanie głębokości polega na odczycie, na którym miejscu pojawia się w levelu pierwsze zero i odjęciu od tej wartości jedynki. Czyli dla "WINDOWS" level wynosi 1000000, zero znajduje się na drugim miejscu, odejmujemy od dwójki jedynkę i już nam wychodzi że "WINDOWS" jest wartością o pierwszym stopniu zagnieżdżenia

Pobieranie struktury całego drzewa

SELECT *, INSTR(level,'0')-1 AS depth
FROM groups
WHERE cluster = [*cluster*] AND
INSTR(level,'0')-1 <= [*depth*]
ORDER BY level

#
#Przykład dla naszej grupy
#

SELECT *, INSTR(level,'0')-1 AS depth
FROM groups
WHERE cluster = 1 AND
INSTR(level,'0')-1 <= 5
ORDER BY level

#
#A jego wynik:
#

+----+---------+--------------+---------+-------+
| id | cluster | name         | level   | depth |
+----+---------+--------------+---------+-------+
| 46 |       1 | systems      | 0000000 |     0 |
| 47 |       1 | windows      | 1000000 |     1 |
| 49 |       1 | NO NT        | 1100000 |     2 |
| 52 |       1 | Win 95       | 1110000 |     3 |
| 53 |       1 | Win 98       | 1120000 |     3 |
| 54 |       1 | Win Milenium | 1130000 |     3 |
| 51 |       1 | NT           | 1200000 |     2 |
| 55 |       1 | Win 2000     | 1210000 |     3 |
| 56 |       1 | Win XP       | 1220000 |     3 |
| 57 |       1 | Win 2003     | 1230000 |     3 |
| 48 |       1 | linuks       | 2000000 |     1 |
| 61 |       1 | Slack        | 2100000 |     2 |
| 62 |       1 | Debian       | 2200000 |     2 |
+----+---------+--------------+---------+-------+

Zapytanie jest raczej proste. Proste dzięki kolumnie level która w łatwy sposób uporządkowuje wartość w odpowiedniej kolejności. Zauważmy również, że jeśli nie podamy opcjonalnego parametru $depth to głębokość jest on równa $maxNest.

Pobieranie struktury gałęzi drzewa

Następne zapytanie ma za zadanie zwrócić strukturę określonej gałęzi drzewa. Początkiem tej gałęzi jest wartość, której id podajemy. Jeśli chodzi o poziom zagnieżdżenia to działa analogicznie jak w poprzednim przykładzie. Zanim wykonamy to zapytanie jesteśmy zmuszeni spytać o dane rodzica.

SELECT *, INSTR(level,'0')-1 AS depth, INSTR(level,'0')-1 - [*parent_depth*] AS relativeDepth
FROM groups
WHERE cluster = [*parent_cluster*] AND
level LIKE '[*parent_cutLevel]%' AND
INSTR(level,'0')-1 <= ([*$depth*]+[*parent_depth*])
ORDER BY level

#
#Czyli w naszym przypadku:
#

SELECT *, INSTR(level,'0')-1 AS depth, INSTR(level,'0')-1 - 1 AS relativeDepth
FROM groups
WHERE cluster = 1 AND
level LIKE '1%' AND
INSTR(level,'0')-1 <= (5+1)
ORDER BY level

+----+---------+--------------+---------+-------+---------------+
| id | cluster | name         | level   | depth | relativeDepth |
+----+---------+--------------+---------+-------+---------------+
| 47 |       1 | windows      | 1000000 |     1 |             0 |
| 49 |       1 | NO NT        | 1100000 |     2 |             1 |
| 52 |       1 | Win 95       | 1110000 |     3 |             2 |
| 53 |       1 | Win 98       | 1120000 |     3 |             2 |
| 54 |       1 | Win Milenium | 1130000 |     3 |             2 |
| 51 |       1 | NT           | 1200000 |     2 |             1 |
| 55 |       1 | Win 2000     | 1210000 |     3 |             2 |
| 56 |       1 | Win XP       | 1220000 |     3 |             2 |
| 57 |       1 | Win 2003     | 1230000 |     3 |             2 |
+----+---------+--------------+---------+-------+---------------+

Zapytanie znajduje wszystkie dzieci z danej grupy (cluster) 1, które maja level '[obcięty level rodzica]%' z głębokością podaną przez użytkownika lub maksymalną ($maxNest) oraz zwraca wszystkie dane z tabeli plus głębokość bezwzględną (liczoną od korzenia drzewa) oraz głębokość względna liczoną od pierwszego elementu rodzica.

Dodanie nowego drzewa

Zapytanie realizujące dodanie nowego drzewa (korzenia). Już wcześniej wspominałem, że elementem nadrzędnym i niepodlegającym tym reguła jest element o levelu 0000000. Jest to rodzic dla całego drzewa. Stworzenie nowego drzewa polega, więc na umieszczeniu nowego rodzica o danej nazwie, unikalnym numerze cluster oraz levelu 0000000.

INSERT INTO groups ( cluster, name, level )
SELECT MAX(cluster)+1,'[*name*]', RPAD('0',".[*levelNum*],'0') FROM groups

Dodanie nowego dziecka do danego rodzica

Zapytanie mające za zadanie dodać nowe dziecko do rodzica. Zauważmy pewna zależność. Dziecko dla danego rodzica posiada zawsze jedna cyfrę więcej w levelu. W tej metodzie niestety cyfry te są ograniczone (1-9) dlatego wiec nie możemy sobie pozwolić na marnotrawstwo przestrzeni i nie możemy po prostu pobierać najwyższego dostępnego levelu i dodawać do niego jedynki. Zauważ ze ta metoda sprawdza się dopóki nie usuniemy jakiejś grupy. Np. jeśli chcemy dodać dziecko do rodzica, o levelu 1100000 i posiada on dziecko 1120000 (dziecko o levelu 1110000 zostało kiedyś usunięte) to w takim przypadku już nigdy nie wykorzystalibyśmy levelu usuniętego. Zapytanie, sql musi wiec przewidzieć ewentualność usunięcia grupy i umożliwić wypełnienie tego miejsca inną.

INSERT into groups (cluster, name, level)
SELECT [*parent_cluster*], '[*name*]',p.level,RPAD(SUBSTRING(g.level, 1, [*parent_depth*] + 1)+1 ,[*levelNum*],'0')
FROM groups AS g
LEFT JOIN groups AS p ON CONCAT(g.cluster,'|',RPAD(SUBSTRING(g.level, 1, [*$parent_depth*]+ 1)+1 ,[*$levelNum*],'0')) = CONCAT(p.cluster,'|',p.level)
WHERE g.cluster = [*parent_cluster*] AND
g.level LIKE CONCAT( '[*parent_ cutLevel *]','%') AND
[*parent_depth*]+ 1 <= [*levelNum*]-1 AND
p.level IS NULL
LIMIT 1

#
# Przykład dodania wartości "WINDOWS CE" do rodzica "WINDOWS"
#

INSERT INTO groups (cluster, name, level)
SELECT 1, 'Windows Ce',RPAD(SUBSTRING(g.level, 1, 1 + 1)+1 ,7,'0')
FROM groups AS g
LEFT JOIN groups AS p ON CONCAT(g.cluster,'|',RPAD(SUBSTRING(g.level, 1, 1 + 1)+1 ,7,'0')) = CONCAT(p.cluster,'|',p.level)
WHERE g.cluster = 1 AND
g.level LIKE CONCAT( '1','%') AND
1 + 1 <= 7-1 AND
p.level IS NULL
LIMIT 1

Skomplikowane? Nie! Zapytanie porostu kopiuje tabele groups na tabele g i p pobiera z tabeli g wszystkie levele oraz dodaje do nich jeden, następnie sprawdza czy istnieje taki level w tabeli p. Jeśli nie to dodaje grupę z takim właśnie levelem do określonego rodzica. Równocześnie zapytanie pilnuje, aby nie dodać grupy zagnieżdżonej bardziej niż wymagamy to od $maxNest (poprzez $levelNum).

Usuwanie gałęzi

Ostatnim zapytaniem Jest zapytanie realizujące usuwanie gałęzi drzewa od wartości której id zostało podane:

DELETE
FROM groups
WHERE level LIKE '[*parent_cutLevel*]%' AND
cluster =[*parent_cluster*]