Widoki w bazie danych

Projektowanie baz danych

Lis Sulmont

Curriculum Manager

Widoki w bazie danych

W bazie danych widok to zbiór wyników zapisanego zapytania, który użytkownicy mogą odpytywać tak samo jak trwały obiekt bazy danych (Wikipedia)

Wirtualna tabela, która nie należy do schematu fizycznego

W pamięci przechowywane jest zapytanie, nie dane
Dane są agregowane z tabel
Można go odpytywać jak zwykłą tabelę
Nie wymaga wielokrotnego pisania tych samych zapytań ani modyfikacji schematów

¹ https://en.wikipedia.org/wiki/View_(SQL)

Tworzenie widoku (składnia)

CREATE VIEW view_name AS

SELECT col1, col2 
FROM table_name 
WHERE condition;

Tworzenie widoku (przykład)

Wymiar książki w schemacie płatka śniegu

Cel: Zwrócenie tytułów i autorów z gatunku science fiction

Tworzenie widoku (przykład)

CREATE VIEW scifi_books AS

SELECT title,  author, genre
FROM dim_book_sf
JOIN dim_genre_sf ON dim_genre_sf.genre_id = dim_book_sf.genre_id
JOIN dim_author_sf ON dim_author_sf.author_id = dim_book_sf.author_id
WHERE dim_genre_sf.genre = 'science fiction';

Zapytanie do widoku (przykład)

SELECT * FROM scifi_books

| title                         | author            | genre           |
|-------------------------------|-------------------|-----------------|
| The Naked Sun                 | Isaac Asimov      | science fiction |
| The Robots of Dawn            | Isaac Asimov      | science fiction |
| The Time Machine              | H.G. Wells        | science fiction |
| The Invisible Man             | H.G. Wells        | science fiction |
| The War of the Worlds         | H.G. Wells        | science fiction |
| Wild Seed (Patternmaster, #1) | Octavia E. Butler | science fiction |
| ...                           | ...               | ...             |

Co dzieje się w tle

SELECT * FROM scifi_books

SELECT * FROM 
(SELECT title,  author, genre
FROM dim_book_sf
JOIN dim_genre_sf ON dim_genre_sf.genre_id = dim_book_sf.genre_id
JOIN dim_author_sf ON dim_author_sf.author_id = dim_book_sf.author_id
WHERE dim_genre_sf.genre = 'science fiction');

Przeglądanie widoków

(w PostgreSQL)

SELECT * FROM INFORMATION_SCHEMA.views;

Zawiera widoki systemowe

SELECT * FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

Wyklucza widoki systemowe

Zalety widoków

Nie zajmuje miejsca w pamięci
Forma kontroli dostępu
- Ukrywa wrażliwe kolumny i ogranicza widoczność danych
Upraszcza złożone zapytania
- Przydatne przy silnie znormalizowanych schematach

$$ Schemat bazy danych recenzji Pitchfork

¹ https://www.kaggle.com/nolanbconaway/pitchfork-data

Czas na ćwiczenia!

Projektowanie baz danych