Programmazione/ MySQL e le sue API: un esempio pratico

Supponete di stare lavorando ad un sito web in PHP e di aver bisogno di fare periodicamente brevi report sul database connesso al webserver: cosa potreste fare? La soluzione più semplice è scrivere un piccolo script in Perl come frontend tra voi e il database, che possa essere magari eseguito via cron. Bé, anche se è  la soluzione più semplice richiede sempre la presenza del
Perl sulla macchina dove è installato il database, cerchiamo quindi dare una soluzione diversa. In questo articolo vedremo come spedire via mail un semplice report utilizzando le API del database server MySQL all’interno di un programma C.

Il mio problema iniziale era scoprire via remoto quanti utenti si erano registrati al database e utilizzare tali dati per mandare periodicamente una newsletter settimanale. Diciamo un problema comune e di facile soluzione, ma volevo fare tutto non dal server dove lavoro ma da casa.  Nel server avevo il servizio sshd e questo mi permetteva di eseguire un comando arbitrario sulla stessa macchina dove era posto il database: MySQL dal canto suo fornisce un’ottima API per programmare in C. La tabella che volevo interrogare aveva la seguente struttura:

Field Type Null Key Default Extra
user_id int(11) PRI 0 auto_increment
user_name text YES NULL
real_name text YES NULL
email text YES NULL
password text YES NULL
remote_addr text YES NULL
confirm_hash text YES NULL
is_confirmed int(11) 0


Per ciascun elemento della tabella sovrastante, chiamata “user”, volevo conoscere il nome reale e l’indirizzo di posta elettronica allo scopo di mandare la newsletter, tutto in modo piu o meno automatico mediante il seguente script:

#!/bin/bash
# Filename: newsletter
# Path for scp , for more info: man scp
SCP=/usr/bin/scp
#path for ssh, ssh client
SSH=/usr/bin/ssh
# remote user, who exists in the box , where's the database server
REMOTE_USER=webm
# DataBase Server host
REMOTE_HOST=lisa.penguin.it
# test if exist ssh and scp otherwise exit
test -f $SCP || exit 0
test -f $SSH || exit 0
# test if the newsletter file exists
test -f $1 || exit 0
$SCP $1 $REMOTE_USER@$REMOTE_HOST:/tmp/newsletter
$SSH $REMOTE_USER@$REMOTE_HOST sendnws -f /tmp/newsletter

Penso che lo script sia abbastanza semplice da capire. Ecco un esempio di esecuzione da linea di comando:
deneb@lisa:~$ newsletter /home/deneb/letter

L’utente esperto si chiederà perché non usare direttamente la connessione alla porta MySQL invece di usare sshd. Il fatto è che abbiamo rispettato una norma utile quando si lavora sul web: evitare (quando possibile) che servizi critici (come mysqld) si interfaccino direttamente con la rete avendo magari diritti di root. Così tutte le connessioni dirette alla porta MySQL vengono
filtrate e MySQL gira con privilegi di un utente fittizio che non ha shell. Come fare? Supposto che mysqluser sia l’utente dedicato a MySQL, basta eseguire i seguenti comandi:

root@main:~# chown -R mysqluser /path/to/mysql/dir
root@main:~# safe_mysqld –user=mysqluser

Semplice no? Bene iniziamo a programmare.

Tuffiamoci nel codice

Per svolgere il suo compito a dovere il nostro programma necessita di lavorare con alcune strutture dati descritte nel file mysql.h, che dovrà essere incluso. La prima che incontreremo è la struttura MYSQL, rappresentante l’handle alla connessione verso il database server. Tale struttura contiene i parametri di comunicazione e diverse opzioni utili per la connessione. Vediamo un esempio di utilizzo:

Esempio 1.

#include <mysql.h>

int main (void) {
MYSQL mysqllink;
mysql_init(&mysqllink);
mysql_close(&mysqllink);
return 0;
}


Bene, ora compiliamo il programma:

deneb@lisa:~$ gcc -g -Wall -o esempio1 esempio1.c -L /usr/local/lib/mysql
-lmysqlclient -lnsl -lm

Se eseguiremo il programma esempio1, esso non ci darà alcun output in quanto inizializza una struttura MYSQL mediante la funzione mysql_init allocandola in memoria e poi la dealloca mediante la struttura dati mysql_close. In generale un programma che usa la libreria mysqlclient segue le seguenti fasi:

1. alloca la struttura MYSQL
2. si connette al database server
3. esegue query
4. per ogni query eseguita memorizza i risultati
5. usa i risultati
6. chiude la connessione e dealloca la struttura MYSQL

Abbiamo visto come fare il primo passo e come deallocare la struttura MYSQL,
vediamo ora come connetterci: facciamo dunque un passo avanti nella descrizione
del programma.

Esempio 2.

#define HOSTNAME "localhost"
#define MYSQL_USER "webclient"
#define DB "webuser"
#define MYSQL_PASSWORD "your_password_here"
#define MYSQL_SOCKET "/tmp/mysql.sock"
#define MYSQL_PORT 3306
#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>

int main (void) {
MYSQL mysqllink;
mysql_init(&mysqllink);

if (!mysql_real_connect(&mysqllink,_HOSTNAME,_MYSQL_USER,_MYSQL_PASSWORD,DB,MYSQL_PORT,MYSQL_SOCKET,0))
{
fprintf(stderr,"Failed connect to MySQL server.nError: %sn",
mysql_error(&mysqllink));
exit(1);
}
printf("Connected to the MySQL Servern");
printf("I'm going to close connectionn");
mysql_close(&mysqllink);
return 0;
}

Descriviamo il listato soprastante, la prima cosa che notiamo sono le macro :

#define HOSTNAME "localhost"
#define MYSQL_USER "webclient"
#define DB "webuser"
#define MYSQL_PASSWORD "your_password_here"
#define MYSQL_SOCKET "/tmp/mysql.sock"
#define MYSQL_PORT 3306

come potrete aver capito esse definiscono i parametri per la funzione mysql_real_connect, che ha il compito di collegarsi al server database. mysql_real_connect per eseguire il suo compito deve conoscere: l’host (HOSTNAME), l’utente con cui si deve collegare(MYSQL_USER), il nome del database (DBUSER), la password da usare (MYSQL_PASSWORD), il socket (MYSQL_SOCKET) e il numero di porta su cui gira il demone mysqld (MYSQL_PORT). Dati questi parametri se mysql_real_connect ritorna un valore nullo significa che la connessione non ha avuto luogo. Come fare allora a sapere il tipo di errore verificatosi? Bene, se si verifica un errore durante la connessione tale errore viene impostatato nella struttura MYSQL allocata, nel nostro caso mysqllink, e per vedere il tipo di errore useremo la macro mysqlerror, definita nel file <mysql.h>, nel seguente modo:

#define mysql_error(mysql) (mysql)->net.last_error

che ci restuirà il campo net.last_error impostato dalla mysql_real_connect, poiché si è verificato un errore. Vediamo come è stata usata nel nostro esempio:

if (!mysql_real_connect(&mysqllink,_HOSTNAME,_MYSQL_USER,_MYSQL_PASSWORD,DB,MYSQL_PORT,MYSQL_SOCKET,0))
{
fprintf(stderr,"Failed connect to MySQL server.nError: %sn",
mysql_error(&mysqllink));
exit(1);
}

Se mysql_real_connect restituisce un valore nullo, allora il programma di esempio scrive sullo standard error un messaggio di errore e il campo net_last_error della struttura MYSQL rappresentante il tipo di errore.

Costruiamo la query

A questo punto si è presentanto un problema: era nostra intenzione fare in modo che il codice che dovevamo scrivere fosse il più generale possibile, e quindi fosse riusabile. Abbiamo quindi pensato di scrivere una funzione simile alla printf, definita in questo modo:

char* alloc_query(const char *query, ...)

Questa funzione si comporta nel seguente modo: data una stringa ne calcola la lunghezza ed alloca in memoria la lunghezza stessa controllando la malloc: se l’allocazione fallisce restituisce NULL. Un classico esempio di utilizzo che ci è venuto in mente sarebbe stato il seguente:

Esempio 3

char* query_on_table(char* tablename){
char *q;
q=alloc_query("SELECT * FROM %s WHERE newsid=1",tablename);
return q;
}

Per realizzare tale funzione ci siamo avvalsi della funzione vasprintf, che alloca automaticamente una stringa. Lasciamo al lettore l’esame del codice relativo a questa routine in quanto non strettamente correlata al nostro problema.

La funzione alloc_query ci fornisce quindi una stringa che rappresenta una query, quindi ora passiamo ad interrogare il database ed a ottenere i risultati. Dobbiamo però distinguere tra le query che ritornano un’insieme di dati (i.e. SELECT, SHOW, DESCRIBE, EXPLAIN) e le altre. Nel nostro esempio prendiamo in esame solo la select, infatti noi dal nostro database dobbiamo selezionare tutti i nomi e le mail degli utenti registrati. La query sarà:

SELECT real_name,email FROM user;

dove user è la tabella descritta precedentemente. Definiamo prima una funzione che esegua sull’host remoto una generica query della classe a cui appartiene SELECT e ritorni un insieme di risultati. Tale insieme è rappresentato dalla struttura MYSQL_RES. Vediamo un esempio di come è fatta tale funzione:

Esempio 4.

MYSQL_RES* get_select_result(const char *myquery,
MYSQL linksql)
{
MYSQL_RES *result;

if (mysql_real_query(&linksql,myquery,sizeof(myquery))) {
fprintf(stderr,"Error during the query:%sn",mysql_error(&linksql));
return NULL;
}
else {
result=mysql_store_result(&linksql);
if (result){
return result;
}
}
return NULL;
}

Questa funzione prende una stringa rappresentata da myquery e la struttura linksql rappresentante la connessione al DBMS, e restituisce il puntatore alla struttura MYSQL_RES, definita in <mysql.h>, rappresentante l’insieme dei risultati. Se si verificano errori restituisce il puntatore NULL. Notiamo che qui sono utilizzate due funzioni definite dell’API di mysql:

int mysql_real_query(MYSQL *mysql, const char *q,
unsigned int length);
MYSQL_RES * mysql_store_result(MYSQL *mysql);


La prima funzione esegue la query contenuta dalla stringa *q, di dimensione length e restituisce un valore non nullo se si verificano errori. Se l’esecuzione si è verificata con successo chiameremo la funzione mysql_store_result per salvare i risultati nella struttura MYSQL_RES di cui la funzione restituisce il puntatore.

Elaboriamo i risultati e generiamo il report

Ora abbiamo l’insieme di risultati posti in MYSQL_RES e vogliamo esaminarli riga per riga: a tal scopo possiamo usare la funzione mysql_fetch_row per ottenere la riga e la funzione mysql_num_fields per ottenere il numero di campi affetti dalla query. Ecco come.

Esempio 5.

void show_results(MYSQL_RES *result) {
MYSQL_ROW row;
unsigned int num,i;
num  = mysql_num_fields(result);
while ((row = mysql_fetch_row(result))) {
for (i=0; i< num; i++) {
printf("Valore: %sn",row[i]);
}
}
}

La funzione mysql_fetch_row ritorna un valore nullo quando l’insieme delle righe esaminato è completato uscendo così dal ciclo while. Da notare come è definito il tipo di dato MYSQL_ROW in <mysql.h>:

typedef char **MYSQL_ROW; /* return data as array of
strings */


Spediamo i risultati via mail.

Al nostro programma per essere completo occorre una routine che spedisca i risultati, cioè che prenda un dato file in input e spedisca una mail ad un dato utente. Per tale motivo abbiamo definito un funzione così progettata:

int mailx(const char from*,const char *to, const
char* mailhost, FILE *body)

Tale funzione restituisce un valore diverso da zero se si verificano errori. L’implementazione di tale funzione si basa su un post di Wietse Venema nella mailing list di postfix, che implementava un client SMTP a riga di comando; il codice è stato solo leggermente modificato per adattarsi al nostro caso. Non è comunque mia intenzione soffermarmi sulla discussione di tale funzione, che implementa una gestione di base dei socket, vorrei continuare il discorso e mettere assieme le parti gia descritte, lasciando al lettore l’esame del codice.

Mettiamo assieme i pezzi

Creiamo una prima versione del programma con tutte le funzioni appena definite che interroghi il database e fornisca mail e real_name di tutti gli appartenenti alla tabella guest stampando infine i risultati.

#define _GNU_SOURCE /* Viene usata per garantire la massima compatibilità e le estensioni GNU, tra cui la funzione vasprintf usata dalla funzione alloc_query
*/
#include <unistd.h>
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <stdarg.h> /* needed for alloc_query routine */
/* in database.h you can find all macros needed for connecting*/
#define HOSTNAME "localhost"
#define MYSQL_USER "webclient"
#define DB "webuser"
#define MYSQL_PASSWORD "your_password_here"
#define MYSQL_SOCKET "/tmp/mysql.sock"
#define MYSQL_PORT 3306
#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>
/* In questo header viene definita la funzione mailx, da noi progettata.
La sua implementazione viene definita in mailx.c */
#include "mailx.h"

char* alloc_query(const char* query, ...);
MYSQL_RES* get_select_result(const char* myquery,MYSQL linksql);
void show_results(MYSQL_RES *result);

/* function main */
int main (void) {
MYSQL mysqllink;
MYSQL_ROW row;
MYSQL_RES *result;
unsigned int num_fields;
unsigned int num_rows,i;
char *nquery=NULL;
mysql_init(&mysqllink);
if ((nquery=alloc_query("SELECT email,real_name FROM user"))==NULL) {
perror("Internal Buffer Overflow");
exit(1);
}
if (!mysql_real_connect(&mysqllink,HOSTNAME,MYSQL_USER,MYSQL_PASSWORD,DB,MYSQL_PORT,MYSQL_SOCKET,0))
{
fprintf(stderr,"Connessione al database mysql fallita.nErrore: %sn",
mysql_error(&mysqllink));
exit(1);
}
printf("Connesso ad MySQL Servern");
result=get_select_result(nquery,mysqllink);
show_results(result);
printf("Chiudo la connessionen");
mysql_close(&mysqllink);
free_query(nquery);
return 0;
}

Alla conclusione del programma mancano due cose ancora: la gestione dei parametri mediante getopt e una variante della funzione show_results, che apra il file dato come parametro ed usi la funzione mailx per mandare la newsletter. Io però mi fermo qui, visto che il mio obiettivo era soltanto quello di porre le basi per la risoluzione del problema, lasciando ai lettori più volenterosi il completamento e l’affinamento del programma. Se avete dubbi o richieste scrivetemi pure all’indirizzo sotto riportato.

Giorgio Zoppi è sistemista Linux e curatore del portale Linux Penguin.it