I Database

I database sono l'evoluzione massima dei sistemi di archiviazione nell'ambito digitale con un'organizzazione ed un'agevolazione ottimale per il reperimento delle informazioni.

Sistemi complessi e strutturati ben distanti dalla gestione per files di un sistema informatico, che hanno permesso una crescita esponenziale per l'informatica ed in particolar modo per il web.

I database sono ad oggi visti come il valore massimo per un portale o in generale per un software, in quanto contengono tutte le informazioni ad esso correlato; ed è proprio per garantire la riservatezza e la protezione dei dati contenuti, che occorre progettare al meglio i database e le strutture che li contengono.

I Database

Il sistema di archiviazione dei dati all'interno dei database è strutturato in tabelle, definite anche entità.
Saper pianificare e progettare un buon database, consiste nella definizione corretta delle sue tabelle, al fine di evitare la duplicazione delle informazioni, ovvero far sì che la gestione dei dati non sia ridondante.

Ciascuna tabella contiene al suo interno campi o attributi che ne definiscono la tipologia di informazioni contenute e ne permettono la reperibilità.

I Database

Esempio dei campi di una tabella generica degli utenti di una piattaforma:

  • Nome
  • Cognome
  • E-mail
  • Password
  • Data di nascita

I Database

Per ciascun campo è possibile definire la tipologia dell'informazione contenuta; ecco un esempio delle tipologia maggiormente utilizzate:

  • INT - numero intero
  • FLOAT - numero decimale
  • TEXT - testo libero
  • VARCHAR - testo con lunghezza limitata
  • DATE - data numerica
  • DATETIME - data e ora numerica
  • ENUM - valori predefiniti (a scelta)

La scelta della tipologia dei campi per quanto possa sembrar banale, determina la velocità del database nella reperibilità delle informazioni e la quantità di risorse hardware utilizzate dal server per l'elaborazione delle singole interrogazioni.

I Database

Per esempio un campo di tipo DATE richiederà un utilizzo di 3 bytes per ciascuna informazione, mentre un campo DATETIME ne richiederà 8 bytes.

O ancora, se si vuole utilizzare un campo come flag, del tipo Utente attivo SI/NO, si potrebbe utilizzare un VARCHAR, un TEXT, un ENUM oppure un INT o un TINYINT nel caso in cui si facesse riferimento ad 1/0.
In questo caso però bisognerebbe tener conto che:

  • VARCHAR - 3 bytes
  • ENUM - 1 byte
  • TEXT - 4 bytes
  • INT - 4 bytes
  • TINYINT - 1 byte
La scelta errata della tipologia di un solo flag, in database ricchi di informazioni, potrebbe dunque determinare addirittura il crollo di un database nel lungo termine.

I Database

Nella definizione dei campi delle tabelle, è sempre necessario individuare una chiave primaria ovvero un'informazione univoca che permetterà la distinzione imprescindibile fra le varie righe della tabella.
Per esempio nel caso di una tabella di utenti, una buona chiave primaria potrebbe essere l'e-mail (nel caso in cui non siano ammesse più righe per lo stesso utente).

E' consuetudine però far riferimento sempre a valori numerici per la chiave primaria, nella maggior parte delle volte addirittura non correlati alle informazioni stesse della tabella, ovvero i così detti ID, generalmente autoincrementali. In questo modo si avrà la certezza che ogni riga della tabella non potrà avere la chiave primaria duplicata.

I Database

Ciascuna tabella all'interno del database può appartenere ad una delle seguenti tipologie:

  • MyISAM
  • InnoDB

Generalmente si applica la stessa tipologia a tutte le tabelle appertenenti allo stesso database, tenendo conto delle seguenti informazioni:

Impostazione MyISAM InnoDB
Blocco operazioni parallele Tabella Riga
Ottimizzazione velocità Big Data
Relazione fra le tabelle NO SI
Supporto transazioni NO SI
Supporto indice FULLTEXT SI NO
Salvataggio dati fisici (tabelle, campi, indici) 3 files 1 file

I Database

Non esiste una scelta migliore fra le due soluzioni, tutto dipende dal contesto delle applicazioni che faranno riferimento al database.

Certo è che le tecnologie moderne sono sempre più orientate a soluzioni MyISAM in quanto non relazionali.
La relazione delle tabelle, ha determinato infatti due diverse classificazioni per i database:

  • RDBMS (Relational Database Management System) - dove le tabelle sono in relazione fra loro, quindi database relazionali
  • DBMS (Database Management System) - dove le tabelle NON sono in relazione fra loro, quindi database non relazionali

I Database

La relazione permette operazioni massive in contemporanea su più tabelle a seconda del legame fra le stesse, secondo quelle che si definiscono foreign keys ovvero chiavi esterne.
Questo può essere si un vantaggio per la gestione dei dati, ma al tempo stesso determina un rallentamento per le singole interrogazioni o operazioni sul database.

Proprio questo ha spinto dunque gli sviluppatori degli applicativi web, verso una scelta non relazionale, dove tutte le operazioni che andrebbero ad influire anche su dati di altre tabelle, sono gestite ad hoc tramite righe di codice.

Come per la maggior parte delle questioni però la soluzione migliore è dettata dall'utilizzo e dalle esigenze.

I Database

Per poter progettare al meglio un database, si consiglia sempre di effettuare una rappresentazione vera e propria delle tabelle e dei rispettivi campi, individuando le chiavi primarie e la natura dei vari campi.

Sono disponibili anche tool open source per la realizzazione della pianificazione di un database, come per esempio il MySQL Workbench della Microsoft, la cui peculiarità permette di passare da uno schema a blocchi disegnato, alla realizzazione vera e propria del database, con tabelle e campi associati, nel processo definito di forward engineering.

I Database

Nella fase di progettazione (soprattutto quando si procede sul cartaceo o senza il supporto di tool ad hoc), è bene ricordare che:

  • Si indica con il simbolo (PK) la chiave primaria
  • Si specifica con il simbolo (AI) la chiave primaria autoincrementale
  • Si indica con il simbo (FK) la chiave esterna
  • Si collega la chiave esterna di una tabella con il campo in relazione di un'altra tabella

Nel collegamento della relazione, si specifica anche la tipologia della relazione:

  • 1:1 - relazione univoca fra le righe delle due tabelle in relazione
  • 1:N - relazione di una riga con più righe della seconda tabella in relazione
  • N:N - relazione di più righe della prima tabella con la seconda in relazione/li>

Esercitazione

Pianificazione di un database complesso

SQL

Il lignuaggio per interagire con i database

SQL

Il linguaggio SQL (acronimo di Structured Query Language) è il linguaggio utilizzato per interagire con i database, tramite singole operazioni, definite query.

Seppur standard come linguaggio, esistono differenti versioni molto simili fra loro, a seconda dei database utilizzati; in tutti i casi però le operazioni fondamentali restano invariate (inserimento, modifica, creazione, eliminazione).

Vedremo successivamente tutti i comandi utilizzati nel linguaggio SQL per le operazioni più comuni, seppure molte operazioni come per esempio la creazione del database stesso o delle tabelle, si effettua generalmente tramite appositi tools.

SQL

Creazione database

Per creare un nuovo database è sufficiente eseguire la query

// Creo un database dal nome "mydb"
CREATE DATABASE mydb;

Si precisa che il linguaggio SQL NON è case sensitive, dunque "CREATE DATABASE" prevede lo stesso funzionamento di "create database".

E' buona norma però indicare i nomi di database, tabelle e campi in sole lettere minuscole e utilizzare i comandi del linguaggio SQL (come appunto CREATE DATAVASE) con lettere maiuscole, per agevolare la lettura e il riconoscimento di una query.

SQL

Creazione tabelle

Nella creazione di una tabella e dei rispettivi campi, è necessario tener conto di alcuni punti:

  • definizione dei campi e della loro natura
  • definizione delle regole per ciascun campo (constraints)
  • definizione degli indici

Rispettando i punti elencati, saremo sempre certi di una buona gestione delle tabelle.

SQL

Creazione tabelle - i campi

In seguito alla fase di progettazione è possibile creare una tabella rispettando la sintassi di base:

CREATE TABLE nome_tabella
(
primo_campo data_type(size),
secondo_campo data_type(size),
terzo_campo data_type(size),
....
);

Potremo avere dunque, nel caso della tabella per un utente:

CREATE TABLE utente
(
id INT,
nome VARCHAR(50), // Lunghezza massima del nome di 50 caratteri
cognome VARCHAR(50),
email VARCHAR(50),
password VARCHAR(50),
...
);

SQL

Creazione tabelle - constraints

Aver creato i campi e definito la loro natura, è solo il primo passaggio per la creazione di una tabella.
In seguito a questa prima fase, è dunque necessario inserire le constraints, ovvero delle regole per la formattazione dei campi e il loro contenuto.

Esistono diverse tipologie di constraints:

  • NOT NULL - per indicare che una colonna non può essere NULL
  • UNIQUE - per assicurare che ogni colonna abbia il campo diverso dagli altri
  • PRIMARY KEY - l'unione di NOT NULL E UNIQUE
  • FOREIGN KEY - per la gestione della chiave esterna
  • CHECK - per garantire un determinato valore al campo
  • DEFAULT - per utilizzare un valore di default in caso di campo vuoto

SQL

Creazione tabelle - constraints - NOT NULL

La regola NOT NULL permette di far si che un campo non contenga manga una voce NULL.
Per aggiungere questa constraints ai vari campi interessati, andremo dunque a modificare la query di creazione della tabella:

CREATE TABLE utente
(
id INT,
nome VARCHAR(50) NOT NULL,
cognome VARCHAR(50) NOT NULL,
email VARCHAR(50),
password VARCHAR(50),
...
);

In questo modo nome e cognome saranno sempre valorizzati oppure vuoti, ma non avranno mai la voce NULL.

SQL

Creazione tabelle - constraints - UNIQUE

L'opzione UNIQUE si utilizza per quei campi che devono essere univoci fra le varie righe della tabella.
Può essere considerata come una restrizione molto forte, da utilizzare dunque solo quando si è certi dell'univocità del campo.
Nella tabella degli utenti un buon utilizzo della constraint UNIQUE, può essere quello della email:

CREATE TABLE utente
(
id INT,
nome VARCHAR(50) NOT NULL,
cognome VARCHAR(50) NOT NULL,
email VARCHAR(50),
password VARCHAR(50),
CONSTRAINT mail_unica UNIQUE (email [,campox, campoy...])
);

La voce UNIQUE non va dunque mai inserita di fianco al singolo campo, ma riportata al termine della definizione dei vari campi.
Si attribuisce un nome identificativo della constraint (nell'esempio "mail_unica") ed è anche possibile aggiungere più campi per un controllo multiplo di univocità. Se si proverà ad inserire un utente con una email già esistente all'interno della tabella utente, il database restituirà un errore di duplicazione non consentita.

SQL

Creazione tabelle - constraints - PRIMARY KEY

La chiave primaria è il campo più importante di una tabella, in quanto permette l'utilizzo univoco di ciascuna riga a prescindere da tutte le informazioni contenute.

All'interno di una tabella ci possono essere una o più chiavi primarie, il cui funzionamento avrà lo stesso effetto dell'unione di NOT NULL e UNIQUE

CREATE TABLE utente
(
id INT AUTO_INCREMENT,
nome VARCHAR(50) NOT NULL,
cognome VARCHAR(50) NOT NULL,
email VARCHAR(50),
password VARCHAR(50),
CONSTRAINT pk_utente PRIMARY KEY (id [,campox, campoy...])
);

Anche in questo caso è possibile aggiungere con un solo comando più chiavi primarie, definendo sempre un nome per la regola.
Se si vuole utilizzare come da esempio un valore numerico autogestito dal database, si imposta la proprietà AUTO_INCREMENT che determina appunto un auto incremento del campo, ad ogni nuova riga della tabella.

SQL

Creazione tabelle - constraints - FOREIGN KEY

La chiave esterna è utilizzata nelle tabelle InnoDB per mettere in relazione un campo di una tabella, con la chiave primaria di una seconda tabella.

Se per esempio nella tabella utente volessimo associare l'ID di una categoria, ovvero una tabella chiamata categoria con i soli campi id e nome:

CREATE TABLE utente
(
id INT,
nome VARCHAR(50) NOT NULL,
cognome VARCHAR(50) NOT NULL,
email VARCHAR(50),
password VARCHAR(50),
id_categoria INT NOT NULL,

CONSTRAINT pk_utente PRIMARY KEY (id [,campox, campoy...]),
CONSTRAINT fk_categoria FOREIGN KEY (id_categoria) REFERENCES categoria(id)
);

Abbiamo dunque associato il campo id_categoria della tabella utente, con il campo (pk) id della tabella categoria.

SQL

Creazione tabelle - constraints - FOREIGN KEY

In seguito a questa associazione è possibile legare le due tabelle a seconda degli eventi di modifica (UPDATE) o rimozione (DELETE) delle righe in relazione fra loro.
In particolar modo è possibile far sì che al cambiamento della chiave primaria associata, si scateni un evento sulla tabella correlata; l'evento può essere del tipo:

  • SET NULL - modifica il campo correlato come NULL
  • CASCADE - aggiorna le informazioni del campo associato
  • SET DEFAULT - imposta il valore di default (se definito) per il campo correlato
  • NO ACTION - ignora le modifiche per il campo correlato

Esempio dell'utilizzo degli eventi:

...
CONSTRAINT fk_categoria FOREIGN KEY (id_categoria) 
	REFERENCES categoria(id) ON UPDATE CASCADE ON DELETE SET NULL

Abbiamo specificato che nella chiave esterna, all'aggiornamento (ON UPDATE) del campo id della tabella categoria, saranno aggiornate di conseguenza tutte le referenze presenti nella tabella utente, mentre alla rimozione (ON DELETE), sarà impostato valore NULL.

SQL

Creazione tabelle - constraints - FOREIGN KEY

Per far sì che si possa creare una chiave esterna, occorre assicurarsi che:

  • Le due tabelle siano della stessa natura (InnoDB)
  • Le due tabelle abbiano lo stesso charset (es. UTF8)
  • I campi correlati abbiano le stesse impostazioni (UNSIGNED, NOT NULL) e natura

In caso contrario non sarà possibile creare una foreign key.

Seppure molto comode, occorre lavorare con cautela con le foreign keys in quanto possono avere effetti a catena sulle modifiche di un database.

SQL

Creazione tabelle - constraints - CHECK

La constraint CHECK permette di definire delle regole o dei range di valori ammessi per i vari campi, assicurando dunque la corretta formattazione delle informazioni, un vero e proprio controllo aggiuntivo.

Se per esempio volessimo far registrare sul nostro database solo utenti il cui nome e cognome sia diverso da "Mario Rossi":

...
CONSTRAINT ck_nome CHECK (nome != "Mario" AND cognome != "Rossi") 

Anche la constraint check va inserita in seguito alla definizione dei campi ed occorre definire un nome.

SQL

Creazione tabelle - constraints - DEFAULT

La constraint DEFAULT permette di indicare un valore di default per i campi nel caso in cui vengano lasciati vuoti.

Se per esempio volessimo attribuire a tutti gli utenti un valore generico dell'id_categoria nel caso in cui non fosse inserito:

CREATE TABLE utente
(
id INT,
nome VARCHAR(50) NOT NULL,
cognome VARCHAR(50) NOT NULL,
email VARCHAR(50),
password VARCHAR(50),
id_categoria INT DEFAULT 1,
);

La constraint default è l'unica inserita direttamente nella definizione dei campi della tabella.

SQL

Creazione tabelle - indici

Gli indici di una tabella, sono fondamentali per garantire il massimo delle performance.
Non si tratta di elementi o informazioni visibili sulla tabella stessa, bensì di strumenti di calcolo per il server, che agevolano la lettura e la reperibilità delle informazioni, una sorta di segnalibri.

Un buon DBA, definisce generalmente gli indici in fuznione dei campi più utilizzati nelle query soprattutto in fase di ricerca, al fine di ridurre il tempo di elaborazione delle query stesse.

CREATE INDEX nome_indice
ON nome_tabella (nome_campo)

SQL

Rimozione tabelle e database

Per rimuovere delle tabelle o un intero database, si utilizza il comando DROP.

DROP TABLE nome_tabella;

DROP DATABASE nome_db;

In alcuni casi può servire semplicemente resettare le informazioni di una tabella, quindi piuttosto che eliminarla e ricrearla da zero, è possibile utilizzare il comando TRUNCATE

TRUNCATE TABLE nome_tabella;

Esercitazione

Partendo dal database di Trenitalia, integrare indici e constraints alle tabelle al fine di avere una struttura stabile e performante.

SQL

CRUD

Dopo aver creato la struttura del database, si può iniziare a lavorare con le informazioni.

Generalmente si individuano quattro operazioni principali su un database:

  • Inserimento - creazione delle informazioni (CREATE / INSERT)
  • Lettura - visualizzazione delle informazioni (READ)
  • Modifica - aggiornamento dei dati (UPDATE)
  • Rimozione - cancellazione delle informazioni (DELETE)

Queste quattro operazioni, si indentificano con l'acronimo CRUD.

Per ognuna delle operazioni di CRUD è associata nel linguaggio SQL una struttura differente di query.

SQL

CRUD - Inserimento

Per l'inserimento delle informazioni (righe) all'interno delle tabelle del database, si utilizza il costrutto SQL INSERT INTO, disponibile in due varianti:

// Prima variante
INSERT INTO nome_tabella(primo_campo, secondo_campo, terzo_campo) VALUES(1, 2, 3);

// Seconda variante
INSERT INTO nome_tabella SET primo_campo = 1, secondo_campo = 2, terzo_campo = 3;

Generalmente il più utilizzato è il primo costrutto, in quanto prevede anche alcune operazioni agevolate.
Se per esempio i campi da inserire corrispondono esattamente a tutti i campi presenti nella tabella, è possibile inserirli nello stesso ordine con la forma compatta:

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

E' inoltre possibile aggiungere più elementi (righe) contemporaneamente:

INSERT INTO nome_tabella(primo_campo, secondo_campo, terzo_campo) 
	VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);

SQL

CRUD - Lettura

Le operazioni di lettura delle informazioni, sono ovviamente quelle più utilizzate nel database; per eseguire query di lettura si utilizza il costrutto SELECT, generalmente così composto:

SELECT primo_campo, secondo_campo, terzo_campo
FROM nome_tabella
WHERE [elenco condizioni]

In seguito al termine SELECT, si vanno ad inserire tutti i campi che si vogliono visualizzare come risultato della query. In alternativa qualora si volessero visualizzare tutti i campi della tabella, si utilizza il simbolo asterisco *, che sarà letto come "all".

Il termine FROM permette di definire la tabella dalla quale attingere le informazioni, mentre il WHERE rappresenta le condizioni per la selezione dei dati.
Se per esempio volessi selezionare dalla tabella utente, tutti coloro che si chiamano "Francesco":

SELECT * FROM utente WHERE nome = "Francesco";

E' anche possibile concatenare più condizioni fra loro utilizzando gli operatori logici (AND, OR).

SQL

CRUD - Lettura

Per quanto riguarda gli operatori di confronto nelle condizioni del WHERE, ci sono anche altre opzioni rispetto al semplice confronto con il simbolo "=", ">", "<", ovvero:

  • BETWEEN - per il confronto fra un range numerico o di date
  • LIKE - per ricercare porzioni di un testo
  • IN - per ricercare fra alcuni valori predefiniti
  • IS NULL - per ricercare fra i valori NULL

SQL

CRUD - Lettura - BETWEEN

L'operatore BETWEEN si utilizza per ricercare fra un range predefinito di valori; è molto utilizzato quando si vuole selezionare uno specifico set di date o di valori numerici.

Se per esempio volessi selezionare tutti gli utenti che appartengono alla categoria che va da ID 1 a ID 10:

SELECT * FROM utente WHERE id_categoria BETWEEN 1 AND 10;

L'utilizzo del BETWEEN è dunque una forma abbreviata dell'espressione:

SELECT * FROM utente WHERE id_categoria >= 1 AND id_categoria <= 10;

SQL

CRUD - Lettura - LIKE

L'operatore LIKE è molto utilizzato in fase di ricerca, in quanto permette di selezionare campi che contengono anche solo in parte un determinato valore. Il caso d'utilizzo più diffuso, è la ricerca testuale.
Se per esempio volessi selezionare tutti gli utenti il cui nome inizia per "Fra":

SELECT * FROM utente WHERE nome LIKE "Fra%";

Il simbolo %, indica "qualunque altra cosa", dunque permetterà alla query di selezionare tutte le righe dove il nome inizia per Fra e poi contiene (oppure no) altro testo.

Il simbolo % è dunque un jolly, dunque la sua posizione determina il risultato della selezione; se avessimo scritto la query con nome LIKE "%Fra%", sarebbero stati selezionati anche tutti i record all'interno dei quali il nome conteneva il testo "Fra".

In opposizione al LIKE si utilizza l'operatore NOT LIKE che ha esattamente il funzionamento opposto e lavora dunque per esclusione.

SQL

CRUD - Lettura - IN

Se invece si vogliono trovare i record che rientrano in alcuni valori ben definiti, è possibile utilizzare l'operatore IN.
L'utilizzo più efficiente di questo operatore, si ha per i numeri e per i testi, dove ovviamente si conoscono i valori certi.
Se per esempio volessimo selezionare tutti gli utenti che appartengono alla categoria 1 o alla categoria 3 o ancora alla categoria 5:

SELECT * FROM utente WHERE id_categoria IN(1, 3, 5);

L'utilizzo dell'operatore IN, altro non è che un'abbreviazione di:

SELECT * FROM utente WHERE id_categoria = 1 OR id_categoria = 3 OR id_categoria = 5;

Anche per questo operatore è disponibile la negazione, ovvero la selezione per tutti i record che NON rientrano in alcuni valori, ovvero l'operatore NOT IN.

SQL

CRUD - Lettura - IS NULL

Per verificare se un valore è uguale a NULL, si utilizza l'operatore IS NULL

SELECT * FROM utente WHERE id_categoria IS NULL;

Per verificare che il valore sia diverso da NULL, si utilizza invece IS NOT NULL

SELECT * FROM utente WHERE id_categoria IS NOT NULL;

SQL

CRUD - Lettura - DISTINCT

In molti casi può esser utile selezionare i valori unici presenti all'interno della tabella, per elaborazioni successive.
Per far ciò si utilizza l'operatore di selezione DISTINCT.

Se per esempio si volesse un riepilogo di tutte le categorie di utenti presenti nel DB:

SELECT DISTINCT(id_categoria) FROM utente;

In questo caso non otterremo dunque una lista di tutte le categorie presenti nella tabella delle categorie, bensì tutte le categorie associate agli utenti in maniera distinta.

SQL

CRUD - Lettura - ORDER BY

Un'altra opzione molto importante, è l'ordinamento dei risultati in funzione dei valori di alcuni campi.
L'ordinamento può essere impostato in ordine crescente (ASC) o decrescente (DESC). Tutto ciò utilizzando l'operatore ORDER BY.

Se per esempio si vogliono ordinare tutti gli utenti in ordine crescente per cognome (dalla A alla Z):

SELECT * FROM utente ORDER BY cognome ASC;

Si precisa che il termine ASC potrebbe essere omesso, in quanto opzione di default per l'ORDER BY.
E' inoltre possibile ordinare i risultati anche secondo più campi, che saranno gestiti per priorità, utilizzando la sintassi:

SELECT * FROM utente ORDER BY cognome, nome;

In questo modo tutti i risultati saranno ordinati prima per cognome e successivamente mantenendo l'ordine dei cognomi, ci sarà un adeguamento per nome.
Un eventuale Maria Rossi, precederà dunque nei risultati l'eventuale Mario Rossi.

SQL

CRUD - Lettura

La scelta delle operazioni di confronto, delle condizioni e del criterio di ordinamento, è fondamentale per determinare la qualità del database e le performance per l'elaborazione delle query.

Operatori come IN, BETWEEN o LIKE, sono sicuramente più esigenti in termini di risorse hardware, dunque occorre utilizzarli solo quando strettamente necessario.

Allo stesso modo l'opzione ORDER BY, prevede uno scorrimento multiplo delle righe risultanti della query, a seconda dei campi inseriti per l'ordinamento stesso. E' evidente dunque che se abbiamo una query che restituisce 1.000 risultati, e inseriamo l'ordinamento su due campi, il server dovrà svolgere migliaia di cicli macchina in più per ordinare i nostri record.

Questa operazione potrebbe portare dunque ad un blocco del database per sovraccarico di memoria!

Essendo dunque il database lo specchio principale delle performance di un applicativo, è necessario dosare al meglio ogni signolo aspetto ed ogni singola query.

SQL

CRUD - Lettura - ALIAS

E' possibile definire degli alias per le tabelle e i campi delle SELECT, utili per fornire una lettura più chiara delle informazioni o gestire al meglio le query.

Per inserire un alias, si utilizza la funzione AS.

SELECT id AS identificativo, nome, cognome FROM utente AS account;

Dall'esempio precedente otterremo dunque una tabella con 3 campi, chiamati "identificativo", "nome" e "cognome".
L'alias ha un ciclo di vita limitato alla query stessa. Non va dunque ad impattare sulla struttura del database e delle singole tabelle.

SQL

CRUD - Lettura - JOIN

Una delle caratteristiche fondamentali del linguaggio SQL è la possibilità di estrarre contemporaneamente informazioni da più tabelle.
Questo processo si identifica con il nome di JOIN e può essere di cinque tipologie:

  • INNER JOIN o JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • UNION

L'utilizzo delle JOIN prevede comunque in tutti i casi l'associazione delle righe delle singole tabelle secondo una chiave ben definita.
La differenza fra le cinque tipologie, consiste proprio nel criterio di associazione delle varie righe fra le tabelle in relazione.

Le JOIN sono dunque un elemento essenziale per i database relazionali, o al tempo stesso possono permettere operazioni di relazione anche per i database non relazionali.

SQL

CRUD - Lettura - INNER JOIN

Le INNER JOIN o semplicemente JOIN, sono le associazioni per eccellenza fra due tabelle.
Permettono di unire le informazioni, affinchè la condizione di relazione sia necessariamente verificata.


Proviamo per esempio a creare una query che riporti per ogni utente non solo le informazioni di base, ma anche il nome della categoria appartenente, anzichè il solo ID; dovremo dunque effettuare una relazione tramite la chiave id_categoria.

SELECT u.id, u.nome, u.cognome, c.nome AS nome_categoria
FROM utente AS u INNER JOIN categoria AS c 
	ON u.id_categoria = c.id 

Notare l'utilizzo fondamentale degli alias per distinguere i campi delle varie tabelle ed evitare dunque errori di sovrascrizione.

SQL

CRUD - Lettura - LEFT JOIN

Le LEFT JOIN permettono di selezionare tutti i valori della prima tabella in relazione con la seconda, indipendentemente dalla relazione. In caso di mancata relazione, i campi della seconda tabella risulteranno NULL.


Se per esempio volessimo selezionare indipendentemente tutti gli utenti anche se non hanno una categoria associata:

SELECT u.id, u.nome, u.cognome, c.nome AS nome_categoria
FROM utente AS u LEFT JOIN categoria AS c 
	ON u.id_categoria = c.id 

Se per esempio un utente non ha una categoria ben definita (per esempio NULL), con la LEFT JOIN comparirà comunque nei risultati; nel caso della INNER JOIN sarebbe invece rimasto escluso.

SQL

CRUD - Lettura - RIGHT JOIN

Le RIGHT JOIN hanno invece il funzionamento opposto alle LEFT JOIN, facendo dunque riferimento alla seconda tabella.

SQL

CRUD - Lettura - FULL OUTER JOIN

Le FULL JOIN hanno un comportamento mixato fra le LEFT e le RIGHT JOIN.

Estraggono dunque indistintamente tutti i record della prima tabella (partendo da quelli che non trovano corrispondenza nella relazione), e tutti i record della seconda tabella, inserendo per ultimi quelli che non trovano corrispondenza


SELECT u.id, u.nome, u.cognome, c.nome AS nome_categoria
FROM utente AS u FULL OUTER JOIN categoria AS c 
	ON u.id_categoria = c.id 

SQL

CRUD - Lettura - UNION

La funzione UNION permettono così come indica il termine stesso, l'unione di due tabelle identiche, ovvero aventi la stessa struttura di campi come natura, nomenclatura e disposizione (ordine).

Se per esempio avessimo una tabella chiamata "dipendente", avente la stessa struttura della tabella "utente", potremmo eseguire la query:

SELECT id, nome, cognome
FROM utente 
UNION
SELECT id, nome, cognome
FROM dipendente

In automatico la funzione UNION esclude i valori duplicati.
Se invece non si vogliono escludere i valori duplicati, si modifica la funzione UNION con UNION ALL

SQL

CRUD - Modifica

Per modificare le informazioni all'interno delle tabelle, si utilizza il costrutto UPDATE.
Se per esempio vogliamo modificare il nome di tutti gli utenti che si chiamano "Francesco", sostituendolo con "Ciccio":

UPDATE utente 
SET nome = "Ciccio" 
WHERE nome = "Francesco";

E' possibile modificare contemporaneamente anche più campi, agendo sulle voci del SET:

UPDATE utente 
SET nome = "Ciccio", cognome = "Riccio" 
WHERE nome = "Francesco";

E' fondamentale definire al meglio le condizioni del WHERE. Viceversa si rischierebbe di agire sull'intera tabella qualora omesse, o sovrascrivere informazioni e perderle definitivamente.
Diventa dunque essenziale il riferimento alla chiave primaria quando si vuole agire su una sola informazione.

SQL

CRUD - Rimozione

Per cancellare le informazioni all'interno delle tabelle, si utilizza il costrutto DELETE.
Se per esempio vogliamo rimuovere tutti gli utenti che appartengono alla categoria con ID = 1:

DELETE 
FROM utente
WHERE id_categoria = 1;

Anche in questo caso è fondamentale definire sempre al meglio le condizioni del WHERE per evitare operazioni massive non desiderate.

Database con PHP

Utilizzo dei database in un applicativo web

Database PHP

Avendo acquisito le competenze di base per la gestione delle query di un database, è possibile iniziare ad adoperarlo in un contesto di un applicativo su web.

Tenendo conto dell'infrastruttura client/server, il database si pone nella parte di back-end ovvero del server, in quanto riesce ad interagire esclusivamente con il linguaggio PHP, prima che la pagina sia visualizzata all'utente.


L'elaborazione delle query da parte del PHP, è come già detto fondamentale, in quanto determina la sicurezza dell'integrità dei dati archiviati e le performance dei tempi di elaborazione dell'applicativo.

Database PHP

Per poter utilizzare i database all'interno della pagina PHP, occorre innanzitutto stabilire una connessione:

<?php
	# Inizializzo i parametri di connessione
	$server = "localhost";
	$username = "root";
	$password = "root";
	$database = "testdb";
	
	# Stabilisco la connessione
	$connessione = mysqli_connect($server, $username, $password, $database);
	
	# Se non si riesce a stabilire la connessione, genero errore bloccante
	if(!$connessione) {
		# Visualizzo errore SQL per la connessione
		die("Impossibile connettersi al database! ". mysqli_connect_error());
	}else {
		# Se la connessione è andata a buon fine posso effettuare query al db
		....
	}
?>

Database PHP

Una volta inizializzata la connessione al database, è possibile effettuare liberamente tutte le query necessarie.

<?php
	# Esempio query di insert
	$sql_query = "INSERT INTO utente (nome, cognome) VALUES('Francesco', 'Pisciotta')";
	$risultato = mysqli_query($connessione, $sql_query);
	
	if($risultato) {
		# Se la tabella prevede una PK con AUTO INCREMENT è possibile leggere il valore inserito
		$id_auto = mysqli_insert_id($connessione);
		echo "Riga inserita con successo ID: ".$id_auto;
	}else {
		echo "Errore nella query: ".mysqli_error($connessione);
	}
?>
<?php
	# Esempio query di update
	$risultato = mysqli_query($connessione, "UPDATE utente SET nome = 'Ciccio' WHERE id = 1");
	
	if($risultato) {
		echo "Riga modificata!";
	}else {
		echo "Errore nella query: ".mysqli_error($connessione);
	}
?>

Database PHP

Vediamo invece come leggere il contenuto del database e gestire i risultati:

<?php
	# Esempio query di select
	$sql_query = "SELECT nome, cognome, email FROM utente WHERE id > 0";
	$risultato = mysqli_query($connessione, $sql_query);
	
	if($risultato === TRUE) {
		if(mysqli_num_rows($risultato) > 0) {
			# Leggo i risultati riga per riga
			while($riga = mysqli_fetch_assoc($risultato)) {
				# Per ogni ciclo, l'array $riga avrà una riga della query risultante
				echo "Utente ".$riga["nome"]." ".$riga["cognome"]."<br />";
			}
		}else {
			echo "Nessun risultato trovato";
		}
	}else echo "Errore nella query: ".mysqli_error($connessione);
?>

Da notare come a differenza delle altre operazioni di CRUD, in fase di lettura non è sufficiente effettuare un controllo del tipo if($risultato) in quanto se ci fossero 0 risultati, sarebbe riconosciuto come condizione falsa.
Proprio per questo è necessario inserire anche un controllo per verificare se la query ha prodotto o meno risultati.

Database PHP

Per quanto riguarda le query di UPDATE e DELETE può invece essere utile capire quante righe sono state interessate nell'operazione; per farlo si può far riferimento alla funzione msqli_affected_rows():

<?php
	# Esempio query di update
	$risultato = mysqli_query($connessione, "UPDATE utente SET nome = 'Ciccio' WHERE id = 1");
	
	if($risultato) {
		$righe_modificate = msqli_affected_rows($connessione);
		echo "Righe modificate: ".$righe_modificate;
	}else {
		echo "Errore nella query: ".mysqli_error($connessione);
	}
?>

Database PHP

Ovviamente è possibile creare le query all'interno del PHP con funzioni e variabili calcolate o in arrivo da un form HTML.

In questi casi occorre sempre fare attenzione nella manipolazione dei dati, soprattutto se derivanti da form, per evitare errori nelle query o nel peggiore dei casi la manomissione del database.
La soluzione è quella di apportare sempre tutti i controlli dei campi prima di eseguire le query.

<?php
	# Esempio di inserimento dati da form
	$nome = $_POST["nome"];
	$cognome = $_POST["cognome"];
	
	/* Salto volontariamente i controlli sui campi in POST */
	
	$sql_query = "INSERT INTO utente SET nome = '$nome', cognome = '$cognome'";
	$risultato = mysqli_query($connessione, $sql_query);
	
	if($risultato) {
		$id_auto = mysqli_insert_id($connessione);
		echo "Riga inserita con successo ID: ".$id_auto;
	}else {
		echo "Errore nella query: ".mysqli_error($connessione);
	}
?>

Database PHP

Facendo riferimento all'esempio precedente, dove sono stati saltati volontariamente i controlli sui campi del form, prima dell'esecuzione della query, sarebbe stato semplicissimo manomettere il database tramite un attacco di SQL injection, ovvero letteralmente iniezione di codice SQL.

Se per esempio nel form l'utente avesse inserito i seguenti valori:

	NOME = Francesco
	COGNOME = Pisciotta'; DELETE FROM utente WHERE id != '0;

Nel processo PHP, sostituendo alle variabili il contenuto in POST, avrei ottenuto una query del tipo

<?php
	$sql_query = "INSERT INTO utente SET nome = 'Francesco', 
		cognome = 'Pisciotta'; DELETE FROM utente WHERE id != '0'";
?>

Questa operazione avrebbe dunque resettato in una frazione di secondo, l'intero contenuto della tabella utente, senza possibilità di recuperare le informazioni!

Esercitazione

Utilizzo dei database in un applicativo web

Esercitazione

Creare un sistema di CRUD per il database di Trenitalia, tramite un apposito CRM.

  • Si potrà accedere al CRM solo in seguito ad un login (design http://bit.ly/1Sh90eq)
  • In seguito al login, si accederà al pannello di controllo (design http://bit.ly/1efZYzU), tramite il quale sarà possibile gestire i treni (aggiunta, modifica, visualizzazione e rimozione) e gli utenti (solo per alcuni amministratori di sistema).
  • L'interfaccia di CRUD fra treni e utenti sarà la stessa. Sulla sinistra sarà riportato l'elenco e sulla destra un log delle attività fatte rispettivamente sui treni e sugli utenti, in modo tale da risalire sempre all'amministratore che ha effettuato le operazioni.
  • Al click sull'APRI dei treni o degli utenti, o all'inserimento di un nuovo elemento, si arriverà ad una semplice pagina contenente un form con i campi rispettivi.

<Thank u!>

Sempre pronto a darti una mano