Zestawienia: nieformalnie o podstawach języka SQL - część 1

Przez kilka ostatnich dni intensywnie pracowałem nad mechanizmem zestawień w QAZP2. Kod można już pobrać z repozytorium, ale nowa wersja nie jest wydana. Po tych zmianach użytkownik może definiować zestawienia, a dokładnie rzecz ujmując - polecenia SQL, w sposób bardzo zbliżony do tego, jak się to robi w takich programach jak Microsoft Access, albo LibreOffice Base. Czyli do wyszukania pożądanych informacji teoretycznie nie jest potrzebna znajomość języka SQL, bo wszystkie parametry zestawienia - takie jak wyświetlane kolumny, albo warunki ograniczające wyniki, można wyklikać, przy pomocy graficznego interfejsu użytkownika.

Jednak w praktyce okazuje się, że przy tworzeniu zestawień podstawowa znajomość SQL jest jak najbardziej na miejscu, szczególnie, gdy planujemy utworzenie jakiegość skomplikowanego podsumowania. I o tych podstawach, które moim zdaniem pokrywają 90% potrzeb przeciętnego użytkownika bazy danych, jest ten artykuł.

Wyobraźmy sobie bazę danych w której znajdują się dwie tabele: Stanowiska oraz FaktyKulturowe. Tabela Stanowiska ma następujące kolumny (atrybuty):

• id - niepowtarzalny identyfikator stanowiska,
• obszar_azp - nr obszaru AZP,
• nr_azp - nr stanowiska w ramach obszaru AZP,
• data_badan - data przeprowadzonych badań.

Tabela FaktyKulturowe zawiera informacje o śladach pobytu człowieka (np. fragmentach ceramiki, narzędziach, itp.) - przesłankach do uznania jakiegoś miejsca za stanowisko archeologiczne i określenia jego chronologii. Tabela ma zdefiniowane następujące kolumny ( atrybuty):

• id - niepowtarzalny identyfikator faktu
• stanowisko - identyfikator stanowiska, którego dotyczy fakt. Pojedyncze stanowisko może mieć przypisany jeden lub więcej faktów, lub nie mieć przypisanych faktów wogóle.
• okres - okres dziejów - na przykład: Neolit, Nowożytność, epoka Żelaza, itd.,
• kultura - jednostka kulturowa - na przykład Kultura Pucharów Lejkowatych,
• funkcja - funkcja stanowiska - np. osada, cmentarzysko, obozowisko, itd.
• liczba fragmentów ceramiki

Szablon polecenia wyszukującego dane w bazie SQL można przedstawić następująco:

SELECT 

      lista_atrybutów 

FROM 

      lista_tabel_oraz_widoków 

[WHERE 

      lista_warunków

] 

[GROUP BY 

      lista_atrybutów_grupujących].

Postawą każdego zestawienia są tabele (conajmniej jedna), które przeszukuje się pod kątem określonych kryteriów i z których pobiera się wartości, które następnie są wyświetlane użytkownikowi. W poleceniu wyszukującym dane (w skrócie w poleceniu SELECT) tabele, które są potrzebne do wykonania zestawienia określa się w miejscu, które oznaczyłem jako lista_tabel_oraz_widoków.

Na takiej liście może znajdować się tylko jedna tabela i wtedy za wyrazem FROM wystarczy wstawić jej nazwę. Sprawa komplikuje się, gdy zestawienie dotyczy więcej niż jednej tabeli. Wyobraźmy sobie przypadek, w którym chcemy wyszukać wszystkie osady na obszarze AZP 55-12. Informacja o funkcji stanowiska jest zapisana w tabeli FaktyKulturowe. A informacja o obszarze AZP, do którego należy Stanowisko w tabeli Stanowiska. Stąd prosty wniosek, że w zestawieniu musimy użyć obu tabel.

Aby to zrobić musimy określić w jaki sposób stanowisko jest połączone z faktami kulturowymi. Tabela FaktyKulturowe ma atrybut stanowisko, który zawsze ma wartość odpowiadającą identyfikatorowi stanowiska, zapisywanego w atrybucie id w tabeli Stanowiska. Dysponując tą wiedzą możemy zbudować listę tabel:

... FROM Stanowiska S JOIN FaktyKulturowe F ON S.ID = F.STANOWISKO ...

W ten prosty sposób przekazaliśmy, że wszystkie potrzebne informacje znajdują się w połączonych tabelach Stanowiska oraz FaktyKulturowe. Tak utworzona lista ma jedną wadę - stanowiska, które nie mają przypisanych faktów kulturowych zostaną pominięte. Czasami ma to uzasadnienie, a czasami nie. Aby spowodować, że Stanowisko zostanie wyświetlone nawet, jeżeli nie ma faktów kulturowych, powyższy fragment należałoby przepisać do takiej postaci:

... FROM Stanowiska S LEFT OUTER JOIN FaktyKulturowe F ON S.ID = F.STANOWISKO ...

Zamiast pisać JOIN, użyliśmy wyrażenia LEFT OUTER JOIN. Jeżeli dla danego stanowiska nie ma w tabeli FaktyKulturowe takiego wiersza, który by zawierał identyfikator tego stanowiska, to wartości z tabeli Stanowiska i tak zostaną przeanalizowane i ewentualnie wyświetlone. Jeżeli musimy dołączyć kolejne tabele do listy, postępujemy w analogiczny sposób.

W zestawieniu zazwyczaj chcemy wyświetlić tylko informacje z wybranych kolumn tabeli. To, które wartości powinny być wyświetlone w zestawieniu określa się w miejscu, które oznaczyłem jako lista_atrybutów, czyli innymi słowy mówiąc lista nazw kolumn tabeli. 

SELECT S.ID, OBSZAR_AZP, FUNKCJA FROM Stanowiska S LEFT OUTER JOIN FaktyKulturowe F ON S.ID = F.STANOWISKO

Polecenie powyżej tworzy listę stanowisk, na której każde stanowisko może mieć określoną funkcję. Jeżeli funkcja jest nieokreślona, to pole w zestawieniu będzie puste. Lista atrybutów w zestawieniu jest następująca: S.ID - identyfikator stanowiska, OBSZAR_AZP, FUNKCJA. Skąd wiadomo, że S.ID oznacza identyfikator stanowiska, a nie identyfikator faktu kulturowego. Stąd, że użyliśmy "skrótu" (aliasu) S wymienionego także na liście tabel, gdzie przypisaliśmy literę 'S' do tabeli Stanowiska. 

A jak powinniśmy zażądać w poleceniu SQL wyświetlenia identyfikatora faktu kulturowego? Podświetl zaczerniony fragment aby porównać swoją odpowiedź z prawidłowym rozwiązaniem: F.ID 

Część druga