-+  Associazione
-+  Documenti
-+  Eventi
-+  Community
-+  Blog
-+  Link

Ottobre 2013

Dom Lun Mar Mer Gio Ven Sab
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    

Cerca






 

« IPW2006 - Ecco la nuova locandina! | Home | 80! »

SQL::Abstract per non scrivere più (troppo) SQL
19.06.06

Non saprei stimare quanti statement SQL ho scritto nella mia vita di programmatore. Mischiare codice SQL all'interno del proprio linguaggio di programmazione, qualunque esso sia, lo rende meno leggibile, più brutto, e - specialmente se le query sono complesse - particolarmente inelegante. Anche programmando secondo il paradigma MVC (caldamente raccomandato in alcuni casi, come le applicazioni web) si finisce semplicemente con lo spostare il codice SQL nel modello, mentre forse la scelta migliore sarebbe quella di liberarsene del tutto. Il problema è che è necessario interrogare il database, quindi non è possibile rimuovere il codice SQL; si può, tuttavia, inserire qualcos'altro al suo posto. Vediamo cosa, ovviamente dal punto di vista del programmatore Perl.

Esistono moduli che permettono l'astrazione dal linguaggio SQL generando codice a partire da una struttura dati. Ciò consente di utilizzare strutture arbitrariamente complesse per generare query arbitrariamente complesse senza scrivere una riga di codice database-related all'interno del proprio programma Perl. È opportuno notare che ciò è diverso da quello che fanno gli ORM (Object Relational Mappers): questi ultimi astraggono il database in oggetti, mentre qui si parla di astrazione di query SQL. Spesso, comunque, un buon ORM (come DBIx::Class) fornisce anche l'astrazione delle query, di solito utilizzando uno dei moduli disponibili su CPAN. Probabilmente vorrete utilizzare un ORM, ma questa è un'altra storia.

In questo breve articolo verrà trattato SQL::Abstract, che tra l'altro è quello utilizzato da DBIx::Class). È piuttosto semplice imparare come scrivere semplici query con questo modulo, e con un po' di ragionamento anche quelle complesse possono essere espresse sotto forma di strutture dati Perl. Se per esempio si ha:

my $query = "SELECT users.name, users.phone, users.email
    FROM users
    WHERE users.id = 1 OR users.id = 2
    ORDER BY id
";
my $sth = $dbh->prepare($query) or die $dbh->errstr;
$sth->execute() or die $dbh->errstr;

utilizzando SQL::Abstract si può scrivere:

my $sql = SQL::Abstract->new();

my ($query, @bind) = $sql->select(
    'users',
    [ 'name', 'phone', 'email' ],
    { id => [ 1, 2 ] },
    [ 'id' ],
);
my $sth = $dbh->prepare($query) or die $dbh->errstr;
$sth->execute(@bind) or die $dbh->errstr;

Si potrebbe obiettare che il codice è più lungo, ed in effetti un po' lo è (anche se la formattazione contribuisce a ciò). Esso è tuttavia molto più chiaro: non c'è codice extra-Perl accanto ai nomi ed ai valori dei campi, e si può capire alla prima occhiata cosa fa la query. Il metodo select accetta 4 parametri: il nome della tabella su cui si intende lavorare; i nomi dei campi da ritornare; la where clause; l'opzionale ordinamento dei risultati. Come valore di ritorno si ottiene una stringa contentente la query, e l'array dei bound values da utilizzarsi al momento di chiamare execute.

La where clause è il luogo dove tutto avviene. È possibile utilizzare una struttura dati Perl per specificare le condizioni di cui si ha bisogno:

my $conds =    {
       id => [qw/ 1 2/]
    }

Si possono indicare condizioni arbitrariamente complesse:

my $conds =    {
    -or => [
        id => [qw/ 1 2/]
        -and => [
            name => 'Fatina buona',
            email => { '!=', 'joker@example.com' },
            phone => { 'like' => '%3456%' },
       ],
    ]
};

In questo caso le condizioni di "primo livello" sono contenute in un arrayref chiamato -or, il quale fa sì che SQL::Abstract le associ tra loro utilizzando l'operatore OR anziché l'AND che rappresenta il default. L'id deve assumere valore 1 oppure 2, come negli esempi precedenti; se ciò non accade, devono essere soddisfatte tutte le 3 condizioni all'interno dell'arrayref -and. Come si nota, la seconda e la terza condizione sono specificate utilizzando un arrayref anziché un valore singolo: ciò permette di specificare l'operatore da utilizzare nella comparazione (il default è '='). A questo punto dovreste essere in grado di ricavare da soli il codice SQL generato dalla struttura sopra riportata.

Le inner join sono semplici da implementare:

</pre> my ($query, @bind) = $sql->select( [ 'users', 'payments' ], # Arrayref con i nomi della tabelle [ 'user.name', 'payment.dsc' ], { 'users.id' => [ 1, 2 ] }, [ 'users.id' ], ); </pre>

Utilizzando una sintassi simile si possono specificare operazioni diverse da SELECT. Ad esempio, per inserire un record in una tabella si può scrivere:

my ($query, @bind) = $sql->insert(
    'users',
   [ 'Michele', 'my@email.com', '123 546 874' ],
);
$dbh->do($query, undef, @bind);

oppure - in maniera più chiara e scalabile - si può scrivere:

my ($query, @bind) = $sql->insert(
    'users',
    {
        name => 'Michele',
        email => 'my@email.com',
        phone => '123 546 874',
    },
);
$dbh->do($query, undef, @bind);

Questa sintassi garantisce che, al momento di rileggere il codice, l'operazione esatta che esso compie sia comprensibile all'istante. Le istruzioni UPDATE sono simili alle INSERT, ma di solito è desiderabile indicare una where condition:

my ($query, @bind) = $sql->update(
    'users',
    {
        name => 'Michele',
        email => 'my@email.com',
        phone => '123 546 874',    # Numero di telefono di default
    },
    {
        id => 4,     # Aggiorna solo l'utente il cui ID è 4
    }
);
$dbh->do($query, undef, @bind);

Come si nota, è anche più semplice inserire i commenti. Infine, si può cancellare un record:

my ($query, @bind) = $sql->delete(
    'users',
    {
        name => { 'like' => 'M%' },    # Cancella tutto ciò il cui nome inizia per 'M'
    },
);
$dbh->do($query, undef, @bind);

Facile, eh? Si può anche ottenere una where clause da utilizzarsi al di fuori di SQL::Abstract. Ciò non è normalmente utile (perché si dovrebbe voler scrivere una parte di query a mano?), ma permette di effettuare SELECT multi-tabella con join diverse dalla inner (SQL::Abstract al momento supporta solo quest'ultima, come mostrato in precedenza) continuando ad utilizzare questo modulo per costruire la where clause (il che è caldamente raccomandato, poiché essa è la parte più complessa e di difficile lettura della query). Ad esempio, si può scrivere:

my ($where, @bind) = $sql->where(
{
    'users.id'      => { '!=' => 1 },
    'users.birth'   => {
        -between => [ '1977-01-01', '1977-11.15' ],
    },
    'locations.name' => {
        'like' => [ 'A%', 'B%' ],   # Inizia con 'A' o 'B'
    },
},
[
    'users.birth', 'locations.name',
]
);
my $query = "
    SELECT users.id, users.birth, locations.name
    FROM users LEFT JOIN locations ON locations.id = users.idlocation
    $where
";
my $sth = $dbh->prepare($query) or die $dbh->errstr;
$sth->execute(@bind) or die $dbh->errstr;

Se si ha bisogno della clausola LIMIT, si può utilizzare il modulo SQL::Abstract::Limit:

my $sql = SQL::Abstract::Limit->new( limit_dialect => $dbh );

my ($query, @bind) = $sql->select(
    'users',
    [ 'name', 'phone', 'email' ],
    { id => [ 1, 2 ] },
    [ 'id' ],
    5, 10
);
my $sth = $dbh->prepare($query) or die $dbh->errstr;
$sth->execute(@bind) or die $dbh->errstr;

Le strutture dati che costituiscono la where clause possono essere complesse quanto si desidera: benché le subselect non siano al momento supportate, è possibile utilizzare tutte le funzioni e stored procedures offerte dal proprio database server. Ulteriori esempi e dettagli di utilizzo si trovano nella documentazione del modulo: questo articolo non vuole costituire una documentazione, ma semplicemente una dimostrazione delle feature di SQL::Abstract, che si spera convinca il lettore a provarlo.

Utilizzando DBIx::Class si può ottenere il massimo da SQL::Abstract, dato che si guadagna la possibilità di effettuare facilmente join con vari tipi di relazioni, group_by, prefetch e (presto) subselect. Tutto ciò senza scrivere SQL. Scriverò qualcosa in merito prossimamente.

Inviato da arthas il 19.06.06 11:42
Ti è piaciuto questo articolo? Iscriviti al feed!










Devo ricordare i dati personali?






D:
Sull'autore...
D:
La ML di Perl.it
Iscriviti! mongers@lists.perl.it è la lista ufficiale di Perl Mongers Italia per porre quesiti di tipo tecnico, per rimanere aggiornato su meeting, incontri, manifestazioni e novità su Perl.it.
D:
Annunci Google