UGISS User Group Italiano SQL Server
In questo articolo illustrerò un’applicazione pratica del CONTEXT_INFO (Books Online), ovvero del sistema messo a disposizione da SQL Server per salvare informazioni di contesto della sessione corrente.
Premessa
In un gestionale aziendale relativamente vasto che stiamo sviluppando abbiamo avuto la necessità, ad un punto avanzato dello sviluppo di una sezione del software, di avere a disposizione alcune informazioni della sessione di lavoro dell’utente nelle varie chiamate al database che venivano effettuate.
L’applicativo è composto da un paio di front-end web che si interfacciano verso un’unica istanza di SQL Server Standard. La parte web è sviluppata in ASP.NET 4.0 e prevede l’utilizzo di un unico utente di dominio (essendo web server e database su macchine separate) non interattivo per la connessione verso il DB. Per il colloquio con SQL utilizziamo un DAL sviluppato internamente (ed in continua evoluzione dalla sua prima incarnazione in .NET 1.1) che fa pesantemente uso di SP per tutti i normali task di lettura/scrittura, mentre un motore di presentazione e ricerca dati basato su tecnologia ajax (lato web) e SP (lato SQL), anch’esso proprietario, si occupa di fornire le ricerche necessarie all’operatività comune.
In particolare la necessità che era sorta era quella di avere, nelle varie SP, sempre a disposizione alcune informazioni dinamiche il cui valore influiva in modi diversi sulle varie estrazioni che venivano effettuate, come ad esempio la società di gestione degli ordini dell’azienda e la società di gestione del personale trattato, dati che in ogni momento gli operativi, durante la loro sessione di lavoro, avrebbero potuto cambiare avendo così viste differenti sui dati estratti.
Ci si ponevano davanti due strade:
- La modifica delle chiamate software alle varie SP con l’aggiunta dei parametri necessari (o di un parametro la cui funzione fosse di fare da Key verso una tabella di “stato” di questi valori)
- L’utilizzo di un meccanismo di passaggio di questi valori trasparente al codice già scritto, che potesse essere iniettato a nostra discrezione dove lo ritenessimo necessario (una specie di pattern IoC alla lontana, per intenderci)
L’opzione 1 si era rivelata subito infattibile per motivi di tempo e di design. Per quanto l’utilizzo di SMO e di un piccolo client .NET ci avrebbe permesso di manipolare le SP programmaticamente aggiungendo i parametri necessari lato SQL, la modifica delle parti di codice che effettuavano tali chiamate avrebbe richiesto troppo tempo (seppur mitigato da una rappresentazione ad oggetti delle SP autogenerata dal framework DAL e dalla concentrazione di alcuni processi chiave in parti di BusinessLogic applicativa). In ogni caso l’idea di portarci in giro questi parametri non ci allettava per niente, per non parlare poi dei costi di manutenzione e aggiornamento se i requisiti fossero cambiati.
CONTEXT_INFO
Dopo alcune ricerche sono capitato su questo post che mi ha dato uno spunto interessante.
In pratica SQL fornisce la possibilità di utilizzare un meccanismo di storage di un valore varbinary (di massimo 128 byte) rendendo disponibile tale valore per tutto il ciclo di vita della sessione SQL corrente. L’istruzione
SET CONTEXT_INFO { binary_str | @binary_var }
consente di salvare tale informazione, mentre la funzione
CONTEXT_INFO
consente di recuperarla.
Per passare a SQL questi dati abbiamo modificato la classe del DAL che si occupa dell’esecuzione dei Commands verso il database facendo in modo che chiamasse una SP dedicata appena dopo aver aperto la connessione al DB e subito prima della chiamata al SqlCommand richiesto.
La SP in questione è così composta:
@PCONTEXTINFO varchar(126),
@PSEPARATOR char(1) = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ctx varbinary(128)
DECLARE @sep char(1)
SET @sep = COALESCE(@PSEPARATOR, ‘;’)
SET @ctx = CAST(@sep + COALESCE(@PCONTEXTINFO, ”) + @sep AS varbinary(128))
SET CONTEXT_INFO @ctx
END
Il parametro @PCONTEXTINFO passato alla SP contiene un elenco separato da punti e virgola dei valori passati dal back-end. Tale valore viene poi normalizzato con il carattere separatore in testa e in coda per agevolare la “deserializzazione” (vedremo tra poco come avviene) e convertito in varbinary, usando la variabile d’appoggio @ctx. A questo punto non resta che da settare il context_info ed il primo passo è fatto.
Un paio di note riguardo a questa SP: per prima cosa, solitamente preferisco usare variabili Unicode ove possibile; l’utilizzo di varchar al posto in nvarchar in questo caso è dovuto al fatto che i dati passati alla SP sono tutti numerici interi ed in ogni caso non sono mai inputati dagli utenti, quindi sono valori prevedibili; sarebbe uno spreco di byte non necessario utilizzare un parametro nvarchar. La seconda considerazione riguarda la scelta del separatore di default; nel nostro caso è sicuro che non possa causare problemi in fase di split.
Il secondo passaggio è stato quello di creare un qualcosa che rendesse comodo l’utilizzo di questi valori all’interno del nostro codice SQL, in quanto una stringa di valori concatenati da punto e virgola non avrebbe avuto alcuna utilità pratica ai fini delle query. La scelta è ricaduta su una funzione di tipo tabella, eccola:
RETURNS
@ContextInfo TABLE
(
IdLogon int,
IdCompanyOrders int,
IdCompanyWorks int,
IdWorkflow int
)
AS
BEGINSET NOCOUNT ON
DECLARE @s AS varchar(128)
SET @s = CAST(CONTEXT_INFO() as varchar(128))
INSERT INTO @ContextInfo
(IdLogon, IdCompanyOrders, IdCompanyWorks, IdWorkflow)
SELECT
[1], [2], [3], [4]
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY dbo.tbl_Tally.N) AS N,
NULLIF(SUBSTRING(@s, dbo.tbl_Tally.N + 1, CHARINDEX(‘;’, @s, dbo.tbl_Tally.N + 1) – dbo.tbl_Tally.N – 1), N”) AS Vals
FROM dbo.tbl_Tally
WHERE dbo.tbl_Tally.N < LEN(@s)
AND SUBSTRING(@s, dbo.tbl_Tally.N, 1) = ‘;’
) AS split
PIVOT
(
MIN(Vals) FOR N IN ([1], [2], [3], [4])
) AS p
RETURN
END
Erano necessari tre step: recuperare la stringa, splittarla nei valori originali e resituirli al chiamante.
Per recuperare il valore contenuto nel CONTEXT_INFO ci è bastato utilizzare la funzione CONTEXT_INFO(), castare il valore ritornato a varchar(128) e salvarlo in una variabile d’appoggio per usi successivi. Veloce e indolore.
Per il secondo problema abbiamo optato per un algoritmo di split molto semplice che non illustrerò nel dettaglio in questo articolo. L’algoritmo è basato sull’uso di una cosiddetta Tally Table (link ad un ottimo articolo di Jeff Moden a riguardo) e scopro scrivendo queste righe che lo stesso Jeff Moden ha pubblicato un nuovo articolo che indirizza alcuni problemi di performance di questo algoritmo in caso di stringhe molto lunghe e con molti elementi; non è questo il caso, ma è indubbiamente un’ottima e interessante lettura.
Non rimaneva che affrontare il terzo problema. Una volta splittata la stringa si ottengono vari record (4, nel nostro caso), uno per ogni variabile presente. Ci è bastato quindi usare una semplice PIVOT per portare questi valori da righe a colonne, inserire il record risultante nella tablella locale @ContextInfo e restituirla. L’uso della funzione di aggregazione MIN è utile esclusivamente ai fini della costruzione della PIVOT, in quanto per ogni colonna risultante avrò un solo ed unico valore.
Una volta creata questa FUNCTION ci è bastato utilizzarla nelle SP che necessitavano di tali parametri. Supponiamo di avere una SP che si occupa di restituire un elenco degli ordini di una particolare società gestiti dall’utente attualmente connesso al sistema. La query potrebbe essere simile alla seguente:
ordini.IdOrdine,
ordini.DataOrdine
ordini.NoteFROM ord.tbl_Ordini AS ordini
INNER JOIN dbo.fnc_ContextInfo() AS ctx ON
ordini.IdLogon = ctx.IdLogon
AND ordini.IdCompanyOrders = ctx.IdCompanyOrders
oppure, in alternativa
DECLARE @idCompanyOrders intSELECT @idLogon = ctx.IdLogon, @idCompanyOrders = ctx.IdCompanyOrders FROM dbo.fnc_ContextInfo() AS ctx
SELECT
ordini.IdOrdine,
ordini.DataOrdine
ordini.Note
FROM ord.tbl_Ordini AS ordini
WHERE (ordini.IdLogon = @idLogon)
AND (ordini.IdCompanyOrders = @idCompanyOrders)
Considerazioni
Quella illustrata è solo una delle tecniche per ottenere ciò di cui avevamo bisogno; un’altra che ho valutato al momento di risolvere il problema è stata quella di utilizzare una Temporary Table, anche se l’idea mi piaceva meno non avendo sempre bisogno dei valori passati dal DAL e soprattutto non dovendo indicizzare alcun campo, essendo il recordset risultante sempre di un solo record. Profiler alla mano, inoltre, non siamo mai incorsi in problemi di ricompilazione delle SP, situazione che si può verificare, in alcuni casi, con l’uso delle Temporary Tables. L’overhead sui singoli Command eseguiti dall’applicativo è inoltre decisamente trascurabile (sempre loggata a zero come “duration” nel profiler, quindi infinitesimale per quanto riguarda l’engine SQL, stesso discorso per il lato .NET), e questo ha fatto sì che scegliessimo questa strada.
Rimango in ogni caso dell’idea, e questo è un parere prettamente personale, che l’uso di una tecnica di questo tipo, nella maggior parte dei casi, è un segnale che qualcosa non va, mentre nei casi rimanenti risponde ad un’esigenza davvero particolare. Mi spiego: avere dati di sessione “condivisi” tra back-end e DB è una cosa che cerco di evitare, reputandolo un po’ un “travalicare” i confini applicativi. Nel nostro caso questa soluzione è stata adottata per venire incontro ad un cambiamento radicale nell’organizzazione dell’azienda che seguivamo, e quindi all’insorgere dell’esigenza di gestire multi-società durante l’operatività comune in maniera il più possibile trasparente all’utente. Come spesso accade, purtroppo, il tempo (leggasi: l’investimento del cliente) per un refactoring come si deve era pura utopia, e ci siamo dovuti ingegnare trovando una strada che riducesse l’impatto globale delle modifiche garantendo comunque un risultato finale che non fosse un workaround ma una soluzione strutturata. Non posso sapere se, in altre circostanze, avremmo percorso strade diverse; fatto sta che nel gestionale che stiamo sviluppando avere a disposizione queste informazioni direttamente nelle nostre SP è diventato naturale come utilizzare le funzioni di sistema.
Ringraziamenti
Voglio ringraziare Computer City srl per avermi concesso la pubblicazione del codice allegato all’articolo.
