Colonne Dinamiche

Le Colonne Dinamiche permettono di memorizzare un insieme di "colonne virtuali" per ogni riga della tabella.

E' possibile aggiungere o rimuovere tali colonne da un record in qualsiasi momento.

Questa funzionalità permette di risolvere problemi come un'applicazione per un negozio, dove si hanno molti articoli diversi - come t-shirt e telefoni - e si desidera che ognuno di questi elementi abbia un differente insieme di attributi. Normalmente è molto difficile ottenere questo con un database relazionale.

Le Colonne Dinamiche memorizzano le colonne aggiuntive in un campo BLOB e per gestirle si utilizzano alcune funzioni particolari. Questa caratteristica è stata implementata in MariaDB 5.3.

E' anche possibile manipolare le Colonne Dinamiche in SQL o nel client.

La struttura originale delle Colonne Dinamiche è stata realizzata nel MWL#34. Lì sono reperibili ulteriori informazioni su come i dati vengono memorizzati.

La prima implementazione delle Colonne Dinamiche punta a essere altamente efficiente per i programmi che compongono query SQL (probabilmente la maggior parte delle applicazioni per la vendita oggi utilizza questo metodo). Ecco perché si accede alle Colonne Dinamiche identificandole con dei numeri. Ti veda la sezione TO-DO più in basso per ulteriori informazioni sugli sviluppi futuri.

Funzioni SQL

Creare un oggetto per le Colonne Dinamiche

COLUMN_CREATE(numero_colonna, valore [as tipo], [numero_colonna, valore [as tipo]]...)

• 'tipo' non è obbligatorio ed è stato aggiunto principalmente per il testing, eccetto casi in cui date e orari potrebbero non essere convertite nel tipo desiderato.

Aggiungere o aggiornare le Colonne Dinamiche

COLUMN_ADD(colonna_dinamica, numero_colonna, valore [as tipo], [numero_colonna, valore [as tipo]]...)

• 'colonna_dinamica' deve essere il dato creato con COLUMN_CREATE() o una stringa vuota.
• Se 'colonna_dinamica' è NULL la funzione restituisce NULL.
• Se si aggiunge una colonna già esistente, il vecchio valore verrà sostituito.
• Se si imposta una colonna a NULL, questa verrà cancellata.
• Il tipo non è obbligatorio (viene rilevato automaticamente) e il parametro è stato aggiunto principalmente per i test.

Uso tipico:

UPDATE t1 SET col_dinamica=COLUMN_ADD(col_dinamica, 5, "prova") WHERE id=1;

In questo esempio, viene aggiunta o sostituita la col_dinamica numero '5', nella quale sarà memorizzato il valore "prova", nel record avente id=1

col_dinamica è scritto due volte perchè COLUMN_ADD() è una normale funzione, come ad esempio CONCAT(); si desidera rimpiazzare il vecchio valore contenuto in 'col_dinamica' con il nuovo valore restituito da COLUMN_ADD().

Eliminare una Colonna Dinamica

COLUMN_DELETE(colonna_dinamica, numero_colonna, numero_colonna...);

Leggere una Colonna Dinamica

COLUMN_GET(colonna_dinamica, numero_colonna as tipo)

Il tipo può essere specificato come si fa all'interno delle funzioni CAST e CONVERT:

• BINARY[(N)]
• CHAR[(N)]
• DATE
• DATETIME[(D)]
• DECIMAL[(M[,D])]
• DOUBLE[(M[,D])]
• INTEGER
• SIGNED [INTEGER]
• TIME[(D)]
• UNSIGNED [INTEGER]

Questo parametro è obbligatorio perchè SQL è un linguaggio fortemente tipizzato e le altre funzioni (così come il client) devono conoscere il tipo del dato per poterne leggere il valore.

Se la Colonna Dinamica ha un tipo differente da quello richiesto, il valore verrà convertito.

Si noti che se si utilizza CHAR o BINARY senza specificarne la lunghezza, MariaDB lo considera un BLOB con lunghezza massima 536870911 (il massimo consentivo in una Colonna Dinamica). Sebbene il server allochi automaticamente uno spazio di memoria con questo limite, i client potrebbero comportarsi diversamente, perciò è sempre meglio specificare la lunghezza.

Verificare l'esistenza di una Colonna Dinamica

COLUMN_EXISTS(colonna_dinamica, numero_colonna);

Questa funzione restituisce 1 se la colonna al numero corrispondente esiste, 0 se non esiste, NULL se la colonna è NULL.

COLUMN_LIST(colonna_dinamica);

Restituisce una stringa con tutte le colonne contenute in colonna_dinamica separate da una virgola (,).

Esempi

Per prima cosa creiamo la tabella. Le colonne dinamiche saranno memorizzate in strdin.

create table t1 (id int auto_increment primary key,
                 nome varchar(40),
                 tipo enum ("maglietta", "telefono", "computer"),
                 prezzo decimal(10,2),
                 strdin mediumblob);

Inseriamo alcuni elementi, che posseggono attributi comuni ma anche attributi specifici.

insert into t1 (nome, tipo, prezzo, strdin) values
("maglietta carina", "maglietta", 10.0, COLUMN_CREATE(1, "blu", 10, "XL")),
("nokia", "telefono", 649, COLUMN_CREATE(1, "nero", 2, "touchscreen")),
("htc Desire hd", "telefono", 579, COLUMN_CREATE(1, "nero", 3, "Android")),
("BM/Lenovo Thinkpad X60s", "computer", 419, COLUMN_CREATE(1, "nero", 3, "Linux"));

Controlliamo i dati:

select id, nome, tipo, prezzo, length(strdin) as len, column_list(strdin) as lista from t1;

+----+-------------------------+----------+--------+------+-------+
| id | nome                    | tipo     | prezzo | len  | lista |
+----+-------------------------+----------+--------+------+-------+
|  1 | maglietta divertente    | shirt    |  10.00 |   17 | 1,10  |
|  2 | nokia                   | phone    | 649.00 |   27 | 1,2   |
|  3 | htc Desire hd           | phone    | 579.00 |   23 | 1,3   |
|  4 | BM/Lenovo Thinkpad X60s | computer | 419.00 |   21 | 1,3   |
+----+-------------------------+----------+--------+------+-------+

SELECT nome FROM t1 WHERE COLUMN_GET(strdin, 1 as char(10)) = "nero";

+-------------------------+
| nome                    |
+-------------------------+
| nokia                   |
| htc Desire hd           |
| BM/Lenovo Thinkpad X60s |
+-------------------------+

SELECT name, COLUMN_GET(strdin, 1 as char(10)) FROM t1 WHERE COLUMN_EXISTS(strdin, 1);

+-------------------------+--------+
| nome                    | colore |
+-------------------------+--------+
| maglietta divertente    | blue   |
| nokia                   | black  |
| htc Desire hd           | black  |
| BM/Lenovo Thinkpad X60s | black  |
+-------------------------+--------+

Modifichiamo tutti gli elementi aventi colore="nero", trasformandolo in rosso

UPDATE t1 set strdin=COLUMN_ADD(strdin, 1, "rosso") where COLUMN_GET(strdin, 1 as char(10)) = "nero";
SELECT nome, COLUMN_GET(strdin, 1 as char(10)) FROM t1 WHERE COLUMN_EXISTS(strdin, 1);

+-------------------------+-----------------------------------+
| nome                    | COLUMN_GET(strdin, 1 as char(10)) |
+-------------------------+-----------------------------------+
| maglietta divertente    | blu                               |
| nokia                   | rosso                             |
| htc Desire hd           | rosso                             |
| BM/Lenovo Thinkpad X60s | rosso                             |
+-------------------------+-----------------------------------+

SELECT COLUMN_GET(strdin, 1 as char(10)) as colore, count(*) FROM t1 group by COLUMN_GET(strdin, 1 as char(10));

+--------+----------+
| colour | count(*) |
+--------+----------+
| blu    |        1 |
| rosso  |        3 |
+--------+----------+

Aggiungere una nuova colonna:

UPDATE t1 set dinstr=COLUMN_ADD(dinstr, 15, "4G ram") where type="computer";
SELECT nome, tipo, prezzo, length(strdin) as len, column_list(strdin) as lista from t1 where type="computer";

+-------------------------+----------+--------+------+--------+
| nome                    | tipo     | prezzo | len  | lista  |
+-------------------------+----------+--------+------+--------+
| BM/Lenovo Thinkpad X60s | computer | 419.00 |   29 | 1,3,15 |
+-------------------------+----------+--------+------+--------+

Integrità delle colonne dinamiche

Le colonne dinamiche cercano di comprimere i dati in modo da richiedere meno spazio su disco pur garantendo un tempo d'accesso ragionevole. Per questo motivo un danno nella stringa può causare la perdita completa dei dati nelle colonne dinamiche di un record.

Ciò probabilmente accade se si aggiornano le colonne dinamiche con dati troppo lunghi. MariaDB (per default) tronca i dati e produce un warning. La scrittura di dati troncati potrebbe causare la perdita dei dati nelle colonne dinamiche dei record (modificati o meno). Per prevenire questo problema, se non si è certi che la dimensione della colonna sia sufficiente, si usi l'SQL_MODE strict, che evita di cancellare i vecchi dati e abortisce l'operazione di modifica.

Le funzioni client in C

Le funzioni per gestire le Colonne Dinamiche sono incluse in libmysqld.a, in MariaDB 5.3. In questo modo è possibile recuperare l'intero oggetto che racchiude le colonne dinamiche (normalmente un campo BLOB) dal server e scompattarlo nel client.

Le definizioni delle strutture e delle funzioni si trovano in include/ma_dyncol.h. L'implementazione si trova in mysys/ma_dyncol.c. Un esempio di utilizzo delle funzioni C si trova in unittest/mysys/ma_dyncol-t.c.

Valore dell'oggetto

Quando si recupera un valore dall'oggetto con dynamic_column_get() si ottiene un risultato avente la seguente struttura:

enum enum_dynamic_column_type
{
  DYN_COL_NULL= 0,
  DYN_COL_INT,
  DYN_COL_UINT,
  DYN_COL_DOUBLE,
  DYN_COL_STRING,
  DYN_COL_DECIMAL,
  DYN_COL_DATETIME,
  DYN_COL_DATE,
  DYN_COL_TIME
};

typedef enum enum_dynamic_column_type DYNAMIC_COLUMN_TYPE;
struct st_dynamic_column_value
{
  DYNAMIC_COLUMN_TYPE type;
  union
  {
    long long long_value;
    unsigned long long ulong_value;
    double double_value;
    struct {
      LEX_STRING string_value;
      CHARSET_INFO *charset;
    };
    struct {
      decimal_digit_t decimal_buffer[DECIMAL_BUFF_LENGTH];
      decimal_t decimal_value;
    };
    MYSQL_TIME time_value;
  };
};

'type' dice quale tipo di dati si trova nell'oggetto, mentre i dati stessi si trovano nelle parte union.

Risultati delle funzioni

Tutte le funzioni restituiscono un enum con uno dei seguenti risultati:

enum enum_dyncol_func_result
{
  ER_DYNCOL_OK= 0,
  ER_DYNCOL_YES= 1,                /* Per le funzioni che restituiscono 0/1 */
  ER_DYNCOL_FORMAT= -1,            /* Formato stringa errato */
  ER_DYNCOL_LIMIT=  -2,            /* Un limite è stato raggiunto */
  ER_DYNCOL_RESOURCE= -3,          /* Fuori dalle risorse */
  ER_DYNCOL_DATA= -4,              /* Dati in input non corretti */
  ER_DYNCOL_UNKNOWN_CHARSET= -5    /* Set di caratteri sconosciuto */
};

Inizializzare e liberare l'oggetto

Per inizializzare un oggetto DYNAMIC_COLUMN si usa il codice seguente (che imposta a 0 tutti i valori):

void dynamic_column_initialize(DYNAMIC_COLUMN *str);

Quando non si ha più bisogno dell'oggetto, è meglio liberare la memoria allocata:

void dynamic_column_column_free(DYNAMIC_COLUMN *str);

Creare un oggetto

Per creare un oggetto con un valore:

enum enum_dyncol_func_result
dynamic_column_create(DYNAMIC_COLUMN *str,
                      uint column_nr, DYNAMIC_COLUMN_VALUE *value);

Per creare un oggetto con più valori:

enum enum_dyncol_func_result
dynamic_column_create_many(DYNAMIC_COLUMN *str,
                           uint column_count,
                           uint *column_numbers,
                           DYNAMIC_COLUMN_VALUE *values);

In questo caso si crea un array contenente i numeri delle colonne e un altro array per i valori.

'str' può essere un oggetto inizializzato con dynamic_column_initialize(DYNAMIC_COLUMN *str), oppure un oggetto DYNAMIC_COLUMN * già esistente che si desidera riutilizzare.

Cancellare l'oggetto

enum enum_dyncol_func_result
dynamic_column_delete(DYNAMIC_COLUMN *org, uint column_nr);

Verificare quali colonne esistono

Per sapere se una colonna esiste:

enum enum_dyncol_func_result
dynamic_column_exists(DYNAMIC_COLUMN *org, uint column_nr);

Per ottenere un elenco delle colonne esistenti:

enum enum_dyncol_func_result
dynamic_column_list(DYNAMIC_COLUMN *org, DYNAMIC_ARRAY *array_of_uint);

Nell'ultimo caso, l'oggetto DYNAMIC_ARRAY verrà inizializzato dalla funzione. Per funzioni per manipolare DYNAMIC_ARRAY sono:

Liberare l'array:

delete_dynamic(DYNAMIC_ARRAY *array);

Numero di elementi nell'array:

array.elements 

Accedere a un elemento:

unsigned int column= dynamic_element(array, index, unsigned int)

Utilizzare le Colonne Dinamiche con i propri oggetti

Se si legge un campo BLOB da MariaDB e si desidera utilizzarlo con le funzioni sopra illustrate, probabilmente non si desidera duplicarlo per accedere ai suoi dati. Si può allora utilizzare il seguente trucco per creare un oggetto DYNAMIC_COLUMN che si potrà utilizzare con quelle funzioni che non lo modificano:

char   *blob;           /* i dati */
size_t  blob_length;    /* lunghezza del blob */
DYNAMIC_STRING dynstr;
struct st_dynamic_column_value value;

dynamic_column_initialize(DYNAMIC_COLUMN &str);

dynstr.str= blog;
dynstr.length= length;
if (dynamic_column_get(&str, 1, &value))
{
  /* fai qualcosa con il valore della colonna 1... */
}

Obiettivi dell'implementazione

Lo sviluppo delle Colonne Dinamiche ha tentato di soddisfare i seguenti obiettivi:

• Possibilità di utilizzare le Colonne Dinamiche ovunque si utilizzi una colonna normale (ORDER BY, GROUP BY etc.)
• Per lavorare con le Colonne Dinamiche si usano le funzioni (non gli opetatori) per rendere più facile l'utilizzo dai diversi client SQL (come ODBC).
• Richiedono un sovrappiù di memoria molto ridotto, rispetto alle altre colonne (attualmente 4-6 byte per ogni colonna)
• Ottimizzate per la lettura; la velocità in lettura si avvicina a quella di qualsiasi altra colonna.
• Le Colonne Dinamiche non supportano il valore NULL; se non hanno un valore, allora non viene immagazzinato. Per questo una Colonna Dinamica viene eliminata se si inserisce NULL.
• Poche funzioni, perché siano facili da comprendere e da utilizzare.
• Licenza libera (BSD) per facilitarne il port in linguaggi diversi dal C.
• In futuro dovrebbe essere possibile modificare il formato senza modificare i dati già esistenti (il primo byte del formato è un flag).
• La prima versione è ottimizzata per istruzioni SQL generate da programma; se questa funzionalità verrà utilizzata, verranno aggiunte altre caratteristiche come i nomi per le colonne.

Limiti

• Le colonne sono identificate da numeri (si veda il To-Do, sotto)
• E' possibile utilizzare numeri da 0 a 65535 per una colonna
• Le dimensioni massime dell'oggetto sono di 536870911 byte (29 bit).
• L'unico modo per indicizzare una Colonna Dinamica è creare una Colonna Virtuale. Tutta via in questo caso verrà creata un'istanza della colonna virtuale.
• Non è possibile specificare due volte lo stesso numero di colonna in COLUMN_CREATE() o COLUMN_ADD(). Se si tenta di farlo si ottiene un errore poco chiaro: "Illegal value used as argument of dynamic column function". Questo problema verrà risolto in futuro in modo che venga usato l'ultimo argomento.

To-Do

• Implementare una funzione COLUMN_CHECK() per controllare se il contenuto della colonna è ben formato. (Utile per verificare l'integrità e trovare errori).
• Permettere di specificare due volte lo stesso numero di colonna in COLUMN_CREATE() / COLUMN_ADD().
• Modificare la sintassi di COLUMN_CREATE() perché sia uguale a COLUMN_GET().
• Implementare gli indici funzionali. Questo risolverà in parte il problema di indicizzare le colonne virtuali (perché aggiungere un indice funzionale non implica una nuova istanza).
• Prevedere un modo per associare dei nomi alle colonne virtuali. Il problema è decidere dove memorizzare il nome: in ogni riga, nel file .frm o in un nuovo file. tutte le soluzioni hanno vantaggi differenti in termini di spazio e velocità, perciò gli sviluppatori attendono di ottenere dei feedback per prendere questa decisione.

Si veda anche

• Cos'è MariaDB 5.3
• Microsecondi in MariaDB