Dzisiaj coś łatwego, przyjemnego i przydatnego w życiu.

Widok – kto zacz?

Widok, to mówiąc najprościej rodzaj aliasu do zapytania SQL. Załóżmy, że mamy tabelę:

Listing 1. Prosta tabela z danymi

CREATE TABLE IF NOT EXISTS data_points
(
    sensor        		varchar,
    measure_time	DATE,
    value			REAL,
    PRIMARY KEY (city, measure_time)
);

I w wielu miejscach mamy do czynienia z zapytaniem takim, jak to poniżej

Listing 2. Nasze zapytanie

SELECT sensor, round(avg(value)::numeric, 2)) AS value 
	WHERE date_part('year', measure_time) BETWEEN 2022 AND 2024 
	GROUP BY sensor;

to możemy je zastąpić widokiem właśnie. By to zrobić wystarczy utworzyć takowy widok:

Listing 3. Nasz widok

CREATE OR REPLACE VIEW avg_value AS
	SELECT sensor, round(avg(value)::numeric, 2)) AS value
		WHERE date_part('year', measure_time) BETWEEN 2022 AND 2024 
		GROUP BY sensor;

Taki widok może być użwany tak samo, jak tabela:

Listing 4. Przykładowe użycie widoku

SELECT * from avg_values 
	WHERE sensor like 'Wrocław%' 
	AND value > 1.1;

Oznacza to możliwoć dodawania kolejnych ograniczeń, agregatów czy możliwość wyboru tylko część kolumn. Ogólna zasada działania jest taka, w uproszczeniu, że wykonując zapytanie na widoku jest tożsame z podzapytaniem. Oczywiście to nie wszystko, ale dla nas w zupełności wystarczy.

Inne rodzaje widoków

Postgres poza zwykłymi widokami pozwala na stworzenie:

  • widoków zmaterializowanych – czyli takich, które w momencie utworzenia wykonają zapytanie, a jego wynik zapiszą w tabeli. W ten sposób w przyszłości będziemy odpytywać dane, z okreslonego momentu w czasie.
  • widoków tymczasowych – czyli takich, które są dostepne tylko w konkretnej sesji użytkownika i po jej zakończeniu zostaną usunięte. Nie są też dostepne dla innych sesji i użytkowników.

Zastosowania

Widoki możemy traktować jako interfejs odczytu dla naszej aplikacji. Dzięki temu rozdzielamy model składowania danych (tabele) od sposobu w jaki je odczytujemy. Możemy dowolnie modyfikować model składowania wraz z rozwojem aplikacji, a klienci nie zauważą zmian. Może kojarzyć się to z podejściem znanym CQRS i coś w tym jest.

Dodatkowo zwykłe (i zmaterializowane) widoki moga być dostepne tylko dla wybranych użytkowników. Możemy zatem już na poziomie bazy danych ogranicznyć dostęp do niektórych informacji. Jednocześnie modelując encje JPA powinniśmy ograniczyć możliwość aktualizacji i zapisywania wszytkich kolumn:

Listing 5. Encja reprezentująca widok avg_values

@Entity
@Table(name="avg_values")
public class AvgValue{
	
	@Id
	@Column(insertable = false, updatable=false)
	private String sensor;
	
	@Column(insertable = false, updatable=false)
	private double value;
// gettery itd.
}

Dzięki temu po stronie Javy nie mamy możliwości wywołania INSERT/UPDATE na encji. Na razie jest prosto, ale widoki mają też pewne ograniczenia.

Oganiczenia i problemy

Najprostsze podejście ujawnia pewien poważny problem związany z widokami. Jeżeli zapytanie, z którego stworzyliśmy widok, zwraca dużo danych, a chcemy tylko niewielki ich fragment, to dołożenie dodatkowej klauzuli WHERE w naszym zapytaniu może spowodować problemy z wydajnością. Na przykład, wracając do zapytania z listingu 4, możemy założyć, że avg_values zwróci wiele sensorów z różnych miast, a my chcemy tylko te z Wrocławia. Cała reszta nie jest nam potrzebna. Możemy przemodelować widok:

Listing 6. Nasz widok z ograniczeniem

CREATE OR REPLACE VIEW avg_value_wro AS
SELECT sensor, round(avg(value)::numeric, 2)) AS value
	WHERE 
		date_part('year', measure_time) BETWEEN 2022 AND 2024 
		AND sensor like 'Wrocław%'
	GROUP BY sensor;

I w tym momencie nasze zapytanie możemy przemodelować do:

Listing 7. Nowa wersja zapytania

SELECT * from avg_values_wro 
	WHERE value > 1.1;

Przestrzeń danych do przeszukania dla warunku value > 1.1 jest mniejsza niż w pierwszym przykładzie. A co jeżeli chcemy zmienić miasto? Albo wybrać inny zakres dat? I tu ujawnia się główna wada widoków – nie można przekazać do nich parametrów.

Widok parametryzowany – jak?

Oczywiście nie można tego zrobić out-of-box, ponieważ postgres nie pozwala na stworzenie czegoś takiego. Zamiast widoku uzyjemy zatem funkcji nazwanej, która zwróci nam tabelę. Trochę to szalone, ale skuteczne.

Listing 8. Nasza funkcja odpowiadająca pierwotnemu widokowi

CREATE OR REPLACE FUNCTION avg_value(strat_year numeric, end_year numeric)
    RETURNS TABLE
            (
                o_sensor	numeric,
                o_value	numeric
            )
AS
$body$
	SELECT sensor, round(avg(value)::numeric, 2)) AS value
		WHERE date_part('year', measure_time) BETWEEN start_year AND end_year 
		GROUP BY sensor;
$body$
LANGUAGE sql;

I oczywiście przykładowe użycie:

Listing 9. Przykładowe użycie funkcji

SELECT * from avg_values(2022, 2024) 
	WHERE sensor like 'Wrocław%' 
	AND value > 1.1;

Co więcej, możemy bardzo łatwo przekształcić naszą funkcję w formę, która przyjmuje opcjonalny parametr z nazwą sensora:

Listing 10. Nasza funkcja odpowiadająca zoptymalizowanemu widokowi

CREATE OR REPLACE FUNCTION avg_value(strat_year numeric, end_year numeric, p_sensor text default '*')
    RETURNS TABLE
            (
                o_sensor	numeric,
                o_value	numeric
            )
AS
$body$
	SELECT sensor, round(avg(value)::numeric, 2)) AS value
		WHERE date_part('year', measure_time) BETWEEN start_year AND end_year 
		AND sensor LIKE p_sensor
		GROUP BY sensor;
$body$
LANGUAGE sql;

Co pozwala na użycie w nastepujący sposob:

Listing 11. Przykładowe użycie funkcji z trzema parametrami

SELECT * from avg_values(2022, 2024, 'Wrocław%') 
	WHERE value > 1.1;

Model w JPA

Tu wkraczamy w domenę „smoków i gołych bab”, czyli musimy poczynić pewną magię. Jako, że funkcja nie może być wywołana jak zwyczajna tabela, to jedyna drogą na dobranie się do niej jest natywne zapytanie SQL. Funkcja zwraca jako wynik tabelę, więc musimy opisać, w jaki sposób kolumny tej mapują się na pola w klasie. W dodatku, funkcja ma opcjonalny parametr, więc tak naprawdę potrzebujemy dwóch zapytań.

Listing 12. Encja reprezentująca funkcję avg_values

@SqlResultSetMapping(
    name = "AvgValuesMapping",
    entities = @EntityResult(
        entityClass = AvgValue.class,
        fields = {
            @FieldResult(name = "sensor", column = "sensor"),
            @FieldResult(name = "value", column = "value")
        }
    )
)
@NamedNativeQuery(
    name = "AvgValuesForYearRange",
    query = "SELECT * FROM avg_value(:start_year, :end_year)",
    resultSetMapping = "AvgValuesMapping"
)
@NamedNativeQuery(
    name = "AvgValuesForYearRangeAndSensor",
    query = "SELECT * FROM avg_value(:start_year, :end_year, :sensor)",
    resultSetMapping = "AvgValuesMapping"
)
@Entity
public class AvgValue{
	
	@Id
	@Column(insertable = false, updatable=false)
	private String sensor;
	
	@Column(insertable = false, updatable=false)
	private double value;
// gettery itd.
}

I to powinno już ładnie śmigać.

Podsumowanie

Żeby uzyskać funkjonalność widoku sparametryzowanego w postgresie użyliśmy funkcji. Jako, że widoki mają inną semantykę wywołania niż funkcje, to musimy też odpowiednio zmienić sposób ich wywoiłania po stronie JPA. Na zakończenie dodam, że rezultat odpytania widoku, podobnie jak wyniki zapytania, nie muszi mieć unilaknych rekordów. Jednocześnie JPA wymaga, żeby każda encja miała unikalny identyfikator. Oznacza to, że użycie ORM z widokami, może oznaczać konieczność użycia kluczy zlożonych, ktore będą obejmować większość kolumn z wyniku. Cóż… bywa.