Komunikacja bazy danych z Backendem - Koncept

[StanislawHornaGitHub]

Zalety

Bezpieczeństwo

Aby podnieść poziom zabezpieczeń zdecydowaliśmy, że backend będzie korzystać wyłącznie z widoków. Użytkownik wykorzystywany po stronie backendu do autoryzacji w bazie danych będzie posiadał uprawnienia jedynie do widoków utworzonych w tej bazie. (Istnieje możliwość rozszerzenia tego konceptu, w taki sposób aby Backend korzystał z 2 różnych kont użytkownika bazy danych - jedno konto do odczytywania informacji zawartych w bazie, drugie do wprowadzania zmian w bazie)

Zwracanie wyników

Flask traktuje widoki tak samo jak tabele, więc operacje na API będą wykonywane w taki sam sposób jak założono na początku. Dodatkowo w przypadku konieczności JOIN'ów wielu tabel operacje na widokach uproszczą logikę. PRZYKŁAD: W najgorszym wypadku jaki odkryliśmy tj "Wystawianie faktury" potrzebujemy następujących informacji:

• nabywcy <- dane customera (w wielu sklepach czy hotelach na fakturach poza imieniem i nazwiskiem jest też uwzględniony adres)
• co kupił <- jakie pokoje w jakich terminach od do
• poprzednie faktury <- żeby móc wygenerować numer następnego dokumentu

W opisanym powyżej przypadku z wykorzystaniem tabel musielibyśmy użyć JOIN na następujących tabelach (8):

• Customers
• Addresses
• Reservations
• dict_reservation_status
• ReservationRooms
• Room
• RoomType
• Invoice

W przypadku użycia widoków musimy sprzężyć 4 widoki, żeby osiągnąć ten sam efekt:

• reservation_view
• room_view
• customer_view
• invoice_view

Modyfikacja i wprowadzanie danych

Instrukcje INSERT, UPDATE, DELETE będą również wykonywane w oparciu o widoki. Pozwoli to na dodatkową weryfikację po stronie bazy danych, czy dana operacja jest dozwolona. PRZYKŁAD:

1. Brak możliwości wprowadzenia rezerwacji, która zawiera pokój w statusie innym niż "dostępny"
2. Brak możliwości edycji rezerwacji, które się już odbyły tj. aktualny_czas > Reservations.End_date.
3. Brak możliwości usuwania rezerwacji, które się już odbyły tj. aktualny_czas > Reservations.End_date.
4. Edycja faktury (tzw. "wystawienie korekty") będzie wymagało ustawienia odpowiedniego statusu faktury.
5. Brak możliwości usunięcia faktury która została opłacona.
6. Automatyczne wprowadzanie rekordu to tabeli Addresses i łączenia go za pomocą odpowiedniego ID z tabelą Customers.

Implementacja

Niestety w przypadku PostgreSQL opcja przy tworzeniu widoków WITH CHECK OPTION znana z MS SQL działa tylko i wyłącznie w przypadku, gdy widok korzysta z tylko jednej tabeli. W pozostałych przypadkach pozostają jedynie triggery i funkcje. Natomiast zastosowanie bardziej złożonych obiektów pozwala rozwiązać dodatkowe utrudnienia bezpośrednio w bazie danych np. generowanie Address ID przy wprowadzaniu nowego Customera.

Views (view_type) #12 :

• customers_view <- zawiera tabele:
  • Customers,
  • Addresses
• reservations_view <- zawiera tabele:
  • Reservations,
  • ReservationRooms,
  • dict_reservation_status
• rooms_view <- zawiera tabele:
  • Room,
  • RoomType,
  • dict_room_status
• invoices_view <- zawiera tabele:
  • Invoices,
  • dict_invoice_status
• admins_view <- zawiera tabele:
  • Admins

Triggers #14

Każdy widok musi mieć zaimplementowane 3 triggery (technicznie rzecz biorąc, triggerów potrzebują tylko te widoki, które korzystają z więcej niż 1 tabeli)

Trigger_types:

• INSTEAD OF INSERT (nazwa w bazie danych ioi)
• INSTEAD OF UPDATE (nazwa w bazie danych iou)
• INSTEAD OF DELETE (nazwa w bazie danych iod)

Functions #13

Każdy z wcześniej wymienionych triggerów potrzebuje dedykowanej funkcji, która będzie on wyzwalać. Funkcje nie są bezpośrednio powiązane z konkretnym widokiem czy tabelą, więc żeby utrzymać porządek musimy trzymać się nazewnictwa:

_ # Przykładowy kod SQL CREATE OR REPLACE FUNCTION admins_view_insert() RETURNS TRIGGER AS $$ BEGIN IF NEW.isActive = true THEN INSERT INTO Admins (user_name, password, isActive, E_mail, Phone_num) VALUES (NEW.user_name, NEW.password, NEW.isActive, NEW.E_mail, NEW.Phone_num); RETURN NULL; ELSE RAISE EXCEPTION 'isActive must be true for insertion'; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ioi INSTEAD OF INSERT ON admins_view FOR EACH ROW EXECUTE FUNCTION admins_view_insert();

[GringoXY]

Podział na użytkowników bazo danowych spoko. Sam tak w jednej apce robiłem, że zamiast domyślnie wziąć super usera to stworzyłem takiego co ma minimum potrzebnych uprawnień. Obsługa widoków zamiast bezpośrednio tabel, hm, okej, pod warunkiem, że ORM pozwala na mapowanie rezultatu z widoki na model/klasę po stronie Pythona

[StanislawHornaGitHub]

https://postgrest.org/en/v12/