Brincando Com SQL Server CLR Integration – Parte I
Actualmente, estou a trabalhar com uma aplicação que armazena propriedades num saco num coluna SQL Server, no seguinte formato:
[[[name1]]]
value1
[[[name2]]]
value2.1
value2.2
[[[name3]]]
value3
Não me perguntem porque o fizeram assim, Apenas fizeram.
A aplicação descodifica este saco para as suas estruturas internas e tudo funciona bem.
Mas algumas vezes eu gostava de efectuar algumas consultas directamente à base de dados ou extraír alguns relatórios incluindo essas propriedaes e não posso.
Então pensei que este seria um bom caso de uso para a SQL Server CLR Integration. Decidi criar uma CLR Table-Valued Function que me retornasse o saco de propriedades como uma tabela com duas colunas.
Descodificar o texto do saco de propriedades pode ser facilmente alcançado usando uma simples expressão regular:
new Regex(
string.Format(@"(?<Name>(?<=\[\[\[).*(?=\]\]\]{0}))\]\]\]{0}(?<Value>(([\s\S]*?(?={0}\[\[\[))|([\s\S]*?(?={0}$))))", Environment.NewLine),
RegexOptions.Multiline | RegexOptions.ExplicitCapture | RegexOptions.CultureInvariant | RegexOptions.Compiled);
O Expresso da Ultrapico foi uma grande ajuda para criar esta expressão regular.
Para quem não sabe, a forma como uma CLR Table-Valued Function é implementada é usando um método inicial que recebe os parâmetros de entrada e retorna um IEnumerable e um método que recebe os itens do enumerador e retorna, como parâmetros de saída, as células da linha correspondente.
Como se tratam de pares nome-valor do tipo string, decidi usar instÂncias de )>) Structure" href="http://msdn.microsoft.com/library/5tbh8a42.aspx" target=_blank>KeyValuePair<string, string> para armazenar os itens e o construír o enumerador foi tão simples como:
private static IEnumerable<KeyValuePair<string, string>> ShortPropsEnumerable(string shortPropsText)
{
return from Match m in shortPropsRegex.Matches(shortPropsText)
select new KeyValuePair<string, string>(m.Groups["Name"].Value, m.Groups["Value"].Value);
}
E a implementação da CLR Table-Valued Function foi tão simples como:
[Microsoft.SqlServer.Server.SqlFunction(
Name = "ShortPropsToTable",
FillRowMethodName = "ShortPropsToTableFillRow",
TableDefinition = "Name NVARCHAR(4000), Value NVARCHAR(4000)",
IsDeterministic = true,
IsPrecise = false,
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None)]
public static IEnumerable ShortPropsToTable(string shortPropsText)
{
return ShortPropsEnumerable(shortPropsText);
}
public static void ShortPropsToTableFillRow(object item, out SqlChars name, out SqlChars value)
{
KeyValuePair<string, string> shortProp = (KeyValuePair<string, string>)item;
name = new SqlChars(shortProp.Key);
value = new SqlChars(shortProp.Value);
}
Para usar esta função numa base de dados SQL Server são necessários alguns passos:
-
Carregar a assembly a base de dados:
CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = SAFE
GO
-
CREATE FUNCTION [dbo].[ShortPropsToTable](@shortPropsText [nvarchar](4000))
RETURNS TABLE (
[Name] [nvarchar](4000) NULL,
[Value] [nvarchar](4000) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [MyAssembly].[ShortProps].[ShortPropsToTable]
GO
-
Habilitar a CLR Integration:
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
E está pronto a usar.
Agora posso fazer consultas ao saco de propriedaes como se fosse uma tabela:
SELECT
e.[ID],
e.[Name],
e.[Class],
e.[Type],
p.[Name],
p.[Value]
FROM
dbo.Entity as e
CROSS APPLY dbo.ShortPropsToTable(e.[ShortProps]) as p
Apenas como curiosidade, para um pouco mais de 50000 linhas (que podem ser obtidas em cerca de 1 segundo no meu laptop), obtive um pouco menos de 630000 propriedades em menos de 40 segundos.
40 segundos podem parece muito quando comparados com 1 segundo, mas gostava de ver tempos melhores usando T-SQL. E desenvolver e testar a TVF em apenas um par de horas.
Se ao menos os DBAs me deixassem usar isto. CLR Integration não está conforme a “política de segurança” da empresa.