Validare i dati usando un TRIGGER

English version

Ho scritto un post su come emulare la clausola CHECK in MariaDB e MySQL utilizzando una vista. Qui seguiremo un approccio differente: useremo i TRIGGER per raggiungere lo stesso scopo. Infine discuteremo quale dei due metodi sia il migliore.

Creiamo ciò che ci serve

Creiamo una semplice tabella:

-- assicuriamoci di poter creare `person`
DROP VIEW   IF EXISTS `person`;
DROP TABLE  IF EXISTS `person`;

-- ecco la tabella su cui bisogna validare i dati
CREATE TABLE `person`
(
	`id`  mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
	`name`  char(50) NOT NULL,
	`email`  char(80) NOT NULL,
	PRIMARY KEY (`id`)
)
	ENGINE = Aria;

`person` contiene una Chiave Primaria, un campo chiamato `name` e `email`. Ci interessa soltanto `email`. Vogliamo validarlo utilizzando un pattern LIKE. Ma come?

Creiamo un TRIGGER

I TRIGGER si possono associare alle operazioni DML di base (INSERT, UPDATE, DELETE). Se vogliamo essere certi che la tabella contenga solo dati validi, dobbiamo associare un TRIGGER a INSERT e uno a UPDATE, ma per brevità mostrerò qui solo quello su INSERT. Inoltre i TRIGGER possono essere eseguiti prima (BEFORE) o dopo (AFTER) l’operazione a cui si riferiscono; siccome vogliamo impedire che i dati non validi vengano scritti nella tabella, naturalmente useremo l’opzione BEFORE.

-- validiamo l'email
CREATE TRIGGER `person_validate_insert`
	BEFORE INSERT
	ON `person`
	FOR EACH ROW
BEGIN
	IF NEW.`email` NOT LIKE '%_@%_.__%' THEN
		SIGNAL SQLSTATE VALUE '45000'
			SET MESSAGE_TEXT = '[table:person] - `email` column is not valid';
	END IF;
END;

I TRIGGER non possono avere un commento, perciò tento sempre di dar loro dei nomi descrittivi. La prima parola è person, cioé il nome della tabella; poi spiego che cosa fa il TRIGGER: ‘validate insert’, valida l’inserimento.

Il TRIGGER verifica se la nuova `email` è valida. Se lo è, l’esecuzione può proseguire e la INSERT verrà eseguita. Ma se non lo è lancia un errore. In questo modo l’esecuzione si ferma, quindi la INSERT non sarà eseguita.

Qualche parola sull’errore. L’SQLSTATE è ‘45000’, come consigliato dalla documentazione di MySQL per gli errori definiti dall’utente. Il messaggio è in inglese – questo è fondamentale non solo perché potremmo lavorare in un ambiente internazionale, ma anche perché potremmo decidere di pubblicare il codice che scriviamo con una licenza libera. Inoltre le istruzioni dei linguaggi di programmazione si basano sull’inglese e questo porta a una serie di affinità che facilitano l’integrazione. Comunque, il messaggio comincia con un’informazione posta tra le [parentesi quadre]. Quando lancio un errore da una Stored Routine, ci scrivo il nome della routine; quando lo lancio da un TRIGGER, ci scrivo il nome della tabella. Segue un messaggio di errore descrittivo. “Descrittivo” significa che comunica all’utente che cosa stava tentando di fare (INSERT) e perché l’operazione ha fallito (`email` column is not valid).

Testiamo!

Cerco di partire dal presupposto che ciò che non testo (ma potrei testare), semplicemente non funziona. Perciò, proviamo a inserire una riga valida:

MariaDB [test]> INSERT INTO `person`
    ->          (`name`, `email`)
    ->  VALUES
    ->          ('Emiliano Zapata', 'zapata@revolucion.mx');
Query OK, 1 row affected (0.59 sec)

Bene! Ora tentiamo di inserirne una non valida:

MariaDB [test]> INSERT INTO `person`
    ->          (`name`, `email`)
    ->  VALUES
    ->          ('John Doe', 'misterdoe@nowhere');
ERROR 1644 (45000): [table:person] - `email` column is not valid

Ottimo! E’ esattamente l’errore che ci aspettavamo di vedere.

Qual è la tecnica migliore?

Dopo aver spiegato due tecniche diverse (vedi Emulare la clausola CHECK in MariaDB), forse dovrei dire quale delle due è migliore. Invece scriverò solo qualche ota, che dovrebbe chiarificare i pro e i contro di ogni metodo:

* Chi usa MariaDB 5.3 o più vecchio, oppure MySQL 5.4 o più vecchio, non può usare l’istruzione SIGNAL. Potrebbe lanciare una SELECT su una tabella inesistente, che è un po’ più sporco (tu sai perché vedi l’errore, ma altri sviluppatori potrebbero non capirlo subito).
* Le società di hosting spesso non permettono di usare i TRIGGER – in questo caso, bisognerà usare la tecnica della vista.
* Le prestazioni di INSERT/UPDATE non dovrebbero cambiare di molto usando un metodo o l’altro. Quelle di DELETE e SELECT potrebbero essere un po’ più lente usando la vista.
* La tecnica dei TRIGGER presuppone 2 trigger distinti (sulla insert, sull’update; e forse sulla delete, se vuoi leggere righe da altre tabelle per assicurare l’integrità dei dati). La tecnica della vista richiede appunto una vista e bisogna rinominare una tabella. La seconda opzione è forse più comoda… ma questo è TRUE per me, e potrebbe essere FALSE per te. (un modo booleano per dire che è soggettivo)
* Questo significa anche che la tecnica dei TRIGGER richiede del codice duplicato (stesse istruzioni nel trigger INSERT e nel trigger UPDATE TRIGGER). Questo problema si può risolvere usando una Stored Routine, ma è più lento.

Siccome questi due metodi hanno prestazioni simili e la manutenibilità è più o meno equivalente, si tratta di una scelta personale.

A presto!

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...