AppMaster oferuje rozbudowane możliwości pracy z bazą danych. Na przykład za pomocą bloku Szukaj można znaleźć potrzebne dane, dołączyć do nich powiązane tabele, posortować je we właściwej kolejności itp. Jednak w niektórych sytuacjach może to nie wystarczyć i wtedy pojawia się blok SQL Exec ratunek. Pozwala na uruchamianie dowolnych zapytań do bazy danych wykorzystując pełną moc SQL.
Rozważmy działanie bloku na przykładzie bazy danych zawierającej katalog książek.
Wykonajmy małe prace przygotowawcze. Konieczne jest stworzenie procesu biznesowego, który w najprostszej formie umożliwiłby wysyłanie żądań, a także odbieranie ich wyników.
Konieczne będzie także utworzenie punktu końcowego, który umożliwi dostęp do tego procesu biznesowego.
Na początkowym etapie to powinno wystarczyć. Możesz opublikować swoją aplikację i przejść do testów. W tym celu bardzo wygodne jest użycie Swaggera, który jest tworzony automatycznie podczas publikowania.
Używamy prostego zapytania, które powinno pobrać wszystkie książki z bazy danych.
WYBIERZ * Z public.book
Należy pamiętać, że sama tabela (jak wszystkie inne tabele utworzone w edytorze bazy danych) posiada przedrostek wskazujący schemat - public.
Możesz sprawdzić, czy żądanie zostało pomyślnie zakończone i otrzymano wynik.
Problem w tym, że bardzo trudno jest dostrzec odpowiedź w tej formie. Możliwym rozwiązaniem jest niewielka modyfikacja żądania i pozostawienie w nim tylko niezbędnych pól – na przykład tytułu książki i liczby stron. Poza tym rozsądnie byłoby ustalić limit, nie żądać wszystkich książek z bazy, ale ograniczyć się do dziesięciu.
WYBIERZ nazwę, strony Z public.book LIMIT 10
Znacznie lepiej, ale nadal nie nadaje się do prawdziwego użytku. Przecież w procesie biznesowym trzeba nie tylko otrzymać żądanie, ale także zrobić coś z jego wynikiem. W tym celu nie wystarczy mieć wynik w formie tekstowej; trzeba go zamienić na model nadający się do dalszego użytku.
Aby to zrobić, wróćmy do procesu biznesowego i dopracujmy go trochę. W bloku końcowym dodamy nową zmienną - tablicę modeli książek.
Będziesz także potrzebował bloku, który przekonwertuje otrzymany w wyniku żądania kod JSON na model. Deserializuj JSON do modelu.
Powtórzmy poprzednią prośbę i upewnijmy się, że wynik stał się znacznie bardziej odpowiedni, zarówno dla percepcji wzrokowej, jak i do dalszego wykorzystania w BP.
Teraz możemy przejść do bardziej złożonej logiki. Stwórzmy proces biznesowy, który:
- Otrzymuje tytuł książki jako dane wejściowe.
- Określa, do której kategorii (gatunku) należy.
- W rezultacie zwraca 3 losowe książki z tej samej kategorii (w tym przypadku książka z żądania nie powinna się wśród nich znajdować)
W tym celu stworzymy nowy proces biznesowy, który łączy wyszukiwanie przy użyciu standardowych bloków i wykorzystanie zapytań SQL.
Pierwszy blok polega na znalezieniu książki po jej tytule, a także ustaleniu, do jakich kategorii ona należy. W tym celu używamy bloku wyszukiwania z następującymi parametrami:
- _With = Kategorie - oprócz samej książki wynik zapytania wymaga informacji z powiązanej tabeli kategorii.
- _Limit = 1 - należy znaleźć tylko jedną książkę.
- _Ilike = False - nazwa musi dokładnie odpowiadać żądanej.
- Nazwa - indeks tytułu książki przekazywany z bloku Start.
Korzystając z bloku Array Element o indeksie 0, z wyniku pobieramy pierwszą (i jedyną) książkę.
Książka może należeć do kilku różnych kategorii jednocześnie i w tym przypadku każda z nich będzie nam odpowiadać. Można go wybrać losowo za pomocą bloku elementów losowych
Po tym mamy już wszystkie niezbędne dane, pozostaje jedynie stworzyć samo żądanie, które może wyglądać tak:
WYBIERZ * Z public.book GDZIE id IN (WYBIERZ rel1_id Z public.book_categorys_category_books_pivot GDZIE book_categorys_category_books_pivot.rel2_id = X) ORAZ id <> Y ZAMÓWIENIE PRZEZ losowe() LIMIT 3
Gdzie X to identyfikator kategorii książki, a Y to identyfikator samej książki.
Należy pamiętać, że to zapytanie zawiera podzapytanie. Najpierw z tabeli book_categorys_category_books_pivot (służy do przechowywania informacji o relacjach pomiędzy dwiema tabelami) znajdują się wszystkie identyfikatory książek odpowiadające wybranej kategorii. Następnie wykonywane jest zapytanie, które znajduje losowe 3 książki pasujące do określonego zakresu, z wyłączeniem identyfikatora książki, której tytuł został pierwotnie przekazany do procesu biznesowego.
Aby dokładniej zapoznać się ze strukturą bazy danych projektu, możesz skorzystać z przycisku Otwórz bazę danych w ustawieniach Wdrażaj plany.
Umożliwi to otwarcie bazy danych w edytorze i uzyskanie bezpośredniego dostępu do przeglądania i edycji danych. Należy jednak zachować ostrożność i liczyć się z tym, że zmiana samej struktury danych w edytorze uniemożliwi dalszą publikację projektu.
Wróćmy do tworzenia procesu biznesowego. Konieczne jest dokończenie kompilacji żądania i w tym celu przekonwertowanie identyfikatora książki i kategorii z liczby całkowitej na ciąg znaków, a także złożenie końcowego żądania za pomocą bloku Concat Strings (Multiple).
Ostatnim krokiem jest wykonanie zapytania, przekształcenie wyniku w model i przesłanie go do bloku End jako wyniku zapytania.
Możesz zapisać zmiany, utworzyć punkt końcowy, opublikować projekt i sprawdzić, czy żądanie działa poprawnie. W tym przypadku jeden blok SQL Exec ze złożonym zapytaniem złożonym został wykorzystany do zastąpienia wielu innych bloków i uproszczenia struktury procesu biznesowego.
Użycie bloku SQL Exec nie ogranicza się do pobierania danych i może być używane w wielu różnych scenariuszach. Przyjrzyjmy się bliżej kilku dodatkowym opcjom.
- Liczenie liczby komentarzy dla książki o id=X
WYBIERZ LICZBĘ(id) Z public.comment GDZIE book_id = X - Obliczenie średniej oceny książki z uwzględnieniem wszystkich ocen podanych w komentarzach:
WYBIERZ AVG(rate) Z public.comment GDZIE book_id = X - Usuwanie wszystkich komentarzy napisanych przed 2023 rokiem (można wykorzystać np. do szybkiego wyczyszczenia logu).
USUŃ * Z public.comment GDZIE utworzono_at < '2023-01-01'
Podsumowując, warto zaznaczyć, że dla poprawy bezpieczeństwa do bloku SQL Exec dodano filtr dla niebezpiecznych operacji, które mogą prowadzić do zmiany schematu.
Funkcje CREATE/ALTER/DROP/TRUNCATE dla TABLE|COLUMN|INDEX|CONSTRAINT|SEQUENCE|SCHEMA|DATABASE są wyłączone przez filtr, jeśli aplikacja jest hostowana na serwerach AppMaster. W przypadku hostingu lokalnego - domyślnie wszelkie żądania są dostępne bez ograniczeń.
