Artigo
03/15/2017

Março de 2017

Volume 32 - Número 3

Cutting Edge - Atualizações suaves com tabelas temporais

Por Dino Esposito | Março de 2017

Dino Esposito A grande maioria dos desenvolvedores de hoje usa um banco de dados relacional clássico para armazenar dados. É uma abordagem que funcionou por décadas e ainda funciona, apesar de armazenamentos de dados alternativos e mais simples (coletivamente chamados de armazenamentos NoSQL) serem comprovadamente eficientes em uma série de cenários de negócios. Sempre que você atualiza um registro de tabela existente, automaticamente perde o controle do seu estado anterior. Substituir dados existentes não foi um grande problema para as empresas até agora, mas as coisas estão mudando rapidamente. Hoje em dia, os dados são o bem mais valioso de cada empresa e a inserção de processos de business intelligence são fundamentais.

Nos capítulos de maio e junho de 2016 desta coluna (msdn.com/magazine/mt703431 e msdn.com/magazine/mt707524, respectivamente), apresentei uma abordagem geral para aprimorar o sistema de criação, leitura, atualização e exclusão (CRUD) por meio de atualizações e exclusões suaves. A atualização suave é uma operação de atualização padrão, exceto pelo fato de o estado antigo do registro ser, de certa forma, preservado. Com ela, você tem uma API adicional para recuperar o histórico de cada entidade criada durante o ciclo de vida do sistema.

Não há muitos meios para se lidar com o problema de atualizar e excluir registros e, ao mesmo tempo, manter o controle sobre eles. No caso das exclusões, adiciona-se uma coluna***, normalmente do tipo booliano***, para assinalar o registro como excluído. Nas atualizações, a abordagem mais prática é criar e manter uma tabela de histórico separada para cada acompanhamento considerado relevante. Manter dados e tabelas de histórico sincronizados exige uma lógica adicional de acesso comercial e a dados, além de uma API dedicada para consultar o histórico.

O gerenciamento de dados de histórico em tabelas relacionais foi formalizado no padrão ANSI SQL 2011. A versão mais recente do SQL Server oferece suporte a um recurso chamado “tabelas temporais” que permite criar e gerenciar uma ***tabela de histórico de sombra para cada tabela escolhida. Neste mês, vou me aprofundar nas tabelas temporais do SQL Server 2016 e no seu uso no Entity Framework (EF).

Estrutura das tabelas temporais

A principal diferença conceitual entre bancos de dados temporais e clássicos é que estes só armazenam dados verdadeiros no momento atual. Os bancos de dados temporais, ao contrário, mantêm várias cópias de cada dado. A tabela temporal anexa mais algumas colunas de ***tempo, que indicam quando o registro atingiu o estado atual. A Figura 1mostra uma tabela temporal no SQL Server 2016 Management Studio.

Figura 1 Uma tabela temporal no SQL Server 2016

Alguns pontos se destacam nesta figura. Um deles é a tabela secundária de histórico, com o nome dbo.BookingsHistory. O SQL Server cria essa tabela automaticamente, sempre que processa a instrução T-SQL criadora de uma tabela temporal. Os desenvolvedores têm apenas acesso de leitura à tabela de histórico. Outro ponto a observar na Figura 1 é a falta do comando ***Delete (Excluir) no menu de contexto da tabela selecionada. Uma vez criada a tabela temporal, qualquer manipulação ali realizada, seja usando a interface do SQL Server Management Studio ou de forma programática, é rigorosamente controlada e, em alguns casos, limitada. Por exemplo, não se pode eliminar ou replicar uma tabela temporal, e também há limitações para atualizações e exclusões em cascata. Veja mais informações sobre limitações que afetam as tabelas temporais no SQL Server 2016 em bit.ly/2iahP1n.

No SQL Server 2016, a tabela temporária é criada com o uso de uma cláusula especial no final da instrução CREATE TABLE. O status da tabela temporal se resume a ativar e desativar o valor da nova definição de SYSTEM_VERSIONING. Em outras palavras, qualquer tabela pode ser transformada de modo programático em tabela temporal e, em seguida, devolvida ao status não temporal original a qualquer momento. Todas as limitações já mencionadas que afetam as tabelas temporais deixam de existir quando a definição SYSTEM_VERSIONING é desativada.

Tabela temporal e Entity Framework

Muitos desenvolvedores usam o SQL Server por meio dos serviços ***EF e EF Code First, em especial. No entanto, no momento, o EF não oferece suporte especial para tabelas temporais. Será fornecido suporte ad hoc em futuro próximo. A boa notícia é que pode-se obter um nível básico de suporte para tabelas temporais com as versões atuais do EF 6.x e, ainda mais, com o EF Core. A má é que, realisticamente, a integração completa com o LINQ-to-Entities só é possível com alterações de nível inferior na estrutura, especialmente no modo como o provedor do LINQ-to-Entities gera o código SQL para a consulta. Porém, se você for desenvolvedor do SQL, a linguagem T-SQL mais recente fornece todas as ferramentas de sintaxe necessárias à operação de tabelas temporais.

O que há de mais moderno em termos de tabelas temporais para desenvolvedores do EF pode ser resumido assim: Primeiramente, é bastante fácil criar tabelas temporais no Code First. Em segundo lugar, as atualizações e exclusões podem ser realizadas pelo EF da forma normal. Em terceiro lugar, a criação de consultas exige recursos ad hoc.

A forma que achei mais eficaz para a consulta de tabelas temporais passa por um pequeno conjunto de métodos de repositório ad hoc baseados em código ADO.NET. Pode parecer surpreendente a princípio, mas, no fim do dia, se você precisar de tabelas temporais, há grande chance de ser preciso obter, principalmente, o histórico de uma entidade específica. Vejamos, por exemplo, todas as alterações de um pedido ou de uma fatura.

No final, você só precisa ter um método dedicado e acessível, parecido com o FirstOrDefault exposto diretamente para uma agregação. E uma classe de repositório me parece um bom local para isso.

Inicializador habilitado para tabelas temporais

No EF Code First, é criado um novo banco de dados sempre que não há nenhum e a classe DbContext herda de CreateDatabaseIfNotExists. Com isso, é criada uma nova tabela para cada propriedade DbSet declarada. Pode-se criar também uma tabela temporal? Hoje, sem atributos ad hoc e recursos de sintaxe, a criação de uma tabela temporal é uma operação com duas etapas. A primeira é a criação de uma tabela comum; esse é o tipo de trabalho comum do Code First. A segunda volta à ativação da definição SYSTEM_VERSIONING. Isso requer uma instrução ad hoc ALTER TABLE. A Figura 2 mostra uma implementação possível do método ***Semente da classe de inicializador que verifica a versão do SQL Server subjacente e, em seguida, altera o estado de uma tabela booleana criada anteriormente.

Figura 2 Criação de uma tabela temporal com Code First

protected override void Seed(DbContext context)
{
  // Grab the SQL Server version number
  var data = context
    .Database
    .SqlQuery<string>(@"select
  left(cast(serverproperty('productversion')
       as varchar), 4)")
    .FirstOrDefault();
  if (data != null)
  {
    var version = data.ToInt();+
    if (version < 13)
      throw new Exception("Invalid version of SQL Server");
  }
  // Prepare the SQL to turn system versioning on SQL Server 2016
  var cmd = String.Format(SqlSystemVersionedDbFormat, "Bookings");
  context.Database.ExecuteSqlCommand(cmd);
}

O comando T-SQL de que você precisa para criar uma tabela temporal (ou com versão do sistema, pois é referenciada no jargão do SQL Server 2016), é mostrado na Figura 3.

Figura 3 Criação de uma tabala temporal

private const string SqlSystemVersionedDbFormat =
  @"ALTER TABLE dbo.{0}
    ADD SysStartTime datetime2(0)
    GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT
      DF_{0}_SysStart DEFAULT SYSUTCDATETIME(),
    SysEndTime datetime2(0)
    GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT
      DF_{0}_SysEnd DEFAULT CONVERT(datetime2 (0),
      '9999-12-31 23:59:59'),
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
  ALTER TABLE dbo.{0}
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.{0}History))";

Os {0} espaços reservados vistos na cadeia de caracteres mostrada na Figura 3 referem-se ao nome da tabela real. O nome é Bookings, como mostra a Figura 1.

A tabela de histórico resultante é uma cópia da principal, somada a um par de colunas datetime2 chamadas SysStartTime e SysEndTime. Juntas, as duas colunas indicam o período de validade desse estado específico do registro. SysStartTime indica quando o registro atingiu determinado estado e SysEndTime indica quando a validade desse estado terminou. Atualizações e exclusões são as operações de banco de dados que provocam mudança nos valores de SysStartTime e SysEndTime.

Atualizações e exclusões

A lógica que mantém o sincronismo entre essa tabela principal e seu histórico baseia-se no mecanismo de banco de dados do SQL Server 2016. Cria-se um novo registro de histórico sempre que um registro é atualizado na tabela principal, qualquer que tenha sido o modo como a atualização foi feita. Ou seja, quer você edite os valores de um registro temporal diretamente no Management Studio, usando procedimentos armazenados, comandos ADO.NET ou o EF, é criado um novo registro de histórico, como mostra a Figura 4.

Figura 4 Atualização de registros em uma tabela temporal

A primeira consulta mostrada na Figura 4 apresenta o estado atual do registro com ID=2. A segunda, por sua vez, mostra o registro encontrado na tabela de histórico para ***o mesmo ID. Esse estado observável foi determinado por duas atualizações rápidas que fiz diretamente no editor do Management Studio. Mudei primeiramente a coluna Hour de nove para 13 e, alguns segundos depois, mudei o valor da coluna Owner de Dino para Paul. O primeiro registro da tabela de histórico informa que aquele criado originalmente (que coloquei na tabela usando o EF e uma chamada para SaveChanges) esteve em estado válido por cerca de cinco minutos. Em seguida, passou a outro estado, que durou alguns segundos e, finalmente, chegou ao estado atual. Como se pode ver, o estado atual não está armazenado na tabela de histórico. A Figura 5 mostra o estado das tabelas após a exclusão do registro com ID=2.

Figura 5 Exclusão de registros em tabelas temporais

A tabela principal retorna um conjunto de resultados vazio quando consultada para ID=2. A tabela de histórico, por sua vez, tem agora um terceiro registro, cujo período de validade vai do momento da última atualização ao momento da exclusão.

Consulta para uma entidade específica

Manter o controle de todas as mudanças de estado é algo útil, pois permite que não se perca nada do que acontece no sistema. Esse controle fornece um registro completo (e ***gratuito) de todas as operações do banco de dados. E, melhor ainda, fornece uma lista de mudanças de estado, com função comercial muito mais relevante do que um registro básico de instruções SQL. Em outras palavras, em termos conceituais, as tabelas temporárias são muito próximas de @@@Event Sourcing, e ouso dizer que elas são uma forma de Event Sourcing baseada em CRUD. Vejamos como se pode consultar estados passados de um determinado agregado.

Embora a tabela aninhada ***History (Histórico) seja útil para ***cálculos, o SQL Server 2016 oferece uma sintaxe direta para consulta de dados temporais sobre determinado registro. Temos aqui o esquema de um exemplo de comando que recupera versões do registro com ID=2 em determinado período:

var sql = @"SELECT * FROM Bookings 
  FOR SYSTEM_TIME BETWEEN '{0}' AND '{1}'
  WHERE ID=2";

Uma consulta temporal é uma consulta normal mais a cláusula FOR SYSTEM_TIME que define o período a ser considerado. O mecanismo de banco de dados resolve a consulta verificando as colunas adicionais da tabela de histórico e o conteúdo das tabelas principal e aninhada. A consulta deve retornar uma lista de registros. Como se pode forçar o EF a executar uma consulta como essa? No EF 6, só se pode aproveitar o método SqlQuery da classe DbSet:

using (var db = new EF6Context())
{
  var current = db.Bookings.Single(b => b.Id == 1);
  var time = DateTime.Now.AddMinutes(-5);
  var old = db.Bookings
    .SqlQuery("SELECT * FROM dbo.Bookings
          FOR SYSTEM_TIME AS OF {0} WHERE Id = 1", time)
    .SingleOrDefault();
}

Observe que, no EF 6, os nomes de colunas retornados na consulta devem corresponder aos nomes de propriedades na classe. Isso ocorre porque o SqlQuery não usa mapeamentos. Se os nomes de colunas e propriedades não corresponderem, será preciso criar alias para as colunas na lista SELECT, não apenas ***em SELECT *.

Com o EF Core, as coisas são, de certa forma, melhores e mais fáceis. No EF Core, o método a ser usado é FromSql. Primeiramente, o método FromSql usa mapeamentos; isso significa que você não precisa se preocupar com alias se os nomes de colunas e propriedades não corresponderem:

using (var db = new EFCoreContext())
{
  var current = db.Bookings.Single(b => b.Id == 1);
  var time = DateTime.Now.AddMinutes(-5);
  var old = db.Bookings
    .FromSql("SELECT * FROM dbo.Bookings
              FOR SYSTEM_TIME AS OF {0}", time)
    .SingleOrDefault(b => b.Id == 1);
}

Em segundo lugar, é possível compor além da seleção inicial usando LINQ. Isso significa que você pode usar Where, OrderBy, GroupBy ou qualquer outro operador LINQ e, em geral, os itens serão convertidos em uma consulta com a forma:

SELECT projection
FROM (SELECT * FROM dbo.Bookings FOR SYSTEM_TIME AS OF {0}) as Bookings
WHERE condition

Dito isso, se você pode usar, se preferir, ADO.NET e leitores de dados básicos para acessar dados armazenados em tabelas temporais.

Conclusão

Pode-se perfeitamente trabalhar com tabelas temporais em uma camada de dados amplamente baseada em EF e, mesmo que se use ADO.NET básico para as consultas, é possível aproveitar LINQ-to-Objects para ***criar objetos complexos na memória. O roteiro da equipe do Entity Framework mostra, nos próximos meses, alguns itens de trabalho ***possíveis em tabelas temporais. Vamos aguardar.

Dino Espositoé o autor de “Microsoft .NET: Architecting Applications for the Enterprise” (Microsoft Press, 2014) e de “Modern Web Applications” (Microsoft Press, 2016). Evangelista técnico das plataformas .NET e Android no JetBrains e palestrante frequente em eventos do setor no mundo todo, Esposito compartilha sua visão de software em software2cents.wordpress.com e, no Twitter, em @despos.

Agradecemos aos seguintes especialistas técnicos da Microsoft pela revisão deste artigo: Rowan Miller