SQL Server 2005
Le espressioni regolari semplificano l'estrazione dei dati e delle corrispondenze dei modelli
David Banister
In questo articolo verranno discussi i seguenti argomenti:
- Esecuzione efficace di query SQL con utilizzo delle espressioni regolari
- Supporto delle espressioni regolari in SQL Server 2005
- Utilizzo delle classi Regex di .NET da SQL Server
- Utilizzo efficace delle espressioni regolari in un database
|
In questo articolo verranno utilizzate le seguenti tecnologie:
SQL Server 2005, .NET Framework
|
Scarica il codice per questo articolo:
Regex2007_02.exe
(154 KB)
Analisi del codice online
Sommario
Sebbene nella maggior parte dei casiT-SQL si riveli estremamente potente per l'elaborazione dei dati, non è molto efficace come supporto per l'analisi o la manipolazione del testo. Tentare di eseguire un'analisi del testo sofisticata tramite l'utilizzo delle funzioni stringa incorporate produce stored procedure e funzioni estremamente grandi che comportano difficoltà nell'esecuzione del debug e della gestione. Esiste una soluzione migliore?
In realtà, le espressioni regolari offrono una soluzione molto più efficace e raffinata. È molto semplice scoprire la loro utilità per il confronto del testo nell'identificazione dei record, ma offrono anche molti altri vantaggi. Verrà illustrato come eseguire un'infinita serie di attività, sia semplici che complesse, considerate poco pratiche o pressoché impossibili in SQL Server 2000, ma ora consentite in SQL Server 2005 grazie al supporto per l'integrazione del CLR (Common Language Runtime).
Le espressioni regolari non sono una novità per SQL. Oracle ha introdotto le espressioni regolari incorporate nella versione 10g e numerose soluzioni di database open source utilizzano una sorta di libreria di espressioni regolari. In effetti, le espressioni regolari potevano essere utilizzate anche nelle versioni precedenti di SQL Server, ma in modo decisamente poco efficace.
Utilizzando la stored procedure sp_OACreate, era possibile utilizzare qualsiasi oggetto di automazione OLE che implementasse le espressioni regolari; tuttavia, era necessario prima creare un oggetto COM, quindi effettuare almeno una chiamata IDispatch e, infine, eliminare l'oggetto. Nella maggior parte dei casi, il tutto si rivelava di scarsa efficacia e provocava troppi problemi di prestazioni. L'unica alternativa consisteva nella creazione di una stored procedure estesa. È ora invece disponibile SQLCLR, una funzione definita dall'utente (UDF) CLR che consente di creare una serie di funzioni di maggiore efficacia e che genera meno errori utilizzando Microsoft .NET Framework.
Funzioni definite dall'utente CLR
Le funzioni definite dall'utente CLR sono semplicemente dei metodi statici (funzioni condivise in Visual Basic) definiti in un assembly .NET. Per utilizzare gli oggetti SQLCLR, è necessario registrare l'assembly con SQL Server utilizzando la nuova istruzione CREATE ASSEMBLY e, quindi, creare ciascun oggetto puntando alla relativa implementazione all'interno dell'assembly. Per le funzioni, l'istruzione CREATE FUNCTION ora supporta anche la creazione delle funzioni definite dall'utente CLR. Per semplificare le cose, Visual Studio 2005 gestisce automaticamente tutti i processi di registrazione quando viene utilizzato un progetto SQL Server. Questo tipo di progetto è diverso dalla maggior parte dei progetti di Visual Studio dato che, quando si tenta di eseguire il debug (o di eseguire l'avvio senza il debug), il progetto viene ricompilato e l'assembly risultante, insieme a tutti gli oggetti SQLCLR in esso definiti, viene distribuito e registrato con SQL Server. L'IDE esegue quindi lo script di test designato per il progetto. È possibile impostare punti di interruzione sia nello script SQL che nel codice .NET, semplificando il tal modo il processo di debug.
L'aggiunta di una funzione avviene in modo simile all'aggiunta di una nuova classe a un qualsiasi altro tipo di progetto. È sufficiente aggiungere un nuovo elemento al progetto e selezionare Funzione definita dall'utente quando richiesto. Il nuovo metodo viene aggiunto alla classe parziale contenente tutte le funzioni. Al nuovo metodo sarà applicato anche un attributo SqlFunction. Viene utilizzato da Visual Studio per creare le istruzioni SQL necessarie per la registrazione della funzione. I campi IsDeterministic, IsPrecise, DataAccess e SystemDataAccess in SqlFunction vengono utilizzati da SQL Server per un'infinità di scopi.
Corrispondenza di modelli
Stabilire se una stringa corrisponde a un modello è il modo più semplice per utilizzare le espressioni regolari e, come mostrato nella figura 1, è un'operazione davvero semplice.
Figure 1 Corrispondenza delle stringhe
public static partial class UserDefinedFunctions
{
public static readonly RegexOptions Options =
RegexOptions.IgnorePatternWhitespace |
RegexOptions.Singleline;
[SqlFunction]
public static SqlBoolean RegexMatch(
SqlChars input, SqlString pattern)
{
Regex regex = new Regex( pattern.Value, Options );
return regex.IsMatch( new string( input.Value ) );
}
}
In primo luogo, viene utilizzato il campo Opzioni per archiviare le opzioni delle espressioni regolari per le funzioni. In questo caso, sono state selezionate le opzioni RegexOptions.SingleLine e RegexOptions.IgnorePatternWhitespace. La prima specifica la modalità di riga singola, mentre la seconda elimina uno spazio vuoto dall'espressione regolare e consente commenti contrassegnati con un cancelletto. Un'altra opzione da utilizzare con attenzione è RegexOption.Compiled. È possibile ottenere un significativo miglioramento delle prestazioni grazie all'opzione di compilazione per le espressioni utilizzate di frequente, sempre che non ve ne siano troppe. Le espressioni che vengono utilizzate continuamente dovrebbero essere assolutamente compilate. Tuttavia, per le espressioni regolari utilizzate di rado, si consiglia di non utilizzare l'opzione di compilazione in quanto provoca un aumento dei costi di avvio e un overhead di memoria. Pertanto, è possibile potenziale la funzione RegexMatch generica con un parametro aggiuntivo che indichi se l'espressione deve essere compilata o meno; in questo modo, è possibile indicare di volta in volta se varrà la pena ottenere un overhead di memoria beneficiando di un miglioramento delle prestazioni.
Dopo aver specificato le opzioni RegexOptions da utilizzare, viene definita la funzione RegexMatch, utilizzando il tipo di dati SqlChars anziché SqlString. Il tipo di dati SqlString esegue la conversione in nvarchar(4000), mentre SqlChars la esegue in nvarchar(max). La nuova funzionalità Dimensioni max consente un'estensione delle stringhe oltre il limite di 8.000 byte tipico di SQL Server 2000. Nel corso dell'articolo, il tipo nvarchar(max) verrà utilizzato nel modo più generico possibile e in modo che garantisca il massimo livello di flessibilità. Tuttavia, è possibile migliorare considerevolmente le prestazioni utilizzando nvarchar(4.000) se tutte le stringhe pertinenti contengono meno di 4.000 caratteri. È necessario valutare con attenzione le proprie esigenze e comportarsi di conseguenza.
Il codice restante nel metodo non presenta alcuna difficoltà. Viene creata un'istanza Regex con le opzioni definite e, quindi, vengono utilizzati il modello e il metodo IsMatch forniti per stabilire se l'input specificato corrisponde al modello in questione. A questo punto, è necessario aggiungere una semplice query allo script di test:
select dbo.RegexMatch( N'123-45-6789', N'^\d{3}-\d{2}-\d{4}$' )
Il modello incluso in questa istruzione è un semplice test per un numero di previdenza sociale. Impostare un punto di interruzione per la nuova query e, quindi, avviare il debug per la funzione. Questa funzione consente di eseguire diversi test ma è necessario prestare attenzione ad alcuni punti troppo spesso non considerati. Ad esempio, è estremamente importante utilizzare convenzioni di denominazione coerenti all'interno di un database, mentre si rivela alquanto difficile scrivere una query che verifichi la conformità di tutte le stored procedure alle linee guida dell'organizzazione. La funzione RegexMatch è in grado di semplificare ancora di più questa attività. Ad esempio, il seguente test di query esegue questa attività:
select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = N'PROCEDURE'
and dbo.RegexMatch( ROUTINE_NAME,
N'^usp_(Insert|Update|Delete|Select)([A-Z][a-z]+)+$' ) = 0
Questa query verifica che ogni stored procedure abbia il prefisso "usp_" seguito dal termine "Insert", "Update", "Delete" o "Select", a sua volta seguito da almeno un nome di entità. Verifica anche che ogni parola inclusa nel nome dell'entità inizi con la lettera maiuscola. Confrontare le quattro righe con questa versione estremamente semplificata che utilizza solo le funzioni incorporate:
select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = N'PROCEDURE'
and ( LEN( ROUTINE_NAME ) < 11
or LEFT( ROUTINE_NAME, 4 ) <> N'usp_'
or SUBSTRING( ROUTINE_NAME, 5, 6 ) not in
( N'Insert', N'Update', N'Delete', N'Select' ) )
Sebbene vi sia più codice, questa query non presenta numerose funzioni incluse nella versione delle espressioni regolari. In primo luogo, non prevede la distinzione tra maiuscole e minuscole e l'utilizzo, al suo interno, di regole di confronto per l'esecuzione dei test non ne garantisce un corretto funzionamento. In secondo luogo, non esegue test relativi al nome effettivo dell'entità incluso nel nome della routine. Il terzo problema consiste nel fatto che le quattro stringhe verificate nella query hanno tutte una lunghezza di sei caratteri, che ha consentito la semplificazione del codice tramite l'estrazione di una singola sottostringa di sei caratteri, messa poi a confronto con ognuna delle operazioni consentite. In questo esempio specifico ciò non comporta problemi, dato che tutti i nomi di operazione hanno una lunghezza di sei caratteri, ma può esserlo nel caso in cui vengano utilizzati verbi più complessi, ad esempio "Get", "List" o "Find". Tali verbi vengono gestiti senza difficoltà dalla funzione RegexMatch, dal momento che rappresentano delle semplici alternative aggiunte a quelle già presenti.
La convalida è uno degli utilizzi più comuni delle espressioni regolari e può riguardare gli elementi più svariati, da un numero di telefono a un CAP o a un formato di numero di account personalizzato. A tale scopo, il vincolo CHECK si rivela l'ideale, come mostrato dalla seguente definizione di tabella.
CREATE TABLE [Account]
(
[AccountNumber] nvarchar(20) CHECK (dbo.RegexMatch(
[AccountNumber], '^[A-Z]{3,5}\d{5}-\d{3}$' ) = 1),
[PhoneNumber] nchar(13) CHECK (dbo.RegexMatch(
[PhoneNumber], '^\(\d{3}\)\d{3}-\d{4}$' ) = 1),
[ZipCode] nvarchar(10) CHECK (dbo.RegexMatch(
[ZipCode], '^\d{5}(\-\d{4})?$' ) = 1)
)
La colonna AccountNumber viene convalidata sulla base di una convenzione arbitraria iniziante con un numero di lettere compreso tra tre e cinque, seguite da cinque numeri, quindi da un trattino e, infine, da altri tre numeri. Sia i numeri di telefono che i CAP vengono convalidati sulla base dei formati di numero di telefono e di CAP standard. La funzione RegexMatch fornisce numerose funzionalità a SQL Server, ma l'implementazione delle espressioni regolari in .NET ne offre molte di più, come verrà illustrato a breve.
Estrazione dei dati
È possibile utilizzare le funzioni di raggruppamento delle espressioni regolari per estrarre dei dati da una stringa. La funzione RegexGroup fornisce tale funzionalità a T-SQL:
[SqlFunction]
public static SqlChars RegexGroup(
SqlChars input, SqlString pattern, SqlString name )
{
Regex regex = new Regex( pattern.Value, Options );
Match match = regex.Match( new string( input.Value ) );
return match.Success ?
new SqlChars( match.Groups[name.Value].Value ) : SqlChars.Null;
}
Questa funziona crea un oggetto Regex, in modo analogo alla funzione RegexMatch. Tuttavia, anziché verificare la presenza di una corrispondenza, viene creato un oggetto Match per la prima corrispondenza trovata nella stringa di input. L'oggetto Match consente di recuperare il gruppo specificato. Se non viene trovata alcuna corrispondenza all'interno dell'input, viene restituito un valore null. È possibile fare ricorso a questa funzione anche nel caso in cui si desideri utilizzare gruppi numerati anziché denominati. È sufficiente passare un valore intero alla funzione all'interno del codice SQL e ne verrà eseguito il cast implicito a un nvarchar; in questo modo, verrà restituito il gruppo appropriato.
È possibile utilizzare la funzione RegexGroup in un elenco SELECT per estrarre specifiche informazioni da determinati dati. Ad esempio, nel caso di una colonna contenente un URL, è ora possibile analizzare con semplicità l'URL per identificarne i singoli componenti. Questa query utilizza il raggruppamento per individuare ogni singolo server archiviato nella colonna Url della tabella UrlTable.
select distinct dbo.RegexGroup( [Url],
N'https?://(?<server>([\w-]+\.)*[\w-]+)', N'server' )
from [UrlTable]
È possibile utilizzare questa funzione anche all'interno delle colonne calcolate. La seguente definizione di tabella suddivide gli indirizzi di posta elettronica nelle colonne mailbox e domain.
CREATE TABLE [Email]
(
[Address] nvarchar(max),
[Mailbox] as dbo.RegexGroup( [Address],
N'(?<mailbox>[^@]*)@', N'mailbox' ),
[Domain] as dbo.RegexGroup( [Address], N'@(?<domain>.*)', N'domain' )
La colonna mailbox restituirà la casetta postale o il nome utente relativo all'indirizzo di posta elettronica. La colonna domain restituirà il dominio relativo all'indirizzo di posta elettronica.
Archiviazione dei modelli
Tutti i modelli utilizzati da queste funzioni sono delle semplici stringhe e, pertanto, possono essere archiviati in una tabella all'interno del database. La maggior parte dei database in cui sono archiviati dati internazionali presenta una tabella rappresentante i vari paesi. Aggiungendo altre colonne a tale tabella, è possibile archiviare modelli di convalida specifici per i diversi paesi. In questo modo, il vincolo applicato a una riga address varierà in base al paese corrispondente a tale riga.
Nei database che archiviano automaticamente i dati senza l'intervento dei client, è generalmente inclusa una tabella rappresentante un client. Tale tabella può essere utilizzata per archiviare i modelli di raggruppamento che consentono di descrivere il modo in cui vengono archiviati nel database i dati dei client non elaborati; in questo modo, è possibile creare colonne calcolate per estrarre dai dati dei client quei dati effettivamente necessari. Ad esempio, se ciascuno dei client dispone di schemi univoci per i numeri di account e si ha necessità solo di alcune parti di tali numeri, è possibile creare senza difficoltà un'espressione che consenta di estrarre i dati appropriati per ciascun client.
Corrispondenze
Anziché determinare se una stringa corrisponde a un modello, è preferibile a volte estrarre ogni singola corrispondenza. In precedenza, questo tipo di estrazione richiedeva di iterare le sezioni di una stringa tramite cursore. Oltre alla lentezza del processo, questo metodo comportava anche notevole difficoltà nella comprensione e gestione del codice. Le espressioni regolari rappresentano uno strumento più efficace per eseguire tale operazione. Ora il problema riguarda il modo in cui restituire tutti i dati necessari all'interno di un costrutto SQL. Le funzioni con valori di tabella sono la soluzione ideale per questo problema.
Tali funzioni presentano delle similitudini con le funzioni precedenti, ma sono diverse per due aspetti specifici. In primo luogo, gli attributi applicati al metodo devono dichiarare per intero la struttura della tabella restituita. In secondo luogo, risultano coinvolti due metodi. Il primo restituisce un oggetto enumerabile anziché il risultato effettivo della funzione. Il secondo metodo viene passato agli oggetti enumerati per popolare i campi di ogni riga. Ogni valore recuperato tramite l'enumeratore dovrebbe corrispondere a una riga del gruppo di risultati. L'interfaccia ICollection in .NET Framework implementa IEnumerable e ciò indica che il primo metodo può restituire qualsiasi raccolta. La classe Regex contiene un metodo Matches che restituisce un MatchCollection da poter utilizzare. Il problema con il MatchCollection è che implica la necessità di elaborare l'intera stringa prima della restituzione del metodo Matches. SQL Server include delle ottimizzazioni che dipendono dalla possibilità di eseguire l'elaborazione quando necessario; pertanto, anziché restituire in anticipo l'intera raccolta, si preferisce scrivere l'enumeratore che consente la restituzione di ciascuna corrispondenza quando necessario. Questa decisione dipende effettivamente da come viene utilizzata la funzione e dovrebbe essere testata in modo accurato prima dell'ottimizzazione dell'enumeratore.
Il codice della figura 2 mostra l'enumeratore. La classe MatchNode esegue il wrapping di una singola corrispondenza nella stringa e, al contempo, tiene traccia della relativa posizione all'interno dell'insieme di corrispondenze restituito. La classe MatchIterator è enumerabile e gestisce l'elaborazione delle espressioni regolari. Utilizza la nuova parola chiave yield per creare l'enumeratore in modo estremamente più semplice di quanto fosse possibile con le precedenti versioni del framework. Restituirà ogni corrispondenza rilevata all'interno della stringa di input come richiesto.
Figure 2 Oggetto enumerabile personalizzato per le corrispondenze
internal class MatchNode
{
private int _index;
public int Index { get{ return _index; } }
private string _value;
public string Value { get { return _value; } }
public MatchNode( int index, string value )
{
_index = index;
_value = value;
}
}
internal class MatchIterator : IEnumerable
{
private Regex _regex;
private string _input;
public MatchIterator( string input, string pattern )
{
_regex = new Regex( pattern, UserDefinedFunctions.Options );
_input = input;
}
public IEnumerator GetEnumerator()
{
int index = 0;
Match current = null;
do
{
current = (current == null) ?
_regex.Match( _input ) : current.NextMatch( );
if (current.Success)
{
yield return new MatchNode( ++index, current.Value );
}
}
while (current.Success);
}
}
Il codice della figura 3 definisce la UDF CLR con valori di tabella. Il metodo RegexMatches restituisce un nuovo MatchIterator. SqlFunctionAttribute nel metodo RegexMatches include anche alcune proprietà aggiuntive. La proprietà TableDefinition è impostata sulla definizione di tabella della funzione. La proprietà FillRowMethodName è impostata sul nome del metodo per chiamare ciascuna iterazione dell'oggetto enumerabile restituito. In questo caso, si tratta del metodo FillMatchRow.
Figure 3 UDF CLR con valori di tabella per le corrispondenze
[SqlFunction( FillRowMethodName = "FillMatchRow",
TableDefinition = "[Index] int,[Text] nvarchar(max)" )]
public static IEnumerable RegexMatches(SqlChars input, SqlString pattern)
{
return new MatchIterator( new string( input.Value ), pattern.Value );
}
[SuppressMessage( "Microsoft.Design", "CA1021:AvoidOutParameters" )]
public static void FillMatchRow( object data,
out SqlInt32 index, out SqlChars text )
{
MatchNode node = (MatchNode)data;
index = new SqlInt32( node.Index );
text = new SqlChars( node.Value.ToCharArray( ) );
}
Per ogni iterazione della classe MatchIterator, viene passata una classe MatchNode al metodo FillMatchRow come suo primo argomento. I restanti parametri del metodo FillMatchRow devono essere dichiarati come parametri Out e devono corrispondere alla definizione di tabella specificata nella prima funzione. La funzione FillMatchRow utilizza semplicemente le proprietà della classe MatchNode per popolare i campi.
Grazie a questa funzione, è possibile infine estrarre senza alcuna difficoltà i dati desiderati da una stringa. Per descrivere l'utilizzo della funzione RegexMatches, verrà elaborata una stringa in grado di stabilire in che modo vengono incluse numerose distinte parole utilizzando questa query:
declare @text nvarchar(max), @pattern nvarchar(max)
select
@text = N'Here are four words.',
@pattern = '\w+'
select count(distinct [Text])
from dbo.RegexMatches( @text, @pattern )
Si tratta di un esempio molto semplice. Mostra alcune potenzialità correlate all'utilizzo della funzione; tuttavia, eliminando la parola chiave distinct, restituisce il numero totale di parole di una stringa. Esistono numerosi siti Web che impongono una restrizione arbitraria relativamente alla lunghezza del testo immesso. Con questo tipo di testo e con la nuova notazione nvarchar(max), diventa possibile limitare l'input a uno specifico numero di parole. Questo tipo di query si rivela utile per le più svariate esigenze di elaborazione analitica, ma la funzione RegexMatches può essere utilizzata anche per attività più comuni. Sfortunatamente, questo genere di query rappresenta anche un utilizzo eccessivo delle espressioni regolari. L'operazione di separazione eseguita tramite l'espressione "\w+" in questo caso potrebbe essere svolta con la stessa semplicità grazie al metodo String.Split che, d'altra parte, garantirebbe anche una maggiore rapidità. Le espressioni regolari costituiscono uno strumento estremamente potente, ma è necessario utilizzarle solo quando realmente necessario: in specifiche situazioni si dovrebbero utilizzare degli strumenti più semplici che potrebbero garantire delle prestazioni migliori.
Nei forum MSDN, è spesso possibile imbattersi in domande relative ai metodi da utilizzare per passare un elenco di valori a una stored procedure. È anche possibile venire a conoscenza di metodi complicati per poter analizzare un simile elenco rendendolo un elenco effettivo per l'identificazione dei record correlati. La funzione RegexMatches garantisce un approccio molto più netto.
declare @pattern nvarchar(max), @list nvarchar(max)
select @pattern = N'[^,]+', @list = N'2,4,6'
select d.* from [Data] d
inner join dbo.RegexMatches( @list, @pattern ) re
on d.[ID] = re.[Text]
Il modello crea una corrispondenza con qualsiasi gruppo di caratteri in cui non sono incluse virgole. Data una tabella denominata Data con una colonna di valori interi denominata ID, questa query restituirà ciascun record identificato nell'elenco. Ciò si dimostra di maggiore utilità quando si prendono in considerazione le funzioni di cast implicito all'interno di SQL Server. La stessa query può essere utilizzata per i tipi di dati a virgola mobile, GUID, data/ora o Integer. Altri metodi di elaborazione di un elenco di valori richiedono più funzioni o stored procedure per rendere l'elenco flessibile. Questa funzione può essere utilizzata anche per gli elenchi con valori non delimitati da virgole. È possibile elaborare anche un elenco con valori separati da spazi, punti e virgola, tabulazioni, ritorni a capo o altri caratteri identificabili.
Estrazione dei dati all'interno delle corrispondenze
In modo analogo a ciò che avviene per la restituzione delle corrispondenze, è possibile estrarre i dati anche da ogni corrispondenza. Tentare di riuscirci utilizzando SQL si rivela estremamente difficile. Generalmente, questo tipo di attività viene implementata all'interno di un'applicazione anziché nel database e ciò provoca dei problemi in quanto ogni applicazione che utilizza il database dovrebbe implementare l'elaborazione richiesta. In scenari di questo tipo, si consiglia di implementare questa funzionalità all'interno delle stored procedure.
Come nel caso dell'implementazione di RegexMatches, è preferibile utilizzare un oggetto enumerabile personalizzato per restituire informazioni sui gruppi. Il raggruppamento è leggermente più complicato in quanto diventa necessario iterare i gruppi all'interno di ciascuna corrispondenza. Nella figura 4, la classe GroupNode è simile alla classe MatchNode, ad eccezione del fatto che include il nome del gruppo che rappresenta. La classe GroupIterator è simile alla classe MatchIterator, ma include un ciclo aggiuntivo per la restituzione di ciascun gruppo. Ora che si dispone di un oggetto enumerabile, viene definita una funzione con valori di tabella, come è già stato fatto nel caso della funzione RegexMatches.
Figure 4 Oggetto enumerabile personalizzato per i gruppi
internal class GroupNode
{
private int _index;
public int Index { get { return _index; } }
private string _name;
public string Name { get { return _name; } }
private string _value;
public string Value { get { return _value; } }
public GroupNode( int index, string group, string value )
{
_index = index;
_name = group;
_value = value;
}
}
internal class GroupIterator : IEnumerable
{
private Regex _regex;
private string _input;
public GroupIterator( string input, string pattern )
{
_regex = new Regex( pattern, UserDefinedFunctions.Options );
_input = input;
}
public IEnumerator GetEnumerator()
{
int index = 0;
Match current = null;
string[] names = _regex.GetGroupNames();
do
{
index++;
current = (current == null) ?
_regex.Match( _input ) : current.NextMatch( );
if (current.Success)
{
foreach(string name in names)
{
Group group = current.Groups[name];
if (group.Success)
{
yield return new GroupNode(
index, name, group.Value );
}
}
}
}
while(current.Success);
}
}
Nella figura 5, la funzione RegexGroups viene definita come la funzione RegexMatches, ad eccezione del fatto che restituisce un'ulteriore colonna di dati contenente il nome del gruppo all'interno della corrispondenza. Grazie a questa funzione, è ora possibile ricercare più corrispondenze all'interno di una stringa ed estrarre specifiche informazioni da ciascuna corrispondenza.
Figure 5 UDF CLR con valori di tabella per i gruppi
[SqlFunction( FillRowMethodName = "FillGroupRow", TableDefinition =
"[Index] int,[Group] nvarchar(max),[Text] nvarchar(max)" )]
public static IEnumerable
RegexGroups( SqlChars input, SqlString pattern )
{
return new GroupIterator( new string( input.Value ), pattern.Value );
}
[SuppressMessage( "Microsoft.Design", "CA1021:AvoidOutParameters" )]
public static void FillGroupRow( object data,
out SqlInt32 index, out SqlChars group, out SqlChars text )
{
GroupNode node = (GroupNode)data;
index = new SqlInt32( node.Index );
group = new SqlChars( node.Name.ToCharArray( ) );
text = new SqlChars( node.Value.ToCharArray( ) );
}
In genere, quando si utilizzano i database, viene eseguita l'importazione dei dati in diversi formati. L'importazione di file in un formato delimitato da virgole si rivela un'operazione più difficile di ciò che si potrebbe pensare. La maggior parte degli sviluppatori crea un'applicazione che elabora tutte le righe, estrae i dati e, quindi, esegue una stored procedure per ogni riga. Nonostante ciò garantisca dei risultati accettabili, viene ora proposta un'altra soluzione. Se si potesse passare l'intero file a una stored procedure e consentirle di gestire l'intero processo? Generalmente, la realizzazione di questa idea viene considerata troppo complicata ma, grazie alla funzione RegexGroups, è possibile svolgere l'operazione con un'unica query. Ad esempio, si prendano in considerazione i seguenti dati dei clienti.
2309478,Janet Leverling,J
2039748,Nancy Davolio,N
0798124,Andrew Fuller,M
4027392,Robert King,L
Da ogni riga sono necessari tre diversi tipi di dati: il numero cliente di sette cifre, il nome del cliente e il tipo di cliente con un singolo carattere. Con la seguente espressione, è possibile estrarre tutti e tre i tipi di dati.
(?<CustomerNumber>\d{7}),(?<CustomerName>[^,]*),(?<CustomerType>[A-Z])\r?\n
Il problema, in questo caso, consiste nella impossibilità di utilizzare direttamente i risultati restituiti dalla funzione RegexGroups. Anziché utilizzare un cursore per iterare i risultati, è possibile fare ricorso alla funzionalità pivot in SQL Server 2005. Riunendo tutti i dati in un'unica stored procedure, si avranno a disposizione tutti i dati necessari. La stored procedure della figura 6 accetta il testo di un intero file con valori delimitati da virgole contenente fino a 2 GB di dati Unicode. Elabora l'intero file e inserisce ogni riga del file come riga nella tabella Customer. Tutti i file con testo delimitato possono essere elaborati nello stesso modo. Con alcune piccole modifiche del modello, è possibile aggiungere sequenze di escape per supportare le virgole all'interno delle stringhe.
Figure 6 Elaborazione di un file con valori delimitati da virgole
create proc ImportCustomers
(
@file nvarchar(max)
)
as
declare @pattern nvarchar(max)
set @pattern = N'(?<CustomerNumber>\d{7}),
(?<CustomerName>[^,]*),(?<CustomerType>[A-Z])\r?\n'
insert [Customer]
(
[CustomerNumber],
[CustomerName],
[CustomerType]
)
select
f.[CustomerNumber],
f.[CustomerName],
f.[CustomerType]
from dbo.RegExGroups( @file, @pattern ) regex
pivot
(
max([Text])
for [Group]
in ( [CustomerNumber], [CustomerName], [CustomerType] )
) as f
Tuttavia, anche in questo caso la procedura dimostra che esistono diversi modi per eseguire la stessa attività e, a volte, le espressioni regolari non rappresentano la soluzione migliore. In questo esempio, l'utilizzo della funzione pivot consente di annullare effettivamente tutto il lavoro eseguito da RegexGroups per restituire i dati nel formato raggruppato speciale. Sarebbe stato possibile inserire i dati direttamente nella tabella utilizzando un funzione con valori di tabella molto più semplice e rapida che avrebbe semplicemente letto ogni riga, eseguito String.Split sulle virgole e restituito ciascuna riga.
Conclusioni
Sebbene queste funzioni siano estremamente efficaci, non si rivelano complete. Esistono numerose alternative in grado di determinare la modalità di esecuzione delle corrispondenze. Se il confronto nel database avviene senza distinzione tra maiuscole e minuscole, è preferibile che anche le funzioni eseguano le corrispondenze in questo modo. Potrebbe essere necessaria la funzione di acquisizione esplicita per ridurre alcuni gruppi di risultati. L'opzione Multiriga consente di creare modelli più precisi per alcune attività. È anche possibile creare un tipo definito dall'utente per passare le opzioni richieste a ciascuna delle funzioni e consentire l'utilizzo di un diverso gruppo di opzioni per l'esecuzione di ogni funzione.
Si dovrebbe inoltre essere consapevoli del fatto che l'elaborazione del testo comporta dei problemi di localizzazione. Ad esempio, la classe Regex di .NET Framework riconosce molti più caratteri rispetto a quelli dell'alfabeto latino utilizzati negli esempi; pertanto, è necessario prestare particolare attenzione durante lo sviluppo dei modelli per i database che utilizzano dati internazionali.
Naturalmente, come menzionato più volte nell'articolo, si dovrebbe fare ricorso alle espressioni regolari solo in caso di reale necessità nonostante siano degli strumenti estremamente efficaci. Alcune attività possono essere eseguite in modo più rapido e semplice con set di strumenti di più elementari.
Per maggiore semplicità, gli esempi forniti non fanno riferimento alle attività di convalida e gestione degli errori, che invece dovrebbero essere incluse in qualsiasi sistema di produzione. Ciascuno degli input della funzione dovrebbe essere convalidato e, in base alle proprie esigenze, si stabiliranno le risposte agli input di stringa null o vuota. La classe Regex consente di generare eccezioni quando il modello non può essere analizzato o le opzioni non sono valide. Tali eccezioni devono essere gestite normalmente.
L'uso congiunto delle espressioni regolari e di SQL garantisce innumerevoli strumenti alternativi per l'elaborazione dei dati. Utilizzare queste funzioni può ridurre il tempo necessario per aggiungere la funzionalità al database e semplificare la gestione del sistema. Qualsiasi database potrebbe utilizzare le espressioni regolari e si consiglia di sperimentare queste funzioni per scoprire modalità di utilizzo nuove e più creative.
David Banister è sviluppatore software senior per una società di contabilità tra le più affermate di Atlanta. Da diversi anni si impegna nella scrittura di software. Nel tempo libero, si dedica alla lettura delle specifiche di linguaggio ECMA, gioca a tennis e sostiene i gruppi locali.