Arquivo da categoria: DICAS

Exportando consultas para documentos XML

Boa Noite Pessoal,

Essa será possivelmente uma das minhas últimas postagens de 2011, então vamos para uma dica rápida. Hoje falarei sobre como exportar uma tabela ou consulta para um documento XML através do BCP e do CLR. Já vi algumas postagens que tratam esse assunto, mas proponho um pouco mais que isso. Demonstrarei a exportação mantendo a identação dos elementos no documento XML. Para começar nada melhor que algumas tabelas de exemplo

— Cria um banco para testes
CREATE DATABASE ExportXML
GO

— Muda o contexto
USE ExportXML
GO

— Cria as tabelas
CREATE TABLE Clientes (
    ClienteID INT NOT NULL,
    ClienteNome VARCHAR(100) NOT NULL)

CREATE TABLE Contas (
    ClienteID INT NOT NULL,
    ContaID INT NOT NULL,
    ContaAgencia VARCHAR(6) NOT NULL,
    ContaNumero VARCHAR(9) NOT NULL)

CREATE TABLE Lancamentos (
    ClienteID INT NOT NULL,
    ContaID INT NOT NULL,
    Valor SMALLMONEY NOT NULL,
    Tipo CHAR(1) NOT NULL,
    Data DATE NOT NULL)

— Insere os registros
INSERT INTO Clientes VALUES (1,‘Ronaldo’)
INSERT INTO Clientes VALUES (2,‘Adalton’)
INSERT INTO Clientes VALUES (3,‘Daniela’)
INSERT INTO Clientes VALUES (4,‘Roberta’)

INSERT INTO Contas VALUES (1,1,‘3590-4’,‘97532-8’)
INSERT INTO Contas VALUES (1,2,‘3590-4’,‘856413-0’)
INSERT INTO Contas VALUES (1,3,‘3590-4’,‘948766-0’)
INSERT INTO Contas VALUES (2,1,‘1004-9’,‘974322-8’)
INSERT INTO Contas VALUES (2,2,‘1004-9’,‘15649-X’)
INSERT INTO Contas VALUES (3,1,‘2944-0’,‘7561-2’)

INSERT INTO Lancamentos VALUES (1,1,6500,‘C’,‘20111220’)
INSERT INTO Lancamentos VALUES (1,1,1200,‘D’,‘20111221’)
INSERT INTO Lancamentos VALUES (1,1,2500,‘D’,‘20111221’)
INSERT INTO Lancamentos VALUES (1,2,3500,‘C’,‘20111221’)
INSERT INTO Lancamentos VALUES (1,2,2600,‘C’,‘20111221’)
INSERT INTO Lancamentos VALUES (2,1,3200,‘D’,‘20111222’)
INSERT INTO Lancamentos VALUES (2,1,1100,‘D’,‘20111222’)
INSERT INTO Lancamentos VALUES (2,1,1900,‘C’,‘20111222’)
INSERT INTO Lancamentos VALUES (2,2,5800,‘D’,‘20111223’)
INSERT INTO Lancamentos VALUES (2,2,3200,‘D’,‘20111223’)
INSERT INTO Lancamentos VALUES (2,2,9800,‘C’,‘20111223’)

Agora que temos uma massa de testes, segue um consulta para estruturar esses dados em XML.

SELECT
    Cliente.ClienteNome As Nome,Conta.ContaAgencia As Agencia,
    Conta.ContaNumero As Numero,Lancamento.Data As Data,
    Lancamento.Valor, Lancamento.Tipo
FROM Clientes As Cliente
    LEFT OUTER JOIN Contas As Conta ON Cliente.ClienteID = Conta.ClienteID
    LEFT OUTER JOIN Lancamentos As Lancamento
        ON Conta.ClienteID = Lancamento.ClienteID AND Conta.ContaID = Lancamento.ContaID
FOR XML AUTO, ROOT(‘Lancamentos’)

Posto apenas um parte do documento XML produzido com esse comando:

<Lancamentos>
  <Cliente Nome=Ronaldo>
    <Conta Agencia=3590-4Numero=97532-8>
      <Lancamento Data=2011-12-20Valor=6500.0000Tipo=C/>
      <Lancamento Data=2011-12-21Valor=1200.0000Tipo=D/>
      <Lancamento Data=2011-12-21Valor=2500.0000Tipo=D/>
    </Conta>
    <Conta Agencia=3590-4Numero=856413-0“>
      <Lancamento Data=2011-12-21Valor=3500.0000Tipo=C/>
      <Lancamento Data=2011-12-21Valor=2600.0000Tipo=C/>
    </Conta>
    <Conta Agencia=3590-4Numero=948766-0>
      <Lancamento />
    </Conta>
  </Cliente>
  <Cliente Nome=Adalton>
    <Conta Agencia=1004-9Numero=974322-8>

Para facilitar os trabalhos, vou encapsultar a instrução SELECT em uma View.

CREATE VIEW vLancamentoXML (ResultadoXML) As
SELECT
    Cliente.ClienteNome As Nome,Conta.ContaAgencia As Agencia,
    Conta.ContaNumero As Numero,Lancamento.Data As Data,
    Lancamento.Valor, Lancamento.Tipo
FROM Clientes As Cliente
    LEFT OUTER JOIN Contas As Conta ON Cliente.ClienteID = Conta.ClienteID
    LEFT OUTER JOIN Lancamentos As Lancamento
        ON Conta.ClienteID = Lancamento.ClienteID AND Conta.ContaID = Lancamento.ContaID
FOR XML AUTO, ROOT(‘Lancamentos’)

Agora façamos uma tentativa de exporar esse conteúdo para um arquivo através do BCP. A instrução abaixo em um PROMPT de comando é capaz de fazê-lo.

BCP “ExportXML.dbo.vLancamentoXML” out “C:\Lancamentos.xml” -T -c

Eis uma parte do resultado do arquivo em XML

<Lancamentos><Cliente Nome=”Ronaldo”><Conta Agencia=”3590-4″ Numero=”97532-8″><Lancamento Data=”2011-12-20″ Valor=”6500.0000″ Tipo=”C”/>

O documento XML foi criado e pode ser plenamente utilizado, pois, é um XML bem formado, mas a identação foi completamente perdida na geração do documento. Se esse XML for processado por uma aplicação, não faz diferença ele estar identado ou não, mas se uma pessoa for abrí-lo, a falta de identação pode ser um grande inconviente.

Uma vez que o BCP foi projetado para exportar texto e não XML, não há parâmetros que possam fazer a identação ficar correta. Então precisaremos dar um jeito do texto já ser exportado de forma identada. Criei aqui uma função capaz de fazê-lo.

CREATE FUNCTION dbo.RetornaXMLGRID (@XML XML)
RETURNS @Registros TABLE (Registro VARCHAR(MAX))
As
BEGIN
    DECLARE
@strXML VARCHAR(MAX)
    SET @strXML = CAST(@XML As VARCHAR(MAX))
    SET @strXML = REPLACE(@strXML,‘<‘,‘+|’)
    SET @strXML = REPLACE(@strXML,‘<‘,‘-|’)

    DECLARE @T TABLE (
        Pos INT IDENTITY(1,1), Elemento VARCHAR(1000))

    INSERT INTO @T SELECT
        node.value(N’fn:local-name(.[1])’, N’varchar(1000)’)
    FROM @xml.nodes(N’//*’) T(Node)

    ;WITH Niveis (Elemento, Nivel) As (
        SELECT Elemento, MIN(Pos)
        FROM @T GROUP BY Elemento)

    DELETE FROM @T
    FROM @T As T
    INNER JOIN Niveis As N ON
        T.Elemento = N.Elemento AND T.Pos > N.Nivel

    DECLARE @i INT = 0
    DECLARE @f INT = (SELECT MAX(Pos) FROM @T)
    DECLARE @Elemento VARCHAR(1000) =

    WHILE @i <= @f – 1
    BEGIN
        SET
@Elemento = (
            SELECT Elemento FROM @T WHERE Pos = @i + 1)

        SET @strXML = REPLACE(@strXML,‘+|’ + @Elemento +
‘ ‘,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|’ + @Elemento + ‘ ‘)
        SET @strXML = REPLACE(@strXML,‘+|’ + @Elemento + ‘-|’,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|’ + @Elemento + ‘-|’)
        SET @strXML = REPLACE(@strXML,‘+|’ + @Elemento + ‘/-|‘,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|’ + @Elemento + ‘/-|’)
        SET @strXML = REPLACE(@strXML,‘+|/’ + @Elemento + ‘-|’,CHAR(10) + REPLICATE(‘ ‘,@i) + ‘+|/’ + @Elemento + ‘-|’)
        SET @i += 1
    END
   
    SET @strXML = REPLACE(@strxml,CHAR(10),‘</e><e>’)
    SET @strXML = ‘<E><e>’ + @strXML + ‘</e></E>’
   
    SET @XML = CAST(@strXML As XML)
   
    INSERT INTO @Registros
    SELECT REPLACE(REPLACE(E.e.value(‘.’,‘VARCHAR(MAX)’),‘+|’,‘<‘),‘-|’,‘>’)
    FROM @XML.nodes(‘/E/e’) E(e)
   
    DELETE FROM @Registros WHERE Registro =
   
    RETURN
END

Agora tentemos o BCP novamente com algumas adaptações:

BCP “SELECT * FROM dbo.RetornaXMLGRID((SELECT ResultadoXML FROM vLancamentoXML))” queryout “C:\Lancamentos.xml” -T -c -d ExportXML

O comando SELECT ResultaadoXML FROM vLancamentoXML é apenas para retornar um texto (ou um tipo XML) no formato de um documento XML. É possível substituir essa parte do comando por qualquer consulta XML. O documento agora veio formatado após abrir o arquivo XML:

<Lancamentos>
<Cliente Nome=”Ronaldo”>
  <Conta Agencia=”3590-4″ Numero=”97532-8″>
   <Lancamento Data=”2011-12-20″ Valor=”6500.0000″ Tipo=”C”/>
   <Lancamento Data=”2011-12-21″ Valor=”1200.0000″ Tipo=”D”/>
   <Lancamento Data=”2011-12-21″ Valor=”2500.0000″ Tipo=”D”/>
  </Conta>
  <Conta Agencia=”3590-4″ Numero=”856413-0″>
   <Lancamento Data=”2011-12-21″ Valor=”3500.0000″ Tipo=”C”/>
   <Lancamento Data=”2011-12-21″ Valor=”2600.0000″ Tipo=”C”/>
  </Conta>
  <Conta Agencia=”3590-4″ Numero=”948766-0″>
   <Lancamento/>
  </Conta>
</Cliente>
<Cliente Nome=”Adalton”>
  <Conta Agencia=”1004-9″ Numero=”974322-8″>

Um teste para garantir que o XML só precisa ser váido por ser feito com outra consulta:

SELECT * FROM dbo.RetornaXMLGRID((
SELECT db.name As banco, t.name As tabela, c.name As coluna FROM sys.databases As db INNER JOIN sys.tables As t ON db.database_id = db_id() INNER JOIN sys.columns As c ON t.object_id = c.object_id for xml auto, elements))

Parte do resultado é exibido abaixo:

<db>
<banco>ExportXML
</banco>
  <t>
   <tabela>Clientes
   </tabela>
    <c>
     <coluna>ClienteID
     </coluna>
    </c>
    <c>
     <coluna>ClienteNome
     </coluna>
    </c>
  </t>
  <t>
   <tabela>Contas
   </tabela>
    <c>
     <coluna>ClienteID
     </coluna>

Acho que já são visíveis algumas limitações desse método. Primeiro porque com tantas conversões, essa alternativa não será escalável quando houver documentos XML muito grandes. Segundo, porque se houver elementos com o mesmo nome pertencentes a níveis diferentes, essa função também irá falhar. Isso torna claro que o T-SQL é poderoso, mas não foi feito para resolver todos os problemas. Tentemos uma abordagem com o CLR para esse mesmo fim.

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

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void UspGerarArquivoXML(SqlString cmdSQL, SqlString local)
    {
        SqlConnection sc = new SqlConnection(“Context Connection=true”);
        SqlCommand sCmd = new SqlCommand(cmdSQL.ToString(), sc);

        // Abre a conexão
        sc.Open();

        // Declara um objeto XmlReader
        XmlReader xr = sCmd.ExecuteXmlReader();

        // Fecha a conexão
        sc.Close();

        // Declara um documento XML
        XmlDocument xd = new XmlDocument();

        // Inicia o documento
        xd.Load(xr);

        // Salva o documento
        xd.Save(local.ToString());
    }
};

Agora que o código em C# está pronto, basta colocá-lo em uma pasta qualquer (C:\SQLCLR\UspGeraArquivoXML.cs) e compilá-lo com o comando de PROMPT

“C:\Windows\Microsoft.NET\Framework\V2.0.50727\csc.exe” /out:C:\SQLCLR\UspGeraArquivoXML.dll /target:library C:\SQLCLR\UspGeraArquivoXML.cs

Os próximos passos são habilitar o CLR e importar o ASSEMBLY:

EXEC sp_configure ‘clr enabled’,1
GO

RECONFIGURE WITH OVERRIDE
GO

USE ExportXML
GO

ALTER DATABASE ExportXML SET TRUSTWORTHY ON

CREATE ASSEMBLY ExportarArquivosXML
FROM ‘C:\SQLCLR\UspGeraArquivoXML.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

Agora que o ASSEMBLY foi gerado, basta criar a procedure e chamá-la:

— Cria a stored procedure
CREATE PROCEDURE UspGerarArquivoXML
    @cmdSQL NVARCHAR(4000),
    @local NVARCHAR(4000)
As
EXTERNAL
NAME [ExportarArquivosXML].[StoredProcedures].[UspGerarArquivoXML]
GO

Por fim, podemos executar a SP com a consulta e o local do arquivo (é importante que a consulta tenha o nó ROOT e que o local tenha as devidas permissões).

— Exporta o Resultado para XML
EXEC dbo.UspGerarArquivoXML
    @cmdSQL =
    SELECT
        Cliente.ClienteNome As Nome,Conta.ContaAgencia As Agencia,
        Conta.ContaNumero As Numero,Lancamento.Data As Data,
        Lancamento.Valor, Lancamento.Tipo
    FROM Clientes As Cliente
        LEFT OUTER JOIN Contas As Conta ON Cliente.ClienteID = Conta.ClienteID
        LEFT OUTER JOIN Lancamentos As Lancamento
        ON Conta.ClienteID = Lancamento.ClienteID AND Conta.ContaID = Lancamento.ContaID
        FOR XML AUTO, ROOT(”Lancamentos”)’
,
    @local = ‘C:\SQLCLR\LancamentosCLR.xml’

O documento já vem identado, mas visivelmente o esforço é muito menor do que escrever código T-SQL para isso.

[ ]s,

Gustavo

Mapeando dependências de objetos para colunas

Boa Noite Pessoal,

Hoje vi no fórum de SQL Server da Microsoft uma necessidade relativamente comum que é a identificação de que objetos referenciam determinadas colunas de determinadas tabelas. É bem comum perguntarmos sobre essa dependências especialmente quando precisamos eliminar ou alterar uma ou outra coluna e sabemos que essas colunas são referenciadas por diversas views, procedures, functions, etc. O melhor para evitar tais situações é usar a opção SCHEMA BINDING sempre que possível, pois, ela evitará que uma ou outra coluna seja excluída e prejudique os objetos que delas dependam. É uma opção mais segura, mas ainda assim, para uma mudança é necessário mapear e isso o SCHEMA BINDING não faz.

Há muito tempo atrás, publiquei os artigos “Mapeando dependências entre tabelas” e “Mapeando dependências entre objetos” que trata sobre dependências com compatibilidade para o SQL Server 2005 (no 2008, os scripts funcionam, mas há outros objetos mais diretos). Ainda assim, não me atentei para uma necessidade de mapeamento para colunas. Vejamos então como podemos fazer isso:

CREATE TABLE Categorias (
    CodigoCategoria INT NOT NULL,
    NomeCategoria VARCHAR(15) NOT NULL,
    Descricao VARCHAR(MAX) NULL
    CONSTRAINT PK_Categorias PRIMARY KEY (CodigoCategoria))

CREATE TABLE Produtos (
    CodigoProduto INT NOT NULL,
    NomeProduto VARCHAR(40) NOT NULL,
    CodigoFornecedor INT NULL,
    CodigoCategoria INT NULL,
    QuantidadePorUnidade VARCHAR(25) NULL,
    PrecoUnitario MONEY NULL,
    UnidadesEstoque SMALLINT NULL,
    UnidadesPedidas SMALLINT NULL,
    NivelDeReposicao SMALLINT NULL,
    Descontinuado BIT NOT NULL,
    CONSTRAINT PK_Produtos PRIMARY KEY (CodigoProduto))

CREATE TABLE DetalhesPedido (
    NumeroPedido INT NOT NULL,
    CodigoProduto INT NOT NULL,
    PrecoUnitario MONEY NOT NULL,
    Quantidade SMALLINT NOT NULL,
    Desconto REAL NOT NULL,
    CONSTRAINT PK_DetalhesPedido PRIMARY KEY (NumeroPedido,CodigoProduto))

CREATE TABLE Pedidos (
    NumeroPedido INT NOT NULL,
    CodigoCliente INT NULL,
    CodigoFuncionario INT NULL,
    DataPedido DATE NULL,
    DataEntrega DATE NULL,
    DataEnvio DATE NULL,
    Frete MONEY NULL,
    CONSTRAINT PK_Pedidos PRIMARY KEY (NumeroPedido))

CREATE TABLE Clientes (
    CodigoCliente INT NOT NULL,
    NomeEmpresa VARCHAR(40) NOT NULL,
    NomeContato VARCHAR(30) NULL,
    CargoContato VARCHAR(30) NULL,
    Endereco VARCHAR(60) NULL,
    Cidade VARCHAR(15) NULL,
    Regiao VARCHAR(15) NULL,
    CEP CHAR(8) NULL,
    Pais VARCHAR(15) NULL,
    Telefone VARCHAR(24) NULL,
    Fax VARCHAR(24) NULL,
CONSTRAINT PK_Clientes PRIMARY KEY (CodigoCliente))

Agora que já criamos algumas tabelas, o próximo passo é criar algumas views, procedures, functions e triggers para criar o mapeamento dos objetos e das colunas desses objetos.

CREATE VIEW vProdutos As
SELECT NomeCategoria, NomeProduto
FROM Categorias As C
INNER JOIN Produtos As P ON C.CodigoCategoria = P.CodigoCategoria
GO

CREATE FUNCTION dbo.CalculaTotalPedido (@NumeroPedido INT)
RETURNS MONEY
As
BEGIN
DECLARE
@Ret MONEY
SET @Ret = (SELECT Frete FROM Pedidos WHERE NumeroPedido = @NumeroPedido)
SET @Ret = @Ret + (
    SELECT SUM(Quantidade * PrecoUnitario * (1 – Desconto))
    FROM DetalhesPedido WHERE NumeroPedido = @NumeroPedido)

RETURN (@Ret)
END
GO

CREATE PROCEDURE UspRetornaClientesPedidos
As
SELECT NomeEmpresa, NomeContato, Cidade, Pais, CEP
FROM Clientes As C
LEFT OUTER JOIN Pedidos As P ON C.CodigoCliente = P.CodigoCliente
GO

CREATE TRIGGER trgEstoque ON DetalhesPedido
FOR INSERT, UPDATE, DELETE
As
BEGIN

    — Efetua os incrementos
    UPDATE Produtos SET UnidadesEstoque += D.Quantidade
    FROM Produtos As P
    INNER JOIN DELETED As D ON P.CodigoProduto = D.CodigoProduto

    — Efetua os decrementos
    UPDATE Produtos SET UnidadesEstoque -= I.Quantidade
    FROM Produtos As P
    INNER JOIN INSERTED As I ON P.CodigoProduto = I.CodigoProduto

END

Visualmente podemos ver que a view, a function, a procedure e a trigger fazem referência para algumas colunas de algumas triggers. Utilizando a view de catálogo sys.sql_dependencies podemos mapear as dependências entre os objetos.

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(object_id) As Objeto
FROM sys.sql_dependencies
ORDER BY ObjetoReferenciado

O resultado de fato mostra as dependências:

Objeto Referenciado Objeto
Categorias vProdutos
Categorias vProdutos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
DetalhesPedido CalculaTotalPedido
DetalhesPedido CalculaTotalPedido
DetalhesPedido CalculaTotalPedido
DetalhesPedido CalculaTotalPedido
Pedidos CalculaTotalPedido
Pedidos UspRetornaClientesPedidos
Pedidos CalculaTotalPedido
Produtos trgEstoque
Produtos vProdutos
Produtos vProdutos
Produtos trgEstoque

Podemos ver todos os mapeamentos, mas o fato de haver duplicidades é no mínimo intrigante. Usar o DISTINCT resolveria isso fácil, mas se há repetição há alguma razão para isso. Vamos pesquisar algumas outras colunas que provocam essa repetição:

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(object_id) As Objeto,
    column_id, referenced_minor_id

FROM sys.sql_dependencies
ORDER BY ObjetoReferenciado

Eis o resultado:

Objeto Referenciado Objeto Column_ID Referenced_minor_id
Categorias vProdutos 0 1
Categorias vProdutos 0 2
Clientes UspRetornaClientesPedidos 0 1
Clientes UspRetornaClientesPedidos 0 2
Clientes UspRetornaClientesPedidos 0 3
Clientes UspRetornaClientesPedidos 0 6
Clientes UspRetornaClientesPedidos 0 8
Clientes UspRetornaClientesPedidos 0 9
DetalhesPedido CalculaTotalPedido 0 1
DetalhesPedido CalculaTotalPedido 0 3
DetalhesPedido CalculaTotalPedido 0 4
DetalhesPedido CalculaTotalPedido 0 5
Pedidos CalculaTotalPedido 0 1
Pedidos UspRetornaClientesPedidos 0 2
Pedidos CalculaTotalPedido 0 8
Produtos trgEstoque 0 1
Produtos vProdutos 0 2
Produtos vProdutos 0 4
Produtos trgEstoque 0 7

O column_id não ajudou muito, mas a coluna Referenced_minor_id já é uma boa pista, pois, é visível que ela provoca a duplicidade. Se olharmos o Books OnLine, essa coluna é mais específica (Referenced_minor_id – 1, referenced_minor_id is a column ID; or if not a column, it is 0.). Só precisamos então fazer um JOIN com a sys.columns e um filtro para garantir que estamos sempre falando de colunas.

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(D.object_id) As Objeto,
    C.name As Coluna
FROM sys.sql_dependencies As D
INNER JOIN sys.columns As C ON
    D.referenced_major_id = C.object_id AND D.referenced_minor_id = C.column_id
WHERE class_desc = ‘OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND’
ORDER BY ObjetoReferenciado

Eis o resultado:

Objeto Referenciado Objeto Coluna
Categorias vProdutos CodigoCategoria
Categorias vProdutos NomeCategoria
Clientes UspRetornaClientesPedidos CodigoCliente
Clientes UspRetornaClientesPedidos NomeEmpresa
Clientes UspRetornaClientesPedidos NomeContato
Clientes UspRetornaClientesPedidos Cidade
Clientes UspRetornaClientesPedidos CEP
Clientes UspRetornaClientesPedidos Pais
DetalhesPedido CalculaTotalPedido NumeroPedido
DetalhesPedido CalculaTotalPedido PrecoUnitario
DetalhesPedido CalculaTotalPedido Quantidade
DetalhesPedido CalculaTotalPedido Desconto
Pedidos CalculaTotalPedido NumeroPedido
Pedidos UspRetornaClientesPedidos CodigoCliente
Pedidos CalculaTotalPedido Frete
Produtos trgEstoque CodigoProduto
Produtos vProdutos NomeProduto
Produtos vProdutos CodigoCategoria
Produtos trgEstoque UnidadesEstoque

Podemos ver que todas as colunas foram retornadas conforme o esperado. Não somente as colunas usadas em SELECTs e UPDATEs, mas também as colunas utilizadas em JOINs (e como isso faz diferença). Será que realmente funciona para outros casos ? Vejamos um objeto com a opção SCHEMA_BINDING (requerida em views indexadas inclusive).

CREATE VIEW vProdutos2
WITH SCHEMABINDING
As
SELECT
NomeCategoria, NomeProduto
FROM dbo.Categorias As C
INNER JOIN dbo.Produtos As P ON C.CodigoCategoria = P.CodigoCategoria
GO

Se tentarmos a consulta anterior, infelizmente a view vProdutos2 não será retornada. A razão é que o vínculo é com schema binding e aí precisamos alterar o predicado.

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(D.object_id) As Objeto,
    C.name As Coluna
FROM sys.sql_dependencies As D
INNER JOIN sys.columns As C ON

    D.referenced_major_id = C.object_id AND D.referenced_minor_id = C.column_id
WHERE class_desc IN (
    ‘OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND’,
    ‘OBJECT_OR_COLUMN_REFERENCE_SCHEMA_BOUND’)
ORDER BY ObjetoReferenciado

Eis o resultado:

Objeto Referenciado Objeto Coluna
Categorias vProdutos CodigoCategoria
Categorias vProdutos NomeCategoria
Categorias vProdutos2 CodigoCategoria
Categorias vProdutos2 NomeCategoria
Clientes UspRetornaClientesPedidos CodigoCliente
Clientes UspRetornaClientesPedidos NomeEmpresa
Clientes UspRetornaClientesPedidos NomeContato
Clientes UspRetornaClientesPedidos Cidade
Clientes UspRetornaClientesPedidos CEP
Clientes UspRetornaClientesPedidos Pais
DetalhesPedido CalculaTotalPedido NumeroPedido
DetalhesPedido CalculaTotalPedido PrecoUnitario
DetalhesPedido CalculaTotalPedido Quantidade
DetalhesPedido CalculaTotalPedido Desconto
Pedidos CalculaTotalPedido NumeroPedido
Pedidos UspRetornaClientesPedidos CodigoCliente
Pedidos CalculaTotalPedido Frete
Produtos trgEstoque CodigoProduto
Produtos vProdutos NomeProduto
Produtos vProdutos CodigoCategoria
Produtos trgEstoque UnidadesEstoque
Produtos vProdutos2 CodigoProduto
Produtos vProdutos2 NomeProduto

Agora sim funcionou. Para mapeamentos simples, essa view é uma mão na roda. Fica aí a dica.

[ ]s,

Gustavo

Exclusões em cascata e auto-referência no SQL Server

Oi Pessoal,

Ainda estou me acostumando com a mudança do blog (algumas coisas melhoraram muito, mas estou sentindo muita falta de outras). Iniciando o primeiro post do ano de 2011 (e o primeiro que não foi importado do Live), vou apontar uma rápida solução para uma thread que vi no fórum do MSDN. A thread referia-se a impossibilidade de utilizar a exclusão em cascata para auto-relacionamentos. Admito que nunca precisei fazer algo desse tipo, pois, normalmente a exclusão em cascata não é utilizada e quase sempre tabelas que se auto-relacionam são tabelas de referência e não são apagadas. Ainda assim, vejamos o que pode ser feito nessa situação.

— Cria uma tabela de Empregados
CREATE TABLE Empregados (
    EmpregadoID INT NOT NULL,
    EmpregadoNome VARCHAR(50) NOT NULL,
    SuperiorID INT NULL)

— Cria as Constraints
ALTER TABLE Empregados ADD CONSTRAINT PK_Empregado PRIMARY KEY (EmpregadoID)
ALTER TABLE Empregados ADD CONSTRAINT FK_Empregado_Empregado
    FOREIGN KEY (SuperiorID) REFERENCES Empregados (EmpregadoID)

— Insere alguns registros
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (1,‘Sálvio’,NULL)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (2,‘Hélio’,1)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (3,‘Dênis’,2)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (4,‘Júnior’,2)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (5,‘Marcos’,3)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (6,‘Bruno’,3)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (7,‘Paulo’,5)

Podemos ver as seguintes relações hierárquicas:

  • Sálvio -> Hélio
    Sálvio -> Hélio -> Dênis
    Sálvio -> Hélio -> Dênis -> Marcos
    Sálvio -> Hélio -> Dênis -> Marcos -> Paulo
    Sálvio -> Hélio -> Júnior
  • Hélio -> Dênis
    Hélio -> Dênis -> Marcos
    Hélio -> Dênis -> Marcos -> Paulo
    Hélio -> Júnior
  • Dênis -> Marcos
    Dênis -> Marcos -> Paulo
  • Marcos -> Paulo

Agora que os registros estão populados, vamos tentar efetuar uma exclusão:

— Tenta excluir o registro do empregado Hélio
DELETE FROM Empregados WHERE EmpregadoID = 2

Como era de se esperar, a existência de subordinados ao empregado Hélio representadas pelo auto-relacionamento gerou um erro na exclusão:

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint “FK_Empregado_Empregado”. The conflict occurred in database “tempdb”, table “dbo.Empregados”, column ‘SuperiorID’.
The statement has been terminated.

Admitindo que a exclusão de um empregado deva excluir também os seus subordinados, poderíamos ativar a exclusão em cascata.

— Remove a constraint FK anterior
ALTER TABLE Empregados DROP CONSTRAINT FK_Empregado_Empregado

— Recria a constraint com a exclusão em cascata
ALTER TABLE Empregados ADD CONSTRAINT FK_Empregado_Empregado
    FOREIGN KEY (SuperiorID) REFERENCES Empregados (EmpregadoID)
    ON DELETE CASCADE

A exclusão da constraint ocorre com sucesso, mas a criação com a exclusão em cascata e a auto-referência gera um erro:

Msg 1785, Level 16, State 0, Line 2
Introducing FOREIGN KEY constraint ‘FK_Empregado_Empregado’ on table ‘Empregados’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

No SQL Server, o recurso de exclusão em cascata possui algumas restrições e uma delas é não poder ser utilizado para constraints que envolvam o auto-relacionamento. Não cheguei a testar esse comportamento em outros SGBDs como o ORACLE, DB2 e afins, mas entendo que essa restrição do SQL Server tenha certo fundamento. A exclusão em cascata com o auto-relacionamento pode levar a situações complexas de gerenciar bem como efeitos catastróficos. Excluir o registro do Sálvio por exemplo, incorreria em praticamente limpar a tabela de empregados se a exclusão em cascata funcionasse com o auto-relacionamento. Outra situação envolve referências circulares. Digamos que não estejamos tratando de uma relação de subordinação, mas de indicações ou até um amigo oculto. Em tais situações é bem fácil de acontecer uma referência circular do tipo K -> X -> Y -> Z -> K. A exclusão em cascata em uma situação dessas iria provocar um loop infinito.

Admitindo-se que não haja um ciclo, como fazer então para excluir em cascata registro de uma tabela que possui auto-relacionamento se a FK não dá essa possibilidade ? A solução que me veio a cabeça para resolver a situação é combinar a chave estrangeira com uma trigger de Instead Of. Só que há mais um detalhe, se a FK irá bloquear as exclusões que a violem, é preciso excluir os registros na ordem correta. Para fazer isso utilizei-me de uma CTE recursiva.

— Captura o ID do empregado Hélio
DECLARE @EmpregadoID INT
SET @EmpregadoID = (
    SELECT EmpregadoID FROM Empregados
    WHERE EmpregadoNome = ‘Hélio’)

— Lista todos os subordinados ao Hélio com o nível
;WITH Emp (EmpregadoID, EmpregadoNome, SuperiorID, Nivel)
As (
    SELECT EmpregadoID, EmpregadoNome, SuperiorID, 1 As Nivel
    FROM Empregados
    WHERE EmpregadoID = @EmpregadoID
    UNION ALL
    SELECT E.EmpregadoID, E.EmpregadoNome, E.SuperiorID, Nivel + 1
    FROM Empregados As E
    INNER JOIN Emp ON E.SuperiorID = Emp.EmpregadoID)

— Recupera os subordinados ao Hélio
SELECT EmpregadoID, EmpregadoNome, SuperiorID, Nivel FROM Emp

EmpregadoID EmpregadoNome SuperiorID Nivel
2 Hélio 1 1
3 Dênis 2 2
4 Júnior 2 2
5 Marcos 3 3
6 Bruno 3 3
7 Paulo 5 4

Agora ficou bem mais fácil. A CTE recursiva retorna o registro e todos os subordinados. Para evitar violação de chave estrangeira, basta excluir do maior nível para o menor nível. Adicionei a lógica dentro da trigger.

— Cria uma trigger Instead Of Delete
CREATE TRIGGER trgIDEmpregados ON Empregados
INSTEAD OF DELETE
As

— Cria uma tabela temporária para armazenar os registros a excluir
DECLARE @Empregados TABLE (EmpregadoID INT, Nivel INT)

— Monta a CTE recursiva
;WITH Emp (EmpregadoID, SuperiorID, Nivel)
As (
    SELECT EmpregadoID, EmpregadoNome, SuperiorID, 1 As Nivel
    FROM Empregados
    WHERE EmpregadoID IN (SELECT EmpregadoID FROM Deleted)
    UNION ALL
    SELECT E.EmpregadoID, E.SuperiorID, Nivel + 1
    FROM Empregados As E
    INNER JOIN Emp ON E.SuperiorID = Emp.EmpregadoID)

— Insere os IDs dos registros a excluir
INSERT INTO @Empregados SELECT EmpregadoID, Nivel FROM Emp
ORDER BY Nivel DESC

— Exclui os registros
DECLARE @MaiorNivel INT
SET @MaiorNivel = (SELECT MAX(Nivel) FROM @Empregados)

WHILE EXISTS (SELECT * FROM @Empregados)
BEGIN
    — Exclui os empregados
    DELETE FROM Empregados WHERE EmpregadoID IN
        (SELECT EmpregadoID FROM @Empregados WHERE Nivel = @MaiorNivel)

    — Exclui os empregados da tabela temporária
    DELETE FROM @Empregados WHERE Nivel = @MaiorNivel

    — Decrementa um nível
    SET @MaiorNivel = @MaiorNivel – 1

END

Agora vamos ver alguns testes:

— Exclui o Hélio
— Só deve sobrar o Sálvio
BEGIN TRAN
    DELETE FROM Empregados WHERE EmpregadoID = 2
    SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

— Exclui o Dênis
— Só deve sobrar o Sálvio, Hélio e Júnior
BEGIN TRAN
    DELETE FROM Empregados WHERE EmpregadoID = 3
    SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

— Exclui o Marcos e o Júnior
— Só deve sobrar o Sálvio, Dênis e Bruno
BEGIN TRAN
    DELETE FROM Empregados WHERE EmpregadoID IN (3,5)
    SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

— Exclui o Sálvio
— Não sobra ninguém
BEGIN TRAN
    DELETE FROM Empregados WHERE EmpregadoID = 1
    SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

Como podemos ver, a trigger inclusive já é orientada a conjuntos permitindo múltiplas exclusões. Confesso que tirando a thread eu nunca tinha tido tal necessidade. Quem precisar é só adaptar.

[ ]s,

Gustavo

Utilizando Common Table Expressions (CTEs) para estruturação de MENUs – Parte II

Boa Noite Pessoal,

Na parte I do artigo, demonstrei como utilizar as recursive CTEs para exibir a montagem correta de um plano de contas. A maior dificuldade é realmente garantir a ordem correta de exibição dos itens, uma vez que itens posteriores podem ser inseridos no meio das contas já existentes. Minha solução foi baseada na materialização dos caminhos até a conta, motando assim uma coluna para ordenação, mas hoje pensei que talvez existam outras necessidades. Vou recolocar o plano para mostrar algumas outras consultas interessantes.

— Cria uma tabela de Plano de Contas
CREATE TABLE PlanoContas (
    ID INT NOT NULL,
    IDSup INT NULL,
    Descricao VARCHAR(50))

— Adiciona as constraints
ALTER TABLE PlanoContas ADD CONSTRAINT PK_Plano PRIMARY KEY (ID)

ALTER TABLE PlanoContas ADD CONSTRAINT FK_Plano FOREIGN KEY (IDSup)
REFERENCES PlanoContas (ID)

— Insere os registros no Plano de Contas
INSERT INTO PlanoContas VALUES (01,NULL,‘Ativo’)
INSERT INTO PlanoContas VALUES (02,01,‘Ativo Circulante’)
INSERT INTO PlanoContas VALUES (03,02,‘Disponibilidades’)
INSERT INTO PlanoContas VALUES (04,03,‘Caixa’)
INSERT INTO PlanoContas VALUES (05,04,‘Caixa Geral’)
INSERT INTO PlanoContas VALUES (06,03,‘Contas Bancárias’)
INSERT INTO PlanoContas VALUES (07,06,‘Conta Corrente’)
INSERT INTO PlanoContas VALUES (08,06,‘Conta Poupança’)
INSERT INTO PlanoContas VALUES (09,02,‘Créditos’)
INSERT INTO PlanoContas VALUES (10,09,‘Duplicatas’)
INSERT INTO PlanoContas VALUES (11,10,‘Duplicatas Descontadas’)
INSERT INTO PlanoContas VALUES (12,10,‘Duplicatas a Receber’)
INSERT INTO PlanoContas VALUES (13,06,‘Conta Investimento’)

;WITH Res (ID, IDSup, Descricao, Caminho) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’
   
FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao, Caminho FROM Res ORDER BY Caminho

ID Descricao Caminho
01 Ativo 0001.
02 Ativo -> Ativo Circulante 0001.0002.
03 Ativo -> Ativo Circulante -> Disponibilidades 0001.0002.0003.
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa 0001.0002.0003.0004.
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral 0001.0002.0003.0004.0005.
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias 0001.0002.0003.0006.
13 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente 0001.0002.0003.0006.0007.
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança 0001.0002.0003.0006.0008.
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento 0001.0002.0003.0006.0013.
09 Ativo -> Ativo Circulante -> Créditos 0001.0002.0009.
10 Ativo -> Ativo Circulante -> Créditos -> Duplicatas 0001.0002.0009.0010.
11 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas 0001.0002.0009.0010.0011.
12 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber 0001.0002.0009.0010.0012.

A montagem da coluna "Caminho" foi necessária para garantir a ordem correta, fazendo assim com que a conta investimento (ID 13) ficasse logo abaixo das contas correnta e poupança de acordo com a ordem de cadastro. Será no entanto, que a ordem de cadastro é a ordem desejada ? E se fosse necessário exibir os itens pode ordem alfabética ? A consulta abaixo mostra como fazê-lo:

;WITH Res (ID, IDSup, Descricao, Caminho) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’
   
FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT
    ID, Descricao, Caminho,
    LEFT(Caminho,LEN(Caminho)-5) As CaminhoSuperior
FROM Res
ORDER BY
    CaminhoSuperior, Descricao

ID Descricao Caminho CaminhoSuperior
01 Ativo 0001.  
02 Ativo -> Ativo Circulante 0001.0002. 0001.
03 Ativo -> Ativo Circulante -> Disponibilidades 0001.0002.0003. 0001.0002.
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa 0001.0002.0003.0004. 0001.0002.0003.
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral 0001.0002.0003.0004.0005. 0001.0002.0003.0004.
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias 0001.0002.0003.0006. 0001.0002.0003.
13 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente 0001.0002.0003.0006.0007. 0001.0002.0003.0006.
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança 0001.0002.0003.0006.0008. 0001.0002.0003.0006.
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento 0001.0002.0003.0006.0013. 0001.0002.0003.0006.
09 Ativo -> Ativo Circulante -> Créditos 0001.0002.0009. 0001.0002.
10 Ativo -> Ativo Circulante -> Créditos -> Duplicatas 0001.0002.0009.0010. 0001.0002.0009.
11 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber 0001.0002.0009.0010.0012. 0001.0002.0009.0010.
12 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas 0001.0002.0009.0010.0011. 0001.0002.0009.0010.

A montagem da coluna "CaminhoSuperior" reflete exatamente o caminho até o "pai" de uma determinada conta. Uma vez que determinadas contas tenham o mesmo pai (e por consequência o mesmo valor para a coluna "CaminhoSuperior"), basta ordená-las pela descrição da conta exatamente da mesma forma que o ORDER BY realiza. É possível evitar o uso do LEN, através de uma pequena adaptação na CTE aproveitando a montagem da coluna "Caminho" antes da recursividade:

;WITH Res (ID, IDSup, Descricao, Caminho, CaminhoSuperior) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’,
        CAST(” As VARCHAR(MAX))
    FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’, Caminho
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT
    ID, Descricao, Caminho, CaminhoSuperior
FROM Res
ORDER BY
    CaminhoSuperior, Descricao

ID Descricao Caminho CaminhoSuperior
01 Ativo 0001.  
02 Ativo -> Ativo Circulante 0001.0002. 0001.
03 Ativo -> Ativo Circulante -> Disponibilidades 0001.0002.0003. 0001.0002.
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa 0001.0002.0003.0004. 0001.0002.0003.
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral 0001.0002.0003.0004.0005. 0001.0002.0003.0004.
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias 0001.0002.0003.0006. 0001.0002.0003.
13 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente 0001.0002.0003.0006.0007. 0001.0002.0003.0006.
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança 0001.0002.0003.0006.0008. 0001.0002.0003.0006.
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento 0001.0002.0003.0006.0013. 0001.0002.0003.0006.
09 Ativo -> Ativo Circulante -> Créditos 0001.0002.0009. 0001.0002.
10 Ativo -> Ativo Circulante -> Créditos -> Duplicatas 0001.0002.0009.0010. 0001.0002.0009.
11 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber 0001.0002.0009.0010.0012. 0001.0002.0009.0010.
12 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas 0001.0002.0009.0010.0011. 0001.0002.0009.0010.

Não que seja uma necessidade comum, mas caso alguém deseje converter um modelo adjacente para um modelo de caminhos materializados, segue a CTE que pode fazê-lo.

;WITH Res (ID, IDSup, Descricao, Caminho, CaminhoSuperior, CaminhoMaterializado) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’,
        CAST( As VARCHAR(MAX)),
        CAST(1 As VARCHAR(MAX))
    FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’, Caminho,
        CaminhoMaterializado + ‘.’ +
            CAST(ROW_NUMBER() OVER (
                PARTITION BY Caminho
                ORDER BY F.Descricao) As VARCHAR(MAX))
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT
    ID, Descricao, Caminho, CaminhoMaterializado As CM
FROM Res
ORDER BY CaminhoMaterializado

ID Descricao Caminho CM
01 Ativo 0001. 1
02 Ativo -> Ativo Circulante 0001.0002. 1.1
09 Ativo -> Ativo Circulante -> Créditos 0001.0002.0009. 1.1.1
10 Ativo -> Ativo Circulante -> Créditos -> Duplicatas 0001.0002.0009.0010. 1.1.1.1
12 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber 0001.0002.0009.0010.0012. 1.1.1.1.1
11 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas 0001.0002.0009.0010.0011. 1.1.1.1.2
03 Ativo -> Ativo Circulante -> Disponibilidades 0001.0002.0003. 1.1.2
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa 0001.0002.0003.0004. 1.1.2.1
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral 0001.0002.0003.0004.0005. 1.1.2.1.1
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias 0001.0002.0003.0006. 1.1.2.2
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente 0001.0002.0003.0006.0007. 1.1.2.2.1
13 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento 0001.0002.0003.0006.0013. 1.1.2.2.2
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança 0001.0002.0003.0006.0008. 1.1.2.2.3

Bem pessoal, acho que por hoje é só. Amanhã terei um longo dia pela frente.

[ ]s,

Gustavo

Utilizando Common Table Expressions (CTEs) para estruturação de MENUs – Parte I

Boa Noite Pessoal,

Está realmente difícil estar presente e o backlog de atividades, artigos e tarefas está enorme. Bem, hoje vou postar uma dica rápida. Estou moderando diariamente os fóruns de SQL Server do MSDN e do Technet e hoje vi uma dúvida interessante envolvendo as CTEs. A dúvida é na montagem, ordenação e exibição dos registros de um plano de contas. Vejamos um exemplo prático:

A figura acima mostra a estrutura de um plano de contas de uma empresa fictícia. Ao lado da legenda do plano está relacionado também o seu ID. O script abaixo cria uma tabela e grava alguns registros para reproduzir o pano:

— Cria uma tabela de Plano de Contas
CREATE TABLE PlanoContas (
    ID INT NOT NULL,
    IDSup INT NULL,
    Descricao VARCHAR(50))

— Adiciona as constraints
ALTER TABLE PlanoContas ADD CONSTRAINT PK_Plano PRIMARY KEY (ID)

ALTER TABLE PlanoContas ADD CONSTRAINT FK_Plano FOREIGN KEY (IDSup)
REFERENCES PlanoContas (ID)

— Insere os registros no Plano de Contas
INSERT INTO PlanoContas VALUES (01,NULL,‘Ativo’)
INSERT INTO PlanoContas VALUES (02,01,‘Ativo Circulante’)
INSERT INTO PlanoContas VALUES (03,02,‘Disponibilidades’)
INSERT INTO PlanoContas VALUES (04,03,‘Caixa’)
INSERT INTO PlanoContas VALUES (05,04,‘Caixa Geral’)
INSERT INTO PlanoContas VALUES (06,03,‘Contas Bancárias’)
INSERT INTO PlanoContas VALUES (07,06,‘Conta Corrente’)
INSERT INTO PlanoContas VALUES (08,06,‘Conta Poupança’)

Há inúmeras possibilidades com uma CTE recursiva, mas uma necessidade comum, é montar um resultset para exibir exatamente o plano de contas com as respectivas identações ou ainda exibir os caminhos para chegar a uma determinada conta. Vejamos então algumas consultas utilizando as recursive CTEs.

— Mostra os caminhos até as contas
;WITH Res (ID, IDSup, Descricao) As (
    SELECT ID, IDSup, CAST(Descricao As VARCHAR(MAX)) FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT F.ID, F.IDSup, P.Descricao + ‘ -> ‘ + F.Descricao
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao FROM Res
ORDER BY ID

ID Descricao
01 Ativo
02 Ativo -> Ativo Circulante
03 Ativo -> Ativo Circulante -> Disponibilidades
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança

— Mostra as contas aninhadas
;WITH Res (ID, IDSup, Descricao, Nivel) As (
    SELECT ID, IDSup, Descricao, 1 As Nivel FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT F.ID, F.IDSup, F.Descricao, Nivel + 1
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, ‘|’ + REPLICATE(‘–‘,Nivel) + Descricao As Descricao FROM Res
ORDER BY ID

ID Descricao
01 |–Ativo
02 |—-Ativo Circulante
03 |——Disponibilidades
04 |——–Caixa
05 |———-Caixa Geral
06 |——–Contas Bancárias
07 |———-Conta Corrente
08 |———-Conta Poupança

Se o plano de contas ficasse estático ou se as contas sempre surgissem abaixo das contas existentes, seria fácil utilizar as consultas acima, mas como será que as consultas se comportam quando há inserções no meio das contas já existentes ? Vejamos a seguir:

INSERT INTO PlanoContas VALUES (09,02,‘Créditos’)
INSERT INTO PlanoContas VALUES (10,09,‘Duplicatas’)
INSERT INTO PlanoContas VALUES (11,10,‘Duplicatas Descontadas’)
INSERT INTO PlanoContas VALUES (12,10,‘Duplicatas a Receber’)
INSERT INTO PlanoContas VALUES (13,06,‘Conta Investimento’)

As novas contas mudam o organograma conforme a figura abaixo:

O novo plano de contas adicionou novos nós em sequência (09, 10, 11 e 12), mas uma nova conta (13) foi adicionada no plano e não manteve a ordem já que o 13 supostamente deve aparecer antes do 09. Vejamos como as consultas se comportam nesse caso

— Mostra os caminhos até as contas
;WITH Res (ID, IDSup, Descricao) As (
    SELECT ID, IDSup, CAST(Descricao As VARCHAR(MAX)) FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT F.ID, F.IDSup, P.Descricao + ‘ -> ‘ + F.Descricao
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao FROM Res
ORDER BY ID

ID Descricao
01 Ativo
02 Ativo -> Ativo Circulante
03 Ativo -> Ativo Circulante -> Disponibilidades
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança
09 Ativo -> Ativo Circulante -> Créditos
10 Ativo -> Ativo Circulante -> Créditos -> Duplicatas
11 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas
12 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber
13 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento

— Mostra as contas aninhadas
;WITH Res (ID, IDSup, Descricao, Nivel) As (
    SELECT ID, IDSup, Descricao, 1 As Nivel FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT F.ID, F.IDSup, F.Descricao, Nivel + 1
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, ‘|’ + REPLICATE(‘–‘,Nivel) + Descricao As Descricao FROM Res
ORDER BY ID

ID Descricao
01 |–Ativo
02 |—-Ativo Circulante
03 |——Disponibilidades
04 |——–Caixa
05 |———-Caixa Geral
06 |——–Contas Bancárias
07 |———-Conta Corrente
08 |———-Conta Poupança
09 |——Créditos
10 |——–Duplicatas
11 |———-Duplicatas Descontadas
12 |———-Duplicatas a Receber
13 |———-Conta Investimento

Na primeira consulta, o caminho até a conta investimento (13) está correto, mas não apareceu na sequência esperada, ou seja, logo após a conta poupança (08) igual ao plano de contas. Na segunda consulta, a identação da conta investimento está correta (5⁰ nível), mas visivelmente aparece após as duplicatas e dá a falsa idéia de que essa conta está abaixo da conta "Duplicatas" o que não é verdade. A grande questão é elaborar a ordem em que as contas precisam ser recuperadas e por mais simples que pareça ela não é óbvia. Não é eficaz fazer a ordenação por ID e nem por nível. Demora algum tempo para perceber, mas a ordenação deve combinar ambos os fatores, ou seja, para um dado ID, organiza-se suas contas inferiores por níveis. A dificuldade está em elaborar o "ORDER BY". Uma alternativa é recorrer a materialização dos caminhos conforme o script abaixo:

;WITH Res (ID, IDSup, Descricao, Caminho) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        CAST(ID As VARCHAR(MAX)) + ‘.’ FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao, Caminho FROM Res ORDER BY Caminho

ID Descricao Caminho
01 Ativo 1.
02 Ativo -> Ativo Circulante 1.2.
03 Ativo -> Ativo Circulante -> Disponibilidades 1.2.3.
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa 1.2.3.4.
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral 1.2.3.4.5.
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias 1.2.3.6.
13 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento 1.2.3.6.13.
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente 1.2.3.6.7.
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança 1.2.3.6.8.
09 Ativo -> Ativo Circulante -> Créditos 1.2.9.
10 Ativo -> Ativo Circulante -> Créditos -> Duplicatas 1.2.9.10.
11 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas 1.2.9.10.11.
12 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber 1.2.9.10.12.

A idéia de materializar o caminho deixou as contas um pouco mais organizadas. Entretanto, os mais observadores irão perceber um pequeno erro que pode inclusive passar desapercebido por alguns. A conta investimento (13) foi recuperada antes das contas corrente (7) e poupança (8). Se o ID da conta investimento é 13 porque então essa conta foi retornada antes das contas de ID 7 e 8 ? A resposta é bem simples. Se considerássemos que 7 e 8 são números, o 13 viria posteriormente, mas considerando que a coluna "caminho" é um VARCHAR, a string 13 é recuperada antes das strings 7 e 8 e por isso ocorre essa pequena divergência. Para resolver esse pequeno detalhe é possível utilizar uma máscara que faça com que a ordem numérica e a ordem alfanumérica sejam iguais. Supondo um plano de até mil contas, pode-se alterar a consulta para a solução seguinte:

;WITH Res (ID, IDSup, Descricao, Caminho) As (
    SELECT
        ID, IDSup, CAST(Descricao As VARCHAR(MAX)),
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’ FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, P.Descricao + ‘ -> ‘ +
        F.Descricao, P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT ID, Descricao, Caminho FROM Res ORDER BY Caminho

ID Descricao Caminho
01 Ativo 0001.
02 Ativo -> Ativo Circulante 0001.0002.
03 Ativo -> Ativo Circulante -> Disponibilidades 0001.0002.0003.
04 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa 0001.0002.0003.0004.
05 Ativo -> Ativo Circulante -> Disponibilidades -> Caixa -> Caixa Geral 0001.0002.0003.0004.0005.
06 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias 0001.0002.0003.0006.
13 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Corrente 0001.0002.0003.0006.0007.
07 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Poupança 0001.0002.0003.0006.0008.
08 Ativo -> Ativo Circulante -> Disponibilidades -> Contas Bancárias -> Conta Investimento 0001.0002.0003.0006.0013.
09 Ativo -> Ativo Circulante -> Créditos 0001.0002.0009.
10 Ativo -> Ativo Circulante -> Créditos -> Duplicatas 0001.0002.0009.0010.
11 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas Descontadas 0001.0002.0009.0010.0011.
12 Ativo -> Ativo Circulante -> Créditos -> Duplicatas -> Duplicatas a Receber 0001.0002.0009.0010.0012.

Agora me parece que a estrutura está correta e adequada para apresentação. Utilizando a mesma estratégia da coluna materializada chamada caminho, podemos arrumar a consulta baseada em aninhamento.

;WITH Res (ID, IDSup, Descricao, Nivel, Caminho) As (
    SELECT
        ID, IDSup, Descricao, 1 As Nivel,
        REPLICATE(‘0’,3-ROUND(LOG10(ID),0,1)) +
        CAST(ID As VARCHAR(MAX)) + ‘.’ FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID, F.IDSup, F.Descricao, Nivel + 1,
        P.Caminho + REPLICATE(‘0’,3-ROUND(LOG10(F.ID),0,1)) +
        CAST(F.ID As VARCHAR(MAX)) + ‘.’
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup)

SELECT
    ID, ‘|’ + REPLICATE(‘–‘,Nivel) + Descricao As Descricao,
    Nivel, Caminho
FROM Res
ORDER BY Caminho

ID Descricao Nível Caminho
01 |–Ativo 1 0001.
02 |—-Ativo Circulante 2 0001.0002.
03 |——Disponibilidades 3 0001.0002.0003.
04 |——–Caixa 4 0001.0002.0003.0004.
05 |———-Caixa Geral 5 0001.0002.0003.0004.0005.
06 |——–Contas Bancárias 4 0001.0002.0003.0006.
07 |———-Conta Corrente 5 0001.0002.0003.0006.0007.
08 |———-Conta Poupança 5 0001.0002.0003.0006.0008.
09 |———-Conta Investimento 5 0001.0002.0003.0006.0013.
10 |——Créditos 3 0001.0002.0009.
11 |——–Duplicatas 4 0001.0002.0009.0010.
12 |———-Duplicatas Descontadas 5 0001.0002.0009.0010.0011.
13 |———-Duplicatas a Receber 5 0001.0002.0009.0010.0012.

Agora sim, ambas as consultas estão corretas e acredito exibir adequadamente o resultado esperado. Espero que essa dica seja útil aqueles que tem de lidar com uma representação hierárquica baseada no modelo adjacente e que tenham que apresentar os resultados com base na posição hierárquica.

[ ]s,

Gustavo

Como validar os nomes das colunas durante a criação de uma tabela – Parte I

Boa Noite Pessoal,

Em uma aula dessas nos cursos de SQL Server, durante a exposição do recurso Policy Based Management no SQL Server 2008, eu apresentei uma política que demonstrava como forçar que o nome de uma tabela obedecesse a um determinado padrão de nomenclatura (no caso as tabelas deveriam iniciar-se com tbl). Os alunos gostaram do exemplo principalmente porque boa parte não conhecia esse recurso e a melhor parte é que ele pode ser utilizado no SQL Server 2008 em conjunto com o SQL Server 2000 e 2005. O script a seguir reproduz o exemplo que utilizei (também disponibilizei o arquivo xml para importar a política em: http://cid-f4f5c630410b9865.office.live.com/self.aspx/ProjetosSQLServer/20100628%5E_pChecaNomeTabela.xml).

DECLARE @condition_id INT
EXEC msdb.dbo.sp_syspolicy_add_condition @name = N’cChecaNomeTabela’,
    @description = N”, @facet = N’ITableOptions’,
    @expression = N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>LIKE</OpType>
  <Count>2</Count>
  <Attribute>
    <TypeClass>String</TypeClass>
    <Name>Name</Name>
  </Attribute>
  <Constant>
    <TypeClass>String</TypeClass>
    <ObjType>System.String</ObjType>
    <Value>tbl%</Value>
  </Constant>
</Operator>’
, @is_name_condition = 2, @obj_name = N’tbl%’, @condition_id = @condition_id OUTPUT

DECLARE @object_set_id INT
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name = N’pChecaNomeTabela_ObjectSet’,
    @facet = N’ITableOptions’, @object_set_id = @object_set_id OUTPUT

DECLARE @target_set_id INT
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name = N’pChecaNomeTabela_ObjectSet’,
    @type_skeleton = N’Server/Database/Table’, @type = N’TABLE’, @enabled = True,
    @target_set_id = @target_set_id OUTPUT

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id = @target_set_id,
    @type_skeleton = N’Server/Database/Table’, @level_name = N’Table’,
    @condition_name = N”, @target_set_level_id = 0

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id = @target_set_id,
    @type_skeleton = N’Server/Database’, @level_name = N’Database’,
    @condition_name = N”, @target_set_level_id = 0

DECLARE @policy_id INT
EXEC msdb.dbo.sp_syspolicy_add_policy @name = N’pChecaNomeTabela’,
    @condition_name = N’cChecaNomeTabela’, @execution_mode = 1, @is_enabled = True,
    @policy_id = @policy_id OUTPUT, @object_set = N’pChecaNomeTabela_ObjectSet’

Agora que a política está criada, o script a seguir verifica se ela está de fato funcionando. Como a política impede a criação de tabelas que não se iniciem com o prefixo tbl, a criação de tabelas com um prefixo diferente deve resultar em um erro:

CREATE TABLE tLancamentos (
    LancamentoID INT NOT NULL IDENTITY(1,1),
    LancamentoValor SMALLMONEY,
    LancamentoData DATE)

Policy ‘pChecaNomeTabela’ has been violated by ‘SQLSERVER:\SQL\MQ002\DEV\Databases\tempdb\Tables\dbo.tLancamentos’.
This transaction will be rolled back.
Policy condition: ‘@Name LIKE ‘tbl%”
Policy description: ”
Additional help: ” : ”
Statement: ‘CREATE TABLE tLancamentos (
    LancamentoID INT NOT NULL IDENTITY(1,1),
    LancamentoValor SMALLMONEY,
    LancamentoData DATE) ‘.

Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

A política foi efetiva uma vez que ela impediu a criação de uma tabela tLancamentos, pois, ela não inicia com o prefixo tbl. A criação de uma tabela com esse prefixo funciona perfeitamente:

CREATE TABLE tblLancamentos (
    LancamentoID INT NOT NULL IDENTITY(1,1),
    LancamentoValor SMALLMONEY,
    LancamentoData DATE)

Command(s) completed successfully.

Imediatamente após mostrar esse exemplo, um aluno me perguntou se haveria como aplicar a mesma idéia na criação de colunas e de preferência para obedecer uma padrão de nomenclatura previamente estabelecido. Essa foi uma pergunta que me surpreendeu. Eu reconheço plenamente essa necessidade por partes do administradores de dados (ADs), mas nunca havia pensado em fazer algo do tipo com políticas. Até procurei na faceta Name, e embora ela consiga estabelecer nomes para vários objetos (tabelas, procedures, views, etc) não é possível utilizá-la para nomes de colunas. Isso era previsível, pois, como o gerenciamento de políticas é baseado em triggers DDL e estas não possuem disparo para colunas, o gerenciamento baseado em política também não haveria de fazê-lo. Ainda que isso fosse possível, normalmente padrões de nomenclatura não limitam-se a prefixos ou sufixos, mas a palavras combinadas para formar um nome válido (ex: NomeCompletoCliente). Mesmo com condições do tipo ExecuteSQL, dificilmente seria possível algo do tipo On Change Prevent para colunas obedecendo um padrão de nomenclatura. Lançado o desafio, comecei a pensar em uma forma de resolver esse problema.

Baseando-me no meu artigo anterior "Extraindo e validando nomes de uma string no padrão Camel Case", vou adotar a mesma tabela de prefixo para definição do nome de colunas. O script abaixo cria a tabela de definições:

— Cria uma tabela de Definições
CREATE TABLE Definicoes (
    DefID INT NOT NULL IDENTITY(1,1),
    DefAcronimo VARCHAR(100) NOT NULL,
    DefPalavra VARCHAR(200) NOT NULL)

— Insere alguns registros
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Num’,‘Número’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Cart’,‘Cartão’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Cred’,‘Crédito’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Deb’,‘Débito’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Cli’,‘Cliente’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Val’,‘Valor’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Par’,‘Parcela’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Venc’,‘Vencimento’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Dat’,‘Data’)

Utilizarei também uma das funções criadas no artigo:

— Criação da função
CREATE FUNCTION dbo.FnExtraiPalavras (@Nome VARCHAR(100))
RETURNS TABLE
RETURN
(

WITH Nums As (
    SELECT 1 As Num
    UNION ALL
    SELECT Num + 1 FROM Nums
    WHERE Num + 1 <= LEN(@Nome)),

Analise As (

SELECT
    Num As Posicao
FROM Nums
WHERE ASCII(SUBSTRING(@Nome,Num,1)) BETWEEN 65 AND 90),

Palavras As (

SELECT Posicao As Inicio,
    ISNULL((SELECT MIN(Posicao) FROM Analise As TInt
    WHERE TInt.Posicao > TOut.Posicao),LEN(@Nome) + 1) As Fim
FROM Analise As TOut)

SELECT SUBSTRING(@Nome, Inicio, Fim – Inicio) As Palavra, Inicio, Fim
FROM Palavras)

As triggers DDL introduzidas no SQL Server 2005 são disparadas após a ação que as disparou. Embora tenham a capacidade de reverter a ação através de um ROLLBACK, seus comandos só executam após a ocorrência de um evento. Em uma trigger DDL de CREATE TABLE, a trigger pode até reverter a criação da tabela, mas a tabela será criada antes da execução da trigger. Isso significa que no momento de execução da trigger, a tabela já existe e suas colunas podem ser consultadas. Seria fácil portanto analisar a relação das colunas com o uso da sys.columns e das funções criadas. Vejamos como ficaria o corpo da trigger:

CREATE TRIGGER TrgForcaNomenclaturaTabela ON DATABASE
FOR CREATE_TABLE
As
BEGIN

    — Captura o esquema e a tabela
    DECLARE @SchemaName SYSNAME
    DECLARE @ObjectName SYSNAME

    SET @SchemaName = EVENTDATA().value(‘(/EVENT_INSTANCE/SchemaName/text())[1]’,‘SYSNAME’)
    SET @ObjectName = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName/text())[1]’,‘SYSNAME’)

    — Verifica se há colunas que violem as regras estabelecidas
    DECLARE @Col VARCHAR(100), @Palavra VARCHAR(10), @MSG VARCHAR(200)
    SELECT TOP(1) @Col = Name, @Palavra = Palavra
    FROM sys.columns As Cols
        CROSS APPLY dbo.FnExtraiPalavras(Cols.name) As Pals
    WHERE object_id = object_id(@SchemaName + ‘.’ + @ObjectName) AND NOT EXISTS (
        SELECT * FROM Definicoes As DEF
        WHERE Def.DefAcronimo = Pals.Palavra)

    IF ISNULL(@Col,@Palavra) IS NOT NULL
    BEGIN
        SET @MSG = ‘A coluna ‘ + @Col + ‘ da tabela ‘ +
            @SchemaName + ‘.’ + @ObjectName +
            ‘ possui palavras inválidas ‘ + CHAR(10)
        SET @MSG = @MSG + ‘O acrônimo ‘ + @Palavra + ‘ é inválido’
        RAISERROR(@MSG,16,1)
        ROLLBACK
    END
END

Vejamos agora a trigger em ação. O script a seguir cria uma tabela com as colunas NumCartCred, ValDeb e ValParCli. As palavras que compõe cada uma dessas colunas são consideradas válidas uma vez que estejam contempladas nas definições previamente cadastradas.

CREATE TABLE tblDadosCliente (
    NumCartCred CHAR(19),
    ValDeb SMALLMONEY,
    ValParCli SMALLMONEY)

Command(s) completed successfully.

Agora vejamos como se comporta a criação de uma tabela onde uma das colunas contém palavras não contempladas nas definições previamente cadastradas:

CREATE TABLE tblDadosComplementares (
    ValCred SMALLMONEY,
    NumParCli SMALLMONEY,
    CodCli INT,
    CredAnt SMALLMONEY)

Msg 50000, Level 16, State 1, Procedure TrgForcaNomenclaturaTabela, Line 30
A coluna CodCli da tabela dbo.tblDadosComplementares possui palavras inválidas
O acrônimo Cod é inválido
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Nesse caso a coluna CodCli possui acrônimos que não estão presentes na tabela de definições. O acrônimo "Cli" significa cliente, mas o acrônimo Cod não está cadastrado e por isso a instrução de CREATE TABLE é rejeitada. A coluna CredAnt também é inválida, pois, apenas o acrônimo Cred está cadastrado enquanto Ant não está presente na tabela de definições. A trigger localiza apenas a primeira referência inválida uma vez que basta apenas uma para que o comando seja rejeitado.

Embora possível a determinação de nomes de colunas através de triggers DDL, a solução envolve uma certa dose de codificação. Espero que em uma release futura do SQL Server o recurso de políticas possa evoluir o suficiente para tornar esse trabalho mais fácil. Até lá, acredito que essa solução venha atender a muitos administradores de dados na garantia de padronização dos nomes das colunas desde a elaboração do modelo de dados até a sua implementação física no SQL Server.

[ ]s,

Gustavo

Extraindo e validando nomes de uma string no padrão Camel Case

Bom Dia Pessoal,

No mundo da programação é bem comum escrevermos nomes na notação Camel Case já que quase todas as linguagens de programação evitam trabalhar com espaços nas definições de nome. Para quem não entendeu nada da frase anterior, a prática Camel Case escreve vários nomes (abreviados ou não) em uma única string. Normalmente os nomes participantes iniciam-se com letras maiúsculas e as demais letras são minúsculas. Tomemos por exemplo as palavras número, cartão e crédito. Essas palavras podem ser combinadas para formar uma variável ou uma coluna em um banco de dados. Possivelmente o nome sugerido será NumeroCartaoCredito. Podemos visualizar que as três palavras (número, cartão e crédito) estão juntas em uma única string (NumeroCartaoCredito). Visualmente é possível perceber que essa string é composta por essas três palavras e ao visualizá-la já se tem idéia do que ela significa.

Uma das boas práticas de administração de dados é a utilização de um padrão de nomenclatura. Normalmente a presença desse padrão representa uma linguagem comum que auxilia os administradores de dados, desenvolvedores, analistas e outros profissionais que irão criar e manter esses modelos de dados. De forma análoga, o mesmo princípio é utilizado para declaração de variáveis nos códigos a serem utilizados pelos softwares desenvolvidos dentro da organização. Para evitar colunas e variáveis com nomes muito grandes é comum abreviar esses nomes. Em um padrão hipotético, as palavras número, cartão e crédito poderiam ser substituídas pelos acrônimos Num, Cart e Cred e a string NumeroCartaoCredito consequentemente seria NumCartCred.

Se existe um padrão de nomenclatura, naturalmente que suas definições serão armazenadas em algum local, pois, será necessário conferir o padrão na hora de criar e manter as colunas e variáveis. O script a seguir cria uma tabela com alguns acrônimos e suas respectivas palavras chave.

— Cria uma tabela de Definições
CREATE TABLE Definicoes (
    DefID INT NOT NULL IDENTITY(1,1),
    DefAcronimo VARCHAR(100) NOT NULL,
    DefPalavra VARCHAR(200) NOT NULL)

— Insere alguns registros
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Num’,‘Número’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Cart’,‘Cartão’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Cred’,‘Crédito’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Deb’,‘Débito’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Cli’,‘Cliente’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Val’,‘Valor’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Par’,‘Parcela’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Venc’,‘Vencimento’)
INSERT INTO Definicoes (DefAcronimo, DefPalavra) VALUES (‘Dat’,‘Data’)

Agora que temos alguns acrônimos definidos, seria fácil montar alguns nomes e identificar seus respectivos significados:

Nome Significado
NumCartCred Número Cartão Crédito
ValDeb Valor Débito
ValParCli Valor Parcela Cliente
DatVencParc Data Vencimento Parcela
NumParCli Número Parcela Cliente

A idéia geral já está posta, mas como fazer para que a partir de um string se extraia o significado ? Se os nomes fossem delimitados por algum caractér como ; ou ainda | seria bem mais fácil. Bastaria utilizar algumas técnicas que já apresentei em artigos anteriores relacionados a Arrays (Parte I, II, III). Entretanto esse não parece ser o caso. A única regra é que as palavras se iniciam com uma letra maiúscula. Se tivéssemos que escolher um delimitador seria uma letra maiúscula, mas isso não representa um único caractér.

As letras maiúsculas no SQL Server iniciam-se na posição ASCII 65 representando a letra A e finalizam na posição ASCII 90 representando a letra Z. Todas as letras maiúsculas concetram-se nesse intervalo. O script abaixo demonstra esse intervalo

SELECT ASCII(‘A’) As Inicio, ASCII(‘Z’) As Fim
SELECT CHAR(65) As [65], CHAR(68) As [68], CHAR(84) As [84], CHAR(90) As [90]

Início Fim
65 90

65 68 84 90
A D T Z

No artigo Criando uma tabela com uma seqüência de números – Parte II demonstrei como gerar uma tabela de números utilizando as CTEs. Com essa mesma técnica é possível percorrer uma string é possível identificar exatamente quais são as posições que obedecem ao RANGE ASCII 65 – 90 e portanto serão maiúsculas. Algumas pessoas como o Paulo e o Thiago já fizeram algo semelhante e possivelmente seus respectivos blogs tem boas referências sobre CTEs (recomendo uma leitura posterior).

DECLARE @Nome VARCHAR(100)
SET @Nome = ‘NumCartCred’

;WITH Nums As (
    SELECT 1 As Num
    UNION ALL
    SELECT Num + 1 FROM Nums
    WHERE Num + 1 <= LEN(@Nome))

SELECT
    SUBSTRING(@Nome,Num,1) As Caracter,
    CASE WHEN ASCII(SUBSTRING(@Nome,Num,1))
        BETWEEN 65 AND 90 THEN ‘Sim’ ELSE ‘Não’ END As Maiuscula,
    Num As Posicao

FROM Nums

Caractér Maiúscula ? Posição
N Sim 01
u Não 02
m Não 03
C Sim 04
a Não 05
r Não 06
t Não 07
C Sim 08
r Não 09
e Não 10
d Não 11

Com essa tabela, podemos ver claramente quais as posições que representam letra maiúsculas (1, 4 e 8) e letras minúsculas. O próximo passo seria ligar as posições das letras maiúsculas para poder montar as palavras. O script a seguir é uma aplicação da técnica utilizada no artigo

DECLARE @Nome VARCHAR(100)
SET @Nome = ‘NumCartCred’

;WITH Nums As (
    SELECT 1 As Num
    UNION ALL
    SELECT Num + 1 FROM Nums
    WHERE Num + 1 <= LEN(@Nome)),

Analise As (

SELECT
    SUBSTRING(@Nome,Num,1) As Caracter,
    Num As Posicao
FROM Nums
WHERE ASCII(SUBSTRING(@Nome,Num,1)) BETWEEN 65 AND 90)

SELECT Caracter, Posicao As Inicio,
    ISNULL((SELECT MIN(Posicao) FROM Analise As TInt
    WHERE TInt.Posicao > TOut.Posicao),LEN(@Nome)) As Fim
FROM Analise As TOut

Caractér Início Fim
C 01 04
N 04 08
N 08 11

Agora que os intervalos de início e fim de cada palavra são conhecidos, fica muito fácil montar as palavras (um pequeno ajuste na posição fim da última palavra):

DECLARE @Nome VARCHAR(100)
SET @Nome = ‘NumCartCred’

;WITH Nums As (
    SELECT 1 As Num
    UNION ALL
    SELECT Num + 1 FROM Nums
    WHERE Num + 1 <= LEN(@Nome)),

Analise As (

SELECT
    Num As Posicao
FROM Nums
WHERE ASCII(SUBSTRING(@Nome,Num,1)) BETWEEN 65 AND 90),

Palavras As (

SELECT Posicao As Inicio,
    ISNULL((SELECT MIN(Posicao) FROM Analise As TInt
    WHERE TInt.Posicao > TOut.Posicao),LEN(@Nome) + 1) As Fim
FROM Analise As TOut)

SELECT SUBSTRING(@Nome, Inicio, Fim – Inicio) As Palavra, Inicio, Fim
FROM Palavras

Palavra Início Fim
Num 1 4
Cart 4 8
Cred 8 12

O próximo passo é encapsular esse processo em uma function para reuso:

— Criação da função
CREATE FUNCTION dbo.FnExtraiPalavras (@Nome VARCHAR(100))
RETURNS TABLE
RETURN
(

WITH Nums As (
    SELECT 1 As Num
    UNION ALL
    SELECT Num + 1 FROM Nums
    WHERE Num + 1 <= LEN(@Nome)),

Analise As (

SELECT
    Num As Posicao
FROM Nums
WHERE ASCII(SUBSTRING(@Nome,Num,1)) BETWEEN 65 AND 90),

Palavras As (

SELECT Posicao As Inicio,
    ISNULL((SELECT MIN(Posicao) FROM Analise As TInt
    WHERE TInt.Posicao > TOut.Posicao),LEN(@Nome) + 1) As Fim
FROM Analise As TOut)

SELECT SUBSTRING(@Nome, Inicio, Fim – Inicio) As Palavra, Inicio, Fim
FROM Palavras)

— Uso da função
SELECT Palavra FROM dbo.FnExtraiPalavras(‘DatVencParc’)

Palavra
Dat
Venc
Parc

Um Join com a tabela de definições pode inclusive dar o significado de cada palavra encontrada em um nome em particular:

— Mostra o significado das palavras em um nome específico
DECLARE @Nome VARCHAR(100)
SET @Nome = ‘ValParCli’

SELECT DefAcronimo As Acronimo, DefPalavra As Palavra
FROM Definicoes As Def
INNER JOIN dbo.FnExtraiPalavras(@Nome) As Pal ON Def.DefAcronimo = Pal.Palavra

Acrônimo Palavra
Val Valor
Par Parcela
Cli Cliente

Embora a função seja capaz de extrair as palavras de um string, o próximo passo é verificar a validade dessas palavras, ou seja, é necessário que cada palavra extraída possua uma definição previamente cadastrada. A seguir mostro como fazer isso com outra função:

— Cria uma função de validação
CREATE FUNCTION dbo.ValidaNome (@Nome VARCHAR(100))
RETURNS INT
AS
BEGIN

    — Declara uma variável de retorno
    DECLARE @Retorno INT
    SET @Retorno = 1

   — Se houver alguma palavra não cadastrada
    IF EXISTS (SELECT * FROM dbo.FnExtraiPalavras(@Nome) As Pal
        WHERE NOT EXISTS (SELECT * FROM Definicoes As Def
            WHERE Pal.Palavra = Def.DefAcronimo))
    SET @Retorno = 0
    RETURN (@Retorno)
END

— Testa a função
SELECT
    dbo.ValidaNome(‘NumParCli’) As NumParCli,
    dbo.ValidaNome(‘NumDebPar’) As NumDebPar,
    dbo.ValidaNome(‘NumIdenCli’) As NumIdenCli

NumParCli NumDebPar NumIdenCli
1 1 0

Para os nomes "NumParCli" e "NumDebPar" a função retornou 1 o que mostra que ambos são válidos já que todas as palavras constituintes estão previamente cadastradas nas definições. O nome "NumIdenCli" ao ser submetido para análise retornou 0 uma vez que uma ou mais palavras não estão cadastradas nas definições (no caso a palavra Iden).

[ ]s,

Gustavo