Tworzenie funkcji SQL

Wstęp #

System Navigator365 jest systemem typu no-code co oznacza, że do przygotowania aplikacji nie ma potrzeby tworzyć kod w jakimś języku programowania. Dodatkowo, w odróżnieniu od systemów low-code, nie ma potrzeby tworzenia wstawek w językach skryptowych takich jak SQL. Oczywiście nigdy nie przewidzimy wszystkich możliwych sytuacji więc może się zdarzyć, że dla pewnych specyficznych funkcjonalności potrzebne będzie dodanie takich wstawek. Jednak projektując system Navigator365 staramy się minimalizować takie przypadki. Tworząc nowe funkcjonalności na poziomie formularza, listy czy procesu (akcji) staramy się tak przygotować system by projektant aplikacji nie musiał uciekać się do metod low-codowych..

Z tego powodu wprowadziliśmy do systemu pewne ograniczenia na budowanie zapytań, których można użyć w systemie. Podstawowym ograniczeniem jest brak dostępu do tabel w bazie danych. Pisząc zapytanie możemy stosować mentionsy, czyli odwołania do pól na formularzu, operatory SQL-owe, itp. ale nie dostaniemy się do danych przechowywanych w tabelach. O ile w warunkach na formularzach nie stanowi to większego ograniczenia to może być poważnym ograniczeniem jeżeli chcemy utworzyć listę opartą na zapytaniu SQL lub użyć akcji Wykonaj SQL. Efektem działania tak skonfigurowanych elementów będzie komunikat „Brak danych”. W tym miejscu musimy najpierw zdefiniować funkcje SQL do której się odwołamy w zapytaniu.

Jaki jest powód takiego ograniczenia skoro i tak można napisać funkcje? Przede wszystkim wygoda, elegancja i bezpieczeństwo.

Wygoda – tworząc aplikację w Navigatorze365 i patrząc na konfigurację widzimy proste odwołanie do funkcji zamiast wielolinijkowego skryptu SQL. Takie rozwiązanie jest znacznie bardziej czytelne i po prostu lepiej wygląda w konfiguracji.

Elegancja – jeżeli piszemy dokumentację – lepiej opisać zestaw funkcji użytych w systemie z ich parametrami i zwracanymi wynikami niż podawać pełne skrypty. Co więcej, funkcji można użyć w wielu miejscach. Popatrzmy na prosty przykład – funkcja, która sprawdza poprawność danych. Używamy jej w wielu miejscach, działa dobrze ale nagle pojawia się potrzeba zmiany. Zamiast szukać po całym systemie – zmieniamy jedną funkcję.

Co do bezpieczeństwa – możemy ograniczyć dostęp do tworzenia funkcji do wąskiego grona osób administratorów oraz udostępnić tworzenie aplikacji dla szerszego grona operatorów. Mamy wtedy pewność, że osoba tworząca aplikacje nic nie popsuje w bazie danych i będzie tylko opierała się o mechanizmy stworzone w kontrolowany sposób przez administratora.

Dostęp do bazy danych #

Navigator365 używa bazy PostgreSQL. Razem z instalacją systemu oraz bazy instalowane jest narzędzie pgAdmin. Jest to narzędzie webowe, do którego można dostać się pod tym samym adresem co system, na przykład:

https://adres.mojego.serwera.pl/pgadmin/

Po otwarciu strony widzimy okno logowania

Dane do logowania zostaną przekazane administratorowi po instalacji systemu.

Po zalogowaniu widzimy stronę główną oraz listę serwerów. Z reguły będzie tu jeden serwer i jedna lub kilka baz danych w sytuacji gdy np. będziemy mieli postawione środowisko produkcyjne i testowe.

Na stronie głównej warto zwrócić uwagi na linki – są tu linki do dokumentacji PostgreSQL-a, strony domowej pgAdmin oraz do wsparcia społeczności PostgreSQL-a. W codziennej pracy, zwłaszcza gdy dopiero zaczynamy poznawać tę bazę, te zasoby będą bardzo pomocne.

Przed przystąpieniem do pracy należy znaleźć odpowiednie elementy systemu. W tym celu należy otworzyć gałęzie Databases > Nazwa_bazy > Schamas > Public i w tej lokalizacji mamy już takie elementy jak tabele, funkcje czy procedury. Na kolejnym zrzucie ekranu widzimy przykładową tabelę Documents z otwartym oknem Query tools gdzie możemy sprawdzać nasze zapytania

Tworzenie funkcji #

Jeżeli chcemy utworzyć nową funkcję to musimy poszukać gałęzi Functions i wybrać opcję Create > Function która znajduje się pod prawym klawiszem myszy.

W kolejnych zakładkach należy zdefiniować naszą funkcję.

Pierwszą rzeczą, którą musimy zrobić to nazwać naszą funkcję. Nazewnictwo jest dowolne ale dobrze jest się trzymać jakiegoś schematu.

Standardowo używamy takiego schematu:

_fn_get_formularz_cecha()

Podkreślenie oznacza, że jest to funkcja prywatna, dostępna tylko na naszym środowisku (w gotowym systemie jest już wiele funkcji zaczyanających się od fn i nie należy ich edytować. Get – pobieramy dane. Nazwa formularza i cecha określają czego funkcja dotyczy, np. _fn_get_contractor_nip_validation

W zakładce Definition definiujemy zwracany typ danych oraz parametry funkcji, np.

W zakładce Code mamy najważniejszą część naszej konfiguracji – definicje naszej funkcji. Wprowadzamy tu po prostu kod SQL który ma być wykonany

W przykładzie powyżej jest to kod do sprawdzania poprawności NIP-u. Jest on o tyle nietypowy, że nie odwołuje się do bazy danych jednak ze względu na złożoność algorytmu łatwiej go napisać w języku skryptowym niż za pomocą narzędzi no-codowych.

Poniżej pełny zapis funkcji:

DECLARE
    clean_nip TEXT;
    check_sum INT := 0;
    weights INT[] := ARRAY[6,5,7,2,3,4,5,6,7];
    i INT;
BEGIN
    -- Usuń wszystkie znaki niebędące cyframi
    clean_nip := regexp_replace(nip, '[^0-9]', '', 'g');
    
    -- Sprawdź czy ma dokładnie 10 cyfr
    IF length(clean_nip) <> 10 OR clean_nip !~ '^[0-9]{10}$' THEN
        RETURN FALSE;
    END IF;
    
    -- Oblicz sumę kontrolną (pierwsze 9 cyfr * wagi)
    FOR i IN 1..9 LOOP
        check_sum := check_sum + 
            (substring(clean_nip, i, 1)::INT * weights[i]);
    END LOOP;
    
    -- Sprawdź czy reszta z dzielenia przez 11 równa się 10. cyfrze
    IF (check_sum % 11) = substring(clean_nip, 10, 1)::INT THEN
        RETURN TRUE;
    END IF;
    
    RETURN FALSE;
END;

Ostatnią zakładką, na którą musimy zwrócić uwagę to zakładka Security. W niej znajdują się uprawnienia do funkcji.

Należy pamiętać, żeby dodać tu użytkownika navigator365_readonly. To właśnie ten użytkownik będzie uruchamiał naszą funkcję, jeżeli nie będzie miał uprawnień to nie uda nam się uruchomić tej funkcji.

Teraz wystarczy skonfigurować warunek poprawności na formularzu, np. tak:

Efekt można zobaczyć na poniższym zrzucie ekranu: 

Zapytania do tabel Navigatora365 #

Jeżeli chcemy dostać się do tabel bazy danych i wyciągnąć te dane np. do pokazania na liście powinniśmy znać strukturę bazy danych. Poniżej prezentujemy podstawowe tabele, których możemy użyć

• Documents: podstawowa tabela, w której przechowywane są wszystkie dokumenty systemu. Również dokumenty systemowe takie jak użytkownicy czy grupy użytkowników. Jeżeli chcemy wyodrębnić dokumenty oparte na konkretnym formularzu to musimy wyszukać dokumenty z odpowiednią wartością form_id.
• Forms: w tej tabeli przechowywane są definicje formularzy. Jeżeli chcemy ograniczyć dokumenty w naszej funkcji do jednego formularza to najlepiej dodać tę tabelę (join) i dodać warunek na guid formularza
• Document_choice_values: w tej tabeli przechowywane są wartości z atrybutów wyboru takich jak checkbox, radiobutton czy lista rozwijana. W tej tabeli mamy takie kolumny jak value_id, która wskazuje na id wybranego elementu, value_name_pl, która przechowuje wartość elementu czy document_id, która przechowuje id dokumentu, na którym znajduje się nasz atrybut. Warto zauważyć, że z poziomu tej tabeli nie jesteśmy w stanie w prosty sposób powiązać konkretnego rekordu z document_choice_values z konkretnym atrybutem na formularzu. Mamy kolumnę form_attribute_id ale musimy tu podać id atrybutu co nie jest podane wprost. Ułatwi nam to kolejna tabela
• Form_attributes: w tej tabeli przechowywane są konfiguracje atrybutów na formularzu. Dla nas najważniejsze jest to, że mamy tu kolumnę guid, którą łatwo odczytamy z formularza w systemie. Prostym zapytaniem SELECT id FROM form_attributes WHERE guid = 'guid z formularza’ znajdziemy id, które użyjemy do przeszukania tabeli document_choice_values

Atrybuty proste, takie jak tekst, liczby czy daty są zapisywane w kolumnach w tabeli Documents. Chcąc się do nich odwołać musimy wiedzieć, w której kolumnie są przechowywane i wprost wpisać to do zapytania – w podanym niżej przykładzie tak odwołujemy się do pól data poprzez kolumny date2 i date3.

Poniżej prezentujemy przykład funkcji, której można użyć do stworzenia listy. Użyliśmy tu tabel opisanych powyżej do wyszukania dokumentów z jednego formularza czy dołączenia wartości z atrybutów wyboru przechowywanych w tabeli document_choice_values

CREATE OR REPLACE FUNCTION public._fn_get_absences_conflict(IN data_od date,IN data_do date,IN user_id integer)
    RETURNS TABLE(id integer, signature text, date_from date, date_to date, user_conflict_id integer, user_name text)
    LANGUAGE 'plpgsql'
    VOLATILE
    PARALLEL UNSAFE
    COST 100    ROWS 1000 
    
AS $BODY$
BEGIN
RETURN QUERY
select 
    documents.id, 
    documents.signature, 
    documents.date2, 
    documents.date3,
    document_choice_values.value_id, 
    document_choice_values.value_name_pl 
from documents 
    left join forms on documents.form_id=forms.id 
    left join document_choice_values on document_choice_values.document_id=documents.id
where 
documents.is_deleted is false
and forms.guid='b3416b30-e276-469e-b8ec-c0a631d345a8'
and document_choice_values.form_attribute_id=(select form_attributes.id from form_attributes where guid='a83a8615-4ecf-4e3a-bad8-193dc2766696')
and document_choice_values.value_id <> user_id
and 
    (
     (date2<data_od and  date3>data_do)
     OR
     (date2>data_od and date2<data_do)
     or
     (date3>data_od and date3<data_do)
    );
    
END;
$BODY$;