picoSQL rel.2x - Stored Procedure

Corso Italia, 178 - 56125 Pisa
Telefono/fax 050 46380
e-mail picosoft@picosoft.it

Obiettivi

Le "stored procedure" sono routine scritte in un qualche linguaggio di programmazione che normalmente sono memorizzate all'interno del database ("stored") e che possono venir attivate tramite uno specifico statement SQL, CALL. L'utilità di queste routine risiede sostanzialmente nel fatto che, essendo contenute nel database, possono accedere più velocemente ai dati, evitando i protocolli di comunicazione. Naturalmente possono essere utilizzate anche per automatizzare operazioni ripetute frequentemente, ma per questo secondo uso, i linguaggi di programmazione, e in particolare quelli a oggetti, permettono di solito di ottenere risultati migliori.
I linguaggi usati per scrivere le "stored procedure" variano da prodotto a prodotto, e non esiste ancora uno standard vero e proprio sebbene il leader del mercato sia riuscito a mettere il proprio linguaggio nello standard SQL99 (SQL3).
Dovendo scegliere un linguaggio da usare per le stored procedure in picoSQL, abbiamo vagliato diverse alternative e, alla fine, abbiamo scelto il C; vantaggi e svantaggi di questa scelta sono riportati di seguito.

Pro
- Velocità
- Vasta diffusione;
- Piccolo ingombro;
- Semplicità d'implementazione;
- Può fungere d'aggancio per altri linguaggi;
Contro
- Necessita la compilazione delle stored procedure fuori del database;
- Non molto usato da chi normalmente tratta con i database;
- Può far abortire il server;

Riguardo all'aggancio ad altri linguaggi, ci è stato suggerito sul forum di fornire un sistema che consentisse di poter realizzare procedure in qualsiasi linguaggio; le scelte fatte hanno tenuto conto di questa richiesta e vedremo che è possibile chiamare procedure in qualsiasi linguaggio a patto di scrivere un'interfaccia opportuna in C.
Una volta fatta questa scelta, ci si è posti il problema di come agganciare le stored procedure. Ci sono molti programmi che usano dei 'plug-in' basandosi su librerie dinamiche e quindi ci siamo guardati un po' intorno. Abbiamo visto però che tutti i sistemi usati sono discretamente complessi, soprattutto per il fatto che le librerie dinamiche funzionano in modo diverso sui diversi sistemi operativi. Per velocizzare lo sviluppo e non essere dipendenti dai diversi sistemi operativi, si è deciso che, almeno in questa prima fase, avremmo scelto la soluzione più semplice, e cioè quella di 'linkare' direttamente le procedure nel server; questo sistema è semplice, performante e indipendente dal sistema operativo, ma è relativamente scomodo se le procedure vengono cambiate spesso.
Sempre nell'ottica di mantenere tutto il più semplice possibile, si accede alla base dati tramite le API di picoSQL, del tutto analoghe a quelle ODBC.

Implementazione

Nella distribuzione di picoSQL 2x comparirà una directory di nome calls che contiene al proprio interno il file picocall.c e un makefile per compilarlo. Al suo interno viene dichiarato un array di strutture di tipo CallTable, di nome Calls che contiene tutti i punti d'ingresso delle stored procedure. La struttura CallTable, dichiarata in picocall.h, ha i seguenti attributi;

char *dbName Nome del database a cui appartiene la procedura. In questo nome si possono includere anche i caratteri jolly usati nella LIKE: questo permette di far appartenere una stessa procedara a più database diversi;

char *procName; Nome della procedura. Anche in questo nome si possono includere anche i caratteri jolly usati nella LIKE: questo permette di far corrispondere una stessa procedura a nomi diversi e quindi di avere delle interfacce generiche. Per esempio, sarebbe possibile fare in modo che tutte le procedure che iniziano con 'ruby_' siano eseguite da un interprete RUBY mentre quelle che iniziano con 'tcl_' siano eseguite da un interprete TCL. Il nome effettivo usato nella chiamata viene fornito alla procedura come argomento.

int (*call)(); Puntatore alla procedura.

short numArgs; Numero di argomenti della procedura. Se questo valore è < 0, picoSQL non fa alcun controllo sul numero degli argomenti.

short return_type; Indicatore se la procedura restituisce un valore (return_type > 0), nessun valore (return_type == 0); impostando return_type < 0, picoSQL non fa alcun controllo sull'esistenza del valore da restituire.

Un esempio di procedura potrebbe essere il seguente:

extern int SPTest(Env *e, Argument args[], int nArgs, Argument *rc);

struct CallTable Calls[] = {
   {"%", "SPTEST", SPTest, 0, 0},
   {0, 0, 0, 0, 0}
};

int
SPTest (Env *e, Argument args[], int nArgs, Argument *rc)
{
   return SQLExecDirect (e->hstmt, "SELECT * FROM AUTORI", SQL_NTS);
}

In questo esempio, la procedura "SPTEST", corrispondente alla funzione C SPTest, è richiamabile da qualsiasi database. Notare come l'array che contiene tutte le procedure sia concluso da un elemento composto da tutti elementi 0.
Una procedura riceve quattro argomenti, il primo che contiene i dati d'ambiente, il secondo che consiste in un array contenente tutti gli argomenti impostati dall'utente, il terzo con il numero degli argomenti e infine l'eventuale puntatore al valore restituito dalla procedura. Quest'ultimo può essere uguale a 0 se nella chiamata non è stato previsto alcun valore da restituire. La funzione deve restituire SQL_SUCCESS se va a buon fine oppure SQL_ERROR in caso di problemi. In quest'ultimo caso può segnalare il tipo di problema occorso. Le strutture Env e Argument sono definite sempre in picocall.h. Gli elementi della struttura Env sono i seguenti:

HSTMT hstmt Handle dello statement da usare per fare query sulla base dati. Una procedura può usare tutti gli statement che desidera, ma le operazioni relative alla chiamata corrente vengono eseguite su questa handle. Per esempio, supponendo di avere una procedura come quella riportata prima, le fetch eseguite dopo una CALL SPTEST restituiscono i risultati della SQLExecDirect solo se a questa viene passato come primo argomento questa handle.

char *userName Utente che ha fatto la chiamata.

char *dbName Database che ha fatto la chiamata.

char *procName Nome completo della procedura.

char exceptionMsg[256] Area di memoria dove inserire eventuali messaggi d'errore.

Per ciascun argomento, la struttura Argument contiene le seguenti informazioni, che sono del tutto analoghe a quelle specificabili nella API ODBC SQLBindParameter:

short ioType Tipo di parametro: può avere valore SQL_PARAM_INPUT, SQL_PARAM_OUTPUT o SQL_PARAM_INPUT_OUTPUT.

short cType Tipo C dell'argomento secondo la catalogazione ODBC.

short sqlType Tipo SQL dell'argomento secondo la catalogazione ODBC.

unsigned long columnSize Lunghezza dell'argomento.

short decimalDigits Numero di decimali.

valuePtr Puntatore all'area che contiene il valore.

valueLen Dimensione massima dell'area dati.

strLenOrIndPtr Puntatore a un numero che, nel caso di argomento di output o input/output, contiene il valore dalla dimensione del dato restituito.

char *dbName	Nome del database a cui appartiene la procedura. In questo nome si possono includere anche i caratteri jolly usati nella LIKE: questo permette di far appartenere una stessa procedara a più database diversi;
char *procName;	Nome della procedura. Anche in questo nome si possono includere anche i caratteri jolly usati nella LIKE: questo permette di far corrispondere una stessa procedura a nomi diversi e quindi di avere delle interfacce generiche. Per esempio, sarebbe possibile fare in modo che tutte le procedure che iniziano con 'ruby_' siano eseguite da un interprete RUBY mentre quelle che iniziano con 'tcl_' siano eseguite da un interprete TCL. Il nome effettivo usato nella chiamata viene fornito alla procedura come argomento.
int (*call)();	Puntatore alla procedura.
short numArgs;	Numero di argomenti della procedura. Se questo valore è < 0, picoSQL non fa alcun controllo sul numero degli argomenti.
short return_type;	Indicatore se la procedura restituisce un valore (return_type > 0), nessun valore (return_type == 0); impostando return_type < 0, picoSQL non fa alcun controllo sull'esistenza del valore da restituire.

HSTMT hstmt	Handle dello statement da usare per fare query sulla base dati. Una procedura può usare tutti gli statement che desidera, ma le operazioni relative alla chiamata corrente vengono eseguite su questa handle. Per esempio, supponendo di avere una procedura come quella riportata prima, le fetch eseguite dopo una CALL SPTEST restituiscono i risultati della SQLExecDirect solo se a questa viene passato come primo argomento questa handle.
char *userName	Utente che ha fatto la chiamata.
char *dbName	Database che ha fatto la chiamata.
char *procName	Nome completo della procedura.
char exceptionMsg[256]	Area di memoria dove inserire eventuali messaggi d'errore.

short ioType	Tipo di parametro: può avere valore SQL_PARAM_INPUT, SQL_PARAM_OUTPUT o SQL_PARAM_INPUT_OUTPUT.
short cType	Tipo C dell'argomento secondo la catalogazione ODBC.
short sqlType	Tipo SQL dell'argomento secondo la catalogazione ODBC.
unsigned long columnSize	Lunghezza dell'argomento.
short decimalDigits	Numero di decimali.
valuePtr	Puntatore all'area che contiene il valore.
valueLen	Dimensione massima dell'area dati.
strLenOrIndPtr	Puntatore a un numero che, nel caso di argomento di output o input/output, contiene il valore dalla dimensione del dato restituito.