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

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

  1. Karamba Gustavo, muito interessante essa politica… vou adotar esse modelo para os novos projetos que aparecem em meu ambiente de trabalho depois da migração para o SQL SERVER 2008..Não sei é possível fazer isso no sql server 2000, para que seja evitado(isso acontece muito) a criação de procedures com inicio "sp", deixando criar apenas aquelas com prefixo "proc".. tem como fazer isso ???

  2. Oi Alexandre,Essa foi pro pessoal do fundo da sala (rs). Esse exemplo foi o mais completo, mas ainda quero montar outro um pouco mais simples. Infelizmente o SQL Server 2000 não possui os recursos de políticas ou triggers DDL para fazermos o bloqueio de SPs com o prefixo sp_. A única coisa que poderíamos fazer é montar um job para de tempos em tempos varrer os bancos a procura dessas stored procedures e informar ao DBA. Se fosse pelo menos 2005…Abs,

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