Databaseviews

Databaseontwerp

Lis Sulmont

Curriculum Manager

Databaseviews

In een database is een view de resultset van een opgeslagen query op de data, die gebruikers kunnen bevragen alsof het een persistente tabel is (Wikipedia)

Virtuele tabel die niet in het fysieke schema zit

Query, geen data, wordt opgeslagen in geheugen
Data wordt samengevoegd uit tabellen
Te bevragen als een normale tabel
Veelgebruikte queries niet herhalen of schema’s wijzigen

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

Een view maken (syntaxis)

CREATE VIEW view_name AS

SELECT col1, col2 
FROM table_name 
WHERE condition;

Een view maken (voorbeeld)

Boekdimension van het snowflake-schema

Doel: Geef titels en auteurs van het genre science fiction terug

Een view maken (voorbeeld)

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';

Een view bevragen (voorbeeld)

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 |
| ...                           | ...               | ...             |

Achter de schermen

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');

Views bekijken

(in PostgreSQL)

SELECT * FROM INFORMATION_SCHEMA.views;

Inclusief systeemviews

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

Exclusief systeemviews

Voordelen van views

Neemt geen opslagruimte in
Een vorm van toegangsbeheer
- Verberg gevoelige kolommen en beperk wat een gebruiker ziet
Maskeert querycomplexiteit
- Handig bij sterk genormaliseerde schema's

$$ Schema van de Pitchfork Reviews-database

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

Laten we oefenen!

Databaseontwerp