Guida sulle viste

Introduzione alla guida

Attenzione: Qui inizia una guida molto basilare sulle viste, che si basa sulle mie sperimentazioni. Esso assume che il lettore abbia letto le guide precedenti, fino a More Advanced Joins (o che si conoscano i concetti ivi spiegati). Questa pagine intende fornire una visione generale di come funzionano e a cosa servono, oltre a esempi di utilizzo.

Prerequisiti del manuale

Per eseguire le istruzioni SQL in questa guida, you will need access to a database hosted on a MySQL 5.x server, and you will need the CREATE TABLE and CREATE VIEW privileges on this table.

Il database Dipendenti

Per cominciare occorrono i dati che poi andremo a ottimizzare, perciò ricreeremo le tabelle già usate nella guida More Advanced Joins. Chi ha già letto tale guida e ha già il database, si può passare oltre.

Andiamo a creare la tabella che contiene tutti gli impiegati e le informazioni per contattarli:

CREATE TABLE `Employees` (
  `ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `First_Name` VARCHAR(25) NOT NULL,
  `Last_Name` VARCHAR(25) NOT NULL,
  `Position` VARCHAR(25) NOT NULL,
  `Home_Address` VARCHAR(50) NOT NULL,
  `Home_Phone` VARCHAR(12) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM;

Poi inseriamo alcuni impiegati nella tabella:

INSERT INTO `Employees` (`First_Name`, `Last_Name`, `Position`, `Home_Address`, `Home_Phone`)
VALUES
  ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492'),
  ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847'),
  ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456'),
  ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349'),
  ('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329'),
  ('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478'); 

Ora creiamo una seconda tabella, che contiene le ore settimanali di entrata e di uscita timbrate dagli impiegati:

CREATE TABLE `Hours` (
  `ID` TINYINT(3) UNSIGNED NOT NULL,
  `Clock_In` DATETIME NOT NULL,
  `Clock_Out` DATETIME NOT NULL
) TYPE=MyISAM;

Infine, anche se sono informazioni ingombranti, aggiungiamo le ore lavorative degli impiegati in questa seconda tabella:

INSERT INTO `Hours`
VALUES ('1', '2005-08-08 07:00:42', '2005-08-08 17:01:36'),
  ('1', '2005-08-09 07:01:34', '2005-08-09 17:10:11'),
  ('1', '2005-08-10 06:59:56', '2005-08-10 17:09:29'),
  ('1', '2005-08-11 07:00:17', '2005-08-11 17:00:47'),
  ('1', '2005-08-12 07:02:29', '2005-08-12 16:59:12'),
  ('2', '2005-08-08 07:00:25', '2005-08-08 17:03:13'),
  ('2', '2005-08-09 07:00:57', '2005-08-09 17:05:09'),
  ('2', '2005-08-10 06:58:43', '2005-08-10 16:58:24'),
  ('2', '2005-08-11 07:01:58', '2005-08-11 17:00:45'),
  ('2', '2005-08-12 07:02:12', '2005-08-12 16:58:57'),
  ('3', '2005-08-08 07:00:12', '2005-08-08 17:01:32'),
  ('3', '2005-08-09 07:01:10', '2005-08-09 17:00:26'),
  ('3', '2005-08-10 06:59:53', '2005-08-10 17:02:53'),
  ('3', '2005-08-11 07:01:15', '2005-08-11 17:04:23'),
  ('3', '2005-08-12 07:00:51', '2005-08-12 16:57:52'),
  ('4', '2005-08-08 06:54:37', '2005-08-08 17:01:23'),
  ('4', '2005-08-09 06:58:23', '2005-08-09 17:00:54'),
  ('4', '2005-08-10 06:59:14', '2005-08-10 17:00:12'),
  ('4', '2005-08-11 07:00:49', '2005-08-11 17:00:34'),
  ('4', '2005-08-12 07:01:09', '2005-08-12 16:58:29'),
  ('5', '2005-08-08 07:00:04', '2005-08-08 17:01:43'),
  ('5', '2005-08-09 07:02:12', '2005-08-09 17:02:13'),
  ('5', '2005-08-10 06:59:39', '2005-08-10 17:03:37'),
  ('5', '2005-08-11 07:01:26', '2005-08-11 17:00:03'),
  ('5', '2005-08-12 07:02:15', '2005-08-12 16:59:02'),
  ('6', '2005-08-08 07:00:12', '2005-08-08 17:01:02'),
  ('6', '2005-08-09 07:03:44', '2005-08-09 17:00:00'),
  ('6', '2005-08-10 06:54:19', '2005-08-10 17:03:31'),
  ('6', '2005-08-11 07:00:05', '2005-08-11 17:02:57'),
  ('6', '2005-08-12 07:02:07', '2005-08-12 16:58:23');

Lavorare con il database Employee

In questo esempio, aiuteremo le Risorse Umane semplificando le query che servono alle loro applicazioni. Allo stesso tempo, questo ci permetterà di astrarre le loro query dal database, il che consente una maggiore flessibilità nella manutenzione.

Filtrare per nome, data e ora

Nel tutorial tutorial precedente, abbiamo visto una query JOIN che mostrava tutti i ritardi di un certo dipendente. In questo tutorial, astrarremo quella query perché trovi tutti i ritardi di tutti i dipendenti, e la standardizzeremo con una vista.

La query precedente era la seguente:

SELECT
  `Employees`.`First_Name`,
  `Employees`.`Last_Name`,
  `Hours`.`Clock_In`,
  `Hours`.`Clock_Out`
FROM `Employees`
INNER JOIN `Hours` ON `Employees`.`ID` = `Hours`.`ID`
WHERE `Employees`.`First_Name` = 'Helmholtz'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') >= '2005-08-08'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') <= '2005-08-12'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%H:%i:%S') > '07:00:59';

Il risultato:

+------------+-----------+---------------------+---------------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           |
+------------+-----------+---------------------+---------------------+
| Helmholtz  | Watson    | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |
+------------+-----------+---------------------+---------------------+

Migliorare la Query

L'esempio precedente mostra tutte le entrate di Heimholtz che sono avvenute dopo le sette del mattino. Vediamo quindi che Heimholz è entrato in ritardo per due volte nel periodo in esame, e vediamo che in entrambi i casi è uscito esattamente in orario o è uscito prima. La politica aziendale tuttavia stabilisce che in caso di ritardo l'ora di uscita deve slittare, pertanto vogliamo escludere dal report le persone la cui uscita è avvenuta dopo 10 ore e un minuto dopo l'ora di entrata.

SELECT
  `Employees`.`First_Name`,
  `Employees`.`Last_Name`,
  `Hours`.`Clock_In`,
  `Hours`.`Clock_Out`,
  (TIMESTAMPDIFF(MINUTE,`Hours`.`Clock_Out`,`Hours`.`Clock_In`) + 601) as Difference
FROM `Employees`
INNER JOIN `Hours` USING (`ID`)
WHERE DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') >= '2005-08-08'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') <= '2005-08-12'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%H:%i:%S') > '07:00:59'
AND TIMESTAMPDIFF(MINUTE,`Hours`.`Clock_Out`,`Hours`.`Clock_In`) > -601;

Otteniamo la seguente lista di persone che hanno violato la politica aziendale:

+------------+-----------+---------------------+---------------------+------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           | Difference |
+------------+-----------+---------------------+---------------------+------------+
| Mustapha   | Mond      | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 |          4 |
| Henry      | Foster    | 2005-08-11 07:01:58 | 2005-08-11 17:00:45 |          2 |
| Henry      | Foster    | 2005-08-12 07:02:12 | 2005-08-12 16:58:57 |          4 |
| Bernard    | Marx      | 2005-08-09 07:01:10 | 2005-08-09 17:00:26 |          1 |
| Lenina     | Crowne    | 2005-08-12 07:01:09 | 2005-08-12 16:58:29 |          3 |
| Fanny      | Crowne    | 2005-08-11 07:01:26 | 2005-08-11 17:00:03 |          2 |
| Fanny      | Crowne    | 2005-08-12 07:02:15 | 2005-08-12 16:59:02 |          4 |
| Helmholtz  | Watson    | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 |          4 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |          4 |
+------------+-----------+---------------------+---------------------+------------+

L'utilità delle viste

Vediamo nell'esempio precedente che diversi dipendenti sono entrati in ritardo e usciti troppo presto. Sfortunatamente, vediamo anche che questa query sta diventando troppo complessa. Avere tutto questo SQL nell'applicazione non solo porta più complessità nel codice dell'applicazione, ma significa anche che, se mai modificassimo la strutura di questa tabella, dovremmo modificare una query che sta diventando ingarbugliata. E' a questo punto che le viste cominciano a mostrare la loro utilità.

Creare la vista Employee Tardiness

Creare una vista è quasi come creare un'istruzione SELECT, perciò possiamo utilizzare la SELECT precedente per creare una nuova vista:

CREATE SQL SECURITY INVOKER VIEW Employee_Tardiness AS 
SELECT
  `Employees`.`First_Name`,
  `Employees`.`Last_Name`,
  `Hours`.`Clock_In`,
  `Hours`.`Clock_Out`,
(TIMESTAMPDIFF(MINUTE,`Hours`.`Clock_Out`,`Hours`.`Clock_In`) + 601) as Difference
FROM `Employees`
INNER JOIN `Hours` USING (`ID`)
WHERE DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') >= '2005-08-08'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') <= '2005-08-12'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%H:%i:%S') > '07:00:59'
AND TIMESTAMPDIFF(MINUTE,`Hours`.`Clock_Out`,`Hours`.`Clock_In`) > -601;

Si noti che la prima riga della query contiene l'istruzione 'SQL SECURITY INVOKER' - ciò significa che, quando si accede alla vista, questa viene eseguita con gli stessi privilegi dell'utente che accede alla vista. Perciò, se qualcuno che non ha accesso alla tabella Employees cerca di interrogare la vista, otterrà un errore.

A parte il parametro sulla sicurezza, il resto della query è abbastanza eloquente. Eseguiamo semplicemente un 'CREATE VIEW <nome_vista> AS' e aggiungiamo un'istruzione SELECT valida, e la nostra vista viene creata. Ora se eseguiamo una SELECT sulla vista, otteniamo gli stessi risultati di prima, con molto meno SQL:

SELECT * FROM Employee_Tardiness;

+------------+-----------+---------------------+---------------------+------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           | Difference |
+------------+-----------+---------------------+---------------------+------------+
| Mustapha   | Mond      | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 |          5 |
| Henry      | Foster    | 2005-08-11 07:01:58 | 2005-08-11 17:00:45 |          3 |
| Henry      | Foster    | 2005-08-12 07:02:12 | 2005-08-12 16:58:57 |          5 |
| Bernard    | Marx      | 2005-08-09 07:01:10 | 2005-08-09 17:00:26 |          2 |
| Lenina     | Crowne    | 2005-08-12 07:01:09 | 2005-08-12 16:58:29 |          4 |
| Fanny      | Crowne    | 2005-08-09 07:02:12 | 2005-08-09 17:02:13 |          1 |
| Fanny      | Crowne    | 2005-08-11 07:01:26 | 2005-08-11 17:00:03 |          3 |
| Fanny      | Crowne    | 2005-08-12 07:02:15 | 2005-08-12 16:59:02 |          5 |
| Helmholtz  | Watson    | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 |          5 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |          5 |
+------------+-----------+---------------------+---------------------+------------+

Ora possiamo perfino eseguire operazioni sulla tabella, come limitare i risultati a quelli in cui Difference è di almeno cinque minuti:

SELECT * FROM Employee_Tardiness WHERE Difference >=5;

+------------+-----------+---------------------+---------------------+------------+
| First_Name | Last_Name | Clock_In            | Clock_Out           | Difference |
+------------+-----------+---------------------+---------------------+------------+
| Mustapha   | Mond      | 2005-08-12 07:02:29 | 2005-08-12 16:59:12 |          5 |
| Henry      | Foster    | 2005-08-12 07:02:12 | 2005-08-12 16:58:57 |          5 |
| Fanny      | Crowne    | 2005-08-12 07:02:15 | 2005-08-12 16:59:02 |          5 |
| Helmholtz  | Watson    | 2005-08-09 07:03:44 | 2005-08-09 17:00:00 |          5 |
| Helmholtz  | Watson    | 2005-08-12 07:02:07 | 2005-08-12 16:58:23 |          5 |
+------------+-----------+---------------------+---------------------+------------+

Altri usi delle viste

A parte semplificare le query SQL, vi sono altri benefici che le viste possono portare, alcuni dei quali si possono ottenere solo attraverso le viste.

Restringere l'accesso ai dati

Per esempio, anche se il database Employees contiene i campi come la posizione, l'indirizzo di casa e il telefono di casa, la nostra query non mostra questi campi. ciò significa che, nel caso ci sia un problema di sicurezza nell'applicazione (come una vulnerabilità alle SQL injection, o anche un programmatore malizioso), non ci sarà il rischio che le informazioni personali degli impiegati vengano diffuse.

Sicurezza a livello di riga

E' anche possibile definire viste reparate per includere una clausola WHERE specifica, per motivi di sicurezza; ad esempio, se volessimo restringere l'accesso di un capo di dipartimento, in modo che possa vedere solo il suo staff, potremmo specificare la sua identità nella definizione della vista, e lui non potrebbe più vedere i dipendenti degli altri dipartimenti, nonostante si trovino tutti nella stessa tabella. Questo è l'unico modo per implementare la sicurezza a livello di riga in MariaDB, pertanto le viste giocano un ruolo importante in quest'area.

Pre-emptive Optimization

We can also define our views in such a way as to force the use of indexes, so that other, less-experienced developers don't run the risk of running un-optimized queries or JOINs that result in full-table scans and extended locks. Expensive queries, queries that SELECT *, and poorly thought-out JOINs can not only slow down the database entirely, but can cause inserts to fail, clients to time out, and reports to error out. By creating a view that is already optimized and letting users perform their queries on that, you can ensure that they won't cause a significant performance hit unnecessarily.

Abstracting Tables

When we re-engineer our application, we sometimes need to change the database to optimize or accommodate new or removed features. We may, for example, want to normalize our tables when they start getting too large and queries start taking too long. Alternately, we may be installing a new application with different requirements alongside a legacy application. Unfortunately, database redesign will tend to break backwards-compatibility with previous applications, which can cause obvious problems.

Using views, we can change the format of the underlying tables while still presenting the same table format to the legacy application. Thus, an application which demands username, hostname, and access time in string format can access the same data as an application which requires firstname, lastname, user@host, and access time in Unix timestamp format.

Summary

Views are an SQL feature that can provide a lot of versatility in larger applications, and can even simplify smaller applications further. Just as stored procedures can help us abstract out our database logic, views can simplify the way we access data in the database, and can help un-complicate our queries to make application debugging easier and more efficient.