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;

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...