Visualizzare ed eliminare foreign keys in MySQL

 13/10/2013

MySQL

Con l’avvento di InnoDB su MySQLci si trova sempre più spesso a dover trattare tabelle con chiavi esterne. A volte per modificare una tabella si rende indispensabile eliminare le chiavi prima di eseguire la modifica, chiavi che però non sono elencate insieme alla struttura della tabella. Per estrarre quindi la lista delle chiavi di una tabella la query da eseguire è quindi la seguente:

select * from KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '<em>database_name</em>' AND TABLE_NAME = '<em>table_name</em>'

Come si può intuire, al posto di database_name si dovrà inserire il nome del database in cui risiede la tabella, e al posto di table_namesi dovrà scrivere il nome della tabella dalla quale estrarre le chiavi.
Una volta ottenuta la lista di foreign key, per elimare una chiave il codice sql da eseguire è:

ALTER TABLE <em>table_name</em> DROP FOREIGN KEY <em>foreign_key_name</em>

Table_name è come sopra il nome della tabella in cui risiedono le chiavi esterne, mentre foreign_key_name è il nome della chiave ottenuto dalla lista estratta con la query precedente.

Eseguire un’applicazione Django su ubuntu server con nginx, gunicorn, upstart e mysql (DUNG stack)

 27/09/2012

Gunicorn

Oggi torno a scrivere sul blog per raccontare come eseguire il deploy di un’applicazione Django su ubuntu server con Nginx come web server, Gunicorn come application server WSGI e MySQL come DBMS.

Inizialmente per ospitare un’applicazione scritta con il framework Django utilizzavo un classico stack in stile LAMPP (Linux + Apache + mod_wsgi). Successivamente per cercare di limitare l’utilizzo di ram ho scoperto (grazie al blog degli ingegneri di instagram) lo stack DUNG che rappresenta la configurazione con nginx + gunicorn + ubuntu.

Non appena si iniziano le configurazioni si comprendono la semplicità e la velocità di messa a punto del nuovo stack. Nginx usa file di configurazione scritti con una sintassi simile ad un linguaggio di programmazione, molto più semplici da comprendere e da scrivere per chi ha poca dimestichezza con queste cose.

Per prima cosa installiamo nell’ordine mysql e nginx, successivamente installiamo django.

Fatto ciò, tramite easy_install possiamo installare Gunicorn:

sudo easy_install gunicorn

A questo punto dobbiamo configurare nginx come proxy server per le richieste: tutto ciò che riguarda i file statici (immagini, css, js, ecc…) verrà servito direttamente da nginx mentre le richieste inerenti il codice python dovranno essere girate al server WSGI (gunicorn nel nostro caso) che le eseguirà e restituirà la pagina risultante.

Come per apache i file di configurazione dei vari siti sono in /etc/nginx/sites-available (invece di /etc/apache/sites-available), per cui creiamo il file di configurazione per la nostra applicazione django in questo modo:

upstream project {
        server 127.0.0.1:8200 fail_timeout=0;
        server 127.0.0.1:8201 fail_timeout=0;
}

server {
        listen 80;
        server_name mydomain.com www.mydomain.com;
        access_log /var/log/nginx/mydomain_access.log;
        error_log /var/log/nginx/mydomain_error.log;

        root /srv/django_srv/mydomain/app;

        location / {
                proxy_set_header Host $host;
                if (!-f $request_filename){
                        proxy_pass http://project;
                        break;
                }

        }
        location /upload  {
                alias /srv/django_srv/mydomain/uploads;
                }
        location /static  {
                alias /srv/django_srv/mydomain/app/_statics;
        }
        location /admin/media {
                alias /opt/Django-1.3.1/django/contrib/admin/media;
        }
}

Come potete vedere nel blocco di configurazione upstream project a questo punto dobbiamo configurare gunicorn in modo da farlo rispondere sulla porta 8200 e 8201. In questo modo quando caricheremo aggiornamenti del nostro progetto e dovremmo riavviare gunicorn, il sito non sarà mai irraggiungibile, inoltre se il processo gunicorn dovesse crollare per qualche motivo ce ne sarà sempre un altro disponibile. Gli alias che vedete sono per i file uploadati dagli utenti tramite l’applicazione, i file statici del sito e i file statici per il backend amministrativo (per questi ultimi dovete dare il path alla cartella di installazione di Django). Dopo aver salvato questo file create un link simbolico nella cartella effettiva dei siti di nginx:

ln -s /etc/nginx/sites-available/mydomain.com /etc/nginx/sites-enabled/mydomain.com

Per avviare gunicorn, essendo su ubuntu, utilizziamo upstart il nuovo sistema per avviare tasks e servizi all’avvio del sistema. Creiamo il file /etc/init/gunicorn_8200.conf e copiamo le seguenti istruzioni:

description "Gunicorn Django on 127.0.0.1:8200"
start on runlevel [2345]
stop on runlevel [06]
respawn
respawn limit 10 5
exec /usr/local/bin/gunicorn_django --bind=127.0.0.1:8200 --workers=2 --access-logfile=/var/log/gunicorn/8200_access.log --error-logfile=/var/log/gunicorn/8200_error.log --daemon /srv/django_srv/mydomain/app/settings.py

Con queste istruzioni diciamo al processo gunicorn di ripartire in caso di crash (respawn) di rispondere all’indirizzo 127.0.0.1 sulla porta 8200 (–bind) di partire come demone (–daemon) e gli diamo i percorsi dei file di log e del file di settings della nostra applicazione.

A questo punto riavviamo il server per verificare che anche upstart funzioni:

shutdown -r now

Il gioco è fatto e la nostra applicazione è pronta per essere utilizzata. I file del progetto django (in base a questa configurazione) devono risiedere in /srv/django_srv/mydomain/app, mentre i file di upload sono in /srv/django_srv/mydomain/uploads per fare in modo che non siano all’interno dell’applicazione e che siano facilmente backuppabili ;)

Spero di essere stato esaustivo, di sicuro con questa configurazione vi potrete accorgere del notevole risparmio di ram rispetto ad un classico stack che utilizza apache come webserver.

Preparare un web server per django su ubuntu server 10.04

 21/11/2010

Poco tempo fa ho scoperto il favoloso mondo di Django, un web framework che consente di scrivere meno codice e creare applicazioni python davvero molto efficienti.

Oltre ad essere un framework che implementa l’MVC(Model View Controller), la caratteristica che mi ha attratto maggiormente è una fantastica funzionalità di ORM(Object Relational Mapping).. cosa significa? In poche parole nel file che implementa il “Model” (models.py) dovrete dichiarare gli oggetti che verranno trasformati in tabelle del vostro database, al resto penserà django. Infatti una volta collegato un db e scritte le classi del modello, con la funzione syncdb verranno create le tabelle residenti sul db. Vi rimando però al tutorial ufficiale per capire davvero qualcosa in più su questo fantastico framework…

In questo post vi farò vedere i pochi e semplici passi per configurare un web server adatto ad ospitare un’applicazione django. Ovviamente il server linux girerà grazie ad ubuntu server ed in particolare nella versione LTS(long term support) 10.04. Salterò ovviamente i passi dell’installazione vera e propria del sistema operativo (anche perchè ormai è a prova di win-user :-P ).

Partendo da un sistema pulito, su cui non è stato installato nessun componente, dovremo per prima cosa assicurarci che il sistema sia aggiornato:

sudo apt-get update
sudo apt-get dist-upgrade
sudo apt-get upgrade

Fatto ciò possiamo procedere con l’installazione dei componenti necessari a mettere in piedi un server web, il che equivale ovviamente ad installare il buon vecchio Apache:

sudo apt-get install lamp-server^

In questo modo installeremo tutti i pacchetti per avere un server LAMP ovvero con apache come detto, e con mysql per la parte di database (verrà installato anche php5 per poter utilizzare phpmyadmin, l’interfaccia per la gestione da web di mysql)

Attesi i dovuti tempi per lo scaricamento, l’installazione e la configurazione dei vari pacchetti (vi verrà chiesta la password per mysql) dovreste avere il vostro server web funzionante (potete testarlo collegandovi da un qualsiasi browser nella rete all’indirizzo ip del server es: http://192.168.0.1 ).

Bene, ora manca un ultimo componente per avere il server pronto: il mod_wsgi ovvero il modulo apache per l’interpretazione del codice python:

sudo apt-get install libapache2-mod-wsgi

Ora non ci resta che installare il driver per fare comunicare python e mysql, che come detto sarà per comodità il nostro dbms:

sudo apt-get install python-mysqldb

Ora possiamo procedere al download e installazione del framework dal sito ufficiale di django(vi risparmio il “wget”, mi sembrava troppo da nerd, ma si può fare ;-) ). Trasferite il tar.gz sul server se non è già lì, e scompattatelo (tar -xvzf nomefile.tar.gz per i newbie). Ora entrando nella cartella che avete ottenuto dalla scompattazione potete installare django:

sudo python setup.py install

Vi consiglio spassionatamente di installare la release ufficiale di django e non quella dagli archivi ubuntu per un puro fatto di aggiornamento della versione, infatti sul canale ufficiale sarete sicuri di avere l’ultima stable-release mentre ubuntu è sempre più indietro in queste cose…

Bene adesso abbiamo tutto ciò che ci serve(r) (battuta di bassissimo umorismo, non ci fate caso, è l’età :-P ).

Procediamo con la creazione del file python che darà le direttive al mod_wsgi sui percorsi della nostra applicazione, nella cartella del nostro progetto, creiamo il file django.wsgi:

vim /home/user/py-srv/applicazione1/django.wsgi

Ipotizzando che la nostra applicazione risieda in /home/user/py-srv/applicazione1 …

Il file dovrà contenere il seguente codice:

import os
import sys

sys.path.append('/home/user/py-srv/applicazione1')

os.environ['PYTHON_EGG_CACHE'] = '/home/user/py-srv/.python-egg'
os.environ['DJANGO_SETTINGS_MODULE'] = 'settings'

import django.core.handlers.wsgi
application = django.core.handlers.wsgi.WSGIHandler()

Ora dobbiamo solamente dire ad apache quale sarà la cartella della nostra applicazione. Per fare ciò editiamo il file default nella cartella sites-available di apache (prima però facciamo una copia del file per sicurezza ;-) ) :

sudo cp /etc/apache2/sites-available/default /etc/apache2/sites-available/default.old
sudo vim /etc/apache2/sites-available/default

Il file dovrà contenere le seguenti configurazioni:

<VirtualHost *:80>
   DocumentRoot /home/user/py-srv/applicazione1/public_html

   WSGIScriptAlias / /home/user/py-srv/applicazione1/django.wsgi
   <Directory /home/user/py-srv/applicazione1>
      Order allow,deny
      Allow from all
   </Directory>

   Alias /favicon.ico /home/user/py-srv/applicazione1/public_html/favicon.ico
   Alias /images /home/user/py-srv/applicazione1/public_html/images
   Alias /static /home/user/py-srv/applicazione1/public_html/static

   ErrorLog /home/user/py-srv/applicazione1/logs/error.log
   CustomLog /home/user/py-srv/applicazione1/logs/access.log combined
</VirtualHost>

Come potete immaginare la cartella “public_html” sarà quella che conterrà i file html pubblici, ovvero i file del template della vostra applicazione.

Bene, se avete fatto tutto nel modo giusto, vi basterà collegarvi all’indirizzo ip che avete già usato prima per testare apache, e vedrete come per magia che funzionerà tutto!

(non funziona? non fate come me che avevo lasciato il vecchio path nel file settings.py, e ci ho perso mezz’ora per accorgermene :-P )

Buon divertimento!

Differenze tra il tipo char e il varchar in MySQL

 30/05/2010

Prosegue il mio lavoro insieme a MySQL, ormai lo sto usando intensamente in molti programmi non solo in php, ma anche nella programmazione in java.

Usandolo così massicciamente sto continuando a scoprire cose interessanti. Probabilmente quella di cui vi parlo oggi, non è una novità per qualcuno, ma credo sia bene chiarirla per chi non lo sapesse.

Qualche giorno fa stavo lavorando su un database che non ho creato io e mi sono imbattuto in un campo CHAR. Subito non ci ho fatto caso, ma poi mi è venuto in mente che di solito quando devo dichiarare un campo stringa, uso il tipo VARCHAR. Mi sono così documentato un po’, cercando su google ho trovato diverse spiegazioni su forum, ma come sapete di quello che viene detto sui forum è sempre meglio non fidarsi troppo. Ho fatto quindi direttamente riferimento alla guida ufficiale MySQL (in inglese) e ho scoperto quello che mi interessava.

Innanzitutto prima di MySQL 5.0.3 sia il tipo char sia il varchar potevano essere dimensionati da 0 a 255 caratteri. Da MySQL 5.0.3 in poi il tipo varchar supporta lunghezza da 0 a 65535 caratteri. Oltre a questa differenza di dimensionamento c’è un’altra sostanziale differenza: quando dimensionate un campo con il tipo char, ad esempio char(4) verrà effettivamente occupato spazio per 4 caratteri, anche se il valore che inserirete nel campo sarà di un solo carattere. Lo spazio rimanente verrà riempito con degli spazi. A differenza del char, il varchar invece utilizza solamente lo spazio necessario, richiesto dal valore inserito nel campo più un byte (non sono riuscito a trovare il motivo, ma credo sia il famoso “tappo” in stile C).

Riepilogando quindi:

Valore CHAR(4) Spazio richiesto
 VARCHAR(4) Spazio richiesto
'' '    ' 4 bytes '' 1 byte
'ab' 'ab  ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

Per finire, come si può vedere nell’ultima riga dell’esempio, per tutti e due i tipi, se il valore supera la lunghezza effettiva dichiarata, esso verrà troncato.

In sostanza se sapete che nel campo che state dichiarando dovrete inserire valori sempre della stessa lunghezza, senza eccezioni, potrebbe essere intelligente dichiararlo char mentre invece se, come succede nella stragrande maggioranza dei casi, il campo che state dichiarando conterrà stringhe generiche, allora vi converrà utilizzare il tipo varchar.

Servire dinamicamente le immagini con php, Mysql e Imagick

 10/04/2010

Nessuno se ne sarà accorto ed effettivamente è un po’ impossibile notarlo se uno non lo sa ;-)

Ormai da una settimana però ho messo in funzione uno strumento “self-made” per la gestione delle immagini del mio blog.

Spulciando un po’ più attentamente google webmaster tools tra le funzioni sperimentali ho trovato un prestazioni del sito…

In questa sezione google presenta un’analisi delle prestazioni del proprio sito (in termini di tempo e kilobyte di dati) e fornisce consigli su come ridurre i tempi di caricamento.

Siccome il mio hosting mi limita lo spazio web, utilizzavo un piccolo escamotage per caricare le foto del blog: le caricavo su picasa e sfruttavo i server di google per fornirle. Webmaster tools mi consigliava però di ridurre al minimo le ricerche DNS e infatti molto spesso le foto venivano reperite da diversi server di picasa. Tutto ciò contribuiva ad incrementare il tempo di caricamento del sito per cui ho preso la decisione di darmi da fare :-P

Come detto, il mio hosting mi limita lo spazio web, ma non ho limiti per quanto riguarda il db MySQL, per cui ho deciso di realizzare una mini-applicazione in php per salvare le immagini su database e servirle dinamicamente. Tutto questo con due funzioni che sfruttano la classe Imagick (installata sul server dove risiede il mio dominio).

Il concetto si basa in sostanza su tre pagine.

La prima sarà un form per far scegliere all’utente l’immagine da uploadare:

<form action="....." method="POST" enctype="multipart/form-data">.....</form>

La seconda, una pagina che riceve il parametro con l’immagine e la salva nel database:

il file è contenuto nell’array

$_FILES['image']['tmp_name']

dove image è il nome del parametro ricevuto via POST.

Qui ci servirà la prima funzione che sfrutterà la classe Imagick:

        public static function prepare_image($file){
		$img=new Imagick();
		$handler=fopen($file,'r');
		$ret=null;
		if(!feof($handler)){
			$fstr=fread($handler,filesize($file));
			$img->readImageBlob($fstr);
			/*Richiamo il metodo resize*/
			$img->setFormat('jpg');
			$img->setImageCompression(Imagick::COMPRESSION_JPEG);
			$img->setImageCompressionQuality(100);
			$ret=addslashes($img->getImageBlob());
			$img->destroy();
		}
		fclose($handler);
		return $ret;
	}

Dove la variabile $file sarà il contenuto dell’array di cui parlavamo prima. A questo punto potremo salvare il valore restituito dalla funzione nel database (il campo dovrà essere di tipo blob o meglio ancora longblob).

Fatto ciò possiamo passare alla terza pagina, quella che servirà l’immagine letta dal db.

Quest’ultima dovrà ricevere come parametro l’id dell’immagine da leggere sul db. La query la lascio fare a voi, io vi spiego la seconda funzione che ci servirà ;-) (molto più “leggera” della prima):

        public static function get_image_from_blob($blob){
		 $img=new Imagick();
		 $img->readImageBlob($blob);
		 $img->setFormat('jpg');
		 return $img;
	}

In questo caso il parametro $blob sarà il valore blob letto dal db, e la funzione ci restituirà l’immagine. A questo punto non vi resta che stampare l’immagine:

header('Content-Type: image/jpeg);
echo $img;

Abbastanza semplice no?
E dopo la prima settimana di utilizzo lascio a voi valutare i risultati ;-) :

Ovviamente tramite Imagick potete anche fare il resize dinamico delle immagini, ma non vorrei confondervi troppo le idee :-P

Un tool open source per la progettazione dei database

 24/12/2009

Ogni buon programmatore che si rispetti sa che non si può creare un database dal nulla. Proprio i database sono alla base di qualsiasi software degno di nota, e sono indispensabili per la realizzazione di un sito web.

Da buon fanatico di progetti open source, ma soprattutto dell’ambiente php-mysql per la creazione di web application mi sono dato da fare nel mio sport preferito e ho trovato un tool davvero interessante.

Si chiama MySQL workbench e proviene dagli stessi laboratori dai quali ci viene fornito uno dei DBMS open source più conosciuti al mondo. E’ da qualche mese che lo uso ogni volta in cui ho bisogno di realizzare un db, a lavoro o a casa e lo trovo davvero utile e funzionale. Che cos’è workbench? Semplice! Un sw che consente di progettare un database, partendo dalla realizzazione dello schema E-R e risparmiando molto lavoro a noi poveri programmatori.

In sostanza basta disegnare lo schema del database inserendo tabelle, chiavi primarie, chiavi esterne, indici, trigger, viste e chi più ne ha più ne metta, e workbench farà per noi tutto il resto. Una volta disegnato lo schema infatti, sarà possibile esportare la struttura del db creato e reimportarla nel nostro DBMS. Io uso abitualmente MySQL e workbench mi permette di esportare in un file .sql le query di creazione della mia base dati, pienamente compatibili con questo DBMS, e di creare con una import il database vero e proprio.

Esiste in due versioni, la community e la standard, la prima rilasciata gratuitamente sotto licenza GPL mentre la seconda a pagamento (e con alcune funzionalità in più) al costo di 79$ per svillupatore per anno. Ovviamente non mi sono potuto permettere di acquistare la versione standard, ma la community svolge il suo egregio lavoro in maniera impeccabile.

Dimenticavo, workbench è multipiattaforma ossia esiste nella versione per Linux (comodo pacchetto debian autoinstallante, o rpm), nella versione per Mac OS X e nella versione per Windows in modo che nessuno possa lamentarsi e tutti possano progettare in tutta serenità i propri database sul proprio sistema preferito.

Direi che non vi resta che provarlo!
[ad]

Indici Full Text in MySQL

 23/10/2009

Post da nerd, astenersi perditempo… :-P

[NERD MODE ON]

Alcuni giorni fa sul lavoro mi sono cimentato in un nuovo esperimento.

Tutto è partito dal problema di dover indicizzare dei contenuti, sui quali poi eseguira una ricerca “generica” in stile google (ovvero un unico campo che riceve un insieme di parole, che possono corrispondere a diversi campi del db).

Bene non voglio stare qui a spiegare nei dettagli la soluzione che abbiamo deciso di adottare, ma in sostanza mi sono ritrovato con un campo testuale con dentro tutte le parole che corrispondono ad una chiave.

Ok, e ora? L’idea iniziale è stata quella di eseguire una query con ” LIKE ‘%parola%’ ” in OR con altre LIKE simili, per ogni parola inserita dall’utente. Una volta implementata, tutto funzionava (avevo 3 record in quella tabella). Siccome per scrupolo avevo anche implementato un timer che misurasse il tempo di esecuzione, ho provato a fare un test pesante… Con python mi sono creato un programma per scrivere su file qualche centinaia di query di inserimento su quella tabella, lo eseguo e popolo il db. Ora ci sono 1000 record (non sono ancora abbastanza ma rende già l’idea), rilancio la ricerca e il timer segna 0,3 secondi. Buono no? sì ma sono 1000 record, se mai i record dovessero essere 100K o ancora di più? Mmm non mi soddisfa questa cosa. *Googleing*….*Googleing*….*Googleing* FULL TEXT INDEX

Cos’è? In MySQL è possibile definire su un campo di una tabella, un indice FULL TEXT, ovvero una struttura che indicizza in maniera ottimale tutte le parole presenti in uno o più campi. Ci sono alcune restrizioni però. Quella più importante è che la tabella non può essere creata con il motore InnoDB (il più comune perchè supporta query multiple in una singola transazione). Inoltre le stringhe da cercare con le query dovranno avere più di 3 caratteri (altrimenti bisogna andare a modificare le impostazioni di MySQL, cosa impossibile se non si ha accesso al server, e comunque relativamente complicata da fare).

Detto ciò i passi da fare sono i seguenti:
Definiamo la tabella e l’indice:

CREATE TABLE test (
id INT UNSIGNED   AUTO_INCREMENT NOT NULL PRIMARY KEY,
keywords TEXT,
[keywords2] VARCHAR(255),
FULLTEXT (keywords[,keywords2])
);

[Tra le parentesi quadre ho messo il secondo campo, spero si capisca che è opzionale…]

Fatto questo e dopo aver popolato la tabella, possiamo provare la nostra ricerca con la seguente query:

SELECT * FROM test WHERE MATCH(keywords) AGAINST('pippo')

In questo modo andremo a dire a MySQL di cercarci l’esatta parola ‘pippo’ all’interno del campo keywords.

Una cosa che non si trova facilmente nelle guide che ho seguito (ho fatto perciò riferimento alla documentazione ufficiale di MySQL) è che così facendo si effettua una ricerca in linguaggio naturale, che è ottimale per indicizzare frasi di senso (più o meno) compiuto. Il “difetto” se può definirsi così, è che con questo tipo di ricerca alcune parole non verranno trovate, infatti MySQL utilizza un array di stopwords che verranno saltate a piè pari dalla ricerca. Per effettuare una ricerca su tutte le parole e utilizzando operatori logici (ad esempio “+” significa OR mentre “-” esclude la parole che segue questo simbolo) bisogna effettuare una ricerca IN BOOLEAN MODE, più lenta, ma più efficace. La nostra query dovrà quindi essere modificata in:

SELECT * FROM test WHERE MATCH(keywords) AGAINST('*pippo*,*pluto*' IN BOOLEAN MODE)

In questo modo si effettua una ricerca all’interno del campo keywords, delle parole che contengono al loro interno “pippo” o “pluto”. Il carattere “*” funziona un po’ come il “%” nelle LIKE.

A questo punto manca un ultima chicca. La funzione MATCH (..) AGAINST(..) ritorna un valore che rappresenta la pertinenza delle parole cercate nel campo. Nel BOOLEAN MODE sarà il numero di parole trovate nel campo, mentre con la ricerca in linguaggio naturale ci sarà un algoritmo che calcola la pertinenza con delle regole strane ma che funzionano ;)

Quindi per sfruttare al massimo questo algoritmo bisognerà scrivere la query con:

SELECT *, MATCH(keywords) AGAINST('*pippo*,*pluto*' IN BOOLEAN MODE) as pertinenza FROM test WHERE MATCH(keywords) AGAINST('*pippo*,*pluto*' IN BOOLEAN MODE) ORDER BY pertinenza DESC

Così facendo ordiniamo i risultati dal più pertinente, a quello meno.

Risultato? sui miei 1000 records il tempo si è dimezzato, nel caso peggiore… Risultato accettabile no? :-)

Non vi resta che provare per credere, buon divertimento!

[NERD MODE OFF]
[ad#ad-1]