Emulare la clausola CHECK in MariaDB

English version

MariaDB (come MySQL e i suoi fork) non supporta la clausola CHECK dell’istruzione CREATE TABLE. Ma si può emularla con una vista.

DDL

Per prima cosa, creiamo una tabella:

CREATE TABLE `t_person`
(
	`id`       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT ,
	`name`     CHAR(30) NOT NULL ,
	`surname`  CHAR(30) NOT NULL ,
	`email`    CHAR(50) NOT NULL ,
	PRIMARY KEY (`id`)
);

La tabella è pronta per ricevere i dati, ma vogliamo controllare che questi siano validi. Quando si inserisce una nuova riga, vogliamo controllare che `surname` (cognome) contenga almeno 2 caratteri e che `email` contenga ‘@’ e ‘.’.

Quindi passiamo a creare una vista, che mostra solo i dati che seguono queste due semplici regole:

CREATE VIEW `person`
	AS SELECT * FROM `t_person`
		WHERE LENGTH(`surname`) > 1
			AND `email` LIKE '%_@_%._%'
	WITH CHECK OPTION;

La vista è INSERTable, perché è costruita su un’unica tabella fisica e non contiene funzioni aggreganti o raggruppamenti. La clausola WITH CHECK OPTION fa in modo che le nuove righe vengano rifiutate se non soddisfano la condizione WHERE.

DML

Ora possiamo provare a inserire dati nella vista. Iniziamo con ua riga non valida:

INSERT
	INTO `person`
		(`name`, `surname`, `email`)
	VALUES
		('Mario', 'Rossi', 'mario.rossi');

`email` non contiene il catattere ‘@’, perciò la riga viene respinta con il seguente messaggio di errore:
[Err] 1369 - CHECK OPTION failed 'test.person'

Ora proviamo a inserirne una valida:

INSERT
	INTO `person`
		(`name`, `surname`, `email`)
	VALUES
		('Mario', 'Rossi', 'mario.rossi@rossionline.it');

La riga viene inserita con successo!

E’ possibile effettuare le SELECT sulla tabella `t_person` oppure su `person`, come si preferisce. Se si usa `person`, si può fingere che la vista sia una tabella e che `t_person` non esista – è assolutamente trasparente. Tuttavia, eseguendo le SELECT su `person` viene aggiunta una WHERE che è sempre vera ma influenza leggermente le prestazioni.

A presto!

MariaDB: Come creare un Buco Nero se BLACKHOLE è disattivato

English version

Lo Storage Engine BLACKHOLE fa in modo che i dati cadano in un buco nero. Non solo i nuovi dati che si tenta di inserire, ma anche quelli già esistenti. In altre parole, BLACKHOLE disabilita una tabella e ne cancella il contenuto. Può essere utile per il debug (non esegue una query, ma c’è un errore viene segnalato) o altri scopi particolari.

Tuttavia, i fornitori di hosting di solito disabilitano BLACKHOLE. Non chiedetemi in che modo questo semplicissimo Storage Engine possa danneggiare il loro servizio; sta di fatto che generalmente non è possibile usarlo.

Ma allora, come si può emulare una tabella BLACKHOLE? La risposta è semplice: basta usare una vista!

Prima di tutto, ci serve una tabella. Il procedimento è banale e privo di rischi, perciò evito di scrivere una a CREATE TABLE. Usa una tabella già esistente. L’esempio seguente presuppone che questa si chiami `t_user` – ma chiamala come ti pare.

Poi, bisogna creare una vista. Ne useremo una del tipo più semplice, che rispecchia il contenuto della tabella. E’ possibile leggere e scrivere i dati. Ecco il codice:

CREATE OR REPLACE VIEW `t_user`
	AS SELECT * FROM `user`;

Da ora in poi useremo `t_user` invece di `user` per tutte le nostre operazioni. Ma a un certo punto, vogliamo disabilitarla – cioè trasformarla in un Buco Nero!

Come ho detto, il procedimento è banale. Si ridefinisce `user`, ma con 2 modifiche:
* Aggiungiamo una clausola WHERE FALSE. Ovviamente la vista non conterrà dati (perché non è possibile che una riga soddisfi WHERE FALSE).
* Aggiungiamo una clausola WITH CHECK OPTION. La vista non sarà scrivibile (perché una riga potrà essere inserita solo se soddisfa WHERE FALSE, il che è appunto impossibile).

Ecco il codicee:

CREATE OR REPLACE VIEW `t_user`
	AS SELECT * FROM `user` WHERE FALSE
	WITH CHECK OPTION;

Quando vogliamo trasformare di nuovo il Buco Nero in un pianeta abitabile, torniamo a eseguire la prima istruzione.

La clausola OR REPLACE è utile in questo caso, perché non siamo costretti a eseguire DROP VIEW e poi di nuovo CREATE VIEW – è sufficiente un’unica operazione atomica.

A presto!

Ciao, mondo!

FALSE non è NULL nasce come versione italiana del blog FALSE IS NOT NULL. I contenuti verranno tradotti dall’inglese, mentre il titolo è stato tradotto… dall’SQL.

Non ho ancora chiaro come regolarmi con FALSE non è NULL:
* Conterrà tutti gli articoli di FALSE IS NOT NULL?
* Conterrà traduzioni di altri materiali interessanti?
Mah, vedremo.

Nel frattempo, qualche parola su di me:
Mi chiamo Federico Razzoli, sono un programmatore italiano e sono l’autore di entrambi i blog. Lo scopo principale dei due progetti è la condivisione libera delle conoscenze, il fine secondario è convincervi che un miliardo di euro non è un prezzo troppo alto per una mia consulenza. Il secondo scopo non è semplice da raggiungere, ma l’importante è non arrendersi.

Per contattarmi (soprattutto se vi siete convinti di quanto ho appena affermato), cliccate sul menù qua sopra, pagina “Chi Sono”.

Questo è tutto, gente.
A presto!