Esporta (0) Stampa
Espandi tutto

SQLCLR: estendere le funzionalità con .Net

Di Andrea Benedetti - Microsoft MVP

Una delle novità più attese dalla comunità degli sviluppatori introdotta da SQL Server 2005, e presente in ogni sua edizione, dalla Express alla Enterprise, è l’integrazione con il Common Language Runtime del .Net Framework, anche detto SQLCLR.Ovvero la possibilità di realizzare ed utilizzare oggetti programmabili, come stored procedure, user function, trigger e tipi definiti dall’utente implementabili con uno qualsiasi dei linguaggi offerti dal framework.

In questa pagina

L’integrazione L’integrazione
La sicurezza La sicurezza
Il nostro primo progetto Il nostro primo progetto
Conclusioni Conclusioni

L’integrazione

Certamente il poter costruire funzioni e procedure scritte dall’utente non è una novità.

Già nelle versioni precedenti era possibile utilizzare funzioni e procedure custom tramite la definizione di stored procedure estese, ovvero tramite la scrittura in C / C++ di librerie, i cui principali problemi erano la non facile produzione del codice, dovuto anche alla difficoltà di debug e di rilascio / aggiornamento di nuove versioni, e la libertà che aveva lo sviluppatore.

Infatti la loro esecuzione, completamente esterna al contesto di SQL Server, non era, di fatto, controllabile e quindi, con librerie scritte male, potenzialmente dannosa.

L’integrazione con il CLR, invece, porta con sé una serie di vantaggi che illustreremo tra breve.

Prima di questo però ci soffermiamo un attimo sul TSQL che, arricchito di nuovi operatori e funzionalità, resta e resterà il linguaggio per accedere ai dati.

Le istruzioni di select, insert, update e delete infatti non avranno sostitute, ma verranno sempre scritte con questo linguaggio.

Il TSQL però, linguaggio pensato ed ottimizzato per lavorare con set di dati, diventa limitante in molti scenari non potendo utilizzare oggetti complessi o risolvere problemi complicati in maniera semplice.

Certamente possiamo arrivare a scrivere stored procedure che risolvano questi scenari, ma il risultato che otterremo potrebbe essere costituito da centinaia di righe di codice che avranno, di conseguenza, scarsa leggibilità e difficile manutenzione.

Oggi, potendo utilizzare le librerie e gli oggetti messi a disposizione dal Framework, risolvere problemi complessi potrebbe diventare molto più semplice.

Si pensi, ad esempio, a tutti quei possibili vincoli CHECK che devono validare un dato per consentirne il suo inserimento o il suo aggiornamento.

Oggi, ad esempio con il supporto delle Regular Expression, potremmo risolvere il problema in maniera molto più rapida, scrivendo meno codice, rendendolo più leggibile, più mantenibile e, magari, riuscendo ad utilizzarlo (portabilità) anche in altre applicazioni Web o Windows.

La sicurezza

Un aspetto assolutamente non trascurabile riguarda la a sicurezza legata alle nostre librerie.

Sicurezza che viene gestita interamente da SQL Server che, grazie al grado di attendibilità associato ad ogni assembly, decide se consentire o meno alle richieste ricevute.

In fase di creazione dei nostri assembly, ovvero in fase di installazione all’interno del database, SQL Server dovrà sempre associare un set di permessi che identifichi il grado di attendibilità dei programmi stessi e, quindi, sapere che cosa può o non può fare.

I set di permessi che abbiamo a disposizione sono tre:

- safe: impostazione di default, è il livello più restrittivo e consente di accedere solo ai dati presenti all’interno dell’istanza

- external access: consente di accedere anche a risorse esterne al contesto di SQL Server come, ad esempio, file, risorse di rete, registro di sistema

- unsafe: il livello meno restrittivo, permette un accesso senza controllo a qualsiasi risorsa (interna ed esterna a SQL Server).

Il nostro primo progetto

Per poter costruire e quindi utilizzare oggetti di tipo SQLCLR utilizzeremo Visual Studio 2005 che, grazie all’integrazione con SQL Server, consente operazioni di deploy molto veloci.

Inoltre, grazie alla nuova famiglia di progetti Database ed ai template forniti (uno per ogni tipo di oggetto costruibile), la scrittura di questi oggetti diventa molto semplice.

Da sottolineare come, utilizzando Visual Studio, saremo in grado di fare uso di un solo ambiente sia per lo sviluppo degli oggetti, sia per le operazioni di debug che si renderanno necessarie.

Prima di costruire qualche esempio di utilizzo cerchiamo di capire se e quando questa nuova possibilità potrebbe fornire dei reali benefici ai nostri database.

Certamente la possibilità che abbiamo, da oggi, di costruire oggetti programmabili tramite il Net Framework non deve portarci al voler scrivere (o migrare) nel nostro linguaggio preferito gli oggetti database.

Principalmente sono tre gli ambiti in cui potremmo scegliere di scrivere, oppure di riscrivere, istruzioni in .Net:

  • riusabilità del codice

  • calcoli complessi, manipolazione di stringhe, funzionalità che non devono accedere ai dati

  • utilizzo di risorse esterne

Il primo caso, la riusabilità del codice, banalmente, ci fornisce la possibilità di utilizzare anche all’interno dei nostri database la logica, più o meno complessa, di algoritmi (magari proprietari), di istruzioni che fino a ieri dovevamo, per utilizzarle, riuscire a tradurre in TSQL.

Nel campo dei calcoli complessi, o più banalmente, nell’utilizzo di funzioni utente, ovvero quegli oggetti dove si presenta la maggior parte delle istruzioni che devono lavorare riga per riga, il CLR che produce del codice nativo, consente di essere più veloce e di risparmiare carico di lavoro al nostro sistema.

In ultima analisi tutti quegli scenari in cui abbiamo la necessità di utilizzare (in scrittura o anche in lettura) delle risorse esterne al nostro motore relazionale.

Pur sapendo che un database non nasce per lavorare con file e cartelle, potremmo trovare applicazioni database in cui questa esigenza si rende necessaria.

Oggi, grazie alle librerie del Framework, ai suoi oggetti ed ai metodi che ci vengono messi a disposizione, questo scenario diventa di assai semplice soluzione.

Da rimarcare come gli oggetti managed, producendo del codice che viene compilato in codice nativo prima dell’esecuzione, consentono di ottenere migliori performance rispetto ad un linguaggio procedurale che dovrà comunque essere sempre interpretato.

Come in molti altri prodotti della famiglia Windows Server System anche in SQL Server molte funzionalità avanzate, non essenziali all’uso, vengono disabilitate di default.

Infatti la procedura di setup installa il CLR ma, nel caso in cui volessimo utilizzarlo, starà a noi abilitarlo all’uso.

La sua abilitazione può essere fatta tramite il nuovo “Surface Area Configuration”, nuovo strumento presente all’interno della cartella Configuration Tools del menu di SQL Server, oppure tramite istruzione TSQL, ovvero tramite la procedura sp_configure:

sp_configure "clr enabled", 1
go
reconfigure
go

Vediamo allora di scrivere un po’ di codice con due esempi pratici, una stored procedure ed una user defined function, per cercare di entrare nel dettaglio e per vedere come utilizzare nella realtà questa integrazione.

Nel primo esempio simuliamo la necessità di scrivere su file un log delle operazioni di una nostra applicazione database.

Costruiremo, per questo, una stored procedure che riceverà in ingresso tutta una serie di parametri, tra cui il testo che vogliamo andare a memorizzare sul log.

Apriamo Visual Studio 2005, scegliamo il linguaggio a noi più congeniale e definiamo un nuovo progetto, che chiameremo SQLCLR, di tipo database.

La prima richiesta che ci viene fatta è quella di definire, o meglio, referenziare, il database su cui vogliamo andare ad installare i nostri assembly.

Tramite la finestra “Add Database Reference” che si sarà aperta scegliamo quindi il pulsante “Add New Reference” ed impostiamo la connessione verso l’istanza ed il database interessati.

*

Figura 1

Definita e provata la nostra connessione premiamo ok ed entriamo nello strumento di sviluppo vero e proprio.

La nostra solution, ancora vuota, ci consentirà, tramite tasto destro sul nome del progetto, di scegliere un nuovo oggetto, andando a selezionare, tra le voci disponibili, Stored Procedure.

*

Figura 2

Visual Studio ci fornirà, quindi, un template, ovvero una traccia, del codice necessario a realizzare l’oggetto del tipo scelto.

Il codice della nostra procedura sarà simile a:

[C#]

[SqlProcedure]
public static void up_logFile
    (string fileLogPath, string textMessage,
     string messageSource, string messageCategory)
{
    /*
     * Formatto la stringa così come voglio venga scritta sul file di log
     */
    string logLine = String.Format
        ("{0:yyyy-MM-dd hh:mm:ss.fff} * {1} * {2} * {3}{4}",
         DateTime.Now, messageSource, messageCategory,
         textMessage, Environment.NewLine);

    /* 
     * Verifico che mi sia stato passato il path del file di log 
     * e che la dir esista
     * 
     */
    if ((fileLogPath == null) || (fileLogPath.Length == 0) ||
        (!Directory.Exists(fileLogPath)))
    {
        FileInfo myFile = new FileInfo(fileLogPath);
        fileLogPath = Path.Combine
            (Path.GetTempPath(), myFile.Name);
    }

    /*
     * Scrivo su file
     */
    File.AppendAllText(fileLogPath, logLine);

    /*
     * Messaggio al client su dove ho scritto il log
     */
    SqlContext.Pipe.Send
        ("Scrittura log su: " + fileLogPath);
}

[VB.Net]

<SqlProcedure()> _
Public Shared Sub up_logFile(ByVal fileLogPath As String, _
ByVal textMessage As String, _
        ByVal messageSource As String, ByVal messageCategory As String)

Dim logLine As String = String.Format("{0:yyyy-MM-dd hh:mm:ss.fff} * {1} * {2} * {3}{4}", _
        
New Object() {DateTime.Now, messageSource, messageCategory, textMessage, Environment.NewLine})
      
If Not (((Not fileLogPath Is Nothing) AndAlso _
            (fileLogPath.Length <> 0)) AndAlso _
            System.IO.Directory.Exists(fileLogPath)) Then
            Dim info1 As New System.IO.FileInfo(fileLogPath)
fileLogPath = System.IO.Path.Combine(System.IO.Path.GetTempPath, info1.Name)
        End If

System.IO.File.AppendAllText(fileLogPath, logLine)
SqlContext.Pipe.Send(("Scrittura log su: " & fileLogPath))
End Sub

La nostra procedura, pubblica e statica, per essere utilizzata dall’esterno e per essere richiamabile senza istanziare l’oggetto che la contiene, è stata decorata con l’attributo SqlProcedure così da informare il nostro database engine della tipologia di oggetto.

Punto cruciale del nostro codice è la scrittura su file, tramite il metodo AppendAllText e il poter inviare al database, così come potrebbe fare l’istruzione Print del TSQL, un messaggio tramite l’istruzione: SqlContext.Pipe.Send(“messaggio…”);

Costruiamo a questo punto un secondo oggetto, una User Defined Function (un solito progetto può, quindi, contenere più oggetti, chiaramente, di tipo differente).

Come abbiamo fatto per la procedura, tramite il tasto destro sul progetto, scegliamo un nuovo oggetto user function.

Questa volta vogliamo scrivere due funzioni.

Una che prenda in ingresso una stringa e verifichi, tramite una regular expression, se si tratti di un indirizzo mail valido.

La seconda, più generica, che prenda in ingresso sia un pattern sia una stringa che verrà validata, tramite il metodo IsMatch della classe RegularExpression, proprio tramite il modello passato come prima stringa.

Il nostro codice sarà quindi simile a:

[C#]

[SqlFunction]
public static SqlBoolean IsValidEmail(SqlString emailAddress)
{
    System.Text.RegularExpressions.Regex regex =
        new System.Text.RegularExpressions.Regex
            (@"^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]"
             + @"{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))"
             + @"([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$");
    return regex.IsMatch(emailAddress.Value);
}

[SqlFunction]
public static bool RegExMatch(string pattern, string matchString)
{
    System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(pattern);
    return regex.IsMatch(matchString);
}

[VB.Net]

<SqlFunction()> _
Public Shared Function IsValidEmail(ByVal emailAddress As SqlString) As SqlBoolean

Dim regex As New System.Text.RegularExpressions.Regex
("^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$")
Return regex.IsMatch(emailAddress.Value)

End Function

<SqlFunction()> _
Public Shared Function RegExMatch(ByVal pattern As String, ByVal matchString As String) As Boolean

Dim regex1 As New System.Text.RegularExpressions.Regex(pattern)
Return regex1.IsMatch(matchString)

End Function

Anche in questo esempio andremo a decorare i nostri metodi con il corretto attributo (SqlFunction) creandoli sia pubblici, sia statici.

Scritti i nostri oggetti non ci resta che effettuarne il deploy sul server e provarli.

A questo punto abbiamo due modi per effettuare la distribuzione dell’assembly che dovrà essere caricato all’interno del nostro database (e visibile tramite la vista di sistema sys.assemblies).

Possiamo effettuarla in maniera automatica, ovvero tramite il menu “Build” “Deploy” di Visual Studio, oppure, in maniera manuale, tramite istruzioni TSQL.

A titolo di conoscenza può essere molto interessante effettuare la procedura di deploy automatica dopo aver fatto partire una traccia di SQL Server Profiler, strumento che consente di registrare il lavoro del nostro sql server, per vedere le istruzioni che vengono eseguite una dopo l’altra per rendere disponibili all’interno del nostro database i nostri oggetti.

Di seguito analizziamo le istruzioni necessarie per eseguire a mano tutte le operazioni di installazione.

Per prima cosa dovremo consentire al nostro database di accedere a risorse esterne per poter caricare l’assembly al suo interno, ovvero, tramite l’istruzione ALTER DATABASE:

ALTER DATABASE MSDN
SET TRUSTWORTHY  ON
GO

Di seguito creiamo l’assembly all’interno del database, andando ad impostare il corretto set di permessi e supponendo che la nostra DLL compilata con Visual Studio si trovi sul percorso “C:\progettoSqlClr”:

FROM 'C:\progettoSqlClr\sqlclr.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS

Attenzione che, se eseguissi la procedura di deploy automatica da Visual Studio 2005, dovrò ricordarmi di modificare il set di permessi di default con quello corretto, tramite l’istruzione ALTER ASSEMBLY, come:

ALTER ASSEMBLY [sqlclr]
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

A questo punto non ci resta che creare la nostra stored procedure ricordando la corretta sintassi, ovvero:

CREATE OGGETTO AS
EXTERNAL NAME [nome_assembly].[nome_classe].[nome_metodo]

Quindi:

CREATE PROCEDURE dbo.up_logFile
(
@fileLogPath [nvarchar](4000),
@textMessage [nvarchar](4000),
@messageSource [nvarchar](4000),
@messageCategory [nvarchar](4000)
) 
AS
EXTERNAL NAME [sqlclr].[StoredProcedures].[up_logFile]
GO

Si può notare come le prime righe della nostra istruzione CREATE PROCEDURE siano assolutamente le classiche istruzioni per creare una procedura.La differenza la notiamo solo dopo la clausola AS dove fino a ieri inserivamo le istruzioni TSQL, mentre oggi andiamo ad inserire i riferimenti, ovvero l’indicazione del metodo, della classe e dell’assembly che lo contiene . Eseguito il deploy apriamo il Management Studio ed espandiamo il nodo Databases, quindi il nostro db, Programmability ed Assemblies.Potremmo così verificare l’effettiva presenza del nostro compilato all’interno del nostro database.

*

Figura 3

Da questo possiamo capire come sia sufficiente un backup per salvare anche il nostro assembly e le procedure / funzioni create e, quindi, un relativo restore anche su una differente macchina, per avere correttamente a disposizione i nostri oggetti.

A questo punto non resta che provare la nostra procedura.

Quindi, tramite il comando EXEC, come faremmo con qualsiasi altra procedura, proviamo ad eseguirla, ricordandoci che alla sua prima esecuzione impiegherà del tempo in più necessario alla sua compilazione in codice nativo:

execute dbo.up_logFile
   @fileLogPath= 'c:\fileLogSqlClr.txt',
   @textMessage= @text,
   @messageSource= 'up_insertAnagrafica',
   @messageCategory=' info'

Proviamo adesso anche le funzioni scritte ed inserite all’interno del nostro database, ad esempio tramite le seguenti istruzioni TSQL:

select dbo.IsValidEmail('info@acme.com') as isValidEmail
select dbo.IsValidEmail('info@aaa') as notValidEmail

Le nostre funzioni SQLCLR, come tutte le altre funzioni TSQL, potranno essere utilizzate come vincolo CHECK delle nostre tabelle, quindi, in un’ipotetica tabella Clienti potremmo inserire un vincolo come:

ALTER TABLE dbo.clienti ADD CONSTRAINT
CK_clienti_email CHECK ((dbo.IsValidEmail(email) = 1))

La seconda funzione, invece, potremmo provarla per verificare la bontà di un indirizzo IP tramite :

select dbo.RegExMatch('^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]','192.168.1.1') as validIP

Oppure per verificare un CAP valido, quindi cinque caratteri numerici, come:

select comune, cap
from localita
where dbo.RegExMatch('^\d{5}$', cap) = 1

Ricordiamoci, infine, che potremmo recuperare il codice sorgente delle nostre procedure e delle nostre funzioni tramite le viste di sistema sys.assemblies e sys.assembly_files, ad esempio con la seguente select:

select
a.[name] as [Assembly],
f.name as _FileName, 
cast( ASSEMBLYPROPERTY(a.Name,'VersionMajor') as varchar(10)) + '.' +
cast(ASSEMBLYPROPERTY(a.Name,'VersionMinor')  as varchar(10)) + '.' +
cast(ASSEMBLYPROPERTY(a.Name,'VersionBuild')  as varchar(10)) + '.' +
cast(ASSEMBLYPROPERTY(a.Name,'VersionRevision')  as varchar(10)) as Version,
cast(f.[content] as varchar(max))  as _sourceCode
from sys.assembly_files f 
join sys.assemblies a 
on f.assembly_id = a.assembly_id 
where f.[name] like '%.cs' or f.[name] like '%.vb'

Conclusioni

Sicuramente l’integrazione che abbiamo con il mondo .Net può aiutarci nel risolvere problemi che fino a ieri potevano essere molto complessi e consentendoci di utilizzare ciò che il framework ci mette a disposizione in maniera nativa: librerie per la manipolazione di stringhe, di file e cartelle, ecc...Sicuramente, se dovessimo identificare i primi soggetti che potrebbero essere migrati sotto il mondo managed, le User Defined Function potrebbero essere le prime candidate.Questo perché sono, nella maggior parte dei casi, dei “contenitori” di codice complesso che, lavorando riga per riga, richiede alla nostra macchina del lavoro computazionale intensivo, senza dover accedere ai dati.


Microsoft sta conducendo un sondaggio in linea per comprendere l'opinione degli utenti in merito al sito Web di MSDN. Se si sceglie di partecipare, quando si lascia il sito Web di MSDN verrà visualizzato il sondaggio in linea.

Si desidera partecipare?
Mostra:
© 2014 Microsoft