Archivi categoria: Database

Script bash per automatizzare le query su MySQL

Scenario

Supponiamo che il vostro DBMS (MySQL) stia gestendo più database e che sia necessario effettuare delle query  “a tappeto”, ovvero su tutte le tabelle relative a ciascuno di essi (cercando, ad esempio, un determinato record oppure un pattern specifico).

bashEseguire tale operazione “a mano” può essere tedioso e soprattutto controproducente in termini di tempo. Come fare quindi? Semplice, realizzare uno scrip bash in grado di automatizzare tale attività.

Ecco il codice:

#!/bin/bash

mysql -u utente -ppassword -e 'show databases' | grep -v Database | grep -v information_schema | grep -v mysql > databases

while read line
do

        command="mysql -u utente -ppassword -e 'use $line; show tables'"
        eval $command | grep -v "Tables_in_*" > tables_$line

done < databases

num_databases=`cat databases | wc -l`

ls | grep "tables_*" > table

num_tables=`cat table | wc -l`

if [ $num_databases -eq $num_tables ];then

        for i in {1..19}
        do
                database=`cat databases | sed -n "$i p"`
                echo ".......tables for $database......."
                cat tables_"$database" | while read line
                do
                        echo $line
                        command="mysql -u utente -ppassword -e 'use $database; select * from $line LIMIT 1'"
                        echo "....Executing $command...." >> result
                        eval "$command" | tee -a result
                        echo "....End executing $command ...." >> result

                done
                echo ".......end tables for $database......."
        done

fi

Per prima cosa identifico tutti i database definiti su MySQL, escludendo quelli di default (mysql ed information_schema), formattando opportunamente l’output ( grep -v Database) e salvando i risultati all’interno del file databases:

mysql -u utente -ppassword -e 'show databases' | grep -v Database | grep -v information_schema | grep -v mysql > databases

Successivamente listo le tabelle presenti in ciascun database mediante un ciclo while che legge, riga per riga, il contenuto del file databases e, per ognuna, esegue il comando show tables:

while read line
do

        command="mysql -u utente -ppassword -e 'use $line; show tables'"
        eval $command | grep -v "Tables_in_*" > tables_$line

done < databases

Da notare come la stringa $command venga dapprima definita in modo tale da contenere il valore della variabile $line e successivamente venga eseguita sottoforma di comando mediante la direttiva eval.

Verifico, quindi, che il numero dei file tables_$line sia uguale al numero di database (e che quindi vi sia la ragionevole certezza che per ciascun database sia stato individuato il nome delle tabelle di cui è composto):

num_databases=`cat databases | wc -l` ls | grep "tables_*" > table 

num_tables=`cat table | wc -l` 

if [ $num_databases -eq $num_tables ];then

Infine, per ciascuna tabella di ciascun database, lancio la query mysql -u utente -ppassword -e ‘use $database; select * from $line LIMIT 1’, definita come stringa ed eseguita mediante eval (come visto in precedenza):

for i in {1..19}
        do
                database=`cat databases | sed -n "$i p"`
                echo ".......tables for $database......."
                cat tables_"$database" | while read line
                do
                        echo $line
                        command="mysql -u utente -ppassword -e 'use $database; select * from $line LIMIT 1'"
                        echo "....Executing $command...." >> result
                        eval "$command" | tee -a result
                        echo "....End executing $command ...." >> result

                done
                echo ".......end tables for $database......."
        done

fi

Da notare come, nel mio caso, vi sia un totale di 19 database, per cui ho utilizzato un semplice ciclo for così definito:

for i in {1..19}

Nulla però vieta di usare come upper bound la variabile $num_databases o $num_tables.

Infine rendiamo eseguibile lo scrip (che ho denominato, per semplicità, my.sh):

[root@server ~]# chmod +x my.sh

ed eseguiamolo:

[root@server ~]# ./my.sh

E’ tutto, alla prossima.

Shrinking di un database Firebird

L’ABC dell’informatica ci ha insegnato che non esiste un solo tipo di file system. Infatti, oltre al file system utilizzato dal sistema operativo esiste anche il file system del DBMS, il network file system (ad esempio NFS e Microsoft DFS) e così via.

Bene, solitamente quando si cancellano N record da una o più tabelle del DB, il DBMS a cui l’istanza afferisce non rilascerà automaticamente al sistema operativo lo spazio appena liberato. Questo significa che il file che identifica il DB manterrà le propre dimensioni originarie, nonostante le operazioni di rimozione dei record.

Per quanto riguarda Firebird, l’unico modo per effettuare lo shrinking del database (ovvero la sua contrazione in termini di spazio occupato su disco) è eseguire il backup di quest’ultimo e successivamente ripristinarlo.

database, dbms, shrinking, disk space, restore, backup, gfix, gbak, garbage collection, firebird

Fortunatamente, Firebird ci mette a disposizione un valido strumento per tale scopo: gbak.

Per default tale applicativo effettuerà anche la garbage collection dei record duplicati (generati dai rollback delle transazioni), impiegando un certo lasso di tempo (in quanto trattasi di un’operazione abbastanza onerosa in termini computazionali). Per fare in modo che gbak non proceda con la garbage collection occorre usare la flag -G. Ad esempio:

server ~ # gbak -B nomedatabase.fdb nomedatabase.fbk
-v -y backup.log

eseguirà il backup del nostro DB, salvando le informazioni relative a tale operazione (-v -y) nel file backup.log, mentre:

server ~ # gbak -B -G nomedatabase.fdb nomedatabase.fbk
-v -y backup.log

eviterà di eseguire la garbage collection (che, solitamente, viene messa in atto automaticamente ad intervalli di tempo regolari, secondo quanto definito nel file di configurazione di Firebird).

Inoltre, è possibile effettuare la garbage collection in un secondo momento, utilizzando gfix:

server ~ # gfix -s nomedatabase.fdb

Una volta creato il file di backup (con estensione *.fbk), sarà necessario ripristinarlo. Il comando da utilizzare è il seguente:

server ~ # gbak -C nomedatabase.fbk nomedatabase.fdb
-v -y restore.log

Assegnamo i giusti permessi al file appena creato:

server ~ # chown firebird:firebird nomedatabase.fdb

riavviamo Firebird:

server ~ # /etc/init.d/firebird restart

ed abbiamo finito.

Alla prossima.

Firebird: cancellazione dei record duplicati

Potrebbe accadere che all’interno di un database siano presenti una o più tabelle non dotate di chiavi primarie. Questo significa, in soldoni, che è molto alto il rischio di ridondanza (aka duplicati) e quindi di inconsistenza.

Fortunatamente, Firebird ci mette a disposizione un metodo abbastanza semplice per la cancellazione dei duplicati. Esso si basa sull’uso di uno specifico campo (RDB$DB_KEY), il cui valore univoco viene assegnato automaticamente a ciascun record durante la fase di inserimento nel DB.

firebirdlogo.jpg

Inutile dire che preferisco di gran lunga l’uso del suddetto metodo rispetto a soluzioni alternative, in quanto esso si basa esclusivamente su di una query secca e quindi minimizza il rischio di errore. Infatti, se ad esempio avessi utilizzato le cosiddette prepared statements avvelendomi di un qualche linguaggio di alto livello che le supporta (come Java), avrei dovuto scrivere N righe di codice, incrementando la possibilità di incappare in bachi (ecco perchè preferisco sempre mettere in pratica il motto less is more).

Di seguito la query che ho utilizzato:

DELETE FROM TABLE1 t1
WHERE EXISTS (
SELECT 1 FROM TABLE1 t2
WHERE t1.COL1 = t2.COL1 and t1.COL2 = t2.COL2
AND t1.RDB$DB_KEY < t2.RDB$DB_KEY
);

Dove t1 e t2 sono gli alias associati alla medesima tabella, mentre COL1 e COL2 sono i campi che la dovrebbero identificare univocamente (la probabile chiave primaria).

Ora, per avere la certezza che tale query non cancelli più record del previsto, si dovrebbero coinvolgere tutti i campi della tabella. Però, quando il numero dei campi è piuttosto elevato (>15), risulta più conveniente identificare la presunta chiave primaria ed utilizzare solo i campi che concorrono a formarla.

In questo caso, prima di effettuare il DELETE vero e proprio è opportuno fare delle query di verifica.

Ad esempio, dapprima si contano tutti i record della tabella:

SELECT count(*) FROM TABLE1;

e successivamente si individuano i record duplicati:

SELECT COL1, COL2, count(*) FROM TABLE1 GROUP BY COL1, COL2 HAVING count(*) >= 2;

A questo punto il numero dei record a cancellazione avvenuta dovrebbe essere pari al numero restituito dalla prima query meno il numero restituito dalla seconda query.

Per avere la certezza che il numero di record rimossi sia identico alla risultante dell’operazione di differenza effettuata in precedenza si può eseguire la query:

SELECT count(*) FROM TABLE1 t1
WHERE EXISTS (
SELECT 1 FROM TABLE1 t2
WHERE t1.COL1 = t2.COL1 and t1.COL2 = t2.COL2
AND t1.RDB$DB_KEY < t2.RDB$DB_KEY
);

Se il numero di record restituiti è identico al numero dato dalla differenza, abbiamo una buona probabilità che la chiave primaria individuata sia quella giusta (almeno dal punto di vista quantitativo).

Alla prossima.

Oracle: modificare la password utente e rimuovere la scadenza delle password

Oracle è di gran lunga il DBMS più affidabile e performante che esiste in circolazione. Detto ciò, qualche giorno fa mi si è presentato un problema su alcune macchine virtuali: le password per accedere ai DB erano scadute o stavano per scadere. Cosa fare dunque?

Per prima cosa ho lanciato la console VMWare e successivamente ho aperto il tool SQL*Plus Instant Client.

oracle.gif

A questo punto mi sono loggato come utente system, digitando il comando:

connect system/password

Per cambiare la password ho invece utilizzato la query:

ALTER USER system IDENTIFIED BY nuovapassword;

Questo output:

User altered.

mi ha confermato che la password è stata effettivamente cambiata.

Ora, per quanto riguarda invece la policy di scadenza delle password, posso dirvi che disabilitarla è controproducente dal punto di vista della sicurezza. Se, però, le macchine utilizzate sono di test e soprattutto NON sono esposte all’esterno, la si può disabilitare tranquillamente digitando:

ALTER PROFILE DEFAULT LIMIT

PASSWORD_LIFE_TIME UNLIMITED;

Alla prossima.

Firebird: workaround per la conversione di un campo varchar(n) in smallint

Firebird è un DBMS abbastanza potente ma soprattutto leggero e versatile. Esso, però, presenta alcune limitazioni di non poco conto. Una su tutte è l’impossibilità di convertire un campo varchar(n) in smallint, nonostante tutti i record che si riferiscono al campo in questione siano degli interi positivi di una sola cifra.

firebird,field,type conversion,varchar,int,smallint,conversion error

Per intenderci, al primo tentativo di conversione mi sono beccato un errore del tipo:

Conversion error from string

Cosa fare dunque? Nelle FAQ di Firebird esiste una procedura, ma sinceramente ho preferito fare di testa mia. Per prima cosa ho creato un nuovo campo nella tabella di interesse, mediante la seguente query:

ALTER TABLE nometabella ADD nuovocampo SMALLINT;

A questo punto ho copiato il contenuto del campo varchar(n) all’interno del campo appena creato:

UPDATE nometabella SET nuovocampo = vecchiocampo;

Ho quindi modificato la posizione di nuovocampo, portandolo a fianco di vecchiocampo:

ALTER TABLE nometabella ALTER COLUMN nuovocampo POSITION x

dove x è, ovviamente, la nuova posizione.

Per maggiore sicurezza, ho provveduto a lanciare una query di verifica, in modo da identificare eventuali discrepanze tra il contenuto di nuovocampo e quello di vecchiocampo:

SELECT * FROM nometabella WHERE nuovocampo <> vecchiocampo

Poichè la suddetta query ha restituito come risultato 0 record, mi sono tranquillizzato ed ho eseguito la query:

ALTER TABLE nometabella ALTER COLUMN vecchiocampo TO vecchiocampo_bak

e quindi:

ALTER TABLE nometabella ALTER COLUMN nuovocampo TO vecchiocampo

In soldoni, non ho fatto altro che rinominare nuovocampo in vecchiocampo, ereditando quindi il tipo di dato (smallint).

Infine, ho eliminato vecchiocampo_bak:

ALTER TABLE nometabella DROP COLUMN vecchiocampo_bak

Semplice, no?

Alla prossima.

MySQL: identificare la tabella da cui provengono i dati ricavati mediante una UNION di due SELECT

Qualche giorno fa, mentre scrivevo del codice server-side per un piccolo CRM homemade, ho avuto la necessità di ricavare dei dati mediante la UNION di due SELECT. In particolare, il codice SQL che ho utilizzato è il seguente:

SELECT 'Entrata' AS NomeTabella, O.ID, O.NumeroOperazione, E.Descrizione, E.Tipo, O.Importo, O.DataInserimento, O.DataOperazione FROM entrata AS E, riguarda AS R, operazione AS O WHERE O.ID = R.IDOperazione AND R.IDEntrata = E.ID AND (E.Tipo = 'Cassa' OR E.Tipo = 'Banca') UNION SELECT 'Uscita' AS NomeTabella, O.ID, O.NumeroOperazione, U.Descrizione, U.Tipo, O.Importo, O.DataInserimento, O.DataOperazione FROM uscita AS U, riguarda AS R, operazione AS O WHERE O.ID = R.IDOperazione AND R.IDUscita = U.ID AND (U.Tipo = 'Cassa' OR U.Tipo = 'Banca') ORDER BY DataInserimento
mysql.jpg

Ora, come potete notare le due SELECT vengono applicate su diversi JOIN ed hanno come obiettivo quello di restituire alcuni informazioni importanti, quali il numero dell’operazione finanziaria, la descrizione dell’operazione stessa, il suo importo, ecc. Ovviamente, affinchè i risultati non venissero ripetuti più volte ho utilizzato solo l’operatore UNION (che prevede un DISTINCT implicito) senza la direttiva ALL. Inoltre, per distinguere la tabella di provenienza dei record selezionati, ho creato un ulteriore campo temporaneo, a cui è stato associato l’alias NomeTabella. Così facendo, la struttura della tabella risultante è diventata la seguente:

Nometabella, ID, NumeroOperazione, Descrizione, Tipo, Importo, DataInserimento, DataOperazione

Da quel momento è stato possibile distinguere piuttosto banalmente la tabella di appartenenza dei record visualizzati.

Bye.

NB: affinchè la UNION possa essere applicata è necessario che le due SELECT riguardino lo stesso numero (e tipologie compatibili) di campi.

 

Pillole MySQL – Parte 1

Esistono diversi strumenti che facilitano la vita ai programmatori/DBA. Uno su tutti è certamente PHPMyAdmin, che con la sua interfaccia user-friendly consente di effettuare modifiche sul database in maniera semplice ed intuitiva.

Spesso, però, accade che sia disponibile solo l’interfaccia a linea di comando del nostro DBMS, ragion per cui è bene ricordare alcuni comandi utili:

USE nomedb;

ci consente di selezionare il database.

SHOW TABLES;

ci consente di visualizzare tutte le tabelle relative al database selezionato;

DESCRIBE nometabella;

ci permette di visualizzare la struttura della tabella specificata;

Un altro comando utile riguarda il reset dei campi di auto-increment. Per fare ciò basta digitare:

ALTER TABLE nometabella AUTO_INCREMENT = 1;

Ovviemente, per visualizzare il contenuto di una tabella, per cancellare determinate righe o per modificare la struttura della tabella stessa (o di una sua entry), basta usare la pura e semplice sintassi SQL.

Ad esempio:

SELECT * FROM nometabella;

visualizza tutte le righe di una tabella;

DELETE FROM nometabella;

cancella tutti i record contenuti in una tabella;

DELETE FROM nometabella WHERE ID = '1';

cancella dalla tabella il record avente ID pari a 1;

DROP TABLE nometabella;

cancella la tabella;

INSERT INTO nometabella (campo1, campo2) VALUES ('valore1', 'valore2');

inserisce nella tabella i valori “valore1” e “valore2” rispettivamente per “campo1” e “campo2”;

UPDATE nometabella SET campo1 = 'valore1', campo2 = 'valore2' WHERE ID = '1';

aggiorna il contenuto di “campo1” e “campo2” rispettivamente a “valore1” e “valore2” per la entry che possiede ID pari a 1.

Inutile dire che quelli da me elencati sono solo alcuni dei comandi che MySQL ci mette a disposizione. Morale della favola: meglio non abusare di PHPMyAdmin 😀

A presto.

Effetture il dump di un database sotto Linux con MySQL

Il dump di un database non è altro che un file di testo in cui sono contenute delle istruzioni SQL. Tali istruzioni hanno come scopo quello di riprodurre fedelmente il database esportato, in modo che possa essere caricato su un altra macchina continuando a funzionare correttamente.

logo-mysql.jpg

 

Per effettuare il dump del database MySQL direttamente da linea di comando occorre digitare (da shell):

mysqldump nome_del_database -u root -p > nome_del_file_dump.pl

dove la flag -u indica l’utente (ovvero root), la flag -p richiede l’inserimento della password di root ed il simbolo > indica che il risultato del comando dovrà essere salvato all’interno del file di destinazione (nome_del_file.pl).

Successivamente, per importare sulla macchina di destinazione il file appena creato è necessario usare i seguenti comandi (sempre da shell):

mysqladmin drop nome_del_database -u root -p

mysqladmin create nome_del_database -u root -p

mysql nome_del_database -u root -p < nome_del_file_dump.pl

Il primo comando ci consente di eliminare eventuali database già presenti nella macchina di destinazione e che possiedono lo stesso nome di quello che stiamo per importare; il secondo comando, invece, ci permette di creare un database vuoto. Infine, l’ultimo comando serve all’importazione vera e propria delle informazioni.

Il post termina qui, a presto.

Installazione e sicurezza di base per MySQL

MySQL è uno dei DBMS più efficienti in circolazione, oltre a rappresentare una valida alternativa ai sistemi proprietari di gestione dei database (vedi Oracle). Esso, ovviamente, è un software libero e proprio questa sua caratteristica comporta numerosi vantaggi quali la possibilità di poterlo adattare completamente alle proprie necessità, sempre nei limiti della licenza GPL, oppure la grande mole di guide ed howto (ufficiali e non) reperibili in rete.

Vediamo adesso come installare tale DBMS e quali sono i comandi per garantire una sicurezza di base.

Apriamo dunque la nostra shell e digitiamo:

nightfly@nightbox:~$ sudo apt-get install mysql-server

Appena l’installazione verrà completata, il demone mysql verrà attivato in automatico. Potete verificare che ciò sia effettivamente avvenuto digitando:

nightfly@nightbox:~$ ps aux | grep mysql

In caso contrario, possiamo avviare il demone manualmente mediante il seguente comando:

nightfly@nightbox:~$ sudo /etc/init.d/mysql start

Ora passiamo alla sicurezza di base. E’ bene notare che l’amministratore di mysql è identificato come root, da non confondere con l’utente root della nostra linux box. Impostiamo dunque la password per l’admin:

nightfly@nightbox:~$ sudo mysqladmin -u root password 'password'

Definiamo la password anche per l’utente root che si collega da locale: 

nightfly@nightbox:~$ sudo mysqladmin -u root -h localhost password 'password'

Infine eliminiamo gli utenti anonimi (si tratta di una semplice istruzione in SQL):

nightfly@nightbox:~$ mysql -u root -p mysql
 DELETE FROM user WHERE User=' ';
 FLUSH PRIVILEGES;

dove la flag -p indica il database sul quale si vuole operare (mysql nella fattispecie). 

La mini guida termina qui, a presto!