Marzo de 2017

Volumen 32, número 3

Tecnología de vanguardia: actualizaciones suaves con tablas temporales

Por Dino Esposito | Marzo de 2017

Dino EspositoLa inmensa mayoría de los desarrolladores actuales usan una base de datos relacional clásica para almacenar datos. Es un enfoque que funcionó durante décadas y que sigue funcionando hoy, aunque los almacenes de datos alternativos y sin esquema (conocidos colectivamente como almacenes NoSQL) están demostrando ser bastante eficaces en distintos escenarios empresariales. Cada vez que actualiza un registro de tabla existente, pierde automáticamente la pista del estado que tenía. Sobrescribir los datos existentes no ha sido un gran problema para las empresas hasta ahora, pero todo cambia muy rápido. Hoy en día, los datos son el activo más valioso de toda empresa y la entrada de los procesos de inteligencia empresarial.

En las ediciones de mayo y junio de 2016 de esta columna (msdn.com/magazine/mt703431 y msdn.com/magazine/mt707524, respectivamente), proporcioné un enfoque general para llevar un sistema de creación, lectura, actualización y eliminación (CRUD) al siguiente nivel por medio de actualizaciones suaves y eliminaciones. Una actualización suave es una operación de actualización estándar, excepto en que el estado antiguo del registro se mantiene en cierto modo. Al hacerlo, dispone de una API adicional para recuperar el historial de cada entidad creada durante la vigencia del sistema.

Existen muchas maneras de enfocar el problema de la actualización y eliminación de registros al mismo tiempo que se sigue su pista. Para las eliminaciones, implica agregar una columna adicional (por lo general, una columna booleana) para marcar el registro como eliminado. En el caso de las actualizaciones, el enfoque más funcional es crear y mantener una tabla de historial aparte para cada una ellas cuya pista se deba seguir. Para mantener las tablas de datos y de historial sincronizadas se requiere una lógica empresarial y de acceso a datos adicional, así como una API dedicada para consultar el historial.

La administración de datos históricos de tablas relacionales está formalizada en el estándar ANSI SQL 2011. En su versión más reciente, SQL Server admite una característica que se conoce como tablas temporales, que permite crear y administrar una tabla histórica paralela para cada tabla de datos de su elección. Este mes, profundizaré en las tablas temporales de SQL Server 2016 y en cómo se usan desde Entity Framework (EF).

Estructura de las tablas temporales

Teóricamente, la principal diferencia entre las bases de datos temporales y las bases de datos clásicas es que las clásicas solo almacenan datos que son reales en ese momento concreto. En cambio, las bases de datos temporales mantienen varias copias de cada fragmento de datos. Una tabla temporal anexa algunas columnas de tiempo adicionales para indicar cuándo el registro alcanzó un estado determinado. En la Figura 1 se muestra el aspecto de una tabla temporal en SQL Server 2016 Management Studio.

Una tabla temporal en SQL Server 2016
Figura 1 Una tabla temporal en SQL Server 2016

En la figura se pueden percibir dos cosas. Una es la tabla de historial secundaria, denominada dbo.BookingsHistory. SQL Server crea automáticamente esta tabla cada vez que procesa la instrucción T-SQL que crearía una tabla temporal. Los desarrolladores tienen acceso de solo lectura a la tabla de historial. La otra cosa que se puede observar en la Figura 1 es la ausencia del comando Eliminar en el menú contextual de la tabla seleccionada. Después de crear una tabla temporal, cualquier manipulación adicional en esta, ya sea a través de la interfaz de SQL Server Management Studio o mediante programación, se controla estrictamente y en ocasiones está limitada. A modo de ejemplo, puede colocar o replicar una tabla temporal. Las limitaciones también existen en las actualizaciones y eliminaciones en cascada. Puede encontrar más información sobre las limitaciones que afectan a las tablas temporales de SQL Server 2016 en bit.ly/2iahP1n.

En SQL Server 2016, una tabla temporal se crea mediante una cláusula especial al final de la instrucción CREATE TABLE. El estado de la tabla temporal se reduce a activar y desactivar el valor del nuevo parámetro SYSTEM_VERSIONING. En otras palabras, cualquier tabla se puede convertir mediante programación en una tabla temporal y, luego, devolverse al estado no temporal original en cualquier momento. Todas las limitaciones mencionadas anteriormente que afectan a las tablas temporales dejan de existir en el momento en que se desactiva el parámetro SYSTEM_VERSIONING.

Tabla temporal y Entity Framework

Muchos desarrolladores usan actualmente SQL Server a través de los servicios de EF y de EF Code First, en concreto. No obstante, EF no proporciona compatibilidad especial para las tablas temporales de momento. La compatibilidad ad hoc se incluirá en un futuro próximo. La buena noticia es que aún se puede conseguir algún nivel básico de compatibilidad para las tablas temporales con las versiones actuales de EF 6.x e incluso más con EF Core. La mala notifica es que la integración completa con LINQ to Entities es realmente posible de conseguir con cambios de bajo nivel en el marco, específicamente de la manera en que el proveedor de LINQ to Entities genera el código SQL de la consulta. Si es un desarrollador de SQL, en cambio, el lenguaje T-SQL más reciente proporciona todas las herramientas de sintaxis necesarias para manipular tablas temporales.

La innovación de las tablas temporales para los desarrolladores de EF se puede resumir de la siguiente manera: Primero, es bastante fácil crear tablas temporales en Code First. Segundo, las actualizaciones y eliminaciones se pueden realizar a través de EF de la manera habitual. Tercero, las consultas requieren la creación de características ad hoc.

El enfoque que encontré más eficaz para consultar tablas temporales pasa por un pequeño conjunto de métodos de repositorio ad hoc basado en código de ADO.NET. Puede sonar extraño al principio, pero al final del día, si necesita tablas temporales, lo más probable es que necesite sobre todo obtener el historial de una entidad concreta. Considere, por ejemplo, todos los cambios de un pedido o una factura.

Por tanto, al final, todo lo que necesita es tener un método accesible y dedicado, como FirstOrDefault, que se exponga directamente para un agregado. Una clase de repositorio me parece un buen lugar para tenerlo.

Un inicializador habilitado para el uso temporal

En EF Code First, se crea una nueva base de datos siempre que la base de datos no exista y la clase DbContext se herede de CreateDatabaseIfNotExists. Al hacerlo, se crea una nueva tabla para cada propiedad DbSet declarada. ¿Puede crear también una tabla temporal? Actualmente, sin los atributos ad hoc y las características de sintaxis, la creación de una tabla temporal es una operación de dos pasos. En primer lugar, se debe crear la tabla normal; este es el tipo de trabajo que Code First suele realizar. El segundo paso gira en torno a la activación del parámetro SYSTEM_VERSIONING. Este requiere una instrucción ad hoc ALTER TABLE. En la Figura 2 se muestra una implementación posible del método Seed de la clase de inicializador, que primero comprueba la versión de la instancia SQL Server subyacente y, luego, altera el estado de una tabla Booking creada anteriormente. 

Figura 2 Creación de una tabla temporal a través de 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);
}

El comando T-SQL real que necesita para crear una tabla temporal (o una tabla con versión del sistema, como se denomina en el argot de SQL Server 2016), se muestra en la Figura 3.

Figura 3 Creación de una tabla 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))";

Los marcadores de posición {0} que se muestran en la cadena de la Figura 3 hacen referencia al nombre de la tabla real. Es Bookings, como se muestra en la Figura 1.

La tabla de historial resultante es una copia de la tabla principal, más un par de columnas datetime2 denominadas SysStartTime y SysEndTime. En general, las dos columnas indican el período de validez de ese estado concreto del registro. SysStartTime indica cuándo obtuvo el registro un estado determinado y SysEndTime indica cuándo cesó la validez de dicho estado. Las actualizaciones y eliminaciones son las operaciones de base de datos que provocan cambios en los valores de SysStartTime y SysEndTime.

Actualizaciones y eliminaciones

La lógica que mantiene esa tabla principal y su historial sincronizados se devuelve al motor de base de datos SQL Server 2016. Se crea un nuevo registro de historial siempre que se actualiza un registro de la tabla principal, independientemente de cómo se realice la actualización. En otras palabras, tanto si edita los valores de un registro temporal directamente en Management Studio, a través de procedimientos almacenados, comandos de ADO.NET o EF, se crea un nuevo registro de historial, como se muestra en la Figura 4.

Actualización de registros en una tabla temporal
Figura 4 Actualización de registros en una tabla temporal

La primera consulta que se muestra en la Figura 4 presenta el estado actual del registro con ID=2. La segunda consulta, en cambio, presenta los registros encontrados en la tabla de historial para el mismo ID. Un estado observable como este viene determinado por dos actualizaciones rápidas que realicé directamente en el editor de Management Studio. Primero cambié el valor de la columna Hour de 9 a 13 y, luego, un par de segundos después, cambié el valor de la columna Owner de Dino a Paul. El primer registro de la tabla de historial indica que el registro creado inicialmente (que coloqué en la tabla a través de EF y de una llamada a SaveChanges) tuvo un estado válido durante unos 5 minutos. Luego, cambió a otro estado que duró un par de segundos y, finalmente, llegó al estado actual. Como puede ver, el estado actual no está almacenado en la tabla de historial. La Figura 5 muestra el estado de las tablas después de eliminar el registro con ID=2.

Eliminación de registros en una tabla temporal
Figura 5 Eliminación de registros en una tabla temporal

La tabla principal devuelve un conjunto de resultados vacío cuando se realiza la consulta de ID=2. La tabla de historial, en cambio, tiene ahora un tercer registro, cuyo período de validez va desde la hora de la última actualización hasta la hora de su eliminación.

Consulta de una entidad específica

Seguir la pista de todos los cambios de estado resulta útil porque permite no perderse nada de lo que sucede en el sistema. Proporciona un registro completo (y gratuito) de todas las operaciones de la base de datos. Y lo mejor, ofrece una lista completa de cambios de estado, que tiene un rol empresarial mucho más importante que un registro estándar de instrucciones SQL. Dicho de otro modo y desde un punto de vista conceptual, las tablas temporales se parecen mucho al abastecimiento de eventos; me atrevería a decir que las tablas temporales son una forma de abastecimiento de eventos basada en CRUD. Veamos cómo puede consultar estados pasados de un agregado determinado.

Aunque la tabla anidada History resulta útil para encontrar soluciones, SQL Server 2016 ofrece una sintaxis directa para consultar datos temporales sobre un registro determinado. Este es el esquema de un comando de ejemplo que recupera versiones del registro con ID=2 en un intervalo de tiempo determinado:

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

Una consulta temporal es una consulta estándar con la cláusula FOR SYSTEM_TIME, que establece el intervalo de tiempo a tener en cuenta. El motor de base de datos resolverá la consulta mediante la observación de las columnas adicionales de la tabla de historial, y del contenido de las tablas principal y anidada. Se espera que la consulta devuelva una lista de registros. ¿Cómo se puede obligar a EF a ejecutar una consulta como esta? En EF 6, solo puede usar el método SqlQuery de la clase 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();
}

Tenga en cuenta que para EF 6, los nombres de columna devueltos en la consulta deben coincidir con los nombres de propiedad en la clase. El motivo es que SqlQuery no usa asignaciones. Si los nombres de columna y propiedad no coinciden, deberá definir un alias para las columnas de la lista SELECT, en lugar de solo SELECT *.

Con EF Core, todo es de alguna manera más fácil y mejor. En EF Core, el método que se debe usar es FromSql. En primer lugar, el método FromSql usa asignaciones, lo que significa que no tiene que preocuparse por la definición de alias si los nombres de columna y propiedad no coinciden:

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);
}

En segundo lugar, puede redactar sobre la selección inicial mediante LINQ. Esto significa que puede usar Where, OrderBy, GroupBy o cualquier otro operador de LINQ, que en general se traducirán en una consulta del tipo:

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

Dicho todo esto, si lo prefiere, siempre puede usar ADO.NET estándar y lectores de datos para acceder a los datos almacenados en tablas temporales.

Resumen

Definitivamente, puede trabajar con tablas temporales en una capa de datos basada principalmente en EF y, aunque decida usar ADO.NET estándar para las consultas, puede emplear LINQ to Objects para la compilación en memoria compleja. El mapa de ruta de Entity Framework muestra algunos elementos de trabajo que se llevarán a cabo en las tablas temporales durante los próximos meses. Por tanto, vamos a esperar.


Dino Esposito es el autor de "Microsoft .NET: Architecting Applications for the Enterprise" (Microsoft Press, 2014) y "Modern Web Applications with ASP.NET" (Microsoft Press, 2016). Como experto técnico para las plataformas .NET y Android en JetBrains y orador frecuente en eventos internacionales del sector, Esposito comparte su visión sobre el software en software2cents.wordpress.com y en su Twitter @despos.

Gracias al siguiente experto técnico de Microsoft por revisar este artículo: Rowan Miller