Viste del database

Progettazione di database

Lis Sulmont

Curriculum Manager

Viste del database

In un database, una vista è il risultato di una query memorizzata sui dati, che gli utenti del database possono interrogare proprio come farebbero con un oggetto di raccolta persistente del database (Wikipedia).

**Tabella virtuale che non fa parte dello schema fisico

La query, non i dati, viene memorizzata
I dati sono aggregati dai dati delle tabelle
Può essere interrogato come una normale tabella di database
Non c'è bisogno di riscrivere le query più comuni o di modificare gli schemi

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

Creare una vista (sintassi)

CREATE VIEW view_name AS

SELECT col1, col2 
FROM table_name 
WHERE condition;

Creare una vista (esempio)

Dimensione libro dello schema a fiocco di neve

Obiettivo: Restituisci titoli e autori del genere science fiction

Creare una vista (esempio)

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

Interrogare una vista (esempio)

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

Dietro le quinte

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

Visualizzazioni

(in PostgreSQL)

SELECT * FROM INFORMATION_SCHEMA.views;

Include viste del sistema

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

Esclude le viste del sistema

Vantaggi delle viste

Non occupa spazio in magazzino
Una forma di controllo degli accessi
- Nascondi le colonne sensibili e limita ciò che l'utente può vedere
Maschera la complessità delle query
- Utile per schemi altamente normalizzati

Schema del database delle recensioni di Pitchfork

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

Passiamo alla pratica!

Progettazione di database