Stored Routine per gestire facilmente l’SQL_MODE

English version

Lavorare con l’SQL_MODE può essere sconfortante, perché è una lista di parole lunghe separata da virgole. Francamente odio riscrivere i flag, cercare di leggere una lista illeggibile, etc. Certo, alcune funzioni stringa possono aiutare (Justin Swanhart consiglia di usare REPLACE() per rendere leggibili le liste separate da virgola).

Ho fatto un piccolo set di Stored Routine che mi permettono in maniera semplice di mostrare l’SQL_MODE, aggiungere un flag, togliere un flag e controllare se il flag è presente. Queste routine lavorano con l’SQL_MODE globale; se non vi va bene, sostituite “@@global.” con “@@session.” nel file SQL prima di installarlo (facile, no?).

Potete scaricare (o migliorare) queste routine usando il repo SQL_MODER su GitHub.

E ora, qualche esempio.

Mostrare i flag attivi:

MariaDB [(none)]> CALL _.sql_mode_show();
+----------------------------+
| FLAG                       |
+----------------------------+
| ERROR_FOR_DIVISION_BY_ZERO |
| NO_AUTO_CREATE_USER        |
| NO_ENGINE_SUBSTITUTION     |
| NO_ZERO_DATE               |
| NO_ZERO_IN_DATE            |
| ONLY_FULL_GROUP_BY         |
| STRICT_ALL_TABLES          |
| STRICT_TRANS_TABLES        |
+----------------------------+
8 rows in set (0.46 sec)

(questo è l’SQL_MODE che consiglio di usare)

Verificare se un flag è attivo:

MariaDB [(none)]> SELECT _.sql_mode_is_set('HIGH_NOT_PRECEDENCE');
+------------------------------------------+
| _.sql_mode_is_set('HIGH_NOT_PRECEDENCE') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.38 sec)

Aggiungere uno o più flag:

MariaDB [(none)]> CALL _.sql_mode_set('NO_UNSIGNED_SUBTRACTION,HIGH_NOT_PRECEDEN
CE');
Query OK, 0 rows affected (0.07 sec)

MariaDB [(none)]> CALL _.sql_mode_show();
+----------------------------+
| FLAG                       |
+----------------------------+
| ERROR_FOR_DIVISION_BY_ZERO |
| HIGH_NOT_PRECEDENCE        |
| NO_AUTO_CREATE_USER        |
| NO_ENGINE_SUBSTITUTION     |
| NO_UNSIGNED_SUBTRACTION    |
| NO_ZERO_DATE               |
| NO_ZERO_IN_DATE            |
| ONLY_FULL_GROUP_BY         |
| STRICT_ALL_TABLES          |
| STRICT_TRANS_TABLES        |
+----------------------------+
10 rows in set (0.13 sec)

Query OK, 0 rows affected (2.09 sec)

Disattivare un flag:

MariaDB [(none)]> CALL _.sql_mode_unset('HIGH_NOT_PRECEDENCE');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT _.sql_mode_is_set('HIGH_NOT_PRECEDENCE');
+------------------------------------------+
| _.sql_mode_is_set('HIGH_NOT_PRECEDENCE') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)

Se hai sbagliato a digitare il flag (o non era impostato):

MariaDB [(none)]> CALL _.sql_mode_unset('hello world');
ERROR 1644 (45000): Flag 'hello world' was not set

A presto!

MariaDB/MySQL: Procedure facili per leggere la Diagnostics Area

English version

UPDATE 2013-08-30: Ho risolto 2 bug e creato un repo su GitHub chiamato sql_diagnostix. Scusate se non l’ho fatto prima. Se io o chiunque altro troverà altri bug, aggiornerò il repo.

Il problema

Per visualizzare velocemente le informazioni sugli errori e i warning generati dall’ultima istruzione eseguita, è possibile utilizzare SHOW WARNINGS. Però i risultati di questa istruzione non possono essere lette via SQL, perciò non è possibile usarla per gestire gli errori in uno Stored Program.

A questo scopo si può usare GET DIAGNOSTICS, che ha però due problemi:

  • Richiede molto codice
  • Non esiste un modo facile per mostrare tutte le informazioni presenti nella Diagnostics Area.

Il primo problema può scoraggiare molti sviluppatori dall’usare GET DIAGNOSTICS.

Il secondo problema nella maggior parte dei casi non è così importante, perché le info mancanti sono SQLSTATE e le informazioni che possono essere impostate solo con SIGNAL e RESIGNAL, e che non possono essere lette da un programma esterno (SCHEMA_NAME, CURSOR_NAME, etc). Tuttavia leggere SQLSTATE può essere importante, e accedere a tutte le clausole di SIGNAL può aiutare nel debug e nella soluzione dei problemi… ecco perché esistono :-)

Oracle MySQL 5.7 ha due Diagnostics Area e supporta la parola chiave STACKED per accedere alla seconda DA da un error HANDLER. Questo però non aiuta molto: permette solo di eseguire un’altra istruzione che accede a una tabella, prima di leggere le informazioni sugli errori che si sono verificati. Perciò la DA STACKED non sembra in grado di risolvere problemi reali.

La soluzione

Ho creato tre Stored Procedure per risolvere questi problemi:

  • _.materialize_diagnostics_area()
  • _.show_diagnostics_area()
  • _.show_full_diagnostics_area()

Tutte le procedure creano una tabella temporanea MEMORY chiamata DIAGNOSTICS_AREA in un database chiamato _. Se la tabella esiste già, viene eliminata e ricreata. Incidentalmente viene anche cancellata la Diagnostics Area del server, ma le informazioni rimangono disponibili nella tabella finché non si chiama di nuovo una di queste procedure.

_.DIAGNOSTICS_AREA ha una colonna chiamata ID, che indica la posizione di ogni singola condizione nella Diagnostics Area. Le altre colonne sono uguali alle proprietà delle condizioni della Diagnostics Area (MYSQL_ERRNO, etc). L’unica eccezione è la colonna SQLSTATE, che usa il nome utilizzabile in SIGNAL e RESIGNAL, non quello usato nella Diagnostics Area (cioè RETURNED_SQLSTATE).

Siccome sia gli Stored Program, sia i programmi esterni possono accedere a _.DIAGNOSTICS_AREA, questa permette ai programmi esterni di leggere tutte le informazioni che si possono impostare con SIGNAL.

Si può chiamare direttamente materialize_diagnostics_area() per copiare interamente la DA nella tabella, per poi leggere le informazioni con delle query.

show_full_diagnostics_area() mostra tutte le informazioni, dopo aver popolato la tabella. E’ una sorta di SHOW WARNINGS con molte colonne in più.

show_diagnostics_area() mostra solo le colonne ID, SQLSTATE, MYSQL_ERRNO, MESSAGE_TEXT.

Esempio:

MariaDB [test]> INSERT INTO `t` VALUES (1/0), (1/0), (1/0), (-1);
Query OK, 4 rows affected, 4 warnings (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 4
 
MariaDB [test]> CALL _.show_diagnostics_area();
+----+----------+-------------+--------------------------------------------+
| ID | SQLSTATE | MYSQL_ERRNO | MESSAGE_TEXT                               |
+----+----------+-------------+--------------------------------------------+
|  1 | 22012    |        1365 | Division by 0                              |
|  2 | 22012    |        1365 | Division by 0                              |
|  3 | 22012    |        1365 | Division by 0                              |
|  4 | 22003    |        1264 | Out of range value for column 'c' at row 4 |
+----+----------+-------------+--------------------------------------------+
4 rows in set (0.09 sec)

Limitazioni

Se uno Stored Program deve essere veloce, queste procedure possono essere utilizzate solo per il debug.

Come detto sopra, ogni chiamata elimina le informazioni già presenti nella tabella _.DIAGNOSTICS_AREA. E’ una scelta di progettazione.

C’è un’informazione presente nell’output di SHOW WARNING che però manca nella tabella _.DIAGNOSTICS_AREA: la colonna Level. Il motivo è che non sono riuscito a trovare nella documentazione di MySQL come distinguere un Warning da una Nota, se non con SHOW WARNINGS. Se non c’è modo, o se un modo esiste ma non è documentato, non posso aggiungere questa informazione.

La Diagnostics Area STACKED di 5.7 non viene condiderata.

Il codice

Va bene, ho scritto abbastanza. Ora, se siete interessati:

Scaricate il codice SQL

Non spiego qui il codice perché mi sembra molto semplice ed è commentato. Date un’occhiata a materialize_diagnostics_area() e capirete cosa ho fatto.

A presto!

I Cursori di MariaDB/MySQL: un breve Tutorial

English version

In MariaDB e MySQL, i Cursori possono essere utilizzati solo dentro gli Stored Program (Stored Procedure e Function, Trigger, Eventi), sono lenti, e hanno funzionalità molto limitate. Detto questo, in alcuni casi possono comunque essere utili. Questa pagina spiega come utilizzarli, con un esempio triviale.

Ecco una Stored Procedure che usa un Cursore:

DELIMITER ||
    
DROP TABLE IF EXISTS `test`.`tab1`;
CREATE TABLE `test`.`tab1` (`c` TINYINT UNSIGNED);
INSERT INTO `test`.`tab1` (`c`) VALUES
    (1),
    (2),
    (3);
    
DROP PROCEDURE IF EXISTS `test`.`demo`;
CREATE PROCEDURE `test`.`demo`()
    READS SQL DATA
BEGIN
    DECLARE `eof` BOOL;
    DECLARE `val` INT;
    
    DECLARE `crs` CURSOR FOR
        SELECT `c`
            FROM `test`.`tab1`;
    
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND
        SET eof = TRUE;
    
    OPEN `crs`;
    `lp`: LOOP
        FETCH `crs` INTO `val`;
        IF `eof` IS TRUE THEN
            LEAVE `lp`;
        END IF;
        
        SELECT `val`;
    END LOOP;
    CLOSE `crs`;
    
    -- workaround for server bug
    DO (SELECT `c` FROM `test`.`tab1` WHERE FALSE);
END;
    
||
DELIMITER ;


Questa Procedura sarebbe completamente inutile nel Mondo Reale, ma ci serve come esempio: restituisce un diverso ResultSet per ogni riga che si trova nella tabella in test.tab1. Ora vado a spiegare come l’ho implementata.

Prima di tutto, dichiaro (DECLARE) alcune variabili. eof è molto importante, perché indica se il Cursore ha già letto tutte le righe restituite dalla query; per ora, naturalmente, è FALSE. Lo useremo più tardi. Per inciso, il tipo BOOL non esiste in Maria e My, e viene trattato come un TINYINT, così come TRUE è sinonimo di 1 e FALSE è sinonimo di 0. Ma utilizzare le parole chiave BOOL, TRUE e FALSE è un modo per rendere il codice più leggibile. val è la variabile che contiene il valore che ci accingiamo a leggere dalla tabella.

Poi dichiaro il Cursore. In questa istruzione vediamo il nome del Cursore (crs) e la query a cui è associato. Questa query deve essere scritta per esteso in questo punto, perché non è possibile utilizzare l’SQL dinamico con i Cursori. Vi è però un workaround: vedi il mio post Cursors for Dynamic SQL (prima o poi lo tradurrò in italiano).

A questo punto dichiaro l’HANDLER per gli errori che ricadono nella classe NOT FOUND. Qui ci interessa l’errore 1329, che si verifica quando il Cursore ha già letto tutte le righe restituite dalla query e tenta di leggerne un’altra. Ma questo lo spiegherò meglio più avanti.

A questo punto apro (OPEN) il Cursore. E’ a questo punto che la query viene eseguita.

E poi c’è il ciclo LOOP sulle righe. Che cosa accade qui? Beh, prima cerchiamo di estrarre una nuova riga dal ResultSet, con FETCH. Se l’operazione ha successo, il valore dell’unico campo presente nella SELECT viene copiato nella variabile val. Controlliamo il valore di eof e, siccome è FALSE, saltiamo la IF. E produciamo un ResultSet con il valore che abbiamo letto, che viene inviato al client. Questo avviene per tre volte, perché abbiamo creato una tabella con tre righe.

La quarta volta, l’istruzione FETCH fallisce, perché non ci sono più righe da leggere. Viene allora generato un errore, e quindi viene eseguito l’HANDLER che abbiamo definito prima. Qui la variabile eof viene impostata a TRUE. Siccome è un CONTINUE HANDLER, dopo questa istruzione, l’esecuzione della Routine riprende da dov’era rimasta e troviamo la IF. Questa volta eof è TRUE, perciò entriamo nella IF. E troviamo un’istruzione LEAVE associata all’etichetta lp. lp è il LOOP, perciò a questo punto ne usciamo.

Ora possiamo chiudere (CLOSE) il Cursore.

Ma ora abbiamo un piccolo problema. Le versioni di MySQL più vecchie della 5.6, e MariaDB, hanno un bug (che però MariaDB sta risolvendo) a causa del quale l’Errore 1329 sopravvive dopo l’HANDLER.Se non facciamo niente per rimuoverlo, verrà inviato al client. Ma se eseguiamo una query su una tabella fisica, l’Errore viene dimenticato (il motivo esatto per cui questo accade va oltre gli scopi di questo post). Non vogliamo che venga restituito un altro ResultSet, perciò utilizziamo la SELECT come subquery dell’istruzione DO (che non restituisce mai nulla). Usiamo WHERE FALSE per evitare di appesantire la Procedura.

Esercizi

Questa volta vi darò qualche semplice esercizio. Se li risolvete potete postare il vostro codice nei commenti, se volete. Se non riuscite a risolverli potete postare delle domande, in modo che io possa aiutarvi. Vi suggerisco di svolgere gli esercizi, perché non si impara molto senza la pratica.

1) Modificate la Procedura demo in modo che termini se incontra più di un valore NULL.

2) Scrivete una nuova Procedura che legge le righe dalla tabella PROCESSLIST del database information_schema, e uccide (KILL) tutte le connessioni che sono in idle ed esistono da più di N secondi – dove N è un parametro della Procedura.

Divertitevi!

Data Cleansing con STK/Unit

L’importanza del Data Cleansing

Un’ovvietà: i dati rappresentano un valore per le aziende.

Un’altra ovvietà: se però non sono corretti sono rappresentano un valore negativo, o almeno “rumore” che distrae dalle informazioni esatte. Sono un valore negativo se spingono il marketing a prendere decisioni sbagliate, sono semplice rumore se le mail di alcuni clienti non sono registrate correttamente.

I motivi per cui un database aziendale può contenere (e contiene) dati parzialmente errati sono molteplici. Vanno dalla pagina di registrazione del sito che accetta come numero di telefono '012345' alla segretaria stanca che ha sbagliato a digitare un nome in un foglio Excel, passando per i bug dei programmi e per i caratteri cirillici gestiti male.

I database, certo, hanno i vincoli di integrità. Ma li hanno se vengono effettivamente usati, ed errori umani permettendo. Parliamo in particolare di MariaDB e MySQL. Fino a poco tempo fa le tabelle erano MyISAM per default, e questa libreria non supporta le chiavi esterne. Del resto le applicazioni open source più diffuse (vuoi per evitare problemi di compatibilità, vuoi per negligenza) se anche usano InnoDB in genere non definiscono le chiavi esterne, tanto meno TRIGGER che mettano al riparo da interventi esterni avventati.

E’ allora indispensabile, per molte aziende, effettuare periodicamente una pulizia dei dati: il Data Cleansing, o Data Cleaning.

Il ruolo di STK/Unit

Per quanto possa sembrare strano, STK/Unit (uno strumento per lo Unit Test di cui ho già parlato in questo blog) è adatto a una prima fase di identificazione degli errori. In effetti uno Unit Testing framework non fa altro che controllare l’esattezza di una serie di unità. Se lo strumento in questione di muove nei database relazionali, può assumere come unità le righe delle tabelle, o insiemi di righe relazionate fra loro. Le asserzioni possono controllare la coerenza di questi dati. Per identificare le righe problematiche, è sufficiente che i messaggi relativi ai Fail contengano il valore della chiave primaria.

Esempio

Supponiamo di avere una tabella customer che contiene i dati dei clienti dell’azienda. Su ognuno di questi dati si potrebbe effettuare almeno un controllo banale (è chiaro che '012345' non è un numero di telefono), ma in questo articolo ci concentreremo sulla Partita IVA. Questa esiste in tutto il mondo sotto il nome anglosassone VAT, ma noi la valideremo secondo le regole specifiche italiane.

Validare una Partita IVA

La prima cosa da fare è creare una Stored Function che accetta in input una stringa e restituisce TRUE se si tratta di una partita IVA italiana valida, FALSE se non è valida, NULL se riceve in input NULL (per coerenza con le funzioni builtin di MySQL e MariaDB). E’ molto comodo, per produrre codice più leggibile, avere anche una funzione char_at() che restituisce un singolo carattere preso da una stringa. Servirà, probabilmente, ogni volta che vi troverete a creare una funzione per la validazione dei dati. Naturalmente, anche se dovremmo scrivere queste funzioni, in realtà non lo faremo: le trovate già pronte, qui di seguito :)

CREATE DATABASE IF NOT EXISTS `it_validation`;
USE `it_validation`;
    
DROP FUNCTION IF EXISTS `char_at`;
CREATE FUNCTION char_at(haystack TEXT, num TINYINT SIGNED)
    RETURNS CHAR(1)
    LANGUAGE SQL
    DETERMINISTIC
    NO SQL
    COMMENT 'Return num-th char from haystack. num starts from 1 and can be negative'
BEGIN
    IF haystack IS NULL OR num IS NULL THEN
        RETURN NULL;
    ELSE
        RETURN (SELECT SUBSTRING(haystack FROM num FOR 1));
    END IF;
END;
    
DROP FUNCTION IF EXISTS `is_vat`;
CREATE FUNCTION is_vat(vat CHAR(50))
    RETURNS BOOL
    LANGUAGE SQL
    DETERMINISTIC
    NO SQL
    COMMENT 'Return wether vat is valid italian VAT number, NULL if is NULL, else FALSE'
BEGIN
    -- current character
    DECLARE cur_char    TINYINT UNSIGNED DEFAULT NULL;
    -- input length
    DECLARE vat_len     TINYINT UNSIGNED DEFAULT 1;
	-- pointer to current char
    DECLARE i           TINYINT UNSIGNED DEFAULT 1;
    -- sub-sums of digits from vat
    DECLARE x           TINYINT UNSIGNED DEFAULT 0;  -- sum of odd nums
    DECLARE y           TINYINT UNSIGNED DEFAULT 0;  -- sum of even nums
    DECLARE z           TINYINT UNSIGNED DEFAULT 0;  -- sum of even nums > 4
    -- computed on sub-sums, must be 0
    DECLARE valid       TINYINT UNSIGNED DEFAULT 0;
    
    IF vat IS NULL THEN
        RETURN NULL;
    END IF;
    
    SET vat_len = CHAR_LENGTH(vat);
    
    -- too short?
    IF vat_len  11 THEN
        RETURN FALSE;
    END IF;
    
    -- get sub-sums
    WHILE i  4 THEN
                SET z = z + 1;
            END IF;
        ELSE
            SET x = x + cur_char;
        END IF;
        
        -- increment index
        SET i = i + 1;
    END WHILE;
    
    -- check digit
    SET valid = 10 - ((x + (y * 2) + z) MOD 10) MOD 10;
    -- if check digit is 10, treat as 0
    RETURN (IF(valid = 10, 0, valid) = CAST(`it_validation`.char_at(vat, 11) AS UNSIGNED INTEGER));
END;

Testiamo la validazione…

Il codice va sempre testato, e il codice che trovate su questo blog non fa eccezione. Siccome stiamo parlando di STK/Unit, appare naturale usarlo anche per testare chat_at() e is_vat():

CREATE DATABASE IF NOT EXISTS `test_it_validation`;
USE `test_it_validation`;
    
DROP PROCEDURE IF EXISTS `test_char_at`;
CREATE PROCEDURE test_char_at()
    LANGUAGE SQL
    COMMENT 'Test char_at()'
BEGIN
    DECLARE haystack CHAR(50) DEFAULT 'miles davis';
    CALL `stk_unit`.assert_null(`it_validation`.char_at(NULL, 1),
        'If any arg is NULL, result should be NULL');
    CALL `stk_unit`.assert_null(`it_validation`.char_at(haystack, NULL),
        'If any arg is NULL, result should be NULL');
    CALL `stk_unit`.assert_equals(`it_validation`.char_at(haystack,  0), '',
        'If num = 0, result should be empty string');
    CALL `stk_unit`.assert_equals(`it_validation`.char_at(haystack,  3), 'l',
        'Incorrect char');
    CALL `stk_unit`.assert_equals(`it_validation`.char_at(haystack, -3), 'v',
        'Incorrect char');
END;
    
DROP PROCEDURE IF EXISTS `test_is_vat`;
CREATE PROCEDURE test_is_vat()
    LANGUAGE SQL
BEGIN
    CALL `stk_unit`.assert_null(`it_validation`.is_vat(NULL),
        'If arg is NULL, should return NULL');
    CALL `stk_unit`.assert_false(`it_validation`.is_vat('123'),
        'Code is too short');
    CALL `stk_unit`.assert_false(`it_validation`.is_vat('012345678912'),
        'Code is too long');
    CALL `stk_unit`.assert_false(`it_validation`.is_vat('12345678901'),
        'This code is not valid');
    CALL `stk_unit`.assert_true(`it_validation`.is_vat('05985341006'),
        'This code is valid');
    CALL `stk_unit`.assert_true(`it_validation`.is_vat('02826010163'),
        'This code is valid');
    CALL `stk_unit`.assert_true(`it_validation`.is_vat('01785490408'),
        'This code is valid');
    CALL `stk_unit`.assert_true(`it_validation`.is_vat('07643520567'),
        'This code is valid');
END;

Per eseguire il Test Case e avere la conferma che quelle due funzioni sono buone, ci basta una semplice istruzione:

CALL `stk_unit`.`tc`('test_it_validation');

Pulire i dati!

Dopo tutti questi preamboli, siamo arrivati al nocciolo della questione: la procedura di Data Cleansing. Il concetto è molto semplice: creiamo un Test Case, e al suo interno un Base Test che esegue un ciclo su una tabella; per ogni riga, il test asserisce che la partita IVA è valida:

CREATE DATABASE IF NOT EXISTS `test_gest`;
USE `test_gest`;
    
DROP PROCEDURE IF EXISTS `test_customer_piva`;
CREATE PROCEDURE test_customer_piva()
    LANGUAGE SQL
    COMMENT 'Test an_anagrafiche.piva'
BEGIN
    -- end of results
    DECLARE `eof` BOOLEAN DEFAULT FALSE;
    -- invalid value
    DECLARE `wrong_piva` TEXT DEFAULT NULL;
    -- id of invalid row
    DECLARE `wrong_id` MEDIUMINT UNSIGNED DEFAULT NULL;
    
    -- read table. but if piva is not mandatory,
    -- we want to exlude empty strings and/or NULLs
    DECLARE `crs_piva` CURSOR FOR
        SELECT `id`, `piva`
            FROM `gest`.`customer`
            WHERE `piva` IS NOT NULL AND `piva` > '';
    
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND
        SET `eof` = TRUE;
    
    -- loop on found rows
    OPEN `crs_piva`;
    `lp_piva`:
    LOOP
        FETCH NEXT FROM `crs_piva`
            INTO `wrong_id`, `wrong_piva`;
        
        IF `eof` IS TRUE THEN
            LEAVE `lp_piva`;
        END IF;
        
        -- trigger a pass if piva is valid, or a fail if its invalid.
        -- failure comment is the if of the wrong row, so we'll be
        -- able to identify it
        CALL `stk_unit`.`assert_true`(`it_validation`.`is_vat`(`wrong_piva`), `wrong_id`);
    END LOOP;
    CLOSE `crs_piva`;
END;

Per eseguire questo test:

CALL stk_unit.tc('test_gest');

E adesso?

Supponendo che abbiate creato la tabella customer contenente almeno i campi id e piva, l’abbiate popolata, e abbiate eseguito il test, ora vi trovate davanti un report di questo genere:

Test Case: test_gest
Id: 11
Completed: YES
198 passes, 2 fails, 0 exceptions
 
FAIL: `test_gest`.`test_customer_piva` [1] - 50
FAIL: `test_gest`.`test_customer_piva` [2] - 100

Questo report ci informa che 198 record hanno passato il test, e quindi hanno una partita IVA valida o non hanno affatto una partita IVA; ma 2 record hanno fallito, perché hanno una partita IVA non valida. Le righe problematiche sono quelle con id 50 e 100. La domanda è: ora che lo sappiamo, cosa facciamo?

Questa decisione va presa in base a diversi fattori. Comunque, nei casi più semplici, vogliamo semplicemente eliminare le righe contenenti dati non validi, o impostare a NULL i campi non validi, o stampare una lista di nominativi a cui sono associati dei dati non validi.

Naturalmente nel primo caso ci serve una DELETE, nel secondo caso una UPDATE, e nel terzo caso una SELECT; ma il principio è esattamente lo stesso. Per semplificare al massimo, consideriamo il caso in cui vogliamo eliminare i dati non validi. Come facciamo?

I risultati dei test si trovano nel database `stk_unit`, e precisamente nella tabella `test_results`. Supponendo che nessuno possa aver eseguito un test dopo di noi, per semplificare ulteriormente, possiamo servirci della vista `last_test_results_bad`, che contiene solo i test falliti durante l’esecuzione dell’ultimo Test Case:

SELECT `msg` FROM `stk_unit`.`last_test_results_bad`;

Nel mio caso, ottengo gli id 50 e 100.

Completiamo l’esempio:

DELETE FROM `customer` WHERE `id` IN
    (SELECT `msg` FROM `stk_unit`.`last_test_results_bad`);

Aggiungere una assert

Supponiamo di aver a che fare con diverse aziende, e di voler essere in grado di eseguire simili test con tutti i nostri clienti. Le aziende alle quali offriamo consulenza utilizzano gestionali diversi, perciò non è possibile riciclare il Test Case `test_gest`. Però possiamo generalizzare una parte del codice che abbiamo scritto, aggiungendo una procedura assert. La useremo al posto di stk_unit.assert_true(), e si chiamerà stk_unit.assert_is_it_vat(). Genererà un Pass ogni volta che le viene passata una partita IVA italiana valida, e un Fail ogni volta che le verrà passato un dato di altro genere.

Ecco come fare:

DROP PROCEDURE IF EXISTS `assert_is_it_vat`;
CREATE PROCEDURE assert_is_it_vat(IN val TEXT, IN msg CHAR(255))
    LANGUAGE SQL
    COMMENT 'Assert that val is valid italian VAT'
BEGIN
    IF `msg` IS NULL THEN
        SET `msg` = CONCAT('Param 1 expected to be a valid Italian VAT; Received: ', `stk_unit`.`ns_str`(`val`));
    END IF;
    CALL `stk_unit`.`assert`(`it_validation`.`is_vat`(val), `msg`);
END;

Il primo parametro è il valore che ci si aspetta sia una partita IVA valida. Il secondo è il messaggio di errore da registrare nel caso in cui non lo sia. Nell’esempio visto prima, il messaggio di errore era semplicemente l’id della riga contenente dati non validi.

Come prima cosa, impostiamo un messaggio di errore di default, da usare nel caso in cui venga passato NULL. Non è obbligatorio, ma è buona norma farlo nelle procedure assert – anche per coerenza con le assert predefinite di STK/Unit.
Dopo, non facciamo altro che chiamare una procedura assert() di livello più basso, che non dovrebbe mai essere chiamata direttamente dai Test Case. Le passiamo il risultato della validazione (ottenuto chiamando it_validation.is_vat()) e il messaggio di errore (che potrebbe essere quello di default).
Questo è sufficiente per avere a disposizione una nuova assert, che si può usare esattamente come prima abbiamo usato assert_true().

Si può inserire questa procedura in qualsiasi database, ma è meglio evitare di inserirla nel database stk_unit per evitare possibili conflitti nelle versioni future. Meglio invece creare un database che funge la libreria personalizzata di assert. Chi facesse questo, è fortemente incoraggiato a contattarmi per condividere le sue librerie!

Divertitevi!

Gli identificatori di MySQL e MariaDB

English version

Gli identificatori di MySQL/MariaDB sono i nomi dei database, delle tabelle, delle colonne, etc. Questi nomi possono essere virgolettati con i `backtick` (AKA backquote), e in questo caso possono contenere dei caratteri che normalmente non sono permessi in un identificatore (perfino il backtick stesso), oppure possono essere parole riservate. Sia il virgolettare i nomi, sia il non farlo, possono causare qualche piccolo problema.

Se si sceglie di non virgolettarli, bisogna evitare di utilizzare caratteri strani e parole riservate (il che comunque è una buona cosa). Ma quando si aggiorna MariaDB, nella nuova versione alcune parole che prima erano permesse potrebbero essere riservate.

Se si virgolettano i nomi, bisogna essere sicuri di farlo dappertutto. E’ anche questa una buona abitudine, ma se uno sviluppatore non usa i backtick (o dimentica di farlo), potrebbe vedere strani errori.

Se si utilizzano parole chiave non virgolettate come identificatori, in genere si riceve un errore 1064 (SQLSTATE 42000):

mysql> SELECT * FROM insert;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert' at line 1

L’identificatore non permesso generalmente è la prima parola della porzione di codice riportata nell’errore (vedi esempio sopra), ma può anche essere l’ultima parola prima dell’inizio di essa:

mysql> SELECT high_priority FROM t;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'FROM t' at line 1

In teoria, una query che contiene parole riservate non virgolettate usate come identificatori, potrebbe addirittura fare qualcosa di diverso da ciò che il programmatore pensa, e non restituire errori.

Altro avvertimento: anche se si virgolettano tutti gli identificatori, è meglio evitare di utilizzare lo stesso nome per una colonna e per una variabile in una Stored Routine. Anche se MariaDB comprende clausole come: WHERE `id` = `id`, queste possono confondere gli esseri umani che le leggono.

Qualche tool in SQL

Seguono due Stored Routine che potrebbero risultare utili. Per entrambe, riporto anche un Base Test per STK/Unit. Le Stored Routine devono trovarsi in un DB chiamato lib, mentre i BT vanno in un Test Case chiamato test_lib.

La prima funzione virgoletta un identificatore in modo che questo possa essere incluso in una stringa SQL (che potrà poi essere eseguita come Prepared Statement). E’ simile alla funzione builtin QUOTE(), che però serve con le stringhe da usare come valori.

CREATE FUNCTION `lib`.`quote_name`(`id` TEXT)
    RETURNS TEXT
    DETERMINISTIC
    NO SQL
    LANGUAGE SQL
    COMMENT 'Return a quoted identifier (if NULL, id is empty)'
BEGIN
    IF `id` IS NULL THEN
        RETURN '``';
    ELSE
        RETURN CONCAT('`', REPLACE(`id`, '`', '``'), '`');
    END IF;
END;
 
CREATE PROCEDURE `test_lib`.`test_quote_name`()
    LANGUAGE SQL
    COMMENT 'Test quote_name()'
BEGIN
    CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`('x'), '`x`', 'Incorrect quoting');
    CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`('x`y'), '`x``y`', 'Incorrect escape');
 
    CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`(''), '``', 'Empty name expected');
    CALL `stk_unit`.`assert_equals`(`lib`.`quote_name`(NULL), '``', 'For NULL value, empty name should be returned');
END;

Se si vuole validare un nome al volo, probabilmente il modo più rapido è:
SELECT 0 AS [nome];
La seguente procedura però può essere utilizzata all’interno degli Stored Program. E’ possibile usarla per sapere se un certo nome è valido o meno. Esegue la query riportata sopra (come Prepared Statement, perché deve comporla dinamicamente) e controlla se si verifica un errore.

CREATE PROCEDURE `lib`.`is_valid_name`(IN `id` TEXT, OUT `ret` BOOL)
    NO SQL
    LANGUAGE SQL
    COMMENT 'set `ret` to TRUE if id is valid name, else FALSE'
BEGIN
    -- error in query
    DECLARE EXIT HANDLER
        FOR 1064
    BEGIN
        SET `ret` = FALSE;
    END;
 
    SET @sql_query = CONCAT('DO (SELECT 0 AS ', `id`, ');');
    PREPARE stmt FROM @sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
 
    SET `ret` = TRUE;
END;
 
CREATE PROCEDURE `test_lib`.`test_is_valid_name`()
	LANGUAGE SQL
	COMMENT 'Test quote_name()'
BEGIN
    CALL `lib`.`is_valid_name`('valid_name', @r);
    CALL `stk_unit`.`assert_true`(@r, 'Specified name is valid');
 
    CALL`lib`.`is_valid_name`('SELECT', @r);
    CALL `stk_unit`.`assert_false`(@r, 'SELECT is a keyword');
    CALL `lib`.`is_valid_name`('a a', @r);
    CALL `stk_unit`.`assert_false`(@r, 'Valid names cant contain spaces');
    CALL `lib`.`is_valid_name`('', @r);
    CALL `stk_unit`.`assert_false`(@r, 'Empty name is not valid');
END;