CREATE VIEW

Sintassi

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { utente | CURRENT_USER | role | CURRENT_ROLE  }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW [IF NOT EXISTS] nome_vista [(elenco_colonne)]
    AS istruzione_select
    [WITH [CASCADED | LOCAL] CHECK OPTION]

Spiegazione

L'istruzione CREATE VIEW crea una nuova vista, o ne rimpiazza una già esistente se la clausola OR REPLACE è presente. Se la vista non esiste, CREATE OR REPLACE VIEW equivale a CREATE VIEW. Se invece esiste, CREATE OR REPLACE VIEW equivale ad ALTER VIEW.

istruzione_select è un'istruzione di tipo SELECT che viene usata come definizione per la vista: quando si selezionano dei valori da essa, sarà come selezionarli dall'istruzione SELECT. istruzione_select può leggere i dati da tabelle fisiche o da altre viste.

La definizione è "congelata" al momento della creazione della vista, e non sarà modificata se si cambierà la struttura delle tabelle sottostanti. Per esempio, se una vista è definita come una SELECT * su una certa tabella, e successivamente a questa tabella verranno aggiunte nuove colonne, queste non faranno parte della vista.

La clausola ALGORITHM influenza il modo in cui MariaDB elabora la vista. Le clausole DEFINER e SQL SECURITY controllano il modo in cui verranno verificati i privilegi per gli accessi alla vista. La clausola WITH CHECK OPTION permette di aggiungere o modificare solo i dati che fanno parte della vista. Queste clausole verranno descritte più avanti nella pagina.

L'istruzione CREATE VIEW richiede il privilegio CREATE VIEW e i privilegi per le colonne selezionate dall'istruzione SELECT. Anche per le altre colonne nominate dalla SELECT occorre il privilegio SELECT. Se la clausola OR REPLACE è specificata, occorre anche il privilegio DROP sulla vista.

Le viste appartengono ai rispettivi database. Per default, le nuove viste vengono create nel database selezionato. Per crearle in un database differente, si specifichi il nome usando la sintassi nome_db.nome_vista.

CREATE VIEW test.v AS SELECT * FROM t;

Le tabelle fisiche e le viste condividono lo stesso namespace, perciò non è possibile che all'interno di uno stesso database ci siano una tabella e una vista con lo stesso nome.

I nomi delle colonne delle viste devono essere univoci, così come accade per le tabelle. Per default, i nomi delle colonne selezionate dalla SELECT vengono usati anche nella vista. Per definire esplicitamente dei nomi differenti da usare nella vista, si può utilizzare la clausola opzionale elenco_colonne, separando i nomi con una virgola. Il numero di nomi presenti in elenco_colonne deve corrispondere al numero di colonne estratte dalla SELECT.

Le colonne restituite dall'istruzione SELECT possono essere semplici riferimenti ai campi di una tabella. Ma possono anche essere espressioni che utilizzano funzioni, valori costanti, operatori e così via.

I nomi non qualificati delle tabelle e delle viste nell'istruzione SELECT vengono interpretati come interni al database corrente. Una vista può comunque fare riferimento a tabelle o ad altre viste che si trovano in altri database utilizzando i nomi qualificati.

Le viste possono essere create con diversi tipi di SELECT. Possono fare riferimento alle tabelle fisiche o ad altre viste. E' possibile utilizzare le join, le UNION e le subquery. La SELECT non deve necessariamente leggere i dati da una tabella. L'esempio seguente mostra una vista che estrae due colonne da una tabella e una espressione calcolata utilizzando tre colonne:

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

Le definizioni delle viste sono soggette alle seguenti restrizioni:

• L'istruzione SELECT non può contenere una subquery nella clausola FROM.
• L'istruzione SELECT non può fare riferimento a tabelle di sistema o variabili utente.
• In uno Stored Program, la definizione non può utilizzare i parametri né le variabili locali.
• L'istruzione SELECT non può utilizzare i parametri di un Prepared Statement.
• Tutte le tabelle e le viste nominate nella definizione devono esistere. Tuttavia, dopo la creazione della vista, è possibile eliminare queste viste o tabelle. In questo caso però, utilizzando la vista si otterrà un errore. Per controllare se le definizioni delle viste hanno problemi di questo genere, si può utilizzare l'istruzione CHECK TABLE.
• La definizione non può utilizzare tabelle temporanee, né è possibile creare viste temporanee.
• Non è possibile associare un trigger a una vista.

La clausola ORDER BY può essere utilizzata nella definizione delle viste, ma viene ignorata quando una query sulla vista ha una propria clausola ORDER BY.

Le altre opzioni e clausole che fanno parte della definizione vengono aggiunte alle opzioni e clausole dell'istruzione che interroga la vista, ma l'effetto è indefinito. Per esempio, se la definizione di una vista ha una clausola LIMIT, e una query che interroga la vista ha una sua clausola LIMIT, non è definito quale delle due si applica. Questo stesso principio si applica alle opzioni che seguono la parola chiave SELECT, come ALL, DISTINCT o SQL_SMALL_RESULT, e a clausole come INTO, FOR UPDATE, LOCK IN SHARE MODE e PROCEDURE.

Se si crea una vista e si altera l'ambiente che elabora le query modificando una variabile di sistema, questo potrebbe influenzare i risultati della vista:

mysql> CREATE VIEW v (mycol) AS SELECT 'abc';
Query OK, 0 rows affected (0.01 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| mycol | 
+-------+
1 row in set (0.01 sec)

mysql> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| abc   | 
+-------+
1 row in set (0.00 sec)

Le clausole DEFINER e SQL SECURITY determinano quale account MariaDB debba essere utilizzato per verificare i privilegi di accesso alla vista, nel momento in cui viene eseguita un'istruzione che fa riferimento ad essa. Sono state aggiunte in MySQL 5.1.2. I valori ammessi per SQL SECURITY sono DEFINER e INVOKER. Essi fanno sì che i privilegi da controllare siano, rispettivamente, quelli dell'utente che ha definito la vista, o quelli dell'utente che la invoca. Il valore predefinito è DEFINER.

Se viene passato il valore utente alla clausola DEFINER, dovrà essere un account MariaDB nella forma 'nome_utente'@'nome_host' (lo stesso formato usato nell'istruzione GRANT). I valori nome_utente e nome_host sono sentrambi necessari. Il definer può anche essere specificato come CURRENT_USER o CURRENT_USER(). Il valore predefinito per DEFINER è l'utente che esegue l'istruzione CREATE VIEW. E' come specificare esplicitamente DEFINER = CURRENT_USER.

Se si specifica la clausola DEFINER, queste sono le regole che determinano i valori ammessi:

• Se non si dispone del privilegio SUPER, l'unico valore ammesso è il proprio account, che si può specificare letteralmente o con CURRENT_USER. Non è possibile impostare un altro account come definer.
• Se si dispone del privilegio SUPER, è possibile specificare qualsiasi account sintatticamente valido. Se questo non esiste, viene generato un warning.
• Se il valore di SQL SECURITY è DEFINER ma l'account non esiste nel momento in cui la vista viene usata, viene generato un errore.

Nelle definizioni delle viste, per default CURRENT_USER restituisce il valore di DEFINER a partire da MySQL 5.1.12. Nelle versioni più vecchie, e nel caso delle viste definite con SQL SECURITY INVOKER, CURRENT_USER restituisce l'account di chi invoca la vista. Per ulteriori informazioni sull'auditing degli utenti nelle viste, si veda la pagina http://dev.mysql.com/doc/refman/5.1/en/account-activity-auditing.html.

Nelle Stored Routine definite con SQL SECURITY DEFINER, CURRENT_USER restituisce il valore DEFINER della routine. Questo comportamento influenza anche le viste definite all'interno del programma, se la definizione della vista ha il DEFINER impostato come CURRENT_USER.

A partire da MySQL 5.1.2 (cioè quando le clausole DEFINER e SQL SECURITY sono state implementate), i privilegi delle viste vengono verificati in questo modo:

• Nel momento della definizione della vista, il creatore deve possedere tutti i privilegi necessari per accedere agli oggetti di livello più alto usati dalla vista. Per esempio, se la vista fa riferimento ad alcune colonne di una tabella, il creatore deve disporre dei permessi su quei campi, come descritto precedentemente. Se la definizione usa una Stored Function, verranno verificati solo i privilegi necessari per invocare quella funzione. I permessi necessari per eseguire la funzione verranno controllati solo se essa viene chiamata: ogni volta che la funzione viene invocata, l'esecuzione potrebbe seguire percorsi differenti al suo interno.
• Per accedere agli oggetti a cui la vista fa riferimento, a seconda che SQL SECURITY sia impostato a DEFINER o INVOKER, saranno usati rispettivamente i permessi del creatore o del chiamante.
• Se accedendo a una vista si lancia una Stored Function, verranno controllati i privilegi per ogni istruzione che fa parte della funzione. Se la Stored Function ha SQL SECURITY impostato a DEFINER, verrà eseguita con i permessi del suo creatore. Se invece è INVOKER, la funzione viene eseguita con i permessi determinati dalla clausola SQL SECURITY della vista.

Nelle versioni di MySQL precedenti alla 5.1.2 (cioè prima che le clausole DEFINER e SQL SECURITY fossero implementate), i privilegi richiesti per l'accesso ai vari oggetti venivano verificati in fase di creazione.

Esempio: una vista può dipendere da una Stored Function, la quale può invocare altre Stored Routine. La vista seguente chiama una Stored Function f():

CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.nome);

Si supponga che f() contenga la seguente istruzione:

IF nome IS NULL THEN
  CALL p1();
ELSE
  CALL p2();
END IF;

I privilegi richiesti per eseguire le istruzioni presenti in f() devono essere verificati quando la funzione viene eseguita. Questo potrebbe significare che siano necessari i privilegi per p1() o per p2(), a seconda del percorso preso da f() durante l'esecuzione. Questi privilegi devono essere quindi controllati a runtime e l'utente che deve possederli è indicato dalle clausole SQL SECURITY della vista v e della funzione f().

Le clausole DEFINER e SQL SECURITY relative alle viste sono estensioni rispetto all'SQL standard. Nelle specifiche dello standard, i privilegi delle viste seguono le regole di SQL SECURITY INVOKER.

Se si invoca una vista che è stata creata con una versione precedente a MySQL 5.1.2, verrà trattata come se fosse stata creata con la clausola SQL SECURITY DEFINER e con un valore di DEFINER uguale all'account corrente. Tuttavia, poiché il definer effettivo è sconosciuto, MySQL produce un warning. Per evitare questo messaggio è sufficiente ricreare la vista includendo nella definizione la clausola DEFINER.

La clausola opzionale ALGORITHM è un'estensione di MySQL e MariaDB rispetto allo standard SQL. Influenza il modo in cui la vista viene elaborata. Si veda gli algoritmi delle Viste. ALGORITHM accetta tre possibili valori: MERGE, TEMPTABLE o UNDEFINED. L'algoritmo predefinito è UNDEFINED. Per ulteriori informazioni si veda la pagina http://dev.mysql.com/doc/refman/5.1/en/view-algorithms.html.

Alcune viste sono aggiornabili. Questo significa che è possibile usare su di esse le istruzioni UPDATE, DELETE e INSERT per modificare la tabella sottostante. Perché una vista sia aggiornabile, deve esistere una relazione uno-a-uno fra le righe della vista e i record della tabella sottostante. Vi sono però alcuni costrutti che rendono le viste non aggiornabili.

La clausola WITH CHECK OPTION può essere specificata per far sì che le righe di una vista vengano inserite o modificate solo se corrispondono alla clausola WHERE di istruzione_select.

Nella clausola WITH CHECK OPTION, le parole chiave LOCAL e CASCADED determinano se i controlli debbano essere propagati ad altre viste. La parola chiave LOCAL restringe la CHECK OPTION alla sola vista che si sta definendo. CASCADED fa sì che i controlli vengano eseguiti anche per le viste sottostanti. L'opzione predefinita è CASCADED.

Per ulteriori informazioni sulle viste aggiornabili e sulla clausola WITH CHECK OPTION si veda http://dev.mysql.com/doc/refman/5.1/en/view-updatability.html.

Examples

CREATE TABLE t (a INT, b INT) ENGINE = InnoDB;

INSERT INTO t VALUES (1,1), (2,2), (3,3);

CREATE VIEW v AS SELECT a, a*2 AS a2 FROM t;

SELECT * FROM v;
+------+------+
| a    | a2   |
+------+------+
|    1 |    2 |
|    2 |    4 |
|    3 |    6 |
+------+------+

OR REPLACE, IF NOT EXISTS:

CREATE VIEW v AS SELECT a, a*2 AS a2 FROM t;
ERROR 1050 (42S01): Table 'v' already exists

CREATE OR REPLACE VIEW v AS SELECT a, a*2 AS a2 FROM t;
Query OK, 0 rows affected (0.04 sec)

CREATE VIEW IF NOT EXISTS v AS SELECT a, a*2 AS a2 FROM t;
Query OK, 0 rows affected, 1 warning (0.01 sec)

SHOW WARNINGS;
+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Note  | 1050 | Table 'v' already exists |
+-------+------+--------------------------+