SQLite è un'ottima libreria open source e multipiattaforma per la gestione di un vero e proprio database relazionale in locale (su file) con pochissimo overhead sia in termini applicativi che di gestione. Proprio recentemente è arrivata un'ulteriore conferma della bontà di questa libreria in seguito al debutto di Google Gears che internamente utilizza SQLite per la memorizzazione dei dati offline. Una delle testimonianze della flessibilità di questa libreria risiede nella possibilità di caricare dinamicamente moduli esterni per estenderne le funzionalità, come nel caso dei due moduli ufficiali ma sperimentali FTS1 e FTS2 che implementano la ricerca full text in SQLite, purtroppo però non è ancora possibile farlo in Ruby poiché l'ultima versione della libreria sqlite3-ruby (v1.2.1) non espone ancora l'interfaccia di SQLite per il caricamento di estensioni. Fortunatamente qualcuno ci ha pensato e 与太郎 (Yotaro) ha preparato una pezza patch da applicare ai sorgenti originali, mettendola a disposizione sul suo sito dove inoltre fornisce uno zip con il tutto già compilato per Win32 tramite Mingw (ho mirrorato il suo zip per evitargli ulteriore consumo di banda). Purtroppo l'applicazione della patch attraverso il comando omonimo mi ha dato problemi in Windows dal momento che ha generato degli errori, per cui nel dubbio ho preparato uno zip con i sorgenti già patchati per evitare problemi a chi volesse compilare il tutto in autonomia in ambiente Win32. A questo punto occorre sostituire i file originali di sqlite3-ruby con quelli patchati insieme al modulo .so compilato, ecco la lista dei file modificati nel caso vogliate sostituire solo quelli: sqlite3_api.so, sqlite3/database.rb, sqlite3/driver/native/driver.rb, sqlite3/driver/dl/driver.rb, sqlite3/sqlite3/driver/dl/api.rb. Ovviamente vale lo stesso discorso anche nel caso abbiate installato sqlite3-ruby tramite rubygem. Questa patch aggiunge due metodi di istanza alla classe SQLite3::Database, trattasi di enable_load_extension e load_extension i quali permettono rispettivamente di abilitare il caricamento delle estensioni e di indicare il file binario dell'estensione da caricare, come da esempio:

require 'rubygems'
require 'sqlite3'

db = SQLite3::Database.new(':memory:')  # apre un database in memoria e non su file
db.enable_load_extension(1)             # abilita il caricamento delle estensioni in SQLite
db.load_extension('fts2.dll')           # carica il modulo FTS2 di SQLite

In MySQL, dalla versione 4.1 in avanti, è presente una funzione di aggregazione spesso trascurata ma che in alcuni casi può tornare molto utile per la sua comodità (si sta parlando di codice SQL non standard), mi riferisco alla funzione GROUP_CONCAT. Molto semplicemente questa funzione si occupa di concatenare gli elementi di un gruppo, per esempio le righe restituite da una sub-query, in un'unica stringa con la possibilità di specificare un separatore per l'unione dei vari elementi. Facciamo un esempio, supponiamo di avere tre tabelle: una che definisce l'elenco di utenti, una che definisce una serie di hobby e infine un'altra tabella ponte che mette in relazione utenti con i relativi hobby e ci permetta quindi di definire una relazione molti-a-molti. Ecco come potrebbe apparire la struttura delle nostre tabelle, in maniera molto semplificata:

CREATE TABLE `hobby` (
  `IdHobby` int(10) unsigned NOT NULL auto_increment,
  `Nome` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`IdHobby`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `utenti` (
  `IdUtente` int(10) unsigned NOT NULL auto_increment,
  `Nome` varchar(60) NOT NULL default '',
  `Email` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`IdUtente`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `utenti_hobby` (
  `IdUtente` int(10) unsigned NOT NULL default '0',
  `IdHobby` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`IdUtente`,`IdHobby`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

A questo punto inseriamo un pò di dati a nostro piacimento e creiamo la nostra query di selezione sfruttando la funzione GROUP_CONCAT:

SELECT
   u.Nome, 
   (
      SELECT GROUP_CONCAT(h.Nome ORDER BY h.Nome ASC SEPARATOR ', ') 
      FROM utenti_hobby AS uh
      JOIN hobby AS h ON h.IdHobby = uh.IdHobby 
      WHERE uh.IdUtente = u.IdUtente
   ) AS Hobby
FROM utenti AS u; 

Ecco cosa otteniamo come risultato:

+---------+-----------------------------------+
| Nome    | Hobby                             |
+---------+-----------------------------------+
| Luigi   | Calcio, Informatica, Pesca        |
| Mario   | Automobilismo, Calcio             |
| Claudio | Automobilismo, Informatica, Pesca |
+---------+-----------------------------------+
3 rows in set (0.00 sec)

Come è possibile notare la funzione GROUP_CONCAT permette di indicare anche quale ordinamento dare agli elementi soggetti alla concatenazione. Avremmo potuto certamente ottenere lo stesso risultato specificando l'ordinamento al livello della sub-select, l'unica differenza nella realtà risiede nel fatto che in questo caso MySQL avrebbe creato una tabella temporanea per effettuare l'ordinamento mentre ciò non avviene con GROUP_CONCAT (basta effettuare l'EXPLAIN delle due query per notarlo). Per maggiori informazioni è possibile consultare la documentazione di questa funzione dal sito di MySQL nella sezione riguardante le funzioni di aggregazione, cercando appunto GROUP_CONCAT all'interno della pagina. In allegato vi fornisco anche lo script sql per ricreare il semplice database che ho usato come esempio.

Per diverso tempo questo messaggio è rimasto semplicemente una bozza, con la convinzione di non aver colto qualcosa o di sbagliare da qualche parte ho preferito attendere per esprimere la mia perplessità riguardante la resa in termini prestazionali delle stored procedures in MySQL 5. Tuttavia le versioni della 5.0 passano ma il problema che ho incontrato fin dall'inizio rimane sempre lo stesso anche con le recenti beta della 5.1, per cui a questo punto ho deciso di rendere pubblico il mio disappunto sperando però che qualcuno possa quantomeno darmi una spiegazione di questo comportamento. Nel mio caso reale su una base di dati "live" di un'applicazione in produzione ho una query di SELECT che prende in causa 7 tabelle differenti nella clausola FROM e con 2 LEFT JOIN su altre due tabelle, in base all'EXPLAIN direi che la query in questione è il più ottimizzata possibile relativamente al contesto e al tipo di selezione che deve effettuare. Eseguendo questa query con una bella clausola SQL_NO_CACHE per evitare la cache di MySQL vengono resituite 87 righe in 0 secondi netti (87 rows in set (0.00 sec)). Prendendo la stessa query e inglobandola in una stored procedure, l'esecuzione restituisce le medesime 87 righe ma questa volta impiegando più di 13 secondi (87 rows in set (13.52 sec)) e occupando il 100% del processore durante l'operazione: non si può nemmeno parlare di forte degrado delle performance, si tratta di un risultato disastroso. La cosa ancora più strana è che le performance sembrano degradare in maniera esponenziale all'aumentare del set di righe risultatanti, per cui escluderei che la colpa sia da imputare alla mancata memorizzazione della stessa stored procedure in formato compilato. Posso riprodurre lo stesso problema anche attraverso una semplicissima tabella di due campi (per esempio id e descrizione) con una banale SELECT sulla stessa, quello che cambia è solamente il grado di abbassamento delle prestazioni, probabilmente per la complessità ridottissima della query, però il comportamento in generale è uguale. Qualcuno è in grado di darmi una spiegazione?

Durante la ricerca di qualche plugin decente per tenere traccia delle statistiche di questo blog ho avuto modo di riscontrare nuovamente la tendenza di moltissimi sviluppatori nel memorizzare indirizzi IP nei database sotto forma di stringa. Tali campi vengono spesso utilizzati in tabelle sfruttate alla stessa stregua di log in linea per cui sarebbe lecito aspettarsi da queste tabelle una considerevole quantità di record con una conseguente occupazione notevole di spazio. In realtà esiste una soluzione per ridurre sensibilmente questo problema anche se a quanto pare risulta sconosciuta a molti, vediamo quindi come fare utilizzando MySQL (comunque il discorso vale, seppur con qualche minima differenza, anche per molti altri database).

Leggi il resto di questo messaggio »