MariaDB: lo Storage Engine Sequence

English version

MariaDB 10.0.3 introduce un nuovo Storage Engine: Sequence. Non è in MySQL né nelle versioni più vecchie di MariaDBs. Sequence è un engine speciale, che non crea né legge alcuna tabella. Si limita a generare al volo una sequenza di interi che viene restituita alla query e poi smette di esistere. I limiti e l’incremento della sequenza dipendono dal nome della tabella.

Un rapidissimo tutorial

Per installare:

INSTALL PLUGIN sequence SONAME 'ha_sequence';

NOTA: Su MariaDB (al contrario di Oracle MySQL) non c’è bisogno di aggiungere ‘.dll’ a questa istruzione su Windows, perciò il comando è indipendente dalla piattaforma. Grazie a Vladislav Vaintroub per questa dritta!

Prima di creare una sequenza bisogna selezionare un database di default, con il comando USE. Poi si può eseguire questa query:

SELECT * FROM seq_1_to_100;

Per creare una sequenza con un incremento diverso da 1:

SELECT * FROM seq_1_to_100_step_2;

Una sequenza non può contenere numeri negativi, ma può essere discendente:

SELECT * FROM seq_10_to_1;

Notare che le sequenze discendenti generano sempre lo stesso insieme di numeri di quelle ascendenti, naturalmente in ordine inverso. Ciò implica che il numero più alto di una sequenza discendente può non essere quello specificato:

MariaDB [test]> SELECT * FROM seq_8_to_1_step_3;
+-----+
| seq |
+-----+
|   7 |
|   4 |
|   1 |
+-----+
3 rows in set (0.00 sec)

Cosa non è sequence

Lo Storage Engine Sequence non è una sequenza SEQUENCE come quelle supportate da PostgreSQL e da IBM, né un generatore di FirebirdSQL. Questo significa che una sequenza esiste solo durante l’esecuzione di una query. Non è possibile estrarre un valore da una sequenza in una query ed estrarre il valore successivo in un’altra query.

Inoltre sequence non può generate numeri negativi e non può ruotare quando raggiunge il limite massimo o minimo (come possono fare invece le sequenze di PostgreSQL).

Alcuni casi d’uso di sequence

Anche se le altre persone non ci credono, gli sviluppatori hanno moltissima fantasia. E’ assolutamente necessaria per risolvere problemi che non hanno una soluzione ovvia, o che non possono essere risolti con un algoritmo progettato da qualcun altro. Perciò sono sicuro che col tempo verranno fuori molti usi creativi dello Storage Engine sequence. Ma per ora, mi piacerebbe condividere alcune idee.

Quasi tutte le tecniche seguenti servono a riempire tabelle con dati generati automaticamente. E’ utile perché, se si può farlo utilizzando una sola query, si evitano molte comunicazioni di rete – che possono essere lente o aumentare il rischio che si verifichino errori.

Trovare i buchi in una colonna

A volte una colonna può contenere una sequenza di valori interi priva di buchi. Un esempio comune sono i campi utilizzati per ordinare gli elementi in una pagina web: mentre i buchi non dovrebbero causare problemi in fase di lettura, possono provocare problemi quando si tenta di modificare l’ordine. Nell’esempio seguente creiamo una tabella con molti buchi e poi visualizziamo una lista dei valori mancanti grazie a una sequenza:

CREATE TABLE t1 (c TINYINT UNSIGNED) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1), (2), (3), (5), (10);
SELECT s.seq FROM seq_1_to_10 s LEFT JOIN t1 t ON s.seq = t.c WHERE t.c IS NULL;

Costruire una combinazione di numeri

SELECT s1.seq, s2.seq FROM seq_1_to_3 s1 JOIN seq_1_to_3 s2 ORDER BY 1, 2;

Ma si può anche combinare numeri e caratteri (vedi sotto).

Trovare i multipli di 3, minori di 100

SELECT seq FROM seq_3_to_100_step_3;

Trovare i multipli di entrambi i fattori: 3 e 5

SELECT s1.seq FROM seq_5_to_100_step_5 s1 INNER JOIN seq_3_to_100_step_3 s2 ON s1.seq = s2.seq;

Ottimizzato: trovare i multipli di (2 o una delle sue potenze)

SELECT seq FROM seq_1_to_100 WHERE NOT seq & 1; -- ...of 2
SELECT seq FROM seq_1_to_100 WHERE NOT seq & 3; -- ...of 4
SELECT seq FROM seq_1_to_100 WHERE NOT seq & 7; -- ...of 8
SELECT seq FROM seq_1_to_100 WHERE NOT seq & 15; -- ...of 16
...

Costruire una sequenza di stringhe di 1 carattere

La query seguente include le lettere minuscole, le maiuscole e le cifre. Ma sono sicuro che chiunque può modificarla per escludere uno di questi gruppi, o includere altri gruppi (per esempio le vocali accentate).

-- Sequence of 1-char strings
SELECT CHAR(seq) AS ch
    FROM (
                -- lowercase
                (SELECT seq FROM seq_97_to_122 l)
            UNION
                -- uppercase
                (SELECT seq FROM seq_65_to_90 u)
            UNION
                -- digits
                (SELECT seq FROM seq_48_to_57 d)
        ) ch;

Costruire una sequenza di stringhe di 2 caratteri

Ouch… questa UNION è un casino. Ma qualcuno potrebbe volerla usare.

SELECT CONCAT(ch1.ch1, ch2.ch2) AS ch
    FROM (
        (SELECT CHAR(seq) AS ch1
            FROM (
                        -- lowercase
                        (SELECT seq FROM seq_97_to_122 l1)
                    UNION
                        -- uppercase
                        (SELECT seq FROM seq_65_to_90 u1)
                    UNION
                        -- digits
                        (SELECT seq FROM seq_48_to_57 d1)
                ) s1
        )
    ) ch1
    CROSS JOIN (
        (SELECT CHAR(seq) AS ch2
            FROM (
                        -- lowercase
                        (SELECT seq FROM seq_97_to_122 l2)
                    UNION
                        -- uppercase
                        (SELECT seq FROM seq_65_to_90 u2)
                    UNION
                        -- digits
                        (SELECT seq FROM seq_48_to_57 d2)
                ) s2
        )
    ) ch2
    ORDER BY ch1, ch2;

Scusate per la confusione con gli alias (in particolare ch1 e ch2). Sono rimasto a corto di fantasia prima di terminare la query.

Sequenza di date

Ecco, questo secondo me è interessante. Molte applicazioni che gestiscono le prenotazioni al momento dell'installazione popolano una tabella con i giorni e/o le ore che potrebbero essere prenotate. Questo esempio mostra come riempire, in modo facile, una tabella con tutti i giorni di gennaio:

SELECT DATE ('2014.01.01' + INTERVAL (s.seq - 1) DAY) AS d
    FROM (SELECT seq FROM seq_1_to_30) s;

Sequenza di ore, mezz'ore, etc

-- Hours in a day
SELECT CAST('00:00:00' AS TIME) + INTERVAL (s.seq - 1) HOUR AS t
    FROM (SELECT seq FROM seq_1_to_24) s;
-- Halfes of an hour in a day
SELECT CAST('00:00:00' AS TIME) + INTERVAL (30 * s.seq) MINUTE AS t
    FROM (SELECT seq FROM seq_1_to_48) s;

Giorni lavorativi

Gli ultimi due esempi, presi da soli, non sono granché utili. Di solito non si riempe una tabella con tutti i giorni che compongono un mese o un anno, né con tutte le ore che stanno in un giorno. Si scrivono invece soltanto le ore lavorative. Per trovare i giorni lavorativi si può usare la funzione DAYOFWEEK() per escludere i sabati e le domeniche. E' chiaro che questo non esclude automaticamente il natale o altre feste - ma credo che la soluzione migliore sia cancellarle dopo aver riempito la tabella.

SELECT DATE ('2014-01-01' + INTERVAL (s.seq - 1) DAY) AS d
    FROM (SELECT seq FROM seq_1_to_30) s
    -- exclude sunday (1) and saturday (7)
    WHERE DAYOFWEEK(DATE ('2014-01-01' + INTERVAL (s.seq - 1) DAY)) BETWEEN 2 AND 6;

Solo una nota sull'implementazione. Perché iniziare le date dal giorno 1 e poi fare un - 1, invece di cominciare direttamente dal giorno 0? Per due ragioni. Primo, nell'articolo Impostare un SQL_MODE restrittivo spiego che i flag NO_ZERO_DATE e NO_ZERO_IN_DATE dell'SQL_MODE non dovrebbero mai essere impostati. Secondo: '2014-01-00' + INTERVAL 1 DAY restituisce NULL, e non è un bug ma il comportamento corretto. Se non capisci il motivo (ma anche se lo capisci), per favore non usare quei flag. Quelle opzioni fanno finta di essere tue amiche, ma un giorno di queste prenderanno il tuo scalpo mentre dormi!

Ore lavorative

SELECT {dt '2013-01-01 01:00:00'} + INTERVAL (wd.d - 1) DAY + INTERVAL (dh.h - 1) HOUR AS wh
    FROM
        (
            -- working days in a month
            SELECT seq AS d FROM seq_1_to_30
        ) wd
    CROSS JOIN
        (
            -- daily working hours
            (SELECT seq AS h FROM seq_9_to_12)
            UNION
            (SELECT seq AS h FROM seq_14_to_17)
        ) dh
    ORDER BY 1;

Qui abbiamo una subquery che restituisce i giorni lavorativi, unita a una subqueryche restituisce le ore lavorative. Notare che le ore hanno un buco (la paura pranzo dei lavoratori), per questo la seconda subquery è una UNION tra due sequenze.

E... sì, il prodotto cartesiano è malvagio, ma in questo caso è esattamente ciò che ci serve. La parola chiave CROSS serve a chiarire che se modifichi la query e commetti un errore, potresti scrivere una SELECT che restituisce un gran numero di righe.

To-Do e suggerimenti

Più avanti scriverò un articolo che mostra una Stored Function che compone dinamicamente una query per popolare una tabella con valori DATETIME, in un dato intervallo di tempo, con una data granularità.

Pensi che una di queste query non sia ottimizzata nel modo migliore? Hai idee interessanti che riguardano lo Storage Engine sequence? Per favore, lascia un commento.

Divertitevi!

About these ads

Rispondi

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