Sante Caserio

L’SQL è un linguaggio imperativo. Questo significa che è costituito da una serie di comandi che indicano un’azione da compiere, lasciando poco spazio per le strutture di controllo del flusso. Sappiamo che il suo sottoinsieme noto come DML (Data Manipulation Language) comprende tre istruzioni fondamentali: DELETE per cancellare i record, UPDATE per aggiornarli e INSERT per inserirli. Sono azioni piuttosto elementari, che nel nostro lavoro quotidiano dovrebbero combinarsi formando istruzioni più complesse. Un esempio di richiesta complessa che spesso si vorrebbe rivolgere a un database è: “se un certo record nella tabella XXX esiste già, allora fai questo”. Mancando in SQL le istruzioni condizionali, generalmente questo tipo di logica è contenuta nel programma che si occupa di lanciare le query (esempio: uno script PHP). Un’alternativa è scrivere una Stored Procedure che se ne occupi, perchè tali procedure ammettono estensioni procedurali al linguaggio SQL, o possono essere scritte in altri linguaggi. MySQL però comprende alcune comode estensioni non standard che permettono di comunicare al server come si debba comportare nel caso in cui un certo record già esista.

Valori unici

Cosa significa per un database “questo record esiste già”? Come sappiamo, un record può essere identificato da una o più chiavi, ognuna delle quali può essere un campo, la prima parte di un campo o una combinazione di più campi. Generalmente la Chiave Primaria, che è il modo principale per identificare un record, è costituita da un campo numerico (UNSIGNED INTEGER) chiamato `id`. Vi possono essere delle Chiavi Candidate, che consistono in velori o combinazioni di valori che sono unici per ogni record. In MySQL questi sono i campi che rappresentano gli indici UNIQUE.

INSERT IGNORE

La prima di queste estensioni è la clausola IGNORE dell’istruzione INSERT. Essa semplicemente chiede a MySQL di ignorare gli errori dovuti al tentativo di inserire valori duplicati nella chiave primaria. In altre parole, se si tenta di inserire un record ma esso non può essere scritto poiché contiene un valore unico già presente in tabella, non viene generato alcun messaggio di errore. Se si tenta di inserire diversi record con un’unica INSERT e alcuni di questi non possono essere scritti perchè contengono valori duplicati, gli altri record verranno scritti senza problemi e non viene restituito alcun errore. Spesso i messaggi di errore hanno effetti collaterali che si desidera evitare.
Un’ulteriore possibilità è usare, oltre alla clausola IGNORE, anche la clausola DELAYED. Essa chiede a MySQL di non generare alcun messaggio di errore in nessun caso. Questo vale per gli errori di tipo “Duplicate key”, ma anche ogni altro tipo di errore - ad esempio la mancanza dei permessi necessari per scrivere sulla tabella, disco pieno, etc. Se tale comportamento ci è comunque gradito (perchè non abbiamo intenzione di gestire alcun tipo di errore) allora la clausola DELAYED è da preferire per ragioni di performance. Infatti ciò che rallenta maggiormente le applicazioni sono le comunicazioni di rete e il mancato invio di messaggi di errore che verrebbero comunque ignorati è un’ottimizzazione intelligente. Comunque, è bene ricordare che gli unici Storage Engine a supportare questa clausola sono MyISAM, MEMORY, ARCHIVE e BLACKHOLE. Se usata su tabelle che usano altri motori, DELAYED provoca un errore.

Esempi:

INSERT IGNORE INTO `clienti` (`nome`, `cognome`, `tel`) VALUES ('Mario', 'Rossi', '123456');
INSERT DELAYED IGNORE INTO `clienti` (`nome`, `cognome`, `tel`) VALUES ('Maria', 'Bianchi', '654321');

REPLACE

Il comportamento appena illustrato è desiderabile nel caso in cui il record che si tenta di inserire non possa essere più completo di quello già esistente. Se però nel record già presente in tabella alcuni campi sono vuoti (o meglio, impostati ai valori di DEFAULT), anche nel caso in cui l’istruzione INSERT contenga tali valori, essi andranno persi.
Esiste però il caso opposto, cioè quello in cui si presume che il record già presente contenga meno informazioni (o informazioni meno aggiornate) rispetto a quelle che si sta tentando di inserire. In tal caso è ragionevole chiedere a MySQL si inserire sempre e comunque il nuovo record, sostituendo quello che eventualmente potrebbe essere già presente. Per fare questo si utilizza l’istruzione REPLACE.
Essa è una combinazione di DELETE + INSERT. Questo implica alcune conseguenze non del tutto ovvie. La prima riguarda i permessi: per eseguire REPLACE bisogna avere i permessi di DELETE e INSERT. Inoltre, se si esegue REPLACE su una tabella InnoDB (o su altre tabelle che supportano le Chiavi Esterne) prima di inserire il nuovo record si cancellerà il vecchio, scatenando l’evento ad esso associato (come ON DELETE SET NULL). Infine, se sulla tabella sulla quale si esegue una REPLACE esiste un trigger associato all’evento DELETE, questo verrà lanciato.

Esempi:

REPLACE INTO `clienti` SET `nome`='Mario', `cognome`='Rossi', `tel`='123456';
REPLACE DELAYED INTO `clienti` SET `nome`='Maria', `cognome`='Bianchi', `tel`='654321';

Si noti, infine, che REPLACE potrebbe eliminare più di un record prima di inserire il nuovo. Infatti, come si è detto, una tabella può avere più chiavi uniche (PRIMARY KEY e campi UNIQUE) e naturalmente un record potrebbe contenere diversi valori duplicati. Dopo aver lanciato questa istruzione, il numero di affected_rows() restituito corrisponde a 1 (il nuovo record inserito) + il numero di record eliminati. Sapere questo ci permette di determinare facilmente se uno o più record sono stati liminati e quanti:

$deleted_rows = mysqli_affected_rows($con) – 1;

INSERT … ON DUPLICATE KEY UPDATE

Vi è poi un ultimo caso, che è una via di mezzo tra i due precedenti. Può darsi che si voglia inserire un record nel caso esso non sia già esistente, ma che se esso esiste non vogliamo perdere né alcune informazioni già scritte in tabella, né alcune informazioni presenti nell’istruzione. In tal caso si utilizzerà INSERT con la clausola ON DUPLICATE KEY UPDATE.

Vediamo subito un esempio, nel quale tentiamo di inserire un nuovo utente; se esso esiste, vengono modificati il suo indirizzo email e l’url del suo blog, lasciando però inalterate tutte le altre informazioni.

INSERT INTO `utenti` (`username`, `password`, `email`, `www`)
VALUES ('pippo', 'sesamo', 'pippo@topolinia.com', 'pippo.noblogs.org')
ON DUPLICATE KEY UPDATE `email`='pippo@topolinia.com', `www`='pippo.noblogs.org';

INSERT … ON DUPLICATE KEY UPDATE non può essere utilizzata con DELAYED, sebbene ciò non generi alcun errore. E’ possibile inserire diversi record come si fa talvolta con le INSERT anche utilizzando la clausola ON DUPLICATE KEY UPDATE. Il numero di affected_rows() equivale al numero di record inseriti + (il numero di record modificati * 2). Questo implica che non è possibile sapere con esattezza quanti record sono stati aggiunti e quanti sono stati modificati basandosi esclusivamente su affected_rows().

Voglio parlare di due errori molto banali che possono essere farti in SQL, soprattutto dai principianti. La particolarità di questi due errori, che sicuramente non saranno gli unici nel loro genere, è che sembrano avere un significato e invece ne hanno un altro. Di conseguenza il server (almeno MySQL, usato per provare le sintassi) eseguirà ciò che gli si chiede di fare, che forse non è ciò che si intendeva realmente.

Creiamo la tabella che useremo per il test:

USE test

CREATE TABLE `t1` (
`c1` INT ,
`c2` INT
)

Inseriamo un record che ci servirà:

INSERT INTO t1 VALUES (1, 2)

Fin qui dovrebbe essere andato tutto bene e lo possiamo verificare così:

SELECT * FROM t1

Uso errato di OR

Questo errore non riguarda solo SQL ma un po’ tutti i linguaggi. Esempio:

SELECT * FROM t1 WHERE c1=100 OR 200

Questa query restituirà il record che abbiamo inserito, nonostante c1 non valga 100 nè 200. Perchè? Perchè l’espressione viene valutata così:

SELECT * FROM t1 WHERE (c1=100) OR 200

Qualsiasi valore intero diverso da 0, se convertito in booleano, risulta TRUE. Di conseguenza il risultato di (c1=100) OR 200 non può che essere TRUE.

La sintassi corretta è:

SELECT * FROM t1 WHERE c1=100 OR c1=200

Oppure:

SELECT * FROM t1 WHERE c1 IN (100, 200)

Errore nella UPDATE

Mi è capitato di vedere una istruzione UPDATE scritta in modo sbagliato e un programmatore alle prime armi disperato:

UPDATE t1 SET c1=100 AND c2=200

Questa istruzione cambia il valore del campo c1 di tutti i record in 0, perchè viene valutata così:

UPDATE t1 SET c1=(100 AND c2=200)

100 è sempre TRUE, di conseguenza l’espressione restituisce TRUE (1) se c2=200, altrimenti FALSE (0).

Chiaramente la sintassi esatta è la seguente:

UPDATE t1 SET c1=100, c2=200

Conclusione

Nei casi (rari e non certo “puliti”) in cui il programmatore voglia scrivere le query che qui ho presentato come errori, ottenendo esattamente ciò che il server effettivamente fa, dovrebbe comunque usare le parentesi. Altrimenti l’istruzione risulterà chiarissima per un computer ma un po’ meno per un essere umano.

Riprendo a scrivere qualcosa di (spero) utile su MySQL e tratto un argomento strano: gli utenti. In realtà MySQL ha sempre avuto un sistema molto potente per la gestione dei permessi, che quasi tutti hanno sempre ignorato creando o utilizzando (in hosting) un utente che ha tutti i permessi possibili almeno sui suoi database. Ma vale la pena di saperne di più.

In realtà bisogna distinguere tra:

• Autenticazione - il login, l’accesso al sistema nudo e crudo
• Permessi - ciò che un utente può o non può fare dopo il login

Per ora parleremo solo di autenticazione, accennando appena ai permessi. Peraltro ciò di cui parleremo prima o poi cambierà, in quanto gli sviluppatori hanno in previsione di creare un sistema di plugin anche per l’autenticazione. In questo modo sarà possibile, oltre ad autenticarsi come già si fa oggi, affidare il riconoscimento degli utenti ad altri software, come ad esempio Kerberos. Per ulteriori info, vedi questo worklog.

Creare un utente

Per creare un nuovo utente in genere si usa l’istruzione CREATE USER, la cui sintassi è:

CREATE USER utente [IDENTIFIED BY [PASSWORD] ‘password’] [, user ...]

utente è il nome dell’utente che stiamo creando.
IDENTIFIED BY [PASSWORD] indica la password da abbinargli. La ragione per cui la clausola non è obbligatoria è che un utente MySQL può anche non avere una password (sconsigliatissimo).
Con una sola istruzione è possibile creare più utenti.

Esempi:

CREATE USER pippo
CREATE USER pippo@topolinia.net
CREATE USER pippo IDENTIFIED BY ‘yukgaspgulp’

Questo può essere anche indicato con la formula ‘nome_utente’@'nome_host’; così facendo, l’utente che stiamo creando sarà utilizzanto solo quando il nome_utente viene usato da nome_host. Si può anche utilizzare il carattere ‘%’, che funzionerà esattamente come funziona in LIKE. Per esempio ‘utente1′@’%.php.net’ permette a utente1 di connettersi da qualsiasi host che faccia parte della sottorete di php.net.

Per create un utente abbiamo bisogno del permesso ‘CREATE USER’.

INFORMATION_SCHEMA

Forse il modo più amichevole per vedere gli utenti esistenti è utilizzare la tabella `USER_PRIVILEGES` del database virtuale `INFORMATION_SCHEMA`. Naturalmente si tratta di una tabella a sola lettura, utilizzabile solo per conoscere gli utenti e non per modificarli. Le due colonne sono:

• GRANTEE - contiene il nome utente nella formula ‘nome_utente’@'nome_host’, anche quando il nome dell’host non è stato specificato in fase di creazione;
• TABLE_CATALOG - inutilizzata (è prevista dall’SQL standard ma in MySQL è sempre null perchè allo stato attuale non esistono i cataloghi; vedi questo worklog)
• PRIVILEGE_TYPE - un tipo di privilegio assegnato all’utente: per ogni privilegio assegnato c’è una riga;
• IS_GRANTABLE - vale ‘YES’ o ‘NO’ e indica se l’utente può assegnare o revocare questo permesso ad altri utenti con le istruzioni CREATE USER, GRANT e REVOKE.

Si, ma dove sono gli utenti?

Le tabelle presenti nell’INFORMATION_SCHEMA sono appunto virtuali e di sola lettura. Ma dove sono registrati fisicamente gli utenti e i loro permessi?

Nel database di sistema ‘mysql’. Bisogna fare molta attenzione nel modificarlo direttamente con le istruzioni INSERT, UPDATE e DELETE, perchè creare incongruenze proprio in questo database può pregiudicare il corretto funzionamento di MySQL. Vi ho terrorizzati? No? Peggio per voi, vorrà dire che farete un pasticcio e dovrete reinstallare tutto. Così imparate a non darmi retta. Ed è inutile che mi mandiate le mail per dirmi “avevi ragione, dovevo stare più attento”, perchè ormai il patatrak è fatto. Vabbè, andiamo avanti :)

L’unica tabella che esamineremo in questo articolo è user. Le sue colonne sono:

• Host - il nome dell’host associato all’utente (’%’ significa ‘qualsiasi host’);
• User - il nome utente;
• Password - la sua password criptata con un algoritmo a una via (si può confrontarla con un’altra password criptandola allo stesso modo e vedere se corrisponde, ma non si può decriptarla);
• ssl_type - il tipo di connessione SSL usata dall’utente (connessione criptata), se usata;
• ssl-cipher - la chiave pubblica, se usata;
• x509_issuer - ente emettitore del certificato SSL, se c’è;
• x509_subject - soggetto del certificato SSL, se c’è;
• max_questions - massimo di domande (SELECT, SHOW, DESCRIBE, HELP) eseguibili in un’ora, 0=nessun limite;
• max_updates - numero di scritture sulle tabelle eseguibili in un’ora, 0=nessun limite;
• max_connections - ?
• max_user_connections - numero di connessioni eseguibili in un’ora, 0=nessun limite;

I privilegi

Per non occupare troppo spazio non ho elencato le colonne relative ai privilegi. Di questi non ci occupiamo più di tanto in questo articolo. Per ora, basti dire che per ogni tipo di privilegio esiste una colonna denominata nomeprivilegio_priv. Per esempio: Inser_priv, Update_priv, Delete_priv. Questo colonne possono avere come valore ‘Y’ o ‘N’.

Le risorse

Le ultime colonne servono a dare un limite alle risorse utilizzabili da ogni singolo utente. Se non avete queste colonne significa che state usando una versione piuttosto vecchia di MySQL, passando ad una versione recente potrete aggiungerle con lo script mysql_upgrade. I limiti sono indicati su base oraria, il valore predefinito è 0 (nessun limite). Oltre che agendo direttamente su questa tabella, possono essere impostati con le istruzioni GRANT e REVOKE, di cui non ci occupiamo qui. Il contatore delle risorse utilizzate finora può essere resettato con il comando SQL FLUSH PRIVILEGES o chiamando mysqladmin reload da riga di comando. Entrambi, però, non riguardano il numero massimo di connessioni.

Altre tabelle

• host - permessi assegnati a un host su un certo database o su tutti;
• tables_priv - permessi assegnati su una tabella specifica;
• columns_priv - permessi su una colonna specifica;
• prov_priv - permessi su una procedura.

Eliminare un utente

Eliminare un utente è molto semplice:

DROP USER utente [, utente ...]

utente è naturalmente il nome dell’utente (degli utenti) che intendiamo eliminare. Il comando non elimina automaticamente i permessi associati all’utente, lasciando quindi delle voci orfane nella tabella relativa.

Per compiere questa operazione dobbiamo disporre del permesso ‘CREATE USER’ o del permesso ‘DELETE’ sul database ‘mysql’.

Cambiare un nome utente o una password

Abbiamo a disposizione due istruzioni SQL che ci permettono di cambiare il nome utente o una password senza agire sul database MySQL.

RENAME USER è stata aggiunta in MySQL 5.0.2 e la sua sintassi è:

RENAME USER nome_vecchio TO nome_nuovo [, ...]

Questa istruzione si limita a modificare il nome utente nella tabella user, ma tutti i privilegi resteranno assegnati al vecchio nome utente (che potrebbe essere ricreato).

Per cambiare una password:

SET PASSWORD [FOR 'utente'] = ‘password’

Se FOR ‘utente’ non è specificato, verrà cambiata la propria password.
utente è il nome dell’utente al quale stiamo cambiando la password.
‘password’ è la nuova password. Può essere già criptata (ma bisogna utilizzare lo stesso algoritmo della funzione PASSWORD()!), o si può usare una delle due funzioni PASSWORD() (più recente) o OLD_PASSWORD() (non sicura, ma compatibile con le vecchie versioni di MySQL). Esempio:

SET PASSWORD FOR pippo = PASSWORD(’londonburns999′)

Drizzle

Il sistema di permessi di MySQL dovrebbe essere stato completamente eliminato da Drizzle, mentre è probabile che l’autenticazione e la gestione utenti funzionino più o meno allo stesso modo. Ne sapremo di più dopo la prima release.

Abbiamo già visto in un altro articolo come selezionare record scelti casualmente. Riassumendo velocemente (ma chi non capisse dovrebbe leggere l’articolo in questione, possibilmente, prima di procedere) si trattava di utilizzare la funzione rand(), che genera numeri casuali, dentro la clausola ORDER BY. In questo modo l’ordine in cui i record vengono selezionati (o aggiornati o cancellati) diventa casuale. Se poi aggiungiamo una clausola LIMIT, verranno selezionati solo i primi record di un insieme scelto casualmente… ovvero, avremo X record scelti a casaccio.

Questo approccio ha 2 limitazioni:

• i record non possono essere ordinati: l’ordine è determinato dalla funzione rand(), quindi non vi è ordine;
• siamo obbligati a selezionare un numero di record prefissato (tutti, oppure il numero di record indicato nella LIMIT).

Anche se esula dall’ambito di questo articolo, accenno rapidamente che al primo problema si potrebbe ovviare anche con una subquery di questo tipo:

SELECT * FROM(
SELECT * FROM CHARACTER_SETS ORDER BY rand() LIMIT 10
) unordered ORDER BY 1

Questo metodo, oltre a non essere (a mio parere) pulitissimo, dai miei test risulta meno performante del metodo che illustrerò in questo articolo.

Basta con i preamboli. Il metodo di cui parleremo oggi è una variazione del precedente. Consiste nell’usare la funzione rand() nella clausola WHERE. Poiché rand() restituisce un numero di tipo FLOAT tra 0 (compreso) e 1 (non compreso), noi sappiamo che vi è il 50% delle probabilità che rand()<0.5. Possiamo sfruttare tale consapevolezza per far sì che un particolare record abbia il 50% delle probabilità di non essere trovato:

SELECT * FROM miatabella WHERE id=1 AND rand()<0.5

In questo caso il record non viene selezionato casualmente, anzi è sempre lo stesso dal momento che ne specifichiamo l’id, ma viene deciso casualmente se il record esiste o meno. Chiaramente possiamo aumentare o diminuire le probabilità a nostro piacimento:

SELECT * FROM miatabella WHERE id=1 AND rand()<0.33 -- 33%
SELECT * FROM miatabella WHERE id=1 AND rand()<0.8 -- 80%

Possiamo anche selezionare tutti i record di una tabella, eliminandone una buona parte con il trucchetto appena spiegato. In questo modo avremo un numero di record variabile, perchè non è possibile determinare quanti record otterranno un valore di rand() minore di X. Probabilmente vorremo anche ordinare i risultati. Esempio:

SELECT * FROM CHARACTER_SETS WHERE rand()<0.5 ORDER BY CHARACTER_SET_NAME

Nulla però ci impedisce di ordinarli in maniera casuale:

SELECT * FROM CHARACTER_SETS WHERE rand()<0.5 ORDER BY rand()

Infine possiamo scegliere un numero fisso di record. Si noti che questa tecnica è utilizzabile solo con grandi tabelle, o meglio, con tabelle il cui numero di record è molto superiore al numero di record che desideriamo legere. Questo perchè, basandosi sul caso, non esiste nessuna garanzia che un certo numero di record risponderà alla clausola WHERE. Se trovare quel numero di record è di importanza critica, questo metodo è sconsigliabile. In caso contrario, è sicuramente più performante che non utilizzare una subquery. Ad ogni modo, si fa così:

SELECT * FROM CHARACTER_SETS WHERE rand()<0.5 ORDER BY CHARACTER_SET_NAME LIMIT 5

Divertitevi!

Sono ben felice di pubblicare un articolo inviatomi dall’amico losciuto, buona lettura.

Ciao,

come dicevo prima le info mi sono servite :) Quello che segue è un esempio di import di record da un database MSSQL ad un database MySQL.
Come prima cosa bisogna che ODBC sia correttamente configurato in modo che il relativo collegamento funzioni.
Testato e funzionante su MSSQL 2000 server e MySQL 5.0.51a-3ubuntu5.4. Per quel che ne so funziona i diversi engine senza problemi.

Questo è uno stralcio di odbc.ini:

[ODBC Data Sources]
dbsumssql = Sybase JDBC Server

…

[Default]
Driver = /usr/local/lib/libtdsodbc.so
UsageCount = 3

…

[dbsumssql]
Driver = /usr/local/lib/libtdsodbc.so
Description = Sybase JDBC Server
Trace = No
Server =
Port = 1433
Database = Card
TDS_Version = 8.0
UsageCount = 3

Uso php, ma le query SQL vanno bene per qualsiasi linguaggio, ovviamente.

///////// copia records da db MSSQL a db MySQL

// lista con i nomi dei campi utilizzata per il loop di formazione della query
$digidbnc = array();

// archivio su MSSQL
$myMSSQLdb=’Card’;

// archivio su MySQL
$myMySQLdb = ‘Card_dbo’;

// le tabelle hanno lo stesso nome sia in MSSQL che MySQL
$myMSSQLtab = ‘cards’;
$myMySQLtab = ‘cards’;

// apri database su MySQL
$hostname_myd = “localhost”;
$database_myd = $myMySQLdb;
$username_myd = “utente”;
$password_myd = “password”;
$myMySQL = mysql_pconnect($hostname_myd, $username_myd, $password_myd) or trigger_error(mysql_error(),E_USER_ERROR);

// apre database su MSSQL per la lettura dei dati da inserire
$connect = odbc_connect(’$myMSSQLdb’, ‘utente’, ‘password’);
$query = “SELECT * FROM $myMSSQLtab”;
$rs = odbc_exec($connect, $query);

// acquisizione dinamica dei nomi dei campi
for($j=1;$j<=odbc_num_fields($rs);$j++) {
$digidbnc[] = odbc_field_name($rs,$j);
}

echo “Inizio inserimento…\n”;

// ciclo di lettura righe dal database in MSSQL
while (odbc_fetch_row($rs)) {
//cotatore alla vecchia maniera, ma sempre efficace,
// voi potete utilizzare anche al Key di $digidbnc[]
$conta = 0;
// parte della query con i nomi dei campi
$campi = “”;
// lista col contenuto dei campi
$fields = array();
// formazione della query di inserimento
$sql = “INSERT INTO $myMySQLtab (”;
$valori = ” VALUES (”;
foreach($digidbnc as $campo) {
$fields[$conta] = odbc_result($rs, $campo);
$campi .= $campo . “, “;
$valori .= “‘$fields[$conta]‘, “;
$conta++;
}
// tolgo l’ultima virgola sulla stringa dei nomi dei campi e
// su quella dei relativi valori
$campi = substr($campi, 0, strlen(rtrim($campi))-1);
$valori = substr($valori, 0, strlen(rtrim($valori))-1);
// stringa definitiva per l’inserimento
$sql = $sql . $campi . “) ” . $valori . “)”;
// selezione del database di destinazione su MySQL
mysql_select_db($database_myd, $myMySQL);
// esecuzione query di inserimento
$res_myd = mysql_query($sql, $myMySQL) or die(mysql_error());
}

odbc_close($connect);
mysql_close();

Vista la flessibilità, è possibile usare questa routine per qualunque db e/o tabella.
Le variabili da modificare sono:
// archivio su MSSQL
$myMSSQLdb=’Card’;

// archivio su MySQL
$myMySQLdb = ‘Card_dbo’;

// le tabelle hanno lo stesso nome sia in MSSQL che MySQL
$myMSSQLtab = ‘cards’;
$myMySQLtab = ‘cards’;

oltre a quelle relative ai server ed utenza e password.

A presto.

Vedi anche: Copiare le tabelle di MySQL

Dalla versione 4.1 MySQL fornisce GROUP_CONCAT(). Questa funzione concatena in un’unica stringa un gruppo di valori che normalmente si trovano su record differenti. Questa funzione deve essere utilizzata con la clausola GROUP BY, ma ovviamente l’uso di quest’ultima può essere pretestuoso. Un esempio per chiarire:

SELECT l.titolo AS titolo, GROUP_CONCAT(a.nome) AS autori FROM libro l
LEFT JOIN libro_autore la ON l.id=la.id_libro
LEFT JOIN autore a ON la.id_autore=a.autore
GROUP BY l.id

Come è facile intuire, la tabella libro_autore rappresenta un’associazione “molti a molti” tra i libri e gli autori. La qui presente doppia JOIN restituisce, per ogni libro, tutti i suoi autori. Grazie alla funzione GROUP_CONCAT() però ogni associazione libro-autore non è un singolo record. Vi è un unico record per ogni libro e tutti gli autori sono elencati nel valore “autori” separati da virgole. Un esempio di possibili risultati:

titolo: The C Programmi Language
autore: B. W. Kernighan,Dennis Ritchie
titolo: La gioia armata
autore: A. M. Bonanno

La sintassi

La sintassi completa è:

GROUP_CONCAT([DISTINCT] espressione [, espressione ...]
[ORDER BY {unsigned_integer | nome_colonna | espressione}
[ASC | DESC] [, nome_colonna ...]]
[SEPARATOR stringa])

E' opportuno ribadire che GROUP_CONCAT può essere utilizzata solo in presenza della clausola GROUP BY. E' per questo che nell'esempio è stata utilizzata, nonostante non abbia alcuna utilità su un campo unico (come id). In caso contrario, la query restituirebbe un errore: "Invalid GROUP BY function'.

Il separatore

Il separatore predefinito è la virgola, senza alcuno spazio prima o dopo. Questo va bene nel caso si voglia ottenere una stringa che PHP “esploderà” in un array tramite la funzione explode() (o qualche altro linguaggio la esploderà con qualche altra funzione). Ovviamente, è possibile solo se i valori da concatenare non contengono virgole.

Il seguente esempio mostra come concatenare valori separandoli con una virgola e uno spazio, in modo che possano essere presentati agli esseri umani senza modifiche da parte di PHP o altri linguaggi:

SELECT l.titolo AS titolo, GROUP_CONCAT(a.nome SEPARATOR ‘, ‘) AS autori FROM libro l
LEFT JOIN libro_autore la ON l.id=la.id_libro
LEFT JOIN autore a ON la.id_autore=a.autore
GROUP BY l.id

Ordinamento

Come si è visto nella sintassi, è possibile estrarre con GROUP BY qualsiasi espressione SQL, comprese subquery, variabili di sessione o globali, etc. Tali valori possono essere ordinati con la clausola ORDER BY.

Per quanto riguarda invece l’ordinamento dei record della query esterna, esso è influenzato naturalmente dalla clausola GROUP BY (si veda in proposito l’articolo La clausola ORDER BY in profondità). Se si desidera invertire tale ordinamento, è possibile utilizzare la parola chiave DESC. Se si desidera ordinare i record in base ad altri campi, non presenti nella GROUP BY, è possibile aggiungere una clausola ORDER BY. Se invece non si desidera affatto ordinarli (ottenendo così performance migliori) è possibile annullare l’ordinamento con ORDER BY NULL. Esempio:

SELECT l.titolo AS titolo, GROUP_CONCAT(a.nome) AS autori FROM libro l
LEFT JOIN libro_autore la ON l.id=la.id_libro
LEFT JOIN autore a ON la.id_autore=a.autore
GROUP BY l.id ORDER BY NULL

Prestazioni

Spesso l’uso di GROUP_CONCAT() può ridurre le comunicazioni tra il client e il server MySQL, che generalmente costituiscono uno dei colli di bottiglia. Inoltre la funzione in sè sembra essere abbastanza veloce.

Limiti

Vi è un limite al numero di record che possono essere concatenati. Esso è rappresentato dalla variabile group_concat_max_len, il cui valore predefinito è 1024. La variabile è modificabile a run time. Esempio:

SET GLOBAL group_concat_max_len=4096

Se poi desideriamo modificare in modo permanente tale limite, basta agire sul file my.cnf.

Di seeQuence ho già parlato. Si tratta di una libreria (1 classe + 1 funzione) PHP che implementa le sequenze stile SQL sul lato client. Può essere utilizzata con i DBMS che non supportano le sequenze (MySQL, SQLite ecc), con quelli che offrono un supporto limitato (Firebird/Interbase etc), con i file CSV, XML, tracciati record, etc.

La documentazione in inglese è stata estesa: mancavano le specifiche riguardanti i valori predefiniti e la sintassi SQL supportata per importare/esportare su PostgreSQL/Oracle. L’errore più divertente era ASS invece di ADD… ecco cosa succede a scrivere la documentazione di notte.

Ora c’è anche la documentazione in italiano, sicuramente più corretta dal punto di vista del linguaggio. E’ una traduzione completa, quindi gli utenti italiani al momento non hanno motivo di preferire quella inglese.

Ringrazio il progetto Documentation, ospitato da Wikia, e vi invito a usarlo per le vostre documentazioni.

Vi ricordo la home page del progetto seeQuence: http://santecaserio.altervista.org/seequence/

Oggi ho rilasciato seeQuence 0.3. Si tratta di una libreria (1 piccola classe + 1 piccola funzione) PHP che implementa lato client le sequenze stile PostgreSQL / Oracle. Il numero di versione fa pensare a un software estremamente instabile, ma non è così: non ho ancora trovato bug, semplicemente finchè non ricevo feedback dagli utenti non posso rilasciare una versione 1.0.

Alcuni casi in cui seeQuence può risultare utile:

• in combinazione con DBMS che non supportano le sequence (MySQL, SQLite, etc) o che le supportano in modo molto limitato (Firebird/Interbase)
• per generare id nei file CSV / XML / altri formati
• per generare id unici per dati che si trovano in fonti diverse (dbms, file etc)

L’implementazione di riferimento è quella di PostgreSQL. Supporta quindi: valore minimo, valore massimo e incremento definiti dall’utente, possibilità di rotazione, valore iniziale; supporta i numeri negativi e le sequenze discendenti (incremento negativo). Possibilità di creare la sequenza partendo dall’istruzione SQL (create sequence) che la genera (sintassi PostgreSQL / Oracle) e di esportarla in SQL. Possibilità di inserire i valori sequenziali in un array associativo. Possibilità di generare molte sequenze chiamando una sola volta la funzione create_sequences(), che imposta automaticamente i parametri perchè ognuna abbia a disposizione la più vasta gamma possibile di valori. Supporta la serializzazione.

• Pagina del progetto + download
• Documentazione (in inglese, su Wikia Documentation)
• Forum

In un precedente articolo si è vista la clausola ORDER BY, mentre in un altro la si è utilizzata per estrarre una o più righe scelte a caso. Questo articolo copre alcuni aspetti ancora non analizzati dell’ordinamento dei record.

Tipi di ordinamento

Come si sa, esistono almeno due tipi di ordinamento fondamentali: l’ordinamento alfabetico e l’ordinamento numerico. Naturalmente delle stringhe contenente solo cifre possono essere ordinate numericamente, così come dei numeri possono essere ordinati alfabeticamente.
Qual è la differenza? Se si desidera ordinare i numeri 1000 e 2, numericamente l’ordine sarà: 2, 1000, perchè 2 è ovviamente minore di 1000; mentre l’ordinamento alfabetico sarà: 1000, 2, perchè il carattere ‘1′ viene prima del carattere ‘2′.

Ordine alfabetico

Ci sono casi in cui si desidera ordinare alfabeticamente dei numeri. Il motivo più comune (forse l’unico?) è che i numeri formino un codice, che è considerato una stringa. Allora le query SQL dovranno trasformare il dato numerico in stringa e MySQL ci permette di farlo nel modo seguente:

SELECT numero FROM t ORDER BY CONCAT(numero, '')

La funzione CONCAT concatena tra loro più stringhe e restituisce il risultato, che è a sua volta una stringa. Concatenando un qualsiasi dato con una stringa vuota non lo si altera, ma il risultato è una stringa. Pertanto, nell’esempio, numero sarà ordinato alfabeticamente.
Un altro esempio può essere:

SELECT CONCAT(int1, int2) AS i FROM t ORDER BY i

Si tenga comunque presente che, se uno dei parametri passati a CONCAT è NULL, il risultato sarà anch’esso NULL. I valori NULL vengono sempre per primi.

Ordine numerico

E’ possibile ordinare numericamente delle stringhe. Il metodo per farlo si basa sullo stesso concetto appena esposto, cioè:

SELECT codice + 0 AS c FROM t ORDER BY c

Aggiungendo 0 a codice non si modifica il suo valore, ma se era una stringa il risultato c sarà necessariamente un numero, in quanto il risultato di un’addizione non può essere altro che un numero.
Questo è utile anche nel caso in cui alcuni numeri vengano concatenati tra loro come se fossero delle stringhe per ottenere un codice:

SELECT CONCAT(int1, int2, int3) + 0 AS codice ORDER BY codice

Lo stesso risultato si può ottenere agendo sui valori booleani, che corrispondono numericamente a 0 (falso) e 1 (vero):

SELECT CONCAT(bool1, bool2, int1) + 0 AS codice ORDER BY codice

Ordinamento booleano

Questo è il tipo di ordinamento che offre i risvolti più interessanti. Non si tratta per forza di ordinare campi booleani: l’ordinamento può essere effettuato su una qualsiasi espressione. Di conseguenza, se si desidera che i primi record siano quelli in cui i campi a e b sono uguali, si può usare una delle seguenti query:

SELECT * FROM x  ORDER BY (a=b) DESC
SELECT * FROM x  ORDER BY (a<>b)

Bisogna infatti ricordare che il valore false (0) viene prima di true (1). Questo aspetto in questi casi può trarre in inganno.

Non è il caso di dilungarsi qui sulle infinite possibilità dell’ordinamento booleano, ma ecco una rapida rassegna di esempi:

• Prima i non-NULL (l’equivalente del NULLS LAST di Oracle): ORDER BY (int IS NOT NULL)
• Prima le stringhe non vuote: ORDER BY LENGTH(i1)=0
• Prima i numeri pari: ORDER BY ((int1 MOD 2)>0)
• Oppure, più ottimizzato: ORDER BY (int1 & 1)
• ecc ecc

In generale, le espressioni che si possono utilizzare nella WHERE, che sono booleane, si possono utilizzare anche nella ORDER BY.

Ordinamento casuale

Se non si è già letto il precedente articolo sull’ordinamento casuale si consiglia di leggerlo. Qui si intende solo aggiungere che in alcuni casi è possibile ottimizzarlo. Ordinare in base alla funzione rnd(), infatti, è efficiente sulle tabelle di piccole dimensioni, cioè aventi poche righe. Ma con le tabelle aventi molte righe, il costo di questa operazione cresce esponenzialmente con il numero delle righe.

Se ad ogni query abbiamo bisogno che l’ordine sia differente, non c’è niente da fare: dobbiamo usare rand(). Sarebbe inutile, inoltre, prelevare un grande numero di righe con una query, farla passare attraverso una rete (anche locale) e poi ordinarle da programma. Oltre ai ritardi dovuti all’estrazione e alla trasmissione di un grosso numero di righe, si creerebbe traffico inutile.

C’è però qualche caso in cui si vuole semplicemente che le righe siano “sparpagliate”, non che ad ogni estrazione seguano un ordine differente. Allora si può semplicemente aggiungere un campo alla tabella, avente un valore casuale:

ALTER TABLE t ADD COLUMN r INTEGER UNSIGNED DEFAULT NULL

Tutto sarebbe più semplice se MySQL, come la maggior parte dei suoi concorrenti, permettesse di assegnare come valore di default un’espressione SQL. Chi utilizza DBMS che consentono questo, come PostgreSQL e Firebird, può usare la seguente query:

ALTER TABLE t ADD COLUMN r INTEGER UNSIGNED DEFAULT rand()

Chi usa MySQL, invece, dovrà assegnare un valore casuale manualmente:

UPDATE t SET r=rand()

Quando nuovi record vengono inseriti, occorre inserire esplicitamente il valore casuale in r. Se questo non è possibile (perchè ci sono già molti programmi che utilizzano quel db e noi non possiamo o non vogliamo modificarli tutti) si può assegnare il valore casuale ai nuovi record periodicamente:

UPDATE t SET r=rand() WHERE r IS NULL

Inoltre, se di tanto in tanto si desidera modificare l’ordinamento, è sufficiente eseguire periodicamente la prima query (è l’unica operazione di manutenzione dei valori casuali necessaria per gli utenti di PostgreSQL, Firebird, ecc):

UPDATE t SET r=rand()

Ho scoperto un’istruzione non documentata:

SHOW COLUMN TYPES

Se provate a eseguirla (almeno con la 5.0.51, cioè la mia) vedrete che in realtà mostra solo due tipi di dati. Probabilmente non è ancora documentata proprio perchè l’implementazione è ancora incompleta ed è stata inserita per errore. O forse non verrà mai completata: non sono poi così sicuro che sia utile.

Chi scopre altre funzioni non documentate, anche se incomplete, me lo scriva nei commenti o sul forum!