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!

Rilasciato STK/Unit 1.0 Release Candidate 3

2013-04-05

STK/Unit 1.0 Release Candidate 3 è stato rilasciato!

STK/Unit 1.0 Release Candidate 3 dovrebbe essere una versione stabile. L’autore originale di STK/Unit utilizza questo strumento dal 2010, ma lo ha modificato pesantemente quando ha deciso di renderlo pubblico.Tutti i vecchi test che ha creato funzionano ancora.

Dalla prima versione pubblica, rc1, abbiamo ricevuto solo feedback privati. Questi hanno un grande valore, perché ci hanno aiutato a identificare bug e difetti. Tuttavia speriamo vivamente di ricevere dei feedback pubblici, prima di dichiarare STK/Unit production-ready.

Questa RC non è stata pianificata. Tuttiavia, siamo riusciti ad allungare la lista delle versioni supportate di MySQL e MariaDB pur mantenendo un delta molto stretto rispetto alla precedente release. Questo dovrebbe aiutarci a ricevere qualche feedback dagli utenti, e al tempo stesso crediamo di non aver introdotto alcun bug.

Ora, le piattaforme supportate sono:

  • MariaDB 10.0, 5.5
  • MariaDB 5.1, 5.2, 5.3 (con alcuni problemi documentati: alcune funzioni assert riguardanti il DDL potrebbero generare eccezioni misteriose)
  • MySQL 5.6, 5.5
  • MySQL 5.1 (stessi problemi di MariaDB 5.1)

Ecco l’elenco delle modifiche presenti nella versione 1.0 Release Candidate 3:

  • Corretto bug #1162515 (test_stk_unit abortisce su MySQL 5.5)
  • Non è più necessario modificare i file SQL prima dell’installazione su Oracle
  • I BT commentati in test_stk_unit_assertions contenevano degli errori
  • Pulizie minori nel codice

La documentazione e i download di STK/Unit e degli altri strumenti STK che verranno in futuro, sono disponibili qui:
http://stk.wikidot.com/

La Mailing List pubblica si trova qui:
https://launchpad.net/~stk-discuss

Il team di STK vi incoraggia a provare STK/Unit nei vostri database, segnalare qualsiasi bug doveste trovare, chiedere aiuto nella mailing list se necessario, e farci avere i vostri commenti. Il vostro feedback ha un grande valore per noi!

The STK Team

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;

Rilasciato STK/Unit 1.0 Release Candidate 1

English announcement

E’ uscito STK/Unit 1.0 Release Candidate 1!

STK sta per SQL ToolKit. Si tratta di una famiglia di progetti per MariaDB, MySQL e Percona Server. STK/Unit è il primo progetto STK a essere rilasciato pubblicamente; altri arriveranno nel prossimo futuro. L’obiettivo di STK nel lungo termine è rendere la programmazione SQL molto più semplice ed affidabile su MariaDB e le sue sorelle.

STK/Unit è una piattaforma di Unit Test per MariaDB, interamente scritta in SQL e ispirata da SimpleTest e JUnit. I Test Case e le Test Suite scritti dall’utente possono creare un ambiente di test e verificare che tutte le operazioni producano i risultati previsti. I risultati possono essere estratti come stringa leggibile, in formato HTML, o esaminati nelle tabelle nelle quali vengono scritti. Tanto gli sviluppatori quanto gli amministratori di database possono trarre beneficio da STK/Unit.

Gli errori che si verificano nelle applicazioni possono avere origine nei database. STK/Unit è progettato principalmente per testare le strutture SQL attive: Stored Routine, Trigger, vincoli di integrità e Viste. Ma anche le tabelle devono servirsi dei tipi di dati, dimensioni delle colonne e set di caratteri adatti a contenere i dati che arrivano dal Mondo Reale. Inoltre gli aggiornamenti del DBMS, i nuovi plugin e perfino i cambiamenti nella configurazione possono portare anomalie nelle complesse e delicate logiche dei database relazionali. Ma un buon set di test può far emergere i problemi appena questi si verificano!

STK/Unit è ancora in sviluppo e sta espandendo il parco di piattaforme supportate; attualmente, funziona sulle seguenti:
* MariaDB 5.5 e 10.0 – funziona bene
* MariaDB 5.3, 5.2, 5.1 – con problemi minori, documentati
* MySQL 5.1 – utilizzando MyISAM invece di Aria, con problemi minori (non documentati al momento)

La Documentazione e i Download di STK/Unit e degli altri strumenti STK che verranno sono reperibili qui:
http://stk.wikidot.com/

La Mailing List pubblica si trova qui:
https://launchpad.net/~stk-discuss

Il team di STK incoraggia tutti a provare STK/Unit nei propri database, segnalare qualsiasi bug venga riscontrato, chiedere aiuto in lista se necessario, e farci avere i vostri commenti. I vostri feedback hanno un grande valore per noi!

Il team di STK

Impostare un SQL_MODE restrittivo

English version

Sviluppare Stored Routine, Trigger o Evente per MariaDB e MySQL non è facile, perché il linguaggio manca di flessibilità e ha molte limitazioni. In più, il server tenta di facilitarci la vita nascondendoci gli errori e permettendo cattive pratiche. Secondo la mia modesta opinione, se si devono scrivere più di 3 righe, le cattive pratiche rendono le cose esponenzialmente più ardue. Quando il server si lamenta perché hai fatto qualcosa di sbagliato, il problema non è nel server stesso: è nel tuo codice! E rimane lì anche se dici al server di tacere. L’unica soluzione ragionevole è trovare il problema e modificare il codice. Perciò suggerisco di far sì che il server sia petulante. Per fare questo, occorre modificare il valore della variabile SQL_MODE.

L’SQL_MODE può modificare SQL syntax, rendendola più compatibile con altri DBMS. Personalmente non amo usare sintassi che non siano standard de facto, ma credo sia una pratica innocua.

Alcuni flag però dovrebbero sempre essere impostati, perché fanno sì che il server non sia eccessivamente flessibile.

ERROR_FOR_DIVISION_BY_ZERO
I numeri non possono essere divisi per zero, perché il risultato non è definito. Se si tenta di eseguire tale operazione, la maggioranza dei linguaggi risponde con un errore. Lo fa anche MariaDB, se questo flag è impostato. Altrimenti, restituisce NULL. Siccome il valore NULL corretto, da un punto di vista logico, per rappresentare un valore non definito, questo comportamento potrebbe andare bene. Ma di solito non è una cosa desiderata – se si divide per zero per errore, è bene esserne informati e correggere il bug!

NO_AUTO_CREATE_USER
Quando si utilizza GRANT per assegnare dei permessi a un utente che non esiste, esso viene creato automaticamente, a meno che questo flag sia impostato. Dal punto di vista della sicurezza, non è una buona cosa che si possa creare un utente per errore.

NO_AUTO_VALUE_ON_ZERO
Questo flag permette di inserire il valore 0 in un campo AUTO_INCREMENT. Poiché 0 è un valore lecito, in genere questo flag dovrebbe essere impostato. Ma non me ne curo molto, perché modificare una colonna AUTO_INCREMENT è secondo me una cattiva pratica.

NO_ENGINE_SUBSTITUTION
Quando si crea una tabella, è importante scegliere lo Storage Engine più appropriato. Se questo flag non è impostato, e si specifica uno Storage Engine che non esiste, il server utilizzerà quello di default.

NO_ZERO_DATE
Se questo flag non è impostato, MariaDB accetta un valore data speciale: ‘0000-00-00’. Se si desidera utilizzarlo, non occorre impostare questo flag; ma non è una situazione comune. In caso contrario è soltanto una fonte di problemi, perché le date non valide verranno convertite in date-zero senza avviso. E questo non è quasi mai ciò che si vuole che accada.

NO_ZERO_IN_DATE
Questo flag impedisce che le date non-zero possano contenere delle parti-zero, come ‘0000-05-20’ o ‘1994-00-01′. E’ un po’ meno usuale creare una data di questo genere per errore, ma può accadere se si compone dinamicamente una data come stringa.

ONLY_FULL_GROUP_BY
Quando si specifica una colonna non aggregata e non raggruppata in una clausola SELECT, il risultato non è definito. Per questo motivo dovrebbe essere generato un errore, ma ciò accade solo se questo flag è impostato.

STRICT_ALL_TABLES
Questo flag è molto importante perché, se non è impostato, non viene generato alcun errore quando si tenta di inserire un valore fuori dall’intervallo consentivo (o una stringa troppo lunga). Funziona non soltanto per le tabelle, ma anche per le variabili e le funzioni. Per esempio, se una funzione dichiarata come TINYINT SIGNED tenta di restituire 200, non viene generato alcun errore (solo un warning), e il valore restituito è 127. Un problema di questo genere può essere difficile da debuggare.

STRICT_TRANS_TABLES
Vedi STRICT_ALL_TABLES, ma questo flag funziona solo per le tabelle transazionali.

Per questi motivi, l’SQL_MODE che ho deciso di utilizzare per scrivere le Stored Routine dei progetti STK è il seguente:

SET @@session.SQL_MODE = 'ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,STRICT_TRANS_TABLES';

Qui troverete una lista di tutti i flag di SQL_MODE in MySQL.

Un’altra ottima pratica è attivare lo Strict Mode di InnoDB, ma questo esula dagli scopi di questo post.

A presto!

Le entità di XML e HTML

English version

Come tutti dovrebbero sapere, alcuni caratteri devono essere sostituiti con delle entità, se si desidera usarli come testo in un documento XML o (X)HTML. Ad esempio, siccome i tag sono circondati dai caratteri < e >, non si può scriverli così come sono e aspettarsi di vederli apparire nella finestra del browser insieme al resto del testo.

Per inserire i caratteri che hanno un significato speciale, di solito si usano le entità. XML ha cinque entità predefinite, che sono presenti anche in HTML:

& &amp;
< &lt;
> &gt;
” &quot;
‘ &apos;

Per ottenere un risultato corretto, i caratteri & devono essere sostituiti prima degli altri.

Questi caratteri sono gli unici che potrebbero confondere il client e invalidare un documento. Nei parametri si può perfino lasciare < e >. Negli altri contesti, è possibile usare ” e ‘.

Si può tranquillamente evitare di sostituire gli altri caratteri “strani” utilizzando il set di caratteri UTF-8.

Nei documenti XML, è anche possibile evitare di utilizzare le entità predefinite, purché i caratteri corrispondenti appaiano solo nelle sezioni CDATA. La sintassi di CDATA è:

<![CDATA[ bla bla bla ]]>

Le sezioni CDATA non possono contenere la sequenza di caratteri ]]> e non vi è modo di aggirare questa restrizione.

Divertitevi!

MariaDB: le funzioni COALESCE, FIELD ed ELT

MariaDB e MySQL hanno una varietà di funzioni utilizzabili all’interno delle istruzioni SQL, che però non sono molto conosciute – né largamente usate. In questo post intendo illustrarne tre, che in qualche modo si complementano tra loro: COALESCE(), FIELD() ed ELT(). Per ognuna farò un esempio della loro utilità (senza escludere che esistano esempi più comuni e interessanti che io non ho ancora trovato) e spiegherò perché in MariaDB sono più interessanti che in MySQL.

Nota: come al solito, in questo post farò riferimento a MariaDB. Ciò non toglie che quanto scrivo, in buona parte, è vero anche su MySQL.

COALESCE

Fra le tre funzioni, questa è la più comune. Accetta un numero di argomenti variabile e restituisce il primo che è diverso da NULL.
Si supponga di voler leggere (SELECT) il modo migliore per contattare una serie di clienti, utenti, dipendenti o fornitori (o amici, perché no?). Per ognuna di queste persone abbiamo una serie di informazioni per i contatti, ma potremmo non averle tutte. Ad esempio potrebbe macare un numero di cellulare, o la mail. Allora COALESCE() ci viene in aiuto: basta invocarla passandole la lista dei campi che ci interessano, nell’ordine in cui li preferiamo:

SELECT COALESCE(cell, tel_lavoro, tel_casa, email, skype) AS contatto
    FROM cliente;

Per ogni cliente ci verrà restituito il cellulare, o se questo è assente il numero dell’ufficio, o se questo manca il numero di casa, etc.

Prima di passare alla prossima fuzione, vediamo un altro caso interessante: quello in cui, per ogni valore, abbiamo più valori di default. In che senso? Bene, partiamo dal caso base, cioè quello in cui abbiamo un solo valore di default. In questo caso ci basterà usare IFNULL():

SELECT IFNULL(url, CONCAT('http://.../', titolo)) AS url
    FROM pagina;

Ma forse abbiamo più valori di default: se il primo è NULL ripieghiamo sul secondo, e così via. Allora, invece di annidare una serie di IFNULL(), useremo COALESCE():

SELECT 
        COALESCE(p.template, s.template, b.template) AS template
    FROM post AS p
    LEFT JOIN sezione AS s
        ON p.id_sezione = s.sezione
    INNER JOIN blog AS b
        ON p.id_blog = b_id;

L’esempio è molto semplice, ma spieghiamolo: abbiamo un post che fa parte di un blog e, opzionalmente, di una sezione. A ogni post potrebbe essere assegnato un template specifico; se così non è si usa il template di default della sezione; se non esiste, si usa il template di default dell’intero blog.

Se il valore cercato non esiste, COALESCE() restituisce NULL.

Nota: è possibile usare COALESCE() solo se si impostano a NULL le colonne in cui il valore è assente. Chi disapprova questa tecnica e preferisce inserire stringhe vuote dovrà fare a meno di COALESCE(). In teoria, potrebbe anche scrivere una UDF simile a COALESCE() che tratti le stringhe vuote come NULL. Non una Stored Function, perché queste non accettano argomenti variabili.

FIELD

Dopo aver trattato COALESCE(), è naturale presentare FIELD() come suo complemento: FIELD() ci dice in quale caso è stato trovato il valore passato come primo argomento. Un esempio:

SELECT 
        FIELD('pinco@pallino.info', cell, tel_lavoro, tel_casa, email, skype) AS tipo_contatto
    FROM cliente;

A volte può essere utile usare COALESCE() e FIELD() nella stessa query, il che rende ovvia il loro ruolo quasi complementare:

SELECT
        COALESCE(cell, tel_lavoro, tel_casa, email, skype) AS contatto,
        FIELD(COALESCE(cell, tel_lavoro, tel_casa, email, skype), cell, tel_lavoro) AS tipo_contatto
    FROM cliente;

Purtroppo qui il codice è ripetitivo, ma non c’è modo di evitarlo in una caso d’uso comune. Come abbiamo visto però, la ripetitività sussiste solo se le due funzioni vengono usate all’interno della stessa query.

Se il primo argomento è NULL (ossia non c’è un valore), FIELD() restituisce 0.

ELT

Anche ELT() può essere considerato un complemento di FIELD(), infatti dato un indice passato come primo argomento, restetuisce l’argomento corrispondente. Prima di vedere un esempio realistico, vediamone uno completamente inutile che renda più chiara questa spiegazione:

SELECT ELT(2, 'a', 'b', 'c'); -- restituisce 'b'

Ora, supponiamo che una query contenente COALESCE() abbia estratto un certo valore corrispondente a un certo contatto. FIELD() ci dice qual è l’indice del contatto estratto all’interno della nostra lista di campi. ELT() ci restituisce l’informazione complementare: dato l’indice, ci dice qual è il campo corrispondente.

Se il primo argomento è un numero reale, viene arrotondato. Se il primo argomento (o il risultato dell’arrotondamento) non è un numero naturale o è maggiore del numero degli argomenti passati escluso il primo, restituisce NULL.

MariaDB rocks!

Va bene, ammettiamolo: se MariaDB spacca non è certo perché rende più utili queste funzioni… ma il titolo MariaDB rocks mi piace tanto!

Tornando alle cose serie, la funzionalità di MariaDB che ci interessa in questo post sono le Colonne Virtuali. Per chi non le conoscesse, consiglio la lettura della Knowledge Base. Per i molto pigri, dico solo che sono campi che contengono un valore calcolato sulla base delle altre colonne – valore che può essere ricalcolato ogni volta che una SELECT lo richiede, oppure scritto su disco.

Con le Colonne Virtuali possiamo aggiungere, alla nostra tabella cliente, una colonna contatto_preferito e una colonna testuale tipo_contatto_preferito:

CREATE TABLE cliente
(
    ...
    contatto_preferito CHAR(50) AS (COALESCE(cell, tel_lavoro, tel_casa, email, skype)) PERSISTENT,
    tipo_contatto_preferito CHAR(20,ELT(FIELD(COALESCE(cell, tel_lavoro, tel_casa, email, skype), cell, tel_lavoro, tel_casa, email, skype), 'cell', 'tel_lavoro', 'tel_casa', 'email', 'skype'))
);

Non c’è dubbio che la definizione della colonna tipo_contatto_preferito sia orribile. Ma questa tecnica risolve almeno due grossi problemi:

  • Permette di usare FIELD() e ELT() senza rendere illeggibili le query che ne fanno uso. Questa illeggibilità è un ottimo motivo per evitare l’uso di queste funzioni in MySQL. Come si vede, nell’esempio riportato, l’unica cosa poco leggibile è la definizione delle Colonne Virtuali. Ma nella vita di tutti i giorni non avremo alcun bisogno di leggerla, e un buon commento risolve in parte il problema.
  • Senza stare a fare altri esempi (che peraltro andrebbero tratti da applicazioni reali), dovrebbe essere chiaro che le tre funzioni in questione non vanno molto d’accordo con gli indici. Anche se COALESCE() legge diverse colonne, generalmente non è il caso di creare un indice su di esse. Una Colonna Virtuale di tipo PERSISTENT però può essere indicizzata; e anche se scegliamo di non farlo, si tratta di una colonna sola. Anziché leggere un insieme di colonne, possiamo leggerne una sola, forse indicizzata, che viene scritta automaticamente ogni volta che inseriamo o modifichiamo un record.

Gli altri linguaggi

Implementare queste tre funzioni in un qualsiasi linguaggio di programmazione è molto semplice. Solo per i pigri, riporto le implementazioni PHP che uso io:

function coalesce()
{
    $args     = func_get_args();
    $numArgs  = count($args);
    for ($i = 0; $i < $numArgs; $i++) {
        if ($args[$i] !== NULL) {
            return $args[$i];
        }
    }
    return NULL;
}
function elt($index)
{
    if ($index  func_num_args() - 1) {
        return NULL;
    }
    return func_get_arg($index);
}
function field($needle)
{
    if ($needle === NULL) {
        return NULL;
    }
    $numArgs = func_num_args();
    $args = func_get_args();
    for ($i = 1; $i < $numArgs; $i++) {
        if ($needle === $args[$i]) {
            return $i;
        }
    }
    return 0;
}

Teniamo però presente che se si usa una singola Colonna Virtuale di MariaDB (o se si usano queste funzioni in una query MySQL) si riduce il traffico di rete.

A presto!

La Prova del Nove

«La prova del nove» fa parte del nostro linguaggio parlato e scritto. In generale, indica una verifica che ci garantisce la veridicità o meno di un certo fatto. Ma quanti sanno da dove deriva questa locuzione?

In questo post intendo spiegare la prova del nove e dimostrarla con un semplice script PHP.

Il modulo

In aritmetica, esiste un’operazione chiamata modulo. Non è altro che il resto di una divisione fra due numeri interi. Ora, il modulo 9, cioè il resto della divisione per 9, ha una particolarità che lo rende facile da calcolare anche a mente, per numeri molto alti: equivale alla somma delle sue cifre. Se questa somma dà un risultato di più di una cifra, il procedimento va reiterato fino a ottenere un risultato di una cifra.

Naturalmente, se il risultato è 0 o 9, significa che la divisione non dà resto.

EDIT: in questa somma si può ignorare i 9, perché naturalmente non modificano il risultato. Ad esempio, la somma di 991 sarà 1.
Ringrazio Amedeo (vedi commenti) perché avevo dimenticato questo dettaglio. Nel suo commento vedrete anche come usare la prova del nove con le addizioni.

Esempio semplice:
52 (mod 9) = 7

Esempio reiterativo:
666 (mod 9) = 18 (mod 9) = 9 = 0

La prova

Possiamo sfruttare il modulo 9 per verificare il risultato di due operazioni. Infatti, se è vero che:
a * b = c
deve necessariamente essere vero che:
(a (mod 9) * b (mod 9)) (mod 9) = c (mod 9)
In parole: il mod9 del prodotto del mod9 dei fattori è uguale al mod9 del prodotto dei fattori.

Verifichiamo ad esempio che 55 * 6 = 330:
55 (mod 9) = 1
6 (mod 9) = 6
1 * 6 = 6
330 (mod 9) = 6

L’operazione è quindi corretta!

Ora verifichiamone una volutamente sbagliata: 4 * 40 = 170.
4 (mod 9) = 4
40 (mod 9) = 4
4 * 4 = 16
170 (mod 9) = 8

La prova del 9 ci conferma che l’operazione era errata!

Ma funziona davvero? (update)

Uno dei commenti afferma che la prova del Prova del Nove ha un 11% di probabilità di errore. Questa frase parrebbe riportata da Wikipedia, che afferma la stessa cosa. Non è del tutto falso, ma va spiegato.

Se io eseguo una moltiplicazione correttamente, la Prova del Nove me lo confermerà. Non è possibile che non risulti, state tranquilli. Il codice che vedrete nel prossimo paragrafo lo dimostra.

Naturalmente, non esiste un risultato che significa “hai sbagliato”. Se l’operazione è sbagliata, il risultato della Prova del Nove è semplicemente “imprevedibile”. Siccome il modulo 9 può risultare in un qualsiasi numero tra 0 e 8, e uno di loro è quello che mi aspetto, c’è 1 possibilità su 9 (11.1% periodico) che il risultato sia la cifra che mi aspetto. Il che potrebbe farmi credere che l’operazione sia giusta quando non lo è.

E’ probabile che questo succeda? No: 1 possibilità su 9 è scarsa. A questo bisogna aggiungere che, se eseguo la prova su un certo numero, è perché mi è risultato dalla moltiplicazione: questo aumenta le probabilità in nostro favore.

Implementazione in PHP

Siete bravi a fare i calcoli? Io no. E non era bravo neanche Einstein. Beh, a dire il vero pare che almeno nella teoria lui fosse un po’ più bravo di me, ma questi sono dettagli. Sta di fatto che vi sto scrivendo usando un computer, quindi perché non affidare a lui il compito ingrato di calcolare la prova del nove?

Scriviamo una piccola funzione PHP. Sull’implementazione non c’è molto da dire, se non che il modulo corrisponde all’operatore %.

Ho chiamato la funzione castNines perché prova del nove in inglese si dice casting out nines.

<?php
function castNines($f1, $f2)
{
  $mod1 = $f1 % 9;
  $mod2 = $f2 % 9;
  $p = $f1 * $f2;
  $mod3 = ($f1 * $f2) % 9;
  
  print '<p>';
  print (string)$f1 . ' * ' . (string)$f2 . '<br/>';
  print 'Result: ' . (string)($f1 * $f2) . '<br/>';
  print (string)$f1 . ' (mod 9) = ' . (string)$mod1 . '<br/>';
  print (string)$f2 . ' (mod 9) = ' . (string)$mod2 . '<br/>';
  print '(' . (string)$mod1 . ' * ' . (string)$mod2 . ') (mod 9) = '
    . (string)$mod3 . '<br/>';
  if ($mod3 === ($p % 9)) {
    print '<strong>OK!</strong>';
  } else {
    print 'Oooops! You found an exception to mathematical rules... or just a bug! :)';
  }
  print '</p>';
}
?>

Ora provatela pure con tutti i numeri che volete:

<?php
castNines(12, 10);
castNines(10, 1);
castNines(20, 3);
?>

A presto!

Come scegliere una Password Sicura nel Mondo Reale

English version

Si trovano un sacco di consigli su come scegliere una password sicura, in giro per la rete. Ma per lo più non mi sembrano delle grandi idee.

Per prima cosa, definiamo i nostri obiettivi. Noi vogliamo:

  • Password sicure;
  • Password che siamo in grado di ricordare.

Il consiglio più comune che si trova è: «Usate sempre lettere maiuscole E minuscole E numeri E underscore». Non è un cattivo consiglio, ma migliora di poco le nostre password. Se queste non sono sicure, lo rimarranno anche se aggiungiamo qualche numero. Esempio: Ab12 è troppo corta. mipiacemarypoppins è molto più sicura. iblacksabbathsonoimiglioridelmondo è ancora meglio. E penso proprio che non la dimenticherete.

A questo punto possiamo applicare l’altro consiglio. iBlackSabbath_sonoimiglioridelmondo01 è ancora più sicura. Ed è impossibile da indovinare per un essere umano.

Ma abbiamo un problema: le persone difficilmente riescono a ricordare più di una password di questo tipo. E l’altro consiglio che troviamo un po’ dappertutto è: «Usate una password separata per ogni servizio al quale vi iscrivete». Che è un suggerimento importante… davvero! Molti piccoli siti collezionano le password degli utenti, e molte app sui social network tentano di indovinarle per poi accedere ai vari servizi. Ma la memoria umana non è perfetta. E le password hanno senso solo se si riesce a ricordarle. Come fare?

Teniamoci solo una password di base. E modifichiamola in base al nome del servizio che stiamo usando. Devi decidere da solo la regola da usare. Non puoi usare la mia – non sarebbe sicuro per nessuno dei due. Ma è facile. Ecco alcune possibili regole:

  • Aggiungere le prime due lettere all’inizio della password, e l’ultima alla fine.
  • Aggiungere la prima lettera all’inizio della password, e le ultime due alla fine.
  • Aggiungere l’ultima lettera all’inizio della password, e l’ultima alla fine.

Per esempio, ecco come si applica la prima regola:

  • Password base: iBlackSabbath_sonoimiglioridelmondo01
  • Nome servizio: Facebook
  • Password specifica: faiBlackSabbath_sonoimiglioridelmondo01k

Troppo difficile? Va bene, togliete ’01’. E anche l’underscore, se volete. Tutti i geek che tenteranno di convincervi di quanto poco la vostra password sia sicura, ne avranno una peggiore della vostra.

AGGIORNAMENTO

Questa striscia dell’inimitabile XKCD riassume bene la questione!

A presto!

MariaDB Mode per CodeMirror

Avevo bisogno di un editor SQL scritto in JavaScript, da integrare in un tool fatto in casa che utilizzo per modificare i miei database. Ho trovato CodeMirror. Funziona molto bene ed è usato da un certo numero di progetti importanti. Il codice è rilasciato con una licenza MIT (personalmente preferirei la AGPL3, ma non importa).

CodeMirror supporta parecchi linguaggi di programmazione, e gli script relativi ai linguaggi si chiamano Mode. Ho provato la modalità MySQL, ma… beh, detto sinceramente, non mi piace. Forse sono stato sfortunato, ma le prime parole chiave che ho scritto (DELIMITER e TRUNCATE) non sono state evidenziate. Non vengono evidenziati nemmeno i numeri decimali.

Perciò ho scritto un nuovo Mode e l’ho messo su GitHub: MariaDB: https://github.com/santec/mariadb-mode-for-CodeMirror.

Prima di tutto, ho rielaborato un po’ il codice del MySQL Mode. Ora è in Strict Mode e utilizza i tipi in modo più statico. Poi ho sistemato alcuni bug minori: i commenti ‘–‘ non richiedevano uno spazio e i `backtick` potevano essere inseriti in un identificatore utilizzando ‘\’ come escape.

Poi ho aggiunto qualcosa:

  • un sacco di parole chiave di MySQL e MariaDB;
  • numeri con la virgola (ed eventualmente gli esponenti);
  • variabili (tutte le sintassi ammesse);
  • binari e hex (tutte le sintassi);
  • tutti i tipi di commenti;
  • ‘?’ (per i prepared statement).

Le limitazioni? Beh, non ha un’indentazione automatica molto intelligente, l’autocompletamento e diverse altre caratteristiche che ci starebbero bene. Però l’ho scritto soltanto per integrarlo in un tool che uso solo io. Se c’è interesse da parte di altri potrei migliorarlo, ma non credo che ci lavorerò su ancora se non sono sicuro che abbia senso.

Se usi MariaDB Mode e hai qualche commento/richiesta, fammelo sapere. Se lo migliori, per favore perdi un po’ di tempo per fare una Pull Request su GitHub.

A presto!