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

Qt4 : wyświetlanie danych w tabeli

W implementacji mechanizmu zestawień w QAZP2, nad którą ostatnio pracowałem, do wyświetlania i edycji danych wykorzystałem tabele. W tym artykule chciałbym krótko przybliżyć zasady wyświetlania informacji przy pomocy komponentu QTableView.

W Qt4 postawą działania komponentów wyświetlających dane w postaci tabelarycznej są implementacje tak zwanego modelu tabeli, a dokładnie klasy QAbstractTableModel. Komponent QTableWidget używa swojej domyślnej implementacji, której nie sposób zmienić, ale za to zdefiniowanie nowej tabeli jest bardzo łatwe. Wystarczy podać jej wstępne wymiary, to znaczy liczbę wierszy oraz kolumn, a następnie wprowadzić wartości do komórek. Jeżeli zachodzi taka potrzeba można te wymiary zmieniać dodając kolejne kolumny i wiersze, albo je usuwając. Zastosowanie QTableWidget ma tę zaletę, że chcąc wyłącznie wyświetlać informacje można szybko osiągnąć zamierzony efekt. Schody zaczynają się, gdy dopuszczamy również edycję danych, bo wtedy trzeba śledzić za pomocą sygnałów zdarzenia w poszczególnych komórkach. W pewnym momencie stało się dla mnie denerwujące pisanie pętli, które na podstawie danych źródłowych, dla każdej komórki tworzyły odrębny obiekt klasy QTableWidgetItem zawierający wartość, która ma być wyświetlana, co sprawiało, że przy bardziej skomplikowanych wymaganiach kod stawał się nieczytelny, a na dodatek za każdym razem trzeba pętlę definiować od nowa. Dlatego osobiście preferuję stosowanie klasy QTableView, która tym różni się od QTableWidget, że do jej używania konieczne jest własnoręczne wskazanie modelu tabeli. 

Aby zastosować QTableView w pierwszej kolejności należy zdefiniować nowy model tabeli. Polega to na utworzeniu nowej klasy, która dziedziczy po klase QAbstractTableModel, w której zaimplementowane są conajmniej trzy metody: rowCount(), columnCount() oraz data(). Znaczenia pierwszych dwóch można się łatwo domyślić: rowCount() oblicza i zwraca liczbę wierszy, a columnCount() liczbę kolumn. Natomiast metoda data(indeks, rola) powinna określać wartość, która ma być wyświetlona w komórce określonej przez indeks. Tu uwaga - metoda data() może być wywoływana wielokrotnie dla tej samej komórki.

Przykład

class NowyModel(QAbstractTableModel):

    def __init__(self, dane): # dane reprezentuje dwuwymiarowa lista

         QAbstractTableModel.__init__(self)

         self._dane = dane

    def rowCount(self, parent=QModelIndex()):

        return len(self._dane)

    def columnCount(self, parent=QModelIndex()):

        return 3 #  z góry zakładamy, że tabela ma mieć 3 kolumny

    def data(self, indeks, rola=Qt.DisplayRole):

        if rola == Qt.DisplayRole:

            r, c = indeks.row(), indeks.column() # współrzędne komórki

            if c < 2:

                return self._dane[r][c]

            elif c == 2:

                return "< %d;  %d>" % (self._dane[r][0], self._dane[r][1])

         return None # wartość zwracana, gdy nie zostały spełnione inne warunki

def nowaTabela():

    tv = QTableView()

   dane = [[10, 20], [-8, -3], [0, 5]]

   model = NowyModel(dane)

   tv.setModel(model)

Dane źródłowe składają się z trzech wierszy, z których każdy zawiera dwie wartości oznaczające początek i koniec jakiegoś zakresu liczb. Aby osiągnąć taki efekt, że w każdym wierszu tabeli w pierwszej kolumnie będzie wyświetlany począte zakresu, w drugiej koniec, a w trzeciej cały zakres (sformatowany jako string), została odpowiednio zaimplementowana metoda data(). To na co warto zwrócić uwagę, bo często prowadzi do błędów w wyświetlaniu danych, to drugi parametr o nazwie rola. QTableView wywołuje data() nie tylko po to żeby wyświetlić wartość w komórce, ale także pobrać dane do umieszczenia w edytorze, jeżeli modyfikowanie informacji jest dopuszczalne. Do odróżnienia tych sytuacji służy właśnie wspomniana rola. Jeżeli data() jest wywołana w celu wyświetlania to rola jest równa Qt.DisplayRole, natomiast gdy zachodzi edycja to rola jest równa Qt.EditRole.

Model tabeli można udoskonalić implementując kolejne metody. Jedną z nich jest headerData(sekcja, orientacja, rola), która określa wartości, które mają być wyświetlane w nagłówku. Jej definicja jest podobna do metody data(). Jeżeli orientacja == Qt.Horizontal to sekcja wskazuje na kolumnę tabeli, natomiast jeżeli orientacja == Qt.Vertical  to sekcja określa numer wiersza.  Nagłówki można także modyfikować, jeśli dopuszcza to nasza implementacja i na tą okoliczność należy sprawdzać, podobnie jak w przypadku metody data() wartość parametru rola. 

Przykład

class NaglowkiModel(NowyModel):

    def __init__(self, dane):

        NowyModel.__init__(self, dane)

        self._naglowki = ["Początek", "Koniec", "Zakres"]

    def headerData(sekcja, orientacja, rola=Qt.DisplayRole):

        if rola == Qt.DisplayRole and orientacja == Qt.Horizontal:

            return self._naglowki[sekcja]

        return None

   

Implementacja modelu tabeli do wyświetlania danych w Qt4 jest bardzo prosta. Ma jeszcze tą zaletę, że po zdefiniowaniu go w jednym miejscu można go stosować wielokrotnie, jeżeli sytuacja na to pozwala. 

W kolejnym artykule spróbuję przedstawić jak używa się QTableView, gdy ma być dopuszczalna edycja wyświetlanych danych.

Golang : import danych AZP

Jednym z ważniejszych aspektów związanych z projektem QAZP było przygotowanie narzędzia, które (w miarę) bezboleśnie pozwoli na "przerzucenie" danych utworzonych w programie AZPMAX do relacyjnej bazy SQL. Oczywiście pierwszą decyzją był wybór języka programowania, który z wiadomych względów padł na Jave, choćby z tego powodu, że mam sporo doświadczeń związanych z tworzeniem narzędzi obsługujących zarówno DBF-y jak i bazy danych. Niestety musiałem z tego zrezygnować, bo mimo wcześniejszych pozytywnych wyników, tym razem sterownik do SQLite odmówił współpracy z danymi przestrzennymi. Przy próbie inicjalizacji rozszerzenia Spatialite JVM przestawała działać. W związku z tym zdecydowałem się stworzyć narzędzie do importu w języku Golang, który od kilku lat jest rozwijany przez dobrze znaną firmę Google. Ten wybór też ma uzasadnienie, biorąc pod uwagę, że:

1. mam już pewne doświadczenie związane z tworzeniem oprogramowania w Go,
2. dobra obsługa baz sql, w tym Sqlite.

Na minus trzeba zaliczyć brak obsługi DBF-ów, ale to póki co rozwiązałem kopiując dane z plików CSV, a do tego formatu można łatwo wyeksportować DBF, posługując się na przykład arkuszem kalkulacyjnym.
Golang / Go ma kilka interesujących właściwości, które przyniosły mu już dużo rozgłosu i powodują, że język dość szybko zyskuje na popularności. Jest statycznie typowany, ale określenie typu zmiennej można przerzucić na kompilator. Wydaje mi się, że nie można go uznać za niskopoziomowy, bo nie umożliwia na przykład wykorzystywanie assemblera, natomiast różnorakie porównania wykazują, że w wielu przypadkach szybkością dorównuje programom pisanym w C / C++. Nad tymi dwoma ma przewagę (przec co niektórych uznawaną za wadę) automatycznego zarządzania pamięcią (garbage collector). Programy można tworzyć w modelu strukturalnym - do reperentowania danych używa się list, map, i struktur bardzo podobnych do tych stosowanych w C / C++, albo takim, który oferuje abstrakcję i polimorfizm (ale bez dziedziczenia) znany z paradygmatu programowania obiektowego. Realizuje się to przy pomocy tak zwanych interfejsów, które nieco przypominają konstrukcje o tej samej nazwie, które stosuje się w Javie. W Golang interfejs jest pewnym kontraktem, który umożliwia tworzenie polimorficznych obiektów. Natomiast inny jest sposób ich stosowania. O ile w Javie, jawnie trzeba podać, że klasa jest implementacją, o tyle w przypadku Golang jest to automatycznie rozpoznawane przez kompilator. Każdy typ danych, dla którego zaimplementowano funkcje wskazane w definicji interfejsu staje się automatycznie jego implementacją. W narzędziu do importu, o którym mowa został zdefiniowany interfejs Tabela z jedną metodą Params, która zwraca tablicę wartości dowolnego typu. Każda struktura, która implementuje interfejs może być zastosowana w metodzie dodaj(ps *sql.Stmt, t Tabela, spr bool), która wykonując metodę Params pobiera wartości ze struktury w takiej kolejności, które powinny być zastosowane jako parametry polecenia SQL reprezentowanego przez inny interfejs Stmt zdefiniowany w bibliotece standardowej Golang.
W tym krótkim opisie trudno wskazać wszystkie właściwości Go, ja skupiłem się tylko na tych, które były istotne z uwagi na problem, który musiałem rozwiązać. W następnym poście opisałem, jak "zmusić" sterownik Go do obsługi danych geometrycznych zapisanych w przestrzennej bazie danych.

Klasyfikacja funkcjonalno-kulturowo-chronologiczna: wykazy

Ostatnio sporo się działo w kwestii rozwoju QAZP2. Z radością donoszę, że wtyczka ma już zaimplementowane wszystkie funkcje, których wymagał program badawczy, w ramach którego aplikacja jest realizowana. Tym samym na podstawie zawartości bazy danych można wygenerować karty AZP zgodne ze specyfikacją Ministra Kultury i Dziedzictwa Narodowego. 

Jednym z najważniejszych elementów karty AZP jest określenie dla znalezionych śladów działalności człowieka  konotacji kulturowo-historycznej, czyli innymi słowy dokonanie klasyfikacji funkcjonalno-kulturowo-chronologicznej w oparciu o zebrane fakty, czyli na przykład fragmenty ceramiki, znalezione narzędzia i inne przedmioty, które archeologowi pozwalają określić wiek badanych osad, cementarzysk, itp. Aby przekaz był uniwersalny i czytelny dla każdego naukowca w Polsce do jego formułowania posługujemy się wykazami pojęć opracowanymi w Narodowym Instytucie Dziedzictwa. Taka standaryzacja ma niebagatelne znaczenie, gdyż czyni bazę danych uniwersalnym źródłem informacji nie tylko w ramach projektu, z którego pochodzi, ale dla wszystkich zainteresowanych analizą wydarzeń, które miały miejsce w przeszłości na terenach, z których pochodzą zebrane dane. 

Opracowano trzy zbiory zawierające pojęcia służące do określania chronologii (okresu dziejów, z których pochodzi obiekt osadniczy - na przykład epoka kamienia, epoka brązu, średniowiecze, nowożytność); jednostki kulturowej, która informuje, że dany przedmiot wykazuje podobieństwo w ramach pewnej grupy wskaźników; funkcji - określenie roli obiektu - na przykład cmentarzysko, osada, grodzisko, itd. Każdy z wykazów jest zapisywany w osobnej tabeli, gdyż ich struktura jest odmienna. Konstrukcja schematu bazy danych obsługiwanego przez QAZP2 wynika z tego, że ma ona dwa podstawowe zadania: 

1. dostarczać informacje w celu ich wyświetlania,
2. dostarczać informacje w celu ich przetwarzania i analizy.

Określenia każdej z właściwości (chornologii, jednostki kulturowej i funkcji) można dokonać na kilku poziomach szczegółowości. Na przykładzie chronologii wyróżnić można takie dwa: pierwszy poziom - epoka - na przykład epoka kamienia, epoka żelaza oraz drugi poziom - okres epoki - paleolit (czyli starsza epoka kamienia), II okres epoki brązu, Halsztad C (okres epoki żelaza). Użytkownik zgodnie ze swoimi umiejętnościami, wiedzą i możliwościami określa w ten sposób chronologię dla każdego znalezionego przedmiotu używając pojęć z pierwszego poziomu (mniej szczegółowo) albo drugiego (bardziej szczegółowo). Ma to znaczenie z uwagi na analizę danych, gdyż okres wpływów rzymskich różni się od okresu halsztackiego. Natomiast w przypadku wyświetlania, zwłaszcza w formie karty KEZA, która w dobie współczesnych technologii jest już tylko wyrazem zacofania polskich służb konserwatorskich, jest mniej znaczące. Stąd w każdej tabeli wykazów znajduje się pole skrót zawierajace wartość, która potem jest umieszczana na wydruku. Z kolei w kolumnie NAZWA zapisuje się wartości, które nie są pełną nazwą okresu chronologicznego, ale wyświetlona jest zrozumiała dla archeologa. Na przykład pisząc wartość HA C domyślamy się, że chodzi o Epokę żelaza - okres halsztacki C, z kolei nazwa Grupa mątewska wskazuje na neolityczną kulturę pucharów lejkowatych i to jest trzeci poziom dokładności w określeniu jednostki kulturowej. 

Taka konstrukcja jest wygodna w analizie danych, gdyż wszukując wszystkie ślady związane z okresem Neolitu nie trzeba znać ich szczegółowego oznaczenia, czyli zbędna jest wiedza, czy osada wiąże się z kulturą pucharów lejkowatych, kulturą amform kulistych, czy grupą radziejowską, gdyż wszystkie mieszczą się w zbiorze Neolit. Z kolei szukając faktów dotyczących grupy mątewskiej nie zajmujemy się kulturą ceramiki wstęgowej, ani pozostałymi grupami kultury pucharów lejkowatych.