Matrizes no SQL Server 2005 – Parte III

Boa Noite Pessoal,

Dando continuidade ao assunto de matrizes no SQL Server 2005, hoje falarei sobre a utilização do CLR para trabalhar com um tipo de dados Array. Dessa vez não utilizei a expressão "simular um tipo de dados Array". Isso propositalmente, visto que o CLR permite a construção de tipos cujo escopo possibilita ir além de derivação de tipos mais simples (User Data Types).

Um pouco sobre CLR

CLR é o acrônimo de Common Language Runtime. O CLR é a peça mais fundamental do Framework .NET e a partir do SQL Server 2005 é possível utilizar esse framework diretamente no SQL Server. Isso possibilita a escrita de stored procedures, triggers, functions em alguma linguagem suportada pelo Framework .NET (tipicamente o VB.NET e o C#), a criação de agregações e tipos próprios.

O T-SQL é uma linguagem poderosa, mas fundamentalmente criada para facilitar a recuperação e gravação de dados. Embora o T-SQL possua uma vasta coleção de comandos que complementam as operações DML tradicionais (SELECT, INSERT, UPDATE, DELETE) ele jamais chegará próximo de linguagens de programação mais tradicionais. Estruturas de laço e repetição (WHILE) bem como de controle de fluxo (IF, ELSE) estão presentes, mas dificilmente veremos tópicos mais avançados como a presença de funções de manipulação de texto (split, insert, etc), um bom suporte a expressões regulares e funções de rede como hostname, ping, telnet, etc.

Isso é perfeitamente compreensível já que o objetivo primordial de um dialeto da SQL como o T-SQL é primar pela recuperação e gravação de dados da melhor forma possível e não tornar-se uma linguagem de programação para competir com o C#, Java, etc. Ainda assim, existem situações que podem exigir outras funcionalidades que não a recuperação e gravação de dados. Se for necessário que uma procedure conecte-se a um WebService para efetuar uma validação ? Se for preciso realizar o cálculo do coeficiente de correlação entre duas séries temporais ? Como fazer que o SQL Server aproveite os métodos de uma classe presente em um componente de terceiros para realizar a geração de arquivos consolidados ?

As situações descritas acima deixam o T-SQL em cheque visto que ele não possui recursos nativos para lidar com as mesmas. Ainda que o implementador tivesse um conhecimento fora do comum e conseguisse codificar algo em T-SQL para atender essas situações, provavelmente o código seria difícil de manter além de pouco performático. Para situações como as descritas, codificar algo suportado pelo Framework .NET é uma alternativa a se considerar.

Há vários prós e contras utilizar o CLR e sem dúvida existem áreas de sombreamento entre o CLR e o T-SQL. O link abaixo pode esclarescer muitas dúvidas de quando utilizar o T-SQL e quando utilizar o CLR.

Como e quando programar em CLR no Microsoft SQL Server 2005
http://www.microsoft.com/brasil/msdn/Tecnologias/sql/CLRSQLServer2005.mspx

Não é o foco descrever o uso do CLR e suas possibilidades no banco de dados. Há várias questões a se considerar como desempenho, segurança, versionamento, etc. Por hora apenas utilizarei o CLR para criar um tipo de dados Array de forma mais nativa do que as simulações apresentadas nos tópicos anteriores. Também ressalto que meus conhecimentos em CLR não são avançados e possivelmente haverá códigos mais eficientes do que o que eu proponho (se alguém tiver algum que me envie). O foco é demonstrar que o CLR também pode ser utilizado para trabalhar com arrays.

Uma pequena degustação

O primeiro passo é habilitar o uso do CLR, que por padrão, vem desabilitado. Podemos fazer isso através do SQL Server Surface Area Configuration ou via T-SQL.

— Habilita o uso do CLR na instância do SQL Server
EXEC sp_configure ‘clr enabled’, 1

— Força que as alterações tenham efeito sem a necessidade de reiniciar o serviço
reconfigure with override

A utilização do CLR normalmente é codificada através do Visual Studio. Entretando, ao contrário do que possa parecer, não é necessário ter o Visual Studio para compilar código CLR e carregá-lo no SQL Server já que o SQL Server 2005 utiliza o Framework .NET também e possui o compilador. Ainda que o SQL Server 2005 não estivesse presente, o próprio Windows Update faz a instalação do Framework 2.0 (atualmente estamos no 3.5). O script abaixo cria a function para retornar em um formato tabular uma seqüência de parâmetros delimitada por algum caractér.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Text;
using System.Collections;

public partial class UserDefinedFunctions
{
    [SqlFunction(
            FillRowMethodName = "PopularLinha",
           TableDefinition = "Elemento nvarchar(max)")]
    public static IEnumerable fnSplit(SqlString str, string separador)
    {
        string[] strArray;
        if (!str.IsNull)
        {
            strArray = str.Value.Split(separador.ToCharArray());
            return strArray;
        }
        else
            return "";
    }

    public static void PopularLinha(Object obj, out string Elemento)
    {
        Elemento = (string)obj;
    }

};

Para gerar o Assembly a partir desse código siga os seguintes passos:

  • Copiar e colar o código no bloco de notas
  • Salvar o arquivo como fnSplit.cs em algum diretório (ex: C:\Fontes\)
  • Abrir um prompt de comando
  • Navegar até o diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou superior se desejar)
  • Rodar o comando csc /target:library C:\Fontes\fnSplit.cs (supondo que o arquivo esteja em C:\Fontes\)
  • Localizar o arquivo fnSplit.dll na pasta utilizada no prompt
  • Mover a DLL para um local mais apropriado (ex: C:\Executaveis\)

Uma vez que o Assembly esteja compilado, resta carregá-lo para o SQL Server, criar a function com base no Assembly e utilizá-la.

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY Funcoes FROM ‘C:\Executaveis\fnSplit.dll’

— Cria uma Function baseada no Assembly
CREATE FUNCTION dbo.fnSplit(@Array NVARCHAR(MAX), @Separador NCHAR(1))
RETURNS TABLE (Elemento NVARCHAR(MAX))
AS
EXTERNAL NAME
Funcoes.UserDefinedFunctions.fnSplit
GO

— Utiliza a função definida no Assembly
SELECT Elemento FROM dbo.fnSplit(‘SP;RJ;DF’,‘;’)

Dada algumas limitações do CLR, irei trabalhar um código para criação de um tipo Array fixo em três elementos.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct TArray : INullable
{
    private bool Nulo;

    public SqlInt16 Pos1;
    public SqlInt16 Pos2;
    public SqlInt16 Pos3;

    public override string ToString()
    {
        return
            Pos1.ToString() + ";" + Pos2.ToString() + ";" + Pos3.ToString() + ";";
    }

    public bool IsNull
    {
        get
        {
            return Nulo;
        }
    }

    public static TArray Null
    {
        get
        {
            TArray TA = new TArray();
            TA.Nulo = true;
            return TA;
        }
    }

    public static TArray Parse(SqlString s)
    {
        try
        {
            if (s.IsNull)
                return Null;
            TArray TA = new TArray();
            string[] SA = s.ToString().Split(";".ToCharArray());

            TA.Pos1 = SqlInt16.Parse(SA[0]);
            TA.Pos2 = SqlInt16.Parse(SA[1]);
            TA.Pos3 = SqlInt16.Parse(SA[2]);

            return TA;
        }
        catch
        {
            throw new ArgumentException("A inicialização da matriz não está correta");
        }

    }

    public SqlDouble Media()
    {
        return (Pos1 + Pos2 + Pos3) / 3;
    }

    public SqlInt16 MenorValor()
    {
        SqlInt16 res = Pos1;
        if (Pos2 <= Pos1)
        {
            res = Pos2;
            if (Pos3 <= Pos2)
                res = Pos3;
        }
        else
        {
            if (Pos3 <= Pos1)
                res = Pos3;
        }

        return res;
    }

    public SqlInt16 MaiorValor()
    {
        SqlInt16 res = Pos1;
        if (Pos2 >= Pos1)
        {
            res = Pos2;
            if (Pos3 >= Pos2)
                res = Pos3;
        }
        else
        {
            if (Pos3 >= Pos1)
                res = Pos3;
        }

        return res;
    }

}

A compilação desse Assembly deve ser feita seguindo-se exatamente os mesmos procedimentos para a compilação do Assembly anterior. Após a compilação estar concluída e o Assembly tiver sido copiado para um diretório mais apropriado, poderemos trabalhar com ele. O script abaixo demonstra a utilização de um tipo Array baseado em .NET.

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY Tipos FROM ‘C:\Executaveis\TipoArray.dll’

— Cria um novo tipo Array a partir do Assembly
CREATE TYPE dbo.Array
EXTERNAL NAME Tipos.TArray

— Cria uma variável do tipo Array
DECLARE @Array dbo.Array

— Inicializa o Array
SET @Array = CAST(’15;20;50′ AS dbo.Array)

— Mostra os valores do Array
SELECT
    @Array.Pos1 AS Elemento1, @Array.Pos2 AS Elemento2, @Array.Pos3 AS Elemento3,
    @Array.Media() AS Media, @Array.MenorValor() AS MenorValor, @Array.MaiorValor() AS MaiorValor

O interessante é que uma vez que o tipo Array exista no banco de dados, o mesmo pode ser utilizado inclusive em tabelas e cláusulas SQL. O script abaixo demonstra como criar uma coluna do tipo Array em uma tabela fictícia de alunos e notas escolares (supondo que cada aluno tenha exatamente três notas).

— Cria uma tabela de Alunos
CREATE TABLE Alunos (NomeAluno VARCHAR(50), Notas dbo.Array)

— Insere registros
INSERT INTO Alunos VALUES (‘Pedro’,CAST(‘5;7;9’ AS dbo.Array))
INSERT INTO Alunos VALUES (‘Leandro’,CAST(‘4;10;9’ AS dbo.Array))
INSERT INTO Alunos VALUES (‘Michelle’,CAST(‘8;9;9’ AS dbo.Array))

— Retorna o nome dos alunos e suas médias
SELECT NomeAluno, Notas.Media() AS Media FROM Alunos

— Retorna os alunos cuja maior nota seja igual a 9
SELECT NomeAluno, Notas.MaiorValor() AS MaiorNota, Notas.Media() AS Media
FROM Alunos WHERE Notas.MaiorValor() = 9

— Retorna todos os dados
SELECT NomeAluno, Notas FROM Alunos

A implementação CLR realmente criou um tipo de dados Array nativo uma vez que ele pode ser utilizado tanto como variável quanto colunas em uma tabela assim como qualquer outro tipo (VARCHAR, INT, SMALLDATETIME, etc). O exemplo postado se assemelha muito a uma tabela aninhada, ou seja, uma tabela dentro de outra tabela. Uma vez que o SQL Server tenha o tipo de dados TABLE, mas esse não possa ser utilizado como coluna, essa seria uma abordagem alternativa para tornar isso possível. É um recurso muito parecido com o Nested Table do Oracle.

Embora seja uma abordagem interessante (e bem incomum), isso não quer dizer que seja a melhor solução. Utilizar uma tabela de notas com uma Foreign Key apontando para Alunos ainda seria o mais correto do ponto vista relacional. Se a matriz for fixa, é mais vantajoso criar uma coluna para cada elemento (no caso três colunas de notas). O tipo de dados criado não é muito performático, visto que não pode ser indexado da forma como foi concebido. A visualização sempre dependerá de escolher a propriedade ou o método correto. Isso só reforça que o uso do CLR deve ser feito com muita cautela e que ele não é a substituição do T-SQL, mas sim uma outra alternativa.

Acho que finalizo por aqui minhas dicas sobre T-SQL e Arrays. Aos que desejarem aprofundar-se não deixem de consultar o link abaixo:

Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html

[ ]s,

Gustavo

2 Respostas para “Matrizes no SQL Server 2005 – Parte III

  1. Pingback: Alex Souza

  2. Pingback: “Array” no SQL Server « Alex Souza

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s